【 Tips: 大小写均可，字段名和表名加不加单引号均可】
【 Tips: 分号用来分隔多个sql块儿，分号放在每个块儿的最后一条语句末尾】
基础语句
1. 查select
查询某一列
SELECT `name`
FROM `courses`;
查询多列，列之间用逗号隔开
select name,created_at
from courses
查询所有数据
SELECT *
FROM courses
查询某一列，只想看一下包含了哪些不同的值，用distinct关键字
select distinct country
from teachers
根据条件进行查询，where中多个条件之间用and分隔
select *
from courses
WHERE student_count > 1000
2. 增insert
插入一条完整的记录
insert into courses
values (14,'SQL',200,'2021-02-25',1)
插入一条记录，指定某几个字段的值
insert into teachers (name,email,age,country)
values ('XiaoFu','XiaoFu@lintcode.com',20,'CN')
从另一个表中选取数据插入到另一张表
INSERT INTO student
SELECT * FROM student_copy1 WHERE s_id='13'
-- 插入时注意两个表的主键不能存在重复值哦
3. 改update
用update、set、where这三个关键字
update courses
set student_count=500
where name='Artificial Intelligence'
4. 删delete
不用where语句会全删了，变成一个空表哦
DELETE FROM Websites
WHERE name='Facebook' AND country='USA';
查询表的总行数
方法1：
select count(字段名) from 表名
【统计字段名那一列，如果某条记录的这个字段值为NULL，这条将不被计数】
【说白了就是统计这个字段不为空的行数】
方法2：
select count(1) from 表名
【在表的结构中添加一列，该列的数据都为1。】
【也就是将1作为一个字段，并将这列全部填充为1】
【统计的是这列有多少个1，也就是总行数，更符合我们的需求】
方法3：
select count(*) from 表名
【统计表中行的数据总量，但如果某一行所有字段值都是NULL，这行将不被计数】
总结：如果不像漏掉 Null 的计数，无脑用count（1）就行了
执行效率：count（1）≈ count（*）> count（字段名）
【count（字段名）需要判断这个字段值是否为NULL，所以效率会低一些】
Navicat导入.sql文件
附录
============================================================
刷SQL面试50题
-- 创建四张表【学生表、课程表、教师表、成绩表】
CREATE TABLE Student(
s_id VARCHAR(20),
s_name VARCHAR(20),
s_birth VARCHAR(20),
s_sex VARCHAR(10),
PRIMARY KEY(s_id)
);
CREATE TABLE Course(
c_id VARCHAR(20),
c_name VARCHAR(20),
t_id VARCHAR(20),
PRIMARY KEY(c_id)
);
CREATE TABLE Teacher(
t_id VARCHAR(20),
t_name VARCHAR(20),
PRIMARY KEY(t_id)
);
CREATE TABLE Score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id,c_id)
);
-- 学生表测试数据
INSERT INTO student VALUES('01','赵雷','1990-01-01','男');
INSERT INTO student VALUES('02','钱电','1990-12-21','男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
-- 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
-- 成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
题目1：查询01的课程比02课程成绩高的学生，显示学生信息及相应两门课的成绩
-- 1.分别找出01的成绩记录和02的成绩记录
-- 2.将两个表根据学号进行关联，留下学号和两列成绩字段
-- 内连接（交集，即同时选了01和02课程的学生）的语法:
-- A inner join B on A.key=B.key
SELECT a.s_id "学号", c.s_name "姓名", a.s_score "01课程成绩", b.s_score "02课程成绩" FROM
(SELECT * FROM score WHERE c_id='01') AS a
INNER JOIN
(SELECT * FROM score WHERE c_id='02') AS b ON a.s_id=b.s_id
INNER JOIN student as c ON c.s_id=a.s_id -- 连接上学生表，补充学生信息
WHERE a.s_score>b.s_score
题目2：查询平均成绩大于60分的学生的学号和平均成绩
-- 做统计时往往会用到group by
-- GROUP BY中用having关键字来筛选符合条件的组
SELECT s_id "学号", AVG(s_score) "平均成绩" FROM score
GROUP BY s_id HAVING AVG(s_score)>60
题目3：查询所有学生的学号、姓名、选课数、总成绩
-- 要求是展示所有学生，所以用左连接比较好，保证学生不缺失
SELECT a.s_id "学号", a.s_name "姓名", COUNT(b.c_id) "选课数",
SUM(CASE WHEN b.s_score IS NULL THEN
0
ELSE
b.s_score
END) "总成绩"
FROM student AS a LEFT JOIN score AS b ON a.s_id=b.s_id
GROUP BY a.s_id, a.s_name
-- 这里最好时带上a.s_name，因为用group by的时候，
-- select的东西要么是统计函数，要么是group by中出现的，这样是比较好的
-- 下边这个查询很好的说明了用group by时，select能写的字段信息
-- SELECT COUNT(c_id) FROM score -- 01学生选了3门课，计数是正常的
-- GROUP BY s_id HAVING s_id='01'
-- 这个写法是错误的！
-- SELECT c_id FROM score -- 但是这个c_id却显示不出来三门课id，就是因为c_id不在group by的字段中
-- GROUP BY s_id HAVING s_id='01'
-- 所以想看分组后其他字段的数据信息，这种情况下用group by是做不到的，可以换成where
-- SELECT c_id FROM score
-- WHERE s_id='01'
题目4：查询姓张的老师的个数
-- 考察like关键字的使用，模糊查询
SELECT COUNT(t_id)
FROM teacher
WHERE t_name LIKE '张%'
-- 如果是想查姓张但是不同名的个数，可以用distinct来去重
SELECT COUNT(DISTINCT t_name)
FROM teacher
WHERE t_name LIKE '张%'
题目5【重点】：查询没学过张三老师课程的学生的学号、姓名
-- 思路1：张三老师(t_name) ==教师表==> 张三老师的编号(t_id) ==课程表 ==>这个老师教过哪些课(c_id)
-- ==成绩表==> 有c_id这门课的成绩的学生 ==学生表==> 其他剩余的学生
SELECT s_id '学号', s_name '姓名' FROM student
WHERE
s_id NOT IN
(SELECT s_id FROM score
WHERE
c_id=(SELECT c_id FROM course
WHERE
t_id=(SELECT t_id FROM teacher
WHERE
t_name='张三')))
-- 思路2：成绩表连接课程表，课程表连接教师表，得到的大表根据“张三”查学号，最后从学生表求剩余学生
SELECT s_id "学号", s_name "姓名" FROM student
WHERE s_id NOT IN
(SELECT s_id FROM
score AS s INNER JOIN course AS c ON s.c_id=c.c_id
INNER JOIN teacher AS t ON c.t_id=t.t_id
WHERE t.t_name='张三')
题目6【重点】：查询学过张三老师所教课程的学生学号、姓名
-- 思路1：还是嵌套查询
SELECT s_id '学号', s_name '姓名' FROM student
WHERE
s_id IN(SELECT s_id FROM score
WHERE
c_id=(SELECT c_id FROM course
WHERE
t_id=(SELECT t_id FROM teacher
WHERE
t_name='张三')))
-- 思路2：直接把三个表连接起来
SELECT st.s_id '学号',st.s_name '姓名'
FROM student AS st
INNER JOIN score AS s ON st.s_id=s.s_id
INNER JOIN course AS c ON s.c_id=c.c_id
INNER JOIN teacher AS t ON c.t_id=t.t_id
WHERE t.t_name='张三'
题目7【重点】：查询学过编号为01课程也学过02课程的学生的学号、姓名
-- 思路：从成绩表中找出01课程有哪些同学选了，再从成绩表中找出02课程有哪些同学选了
-- 这两个结果求交集，即同时选了两门课的学生（inner join）
SELECT s_id '学号', s_name '姓名' FROM student
WHERE s_id IN
(
SELECT a.s_id FROM
(SELECT s_id FROM score WHERE c_id='01') AS a
INNER JOIN
(SELECT s_id FROM score WHERE c_id='02') AS b
ON a.s_id=b.s_id
)
题目8：查询课程编号为02的总成绩
-- 思路1：直接用where挑出02的记录
SELECT SUM(s_score) FROM score
WHERE c_id='02'
-- 思路2：用group by分组，再用having筛选出02的
SELECT SUM(s_score) FROM score
GROUP BY c_id HAVING c_id='02'
-- 附：查看所有课程的总成绩
SELECT c_id '课程', SUM(s_score) "总成绩" FROM score
GROUP BY c_id
题目9：查询所有课程成绩小于60分的学生学号、姓名
-- 思路：如果一开始就想着用group by对学生分组，再看这个学生的每条成绩，这个是做不了的
-- 因为没有像SUM、AVG、COUNT这样的统计函数去遍历每条成绩
-- 可以先找出低于60分的记录，看看有哪些学生，再观察一下这些学生是不是就只选了这几门课
SELECT a.s_id '学号', t.s_name '姓名' FROM
(SELECT s_id, COUNT(c_id) AS cnt FROM score
WHERE s_score<60
GROUP BY s_id) AS a -- 表1
INNER JOIN -- 1，2连接
(SELECT s_id, COUNT(c_id) AS cnt FROM score
GROUP BY s_id) AS b -- 表2
ON a.s_id=b.s_id -- 连接条件
INNER JOIN -- 2，3连接
student AS t -- 表3
ON a.s_id=t.s_id -- 连接条件
WHERE a.cnt=b.cnt -- 三个表连接之后进行筛选
题目10【重点】：查询没有学全所有课的学生的学号、姓名
-- 不要忘了啥课都没选的学生哦，所以最根本上还是要筛选student表的（left join）
SELECT st.s_id '学号', st.s_name '姓名'
FROM
student AS st
LEFT JOIN
score AS sc
ON st.s_id=sc.s_id
GROUP BY st.s_id
HAVING COUNT(DISTINCT sc.c_id)<(SELECT COUNT(DISTINCT c_id) FROM course)
题目11【重点】：查询至少有一门课与学号为01的学生所学课程相同的学生的学号和姓名
-- 最后的拼接姓名，可以用in的方式
SELECT s_id '学号', s_name '姓名' FROM student
WHERE s_id IN(
SELECT DISTINCT s_id FROM score
WHERE c_id IN(
SELECT c_id FROM score
WHERE s_id='01'
) AND s_id!='01'
)
-- 最后的拼接姓名，也可以用内连接的方式
SELECT a.s_id '学号', s_name '姓名' FROM
(
SELECT DISTINCT s_id FROM score
WHERE c_id IN(
SELECT c_id FROM score
WHERE s_id='01'
) AND s_id!='01'
) AS a
INNER JOIN student AS b
ON a.s_id=b.s_id
题目12【重点】：查询和01同学所学课程完全相同的同学的学号
-- 假设01同学选了（1，2，3）这三门课
-- 1、score表中所有选课的条目，如果某一条的课程超出了（1，2，3），
-- 那这个条目对应的同学，他和01同学一定不是完全相同课程
-- 2、剩下的同学，他们选课是在（1，2，3）当中，但此时一定要保证课程总数上相等才可以
SELECT s_id FROM student
WHERE s_id IN
(
SELECT s_id FROM score -- 看数量上能不能对上
WHERE s_id!='01'
GROUP BY s_id HAVING COUNT(c_id)=(SELECT COUNT(c_id) FROM score WHERE s_id='01') -- 数量对上才有进一步的可能
)
AND s_id NOT IN
(
SELECT s_id FROM score -- 这样的学生我们是不要的
WHERE c_id NOT IN ( -- 因为它有超出（1，2，3）的课程
SELECT c_id FROM score
WHERE s_id='01')
)
题目15【重点】：查询两门及以上不及格同学的学号、姓名、平均成绩
-- 查出所有不及格的条目，对s_id分组，如果该同学c_id数量>=2，输出这个同学的信息
SELECT a.s_id '学号', a.s_name '姓名', AVG(s_score) FROM -- 从哪个表呢
-- ######### 这个内连接好的表
student AS a
INNER JOIN
score AS b
ON a.s_id=b.s_id
-- #########
WHERE a.s_id IN( -- 但是呢，我们只要s_id在这个范围内的
SELECT s_id FROM score
WHERE s_score<60
GROUP BY s_id HAVING COUNT(c_id)>=2)
GROUP BY a.s_id,a.s_name
-- 仔细看的话，大的框架上还是select | from | where | group by | order by的结构，好好记住这个结构哦
题目16：检索01课程分数小于60，按分数降序排列的学生信息
SELECT a.*, b.c_id, b.s_score FROM
student AS a
INNER JOIN score AS b
ON a.s_id=b.s_id
WHERE b.c_id='01' AND b.s_score<60
ORDER BY b.s_score DESC
题目17【重点】：按平均成绩从高到低显示所有学生的所有课程的成绩及平均成绩
-- 思路1：分组得到的学号、平均成绩再拼接上score表，一个学生有多行
-- 最后的形式就类似于score再拼接上平均成绩这一列
SELECT a.s_id '学号', a.mean_score '平均成绩', b.c_id '课程', b.s_score'该课成绩' FROM
(SELECT s_id,AVG(s_score) AS mean_score FROM score
GROUP BY s_id) AS a
INNER JOIN score AS b
ON a.s_id=b.s_id
ORDER BY a.mean_score DESC
-- 思路2：把每个课程作为一列属性，这样一个同学的多门课程成绩就不用形成多行了
-- 一个同学就一行信息了。跟思路1比起来，行数减少、列数增多。
-- 但是这里用MAX统计函数来展示列属性的写法，还是有点奥妙的
-- （换成MIN/SUM/AVG也可以，因为只有一条数据）
SELECT
s_id '学号',
SUM(case when c_id='01' THEN s_score ELSE NULL END) '语文',
AVG(case when c_id='02' THEN s_score ELSE NULL END) '数学',
MIN(case when c_id='03' THEN s_score ELSE NULL END) '英语',
AVG(s_score) '平均成绩'
FROM score
GROUP BY s_id
ORDER BY AVG(s_score) DESC
题目18【重点】：查询各科成绩最高分、最低分、平均分、及格率、中等率、优良率、优秀率
-- 及格：60及以上，中等：70-80，优良：80-90，优秀：90及以上
SELECT
s.c_id '课程号',
c.c_name '课程名',
MAX(s.s_score) '最高分',
MIN(s.s_score) '最低分',
AVG(s.s_score) '平均分',
SUM(case when s_score>=60 THEN 1 ELSE 0 END)/COUNT(s.s_score) '及格率',
SUM(case when s_score>=70 and s_score<80 THEN 1 ELSE 0 END)/COUNT(s.s_score) '中等率',
SUM(case when s_score>=80 and s_score<90 THEN 1 ELSE 0 END)/COUNT(s.s_score) '优良率',
SUM(case when s_score>=90 THEN 1 ELSE 0 END)/COUNT(s.s_score) '优秀率'
FROM
score AS s
INNER JOIN course AS c
ON s.c_id=c.c_id
GROUP BY s.c_id
-- 结合第3题，group by之后其他字段的数据信息，虽然说是看不到，但是想利用其数据大小信息还是可以的
-- 就像这里，虽然我们没法直接查看展示s_score的信息
-- 但是统计函数是可以用的啊，在里边加上case when就可以对s_score的值做一些利用，相当于间接查看了呗
题目19：对各科成绩进行排名，按排名先后展示条目
-- 窗口函数的题目，很贴近现实生活中的需求
-- 对成绩表的条目进行重新排序，分块，块内再升/降排序
-- rank：跳跃排序、dense_rank：连续排序、row_number：没有重复值的排序
SELECT c_id '课程号', s_id '学号', s_score '成绩', RANK() over(PARTITION BY c_id ORDER BY s_score DESC) AS '排名'
FROM score
题目20：查询学生的总成绩并进行排名
SELECT s_id '学号', SUM(s_score) '总成绩'
FROM score
GROUP BY s_id
ORDER BY SUM(s_score) DESC -- 【ORDER BY 总成绩 DESC】 这样写也可以，但是总成绩不能加引号哈，加上就不对了
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
