SQL语言
本文最后更新于:2023年12月5日 晚上
数据库组件(对象)
数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等
SQL 语言语法标准
命名规则
必须以字母开头,可包括数字和三个特殊字符(# _ $);
不要使用 MySQL 的保留字
SQL 语句分类
DDL:Data Defination Language 数据定义语言
CREATE,DROP,ALTER
DML:Data Manipulation Language 数据操纵语言
INSERT,DELETE,UPDATE
DQL:Data Query Language 数据查询语言
SELECT
DCL:Data Control Language 数据控制语言
GRANT,REVOKE,COMMIT,ROLLBACK
字符集和排序
uft8 最长占用 3 个字节,utf8mb4 最长占用 4 个字节,为了获取更好的兼容性,推荐使用 uft8mb4
以下排序规则:
utf8mb4_unicode_ci:基于标准的 Unicode 来排序和比较,能够在各种语言之间精确排序
utf8mb4_general_ci:没有实现 Unicode 排序规则,在遇到某些特殊语言或者字符集,排序结果可能不一致
utf8mb4_0900_ai_ci:比以上两种都好,mysql8.0 默认的排序规则,mysql8.0 以下版本不支持
所以:mysql8.0 使用默认排序,mysql8.0 以下版本设置 utf8mb4_general_ci
数据库
创建数据库
修改数据库
只能修改字符集,不能修改排序
删除数据库
查看数据库列表
数据类型
选择数据类型三大原则:
- 更小的通常更好,尽量使用可正确存储数据的最小数据类型
- 简单就好,简单数据类型的操作通常需要更少的 CPU 周期
- 尽量避免 NULL,包含为 NULL 的列,对 MySQL 更难优化
1. 整数
- int(m)里的 m 表示 select 查询结果集中的显示宽度,并不影响实际的取值范围,规定了 MySQL 的一些交互工具(例如 MySQL 命令行客户端)用来显示字符的个数。对于存储和计算来说,Int(1)和 Int(20)是相同的
- BOOL,BOOLEAN:布尔型,是 TINYINT(1)的同义词。zero 值被视为假,非 zero 值视为真
2. 浮点
float(m,d) 单精度浮点型 8 位精度(4 字节) m 总个数,d 小数位
double(m,d) 双精度浮点型 16 位精度(8 字节) m 总个数,d 小数位
3. 定点数
decimal(m,d) 十进制,精确值,总个数 m<65,小数位 d<30 且 d<\m
MySQL5.0 和更高版本将数字打包保存到一个二进制字符串中(每 4 个字节存 9 个数字)。
例如:
decimal(18,9)小数点两边将各存储 9 个数字,一共使用 9 个字节:其中,小数点前的 9 个数字用 4 个字节,小数点后的 9 个数字用 4 个字节,小数点本身占 1 个字节
浮点类型在存储同样范围的值时,通常比 decimal 使用更少的空间。float 使用 4 个字节存储。double 占用 8 个字节
因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用 decimal,例如存储财务数据。但在数据量比较大的时候,可以考虑使用 bigint 代替 decimal
4. 字符串
char(n):固定长度,最多 255 个字符
varchar(n):可变长度,最多 65535 个字符,很多人以为 n 最大是 255,其实不然,只是 varchar(255)是习惯性写法
tinytext:可变长度,最多 255 个字符
text:可变长度,最多 65535 个字符
mediumtext:可变长度,最多 2^24-1 个字符
longtext:可变长度,最多 2^32-1 个字符
BINARY(M):固定长度,可存储二进制或字符,长度为 0-M 字节
VARBINARY(M):可变长度,可存二进制或字符,允许长度为 0-M 字节
ENUM:枚举
SET:集合
char 和 varchar
char(n) 若存入字符数小于 n,则以空格补于其后,查询之时再将空格去掉,所以 char 类型存储的字符串末尾不能有空格,varchar 不限于此
.char(n) 固定长度,char(4)不管是存入几个字符,都将占用 4 个字节,varchar 是存入的实际字符数+1 个字节所以 varchar(4),存入 3 个字符将占用 4 个字节
char 比 varchar 快
varchar 和 text
- varchar 可指定 n,text 不能指定,内部存储 varchar 是存入的实际字符数+1 个字节(n< n>255),text 是实际字符数+2 个字节。
- text 类型不能有默认值
- varchar 可直接创建索引,text 创建索引要指定前多少个字符。varchar 查询速度快于 text
5. 二进制 BLOB
- TEXT 以文本方式存储,英文存储区分大小写,而 Blob 以二进制方式存储,不分大小写
- BLOB 存储的数据只能整体读出
- TEXT 可以指定字符集,BLOB 不用指定字符集
6. 日期时间
- date:日期 ‘2008-12-2’
- time:时间 ‘12:25:36’
- datetime:日期时间 ‘2008-12-2 22:06:44’
- timestamp:自动存储记录修改时间
- YEAR(2), YEAR(4):年份
- timestamp:字段里的时间数据会随其他字段修改的时候自动刷新,这个数据类型的字段可以存放这条记录最后被修改的时间
7. 修饰符
适用所有类型的修饰符:
- DEFAULT 默认值、
- NULL 数据列可包含 NULL 值,默认值
- NOT NULL 数据列不允许包含 NULL 值,*为必填选项
- PRIMARY KEY 主键,所有记录中此字段的值不能重复,且不能为 NULL
- UNIQUE KEY 唯一键,所有记录中此字段的值不能重复,但可以为 NULL
- CHARACTER SET name 指定一个字符集
适用数值型的修饰符:
- AUTO_INCREMENT 自动递增,适用于整数类型
- UNSIGNED 无符号
insert、update、select、delete
insert
插入单条数据
注意:插入字段的个数,必须要与值的个数一致,字段的顺序可以与表结构中的字段顺序不一致,插入字段可以省略,但是省略的字段必须要有 default 值
插入多条数据
从其他表中 copy 数据
蠕虫复制:将自己表中的数据插入到自己表中,可以用来测试数据库性能
replace into
主要是用来解决在添加数据时, 与主键或唯一键冲突的情况下,数据如何变化的问题
update
注意:一定要指定更新的条件,如果没有指定则更新的是整张表
delete
另外:
select
单表操作
select 五子句,每个子句都可以省略, 但是位置不能乱
别名: as
运算符: =,>,<,<=,>=,!=
is null 和 is not null
in 和 not in
between and 和 not between and
逻辑判断: and(&&) or(||)
聚合函数:聚合函数对一组值执行计算并返回单一的值。除了 count 以外,聚合函数会忽略空值。聚合函数经常和 group by 子句一起使用多表操作
join
select 语句处理顺序:
练习:
练习
导入 hellodb.sql 生成数据库
- 在 students 表中,查询年龄大于 25 岁,且为男性的同学的名字和年龄
- 以 ClassID 为分组依据,显示每组的平均年龄
- 显示第 2 题中平均年龄大于 30 的分组及平均年龄
- 显示以 L 开头的名字的同学的信息
- 显示 TeacherID 非空的同学的相关信息
- 以年龄排序后,显示年龄最大的前 10 位同学的信息
- 查询年龄大于等于 20 岁,小于等于 25 岁的同学的信息
- 以 ClassID 分组,显示每班的同学的人数
- 以 Gender 分组,显示其年龄之和
- 以 ClassID 分组,显示其平均年龄大于 25 的班级
- 以 Gender 分组,显示各组中年龄大于 25 的学员的年龄之和
- 显示前 5 位同学的姓名、课程及成绩
- 显示其成绩高于 80 的同学的名称及课程
- 取每位同学各门课的平均成绩,显示成绩前三名的同学的姓名和平均成绩
- 显示每门课程课程名称及学习了这门课的同学的个数
- 显示其年龄大于平均年龄的同学的名字
- 显示其学习的课程为第 1、2,4 或第 7 门课的同学的名字
- 显示其成员数最少为 3 个的班级的同学中年龄大于同班同学平均年龄的同学
- 统计各班级中年龄大于全校同学平均年龄的同学
SHOW 语句
常用 show 语句:
显示所有数据库
显示数据库中所有表
显示数据表中所有列
显示一个用户的权限,结果显示类似 grant 命令
显示表的索引
显示一些系统特定资源的信息,例如,正在运行的线程数量
显示系统变量的名称和值
显示系统中正在运行的所有进程,也就是当前正在执行的查询。大多数用户可以查看他们自己的进程,但是如果他们拥有 process 权限,就可以查看所有人的进程,包括密码
显示 database 中的每个表的信息。信息包括表类型和表的最新更新时间
示服务器所支持的不同权限
显示创建数据库的语句
显示创建数据表的语句
显示可用的存储引擎和默认引擎
显示 innoDB 存储引擎的状态
显示最后一个执行的语句所产生的错误、警告和通知
显示最后一个执行语句所产生的错误
查看视图定义
VIEW 视图
虚拟表,保存实表的查询结果,相当于别名
创建视图:
查看视图
删除视图
FUNCTION 函数
分为系统内置函数和自定义函数
内置函数
参考:mysql8.0、mysql5.7,注意:从 mysql5.7 开始,取消了 password()函数
常见内置函数:
database():当前所在数据库
now()
curdate():当前日期
curtime():当前时间
datediff():时间差
version()
from_unixtime():时间戳转日期
文本处理函数:
- trim():去除两边的空格
- upper():返回大写字符
- lower():返回小写字符
- concat():返回连接字符串 ★★★
- substring():返回截取的字符
- repace(str, str1, str2):在 str 中, 将字符串 str1 替换为 str2
数学函数:
- abs(x):返回 x 的绝对值
- bin(x):返回 x 的二进制(oct 八进制, hex 返回十六进制)
- ceiling(x):向上取整
- floor(x):向下取整
- mod(被除数, 除数):返回余数, 取模
- pow(底数, 指数):返回求指数值
自定义函数
user-defined function,UDF,保存在 mysq.proc 表中
…
变量
两种变量:系统内置变量和用户自定义变量
- 系统变量:MySQL 数据库中内置的变量,可用@@var_name 引用
- 用户自定义变量分为以下两种
- 普通变量:在当前会话中有效,可用@var_name 引用
- 局部变量:在函数或存储过程内才有效,需要用 DECLARE 声明,之后直接用 var_name 引用
PROCEDURE 存储过程
多表 SQL 语句的集合,可以独立执行,存储过程保存在 mysql.proc 表中
流程控制
存储过程 和 函数 中可以使用流程控制来控制语句的执行
- IF:用来进行条件判断。根据是否满足条件,执行不同语句
- CASE:用来进行条件判断,可实现比 IF 语句更复杂的条件判断
- LOOP:重复执行特定的语句,实现一个简单的循环
- LEAVE:用于跳出循环控制,相当于 SHELL 中 break
- ITERATE:跳出本次循环,然后直接进入下一次循环,相当于 SHELL 中 continue
- REPEAT:有条件控制的循环语句。当满足特定条件时,就会跳出循环语句
- WHILE:有条件控制的循环语句
TRIGGER 触发器
触发器是由事件来触发某个操作, 这些事件包括 insert、update 和 delete 语句。当执行这些事件时,就会激活触发器执行响应的操作
Event 事件
用户管理
用户创建,新建用户默认权限:USAGE
用户重命名
删除用户
修改密码
如果 mysql.user 表的 authentication_string 和 password 字段都保存密码,authentication_string 优先生效
忘记管理员密码的解决方法
- 重启 mysqld 时,使用选项
--skip-grant-tables --skip-networking
- 使用 update 命令修改 mysql.user 表,把管理员密码去掉
- 移除上述两个选项,然后重启
- 使用
ALTER USER 'root'@'localhost' IDENTIFIED BY \"${rootpwd}\";
设置密码
- 重启 mysqld 时,使用选项
角色管理
给账户分配角色,更加方便管理多个同样权限的账户
语句 | 作用 |
---|---|
CREATE ROLE 和 DROP ROLE |
创建和删除角色 |
GRANT 和 REVOKE |
给角色授权和取消授权 |
SHOW GRANTS |
查看角色权限 |
SET DEFAULT ROLE |
设置账户默认使用什么角色 |
SET ROLE |
改变当前会话角色 |
CURRENT_ROLE() 函数 |
显示当前会话的角色 |
mandatory_roles 和 activate_all_roles_on_login 系统变量 | 允许定义用户登陆时强制的或者激活授权的角色 |
权限管理 GRANT/REVOKE
三种授权方式:
给用户分配权限
- priv_type:权限类型,有很多种
- column_list:有些权限作用与数据表的字段
- object_type:表(TABLE)|函数(FUNCTION)|存储过程(PROCEDURE)
- priv_level:
- * | *.*:所有数据库
- db_name.*:指定数据库中所有表
- db_name.tbl_name:指定数据库的指定数据表
- db_name.routine_name:指定数据库的函数、存储过程、触发器
- priv_level:
- with_option:设置权限某些参数,例如:
- MAX_QUERIES_PER_HOUR count
- MAX_UPDATES_PER_HOUR count
- MAX_CONNECTIONS_PER_HOUR count
- MAX_USER_CONNECTIONS count
- priv_type:权限类型,有很多种
给用户分配角色
代理其他用户:这种情况暂时不研究
查看用户权限:
示例:
取消授权:
设置权限后需要刷新权限:
权限类型:
所有权限、管理类、程序类、数据库级别、表级别、字段级别
所有权限:
ALL PRIVILEGES
或ALL
管理类:
CREATE USER
、FILE
、SUPER
、SHOW DATABASES
、RELOAD
、SHUTDOWN
、REPLICATION SLAVE
、REPLICATION CLIENT
、LOCK TABLES
、PROCESS
、CREATE TEMPORARY TABLES
程序类:针对 FUNCTION、PROCEDURE、TRIGGER
CREATE
、ALTER
、DROP
、EXCUTE
库和表级别:针对 DATABASE、TABLE
ALTER
、CREATE
、CREATE VIEW
、DROP INDEX
、SHOW VIEW
、WITH GRANT OPTION
:能将自己获得的权限转赠给其他用户数据操作:
SELECT
、INSERT
、DELETE
、UPDATE
字段级别:
SELECT(col1,col2,...)
、UPDATE(col1,col2,...)
、INSERT(col1,col2,...)