SQL 学习

最新推荐文章于 2024-06-04 08:08:00 发布
最新推荐文章于 2024-06-04 08:08:00 发布
阅读量628 收藏 13
点赞数
分类专栏： 数据分析学习 文章标签： SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u012470887/article/details/102587527
版权

SQL 学习

1. 概念

1.1 数据库基本概念

  • 数据(Data)：描述事物的符号记录
  • 数据库(DataBase, a.k.a DB)：存放数据的仓库
  • 数据库管理系统(DataBase Management System, a.k.a DBMS)：科学地组织和存储数据，高效地获取和维护数据。如 SQL Sever, Oracle, MySQL 等。
  • 数据库系统(DataBase System, a.k.a DBS)：在计算机系统中引入数据库后的系统

1.2 SQL 概述

  • SQL 全称为 Structured Query Language，即结构化查询语言。功能包括数据查询、数据操纵、数据定义和数据控制四个部分。
  • 数据定义语言(Data Definition Language, a.k.a DDL)：允许用户定义存储数据的结构和组织，以及存储数据项之间的关系
  • 数据检索语言：允许用户或应用程序从数据库中检索存储的数据并使用
  • 数据操纵语言(Data Manipulation Language, a.k.a DML)：允许用户或应用程序通过增、删、改来对数据库进行更新
  • 数据控制语言(Data Control Language, a.k.a DCL)：可以使用 SQL 语言来限制用户检索、添加和修改数据的能力，保护存储的数据不被未授权的用户所访问
  • 数据共享：可以使用 SQL 来协调多个并发用户共享数据，确保他们不会互相干扰
  • 数据完整性：SQL 语言在数据库中定义完整性约束条件，使它不会因不一致的更新或系统失败而遭到破坏

2. 查询

2.1 SELECT 语句基本结构

  • SELECT 语句可以从数据表中或视图中进行查询，并将查询结果以表格的形式返回，称为结果集
  • SELECT 的主要结构如下
SELECT select_list    # 指定要查询的列，* 代表所有列
[INTO new_table]    # 创建新表并将查询行插入新表中
FROM table_name    # 指定要查询的表，可能包括基表，视图和链接表
[WHERE search_condition]    # 搜索条件，无法使用聚合函数
[GROUP BY group_by_expression]    # 根据 group_by_expression 列中的值将结果集分组
[HAVING search_condition]    # 指定组或聚合的搜索条件。通常用于处理 GROUP BY 之后的数据
[ORDER BY order_expression [ASC | DESC]];    # 定义结果集中的行排列的顺序，默认为 ASC 升序

2.2 基本查询

  1. 单列
SELECT select_list FROM table_name;
  1. 多列
SELECT list1, ..., listn FROM table_name;
  1. 所有列
SELECT * FROM table_name;

2.3 别名的使用

  1. 使用方法
SELECT list "别名" FROM table_name;
SELECT list '别名' FROM table_name;
SELECT list 别名 FROM table_name;
SELECT list AS "别名" FROM table_name;
# 别名中有空格时必须使用引号
  1. 使用场景
  • 字段为英文或缩写时，增强可阅读性
SELECT list_name AS "名称" FROM table;
  • 区分多个表查询时出现的相同列名
SELECT table1.list AS "表一的列", table2.list AS "表二的列" 
FROM table1, table2 
WHERE table1.id = table2.id;
  • 为计算结果设置别名列
SELECT list1 AS "A", list2 AS "B", (list1 - list2) AS "DIFF" FROM table;
  • 为使用聚合函数的列设置别名
SELECT MAX(list1) AS "MAX", MIN(list2) AS "MIN" FROM table;

2.4 删除查询结果中的重复数据

SELECT [DISTINCT | ALL] list FROM table;    # DISTINCT 只能使用一次且必须放在第一位

2.5 限制查询结果

  1. SQL Server：
SELECT TOP n list FROM table;    # 查询前 n 行，n 介于 0 ~ 4294967295
  1. MySQL：
SELECT list FROM table LIMIT n;    # 查询前 n 行
SELECT list FROM table LIMIT m, n;    # 从第 m 行开始的 n 条数据信息（m 从 0 开始计算）
SELECT list FROM table LIMIT n OFFSET m;    # 从第 m 行开始的 n 条数据信息（m 从 0 开始计算）
  1. Oracle：
SELECT list FROM table WHERE ROWNUM <= n;    # 前 n 行数据

2.6 连接列值

  • 将多个列中的数据合并到一列，需指定列名，否则为空
SELECT list1 + list2 AS "NAME" FROM table;

2.7 查询中使用计算列

SELECT (list_a * list_b - list_c) / list_d AS "NAME" FROM table;

2.8 查询中使用表达式

  1. 数值表达式
SELECT list + 50 AS 加50 FROM table;
  1. 字符表达式
SELECT convert(char(2), list) + '个' AS NAME;
  1. 使用表达式创建新列
SELECT 1+1 AS NAME1, "字符"+"串列" AS NAME2

2.9 条件查询-WHERE

  1. 基本语法结构
SELECT list FROM table WHERE condition
  1. 常用比较运算符：=, >, <, >=, <=, !>, !<, <>, !=

2.10 范围查询-BETWEEN

  1. 查询两个数或日期之间的数据，包括 m 和 n
SELECT list1 FROM table WHERE list2 BETWEEN m AND n
  1. 查询不在两个数或日期之间的数据
SELECT list1 FROM table WHERE list2 NOT BETWEEN m AND n

2.11 日期查询

  1. GETDATE(), DATEADD()
SELECT list1 FROM table WHERE list2 
BETWEEN
DATEADD(DAY, -1, GETDATE())    # DATEADD 在这里为减1天
AND
GETDATE()    # 获得当前日期
  1. 计算两个日期的间隔天数
# **SQL Server**
DATEDIFF(datepart, startdate, enddate)
# datepart 为计算哪部分的时间间隔，如 year, month, day 等
# startdate enddate 分别为开始和终止日期
# **MySQL 中** 也可用 DATADIFF 函数，只计算天数
DATEDIFF(stardate, enddate)
  1. 按指定日期查询数据
DAY()
MONTH()
YEAR()

2.12 过滤数据

  • 逻辑运算符：优先级 NOT > AND > OR
  • IN 操作符
SELECT list1 FROM table WHERE list2 IN (v1, v2, ...)
SELECT list1 FROM table WHERE n IN (col1, col2)    # n 在 col1 列或 col2 列的行
SELECT list1 FROM table WHERE list2 NOT IN(v1, v2, ...)

2.13 格式化结果集

2.13.1 格式化日期

  1. SQL Server
CONVERT(date_type[(length)], expression [, style])
# date_type 为目标数据类型(可选长度)
# expression 为 DATETIME 类型的数据
# style 指定转换形式，例如 120 或者 20 为 yyyy-mm-dd hh:mi:ss
  • style 参数可选值
可选值输出格式
100 or 0mon dd yyyy hh:miAM(or PM)
101mm/dd/yy
102yy.mm.dd
103dd/mm/yy
104dd.mm.yy
105dd-mm-yy
106dd mm yy
107mm dd,yy
108hh:mm:ss
109 or 9mon dd yyyy hh:mi:ss:mmmAM(or PM)
110mm-dd-yy
111yy/mm/dd
112yymmdd
113 or 13dd mon yyyy hh:mi:ss:mmm(24h)
114hh:mi:ss:mmm(24h)
120 or 20yyyy-mm-dd hh:mi:ss(24h)
121 or 21yyyy-mm-dd hh:mi:ss.mmm(24h)
126yyyy-mm-ddThh:mi:ss.mmm(不含空格)
130dd mom yyyy hh:mi:ss:mmmAM(or PM)
131dd/mm/yy hh:mi:ss:mmmAM(or PM)
  1. MySQL
DATE_FORMAT(date, format)
# date: 一个合法的日期
# format: 规定日期/时间的输出格式
  • format 参数：
格式说明
%a简写的英文星期
%b简写的英文月份
%c数值表示的月份
%D表示带有英文后缀的月中的第几天
%d月中第几天（00~31）
%e月中第几天（0~31）
%f微秒数
%H小时数（00~23）
%h小时数（01~12）
%I小时数（01~12）
%i分钟数（00~59）
%j年中的第几天（001~366）
%k小时 (0-23)
%l小时 (1-12)
%M月名
%m月，数值(00-12)
%pAM 或 PM
%r时间，12-小时（hh:mm:ss AM 或 PM）
%S秒(00-59)
%s秒(00-59)
%T时间, 24-小时 (hh:mm:ss)
%U周 (00-53) 星期日是一周的第一天
%u周 (00-53) 星期一是一周的第一天
%W完整的英文日期
%w周的天 （0=星期日, 6=星期六）
%Y年，4 位
%y年，2 位
  1. Oracle
TO_CHAR(expression, format)
格式说明
YYYY年， 4位
YYY年，后三位
YY年，后两位
Y年，最后一位
YEAR英文表示的年份
MONTH文字表示的月份
MM数字表示的月份
DAY星期名
DDD年中第几天
DD月中第几天
D一周第几天 1~7，1为星期日
DY星期名缩写
WW年中第几周
W月中第几周
HH12 小时 的小时
HH2424 小时的小时
MI分钟数
SS秒数
AM|PM(A.M.|P.M.)上午或下午

2.13.2 数据表的数值类型转换–CAST()

CAST(expression AS data_type)
# expression: 任何有效的 SQL Sever 表达式
# data_type: 目标类型，必须是系统所提供的数据类型
# 将浮点型转换为整型时，小数部分会舍去而不会进行四舍五入
# 将字符串转换为 decimal 类型时，需要指定精度（总位数）和小数位数，此时小数部分为四舍五入

2.13.3 去掉空格–LTRIM()

LTRIM(character_expression)

2.14 模糊查询–LIKE

  1. 通配符及其含义
通配符说明
%由0个或多个字符组成的任意字符串
_任意单个字符
[]用于指定范围，和正则表达式中类似
[^]用于表示指定范围外的
  1. LIKE 语句格式
WHERE list LIKE '%mr%'
WHERE list LIKE 'mr%'
WHERE list LIKE '[mr]'
WHERE list LIKE '[^a-z]'
...
  1. 转义字符使用 ESCAPE 定义
WHERE list LIKE '%10#%' ESCAPE '#'
# '#' 被定义为转义字符，其后的 '%' 被解释为普通字符

2.15 行数据过滤

2.15.1 行查询

  1. 查询指定行：查询第 n 行，先将表中的前 n 行数据查询出来，再将表中的前 n-1 行数据查询出来，最后通过 NOT EXISTS 操作符将表中的第 n 行数据显示出来。(如下示例，其中用到了子查询)
SELECT list1, list2 FROM (SELECT TOP n * FROM table) aa
WHERE NOT EXISTS (SELECT * FROM (SELECT TOP n-1 * FROM table) bb
WHERE aa.list1=list2);
  1. 随机查询一行数据：
  • SQL Server：使用 NEWID() 函数返回一个唯一的标识符值（类似随机排序，然后选择第一个）
SELECT TOP 1 list
FROM table order by NEWID();
  • MySQL：使用 RAND() 函数
SELECT list
FROM table
ORDER BY RAND() LIMIT 1;
  • Oracle：
SELECT list FROM(
SELECT list FROM table ORDER BY DBMS_RANDOM.VALUE())
WHERE ROWNUM=1;
  1. 在结果集中添加行号：
SELECT (SELECT COUNT(num) FROM table A
WHERE A.num>=B.num) id, num, sn
FROM table B ORDER BY 1;
  1. 使用 ROW_NUMBER() OVER() 函数查询指定行：
  • 例子：SQL Server：
SELECT 编号, list2 FROM (
SELECT ROW_NUMBER() OVER(ORDER BY list2) 编号, list2
FROM table) a WHERE a.编号%2=1;
  • Oracle：取余函数使用 MOD()
  • MySQL 不支持 ROW_NUMBER() OVER() 函数
  • 使用 BETWEEN…AND… 可查询指定范围内的行

2.15.2 空值判断

  1. 查询是否为空值：
SELECT list1, list2
FROM table
WHERE list2 IS [NOT] NULL;
  1. 对空值进行处理：
  • SQL Server 中填充空值：
SELECT list1, list2, ISNULL(list3, 0) AS new_list3
FROM table;
  • Oracle 中填充空值：
SELECT list1, list2, NVL(list3, 0) AS new_list3
FROM table;
  • MySQL: ISNULL() 只返回 0 或 1
  • 将指定值转换为空值：NULLIF()
SELECT list1, list2, NULLIF(name, 'abc') AS name
FROM table;

2.16 数据排序

2.16.1 数值排序

  • ORDER BY 基本语法：
ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n]
# order_by_expression：指定要排序的列，列名或别名的表达式，可以指定多个排序列，第一个列为主排列，以此类推
# 默认为 ASC 升序
# ORDER BY 只能出现在 SELECT 语句的最后
# 一般情况下，排序列可以不出现在 SELECT 选择列表中，但当 SELECT 语句出现了 DISTINCT 或 UNIQUE 关键字，则 ORDER BY 只能指定前面选择列表中的项目

# 例子：
SELECT list1, list2
FROM table
ORDER BY list2 DESC;
  • SQL Server 中，ORDER BY 优先级高于 TOP, Oracle 中，ORDER BY 优先级低于 ROWNUM()

2.16.2 汉字排序–COLLATE Chinese_PRC_

  1. 排序规则：Chinese_PRC_[] 是指针对简体字 UNICODE 字符集的排序规则
  2. 其中后半部分有：
  • _BIN: 表示二进制排序
  • _CI(CS): 是否区分大小写，前者为不区分
  • _AI(AS): 是否区分重音，前者为不区分
  • _KI(KS): 是否区分假名类型，前者为不区分
  • _WI(WS): 是否区分宽度，前者为不区分
  1. 例子：按姓氏笔画排序：Chinese_PRC_Stroke_
SELECT * FROM table;
SELECT * FROM table
ORDER BY LEFT(name, 1) COLLATE Chinese_PRC_Stroke_CS_AS_KS_WS；
  1. 例子：按拼音排序：Chinese_PRC_
SELECT * FROM table;
SELECT * FROM table
ORDER BY LEFT(name, 1) COLLATE Chinese_PRC_CS_AS_KS_WS；

2.17 数据统计分析

SELECT SUM( [DISTINCT] expression) 
FROM table
[WHERE condition];    # 非空值求和

SELECT AVG( [DISTINCT] expression)    # expression 必须为数值型，且不允许为聚合函数和子查询
FROM table
[WHERE condition];    # 非空值 [非重复值] 求平均值

SELECT MIN( [DISTINCT] expression) 
FROM table
[WHERE condition];    # 最小值（数值、字符串、日期）

SELECT MAX( [DISTINCT] expression) 
FROM table
[WHERE condition];    # 最大值（数值、字符串、日期）

SELECT COUNT( [DISTINCT] * | expression)    # * 为所有行，不能与 DISTINCT 一起使用
FROM table
[WHERE condition];   # [非重复值] 计数
  • WHERE 语句中可以使用聚合函数
  • SQL Server 中聚合函数不能嵌套，在 Oracle 中可以，但必须设置 GROUP BY 子句
  • 子查询不能作为一个聚合函数的表达式

2.18 分组统计

2.18.1 创建分组

SELECT list, COUNT(*) AS 数量
FROM table
[WHERE condition]
GROUP BY list1 [, list2]
[ORDER BY item];
  • 如果使用了 GROUP BY 子句进行分组查询，则 SELECT 查询的列必须包含在 GROUP BY 子句中或者包含在聚合函数中（否则分组拿来干嘛用的）
  • 上述语句执行顺序为：先执行 FROM，如果存在 WHERE，则进行筛选，根据 GROUP BY 进行分组，最后根据 SELECT 子句生成结果（如果有 ORDER BY 子句的话）
  • 对于 NULL 值，尽管 SQL 中规定 NULL<>NULL ，但在分组时会将 NULL 值都分为一组

2.18.2 使用 ROLLUP 关键字

GROUP BY ROLLUP(A, B, C)    # Oracle, SQL Server
GROUP BY A, B, C WITH ROLLUP    # SQL Server
  • 系统先对 A, B, C 进行分组操作，然后对 A, B 进行分组操作， 然后对 A 进行分组操作，最后对全表分组（即全表汇总）
  • 相当于生成了各种分类汇总

2.18.3 使用 CUBE 关键字

GROUP BY CUBE(A, B)    # Oracle, SQL Server
GROUP BY A, B, C WITH CUBE    # SQL Server
  • 系统会对 A, B 分组，对 A 分组， 对 B 分组，最后对全表分组（即全表汇总）

2.18.4 使用 HAVING 子句进行过滤分组

  1. HAVING 子句和 WHERE 子句用法相似，两者区别如下：
  • WHERE 不能放在 GROUP BY 后面，HAVING 可以
  • HAVING 是和 GROUP BY 一起用的，放在其后面，作用和 WHERE 一样
  • WHERE 不能用于聚合函数，HAVING可以
  • WHERE 用于分组前过滤，HAVING 用于分组后过滤
  1. 示例：统计 list3 小于 1000 并且 list2 大于 list2 的平均值的，根据 list1 分组的条目数量
SELECT list 1, list2, COUNT(list1) 数量
FROM table
WHERE (list3 < 1000)
GROUP BY list1, list2
HAVING (list2 > 
	(SELECT AVG(list2)
	FROM table))
ORDER BY list2 DESC;

2.18.5 SELECT 子句的顺序

SELECT → \to → FROM → \to → WHERE → \to → GROUP BY → \to → HAVING → \to → ORDER BY

2.19 子查询

2.19.1 简单子查询

  1. 子查询的语法：
(SELECT [ALL | DISTINCT] <select item list>
FROM <table list>
[WHERE <search condition>]
[GROUP BY <group item list>
[HAVING <group by search condition>]])
  • 子查询的 SELECT 查询必须使用圆括号
  • 不能包括 COMPUTE 或 FOR BROWSE 子句
  • 如果同时指定 TOP 子句，则可能只包括 ORDER BY 子句
  • 子查询最多可以嵌套 32 层
  • 任何可以使用表达式的地方都可以使用子查询，只要返回的是单个值
  • 如果某个表只出现在子查询中，那么该表中的列就无法包含在输出中
  • 子查询中可以使用聚合函数
  1. 常用语法格式：
WHERE expression [NOT] IN (子查询)
WHERE expression 比较运算符 [ ANY | ALL ] (子查询)
WHERE [NOT] EXISTS (子查询)

2.19.2 多行子查询

  1. IN, NOT IN 进行多行子查询
  • IN 实现交集运算：获取 table1.list1 既在 table1 中，又在 table2 中的 table1 中的数据，并按 table1.list2 排序
SELECT *
FROM table1
WHERE list1 IN (
	SELECT list1
	FROM table2
	WHERE table1.list1=table2.list1)
ORDER BY table1.list2;
  • NOT IN 实现差集运算：
SELECT *
FROM table1
WHERE list1 NOT IN (
	SELECT list2
	FROM table2);
  1. EXISTS, NOT EXISTS
  • EXISTS 实现交集：
SELECT list1, list2
FROM table1
WHERE EXISTS(
	SELECT list2
	FROM table2
	WHERE table1.list2=table2.list2);
  • NOT EXISTS 实现差集：
SELECT * 
FROM table1
WHERE NOT EXISTS(
	SELECT list2
	FROM table2
	WHERE table1.list2=table2.list2);
  1. 使用量词实现多行子查询
  • 格式：
SELECT list1, list2, list3
FROM table
WHERE list3 < SOME(
	SELECT AVG(list3)
	FROM table
	GROUP BY list1);
  • ALL 要求 list3 的值小于子查询中的每一个值（全部满足比较运算）
  • ANY/SOME 要求 list3 的值小于子查询中的最大值（满足一个即可，如果是大于则大于最小值即可）

2.20 多表连接

2.20.1 内连接

  1. 等值连接
SELECT t1.l1, t1.l2, t2.l1
FROM t1, t2
WHERE t1.l3=t2.l2;

SELECT fieldlist
FROM table1 [INNER] JOIN table2
ON table1.column=table2.column
  1. 不等值连接：使用非 = 即可，如 >, < …
  2. 可以连接多个表

2.20.2 外连接

  1. 左外连接：左表全部包含，右表满足条件，右表不满足条件的行用 NULL 填充
SELECT fieldlist
FROM table1 LEFT JOIN table2
ON table1.column=table2.column
  1. 右外连接同理
  2. 全外连接：左右都包含，不符合条件的用 NULL 填充（MySQL, Access 和 SQLite 不支持全外连接）

2.20.3 其他连接

  1. 自连接：自身连接，相当于复制一个，成为两个表来进行连接
  2. 交叉连接：即两个表的笛卡尔积，列数相加，行数相乘，table1 的每一行都会和 table2 的每一行进行匹配
SELECT fieldlist
FROM table1
CROSS JOIN table2

2.20.4 组合查询–UNION

  • 使用 UNION 组合多条查询结果，必须由两个或两个以上的 SELECT 语句组成，语句之间用 UNION 关键字分隔
  • 每个 SELECT 语句中列的数目必须相同，而且对应位置上的列的数据类型必须相同或者兼容
  • 最后的结果集中的列名是由第一个 SELECT 语句中的列名决定的
  • 默认结果滤除了重复的行，使用 ALL 关键字返回重复的行
SELECT <lists>
FROM <tables>
WHERE <conditon>
UNION [ALL]
SELECT <lists>
FROM <tables>
WHERE <conditon>
...
ORDER BY ...

3. 插入数据–INSERT

3.1 插入单行数据

  1. 基本语法：
INSERT INTO table_or_view [(column_list)] VALUES (value_list)
# table_or_view: 表名或视图的名称
# column_list: 由逗号分隔的列名列表，插入整行时可以省略
# value_list: 作为一行或者多行插入一命名的表或视图中，若某一列没有值且该列允许为 NULL 值，可以使用 NULL 填充
  1. 创建表的时候允许通过 DEFAULT 关键字为列定义默认值，当插入数据时没有指定该列值，则用默认值填充

3.2 插入多行数据

  1. 使用 VALUES 关键字
INSERT INTO table_name
[(column_list)]
VALUES
(data11, data12,...),(data21, data22,...),...;
  1. 通过 SELECT 语句插入多行数据
INSERT INTO table_name
SELECT {* | fieldname1[, fieldname2...]}
FROM table_source
[WHERE search_condition];

3.3 表中数据的复制

  1. SQL Server
SELECT [select_list]
INTO new_table
FROM table_name
WHERE search_condition;
  1. MySQL and Oracle
CREATE TABLE new_table AS
SELECT [select_list]
FROM table_name
WHERE search_condition;

4. 更新删除数据

4.1 更新修改–UPDATE

  1. 基本语法：
UPDATE table_name
SET column1=value1, column2=value2,...
[WHERE search_condition];
  1. 若没有 WHERE 语句，则为修改全部数据

4.2 删除数据-DELETE

  1. 基本语法：
DELETE FROM
{table_name | view_name}
[WHERE search_conditions];
  1. 若没有 WHERE 语句，则为删除全部数据，此时建议使用 TRUNCATE TABLE，效率更高
TRUNCATE TABLE table_name

5. 使用视图

5.1 视图的优点

  1. 简化操作：尤其是简化复杂的多表关联查询。
  2. 建立前台和后台的缓冲
  3. 合并分割数据
  4. 提高安全性

5.2 创建视图

CREATE VIEW view_name [(column_name)]
AS select_statement
[WITH CHECK OPTION]；

# view_name：视图的名称
# column_name：视图中的字段名，若不指定则使用 SELECT 语句中的字段名称。
# 如下情况必须指定：
# 1. 视图是从多个表中产生的，对于表中由数据列重名时
# 2. 列是从算术表达式、函数或常量派生得到的
# 3. 视图中的某列不同于源表中列的名称时
# select_statement：定义视图的 SELECT 语句，可以引用多个表或其他视图
# CHECK OPTION：规定在视图上执行的所有数据修改语句都必须符合由 select_statement 设置的准则

5.3 删除视图

DROP VIEW view_name；

5.4 视图的应用

  1. 通过视图简化复杂查询：通过创建视图，实现多表之间的复杂连接。利用视图可以编写一个复杂的查询语句，然后根据需要进行多次使用。
  2. 使用视图过滤不想要的数据；
  3. 通过视图显示函数的结果，此时必须提供列名
  4. 通过视图添加数据：首先，必须有权限进行插入，其次，视图未引用的字段必须在没有给定取值的情况下自动填充（比如允许空值、设有默认值等），然后，视图不能包含多个字段值的组合，或者包含使用统计函数的结果，最后，视图中不能包含 DISTINCT 或 GROUP BY 子句。
INSERT INTO 
view_name [(view_column_name...)]
VALUES (view_column_value...);
  1. 通过视图更新数据：
UPDATE view_name
SET column1=expression1...
[WHERE condition];
  1. 通过视图删除数据：视图只能来自一个数据表，且不是自连接
DELETE view_name WHERE search_condition;
  1. 使用 WITH CHECK OPTION 子句：如果设置了该子句，则在视图上的 INSERT 或 UPDATE 操作都必须符合定义视图时设置的查询条件。

6. 使用存储过程

6.1 存储过程概述

  1. 概念：存储过程（Stored Procedure）是一组预先编译好的 Transact-SQL 语句。将其放在服务器上，由用户通过指定存储过程的名字来执行它。存储过程可以作为一个独立的数据库对象，也可以作为一个单元被用户的应用程序调用。存储过程可以接收和输出参数，返回执行存储过程的状态值，还可以嵌套调用。（Access 和 SQLite 不支持存储过程）
  2. 功能：条件执行、循环控制结构、命名变量、命名过程、语句块

6.2 SQL Server 存储过程：

  1. 创建：
CREATE PROC[EDURE] procedure_name
	[{ @parameter data_type}
		[ = default] [OUTPUT]]
AS sql_statement [...n];

# procedure_name: 存储过程名称
# @parameter：过程中的参数
# data_type: 参数的数据类型
# default: 参数的默认值
# OUTPUT：表明参数是返回参数
# sql_statement：过程中要包含的任意数目和类型的 Transact-SQL 语句
  1. 执行：
[ EXEC[UTE] procedure_name];
  1. 查看：
sp_helptext [ @objname = ] 'name';     # 查看文本信息
sp_depends [ @objname = ] 'object';    # 查看相关性信息
sp_help [ [ @objname = ] name ];    # 查看一般信息
  1. 修改：
ALTER PROC[EDURE] procedure_name
	[ { @parameter data_type }
		[ = default ] [ OUTPUT ]]
AS sql_statement [ ...n ];
  1. 删除：
DROP PROCEDURE procedure_name;

6.3 Oracle 创建存储过程

CREATE [OR REPLACE] PROCEDURE procedure_name
	(参数1 [方式1] 数据类型1, ...)
IS|AS
BEGIN
...
END;

# 方式有3种：IN 为输入参数，OUT 为输出参数，IN OUT 为输入输出参数

6.4 MySQL 创建存储过程

CREATE PROCEDURE procedure_name (方式 参数 类型)
BEGIN
...
END;

7. 使用游标

  • 声明游标
  • 打开游标
  • 读取数据
  • 关闭游标
  • 释放游标

7.1 声明游标

  1. SQL Server, MySQL：
DECLARE cursor_name CURSOR
	FOR select_statement
# select_statement 不允许使用 COMPUTE,COMPUTE BY, FOR BROWSE 和 INTO 关键字
  1. Oracle：
DECLARE
	CURSOR cursor_name ([参数列表])
	IS select_statement

7.2 打开游标

OPEN cursor_name

7.3 读取游标中的数据

  1. SQL Server：
FETCH
	[[NEXT | PRIOR | FIRST | LAST]
		FROM
	]
cursor_name
[ INTO @variable_name [, ...n]]

# NEXT：下一行，为默认值
# PRIOR：前一行
# FIRST：第一行
# LAST：最后一行
  1. Oracle：
FETCH 游标名称 INTO {RECORD 类型变量}

7.4 关闭并释放游标

CLOSE cursor_name;
DEALLOCATE { { [GLOBAL]  cursor_name } | @cursor_variable_name }

8. 事务处理

8.1 事务概述

  • 原子性：事务是一个整体的工作单元，要么全部执行，要么全部取消
  • 一致性：事务在完成时，必须使所有的数据都保持一致状态
  • 隔离性：事务所做的修改必须与其他事务所做的修改隔离
  • 持久性：事务提交后，对数据库所做的修改就会永久保存下来

8.2 显式事务

  • 用户自定义或用户指定的事务
  1. SQL Server：
BEGIN TRANSACTION name
...
COMMIT TRANSACTION
  1. MySQL：
START TRANSACTION name
...
  1. Oracle：
SET TRANSACTION name
...

8.3 隐式事务

  • 打开隐式事务模式后，自动启动一个新事物，然后关闭一个事务时，执行下一条语句又会启动一个新事物，直到关闭隐式事务模式
  • 使用 T-SQL 控制隐式事务
SET IMPLICIT_TRANSACTIONS ON    # 开启隐式事务模式
...
COMMIT TRANSACTION    # 提交事务
...
COMMIT TRANSACTION    #提交事务
...
SET IMPLICIT_TRANSACTIONS OFF    # 关闭隐式事务模式

8.4 事务操作

  1. 提交事务：
COMMIT [TRAN[SACTION]] [transaction_name | @tran_name_variable]
  1. 设置事务回退点：
SAVE TRANSACTION [savepoint_name | @savepoint_name]
  1. 回滚事务：
ROLLBACK [TRAN[SACTION]] [savepoint_name | transaction_name]

9. 数据库管理

9.1 创建数据库

CREATE DATABASE database_name    # 指定数据库名称，不超过 123 个字符
[ON    # 指明数据库文件和文件组的明确定义
	[<filespec>[,...n]]    # 文件说明（说明文件用途等）
	[,<filegroup>[,...n]]
]
[LOG ON{<filespec>[,...n]}]    # 指明事务日志文件的明确定义，若不指定，则自动产生一个与数据库名称相同的文件名前缀且容量为所有数据库文件大小 1/4 的事务日志文件
[COLLATE collation_name]    # 指明数据库的校验方式
[FOR LOAD|FOR ATTACH]

<filespec>::=
[PRIMARY]    # 指定主数据库文件或主文件组，若没有则默认将第一个文件列为主文件
([NAME=logical_file_name,]    # 文件在 SQL Server 中的逻辑名称
	FILENAME='os_file_name'    # 文件在操作系统中存储的路径和文件名称
	[,SIZE=size]    # 数据库的初始容量
	[,MAXSIZE={max_size|UNLIMITED}]    # 文件的最大容量
	[,FILEGROWTH=growth_increment])[,...n]    # 文件每次增容时增加的容量大小
	
<filegroup>::=
FILEGROUP filegroup_name<filespec>[,...n]

9.2 修改数据库

  1. 修改数据库
ALTER DATABASE  name
{ADD FILE<filespec>[,...n][TO FILEGROUP filegroup_name]    # 增加数据库文件[至某个文件组]
|ADD LOG FILE<filespec>[,...n]    # 增加的事务日志文件
|REMOVE FILE logical_file_name    # 删除指定文件的定义，并删除其物理文件。文件只有为空时才能被删除
|ADD FILEGROUP filegroup_name    # 增加文件组
|REMOVE FILEGROUP filegroup_name    # 删除文件组的定义，并且删除其包含的所有数据库文件。文件组只有为空时才能被删除
|MODIFY FILE<filespec>    # 修改指定文件的属性，一次只能修改一个属性。必须使用 NAME 明确指定文件名称
|MODIFY NAME=new_dbname
|MODIFY FILEGROUP filegroup_name{filegroup_proprety|NAME=new_filegroup_name}    # 修改文件组属性（READONLY：只读；READWRITE：读写；DEFAULT：文件组为默认文件组）
|SET<optionspec>[,...n][WITH<termination>]    # 设置数据库属性
|COLLATE<collation_name>
}
  1. 缩小数据库
DBCC SHRINKDATABASE
( database_name [, target_percent] [, {NOTRUNCATE | TRUNCATEONLY}] )

# target_percent 为缩小后的空间大小
# NOTRUNCATE：被释放的文件空间依然保持在数据库文件的范围内，若不设置该选项，则释放的空间将被操作系统回收利用
# TRUNCATEONLY: 将所有未使用的数据空间释放并让操作系统回收利用，此时将忽略 target_percent
  1. 缩小数据文件或日志文件
DBCC SHRINKFILE
(file_name { [, target_size] | [, EMPTYFILE | NOTRUNCATE | TRUNCATEONLY] })

# EMPTYFILE：将指定文件上的数据全部转移到文件组内其他文件上
  1. 数据库更名
EXEC sp_renamedb 'old_name', 'new_name';

9.3 删除数据库

DROP DATABASE database_name [,...n];
  • 不能删除正在使用的数据库

10. 数据表管理

10.1 创建数据表

CREATE TABLE table_name (
字段名称 字段类型 [DEFAULT 默认值] [PRIMARY KEY]    # 设置为主键，列值必须为唯一的，且不包含 NULL
字段名称 字段类型 [DEFAULT 默认值] [UNIQUE]    # 约束为唯一值，允许 NULL
字段名称 字段类型 [DEFAULT 默认值] [NOT NULL]    # 约束不允许 NULL
字段名称 字段类型 [DEFAULT 默认值] [CHECK(condition)]    # 约束满足 condition
...
);

10.2 查看数据表

  1. 查看数据表信息：
EXEC sp_help table_name;
  1. 查看数据表的行数和存储空间
EXEC sp_spaceused table_name;

10.3 修改数据表

  1. 基本操作
ALTER TABLE name
{ADD 字段名称 字段类型...     # 添加列，只允许添加可包含 NULL 值的列
|ALTER COLUMN 字段名称 字段类型...    # 修改列的数据类型和约束条件
|ADD PRIMARY KEY(字段名称)    # 添加主键，要求原来表中没有主键（主键只能有一个）
|DROP COLUMN 字段名称    # 删除字段
}
  1. 删除表中约束
# 查找约束名称
SELECT * FROM sysobjects
WHERE parent_obj IN(
SELECT id FROM sysobjects
WHERE name='table_name');
# 删除约束
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
  1. 数据表重命名
EXCU sp_rename 'old_name', 'new_name';

10.4 删除数据表

DROP TABLE table_name[,...n];
Raymone_
关注
专栏目录
SQL快速入门（基本用法）
Strengthen123的博客
07-29 1万+
基本语句用法
史上最全SQL学习指南（教程+实例+练习题）
九章算法的博客
04-27 1万+
报告称，未来10年数据细分岗位将扩张5倍，大数据19w的人才缺口将继续增加。国内外一线互联网公司纷纷开设了数据分析岗位。 数据分析岗位中用的最频繁的工具就是SQL了。不论是满足业务日常取数需求，还是自己做分析，都离不开SQL。 最近，有不少想接触数据分析的同学，都在咨询系统学习SQL。如何学、怎么学、在哪学成了大家普遍的问题。 我们在领扣上新的SQL教程刚好能帮你解决这些问题！教程+实例+练习题，学过的人都反馈极高，最最重要是免费的！而且相信我，任何一个会基本数学运算的同学，从零开始搞定SQL只需五天。
SQL DATEDIFF函数
寒冰屋的专栏
06-29 3万+
SQL DATEDIF函数计算并返回两个日期值之间的差值。返回的值是一个整数。您可以通过DATEDIFF改变datepart参数来计算各种日历计算。
SQL Server各种日期计算方法
卢弋 的个人技术专栏 -- 总结使人进步
09-13 1814
通常，你需要获得当前日期和计算一些其他的日期，例如，你的程序可能需要判断一个月的第一天或者最后一天。你们大部分人大概都知道怎样把日期进行分割（年、月、日等），然后仅仅用分割出来的年、月、日等放在几个函数中计算出自己所需要的日期！在这篇文章里，我将告诉你如何使用DATEADD和DATEDIFF函数来计算出在你的程序中可能你要用到的一些不同日期。 　　在使用本文中的例子之前，你必须注意以下的问题。大部
SQL入门教程
最新发布
qq_44624290的博客
06-04 1084
你是一个普普通通的人力，每天面对的就是一张张的员工信息表做一些简单的统计工作，大部分的工作只是简单的表格处理，甚至连宏都不需要，虽然有时候需要用一些笨方法，但是工作还是可以完成的。但是突然有一天，你的老板突发奇想，觉得员工入职的都是好日子，打算只要员工入职了就去买股票去，然后扔给了你了一个表格，里面有着20年的股票数据，让你去算一下只要员工入职了就去买股票，当天是涨的多还是跌的多你要怎么办，你只能无奈的去翻找每个员工入职的日子，然后再去看看当天的股票是不是开盘，然后再算出来是涨还是跌。
SQL入门教程（非常详细）从零基础入门到精通，看完这一篇就够了
Python84310366的博客
07-05 6万+
SQL 是用于访问和处理数据库的标准的计算机语言。SQL 指结构化查询语言SQL 使我们有能力访问数据库SQL 是一种 ANSI 的标准计算机语言SQL 可与数据库程序协同工作，比如 MS Access、DB2、Informix、MS SQL Server、Oracle、Sybase 以及其他数据库系统。但是由于各种各样的数据库出现，导致很多不同版本的 SQL 语言。
SQL学习
leizi1995的博客
10-09 505
这里写自定义目录标题SQL语言学习新的改变功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入 SQL语言学习 你好！ 这是...
SQL学习资料
03-28
SQL学习资料，用于常用的SQL复习以及进阶复杂SQL的学习
SQL学习指南
06-05
资料里面有pdf版的电子书，是本学习SQL很好的书，尤其对于初学者或者学习不久的同学，我大二学了一学期的数据库课程，并不是很懂数据库到底怎么写，到考试的时候也是飘过。 后面做项目用到数据库，我通过这里面的...
mybatis sql学习
02-17
MyBatis学习总结(四)——解决字段名与实体类属性名不相同的冲突 　在平时的开发中，我们表中的字段名和表对应实体类的属性名称不一定都是完全相同的，下面来演示一下这种情况下的如何解决字段名与实体类属性名不...
SQL 教程-入门基础篇
Java智慧阁
05-16 3229
SQL（Structured Query Language）是一种用于管理和操作关系型数据库的标准化语言。它允许用户通过简单的语句来定义、操作和查询数据库中的数据。
【SQL入门】最全SQL基础知识总结（理论+举例+建议）
qq_39172059的博客
03-06 6767
数字型（int，tinyint，decimal，float等）、日期和时间类型（Date，TIME等）、字符串类型（char，varchar等）。：于1992年发布，这个版本通过引入新的特性进一步扩展了SQL语言的能力，包括多表连接、子查询、视图和存储过程等，是目前获得最广泛支持的版本。：为表设置适当的表别名，不仅可以减少SQL语句的复杂度，也能尽量避免出错，让SQL语句更加的直观、易理解。：在1989年发布，这个版本引入了更多的特性，包括排序、分组和连接操作的支持。
史上最全SQL基础知识总结(理论+举例)
热门推荐
PILIpilipala的博客
02-13 18万+
SQL基础知识一、SQL 概述1、什么是 SQL2、语法要求二、SQL 分类三、DDL（Data Definition Language）：数据定义语言基本操作1.操作数据库2.数据类型3.操作表四、DML（Data Manipulation Language）：数据操作语言1.插入数据2.修改数据3.删除数据五、DCL（Data Control Language）：数据控制语言1.创建用户2.给用户授权3.撤销授权4.查看用户权限5.删除用户6.修改用户密码（以root身份）六、DQL（Data Quer
【数据库必备知识】零基础带你快速上手SQL语言
一些普普通通的学习笔记
11-11 9883
只需要学习一篇博文的时间, 带你入门SQL语言, 熟悉SQL增删改查等操作, 赶快收藏吧!
SQL零基础入门必知必会！
华章IT官方博客
10-07 1万+
???? 前言SQL语言有40多年的历史，从它被应用至今几乎无处不在。我们消费的每一笔支付记录，收集的每一条用户信息，发出去的每一条消息，都会使用数据库或与其相关的产品来存储，而操纵数据库的语言...
SQL语句快速学习
zx_glave的博客
07-10 2177
​本文来自本·福达的《SQL必知必会（第5版）》的学习总结。如果之前学习过SQL相关知识，这篇文档将很有用。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值