DML
数据管理语言,对数据增删改
新增
指定列添加数据
insert into staff(id,code,name,salary) VALUE(1,'10001','李四',9000);
自动递增列可以不指定;自动递增列不会回撤,不会补齐;自动递增列从该列最大值递增
not null列必须要指定数据
insert into staff(code,name,salary) VALUES('10002','王五',10000),('10003','赵六',11000);
可以不指定列,但是需要将全部列指定数据
INSERT into staff value(12,'10012','王维',3000);
删除数据 delete
delete from staff where id=1;
修改/编辑 update
update staff set name='李清照' WHERE id=5;
UPDATE staff set salary=salary+2000 where name='李清照';
UPDATE staff set name='王维',salary=salary-2000 where id=12;
清空表
delete from staff;-- 删除每一行数据
TRUNCATE staff;-- 清空表 性能更优越
查询 MQL
SELECT 1;
select now();
-- 查询表格
-- 指定列名查询
select name,salary from staff;
-- 使用*代替所有的列
select * from staff;
-- 使用as 指定别名 列 方法结果 表 视图
select name as `姓名` from staff;
-- where 指定条件语句
select * from staff where id=15;
select * from staff where id!=1;-- 不等于
select * from staff where id<>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;
SELECT * from staff where salary IS NOT NULL;
SELECT * from staff where salary <=> NULL;-- 等于null
-- 多条件 and or in not in
select * from staff where id<=100 AND salary>=1000;
SELECT * FROM staff where salary>100 OR salary<500;
SELECT * from staff where id IN (1,3,5,7,8,10);
SELECT * from staff where id NOT IN (1,3,5,7,8,10);
-- between and
SELECT * from staff WHERE salary BETWEEN 5000 and 10000;
-- 模糊查询 LIKE %任意个数任意字符 _有且只有一个字符
select * from staff where name like '%a%';
select * from staff where name like '张%';-- 姓张的员工
select * from staff where name like '张_';-- 张某
-- 是否存在 exists
SELECT * from staff WHERE exists (SELECT * FROM staff WHERE id=1);
-- 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 all不会去除重复结果
select name,salary FROM staff UNION SELECT code,name from staff;
-- 去重 对整个查询结果去重
select DISTINCT salary,name FROM staff
-- 部分查询
select * from staff order by salary desc limit 3;-- 查询前三名
-- 查询第三名到第五名 limit start,count;
select * from staff order by salary desc limit 2,3;
-- case when then
select *,case WHEN salary<15000 THEN '薪资较低'
WHEN salary<=20000 THEN '薪资正常'
ELSE '薪资较高'
end
from staff;
SELECT *,CASE salary
WHEN 12000 THEN
'还行'
WHEN 15000 THEN
'挺高'
WHEN 18000 THEN
'哇哦'
WHEN 20000 THEN
'好厉害'
END AS `level` from staff;
-- 分组 聚合函数 将多个数据聚合成一个
select max(salary) from staff;-- 最大值
select min(salary) from staff;-- 最小值
select avg(salary) from staff;-- 平均数
select sum(salary) from staff;-- 求和
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)<50000;
-- null 忽略
select department,avg(salary),count(salary),count(*) from staff GROUP BY department
连接查询
外连接
左外连接 left join
右外连接 right join
全外链接 full join; mysql不支持全外连接
select a.Tname,b.Cname from teacher as a
left JOIN course as b on a.tid=b.tid;
select a.Tname,b.Cname from teacher as a
right JOIN course as b on a.tid=b.tid;
左连接以左表为主表,会显示所有数据.右表为副表,只会显示与主表有关的数据;右连接相反
内连接
内连接inner join 也可以写成join; 只显示有对应关系的数据
select a.Tname,b.Cname from teacher as a
INNER JOIN course as b on a.tid=b.tid;
交叉连接 cross join
子查询
-- 子查询
SELECT sname from student where sid IN(
SELECT sid from sc where score<60
);
-- 将子查询当作表进行查询
SELECT Sname from (SELECT * from student where sid=01) as B;