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

数据库

  • 创建数据库

    create database [if not exists] 'db_name' character set 'utf8mb4' collate 'utf8mb4_general_ci';
    10:20:30(root@localhost) [(none)]> create database if not exists blog character set utf8mb4 collate utf8mb4_general_ci;
    Query OK, 1 row affected (0.00 sec)
    
    10:20:36(root@localhost) [(none)]> show create database blog;
    +----------+------------------------------------------------------------------+
    | Database | Create Database                                                  |
    +----------+------------------------------------------------------------------+
    | blog     | CREATE DATABASE `blog` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
    +----------+------------------------------------------------------------------+
    1 row in set (0.01 sec)
    
    10:20:40(root@localhost) [(none)]> exit
    Bye
    [root@centos7 script]$cat /data/mysql/blog/db.opt
    default-character-set=utf8mb4
    default-collation=utf8mb4_general_ci
  • 修改数据库

    alter database 'db_name' character set 'utf8mb4';

只能修改字符集,不能修改排序

  • 删除数据库

    drop database [if exists] 'db_name';
  • 查看数据库列表

    show databases;

数据类型

选择数据类型三大原则:

  1. 更小的通常更好,尽量使用可正确存储数据的最小数据类型
  2. 简单就好,简单数据类型的操作通常需要更少的 CPU 周期
  3. 尽量避免 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

  1. char(n) 若存入字符数小于 n,则以空格补于其后,查询之时再将空格去掉,所以 char 类型存储的字符串末尾不能有空格,varchar 不限于此

  2. .char(n) 固定长度,char(4)不管是存入几个字符,都将占用 4 个字节,varchar 是存入的实际字符数+1 个字节所以 varchar(4),存入 3 个字符将占用 4 个字节

  3. char 比 varchar 快

varchar 和 text

  1. varchar 可指定 n,text 不能指定,内部存储 varchar 是存入的实际字符数+1 个字节(n< n>255),text 是实际字符数+2 个字节。
  2. text 类型不能有默认值
  3. 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

  • 插入单条数据

    insert into 表名 values(值列表, 注意: 值的顺序一定要和表结构中的顺序保持一致);
    insert into 表名 (字段列表) values(值列表);

    注意:插入字段的个数,必须要与值的个数一致,字段的顺序可以与表结构中的字段顺序不一致,插入字段可以省略,但是省略的字段必须要有 default 值

  • 插入多条数据

    insert into 表名 values(),(),()...;
    insert into 表名(字段列表) values(),(),()...;
  • 从其他表中 copy 数据

    insert into 目标表 select * from 数据来源表;
    insert into 目标表(字段列表) select 字段列表 from 数据来源表;

    蠕虫复制:将自己表中的数据插入到自己表中,可以用来测试数据库性能

  • replace into

    主要是用来解决在添加数据时, 与主键或唯一键冲突的情况下,数据如何变化的问题

update

update 表名 set 字段1=1, 字段2=2,... where 条件;

# update:更新的关键字
# set: 设置 字段 = 值

注意:一定要指定更新的条件,如果没有指定则更新的是整张表

delete

delete from 表名 where 条件;    # 如果不加条件,则为清空表

另外:

truncate 表名;    # 清空表,主键也会被摧毁,重新开始

select

  • 单表操作

    select 五子句,每个子句都可以省略, 但是位置不能乱

    select 字段列表 from 表名 where 条件 group by 字段 having 条件 order by ASC | DESC limit m,n;
    
    # group by : 按照字段进行分组, 目的是为了统计, 通常配合MySQL中的聚合函数来使用。
    # having : 对分组之后的结果, 在进行一次过滤。
    # order by : asc是升序,desc是降序
    # limit: limit n 是显示n条数据, limit m,n 是从m开始(不包含m),显示n条数据。

    别名: as
    运算符: =,>,<,<=,>=,!=
    is null 和 is not null
    in 和 not in
    between and 和 not between and
    逻辑判断: and(&&) or(||)
    聚合函数:聚合函数对一组值执行计算并返回单一的值。除了 count 以外,聚合函数会忽略空值。聚合函数经常和 group by 子句一起使用

    count(*)    统计数量
    sum(字段)    求和
    avg(字段)    平均值
    min(字段)    求最小值
    max(字段)    求最大值
  • 多表操作

    join

    Select * from 表A  cross|inner |left outer|right outer  | natural |  join 表B  on 表A.字段名= 表B.字段名
    
    # 最常用的是左连接
    select * from 表A left join 表B on 表A.字段名 = 表B.字段名;

select 语句处理顺序:

练习:

练习
导入 hellodb.sql 生成数据库

  1. 在 students 表中,查询年龄大于 25 岁,且为男性的同学的名字和年龄
  2. 以 ClassID 为分组依据,显示每组的平均年龄
  3. 显示第 2 题中平均年龄大于 30 的分组及平均年龄
  4. 显示以 L 开头的名字的同学的信息
  5. 显示 TeacherID 非空的同学的相关信息
  6. 以年龄排序后,显示年龄最大的前 10 位同学的信息
  7. 查询年龄大于等于 20 岁,小于等于 25 岁的同学的信息
  8. 以 ClassID 分组,显示每班的同学的人数
  9. 以 Gender 分组,显示其年龄之和
  10. 以 ClassID 分组,显示其平均年龄大于 25 的班级
  11. 以 Gender 分组,显示各组中年龄大于 25 的学员的年龄之和
  12. 显示前 5 位同学的姓名、课程及成绩
  13. 显示其成绩高于 80 的同学的名称及课程
  14. 取每位同学各门课的平均成绩,显示成绩前三名的同学的姓名和平均成绩
  15. 显示每门课程课程名称及学习了这门课的同学的个数
  16. 显示其年龄大于平均年龄的同学的名字
  17. 显示其学习的课程为第 1、2,4 或第 7 门课的同学的名字
  18. 显示其成员数最少为 3 个的班级的同学中年龄大于同班同学平均年龄的同学
  19. 统计各班级中年龄大于全校同学平均年龄的同学

SHOW 语句

常用 show 语句:

  • 显示所有数据库

  • 显示数据库中所有表

    show databases;
    show tables;
    show tables from database_name;
  • 显示数据表中所有列

    show columns from database_name.table_name
  • 显示一个用户的权限,结果显示类似 grant 命令

    08:04:50(root@localhost) [(none)]> show grants for root@localhost;
    +---------------------------------------------------------------------+
    | Grants for root@localhost                                           |
    +---------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
    | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
    +---------------------------------------------------------------------+
    2 rows in set (0.00 sec)
  • 显示表的索引

    show index from mysql.user;
  • 显示一些系统特定资源的信息,例如,正在运行的线程数量

    show status;
  • 显示系统变量的名称和值

    show variables;
  • 显示系统中正在运行的所有进程,也就是当前正在执行的查询。大多数用户可以查看他们自己的进程,但是如果他们拥有 process 权限,就可以查看所有人的进程,包括密码

    show processlist;
  • 显示 database 中的每个表的信息。信息包括表类型和表的最新更新时间

    show table status from mysql;
  • 示服务器所支持的不同权限

    show privileges;
  • 显示创建数据库的语句

  • 显示创建数据表的语句

    08:16:18(root@localhost) [(none)]> show create database mysql;
    08:16:18(root@localhost) [(none)]> show create table mysql.user;
  • 显示可用的存储引擎和默认引擎

    08:19:02(root@localhost) [(none)]> show engines;
  • 显示 innoDB 存储引擎的状态

    08:21:11(root@localhost) [(none)]> show engine innodb status;
  • 显示最后一个执行的语句所产生的错误、警告和通知

  • 显示最后一个执行语句所产生的错误

    show warnings;
    show errors;
  • 查看视图定义

VIEW 视图

虚拟表,保存实表的查询结果,相当于别名

  • 创建视图:

    create view 视图名称 as 查询语句;
    # 例如
    create view v_age as * from tb_stu where age < 25;
  • 查看视图

    SHOW CREATE VIEW view_name #只能看视图定义
    SHOW CREATE TABLE view_name # 可以查看表和视图
  • 删除视图

    DROP VIEW [IF EXISTS] view_name

FUNCTION 函数

分为系统内置函数和自定义函数

内置函数

参考:mysql8.0mysql5.7,注意:从 mysql5.7 开始,取消了 password()函数

常见内置函数:

  • database():当前所在数据库

  • now()

    08:53:59(root@localhost) [mysql]> select now();
    +---------------------+
    | now()               |
    +---------------------+
    | 2020-10-19 20:56:46 |
    +---------------------+
    1 row in set (0.00 sec)
  • curdate():当前日期

  • curtime():当前时间

  • datediff():时间差

    08:53:42(root@localhost) [mysql]> select datediff('2020-10-19','2020-7-27');
    ------------------------------------+
    | datediff('2020-10-19','2020-7-27') |
    +------------------------------------+
    |                                 84 |
    +------------------------------------+
    1 row in set (0.00 sec)
  • version()

  • from_unixtime():时间戳转日期

    08:48:39(root@localhost) [mysql]> select from_unixtime(1603111741);
    +---------------------------+
    | from_unixtime(1603111741) |
    +---------------------------+
    | 2020-10-19 20:49:01       |
    +---------------------------+
    1 row in set (0.00 sec)
    08:50:10(root@localhost) [mysql]> select from_unixtime(1603111741,'%Y-%m-%d %T');
    +-----------------------------------------+
    | from_unixtime(1603111741,'%Y-%m-%d %T') |
    +-----------------------------------------+
    | 2020-10-19 20:49:01                     |
    +-----------------------------------------+
    1 row in set (0.00 sec)

文本处理函数:

  • 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

    CREATE USER [IF NOT EXISTS] 'USERNAME'@'HOST' [IDENTIFIED BY 'password']
  • 用户重命名

    RENAME USER old_user_name TO new_user_name;
  • 删除用户

    DROP USER [IF EXISTS] 'USERNAME'@'HOST'
  • 修改密码

    如果 mysql.user 表的 authentication_string 和 password 字段都保存密码,authentication_string 优先生效

    # 方法一
    ALTER USER [IF EXISTS] 'root'@'localhost' IDENTIFIED BY \"${rootpwd}\";
    # 其他方法,略...
  • 忘记管理员密码的解决方法

    1. 重启 mysqld 时,使用选项 --skip-grant-tables --skip-networking
    2. 使用 update 命令修改 mysql.user 表,把管理员密码去掉
    3. 移除上述两个选项,然后重启
    4. 使用ALTER USER 'root'@'localhost' IDENTIFIED BY \"${rootpwd}\";设置密码
    [root@c71 ~]$vim /etc/my.cnf
    [mysqld]
    skip-grant-tables
    skip-networking  # MySQL8.0不需要此选项
    ...
    [root@c71 ~]$systemctl restart mysqld.service
    [root@c71 ~]$msyql -e "update mysql.user set authentication_string='' where user='root' and host='localhost';"
    [root@c71 ~]$vim /etc/my.cnf
    [mysqld]
    # skip-grant-tables
    # skip-networking  # MySQL8.0不需要此选项
    ...
    [root@c71 ~]$systemctl restart mysqld.service
    [root@c71 ~]$mysql -e "alter user 'root'@'localhost' IDENTIFIED BY \"123456\";"

角色管理

给账户分配角色,更加方便管理多个同样权限的账户

语句 作用
CREATE ROLEDROP ROLE 创建和删除角色
GRANTREVOKE 给角色授权和取消授权
SHOW GRANTS 查看角色权限
SET DEFAULT ROLE 设置账户默认使用什么角色
SET ROLE 改变当前会话角色
CURRENT_ROLE()函数 显示当前会话的角色
mandatory_roles 和 activate_all_roles_on_login 系统变量 允许定义用户登陆时强制的或者激活授权的角色

权限管理 GRANT/REVOKE

三种授权方式:

  1. 给用户分配权限

    GRANT
        priv_type [(column_list)][, priv_type [(column_list)]] ...
        ON [object_type] priv_level
        TO user_or_role [, user_or_role] ...
        [WITH GRANT OPTION]
        [AS user
            [WITH ROLE
                DEFAULT
              | NONE
              | ALL
              | ALL EXCEPT role [, role ] ...
              | role [, role ] ...
            ]
        ]
    • priv_type:权限类型,有很多种
      • column_list:有些权限作用与数据表的字段
    • object_type:表(TABLE)|函数(FUNCTION)|存储过程(PROCEDURE)
      • priv_level:
        • * | *.*:所有数据库
        • db_name.*:指定数据库中所有表
        • db_name.tbl_name:指定数据库的指定数据表
        • db_name.routine_name:指定数据库的函数、存储过程、触发器
    • with_option:设置权限某些参数,例如:
      • MAX_QUERIES_PER_HOUR count
      • MAX_UPDATES_PER_HOUR count
      • MAX_CONNECTIONS_PER_HOUR count
      • MAX_USER_CONNECTIONS count
  2. 给用户分配角色

    GRANT role [, role] ...
        TO user_or_role [, user_or_role] ...
        [WITH ADMIN OPTION]
  3. 代理其他用户:这种情况暂时不研究

查看用户权限:

SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER[()];

示例:

mysql> show grants for root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.02 sec)

mysql> show grants for root@127.0.0.1;
+---------------------------------------------------+
| Grants for root@127.0.0.1                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' | # 没有操作用户的权限
+---------------------------------------------------+
1 row in set (0.02 sec)

取消授权:

REVOKE
    priv_type [(column_list)][, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user_or_role [, user_or_role] ...

REVOKE ALL [PRIVILEGES], GRANT OPTION
    FROM user_or_role [, user_or_role] ...

REVOKE PROXY ON user_or_role
    FROM user_or_role [, user_or_role] ...

REVOKE role [, role ] ...
    FROM user_or_role [, user_or_role ] ...

user_or_role: {
    user (see Section 6.2.4, “Specifying Account Names”)
  | role (see Section 6.2.5, “Specifying Role Names”.
}

# 范例
REVOKE DELETE ON *.* FROM 'testuser'@'172.16.0.%';

设置权限后需要刷新权限:

FLUSH PRIVILEGES;

权限类型:

所有权限、管理类、程序类、数据库级别、表级别、字段级别

  • 所有权限:ALL PRIVILEGESALL

  • 管理类:CREATE USERFILESUPERSHOW DATABASESRELOADSHUTDOWNREPLICATION SLAVEREPLICATION CLIENTLOCK TABLESPROCESSCREATE TEMPORARY TABLES

  • 程序类:针对 FUNCTION、PROCEDURE、TRIGGER

    CREATEALTERDROPEXCUTE

  • 库和表级别:针对 DATABASE、TABLE

    ALTERCREATECREATE VIEWDROP INDEXSHOW VIEW

    WITH GRANT OPTION:能将自己获得的权限转赠给其他用户

  • 数据操作:SELECTINSERTDELETEUPDATE

  • 字段级别:SELECT(col1,col2,...)UPDATE(col1,col2,...)INSERT(col1,col2,...)


SQL语言
http://blog.lujinkai.cn/运维/MySQL/SQL语言/
作者
像方便面一样的男子
发布于
2020年12月31日
更新于
2023年12月5日
许可协议