SQL,何必在忆之一(基础篇)
还记得那是在2018年的十月的某个日子，虽早已入秋，但夏日的炎热却丝毫不减退散。那时的我正捧着一本SQL Server程序设计的白蓝皮书与九栋315的狗子们，匆匆的走向j1-402进行了我们人生中第一次SQL数据库的学习，时光总是戏人，现实总是玩笑。当初的几个伙伴都走向了各行各业，而唯有我编程课，问啥啥不会，写啥啥就废的我进入了IT行业。说来实在嘲讽，缅怀那些我错过的编程课，致那些年说过无数次“让我学SQL,根本不可能”，我承认我打脸了。正如此章的title一般，“SQL语句， 何必在忆？”
很久之前就学了SQL，然而又忘记，今天正式系统的性的回顾一下，温故而知新。可以为师矣
表属性
表的属性
存储引擎:
InnoDB（默认的）
字符集和排序规则:
utf8
utf8mb4
列的属性
约束(一般建表时添加):
primary key ：主键约束
设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。 作为聚簇索引
not null ：非空约束
列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0
unique key ：唯一键
列值不能重复
unsigned ：无符号
针对数字列，非负数。
其他属性:
key :索引
可以在某列上建立索引，来优化查询,一般是根据需要后添加
default :默认值
列中，没有录入值时，会自动使用default的值填充
auto_increment:自增长
针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）
comment : 注释
sql_mode
作用：影响sql执行行为，规范SQL语句的书写方式(例如除数不能为0)
可以使用select @sql_mode查看(各版本有所出入)
字符集(charset)及校对规则(Collation)
字符集：
- utf8：最大存储长度，单个字符最多3字节
- utf8mb4：最大存储长度，单个字符最多4字节
常用于建库建表时
create database dbname charset utf8mb4;
# 查看数据库的字符集合
show create database dbname;
校对规则
每种字符集，有多种校对规则(排序)，例如常见的ASCII编码表
show collation;
作用：影响排序的操作
数据类型
text类型
Number类型
*:这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。
Date类型
*即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。
什么是SQL语句
SQL语句是结构化查询语言(Structured Query Language)的简称,是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
SQL语句的类型
数据查询语言（DQL:Data Query Language）：其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其它类型的SQL语句一起使用。
数据操作语言（DML：Data Manipulation Language）：其语句包括动词INSERT、UPDATE和DELETE。它们分别用于添加、修改和删除。
事务控制语言（TCL）：它的语句能确保被DML语句影响的表的所有行及时得以更新。包括COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。
数据控制语言（DCL）：它的语句通过GRANT或REVOKE实现权限控制，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
数据定义语言（DDL）：其语句包括动词CREATE,ALTER和DROP。在数据库中创建新表或修改、删除表（CREATE TABLE 或 DROP TABLE）；为表加入索引等。
指针控制语言（CCL）：它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。
比较常用的有DDL(数据定义语言)`DCL(数据控制语言)\DML(数据操作语言)\DQL(数据查询语言) ¨K65K ¨K66K ¨G4G ¨K67K ¨K68K 在日常中DDL多用于库、表的管理. **库名与库属性** ¨G5G ¨K32K **表** ¨G6G ¨K35K ¨K69K 控制就是操作权限，而在DCL之中，主要有两个语法：GRANT,REVOKE ¨G7G  **DCL** 授权 ¨G8G 权限: ALL: 管理员(不包含“ Grant option”，给他人授权) 权限1，权限2，权限3...：普通人员(开发人员) Grant option 对象范围: 库，表 | "." | ---> chmod -R 755 / | 管理员 | | :---------: | :----------------------------: | :------: | | userName.* | ---> chmod -R 755 userName/ | 普通用户 | | userName.t1 | ---> chmod -R 755 userName/t1 | | ¨G9G Mysql授权表 | user | *.* | | | :---------: | :--------------: | ---- | | db | db.* | | | Tables_priv | db.table | | | Colums | 列 | | | Procs_priv | 存储过程中的权限 | | 回收权限 ¨G10G 拓展，忘记root密码了该怎么办？¨G11G **原理探究** 说到这个，那就不得不从mysql的server`层说起了，mysql的架构图如下(仅关键部分)
当我们忘记密码的时候,改怎么办呢？
这就对于我们平时对于mysql的模型有所考察了，如果你知道mysql的他内部到底是如何运行的,那么只需要在合适的地方，按照我们所想的给他“绕道而行”，是否就可以绕过这个密码验证了呢？答案是当然可以的。
首先我们介绍一下上面这副图中执行流程，当我们启动mysql服务的时候，系统会自动帮我们做一下这些事儿
- 首先提供可连接的协议，也就是提供服务
- 打开用户与密码校验，以处理将要连接的客户
- 验证成功，分配独立的连接线程
如果我们需要跳过密码校验,那么只需要做以下几件事。
- 让系统重启
- 在重启的过程中停掉用户与密码校验
这样我们就可以连接了，但是还不够。尽然已经停掉了。此时的我们无法修改密码.(跳过验证，而不是把验证功能移除了)
那么我们此时还需要把验证功能加载进来，然后对验证的表进行修改。
DML 数据操作语言
这个也是我们日常中用的最多的地方，应为建库表，改权限，改密码。修改等等什么的并不是每次都要嘛.这个也很好理解
DML 数据操作语言 对表中的数据行进行增、删、改
insert
# 语法
单行数据
INSERT INTO tableName(key1, key2, key3..) VALUES(value1, value2,value3...) [SELECT * FROM tableName]
# 多行数据
INSERT INTO tableName(key1, key2, key3..) VALUES \
(value1, value2,value3...)
(value1, value2,value3...)
(value1, value2,value3...);
...
[SELECT * FROM tableName]
插入时, key1,key2,key3 必须与value1，value2， value3 数量一致
插入对应字段
INSERT INTO tableName(key1, key3..) VALUES(value1, value3...) [SELECT * FROM tableName]
update
# 更新前我们一般都会先查表内数据
# 查询出对应表已存在所有行
DESC tableName;
# 查询对应表已存在数据
SELECT * FROM tableName; # * 可替换成字段名，查对应字段
# 更新数据
UPDATE student SET 字段名='新值' [WHERE 限定条件];
Eg:
创建一张新的student表
# 建表
CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
`sname` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '姓名',
`sage` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
`intime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表'
# 插入演示数据
INSERT INTO student(sname,sage) VALUES("赵一", 1),
("王二", 2),
("张三", 3),
("李四", 4);
数据库
需求一:
李四改名为“里斯”;
UPDATE student SET sname="里斯" WHERE sname = "李四";
# 或者
UPDATE student SET sname="里斯" WHERE id=4;
UPDATE student SET sname="里斯" WHERE sage=4;
修改后，如下所示
需求二:
将所有表内成员的年龄+10;
UPDATE student SET sage=sage + 10
# UPDATE student SET sage+=10(错误写法,开发时候用的什么sage ++， sage +=，在这里都不允许)
需求三:将所有表内成员的年龄+10,除了里斯
UPDATE student SET sage=sage + 10 WHERE sname != "里斯";
# 当然也可以这样写;
UPDATE student SET sage=sage + 10 WHERE sname = "赵一" OR sname = "王二" OR sname="张三" ;
AND: 执行均满足 OR: 满足其一执行 where 见下文
delete
# 删除指定数据
DELETE FROM tableName [WHERE id=1];
# 清空标中所有数据
DELETE FROM student;
truncate table student;
区别: delete: DML操作, 是逻辑性质删除,逐行进行删除,速度慢. truncate: DDL操作,对与表段中的数据页进行清空,速度快.
伪删除：用update来替代delete，最终保证业务中查不到（select）
1.添加状态列
ALTER TABLE stuent ADD state TINYINT NOT NULL DEFAULT 1 ;
SELECT * FROM stuent;
2. UPDATE 替代 DELETE
UPDATE stuent SET state=0 WHERE id=6;
3. 业务语句查询
SELECT * FROM stu WHERE state=1;
拓展
DELETE FROM student;
DROP TABLE student;
truncate table student;
以上三条删除语句有何区别？
同：三者都是删除语句，均可删除
异:
DELETE FROM student:
逻辑上逐行删除，数据过多，操作很慢
并没有真正的从磁盘上删除，知识在磁盘上打上标记，磁盘空间不立即释放。HWM高位线不会降低
DROP TABLE student;
将表结构(元数据)和数据行，物理层次删除
truncate truncate table student;
清空表段中的所有数据页，物理层次删除全表数据，磁盘空间立即释放。HWM高位线降低
DQL 数据查询语言
show类
show databases;
show CREATE DATABASE databaseName;
show table;
show CREATE TABLE table;
desc tableName;
select类
获取表中的数据行
# select @@xxx 查看系统参数
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@socket;
SELECT @@server_id;
# select 函数
SELECT NOW();
SELECT DATABASE();
SELECT USER();
SELECT CONCAT("hello world");
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;
手册 https://dev.mysql.com/doc/refman/8.0/en/sql-function-reference.html
select配合子句
select
FROM 表1，表2...,
WHERE 过滤条件1，过滤条件2，过滤条件3 ...
GROUP BY 条件列1，条件列2，条件列3 ...
# selct_list 列名
HAVING 过滤条件1，过滤条件2，过滤条件3 ...
ORDER BY 条件列1，条件列2，条件列3 ...
LIMIT 限制条件;
单表子句-from
SELECT 列1,列2 FROM 表
SELECT * FROM 表
# EG
# 查询student中所有的数据(不要对大表进行操作)
SELECT * FROM stu ;
# 查询stu表中,学生姓名和入学时间
SELECT sname , intime FROM stuent;
单表子句-where
SELECT col1,col2 FROM TABLE WHERE colN 条件;
# where 操作符(>、<、>=、 <=、 <>、in、like、and、or)
SELECT col1,col2 FROM TABLE WHERE = 条件;
# where 模糊查询
SELECT * FROM city WHERE district LIKE 'guang%';
% : 表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
_ : 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句
[] : 表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
# where配合between...and...
SELECT * FROM city WHERE population >1000000 AND population <2000000;
SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000;
group by
根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列
未分组分组列，使用聚合函数
聚合函数
**max()** ：最大值
**min()** ：最小值
**avg()** ：平均值
**sum()** ：总和
**count()** ：个数
group_concat() : 列转行
HAVING
需要在group by 之后，在做判断过滤使用(类似于where)
order by
实现先排序，by后添加条件列(默认从小到大) 逆序：后加DESC
distinct：去重复
SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city ;
联合查询- union all
-- 中国或美国城市信息
SELECT * FROM city
WHERE countrycode IN ('CHN' ,'USA');
SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'
说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
UNION 去重复
UNION ALL 不去重复
LIMIT 限制条件
限制查询
select * FROM 表名 LIMIT 限制条件
eg:
--- 只输出前1000条
select * FROM 表名 LIMIT 1000
--- 只输出前1000-2000条
select * FROM 表名 LIMIT 1000, 2000
select * FROM 表名 LIMIT 1000, 2000
相当于
select * FROM 表名 LIMIT 2000 OFFSET 1000
join 多表连接查询
内连接
查询li4家的地址
SELECT A.name,B.address FROM
A JOIN B
ON A.id=B.id --- 关联列
WHERE A.name='li4'
--- 相当于
SELECT A.name,B.address FROM
A JOIN B
ON A.id=B.id
WHERE A.name='li4'
外连接
驱动表建议使用 数据少的表 为驱动表
SELECT A.name,B.address FROM
A JOIN B
ON A.id=B.id --- 关联列
WHERE A.name='li4'
--- 相当于
SELECT A.name,B.address FROM
A left JOIN B
ON A.id=B.id
WHERE A.name='li4'
--- A left JOIN B 其中a位驱动表腾讯云开发者
