MySQL学习笔记第二天

DML数据管理语言

对数据增删改

新增insert

指定列添加数据
INSERT INTO staff(id,code,name,salary) VALUE (1,'1002','李四',9000);

可以不指定列，但是需要将全部列指定数据
INSERT INTO staff VALUE(12,'1012','王伟',3000);

也可以指定部分列添加数据
INSERT INTO staff(code,name) VALUE ('1008','杜甫');

自动递增列可以不指定id
INSERT INTO staff(code,name,salary) VALUES ('1003','王五',10000),('1004','赵六',11000);
自动递增列不会回撤不会补齐(删除3,4列,再运行,自动补齐的id是5,6)从程序计数器最大值递增

在添加数据时，如果列属性为not null，则该列必须要指定数据

–删除 delete

DELETE FROM staff WHERE id=1;

–修改/编辑 UPDATE

UPDATE staff SET NAME='李清照' WHERE id=13;
UPDATE staff SET salary=salary+2000 WHERE NAME='李清照';
UPDATE staff SET salary=salary-2000,name='刘禹锡' WHERE id=2;

– 清空表

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>15;
SELECT * FROM staff WHERE id>=15;

SELECT * FROM staff WHERE id<15;
SELECT * FROM staff WHERE id<=15;

– 对null值的判断

等于null
SELECT * FROM staff WHERE salary is NULL;
SELECT * FROM staff WHERE salary <=> NULL;
不等于null
SELECT * FROM staff WHERE salary IS NOT NULL;

– 多条件

SELECT * FROM staff WHERE salary>=100 and salary <=10000;
SELECT * FROM staff WHERE salary>1000 or salary <500;

– in和not in

SELECT * FROM staff WHERE id IN (1,3,5,7,8,10,12);
SELECT * FROM staff WHERE id NOT IN (1,3,5,7,8,10,12);

between and

SELECT * FROM staff WHERE salary BETWEEN 500 AND 6000;

模糊查询 like 占位符 % (任意个数，任意字符) _占且只占一个字符

SELECT * FROM staff WHERE NAME LIKE '%刘%';

根据姓氏查询
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 >all(SELECT 1000);

– 排序 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;
SELECT * FROM staff ORDER BY salary,CODE DESC;

– 拼接（合并）查询结果 俩个列的数据的数量需要相同

==UNION会将重复的结果去除==
SELECT NAME,salary FROM staff UNION
SELECT CODE,NAME FROM staff

==UNION ALL不会去除重复数据==
SELECT NAME,salary FROM staff UNION ALL
SELECT NAME,salary FROM staff

– 去重 对整个查询结果去重

SELECT DISTINCT salary,NAME FROM staff;

– 部分查询 limit

SELECT * FROM staff ORDER BY salary DESC LIMIT 3;
-- 查询第三名到第四名  limit start,count start的下标从零开始
SELECT * FROM staff ORDER BY salary DESC LIMIT 2,2;

– case when then
– 范围性判断

SELECT * ,CASE
	WHEN salary<10000 THEN '薪资较低'
  WHEN salary>=10000 AND salary<=15000 THEN '薪资正常'
  ELSE '薪资较高'
END
 FROM staff;

数值匹配

SELECT *,CASE salary
	WHEN 12000 THEN '还行'
  WHEN 15000 THEN '不错'
  WHEN 20000 THEN '挺高'
  WHEN 110000 THEN 'good'
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

– count()括号内加*，或数字，都可以计算所有数量，包括null值

SELECT COUNT(*),COUNT(2) FROM staff;