MySQL之DML和DQL

SQL语言共分为四大类：

• 数据查询语言DQL
• 数据操纵语言DML
• 数据定义语言DDL
• 数据控制语言DCL
  

修改表：

SELECt * FROM t_user;
UPDATe t_user
SET usr_account = '赵六',usr_password = '000'
WHERe usr_id = '4';

删除表：

DELETE FROM t_user
WHERe usr_id = '6'; -- 删除一行。
INSERT INTO t_user(usr_account,usr_password) VALUES('xx','999'); -- id从7开始
TRUNCATE t_user; -- 摧毁整张表（数据全部不见，ID从头开始）
DELETe t_user; -- 删除整张表（数据全部不见，ID不会从头开始）

查询表：

SELECT usr_account,usr_id*5,UUID(),3.141592654
FROM t_user; -- 查询语句，可以是列名，表达式，函数或者常量

SELECt UUID() FROM DUAL; -- 伪表查询

给表取个别名，再查询这张表。查询不会修改原表，查询结果的本质是内存中的表。

SELECt * FROM (SELECt * FROM t_user) AS u; -- AS可省略

-- 逻辑与或非 优先级：（高——》低）NOT->AND->OR
SELECt * FROM t_user WHERe usr_id**=**2; -- 一个等于
SELECt * FROM t_user WHERe usr_id!=2;
SELECt * FROM t_user WHERe usr_id<>2;-- 表示不等于
SELECt * FROM t_user WHERe usr_id>2;
SELECt * FROM t_user WHERe usr_id>=2;
SELECt * FROM t_user WHERe usr_id<2;
SELECt * FROM t_user WHERe usr_id<=2;
SELECt * FROM t_user WHERe usr_id<=2 AND usr_accound='zs';-- 尽可能让容易判断的写在前面
SELECt * FROM t_user WHERe usr_id<=2 OR usr_accound='zs';
SELECt * FROM t_user WHERe NOT usr_id<=2;

排序查询

SELECt * FROM t_user ORDER BY usr_account; -- 默认升序
SELECt * FROM t_user ORDER BY usr_account ASC;
SELECt * FROM t_user ORDER BY usr_account DESC; -- 倒序
SELECt * FROM t_user ORDER BY usr_password,usr_id DESC;-- 先按前者升序，若相同，按后者倒序

SELECt usr_id i,usr_account a,usr_password p FROM t_user u;
SELECt COUNT(*) cnt FROM t_user; -- 聚合函数 别名
SELECt CONCAt(usr_id,usr_id),CONCAt(usr_account,usr_password) FROM t_user u; -- 拼接函数



SELECt * FROM t_user  WHERe usr_password IS NULL; -- 查找密码为空的，用IS NULL

模糊查询：关键字like

SELECt * FROM emp
WHERe `name` LIKE '孙%'; -- %匹配所有字符（只要有孙字开头的）
SELECt * FROM emp
WHERe `name` LIKE '孙__'; -- _匹配一个字符（一个下划线代表一个字符）
SELECt * FROM emp
WHERe `name` LIKE '%八%'; -- % %匹配某个字符（包含某个字符的）

筛除查询：关键字distinct

SELECt DISTINCT job FROM emp;

统计函数

SELECt SUM(sal),MAX(sal),MIN(sal),AVG(sal),COUNT(*) FROM emp;

判空函数

SELECt sal,comm,sal+IFNULL(comm,0) FROM emp; -- 如果comm为空，用0代替，如果不为空，就是它本身
SELECt sal,comm,sal+IF(comm,comm,0) FROM emp; -- 如果comm为真，本身，如果comm为假，用0.

分页查询 关键字limit （mysql特有）

SELECt * FROM emp LIMIT m,n; -- m起始的下标（0起算），n要查询多少行
SELECt * FROM emp LIMIT 0,3; -- 第一页
SELECt * FROM emp LIMIT 3,3; -- 第二页
SELECt * FROM emp LIMIT 6,3; -- 第三页
SELECt * FROM emp LIMIT 5; -- 不写m默认0，n=5

SELECt INSTR('abcdefghijklmn','abcd') FROM DUAL; -- 查找，下标从1起算，找不到就为0
SELECt SUBSTr('abcdefghijk',2) FROM DUAL;-- 从第2个开始找
SELECt SUBSTr('abcdefghijk',3,4) FROM DUAL;-- 从第3个开始找，找4个
SELECt SUBSTRINg('abcdefghijk',3,4) FROM DUAL; -- 同上
SELECt CONCAt('abcefgh','123') FROM DUAL; -- 拼接
SELECt UPPER('abcDEFG') FROM DUAL; -- 全部大写
SELECt LOWER('abcDEFG') FROM DUAL; -- 全部小写

获取年月日时分秒、年月日、时分秒、字符转日期、日期转字符

SELECt NOW(),CURDATE(),CURTIME(),
STR_TO_DATE('2020-10-26','%Y-%m-%d'),
DATE_FORMAT(NOW(),'%h-:%i:%s %d-%m-%Y')
FROM DUAL;

SELECt CEIL(3.14), -- 天花板
FLOOR(3.99), -- 地板
ROUND(3.5), -- 四舍五入
RAND(), -- 随机小数
MOD(8,3), -- 取余
TRUNCATE(3.141592654,3) -- 截取几位小数
FROM DUAL;

SELECt VERSION(), -- 软件版本号
DATAbase(), -- 仓库
USER() -- 用户
FROM DUAL;

用于主键和密码：

UUID()生成唯一的36位唯一字符串，0-9a-f字符构成。如果去掉固定位置的中间杠，就是32位唯一字符串。

MD5(‘密码’)生成32位密文字符串 。

SELECt UUID(),MD5('123456') FROM DUAL;

exists:不关注查询内容。只关注有没有结果集，有就为真。

SELECt * FROM emp 
WHERe EXISTS (SELECt * FROM emp WHERe sex='女');

语法：SELECt 被分组的列，聚合函数 FROM 表名 WHERe 过滤条件 GROUP BY 列 HAVINg 过滤条件。

-- 查询每个部门的员工总数
SELECt deptno,COUNT(*) FROM emp GROUP BY deptno;
-- 查询每个部门的女员工总数
SELECt deptno,COUNT(*) FROM emp WHERe sex='女' GROUP BY deptno; 
-- 查询每个部门的平均薪水，只保留高于2000的
SELECt deptno,AVG(sal) FROM emp GROUP BY deptno HAVINg AVG(sal)>2000;

where 对表过滤，having 对查询结果过滤。

1.内联查询

SELECt ename,loc FROM emp e,dept d WHERe e.deptno=d.deptno;
-- 等价于
SELECt ename,loc FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

2.左联和右联

RIGHT JOIN ,右表为主表，右表的每一条记录都应该出现

-- RIGHT JOIN ,右表为主表，右表的每一条记录都应该出现
SELECt ename,loc FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

左联同理。