106 记录操作

# 增： insert into user select user,password from mysql.user; # 删除： delete from 表 where 条件 #改： update 表 set 字段=值 where 条件； #查： #单表查询： select distinct 字段1，字段2，字段3，...from 表名 where 过滤条件 group by 分组的条件 having 筛选条件 order by 排序字段 limit n； 简单查询： select name,sex from emp; select * from emp; 避免重复 distinct： select distinct sex,post from emp; 进行四则运算： select name as 名字,salary*12 as 年薪 from emp; concat()拼接记录的内容 select name,concat(salary*12,"$") as 年薪 from emp; select name,concat_ws(":","annual_salary",salary*12,"$") as 年薪 from emp; select ( case when name="egon" then name when name="egon" then concat(name,"_sb") =================where=============== select * from emp where id >= 3 and id <=5; select * from emp where id between 3 and 5; select * from emp where id = 3 or id=5 or id=7; select * from emp where id in (3,5,7); select * from emp where id like "jin%" select * from emp where id like "jin__" select * from emp where name regexp "n$"; --正则 ===============group by============== select * from emp group by depart_id 分完组之后只能看到分组的字段以及聚合的结果 max() min() avg() sum() count() select depart_id,count(id),avg(salary),max(age),min(salary),sum(salary) from emp group by depart_id; # 每个部门都有多少个人 select depart_id,count(id) from emp group by depart_id; # 每个职位男生的平均薪资 select post,avg(salary) from emp where sex="male" group by post; select post, group_concat(name) from emp group by post; select post, group_concat(name) from emp where sex="male" group by post; ===============having===============' # having与where本质区别就是在于having是在分组之后发生过滤，可以使用聚合函数，where则不能。 select max(salary) from emp where max(salary) > 100000; # 找出来男生平均薪资大于3000的职位 select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 8000; =================order by================ select * from emp order by salary; select * from emp order by salary desc; select * from emp order by age,id desc; ====================limit=================== # 可用于分页 select * from emp limit 0,5; --0是起始位置，5是显示的记录条数 select * from emp limit 5,5; select * from emp limit 10,5; select * from emp limit 15,5; select * from emp limit 20,5;