sql重点知识学习笔记（1）

最新推荐文章于 2024-05-10 14:06:22 发布
最新推荐文章于 2024-05-10 14:06:22 发布
阅读量641 收藏 2
点赞数 1
分类专栏： 其他 文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xiapi3/article/details/108419086
版权

sql学习笔记


笔记由极客时间摘录得到

sql基础

基础中的基础

sql标准有两个：
sql92和sql99

sql按功能分为4个部分：
ddl：数据定义语言——负责，增删改数据库表
dml：数据操作语言——负责，增删改数据项
dcl：数据控制语言——负责，定义访问权限
dql：数据查询语言——负责，查询

eg：

SELECT name, hp_max FROM heros WHERE role_main = '战士'

sql书写和命名规则：

  • 表名、表别名、字段名、字段别名等都小写；
  • SQL 保留字、函数名、绑定变量等都大写。
  • 此外在数据表的字段名推荐采用下划线命名，比如 role_main 这种。
  • SQL 语句必须以分号结尾。

常用数据库：
MySQL
SQL Server
PostgreSQL
DB2
MongoDB

数据库分类：
键值对型：Redis，查找速度快，无法过滤，需要遍历
文档型：MongoDB，文档作为信息基本单位
全文索引型：Elasticsearch，Splunk，Solr，核心原理是“倒排索引”
关系型行存储型：Oracle，MySQL，SQL Server
关系型列存储型：Hadop，降低系统I/O，用于分布式文件系统，但功能有限
图形型：利用实体和对象映射到节点和边得关系，解决复杂关系问题

在这里插入图片描述

列式存储降低I/O：行式存储是把一行的数据都串起来进行存储，然后再存储下一行。
同样，列式存储是把一列的数据都串起来进行存储，然后再存储下一列。
这样做的好处，就是相邻的数据的数据类型是一样的，因此也更容易压缩。压缩之后就自然降低了IO

sql执行过程

oracle

在这里插入图片描述

  1. 语法检查：检查 SQL 拼写是否正确，如果不正确，Oracle 会报语法错误。
    语义检查：检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候，列名写错了，系统就会提示错误。语法检查和语义检查的作用是保证 SQL 语句没有错误。
  2. 权限检查：看用户是否具备访问该数据的权限。
  3. 共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存 SQL 语句和该语句的执行计划。Oracle 通过检查共享池是否存在 SQL 语句的执行计划，来判断进行软解析，还是硬解析。
  4. 优化器：优化器中就是要进行硬解析，也就是决定怎么做，比如创建解析树，生成执行计划。
  5. 执行器：当有了解析树和执行计划之后，就知道了 SQL 该怎么被执行，这样就可以在执行器中执行语句了。

那软解析和硬解析又该怎么理解呢？
在共享池中，Oracle 首先对 SQL 语句进行 Hash 运算，然后根据 Hash值在库缓存（Library Cache）中查找，如果存在 SQL语句的执行计划，就直接拿来执行，直接进入“执行器”的环节，这就是软解析。
如果没有找到 SQL 语句和执行计划，Oracle就需要创建解析树进行解析，生成执行计划，进入“优化器”这个步骤，这就是硬解析。

共享池是 Oracle 中的术语，包括了库缓存，数据字典缓冲区等。我们上面已经讲到了库缓存区，它主要缓存 SQL 语句和执行计划。而数据字典缓冲区存储的是 Oracle 中的对象定义，比如表、视图、索引等对象。当对 SQL 语句进行解析的时候，如果需要相关的数据，会从数据字典缓冲区中提取。

注意：为了提升 SQL 的执行效率，我们应该尽量避免硬解析，因为在 SQL 的执行过程中，创建解析树，生成执行计划是很消耗资源的。

如何避免硬解析，尽量使用软解析呢？在 Oracle 中，绑定变量是它的一大特色。绑定变量就是在 SQL 语句中使用变量，通过不同的变量取值来改变 SQL 的执行结果。这样做的好处是能提升软解析的可能性，不足之处在于可能会导致生成的执行计划不够优化，因此是否需要绑定变量还需要视情况而定。
普通查询：
SQL> select * from player where player_id = 10001;
绑定变量：
SQL> select * from player where player_id = :player_id;
这两个查询语句的效率在 Oracle 中是完全不同的。如果你在查询 player_id = 10001 之后，还会查询 10002、10003 之类的数据，那么每一次查询都会创建一个新的查询解析。而第二种方式使用了绑定变量，那么在第一次查询之后，在共享池中就会存在这类查询的执行计划，也就是软解析。

注意：因此我们可以通过使用绑定变量来减少硬解析，减少 Oracle 的解析工作量。但是这种方式也有缺点，使用动态 SQL 的方式，因为参数不同，会导致 SQL 的执行效率不同，同时 SQL 优化也会比较困难。

MySQL

架构：C/S
服务端：mysqld
在这里插入图片描述

  1. 连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
  2. SQL 层：对 SQL 语句进行查询处理；
  3. 存储引擎层：与数据库文件打交道，负责数据的存储和读取。

其中sql层：
在这里插入图片描述

  1. 查询缓存：Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。
  2. 解析器：在解析器中对 SQL 语句进行语法分析、语义分析。
  3. 优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引来检索等。
  4. 执行器：在执行之前需要判断该用户是否具备权限，如果具备权限就执行 SQL 查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

MySQL 中的流程是：SQL 语句→缓存查询→解析器→优化器→执行器。这一部分中，MySQL 和 Oracle 执行 SQL 的原理是一样的。

MySQL特点，可以选择存储引擎，特点：

  1. InnoDB 存储引擎：它是 MySQL 5.5 版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等。
  2. MyISAM 存储引擎：在 MySQL 5.5 版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少。
  3. Memory 存储引擎：使用系统内存作为存储介质，以便得到更快的响应速度。不过如果 mysqld 进程崩溃，则会导致所有的数据丢失，因此我们只有当数据是临时的情况下才使用Memory 存储引擎。
  4. NDB 存储引擎：也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的 RAC 集群。
  5. Archive 存储引擎：它有很好的压缩机制，用于文件归档，在请求写入时会进行压缩，所以也经常用来做仓库。

查看MySQL执行时的资源使用情况
设置是否开启资源监测：

mysql> select @@profiling;

profiling=0关闭
profiling=1开启

开启资源监测后，执行一个sql语句，然后执行下面语句可查看上一条资源信息：

mysql> show profile;

查询当前会话所有profiles：

mysql> show profiles;

在这里插入图片描述
如果需要查看指定的资源信息：

mysql> show profile for query 2;

在 8.0 版本之后，MySQL 不再支持缓存的查询，原因我在上文已经说过。一旦数据表有更新，缓存都将清空，因此只有数据表是静态的时候，或者数据表很少发生变化时，使用缓存查询才有价值，否则如果数据表经常更新，反而增加了 SQL 的查询时间。

查询当前数据库版本：

select version();

DDL的注意点

在 DDL 中，我们常用的功能是增删改，分别对应的命令是 CREATE、DROP 和 ALTER。需要注意的是，在执行 DDL 的时候，不需要 COMMIT，就可以完成执行任务。

新建表

分析一段标准的DDL：

DROP TABLE IF EXISTS `player`;
CREATE TABLE `player`  (
  `player_id` int(11) NOT NULL AUTO_INCREMENT,
  `team_id` int(11) NOT NULL,
  `player_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `height` float(3, 2) NULL DEFAULT 0.00,
  PRIMARY KEY (`player_id`) USING BTREE,
  UNIQUE INDEX `player_name`(`player_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

varchar(255)：可变长度字符串，最长255，单位是字节，一个字母一个字节，一个中文两个字节
nvarchar(255)：单位是字符，unicode，统一两个字节
语句结束符为；分号
NOT NULL：空值约束
auto_increment：主键自增
反引号：能避免与MySQL保留字冲突
CHARACTER SET utf8：字符编码
COLLATE utf8_general_ci：排序规则
UNIQUE INDEX：唯一索引
NORMAL INDEX：普通索引
DEFAULT：默认值
CHECK：取值范围约束：CHECK(height>=0 AND height<3)

排序规则：
utf8_general_ci：对大小写不敏感
utf8_bin：大小写敏感

注意：InnoDB，它是 MySQL5.5 版本之后默认的存储引擎

修改表

eg:

# 加字段
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;

数据表设计原则：

  1. 表数量尽可能少
  2. 数据表字段尽可能少
  3. 联合主键字段尽可能少
  4. 主键和外键尽可能多

查询表

eg：

# 查询所有
SELECT * FROM heros;
# 查询特定列，起别名
SELECT name AS n, hp_max AS hm, mp_max AS mm, attack_max AS am, defense_max AS dm FROM heros;
# 查询常数
SELECT '王者荣耀' as platform, name FROM heros;
# 同上
SELECT 123 as platform, name FROM heros;
# 去重
SELECT DISTINCT attack_range FROM heros;
# 多字段合一去重
SELECT DISTINCT attack_range, name FROM heros

注意：

  1. DISTINCT 需要放到所有列名的前面，如果写成SELECT name, DISTINCT attack_range FROM heros会报错。
  2. DISTINCT 其实是对后面所有列名的组合进行去重。

order by语句

  1. 可以跟多个列名，排列时先按照第一个排，然后依次比较
  2. ASC 增（默认），DESC 降
  3. 比价规则，如果为文本，需要看数据库设置的规则，（如果再创建字段时设置binary属性，则区分大小写）
  4. order by通常位于select语句最后
SELECT name, hp_max FROM heros ORDER BY hp_max DESC ;

SELECT name, hp_max FROM heros ORDER BY mp_max, hp_max DESC;

# MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 关键字
SELECT name, hp_max FROM heros ORDER BY hp_max DESC LIMIT 5;
# SQL Server 和 Access使用Top关键字
SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC;
# DB2，使用FETCH FIRST 5 ROWS ONLY这样的关键字
SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY;
# Oracle使用rownum关键字，注意：这条语句执行逻辑是：取5条数据→排序，想要逻辑一样，使用下面方法
SELECT name, hp_max FROM heros WHERE ROWNUM <=5 ORDER BY hp_max DESC;
# 使用子查询
SELECT name, hp_max FROM (SELECT name, hp_max FROM heros ORDER BY hp_max) WHERE ROWNUM <=5;

select执行顺序

# 书写顺序
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

# 执行顺序
# FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

注意：在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。

select *使用限制

使用*会加重数据库负担，但是和将列名全部列出来效率差不多。生产上不推荐使用select *

count 1和count *

在MySQL innoDB中：count (*) 和count(1) 都是对所有结果进行count本质没有区别，复杂度O(N)，全表扫描（循环计数）
在MyISAM中：由于有meta信息存了row_count值，所以复杂度O(1)

另外，InnoDB中，count (*) 和count(1) 统计行数，尽量采用二级索引，因为主键索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。对于查找具体的行来说，采用主键索引效率更高。

优化总结：
1、一般情况下：COUNT(*) = COUNT(1) > COUNT(字段)
所以尽量使用COUNT(*)，当然如果你要统计的是就是某个字段的非空数据行数，那另当别论。毕竟执行效率比较的前提是要结果一样才行。
2、如果要统计COUNT(*)，尽量在数据表上建立二级索引，系统会自动采用key_len小的二级索引进行扫描，这样当我们使用SELECT COUNT(*)的时候效率就会提升，有时候提升几倍甚至更高都是有可能的。

条件过滤

条件过滤主要使用where子句

比较运算符

在这里插入图片描述
注意： Access 不支持（!=），不等于应该使用（<>）。在 MySQL 中，不支持（!>）（!<）

逻辑运算符

在这里插入图片描述
注意：当 WHERE 子句中同时存在 OR 和 AND 的时候，AND 执行的优先级会更高，也就是说 SQL 会优先处理 AND 操作符，然后再处理 OR 操作符，如需改变优先级，使用括号即可

eg：

SELECT name, role_main, role_assist, hp_max, mp_max, birthdate
FROM heros 
WHERE (role_main IN ('法师', '射手') OR role_assist IN ('法师', '射手')) 
AND DATE(birthdate) NOT BETWEEN '2016-01-01' AND '2017-01-01'
ORDER BY (hp_max + mp_max) DESC

注意：对于日期，有可能是字符串格式，建议用DATE函数来转换成日期格式再比较

通配符

通配符的关键字：LIKE

  • %：匹配任意个字符
  • ?：匹配单个字符

注意：在 Access 中使用?来代替_，而且在 DB2 中是不支持通配符_的，因此你需要在使用的时候查阅相关的 DBMS 文档。

不过在实际操作过程中，我还是建议你尽量少用通配符，因为它需要消耗数据库更长的时间来进行匹配。即使你对 LIKE 检索的字段进行了索引，索引的价值也可能会失效。
如果要让索引生效，那么 LIKE 后面就不能以（%）开头，比如使用LIKE '%太%'或LIKE '%太’的时候就会对全表进行扫描。如果使用LIKE ‘太%’，同时检索的字段进行了索引的时候，则不会进行全表扫描

保持高效率的一个很重要的原因，就是要避免全表扫描，所以我们会考虑在 WHERE 及 ORDER BY 涉及到的列上增加索引。

为什么要在order by上加上索引有利于排序避免全表扫描？

关于ORDER BY字段是否增加索引：在MySQL中，支持两种排序方式：FileSort和Index排序。Index排序的效率更高，
Index排序：索引可以保证数据的有序性，因此不需要再进行排序。
FileSort排序：一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序，效率较低。

所以使用ORDER BY子句时，应该尽量使用Index排序，避免使用FileSort排序。当然具体优化器是否采用索引进行排序，你可以使用explain来进行执行计划的查看。

优化建议：

  1. SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，ORDER BY子句避免使用FileSort排序。
    当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
    一般情况下，优化器会帮我们进行更好的选择，当然我们也需要建立合理的索引。
  2. 尽量Using Index完成ORDER BY排序。
    如果WHERE和ORDER BY相同列就使用单索引列；如果不同使用联合索引。
  3. 无法Using Index时，对FileSort方式进行调优。

避免索引失效的手段：

  1. 不要在WHERE子句后面对字段做函数处理，同时也避免对索引字段进行数据类型转换
  2. 避免在索引字段上使用<>，!=，以及对字段进行NULL判断（包括 IS NULL, IS NOT NULL）
  3. 在索引字段后，慎用IN和NOT IN，如果是连续的数值，可以考虑用BETWEEN进行替换
    因为在WHERE子句中，如果对索引字段进行了函数处理，或者使用了<>,!=或NULL判断等，都会造成索引失效。

sql函数

内置函数：算术函数、字符串函数、日期函数、转换函数

eg：

# 算术函数
SELECT ABS(-2) #运行结果为 2。
SELECT MOD(101,3) #运行结果 2。
SELECT ROUND(37.25,1) #运行结果 37.3。
# 字符串函数
SELECT CONCAT('abc', 123) #运行结果为 abc123。
SELECT LENGTH('你好') #运行结果为 6。
SELECT CHAR_LENGTH('你好') #运行结果为 2。
SELECT LOWER('ABC') #运行结果为 abc。
SELECT UPPER('abc') #运行结果 ABC。
SELECT REPLACE('fabcd', 'abc', 123) #运行结果为 f123d。
SELECT SUBSTRING('fabcd', 1,3) #运行结果为 fab。
# 日期函数
SELECT CURRENT_DATE() #运行结果为 2019-04-03。
SELECT CURRENT_TIME() #运行结果为 21:26:34。
SELECT CURRENT_TIMESTAMP() #运行结果为 2019-04-03 21:26:34。
SELECT EXTRACT(YEAR FROM '2019-04-03') #运行结果为 2019。
SELECT DATE('2019-04-01 12:00:05') #运行结果为 2019-04-01。
# 转换
SELECT CAST(123.123 AS INT) #运行结果会报错。
SELECT CAST(123.123 AS DECIMAL(8,2)) #运行结果为 123.12。
SELECT COALESCE(null,1,2) #运行结果为 1。

日期函数：
在这里插入图片描述
注意：DATE 日期格式必须是yyyy-mm-dd 的形式。如果要进行日期比较，就要使用 DATE 函数，不要直接使用日期与字符串进行比较，我会在后面的例子中讲具体的原因。

转换函数：
在这里插入图片描述
CAST 函数在转换数据类型的时候，不会四舍五入，如果原数值有小数，那么转换为整数类型的时候就会报错。不过你可以指定转化的小数类型，在 MySQL 和 SQL Server 中，你可以用DECIMAL(a,b)来指定，其中 a 代表整数部分和小数部分加起来最大的位数，b 代表小数位数，比如DECIMAL(8,2)代表的是精度为 8 位（整数加小数位数最多为 8 位），小数位数为 2 位的数据类型。所以SELECT CAST(123.123 AS DECIMAL(8,2))的转换结果为 123.12。

使用sql会出现的问题：大部分 DBMS 会有自己特定的函数，这就意味着采用 SQL 函数的代码可移植性是很差的，因此在使用函数的时候需要特别注意。

sql聚合函数

聚合函数共5个：
在这里插入图片描述
eg：


SELECT COUNT(*) FROM heros WHERE hp_max > 6000

SELECT COUNT(role_assist) FROM heros WHERE hp_max > 6000

SELECT MIN(CONVERT(name USING gbk)), MAX(CONVERT(name USING gbk)) FROM heros

SELECT ROUND(AVG(DISTINCT hp_max), 2) FROM heros

SELECT COUNT(*), role_main FROM heros GROUP BY role_main

SELECT COUNT(*) as num, role_main, role_assist FROM heros GROUP BY role_main, role_assist HAVING num > 5 ORDER BY num DESC

注意：
count(字段)：会忽略null
count(*)：如果是*则不会
group by：按照多字段分组，则会罗列所有组合情况进行分组

HAVING和WHERE区别：WHERE 是用于数据行，而 HAVING 则作用于分组。HAVING 支持所有 WHERE 的操作，因此所有需要 WHERE 子句实现的功能，你都可以使用 HAVING 对分组进行筛选。

子查询

子查询分为关联子查询和非关联子查询

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

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

eg：

# 非关联子查询
SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player);
# 关联子查询：因为每次表 a 中的 team_id 可能是不同的，所以是关联子查询。如果是非关联子查询，那么从句计算的结果是固定的才可以
SELECT player_name, height, team_id FROM player AS a WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id)

特点：子查询结果固定=非关联子查询

EXIST子查询

特点是：where exist + 子查询
eg：

SELECT player_id, team_id, player_name FROM player WHERE NOT EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)

集合比较子查询类型

在这里插入图片描述
eg：

SELECT player_id, player_name, height FROM player WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002);

SELECT team_name, (SELECT count(*) FROM player WHERE player.team_id = team.team_id) AS player_num FROM team;

IN和EXIST区别和使用场景

总结：大 IN 小；小 EXIST 大；

连接查询

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

  • 笛卡尔积：交叉连接CROSS JOIN，全连接
  • 等值连接：where后判定等值
  • 非等值连接：where后逻辑判断：大于，小于，不等于，之间
  • 外连接：主表全显示，从表有对应就显示（sql92使用+标记主表）：left join和right join
  • 自连接：自己和自己连接查询，一般会有优化，效率高
  • 自然连接：sql99中，natural join，将连接表主键字段等值比较

eg：

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

SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id;
# 自连接，不好解释
SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克-格里芬' and a.height < b.height
# natural join，相当于：WHERE player.team_id = team.team_id。
SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team

注意：当使用表别名后就不能使用原表名

ON条件

eg：SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id;

USING条件

指定字段的等值连接：
eg:SELECT player_id, team_id, player_name, height, team_name FROM player JOIN team USING(team_id);
相当于：ON player.team_id = team.team_id

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

连接查询标准格式

建议采用如下格式进行连接查询：

SELECT ...
FROM table1
    JOIN table2 ON table1和table2的连接条件
        JOIN table3 ON table2和table3的连接条件

一些注意事项

  1. 不是所有数据库都支持全外连接
  2. oracle表没有别名as，直接跟别名即可
  3. sqlite只有左连接
  4. 连接表数量过多会降低查询效率
  5. 连接时一定要加条件
  6. 自连接比子查询效率高

视图的作用

视图是虚拟表，本身不具有数据。非长期存放，连接关闭后临时表自动释放。

可以简化复杂的sql查询，可以直接重用无需考虑细节。可以嵌套创建。

视图的优点：安全性，简单清晰

创建视图 create view

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

修改视图 alter view

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

删除视图 drop view

DROP VIEW view_name

注意：SQLite 不支持视图的修改，仅支持只读视图，也就是说你只能使用 CREATE VIEW 和 DROP VIEW，如果想要修改视图，就需要先 DROP 然后再 CREATE

利用视图进行格式化

 CREATE VIEW player_team
 AS SELECT CONCAT(player_name, '(' , team.team_name , ')') 
 AS player_team FROM player 
 JOIN team WHERE player.team_id = team.team_id

存储过程

类似于函数，可以接收参数并执行的一系列语句，返回结果

创建

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

注意：和视图一样，我们可以删除已经创建的存储过程，使用的是 DROP PROCEDURE。如果要更新存储过程，我们需要使用 ALTER PROCEDURE。

eg：

# 1+2+...+n
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

调用：CALL add_num(50)

注意：因为默认情况下 SQL 采用（；）作为结束符，这样当存储过程中的每一句 SQL 结束之后，采用（；）作为结束符，就相当于告诉 SQL 可以执行这一句了。但是存储过程是一个整体，我们不希望 SQL 逐条执行，而是采用存储过程整段执行的方式，因此我们就需要临时定义新的 DELIMITER，新的结束符可以用（//）或者（$$）。如果你用的是 MySQL，那么上面这段代码，应该写成下面这样：
eg：


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 ;

入参修饰符：
在这里插入图片描述
eg：

CREATE PROCEDURE `get_hero_scores`(
       OUT max_max_hp FLOAT,
       OUT min_max_mp FLOAT,
       OUT avg_max_attack FLOAT,  
       s VARCHAR(255)
       )
BEGIN
       SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
END

调用：

CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '战士');
SELECT @max_max_hp, @min_max_mp, @avg_max_attack;

注意：当参数为out时，参数名前必须加@

流程控制

BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。

DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。

SET：赋值语句，用于对变量进行赋值。

SELECT…INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

IF…THEN…ENDIF：条件判断语句，我们还可以在 IF…THEN…ENDIF 中使用 ELSE 和 ELSEIF 来进行条件判断。

CASE：CASE 语句用于多条件的分支判断
eg：

CASE 
  WHEN expression1 THEN ...
  WHEN expression2 THEN ...
  ...
    ELSE 
    --ELSE语句可以加，也可以不加。加的话代表的所有条件都不满足时采用的方式。
END

LOOP、LEAVE 和 ITERATE：LOOP 是循环语句，使用 LEAVE 可以跳出循环，使用 ITERATE 则可以进入下一次循环。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 BREAK，把 ITERATE 理解为 CONTINUE。

REPEAT…UNTIL…END REPEAT：这是一个循环语句，首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。

WHILE…DO…END WHILE：这也是循环语句，和 REPEAT 循环不同的是，这个语句需要先进行条件判断，如果满足条件就进行循环，如果不满足条件就退出循环。

存储过程的特点：首先存储过程可以一次编译多次使用。存储过程只在创造时进行编译，之后的使用都不需要重新编译，这就提升了 SQL 的执行效率。其次它可以减少开发工作量。将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，还能保证代码的结构清晰。还有一点，存储过程的安全性强，我们在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。最后它可以减少网络传输量，因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。同时在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可。
缺点也很明显：可移植性差，调试困难，版本更新容易出问题，不适合高并发

天落枫
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL语句基础知识
qq_44643302的博客
03-22 1684
SQL 1.数据库的三大范式 1、第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据线；也就是说：每列的值具有原子性，不可再分割。 2、第二范式(2NF)是在第一范式(1NF)的基础上建立起来得，满足第二范式(2NF)必须先满足第一范式(1NF)。如果表是单主键，那么主键以外的列必须完全依赖于主键；如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。 3、第三范式(3NF)是在第二范式的基础上建立起来的，即满足第三范式必须要先满足第二范式。第三范式(3NF)要求：表中的非
浅析PostgreSQL与SQL Server两者间的关键性差异
WPHunter的编程技术资料库！
01-02 1701
比较PostgreSQL和SQL Server将帮助您更好地了解这两个系统的可能优势和权衡，以及哪一个更适合您的目的。
SQL基础知识笔记
最新发布
Mr丶·Zhou博客
05-10 1042
SQL是一门大小写不敏感的语言，语句以英文半角分号（;）结尾，多行注释使用 /* ……*/，单行注释使用 – ……关键词：grant（赋予权限）、revoke（取消权限）、commit（提交）、rollback（回滚）*内层的查询为子查询，外层的查询为主查询。
SQL笔记
weixin_54394527的博客
04-12 777
SQL(Structured Query Language:结构化查询语言) 是用于管理关系数据库管理系统（RDBMS）。SQL 的范围包括数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制。
SQLServer连接Pgsql或mysql读写数据
奋斗鱼
10-30 2013
问题 想把SQLServer的数据库迁移到PgSql里面，因为两个数据库业务表的结构和字段都不一样，需要逐表进行按需抽取想要的字段并导入数据。 解决方案 可以在SqlServer里面创建链接服务器，链接到Pgsql的表，然后在SQLServer中把数据写入到Pgsql数据库。(SqlServer链接Mysql也是一样的方法)。 步骤1：创建ODBC数据源 在开始菜单中输入ODBC,找到ODBC64位数据源(我的是64位系统，如果后续链接服务器连接时提示架构不匹配，就要用ODBC32位来创建)。
SqlServer数据迁移Pgsql方案
奋斗鱼
11-02 3791
将SqlServer数据库迁移到pgsql，分别尝试了以下几种方案。(最终用方法四解决) 方法一：Sqlserver链接服务器(不支持大数据迁移) 通过在Sqlserver中创建链接服务器，直接链接到pgsql。然后在查询分析器中通过[链接服务器].[数据库名].[表名]的方式读写pgsql的数据表。 此方式简单方便，在sqlserver中将查询的数据直接写入到pgsql数据表，能达到迁移数据的目的。 问题：只能导入少量数据(几千条)，导入大几万条数到pgsql库时会出现“管道结束”的报错，链接服务
MySQL架构与内部模块学习笔记
08-14
本笔记主要探讨了MySQL 5.7版本的架构和内部组件，重点关注了SQL查询的执行流程、通信协议、连接管理以及查询缓存等方面。 首先，了解一条SQL查询的执行过程至关重要。当客户端（例如一个程序或数据库工具）想要...
linq to sql 语法 学习笔记
09-05
本文将重点讲解 LINQ to SQL 中的 `Where` 和 `Select/Distinct` 操作符的使用。 ### 一、`Where` 操作符 `Where` 操作符用于过滤数据，其功能类似于 SQL 中的 `WHERE` 子句。它可以接受不同的形式： 1. **简单...
MySQL学习笔记1-数据库基础.md
08-22
需要学习数据库基础知识和SQL语言的初学者或开发人员。文中具体的示例代码有助于操作实践。 能学到什么: 通过学习可以掌握关系数据库的基本概念,熟练使用SQL语言进行数据库操作,以及学习数据库的备份与恢复方法。 ...
2021年-Hive大数据仓库-期末考试知识点重点-笔记整理.pdf
10-11
1. 接收SQL语句 2. 进行词法分析和语法分析 3. 进行语义分析 4. 生成逻辑计划，得到算树 5. 逻辑计划优化，包括剪枝和谓词下推等 6. 物理计划生成，将逻辑计划生成包含由MapReduce任务组成的DAG的物理计划 7. 物理...
马士兵hibernate学习笔记.docx
04-01
《马士兵Hibernate学习笔记》是一份详尽的教程，涵盖了Hibernate框架的核心概念和技术要点。Hibernate作为Java领域中广泛使用的对象关系映射（ORM）框架，它有效地解决了Java应用程序与数据库之间的数据交互问题，...
sql学习笔记
08-10
对w3school的sql课程进行了整理，doc格式方便阅读和打印
sql筆記整理
03-07
sql筆記整理，很有用的
数据迁移——将数据由SQLServer迁移到PostgreSQL中
ZZY1078689276的专栏
04-25 5670
文章目录开发背景源码核心依赖Java源码运行结果 开发背景 源码 核心依赖 <dependencies> <!-- eureka注册中心 --> <dependency> <groupId>org.springframework.cloud</groupId> <artifactId&...
SQL笔记 -- 黑马程序员
weixin_74814064的博客
03-14 1140
执行时机不同： where是分组之前进行过滤不满足where条件，不参与分组;而having是分组之后对结果进行过滤。判断条件不同:：where不能对聚合函数进行判断，而having可以。分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。注意：修改语句的条件可以有，如果没有，则修改整张表的所有数据。删除指定表，并重新创建该表。NULL 不进行计算。查询当前数据库所有表。查询指定表的建表语句。
常用SQL笔记
ouYang的博客
09-22 1044
SQL笔记 数据库相关概念 1.DB：数据库，保存一组有组织的数据的容器 2.DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据 3.SQL：结构化查询语言，用于和DBMS通信的语言 基本SQL操作 1.操作数据库 -- 查询数据库 show databases; -- 创建数据库 create database newDatabase; -- 删除数据库 drop data...
show tables not like xxx
lein_wang的专栏
09-16 4406
有时候，比如备份数据库，需要排除一些表 SHOW TABLES WHERE Name NOT LIKE 'tree%'; http://stackoverflow.com/questions/3698891/mysql-what-is-the-right-syntax-for-not-like http://stackoverflow.com/questions/4432062/matc
【数据库】SQL之重点知识点总结
csdn_baotai的博客
09-18 2220
SQL之重点知识点总结 ##事务概念 事务是一种机制、是一种操作序列，它包含了一组数据库操作命令， 这组命令要么全部执行，要么全部不执行。因此事务是一个不可分割的工作逻辑单元。 在数据库系统上执行并发操作时事务是作为最小的控制单元来使用的。 这特别适用于多用户同时操作的数据通信系统。 eg:订票、银行、保险公司以及证券交易系统等。 ##事务属性 事务4大属性(ACID特性)： 原子性(...
10分钟学会 SQL 语言核心知识点！
海纳百川
09-10 2023
在Java Web开发中必不可少的就是 SQL 语言！万能的 SQL 语言可以使你自由的徜徉于各种数据库的海洋中。而学习 SQL 语言一点都不困难，小编整理了整理了 SQL 语言常用的语法和命令，要让大家熟练掌握SQL语句，掌握一定的数据库查询技巧及SQL语句优化技巧。快点来学习吧~ 一些最重要的 SQL 命令： 　　1）SELECT
sql server学习笔记
03-20
很高兴为您提供关于 SQL Server 学习笔记的建议。SQL Server 是一种关系型数据库管理系统，它可以用于存储和管理数据。如果您想学习 SQL Server，您可以从以下几个方面入手： 1. 学习 SQL 语言：SQL 是一种用于访问和管理关系型数据库的语言。您可以通过学习 SQL 语言来了解 SQL Server 的基本操作和语法。 2. 学习 SQL Server 的基本概念：了解 SQL Server 的基本概念，如数据库、表、列、行等，可以帮助您更好地理解 SQL Server 的工作原理。 3. 学习 SQL Server 的管理和维护：学习如何管理和维护 SQL Server 数据库，包括备份和恢复、性能优化、安全性等方面。 4. 学习 SQL Server 的高级功能：学习 SQL Server 的高级功能，如存储过程、触发器、索引等，可以帮助您更好地利用 SQL Server 来解决实际问题。 希望这些建议对您有所帮助。如果您有任何其他问题，请随时问我。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值