【烈日炎炎战后端】MySQL编程（3.6万字）

最新推荐文章于 2021-01-18 19:00:45 发布

置顶徐明曉

最新推荐文章于 2021-01-18 19:00:45 发布

阅读量1k

点赞数

分类专栏： Java后端

本文链接：https://blog.csdn.net/weixin_43518038/article/details/107831214

版权

Java后端专栏收录该内容

20 篇文章 1 订阅

订阅专栏

SQL分支及常用命令脑图：link.

查询时用到的表格

表结构如下

学生课：Student(Sno，Sname，Ssex，Sage，Sdept)
课程表：Student(Cno，Cname，Cpno，Ccredit)
学生选课表：SC(Sno，Cno，Ssex，Grade)

注：加粗字体为主关键字.

(a) Student

学号(Sno)	姓名（Sname）	性别（Ssex）	年龄（Sage）	所在系（Sdept）
20125121	李勇	男	20	CS
201215122	刘晨	女	19	CS
201215122	王敏	女	18	MA
201215125	张立	男	19	IS

(b) Course

课程号(Cno)	课程名（Cname）	先行课（Cpno）	学分（Ccredit）
1	数据库	5	4
2	数学	null	2
3	信息系统	1	4
4	操作系统	6	3
5	数据结构	7	4
6	数据处理	null	2
7	PASCAL语言	6	4

（c）SC

学号（Sno)	课程号（Cno）	成绩（Crade）
201215121	1	92
201215121	2	85
201215121	3	88
201215122	2	90
201215122	3	80

另外，我们要了解这四个词的含义：模式，基本表，视图，索引.

1．模式定义与删除

SQL的数据定义语句：

操作对象	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA	无
表	CREATE TABLE	DROP TABLE	ALTER TABLE
视图	CREATE VIEW	DROP VIEW	无
索引	CREATE INDEX	DROP INDEX	ALTER INDEX

1.1 定义模式

在SQL中，模式定义语句如下：

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;

【例1.1】为用户WANG定义一个学生-课程模式S-T：
CREATE SCHEMA ”S-T” AUTHORIZATION WANG;

1.2 模式删除

在SQL中，模式删除语句如下：

DROP SCHEMA <模式名> <CASCADE|RESTRICT>;

【例1.2】删除模式ZHANG和其中定义的表TAB1；
DROP SCHEMA ZHANG CASCADE;

2. 基本表操作

2.1 定义基本表

在SQL中，定义基本表语句如下：

CREATE TABLE <表名> ( <列名> <数据类型>[列级完整性约束条件]，
　　　　　　　　　　　[列名> <数据类型>[列级完整性约束条件]，
　　　　　　　　　　　…
　　　　　　　　　　　<表级完整性约束条件>]);

【例 2.1】建立学生选课表SC，表©.
CREATE TABLE SC
　(Sno CHAR(9)， 　　　　　 //定义Sno，数据类型为CHAR，字节不多于9
　Cno CHAR(4)，
　Grade SMALLINT， 　　　　//定义Grade，数据类型为SMALLINT
　PRIMARY KEY(Sno， Cno)， //主码由两个属性构成，必须作为表级完整性进行定义
　FOREIGE KEY (Sno) REFERENCES Student(Sno)，
//表级完整性定义，Sno为外码，被参照表为Student
　FOREIGE KEY (Cno) REFERENCES Course(Cno)，
//表级完整性定义，Cno为外码，被参照表为Course
);

2.2 修改基本表

略

2.3 删除基本表

略

2.4索引的建立与删除

略

3．数据查询

其一般格式为：

SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] …
FROM <表名或视图名> [，<表名或视图名>] |(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];

含义为：

根据WHERE子句的表达式从FROM子句指定的表中找出满足条件的元组，再按照SELECT子句中的目标表达式选出元组中的属性值形成结果表.
如果有GROUP BY表达式，则将结果按照<列名1>分组.HAVING 为输出指定条件的组.
如果有ORDER BY表达式，则将结果按照 <列名2>升序或者降序.

3.1 单表查询：仅涉及一个表的查询

WHERE常用的查询条件

查询条件	谓词
比较	=，>，<，>=，<=，!=，<>，!>，!<;NOT加上述比较符号
确定范围	BETWEEN AND， NOT BETWEEN AND
确定集合	IN， NOT IN
字符匹配	LIKE，NOT LIKE（%任意长度；_单个字符）
空值	IS NULL， IS NOT NULL
多重条件（逻辑运算）	AND， OR， NOT

[例 3.1] 查询年龄在20岁以下的学生姓名和年龄
SELECT Sname， Sage
　FROM Student
　WHERE Sage<20;

3.2　连接查询：涉及两个表以上的查询

[例3.2.1：等值与非等值连接查询] 查询每个学生及其选修课程的情况
SELECT Student，SC. 　　　　　　　 / /若把列中重复的属性列去掉改为自然连接：则为** 　Student.Sno，Sname，Ssex，Sage，Sdpet，Cno，Grade 　
　FROM Student， SC　　　　　　　　//容易混淆时，属性名前应该加前缀
　WHERE Student.Sno=SC.Sno;　　//将Student与SC中同一学生的元组连接起来

[例3.2.2：等值与非等值连接查询] 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
SELECT Student.Sno，Sname
　FROM Student，SC
　WHERT Student.Sno=SC.Sno AND SC.Cno=’2’AND SC.Grade>90;

[例3.2.3：自身连接] 查询每一门课的间接先修课（即先修课的先修课）
SELECT FIRST.Cno，SECOND Cpno //注意要编号
　FROM Course FIRST.Course SECOND
　WHERE FIRST.Cpno=SECOND.Cno;

[例3.2.4：外连接：将不满足条件的元组作为结果输出] 改进例3.2.1
SELECT Student.Sno，Sname，Ssex，Sage，Sdpet，Cno，Grade
　FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);

[例3.2.5：多表连接] 查询每个学生的学号，姓名，选修课的课程名及成绩
SELECT Student，Sno，Sname，Cname，Grade
　FROM Student，SC，Course
　WHERE Student.Sno=SC.SnoAND SC.Cno=Course.Cno;

3.3　嵌套查询

[例3.3.1：带有IN谓词的子查询]查询“刘晨”在同一个系学习的学生
首先确定“刘晨”所在的系名
SELECT Sdept
　FROM Student
　WHERE Sname=’刘晨’；
再确定CS系的学生
SELECT Sno，Student，Sdept
　FROM Student
　WHERE Sdept=’CS’;
最后第一步查询嵌入到第二步中
SELECT Sno，Sname，Sdept
　FROM Student
　WEHRE Sdept IN
　(SELECT Sdept
　FROM Student
　WHERE Sname=’刘晨’
)；
//子查询不依赖父查询，成为不相关子查询

[例3.2.2：带有比较运算符的子查询]　是相关子查询

[例3.2.3：带有ANY(SOME)或ALL谓词的子查询] 略

[例3.2.4：带有EXISTS谓词的子查询]　带有EXISTS谓词的子查询不返回数据，只返回真（内层不为空）和假（内层为空）.

[例5：基于派生表查询 ]略

3.4 数据更新

[例3.4.1：插入数据INSERT]插入一条选课记录（‘201215128’，’1’）
INSERT
　INTO SC(Sno，Cno)
　 VALUES(‘201215128’，’1’)

[例3.4.2：修改数据UPDATE]将学生201215121的年龄改为22岁

UPDATE Student
　 SET Sage=22
　 WHERE Sno=’201215121’;
　
[例3.4.3：删除数据DELETE]删除学号为201215128的学生记录
DELETE
　FROM Student
　WHERE Sno=’201215128’;

4．视图

略

5. SQL连接查询

https://www.cnblogs.com/cthon/p/9075774.html
MySQL 连接的使用

在前几章节中，我们已经学会了如何在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。

本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。

你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
**LEFT JOIN（左连接）：**获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

在命令提示符中使用 INNER JOIN

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

等价于：

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;

MySQL LEFT JOIN

MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。

6. 分页查询

LIMIT 子句可以被用于指定 SELECT 语句返回的记录数。需注意以下几点：

1、第一个参数指定第一个返回记录行的偏移量，注意从 0开始
2、第二个参数指定返回记录行的最大数目
3、如果只给定一个参数：它表示返回最大的记录行数目
4、第二个参数为 -1 表示检索从某一个偏移量到记录集的结束所有的记录行
5、初始记录行的偏移量是 0(而不是 1)

https://blog.csdn.net/happypX/article/details/94504057

https://www.jb51.net/article/109749.htm

在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了这样一个功能。

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)：为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15

为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：

mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

如果只给定一个参数，它表示返回最大的记录行数目：

mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行

换句话说，LIMIT n 等价于LIMIT 0,n。

Mysql的分页查询语句的性能分析

MySql分页sql语句，如果和MSSQL的TOP语法相比，那么MySQL的LIMIT语法要显得优雅了许多。使用它来分页是再自然不过的事情了。

最基本的分页方式：

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

在中小数据量的情况下，这样的SQL足够用了，唯一需要注意的问题就是确保使用了索引：举例来说，如果实际SQL类似下面语句，那么在category_id, id两列上建立复合索引比较好：

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10

子查询的分页方式：

随着数据量的增加，页数会越来越多，查看后几页的SQL就可能类似：

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10

一言以蔽之，就是越往后分页，LIMIT语句的偏移量就会越大，速度也会明显变慢。

此时，我们可以通过子查询的方式来提高分页效率，大致如下：

SELECT * FROM articles WHERE id >= 
(SELECT id FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10

JOIN分页方式

SELECT * FROM `content` AS t1 
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2 
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

经过我的测试，join分页和子查询分页的效率基本在一个等级上，消耗的时间也基本一致。 explain SQL语句：

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 
1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where
2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index

为什么会这样呢？因为子查询是在索引上完成的，而普通的查询时在数据文件上完成的，通常来说，索引文件要比数据文件小得多，所以操作起来也会更有效率。

实际可以利用类似策略模式的方式去处理分页，比如判断如果是一百页以内，就使用最基本的分页方式，大于一百页，则使用子查询的分页方式。

【MySQL命令脑图】

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-y4Nx9Coz-1596676614139)(X:\Users\xu\Desktop\20190305122455799.png)]

【MySQL学习日志】

show tables;
show table status;
show  databases;
use community;
show databases;
use community;
show tables;
show columns FROM comment;
select * from comment;
select user_id from comment;
select distinct user_id from comment;
select distinct user_id from comment limit 1;
select distinct user_id from comment limit 2;
select distinct user_id from comment limit 2，3;
select distinct user_id from comment limit 2;
select distinct user_id from comment limit 2，3;
select  comment.user_id from comment;
select * from user;
select username from user;
select username from user order by username desc; 
select username from user where username='zzz';
select username from user where username<>'zzz';
select * from user;
select * from user where status between 0 and 1;
select * from user where type=0 and status=1;
select * from user where type =0 or status=1;
# 如果要列出价格为10美元以上并且由1002或1003制造的所有产品.
# select prod_name，prod_price from products where vend_id=1003 and pro_price>=10;
#以上错误 SQL语言优先处理and，必须使用优先级更高的()
# select prod_name，prod_price from products where (vend_id=1002 or vend_id=1003) and pro_price>=10;
# IN 操作符与or类似
# 通配符匹配字符串
select * from user where password like '_5%';
select * from user where salt regexp '\\5' order by username desc;
#6.11
#\\：匹配特殊字符 [:lower:]:匹配小写字母 ?使得g可选
select * from user where username regexp '[a-z]hang';
#匹配连在一起的3位数字 {}前要带[]
select * from user where id regexp '[[:digit:]]{3}' order by id;
#定位符
#创建计算字段 使用 concat可以对字段进行拼接
select Concat(username，password) from user order by id desc;
#使用数据处理函数 使用rtrim()可以去除列值右边的空格
# uppar()可以将文本转化为大写
# 汇总数据：
#聚集函数
#1.AVG()函数
select * from user;
select AVG(id) from user;
#2.COUNT()函数
select COUNT(id) from user;
#3.MAX()函数 MIN()函数
select MAX(id) from user;
select MIN(id) from user;
select id from user order by id desc;
#4.SUM()函数
select SUM(id) from user;
#使用别名，使用AS关键字
#数据分组，不适用group by只能查询一种salt的id数量
select count(id) AS num_id from user where salt ='49f10';
#根据行的某一列的值进行分组
select salt，count(id) AS num_id from user group by salt order by salt;
#having过滤分组，where过滤行
select salt，count(id) AS num_id from user group by  salt having count(id)>=2;
#子查询 IN
# 1.利用子查询进行过滤
select id from user;
select id from user where id in(1，11);
select username  from user where id in(select id from message where from_id ='1');
# 2.作为计算字段使用子查询
#相关子查询：涉及外部查询的子查询
#联结表：为什么要使用联结？
#https://www.runoob.com/mysql/mysql-join.html
# 等值联结
select * from user;
select * from comment;
select username，user_id from  user，comment where user.id=comment.id;
#在使用以下语法时，可以明确联结条件.
#MySQL left join 与 join 有所不同.
#MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据.
select username，user_id from user LEFT join comment on user.id=comment.id;
select username，user_id from  user，comment;
# 内部联结 INNER JOIN ON 可以明确指定联结的类型
# select返回
#联结多个表
# 高级联结
# union 组合查询
# 全文本搜索 什么是全文本搜索？
# 插入数据INSERT更新数数据UPADATE删除数据DELETE
select * from  user order by id desc;
INSERT INTO user(username)values('2118327937');
INSERT INTO user values('110');#必须有null去逐一匹配
UPDATE user SET username='1206512593@qq.com' where password='hello';
DELETE FROM user WHERE username='2118327937';

MySQL练习题

https://www.jianshu.com/p/476b52ee4f1b

网上流传较广的50道SQL训练，奋斗了不知道多久终于写完了。前18道题的难度依次递增，从19题开始的后半部分算是循环练习和额外function的附加练习，难度恢复到普通状态。
第9题非常难，我反正没有写出来，如果有写出来了的朋友还请赐教。
这50道里面自认为应该没有太多错误，而且尽可能使用了最简单或是最直接的查询，有多种不相上下解法的题目我也都列出了，但也欢迎一起学习的朋友进行讨论和解法优化啊~

数据表介绍

学生表 Student(SId,Sname,Sage,Ssex)
SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
课程表 Course(CId,Cname,TId)
CId 课程编号,Cname 课程名称,TId 教师编号
教师表Teacher(TId,Tname)
TId 教师编号,Tname 教师姓名
成绩表SC(SId,CId,score)
SId 学生编号,CId 课程编号,score 分数

学生表 Student

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

科目表 Course

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

教师表 Teacher

create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

成绩表 SC

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

1. 50道练习题目

查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

# 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
# as的意思为命名
# r的意思为命名
select * from Student RIGHT JOIN (
    select t1.SId, class1, class2 from
          (select SId, score as class1 from sc where sc.CId = '01') as t1, 
          (select SId, score as class2 from sc where sc.CId = '02') as t2
    where t1.SId = t2.SId AND t1.class1 > t2.class2
)r 
on Student.SId = r.SId;

1.1 查询同时存在" 01 “课程和” 02 "课程的情况

# 查询同时存在" 01 "课程和" 02 "课程的情况
select * from 
    (select * from sc where sc.CId = '01') as t1, 
    (select * from sc where sc.CId = '02') as t2
where t1.SId = t2.SId;

1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

select * from 
(select * from sc where sc.CId = '01') as t1
left join 
(select * from sc where sc.CId = '02') as t2
on t1.SId = t2.SId;

1.3 查询不存在" 01 “课程但存在” 02 "课程的情况

#查询不存在" 01 "课程但存在" 02 "课程的情况
select * from sc
where sc.SId not in (
    select SId from sc 
    where sc.CId = '01'
) 
AND sc.CId= '02';

查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

#查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
#这里只用根据学生ID把成绩分组，对分组中的score求平均值，最后在选取结果中AVG大于60的即可. 注意，这里必须要给计算得到的AVG结果一个alias.（AS ss）
#得到学生信息的时候既可以用join也可以用一般的联合搜索
select Student.SId, Student.Sname, r.ss from Student right join(
      select SId, AVG(score) AS ss from sc
      GROUP BY SId
      HAVING AVG(score)> 60
)r on Student.SId = r.SId;

查询在 SC 表存在成绩的学生信息

#查询在 SC 表存在成绩的学生信息
select DISTINCT student.* from student join sc on student.SId =SC.SId; 
#DISTINCT去重

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

#查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select student.sid, student.sname,r.coursenumber,r.scoresum
from student,
(select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc 
group by sc.sid) as r
where student.sid = r.sid;

4.1 查有成绩的学生信息

# 查有成绩的学生信息
select distinct student.* from student,sc where student.sid=sc.sid; 
select * from student
where student.sid in (select sc.sid from sc);

查询「李」姓老师的数量

#查询「李」姓老师的数量
select count(tid) as count_tid from teacher group by tname having tname like "李%";
#y由于教师表中「李」姓老师不重复
select count(*)
from teacher
where tname like '李%';

查询学过「张三」老师授课的同学的信息

#查询学过「张三」老师授课的同学的信息
select student.* from student,teacher,course,sc
where 
    student.sid = sc.sid 
    and course.cid=sc.cid 
    and course.tid = teacher.tid 
    and tname = '张三';

查询没有学全所有课程的同学的信息

#查询没有学全所有课程的同学的信息
select * from student 
where student.sid not in(
   select sc.sid from sc
   group by sc.sid
   having count(sc.cid)=(select count(cid) from course)
);

查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

#查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select * from student 
where student.sid in (
    select sc.sid from sc 
    where sc.cid in(
        select sc.cid from sc 
        where sc.sid = '01'
    )
);

查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

查询没学过"张三"老师讲授的任一门课程的学生姓名

#查询没学过"张三"老师讲授的任一门课程的学生姓名
select distinct student.sname from student where student.sid not in(
select  sc.sid from sc where sc.cid in(
select course.cid from
course where course.tid in(
select teacher.tid from teacher where teacher.tname="张三"
)
)
);
#题解
select * from student
    where student.sid not in(
        select sc.sid from sc where sc.cid in(
            select course.cid from course where course.tid in(
                select teacher.tid from teacher where tname = "张三"
            )
        )
    );
#联合查询
select * from student
where student.sid not in(
    select sc.sid from sc,course,teacher 
    where
        sc.cid = course.cid
        and course.tid = teacher.tid
        and teacher.tname= "张三"
);

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

# 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.sid,student.sname,avg(sc.score) from 
student,sc 
where student.sid=sc.sid and sc.score<60
group by sc.sid 
having count(*)>1;

select student.sid, student.sname, AVG(sc.score) from student,sc
where 
    student.sid = sc.sid and sc.score<60
group by sc.sid 
having count(*)>1;

检索" 01 "课程分数小于 60，按分数降序排列的学生信息

# 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select distinct student.* from student,sc where student.sid=sc.sid and sc.cid="01" and  sc.score<60 order by sc.score desc;
select student.*, sc.score from student, sc
where student.sid = sc.sid
and sc.score < 60
and cid = "01"
ORDER BY sc.score DESC;

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

# 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select * from sc left join (
select sc.sid,avg(sc.score) as avg_score from sc
group by sid)r
on sc.sid=r.sid
order by avg_score desc;

查询各科成绩最高分、最低分和平均分：

# 查询各科成绩最高分、最低分和平均分
select sc.cid, max(sc.score) as score_max, min(sc.score) as score_min,avg(sc.score) as score_avg
from sc group by sc.cid;

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

查询学生的总成绩，并进行排名，总分重复时保留名次空缺

16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
查询各科成绩前三名的记录
查询每门课程被选修的学生数
查询出只选修两门课程的学生学号和姓名
查询男生、女生人数
查询名字中含有「风」字的学生信息
查询同名同性学生名单，并统计同名人数
查询 1990 年出生的学生名单
查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
查询不及格的课程
查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
求每门课程的学生人数
成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
查询每门功成绩最好的前两名
统计每门课程的学生选修人数（超过 5 人的课程才统计）。
检索至少选修两门课程的学生学号
查询选修了全部课程的学生信息
查询各学生的年龄，只按年份来算
按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
查询本周过生日的学生
查询下周过生日的学生
查询本月过生日的学生
查询下月过生日的学生

2.答案

1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
因为需要全部的学生信息，则需要在sc表中得到符合条件的SId后与student表进行join，可以left join 也可以 right join

select * from Student RIGHT JOIN (
    select t1.SId, class1, class2 from
          (select SId, score as class1 from sc where sc.CId = '01')as t1, 
          (select SId, score as class2 from sc where sc.CId = '02')as t2
    where t1.SId = t2.SId AND t1.class1 > t2.class2
)r 
on Student.SId = r.SId;

select * from  (
    select t1.SId, class1, class2 
    from
        (SELECT SId, score as class1 FROM sc WHERE sc.CId = '01') AS t1, 
        (SELECT SId, score as class2 FROM sc WHERE sc.CId = '02') AS t2
    where t1.SId = t2.SId and t1.class1 > t2.class2
) r 
LEFT JOIN Student
ON Student.SId = r.SId;

1.1 查询同时存在" 01 “课程和” 02 "课程的情况

select * from 
    (select * from sc where sc.CId = '01') as t1, 
    (select * from sc where sc.CId = '02') as t2
where t1.SId = t2.SId;

1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
这一道就是明显需要使用join的情况了，02可能不存在，即为left join的右侧或right join 的左侧即可.

select * from 
(select * from sc where sc.CId = '01') as t1
left join 
(select * from sc where sc.CId = '02') as t2
on t1.SId = t2.SId;

select * from 
(select * from sc where sc.CId = '02') as t2
right join 
(select * from sc where sc.CId = '01') as t1
on t1.SId = t2.SId;

1.3 查询不存在" 01 “课程但存在” 02 "课程的情况

select * from sc
where sc.SId not in (
    select SId from sc 
    where sc.CId = '01'
) 
AND sc.CId= '02';

查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
这里只用根据学生ID把成绩分组，对分组中的score求平均值，最后在选取结果中AVG大于60的即可. 注意，这里必须要给计算得到的AVG结果一个alias.（AS ss）
得到学生信息的时候既可以用join也可以用一般的联合搜索

select student.SId,sname,ss from student,(
    select SId, AVG(score) as ss from sc  
    GROUP BY SId 
    HAVING AVG(score)> 60
    )r
where student.sid = r.sid;

select Student.SId, Student.Sname, r.ss from Student right join(
      select SId, AVG(score) AS ss from sc
      GROUP BY SId
      HAVING AVG(score)> 60
)r on Student.SId = r.SId;

select s.SId,ss,Sname from(
select SId, AVG(score) as ss from sc  
GROUP BY SId 
HAVING AVG(score)> 60
)r left join 
(select Student.SId, Student.Sname from
Student)s on s.SId = r.SId;

查询在 SC 表存在成绩的学生信息

select DISTINCT student.*
from student,sc
where student.SId=sc.SId

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
联合查询不会显示没选课的学生：

select student.sid, student.sname,r.coursenumber,r.scoresum
from student,
(select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc 
group by sc.sid)r
where student.sid = r.sid;

如要显示没选课的学生(显示为NULL)，需要使用join:

select s.sid, s.sname,r.coursenumber,r.scoresum
from (
    (select student.sid,student.sname 
    from student
    )s 
    left join 
    (select 
        sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber
        from sc 
        group by sc.sid
    )r 
   on s.sid = r.sid
);

4.1 查有成绩的学生信息
这一题涉及到in和exists的用法，在这种小表中，两种方法的效率都差不多，但是请参考SQL查询中in和exists的区别分析
当表2的记录数量非常大的时候，选用exists比in要高效很多.
EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False.
结论：IN()适合B表比A表数据小的情况
结论：EXISTS()适合B表比A表数据大的情况

select * from student 
where exists (select sc.sid from sc where student.sid = sc.sid);

select * from student
where student.sid in (select sc.sid from sc);

查询「李」姓老师的数量

select count(*)
from teacher
where tname like '李%';

查询学过「张三」老师授课的同学的信息
多表联合查询

select student.* from student,teacher,course,sc
where 
    student.sid = sc.sid 
    and course.cid=sc.cid 
    and course.tid = teacher.tid 
    and tname = '张三';

查询没有学全所有课程的同学的信息
因为有学生什么课都没有选，反向思考，先查询选了所有课的学生，再选择这些人之外的学生.

select * from student
where student.sid not in (
  select sc.sid from sc
  group by sc.sid
  having count(sc.cid)= (select count(cid) from course)
);

查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
这个用联合查询也可以，但是逻辑不清楚，我觉得较为清楚的逻辑是这样的：从sc表查询01同学的所有选课cid–从sc表查询所有同学的sid如果其cid在前面的结果中–从student表查询所有学生信息如果sid在前面的结果中

select * from student 
where student.sid in (
    select sc.sid from sc 
    where sc.cid in(
        select sc.cid from sc 
        where sc.sid = '01'
    )
);

9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
不会做。

10.查询没学过"张三"老师讲授的任一门课程的学生姓名
仍然还是嵌套，三层嵌套，或者多表联合查询

select * from student
    where student.sid not in(
        select sc.sid from sc where sc.cid in(
            select course.cid from course where course.tid in(
                select teacher.tid from teacher where tname = "张三"
            )
        )
    );

select * from student
where student.sid not in(
    select sc.sid from sc,course,teacher 
    where
        sc.cid = course.cid
        and course.tid = teacher.tid
        and teacher.tname= "张三"
);

11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
从SC表中选取score小于60的，并group by sid，having count 大于1

select student.sid, student.sname, AVG(sc.score) from student,sc
where 
    student.sid = sc.sid and sc.score<60
group by sc.sid 
having count(*)>1;

检索" 01 "课程分数小于 60，按分数降序排列的学生信息
双表联合查询，在查询最后可以设置排序方式，语法为ORDER BY ***** DESC\ASC;

select student.*, sc.score from student, sc
where student.sid = sc.sid
and sc.score < 60
and cid = "01"
ORDER BY sc.score DESC;

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select *  from sc 
left join (
    select sid,avg(score) as avscore from sc 
    group by sid
    )r 
on sc.sid = r.sid
order by avscore desc;

查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select 
sc.CId ,
max(sc.score)as 最高分,
min(sc.score)as 最低分,
AVG(sc.score)as 平均分,
count(*)as 选修人数,
sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as 优良率,
sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 优秀率 
from sc
GROUP BY sc.CId
ORDER BY count(*)DESC, sc.CId ASC

按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
这一道题有点tricky，可以用变量，但也有更为简单的方法，即自交（左交）
用sc中的score和自己进行对比，来计算“比当前分数高的分数有几个”。

select a.cid, a.sid, a.score, count(b.score)+1 as rank
from sc as a 
left join sc as b 
on a.score<b.score and a.cid = b.cid
group by a.cid, a.sid,a.score
order by a.cid, rank ASC;

查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
这里主要学习一下使用变量。在SQL里面变量用@来标识。

set @crank=0;
select q.sid, total, @crank := @crank +1 as rank from(
select sc.sid, sum(sc.score) as total from sc
group by sc.sid
order by total desc)q;

统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
有时候觉得自己真是死脑筋。group by以后的查询结果无法使用别名，所以不要想着先单表group by计算出结果再从第二张表里添上课程信息，而应该先将两张表join在一起得到所有想要的属性再对这张总表进行统计计算。这里就不算百分比了，道理相同。
注意一下，用case when 返回1 以后的统计不是用count而是sum

select course.cname, course.cid,
sum(case when sc.score<=100 and sc.score>85 then 1 else 0 end) as "[100-85]",
sum(case when sc.score<=85 and sc.score>70 then 1 else 0 end) as "[85-70]",
sum(case when sc.score<=70 and sc.score>60 then 1 else 0 end) as "[70-60]",
sum(case when sc.score<=60 and sc.score>0 then 1 else 0 end) as "[60-0]"
from sc left join course
on sc.cid = course.cid
group by sc.cid;

查询各科成绩前三名的记录
大坑比。mysql不能group by 了以后取limit，所以不要想着讨巧了，我快被这一题气死了。思路有两种，第一种比较暴力，计算比自己分数大的记录有几条，如果小于3 就select，因为对前三名来说不会有3个及以上的分数比自己大了，最后再对所有select到的结果按照分数和课程编号排名即可。

select * from sc
where (
select count(*) from sc as a 
where sc.cid = a.cid and sc.score<a.score 
)< 3
order by cid asc, sc.score desc;

第二种比较灵巧一些，用自身左交，但是有点难以理解。
先用自己交自己，条件为a.cid = b.cid and a.score<b.score，其实就是列出同一门课内所有分数比较的情况。
想要查看完整的表可以

select * from sc a 
left join sc b on a.cid = b.cid and a.score<b.score
order by a.cid,a.score;

结果

查看，发现结果是47行的一个表，列出了类似 01号课里“30分小于50，也小于70，也小于80，也小于90”“50分小于70，小于80，小于90”…
所以理论上，对任何一门课来说，分数最高的那三个记录，在这张大表里，通过a.sid和a.cid可以联合确定这个同学的这门课的这个分数究竟比多少个其他记录高/低，
如果这个特定的a.sid和a.cid组合出现在这张表里的次数少于3个，那就意味着这个组合（学号+课号+分数）是这门课里排名前三的。
所以下面这个计算中having count 部分其实count()或者任意其他列都可以，这里制定了一个列只是因为比count()运行速度上更快。

select a.sid,a.cid,a.score from sc a 
left join sc b on a.cid = b.cid and a.score<b.score
group by a.cid, a.sid
having count(b.cid)<3
order by a.cid;

查询每门课程被选修的学生数

select cid, count(sid) from sc 
group by cid;

查询出只选修两门课程的学生学号和姓名
嵌套查询

select student.sid, student.sname from student
where student.sid in
(select sc.sid from sc
group by sc.sid
having count(sc.cid)=2
);

联合查询

select student.SId,student.Sname
from sc,student
where student.SId=sc.SId  
GROUP BY sc.SId
HAVING count(*)=2；

21.查询男生、女生人数

select ssex, count(*) from student
group by ssex;

查询名字中含有「风」字的学生信息

select *
from student 
where student.Sname like '%风%'

23.查询同名学生名单，并统计同名人数
找到同名的名字并统计个数

select sname, count(*) from student
group by sname
having count(*)>1;

嵌套查询列出同名的全部学生的信息

select * from student
where sname in (
select sname from student
group by sname
having count(*)>1
);

24.查询 1990 年出生的学生名单

select *
from student
where YEAR(student.Sage)=1990;

25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select sc.cid, course.cname, AVG(SC.SCORE) as average from sc, course
where sc.cid = course.cid
group by sc.cid 
order by average desc,cid asc;

26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
having也可以用来截取结果表，在这里就先得到平均成绩总表，再截取AVG大于85的即可.

select student.sid, student.sname, AVG(sc.score) as aver from student, sc
where student.sid = sc.sid
group by sc.sid
having aver > 85;

查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

select student.sname, sc.score from student, sc, course
where student.sid = sc.sid
and course.cid = sc.cid
and course.cname = "数学"
and sc.score < 60;

查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

select student.sname, cid, score from student
left join sc
on student.sid = sc.sid;

查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

select student.sname, course.cname,sc.score from student,course,sc
where sc.score>70
and student.sid = sc.sid
and sc.cid = course.cid;

30.查询存在不及格的课程
可以用group by 来取唯一，也可以用distinct

select cid from sc
where score< 60
group by cid;

select DISTINCT sc.CId
from sc
where sc.score <60;

31.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名

select student.sid,student.sname 
from student,sc
where cid="01"
and score>=80
and student.sid = sc.sid;

求每门课程的学生人数

select sc.CId,count(*) as 学生人数
from sc
GROUP BY sc.CId;

成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
用having max()理论上也是对的，但是下面那种按分数排序然后取limit 1的更直观可靠

select student.*, sc.score, sc.cid from student, teacher, course,sc 
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "张三"
having max(sc.score);

select student.*, sc.score, sc.cid from student, teacher, course,sc 
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "张三"
order by score desc
limit 1;

成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
为了验证这一题，先修改原始数据

UPDATE sc SET score=90
where sid = "07"
and cid ="02";

这样张三老师教的02号课就有两个学生同时获得90的最高分了。
这道题的思路继续上一题，我们已经查询到了符合限定条件的最高分了，这个时候只用比较这张表，找到全部score等于这个最高分的记录就可，看起来有点繁复。

select student.*, sc.score, sc.cid from student, teacher, course,sc 
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = "张三"
and sc.score = (
    select Max(sc.score) 
    from sc,student, teacher, course
    where teacher.tid = course.tid
    and sc.sid = student.sid
    and sc.cid = course.cid
    and teacher.tname = "张三"
);

查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
同上，在这里用了inner join后会有概念是重复的记录：“01 课与 03课”=“03 课与 01 课”，所以这里取唯一可以直接用group by

select  a.cid, a.sid,  a.score from sc as a
inner join 
sc as b
on a.sid = b.sid
and a.cid != b.cid
and a.score = b.score
group by cid, sid;

36.查询每门功成绩最好的前两名
同上19题

select a.sid,a.cid,a.score from sc as a 
left join sc as b 
on a.cid = b.cid and a.score<b.score
group by a.cid, a.sid
having count(b.cid)<2
order by a.cid;

37.统计每门课程的学生选修人数（超过 5 人的课程才统计）

select sc.cid, count(sid) as cc from sc
group by cid
having cc >5;

38.检索至少选修两门课程的学生学号

select sid, count(cid) as cc from sc
group by sid
having cc>=2;

查询选修了全部课程的学生信息

select student.*
from sc ,student 
where sc.SId=student.SId
GROUP BY sc.SId
HAVING count(*) = (select DISTINCT count(*) from course )

40.查询各学生的年龄，只按年份来算
不想做，一般都用41题的方法精确到天

按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select student.SId as 学生编号,student.Sname  as  学生姓名,
TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as 学生年龄
from student

42.查询本周过生日的学生

select *
from student 
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE());

查询下周过生日的学生

select *
from student 
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1;

44.查询本月过生日的学生

select *
from student 
where MONTH(student.Sage)=MONTH(CURDATE());

45.查询下月过生日的学生

select *
from student 
where MONTH(student.Sage)=MONTH(CURDATE())+1;

3. mysql中如何查看sql语句是否用到索引

1、操作步骤
1.1 使用explain ，放在sql前面
在这里插入图片描述
2、解释
我们只需要注意一个最重要的type 的信息很明显的提现是否用到索引：

type结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

possible_keys：sql所用到的索引

key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

rows: 显示MySQL认为它执行查询时必须检查的行数

4.mysql查询什么时候用on什么时候用where？

https://blog.csdn.net/cheyuan4575/article/details/100720316

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用left jion时，on和where条件的区别如下：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

表1：tab2

id	size
1	10
2	20
3	30

表2：tab2

size	name
10	AAA
20	BBB
20	CCC

两条SQL:
1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

第一条SQL的过程：1、中间表 on条件: tab1.size = tab2.sizetab1.idtab1.sizetab2.sizetab2.name11010AAA22020BBB22020CCC330(null)(null)||2、再对中间表过滤 where 条件： tab2.name=’AAA’tab1.idtab1.sizetab2.sizetab2.name11010AAA

第二条SQL的过程：1、中间表 on条件: tab1.size = tab2.size and tab2.name=’AAA’ (条件不为真也会返回左表中的记录)tab1.idtab1.sizetab2.sizetab2.name11010AAA220(null)(null)330(null)(null)

其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

on、where、having的区别

on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后。有时候如果这先后顺序不影响中间结果的话，那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的。根据上面的分析，可以知道where也应该比having快点的，因为它过滤数据后才进行sum，所以having是最慢的。但也不是说having没用，因为有时在步骤3还没出来都不知道那个记录才符合要求时，就要用having了。在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢。如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里 JOIN联表中ON,WHERE后面跟条件的区别对于JOIN的连表操作，这里就不细述了，当我们在对表进行JOIN关联操作时，对于ON和WHERE后面的条件，不清楚大家有没有注意过，有什么区别，可能有的朋友会认为跟在它们后面的条件是一样的，你可以跟在ON后面，如果愿意，也可以跟在WHERE后面。它们在ON和WHERE后面究竟有一个什么样的区别呢？在JOIN操作里，有几种情况。LEFT JOIN,RIGHT JOIN,INNER JOIN等。为了清楚的表达主题所描述的问题，我简要的对LEFT,RIGHT,INNER这几种连接方式作一个说明。下面就拿一个普通的博客系统的日志表(post)和分类表(category)来描述吧。这里我们规定有的日志可能没有分类，有的分类可能目前没有属于它的文章。1. LEFT JOIN:（保证找出左联表中的所有行）查出所有文章，并显示出他们的分类：SELECT p.title,c.category_name FROM post p LEFT JOIN category c ON p.cid = c.cid2. RIGHT JOIN:（保证找出右联表中的所有行）查询所有的分类，并显示出该分类所含有的文章数。SELECT COUNT(p.id),c.category_name FROM post p RIGHTJOIN category c ON p.pid = c.cid3. INNER JOIN（找出两表中关联相等的行）查询有所属分类的日志。（即那些没有所性分类的日志文章将不要我们的查询范围之内）。SELECT p.title,c.category_name FROM post p INNER JOIN category c ON p.cid = c.cid.这种情况和直接两表硬关联等价。现在我们回过头来看上面的问题。对于第一种情况，如果我们所ON 的条件写在WHERE 后面，将会出现什么情况呢？即：SELECT p.title,c.category_name FROM post p LEFT JOIN category c WHERE p.cid = c.cid对于第二种情况，我们同样按照上面的书写方式。SELECT COUNT(p.id),c.category_name FROM post p RIGHTJOIN category c WHERE p.pid = c.cid如果运行上面的SQL语句，就会发现，它们已经过滤掉了一些不满足条件的记录，可能在这里，大家会产生疑问了，不是用了LEFT和RIGHT吗？它们可以保证左边或者右边的所有行被全部查询出来，为什么现在不管用了呢？对于出现这种的问题，呵呵！是不是觉得有些不可思议。出现这种的问题，原因就在WHERE和ON这两个关键字后面跟条件。好了，现在我也不调大家味口了，给大家提示答案吧。对于JOIN参与的表的关联操作，如果需要不满足连接条件的行也在我们的查询范围内的话，我们就必需把连接条件放在ON后面，而不能放在WHERE后面，如果我们把连接条件放在了WHERE后面，那么所有的LEFT,RIGHT,等这些操作将不起任何作用，对于这种情况，它的效果就完全等同于INNER连接。对于那些不影响选择行的条件，放在ON或者WHERE后面就可以。记住：所有的连接条件都必需要放在ON后面，不然前面的所有LEFT,和RIGHT关联将作为摆设，而不起任何作用。

5.on、where、having的区别

on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后。有时候如果这先后顺序不影响中间结果的话，那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的。

根据上面的分析，可以知道where也应该比having快点的，因为它过滤数据后才进行sum，所以having是最慢的。但也不是说having没用，因为有时在步骤3还没出来都不知道那个记录才符合要求时，就要用having了。

在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢。

如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。

在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里

6. sql语句什么时候需要加前缀

你说的是abc.table，的 abc吧，这个是用户名，是这张表所属于的用户。
我举个例子：
比如有a，b两个用户。b用户下有一张c表。a用户下也有一张c表。
如果a用户写select * from c,那么这个c查询的是a用户下的c表。
如果a用户写为select * from b.c，那么这次a用户查询的就是b用户下的c表，不过这里有一个前提，a用户有查询b用户下c表的权限。
oracle下因为遵循最小权限原则，比如b用户对于b用户下的c表可以进行增删改查，但是对于另外一个人来说能查询就可以，如果让他去删除和改，那么可能会有问题产生，所以只给查询的权限，所以就会出现这个问题。

7.SQL中GROUP BY用法示例

https://www.jianshu.com/p/8b135d373df1

概述

GROUP BY我们可以先从字面上来理解，GROUP表示分组，BY后面写字段名，就表示根据哪个字段进行分组，如果有用Excel比较多的话，GROUP BY比较类似Excel里面的透视表。
GROUP BY必须得配合聚合函数来用，分组之后你可以计数（COUNT），求和（SUM），求平均数（AVG）等。

常用聚合函数

count() 计数
sum() 求和
avg() 平均数
max() 最大值
min() 最小值

语法

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

例子

接下来我们将通过例子进行理解：
我们现在有一张dept_emp表共四个字段，分别是emp_no(员工编号)，dept_no(部门编号)，from_date(起始时间)，to_date(结束时间)，记录了员工在某一部门所处时间段，to_date等于9999-01-01的表示目前还在职。

image.png

部门人数

我们现在想知道每个部门有多少名在职员工，步骤如下：

筛选在职员工 where to_date='9999-01-01';
对部门进行分组group by dept_no
对员工进行计数 count(emp_no)

完整语句如下：

SELECT
  dept_no as 部门,
  count( emp_no) as 人数
FROM
  dept_emp 
WHERE
  to_date = '9999-01-01' 
GROUP BY
  dept_no

结果

image.png

部门名称

我们上一步分组之后得到的结果是部门编号，下一步我们可以通过departments去关联出部门名称，语句如下：

SELECT
    ( SELECT d.dept_name FROM departments d WHERE de.dept_no = d.dept_no ) AS 部门,
    count( de.emp_no ) AS 人数 
FROM
    dept_emp de 
WHERE
    de.to_date = '9999-01-01' 
GROUP BY
    de.dept_no

结果

image.png

HAVING

当然提到GROUP BY 我们就不得不提到HAVING，HAVING相当于条件筛选，但它与WHERE筛选不同，HAVING是对于GROUP BY对象进行筛选。
我们举个例子：
每个部门人数都有了，那如果我们想要进一步知道员工人数大于30000的部门是哪些，这个时候就得用到HAVING了。
语句如下：

SELECT
    ( SELECT d.dept_name FROM departments d WHERE de.dept_no = d.dept_no ) AS 部门,
    count( de.emp_no ) AS 人数 
FROM
    dept_emp de 
WHERE
    de.to_date = '9999-01-01' 
GROUP BY
    de.dept_no 
HAVING
    count( de.emp_no ) > 30000