【Day 30】Linux-SQL语句

简称	全称	核心命令	主要功能	操作对象 / 影响范围
DDL	数据定义语言	CREATE、ALTER、DROP、TRUNCATE	不处理表中的具体数据，只修改数据库 / 表的定义	数据库结构
DML	数据操作语言	INSERT、UPDATE、DELETE、	新增、修改、删除表中的记录	表中的具体数据
DQL	数据查询语言	SELECT	从数据库中查询数据（仅读取，不修改）	表中的记录（返回查询结果集）
DCL	数据控制语言	GRANT、REVOKE	授予或收回用户对数据库对象的操作权限	用户权限
TCL	事务控制语言	COMMIT、ROLLBACK、SAVEPOINT	管理事务，确保操作的原子性、一致性等	事务

一、DDL语句

（一）库

• > CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ CHARACTER SET 字符集] [ COLLATE 排序规则];            //创建数据库。

        // character set  简写：character ；别名：charset

        //若不指定 collate， 会用字符集默认排序规则。

        MySQL 8.0默认使用的字符集utf8mb4，支持存储中文

        MySQL 5.7默认使用的字符集是latin1，不支持存储中文；可在创建数据库时通过选项charset指定字符集为utf8，方便在表中存储中文。在 5.5.3-8.0版本，utf8 实际上是 utf8mb3 的别名。

• utf8mb3：支持基本中文，但不支持 emoji、某些生僻字（如 “𪚥”“𠀍” 等）。
• utf8mb4：完全支持 UTF-8 标准，可存储所有中文、emoji 及其他 Unicode 字符。

        

//可通过在/etc/my.cnf配置文件中添加如下参数修改字符集

        

• > DROP DATABASE [ IF EXISTS ] 数据库名;         //删除数据库

        //删除数据库会彻底删除所有表和数据，

        

• >SHOW DATABASES;           //查看所有数据库
• >SHOW CREATE DATABASE 数据库;        //查看当前数据库详情（字符集、排序规则等）
• >SHOW TABLES; //查看所有表
• >USE 数据库;        //切换数据库
• >DESC 表名;      //查看表结构

• >ALTER DATABASE bbs CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

• //-- 修改数据库的字符集和排序规则

（二）表

        1、创建表

        

• 常用约束：PRIMARY KEY（主键）、NOT NULL（非空）、UNIQUE（唯一）、DEFAULT（默认值）、FOREIGN KEY（外键）等。
• ENGINE：指定存储引擎（如 InnoDB 支持事务，MyISAM 不支持）。

（1）数据类型（常用）

• 数值型

类型	存储大小	有效数字 / 范围	适用场景	精度损失
整数类型
TINYINT	1 字节	范围：-128 ~ 127（有符号）；0 ~ 255（无符号）	小范围整数	无
SMALLINT	2 字节	范围：-32768 ~ 32767（有符号）	中等范围整数（如班级人数）	无
INT	4 字节	范围：-2^31 ~ 2^31-1	常用整数（用户 ID、年龄）	无
BIGINT	8 字节	范围：-2^63 ~ 2^63-1	超大整数（订单号、海量数据 ID）	无
浮点数 / 定点数
FLOAT	4 字节	6-7 位有效数字，范围 ±3.4×10^38	精度要求低的小数（如温度）	有
DOUBLE	8 字节	15-17 位有效数字，范围 ±1.8×10^308	较高精度的科学计算（如坐标）	有
DECIMAL(M,D)	可变（M+2 字节）	M 为总位数（1-65） D 为小数位数（0-D）	金额、汇率等需精确计算场景 （如DECIMAL(10,2)表示最大 99999999.99）	无

• 字符型

类型	存储特点	最大长度限制	适用场景
VARCHAR(N)	可变长度 （按需分配空间）	N 为字符数（最大 65535 字节，受行总长度限制）	长度不固定的字符串（如姓名、地址）
CHAR(N)	固定长度 占用 N 字符空间	N 为字符数（最大 255）	长度固定的字符串（如身份证号、手机号）
TEXT	长文本 存储在独立空间	最大 65535 字节（约 64KB）	较长文本（如文章摘要、备注）
LONGTEXT	超长文本	最大 4GB	极长文本（如全文、日志）

• 日期型：

类型	格式示例	范围	适用场景
DATE	2025-8-18	1000-01-01 ~ 9999-12-31	仅需日期（如出生日期）
TIME	11:11:11	-838:59:59 ~ 838:59:59	仅需时间（如上下班时间）
DATETIME	2025-8-18 11:11:11	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	日期 + 时间（如订单创建时间）

• 其他：

类型	特点与取值	适用场景
BOOLEAN	等价于TINTINT（1） TRUE，FALSE=0	逻辑判断（如是否启用：1 = 是，0 = 否）
ENUM	枚举值（如ENUM('男','女','沃尔玛购物袋')）	固定可选值的场景（如性别、状态）单/不选
SET	多选项（如SET('钢琴','长笛','竖琴'))	多选场景（如用户兴趣标签）多选+单/不选

（2） 常用属性 / 约束（字段级）

约束 / 属性	作用说明	示例
NOT NULL	字段值不能为NULL（强制必填）	name VARCHAR(50) NOT NULL（姓名必须填写）
PRIMARY KEY	设为主键（唯一标识一条记录，自带NOT NULL属性，一个表只能有一个主键）	id INT PRIMARY KEY（用 id 唯一标识用户）
AUTO_INCREMENT	数值自动递增（仅用于整数类型，配合主键使用，避免手动插入重复值）	id INT PRIMARY KEY AUTO_INCREMENT（每次新增记录 id 自动 + 1）
DEFAULT 值	未显式插入值时，自动填充默认值	status TINYINT DEFAULT 0（状态默认值为 0，代表 “未激活”）
UNIQUE	字段值唯一（允许NULL，但非NULL值不能重复，可用于唯一标识非主键字段）	email VARCHAR(100) UNIQUE（邮箱地址不能重复）
COMMENT ' 说明'	字段注释（用于说明字段含义，方便后期维护和协作）	age INT COMMENT '用户年龄（0-150岁）'
CHECK (条件)	限制字段值必须满足指定条件（MySQL 8.0 + 支持，确保数据合法性）	age INT CHECK (age > 0 AND age <= 150)（年龄必须在 1-150 之间）
FOREIGN KEY	关联其他表的主键（字段级外键）	class_id INT REFERENCES classes(class_id)（关联班级表的 class_id）
UNSIGNED	仅用于整数类型，限制字段值为非负数	score INT UNSIGNED（分数不能为负）
ZEROFILL	整数类型专用，不足位数时用 0 填充	order_no INT(6) ZEROFILL（订单号不足 6 位时补 0，如 “000123”）

（3）表级约束（多字段关联）

• 复合主键：多个字段组合唯一标识记录
  PRIMARY KEY (字段1，字段2)

• 外键关联：关联其他表的主键
  FOREIGN KEY (字段) REFERENCES(主表字段)

（4）表选项（常用）

• ENGINE = 存储引擎;   //如InnoDB（支持事务）、MyISAM（查询快）

• CHARSET = 字符集;      //如utf8mb4（支持中文和 emoji）

        

        2、修改表

    

        3、删除表

• DROP TABLE [ IF EXISTS ] 表名;

        //删除表会删除所有数据和结构，且不可恢复。

• TRUNCATE TABLE 表名;        //清空某个表的数据，自增列会重置

（三）索引

        1、创建索引

   

        2、删除索引

• DROP INDEX idx_username ON users;

（四）视图

        1、创建视图

    

        2、删除视图

• DROP VIEW IF EXISTS view_active_users;

---

二、DML语句

1、INSERT

• INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);     //向表中添加新记录

//若插入所有字段且顺序与表定义一致，可省略字段列表：

//批量插入

        

//插入查询结果

        

2、UPDATE

• UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2, ... [ WHERE 条件 ];     //更新表中已存在的记录

        

3、DELETE

• DELETE FROM 表名 [ WHERE 条件];     //用于删除表中的记录

• >select * from 表名

        

三、DQL

（一）单表查询

（1）基础查询（SELECT）

① 选择表中的列

• SELECT 字段1, 字段2, ... FROM 表名;    //选择指定列

• SELECT * FROM 表名;         // 选择所有列（*）

② 去重显示 distinct

• SELECT DISTINCT 列名 FROM 表名;        // DISTINCT去重,去除重复行

• SELECT DISTINCT 字段1,字段2 FROM 表名;    //根据多列的组合去重

//DISTINCT必须放在所有列名之前，且若指定多列，会同时对多列的组合结果去重，即只有当所有指定列的值都完全相同时，才会被视为重复记录并只保留一条。

（2）条件查询（WHERE）

// 筛选符合条件的行

①比较运算符

=（等于）、<>/!=（不等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）

• SELECT name, age FROM student WHERE age > 18;  //查询student表中age>18 的学生。

②逻辑运算符

AND（与）、OR（或）、NOT（非）。

• SELECT name, age, score FROM student WHERE age > 18 AND score > 80;

• //查询年龄大于 18 且分数大于 80 的学生

• SELECT name, age FROM student WHERE NOT (age BETWEEN 18 AND 22);

• //查询查询年龄不在 18-22 之间的学生

③范围查询（BETWEEN...AND...）

// 筛选某个范围内的值（闭区间，包含两端值）

• SELECT name, score FROM student WHERE score BETWEEN 80 AND 100;

• //查询分数在 80 到 100 之间的学生

④集合查询（IN / NOT IN）

// IN用于筛选 “值在指定集合中” 的行；NOT IN 则相反。

• SELECT name, age FROM student WHERE age IN (18 , 20 , 22);   

• SELECT name, age FROM student WHERE age NOT IN (18 , 20 , 22);

• //年龄为/不为18、20、22 的学生

⑤模糊查询

① LIKE 配合通配符

• %：匹配任意多个字符（包括 0 个）；

• _：匹配单个字符。

• SELECT name FROM student WHERE name LIKE '张%';    //查询姓名以 “张” 开头的学生

• SELECT name FROM student WHERE name NOT LIKE '%李%';  //姓名中不含 “李” 的学生

• SELECT name FROM student WHERE name LIKE '_子%';   //姓名第二个字是 “子” 的学生

② RLIKE配合正则表达式

• ^：匹配字符串开头。
• $：匹配字符串结尾。
• .：匹配任意单个字符（类似 _）。
• *：匹配前面的字符 0 次或多次。
• +：匹配前面的字符 1 次或多次。
• [abc]：匹配 a、b、c 中的任意一个。
• [0-9]：匹配任意数字。
• SELECT * FROM users WHERE username RLIKE '^李.';
• //匹配以 "李" 开头且长度至少 2 的用户名
• SELECT * FROM users WHERE email RLIKE '.+@.+\..+';        //匹配邮箱格式

// NULL 表示 “未知”（非空字符串或 0），需用 IS 判断，不能用 = 或 != 判断。

• SELECT name, score FROM student WHERE score IS NULL; 

• SELECT name, score FROM student WHERE score IS NOT NULL;

•  //查询student表内score为null/不为null的学生名

（3）排序查询

// 对查询结果排序

①排序

• SELECT 列名 FROM 表名 ORDER BY 排序列1 [ASC|DESC], 排序列2 [ASC|DESC];

• ASC ：升序（默认，可省略）；

• DESC ：降序；

• 支持多列排序（先按第一列排，第一列相同则按第二列排）。

• SELECT name, age, score FROM student ORDER BY age DESC, score ASC;

• //按年龄降序排列，年龄相同则按分数升序排列。

②排序限制行数

• SELECT 列名 FROM 表名 LIMIT n;          //返回前n行。

• SELECT 列名 FROM 表名 LIMIT m, n;   //从第m行（索引从0开始）起，返回n行

• SELECT name, age FROM student ORDER BY age DESC LIMIT 2, 1;     //年龄第三

（4）聚合查询

函数	作用
COUNT(字段)	统计行数（非 NULL 值数量）
SUM(字段)	计算列的总和（仅数值型）
AVG(字段)	计算列的平均值（仅数值型）
MAX(字段)	求列的最大值
MIN(字段)	求列的最小值

• COUNT（*）：统计所有行数（包括 NULL 值）；

• COUNT（字段名）：统计该列非 NULL 值的行数；

• COUNT（DISTINCT 字段名）：统计该列非 NULL 且不重复的值的行数。

（5）分组查询

// 当需要按某个列进行分组统计时，使用GROUP BY分组后，聚合函数会对每个组单独计算。

• SELECT 分组列, 聚合函数 FROM 表名 GROUP BY 分组列;

• HAVING用于过滤分组后的结果（需配合GROUP BY使用），且HAVING可使用聚合函数。

        

（二）多表查询（关联查询）

（1）交叉连接（Cross Join）：笛卡尔积

• 原理：将两张表的所有记录两两组合，结果行数 = 表 1 行数 × 表 2 行数。

        

（2）内连接（Inner Join）：匹配关联记录

• 原理：只返回两张表中满足关联条件的记录（相关联字段存在相同值时，才显示对应的结果）

        

        

• 特点：只返回匹配的记录，不匹配的记录会被过滤掉。

（3） 外连接（Outer Join）：保留不匹配的记录

外连接会保留其中一张表的所有记录，另一张表中不匹配的记录用NULL  填充。分为以下三种：

①左外连接（Left Outer Join）

• 原理：保留左表（JOIN 左侧的表）的所有记录，右表中不匹配的记录用NULL 填充。

②右外连接（Right Outer Join）

• 原理：保留右表（JOIN 右侧的表）的所有记录，左表中不匹配的记录用NULL 填充。

（三）子查询

• 定义：子查询是一个独立的SELECT语句，嵌套在另一个 SQL 语句的WHERE，FHAVING等子句中。将一个查询的结果作为另外一个查询的条件用。

---

练习题

 练习题 1：查询每个学生的基本信息、所选第一门课的名称及授课教师

练习题 2：查询每个教师的基本信息、所教课程中平均分最高的课程名称及该课程的学生

练习题3

练习题 3：查询所有选了 “数据库原理” 课程的学生姓名、该课程成绩、授课教师姓名及学生的指导教师姓名

• 表关联：students（SID、Name、TID（指导教师 ID））、scores（SID、CID、Score）、courses（CID、Cname、TID（授课教师 ID））、tutors（TID、Tname）。

• 说明：需区分 “授课教师”（教该课程的老师）和 “指导教师”（学生的专属指导老师），两个TID分别关联教师表。

• 提示：给教师表起两个别名（如t1代表授课教师，t2代表指导教师），分别关联课程表和学生表。

练习题 4：查询每个班级（假设学生表有class_id字段）中年龄最小的学生姓名、班级编号、该学生所选所有课程的名称及对应成绩

• 表关联：students（SID、Name、Age、class_id）、scores（SID、CID、Score）、courses（CID、Cname）。

• 说明：先按班级分组，找到每个班级中年龄最小的学生（可能有多个，用MIN(Age)筛选），再查询该学生的所有选课记录。

• 提示：用子查询筛选每个班级的最小年龄学生 ID，再关联成绩表和课程表。

练习题 5：查询没有选任何课程的学生姓名、年龄及指导教师姓名（若有）

• 表关联：students（SID、Name、Age、TID）、tutors（TID、Tname）、scores（SID）。

• 说明：“没有选任何课程” 即students.SID在scores表中无匹配记录。

• 提示：用LEFT JOIN关联学生表和成绩表，通过scores.SID IS NULL筛选无选课记录的学生，再关联教师表获取指导教师。

---

四、用户权限

• # yum install -y mysql-community-client        //安装操作 MySQL 服务器的 “客户端工具集”，用于在服务器上远程连接其他 MySQL 数据库。
• # mysql -u martin -p -h 192.168.140.10    //通过命令行连接 MySQL 数据库

        // -h 192.168.140.10：指定要连接的 MySQL 服务器的 IP 地址是 192.168.140.10（ -h是 --host 的缩写），若连接本地服务器可省略此参数（默认连接 localhost）。

• > SHOW PROCESSLIST;       //在数据库服务器查看连接

1、修改用户信息

修改用户信息后无需刷新权限（FLUSH PRIVILEGES），但用户需重新登录才能应用新设置。

• select user, host from mysql.user;        //mysql库中的user表
• select count(*) as 用户数量 from mysql.user;      //所有用户数量

① 新建用户：

• 用户名：自定义的用户名（区分大小写）
• 登陆主机：限制用户登录的 IP / 主机，常用值：
  • lcoalhost：仅允许本地登录（通过mysql.sock）
  • 具体IP（如192.168.1.10）：仅允许该 IP 登录。
  • 192.168.1.%：允许指定网段（如 192.168.1.x）登录。
  • %：允许从任何主机登录。
• 密码：用户登录密码。密码需要英文大小写、数字、特殊符号。
• >INSTALL  PLUGIN validate_password SONAME 'validate_password.so';   //使用插件
• >SET GLOBAL validate_password.policy = LOW;    //降低策略等级为 LOW（仅检查长度）
• >SET GLOBAL validate_password.mixed_case_count = 0;   //允许不区分大小写
• sudo vi /etc/my.cnf    //打开配置文件，之后重启服务sudo systemctl restart mysqld

        

        

② 删除用户：

        

③ 修改用户：

（1）修改密码

        

        

（2）修改登录主机

        

（3）修改认证插件

        

④ 查询用户

        

 

2、权限操作（GRANT）

• GRANT 权限 ON 作用范围 TO '用户名'@'登录主机' [ WITH GRANT OPTION ];     //授予权限

版本	操作方式
MySQL 5.X	GRANT 可直接授权给不存在的用户（自动创建）
MySQL 8.0	必须先CRETE USER, 再 GRANT 授权

• REVOKE 权限1,权限2 ON 作用范围 FROM '用户名'@'登录主机';   //移除权限
• SHOW GRANTS FOR '用户名'@'登录主机';    //查看权限

        USAGE是 MySQL 中的一个特殊权限，表示 “无任何实际权限”，仅代表该用户存在（可以登录，但无法执行任何操作）。

MySQL ：： MySQL 8.0 参考手册 ：： 8.2.2 MySQL 提供的权限

• 权限
  • 数据操作：select、insert、update、delete
  • 结构操作：create、alter、drop
  • 管理权限：super、shutdown、process
  • 所有权限：all（表示除 GRANT OPTION 外的所有权限）
• 作用范围：
  • 全局：*.*（所有库的所有对象）
  • 数据库级：库名.*（指定库的所有对象）
  • 表级：库名.表名（指定库的指定表）
  • 列级：库名.表名（列1，列2）（指定表的特定列）
• WITH GRANT OPTION：允许用户将自己的权限授予他人（谨慎使用）

---

待补充