目錄

一 、題目與設計

二? 、運行結果

---

一 、題目與設計

SHOW DATABASES; drop DATABASE student;-- **********創建庫************************ create DATABASE student default charset = utf8;-- SHOW tables; use student; show tables; -- show create DATABASE student; -- select DATABASE() -- alter table student change ssno sno varchar(3) ;-- **********創建列表*********************** create table if not exists student(sno varchar(3) primary key default 000 comment "學號",sname varchar(4) not null comment "姓名",ssex varchar(2) default 0 comment "性別",sbirthday datetime comment "生日",class varchar(5) not null comment "班級" ); -- -- -- -- alter table student add ssex enum('男','女') default '男'; alter table student modify ssex enum('男','女') default '男'; desc student;create table if not exists course(cno varchar(5) primary key,cname varchar(10) not null,tno varchar(10) not null );create table if not exists grade(sno varchar(3) ,cno varchar(5) ,degree numeric(3) not null,primary key(sno,cno) );create table if not exists teacher(tno varchar(3) primary key,tname varchar(10) not null,tsex varchar(2) ,tbirthday datetime,prof varchar(6) not null,depart varchar(10) not null );create table if not exists rank(down numeric(3) not null,up numeric(3) not null,rank varchar(1) primary key );-- ***********插入列表信息***********************INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (108 ,'曾華','女' , '1977-09-01',95033); INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (105 ,'匡明' ,'男' , '1975-10-02',95031); INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (107 ,'王麗' ,'女' , '1976-01-23',95033); INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (101 ,'李軍' ,'男' , '1976-02-20',95033); INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (109 ,'王芳' ,'女' , '1975-02-10',95031); INSERT INTO student (sno,sname,ssex,sbirthday,class) VALUES (103 ,'陸君' ,'男' , '1974-06-03',95031);-- select * from student;INSERT INTO course(cno,cname,tno) VALUES ('3-105' ,'計算機導論', '825'); INSERT INTO course(cno,cname,tno) VALUES ('3-245' ,'操作系統' , '804'); INSERT INTO course(cno,cname,tno) VALUES ('6-166' ,'數據電路' , '856'); INSERT INTO course(cno,cname,tno) VALUES ('9-888' ,'高等數學' , '100');-- select * from course;INSERT INTO grade(sno,cno,degree)VALUES (103,'3-245',86); INSERT INTO grade(sno,cno,degree)VALUES (105,'3-245',75); INSERT INTO grade(sno,cno,degree)VALUES (109,'3-245',68); INSERT INTO grade(sno,cno,degree)VALUES (103,'3-105',92); INSERT INTO grade(sno,cno,degree)VALUES (105,'3-105',88); INSERT INTO grade(sno,cno,degree)VALUES (109,'3-105',76); INSERT INTO grade(sno,cno,degree)VALUES (101,'3-105',64); INSERT INTO grade(sno,cno,degree)VALUES (107,'3-105',91); INSERT INTO grade(sno,cno,degree)VALUES (108,'3-105',78); INSERT INTO grade(sno,cno,degree)VALUES (101,'6-166',85); INSERT INTO grade(sno,cno,degree)VALUES (107,'6-106',79); INSERT INTO grade(sno,cno,degree)VALUES (108,'6-166',81);-- select * from grade;INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) VALUES (804,'李誠','男','1958-12-02','副教授','計算機系'); INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) VALUES (856,'張旭','男','1969-03-12','講師','電子工程系'); INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) VALUES (825,'王萍','女','1972-05-05','助教','計算機系'); INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart) VALUES (831,'劉冰','女','1977-08-14','助教','電子工程系');-- select * from teacher;insert into rank(down,up,rank) values(90,100,'A'); insert into rank(down,up,rank) values(80,89,'B'); insert into rank(down,up,rank) values(70,79,'C'); insert into rank(down,up,rank) values(60,69,'D'); insert into rank(down,up,rank) values(0,59,'E');-- select * from rank;-- 1、 查詢Student表中的所有記錄的Sname、Ssex和Class列。 select sname,ssex,class from student;-- 2、 查詢教師所有的單位即不重復的Depart列。 select distinct depart from teacher ;-- 3、 查詢Student表的所有記錄。 select * from student;-- 4、 查詢Grade表中成績在60到80之間的所有記錄。 select * from grade where degree>=60 and degree<=80 ;-- 5、 查詢Grade表中成績為85，86或88的記錄。 select * from grade where degree=85 or degree=86 or degree=88;-- 6、 查詢Student表中“95031”班或性別為“女”的同學記錄。 select * from student where class = 95031 or ssex = '女';-- 7、 以Class降序查詢Student表的所有記錄。 select * from student order by class desc;-- 8、 以Cno升序、Degree降序查詢Grade表的所有記錄。 select * from grade order by cno asc,degree desc;-- 9、 查詢“95031”班的學生人數。 select count(*) from student where class=95031; -- 10、查詢Grade表中的最高分的學生學號和課程號。 -- select sno,cno from grade where degree=max(degree) ; select max(degree), sno,cno from grade ;-- 11、查詢‘3-105’號課程的平均分。 select avg(degree) from grade where cno='3-105';-- 12、查詢Grade表中至少有5名學生選修的并以3開頭的課程的平均分數。 select cno, avg(degree) from grade where cno like '3%' group by cno having count(*)>5;-- 13、查詢最低分大于70，最高分小于90的Sno列。 select sno from grade where degree>70 and degree<90 ;-- 14、查詢所有學生的Sname、Cno和Degree列。 select sname,cno,degree from student s,grade g where s.sno = g.sno;select sname,cno,degree from student s left outer join grade g on s.sno = g.sno;-- 15、查詢所有學生的Sno、Cname和Degree列。 select sno, cname, degree from course c, grade g where c.cno = g.cno;select sno, cname, degree from course c left outer join grade g on c.cno = g.cno;-- 16、查詢所有學生的Sname、Cname和Degree列。 select sname, cname, degree from (student s inner join grade g on s.sno = g.sno)inner join course c on c.cno = g.cno;-- 17、查詢“95033”班所選課程的平均分。 select avg(degree) from grade g where sno in (select sno from student s where class = '95033');select cno , avg(degree) from grade g inner join student s on g.sno = s.snowhere class = '95033' group by cno ;-- 18、查詢選修課成績為A等的學生信息 select * from student s inner join grade g on s.sno = g.sno where degree>=(select down from rank where rank = 'A')and degree<=(select up from rank where rank = 'A');-- 19、查詢選修“3-105”課程的成績高于“109”號同學成績的所有同學的記錄。 select * from student where sno in (select sno from grade where cno = '3-105' and degree >= (select degree from grade where sno = 109 and cno = '3-105'));select * from student s join grade g on s.sno = g.sno where cno= '3-105' and degree >= (select degree from grade where sno = 109 and cno = '3-105');-- 20、查詢grade中選學一門以上課程的同學中分數為非最高分成績的記錄。 select g.sno, cno, degree ,max_dg from grade g inner join (select sno, max(degree) max_dg from grade group by sno having count(*)>1 ) tmp on g.sno = tmp.snowhere degree != max_dg ;-- 21、查詢成績高于學號為“109”、課程號為“3-105”的成績的所有記錄。 select * from student s join grade g on g.cno = '3-105' where degree > (select degree from student s join grade g on s.sno = g.sno where g.cno = '3-105' and s.sno = '109');-- 22、查詢和學號為108的同學同年出生的所有學生的Sno、Sname和Sbirthday列。 select sno, sname, sbirthday from student where year(sbirthday) = (select year(sbirthday) from student where sno = '108');-- 23、查詢“張旭“教師任課的學生成績。 select sno, degree from grade where cno = (select cno from course where tno = (select tno from teacher where tname = '張旭'));select sno, degree from grade g inner join course c on g.cno = c.cno where c.tno = (select t.tno from teacher t where t.tname = '張旭') ; -- 24、查詢選修某課程的同學人數多于5人的教師姓名。 select tname from teacher t where t.tno = (select c.tno from course c where c.cno in (select g.cno from grade g group by g.cno having count(*)>5));select t.tname from teacher t inner join course c on t.tno = c.tno where c.cno in (select g.cno from grade g group by g.cno having count(*)>5) ;-- 25、查詢95033班和95031班全體學生的記錄。 select * from student s left outer join grade g on s.sno = g.sno left outer join course c on g.cno = c.cnoleft outer join teacher t on c.tno = t.tnowhere s.class in ('95033','95031') ;-- 26、查詢存在有85分以上成績的課程Cno. select cno from grade g where degree > 85;-- 27、查詢出“計算機系“教師所教課程的成績表。 select * from grade g where g.cno in (select c.cno from course c where c.tno in (select t.tno from teacher t where t.depart = '計算機系')); select * from grade g inner join course c on g.cno = c.cnowhere c.tno in (select t.tno from teacher t where t.depart = '計算機系'); -- 28、查詢“計算機系”與“電子工程系“不同職稱的教師的Tname和Prof。 select tname , prof from teacher where prof not in (select t.prof from teacher t where t.depart = '電子工程系'); -- 29、查詢選修編號為“3-105“課程且成績至少高于選修編號為“3-245”的同學的Cno、Sno和Degree,并按Degree從高到低次序排序。 select g1.sno, g1.cno, g1.degree from grade g1 inner join grade g2 on g1.cno = '3-105' and g2.cno = '3-245'where g1.sno = g2.sno and g1.degree >= g2.degree order by g1.degree asc ;-- 30、查詢選修編號為“3-105”且成績高于選修編號為“3-245”課程的同學的Cno、Sno和Degree. select g1.sno, g1.cno, g1.degree from grade g1 inner join grade g2 on g1.cno = '3-105' and g2.cno = '3-245'where g1.sno = g2.sno and g1.degree > g2.degree ;-- 31、查詢所有教師和同學的name、sex和birthday. select tname, tsex, tbirthday from teacherunion allselect sname, ssex, sbirthday from student;-- 32、查詢所有“女”教師和“女”同學的name、sex和birthday. select tname, tsex, tbirthday from teacher where tsex = '女'union allselect sname, ssex, sbirthday from student where ssex = '女' ;-- 33、查詢成績比該課程平均成績低的同學的成績表。 select * from grade g1 inner join (select cno, avg(degree) avg_dg from grade group by cno) g2 on g1.cno = g2.cnowhere g1.degree < avg_dg; -- 34、查詢所有任課教師的Tname和Depart. select tname ,depart from course c inner join teacher t on c.tno = t.tno;-- 35 查詢所有未講課的教師的Tname和Depart. select tname, depart from teacher t where t.tno not in (select c.tno from course c ) ;-- 36、查詢至少有2名男生的班號。 select class, count(*) from student where ssex = '男' group by class having count(*)>=2;-- 37、查詢Student表中不姓“王”的同學記錄。 select * from student where sname not like '王%';-- 38、查詢Student表中每個學生的姓名和年齡。 select sname,year(now()) - year(sbirthday) from student; -- 39、查詢Student表中最大和最小的Sbirthday日期值。 select sbirthday from student where sbirthday in (select min(sbirthday) from studentunion select max(sbirthday) from student);-- 40、以班號和年齡從大到小的順序查詢Student表中的全部記錄。 select * from student order by class desc, sbirthday asc;-- 41、查詢“男”教師及其所上的課程。 select * from teacher t inner join course c on t.tno = c.tnowhere tsex = '男';-- 42、查詢最高分同學的Sno、Cno和Degree列。 select * from grade g inner join (select cno, max(degree) max_dg from grade group by cno) tmp on g.cno = tmp.cnowhere g.degree = max_dg;-- 43、查詢和“李軍”同性別的所有同學的Sname. select sname from student where ssex = (select ssex from student where sname = '李軍') ;-- 44、查詢和“李軍”同性別并同班的同學Sname. select sname from student where ssex = (select ssex from student where sname = '李軍') and class = (select class from student where sname = '李軍') ;-- 45、查詢所有選修“計算機導論”課程的“男”同學的成績表 select * from course c inner join grade g on c.cno = g.cnoinner join student s on s.sno = g.snowhere c.cname = '計算機導論' and s.ssex = '男';-- 46、查詢計算機系教師所教課程成績為B等的課程信息 select * from course where cno in (select cno from grade where degree >= (select down from rank where rank = 'B')and degree <= (select up from rank where rank = 'B'))and tno in (select tno from teacher where depart = '計算機系') ;select * from course c inner join grade g on c.cno = g.cnoinner join teacher t on t.tno = c.tnowhere g.degree>= (select down from rank where rank = 'B')and degree <= (select up from rank where rank = 'B')and depart = '計算機系';-- 47、查詢成績在C等以上的學生的所在班級 select * from student s inner join grade g on s.sno = g.sno where g.degree >= (select down from rank where rank = 'C')order by class asc,cno asc ;select distinct class from student s inner join grade g on s.sno = g.sno where g.degree >= (select down from rank where rank = 'C');-- 48、查詢班里B等以上學生人數最多的班級信息 select * from student s inner join grade g on s.sno = g.sno where g.degree >= (select down from rank where rank = 'B');select class, count(class) from student s inner join grade g on s.sno = g.sno where g.degree >= (select down from rank where rank = 'B')group by class;

二? 、運行結果

D:/Program Files (x86)/MySQL/MySQL Server 5.7/bin/MYSQL: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | student | | student1 | | sys | +--------------------+ +-----------+-------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------------+------+-----+---------+-------+ | sno | varchar(3) | NO | PRI | 0 | | | sname | varchar(4) | NO | | NULL | | | ssex | enum('男','女') | YES | | 男 | | | sbirthday | datetime | YES | | NULL | | | class | varchar(5) | NO | | NULL | | +-----------+-------------------+------+-----+---------+-------+ +--------+------+-------+ | sname | ssex | class | +--------+------+-------+ | 李軍 | 男 | 95033 | | 陸君 | 男 | 95031 | | 匡明 | 男 | 95031 | | 王麗 | 女 | 95033 | | 曾華 | 女 | 95033 | | 王芳 | 女 | 95031 | +--------+------+-------+ +-----------------+ | depart | +-----------------+ | 計算機系 | | 電子工程系 | +-----------------+ +-----+--------+------+---------------------+-------+ | sno | sname | ssex | sbirthday | class | +-----+--------+------+---------------------+-------+ | 101 | 李軍 | 男 | 1976-02-20 00:00:00 | 95033 | | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | | 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 | | 108 | 曾華 | 女 | 1977-09-01 00:00:00 | 95033 | | 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | +-----+--------+------+---------------------+-------+ +-----+-------+--------+ | sno | cno | degree | +-----+-------+--------+ | 101 | 3-105 | 64 | | 105 | 3-245 | 75 | | 107 | 6-106 | 79 | | 108 | 3-105 | 78 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | +-----+-------+--------+ +-----+-------+--------+ | sno | cno | degree | +-----+-------+--------+ | 101 | 6-166 | 85 | | 103 | 3-245 | 86 | | 105 | 3-105 | 88 | +-----+-------+--------+ +-----+--------+------+---------------------+-------+ | sno | sname | ssex | sbirthday | class | +-----+--------+------+---------------------+-------+ | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | | 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 | | 108 | 曾華 | 女 | 1977-09-01 00:00:00 | 95033 | | 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | +-----+--------+------+---------------------+-------+ +-----+--------+------+---------------------+-------+ | sno | sname | ssex | sbirthday | class | +-----+--------+------+---------------------+-------+ | 101 | 李軍 | 男 | 1976-02-20 00:00:00 | 95033 | | 107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 | | 108 | 曾華 | 女 | 1977-09-01 00:00:00 | 95033 | | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | | 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | +-----+--------+------+---------------------+-------+ +-----+-------+--------+ | sno | cno | degree | +-----+-------+--------+ | 103 | 3-105 | 92 | | 107 | 3-105 | 91 | | 105 | 3-105 | 88 | | 108 | 3-105 | 78 | | 109 | 3-105 | 76 | | 101 | 3-105 | 64 | | 103 | 3-245 | 86 | | 105 | 3-245 | 75 | | 109 | 3-245 | 68 | | 107 | 6-106 | 79 | | 101 | 6-166 | 85 | | 108 | 6-166 | 81 | +-----+-------+--------+ +----------+ | count(*) | +----------+ | 3 | +----------+ +-------------+-----+-------+ | max(degree) | sno | cno | +-------------+-----+-------+ | 92 | 101 | 3-105 | +-------------+-----+-------+ +-------------+ | avg(degree) | +-------------+ | 81.5000 | +-------------+ +-------+-------------+ | cno | avg(degree) | +-------+-------------+ | 3-105 | 81.5000 | +-------+-------------+ +-----+ | sno | +-----+ | 101 | | 103 | | 105 | | 105 | | 107 | | 108 | | 108 | | 109 | +-----+ +--------+-------+--------+ | sname | cno | degree | +--------+-------+--------+ | 李軍 | 3-105 | 64 | | 李軍 | 6-166 | 85 | | 陸君 | 3-105 | 92 | | 陸君 | 3-245 | 86 | | 匡明 | 3-105 | 88 | | 匡明 | 3-245 | 75 | | 王麗 | 3-105 | 91 | | 王麗 | 6-106 | 79 | | 曾華 | 3-105 | 78 | | 曾華 | 6-166 | 81 | | 王芳 | 3-105 | 76 | | 王芳 | 3-245 | 68 | +--------+-------+--------+ +--------+-------+--------+ | sname | cno | degree | +--------+-------+--------+ | 李軍 | 3-105 | 64 | | 李軍 | 6-166 | 85 | | 陸君 | 3-105 | 92 | | 陸君 | 3-245 | 86 | | 匡明 | 3-105 | 88 | | 匡明 | 3-245 | 75 | | 王麗 | 3-105 | 91 | | 王麗 | 6-106 | 79 | | 曾華 | 3-105 | 78 | | 曾華 | 6-166 | 81 | | 王芳 | 3-105 | 76 | | 王芳 | 3-245 | 68 | +--------+-------+--------+ +-----+-----------------+--------+ | sno | cname | degree | +-----+-----------------+--------+ | 101 | 計算機導論 | 64 | | 101 | 數據電路 | 85 | | 103 | 計算機導論 | 92 | | 103 | 操作系統 | 86 | | 105 | 計算機導論 | 88 | | 105 | 操作系統 | 75 | | 107 | 計算機導論 | 91 | | 108 | 計算機導論 | 78 | | 108 | 數據電路 | 81 | | 109 | 計算機導論 | 76 | | 109 | 操作系統 | 68 | +-----+-----------------+--------+ +------+-----------------+--------+ | sno | cname | degree | +------+-----------------+--------+ | 101 | 計算機導論 | 64 | | 101 | 數據電路 | 85 | | 103 | 計算機導論 | 92 | | 103 | 操作系統 | 86 | | 105 | 計算機導論 | 88 | | 105 | 操作系統 | 75 | | 107 | 計算機導論 | 91 | | 108 | 計算機導論 | 78 | | 108 | 數據電路 | 81 | | 109 | 計算機導論 | 76 | | 109 | 操作系統 | 68 | | NULL | 高等數學 | NULL | +------+-----------------+--------+ +--------+-----------------+--------+ | sname | cname | degree | +--------+-----------------+--------+ | 李軍 | 計算機導論 | 64 | | 李軍 | 數據電路 | 85 | | 陸君 | 計算機導論 | 92 | | 陸君 | 操作系統 | 86 | | 匡明 | 計算機導論 | 88 | | 匡明 | 操作系統 | 75 | | 王麗 | 計算機導論 | 91 | | 曾華 | 計算機導論 | 78 | | 曾華 | 數據電路 | 81 | | 王芳 | 計算機導論 | 76 | | 王芳 | 操作系統 | 68 | +--------+-----------------+--------+ +-------------+ | avg(degree) | +-------------+ | 79.6667 | +-------------+ +-------+-------------+ | cno | avg(degree) | +-------+-------------+ | 3-105 | 77.6667 | | 6-106 | 79.0000 | | 6-166 | 83.0000 | +-------+-------------+ +-----+--------+------+---------------------+-------+-----+-------+--------+ | sno | sname | ssex | sbirthday | class | sno | cno | degree | +-----+--------+------+---------------------+-------+-----+-------+--------+ | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | 103 | 3-105 | 92 | | 107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 | 107 | 3-105 | 91 | +-----+--------+------+---------------------+-------+-----+-------+--------+ +-----+--------+------+---------------------+-------+ | sno | sname | ssex | sbirthday | class | +-----+--------+------+---------------------+-------+ | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | | 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 | | 108 | 曾華 | 女 | 1977-09-01 00:00:00 | 95033 | | 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | +-----+--------+------+---------------------+-------+ +-----+--------+------+---------------------+-------+-----+-------+--------+ | sno | sname | ssex | sbirthday | class | sno | cno | degree | +-----+--------+------+---------------------+-------+-----+-------+--------+ | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | 103 | 3-105 | 92 | | 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 105 | 3-105 | 88 | | 107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 | 107 | 3-105 | 91 | | 108 | 曾華 | 女 | 1977-09-01 00:00:00 | 95033 | 108 | 3-105 | 78 | | 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 109 | 3-105 | 76 | +-----+--------+------+---------------------+-------+-----+-------+--------+ +-----+-------+--------+--------+ | sno | cno | degree | max_dg | +-----+-------+--------+--------+ | 101 | 3-105 | 64 | 85 | | 103 | 3-245 | 86 | 92 | | 105 | 3-245 | 75 | 88 | | 107 | 6-106 | 79 | 91 | | 108 | 3-105 | 78 | 81 | | 109 | 3-245 | 68 | 76 | +-----+-------+--------+--------+ +-----+--------+------+---------------------+-------+-----+-------+--------+ | sno | sname | ssex | sbirthday | class | sno | cno | degree | +-----+--------+------+---------------------+-------+-----+-------+--------+ | 101 | 李軍 | 男 | 1976-02-20 00:00:00 | 95033 | 103 | 3-105 | 92 | | 101 | 李軍 | 男 | 1976-02-20 00:00:00 | 95033 | 105 | 3-105 | 88 | | 101 | 李軍 | 男 | 1976-02-20 00:00:00 | 95033 | 107 | 3-105 | 91 | | 101 | 李軍 | 男 | 1976-02-20 00:00:00 | 95033 | 108 | 3-105 | 78 | | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | 103 | 3-105 | 92 | | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | 105 | 3-105 | 88 | | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | 107 | 3-105 | 91 | | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | 108 | 3-105 | 78 | | 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 103 | 3-105 | 92 | | 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 105 | 3-105 | 88 | | 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 107 | 3-105 | 91 | | 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 108 | 3-105 | 78 | | 107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 | 103 | 3-105 | 92 | | 107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 | 105 | 3-105 | 88 | | 107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 | 107 | 3-105 | 91 | | 107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 | 108 | 3-105 | 78 | | 108 | 曾華 | 女 | 1977-09-01 00:00:00 | 95033 | 103 | 3-105 | 92 | | 108 | 曾華 | 女 | 1977-09-01 00:00:00 | 95033 | 105 | 3-105 | 88 | | 108 | 曾華 | 女 | 1977-09-01 00:00:00 | 95033 | 107 | 3-105 | 91 | | 108 | 曾華 | 女 | 1977-09-01 00:00:00 | 95033 | 108 | 3-105 | 78 | | 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 103 | 3-105 | 92 | | 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 105 | 3-105 | 88 | | 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 107 | 3-105 | 91 | | 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 108 | 3-105 | 78 | +-----+--------+------+---------------------+-------+-----+-------+--------+ +-----+--------+---------------------+ | sno | sname | sbirthday | +-----+--------+---------------------+ | 108 | 曾華 | 1977-09-01 00:00:00 | +-----+--------+---------------------+ +-----+--------+ | sno | degree | +-----+--------+ | 101 | 85 | | 108 | 81 | +-----+--------+ +-----+--------+ | sno | degree | +-----+--------+ | 101 | 85 | | 108 | 81 | +-----+--------+ +--------+ | tname | +--------+ | 王萍 | +--------+ +--------+ | tname | +--------+ | 王萍 | +--------+ +-----+--------+------+---------------------+-------+------+-------+--------+-------+-----------------+------+------+--------+------+---------------------+-----------+-----------------+ | sno | sname | ssex | sbirthday | class | sno | cno | degree | cno | cname | tno | tno | tname | tsex | tbirthday | prof | depart | +-----+--------+------+---------------------+-------+------+-------+--------+-------+-----------------+------+------+--------+------+---------------------+-----------+-----------------+ | 101 | 李軍 | 男 | 1976-02-20 00:00:00 | 95033 | 101 | 3-105 | 64 | 3-105 | 計算機導論 | 825 | 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 計算機系 | | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | 103 | 3-105 | 92 | 3-105 | 計算機導論 | 825 | 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 計算機系 | | 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 105 | 3-105 | 88 | 3-105 | 計算機導論 | 825 | 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 計算機系 | | 107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 | 107 | 3-105 | 91 | 3-105 | 計算機導論 | 825 | 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 計算機系 | | 108 | 曾華 | 女 | 1977-09-01 00:00:00 | 95033 | 108 | 3-105 | 78 | 3-105 | 計算機導論 | 825 | 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 計算機系 | | 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 109 | 3-105 | 76 | 3-105 | 計算機導論 | 825 | 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 計算機系 | | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | 103 | 3-245 | 86 | 3-245 | 操作系統 | 804 | 804 | 李誠 | 男 | 1958-12-02 00:00:00 | 副教授 | 計算機系 | | 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 105 | 3-245 | 75 | 3-245 | 操作系統 | 804 | 804 | 李誠 | 男 | 1958-12-02 00:00:00 | 副教授 | 計算機系 | | 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 109 | 3-245 | 68 | 3-245 | 操作系統 | 804 | 804 | 李誠 | 男 | 1958-12-02 00:00:00 | 副教授 | 計算機系 | | 101 | 李軍 | 男 | 1976-02-20 00:00:00 | 95033 | 101 | 6-166 | 85 | 6-166 | 數據電路 | 856 | 856 | 張旭 | 男 | 1969-03-12 00:00:00 | 講師 | 電子工程系 | | 108 | 曾華 | 女 | 1977-09-01 00:00:00 | 95033 | 108 | 6-166 | 81 | 6-166 | 數據電路 | 856 | 856 | 張旭 | 男 | 1969-03-12 00:00:00 | 講師 | 電子工程系 | | 107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 | 107 | 6-106 | 79 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +-----+--------+------+---------------------+-------+------+-------+--------+-------+-----------------+------+------+--------+------+---------------------+-----------+-----------------+ +-------+ | cno | +-------+ | 3-105 | | 3-245 | | 3-105 | | 3-105 | +-------+ +-----+-------+--------+ | sno | cno | degree | +-----+-------+--------+ | 101 | 3-105 | 64 | | 103 | 3-105 | 92 | | 103 | 3-245 | 86 | | 105 | 3-105 | 88 | | 105 | 3-245 | 75 | | 107 | 3-105 | 91 | | 108 | 3-105 | 78 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | +-----+-------+--------+ +-----+-------+--------+-------+-----------------+-----+ | sno | cno | degree | cno | cname | tno | +-----+-------+--------+-------+-----------------+-----+ | 101 | 3-105 | 64 | 3-105 | 計算機導論 | 825 | | 103 | 3-105 | 92 | 3-105 | 計算機導論 | 825 | | 103 | 3-245 | 86 | 3-245 | 操作系統 | 804 | | 105 | 3-105 | 88 | 3-105 | 計算機導論 | 825 | | 105 | 3-245 | 75 | 3-245 | 操作系統 | 804 | | 107 | 3-105 | 91 | 3-105 | 計算機導論 | 825 | | 108 | 3-105 | 78 | 3-105 | 計算機導論 | 825 | | 109 | 3-105 | 76 | 3-105 | 計算機導論 | 825 | | 109 | 3-245 | 68 | 3-245 | 操作系統 | 804 | +-----+-------+--------+-------+-----------------+-----+ +--------+-----------+ | tname | prof | +--------+-----------+ | 李誠 | 副教授 | +--------+-----------+ +-----+-------+--------+ | sno | cno | degree | +-----+-------+--------+ | 109 | 3-105 | 76 | | 105 | 3-105 | 88 | | 103 | 3-105 | 92 | +-----+-------+--------+ +-----+-------+--------+ | sno | cno | degree | +-----+-------+--------+ | 103 | 3-105 | 92 | | 105 | 3-105 | 88 | | 109 | 3-105 | 76 | +-----+-------+--------+ +--------+------+---------------------+ | tname | tsex | tbirthday | +--------+------+---------------------+ | 李誠 | 男 | 1958-12-02 00:00:00 | | 王萍 | 女 | 1972-05-05 00:00:00 | | 劉冰 | 女 | 1977-08-14 00:00:00 | | 張旭 | 男 | 1969-03-12 00:00:00 | | 李軍 | 男 | 1976-02-20 00:00:00 | | 陸君 | 男 | 1974-06-03 00:00:00 | | 匡明 | 男 | 1975-10-02 00:00:00 | | 王麗 | 女 | 1976-01-23 00:00:00 | | 曾華 | 女 | 1977-09-01 00:00:00 | | 王芳 | 女 | 1975-02-10 00:00:00 | +--------+------+---------------------+ +--------+------+---------------------+ | tname | tsex | tbirthday | +--------+------+---------------------+ | 王萍 | 女 | 1972-05-05 00:00:00 | | 劉冰 | 女 | 1977-08-14 00:00:00 | | 王麗 | 女 | 1976-01-23 00:00:00 | | 曾華 | 女 | 1977-09-01 00:00:00 | | 王芳 | 女 | 1975-02-10 00:00:00 | +--------+------+---------------------+ +-----+-------+--------+-------+---------+ | sno | cno | degree | cno | avg_dg | +-----+-------+--------+-------+---------+ | 101 | 3-105 | 64 | 3-105 | 81.5000 | | 105 | 3-245 | 75 | 3-245 | 76.3333 | | 108 | 3-105 | 78 | 3-105 | 81.5000 | | 108 | 6-166 | 81 | 6-166 | 83.0000 | | 109 | 3-105 | 76 | 3-105 | 81.5000 | | 109 | 3-245 | 68 | 3-245 | 76.3333 | +-----+-------+--------+-------+---------+ +--------+-----------------+ | tname | depart | +--------+-----------------+ | 李誠 | 計算機系 | | 王萍 | 計算機系 | | 張旭 | 電子工程系 | +--------+-----------------+ +--------+-----------------+ | tname | depart | +--------+-----------------+ | 劉冰 | 電子工程系 | +--------+-----------------+ +-------+----------+ | class | count(*) | +-------+----------+ | 95031 | 2 | +-------+----------+ +-----+--------+------+---------------------+-------+ | sno | sname | ssex | sbirthday | class | +-----+--------+------+---------------------+-------+ | 101 | 李軍 | 男 | 1976-02-20 00:00:00 | 95033 | | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | | 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 108 | 曾華 | 女 | 1977-09-01 00:00:00 | 95033 | +-----+--------+------+---------------------+-------+ +--------+-------------------------------+ | sname | year(now()) - year(sbirthday) | +--------+-------------------------------+ | 李軍 | 44 | | 陸君 | 46 | | 匡明 | 45 | | 王麗 | 44 | | 曾華 | 43 | | 王芳 | 45 | +--------+-------------------------------+ +---------------------+ | sbirthday | +---------------------+ | 1974-06-03 00:00:00 | | 1977-09-01 00:00:00 | +---------------------+ +-----+--------+------+---------------------+-------+ | sno | sname | ssex | sbirthday | class | +-----+--------+------+---------------------+-------+ | 107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 | | 101 | 李軍 | 男 | 1976-02-20 00:00:00 | 95033 | | 108 | 曾華 | 女 | 1977-09-01 00:00:00 | 95033 | | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | | 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | +-----+--------+------+---------------------+-------+ +-----+--------+------+---------------------+-----------+-----------------+-------+--------------+-----+ | tno | tname | tsex | tbirthday | prof | depart | cno | cname | tno | +-----+--------+------+---------------------+-----------+-----------------+-------+--------------+-----+ | 804 | 李誠 | 男 | 1958-12-02 00:00:00 | 副教授 | 計算機系 | 3-245 | 操作系統 | 804 | | 856 | 張旭 | 男 | 1969-03-12 00:00:00 | 講師 | 電子工程系 | 6-166 | 數據電路 | 856 | +-----+--------+------+---------------------+-----------+-----------------+-------+--------------+-----+ +-----+-------+--------+-------+--------+ | sno | cno | degree | cno | max_dg | +-----+-------+--------+-------+--------+ | 101 | 6-166 | 85 | 6-166 | 85 | | 103 | 3-105 | 92 | 3-105 | 92 | | 103 | 3-245 | 86 | 3-245 | 86 | | 107 | 6-106 | 79 | 6-106 | 79 | +-----+-------+--------+-------+--------+ +--------+ | sname | +--------+ | 李軍 | | 陸君 | | 匡明 | +--------+ +--------+ | sname | +--------+ | 李軍 | +--------+ +-------+-----------------+-----+-----+-------+--------+-----+--------+------+---------------------+-------+ | cno | cname | tno | sno | cno | degree | sno | sname | ssex | sbirthday | class | +-------+-----------------+-----+-----+-------+--------+-----+--------+------+---------------------+-------+ | 3-105 | 計算機導論 | 825 | 101 | 3-105 | 64 | 101 | 李軍 | 男 | 1976-02-20 00:00:00 | 95033 | | 3-105 | 計算機導論 | 825 | 103 | 3-105 | 92 | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | | 3-105 | 計算機導論 | 825 | 105 | 3-105 | 88 | 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | +-------+-----------------+-----+-----+-------+--------+-----+--------+------+---------------------+-------+ +-------+-----------------+-----+ | cno | cname | tno | +-------+-----------------+-----+ | 3-245 | 操作系統 | 804 | | 3-105 | 計算機導論 | 825 | +-------+-----------------+-----+ +-------+-----------------+-----+-----+-------+--------+-----+--------+------+---------------------+-----------+--------------+ | cno | cname | tno | sno | cno | degree | tno | tname | tsex | tbirthday | prof | depart | +-------+-----------------+-----+-----+-------+--------+-----+--------+------+---------------------+-----------+--------------+ | 3-245 | 操作系統 | 804 | 103 | 3-245 | 86 | 804 | 李誠 | 男 | 1958-12-02 00:00:00 | 副教授 | 計算機系 | | 3-105 | 計算機導論 | 825 | 105 | 3-105 | 88 | 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 計算機系 | +-------+-----------------+-----+-----+-------+--------+-----+--------+------+---------------------+-----------+--------------+ +-----+--------+------+---------------------+-------+-----+-------+--------+ | sno | sname | ssex | sbirthday | class | sno | cno | degree | +-----+--------+------+---------------------+-------+-----+-------+--------+ | 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 105 | 3-105 | 88 | | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | 103 | 3-105 | 92 | | 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 109 | 3-105 | 76 | | 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 105 | 3-245 | 75 | | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | 103 | 3-245 | 86 | | 108 | 曾華 | 女 | 1977-09-01 00:00:00 | 95033 | 108 | 3-105 | 78 | | 107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 | 107 | 3-105 | 91 | | 107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 | 107 | 6-106 | 79 | | 101 | 李軍 | 男 | 1976-02-20 00:00:00 | 95033 | 101 | 6-166 | 85 | | 108 | 曾華 | 女 | 1977-09-01 00:00:00 | 95033 | 108 | 6-166 | 81 | +-----+--------+------+---------------------+-------+-----+-------+--------+ +-------+ | class | +-------+ | 95033 | | 95031 | +-------+ +-----+--------+------+---------------------+-------+-----+-------+--------+ | sno | sname | ssex | sbirthday | class | sno | cno | degree | +-----+--------+------+---------------------+-------+-----+-------+--------+ | 101 | 李軍 | 男 | 1976-02-20 00:00:00 | 95033 | 101 | 6-166 | 85 | | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | 103 | 3-105 | 92 | | 103 | 陸君 | 男 | 1974-06-03 00:00:00 | 95031 | 103 | 3-245 | 86 | | 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | 105 | 3-105 | 88 | | 107 | 王麗 | 女 | 1976-01-23 00:00:00 | 95033 | 107 | 3-105 | 91 | | 108 | 曾華 | 女 | 1977-09-01 00:00:00 | 95033 | 108 | 6-166 | 81 | +-----+--------+------+---------------------+-------+-----+-------+--------+ +-------+--------------+ | class | count(class) | +-------+--------------+ | 95031 | 3 | | 95033 | 3 | +-------+--------------+ [Finished in 0.3s]

?

總結

以上是生活随笔為你收集整理的mysql练习_创建库与列表、增加列表信息、列表查询(包含多列表查询)_月隐学python第23课的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。