三天学习数据库——03

最新推荐文章于 2024-12-13 21:28:23 发布

伊柒丶

最新推荐文章于 2024-12-13 21:28:23 发布

阅读量332

点赞数

文章标签：数据库 mysql

本文链接：https://blog.youkuaiyun.com/Ensemble_/article/details/108816102

版权

本文详细介绍了数据库的约束，包括唯一性约束、主键约束和外键约束，强调了主键的重要性和作用。接着，讨论了存储引擎的概念，包括常见的InnoDB和MyISAM引擎。此外，还深入讲解了事务的特性，如ACID原则和不同隔离级别的示例。最后，简要提及了索引和视图的概念及其在数据库中的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

三天学习数据库——03

1、约束

1.1、唯一性约束

唯一性的约束修饰的字段具有唯一性，不能重复，但可以为null

案例：给某一列添加unique(列级约束)

create table t_user(
	id int(10) unique,	//列级约束，该字段的数值不能和其他的记录的数值重复
	password varchar(20)
);

案例：给两列或多列添加unique(表级约束)

create table t_user(
	id int(10),
	password varchar(20),
	unique(id,password)//多字段联合添加一个约束
);

表级约束是两个字段组成的集合形成的约束。

 insert into t_user(id,password) values(1,'123'),(2,'123');

+------+----------+
| id   | password |
+------+----------+
|    1 | 123      |
|    2 | 123      |
+------+----------+

注意：not null 只有列级约束，没有表级约束。

1.2、主键约束

怎么给一张表添加主键约束

create table t_user(
	id int primary key,	//列级约束
	username varchar(255),
	password varchar(255)
);

insert into t_user(id,username,password) values(1,'zhangsan','123'),(2,'lisi',123);
Query OK, 2 rows affected (0.01 sec)


insert into t_user(id,username,password) values(1,'222','111');
ERROR 1062 (23000): Duplicate entry '1' for key 't_user.PRIMARY'

主键具有非空约束和唯一性约束

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | NO   | PRI | NULL    |       |
| username | varchar(255) | YES  |     | NULL    |       |
| password | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

主键相关术语：

主键约束：primary key

主键字段：id字段添加primary key之后，id叫做主键字段

主键值：id字段中的每一个值都是主键值

主键有什么用：

表的设计有三大范式有要求：

一范式：每一张表都应该有主键

作用：主键值是这行记录在这张表当中的唯一标识

主键的分类：

根据主键的字段数量来划分

单一主键（推荐的，常用的）

复合主键：多个字段联合起来添加的一个主键约束（不建议使用，违反三范式）

自然主键：主键值最好是一个和业务没有任何关系的自然数

业务主键：主键值和系统的业务挂钩

一张表的主键约束只能有一个

使用表级约束方式定义主键：

drop table if exists t_user;
create table t_user(
	id int,
    username varchar(20),
    password varchar(20),
    primary key(id)
  );

insert into t_user values (1,'zhangsan','123'),(2,'lisi','123');

select * from t_user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | zhangsan | 123      |
|  2 | lisi     | 123      |
+----+----------+----------+

MySQL提供主键自增

drop table if exists t_user;
 create table t_user(
    id int primary key auto_increment,
    username varchar(20),
    password varchar(20)
 );

id字段自动维护一个自增的数字，以1开始递增

如果跳过1赋值，则从赋值的数字开始自增，如1,2,20,21…

insert into t_user(username,password) values ('zhangsan','123'),('lisi','123');
insert into t_user(id,username,password) values (40,'wangwu','123');
insert into t_user(username,password) values ('liuliu','123');

select * from t_user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | zhangsan | 123      |
|  2 | lisi     | 123      |
| 40 | wangwu   | 123      |
| 41 | liuliu   | 123      |
+----+----------+----------+

1.3、外键约束

关于外键约束的相关术语

外键约束：foreign key

外键字段：添加有外键约束的字段

外键值：外键字段中的每一个值

业务背景：

设计数据库用来维护学生和班级的信息

第一种：一张表

第二种：两张表（班级表和学生表）

t_class(班级表)：classno(pk),cname

t_student(学生表)：sno(pk),sname,classno(fk)

将以上表的建表语句写出来：

t_student叫做子表，t_class叫做父表

顺序要求：

删除时先删除子表

添加数据时，先添加父表

	drop table if exists t_student;
	drop table if exists t_class;

	create table t_class(
		cno int,
		cname varchar(255),
		primary key(cno)
	);

	create table t_student(
		sno int,
		sname varchar(255),
		classno int,
		primary key(sno),
		foreign key(classno) references t_class(cno)
	);

	insert into t_class values(1812031,'软件工程1801班');
	insert into t_class values(1812032,'软件工程1802班');

	insert into t_student values(1,'zs','1812031');
	insert into t_student values(2,'ww','1812032');
	insert into t_student values(3,'dj','1812032');
	insert into t_student values(4,'ll','1812031');

外键可以为NULL？

外键可以为NULL

外键必须引用的主键吗

不一定，但是必须为候选码（具有唯一性）

2、存储引擎

2.1、建表的时候可以指定存储引擎，也可以指定字符集

MySQL默认使用的存储引擎是InnoDB方式

默认采用的字符集是UTF-8

2.2、什么是存储引擎

存储引擎只有在MySQL中存在（Oracle中叫做存储方式）

MySQL支持很多存储引擎，每一个引擎对应一种存储方式

每一个引擎有自己的优缺点，需要在合适的时机选择适合的引擎

2,3、查看当前MySQL支持的存储引擎

查看引擎的MySQL命令

	show engines \G

8.0版本支持的存储引擎有9个

*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO

2.4、常见的存储引擎

	Engine: MyISAM
		  Support: YES
		  Comment: MyISAM storage engine
	Transactions: NO
				 XA: NO
	Savepoints: NO

MyISAM不支持事务

MyISAM是MySQL最常用的，但不是默认的

MyISAM采用三个文件组织一张表

xxx.frm（存储格式文件）

xxx.MYD（存储表中数据文件）

xxx.MYI（存储表中索引的文件）

优点：可以被压缩，节省存储空间，可以转换成只读表，提高检索效率

缺点：不支持事物

	Engine: InnoDB
		Support: DEFAULT
		Comment: Supports transactions, row-level locking, and foreign keys
	Transactions: YES
      XA: YES
	Savepoints: YES

优点：支持事务，行级锁，外键等。这种存储引擎数据的安全得到了保障。数据存储在tablespace这样的表空间中，无法被压缩，无法转换成只读。这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。

	Engine: MEMORY
		Support: YES
		Comment: Hash based, stored in memory, useful for temporary tables
	Transactions: NO
      XA: NO
	Savepoints: NO

缺点：不支持事务，数据容易丢失，因为所有数据和索引都是存储在内存当中的。

优点：查询速度最快

以前叫做HAPE引擎

3、事务（transaction）

3.1、什么是事务

一个事务是一个完整的业务逻辑单元，不可再分

比如：银行账户转账，从A账户向B账户转账，需要执行两条update语句：

update t_act set balance = balance - 10000 where actno = ‘act_001’;
update t_act set balance = balance + 10000 where actno = 'act_002';

以上两条DML语句必须同时执行成功，或者同时失败，不允许一条成功，一条失败

要想保证以上两条DML语句同时成功或者同时失败，那么就必须使用数据库的“事务机制”

3.2、和事务相关的语句只有：DML语句（insert delete update）

为什么？

因为它们三个语句都是数据库当中与“数据”相关的。

事务的存在是为了保证数据的安全性、完整性。

3.3、假设所有的业务都能用1条DML语句搞定，还需要事务机制吗

不需要，但是通常一件事的完成需要多条DML语句，共同联合完成。

3.4、事务的特性：

事务包括四大特性：ACID

A：原子性：事务是最小的工作单元，不可再分。

C：一致性：事务必须保证多条DML语句同时成功或同时失败。

I：隔离性：事务A和事务B之间具有隔离。

D：持久性：最终数据必须持久化到硬盘文件，事务才算成功的结束。

3.5、关于事务之间的隔离性

事务的隔离性存在隔离级别，理论上隔离级别包括四个：

第一个级别：读未提交（read uncommitted）

对方事务还没有提交，我们可以读到对方未提交的数据。

读未提交存在脏读（dirty read）现象，读脏数据。

第二个级别：读已提交（read committed）

对方事务提交之后的数据我方可以读到。

读已提交存在的问题是：不可重复读。

第三级别：可重复读（repeatable read）

这种隔离级别解决了：不可重复读问题。

这种隔离级别存在的问题是：读到的数据是幻象。

第四级别：序列化读/串行化读

解决所有问题。

效率低，需要事务排队。

mysql数据库默认的隔离机制是：可重复读

3.6、演示事务

MySQL事务默认的情况下是自动提交的。

（什么是自动提交？只要执行一条DML语句就提交一次）

怎么关闭自动提交？

	start transaction;

*演示：MySQL中的事务是支持自动提交的，只要执行一条DML语句，就提交一次

drop table if exists t_user;

create table t_user(

	id int primary key auto_increment,

	username varchar(10)

);

insert into t_user(username) values('zs');


select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+


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

 select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+

*演示使用start transaction；关闭自动提交机制。

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

    mysql> insert into t_user(username) values('ls');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_user(username) values('ww');
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_user;
    +----+----------+
    | id | username |
    +----+----------+
    |  1 | zs       |
    |  2 | ls       |
    |  3 | ww       |
    +----+----------+
    3 rows in set (0.00 sec)

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

    mysql> select * from t_user;
    +----+----------+
    | id | username |
    +----+----------+
    |  1 | zs       |
    +----+----------+
    1 row in set (0.00 sec)

mysql> insert into t_user(username) values('mary');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_user(username) values('eric');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(username) values('jack');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  4 | mary     |
|  5 | eric     |
|  6 | jack     |
+----+----------+
4 rows in set (0.00 sec)

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

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

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  4 | mary     |
|  5 | eric     |
|  6 | jack     |
+----+----------+
4 rows in set (0.00 sec)

3.7、使用两个事务演示以上的隔离级别

第一：演示read uncommitted

设置事务的隔离级别：set global transaction isolation level read uncommitted;

查看事务的全局隔离级别：

MySQL5：select @@global.tx_isolation;

MySQL8：select @@global.transaction_isolation;

    select @@global.transaction_isolation;
    +--------------------------------+
    | @@global.transaction_isolation |
    +--------------------------------+
    | READ-COMMITTED                 |
    +--------------------------------+

设置全局隔离级别之后，需要退出数据库，重新连接数据库之后，手动开启事务，不然设置的全局隔离级别会不起作用。

事务1：

向表中插入数据，不提交

			mysql> insert into t_user(username) values('hongqing');
			Query OK, 1 row affected (0.01 sec)

事务2：

查询t_user表中的数据

			mysql> select * from t_user;
			+----+----------+
            | id | username |
            +----+----------+
            |  1 | zs       |
            |  4 | mary     |
            |  5 | eric     |
            |  6 | jack     |
            |  8 | hongqing |
            +----+----------+

事务2读取到了事务1未提交的数据，发生了脏读现象。

第二：演示read committed

事务1：

    mysql> insert into t_user(username) values('chang');
    Query OK, 1 row affected (0.02 sec)

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

事务2：

    mysql> select * from t_user;
    +----+----------+
    | id | username |
    +----+----------+
    |  1 | zs       |
    |  4 | mary     |
    |  5 | eric     |
    |  6 | jack     |
    |  8 | hongqing |
    |  9 | chang    |
    +----+----------+

事务2可以读到事务1提交之后的数据，删除t_user表之后事务2读到的为空表

第三：演示repeatable read

事务1：

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

    mysql> insert into t_user(username) values('wangli');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_user(username) values('chang');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_user(username) values('xxxsdw');
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_user;
    +----+----------+
    | id | username |
    +----+----------+
    | 13 | wangli   |
    | 14 | chang    |
    | 15 | xxxsdw   |
    +----+----------+
    3 rows in set (0.00 sec)

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

事务2：

    mysql> select * from t_user;
    Empty set (0.00 sec)

事务1删除表数据后，开始事务，再向表中添加数据，事务1可以读到重新添加后的数据，但是事务2读到的还是空表，这种情况叫做可重复读，或者叫做幻象读。

第四：演示serializable

事务1：

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

    mysql> insert into t_user(username) values('pei');
    Query OK, 1 row affected (0.00 sec)

事务2：

	mysql> select * from t_user;

事务1未提交，事务2的查询语句会卡住，事务1提交之后，事务2立即查询出结果。

4、索引

4.1、什么是索引？有什么用？

索引就相当于一本书的目录，通过目录就可以快速的找到对应的资源

在数据库方面，查询一张表有两种检索方式：

第一种方式：全表扫描

第二种方式：根据索引检索（效率很高）

索引为什么可以提高检索效率呢？

其实最根本的原理是缩小了扫描范围

索引虽然可以提高检索效率，但是不能随意添加索引，因为索引也是数据库中的对象，也需要数据库的不断的维护。是有维护成本的。加入表中的数据经常被修改，就不适合添加索引，因为数据一旦被修改，索引就需要重新排序，进行维护。

select ename,sal from emp where ename = ‘SMITH’;

当ename字段没有添加索引的时候，以上的SQL语句会进行全表扫描，扫描ename字段中所有的值

当ename字段上添加索引的时候，以上SQL语句会根据索引扫描，快速定位。

4.2、怎么创建索引对象？怎么删除索引对象？

创建索引对象：

create index 索引名称 on 表名（字段名）;

删除索引对象：

drop index 索引名称 on 表名;

4.3、什么时候考虑给字段添加索引？（满足什么条件）

数据量庞大。（根据客户的需求，根据线上的环境）

该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）

该字段经常出现在where子句中（经常根据哪个字段查询）

4.4、注意：主键和具有unique的约束字段会自动添加索引。

根据主键查询效率较高，尽量根据主键检索。

4.5、查看SQL语句的执行计划

mysql> explain select ename,sal from emp where sal = 5000;

给薪资 sal 添加索引：

create index emp_sal_index on emp(sal);

4.6、索引底层采用的数据结构是B+Tree

4.7、索引的实现原理

通过BTree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位到表中的数据，效率是最高的。

4,8、索引的分类

单一索引：给单个字段添加索引

复合索引：给多个字段1个索引

主键索引：主键上会自动添加索引

唯一索引：有unique约束的字段上会自动添加索引

…

4.9、索引什么时候失效

select ename from emp where ename like ‘%A%’;

模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

5、视图（view）

5.1、什么是视图？

站在不同的角度去看待数据，（同一张表的数据，通过不同的角度去看待）。

5.2、怎么创建视图？怎么删除视图？

create view myview as select empno，ename from emp；

drop view myview；

注意：只有DQL才能以视图对象的方式创建出来。

5.3、对视图进行增删改查，会影响到原表数据（通过视图影响到原表数据的，不是直接操作的原表）

可以对视图进行CRUD操作

5.4、视图的作用？

视图可以隐藏表的实现细节，保密级别较高的系统，数据库只对外提供相关的视图，

Java程序员只对试图对象进行CURD。

6、DBA命令

6.1、将数据库中的数据导出

在windows的dos命令窗口中执行(导出整个库)

mysqldump mydatabase>E:\mydatabase.sql -uroot -p123456

在windows的dos命令窗口中执行（导出指定数据库当中的指定表）

mysqldump mydatabase t_user>E:\mydatabase.sql -uroot -p123456

6.2、将数据导入数据库

	create database mydatabase;
	use mydatabase;
	source E:\mydatabase.sql;

7、数据库设计的三范式（重点内容）

7.1、什么是设计范式？
设计表的依据。根据这个三范式设计的表不会出现数据冗余

7.2、三范式都是哪些
第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分

第二范式：在第一范式的基础上，每一个字段都完全依赖主键，没有部分依赖。
	多对多？三张表，关系表两个外键

t_student 学生表

sno(pk) sname

-------------------------------

1 张三

2 李四

3 王五

t_teacher 讲师表

tno(pk) tname

-------------------------------

1 张老师

2 王老师

3 李老师

t_student_teacher_relation 学生讲师关系表

id sno(fk) tno(fk)

---------------------------------------------

1 1 2

2 1 1

3 2 2

4 2 3

5 3 3

第三范式：建立在二范式基础上，所以非主键字段不能对主键产生传递依赖。
一对多？两张表，多的表加外键

班级 t_class

cno(pk) cname

----------------------------------------

1 一班

2 二班

3 三班

4 四班

学生 t_student

sno(pk) sname cno(fk)

---------------------------------------------------------

1 张三 2

2 李四 1

3 王五 3

4 刘能 4

提醒：在实际开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度