DML 数据管理语言 增删改
新增 insert
#指定列添加数据
insert into staff(id,code,name,salary) VALUE(2,'1002','李四',9000);
#自动递增列可以不指定数据
insert into staff(code,name,salary) values('1003','王五',10000),('1004','赵六',11000);
#自动递增列不会回撤,不会补齐。 从该列最大值递增
#notnull列必须要指定数据
insert into staff(code,name) value('1008','杜甫');
#可以不指定列,但是需要将全部列指定数据
insert into staff value(6,'1009','王维','3000');
删除数据delete
delete from staff where id=1001;
修改/编辑 UPDATE
UPDATE staff set name='李清照' where id=4;
update staff set salary=salary+2000 where name ='李清照';
UPDATE staff set name='郑板桥',salary=salary-2000 where id = 5;
清空表 truncate
DELETE from staff;#删除每一行数据
truncate staff;#清空表 性能更快一点
查询 MQL
select 1;#查询1
SELECT new();#查询当前时间
#查询表格
#指定列名查询
select name,salary from staff;
#可以使用*来代替所有的列
select * from staff;
#可以使用as指定别名 对列,方法,结果,表,视图等
select name as `姓名` from staff;
#可以用where指定条件语句
select * from staff where id = 2;
SELECT * from staff where id != 1;#不等于1
SELECT * from staff where id <> 1;#不等于1
#大于小于
select * from staff where id>20;
select * from staff where id>=20;
select * from staff where id<20;
select * from staff where id<=20;
#对null值的判断
select * from staff where salary is null;#null不能用=
select * from staff where salary is not null;
#等于null
select * from staff where salary <=> null;
#多条件
select * from staff where id<100 and salary>10000;
select * from staff where id<100 or salary>10000;
#in / not in
select * from staff where id=1 or id=3 or id=5;
select * from staff where id in(1,3,5);
# between and 只能做数值性的判断
select * from staff where salary BETWEEN 5000 and 6000;
#like 模糊查询 %任意个数任意字符 _有且只有一个字符
select * from staff where name like '%李%';
#查询姓张的人
select * from staff where name like '张%';
#是否存在 exists
select * from staff where exists(select *from staff where 1=2);
#any大于任意一个 ALL大于全部
select * from staff where salary>any(select 25);
select * from staff where salary>all(select 25);
#排序 order by
#默认为正序
select * from staff order by salary;
#正序asc 倒叙desc
select * from staff order by salary asc;
select * from staff ORDER BY salary desc;
select * from staff order by salary asc,code desc;
#拼接(合并)查询结果 UNION
select name,salary from staff union select code,name from staff;
#union 会有一个去重的效果会将重复的结果去除
select name,salary from staff union select name,name from staff;
#union all 不会去重
select name,salary from staff union all select name,name from staff;
#去重 distinct 对整个查询结果去重
select distinct salary,name from staff;
#部分查询 limit
select * from staff order by salary desc limit 3;
#查询第三名到第5名 limit start,count;
select * from staff order by salary desc limit 2,3;
#case when then
select * ,case when salary <10000 then '薪资较低'when salary>=10000 and salary<20000 then '薪资正常'else '薪资较高' end from staff;
select * ,case salary when 12000 then '还行' WHEN 15000 then '还可以' WHEN 18000 then '挺高' when 800000 then '不是人' end from staff;
#分组 聚合函数 只有五个 将多个数据构造成一个的函数
#最大值 max
select max(salary) from staff;
#最小值 min
select min(salary) from staff;
#计算平均数 avg 会排除null
select avg(salary) from staff;
#求和 sun()
select sum(salary) from staff;
#求个数 count
select count(salary) from staff;
#分组查询
select department,avg(salary) as 平均薪资,sum(salary) as 薪资总和 from staff group by department;
#分组当中进行筛选
#having 对分组之后的数据再筛选
select department from staff group by department having avg(salary)<40000;
#NULL经常出笔试题 avg 会排除null count会排除null,count(*)会计算null值
#连接查询
#老师的姓名以及教授的课程
#分别在teacher Tname course cname
#teacher.tid = course.tid
select tname,cname from teacher left join course on teacher.tid = course.tid;
#as 起别名
select a.tname,b.cname from teacher as a left join course as b on a.TId=b.tid;
#链接查询
#外连接 左外连接 left join
# 右外连接 right join
# 全外连接 full join
#内连接 inner join 也可以写为join
#只显示有对应关系的数据
#左连接以左表为主表,会显示所有的数据,右表为附表,只会显示和主表有关系的数据,右连接相反。
#交叉链接 Cross join 很少使用
select * from teacher a,course b where a.TId=b.TId;
#子查询 在一个查询里面又有一个查询
select sname from student where sid in (
select sid from sc where score<60;
)
#将子查询当作表进行查询
select sname from (SELECT * from student where sid =01)as i;