[database] MySQL single table query

Practice three Single table query

One 、 Table structure modification

1． Add... To the database created in Exercise 2 Teacher surface , The structure is as follows ：

Field name	data type	length	precision	Decimal digit	Whether to allow Null value	explain
Tno	Char	3	0	0	no	Teacher number , Main code
Tname	Varchar	8	0	0	yes	Teacher's name
Tsex	Char	2	0	0	yes	Gender , Value ： Male or female
Tbirthday	Date	8	0	0	yes	Date of birth
Tdept	Char	16	0	0	yes	Department

 

Here I found that I didn't give the field `Tno` Add main code

Add... Here ：

 

And the primary key will be clearly marked when displaying the table Tno

 

2． Add... To the database created in Exercise 2 Teaching surface , The table structure is as follows ：

Teaching surface （ Teaching schedule ） The table structure

Field name	data type	length	precision	Decimal digit	Whether to allow Null value	explain
Cno	Char	5	0	0	no	Course no. , Outer code
Tno	Char	3	0	0	no	Teacher number , Outer code
Cterm	Tinyint	1	0	0	yes	1-8 Between

 

Here I found that I didn't give `Cno` and `Tno` Add external code ：

 

Here I give Cno and Tno Add foreign code ：

 

When I first told teaching In the table Cno When adding a foreign code , Find the name of the outer code cn Repetitive , I tried my best to find , Originally in this database sc In the table Cno Fields are also added to the main table course Medium Cno Added foreign keys , And the name of the foreign key is also called cn, So the foreign key name here cn Repeat, just put cn Just change your name

 

 

about Tno The same is true for foreign keys ：

 

But the meaning of this topic seems to be to add primary keys and foreign keys when creating tables , therefore ------ Put these two new forms drop 了 QAQ

 

When I want to delete a table teacher I will make a mistake , The reason is that teacher yes teaching In the table Tno The main table bound by the foreign key of the field , So we need to delete the slave table first teaching,（ After deleting the table , The foreign keys in the table will be deleted naturally ）, Next we delete teacher Watch to succeed

The following is the original meaning of the question ： Create the primary key and foreign key when creating the table ：

 

 

 

Add foreign keys when creating tables

 

3． Enter the following data records into the above two tables

Cno	Tno	Cterm
C01	101	2
C02	102	1
C03	201	3
C04	202	4

Teachers list Teacher                          Teaching schedule Teaching

Tno	Tname	Tsex	Tbirthday	Tdept
101	Li Xin	male	1977-01-12	CS
102	Qian Jun	Woman	1978-06-04	CS
201	Wang Xiaohua	Woman	1979-12-23	IS
202	Xiao Qing Zhang	male	1978-08-25	IS

Go to teacher Add data to the table ：

 

Go to teaching Add data to the table ：

 

 

Since then, the table has been created ：

 

Two 、 Complete the following query （ Write the code in the space below ）

1． Query all boys' information .

 

2． Query age is greater than 20 Year old girl's student number and name .

 

Pay attention to year() Function to obtain Sbrithday Year of the field

3． Query all teachers Tname、Tdept.

 

4． Inquire about “ Electronic Commerce ” Name of the student majoring in 、 Gender and date of birth .

 

5． The query score is lower than 90 Student number and class number , And in descending order .

 

Parameters ASC Means sort in ascending order ,DESC Indicates sort in descending order ; By default , according to ASC Sort by . Usually ,ORDER BY Clause in the whole SELECT At the end of the statement .

6． Inquire about Student All the family names in the table .

 

How to solve ？---- Can be in Sdept The field is preceded by distinct

 

7． Inquire about “C01” The first semester of the course .

 

8． The results are in 80 Points to the 90 Student number and class number between .

 

9． Count the number of courses selected by students .

 

But query a sc surface ：

 

Duplicate data , have access to distinct

 

10． The query result is 77,88 or 99 The record of

 

According to our table above sc There are no three records in the table

11． Calculation “C01” The average grade of the course .

 

12． Output the student number of students with grades .

 

13． Check all last names “ Liu ” Student information .

 

% Used to match strings of any length . for example , character string “a%” Match with characters a Start a string of arbitrary length ;”%a” Match with characters a end Any length of string ;”%a%” Match contains characters a1 A string of any length

14． Count and output the number of students in each department . Group by Department

 

15． Check the elective course “C03” The student number and grades of the students in the course , The query results are arranged in descending order of scores .

 

16． Query each course number and the corresponding number of selected courses , And alias the number of candidates as “ The number of ”.

 

But I found that the number of people in the field is not displayed

 

17． Count the number of selected courses and the highest score of each course , And alias the number of students and the highest score as “ The number of ”、“ The highest ”.

 

18． Count the number of courses selected by each student and the total score of the exam , And alias the number of courses selected and the total score as “ Number of gates ”、“ Total score ”, And arranged in descending order according to the number of courses selected .

 

1. Thinking questions

1． Can aggregate functions be used directly in ：SELECT Clause 、HAVING Clause 、WHERE Clause 、GROUP BY clause ？

Sure

2． keyword ALL and DISTINCT What are the different meanings ？

ALL Decorated fields can have duplicate values （ And the default is ALL）

DISTINCT The decorated field will not have duplicate values （ if necessary , Need to add DSITINCT）

3．SELECT There are several wildcards in the statement ？ What are the meanings ？

% wildcard ：% Used to match strings of any length . for example , character string “a%” Match with characters a Start a string of arbitrary length ;”%a” Match with characters a end Any length of string ;”%a%” Match contains characters a1 A string of any length

_ wildcard ： The underscore wildcard matches only a single character , If you want to match multiple characters , Multiple underscore wildcards need to be used consecutively . for example , character string “ab_” Match with string “ab” Start length is 3 String , Such as abc、abp wait ; character string “a__d” Match in character “a” and “d” A string containing two characters between , Such as "abcd"、"atud" wait .

1. utilize BETWEEN…AND Operator indicates whether the range of data can be represented by other methods ？ How to express ？

for example where age between 15 and 18 Can where age>=15 and age<=18