【SQL】超全！以练代学，练完这篇，搞定MySQL语句！（基础篇）

爱写代码的July

于 2024-07-24 00:18:29 发布

阅读量437

点赞数 8

分类专栏： SQL 文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/Liu_y_xin/article/details/140649187

版权

SQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一增删改

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;

爱写代码的July

关注

8
点赞
踩
19

收藏

觉得还不错? 一键收藏
0
评论
【SQL】超全！以练代学，练完这篇，搞定MySQL语句！（基础篇）

以练代学，练完这篇，搞定MySQL语句！（基础篇）包括了增删改查等必须掌握的SQL操作
复制链接

扫一扫

专栏目录

【SQL】超全！以练代学，练完这篇，搞定MySQL语句！（基础篇）

一 增删改

二 单表查询

三 多表连接查询

四 综合查询

一增删改

二单表查询

三多表连接查询

四综合查询