mysql之数据操作

一、单表查询

1、语法

select distinct 字段1,字段2，字段3 from 库.表
    where 条件
    group by 分组条件
    having 过滤
    order by 排序字段
    limit n; 限制条数

2、创建表

create table employee(
    id int primary key unique auto_increment,
    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(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)
;

3、简单查询

# 去重
select distinct post from employee;

# 支持四则运算(查年薪)
select name,salary * 12 as annual_salary from employee;  # 不加as也可以，加了as更易读

# 拼接字符串
select concat('姓名：',name,'性别：',sex) as info,concat('年薪：',salary * 12) as annual_salary from employee;

select concat('<名字：',name,'>') as 名字,concat('<薪资：',salary,'>') as 薪资 from employee;

# concat_ws
select concat(name,'：',sex,'：',age) from employee;
select concat_ws('：',name,sex,age) from employee;

4、where约束

select id,name,age from employee where id > 7;

# and的用法 找到薪资大于8000的老师
select name,salary from employee where salary>8000 and post='teacher';

# between and 的用法  找薪资大于等于20000并且小于等于30000  
select name,salary from employee where salary between 20000 and 30000;

#  薪资小于20000或大于30000
select name,salary from employee where salary not between 20000 and 30000;

# in 找年龄等于28、73和81的
select name,age from employee where age in(28,73,81);

# 查找空要用is null 而不是 = ''
select * from employee where post_comment is null;

# like(模糊匹配)，匹配名字是jin开头的
select * from employee where name like 'jin%';  # %代表任意多个字符

select * from employee where name like 'jin_';  # _代表任意一个字符
select * from employee where name like 'jin___';

5、group by

分组一定是找大部分都相同的字段

如果有where，where一定要在group by之前，没有也可以。

set global sql_mode='ONLY_FULL_GROUP_BY'; # 把sql设置成一个严格的分组模式,只能取分组的字段

select * from employee group by post;  报错
select post from employee group by post;  # 这么取，分完组之后只能取分组字段的值

# 聚合函数，从一个组当中，聚合出你想要的结果
max
min
avg
sum
count

# 每个职位有多少个员工
select post ,count(id) as emp_count from employee group by post ;
 
# 每个部门的最大、最小、平均工资
select post ,max(salary) as max_salary from employee group by post ;
select post ,min(salary) as min_salary from employee group by post ;
select post ,avg(salary) as avg_salary from employee group by post ;

# 年龄总和
select post ,sum(age) as emp_count from employee group by post ;

# 分组之后，只能取分组的字段以及聚合函数的结果。
# 没有group by则默认整体算做一组
select max(salary) from employee;

# group_concat
select post,group_concat(name) from employee group by post;  # 把以职务分组的姓名都列出来

6、having

having用在分组之后,并且可以使用聚合函数。

# 1 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(name),count(id) from employee group by post having count(id) < 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;

7、order by

默认是升序排序

# 升序
select * from employee order by age asc;   
select * from employee order by age; 

# 降序
select * from employee order by age desc; 

# 先按照年龄升序排，如果年龄相同的话，就按照id降序排
select * from employee order by age, id desc;

8、limit

# 找到工资最高的那个人的信息
select * from employee order by salary desc limit 1;

# 每次从数据库取5条数据(网页)，真正开发的时候会用的缓存机制，因为下面这样取不是直接跳到你要取得那5条，而是从第1条开始数的，效率低。
select * from employee limit 0,5;
select * from employee limit 5,5;
select * from employee limit 10,5;
select * from employee limit 15,5;

9、总结

1、语法顺序

select post,count(id) as emp_number from employee
where salary > 1000
group by post
having count(id) > 1
order by emp_number desc
;

2、执行顺序

from --> where --> group by --> having --> select --> distinct --> order by --> limit

补充：正则表达式

select * from employee where name regexp '^jin.*(g|n)$';

二、多表查询

1、连表操作

#建表
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 department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert 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)
;

三种连接表的方式

#0 两个表先做笛卡尔积，在筛选。

# 1 内连接：只连接匹配的
select * from employee inner join department on employee.dep_id = department.id;

# 2 左、右连接
# 左连接：在内连接的基础行，保留左表
select * from employee left join department on employee.dep_id = department.id; # 左表没有对应的也会出来
# 右链接：和左连接相反
select * from employee right join department on employee.dep_id = department.id;

# 3 全外连接：在内连接的基础上保留匹配不上的记录
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;

# 实现原理：左右连接加起来后去重

2、select语句执行顺序

# 查询平均年龄大于30岁的部门名
select department.name,avg(age) as 'average age' from employee inner join department on employee.dep_id = department.id 
group by department.name 
having avg(age) > 30;

# 执行顺序
# from --> on --> join --> where --> group by --> having --> select --> distinct --> order by --> limit

3、子查询

1、带in关键字的子查询
# 查询平均年龄25岁以上的部门名
select name from department where id in 
(select dep_id from employee
    group by dep_id
    having avg(age) > 25)
;

# 查看技术部的员工姓名
select name from employee where dep_id =            
(select id from department
    where name = '技术')
;

# 查看不足1人的部门名
select name from department where id not in
(select distinct dep_id from employee)
;

2、带比较运算符的子查询
select name,age from employee where age >
(select avg(age) from employee)
;


3、带exists关键字的子查询
select * from employee
where exists
(select id from department where name='技术');
# 如果存在，where exists返回True,select就按照查询语句显示;不存在，where exists返回Flase，查询结果为empty。

4、和自己进行链接
# 每个职位最新入职的员工

select * from employee as t1
    inner join
(select post,max(hire_date) as max_hire_date from employee
    group by post) as t2
    on t1.post = t2.post where t1.hire_date = t2.max_hire_date;