mysql database interview questions (Student_Course_Results_Teacher table)
Student(Sid,Sname,Sage,Ssex)Student table
Sid: Student number
Sname: Student name
Sage: Student age
Ssex: Student gender< /p>
Course(Cid,Cname,Tid)course table
Cid: course number
Cname: course name
Tid: teacher number
SC(Sid,Cid,score)grade table
Sid: student number
Cid: course Number
score: grade
Teacher(Tid,Tname)Teacher table
Tid: Teacher number:
Tname: Teacher name
1.Insert data
2.Delete all data of course table
3, change the name Zhang San to Zhang Dashan in the student table
or
4, query the number of teachers with the last name of ‘Li’:
5, query the student number of all students who have a grade of less than 60 in the course, Name:
6. Query the number and name of students who have not taken all the courses
7. Query the number and average grade of students who have an average of more than 60
8. Query the number and average grade of students who have taken the course number “100” and also the course number “101”. The student number and name of students who have taken “100” and have also taken “101”
9. The student number and name of all students who have received a higher grade in “100” than in “101. The student’s number
10, query course number “100” than the course number “101” course grades of all the students of the student’s number, name
11, query the learning of the “The number and name of all the students who have taken all the courses taught by Mr. Lu Xun
12, query all the students’ number, name, number of courses taken, and total grade
13, query all the students who have taken at least one course that is the same as that of the student with the number “1”. p>Error
15, query and “2” number of students studying the exact same course of other students number and name
16, deletion of learning “Mr. Lu Xun” class of the SC table records
17, to the SC table to insert a number of records, these records are required to meet the following conditions: did not take the course number “003” classmate number, the average grade of the 002 class
18, the query of the subject grades of the highest and lowest score: to The following form is displayed: course ID, highest score, lowest score
19, according to the average grade of each subject in descending order and the percentage of passing rate in descending order
20, query the following average grade of the course and the percentage of passing rate (with “1 line” display) Math (100), Language (101), English (102)
22, query the average score of different courses taught by different teachers is shown from high to low
23, query the following course grades of the 3rd to 6th student report card: Math (100), Language (101), English (102)
23, statistics on the following subject grades, the number of people in each score band: course ID, course name, [100-85],[85-70],[70-60],[less than 60]
24, query the average student grades and their rank
25, query the records of the top three grades in each subject (not considering the The number of students who have taken each course
27. The number and name of all students who have taken only one course
28. The number of male and female students
29. The number of students whose last name is “Zhang” students list
30, query the list of students with the same name and surname, and statistics on the number of students with the same name
31, the list of students born in 1981 (Note: the type of the SAGE column in the STUDENT table is DATETIME)
32, query the number, name and average grade of all students with an average grade greater than 85
33, query the average grade of each course, and the results are sorted in ascending order of the average grade, and when the average grade is the same, the results are sorted in descending order of the course number
34, query the name of the course as “English” and the names and scores of students with scores lower than 60
35, query the course selection of all students
36, query the names, course names and scores of students who have scores of 70 points or more in any course
37、Query the failed courses and arrange them in descending order by course number
38、Query the number and name of the student whose course number is “101” and whose course grade is 80 or above
39、Find out the number of students who have taken the course:
< p>
40、Query the name and grade of the student with the highest grade among the students who took the course taught by Mr. Lu Xun
41、Retrieve the student numbers of the students who took at least two courses
42、Query the course numbers and course names of the courses that all the students have taken
42、Query the course numbers and course names of the courses that all the students have taken. Course number and course name (1. a course taken by all students, 2. all courses chosen by all students)
43, search for the names of students who have not taken any course taught by Mr. Lu Xun
44, search for the student numbers of students who have failed more than two courses and their average number of students who have failed more than two courses
44, search for the student numbers of students who have failed more than two courses and their average number of students who have failed more than two courses. The number of students who have failed more than two courses and their grade point averages
45. Retrieve the number of students who have scored less than 60 points in “101” and are listed in descending order of their scores
46. Delete the number of students who have scored less than 60 points in “101” and are listed in descending order of their scores
46. The classmate’s “101” course grade
Database Basics – Mysql
Database Basics – Mysql includes what is a database,, Mainstream databases, MySQL architecture,,, Relationships between servers, databases, and tables,, Using databases ,, SQL classification, storage engines.
1. What is a database
Databases are used to store data. So we have previously learned that storing data can be done using files, so why get a database?
This is going to talk about a few disadvantages of using files to save data:
File security issues, files are not conducive to data query and management, files are not conducive to storing large amounts of data, files in the program control is not convenient.
So in order to solve the above mentioned problems, experts have designed something more conducive to manage data – databases, which manage data more efficiently. The level of database is an important indicator of the level of a programmer.
Database: a collection of physical operating system files or other forms of file types. In MySQL databases, database files can
be files ending in frm,MYD,MYI,ibd. When using the NDB engine, the database files may not be operating system files, but files stored in memory, but the definitions remain the same.
2. Mainstream databases
l MySQL: the world’s most popular database, belongs to Oracle, concurrency is good, not suitable for complex business. Mainly used in e-commerce, SNS, forums. Good for simple SQL processing.
l SQLSever: Microsoft’s products, the favorite of the .Net program elements, commonly used in medium and large projects.
l Oracle: Oracle products, suitable for large projects, complex business logic, concurrency is generally not as good as MySQL.
2.1 MySQL
I’m mainly learning about MySQL databases, then learn more about understanding MySQL databases.MySQL is designed to be a portable database that can run on virtually all current MySQL is designed to be a portable database that runs on almost all current systems, such as Linux, Windows, Mac, Solaris, and FreeBSD, although the underlying (e.g., threaded) implementations vary from platform to platform.
But MySQL essentially ensures that the physical architecture is consistent across platforms. As a result, users should have a good understanding of how MySQL databases work on all of these platforms.
3. MySQL Architecture:
A MySQL database consists of background threads as well as a shared memory area. The shared memory can be shared by running background threads. It is important to keep in mind that the database instance is what is actually used to manipulate the database files. In MySQL, the relationship between instances and databases is usually one-to-one. That is, one instance corresponds to one database, and one database corresponds to one instance. However, in a clustered situation there may be cases where one database is used by multiple data instances.
MySQL is designed as a single-process, multi-threaded database, similar to SQLServer but different from Oracle’s multi-process architecture (Oracle’s Windows version is also single-process, multi-threaded). This means that the MySQL database instance is represented on the system as a process. You can observe the MySQL database process after it is started with the ps command:
Note that the process with process 17022 is the MySQL instance. When an instance is started, the MySQL database goes and reads the configuration file and starts the database instance based on the parameters in the configuration file. It is possible to have no configuration file in the MySQL database, in which case MySQL will start the instance according to the default parameter settings at compile time.
To see the locations where the configuration file is looked for when a MySQL instance is started:
[Lxy@VM-20-12-centos~]$mysql–help|grepmy.cnforderofpreference,my.cnf,$MYSQL_TCP_ PORT,/etc/my.cnf/etc/mysql/my.cnf/usr/etc/my.cnf~/.my.cnf
You can see that the MySQL database is running according to /etc/my.cnf->/etc/mysql/my.cnf->/usr/etc/my. cnf->/usr/etc/my.cnf->/usr/etc/my.cnf->/usr/etc/my.cnf->/usr/etc/my. If several configuration files have the same parameter, the MySQL database will follow the last parameter read. In a Linux environment, configuration files are generally placed under /etc/my.cnf. On Windows, the configuration file may have a .cnf or .ini extension.
The configuration file has a parameter, datadir, which specifies the path where the database resides. In Linux, the default datadir is /usr/local/mysql/data, which can be modified by the user, but of course you can also use this path.
3.1 Connecting to the server
Enter:
mysql-uroot-p.
Note:
If you don’t write -h127.0.0.1 defaults to local connection
If you don’t write “-P3306` defaults to port number 3306
3.2 Server Management.
l Open Service Manager by executing win+r and typing services.msc.
l Manage services via the Stop, Pause, Restart buttons on the left side of the image below (generally recommended to leave them untouched).
4, the relationship between the server, database, table
The so-called installation of the database server, just installed on the machine a database management system program, this management program can manage multiple databases, the general developers for each application to create a database.
In order to save the data of the entities in the application, multiple tables are generally created in the database to save the data of the entities in the program.
5, using the database
Here using the database is just some simple sql statements, more believe that more complex will be gradually presented in the later blog.
Display all the current database.
showdatabases;
Using databases.
use[database name];//e.g. use104_db;
Create database tables.
createtablestudent(idint,namevarchar(20));
View table information.
desc[table name];//e.g. descstudent;
Inserts data into the specified table.
insertinto[table name](id,name)values(1, “ZhangSan”);//e.g. insertintostudent(id,name)values(1, “ZhangSan”);//also you can also omit attribute fields if it’s a full-attribute insertion//e.g.: insertintostudentvalues(2, “Li Si”);//If the attribute field is explicitly specified the insertion field following it must match it //for example: insertintostudent(id)values(3);
Querying the data in a table.
Clear the screen.
systemclear// mysql doesn’t provide a command to clear the screen you can use the command that comes with the system:
Exit the database.
6, SQL classification
l DDL [DataDefinitionlanguage] data definition language, used to maintain the structure of the stored data
representative commands: create, drop, alter.
l DML [DataManipulationLanguage], used to maintain the structure of the stored data
representative commands: create, drop, alter. DataManipulationLanguage】Data Manipulation Language, used to manipulate data
Represents the commands: insert, delete, update.
l DML in a separate DQL, Data Query Language, represented by the command: select.
l DCL【DataControlLanguage], used to maintain the structure of stored data
Represents the commands: create, drop, alter. DataControlLanguage】Data Control Language, mainly responsible for rights management and transaction
Representative commands: grant, revoke, commit.
7, storage engine
Storage engine: database management system how to store data, how to index the stored data and how to update, query data and other technology implementation methods. The core of MySQL is the plug-in storage engine, which supports a variety of storage engines.
麻烦请教各位高手一些MYSQL数据库的问题？
createtableStudent
(
snovarchar(10)notnullprimarykey,
snamevarchar(10),
ssexvarchar(2),
sageint(5),
sdeptvarchar(10)
);
altertableStudentaddScomeDate();
insertintoscvalues(‘08111′,’203’,80);
updateStudentsetsdept=’经济系’wheresno=’李阳’;//多写了一个from
selectSno,GradefromSCwhereCno=’103’orderbyGradedesc;
selectmax(grade),arg(grade)fromSCgroupbyCnohavingCno=’103′;
deletefromSCwhereSno=’05019′;
deletefromStudentwhereSno=’05019′;
select*fromSCwhereGradebetween60and80;
select*fromStudentwheresdept=’计算机系’orssex=’女’;
selectcount(sno)fromStudentgroupbysdepthavingsdept=’计算机’;
MySQL Questions and Answers VIII for Computer Level 2 Exam 2018
MySQL Questions and Answers VIII for Computer Level 2 Exam 2018
Short Answer Questions
1. Create a stored procedure in the database db_test to implement the idea that the name of a message sender in the table content can be modified given the name of that message sender in the table content The e-mail address of the message is modified to a given value.
Explanation:
This can be achieved by entering the following SQL statement in the MySQL command-line client:
mysql>USEdb_test;
Databasechanged
mysql>DELIMITER$$
mysql>CREATEPROCEDUREsp_updateemail(INUSer_nameVARCHAR(50), INe_mailVARCHAR(50))
one>BEGIN
one> UPDATEcontentSETemail=e_mailWHEREusername=user_name;
One>END$$
QueryOK, 0rowsaffected(0.06SeC)
2. Briefly explain the role of cursors in stored procedures.
Explanation: A cursor is a result set that is retrieved by a SELECT statement. After storing the cursor, the application or the user can scroll or browse through the data as required.
3. List two commonly used MySQL client management tools.
Explanation: MySQL command-line client, MySQL graphical administration tool phpAdmin.
4. Please use SELECTINTO…. .OUTFlLE statement to back up all the data in the table content in the database db_test to a file named backupcontent.txt in the BACKUP directory on the C drive, requiring that field values be marked with double quotes if they are characters, that the field values be separated by commas, and that each line end with a question mark.
Reference analysis: This can be achieved by entering the following SQL statement in the MySQL command line client:
mysql>USEdb_test;
Databasechanged
mysql>SELECT*FROMcontent
a>INTOOO.txt file. gt;INTOOUTFILE’C:/BACKUP/backupcontent.txt’
one>FIELDSTERMINATEDBY’,’
one>OPTIONALLYENCLOSEDBY'”‘
one> LINESTERMINATEDBY’?”
QueryOK, 1rowaffected(0.Olsec)
5. Please use the DELETE statement to delete the message in the table content of database db_test with the name of the person who left the message as “MysQL beginner”.
For reference, enter the following SQL statement in the MySQL command line client:
mysql>USEdb-test;
Databasechanged
mysql>DELETEFROMcontent
a>WHERE? gt;WHEREusemame=’MySQL beginner’;
QueryOK, 1rOWaffected(0.09sec)