# ### part1 单表查询
# sql 查询语句的完整语法:
"""select .. from .. where .. group by .. having .. order by .. limit .."""
# 一.where 条件的使用
"""功能: 对表中的数据进行筛选和过滤"""
"""
语法:
1.判断的符号:
= > >= < <= != <> 不等于
2.拼接条件的关键字
and or not
3.查询的区间范围值 between
between 小值 and 大值 [小值,大值] 查询两者之间这个范围内所有数据
4.查询具体某个值的范围 in
in(1,2,3) 指定范围
5.模糊查询 like "%" "_" 通配符
like "%a" 匹配以a结尾的任意长度的字符串
like "a%" 匹配以a开头的任意长度的字符串
like "%a%" 匹配含有a字母的任意长度的字符串
like "_a" 个数一共2个字符,必须以a结尾,前面的字符随意
like "a__" 个数一共3个字符,必须以a开头,后面的字符随意
"""
# (1) 单条件的查询
# 查询部门是sale的所有员工姓名:
select emp_name from employee where post = "sale";
# (2) 多条件的查询
# 部门是teacher , 收入大于10000的所有数据
select * from employee where post="teacher" and salary > 10000;
# (3) 关键字 between .. and ..
# 收入在1万到2万之间的所有员工姓名和收入
select emp_name,salary from employee where salary between 10000 and 20000;
# 收入不在1万到2万之间的所有员工姓名和收入
select emp_name,salary from employee where salary not between 10000 and 20000;
# (4) null 关键字 在查询的时候,要用is进行判定,不要用=
# 查询post_comment 是空的所有数据
select * from employee where post_comment = null;
select * from employee where post_comment = '';
select * from employee where post_comment is null;
select * from employee where post_comment is not null;
update employee set post_comment = "" where id = 1
select * from employee where post_comment = "";
# (5) 关键字 in 在..之中 查询
# 查询收入是3000 ,4000 ,5000,8300 所有员工的姓名和收入
select emp_name,salary from employee where salary=3000 or salary=4000 or salary=5000 or salary=8300;
# 用in优化,在小括号里面具体指定某个值
select emp_name,salary from employee where salary in (3000,4000,5000,8300);
# 不在 not in ..
select emp_name,salary from employee where salary not in (3000,4000,5000,8300);
# (6) 模糊查询 like "%" "_" 通配符
# (1) "%" 通配符 以on结尾的员工名搜一下
select emp_name,age,post from employee where emp_name like "%on";
# (2) "_" 通配符 可以限定具体的长度
select emp_name,age,post from employee where emp_name like "a_e_";
# (7) concat (as 起别名)
select concat("姓名:",emp_name,"工资:",salary) as aa from employee;
# concat_ws(拼接的符号,参数1,参数2,参数3 ... )
select concat_ws(" : ",emp_name,salary) as bb from employee;
# 计算年薪 可以在mysql中使用四则运算符(+ - * /)
select concat_ws(" : ",emp_name,salary * 12) as cc from employee;
# 二.group by 子句 分组分类
"""group by 字段 对数据进行分类 , by后面接什么字段,select就搜索什么字段"""
select sex from employee group by sex;
select post from employee group by post;
# group_concat 按照分类的形式进行字段的拼接
select group_concat(emp_name),post from employee where id >5 group by post;
# 聚合函数
# count 统计总数 *所有
select count(*) from employee;
# max 统计最大值
select max(salary) from employee;
# min 统计最小值
select min(salary) from employee;
# avg 统计平均值
select avg(salary) from employee;
# sum 统计总和
select sum(salary) from employee
# 一般情况下 分组 + 聚合函数 配合使用
# 1. 查询部门名以及各部门的平均薪资
select post,avg(salary) from employee group by post;
# 2. 查询部门名以及各部门的最高薪资
select post,max(salary) from employee group by post;
# 3. 查询部门名以及各部门的最低薪资
select post,min(salary) from employee group by post;
# 4. 查询公司内男员工和女员工的个数
select sex , count(*) from employee group by sex;
# 5. 查询部门名以及部门包含的所有员工名字
select group_concat(emp_name) from employee group by post
# 可以group by 两个字段,即可搜索两个字段
select emp_name , post from employee group by post ,emp_name ;
# 三.having 数据在分类分组之后,进行二次数据过滤,一般是配合group by 使用,分组之后在过滤
# 找出各部门平均薪资, 并且大于10000以上的所有部门
select post , avg(salary) from employee group by post having avg(salary) > 10000;
# 1.查询各岗位内包含的员工个数小于2的岗位名、查询岗位内包含员工名字、个数
select post ,group_concat(emp_name),count(*) from employee group by post having count(*) < 2
# 2.查询各岗位平均薪资小于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) < 10000;
# 3.查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;# between 10000 和 20000本身这个值可以取到
select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000;
# 四.order by 排序 , 按照什么字段进行排序
# asc 升序: 从小到大 (默认)
# desc 降序: 从大到小
select * from employee order by age #(asc 默认升序)
select * from employee order by age desc #(desc 降序)
# 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select emp_name,age,hire_date,post from employee order by age , hire_date desc;
# 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post , avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) asc
# 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
select post , avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) desc
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | | 7300.33 | 401 | 1 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
# 五.limit 限制查询条数 (数据分页)
"""limit m,n m代表从第几条数据查询,n代表查询几条, m=0 代表的是第一条"""
select * from employee limit 0,5; #从第一条数据开始搜,搜5条
select * from employee limit 5,5 #从第六条数据开始搜,搜5条
# 只查询一条数据
select * from employee limit 1
# 找数据库当中最后一条数据
select * from employee order by id limit 1
# 找数据库当中最后三条数据
select * from employee order by id limit 3
# 六.(了解)可以使用正则表达式查询数据 (不推荐使用,效率不高)
select * from employee where emp_name regexp ".*on$" # .*? 问号?不识别
select * from employee where emp_name regexp "^程";
select * from employee where emp_name regexp "^程.*金";
# ### part2 多表查询
# 内连接: (内联查询 inner join ) -> 两表或者多表满足条件的所有数据查询出来(两表之间共有的数据)
"""
# 两表查询
select 字段 from 表1 inner join 表2 on 必要的关联条件
# 多表查询
select 字段 from 表1 inner join 表2 on 必要的关联条件1 inner join 表3 on 必要的关联条件2 ...
"""
# 基本语法 inner join on .. on后面接必要的关联条件
select * from employee inner join department on employee.dep_id = department.id;
# 用as 起别名 (推荐)
select * from employee as e inner join department as d on e.dep_id = d.id;
# as 可以省略掉
select * from employee e inner join department d on e.dep_id = d.id;
# where 默认实现的就是内联查询的效果
select * from employee , department where employee.dep_id = department.id;
select * from employee as e , department as d where e.dep_id = d.id;
# 外连接
#(1) 左连接(左联查询 left join) 以左表为主,右表为辅,完整查询左表所有数据,右表没有的数据补null
select * from employee left join department on employee.dep_id = department.id;
#(2) 右链接(右联查询 right join)以右表为主,左表为辅,完整查询右表所有数据,左表没有的数据补null
select * from employee right join department on employee.dep_id = department.id;
#(3) 全连接(全连接 union) 所有的数据都合并起来
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id;
# ### part3 子查询
"""
子查询: 嵌套查询
(1)sql语句当中有嵌套了另外一条sql语句,用包括()包起来,表达一个整体
(2)一般应用在from 子句后面表达一张表, where子句后面表达一个条件
(3)查询速度从快到慢 : 单表查询 -> 联表查询 -> 子查询
"""
# 一.找出平均年龄大于25岁以上的部门
# (1) 普通where写法
select
d.id,d.name
from
employee as e,department as d
where
e.dep_id = d.id
group by
d.id,d.name
having
avg(e.age) > 25;
# (2) inner join
select
d.id,d.name
from
employee as e inner join department as d on e.dep_id = d.id
group by
d.id,d.name
having
avg(e.age) > 25;
# (3) 子查询
# 1.先选出平均年龄大于25岁的部门id
select dep_id from employee group by dep_id having avg(age) > 25;
# 2.通过部门id,找部门的名字
select name from department where id in (201,202);
# 3.综合拼接
select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);
# 二.查看技术部门员工姓名
# (1) 普通where 查询
select
e.name,d.name
from
department as d , employee as e
where
d.id = e.dep_id
and
d.name = "技术";
# (2) inner join 写法
select
e.name,d.name
from
department as d inner join employee as e on d.id = e.dep_id
where
d.name = "技术";
# (3) 子查询
# 1.找技术部门对应id
select id from department where name = "技术";
# 2.通过id找员工姓名
select name from employee where dep_id = 200;
# 3.综合拼接
select name,dep_id from employee where dep_id = (select id from department where name = "技术");
# 三.查看哪个部门没员工
# 联表写法(把null的数据露出来 , 员工的部门dep_id 为null,代表这个部门没人.)
select
d.id,d.name
from
department as d left join employee as e on d.id = e.dep_id
where
e.dep_id is null
# 子查询
# 1.先查询,员工在哪些部门 (所有员工的分类分别是 200 201 202 204)
select dep_id from employee group by dep_id
# 2.把不在部门的数据找出来
select id from department where id not in (200,201,202,204);
# 3.综合拼接
select id,name from department where id not in (select dep_id from employee group by dep_id)
# 四.查询大于平均年龄的员工名与年龄
# 假设平均年龄是20岁
select name,age from employee where age > 20
# 找平均年龄
select avg(age) from employee;
# 综合拼装
select name,age from employee where age > (select avg(age) from employee);
# 五.把大于其本部门平均年龄的员工名和姓名查出来
+----+------------+--------+------+--------++--------+----------+
| id | name | sex | age | dep_id || dep_id | avg(age) |
+----+------------+--------+------+--------++--------+----------+
| 1 | egon | male | 18 | 200 || 200 | 18.0000 |
| 2 | alex | female | 48 | 201 || 201 | 43.0000 |
| 3 | wupeiqi | male | 38 | 201 || 201 | 43.0000 |
| 4 | yuanhao | female | 28 | 202 || 202 | 28.0000 |
| 5 | liwenzhou | male | 18 | 200 || 200 | 18.0000 |
| 6 | jingliyang | female | 18 | 204 || 204 | 18.0000 |
+----+------------+--------+------+--------+--------+----------+
| id | name | sex | age | dep_id | dep_id | avg(age) |
+----+------------+--------+------+--------+--------+----------+
| 1 | egon | male | 18 | 200 | 200 | 18.0000 |
| 2 | alex | female | 48 | 201 | 201 | 43.0000 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 43.0000 |
| 4 | yuanhao | female | 28 | 202 | 202 | 28.0000 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 18.0000 |
| 6 | jingliyang | female | 18 | 204 | 204 | 18.0000 |
+----+------------+--------+------+--------+--------+----------+
# 1.先计算各部门平均年龄是多少
select dep_id,avg(age) from employee group by dep_id
# 2.把查询的各部门的平均年龄和过去的employee联表,变成一张更大的表,方便做一次单条查询
select
*
from
employee as t1 inner join (1号查询到的数据) as t2 on t1.dep_id = t2.dep_id
# 3.综合拼接
select
*
from
employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id
# 4.做最后的数据筛选 age > 平均年龄
select
*
from
employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id
where
t1.age > t2.avg_age
# 六.查询每个部门最新入职的那位员工 # 利用上一套数据表进行查询;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------++-----------------------------------------+----------------+
| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id || post | max(hire_date) |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------++-----------------------------------------+----------------+
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | | 7300.33 | 401 | 1 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
+-----------------------------------------+----------------+
| post | max(hire_date) |
+-----------------------------------------+----------------+
| operation | 2016-03-11 |
| sale | 2017-01-27 |
| teacher | 2015-03-02 |
| 老男孩驻沙河办事处外交大使 | 2017-03-01 |
+-----------------------------------------+----------------+
+----------+------------+
| emp_name | hire_date |
+----------+------------+
| egon | 2017-03-01 |
| alex | 2015-03-02 |
| 格格 | 2017-01-27 |
| 张野 | 2016-03-11 |
+----------+------------+
# 1.找每个部门最大的入职时间
select post , max(hire_date) as max_data from employee group by post
# 2.把子查询搜出来的数据和employee联合成一张更大的表,做一次单表查询
select
t1.emp_name,t1.hire_date
from
employee as t1 inner join (1号查出来的数据) as t2 on t1.post = t2.post
where
t1.hire_date = t2.max_data
# 3.综合拼接
select
t1.emp_name,t1.hire_date
from
employee as t1 inner join (select post , max(hire_date) as max_data from employee group by post) as t2 on t1.post = t2.post
where
t1.hire_date = t2.max_data
# 七.带EXISTS关键字的子查询
"""
exists 关键字,表达存在
如果内层sql 能够查到数据,返回True , 外层sql执行查询语句
如果内层sql 不能查到数据,返回False, 外层sql不执行查询语句
"""
select * from employee where exists (select * from employee where id = 1) # 能
select * from employee where exists (select * from employee where id = 100) # 不能
"""
子查询总结:
子查询可以单独作为一个临时数据,临时的表,临时的字段
一般用在from where select 子句后面
可以通过查询出来的临时数据和另外的表联合,变成一张更大的表,在做单表查询查到想要的数据
"""
# ### 补充
# 关于约束的添加和删除
# 1 添加/删除 约束 not null
#alter table 表名 modify 字段名 类型
alter table t1 modify id int not null
alter table t1 modify id int
# 2 添加/删除 unique 唯一索引
# alter table 表名 add unique(id)
alter table t1 add unique(id)
alter table t1 drop index id
# 3 添加/删除 primary key
# alter table 表名 add primary key(id);
alter table t1 add primary key(id);
alter table t1 drop primary key;
# 4 添加/删除 foreign key 外键 (先通过desc 表 找到外键名字,然后再删)
alter table student1 drop foreign key student1_ibfk_1; #删除
alter table student1 add foreign key(classid) references class1(id) #添加
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
