other
mysql

03数据表的编辑、事务、索引

# 创建表

建表语句的语法：

create table 表名称(
  字段名 数据类型 约束,
  字段名2 数据类型 约束,
  ...
);

1
2
3
4
5

# 关于MySQL当中数据类型：

int	整数	java中的init
bigint	长整数（java中的long）	java中的long
float	浮点数	java中的float double
char	定长字符串	String
varchar	可变长字符串（最长存放255个）	StringBuffer/StringBuilder
date	日期	java.sql.Date
BLOB	二进制大文件（存储图片或者视频流媒体文件）	Object
CLOB	字符大对象（存储大的文本，可以存放4G的字符串文件）	Object

# char和varchar的区别：

char属于固定长度字符串，性能更高。比如：日期值、性别类型
varchar是可变换长度的数据字符串，最初为255个。长度会随时变化，节省空间。

# BLOB和CLOB类型的使用

主要用来存放大对象，比如存放电影数据表，海报的类型使用BLOB，简介的类型使用CLOB。

id(int)	name(varchar)	images(BLOB)	introduce(CLOB)
1	hello
2	sql

# 创建学生表

学生信息：
学号： bigint
姓名： varchar
性别： char
班级编号： int
生日： char
------------------------
create table t_student(
 sNo bigint,
 name varchar(255),
 gender char(1),
 classNo varchar(255),
 birth char(10)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 插入数据

# insert插入数据语法：

语法格式：insert into 表名(字段1,字段2,字段3,字段4, ...) values(value1, value2, value3, value4, ...) 第一种：insert into 表名(字段1,字段2,字段3,字段4, ...) values(value1, value2, value3, value4, ...); 第二种：insert into 表名(字段1) values(value1); 第三种：insert into 表名 values(value1, value2, value3, value4); 这种情况values必须是全部字段的值。第四种：insert into 表名(字段1) values(value1), (value2), (value3), (value4); 用逗号隔开，一次插入多条数据

# 给学生表插入数据：

insert into t_student(sNo, name, gender, classNo, birth) values(1,'zs','f','class1', '2022-10-22');
insert into t_student(sNo, name, gender, classNo, birth) values(2,'ls','m','class2', '2021-10-22');

mysql> select * from t_student;
+------+------+--------+---------+------------+
| sNo  | name | gender | classNo | birth      |
+------+------+--------+---------+------------+
|    1 | zs   | f      | class1  | 2022-10-22 |
|    2 | ls   | m      | class2  | 2021-10-22 |
+------+------+--------+---------+------------+

mysql> insert into t_student(name) values('wangwu');
mysql> select * from t_student;
+------+--------+--------+---------+------------+
| sNo  | name   | gender | classNo | birth      |
+------+--------+--------+---------+------------+
|    1 | zs     | f      | class1  | 2022-10-22 |
|    2 | ls     | m      | class2  | 2021-10-22 |
| NULL | wangwu | NULL   | NULL    | NULL       |
+------+--------+--------+---------+------------+
如果字段没有值，则设置为NULL，因为在创建表时设置的default为NULL。
mysql> desc t_student;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| sNo     | bigint(20)   | YES  |     | NULL    |       |
| name    | varchar(255) | YES  |     | NULL    |       |
| gender  | char(1)      | YES  |     | NULL    |       |
| classNo | varchar(255) | YES  |     | NULL    |       |
| birth   | char(10)     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

# 删除表，之后在插入数据

# 删除表 drop 需要先进行表是否存在的判断

drop table if exists tableName;

#如果t_student表存在，就删除表
drop table if exists t_student;

#删除表后，重新创建t_student表
create table t_student(
  sNo bigint,
  name varchar(255),
  gender char(1) default 'm',
  classNo varchar(255),
  bith char(10)
);
mysql> desc t_student;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| sNo     | bigint(20)   | YES  |     | NULL    |       |
| name    | varchar(255) | YES  |     | NULL    |       |
| gender  | char(1)      | YES  |     | m       |       |
| classNo | varchar(255) | YES  |     | NULL    |       |
| birth   | char(10)     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

insert into t_student(name) values('wang5');
mysql> select * from t_student;
+------+-------+--------+---------+-------+
| sNo  | name  | gender | classNo | birth |
+------+-------+--------+---------+-------+
| NULL | wang5 | m      | NULL    | NULL  |
+------+-------+--------+---------+-------+
此时gender 默认就是m值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

# 修改表数据

# 先复制一张表，对复制出来的表进行数据修改

create table copyTableName as select * from t_student;

#复制一张学生表
create table t_student1 as select * from t_student;

#复制一张员工表，只包括员工编号和薪水
create table EMP1 as select empno,sal from EMP;
mysql> select * from EMP1;
+-------+---------+
| empno | sal     |
+-------+---------+
|  7369 |  800.00 |
|  7499 | 1600.00 |
|  7521 | 1250.00 |
|  7566 | 2975.00 |
|  7654 | 1250.00 |
|  7698 | 2850.00 |
|  7782 | 2450.00 |
|  7788 | 3000.00 |
|  7839 | 5000.00 |
|  7844 | 1500.00 |
|  7876 | 1100.00 |
|  7900 |  950.00 |
|  7902 | 3000.00 |
|  7934 | 1300.00 |
+-------+---------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# 将查询结果插入到一张表中,前提必须确保字段一致

create table dept1 as select * from DEPT;

insert into dept1 select * from DEPT;
mysql> select * from dept1;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 修改表数据

update更新，语法格式： update 表名 set 字段名1=值1, 字段名2=值2 ... where 条件; 注意：没有where条件，整张表的数据全部更新。

#将部门deptno为10的LOC修改为上海, 将部门名称修改为HR
mysql> update DEPT set LOC='shanghai',dname='HR' where deptno = 10;
mysql> select * from DEPT;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | HR         | shanghai |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

1
2
3
4
5
6
7
8
9
10
11

# 删除表数据

delete语法格式： delete from 表名 where 条件; 没有where条件，全部删除

mysql> create table dept2 as select * from dept;
mysql> delete from dept2 where deptno = 10;
mysql> select * from dept2;
+--------+------------+---------+
| DEPTNO | DNAME      | LOC     |
+--------+------------+---------+
|     20 | RESEARCH   | DALLAS  |
|     30 | SALES      | CHICAGO |
|     40 | OPERATIONS | BOSTON  |
+--------+------------+---------+

1
2
3
4
5
6
7
8
9
10

删除表所有数据 delete from dept2; 怎么删除大表de数据？ 使用 truncate ，风险非常大，数据无法回滚。

mysql> truncate table dept2;   //表被截断，数据不可回滚
mysql> select * from dept2;
Empty set (0.01 sec)

1
2
3

# 修改表结构alter

就是修改DDL(create, drop, alter);类型语句

create table 表名(...)创建表

drop table if exists 表名 删除表

alter table 表名 修改表结构字段类型

1
2
3
4
5

# ADD 添加字段

# 给表 dept2 添加一个coll字段，类型int，并使用first放置第一个位置
mysql> alter table dept2 add column coll int first;
mysql> desc dept2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| coll   | int(11)     | YES  |     | NULL    |       |
| DEPTNO | int(2)      | NO   |     | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

1
2
3
4
5
6
7
8
9
10
11

# 给 dept2 添加col2字段，类型int，并且放置到dname之后
mysql> alter table dept2 add column col2 int after dname;
mysql> desc dept2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| coll   | int(11)     | YES  |     | NULL    |       |
| DEPTNO | int(2)      | NO   |     | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| col2   | int(11)     | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

1
2
3
4
5
6
7
8
9
10
11
12

# MODIFY修改字段类型

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

# 修改表 dept2 中的 col2 字段的类型为 varchar
mysql> alter table dept2 modify col2 varchar(255);
mysql> desc dept2;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| coll   | int(11)      | YES  |     | NULL    |       |
| DEPTNO | int(2)       | NO   |     | NULL    |       |
| DNAME  | varchar(14)  | YES  |     | NULL    |       |
| col2   | varchar(255) | YES  |     | NULL    |       |
| LOC    | varchar(13)  | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

1
2
3
4
5
6
7
8
9
10
11
12

# DROP删除字段

ALTER TABLE <表名> DROP <字段名>;

# 删除表 dept2 中的 coll字段
mysql> alter table dept2 drop coll;
mysql> desc dept2;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| DEPTNO | int(2)       | NO   |     | NULL    |       |
| DNAME  | varchar(14)  | YES  |     | NULL    |       |
| col2   | varchar(255) | YES  |     | NULL    |       |
| LOC    | varchar(13)  | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

1
2
3
4
5
6
7
8
9
10
11

# CHANGE修改字段名称

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;

#修改dept2 字段 col2为部门人数，类型为int

mysql> alter table dept2 change col2 deptpeoplenumber int;
mysql> desc dept2;
+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| DEPTNO           | int(2)      | NO   |     | NULL    |       |
| DNAME            | varchar(14) | YES  |     | NULL    |       |
| deptpeoplenumber | int(11)     | YES  |     | NULL    |       |
| LOC              | varchar(13) | YES  |     | NULL    |       |
+------------------+-------------+------+-----+---------+-------+

1
2
3
4
5
6
7
8
9
10
11
12

# RENAME修改表名

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

#修改表 dept2名称为 deptx

mysql> alter table dept2 rename to deptx;
mysql> show tables;
+-----------------+
| Tables_in_pnode |
+-----------------+
| DEPT            |
| dept1           |
| deptx           |
+-----------------+
front1234!

1
2
3
4
5
6
7
8
9
10
11
12

# 约束【Constraint】作用以及常见约束

# 什么是约束？常见的约束有哪些？

在创建表的时候，可以给表的字段添加相应的约束，目的是为了让表中的数据具有合法性、有效性、完整性。 常见的约束：

非空约束（not null）：约束的字段不能为 NULL
唯一约束（unique）：约束的字段不能重复
主键约束（primary key）：约束的字段即不能为NULL，也不能为重复，简称PK
外键约束（foreign key）：简称 FK
检查约束（check）：注意Oracle数据库有check约束，但是mysql不支持；

# 非空约束 not null

drop table if exists dept1;
create table dept1(
 id int,
 name varchar(255) not null
);
mysql> insert into dept1(id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

1
2
3
4
5
6
7

# 唯一性unique约束

唯一约束的字段具有唯一性，不能重复，但是unqiue可以为NULL，NULL和NULL中间不能=；

mysql> create table t_user(
    -> id int,
    -> username varchar(255) unique
    -> );

mysql> insert into t_user values(1, 'zs');

mysql> insert into t_user values(2, 'zs');
ERROR 1062 (23000): Duplicate entry 'zs' for key 'username'

1
2
3
4
5
6
7
8
9

给2列或者多列添加**unique**约束, 是多个字段联合起来判断唯一性

mysql> drop table if exists t_user;
mysql> create table t_user(
    ->  id int,
    ->  usercode varchar(255),
    ->  username varchar(255),
    ->  unique(usercode, username)
    -> );
mysql> insert into t_user values(1,'101', 'zs');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t_user values(2,'102', 'zs');
Query OK, 1 row affected (0.08 sec)

mysql> insert into t_user values(2,'102', 'zs');
ERROR 1062 (23000): Duplicate entry '102-zs' for key 'usercode'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 主键约束primary key

**使用：字段后添加 ****primary key**

mysql> drop table if exists t_user;
create table t_user(
	id int primary key,
  username varchar(255),
  email varchar(255)
);
insert into t_user values(1, 'zs', 'zs@qq.com');
insert into t_user values(2, 'ls', 'ls@qq.com');
insert into t_user values(3, 'ws', 'ss@qq.com');

# 主键不能重复
mysql> insert into t_user value(1, 'jk', 'jk@qq.com');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

# 主键不能为NULL
mysql>insert into t_user(username, email) values('zl', 'zl@qq.com');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 主键的作用：

主键的值，是这一行数据的唯一标识，即使该行其它字段值完全相同，只要主键不同，就是不同的数据。
表设计三范式，第一范式要求任何一张表都应该有主健。

# 主健的分类：

根据主健字段数量来划分：单一主键【常用】、复合主键【不推荐使用】
根据主健性质划分：自然主键、业务主键【使用业务字段设置主键，不推荐使用】

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

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

mysql> drop table if exists t_user;
create table t_user(
  id int,
  username varchar(255),
  primary key(id)
);
insert into t_user(id, username) values(1,'zs');
insert into t_user(id, username) values(2,'ls');

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

1
2
3
4
5
6
7
8
9
10
11

# 主键值自增 auto_increment

mysql> drop table if exists t_user;
create table t_user(
  id int primary key auto_increment,
  username varchar(255)
);
insert into t_user(username) values('zs');
insert into t_user(username) values('ls');
insert into t_user(username) values('we');

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  2 | ls       |
|  3 | we       |
+----+----------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 外键约束foreign key

外健主要为了解决数据存在冗余的问题。业务背景：设置一张学生表，维护学生和班级信息 第一种方案：一张表存储所有数据，缺陷数据冗余，不推荐使用

no(pk)   name    classno    classname
-----------------------------------------------
1        zs1       101       高中1班
2        zs2       101       高中1班
3        zs3       102       高中2班
4        zs4       102       高中2班
5        zs5       102       高中2班

1
2
3
4
5
6
7

第二种方案：拆分为2张表

t_class 班级表  (父表)
cno(pk)      cname
-----------------------
101          高中1班
102	         高中2班

t_student   学生表。（子表）
sno(pk)    sname   classno(fk)
-------------------------------
1           zs1     101
2           zs2     101
3           zs3     102
4           zs4     102
5           zs5     102

1
2
3
4
5
6
7
8
9
10
11
12
13
14

操作顺序要求：

删除数据时，先删除子表，再删除父表
添加数据时，先添加父表，在添加子表
创建表，先创建父表，在创建子表
删除表的时候，先删除子表，再删除父表

#删除表
drop table if exists t_student;
drop table if exists t_class;

#创建表
create table t_class(
  cno int primary key,
  cname varchar(255)
);
create table t_student(
  sno int primary key,
  sname varchar(255),
  classno int,
  foreign key(classno) references t_class(cno)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

**foreign key(classno) references t_class(cno)**外键关联2张表，

#插入数据
insert into t_class values(101, 'class101');
insert into t_class values(102, 'class102');

insert into t_student values(1,'zs1', 101);
insert into t_student values(2,'zs2', 101);
insert into t_student values(3,'zs3', 102);
insert into t_student values(4,'zs4', 102);
insert into t_student values(5,'zs5', 102);

mysql> select * from t_class;
+-----+----------+
| cno | cname    |
+-----+----------+
| 101 | class101 |
| 102 | class102 |
+-----+----------+

mysql> select * from t_student;
+------+-------+---------+
| sno  | sname | classno |
+------+-------+---------+
|    1 | zs1   |     101 |
|    2 | zs2   |     101 |
|    3 | zs3   |     102 |
|    4 | zs4   |     102 |
|    5 | zs5   |     102 |
+------+-------+---------+

#外健的值不能随便加，必须是另外一张表中存在的数据
insert into t_student values(6, 'zs6', 103);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`pnode`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

#外健可以为NULL
insert into t_student(sno, sname) values(7, 'zs7');
mysql> select * from t_student;
+------+-------+---------+
| sno  | sname | classno |
+------+-------+---------+
|    1 | zs1   |     101 |
|    2 | zs2   |     101 |
|    3 | zs3   |     102 |
|    4 | zs4   |     102 |
|    5 | zs5   |     102 |
|    7 | zs7   |    NULL |
+------+-------+---------+

1
2
3
4
5
6
7
8
9
10
11
12
13

外键字段引用其它表的某个字段，被引用的字段在另外表中不一定是主健，但是必须具有唯一性 unique 约束;

# 常见的存储引擎

存储引擎是表存储数据的方式，常见的存储引擎，MyISAM、InnoDB、MEMORY三个。 mysql默认存储引擎InnoDB。

#查看建表语句
show create table EMP;
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EMP   | CREATE TABLE `EMP` (
  `EMPNO` int(4) NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int(4) DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int(2) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql默认为 ENGINE=InnoDB；默认的字符集采用UTF8

#完整的建表语句
create table t_x(
  id int(11) default NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1
2
3
4

# 查看当前mysql支持的存储引擎

show engines \G;
*************************** 1. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
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: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
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: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

# MyISAM

Engine: MyISAM
			  Support: YES
			  Comment: MyISAM storage engine
		Transactions: NO
					 XA: NO
		  Savepoints: NO
------------------------------
MyISAM这种存储引擎不支持事务。
		MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。
		MyISAM采用三个文件组织一张表：
			xxx.frm（存储格式的文件）
			xxx.MYD（存储表中数据的文件）
			xxx.MYI（存储表中索引的文件）

1
2
3
4
5
6
7
8
9
10
11
12
13

优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。缺点：不支持事务。

# InnoDB

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

1
2
3
4
5
6
7

优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。

表的结构存储在xxx.frm文件中数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。 InnoDB支持级联删除和级联更新。

# MEMORY

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

1
2
3
4
5
6
7

缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。优点：查询速度最快。以前叫做HEPA引擎。

# 事务的使用

# 事务原理

一个事务是一个完整的业务逻辑单元，不可再分。比如：银行账户转账，从A账户向B账户转账10000.需要执行两条update语句：

update t_act set balance = balance - 10000 where actno = 'act-001'; 		
update t_act set balance = balance + 10000 where actno = 'act-002';

1
2

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

# 和事务相关的语句只有：DML语句。（insert delete update）；

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

# 事务的四大特性

事务包括四大特性：ACID A: 原子性：事务是最小的工作单元，不可再分。 C: 一致性：事务必须保证多条DML语句同时成功或者同时失败。 I：隔离性：事务A与事务B之间具有隔离。 D：持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。

# 事务隔离性的分类

事务隔离性存在隔离级别，理论上隔离级别包括4个：
  第一级别：读未提交（read uncommitted）
    对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
    读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
  第二级别：读已提交（read committed）
    对方事务提交之后的数据我方可以读取到。
    这种隔离级别解决了: 脏读现象没有了。
    读已提交存在的问题是：不可重复读。
  第三级别：可重复读（repeatable read）
    这种隔离级别解决了：不可重复读问题。
    这种隔离级别存在的问题是：读取到的数据是幻象。
  第四级别：序列化读/串行化读（serializable） 
    解决了所有问题。
    效率低。需要事务排队。
  
  oracle数据库默认的隔离级别是：读已提交。
  mysql数据库默认的隔离级别是：可重复读。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 演示事务

* mysql事务默认情况下是自动提交的。
 （什么是自动提交？只要执行任意一条DML语句则提交一次。）
  怎么关闭自动提交？start transaction;
	
* 准备表：
  drop table if exists t_user;
  create table t_user(
    id int primary key auto_increment,
    username varchar(255)
  );
	
* 演示：mysql中的事务是支持自动提交的，只要执行一条DML，则提交一次。
  mysql> insert into t_user(username) values('zs');
  Query OK, 1 row affected (0.03 sec)

  mysql> select * from t_user;
  +----+----------+
  | id | username |
  +----+----------+
  |  1 | zs       |
  +----+----------+
  1 row 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)
	
* 演示：使用start transaction;   关闭自动提交机制。
  mysql> start transaction;
  Query OK, 0 rows affected (0.00 sec)

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

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

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

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

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

  mysql> select * from t_user;
  +----+----------+
  | id | username |
  +----+----------+
  |  1 | zs       |
  +----+----------+
  1 row in set (0.00 sec)
  --------------------------------------------------------------------
  mysql> start transaction;
  Query OK, 0 rows affected (0.00 sec)

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

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

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

  mysql> select * from t_user;
  +----+----------+
  | id | username |
  +----+----------+
  |  1 | zs       |
  |  4 | wangwu   |
  |  5 | rose     |
  |  6 | jack     |
  +----+----------+
  4 rows in set (0.00 sec)
* 进行提交， rollback也是提交过的数据
  mysql> commit;  
  Query OK, 0 rows affected (0.04 sec)

  mysql> select * from t_user;
  +----+----------+
  | id | username |
  +----+----------+
  |  1 | zs       |
  |  4 | wangwu   |
  |  5 | rose     |
  |  6 | jack     |
  +----+----------+
  4 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       |
  |  4 | wangwu   |
  |  5 | rose     |
  |  6 | jack     |
  +----+----------+
  4 rows in set (0.00 sec)

* 演示两个事务，假如隔离级别，设置隔离级别
  演示第1级别：读未提交
    set global transaction isolation level read uncommitted;
  演示第2级别：读已提交
    set global transaction isolation level read committed;
  演示第3级别：可重复读
    set global transaction isolation level repeatable read;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132

# 索引 Index

# 索引的定义以及作用

索引相当于给数据添加了一个目录，当查询数据时通过目录可以快速查询出对应数据。添加索引内部使用BTree算法对数据做了分类计算。索引快速查询的原理，缩小了扫描范围。添加索引是给某个或某些字段添加。

explain select ename,sal from EMP where sal = 3000;
#当 sal 字段没添加索引，sql语句会全表扫描，查询sal符合条件的数据
#当 sal 字段添加索引，sql语句根据索引查询，快速定位结果。内部采用BTree对数据进行分类

1
2
3
4

# 添加索引的前提

数据量很大
该字段很少有DML操作
该字段经常出现在where子句中。

主键或者具有unique约束的字段自动添加索引。

# 使用 `explain` 查看sql执行情况

mysql> explain select ename,sal from EMP where sal = 3000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

可以看到 type 为 ALL，表示全表扫描。

1
2
3
4
5
6
7
8

# 给字段添加索引

# 创建索引 index .. on ..

create index 索引名 on 表名(字段名);

create index emp_sal_index on EMP(sal);

mysql> explain select ename,sal from EMP where sal = 3000;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | EMP   | NULL       | ref  | emp_sal_index | emp_sal_index | 9       | const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
可以看到 type 为 ref，总共扫描了 2行 rows数据。

1
2
3
4
5
6
7
8
9

# 删除索引

drop index 索引名 on 表名;

# 索引的分类

单一索引：单字段索引复合索引：给多个字段联合起来添加索引主键索引：主键上自动添加索引唯一索引：有unique约束的字段会自动添加索引

# 视图view

视图的目的是保障数据安全, 可以把真实的数据字段给隐藏。操作视图会操作原表的数据。

# 创建和删除view

create table emp_bak as select * from emp;
create view emp_view as select empno,ename,sal from emp_bak;
update emp_view set ename='sam',sal=9999 where empno = 7369; // 通过视图修改原表数据。
delete from emp_view where empno = 7369; // 通过视图删除原表数据。

1
2
3
4

视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，程序员只对视图对象进行CRUD。

# 只有DQL才能创建view

create view myview as select empno,ename from emp;
drop view myview;

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

1
2
3
4

# 数据库的导入导出

# 导出

在系统的终端执行命令：【是在系统终端执行命令，不是在mysql中】

导出指定库 pnode 的指定表 emp；
mysqldump pnode emp >  ~/Desktop/pnode.sql -uroot -p

导出库的所有数据
mysqldump pnode >  ~/Desktop/pnodeall.sql -uroot -p

1
2
3
4
5

# 导入，进入到数据库中操作

create database pnode;
use pnode;
source ~/Desktop/pnodeall.sql

1
2
3

# 数据库设计的三范式

设计表的依据：按照三范式设计的表不会出现数据冗余。

# 三范式：

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

第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。
  多对多？三张表，关系表两个外键。
  t_student学生表
  sno(pk)		sname
  -------------------
  1				张三
  2				李四
  3				王五

  t_teacher 讲师表
  tno(pk)		tname
  ---------------------
  1				王老师
  2				张老师
  3				李老师

  t_student_teacher_relation 学生讲师关系表
  id(pk)		sno(fk)		tno(fk)
  ----------------------------------
  1				1				3
  2				1				1
  3				2				2
  4				2				3
  5				3				1
  6				3				3
	
第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。
  一对多？两张表，多的表加外键。
  班级t_class
  cno(pk)			cname
  --------------------------
  1					班级1
  2					班级2

  学生t_student
  sno(pk)			sname				classno(fk)
  ---------------------------------------------
  101				张1				1
  102				张2				1
  103				张3				2
  104				张4				2
  105				张5				2

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

# 一对一表的设计：

一对一设计有两种方案：主键共享
  t_user_login  用户登录表
  id(pk)		username			password
  --------------------------------------
  1				zs					123
  2				ls					456

  t_user_detail 用户详细信息表
  id(pk+fk)	realname			tel			....
  ------------------------------------------------
  1				张三				1111111111
  2				李四				1111415621

一对一设计有两种方案：外键唯一。
  t_user_login  用户登录表
  id(pk)		username			password
  --------------------------------------
  1				zs					123
  2				ls					456

  t_user_detail 用户详细信息表
  id(pk)	   realname			tel				userid(fk+unique)....
  -----------------------------------------------------------
  1				张三				1111111111		2
  2				李四				1111415621		1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

本站部分内容来源网络转载，如有侵权，请联系删除；本站不负任何版权责任！

编辑

上次更新: 2025/10/22, 08:49:36

← 02关联表查询 04常见的查询sql面试题→

03数据表的编辑、事务、索引

# 创建表

# 关于MySQL当中数据类型：

# char和varchar的区别：

# BLOB和CLOB类型的使用

# 创建学生表

# 插入数据

# insert插入数据语法：

# 给学生表插入数据：

# 删除表，之后在插入数据

# 删除表 drop 需要先进行表是否存在的判断

# 修改表数据

# 先复制一张表，对复制出来的表进行数据修改

# 将查询结果插入到一张表中,前提必须确保字段一致

# 修改表数据

# 删除表数据

# 修改表结构alter

# ADD 添加字段

# MODIFY修改字段类型

# DROP删除字段

# CHANGE修改字段名称

# RENAME修改表名

# 约束【Constraint】作用以及常见约束

# 什么是约束？常见的约束有哪些？

# 非空约束 not null

# 唯一性unique约束

# 主键约束primary key

# 主键的作用：

# 主健的分类：

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

# 主键值自增 auto_increment

# 外键约束foreign key

# 常见的存储引擎

# 查看当前mysql支持的存储引擎

# MyISAM

# InnoDB

# MEMORY

# 事务的使用

# 事务原理

# 和事务相关的语句只有：DML语句。（insert delete update）；

# 事务的四大特性

# 事务隔离性的分类

# 演示事务

# 索引 Index

# 索引的定义以及作用

# 添加索引的前提

# 使用 explain 查看sql执行情况

# 给字段添加索引

# 创建索引 index .. on ..

# 删除索引

# 索引的分类

# 视图view

# 创建和删除view

# 只有DQL才能创建view

# 数据库的导入导出

# 导出

# 导入，进入到数据库中操作

# 数据库设计的三范式

# 三范式：

# 一对一表的设计：

# 使用 `explain` 查看sql执行情况