数据库学习

— 查询所有的数据库： show databases;
— 选择访问的数据库： use mysql;
— 查询数据库中的表： show tables;

数据库(存储数据的仓库)增删改查
1.创建数据库
格式： CREATE DATABASE 数据库名 CHARSET=utf8;
例：创建mystu数据库
CREATE DATABASE mystu;

2.删除数据库
格式： DROP DATABASE 数据库名;
例：删除mystu数据库
DROP DATABASE mystu;

3.数据库不允许修改名字（先备份-再还原）
mysqldump -uroot -p密码 要备份的数据库 > 备份的.sql
mysql -uroot -p密码 新数据库名 < 备份的.sql文件的绝对路径
注意：还原时新数据库名必须先创建好，字符集必须一致

4.查询数据库
格式： SHOW DATABASES;

表的增删改查（必须先选择数据库-仓库）
数据类型： INT 整型|VARCHAR 变长字符型|TIMESTAMP 时间戳
主键 PRIMARY KEY|默认值 DEFAULT|非空 NOT NULL|自增序号 AUTO_INCREMENT

1. 创建表
格式： CREATE TABLE 表名(列 数据类型 [约束],
列 数据类型 [约束],…);
注意：至少要有一个列，每个列必须指定数据类型，
列与类型与约束之间空格分隔，列与列之间逗号分隔

例：创建学生表student，学号sid，姓名sname，性别sex，年龄age，其中
学号要求为整型，并设为主键；
姓名要求为变长字符，长度为20；
性别要求为枚举型，默认值为男；
年龄要求为无符号的较小整型，默认值为0。

CREATE TABLE student(
sid INT PRIMARY KEY,sname VARCHAR(20),
sex ENUM(‘男’,’女’) DEFAULT ‘男’,
age TINYINT UNSIGNED DEFAULT 0);

2.修改表名
格式： RENAME TABLE 旧表名 TO 新表名[,旧表名 TO 新表名,…];
例：修改学生表表名为stu
RENAME TABLE student TO stu;

3.查询表格式： SHOW TABLES;
查询表结构： DESC 表名;

4.删除表
格式： DROP TABLE 表名; 如果表不存在，语句报错
格式： DROP TABLE IF EXISTS 表名; 如果表不存在，语句不报错

5.复制表
格式1： CREATE TABLE 表名 LIKE 旧表名; 不能复制数据
格式2： CREATE TABLE 表名 SELECT * FROM 表名; 复制数据不能复制约束

FLOAT(),DECIMAL(),VARCHAR()

练习：创建学生表，表名student
sid为学号整型设为主键,sname姓名字符型长度20,
sex性别字符型,age较小整型,adds变长字符长度50,
scome入学时间类型为年份,dept所在系字符型长度20

CREATE TABLE student(sid INT PRIMARY KEY,
sname CHAR(20),sex CHAR(1),age TINYINT,
adds VARCHAR(50),scome YEAR,dept CHAR(20));

创建课程表，表名course
cid为课程号整型设为主键,cname课程名字符型要求取值唯一,
teacher授课老师名变长字符长度为20,phone联系方式字符型11

CREATE TABLE course(cid INT PRIMARY KEY,
cname CHAR(20) UNIQUE,teacher VARCHAR(20),
phone CHAR(11));

创建成绩表，表名score
id为整型，自增主键，sid为学号，cid为课程号，
分数grade要求为小数，长度6位，精度2位

CREATE TABLE score(
id INT PRIMARY KEY AUTO_INCREMENT,
sid INT,cid INT,grade DECIMAL(6,2));

类型： INT 整型， VARCHAR(10)变长字符， DECIMAL(6,2)小数，精度2
DATE 日期， TIME 时间， TIMESTAMP 时间戳， YEAR 年份
约束： PRIMARY KEY 主键， AUTO_INCREMENT 自增序号， UNIQUE 取值唯一
DEFAULT 默认值， NOT NULL 非空

DML语言：数据操作语言(除数值以外必须加单引号)
INSERT 增加， UPDATE 修改， DELETE 删除， SELECT 查询

1.增加数据 INSERT INTO
格式： INSERT INTO 表名(列1,列2,…) VALUES(值1,值2,…);
注意：列与值必须对应，并且类型一致，列可以省略（默认表的顺序）

例：插入一条学生信息，张三，男，20，深圳，2018，英语
INSERT INTO student(sid,sname,sex,age,adds,scome,dept)
VALUES(20011,’张三’,’男’,20,’深圳’,2018,’英语’);

表中列名可以省略，值的顺序就是表中默认顺序
INSERT INTO student
VALUES(20012,’张三’,’男’,20,’深圳’,2018,’英语’);

表中列可以无序，只要值与列对应
INSERT INTO student(sname,age,sid) VALUES(‘李四’,22,12231);

插入多条数据
INSERT INTO student(sname,age,sid)
VALUES(‘李四’,21,12232),(‘李四’,23,12233);

2.修改数据 UPDATE
格式： UPDATE 表名 SET 列=修改的值,列=修改的值 WHERE 条件表达式;
注意：指定条件，没有条件修改整个列

例：修改学号为12231的学生的性别为女
UPDATE student SET sex=’女’ WHERE sid=12231;

例：修改学号为20011的学生的年龄为21，入学时间2019年
UPDATE student SET age=21,scome=2019 WHERE sid=20011;

例：将所有学生的年龄增加1岁
UPDATE student SET age=age+1;

3.删除数据 DELETE
格式： DELETE FROM 表名 WHERE 条件表达式;
注意：数据删除是无法恢复，先备份再删除

例：删除李四的学生信息
DELETE FROM student WHERE sname=’李四’;

逻辑删除-数据没有真实的删除，原有的表中增加一列标记是否删除
本质执行的是一条update语句

删除学号为20011的学生信息（逻辑删除）
UPDATE student SET ifdelete=0 WHERE sid=20011;

—————————————
插入数据student、course、score如下：
INSERT INTO student VALUES
(19001,’宁采臣’,’男’,22,’深圳’,2019,’计算机’),
(19002,’聂小倩’,’女’,24,’北京’,2018,’数学’),
(19003,’燕赤霞’,’男’,23,’上海’,2017,’计算机’),
(19004,’姥姥’,’女’,22,’深圳’,2018,’英语’),
(19005,’小青’,’女’,19,’深圳’,2017,’中文’),
(19006,’宁采臣’,’男’,20,’云南’,2019,’中文’),
(19007,’黑山老妖’,’男’,26,’湖南’,2020,’计算机’),
(19008,’宁采蝶’,’女’,24,’海南’,2019,’英语’),
(19009,’秋蓉’,’女’,27,’湖南’,2017,’英语’),
(19010,’乔峰’,’女’,27,NULL,2017,’英语’),
(20001,’张三丰’,’男’,24,’北京’,’2015′,’中文’),
(20002,’张无忌’,’女’,20,’上海’,’2016′,’中文’),
(20003,’小龙女’,’女’,27,’湖北’,’2017′,’数学’),
(20004,’杨过’,’男’,20,’湖北武汉’,’2014′,’中文’),
(20005,’聂小倩’,’女’,18,’北京’,’2015′,’英语’),
(20006,’宁彩蝶’,’女’,23,’江西’,’2016′,NULL),
(20007,’诸葛流云’,’男’,26,’江西’,’2018′,’数学’),
(20008,’张翠山’,’男’,28,”,’2019′,’中文’),
(20009,’张五哥’,’女’,27,’null’,’2019′,’英语’),
(20010,’诸葛_亮’,’男’,’18’,’深圳’,2020,’计算机’);
INSERT INTO course(cid,cname,teacher,phone) VALUES
(1, ‘Linux基础’,’樊老师’,’135****5678′),
(2, ‘数据库’,’斛老师’,’135****4321′),
(3, ‘测试基础’,’斛老师’,’136****1234′),
(4, ‘C语言’,’雷老师’,’138****1234′),
(5, ‘自动化测试’,’文老师’,’135****4321′),
(6, ‘性能测试’,’斛老师’,’138****234′),
(7, ‘python语言’,’文老师’,’136****268′);
INSERT INTO score VALUES
(NULL,19001,1,88),(NULL,19001,6,55),(NULL,19001,3,58),
(NULL,19001,2,98),(NULL,19002,1,65),(NULL,19002,2,78),
(NULL,19002,6,100),(NULL,19002,3,55),(NULL,19003,1,85),
(NULL,19003,4,72),(NULL,19003,2,85),(NULL,19003,3,85),
(NULL,19003,5,65),(NULL,19003,6,95),(NULL,19004,3,48),
(NULL,19004,1,93),(NULL,19004,2,99),(NULL,19005,4,79),
(NULL,19005,1,86),(NULL,19005,2,76),(NULL,19005,3,76),
(NULL,19005,5,86),(NULL,19005,6,78),(NULL,19006,1,66),
(NULL,19006,2,56),(NULL,19007,1,66),(NULL,19007,6,46),
(NULL,19007,3,58),(NULL,19009,1,67),(NULL,19009,2,56),
(NULL,20001,2,85),(NULL,20002,2,85),(NULL,20003,5,85),
(NULL,20003,6,85),(NULL,20004,3,48),(NULL,20004,1,73),
(NULL,20004,2,79),(NULL,20005,4,69),(NULL,20005,1,57),
(NULL,20005,2,76),(NULL,20005,3,56),(NULL,20005,5,86),
(NULL,20005,6,98),(NULL,20006,1,76),(NULL,20006,2,56),
(NULL,20007,1,66),(NULL,20007,6,46),(NULL,20007,3,58),
(NULL,20009,1,68),(NULL,20009,2,56);
—————————————

Mysql运算符：
1.算术运算符：+ – * / %
SELECT 2+3,3-7,4*4,7/3,9%4;

2.比较运算符：> < = != <>位(真1或假0)
SELECT 4>5,5<7,3=4,4!=6,3<>5;

3.逻辑运算符： AND 与、 OR 或、 NOT 非(AND 优先级高于 OR)

SQL语言-核心查询 SELECT
1.基本查询
格式： SELECT DISTINCT 列1 AS 取别名,列2,|* FROM 表;
从表中查询信息，*代表全部列，默认查询的全部的数据

SELECT 与 FROM 之间，查询结果需要显示的列，从 FROM 后表中

例：查询所有学生的信息
SELECT sid,sname,sex,age,adds,scome,dept FROM student;
SELECT * FROM student;

1） AS 给列取别名，as可以省略
例：查询学生的信息，显示姓名，年龄
SELECT sname AS ‘姓名’,age AS ‘年龄’ FROM student;

2） DISTINCT 去重，去除相同的元组（行）
例：查询所有学生来至哪些地方
SELECT DISTINCT adds,age FROM student;

查询学生信息，显示姓名，入学时间，年龄
SELECT sname ‘姓名’,scome ‘入学时间’,age ‘年龄’ FROM student;

————————————————-
查询格式：
1. SELECT DISTINCT 列 AS 取别名 |* 默认查询结果显示all
2. FROM 表1,表2,…
3. WHERE 条件表达式1 AND|OR 条件表达式2 AND|OR 条件表达式3
4. GROUP BY 列1,列2 按某一列进行分组（列相同的合并为一组）
HAVING 分组后的条件
5. ORDER BY 列1 DESC 降序,列2 ASC 升序 对查询结果按某一列进行排序
6. LIMIT m,n 显示查询结果的行数，从m+1行开始显示n行数据

书写顺序：1、2、3、4、5、6
SELECT .FROM .[WHERE .GROUP BY .HAVING .ORDER BY .LIMIT];
执行顺序：2、3、4、1、5、6
————————————————-

2. WHERE 条件（针对表中过滤）
格式： SELECT * FROM 表 WHERE 条件表达式;

1）比较运算符
例：查询年龄大于25岁的学生信息
SELECT * FROM student WHERE age>25;
SELECT sid,sname,sex,age,adds,scome,dept FROM student WHERE age>=26;

例：查询不是2018年入学的学生信息
SELECT * FROM student WHERE scome!=2018;

2）逻辑运行符
例：查询地址位深圳的男生信息
SELECT * FROM student WHERE adds=’深圳’ AND sex=’男’;

例：查询计算机系的男生和英语系的女生
SELECT * FROM student WHERE (dept=’计算机’ AND sex=’男’) OR
(dept=’英语’ AND sex=’女’);

例：查询北京和湖南的女学生；
SELECT * FROM student WHERE (adds=’北京’ OR adds=’湖南’) AND sex=’女’;

例：查询北京或湖南的女学生；
SELECT * FROM student WHERE adds=’北京’ OR adds=’湖南’ AND sex=’女’;

例：查询不是2018年入学的学生信息
SELECT * FROM student WHERE NOT scome=2018;

3）算术运算符（列相加）

4）集合 [NOT] IN(成员1,成员2,…)
SELECT 40 IN(10,20,30);

例：查询2018年和2019年入学的学生信息
SELECT * FROM student WHERE scome=2018 OR scome=2019 OR scome=2020;
SELECT * FROM student WHERE scome IN(2018,2019,2020);

SELECT * FROM student WHERE scome NOT IN(2018,2019,2020);

5）范围 [NOT] BETWEEN 下限 AND 上限(包含边界=相对于大于等于)
例：查询年龄大于22岁并且小于26岁的学生信息
SELECT * FROM student WHERE age>22 AND age<26;
SELECT * FROM student WHERE age BETWEEN 23 AND 25;

例：查询年龄小于22岁和大于26岁的女学生信息
SELECT * FROM student WHERE age NOT BETWEEN 22 AND 26 AND sex=’女’;
SELECT * FROM student WHERE (age<22 OR age>26) AND sex=’女’;

6）空值 IS [NOT] NULL ，is不能写成等于 =NULL
NULL 与0， NULL 与空数据， NULL 与任何值相加都为 NULL
SELECT adds FROM student;

例：查询没有地址的学生信息
SELECT * FROM student WHERE adds IS NULL; 默认空
SELECT * FROM student WHERE adds=”; 空数据
SELECT * FROM student WHERE adds IS NULL OR adds=”;

例：查询有地址的学生信息
SELECT * FROM student WHERE adds!=”; 包含默认空和空数据
SELECT * FROM student WHERE NOT adds!=”; 空数据(双重否定)

7）匹配 [NOT] LIKE ，通配符：%表示0或多，_表示一个（汉字、字符）
转义符： \
例：查询学生中姓张的学生信息
SELECT * FROM student WHERE sname LIKE ‘张%’;

例：查询学生中姓张且名字为3个汉字的学生信息
SELECT * FROM student WHERE sname LIKE ‘张__’;

例：查询名字中包含【小】字的学生信息
SELECT * FROM student WHERE sname LIKE ‘%小%’;

例：查询名字中间含【小】字的学生信息
SELECT * FROM student WHERE sname LIKE ‘%_小_%’;

例：查询名字中含下划线【_】的学生信息
SELECT * FROM student WHERE sname LIKE ‘%\_%’;

3.集函数-统计函数-聚合函数
COUNT(列|*)、 SUM(列)、 AVG(列)、 MAX(列)、 MIN(列)
格式： SELECT 列,集函数(列) FROM 表 WHERE 条件表达式 ….

1） COUNT(*) 统计元组（行-多个列）的个数=相对于统计主键个数
例：查询有多少个学生？
SELECT COUNT(*) FROM student;

例：查询有多少个男学生？
SELECT sex,COUNT(*) FROM student WHERE sex=’男’;
SELECT COUNT(*) FROM (SELECT * FROM student WHERE sex=’男’) a;
SELECT SUM(IF(sex=’男’,1,0)) FROM student;

2） COUNT(列) 统计某一列的个数，不会统计默认 NULL,统计空数据
例：查询所有学生来自哪些地方
SELECT DISTINCT adds FROM student WHERE adds!=”;

例：统计有地址的学生有多少个？
SELECT COUNT(adds) FROM student WHERE adds!=”;

step1： SELECT * FROM student WHERE adds!=”; 当作新表s
step2： SELECT COUNT(*) FROM s;

例：统计有多少个不同的城市
SELECT COUNT(DISTINCT adds) FROM student WHERE adds!=”;

3） SUM(列) 求某一列的总和，列必须是数值
例：求所有男生的年龄总和
SELECT SUM(age) FROM student WHERE sex=’男’;

4） AVG(列) 求某一列的平均值，列必须是数值
例：求所有男生的平均年龄
SELECT AVG(age) FROM student WHERE sex=’男’;

5） MAX(列) 求某一列的最大值，列通常为数值或日期
例：求入学最晚的时间
SELECT MAX(scome) FROM student;

6） MIN(列) 求某一列的最小值，列通常为数值或日期
例：求学生最小年龄
SELECT MIN(age) FROM student;

4.分组 GROUP BY 列,列，对某一列进行分组 HAVING 分组后条件
格式： SELECT 列,集函数(列) FROM 表 WHERE 条件表达式 GROUP BY 列….
例：查询所有学生来自哪些地方
SELECT DISTINCT adds FROM student WHERE adds!=”;
SELECT adds FROM student WHERE adds!=” GROUP BY adds;
SELECT adds FROM student GROUP BY adds HAVING adds!=”;

WHERE 与 HAVING 区别：
WHERE 从表中找条件，不能使用集函数
HAVING 分组后表找条件，直接使用集函数

例：查询男、女生各多少人？
SELECT sex,COUNT(*) FROM student WHERE sex=’男’;
SELECT sex,COUNT(*) FROM student WHERE sex=’女’;

SELECT sex,COUNT(*) FROM student GROUP BY sex;

例：分别查询男、女的最大年龄
SELECT sex,MAX(age) FROM student GROUP BY sex;

注意：如果使用分组，在 SELECT 后面尽量不要使用 *（主键分组）
数据错误，显示表中第一次出现分组字段数据

例：查询男，女生年龄最小的学生信息(必须嵌套)
SELECT *,MIN(age) FROM student GROUP BY sex;

多列进行分组
例：查询学生中是否有同一年入学并且同一系的，显示入学时间，系
SELECT scome,dept FROM student GROUP BY scome,dept
HAVING COUNT(*)>1;

查询学生表中，相同的名字
SELECT sname FROM student GROUP BY sname HAVING COUNT(*)>1;

5.排序 ORDER BY 列 ASC,列 DESC 降序，对查询结果按列进行排序，默认升序
排序的顺序就是 ORDER BY 中列的顺序
Mysql，都是主键升序

例：查询学生信息，按年龄进行降序排列
SELECT * FROM student ORDER BY age DESC;

例：查询学生信息，按入学时间进行升序排列，再按年龄进行降序排列
SELECT * FROM student ORDER BY scome ASC,age DESC;

6.显示结果的行数 LIMIT m,n 从m+1行开始显示n行数据，m为0可以省略
例：查询前10行的数据
SELECT * FROM student LIMIT 0,10;
SELECT * FROM student LIMIT 10;

例：查询学生表中，第5行到第13行的数据
SELECT * FROM student LIMIT 4,9; m+1=5,n=13-5+1

例：查询最后10行数据（默认主键升序）
SELECT * FROM student ORDER BY sid DESC LIMIT 10;

查询哪一年入学最多的学生，显示年份，人数（针对表中现有的数据）
SELECT scome,COUNT(*) FROM student GROUP BY scome ORDER BY COUNT(*) DESC LIMIT 1;

连接查询：学生表student、课程表course、成绩表score
连接查询本质将多个表通过某一个相同字段将表进行连接
注意：查询结果需要去重 DISTINCT

SELECT * FROM student;SELECT * FROM course;SELECT * FROM score;
1.等值连接：通过列，找出2各表中都存在列的数据
格式： SELECT 表1.*,表2.* FROM 表1,表2 WHERE 表1.列=表2.列;
例：查询每个学生每门课程的成绩，要求显示学号，姓名，课程号，分数
学号和姓名在学生表student中，课程号和分数在成绩表score中
SELECT student.sid,sname,cid,grade FROM student,score
WHERE student.sid=score.sid;

例：查询学生的信息以及课程的成绩，要求显示学号，姓名，课程名，分数
student-sid，score-sid、cid，course-cid
SELECT student.sid,sname,cname,grade FROM student,course,score
WHERE student.sid=score.sid AND course.cid=score.cid;

SELECT s.sid,sname,cname,grade FROM student AS s,course c,score sc
WHERE s.sid=sc.sid AND c.cid=sc.cid;

例：查询哪些学生的总成绩大于300，显示学生的基本信息
SELECT student.*,SUM(grade) FROM student,score WHERE student.sid=score.sid
GROUP BY score.sid HAVING SUM(grade)>300;

2.内连接： [INNER] JOIN 与等值连接一样
格式： SELECT * FROM 表1 JOIN 表2 ON 表1.列=表2.列 WHERE 条件..;
例：查询哪些学生的总成绩大于300，显示学生的基本信息
SELECT student.*,SUM(grade) FROM student JOIN score
ON student.sid=score.sid
GROUP BY score.sid HAVING SUM(grade)>300;

例：3个表连接
格式： SELECT * FROM 表1 JOIN 表2 ON 表1.列=表2.列
JOIN 表3 ON 表3.列=表2|1.列 WHERE …

3.左连接： LEFT JOIN 以左边表为主，如果右表少，全部补 NULL
格式： SELECT * FROM 表1 LEFT JOIN 表2 ON 表1.列=表2.列 WHERE …;

4.右连接： RIGHT JOIN 以右边表为主
格式： SELECT * FROM 表1 RIGHT JOIN 表2 ON 表1.列=表2.列 WHERE …;

例：以学生表为主查询每个学生的成绩，显示学生信息和成绩
SELECT student.*,grade FROM student LEFT JOIN score
ON student.sid=score.sid;

例：查询哪些学生没有参加考试，显示学生的基本信息
SELECT s.* FROM student s LEFT JOIN score sc ON s.sid=sc.sid
WHERE grade IS NULL;

step1：从成绩表中，查询学号
SELECT sid FROM score;

step2：查询学生信息，条件sid不在（step1查询结果）
SELECT * FROM student WHERE sid NOT IN(SELECT sid FROM score);

例：统计每门课程的人数有多少，要求显示课程名和人数，没有人数显示为0
从成绩表中利用cid 进行分组统计人数，课程名在课程表，以课程表为主
SELECT cname,COUNT(score.cid) FROM score RIGHT JOIN course
ON score.cid=course.cid
GROUP BY score.cid;

嵌套查询：一个SQL语句中出现多个 SELECT ，select查询结果-虚表
表的结果：一行一列，一列多行，一行多列，多行多列

1.把子查询当作条件进行嵌套（一行一列-数值）
格式： SELECT * FROM 表 WHERE 列>|=(子查询-一行一列)

例：查询学生表中年龄最小的学生信息
step1：查询最小的年龄（一行一列-相当于一个数值） 18
SELECT MIN(age) FROM student;

step2：查询学生信息，条件年龄等于（step1查询结果）
SELECT * FROM student WHERE age=(SELECT MIN(age) FROM student);

例：查询学生信息，要求年龄大于平均年龄
step1：查询学生的平均年龄（一行一列-相当于一个数值） 23
SELECT AVG(age) FROM student;

step2：查询学生信息，条件年龄大于（step1查询结果）
SELECT * FROM student WHERE age>(SELECT AVG(age) FROM student);

2.把子查询当作条件进行嵌套（一列多行-集合）
格式： SELECT * FROM 表 WHERE 列 IN(子查询);

例：查询名字相同的学生信息
step1：找出相同的名字(一列多行-集合)
SELECT sname FROM student GROUP BY sname HAVING COUNT(*)>1;

step2：查询学生信息，条件名字在（step1查询结果-集合）
SELECT * FROM student WHERE sname
IN(SELECT sname FROM student GROUP BY sname HAVING COUNT(*)>1);

3.把子查询当作条件进行嵌套（一行多列、多行多列-查询条件必须等值）
格式： SELECT * FROM 表 WHERE (列1,列2,..) IN(子查询-多列等值) 、

例：查询每个专业中年龄最大的学生信息
step1：查询每个专业的最大年龄
SELECT dept,MAX(age) FROM student WHERE dept!=” GROUP BY dept;

step2：查询学生信息，条件列1，列2都与（step1查询结果）等值
SELECT * FROM student WHERE (dept,age)
IN(SELECT dept,MAX(age) FROM student WHERE dept!=”
GROUP BY dept);

4.当作表进行嵌套（一行多列、多行多列-查询条件进行比较大小）
格式： SELECT * FROM 表,(子查询) 新表 WHERE 表.列=新表.列 AND ..;

例：查询每个专业中年龄最大的学生信息
step1：查询每个专业的最大年龄
SELECT dept,MAX(age) FROM student WHERE dept!=” GROUP BY dept;

step2：把第一步的查询结果当作新表，与查询的表，进行连接查询
SELECT student.* FROM student,
(SELECT dept,MAX(age) maxage FROM student WHERE dept!=”
GROUP BY dept) AS s
WHERE student.dept=s.dept AND student.age=s.maxage;

例：查询学生的年龄，比该专业的平均年龄大的
step1：查询每个专业的平均年龄
SELECT dept,AVG(age) FROM student WHERE dept!=” GROUP BY dept;

step2：把（step1查询结果）当作新表与原表进行连接查询
SELECT student.* FROM student,
(SELECT dept,AVG(age) avgage FROM student
WHERE dept!=” GROUP BY dept) AS s
WHERE student.dept=s.dept AND age>avgage;

查询英雄类型中人数最少的英雄类型

SELECT TYPE,COUNT(*) FROM hero WHERE TYPE!=”
GROUP BY TYPE HAVING
COUNT(*)=(SELECT COUNT(*) FROM hero WHERE TYPE!=”
GROUP BY TYPE ORDER BY COUNT(*) LIMIT 1);

SELECT TYPE,COUNT(*),(SELECT MIN(num)
FROM (SELECT TYPE,COUNT(*) num FROM hero
WHERE TYPE!=” GROUP BY TYPE) h) num FROM hero WHERE TYPE!=”
GROUP BY TYPE HAVING a=MIN(num);

SELECT MIN(num) FROM (SELECT TYPE,COUNT(*) num FROM hero WHERE TYPE!=”
GROUP BY TYPE) h

SELECT TYPE,COUNT(*) FROM hero WHERE TYPE!=”
GROUP BY TYPE HAVING
COUNT(*)=(SELECT MIN(num)
FROM (SELECT TYPE,COUNT(*) num FROM hero
WHERE TYPE!=” GROUP BY TYPE) h);

查询每个英雄类型中，伤害相同的英雄信息

step1：查询同一类型中，相同的伤害的值
SELECT TYPE,hurt FROM hero GROUP BY TYPE,hurt HAVING COUNT(*)>1;

SELECT * FROM hero WHERE (TYPE,hurt)
IN(SELECT TYPE,hurt FROM hero GROUP BY TYPE,hurt
HAVING COUNT(*)>1);
SELECT hero.* FROM hero,(SELECT TYPE,hurt FROM hero
GROUP BY TYPE,hurt HAVING COUNT(*)>1) h
WHERE hero.type=h.type AND hero.hurt=h.hurt;

SELECT uptime FROM hero GROUP BY uptime HAVING COUNT(*)>1;

SELECT * FROM hero WHERE uptime
IN(SELECT uptime FROM hero GROUP BY uptime HAVING COUNT(*)>1)

嵌套查询中关键词： IN 、 ANY 、 ALL 、 UNION 、 EXISTS
1. IN(集合) 查询结果：一列多行，一行多列，多行多列（在集合中）
使用嵌套，把子查询当作条件进行嵌套

格式： SELECT * FROM 表 WHERE 列 IN(子查询-一列多行);

例：查询学生表中名字相同的学生信息
step1：找出相同的名字-按名字进行分组，分组后统计名字个数 大于1
SELECT sname FROM student GROUP BY sname HAVING COUNT(*)>1;
SELECT sname FROM student GROUP BY sname HAVING COUNT(sname)>1;
结果：一列多行 in（宁采臣、张三、聂小倩）

step2：查询学生的信息，条件名字等于（宁采臣、张三、聂小倩）
SELECT * FROM student WHERE sname IN(第一步查询结果);

合并：SELECT * FROM student WHERE
sname IN(SELECT sname FROM student GROUP BY sname
HAVING COUNT(sname)>1);

格式： SELECT * FROM 表 WHERE (多列) IN(子查询-一行多列|多行多列);
注意：条件的多个列与子查询的多个列，个数类型必须一致

例：查询每个系中年龄最大的学生信息
step1：查询每个系的最大年龄（多行多列）
SELECT dept,MAX(age) FROM student WHERE dept!=” GROUP BY dept;
SELECT dept,MAX(age) FROM student GROUP BY dept HAVING dept!=”;

查询结果：中文-28、数学-27、英语-27、计算机-26

step2：查询学生信息，条件系与年龄分别等于（第一步查询结果）
SELECT * FROM student WHERE (dept,age) IN(第一步结果);

合并：SELECT * FROM student WHERE (dept,age)
IN(SELECT dept,MAX(age) FROM student
GROUP BY dept HAVING dept!=”);

练习：查询每门课程的最高分，显示学号，课程名，分数

step1：查询每门课程的最高分
SELECT cid,MAX(grade) FROM score GROUP BY cid;

step2：先连接2个表，在加条件cid，分数 IN(第一步结果)
SELECT sid,cname,grade FROM course c,score s WHERE c.cid=s.cid
AND (s.cid,grade) IN(SELECT cid,MAX(grade) FROM score
GROUP BY cid);

2. ALL 表示全部，使用必须与比较运算符一起
格式： SELECT * FROM 表 WHERE 列>ALL(子查询);
>ALL ：大于全部，相当于大于最大值 MAX

例：查询学生的信息，要求学生的所有成绩大于所有课程的平均分
step1：查询每门课程的平均分
SELECT AVG(grade) FROM score GROUP BY cid;

step2：查询每个学生的成绩最小值，最小值>ALL(第一步)
SELECT sid FROM score GROUP BY sid HAVING
MIN(grade)>ALL(SELECT AVG(grade) FROM score GROUP BY cid);

step3：查询学生信息，学号在（第二步）
SELECT * FROM student WHERE sid
IN(SELECT sid FROM score GROUP BY sid HAVING
MIN(grade)>ALL(SELECT AVG(grade) FROM score GROUP BY cid));

<ALL ：小于全部，相当于小于最小值 MIN
例：查询女生的信息，要求年龄小于所有男生年龄都小
SELECT * FROM student WHERE sex=’女’ AND
age<(SELECT MIN(age) FROM student WHERE sex=’男’);

step1：查询所有男生的年龄
SELECT age FROM student WHERE sex=’男’;

step2：查询女生的信息，条件年龄<ALL(第一步结果)
SELECT * FROM student WHERE sex=’女’ AND
age<ALL(SELECT age FROM student WHERE sex=’男’);

3. ANY 表示某一个，使用必须与比较运算符一起
格式： SELECT * FROM 表 WHERE 列>ANY(子查询);

>ANY ：大于某一个，相当于大于最小值 MIN
例：查询每个学生的所有成绩，比课程号为4的最低分高（课程2的某一个高）
SELECT MIN(grade) FROM score WHERE cid=4;
step1：查询课程为4的分数
SELECT grade FROM score WHERE cid=4;

step2：查询sid，按sid进行分组，分组后min(grade)>ANY(第一步)
SELECT sid FROM score GROUP BY sid HAVING
MIN(grade)>ANY(SELECT grade FROM score WHERE cid=4);

<ANY ：小于某一个，相当于小于最大值 MAX
例：查询中文系的学生信息，要求年龄比计算机系的某一个年龄小

step1：查询计算机系的所有年龄
SELECT age FROM student WHERE dept=’计算机’;

step2：查询中文系的学生信息，条件年龄<ANY(第一步结果)
SELECT * FROM student WHERE dept=’中文’
AND age<ANY(SELECT age FROM student WHERE dept=’计算机’);

4. UNION [ALL] 并操作，将2个查询结果拼接在一起（去重）
注意：查询结果的列的个数必须是相同的
格式： SELECT 列1,列2 FROM 表1 UNION SELECT 列1,列2 FROM 表2;

例：查询姓名，年龄 并性别，年龄
SELECT sname,sid FROM student UNION
SELECT sex,age FROM student

5. EXISTS 返回逻辑值（真，假），使用 EXISTS 时，子查询必须使用*

例：查询没有参加考试的学生信息
1）嵌套查询-多个select
step1：查询参加考试的学生的学号
SELECT sid FROM score;

step2：查询学生表，如果学号不在（第一步结果）
SELECT * FROM student WHERE
sid NOT IN(SELECT sid FROM score);

2）连接查询-一条SQL语句
查询没有参加考试的，以学生表为主连接，补 NULL
SELECT student.* FROM student LEFT JOIN score
ON student.sid=score.sid WHERE score.sid IS NULL;

3）关键字 [NOT] EXISTS
SELECT * FROM student WHERE
NOT EXISTS(SELECT * FROM score
WHERE student.sid=score.sid);