一、表记录相关操作
增加记录
修改记录
删除记录
查询记录（单表查询，多表联合）
单表查询语法(一定要注意执行顺序！)
select distinct 字段1,字段2,字段3,... from 表名
where 过滤条件
group by 分组的条件
having 筛选条件
order by 排序字段
limit n;
# 插入 mysql> create table user(name varchar(16),password varchar(10)); Query OK, 0 rows affected (0.29 sec) mysql> mysql> 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 name as 名字,sex 性别 from emp; select * from emp; # 避免重复（针对的是记录） select distinct post from emp; # 进行四则运算 select name as 名字,salary*12 as 年薪 from emp; # concat()拼接记录的内容 select name ,concat(salary*12,"$") from emp; select name ,concat("annual_salary",':',salary*12) as 年薪 from emp; select name ,concat("annual_salary",':',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="alex" then concat(name,"_dsb") else concat(name,"_sb") end ) as 名字 from emp; ===========================================where select * from emp where id >= 3 and id <= 5; select * from emp where id between 3 and 5; select * from emp where id not 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 not in (3,5,7); select * from emp where id=3 or id=5 or id=7; select * from emp where name like 'jin%'; select * from emp where name like 'jin___'; select * from emp where name regexp 'n$'; mysql> select * from emp where post_comment is not null; Empty set (0.00 sec) mysql> update emp set post_comment='' where id=3; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from emp where post_comment is not null; +----+---------+------+-----+------------+---------+--------------+---------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+---------+------+-----+------------+---------+--------------+---------+--------+-----------+ | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | | 8300.00 | 401 | 1 | +----+---------+------+-----+------------+---------+--------------+---------+--------+-----------+ 1 row in set (0.00 sec) mysql> select * from emp where name="丫丫"; select * from emp where name regexp "丫$"; select * from emp where name like "丫_"; select * from emp where name regexp "^程"; select hex(name) from t4 where hex(name) regexp 'e[4-9][0-9a-f]{4}'; ===========================================group by 分完组之后只能看到分组的字段以及聚合的结果 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是在分组之后发生过滤，可以使用聚合函数 mysql> select max(salary) from emp where max(salary) > 100000; ERROR 1111 (HY000): Invalid use of group function mysql> select max(salary) from emp having max(salary) > 100000; +-------------+ | max(salary) | +-------------+ | 1000000.31 | +-------------+ 1 row in set (0.00 sec) mysql> # 找出来男生平均薪资大于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; ps:asc升序（默认为升序，一般就不要写了） desc降序 select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 3000; mysql> select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 3000; +-----------------------------------------+---------------+ | post | avg(salary) | +-----------------------------------------+---------------+ | operation | 16000.043333 | | teacher | 175650.051667 | | xxxxxxxxx驻沙河办事处外交大使 | 7300.330000 | +-----------------------------------------+---------------+ 3 rows in set (0.00 sec) mysql> select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 3000 order by avg(salary); +-----------------------------------------+---------------+ | post | avg(salary) | +-----------------------------------------+---------------+ | xxxxxxxxx驻沙河办事处外交大使 | 7300.330000 | | operation | 16000.043333 | | teacher | 175650.051667 | +-----------------------------------------+---------------+ 3 rows in set (0.00 sec) mysql> select post,avg(salary) as v from emp where sex="male" group by post having avg(salary) > 3000 order by v; +-----------------------------------------+---------------+ | post | v | +-----------------------------------------+---------------+ | xxxxxxxxx驻沙河办事处外交大使 | 7300.330000 | | operation | 16000.043333 | | teacher | 175650.051667 | +-----------------------------------------+---------------+ 3 rows in set (0.00 sec) mysql> 分页显示：（比如淘宝里商品页面显示，可以分好多页） select * from emp limit 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;
二、
三、
四、
五、