MySQL

bin   启动文件
conf  配置文件
lib   公共方法
core  业务逻辑
db    数据库

--------------------分界线-------------------------

数据库的本质
    -- 就是一款基于网络通信的应用程序
    -- 关系型数据库
        ( MySQL, Oracle, db2, access, sql, server)
        -- 1、数据之间彼此有关系或者约束
        -- 2、存储数据的表现形式通常是以表格存储
    -- 非关系型数据库
        -- k , v 键值对 的形式

任何基于网络通信的应用程序底层用的都是 socket

----------------------分界线-----------------------
MySQL:
-- 服务端
    -- 基于 socekt 通信
    -- 收发消息
    -- SQL 语句

-- 客户端
    -- 基于 socekt 通信
    -- 收发消息
    -- SQL 语句

-- 重要概念
    -- 库   》》》 文件夹
    -- 表   》》》 文件
    -- 记录 》》》 文件内的一行行数据

-- 客户端连接服务器完整命令
    mysql -h 127.0.0.1 -P 3306 -u root -p
    mysql -u root -p  （简写）
-- 基本命令：
    show databases; （分号结束语句）
    quit / exit 退出

-- 当你在连接服务端的时候，发现只输入 mysql 也可以进入，这是 “游客” 模式

--------------------分界线------------------------
环境变量配置及系统服务制作
1、如何查看当前具体进程
    tasklist
    tasklist | findstr mysql
2、如何杀死具体进程  （只有在管理员 cmd 窗口下才能成功）
    taskkill /F /PID  PID号

---- 环境变量配置
1、将 mysqld 服务器所在的文件路径添加到 系统环境变量 path里，注意用分号结尾
2、将 mysql 服务端制作成系统服务（开机自启动）
    -- 查看当前计算机的运行进程数
        services,msc
    -- 将mysql 制作成系统服务，在 端口界面 输入
        mysqld --install
    -- 移除mysql系统服务
        mysqld --remove

---- 设置 mysql 密码 （在终端下输入）
mysqladmin -u root -p 原密码 password 新密码

---- 忘记密码怎么办？
    -- mysql 获取用户名和密码的校验功能相当于一个装饰器，
    装饰器装在客户端请求访问的功能上
    -- 我们如果将该装饰器移除，那么 mysql 服务端就不会校验用户名和密码了
        -- 1、先关闭当前 mysql 服务端
        -- 2、命令行的方式启动（让 mysql 跳过用户名和密码验证功能）
        -- 3、mysqld --skip-grant-tables  （跳过验证表直接服务端登录）
        -- 4、mysql -u root -p  直接回车登录
        -- 5、修改当前用户密码：
            update mysql.user set password=password(明文密码) where user="root" and host="localhost"
        -- 6、立刻将修改的数据刷到硬盘
        flush privileges
        -- 7、关闭当前服务端，然后以正常校验表的形式启动
---- 统一编码
    -- ini 结尾的一般都是配置文件（要用ASCII码编写和保存）

---- 数据库（文件夹）的增删改查
    -- 增 ： create database 库名 charset="gbk";  （可以指定编码）
    -- 查：  show databases; （查看所有）
            show create database 自定义的数据库库名;  （查看单个）
    -- 改： alter database 库名 charset="utf8";
    -- 删： drop database 库名;

---- 表（文件夹内部的文件）的增删改查
    （在操作表的时候，需要制定所在的库）
    -- 查当前所在的库的库名： select database();
    -- 切换库： use 库名ABC;  （接下来的所有操作都在ABC这个库下面进行）
    -- 增表： create table t1(id int,name char(4));
    -- 可以绝对路径：create table db1.t1(id int,name char(4));
    -- 查库里一共有多少表：show tables;
    -- 查看指定一张表： show create table t1;
    -- 以表格的形式展示表的内容： describe t1;  (简写： desc t1;)
    -- 改表的名字或者类型：alter table t1 modify name char(16);
    -- 删除一个表： drop table t1;

---- 数据（表里面的内容）增删改查
    -- 增： insert into t1 values (1,"jason"),(2,"egon"),(3,"alex");
    -- 查： select * from t1;
            select name from t1;
    -- 改： update t1 set name="CBA" where id > 1;
    -- 删： delete from t1 where id > 1;
            delete from t1 where name="jason";
    -- 清空：delete from t1
-----------------------分界线-------------------------

MySQL 存储引擎
-- Innodb  5.5版本以上默认
        （比 myisam 更安全）
-- myisam  5.5版本之前默认
        （速度比 Innodb更快）
-- memory  内存引擎（数据全部存放在内存中） 断电时数据就会丢失
-- blackhole  无论存什么，都立刻消失（黑洞）

---- 查看所有引擎（管理员身份）
        show engines;
-------------------分界线------------------
（在记事本里写好四个创建表的语句，一并复制，粘贴到窗口即可）
create table db1.t1(id int) engine=innodb;
create table db1.t2(id int) engine=myisam;
create table db1.t3(id int) engine=memory;
create table db1.t4(id int) engine=blackhole;

（在GUI界面查看文件夹，会看到表）

-- Innodb表：
.frm 后缀的文件是： 表结构
.idb 后缀的文件是： 表数据

-- myisam表：
.frm 后缀的文件是： 表结构
.MYD 后缀的文件是： 表数据
.MYI 后缀的文件是： 索引（index）[类似于书本的目录]

-- memory/blackhole表：
只有 .frm 后缀的文件
------------------分界线-------------------
创建表的完整语法
# -- 语法 --
create table 表名(
    字段1  类型（宽度）  约束条件,
    字段2  类型（宽度）  约束条件,
    字段3  类型（宽度）  约束条件
)
-- 注意：
1、同一张表中字段名不能重复
2、括号内的最后一行不能加逗号
3、字段名和字段类型是必须的
4、宽度和约束条件是可选的（可写，可不写）
    --
    字段1 类型（宽度） 约束条件1 约束条件2 ...
    create table t5(
    id int,
    name char
    );

# -- 宽度 --
    一般情况下指的是对存储数据的限制
    create table t6(name char);    # 默认宽度是 1
    insert into t6 values("jason");
        针对不同的版本会出现不同的效果
        5.6版本默认没有开启严格模式，规定只能存一个字符，而你写入多个字符，mysql会自动截取
        5.7版本及以上默认开启严格模式，一旦超出范围，会报错
    -- 使用数据库的准则：
        尽量少的让数据库干活，不要增加额外工作

# -- 约束条件 --
insert into t7 values(null);  # 可以插入 空
insert into t8 (id int,name char not null);   not null 不可以插入 空

-------- MYSQL 里的数据类型 --------
-- 整型 --
    以 TINYINT 为例：
    create table t9(id tinyint);
    insert into t9 values(-129),(256);
        -- 结论：
        是否有符号：
            默认情况下是带符号的
        超出会如何：
            超出限制只存最大可接受的值
    -- 约束条件：unsigned
    create table t10(id tinyint unsigned);
    insert into t10 values(-1),(256);
    -------- 特例：只有整型 int 括号里面的数字不是表示限制位数
            id int(8)
                如果数字没有超出8位，那么默认用空格填充至8位
                如果数字超过了8位，那么有几位就存几位（但还是遵守最大范围）
            create table t11(id int(8) unsigned zerofill);
            -- 总结：
            针对整型字段，括号内无需指定宽度，因为它默认的宽度已经足以显示所有的数据了
---- 严格模式？
-- 查看：
（ "%mode" --> 模糊匹配/查询 --> 关键字 like --> %：匹配任意多个字符 --> _下划线：匹配任意单个字符）
show variables like "%mode";
-- 修改严格模式：
set session   只在当前窗口有效
set global    全局有效

set global sql_mode = "STRICT_TRANS_TABLES";   # 设置严格模式（退出客户端，重新进入即可）
select char_length(name) from t1;  查询存储数据的长度

-- char 与 varchar
    -- 定长 char(4)：数据超过4个字符直接报错，不够四个字符空格补全
    -- 变长 varchar(4) ：数据超过4个字符直接报错，不够有几个存几个
    ---- 首先可以肯定的是：
            char 硬盘上存的绝对是真正的数据，带有空格的，但是在显示的时候，MySQL会自动删除多余的空格
            如何让MySQL不要做自动剔除空格的操作？
            -- set global sql_mode = "STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH";
            退出当前mysql账户，再次登录就可以看到修改成功了。

            -- 过程：
                create table t1(name char(4));
                create table t2 values(name varchar(4));
                insert into t1 values("ok");
                insert into t2 values("ok");
                set global sql_mode = "STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH";
                quit
                show variables like "%mode";
                select char_length(name) from t1;
                select char_length(name) from t2;

    -- char 与 varchar 对比
        char
            缺点：浪费空间
            优点：直接按照固定的字符存取数据即可
            jason egon alex tank
            存：按照五个字符存，取：也直接按照五个字符取
        varchar
            优点：节省空间
            缺点：存取较为麻烦
            1bytes+jason 1bytes+egon 1bytes+alex 1bytes+tank
            存：需要制作报头，取：需要先读报头，之后才能读取真实数据

---- 时间类型
    date ： 年月日
    datetime ：年月日时分秒
    time ：时分秒
    Year ：年

create table student(
    id int,
    name varchar(16),
    born_year year,
    birth date,
    study_time time,
    reg_time datetime
);
insert into student values(1,"egon","1988","1988-11-11","11:11:11","2020-11-11 11:11:11");


---- 枚举与集合类型
枚举（enum）： 在枚举内多选一    （性别）
集合（set）：  在集合内多选多    （爱好）

    -- 具体使用
create table user(
    id int,
    name char(16),
    gender enum("male","female","others")
);
insert into user values(1,"jason","male");
insert into user values(2,"egon","xxxooo");  # 会报错

-------------------------------------分界线------------------------------------

----约束条件----

-- default 默认值
    # 补充知识点：插入数据的时候可以指定字段

create table t1(
    id int,
    name char(16)
    );
insert into t1(name, id) values("egon",1);

create table t2(
id int,
name char(16),
gender enum("male","female","others") default "male"
);
insert into t2(name, id) values("egon",1);

-- unique唯一
# 单列唯一
create table t3(
id int unique,
name char(16)
);
insert into t3 values("1", "egon"),(2,"jason");
# 联合唯一  （应用场景：ip 不同，端口可以重复； IP + PORT 一起是唯一的）
create table t4(
id int,
ip char(16),
port int,
unique(ip,port)
);

-- primary key主键
    -- 单单从约束效果上来看primary key 等价于 not null + unique（非空且唯一）
    create table t5(id int primary key);
    insert into t5 values(null);    # 报错
    insert into t5 values(1),(1);   # 报错
    insert into t5 values(1),(2);   # 正确
    -- 它除了有约束效果之外，它还是Innodb存储引擎组织数据的依据
    Innodb存储引擎在创建表的时候，必须要有primary key
    因为它类似于书的目录，能够帮助提示查询效率并且也是建表的依据

    # 1
    一张表中有且只有一个主键，如果你没有设置主键，那么会
    从上往下搜索，直到遇到一个非空且唯一的字段将它自动升级为主键
    # 2
    如果表中没有主键也没有其他任何的非空且唯一字段
    那么Innodb会采用自己内部提供的一个隐藏字段作为主键，
    隐藏意味着你无法使用到它，就无法提升查询速度
    # 3
    一张表中通常都应该有一个主键字段，并且通常将id/uid/sid字段（编号）作为主键
    # 联合主键
    （多个字段联合起来作为表的主键，本质还是一个主键）

-- auto_increment 自增 （通常在 主键后面使用）
    当编号特别多的时候，人为地去维护太麻烦，
    像敲代码时行号自动增加那样
    但是，如果你删除了已经存在的数据，主键的自增不会停止，即：
    如果删除了1，2，3行，下次写入的新内容是从 第4行开始的

create table t6(
id int primary key auto_increment,
name char(16)
);
insert into t6(name) values("jason"),("egon"),("tank");

-- 注意：
delete from t6;     在删除表中数据的时候，主键的自增不会停止
truncate t6;        清空表内数据并且重置主键

----------------------------分界线------------------------------
---- 外键
    （外键就是用来帮助我们建立表与表之间的关系的）
    foreign key
        1、一对多关系，外键在 多方（员工表）
        2、先建立被关联表（部门表 --> 外键不在的那个表）
        3、录入数据，先录入到被关联表（部门表 --> 外键不在的那个表）

---- 表关系
    （一对多，多对多，一对一，没有关系）

    --------------------//-------------------
    ** 一对多 关系
    站在员工的角度：
        一个员工可不可以对应多个部门？  不能！
    站在部门的角度：
        一个部门可不可以对应多个员工？  可以！
    --------------------//-------------------

    # SQL语句建立表关系

    # 员工表
    create table dep(
    id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
    );

    # 部门表
    create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum("male","female","others") default "male",
    dep_id int,
    foreign key(dep_id) references dep(id)
    );

    insert into dep(dep_name,dep_desc) values("vip教学部","教书育人"),
    ("外交部","喝茶聊天"),("nb技术部","技术能力有限部门");
    insert into emp(name,dep_id) values("jason",2),("egon",1),("tank",1),("kevin",3);

---- 错误的删改方法 ----
    # 修改 emp 里面的 dep_id 字段或者 dep 表里面的 id 字段
    update dep set id=200 where id=2;  # 会报错
    # 删除 dep 表里面的数据
    delete from dep where id=1;  # 会报错
---- 正确的删改方法 ----
    方法一：先删改员工表，再删改部门表
    方法二：同步删改 （--> 级联更新，级联删除 --> 在创建表的时候加条件）
        foreign key(dep_id) references dep(id) on update cascade on delete cascade

    # 员工表
    create table dep(
    id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
    );

    # 部门表
    create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum("male","female","others") default "male",
    dep_id int,
    foreign key(dep_id) references dep(id) on update cascade on delete cascade
    );

---- 多对多
    图书表 -- 作者表
    --------------------//-------------------
    先站在书的角度：
        一本书可不可以有多个作者？  可以！
    再站在作者的角度：
        一个作者可不可以写多本书？  可以！
    --------------------//-------------------
    ** 需要单独创建一张 媒介表 来存储 两张表之间的关系

    create table book(
    id int primary key auto_increment,
    title varchar(32),
    price int
    );

    create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int
    );

insert into book(title,price) values("水浒传",123),("三国演义",456),("西游记",789);
insert into author(name,age) values("jason",18),("egon",19),("tank",19);

    create table book2author(
    id int primary key auto_increment,
    author_id int,
    book_id int,
    foreign key(author_id) references author(id)
    on update cascade on delete cascade,
    foreign key(book_id) references book(id)
    on update cascade on delete cascade
    );

insert into book2author(author_id,book_id) values(1,1),(1,2),(2,3);

---- 一对一
    应用场景1：
    查看 QQ 好友时，一般只会显示 昵称和QQ号，如果要查看对方的QQ空间之类的，需要点击更多信息
    所以，当一个表的字段特别多时，而每次查询又不是所有的字段都能用得到，
    这时可以将表一分为二：
        用户表
            -- （用户表）基础信息
                id  name  age
            -- （用户详情）详细信息
                id addr phone hobby email...
    应用场景2：
    客户表和学生表
        在报名之前是客户
        在报名之后是学生（期间有一些客户不会报名）

    --------------------//-------------------
    一对一，外键字段建在任意一方都可以，但是推荐建在查询频率比较高的表中

    create table authordetail(
    id int primary key auto_increment,
    phone int,
    addr varchar(64)
    );

    create table author2(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    authordetail_id int unique,
    foreign key(authordetail_id) references authordetail(id)
    on update cascade on delete cascade
    );

---- 修改表
    ** MySQL 对大小写不敏感
    1、修改表名：
    alter table 表名 rename 新表明;

    2、增加字段
    alter table 表名 add 字段名 字段类型（宽度） 约束条件;
    alter table 表名 add 字段名 字段类型（宽度） 约束条件 first;
    alter table 表名 add 字段名 字段类型（宽度） 约束条件 after 字段名;

    3、删除字段
    alter table 表名 drop 字段名;

    4、修改字段
    alter table 表名 modify 字段名 字段类型（宽度） 约束条件;    # 修改字段类型
    alter table 表名 change 旧字段名 新字段名 字段类型（宽度） 约束条件;

---- 复制表
    ** 我们 sql  语句查询的结果其实是一张虚拟表
    言外之意：针对查询结果还可以用查询表的语句

    create table 表名 select * from 旧表;   --> 这方法不能复制 主键，外键
-----------------------------//-----------------------------

---- 前期表准备

create table emp2(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum("male","female") not null default "male",
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,
depart_id int
);

# age int(3)  表示显示 3位数字
# 插入记录
# 三个部门：教学，销售，运营

insert into emp2
(name,sex,age,hire_date,post,salary,office,depart_id) values
("jason","male",18,"20170301","teacher",7300.12,401,1),
("egon","male",29,"20110822","teacher",12300.56,401,1),
("tank","male",26,"20130601","teacher",10000,401,2),
("jack","male",22,"20130109","sale",6300.90,401,1),
("mike","male",25,"20161209","sale",9000.90,401,2),
("笑笑","female",28,"20171121","sale",5000.50,402,2),
("悠悠","female",26,"20160902","operation",11300.70,403,3),
("呵呵","male",18,"20190125","operation",10300.12,403,3),
("哈哈","male",18,"20150829","operation",12590.50,403,3);

** 当表的字段特别多，可以使用\G分行展示
 select * from emp\G;

---- 几个重要的关键字的执行顺序
    # 书写顺序
    select id,name from emp where id > 3;
    # 执行顺序
    from (emp)
    where (id > 3)
    select (id,name)

-- where 约束条件
    -- 作用：是对整体数据的一个筛选操作

    1、查询 id 大于等于2 小于等于5 的数据
    select id,name from emp2 where id >= 2 and id <= 5;
    select id,name from emp2 where id between 2 and 5;

    2、查询指定薪资
    select * from emp2 where salary=20000 or salary=18000 or salary=17000;
    select * from emp2 where salary in (20000,18000,17000);

    3、查询员工姓名中包含字母a的员工的姓名和薪资
        -- 模糊查询  like
            % 匹配任意多个字符
            _ 匹配任意单个字符

    select name,salary from emp2 where name like "%a%";

    4、查询员工姓名是由 四个字符组成的 姓名和薪资
    select name,salary from emp2 where name like "____";    # 4 个下划线
    select name,salary from emp2 where char_length(name) = 4;

    5、查询 id 小于2 或者 大于 5 的数据
    select id,name from emp2 where id not between 2 and 5;

    6、查询岗位描述为空的员工姓名和岗位名
    select name,post from emp2 where post_comment is NULL;

---- group by 分组
    # 分组实际应用场景
        男女比例 / 部门平均薪资 / 国家之间的数据统计
    1、按照部门分组
    select * from emp2 group by post;

        设置严格模式和分组
        set global sql_mode = "strict_trans_tables,only_full_group_by";
        设置严格模式之后，分组默认只能拿到分组的依据
        select post from emp2 group by post;
        按照什么分组就只能拿到分组，其他字段不能直接获取，需要借助于一些方法（参考进程的队列）--》 聚合函数
            聚合函数 --》 max,min,avg,sum

    """
    什么时候需要分组？
        关键字： 每个，平均，最高，最低
        1、获得每个部门的最高薪资  --> 先按post分组，再匹配最高工资
        select post,max(salary) from emp2 group by post;
            as 可以给字段起别名，不写 as 也行的
            select post as "部门",max(salary) as "最高薪资" from emp2 group by post;
            as 还可以给表写别名
            select * from emp2 as t11;        --> 将 emp2 这个表起别名为 t11
        2、获得每个部门的最低薪资
        select post,min(salary) from emp2 group by post;

        3、获得每个部门的平均薪资
        select post,avg(salary) from emp2 group by post;

        4、获得每个部门的薪资总和
        select post,sum(salary) from emp2 group by post;

        5、获取每个部门的人数 --> count 计数，统计个数，括号里的字段放这些都可以，不能 post_comment，因为那里是 NULL
        select post,count(salary) from emp2 group by post;
        select post,count(id) from emp2 group by post;
        select post,count(age) from emp2 group by post;

        6、查询分组之后的部门名称和每个部门下所有的员工姓名 --> group_concat --> 可以拼接
        select post,group_concat(name) from emp2 group by post;
        select post,group_concat(name,"_CBA") from emp2 group by post;
        select post,group_concat(name,":",salary) from emp2 group by post;
            # concat 不分组的时候用
            select concat("NAME:",name),concat("SAL:",salary) from emp2;
            # concat_ws ：如果多个字段之间的连接符号是相同的
            select concat_ws(":",name,age,sex) from emp2;
        7、查询每个人的年薪（12薪）
        select name,salary*12 from emp2;
    """
-- 分组注意事项
    关键字 where 和 group by 同时出现的时候 group by 必须在 where 的后面
        where 先对整体数据进行过滤之后再分组操作
        聚合函数只能在分组之后使用

    统计各个部门年龄在25岁以上的员工的平均薪资
    1、先求出所有年龄大于25岁的员工
    select * from emp2 where age > 25;
    2、再对结果进行分组
    select * from emp2 where age > 25 group by post;
    3、把 * 星号替换
    select post,avg(salary) from emp2 where age > 25 group by post;

-- having 分组之后的筛选条件
    语法和 where 一致，可以直接使用聚合函数
    -- 统计各个部门年龄在25岁以上的员工的平均薪资，并且保留平均薪资大于1w的部门
    select post,avg(salary) from emp2
        where age>25
        group by post
        having avg(salary)>10000
        ;


-- distinct 去重
    -- 一定要所有数据内容完全一样的数据才可以去重！
    select distinct age from emp2;

-- order by 排序 --> 默认升序 asc， 降序是 desc
    select * from emp2 order by salary;
    select * from emp2 order by salary asc;
    select * from emp2 order by salary desc;
        -- 先按照 age 降序排列，如果碰到 age 相同，则再按照 salary 升序排序
        select * from emp2 order by age desc,salary asc;
        -- 统计各个部门年龄在20岁以上的员工的平均薪资，并且保留平均薪资大于5k的部门，
            然后对平均工资降序排序

            select post,avg(salary) from emp2
            where age>20
            group by post
            having avg(salary)>5000
            order by avg(salary) desc
            ;

-- limit 限制展示条数
    -- 针对数据过多的情况，我们通常都是做分页处理
    select * from emp2 limit 3;    # 只展示三条数据
    select * from emp2 limit 0,5;    # 参数1：起始位置，参数2：条数

-- 正则
    select * from emp2 where name regexp "^j.*(n|y)$";    # 以j开头，n/y结尾

-- 连表操作（拼接）

select * from emp,dep;    # 结果，笛卡尔积
select * from emp,dep where emp.dep_id = dep.id;

MySQL 拼表方法：
    inner join  内连接  ： 只拼接两张表中公有的数据部分
    left join   左连接  ： 左表所有的数据都展示，没有对应的就用 NULL
    right join  右连接  ： 右...
    union       全连接  ： 左右两表的数据都展示出来

# 内连接
select * from emp inner join dep on emp.dep_id = dep.id;
# 全连接 （就是左连接union右连接）
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;

# 子查询
    -- 子查询就是我们平时解决问题的思路
        分步骤：第一步。。。第二步。。。
    MySQL 官方说法：
        将一个查询语句的结果当做另外一个查询语句的条件去用

    例子： （emp 雇员， dep 部门）
        查询部门是技术或者人力资源的员工信息
        1、先获取部门的id号
        2、再去员工表里面筛选出对应的员工
        select id from dep where name="技术" or name="人力资源";
        select name from emp where dep_id in (200,201);

        综上：
        select * from emp where dep_id in (select id from dep where name="技术" or name="人力资源");

-- 在做多表查询的时候，有两种思路：联表和子查询

-- 关键字 exist
    -- 返回 True， False
    返回 True 的时候，外层查询语句执行
    返回 False 的时候，外层查询语句不再执行

    select * from emp where exists
        (select id from dep where id > 3);

    select * from emp where exists
        (select id from dep where id > 300);

-------------------------//-------------------------

---- Navicat 软件  （是一个集成的支持多种数据库的客户端）

---- 视图
    # 固定语法
    create view 表名 as 虚拟表的查询 sql 语句

    # 具体操作
    create view teacher2course as
    select * from teacher inner join course
    on teacher.tid = course.teacher_id
    ;

    # 注意
    1、创建视图在硬盘上只会有表结构，没有表数据（数据还是来自于之前的表）
    2、视图一般只用来查询，里面的数据不要修改，可能会影响真正的表

---- 触发器
    -- 在满足对表数据进行增删改的情况下，自动触发的功能 （可以做监控和日志）
    -- 基本语法结构
        --
create trigger 触发器的名字  before/after    insert/update/delete  on 表名 for each row begin  sql语句  end;

# 具体使用（触发器的名字要见名知意） 例子：
create trigger tri_before_insert_t1  before insert on t1
for each row
begin
sql语句
end;

    -- 临时当前窗口修改 MySQL 默认的语句结束符 （将分号;改为 $）（写触发器需要改结束符）
        delimiter $$
    # 案例

    create table cmd(
        id int primary key auto_increment,
        user char(32),
        priv char(10),
        cmd char(64),
        sub_time datetime,  # 提交时间
        success enum("yes", "no")  # 0 代表执行失败
    );

    create table errlog(
        id int primary key auto_increment,
        err_cmd char(64),
        err_time datetime
    );

    """ 需求：
    当 cmd 表中的记录 success 字段是 no ， 那么就会触发 触发器去 errlog 表中插入数据
    NEW 指代的就是一条条数据对象
    """

    delimiter $$
    create trigger tri_after_insert_cmd after insert on cmd
    for each row
    begin
        if NEW.success = "no" then
        insert into errlog(err_cmd, err_time)
        values(NEW.cmd, NEW.sub_time);
        end if;
    end $$
    delimiter ;

    -------- 以上，触发器安装完成，上面两个表已经被监控了
    -- 准备往 cmd 表 录入数据，测试

    insert into cmd(
        user,
        priv,
        cmd,
        sub_time,
        success
    )
    values
        ("jason", "0755", "ls -l/etc", NOW(), "yes"),
        ("jason", "0755", "cat / etc/password", NOW(), "no"),
        ("jason", "0755", "useradd xxx", NOW(), "no"),
        ("jason", "0755", "ps aux", NOW(), "yes");

    -- 删除触发器
    drop trigger tri_after_insert_cmd;

---- 事务
    -- 什么是事务？
        开启一个事务可以包含多条 sql 语句， 这些 sql 语句要么同时成功
        要么一个都别想成功
        这个特点叫 “ 事务的原子性 ”
    -- 事务的作用：
        保证了对数据操作的安全性
        案例：
        egon用银行卡给jason的支付宝转账100元
            将 egon 银行卡账户的数据减少100元
            将 jason 支付宝账户的数据增加100元


    -- 事务的四大特性  ACID
        -- A 原子性
            一个事务是一个不可分割的单位，事务中包含的诸多操作
            要么同时成功要么同时失败
        -- C 一致性
            事务必须是 使数据库从一个 一致性的状态变到另外一个 一致性的状态
            一致性跟原子性是密切相关的
        -- I 隔离性
            一个事务的执行不能被其他事务干扰
            即 一个事务内部的操作使用 及 使用到的数据
            对并发的其他事务是隔离的，并发执行的事务之间也是互相不干扰
        -- D 持久性
            也叫“永久性”
            一个事务一旦提交成功、执行成功
            那么它对数据库中数据的修改应该是永久的
            接下来的其他操作或者故障不应该对其有任何的影响
            （即 数据 保存到 硬盘）

    -- 如何使用事务？
        -- 事务相关的三个关键字
        1、开启事务
        start transaction;
        2、回滚操作（回到事务执行之前的状态）
        rollback;
        3、确认（确认之后就无法回滚了）
        commit;

        ## 模拟转账功能
        ---- 前期表的准备
        create table user(
            id int primary key auto_increment,
            name char(16),
            balance int
        );

        insert into user(name,balance) values
        ("jason", 1000),("egon",1000),("tank",1000);

        ---- ok

        # 1 开启事务
        start transaction;
        # 2 多条 sql 语句
        update user set balance= 900 where name="jason";
        update user set balance= 1010 where name="egon";
        update user set balance= 1090 where name="tank";
        # 3 回滚
        rollback;
        # 4 再录入数据
        update user set balance= 900 where name="jason";
        update user set balance= 1010 where name="egon";
        update user set balance= 1090 where name="tank";
        # 5 确认
        commit;

---- 存储过程
    存储过程类似于 python 中 的自定义函数
    它的内部包含了一系列可以执行的 sql 语句，存储过程存放于 MySQL 服务端中
    你可以直接通过调用存储过程触发内部 sql 语句的执行

    -- 基本使用

    create procedure 存储过程的名字(形参1,形参2,...)
    begin
        sql 语句
    end

    -- 如何调用？ --> 像调用函数一样
    call 存储过程的名字()

    -- 三种开发模型 （第一种基本不用，一般都用第三种，出现效率问题再自己动手写sql）
        -- 第一种
            应用程序：程序员写代码开发
            MySQL：提前编写好存储过程，供应用程序调用

            好处：开发效率提升了，执行效率也上去了
            缺点：考虑到人为因素、跨部门沟通的问题，后续的存储过程的扩展性差

        -- 第二种
            应用程序：程序员写代码开发之外，涉及到数据库操作也自己动手写
            优点：扩展性很高
            缺点：
                开发效率降低
                编写 sql 语句太过繁琐，而且后续还需要考虑 sql 优化的问题

        -- 第三种
            应用程序：只写程序代码吗，不写 sql 语句
                        基于别人写好的操作 MySQL 的 python 框架直接调用操作即可 （ORM框架）
            优点：开发效率比上面两种情况都要高
            缺点：语句的扩展性差，可能会出现效率低下的问题


        # 存储过程具体演示

        delimiter $$
        create procedure p1(
            in m int,    # 只进不出， m 不能返回出去
            in n int,
            out res int    # 该形参可以返回出去
        )
        begin
            select tname from teacher where tid > m and tid < n;
            set res=123;    # 将 res 变量修改，用来标识当前的存储过程代码确实执行了
        end $$
        delimiter ;

        # 怎么调用？
        -- 针对形参 res 不能直接传数据，应该传一个变量名
        -- 定义变量
        set @ret=10;
        # 查看变量对应的值
        select @ret;
        # 调用
        call p1(1,2,@ret);

        -- 在 pymysql 中如何调用存储过程？--> pymysql有自己的优化
        cursor.callproc("p1", (1,2,10))
        print(cursor.fetchall())

---- 函数
    -- 存储过程是自定义函数，函数就类似于内置函数

---- 流程控制

    -- if 判断 --

delimiter //
create procedure proc_if()
begin
    declare i int default 0;
    if i = 1 then
        select 1;
    elseif i = 2 then
        select 2;
    else
        select 7;
    end if;
end //
delimiter;

    -- while 循环 --

delimiter //
create procedure proc_while()
begin
    declare num int;
    set num = 0;
    while num < 10 do
        select
            num;
        set num = num + 1;
    end while;


---- 索引
    ps: 数据都是存在于 硬盘 上的，查询数据 不可避免地需要进行 IO 操作
    索引：就是一种数据结构，类似于书的目录。
        意味着以后在查询数据的时候应该先找目录，再找数据，
        而不是一页一页地翻书，从而提升查询速度，降低 IO操作

    索引，在 MySQL 中也叫“键”，是存储引擎用于快速查找记录的一种数据结构
        primary key
        unique key
        index key

    注意：foreign key 不是用来加速查询用的，不在我们的研究范围之内
    上面的三种 key，前面两种除了可以增加查询速度之外各自还具有约束条件，
    而最后一种 index key 没有任何的约束条件，只是用来帮助你快递查询数据的

    -- 本质 --
    通过不断地缩小想要的数据范围筛选出最终的结果，同时将随机事件（一页一页地翻）
    变成顺序事件（先找目录，再找数据）
    也就是说，有了索引机制，我们可以总是用一种固定的方式查找数据

    一张表中可以有多个索引（多个目录）

    索引虽然能够帮助你加快查询速度但是也有缺点
    1、当表中有大量数据存在的前提下，创建索引速度会很慢
    2、在索引创建完毕之后，对表的查询性能会大幅度的提升，但是写的性能也会大幅度地降低

    ---- b + 树
        --> 只有叶子节点存放的是真实的数据，其他节点存放的是虚拟数据，仅仅是用来指路的
        --> 树的层级越高查询数据所需要经历的步骤就越多（树有几层查询数据就需要几步）
        --> 一个磁盘块的存储是有限制的
        --> 为什么建议你将 id 字段作为 索引？
            占用空间少，一个磁盘块能够存储的数据多
            那么就降低了树的高度，从而减少了查询次数

    ---- 聚集索引 ( primary key )
    ---- 辅助索引 ( unique, index)
        查询数据的时候不可能一直使用到主键，也有可能会用到 where name/ password =? 等其他字段
        那么这个时候就无法利用聚集索引
        可以根据情况给其他字段设置辅助索引（b+树）
            --
            叶子节点存放的是数据对应的主键值
                先按照辅助索引拿到数据的主键值
                之后还是需要去主键的聚集索引里面查询数据
    ---- 覆盖索引
        -- 在辅助索引的叶子节点就已经拿到了需要的数据

        # 给 name 设置辅助索引
        select name from user where name = "jason";

        # 非覆盖索引
        select age from user where name = "jason"