【0430】SQL学习

最新推荐文章于 2024-07-22 18:42:29 发布

My_summers

最新推荐文章于 2024-07-22 18:42:29 发布

阅读量138

点赞数 1

分类专栏：数据库系统及安全文章标签：数据库

本文链接：https://blog.csdn.net/weixin_44164249/article/details/107087697

版权

数据库系统及安全专栏收录该内容

7 篇文章 1 订阅

订阅专栏

SQL

SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引（键），规定表之间的链接，以及施加表间的约束。

SQL 中最重要的 DDL 语句:

CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引

查询和更新指令构成了 SQL 的 DML 部分：

SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据

DML数据操作语言

可以把 SQL 分为两个部分：数据操作语言 (DML) 和数据定义语言 (DDL)

SQL是非过程化语言。保留字不能分行写。

SELECT语句的处理顺序：FROM，WHERE,GROUP BY, HAVING,SELECT ，ORDER BY

**SELECT部分：**DISTINCT 去掉重复的，SELECT的计算，别名

WHERE部分：比较/计算，AND，OR，NOT（NOT>AND>OR）,BETWEEN AND（包括端点）,NOT BETWEEN　AND,NOT IN,IN,LIKE(模式匹配:’%’–任意个字符,’_’----单个字符,),IS NULL,IS NOT NULL(不能用’ '代替)

**ORDER BY部分:**ASC是升序,DESC是降序,直接加在列名后面.默认是升序.还可以多列排序,直接用逗号连接.

聚集函数

AVG(xx)计算平均数,COUNT(xxx)计算数据个数,SUM返回数据总和,MIN,MAX.

聚集函数只能用于SELECT列表和HAVING子句中,如果SELECT列表包括聚集函数,却没有使用GROUP BY子句分组,那么SELECT列表的任何项都不能引用列,除了作为聚集函数的参数.

COUNT(DISTINCT date)去掉重复项

SELECT dept_name,AVG(salary) AS avg_salary

FROM instructor 

GROUP BY dept_name-----------计算系中老师的平均工资并显示

SELECT COUNT(DISTINC propertyNo) AS A

FROM nn

WHERE ...

GROUP BY xx----先分成一个组方便计算

先排序再选择

GROUP BY部分

SELECT子句中的所有列除非用在聚集函数中,否则必须在GROUP BY子句中出现.反之,GROUP BY子句中出现的项不一定出现在SELECT子句中.

HAVING 部分

HAVING子句使用的列名必须出现在GROUP BY子句列表中,或包括在聚集函数中.

任何使用HAVING子句的查询都可以用不带HAVING子句的语句重写.

子查询

子查询可以使用在WHERE,HAVING子句和INSERT\UPDATE\DELETE语句中,子查询三种类型:标量子查询(返回单个值),行子查询(返回多个列,但只有单个行),表子查询(返回多个行,每行有一个或多个列)

SELECT staffNo,position,salary-(SELECT AVG(salary) FROM Staff)AS salDiff
FROM staff
WHERE salary>
	(SELECT AVG(salary)
	FROM Staff)

子查询应遵循规则:

(1)ORDER BY子句不能用于子查询;

(2)子查询SELECT列表必须由单个列名或表达式组成,除非使用EXITS

(3)子查询必须出现在比较式的右边

ANY和ALL

ANY/SOME 只要有一个值满足条件就为真;ALL 所有值都满足条件时才为真.子查询为空时,ALL为真,ANY为假

NOT EXISTS/EXISTS

当且仅当子查询返回结果至少存在一行是EXISTS返回真,NOT EXISTS相反.

引出子查询的运算符:

(1)比较运算符

(2)带有in/not in

(3)some / any/all

分类方式(一):单值子查询/列表子查询

分类方式(二):相关子查询[子查询的where字句中引用了外部查询]/不相关子查询

不相关子查询

SELECT *
FROM staff S1
WHERE salary>
	SELECT AVG(salary)
	FROM Staff S2
	WHERE S1.salary=S2.salary

多表查询

简单连接

SELECT c.clientNO,fName,IName,comment
FROM Client c,Viewing v
WHERE c.clientNo=v.clientNo
--------其他方式
FROM Client c JOIN Viewing v ON c.clientNo=v.clientNO
FROM Client JOIN Viewing USING clientNO
FROM Client NATURAL JOIN Viewing

排序连接

SELECT c.clientNO,fName,IName,comment
FROM Client c,Viewing v,
WHERE c.clientNo=v.clientNo
ORDER BY c.clientNo,v.comment

三表连接

按多个列分组

内连接

如果表中某一行不匹配另一表的任何行,那么这行将从表中删除.

左外连接

除了内连接的行,第一个表中与第二个表无匹配行的那些行也会显示.

SELECT b.*,p.*
FROM Branch b LEFT JOIN PropertyRorRent p ON b.bcity=p.pcity
----Branch的不匹配的信息也会显示,关于PropertyForRent就填NULL

右外连接

全外连接

两边表不匹配的信息也会显示

SELECT b.*,p.*
FROM Branch b FULL JOIN PropertyRorRent p ON b.bcity=p.pcity

合并结果表

并UNION,交INTERSECT,差EXCEPT

用于集合操作的表有一些限制,最重要的一点是两个表具有并相容性—两个表具有相同数目的列,且对应的列具有相同的数据类型和长度.

(SELECT city
FROM Branch
WHERE city IS NOT NULL)
UNION
(SELECT city
 FROM PropertyForRent
 WHERE city IS NOT NULL
)
或者
(SELECT *
FROM Branch
WHERE city IS NOT NULL)
UNION CORRESPONDING BY city
(SELECT *
 FROM PropertyForRent
 WHERE city IS NOT NULL
)

(SELECT city
FROM Branch
WHERE city IS NOT NULL)
INTERSECT
(SELECT city
 FROM PropertyForRent
 WHERE city IS NOT NULL
)
或者
(SELECT *
FROM Branch
WHERE city IS NOT NULL)
INTERSECT CORRESPONDING BY city
(SELECT *
 FROM PropertyForRent
 WHERE city IS NOT NULL
)

(SELECT city
FROM Branch
WHERE city IS NOT NULL)
EXCEPT
(SELECT city
 FROM PropertyForRent
 WHERE city IS NOT NULL
)
或者
(SELECT *
FROM Branch
WHERE city IS NOT NULL)
EXCEPT CORRESPONDING BY city
(SELECT *
 FROM PropertyForRent
 WHERE city IS NOT NULL
)

数据库更新

INSERT:向表中添加新的行

INSERT INTO 语句可以有两种编写形式。

第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：

INSERT INTO table_name
VALUES (value1,value2,value3,...);

第二种形式需要指定列名及被插入的值：

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

UPDATE:修改表中现有的行

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

DELETE：删除表中已有的行

DELETE FROM table_name
WHERE some_column=some_value;

ALTER TABLE 语句

ALTER TABLE table_name
ALTER COLUMN column_name datatype

DDL数据定义语言

完整性增强特性:必须有值的数据,域约束,实体完整性,引用完整性,一般性约束.

(1)必须有值的数据

某些列的值必须是有效值,不允许为空.

(2)域约束

合法值的集合.定义域两种方式:CHECK子句.或者CREATE DOMAIN语句

CHECK后面可以加SELECT查询

CREATE DOMAIN SexType AS CHAR DEFAULT 'M' CHECK（VALUE IN（‘M’，‘F’））;
--------------
sex CHAR NOT NULL CHECK(sex IN('M','F'))

(3)实体完整性

PRIMARY KEY()----主键,每个表中只能使用一个PRIMARY key子句,但是可以用UNIQUE保证列的唯一性

(4)引用完整性

FOREIGN KEY(branchNO) REFERENCES Branch;----外部关键字
-------------
CASCADE删除父表中的行并且自动删除子表中匹配的行
SET NULL删除父表中的元组且设置子表中的外部关键字为NULL
SET　DEFAULT删除父表中的元组且设置子表中的外部关键字为默认值
NO　ACTION拒绝对父表进行删除操作

(5)一般性约束.

和前面的check语句很像，作用范围更大

CREATE ASSERTION StaffPro
CHECK(NOT EXISTS(SELECT satffNo FROME PropertyForRent GROUP BY staffNo HAVING COUNT(*)>100))

数据定义

CREATE TABLE - 创建新表

ALTER TABLE- 变更（改变）数据库表

DROP TABLE - 删除表

CREATE SCHEMA-架构名建立架构

DROP SCHEMA+架构名删除架构

ALTER SCHEMA 新架构名改变架构

CREATE DOMAIN

DROP DOMAIN

ALTER DOMAIN

CREATE VIEW

DROP VIEW

ALTER VIEW

（1）创建数据库

SCHEMA是一组数据库对象的命名集合

DROP SCHEMA+架构名［ＲＥＳＴＲＩＣＴ｜ＣＡＳＣＡＤＥ］

ＲＥＳＴＲＩＣＴ　这个默认值的话，删除的模式必须是空的

（２）创建表

CREATE　TABLE　tablename
columnName dataType[Not NULL][UNIQIUE][DEFAULT value][CHEACK...]
------
ALTER TABLE tablename
set columnName dataType[Not NULL][UNIQIUE][DEFAULT value][CHEACK...]
ALTER columnName dataType[Not NULL][UNIQIUE][DEFAULT value][CHEACK...]
----------
CREATE TABLE a(
    arr1 int,
    arr2 char(14),
    arr3 date,
    hours int default 8 CHECK(hours>=0)
    Primary key(arr1),
    foreign key (arr1) references B(arr1),
    ...
)

(3)创建索引

和排序差不多

（3）创建视图

CREATE VIEW 视图名{（列1，列2，列3......）} AS
SELECT s.列1，s.列2,t.列3......
FROM 表1 s,表2 t
WHERE s.sNO=t.tNO
GROUP BY s.列1,t.列3
----删除视图---
DROP VIEW 视图名 [RESTRICT|CASCADE]
-------视图分解----
SELECT staffNo,cnt
FROM StaffView
WHERE branchNo='B003'
ORDER staffNo
------视图插入/更新---
INSERT INTO 视图名 VALUES（值1，值2......）;
UPDATE Manager3Staff SET branchNo='Boo5' WHERE staffNo='SG34';
------WITH CHECK OPTION----
CREATE VIEW 视图名{（列1，列2，列3......）} AS
SELECT s.列1，s.列2,t.列3......
FROM 表1 s,表2 t
WHERE s.sNO=t.tNO
WITH CHECK OPTION;

水平视图：水平视图限制用户只能访问一个或多个表中选定的元组。

垂直视图：垂直视图限制用户只能访问一个或多个表中选定的列。

**视图的局限性：**如果视图中某个列是基于聚集函数的，那么在访问该视图的查询语句中，该列只能出现在SELECT和ORDER BY子句里，也不能作为任何聚集函数的参数。

可更新视图：为了使视图可更新，对于任何一个行或列，DBMS必须能追溯到其源表中相应的行或列。

可更新性：

1)没有指定DISTINCT，即重复元组未从查询结果中消除

2）定义查询的SELECT列表中的每个元素均为列名，且列名出现的次数不多于一次

3）FROM只指定一个表

4）WHERE子句不能包括任何引用了FROM子句中的表的嵌套SELECT操作

5）定义查询中不能有GROUP BY或HAVING子句

优点:

数据独立性;实时性

（4）创建角色

CREATE ROLE manager
----授权
GRANT SELECT，UPDATE...（权限名字）
ON table
TO 角色 WITH GRANT OPTION
---授予全部权限----
CREATE ROLE Manager;
CREATE ROLE Director;
GRANT ALL PRIVILEGES
ON test01,test02
TO Manager,Director
WITH GRANT OPTION;---可以传递这些权限给其他用户
-----授予特定权限---
GRANT SELECT,UPDATE
ON test01,test02
TO Manager,Director
-----授予所有用户---
GRANT SELECT,UPDATE
ON test01,test02
TO PUBLIC;
------撤销用户权限---
REVOKE SELECT,UPDATE
ON staffView
FROM Manager;

My_summers

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【0430】SQL学习

SQLSQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引（键），规定表之间的链接，以及施加表间的约束。SQL 中最重要的 DDL 语句:CREATE DATABASE - 创建新数据库ALTER DATABASE - 修改数据库CREATE TABLE - 创建新表ALTER TABLE - 变更（改变）数据库表DROP TABLE - 删除表CREATE INDEX - 创建索引（搜索键）DROP INDEX - 删除索引查询和更新指令构成了 S
复制链接

扫一扫