核心技术

一、数据表基本操作

1、创建数据表

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

1、语法形式

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

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

建表时注意：

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

## 实例
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;

2、主键约束

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

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

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

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

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

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

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

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

3、外键约束

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

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

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

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

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

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

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

字段名 数据类型 not null

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

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

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

UNIQUE和PRIMARY KEY的区别：

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

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

字段名数据类型 DEFAULT 默认值

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

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

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

字段名 数据类型 AUTO_INCREMENT

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

2、查看数据表结构

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

其中：

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

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

3、修改数据表

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

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

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

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

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

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

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

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

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

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

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

4、删除数据表

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

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

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

1、MySQL数据类型

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

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

1、整数类型

类型名称

说明

存储需求

TINYINT

很小的整数

1个字节

SMALLINT

小的整数

2个字节

MEDIUMINT

中等大小的整数

3个字节

INT（INTEGER）

普通大小的整数

4个字节

BIGINT

大整数

8个字节

整数类型的显示宽度只用于显示，不能限制取值范围和存储空间，如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类型的取值范围如下

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

DOUBLE类型的取值范围如下

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

在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

YYYY-MM-DD HH:MM:SS

1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

8个字节

TIMESTAMP

YYYY-MM-DD HH:MM:SS

1970-01-01 00:00:001 ~ 2038-01-19 03:14:07

4个字节

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

以4位字符串或者4位数字格式表示YEAR，其范围为‘1901’～‘2155’，输入格式为‘YYYY’或YYYY。例如，输入‘2015’或2015，插入到数据库的值都是2015。
以2位字符串格式表示YEAR，范围为‘00’到‘99’。‘00’～‘69’和‘70’～‘99’范围的值分别被转换为2000～2069和1970～1999范围的YEAR值。输入‘0’与‘00’取值相同，皆为2000。插入超过取值范围的值将被转换为2000。
以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。

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、视图的使用

最后更新于4年前

这有帮助吗？

核心技术

一、数据表基本操作

1、创建数据表

1、语法形式

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

建表时注意：

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

## 实例
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;

2、主键约束

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

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

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

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

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

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

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

3、外键约束

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

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

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

字段名 数据类型 not null

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

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

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

UNIQUE和PRIMARY KEY的区别：

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

字段名数据类型 DEFAULT 默认值

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

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

字段名 数据类型 AUTO_INCREMENT

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

2、查看数据表结构

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

其中：

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

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

3、修改数据表

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

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

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

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

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

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

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

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

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

4、删除数据表

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

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

1、MySQL数据类型

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

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

1、整数类型

类型名称

说明

存储需求

TINYINT

很小的整数

1个字节

SMALLINT

小的整数

2个字节

MEDIUMINT

中等大小的整数

3个字节

INT（INTEGER）

普通大小的整数

4个字节

BIGINT

大整数

8个字节

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

2、小数类型

类型名称

说明

存储需求

FLOAT

单精度浮点数

4个字节

DOUBLE

双精度浮点数

8个字节

DECIMAL（M，D），DEC

压缩的“严格”定点数

M+2个字节

FLOAT类型的取值范围如下

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

DOUBLE类型的取值范围如下

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

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

YYYY-MM-DD HH:MM:SS

1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

8个字节

TIMESTAMP

YYYY-MM-DD HH:MM:SS

1970-01-01 00:00:001 ~ 2038-01-19 03:14:07

4个字节

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

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