MySQL基础学习 之 约束和存储引擎

目录

一、insert一次插入多条记录

二、快速创建表

语法：

原理：

示例：

三、将查询结果插入一张表

四、删除表中的数据

delete命令（DML语句）：

truncate命令（DDL语句）：

五、约束

定义：

表级约束：

列级约束：

分类：

1.非空约束：not null

作用：

语法：

示例： 

2.唯一性约束 ：unique

作用：

语法：

示例：

两个字段联合使用唯一性约束

对一个字段同时使用unique和not null关键字

3.主键约束（primary key）

1.相关术语：

2.主键的作用：

3.特点：

4.主键的分类：

5.语法：

6.auto_increment：

4.外键约束(foreign key)

1.相关术语：

2.作用：

3.语法：

六、存储引擎

1.定义：

2.指定存储引擎和字符编码方式

结论：

 3.查看当前MySQL的版本：

命令：

命令操作：  

4.查看当前的MySQL支持哪些存储引擎：

命令：

命令操作： 

5.MySQL常用的存储引擎

MyISAM存储引擎：

InnoDB存储引擎：

MEMORY存储引擎：

---

一、insert一次插入多条记录

mysql> create table t_user(
    -> id int,
    -> name varchar(32),
    -> birth date,
    -> create_time datetime
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t_user(id,name,birth,create_time)
    -> values  //！！！！
    -> (10001,'zs','2001-3-7',now()),
    -> (10002,'ls','2002-5-8',now()),
    -> (10003,'ww','2004-7-7',now());

mysql> select * from t_user;
+-------+------+------------+---------------------+
| id    | name | birth      | create_time         |
+-------+------+------------+---------------------+
| 10001 | zs   | 2001-03-07 | 2022-10-03 09:34:12 |
| 10002 | ls   | 2002-05-08 | 2022-10-03 09:34:12 |
| 10003 | ww   | 2004-07-07 | 2022-10-03 09:34:12 |
+-------+------+------------+---------------------+
3 rows in set (0.00 sec)

二、快速创建表

语法：

create table 新表 as select ... from 旧表;

原理：

将查询出来的结果，当做一个新的表新建，实现表的快速复制。

示例：

mysql> create table t_user2 as select * from t_user;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_user;
+-------+------+------------+---------------------+
| id    | name | birth      | create_time         |
+-------+------+------------+---------------------+
| 10001 | zs   | 2001-03-07 | 2022-10-03 09:34:12 |
| 10002 | ls   | 2002-05-08 | 2022-10-03 09:34:12 |
| 10003 | ww   | 2004-07-07 | 2022-10-03 09:34:12 |
+-------+------+------------+---------------------+
3 rows in set (0.00 sec)

三、将查询结果插入一张表

语法：

insert into 被插入表 select ... from 插入数据表;

示例： 

mysql> insert into t_user2 select * from t_user;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> select * from t_user2;
+-------+------+------------+---------------------+
| id    | name | birth      | create_time         |
+-------+------+------------+---------------------+
| 10001 | zs   | 2001-03-07 | 2022-10-03 09:34:12 |
| 10002 | ls   | 2002-05-08 | 2022-10-03 09:34:12 |
| 10003 | ww   | 2004-07-07 | 2022-10-03 09:34:12 |
| 10001 | zs   | 2001-03-07 | 2022-10-03 09:34:12 |
| 10002 | ls   | 2002-05-08 | 2022-10-03 09:34:12 |
| 10003 | ww   | 2004-07-07 | 2022-10-03 09:34:12 |
+-------+------+------------+---------------------+
6 rows in set (0.00 sec)

四、删除表中的数据

删除表数据，留下表结构。

delete命令（DML语句）：

特点：

• 表中的数据被删除了，但是硬盘上真实储存空间不会被释放；
• 优点：支持回滚，可以恢复数据
• 缺点：一个一个删除数据，删除效率较低

语法：

delete from 表名;

示例： 

mysql>  insert into t_user2 select * from t_user;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>  select * from t_user2;
+-------+------+------------+---------------------+
| id    | name | birth      | create_time         |
+-------+------+------------+---------------------+
| 10001 | zs   | 2001-03-07 | 2022-10-03 09:34:12 |
| 10002 | ls   | 2002-05-08 | 2022-10-03 09:34:12 |
| 10003 | ww   | 2004-07-07 | 2022-10-03 09:34:12 |
+-------+------+------------+---------------------+
3 rows in set (0.00 sec)

mysql> delete from t_user2;
Query OK, 3 rows affected (0.00 sec)

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

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

mysql> select * from t_user2;
+-------+------+------------+---------------------+
| id    | name | birth      | create_time         |
+-------+------+------------+---------------------+
| 10001 | zs   | 2001-03-07 | 2022-10-03 09:34:12 |
| 10002 | ls   | 2002-05-08 | 2022-10-03 09:34:12 |
| 10003 | ww   | 2004-07-07 | 2022-10-03 09:34:12 |
+-------+------+------------+---------------------+
3 rows in set (0.00 sec)

truncate命令（DDL语句）：

特点：

• 一般使用在确定永久删除的大表（上亿条数据）中
• 优点：物理删除，效率高，表中的数据被一次截断
• 缺点：不支持回滚，删除后无法找回

语法：

truncate table 表名;

示例：

mysql> insert into t_user2 select * from t_user;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> truncate table t_user2;
Query OK, 0 rows affected (0.02 sec)

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

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

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

五、约束

定义：

• 在创建表时，我们可以给表中的字段加上一些约束，来保证表中数据完整和有效。.

表级约束：

• 约束没有添加在列的后面，一般在多个字段联合起来进行约束时使用。

列级约束：

• 约束直接添加在列的后面。

分类：

• 非空约束：not null
• 唯一性约束：unique
• 主键约束：primary key
• 外键约束：foreign key
• 检查约束：check

1.非空约束：not null

只有列级约束，没有表级约束。

作用：

非空约束的字段不能为NULL

语法：

在字段的数据类型后加上'not null'关键字。

mysql> drop table if exists t_vip;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table t_vip(
    -> id int,
    -> name varchar(32) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

示例： 

mysql> insert into t_vip(id,name) values('10001','zs');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_vip(name) values('ls');
Query OK, 1 row affected (0.01 sec)

mysql> inser into t_vip(id) values('10003');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'inser into t_vip(id) values('10003')' at line 1
//报错

mysql> select * from t_vip;
+-------+------+
| id    | name |
+-------+------+
| 10001 | zs   |
|  NULL | ls   |
+-------+------+
2 rows in set (0.00 sec)

2.唯一性约束 ：unique

作用：

唯一性约束的字段不能重复，但是可以为NULL。

语法：

在字段的数据类型后，加上unique关键字。

mysql> drop table if exists t_vip;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_vip(
    -> id int,
    -> name varchar(32) unique,
    -> age int
    -> );
Query OK, 0 rows affected (0.03 sec)

示例：

mysql> insert into t_vip(id,name,age) values
    -> (001,'zs',17),
    -> (002,'ls',20),
    -> (003,'ww',19);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t_vip(id,name,age) values
    -> (004,'zs',27);
ERROR 1062 (23000): Duplicate entry 'zs' for key 't_vip.name'
//报错，name字段的数据出现重复

mysql> insert into t_vip(id) values
    -> (004),(005);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
//unique约束的字段可以为NULL

mysql> select * from t_vip;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | zs   |   17 |
|    2 | ls   |   20 |
|    3 | ww   |   19 |
|    4 | NULL | NULL |
|    5 | NULL | NULL |
+------+------+------+
5 rows in set (0.00 sec)

两个字段联合使用唯一性约束

语法：

在约束的字段后，加上unique(约束字段1，约束字段2.....)

mysql> create table t_vip(
    -> id int,
    -> name varchar(32),
    -> age int,
    -> unique(name,age)  //name和age联合起来进行唯一性约束
    -> );
Query OK, 0 rows affected (0.03 sec)

示例： 

mysql> insert into t_vip(id,name,age) values
    -> (001,'zs',18),
    -> (002,'zs',20);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_vip;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | zs   |   18 |
|    2 | zs   |   20 |
+------+------+------+
2 rows in set (0.00 sec)

对一个字段同时使用unique和not null关键字

注意：MySQL遇到这种情况，会自动把该约束变为主键约束（Primary Key）

示例：

mysql> create table t_vip(
    -> id int,
    -> name varchar(32) unique not null,
    -> age int,
    -> );

3.主键约束（primary key）

1.相关术语：

• 主键约束：一种约束
• 主键字段：添加了主键约束的字段
• 主键值：主键字段下的每一个值

2.主键的作用：

• 主键是一张表中，每一行记录的唯一标识，相当于身份证号。
• 任何一张表都应该有主键，没有主键，表无效。

3.特点：

• 相当于unique+not null，不能重复也不能为空。
• 一张表中，主键只能有一个（可以两个字段做一个主键构成一个复合主键）
• 主键值通常是定长的数字，建议使用int，bigint，char类型的数据作为主键，不建议用varchar

4.主键的分类：

第一种：

• 单一主键
• 复合主键

第二种：

• 自然主键：一般使用自然主键
• 业务主键：不建议使用业务主键，因为若主键与业务挂钩，业务发生变动时可能会影响到主键

5.语法：

• 列级约束：在字段名和数据类型后，加上primary key关键字
• 表级约束：在最后补上"primary key(约束字段）"

//列级约束
mysql> drop table if exists t_vip;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_vip(
    -> id int primary key,   //添加主键约束
    -> name varchar(32),
    -> age int
    -> );
Query OK, 0 rows affected (0.03 sec)


//表级约束
mysql> create table t_vip(
    -> id int,   
    -> name varchar(32),
    -> age int,
       primary key(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

6.auto_increment：

MySQL中的一种维护主键的机制，会从1开始自增。

使用了auto-increment的主键，在insert插入数据时可以省略字段名。

语法：

mysql> drop table if exists t_vip;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_vip(
    -> id int primary key auto_increment,  //维护机制，表示从1开始自增
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_vip(name) values  //id有auto_increment维护机制，可以省略字段名
    -> ('zs'),
    -> ('zs'),
    -> ('zs');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_vip;
+----+------+
| id | name |
+----+------+
|  1 | zs   |
|  2 | zs   |
|  3 | zs   |
+----+------+
3 rows in set (0.00 sec)

4.外键约束(foreign key)

1.相关术语：

• 外键约束：一种约束
• 主键字段：添加了外键约束的字段
• 主键值：外键字段下的每一个值

2.作用：

让表与表之间的数据建立关联，使数据更加完整，关联性更强。

3.语法：

foreign key(外键) references 父表(被引用的字段) 

示例：

请设计数据库表，描述“班级和学生”的信息。

方法一： 

将班级与学生的信息放在一张表上，显示学生编号、学生姓名、学生班级编号和学生班级名称。

缺点：数据沉余，空间浪费。

方法二：

分为学生表t_student和班级表t_class两张表。学生表显示：学生编号、学生姓名、班级编号；

班级表显示：班级编号和班级名称，两张表通过班级编号的连接起来。

注意：

当t_student表中的cno没有条件约束时，可能会出现数据的失误（eg:不小心把101输入为102等情况）。为避免这种情况，保证cno的数据都是100或者101，可以通过对cno字段使用外键约束来实现。则cno是外键字段，cno字段中的每一个值都是外键值。

使用外键约束：

1.被引用来实现外键约束的表称为父表，使用外键约束的表称为子表。

在这个案例中，通过引用t_class表中的班级编号classno，对t_student表中的cno字段进行外键约束。因此，t_class是父表，t_student是子表。

注意：

父表中被引用的字段：不一定是主键，但是一定具有唯一性unique。

子表中的外键：可以为NULL。

一些顺序：

创建表时：先创建父表，再创建子表。

插入数据时：先插入父表，再插入子表。

删除数据时：先删除子表，再删除父表。

删除表时：先删除子表，再删除父表。

--创建父表t_class
mysql> create table t_class(
    -> classno int primary key, //classno作为t_class的主键
    -> classname varchar(255)
    -> );
Query OK, 0 rows affected (0.04 sec)

--创建子表t_student
mysql> create table t_student(
    -> no int primary key auto_increment,  //no作为t_class的主键
    -> name varchar(255),
    -> cno int,
    -> foreign key(cno) references t_class(classno) 
    -> );                 //cno作为外键，cno引用父表中的classno字段
Query OK, 0 rows affected (0.03 sec)

--给父表t_class插入数据
mysql> insert into t_class(classno,classname) values
    -> (101,'Guangdong,Guangzhou,Sunshine High School Class 1'),
    -> (102,'Guangdong,Guangzhou,Sunshine High School Class 2');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

--给子表t_student插入数据
mysql> insert into t_student(name,cno) values
    -> ('zs',101),
    -> ('ls',101),
    -> ('ww',101),
    -> ('zl',101),
    -> ('mn',102),
    -> ('sg',102);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

--显示父表t_class
mysql> select * from t_class;
+---------+--------------------------------------------------+
| classno | classname                                        |
+---------+--------------------------------------------------+
|     101 | Guangdong,Guangzhou,Sunshine High School Class 1 |
|     102 | Guangdong,Guangzhou,Sunshine High School Class 2 |
+---------+--------------------------------------------------+
2 rows in set (0.00 sec)

--显示子表
mysql> select * from t_student;
+----+------+------+
| no | name | cno  |
+----+------+------+
|  1 | zs   |  101 |
|  2 | ls   |  101 |
|  3 | ww   |  101 |
|  4 | zl   |  101 |
|  5 | mn   |  102 |
|  6 | sg   |  102 |
+----+------+------+
6 rows in set (0.00 sec)

六、存储引擎

1.定义：

• 一个表存储/组织数据的方式
• 不同的存储引擎，存储方式不同

2.指定存储引擎和字符编码方式

表的存储引擎和字符编码方式是在建表的时候指定的，

建表的时候在最后小括号')'的后面指定存储引擎和字符编码方式。

ENGINE指定存储引擎，CHARSET指定字符编码方式。

mysql> show create table t_class;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                             |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_class | CREATE TABLE `t_class` (
  `classno` int NOT NULL,
  `classname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`classno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

结论：

MySQL默认的存储引擎是InnoDB，MySQL默认的字符编码方式是utf8mb4。

//创建t_product表，将字符编码方式改为gbk(可以输入中文)
mysql> create table t_product(
    -> id int primary key auto_increment;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
mysql> create table t_product(
    -> id int primary key auto_increment,
    -> name varchar(255)
    -> )ENGINE=InnoDB DEFAULT CHARSET=gbk; --修改为gbk
Query OK, 0 rows affected (0.02 sec)

//展示创建t_product表的sql语句
mysql> show create table t_product;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_product | CREATE TABLE `t_product` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |  --修改成功
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 3.查看当前MySQL的版本：

命令：

mysql> select version();

命令操作：  

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.30    |
+-----------+
1 row in set (0.00 sec)

4.查看当前的MySQL支持哪些存储引擎：

命令：

mysql> show engines \G;

命令操作： 

mysql> show engines \G;
*************************** 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
9 rows in set (0.00 sec)

ERROR:
No query specified

5.MySQL常用的存储引擎

MyISAM存储引擎：

他管理的表使用三个文件表示每个表：

• 格式文件 —— 存储表结构的定义(mytable.frm)
• 数据文件 —— 存储表行的内容（mytable.MYD）
• 索引文件 —— 存储表上索引(mytable.MYI) 

优点：节省空间（可以被转换为压缩、只读表）

缺点：安全性低（不支持事务）

索引是一本书的目录，可以被转换为压缩、只读表来节省空间。

在一张表中，只要是主键或者加有unique约束的字段上，会自动创建索引。

InnoDB存储引擎：

• 是MySQL默认的存储引擎
• 支持事务，支持MySQL数据库崩溃后的自动恢复引擎

他管理的表有如下特征：

• 在数据库目录里以.frm格式文件表示
• 提供一组用来提供事务性活动的日志文件
• 表的内容存储在InnoDB的表空间tablespace中（表空间用来存储数据+索引）
• 用COMMIT，SAVEPOINT和ROLLBACK支持事务的处理。
• 提供全ACID兼容
• 多版本（MVCC）和行级锁定
• 支持外键及引用的完整性，包括级联删除和更新

优点：安全性很高（支持事务）

缺点：效率较低，不能节省空间（不能压缩，转换为只读表）

MEMORY存储引擎：

特点：其数据存储在内存中（一断电就消失），且行的长度固定，因此速度极快。

他管理的表有如下特征：

• 在数据库目录里以.frm格式文件表示
• 表数据与索引被存储在内存中（目的：为了查询快）
• 表级锁机制
• 不能包含TEXT或BLOB字段

优点：查询效率最高（不需要和硬盘进行交互）

缺点：安全性低（因为数据和索引都在内存当中，关机后数据就消失）