MySQL04_22.7.31

1、约束

作用是保证表中数据有效

1、约束包括

1、非空约束 not null

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255) not null
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');
//not null 只有列级约束

2、唯一性约束 unique

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255) unique,
    email varchar(255) unique
    // 约束直接加在列后面的 叫做列级约束
);
// 两个字段各自具有唯一性
drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255),
    email varchar(255)
    unique(name,email) //约束没有添加在列后面的 叫做表级约束
);
// 两个字段的约束联合在一起

unique 和 not null 联合 即 既不能为空 也不能重复

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255) not null unique
);

如果一个字段同时被 not null 和 unique 同时约束 将自动转换为 主键字段

3、主键约束 primary key （PK) 任何一张表 都要又主键

          1、主键约束 not null + unique

mysql> create table t_vip(
    -> id int primary key,
    -> name varchar(255)
    -> );
Query OK, 0 rows affected (0.03 sec)
​
mysql> insert into t_vip(id,name) values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)
​
mysql> insert into t_vip(id,name) values(2,'zhangsan');
Query OK, 1 row affected (0.01 sec)
​
mysql> insert into t_vip(id,name) values(2,'zhaan');
ERROR 1062 (23000): Duplicate entry '2' for key 't_vip.PRIMARY'
drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255),
    email varchar(255),
    primary key(id,name)
);
// id 和 name 联合起来做主键 叫做复合主键  实际开发中不建议使用！！最好就直接单一主键就行
mysql> insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@qq.com');
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into t_vip(id,name,email) values(1,'lisi','zhangsan@123.com');
Query OK, 1 row affected (0.07 sec)

        2、主键值

                1、主 键值是每一行记录的唯一标识

                2、主键值是每一行记录的身份证号

                3、主键值一般都是定长的 不建议用varchar做主键

4、外键约束 foreign key (FK) 

drop table if exists t_student;
drop table if exists t_class;
​
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)
);

1、子表中的外键 引用父表中的某一个字段 必须要有唯一性约束

2、外键可以为null

2、source sql文件

mysql> source E:\09sql\Better_Mysql\document\vip.sql
Query OK, 0 rows affected (0.02 sec)
​
Query OK, 0 rows affected (0.03 sec)
​
Query OK, 1 row affected (0.00 sec)
​
Query OK, 1 row affected (0.01 sec)
​
mysql> select * from t_vip;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
+------+----------+
2 rows in set (0.00 sec)

2、引擎

        1、存储引擎

| t_student | CREATE TABLE `t_student` (
  `no` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `cno` int DEFAULT NULL,
  PRIMARY KEY (`no`),
  KEY `cno` (`cno`),
  CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

可以在建表的时候指定存储引擎

在建表最后小阔号')'的右边 使用 ENGINE 来指定存储引擎 CHARSET 来指定此表的字符编码方式

mysql默认 的存储引擎是 InnoDB 默认的字符编码方式 utf8\

mysql> create table t_rr(
    -> no int primary key,
    -> name varchar(255)
    -> )engine = InnoDB  default charset=gbk;
Query OK, 0 rows affected (0.03 sec)

        2、常用引擎 mysql 支持9大存储引擎

1、MyISAM 

        管理的表具有以下特征：

        使用三个文件来表示每个表：

        1、格式文件 存储表结构的定义(mytable.frm)

        2、数据文件 存储表行的内容(mytable.MYD)

        3、索引文件 存储表上的索引 (mytable.MYI)

        4、不支持事务

        对于一张表 主要是主键 或添加了unique约束 就会自动创建索引

2、InnoDB

        1、支持事务 支持数据库崩溃后自动恢复机制

        2、特点 ：安全

3、MEMORY

        1、表数据及索引存储在内存中

        2、优点 查询效率高 缺点 关机自动消失

3、事务 （DML语句）

        事务 就是一个完整的业务逻辑 transaction

        提交事务 commit; 回滚事务 rollback;

1、事务的特点：

原子性 说明事务是最小的工作单元 不可再分

  一致性  在同一个事务中 所有操作必须同时成功 或同时失败 以保证数据的一致性

隔离性 A事务与B事务具有一定的隔离 A B 两个事务同时操作一张表

  持久性  事务最终结束的一个保障 事务提交相当于将没有保存 到硬盘上的数据保存到硬盘中

2、事务与事务之间的隔离级别

1、读未提交 read uncommitted

事务A可以读取到事务B未提交的数据 （脏读）

2、读已提交 read committed

事务A只能读取事务B提交后的数据

解决脏读

不可重复读取数据

3、可重复读 repeatable read

解决不可重复读

可能出现幻影读 不够真实

（早上9点提交的事务 只要事务没有结束 到晚上9点 读到的数据还是原来的样子）

 

4、序列化/串行化 serializable (最高的隔离级别)

最高隔离级别 效率最低 解决所有问题

事务排队 不能并发

4、索引

1、索引在数据 库表的字段上添加 提高检索效率 的一种机制

2、一个字段添加一个索引 多个字段联合起来也可以添加索引

3、索引相当于 书的目录 目的：为了缩小扫描范围添加的机制

4、mysql数据库中的索引 也需要排序 并且这个排序与TreeSet数据结构相同 (TreeMap)自平衡的二叉树 在mysql中索引是一个B-Tree数据结构

在任何数据库中 主键会自动添加索引 在mysql中 一个字段有unique约束 也会自动添加索引

 

1、创建索引

mysql> create index emp_ename_index on emp(ename);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

2、删除索引

mysql> drop index emp_ename_index on emp;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

3、查看sql语句是否使用索引进行检索

mysql> explain select * from emp where ename = 'King';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

由此看出 有14条记录 全局扫描 没有索引！

//创建索引
mysql> create index emp_ename_index on emp(ename);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
//仅一条扫描记录 使用索引
mysql> explain select * from emp where ename = 'king';
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_ename_index | emp_ename_index | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

4、索引失效

        1、尽量避免模糊查询以 "%" 开头

select * from emp where ename like '%T';

        2、使用or的时候会失效 必须 or 两边的字段同时有索引

        3、使用复合索引时 没有使用左侧的列查找 索引失效

mysql> create index emp_job_sal_index on emp(job,sal);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
mysql> explain select * from emp where job = 'manager';
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_job_sal_index | emp_job_sal_index | 39      | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

        4、在where中 索引列参加了数学运算

mysql> explain select * from emp where sal+1 = 800;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

        5、在where中使用了函数

mysql> explain select * from emp where lower(ename) = 'smith';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

5、视图

1、创建视图 （视图同样存在硬盘中 不会消失）

//  as  后面必须是 DQL 语句 
mysql> create view dept2_view as select * from dept2;
Query OK, 0 rows affected (0.01 sec)

2、删除视图

drop  view dept2_view;

3、视图作用 （对视图进行操作 会影响到原表的数据）

        1、面向视图对象进行增删改查 对视图对象操作 会导致原表被操作

mysql> create view dept2_view as select * from dept2;
Query OK, 0 rows affected (0.01 sec)
​
//插入数据至视图
mysql> insert into dept2_view(deptno,dname,loc) values(90,'sales','beijing');
Query OK, 1 row affected (0.01 sec)
​
//查看视图数据
mysql> select * from dept2_view;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     90 | sales      | beijing  |
+--------+------------+----------+
5 rows in set (0.00 sec)

​//查看原表数据 同样别操作了
mysql> select * from dept2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     90 | sales      | beijing  |
+--------+------------+----------+
5 rows in set (0.00 sec)

        2、简化sql语句

//将指定sql语句"封装"成一个视图
mysql> create view emp_dept_view
//需要用到下面的sql语句时 直接引用上面的视图即可
    -> as select e.ename,e.sal,d.dname
    -> from emp e
    -> join dept d
    -> on e.deptno = d.deptno;

适用场景：当需要将一条sql语句被多个位置反复使用 即可将这条sql语句以视图的形式新建

在需要编写这条sql语句的时候 直接使用这个视图对象 简化开发

利于后期维护 需要修改时 只需要修改一个位置即可 修改视图对象所映射的sql语句

6、数据库设计三范式

1、第一范式 ： 要求任何一张表必须有主键 每个字段的原子性不可再分

2、第二范式 ： 建立在第一范式基础之上 要求所有非主键字段完全依赖主键 不要产生部分依赖

多对多 三张表 关系表两个外键

3、第三范式 ： 建立在 第二范式基础之上 要求所有非主键字段直接依赖主键 不要产生传递依赖

一对多 两张表 多的表加两个外键