mysql数据库常见进阶使用

1. 事务
   1）mysql中的工作单元，由一个或者多个sql语句组成，“不成功便成仁”，要么全部执行成功，要么全部执行失败，以此来保证数据的一致性。
   2）事务的回滚：如果事务中的任何一个sql执行失败，ROLLBACK可以进行事务的回滚，将数据恢复到事务执行之前的状态，保证数据的一致性。
   3）前提：mysql存储引擎InnoDB
   4）事务的特征：ACID
   原子性 ATOMICITY
   事务的sql语句不可分割，要么全部成功，要么全部失败。
   一致性 CONSISTENCY
   不管事务执行成功还是失败，数据总是保持一致性的。
   隔离性 ISOLATION
   事务与事务之间是相互隔离的，不相互影响。
   持久性 DURABLITY
   事务一旦提交成功，对于数据的改变是持久性的，不能再进行事务的回滚了。
   5）事务的提交
   COMMIT显示的提交事务。
   隐式的提交事务 DDL，DCL等都可以造成事务的隐式提交。
   6）事务的操作
   BEGIN
   COMMIT
   ROLLBACK
   SAVEPOINT
   7）事务的自动提交模式
   mysql中的事务是自动提交的，即每一个sql语句后默认加COMMIT语句。
   查看mysql的事务自动提交模式：

SHOW VARIABLES  LIKE 'autocommit'

修改mysql的自动提交模式

SET AUTOCOMMIT=1   表示开启
SET AUTOCOMMIT=0   表示关闭

2. DQL
   SELECT 语句查询表中的记录
   1）简单查询

SELECT 字段1,字段2,....,字段n  FROM 表名  WHERE子句

其中：
如果查询表中所有的字段，可以使用通配符*表示。
字段后可以加别名：字段 AS 别名，AS可以省略，一般省略。
表名也可以使用AS指定别名。

2）使用运算符处理查询后的字段（不影响表中的原始数据）

+ - * /

SELECT ename, sal+2000 'salary',hiredate FROM emp 查询的工资每人增加2000元

3）NULL值
null不是0值，也不是最大值，也不是最小值，是不能确定的值。
null参与算数运算的结果仍是null。
如果想让null参与运算，可以为null指定一个默认值，参与运输，使用COALESCE。

SELECT ename, COALESCE(sal,0)+2000  'salary',hiredate FROM emp

4）去除重复记录 DISTINCT

SELECT   DISTINCT 字段名  FROM 表  WHERE子句

3. 模糊查询
   1）LIKE 用于字符串字段的模糊查询
   2）% 该符号出现的位置可以被任意个字符替代

WHERE ename  LIKE '王%' 查询姓王的员工

3）该符号所出现的位置，必须有且只能有一个字符。

WHERE ename  LIKE '王_' 比如查询全名为两个字的王姓员工（比如“王菲”）

4. 排序 ORDER BY
   1）排序是指将查询回来的结果集按照一定的规则进行排序（升序或降序）
   2）ASC 升序(默认） DESC 降序
   3）语法：

ELECT子句 ORDER BY 字段 ASC/DESC

其中：
ORDER BY 的子句必须放在where子句之后。
如果排序的字段里有null值，如果是升序排序，null值的记录在最前面，降序的话，null排在最后。
日期排序，较早的日期，认为是小的数值，靠后的日期，认为是大的数值。
字符串排序，按照A~Z排。
汉字排序，按照字典顺序，如果不是gbk编码，需要使用convert函数转换一下。

SELECT * FROM emp ORDER BY CONVERT(ename USING gbk) DESC

也可以按照多个字段排序

SELECT子句 ORDER BY 字段1 ASC/DESC, 字段2 ASC/DESC

5. 限制查询回来的结果集的条数
   1）应用场景：分页查询
   2）语法：

SELECT子句  LIMIT  n  n表示获取结果集的前n条记录

SELECT子句   LIMIT   index, length 表示从结果集的第index处开始，取length条记录。index为0表示从第一条记录。

6. 函数
   mysql中提供的功能强大，使用方便的函数，提高开发者在数据管理的查询和操作。

   语法: 函数名(参数列表)

常用函数的分类
1）数学函数
ABS(x) 返回x的绝对值
SQRT(x) 平方根
PI() 圆周率
MOD(x,y) 求x除以y的余数

MOD(4.5,3)   结果是1.5

CEIL(x) 返回大于等于x的最小整数

CEIL(3)  返回3
CEIL(3.3)  返回4

应用场景：不足1按1计算的场景

FLOOR(x) 返回小于等于x的最大整数

FLOOR(3)    3
FLOOR(3.4)  3
FLOOR(-3.4)   -4

应用场景：不足1舍弃的场景

ROUND(x,y) 保留小数y位，四舍五入。

ROUND(23.55,1) 23.6
ROUND(23.55,-1)   20     -1表示保留到十位数

TRUNCATE(x,y) 保留小数y位，后面的截断丢弃，不进行四舍五入

RAND() 产生随机数
应用场景：
随机排序：比如从题库表中随机抽取100道试题

SELECT * FROM  题库表  ORDER BY  RAND()   LIMIT 100

POW(x,y) x的y次方

三角函数

2）字符串函数
CONCAT(str1,str2,…) 字符串拼接
LOWER(str) / LCASE(str) 全部转为小写字母
UPPER(str) / UCASE(str) 全部转为大写字母
LEFT(str,n) / RIGHT(str,n) 返回左侧/右侧长度位n的子串
TRIM(str) 去除字符串首尾的空格
SUBSTRING(str,index,length)
REVERSE(str)

3）日期和时间
CURDATE() / CURRENT_DATE() 当前日期
NOW() 当前年月日时分秒
CURRENT_TIMESTAMP() 当前时间戳
DATE_ADD(hiredate，interval 10 DAY) 入职日期加十天
DATE_SUB
DATE_FORMAT(date, fmt)

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') FROM DUAL

4）流程函数
CASE
CASE value WHEN 表达式 THEN 结果
ELSE 结果
END

CASE WHEN sal>=3000  THEN '高收入'
ELSE '低收入'
END  'level'

IF
IF(表达式1,表达式2，表达式3）
如果表达式1的值为true，则返回表达式2的值，否则返回表达式3的值。

IFNULL
IFNULL(表达式1,表达2）
如果表达式1不为null，则返回表达式1的值，否则返回表达式2的值
NULLIF
NULLIF(表达式1，表达式2)
如果表达式1=表达式2，那么返回null，否则返回表达式1的值

5）其他函数
VERSION()
USER()
DATABASE()

7. 三大范式
   1）第一范式
   表中的字段设计的具有原子性，不能再分割。（视具体场景而定）
   2）第二范式
   表中有主键作为唯一标识，其他字段都依赖主键。
   （第二范式说白了就是来拆分表的）
   3）第三范式
   禁止传递依赖 A->B->C

8. 多表联查
   1）多表联查的根本原因：
   设计数据库遵循三大范式，相关的数据存在多个表中了。
   2）笛卡尔积
   两个表的笛卡尔积，以员工表和部门表为例

SELECT * FROM 表1, 表2

笛卡尔积的意义：入职员工和入职部门所有的可能的组合。
表联查过程中的中间结果集。
3）等值查询
SELECT 表1.字段1,表1.字段2,表2.字段1,表2.字段2 FROM 表1 , 表2 WHERE 连接条件

SELECT e.empno, e.ename,d.dname,d.loc  FROM emp  e,dept d  WHERE  e.deptno=d.deptno

n个表的连接条件n -1个
4）非等值连接
查询的多个表之间没有公有的字段作为等值连接的条件
查询员工的姓名、工资及工资等级。（员工信息表与工资等级表之间没有相同的字段，但可以通过员工的工资来判断符合工资等级表中的那个等级的区间来判断）
5）自连接查询
自己与自己连接查询（自身看作两个相同的表进行连接查询）
查询员工及其主管的姓名

SELECT e1.ename '员工名',e2.ename '主管名' FROM emp e1,emp e2 WHERE e1.mgr=e2.empno

9. SQL 99
   1）笛卡尔积 CROSS JOIN

SELECT * FROM 表1 CROSS JOIN 表2

2）自然连接 NATURAL JOIN
前提：两个表中有相同的字段作为连接条件
SELECT 字段 FROM 表1 NATURAL JOIN 表2

SELECT e.ename,d.dname FROM emp e  NATURAL  JOIN dept d

可以使用USING指定连接条件的字段，但是要去掉NATURAL

SELECT e.ename,d.dname FROM emp e  JOIN dept d USING(deptno)

3）ON子句
使用JOIN关键字时，可以使用ON子句指定连接条件。

4）左连接 LEFT JOIN
以join左侧的表作为主表，右侧的作为从表，主表的记录不管是否在从表中能否找到匹配记录，主表所有的记录都显示，没有匹配的字段显示null。

5） 右连接查询 RIGHT JOIN
与左连接查询一样，以join右边的表位主表，左侧的表为从表。

10. 分组函数
    MAX() / MIN() 返回最大、最小值
    SUM() 返回总和
    AVG() 返回平均值
    COUNT() 返回记录的条数

SELECT COUNT(*) FROM emp 查询员工的总数

注：分组函数排除NULL值

11. DISTINCT 去除查询结果的重复记录

SELECT DISTINCT deptno FROM emp 从员工信息表中查询部门编号
SELECT COUNT(DISTINCT deptno) FROM emp 查询员工表中有员工的部门的个数

12. 分组查询
    1）语法
    SELECT 子句 GROUP BY 字段1,字段n
    其中：
    select后所跟的字段，要包含在group by的分组字段中，否则是没有意义的。
    如果select后使用分组函数，分组函数的参数的字段，可以不是groupby 后的字段。

SELECT deptno,AVG(sal) FROM emp GROUP BY deptno 求员工表中的各部门的平均工资
SELECT deptno,job,AVG(sal) FROM emp GROUP BY deptno,job 求每个部门每个岗位的员工的平均工资

2）分组之后的结果的筛选 HAVING子句

SELECT子句 FROM 表 GROUP BY 字段 HAVING子句

HAVING子句是对已经分好组的结果进行筛选。
一般having子句中使用分组函数进行筛选和条件判断。
分组之后的数据，不能用where筛选。（WHERE出现在GROUP BY之前）
3）GROUP BY 使用分组查询中where和having区别
分组查询的select语句中可以同时包含where和having子句。
where在group by之前，having 在group by之后。
where在分组之前对数据进行限制，having是对分组之后的数据进行限制。

13. 子查询
    1）
    当查询的数据需要用到另一个查询的结果，需要子查询。
    子查询是指select语句，内嵌的select语句，称为子查询，也叫内部查询。
    一般使用小括号，把子查询语句括起来。
    （小技巧：一般可以先把括号内的当作一常量，把框架构建起来，再补充括号）
    子查询可以用在 where子句，from后，having后面。

SELECT ename,sal FROM emp WHERE sal=(SELECT MIN(sal) FROM emp) 查询最低工资的员工姓名和工资

2）单行子查询
返回结果就是一行一列（就是一个数值）
= != > < >= <=

3）多行子查询
子查询结果返回多行
IN 表示符合返回的值之一即可。
ANY 与子查询的结果中的一个相符即可。
>ANY 表示大于结果中的最小值即可
<ANY 表示小于结果中的最大值即可
=ANY 相当与IN
ALL 与子查询返回的结果中的任意一个相符
>ALL 表示大于返回结果的最大值
<ALL 小于结果的最小值
=ALL 等于所有的值，无意义。
注：如果子查询的结果有null值，不使用NOT IN

4）子查询用在FROM后
将子查询结果，当作一个临时表

出处：东软Java实训