大佬教程收集整理的这篇文章主要介绍了MySQL56--SQL语句练习02,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
CREATE DATABASE test01_2106 character set utf8;
USE test01_2106;
sno varchar(20) NOT NULL 学生编号
Sname varchar(20) NOT NULL 学生姓名
ssex varchar(20) NOT NULL 性别
sbirthday datetiR_102_11845@e 出生日期
class varchar(20) NOT NULL 班级
create table student
(
sno varchar(20) NOT NULL,
sname varchar(20) NOT NULL,
ssex varchar(20) NOT NULL,
sbirthday datetime,
class varchar(20)
);
tno varchar(20) NOT NULL 教师编号
tname varchar(20) NOT NULL 教师姓名
tsex varchar(20) NOT NULL 性别
tbirthday datetiR_102_11845@e 生日
prof varchar(20)职位
depart varchar(20) NOT NULL 单位
create table teacher
(
tno varchar(20) NOT NULL priMary key,
tname varchar(20) NOT NULL,
tsex varchar(20) NOT NULL,
tbirthday datetime,
prof varchar(20),
depart varchar(20) NOT NULL
);
Cno varchar(20) NOT NULL 课程编号
Cname varchar(20) NOT NULL 课程名称
tno varchar(20) NOT NULL 教师编号
create table course
(
cno varchar(20) NOT NULL,
cname varchar(20) NOT NULL,
tno varchar(20) NOT NULL,
);
sno varchar(20) NOT NULL 学生编号
cno varchar(20) NOT NULL 课程编号
degree decimal)分数
create table score
(
sno varchar(20) NOT NULL,
cno varchar(20) NOT NULL,
degree decimal
);
sno Sname ssex sbirthday class
108 曾华男1977-09-01 95033
107 王丽女1976-01-23 95033
101 李军男1976-02-20 95033
109 王芳女1975-02-10 95031
103 陆君男1974-06-03 95031
INSERT INTO student values('108','曾华','男','1977-09-01','95033');
INSERT INTO student values('105','匡明','男','1975-10-02','95031');
INSERT INTO student values('107','王丽','女','1976-01-23','95033');
INSERT INTO student values('101','李军','男','1976-02-20','95033');
INSERT INTO student values('109','王芳','女','1975-02-10','95031');
INSERT INTO student values('103','陆君','男','1974-06-03','95031');
tno tname tsex tbirthday prof depart
804 李诚男1958-12-02 副教授计算机系
856 张旭男1969-03-12 讲师电子工程系
825 王萍女1972-05-05 助教计算机系
INSERT INTO teacher values('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher values('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');
cno Cname tno
3-105 计算机导论825
3-245 操作系统804
6-166 数字电路856
9-888 高等数学831
INSERT INTO course values('3-105','计算机导论','825');
INSERT INTO course values('3-245','操作系统','804');
INSERT INTO course values('6-166','数字电路','856');
INSERT INTO course values('9-888','高等数学','831');
sno Cno degree
103 3-245 86
105 3-245 75
109 3-245 68
103 3-105 92
105 3-105 88
109 3-105 76
103 3-105 64
105 3-105 91
109 3-105 78
103 6-166 85
105 6-166 79
109 6-166 81
INSERT INTO score values('103','3-245','86');
INSERT INTO score values('105','3-245','75');
INSERT INTO score values('109','3-245','68');
INSERT INTO score values('103','3-105','92');
INSERT INTO score values('105','3-105','88');
INSERT INTO score values('109','3-105','76');
INSERT INTO score values('103','3-105','64');
INSERT INTO score values('105','3-105','91');
INSERT INTO score values('109','3-105','78');
INSERT INTO score values('103','6-166','85');
INSERT INTO score values('105','6-166','79');
INSERT INTO score values('109','6-166','81');
SELECT Sname,Ssex,Class
FROM student;
SELECT disTinct Depart
FROM teacher;
SELECT *
FROM student;
SELECT *
FROM Score
WHERE Degree between 60 and 80;
SELECT *
FROM Score
WHERE Degree in(85,86,88);
SELECT *
FROM student
WHERE class='95031' or Ssex='女';
SELECT *
FROM student
ORDER BY class desc;
SELECT *
FROM Score
ORDER BY cno asc,degree desc;
SELECT count(*)
FROM student
WHERE class='95031';
SELECT sno,Cno
FROM Score
WHERE Degree=(SELECT max(DegreE) FROM score);
或者
SELECT sno,Cno
FROM Score
ORDER BY Degree desc
LIMIT 0,1;
SELECT Cno,avg(degreE)
FROM Score
GROUP BY Cno;
SELECT avg(DegreE)
FROM score
WHERE Cno like '3%' and Cno in (SELECT Cno FROM score GROUP BY Cno having count(*)>=5);
SELECT sno
FROM Score
WHERE degree>70 and degree<90;
SELECT Sname, Cno,Degree
FROM Score , student
WHERE Score.sno=student.sno;
SELECT sno,Cname,Degree
FROM Score , Course
WHERE Score.Cno=Course.Cno;
SELECT Sname,Cname,Degree
FROM student,course,score
WHERE student.sno=score.sno and course.Cno=score.Cno;
SELECT avg(degreE)
FROM Score
WHERE sno in (SELECT sno FROM student WHERE Class='95031');
SELECT *
FROM score
WHERE degree > all(SELECT degree FROM Score WHERE sno='109' and Cno='3-105');
SELECT sno,sname,sbirthday
FROM student
WHERE year(sbirthday)= (SELECT year(sbirthday) FROM student WHERE sno='108');
SELECT sno,sname,sbirthday
FROM student
WHERE year(sbirthday)= (SELECT year(sbirthday) FROM student WHERE sno='101');
SELECT sno,degree
FROM score,Course
WHERE score.Cno=Course.Cno and Course.Tno= (SELECT Tno FROM Teacher WHERE Tname='张旭');
SELECT degree
FROM score
WHERE Cno in (SELECT cno FROM course WHERE Tno= (SELECT Tno FROM Teacher WHERE Tname='张旭'));
SELECT tname
FROM teacher
WHERE tno in(SELEct tno from course where cno in(SELEct cno from score sc
GROUP BY cno
HAVING count(sc.sno)>5));
SELECT *
FROM student
WHERE class in ('95033','95031');
SELECT Cno
FROM score
WHERE degree>85;
SELECT *
FROM course
WHERE cno in (SELECT cno FROM course WHERE tno in (SELECT tno FROM teacher WHERE Depart='计算机系'));
SELECT Tname,Prof
FROM Teacher
WHERE Depart ='计算机系' and Prof not in( SELECT Prof FROM Teacher WHERE Depart ='电子工程系')<br>union <br>SELECT Tname,Prof FROM Teacher WHERE Depart ='电子工程系' and Prof not in( SELECT Prof FROM Teacher WHERE Depart ='计算机系')
SELECT Cno,sno,Degree
FROM score
WHERE cno='3-105' and degree >any(SELECT degree FROM score WHERE cno='3-245')
ORDER BY degree desc;
SELECT Cno,sno,Degree
FROM score
WHERE cno='3-105' and degree >all(SELECT degree FROM score WHERE cno='3-245')
ORDER BY degree desc;
SELECT tname,tsex,tbirthday
FROM Teacher union SELECT sname,ssex,sbirthday FROM student;
SELECT Tname,Tsex,Tbirthday
FROM Teacher WHERE Tsex='女' union SELECT Sname,Ssex,Sbirthday FROM student WHERE Ssex='女';
SELECT *
FROM score a
WHERE degree < (SELECT avg(degreE) FROM score b WHERE b.cno=a.cno);
SELECT Tname,Depart
FROM Teacher
WHERE tno in (SELECT tno FROM course );
SELECT Tname,Depart
FROM Teacher
WHERE Tno not in (SELECT Tno FROM Course WHERE cno in (SELECT cno FROM score ));
SELECT class
FROM student
WHERE ssex='男' GROUP BY class having count(*)>1;
SELECT *
FROM student
WHERE Sname not like '王%';
SELECT Sname, year(now())-year(sbirthday)
FROM student;
SELECT Max(Sbirthday ),Min(Sbirthday )
FROM student;
SELECT *
FROM student
ORDER BY class desc, Sbirthday asc;
SELECT Tname,Cname
FROM course,teacher
WHERE course.tno= teacher.tno and teacher.Tsex='男';
SELECT sno,Cno,Degree
FROM score
WHERE degree=(SELECT max(degreE) FROM score);
SELECT sno,Cno,Degree
FROM score
ORDER BY degree desc
LIMIT 0,1;
SELECT Sname
FROM student
WHERE Ssex = (SELECT Ssex FROM student WHERE Sname='李军');
SELECT Sname
FROM student
WHERE Ssex = (SELECT Ssex FROM student WHERE Sname='李军') and class=( SELECT class FROM student WHERE Sname='李军');
SELECT sno,Cno,degree
FROM score
WHERE Cno=( SELECT Cno FROM course WHERE Cname='计算机导论') and sno in (SELECT sno FROM student WHERE Ssex='男');
以上是大佬教程为你收集整理的MySQL56--SQL语句练习02全部内容,希望文章能够帮你解决MySQL56--SQL语句练习02所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。