当前位置：首页 > article >正文

SQL学习日记

article 2024/9/19 9:00:37

一、数据定义（create，alter，drop）

1.1数据类型

补充注释

1.2定义基本表（create，alter，drop）

1.3约束

1.3.1主键约束

1.3.2外码约束

编辑

补充CASCADE 关键字

1.3.3Check约束

1.3.4NOT NULL约束

1.3.5UNIQUE约束

1.3.6DEFAULT约束

1.3.7显示约束信息

二、修改基本表

2.1添加表中的列

2.2修改表中的列

2.3删除表中的列

2.4增加约束

2.5删除约束

2.6删除基本表

三、索引的建立与删除

3.1建立索引

3.2删除索引

3.3利用索引

四、数据操纵（insert，update，delete）

4.1插入数据

4.1.1插入数据基础

4.1.2 INSERT与子查询结合

4.2修改数据

4.2.1修改数据基础

4.2.2修改数据与子查询结合

4.3删除数据

五、单表查询（select）

4.1单表查询概述

4.1.1查询指定列

4.1.2查询全部列

4.1.3查询经过计算的值

补充使用列别名改变查询结果的列标题:

4.2关键字DISTINCT

4.3使用WHERE子句

4.3.1WHERE子句常用的查询条件

4.3.2比较

4.3.3确定范围

4.3.3确定集合

4.3.4字符匹配

4.3.5涉及空值的查询

4.3.6多重条件查询

4.4ORDER BY子句

4.5聚集函数

4.6GROUP BY和HAVING子句

六、多表查询

6.1连接查询

6.1.1WHERE子句中的连接查询

6.1.2FROM子句中的连接查询

6.2嵌套查询

6.2.1不相关子查询

6.2.2相关子查询

6.2.3带有比较运算符的子查询

6.2.3含有IN的子查询

6.2.4含有BETWEEN AND的子查询

6.2.5含有ALL和ANY的子查询

6.2.6带有EXISTS谓词的子查询

七、集合查询

7.1使用UNION（并集）

7.2使用INTERSECT（交集）

7.3使用EXCEPT（差集）

八、视图

8.1建立视图

8.1.1单表视图与多表视图

8.1.2视图套视图

8.1.3分组视图

8.2删除视图

8.3查询视图

8.4更新视图

九、数据安全

9.1授权（grant）

补充权限

补充PUBLIC

9.2回收权限（revoke）

9.3角色（权限的集合）

9.3.1角色的创建

9.3.2给角色授权

9.3.3将一个角色授予其他的角色或用户

9.3.4角色权限的收回

9.4审计

9.5数据加密

一、数据定义（create，alter，drop）

1.1数据类型

在SQL中，数据类型用来定义列中可以存储的数据的类型。常见的SQL数据类型包括：

数值型：

integer/int: 整数类型，占用4个字节；
smallint: 较小的整数类型，占用2个字节；
bigint: 较大的整数类型，占用8个字节；
decimal/numeric: 固定精度的十进制数，例如decimal(10,2)表示小数位数为2的最大长度为10的十进制数；
float/real: 浮点数，可以表示较大或较小的数，但精度较低。
number 数据类型可以存储任意精度的数字，包括正数、负数和零。

字符型：

char: 固定长度的字符型，例如char(10)表示长度为10的字符；
varchar: 可变长度的字符型，例如varchar(50)表示长度最大为50的字符。
varchar2 是 Oracle 数据库独有的数据类型，对于汉字占两个字节，对于数字、英文字符等是一个字节，占的内存大

日期和时间型：

date: 日期型，例如'2021-09-30'；
time: 时间型，例如'10:30:00'；
datetime: 日期和时间型，例如'2021-09-30 10:30:00'；
timestamp: 时间戳型，例如'2021-09-30 10:30:00.123456'。

布尔型：

boolean: 只能取值true或false的数据类型。

除了上述常见的数据类型之外，不同的数据库管理系统还可能支持其他类型，例如文本型、二进制型等。

补充注释

在 SQL 中，有两种类型的注释：单行注释和多行注释。单行注释以两个连字符（–）开头，多行注释以 /* 开头，以 */ 结尾。
-- 这是一个单行注释
SELECT * FROM my_table; -- 这也是一个单行注释

/*
这是一个多行注释
它可以跨越多行
*/

1.2定义基本表（create，alter，drop）

CREATE TABLE <表名> (
    <列名> <数据类型> [列级完整性约束定义] {,<列名> <数据类型> [列级完整性约束定义]……}
    [表级完整性约束定义]
);

Create Table student (
    id int Primary Key,
    name Varchar(50) NOT NULL,
    age int Check(age >= 18),
    gender Char(1) Default 'U' Check(gender In ('M', 'F', 'U')),
    address Varchar(100),
    phone Varchar(20) Unique,
    enrollment_date Date Default Current_Date
);

我们创建了一个名为"student"的表，并定义了六个列，分别是"id"、"name"、"age"、"gender"、"address"、"phone"和"enrollment_date"。其中"id"列被定义为主键，"name"列被定义为非空列，"age"列被定义为大于等于18的整数，"gender"列被定义为默认值为'U'的字符型，并且只允许取'M'、'F'或'U'三个值，"phone"列被定义为唯一的字符型，"enrollment_date"列被定义为默认值为当前日期的日期型。

1.3约束

约束是用于规定表中的数据规则的。如果存在违反约束的数据行为，行为会被约束终止。在SQL中，有列级约束和表级约束两种类型的约束。列级约束是行定义的一部分，只能应用于一列上。而表级约束是独立于列的定义，可以应用在一个表中的多列上.。

1.3.1主键约束

Create Table student (
    id int constraint pk_Course primary key,
    name Varchar(50) NOT NULL,
    age int Check(age >= 18),
    gender Char(1) Default 'U' Check(gender In ('M', 'F', 'U')),
    address Varchar(100),
    phone Varchar(20) Unique,
    enrollment_date Date Default Current_Date
);

另外一种形式

Create Table student (
    id int ,
    name Varchar(50) NOT NULL,
    age int Check(age >= 18),
    gender Char(1) Default 'U' Check(gender In ('M', 'F', 'U')),
    address Varchar(100),
    phone Varchar(20) Unique,
    enrollment_date Date Default Current_Date,
    Constraint pk_Course primary key(id)
);

id int constraint pk_Course primary key和id int primary key, Constraint pk_Course primary key(id)的区别在于前者使用了约束(constraint)关键字，而后者没有。在SQL中，约束是用来限制表中数据的完整性和正确性的。在这个例子中，约束是主键(primary key)，它保证了表中每一行的唯一性

在SQL中，id int constraint pk_Course primary key和id int primary key, Constraint pk_Course primary key(id)都是定义主键的方式。两者的区别在于前者是列级约束，而后者是表级约束

1.3.2外码约束

外键约束是一种用于防止破坏两个表之间的关联性，保证数据的完整性和一致性的约束。外键约束用于限制来自另一个表的数据插入当前表中的特定列，外键约束还可以用于确保引用表中的每个行都具有对应的主键值外键约束能够防止非法数据进入外键字段，因为它的值必须存在于它指向的主键中。

在SQL中，一个表可以有多个外键，但是只能有一个主键。

从表的外键类型，必须与主表的主键类型一致

列级约束

CREATE TABLE course (
    course_id int Primary Key,
    course_name Varchar(50)
);

CREATE TABLE student (
    student_id int Primary Key,
    student_name Varchar(50),
    course_id int,
    Foreign Key (course_id) References course(course_id)
);

表级约束

CREATE TABLE course (
    course_id int Primary Key,
    course_name Varchar(50)
);

CREATE TABLE student (
    student_id int Primary Key,
    student_name Varchar(50),
    course_id int,
    Constraint fk_course_id Foreign Key (course_id) References course(course_id)
);

删除主表记录时，必须先删除从表中的关联记录，否则主表中的记录将无法删除。

删除主表时，必须先删除从表，否则主表将无法删除

补充CASCADE 关键字

CASCADE是一个选项，用于删除/更改约束时指定级联删除。如果该约束被其他对象引用，则必须使用CASCADE选项删除/更改该约束。

1.3.3Check约束

CHECK约束是SQL中的一种约束，它指定了表中的列所允许的值的范围。CHECK约束不能在VIEW中定义，只能定义在表的列中。CHECK约束不能包含子查询

列级约束

Create Table student (
    student_id int Primary Key,
    student_name Varchar(50),
    age int Check (age Between 18 And 30)
);

表级约束

CREATE TABLE student (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    age INT CHECK (age BETWEEN 18 AND 30)
);

1.3.4NOT NULL约束

NOT NULL约束是SQL中的一种约束，它强制列不接受NULL值。NOT NULL约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50) ,
    age INT,
    gender CHAR(1) NOT NULL
);

1.3.5UNIQUE约束

UNIQUE约束是SQL中的一种约束，它唯一标识数据库表中的每条记录。UNIQUE和PRIMARY KEY约束均为列或列集合提供了唯一性的保证。PRIMARY KEY拥有自动定义的UNIQUE约束。请注意，每个表可以有多个UNIQUE约束，但是每个表只能有一个PRIMARY KEY约束

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    student_email VARCHAR(50) UNIQUE,
    age INT,
    gender CHAR(1)
);

多列唯一：unique(列名1，列名2)，表级约束。

1.3.6DEFAULT约束

DEFAULT约束用于向列中插入默认值。如果没有规定其他的值，那么会将默认值添加到所有的新记录。

列级约束

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    age INT DEFAULT 18,
    gender CHAR(1) DEFAULT 'M'
);

表级约束

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    age INT,
    gender CHAR(1),
    CONSTRAINT default_age DEFAULT 18 FOR age,
    CONSTRAINT default_gender DEFAULT 'M' FOR gender
);

1.3.7显示约束信息

可以通过查询数据字典视图user_cons_columns，可以显示约束所对应的表的列的信息。例如，使用以下语句：select column_name,position from user_cons_columns where constraint_name='约束名';。

二、修改基本表

2.1添加表中的列

ALTER TABLE Student ADD S_entrance date;

当向一个表中添加多个列时，用括号围住一个由逗号分隔的列声明列表。列声明包括列名称、列类型及默认值。

2.2修改表中的列

ALTER TABLE Student MODIFY Student_id DEFAULT ('2003122');

要修改多列，用括号括住要修改的列，指明列名和新特征，列之间用逗号分隔。

2.3删除表中的列

ALTER TABLE Student DROP COLUMN S_entrance;

要想删除多个列时，省略关键字COLUMN，并用括号括住要删除的列，列和列之间用逗号隔开

2.4增加约束

ALTER TABLE student ADD CONSTRAINT constraint_name UNIQUE (STUDENT_ID,STUDENT_NAME);

2.5删除约束

用alter语句添加主键约束 alter table table_name add [constraint constraint_name] primary key(column_name)

以下语句删除Oracle表中的主键约束：alter table table_name drop primary key;。

如果您想删除其他类型的约束，可以使用以下语句：alter table table_name drop constraint constraint_name;。

请注意，如果该约束被其他对象引用，则必须使用CASCADE选项删除该约束。例如，如果您想删除名为“constraint_name”的约束，则可以使用以下语句：alter table table_name drop constraint constraint_name cascade;。

ALTER TABLE Student DROP UNIQUE(STUDENT_ID,STUDENT_NAME);

约束一旦建成就允许被删除，当禁用UNIQUE或PRIMARY KEY约束时需要小心，因为禁用这些约束可能导致它所生成的索引被删除。如果想删除一个已经存在的约束，可以使用ALTER语句

2.6删除基本表

DROP TABLE <表名>

DROP TABLE Student;

三、索引的建立与删除

索引是一种数据结构，它可以帮助数据库系统更快地检索数据。在 SQL 中，可以使用 CREATE INDEX 语句来创建索引，使用 DROP INDEX 语句来删除索引。

简单来说，就是将数据库中的某一列或多列值按照一定规则存储起来，形成一个“索引”，这样在查询时就可以直接使用这个“索引”快速定位到所需数据，而不需要逐一扫描整个表格。

类似于书的目录（CSDN标题目录）

3.1建立索引

一般格式为：

CREATE [UNIQUE] [CLUSTER] INDEX <索引名>

ON <表名> (<列名>[<次序>][,<列名>[<次序>]]...);

其中，<表名>指定要建索引的基本表的名字。索引可以建在该表的一列或多列上，各列名之间用逗号分隔。每个<列名>后面还可以用<次序>指定索引值的排列次序，包括ASC(升序)和DESC(降序)两种，缺省值为ASC。

CREATE UNIQUE INDEX idx_student_id_name
ON Student (STUDENT_ID ASC, STUDENT_NAME ASC);

上述语句将在Student表格的id和name列上创建一个名为idx_student_id_name的唯一索引，并且以id列为第一排序关键字，以name列为第二排序关键字

3.2删除索引

一般格式为： DROP INDEX<索引名>;

DROP INDEX idx_student_id_name;

3.3利用索引

基于上述我们有一个名为Student的表格，其中有id、name、age和gender等列，并且我们已经在id和name列上创建了名为idx_student_id_name的索引。现在，我们可以使用以下查询语句利用该索引：

SELECT id, name
FROM Student
WHERE name = 'John'
ORDER BY id;

上述查询语句将在Student表格中查找所有name为John的行，并按id列的升序排列结果，由于我们已经在id和name列上创建了索引，因此这个查询将使用idx_student_id_name索引来加速查询操作，提高查询效率。

四、数据操纵（insert，update，delete）

4.1插入数据

4.1.1插入数据基础

语法格式

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

其中，table_name是要插入数据的表名，column1、column2、column3等是要插入的列名，value1、value2、value3等是要插入的值。

举例

INSERT INTO students (name, age, gender) VALUES ('张三', 20, '男');

4.1.2 INSERT与子查询结合

插入子查询结果的INSERT语句的格式为： INSERT INTO <表名> [(<属性列1> [,<属性列2>...)] (子查询)；

4.2修改数据

4.2.1修改数据基础

语法格式

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

其中，table_name是要修改的表名称，column1、column2等是要修改的字段名称，value1、value2等是要修改的值，condition是修改条件，用于指定哪些数据要修改。

举例说明

UPDATE table_name SET name = '张三' WHERE id = 1;

将表中id为1的name字段改为“张三”

4.2.2修改数据与子查询结合

4.3删除数据

语法格式

DELETE 语句用于删除表中的行。DELETE FROM table_name WHERE condition; 参数说明：table_name：要删除的表名称。condition：删除条件，用于指定哪些数据要删除。

DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句，表示删除表中全部元组，但表的定义仍在数据字典中。DELETE语句删除的是表中的数据，而不是关于表的定义

delete from STUDENT where sdept in
(
    select sdept from STUDENT
    where sname='刘晨'
);

五、单表查询（select）

查询是从数据库中检索满足条件的数据的过程。在SQL中，查询语句（SELECT）是数据库中最基本的和最重要的语句之一，其功能是从数据库中检索满足条件的数据

4.1单表查询概述

SELECT语句的基本语法如下：SELECT column1, column2, … FROM table_name;

其中，column1、column2等是要查询的列名，table_name是要查询的表名。例如，如果您想要从名为“customers”的表中选择所有列，则可以使用以下SQL语句：SELECT * FROM customers;

4.1.1查询指定列

查询全体学生的姓名、学号、所在系。

SELECT Sname，Sno，Sdept
		FROM Student；

4.1.2查询全部列

SELECT  *
FROM Student；

4.1.3查询经过计算的值

算术表达式

从Student表中查找学生姓名，出生日期。
SELECT Sname, 2007-Sage FROM Student;
这个查询语句会返回一个名为Sname的列和一个名为2007-Sage的列。第一个列包含学生姓名，第二个列包含学生年龄与2007年之间的差值。

这个查询语句中的计算列是2007-Sage，它是通过将2007年减去Sage列中的值来计算得到的

字符串常量

查询学生的姓名和出生日期，出生日期用别名Stu birthday显示。
SELECT Sname, 2009-Sage  “Stu birthday”
FROM Student;
从名为“Student”的表中选择“Sname”和“2009-Sage”列，并将结果命名为“Stu birthday”。这个查询语句的结果将显示学生的姓名和他们的出生日期

补充使用列别名改变查询结果的列标题:

使用列别名可以改变查询结果的列标题。在SQL中，有三种方法可以使用列别名：使用AS字句、使用“=”和使用空格。

SELECT Sname NAME，'Year of Birth: ’ BIRTH，
       2000-Sage BIRTHDAY，LOWER(Sdept)  DEPARTMENT
	FROM Student；

可能有这样的情况，对于查出来的列有时列名不能清晰表达列的含义，或显示的是列的表达式。这些情况用户可以在列的后面(用空格分开)指定相应列的别名，如果别名中含有空格，则用双引号进行包含。

4.1_5别名

AS – 别名

通过使用 SQL，可以为列名称和表名称指定别名（Alias），别名使查询程序更易阅读和书写。

语法

表别名：
SELECT 列名称/(*) FROM 表名称 AS 别名;
举例
SELECT p.LastName, p.FirstName
FROM Persons p 
WHERE p.LastName='Adams' AND p.FirstName='John';
列别名：
SELECT 列名称 as 别名 FROM 表名称;
举例
SELECT LastName "Family", FirstName "Name" FROM Persons;
注意： 实际应用时，这个 AS 可以省略，但是列别名需要加上 " "。

4.2关键字DISTINCT

DISTINCT是Oracle中的关键字，用于从结果集中过滤重复行。它确保在SELECT子句中指定的列或列的行是唯一的。

如果没有指定DISTINCT,则默认为ALL，那么将返回所有行，包括重复的行。

语法格式：

语法：SELECT DISTINCT column_1 FROM table; 如果要根据多列检索唯一数据，只需要在SELECT子句中指定列的列表，如下所示：SELECT DISTINCT column_1, column_2, ... FROM table_name;

举例说明
SELECT DISTINCT Sname，Sage 
FROM Student;
对于选择出的每个<Sname,Sage>行，如果有两个或多个同学有同样的名字和年龄，则在结果中只显示一次。

4.3使用WHERE子句

WHERE子句是SQL语句中的一个子句，用于计算表中的每一行。如果条件计算为true，则满足条件的行记录将包含在结果集中；否则，它将被排除在外。请注意，SQL具有三值逻辑，即TRUE，FALSE和UNKNOWN。

4.3.1WHERE子句常用的查询条件

4.3.2比较

查询所有年龄不等于20岁的学生姓名及其年龄。
SELECT Sname, Sage 
FROM Student 
WHERE Sage <>20; 

4.3.3确定范围

使用BETWEEN AND操作符可以选中排列于两值(包括这两个值)之间的数据。

举例说明

查询年龄在20至23岁之间的学生的姓名和年龄。
SELECT Sname, Sage
FROM Student 
WHERE Sage BETWEEN 20 AND 23; 

4.3.3确定集合

IN操作符用于在WHERE子句中指定多个值。它允许您在WHERE子句中指定多个值，而不是使用多个OR条件。

与IN相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组。

举例说明

查询年龄为18或者20的学生的姓名和年龄。
SELECT Sname, Sage 　
FROM Student 　
WHERE Sage IN (18,20);

4.3.4字符匹配

LIKE运算符用于在WHERE子句中指定搜索模式。它可以与通配符一起使用，以便更灵活地搜索数据。

其一般语法格式如下： [NOT] LIKE '<模式>' [ESCAPE '<换码字符>']

其中，<模式>是搜索模式，可以包含通配符。NOT是可选的，用于指定不匹配模式。ESCAPE是可选的，用于指定转义字符。

通配符：

%：匹配任意字符（包括空格）。
_：匹配单个字符。
[]：匹配指定范围内的任意单个字符。
[^]：不匹配指定范围内的任何单个字符。

[]是一个特殊的通配符，用于匹配指定范围内的任意单个字符。
SELECT * FROM Customers
WHERE CustomerName LIKE '[ab]%';
将返回所有以“a”或“b”开头的客户

[^]是一个特殊的通配符，用于不匹配指定范围内的任何单个字符
SELECT * FROM Customers
WHERE CustomerName LIKE '[ab]%';
返回所有以“a”或“b”开头的客户

%：匹配任意字符（包括空格）。
SELECT Sname, Sno
FROM Student 
WHERE Sname LIKE '张%'; 
查所有姓张的学生的姓名、学号

_：匹配单个字符
SELECT Sname, Sno 
FROM Student 
WHERE Sno LIKE '%1_'; 
查学号中倒数第二个数字为1的学生姓名和学号。

注意：

由于数据存储方式的原因，使用包含Char数据模式的字符串比较无法通过LIKE比较。例如Student表中Sname属性的数据类型是Char(6)，存在姓名为Dtt的学生，但是通过WHERE Sname LIKE '_tt'语句查不到记录，这是因为Char是定长的数据类型，在存储“Dtt”时，默认以空格补足后面的3位长度。

ESCAPE ‘\’短语表示\为换码字符，这样匹配串中紧跟在\后面的字符”_”不再具有通配符的含义，而是取其本身含义，被转义为普通的“_”字符。
SELECT Cno, Ccredit
FROM Course
WHERE Cname LIKE ‘DB\_Design%’ ESCAPE ‘\’;
查DB_Design开头课程的课程号和学分。

4.3.5涉及空值的查询

因为空值表示缺少数据，所以空值和其它值没有可比性，即不能用等于、不等于、大于或小于和其它数值比较，测试空值只能用比较操作符IS NULL和IS NOT NULL。
SELECT Sno, Cno 
FROM SC
WHERE Grade IS NULL; 
注意这里的'IS'不能用等号代替。

4.3.6多重条件查询

4.4ORDER BY子句

ORDER BY子句用于按升序或降序对结果集进行排序，其中升序ASC为缺省值。如果没有指定查询结果的显示顺序

一般语法格式：
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
其中，column1, column2, …是要排序的列的名称，table_name是要排序的表的名称，ASC表示升序，DESC表示降序。

查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。
SELECT Sno, Grade
FROM SC
WHERE Cno=3 ORDER BY Grade DESC;
可能有些学生选修了3号课程后没有参加考试，即成绩列为空值。用ORDER BY子句对查询结果按成绩排序时，若按升序排，成绩为空值的元组将最后显示，若按降序排列，成绩为空值的元组将最先显示。

4.5聚集函数

聚集函数是用于对一组值执行计算并返回单个值的函数

AVG()：返回一组值的平均值。
COUNT()：返回一组值的行数。
MAX()：返回一组值的最大值。
MIN()：返回一组值的最小值。
SUM()：返回一组值的总和。

注意：

1.聚集函数遇到空值时，除count（*）外，都跳过空值而只处理非空值。

2.where子句中不能用聚集函数作为条件表达式。
SELECT Sname,Sage
FROM Student
WHERE Sage=(SELECT MAX(Sage) FROM Student);

补充

LEN/LENGTH – 获取长度

语法：
select length(列名) from 表名;
实例：

获取 LASTNAME 的值字符长度：
select length(lastname),lastname from persons;
ROUND – 数值取舍

语法：
select round(列名,精度) from 表名;
实例：

保留2位：
select round(1.1314,2) from dual;
select round(1.1351,2) from dual;
NOW/SYSDATE – 当前时间

语法：
select sysdate from 表名;
实例：

获取当前时间
select sysdate from dual;

4.6GROUP BY和HAVING子句

GROUP BY和HAVING子句是SQL查询语句中的两个重要部分。GROUP BY子句用于将结果集按照一个或多个列进行分组，HAVING子句用于筛选分组后的结果集。HAVING子句只能与GROUP BY一起使用，而WHERE子句可以在GROUP BY之前使用。如果在一条SQL语句中同时使用WHERE、GROUP BY和HAVING，则应该按照WHERE、GROUP BY、HAVING的顺序使用它们

查询平均分在80分以上的学生的学号及其选课数。
SELECT Sno,Count(Cno)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>80;

六、多表查询

6.1连接查询

SQL连接查询是指在SQL语句中使用JOIN关键字，将多个表中的数据进行关联查询的操作。连接查询包括内连接、外连接、自连接等。内连接是指只返回两个表中匹配的行，而外连接则会返回两个表中所有的行，其中一个表中没有匹配的行用NULL值填充。自连接是指在同一张表中进行连接查询。

用WHERE子句连接的查询一般分为等值连接查询、非等值连接查询、自然连接查询、外部连接查询和复合条件连接查询；

用FROM子句连接的查询一般分为内连接、外连接和交叉连接

6.1.1WHERE子句中的连接查询

等值连接和非等值连接

等值连接和非等值连接

查询每个学生及其选修课程的情况。
SELECT * 
FROM Student, SC 
WHERE Student.Sno=SC.Sno;
自身连接

自身连接是指一个表自己连接自己，通常使用表的别名来实现。

查询每一门课的间接先修课(即先修课的先修课)。
SELECT FIRST.Cno, SECOND. Cpno 
FROM Course FIRST, Course SECOND 
WHERE FIRST. Cpno =SECOND.Cno; 
外连接

Oracle 的外连接查询分为：左外连接（左边的表不加限制）left outer join；右外连接（右边的表不加限制）right outer join；全外连接（左右两表都不加限制）full outer join。通常 outer 关键字可省略，写成：left/right/full join 即可

假设有一个名为employee的表，其中包含员工ID、姓名和上级ID：如果我们想要查询每个员工的姓名以及他们的上级的姓名，可以使用自身连接来实现：
 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade 
    FROM Student, SC 
    WHERE Student.Sno=SC.Sno(+); 
其中的“(+)”表示右外连接。在这个例子中，Student表是左表，SC表是右表。在这个例子中，我们使用了“Student.Sno=SC.Sno(+)”来表示右外连接。这个语句将返回Student表和SC表的所有行，如果SC表中没有与Student表匹配的行，则返回NULL值

复合条件连接

6.1.2FROM子句中的连接查询

内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。

内连接又分为

等值连接
自然连接
不等连接

等值连接

查询选修了课程的学生全部信息。
SELECT *
　　FROM Student INNER JOIN Sc
　　ON Student.Sno=Sc.Sno;
自然连接
SELECT Student.*,Cno,Grade
　　FROM Student natural JOIN Sc
　　ON Student.Sno=Sc.Sno;

外连接

SQL外连接是一种查询操作，它可以返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表（左外连接）、右表（右外连接）或两个边接表（全外连接）中的所有数据行。

在 SQL 中，外连接可以使用 LEFT JOIN、RIGHT JOIN、FULL JOIN 等关键字来实现。
SELECT * 
       FROM Student LEFT JOIN Sc
　　ON Student.Sno=Sc.Sno;

交叉连接

交叉连接（CROSS JOIN）是 SQL 中的一种连接方式，也称为笛卡尔积。它用于从两个或多个表中返回记录集的笛卡尔积，即将左表的每一行与右表的每一行合并。
SELECT *
FROM Student CROSS JOIN Course;

6.2嵌套查询

嵌套查询（Nested Query）是 SQL 中的一种查询方式，也称为子查询。它是指将一个 SELECT 查询（子查询）的结果作为另一个 SQL 语句（主查询）的数据来源或者判断条件。子查询可以嵌入 SELECT、INSERT、UPDATE 和 DELETE 语句中，也可以和 =、<、>、IN、BETWEEN、EXISTS 等运算符一起使用。

6.2.1不相关子查询

不相关子查询（Non-Correlated Subquery）是指子查询中的数据来自于子查询所在的表，而不是来自于主查询中的表。不相关子查询可以用 =、<、>、IN、BETWEEN 等运算符，也可以用 ALL、ANY、SOME 等关键字。

6.2.2相关子查询

相关子查询（Correlated Subquery）是指子查询中的数据来自于主查询中的表，而不是来自于子查询所在的表。相关子查询可以用 EXISTS 或 NOT EXISTS 运算符，也可以用 IN 或 NOT IN 运算符。

6.2.3带有比较运算符的子查询

如果确切知道子查询返回的是单值，可以用=、>、>=、<、<=、<>比较运算符连接子查询和主查询。

查询与“刘晨”在同一个系学习的学生。
  SELECT Sno，Sname，Sdept
    	FROM Student
   	WHERE Sdept  =
                  (SELECT Sdept
                   FROM Student
                   WHERE Sname= ‘ 刘晨 ’)；
自身连接完成
 SELECT  S1.Sno，S1.Sname，S1.Sdept
      FROM     Student S1，Student S2
      WHERE  S1.Sdept = S2.Sdept  AND
                      S2.Sname = '刘晨'；

6.2.3含有IN的子查询

IN 子查询是指在 SQL 语句中使用 IN 运算符来进行子查询，它可以用于 WHERE 子句、HAVING 子句和 FROM 子句中。IN 子查询的作用是将一个查询结果集作为另一个查询的条件，从而实现更加复杂的查询。

查询所有选修了1号课程的学生的学号、姓名。

含有IN的子查询
SELECT Sno,Sname
FROM Student 
WHERE Sno IN 
 (SELECT Sno 
    FROM SC 
    WHERE Cno=1)；
连接查询
SELECT Student.Sno,Sname 
FROM Student,SC 
WHERE Student.Sno=SC.Sno AND Cno=1;

6.2.4含有BETWEEN AND的子查询

［NOT］BETWEEN…AND 也可以作为嵌套查询的连接词。子查询可以跟在BETWEEN后面，也可以跟在AND后面。

查找从19岁到Student表中年龄最大之间的学生学号和姓名。
SELECT Sno,Sname 
FROM Student 
WHERE Sage BETWEEN 19 AND 
(SELECT MAX(Sage) 
FROM Student );

6.2.5含有ALL和ANY的子查询

ANY表示如果子查询中任意一个值满足条件，则返回True，否则返回False。

ALL表示只有当子查询中所有值都满足条件时才返回True，否则返回False

查询年龄比数学系最小的学生还小的学生学号和姓名
SELECT Sno,Sname FROM Student 
WHERE Sage<ALL
(SELECT Sage FROM Student 
WHERE Sdept=‘MA’);
聚集函数
SELECT 学号, 姓名
FROM 学生
WHERE 年龄 < (SELECT MIN(年龄) FROM 学生 WHERE 系别 = '数学');

6.2.6带有EXISTS谓词的子查询

带有EXISTS谓词的子查询通常用于检查主查询中的记录是否在子查询中存在。当子查询返回结果集时，EXISTS会返回TRUE，否则返回FALSE。

NOT EXISTS谓词则相反

查询所有选修了1号课程的学生姓名。

SELECT Sname
FROM Student 
WHERE EXISTS 
      (SELECT * 
        FROM SC 
        WHERE Sno=Student.Sno AND Cno=1);

含有IN嵌套查询

SELECT Sname
FROM Student 
WHERE Sno IN 
      (SELECT Sno 
        FROM SC 
        WHERE Cno=1);

=ANY的嵌套查询

SELECT Sname
FROM Student 
WHERE Sno=ANY 
      (SELECT Sno 
        FROM SC 
        WHERE Cno=1);

一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换

七、集合查询

集合查询是SQL中的一种查询方式，用于对多个表进行查询并返回结果。常见的集合查询包括UNION、UNION ALL、INTERSECT和EXCEPT。UNION用于返回两个查询结果的并集，UNION ALL用于返回两个查询结果的并集，包括重复的行；INTERSECT用于返回两个查询结果的交集；EXCEPT用于返回第一个查询结果中不在第二个查询结果中出现的行。

7.1使用UNION（并集）

使用UNION将多个查询结果合并起来，形成一个完整的查询结果时，系统会自动去掉重复的元组。需要注意的是，参加UNION操作的各数据项数目必须相同，对应项的数据类型也必须相同。

查询选修1号课程或者选修2号课程的学生学号。
SELECT Sno 
FROM SC 
WHERE Cno=1 
UNION 
SELECT Sno
FROM SC 
WHERE Cno=2 

7.2使用INTERSECT（交集）

查询选修1号课程和2号课程的学生姓名。

SELECT Sname
FROM SC,Student
WHERE Cno=1 AND Student.Sno=SC.Sno 
INTERSECT
SELECT Sname
FROM SC,Student
WHERE Cno=2 AND Student.Sno=SC.Sno

7.3使用EXCEPT（差集）

在Oracle中支持这三种操作，其中EXCEPT用MINUS关键字表示。

查询选修1号课程但是没有选修2号课程的学生学号。
SELECT Sno 
FROM SC 
WHERE Cno=1 
MINUS
SELECT Sno
FROM SC 
WHERE Cno=2 

八、视图

视图是SQL中的一种对象，它是一个虚拟表，由一个或多个表的行和列组成。视图可以看作是对表的一种抽象，它可以隐藏底层表的复杂性，简化查询操作。视图可以用于限制用户对表的访问权限，只允许用户访问视图中指定的列或行。视图还可以用于将多个表的数据合并成一个逻辑表，方便查询和分析。

8.1建立视图

建立视图的语法格式如下：

CREATE VIEW view_name AS SELECT column1, column2, … FROM table_name WHERE condition [WITH CHECK OPTION];

其中，view_name是视图的名称，column1、column2等是视图中包含的列，table_name是视图所基于的表，condition是筛选条件（其中子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDER BY子句和DISTINCT短语），WITH CHECK OPTION是可选的，用于限制对视图的更新操作，只允许更新符合条件的数据，使用CREATE VIEW语句可以创建一个新的视图，该视图将基于指定的表和列，并根据指定的条件筛选数据。创建视图后，可以使用SELECT语句来查询视图中的数据。

8.1.1单表视图与多表视图

CREATE VIEW student_course_view (Sno, Sname, Cno, Cname) 
AS SELECT student.Sno, student.Sname, course.Cno, course.Cname 
FROM student, course, sc
WHERE student.Sno = sc.Sno AND course.Cno = sc.Cno
WITH CHECK OPTION;
这个视图名为student_course_view，包含Sno、Sname、Cno和Cname这四个列，基于student、course和sc三个表，筛选出学生选修的课程信息。WITH CHECK OPTION用于限制对视图的更新操作，只允许更新符合条件的数据。这个视图可以用于查询学生选修的课程信息。

WITH CHECK OPTION用于限制对视图的更新操作，只允许更新符合条件的数据。例如，如果视图中包含了WHERE子句，那么只有满足WHERE子句的数据才能被更新。如果试图更新不符合条件的数据，就会出现错误。这个特性可以保证视图中的数据始终符合指定的条件，避免了数据不一致性的问题。

8.1.2视图套视图

视图不仅可以建立在单个基本表上，也可以建立在多个基本表上，也可以建立在一个或多个已定义好的视图上，或同时建立在基本表与视图上。

8.1.3分组视图

可以用带有集合函数和GROUP BY子句的查询来定义视图。这种视图称为分组视图。

8.2删除视图

要删除一个视图，可以使用DROP VIEW语句。语法格式如下：

DROP VIEW view_name;

其中，view_name是要删除的视图的名称。执行这个语句后，指定的视图将被删除。

一个视图被删除后，由此视图导出的其他视图也将失效，用户应该使用DROP VIEW语句将他们一一删除。

8.3查询视图

视图定义后，用户就可以象对基本表进行查询一样对视图进行查询了。

8.4更新视图

更新视图包括插入(INSERT)、删除(DELETE)和修改(UPDATE)三类操作。由于视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。

为防止用户通过视图对数据进行增删改时，无意或故意操作不属于视图范围内的基本表数据，可在定义视图时加上WITH CHECK OPTION子句，这样在视图上增删改数据时，DBMS会进一步检查视图定义中的条件，若不满足条件，则拒绝执行该操作。

九、数据安全

9.1授权（grant）

GRANT语句是Oracle数据库中的一种授权语句，用于授予用户对数据库对象的访问权限。GRANT语句的一般格式如下：
GRANT <权限>[,<权限>]... 
[ON <对象名>]
TO <用户>[,<用户>]...
[WITH GRANT OPTION];
其中，<权限>表示要授予的权限，可以是系统级别的，也可以是对象级别的；<对象名>表示要授权的对象名，可以是表、视图、序列等；<用户>表示要授权的用户，可以是一个或多个用户；WITH GRANT OPTION表示授予被授权者将其拥有的权限授予给其他用户的权利。

补充权限

Oracle数据库中的权限包括系统级别的权限和对象级别的权限。系统级别的权限包括：

CREATE SESSION：允许用户连接到数据库。
CREATE TABLE：允许用户创建表。
CREATE VIEW：允许用户创建视图。
CREATE SEQUENCE：允许用户创建序列。
CREATE PROCEDURE：允许用户创建存储过程。
CREATE TRIGGER：允许用户创建触发器。
CREATE SYNONYM：允许用户创建同义词。
DROP ANY TABLE：允许用户删除任何表。
DROP ANY VIEW：允许用户删除任何视图。
DROP ANY SEQUENCE：允许用户删除任何序列。
DROP ANY PROCEDURE：允许用户删除任何存储过程。
DROP ANY TRIGGER：允许用户删除任何触发器。
DROP ANY SYNONYM：允许用户删除任何同义词。

对象级别的权限包括：

SELECT：允许用户查询表或视图中的数据。
INSERT：允许用户向表中插入数据。
UPDATE：允许用户更新表中的数据。
DELETE：允许用户删除表中的数据。
ALL PRIVILEGES关键字将数据库对象的所有权限授予用户

补充PUBLIC

9.2回收权限（revoke）

授予的权限可以由DBA或其他授权者用REVOKE语句收回

REVOKE语句的一般格式为：

REVOKE <权限>[,<权限>]

[ON <对象名>]

FROM <用户>[,<用户>]...;

其中，<权限>表示要收回的权限，可以是系统级别的，也可以是对象级别的；<对象名>表示要收回权限的对象名，可以是表、视图、序列等；<用户>表示要收回权限的用户，可以是一个或多个用户。

9.3角色（权限的集合）

9.3.1角色的创建

CREATE ROLE <角色名>

create role testrole; # 角色名应该不能含数字，试了一下，role1无法创建

9.3.2给角色授权

 GRANT  <权限>［，<权限>］

ON <对象类型>对象名  

 TO <角色>［，<角色>］
其中，<权限>表示要授予的权限，可以是系统级别的，也可以是对象级别的；<对象类型>表示要授予权限的对象类型，可以是表、视图、序列等；<对象名>表示要授予权限的对象名；<角色>表示要授予权限的角色，可以是一个或多个角色。

grant select on sc to testrole;

9.3.3将一个角色授予其他的角色或用户

GRANT  <角色1>［，<角色2>］…
TO  <角色3>［，<用户1>］… 
［WITH ADMIN OPTION］ 
其中，<角色1>和<角色2>表示要授予的角色，可以是一个或多个角色；<角色3>和<用户1>表示要授予权限的角色或用户，可以是一个或多个；WITH ADMIN OPTION表示授予权限的角色可以将该权限授予其他用户或角色。

grant testrole to S2018214184u5;

9.3.4角色权限的收回

REVOKE <权限>［，<权限>］…
ON <对象类型> <对象名>
FROM <角色>［，<角色>］…
其中，<权限>表示要收回的权限，可以是系统级别的，也可以是对象级别的；<对象类型>表示要收回权限的对象类型，可以是表、视图、序列等；<对象名>表示要收回权限的对象名；<角色>表示要收回权限的角色或用户，可以是一个或多个。

select * from D2018214184.sc; # 在u5中查询

9.4审计

在Oracle中，您可以使用AUDIT语句设置审计功能，也可以使用NOAUDIT语句取消审计功能。这两个语句的一般格式如下：
AUDIT <操作>[,<操作>]...
[BY <用户>[,<用户>]...]
[WHENEVER SUCCESSFUL|FAILURE|NOT SUCCESSFUL];

NOAUDIT <操作>[,<操作>]...
[BY <用户>[,<用户>]...]
[WHENEVER SUCCESSFUL|FAILURE|NOT SUCCESSFUL];
其中，<操作>表示要审计或取消审计的操作，可以是SELECT、INSERT、UPDATE、DELETE等；<用户>表示要审计或取消审计的用户，可以是一个或多个；WHENEVER SUCCESSFUL|FAILURE|NOT SUCCESSFUL表示只在成功、失败或不成功时才进行审计。

9.5数据加密

在Oracle中，您可以使用透明数据加密（TDE）或包DBMS_CRYPTO来加密数据。TDE是一种透明的加密技术，它可以对整个表空间进行加密，而不需要修改应用程序。DBMS_CRYPTO是一个包，它提供了一组加密和解密函数，可以用于加密和解密应用程序数据。这些函数支持各种加密算法，如AES、DES、3DES、MD5等。

查看全文

http://www.kler.cn/news/18540.html