SQL语言基础

女爷i 2023-06-16 10:55 12阅读 0赞

### 前言 ###

最近在复习SQL这门课程，有许多需要记的地方，所以就做一个笔记，方便自己学习和以后的复习。

## 数据定义 ##

SQL的数据定义语句如下

<table style="width:594px;"> 
 <tbody> 
  <tr> 
   <td colspan="1" style="width:83px;">操作对象</td> 
   <td colspan="3" style="width:148px;">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 操作方式</td> 
  </tr> 
  <tr> 
   <td style="width:148px;">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;创建</td> 
   <td style="width:198px;">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 删除</td> 
   <td style="width:162px;">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;修改</td> 
  </tr> 
  <tr> 
   <td style="width:83px;"> <p>&nbsp; 模式</p> </td> 
   <td style="width:148px;">&nbsp;CREATE SCHEMA</td> 
   <td style="width:198px;">&nbsp; &nbsp; &nbsp; &nbsp;DROP&nbsp; SCHEMA</td> 
   <td style="width:162px;">&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:83px;">&nbsp; 表</td> 
   <td style="width:148px;">&nbsp;CREATE&nbsp; TABLE</td> 
   <td style="width:198px;">&nbsp; &nbsp; &nbsp; &nbsp; DROP&nbsp; TABLE</td> 
   <td style="width:162px;">&nbsp; ALTER&nbsp; TABLE</td> 
  </tr> 
  <tr> 
   <td style="width:83px;">&nbsp; 视图</td> 
   <td style="width:148px;">&nbsp;CREATE VIEW</td> 
   <td style="width:198px;">&nbsp; &nbsp; &nbsp; &nbsp; DROP&nbsp; VIEW</td> 
   <td style="width:162px;">&nbsp;&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:83px;">&nbsp; 索引</td> 
   <td style="width:148px;">&nbsp;CREATE INDEX</td> 
   <td style="width:198px;">&nbsp; &nbsp; &nbsp; &nbsp; DROP INDEX</td> 
   <td style="width:162px;">&nbsp;ALTER&nbsp; INDEX</td> 
  </tr> 
 </tbody> 
</table>

### 模式的定义与删除 ###

1. 定义模式：

CREATE  SCHEMA <模式名>   AUTHORIZATION <用户名>: (如果没有模式名就默认为用户名)

例如：为用户wang 定义一个学生-课程模式S-T

CREATE SCHEMA AUTHORIZATION WANG;

2. 删除模式：

DROP SCHEMA <模式名> <CASCADE | RESTRICT>;

选择CASCADE表示删除模式的同时把该模式中所有的数据库对象全部删除。 选择RESTRICT表示如果该模式已经定义了下属的数据库对象，那么就不会删除，二者必选其一。

例如：删除模式wang

DROP SCHEMA WANG CASCADE;

### 基本表的定义、删除与修改 ###

1. 定义基本表：

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

例如：建立一个学生表 Student：

CREATE TABLE Student
      (Sno CHAR(9) PRIMARY KEY,  /*Sno是主码*/
       Sname CHAR(9) UNIQUE,  /*Sname取唯一值*/
       Ssex CHAR(2),
       Sdept CHAR(20)
       FOREIGN KEY (Ssex) REFERENCES Coures(Sno)
       /*表级完整性，Ssex是外码，被参照表是Course， 被参照列是Sno*/
      );

2. 数据类型：

下图中列举比较常见的几种数据类型：

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td style="width:207px;">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;数据类型</td> 
   <td style="width:292px;">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 含义</td> 
  </tr> 
  <tr> 
   <td style="width:207px;">CHAR(n), CHARACTER(n)</td> 
   <td style="width:292px;">长度为n的定长字符串</td> 
  </tr> 
  <tr> 
   <td style="width:207px;">VARCHAR(n)</td> 
   <td style="width:292px;">最大长度为n的变长字符串</td> 
  </tr> 
  <tr> 
   <td style="width:207px;">CLOB</td> 
   <td style="width:292px;">字符串最大对象</td> 
  </tr> 
  <tr> 
   <td style="width:207px;"> <p>BLOB</p> </td> 
   <td style="width:292px;">二进制最大对象</td> 
  </tr> 
  <tr> 
   <td style="width:207px;">INT, INTEGER</td> 
   <td style="width:292px;">长整数（4字节）</td> 
  </tr> 
  <tr> 
   <td style="width:207px;">SMALLINT</td> 
   <td style="width:292px;">短整数（2字节）</td> 
  </tr> 
  <tr> 
   <td style="width:207px;">BIGINT</td> 
   <td style="width:292px;">大整数（8字节）</td> 
  </tr> 
  <tr> 
   <td style="width:207px;">NUMERIC(p, d)</td> 
   <td style="width:292px;">定点数，p位数字，小数点后有d位数字</td> 
  </tr> 
  <tr> 
   <td style="width:207px;">REAL</td> 
   <td style="width:292px;">单精度浮点数</td> 
  </tr> 
  <tr> 
   <td style="width:207px;">FLOAT(n)</td> 
   <td style="width:292px;">双精度浮点数</td> 
  </tr> 
  <tr> 
   <td style="width:207px;">BOOLEAN</td> 
   <td style="width:292px;">布尔类型</td> 
  </tr> 
  <tr> 
   <td style="width:207px;">DATE</td> 
   <td style="width:292px;">日期，Y-M-D</td> 
  </tr> 
  <tr> 
   <td style="width:207px;">TIME</td> 
   <td style="width:292px;">时间，H:M:S</td> 
  </tr> 
 </tbody> 
</table>

3. 修改基本表：

一般格式为：

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

<表名>是要修改的基本表， ADD子句用于增加新列， DROP子句用于删除列， ALTER用于修改原有的列定义

例如：向Student表增加“入学时间”列， 其数据类型为日期型

ALTER TABLE Student ADD S_entrance DATE;
    /*新增加的列一律为空值*/

例如：将年龄的数据类型由字符型改为整数。

ALTER TABLE Student ALTER COLUMN Sage INT;

4. 删除基本表：

一般格式为：

DROP TABLE <表名> [RESTRICT | CASCADE]; /*有限制的删和全部删,默认有限制*/

例如：删除Student表

DROP TABLE Student CASCADE;

### 索引的建立与删除 ###

1. 建立索引：

CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
    ON <表名> (<列名> [<次序>] [,<列名> [<次序>]] …… );

索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。每个<列名> 后面可以用<次序> 指定索引值的排列次序，其中

ASC（升序）， DESC（降序），默认为升序。

例如：建立一个sc表按学号升序和课程号为降序建立唯一索引SCno。

CREATE UNIQUE INDEX SCno ON SC (Sno ASC, Cno DESC);

2. 修改索引：

ALTER INDEX <旧索引名> RENAME TO <新索引名>;

3. 删除索引：

DROP INDEX <索引名>;

## 数据查询 ##

基本格式为：

SELECT [ALL | DISTINCT] <目标列表达式> [, <目标列表达式>] ……
    FROM <表名或视图名> [, <表名或视图名>……] | (<SELECT语句>)[AS]<别名>
    [WHERE <条件表达式>]
    [GROUP BY <列名1> [HAVING <条件表达式> ]]
    [ORDER BY <列名2> [ASC | DESC]];

### 单表查询------------------------- ###

1. 查询指定列：例如查询全体雪上的学号与姓名

SELECT Sname, Sno
    FROM Student;

2. 查询全部列：

SELECT *
    FROM Student;

也可以将所选取的全部列出来。

3. 查询经过计算的值：

例如：查询全体学生的姓名及其出生年份。

SELECT Sname, 2014 - Sage
    FROM Student;

第二列是一个计算表达式，用当时的年份减去学生的年龄就可以 得到学生的出生年份。

用户同样可以通过指定别名来改变查询结果的列标题：

SELECT Sname NAME, 'Year of Birth:' BIRTH, 2014 - Sage BIRTHDAY,
    LOWER(Sdept) DEPARTMENT
    FROM Student;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz_size_16_color_FFFFFF_t_70][]

### 选择表中的若干元祖 ###

1. 消除取值重复的行

例如查询选修课程的学生学号去除重复的

SELECT DISTINCT Sno
    FROM SC;

如果没有指定关键词则默认为ALL。

2. 查询满足条件的元祖：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz_size_16_color_FFFFFF_t_70 1][]1）比较大小：

例如查询所有年龄在20岁以下的学生姓名及其名单

SELECT Sname, Sage
    FROM Student
    WHERE Sage < 20;

2)确定范围：

查询年龄在20 - 30岁之间的学生姓名

SELECT Sname
    FROM Student 
    WHERE Sage BETWEEN 20 AND 30; /*NOT BETWEEN    AND     */

3）确定集合：

查询计科系（CS），信息系（IS）学生的姓名

SELECT Sname
    FROM Student
    WHERE Sdept IN ('CS', 'IS'); /* NOT IN */

4) 字符匹配：

一般格式如下

NOT LIKE '<匹配串>' [ ESCAPE '<换码字符>']

匹配串可以是完整的字符也可以含有通配符%和\_。

% 代表任意长度（0）的字符串，例如a%b表示以a开头，以b结尾的任意长度的字符串，如acb，ab等

\_ 代表任意单个字符，如a\_b表示以a开头，以b结尾的长度为3的任意字符，如acb，agb等。

例如：查询所有姓刘的学生的全部信息

SELECT *
    FROM Student
    WHERE Sname LIKE '刘%';

查询名字中第二个字为‘阳’的学生的姓名和学号

SELECT Sname, Sno
    FROM Student
    WHERE Sname LIKE '_阳_';

查询以‘DB\_’开头，且倒数第三个字符为i的课程的详细信息

SELECT *
    FROM Student
    WHERE Cname LIKE 'DB\_%i__' ESCAPE '\'; /*用ESCAPE ‘换码字符’ 对通配字符来转义*/

5）空值查询

例如查询所有有成绩的学生的学号和课程号

SELECT Sno, Cno
    FROM Student
    WHERE Grade IS NOT NULL;

6) 多重条件查询

例如查询计科系年龄在20岁以下的学生姓名

SELECT Sname
    FROM Student
    WHERE Sdept = 'CS' AND Sage < 20;

IN运算符就相当于多个OR运算符的缩写

例如查询计科系和信息系学生的全部信息也可以写成

SELECT *
    FROM Student
    WHERE Sdept = 'CS' OR Sdept = 'IS';

### ORDER BY子句 ###

用户可以对查询结果按照一个或者多个属性列的升序（默认）或者降序排列。

例如查询全体学生情况，查询结果按照所在系的系号升序排列，同一系中的学生按照年龄降序排列。

SELECT *
    FROM Student
    OPDER BY Sdept, Sage DESC;

### 聚集函数 ###

SQL有许多方便的聚集函数

COUNT(*)                                 /*统计元祖个数*/
    COUNT([ DISTRINCT | ALL] <列名>)         /*统计一列中值的个数*/
    SUM([ DISTRINCT | ALL] <列名>)           /*计算一列值的总和*/
    MAX([ DISTRINCT | ALL] <列名>)           /*计算一列值的最大值*/
    MIN([ DISTRINCT | ALL] <列名>)           /*计算一列值的最小值*/
    AVG([ DISTRINCT | ALL] <列名>)           /*计算一列值的平均值*/

例如：

SELECT COUNT(*)
    FROM Student;                /*查询学生总人数*/
    
    SELECT COUNT(DISTINCT Sno)
    FROM SC;                     /*查询了选修课程的学生人数*/
    
    SELECT AVG(Grade)
    FROM SC
    WHERE Cno = '1';             /*计算选修1号课程的学生平均成绩*/

当聚集函数遇到空值时，除COUNT（）函数外都跳过空值而处理非空值。

### GROUP BY子句 ###

将查询结果按某一列或多列的值分组，值相等的为一组，分组后聚集函数将作用于每一个组。

例如求各个课程号及相应的选课人数

SELECT Cno, COUNT(Sno)
    FROM SC
    GROUP BY Cno;

查询结果可能为：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz_size_16_color_FFFFFF_t_70 2][]

查询选修了三门以上课程的学生学号

SELECT Sno
    FROM SC
    GROUP BY Sno
    HAVING COUNT(*) > 3;

先按Sno进行分组，然后再用聚集函数COUNT对每一组计数，HAVING短语给出了选择组的条件。

查询平均成绩大于等于90分的学生学号和平均成绩

SELECT Sno, AVG(Grade)
    FROM SC
    GROUP BY Sno
    HAVING AVG(Grade) >= 90;

### 连接查询-------------------------------- ###

### 等值与非等值连接查询 ###

[<表名1>.] <列名1> <比较运算符> [<表名2>.] <列名2>

连接谓词可以用下面的形式

[<表名1>.] <列名> BETWEEN [<表名2>.] <列名2> AND [<表名3>.] <列名3>

当连接运算符为=时称为等值连接，使用其他运算符称为非等值连接。

例：查询每个学生及其选修课程的情况

学生情况存放在Student表中，选修课程情况存放在SC表中，查询涉及的两个表之间的联系是通过公共属性Sno实现的。

SELECT Student. *SC.*
    FROM Student, SC
    WHERE Student.Sno = SC.Sno        /*将两个表中同一学生的元祖连接起来*/

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz_size_16_color_FFFFFF_t_70 3][]

其过程为：首先在表Studnet中找到第一个元祖，然后从头开始扫描SC表，挨个查找与Student表第一个元祖sno相等的SC元祖

找到后将其连接起来，形成结果表中一个元祖。SC全部查找完后再找Student中的第二个元祖，重复进行上述操作，直到Student

表中数据处理完毕。这就是**嵌套循环**算法的基本思想。

结果如图所示

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz_size_16_color_FFFFFF_t_70 4][]

如果在等值连接中把目标列中重复的属性列去掉则为自然连接。

例如：查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。

SELECT Student.Sno, Sname
    FROM Student, SC
    WHERE Student.Sno = SC.Sno AND SC.Cno = '2' AND SC.Grade > 90;
    /*Sno在两个表中都出现了引用时必须加上表名前缀，从SC表中挑出符合条件的元祖然后再和Student连接*/

### 自身连接 ###

例：查询每一门课的间接先修课(即先修课的先修课)

每门课只有其先修课的信息但是没有其间接先修课的信息，所以我们需要先找到每门课的先修课信息，然后再按先修课的课程号去查找它的先修课程，这就需要将表与其自身连接。

当前表为Course，为其取两个别名，First和Second，如图、

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz_size_16_color_FFFFFF_t_70 5][]

完成查询的语句为

SELECT FIRST.Cno, SECOND.Cpno
    FROM Course FIRST, Course SECOND
    WHERE FIRST.Cpno = SECOND.Cno;

其实也就是相当于通过First.cpno去找Second.cpno。

多表连接的话每次先进行两个表的连接，然后再将连接表与下一个继续连接。

### 嵌套查询--------------------------- ###

在SQL语句中，一个SELECT - FROM - WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块中的WHERE或者

HAVING语句的条件查询中时称为嵌套查询。例如

SELECT Sname                      /*外层查询或父查询*/
    FROM Student
    WHERE Sno IN
            (SELECT Sno              /*内层查询或子查询*/
             FROM SC
             WHERE Cno = '2');

### 带有IN谓词的子查询 ###

例1：查询与“刘晨”在同一个系学习的学生

1.首先我们先找到刘晨所在的系：

SELECT Sdept
    FROM Student
    WHERE Sname = '刘晨';

找到结果为CS。

2.找到所有在CS系学习的学生：

SELECT *
    FROM Student
    WHERE Sdept = 'CS';

3.将第一步查询嵌套到第二步查询里：

SELECT *
    FROM Student
    WHERE Sdept IN
            (SELECT Sdept
             FROM Student
             WHERE Sname = '刘晨');

本例中子查询的查询条件不依赖于父查询，称为不相关子查询。

例2：查询选修了课程名为“信息系统”的学生学号与姓名。

SELECT Sno, Sname                       3.最后在Student表中取出内容
    FROM Student
    WHERE Sno IN
            (SELECT Sno                     2.然后再SC关系中找出选修了3号课程的学生学号
             FROM SC
             WHERE Cno IN
                    (SELECT Cno             1.首先在Course关系中找出课程号
                     FROM Course
                     WHERE Cname = '信息系统'
                    )
            );

如果子查询的查询条件依赖于父查询，这类子查询就称为相关子查询，整个子查询语句称为相关嵌套查询。

### 带有比较运算符的查询 ###

父查询与子查询之间可以用比较运算符来进行连接，当内层查询返回的是单个值时可以用>, <, = 等比较运算符。

例：找出每个学生超过自己选修课程平均成绩的课程号。

SELECT Sno, Cno
    FROM SC X
    WHERE Grade >= (SELECT AVG(Grade)
                    FROM SC, y
                    WHERE y.Sno = x.Sno);

x是表SC的别名，用来表示SC的一个元组，内层查询是查找一个学生所有选修课程的平均成绩。

### 带有ANY(SOME) 或 ALL谓词的子查询 ###

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191128105150663.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191128105610938.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191128131010986.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191211141041756.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20191211142210742.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20191212161954595.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNzg1ODYz,size_16,color_FFFFFF,t_70