Lesson seventeen
MySQL数据库的DML语言、数据增删改查:
1,插入数据
insert into 表名(列名1,列名2,…) values(值1,值2,…)
eg: insert into emp(empno,ename,job,mgr,salary,reward,dept_name,dept_loc) values(2101,‘刘奕铭’,‘保洁’,NULL,1500,0,‘行政’,‘纽约’)
注意:当插入所有列的值时,表名后的列可省略
2,修改
update 表名 set 列1=新值1,列2=新值2,… where 条件
eg: update emp set job=‘厨师’,salary=8000 where empno=2102
3,删除语句
delete from 表名 where 条件
eg: delete from emp where empno=2103
4,查询
select 列名1,列名2,… from 表名 [where 条件 group by 分组列 having 分组后条件 order by 排序列 limit 起始,长度]
5,运算符:
算数运算符:+ - * / %
比较运算符: = != <> > >= < <= in between…and… like not like (% _) is null is not null
逻辑运算符: and or not
位运算符:~ ^
6,分组
select 分组列,分组函数… from 表名 group by 分组列 having 分组后条件
分组函数: count sum avg max min
eg: select dept_name,count(1) as 总数,sum(salary) 总工资,avg(salary) 平均工资,max(salary) 最高工资,min(salary) from emp GROUP BY dept_name
select dept_name,count(1) as 总数,sum(salary) 总工资,avg(salary) 平均工资,max(salary) 最高工资,min(salary)
from emp
where reward!=0
GROUP BY dept_name having avg(salary)<5000
7,排序 top_N
select * from emp order by salary ASC|DESC
默认时升序
8,limit限制
select * from emp order by salary DESC LIMIT 2,2