SQL的常用查询操作及例题

妖狐艹你老母 2023-07-17 11:30 23阅读 0赞

*SQL分支及常用命令脑图：[link][].*

## 0. 查询时用到的表格 ##

**表结构如下**

*  学生课：Student(**Sno**,Sname,Ssex,Sage,Sdept)
 *  课程表：Student(**Cno**,Cname,Cpno,Ccredit)
 *  学生选课表：SC(**Sno,Cno**,Ssex,Grade)

**注**：加粗字体为**主关键字**。

**(a) Student**

<table> 
 <tbody> 
  <tr> 
   <td> 学号(Sno) </td> 
   <td> 姓名（Sname） </td> 
   <td> 性别（Ssex） </td> 
   <td> 年龄（Sage） </td> 
   <td> 所在系（Sdept） </td> 
  </tr> 
  <tr> 
   <td> 20125121 </td> 
   <td> 李勇 </td> 
   <td> 男 </td> 
   <td> 20 </td> 
   <td> CS </td> 
  </tr> 
  <tr> 
   <td> 201215122 </td> 
   <td> 刘晨 </td> 
   <td> 女 </td> 
   <td> 19 </td> 
   <td> CS </td> 
  </tr> 
  <tr> 
   <td> 201215122 </td> 
   <td> 王敏 </td> 
   <td> 女 </td> 
   <td> 18 </td> 
   <td> MA </td> 
  </tr> 
  <tr> 
   <td> 201215125 </td> 
   <td> 张立 </td> 
   <td> 男 </td> 
   <td> 19 </td> 
   <td> IS </td> 
  </tr> 
 </tbody> 
</table>

**(b) Course**

<table> 
 <tbody> 
  <tr> 
   <td> 课程号(Cno) </td> 
   <td> 课程名（Cname） </td> 
   <td> 先行课（Cpno） </td> 
   <td> 学分（Ccredit） </td> 
  </tr> 
  <tr> 
   <td> 1 </td> 
   <td> 数据库 </td> 
   <td> 5 </td> 
   <td> 4 </td> 
  </tr> 
  <tr> 
   <td> 2 </td> 
   <td> 数学 </td> 
   <td> null </td> 
   <td> 2 </td> 
  </tr> 
  <tr> 
   <td> 3 </td> 
   <td> 信息系统 </td> 
   <td> 1 </td> 
   <td> 4 </td> 
  </tr> 
  <tr> 
   <td> 4 </td> 
   <td> 操作系统 </td> 
   <td> 6 </td> 
   <td> 3 </td> 
  </tr> 
  <tr> 
   <td> 5 </td> 
   <td> 数据结构 </td> 
   <td> 7 </td> 
   <td> 4 </td> 
  </tr> 
  <tr> 
   <td> 6 </td> 
   <td> 数据处理 </td> 
   <td> null </td> 
   <td> 2 </td> 
  </tr> 
  <tr> 
   <td> 7 </td> 
   <td> PASCAL语言 </td> 
   <td> 6 </td> 
   <td> 4 </td> 
  </tr> 
 </tbody> 
</table>

**（c）SC**

<table> 
 <tbody> 
  <tr> 
   <td> 学号（Sno) </td> 
   <td> 课程号（Cno） </td> 
   <td> 成绩（Crade） </td> 
  </tr> 
  <tr> 
   <td> 201215121 </td> 
   <td> 1 </td> 
   <td> 92 </td> 
  </tr> 
  <tr> 
   <td> 201215121 </td> 
   <td> 2 </td> 
   <td> 85 </td> 
  </tr> 
  <tr> 
   <td> 201215121 </td> 
   <td> 3 </td> 
   <td> 88 </td> 
  </tr> 
  <tr> 
   <td> 201215122 </td> 
   <td> 2 </td> 
   <td> 90 </td> 
  </tr> 
  <tr> 
   <td> 201215122 </td> 
   <td> 3 </td> 
   <td> 80 </td> 
  </tr> 
 </tbody> 
</table>

另外，我们要了解这四个词的含义：**模式，基本表，视图，索引。**

## 一．数据定义 ##

### 1. 模式定义与删除 ###

**SQL的数据定义语句**：

<table> 
 <tbody> 
  <tr> 
   <td> 操作对象 </td> 
   <td> 创建 </td> 
   <td> 删除 </td> 
   <td> 修改 </td> 
  </tr> 
  <tr> 
   <td> 模式 </td> 
   <td> CREATE SCHEMA </td> 
   <td> DROP SCHEMA </td> 
   <td> 无 </td> 
  </tr> 
  <tr> 
   <td> 表 </td> 
   <td> CREATE TABLE </td> 
   <td> DROP TABLE </td> 
   <td> ALTER TABLE </td> 
  </tr> 
  <tr> 
   <td> 视图 </td> 
   <td> CREATE VIEW </td> 
   <td> DROP VIEW </td> 
   <td> 无 </td> 
  </tr> 
  <tr> 
   <td> 索引 </td> 
   <td> CREATE INDEX </td> 
   <td> DROP INDEX </td> 
   <td> ALTER INDEX </td> 
  </tr> 
 </tbody> 
</table>

##### 1.1 定义模式 #####

在SQL中，模式定义语句如下：

> CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;

【例1.1】为用户WANG定义一个学生-课程模式S-T：  
**CREATE SCHEMA ”S-T” AUTHORIZATION WANG;**

###  ###

##### 1.2 模式删除 #####

在SQL中，模式删除语句如下：

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

【例1.2】删除模式ZHANG和其中定义的表TAB1；  
**DROP SCHEMA ZHANG CASCADE;**

### 2. 基本表的定义，删除与修改 ###

##### 2.1 定义基本表 #####

在SQL中，定义基本表语句如下：

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

【例 2.1】建立学生选课表SC， 表©。  
**CREATE TABLE SC**  
　**(Sno CHAR(9),** 　　　　　 //定义Sno，数据类型为CHAR，字节不多于9  
　**Cno CHAR(4),**  
　**Grade SMALLINT,** 　　　　//定义Grade，数据类型为SMALLINT  
　**PRIMARY KEY(Sno, Cno),** //主码由两个属性构成，必须作为表级完整性进行定义  
　**FOREIGE KEY (Sno) REFERENCES Student(Sno),**  
//表级完整性定义，Sno为外码，被参照表为Student  
　**FOREIGE KEY (Cno) REFERENCES Course(Cno),**  
//表级完整性定义，Cno为外码，被参照表为Course  
);

##### 2.2 修改基本表 &2.3 删除基本表 &2.4索引的建立与删除 #####

略

### 3．数据查询 ###

**其一般格式为：**

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

**含义为：**

*  根据WHERE子句的表达式从FROM子句指定的表中找出满足条件的元组，再按照SELECT子句中的目标表达式选出元组中的属性值形成结果表。
 *  如果有GROUP BY表达式，则将结果按照<列名1>分组。HAVING 为输出指定条件的组。
 *  如果有ORDER BY表达式，则将结果按照 <列名2>升序或者降序。

##### 3.1 单表查询：仅涉及一个表的查询 #####

**WHERE常用的查询条件**

<table> 
 <tbody> 
  <tr> 
   <td> 查询条件 </td> 
   <td> 谓词 </td> 
  </tr> 
  <tr> 
   <td> 比较 </td> 
   <td> =,&gt;,&lt;,&gt;=,&lt;=,!=,&lt;&gt;,!&gt;,!&lt;;NOT加上述比较符号 </td> 
  </tr> 
  <tr> 
   <td> 确定范围 </td> 
   <td> BETWEEN AND, NOT BETWEEN AND </td> 
  </tr> 
  <tr> 
   <td> 确定集合 </td> 
   <td> IN, NOT IN </td> 
  </tr> 
  <tr> 
   <td> 字符匹配 </td> 
   <td> LIKE,NOT LIKE（%任意长度；_单个字符） </td> 
  </tr> 
  <tr> 
   <td> 空值 </td> 
   <td> IS NULL, IS NOT NULL </td> 
  </tr> 
  <tr> 
   <td> 多重条件（逻辑运算） </td> 
   <td> AND, OR, NOT </td> 
  </tr> 
 </tbody> 
</table>

\[例 3.1\] 查询年龄在20岁以下的学生姓名和年龄  
**SELECT Sname, Sage  
　FROM Student  
　WHERE Sage<20;**

##### 3.2　连接查询：涉及两个表以上的查询 #####

\[例3.2.1：等值与非等值连接查询\] 查询每个学生及其选修课程的情况  
**SELECT Student,SC.** 　　　　　　　 / /若把列中重复的属性列去掉改为自然连接：则为\*\* 　**Student.Sno,Sname,Ssex,Sage,Sdpet,Cno,Grade** 　  
　**FROM Student, SC**　　　　　　　　//容易混淆时，属性名前应该加前缀  
　**WHERE Student.Sno=SC.Sno;**　　//将Student与SC中同一学生的元组连接起来

\[例3.2.2：等值与非等值连接查询\] 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名  
**SELECT Student.Sno,Sname  
　FROM Student,SC  
　WHERT Student.Sno=SC.Sno AND SC.Cno=’2’AND SC.Grade>90;**

\[例3.2.3：自身连接\] 查询每一门课的间接先修课（即先修课的先修课）  
**SELECT FIRST.Cno,SECOND Cpno** //注意要编号  
　**FROM Course FIRST.Course SECOND  
　WHERE FIRST.Cpno=SECOND.Cno;**

\[例3.2.4：外连接：将不满足条件的元组作为结果输出\] 改进例3.2.1  
**SELECT Student.Sno,Sname,Ssex,Sage,Sdpet,Cno,Grade  
　FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);**

\[例3.2.5：多表连接\] 查询每个学生的学号，姓名，选修课的课程名及成绩  
**SELECT Student,Sno,Sname,Cname,Grade  
　FROM Student,SC,Course  
　WHERE Student.Sno=SC.SnoAND SC.Cno=Course.Cno;**

##### 3.3　嵌套查询： #####

\[例3.3.1：带有IN谓词的子查询\]查询“刘晨”在同一个系学习的学生  
首先确定“刘晨”所在的系名  
**SELECT Sdept  
　FROM Student  
　WHERE Sname=’刘晨’；**  
再确定CS系的学生  
**SELECT Sno,Student,Sdept  
　FROM Student  
　WHERE Sdept=’CS’;**  
最后第一步查询嵌入到第二步中  
**SELECT Sno,Sname,Sdept  
　FROM Student  
　WEHRE Sdept IN  
　(SELECT Sdept  
　FROM Student  
　WHERE Sname=’刘晨’  
)；**  
//子查询不依赖父查询，成为不相关子查询

\[例3.2.2：带有比较运算符的子查询\]　是相关子查询

\[例3.2.3：带有ANY(SOME)或ALL谓词的子查询\] 略

\[例3.2.4：带有EXISTS谓词的子查询\]　带有EXISTS谓词的子查询不返回数据，只返回真（内层不为空）和假（内层为空）。

\[例5：基于派生表查询 \]略

##### 3.4 数据更新 #####

\[例3.4.1：插入数据INSERT\]插入一条选课记录（‘201215128’,’1’）  
**INSERT  
　INTO SC(Sno,Cno)  
　 VALUES(‘201215128’,’1’)**

\[例3.4.2：修改数据UPDATE\]将学生201215121的年龄改为22岁

**UPDATE Student  
　 SET Sage=22  
　 WHERE Sno=’201215121’;**  
　  
\[例3.4.3：删除数据DELETE\]删除学号为201215128的学生记录  
**DELETE  
　FROM Student  
　WHERE Sno=’201215128’;**

### 4．视图 ###

略

[link]: https://blog.csdn.net/weixin_43518038/article/details/104996604