MySQL增强

数据库

数据库（DataBase）：是一个按数据结构来存储和管理数据的计算机软件系统.

简而言之:存储数据的仓库/存储数据的特殊文件.

1.数据定义语言:DDL数据库建立、修改、变更及扩充功能
2.数据操纵语言:DML实现数据的插入,修改,删除,查询,统计等数据存取操作的功能称为数据操纵功能
3.数据查询语言:DQL用以从表中获得数据,确定数据怎样在应用程序给出.保留字SELECT是DQL(也是所有SQL)用得最多的动词,其他DQL常用的保留字有WHERE,ORDER BY,GROUP BY和HAVING.这些DQL保留字常与其他类型的SQL语句一起使用

数据库基础

关系型数据库和非关系型数据库
关系型数据库：在关系模型中，数据的逻辑结构为满足一定条件的二维表，表具有固定的列数和任意的行数，在数学上称为”关系”

关系型数据库的三范式:

第一范式(1NF):是对关系模式的基本要求,不满足第一范式就不是关系数据库.是指数据表的每一列都是不可分割的基本数据项,同一个列中不能有多个值.

第二范式(2NF):每个实例或行'必须有主键',区分每一行数据(可以被打破)

第三范式(3NF):要求一个数据表中,不包含其它表中的非主键信息.(在设计表的时候,为了性能,有时会打破第3范式)

常见的数据库

1.Oracle：大型企业数据库，支持数据量，速度较快，安全性非常好，提供完善的存储过程支持；新的版本提供了众多新功能；
2.DB2（IBM）：大型企业数据库，支持数据量，速度较快，安全性较好, 恢复性强；
3.SQL Server（MS）：大型企业数据库，支持数据量，速度较快，安全性较好；
4.MySQL（Oracle）：性能不错，使用方便，体积小，易扩展；是目前使用最广的关系型数据库；

查询

多表查询-笛卡尔积
没有连接条件的表关系返回结果,多表查询会产生笛卡尔积.
解决方案: 在WHERE加入有效的连接条件—->等值连接
隐藏内连接查询语法:

SELECT  <select_list>
FROM 表名称 A, 表名称 B
WHERE 查询条件  AND  消除笛卡尔积的连接条件
[ORDER BY 排序字段 [ASC|DESC] [,排序字段 [ASC|DESC] ,…]];

显示内连接查询语法:

1 2	SELECT <select_list> FROM A [INNER] JOIN B ON 消除笛卡尔积的连接条件 [JOIN ...]

内连接查询,两种查询的结果完全相同,区别在于是否能看到JOIN关键字

union

JOIN是用于把表横向连接，UNION/UNION ALL是用于把表纵向连接(一般用于做查询的临时表)
UNION 操作符用于合并两个或多个 SELECT 语句的结果集.

语法：
SELECT column_name(s) FROM table_name1
UNION|UNION ALL
SELECT column_name(s) FROM table_name2

单行函数

DAY(date):获取日期中的天数（DAYOFMONTH）
HOUR(time):返回time 对应的小时数。对于日时值的返回值范围是从 0 到 23
MINUTE(time):返回 time 对应的分钟数,范围是从 0 到 59
MONTH(date):返回date 对应的月份，范围时从 1 到 12
YEAR(date):返回date 对应的年份,范围是从1000到9999
LAST_DAY(date):获取一个日期或日期时间值，返回该月最后一天对应的值
DATE_FORMAT(data,format)

聚合分组函数

AVG：计算平均值
SUM：计算总和

COUNT ({ |[DISTINCT|ALL]expr})
COUNT()返回表中所有符合条件的记录数
COUNT(字段) 返回所有符合条件并且字段值非空的记录
(右键设置某个字段为null,如果加了where条件后, 就以where 后面的列作为计数条件)
COUNT(distinct(expr))返回不重复的，非空值的数量

MIN and MAX适用于任何数据类型
MIN：计算最小值
MAX：计算最大值

分组查询

分组查询的语法:
SELECT [DISTINCT] *|分组字段1 [别名] [,分组字段2 [别名] ,…] | 统计函数/分组函数/聚合函数
FROM 表名称 [别名], [表名称 [别名] ,…]
[WHERE 条件(s)]
[GROUP BY 分组字段1 [,分组字段2 ,…]]
[ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC]];

注意:

1出现在SELECT列表中的字段,如果出现的位置不是在聚合函数中,那么必须出现在 GROUP BY 子句中

2.在GROUP BY 子句中出现的字段，可以不出现在SELECT列表中 

3.如果没有GROUP BY子句,  SELECT列表中的任何列或表达式不能和统计/分组/聚合函数同时使用：
错误操作: select ename, sal, count(empno) from emp;

如果现在要进行分组的话，则SELECT子句之后，只能出现分组的字段和统计函数，其他的字段不能出现

使用HAVING子句对分组的结果进行限制

1.不能在 WHERE 子句中 对分组之后的结果过滤.
2.使用group by语句后,如果需要过滤, 必须使用 HAVING 子句(分组后的过滤).
3.不能在 WHERE 子句中使用聚合函数.

其他

1
2
3

select  -- from --- where  --- group by  ----having --- order by --- limit?,?
sql 执行顺序~~~
from -- where -- group by -- having -- select -- order by -- limit ?, ?

子查询

子查询指的就是在一个查询之中嵌套了其他的若干查询.
在where查询条件中的限制条件不是一个确定的值,而是来自于另一个查询结果.

子查询一般出现在FROM和WHERE子句中.

使用子查询的注意事项:

1.子查询要用括号括起来 
2.将子查询放在比较运算符的右边(增强可读性)
3.对单行子查询使用单行运算符 
4.对多行子查询使用多行运算符

语法:

1
2
3

SELECT <select_list>
FROM 'table'
WHERE 条件 操作符 (SELECT select_list FROM table);

子查询的分类:

单行单列子查询,得到的是一个值,返回的结果只包含一行数据
多行单列子查询,得到的是一个集合,返回多行或零行
多行多列子查询,得到的是一个二维表,包含多个字段的返回(一张临时的表)

单行单列子查询:

1.返回一行记录,好比一个值.
2.使用单行记录比较运算符=;>;>=;<;<=!=(针对于一个值得运算符)

多行子查询返回多行单列

1.返回多行,好比多个值
2.使用多行比较运算符

IN:与列表中的任意一个值相等

=ANY:此时和IN操作符相同
>ANY:大于子查询中最小数据
<ANY:小于子查询中最大数据

>ALL:大于子查询中最大的数据
<ALL:小于子查询中最小的数据

多列子查询

子查询返回的结果是多行多列/一行多列,只要是多列,就可以看成是一张表.
一般会把多列子查询.返回的结果当成一个临时表,接着在临时表上继续查询或者询
注意:多行多列的子查询返回的结果必须要设置一个临时表的名称.

DML操作

1.插入操作

1	INSERT INTO table [(column [, column...])] VALUES (value [, value...]),(value [, value...]),...

一条SQL的长度是有限的，可以通过调整max_allowed_packet参数(可以在my.ini文件中修改)

2.更新操作

1
2
3

UPDATE table
SET column = value [,column = value]…
[WHERE condition];

UPDATE语句也可以使用表连接，子查询等多种方式执行
3.删除操作

1 2	DELETE FROM table [WHERE condition];

事务控制

开启事务: begin
提交事务: commit
回滚事务: rollback

事务的ACID:
原子性:Atomicity
一致性:Consistency
隔离性:Isolation
持久性:Durability

控制事务的SQL语句(命令):
commit 和 rollback可以显示的控制事务
好处：
1.保证数据一致性,修改过的数据在没有提交之前是不能被其他用户看到的
2.在数据永久性生效前重新查看修改的数据
3.将相关操作组织在一起,一个事务中相关的数据改变或者都成功,或者都失败

数据库的并发问题:

MySQL使用的是repreatable read隔离级别

避免第二类丢失更新

方案1:
悲观锁: 使用数据库自身的排它锁机制(写锁)(排斥其他锁).
DML操作自动会加上排它锁 (排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁).

DQL操作需要我们手动加上排他锁.

1	SELECT * FROM 表名 FOR UPDATE.

方案2:
乐观锁: 在表中额外增加一个列,用来表示修改的版本(整数类型),修改一次就把版本增加1.

1:在表中新增一个列,用来表示修改的版本号,类型使用整数类型,初始值为0.

2:每次在修改数据之前,先发送SELECT语句去查询当前被修改数据的信息(包括版本号).

1	SELECT id,name,version FROM person WHERE id = 10;

查询出来的版本号为0.

3:发送UPDATE语句去更新数据:版本号修改递增1/判断条件中的版本号必须是刚刚查询出来的版本.

1	UPDATE person set name = 'Java' ,version = version + 1 WHERE id = 10 AND version = 刚刚查询出来的版本号(0).

4:判断update语句执行之后的受影响行数(rows),若rows>0则提交事务,否则回滚事务.

数据库的权限

权限相关命令:
GRANT:
MySQL中用于赋权限的命令是GRANT，语法为：
完整语法：
GRANT 权限 (columns)
ON 数据库对象
TO 用户 IDENTIFIED BY “密码”
WITH GRANT OPTION

三种具体的语法:

给一个存在用户赋予权限:
语法1:GRANT 权限 (columns) ON 数据库对象 TO 用户:
赋予will账户:SELECT和INSERT权限.
GRANT select,insert ON . TO will@localhost; 左边是数据库 . 右边是数据表
. 表示全局权限

创建用户,设置密码,赋予权限:
语法2:GRANT 权限 (columns) ON 数据库对象 TO 用户 IDENTIFIED BY “密码”
创建lucy用户:
GRANT ALL ON . TO lucy@localhost IDENTIFIED BY ‘1234’;

创建用户,设置密码,赋予权限,并且该用户可以继续授权给其他用户:
语法3:GRANT 权限 (columns) ON 数据库对象 TO 用户 IDENTIFIED BY “密码” WITH GRANT OPTION