SQL 学习

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];

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值