SQL必知必会（一）语法基础篇

努力添砖java

已于 2024-02-26 16:35:39 修改

阅读量875

点赞数 20

分类专栏：工作中复习文章标签： sql 数据库

于 2024-02-26 16:26:52 首次发布

本文链接：https://blog.csdn.net/qq_51098044/article/details/136240458

版权

工作中复习专栏收录该内容

1 篇文章 0 订阅

订阅专栏

这是博主工作闲时的一些日常学习记录，有些之前很熟悉的，但工作中不常用，慢慢就遗忘了，在这里记录，也是为了激励自己坚持复习，如果有能帮到你，那我将感到非常的荣幸~

MySQL 中对一条 SQL 语句的执行时间进行分析

1）检查profiling 是否开启，开启它可以让 MySQL 收集在 SQL 执行时所使用的资源情况
2）

select @@profiling; //查看
set profiling=1;//1是开启，0是未开启

3）执行sql语句，可以一条或者多条
4）当前会话所产生的所有

profiles show profiles;//(所有) 
show profile;//(上一次)
show profile for query 2;//(指定的Query ID)

DDL语言中，修改表结构的ALTER语句

ALTER TABLE player ADD (age int(11));
ALTER TABLE player RENAME COLUMN age to player_age;
ALTER TABLE player MODIFY (player_age float(3,1));
ALTER TABLE player DROP COLUMN player_age;

搜索常量 SELECT

SELECT '王者荣耀' as platform, name FROM heros

以上可以增添常数列
如果常数是个字符串，那么使用单引号（‘’）就非常重要了，比如‘王者荣耀’。单引号说明引号中的字符串是个常数，否则 SQL 会把王者荣耀当成列名进行查询，但实际上数据表里没有这个列名，就会引起错误。如果常数是英文字母，比如’WZRY’也需要加引号。如果常数是个数字，就可以直接写数字，不需要单引号

DISTINCT去重放置位置

需要放到所有列名的前面 DISTINCT其实是对后面所有列名的组合进行去重

ORDER BY 排序规则

ORDER BY 对数值类型字段的排序规则是按照大小，但如果排序字段类型为文本数据，就需要参考数据库的设置方式了，这样才能判断 A 是在 B 之前，还是在 B 之后。比如使用 MySQL 在创建字段的时候设置为 BINARY 属性，就代表区分大小写。ORDER BY 可以使用非选择列进行排序。通常位于 SELECT 语句的最后一条子句。

不同数据库系统下的分页关键字

mysql — limit
SQL Server、Access — top
DB2 — FETCH FIRST 5 ROWS ONLY
oracle — ROWNUM

SELECT 通配符相关

不等于 != <> %零个或多个 _标识一个
只有当 LIKE 语句后面不用通配符，并且对字段进行索引的时候才不会对全表进行扫描
避免全表扫描,会考虑在 WHERE 及 ORDER BY 涉及到的列上增加索引

SELECT获取时间函数

时间格式
SELECT CURRENT_TIME();
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT CURRENT_TIMESTAMP();
SELECT EXTRACT(YEAR FROM ‘2019-04-03’);
SELECT DATE(‘2019-04-01 12:00:05’);

SQL数值转换函数

SELECT CAST(123.123 AS DECIMAL(8,2));
//数据类型转换，参数是一个表达式，表达式通过AS关键词分割了2个参数，
//分别是原始数据和目标数据类型
SELECT COALESCE(null,1,2);
//返回第一个非空数值

关于HAVING

HAVING 支持所有 WHERE 的操作，因此所有需要 WHERE 子句实现的功能，你都可以使用 HAVING 对分组进行筛选。

关于子查询分类

子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做非关联子查询。

同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为关联子查询。

关联子查询通常也会和 EXISTS 一起来使用，EXISTS 子查询用来判断条件是否满足，满足的话为 True，不满足为 False。
（NOT EXISTS用法同理）

//想要看出场过的球员都有哪些
SELECT player_id, team_id, player_name 
FROM player 
WHERE EXISTS (
	SELECT player_id FROM player_score 
	WHERE player.player_id = player_score.player_id
	)

也可以在子查询中使用in

实际上在查询过程中，在我们对 cc 列建立索引的情况下，我们还需要判断表 A 和表 B 的大小。在这里例子当中，表 A 指的是 player 表，表 B 指的是 player_score 表。
如果表 A 比表 B 大，那么 IN 子查询的效率要比 EXIST 子查询效率高，因为这时 B 表中如果对 cc 列进行了索引，那么 IN 子查询的效率就会比较高。
同样，如果表 A 比表 B 小，那么使用 EXISTS 子查询效率会更高，因为我们可以使用到 A 表中对 cc 列的索引，而不用从 B 中进行 cc 列的查询。

还有ANY(任何值,别名SOME)、ALL (所有值)关键字必须与一个比较操作符一起使用才能参与集合的比较

常用的SQL标准

SQL 有两个主要的标准，分别是 SQL92 和 SQL99,也分别叫做 SQL-2 和 SQL-3 标准。92 和 99 代表了标准提出的时间，SQL92 就是 92 年提出的标准规范。

SQL92具有5 种连接方式，它们分别是笛卡尔积、等值连接、非等值连接、外连接（左连接、右连接）和自连接。

笛卡尔积也称为交叉连接，英文是 CROSS JOIN，它的作用就是可以把任意表进行连接。

左外连接，就是指左边的表是主表，需要显示左边表的全部行，而右侧的表是从表，（+）表示哪个是从表。(不同数据库管理系统使用时的差别，比如 Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。)

SQL：SELECT * FROM player, team where player.team_id = team.team_id(+)

相当于 SQL99 中的：（同理 RIGHT JOIN）

SQL：SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id

SQL99 中用 NATURAL JOIN 替代了自然连接（WHERE player.team_id = team.team_id）,就可以不用向SQL92再写连接条件，但需要自定要的时候就要用ON去链接条件。

SELECT player_id, team_id, player_name, height, team_name 
FROM player NATURAL JOIN team

USING 可以指定具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。同时使用 JOIN USING 可以简化 JOIN ON 的等值连接。

SELECT player_id, team_id, FROM player JOIN team USING(team_id)

SQL99中需要注意的是 MySQL 不支持全外连接(FULL JOIN)，否则的话全外连接会返回左表和右表中的所有行。当表之间有匹配的行，会显示内连接的结果。当某行在另一个表中没有匹配时，那么会把另一个表中选择的列显示为空值。

最后注意的是：

不是所有的 DBMS 都支持全外连接虽然 SQL99 标准提供了全外连接，但不是所有的 DBMS 都支持。不仅 MySQL 不支持，Access、SQLite、MariaDB 等数据库软件也不支持。不过在 Oracle、DB2、SQL Server中是支持的。
Oracle 没有表别名 AS为了让 SQL 查询语句更简洁，我们经常会使用表别名 AS，不过在 Oracle 中是不存在 AS 的，使用表别名的时候，直接在表名后面写上表别名即可，比如 player p，而不是 player AS p。
SQLite 的外连接只有左连接SQLite 是一款轻量级的数据库软件，在外连接上只支持左连接，不支持右连接，不过如果你想使用右连接的方式，比如table1 RIGHT JOIN table2，在 SQLite 你可以写成table2 LEFT JOIN table1，这样就可以得到相同的效果。

视图

视图作为一张虚拟表，帮我们封装了底层与数据表的接口。它相当于是一张表或多张表的数据结果集。在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。
创建视图: CREATE VIEW

CREATE VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition

修改视图：ALTER VIEW
删除视图：DROP VIEW

作用：1.完成复杂的连接 2.对数据进行格式化 3.计算字段

存储过程

存储过程是程序化的 SQL，可以直接操作底层数据表，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。存储过程可以说是由 SQL 语句和流控制语句构成的语句集合，它和我们之前学到的函数一样，可以接收输入参数，也可以返回输出参数给调用者，返回计算结果。
创建

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    需要执行的语句
END

删除已经创建的存储过程，使用的是 DROP PROCEDURE。
更新存储过程，我们需要使用 ALTER PROCEDURE。
举例计算1+2+3+…+n的值

DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
       DECLARE i INT;
       DECLARE sum INT;
       
       SET i = 1;
       SET sum = 0;
       WHILE i <= n DO
              SET sum = sum + i;
              SET i = i +1;
       END WHILE;
       SELECT sum;
END //
DELIMITER ;

DELIMITER // 意思是让//作为结束符号，一般sql语句;作为结束语句，但这个存储过程用;一条一条执行会影响程序效率，就选择采用整段执行，就定义了字符（//或者$$）去作为结束符，而最后DELIMITER ;是恢复默认设置

SET：赋值语句，用于对变量进行赋值。
SELECT…INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

参数类型: IN OUT INOUT
INOUT，既用于存储过程的传入参数，同时又可以把计算结果放到参数中，调用者可以得到返回值。
IN 参数必须在调用存储过程时指定，而在存储过程中修改该参数的值不能被返回。
OUT 参数和 INOUT 参数可以在存储过程中被改变，并可返回。

CREATE PROCEDURE `get_sum_score`(
        OUT sum_max_hp FLOAT,
        IN s VARCHAR(255)
    )
BEGIN
    SELECT
        sum(hp_max)
    FROM heros 
    WHERE role_main = s
    INTO sum_max_hp;
END

调用存储过程

CALL get_sum_score(@sum_max_hp,'战士')
SELECT @sum_max_hp;

常用流控制语句

IF…THEN…ENDIF：条件判断语句，我们还可以在 IF…THEN…ENDIF 中使用 ELSE 和 ELSEIF 来进行条件判断。
CASE：CASE 语句用于多条件的分支判断，使用的语法是下面这样的。
LOOP、LEAVE 和 ITERATE：LOOP 是循环语句，使用 LEAVE 可以跳出循环，使用 ITERATE 则可以进入下一次循环。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 BREAK，把 ITERATE 理解为 CONTINUE。
REPEAT…UNTIL…END REPEAT：这是一个循环语句，首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。
WHILE…DO…END WHILE：这也是循环语句，和 REPEAT 循环不同的是，这个语句需要先进行条件判断，如果满足条件就进行循环，如果不满足条件就退出循环。

优缺点
优点：

一次编译多次使用，封装成模块，多次使用。
可以设置用户使用权限，保证安全性。
减少网络的传输量和在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可。

缺点：

可移植性差，不能跨数据库移植。
调试困难，有些DBMS不支持，开发维护成本高。
版本管理困难，没有版本控制。
不支持高并发。

事务处理

事务处理ACID 原子性一致性隔离性持久性
一致性：一种正确状态变为另一种正确状态
隔离性：对其他事务不可见

在 MySQL 中存储引擎，InnoDB 是支持事务的，而 MyISAM 存储引擎不支持事务。

## 查看存储引擎
SHOW ENGINES

事务的常见控制语句

START TRANSACTION 或者 BEGIN，作用是显式开启一个事务。
COMMIT：提交事务。当提交事务后，对数据库的修改是永久性的。
ROLLBACK 或者 ROLLBACK TO[SAVEPOINT]，意为回滚事务。意思是撤销正在进行的所有没有提交的修改，或者将事务回滚到某个保存点。
SAVEPOINT：在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。
RELEASE SAVEPOINT：删除某个保存点。
SET TRANSACTION: 设置事务的隔离级别。

使用事务有两种方式，分别为隐式事务和显式事务。隐式事务实际上就是自动提交，Oracle 默认不自动提交，需要手写 COMMIT 命令，而 MySQL 默认自动提交，当然我们可以配置 MySQL 的参数：

mysql> set autocommit =1;  //开启自动提交 （MYSQL默认） 每条 SQL 语句都会自动进行提交
mysql> set autocommit =0;  //关闭自动提交

如果在事务开始之前设置了，SQL自动提交就会受影响

SET @@completion_type = 1

completion=0，这是默认情况。也就是说当我们执行 COMMIT 的时候会提交事务，在执行下一个事务时，还需要我们使用START TRANSACTION 或者 BEGIN 来开启。
completion=1，这种情况下，当我们提交事务后，相当于执行了 COMMIT AND CHAIN，也就是开启一个链式事务，即当我们提交事务之后会开启一个相同隔离级别的事务，相当于加入START TRANSACTION 或者 BEGIN。
completion=2，这种情况下 COMMIT=COMMIT AND RELEASE，也就是当我们提交后，会自动与服务器断开连接。

事务隔离

SQL-92 标准中进行了定义三种异常情况级别分别为脏读（Dirty Read）、不可重复读（Nonrepeatable Read）和幻读（Phantom Read）。
脏读：读到未提交的数据
不可重复读：同一记录两次读取结果不同 重点在于UPDATE或DELETE
幻读：事务A查询，事务B更改数据，事务A再次查询，前后结果条数不同 重点在于INSERT

四种隔离等级
读未提交（READ UNCOMMITTED ）
读已提交（READ COMMITTED）-----解决脏读（属于 RDBMS 中常见的默认隔离级别（比如说 Oracle 和 SQL Server））
可重复读（REPEATABLE READ）-----解决脏读和不可重复读（MySQL 默认的隔离级别就是可重复读。）
可串行化（SERIALIZABLE）-----解决幻读

查看当前会话的隔离等级

SHOW VARIABLES LIKE 'transaction_isolation';

设置隔离等级

## 读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

## 可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE-READ;

游标

引入: SQL 本身是以关系模型和集合论为基础的。比起如何去做，更看重最后结果（获取什么）。而游标让 SQL 这种面向集合的语言有了面向过程开发的能力。

游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用，可以通过操作游标来对数据行进行操作。

使用步骤

## 1. 定义游标  select_statement（为结果集 可以写为select * from table）

## 适用于MySQL，SQL Server，DB2 和 MariaDB
DECLARE cursor_name CURSOR FOR select_statement
## 适用于Oracle 或者 PostgreSQL
DECLARE cursor_name CURSOR IS select_statement

## 2. 打开游标
OPEN cursor_name


## 3. 获取数据 (这句的作用是使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中，游标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。)
FETCH cursor_name INTO var_name ...

## 4. 关闭游标
CLOSE cursor_name

## 5. 释放游标 
DEALLOCATE cursor_namec

使用实例：（累加）

CREATE PROCEDURE `calc_hp_max`()
BEGIN
       -- 创建接收游标的变量
       DECLARE hp INT;  
       -- 创建总数变量 
       DECLARE hp_sum INT DEFAULT 0;
       -- 创建结束标志变量  
       DECLARE done INT DEFAULT false;
       -- 定义游标     
       DECLARE cur_hero CURSOR FOR SELECT hp_max FROM heros;
       
       OPEN cur_hero;
       read_loop:LOOP 
       FETCH cur_hero INTO hp;
       SET hp_sum = hp_sum + hp;
       END LOOP;
       CLOSE cur_hero;
       SELECT hp_sum;
END

可以浅浅把游标理理解成“指针”，但平时元素遍历数组到最后一个字母的时候没有指定长度会存在溢出的情况，所以还需要添加限定条件，防止其游标溢出。

CREATE PROCEDURE `calc_hp_max`()
BEGIN
       -- 创建接收游标的变量
       DECLARE hp INT;  

       -- 创建总数变量 
       DECLARE hp_sum INT DEFAULT 0;
       -- 创建结束标志变量  
       DECLARE done INT DEFAULT false;
       -- 定义游标     
       DECLARE cur_hero CURSOR FOR SELECT hp_max FROM heros;
       
       -- 指定游标循环结束时的返回值  
       DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;  
       
       OPEN cur_hero;
       read_loop:LOOP 
       FETCH cur_hero INTO hp;
       -- 判断游标的循环是否结束  
       IF done THEN  
                     LEAVE read_loop;
       END IF; 
              
       SET hp_sum = hp_sum + hp;
       END LOOP;
       CLOSE cur_hero;
       SELECT hp_sum;
END

ORM存在的意义

ORM 对象关系映射开发基于ORM方式操作数据库，只需要关注业务逻辑，不需要了解底层怎么操作数据库，同时对于已经定义好的对象模型，即使底层数据库被替换，写好的业务逻辑仍可以使用。

ORM	DB
类	数据库
实例对象	数据行
属性	字段

列式数据库好处

降低系统的I/O。行式数据库是将数据按行存储，而列式数据库则是将数据按列存储，这样相邻的数据类型一致，类型一致更适合压缩，压缩后相比于行式降低系统的I/O。
更适合查询多的情景。数据处理分为OLTP(联机事务处理)和OLAP(联机分析处理)。OLTP指修改数据频繁，查询少；OLAP指市场数据分析查询数据多，修改少，更适合大批量数据的查询，但实时性不如行式数据库。而传统的RDBMS(关系型数据库) 都是行式存储。

查询优化相关

查询效率
1）一般情况下，三者执行的效率为 COUNT(*)= COUNT(1)>
COUNT(字段)。我们尽量使用COUNT(*)，当然如果你要统计的是某个字段的非空数据行数，则另当别论，毕竟比较执行效率的前提是结果一样才可以。
2）如果要统计COUNT(*)，尽量在数据表上建立二级索引，系统会自动采用key_len小的二级索引进行扫描，这样当我们使用SELECT COUNT(*)的时候效率就会提升，有时候可以提升几倍甚至更高。
在 MySQL 中，LIMIT关键词是最后执行的，如果可以确定只有一条结果，是否就起不到查询优化的效果了吧，因为LIMIT 1是对最后的结果集过滤，如果结果集本来就只有一条，那就没有什么用了。
不是这样的，如果只有一条数据，加上limit 1 就不会全表扫描了，但是数据做了唯一索引，就不会全表扫描，limit 1就没什么用了。
针对ORDER BY后字段加索引的问题
1）SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描，在 ORDER BY 子句避免使用 FileSort 排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。一般情况下，优化器会帮我们进行更好的选择，当然我们也需要建立合理的索引。
2）尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
3）无法使用 Index 时，需要对 FileSort 方式进行调优。
ORDER BY 就是对记录进行排序。如果你在 ORDER BY 前面用到了 GROUP BY，实际上这是一种分组的聚合方式，已经把一组的数据聚合成为了一条记录，再进行排序的时候，相当于对分的组进行了排序。
SELECT语句执行顺序
1）FROM 子句组装数据（包括通过 ON 进行连接）；
2）WHERE 子句进行条件筛选；
3）GROUP BY 分组；
4）使用聚集函数进行计算；
5）HAVING 筛选分组；
6）计算所有的表达式；
7）SELECT 的字段；
8）ORDER BY 排序；
9）LIMIT 筛选。
EXISTS和IN区别
EXISTS是左表在外部循环。 IN是右表（或子语句中的表）在外部循环。所以左表小就用EXISTS，反之用IN。
varchar和nvarchar区别
1）varchar(n)是n个字节，非Unicode字符。（英文字母占1个字节，中文占2个字节）
2）nvarchar(n)是n个字符，Unicode字符。（英文字母或者中文都是占用2个字节）

事务相关–补充

1）在 MySQL 中 BEGIN 用于开启事务，如果是连续 BEGIN，当开启了第一个事务，还没有进行 COMMIT 提交时，会直接进行第二个事务的 BEGIN，这时数据库会隐式地 COMMIT 第一个事务，然后再进入到第二个事务。
2）如果在语句里强行进行 COMMIT，数据库会将这个事务中成功的操作进行提交，它会认为你觉得已经是 ACID 了（就是你认为可以做 COMMIT 了，即使遇到了一些小问题也是可以忽略的，例如提交相同的数据，只会保存第一条数据）。