MySql的用法总结-2

一、单表查询

1、where 条件的使用

　　功能:对表中的数据进行筛选和过滤

　　语法:
　　　　1.判断的符号
　　　　　　> < >= <= = !=( <>不等于 )
　　　　2.拼接不同条件的关键字
　　　　　　and or not
　　　　3.查询区间值
　　　　　　between 小值 and 大值 [小值,大值] 查询两者之间的范围
　　　　4.查询区间值
　　　　　　id in (1,2,3,4,5,6)
　　　　5.模糊查询 like %通配符 _通配符
　　　　　　like "%b" 匹配以b结尾的任意长度字符串
　　　　　　like "a%" 匹配以a开头的任意长度字符串
　　　　　　like "%c%" 匹配字符串中含有c的任意长度字符串
　　　　　　like "__d" 匹配总长度为3位,而且以d结尾的字符串
　

　　　　　like "e__" 匹配总长度为3位,而且以e开头的字符

 #创建表
create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);

#三个部门：教学，销售，运营
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;

创建表

 1 # 1. 查询部门是sale的所有员工姓名:
 2 select emp_name from employee where post="sale"
 3 
 4 # 2. 部门是teacher , 收入大于10000的所有数据
 5 select * from employee where post="teacher" and salary > 10000;
 6 
 7 # 3. 收入在1万到2万之间的所有员工姓名和收入
 8 select emp_name,salary from employee where salary between 10000 and 20000;
 9 
10 # 4. 收入不在1万到2万之间的所有员工姓名和收入
11 select emp_name,salary from employee where salary not between 10000 and 20000;
12 
13 # 5. 查看岗位描述为NULL的员工信息
14 select * from employee where post_comment is null;
15 update employee set post_comment = "" where id = 1;
16 select * from employee where post_comment = "";
17 # 查看岗位描述不为NULL的员工信息 
18 select * from employee where  post_comment is not null;
19 
20 # 6. 查询收入是3000 ,4000 ,5000,8300 所有员工的姓名和收入
21 select emp_name,salary from employee where salary in(3000,4000,5000,8300); # (推荐)
22 select emp_name,salary from employee where salary = 3000 or salary = 4000 or salary = 5000 or salary = 8300;
23 
24 # 查询收入不是3000 ,4000 ,5000,8300 所有员工的姓名和收入
25 select emp_name,salary from employee where salary not in(3000,4000,5000,8300); # (推荐)
26 
27 # 7. 以on结尾的员工名搜一下
28 select emp_name from employee where emp_name like "%on";
29 select emp_name from employee where emp_name like "wu%";
30 select emp_name from employee where emp_name like "%le%";
31 select emp_name from employee where emp_name like "al__";
32 select emp_name from employee where emp_name like "%alex%";
33 
34 # 8. 统计员工一年的年薪
35 select concat("姓名:",emp_name,"收入:",salary)  from employee;
36 # + - * / 四则运算
37 select concat("姓名:",emp_name,"收入:",salary * 12 )  from employee;
38 # 语法: concat_ws(拼接符号,字段1,字段2,字段3 .... )
39 select concat_ws(" : ",emp_name,salary * 12 )  from employee;
40 
41 # 9. 查询部门的种类
42 # distinct 去重
43 select distinct(post) from  employee

练习:where 拼接条件

2、 group by 子句分类,分组

注意点: 针对于当前表,by谁搜谁

select sex from employee group by sex
select emp_name from employee group by sex # error
# group_concat 按照分组把对应的字段拼接在一起
select group_concat(emp_name) from employee group by sex;

# 聚合函数
    # 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 #练习:group 分类
 2 # 1. 查询部门名以及各部门的平均薪资
 3 select avg(salary),post from employee  group by post
 4 
 5 # 2. 查询部门名以及各部门的最高薪资
 6 select max(salary),post from employee  group by post
 7 
 8 # 3. 查询部门名以及各部门的最低薪资
 9 select min(salary),post from employee  group by post
10 
11 # 4. 查询公司内男员工和女员工的个数
12 select count(*),sex from employee  group by sex
13 
14 # 5. 查询部门名以及部门包含的所有员工名字
15 select group_concat(emp_name),post from employee group by post;
16 
17 # 6. 可以group by 两个字段,by谁搜谁;
18 select emp_name,post from employee group by post,emp_name;

练习:group 分类

3、.having 对分类后的数据进行二次过滤[应用在group by这个场景里]

 1 # 找出各部门平均薪资,且大于10000
 2 select post,avg(salary) from  employee group by post having  avg(salary) > 10000
 3 
 4 # 1.查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
 5 select group_concat(emp_name),post,count(*) from employee group by post having count(*) > 2
 6 
 7 # 2.查询各岗位平均薪资小于10000的岗位名、平均工资
 8 select post,avg(salary) from  employee group by post having  avg(salary) < 10000
 9 
10 # 3.查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
11 select post,avg(salary) from  employee group by post having  10000 < avg(salary)< 20000  error[没有搜到想要的结果]
12 select post,avg(salary) from  employee group by post having  10000 < avg(salary) and   avg(salary)  < 20000
13 # 10000 <= avg(salary) <= 20000
14 select post,avg(salary) from  employee group by post having  avg(salary) between 10000 and 20000;

练习:having

4、.order by 排序

正序升序 asc
倒序降序 desc

#练习:order by
select * from employee order by age;
select * from employee order by age asc;(默认升序)
select * from employee order by age desc;(默认升序)

1 # 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照 hire_date 降序排序
2 select * from employee order by age asc , hire_date desc;
3 # 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
4 select post,avg(salary) from employee group by post having avg(salary) > 10000 order by  avg(salary)
5 # 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
6 select post,avg(salary) from employee group by post having avg(salary) > 10000 order by  avg(salary) desc;

练习：order by

5、.limit 限制查询的条数

　　limit m,n m代表从第几条搜索数据 , n 代表搜索几条 m=0 代表搜索第一条数据

# 分页
select * from employee limit 0,10  # 0代表第一条 ,往后搜10条数据
select * from employee limit 10,10 # 10代表第11条,往后搜10条数据
select * from employee limit 20,10 # 20代表第21条,往后搜10条数据

# limit 数字  代表搜索条数
select * from employee limit 1;
# 搜索表里面最后一条数据
select * from employee order by id desc limit 1;
# 搜索表里面最后三条数据
select * from employee order by id desc limit 3;

6、(了解) 可以使用正则表达式 (不推荐使用)

select * from employee where emp_name regexp ".*n$"; #?号不识别
select * from employee where emp_name regexp "程咬.*";

二、多表查询

 #建表
 create table department(
 id int,
 name varchar(20) 
 );
  
 create table employee(
 id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
 
#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

建两张表

1、内联查询(内联接)

　　inner join 至少两表以上做查询,把满足条件的所有数据查询出来(查询的是共同拥有的数据)

select 字段 from 表1 inner join 表2 on 必要的关联字段 (2张表)
select 字段 from 表1 inner join 表2 on 必要的关联字段1 inner join 表3 on 必要的关联字段2 ... inner join ...

# 语法:
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 写法默认等价于inner join 也是内联查询
select * from employee , department  where  employee.dep_id = department.id ;
select * from employee as e, department as d  where  e.dep_id = d.id ;

2、外联查询(外联接)

# (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、全联查询(全联接) left join + right join

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 ;

三、子查询

子查询 : sql语句的嵌套
(1) sql语句当中嵌套另外一条sql,用括号()包起来,表达一个整体;
(2) 一般用在子句的后面比如from , where ...身后表达一个条件或者一张表
(3) 速度快慢 : 单表查询 > 联表查询 > 子查询

 1 #建表
 2 create table department(
 3 id int,
 4 name varchar(20) 
 5 );
 6 
 7 create table employee(
 8 id int primary key auto_increment,
 9 name varchar(20),
10 sex enum('male','female') not null default 'male',
11 age int,
12 dep_id int
13 );
14 
15 #插入数据
16 insert into department values
17 (200,'技术'),
18 (201,'人力资源'),
19 (202,'销售'),
20 (203,'运营');
21 
22 insert into employee(name,sex,age,dep_id) values
23 ('egon','male',18,200),
24 ('alex','female',48,201),
25 ('wupeiqi','male',38,201),
26 ('yuanhao','female',28,202),
27 ('liwenzhou','male',18,200),
28 ('jingliyang','female',18,204)
29 ;

建立两张表

1、基本用法

1、找出平均年龄大于25岁以上的部门

# where 
select 
    department.id,department.name
from
    employee,department
where 
    employee.dep_id = department.id
group by
    department.id,department.name
having 
    avg(age) > 25;

# 用as 起别名
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(age) > 25;

# 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(age) > 25;

2、子查询练习

1.找平均年龄大于25岁以上的部门id

select 
    dep_id
from 
    employee 
group by 
    employee.dep_id
having 
    avg(age) > 25
# 2.通过id上 department 表里面找部门名
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  employee.dep_id having avg(age) > 25);

2.查看技术部门员工姓名

 1 # where 
 2 select 
 3     e.name
 4 from 
 5     employee as e , department as d
 6 where
 7     e.dep_id = d.id
 8     and
 9     d.name = "技术"
10     
11 # inner join 
12 select 
13     e.name
14 from 
15     employee as e inner join  department as d on e.dep_id = d.id
16 where 
17     d.name = "技术"
18 
19 
20 # 子查询
21 # (1) 通过技术部门找id
22 select id from department where name = "技术"
23 # (2) 通过id 找员工姓名
24 select name from employee where dep_id = 200;
25 # (3) 综合拼接
26 select name from employee where dep_id = (select id from department where name = "技术");

View Code

3.查看哪个部门没员工

 1 # 联表查询
 2 select 
 3     d.id,d.name 
 4 from 
 5     department as d left join employee  as e on d.id = e.dep_id 
 6 where 
 7     e.id is null
 8 
 9 # 子查询
10 # 1.找员工都在哪些部门
11 select dep_id from employee group by dep_id
12 
13 # 2把不在该部门的员工找出来
14 select id from department where id not in (200,201,202,204);
15 
16 # 综合拼接
17 select id , name  from department where id not in (select dep_id from employee group by dep_id);

View Code

4.查询大于平均年龄的员工名与年龄

1 select name , age  from employee where age > 28
2 # 计算平均年龄
3 select  avg(age) from employee;
4 # 综合拼接
5 select name , age  from employee where age > (select  avg(age) from employee);

View Code

5.把大于其本部门平均年龄的员工名和姓名查出来

 1 # 1.先计算本部门的平均年龄是多少
 2 select dep_id,avg(age) from employee  group by dep_id
 3 # 2.把搜索出来的数据和employee表进行联表,最后做单表查询
 4 select 
 5     *
 6 from
 7     employee as t1 inner join (1号sql查询出来的数据) as t2 on t1.dep_id = t2.dep_id
 8 
 9 # 3.综合拼接
10 select 
11     *
12 from
13     employee as t1 inner join (select dep_id,avg(age) from employee  group by dep_id) as t2 on t1.dep_id = t2.dep_id
14     
15 # 4.做单表查询
16 select 
17     t1.name
18 from
19     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
20 where 
21     t1.age > t2.avg_age

View Code

6.查询每个部门最新入职的那位员工 # 利用上一套数据表进行查询;

 1 # 1.找每个部门 hire_date 字段的最大值(即是最新入职的员工)
 2 select max(hire_date) as max_date , post from employee  group by post
 3 # 2.把 employee 和 子查询搜出的最大日期做联表,合并成大表之后,在做单表查询;
 4 select 
 5     *
 6 from 
 7     employee as t1 inner join (1号查询出来的数据) as t2 on t1.post = t2.post
 8     
 9 # 3.综合拼接
10 select 
11     *
12 from 
13     employee as t1 inner join (select max(hire_date) as max_date , post from employee  group by post) as t2 on t1.post = t2.post
14     
15 # 4.最后做单表查询
16 select 
17     t1.emp_name,t1.hire_date
18 from 
19     employee as t1 inner join (select max(hire_date) as max_date , post from employee  group by post) as t2 on t1.post = t2.post
20 where 
21     t1.hire_date = t2.max_date

View Code

7 总结:

子查询可以作为临时表,也可以作为where子句的条件,通过()包括sql,表达一个整体;
一般用在各个子句后面 select .. from ... where ...
思路:
可以把临时搜索出来的数据变成临时表,在和其他表做联表,最后做单表查询;

3.带EXISTS关键字的子查询

exists 关键字,表达数据是否存在,用在子查询里
如果内层sql 能够查到数据,返回True ,外层sql执行sql语句
如果内层sql 不能够查到数据,返回False ,外层sql不执行sql语句

select * from employee where exists (select * from employee where id = 1);
select * from employee where exists (select * from employee where id = 100);