文章目录
MySQL 冲冲冲
1. MySQL的基本操作
-
按照好MySQL后需要先设置环境变量
-
命令行启动(要以管理员身份运行的终端)
查看服务命令： net start 查看MySQL是否在运行中
关闭服务命令: net stop MySQL80
启动服务命令: net start MySQL80
控制台登陆MySQL命令（设置好环境变量的情况下）
- mysql -h 127.0.0.1 -uroot -p密码
2. MySQL数据库命名规范
-
注意：数据库是不区分大小写的，所以大驼峰法和小驼峰法在这里都不适用，一般用下划线区分两个单词
-
就算用大写创建的数据库，用小写字母依然可以成功访问
-
可以采用26个英文字母 (不区分大小写) 和0-9的自然数 (一般不需要) 加上下划线 ‘’ 组成，命名简介明确 (School)，多个单词用下划线 ‘’ 分隔，一个项目一个数据库，多个项目慎用同一个数据库!!!
3. 数据库操作
3.1 关系模型
关系数据库是建立在关系模型上的。而关系模型本质上就是若干个存储数据的二维表，可以把它们看作很多Excel表。
-
表的每一行称为记录（Record），记录是一个逻辑意义上的数据。
-
表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。
-
字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为NULL。注意NULL表示字段数据不存在。一个整型字段如果为NULL不表示它的值为0，同样的，一个字符串型字段为NULL也不表示它的值为空串’'。
-
通常情况下，字段应该避免允许为NULL。不允许为NULL可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为NULL。
3.2 数据库的操作
像文件一样，我们可以随时对数据库执行如下操作：
① 创建数据库
② 查看数据库
③ 选择数据库
④ 删除数据库
创建数据库
创建数据库是指在数据库系统中划分一块空间，用来存储相应的数据，这是进行表操作的基础，也是进行数据库管理的基础。
（1）在MySQL中，创建数据库之前，可以使用SHOW语句来显示当前已经存在的数据库，具体SQL语句如下：
show databases;
（2）创建数据库的SQL语句如下:
create database database_name
- (其中参数database_name表示所要创建的数据库的名称)
查看数据库
查看数据库在上面已提及，SQL语句如下：
show databases;
选择数据库
在数据库管理系统中一般会存在许多数据库。在操作数据库对象之前，需要先选择一个数据库。
- 在MySQL中选择数据库可以通过SQL语句USE来实现，其语法形式如下：
use database_name;
注：在上述语句中，database_name参数表示所要选择的数据库名字。
删除数据库
在删除数据库之前，首先需要确定所操作的数据库对象已经存在。在MySQL中删除数据库可以通过SQL语句DROP DATABASE来实现，其语法形式如下：
drop database database_name;
注：在上述语句中，database_name参数表示所要删除的数据库名字。
切记： 删除数据库时mysql 不会确认，千万要小心！！！
4. 数据表操作
在MySQL数据库中，表是一种很重要的数据库对象，是组成数据库的基本元素，由若干个字段组成，主要用来实现存储数据记录。表的操作包含创建表、查询表、修改表和删除表，这些操作是数据库对象的表管理中最基本也是最重要的操作。
4.1 从最简单的表操作开始
创建班级表：
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6QPRWuDZ-1668351348156)(D:\冲击offer\博客\数据库\assets\image-20221103211237978.png)]](https://i-blog.csdnimg.cn/blog_migrate/0ca6e316fdbca3d84872b579c74f3165.png)
操作语句：
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 中的全部记录
4.2 查看表内容和查看表结构
- 前提进入数据库中才能查看数据库中的表
查看表结构
desc class;#desc 表名
查看表中的内容
select * from class;
4.3 表的创建
基本语法：
CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];
- create table 命令语法比较多，其主要是由表创建定义（create-definition）、表选项（table-options）和分区选项（partition-options）所组成的。
mysql> use school;#选择数据库school
mysql>create table class1(class_id int, class_name varchar(128), class_teacher varchar(64));#创建表class
create table语句的主要语法及使用说明如下：
-
create table：用于创建给定名称的表，必须拥有表CREATE的权限。
-
在没use 表名的情况下创建表：
指定要创建表的名称，表名称可以被指定为 数据库名.表名，以便在特定的数据库中创建表。
例如：创建表class2
mysql>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_name varchar(128) NOT NULL, class_teacher varchar(64)UNIQUE);#创建表class3
-
PRIMARY KEY：主键具有唯一性，比如每个人的身份证；------primary key :主要的值
-
NOT NULL：不能为空-----------------net null :不为空
-
UNIQUE：表示在这个表中，这列不能有相同的 -------unique :独特
注意：
-
默认的情况是，表被创建到当前的数据库中。若表已存在、没有当前数据库或者数据库不存在，则会出现错误。
-
提示：使用 create table 创建表时，必须注意以下信息：
-
要创建的表的名称不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等。
-
数据表中每个列（字段）的名称和数据类型，如果创建多个列，要用逗号隔开。
列名
-
全部小写命名，尽量避免出现大写
-
字段必须填写描述信息
-
禁止使用数据库关键字
-
在命名表的列时，不要重复表的名称（如：在 user 表中，出现 user_name 字段）
-
字段命名使用完整名称（尽量用完整英文单词）
4.3.1 列的类型定义
4.3.1.1 整形类型
整数类型是数据库中最基本的数据类型。标准SQL中支持INTEGER和SMALLINT这两种数据类型。MySQL数据库除了支持这两种类型以外，还扩展支持了TINYINT、MEDIUMINT和BIGINT。下表从不同整数类型的字节数、取值范围等方面进行对比。
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0Z4mRaD2-1668351348157)(D:\冲击offer\博客\数据库\assets\wps1-16676253268051.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/c7de793df7623fd315cef5de3850114b.jpeg)
mysql>use school;#选择数据库school
mysql>create table class5(class_id int , class_name varchar(128),
class_teacher varchar(64) );#创建表class5
整数类型的附带属性
- 类型名称后面的小括号指定显示宽度（并不是该类型占用字节数）。
- 如果不显示指定宽度则默认为tinyint(3)、 smallint(5)、 mediumint(8)、 int(11) 和 bigint(20)。
- 一般配合 zerofill 使用，顾名思义，zerofill 就是用“0”填充的意思，也就是在数字位数不够的空间用字符“0”填满。
mysql>use school;#选择数据库school
mysql>create table class6(class_id integer(5) zerofill, class_name varchar(128),
class_teacher varchar(64) );#创建表class6
unsigned（无符号）----很少使用
- 如果需要在字段里面保存非负数或者需要较大的上限值时，可以用此选项，
- 它的取值范围是正常值的下限取 0，上限取原值的 2 倍，
- 例如，tinyint 有符号范围是-128～+127，而无符号范围是 0～255。
- 如果一个列指定为 zerofill，则 MySQL 自动为该列添加 unsigned 属性。
mysql>use school;#选择数据库school
mysql>create table class6(id integer unsigned, name varchar(128), teacher varchar(64) );#创建表class6
mysql>create table class7(id integer zerofill, name varchar(128),
teacher varchar(64) );#创建表class7, id类型为 int unsigned
auto_increment(用的较多)
- 在需要产生唯一标识符或顺序值时，可利用此属性，这个属性只用于整数类型。
- auto_increment 值一般从 1 开始，每行增加 1。 一个表中最多只能有一个 auto_increment列 。
- 对于任何想要使用 auto_increment 的列，应该定义为 not null，并定义为 primary key 或定义为 unique 键(这两个属性中就包括了not null)。
- 例如，可按下列任何一种方式定义 auto_increment 列：
mysql>use school;#选择数据库school
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 具有自增长属性
在不输入id的情况下,让id自增长 -------需要指明values中的值是给哪一个的
inser into class8 (name , teacher) values('六年级','王老师')
4.3.1.2 浮点类型和定点数类型
数据表中用浮点数类型和定点数类型来表示小数。浮点数类型包括单精度浮点数（FLOAT型）和双精度浮点数（DOUBLE型）。定点数类型就是DECIMAL型。下面从这三种类型的字节数、取值范围等方面进行对比，如下表所示。
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5emg2fsE-1668351348158)(D:\冲击offer\博客\数据库\assets\wps1-16683392179491.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/0705124e1a6d0d0ac976245f444827df.jpeg)
三种类型的区别：
- float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double都是浮点型，而decimal是定点型；
mysql>use school;#选择数据库school
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)。
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XlwEFQNn-1668351348158)(D:\冲击offer\博客\数据库\assets\image-20221113203705702.png)]](https://i-blog.csdnimg.cn/blog_migrate/64296a4d2a3fb849ab934eec6d1e45ee.png)
上述：20 = （小数 + 整数） 的位数 10 = 小数的位数
- 忠告: 当我们需要存储小数，并且有精度要求，比如存储金额时，通常会考虑使用DECIMAL字段类型!!
4.3.1.3 日期和时间类型
- 日期与时间类型是为了方便在数据库中存储日期和时间而设计的，数据库有多种表示日期和时间的数据类型。其中，YEAR类型表示年，DATE类型表示日期，TIME类型表示时间，DATETIME和TIMESTAMP表示日期和时间。下面从这5种日期与时间类型的字节数、取值范围和零值等方面进行对比，如下表所示。
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rVQwEMqx-1668351348158)(D:\冲击offer\博客\数据库\assets\wps6.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/5587dfd8d67209d31c711d0105ffdfff.jpeg)
-
每种日期与时间类型都有一个有效范围。如果插入的值超过了这个范围，系统就会报错，并将零值插入到数据库中。不同的日期与时间类型均有不同的零值.
-
插入日期时间时，日期年月日和时间时分秒可以使用 “*: - _ /* ”中的任意字符来分隔,如果单独插入时间，则会报错！！！
mysql>use test;#选择数据库test
mysql>create table date_example (e_date date, e_datetime datetime, e_timestamp timestamp, e_time time, e_year year);#创建表dt_example
#使用分隔符的时候,尽量标准,不然看着很杂
mysql>insert into date_example values('2020-5-9', '2020-5-9 15:01:00', '2020-05-09 15:01:00', '15:56:01', 2011);
mysql>insert into date_example values('2020-5/9', '2020:5-9 15/01-00', '2020:05/09 15-01_00', '15:56:01', '2011');#效果同上面插入语句
也可以通过mysql 本身提供的时间函数获取：
常用的时间函数:
- 用函数直接读出当前时间
-
curdate() - 获得当前的DATE, 可直接插入DATE 类型中.
-
now() - 获得当前的DATETIME, 可直接插入DATETIME 和TIMESTAMP类型中.
后面几项都需要先给函数传入now();
-
time() - 获取参数给定时间串中的时分秒，可直接插入TIME 类型中.
-
year() - 获取参数给定时间串中的年份，可直接插入YEAR类型中.
-
month() 、day()、hour()、minute()、second() 获取参数给定时间串中的月、日、时、分、秒值.
mysql>use test;#选择数据库test
mysql>insert into date_example values(CURDATE(), NOW(), NOW(), time(NOW()), YEAR(NOW()) );
4.3.1.4 字符串类型
char类型和varchar类型
char类型和varchar类型都在创建表时指定了最大长度，其基本形式如下：
# 字符串类型（M）
-
其中，“字符串类型”参数指定了数据类型为char类型还是varchar类型；M参数指定了该字符串的最大长度为M。例如，char(4)就是数据类型为char类型，其最大长度为4。
-
char类型的长度是固定的，在创建表时就指定了。其长度可以是0~255的任意值。例如，char(100)就是指定char类型的长度为100。char存储值时，它们会用空格右填充到指定的长度, 所以在字符串尾部如果有空格的话不可以用char存储，因为空格会被认为字符串结束符。
-
varchar类型的长度是可变的，在创建表时指定了最大长度。定义时，其最大值可以取0~65535之间的任意值。指定varchar类型的最大值以后，其长度可以在0到最大长度之间。例如，varchar(100)的最大长度是100，但是不是每条记录都要占用100个字节，而是在这个最大值范围内使用多少就分配多少。varchar类型实际占用的空间为字符串的实际长度加1或2，这样即可有效节约系统的空间。
| 值 | char(4) | 存储字节数 | varchar(4) | 存储字节数 |
|---|---|---|---|---|
| ‘’ | ‘’ | 4 | ‘’ | 1 |
| ‘ab’ | ‘ab’ | 4 | ‘ab’ | 3 |
| ‘abcd’ | ‘abcd’ | 4 | ‘abcd’ | 5 |
| ‘abcdefgh’ | - | #插入失败 | - | #插入失败 |
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 后面屏蔽的空格原型毕露
实战建议
· 1、 char一定会使用指定的空间，varchar是根据数据来定空间
· 2、 char的数据查询效率比varchar高：varchar是需要通过后面的记录数来计算
· 3、 如果确定数据一定是占指定长度，那么使用char类型；
· 4、 如果不确定数据到底有多少，那么使用varchar类型；
· 5、 如果数据长度超过255个字符而在65535之内，直接使用varchar
· 6、 如果字符串尾部要保留空格，必须选择varchar
4.3.1.5 TEXT类型
- TEXT类型是一种特殊的字符串类型，包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT，其长度和存储空间的对比下表所示:
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WRvuXv6n-1668610852650)(D:\冲击offer\博客\数据库\assets\wps1-16685171162101.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/13f6f12b4ab3ca677755c4a6b0806722.jpeg)
- 各种TEXT类型的区别在于允许的长度和存储空间不同。因此，在这几种TEXT类型中，根据需求选取既能满足需要又节省空间的类型即可。
特别注意：
以上各类型无须指定长度！
允许的长度是指实际存储的字节数，而不是实际的字符个数，比如假设一个中文字符占两个字节，那么TEXT 类型可存储 65535/2 = 32767 个中文字符，而varchar(100)可存储100个中文字符，实际占200个字节，但varchar(65535) 并不能存储65535个中文字符，因为已超出表达范围.
mysql>use test;#选择数据库test
mysql>create table text_example(e_text tinytext, v_char varchar(255));#创建数据库表，e_text 可存储255个字节，v_char可存储255个字符
mysql>insert into char_example values(90个中文字符,90个中文字符);#插入失败，utfmb4 用3个字节表示一个中文汉字,会超出tinytext 保存范围
mysql>insert into char_example values(80个中文字符,100个中文字符);#插入成功
实战建议
-
char长度固定， 即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定。超过255字 节的只能用varchar或者text；
-
varchar可变长度，可以设置最大长度；适合用在长度可变的属性。
-
text不设置长度， 当不知道属性的最大长度时，适合用text， 能用varchar的地方不用text；
-
如果都可以选择，按照查询速度： char最快， varchar次之，text最慢。
-
char:有可能会浪费内存,如果是固定长度且在255范围之内的用char
-
varchar:如果数据经常被修改且在65535的范围之内,则用varchar
4.3.1.6 实战练习
创建一个用户信息表，用来存储用户的如下信息：姓名，手机号码，家庭地址，个人简介，性别，年龄，身份证号。数据库表名可定义为userinfo, 同时增加一个列id 用来作为每一条记录的唯一标识，并设为主键，自加！
create table people(
id int(11) unsigned primary key auto_increment not null comment'主键',
name varchar(64) default null comment'姓名',
shouji int(11) unsigned default null comment'电话号码',
address varchar(64) default null comment'住址',
desprition varchar(64) default null comment'简介',
sex char(1) default null comment'性别',
age tinyint default null comment'年龄',
idon char(11) default null comment'身份证号');
解析:
Default null :允许为空
Default 0:默认为0
不成文规定:给定大小的时候一般是2的次方 64 128
身份证号码一般为11位,而且有的人的身份证有字母,所以用char(11)
看注释的语句:show create table people;
利用自增长id的时候,需要指定要插入目录 插入:insert into userinfo(name,…)values(‘王鹏程’,’186868…’,’
4.3.1.7 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); #插入记录可以用数值表示
- 如果ENUM类型加上了NOT NULL属性，其默认值为取值列表的第一个元素。如果不加NOT NULL属性，ENUM类型将允许插入NULL，而且NULL为默认值。
create table enum_example (e_enum enum(‘男’,’女’,’选择保密’,name varchar(64));
Insert into enum_example(name) values(‘Martin’);
SET类型
-
在创建表时，SET类型的取值范围就以列表的形式指定了，其基本形式如下：属性名 SET(‘值1’, ‘值2’, …, ‘值n’) 64个字节8位
-
其中，属性名参数指字段的名称，“值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;#以整数的方式查询
4.3.1.8 二进制类型
二进制数据和文本数据在mysql 中的最大区别在于：
-
二进制类型存储原始的二进制数据(如图片，视频，exe文件等)。文本类型(TEXT)用来存储字符字符串(如由英文字符、中文字符或其它语言字符组成的字符串)。
-
二进制类型没有字符集，并且排序和比较基于列值字节的数值。而TEXT类型有字符集，并且根据字符集的校对规则对值进行排序和比较。
-
二进制类型是存储二进制数据的数据类型，包括BINARY、VARBINARY、BIT、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。二进制类型之间的对比如下表所示。
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UxXWDVSU-1668610852650)(D:\冲击offer\博客\数据库\assets\wps1-16686099018583.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/ed3b0e1035f512e1aceff9a0ce568c99.jpeg)
可以分成三个部分
第一部分 类比char varchar
binary:长度固定,若写入的长度小于可保存的最大长度,则用0填充
varbinary:在保存最大长度的范围内,写入多少长度就占多少字节
第二部分
bin:一个字节存8个位
第三部分 类比text
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到最大长度之间，在这个最大值范围内使用多少就分配多少。
mysql> use test; #选择数据库test
mysql> create table bin_example(e_bin binary(5),e_varbin varbinary(5)); #创建表
mysql> insert into bin_example values( 'ab','ab'); #插入记录,可以是普通字符串
mysql> insert into bin_example values( b'0110000101100010',b'0110000101100010'); #插入记录,可以是二进制，与上例等同
mysql> select * from bit_example ; #以十六进制的方式显示
BIT类型
BIT类型在创建表时指定最大长度，其基本形式如下：
BIT（M）
- 其中，“M”指定该二进制数的最大存储长度为M，M的最大值为64（最大存储64个位）。例如，BIT(4)就是指数据类型为BIT类型，长度为4（可以存放4个二进制的数）。若字段的类型BIT(4)存储的数据是0~15（最大存储1111），因为变成二进制之后15的值为1111，则其长度为4。如果插入的值为16，其二进制数为10000，长度为5，超过了最大长度，因此大于16的数是不能插入BIT(4)类型字段中的。
操作要点:
-
插入数据时，使用 b位串的方式插入相应值!
-
查询时,可以使用 bin() 、oct() 、hex() 函数讲字段的值转成相应的二进制、八进制和十六进制.
mysql> use test; #选择数据库test
mysql> create table bit_example (b bit(8) ); #创建表
mysql> insert into bit_example values( b'10110111' ); #插入记录,可以是二进制位
以二进制方式查看:
mysql> insert into bit_example values( 'a' ); #插入记录,可以是字符，但不能超出字节长度
mysql> select bin(b) from bit_example ; #以二进制的方式显示字段值
- 这里a的二进制数前面省略了一个0
BLOB类型
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q7ZQ2c8F-1668610852651)(D:\冲击offer\博客\数据库\assets\wps7.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/ac86e68ed8ace9344cfc8796c9d7f0e6.jpeg)
1.以上各类型无须指定长度！
2.允许的长度是指实际存储的字节数，不用考虑字符编码。
3.第一（tinyblob）/ 二(blob) / 三(mediumblob) / 四(longblob)个字节存放后续保存字节的数目
实战建议
· 1、 binary长度固定， 即每条数据占用等长字节空间；保存长度不超过255字节的二进制数据；
· 2、 varbinary可变长度，可以设置最大长度,最大长度65535；适合用在长度可变的二进制数据;
· 3、 blob不设置长度， 当不知道属性的最大长度时，适合用blob， 能用varbinary的地方不用blob；
· 4、 如果都可以选择，按照查询速度： binary最快， varbinary次之，blob最慢。
4.3.1.9 列的完整性约束
完整性约束条件是对字段进行限制，要求用户对该属性进行的操作符合特定的要求。如果不满足完整性约束条件，数据库系统将不再执行用户的操作。下表是完整性约束条件:
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-91Twmnpb-1668680234457)(D:\冲击offer\博客\数据库\assets\wps1-16686789394581.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/63d153792f8d05888a225563aec39209.jpeg)
设置表字段的主键约束（PRIMARY KEY，PK）
-
主键是表的一个特殊字段，能唯一标识该表中的每条信息。主键和记录的关系，如同身份证和人的关系。主键用来标识每个记录，每个记录的主键值都不同。身份证用来表明人的身份，每个人都具有唯一的身份证号。设置表的主键是指在创建表时设置表的某个字段为该表的主键。
-
主键的主要目的是帮助数据库管理系统以最快的速度查找到表的某一条信息。主键必须满足的条件就是主键必须是唯一的，表中任意两条记录的主键字段的值不能相同，并且是非空值。主键可以是单一的字段，也可以是多个字段的组合。
单字段主键
单字段主键的语法规则如下：
CREATE TABLE tablename(
propName propType PRIMARY KEY, ......
);
或
CREATE TABLE tablename(
propName propType , ......
PRIMARY KEY(propType）
);
- 创建表school，设置id字段为PK约束，再查看class表信息，SQL语句如下
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,'一班','martin'); #插入成功
mysql> insert into class VALUES(1,'二班','rock'); #因主键约束，插入失败
Mysql 支持给主键设置名字：
CREATE TABLE tablename(
propName propType , ......
CONSTRAINT pk_name PRIMARY KEY(propType）
);
给id字段的PK约束设置一个名字，可以执行SQL语句CONSTRAINT。创建表class:
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int , name varchar(128), teacher varchar(64), CONSTRAINT id_pk PRIMARY KEY (id)); #创建表class
mysql> desc class ; #查询表class 的定义, describe class 效果等同
mysql> show create table class ; #同样可以实现查询表class 的定义
mysql> insert into class VALUES(1,'一班','martin'); #插入成功
mysql> insert into class VALUES(1,'二班','rock'); #因主键约束，插入失败
多字段主键
-
一个表最多只能有一个组件,这里的多字段联合成为一个组件
-
主键是由多个属性组合而成时，在属性定义完之后统一设置主键。语法规则如下：
-
主键中只要有一个属性不一样,就可以正常插入
CREATE TABLE tablename(
propName1 propType ,
propName2 propType ,
......
[CONSTRAINT pk_name]PRIMARY KEY(propName1, propName2)
);
多字段主键的设置
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class3(id int, name varchar(128), teacher varchar(64), CONSTRAINT id_pk PRIMARY KEY(id,name)); #创建表class3,设置联合主键
mysql> desc class3 ; #查询表class3 的定义, describe class3 效果等同
mysql> insert into class VALUES(1,'一班','martin'); #插入成功
mysql> insert into class VALUES(1,'二班','cuicui'); #插入成功
mysql> insert into class VALUES(1,'一班','rock'); #因联合主键约束，插入失败
设置表字段的外键约束（FOREIGN KEY，FK）
-
外键是表的一个特殊字段，外键约束是为了保证多个表（通常为两个表）之间的参照完整性，即构建两个表的字段之间的参照关系。
-
设置外键约束的两个表之间具有父子关系，即子表中某个字段的取值范围由父表决定。例如，表示一个班级和学生关系，即每个班级有多个学生。首先应该有两个表：班级表和学生表，然后学生表有一个表示班级编号的字段no，其依赖于班级表的主键，这样字段no就是学生表的外键，通过该字段班级表和学生表建立了关系。
一个班级表：班级表主键
| ID | 名称 | 班主任 |
|---|---|---|
| 101 | 六年级一班 | 马老师 |
| 102 | 六年级二班 | 潘老师 |
每一行对应着一个班级，而一个班级对应着多个学生，所以班级表和学生表的关系就是“一对多”：
班级表 为学生表<班级ID >的外键
| ID | 姓名 | 班级ID | 性别 | 年龄 |
|---|---|---|---|---|
| 1 | 小花 | 101 | M | 9 |
| 2 | 小红 | 102 | F | 8 |
| 3 | 小军 | 102 | M | 8 |
| 4 | 小白 | 101 | F | 9 |
-
在具体设置FK约束时，设置FK约束的字段必须依赖于数据库中已经存在的父表的主键，同时外键可以为空（NULL）。
-
设置表中某字段的FK约束非常简单，可以在MySQL数据库管理系统中通过SQL语句FOREIGN KEY来实现，其语法形式如下：
CREATE TABLE tablename_1(
propName1 propType ,
propName2 propType ,
......
[CONSTRAINT fk_name]FOREIGN KEY(propName1) REFERENCES table2_name(table2_column)
);
- 其中,tablename_1参数是要设置外键的表名，propName1参数是要设置外键的字段，table2_name是父表的名称，table2_column是父表中设置主键约束的字段名。
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> create table student (id int PRIMARY KEY AUTO_INCREMENT, name varchar(64) NOT NULL, class_id int, sex enum('M','F'), FOREIGN KEY(class_id) REFERENCES class(id) ); #创建表student, class_id 为表class id 字段的外键
mysql> insert into student(name, class_id, sex) values('小红', 2, 'M'); #插入记录，主键自增长
mysql> insert into student(name, sex) values('小军', 'M'); #插入记录，允许外键为空
设置表字段的非空约束（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 的定义
设置表字段唯一约束（UNIQUE，UK）
-
当数据库表中某个字段上的内容不允许重复时，可以使用UK约束进行设置。UK约束在创建数据库时为某些字段加上“UNIQUE”约束条件，保证所有记录中该字段上的值不重复。如果在用户插入的记录中该字段上的值与其他记录中该字段上的值重复，那么数据库管理系统会报错。
-
设置表中某字段的UK约束非常简单，可以在MySQL数据库管理系统中通过SQL语句UNIQUE来实现，其语法形式如下：
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class
mysql> desc class ; #查询表class 的定义, describe class 效果等同
mysql> show create table class ; #同样可以实现查询表class 的定义
设置表字段值自动增加（AUTO_INCREMENT）
-
AUTO_INCREMENT是MySQL唯一扩展的完整性约束，当向数据库表中插入新记录时，字段上的值会自动生成唯一的ID。
-
在具体设置AUTO_INCREMENT约束时，一个数据库表中只能有一个字段使用该约束，该字段的数据类型必须是整数类型。
-
由于设置AUTO_INCREMENT约束后的字段会生成唯一的ID，因此该字段也经常会同时设置成主键。
-
注意:mysql8 中,AUTO_INCREMENT 必须设为键(主键，外键，唯一键，均可)！ 否则会报错！
设置表中某字段值的自动增加约束非常简单，可以在MySQL数据库管理系统中通过SQL语句AUTO_INCREMENT来实现，其语法形式如下：
CREATE TABLE tablename(
propName propType AUTO_INCREMENT, ......
);
在上述语句中，tablename参数表示所要设置非空约束的字段名字，propName参数为属性名，propType为属性类型，propName字段要设置自动增加约束。默认情况下，字段propName的值从1开始增加，每增加一条记录，记录中该字段的值就会在前一条记录(或已存在的最大值（包括曾经存在的）)的基础上加1。
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int PRIMARY KEY AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,设置字段id为自增长
mysql> show create table class ; #查询表class 的定义
调整列的完整性约束
主键PK、外键FK和 唯一键UK
- 新增
alter table [table_name] add constraint [constraint_name] [unique key| primary key|foreign key] ([column_name])
例如:
增加主键–给id增加primary key属性且起一个约束名pk_id
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WEaBAdJo-1669191638550)(D:\冲击offer\博客\数据库\assets\wps1-16691907626371.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/aa367b7e932a6c79a5eb11c5a93cb500.jpeg)
给teather增加唯一性属性 并且 起了一个约束名
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xmMDFTWQ-1669191638551)(D:\冲击offer\博客\数据库\assets\wps2-16691907626372.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/afe32549b94d174be8ae8dda9a018a9e.jpeg)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0pFh512X-1669191638551)(D:\冲击offer\博客\数据库\assets\wps3-16691907626373.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/6db83bde4f4fbd22fc167001608674e3.jpeg)
- 删除
- 通过如下命令查询键值的约束名(例如第一个pk_id或者uk_teacher):
show index或keys from 表名;
- 执行如下命令删除:
主键： alter table 表名 drop primary key;
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R4qVhnl9-1669191638552)(D:\冲击offer\博客\数据库\assets\wps4-16691907626374.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/f2ecb78bc9d1b40bf5361c5130d641a2.jpeg)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LgvGLDXY-1669191638552)(D:\冲击offer\博客\数据库\assets\wps5-16691907626375.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/667d117766050ef898b6ba3a8640e18c.jpeg)
外键或唯一键: alter table 表名 drop index 约束名;
注:没有定义约束名的情况下就是列名
- 修改
修改的语法太过繁琐,所以我们可以先删除再新增
修改默认值DEFAULT、自增长和非空NK
使用如下语法重新定义列即可：
alter table 表名 modify 列名 类定义;
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UE0DYPWw-1669191638552)(D:\冲击offer\博客\数据库\assets\wps6-16691907626386.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/acf1347c588bf753db11a8e0e0e16e12.jpeg)
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int PRIMARY KEY AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,设置字段id为自增长
mysql> alter table class modify teacher varchar(64) DEFAULT ‘Martin’ NOT NULL ; #设置默认值和非空
mysql> alter table class modify teacher varchar(64) ; #取消默认值和非空
4.3.2 表索引
4.3.2.1 普通索引
所谓普通索引，就是在创建索引时，不附加任何限制条件（唯一、非空等限制）。该类型的索引可以创建在任何数据类型的字段上。
- [ ]之间的内容是可有可无，（）中的内容是必须要有的
CREATE TABLE tablename(
propname1 type1,
propname2 type2,
……
propnamen type..n,
INDEX | KEY
[indexname] (propnamen [(length)] [ ASC | DESC ] ) );
- 其中，参数index和key是用来指定字段为索引的，两者选择其中之一就可以了，作用是一样的；-- 我选index
- 参数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查询记录，结果将降序排列
因为指定的是降序的所以2在1的前面
已存在的表上创建索引
- 两种方式会一种就行
方法一: 执行create 语句
CREATE INDEX indexname
ON tablename (propname [(length)] [ASC|DESC]);
- 参数INDEX是用来指定字段为索引，此处不能为KEY；
- 参数indexname是新创建的索引的名字；
- 参数tablename是指需要创建索引的表的名称，该表必须是已经存在的，如果不存在，需要先创建；
- 参数propname指定索引对应的字段的名称，该字段必须为前面定义好的字段；
- 参数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)); #创建表class, 并建立为id 字段索引
mysql> insert into class values(1, '一班', 'Martin'); # 插入记录1
mysql> insert into class values(1, '二班', 'Rock'); # 插入记录2
mysql> create index index_id on class(id DESC); #追加升序索引
mysql> show create table class; #查看表定义
mysql> select * from class where id > 0 ; #根据id查询记录，结果将降序排列
方法二: 执行ALTER TABLE 语句
ALTER TABLE tablename ADD INDEX | KEY indexname
(propname [(length)] [ASC|DESC]);
- 在上述语句中，参数tablename是需要创建索引的表；
- 关键字IDNEX或KEY用来指定创建普通索引；
- 参数indexname用来指定所创建的索引名；
- 参数propname用来指定索引所关联的字段的名称；
- 参数length用来指定索引的长度；参
- 数ASC用来指定升序排序；参数DESC用来指定降序排序。
查看索引执行情况
explain 查询语句
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D4hbI1oz-1669270393695)(D:\冲击offer\博客\数据库\assets\wps5-16692113199039.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/d79198474a34e00427b05e26b98f2c72.jpeg)
输出结果：
-
key： 实际使用的索引。如果为NULL，则没有使用索引
-
possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引 将被列出，但不一定被查询实际使用
-
key_len： 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。此值越短越好！
4.3.2.1 唯一索引
所谓唯一索引，就是在创建索引时，限制索引的字段值必须是唯一的。通过该类型的索引可以比普通索引更快速地查询某条记录。
创建表时定义索引
CREATE TABLE tablename(
propname1 type1,
……
propnamen type..n,
UNIQUE INDEX | KEY [indexname] (propnamen [(length)] [ ASC | DESC ] ) );
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Y00MAU8v-1669294124818)(D:\冲击offer\博客\数据库\assets\wps1-16692938199692.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/85f8fcca0f9e0b5c9d649973e7e5d5b9.jpeg)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tAgSxPhW-1669294124819)(D:\冲击offer\博客\数据库\assets\wps2-16692938199691.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/a511cfc1fff3357014904e04d0badb10.jpeg)
注:内部做了优化:索引有时候会用,有时候不用
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pO5N35V5-1669294124820)(D:\冲击offer\博客\数据库\assets\wps3-16692938199693.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/94c95ffcde5363db43e5172c5f1438c2.jpeg)
注意：
- 参数unique index和unique key是用来指定字段为索引的，两者选择其中之一即可；
- 参数indexname是索引名字，可省略；
- 参数propnamen是索引对应的字段的名称，该字段必须为前面定义好的字段且必须定义为 unique 约束；
- 参数length是可选参数，其指索引的长度，必须是字符串类型才可以使用；
- 参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列，如果不指定，则为升序。
已存在的表上创建索引
方法一:
CREATE UNIQUE INDEX indexname
ON tablename (propname [(length)] [ASC|DESC]);
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YVmVa0x5-1669294124820)(D:\冲击offer\博客\数据库\assets\wps4-16692938199694.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/58c95c176ef081a1926885b4072f0a60.jpeg)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-extrD4p4-1669294124820)(D:\冲击offer\博客\数据库\assets\wps5-16692938199705.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/9bb87a5887ce81f21f5bf94fc7f75f55.jpeg)
方法二:
ALTER TABLE tablename ADD UNIQUE INDEX | KEY indexname (propname [(length)] [ASC|DESC]);
4.3.2.3 全文索引
-
全文索引主要对字符串类型建立基于分词的索引，主要是基于CHAR、VARCHAR和TEXT的字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。
-
全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符。
创建表时定义索引
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('我是一个兵');#利用全文检索索引快速查询记录
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KVkrCovM-1669707864449)(D:\冲击offer\博客\数据库\assets\wps1-16697073981731.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/9ccf795011f7be3877eb7e580e14547c.jpeg)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xEaPRJ6j-1669707864450)(D:\冲击offer\博客\数据库\assets\wps2-16697073981742.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/8010b74291f2854ae0c685dc6989019f.jpeg)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RCNIc3oy-1669707864451)(D:\冲击offer\博客\数据库\assets\wps3-16697073981743.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/1253cd4610a3ef7bf8c679a62f5e5030.jpeg)
已存在的表上创建索引
方法一: 执行create 语句
- 在MySQL中创建全文索引除了通过SQL语句FULLTEXT INDEX来实现外，还可以通过SQL语句CREATE FULLTEXT INDEX来实现，其语法形式如下：
CREATE FULLTEXT INDEX indexname
ON tablename( propname1 [ ( length ) ] );
-
在上述语句中，关键字CREATE FULLTEXT INDEX表示用来创建全文索引。
-
如下例表已存在，可通过CREATE语句创建全文索引：
-
搜索的时候,不区分大小写
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class, 并建立为id 字段索引
mysql> create FULLTEXT index index_teacher on class(teacher ); #追加全文索引
mysql> show create table class; #查看表定义
方法二: 执行ALTER TABLE 语句
- 除了上述两种方式来创建全文索引外，在MySQL中创建全文索引还可以通过SQL语句ALTER来实现，其语法形式如下：
ALTER TABLE tablename
ADD FULLTEXT INDEX|KEY indexname(propname [(length)]);
使用场景
-
根据全文索引字段进行全文检索数据：
-
SELECT * FROM 表名WHERE MATCH(
列名,列名) AGAINST(‘关键字,关键字’);
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64),info varchar(1024),FULLTEXT INDEX index_des(info)); #创建表class, 并建立为info 字段为全文索引
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(teacher) AGAINST('我是一个兵');#根据id查询记录
4.3.2.4 MySQL8 中文分词支持
- 配置文件my.ini（Windows 10默认路径: C:\ProgramData\MySQL\MySQL Server 8.0） 中增加如下配置项，同时重启MySQL80 服务:
[mysqld]
ngram_token_size=2 //以两个中文字段为一个搜索单词
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
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 字段为全文索引
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('百姓');#利用全文检索索引快速查询记录
mysql> select * from class where match(comment) AGAINST('唐僧');#利用全文检索索引快速查询记录
4.3.2.5 多列索引
- 多列索引，是指在创建索引时所关联的字段不是一个字段，而是多个字段，虽然可以通过所关联的字段进行查询，但是只有查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用。
创建表时定义索引
CREATE TABLE tablename(
propname1 type1,
……
propnamen type..n,
INDEX | KEY [indexname] (propname1 [(length)] [ ASC | DESC ],
Propname2 [(length)] [ ASC | DESC ],
... ...
Propnamen [(length)] [ 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_mult_columns(id, teacher)); #创建表class, 并建立包含id,teacher字段的多列索引
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查询记录会启用多列索引
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vJwum0Fs-1669721488089)(D:\冲击offer\博客\数据库\assets\wps3-16697213929285.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/d716e6800e458149e2fb22b43e1cfc60.jpeg)
- 单独查询第一个字段也会使用多列索引
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eGZGHvyq-1669721488090)(D:\冲击offer\博客\数据库\assets\wps4-16697214127247.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/11701c7059d88b8deb30dc44bca7008e.jpeg)
4.3.2.6 隐藏索引 (主要用于性能调优)
-
MySQL 8开始支持隐藏索引。隐藏索引提供了更人性化的数据库操作。
-
隐藏索引，顾名思义，让索引暂时不可见，不会被优化器使用。默认情况下索引是可见的。隐藏索引可以用来测试索引的性能。验证索引的必要性时不需要删除索引，可以先将索引隐藏，如果优化器性能无影响就可以真正地删除索引。
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #隐藏索引
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-758T2SXa-1669956034166)(D:\冲击offer\博客\数据库\assets\wps1-16699554616301.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/13452fad16030f2107e59e40b2ed20c6.jpeg)
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #取消隐藏
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SEDE255A-1669956034167)(D:\冲击offer\博客\数据库\assets\wps2-16699554616302.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/7f65c6b80cf211341fe15c8b2455d2f0.jpeg)
4.3.2.7 删除索引
- 所谓删除索引，就是删除表中已经创建的索引。之所以要删除索引，是因为这些索引会降低表的更新速度，影响数据库的性能。
在MySQL中删除索引通过SQL语句DROP INDEX来实现，其语法形式如下：
DROP INDEX indexname ON tablename;
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tJViJemY-1669956034167)(D:\冲击offer\博客\数据库\assets\wps3-16699554616303.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/ea8e65e5e50b3d32f053125a4a72df34.jpeg)
修改索引:先删除，后增加
4.3.2.8索引的设计原则
- 为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。本节将介绍一些索引的设计原则。
- 选择唯一性索引
唯一性索引的值是唯一的，可以更快速地通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个学生的信息，如果使用姓名的话，可能存在同名现象，从而降低查询速度。
- 为经常需要排序、分组和联合操作的字段建立索引
经常需要使用ORDER BY(排序)、GROUP BY(分组)、DISTINCT(去重)和UNION(联合查询)等操作的字段，排序操作会浪费很多时间，如果为其建立索引，可以有效地避免排序操作。
- 为经常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度，为这样的字段建立索引可以提高整个表的查询速度。
- 限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大，修改表时，对索引的重构和更新很麻烦。
- 尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间多。
- 尽量使用前缀来索引
如果索引的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间，如果只检索字段前面的若干字符，这样可以提高检索速度。
- 删除不再使用或者很少使用的索引(模糊查找)
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
4.3.3 表的插入
插入数据记录是常见的数据操作，可以显示向表中增加的新的数据记录。在MySQL中可以通过“INSERT INTO”语句来实现插入数据记录，该SQL语句可以通过如下4种方式使用：插入完整数据记录、插入部分数据记录、插入多条数据记录和插入JSON结构的数据记录（暂且不讲）。
插入完整数据记录
- 在MySQL中插入完整的数据记录可通过SQL语句INSERT来实现，其语法形式如下：
INSERT INTO tablename(field1, field2, field3, ..., fieldn) VALUES(value1, value2, value3, ..., valuen);
可以缩写为：
INSERT INTO tablename VALUES(value1, value2, value3, ..., valuen);
- 参数: tablename表示所要插入完整记录的表名，参数fieldn表示表中的字段名字，参数valuen表示所要插入的数值，并且参数fieldn与参数valuen一一对应。
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class, 并建立包含id,teacher字段的多列索引
mysql> show create table class; #查看表定义
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'); # 插入完整记录
mysql> insert into class values(2, '二班', 'Rock'); # 方式二，完整插入可缩写
插入部分数据记录
语法形式:
INSERT INTO tablename(field1, field2, field3, ..., fieldn) VALUES(value1, value2, value3, ..., valuen);
- 注意: 如果域没有设定默认值，也没有设定为自增长，则插入记录时必须插入值
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> show create table class; #查看表定义
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'); # 插入完整记录
mysql> insert into class (name, teacher)values('二班', 'Rock'); # 插入部分记录
插入多条数据记录
INSERT INTO tablename(field1, field2, field3, ..., fieldn)
VALUES(value11, value12, value13, ..., value1n)，
(value21, value22, value23, ..., value2n)，
... ....
(valuen1, valuen2, valuen3, ..., valuenn);
例如:
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> show create table class; #查看表定义
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'),(2,'二班', 'Rock'),(3, '三班', 'Janny'); # 插入多条记录
4.3.4 更新表中的数据记录
更新数据记录是数据操作中常见的操作，可以更新表中已经存在数据记录中的值。在MySQL中可以通过UPDATE语句来实现更新数据记录，该SQL语句可以通过如下几种方式使用：更新特定数据记录、更新所有数据记录、更新JSON结构的数据记录。
新特定数据记录
- 在MySQL中更新特定数据记录可通过SQL语句UPDATE来实现，其语法形式如下：
UPDATE tablename SET field1 = value1, field2 = value2, field3 = value3, ..., fieldn = valuen) WHERE CONDITION;
- 上述语句中，参数tablename表示所要更新数据记录的表名，参数field表示表中所要更新数值的字段名字，参数valuen表示更新后的数值，参数CONDITION指定更新满足条件的特定数据记录。
例如：校长新找了个如花老师把Rock 老湿给换了!
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'),(2,'二班', 'Rock'),(3, '三班', 'Janny'); # 插入多条记录
mysql> update class set teacher = '如花老师' where teacher = 'Rock'; #通过teacher 字段匹配更新记录 或
mysql> update class set teacher = '如花老师' where id = 2; #通过id 字段匹配更新记录
更新所有数据记录
在MySQL中更新特定数据记录可通过SQL语句UPDATE来实现，其语法形式如下：
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语句。
例如：校长新找了个如花老师就把所有老师都给换了!
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'),(2,'二班', 'Rock'),(3, '三班', 'Janny'); # 插入多条记录
mysql> update class set teacher = '如花老师' ; #把所有记录的teacher列改成如花老师
# 或
mysql> update class set teacher = '如花老师' where 1=1;
4.3.5 删除表中的数据记录
删除数据记录是数据操作中常见的操作，可以删除表中已经存在的数据记录。在MySQL中可以通过DELETE语句来删除数据记录，该SQL语句可以通过以下几种方式使用：删除特定数据记录、删除所有数据记录。
删除特定数据记录
- 在MySQL中删除特定数据记录可通过SQL语句DELETE来实现，其语法形式如下：
DELETE FROM tablename WHERE CONDITION;
- 在上述语句中，参数tablename表示所要删除数据记录的表名，参数CONDITION指定删除满足条件的特定数据记录。
例如：Martin 所带的一班毕业了，从class 表中删除!
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'),(2,'二班', 'Rock'),(3, '三班', 'Janny'); # 插入多条记录
mysql> delete from class where teacher = 'Martin'; #通过teacher字段删除记录 或
mysql> delete from class where id = 1; #通过id 字段删除匹配的记录
删除所有数据记录
- 在MySQL中删除所有数据记录同样可通过SQL语句DELETE来实现，其语法形式如下：
DELETE FROM tablename WHERE CONDITION;
或
DELETE FROM tablename；
- 在上述语句中，为了删除所有的数据记录，参数CONDITION需要满足表tablename中所有数据记录，如id>0; 或者无关键字WHERE语句。
例如：所有老师带的班级都毕业了，从class 表中删除!
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'),(2,'二班', 'Rock'),(3, '三班', 'Janny'); # 插入多条记录
mysql> delete from class ; #直接删除所有记录 或
mysql> delete from class where id > 0; #通过id 字段删除匹配的所有记录
4.3.6 作业：设计游戏用户信息表
回顾下Jack 要实现的用户登陆模块，具体需求如下：
-
游戏玩家访问游戏客户端，通过客户端界面输入用户名和密码
-
在游戏玩家点击”确认”后，客户端连接至数据库服务器对用户名和密码进行确认，
-
如果验证成功，则 玩家可以进入大厅，如果失败，则不允许进入！
请帮Jack 设计好相应的数据库表，并插入少量样例数据!
mysql> create database game_db; #创建游戏数据库game_db
mysql> use game_db; #选择数据库game_db
mysql> create table users (
);
答案
回顾下Jack 要实现的用户登陆模块，具体需求如下：
-
游戏玩家访问游戏客户端，通过客户端界面输入用户名和密码
-
在游戏玩家点击”确认”后，客户端连接至数据库服务器对用户名和密码进行确认，
如果验证成功，则 玩家可以进入大厅，如果失败，则不允许进入！
mysql> create database game_db; #创建游戏数据库game_db
mysql> use game_db; #选择数据库game_db
mysql> create table users (
id int(11) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
username varchar(64) NOT NULL UNIQUE COMMENT '用户名',
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： 用来保存用户的基本信息
更高逼格的设计： 分成两个表，用户信息表和用户验证表
基于以下三方面的原因：
-
面向对象方面考虑 - 用户信息和用户验证是两种”对象”
-
性能方面考虑 - 登陆验证的时候列较少，查询速度快。
-
安全方面考虑 - 防止在查询用户信息时，把密码也直接查询出来，会容易被攻击和进行恶意操作。
mysql> create database game_db; #创建游戏数据库game_db
mysql> use game_db; #选择数据库game_db
mysql> create table users (
id int(11) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
username varchar(64) NOT NULL UNIQUE 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 '身份证号码'
);
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)
);
#插入数据
mysql> insert into users (username, nickname, mobile, age, idno) values('martin', '程咬金', '18684518289', 39, '430623871234567898');
mysql> insert into user_auths values(1, 'martin', '123456qweQWE'); #密码明文，不安全
mysql> insert into user_auths values(1, 'martin', md5('123456qweQWE')); #使用内部函数md5 加密，一亿年马老师都无法破解
mysql> select * from user_auths where username = 'martin' and password = md5('123456qweQWE') #使用内部函数md5加密的数据,插叙的时候也需要加上md5
5. 数据查询
5.1 简单查询
- 在MySQL中可以通过SQL语句来实现基本数据查询，SQL语句可以通过如下多种使用：查询所有字段数据、查询指定字段数据、避免重复数据查询、对结果进行排序和分组等查询。
在MySQL中，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参数表示按降序的顺序进行排序。
5.1.1 查询所有字段数据
- 查询所有字段是指查询表中所有字段的数据，这种方式可以将表中所有字段的数据都查询出来。
- MySQL有两种方式可以查询表中的所有字段。
- 列出表的所有字段
- 通过SQL语句SELECT列出表的所有字段，具体语法形式如下：
SELECT field1,field2,…,fieldn FROM tablename;
其中，filed1~fieldn参数表示需要查询的字段名；tablename参数表示表的名称。
- “ * ”符号表示所有字段
查询所有字段数据，除了使用上面的方式外，还可以通过符号“*”来实现，具体语法形式如下：
SELECT * FROM tablename;
其中，符号“ * ”表示所有字段名；tablename参数表示表的名称。与上一种方式相比，“ * ”符号方式的优势比较明显，即可用该符号代替表中的所有字段，但是这种方式不够灵活，只能按照表中字段的固定顺序显示，不能随便改变字段的顺序。
- 查询指定字段数据
查询指定字段数据，只需修改关键字SELECT后的字段列表为指定字段即可。
SELECT field1,field2,…,fieldn FROM tablename;
- 例如，从班级表中查询班主任字段，SQL语句如下所示。
SELECT teacher FROM class;
演示
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'),(2,'二班', 'Rock'),(3, '三班', 'Janny'),(4,'四班', 'Janny'); # 插入多条记录
mysql> select teacher from class ; #查询class表中的teacher 域
- distinct查询
当在MySQL中执行简单数据查询时，有时会显示出重复数据。为了实现查询不重复数据，MySQL提供了DISTINCT功能，SQL语法如下：
SELECT DISTINCT field1,field2,…,fieldn FROM tablename;
在上述语句中，关键字DISTINCT去除重复的数据。下面将通过一个具体的示例来说明如何实现查询不重复数据。
执行SQL语句SELECT，在数据库school中查询班级表 class 中teacher字段的数据。具体步骤如下：
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'),(2,'二班', 'Rock'),(3, '三班', 'Janny'),(4,'四班', 'Janny'); # 插入多条记录
mysql> select teacher from class ; #查询class表中的teacher 域，包含重复记录
mysql> select distinct teacher from class ; #查询class表中的teacher 域，去重
5.1.2 IN查询
有的时候，当我们需要查询的目标记录限定在某个集合中的时候，在MySQL中可以使用关键字IN来实现，关键字IN可以实现判断字段的数值是否在指定集合中，该关键字的具体语句形式如下：
SELECT field1,field2,…,fieldn from tablename where filedm IN(value1,value2,value3,…,valuen);
- 注：参数fieldn表示名称为tablename的表中的字段名，参数valuen表示集合中的值，通过关键字IN来判断字段fieldm的值是否在集合（value1,value2,value3,…,valuen）中，如果字段fieldm的值在集合中，就满足查询条件，该记录会被查询出来，否则不会被查询出来。
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table class(id int UNIQUE AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,指定id 字段自增长
mysql> insert into class(id, name, teacher) values(1, '一班', 'Martin'),(2,'二班', 'Rock'),(3, '三班', 'Janny'),(4,'四班', 'Janny'); # 插入多条记录
mysql> select * from class where teacher in ('Martin','Rock') ; #查询class表中的teacher 域是martin 和Rock 的记录
使用NOT IN可以反向查询非集合中的数据
SELECT field1,field2,…,fieldn from tablename where filedm NOT IN(value1,value2,value3,…,valuen);
集合查询的注意 : 集合中慎用 NULL
在具体使用关键字IN时，查询的集合中如果存在NULL，则不会影响查询,NULL 存不存在的效果都一样；但如果使用关键字NOT IN，查询的集合中如果存在NULL，则不会查询到任何的结果。
5.1.3 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设置查询范围，以实现查询语文成绩（字段Chinese）在80和90之间的学生，具体SQL如下：
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table grade(id int UNIQUE AUTO_INCREMENT, name varchar(128) NOT NULL, math tinyint unsigned, chinese tinyint unsigned, english tinyint unsigned, create_date date); #创建成绩表 grade
mysql> insert into grade values(1, '甲', 80, 87, 91,'2020-02-03'),(2,'乙', 72, 64, 89,'2020-05-01'),(3, '丙', 54, 69, 87,'2020-04-21'),(4,'丁', 78, 79, 89, '2020-06-04'); # 插入多条记录
mysql> select * from class where chinese between 80 and 90 ; #查询成绩表中语文成绩在80 和 90 之间的学员记录
mysql> select * from grade where create_date between '2020-05-01' and '2020-06-04';
5.1.4 不符合范围的数据记录查询
通过关键字NOT设置反向查询非查询范围的条件，具体SQL语句如下：
SELECT name,chinese FROM grade WHERE Chinese NOT BETWEEN 85 AND 90;
上面语句等同于：
select name,chinese from grade where chinese <85 or chinese >90;
5.1.4 LIKE模糊查询
- 当我们只想用字符串中间的一部分特征查找含有特征字串的信息时，MySQL提供了关键字LIKE来实现模糊查询，需要使用通配符，具体语法形式如下：
SELECT field1,field2,…,fieldn FROM tablename WHERE fieldm LIKE value;
- 在上述语句中，参数tablename表示表名，参数fieldn表示表中的字段名字，通过关键字LIKE来判断字段field的值是否与value字符串匹配，如果相匹配，则满足查询条件，该记录就会被查询出来；否则就不会被查询出来。
在MySQL中，字符串必须加上单引号（‘’）和双引号（″″）。由于关键字LIKE可以实现模糊查询，因此该关键字后面的字符串参数除了可以使用完整的字符串外，还可以包含通配符。LIKE关键字支持的通配符如表5-1所示。
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vC8pBYKz-1670411778542)(D:\冲击offer\博客\数据库\assets\wps1-16704111806391.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/7565fa07b3e02790865c71655b3c0206.jpeg)
基本使用
- 查找某个字段含有‘三’字的记录：
SELECT * FROM class WHERE teacher LIKE ‘%三%’;
注意：将会把teacher字段为“三爷”，“张三”，“张猫三”、“三脚猫”，“唐三藏”等等含“三”的记录全找出来。
- 查询teacher字段中既有“三”又有“猫”的记录，同时“猫”在“三”之后的记录
SELECT * FROM class WHERE teacher LIKE ‘%三%猫%’;
注意： 虽然能搜索出“三脚猫”，但不能搜索出符合条件的“张猫三”。
- 查找teacher字段中既有“三”又有“猫”的记录(三和猫 位置不分前后)，请使用and条件
SELECT * FROM class WHERE teacher LIKE ‘%三%’ AND teacher LIKE ‘%猫%’;
- 只找出“唐三藏”这样teacher为三个字且中间一个字是“三”的;
SELECT * FROM ［user］ WHERE u_name LIKE ‘_三_’;
注意：_表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句：（可以代表一个中文字符）
两个通配符联合使用
- 查询字段teacher中第二个字母为A的数据记录，具体SQL语句如下：
SELECT * FROM class WHERE teacher LIKE '_A%';
取反操作
- 如果想查询第二个字母不是A的全部老师，可以执行逻辑非运算符（NOT），具体SQL语句如下：
SELECT * FROM class WHERE NOT teacher LIKE '_A%';
- 如果想查询第二个字母不是A的全部老师，也可以用以下SQL语句查询：
SELECT * FROM class WHERE teacher NOT LIKE '_A%';
5.1.5 对查询结果排序
在MySQL中，从表中查询出的数据可能是无序的，或者其排列顺序不是用户所期望的顺序，为了使查询结果的顺序满足用户的要求，可以使用关键字ORDER BY对记录进行排序，其语法形式如下：
SELECT field1, field2, field3, …, fieldn FROM tablename ORDER BY fieldm [ASC|DESC]
-
在上述语句中，参数tablename表示所要进行排序的表名，参数fieldn表示表中的字段名字，参数fieldm表示按照该字段进行排序；ASC表示按升序进行排序；DESC表示按降序进行排序。默认的情况下按ASC进行排序。
-
注意：如果存在一条记录字段的值为空值（NULL），那么按升序排序时，含空值的记录将最先显示，可以理解为空值是该字段的最小值；按降序排列时，字段为空值的记录将最后显示。
执行SQL语句SELECT，查询表grade中所有的数据记录，按照语文成绩（字段chinese）升序排序，具体SQL语句如下：
SELECT id,name,chinese from class order by chinese ASC;
示例:
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table grade(id int UNIQUE AUTO_INCREMENT, name varchar(128) NOT NULL, math tinyint unsigned, chinese tinyint unsigned, english tinyint unsigned, create_date date); #创建成绩表 grade
mysql> insert into grade values(1, '甲', 80, 87, 91,'2020-02-03'),(2,'乙', 72, 64, 89,'2020-05-01'),(3, '丙', 54, 69, 87,'2020-04-21'),(4,'丁', 78, 79, 89, '2020-06-04'); # 插入多条记录
mysql> SELECT id,name,chinese FROM class ORDER BY chinese ASC; #按升序的方式查询学员记录
显示分组的名字
5.1.6 简单分组查询
MySQL软件提供了5个统计函数来帮助用户统计数据，可以使用户很方便地对记录进行统计数、计算和、计算平均数、计算最大值和最小值，而不需要查询所有数据。
- 在具体使用统计函数时，都是针对表中所有记录数或指定特定条件（WHERE子句）的数据记录进行统计计算。在现实应用中，经常会先把所有数据记录进行分组，再对这些分组后的数据记录进行统计计算。
MySQL通过SQL语句GROUP BY来实现，分组数据查询语法如下：
SELECT function()[,filed ]FROM tablename WHERE CONDITION GROUP BY field;
-
在上述语句中，参数field表示某字段名，通过该字段对名称为tablename的表的数据记录进行分组。
-
注意：在具体进行分组查询时，分组所依据的字段上的值一定要具有重复值，否则分组没有任何意义。
五个统计函数简介
1. 统计数量
- COUNT(field):该种方式可以实现对指定字段的记录进行统计,在具体统计时将忽略NULL值。
统计class_id为1 和 为2的条数
- COUNT(*)：该种方式可以实现对表中记录进行统计,不管表字段中包含的是NULL值还是非NULL值。
- 统计计算平均值
该函数只有一种使用方式。
- AVG(field)使用方式:该种方式可以实现对指定字段的平均值进行计算,在具体统计时将忽略NULL值。
- 统计计算求和
该函数只有一种使用方式。
- SUM(field)使用方式:该种方式可以实现计算指定字段值之和,在具体统计时将忽略NULL值。
- 统计最大值
该函数只有一种使用方式。
- MAX(field)使用方式:该种方式可以实现计算指定字段值中的最大值,在具体统计时将忽略NULL值。
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-x00It2QJ-1670497958071)(D:\冲击offer\博客\数据库\assets\wps8-167049770167612.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/a04003ee9d272e8316dd3be24623ac8a.jpeg)
- 统计最小值
该函数只有一种使用方式。
- MIN(field)使用方式:该种方式可以实现计算指定字段值中的最小值,在具体统计时将忽略NULL值。
5.1.7 统计分组查询
在MySQL中，只实现简单的分组查询有时候可能没有任何实际意义，因为关键字GROUP BY单独使用时，默认查询出每个分组中随机的一条记录，具有很大的不确定性，一般建议将分组关键字与统计函数一起使用。
- 如果想显示每个分组中的字段，可以通过函数GROUP_CONCAT()来实现。该函数可以实现显示每个分组中的指定字段，函数的具体语法形式如下：
SELECT GROUP_CONCAT(field)
FROM tablename
WHERE CONDITION GROUP BY field;
-
在上述语句中会显示每个数组中的字段值。
-
【示例5-5】使用GROUP_CONCAT()对班级进行统计分组，并显示每组中学生的姓名。
执行SQL语句GROUP_CONCAT()，显示每个分组，具体SQL语句如下：
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> create table grade(id int UNIQUE AUTO_INCREMENT, name varchar(128) NOT NULL, math tinyint unsigned, chinese tinyint unsigned, english tinyint unsigned, class_id int NOT NULL); #创建成绩表 grade
mysql> insert into grade values(1, '甲', 80, 87, 91, 1),(2,'乙', 72, 64, 89,2),(3, '丙', 54, 69, 87,2),(4,'丁', 78, 79, 89, 1); #插入多条记录
mysql> SELECT GROUP_CONCAT(name) name, sum(math) FROM grade GROUP BY class_id; #按class_id 进行分组，统计数学总分并显示每个分组中的姓名
5.1.8 联合查询
1.内连接查询
-
这种只有2张表匹配的行才能显示的连接方式在Mysql 中称之为内连接： INNER JOIN
-
在MySQL中内连接数据查询通过“INNER JOIN…ON”语句来实现，语法形式如下所示。
SELECT field1,field2,…,fieldn FROM tablename1
INNER JOIN tablename2 [INNER JOIN tablenamen] ON CONDITION
其中，参数fieldn表示要查询的字段名，来源于所连接的表tablename1和tablename2，关键字INNER JOIN表示表进行内连接，参数CONDITION表示进行匹配的条件。
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> CREATE TABLE class (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(128) DEFAULT NULL,
`teacher` varchar(64) DEFAULT NULL,
UNIQUE KEY `id` (`id`)
); #创建班级表 class
mysql> insert into class values(101, '萌新一班', 'Martin'),(102, '萌新二班', 'Rock'),(103, '萌新三班', 'Janny'); #创建成绩表 grade
mysql> CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT UNIQUE,
`name` varchar(64) DEFAULT NULL,
`class_id` int DEFAULT NULL,
`sex` enum('F','M') DEFAULT NULL
);
mysql> insert into student values(1,'小花',101,'M'),(2,'小红',102, 'F'),(3,'小军',102,'F'),(4,'小白',101,'F'); #插入多条记录
mysql> select * from class inner join student on class.id = student.class_id; #查询class 表和student 表中班级字段相同的记录并进行内连接
mysql> select * from class as a inner join student as b where a.id = b.class_id; #同上，使用别名查询
- 当表名特别长时，直接使用表名很不方便，或者在实现自连接操作时，直接使用表名无法区别表。为了解决这一类问题，MySQL提供了一种机制来为表取别名，具体语法如下：
SELECT field1, field2, ...,fieldn [AS] otherfieldn
FROM tablename1 [AS] othertablename1, ...,
Tablenamen [AS] othertablenamen ... where othertablename1.fieldx = othertablenamen.fieldx ... ;
其中，参数tablename为表原来的名字，参数othertablename为新表名，之所以要为表设置新的名字，是为了让SQL语句代码更加直观、更加人性化和实现更加复杂的功能。
自连接
- 内连接查询中存在一种特殊的等值连接——自连接。所谓自连接，就是指表与其自身进行连接。
如：查询学生 ”小红 ”所在班级的其他学生,SQL语句如下：
mysql> use school; #选择数据库school
mysql> select t1.id, t1.name, t1.class_id from student t1 inner join student t2 on t1.class_id = t2.class_id and t2.name= '小红'; #查询学生 ”小红 ”所在班级的其他学生
等值连接
- 内连接查询中的等值连接就是在关键字ON后的匹配条件中通过等于关系运算符（=）来实现等值条件。
mysql> select * from class as a inner join student as b where a.id = b.class_id;
不等值连接
- 内连接查询中的不等连接就是在关键字ON后的匹配条件中通过除了等于关系运算符来实现不等条件外，还可以使用关系运算符，包含“>”“>=”“<”“<=”和“!=”等运算符号。
mysql> select * from class as a inner join student as b where a.id != b.class_id;
5.1.9 外连接查询
- 当我们在查询数据时，要求返回所操作表中至少一个表的所有数据记录，通过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左边的表为参考表。左连接的结果包括LEFT OUTER字句中指定的左表的所有行，而不仅仅是连接列所匹配的行，如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表均为空值。
例如：查询所有学生的学号、姓名、班级编号、性别、班级名、班主任信息，具体SQL语句如下：
mysql> use school; #选择数据库school
mysql> select * from student as a left join class as b on a.class_id = b.id; #左连接查询所有学生对应的班级信息
mysql> select * from class as a left join student as b on a.id = b.class_id; #左连接查询所有班级的学员信息
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EMf4qXYu-1670848649209)(D:\冲击offer\博客\数据库\assets\wps1-16708483849671.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/4ec17a133179a5c3a9e4abddb6ca2bfb.jpeg)
(左连接查询一 所有学员对应的班级信息)
如果左边的表在右边的表中没有找到相应的匹配,也会生成一条记录,且全部置空
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-v2QEIIit-1670848649209)(D:\冲击offer\博客\数据库\assets\wps2-16708483849682.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/75945cf54980ba5d3c09357814d3f7cb.jpeg)
(左连接查询二 所有班级对应的学员信息)
右外连接
外连接查询中的右外连接在新关系中执行匹配条件时，以关键字RIGHT JOIN右边的表为参考表，如果右表的某行在左表中没有匹配行，左表将返回空值。
例如：查询所有班级的所有学生信息。具体SQL语句如下：
mysql> use school; #选择数据库school
mysql> select * from student as a right join class as b on a.class_id = b.id; #右连接查询所有班级对应的学员信息
mysql> select * from class as a right join student as b on a.id = b.class_id; #右连接查询所有学员对应的班级信息
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tYaK4u4F-1670848649210)(D:\冲击offer\博客\数据库\assets\wps3-16708484371555.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/28f101be542f530b2002058b761166ab.jpeg)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HFyfZ87I-1670848649210)(D:\冲击offer\博客\数据库\assets\wps4-16708484371576.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/b4cb94268d47d809c4a4ca7686407582.jpeg)
5.1.10 子查询
所谓子查询，是指在一个查询中嵌套了其他的若干查询，即在一个SELECT查询语句的WHERE或FROM子句中包含另一个SELECT查询语句。在查询语句中，外层SELECT查询语句称为主查询，WHERE子句中的SELECT查询语句被称为子查询，也被称为嵌套查询。
- 通过子查询可以实现多表查询，该查询语句中可能包含IN、ANY、ALL和EXISTS等关键字，除此之外还可能包含比较运算符。理论上，子查询可以出现在查询语句的任意位置，但是在实际开发中子查询经常出现在WHERE和FROM子句中。
带比较运算符的子查询
- 子查询可以使用比较运算符。这些比较运算符包括=、!=、>、>=、<、<=和<>等。其中，<>与!=是等价的。比较运算符在子查询中使用得非常广泛，如查询分数、年龄、价格和收入等。
例如：查询student 表中“小花”所在班级班主任的名字。SQL语句如下：
mysql> use school; #选择数据库school
mysql> select teacher from class where id = (select class_id from student where name='小花'); #查询“小花”所在班级班主任的姓名
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qDoYsCzG-1670914425042)(D:\冲击offer\博客\数据库\assets\wps1-16709140951631.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/0dcdafc960dbc727f3fe348268a07649.jpeg)
注意：使用比较运算符时，select 子句获得的记录数不能大于1条!!!
带关键字IN的子查询
-
一个查询语句的条件可能落在另一个SELECT语句的查询结果中，这时可以使用IN关键字，SQL示例如下：
-
NOT IN的用法与IN相同。
例如：查询student 表中“小花”所在班级班主任的名字。SQL语句如下：
注意：使用比较运算符时，select 子句获得的记录数可以大于1条!!!,
mysql> use school; #选择数据库school
mysql> select teacher from class where id in (select class_id from student where name='小花'); #查询student 表中“小花”所在班级班主任的名字
mysql> select teacher from class where id in (select class_id from student where name like '小%'); #查询姓名以“小”字开头的学生所在班级班主任的姓名
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bKAWcDUX-1670914425043)(D:\冲击offer\博客\数据库\assets\wps2-16709142101443.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/03f1252b26df9b2726140293bbdc11e4.jpeg)
带关键字EXISTS的子查询
关键字EXISTS表示存在，后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行；如果至少返回一行，那么EXISTS的结果为true，此时外层语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。
例如：如果102班存在学生记录，就查询102班的班级信息的记录。SQL示例语句如下：
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-r2wRX0nT-1670914425043)(D:\冲击offer\博客\数据库\assets\wps3-16709142468305.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/e4ca716b23aa4b33a7bb0c00d1868693.jpeg)
带关键字ANY的子查询
关键字ANY表示满足其中任一条件。使用关键ANY时，只要满足内层查询语句返回的结果中的任何一个就可以通过该条件来执行外层查询语句。例如，需要查询哪些学生可以获取奖学金，那么首先要有一张奖学金表，从表中查询出各种奖学金要求的最低分，只要一个同学的乘积大于等于不同奖学金最低分的任何一个，这个同学就可以获得奖学金。关键字ANY通常和比较运算符一起使用。例如，“>ANY”表示大于任何一个值，“=ANY”表示等于任何一个值。
例如：查询数据库school的表student中哪些学生可以获得奖学金。学生的成绩达到其中任何一项奖学金规定的分数即可，SQL语句示例如下：
mysql> use school; #选择数据库school
mysql> create table scholarship (score int, level varchar(64));
mysql> insert into scholarship values(240, '二等奖'),(257,'一等奖');
mysql> select st.id, st.name, st.math+st.chinese+st.english total from grade st where (math+chinese+english) >= ANY (select score from scholarship); #查询能获得奖学金的学院记录
前提已经有了一个grade表
带关键字ALL的子查询
关键字ALL表示满足所有条件。使用关键字ALL时，只有满足内层查询语句返回的所有结果才可以执行外层查询语句。例如，需要查询哪些同学能够获得一等奖学金，首先要从奖学金表中查询出各种奖学金要求的最低分。因为一等奖学金要求的分数最高，只有当成绩高于所有奖学金最低分时，这个同学才可能获得一等奖学金。关键字ALL也经常与比较运算符一起使用。例如，“>ALL”表示大于所有值，“<ALL”表示小于所有值。
例如：查询数据库school的表student中哪些学生可以获得一等奖学金，即学生的总成绩要达到一等奖学金规定的分数，而一等奖学金是最高奖学金。SQL语句示例如下：
mysql> use school; #选择数据库school
mysql> select st.id, st.name, st.math+st.chinese+st.english total from grade st where (math+chinese+english) >= ALL (select score from scholarship); #查询能获得一等奖学金的同学记录
mysql> select st.id, st.name, st.math+st.chinese+st.english total from grade st where (math+chinese+english) < ALL (select score from scholarship); #查询不能获得奖学金的同学记录
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JLvYeVZZ-1670914425044)(D:\冲击offer\博客\数据库\assets\wps7-167091436821713.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/1c767ee7fd34c5832ee7d6be68d33389.jpeg)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KUTUk9LW-1670914425044)(D:\冲击offer\博客\数据库\assets\wps8-167091436821714.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/c8f31cff390c0fb5296d4bbafcd36b09.jpeg)
6. 视图操作
小学的时候，每年都会举办一次抽考活动，意思是从每一个班级里面筛选出几个优秀的同学去参加考试。这时候很多班级筛选出来的这些同学就可以临时组成一个班级，如果我们把每一个班级都当做是一张真实的表，这个临时的班级在数据库里就可以当做一个视图，也就是说，这个临时的班级其实不是真实存在的，当考试过后，这些学生还是各回各家各找各妈。。。。
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YAQHRmPm-1670995006704)(D:\冲击offer\博客\数据库\assets\wps1-16709938936301.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/a92232a71fab228713bf2bc30356f190.png)
视图是从一个或多个表中导出来的表，是一种虚拟存在的表。视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样用户可以不看整个数据库表中的数据，而只关心对自己有用的数据。视图可以使用户的操作更方便，而且可以保障数据库系统的安全性。
为什么要使用视图
-
通过前面章节的知识可以发现，数据库中关于数据的查询有时非常复杂，例如表连接、子查询等，这种查询会让程序员感到非常痛苦，因为它的逻辑太复杂、编写语句比较多，当这种查询需要重复使用时，很难每次都编写正确，从而降低了数据库的实用性。
-
在具体操作表之前，有时候要求只能操作部分字段，而不是全部字段。例如，在学校里，学生的智商测试结果一般都是保密的，如果因为一时疏忽向查询中多写了关于“智商”的字段，则会让学生的智商显示给所有能够查看该查询结果的人，这时就需要限制使用者操作的字段。
-
为了提高复杂的SQL语句的复用性和表的操作的安全性，MySQL数据库管理系统提供了视图特性。所谓视图，本质上是一种虚拟表，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储数据值的形式存在，行和列数据来自定义视图的查询所引用的基本表，并且在具体引用视图时动态生成。
-
视图使程序员只关心感兴趣的某些特定数据和他们所负责的特定任务。这样程序员只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高数据库中数据的安全性。
创建视图
虽然视图可以被看成是一种虚拟表，但是其物理上是不存在的，即MySQL并没有专门的位置为视图存储数据。根据视图的概念可以发现其数据来源于查询语句，因此创建视图的基本语法为：
CREATE[OR REPLACE] VIEW viewname[columnlist]
AS SELECT statement
其中，CREATE表示创建新的视图；REPLACE表示替换已经创建的视图；viewname为视图的名称；columnlist为属性列；SELECT statement表示SELECT语句；
注意! 创建视图需要登陆用户有相应的权限，查看权限方法：
mysql> use school; #选择数据库school
mysql> select user, Select_priv, Create_view_priv FROM mysql.user;#查询数据库用户创建和选择视图权限
在单表上创建视图
mysql> use school; #选择数据库school
mysql> alter table student add privacy varchar(64);# 增加私隐列
mysql> ;#查询数据库用户创建和选择视图权限
mysql> CREATE VIEW view_student AS select id, class_id, name from student ;#为学生表创建视图
mysql> desc view_student;#查看视图
mysql> select * from view_student; #根据视图进行查询
当虚表数据被改变的时候,实表也会被改变
在多表上创建视图
CREATE[OR REPLACE] VIEW viewname[columnlist]
AS SELECT statement
其中，CREATE表示创建新的视图；REPLACE表示替换已经创建的视图；viewname为视图的名称；columnlist为属性列；SELECT statement表示SELECT语句；与单表上创建视图不同的是，SELECT子句是涉及到多表的联合查询语句。
mysql> use school; #选择数据库school
mysql> alter table student add privacy varchar(64);# 增加私隐列
mysql> ;#查询数据库用户创建和选择视图权限
mysql> CREATE VIEW view_student_class AS select student.id, student.name, class.name class_name, class.teacher from class inner join student on class.id = student.class_id;#为学生表创建视图
mysql> desc view_student_class;#查看视图
mysql> select * from view_student_class; #根据视图进行查询
查看视图
- 创建完视图后，像表一样，我们经常需要查看视图信息。在MySQL中，有许多可以实现查看视图的语句，如DESCRIBE、SHOW TABLES、SHOW CREATE VIEW。如果要使用这些语句，首先要确保拥有SHOW VIEW的权限。本节将详细讲解查看视图的方法。
使用DESCRIBE | DESC语句查看视图基本信息
- 前面我们已经详细讲解过使用DESCRIBE语句来查看表的基本定义。因为视图也是一张表，只是这张表比较特殊，是一张虚拟的表，所以同样可以使用DESCRIBE语句来查看视图的基本定义。DESCRIBE语句查看视图的语法如下：
DESCRIBE | DESC viewname;
在上述语句中，参数viewname表示所要查看设计信息的视图名称。
使用SHOW TABLES语句查看视图基本信息
从MySQL 5.1版本开始，执行SHOW TABLES语句时不仅会显示表的名字，同时也会显示视图的名字。
下面演示通过SHOW TABLES语句查看数据库school中的视图和表的功能，具体SQL语句如下，执行结果如下图所示。
SHOW TABLES;
使用 show create view/table 语句查看视图创建信息
SHOW CREATE TABLE或VIEW viewname;
更新视图数据
更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图实质是一个虚拟表，其中没有数据，通过视图更新时都是转换到基本表更新。更新视图时，只能更新权限范围内的数据，超出范围就不能更新了。
mysql> use school; #选择数据库school
mysql> alter table student add privacy varchar(64);# 增加私隐列
mysql> ;#查询数据库用户创建和选择视图权限
mysql> CREATE VIEW view_student AS select id, class_id, name from student ;#为学生表创建视图
mysql> desc view_student;#查看视图
mysql> select * from view_student; #根据视图进行查询
mysql> update view_student set name='小花花' where name='小花'; #通过视图更新小花为小花花
不能更新的情况：
-
视图中包含SUM()、COUNT()、MAX()和MIN()等函数
-
视图中包含UNION、UNION ALL、DISTINCT、GROUP BY和HAVING等关键字
-
视图对应的表存在没有默认值的列，而且该列没有包含在视图里
-
包含子查询的视图
-
其他特殊情况
修改视图
修改视图是指修改数据库中存在的视图，当基本表的某些字段发生变化的时候，可以通过修改视图来保持与基本表的一致性。ALTER语句来修改视图。
-
当原表的列名改变后,虚拟表就不可以使用了
-
当把列名改回来后,虚拟表就可以使用了
-
若原表改变虚拟表也跟着改变那么也可以
使用ALTER语句修改视图
ALTER VIEW viewname[columnlist]
AS SELECT statement
这个语法中的所有关键字和参数除了alter 外，其他都和创建视图是一样的，因此不再赘述。
【示例9.3】对于示例9.2中创建的视图view_student_class，使用一段时间后需要将表示编号的字段id加进去。步骤如下：
mysql> use school; #选择数据库school
mysql> alter table student add privacy varchar(64);# 增加私隐列
mysql> ;#查询数据库用户创建和选择视图权限
mysql> ALTER VIEW view_student_class AS select student.id, student.name, class.name, class.id as class_id, class.teacher from class inner join student on class.id = student.class_id;#为学生班级表视图增加 class_id 字段
mysql> desc view_student_class;#查看视图
mysql> select * from view_student_class; #根据视图进行查询
改变视图的列名
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WKOrKStO-1670995006705)(D:\冲击offer\博客\数据库\assets\wps12-167099491564723.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/f85155aac005a6faae9203e08fac77b1.png)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HBVGKXmj-1670995006706)(D:\冲击offer\博客\数据库\assets\wps13.jpg)]](https://i-blog.csdnimg.cn/blog_migrate/2f5c86d1284b41f81cba3d5e7f96a132.png)
删除视图
-
删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。
-
在MySQL中，可使用DROP VIEW语句来删除视图，但是用户必须拥有DROP权限。删除视图的语法如下：
DROP VIEW viewname [,viewnamen]；# 删除多个视图用逗号分割
在上述语句中，参数viewname表示所要删除视图的名称，可同时指定删除多个视图。
mysql> use school; #选择数据库school
mysql> ;#查询数据库用户创建和选择视图权限
mysql> CREATE VIEW view_student_class AS select student.id, student.name, class.name, class.id as class_id, class.teacher from class inner join student on class.id = student.class_id;#为学生表创建视图
mysql> drop view view_student_class;#删除视图
7 触发器
触发器的概念精讲
在实际开发中往往会碰到这样的情况：
- 当我们对一个表进行数据操作时，需要同步对其它的表执行相应的操作，正常情况下，如果我们使用sql语句进行更新，将需要执行多条操作语句！
- 比如，在某些棋牌游戏中，当玩家充值金币后，玩家表数据库中金币增加的同时，玩家所属的代理会得到相应的提成并计入代理的收益中，即代理数据库表提成记录字段也要同步更新。
而以上的场景，我们可以轻松使用触发器来实现！
- 触发器（TRIGGER）是由事件来触发某个操作。这些事件包括INSERT语句、UPDATE语句和DELETE语句。当数据库系统执行这些事件时，就会激活触发器执行相应的操作。MySQL从5.0.2版本开始支持触发器。
- 通过本章的学习，我们将了解触发器的含义和作用、如何创建触发器、查看触发器和删除触发器的方法。同时，可以了解各种事件的触发器的执行情况。
创建触发器
在MySQL中创建触发器通过SQL语句CREATE TRIGGER来实现，其语法形式如下：
CREATE trigger trigger_name BEFORE|AFTER trigger_EVENT
ON TABLE_NAME FOR EACH ROW trigger_STMT
在上述语句中，参数trigger_name表示要创建的触发器名；
- 参数EFORE和AFTER指定了触发器执行的时间，前者在触发器事件之前执行触发器语句，后者在触发器事件之后执行触发器语句；
- 参数trigger_EVENT表示触发事件，即触发器执行条件，包含DELETE、INSERT和UPDATE语句；参数TABLE_NAME表示触发事件的操作表名；参数FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器；
- 参数trigger_STMT表示激活触发器后被执行的语句。执行语句中如果要引用更新记录中的字段，对于INSERT语句,只有NEW是合法的，表示当前已插入的记录；对于DELETE语句，只有OLD才合法，表示当前删除的记录；而UPDATE语句可以和NEW（更新后）以及OLD（更新前）同时使用。
- 注意：不能创建具有相同名字的触发器。另外，对于具有相同触发程序动作时间和事件的给定表，不能有两个触发器。因此，对于有经验的用户，在创建触发器之前，需要查看MySQL中是否已经存在该标识符的触发器和触发器的相关事件。
【示例10-1】执行SQL语句CREATE TRIGGER，在数据库school中存在两个表对象：学员表student和班级表 class，创建触发器实现向学员表中插入记录时，就会在插入之后更新班级表中的人数，当我们删除某条学员的记录时，就会在删除后更新班级表中的人数，具体步骤如下：
mysql> use school; #选择数据库school
mysql> CREATE TABLE class (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(128) DEFAULT NULL,
`teacher` varchar(64) DEFAULT NULL,
`count` int DEFAULT 0,
UNIQUE KEY `id` (`id`)
); #创建班级表 class
mysql> insert into class values(101, '萌新一班', 'Martin', 0),(102, '萌新二班', 'Rock', 0),(103, '萌新三班', 'Janny', 0); #创建成绩表 grade
mysql> CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT UNIQUE,
`name` varchar(64) DEFAULT NULL,
`class_id` int DEFAULT NULL,
`sex` enum('F','M') DEFAULT NULL
);
mysql> 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
mysql> insert into student values(1,'小花',101,'M'),(2,'小红',102, 'F'),(3,'小军',102,'F'),(4,'小白',101,'F'); #插入多条记录
mysql> select count from class ; #查询class 表人数
mysql> 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 $$可以将结束符号设置成两个美元符号。
mysql> use school; #选择数据库school
mysql> create table grade(id int UNIQUE AUTO_INCREMENT, math tinyint unsigned, chinese tinyint unsigned, english tinyint unsigned); #创建成绩表 grade
mysql> insert into grade values(1, 80, 87, 91),(2, 72, 64, 89),(3, 54, 69, 87),(4, 78, 79, 89); #插入多条记录
mysql> DELIMITER $$
mysql> 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;
$$
DELIMITER ;
两个表中的记录
删一个学生后两个表都有所改变
查看触发器
SHOW TRIGGERS语句查看触发器
- 那么如何查看MySQL软件中已经存在的触发器呢？在MySQL软件中查看已经存在的触发器，通过SQL语句SHOW TRIGGERS来实现，其语法形式如下，执行上面的SQL语句，执行结果如图9-10所示。
SHOW TRIGGERS ;
- 通过执行结果可以发现，执行完“SHOW TRIGGERS”语句后会显示一个列表，在该列表中会显示出所有触发器的信息。其中，参数Trigger表示触发器的名称；参数Event表示触发器的激发事件；参数Table表示触发器对象触发事件所操作的表；参数Statement表示触发器激活时所执行的语句；参数Timing表示触发器所执行的时间。
查看系统表triggers实现查看触发器 - 在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中的触发器
触发器的删除
- 在MySQL软件中，可以通过DROP TRIGGER语句或通过工具来删除触发器。
- 在MySQL中，删除触发器可以通过SQL语句DROP TRIGGER来实现，其语法形式如下：
DROP TRIGGER trigger_name;
在上述语句中，参数trigger_name表示所要删除的触发器名称。
第11节存储过程和函数
存储过程和函数是在数据库中定义的一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户器端和服务端的数据传输。
通过本章的学习，我们将了解存储过程的定义、作用，还可以了解创建、使用、查看、修改及删除存储过程及函数的方法。
8 创建存储过程
创建存储过程和函数是指将经常使用的一组SQL语句组合在一起，并将这些SQL语句当作一个整体存储在MySQL服务器中。存储程序可以分为存储过程和函数。在MySQL中创建存储过程使用的语句CREATE PROCEDURE。其语法形式如下：
CREATE PROCEDURE procedure_name([proc_param[,…]])
routine_body
- 在上述语句中，参数procedure_name表示所要创建的存储过程名字，参数proc_param表示存储过程的参数，参数routine_body表示存储过程的SQL语句代码，可以用BEGIN…END来标志SQL语句的开始和结束。
提示: 在具体创建存储过程时，存储过程名不能与已经存在的存储过程名重名，实战中推荐存储过程名命名为procedure_xxx或者proc_xxx。
proc_param中每个参数的语法形式如下：
[IN|OUT|INOUT] param_name type
在上述语句中，每个参数由三部分组成，分别为输入/输出类型、参数名和参数类型。其中，输入/输出类型有三种类型，分别为IN（表示输入类型）、OUT（表示输出类型）、INOUT（表示输入/输出类型）。param_name表示参数名；type表示参数类型，可以是MySQL软件所支持的任意一个数据类型。
mysql> use school; #选择数据库school
mysql> DELIMITER $$
mysql> create PROCEDURE proc_delete_student (IN sid int )
BEGIN
declare cid int ; #定义变量cid
Select class_id into cid from student where id = sid; #通过查询语句设置变量
delete from grade where id = sid; #删除成绩表中的记录
delete from student where id = sid; #删除学生表中的记录
update class set count=count-1 where id = cid; #更新班级表中的记录
END;
$$
DELIMITER ;
mysql> call proc_delete_student(3); #调用存储过程
初始表
执行操作后
在存储过程中使用变量
- 在存储过程和函数中，可以定义和使用变量。用户可以使用关键字DECLARE来定义变量，然后为变量赋值。这些变量的作用范围是在BEGIN…END程序段中。
- 定义变量
在MySQL中，可以使用DECLARE关键字来定义变量。定义变量的基本语法如下：
DECLARE var_name[,…] type [DEFAULT value]
- 其中，关键字DECLARE是用来声明变量的；参数var_name是变量的名称，可以同时定义多个变量；参数type用来指定变量的类型；DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。
- 定义变量cid，数据类型为INT型，默认值为10，代码如下：
DECLARE cid INT DEFAULT 10;
- 为变量赋值
在MySQL中可以使用关键字SET来为变量赋值，SET语句的基本语法如下：
SET var_name=expr[,var_name=expr]…
- 其中，关键字SET用来为变量赋值；参数var_name是变量的名称；参数expr是赋值表达式。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。
例如，将变量tmp_id赋值为88，代码如下：
SET tmp_id = 88;
在MySQL中，还可以使用SELECT…INTO语句为变量赋值。其基本语法如下：
SELECT col_name[,…] INTO var_name[,…]
FROM table_name WHERE condition
其中，参数col_name表示查询的字段名称；参数var_name是变量的名称；参数table_name指表的名称；参数condition指查询条件。
【示例11-2】从表employee中查询id为3的记录，将该记录的id值赋给变量tmp_id，代码如下：
SELECT id INTO tmp_id
FROM grade WEHRE id=sid;
mysql> use school; #选择数据库school
mysql> drop PROCEDURE if exists query_student_class_info;
mysql> DELIMITER $$
mysql> 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;
$$
DELIMITER ;
mysql> call query_student_class_info(4, @name, @count); #调用存储过程
mysql> select @name, @count;
光标的使用
查询语句可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记录。有些书上将光标称为游标。光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前，并且声明在变量和条件之后。
- 声明光标
在MySQL中，可以使用DECLARE关键字来声明光标，其基本语法如下：
DECLARE cursor_name CURSOR
FOR select_statement;
- 其中，参数cursor_name表示光标的名称；参数select_statement表示SELECT语句的内容。
【示例11-2】下面声明一个名为cur_student的光标，代码如下：
mysql> use school; #选择数据库school
mysql> DELIMITER $$
mysql> 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;
$$
DELIMITER ;
在上面的示例中，光标的名称为cur_student；SELECT语句部分是从表student中查询出字段name和class_id的值。
- 打开光标
- 在MySQL中，使用关键字OPEN来打开光标，其基本语法如下：
OPEN cursor_name;
- 其中，参数cursor_name表示光标的名称。
- 下面代码打开一个名为cur_student的光标，代码如下：
OPEN cur_student;
- 使用光标
在MySQL中，使用关键字FETCH来使用光标，其基本语法如下：
FETCH cursor_name
INTO var_name[,var_name…];
其中，参数cursor_name表示光标的名称；参数var_name表示将光标中的SELECT语句查询出来的信息存入该参数中。var_name必须在声明光标之前就定义好。
【示例11-3】下面声明一个名为cur_student的光标，代码如下：
mysql> use school; #选择数据库school
mysql> DELIMITER $$
mysql> 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> $$
mysql> DELIMITER ;
流程控制的使用
在存储过程和函数中，可以使用流程控制来控制语句的执行。在MySQL中，可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。
1.IF语句
- IF语句用来进行条件判断。根据条件执行不同的语句。其语法的基本形式如下：
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
- 参数search_condition表示条件判断语句；参数statement_list表示不同条件的执行语句。
例1 下面是一个IF语句的示例，代码如下：
IF age>20 THEN SET @count1=@count1+1;
ELSEIF age=20 THEN @count2=@count2+1;
ELSE @count3=@count3+1;
END IF;
该示例根据age与20的大小关系来执行不同的SET语句。如果age值大于20，将count1的值加1；如果age值等于20，就将count2的值加1；其他情况将count3的值加1。IF语句都需要使用END IF来结束。
例2
mysql> use school; #选择数据库school
mysql> DELIMITER $$
mysql> 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> $$
mysql> DELIMITER ;
2.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值的执行语句。
【示例11-5】下面是一个CASE语句的示例。代码如下：
CASE level
WHEN 20 THEN SET attack = attack + 5;
WHEN 30 THEN SET attack = attack + 10;
WHEN 40 THEN SET attack = attack + 15;
ELSE SET attack = attack + 1;
END CASE
当级别level值为20时，attack值加5；当级别level值为30时，attack值加10；当级别level值为40时，attack值加15；否则，attack + 1。CASE语句使用END CASE结束。
LOOP语句 & LEAVE语句
LOOP语句
LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。LOOP语句本身没有停止循环，只有遇到LEAVE语句等才能停止循环。LOOP语句的语句形式如下：
[begin_label:] LOOP
statement_list
END LOOP [end_label]
- 其中，参数begin_label和参数end_label分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；参数statement_list表示需要循坏执行的语句。
【示例11-6】下面是一个LOOP语句的示例，代码如下：
add_num:LOOP
SET @count = @count + 1;
END LOOP add_num;
- 该示例循环执行count加1的操作。因为没有跳出循环的语句，这个循环成了一个死循环。LOOP循环都以END LOOP结束。
LEAVE语句
- LEAVE语句主要用于跳出循环控制，其语法形式如下：
LEAVE label
其中，参数label表示循环的标志。
—【示例11-7】下面是一个LEAVE语句的示例。代码如下：
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语句跳出循环。
例如
ITERATE语句
ITERATE语句也是用来跳出循环的语句，但是ITERATE语句是跳出本次循环，然后直接进入下一次循环，ITERATE语句的语法形式如下：
ITERATE label
其中，参数label表示循环的标志。
【示例11-8】下面是一个ITERATE语句的示例。代码如下：
add_num1:LOOP
Set @count = @count +1
IF @count=100 THEN
LEAVE add_num1
ELSE IF MOD(@count, 3) = 0 then
ITERATE add_num1;
Select * from student;
END LOOP add_num1;
- 该示例循环执行count加1的操作，count的值为100时结束循环。如果count的值能够整除3，就跳出本次循环，不再执行下面的SELECT语句。
注意： LEAVE语句和ITERATE语句都用来跳出循环语句，但是两者的功能是不一样的。LEAVE语句是跳出整个循环，然后执行循环后面的程序，和C++ break 相似。ITERATE语句是跳出本次循环，然后进入下一次循环,和C++ continue 相似。使用这两个语句时一定要区分清楚。
REPEAT语句
- REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT语句的基本语法形式如下：
[begin_label:] REPEAT
statement_list;
UNTIL search_condition
END REPEAT [end_label]
- 其中，参数statement_list表示循环的执行语句；参数search_condition表示结束循环的条件，满足该条件时循环结束。
【示例11-9】下面是一个REPEAT语句的示例。代码如下：
REPEAT
SET @count=@count+1;
UNTIL @count=100
END REPEAT;
- 该示例循环执行count加1的操作，count值为100时结束循环。REPEAT循环都用END REPEAT结束。
WHILE语句
- WHILE语句也是有条件控制的循环语句，但WHILE语句和REPEAT语句是不一样的。WHILE语句是当满足条件时执行循环内的语句。WHILE语句的基本语法形式如下：
[begin_label:] WHILE search_condition DO
Statement_list
END WHILE [end_label]
其中，参数statement_condition表示循环执行的条件，满足该条件时循环执行；参数statement_list表示循环的执行语句。
—【示例11-10】下面是一个WHILE语句的示例。代码如下：
WHILE @count<100 DO
SET @count = @count + 1;
END WHILE;
流程控制综合运用
【示例11-11】循环访问光标操作，访问光标中的所有记录,代码如下：
mysql> use school; #选择数据库school
mysql> DELIMITER $$
mysql> create 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;
mysql> $$
mysql> DELIMITER ;
【示例11-12】在学生表中插入一条记录，并返回记录的自增长id
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 STATUS语句查看存储过程
在MySQL中，可以通过SHOW STATUS语句。其基本语法形式如下：
SHOW PROCEDURE STATUS [ like ‘pattern’ ] ;
- 其中，参数PROCEDURE表示查询存储过程；参数LIKE 'pattern’用来匹配存储过程的名称。
图11-13的执行结果显示了存储过程的创建时间、修改时间和字符集等信息。
使用SHOW CREATE语句查看存储过程的定义
- 在MySQL中，可以通过SHOW CREATE语句查看存储过程的状态，语法形式如下：
SHOW CREATE PROCEDURE proc_name
其中，参数PROCEDURE表示查询存储过程；参数proc_name表示存储过程的名称。
—【示例11-14】查询名为proc_delete_student的存储过程的状态，代码如下，执行结果如下图所示。
SHOW CREATE PROCEDURE proc_delete_student \G
- 从information_schema.Routine表中查看存储过程的信息
存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下：
SELECT * FROM information_schema.Routines
Where ROUTINE_NAME = ‘proc_name’;
- 其中，字段ROUTINE_NAME是Routines 存储存储过程和函数的列名称；参数proc_name表示存储过程或函数的名称。
【示例11-15】下面从Routines表中查询名为proc_delete_student的存储过程信息，具体SQL代码如下，执行结果如下图所示。
select routine_definition from information_schema.Routines
where routine_name='proc_delete_student';
存储过程的删除
- 在MySQL中删除存储过程通过SQL语句DROP完成：
DROP PROCEDURE proc_name;
- 在上述语句中，关键字DROP PROCEDURE用来表示实现删除存储过程，参数proc_name表示所要删除的存储过程名称。
【示例11-16】执行SQL语句DROP PROCEDURE，删除存储过程对象proc_delete_student，具体步骤如下：
DROP PROCEDURE proc_delete_student;
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
