MySQL学习笔记二---完结篇

20.Union用法
    select ename，job from emp where job='MANAGER'
    union
    select ename，job from emp where job='salesman';
    优点：表连接次数减少，时间复杂度提升
    union把乘法运算变成了加法运算。union在进行结果集合并时，要求两个结果集的列数相同。
    在mysql中两个结果集的列的数据类型可以不一样，但在oracle中不允许。

21、limit（*****）
    作用：将查询结果集的一部分取出来。通常使用在分页查询当中。
    类似百度网页：一页显示10条记录。
    提高用户体验
    完整用法：limit startIndex，length（缺省默认从零开始）
    select ename，sal from emp order by sal desc limit 0,5;//显示前5条记录
    ***优先级：limit在order by后面执行
 分页：limit (pageno-1)*pagesize,pagesize
22、表的创建
        建表的语法格式：creat table 表名（
                        字段名1 数据类型，
                        字段名2 数据类型，
                        字段名3 数据类型
                        ）；
        表名：建议以t_或者tbl_开始，可读性强。
        表名和字段名都属于标识符。
        MySQL中的数据类型：
                                 字符长度
                        varchar（最长255）
                            可变长度字符串：动态分配
                            速度慢
                        char（最长255）
                            定长字符串：不管实际数据据长度多少，分配固定长度空间存储数据
                            速度快
                        int（最长11）
                            Java中int
                        bigint
                            Java中long
                        double
                        float
                        date
                            短日期类型
                        datetime
                            长日期类型
                        clob
                            字符大对象
                            最多可以存储4G的字符串
                            比如：存储一篇文章，一篇说明。
                        blob
                            二进制大对象
                            Binary Large Object
                            专门用来存储图片、声音、视频等流媒体数据。
                            往BLOB类型的字段插入数据时，例如插入图片/视频时，需要使用IO流才行。
        删除表：drop table if exists 表名；

       下面的数据默认
例子：  create table t_student(
            no int,
            name varchar(32),
            sex char(1),
            age int(3),
            email varchar(255)
         );
23、插入数据insert（DML）
    insert into 表名（字段名1，字段名2，字段名3...）values(值一，值二，值三...)
        字段名和值要一一对应。
    mysql> insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@123.com');
    mysql> select *from t_student;
    +------+----------+------+------+------------------+
    | no   | name     | sex  | age  | email            |
    +------+----------+------+------+------------------+
    |    1 | zhangsan | m    |   20 | zhangsan@123.com |
    +------+----------+------+------+------------------+
    1 row in set (0.00 sec)
24、insert插入日期
    数字格式化：format
    format(数字，'格式')
                mysql>  select ename,format(sal,'￥999.9999') from emp;
                +--------+---------------------------+
                | ename  | format(sal,'￥999.9999')  |
                +--------+---------------------------+
                | SMITH  | 800                       |
                | ALLEN  | 1,600                     |
                | WARD   | 1,250                     |
                | JONES  | 2,975                     |
                | MARTIN | 1,250                     |
                | BLAKE  | 2,850                     |
                | CLARK  | 2,450                     |
                | SCOTT  | 3,000                     |
                | KING   | 5,000                     |
                | TURNER | 1,500                     |
                | ADAMS  | 1,100                     |
                | JAMES  | 950                       |
                | FORD   | 3,000                     |
                | MILLER | 1,300                     |
                +--------+---------------------------+
                14 rows in set, 14 warnings (0.00 sec)
    str_to_date:将字符串varchar类型转换成date类型
    date_format：将date类型转换成具有一定格式的varchar字符串类型(select id,name,date_format(birth,'%Y/%m/%d') as birth from t_users);
    注意：数据库中的有一条命名规范：所有的标识符都是全部小写，单词和单词之间使用下划线进行衔接。
    mysql日期格式：
                %Y 年
                %m 月
                %d 日
                %h 时
                %i 分
                %s 秒
    Java：yyyy-MM-dd HH:mm:ss SSS
            drop table if exists t_users;
            create table t_users(
                id int,
                name varchar(32),
                birth date
            );
             insert into t_users(id,name,birth) values(1,'wangshouren', '1562-02-22');
             insert into t_users(id,name,date) values(1,'wangshouren', str_to_date('18-02-1572','%d-%m-%Y'));
            mysql> select *from t_users;
            +------+-------------+------------+
            | id   | name        | birth      |
            +------+-------------+------------+
            |    1 | wangshouren | 1562-02-22 |
            |    1 | wangshouren | 1572-02-18 |
            +------+-------------+------------+
            2 rows in set (0.00 sec)
25、date和datetime的区别
date是短日期：只包括年月日
datetime是长日期：包括年月日时分秒信息
            drop table if exists t_users;
            create table t_users(
                id int,
                name varchar(32),
                birth date,
                create_time datetime
            );
      insert into t_users(id,name,birth,create_time) values(1,'wangshouren', '1562-02-22','2023-02-20 08:52:52');
自动获取时间 insert into t_users(id,name,birth,create_time) values(2,'wangshouren', '1562-02-22',now());
        mysql> select *from t_users;
                                  +------+-------------+------------+---------------------+
                                  | id   | name        | birth      | create_time         |
                                  +------+-------------+------------+---------------------+
                                  |    1 | wangshouren | 1562-02-22 | 2023-02-20 08:52:52 |
                                  +------+-------------+------------+---------------------+
        1 row in set (0.00 sec)
26、update（DML）
    update t_users set name='海绵宝宝',birth='1288-03-21' where id=1;
    mysql> select * from t_users;
    +------+--------------+------------+---------------------+
    | id   | name         | birth      | create_time         |
    +------+--------------+------------+---------------------+
    |    1 | 海绵宝宝     | 1288-03-21 | 2023-02-20 08:52:52 |
    |    1 | 海绵宝宝     | 1288-03-21 | 2023-02-20 09:01:31 |
    |    2 | wangshouren  | 1562-02-22 | 2023-02-20 09:09:08 |
    +------+--------------+------------+---------------------+
    3 rows in set (0.00 sec)
27、delete（DML）
mysql> delete from t_users where create_time='2023-02-20 08:52:52';
+------+--------------+------------+---------------------+
| id   | name         | birth      | create_time         |
+------+--------------+------------+---------------------+
|    1 | 海绵宝宝     | 1288-03-21 | 2023-02-20 09:01:31 |
|    2 | wangshouren  | 1562-02-22 | 2023-02-20 09:09:08 |
+------+--------------+------------+---------------------+
2 rows in set (0.00 sec)
全部删除：delete from t_users;
物理删除（很快）：mysql> truncate table t_user;
快速创建：
mysql> create table t_user as select *from t_users;
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept                  |
| emp                   |
| salgrade              |
| t_student             |
| t_user                |
| t_users               |
+-----------------------+
6 rows in set (0.00 sec)
28、约束*****
    constraint：保证表中的数据有效
    约束分类：1）非空约束：not null
             2）唯一性约束：unique
             3）主键约束：primary key  （PK）
             4）外键约束：foreign key   （FK）
             5）检查约束：check（MySQL不支持，Oracle支持）
       1）drop table if exists t_student;
       create table t_student(
        id int,
        name varchar(255) not null
        );
        insert into t_student(id,name) values(1,'严嵩'),(2,'李时珍');
        2)drop table if exists t_student;
                 create table t_student(
                  id int unique,
                  name varchar(255) not null
                  );
          出错：        insert into t_student(id,name) values(1,'严嵩'),(1,'李时珍');
          联合唯一：unique（id，name）       表级约束
          既不为空又不重复：name varchar(255) not null unique
        3）primary key  （PK）*********
        主键值是每一行记录的唯一标识。
        主键值是每一行记录的身份证号！！！
        任何一张表都应该都应该有主键，没有主键，表无效！！！
            主键值不能是NULL，不能重复：not null+unique/primary key(单一主键/复合主键)
        复合主键在实际开发中不建议使用。
        一张表主键约束只能添加一个
        主键值建议使用：int,char,bigint等类型。不建议使用varchar来做主键。主键值一般都是数字，一般都是定长的。
        主键另一种分类方式：自然主键使用较多主键只要做到不重复就行，不需要有意义。业务主键和业务挂钩，当业务发生改变时，可能会影响主键值，所以业务主键不建议使用，尽量使用自然主键。
        在MySQL中 有一种机制，可以帮助自动维护一个主键值？
        eg.   id int primary key auto_increment,....
        采用自增的方式维护主键值。
        4)foreign key   （FK）
        create table t_class(
            classno int primary key,
            classname varchar(255)
        );
        create table t_student(
            no int primary key auto_increment,
            name varchar(255),
            cno int,
              foreign key(cno) references t_class(classno)
        );
        insert into t_class(classno,classname) values(100,'河南省新乡市');
        insert into t_class(classno,classname) values(100,'河南省郑州市');

        insert into t_student(name,cno) values('jack',100);
        insert into t_student(name,cno) values('jack1',101);
        insert into t_student(name,cno) values('jack2',100);
        insert into t_student(name,cno) values('jack3',100);
        insert into t_student(name,cno) values('jack4',100);
29、存储引擎
    MySQL特有的一个术语。实际上存储引擎是一个表存储/组织数据的方式。
    不同的存储引擎，表存储的方式不同。
    怎么给表添加/指定“存储引擎”呢？
        show create table t_student；
         CREATE TABLE `t_student` (
          `id` int(11) DEFAULT NULL,
          `name` varchar(255) NOT NULL,
          UNIQUE KEY `id` (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
      可以在建表的时候给表指定存储引擎。在建表的时候可以在最后的小括号的")"的右边使用：
        mysql默认的存储引擎为InnoDB
        mysql默认的字符编码方式是utf8
存储引擎介绍：
    MYISAM：
    使用三个文件表示每个表：格式文件-存储表结构的定义(mytable.frm)
                          数据文件-存储表行的内容（mytable.MYD）
                          索引文件-存储表上索引（mytable.MYI）:索引是一本书的目录，缩小扫描范围，提高检索效率。
                        可被转换成压缩、只读表来节省空间。
                        对于一张表来说，只要是主键，或者加有unique约束的字段上会自动创建索引。
                        MYISAM存储引擎特点：
                            可被转换成压缩、只读表来节省空间。这是一种优势。
    InnoDB：支持事务，支持数据库崩溃后自动恢复机制。特点：非常安全。
            表主要特征：
                  支持事务操作，具有事务 ACID 隔离特性，默认的隔离级别是可重复读(repetable-read)、通过MVCC（并发版本控制）来实现的。能够解决脏读和不可重复读的问题。

                  InnoDB 支持外键操作。

                  InnoDB 默认的锁粒度行级锁，并发性能比较好，会发生死锁的情况。

                  和 MyISAM 一样的是，InnoDB 存储引擎也有 .frm文件存储表结构 定义，但是不同的是，InnoDB 的表数据与索引数据是存储在一起的，都位于 B+ 数的叶子节点上，而 MyISAM 的表数据和索引数据是分开的。

                  InnoDB 有安全的日志文件，这个日志文件用于恢复因数据库崩溃或其他情况导致的数据丢失问题，保证数据的一致性。

                  InnoDB 和 MyISAM 支持的索引类型相同，但具体实现因为文件结构的不同有很大差异。

                  增删改查性能方面，果执行大量的增删改操作，推荐使用 InnoDB 存储引擎，它在删除操作时是对行删除，不会重建表。
            效率不高，不能压缩，不能转换成只读，不能很好的节省空间。
    memory：
    也称HEAP存储引擎,所以数据保存在内存中,如果MySQL服务重启数据会丢失,但是表结构会保存下来
    功能特点：
    支持HASH索引和BTree索引
    所有字段都为固定长度 varchar(10)=char(10)
    不支持BLOB和TEXT等大字段
    Memory存储引擎使用表级锁
    如何选择存储引擎
    大部分情况下，InnoDB都是正确的选择，可以简单地归纳为一句话“除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎
    优点：查询效率是最高的。
30、事务********
定义：一个事务就是一个完整的业务逻辑（最小的工作单元）。
只有DML语句才会有事务这一说。涉及到安全
只有insert、delete、update和事务相关，其他无关。
实质：一个事务就是多个（批量的）DML语句同时成功，或者同时失败。
    怎么做到同时成功，或者同时失败？
        InnodB存储引擎：提供一组用来记录事务性活动的日志文件。
        事务开始，批量DML语句，事务结束。
        提交事务：清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。提交事务标志着，事务的结束，并且是一种全部成功的结束。
        回滚事务：将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件、回滚事务标志着，事物的结束，并且是一种全部失败的结束。
命令：
提交事务：commit
回滚事务：rollback
事务：transaction
    MySQL默认情况下是自动提交的，也就是每执行一条DML语句，则提交一次。
    关闭自动提交机制：start transaction;    这样可以回滚事务
回滚事务：
mysql> select *from t_student;
Empty set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into t_student(id,name) values(1,'zhangsan'),(2,'magui'),(3,'xx');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select *from t_student;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | magui    |
|    3 | xx       |
+------+----------+
3 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from t_student;
Empty set (0.00 sec)
提交事务：
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into t_student(id,name) values(1,'zhangsan'),(2,'magui'),(3,'xx');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from t_student;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | magui    |
|    3 | xx       |
+------+----------+
3 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from t_student;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | magui    |
|    3 | xx       |
+------+----------+
3 rows in set (0.00 sec)
事务4个特性：
A C I D
    原子性 Atomicity：事务是最小的工作单元，不可再分。
    一致性 Consistency：所有操作必须同时成功，同时失败
    隔离性 Isolation：事务的隔离性是指在并发环境中，并发的事务是互相隔离的，一个事务的执行不能被其它事务干扰。也就是说，不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间。
    持久性 Duration：事务的持久性是指事务一旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态。

    在事物进行过程中，未结束之前，DML语句是不会更改底层数据，只是将历史操作记录一下，在内存中完成记录。
    只有在事物结束的时候，而且是成功的结束的时候，才会修改底层硬盘文件中的数据

隔离性 Isolation：隔离比作一道墙，厚薄表示隔离级别。
    隔离级别分为4个：
            读未提交：read uncommitted (最低的隔离级别)
            事务A可以读到事务B未提交的数据，出现脏读现象。

            读已提交：read committed
            事务A只可以读到事务B提交的数据。不可重复读取数据

            可重复读：repeatable read
            事务A开启后，每次事务A读取的数据都是一致的。无论事务B是否将数据已经修改。
            解决了不可重复读。
            读到的数据称为幻像。

            序列化读：serializable     (最高的隔离级别)
            效率最低，解决了所有的问题。表示事务排队，不能并发。synchronized,线程同步，每一次读到的数据都是最真实的。

    代码验证各种隔离级别：
    mysql> select @@tx_isolation
        -> ;
    +-----------------+
    | @@tx_isolation  |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+
    1 row in set, 1 warning (0.08 sec)
    修改事务隔离级别：set global transaction isolation level read committed;
    关闭再打开即可，两个窗口进行测试
    read uncommitted:略
    read committed：

    use bjpowernode;                                        use bjpowernode;
                                                            start transaction;
    start transaction;                                          mysql> insert into t_student(id,name) values(4,'yingyu');
                                                                Query OK, 1 row affected (0.00 sec)
    select *from t_student;
    +------+----------+
    | id   | name     |                                      committ;
    +------+----------+
    |    1 | zhangsan |
    |    2 | magui    |
    |    3 | xx       |
    +------+----------+
    3 rows in set (0.00 sec)
    mysql> select *from t_student;
    +------+----------+
    | id   | name     |
    +------+----------+
    |    1 | zhangsan |
    |    2 | magui    |
    |    3 | xx       |
    |    4 | yingyu   |
    +------+----------+
    4 rows in set (0.00 sec)
    repeatable read：略
    serializable ：
        set global transaction isolation level serializable;
        表示事务排队，不能并发。一个窗口操作这个表，在不提交的状况下，另一个窗口不能查询（光标会停住）。
                当在当前窗口提交，另一个窗口会马上打印结果。
31、索引
    索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。一张表的一个字段可以添加一个
    索引，当然，多个字段联合起来也可以添加索引，索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
    MySQL在查询方面主要就是两种方式：全表扫描和根据索引检索
    MySQL数据库当中索引也是需要排序的。并且这个索引的排序和TreeSet数据结构相同。treeset(Treemap)底层是一个自平衡的二叉树！B-tree  在MySQL当中索引是一个B-Tree数据结构。
            遵循左小右大原则存放，采用中序遍历方式遍历取数据。
    在mysql当中，主键上，以及unique字段上都会自动添加索引的。什么条件下，我们会考虑给字段添加索引呢？
            条件一：数据量庞大
            条件二：该字段经常出现在where的后面，以条件的形式存在，也就是这个字段总是被扫描。
            条件三：该字段很少的DML操作。（因为DML之后，索引需要重新排序）。
    不要随意添加索引，会导致效率下降。
    查询时建议通过主键、unique查询，效率高。
索引的创建：create index emp_ename_index on emp(ename);
索引的删除：drop index emp_ename_index on emp;
查看检索是否使用索引：
mysql> explain select *from t_student where name='xx';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.08 sec)
//使用索引
mysql> create index ename_index on t_student(name);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select *from t_student where name='xx';
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_student | NULL       | ref  | ename_index   | ename_index | 767     | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
索引的失效：
1、模糊查询中%开头
2、使用or时，两边必须都有索引，否则失效
3、复合索引，使用右侧索引对象会失效。
create index ename_index on t_student(name，id);
4、在where当中索引列参加了运算，索引失效
5、在where当中索引列使用了函数，索引失效

索引是各种数据库进行优化的重要手段。
索引分了很多类。注意：唯一性比较弱（存在大量重复的数据）的字段上添加索引用处不大。
32、视图（view）
站在不同的角度去看待同一份数据。
    创建：create view dept2_view as select *from dept2;
    删除：drop view dept_view
    只有DQL语句以view的形式操作。
特点：我们可以面向视图对象进行增删查改，对视图的增删改查会导致原表被操作！
用处：视图对象存储在硬盘上面不会消失。视图对象用来简化较长的DQL语句，对其打包。
C（create）R（retrieve）U（update）D（delete）：增删改查
33、DBA的命令
数据的导出：mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123000
    表：mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123000
数据的导入：
create database bjpowernode;
use bjpowernode;
source D:\bjpowernode.sql;
34、数据库设计3范式******
    数据库的设计范式分为3个
    1、要求任何一张表必须有主键，每一个字段原子性不可再分。
    2、建立在第一范式的基础之上，要求所有非主键字段完全依赖于主键，不要产生部分依赖
    3、建立在第二范式的基础之上，要求所求非主键字段直接依赖主键，不要产生传递依赖。
    面试经常问*****
    设计数据库表的时候，按照以上范式进行。可以避免表中的数据的冗余。
第一范式：核心范式，所有表必须满足。必须有主键，每一个字段原子性不可再分。
第二范式：
学生编号+教师编号(pk)  学生姓名   教师姓名
------------------------------------------
1001        001        张三        王
1002        002        李四        赵
1003        001        王五        王
1001        002        张三        赵
以上表产生部分依赖
设计口诀：多对多，三张表，关系表两个外键！

教师编号(pk)    教师姓名
------------------------
001             王
002             赵
学生编号(pk)  学生姓名
----------------------
1001        张三
1002        李四
1003        王五

id(pk)  学生编号(FK)   教师编号（fk）
------------------------------------------
1        1001           001
2        1002           002
3        1003           001
4        1001           002

第三范式：
一个部门存在多个员工，如果写在一起会产生数据的冗余，也就是传递依赖。
解决：一对多，两张表，多的表加外键。

一对一怎么设计：一对一，外键唯一。
完结：
数据库设计三范式是理论上的，实践和理论有的时候会有偏差。最终的目的都是为了满足客户的需求，
有的时候会拿冗余换执行速度。因为在sql当中，表和表之间连接次数越多，效率越低。
为了减少表的连接次数，这样做也是合理的。对于开发人员来说，sql语句的编写难度也会降低。