sql查詢練習

建表語句：

CREATE TABLE students
(sno VARCHAR(3) NOT NULL, 
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL, 
sbirthday DATETIME,
class VARCHAR(5));

CREATE TABLE courses
(cno VARCHAR(5) NOT NULL, 
cname VARCHAR(10) NOT NULL, 
tno VARCHAR(10) NOT NULL);



CREATE TABLE scores 
(sno VARCHAR(3) NOT NULL, 
cno VARCHAR(5) NOT NULL, 
degree NUMERIC(10, 1) NOT NULL);

CREATE TABLE teachers 
(tno VARCHAR(3) NOT NULL, 
tname VARCHAR(4) NOT NULL, tsex VARCHAR(2) NOT NULL, 
tbirthday DATETIME NOT NULL, prof VARCHAR(6), 
depart VARCHAR(10) NOT NULL);

準備數(shù)據(jù)：

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾華' ,'男' ,'1977-09-01',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王麗' ,'女' ,'1976-01-23',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李軍' ,'男' ,'1976-02-20',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陸君' ,'男' ,'1974-06-03',95031);

INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'計算機導論',825);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系統(tǒng)' ,804);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'數(shù)據(jù)電路' ,856);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等數(shù)學' ,100);

INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'李誠','男','1958-12-02','副教授','計算機系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'張旭','男','1969-03-12','講師','電子工程系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'王萍','女','1972-05-05','助教','計算機系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'劉冰','女','1977-08-14','助教','電子工程系');

mysql> show tables;
+-------------------+
| Tables_in_db_demo |
+-------------------+
| courses           |
| scores            |
| students          |
| teachers          |
+-------------------+
4 rows in set (0.00 sec)

題目：
1、查詢Student表中的所有記錄的Sname、Ssex和Class列现恼。

mysql> select distinct Sname,Ssex,Class from students;
+--------+------+-------+
| Sname  | Ssex | Class |
+--------+------+-------+
| 曾華   | 男   | 95033 |
| 匡明   | 男   | 95031 |
| 王麗   | 女   | 95033 |
| 李軍   | 男   | 95033 |
| 王芳   | 女   | 95031 |
| 陸君   | 男   | 95031 |
+--------+------+-------+
6 rows in set (0.00 sec)

2、查詢教師所有的單位即不重復的Depart列黍檩。

mysql> select distinct depart from teachers;
+-----------------+
| depart          |
+-----------------+
| 計算機系        |
| 電子工程系      |
+-----------------+
2 rows in set (0.00 sec)

3叉袍、查詢Student表的所有記錄。

mysql> select * from students;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 108 | 曾華   | 男   | 1977-09-01 00:00:00 | 95033 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 107 | 王麗   | 女   | 1976-01-23 00:00:00 | 95033 |
| 101 | 李軍   | 男   | 1976-02-20 00:00:00 | 95033 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 103 | 陸君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 108 | 曾華   | 男   | 1977-09-01 00:00:00 | 95033 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 107 | 王麗   | 女   | 1976-01-23 00:00:00 | 95033 |
| 101 | 李軍   | 男   | 1976-02-20 00:00:00 | 95033 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 103 | 陸君   | 男   | 1974-06-03 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
12 rows in set (0.00 sec)

4刽酱、查詢Score表中成績在60到80之間的所有記錄喳逛。

mysql> select * from scores where degree between 60 and  80;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-245 |   75.0 |
| 109 | 3-245 |   68.0 |
| 109 | 3-105 |   76.0 |
| 101 | 3-105 |   64.0 |
| 108 | 3-105 |   78.0 |
| 107 | 6-106 |   79.0 |
+-----+-------+--------+
6 rows in set (0.01 sec)

5、查詢Score表中成績?yōu)?5棵里，86或88的記錄润文。

mysql> select * from scores where degree in (85,86,88);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |   86.0 |
| 103 | 3-245 |   86.0 |
| 105 | 3-105 |   88.0 |
| 101 | 6-166 |   85.0 |
+-----+-------+--------+
4 rows in set (0.00 sec)

6、查詢Student表中“95031”班或性別為“女”的同學記錄殿怜。

mysql> select sname,ssex,class from students where class = '95031'  or ssex ='女' group by sname;
+--------+------+-------+
| sname  | ssex | Class |
+--------+------+-------+
| 匡明   | 男   | 95031 |
| 王麗   | 女   | 95033 |
| 王芳   | 女   | 95031 |
| 陸君   | 男   | 95031 |
+--------+------+-------+
4 rows in set (0.00 sec)

7典蝌、以Class降序查詢Student表的所有記錄。

mysql> select sno,sname ,class from students group by sno order by class desc;
+-----+--------+-------+
| sno | sname  | class |
+-----+--------+-------+
| 108 | 曾華   | 95033 |
| 107 | 王麗   | 95033 |
| 101 | 李軍   | 95033 |
| 103 | 陸君   | 95031 |
| 105 | 匡明   | 95031 |
| 109 | 王芳   | 95031 |
+-----+--------+-------+
6 rows in set (0.00 sec)

8头谜、以Cno升序骏掀、Degree降序查詢Score表的所有記錄。

mysql> select cno,degree from scores order by cno,degree desc;
+-------+--------+
| cno   | degree |
+-------+--------+
| 3-105 |   92.0 |
| 3-105 |   91.0 |
| 3-105 |   88.0 |
| 3-105 |   78.0 |
| 3-105 |   76.0 |
| 3-105 |   64.0 |
| 3-245 |   86.0 |
| 3-245 |   86.0 |
| 3-245 |   75.0 |
| 3-245 |   68.0 |
| 6-106 |   79.0 |
| 6-166 |   85.0 |
| 6-166 |   81.0 |
+-------+--------+
13 rows in set (0.00 sec)

9柱告、查詢“95031”班的學生人數(shù)截驮。


mysql> select count(*) from students where class = 95031;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

10、查詢Score表中的最高分的
學生學號和課程號际度。

mysql> select max(degree) from scores;
+-------------+
| max(degree) |
+-------------+
|        92.0 |
+-------------+
1 row in set (0.00 sec)

mysql> select * from scores where degree = (select max(degree) from scores);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |   92.0 |
+-----+-------+--------+
1 row in set (0.00 sec)

mysql>

11葵袭、查詢‘3-105’號課程的平均分。

mysql> select avg(DEGREE) from SCORES  where cno = '3-105';
+-------------+
| avg(DEGREE) |
+-------------+
|    81.50000 |
+-------------+
1 row in set (0.00 sec)

12乖菱、查詢Score表中至少有5名學生選修的并以3開頭的課程的平均分數(shù)坡锡。

mysql> select cno ,avg(degree) from scores where cno like '3%' group by cno having count(1)>5;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |    81.50000 |
+-------+-------------+
1 row in set (0.00 sec)

13、查詢最低分大于70窒所，最高分小于90的Sno列娜氏。

mysql> select sc.sno from scores sc,students st where st.sno=sc.sno group by sc.sno having min(sc.degree)>70 and max(sc.degree)<90;
+-----+
| sno |
+-----+
| 105 |
| 108 |
+-----+
2 rows in set (0.00 sec)

14、查詢所有學生的Sname墩新、Cno和Degree列。

mysql>  select sname,cno,degree from students as std left join scores sc on std.sno = sc.sno group by sname;
+--------+-------+--------+
| sname  | cno   | degree |
+--------+-------+--------+
| 匡明   | 3-245 |   75.0 |
| 曾華   | 3-105 |   78.0 |
| 李軍   | 3-105 |   64.0 |
| 王麗   | 3-105 |   91.0 |
| 王芳   | 3-245 |   68.0 |
| 陸君   | 3-245 |   86.0 |
+--------+-------+--------+
6 rows in set (0.00 sec)

15窟坐、查詢所有學生的Sno海渊、Cname和Degree列绵疲。

mysql> select students.sno,courses.cname ,scores.degree from students ,courses,scores where students.sno =  scores.sno and courses.cno = scores.cno group by students.sno;
+-----+-----------------+--------+
| sno | cname           | degree |
+-----+-----------------+--------+
| 101 | 計算機導論      |   64.0 |
| 103 | 操作系統(tǒng)        |   86.0 |
| 105 | 操作系統(tǒng)        |   75.0 |
| 107 | 計算機導論      |   91.0 |
| 108 | 計算機導論      |   78.0 |
| 109 | 操作系統(tǒng)        |   68.0 |
+-----+-----------------+--------+
6 rows in set (0.00 sec)

16、查詢所有學生的Sname臣疑、Cname和Degree列盔憨。

mysql> select students.sname,courses.cname ,scores.degree from students ,courses,scores where students.sno =  scores.sno and courses.cno = scores.cno group by students.sno;
+--------+-----------------+--------+
| sname  | cname           | degree |
+--------+-----------------+--------+
| 李軍   | 計算機導論      |   64.0 |
| 陸君   | 操作系統(tǒng)        |   86.0 |
| 匡明   | 操作系統(tǒng)        |   75.0 |
| 王麗   | 計算機導論      |   91.0 |
| 曾華   | 計算機導論      |   78.0 |
| 王芳   | 操作系統(tǒng)        |   68.0 |
+--------+-----------------+--------+
6 rows in set (0.00 sec)

17、查詢“95033”班所選課程的平均分讯沈。

mysql> select cr.cno,cr.cname,avg(sc.degree) from courses cr,scores sc,students st where  st.sno=sc.sno and st.class='95033' and sc.cno=cr.cno group by cr.cno,cr.cname;
+-------+-----------------+----------------+
| cno   | cname           | avg(sc.degree) |
+-------+-----------------+----------------+
| 3-105 | 計算機導論      |       77.66667 |
| 6-166 | 數(shù)據(jù)電路        |       83.00000 |
+-------+-----------------+----------------+
2 rows in set (0.00 sec)

18郁岩、假設使用如下命令建立了一個grade表：

create table grade(low int(3),upp int(3),rank char(1));

insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
commit;

現(xiàn)查詢所有同學的Sno、Cno和rank列缺狠。


select sno from scores inner join students on scores.sno = students.sno;

select * from students inner join grade where students.

19问慎、查詢選修“3-105”課程的成績高于“109”號同學成績的所有同學的記錄。

mysql> select  distinct st.sno,st.sname,sc.degree from students st,scores sc where sc.cno='3-105' and sc.degree> (select degree from scores where cno='3-105'and sno='109') and st.sno=sc.sno;;
+-----+--------+--------+
| sno | sname  | degree |
+-----+--------+--------+
| 108 | 曾華   |   78.0 |
| 105 | 匡明   |   88.0 |
| 107 | 王麗   |   91.0 |
| 103 | 陸君   |   92.0 |
+-----+--------+--------+
4 rows in set (0.00 sec)

20挤茄、查詢score中選學一門以上課程的同學中分數(shù)為非最高分成績的記錄如叼。

mysql> select * from scores where degree < (select max(degree) from scores) having count(*) >1;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |   86.0 |
+-----+-------+--------+
1 row in set (0.00 sec)

21、查詢成績高于學號為“109”穷劈、課程號為“3-105”的成績的所有記錄笼恰。

mysql> select distinct * from scores where degree > all (select degree from scores where sno = '109' and cno = '3-105');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |   86.0 |
| 103 | 3-105 |   92.0 |
| 105 | 3-105 |   88.0 |
| 107 | 3-105 |   91.0 |
| 108 | 3-105 |   78.0 |
| 101 | 6-166 |   85.0 |
| 107 | 6-106 |   79.0 |
| 108 | 6-166 |   81.0 |
+-----+-------+--------+
8 rows in set (0.00 sec)

22、查詢和學號為108的同學同年出生的所有學生的Sno歇终、Sname和Sbirthday列社证。

mysql> select distinct sno,sname ,sbirthday from students where sbirthday = (select distinct sbirthday from students where sno = 108);
+-----+--------+---------------------+
| sno | sname  | sbirthday           |
+-----+--------+---------------------+
| 108 | 曾華   | 1977-09-01 00:00:00 |
+-----+--------+---------------------+
1 row in set (0.00 sec)

23、查詢“張旭“教師任課的學生成績评凝。

mysql> select distinct * from scores  where cno = (select distinct cno from  courses inner join teachers  where  teachers.tno = courses.tno and teachers.tname = '張旭');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 6-166 |   85.0 |
| 108 | 6-166 |   81.0 |
+-----+-------+--------+
2 rows in set (0.00 sec)

24追葡、查詢選修某課程的同學人數(shù)多于5人的教師姓名。

mysql> select tname from teachers where tno in (select tno from courses where cno in (select cno from scores group by cno having COUNT(*)>5));
+--------+
| tname  |
+--------+
| 王萍   |
+--------+
1 row in set (0.00 sec)

25肥哎、查詢95033班和95031班全體學生的記錄辽俗。

mysql> select * from students where class = '95033' or class = '95031';
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 108 | 曾華   | 男   | 1977-09-01 00:00:00 | 95033 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 107 | 王麗   | 女   | 1976-01-23 00:00:00 | 95033 |
| 101 | 李軍   | 男   | 1976-02-20 00:00:00 | 95033 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 103 | 陸君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 108 | 曾華   | 男   | 1977-09-01 00:00:00 | 95033 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 107 | 王麗   | 女   | 1976-01-23 00:00:00 | 95033 |
| 101 | 李軍   | 男   | 1976-02-20 00:00:00 | 95033 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 103 | 陸君   | 男   | 1974-06-03 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
12 rows in set (0.00 sec)

26、查詢存在有85分以上成績的課程Cno.

mysql> select cno from scores where degree > 85;
+-------+
| cno   |
+-------+
| 3-245 |
| 3-245 |
| 3-105 |
| 3-105 |
| 3-105 |
+-------+
5 rows in set (0.00 sec)

27篡诽、查詢出“計算機系“教師所教課程的成績表崖飘。

mysql> select * from scores where cno in (select cno from courses where tno in (select tno from teachers where depart='計算機系'));
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |   86.0 |
| 103 | 3-245 |   86.0 |
| 105 | 3-245 |   75.0 |
| 109 | 3-245 |   68.0 |
| 103 | 3-105 |   92.0 |
| 105 | 3-105 |   88.0 |
| 109 | 3-105 |   76.0 |
| 101 | 3-105 |   64.0 |
| 107 | 3-105 |   91.0 |
| 108 | 3-105 |   78.0 |
+-----+-------+--------+
10 rows in set (0.00 sec)

28、查詢“計算機系”與“電子工程系“不同職稱的教師的Tname和Prof杈女。

mysql> select * from teachers where depart = '計算機系' or depart = '電子工程系' group by prof ,tname;
+-----+--------+------+---------------------+-----------+-----------------+
| tno | tname  | tsex | tbirthday           | prof      | depart          |
+-----+--------+------+---------------------+-----------+-----------------+
| 804 | 李誠   | 男   | 1958-12-02 00:00:00 | 副教授    | 計算機系        |
| 831 | 劉冰   | 女   | 1977-08-14 00:00:00 | 助教      | 電子工程系      |
| 825 | 王萍   | 女   | 1972-05-05 00:00:00 | 助教      | 計算機系        |
| 856 | 張旭   | 男   | 1969-03-12 00:00:00 | 講師      | 電子工程系      |
+-----+--------+------+---------------------+-----------+-----------------+
4 rows in set (0.00 sec)

29朱浴、查詢選修編號為“3-105“課程且成績至少高于選修編號為“3-245”的同學的Cno、Sno和
Degree,并按Degree從高到低次序排序达椰。

mysql> select * from scores where cno='3-105' and degree > all (select degree  from scores where cno='3-245') order by degree desc;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |   92.0 |
| 107 | 3-105 |   91.0 |
| 105 | 3-105 |   88.0 |
+-----+-------+--------+
3 rows in set (0.00 sec)

30翰蠢、查詢選修編號為“3-105”且成績高于選修編號為“3-245”課程的同學的Cno、Sno和
Degree.

mysql> select * from scores where cno='3-105' and degree > all (select degree from scores where cno='3-245');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |   92.0 |
| 105 | 3-105 |   88.0 |
| 107 | 3-105 |   91.0 |
+-----+-------+--------+
3 rows in set (0.00 sec)

31啰劲、查詢所有教師和同學的name梁沧、sex和birthday.

mysql> select distinct sname as name ,ssex as sex ,sbirthday as birthday from students union select distinct tname as name ,tsex as sex ,tbirthday as birthday from teachers ;
+--------+-----+---------------------+
| name   | sex | birthday            |
+--------+-----+---------------------+
| 曾華   | 男  | 1977-09-01 00:00:00 |
| 匡明   | 男  | 1975-10-02 00:00:00 |
| 王麗   | 女  | 1976-01-23 00:00:00 |
| 李軍   | 男  | 1976-02-20 00:00:00 |
| 王芳   | 女  | 1975-02-10 00:00:00 |
| 陸君   | 男  | 1974-06-03 00:00:00 |
| 李誠   | 男  | 1958-12-02 00:00:00 |
| 張旭   | 男  | 1969-03-12 00:00:00 |
| 王萍   | 女  | 1972-05-05 00:00:00 |
| 劉冰   | 女  | 1977-08-14 00:00:00 |
+--------+-----+---------------------+
10 rows in set (0.00 sec)

32、查詢所有“女”教師和“女”同學的name蝇裤、sex和birthday.

mysql> select distinct sname as name,ssex as sex,sbirthday as birthday from students where ssex='女' union select distinct tname as name,tsex as sex,tbirthday as birthday from teachers where tsex='女';
+--------+-----+---------------------+
| name   | sex | birthday            |
+--------+-----+---------------------+
| 王麗   | 女  | 1976-01-23 00:00:00 |
| 王芳   | 女  | 1975-02-10 00:00:00 |
| 王萍   | 女  | 1972-05-05 00:00:00 |
| 劉冰   | 女  | 1977-08-14 00:00:00 |
+--------+-----+---------------------+
4 rows in set (0.00 sec)

33廷支、查詢成績比該課程平均成績低的同學的成績表频鉴。

mysql> select * from scores sc where sc.degree between 0 and(select avg(degree) from scores sc2 where sc.cno=sc2.cno) ;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-245 |   75.0 |
| 109 | 3-245 |   68.0 |
| 109 | 3-105 |   76.0 |
| 101 | 3-105 |   64.0 |
| 108 | 3-105 |   78.0 |
| 107 | 6-106 |   79.0 |
| 108 | 6-166 |   81.0 |
+-----+-------+--------+
7 rows in set (0.00 sec)

34、查詢所有任課教師的Tname和Depart.

mysql> select tname,depart from teachers inner join courses on courses.tno = teachers.tno group by tname;
+--------+-----------------+
| tname  | depart          |
+--------+-----------------+
| 張旭   | 電子工程系      |
| 李誠   | 計算機系        |
| 王萍   | 計算機系        |
+--------+-----------------+
3 rows in set (0.00 sec)

35 查詢所有未講課的教師的Tname和Depart.

mysql> select tname,depart from teachers where tno not in  (select teachers.tno from teachers inner join courses on courses.tno = teachers.tno);
+--------+-----------------+
| tname  | depart          |
+--------+-----------------+
| 劉冰   | 電子工程系      |
+--------+-----------------+
1 row in set (0.00 sec)

36恋拍、查詢至少有2名男生的班號垛孔。

mysql> select class from students where ssex = '男' group by class having count(sno)>=2;
+-------+
| class |
+-------+
| 95031 |
| 95033 |
+-------+
2 rows in set (0.00 sec)

37、查詢Student表中不姓“王”的同學記錄施敢。

mysql> select * from students where sname not like '王%' group by sname;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 108 | 曾華   | 男   | 1977-09-01 00:00:00 | 95033 |
| 101 | 李軍   | 男   | 1976-02-20 00:00:00 | 95033 |
| 103 | 陸君   | 男   | 1974-06-03 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
4 rows in set (0.00 sec)

38周荐、查詢Student表中每個學生的姓名和年齡。
參考

mysql> SELECT sname, ROUND(DATEDIFF(CURDATE(), sbirthday)/365.2422) as age from students group by sname;
+--------+------+
| sname  | age  |
+--------+------+
| 匡明   |   43 |
| 曾華   |   41 |
| 李軍   |   42 |
| 王麗   |   42 |
| 王芳   |   43 |
| 陸君   |   44 |
+--------+------+
6 rows in set (0.00 sec)

39僵娃、查詢Student表中最大和最小的sbirthday日期值概作。

mysql> select max(sbirthday) as maxTime ,  min(sbirthday) as minTime  from students;
+---------------------+---------------------+
| maxTime             | minTime             |
+---------------------+---------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

40谎替、以班號和年齡從大到小的順序查詢Student表中的全部記錄爪膊。

mysql> select * from students  order by class desc , sbirthday asc;
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 107 | 王麗   | 女   | 1976-01-23 00:00:00 | 95033 |
| 107 | 王麗   | 女   | 1976-01-23 00:00:00 | 95033 |
| 101 | 李軍   | 男   | 1976-02-20 00:00:00 | 95033 |
| 101 | 李軍   | 男   | 1976-02-20 00:00:00 | 95033 |
| 108 | 曾華   | 男   | 1977-09-01 00:00:00 | 95033 |
| 108 | 曾華   | 男   | 1977-09-01 00:00:00 | 95033 |
| 103 | 陸君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 103 | 陸君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
+-----+--------+------+---------------------+-------+
12 rows in set (0.00 sec)

41杨箭、查詢“男”教師及其所上的課程缩抡。

mysql> select t1.tname ,cr.cname from teachers as t1  left join courses as cr on t1.tno = cr.tno  where tsex = '男' group by t1.tname ;
+--------+--------------+
| tname  | cname        |
+--------+--------------+
| 張旭   | 數(shù)據(jù)電路     |
| 李誠   | 操作系統(tǒng)     |
+--------+--------------+
2 rows in set (0.00 sec)

42昌讲、查詢最高分同學的Sno拐辽、Cno和Degree列焚碌。

mysql> select students.sno,courses.cno,scores.degree from students , courses , scores where scores.degree = (select max(degree) from scores)  group by students.sname ;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-105 |   92.0 |
| 108 | 3-105 |   92.0 |
| 101 | 3-105 |   92.0 |
| 107 | 3-105 |   92.0 |
| 109 | 3-105 |   92.0 |
| 103 | 3-105 |   92.0 |
+-----+-------+--------+
6 rows in set (0.00 sec)

43媒鼓、查詢和“李軍”同性別的所有同學的Sname.

mysql> select sname from students where ssex = (select ssex from students where sname = '李軍' limit 1);
+--------+
| sname  |
+--------+
| 曾華   |
| 匡明   |
| 李軍   |
| 陸君   |
| 曾華   |
| 匡明   |
| 李軍   |
| 陸君   |
+--------+
8 rows in set (0.00 sec)

44垃僚、查詢和“李軍”同性別并同班的同學Sname.

mysql> select sname from students where ssex = (select ssex from students where sname = '李軍' limit 1) and class = (select class from students where sname = '李軍' limit 1) group by sname;
+--------+
| sname  |
+--------+
| 曾華   |
| 李軍   |
+--------+
2 rows in set (0.00 sec)

45集绰、查詢所有選修“計算機導論”課程的“男”同學的成績表

mysql> select * from students, courses, scores where courses.cname = '計算機導論' and students.ssex = '男' group by sname;
+-----+--------+------+---------------------+-------+-------+-----------------+-----+-----+-------+--------+
| sno | sname  | ssex | sbirthday           | class | cno   | cname           | tno | sno | cno   | degree |
+-----+--------+------+---------------------+-------+-------+-----------------+-----+-----+-------+--------+
| 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 | 3-105 | 計算機導論      | 825 | 103 | 3-245 |   86.0 |
| 108 | 曾華   | 男   | 1977-09-01 00:00:00 | 95033 | 3-105 | 計算機導論      | 825 | 103 | 3-245 |   86.0 |
| 101 | 李軍   | 男   | 1976-02-20 00:00:00 | 95033 | 3-105 | 計算機導論      | 825 | 103 | 3-245 |   86.0 |
| 103 | 陸君   | 男   | 1974-06-03 00:00:00 | 95031 | 3-105 | 計算機導論      | 825 | 103 | 3-245 |   86.0 |
+-----+--------+------+---------------------+-------+-------+-----------------+-----+-----+-------+--------+
4 rows in set (0.00 sec)

最后編輯于：2018.06.13 12:21:00

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個濱河市谆棺，隨后出現(xiàn)的幾起案子栽燕，更是在濱河造成了極大的恐慌，老刑警劉巖改淑，帶你破解...
沈念sama閱讀 211,348評論 6贊 491
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件碍岔，死亡現(xiàn)場離奇詭異，居然都是意外死亡朵夏，警方通過查閱死者的電腦和手機蔼啦，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 90,122評論 2贊 385
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來仰猖，“玉大人捏肢，你說我怎么就攤上這事〖⑶郑” “怎么了鸵赫？”我有些...
開封第一講書人閱讀 156,936評論 0贊 347
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長躏升。經(jīng)常有香客問我辩棒，道長，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 56,427評論 1贊 283
?港島之戀（遺憾婚禮）
正文為了忘掉前任盗温，我火速辦了婚禮藕赞，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘卖局。我一直安慰自己，他們只是感情好双霍，可當我...
茶點故事閱讀 65,467評論 6贊 385
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布砚偶。她就那樣靜靜地躺著，像睡著了一般洒闸。火紅的嫁衣襯著肌膚如雪染坯。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 49,785評論 1贊 290
城市分裂傳說
那天丘逸，我揣著相機與錄音单鹿，去河邊找鬼。笑死深纲，一個胖子當著我的面吹牛仲锄，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播湃鹊，決...
沈念sama閱讀 38,931評論 3贊 406
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼儒喊，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了币呵？” 一聲冷哼從身側(cè)響起怀愧，我...
開封第一講書人閱讀 37,696評論 0贊 266
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎余赢，沒想到半個月后芯义，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 44,141評論 1贊 303
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡妻柒，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 36,483評論 2贊 327
?白月光啟示錄
正文我和宋清朗相戀三年扛拨，在試婚紗的時候發(fā)現(xiàn)自己被綠了。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片蛤奢。...
茶點故事閱讀 38,625評論 1贊 340
活死人
序言：一個原本活蹦亂跳的男人離奇死亡鬼癣，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出啤贩，到底是詐尸還是另有隱情待秃，我是刑警寧澤，帶...
沈念sama閱讀 34,291評論 4贊 329
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布痹屹，位于F島的核電站章郁，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜暖庄，卻給世界環(huán)境...
茶點故事閱讀 39,892評論 3贊 312
男人毒藥：我在死后第九天來索命
文/蒙蒙一聊替、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧培廓，春花似錦惹悄、人聲如沸。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,741評論 0贊 21
一樁弒父案泣港，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽。三九已至价匠，卻和暖如春当纱，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背踩窖。一陣腳步聲響...
開封第一講書人閱讀 31,977評論 1贊 265
情欲美人皮
我被黑心中介騙來泰國打工坡氯，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留，地道東北人洋腮。一個月前我還...
沈念sama閱讀 46,324評論 2贊 360
代替公主和親
正文我出身青樓箫柳，卻偏偏與公主長得像，于是被迫代替她去往敵國和親徐矩。傳聞我的和親對象是個殘疾皇子滞时，可洞房花燭夜當晚...
茶點故事閱讀 43,492評論 2贊 348

sql查詢練習

推薦閱讀更多精彩內(nèi)容