MySQL

前言

数据库概念

数据库就是按照数据结构来组织、存储和管理数据，建立在计算存储设备上的仓库。可以看成是电子化的文件柜，也就是存储电子文件的处所，用户可以对文件中的数据进行进行新增、查询、更新、删除等操作。

为什么要使用数据库？

随着，应用程序的功能越来越复杂，数据量越来越大，如何管理这些数据就成了一个大问题。

读写文件的并解析出数据需要大量重复代码
从成千上万的数据中快速查询出指定数据需要复杂的逻辑
数据缺乏安全感

如果每个应用程序都各自写自己的读写数据代码，一方面效率低，容易出错，另一方面，每个应用程序访问数据的接口都不同，数据难以复用。

所以

数据库作为一种转门管理数据的软件就出现了。应用程序不需要自己管理数据，而是通过数据库软件提供的接口来读写数据。至于数据本身如何存储到文件，那是数据库软件的事情，应用程序自己并不关心。

这样一来，编写应用程序的时候，数据读写的功能就被大大地简化了。

MySql简介

MySql作为一种开源的轻量级数据库（ 关系型数据库），在开源数据库中比较流行，由于小巧安装方便快捷，经常会用于互联网公司，维护也比较方便。因为开源，使用上比较灵活，mysql还有许多第三方的存储引擎，可以根据自己的需要安装。在功能上mysql可能没有oracle强大，但是对于资源的占用非常少，数据恢复快。在维护上，追求稳定的是性能和易用性。

选择数据库时，既要考虑成本，还要考虑维护的稳定性和便利性，需要结合我们的业务，如果提高安全性，海量数据，能承担高昂的成本，可以选择oracle, 一般应用的快速查询，高并发访问，一般情况下都可以选择mysql，另外特殊情况特殊处理。

优势：

开源
成本低
快平台性
容易使用

数据库操作

关系模型

关系数据库是建立在关系模型上的。而关系模型本质上就是若干个存储数据的二维表，可以吧他们看作很多Excel表。

例如:

表的每一行称为记录(Record),记录是一个逻辑上的数据。
表的每一列称为字段(Column),同一个表的每一刚记录都拥有相同的若干字段。

字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为NULL。注意NULL表示字段数据不存在。一个整型字段如果为NULL不表示它的值为0，同样的，一个字符串型字段为NULL也不表示它的值为空串''。

通常情况下，字段应该避免允许为NULL。不允许为NULL可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为NULL。

和Excel表有所不同的是，关系数据库的表和表之间需要建立“一对多”，“多对一”和“一对一”的关系，这样才能够按照应用程序的逻辑来组织和存储数据。

每一行对应着一个班级，而一个班级对应着多个学生，所以班级表和学生表的关系就是“一对多”：

反过来，如果我们先在学生表中定位了一行记录，例如ID=1的小花，要确定他的班级，只需要根据他的“班级ID”对应的值101找到班级表中ID=101的记录，即六年级一班。所以，学生表和班级表是“多对一”的关系。

如果我们把班级表分拆得细一点，例如，单独创建一个教师表：

查看数据库

像文件一样，数据库也能进行如下操作。

（windows下不区分大小写，Linux下区分大小写。）
注意结尾的分号;(就像写C/C++一样)

创建数据库
查看数据库
选择数据库
删除数据库

在创建数据库之前，可以使用SHOW语句来显示当前已经存在的数据库，如下:

SHOW DATABASES;

因为数据库本身的需要，所以它默认自带了几个库。不要随意删除，否则可能会无法正常运行数据库。

创建数据库

创建数据库是指在数据库系统中划分一块空间，用来存储相应的数据，这是进行表操作的基础，也是进行数据库管理的基础。

CREATE DATABASE database_name(数据库的名字);

例如：

再SHOW

选择数据库

在对数据库进行操作之前，应该先选择一个数据库。

USE database_name(要记性操作的数据库名字);

要选择已存在的库，否则会报错。

删除数据库

DROP DATABASE database_name;

要删除已有的数据库，否则会报错。

成功删除提示。

数据库命名规范

可以采用26个英文字母 (不区分大小写) 和0-9的自然数 (一般不需要) 加上下划线 _ 组成，命名简介明确 (Student_Age)，多个单词用下划线 ‘_’ 分隔，一个项目一个数据库，多个项目慎用同一个数据库!!!

数据表的操作

表是一种很重要的数据库对象，是组成数据库的基本元素，由若干个字段组成，主要用来实现存储数据记录。表的操作包含创建表、查询表、修改表和删除表，这些操作是数据库对象的表管理中最基本也是最重要的操作。

创建一个班级表

mysql> create database school;   #创建数据库school

mysql> use school;   #选择数据库school 
mysql> create table class(class_id int, class_name varchar(128), class_teacher varchar(64));         #创建表class
mysql> insert into class values (101, '六年级一班', '马老师');  #往表中插入101班记录
mysql>insert into class values (102, '六年级二班', '潘老师');   #往表中插入102班记录
mysql>select * from class;  #查询表class 中的全部记录
mysql>select * from class where class_id=101;  #查询表class 中的全部记录

补充:mysql中字符串用单引号括起来。

表的创建

CREATE TABLE 表名([表定义选项])[表选项][分区选择];

其中[表定义选项]格式为:

<列名1> <类型1> […] <列名n> <类型n>

CREATE TABLE 命令语法比较多，其主要是由
表创建定义（create-definition）、

表选项（table-options）和

分区选项（partition-options）所组成的。

- CREATE TABLE：用于创建给定名称的表，必须拥有表CREATE的权限。

有些数据库可以使用加引号的识别名，例如，'mydb'.'mytbl' 是合法的，但 mysql 是不合法。

表的创建有两种方式：

1.选择选择数据库，然后按照上面方式创建表。

2.不选择先选择数据库，以db_name.tbl_name的方式创建，例如：

create table school.class2(class_id int, class_name varchar(128), class_teacher varchar(64));

<表定义选项>：表创建定义，由列名（col_name）、列的定义（column_definition, 或者叫类型定义）以及可能的空值说明、完整性约束或表索引组成。

mysql> use school;  #选择数据库school 
mysql> create table class4(class_id int PRIMARY KEY, class_namevarchar(128) NOT NULL, class_teacher varchar(64) UNIQUE);#创建表class3 

# UNIQUE 唯一限定-不能重复

注意：

默认的情况是，表被创建到当前的数据库中。若表已存在、没有当前数据库或者数据库不存在，则会出现错误。
提示：使用 CREATE TABLE 创建表时，必须注意以下信息：

- 要创建的表的名称不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等。

- 数据表中每个列（字段）的名称和数据类型，如果创建多个列，要用逗号隔开。

列名

1）采用26字母和0-9的自然数加上下互相 ‘_’ 组成，命名简洁明确，多个单词用下划线 ‘_’_ 隔开。

2）全部小写命名，尽量避免出现大写

3）字段必须填写描述信息

4）禁止使用数据库关键字

5）字段名称一般采用名词或动宾短语

6）采用字段的名字必须是易于理解，一般不超过三个英文单词

7）在命名表的列时，不要重复表的名称（如：在 user 表中，出现 user_name 字段）

8）字段命名使用完整名称

列的类型定义

整型类型

整数类型是数据库中最基本的数据类型。标准SQL中支持INTEGER和SMALLINT这两种数据类型。MySQL数据库除了支持这两种类型以外，还扩展支持了TINYINT、MEDIUMINT和BIGINT。下表从不同整数类型的字节数、取值范围等方面进行对比。

整数类型的附带属性：

(1)类型名后面的小括号

指定显示宽度 (并不是该类型占用的字节数)。如果不显式指定宽度，则默认为:

tinyint(3)、 smallint(5)、 mediumint(8)、 int(11) 和 bigint(20)。

一般配合zerofill使用，顾名思义，zerofill就是用‘0’填充的意思，也就是在数字位不够的空间用字符“0”填满。

create table class2(id,int(8) zerofill,name varchar(128),teacher varchar(64));

效果如下:

超过显示位数则不会补零

（2）UNSIGNED(无符号)

如果需要在字段里面保存非负数或者需要较大的上限值时，可以用此选项，它的取值范围是正常值的下限取 0，上限取原值的 2 倍，例如，tinyint 有符号范围是-128～+127，而无符号范围是 0～255。如果一个列指定为 zerofill，则 MySQL 自动为该列添加 UNSIGNED 属性。

create table class2(id int unsigned,name varchar(128),teacher varchar(64));

添加一个zerofill属性。

(3)AUTO_INCREMENT

在需要产生唯一标识符或顺序值时，可利用此属性，这个属性只用于整数类型。AUTO_INCREMENT 值一般从 1 开始，每行增加 1。一个表中最多只能有一个 AUTO_INCREMENT列。对于任何想要使用 AUTO_INCREMENT 的列，应该定义为 NOT NULL，并定义为 PRIMARY KEY 或定义为 UNIQUE 键(不能有重复的)。例如，可按下列任何一种方式定义 AUTO_INCREMENT 列：

mysql> create table class8(id integer auto_increment PRIMARY KEY , name varchar(128), teacher varchar(64) );         #创建表class8， id 具有自增长属性

mysql> create table class9(id integer auto_increment UNIQUE , name varchar(128), teacher varchar(64) );             #创建表class9， id 具有自增长属性

设置的类型可以不指定值，但要在value前进行一个声明，声明一下哪个需要手动设定。

插入的第一个元素如果不指定的话就从1开始，指定的话，后面的元素不指定，就前一个的基础上+1。

如下图所示。

浮点类型和定点数类型

数据表中用浮点数类型和定点数类型来表示小数。浮点数类型包括单精度浮点数（FLOAT型）和双精度浮点数（DOUBLE型）。定点数类型就是DECIMAL型。下面从这三种类型的字节数、取值范围等方面进行对比，如下表所示。

三种类型的区别：
float是单精度浮点型，double是双精度浮点型，decimal是定点型。

mysql> create table class10 (f1 float, do1 double, de1 decimal);  #创建表class10

MySQL 浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度，如float(7,4)的可显示为-999.9999，MySQL保存值时进行四舍五入(当数的长度超过所设置精度)，如果插入999.00009，则结果为999.0001。

对于decimal,M是最大位数（精度），范围是1到65。可不指定，默认值是10。D是小数点右边的位数（小数位）。范围是0到30，并且不能大于M，可不指定，默认值是0。

FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0,即(10, 0)。

注意：

当我们需要存储小数，并且有精度要求，比如存储金额时，通常会考虑使用DECIMAL字段类型!!!

日期和时间类型

日期与时间类型是为了方便在数据库中存储日期和时间而设计的，数据库有多种表示日期和时间的数据类型。其中，YEAR类型表示年，DATE类型表示日期，TIME类型表示时间，DATETIME和TIMESTAMP表示日期和时间。下面从这5种日期与时间类型的字节数、取值范围和零值等方面进行对比，如下表所示。

每种日期与时间类型都有一个有效范围。如果插入的值超过了这个范围，系统就会报错，并将零值插入到数据库中。不同的日期与时间类型均有不同的零值.
插入日期时间时，日期年月日和时间时分秒可以使用 “*: - _ /* ”中的任意字符来分隔,如果单独插入时间，则会报错！！！

mysql> create table test(e_data date,e_datatime datetime,e_timestamp timestamp,e_time time,e_year year)

mysql> insert into test value('2021-10-25','2021-10-25 9:13:00','2021-10-25 9:13:00','9:13:00','2021');

常用的时间函数：

CURDATE() - 获得当前的DATE, 可直接插入DATE 类型中.

NOW() - 获得当前的DATETIME, 可直接插入DATETIME 和TIMESTAMP类型中.

TIME() - 获取参数给定时间串中的时分秒，可直接插入TIME 类型中.

YEAR() - 获取参数给定时间串中的年份，可直接插入YEAR类型中.

MONTH() 、DAY()、HOUR()、MINUTE()、SECOND() 获取参数给定时间串中的月、日、时、分、秒值.

mysql> insert into date_example values(CURDATE(), NOW(), NOW(), time(NOW()), YEAR(NOW()) );

字符串类型

CHAR类型和VARCHAR类型

CHAR类型和VARCHAR类型都在创建表时指定了最大长度，其基本形式如下：
字符串类型（M）

其中，“字符串类型”参数指定了数据类型为CHAR类型还是VARCHAR类型；M参数指定了该字符串的最大长度为M。例如，CHAR(4)就是数据类型为CHAR类型，其最大长度为4。

CHAR类型的长度是固定的，在创建表时就指定了。其长度可以是0~255的任意值。例如，CHAR(100)就是指定CHAR类型的长度为100。CHAR 存储值时，它们会用空格右填充到指定的长度。

VARCHAR类型的长度是可变的，在创建表时指定了最大长度。定义时，其最大值可以取0~65535之间的任意值。指定VARCHAR类型的最大值以后，其长度可以在0到最大长度之间。例如，VARCHAR(100)的最大长度是100，但是不是每条记录都要占用100个字节，而是在这个最大值范围内使用多少就分配多少。VARCHAR类型实际占用的空间为字符串的实际长度加1或2，这样即可有效节约系统的空间。

mysql> use test;   #选择数据库test 
mysql> create table char_example(e_char char(5), v_char varchar(5)); #创建数据库表
mysql> insert into char_example values('12345','12345');  #正常插入数据
mysql> insert into char_example values('1 2  ','1 2  ');   #char类型会屏蔽后面隐藏的空格，varchar 不会
mysql>select concat('(',e_char, ')'), concat('(',v_char, ')')  from char_example ; #让char 后面屏蔽的空格原型毕露

使用建议：

char一定会使用指定的空间，varchar是根据数据来定空间。
char的数据查询效率比varchar高：varchar是需要通过后面的记录数来计算。
如果确定数据一定是占指定长度，那么使用char类型。
如果不确定数据到底有多少，那么使用varchar类型。
如果数据长度超过255个字符而在65535之内，直接使用varchar。
如果字符串尾部要保留空格，必须选择varchar。

TEXT类型

TEXT类型是一种特殊的字符串类型，包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT，其长度和存储空间的对比下表所示:

各种TEXT类型的区别在于允许的长度和存储空间不同。因此，在这几种TEXT类型中，根据需求选取既能满足需要又节省空间的类型即可。

注意：

以上各类型无须指定长度！
允许的长度是指实际存储的字节数，而不是实际的字符个数，比如假设一个中文字符占两个字节，那么TEXT 类型可存储65535/2 = 32767个中文字符，而varchar(100)可存储100个中文字符，实际占200个字节，但varchar(65535) 并不能存储65535个中文字符，因为已超出表达范围。

使用建议：

char长度固定，即每条数据占用等长字节空间，适合用在身份证号码、手机号码等。超过255字节的只能用varchar或者text。
varchar可变长度，可以设置最大长度，适合用在长度可变的属性。
text不设置长度，当不知道属性的最大长度来时，适合用text,能用varchar的地方不用text；
如果都可以选择，按照查询速度，char最快，varchar次之，text最慢。(跟数据库的设计有关，不同的类型有不同的特性。实现不用的功能都是有代价的。)

字符串类型选择练习

创建一个用户信息表，用来存储用户的如下信息：姓名，手机号码，家庭地址，个人简介，性别，年龄，身份证号。数据库表名可定义为userinfo, 同时增加一个列id 用来作为每一条记录的唯一标识，并设为主键，自加！

mysql> create table userinfo (
 id int(11) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘主键’,
 name   varchar(64) DEFAULT NULL COMMENT ‘姓名’,
 mobile  char(11)  DEFAULT NULL COMMENT ‘手机号码’,
 address varchar(128) DEFAULT NULL COMMENT ‘居住地址’,
 description text  DEFAULT NULL COMMENT ‘个人简介-不知道具体的范围，不常更新用text’,
 sex     char(1)  DEFAULT NULL COMMENT ‘性别 - 男或女’,
 age     tinyint unsigned DEFAULT 0 COMMENT ‘年龄’,
 idno    char(18) DEFAULT NULL COMENT ‘身份证号码’
);

设计数据库内容的时候就想好它的使用场景。

ENUM和SET类型

ENUM类型

ENUM类型又称为枚举类型。在创建表时，ENUM类型的取值范围以列表的形式指定，其基本形式如下：
属性名 ENUM('值1', '值2', …, '值n')

其中，“属性名”参数指字段的名称，“值n”参数表示列表中的第n个值。ENUM类型的值只能取列表中的一个元素。其取值列表中最多能有65535个值。如果数据值列表在255个以内，那么一个字节就够，如果超过255但是小于65535，那么系统采用两个字节保存。列表中的每个值独有一个顺序排列的编号，MySQL中存入的是这个编号，而不是列表中的值。默认编号从1开始！

Enum(‘男’,’女’,‘选择保密’) # ‘男’=>1 ‘女’=>2 ‘选择保密’=> 3

mysql> use test;   #选择数据库test 
mysql> create table enum_example (e_enum enum('男','女','选择保密') ); #创建表 
mysql> insert into enum_example values('男');  #插入记录,必须是enum 选项中的值
mysql> insert into enum_example values(1);  #插入记录可以用数值表示
mysql>select e_enum + 0  from enum_example; #查询enum 选项对应的整数值（是当前表中已经存的内容，对应enum类型中的数值）

如果ENUM类型加上了NOT NULL属性，其默认值为取值列表的第一个元素。如果不加NOT NULL属性，ENUM类型将允许插入NULL，而且NULL为默认值。

SET类型

在创建表时，SET类型的取值范围就以列表的形式指定了，其基本形式如下：
属性名 SET('值1', '值2', …, '值n')

其中，属性名参数指字段的名称，“值n”参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除。其基本形式与ENUM类型一样。SET类型的值可以取列表中的一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET类型的值最多只能是由64个元素构成的组合。

mysql> use test;   #选择数据库test 
mysql> create table set_example (interest set('足球','追剧','篮球','撩妹') ); #创建表 
mysql> insert into set_example values( '足球,撩妹' ); #插入记录,必须是enum 选项中的值
mysql> insert into enum_example values(9);  #插入相应位效果等同，9 =>1001 选择1,4
mysql>select interest+0 from set_example;  #以整数的方式查询

注意:对应二进制位计算的顺序是反过来的。

例：
选足球，其他的不选

足球追剧篮球撩妹

1 0 0 0

与正常顺序的二进制不同，正常算结果为8

在mysql的顺序计算就是1，反过来了。

二进制类型

二级制数据和文本数据在mysql中的最大差距在于:

二进制类型存储原始的二进制数据(如图片，视频，exe文件等)。文本类型(TEXT)用来存储字符字符串(如由英文字符、中文字符或其它语言字符组成的字符串)。
二进制类型没有字符集，并且排序和比较基于列值字节的数值。而TEXT类型有字符集，并且根据字符集的校对规则对值进行排序和比较。

二进制类型是存储二进制数据的数据类型，包括BINARY、VARBINARY、BIT、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。二进制类型之间的对比如下表所示。

BINARY和VARBINARY

BINARY类型和VARBINARY类型都是在创建表时指定最大长度，其基本形式如下：
类型M

其中，“字符串类型”参数指定数据类型为BINARY类型还是VARBINARY类型；M参数指定该二进制数的最大字节长度为M。这与CHAR类型和VARCHAR类型相似。例如，BINARY(10)就是指数据类型为BINARY类型，其最大长度为10。
BINARY类型的长度是固定的，在创建表时就指定了，不足最大长度的空间由″\0″补全。例如，BINARY(50)就是指定BINARY类型的长度为50。

VARBINARY类型的长度是可变的，在创建表时指定了最大的长度，其长度可以在0到最大长度之间，在这个最大值范围内使用多少就分配多少。

例如：
都存ab

查：

BIT类型

BIT类型在创建表时指定最大长度，其基本形式如下:
BIT(M)

其中，“M”指定该二进制数的最大存储长度为M，M的最大值为64。例如，BIT(4)就是指数据类型为BIT类型，长度为4。若字段的类型BIT(4)存储的数据是0~15，因为变成二进制之后15的值为1111，则其长度为4。如果插入的值为16，其二进制数为10000，长度为5，超过了最大长度，因此大于16的数是不能插入BIT(4)类型字段中的。

注意：

插入数据时，使用 b‘位串’的方式插入相应值!
查询时,可以使用 bin() 、oct() 、hex() 函数讲字段的值转成相应的二进制、八进制和十六进制。

(二进制位串高位0会省略)

BLOB类型

以上各类型无需指定长度。
允许的长度是指实际存储的字节数，不用考虑字符编码。

使用建议：

binary长度固定，即每条数据占用等长字节空间；保存长度不超过255字节的二进制数据。
varbinary可变长度，可以设置最大长度,最大长度65535；适合用在长度可变的二进制数据。
blob不设置长度，当不知道属性的最大长度时，适合用blob，能用varbinary的地方不用blob。
如果都可以选择，按照查询速度： binary最快， varbinary次之，blob最慢。

其他数据类型

JASON类型

SPATIAL数据类型

(略)

列的完整性约束

完整性约束条件是对字段进行限制，要求用户对该属性进行的操作符合特定的要去，如果不满足完整性约束条件，数据库系统将不再执行用户的操作。下表为完整性约束条件。

设置表字段的主键约束(PRIMARY KEY,PK)

主键是表的一个特殊字段， 能唯一标识该表中的每条信息。主键和记录的关系，如同身份证和人的关系。 主键用来标识每个记录，每个记录的主键值都不同。身份证用来表明人的身份，每个人都具有唯一的身份证号。设置表的主键是指在创建表时设置表的某个字段为该表的主键。
主键的主要目的是帮助数据库管理系统以最快的速度查找到表的某一条信息。主键必须满足的条件就是主键必须是唯一的，表中任意两条记录的主键字段的值不能相同，并且是非空值。主键可以是单一的字段，也可以是多个字段的组合。

主键是唯一的

单字段主键

单字段主键的语法规则如下：
CREATE TABLE tablename(
    propName propType PRIMARY KEY, ......
);
或
CREATE TABLE tablename(
    propName propType , ......
PRIMARY KEY(propType）
);

mysql> create database school;   #创建数据库school
mysql> use school;   #选择数据库school 
mysql> create table class(id int PRIMARY KEY, name varchar(128), teacher varchar(64));         #创建表class
mysql> desc class ;  #查询表class 的定义, describe class 效果等同
mysql> show create table class ;  #同样可以实现查询表class 的定义
mysql> insert into class VALUES(1,'一班','s1');  #插入成功
mysql> insert into class VALUES(1,'二班','s2');  #因主键约束，插入失败

MySQL支持给主键设置名字

CREATE TABLE tablename(
    propName propType , ......
CONSTRAINT pk_name PRIMARY KEY(propType）
);

多字节主键

主键是由多个属性组合而成时，在属性定义完之后统一设置主键。语法规则如下：

CREATE TABLE tablename(  
    propName1 propType , 
    propName2 propType , 
    ......                     
[CONSTRAINT pk_name]PRIMARY KEY(propName1, propName2)
);

例如：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3oM0bThT-1640525294346)(MySql.assets/image-20211026143246285.png)]

mysql> create table class3(id int, name varchar(128), teacher varchar(64), CONSTRAINT id_pk PRIMARY KEY(id, name));         #创建表class3,设置联合主键
mysql> insert into class VALUES(1,'一班','S1');  #插入成功
mysql> insert into class VALUES(1,'一班','S2');    #因联合主键约束，插入失败

设置表字段的外键约束(FORENIGN KEY,FK)

(就是外部的约束)

外键是表的一个特殊字段，外键约束是为了保证多个表（通常为两个表）之间的参照完整性，即构建两个表的字段之间的参照关系。
设置外键约束的两个表之间具有父子关系，即子表中某个字段的取值范围由父表决定。例如，表示一个班级和学生关系，即每个班级有多个学生。首先应该有两个表：班级表和学生表，然后学生表有一个表示班级编号的字段no，其依赖于班级表的主键，这样字段no就是学生表的外键，通过该字段班级表和学生表建立了关系。

在具体设置外键约束时，设置外键约束的字段必须依赖于数据库中已经存在的父表的主键，同时外键可以为空（NULL）。

设置表中某字段的外键约束非常简单，可以在MySQL数据库管理系统中通过SQL语句FOREIGN KEY来实现，其语法形式如下：

CREATE TABLE tablename_1(  
    propName1 propType , 
    propName2 propType , 
    ......                     
[CONSTRAINT fk_name]FOREIGN KEY(propName1) REFERENCES table2_name(table2_column)
);

例如：学生表的外键(班级id字段)依赖于父类班级表的主键(id),插入的学生信息班级id必须是父类班级表主键中有的。否则就会插入失败。

并且，外键是可以为空的，不为空一定要受父类表主键的约束。(父类表一定要有主键，否则不能为子类表创建外键。)

设置列的非空约束(NOT NULL,NK)

当数据库表中的某个字段上的内容不希望设置为NULL时，可以使用NK约束进行设置。NK约束在创建数据库表时为某些字段上加上“NOT NULL”约束条件，保证所有记录中的该字段都有值。如果在用户插入的记录中该字段为空值，那么数据库管理系统会报错。
设置表中某字段的NK约束非常简单，查看帮助文档可以发现，在MySQL数据库管理系统中是通过SQL语句NOT NULL来实现的，其语法形式如下：

CREATE TABLE tablename(
    propName propType NOT NULL , ......
);

mysql> create database school;   #创建数据库school
mysql> use school;   #选择数据库school 
mysql> create table class(id int, name varchar(128) NOT NULL, teacher varchar(64));         #创建表class
mysql> desc class ;  #查询表class 的定义, describe class 效果等同 
mysql> show create table class ;  #同样可以实现查询表class 的定义

这个NULL的YES or NO,意思是是否可以为空。(可以为空也就是没有设置非空约束)

设置表字段唯一约束(UNIQUE,UK)

当数据库表中某个字段上的内容不允许重复时，可以使用UK约束进行设置。UK约束在创建数据库时为某些字段加上“UNIQUE”约束条件，保证所有记录中该字段上的值不重复。如果在用户插入的记录中该字段上的值与其他记录中该字段上的值重复，那么数据库管理系统会报错。
设置表中某字段的UK约束非常简单，可以在MySQL数据库管理系统中通过SQL语句UNIQUE来实现，其语法形式如下：

(该列被设为主键了，就没必要再设置UNIQUE了，因为主键就是不可重复，为了区分表中的每一行数据。)

CREATE TABLE tablename(
    propName propType UNIQUE, ......
);

unique 约束允许约束的字段出现多个空值NULL，不算重复。(mysql8.0)

mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64));         #创建表class

设置表字段值自动增加(AUTO_INCREMENT)

AUTO_INCREMENT是MySQL唯一扩展的完整性约束，当向数据库表中插入新记录时，字段上的值会自动生成唯一的ID。在具体设置AUTO_INCREMENT约束时， 一个数据库表中只能有一个字段使用该约束， 该字段的数据类型必须是整数类型。由于设置AUTO_INCREMENT约束后的字段会生成唯一的ID，因此该字段也经常会同时设置成PK主键。
设置表中某字段值的自动增加约束非常简单，可以在MySQL数据库管理系统中通过SQL语句AUTO_INCREMENT来实现，其语法形式如下：

CREATE TABLE tablename(
    propName propType AUTO_INCREMENT, ......
);

在上述语句中，tablename参数表示所要设置非空约束的字段名字，propName参数为属性名，propType为属性类型，propName字段要设置自动增加约束。默认情况下，字段propName的值从1开始增加，每增加一条记录， 记录中该字段的值就会在前一条记录(或已存在的最大值（包括曾经存在的）)的基础上加1。

(自增的列类型，需要被设置成主键。)如下图所示:

mysql> create table class(id int PRIMARY KEY AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64));         #创建表class,设置字段id为自增长

默认是在上一个的数据的基础上+1

注意：mysql8中，AUTO_INCREMENT必须设为键(主键、外键和唯一键),否则会报错。

小细节：受外键约束的一个列设置为自增，其实是没用的，无法根据外键限制自己增加，就是没用。

设置表字段的默认值(DEFAULT)

当为数据库表中插入一条新记录时，如果没有为某个字段赋值，数据库系统就会自动为这个字段插入默认值。为了达到这种效果，可通过SQL语句关键字DEFAULT来设置。
设置数据库表中某字段的默认值非常简单，可以在MySQL数据库管理系统中通过SQL语句DEFAULT来实现，其语法形式如下：

CREATE TABLE tablename(
    propName propType DEFAULT defaultvalue, ......
);

如果没有设置，则默认为空。

调整列的完整性约束

修改主键PK、外键FK和唯一键UK

新增

alter table [table_name] add constraint [constraint_name] [unique key| primary key|foreign key] ([column_name])

删除

通过如下命令查询键值的约束名:
```
show index或keys from 表名;     
```
执行如下命令删除

主键： alter table 表名 drop primary key; (主键只有一个。直接通过限制名删除)

外键或唯一键:alter table 表名 drop index 约束名;

修改:先删除再新增。

修改默认值DEFAULT、自增长和非空NK

alter table 表名 modify 列名 类定义；

表索引

索引概述

帮助我们快速找到想要的内容。

索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录，是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。
通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列，否则数据库系统将读取每条记录的所有信息进行匹配。。使用索引可以在很大程度上提高数据库的查询速度，有效地提高了数据库系统的性能。

索引类型

普通索引
唯一性索引
全文索引
单列索引
空间索引
等

索引存储

数据库底层索引实现主要有两种存储类型，B树(BTREE)和哈希(HASH)索引，InnoDB和MyISAM 使用BTREE索引；而MEMORY 存储引擎可以使用BTREE 和HASH 索引，默认用BTREE.在没有指定的情况下，数据库使用的引擎是 InnoDB。

索引优点

可以提高检索数据的速度。

索引缺点

创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态地维护索引，造成数据的维护速度降低了。

注意

索引可以提高查询的速度，但是会影响插入记录的速度，因为向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度， 插入大量记录时的速度影响更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据， 插入完成后再创建索引。

创建和查看索引

创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。创建索引有3种方式，分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTER TABLE语句来创建索引。

普通索引

就是在创建索引的时候不附加任何限制条件(唯一、非空等限制)，给类型的索引可以创建在任何数据类型的字段上。

创建表时定义索引:

CREATE TABLE tablename(
    propname1 type1,
    propname2 type2,
    ……
    propnamen type..n,
     INDEX | KEY
    [indexname] (propnamen [(length)] [ ASC | DESC ] ) );

其中，参数INDEX和KEY是用来指定字段为索引的，两者选择其中之一就可以了，作用是一样的；
参数indexname是索引名字，可省略；

参数propnamen是索引对应的字段的名称，该字段必须为前面定义好的字段；

参数length是可选参数，其指索引的长度，必须是字符串类型才可以使用；

参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列，如果不指定，则为升序。

代码示例:

mysql> create database school;   #创建数据库school  
mysql> use school;   #选择数据库school  
mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64), INDEX index_no(id DESC));       #创建表class, 并建立为id 字段索引  
mysql> show create table class;  #查看表结构 
mysql> insert into class values(1, '一班', 'Martin');  # 插入记录1 
mysql> insert into class values(1, '二班', 'Rock');   # 插入记录2 
mysql> select * from class where id > 0 ;   #根据id查询记录，结果将降序排列

或者，在已存在的表上面创建索引。

ALTER TABLE tablename ADD INDEX | KEY indexname  
     (propname [(length)] [ASC|DESC]);

例如：

alter table class1 add index index_id(id DESC);

查询索引执行情况

EXPLAIN 查询语句

在上述语句中，参数tablename是需要创建索引的表；关键字IDNEX或KEY用来指定创建普通索引；参数indexname用来指定所创建的索引名；参数propname用来指定索引所关联的字段的名称；参数length用来指定索引的长度；参数ASC用来指定升序排序；参数DESC用来指定降序排序。

输出结果:
key: 实际使用的索引。如果为NULL，则没有使用索引。

possible_keys:显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。此值越短越好！

唯一索引

所谓唯一索引，就是在创建索引时，限制索引的字段值必须是唯一的。

通过该类型的索引可以比普通索引更快速地查询某条记录。

创建表时定义索引：

CREATE TABLE tablename(
    propname1 type1,
    ……
    propnamen type..n,
    UNIQUE INDEX | KEY [indexname] (propnamen [(length)] [ ASC | DESC ] ) );

示例：

create table class(id int,name varchar(128) UNIQUE,teachar varchar(64),UNIQUE INDEX name_index (name(32)));

补充

%为通配符，例如%班。

索引的数据量很小，便于核对查询是否有重复的。

对于已经设置UNIQUE约束的字段，在设置UNIQUE KEY(唯一约束)是多余的。

注意：

参数UNIQUE INDEX和UNIQUE KEY是用来指定字段为索引的，两者选择其中之一即可；
参数indexname是索引名字，可省略；

参数propnamen是索引对应的字段的名称，该字段必须为前面定义好的字段且必须定义为 UNIQUE 约束；

参数length是可选参数，其指索引的长度，必须是字符串类型才可以使用；

参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列，如果不指定，则为升序。

已存在的表上创建索引

方法1

CREATE UNIQUE INDEX indexname  
    ON tablename (propname [(length)] [ASC|DESC]);

方法2

ALTER TABLE tablename ADD UNIQUE INDEX | KEY indexname (propname [(length)] [ASC|DESC]);

全文索引

全文索引主要对字符串类型建立基于分词的索引，主要是基于CHAR、VARCHAR和TEXT的字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。

全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符。

MySQL从3.23.23版本开始支持全文索引，MySQL5.6以前只能在存储引擎为MyISAM的数据表上创建全文索引,5.6之后InnoDB开始支持全文索引(5.7之后支持中文全文索引) 。在默认情况下，全文索引的搜索执行方式为不区分大小写，如果全文索引所关联的字段为二进制数据类型，就以区分大小写的搜索方式执行。

创建表时定义索引

CREATE TABLE tablename(
    propname1 type1,
    propname2 type2,
    ……
    propnamen type..n,
    FULLTEXT INDEX | KEY
    [indexname] (propnamen [(length)] ) );

示例：

mysql> create database school;   #创建数据库school  
mysql> use school;   #选择数据库school  
mysql>create table class(id int, name varchar(128) UNIQUE, teacher varchar(64),comment varchar(1024),FULLTEXT INDEX index_comm(comment));       #创建表class, 并建立为comment 字段为全文索引  
mysql> insert into class values(1,'1班','Martin','我是一个兵，来自老百姓!');  # 插入记录1 
mysql> insert into class values(2,'2班','Rock','此班主任毕业自唐僧系');   # 插入记录2 
mysql> insert into class values(3,'3班','Janny','I''m Miss Zhang.');   #插入记录3  
mysql> select * from class where match(comment) AGAINST('我是一个兵');#利用全文检索索引快速查询记录

查找:

select* from tablename where match(propnamen)against('内容')；

已经存在的表上创建索引

方法1:执行create语句

 CREATE FULLTEXT INDEX indexname
    ON tablename( propname1 [ ( length ) ] );

mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64));       #创建表class, 并建立为id 字段索引  
mysql> create FULLTEXT index index_teacher on class(teacher );  #追加全文索引

方法2:执行ALTER TABLE语句

    ALTER TABLE tablename
    ADD FULLTEXT INDEX|KEY indexname(propname [(length)]);

alter table class add fulltext index name_index(teacher);

MySQL8中文分词支持

配置文件my.ini（Windows 10默认路径: C:\ProgramData\MySQL\MySQL Server 8.0）中增加如下配置项，同时重启MySQL80 服务:

[mysqld]
ngram_token_size=2

mysql> mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64),comment varchar(1024),FULLTEXT INDEX index_des(comment) with parser   ngram);       #创建表class, 并建立为comment 字段为全文索引

这样对相关词进行检索也可以查到了。

多列索引

多列索引，是指在创建索引时所关联的字段不是一个字段，而是多个字段，虽然可以通过所关联的字段进行查询，但是只有查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用。

创建表时定义索引

CREATE TABLE tablename(
    propname1 type1,
    ……
    propnamen type..n,
    INDEX | KEY [indexname] (propname1 [(length)] [ ASC | DESC ],
                             Propname2 [(length)] [ ASC | DESC ], 
                              ... ...                               
                             Propnamen [(length)] [ ASC | DESC ]) 
);

例如:

create table class(id int, name varchar(128) UNIQUE, teacher varchar(64), INDEX index_mult_columns(id, teacher));

就是在单个索引上增加了个索引列。

已存在的表上创建索引

方法1:执行create语句

CREATE  INDEX indexname
    ON tablename( propname1 [(length)] [ ASC | DESC ],  
                   Propname2 [(length)] [ ASC | DESC ],   
                              ... ...                                
                         Propnamen [(length)] [ ASC | DESC ]  );

方法2:执行ALTER TABLE 语句

 ALTER TABLE tablename
    ADD INDEX|KEY indexname(propname1 [(length)] [ ASC | DESC ],  
                   Propname2 [(length)] [ ASC | DESC ],              
                              ... ...                                          
                         Propnamen [(length)] [ ASC | DESC ]  );

隐藏索引

MySQL 8开始支持隐藏索引。隐藏索引提供了更人性化的数据库操作。

隐藏索引，顾名思义，让索引暂时不可见，不会被优化器使用。默认情况下索引是可见的。隐藏索引可以用来测试索引的性能。验证索引的必要性时不需要删除索引，可以先将索引隐藏，如果优化器性能无影响就可以真正地删除索引。

（测试索引性能 ）

ALTER TABLE tablename ALTER  INDEX  index_name INVISIBLE;  #隐藏索引
ALTER TABLE tablename ALTER  INDEX  index_name VISIBLE;    #取消隐藏

删除索引

所谓删除索引，就是删除表中已经创建的索引。之所以要删除索引，是因为这些索引会降低表的更新速度，影响数据库的性能。

DROP INDEX indexname ON tablename;

修改索引：先删除后增加。

索引的设计原则

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。

1.选择唯一性索引

唯一性索引的值是唯一的，可以更快速地通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个学生的信息，如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2.为经常需要排序、分组和联合的操作的字段建立索引

经常需要使用ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间，如果为其建立索引，可以有效地避免排序操作。

3.为经常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度，为这样的字段建立索引可以提高整个表的查询速度。

4.限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大，修改表时，对索引的重构和更新很麻烦。

5.尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间多。

6.尽量使用前缀来索引

如果索引的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间，如果只检索字段前面的若干字符，这样可以提高检索速度。

7.删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

表的插入

插入数据记录是常见的数据操作，可以显示向表中增加的新的数据记录。在MySQL中可以通过“INSERT INTO”语句来实现插入数据记录，该SQL语句可以通过如下4种方式使用：

插入完整数据记录、
插入部分数据记录、
插入多条数据记录和
插入JSON结构的数据记录（略）。

插入完整数据记录

NSERT INTO tablename(field1, field2, field3, ..., fieldn) VALUES(value1, value2, value3, ..., valuen);

tablename(xxx)省略

缩写为：

INSERT INTO tablename VALUES(value1, value2, value3, ..., valuen);

tablename表示所要插入完整记录的表名，

参数fieldn表示表中的字段名字，

参数valuen表示所要插入的数值，

并且参数fieldn与参数valuen一一对应。

插入部分数据记录

在插入完整数据记录的基础上指定要添加字段的名称。

INSERT INTO tablename(field1, field2, field3, ..., fieldn) VALUES(value1, value2, value3, ..., valuen);

注意：如果域没有设定默认值，也没有设定为自增长，则插入记录时必须插入值。

插入多条数据记录

INSERT INTO tablename(field1, field2, field3, ..., fieldn) 
VALUES(value11, value12, value13, ..., value1n)，   
(value21, value22, value23, ..., value2n)，          
... ....                                             
(valuen1, valuen2, valuen3, ..., valuenn);

每条要插入的数据都要按着tablename(x,)所指定的插入数据个数。

更新表中的数据记录

(更新就是修改)

更新数据记录是数据操作中常见的操作，可以更新表中已经存在数据记录中的值。在MySQL中可以通过UPDATE语句来实现更新数据记录，该SQL语句可以通过如下几种方式使用：

更新特定数据记录、更新所有数据记录、更新JSON结构的数据记录。

更新特定数据记录

UPDATE tablename SET field1 = value1, field2 = value2, field3 = value3, ..., fieldn = valuen) WHERE CONDITION;

上述语句中：

参数tablename表示所要更新数据记录的表名，

参数field表示表中所要更新数值的字段名字，

参数valuen表示更新后的数值，

参数CONDITION指定更新满足条件的特定数据记录。

mysql> update class set teacher = 'zyx' where teacher = 'zxx'; 
#通过teacher 字段匹配更新记录   
                或   
mysql> update class set teacher = 'zyx' where id = 2;  
#通过id(其他)字段匹配更新记录

个人理解:

可以将满足指定条件的数据记录都进行修改。

就是说要根据满足field和value(对应的字段和它的值)所进行更新(修改)。

更新所有记录

UPDATE tablename SET field1 = value1, field2 = value2, field3 = value3, ..., fieldn = valuen)  WHERE CONDITION;      
    或
UPDATE tablename SET field1 = value1, field2 = value2, field3 = value3, ..., fieldn = valuen) ；

在上述语句中：

参数tablename表示所要更新数据记录的表名，

参数field表示表中所要更新数值的字段名字，

参数valuen表示更新后的数值，

参数CONDITION表示满足表tablename中的所有数据记录，或不使用关键字WHERE语句。

例：

 update class set teacher = 'xx' ;  #把所有记录的teacher列改成xx       
       或   
mysql> update class set teacher = 'xx' where 1=1;

删除表中的数据记录

删除数据记录是数据操作中常见的操作，可以删除表中已经存在的数据记录。在MySQL中可以通过DELETE语句来删除数据记录，该SQL语句可以通过以下几种方式使用：删除特定数据记录、删除所有数据记录。

删除特定数据记录

DELETE FROM tablename WHERE CONDITION;

在上述语句中，参数tablename表示所要删除数据记录的表名，参数CONDITION指定删除满足条件的特定数据记录。

删除所有数据记录

DELETE FROM tablename WHERE CONDITION;      
或
DELETE FROM tablename；

在上述语句中，为了删除所有的数据记录,

参数CONDITION需要满足表tablename中所有数据记录，如id>0;

或者无关键字WHERE语句。

练习:设计游戏用户信息表

游戏玩家访问游戏客户端，通过客户端界面输入用户名和密码,
在游戏玩家点击”确认”后，客户端连接至数据库服务器对用户名和密码进行确认，

如果验证成功，则玩家可以进入大厅，如果失败，则不允许进入

 create table users (
 id int(11) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
 username   varchar(64)  NOT NULL UNIQUE COMMENT '用户名',
  
    加上下面的user_auths表，所以将password字段从users表中剥离
（ password   varchar(32)  NOT NULL COMMENT '密码',）
     
 nickname   varchar(64)  DEFAULT NULL COMMENT '昵称',
 mobile      char(11)     DEFAULT NULL COMMENT '手机号码',
 age         tinyint(3)    unsigned DEFAULT 18 COMMENT '年龄',
 idno        char(18)     DEFAULT NULL COMMENT '身份证号码'
);

作用1:验证用户身份

作用2:保存用户信息

更好的设计是分成2个表，用户信息表和用户验证表

基于以下三个方面考虑：

1.面向对象考虑

用户信息和用户验证是两种“对象”

2.性能方面考虑

登录验证的时候列较少，加快查询速度。

3.安全方面考虑

防止在查询用户信息时，将用户密码也直接查出来，容易被恶意攻击和进行恶意操作。

添加如下表:

用户验证表

create  table user_auths(
userid int(11) unsigned NOT NULL COMMENT '外键,对应users表中的id',
username   varchar(64)  NOT NULL UNIQUE COMMENT '用户名',
password varchar(32) NOT NULL COMMENT '密码',
FOREIGN KEY(userid) REFERENCES users(id)
);

可以md5('xxx')进行加密

可以理解为users表用来输入查询个人信息，user_auths表用来验证(注册、登录)。

数据量大的项目必须分表！

数据查询

查询数据是指从数据库中获取所需要的数据。

如Jack 要达到验证用户名和密码的目的，就需要从数据库已保存的用户表中读取当前用户的密码进行验证，以验明正身！保存查询数据是数据库操作中常用且重要的操作。

用户可以根据自己对数据的需求，使用不同的查询方式，获得不同的数据。

简单查询

在MySQL中可以通过SQL语句来实现基本数据查询，SQL语句可以通过如下多种使用：

查询所有字段数据
查询指定字段数据
避免重复数据查询
对结果进行排序和分组等查询。、

SELECT语句来查询数据，根据查询条件的不同，数据库系统会找到不同的数据，通过SELECT语句可以很方便地获取所需的信息。

SELECT field1 field2 … fieldn
    FROM tablename
    [WHERE CONDITION1]
    [GROUP BY fieldm [HAVING CONDITION2]]
    [ORDER BY fieldn [ASC|DESC]]；

filed1~fieldn参数表示需要查询的字段名；

tablename参数表示表的名称；

CONDITION1参数表示查询条件；

fieldm参数表示按该字段中的数据进行分组；

CONDITION2参数表示满足该表达式的数据才能输出；

fieldn参数指按该字段中数据进行排序。

排序方式由ASC和DESC两个参数指出；

ASC参数表示按升序的顺序进行排序，是默认参数；

DESC参数表示按降序的顺序进行排序。

(*号可以代替所有的字段)

列出表的所有字段

  SELECT field1,field2,…,fieldn FROM tablename;

filed1~fieldn参数表示需要查询的字段名；

tablename参数表示表的名称。

*符号表示所有字段

  SELECT * FROM tablename;

查询所有数据。

‘*’符号方式的优势比较明显，即可用该符号代替表中的所有字段，但是这种方式不够灵活，只能按照表中字段的固定顺序显示，不能随便改变字段的顺序。

查询指定字段数据

 SELECT field FROM tablename;

如果关键字SELECT后面的字段不包含在所查询的表中，那么MySQL会报错。

只显示该字段的一列数据。

DISTINCT查询

实现查询不重复。

SELECT DISTINCT field1,field2,…,fieldn FROM tablename;

可以多个字段组合查询。

例如两个字段组合查询，有两条数据中，其中一个字段相同，一个不同，那这两个就不是重复的，显示两个。反之，两个字段都相同，那就是重复的，只显示一个。

IN查询

关键字IN可以实现判断字段的数值是否在指定集合中，该关键字的具体语句形式如下：

SELECT field1,field2,…,fieldn FROM tablename WHERE filedn IN(value1,value2,value3,…,valuen);

参数fieldn表示名称为tablename的表中的字段名，

参数valuen表示集合中的值，通过关键字IN来判断字段fieldm的值是否在集合（value1,value2,value3,…,valuen）中，如果字段fieldm的值在集合中，就满足查询条件，该记录会被查询出来，否则不会被查询出来。

 select* from class where name in ('1班');

使用NOT IN可以反向查询，显示对应字段值之外的值。相当于C++中的!取反。

查询时慎用NULL

在具体使用关键字IN时，查询的集合中如果存在NULL，NULL对应的数据查不出来，其它字段对应的数据可以正常查询出来；
但如果使用关键字NOT IN，查询的集合中如果存在NULL，则不会查询到任何的结果。

BETWEEN AND查询

当我们需要查询指定范围内的数据（如： id 从0 到 100）的时候，MySQL提供了关键字BETWEEN AND，用来实现判断字段的数值是否在指定范围内的条件查询。该关键字的具体语法形式如下：

SELECT field1,field2,…,fieldn FROM tablename WHERE fieldm BETWEEN minvalue AND maxvalue

参数fieldn表示名称为tablename的表中的字段名，通过关键字BETWEEN和AND来设置字段field的取值范围，如果字段field的值在所指定的范围内，那么满足查询条件，该记录会被查询出来，否则不会被查询出来。

BETWEEN minvalue AND maxvalue，表示的是一个范围间的判断过程，只针对数字类型。

符合范围内的数据查询

通过关键字BETWEEN 和 AND设置查询范围。

代码示例：

 select * from class where chinese between  80 and 90 ;  #查询成绩表中语文成绩在80 和 90 之间的学员记录

不符合范围的数据记录查询

通过关键字NOT来实现反向查询

   SELECT name,chinese   
FROM grade WHERE Chinese NOT BETWEEN 85 AND 90;

等价于

select name,chinese from grade where chinese <85 or chinese >90;

LIKE模糊查找

当我们只想用字符串中间的一部分特征查找含有特征字串的信息时，MySQL提供了关键字LIKE来实现模糊查询，需要使用通配符，具体语法形式如下：

SELECT field1,field2,…,fieldn FROM tablename WHERE fieldm LIKE value;

参数tablename表示表名，

参数fieldn表示表中的字段名字，通过关键字LIKE来判断字段field的值是否与value字符串匹配，如果相匹配，则满足查询条件，该记录就会被查询出来；否则就不会被查询出来。

MySQL中，字符串必须加上单引号（''）和双引号（″″）。由于关键字LIKE可以实现模糊查询，因此该关键字后面的字符串参数除了可以使用完整的字符串外，还可以包含通配符。

符号	功能描述
-	该通配符能匹配单个字符
%	该通配符可以匹配任意长度的字符串，既可以是0个字符、1个字符，也可以是很多字符。

同样的，使用NOT关键字可以查询除了这个模糊之外的数据。

在对应查询字段前加上!是同样的效果。

利用模糊查找同样可以实现between的效果

例如:
select* from grade where english like '9_';
查询english在90-99之间的记录

对排序结果进行排序

在MySQL中，从表中查询出的数据可能是无序的，或者其排列顺序不是用户所期望的顺序，为了使查询结果的顺序满足用户的要求，可以使用关键字ORDER BY对记录进行排序，其语法形式如下：

SELECT field1, field2, field3, …, fieldn  FROM tablename ORDER BY fieldm [ASC|DESC]

参数tablename表示所要进行排序的表名，

参数fieldn表示表中的字段名字，参数fieldm表示按照该字段进行排序；ASC表示按升序进行排序；DESC表示按降序进行排序。默认的情况下按ASC进行排序。

注意:
如果存在一条记录字段的值为空值（NULL），那么按升序排序时，含空值的记录将最先显示，可以理解为空值是该字段的最小值；按降序排列时，字段为空值的记录将最后显示。

例如：

select* from grade order by chinese;# 按照语文成绩排序

简单分组查询

在具体使用统计函数时，都是针对表中所有记录数或指定特定条件（WHERE子句）的数据记录进行统计计算。在现实应用中，经常会先把所有数据记录进行分组，再对这些分组后的数据记录进行统计计算。

MySQL通过SQL语句GROUP BY来实现，分组数据查询语法如下：

SELECT function()[,filed ]
FROM tablename WHERE CONDITION GROUP BY field;

参数field表示某字段名，通过该字段对名称为tablename的表的数据记录进行分组。

依据该字段的不同名字进行分组

五个统计函数

1.统计数量

OUNT(*)：该种方式可以实现对表中记录进行统计,不管表字段中包含的是NULL值还是非NULL值。
COUNT(field):该种方式可以实现对指定字段的记录进行统计,在具体统计时将忽略NULL值。

2.统计计算平均值

该函数只有一种使用方式。
AVG(field)使用方式:该种方式可以实现对指定字段的平均值进行计算,在具体统计时将忽略NULL值。

3.统计计算求和

该函数只有一种使用方式。
SUM(field)使用方式:该种方式可以实现计算指定字段值之和,在具体统计时将忽略NULL值。

4.统计最大值

该函数只有一种使用方式。
MAX(field)使用方式:该种方式可以实现计算指定字段值中的最大值,在具体统计时将忽略NULL值。

5.统计最小值

该函数只有一种使用方式。
MIN(field)使用方式:该种方式可以实现计算指定字段值中的最小值,在具体统计时将忽略NULL值。

代码示例

select avg(math), class_id id from grade group by class_id;

统计分组

如果想显示每个分组中的字段，可以通过函数GROUP_CONCAT()来实现。该函数可以实现显示每个分组中的指定字段，函数的具体语法形式如下：

SELECT GROUP_CONCAT(field)
 FROM tablename WHERE CONDITION GROUP BY field;

代码示例:

select class_id,count(*),group_concat(name) from grade group by class_id;

联合查询

内连接查询

联合多个表来实现查询。

在MySQL中内连接数据查询通过“INNER JOIN…ON”语句来实现，语法形式如下所示:

SELECT field1,field2,…,fieldn FROM tablename1 INNER JOIN tablename2 [INNER JOIN tablenamen] ON CONDITION

其中：
参数fieldn表示要查询的字段名，来源于所连接的表tablename1和tablename2，关键字INNER JOIN表示表进行内连接，

参数CONDITION表示进行匹配的条件。

例如:

select * from class  inner join student  on class.id = student.class_id;  #查询class 表和student 表中班级字段相同的记录并进行内连接

无所谓两个表的前后顺序。

也可以通过起别名进行链接查询。

 select * from class as a inner join student as b where a.id = b.class_id; #同上，使用别名查询

SELECT field1, field2, ...,fieldn [AS] otherfieldn 
FROM tablename1 [AS] othertablename1, ...,
  Tablenamen [AS] othertablenamen ... where othertablename1.fieldx = othertablenamen.fieldx ... ;

其中，参数tablename为表原来的名字，参数othertablename为新表名，之所以要为表设置新的名字，是为了让SQL语句代码更加直观、更加人性化和实现更加复杂的功能。

select t1.id,t1.name,t1.sex,t1.class_id from student AS t1 inner join class t2 on  t1.class_id = t2.id;

自链接

就是表与其自身进行连接。

例如在一个学生表中找到小花的同班同学。

select* from student s1 inner join student s2 on s1.class_id = s2.class_id and s1.name = '小花'and s2.name !='小花';

等值连接

内连接查询中的等值连接就是在关键字ON后的匹配条件中通过等于关系运算符(=)来实现等值条件。

例如:将班级表和学生表连接到一起，条件是班级的id和学生的班级id相同。

select* from class as a inner join student as b where a.id = b.class_id;

不等值连接

同等值连接，只不过运算符不同。

内连接查询中的不等连接就是在关键字ON后的匹配条件中通过除了等于关系运算符来实现不等条件外，还可以使用关系运算符，包含“>”“>=”“<”“<=”和“!=”等运算符号。

外连接查询

当我们在查询数据时，要求返回所操作表中至少一个表的所有数据记录，通过SQL语句“OUTER JOIN…ON”来实现。外连接数据查询语法形式如下：

SELECT field1,field2,…,fieldn FROM tablename1 LEFT|RIGHT [OUTER] JOIN tablename2
 ON CONDITION

参数fieldn表示所要查询的字段名字，来源于所连接的表tablename1和tablename2，

关键字OUTER JOIN表示表进行外连接，

参数CONDITION表示进行匹配的条件。

左外连接

就是指新关系中执行匹配条件时，以关键字LEFT JOIN(outer可以省略)左边的表为参考表。左连接的结果包括LEFT OUTER字句中指定的左表的所有行，而不仅仅是连接列所匹配的行，如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表均为空值。

就是,left join左边的表中所有数据都会显示，而left join表右边的值根据on 后面的条件进行保留匹配。

例如:

 select* from class as a left join student as b on a.id = b.class_id;

右外连接

同左外连接，只是肯定显示(保留)的是right join的右边表。

无论是哪种查询方式，先写的表的字段在前面显示。

合并查询数据记录

在MySQL中通过关键字UNION来实现合并操作，即可以通过其将多个SELECT语句的查询结果合并在一起，组成新的关系。在MySQL软件中，合并查询数据记录可通过SQL语句UNION来实现，具体语法形式如下：

SELECT field1,field2,…,fieldn  FROM tablename1
    UNION | UNION ALL
    SELECT field1,field2,…,fieldn  FROM tablename2
    UNION | UNION ALL  SELECT field1,field2,…,fieldn ;

注意：
多个选择语句select 的列数相同就可以合并,union和union all的主要区别是union all是把结果集直接合并在一起，而union 是将union all后的结果再执行一次distinct，去除重复的记录后的结果。

前后的字段个数相同

例如：将class表中的老师和student表中的学生整合到一起。

select teacher from class union all select name  from student;

字段显示优化

select teacher as person from class union all select name as person from student;

子查询

所谓子查询，是指在一个查询中嵌套了其他的若干查询，即在一个SELECT查询语句的WHERE或FROM子句中包含另一个SELECT查询语句。在查询语句中，外层SELECT查询语句称为主查询，WHERE子句中的SELECT查询语句被称为子查询，也被称为嵌套查询。

通过子查询可以实现多表查询，该查询语句中可能包含IN、ANY、ALL和EXISTS等关键字，除此之外还可能包含比较运算符。理论上，子查询可以出现在查询语句的任意位置，但是在实际开发中子查询经常出现在WHERE和FROM子句中。

带比较运算符的子查询

子查询可以使用比较运算符。这些比较运算符包括=、!=、>、>=、<、<=和<>等。其中，<>与!=是等价的。比较运算符在子查询中使用得非常广泛，如查询分数、年龄、价格和收入等。

示例：查询云云的班主任是谁

select teacher from class where id = (select class_id from student where name ='云云'); # 查询在class表中，那个老师教的班级和student表中云云的班级相同

注意：使用比较运算符时，select子句获得的数据不能大于1条。

带有关键字IN的子查询

用途：一个查询语句的条件可能在另一个Select语句的结果中，这时候就可以使用IN / NOT IN 关键字。

示例：

select teacher from class where id in (select class_id from student where name like '小%');# 查询名字是小什么的班主任都是谁

带关键字EXISTS的子查询

关键字EXISTS表示存在，后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行；如果至少返回一行，那么EXISTS的结果为true，此时外层语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。

示例:

select* from class where id = 102 and exists(select* from student where class_id = 102); # 如果102班存在学生记录，就查询102班的班级信息

带关键字ANY的子查询

关键字ANY表示满足其中任一条件。使用关键ANY时，只要满足内层查询语句返回的结果中的任何一个就可以通过该条件来执行外层查询语句。

示例：

 select id,name,chinese+math+english as total from grade where chinese+english+math >= ANY(select score from scholarship);
 #查询能获得将学金的同学记录

带关键字ALL的子查询

类似带关键字ANY的子查询，内层查询语句要全部满足条件才能执行外部查询语句。

select id,name,chinese+math+english as total from grade where chinese + english + math >= ALL(select score from scholarship);

视图操作

例如:每个班抽出几个学生组成一个临时班级。

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。视图就像一个窗口，通过这个窗口可以看到系统的专门提供的数据，这样用户就可以不看整个数据库表中的数据，而只关心对自己有用的数据。

视图可以使用户的操作更方便，并且可以保障数据库系统的安全性。

为什么要使用视图

为了提高复杂的SQL语句的复用性和表的操作的安全性，MySQL数据库管理系统提供了视图特性。所谓视图，本质上是一种虚拟表，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储数据值的形式存在，行和列数据来自定义视图的查询所引用的基本表，并且在具体引用视图时动态生成。

视图使程序员只关心感兴趣的某些特定数据和他们所负责的特定任务。这样程序员只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高数据库中数据的安全性。

创建视图

虽然视图可以被看成是一种虚拟表，但是其物理上是不存在的，即MySQL并没有专门的位置为视图存储数据。根据视图的概念可以发现其数据来源于查询语句，因此创建视图的基本语法为：

CREATE[OR REPLACE] VIEW viewname[columnlist] AS SELECT statement;

查看用户权限创建视图的权限

创建视图需要登陆用户有相应的权限

select user,Select_priv,Create_view_priv from mysql.user;

示例:

#为学生表创建视图
CREATE VIEW view_student AS select id,class_id,name from student;
#查看视图
desc view_student;
#根据视图进行查询
select* from view student;

通过视同只允许查询者查看允许查看的那几个字段。

也可以修改视图中对应字段的值，就相当于更新了对应表中对应字段的值。

在多表上创建视图(联合多个表创建视图)
不能联合重复名字的字段。用as起别名解决。

CREATE[OR REPLACE] VIEW viewname[columnlist] AS SELECT statement

示例:

CREATE VIEW view_student_class AS select student.id, student.name, class.name as class_name,  class.teacher from class inner join student  on class.id = student.class_id;
# class.id == student.class_id 班级id和学生对应的班级id相同的数据。

更新视图

更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图实质是一个虚拟表，其中没有数据，通过视图更新时都是转换到基本表更新。更新视图时，只能更新权限范围内的数据，超出范围就不能更新了。

更新视图就是对原来的表进行更新。

示例:

update view_student set name='小花花' where name='小花'; #通过视图更新小花为小花花

不能更新的情况

视图中包含SUM()、COUNT()、MAX()和MIN()等函数
视图中包含UNION、UNION ALL、DISTINCT、GROUP BY和HAVING等关键字
视图对应的表存在没有默认值的列，而且该列没有包含在视图里
包含子查询的视图
其他特殊情况

就是，在视图视角下，准备输入的这条插入语句是否可以在原来的表情况下完成插入，能插入就能在当前视图下插入(更新视图)。

修改视图

修改视图是指修改数据库中存在的视图，当基本表的某些字段发生变化的时候，可以通过修改视图来保持与基本表的一致性。ALTER语句来修改视图。

示例:

alter view view_student_class as select id,class_id ,name1,sex from student;

追加视图

alter view view_student_class AS select student.id,student.name1,class.name,class.id as class_id,class.teacher from class inner join student on class.id = student.class_id;

删除视图

删除数据库中已经存在的视图。

删除是视图时，只能删除视图的定义，不会删除数据。

DROP VIEW viewname;

触发器

触发器（TRIGGER）是由事件来触发某个操作。这些事件包括INSERT语句、UPDATE语句和DELETE语句。当数据库系统执行这些事件时，就会激活触发器执行相应的操作。MySQL从5.0.2版本开始支持触发器。

创建触发器

CREATE TRIGGER来实现创建。

 CREATE trigger trigger_name BEFORE|AFTER trigger_EVENT ON TABLE_NAME FOR EACH ROW trigger_STMT

参数trigger_name表示要创建的触发器名；

参数BEFORE和AFTER指定了触发器执行的时间，前者在触发器事件之前执行触发器语句，后者在触发器事件之后执行触发器语句；

参数trigger_EVENT表示触发事件，即触发器执行条件，包含DELETE、INSERT和UPDATE语句；参数TABLE_NAME表示触发事件的操作表名；参数FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器；

参数trigger_STMT表示激活触发器后被执行的语句。执行语句中如果要引用更新记录中的字段，对于INSERT语句,只有NEW是合法的，表示当前已插入的记录；对于DELETE语句，只有OLD才合法，表示当前删除的记录；而UPDATE语句可以和NEW（更新后）以及OLD（更新前）同时使用。

示例:

create trigger tri_insert_student after insert on student for each row update class set count=count+1 where class.id = NEW.class_id; #创建触发器，新增学员班级人数增1

create trigger tri_delete_student after delete on student for each row update class set count=count-1 where id = OLD.class_id; 
#创建触发器，删除学员班级人数减1

触发器包含多条语句

CREATE   trigger trigger_name BEFORE|AFTER trigger_EVENT  ON TABLE_NAME FOR EACH ROW                                 
BEGIN                                                        
trigger_STMT                                                 
END

在上述语句中，比“只有一条执行语句的触发器”语法多出来两个关键字BEGIN和END，在这两个关键字之间是所要执行的多个执行语句的内容，执行语句之间用分号隔开。

在MySQL中，一般情况下用“;”符号作为语句的结束符号，可是在创建触发器时，需要用到“;”符号作为执行语句的结束符号。为了解决该问题，可以使用关键字DELIMITER语句。例如，“DELIMITER $$”可以将结束符号设置成“$$”。

示例：

  create trigger tri_delete_student after delete on student for each row 
        BEGIN                                    
         Delete from grade where id = OLD.id;  #删除成绩表中的记录                                                        
         update class set count=count-1 where id = OLD.class_id; #更新班级表中的记录   
         END;                                    
         $$

查看触发器

SHOW TRIGGER;

执行完“SHOW TRIGGERS”语句后会显示一个列表，在该列表中会显示出所有触发器的信息。其中，参数Trigger表示触发器的名称；参数Event表示触发器的激发事件；参数Table表示触发器对象触发事件所操作的表；参数Statement表示触发器激活时所执行的语句；参数Timing表示触发器所执行的时间。

示例：

从系统表查看触发器

在MySQL中，在系统数据库information_schema中存在一个存储所有触发器信息的系统表triggers，因此查询该表格的记录也可以实现查看触发器功能。系统表triggers的表结构。

mysql>  use information_schema;   #选择数据库information_schema                  
mysql>  select * from triggers;                                                        
mysql>  select * from triggers where trigger_name=’tri_delete_student’; #查询系统表triggers中的触发器

删除触发器

DROP TRIGGERS triggers_name;

存储过程和函数

创建存储过程

创建存储过程和函数是指将经常使用的一组SQL语句组合在一起，并将这些SQL语句当作一个整体存储在MySQL服务器中。存储程序可以分为存储过程和函数。在MySQL中创建存储过程使用的语句CREATE PROCEDURE。其语法形式如下：

CREATE PROCEDURE procedure_name([proc_param[,…]])   routine_body

在上述语句中，参数procedure_name表示所要创建的存储过程名字，参数proc_param表示存储过程的参数，参数routine_body表示存储过程的SQL语句代码，可以用BEGIN…END来标志SQL语句的开始和结束。

proc_param中每个参数的语法形式如下：

[IN|OUT|INOUT] param_name type

在上述语句中，每个参数由三部分组成，分别为输入/输出类型、参数名和参数类型。其中，输入/输出类型有三种类型，分别为IN（表示输入类型）、OUT（表示输出类型）、INOUT（表示输入/输出类型）。param_name表示参数名；type表示参数类型，可以是MySQL软件所支持的任意一个数据类型。

示例：

删除一名学生同时删除对应的成绩表中的信息，并将对应班级表中的人数-1。

create PROCEDURE  proc_delete_student (IN sid int )                                   
        BEGIN 
         declare cid  int ;   #定义变量cid                            
         Select class_id into cid from student where id = sid;    #通过查询语句设置变量      
 #根据输入的学生在student表拿到该学生对应的班级id，并将这个班级id存储到cid中，用于下面的操作。
         delete from grade where id = sid;  #删除成绩表中的记录  
         delete from student where id = sid;   #删除学生表中的记录                                                    
         update class set count=count-1 where id = cid; #更新班级表中的记录   
        END;                                                                        
        ##

调用存储过程

call proc_delete_student(3);# 删除学生的id号。

注意：上述代码并没有对合法性进行检查，是有出现错误的风险的！！！

在存储过程中使用变量

在存储过程和函数中，可以定义和使用变量。用户可以使用关键字DECLARE来定义变量，然后为变量赋值。这些变量的作用范围是在BEGIN…END程序段中。

定义变量

DECLARE var_name[,…] type [DEFAULT value]

其中，关键字DECLARE是用来声明变量的；

参数var_name是变量的名称，可以同时定义多个变量；

参数type用来指定变量的类型；

DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。

定义变量cid，数据类型为INT型，默认值为10，代码如下：

DECLARE cid INT DEFAULT 10;

为变量赋值

关键字SET用来为变量赋值；

参数var_name是变量的名称；

参数expr是赋值表达式。

一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。

 DECLARE cid INT DEFAULT 10;

示例：

set tmp_id = 88;

还可以使用SELECT…INTO语句为变量赋值。

SELECT col_name[,…] INTO var_name[,…]   

FROM table_name WHERE condition

参数col_name表示查询的字段名称；

参数var_name是变量的名称；

参数table_name指表的名称；

参数condition指查询条件。

示例：

 create procedure  query_student_class_info (IN sid int, OUT cname varchar(128), OUT ccount  int)                                   
        BEGIN   
            declare tmp_name varchar(128);
            declare tmp_count int;
            declare tmp_cid  int;
            select class_id into tmp_cid from student where id = sid;         
            select name, count into tmp_name, tmp_count from class where id = tmp_cid;
            set cname = tmp_name, ccount = tmp_count;
         END;    
         $$

调用存储过程

class query_student_class_info(4,@name,@count);
# 显示
select name,count;

光标的使用

查询语句可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记录。有些书上将光标称为游标。光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前，并且声明在变量和条件之后。

声明光标

DECLARE cursor_name CURSOR         
FOR select_statement;

参数cursor_name表示光标的名称；

参数select_statement表示SELECT语句的内容。

示例：

 create procedure  query_student (IN sid int, OUT cname varchar(128), OUT class_id  int )                                 
        BEGIN                                                  
            DECLARE cur_student CURSOR                     
                FOR SELECT name, class_id FROM  student;    
         END;                                                  
         $$

上面的字段中，光标的名称为cur_student;

select语句部分是从表student中查询出字段name和class_id的值。

打开光标

OPEN cursor_name;

使用光标

FETCH cursor_name 
    INTO var_name[var_name]

参数cursor_name表示光标的名称；

参数var_name表示将光标中的SELECT语句查询出来的信息存入该参数中。var_name必须在声明光标之前就定义好。

示例：

 create procedure query_student (IN sid int, OUT cname varchar(128), OUT cid int)                            
        BEGIN                               
            declare tmp_name varchar(128);  #必须定义在声明光标之前 
            declare tmp_cid  int;                     
            declare  done int default 0;               
            declare cur_student CURSOR FOR SELECT name, class_id FROM  student where id = sid;                 
            declare continue handler for not found set done = 1; #将结束标志绑定到游标上                           
            open  cur_student;                         
            select done;                               
            fetch cur_student into tmp_name, tmp_cid;   
            select done;                               
            select tmp_name, tmp_cid; #打印从光标中获取到的值                 
            close cur_student;                         
            set cname = tmp_name, cid = tmp_cid;       
         END;                                                                              
mysql> ##

流程控制的使用

IF语句

IF语句用来进行条件判断。根据条件执行不同的语句。

IF search_condition THEN statement_list       
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]                           
END  IF

参数search_condition表示条件判断语句；参数statement_list表示不同条件的执行语句。

示例：

 create procedure proc_test_if (IN input int, OUT output int)
        begin
            if input>20 then set input=input+1;
            elseif input=20 then  set input=input+2;
            else  set input = input+3;
            end if;

            set output = input;
        end; 
mysql>  $$

CASE语句

CASE语句可以实现比IF语句更复杂的条件判断。

CASE case_value                                  
WHEN when_value THEN statement_list            
[ WHEN when_value THEN statement_list ]          
[ELSE statement_list]                               
END CASE

其中，参数case_value表示条件判断的变量；

参数when_value表示变量的取值；

参数statement_list表示不同when_value值的执行语句。

类似IF ELSE语句。

示例:

  create procedure proc_test_case3 (IN input int, OUT output int)
       begin
       case input
            when 30 then set output=input+1;
            when 20 then  set output=input+2;
            else  set output = input+3;
            end case;      
        end; 
##

case语句要是确定的值，不能像IF语句一样是一个范围。

LOOP语句

LOOP语句可以使某些特定的语句重复执行，实现一简单的循环。

LOOP语句本身没有停止循环，只有遇到LEVEA语句等才能停止循环。

   [begin_label:] LOOP             
   statement_list                  
   END LOOP [end_label]

参数begin_label和参数end_label分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；

参数statement_list表示需要循坏执行的语句。

示例：

add_num:LOOP                 
     SET @count = @count + 1; 
END LOOP add_num;

该示例循环执行count加1的操作。因为没有跳出循环的语句，这个循环成了一个死循环。LOOP循环都以END LOOP结束。

LEVEA语句

LEAVE语句主要用于跳出循环控制，其语法形式如下：

LEAVE label

其中，参数label表示循环的标志。

相当于C/C++中的break

示例：

add_num: LOOP             
SET @count=@count + 1;
Select @count;
IF @count = 100 THEN 
    LEAVE add_num;     
END IF;
END LOOP add_num;

该示例循环执行count值加1的操作。当count的值等于100时，LEAVE语句跳出循环。

示例1：

 create procedure proc_test_loop(IN input int,OUT output int)
      begin
      add_num:LOOP
      set input = input +1;
      if input=100 then
       LEAVE add_num;
      end if;
      end loop add_num;
      set output = input;
      select input,output;
      end;
      ##

示例2：

create procedure proc_test_loop1(IN input int,OUT output int)
      begin
      set @count =1;//会话变量，只在当前窗口生效
      add_num:LOOP
      set @count = @count +1;
      if @count =100 then leave add_num ;
      select @count;
      end if;
      end LOOP add_num;
      end ;
      ##

ITERATE语句

ITERATE语句也是用来跳出循环的语句，但是ITERATE语句是跳出本次循环，然后直接进入下一次循环。

  ITERATE label

参数label表示循环的标志。

类似于C/C++中的continue

示例：

 create procedure proc_test_proc(IN input int,OUT output int)
      begin
       add_num1:LOOP
      if input>10 then iterate add_num1;
      else set output = input+100;
        leave add_num1;
      end if;
      end LOOP add_num1;
      end;
      ##

上述示例中，输入大于10的数就会陷入死循环。

REPEAT语句

REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。

先执行再判断条件。

[begin_label:] REPEAT        
statement_list;       
UNTIL search_condition 
END REPEAT [end_label]

参数statement_list表示循环的执行语句；

参数search_condition表示结束循环的条件，满足该条件时循环结束。

示例

create procedure proc_test_repeat2(IN input int,OUT output int)
      begin
      REPEAT
      set input = input + 1;
      UNTIL input >100
      END REPEAT;
      set output = input;
      end;
      ##

注意：结束条件后不用加；

WHILE语句

WHILE语句也是有条件控制的循环语句，但WHILE语句和REPEAT语句是不一样的。WHILE语句是当满足条件时执行循环内的语句。

先判断条件再执行语句。

[begin_label:] WHILE search_condition DO   
        Statement_list                      
END WHILE [end_label]

其中，参数statement_condition表示循环执行的条件，满足该条件时循环执行；参数statement_list表示循环的执行语句。

示例：

WHILE @count<100 DO       
SET @count = @count + 1;
END WHILE;

流程控制综合运用

示例：循环访问光标操作，访问光标中的所有记录。

reate procedure query_all_students (IN sid int, OUT cname varchar(128), OUT cid int)                                                                                    
        BEGIN                                                                             
            declare tmp_name varchar(128);    #必须定义在声明光标之前                                                                                             
            declare tmp_cid  int;                                                           
            declare  done int default 0;                                                                                                                                                
            declare cur_student CURSOR FOR SELECT name, class_id FROM  student ;                                                                                      
            declare continue handler for not found set done = 1; #将结束标志绑定到游标上                                                                             
            open  cur_student;                                                             
            read_loop:LOOP      #循环读取                                                                   
                fetch cur_student into tmp_name, tmp_cid;                                                                                                                              
                IF done=1 then                                                              
                    Leave read_loop;                                                                  
                END IF;                                                                     
                select tmp_name, tmp_cid;         #打印从光标中获取到的值                                                                                       
            END LOOP read_loop;                                                                                
            close cur_student;                                                              
            set cname = tmp_name, cid = tmp_cid;                                                                   END;  
           ##

示例：在学生表中插入一条记录，并返回记录的自增长id。

返回记录的自增长最新id,select last_insert_id();
而不是用select max(id) from student;避免多用户对数据库进行操作的情况。

mysql>  use school;   #选择数据库school                                                                                                                         
mysql> DELIMITER $$                                                                                
mysql> create procedure fetch_insert_student_id (IN p_name varchar(128), in p_class_id int, IN p_sex char(1), OUT rid int)                                                                                    
        BEGIN                                                                             
            Insert into student (name, class_id, sex) values(p_name, p_class_id, p_sex);                                                                                
            select last_insert_id() as rid;                                                                                                                     
         END;                                                                              
mysql>  $$                                                                               
mysql>  DELIMITER ;

从第二个示例不难看出，存储结构就是对一些语句的整合。

查看存储过程

存储过程创建以后，用户可以通过SHOW STATUS语句来查看存储过程的状态，也可以通过SHOW CREATE语句来查看存储过程的定义。用户也可以通过查询information_schema数据库下的Routines表来查看存储过程的信息。

查看存储过程

  SHOW PROCEDURE STATUS  [ like ‘pattern’ ] ;

参数PROCEDURE表示查询存储过程；

参数LIKE 'pattern'用来匹配存储过程的名称。

示例：

show procedure status like '%student%'##

查看存储过程的定义

  SHOW CREATE PROCEDURE proc_name

参数PROCEDURE表示查询存储过程；

参数proc_name表示存储过程的名称。

示例：

show create procedure fetch_insert_student_id##

查看存储过程的信息

存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。

SELECT * FROM information_schema.Routines
               Where ROUTINE_NAME = ‘proc_name’;

字段ROUTINE_NAME是Routines 存储存储过程和函数的列名称；

参数proc_name表示存储过程或函数的名称。

示例：

 select *from information_schema.Routines where ROUTINE_NAME = 'fetch_insert_student_id'##

删除存储过程

DRPO PROCEDURE proc_name;

关键字DROP PROCEDURE用来表示实现删除存储过程，

参数proc_name表示所要删除的存储过程名称。

数据库的存储引擎

什么是存储引擎

MySQL中存在多种存储引擎的概念。简而言之，存储引擎就是指表的类型。在具体开发时，为了提高MySQL数据库管理系统的使用效率和灵活性，可以根据实际需要来选择存储引擎。因为存储引擎指定了表的类型，即如何存储和索引数据、是否支持事务等，同时存储引擎也决定了表在计算机中的存储方式。

MySQL支持的引擎

查看存储引擎

SHOW ENGINES

查看当前默认存储引擎

SHOW VARIABLES LIKE 'default_storage_engine';

InnoDB存储引擎

nnoDB是MySQL数据库的一种存储引擎。InnoDB给MySQL的表提供了事务、回滚、崩溃修复能力和多版本并发控制的事务安全。MySQL从3.23.34a开始就包含InnoDB存储引擎。InnoDB是MySQL第一个提供外键约束的表引擎，而且InnoDB对事务处理的能力也是MySQL对其他存储引擎所无法与之比拟的(保证数据的安全)。

MySQL 5.6版本之后，除系统数据库之外，默认的存储引擎由MyISAM改为InnoDB，MySQL 8.0版本在原先的基础上将系统数据库的存储引擎也改为了InnoDB。

InnoDB存储引擎中支持自动增长列AUTO_INCREMENT。自动增长列的值不能为空，且值必须唯一。MySQL中规定自增列必须为主键。在插入值时，如果自动增长列不输入值，那么插入的值为自动增长后的值；如果输入的值为0或空（NULL），那么插入的值也为自动增长后的值；如果插入某个确定的值，且该值在前面没有出现过，那么可以直接插入。

InnoDB存储引擎中支持外键（FOREIGN KEY）。外键所在的表为子表，外键所依赖的表为父表。父表中被子表外键关联的字段必须为主键。当删除、更新父表的某条信息时，子表也必须有相应的改变。

InnoDB存储引擎的优势在于提供了良好的事务管理、崩溃修复能力和并发控制；缺点是其读写效率稍差，占用的数据空间相对比较大。

MylSAM存储引擎

MyISAM存储引擎是MySQL中常见的存储引擎，曾是MySQL的默认存储引擎。MyISAM存储引擎是基于ISAM存储引擎发展起来的。MyISAM增加了很多有用的扩展。

MyISAM存储引擎的表存储成3个文件。文件的名字与表名相同，或站名包括frm、MYD和MYI。其中，frm为扩展名的文件存储表的结构；MYD为扩展名的文件存储数据，是MYData的缩写；MYI为扩展名的文件存储索引，是MYIndex的缩写。

基于MyISAM存储引擎的表支持3种存储格式，包括静态型、动态型和压缩型。其中，静态型为MyISAM存储引擎的默认存储格式，其字段是固定长度的；动态型包含变长字段，记录的长度不是固定的；压缩型需要使用myiampack工具创建，占用的磁盘空间较小。

MyISAM存储引擎的优势在于占用空间小，处理速度快；缺点是不支持事务的完整性和并发性。

MEMORY存储引擎

MEMORY存储引擎是MySQL中一类特殊存储引擎。其使用存储在内存中的内容来创建表，而且所有数据也放在内存中。这些特性都与InnoDB存储引擎、MyISAM存储引擎不同。

每个基于MEMORY存储引擎的表实际对应一个磁盘文件，该文件的文件名与表名相同，类型为frm类型，该文件中只存储表的结构，而其数据文件都是存储在内存中的。这样有利于数据的快速处理，提供整个表的处理效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要使用了，可以释放这些内存，甚至可以删除不需要的表。

MEMORY存储引擎默认使用哈希（HASH）索引。其速度要比使用B型树（BTREE）索引快。如果读者希望使用B型树索引，可以在创建索引时选择使用。

MEMORY表的大小是受到限制的。表的大小主要取决于两个参数，分别是max_rows和max_heap_table_size。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默认为16MB，可以按需要进行扩大。因此，其存在于内存中的特性，这类表的处理速度非常快。但是，其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心。

选出存储引擎

在具体使用MySQL数据库管理系统时，选择一个合适的存储引擎是非常复杂的问题。因为每种存储引擎都有自己的特性、优势和应用场合，所以不能随便选择存储引擎。为了能够正确地选择存储引擎，必须掌握各种存储引擎的特性。

下面从存储引擎的事务安全、存储限制、空间使用、内存使用、插入数据的速度和对外键的支持等角度来比较InnoDB、MyISAM和MEMORY。

表锁：一个用户一个用户的用，当某一位用户插入数据时，直接“占据”表的所有位置，其他用户不许插入。

行锁:一个用户只“占据”他要插入数据的那一行。

相关联想:上厕所，你只锁住了你的那个坑位，而不是锁住厕所的大门。

就是：是否允许多个用户同时更新一个表。

个简单的循环，LOOP语句本身没有停止循环，只有遇到LEAVE

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

【MySQL】MySQL知识总结

MySQL

前言

数据库概念

为什么要使用数据库？

MySql简介

数据库操作

关系模型

查看数据库

创建数据库

选择数据库

删除数据库

数据库命名规范

数据表的操作

创建一个班级表

表的创建

列名

列的类型定义

整型类型

(1)类型名后面的小括号

（2）UNSIGNED(无符号)

(3)AUTO_INCREMENT

浮点类型和定点数类型

日期和时间类型

字符串类型

CHAR类型和VARCHAR类型

TEXT类型

字符串类型选择练习

ENUM和SET类型

二进制类型

BINARY和VARBINARY

BIT类型

BLOB类型

其他数据类型

列的完整性约束

设置表字段的主键约束(PRIMARY KEY,PK)

单字段主键

多字节主键

设置表字段的外键约束(FORENIGN KEY,FK)

设置列的非空约束(NOT NULL,NK)

设置表字段唯一约束(UNIQUE,UK)

设置表字段值自动增加(AUTO_INCREMENT)

设置表字段的默认值(DEFAULT)

调整列的完整性约束

修改主键PK、外键FK和 唯一键UK

修改默认值DEFAULT、自增长和非空NK

表索引

索引概述

索引类型

索引存储

索引优点

索引缺点

注意

创建和查看索引

普通索引

查询索引执行情况

唯一索引

全文索引

MySQL8中文分词支持

多列索引

隐藏索引

删除索引

索引的设计原则

表的插入

插入完整数据记录

插入部分数据记录

修改主键PK、外键FK和唯一键UK