菜鸟教程学习链接:https://www.runoob.com/MySQL/MySQL-tutorial.html
C语言中文网:数据库入门 (biancheng.net)
数据库和表的创建、修改、删除,数据的导入。
数据查询:多表查询,分组查询等
mysql的常用内置函数
约束:实体完整性约束和参照完整性约束
用户的添加、查看、删除、信息修改;并发控制,事务,隔离
MySQL安装
windows
- zip压缩包安装:https://blog.csdn.net/a802976/article/details/119255644
- msi安装程序安装:https://blog.csdn.net/zqh529/article/details/114734950
启动和停止MySQL服务(cmd.exe管理员运行)
1 | net start mysql |
登录MySQL数据库
1 | mysql -u root –p |
输入密码即可出现欢迎界面
Linux
1) 准备一台Linux服务器
云服务器或者虚拟机都可以;Linux的版本为 CentOS7;
2) 下载Linux版MySQL安装包
https://downloads.mysql.com/archives/community/
3) 上传MySQL安装包
4) 创建目录,并解压
1 | mkdir mysql |
5) 安装mysql的安装包
1 | cd mysql |
6) 启动MySQL服务
1 | systemctl start mysqld |
7) 查询自动生成的root用户密码
1 | grep 'temporary password' /var/log/mysqld.log |
命令行执行指令 :
1 | mysql -u root -p |
然后输入上述查询到的自动生成的密码, 完成登录 .
8) 修改root用户密码
登录到MySQL之后,需要将自动生成的不便记忆的密码修改了,修改成自己熟悉的便于记忆的密码。
1 | ALTER USER 'root'@'localhost' IDENTIFIED BY '1234'; |
执行上述的SQL会报错,原因是因为设置的密码太简单,密码复杂度不够。我们可以设置密码的复杂度为简单类型,密码长度为4。
1 | set global validate_password.policy = 0; |
降低密码的校验规则之后,再次执行上述修改密码的指令。
9) 创建用户
默认的root用户只能当前节点localhost访问,是无法远程访问的,我们还需要创建一个root账户,用户远程访问
1 | create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '1234'; |
10) 并给root用户分配权限
1 | grant all on *.* to 'root'@'%'; |
11) 重新连接MySQL
1 | mysql -u root -p |
然后输入密码
12) 通过DataGrip远程连接MySQL
MySQL基本命令
在描述命令格式时,用[ ]表示可选项
MySQL命令不区分大小写,为了读者阅读方便,在描述命令格式和命令实例时,命令关键字用大写表示,其他用小写表示。但在实际对MySQL操作时为了避免大小写频繁切换,一般都用小写。
命令关键字可以只写前面4个字符。DESCRIBE user;
与 DESC user;
效果是一样的。
在MySQL中,服务器处理语句的时候是以分号为结束标志的。使用DELIMITER
命令可以将MySQL语句的结束标志修改为其他符号。
1 | /*以\\结束*/ |
1)查看MySQL系统的已有的数据库:
1 | SHOW DATABASES; |
2)进入某个数据库,切换数据库:
1 | USE database_name; |
3)查看当前数据库:
1 | SELECT database(); |
4)显示当前数据库中所有表的名称
1 | SHOW tables; |
5)显示创建基本表的语句:(即创建这个基本表时所用的语句)
1 | SHOW create table table_name; |
6)显示表中所有列名:
1 | /*在选择进入了某个数据库之后,可以不指定数据库名称,否则按后两行格式来写*/ |
7)显示一个用户的权限:
1 | show grants for user_name; |
8)显示一些系统特定资源的信息,例如,正在运行的线程数量。
1 | SHOW status; |
9)显示系统变量的名称和值:
1 | SHOW GLOBAL VARIABLES; # 查看 MySQL 中所有的全局变量信息 |
10)DESCRIBE
语句用于显示表中各列的信息,语法格式:
1 | {DESCRIBE | DESC} 表名 [列名 | wild]; |
也可以使用explain table_name\G
来显示表信息
11)查看所有数据库
1 | SHOW DATABASES; # 列出当前用户可查看的所有数据库 |
上面的表都是安装 MySQL 时系统自动创建
- information_schema:主要存储了系统中的一些数据库对象信息,比如用户表信息、列信息、权限信息、字符集信息和分区信息等。
- mysql:MySQL 的核心数据库,类似于 SQL Server 中的 master 表,主要负责存储数据库用户、用户访问权限等 MySQL 自己需要使用的控制和管理信息。常用的比如在 mysql 数据库的 user 表中修改 root 用户密码。
- performance_schema:主要用于收集数据库服务器性能参数。
- sys:MySQL 5.7 安装完成后会多一个 sys 数据库。sys 数据库主要提供了一些视图,数据都来自于 performation_schema,主要是让开发者和使用者更方便地查看性能问题。
建库建表
创建数据库
1 | CREATE DATABASE [IF NOT EXISTS] 数据库名 [选项...]; |
修改数据库
1 | ALTER DATABASE [数据库名] 选项... ; |
删除数据库
1 | DROP DATABASE [IF EXISTS] database_name; |
注:除列名区分大小写,数据库名、表名等,以及create
、delete
等既可大写也可小写
创建表
全新创建
1 | CREATE TABLE [IF NOT EXISTS] table_name |
复制现成的表
1 | CREATE TABLE [IF NOT EXISTS] table_name |
修改表
ALTER TABLE用于更改原有表的结构。例如,可以增加(删减)列、创建(取消)索引、更改原有列的类型、重新命名列或表,还可以更改表的评注和表的类型。语法格式如下:
1 | ALTER [IGNORE] TABLE table_name 选项...; |
直接修改表名:
1 | RENAME TABLE 旧表名 TO 新表名 ...; |
删除表
1 | DROP TABLE [IF EXISTS] 表名1[,表名2,...] |
1 | TRUNCATE TABLE 表名; # 删除表并重新创建 |
文件导入数据
注意:后面向表中导入本地文件所存储的数据时,文件需完整路径名,且文件路径名不能有中文和空格,路径用/不能用\。谨慎避坑。
同时导入数据可能遇到
The MySQL server is running with the --secure-file-priv option so it cannot execute
需要如下修改my.ini文件
在mysql安装文件夹下的my.ini文件中修改或添加以下内容,之后重启mysql
1 | [mysqld] |
secure-file-priv的值有三种情况:
1 | secure_file_prive=null ––限制mysqld 不允许导入导出 |
详情见:https://blog.csdn.net/weixin_44595372/article/details/88723191
1 | /*想在table中导入csv文件中的data*/ |
插入记录
插入一个全新记录到表里面,使用INSERT命令,语法格式如下:
1 | INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] |
从已有表中选择记录到当前表中,语法格式如下:
1 | INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] |
修改记录
修改基本表中的记录,使用UPDATE命令,语法格式如下:
1 | UPDATE [LOW_PRIORITY] [IGNORE] 表名 |
删除记录
删除基本表中的记录,使用DELETE命令,语法格式如下:
1 | DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM 表名 |
1 | /*删除表table*/ |
1 | /*向表中插入数据*/ |
DQL
单表查询、分组统计查询和连接查询,视图的创建,删除等
- 基本查询
- 条件查询(WHERE)
- 聚合查询(count、max、min、avg、sum)
- 分组查询(GROUP BY)
- 排序查询(ORDER BY)
- 分页查询(LIMIT)
SELECT语句
1 | SELECT [ALL | DISTINCT | DISTINCTROW ][HIGH_PRIORITY]... |
聚合函数
通常与GROUP BY子句一起使用:
函 数 名 | 说 明 |
---|---|
COUNT | 求组中项数,返回int类型整数 |
MAX | 求最大值 |
MIN | 求最小值 |
SUM | 返回表达式中所有值的和 |
AVG | 求组中值的平均值 |
STD或STDDEV | 返回给定表达式中所有值的标准差 |
VARIANCE | 返回给定表达式中所有值的方差 |
GROUP_CONCAT | 返回由属于一组的列值连接组合而成的结果 |
BIT_AND | 逻辑或 |
BIT_OR | 逻辑与 |
BIT_XOR | 逻辑异或 |
查询示例
1 | /*下面都是例子例*/ |
SQL查询语法要点
通配符 * 可以表示所有字段
可以使用+ - * / 数学表达式,列与列之间的运算
AS 别名
select id as 'ID' from student;
ORDER BY 排序
1 | 默认升序ASC |
DISTINCT 去重
需放在第一个字段名之前,否则报错
作用于后面跟着的所有字段
WHERE + 条件
功能 | 比较运算符 |
---|---|
大于 | > |
小于 | < |
等于 | = |
不等于 | != 或 <> |
大于等于 | >= |
小于等于 | <= |
某个范围(含左右边界) | BETWEEN … AND … |
in列表中的值,多选一 | IN(…) |
模糊匹配(_ 单个字符, % 任意各字符) |
LIKE 占位符 |
是NULL/不是NULL | IS NULL 或 IS NOT NULL |
功能 | 逻辑运算符 | ||
---|---|---|---|
并且(多个条件同时成立) | AND 或 && | ||
或者 | OR 或 \ | \ | |
非,不是 | NOT 或 ! |
1 | WHERE age>12 AND (gender='男' OR gender='女') |
五大聚类函数
COUNT()
- COUNT(*)统计所有行数,包括列中是NULL的行和重复的行
- COUNT(X字段)统计X列中不为NULL的行数
- COUNT(DISTINCT X)统计X列中不为NULL的行数,并去重
MAX()
- eg:
SELECT account_id,MAX(total_price) AS max_price
- MAX()找最大的数值或日期值
- MAX()函数忽略列值为NULL的行
- 在MySQL中,可以用于文本列字母排序的最高值
MIN()和MAX()类似
SUM()
- 返回指定列值的和
- 必须指定列名如:
SUM(total_price)
- 忽略列值为NULL的行
- eg:
SELECT SUM(total_price)/50000 AS ROI
AVG()
- 计算数值列的平均值
- 忽略列值为NULL的行
- eg:
SELECT AVG(total_price) AS avg_price
GROUP BY 分组
1 | # 根据性别分组,统计男性员工 和 女性员工的数量 |
分组之后,查询的字段一般为聚合函数和分组字段,查询其它字段无任何意义
HAVING和WHERE
where和having的区别:
执行时机不同:
- where是分组之前进行过滤,过滤行
- having是对分组之后的结果过滤,过滤组
判断条件不同:where不能对聚合函数进行判断,而having可以
子查询
子查询就是将一个查询(子查询)的结果作为另一个查询(主查询)的数据来源或判断条件的查询
1 | /*例*/ |
ANY:子查询结果至少存在一个满足
1 | /*例*/ |
ALL:子查询结果所有均要满足
1 | /*例*/ |
内联结(连接)JOIN ON
INNER JOIN 内联结(INNER可省略)
ON 设定联结键的条件
1 | /*例:将商品名与品牌表的数据联结,并筛选出所有的商品编号、商品名、品牌名*/ |
外联结(连接)
左外联结:LEFT JOIN
右外联结:RIGHT JOIN
limit
1 | SELECT ... FROM ... LIMIT 起始索引,查询记录数; |
- 起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL是LIMIT
- 如果查询的是第一页数据,起始索引可以省略
1 | select .. .. limit 4; /*取最后结果的前四行*/ |
SQL书写顺序
SQL执行顺序
使用AS关键字时,执行顺序这一点会有体现,尤其注意!
注:字符串加单引号’’,字段名即列名在特殊情况下用 `` (英文键盘下Tab上面的键)包裹
注:多个表连接时的格式:
1 | select xxx |
多表关系
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结 构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
- 一对多(多对一)
- 多对多
- 一对一
一对多
- 案例: 部门 与 员工的关系
- 关系: 一个部门对应多个员工,一个员工对应一个部门
- 实现: 在多的一方建立外键,指向一的一方的主键
多对多
- 案例: 学生 与 课程的关系
- 关系: 一个学生可以选修多门课程,一门课程也可以供多个学生选择
- 实现: 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
1 | create table student( |
一对一
- 案例: 用户 与 用户详情的关系
- 关系: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
- 实现: 在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
1 | create table tb_user( |
多表查询
数据准备
1 | -- 准备数据 |
分类
多表查询就是指从多张表中查询数据。执行多表查询,就只需要使用逗号分隔多张表即可,
如:select * from emp , dept;
,查询结果中包含了大量的结果集,总共102条记录,而这其实就是员工表emp所有的记录(17) 与 部门表dept所有记录(6) 的所有组合情况,这种现象称之为笛卡尔积。
笛卡尔积: 笛卡尔乘积是指在数学中,两个集合A集合 和 B集合的所有组合情况。
而在多表查询中,我们是需要消除无效的笛卡尔积的,只保留两张表关联部分的数据。
我们可以给多表查询加上连接查询的条件即可。select * from emp , dept where emp.dept_id = dept.id;
分类:
- 连接查询
- 内连接:相当于查询A、B交集部分数据
- 外连接:
- 左外连接:查询左表所有数据,以及两张表交集部分数据
- 右外连接:查询右表所有数据,以及两张表交集部分数据
- 自连接:当前表与自身的连接查询,自连接必须使用表别名
- 子查询
内连接
内连接的语法分为两种: 隐式内连接、显式内连接。
1 | -- 隐式内连接 |
查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现):
1 | select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ; |
查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现):
1 | select e.name, d.name from emp e inner join dept d on e.dept_id = d.id; |
外连接
外连接分为两种,分别是:左外连接 和 右外连接。
1 | -- 左外连接 |
- 左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。
- 右外连接相当于查询表2(右表)的所有数据,当然也包含表1和表2交集部分的数据。
查询emp表的所有数据, 和对应的部门信息:
1 | select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id; |
查询dept表的所有数据, 和对应的员工信息(右外连接):
1 | select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id; |
注意事项:
左外连接和右外连接是可以相互替换的,只需要调整在连接查询时SQL中,表结构的先后顺序就可以了。而我们在日常开发使用时,更偏向于左外连接。
自连接
自连接查询,顾名思义,就是自己连接自己,也就是把一张表连接查询多次。
1 | SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ; |
而对于自连接查询,可以是内连接查询,也可以是外连接查询。
1 | -- 查询员工 及其 所属领导的名字: |
注意事项:
在自连接查询中,必须要为表起别名,要不然我们不清楚所指定的条件、返回的字段,到底是哪一张表的字段。
联合查询
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
1 | SELECT 字段列表 FROM 表A ... |
- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
- union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
1 | -- 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来: |
注意:
如果多条查询语句查询出来的结果,字段数量不一致,在进行union/union all联合查询时,将会报错。
子查询
SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
1 | SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 ); |
子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。
根据子查询结果不同,分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
根据子查询位置,分为:
- WHERE之后
- FROM之后
- SELECT之后
标量子查询
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询。
常用的操作符:= <> > >= < <=
1 | -- 查询 "销售部" 的所有员工信息 |
列子查询
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用的操作符:IN 、NOT IN 、 ANY 、SOME 、 ALL
操作符 | 描述 |
---|---|
IN | 在指定的集合范围之内,多选一 |
NOT IN | 不在指定的集合范围之内 |
ANY | 子查询返回列表中,有任意一个满足即可 |
SOME | 与ANY等同,使用SOME的地方都可以使用ANY |
ALL | 子查询返回列表的所有值都必须满足 |
1 | -- 查询 "销售部" 和 "市场部" 的所有员工信息 |
行子查询
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。
常用的操作符:= 、<> 、IN 、NOT IN
1 | -- 查询与 "张无忌" 的薪资及直属领导相同的员工信息 |
表子查询
子查询返回的结果是多行多列,这种子查询称为表子查询。
常用的操作符:IN
1 | -- 查询与 "鹿杖客","宋远桥" 的职位和薪资相同的员工信息 |
多表查询案例
在多表查询的数据基础上,添加下面的表,共:emp员工表、dept部门表、salgrade薪资等级表
1 | create table salgrade( |
1 | -- 查询员工的姓名、年龄、职位、部门信息 (隐式内连接) |
DCL
管理用户
创建用户
使用CREATE USER
命令创建用户并设置密码,语法格式如下:
1 | CREATE USER 用户 [IDENTIFIED BY'password']; |
查看用户
1 | /*使用以下命令查看所有用户*/ |
其中 Host 代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问,是不可以远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户。
删除用户
使用DROP USER
命令删除用户,语法格式如下:
注:只有用户当前数据库全局权限的用户才能使用DROP
命令
1 | DROP USER 用户; |
修改用户信息
修改用户密码:
使用SET PASSWORD
命令修改某用户登录密码,语法格式如下:
1 | /*8.0版本之前*/ |
也可以使用以下命令修改密码
1 | ALTER USER 'ALEX'@'localhost' IDENTIFIED BY '123'; |
还可以使用update
1 | UPDATE user SET password=PASSWORD('123456') WHERE user='root'; |
修改用户名:
1 | /*相当于修改mysql.user这张存有用户信息的表,将ALEX修改为alex*/ |
注意事项:
1、在MySQL中需要通过用户名@主机名的方式,来唯一标识一个用户。
2、主机名可以使用 % 通配。
3、这类SQL开发人员操作的比较少,主要是DBA( Database Administrator 数据库管理员)使用。
权限控制
MySQL中定义了很多种权限,但是常用的就以下几种:
权限 | 说明 |
---|---|
ALL, ALL PRIVILEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
CREATE | 创建数据库/表 |
上述只是简单罗列了常见的几种权限描述,其他权限描述及含义,可以直接参考官方文档。
授予权限
可授予用户列权限、表权限、数据库权限、用户权限等,语法格式如下:
1 | GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名' |
注:若指定密码,则原密码将会被覆盖,如果权限授予一个不存在的用户,则MySQL会自动创建这个用户,但必须为该用户指定密码。
多个权限之间,使用逗号分隔;授权时, 数据库名和表名可以使用 * 进行通配,代表所有;如果要授予所有权限,则用ALL
或者ALL PRIVILEGES
表示。
WITH GRANT OPTION
:若加上了WITH GRANT OPTION子句,获得某种权限的用户还可以把这种权限再授予其他的用户。
1 | /*例*/ |
查看权限
1 | show grants for '用户名'@'主机名'; |
回收权限
使用REVOKE命令可以回收授予的权限,语法格式如下:
1 | REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'; |
权限一览表
语 句 | 用 户 权 限 | 数据库权限 | 表 权 限 | 列 权 限 |
---|---|---|---|---|
SELECT | Yes | Yes | Yes | No |
INSERT | Yes | Yes | Yes | No |
DELETE | Yes | Yes | Yes | Yes |
UPDATE | Yes | Yes | Yes | Yes |
REFERENCES | Yes | Yes | Yes | Yes |
CREATE | Yes | Yes | Yes | No |
ALTER | Yes | Yes | Yes | No |
DROP | Yes | Yes | Yes | No |
INDEX | Yes | Yes | Yes | Yes |
CREATE TEMPORARY TABLES | Yes | Yes | No | No |
CREATE VIEW | Yes | Yes | No | No |
SHOW VIEW | Yes | Yes | No | No |
CREATE ROUTINE | Yes | Yes | No | No |
ALTER ROUTINE | Yes | Yes | No | No |
EXECUTE ROUTINE | Yes | Yes | No | No |
LOCK TABLES | Yes | Yes | No | No |
CREATE USER | Yes | No | No | No |
SHOW DATABASES | Yes | No | No | No |
FILE | Yes | No | No | No |
PROCESS | Yes | No | No | No |
RELOAD | Yes | No | No | No |
REPLICATION CLIENT | Yes | No | No | No |
REPLICATION SLAVE | Yes | No | No | No |
SHUTDOWN | Yes | No | No | No |
SUPER | Yes | No | No | No |
USAGE | Yes | No | No | No |
函数
函数 是指一段可以直接被另一段程序调用的程序或代码。 也就意味着,这一段程序或代码在MySQL中已经给我们提供了,我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可。
MySQL中的函数主要分为以下四类: 字符串函数、数值函数、日期函数、流程函数。
字符串函数
MySQL中内置了很多字符串函数,常用的几个如下:
函数 | 功能 |
---|---|
CONCAT(S1,S2,…Sn) | 字符串拼接,将S1,S2,… Sn拼接成一个字符串 |
LOWER(str) | 将字符串str全部转为小写 |
UPPER(str) | 将字符串str全部转为大写 |
LPAD(str,n,pad) | 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度 |
RPAD(str,n,pad) | 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度 |
TRIM(str) | 去掉字符串头部和尾部的空格 |
SUBSTRING(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串 |
CAST(expr as type) | 将expr转化为type类型 |
使用示例:
1 | -- concat : 字符串拼接 |
数值函数
常见数值函数如下:
函数 | 功能 |
---|---|
CEIL(x) | 向上取整 |
FLOOR(x) | 向下取整 |
MOD(x,y) | 返回x/y的模(相除取余) |
RAND() | 返回0~1内的随机数 |
ROUND(x,y) | 求参数x的四舍五入的值,保留y位小数 |
1 | -- ceil:向上取整 |
通过数据库的函数,生成一个六位数的随机验证码
1 | select lpad(round(rand()*1000000, 0), 6, '0'); |
日期函数
常见的日期函数如下:
函数 | 功能 |
---|---|
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前日期和时间 |
YEAR(date) | 获取指定date的年份 |
MONTH(date) | 获取指定date的月份 |
DAY(date) | 获取指定date的日期 |
DATE_ADD(date, INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
DATEDIFF(date1,date2) | 返回起始时间date1 和 结束时间date2之间的天数 |
SEC_TO_TIME(sec) | 将秒值转换为时间格式 |
1 | -- curdate |
查询所有员工的入职天数,并根据入职天数倒序排序:
1 | select name, datediff(curdate(), entrydate) as 'entrydates' from employee order by entrydates desc; |
流程函数
流程函数也是很常用的一类函数,可以在SQL语句中实现条件筛选,从而提高语句的效率。
函数 | 功能 |
---|---|
IF(value , t , f) | 如果value为true,则返回t,否则返回f |
IFNULL(value1 , value2) | 如果value1不为空,返回value1,否则返回value2 |
CASE WHEN [ val1 ] THEN [res1] … ELSE [ default ] END | 如果val1为true,返回res1,… 否则返回default默认值 |
CASE [ expr ] WHEN [ val1 ] THEN [res1] … ELSE [ default ] END | 如果expr的值等于val1,返回res1,… 否则返回default默认值 |
1 | -- if |
1 | -- 统计班级各个学员的成绩,展示规则如下: |
约束
概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据。
目的:保证数据库中数据的正确、有效性和完整性。
分类:
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | NOT NULL |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | DEFAULT |
检查约束(8.0.16版本之后) | 保证字段值满足某一个条件 | CHECK |
外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 | FOREIGN KEY |
注意:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
建表示例
案例需求: 根据需求,完成表结构的创建。需求如下:
字段名 | 字段含义 | 字段类型 | 约束条件 | 约束关键字 |
---|---|---|---|---|
id | ID唯一标识 | int | 主键,并且自动增长 | PRIMARY KEY, AUTO_INCREMENT |
name | 姓名 | varchar(10) | 不为空,并且唯一 | NOT NULL , UNIQUE |
age | 年龄 | int | 大于0,并且小于等于120 | CHECK |
status | 状态 | char(1) | 如果没有指定该值, 默认为1 | DEFAULT |
gender | 性别 | char(1) | 无 |
1 | -- 建表 |
外键约束
外键:用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性
示例:
左侧的emp表是员工表,里面存储员工的基本信息,包含员工的ID、姓名、年龄、职位、薪资、入职日期、上级主管ID、部门ID,在员工的信息中存储的是部门的ID dept_id,而这个部门的ID是关联的部门表dept的主键id,那emp表的dept_id就是外键,关联的是另一张表的主键。
注意:目前上述两张表,只是在逻辑上存在这样一层关系;在数据库层面,并未建立外键关联,所以是无法保证数据的一致性和完整性的
设置/删除外键
添加外键的方法
1 | -- 创建表的时候设置 |
删除外键
1 | ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; |
上图示例的数据:
1 | create table dept( |
1 | -- 为emp表的dept_id字段添加外键约束,关联dept表的主键id |
添加了外键约束之后,我们再到dept表(父表)删除id为1的记录,然后看一下会发生什么现象。 此时
将会报错,不能删除或更新父表记录,因为存在外键约束。
删除/更新行为
添加了外键之后,再删除父表数据时产生的约束行为,我们就称为删除/更新行为。具体的删除/更新行 为有以下几种:
行为 | 说明 |
---|---|
NO ACTION | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不 允许删除/更新。 (与 RESTRICT 一致) 默认行为 |
RESTRICT | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不 允许删除/更新。 (与 NO ACTION 一致) 默认行为 |
CASCADE | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则 也删除/更新外键在子表中的记录。 |
SET NULL | 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表 中该外键值为null(这就要求该外键允许取null)。 |
SET DEFAULT | 父表有变更时,子表将外键列设置成一个默认的值 (Innodb不支持) |
具体语法:
1 | ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE; |
实体完整性约束
定义主键约束
列级实体完整性
列定义时加上关键字
PRIMARY KEY
表级实体完整性
表定义时,在语句最后加上
PRIMARY KEY (col_name,…)
定义替代键约束
- 替代键是没有被选作主键的候选键,用
UNIQUE
来定义
注:CONSTRAINT
可以用来指定表完整性约束的名字
1 | CONSTRAINT key_name PRIMARY KEY(key) |
参照完整性约束
创建表时定义参照完整性
列级参照完整性(不会生效,仅用于备注)
- 列定义时加上关键字
REFERENCES ref_table_name(ref_key)
表级参照完整性
表定义时,在语句最后加上
CONSTRAINT ref_key_name FOREIGN KEY (ref_key) REFERENCES ref_table_name(ref_key)
参照动作
指定这个参照动作应用哪一条语句:这里有两条相关的语句,即UPDATE
和DELETE
语句;
指定采取哪个动作:可能采取的动作是RESTRICT、CASCADE、SET NULL、NO ACTION和SET DEFAULT
不同参照动作含义:
RESTRICT
:当要删除或更新父表中被参照列上在外键中出现的值时,拒绝对父表的删除或更新操作
CASCADE
:从父表删除或更新行时自动删除或更新子表中匹配的行
SET NULL
:当从父表删除或更新行时,设置子表中与之对应的外键列为NULL。如果外键列没有指定NOT NULL限定词,这就是合法的
NO ACTION
:NO ACTION意味着不采取动作,就是如果有一个相关的外键值在被参考的表里,删除或更新父表中主要键值的企图不被允许,和RESTRICT一样
SET DEFAULT
:作用和SET NULL一样,只不过SET DEFAULT是指定子表中的外键列为默认值
设置外键的时候需要注意以下几点:
- 外键是用于两个表的数据之间建立连接,可以是一列或者多列,即一个表可以有一个或多个外键。
- 这个表里面设置的外键必须是另外一个表的主键!
- 外键可以不是这个表的主键,但必须和另外一个表的主键相对应(字段的类型和值必须一样)。
- 带有主键的那张表称为父表,含外键的是子表,必须先删除外键约束才能删除父表。
并发控制
事务概念
事务由作为一个单独单元的一个或多个SQL语句组成。这个单元中的SQL语句是相互依赖的,整个单元是不可分割的。如果单元中的一个语句不能完成,整个单元就会回滚(撤销操作),所有影响到的数据将返回到事务开始以前的状态。(银行交易、网上购物等)
事务 是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
就比如:张三给李四转账1000块钱,张三银行账户的钱减少1000,而李四银行账户的钱要增加1000。 这一组操作就必须在一个事务的范围内,要么都成功,要么都失败
正常情况: 转账这个操作, 需要分为以下这么三步来完成 , 三步完成之后, 张三减少1000, 而李四增加1000, 转账成功 :
异常情况: 转账这个操作, 也是分为以下这么三步来完成 , 在执行第三步是报错了, 这样就导致张三减少1000块钱, 而李四的金额没变, 这样就造成了数据的不一致, 就出现问题了。
为了解决上述的问题,就需要通过数据的事务来完成,我们只需要在业务逻辑执行之前开启事务,执行完毕后提交事务。如果执行过程中报错,则回滚事务,把数据恢复到事务开始之前的状态。
注意: 默认MySQL的事务是自动提交的,也就是说,当执行完一条DML语句时,MySQL会立即隐式的提交事务
演示
数据准备
1 | drop table if exists account; |
未控制事务
1)测试正常情况
1 | -- 1. 查询张三余额 |
测试完毕之后检查数据的状态, 可以看到数据操作前后是一致的。
2)测试异常情况
1 | -- 1. 查询张三余额 |
我们把数据都恢复到2000, 然后再次一次性执行上述的SQL语句(出错了…. 这句话不符合SQL语法,执行就会报错),检查最终的数据情况, 发现数据在操作前后不一致了。
控制事务
方法一:
1 | -- 查看/设置事务提交方式 |
注意:上述的这种方式,我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。
方法二:
1 | -- 开启事务 |
转账示例:
1 | -- 开启事务 |
四大特性ACID
原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
上述就是事务的四大特性,简称ACID。
并发控制问题
丢失修改:两个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,因此最后的更新将重写由其他事务所做的更新,导致数据丢失
脏读:一个事务正在访问数据,而其他事务正在更新该数据,但尚未提交,此时会发生脏读问题,即第一个事务所读取的数据是“脏”的
不可重复读:在一个事务内多次读同一数据,在该事务还没有结束时,另外的事务也访问该数据并对其做修改,从而导致第一个事务两次读取的数据不一样
幻读:当一个事务对某行执行了插入或删除操作时,由于该行在另外的事务读取的范围内,从而导致两次读取多出了一行或者消失了一行
1)赃读:一个事务读到另外一个事务还没有提交的数据。
比如B读取到了A未提交的数据。
2)不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。
事务A两次读取同一条记录,但是读取到的数据却是不一样的。
3)幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了 “幻影”。
事务隔离级别
为了解决并发事务所引发的问题,在数据库中引入了事务隔离级别。四级隔离级别:定义用户之间隔离和交互的程度
读未提交(read uncommitted):可以读到其他事务还没有提交的数据,仅避免丢失修改,会导致大量数据变化,一级封锁协议
读已提交(read committed):可以看到其他事务添加的新纪录,而且其他事务对现存记录做出的修改一旦被提交,也可以看到,避免丢失修改和脏读,二级封锁协议
可重复读(repeatable read):当前在执行的事务的变化无法被看到,同一事务中执行
select数次结果都相同
,避免丢失修改、脏读和不可重复读,增强的二级封锁协议可串行化(serializable):用户之间一个接一个执行事务,避免所有并发控制问题,最大限度的隔离,三级封锁协议
注:MySQL默认为repeatable read
隔离级别
隔离级别 | 丢失修改 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|---|
读未提交 | X | √ | √ | √ |
读已提交 | X | X | √ | √ |
可重复读 | X | X | X | √ |
可串行化 | X | X | X | X |
1 | -- 查看事务隔离级别 |
注意:事务隔离级别越高,数据越安全,但是性能越低。
在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。
事务控制语句
1 | BEGIN 或 START TRANSACTION 显式地开启一个事务; |
MYSQL 事务处理主要有两种方法:
1、用 BEGIN
, ROLLBACK
, COMMIT
来实现
BEGIN
开始一个事务ROLLBACK
事务回滚COMMIT
事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0
禁止自动提交SET AUTOCOMMIT=1
开启自动提交
补充
注意:用户的”localhost”,是指该用户只能在本地登录,不能在另外一台机器上远程登录。如果想远程登录的话,将”localhost”改为”%”,表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录。
flush privileges;
刷新系统权限表
查看帮助
在 MySQL 中,查看帮助的命令是 HELP,语法格式如下:
1 | HELP 查询内容 # 查询内容为要查询的关键字 |
- 查询内容中不区分大小写。
- 查询内容中可以包含通配符“%”和“_”,效果与 LIKE 运算符执行的模式匹配操作含义相同。例如,
HELP 'rep%'
用来返回以 rep 开头的主题列表。 - 查询内容可以使单引号引起来,也可以不使用单引号,为避免歧义,最好使用单引号引起来。
示例:
1)查询帮助文档目录列表
2)查看具体内容
HELP 'Data Types;'
查看所支持的数据类型
进一步查看某一数据类型,如 INT 类型,可以使用 HELP INT;
命令,运行结果如下:
另外,还可以查询某命令,例如使用 HELP CREATE TABLE
命令查询创建数据表的语法,运行结果如下所示:
拓展
MySQL 提供了 4 张数据表来保存服务端的帮助信息,即使用 HELP 语法查看的帮助信息。执行语句就是从这些表中获取数据并返回给客户端的,MySQL 提供的 4 张数据表如下:
- help_category:关于帮助主题类别的信息
- help_keyword:与帮助主题相关的关键字信息
- help_relation:帮助关键字信息和主题信息之间的映射
- help_topic:帮助主题的详细内容
查看系统变量
1 | SHOW GLOBAL VARIABLES; # 查看 MySQL 中所有的全局变量信息 |
MySQL 中的系统变量以两个“@”开头。
@@global
仅仅用于标记全局变量;@@session
仅仅用于标记会话变量;@@
首先标记会话变量,如果会话变量不存在,则标记全局变量。
MySQL 中有一些系统变量仅仅是全局变量,例如 innodb_data_file_path
,可以使用以下 3 种方法查看:
1 | SHOW GLOBAL VARIABLES LIKE 'innodb_data_file_path'; |
MySQL 中有一些系统变量仅仅是会话变量,例如 MySQL 连接 ID 会话变量 pseudo_thread_id
,可以使用以下 2 种方法查看。
1 | SHOW SESSION VARIABLES LIKE 'pseudo_thread_id'; |
MySQL 中有一些系统变量既是全局变量,又是会话变量,例如系统变量 character_set_client
既是全局变量,又是会话变量。
1 | SHOW SESSION VARIABLES LIKE 'character_set_client'; |
此时查看全局变量的方法如下:
1 | SHOW GLOBAL VARIABLES LIKE 'character_set_client'; |
设置系统变量
可以通过以下方法设置系统变量:
- 修改 MySQL 源代码,然后对 MySQL 源代码重新编译(该方法适用于 MySQL 高级用户,这里不做阐述)。
- 在 MySQL 配置文件(mysql.ini 或 mysql.cnf)中修改 MySQL 系统变量的值(需要重启 MySQL 服务才会生效)。
- 在 MySQL 服务运行期间,使用 SET 命令重新设置系统变量的值。
服务器启动时,会将所有的全局变量赋予默认值。这些默认值可以在选项文件中或在命令行中对执行的选项进行更改。
更改全局变量,必须具有 SUPER 权限。设置全局变量的值的方法如下:
1 | SET @@global.innodb_file_per_table=default; |
需要注意的是,更改全局变量只影响更改后连接客户端的相应会话变量,而不会影响目前已经连接的客户端的会话变量(即使客户端执行 SET GLOBAL
语句也不影响)。也就是说,对于修改全局变量之前连接的客户端只有在客户端重新连接后,才会影响到客户端。
客户端连接时,当前全局变量的值会对客户端的会话变量进行相应初始化。设置会话变量不需要特殊权限,但客户端只能更改自己的会话变量,而不能更改其它客户端的会话变量。设置会话变量的值的方法如下:
1 | SET @@session.pseudo_thread_id=5; |
如果没有指定修改全局变量还是会话变量,服务器会当作会话变量来处理。比如:
1 | SET @@sort_buffer_size = 50000; # 没有指定是 GLOBAL 还是 SESSION,服务器会当做 SESSION 处理 |
使用 SET 设置全局变量或会话变量成功后,如果 MySQL 服务重启,数据库的配置就又会重新初始化。一切按照配置文件进行初始化,全局变量和会话变量的配置都会失效。
MySQL 中还有一些特殊的全局变量,如 log_bin、tmpdir、version、datadir,在 MySQL 服务实例运行期间它们的值不能动态修改,也就是不能使用 SET 命令进行重新设置,这种变量称为静态变量。数据库管理员可以使用前面提到的修改源代码或更改配置文件来重新设置静态变量的值。
MySQL注释
mysql的注释有4 种 :
1 | # 开头到行尾的都为注释,只能注释一行 |
/*! 数字 代码 */"
是有特殊含义的
例如: /!40101 SET NAMES gbk /; 代表的是 如果mysql 版本大于4.0.1.01 ,就执行后面的 set names gbk 这句代码。这就有一个好处,这样就可以把mysql 特有的功能用这种注释,给注释掉,对于非mysql数据库可以正常运行,不需要修改,而对于mysql 数据库,就可以利用这些特性。
MySQL密码规则
1 | mysql> show variables like 'validate_password%'; # 查看密码规则 |
其中验证等级:
- 0/LOW:只验证长度;
- 1/MEDIUM:验证长度、数字、大小写、特殊字符;
- 2/STRONG:验证长度、数字、大小写、特殊字符、字典文件;