python开发基础篇:十:数据库

 1：数据库基本常识　　数据库参考文档：https://www.cnblogs.com/wangfengming/p/8143554.html

1：什么是数据库
    存储数据的仓库

2：什么是数据
　　大家所知道的都是数据

3：数据库的概念
　　数据库服务器
　　数据库管理系统
　　库
　　表
　　记录
　　数据

文件存储数据：
    文件存储信息资源，文件放在文件夹里，文件夹存在什么盘上，盘上还需要有个操作系统管理文件和盘，操作系统存在于电脑机器上面---这就是一套完整存储数据需要的所有东西

数据库存储数据也类似：
    数据库服务器(机器)——>数据库管理系统(软件，mysql这些)——>存储数据的盘——>库(对应文件夹)——>表——>记录——数据
    一个文件夹下有多张表，数据按照规则存储的，一条条写在数据库里的，一条数据就称为记录
    数据库操作的核心就是库，表和记录

2：数据库Mysql管理软件　　https://www.mysql.com/ :mysql官网

现在简单在windows上安装mysql：
bin文件里的：
    mysql.exe    服务端的执行文件
    mysqld.exe    mysql服务端执行的文件
docs：文档目录

inclue：头部信息目录

lib：包的目录

share：错误信息的目录

1：解压安装包得到上面几个目录
2：windows上mysql服务初始化：
　　1：cmd　　cd进入bin文件目录
　　2：输入　　mysqld --initialize-insecure
　　　　进行初始化操作,初始化当前mysql的服务，当前操作会在mysql-5.7.20-winx64目录下创建一个data文件夹
　　　　装的软件就是数据库的操作系统，在这个里面还包含文件夹文件，数据，记录，
　　　　data文件夹里面的mysql文件夹就是mysql库，库里面存储的都是文件，文件存储数据库里面的数据
　　3：连接服务器
　　　　E:\softwareDevelop\mysql-5.7.20-winx64\bin\mysqld　
　　　　把bin文件夹配置环境变量直接输入　mysqld　
　　　　这时候软件就启动起来了
　　4:再开个cmd输入：
　　　　mysql -u root -p　　就能连接上mysql
　　　　这边客户端就连接上了服务端，客户端可以像服务端发送命令

3306是mysql固定端口

mysql：是一个用户多个数据库，一个用户登录的时候可以创建多个不同的数据库
Oracle：一个用户，一个库，只能创建表

3：安装可视化的客户端navicate来操作数据库　　https://www.cnblogs.com/wangfengming/articles/7880595.html　

4：windows上完全卸载mysql　https://www.cnblogs.com/wangfengming/articles/7879074.html　

5：Mysql库操作　　https://www.cnblogs.com/wangfengming/articles/7875313.html

库操作：
1.创建数据库：在data文件夹里创建一个db_name的文件键
　　CREATE DATABASE db_name charset utf8;
2：删除数据库
　　DROP DATABASE db_name;
3：使用进入数据库，选择数据库
　　USE db_name;　　使用navicate客户端不需要进入，使用命令行的客户端一般才使用这个命令
4：查看库
　　show databases;　　查询当前用户下所有数据库
　　show create database db_name;　　查看创建数据库的信息
　　select database();　　查询当前操作所在的数据库名称

6：Mysql之表操作　　https://www.cnblogs.com/wangfengming/articles/7880853.html

char和vachar：
　　vachar字符串类型：可变长度，给100长度，但是只填写2个值，就占用2个长度
　　char字符串类型：不可变长度，给我多大创建表的时候都占用这么大空间，给100长度，就算不填写值占的空间就是100长度
主键：
　　设置一个标题是主键之后，那么在这个表里这个标题就不能重复了

表在库的里面

创建表：
CREATE TABLE 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
)ENGINE=innodb DEFAULT CHARSET utf8;

create table student(
    id int not null auto_increment primary key,
    name varchar(250) not null,
    age int not null,
    sex enum('男','女') not null default '男',
    salary double(10,2) not null
)engine=innodb default charset=utf8;

ps: 
　　not null :表示此列不能为空
　　auto_increment :表示自增长,默认每次增长+1,差入数据的时候设置了自增的字段(标题)就不需要填值了会自动增长+1自动插入值
　　　　　　　　　　　　主键自增的比较多，不需要管他，还不可能重复
　　　　　　　　　　　　注意:自增长只能添加在主键或者唯一索引字段上
  
　　primary key :表示主键(唯一不重复且不为空)，自增长必须和主键或者索引一块使用，普通字段不能加自增长的
　　engine =innodb :表示指定当前表的存储引擎
　　default charset utf8 :设置表的默认编码集

查询表数据：
SELECT * FROM info；　　查看表数据
DESC info;　　　　查看表结构
show create table info;　　查看创建表信息　　

删除表：
drop table 表名;
清空表：
truncate table 表名;　

修改表结构
1：添加表字段
alter table 表名 add 字段名 类型 约束;
ALTER TABLE info add age int not null;    表李添加age字段
alter table student add age int not null default 0 after name;   after name 表示在name字段后添加字段 age.

2： 修改表字段
alter table student modify 字段 varchar(100) null;
alter table student change 旧字段 新字段 int not null default 0;
change 可以改变字段名字和属性
modify只能改变字段的属性
ALTER TABLE info MODIFY name CHAR(100) null;    修改表字段属性
ALTER TABLE info CHANGE name name1 varchar(33) not null;    修改表字段名称和属性

3：删除表字段 
alter table student drop 字段名;
ALTER TABLE info DROP age;

4：更新表名称:
rename table 旧表名 to 新表名;
rename TABLE info_new to info;

更新主键操作
1：添加主键 : 
alter table student add primary key(字段,"多个","间隔");
ALTER TABLE info_new add PRIMARY KEY(ID);
ALTER TABLE info_new add PRIMARY KEY(ID, name1);　　设置联合主键，但是不能表李已经设置好了主键才能生效
　　主键可以写多个字段，联合主键，多个字段加在一起不能有重复的，
　　比如姓名是主键：那么只能有一个人叫张三
　　如果姓名和年龄加一起是联合主键：那么能多个人叫张三，但是不能有两个张三且年龄相同

2：移除主键 : 
alter table student drop primary key;

ps:如果主键为自增长,以上方式则不被允许执行,请先去掉主键自增长属性,然后再移除主键
alter table student modify id int not null,drop primary key 

默认值更新操作
1：修改设置默认值 : 
alter table 表 alter 字段 set default 100;
alter table  info_new alter sex set default "男";

2：删除默认值 :
alter table 表 alter 字段 drop default;
alter table info_new ALTER sex DROP DEFAULT;

复制表：
1：只复制表结构和表中数据
CREATE TABLE tb2 SELECT * FROM tb1;
CREATE TABLE info3 SELECT * FROM info WHERE id=2;　　选择性复制
ps:主键自增/索引/触发器/外键 不会 被复制
   
2：只复制表结构
create table tb2 like tb1;
ps: 数据/触发器/外键 不会被复制　

enum：枚举数据类型
    sex ENUM('男', '女', '未知')：性别要么是男，女，未知，，不是这3个之一就报错，
　　这就是枚举数据类型，定义有限的数据的时候用枚举类型，一般不超过10个使用枚举，比如红绿灯

7：数据库存储引擎　　

　　参考文档：　　https://www.cnblogs.com/wangfengming/p/7930333.html

　　MySQL数据库的体系架构如下图所示：

从上图中可以看出，MySQL主要分为以下几个组件：
    连接池组件：connection pool，和线程池进程池一样，自身设置多少个连接，默认应该150左右个连接，同时最多150个客户端能连接进数据库
　　　　可以在mysql配置文件my.ini修改连接数，
　　　　客户端，cmd和navicate这些都是连接，算客户端
    管理服务和工具组件：management services utiltles　　mysql服务由这块管理的
    SQL接口组件：sql interface　　写的命令怎么识别出来的，由sql接口组件来识别这些语法是否正确
    分析器组件：parser　　分析好之后把sql语句交给优化器组件，优化器组件来调整sql
    优化器组件：optimizer
　　　　　　写的sql不一定写的特别规范或者完美，传给sql服务之后，实际上不少按照外部写的sq执行的，sql服务会自动对sql进行调整，
　　　　　　调整到mysql认为这样写比外部写的更好，调整完之后执行，执行完的结果会先放到缓冲里
　　缓冲组件：cache buffers　　这里才真正拿到结果
　　插件式存储引擎：mysql多个存储引擎
　　物理文件：data目录下的数据文件，永久存储的文件

存储引擎：
　　其实就是指定 表 如何存储数据(按照什么结构存储数据)，如何为存储的数据 建立索引 以及 如何更新，查询数据等技术实现的方法--这些都是存储引擎实现的
　　因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）
　　不同存储引擎存储数据的方式不一样的，索引建立也不一样，查询计数的实现方法也不同

在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。
而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，
　　用户也可以根据自己的需要编写自己的存储引擎

MySQL存储引擎比较：
特性    　　MyISAM    InnoDB    Memory    Archive　　NDB
存储限制     没有      64TB      有     　　没有   　　没有
事务           　　　　支持               　　　　　　 支持
锁粒度　　　　表      　行      　　表     　　行       页
B树索引      支持      支持      支持                 支持
哈希索引              支持       支持          
全文索引     支持                    
集群索引           　　支持               
数据缓存              支持       支持          
索引缓存     支持      支持       支持          
数据压缩     支持               　　　　　　 支持     
批量插入     高       相对低      高       非常高      高
内存消耗     低       高         中        低         低
外键支持          　　支持               
复制支持     支持     支持       支持      支持        支持
查询缓存     支持     支持       支持      支持        支持
备份恢复     支持     支持       支持      支持        支持
集群支持                         　　　　　　　　      支持

前两种重点：
MyISAM：mysql5.5版本以前默认的存储引擎，mysql5.5版本默认的存储引擎是innodb了

存储限制：能存多少数据，数据存储的越多，性能和效率越低，一般达不到上线
myisam：不支持事务
锁粒度：支持的锁是什么级别，MyISAM只支持表表级别的锁，锁表，而innodb支持行数据锁，效率肯定比锁表更高
B数索引：
数据缓存：一个sql执行花了3s，同一个sql再来执行一次就是0.003s，利用数据的缓存，第一次从数据文件里读，残生I/O，所以效率低
　　　　第二次已经把结果加载到了缓存里，所以没有I/O，读取效率高
索引缓存：
数据压缩：
批量插入：大数据量插入，建议先选用myisam存储引擎，myisam插入数据比innodb高，由于索引文件存储原因来决定的
内存消耗：innodb高，因为需要维护事务，外键这些东西，内存消耗高
外键支持：只有innodb存在外键的概念

其中最常见的两种存储引擎是MyISAM 和 InnoDB

 MyISAM存储引擎：
　　1、MyISAM 是MySQL (mysql 5.5版本以前) 原来的默认存储引擎.
　　2、MyISAM   这种存储引擎不支持事务，不支持行级锁，只支持并发插入的表锁。还不支持外键
　　3、MyISAM   类型的表支持三种不同的存储结构：静态型、动态型、压缩型。
　　　　（1）静态型：就是定义的表列的大小是固定（即不含有：xblob、xtext、varchar等长度可变的数据类型），这样mysql就会自动使用静态myisam格式。
　　　　　　使用静态格式的表的性能比较高，因为在维护和访问的时候以预定格式存储数据时需要的开销很低。
　　　　　　但是这高性能是用空间换来的，因为在定义的时候是固定的，所以不管列中的值有多大，都会以最大值为准，占据了整个空间。
　　　　（2）动态型：如果列（即使只有一列）定义为动态的（xblob, xtext, varchar等数据类型），
　　　　　　这时myisam就自动使用动态型，虽然动态型的表占用了比静态型表较少的空间，但带来了性能的降低.
　　　　（3）压缩型：如果在这个数据库中创建的是在整个生命周期内只读的表，则这种情况就是用myisam的压缩型表来减少空间的占用。
　　　　　　　　　　提供一个工具来对表压缩，压缩之后这个表的空间会减少40%到60%，压缩后的表只能做查询操作，而不能做增删改了
　　　　　　压缩方式参考官方文档: https://dev.mysql.com/doc/refman/5.6/en/myisampack.html
　　选择那种存储结构是myisam自动提供的，我们控制不了
　　4、MyISAM也是使用B+tree索引但是和Innodb的在具体实现上有些不同。

InnoDB存储引擎：
　　（1）MySQL默认存储引擎(MySQL 5.5 版本后).
　　（2）innodb 支持事务，回滚以及系统崩溃修复能力和多版本迸发控制的事务的安全。
　　（3）innodb 支持自增长列（auto_increment）,自增长列的值不能为空，(一个表只允许存在一个自增,并且要求自增列必须为索引)
　　　　　主键为什么能加自增长，因为主键是主键索引，普通的字段不能加自增长，其他的索引也能加自增长，但是一张表中只能一个自增长
　　（4）innodb 支持外键（foreign key） ,外键所在的表称为子表,而所依赖的表称为父表。
　　（5）innodb存储引擎支持行级锁。
　　（6）innodb存储引擎索引使用的是B+Tree

补充3点：
    1.大容量的数据集时趋向于选择Innodb。因为它支持事务处理和故障的恢复。Innodb可以利用数据日志来进行数据的恢复。
　　　　主键的查询在Innodb也是比较快的：和存储结构有关，
    2.大批量的插入语句时（这里是INSERT语句）在MyIASM引擎中执行的比较的快，但是UPDATE语句在Innodb下执行的会比较的快，尤其是在并发量大的时候。
    3.两种引擎所使用的索引数据结构是什么？
        答案:都是B+树!b tree
　　　　MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，
　　　　　　两个文件来存储，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。
　　　　Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，索引和数据放在一个文件里
　　　　　　这种索引有被称为聚集索引。
　　所以使用索引肯定是innodb在一个文件里的更快，MYIASM是分开的，而innodb存在了一起
　　存在一起称之为聚集索引

Memory  存储引擎
　　Memory存储引擎（之前称为Heap）将表中数据存放在内存中，读取处理很快，电脑gg重启内存东西就不存在了，只是临时存储的引擎
　　　　如果数据库重启或崩溃，数据丢失，因此它非常适合存储临时数据。

Archive存储引擎
　　正如其名称所示，Archive非常适合存储归档数据，
　　　　如日志信息。它只支持INSERT和SELECT操作，没有delete和update的操作，其设计的主要目的是提供高速的插入和压缩功能。

NDB存储引擎
　　NDB存储引擎是一个集群存储引擎，类似于Oracle的RAC，但它是Share Nothing(非共享)的架构，只能集群，不能共享
　　　　因此能提供更高级别的高可用性和可扩展性。NDB的特点是数据全部放在内存中，因此通过主键查找非常快。但是数据量过大内存会溢出
　　关于NDB，有一个问题需要注意，它的连接(join)操作是在MySQL数据库层完成，不是在存储引擎层完成，
　　　　这意味着，复杂的join操作需要巨大的网络开销，查询速度会很慢。

测试存储引擎：
创建三个表，分别使用innodb，myisam，memory 存储引擎，进行插入数据测试
create table t1(id int)engine=innodb;
create table t2(id int)engine=myisam;
create table t3(id int)engine=memory;
 
看一下三个存储引擎创建的 表文件
 t1.frm(innodb类型表的表结构)  t1.ibd(数据文件) 
 t2.MYD(数据文件)  t2.MYI(单独的索引文件)  t2.frm(表结构) 
 t3.frm(存储在内存当中就不需要数据文件了)

细心的同学会发现最后的存储引擎只有表结构，无数据
memory，在重启mysql或者重启机器后，表内数据清空

重点[面试题]:
innodb与MyIASM存储引擎的区别:
　　1.innodb 是mysql5.5版本以后的默认存储引擎, 而MyISAM是5.5版本以前的默认存储引擎.
　　2.innodb 支持事物,而MyISAM不支持事物
　　3.innodb 支持行级锁.而MyIASM 它支持的是并发的表级锁.并且这个并发的表锁只是在大批量插入的时候才支持
　　4.innodb 支持外键, 而MyIASM 不支持外键
　　5.innodb与MyIASM存储引擎都采用B+TREE存储数据, 但是innodb的索引与数据存储在一个文件中,这种方式我们称之为聚合索引.
　　　　而MyIASM则会单独创建一个索引文件,也就是说,数据与索引是分离开的
　　6.在效率方面MyISAM比innodb高(数据读取方面，innodb支持事务，要把每一个操作都记下来，有可能回滚，记录的时候对效率的影响就很大，myisam不需要记),
　　  但是在性能方面innodb要好一点.

8：Mysql数据操作　　https://www.cnblogs.com/wangfengming/articles/8150903.html

1：插入数据insert　　增
INSERT into info (name1, sex) VALUES ("二逼子", "女");
INSERT into info VALUES (9, "二逼子1", "女");
INSERT into info (name1, sex) VALUES 
("二逼子1", "女"),
("二逼子2", "女"),
("二逼子3", "女"),
("二逼子4", "女"),
("二逼子5", "女");
INSERT INTO info(name1, sex) SELECT name1, sex FROM info WHERE ID=1;
INSERT INTO info(name1, sex) SELECT name1, sex FROM info;

2：删除数据delete    删
DELETE FROM info WHERE ID=1;
DELETE FROM info;　　删除info表中所有数据
DELETE FROM info WHERE ID IN(2, 10);
truncate info;　　清空整张表

delete和truncate都是删除表里的数据，表结构不变
区别：
　　1：效率，删除快慢问题，delete是一条条删除，truncate就是直接删除整张表，直接清空，truncate执行速度快
　　2：表有自增和主键，delete语句清空12条数据，再新增的时候主键从13开始，truncate清空后自增主键的信息也删除，整个表变成一张空白表，下次自增主键从1开始
　　3、TRUNCATE是一个DDL语言而DELETE是DML语句，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。
　　4、TRUNCATE不能触发触发器，DELETE会触发触发器

3：更新数据UPDATE    改
UPDATE info SET sex = "人妖" WHERE id = 12;
UPDATE info SET sex = "人妖";　　不加where条件整个表里的记录全改　　
UPDATE info SET sex = "男", name1="宝强";　　修改多个字段

4：查询数据     SELECT
1：单表查询：http://www.cnblogs.com/wangfengming/articles/8064956.html
2：多表查询：http://www.cnblogs.com/wangfengming/articles/8067220.html

去重distinct：没啥用
select distinct age from person;　　　　# 对age去重
select distinct age,name from person;　　　# 这样运行是对distinct后面所有的字段加一起去重，age+name组合重复才去重，单一字段distinct可以使用，多字段不得行　

<>：不等于　　<>等同 !=
select * from person where age <> 23;

null 关键字: is null , not null
select * from person where dept_id is null;　　　　查询部门id为null空的人
select * from person where dept_id is not null;　　查询部门id不为空的人

字段设置为空字符串和null字段为空的是不同的两种形式，查询如下
SELECT * FROM person WHERE name IS null;　　查询为null的数据
SELECT * FROM person WHERE name="";　　　　查询空字符串的数据

逻辑运算符: 与 and 或 or　　连接多个条件使用
select * from person where age = 23 and salary =29000;　　　　于，且
select * from person where age = 23 or salary =29000;　　　　或
select * from person where not(age = 23 or salary =29000);　　　　非

区间查询：关键字 between 10 and  20 :表示 获得10 到 20 区间的内容
select * from person where salary between 4000 and 8000;　　　　前后包含，后面大于前面的数
select * from person where salary >= 4000 and salary <= 8000;

集合查询：关键字: in, not null
select * from person where age in(23,32,18);　　　　等同　　select * from person where  age =23 or age = 32 or age =18;
SELECT * FROM person WHERE id not in (1, 3, 5);

模糊查询：关键字 like , not like
　　%：任意多个字符
　　_：只能是单个字符
SELECT * FROM person WHERE name LIKE "e%";　　　　查询以e开头的
SELECT * FROM person WHERE name LIKE "%e%";　　包含指定参数，查找包含e的，不管中间还是结尾还是开头，含有e就匹配打牌
SELECT * FROM person WHERE name LIKE "%e";　　查找以e结尾的
SELECT * FROM person WHERE name LIKE "__e%";　　 __表示单个字符，占位符
select * from person where name like '____';　　查询 name 名称 是四个字符的人
select * from student where name not like 'a%'　　　　排除名字带 a的学生

排序查询:关键字: ORDER BY  字段1 DESC：降序,　　 字段2 ASC：升序，默认为升序
SELECT * FROM person ORDER BY salary;　　　　按照salary工资排序
SELECT * FROM person ORDER BY salary DESC;　　按照工资倒叙排序
SELECT * FROM person WHERE age>20 ORDER BY salary DESC;　　order by一般写在最后面
SELECT* FROM person ORDER BY name DESC;　　name是中文的不支持排序，需要强制让MySQL按中文来排序
　　编码集为UTF-8，UTF-8不支持中文排序，转化成gbk就支持中文排序了
select * from person order by CONVERT(name USING gbk) desc;　　中文排序需要转编码集

 9：Mysql中文乱码问题　　https://www.cnblogs.com/wangfengming/articles/7875313.html

10：数据库权限　　https://www.cnblogs.com/wangfengming/articles/7875313.html

用户管理：
创建用户
　　create user '用户名'@'IP地址' identified by '密码';
　　CREATE USER "ywt"@"127.0.0.1" IDENTIFIED BY "123456";
　　create user "test"@"%" IDENTIFIED by "123456";　　　　想让所有人通过用户连接数据库，需要使用%充当ip，不能使用固定的ip值，%表示所有的ip
　　　　　　　　　　　　其他机器可以使用test：123456  和我的本机ip连接我的数据库

删除用户
　　drop user '用户名'@'IP地址';
　　DROP user "ywt"@"127.0.0.1";

修改用户
　　rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';
　　rename user "ywt"@"127.0.0.1" to "ywt_new"@"127.0.0.1";

授权管理
show grants for '用户'@'IP地址'                  -- 查看权限
SHOW GRANTS for "ywt"@"127.0.0.1";　　GRANT USAGE ON *.* TO `ywt`@`127.0.0.1`

grant  权限 on 数据库.表 to   '用户'@'IP地址'      -- 授权
grant  select,update on bd_test.info to "ywt"@"127.0.0.1";　　　　固定给用户能够访问哪张表和哪些权限，select查看update修改
grant select,update,delete on bd_test.*  to "ywt"@"127.0.0.1";
GRANT all PRIVILEGES on *.* to "ywt"@"127.0.0.1";　　　　所有库所有表的仅次于root用户的权限给ywt用户，
　　　　　　　　　　　　　　　　all privileges  除grant外的所有权限：不能把权限复制给别人，只能使用这些权限

revoke 权限 on 数据库.表 from '用户'@'IP地址'      -- 取消权限
REVOKE all PRIVILEGES on *.* FROM "ywt"@"127.0.0.1";　　　　移除所有库的所有权限

刷新用户权限
flush privileges;　　创建用户设置用户权限后只能在本机生效，其他机器上不生效的，需要flush刷新权限

 11：数据库修改密码　　https://www.cnblogs.com/wangfengming/articles/7875313.html#label5

1：CREATE user "huangtiandi"@"%" IDENTIFIED by "123456";    创建用户

修改密码方式一：cmd 输入mysqladmin 命令，　　　　这种方式仅限本机使用
　　mysqladmin -u用户名 -p原密码 password 新密码
　　mysqladmin -uhuangtiandi -p123456 password 111111

修改密码方式二：root用户连接进数据库后使用sql语句直接设置用户密码
　　set password for '用户名'@'IP' = password('新密码');
　　flush privileges;
　　set password for 'huangtiandi'@'%'  = password('666')

修改密码方式三：修改mysql库下的user表
　　5.7版本：　　5.7版本的password密码字段名改成了authentication_string字段，5.6以下的是password字段
　　update mysql.user set authentication_string=password('新密码') where user= '用户名'    　　      
　　flush privileges; -- 刷新权限
　　5.6和以前的旧版本：不建议这样使用，直接修改了mysql核心库下的user表，安全隐患大，这样改需要flush刷新才能生效，否则需要重启mysql服务才生效
　　update mysql.user set password = password('新密码') where user= '用户名'
　　flush privileges; -- 刷新权限
　　8.0版本：移除了password()加密函数

12：mysql数据库忘记密码　　　　https://www.cnblogs.com/wangfengming/articles/7875313.html#label6

mysql忘记密码：
　　mysql库下的表来控制，每次输入密码需要检测mysql库下的表来检查用户名和密码是否正确
　　现在就让mysql库下的的表全部失效那么就不用输入密码了，让控制权限不存在　　-----暴力破解就是让表失效

在忘记root密码的时候，可以这样:　　仅限本机破解
　　1.首先打开cmd 窗口,关闭mysql服务
　　　　net stop mysql
　　2.然后跳过权限检查,启动mysql,输入命令
　　　　mysqld --skip-grant-tables　　　　　　　　　　skip：跳过　　　　grant：权限　　tables：表
　　3.重新打开一个新的cmd窗口,启动客户端(已跳过权限检查,可以直接登录)
　　　　mysql
　　4.直接进来,修改密码
　　　　5.6版本修改密码：update mysql.user set password = password('新密码') where user= '用户名'
　　　　5.7版本修改密码：update mysql.user set authentication_string=password('新密码') where user= '用户名'  
　　5. 刷新权限
　　　　flush privileges;

13：sql语句之单表查询　　https://www.cnblogs.com/wangfengming/articles/8064956.html

聚合函数：对列进行操作,返回的结果是一个单一的值,除了 COUNT 以外，都会忽略空值
　　COUNT：统计指定列不为NULL的记录行数；
　　SUM：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
　　MAX：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
　　MIN：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
　　AVG：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

select 聚合函数(字段) from 表名;
SELECT AVG(salary) FROM person;
select max(age),min(age),avg(age) from person;
SELECT COUNT(*) FROM person WHERE age = 20;　　　　查询age=20的计数

分组查询：　　group_by
分组的含义: 将一些具有相同特征的数据 进行归类.比如:性别,部门,岗位等等
怎么区分什么时候需要分组呢?  
　　套路: 遇到 "每" 字,一般需要进行分组操作.比如：每个班多少人，每组总成绩
　　例如: 1. 公司每个部门有多少人.
　　　　  2. 公司中有 多少男员工 和 多少女员工.

select 被分组的字段 from 表名 group by 分组字段 [having 条件字段]
ps: 分组查询可以与 聚合函数 组合使用.
SELECT  dept_id, sum(salary) FROM person GROUP BY dept_id;　　　　　　按照dept_id部门分组查询所有部门的总工资
SELECT  dept_id, sum(salary) as w FROM person GROUP BY dept_id HAVING w>20000;　　查询部门分组每个部门工资大于20000的组
　　GROUP BY之后要求加条件不能使用where需要使用having，having作用和where一样，只不过必须跟在group_by后面
　　group_by之后不能加where只能加having，group_by和having组合使用
SELECT  dept_id, max(salary) FROM person GROUP BY dept_id;　　　　求每个部门最大的工资
SELECT GROUP_CONCAT(name) FROM person GROUP BY dept_id;　　　　查询按照部门分组后，每组有那些人
select avg(salary),dept_id,GROUP_CONCAT(name) from person GROUP BY dept_id;　　　　查询每个部门的平均薪资 并且看看这个部门的员工都有谁?
　　GROUP_CONCAT(expr):按照分组，将expr字符串按逗号分隔，组合起来 ----了解
　　分组函数group_by分组的时候会把一组的个重叠，一组的数据重叠压上，永远看到的是第一张牌
　　　　select avg(salary),dept_id,name from person GROUP BY dept_id;　　　这个语句name看到的只能是一叠数据的一张

题目：查询平均薪资大于10000的部门, 并且看看这个部门的员工都有谁?  
SELECT dept_id,AVG(salary) as sa, GROUP_CONCAT(name) FROM person GROUP BY dept_id HAVING sa > 10000;

 where 与 having区别:
执行优先级从高到低：where > group by > having
    1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
　　　　聚合函数的值要在分组之后得出来，还没运行到分组得时候就在where中使用分组数据一定会报错
    2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

　　SELECT dept_id FROM person GROUP BY dept_id HAVING id = 4;　　报错。因为having后只能使用分组字段dept_id，使用普通字段name就报错，having还可以使用聚合字段
　　SELECT dept_id FROM person WHERE age = 20 GROUP BY dept_id HAVING dept_id>2;　　　　这样使用就不会报错了

分页查询：limit　　参数1：起始位置　　参数2：每一页查多少条
select * from person limit 5;　　查询前5条数据
select * from person limit 0,4;　　查询1到4条
select * from person limit 10,5;　　查询第10条到第15条数据
ps: 
　　limit (起始条数),(查询多少条数);
　　limit 5　　前面从0开始，依次取5条  等同   limit 0 5

 14：正则表达式　　参数文档：http://www.cnblogs.com/wangfengming/articles/8067037.html

MySQL中使用 REGEXP 操作符来进行正则表达式匹配
    模式    　　　　 描述
    ^    　　　　　　匹配输入字符串的开始位置。 
    $    　　　　　　匹配输入字符串的结束位置。
    .    　　　　　　匹配任何字符（包括回车和新行）
    [...]    　　　 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
    [^...]    　　　负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
    p1|p2|p3   　　 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。


# ^  匹配 name 名称 以 "e" 开头的数据
select * from person where name REGEXP '^e';
 
# $  匹配 name 名称 以 "n" 结尾的数据
select * from person where name REGEXP 'n$';
 
# . 匹配 name 名称 第二位后包含"x"的人员 　　"."表示任意字符，类似占位符，通配符
select * from person where name REGEXP '.x';
 
# [abci] 匹配 name 名称中含有指定集合内容的人员
select * from person where name REGEXP '[abci]';
 
# [^alex] 匹配 不符合集合中条件的内容 , ^表示取反
select * from person where name REGEXP '[^alex]';
　　注意1：^只有在[]内才是取反的意思，在别的地方都是表示开始处匹配
　　注意2 : 简单理解 name  REGEXP '[^alex]' 等价于 name != 'alex'
 
# 'a|x' 匹配 条件中的任意值
select * from person where name REGEXP 'a|x';　　
 
#查询以w开头以i结尾的数据
select * from person where name regexp '^w.*i$';
#注意:^w 表示w开头, .*表示中间可以有任意多个字符, i$表示以 i结尾

正则表达式regexp和like模糊查询基本类似
　　SELECT * FROM person WHERE name="alex";
　　SELECT * FROM person WHERE name LIKE "alex";
　　SELECT * FROM person WHERE name REGEXP "alex";

15：SQL 语句关键字的执行顺序　　参考文档：https://www.cnblogs.com/wangfengming/articles/7880312.html

一条sql语句：
    查询:姓名不同人员的最高工资,并且要求大于5000元,同时按最大工资进行排序并取出前5条.
select name, max(salary)
from person  
where name is not null  
group by name  
having max(salary) > 5000
order by max(salary)
limit 0,5

在上面的示例中 SQL 语句单表查询的逻辑执行顺序如下:
    1：首先执行 FROM 子句, 先拿到表得内容，从 person 表 组装数据源的数据
    2：执行 WHERE 子句, 拿到表得内容后需要通过where条件过滤一下，筛选 person 表中 name 不为 NULL 的数据
    3：执行 GROUP BY 子句, 条件过滤完，group by按照字段分组一下，把 person 表按 "name" 列进行分组
    4：计算 max() 聚集函数, 按 "工资" 求出工资中最大的一些数值
    5：执行 HAVING 子句, 筛选工资大于 5000的人员.
　　 6：selec关键字查找数据，拿到结果
    7：执行 ORDER BY 子句, 对select拿到得数据进行重新排序，把最后的结果按 "Max 工资" 进行排序.
    8：最后执行 LIMIT 子句, . 进行分页查询2，分页拿到一个结果
执行顺序: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY ->limit　

#多表查询执行顺序
SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

1：from　　拿到表的数据，from会一次性把两个表的结果都拿到
2：join　　

16：sql简单练习

练习题一: http://www.cnblogs.com/wangfengming/articles/7944029.html
练习题二: http://www.cnblogs.com/wangfengming/articles/7889786.html
练习题四：https://www.cnblogs.com/wangfengming/articles/7891939.html

练习题四：https://www.cnblogs.com/wangfengming/articles/7891939.html
1：查询学习课程"python"比课程 "java" 成绩高的学生的学号
　　1：看python课程学生的成绩和java课程的成绩
　　　　SELECT num FROM score,course WHERE score.c_id=course.c_id and course.c_name="python";　　　　python表成绩
　　　　SELECT num FROM score,course WHERE score.c_id=course.c_id and course.c_name="java";　　　　　　java表成绩
　　2：python成绩表和java成绩表连接起来然后对比找结果
　　　　SELECT p1.s_id FROM 
　　　　(SELECT num as python_num,s_id FROM score,course WHERE score.c_id=course.c_id and course.c_name="python") as p1, 
　　　　(SELECT num as java_num,s_id FROM score,course WHERE score.c_id=course.c_id and course.c_name="java")as p2 
　　　　WHERE p1.s_id=p2.s_id and p1.python_num>p2.java_num;
　　3:如果查出来学生学号s_id还需要关联到学生姓名等信息
　　　　SELECT p1.s_id, student.s_name FROM 
　　　　(SELECT num as python_num,s_id FROM score,course WHERE score.c_id=course.c_id and course.c_name="python") as p1, 
　　　　(SELECT num as java_num,s_id FROM score,course WHERE score.c_id=course.c_id and course.c_name="java")as p2, 
　　　　student 
　　　　WHERE p1.s_id=p2.s_id and p1.s_id=student.s_id and p1.python_num>p2.java_num
　　思路：
　　　　多表查询，课程成绩python比java高的，python课程分数的临时表，java课程分数的临时表，两张临时表做对比，两张表连接查询
　　　　
2：查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数);　
　　1：成绩表，学生姓名表连接后再去做条件判断
　　　　SELECT * FROM student,score WHERE score.s_id=student.s_id;
　　2：需要查平均成绩大于65的，所以需要根据人的s_id进行group分组然后约束avg平均成绩大于65的
　　　　SELECT student.s_name,AVG(score.num) FROM 
　　　　score, 
　　　　student 
　　　　WHERE score.s_id=student.s_id GROUP BY student.s_id HAVING AVG(score.num)>65;
　　3：查询到的avg平均成绩保留2位小数，使用round函数
　　　　SELECT student.s_name,ROUND(AVG(score.num), 2) FROM 
　　　　score, 
　　　　student 
　　　　WHERE score.s_id=student.s_id GROUP BY student.s_id HAVING AVG(score.num)>65;
　　我的解法：
　　1：先在socre表中查询出成绩平均成绩大于65分的人的s_id和平均成绩
　　　　SELECT s_id,AVG(num) as pingjun FROM score GROUP BY s_id having AVG(num)>65
　　2：第一步拿到的表作为一个临时表个student表连接查询，其实差不多
　　　　SELECT p2.s_name, ROUND(p1.pingjun, 2) FROM 
　　　　(SELECT s_id,AVG(num) as pingjun FROM score GROUP BY s_id having AVG(num)>65) as p1, 
　　　　student as p2 
　　　　WHERE p1.s_id=p2.s_id;　

练习题四：https://www.cnblogs.com/wangfengming/articles/7891939.html
3：查询所有同学的姓名、选课数、总成绩；
　　1：学生总成绩需要使用分组，选课信息和总成绩使用score成绩表，学生姓名使用学生表student
　　　　SELECT * FROM student,score WHERE student.s_id=score.s_id;
　　2：连接后面的表按照学生s_id分组后查询总成绩，学生名字和选课数目
　　　　SELECT s_name,COUNT(score.s_id) as "人员选课门数",SUM(score.num) as "总成绩" FROM 
　　　　student,
　　　　score 
　　　　WHERE student.s_id=score.s_id GROUP BY score.s_id;
　　我的解法：
　　1：把socre表按照s_id人分组，查询总成绩，选课书数，和s_id得到一张临时表
　　　　SELECT s_id,SUM(num) as zongcj,SUM(case when 1=1 then 1 else 0 END) as "选课数" FROM score GROUP BY s_id
　　2：把1得到的临时表和student学生表连接查询
　　　　SELECT p1.s_name,p2.选课数,p2.zongcj FROM 
　　　　student as p1, 
　　　　(SELECT s_id,SUM(num) as zongcj,SUM(case when 1=1 then 1 else 0 END) as "选课数" FROM score GROUP BY s_id) as p2 
　　　　WHERE p1.s_id=p2.s_id;

练习题四：https://www.cnblogs.com/wangfengming/articles/7891939.html
4：查询所有的课程的名称以及对应的任课老师姓名;
　　1：课程表和老师连连接后，然后查询所有课程名称和任课老师名称
　　　　SELECT c_name,teacher.t_name FROM 
　　　　course,
　　　　teacher 
　　　　WHERE course.t_id=teacher.t_id;

练习题四：https://www.cnblogs.com/wangfengming/articles/7891939.html
5：查询没学过“alex”老师课的同学的姓名;
　　1：alex教学的课程的c_id查询到
　　　　SELECT  c_id FROM course, teacher WHERE course.t_id=teacher.t_id and t_name="alex";
　　　　SELECT c_id FROM course WHERE t_id = (SELECT t_id FROM teacher WHERE t_name="alex");　　　　alex教学课程的c_id = 2,4
　　2：查询学过2，4这两门课的学生有哪些
　　　　SELECT DISTINCT(s_id) FROM score WHERE c_id in (SELECT  c_id FROM course, teacher WHERE course.t_id=teacher.t_id and t_name="alex");
　　3：找到了学过2.4这两门课程的学生，那么就能从stuednt学生表中排除学过的人来找到没有学过的人
　　　　SELECT * FROM 
　　　　student 
　　　　WHERE s_id not in (SELECT DISTINCT(s_id) FROM score WHERE c_id in (SELECT c_id FROM course, teacher WHERE course.t_id=teacher.t_id and t_name="alex"));
　　我的解法：和上面类似
　　1：SELECT c_id FROM course WHERE t_id = (SELECT t_id FROM teacher WHERE t_name="alex")　　找到alex教过的课程
　　2：SELECT DISTINCT s_id FROM score WHERE c_id in (SELECT c_id FROM course WHERE t_id = (SELECT t_id FROM teacher WHERE t_name="alex"))
　　　　从score表中找到学过alex课程的学生的s_id　　1，2，4，5
　　3：从score表中找到除了学过alex课程1，2，4，5学生外其他的学生
　　　　SELECT DISTINCT s_id FROM score WHERE 
　　　　s_id not in (SELECT DISTINCT s_id FROM score WHERE c_id in (SELECT c_id FROM course WHERE t_id = (SELECT t_id FROM teacher WHERE t_name="alex")));

练习题四：https://www.cnblogs.com/wangfengming/articles/7891939.html
6:查询学过'python'并且也学过编号'java'课程的同学的姓名;
　　1：course,score,student 3表连接
　　　　SELECT * FROM course,score,student WHERE score.s_id=student.s_id and course.c_id=score.c_id
　　2：3表连接后加约束条件，查询表里学过python和java的记录
　　　　SELECT * FROM course,score,student WHERE score.s_id=student.s_id and course.c_id=score.c_id and course.c_name in ("python", "java") 
　　3：第2步后拿到的就是学习python和java的记录表，按照s_id分组后如果有2条记录的就是学过python+java的了
　　　　SELECT student.s_name FROM 
　　　　course,
　　　　score,
　　　　student 
　　　　WHERE score.s_id=student.s_id and course.c_id=score.c_id and course.c_name in ("python", "java") 
　　　　GROUP BY score.s_id 
　　　　HAVING COUNT(*)=2;

　　我的解法：
　　1：从course表中找到python和java课程对应的的课程c_id
　　SELECT c_id FROM course WHERE c_name="python" or c_name="java"　　　　得到课程id 1，2
　　2：score表按照s_id学生人员分组，分组后一个人多个数据，每条数据都是这个人员的课程+成绩为一行，
　　　　使用case when then else end语句，让一个分组人员后查询一个计数列，计算这个人学习python和java的课程数，
　　　　学习了python计1，学习了java计1，学习这两门以外的课程计数0这就是case语句：
　　　　　　case when c_id in (SELECT c_id FROM course WHERE c_name="python" or c_name="java") then 1 else 0 END
　　　　然后sum求这一个计数列的和，计数列的和为1+1=2的时候说明这个人学了python也学了java，然后找到这些人的s_id
　　SELECT s_id FROM 
　　score 
　　GROUP BY s_id 
　　HAVING sum(case when c_id in (SELECT c_id FROM course WHERE c_name="python" or c_name="java") then 1 else 0 END)=2
　　　　这里找到这些人的s_id了为1，2，4
　　3：找到了s_id后和student表关联查询得到学生名字
　　SELECT s_name FROM 
　　student 
　　WHERE s_id in 
　　(SELECT s_id FROM score GROUP BY s_id HAVING sum(case when c_id in (SELECT c_id FROM course WHERE c_name="python" or c_name="java") then 1 else 0 END)=2)

　　解法三：
　　1：学过python的人员表和学过java的人员表按照s_id人相同连接到一起，拿到s_id这个s_id就是学过两门的人的学生的s_id，然后拿着这个s_id去关联学生姓名
　　　　我这里还是3表连接在一起
　　SELECT student.s_name FROM 
　　(SELECT score.s_id,course.c_name FROM score, course WHERE score.c_id=course.c_id and c_name="python") as p1, 
　　(SELECT score.s_id,course.c_name  FROM score, course WHERE score.c_id=course.c_id and c_name="java") as p2, 
　　student 
　　WHERE p1.s_id=p2.s_id and p1.s_id=student.s_id;

练习题四：https://www.cnblogs.com/wangfengming/articles/7891939.html
7:查询学过“alex”老师所教的全部课程的同学的姓名；
　　1：查询alex到底教了哪些课程　　　　alex教的课程是2，4
　　　　SELECT c_id FROM course WHERE t_id = (SELECT t_id FROM teacher WHERE t_name = "alex");　　　获取alex教学的课程的id，2和4
　　　　SELECT c_id FROM course,teacher WHERE course.t_id=teacher.t_id and teacher.t_name="alex";
　　　　SELECT COUNT(*) FROM course,teacher WHERE course.t_id=teacher.t_id and teacher.t_name="alex";　　获取alex教的课程数目，2
　　2：查询学过这两门课程的学生，和上面学例子类似了，学生当中谁学了2和4的找出来，然后按照人分组，学了一门的count数=1，学了两门的count数=2
　　　　SELECT student.s_name FROM 
　　　　score,
　　　　student  
　　　　WHERE score.s_id=student.s_id and score.c_id in (SELECT c_id FROM course,teacher WHERE course.t_id=teacher.t_id and teacher.t_name="alex") 
　　　　GROUP BY student.s_name 
　　　　HAVING COUNT(*)=(SELECT COUNT(*) FROM course,teacher WHERE course.t_id=teacher.t_id and teacher.t_name="alex");
　　我的解法和上类似：
　　1：把score分数表按照s_id学生分组：
　　　　SELECT s_id FROM score GROUP BY s_id
　　2：分组后新增一列sum计算学习alex老师教过课程出现次数，sum计数2表示全部课程都学过的人，所有这里找出学过alex全部课程的学生的s_id，
　　　　SELECT s_id FROM 
　　　　score 
　　　　GROUP BY s_id 
　　　　HAVING sum((case when c_id in (SELECT c_id FROM course WHERE t_id = (SELECT t_id FROM teacher WHERE t_name = "alex")) then 1 else 0 END))=
　　　　(SELECT COUNT(*) FROM course,teacher WHERE course.t_id=teacher.t_id and teacher.t_name="alex")
　　　　　　这里找到s_id=4
　　3:最后根据4从student表中找学生姓名：SLECT s_name FROM student WHERE s_id = 4
　　　　SELECT s_name FROM student WHERE s_id = 
　　　　(SELECT s_id FROM 
　　　　score GROUP BY s_id 
　　　　HAVING sum((case when c_id in (SELECT c_id FROM course WHERE t_id = (SELECT t_id FROM teacher WHERE t_name = "alex")) then 1 else 0 END))= 
　　　　(SELECT COUNT(*) FROM course,teacher WHERE course.t_id=teacher.t_id and teacher.t_name="alex"))

练习题四：https://www.cnblogs.com/wangfengming/articles/7891939.html
8:查询挂科超过两门(包括两门)的学生姓名;　　假设70分以下不及格
　　1：从score成绩表中找到70分以下的数据，这里面得到的都是不及格的记录，记录里都是每个人没门考试不及格为一条数据，一个人多门不及格就多条数据
　　　　SELECT * FROM score,student WHERE score.s_id=student.s_id and num <=70;
　　2：把第一步得到的结果按照student人进行排序，找到count>=2的就是有两门不及格的了
　　　　SELECT student.s_name FROM 
　　　　score,
　　　　student 
　　　　WHERE score.s_id=student.s_id and num <=70 
　　　　GROUP BY student.s_name 
　　　　HAVING COUNT(*) >=2;
　　我的解法：
　　1：把score表按照人员分组，新增一列sum计算成绩小于70的计数。使用case语句，小于70的课程计1，否则计0，那么sum这个计数>=2的时候表示至少两门不及格，
　　　　找到这些至少两门不及格人员的s_id
　　　　SELECT s_id FROM score GROUP BY s_id  HAVING sum(case when num < 70 then 1 else 0 END) >=2
　　2:根据找到的s_id关联student学生表中的s_name学生名字：　　SELECT s_name FROM student WHERE s_id in "2门不及格的学生id"
　　　　SELECT s_name FROM student WHERE s_id in 
　　　　(SELECT s_id FROM score GROUP BY s_id  HAVING sum(case when num < 70 then 1 else 0 END) >=2);

练习题四：https://www.cnblogs.com/wangfengming/articles/7891939.html
9：查询有课程成绩小于60分的同学的姓名;
　　1：score表和student表连接筛选num成绩小于60的人，有可能一个人多门不及格所有使用distinct去重
　　　　SELECT DISTINCT s_name FROM score,student WHERE score.s_id=student.s_id and score.num < 60;
　　我的解法：
　　1：SELECT DISTINCT s_id FROM score WHERE num < 60　　从score表找到成绩小于60的记录按照s_id人员去重，得到有不及格的人员的s_id
　　2:步骤1得到了不及格的人员的s_id了根据s_id从student表中找人员名字：SELECT s_name FROM student WHERE s_id in "不及格的人员的s_id"
　　　　SELECT s_name FROM student WHERE s_id in (SELECT DISTINCT s_id FROM score WHERE num < 60);
　　3:或者把步骤1得到的s_id表和student表根据 student.s_id=p.s_id 连接起来，那么就自然只有人员两条数据，
　　　　因为不及格的人员的s_id=1，2　　student.s_id=p.s_id   只可能两条
　　　　SELECT * FROM student, (SELECT DISTINCT s_id FROM score WHERE num < 60) as p WHERE student.s_id=p.s_id;

练习题四：https://www.cnblogs.com/wangfengming/articles/7891939.html
10：查询选修了全部课程的学生姓名;　　
　　1：第一步需要知道全部课程有几门
　　　　SELECT count(*) FROM course;　　　　返回4门
　　2：学够4门的同学才符合标准，score表根据s_id人员分组，如果count=4表示学了4门：　　SELECT s_id FROM score GROUP BY s_id HAVING count(*)=4;
　　　　SELECT s_id FROM score GROUP BY s_id HAVING count(*) = (SELECT count(*) FROM course);
　　　　SELECT s_id FROM score GROUP BY s_id HAVING count(*)=(SELECT count(*) FROM course)-1;　　　　这个查询学了3门的学生的s_id
　　3：得到学生的s_id需要求学生的名字，和student连表查询
　　　　SELECT s_name FROM 
　　　　score,
　　　　student 
　　　　WHERE score.s_id=student.s_id 
　　　　GROUP BY s_name 
　　　　HAVING count(*)=(SELECT count(*) FROM course)-1;
　　我的解法：全部课程的c_id加起来等于10，所以对socre表按照人员分组把所有课程的c_id加起来等于10的就是学了所有课程的了
　　1：SELECT sum(c_id) FROM course　　求所有课程c_id的sum和为10
　　2：score表按照人分组后求每组的c_id之和是不是和1求出来的10相等
　　　　SELECT s_id FROM score GROUP BY s_id HAVING sum(c_id)=(SELECT sum(c_id) FROM course);

练习题四：https://www.cnblogs.com/wangfengming/articles/7891939.html
11：查询至少有一门课程与“貂蝉”同学所学课程相同的同学姓名；
　　1：查询貂蝉学了什么，学生表和课程表连接查询
　　　　SELECT c_id FROM student,score WHERE score.s_id=student.s_id and s_name="貂蝉";　　找到貂蝉学习的课程的c_id是1，2
　　2：至少一门一样，in就可以了　　SELECT s_name FROM score,student WHERE score.s_id=student.s_id and c_id in (1,2);
　　3：查询出来的记录对姓名去重
　　　　SELECT DISTINCT s_name FROM 
　　　　score,
　　　　student 
　　　　WHERE score.s_id=student.s_id and c_id in (SELECT c_id FROM student,score WHERE score.s_id=student.s_id and s_name="貂蝉");
　　4：查询出来的结果排除貂蝉
　　　　SELECT DISTINCT s_name FROM 
　　　　score,
　　　　student 
　　　　WHERE score.s_id=student.s_id and c_id in (SELECT c_id FROM student,score WHERE score.s_id=student.s_id and s_name="貂蝉") and s_name<>"貂蝉";
　　我的解法：和上面差不多：
　　1：查询到貂蝉学习的c_id：　　SELECT c_id FROM score WHERE s_id = (SELECT s_id FROM student WHERE s_name = "貂蝉")　　返回1，2
　　2：查询score表，判断c_id in 貂蝉学习的id　　并且　　s_id不等于貂蝉的id，这样得到剩下和貂蝉一样都学习了1，2课程学生的记录，然后按照s_id去重
　　　　SELECT DISTINCT s_id FROM 
　　　　score 
　　　　WHERE c_id in (SELECT c_id FROM score WHERE s_id = (SELECT s_id FROM student WHERE s_name = "貂蝉")) 
　　　　　　and s_id <> (SELECT s_id FROM student WHERE s_name = "貂蝉")
　　　　这里得到和貂蝉学习过至少一门课程的学生的s_id
　　3：然后根据s_id查询学生名字　　SELECT * FROM student WHERE s_id in "和貂蝉至少学过一门课程的学生id"
　　　　SELECT * FROM 
　　　　student 
　　　　WHERE s_id in (SELECT DISTINCT s_id FROM score WHERE c_id in (SELECT c_id FROM score WHERE s_id = (SELECT s_id FROM student WHERE s_name = "貂蝉")) 
　　　　　　and s_id <> (SELECT s_id FROM student WHERE s_name = "貂蝉"))

练习题四：https://www.cnblogs.com/wangfengming/articles/7891939.html
12：查询学过'貂蝉'同学全部课程 的其他同学姓名；
　　1：找到貂蝉学习了几门课程　　
　　　　SELECT count(*) FROM student,score WHERE score.s_id=student.s_id and s_name="貂蝉";　　　　　　返回2
　　2:和1类似，貂蝉学了2门，先从score表中查询出课程是1，2的记录，然后按照s_id分组，找到学了两门课程的人
　　　　SELECT s_name FROM 
　　　　score,
　　　　student 
　　　　WHERE score.s_id=student.s_id and c_id in (SELECT c_id FROM student,score WHERE score.s_id=student.s_id and s_name="貂蝉") and s_name<>"貂蝉"
　　　　GROUP BY s_name 
　　　　HAVING COUNT(*)=(SELECT count(*) FROM student,score WHERE score.s_id=student.s_id and s_name="貂蝉");
　　我的解法：
　　1：和上面类似：也是score表查询c_id  in 貂蝉学习过的课程1，2，然后按照学生的s_id分组，
　　　　　　　　　　每个人学习的课程的的c_id的和等于貂蝉学习课程的c_id得到和貂蝉一样学习了1，2的人的c_id
　　　　SELECT s_name FROM 
　　　　student 
　　　　WHERE s_id in (SELECT s_id FROM score WHERE c_id in (SELECT c_id FROM score WHERE s_id = (SELECT s_id FROM student WHERE s_name = "貂蝉")) 
　　　　GROUP BY s_id HAVING sum(c_id)=(SELECT sum(c_id) FROM score WHERE s_id = (SELECT s_id FROM student WHERE s_name = "貂蝉")))

练习题四：https://www.cnblogs.com/wangfengming/articles/7891939.html
13：查询和'貂蝉'同学学习的课程完全相同的,其他同学姓名；
　　和12提比较类似
　　1：知道貂蝉学习了哪些课程
　　　　SELECT c_id FROM student,score WHERE score.s_id=student.s_id and s_name="貂蝉"　　　　得到1，2
　　2：找到和貂蝉学的课程门数一样的人，  得到s_id=2,5
　　　　SELECT score.s_id FROM 
　　　　score,
　　　　student 
　　　　WHERE score.s_id=student.s_id 
　　　　GROUP BY score.s_id 
　　　　HAVING COUNT(*)=(SELECT COUNT(*) FROM student,score WHERE score.s_id=student.s_id and s_name="貂蝉");
　　3:从socre表中找到2，5这两个人，使用 s_id in (2, 5)　　现在找到同时学了两门的s_id，从score表中筛选出这两个人学的课程和考试的记录需要in这两名学生
　　　　但是学了两门还需要跟我学的课程一样
　　　　看貂蝉学了什么。然后还需要in一下貂蝉学的课程，那么剩下的同学一定是学过和貂蝉一样一门或者两门的同学，然后按照学生s_id分组看count计数等总课程数
　　　　那么剩下的人一定和貂蝉一样的了
　　　　SELECT s_name FROM 
　　　　score,
　　　　student 
　　　　WHERE score.s_id=student.s_id and score.s_id in 
　　　　　　(SELECT score.s_id FROM score,student WHERE score.s_id=student.s_id GROUP BY score.s_id HAVING COUNT(*)=
　　　　　　　　(SELECT COUNT(*) FROM student,score WHERE score.s_id=student.s_id and s_name="貂蝉")) 
　　　　　　and score.c_id in (SELECT c_id FROM student,score WHERE score.s_id=student.s_id and s_name="貂蝉") 
　　　　GROUP BY s_name 
　　　　HAVING COUNT(*)=2 and  s_name <> "貂蝉";
　　我的解法：
　　1：SELECT COUNT(*) FROM score,student WHERE score.s_id=student.s_id GROUP BY s_name HAVING s_name="貂蝉";　貂蝉学习的课程数为2
　　2：SELECT c_id FROM score,student WHERE score.s_id=student.s_id and s_name="貂蝉";　　貂蝉学习了哪些课程，查询学过了课程的c_id
　　3：貂蝉学过的课程也全部学过的学生的s_id
　　　　SELECT s_name FROM 
　　　　score,
　　　　student 
　　　　WHERE score.s_id=student.s_id and score.c_id in (SELECT c_id FROM score,student WHERE score.s_id=student.s_id and s_name="貂蝉") 
　　　　GROUP BY s_name 
　　　　HAVING COUNT(*)=(SELECT COUNT(*) FROM score,student WHERE score.s_id=student.s_id GROUP BY s_name HAVING s_name="貂蝉");
　　　　这里找到学了和貂蝉学过所有课程的学生名单：得到　　　鲁班，貂蝉，关羽
　　4:找到学过的课程数目和貂蝉学过的课程数目一模一样的学生
　　　　SELECT s_name FROM 
　　　　student,
　　　　score 
　　　　WHERE score.s_id=student.s_id 
　　　　GROUP BY s_name 
　　　　HAVING COUNT(*)=(SELECT COUNT(*) FROM score,student WHERE score.s_id=student.s_id GROUP BY s_name HAVING s_name="貂蝉");
　　　 这里找到和貂蝉学习的课程数目一样的学生名单：得到　　　貂蝉，张飞
　　5：3得到的结果和4得到的结果做并集剔除了貂蝉就能得到结果
　　　　SELECT p1.s_name FROM 
　　　　(SELECT s_name FROM 
　　　　score,
　　　　student 
　　　　WHERE score.s_id=student.s_id and score.c_id in (SELECT c_id FROM score,student WHERE score.s_id=student.s_id and s_name="貂蝉") 
　　　　GROUP BY s_name 
　　　　HAVING COUNT(*)=(SELECT COUNT(*) FROM score,student WHERE score.s_id=student.s_id GROUP BY s_name HAVING s_name="貂蝉")) as p1,
　　　　(SELECT s_name FROM 
　　　　student,
　　　　score 
　　　　WHERE score.s_id=student.s_id GROUP BY s_name 
　　　　HAVING COUNT(*)=(SELECT COUNT(*) FROM score,student WHERE score.s_id=student.s_id GROUP BY s_name HAVING s_name="貂蝉")) as p2
　　　　WHERE p1.s_name=p2.s_name and p1.s_name<>"貂蝉"

练习题四：https://www.cnblogs.com/wangfengming/articles/7891939.html
14：按平均成绩倒序显示所有学生的“python”、“java”、“linux”三门的课程成绩，按如下形式显示： 学生ID,python,java,linux,课程数,平均分
　　1：查询1同学的python课程的成绩
　　　　SELECT num FROM score,course WHERE score.c_id=course.c_id and course.c_name="python" and score.s_id=1;
　　　　　　这个结果可以作为一列(字段)来使用
重点：一个结果集合可以作为一个列来使用，作为一个字段来使用
　　2：查询id，课程数，平均分
　　　　SELECT s_id as "学生ID",　　　　　　学生id
　　　　ROUND(avg(num), 2), 　　　　　　　　平均分
　　　　COUNT(*) as "课程数"　　　　　　　　 课程数 　　　　　　　　
　　　　FROM score GROUP BY s_id;
　　3：把1和2结合在一起，1是查询s_id=1的人的python成绩，那么插入到2的select语句中查询s_id随着2里面的s_id变化
　　　　SELECT s_id as "学生ID", 
　　　　ROUND(avg(num), 2) as "平均成绩", 
　　　　COUNT(*) as "课程数",
　　　　(SELECT num FROM score,course WHERE score.c_id=course.c_id and course.c_name="python" and score.s_id=sco.s_id) as "python成绩",
　　　　(SELECT num FROM score,course WHERE score.c_id=course.c_id and course.c_name="java" and score.s_id=sco.s_id) as "java成绩",
　　　　(SELECT num FROM score,course WHERE score.c_id=course.c_id and course.c_name="linux" and score.s_id=sco.s_id) as "linux成绩"
　　　　FROM score as sco GROUP BY s_id;
　　我的解法：
　　1：score表按照人分组后查询平均成绩，课程数，这些数据
　　　　SELECT score.s_id, 
　　　　ROUND(AVG(score.num),2) as "平均成绩",
　　　　count(*) as "课程数" 
　　　　FROM score 
　　　　GROUP BY score.s_id ORDER BY AVG(score.num);
　　2：往1里查询到的结果加上  python成绩列，java成绩列这些
　　　　按照人每组了，相当于每组每个人压了多条数据，每条数据都是这个人不同课程的成绩记录
　　　　假设添加python成绩列，这一列只想查询分组后当前人的python的成绩，但是分组后num这列有这个同学多门课程的成绩，
　　　　python成绩列使用case语句当课程=python的时候记录python的num成绩，当课程不是python的时候成绩记录为0，
　　　　添加的python成绩列相当于积压了多条数据，当前人的python成绩和其他课程的成绩记为0，把这列python成绩列累加就得到了python成绩了
　　　　SELECT score.s_id, 
　　　　ROUND(AVG(score.num),2) as "平均成绩",
　　　　count(*) as "课程数" ,
　　　　SUM(case WHEN course.c_name="python" THEN score.num else NULL END) as "python成绩", 
　　　　SUM(case WHEN course.c_name="java" THEN score.num else NULL END) as "java成绩",
　　　　SUM(case WHEN course.c_name="linux" THEN score.num else NULL END) as "linux成绩",
　　　　SUM(case WHEN course.c_name="web" THEN score.num else NULL END) as "web成绩" 
　　　　FROM score,course WHERE score.c_id=course.c_id 
　　　　GROUP BY score.s_id 
　　　　ORDER BY AVG(score.num);

练习题四：https://www.cnblogs.com/wangfengming/articles/7891939.html
15：统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] 　　
　　1：按照各科分组，查询课程名称，课程编号，各科计数
　　　　SELECT score.c_id as "课程ID",course.c_name FROM course,score WHERE course.c_id=score.c_id GROUP BY score.c_id;
　　2：然后把各科的分数统计下人数，如下：　　
　　　　case when score.num<60 then 1 else 0 END) as "[ <60]　　
　　　　按照课程分组后，压缩成一堆，多个课程，如果这个课程分数小于60那么就是1，否则0，然后sum函数累加就得到了这个课程小于60分的人数统计了
　　　　SELECT course.c_name, 
　　　　sum(case when score.num<60 then 1 else 0 END) as "[ <60]", 
　　　　sum(case when score.num>=60 and score.num<70  then 1 else 0 END) as "[70-60]", 
　　　　sum(case when score.num>=70 and score.num<85  then 1 else 0 END) as "[85-70]",
　　　　sum(case when score.num>=85 and score.num<=100  then 1 else 0 END) as "[100-85]"
　　　　FROM score, course WHERE score.c_id=course.c_id GROUP BY course.c_name;
　　我的解法：
　　　　SELECT score.c_id,count(*) FROM score WHERE score.num < 60 GROUP BY score.c_id;
　　　　SELECT score.c_id,count(*) FROM score WHERE score.num >= 60 and score.num<70 GROUP BY score.c_id;
　　　　SELECT score.c_id,count(*) FROM score WHERE score.num >=70 and score.num<85 GROUP BY score.c_id;
　　　　SELECT score.c_id,count(*) FROM score WHERE score.num>=85 and score.num<=100 GROUP BY score.c_id;
　　　　　　上面的语句是按照c_id课程分组后查询每门课程c_id低于60的人数，60-70的人数，70-85的人数，85-100的人数
　　　　SELECT sco.c_id as "课程ID",course.c_name FROM course,score as sco WHERE course.c_id=sco.c_id GROUP BY sco.c_id;
　　　　　　这是从score成绩表按照c_id课程名称分类后，得到课程的id和课程的名称，
　　　　　　现在还需要把上面的小于60这些的统计数据插入到这里面
　　　　如下：
　　　　SELECT sco.c_id as "课程ID",course.c_name,
　　　　(SELECT count(*) FROM score WHERE score.num < 60 GROUP BY score.c_id HAVING score.c_id=sco.c_id) as "[ <60]",
　　　　(SELECT count(*) FROM score WHERE score.num >= 60 and score.num<70 GROUP BY score.c_id HAVING score.c_id=sco.c_id) as "[70-60]",
　　　　(SELECT count(*) FROM score WHERE score.num >=70 and score.num<85 GROUP BY score.c_id HAVING score.c_id=sco.c_id) as "[85-70]",
　　　　(SELECT count(*) FROM score WHERE score.num>=85 and score.num<=100 GROUP BY score.c_id HAVING score.c_id=sco.c_id) as "[100-85]" 
　　　　FROM course,score as sco WHERE course.c_id=sco.c_id GROUP BY sco.c_id;
　　　　从按照课程低于60分数的人数统计表中 找到当前语句分组后当前课程id的的低于60分的计数值
　　　　依次找到70-60等的插入数值插入进去

练习题四：https://www.cnblogs.com/wangfengming/articles/7891939.html
16：查询每门课程被选修的次数
SELECT course.c_name, COUNT(*) as "选修人数" FROM score,course WHERE score.c_id=course.c_id GROUP BY course.c_name;

17：查询出只选修了一门课程的学生的学号和姓名　　按照学生学号分组，count计数为1的就是选了一门课程
SELECT score.s_id,student.s_name FROM score,student WHERE score.s_id=student.s_id GROUP BY score.s_id HAVING COUNT(*)=1;

18：查询学生表中男生、女生各有多少人
SELECT s_sex,count(*) as "计数" FROM student GROUP BY s_sex;

19：查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
SELECT c_id, ROUND(avg(num), 2) as "平均成绩" FROM score GROUP BY c_id ORDER BY avg(num), c_id DESC;
　　order by可以按照多个字段来排序，依次逗号往后写就行了，第一个字段开始，第一个字段一样找第二个字段，依此类推

20：查询课程名称为“java”，且分数低于60的学生姓名和分数
　　SELECT s_name,num FROM 
　　course, 
　　score,
　　student 
　　WHERE course.c_id=score.c_id and score.s_id=student.s_id and course.c_name="java" and score.num< 60;
我的解法：
　　1：从socre成绩表找到java课程分数小于60的人的s_id和分数
　　　　SELECT s_id,num FROM score WHERE c_id =(SELECT c_id FROM course WHERE c_name = "java") and score.num<60
　　2:根据找到的学生的s_id得到学生姓名等信息　　　　
　　　　从1得到一个记录分数小于60的  s_id和num分数的表然后跟student连表，过滤出s_id相同的那么就得到一个学生信息和记录java 60分以下的表
　　　　SELECT student.s_name,p1.num FROM 
　　　　student, 
　　　　(SELECT s_id,num FROM score WHERE c_id =(SELECT c_id FROM course WHERE c_name = "java") and score.num<60) as p1 
　　　　WHERE student.s_id=p1.s_id;

17：多表查询：一　　参数文档：http://www.cnblogs.com/wangfengming/articles/8067220.html

1:多表联合查询：
多表联合查询语法：
select  字段1,字段2... from 表1,表2... [where 条件]

如果不加条件直接进行查询,则会出现以下效果,这种结果我们称之为 笛卡尔乘积
SELECT * FROM person, dept;    笛卡尔乘积，两个表数据结果集相乘，假设表a5条表b5条程积就是25条，假设3张表都5条数据那么程积就是125条数据
　　　　　　　　　　　　　　　　　　笛卡尔乘积是多表查询得基础，这种数据不能使用，所以需要使用id关系过滤一下
多表联合查询：SELECT * FROM person,dept 
SELECT * FROM person p, dept d WHERE p.dept_id=d.did;　　两张表中符合where条件的数据才会出来

2：多表连接查询
多表连接查询语法(重点)
SELECT 字段列表 FROM 表1  INNER|LEFT|RIGHT JOIN  表2 ON 表1.字段 = 表2.字段;

一：内连接查询inner join
select * from person inner JOIN dept  on person.dept_id =dept.did;　　内连接查询与多表联合查询效果是一样的

二：左连接查询left join：左边的表数据都出来，和右边表匹配的就匹配上，不匹配上的使用空填充
select * from person LEFT JOIN dept;　　　　不加on条件执行会报错，不会返回笛卡尔乘积，这种写法要求必须要给一个条件，单独自己条件的关键字on
select * from person LEFT JOIN dept  on person.dept_id =dept.did;

三：右连接查询right join：右边的表数据都出来，和左边表匹配的就匹配上，不匹配上的使用空填充
select * from person LEFT JOIN dept  on person.dept_id =dept.did;

多表联合查询和多表连接查询：
　　多表联合查询：符合条件的数据统统拿出来，不符合的扔掉
　　连接查询：一张表作为基准，去另外一张表去匹配，匹配上的拿过来，匹配不上的用空来填充，基准表的所有数据都会显示出来


SELECT * FROM person, dept;
SELECT * FROM person INNER JOIN dept;
　　这两个查询方式打印的结果都是一样，都是笛卡尔乘积，表a的数x表b的数目

4：全连接查询(显示左右表中全部数据)　　union 
　　左连接查询 union 右连接查询　　达到全连接查询的效果
　　全连接查询：是在内连接的基础上增加 左右两边没有显示的数据
　　注意: mysql并不支持全连接 full JOIN 关键字
　　注意: 但是mysql 提供了 UNION 关键字.使用 UNION 可以间接实现 full JOIN 功能　　
查询人员和部门的所有数据
SELECT * FROM person LEFT JOIN dept ON person.did = dept.did
UNION
SELECT * FROM person RIGHT JOIN dept ON person.did = dept.did;

union：重复的数据去重
UNION 和 UNION ALL 的区别:
　　UNION 会去掉重复的数据,而 UNION ALL 则直接显示结果

复杂条件多表查询练习：
1：查询出 教学部 年龄大于20岁,并且工资小于40000的员工,按工资倒序排列.(要求:分别使用多表联合查询和内连接查询)
SELECT * FROM dept, person WHERE dept.did=person.dept_id and age>20 and salary<40000 and dname="教学部" ORDER BY salary DESC;
SELECT * FROM dept INNER JOIN person on dept.did=person.dept_id  WHERE age>20 and salary<40000 and dname="教学部" ORDER BY salary DESC;
SELECT * FROM person WHERE age>20 and dept_id = (SELECT did FROM dept WHERE dname="教学部") and salary<10000 ORDER BY salary DESC;　　复杂的条件查询，多个条件，子查询

2：查询每个部门中最高工资和最低工资是多少,显示部门名称
SELECT max(salary), min(salary),dept_id,dname FROM person LEFT JOIN dept on person.dept_id=dept.did GROUP BY dept_id;

子语句查询：
    子查询(嵌套查询): 查多次, 多个select，最外层的select语句称为父语句，里层的是子语句
    注意: 第一次的查询结果可以作为第二次的查询的 条件 或者 表名 使用.
    子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字. 还可以包含比较运算符：= 、 !=、> 、<等.

1.结果集作为表名使用
select * from (select * from person) as 表名;
ps:大家需要注意的是: 一条语句中可以有多个这样的子查询,在执行时,最里层括号(sql语句) 具有优先执行权.<br>注意: as 后面的表名称不能加引号('')
SELECT * FROM (SELECT * FROM person) as per;　　　　　　　别名不让加引号，操作的时候不能操作原表的时候可以这样使用，临时表的使用

2：求最大工资那个人的姓名和薪水
SELECT salary,name FROM person ORDER BY salary DESC LIMIT 1;　　　　　　排序查询
SELECT name,salary FROM person WHERE salary=(SELECT MAX(salary) FROM person);　　子语句查询
SELECT MAX(salary),name,salary FROM person;　　这样查询不行，在8.0版本报错，5.7以前的版本查询出的结果异常是因为only_full_group_by 模式
　　5.6版本查询可以，max聚合函数最大值后从整张表中挑一个最大的，如果加其他字段的时候会给第一条加上，
　　name这些字段查询的都是一条数据，不管最大值是哪个人的，和分组是一样的

3：only_full_group_by 模式详解：https://www.jianshu.com/p/7f532985ff39
　　在这个模式下，我们使用分组查询时，出现在select字段后面的只能是group by后面的分组字段，或使用聚合函数包裹着的字段
　　mysql报错1140：
　　　　https://blog.csdn.net/qq_41937388/article/details/107280037
　　mysql5.7后面的版本删除sql_modul的only_full_group_by模式：
　　　　https://www.jianshu.com/p/4b69e39edc4a
　　　　https://zhuanlan.zhihu.com/p/103283746
　　mysql开启only_full_group_by 模式的影响：
　　　　https://segmentfault.com/q/1010000007914969/a-1020000007966378
　　　　https://www.jianshu.com/p/7f532985ff39

4：聚合函数和group by会把表里面的记录压缩，比如聚合max后select还查询name，只拿出压缩后数据的一条

5：求工资高于所有人员平均工资的人员
SELECT * FROM person WHERE salary>(SELECT AVG(salary) FROM person);

6：查询平均年龄在20岁以上的部门名
SELECT * from dept where dept.did in (select dept_id from person GROUP BY dept_id HAVING avg(person.age) > 20);

7：查询教学部 下的员工信息
select * from person where dept_id = (select did from dept where dname ='教学部');

8：查询大于所有人平均工资的人员的姓名与年龄
SELECT * FROM person WHERE salary> (select avg(salary) from person);　　　　子语句查询

子语句查询配合关键字：
　　子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字. 还可以包含比较运算符：= 、 !=、> 、<等.
1：any：
　　假设any内部的查询语句返回的结果个数是三个，如:result1,result2,result3,那么，
　　select ...from ... where a > any(...);　　等同　　select ...from ... where a > result1 or a > result2 or a > result3;　　和in()集合类似

2：ALL关键字：ALL关键字与any关键字类似，只不过上面的or改成and。即:
　　select ...from ... where a > all(...);　　等同　　select ...from ... where a > result1 and a > result2 and a > result3;

3：SOME关键字：some关键字和any关键字是一样的功能
　　select ...from ... where a > some(...);　　等同　　select ...from ... where a > result1 or a > result2 or a > result3;

4：EXISTS 关键字
　　SELECT ... FROM table WHERE  EXISTS (子条件查询)
　　该语法可以理解为：主查询(外部查询)会根据子查询验证结果（TRUE 或 FALSE）来决定主查询是否得以执行。
　　　　　　　　　　子语句查询结果返回一个True，TRUE传递给前面的select查询就会得到执行，如果子语句查询得到的结果是空，
　　　　　　　　　　结果就认为是False，造成前面的语句不执行
SELECT * FROM person WHERE EXISTS(SELECT * FROM dept WHERE did=5);　　　　　　子语句查询到结果不为空那么为True父语句能查询到结果
SELECT * FROM person WHERE EXISTS(SELECT * FROM dept WHERE did=7);　　　　　  子语句查询的结果为空那么为False，父语句无法查询到结果　　 

 18：多表查询：二　　参数文档：http://www.cnblogs.com/wangfengming/articles/8067220.html

其他查询

1：临时表查询
需求:  查询高于本部门平均工资的人员
解析思路: 1.先查询本部门人员平均工资是多少　　SELECT AVG(salary) FROM person GROUP BY dept_id;　　
 　　　　 2.再使用人员的工资与部门的平均工资进行比较　　
SELECT * FROM person as p1,
　　(SELECT AVG(salary) as pj,dept_id FROM person GROUP BY dept_id) as p2
WHERE p1.dept_id=p2.dept_id and p1.salary>p2.pj;　　　　　　　　联合查询

SELECT * FROM person p1 inner JOIN 
　　(SELECT dept_id, AVG(salary) avg_income FROM person GROUP BY dept_id) p2 on p1.dept_id=p2.dept_id 
WHERE p1.salary>p2.avg_income;　　　　　　　　　　　　　　连接查询

ps：语句中,我们可以把上一次的查询结果当前做一张表来使用.因为p2表不是真是存在的,所以:我们称之为 临时表　　
　　临时表:不局限于自身表,任何的查询结果集都可以认为是一个临时表.


2：判断查询　IF关键字
需求1:根据工资高低,将人员划分为两个级别,分别为 高端人群和低端人群。显示效果:姓名,年龄,性别,工资,级别
　　工资10000以上的就算高端人群，10000一下低端人群
SELECT name,age,sex,salary,
　　if(salary>10000, "高端人士", "低端人士") as "级别"　　　　
FROM person
ps：
　　if语法: IF(条件表达式,"条件表达式判断为true取这里",'条件结果判断为False取这里');
　　if语句也可以作为字段展示，


3：多条件查询　　case  when then　　语法
需求2: 根据工资高低,统计每个部门人员收入情况,划分为 富人,小资,平民,吊丝 四个级别, 要求统计四个级别分别有多少人
语法一：
SELECT
CASE WHEN 1 = '1' THEN '成功'
WHEN 1 = '2' THEN '失败'
ELSE '其他' END;　　　　打印　　成功

SELECT
CASE WHEN 2 = '1' THEN '成功'
WHEN 2 = '2' THEN '失败'
ELSE '其他' END;　　　　打印　　失败

SELECT
CASE WHEN 1 = '1' THEN '成功'
WHEN 2 = '2' THEN '失败'
ELSE '其他' END;　　　　打印　　成功　　因为第一个when语句就匹配上了，如果都匹配不上才会去走else里面的，最后需要加end表示条件的结束
语法二：判断的字段移动到前面去了，判断age是不是等于23，27，30然后结束
SELECT CASE age
WHEN 23 THEN '23岁'
WHEN 27 THEN '27岁'
WHEN 30 THEN '30岁
ELSE '其他岁' END
FROM person;

SELECT CASE 20
WHEN 23 THEN '23岁'
WHEN 27 THEN '27岁'
WHEN 30 THEN '30岁'
ELSE '其他岁' END

需求2: 根据工资高低,统计每个部门人员收入情况,划分为 富人,小资,平民,吊丝 四个级别, 要求统计四个级别分别有多少人
SELECT dname,
SUM(case when person.salary>10000 then 1 else 0 END) as "富人",
SUM(case when person.salary BETWEEN 5000 and 10000 then 1 else 0 END) as "小资",
SUM(case when person.salary BETWEEN 3000 and 5000 then 1 else 0 END) as "平民",
SUM(case when person.salary<3000 then 1 else 0 END) as "屌丝"
FROM dept,person WHERE dept.did=person.dept_id GROUP BY dname
　　工资大于10000的就给他返个1，否则返回0，sum统计人数
　　group分组后其实还是有那么多条数据，只是压缩了，比如12个人，按照分组有5个组
　　case when person.salary>10000 then 1 else 0 END　　这个还是会作用在12条数据每一条上，
　　相当于select又加了一项目case when person.salary>10000 then 1 else 0 END这个查询项，如果符合条件返回1，不符合条件返回0
　　然后使用sum吧这一项的数据全加起来

 19：外键约束　　建立两个表的关联关系，其他的表的主键在别人的表中就称之为外键，从表里的外键关联字段只能设置成主表的主键的范围，主表删除被从表关联的记录默认无法删除

1.问题?
　　什么是约束:约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性

2.问题?
　　以上两个表 person和dept中, 新人员可以没有部门吗?

3.问题?
　　新人员可以添加一个不存在的部门吗?

4.如何解决以上问题呢?
　　简单的说,就是对两个表的关系进行一些约束 (即: froegin key：外键). 
　　foreign key 定义:就是表与表之间的某种约定的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，关连性更强。

添加外键：
1：创建表时,同时创建外键约束
CREATE TABLE IF NOT EXISTS dept (
    did int not null auto_increment PRIMARY KEY,
    dname VARCHAR(50) not null COMMENT '部门名称'
)ENGINE=INNODB DEFAULT charset utf8;
   
CREATE TABLE IF NOT EXISTS person(
    id int not null auto_increment PRIMARY KEY,
    name VARCHAR(50) not null,
    age TINYINT(4) null DEFAULT 0,
    sex enum('男','女','人妖') NOT NULL DEFAULT '人妖',
    salary decimal(10,2) NULL DEFAULT '250.00',
    hire_date date NOT NULL,
    dept_id int(11) DEFAULT NULL,
　  CONSTRAINT fk_did FOREIGN KEY(dept_id) REFERENCES dept(did) -- 添加外键约束
)ENGINE = INNODB DEFAULT charset utf8;


2：已经创建表后,追加外键约束
添加外键约束
　　ALTER table person add constraint fk_did FOREIGN key(dept_id) REFERENCES dept(did);
　　　　constraint：约束
　　　　fk_did:约束的名字，随便起
　　　　给person表的dept_id字段添加外键，和dept表的did字段产生约束的关系
　　　　一般外键字段需要设置不能为空，
　　　　添加外键的字段dept_id只能设置成detp表里面的did字段含有的范围，不能超过

删除外键约束 
　　ALTER TABLE person drop FOREIGN key fk_did;　　　　　　外键名字规则就是fk_开头


3：定义外键的条件
　　1：外键对应的字段数据类型保持一致,且被关联的字段(即references指定的另外一个表的字段)，必须保证唯一，也就是person表的dep_id和dept表的did类型一致
　　2：所有tables的存储引擎必须是InnoDB类型.存储引擎只有innodb才支持外键，其他的存储引擎不支持外键
　　3：如果需要外键约束,最好创建表同时创建外键约束.　　数据都添加完了再设置外键可能加不上，需要删数据
　　　　如果需要设置级联关系,删除时最好设置为 SET NULL.
　　4：外键的约束4种类型: 1.RESTRICT 2. NO ACTION 3.CASCADE 4.SET NULL
　　　　外键约束4种关联关系，主要针对删除和更新来说
　　　　假设：person表中把人员删除了，那么这个部门表这条记录需要删除吗，因为表之间有关联----需要自己设置
　　　　1.RESTRICT：约束
　　　　　　主表是dept主键表，从表person人员表，主表的数据不能随便删除
　　　　　　部门dept表的主键作为了人员表person的外键并且参数了约束关系，删除部门表种的5，人员指定的5没有数据了，约束非法的了
　　　　　　RESTRICT建立了约束就不让删除，想删1：人员表使用5的人都删除或者指向6让外键约束不生效了
　　　　　　主表不能随意删除，从表可以随意删除记录
　　　　2：NO ACTION　　如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作，和restrict使用一样  
　　　　3：CASCADE 　　串联　　可怕，别选
　　　　　　dept主表删除6，那么从表person和6进行外键关联的数据也跟着删除
　　　　4：SET NULL
　　　　　　dept主表删除6，那么从表person和6进行外键关联的数据的字段变成Null
　　　　　　这个需要把person表的外键关联字端　　可以为null　　也就是不要把这个字段设置为不能为null
　　　　
　　　　最常用的约束类型：
　　　　　　set null和restrict，默认就是RESTRICT约束不让删除
　　　　真实外键和虚拟：
　　　　　　真的建一个外键，虚拟外键就算理论上通过两个字段连接，虚拟外键表便于维护，删除表数据的时候不会因为有外键而删除不了
　　　　　　理论外键就是不设置真正的外键关联但是还是按照外键的约束去设置数据，真正的约束就设置外键，不设置就是理论的外键

ps：
　　插入数据时，先插入主表(dept部门表)中的数据，再插入从表(person人员表)中的数据。先插入从表，关联外键不存在就报错
　　删除数据时，先删除从表中的数据，再删除主表中的数据　　默认有约束关系　　

设置外键约束后：
　　1：主表dept部门表的数据不能随意删除记录，因为他和person表建立了关系，把dept表删除了拿person表外键指向的字段怎么办，所以主表不能随便删除
　　　　想删除：
　　　　　　1：从表外键关联的到的数据删除或者修改
　　　　　　2：设置约束的模式
　　2：从表的数据可以随意删除
　　3：从表有外键，新增的时候外键字段只能选择主表中被关联字段存在的数据

20：非空约束

关键字: NOT NULL ,表示不可空. 用来约束表中的字段列
create table t1(
       id int(10) not null primary key,
       name varchar(100) null
 );   
字段不能设置为空

21：主键约束

用于约束表中的一行，作为这一行的标识符，在一张表中通过主键就能准确定位到一行，因此主键十分重要。主键约束就是主键的值需要唯一不能重复
每张表中只能有一个主键约束
reate table t2(
    id int(10) not null primary key
);

注意: 主键这一行的数据不能重复且不能为空。
还有一种特殊的主键，多个字段作为主键的时候——复合主键。主键不仅可以是表中的一列，也可以由表中的两列或多列来共同标识
create table t3(
    id int(10) not null,
    name varchar(100) ,
    primary key(id,name)
);

22：唯一约束

关键字: UNIQUE, 比较简单，它规定一张表中指定的一列的值必须不能有重复值，即这一列每个值都是唯一的。
create table t4(
    id int(10) not null,
    name varchar(255) ,
    unique id_name(id,name)　　　　id和name是唯一约束，组合在一张表的列中只能出现一次，列里面不能重复
);
//添加唯一约束
alter table t4 add unique id_name(id,name);
//删除唯一约束
alter table t4 drop index id_name;
注意: 当INSERT语句新插入的数据和已有数据重复的时候，如果有UNIQUE约束，则INSERT失败.　

唯一约束和主键约束效果一样的，只不过唯一约束不是主键，添加唯一约束之后值在这张表种只能出现一次
主键：一张表之中只能有一个主键
唯一约束：一张表种可以有多个唯一约束

23：默认值约束  default

关键字: DEFAULT
create table t5(
    id int(10) not null primary key,
    name varchar(255) default '张三'   
);
#插入数据
INSERT into t5(id) VALUES(1),(2);
INSERT into t5(id) VALUES(1,default),(2,default);　　使用default关键字调用默认值
注意: INSERT语句执行时.，如果被DEFAULT约束的位置没有值，那么这个位置将会被DEFAULT默认的值填充　

5种约束类型：
　　外键约束　　　　最重要的

 24：表与表之间的关系　　参考文档：https://www.cnblogs.com/wangfengming/articles/8067220.html#label4

表关系分类:
    总体可以分为三类: 一对一 、一对多(多对一) 、多对多

如何区分表与表之间是什么关系?
　　如果person表的dept_id加一个唯一约束的话，dept_id是外键和dept表的did关联　　一对一
　　如果person表的dept_id加没有约束的话，peron表的多个人的dept_id部门都相同，dept_id是外键和dept表的did关联　　一对多
　　超市和人，一个人可以去多个超市，多个超市能进不同的人　　多对多，两张表无法完全表示多对多的关系的，需要一个第三张表来表示，中间表

一对一关系，建立唯一约束：
　　外键约束+唯一约束实现
　　一张表的主键只能有一个，另外一张表给这个字段加个唯一----建立1对1的关系
　　不是主键与普通字段，外键与普通字段，普通字段加唯一约束，外键字段froegin key加唯一约束----产生1对1的关系

多对多：
　　两张表无法建立关系，搞个第三张表存储两张表的关系

　　建立表关系：一对多关系

一对多关系：
　例如:一个人可以拥有多辆汽车，要求查询某个人拥有的所有车辆。
　分析：人和车辆分别单独建表，那么如何将两个表关联呢？有个巧妙的方法，在车辆的表中加个外键字段(人的编号)即可。
　* (思路小结：’建两个表，一’方不动，’多’方添加一个外键字段)*
//建立人员表
CREATE TABLE people(
    id VARCHAR(12) PRIMARY KEY,
    sname VARCHAR(12),
    age INT,
    sex CHAR(1)
);
INSERT INTO people VALUES('H001','小王',27,'1');
INSERT INTO people VALUES('H002','小明',24,'1');
INSERT INTO people VALUES('H003','张慧',28,'0');
INSERT INTO people VALUES('H004','李小燕',35,'0');
INSERT INTO people VALUES('H005','王大拿',29,'1');
INSERT INTO people VALUES('H006','周强',36,'1');

 //建立车辆信息表
CREATE TABLE car(
    id VARCHAR(12) PRIMARY KEY,
    mark VARCHAR(24),
    price NUMERIC(6,2),
    pid VARCHAR(12),
    CONSTRAINT fk_people FOREIGN KEY(pid) REFERENCES people(id)
);
INSERT INTO car VALUES('C001','BMW',65.99,'H001');
INSERT INTO car VALUES('C002','BenZ',75.99,'H002');
INSERT INTO car VALUES('C003','Skoda',23.99,'H001');
INSERT INTO car VALUES('C004','Peugeot',20.99,'H003');
INSERT INTO car VALUES('C005','Porsche',295.99,'H004');
INSERT INTO car VALUES('C006','Honda',24.99,'H005');
INSERT INTO car VALUES('C007','Toyota',27.99,'H006');
INSERT INTO car VALUES('C008','Kia',18.99,'H002');
INSERT INTO car VALUES('C009','Bentley',309.99,'H005');
car汽车表添加一个pid字段，pid就是人员id并且把pid设置成为外键，一对多，并且互有约束关系


例子1:学生和班级之间的关系
班级表
id   class_name 
1    python脱产100期
2    python脱产300期

学生表          foreign key               
id     name    class_id
1       alex     2
2       刘强东    2
3       马云      1

例子2: 一个女孩 拥有多个男朋友...

例子3:....

　　建立表关系：一对一关系

 

一对一关系
　　例如：一个中国公民只能有一个身份证信息
　　分析: 一对一的表关系实际上是 变异了的 一对多关系. 通过在从表的外键字段上添加唯一约束(unique)来实现一对一表关系.

 #身份证信息表
CREATE TABLE card (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code varchar(18) DEFAULT NULL,
  UNIQUE un_code (CODE) -- 创建唯一索引的目的,保证身份证号码同样不能出现重复，code就是身份证，表中只能有一个加unique唯一约束
);

INSERT INTO card VALUES(null,'210123123890890678'),
                       (null,'210123456789012345'),
                       (null,'210098765432112312');
#公民表
CREATE TABLE people (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(50) DEFAULT NULL,
  sex char(1) DEFAULT '0',
  c_id int UNIQUE, -- 外键添加唯一约束,确保一对一
  CONSTRAINT fk_card_id FOREIGN KEY (c_id) REFERENCES card(id)
);

INSERT INTO people VALUES(null,'zhangsan','1',1),
                         (null,'lisi','0',2),
                         (null,'wangwu','1',3);


例子一：一个用户只有一个博客
    用户表：
    主键
    id   name
    1    egon
    2    alex
    3    wupeiqi

    博客表   
           fk+unique
    id url user_id
    1  xxxx   1
    2  yyyy   3
    3  zzz    2

例子2: 一个男人的户口本上，一辈子最多只能一个女主的名字.等等

　　建立表关系：多对多关系

多对多关系
　例如：学生选课，一个学生可以选修多门课程，每门课程可供多个学生选择。
　分析：这种方式可以按照类似一对多方式建表，但冗余信息太多，好的方式是实体和关系分离并单独建表，实体表为学生表和课程表，关系表为选修表，
　　　　其中关系表采用联合主键的方式(由学生表主键和课程表主键组成)建表。

#//建立学生表
CREATE TABLE student(
    id VARCHAR(10) PRIMARY KEY,
    sname VARCHAR(12),
    age INT,
    sex CHAR(1)
);
INSERT INTO student VALUES('S0001','王军',20,1);
INSERT INTO student VALUES('S0002','张宇',21,1);
INSERT INTO student VALUES('S0003','刘飞',22,1);
INSERT INTO student VALUES('S0004','赵燕',18,0);
INSERT INTO student VALUES('S0005','曾婷',19,0);
INSERT INTO student VALUES('S0006','周慧',21,0);
INSERT INTO student VALUES('S0007','小红',23,0);
INSERT INTO student VALUES('S0008','杨晓',18,0);
INSERT INTO student VALUES('S0009','李杰',20,1);
INSERT INTO student VALUES('S0010','张良',22,1);

# //建立课程表
CREATE TABLE course(
    id VARCHAR(10) PRIMARY KEY,
    sname VARCHAR(12),
    credit DOUBLE(2,1),
    teacher VARCHAR(12)
);
INSERT INTO course VALUES('C001','Java',3.5,'李老师');
INSERT INTO course VALUES('C002','高等数学',5.0,'赵老师');
INSERT INTO course VALUES('C003','JavaScript',3.5,'王老师');
INSERT INTO course VALUES('C004','离散数学',3.5,'卜老师');
INSERT INTO course VALUES('C005','数据库',3.5,'廖老师');
INSERT INTO course VALUES('C006','操作系统',3.5,'张老师');

# //建立选修表
CREATE TABLE sc(
    sid VARCHAR(10),
    cid VARCHAR(10),
      PRIMARY KEY(sid,cid),　　联合主键
      CONSTRAINT fk_student FOREIGN KEY(sid) REFERENCES student(id),　　外键，往这个字段在学生表中出现过
      CONSTRAINT fk_course FOREIGN KEY(cid) REFERENCES course(id)　　　　外键
);
INSERT INTO sc VALUES('S0001','C001');
INSERT INTO sc VALUES('S0001','C002');
INSERT INTO sc VALUES('S0001','C003');
INSERT INTO sc VALUES('S0002','C001');
INSERT INTO sc VALUES('S0002','C004');
INSERT INTO sc VALUES('S0003','C002');
INSERT INTO sc VALUES('S0003','C005');
INSERT INTO sc VALUES('S0004','C003');
INSERT INTO sc VALUES('S0005','C001');
INSERT INTO sc VALUES('S0006','C004');
INSERT INTO sc VALUES('S0007','C002');
INSERT INTO sc VALUES('S0008','C003');
INSERT INTO sc VALUES('S0009','C001');
INSERT INTO sc VALUES('S0009','C005');

例子1:中华相亲网: 男嘉宾表+相亲关系表+女嘉宾表
男嘉宾:
    1  孟飞
    2  乐嘉
女嘉宾:
    1  小乐
    2  小嘉
                    
相亲表:(中间表)
                    
男嘉宾  女嘉宾  相亲时间
1          1            2017-10-12 12:12:12                    
1          2           2017-10-13 12:12:12
1          1           2017-10-15 12:12:12

例子2: 用户表,菜单表,用户权限表...

25：数据库设计三范式　　参考文档：http://www.cnblogs.com/wangfengming/p/7929118.html

26：Mysql之视图　　create view　　　　一张虚拟的表，把临时表永久存储起来，下次使用的话和普通表一样

　　参考文档：　　https://www.cnblogs.com/wangfengming/articles/7883974.html#lable1

SELECT name, age ,sex FROM person;
　　这个sql语句执行得到的结果就是一张临时表，在数据库当中不是真实存在的，查询产生的结果集，虚拟的存在
　　多表情况也是这样
如果上面结果用的比较频繁，每次使用需要写一次，特别浪费时间，经常使用的可以把结果保存起来

视图:是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据
　　　这个表不是真实存在的，内容由多个或者一个实体表来定义出来的，
　　一张正常的表存储两个文件
　　　　xxx.frm　　文件结构
　　　　xxx.ibd　　文件的数据
　　而虚拟表只存储一个文件结构xxx.frm，文件的结构有可能是由多张或者一张表来定义的，由查询sql决定，没有真实的idb文件，所以是虚拟表，只有结构没有数据
　　　　每次使用的时候根据结构文件找相应的表拿到真实的数据，所以虚拟币关联的相应表的数据如果改变，那么虚拟表拿到的数据也会改变
　　　　这个使用比较方便
　　试图存储在navicate可视化工具的视图目录下，可以查看，和实体表一模一样，区别就是没有实际数据存储文件

操作视图和操作表的方式一模一样的
　　1：视图能改变字段，视图改变会影响相应的关联的实体表

视图有如下特点;
　　1. 视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系。
　　2. 视图是由基本表（实表）产生的表（虚表）。
　　3. 视图的建立和删除不影响基本表。
　　4. 对视图内容的更新（添加、删除和修改）直接影响基本表。
　　5. 当视图来自多个基本表时，不允许添加和删除数据，单表可以

CREATE VIEW ppp as SELECT name, age,dname FROM person, dept WHERE dept.did=person.dept_id;　　
　　多表创建一个视图，删除新增无法操作，修改我的8.0版本也行，以前的5.6或者允许修改

视图实际开发使用的不多，当实体表结构发送变化，新增一个xxx字段或者删除一个字段，或者修改一个字段的名称，虚拟表就使用不了了

1.创建视图
    create view 视图名称  as sql查询语句  
2.使用视图
    select * from 视图名称;
3.更新视图
    alter view 视图名称 AS SQL语句
4. 删除视图
    drop view ren_view;

27：Mysql之触发器　　create trigger　　　　监视某种情况，然后做出相应的某种动作，Mysql触发器帮助完成对应的操作，减少程序员的sql操作过程

　　参考文档：　　https://www.cnblogs.com/wangfengming/articles/7883974.html#label2

触发器：监视某种情况，根据情况触发某种动作
触发器创建语法四要素：
　　1.监视地点(table)：　　给哪张表加触发器
　　2.监视事件(insert/update/delete)　　有了什么情况才触发这个触发器
　　3.触发时间(after/before)　　　　前置和后置，触发器在sql前执行还是sql后执行
　　4.触发事件(insert/update/delete)　　触发触发器后执行相应的动作，查询不涉及触发器

商品表，订单表
　　购买了商品需要在订单里产生一条订单
没使用触发器之前：假设我们现在卖了3个商品1，我们需要做两件事
　　1：往订单表插入一条记录
　　　　insert into order_table(gid,much) values(1,3);
　　2：新商品表商品1的剩余数量
　　　　update goods set num=num-3 where id=1;
　　这两个动作才是购买商品的完整动作
　　写两个sql麻烦，下订单的时候商品自动减少，使用触发器
触发器语法4要素：
　　1：监视地点：订单表
　　2：减少事件：如果订单表触发了insert操作
　　3：触发时间：前置和后置这里不是很重要
　　4：触发事件：那么商品表数量自动减少对应卖出去的商品数量

创建一个触发器：
create trigger tg1 after insert on order_table 　　　　创建一个触发器，名字tg1，在insert操作之后触发，指定监听order_table表触发操作
for each row 　　　　检测每一行，固定写法
begin 　　　　开启
　　update goods set num = num -3 where id = 1; 
end;　　　　结束，begin和end之间写触发事件的代码，可以学多条sql，多条sql之间分号间隔

重点：
　　对于insert而言，新插入的行用new来表示，行中的每一列的值用new.列名来表示。
　　对于delete而言：原本有一行,后来被删除，想引用被删除的这一行，用old来表示旧表中的值，old.列名可以引用原(旧)表中的值。

查看触发器：
　　触发器是由order订单表进行操作触发的，
　　1：navicate——>选择order订单表点击 设计表——>点击触发器就可以查看了
　　2：命令查看触发器　　
　　　　SHOW TRIGGERS;

删除触发器：
DROP TRIGGER tg1; 

触发器完整写法：
　　new：表示新增的值，没插入表之前的新值
　　old：表中的值
监听插入事件的触发器：
create trigger tg1 after insert on order_table 
for each row 
begin 
　　update goods set num = num -new.much where id = new.gid; 
end;
上面就是一个完整触发器，new表示新的，表示监听语句新增的字段
然后执行下面的insert语句
　　INSERT INTO order_table(gid, much) VALUES(2,5)
执行上面的insert语句会触发触发器里面的事件，会操作goods商品表修改商品数量

监视update修改更新操作的触发器
假设商品下单之后马上又要退货：
正常不使用触发器使用sql语句如下
　　1：订单表的购买数量修改，比如买3件退2件，那么订单表的订单记录由3改为1
　　　　UPDATE order_table set much = much - 2 WHERE oid = 1;
　　2：商品表商品总数量需要加上2
　　　　UPDATE goods set nun=num+2 WHERE id=1;

使用触发器来实现
　　1：编写触发器
　　　　CREATE TRIGGER tg2 AFTER UPDATE on order_table 
　　　　for EACH ROW 
　　　　BEGIN 
　　　　UPDATE goods set num=num+old.much-new.much WHERE id=old.gid; 
　　　　END
　　2：然后退货调用update语句操作order表，就不需要操作goods商品表了，自动触发触发器修改goods商品表
　　　　UPDATE order_table set much = much - 2 WHERE oid = 1;
　　　　　　这样修改order订单表的下单数量的时候也会修改goods表里商品剩余数量

监视delete删除事件的触发器
完全退货的话需要把订单记录删除掉，订单的商品数量还给商品
　　CREATE TRIGGER tg5 AFTER DELETE on order_table 
　　for EACH ROW 
　　BEGIN 
　　UPDATE goods set num=num+old.much WHERE id=old.gid; 
　　END
　　创建一个触发器tg5，当order_table订单表deleter删除数据的时候触发goods商品表的update修改操作
现在进行order订单表的delete操作就会自动修改goods订单表
　　DELETE FROM order_table WHERE oid=1;

触发器只有update，delete，insert这三种动作和查询无法，查询和表数据无关

 28：Mysql之存储过程　　procedure　　　　一堆sql语句封装成一个函数的东西，类似python定义函数，后面使用sql直接调用存储过程就行了

　　参考文档：https://www.cnblogs.com/wangfengming/articles/7883974.html#label3

存储过程：
　　类似于函数(方法),简单的说存储过程是为了完成某个数据库中的特定功能而编写的语句集合，该语句集包括SQL语句（对数据的增删改查）、条件语句和循环语句等
　　只有第一次写完存储过程优先编译的，调用的时候直接使用不用花费实际去编译了，执行效率比写多条sql快很多

创建存储过程和调用存储过程
一：创建一个简单的存储过程
体会封装：封装，多条sql语句写一起
create procedure p1 () 
begin 
    INSERT into goods VALUES(null, "傻逼",100); 
    select * from goods; 
end

2：使用call调用存储过程
CALL p1();

二：存储过程：SQL语句体会参数
1：in参数：表示参数只能传进来，不能拿走，只能在存储过程里使用


2：out参数：参数传进来之后可以往外出的，执行最后把结果拿出来


3：inout参数：出入参数，默认不给的话是in入参
    create procedure p2(IN i int, INOUT names VARCHAR(50)) 
    begin 
        UPDATE goods set name=names WHERE id=i;
        SELECT names;
    end
inout:既是入参又是出参，需要set声明一个变量来接收      
i：是一个入参
SET @names = "李二狗";
call p2(4, @names);
SELECT @names;    出入参数还可以在外部执行变量。出入形式的，执行完还可以在外部直接拿到  

select xx into xxx的使用：into 关键字 可以 将前面字段的查询结果 执行 给 into 后面的变量
　　into查询的时候不可以多个字段赋给多个变量，只能一次赋值一个
set @i=0;
SELECT num INTO @i FROM goods WHERE id=1;　　　　把查询出来的值赋值给i就不会运行出来一个展示的结果
SELECT @i;　　　　i被赋值成10了。into就是把查询出来的num值赋值给i

三：SQL存储过程体会控制：加if等语句，存储过程的控制语句
CREATE PROCEDURE p3(in flag char(5), in nums int)
BEGIN
    if flag = "true" THEN
        SELECT * FROM goods WHERE num<nums;
    ELSEIF    flag = "false" THEN
        SELECT * FROM goods WHERE num>nums;
    ELSE
        SELECT * FROM goods;
    END if;
END

CALL p3("true", 20);
CALL p3("zzzzz", 20);
CALL p3("false", 20);

if语句的用法end if结束if判断

四：sql存储过程体会循环
　　需求：计算1-100累加的和,并且返回计算结果.
　　DECLARE：声明一个变量　　DECLARE i int DEFAULT 0　　声明一个变量i，int类型，给个默认值是0
　　　　DECLARE sum int;　　set sum=0;　　声明一个sum变量，如果不想declare里面设置默认值，使用set也可以设置默认值
　　　　set只是设置，declare声明
CREATE PROCEDURE p4(in n INT, OUT he int)
BEGIN
    DECLARE i int DEFAULT 0;
    DECLARE sum int;
    set sum=0;
    WHILE i <= n DO
        set sum = sum + i;
        set i = i + 1;
    END WHILE;
    set he = sum;
END

set @he = 0;    --出参的值需要先声明成变量，然后变量传到调用函数里面去，就能拿到出参的值
CALL p4(100, @he);
SELECT @he;


create procedure p4(inout n int)
begin
      DECLARE sum int default 0; -- 设置总和变量,并且指定初始值0
      declare i int; -- 声明变量
      set i = 0;    -- 通过set为变量设置值
　　  while i<=n DO  -- 开始循环
            set sum = sum +i;
            set i = i+1;
      end while; -- 结束循环
　　  select sum; -- 提供结果           
     set n = sum;--将计算结果提供给 输出变量 n;
end;

1. 查看现有的存储过程
    show procedure status;
2 .删除存储过程
    drop procedure 存储过程名称;
3. 调用 存储过程
    call 存储过程名称(参数入/出类型 参数名 数据类型);

存储过程优点：
　　1、存储过程增强了SQL语言灵活性。
　　　　存储过程可以使用控制语句编写，可以完成复杂的判断和较复杂的运算，有很强的灵活性；
　　2、减少网络流量，降低了网络负载。
　　　　存储过程在服务器端创建成功后，只需要调用该存储过程即可，而传统的做法是每次都将大量的SQL语句通过网络发送至数据库服务器端然后再执行
　　3、存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译。
　　　　一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
存储过程缺点：     
　　1、扩展功能不方便
　　2、不便于系统后期维护

29：Mysql函数　　

　　参考文档：　　https://www.cnblogs.com/wangfengming/articles/7883974.html#label4

　　官方函数查询网址：　　https://dev.mysql.com/doc/refman/5.7/en/functions.html

常用的mysql内建函数：
一、数学函数
    ROUND(x,y)
        返回参数x的四舍五入的有y位小数的值        
    RAND()
        返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
　　　　　伪随机数，实现把0-100以内所有随机数都订好了，rand(10)可以一直取订好的数的第10个，rand(10)变化不了一直都是0.65xxx，伪随机数二、聚合函数(常用于GROUP BY从句的SELECT查询中)
    AVG(col)返回指定列的平均值
    COUNT(col)返回指定列中非NULL值的个数
    MIN(col)返回指定列的最小值
    MAX(col)返回指定列的最大值
    SUM(col)返回指定列的所有值之和
    GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果三、字符串函数
　　CHAR_LENGTH(str)
　　　　SELECT CHAR_LENGTH("李二狗");　　输出8，字符为单位
　　　　SELECT LENGTH("李二狗");　　输出9，字节为单位，中文3个字节
　　　　返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符
　　CONCAT(str1,str2,...)
　　　　SELECT CONCAT("aaa", "bbb","ccc");　　输出aaabbbccc，返回连接好的字符串
　　　　SELECT CONCAT("aaa", "bbb","ccc", NULL);　　返回Null，有Null之间返回Null，不能有空值，而concat_ws无所谓，可以有Null
　　　　字符串拼接
　　　　如有任何一个参数为NULL ，则返回值为 NULL。
　　CONCAT_WS(separator,str1,str2,...)　　多了个连接符号，可以自己指定多个字符串之间的连接符
　　　　SELECT CONCAT_WS("--","aaa","bbb","ccc")　　　　输出aaa--bbb--ccc
　　　　SELECT CONCAT_WS("--","aaa","bbb","ccc",NULL)　　输出aaa--bbb--ccc　　null不管直接跳过
　　　　字符串拼接（自定义连接符）
　　　　CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
    FORMAT(X,D)　　数字类型的格式化
        将数字 X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。
        例如：
            SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
　　INSERT(str,pos,len,newstr)
　　　　在str的指定位置插入字符串
　　　　　　str：要替换位置的字符串
　　　　　　pos：要替换位置真实位置
　　　　　　len：替换的长度
　　　　　　newstr：新字符串
　　　　例如:
　　　　　　SELECT INSERT('abcd',1,2,'tt'); 结果为: 'ttcd'　　1：从1开始替换，替换2位，ab换成tt
　　　　　　SELECT INSERT('abcd',1,4,'tt'); 结果为: 'tt'　　从1开始替换4位，只给了两个值，后面2个使用空格来代替了
　　　　特别的：
　　　　　　如果pos超过原字符串长度，则返回原字符串
　　　　　　如果len超过原字符串长度，则由新字符串完全替换
　　INSTR(str,substr)
　　　　返回字符串 str 中子字符串的第一个出现位置。
　　　　SELECT INSTR("abdcef", "d")　　d出现的位置  3
　　LEFT(str,len)
　　　　返回字符串str 从开始的len位置的子序列字符。
　　　　例如:
　　　　　　SELECT LEFT("abdcef", 2)　　输出：ab
　　LOWER(str)
　　　　变小写
　　UPPER(str)
　　　　变大写
　　REVERSE(str)
　　　　返回字符串 str ，顺序和字符顺序相反。
　　　　例如:
　　　　　　SELECT REVERSE('1234567') 结果为：7654321
　　SUBSTRING(str,pos) ：截取。
　　　　SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
　　　　不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。
　　　　带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 
　　　　使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。
　　　　假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。
　　　　在以下格式的函数中可以对pos 使用一个负值。
　　　　mysql> SELECT SUBSTRING('Quadratically',5); -- 从第5位开始截取　　'ratically'
　　　　mysql> SELECT SUBSTRING('foobarbar' FROM 4); -- 从第4位开始截取　　'barbar'
　　　　mysql> SELECT SUBSTRING('Quadratically',5,6); --从第5位开始截取,截取6个长度　　'ratica'
　　　　mysql> SELECT SUBSTRING('Sakila', -3);    -- 从倒数第3位开始截取　　　　'ila'
　　　　mysql> SELECT SUBSTRING('Sakila', -5, 3); -- 从倒数第5位开始截取,截取3个长度　　'aki'
            
四、日期和时间函数
    CURDATE()或CURRENT_DATE() 返回当前的日期
    CURTIME()或CURRENT_TIME() 返回当前的时间
    DAYOFWEEK(date)   返回date所代表的一星期中的第几天(1~7)
    DAYOFMONTH(date)  返回date是一个月的第几天(1~31)
    DAYOFYEAR(date)   返回date是一年的第几天(1~366)
    DAYNAME(date)   返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
    FROM_UNIXTIME(ts,fmt)  根据指定的fmt格式，格式化UNIX时间戳ts
    HOUR(time)   返回time的小时值(0~23)
    MINUTE(time)   返回time的分钟值(0~59)
    MONTH(date)   返回date的月份值(1~12)
    MONTHNAME(date)   返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
    NOW()    　　返回当前的日期和时间，这个使用的比较多
    QUARTER(date)   返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
    WEEK(date)   返回日期date为一年中第几周(0~53)
    YEAR(date)   返回日期date的年份(1000~9999)
    
　　重点:
　　DATE_FORMAT(date,format) 根据format字符串格式化date值，时间给的可能是个字符串
　　　　%Y:年　　%M:月　　%W：星期　　%D：天　　%H：时　　%i：分　　%s：秒　　%a：上午下午
　　mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');　　　　　　　　'Sunday October 2009'
　　mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');　　　　　　　　'22:23:00'
　　mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00','%D %y %a %d %m %b %j');　　'4th 00 Thu 04 10 Oct 277'
　　mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00','%H %k %I %r %T %S %w');　　'22 22 10 10:23:00 PM 22:23:00 00 6'
　　mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');　　　　　　　　　　　　　　　　'1998 52'
　　mysql> SELECT DATE_FORMAT('2006-06-00', '%d');　　　　　　　　　　　　　　　　　　 '00'
        
五、加密函数
　　MD5()   　　md5可以反序列的可以把结果拿到，拿到密文通过工具拿到明文
　　　　计算字符串str的MD5校验和
　　　　例如:
　　　　SELECT MD5('1234') 结果为：81dc9bdb52d04dc20036dbd8313ed055
    PASSWORD(str)  　　password是不可逆的，拿到密文得不到明文的
　　　　返回字符串str的加密版本，这个加密过程是不可逆转的
　　　　例如:
　　　　　　SELECT PASSWORD('1234') 结果为:*A4B6157319038724E3560894F7F932C8886EBFCF
        
六、控制流函数            
　　CASE WHEN[test1] THEN [result1]...ELSE [default] END
　　　　如果testN是真，则返回resultN，否则返回default
　　CASE [test] WHEN[val1] THEN [result]...ELSE [default]END  
　　　　如果test和valN相等，则返回resultN，否则返回default
　　IF(test,t,f)   
　　　　如果test是真，返回t；否则返回f
　　IFNULL(arg1,arg2) 
　　　　如果arg1不是空，返回arg1，否则返回arg2
　　　　例如:
　　　　　　SELECT IFNULL('bbb','abc'); 结果为: bbb
　　　　　　SELECT IFNULL(null,'abc');  结果为: abc
　　NULLIF(arg1,arg2) 
　　　　如果arg1=arg2返回NULL；否则返回arg1
　　　　例如:
　　　　　　SELECT NULLIF('bbb','bbb');结果为： null
　　　　　　SELECT NULLIF('aaa','bbb');结果为： aaa

Mysql自定义函数：CREATE FUNCTION
需求：给两个值求和
1、定义自定义函数
CREATE FUNCTION fun1(i1 int,i2 int)
RETURNS INT //设置返回类型
BEGIN
    DECLARE sum int default 0;
    set sum = i1+i2;
    RETURN(sum); //返回结果
end

2.调用自定义函数
　　#直接调用自定义函数
　　　　select fun1(1,5);
　　#在sql语句中使用自定义函数
　　　　select fun1(参数1,参数2),name from 表名
　　　　SELECT g.*,fun1(g.num, 100) FROM goods g;

3.删除自定义函数
　　DROP FUNCTION fun_name;

　　函数与存储过程的区别：

 30：Mysql事务　　　　

　　参考文档：　　https://www.cnblogs.com/wangfengming/articles/7883974.html#label5

 一、 什么是事务
　　   一组sql语句批量执行,要么全部执行成功,要么全部执行失败

二、为什么出现这种技术
　　为什么要使用事务这个技术呢？ 
　　　　现在的很多软件都是多用户，多程序，多线程的，对同一个表可能同时有很多人在用，为保持数据的一致性，所以提出了事务的概念。这样很抽象，
　　举个例子： 
　　　　A 给B 要划钱，A 的账户-1000元， B 的账户就要+1000元，这两个update 语句必须作为一个整体来执行，不然A 扣钱了，B 没有加钱这种情况很难处理。

三、事物的特性　
　　80年代中国人结婚四大件：手表、自行车、缝纫机、收音机（三转一响）。
　　要把事务娶回家同样需要四大件，所以事务很刻薄（ACID），
　　四大件清单：
　　　　原子性(Atom）、
　　　　一致性（Consistent）、
　　　　隔离性（Isolate）、
　　　　持久性（Durable）。
　　ACID就是数据库事务正确执行的四个特性的缩写。
　　原子性：要么不谈，要谈就要结婚！
　　　　对于其数据修改，要么全都执行，要么全都不执行。所有的sql组合在一起全都成功，作为一个整体单独的原子不能再拆分了，整个事务不能再拆开
　　一致性：恋爱时，什么方式爱我；结婚后还得什么方式爱我；
　　　　数据库原来有什么样的约束，事务执行之后还需要存在这样的约束，所有规则都必须应用于事务的修改，以保持所有数据的完整性。
　　隔离性：闹完洞房后，是俩人的私事。
　　　　一个事务不能知道另外一个事务的执行情况（中间状态），两个事务不能重叠，我在做的时候你不能做，不能相交叉，隔离开的
　　持久性：一旦领了结婚证，无法后悔。
　　　　即使出现致命的系统故障也将一直保持。不要告诉我系统说commit成功了，回头电话告诉我，服务器机房断电了，我的事务涉及到的数据修改可能没有进入数据库。

事务的隔离性：
　　一个端运行事务执行下面的语句：
　　　　START TRANSACTION; UPDATE account set money=money-1000 WHERE name="后羿"; UPDATE account set money=money+1000 WHERE name="鲁班";
　　又开一个端运行事务执行下面的语句：
　　　　START TRANSACTION; UPDATE account set money=money-1000 WHERE name="后羿"; UPDATE account set money=money+1000 WHERE name="鲁班";
　　第二次执行事务的时候阻塞住了，是因为操作的account表在事务之内，正在执行事务，而这时候第二次端也要执行这个account表的事务，第一次事务正在执行事务操作
　　　　有隔离性，第二次不能操作表里面的数据，不能同时执行，等第一次执行完第二次再执行
　　两个事务不能交叉，一个就是一个的
事务的持久性：
　　一旦commit提交也好或者rollback回滚也好真实反应给物理区就不能再改变了
　　即使出现致命的系统故障也将一直保持。不要告诉我系统说commit成功了，回头电话告诉我，服务器机房断电了，我的事务涉及到的数据修改可能没有进入数据库。

 另外需要注意: 　　
　　在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务，mysql默认存储引擎就是innodb 　　
　　事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。 　　
　　事务用来管理 insert,update,delete 语句

四、事务控制语句：
　　BEGIN 或 START TRANSACTION；显式地开启一个事务；
　　COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；
　　　　　　　　commit提交的时候会把缓冲区的数据也清空
　　ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
　　　　　　　　从缓冲区把执行的结果清除掉，相当于什么都没有做
　　SAVEPOINT : 保存点,可以把一个事物分割成几部分.在执行ROLLBACK 时 可以指定在什么位置上进行回滚操作.
　　　　　　　　rollback回滚会把整条事务全部回滚，如果一个事务多条sql，第一条sql运行了，第二条也运行了，但是第一条我想留下，只想回滚第二条
注意: 
　　SET AUTOCOMMIT=0 ;禁止自动提交那么就需要手动去提交了 和 SET AUTOCOMMIT=1 开启自动提交.默认为1开启自动提交，
　　　　　　　　　　　　这个和begin和start transacthion作用是一样的，名称不同而已

savepoint的使用：
　　START TRANSACTION; 
　　　　UPDATE account set money=money-1000 WHERE name="后羿"; 
　　　　SAVEPOINT sa1;
　　UPDATE account set money=money+1000 WHERE name="鲁班";
　　ROLLBACK to sa1;
运行上面的语句：
　　第一条会commit提交给物理区执行
　　rollback执行完毕之后会自动再执行一次commit，把上面的commit提交，下面的回滚掉
　　一个事务拆成好几段，可以设置多个保存点，

mysql：
　　提交方式默认提交，每一条sql遇到分号之后就自动提交了，使用事务有三种方式
　　1：打开事务就自动关闭mysql的自动提交，需要手动提交所有sql的执行结果　　commit提交

需求：
    人员表中后羿给鲁班转1000块钱，需要两条sql语句，转账先减再加
　　　　UPDATE account set money=money-1000 WHERE name="后羿";
　　　　UPDATE account set money=money+1000 WHERE name="鲁班";
　　两台sql语句也存在执行语句的延迟，有可能执行第一条后就断电了，扣了后羿1000，但是鲁班没有涨1000
　　少了1000，一般情况是日志查询，
　　　　1：可能断电  
　　　　2：可能是第二条sql语句写错了
　　所以理论上如果断电或者第二条sql写错了，理论上要把扣的1000还给后羿----这就使用到事务了
事务：
　　多条sql作为一组sql，要求事务之内的sql要么通通执行成功，要么统统执行失败　　----这就是事务，
　　比如转账需要使用到sql，比如前3条成功了，后3条失败了，数据的回滚比较麻烦，需要成功的都改回来，
　　交给事务来处理
事务的提交：
　　mysql每执行完一条sql，就向数据库反应一次，
　　数据库分为两部分
　　　　第一部分：物理区
　　　　第二部分：缓冲区
　　实际上执行sql的时候实际上是在缓冲区，执行一行sql完毕没有错才会从缓冲区把结果反应给物理区
　　mysql默认执行一条sql遇到一个分号没有问题就提交给物理区，提交给物理区就是真实改变的，不可逆的
　　所以想让多条sql成功都成功，失败一条都失败，不能让sql语句执行一条就马上提交给物理区
　　而是执行多条语句最后根据最终结果再来提交----start开启事务

START TRANSACTION来开启事务
start transaction; -- 开启事务，关闭mysql自己的自动提交方式，仅限于这个窗口开启了一个事务
 --  执行sql语句操作
　　update account set money = money - 500 where id =1;  
　　update account set money = money+500 where id = 2;
commit;  -- 手动提交事物
rollback; -- 回滚事物，一个窗口提交多次实际，rollback就返回一次然后提交全部的事务
 
--  查看结果，拿的缓冲区的数据，以为成功了其实在物理区没有成功的
select * from account;

commit：提交事务关键字
　　START TRANSACTION; 
　　　　UPDATE account set money=money-1000 WHERE name="后羿"; 
　　　　UPDATE account set money=money+1000 WHERE name="鲁班";
　　如下我只写这条sql语句没有加commt提交事务，这个语句没有commit就不会在物理区执行生效
　　这两条sql没有问题，实际上只是在缓冲区执行成功了，没有反应给物理区
　　关闭自动提交之后需要手动使用commit提交事务
　　如果上面事务中有一条sql语句写错了就不会提交任务反应给物理区域，成功都成功，一条失败都失败----这就是事务

rollback：回滚关键字
　　START TRANSACTION; 
　　　　UPDATE account set money=money-1000 WHERE name="后羿"; 
　　　　UPDATE account set money=money+1000 WHERE name="鲁班";
　　先执行上面三条语句，现在语句只是操作缓冲区，没有实际作用在物理区域

　　SELECT * FROM account;　　这个查询缓冲区account的内容，因为上面的sql语句已经操作了缓冲区，所以缓冲区和实际物理区域的数据不同
　　　　　　　　　　　　　　　　　　拿的缓冲区的数据，在物理区没有成功的
　　ROLLBACK　　　　回滚缓冲区的数据，
　　SELECT * FROM account;　　rollback回滚后查询缓冲区的数据就和物理区域的数据相同了

31：Mysql锁　　悲观锁　　乐观锁

　　参考文档：　　https://www.cnblogs.com/wangfengming/articles/7883974.html#label6

并发访问同一资源需要加锁，数据库也有这个不安全的并发问题
需求：有一个账户,两个人在同一时间要对此账户操作,A要对账户充值100块,B要从账户中取出100块.操作前都要先看一下账户的 余额然后再操作.

窗口1 用户进行充值
-- 充值前 先查看余额
set @m=0;
SELECT money into @m from account where id = 1;
select @m;
-- 看到余额后 充值100 块
update account set money = @m + 100 where id = 1;
SELECT * from account;

窗口2 用户进行取款
-- 取款前 先查看余额
set @m=0;
SELECT money into @m from account where id = 1;
select @m;
-- 看到余额后 取款100 块
update account set money = @m - 100 where id = 1;
SELECT * from account;

假设窗口1拿到余额1000的同时窗口2也拿到1000，然后窗口1充钱100变成1100余额update到account表
这时候窗口2正好也取钱1000-100变成900然后啊余额900 update写入account表里，最后余额变成900了，本来应该是1000的
　　---这就是不安全的并发，窗口2获取的是历史余额，不是最新的，这种问题需要加锁来解决

1. 锁的基本概念
　　当并发事务同时访问一个资源时，有可能导致数据不一致，因此需要一种机制来将数据访问顺序化，以保证数据库数据的一致性。

2. 锁的基本类型
　　多个事务同时读取一个对象的时候，是不会有冲突的。同时读和写，或者同时写才会产生冲突。因此为了提高数据库的并发性能，通常会定义两种锁：共享锁和排它锁。
　　2.1 共享锁（Shared Lock，也叫S锁）
　　　　共享锁(S)表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。（如果试衣间的门还没被锁上，顾客都能够同时进去参观）
　　2.2 排他锁(Exclusive Lock，也叫X锁)：这个就是悲观锁
　　　　排他锁(X)表示对数据进行写操作。如果一个事务对 对象加了排他锁，其他事务就不能再给它加任何锁了。
　　　　(某个顾客把试衣间从里面反锁了，其他顾客想要使用这个试衣间，就只有等待锁从里面给打开了)

3. 实际开发中常见的两种锁:悲观锁
　　悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，
　　　　　　所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block(阻塞)直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制.

注意：要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，所有使用悲观锁必须开启一个事务，没有事务for update不生效了
　　 因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。关闭自动提交命令为：set autocommit=0;
for update就是给查询加一把锁：悲观锁写法就是在查询语句的后面加一个：for update

设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：
-- 0.开始事务
start transaction;
-- 1.查询账户余额
set @m = 0; -- 账户余额
select money into @m from account where id = 1 for update;
select @m;
-- 2.修改账户余额
update account set money = @m -100 where id = 1;
select * FROM account where id = 1;
-- 3. 提交事务
commit; 

在另外的查询页面执行:
-- 0.开始事务
start transaction;
-- 1.查询账户余额
set @m = 0; -- 账户余额
select money into @m from account where id = 1 for update;
select @m;
-- 2.修改账户余额
update account set money = @m +100 where id = 1;
select * FROM account where id = 1;
-- 3. 提交事务
commit;
会发现当前查询会进入到等待状态,不会显示出数据,当上面的sql执行完毕提交事物后,当前sql才会显示结果.

上面：
　　SELECT money into @m from account where id = 1 FOR UPDATE;
　　悲观锁使用id作为一个条件，是锁住一条数据，一行数据
　　如果表中没有设置主键，那么就锁住整张表，

如下：
端1运行如下sql：
　　start transaction;
　　set @m = 0; 
　　select money into @m from account where id = 1 for update;
　　select @m;
　　这端暂时不不允许commit提交这个事务
端2允运行如下sql：
　　start transaction;
　　SELECT * FROM account WHERE id=1 FOR UPDATE;
　　端1运行的时候id=1的数据是锁住的，需要commit提交任务才会解锁，所有端2想select查询id=1的数据会阻塞住，等端1 commit解锁后端2才能select查询到
端3运行如下sql：
　　start transaction;
　　SELECT * FROM account WHERE id=2 FOR UPDATE;
　　这个可以查看，因为端1锁住的只有id=1的数据，id=2的数据没有被锁住可以select查看
　　for update是锁数据的

现在如果把account表的主键去掉：
端1运行：
　　start transaction;
　　set @m = 0;
　　select money into @m from account where id = 1 for update;
　　select @m;
端2运行：
　　start transaction;
　　SELECT * FROM account WHERE id=2 FOR UPDATE;
　　现在account表id不是主键了，所以不管查询id=1或者id=2的都会阻塞了，因为锁住的是整个表
　　用悲观锁一般需要主键，不然锁表，锁表效率很低
　　这时候可以使用普通的事务查询数据：start transaction;　　SELECT * FROM account WHERE id=2;
　　　　这样查询没有锁的效果就是普通查询，不会阻塞，就可能发送不安全的并发

注意1：在使用悲观锁时,如果表中没有指定主键,则会进行锁表操作，加悲观锁一定看看表有没有主键，
注意2: 悲观锁的确保了数据的安全性，在数据被操作的时候锁定数据不被访问，但是这样会带来很大的性能问题。因此悲观锁在实际开发中使用是相对比较少的。　

3. 实际开发中常见的两种锁：乐观锁
乐观锁：顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，
　　　　但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。　

使用乐观锁的两种方式:
　　1.使用数据版本（Version）记录机制实现：
　　　　这是乐观锁最常用的一种实现 方式。何谓数据版本？
　　　　即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。
　　　　当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。
　　　　当我们提交更新的时候，判断数据库表对应记录 的当前版本信息与第一次取出来的version值进行比对，
　　　　如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。
-- 1.查询账户余额
set @m = 0; -- 账户余额
select money into @m from account where id = 1 ;
select @m;
-- 2.查询版本号
set @version = 0; -- 版本号
select version into @version from account where id = 1 ;
select @version;
-- 3.修改账户余额
update account set money = @m -100,version=version+1 where id = 1 and version = @version;
select * FROM account where id = 1;

　　2.乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳 （datatime）,
　　和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突
乐观锁不需要开启事务，开启事务会影响到系统的开销，

悲观锁与乐观锁的优缺点:
　　两种锁各有其有点缺点,不能单纯的讲哪个更好.
　　　　乐观锁适用于写入比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，减少系统开销，不需要开事务，也不需要加额外代码，加一个字段就行了
　　　　这和字段名可以随意设置，不一定叫version
　　　　开启事务会影响系统开销
　　　　乐观锁加大了系统的整个吞吐量。
　　　　但如果经常产生冲突，上层应用会不断的进行重试操作，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适.

32：数据库的备份　　https://www.cnblogs.com/wangfengming/articles/7883974.html#label7

1：navicate可视化工具来备份
　　鼠标选择数据库右键——>转存sql文件——>选择结构和数据或者仅结构——>导出一个sql文件选择存储位置
2：恢复数据库文件
　　进入新的数据库——>选择数据库鼠标右键——>运行sql文件——>选择一个sql文件点击开始　

使用mysqldump实现逻辑备份
语法：
    mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
示例：
单库备份
    mysqldump -uroot -p123456 db1 > c:/db1.sql
    mysqldump -uroot -p123456 db1 table1 table2 > c:/db1-table1-table2.sql
多库备份
    mysqldump -uroot -p123456 --databases db1 db2 mysql db3 > c:/db1_db2_mysql_db3.sql
备份所有库
    mysqldump -uroot -p123456 --all-databases > c:/all.sq

恢复逻辑备份
在mysql命令下，用source命令导入备份文件：
    mysql>  USE 数据库名;
    mysql>  source 备份文件.sql;
     注意：只能在cmd界面下执行source命令，不能在mysql工具里面执行source命令，会报错，因为cmd是直接调用mysql.exe来执行命令的。　

 33：Mysql之索引原理和查询优化

　　参考文档：　　https://www.cnblogs.com/wangfengming/articles/8092914.html

索引介绍：
需求:
　　一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，
　　在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。
　　说起加速查询，就不得不提到索引了。

索引:
　　 简单的说,相当于图书的目录,可以帮助用户快速的找到需要的内容.
　　 在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。能够大大提高查询效率。
　　　　特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍.
　　给数据建一个目录，然后查询的时候先查询索引，索引非常少，比较快，通过索引告诉我这些内容的位置，直接跳过去拿到这些内容
　　　　索引就是为了提高查询效率

索引本质：
　　通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，
　　也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

索引方法
1：B+TREE 索引　　mysql默认索引的方法
　　B+树是一种经典的数据结构，由平衡树和二叉查找树结合产生，
　　它是为磁盘或其它直接存取辅助设备而设计的一种平衡查找树，
　　在B+树中，所有的记录节点都是按键值大小顺序存放在同一层的叶节点中，叶节点间用指针相连，
　　构成双向循环链表，非叶节点（根节点、枝节点）只存放键值，不存放实际数据

磁盘块：
　　100条数据，100条数据分成10个存，每一段存10个数据1-10，11-20，21-30...
　　分段存储，每一段用一个磁盘块来存放数据，
　　一个磁盘块需要包含数据项，1-10的10个数字，如果查询数据需要找到对应的磁盘块，然后再来找对应的数据
　　磁盘块顶上存的不是真实的数据，
如下图：
　　5，28，65存储的是数据项，只是类似目录的东西，不是真正的存储数据
　　如下的目录当中想要拿一个29的数据，系统优先读取到一个区域一个区间比如17-35中间包含了29
　　他会先找到这么一个字段块，这个字段块没有29，29比17大比35小，如果比17小，去左边找另外的磁盘块
　　如果数比35大会去找右边的字段块，右边的数字一定比左边大，如果比17大比35小找中间值
　　p1和p2这些黄颜色的被称为指针，指针指向下一个字段快，比如比17大比35小，应该去哪个字段快去找，这个指针就指向另外一个字段块的地址
　　告诉去哪里能找到字段块，比17大35小找的应该是p2指针，p2指针指向磁盘块3，磁盘块3还不是要找的数据，29比26大比30小
　　那么还是找中间指针p2，再往下这一块找到了数据，磁盘块8能找到29，经过3次就找到了
　　40亿的数据如果说查找第40亿条数据不加索引的话一条条去查，一个个对比，需要查询40e次
　　而加了索引利用上面这种树形菜单一次次来比较最少32次就能找到第40e条数据了，效率是不加索引的几千万倍

类似二分查找，还是有一些不同

树形结构里根节点和字节的不存真实数据的，所有的真实数据都存储在最末层的叶节点上，
躯干不存真实数据的，只存储指针和数据项，叶子才会包含真实数据，所以每次找的时候都要找到最末尾才能拿到一个真实数据的

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，
　　每次只能读一块磁盘块，一看是的话就从这里得到一个指针，不是的话释放掉再去拿另外一个磁盘块，每一次只能读取一块
　　位于同一磁盘块中的数据会被一次性读取出来，而不是按需读取。
　　就是写了个select * from xxx id=100，拿到的磁盘块不是就id=100的这一台数据，拿到的是一整个磁盘块，这个磁盘块可能有100，1000条数据
　　然后这1000条加载到内存里，从1000条里筛选出来id=100的那一条数据
　　InnoDB 存储引擎使用页作为数据读取单位，页是磁盘管理的最小单位，默认 page 大小是 16kB
　　　　innodb存储引擎一次能读出16kb大小的磁盘块，
　　如果相让索引高效，那么磁盘块中的数据越多越好，
　　磁盘块假设都只存储100条数据，假设1千万条数据，找1千万条，需要几十次次才能找到第1千万条的数据
　　如果每一个磁盘块装1w条数据，那么只需要读几次就找到了，

　　磁盘块中的数据是越多越好，这样的话而已减少I/O，因为每读一次磁盘块产生一个I/O的操作
　　第一个字段快存储1-1000条数据，第二个字段快存储1000-2000的数据，后面以此类推，
　　如果现在找第2900条的数据，一次只能加载一个字段快，先加载1-1000的没有，加载第二个字段快，1000-2000还是没有
　　2900比2000大，找右测的，2900在字段块2000-3000的字段快中，3次就找到了

　　假设字段快存储的数据很少1-100，100-200，3000条数据找到读到可能需要30次磁盘块，

字段快的大小：
　　每次默认只能读取16kb磁盘块的大小，读取磁盘块数据的时候和数据项有关的
　　索引字段如果小一个磁盘块能加载更多的数据项，如果数据项是一个int类型，
　　int类型占4字节，vachar如果给500占用500个字节，
　　索引建立在int类型上好，int类型上一个磁盘块能加载几千几万条数据

建立索引：，
　　1：尽量选择数据类型比较小的字段来建立索引，,磁盘块可以存储更多的索引，这样能提供查询效率

B True：
　　根节点的值看数据量有多大来合理给出一个根节点，然后一层层去找，
　　b+Tree唯一的问题：就是根节点，枝节点不存数据的，只是存储数据项的一个值，所有的数据项都是以一个数字类型排好序的形式来存储的
　　　　100w条从1到100排好序的，就是1，2，3.....，有序的才能查找，经过几层之后找到最终的一个结构才到叶子节点拿到真实的数据，
　　　　类似树形菜单，一层层点开，在最底层才能找到选择的菜单
　　b+tree索引选择一个尽量字段比较小的，让这个磁盘块能容纳更多的数据，这样也会加快查找效率

索引方法：
2. HASH 索引，hash值是一个算法，也可以作为索引的结构来使用
　　hash就是一种（key=>value）形式的键值对(类似字典形式存储数据，value对应hash算法的出来的一个值),
　　允许多个key对应相同的value，
　　但不允许一个key对应多个value,
　　为某一列或几列建立hash索引，就会利用这一列或几列的值通过一定的算法计算出一个hash值，对应一行或几行数据.   
　　hash索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率.

如上查询一张表得到上面4个数据项,4条数据

假设索引使用hash函数f()：fname的值经过hash算法得到一串数字，数字就是hash索引的key，他的值告诉你这个数据在第几行
　　下次条件是这个名字的时候依旧吧名字算成数字，数字可以排序，数字排序有序的时候找这个值比较快
　　假设查询8784，在一个有序的数字里比较快，找到这个数字对应的value就能知道数据项在第几行
hash索引不能主动的设置为hash索引，这个是mysql自适应的，mysql相加就给加上，不想也没有办法---mysql自适应的hash索引，只能他自己加上
f('Arjen') = 2323
f('Baron') = 7437
f('Peter') = 8784
f('Vadim') = 2458
此时，索引的结构大概如下：

HASH与BTREE比较:
　　hash类型的索引：查询单条快，
　　　　　　　　　　范围查询慢(比如like模糊查询，得到多个结果，得到多个结果需要一个个去拿结果，范围查找hash索引据慢了)
　　btree类型的索引：b+树，层数越多，数据量越大,范围查询和随机查询比hash所有要快（innodb默认索引类型）
　　　　　　　　　　范围查询快，btree在建立索引的时候按照索引的规则吧相同的数据放到同一个磁盘块下，这时候找到这个磁盘块就找到了所有的数据

不同的存储引擎支持的索引类型也不一样
　　InnoDB 支持事务，支持行级别锁定，支持 Btree、Hash 等索引，不支持Full-text 索引；　　innodb存储引擎我们默认使用的就是b+tree索引
　　MyISAM 不支持事务，支持表级别锁定，支持 Btree、Full-text 等索引，不支持 Hash 索引；　　myisam存储引起使用的也是b+tree索引
　　Memory 不支持事务，支持表级别锁定，支持 Btree、Hash 等索引，不支持 Full-text 索引；
　　NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 Btree、Full-text 等索引；
　　Archive 不支持事务，支持表级别锁定，不支持 Btree、Hash、Full-text 等索引；

hash索引：
　　给我一个名称，我帮你转成数字得到一个算法，只有数字才能排序，排好顺序的数字找就很快了，给一堆字符串的名字很难找，没有任何规则可言的
　　数据结构需要有规则可言的，一定需要算出一个数字
　　如上图：fname的字段经过f这个hash算法得到数字，然后排好序了，拍好顺序找这个值的时候找的比较快，找到这个值后面的有个value，
　　value准确告诉我们这条数据在第几行---　　一步到到位找到这条数据了，一步跳到那一行

hash索引和b+tree索引：
　　b+tree索引经过好几层的节点来查找，
　　hash就有个key一个value，只要找到key就能拿到value行
　　hash索引的效率比b+tree索引效率高

 

select  字段1,字段2... from 表1,表2... [where 条件]