# 核心技术

## 一、数据表基本操作

### 1、创建数据表

创建数据表：指的是在已经创建好的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性）约束的过程

1、语法形式

数据表属于数据库，在创建数据表之前，应该使用语句“USE <数据库名>”指定操作是在哪个数据库中进行，如果没有选择数据库，直接创建数据表，系统会显示“No database selected”的错误

```
## 语法形式：
CREATE TABLE <表名>    
(    
字段名1，数据类型[列级别约束条件][默认值],
字段名2，数据类型[列级别约束条件][默认值],    
……    
[表级别约束条件]
);
```

建表时注意：

1. 要创建的表的名称，不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等。
2. 数据表中每一个列（字段）的名称和数据类型，如果创建多个列，要用逗号隔离开

```
## 实例
create database aa;
use aa;
create table ceshi(id int(11),name varchar(25),depid int(11),gongzi float);
show tables;
select 8 from ceshi;
```

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MD_Wg8VMtZfc9A4yXEl%2F-MD_ZmxvFFKI7J9UyIjx%2Fimage.png?alt=media\&token=dadf4894-d935-499b-a944-5f4376c03eb9)

2、主键约束

主键，又称主码，是表中一列或多列的组合。

主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空。主键能够唯一标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关系，它们之间是一一对应的。

主键分为两种类型：单字段主键和多字段联合主键。

单字段主键：\
单字段主键是指主键由一个字段组成，SQL语句格式分为以下两种情况：

1. 义列的同时指定主键
2. 在定义完所有列之后指定主键

```
字段名 数据类型 PRIMARY KEY[默认值]
```

![在id字段上设置主键约束](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MD_Wg8VMtZfc9A4yXEl%2F-MD_ipnTSoRJCzm_pBbw%2Fimage.png?alt=media\&token=ec28e420-95a8-4574-8696-31865ac8e749)

![在id字段上设置主键约束](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MD_Wg8VMtZfc9A4yXEl%2F-MD_jXtXLKO_eEMXGRWn%2Fimage.png?alt=media\&token=2251d801-f24c-4f60-a4c8-4f0beb5c40dd)

多字段联合主键：\
多字段联合主键是指主键由多个字段联合组成

```
PRIMARY KEY[字段1，字段2，…，字段n]
```

![id字段和depid字段组合在一起成为该数据表的多字段联合主键](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MD_Wg8VMtZfc9A4yXEl%2F-MD_kFAD7-FsHbQIND6D%2Fimage.png?alt=media\&token=f28d9533-6ec8-4f1b-b6a6-5e634a37e629)

3、外键约束

外键用来在两个表的数据之间建立连接，它可以是一列或者多列。一个表可以有一个或者多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。

外键：下面介绍几个概念。是表中的一个字段，它可以不是本表的主键，但对应另外一个表的主键。外键的主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。例如：部分表tb\_dept的主键id，在员工表tb\_employee5中有一个键deptId与这个id关联。

主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。\
从表（自表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

```
[CONSTRAINT<外键名>]FOREIGN KEY 字段名1[,字段名2,…]    
REFERENCES<主表名> 主键列1[,主键列2,…]
```

![创建了一个name字段，其插入值不能为空（NOT NULL）](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MD_Wg8VMtZfc9A4yXEl%2F-MD_lN00UchrwkI4zdaz%2Fimage.png?alt=media\&token=6f0aade7-cc29-4b98-9d93-6a923ecb39cd)

其中，“外键名”为定义的外键约束的名称，一个表中不能有相同名称的外键；“字段名”表示子表需要添加外键约束的字段列

![在表tb\_employee5上添加了名称为fk\_emp\_dept1的外键约束，外键名称为depid，其依赖于表tb\_dept1的主键id](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MD_Wg8VMtZfc9A4yXEl%2F-MD_mC_RveimgEIEQVEV%2Fimage.png?alt=media\&token=4ff5a253-394c-4d18-9522-e13825094b3a)

注意：\
关联值是在关系型数据库中，相关表之间的联系。它是通过相容或相同的属性或属性组来表示的。子表的外键必须关联父表的主键，且关联字段的数据类型必须匹配，如果类型不一样，则创建子表时，就会出现错误提示

4、非空约束：\
是指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错

```
字段名 数据类型 not null
```

![创建了一个name字段，其插入值不能为空（NOT NULL）](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MD_Wg8VMtZfc9A4yXEl%2F-MD_mqS09oGoa36yb8as%2Fimage.png?alt=media\&token=42bd5a1e-a72b-47f9-a137-e310a4a1a621)

5、唯一性约束\
要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列都不出现重复值

```
## 在定义完列之后直接指定唯一约束
字段名 数据类型 UNIQUE

## 在定义完所有列之后指定唯一约束，语法规则如下。    
[CONSTRAINT <约束名>] UNIQUE(<字段名>)
```

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MD_Wg8VMtZfc9A4yXEl%2F-MD_nMF3OX_MDA6lVMjs%2Fimage.png?alt=media\&token=b5b2b0d3-3938-4cbd-89e4-b8f20486e5a4)

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MD_Wg8VMtZfc9A4yXEl%2F-MD_nXg8LVsp8srCf1WP%2Fimage.png?alt=media\&token=7f04ce14-69d7-4d7d-80a5-9d64de634c8b)

UNIQUE和PRIMARY KEY的区别：

一个表中可以有多个字段声明为UNIQUE，但只能有一个PRIMARY KEY声明；\
声明为PRIMARY KEY的列不允许有空值，但是声明为UNIQUE的字段允许空值的存在

默认约束：\
指定某列的默认值。如男性同学较多，性别就可以默认为“男”。如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为“男”。

```
字段名数据类型 DEFAULT 默认值
```

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MD_Wg8VMtZfc9A4yXEl%2F-MD_nqH5Wu_GOV1BhjHZ%2Fimage.png?alt=media\&token=9dd202f2-8704-4e30-ac05-f18f53e44352)

表tb\_employee7中的字段deptId拥有了一个默认值“1111”，新插入的记录如果没有指定部门编号，则默认设置为“1111”

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MD_Wg8VMtZfc9A4yXEl%2F-MD_o5P_eH8xm548aJk9%2Fimage.png?alt=media\&token=38f63791-bd06-4a5e-93a9-264397a34a84)

7、设置数据表的属性值自动增加

在数据库应用中，经常希望在每次插入新记录时，系统自动生成字段的主键值。可以通过为表主键添加AUTO\_INCREMENT关键字来实现。在MySQL中，默认情况下AUTO\_INCREMENT初始值为1，每新增一条记录，字段自动加1。一个表只能有一个字段使用AUTO\_INCREMENT约束，且该字段必须为主键的一部分。AUTO\_INCREMENT约束的字段可以是任何整数类型（TINYINT、SMALLINT、INT、BIGINT）。

```
字段名 数据类型 AUTO_INCREMENT
```

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MD_Wg8VMtZfc9A4yXEl%2F-MD_oKBcUoiqvXV-TjYX%2Fimage.png?alt=media\&token=30a33dc4-c0eb-4fbe-a9c8-356cf6d8e30e)

创建名称为tb\_employee8的数据表。表中的id字段值在添加记录的时候会自动增加，id字段默认值从1开始，每次添加一条新纪录，该值自动加1

![向表中添加数据](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MD_Wg8VMtZfc9A4yXEl%2F-MD_o_c3gOIbdJQre_jH%2Fimage.png?alt=media\&token=c493498a-1507-40be-9626-ed24fb95826a)

![表内容](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MD_Wg8VMtZfc9A4yXEl%2F-MD_okdvL1edNNKAM5Qe%2Fimage.png?alt=media\&token=b4f43650-146e-4323-a4f2-7604d3af7ab2)

### 2、查看数据表结构

```
## 查看表的基本结构
describe tb_name;
desc tb_name;
```

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MD_Wg8VMtZfc9A4yXEl%2F-MD_pUymChn4mRKOYGi6%2Fimage.png?alt=media\&token=0ff56cef-d0d7-461f-8340-317ff2145304)

其中：

1. NULL：表示该列是否可以存储NULL值。
2. Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次。
3. Default：表示该列是否有默认值，如果有的话值是多少。
4. Extra：表示可以获取的与给定列有关的附加信息，例如AUTO\_INCREMENT等

```
## 查看表详细结构
SHOW CREATE TABLE tb_name(\G);    #\g:是否格式化显示
```

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MD_Wg8VMtZfc9A4yXEl%2F-MD_qURNY4jXmCa_xVxD%2Fimage.png?alt=media\&token=e0ec984b-518a-462a-9669-70f4e01d77d5)

### 3、修改数据表

修改表指的是修改数据库中已经存在的数据表的结构。MySQL使用ALTER TABLE语句修改表。常用的修改表的操作有：修改表名，修改字段数据类型或字段名，增加和删除字段，修改字段的排列位置，更改表的存储引擎，删除表的外键约束等

```
## 修改表名
alter table old_name rename nwe_name;
```

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MDbvV5-5E9IsK9x_elR%2F-MDcPnPe4Xu9TzscLyjN%2Fimage.png?alt=media\&token=bd6b02c5-4dc1-43d0-b332-4092eb52a918)

```
## 修改字段数据类型
ALTER TABLE tb_name MODIFY name VARCHAR(30);
```

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MDbvV5-5E9IsK9x_elR%2F-MDcQYoiRM8htnI0ivkF%2Fimage.png?alt=media\&token=30aa3825-8c2f-44a4-aa34-c205d6fec4cd)

```
## 修改字段名
alter table tb_name change old_id new_id varchar(255);
```

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MDbvV5-5E9IsK9x_elR%2F-MDcSJAeggIt6KLq0JP7%2Fimage.png?alt=media\&token=3bd6310d-9fab-46d2-951f-d73604ce74fc)

由于不同类型的数据在机器中存储的方式及长度并不相同，修改数据类型可能会影响到数据表中已有的数据记录。因此，当数据库中已经有数据时，不要轻易修改数据类型

```
## 添加字段
alter tabe tb_name add new_ziduan varchar(255) first;
alter tabe tb_name add new_ziduan varchar(255) after XXX;
```

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MDbvV5-5E9IsK9x_elR%2F-MDcTQIvXcYkJ91IqLKm%2Fimage.png?alt=media\&token=a77d8e36-9fc8-4511-b676-a8cbda9a7ddc)

“FIRST”“AFTER”“已存在字段名”用于指定新增字段在表中的位置，如果SQL语句中没有这两个参数，则默认将新添加的字段设置为数据表的最后列

```
## 删除字段
alter table tb_name drop ziduan;
```

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MDbvV5-5E9IsK9x_elR%2F-MDcU1M7qTBMbmbBF7OR%2Fimage.png?alt=media\&token=d3cbe8a9-ff97-4e9b-96ed-20625da6d4ec)

```
## 修改字段排序
alter table tb_name modify ziduan1 varchar(255) first;
alter table tb_name modify ziduan1 varchar(255) after XXX;
```

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MDbvV5-5E9IsK9x_elR%2F-MDcVGSe9SQmdZf3_e7j%2Fimage.png?alt=media\&token=724e479e-c48b-4f7e-b754-1933ab6b7775)

```
## 更改表的存储引擎
alter table tb_name ENGINE=InnoDB;    #修改存储引擎为InnoDB
```

```
## 删除表的外键约束
alter table tb_name frop foreign key key_name;
```

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MDcYBDqTy8xjNnLk6qm%2F-MDch3OsotgPW2kdjfoM%2Fimage.png?alt=media\&token=21378545-7b4c-4d3f-b6c4-e05dcd124f42)

### 4、删除数据表

```
## 删除数据表
drop table tb_name;
## 若报错，则说明有外键约束，删除外键约束再删除表即可
```

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MDcYBDqTy8xjNnLk6qm%2F-MDchtEf7tsmGOoPf-qU%2Fimage.png?alt=media\&token=4bccd25d-0c2c-4e6c-b200-412aadbc030c)

在MySQL中AUTO\_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。设置自增属性（AUTO\_INCREMENT）的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增。

## 二、MySQL的数据类型和运算符

### 1、MySQL数据类型

MySQL支持多种数据类型，主要有数值类型、日期/时间类型、字符串类型和二进制类型。

1. 数值数据类型：包括整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，浮点小数类型FLOAT和DOUBLE，定点小数类型DECIMAL。
2. 日期/时间类型：包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。
3. 字符串类型：包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。
4. 二进制类型：包括BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。

1、整数类型

| 类型名称         | 说明      | 存储需求 |
| ------------ | ------- | ---- |
| TINYINT      | 很小的整数   | 1个字节 |
| SMALLINT     | 小的整数    | 2个字节 |
| MEDIUMINT    | 中等大小的整数 | 3个字节 |
| INT（INTEGER） | 普通大小的整数 | 4个字节 |
| BIGINT       | 大整数     | 8个字节 |

![整数型数据类型的取值范围](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MDcYBDqTy8xjNnLk6qm%2F-MDdn9z2M8a5wnqZdkjz%2Fimage.png?alt=media\&token=678558a1-65a0-4b52-815a-8f521c7322b0)

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MDcYBDqTy8xjNnLk6qm%2F-MDduVIfE1I4LbfgPtgs%2Fimage.png?alt=media\&token=2b3e3923-e1c9-48c7-af0a-859dc6c5d27c)

整数类型的显示宽度只用于显示，不能限制取值范围和存储空间，如INT(4)会占用4个字节的存储空间，其允许的最大值是231-1或232-1，而不是9999

2、小数类型

MySQL中使用浮点数和定点数表示小数。浮点类型有两种：单精度浮点类型（FLOAT）和双精度浮点类型（DOUBLE）。定点类型只有一种：DECIMAL。浮点类型和定点类型都可以用（M，D）来表示，其中M称为精度，表示总共的位数；D称为标度，表示小数的位数。浮点类型取值范围为：M(1～255)和D(1～30，且不能大于M-2)。分别表示显示宽度和小数位数。M和D在FLOAT和DOUBLE中是可选的，MySQL 3.23.6以上版本中，FLOAT和DOUBLE类型将被保存为硬件所支持的最大精度。DECIMAL的M和D值在MySQL 3.23.6后可选，默认D值为0，M值为10。

| 类型名称             | 说明         | 存储需求   |
| ---------------- | ---------- | ------ |
| FLOAT            | 单精度浮点数     | 4个字节   |
| DOUBLE           | 双精度浮点数     | 8个字节   |
| DECIMAL（M，D），DEC | 压缩的“严格”定点数 | M+2个字节 |

FLOAT类型的取值范围如下

1. 有符号的取值范围：-3.402823466E+38～-1.175494351E-38。
2. 无符号的取值范围：0和1.175494351E-38～3.402823466E+38。

DOUBLE类型的取值范围如下

1. 有符号的取值范围：-1.7976931348623157E+308～-2.2250738585072014E-308。
2. 无符号的取值范围：0和2.2250738585072014E-308～1.7976931348623157E+308。

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MDcYBDqTy8xjNnLk6qm%2F-MDdwbCDp6v6b8FO9qum%2Fimage.png?alt=media\&token=afe7c908-2af6-4ba4-aa56-2bf0eee109e8)

在MySQL中，在对精度要求比较高的时候（如货币、科学数据等），尽量选择使用DECIMAL类型。另外，两个浮点数在进行减法和比较运算的时候容易出问题，因此在使用浮点数类型时需要注意，并尽量避免做浮点数比较

3、日期与时间类型

MySQL中有多种表示日期的数据类型，主要有：YEAR、TIME、DATE、DATETIME、TIMESTAMP。

| 类型名称      | 日期格式                          | 日期范围                                        | 存储需求 |
| --------- | ----------------------------- | ------------------------------------------- | ---- |
| YEAR      | YYYY                          | 1901\~2155                                  | 1个字节 |
| TIME      | HH:MM:SS                      | -838:59:59 \~ 838:59:59                     | 3个字节 |
| DATE      | YYYY-MM-DD                    | 1000-01-01 \~ 9999-12-31                    | 3个字节 |
| DATETIME  | <p>YYYY-MM-DD<br>HH:MM:SS</p> | 1000-01-01 00:00:00 \~ 9999-12-31 23:59:59  | 8个字节 |
| TIMESTAMP | <p>YYYY-MM-DD<br>HH:MM:SS</p> | 1970-01-01 00:00:001 \~ 2038-01-19 03:14:07 | 4个字节 |

YEARYEAR类型使用单字节表示年份，在存储时只需要一个字节。可以使用不同格式指定YEAR的值。

1. 以4位字符串或者4位数字格式表示YEAR，其范围为‘1901’～‘2155’，输入格式为‘YYYY’或YYYY。例如，输入‘2015’或2015，插入到数据库的值都是2015。
2. 以2位字符串格式表示YEAR，范围为‘00’到‘99’。‘00’～‘69’和‘70’～‘99’范围的值分别被转换为2000～2069和1970～1999范围的YEAR值。输入‘0’与‘00’取值相同，皆为2000。插入超过取值范围的值将被转换为2000。
3. 以2位数字表示的YEAR，范围为1～99。1～69和70～99范围的值分别被转换为2001～2069和1970～1999范围的YEAR值。注意：0值被转换为0000，而不是2000。

两位整数与两位字符串的取值范围稍有不同。例如：插入2000年，读者可能会使用数字格式的0表示YEAR，实际上，插入数据库的值为0000，而不是期望的20000。只有使用字符串格式的‘0’和‘00’，才可以得到2000。非法YEAR值被转换为0000。

![](https://172932098-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MC0hWgbNjaxH4i6ny5D%2F-MDcYBDqTy8xjNnLk6qm%2F-MDe-V65CjaDAa8FvVa-%2Fimage.png?alt=media\&token=8c17c0d0-ad1c-44e7-a493-44202a70d8c3)









### 2、如何选择数据类型



### 3、常见运算符



## 三、MySQL函数

### 1、数学函数



### 2、字符串函数



### 3、日期和时间函数



### 4、控制流函数



### 5、系统信息函数



### 6、加密函数



### 7、其他函数



## 四、查询语句详解

### 1、学生-课程数据库



### 2、基本查询语句



### 3、对查询结果进行排序



### 4、统计函数和分组记录查询



### 5、GROUP BY语句



### 6、使用LIMIT限制查询结果的数量



### 7、连接查询



### 8、子查询



### 9、合并查询结果



### 10、使用正则表达式表示查询



## 五、存储过程与函数

### 1、存储过程的定义



### 2、存储过程的创建



### 3、存储过程的操作



### 4、自定义函数



## 六、触发程序

### 1、触发程序的定义



### 2、触发程序的创建



### 3、触发程序的操作



## 七、视图

### 1、视图的定义



### 2、视图的创建，修改与删除



### 3、视图的使用

