本文详细介绍了数据库管理系统的各种操作，包括模式、表、视图和索引的创建、删除与修改。深入探讨了SQL语言，包括数据查询、数据更新、视图的创建与管理。内容涵盖基本表的定义、外连接、嵌套查询、集合查询及复杂条件的使用，展示了如何利用SQL进行高效的数据操作和分析。
数据定义
| 操作对象 | 操作方式 | ||
|---|---|---|---|
| 创建 | 删除 | 修改 | |
| 模式 | CREATE SCHEMA | DROP SCHEMA | |
| 表 | CREATE TABLE | DROP TABLE | ALTER TABLE |
| 视图 | CREATE VIEW | DROP VIEW | |
| 索引 | CREATE INDEX | DROP INDEX | ALTER INDEX |
一个关系数据库管理系统的实例中可以建立多个数据库，一个数据库中，可以建立多个模式，一个模式下通常包括多个表、视图和索引等数据库库对象
模式的定义与删除
定义模式
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>
用户可以在创建模式的同时在这个模式定义中进一步创建基本表、视图，定义授权。即：
CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>]
删除模式
-- CASCADE和RESTRICT必选其一，CASCADE表示级联(一删全删)，RESTRICT表示限制，模式下有数据则删除不成功
DROP SCHEMA <模式名> <CASCADE|RESTRICT>;
基本表的定义、删除与修改
定义基本表
create table <表名> (
<列名><数据类型>[列级完整性约束条件],
<列名><数据类型>[列级完整性约束条件],
...
[,<表级完整性约束条件>]
);
参照表和被参照表可以是同一个表
模式与表
- 在表名中明确指定模式名
CREATE TABLE "S-T".Student(...); /*Student所属的模式是S-T*/
-
在创建模式语句中创建表
-
设置所属模式，这样在创建的表时表名中就不必给出模式名
SHOW search_path;
SET search_path TO "S-T",PUBLIC;
CREATE TABLE Student(...);
修改基本表
ALTER TABLE <表名> [ADD [COLUMN] <新列名> <数据类型> [完整性约束] ]
[ADD <表级完整性约束>]
[DROP [COLUMN] <列名> [CASCADE|RESTRICT] ]
[DROP CONSTRAINT <完整性约束> [CASCADE|RESTRICT] ]
[ALTER COLUMN <列名> <数据类型>];
删除基本表
DROP TABLE <表名> [RESTRICT|CASCADE];
索引的建立和删除
建立索引是加快查询速度的有效手段。用户不必也不能显式地选择索引
-
顺序文件上的索引：顺序文件上的索引是针对按指定属性值升序或降序储存的关系，在该属性上建立一个顺序索引文件，索引文件由属性值和相应的元组指针组成。
-
B+树索引：是将索引属性组织成B+树形式
-
散列索引：是建立若干个桶，将索引属性按照其散列函数值映射到相应桶中，桶中存放索引属性值和相应的元组指针
-
位图索引：是用位向量记录索引属性中可能出现的值，每个位向量对应一个可能值
建立索引
CREATE [UNIQUE] [COLUMN] INDEX <索引名>
ON <表名>(<列名>[次序][,<列名>[次序]]...);
修改索引
ALTER INDEX <旧索引名> RENAME TO <新索引名>;
删除索引
DROP INDEX <索引名>;
数据字典
数据字典是关系数据库管理系统内部的一组系统表，他记录了数据库中所有的定义信息，包括关系模型定义、试图定义、索引定义、完整性约束定义、各类用户对数据库的操纵权限、统计信息等。
数据查询
SELECT [ALL|DISTINCT] <目标列表达式> [,<目标表达式>]...
FROM <表名或视图名> [,<表名或视图名>...]|(SELECT语句) [AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING<条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];
消除取值重复的行
DISTINCT关键字(默认是All即不作处理)
SELECT [ALL|DISTINCT] <目标列表达式> [,<目标表达式>]...
FROM <表名或视图名> [,<表名或视图名>...]|(SELECT语句) [AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING<条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];
Where常用的查询条件
| 查询条件 | 谓词 | ||
|---|---|---|---|
| 比较 | =,>,<,>=,<=,!=,<>,!>,!<;NOT +上述比较运算符 | ||
| 确定范围 | BETWEEN AND ,NOT BETWEEN AND | ||
| 确定集合 | IN,NOT IN | ||
| 字符匹配 | LIKE,NOT LIKE | ||
| 空值 | IS NULL,IS NOT NULL | ||
| 多重条件(逻辑运算) | AND,OR,NOT |
聚焦函数
| COUNT(*) | 统计元组的个数 | ||
|---|---|---|---|
| COUNT ([DISTINCT | ALL] <列名>) | 统计一列中值的个数 | |
| SUM ([DISTINCT | ALL] <列名>) | 计算一列值的总合(此列必须是数值型) | |
| AVG ([DISTINCT | ALL] <列名>) | 计算一列值的平均值(此列必须是数值型) | |
| MAX ([DISTINCT | ALL] <列名>) | 求一列值中的最大值 | |
| MIN ([DISTINCT | ALL] <列名>) | 求一列值中的最小值 |
连接查询
等值连接
-- 比较运算符有:=,>,<,>=,<=,!=,<>,!>,!<等
[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>
-- 此外连接谓词还可以使用以下形式
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
-- 当连接运算符为=时,成为等值连接。使用其他运算符称为非等值连接
-- 连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但名字不必相同
自身连接
一个表与其自己进行连接，称为表的自身连接(语法如等值连接一致)
外连接
-
左外连接：
LEFT OUTER JOIN -
右外连接：
RIGHT OUTER JOIN
多表连接
-- 以下是示例
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;
嵌套查询
用户可以使用多个简单查询构成复杂的查询，从而增强SQl的查询能力。以及层层嵌套的方式来构造程序正式SQL中"结构化"的含义所在。
select
(decode(XBM_D_,'0','男','女'))XBM_,
(case when BLZT_D_ is null then '未办理' when BLZT_D_='0' then '撤销' when BLZT_D_='1' then '已办理' end )BLZT_M_
from T_XG_LXGL_PCSX_ZRR ZRR
LEFT JOIN T_XG_LXGL_LXPCSX LXPCSX on ZRR.XG_LXGL_LXPCSX_ID_ = LXPCSX.ID_
inner join T_XG_LXGL_XSLX XSLX on XSLX.XG_LXGL_LXPC_ID_ = LXPCSX.XG_LXGL_LXPC_ID_
LEFT JOIN T_XG_LXGL_LXPC LXPC on LXPC.ID_ = LXPCSX.XG_LXGL_LXPC_ID_
left join T_XG_LXGL_LXSX LXSX on LXSX.ID_ = LXPCSX.XG_LXGL_LXSX_ID_
LEFT JOIN T_JW_XS0101 XS0101 on XS0101.ID_ = XSLX.JW_XS0101_ID_
LEFT JOIN T_JW_XS0701 XS0701 on XS0701.JW_XS0101_ID_ = XS0101.ID_
LEFT JOIN T_XG_LXGL_SXBL SXBL on SXBL.XG_LXGL_LXSX_ID_ = LXSX.ID_ and SXBL.XG_LXGL_XSLX_ID_ = XSLX.ID_
Left Join T_JW_JX01ND JX01ND on JX01ND.ID_=JW_JX01ND_ID_
Left Join T_JW_XX04 XX04 on XX04.ID_=JW_XX04_ID_
Left Join T_JW_XX0301 XX0301 on XS0701.JW_XX0301_ID_=XX0301.ID_
Left Join (select SSL.ID_,XSRZ.T_JW_XS0101_ID_
from T_XG_SSGL_SSL SSL
left join T_XG_SSGL_SSFJ SSFJ on SSFJ.T_XG_SSGL_SSL_ID_=SSL.ID_
left join T_XG_SSGL_XSRZ XSRZ on XSRZ.T_XG_SSGL_SSFJ_ID_=SSFJ.ID_ where ISDEL_='0') TSSL on TSSL.T_JW_XS0101_ID_=XS0101.ID_--宿舍楼关联表
Left Join (select * from IBPS_CAT_DIC where TYPE_ID_ = '723218685921067008') PYCCT on PYCCT.KEY_=PYCCM_D_
where ZRR.JW_JG0101_ID_='1253017' and
(case when LXPCSX.LX_D_='yx' then XS0701.JW_XX0301_ID_ when LXPCSX.LX_D_='ssl' then TSSL.ID_ when LXPCSX.LX_D_='xx' then '1' end) = (decode(ZRR.ZR_ID_,'','1',ZRR.ZR_ID_));
带有ANY(SOME)或ALL谓词的子查询
-
ANY：条件并不强烈，满足条件里某个值就行
-
ALL：必须满足条件所有值，条件较强烈
| = | <>或!= | < | <= | > | >= | |
|---|---|---|---|---|---|---|
| ANY | IN | – | <MAX | <=MAX | >MIN | >=MIN |
| ALL | – | NOT IN | <MIN | <=MIN | >MAX | >=MAX |
带有EXISTS谓词的子查询
EXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值”false”。
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno =Student.Sno AND Cno='1');
-- 使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。
-- 由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。
集合查询
集合查询主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT
SQL UNION 语法
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SQL UNION ALL 语法
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
基于派生表的查询
select
(decode(XBM_D_,'0','男','女'))XBM_,
(case when BLZT_D_ is null then '未办理' when BLZT_D_='0' then '撤销' when BLZT_D_='1' then '已办理' end )BLZT_M_
from T_XG_LXGL_PCSX_ZRR ZRR
LEFT JOIN T_XG_LXGL_LXPCSX LXPCSX on ZRR.XG_LXGL_LXPCSX_ID_ = LXPCSX.ID_
inner join T_XG_LXGL_XSLX XSLX on XSLX.XG_LXGL_LXPC_ID_ = LXPCSX.XG_LXGL_LXPC_ID_
LEFT JOIN T_XG_LXGL_LXPC LXPC on LXPC.ID_ = LXPCSX.XG_LXGL_LXPC_ID_
left join T_XG_LXGL_LXSX LXSX on LXSX.ID_ = LXPCSX.XG_LXGL_LXSX_ID_
LEFT JOIN T_JW_XS0101 XS0101 on XS0101.ID_ = XSLX.JW_XS0101_ID_
LEFT JOIN T_JW_XS0701 XS0701 on XS0701.JW_XS0101_ID_ = XS0101.ID_
LEFT JOIN T_XG_LXGL_SXBL SXBL on SXBL.XG_LXGL_LXSX_ID_ = LXSX.ID_ and SXBL.XG_LXGL_XSLX_ID_ = XSLX.ID_
Left Join T_JW_JX01ND JX01ND on JX01ND.ID_=JW_JX01ND_ID_
Left Join T_JW_XX04 XX04 on XX04.ID_=JW_XX04_ID_
Left Join T_JW_XX0301 XX0301 on XS0701.JW_XX0301_ID_=XX0301.ID_
Left Join (select SSL.ID_,XSRZ.T_JW_XS0101_ID_
from T_XG_SSGL_SSL SSL
left join T_XG_SSGL_SSFJ SSFJ on SSFJ.T_XG_SSGL_SSL_ID_=SSL.ID_
left join T_XG_SSGL_XSRZ XSRZ on XSRZ.T_XG_SSGL_SSFJ_ID_=SSFJ.ID_ where ISDEL_='0') TSSL on TSSL.T_JW_XS0101_ID_=XS0101.ID_--宿舍楼关联表
Left Join (select * from IBPS_CAT_DIC where TYPE_ID_ = '723218685921067008') PYCCT on PYCCT.KEY_=PYCCM_D_
where ZRR.JW_JG0101_ID_='1253017' and
(case when LXPCSX.LX_D_='yx' then XS0701.JW_XX0301_ID_ when LXPCSX.LX_D_='ssl' then TSSL.ID_ when LXPCSX.LX_D_='xx' then '1' end) = (decode(ZRR.ZR_ID_,'','1',ZRR.ZR_ID_));
SELECT语句的一般格式
SELECT语句的一般格式
SELECT [ALL|DISTINCT] <目标列表达式> [,<目标表达式>]...
FROM <表名或视图名> [,<表名或视图名>...]|(SELECT语句) [AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING<条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];
目标列表达式的可选格式
-
* -
<表名>.* -
COUNT([DISTINCT|ALL] *) -
[<表名>.]<属性列名表达式> [,[<表名>.]<属性列名表达式>]...
聚集函数的一般格式
| COUNT | ([DISTINCT|ALL] <列名>) |
| ----- | --------------------- |
| SUM | ([DISTINCT|ALL] <列名>) |
| AVG | ([DISTINCT|ALL] <列名>) |
| MAX | ([DISTINCT|ALL] <列名>) |
| MIN | ([DISTINCT|ALL] <列名>) |
WHERE子句的条件表达式的可选格式
这个发挥自己想象。
select
LX_D_, XSLX.ID_ xslx_id_,ZR_ID_,LXNF_,LXPC.ID_,LXPC.PCMC_,LXSX.ID_ LXSXID_,LXPCSX.ID_ LXPCSXID_,LXSX.LXSXMC_,
XH_,XM_,XBM_D_,(decode(XBM_D_,'0','男','女'))XBM_,PYCCM_D_,PYCCT.NAME_ PYCC_M_,DQSZJ_,JX01ND.ID_ JX01ND_ID_,JX01ND.ZYMC_,
XS0701.JW_XX0301_ID_,XX0301.DWMC_,XX04.ID_,XX04.BJ_,SXBL.ID_ SXBLID_,BLZT_D_,TSSL.ID_ SSLID_,
(case when BLZT_D_ is null then '未办理' when BLZT_D_='0' then '撤销' when BLZT_D_='1' then '已办理' end )BLZT_M_
from T_XG_LXGL_PCSX_ZRR ZRR
LEFT JOIN T_XG_LXGL_LXPCSX LXPCSX on ZRR.XG_LXGL_LXPCSX_ID_ = LXPCSX.ID_
inner join T_XG_LXGL_XSLX XSLX on XSLX.XG_LXGL_LXPC_ID_ = LXPCSX.XG_LXGL_LXPC_ID_
LEFT JOIN T_XG_LXGL_LXPC LXPC on LXPC.ID_ = LXPCSX.XG_LXGL_LXPC_ID_
left join T_XG_LXGL_LXSX LXSX on LXSX.ID_ = LXPCSX.XG_LXGL_LXSX_ID_
LEFT JOIN T_JW_XS0101 XS0101 on XS0101.ID_ = XSLX.JW_XS0101_ID_
LEFT JOIN T_JW_XS0701 XS0701 on XS0701.JW_XS0101_ID_ = XS0101.ID_
LEFT JOIN T_XG_LXGL_SXBL SXBL on SXBL.XG_LXGL_LXSX_ID_ = LXSX.ID_ and SXBL.XG_LXGL_XSLX_ID_ = XSLX.ID_
Left Join T_JW_JX01ND JX01ND on JX01ND.ID_=JW_JX01ND_ID_
Left Join T_JW_XX04 XX04 on XX04.ID_=JW_XX04_ID_
Left Join T_JW_XX0301 XX0301 on XS0701.JW_XX0301_ID_=XX0301.ID_
Left Join (select SSL.ID_,XSRZ.T_JW_XS0101_ID_
from T_XG_SSGL_SSL SSL
left join T_XG_SSGL_SSFJ SSFJ on SSFJ.T_XG_SSGL_SSL_ID_=SSL.ID_
left join T_XG_SSGL_XSRZ XSRZ on XSRZ.T_XG_SSGL_SSFJ_ID_=SSFJ.ID_ where ISDEL_='0') TSSL on TSSL.T_JW_XS0101_ID_=XS0101.ID_--宿舍楼关联表
Left Join (select * from IBPS_CAT_DIC where TYPE_ID_ = '723218685921067008') PYCCT on PYCCT.KEY_=PYCCM_D_
where ZRR.JW_JG0101_ID_='1253017' and
(case when LXPCSX.LX_D_='yx' then XS0701.JW_XX0301_ID_ when LXPCSX.LX_D_='ssl' then TSSL.ID_ when LXPCSX.LX_D_='xx' then '1' end) = (decode(ZRR.ZR_ID_,'','1',ZRR.ZR_ID_));
数据更新
INSERT INTO 语句
INSERT INTO 语句用于向表格中插入新的行。
INSERT INTO 表名称 VALUES (值1, 值2,....)
我们也可以指定所要插入数据的列：
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
Update 语句
Update 语句用于修改表中的数据。
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
DELETE 语句
DELETE 语句用于删除表中的行。
DELETE FROM 表名称 WHERE 列名称 = 值
视图的操作
什么是视图？
在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。
视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。
注释：数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。
视图的作用
-
视图可以简化用户的操作
-
视图使用户以多种角度看待同一数据
-
视图对重构数据库提供了一定程度的逻辑独立性
-
视图可以对机密数据提供安全保护
-
适当使用视图可以更清晰地表达查询
CREATE VIEW 语法
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
注释：视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。
CREATE VIEW 实例
可以从某个查询内部、某个存储过程内部，或者从另一个视图内部来使用视图。通过向视图添加函数、join 等等，我们可以向用户精确地提交我们希望提交的数据。
样本数据库 Northwind 拥有一些被默认安装的视图。视图 “Current Product List” 会从 Products 表列出所有正在使用的产品。这个视图使用下列 SQL 创建：
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
我们可以查询上面这个视图：
SELECT * FROM [Current Product List]
Northwind 样本数据库的另一个视图会选取 Products 表中所有单位价格高于平均单位价格的产品：
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
我们可以像这样查询上面这个视图：
SELECT * FROM [Products Above Average Price]
另一个来自 Northwind 数据库的视图实例会计算在 1997 年每个种类的销售总数。请注意，这个视图会从另一个名为 “Product Sales for 1997” 的视图那里选取数据：
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName
我们可以像这样查询上面这个视图：
SELECT * FROM [Category Sales For 1997]
我们也可以向查询添加条件。现在，我们仅仅需要查看 “Beverages” 类的全部销量：
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'
SQL 更新视图
您可以使用下面的语法来更新视图：
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
现在，我们希望向 “Current Product List” 视图添加 “Category” 列。我们将通过下列 SQL 更新视图：
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No
删除视图
您可以通过 DROP VIEW 命令来删除视图。
DROP VIEW view_name
扫一扫
2379
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
