数据库_关系数据库SQL语言

SQL语言概述

SQL语言是面向集合的非过程化的语言。是介于关系代数和关系演算之间的标准查询语言。

集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL功能于一体。

核心功能：9个动词

• 数据查询：SELECT（最为复杂的操作）

• 数据定义：CREATE（创建）、DROP（删除）、ALTER（更新）

• 数据操纵DML：INSERT、UPDATE、DELETE

• 数据控制DCL：GRANT（授权）、REVOKE（撤销，回收权限）

• 基本概念：

  

数据定义概述

数据定于语言DDL：是SQL中提供给用户定义DBMS支持的抽象对象的SQL语句。

DBMS支持4种抽象对象：模式、表、视图、索引

一个RDBMS的实例中，可建立多个数据库；

一个数据库中可以建立多个外模式，只有一个内模式；

一个模式下可以建立多个表、视图和索引等数据库对象

基本表的定义、修改和删除

定义基本表

CREATE TABLE<表名>（<列名> <数据类型>[ <列级完整性约束条件> ][，<列名> <数据类型>[ <列级完整性约束条件>]]...[，<表级完整性约束条件> ]）；

可以加多个列级完整性约束条件。

根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only

模式和表

• 每一个基本表都属于某一个模式，一个模式包含多个基本表
• 定义基本表所属模式：
  • 方法一：在表名中明显地给出模式名
    CREATE TABLE S-T.Student（……）; /模式名为S-T/
  • 方法二：在创建模式语句中同时创建表
    CREATE SCHEMA S-T AUTHORIZATION WANG
    CREATE TABLE Student（……）;

修改表

ALTER TABLE<表名>
[ ADD<新列名> <数据类型> [ 完整性约束 ]  ]
[ DROP[COLUMN]<列名> [CASCADE|RESTRICT]]
[ADD<表级完整性约束名> ] 
[DROP CONSTRAINT<完整性约束名> [CASCADE|RESTRICT]] 
[ALTER COLUMN<列名> <数据类型> ]；

//新增加的列一律为空值。

删除表

DROP TABLE <表名>［RESTRICT| CASCADE］;

RESTRICT：删除表是有限制的。欲删除的基本表不能被其他表的约束所引用，如果存在依赖该表的对象，则此表不能被删除

CASCADE：删除该表没有限制。在删除基本表的同时，相关的依赖对象一起删除

数据查询

基本语法：

SELECT A1,A2··· FROM R1,R2··· WHERE F

这里R1,…,Rm为关系，F是公式，A1,…,An为属性。

完整语法：

SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]...
FROM <表名或视图名>[，<表名或视图名> ] ...
[WHERE<条件表达式>]
[GROUP BY<列名1>[HAVING<条件表达式>] ]
[ORDER BY<列名2>[ ASC|DESC ] ]；

• SELECT子句，投影运算，选择表中的若干列；
• WHERE子句，行筛选条件，选择表中的若干行；
• GROUP子句，分组运算；
• HAVING子句，分组条件；
• ORDER子句，排序计算。

单表查询

• 消除重复元组Distinct

• where子句：如何从表中选择指定元组？

  • 对应于关系代数运算σP，SQL提供WHERE子句解决表元组的选择。

  • 格式：WHERE <条件表达式>【<条件表达式>是包含属性名的逻辑表达式P】

  • 

  • 范围查询：

    • 比较运算符：=、<、>、>=、<=、!=、<>、!>、!
    • <确定范围：BETWEEN … AND …NOT BETWEEN … AND …

  • 集合查询：

    • x IN <值表>, x NOT IN <值表>

  • 字符匹配查询：

    • [NOT]LIKE‘<匹配串>’[ESCAPE‘ <换码字符>’]

      查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符。
      通配符：SQL规定符号百分号%及下划线__ 具有其他含义:
      百分号%：代表任意长度的字符串
      下划线__ ：代表任意一个字符
      ESCAPE：是将百分号% 或下划线__转回其本意

  • 涉及空值查询：

    • IS NULL 或IS NOT NULL

  • 多重条件查询：

    • 逻辑运算符：AND和OR可以用来将多个简单查询条件复合成更加复杂的条件
    • 优先级：NOT>AND>OR

• Order By子句：将查询结果的元组按照一个或多个属性列的排列次序显示

  • 格式：SELECT 块 ORDER BY 子句；
    其中，ORDER BY子句可以按一个或多个属性列排序；
    每列可选择：升序：ASC；降序：DESC；缺省值为升序

使用函数

函数只是将取出的数据进行处理，不会改变数据库中的值。

单行函数

1、字符函数

函 数	功 能	示 例	结 果
INITCAP (char)	首字母大写	initcap (‘hello’)	Hello
LOWER (char)	转换为小写	lower (‘FUN’)	fun
UPPER (char)	转换为大写	upper (‘sun’)	SUN
LTRIM (char, set)	左剪裁	ltrim (‘xyzadams’, ‘xyz’)	adams
RTRIM (char, set)	右剪裁	rtrim (‘xyzadams’, ‘ams’)	xyzad
TRANSLATE (char, from, to)	按字符翻译	translate (‘jack’, ‘abcd’, ‘1234’)	j13k
REPLACE (char, search_str, replace_str)	字符串替换	replace (‘jack andjue’, ‘j’, ‘bl’)	black and blue
INSTR (char, substr[, pos])	查找子串位置	instr (‘worldwide’, ‘d’)	5
SUBSTR (char, pos, len)	取子字符串	substr (‘abcdefg’,3,2)	cd
CONCAT (char1, char2)	连接字符串	concat (‘Hello’, ‘world’)	Helloworld

多行函数

1、SQL支持聚集函数来解决求集合特征值问题：如该集合的个数、最小值等

集函数只能用于SELECT子句和HAVING子句中

2、GROUP BY子句：将一个查询结果集合进行分组

• GROUP BY A1，A2，…,An（其中：Ai为属性名）【按指定的一列或多列，对一个SELECT块按值分组，值相等的为一组】

3、HAVING子句：使用限定条件选择部分分组，则可以使用HAVING子句

注意：HAVING短语与WHERE子句的区别：
– WHERE子句作用于基表或视图，从中选择满足条件的元组
– HAVING短语作用于组，从中选择满足条件的组。

多表连接查询

SQL1999规范中规定的连接查询语法

select 字段列表
from table1
[cross join table2] |                                    //1：交叉连接
[natural join table2] |                                  //2：自然连接
[join table2 using (字段名)] |                      //3：using子句
[join table2 on (table1.column_name 
	= table2.column_name)] |      //4：on子句
[(left | right | full outer) join table2
on (table1.column_name = table2.column_name)]; //5：左/右/满外连接

• 合并：FROM
• 选择：WHERE
• 投影：SELECT

自身连接

两种连接表示方法：

1、在FROM子句中指明进行连接的表名，在WHERE子句中指明连接的列名及其连接条件

2、利用关键字JOIN进行连接，通过关键词ON与JOIN相对应，指明连接条件。具体分为以下几种：

交叉连接

• Cross join产生了一个笛卡尔集，其效果等同于在两个表进行连接时未使用WHERE子句限定连接条件;
• 可以使用where条件从笛卡尔集中选出满足条件的记录。

用法举例：

<!--两张表连接-->
select * from emp,dept 
	where emp.deptno=dept.deptno
--等价于
select * from emp cross join dept
	where emp.deptno=dept.deptno
<!--三张表连接-->
select * from emp,dept,salgrade 
	where emp.deptno=dept.deptno and
	(emp.sal>salgrade.lostl and emp.sal<salgrade.hisal)
--等价于
select * from emp "E" 
	cross join dept "D" 
	cross join salgrade "S"
	where "E".deptno="D".deptno and
	("E".sal>"S".lostl and "E".sal<"S".hisal)

自然连接

• Natural join基于两个表中的全部同名列建立连接（连接两个table之后，两个table共用的属性就会合并在一起）
• 如果连个table没有共有的属性，则进行笛卡尔乘积，也就是进行两两相乘，如果table 1有3行，table 2有4行，自然连接后就有12行。
  • 从两个表中选出同名列的值均对应相等的所有行
  • 如果两个表中同名列的数据类型不同，则出错
  • 不允许在参照列上使用表名或者别名作为前缀
  • 自然连接的结果不保留重复的属性

举例：

select empno, ename, sal, deptno, dname
from emp natural join dept
where deptno = 10;

Using子句

using等价于join操作中的on

• 使用using必须满足如下两个条件：

  1. 查询必须是等值连接。

  2. 等值连接中的列必须具有相同的名称和数据类型。

• using子句引用的列在sql任何地方不能使用表名或者别名做前缀。

• 可以在using子句中指定多个列名

举例：

select * from my_test_user a , my_test_teacher b where a.userid = b.userid;
=
select * from my_test_user inner join my_test_teacher using(userid);

join连接

• INNER JOIN：返回符合连接条件的记录；
• LEFT [OUTER] JOIN：返回符合连接条件的数据行以及左边表中不符合条件的数据行，此时右边数据行以NULL来显示，称为左连接；
• RIGHT [OUTER] JOIN：返回符合连接条件的数据行以及右边表中不符合条件的数据行，此时左边数据行以NULL来显示，称为右连接；
• FULL [OUTER] JOIN：返回符合连接条件的数据行以及左边表和右边表中不符合条件的数据行，此时缺乏数据的数据行会以NULL来显示

on子句

外连接

LEFT JOIN、RIGHT JOIN与 FULL JOIN统称为外连接。可用来显示不满足连接条件的元组。某些查询要求只能用外连接来表达

嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块；将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询，相当于在SELECT中调用另一段SELECT。

• 子查询不能使用ORDER BY子句；
• 层层嵌套方式反映了SQL语言的结构化；
• 有些嵌套查询可以用连接运算替代
• AS的作用是起别名

子查询

• 特点
  • 子查询在主查询前执行一次
  • 主查询使用子查询的结果
• 使用子查询注意事项
  • 在查询是基于未知值时应考虑使用子查询
  • 子查询必须包含在括号内
  • 建议将子查询放在比较运算符的右侧，以增强可读性。
  • 除非进行Top-N 分析，否则不要在子查询中使用ORDER BY 子句。
  • 如果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比较运算符
  • 如果子查询返回多行结果，则为多行子查询，此时不允许对其使用单行记录比较运算符

单行子查询

• 单行子查询只返回一行记录
• 对单行子查询可使用单行记录比较运算符
• < 、 > 、 = 、 >=、 <= 、 <>

多行子查询

• 多行子查询返回多行记录

• 对多行子查询只能使用多行记录比较运算符

  • ALL 和子查询返回的所有值比较
  • ANY 和子查询返回的任意一个值比较
  • IN 等于列表中的任何一个

带EXISTS谓词的子查询

EXISTS：本质上是一个返回值为“真”/“假”的集函数，用于判断一个集合是否为空。
EXISTS（R），R为非空则返回真。

NOT EXISTS：语义与EXISTS函数相反的逻辑函数。

带EXISTS的子查询的用法：

1.不同形式的查询间的替换:带IN谓词、比较运算符、ANY和ALL谓词的子查询 与 带EXISTS谓词的子查询 等价替换

2.用EXISTS/NOT EXISTS实现全称量词(难点)

3.用EXISTS/NOT EXISTS实现逻辑蕴函(难点)

作用：把带有全称量词的谓词转换为等价的带有存在量词的谓词

数据更新

插入数据

INSERT INTO <表名> [(<属性列1>[,<属性列2>]...)]
VALUES （<常量1>[,<常量2>]....）
| SELECT子查询；

INTO子句 属性列的顺序可与表定义中的顺序不一致，可以不指定或指定部分属性列

VALUES子句 提供的值必须与INTO子句匹配

修改数据

UPDATE<表名> 
SET<列名>=<表达式>[, <列名>=<表达式>]...
[WHERE<条件>]；

SET子句给出<表达式>的值用于取代相应的属性列值。

省略WHERE子句，则表示要修改表中的所有元组。

删除数据

DELETE 
FROM<表名>
[WHERE<条件>]；

视图

• 视图的特点：
  • 是从一个或几个基本表（或视图）导出的表，是虚表
  • 数据库只存放视图的定义，不存放视图对应的数据
  • 基表中的数据发生变化，从视图中查询出的数据也随之改变
• 基于视图的操作：
  • 查询
  • 删除
  • 定义基于该视图的新视图
  • 受限更新

创建视图

创建视图语句格式：

CREATE  VIEW <视图名>  [(<列名>  [，<列名>]...)]
AS<子查询>
[WITH  CHECKOPTION]；

其中，

• 组成视图的属性列名：全部省略或全部指定
• 子查询是否可含有ORDER BY子句和DISTINCT短语，取决于具体系统的实现。
• WITH CHECK OPTION表示对视图进行增删改时要保证删除、插入、更新的行满足视图定义中的谓词条件（即子查询中的条件表达式）。

删除视图

删除视图语句格式：

DROP  VIEW  <视图名> [CASCADE]；

从数据字典中删除指定的视图定义。

• 如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除
• 删除基表时，由该基表导出的所有视图定义都必须显式删除

查询视图

RDBMS采用视图消解法实现视图查询。

更新视图

视图不实际存储数据，因此，对视图的更新最终要转换为对基本表的更新。为防止用户通过视图更新，对不属于视图范围内的基本表数据进行更新操作，在定义视图时加上WITH CHECK OPTION操作。

• 一般RDBMS允许对行列子集视图进行更新
• 对其他类型视图的更新不同系统有不同限制

作用

• 视图能够简化用户的操作
• 视图使用户能以多种角度看待同一数据
• 视图对重构数据库提供了一定程度的逻辑独立性
• 视图能够对机密数据提供安全保护
• 适当的利用视图可以更清晰的表达查询

key、index、primary key和unique key

[参考文章](https://www.cnblogs.com/zjfjava/p/6922494.html

SQL 约束（Constraints）

SQL 约束用于规定表中的数据规则。

如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

SQL CREATE TABLE + CONSTRAINT 语法

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

在 SQL 中，我们有如下约束：

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 保证某列的每行必须有唯一的值。
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK - 保证列中的值符合指定的条件。
• DEFAULT - 规定没有给列赋值时的默认值。