MySQL基础学习知识点和重点（二）初级查询和事务

MySQL基本知识和重点之初级查询和事务

一、约束

约束的目的：使得数据更准确，更完整。
分类:
1、键约束
（1）主键约束（2）唯一键约束（3）外键约束
2、非空约束
3、默认值约束
4、自增约束
5、检查约束（mysql暂时没有支持）

1.主键约束

1.1 概述

关键字 ：primary key	
特点：增加主键约束的列（字段）的值必须是**非空 + 唯一的**，一个表只有一个主键约束
作用：保证表中不会出现两条无法区分的记录
要求：每一张表都必须有主键约束
分类：（1）单列主键约束 （2）复合主键约束

1.2使用主键约束

1、创建主键约束
（1）在建表时指定主键约束
create table 【数据库名.】表名称(字段1 数据类型 primary key,字段2 数据类型,....);
或者：create table 【数据库名.】表名称(字段1 数据类型,字段2 数据类型,....,primary key(字段1));
例如：create table dept(id int primary key,name varchar(20),description varchar(100));
（2）建表后指定主键约束
create table dept(id int,name varchar(20),description varchar(100));
修改表结构：alter table dept add primary key(id);	
2.删除主键约束
修改表结构：alter table 表名称 drop primary key;
3.复合主键
（1）在建表时指定主键约束
create table 【数据库.】表名称(字段1 数据类型,字段2 数据类型,字段3 数据类型,...,primary key(字段列表));
==说明==：复合主键不能在列后面加，需要单独指定
（2）建表后指定主键约束
alter table 【数据库.】表名称 add primary key(字段列表);

2.唯一键约束

2.1 概述

关键字：unique key
特点：指定了唯一键的列的值必须唯一，不能重复
作用：给主键以外的列，限定唯一性
唯一键分类
唯一键和主键的区别：
（1）主键不能为空，唯一键可以为空
（2）主键约束，一个表只能有一个，而唯一键可以有很多个

2.2使用唯一键

1、如何创建/指定唯一键
（1）在建表时
create table 【数据库名.】表名称(字段1 数据类型 primary key,字段2 数据类型 【unique key】,字段3 数据类型 【unique key】,…);
或者： create table 【数据库名.】表名称(字段1 数据类型 primary key,字段2 数据类型 ,字段3 数据类型 ,…,unique key(字段2), unique key(字段3));#此法为分别唯一
或者：create table 【数据库名.】表名称(字段1 数据类型 primary key,字段2 数据类型 ,字段3 数据类型 ,…,unique key(字段列表)); #此法复合唯一
（2）在建表后
修改表结构：（运用DDL）
alter table 【数据库名.】表名称 add unique key(字段名);
alter table 【数据库名.】表名称 add unique key(字段列表); #复合唯一
2、删除唯一键
修改表结构：alter table 【数据库名.】表名称 drop index 索引名;
如果不知道索引名，可以通过如下的语句查询：
show index from 表名称;
这里提到了mysql中一个重要概念，索引：index
索引的作用：为了提高查询效率，而设置索引
我们的键约束（主键、唯一键、外键），都会自动创建索引。
因为既然你建立键约束，那么该列的值一定很关键，那么在实际中肯定经常用他们的值来查询。
因此，为了提高查询效率，会自动在这些列上增加索引。

3.非空和默认值约束

3.1 如何指定非空约束

（1）建表时
create table emp(
eid int primary key, #员工编号
ename varchar(20) not null, #姓名
cardid varchar(18) unique key not null , #身份证号
tel varchar(11) unique key not null,#电话号码
gender char not null default ‘男’#性别
);
（2）建表后
create table 【数据库名.】表名称(
字段1 数据类型 primary key,
字段2 数据类型 【unique key】【not null】【default 默认值】,
字段3 数据类型 【unique key】【not null】【default 默认值】,
…);

3.2 如何去掉非空和默认值约束

假设原来gender char添加了非空和默认值约束：
alter table emp modify gender char not null default ‘男’;
去掉非空和默认值约束操作为：
alter table emp modify gender char ;

4. 自增约束

4.1 概述

关键字：auto_increment
特点:（1）一个表只能有一个自增列
（2）自增列必须是整型的
（3）自增列必须是键列，例如：主键，唯一键

4.2 如何指定自增

create table emp(
eid int primary key auto_increment,
ename varchar(20) not null
);

5. 外键约束

5.1 概述

关键字：foreign key
特点：（1）约束的是两张表的关系，即需要两张表，或者一张表虚拟成两张表；
（2）两张表分为主表（父表）和从表（子表）即外键的建立/指定是在从表（子表）上建立。
（3）被参考的表称为主表，主表的被参考列必须是主键或唯一键
（4）一个表可以有多个外键

5.2 如何指定外键

1、在建表时指定外键
（1）建表的顺序：先建主表，再建从表
从表的语法：create table 【数据库名.】表名称(
字段1 数据类型 primary key,
字段2 数据类型 【unique key】【not null】【default 默认值】,
…,
foreign key(从表的外键列) references 主表名(主表被参考的列名));
（2）删表的顺序 ：先删从表，再删除主表
（3）添加/修改从表数据
添加/修改从表记录时，引用主表的列的值必须是存在的。
例如：添加/修改员工表时，员工所在部门的值必须引用部门表的部门编号，保证该部门编号是存在的。
（4）删除/修改主表记录
A：默认情况下，如果主表的被参考列的值被引用，那么就不能轻易的被删除和修改。
foreign key(从表的外键列) references 主表名(主表被参考的列名) 【on update restrict/no action】 【on delete restrict/no action】
B：如果在建立外键时，指定了“级联”策略，那么可以做到级联修改和删除
foreign key(从表的外键列) references 主表名(主表被参考的列名) 【on update cascade】 【on delete cascade】
C:如果在建立外键时，指定了“置空”策略，那么可以做到主表的记录被修改或删除时，从表的对应字段变为NULL
foreign key(从表的外键列) references 主表名(主表被参考的列名) 【on update set null】 【on delete set null】
2、建表后指定外键
alter table 从表名称 add foreign key(从表的字段) references 主表名(主表被参考的列名);
3.同一张表，自引用
create table emp(
eid int primary key,
ename varchar(20) not null,
managerid int,
foreign key(managerid) references emp(eid)
);
4.如何删除外键
alter table emp drop foreign key 外键约束名;
5.如何查看外键约束名：
select * from information_schema.table_constraints where table_name = ‘表名称’ ;

二、运算符

2.1算术运算符

加：+
减：-
乘：*
除：/ 或 div 其中div方法只保留整数部分
模：% 或 mod
例如：
#查询员工的姓名和原来的薪资和涨薪1000元后的薪资
SELECT ename,salary,salary+1000 from t_employee;
#查询员工的姓名，和每天的薪资，假设每个月的工作日是22天
SELECT ename AS “姓名”, salary / 22 AS “日薪” FROM t_employee

2.2 比较运算符

大于：>
小于：<
大于等于：>=
小于等于：<=
等于：= 注意 赋值和比较都是用=
不等于：!= 或 <>
安全等于：<=>
例如：
#查询奖金比例是NULL的员工
SELECT * FROM t_employee WHERE commission_pct IS NULL;
SELECT * FROM t_employee WHERE commission_pct <=> NULL;
经典错误写法：
SELECT * FROM t_employee WHERE commission_pct = NULL;

2.3 逻辑运算符

与：&& 或 and
或：|| 或 or
非：! 或 not
例如：
#查询奖金比例非空的员工
SELECT * FROM t_employee WHERE commission_pct IS NOT NULL;

2.4 区间范围和集合运算符

区间范围：between xx and yy：[xx,yy]
not between xx and yy： 小于xx 或 大于yy
集合范围：in (值列表) not in(值列表)
例如：
#查询薪资大于等于10000 并且小于等于15000的员工
SELECT * from t_employee WHERE salary BETWEEN 10000 AND 15000;
#查询籍贯不是 “浙江”、“北京”、“上海”、“黑龙江”的员工
SELECT * FROM t_employee WHERE native_place NOT IN (‘浙江’,‘上海’，‘北京’,‘黑龙江’);

2.5 模糊查询

like ‘xx’
xx可以用占位符： _：代表确定的一个字符， %：代表是任意个数的字符，0~n个
例如：
#查询名字中，第二个字是“冰”
SELECT * FROM t_employee WHERE name LIKE ‘_冰%’;

三、 关联查询

前提：关联查询必须是有两张或两张以上。
1、内连接：inner join
2、外连接
（1）左外连接：left join
（2）右外连接：right join
（3）全外连接：full join 但是mysql不支持
关联查询的结果一共有七种：
（1）A ∩ B 用内连接；
（2）A ： 用左连接
（3）A - A ∩ B 用左连接
（4）B
（5）B - A ∩ B
（6）A ∪ B
（7）A ∪ B - A ∩ B

3.1 内连接

（1）形式一
select 字段列表 from A表 inner join B表 on 关联条件 【where 其他筛选条件】
说明：
如果不写关联条件，会出现一种现象：笛卡尔积
关联条件的个数 = n - 1，n是几张表关联，
而且 on只能和join一起用。
（2） 形式二
select 字段列表 from A表 , B表 where 关联条件 【and 其他筛选条件】
例如：
#查询所有的员工的姓名和他所在部门的编号和部门的名称，不包括那些没有安排部门的员工
SELECT ename,t_employee.did,dname FROM t_employee INNER JOIN department ON t_employee.did = t_department.did;
或者：SELECT ename,t_employee.did,dname FROM t_employee , t_department WHERE t_employee.did = t_department.did ；
#查询所有的女员工的姓名和他所在部门的编号和部门的名称，不包括那些没有安排部门的员工
SELECT ename,t_employee.did,dname FROM t_employee INNER JOIN t_department ON t_employee.did = t_department.did WHERE t_employee.gender = ‘女’;
#查询员工编号，员工的姓名，部门编号，部门名称，职位编号，职位名称
SELECT t_employee.eid ,ename,t_department.did,dname,t_job.job_id,t_job.job_name
FROM t_employee INNER JOIN t_department INNER JOIN t_job
ON t_employee.did = t_department.did AND t_employee.job_id =t_job.job_id;
或者：SELECT t_employee.eid, t_employee.ename, t_department.did,t_department.dname, t_job.job_id,t_job.job_name
FROM t_employee , t_department , t_job
WHERE t_employee.did = t_department.did AND t_employee.job_id = t_job.job_id；

3.2 左连接

第一种情况：A
select 字段列表 from A表 left join B表 on 关联条件
第二种情况：A - A∩B
select 字段列表 from A表 left join B表 on 关联条件 where 从表的关联字段 is null
注意where后面是从表的关联字段is null，这里从表是B表。

左连接和右连接的区别：
（1）left换成right
（2）以左边的表为主还是以右边的表为主
右连接第一种情况：B
select 字段列表 from A表 right join B表 on 关联条件
右连接第二种情况：B - A∩B
select 字段列表 from A表 right join B表 on 关联条件 where 从表的关联字段 is null
注意where后面是从表的关联字段is null，这里从表是A表
例如：
#查询所有员工和他的部门编号，部门名称，包括那些没有部门的员工
SELECT * FROM t_employee LEFT JOIN t_department ON t_employee.did = t_department.did;
#查询所有没有部门的员工
SELECT * FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did WHERE t_employee.did IS NULL 此为用关联查询
也可以不用关联查询：SELECT * FROM t_employee WHERE did IS NULL

3.3 全连接

使用union实现全连接的效果
（1）A ∪ B
select 字段列表 from A表 left join B表 on 关联条件
union
select 字段列表 from A表 right join B表 on 关联条件
（2）A ∪ B - A ∩ B
select 字段列表 from A表 left join B表 on 关联条件 where 从表的关联字段 is null
union
select 字段列表 from A表 right join B表 on 关联条件 where 从表的关联字段 is null
例如：
#查询所有员工和部门信息，包括那些没有部门的员工和没有员工的部门
SELECT * FROM t_employee LEFT JOIN t_department ON t_employee.did = t_department.did
union
SELECT * FROM t_employee RIGHT JOIN t_department ON t_employee.did = t_department.did;
#查询那些没有部门的员工和没有员工的部门
SELECT * FROM t_employee LEFT JOIN t_department ON t_employee.did = t_department.did WHERE t_department.did IS NULL
union
SELECT * FROM t_employee RIGHT JOIN t_department ON t_employee.did = t_department.did WHERE t_employee.did IS NULL;

3.4 特例：自连接

特点：联合查询需要两张表，现在自连接，就一张表当两张表用。
方法：通过给表取别名的方式，把一张表变成“两张表”，注意此时表的别名不要加""
例如：
#查询员工的编号，员工的姓名，领导的编号，领导的姓名
#因为员工的信息和领导的信息都在t_employee表
#emp代表员工表，mgr代表领导表
SELECT emp.eid,emp.ename,emp.mid,mgr.ename FROM t_employee
AS emp INNER JOIN t_employee AS mgr ON emp.mid = mgr.eid#员工的领导编号 = 领导的员工编号

四、SELECT的5个子句

概述：select语句的5个子句：
（1）where：where 条件 用于从表中筛选出符合条件的记录（行）
（2）group by
（3）having
（4）order by
（5）limit
说明 这5个子句可以同时出现，也可以只出现其中的一部分，其中如果有having前面得有group by，但是有group by不一定有having。如果5个子句有多个同时出现的，那么必须按照（1）-（5）的顺序

4.1 分组函数

（1）sum 求和
（2）count 计数
（3）avg 平均值
（4）max 最大值
（5）min 最小值
例如:
#即查询全公司所有员工的工资总数
SELECT sum(salary) AS “工资总数” FROM t_employee ;
#查询全公司的员工总数
SELECT count(eid) AS “员工总数” FROM t_employee;#如果count(字段名)那么会排除该字段是null值的行
SELECT COUNT(1) AS “总人数” FROM t_employee;
或者：SELECT COUNT() AS “总人数” FROM t_employee; #如果count(常量值)或count()那么统计的是行数
#查询全公司的平均工资
SELECT avg(salary) FROM t_employee;
#查询全公司的女员工的平均工资和最高工资、总工资
SELECT avg(salary),max(salary),sum(salary) FROM t_employee WHERE gender = ‘女’;
#查询did=5部门的女员工的平均工资和最高工资、总工资
SELECT AVG(salary),MAX(salary),SUM(salary) FROM t_employee WHERE gender = ‘女’ AND did = 5;

4.2 group by：分组统计

例如：
#查询每个部门的平均工资
SELECT avg(salary) FROM t_employee GROUP BY did;
#查询每个部门的男、女员工的平均工资分别是多少
SELECT did,gender, avg(salary) FROM t_employee GROUP BY did,gender;
#如果强调排除没有部门的员工：SELECT did,gender,avg(salary) FROM t_employee WHERE did IS NOT null GROUP BY did,gender;
#查询每个部门的男、女员工的人数
SELECT did,count(*),gender FROM t_employee WHERE did IS NOT NULL GROUP BY did,gender;

4.3 having：写条件

where和having的区别：
（1）where后面是不允许使用分组函数，having后面可以加分组函数
（2）where是用于“原”表中的记录的筛选，而having是用于“统计结果”的筛选
例如：
#查询每个部门的平均工资，只显示平均工资在12000以上的
SELECT did,avg(salary) FROM t_employee GROUP BY did HAVING avg(salary) > 12000;

4.4 order by：排序

特点：order by 第一字段名/统计结果 【DESC/ASC】, 第二字段名/统计结果 【DESC/ASC】 …
例如:
#查询员工的姓名和薪资，按照薪资的从高到低排序
SELECT ename,salary FROM t_employee ORDER BY salary DESC;
#查询员工的编号，姓名和薪资，按照薪资的从高到低排序，如果薪资相同，再按照编号升序排列
SELECT eid,ename,salary FROM t_employee ORDER BY salary DESC ,eid ASC;
#查询每个部门的女员工的平均工资，按照平均工资的升序排序，并且只显示平均工资高于12000的
SELECT did,avg(salary) FROM t_employee WHERE gender = ‘女’ GROUP BY did HAVING avg(salary) >12000 ORDER BY avg(salary) ASC;

4.5 limit:分页

limit m,n
m = (page - 1)*每页的记录数
说明相当于表示从那一页起（不包括本页）开始显示
n = 每页的记录数
#查询员工信息，每页显示10条，显示第一页
SELECT * FROM t_employee LIMIT 0,10;
#查询员工信息，每页显示5条，显示第三页
SELECT * FROM t_employee LIMIT 10,5;
#查询每个部门的女员工的平均工资，按照平均工资的升序排序，并且只显示平均工资高于12000的，每页显示1条，显示第二页
SELECT did,avg(salary) FROM t_employee WHERE gender = ‘女’ GROUP BY did HAVING avg(salary) >12000 ORDER BY avg(salary) ASC LIMIT 1,1;

4.6 子查询

概述：某些情况下，当进行一个查询时，需要的条件或数据要用另外一个 select 语句的结果，这个时候，就要用到子查询。
先于当前查询执行的，并且是嵌套在当前查询中的查询叫做子查询。
子查询分为：

（1）where型

子查询嵌套在where里面
条件的运算符分为两类：
1）=,>,>=,<,<=,!= 后面接子查询的结果必须是“单值”
2）in, = any, >all,>= ,<all… 后面接子查询的结果可以是“多值”
例如:
#查询全公司最高工资的员工的信息
SELECT * FROM t_employee WHERE salary = (SELECT max(salary) FROM t_employee);
或者：SELECT * FROM t_employee WHERE salary >=all (SELECT salary FROM t_employee);
#查询和孙红雷，刘烨，范冰冰三个人中任意一个工资一样的员工
SELECT * FROM t_employee WHERE salary IN (SELECT salary FROM t_employee WHERE ename IN(“孙红雷”,“刘烨”,“范冰冰”));
或者：SELECT * FROM t_employee WHERE salary = ANY (SELECT salary FROM t_employee WHERE ename IN (‘孙红雷’,‘刘烨’,‘范冰冰’));

（2）from型

子查询嵌套在from后面
例如：
#查询每个部门编号，部门名称，和平均工资，排除没有部门的员工，包括那些没有员工的部门
SELECT t_department.* ,temp.pingjun FROM (SELECT did,AVG(salary) AS pingjun FROM t_employee WHERE did IS NOT NULL GROUP BY did) AS temp RIGHT JOIN t_department ON t_department.did = temp.did ;
注意事项 首先解释为何需要子查询，因为只用t_employee表无法得知部门名称等部门信息，需要t_department表联合查询，而且也不需要完整的t_employee表，而是需要排除排除没有部门的员工表的编号信息和平均工资。
此处用的是关联查询的右连接，主表是t_department，从表是子查询的子表。
特别的，1.因为子表太长，在调用表的时候，需要给子表取一个别名，这里取的是temp；2.这里还需要给AVG(salary) 取一个别名"pingjun"，因为AVG(salary)相当于是在t_employee表上用到了分组函数AVG，不能用"temp表.AVG(salary)"方式查询，而是需要用"temp表.pingjun"方式。（可以理解为先把计算了一次salary保存下再用，java中的Stream流也是这种原理操作。）
或者:用左连接的方式：
SELECT t_department.* , temp.pingjun FROM t_department LEFT JOIN (SELECT did,AVG(salary) AS pingjun FROM t_employee WHERE did IS NOT null GROUP BY did) AS temp ON t_department.did = temp.did;

（3）exists型

例如：
#查询部门信息，该部门必须有员工
SELECT * FROM t_department WHERE EXISTS (SELECT * FROM t_employee WHERE t_employee.did = t_department.did)
说明 把SELECT * FROM t_department的每一行记录，根据t_employee.did = t_department.did，代入到(SELECT * FROM t_employee)中查，如果可以查到结果，说明该行要留下，否则就排除掉。
使用内连接查询，也可以实现:
SELECT t_department.* FROM t_department INNER JOIN t_employee ON t_department.did = t_employee.did GROUP BY did;
或者：
SELECT DISTINCT t_department.* FROM t_department INNER JOIN t_employee ON t_department.did = t_employee.did;
说明 这两个用内连接的方法中，去重的方法可以用关键字DISTINCT 或者用GROUP BY.

五、 函数

5.1数学函数

其中，4.1介绍了分组函数，现在主要介绍单行函数。
单行函数和分组函数：只对某一行的记录做运算，分组函数是多行一起统计/合计运算。分组函数：sum,count,avg,max,min都是针对多行求一个结果。
数学函数：
round(x,y)：小数点后取y位,并且四舍五入
truncate(x,y)：直接截掉，保留x的小数点后取y位
CEIL(x) :返回大于x的最小整数；
FLOOR(x) ： 返回小于x的最大整数。
#查询员工的姓名，薪资，薪资保留小数点后一位
SELECT ename,ROUND(salary,1) FROM t_employee;

5.2 字符串函数

1.截取
mysql：下标从1开始
substring(str,index)
substring(str,start,len)
在java中：下标从0开始，
str.substring(index)
str.substring(start,end)
#查询每个员工的姓，不考虑复姓
SELECT ename AS “姓名”, LEFT(ename,1) AS “姓” FROM t_employee;
或者：
SELECT ename AS “姓名”, SUBSTRING(ename,1,1) AS “姓” FROM t_employee;
2.求长度
#查询员工的姓名的长度
mysql中：
length(str)：求字符串的长度，字节数，一个汉字对应3个字节
char_length(str) 字符串长度，1个汉字长度为1
java中：
str.length()
例如：
#查询所有名字是3个字的员工
SELECT * FROM t_employee WHERE CHAR_LENGTH(ename) = 3;
3. 字符串拼接
mysql中字符串的拼接：只能用concat()
java中字符串拼接：
（1）+
（2）str.concat(xx)
注意： 在mysql中的+，都是求和，如果不是数字，会尽力而为求和，结果不一定对。
例如：
#查询员工的姓名和手机号码，结果要显示为：孙红雷:13789098765
SELECT CONCAT(ename,’:’,tel) FROM t_employee;
4.删除空白格
在mysql中，trim系列的函数；
在java中trim()表示去掉前后空白格。
#删除”helloworld“左右的空白格,用TRIM
SELECT CONCAT(’[’,TRIM(’ hello world ‘),’]’)
#删除”helloworld“左边的空白格，用LTRIM
SELECT CONCAT(’[’,LTRIM(’ hello world ‘),’]’)
#删除”helloworld“右边的空白格，用RTRIM
SELECT CONCAT(’[’,RTRIM(’ hello world ‘),’]’)
#删除”helloworld“左右的‘&’符，用TRIM(BOTH…FROM…)
SELECT CONCAT(’[’,TRIM(BOTH ‘&’ FROM ‘&&&&&hello world&&&&’),’]’)
#删除”helloworld“左边的‘&’符，用TRIM(LEADING…FROM…)
SELECT CONCAT(’[’,TRIM(LEADING ‘&’ FROM ‘&&&&&hello world&&&&’),’]’)
#删除”helloworld“右边的‘&’符，用TRIM(TRILING…FROM…)
SELECT CONCAT(’[’,TRIM(TRAILING ‘&’ FROM ‘&&&&&hello world&&&&’),’]’)

5.3 日期时间函数

#获取当前的系统时间
SELECT NOW(),SYSDATE()
SELECT CURRENT_DATE(),CURRENT_TIME()
#查询当前的年份
SELECT YEAR(CURRENT_DATE())
例如：
#查询满足40岁的员工
SELECT * FROM t_employee WHERE (YEAR(CURRENT_DATE())-YEAR(birthday)) > 40;
#计算当前日期，再过130天是什么日期
SELECT DATE_ADD(CURRENT_DATE(),INTERVAL 130 DAY)
#计算当前日期，45天前是什么日期
SELECT DATE_ADD(CURRENT_DATE(),INTERVAL -45 DAY)
说明： 除了在JAVA中可以把字符串转日期时间，或者把日期时间转字符串，
在mysql中也可以。
SELECT DATE_FORMAT(NOW(),’%y年%c月%e日’);
SELECT STR_TO_DATE(‘19年1月18日’,’%y年%c月%e日’);

5.4 流程控制语句函数

在Java中有if…else,switch…case等流程控制语句结构。
mysql中有对应的函数
（1）ifnull(x,value)：如果x是null，就用value计算，否则还是用x计算
（2）CASE
WHEN 条件1 THEN result1
WHEN 条件2 THEN result2
…
[ELSE resultn]
END
例如：
#查询员工的姓名，薪资，奖金比例，实发工资
#实发工资 = 薪资 + 薪资 * 奖金比例
SELECT ename,salary,commission_pct, salary +salary * IFNULL(commission_pct,0) AS “实发工资” FROM t_employee;

5.5 加密函数

1）password(x)
2）md5(x) 两种不同的加密方式。
INSERT INTO t_user VALUES(1,‘charles’,PASSWORD(‘123456’));
INSERT INTO t_user VALUES(2,‘zeng’,MD5(‘123456’));
查询：SELECT * FROM t_user WHERE username=‘charles’ AND password = PASSWORD(‘123456’);

六、事务

==定义：==表示一组操作（sql），要么同时成功，要么同时失败，那么这种操作就构成了一个事务。

6.1 事务语法

涉及到的语句：
（1）开启事务的语句 start transaction;
（2）提交事务/回滚事务 commit;/rollback;

6.2 两种提交模式

（1）自动提交模式：mysql默认情况下是自动提交事务模式，即执行一句，自动提交一句，一旦提交就不能回滚。
（2）手动提交模式：如果开始了手动提交模式，那么一组sql语句，直到commit或rollback才算是结束，从这里commit/rollback到下一个commit或rollback之间算是另一个事务。
开启方式一：可通过以下语句完成： set autocommit = FALSE;此方法来开启手动提交模式，作用范围是一次连接（从登录到退出）
开启方式二：此开启方式只能用于命令行，不能用于JDBC的Java代码
start transaction;
… …(其他代码操作)
commit;/rollback;

6.3 事务的特点、特性(ACID)

（1）原子性：事务的sql语句的划分，必须小到不能在小。
这一组操作是否真的要求：要么同时成功，要么同时失败。
（2）一致性：要保证事务前后数据一致性
（3）隔离性：两个事务之间是独立。
（4）持久性：一旦提交就确定了。
特别说明： 1）mysql中只有Innodb引擎才支持事务。2）事务只对DML语句有效，对DDL语句无效。

6.4 事务的隔离级别

1. 为什么要隔离?

要保证事务的独立。但是很多时候，事务之间有互相影响的。两个事务对同一个表的同一个记录的修改，这就互相影响了。
在Java中就是线程安全问题。在mysql中这种问题也是线程安全问题。

2. 隔离级别

（1）READ-UNCOMMITTED：读取未提交的数据
（2）READ-COMMITTED：读取已提交的数据
（3）REPEATABLE-READ：可重复读取
（4）SERIALIZABLE：序列化
mysql的默认隔离级别是：（3）REPEATABLE-READ

3. 隔离问题

（1）脏读：一个事务读取了另一个事务还未提交的数据。
如果当前连接的隔离级别是READ-UNCOMMITTED，你会读取到别人未提交的数据，这个现象称为脏读。
如果避免“脏读”，就是提高隔离级别，提高为READ-COMMITTED或它以上。
（2）不可重复读： 一个事务读取了另一个事务“修改”已提交的数据，导致一个事务期间对同一个数据的前后两次读取，结果不一致。
如果当前连接的隔离级别是READ-UNCOMMITTED和READ-COMMITTED，都会出现不可重复的的现象，要避免“不可重复读”，还是提高隔离级别，提高为REPEATABLE-READ或它以上。
（3）幻读：一个事务读取了另一个事务新增加（删除）并已经提交的数据，导致一个事务期间记录数不一样。
在别的数据库幻读是需要用(4)SERIALIZABLE隔离级别，但是mysql可以用（3）就行，因为INNODB引擎做了一个优化，防止幻读。

4. 查看、修改隔离级别

查看当前连接的隔离级别：select @@tx_isolation;
修改当前连接的隔离级别：set tx_isolation = ‘READ-UNCOMMITTED’;
特别说明： mysql中（3）REPEATABLE-READ和（4）SERIALIZABLE都能避免脏读、不可重复读、幻读，那么有什么区别呢？
REPEATABLE-READ：行锁，两个客服端都是对不同行的操作才会锁。
SERIALIZABLE：表锁，只要一个客户端在使用表，整个表都被锁住，其他客户端都不允许使用。

七、权限验证

mysql：权限验证分两个阶段

7.1 是否能够连接

mysql的用户的认证是通过 主机地址 + 用户名 + 密码
如果主机地址写%，就表示任意IP都可以访问
如果主机地址写localhost，那么说明只能本机并且用localhost/127.0.0.1 才能访问

7.2 验证权限

A：全局权限，针对所有库，所有表，所有字段的权限
   如果某个操作有全局权限，那么就不判断 对象级别权限了
B：对象级别权限
   库–>表–>字段  
   每一种操作单独设置权限。