一 增删改
1.采用SQL语句创建表
- 学生信息表student: 学号、姓名、性别、年龄、专业、院系
- 教师信息表teacher: 教师号、姓名、性别、年龄、职称、工资、专业、院系
- 课程信息表 course :课程号、课程名、课时
- 选课表sc:学号、课程号、教师号,成绩、开课日期
在各个表中分别插入如下数据:
学生信息表student中:
学号 | 姓名 | 性别 | 年龄 | 专业 | 院系 |
s1 | 王彤 | 女 | 18 | 计算机 | 信息学院 |
s2 | 苏乐 | 女 | 20 | 信息 | 信息学院 |
s3 | 林昕 | 男 | 19 | 信息 | 信息学院 |
s4 | 陶然 | 女 | 18 | 自动化 | 工学院 |
s5 | 魏立 | 男 | 17 | 数学 | 理学院 |
s6 | 何欣荣 | 女 | 21 | 计算机 | 信息学院 |
Insert into s(sno,sn,age,sex,maj,dept)
Values(`s1`,`王彤`,`18,`女`,`计算机`,`信息学院`),
(`s2`,`苏乐`,`20,`女`,`信息`,`信息学院`),
(`s3`,`林昕`,`19,`男`,`信息`,`信息学院`),
(`s4`,`陶然`,`18,`女`,`自动化`,`工学院`),
(`s5`,`魏立`,`17,`男`,`数学`,`理学院`),
(`s6`,`何欣荣`,`21,`女`,`计算机`,`信息学院`);
教师信息表teacher 中:
教师号 | 姓名 | 性别 | 年龄 | 职称 | 工资 | 专业 | 院系 |
t1 | 刘杨 | 男 | 40 | 教授 | 3610.5 | 计算机 | 信息学院 |
t2 | 石丽 | 女 | 26 | 讲师 | 2923.3 | 信息 | 信息学院 |
t3 | 顾伟 | 男 | 32 | 副教授 | 3145 | 计算机 | 信息学院 |
t4 | 赵礼 | 女 | 50 | 教授 | 4267.9 | 自动化 | 工学院 |
t5 | 赵希希 | 女 | 36 | 副教授 | 3332.67 | 数学 | 理学院 |
t6 | 张刚 | 男 | 30 | 讲师 | 3012 | 自动化 | 工学院 |
课程信息表course中:
课程号 | 课程名 | 课时 |
c1 | Java程序设计 | 40 |
c2 | 程序设计基础 | 48 |
c3 | 线性代数 | 48 |
c4 | 数据结构 | 64 |
c5 | 数据库系统 | 56 |
c6 | 数据挖掘 | 32 |
选课表sc中:
学号 | 课程号 | 教师号 | 成绩 | 开课日期 |
s1 | c1 | t1 | 90.5 | 20210903 |
s1 | c2 | t1 | 85 | 20210904 |
s3 | c2 | t3 | 70.5 | 20210308 |
s4 | c1 | t1 | 93 | 20210903 |
s5 | c5 | t6 | 20210906 | |
s6 | c6 | t5 | 20210910 |
INSERT INTO `sc` VALUES ('s1', 'c1', 't1', '90.50', '2021-09-03');
INSERT INTO `sc` VALUES ('s1', 'c2', 't1', '85.00', '2021-09-04');
INSERT INTO `sc` VALUES ('s3', 'c2', 't3', '70.50', '2021-03-08');
INSERT INTO `sc` VALUES ('s4', 'c1', 't1', '93.00', '2021-09-03');
INSERT INTO `sc` VALUES ('s5', 'c5', 't6', '75.00', '2021-09-06');
INSERT INTO `sc` VALUES ('s6', 'c6', 't5', '76.00', '2021-09-10');
2.修改
- 将刘杨老师转到工学院。
Update t
Set dept=`工学院`
Where tn=`刘杨`;
- 将所有学生的年龄增加一岁。
Update s
Set age=age+1;
- 将所有课程的课时提高到原来的1.5倍
Update c
Set ct=ct*1.5;
- 将s1学生的性别改为男
UPDATE s
SET `sex` = '男' WHERE (`sno` = 's1');
- 将赵希希老师的职称改为教授
UPDATE `teaching`.`t`
SET `prof` = '教授' WHERE (`tno` = 't5');
- 将c3课程的课时改为60
PDATE `c`
SET `ct` = '60' WHERE (`cno` = 'c3');
3.删除
- 删除成绩为90.5分的记录。
Delete
From sc
Where score=90.5;
- 删除课程号为c3的课程信息
Delete
From c
Where sno=c3;
- 删除课程号为c6的课程信息
Delete
From c
Where sno=c6;
- 删除所有学生的选课记录
Truncate table sc;
二 单表查询
1.无条件查询
- 查询课程表中的全部数据
Select cno,cn,ct
From c;
- 查询已选课的学生的学号
Select distinct sno
From sc;
2.条件查询
- 查询成绩在90分及其以上的选课信息
Select *
From sc
Where score>=90;
- 查询职称为教授的教师的教师号、姓名和专业
select tno,tn,maj
from teacher
where prof='教授';
- 查询专业是计算机和数学的学生信息
select *
from student
where maj='计算机' or maj='数学';
- 查询年龄在30-40(包括30和40)岁的教师的教师号、姓名和职称
select tno,tn,prof
from teacher
where age>=30 and age<=40;
- 查询课时不在30-40(包括30和40)课时的课程的课程号、课程名和课时
select *
from course
where ct<30 or ct>40;
- 查询课程号为c4和c6的选课信息,包括学号、课程号和成绩
select sno,cno,score from sc where cno in ('c4','c6');
- 查询年龄大于20的学生的学号、姓名和年龄, 结果列名为汉字
select sno as 学号,sn as 姓名,age as 年龄
from student
where age >=20;
- 查询选修了课程的学生学号
select distinct sno as 学号
from sc;
- 查询不是计算机系或信息系学生
select *
from student
where dept not in ('计算机系' ,'信息系');
- 查询姓名长度至少是三个汉字且第三个汉字必须是“马”的学生
select *
from student
where sn like '__马%';
- 查询姓名中含有“然”的学生信息
select *
from student
where sn like '%然%';
- 查询选修't3'老师,成绩在80至90之间学生的信息
select *
from sc
where tno='t3' and score between 80 and 90;
- 查询没有成绩的学生的学号和课程号
select sno,cno
from sc
where score is null;
- 查询学号为s1的学生的课程的平均分
select avg(score),max(score),min(score),sum(score),count(*)
from sc
where sno='s1';
- 查询选课表sc中选课学生人数
select count(distinct sno)
from sc;
- 查询选课表sc中每个学生的选课信息及每个学生的选课门数
select sno,count(*)
from sc
group by sno;
- 查询选课表sc中选了1门以上课程的学生的选课信息及学生的选课门数(不包括1门)
select count(*)
from sc
group by sno
having count(*)>1;
- 查询学号为s1的学生的选课信息,按照成绩降序排序
select sno,cno,score
from sc
where sno='s1'
order by score desc;
- 查询从第三位学生开始的4位学生的信息
select *
from student
limit 2,4;
- 查询选课表sc中每门课程的课程号及选课人数,按照选课人数降序排列,并且显示前5行
select cno,count(*)
from sc
group by cno
order by count(*) desc
limit 5;
三 多表连接查询
1.连接查询
- 查询学号为“s5”的学生的选课信息,要求列出学号,姓名,课程号和课程名称。
SELECT student.sno,student.sn,course.cno,course.cn
From student,course,sc
WHERE student.sno=sc.sno AND course.cno=sc.cno AND student.sno='s5';
- 查询所有授课教师的教师号、姓名和讲授的课程,并且按照教师号升序排列。
SELECT teacher.tno,teacher.tn,course.cn
FROM teacher,sc,course
WHERE sc.tno=teacher.tno AND course.cno=sc.cno
ORDER BY teacher.tno;
- 查询选课人数在3人及以上的课程号、课程名和选课人数。
SELECT sc.cno,course.cn,COUNT(sno) '选课人数'
FROM course RIGHT JOIN sc ON (course.cno=sc.cno)
GROUP BY sc.cno HAVING COUNT(*)>=3;
- 查询没有选课的学生信息。
SELECT DISTINCT student.*
FROM student
WHERE
student.sno NOT IN (SELECT sno
FROM sc);
- 查询没有学生选课的课程信息。
SELECT DISTINCT course.*
FROM course
WHERE course.cno NOT IN (SELECT cno
FROM sc);
- 查询和“王彤”在同一个系的学生的姓名。
SELECT sn
FROM student
WHERE dept IN
(SELECT dept
FROM student
WHERE sn='王彤');
- 查询和“程序设计基础”课程课时数相同的其他课程信息。
SELECT course1.*
FROM course course1,course course2
WHERE course1.ct=course2.ct AND course2.cn='程序设计基础';
2.不相关子查询
- 查询与’苏乐’在同一个专业学习的学生的信息。
SELECT student.*
FROM student
WHERE maj IN
(SELECT maj
FROM student
WHERE sn='苏乐');
- 查询选修了课程名为’数据库系统’ 的学生的学号和姓名。
SELECT sno,sn
FROM student
WHERE sno IN
(SELECT sno
FROM sc
WHERE cno IN
(SELECT cno
FROM course
WHERE cn='数据库系统')
);
- 查询教师号为“t1”的教师讲授的课程的课程号、课程名和课时。
SELECT course.*
FROM course
WHERE cno IN
(SELECT cno
FROM sc
WHERE tno='t1');
- 查询比’王彤’年龄小的所有学生的信息。
SELECT student.*
FROM student
WHERE age<(SELECT age
FROM student
WHERE sn='王彤');
- 查询与教师“顾伟”职称不同的教师的教师号、姓名和职称。
SELECT tno,tn,prof
FROM teacher
WHERE prof !=
(SELECT prof
FROM teacher
WHERE tn='顾伟')
- 查询其他院系中比信息学院某一学生年龄大的学生姓名和年龄。
SELECT sn,age
FROM student
WHERE age>ANY(SELECT age
FROM student
WHERE dept='信息学院')
AND dept<>'信息学院';
- 查询其他院系中比信息学院学生年龄都大的学生姓名和年龄。
SELECT sn,age
FROM student
WHERE age>ALL(SELECT age
FROM student
WHERE dept='信息学院')
AND dept<>'信息学院';
3.相关子查询(带Exists谓词的子查询)
- 查询所有选修了“c1”课程的学生姓名。
SELECT sn
FROM student
WHERE EXISTS
(SELECT *
FROM sc
WHERE sno=student.sno AND cno='c1');
- 查询没有选修了“c1”号课程的学生姓名。
SELECT sn
FROM student
WHERE NOT EXISTS
(SELECT *
FROM sc
WHERE sno=student.sno AND cno='c1');
四 综合查询
1. 查询成绩在90分及以上的学生的学号。
Select sno as‘学号’
From sc
Where score>=90;
2. 查询所有学生的出生年份。
SELECT sn,2022-age
FROM student;
3. 查询所有姓王、张、吴、李的学生信息。
select *
from student
where sn like '王%' or sn like'张%'or sn like'吴%' or sn like'李%';
4. 查询选了课的学生学号和姓名。
select diatinct student.sno,sn
from student
where student.sno in (select sno
from sc);
5. 查询没有学生选的课程信息。
Select distinct course.*
From course
where course.cno not in (select cno
from sc);
6. 查询学习了课程名中包含“数据”的学生信息。
SELECT student.*
FROM sc,student
WHERE sc.sno=student.sno AND sc.cno IN(SELECT cno
FROM course
WHERE cn LIKE('%数据%')
);
7. 查询不学习“程序设计基础”的学生姓名和所在系。
SELECT sn,dept
FROM student
WHERE sno NOT IN(
SELECT sno
FROM sc
WHERE cno IN(
SELECT cno
FROM course
WHERE cn LIKE('程序设计基础')
));
8. 查询与“苏乐”在同一专业学习的学生信息。
SELECT student.*
FROM student
WHERE maj IN
(SELECT maj
FROM student
WHERE sn='苏乐');
9. 查询“数据库系统”课程的前三名学生的成绩。
select sc.sno,cno,score
from sc
where cno in (select cno
from course
where cn ='数据库系统')
order by score
limit 3;
10.统计每个学生的选课门数和平均成绩。
select sno,count(cno) as '选课门数',avg(score) as '平均分数'
from sc
group by sno;
11.统计各院系男学生的人数。
select dept,count(sex)
from student
where sex='男'
group by dept;
12.统计学校院系的个数。
select count(dept)
from student;
13.统计每个院系的男生人数和女生人数。
SELECT dept,SUM(CASE WHEN sex='男' THEN 1 ELSE 0 END) 男生人数,SUM(CASE WHEN sex='女' THEN 1 ELSE 0 END) 女生人数
FROM student
GROUP BY dept;
14.统计院系教师平均工资在3500以上的系的名称和平均工资。
select dept,avg(sal)
from teacher
group by dept
having avg(sal)>3500;