CASCADE注：mysql中不区分大小写，一般关键字用大写，自己写的用小写就可以
用到的数据：
1 #创建部门
2 CREATE TABLE IF NOT EXISTS dept (
3 did int not null auto_increment PRIMARY KEY,
4 dname VARCHAR(50) not null COMMENT '部门名称'
5 )ENGINE=INNODB DEFAULT charset utf8;
6
7
8 #添加部门数据
9 INSERT INTO `dept` VALUES ('1', '教学部');
10 INSERT INTO `dept` VALUES ('2', '销售部');
11 INSERT INTO `dept` VALUES ('3', '市场部');
12 INSERT INTO `dept` VALUES ('4', '人事部');
13 INSERT INTO `dept` VALUES ('5', '鼓励部');
14
15 -- 创建人员
16 DROP TABLE IF EXISTS `person`;
17 CREATE TABLE `person` (
18 `id` int(11) NOT NULL AUTO_INCREMENT,
19 `name` varchar(50) NOT NULL,
20 `age` tinyint(4) DEFAULT '0',
21 `sex` enum('男','女','人妖') NOT NULL DEFAULT '人妖',
22 `salary` decimal(10,2) NOT NULL DEFAULT '250.00',
23 `hire_date` date NOT NULL,
24 `dept_id` int(11) DEFAULT NULL,
25 PRIMARY KEY (`id`)
26 ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
27
28 -- 添加人员数据
29
30 -- 教学部
31 INSERT INTO `person` VALUES ('1', 'alex', '28', '人妖', '53000.00', '2010-06-21', '1');
32 INSERT INTO `person` VALUES ('2', 'wupeiqi', '23', '男', '8000.00', '2011-02-21', '1');
33 INSERT INTO `person` VALUES ('3', 'egon', '30', '男', '6500.00', '2015-06-21', '1');
34 INSERT INTO `person` VALUES ('4', 'jingnvshen', '18', '女', '6680.00', '2014-06-21', '1');
35
36 -- 销售部
37 INSERT INTO `person` VALUES ('5', '歪歪', '20', '女', '3000.00', '2015-02-21', '2');
38 INSERT INTO `person` VALUES ('6', '星星', '20', '女', '2000.00', '2018-01-30', '2');
39 INSERT INTO `person` VALUES ('7', '格格', '20', '女', '2000.00', '2018-02-27', '2');
40 INSERT INTO `person` VALUES ('8', '周周', '20', '女', '2000.00', '2015-06-21', '2');
41
42 -- 市场部
43 INSERT INTO `person` VALUES ('9', '月月', '21', '女', '4000.00', '2014-07-21', '3');
44 INSERT INTO `person` VALUES ('10', '安琪', '22', '女', '4000.00', '2015-07-15', '3');
45
46 -- 人事部
47 INSERT INTO `person` VALUES ('11', '周明月', '17', '女', '5000.00', '2014-06-21', '4');
48
49 -- 鼓励部
50 INSERT INTO `person` VALUES ('12', '苍老师', '33', '女', '1000000.00', '2018-02-21', null);
51
52 创建表和数据
目录：
多表联合查询（包含了好多的问题的）
子语句查询（也包含了好多问题）
1、select sum(salary) from person GROUP BY dept_id #按照部门分组后，求各个部门总的工资数。如果一个部门为空，那么单独作为一组
2、select sum(salary),dept_id from person GROUP BY dept_id #加上每个部门的id
3、select sum(salary) from person; #求出person表中所有人的工资
4、select sum(salary) as w,dept_id from person GROUP BY dept_id HAVING w>20000 #查询出哪个部门总工资高于两万的
5、select max(salary),dept_id from person group by dept_id; #找出每个部门工资最大的那个人
6、select avg(salary),dept_id from person group by dept_id; #找出每个部门的平均工资
7、select avg(salary),dept_id,GROUP_CONCAT(name) from person group by dept_id; #找出每个部门的平均工资,并显示每个组内所有的人
8、select avg(salary) as w,dept_id,GROUP_CONCAT(name) from person group by dept_id HAVING w>5000; 找出平均工资大于5k的部门,并显示每个部门内所有的人
where 与 having区别:
#执行优先级从高到低：where > group by > having
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
1、
原表格：
select * from person LIMIT 0,4; #id为1-4的分为一页
select * from person LIMIT 0,4等价于select * from person LIMIT 4
select name, max(salary) 5 查找数据
from person 1 先拿数据 where name is not null 2 再判断，通过条件过滤一下 group by name 3 分组 having max(salary) > 5000 4分组之后再根据条件进行过滤 order by max(salary) 6 对数据进行排序limit 0,5 7 分页1、select * from person,dept; #笛卡尔乘积，两个表的数据结果相乘
2、select * from person p,dept d where p.dept_id=d.did; #这样加条件之后就无数据重复了，如果person中由没有部门或者是部门不在dept中的话，那么就不再显示了，同理如果dept中有person中没有的，那么也不会在这里显示的
只有表A和表B中共有的数据才会显示出来
3、select * from person left join dept on person.dept_id=dept.did #左连接查询,特点就是左边的表数据全出来,如果能跟右边的表匹配出来那么就显示，如果匹配不 出来，只显示左边表中的匹配不出来的数据，此时person为基准表
以一张表为基准，符合条件的就拿过来，不符合条件的那么扔掉，优点是基准表的所有数据都会显示出来
4、select * from person right join dept on person.dept_id=dept.did #同理右连接，也可以将person和dept调换位置以实现右关联，此时dept为基准表
5、select * from person inner join dept on person.dept_id=dept.did #内连接
多表联合查询和内连接是一样的
6、select * from person left join dept on person.dept_id=dept.did #同理右连接
union #将重复的数据去掉实现全连接
select * from person right join dept on person.dept_id=dept.did #内连接
7、select * from person left join dept on person.dept_id=dept.did #同理右连接
union all #不去重实现全连接
select * from person right join dept on person.dept_id=dept.did #内连接
问题：查询出 教学部 年两大于20岁 并且工资小于1万的员工 并按照工资进行降序排序
8、问题一步步分解
1）select * from dept where dname = '教学部'
2）select did from dept where dname = '教学部'
3）select * from person where age>20 and dept_id=1; #选出第二张表中年龄大于20且部门id为1的人
4）select * from person where age>20 and dept_id=(select did from dept where dname = '教学部'); #选出第二张表中年龄大于20且部门id为1的人
5)select * from person where age>20 and dept_id=(select did from dept where dname = '教学部') and salary<10000 order by salary desc; #在括号里的成为子查 询
问题2： 查询每个部门中最高员工工资和最低工资是多少，并显示部门名称
解析：每个部门就是要分组了，根据部门分组；显示部门名称：因为person中只有部门对应的数字，而在dept表中有数字对应的部门，所以得用多表联合查询
1）select max(salary),min(salary) from person group by dept_id
2）select max(salary),min(salary),dname from person left join dept on person.dept_id=dept.did group by dept_id
select avg(age),dname from person left join dept on person.dept_id=dept.did group by dept_id #求每个组的平均年龄
1、select * from (select * from person) as aaa
问题1：查找最大工资哪个人的姓名和薪水
select * from person where salary = (select max(salary) from person)
问题2：求工资高于所有人员平均工资的人员
select * from person where salary>(select avg(salary) from person)
问题3：查询平均年龄在20岁以上的部门名
同理在person中只有部门名对应的数字，在dept中有数字对应的部门名，所以要用到联合查询
1）select * from person where (select avg(age) from person)>20
2）select dept_id from person where (select avg(age) from person)>20 #只是查询出了person中平均年龄大于20岁的部门代表的数字
3）select dname from person left join dept on (select avg(age) from person)>20 group by dname #有问题的。。。不对
对于问题3别人的方法：
select dept_id from person GROUP BY dept_id HAVING avg(person.age) > 20 #就只能查到部门所对应的数字
问题4：查询教学部下的员工信息
select * from person where dept_id = (select did from dept where dname ='教学部');
2019.01.06 晚
20：16 距离睡觉还有好长时间
good night
haijing in HZ
问题01：查询高于本部门平均工资的人员
错误的做法（有可能是面试题）：
正确的做法：select * from person p1,(select dept_id,avg(salary) as '平均工资' from person group by dept_id) as p2
where p1.dept_id=p2.dept_id and p2.`平均工资`<p1.salary;
问题：根据工资高低,将人员划分为两个级别,分别为 高端人群和低端人群。显示效果:姓名,年龄,性别,工资,级别
select p.name,p.age,p.sex,p.salary,if(salary>10000,'高端人群','低端人群') as '级别' from person as p;
select name,age,sex,salary,if(salary>10000,'高端人群','低端人群') as '级别' from person;
问题02：根据工资高低,统计每个部门人员收入情况,划分为 富人,小资,平民,吊丝 四个级别, 要求统计四个级别分别有多少人 *****
select dname from dept,person where dept.did=person.dept_id group by dept_id
select dname,
sum(case when person.salary>10000 then 1 else 0 end) as '富人',
sum(case when person.salary between 5000 and 10000 then 1 else 0 end) as '小资',
sum(case when person.salary between 3000 and 5000 then 1 else 0 end) as '平民',
sum(case when person.salary<3000 then 1 else 0 end) as '屌丝'
from dept,person where dept.did=person.dept_id group by dept_id
一.SQL语句定义顺序
SELECT DISTINCT <select_list>FROM <left_table><join_type> JOIN <right_table>ON <join_condition>WHERE <where_condition>GROUP BY <group_by_list>HAVING <having_condition>ORDER BY <order_by_condition>LIMIT <limit_number>
二.准备测试
1. 新建一个测试数据库TestDB;
CREATE DATABASE TestDB DEFAULT charset utf8;2.创建测试表table1和table2;
3.插入测试数据
4.效果
5.准备SQL逻辑查询测试语句
SELECTa.customer_id,COUNT(b.order_id)astotal_ordersFROM table1 AS a LEFT JOIN table2 AS bON a.customer_id = b.customer_idWHERE a.city ='hangzhou'GROUP BY a.customer_idHAVING count(b.order_id) < 2ORDER BY total_orders DESC;SQL语句解释: 获得来自杭州，并且订单数少于2的客户
三.SQL逻辑查询语句执行顺序
还记得上面给出的那一长串的SQL逻辑查询规则么？那么，到底哪个先执行，哪个后执行呢？现在，我先给出一个查询语句的执行顺序：*****
前边的序号表示谁先执行
(7) SELECT (8) DISTINCT <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> #连接添加外部行 (2) ON <join_condition> (4) WHERE <where_condition> (5) GROUP BY <group_by_list> (6) HAVING <having_condition> (9) ORDER BY <order_by_condition> (10) LIMIT <limit_number>上面在每条语句的前面都标明了执行顺序号，不要问我怎么知道这个顺序的。我也是读各种“武林秘籍”才得知的. 如果你有功夫，去阅读一下MySQL的源码，也会得出这个结果的
四.SQL执行先后顺序分析
重点:
在这些SQL语句的执行过程中，都会产生一个虚拟表，用来保存SQL语句的执行结果（这是重点），我们现在就来跟踪这个虚拟表的变化，得到最终的查询结果的过程，来分析整个SQL逻辑查询的执行顺序和过程。
1.执行FROM语句
第一步，执行
FROM语句。我们首先需要知道最开始从哪个表开始的，这就是FROM告诉我们的。现在有了<left_table>和<right_table>两个表，我们到底从哪个表开始，还是从两个表进行某种联系以后再开始呢？它们之间如何产生联系呢？——笛卡尔积经过FROM语句对两个表执行笛卡尔积，会得到一个虚拟表，暂且叫VT1（vitual table 1），内容如下：
+-------------+----------+----------+-------------+ | customer_id | city | order_id | customer_id | +-------------+----------+----------+-------------+ | 163 | hangzhou | 1 | 163 | | 9you | shanghai | 1 | 163 | | baidu | hangzhou | 1 | 163 | | tx | hangzhou | 1 | 163 | | 163 | hangzhou | 2 | 163 | | 9you | shanghai | 2 | 163 | | baidu | hangzhou | 2 | 163 | | tx | hangzhou | 2 | 163 | | 163 | hangzhou | 3 | 9you | | 9you | shanghai | 3 | 9you | | baidu | hangzhou | 3 | 9you | | tx | hangzhou | 3 | 9you | | 163 | hangzhou | 4 | 9you | | 9you | shanghai | 4 | 9you | | baidu | hangzhou | 4 | 9you | | tx | hangzhou | 4 | 9you | | 163 | hangzhou | 5 | 9you | | 9you | shanghai | 5 | 9you | | baidu | hangzhou | 5 | 9you | | tx | hangzhou | 5 | 9you | | 163 | hangzhou | 6 | tx | | 9you | shanghai | 6 | tx | | baidu | hangzhou | 6 | tx | | tx | hangzhou | 6 | tx | | 163 | hangzhou | 7 | NULL | | 9you | shanghai | 7 | NULL | | baidu | hangzhou | 7 | NULL | | tx | hangzhou | 7 | NULL | +-------------+----------+----------+-------------+总共有28（table1的记录条数 * table2的记录条数）条记录。这就是VT1的结果，接下来的操作就在VT1的基础上进行。
2.执行ON过滤
执行完笛卡尔积以后，接着就进行
ON a.customer_id = b.customer_id条件过滤，根据ON中指定的条件，去掉那些不符合条件的数据，得到VT2表，内容如下：+-------------+----------+----------+-------------+ | customer_id | city | order_id | customer_id | +-------------+----------+----------+-------------+ | 163 | hangzhou | 1 | 163 | | 163 | hangzhou | 2 | 163 | | 9you | shanghai | 3 | 9you | | 9you | shanghai | 4 | 9you | | 9you | shanghai | 5 | 9you | | tx | hangzhou | 6 | tx | +-------------+----------+----------+-------------+T2就是经过
ON条件筛选以后得到的有用数据，而接下来的操作将在VT2的基础上继续进行。
3.添加外部行
这一步只有在连接类型为
OUTER JOIN时才发生，如LEFT OUTER JOIN、RIGHT OUTER JOIN。在大多数的时候，我们都是会省略掉OUTER关键字的，但OUTER表示的就是外部行的概念。
LEFT OUTER JOIN把左表记为保留表，得到的结果为：+-------------+----------+----------+-------------+ | customer_id | city | order_id | customer_id | +-------------+----------+----------+-------------+ | 163 | hangzhou | 1 | 163 | | 163 | hangzhou | 2 | 163 | | 9you | shanghai | 3 | 9you | | 9you | shanghai | 4 | 9you | | 9you | shanghai | 5 | 9you | | tx | hangzhou | 6 | tx | | baidu | hangzhou | NULL | NULL | +-------------+----------+----------+-------------+
RIGHT OUTER JOIN把右表记为保留表，得到的结果为：+-------------+----------+----------+-------------+ | customer_id | city | order_id | customer_id | +-------------+----------+----------+-------------+ | 163 | hangzhou | 1 | 163 | | 163 | hangzhou | 2 | 163 | | 9you | shanghai | 3 | 9you | | 9you | shanghai | 4 | 9you | | 9you | shanghai | 5 | 9you | | tx | hangzhou | 6 | tx | | NULL | NULL | 7 | NULL | +-------------+----------+----------+-------------+添加外部行的工作就是在VT2表的基础上添加保留表中被过滤条件过滤掉的数据，非保留表中的数据被赋予NULL值，最后生成虚拟表VT3。
由于我在准备的测试SQL查询逻辑语句中使用的是
LEFT JOIN，过滤掉了以下这条数据：| baidu | hangzhou | NULL | NULL |现在就把这条数据添加到VT2表中，得到的VT3表如下：
+-------------+----------+----------+-------------+ | customer_id | city | order_id | customer_id | +-------------+----------+----------+-------------+ | 163 | hangzhou | 1 | 163 | | 163 | hangzhou | 2 | 163 | | 9you | shanghai | 3 | 9you | | 9you | shanghai | 4 | 9you | | 9you | shanghai | 5 | 9you | | tx | hangzhou | 6 | tx | | baidu | hangzhou | NULL | NULL | +-------------+----------+----------+-------------+接下来的操作都会在该VT3表上进行。
4.执行WHERE过滤
对添加外部行得到的VT3进行WHERE过滤，只有符合<where_condition>的记录才会输出到虚拟表VT4中。当我们执行
WHERE a.city = 'hangzhou'的时候，就会得到以下内容，并存在虚拟表VT4中：+-------------+----------+----------+-------------+ | customer_id | city | order_id | customer_id | +-------------+----------+----------+-------------+ | 163 | hangzhou | 1 | 163 | | 163 | hangzhou | 2 | 163 | | tx | hangzhou | 6 | tx | | baidu | hangzhou | NULL | NULL | +-------------+----------+----------+-------------+但是在使用WHERE子句时，需要注意以下两点：
- 由于数据还没有分组，因此现在还不能在WHERE过滤器中使用
where_condition=MIN(col)这类对分组统计的过滤；- 由于还没有进行列的选取操作，因此在SELECT中使用列的别名也是不被允许的，如：
SELECT city as c FROM t WHERE c='shanghai';是不允许出现的。5.执行GROUP BY分组
GROU BY子句主要是对使用WHERE子句得到的虚拟表进行分组操作。我们执行测试语句中的GROUP BY a.customer_id，就会得到以下内容：+-------------+----------+----------+-------------+ | customer_id | city | order_id | customer_id | +-------------+----------+----------+-------------+ | 163 | hangzhou | 1 | 163 | | baidu | hangzhou | NULL | NULL | | tx | hangzhou | 6 | tx | +-------------+----------+----------+-------------+得到的内容会存入虚拟表VT5中，此时，我们就得到了一个VT5虚拟表，接下来的操作都会在该表上完成。
6.执行HAVING过滤
HAVING子句主要和GROUP BY子句配合使用，对分组得到的VT5虚拟表进行条件过滤。当我执行测试语句中的HAVING count(b.order_id) < 2时，将得到以下内容：+-------------+----------+----------+-------------+ | customer_id | city | order_id | customer_id | +-------------+----------+----------+-------------+ | baidu | hangzhou | NULL | NULL | | tx | hangzhou | 6 | tx | +-------------+----------+----------+-------------+这就是虚拟表VT6。
7.SELECT列表
现在才会执行到
SELECT子句，不要以为SELECT子句被写在第一行，就是第一个被执行的。我们执行测试语句中的
SELECT a.customer_id, COUNT(b.order_id) as total_orders，从虚拟表VT6中选择出我们需要的内容。我们将得到以下内容：+-------------+--------------+ | customer_id | total_orders | +-------------+--------------+ | baidu | 0 | | tx | 1 | +-------------+--------------+不，还没有完，这只是虚拟表VT7。
8.执行DISTINCT子句
如果在查询中指定了
DISTINCT子句，则会创建一张内存临时表（如果内存放不下，就需要存放在硬盘了）。这张临时表的表结构和上一步产生的虚拟表VT7是一样的，不同的是对进行DISTINCT操作的列增加了一个唯一索引，以此来除重复数据。由于我的测试SQL语句中并没有使用DISTINCT，所以，在该查询中，这一步不会生成一个虚拟表。
9.执行ORDER BY子句
对虚拟表中的内容按照指定的列进行排序，然后返回一个新的虚拟表，我们执行测试SQL语句中的
ORDER BY total_orders DESC，就会得到以下内容：+-------------+--------------+ | customer_id | total_orders | +-------------+--------------+ | tx | 1 | | baidu | 0 | +-------------+--------------+可以看到这是对total_orders列进行降序排列的。上述结果会存储在VT8中。
10.执行LIMIT子句
LIMIT子句从上一步得到的VT8虚拟表中选出从指定位置开始的指定行数据。对于没有应用ORDER BY的LIMIT子句，得到的结果同样是无序的，所以，很多时候，我们都会看到LIMIT子句会和ORDER BY子句一起使用。MySQL数据库的LIMIT支持如下形式的选择：
LIMIT n, m表示从第n条记录开始选择m条记录。而很多开发人员喜欢使用该语句来解决分页问题。对于小数据，使用LIMIT子句没有任何问题，当数据量非常大的时候，使用
LIMIT n, m是非常低效的。因为LIMIT的机制是每次都是从头开始扫描，如果需要从第60万行开始，读取3条数据，就需要先扫描定位到60万行，然后再进行读取，而扫描的过程是一个非常低效的过程。所以，对于大数据处理时，是非常有必要在应用层建立一定的缓存机制（貌似现在的大数据处理，都有缓存哦）.
其他表的主键在别人的表中就被成为了外键
约束即限制，以确保表的完整行和唯一性
alter table person add constraint fk_id foreign key (dept_id) references dept(did) #添加外键
而此时要删除dept中did中的数据，比如
delete from dept where did=5 #会报错，因为此时dept中的did数据和person中的dept_id已经产生约束关系了，此时要删除dept中did的数据是不被允许的
解决方法1：将person中dept_id 等于5的换为其他的数字，此后再删除就可以了，这样删除的话，dept中的did对应的数据也会被删除掉
解决方法2：选择person-->右键选择设计表-->在外键选项中选择CASCADE,再执行 delate from person where dept_id=5就可以删除了
但是这样删除的话，在dept表中的did对应的数据也会被删除掉
删除外键：
alter table person drop foreign key fk_id #fk_id为要删除的外键的名字
1）首先为一个表person下的dept_did添加上真实存在的外键：
alter table person add constraint fk_id foreign key (dept_id) references dept(did) #添加外键
2）右键person-->选择设计表-->选择外键-->删除外键，之后点击保存即可形成理论外键，即在person下保留了如下下图的效果
1）在创建表的时候就添加上唯一约束：
create table t4(
id int(10) not null,
name varchar(255) ,
unique id_name(id,name)
);
2）给已经创建了的表添加唯一约束：
alter table t4 add unique id_name(id,name); #给已经创建了的表t4添加唯一约束,并给这个唯一约束起名字为id_name
3）删除唯一约束：
alter table t4 drop index id_name; #删除唯一约束id_name
create table t5(
id int(10) not null primary key,
name varchar(255) default '张三' #先创建一个含有默认值的表t5
);
INSERT into t5(id) VALUES(1),(2); #再往t5中插入数据，此时只是插入id的数据，那么默认就为‘张三’
