1.DML 数据管理语言(对数据增删改)
1.新增
1.指定列添加数据
自动递增列可以不指定数据,当有一个数跳跃很大的时候,也不会不起中间数值,会从最大数值开始递增
添加一行数据用value
insert into staff(id,code,name,salary) value(1,'1001','gejunjie',1000000);
-- 添加一行
可以指定列进行添加数据
insert into staff(code,name) value ('1003','libai');
也可以不指定列进行添加数据,但是这个时候需要将每个列都指定值
insert into staff value(5,'1004','dufu',10000);
2.添加多行数据
添加多行数据用values
insert into staff(code,name,salary) values ('1002','tuoshaokai',1000000),('1003','duanxiaosong',1000000);-- 添加多行
2.修改
修改使用update
下面就使update,分别是把id=5的name改为”liqingzhao“,把name=liqingzhao的工资上涨2000,把id=5的工资下降2000,名字改为苏轼
update staff set name='liqingzhao' WHERE id=5;
update staff set salary=salary+2000 where name='liqingzhao';
UPDATE staff set salary=salary-2000,name='苏轼' WHERE id=5;
3.删除
删除某一列的某个值使用delete,只有删除表的时候才使用drop
delete FROM staff where id=1;
清空表
1.delete from staff; 是删除每一行数据,后面可以有where语句
2. truncate staff; 清空表,如果数据量比较大,可以用这个,性能优异
2.MQL 数据查询语言(查询)
1.查看当前时间
select now();
2.查询表格(指定列名)
select name,salary from staff;
3.查询所有列
select * from staff;
4.使用as指定别名 列 方法结果 表 视图
SELECT name as `姓名` from staff;
5.使用where语句
使用where查找不等于2的
SELECT * from staff WHERE id!=2;
SELECT * from staff WHERE id <> 2;
查找大于的或者小于的
SELECT * FROM staff WHERE id>2;
SELECT * FROM staff where id<2;
SELECT * from staff WHERE id>=2;
6.多条件 and/or
SELECT * from staff where id<=4 AND salary >=10000;
SELECT * FROM staff WHERE salary>10000 OR salary <5000;
7.查找 in/not in
查询多个的时候,可以使用or一个一个写,但是很麻烦,所以可以使用in
SELECT * from staff where id=1 or id=3 .......;
SELECT * from staff WHERE id in (1,2,3);
SELECT * from staff where id NOT IN(1,3);
8.bewteen and
只能用在数值的范围判断 两个范围边界也包含着,会查询出
SELECT * from staff where salary between 500 and 600;
//这样查询的是[500,600]
9.模糊查询
模糊查询有两种方式可以使用%也可以使用_作为占位符,%表示任意个数个字符
SELECT * from staff where name LIKE '%l%';
-- 查询姓张的员工
SELECT * from staff where name like '张%';
-- 查询张某
SELECT * FROM staff where name like '张__';
-- 写一个就是张某,写两个_就是张某某
10.是否存在 exists
得到的结果类似于一把锁,只要括号内查到内容就会给结果,查不到内容就不给结果
SELECT * FROM staff where EXISTS(SELECT * from staff where id=2);
11.any all
SELECT * from staff where salary >ANY(SELECT code from staff);
//any是只需要salary大于code中任意一条数据就可以
SELECT * from staff where salary >all(SELECT code from staff);
//需要salary中的工资大于code中所有的数据
12.排序 从小到大:order by 正序:asc 倒序:desc
越往前放优先级越高,当存放的两条数据相同时才看后边的排序顺序,当没有指定排序方式的时候,默认就是asc
SELECT * FROM staff ORDER BY salary
SELECT * from staff order by salary asc;
SELECT * FROM staff ORDER BY salary DESC;
13.拼接 union/union All
使用union拼接的的时候拼接的数据数量必须相等,union会去重(即把相同的结果去掉)
ubion ALL不会去重,将重复的也全部查询出来
SELECT name,salary from staff UNION
SELECT code,name from staff;
其次,去重也可以使用distinct方法
- 去重 对整个查询结果去重
SELECT DISTINCT salary FROM staff;
SELECT DISTINCT salary,name FROM staff;
14.部分查询 limit count /limit start count
limit start count 中start是查询的起始位置(下标),count是数量
SELECT * from staff order BY salary desc limit 3;
SELECT * from staff order BY salary desc limit 2,2;
15.case when then
这个可以用两个方式
1.范围判断
SELECT * ,
case
WHEN salary<15000 THEN '低'
WHEN salary>=15000 and salary<=20000 then '薪资正常' ELSE '薪资较高'
end
FROM staff;
2.数值匹配(数值重复率高的时候可以使用这个)
SELECT * ,CASE salary
WHEN 10000 THEN
'还行'
WHEN 15000 THEN
'还可以'
WHEN 18000 THEN
'挺高'
WHEN 1000000 THEN
'牛逼'
END as `level` FROM staff;
16.聚合函数(重要!!!记住)
聚合函数是将多个数据聚合成一个数据
聚合函数的null值问题,要注意,也可能会出现在面试题里面
1.最大值
SELECT max(salary) from staff;
2.最小值
SELECT MIN(salary) from staff;
3.平均数
(在计算平均数的时候,如果里面是null,会派出掉这个数据)
SELECT AVG(salary) from staff;
4.求和
SELECT sum(salary) FROM staff;
5.求个数
(在计算个数的时候,如果里面是null,会派出掉这个数据,不算在内)
如果想计算出来可以使用count(*),这个时候会计算null值
SELECT COUNT(salary) from staff;
17.分组查询
分组查询职能和聚合函数一起使用
比如查询每一个部门的平均薪资和综合薪资
SELECT department,AVG(salary) AS 平均薪资,SUM(salary) as 薪资综合 from staff GROUP BY department;
分组查询也可以用于筛选
having对分组之后的数据再筛选,这个可以使用聚合函数
也就是说下面这个语句的顺序是先group by分组,然后是跟局分组结果进行查询(使用having),最后是选出department
SELECT department from staff group by department HAVING AVG(salary)<50000;
18.联合查询
连接查询
查询老师的姓名以及教授的课程
要分别从teacher表查出 Tname ,course表查出cname
这个时候要靠teacher.Tid=coures.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;
SELECT a.Tname,b.Cname from teacher as a RIGHT JOIN course as b on a.TId=b.TId;
连接查询:外连接 内连接
外连接:
1.左外连接(left join):左表为主表,所有数据都会显示,右表是副表,只会显示和主表内容有关系的数据
2.右外连接(right join):右表为主表,与左外连接相反
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;
3.全外连接(full join):这个MySQL不支持,但是可以是用其他方法实现,左外连接union右外连接(因为union会去重,所以union之后就是full union的效果)
内连接:inner join(也可以简化成join)
内连接只会显示有对应关系的数据
SELECT a.Tname,b.Cname from teacher as a INNER JOIN course as b on a.TId=b.TId;
交叉连接
SELECT * from teacher, course;
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 a;
多张表连接查询:
查询学过张三老师的课程的学生信息:
SELECT a.* from student AS A LEFT JOIN sc b A.SId=b.SId
LEFT JOIN
course c ON c.CId=b.CId
LEFT JOIN teacher d on c.TId=d.TId
WHERE d.Tname='张三';