一、MySQL五子句
1、基本概念
select语句中包含一个查询子句（五子句，==包括where、group by、having、order by和limit==）
基本语法：
select */字段 from 表 where ... group by ... having ... order by ... limit ...
2、where子句
案例1：查询姓名为"波仔"的学员信息（精准查询）
select * from it_student where name='波仔';
或
select * from it_student where name="波仔";
案例2：查询id>5的所有产品信息
select * from it_goods where id > 5;
案例3：查询产品价格在3000到5000的所有产品信息
select * from it_goods where price > 3000 and price < 5000;
或
select * from it_goods where price between 3000 and 5000;
案例4：查询学科subject不为运维的其他学员信息
select * from it_student where subject != 'yunwei';
或
select * from it_student where subject <> 'yunwei';
案例5：查询学科为ui与yunwei的所有学员信息
select * from it_student where subject = 'ui' or subject = 'yunwei';
或
select * from it_student where subject in ('ui','yunwei');
案例6：查询id不等于1,3,5的学员信息
select * from it_student where id not in (1,3,5);
案例7：查询学生表中姓"王"的所有学员信息
select * from it_student where name like '王%';
案例8：查询学生表中姓"王"且名字为二个字的学员信息，如王一
select * from it_student where name like '王_';
案例9：查询产品表中包含Apple的所有产品信息
select * from it_goods where title like '%Apple%';
案例10：查询标题中包含数字的产品信息（建议少用，效率较低）
select * from it_student where title regexp '[0-9]';
案例11：空值NULL，查询subject信息为NULL的所有学员信息
空值一般表示数据未知、不确定或以后再添加。空值不同于0，也不同于空字符串。当需要查询某字段内容是否为空值时，可以使用关键字IS NULL来实现，不为空则一使用IS NOT NULL来实现。
select * from it_student where subject is null;
扩展：查询学生表中的所有学科信息（去重）
select distinct subject from it_student;
3、group by子句
GROUP BY我们可以先从字面上来理解，GROUP表示分组，BY后面写字段名，就表示根据哪个字段进行分组，GROUP BY必须得配合聚合函数来用，分组之后你可以计数（COUNT），求和（SUM），求平均数（AVG）等。
案例：求it_student学生表中，男女的人数比例信息
第一步：select sex from it_student group by sex;
+------+
| sex |
+------+
| 男 |
| 女 |
+------+
第二步：结合统计（聚合）函数配合使用
select sex,count(*) from it_student group by sex;
原理图：
由上图可知，使用GROUP BY对sex进行分组时，系统会自动对数据记录进行遍历（一个一个查询），如果sex='男'，则系统自动放入sex='男'的分组，COUNT(id) + 1，反之，sex='女'的分组进行+1操作，当所有记录遍历结束后，系统自动呈现分组后的结果。
4、having子句
① having与where类似，根据条件对==数据==进行过滤筛选。（能使用where查询的都可以使用having代替）
② where==针对表中的列==发挥作用，查询数据
③ having==针对查询结果集==发挥作用，筛选数据
select */字段 from 数据表 where子句 group by子句 having子句
案例：求学科中，学科人数大于2以上的学科信息
select subject,count(*) from it_student group by subject having count(*) > 2;
5、order by子句
由小到大，正序排列，1 2 3 4 5 ... => order by 字段 asc
由大到小，倒序排列，5 4 3 2 1 ... => order by 字段 desc
案例：列出it_student表中所有学员的信息，要求，按薪资进行排序(由大到小)
select * from it_student order by salary desc;
如果不指定order by 排序条件，则计算机默认根据主键由小到大进行正序排列
6、limit子句
案例1：查询学生表中，年龄最大的3名学员信息
select * from it_student order by age desc limit 3;
案例2：数据分页核心技术分析
一共7条记录，每页显示2条记录：
第一页，显示id=1和id=2 select * from lamp limit 0,2;
第二页，显示id=3和id=4 select * from lamp limit 2,2;
第三页，显示id=5和id=6 select * from lamp limit 4,2;
第四页，显示id=7和id=8 select * from lamp limit 6,2;
7、常用函数
- IFNULL函数扩展
IFNULL()函数：
MySQL中的IFNULL函数类似于Oracle中的NVL函数，其表达式为：IFNULL(expr1,expr2)，如果第一个参数不为空，则返回第一个参数，否则返回第二个参数。
ifnull(comm,0)
IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.
IF()函数：
IF(expr1,expr2,expr3)类似于Oracle中的NVL2函数，如果第一个表达式的值为TRUE（不为0或null），则返回第二个参数的值，否则返回第三个参数的值
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF() returns expr2. Otherwise, it returns expr3.
NULLIF()函数：
NULLIF(expr1,expr2),如果expr1=expr2为真，返回null；否则返回expr1
NULLIF(expr1,expr2)
Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
- 合并列函数(concat)
select concat(user,'-',host) as '用户名-主机信息' from mysql.user;
create table it_home(path varchar(255),homedir varchar(255));
insert into it_home set path='/home',homedir='itcast';
select concat(path,'/', homedir) from it_home;
8、四则运算
select 1+2;
select 1-3;
select 1*4;
select 1/4;
select 1 + 1 from dual; dual表,俗称万能表
二、MySQL高级查询
1、基本概念
使用UNION关键字可以把来自多个SELECT语句的结果组合到一个结果集中，这种查询就称之为并（UNION）运算或联合查询。合并时，多个SELECT子句中对应的字段数和数据类型必须相同。
基本语法：
SELECT...FROM...WHERE
UNION [ALL]
SELECT...FROM...WHERE
[...UNION [ALL]
SELECT...FROM...WHERE]
注：其中，不适用关键字ALL，执行的时候去掉重复的记录，所有返回的行都是唯一的；使用关键字ALL的作用是不去掉重复的记录，也不对结果进行自动排序。
2、UNION案例
在实际项目开发中，当一个表的数据很大，比如200G，这时我们需要进行分表处理（如水平分表），核心思想：把一个大表，分割N个小表，小表和大表结构一样，只是把数据分散到不同的表中。
案例：把数据表1与表2进行联合查询
3、交叉连接查询（了解）
创建数据库db_shop，在数据库中创建分类表与产品表：
创建分类表tb_category：
创建产品表tb_goods：
基本语法：
SELECT * FROM 表1 CROSS JOIN 表2;
或
SELECT * FROM 表1, 表2;
交叉连接返回的查询结果集的记录行数等于其所连接的两张表记录行数的乘积。例如tb_category表中有5条记录，tb_goods表中有4条记录，这两个表交叉连接后结果集的记录行数将是5 x 4 = 20条。
由此可见，倘若所关联的两张表的记录行数很多时，交叉连接的查询结果集会非常大，且查询执行时间非常长，甚至有可能因为返回数据过多而造成系统的停滞不前。因此，对于存在大量数据的表，应该避免使用交叉连接。同时，也可以在FROM子句的交叉连接后面，使用WHERE子句设置过滤条件，减少返回的结果集。
4、内连接查询
内连接（INNER JOIN）通过在查询中设置连接条件来移除交叉连接查询结果集中某些数据行。具体而言，内连接就是使用比较运算符进行表间某些字段值的比较操作，并将与连接条件相匹配的数据行组成新的记录，其目的是为了消除交叉连接中某些没有意义的数据行。也就是说，在内连接查询中，只有满足条件的记录才能出现在结果集中。
内连接所对应的SQL语句由两种表示形式：
① 使用INNER JOIN的显式语法结构为：
SELECT 目标列表达式1, 目标列表达式2, 目标列表达式n
FROM table1 [INNER] JOIN table2
ON 连接条件
[WHERE 过滤条件];
② 使用WHERE子句定义联结条件的隐式语法结构为：
SELECT 目标列表达式1, 目标列表达式2, ..., 目标列表达式n
FROM table1, table2
WHERE 连接条件 [AND 过滤条件];
上述两种表示形式的差别在于：使用INNER JOIN连接后，FROM子句中的ON子句可用来设置连接表的连接条件，而其他过滤条件则可以在SELECT语句中的WHERE子句中指定；而使用WHERE子句定义连接条件的形式，表与表之间的连接条件和查询时的过滤条件均在WHERE子句中指定。
案例1：
5、表别名
当表的名称很长或需要多次使用相同的表时，可以为表指定别名，用别名代表原来的表名。
基本格式：
6、外连接查询
☆ 左外连接
左外连接，也称左连接（LEFT OUTER JOIN或LEFT JOIN），用于返回该关键字左边表（基表）的所有记录，并用这些记录与该关键字右边表（参考表）中的记录进行匹配，如果左表的某些记录在右表中没有匹配的记录，就和右表中的“万能行”连接，即右表对应的字段值均被设置为空值NULL。
使用内连接查询，查询所有产品的分类信息：
由上图可知，如果使用内连接进行连接查询，系统只会返回catid与tb_category分类表中catid所匹配的产品信息，但是我们刚才插入的"神舟战神"笔记本并没有显示出来，因为其catid为NULL。这个时候如果我们需要显示所有产品信息的分类信息，就需要使用左外连接了。
使用外连接查询，查询所有产品的分类信息：
☆ 右外连接
右外连接，也称右连接（RIGHT OUTER JOIN或RIGHT JOIN），以右表为基表，其连接方法和左外连接完全一样，即返回右表的所有记录，并用这些记录与左边表（参考表）中的记录进行匹配，如果右表的某些记录在坐标中没有匹配的记录，左表对应的字段值均被设置为空值NULL。
7、子连接查询
子查询也称嵌套查询，是将一个查询语句嵌套在另一个查询语句的WHERE子句或HAVING短语中，前者被称为内层查询或子查询，后者被称为外层查询或父查询。在整个SELECT语句中，先计算子查询，然后将子查询的结果作为父查询的过滤条件。嵌套查询可以用多个简单查询构成一个复杂的查询，从而增强SQL的查询能力。
案例1：查询具有分类的产品信息
① 第一步：获取所有产品分类编号catid
② 第二步：查询所有产品信息（要求产品的catid出现在第一步的catid列表中）
案例2：查询学生表中薪资最高的学员信息
第一步：求出每个学科薪资最高是多少
select subject,max(salary) from it_student group by subject;
第二步：查询班级中学员的信息
select * from it_student where (subject,salary) in (?);
第三步：把第二步中的问号，替换成第一步中的SQL语句（完成）
select * from it_student where (subject,salary) in (select subject,max(salary) from it_student group by subject);
案例3：查询每个学科中薪资最高的学员信息
第一步：求出每个学科薪资最高是多少
select subject,max(salary) from it_student group by subject;
第二步：查询学生表中的学员信息
select * from it_student where (subject,salary) in (?);
第三步：把第二步中的问号，替换成第一步中的SQL语句（完成）
select * from it_student where (subject,salary) in (select subject,max(salary) from it_student group by subject);
三、事务处理
1、什么是事务处理
事务(transaction)：一系列将要发生或正在发生的连续操作。
事务安全，是一种保护连续操作同时实现（完成）的机制。事务安全的意义就是，保证数据操作的一致性与完整性。
2、举个栗子：金钱交易
A最近手头紧，没钱，找B借钱。 A：0.00元 银行转账 B：1000.20元
要完成一系列操作：转账（关联很多SQL语句） B银行卡扣钱-1000 update 数据表 set money=money-1000 where name='B';
A：0.00元 银行机器突然故障、断电了，问题：导致数据不一致，数据不完整。(事务安全) B：0.20元
A银行卡增钱+1000 update 数据表 set money=money+1000 where name='A'; 当以上两个SQL语句同时完成，则认为交易成功，交易成功才能写入数据库。
3、事务处理的前提
MySQL引擎：MyISAM引擎（查）与InnoDB引擎（安全） 记住：事务处理只能发生在InnoDB引擎上，从MySQL5.5以后版本开始，系统默认的引擎都是InnoDB引擎。
实验：事务处理必须使用InnoDB引擎
create table it_bank(
id int not null auto_increment primary key,
name varchar(40),
money decimal(11,2)
) engine=innodb default charset=utf8;
插入测试数据
insert into it_bank values (null,'A',1000.20);
insert into it_bank values (null,'B',0.00);
4、事务处理步骤
第一步：开启事务
start transaction;
第二步：执行SQL语句
update it_bank set money=money-1000 where id=1;
update it_bank set money=money+1000 where id=2;
第三步：如果成功，则提交。反之，则回滚
commit; #提交
或
rollback; #回滚
5、事务的ACID
A:原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
C:一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
I:隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
D:持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。
四、用户管理
1、创建用户(create user)
注意：MySQL中不能单纯通过用户名来说明用户，必须要加上主机。如jack@10.1.1.1
① 语法
创建用户设置密码
create user 'user'@'host' identified by 'password';
说明：用户的信息保存在mysql数据库中的user表中，验证用户是否创建成功如下：
select user,host from mysql.user;
② 示例
create user 'tom'@'localhost' identified by '123';
create user 'harry'@'localhost' identified by '123';
create user 'tony'@'10.1.1.1' identified by '123';
create user 'jack'@'%' identified by '123';
③ 用户主机表示方式
'user'@'localhost' 表示user只能在本地通过socket登录数据库
'user'@'192.168.0.1' 表示user用户只能在192.168.0.1登录数据库
'user'@'192.168.0.0/24' 表示user用户可以在该网络任意的主机登录数据库
'user'@'%' 表示user用户可以在所有的机器上登录数据库
2、用户权限管理
① 权限说明
USAGE 无权限,只有登录数据库,只可以使用test或test_*数据库
ALL 所有权限
以下权限为指定权限
select/update/delete/super/slave/reload...
with grant option 选项表示允许把自己的权限授予其它用户或者从其他用户收回自己的权限
② 权限保存位置
mysql.user 所有mysql用户的账号和密码，以及用户对全库全表权限（*.*）
mysql.db 非mysql库的授权都保存在此(db.*)
mysql.table_priv 某库某表的授权(db.table)
mysql.columns_priv 某库某表某列的授权(db.table.col1)
mysql.procs_priv 某库存储过程的授权
③ 给用户授权
1) 语法
grant 权限1,权限 on 库.表 to 用户@主机
grant 权限(列1,列2,...) on 库.表 to 用户@主机
2) 用户授权示例
给tom@10.1.1.1用户授予查看db01库里所有表权限
mysql> grant select on db01.* to 'tom'@'10.1.1.1';
刷新权限表
mysql> flush privileges;
给tom@10.1.1.1用户授予修改db01库的ID字段的权限
mysql> grant update(ID) on db01.tt1 to 'tom'@'10.1.1.1';
mysql> flush privileges;
查看当前用户权限
mysql> show grants;
查看指定用户权限
mysql> show grants for 'tom'@'10.1.1.1';
3) 使用grant创建用户
grant all on *.* to 'harry'@'10.1.1.%' identified by '123';
4) with grant option选项
with grant option:用户是否可以下放和回收权限
grant all on *.* to 'amy'@'10.1.1.%' identified by '123' with grant option;
grant all on *.* to 'harry'@'10.1.1.%' identified by '123';
测试harry用户和amy用户是否可以将自己的权限下放：
harry用户登录：
mysql> grant select on db01.* to 'tom'@'10.1.1.1';
ERROR 1044 (42000): Access denied for user 'harry'@'10.1.1.%' to database 'db01'
amy用户登录：
mysql> grant select on db01.* to 'tom'@'10.1.1.1';
5) 总结
- 创建用户方法
-
create user...==需要单独grant授权== -
grant xxx==直接创建用户并授权== - 扩展补充
- 从MySQL 5.7.6开始，不赞成使用grant修改密码;使用 ALTER USER来代替。
ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
④ 回收用户权限
revoke 权限 on 库.表 from 用户;
撤消指定的权限
mysql> revoke update on db01.tt1 from 'tom'@'10.1.1.1';
撤消所有的权限
mysql> revoke select on db01.* from 'tom'@'10.1.1.1';
3、删除用户(==drop user==)
① 语法
drop user 用户;
② 示例
删除'user01'@'localhost'用户
mysql> drop user 'user01'@'localhost';
默认删除user01从任意主机登录
mysql> drop user 'user01';
mysql> drop user 'user01'@'%';
重命名用户名
mysql> rename user 'harry'@'10.1.1.%' to 'harry'@'10.1.1.1';
删除一个匿名用户
mysql> drop user ''@'localhost';
删除mysql中的匿名用户
mysql> delete from mysql.user where user='';
删除root用户从本机::1登录（::1表示IPv6地址）
mysql> delete from mysql.user where user='root' and host='::1';
mysql> flush privileges;
注意：如果tcp/ip登录，服务器端口不是默认3306，则需要加端口号
