个人推荐:
📢📢📢 前些天发现了一个蛮有意思的人工智能学习网站,8个字形容一下
"通俗易懂，风趣幽默"，感觉非常有意思,忍不住分享一下给大家。
点击跳转到教程。
前言:
📣📣📣 花了几天时间整理了一下MySQL基础篇的知识,全文4万字左右,涉及MySQL基础篇所有知识点,整理图书<<MySQL数据库入门>>,<<尚硅谷MySQL数据库基础篇笔记>>,<<黑马程序员MySQL 基础语法>>。
1.Mysql服务和登入
(1) 服务的关闭和启动
在Windows下启动Mysql本质就是在启动服务。
| 操作 | 说明 |
|---|---|
| net stop 服务名称 | 停止服务 |
| net start 服务名称 | 启动服务 |
- 查看Windows当前所有服务: services.msc
- 关闭Mysql服务
使用 net stop MySQL56 命令
执行后结果如下:
- 启动Mysql服务
使用 net start MySQL56 启动Mysql服务
执行后结果如下:
(2) 登入Mysql(命名行模式下)
登入方式分为本地登入和远程登入,其中远程登入需要在数据库中开启远程访问权限,如何开启权限请查看权限章节的内容。
语法格式:
mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"下面详细介绍命令中的参数：
| 参数 | 介绍 |
|---|---|
| -h | 后面接主机名或者主机IP，hostname为主机，hostIP为主机IP。 |
| -P | 后面接MySQL服务的端口，通过该参数连接到指定的端口。MySQL服务的默认端口是3306，不使用该参数时自动连接到3306端口，port为连接的端口号。 |
| -u | 后面接用户名，username为用户名。 |
| -p | 会提示输入密码。 |
| DatabaseName | 指明登录到哪一个数据库中。如果没有该参数，就会直接登录到MySQL数据库中，然后可以使用USE命令来选择数据库。 |
| -e | 后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL服务器 |
举例:
mysql -uroot -p -hlocalhost -P3306 mysql -e "select host,user from user"运行效果:
练习:
本地登入:
- 密码可见:mysql -u 账号 -p 密码
- 密码不可见方式:mysql -u 账号 -p
远程登入:
- mysql -h Ip地址 -p 端口号 -u 账号 -p 密码
2. Mysql语法规则
- 每条语句以分号结尾
";"，如果在 SQLyog 中不是必须加的。 - SQL 中不区分大小写，关键字中认为大写和小写是一样的
- 3 种注释：
3.SQL语句五种分类
- DQL:数据库查询语言(SELECT...)
- DML:数据库操作语言(insert,delete,update...)
- DDL:数据库定义语言(create,drop,alter...)
- TCL:事务控制语言(commit,rollback...)
- DCL:数据控制语言(grant,revoke...)
4. 数据库DDL的常用命令(操作数据库)
(1) 数据库的查询,选择和退出
| 操作 | SQL语句 | 描述 |
|---|---|---|
| 数据库的查询 | show databses; | 查看当前系统下的所有数据库 |
| 数据库的查询 | show create database; | 查看某个数据库的定义信息 |
| 数据库的查询 | select version(); | 查看数据库的版本号 |
| 数据库的查询 | select Database(); | 查看正在使用的数据库 |
| 数据库的选择 | use 数据库名; | 选择指定数据库 |
| 数据库的退出 | exit或quit; | 退出数据库 |
(2) 数据库的增,删,改
下表中提到的数据库字符集如gbk,utf8,校对规则如utf8_chinese_ci等,至于其他更多字符集编码你可使用SHOW CHARACTER SET;进行查询,更多校对规则你可以使用SHOW COLLATION;进行查询,更详细的操作请查询官方文档 https://dev.mysql.com/doc/refman/5.7/en/charset-mysql.html。
| 操作 | SQL语句 | 描述 |
|---|---|---|
| 增 | CREATE DATABASE 数据库名; | 创建一个数据库 |
| 增 | CREATE DATABASE IF NO EXISTS 数据库名; | 判断数据库是否存在,不存在则创建数据库 |
| 增 | CREATE DATABASE 数据库名 [DEFAULT] CHARACTER SET 字符集 [DEFAULT] COLLATE 校对规则名; | 创建数据库并指定字符集,和校对规则 |
| 删 | DROP DATABASE 数据库名; | 删除数据库 |
| 删 | DROP DATABASE IF EXISTS 数据库名; | 判断数据库是否存在,存在则删除数据库 |
| 改 | ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集; | 修改数据库默认的字符集 |
- 使用 SHOW CHARACTER SET; 查询字符集
下图中的 Charset列为字符集,Default collation为默认字符集的校对规则。
- 使用 SHOW COLLATION; 查询校对规则
下图中的Collation为字符集的校对规则: Charset列为对应字符集。
(3) 数据库的备份与还原
数据库备份文件类型以.sql为后缀进行保存简称sql脚本数据库的备份
| 命令语法格式 |说明 |
| mysqldump -u用户名 -p密码 数据库名 [表1] [表2] ... > 脚本名.sql | 绝对路径 \ 脚本名.sql | 备份单个数据库 |
| mysqldump -u用户名 -p密码 --database 数据库1 [数据库2] [数据库3] ... > 脚本名.sql | 绝对路径 \ 脚本名.sql | 备份多个数据库 |
| mysqldump -u用户名 -p密码 --all-databases >脚本名.sql | 绝对路径 \ 脚本名.sql | 备份所有数据库 |
练习:
- mysqldump -uroot -proot test>C:\Users\Administrator\Desktop\db\test.sql
- mysqldump -uroot -proot --database test user>C:\Users\Administrator\Desktop\db\test2.sql
- mysqldump -uroot -proot --all-databases>C:\Users\Administrator\Desktop\db\test3.sql
导出结果:
备注:使用 --all-databases参数备份了所有数据库,那么在还原数据库时,不需要创建数据库或指定数据库,因为在生成的SQL文件中包含了数据库的创建语句。
数据库还原
| 命令格式 | 说明 |
|---|---|
| mysql -u账号 -p密码 [dbname] < 脚本名.sql | 绝对路径\脚本名.sql | 在dos下还原数据库 |
| source 路径\xxx.sql | 在mysql下还原数据库 |
备注:dbname表示还原数据库名,在脚本中不包含创建数据库的语句时使用,包含时则不需要指定数据库名。
练习:
- source 路径\xxx.sql
导入结果:
备注:数据库的还原是还原数据库中的数据,而不是数据库,所以在还原的时候先删除原数据库然后再新建数据库,最后在从备份脚本中还原数据。
5. 数据库DDL的常用命令(操作表)
表的操作需要在具体的数据库下才可以进行操作,使用
use 数据库名; 选择数据库。
(1) 表的查询
| SQL语句 | 描述 |
|---|---|
| show tables; | 查看某个数据库中的所有表 |
| des 表名; | 查看表的结构 |
| SHOW CREATE TABLE 表名; | 查看创建表的 SQL 语句 |
(2) 表的创建
创建一张全新的表:
- 创建表的格式
CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
)ENGINE = 引擎 DEFAULT CHARACTER SET 编码;- 表的字段数据类型
- 约束条件
| 约束 | 说明 |
|---|---|
| NOT NULL | 非空约束，规定某个字段不能为空 |
| UNIQUE | UNIQUE KEY(字段1,字段2...) | 唯一约束，规定某个字段在整个表中是唯一的|复合约束,多个字段一起满足约束 |
| PRIMARY KEY | primary key(字段名1,字段名2,...) | 主键(非空且唯一)约束 |复合主键,多个字段一起组成一个主键 |
| FOREIGN KEY | 外键约束 |
| CHECK | 检查约束(MySQL 8.0可以使用MySQL5.7不支持) |
| DEFAULT | 默认值约束 |
| auto_increment | 自增约束 |
表级约束条件:
| 约束 | 说明 |
|---|---|
| [constraint 约束名] UNIQUE(字段1,字段2,...) | 唯一约束 |
| [constraint 约束名] primary key(字段名) | 主键约束 |
| [CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段) | 外键约束 |
练习:
创建一张部门表和一张员工表
DROP TABLE If EXISTS dept;
CREATE TABLE IF NOT EXISTS dept( #主表
did int primary key, #部门编号
dname varchar(50) NOT NULL #部门名称
)ENGINE = INNODB DEFAULT CHARACTER SET utf8;
DROP TABLE If EXISTS emp;
CREATE TABLE IF NOT EXISTS emp(#从表
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) #在从表中指定外键约束
#emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
)ENGINE = INNODB DEFAULT CHARACTER SET utf8;
desc dept;
desc emp;
运行效果:
根据旧表快速创建一张新表:
只复制表结构,不复制表数据
CREATE TABLE 新表名 LIKE 旧表名;# 快速创建一个表结构相同的表可选择字段,并且复制表数据
CREATE TABLE 新表名 AS SELECT 字段1,字段2,... FROM 旧表名练习:
CREATE TABLE class_new_like LIKE class;
CREATE TABLE class_new_as AS SELECT id,name FROM class;
SELECT * FROM class_new_like;
SELECT * FROM class_new_as;
运行效果:
(3) 表的删除
| SQL语句 | 描述 |
|---|---|
| DROP TABLE 表名; | 直接删除表 |
| DROP TABLE IF EXISTS 表名; | 判断表是否存在，如果存在则删除表 |
(3) 表的修改
| SQL语句 | 描述 |
|---|---|
| ALTER TABLE 表名 ADD 列名 类型; | 添加表列 |
| ALTER TABLE 表名 MODIFY 列名 新的类型; | 修改列类型 |
| ALTER TABLE 表名 CHANGE 旧列名 新列名 类型; | 修改列名 |
| ALTER TABLE 表名 DROP 列名; | 删除列 |
| ALTER TABLE 表名 DROP 列名; | 修改表名 |
| ALTER TABLE 表名 character set 字符集; | 修改字符集(gbk,utf8...) |
6. 数据库DML的常用命令(操作表记录)
DML用于对表中的记录进行增删改操作,需要在相应的数据库下,使用
use 数据库名; 选择数据库。
(1) 记录的增(insert)
- 插入记录语法
- 插入记录
| 插入字段情况 | SQL语句 | 备注 |
|---|---|---|
| 插入全部字段 | INSERT INTO 表名 ( 字段名1, 字段名2, 字段名3…) VALUES (字段1的值, 字段2的值, 字段3的值,...); | 需要的字段名都写出来,值和字段名一一对应 |
| 插入全部字段 | INSERT INTO 表名 VALUES (值 1, 值 2, 值 3…); | 不写字段名,值要和数据库表中的列(表结构列的顺序)相对应 |
| 插入部分字段 | INSERT INTO 表名 ( 字段名 1, 字段名 2, ...) VALUES (值 值 1, 值 值 2, ...); | 需要对哪一些字段赋值,就写相应的字段,但是如果对某个字段设置为了not null,或者相关属性如主键等,就必须对该列赋值 |
插入字段注意事项:
- 插入的数据应与字段的数据类型相同
- 数据的大小应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中。
- 在values中列出的数据位置必须与被加入的列的排列位置相对应。在mysql中可以使用value，但不建议使
用，功能与 values 相同。 - 字符和日期型数据应包含在单引号中。MySQL 中也可以使用双引号做为分隔符。
- 不指定列或使用 null，表示插入空值
蠕虫复制:
将一张已经存在的表中的数据复制到另一张表中,操作语法如下。
| SQL语句 | 描述 |
|---|---|
| INSERT INTO 表名1 SELECT * FROM 表名 2; | 将表名2中的所有的列复制到表名1中 |
| INSERT INTO 表名1(列1, 列2) SELECT 列1, 列2 FROM 表名2; | 将表名2中列1和列2复制到表名1中 |
备注:表1为新表,表2为提供数据的表
(2) 记录的删(delete,truncate)
- 使用delete删除记录
| 是否带条件 | SQL语句 | 描述 |
|---|---|---|
| 不带条件删除数据 | DELETE FROM 表名; | 删除表中所有数据，注意表的结构并没有删除 |
| 带条件删除数据 | DELETE FROM 表名 WHERE 字段名= 值 | 删除符合条件的数据 |
- 使用 truncate 删除表中所有记录
| SQL语句 | 描述 |
|---|---|
| TRUNCATE TABLE 表名; | 删除表中所有数据，并且表的结构也删除 |
- truncate 和 delete 的区别
truncate 相当于删除表的结构，再创建一张表,而delete只删除数据,并不删除结构。
(3) 记录的改(update)
| 描述 | SQL语句 | 备注 |
|---|---|---|
| 更新表记录语法 | UPDATE 表名 SET 列名= 值,.... [WHERE 条件表达式] ; | - |
| 不带条件修改数据 | UPDATE 表名 SET 字段名1= 值,字段名2=值...; | 不按条件修改指定记录字段的值 |
| 带条件修改数据 | UPDATE 表名 SET 字段名1= 值,字段名2=值... WHERE 字段名= 值; | 按条件修改指定记录字段的值 |
7.数据库DQL的常用命令(查询表记录)
查询不会对数据库中的数据进行修改.只是一种显示数据的方式。
(1) 简单查询
语法格式:
| 语法格式 | 说明 |
|---|---|
| SELECT [DISTNCT] 列名 FROM 表名 [WHERE 条件表达式] |
① SELECT 命令可以读取一行或者多行记录。 ② 你可以使用星号(*)来代替其他字段,SELECT 语句会返回表的所有字段数据。 ③ 你可以使用 WHERE 语句来包含任何条件。 ④ DISTNCT是可选参数,用于剔除查询结果中重复的数据 |
不带条件查询:
- 语法格式
| 操作描述 | SQL语句 | 描述 |
|---|---|---|
| 查询表中的所有行和列的数据 | SELECT * FROM 表名; | 使用*表示所有列 |
| 查询指定列 | SELECT 字段名 1, 字段名 2, 字段名 3, ... FROM 表名; | 查询指定列的数据,多个列之间以逗号分隔 |
- 指定列的别名进行查询( 显示的时候使用新的名字，并不修改表的结构)
| 操作描述 | SQL语句 |
|---|---|
| 对列指定别名 | SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名; |
| 对列和表同时指定别名 | SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名 AS 表别名; |
- 清除重复值(DISTINCT)
| 操作描述 | SQL语句 |
|---|---|
| 查询指定列并且结果不出现重复数据 | SELECT DISTINCT 字段名 FROM 表名; |
| 查询多列并且结果不出现重复数据 | SELECT DISTINCT 字段1,字段2,字段3... FROM 表名; |
- 查询结果参与运算(参与运算的必须是数值类型)
| 操作描述 | SQL语句 |
|---|---|
| 某列数据和固定值运算 | SELECT 列名 1 + 固定值 FROM 表名; |
| 某列数据和其他列数据参与运算 | SELECT 列名 1 + 列名 2 FROM 表名; |
带条件查询:
- 语法格式
| 操作描述 | SQL语句 | 描述 |
|---|---|---|
| 查询表中满足条件的所有行和列的数据 | SELECT * FROM 表名 where 条件; | 使用*表示所有列 |
| 查询表中满足条件的数据的指定列的数据 | SELECT 字段名 1, 字段名 2, 字段名 3, ... FROM 表名 where 条件; | 查询满足添加的数据的指定列的数据,多个列之间以逗号分隔 |
- 使用运算符
| 比较运算符 | 说明 |
|---|---|
| > 、< 、<= 、>= 、= 、<> | <>在 SQL 中表示不等于，在 mysql 中也可以使用!=没有== |
| BETWEEN...AND | 在一个范围之内，如：between 100 and 200相当于条件在 100 到 200 之间，包头又包尾 |
| IN( 集合) | 集合表示多个值，使用逗号分隔 |
| LIKE ' 张%' | 模糊查询 |
| IS NULL | 查询某一列为 NULL 的值，注：不能写=NULL |
| 逻辑运算符 | 说明 |
|---|---|
| and 或 && | 与，SQL 中建议使用前者，后者并不通用。 |
| or 或 || | 或 |
| not 或 ! | 非 |
备注:如果and和or同时使用的时候,按优先级高的先进行运算,即应该先运算AND二边的条件表达式。
算术运算符：
算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算。
位运算符：
位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数。
运算符的优先级：
数字编号越大，优先级越高，优先级高的运算符先进行计算。可以看到，赋值运算符的优先级最低，使用“()”括起来的表达式的优先级最高。
扩展使用正则表达式:
正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。例如，从一个文本文件中提取电话号码，查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等，这些地方都可以使用正则表达式。正则表达式强大而且灵活，可以应用于非常复杂的查询。
MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式。下表列出了REGEXP操作符中常用字符匹配列表
练习:
#在fruits表中，查询f_name字段以字母‘b’开头的记录:
SELECT * FROM fruits WHERE f_name REGEXP '^b';- 使用关键字
| 关键字 | 语法 | 描述 |
|---|---|---|
| in关键字 | SELECT 字段名 FROM 表名 WHERE 字段 in ( 数据 1, 数据 2...); | in 里面的每个数据都会作为一次条件，只要满足条件的就会显示 |
| like关键字 | SELECT * FROM 表名 WHERE 字段名 LIKE ' 通配符字符串'; | LIKE 表示模糊查询 |
| BETWEEN AND关键字 | 字段名 BETWEEN 值1 AND 值2 | 表示从值 1 到值 2 范围，包头又包尾 |
IS [NOT] NULL关键字
| 某字段是否为null | SQL | 描述 |
|---|---|---|
| 是 | SELECT * FROM 表1 WHERE 字段1 IS NULL | 查询表1中字段1是null的所有记录 |
| 否 | SELECT * FROM 表1 WHERE 字段1 IS NOT NULL | 查询表1中字段1不是null的所有记录 |
备注:在 MySQL 里面,空值不等于空字符串,一个空字符串的长度是 0,而一个空值的长度是空.而且,在 MySQL 里面,空值是占用空间的。
- 使用通配符
| 通配符 | 说明 |
|---|---|
| % | 匹配任意多个字符串 |
| _ | 匹配一个字符 |
备注:如果查询的数据中有字符%或_,需要使用转义字符,如"/%" 表示"%","/_" 表示_。
练习:
创建一个学生表student3 ，并插入几条记录
CREATE TABLE student3 (
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math int, -- 数学
english int -- 英语
);
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','
杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩
',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港
',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);使用逻辑运算符查询相关操作:
查询 math 分数大于 80 分的学生
select * from student3 where math>80;查询 english 分数小于或等于 80 分的学生
select * from student3 where english <=80;查询 age 等于 20 岁的学生
select * from student3 where age = 20;查询 age 不等于 20 岁的学生，注：不等于有两种写法
select * from student3 where age <> 20;
select * from student3 where age != 20;使用相关逻辑运算符相关操作:
查询 age 大于 35 且性别为男的学生(两个条件同时满足)
select * from student3 where age>35 and sex='男';查询 age 大于 35 或性别为男的学生(两个条件其中一个满足)
select * from student3 where age>35 or sex='男';查询 id 是 1 或 3 或 5 的学生
select * from student3 where id=1 or id=3 or id=5;使用关键字相关操作:
查询 id 是 1 或 3 或 5 的学生
select * from student3 where id in(1,3,5);查询 id 不是 1 或 3 或 5 的学生
select * from student3 where id not in(1,3,5);`查询 english 成绩大于等于 75，且小于等于 90 的学生
select * from student3 where english between 75 and 90;使用通配符相关操作:
查询姓马的学生
select * from student3 where name like '马%';
select * from student3 where name like '马';查询姓名中包含'德'字的学生
select * from student3 where name like '%德%';查询姓马，且姓名有两个字的学生
select * from student3 where name like '马_';(2) 高级查询
语法格式:
| 语法格式 | 说明 |
|---|---|
| SELECT [DISTNCT] 列名 FROM 表名 [WHERE 条件表达式1] [GROUP BY 字段名,.. [HAVING 条件表达式2]] [ORDER BY 字段名 [ASC| DESC] ] [LIMIT [OFFSET] 记录数 |
①GROUP BY 表示分组查询,可按多个字段分组,HAVING 为可选项表示条件,当出现HAVING 时就无法使用Where作为条件 ② ORDER BY表示排序,对筛选过后的数据进行排序,默认升序ASC,降序需要加上DESC ③ LIMIT 表示分页查询,OFFSET用来设置偏移量,默认偏移量为0 |
使用函数进行查询(作用在列上)
函数分为内置函数和自定义函数,内置函数是系统内置的通用函数,而自定义函数是根据自己需求编写的函数,下面列出的函数是常用的Mysql内置函数,来源于尚硅谷Mysql视频
https://www.bilibili.com/video/BV1iq4y1u7vj?p=32,当然更全更详细的查询还是要通过Mysql官网进行查询
https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html(
注意这里给出的是mysql数据库的函数,不同数据库的函数存在一定差异,在其他数据库中可能无法使用 )。
聚合函数
| 函数名称 | 作 用 |
|---|---|
| MAX | 查询指定列的最大值 |
| MIN | 查询指定列的最小值 |
| COUNT | 统计查询结果的行数 |
| SUM | 求和，返回指定列的总和 |
| AVG | 求平均值，返回指定列数据的平均值 |
练习:
# 查询student3中平均年龄
SELECT AVG(age) as '平均年龄'
FROM student3;
# 查询年龄最小的学生信息
use test;
SELECT MIN(age) as '最小年龄'
FROM student3
# 查询年龄最大的学生信息
SELECT MAX(age) as '最大年龄'
FROM student3
# 一共有多少条记录
SELECT Count(*) as '总人数' FROM student3
# 学生英语总分
SELECT SUM(english) as '全班英语总共获得分数' FROM student3数值型函数
- 基本函数
| 函数名称 | 作 用 |
|---|---|
| ABS(x) | 返回x的绝对值 |
| SIGN(X) | 返回X的符号。正数返回1，负数返回-1，0返回0 |
| PI() | 返回圆周率的值 |
| CEIL(x)，CEILING(x) | 返回大于或等于某个值的最小整数 |
| FLOOR(x) | 返回小于或等于某个值的最大整数 |
| LEAST(e1,e2,e3…) | 返回列表中的最小值 |
| GREATEST(e1,e2,e3…) | 返回列表中的最大值 |
| MOD(x,y) | 返回X除以Y后的余数 |
| RAND() | 返回0~1的随机值 |
| RAND(x) | 返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数 |
| ROUND(x) | 返回一个对x的值进行四舍五入后，最接近于X的整数 |
| ROUND(x,y) | 返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位 |
| TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
| SQRT(x) | 返回x的平方根。当X的值为负数时，返回NULL |
- 角度与弧度互换函数
| 函数名称 | 作 用 |
|---|---|
| RADIANS(x) | 将角度转化为弧度，其中，参数x为角度值 |
| DEGREES(x) | 将弧度转化为角度，其中，参数x为弧度值 |
- 三角函数
| 函数名称 | 作 用 |
|---|---|
| SIN(x) | 返回x的正弦值，其中，参数x为弧度值 |
| ASIN(x) | 返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL |
| COS(x) | 返回x的余弦值，其中，参数x为弧度值 |
| ACOS(x) | 返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL |
| TAN(x) | 返回x的正切值，其中，参数x为弧度值 |
| ATAN(x) | 返回x的反正切值，即返回正切值为x的值 |
| ATAN2(m,n) | 返回两个参数的反正切值 |
| COT(x) | 返回x的余切值，其中，X为弧度值 |
- 指数与对数
| 函数名称 | 作 用 |
|---|---|
| POW(x,y)，POWER(X,Y) | 返回x的y次方 |
| EXP(X) | 返回e的X次方，其中e是一个常数，2.718281828459045 |
| LN(X)，LOG(X) | 返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL |
| LOG10(X) | 返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL |
| LOG2(X) | 返回以2为底的X的对数，当X <= 0 时，返回NULL |
- 进制间的转换
| 函数名称 | 作 用 |
|---|---|
| BIN(x) | 返回x的二进制编码 |
| HEX(x) | 返回x的十六进制编码 |
| OCT(x) | 返回x的八进制编码 |
| CONV(x,f1,f2) | 返回f1进制数变成f2进制数 |
练习:
#返回10的二进制编码
SELECT BIN(10);
#返回10的十六进制编码
SELECT HEX(10);
#返回10的八进制编码
SELECT OCT(10);
#返回2进制数变成10进制数
SELECT CONV(1010,2,10);
#返回10进制数变成2进制数
SELECT CONV(10,10,2);
字符串函数
| 函数名称 | 作 用 |
|---|---|
| LENGTH | 计算字符串长度函数，返回字符串的字节长度 |
| CONCAT | 合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个 |
| INSERT | 替换字符串函数 |
| LOWER | 将字符串中的字母转换为小写 |
| UPPER | 将字符串中的字母转换为大写 |
| LEFT | 从左侧字截取符串，返回字符串左边的若干个字符 |
| RIGHT | 从右侧字截取符串，返回字符串右边的若干个字符 |
| TRIM | 删除字符串左右两侧的空格 |
| REPLACE | 字符串替换函数，返回替换后的新字符串 |
| SUBSTRING | 截取字符串，返回从指定位置开始的指定长度的字符换 |
| REVERSE | 字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串 |
日期和时间函数
- 获取日期、时间
| 函数名称 | 作 用 |
|---|---|
| CURDATE() ，CURRENT_DATE() | 返回当前日期，只包含年、月、日 |
| CURTIME() ， CURRENT_TIME() | 返回当前时间，只包含时、分、秒 |
| NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() /LOCALTIMESTAMP() | 返回当前系统日期和时间 |
| UTC_DATE() | 返回UTC（世界标准时间）日期 |
| UTC_TIME() | 返回UTC（世界标准时间）时间 |
练习:
#返回当前日期，只包含年、月、日
SELECT CURDATE() as '当前日期'
#返回当前时间，只包含时、分、秒
SELECT CURTIME() as '当前日期'
#返回当前系统日期和时间
SELECT NOW() as '当前系统时间'
#返回UTC（世界标准时间）日期
SELECT UTC_DATE();
#返回UTC（世界标准时间）时间
SELECT UTC_TIME();
- 日期与时间戳的转换
| 函数名称 | 作 用 |
|---|---|
| UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() ->1634348884 |
| UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回。 |
| FROM_UNIXTIME(timestamp) | 将UNIX时间戳的时间转换为普通格式的时间 |
练习:
# 获取当前时间的时间戳
SELECT UNIX_TIMESTAMP();
# 获取当前时间的时间戳
SELECT UNIX_TIMESTAMP(NOW());
# 获取当前时间的时间戳
SELECT UNIX_TIMESTAMP(CURRENT_DATE());
# 获取当前时间的时间戳
SELECT FROM_UNIXTIME(CURRENT_TIME())- 获取月份、星期、星期数、天数等函数
| 函数名称 | 作 用 |
|---|---|
| YEAR(date) / MONTH(date) / DAY(date) | 返回具体的日期值 |
| HOUR(time) / MINUTE(time) /SECOND(time) | 返回具体的时间值 |
| MONTHNAME(date) | 返回月份：January，... |
| DAYNAME(date) | 返回星期几：MONDAY，TUESDAY.....SUNDAY |
| WEEKDAY(date) | 返回周几，注意，周1是0，周2是1，。。。周日是6 |
| QUARTER(date) | 返回日期对应的季度，范围为1～4 |
| WEEK(date) ， WEEKOFYEAR(date) | 返回一年中的第几周 |
| DAYOFYEAR(date) | 返回日期是一年中的第几天 |
| DAYOFMONTH(date) | 返回日期位于所在月份的第几天 |
| DAYOFWEEK(date) | 返回周几，注意：周日是1，周一是2，。。。周六是7 |
练习:
#获取当前年月日
SELECT YEAR(NOW()) as '年' ,MONTH(NOW()) as '月' , DAY(NOW()) as '日';
#获取当前时分秒
SELECT HOUR(NOW()) as '时' , MINUTE(NOW()) as '分' , SECOND(NOW()) as '秒';
- 日期的操作函数
| 函数名称 | 作 用 |
|---|---|
| EXTRACT(type FROM date) | 返回指定日期中特定的部分，type指定返回的值 |
EXTRACT(type FROM date)函数中type的取值与含义：
练习:
#获取NOW()中的日
SELECT EXTRACT(DAY FROM NOW()) as '获取当前的日';
#获取NOW()中的年月
SELECT EXTRACT(YEAR_MONTH FROM NOW()) as '获取当前的年和月';- 时间和秒钟转换的函数
| 函数名称 | 作 用 |
|---|---|
| TIME_TO_SEC(time) | 将 time 转化为秒并返回结果值。转化的公式为： 小时3600+分钟60+秒 |
| SEC_TO_TIME(seconds) | 将 seconds 描述转化为包含小时、分钟和秒的时间 |
练习:
#将 time 转化为秒并返回结果值
SELECT TIME_TO_SEC(NOW());
#将 seconds 描述转化为包含小时、分钟和秒的时间
SELECT SEC_TO_TIME(85577);- 计算日期和时间的函数
| 函数名称 | 作 用 |
|---|---|
| DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type) | 返回与给定日期时间相差INTERVAL时间段的日期时间 |
| DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type) | 返回与date相差INTERVAL时间间隔的日期 |
上述函数中type的取值：
| 函数名称 | 作 用 |
|---|---|
| ADDTIME(time1,time2) | 返回time1加上time2的时间。当time2为一个数字时，代表的是秒 ，可以为负数 |
| SUBTIME(time1,time2) | 返回time1减去time2后的时间。当time2为一个数字时，代表的是 秒 ，可以为负数 |
| DATEDIFF(date1,date2) | 返回date1 - date2的日期间隔天数 |
| TIMEDIFF(time1, time2) | 返回time1 - time2的时间间隔 |
| FROM_DAYS(N) | 返回从0000年1月1日起，N天以后的日期 |
| TO_DAYS(date) | 返回日期date距离0000年1月1日的天数 |
| LAST_DAY(date) | 返回date所在月份的最后一天的日期 |
| MAKEDATE(year,n) | 针对给定年份与所在年份中的天数返回一个日期 |
| MAKETIME(hour,minute,second) | 将给定的小时、分钟和秒组合成时间并返回 |
| PERIOD_ADD(time,n) | 返回time加上n后的时间 |
- 日期的格式化与解析
| 函数名称 | 作 用 |
|---|---|
| DATE_FORMAT(date,fmt) | 按照字符串fmt格式化日期date值 |
| TIME_FORMAT(time,fmt) | 按照字符串fmt格式化时间time值 |
| GET_FORMAT(date_type,format_type) | 返回日期字符串的显示格式 |
| STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析，解析为一个日期 |
上述 非GET_FORMAT 函数中fmt参数常用的格式符：
GET_FORMAT函数中date_type和format_type参数取值如下：
流程控制函数
| 函数名称 | 作 用 |
|---|---|
| CASE | 搜索语句 |
| IF(expr,v1,v2) | 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。 |
| IFNULL(v1,v2) | 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 |
| NULLIF(expr1, expr2) | 比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1 |
IF操作练习:
SELECT IFNULL(NULL,'值为NULL');#值为NUL
SELECT IFNULL(1,'值不为NULL');#1
SELECT NULLIF(1,1);#null
SELECT NULLIF(1,2); # 1
SELECT NULLIF(2,1); # 2CASE操作练习:
语法方式1:
CASE WHENconditionTHENresult [WHEN condition THEN result ...] [ELSE result]END(相当于Java中的if...else if...else...)
语法方式2:
CASEvalueWHENcompare_valueTHENresult [WHEN compare_value THEN result ...] [ELSE result]END(相当于Java的switch...case...)
#查询学生信息,但是学生的性别按1,0和-1进行显示,即1:男,0:女,-1:未知
SELECT id,name,
CASE sex
WHEN '男' THEN 1
WHEN '女' THEN 0
ELSE -1
END as 'sex',
age,address,math,english
FROM student3;
#查询学生信息,但是学生的英语列显示合格(>=60 && <85),不合格(<60),优秀(>=85)
SELECT
id,name,age,sex,address,math,
CASE
WHEN english >= 60 && english <85
THEN '合格'
WHEN english >= 85
THEN '优秀'
ELSE
'不合格'
END as '英语情况'
FROM student3;运行效果:
对查询结果进行排序(作用在行上)
从表中查询到的数据可能是无序的,或者不满足用户期望的,这时就可以使用 ORDER BY 子句进行排序,其中ASC（ascend）表示升序,DESC（descend）表示降序,mysql默认采用ASC升序方式。
语法格式如下:
SELECT 字段名1,字段名2,...
FROM 表名
ORDER BY 字段名1 [ASC| DESC] ,字段名2 [ASC| DESC]...按排序的字段数量可分为单列排序和多列排序,单列排序表示按单个字段进行排序,多列排序表示按多个字段进行排序,相关规则如下:
- 可以使用不在SELECT列表中的列排序。
- 在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第
一列数据中所有值都是唯一的，将不再对第二列进行排序。
练习:
# 按数学升序ASC进行排序【单列排序】
SELECT * FROM student3
ORDER BY math
# 按数学降序,英语降序进行排序【多列排序】
SELECT * FROM student3
ORDER BY math DESC ,english DESC运行效果:
分组查询(作用在行上)
在表中数据进行统计时,有时需要按照一定的类别进行统计,如按性别统计班上男,女,null(性别可能的值为男,女,null)的学科成绩的平均分，这时候就可以使用GROUP BY语句进行分组查询(一般会和聚合函数搭配使用),如果分组查询需要相应条件使用HAVING，并且HAVING语句后面可以跟内置函数。
语法格式:
SELECT 字段名1,字段名2,...
FROM 表名
GROUP BY 字段1,字段2.. [HAVING 条件表达式]单独使用:
# 按性别进行分组,查询用户信息,会按sex的值进行分组查询
INSERT INTO student3(id,name,age,sex,address,math,english) VALUES (9,'黑户',99,NULL,'NULL',99,99);
SELECT * FROM student3
GROUP BY SEX
搭配聚合函数一起使用:
# 分别统计班上男生和女生的数学和英语的平均分
SELECT sex,AVG(math) as '数学平均分', AVG(english) as '英语平均分'
FROM student3
GROUP BY SEX
ORDER BY math DESC ,english DESC
# 分别统计班上男生和女生的数学和英语的平均分,并且均在80及以上
SELECT sex,AVG(math) as '数学平均分', AVG(english) as '英语平均分'
FROM student3
GROUP BY SEX HAVING AVG(math) >= 80 and AVG(english) >= 80
ORDER BY math DESC ,english DESC运行效果:
分页查询(作用在行上)
查询数据时,可能会返回很多条记录,而用户需要记录可能只是其中的一条或者几条,这时就可以通过LIMIT进行分页查询,其中OFFSET表示设置偏移量,偏移量决定记录从第几条开始,默认偏移量为0。所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。
语法格式:
SELECT 字段名1,字段名2,...
FROM 表名
LIMIT [OFFSET,] 记录数单个参数查询
在mysql 5以后支持这种写法
#前3条记录[1,3]：
SELECT * FROM student3 LIMIT 3;
#第4至6条记录 [4,6]：
SELECT * FROM student3 LIMIT 3 OFFSET 3;
#第7至9条记录 [7,9]：
SELECT * FROM student3 LIMIT 3 OFFSET 6;运行效果:
二个参数的查询(运行结果一致):
#前3条记录[1,3]：
SELECT * FROM student3 LIMIT 0,3;
#第4至6条记录 [4,6]：
SELECT * FROM student3 LIMIT 3,3;
#第7至9条记录 [7,9]：
SELECT * FROM student3 LIMIT 6,3;备注: 第一个参数表示偏移量,第二个参数表示记录数
使用分页查询的好处:
约束返回结果的数量可以 减少数据表的网络传输量 ，也可以 提升查询效率 。如果我们知道返回结果只有 1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需 要扫描完整的表，只需要检索到一条符合条件的记录即可返回。
(3) 多表查询
多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。
笛卡尔积（交叉连接）
笛卡尔乘积是一个数学运算,可以把任意表进行连接,即使二张表不相关(
实际开发中很少使用 )。假设有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。
语法格式:
SELECT * FROM 表1 CROSS JOIN 表2
# 或
SELECT * FROM 表1,表2二张表的字段不相同
练习:
# 创建class表
use test;
DROP TABLE IF EXISTS class;
CREATE TABLE class(
id int, -- 编号
name varchar(20), -- 编辑名称
count int -- 班级人数
)DEFAULT CHARACTER SET utf8;
INSERT INTO class(id,name,count) VALUES (1,'1班',45),(2,'2班',50),(3,'3班',42),(4,'4班',36),(5,'5班',43)
# 使用笛卡尔积查询
SELECT *
FROM student3 CROSS JOIN class;
# 使用笛卡尔积查询(还上面的运行效果一致)
SELECT *
FROM student3,class;
运行效果:
二张表的字段相同:
练习:
#创建一张和student3结构一样的表
USE test;
DROP TABLE IF EXISTS student4;
CREATE TABLE student4 (
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math int, -- 数学
english int -- 英语
) DEFAULT CHARACTER SET utf8;
INSERT INTO student4(id,name,age,sex,address,math,english) VALUES (1,'马云',55,'男','
杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩
',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港
',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);
# 使用笛卡尔积查询
SELECT * FROM student3,student4;运行效果:
接下里的所有操作都是在笛卡尔积的基础上进行进一步赛选,赛选出符合自己需求的查询。
内连接
内连接(Inner Join) 又称简单连接或自然连接（特殊的等值连接）,是一种常见的连接查询,内连接使用比较运算符对二张表的数据进行比较,并列出与连接条件匹配的数据行,组合成新的记录,
语法格式:
.
# 等值连接
SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段 = 表2.关系字段
# 特殊等值连接(自然连接)
SELECT 查询字段 FROM 表1 NATURAL JOIN 表2 WHERE 表1.关系字段 = 表2.关系字段
# 非等值连接
SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段 >或<或<=或>=或<> 表2.关系字段备注:相同字段,使用 表名.字段名 进行区分。
内连接可简单分为等值连接和非等值连接,等值连接就是采用比较运算符里的'='进行连接 ,非等值连接就是不采用'='进行连接的连接,如(> 、< 、<= 、>=,<> )。
等值连接
# 查询表student3和student4交集部分的记录
SELECT *
FROM student3 s3 JOIN student3 s4
ON s3.id = s4.id运行效果:
非等值连接
# 查询表student3和student4除了交集部分的记录
SELECT *
FROM student3 s3 JOIN student3 s4
ON s3.id <> s4.id运行效果:
特殊的等值连接(自然连接)
自然连接(Naturaljoin)是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。
相同属性组:
SELECT *
FROM student3 s3 NATURAL JOIN student4 s4
WHERE s3.id = s4.id运行效果:
非相同属性组( 查询不出任何数据 ):
SELECT *
FROM student3 s3 NATURAL JOIN class c
WHERE s3.id = c.id运行效果:
外连接:
内连接返回结果只包含符合查询添加和连接条件的数据,然而有时还需要包含没有关联的数据,即返回查询结果中不仅包含符合条件的数据,而且还包括左表(左连接或左外连接),右表(右连接或左外连接)或二个表(全外连接)中的所有数据,此时就需要使用外连接查询,在Mysql中外连接分为左连接,右连接(mysql不支持全外连接)。
语法格式:
SELECT 所查字段 FROM 表1 LEFT|RIGHT [OUTER] JOIN 表2
ON 表1.关系字段 = 表2.关系字段
WHERE 条件LEFT JOIN (左连接):
左连接结果 = 左表中所有记录 + 右表中符合连接条件的记录
练习:
# 查询所有学生信息和满足条件的班级信息
SELECT *
FROM student3 s
LEFT JOIN class c
ON s.id = c.id运行效果:
RIGHT JOIN(右连接):
右连接返回结果 = 左表中符合连接条件的记录 + 右表中所有记录
练习:
全连接 FULL JOIN(全外连接):
全外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
由于mysql不支持FULL JOIN 但是可以使用 LEFT JOIN UNION RIGHT JOIN 来代替:
练习:
# 返回结果 = 左表中的所有记录 + 右表中符合条件的记录
SELECT *
FROM student3 s
LEFT JOIN class c
ON s.id = c.id
UNION # 返回结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据
# 返回结果 =左表中符合条件的记录 + 右表中的所有记录
SELECT *
FROM student3 s
RIGHT JOIN class c
ON s.id = c.id运行效果:
UNION的使用:
合并查询结果:利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。
语法格式:
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2UNION 操作符
返回两个查询的结果集的并集，去除重复记录。
练习:
SELECT * FROM student3
UNION
SELECT * FROM student4运行效果:
UNION ALL操作符
返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
练习:
SELECT * FROM student3
UNION ALL
SELECT * FROM student4运行效果:
子查询:
子查询是指一个查询语句嵌套在另一个查询语句内部的查询,在执行查询语句时,首先会执行子查询中的语句,然后将返回结果作为外层查询的过滤条件,按内查询的结果返回一条还是多条记录，将子查询分为 单行子查询和多行子查询。
单行子查询:
| 单行比较操作符 | 说明 |
|---|---|
| > 、< 、<= 、>= 、= 、<> | <>在 SQL 中表示不等于，在 mysql 中也可以使用 != |
练习:
# 查询5班中所有学生信息
SELECT *
FROM student3
WHERE id = (SELECT id FROM class WHERE name = '5班')
# 查询除了5班外所有学生信息
SELECT *
FROM student3
WHERE id <> (SELECT id FROM class WHERE name = '5班')运行效果:
多行子查询:
| 多行比较操作符 | 说明 |
|---|---|
| IN | 等于列表中的任意一个 |
| [NOT] EXISTS | 子查询不产生任何数据,相当于测试,只返回TRUE和FALSE,当返回TRUE时,外层查询才执行 |
| ANY | 需要和单行比较操作符一起使用，和子查询返回的某一个值比较 |
| ALL | 需要和单行比较操作符一起使用，和子查询返回的所有值比较 |
| SOME | 实际上是ANY的别名，作用相同，一般常使用ANY |
练习:
- IN
# 查询班级表中所有学生信息
SELECT *
FROM student3
WHERE id in (SELECT id FROM class ) # 子查询,查出的id分别为[1,5]运行效果:
- EXISTS
# 没有记录返回
SELECT *
FROM student3
WHERE EXISTS (SELECT id FROM class WHERE id >5)
# 有记录返回
SELECT *
FROM student3
WHERE EXISTS (SELECT id FROM class WHERE id >= 5)
# 有记录返回
SELECT *
FROM student3
WHERE NOT EXISTS (SELECT id FROM class WHERE id >5)
# 没有记录返回
SELECT *
FROM student3
WHERE NOT EXISTS (SELECT id FROM class WHERE id >= 5)运行效果:
备注:在EXISTS 中的子查询只要查询到一条记录就会返回TRUE,如果一条都没有就返回FALSE,如果前面有NOT的话,可以假设为取反,子查询返回TRUE取反就为FALSE如果为FALSE取反就为TRUE,最后返回的结果为TRUE时外层查询就会执行,并且EXISTS要比IN的执行效率更高。
- ANY 和SOME
# student3中的id只要满足大于class中任一一条记录的id
SELECT *
FROM student3
WHERE id > ANY (SELECT id FROM class)
# 效果和前面一条查询一致
SELECT *
FROM student3
WHERE id > SOME (SELECT id FROM class)运行效果:
- ALL
# student3中的id必须满足大于class中所有记录的id
SELECT *
FROM student3
WHERE id > ALL (SELECT id FROM class)运行效果:
8.数据库TCL的常用命令
(1) 事务
事务处理机制在程序开发过程中有着非常重要的作用,它可以使整个系统更加安全,保证在同一个事务中的操作具有同步性。
| SQL语句 | 描述 |
|---|---|
| START TRANSACTION | 开启事务 |
| COMMIT | 提交事务 |
| ROLLBACK | 回滚事务(只能回滚未提交的事务) |
| SET SESSION TRANSACTION ISOLATION LEVEL 事务隔离级别 | 设置事务的隔离级别 |
(2) 事务的开启,提交,回滚
练习:
创建相应表格:
#账户
USE test;
DROP TABLE IF EXISTS account;
CREATE TABLE account (
id int PRIMARY KEY auto_increment, -- 账户
name varchar(20), -- 账户名
money FLOAT -- 金额
) DEFAULT CHARACTER SET utf8;
INSERT INTO account(name,money) VALUES('a',1000),('b',1000);开启事务,未提交,a向b转100元:
# 开启事务
START TRANSACTION;
UPDATE account SET money = money -100 WHERE name = 'a';
UPDATE account SET money = money + 100 WHERE name = 'b';在Navicat中执行:
在DOS中进行查询:
开启事务,并提交,a向b转100元:
开启事务
START TRANSACTION;
UPDATE account SET money = money -100 WHERE name = 'a';
UPDATE account SET money = money + 100 WHERE name = 'b';
COMMIT;
在Navicat中执行:
在DOS中进行查询:
**`备注:在MYSQL中直接书写的SQL语句都是自动提交的,而事务中的操作语句都需要COMMIT语句手动提交，只有事务提交后其中的操作才会生效。`**
事务1:开启事务,a向b转100元,再提交
事务2:开启事务a向b转900,回滚事务,再提交
a向b转100元,a:800 b:1200
START TRANSACTION;
UPDATE account SET money = money - 100 WHERE name = 'a';
UPDATE account SET money = money + 100 WHERE name = 'b';
COMMIT;
a向b转900元 800-900 余额不足,回滚 a:800 b:1200
START TRANSACTION;
UPDATE account SET money = money - 900 WHERE name = 'a';
UPDATE account SET money = money + 900 WHERE name = 'b';
ROLLBACK;
COMMIT;
在Navicat中执行:
执行第一次事务
执行第二次事务
在DOS中进行查询:
查询第一次事务
查询第二次事务
#### (3) 事务隔离级别
- READ UNCOMMITTED(没提交)
- READ COMMITTED(读提交)
- REPEATABLE READ(可重复读,mysql默认事务隔离级别)
- SERIALIZABLE(可串行化)
**使用下面的SQL语句查询事务隔离级别:**
SELECT @@tx_isolation;
运行效果:
**设置事务隔离级别为READ COMMITTED(读提交):**
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@tx_isolation;
**`备注:SESSION 表示当前会话,TRANSACTION 表示事务,ISOLATION 表示隔离,LEVEL 表示级别`**
运行效果:
### 9.存储过程与存储函数
#### (1) 存储过程
**创建存储过程**CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
存储过程体
END
说明：
1、参数前面的符号的意思
- IN ：当前参数为输入参数，也就是表示入参；存储过程只是读取这个参数的值。如果没有定义参数种类， 默认就是 IN ，表示输入参数。
- OUT ：当前参数为输出参数，也就是表示出参；执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
- INOUT ：当前参数既可以为输入参数，也可以为输出参数。
2、形参类型可以是 MySQL数据库中的任意类型。
3、 characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
**characteristics 参数解释:**
1. LANGUAGE SQL
> 说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
2. [NOT] DETERMINISTIC
> 指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT
> DETERMINISTIC。
3. { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
> 指明子程序使用SQL语句的限制。
| 限制名 | 描述 |
|--|--|
CONTAINS SQL|表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；（默认情况）
NO SQL|表示当前存储过程的子程序中不包含任何SQL语句；
READS SQL DATA |表示当前存储过程的子程序中包含读数据的SQL语句；
MODIFIES SQL DATA |表示当前存储过程的子程序中包含写数据的SQL语句。
4. SQL SECURITY { DEFINER | INVOKER }
> 执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。
|权限名|描述 |
|--|--|
DEFINER |表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
INVOKER |表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
**练习:**
定义存储过程select_all_data()DELIMITER $ # 定义结束符号为$
CREATE PROCEDURE select_all_data()
DETERMINISTIC
CONTAINS SQL
BEGIN
SELECT * FROM student3;
SELECT * FROM class;
END $
DELIMITER ; # 恢复结束符号为;
定义存储过程select_max_english(OUT num DOUBLE)
DELIMITER $ # 定义结束符号为$
CREATE PROCEDURE select_max_english(OUT num DOUBLE)
DETERMINISTIC
CONTAINS SQL
BEGIN
SELECT MAX(english) INTO num FROM student3 ;
END $
DELIMITER ; # 恢复结束符号为;
定义存储过程select_name_students(IN name VARCHAR(20))
DELIMITER $ # 定义结束符号为$
CREATE PROCEDURE select_name_students(IN inname VARCHAR(20) CHARACTER SET utf8)
DETERMINISTIC
CONTAINS SQL
BEGIN
SELECT s.id as '学号',s.name as '姓名' , sex as '性别',address as '地址' , c.name as '班级名' ,count as '班级人数' FROM student3 s,class c
WHERE s.id = c.id and c.name = inname;
END $
DELIMITER ; # 恢复结束符号为;
定义存储过程select_name_class(INOUIT name VARCHAR(20))
DELIMITER $ # 定义结束符号为$
CREATE PROCEDURE select_name_class(INOUT stuname VARCHAR(20) CHARACTER SET utf8)
DETERMINISTIC
CONTAINS SQL
BEGIN
SELECT name INTO stuname FROM class WHERE id = (SELECT id FROM student3 WHERE name = stuname);
END $
DELIMITER ; # 恢复结束符号为;
**存储过程中复杂的SQL语句:**
- BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
- DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进
行变量的声明。
- SET：赋值语句，用于对变量进行赋值。
- SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。
- DELIMITER : 新的结束标记
**`备注:存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END
编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的 SQL 语句。`**
**调用存储过程**
> 存储过程有多种调用方法。存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行
其他数据库中的存储过程，需要指定数据库名称，例如CALL dbname.procname。
格式:
| 格式名称 | SQL语句 |
|--|--|
| 调用无参| CALL 存储过程名(); |
|调用IN模式的参数|CALL sp1('值');
|调用out模式的参数|SET @name;<br>CALL sp1(@name);<br>SELECT @name;<br>
|调用inout模式的参数|SET @name=值;<br>CALL sp1(@name);<br>SELECT @name;|
**练习:**
CALL select_all_data();
运行效果:
CALL select_name_students('五班');
运行效果:
SET @num = 0.0;# 声明变量@num
CALL select_max_english(@num);# 传参@num
SELECT @num as '英语最高分';# 输出@num
运行效果:
SET @stuname = '马云';
CALL select_name_class(@stuname);
SELECT @stuname as '班级名' ;
运行效果:
#### (2) 存储函数
**创建存储函数**
CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
函数体 #函数体中肯定有 RETURN 语句
END
说明：
1. 参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参 数。
2. RETURNS type 语句表示函数返回数据的类型；RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个 RETURN value 语句。
3. characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述。
4. 函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略 BEGIN…END。
**调用存储函数(自定义函数)**
SELECT 函数名(实参列表)
练习:
创建get_name(num INT)函数
DELIMITER $ # 定义结束符号为$
CREATE FUNCTION get_name(num INT)
RETURNS VARCHAR(20) CHARACTER SET utf8
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT name FROM class WHERE id = num);
END $
DELIMITER ; # 恢复结束符号为;
调用函数
SELECT get_name(1) AS '班级';
运行效果:
#### (3) 存储过程与存储函数的对比
| | 关键字 |调用语法 |返回值| 应用场景|
|--|--|--|--|--|
|存储过程|PROCEDURE|CALL 存储过程()|理解为有0个或多个|一般用于更新|
|存储函数|FUNCTION|SELECT 函数()|只能是一个|一般用于查询结果为一个值并返回时
**`备注:存储函数可以放在查询语句中使用，存储过程不行。反之，存储过程的功能更加强大，包括能够执行对表的操作（比如创建表，删除表等）和事务操作，这些功能是存储函数不具备的。`**
#### (4) 存储过程与存储函数的查看,修改和删除
| 操作 |语法 |
|--|--|
|查看存储过程或存储函数创建信息 | SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名 |
|查看存储过程或存储函数状态信息 | SHOW {PROCEDURE | FUNCTION} STATUS [LIKE '存储过程名或函数名'] |
|从information_schema.Routines表中查看存储过程和函数的信息|SELECT * FROM information_schema.Routines<br>WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE | FUNCTION'}];|
|修改存储过程或函数的特性|ALTER {PROCEDURE|FUNCTION} 存储过程或函数的名 [characteristic ...]|
|删除存储过程或函数|DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名|
characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
参数解释:
| 参数 | 解释 |
|--|--|
CONTAINS SQL|表示子程序包含SQL语句，但不包含读或写数据的语句。
NO SQL|表示子程序中不包含SQL语句。
READS SQL DATA |表示子程序中包含读数据的语句。
MODIFIES SQL DATA |表示子程序中包含写数据的语句。
SQL SECURITY { DEFINER | INVOKER } |指明谁有权限来执行。
DEFINER |表示只有定义者自己才能够执行。
INVOKER |表示调用者可以执行。
COMMENT 'string' |表示注释信息。
**练习:**
select_all_dat:前面创建的存储过程,get_name:前面创建的存储函数
查看存储过程或存储函数创建信息
SHOW CREATE PROCEDURE select_all_data;
SHOW CREATE FUNCTION get_name;
查看存储过程或存储函数状态信息
SHOW PROCEDURE STATUS LIKE 'select_all_data' ;
SHOW FUNCTION STATUS LIKE 'get_name' ;
从information_schema.Routines表中查看存储过程和函数的信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='select_all_data' AND ROUTINE_TYPE = 'PROCEDURE';
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='get_name' AND ROUTINE_TYPE = 'FUNCTION';
修改存储过程或函数的特性
ALTER PROCEDURE select_all_data
MODIFIES SQL DATA
SQL SECURITY INVOKER ;
ALTER FUNCTION get_name
MODIFIES SQL DATA
SQL SECURITY INVOKER ;
删除存储过程或函数
DROP PROCEDURE IF EXISTS select_all_data;
DROP FUNCTION IF EXISTS get_name;
### 10.视图
> 视图是从一个或多个表中导出来的表,它是一种虚拟存在的表,并且表结构和数据都依赖与基本表。通过视图不仅可以看到存放在基本表中的数据,并且还可以像操作基本表一样,对视图中存放的数据进行查询,修改和删除。
#### (1) 创建视图
**语法格式**
- 详细格式:
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(column_list)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]
- 参数解释:
| 参数 | 解释 |
|--|--|
| OR REPLACE | 表示该语句能够替换已有视图|
|ALGORITHM |表示视图选择的算法|
|UNDEFINED |表示Mysql将自动选择所需要的算法|
|MERGE |表示将使用视图的语句与视图定义合并起来,使得视图定义的某一部分取代语句对应部分|
|TEMPTABLE|表示将视图的结果存入临时表,然后使用临时表执行语句|
|column_list|表示属性清单，指定了视图中各个属性的名,默认情况与SELECT语句查询的属性相同|
|WITH CHECK OPTION|表示创建视图时要保证在该视图的权限范围之内|
|CASCADED|表示在创建视图时,需要满足跟该视图有关的所有相关的视图和表的条件,该参数为默认值|
|LOCAL|表示创建视图时,只要满足该视图本身定义的条件即可|
- 精简格式:
CREATE VIEW 视图名称
AS 查询语句
**创建视图**
- 在单表上创建视图
CREATE VIEW select_all_data_view(id,name,sex)
AS
SELECT id,name,sex FROM student3;
- 在多表上创建视图
CREATE VIEW select_all_students_view
AS
SELECT s.id as '学号',s.name as '姓名' , sex as '性别',
address as '地址' , c.name as '班级名' ,count as '班级人数'
FROM student3 s,class c
WHERE s.id = c.id
#### (2) 查看视图
| SQL语句 | 描述 |
|--|--|
| SHOW TABLES; | 查看数据库的表对象、视图对象 |
|DESC / DESCRIBE 视图名称;|查看视图的结构|
|SHOW TABLE STATUS LIKE '视图名称|表名' [\G]|查看视图的属性信息(显示数据表的存储引擎、版本、数据行数和数据大小等)|
|SHOW CREATE VIEW 视图名称;|查看视图的详细定义信息|
|SELECT * FROM 视图名;|查询视图执行结果,将视图当成表来操作即可|
练习:
查看数据库的表对象、视图对象
SHOW TABLES;
查看视图的结构
DESC select_all_students_view;
DESCRIBE select_all_students_view;
查看视图的属性信息
SHOW TABLE STATUS LIKE 'select_all_students_view' \G;
SHOW TABLE STATUS LIKE 'student3' \G;
查看视图的详细定义信息
SHOW CREATE VIEW select_all_students_view;
**`DESC select_all_students_view;`** 执行结果如下:
上图列名解释:
|列名|解释|
|--|--|
NULL |表示该列是否可以存储NULL值
Key |表示该列是否已经编制索引
Default |表示该列是否为默认值
Extra | 表示获得到的与给定列相关的附加信息
**`SHOW TABLE STATUS LIKE 'select_all_students_view' \G;`** 执行结果如下(Navicat不支持/G):
使用DOS进行查询,执行结果如下:
执行结果解释:
> 执行结果显示，Comment为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表,如果Comment为空说明是表不是视图。
**`SHOW TABLE STATUS LIKE 'student3' \G;`** 执行结果如下:
#### (3) 修改视图
**使用 CREATE OR REPLACE VIEW 语句修改视图**
> 如果视图存在,更新视图,如果视图不存在创建一个视图。
语法格式:
CREATE OR REPLACE VIEW
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(column_list)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]
练习:
CREATE OR REPLACE
VIEW select_all_students_view
AS
SELECT s.id as '学号',s.name as '姓名' , sex as '性别'
FROM student3 s,class c
WHERE s.id = c.id;
SELECT * FROM select_all_students_view;
运行效果:
**使用ALTER语句修改视图**
语法格式:
ALTER
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(column_list)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]
练习:
修改select_all_students_view视图内容
ALTER
VIEW select_all_students_view
AS
SELECT s.id as '学号',s.name as '姓名' , sex as '性别',address as '地址' , c.name as '班级名' ,count as '班级人数'
FROM student3 s,class c
WHERE s.id = c.id
# 查询select_all_students_view视图内容
SELECT * FROM select_all_students_view;
运行效果:
#### (4) 更新视图(把视图当成表来处理)
> 更新视图,视图中的基本表也会发生改变。
当视图包含如下内容时,视图的更新操作将不能被执行:
1. 视图中包含基本表中被定义为非空的列。
2. 在定义视图的SELECT语句后的字段列表中使用了数学表达式。
3. 在定义视图的SELECT语句后的字段列表中使用了聚合函数。
4. 在定义视图的SELECT语句中使用了 DISTINCT,UNION,TOP,GROUP BY或HAVING字句
**使用UPDATE语句更新视图**
> 使用UPDATE语句更新视图更新后相关的基本表也会发生变化。
**练习:**# 查询select_all_students_view视图内容【修改前】
SELECT * FROM select_all_students_view WHERE 姓名 = '马云';
# 修改马云班级的人数为1人
UPDATE select_all_students_view SET 班级人数 = 1 WHERE 姓名 = '马云';
# 查询select_all_students_view视图内容【修改后】
SELECT * FROM select_all_students_view WHERE 姓名 = '马云';
更新前:
更新后:
**`备注:基本表中的数据也发生了变化。如下所示`**
**使用INSERT语句更新视图**
> 使用INSERT语句，主要作用在基本表上,往基本表中插入数据,因为基本表中数据发生变化视图中也会发生变化,从而更新了视图中的数据。
**练习:**INSERT INTO class VALUES(6,'6班',30);
SELECT * FROM select_all_students_view;
运行结果:
**使用DELETE语句更新视图**
> 使用DELETE语句删除视图中部分记录从而更新视图,并且基本表也会更新数据。
**练习:**
# 创建视图student_view
CREATE VIEW student_view
AS
SELECT * FROM student3;
# 更新前查询视图
SELECT * FROM student_view;
DELETE FROM student_view WHERE id = 8;
# 更新后查询视图
SELECT * FROM student_view;
运行效果:
更新前:
更新后:
#### (5) 删除视图
> 删除视图只是删除视图的定义，并不会删除基表的数据。
**语法格式:**
DROP VIEW [IF EXISTS]
视图名,[视图名]....
**练习:**
DROP VIEW IF EXISTS
student_view,select_all_students_view
运行效果:
查询基本表:
SELECT * from class;
SELECT * FROM student3;
运行效果( **`删除视图并不影响视图中的基本表`** ):
### 11.触发器
> MySQL从 5.0.2 版本开始支持触发器。MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。触发器是由 事件来触发 某个操作，这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会 自动 激发触发器执行相应的操作。当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。
#### (1) 创建触发器
**语法格式:**
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;
说明：
|参数 | 解释 |
|--|--|
表名 |表示触发器监控的对象
BEFORE|AFTER |表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发
INSERT | UPDATE | DELETE |表示触发的事件
INSERT |表示插入记录时触发
UPDATE |表示更新记录时触发
DELETE |表示删除记录时触发
**练习:**
1、创建数据表：DROP TABLE IF EXISTS test_trigger;
CREATE TABLE test_trigger (
id INT PRIMARY KEY AUTO_INCREMENT,
t_note VARCHAR(30)
)DEFAULT CHARACTER SET utf8;
DROP TABLE IF EXISTS test_trigger_log;
CREATE TABLE test_trigger_log (
id INT PRIMARY KEY AUTO_INCREMENT,
t_log VARCHAR(30)
)DEFAULT CHARACTER SET utf8;
2、创建触发器：创建名称为before_insert的触发器，向test_trigger数据表插入数据之前，向
test_trigger_log数据表中插入before_insert的日志信息。
DELIMITER //
CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN
INSERT INTO test_trigger_log (t_log)
VALUES('执行了INSERT INTO语句');
END //
DELIMITER ;
3、向test_trigger数据表中插入数据
INSERT INTO test_trigger (t_note) VALUES ('测试 BEFORE INSERT 触发器');
4、查看test_trigger_log数据表中的数据
SELECT * FROM test_trigger_log;
运行效果:
#### (2) 查看触发器
| SQL语句| 说明|
|--|--|
| SHOW TRIGGERS[\G] ;| 查看当前数据库的所有触发器的定义|
|SHOW CREATE TRIGGER 触发器名;|查看当前数据库中某个触发器的定义
|SELECT * FROM information_schema.TRIGGERS;|从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。|
**练习:**
# 查看当前数据库的所有触发器的定义
SHOW TRIGGERS;
# 查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER before_insert;
#系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息
SELECT * FROM information_schema.TRIGGERS;
#### (3) 删除触发器
**语法格式:**
DROP TRIGGER IF EXISTS 触发器名称;
**练习:**
DROP TRIGGER IF EXISTS before_insert;
运行效果:
### 12.用户管理
#### (1) 创建用户
**使用CREATE USER 语句创建用户**
> 使用CREATE USER 语句需要在mysql数据库下，才能够创建用户,并且创建的新用户没有任何权限。
语法格式:
CREATE USER 用户名 IDENTIFIED BY '密码'];
参数解释:
| 参数 |解释 |
|--|--|
用户名 |表示新建用户的账户，由 用户（User） 和 主机名（Host） 构成；
“[ ]” |表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用可以直接登录。不过，不指定密码的方式不安全，不推荐使用。如果指定密码值，这里需要使用
IDENTIFIED BY |指定明文密码值。
CREATE USER语句 |表示可以同时创建多个用户。
**练习:**
- 创建用户zhang3 ,密码123123
CREATE USER zhang3 IDENTIFIED BY '123123'; # 默认host是 %
- 创建用户lisi密码123456
CREATE USER 'lisi'@'localhost' IDENTIFIED BY '123456';
**使用INSERT语句创建用户**
> 创建用户实际上都是在往mysql数据库中的user表中添加记录,所以使用INSERT语句也可以用来创建用户。
语法格式:
INSERT INTO mysql.user(Host,User,Password, ssl_cipher,x509_issuer,x509_subject)
VALUES('ip地址','账号',PASSWORD('密码'),'','','');
**`备注:PASSWORD()函数是加密函数,对明码进行加密操作。`**
如下图所示, ssl_cipher,x509_issuer,x509_subject都没有默认值所以插入记录的时候需要给初始值:
**`注意:数据插入后还需要刷新权限表`**
FLUSH PRIVILEGES;
**练习:**
- 创建用户wangmazhi,密码123456
INSERT INTO mysql.user(Host,User,Password, ssl_cipher,x509_issuer,x509_subject)
VALUES('localhost','wangmazhi',PASSWORD('123456'),'','','');
执行结果:
- 刷新权限表
FLUSH PRIVILEGES;
运行效果:
**使用GRANT语句创建用户**
> GRANT语句创建用户，还可以对用户进行授权,该语句会自动加载权限表,不需要手动刷新，而且安全,准确,错误少,因此,使用使用GRANT语句创建用户是最常见的方法。
语法格式:
GRANT privileges ON database.table
TO 'username'@'hostname' [IDENTIFIED BY [PASSWORD] 'password']
[,'username'@'hostname' [IDENTIFIED BY [PASSWORD] 'password']]....
参数解释:
| 参数 | 解释 |
|--|--|
privileges |表示访用户且有的权限信息
database.table |表示新用户的权限范围表,可以在指定的数掘库.表上使用自己的权限
username |表示新用户的名称
hostname |表示主机名
password |表示新用户的密码
**练习:**
创建用户user,密码123456,并且对test数据库中的test表有查询权限
GRANT SELECT ON test.test
TO 'user'@'localhost' IDENTIFIED BY '123456';
运行效果:
#### (2) 修改用户
**修改root用户密码**
> 修改root用户密码有3种方式,方式1使用mysqladmin命令修改root用户密码,mysqladmin命令通常用于执行一些管理性的工作,以及显示服务器状态等,方式2使用UPDATE语句修改密码,前面说到用户信息存放在mysql.user下,在root用户情况下直接通过UPDATE语句对密码进行修改,方式3使用SET语句进行修改。
语法格式:
方式1:mysqladmin -u 账号[-h 主机] -p password 新密码;
方式2( **`不推荐`** ):
UPDATE mysql.user set Password=PASSWORD('新密码') WHERE User = '用户名';
**`注意:数据修改后还需要刷新权限表`**
FLUSH PRIVILEGES;
方式3:
SET PASSWORD = PASSWORD('新密码'); # 修改root用户
SET PASSWORD FOR 'username'@'hostname'= PASSWORD('新密码'); # 修改指定用户
**练习:**
- 方式1修改密码
mysqladmin -u root -p password 123456
- 方式2修改密码
UPDATE mysql.user set Password=PASSWORD('root') WHERE User = 'root'
FLUSH PRIVILEGES;
- 方式3修改密码
SET PASSWORD = PASSWORD('123456');
**root用户普通用户修改密码**
> root用户修改普通密码有三种方式,前2种方式分别是UPDATE语句和SET语句,由于前面已经讲解了,这里就不在讲解了,接下来主要说明第三种方式使用GRANT语句。
方式3:
GRANT USAGE ON . TO 'username'@'localhost' IDENTIFIED BY [PASSWORD] '新密码';
**练习:**
GRANT USAGE ON . TO 'lisi'@'localhost' IDENTIFIED BY '654321';
#### (3) 删除用户(普通用户)
**使用DROP方式删除（推荐）**
> 使用DROP USER语句来删除用户时，必须用于DROP USER权限。
语法格式:
DROP USER user[,user]…;
**练习:**
- 删除用户zhang3
DROP USER zhang3;
**使用DELETE方式删除**
> 使用DELETE方式删除就是将mysql.user表中记录移除,所以这种方式需要刷新权限表。
语法格式:DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;
FLUSH PRIVILEGES;
**练习:**
- 删除用户lisi
DELETE FROM mysql.user WHERE User='lisi';
FLUSH PRIVILEGES;
### 13.授权管理(数据库DCL常用命令)
#### (1) 用户权限相关概念
**权限列表**
> 用户权限可通过 **`show privileges;`** 命名进行查询。
使用 show privileges; 命令查询权限列表:
权限解释:
| 权限 | 说明|
|--|--|
CREATE和DROP|可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将
DROP|授予某用户，用户就可以删除MySQL访问权限保存的数据库。
SELECT、INSERT、UPDATE和DELETE |允许在一个数据库现有的表上实施操作。
SELECT|只有在它们真正从一个表中检索行时才被用到。
INDEX |允许创建或删除索引，INDEX适用于已有的表。如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义。
ALTER |可以使用ALTER TABLE来更改表的结构和重新命名表。
CREATE ROUTINE |用来创建保存的程序（函数和程序）
ALTER ROUTINE|用来更改和删除保存的程序
EXECUTE |用来执行保存的程序。
GRANT |允许授权给其他用户，可用于数据库、表和保存的程序。
FILE |使用户可以使用LOAD DATA INFILE和SELECT ... INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。
**授予权限的原则**
权限控制主要是出于安全因素，因此需要遵循以下几个 经验原则 ：
1. 只授予能 满足需要的最小权限 ，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可
以了，不要给用户赋予update、insert或者delete权限。
2. 创建用户的时候 限制用户的登录主机 ，一般是限制成指定IP或者内网IP段。
3. 为每个用户 设置满足密码复杂度的密码 。
4. 定期清理不需要的用户 ，回收权限或者删除用户
#### (2) 授予权限
> 用户是数据库的使用者，我们可以通过给用户授予访问数据库中资源的权限，来控制使用者对数据库的访问，消除安全
隐患。
语法规则:
GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];
**`备注:该权限如果发现没有该用户，则会直接新建一个用户。`**
**练习:**
- 给user用户用本地命令行方式，授予test这个库下的所有表的插删改查的权限
GRANT SELECT,INSERT,DELETE,UPDATE ON test.* TO user@localhost ;
- 授予通过网络方式登录的joe用户 ，对所有库所有表的全部权限，密码设为123。注意这里唯独不包括grant的权限
GRANT ALL PRIVILEGES ON . TO joe@'%' IDENTIFIED BY '123';
#### (3) 查看权限
**查看当前用户权限**
SHOW GRANTS;
或
SHOW GRANTS FOR CURRENT_USER;
或
SHOW GRANTS FOR CURRENT_USER();
**查看某用户的全局权限**
SHOW GRANTS FOR 'user'@'主机地址' ;
**练习:**
- 查看当前用户权限
SHOW GRANTS;
- 查看user用户的全局权限
SHOW GRANTS FOR 'user'@'localshot' ;
#### (4) 收回权限
> 收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。MySQL中使用 REVOKE语句 取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从db,host,tables_priv和columns_priv表中删除，但是用户账户记录仍然在user表中保存（删除user表中的账户记录使用DROP USER语句,但是在将用户账户从user表删除之前，应该收回相应用户的所有权限。）。
语法格式:
REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
**练习:**
- 收回joe用户全库全表的所有权限
REVOKE ALL PRIVILEGES ON . FROM joe@'%';
- 收回user用户test库下的所有表的插删改查权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON test.* FROM user@localhost;
**`注意： 须用户重新登录后才能生效`**
### 14.角色管理
> 在 **`Mysql8.0`** 引入角色管理的目的是方便管理拥有相同权限的用户 。恰当的权限设定，可以确保数据的安全性，这是至关重要的。
角色管理部分的操作如下:
| 操作| 语法格式 |说明 |
|--|--|--|
| 创建角色 | CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]... |角色名称的命名规则和用户名类似。如果 host_name省略，默认为% ， role_name不可省略 ，不可为空。|
|给角色赋予权限|GRANT privileges ON table_name TO 'role_name'[@'host_name'];|语句中privileges代表权限的名称，多个权限以逗号隔开。可使用SHOW语句查询权限名称|
|查看角色的权限|SHOW GRANTS FOR '角色名';|只要你创建了一个角色，系统就会自动给你一个“ USAGE ”权限，意思是 连接登录数据库的权限 。|
|回收角色的权限|REVOKE privileges ON tablename FROM 'rolename';|privileges 权限名称;tablename表名,需要对哪一张收回权限;rolename角色名|
|删除角色|DROP ROLE role [,role2]...|注意,如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限 。|
|给用户赋予角色|GRANT role [,role2,...] TO user [,user2,...];|role代表角色，user代表用户。可将多个角色同时赋予多个用户，用逗号隔开即可。|
|激活角色|SET DEFAULT ROLE ALL TO '用户名'@'localhost';|给用户授权后,用户的权限需要激活|
|激活角色|SET GLOBAL activate_all_roles_on_login=ON;|当activate_all_roles_on_login为OFF时需要手动激活,当为ON时，用户赋予角色后角色就会激活|
|查看activate_all_roles_on_login的值|show variables like 'activate_all_roles_on_login';|默认为OFF|
|撤销用户的角色|REVOKE role FROM user;|role为角色;user为用户|
**`注意下面练习未做测试`**
**练习:**
- 创建角色manager
CREATE ROLE 'manager'@'localhost';
- 给角色manager赋予SELECT权限,对test.*(test数据库下所有表有效)
GRANT SELECT ON test.*TO 'manager';
- 查看角色manager的权限
SHOW GRANTS FOR 'manager';
- 给user用户添加manager角色
GRANT 'manager' TO 'user'@'localhost';
- 激活用户权限
> 如果用户将activate_all_roles_on_login设置为ON,可以不用激活。SET DEFAULT ROLE ALL TO 'user'@'localhost';
- 设置activate_all_roles_on_login设置为ON
SET GLOBAL activate_all_roles_on_login=ON;
- 查看ctivate_all_roles_on_login的值
show variables like 'activate_all_roles_on_login';
- 回收manager角色的SELECT权限
REVOKE SELECT ON test.* FROM 'manager';
- 撤销user用户的角色manager
REVOKE 'manager' FROM 'user'@'localhost';
- 删除角色manager
DROP ROLE manager;
