SQL 常用语句

目录

操作表

创建表

删除基本表

修改基本表

查找表

操作数据

查询数据

1.目标列表达式的可选格式

2.聚集函数的一般格式

3.WHERE 子句的条件表达式的可选格式

4.ORDER BY 子句

5.GROUP BY 子句

6.嵌套查询

7. 集合查询

8. 基于派生表的查询

参考文章

---

操作表

创建表

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

格式：create table 语句

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

  <表名>：所要定义的基本表的名字
  <列名>：组成该表的各个属性（列）
  <列级完整性约束条件>：涉及相应属性列的完整性约束条件
  <表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

[例]  建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。

CREATE TABLE Student
        (Sno    CHAR(5)  NOT NULL  UNIQUE，
         Sname  CHAR(20)  UNIQUE，         
         Ssex   CHAR(1) ，
         Sage   INT，
         Sdept  CHAR(15))；

• 常用完整性约束

  主码约束：  PRIMARY  KEY
  唯一性约束：UNIQUE
  非空值约束：NOT NULL
  参照完整性约束  

• PRIMARY KEY与 UNIQUE的区别？

[例]  建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno, Cno)为主码。

CREATE TABLE SC(
            Sno CHAR(5) ,
            Cno CHAR(3) ,
            Grade   int,
            Primary key (Sno, Cno));

删除基本表

DROP TABLE <表名>  [restrict|cascade];

(restrict 表的删除是有限制条件的，如果存在这些依赖该表的对象，则此表不能被删除。Casecade 改表的删除没有限制条件，删除表的同时，相关依赖都被删除)

 [例]

drop table Student cascade;

修改基本表

ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束 ] ]
[ DROP <完整性约束名> ]
[ MODIFY <列名> <数据类型> ]；

  <表名>：要修改的基本表
  ADD子句：增加新列和新的完整性约束条件
  DROP子句：删除指定的完整性约束条件
  MODIFY子句：用于修改列名和数据类型 

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

ALTER TABLE Student ADD Scome DATE；

不论基本表中原来是否已有数据，新增加的列一律为空值。

查找表

语句格式

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

FROM 子句：指定查询对象(基本表或视图)
WHERE 子句：指定查询条件
GROUP BY 子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。
HAVING 短语：筛选出只有满足指定条件的组
ORDER BY 子句：对查询结果表按指定列值的升序或降序排序

操作数据

查询数据

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

1.目标列表达式的可选格式

1.  *
2. <表名.>*
3. COUNT（[DISTINCT|ALL] *） 
4. [<表名.>]<属性列名表达式>[,[<表名.>]<属性列名表达式>]...  

Tips :<属性列名表达式> 可以是由属性列、作用于属性列的聚集函数、属性和常量的任意算术运算（+、-、*、/）组成的运算公式。

• 表达式

SELECT Sname,2016-Sage FROM Student;

• 字符串常量、函数

SELECT Sname,'Year of Birth:',2016-Sage,LOWER(Sdept) FROM Student; // 小写字母表示系名

+--------+-----------+
| Sname  | 2016-Sage |
+--------+-----------+
| Jason  |      1995 |
| Taylor |      1995 |
| dsf    |      1995 |
+--------+-----------+

+--------+----------------+-----------+--------------+
| Sname  | Year of Birth: | 2016-Sage | LOWER(Sdept) |
+--------+----------------+-----------+--------------+
| Jason  | Year of Birth: |      1995 | ele          |
| Taylor | Year of Birth: |      1995 | ele          |
| dsf    | Year of Birth: |      1995 | ele          |
+--------+----------------+-----------+--------------+

• 查询时可以指定别名，这对于算术表达式、常量、函数名的目标列表达尤为有用

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

+--------+----------------+----------+------------+
| NAME   | BIRTH          | BIRTHDAY | DEPARTMANT |  //指定别名，界面友好性。
+--------+----------------+----------+------------+
| Jason  | Year of Birth: |     1995 | ele        |
| Taylor | Year of Birth: |     1995 | ele        |
| dsf    | Year of Birth: |     1995 | ele        |
+--------+----------------+----------+------------+

2.聚集函数的一般格式

 COUNT（*）                        统计元组的个数
 COUNT（[DISTINCT|ALL] <列名>）    统计一列中值的个数
 SUM（[DISTINCT|ALL] <列名>）      计算一列值的总和（此列必须是数值型）
 AVG（[DISTINCT|ALL] <列名>）      计算一列值的平均值（此列必须是数值型）
 MAX（[DISTINCT|ALL] <列名>）      求一列值中的最大值
 MIN（[DISTINCT|ALL] <列名>）      求一列值中的最小值 

DISTINC 短语，指明在计算时要取消指定列中的重复值。默认为ALL，表示不取消重复值。 当聚集函数遇到空值时，除COUNT()外，都跳过空值而只处理非空值。 COUNT（）对元组进行计数时，包含空值所在的元组。

注意： WHERE 子句中是不能用聚集函数作为条件表达式的。 聚集函数只能用于 SELECT子句 和 GROUP BY 中的 HAVING 子句。

SELECT COUNT(*) FROM Student;

+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

SELECT COUNT(DISTINCT Sno) FROM SC;  // 查询选修了课程的学生人数

+---------------------+
| COUNT(DISTINCT Sno) |
+---------------------+
|                   5 |
+---------------------+

学生每选一门课在SC表中都有一条相应的记录. 一个学生选多门课，SC表会记录多次，COUNT函数必须用DISTINCT短语去掉重复的记录

3.WHERE 子句的条件表达式的可选格式

• 比较大小

常见运算符： =（等于），>（大于），<(小于)，>=,<=, !=或<>,!>(不大于)，!<（不小于） 

• 确定范围

[NOT] BETWEEN 下限 AND 上限 

[例] 查询年龄在20~23岁之间的学生的姓名、系名和年龄

SELECT Sname,Sdept,Sage FROM WHERE Sage BETWEEN 20 AND 24;

• 确定集合

IN 用来查找属性值属于指定集合的元组
NOT IN  属性不属于任何指定集合的元组

[例] 查找 CS系，ELE系，IS系 中的学生的姓名、性别

SELECT Sname,Ssex FROM Student WHERE Sdept IN ('CS','ELE','IS');

• 字符匹配

LIKE 用来进行字符串的匹配。 查找指定的属性列值与<匹配字符串>相匹配的元组

语法格式：

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

% 代表任意长度（>=0个）的字符串
_ 下划线代表任意单个字符

[例] a_b 表示以a开头，以b结尾的长度为3的任意字符串

[例] 查找学号为3120开头的学生的信息

SELECT * FROM Student WHERE Sno LIKE '3120%'; 

+--------+--------+------+------+-------+
| Sno    | Sname  | Ssex | Sage | Sdept |
+--------+--------+------+------+-------+
| 312001 | Jason  | fe   |   21 | ELE   |
| 312028 | Taylor | fe   |   21 | ELE   |
+--------+--------+------+------+-------+

如果用户要查询的字符串本身就含有通配符 % 或 _ ，这时就要用 ESCAPE '<换码字符>' 短语对通配符进行转义了

[例] 以 & 作为转义字符。mysql不支持''

select Cno,Cname,Ccredit from Course where Cname LIKE 'DB&_Design' ESCAPE '&';

+-----+-----------+---------+
| Cno | Cname     | Ccredit |
+-----+-----------+---------+
| 004 | DB_Design |       6 |
+-----+-----------+---------+

• 涉及空值的查询

IS NULL // 为空  
IS NOT NULL  // 不为空

[例]

select Sno,Cno from Sc where Grade is not null;

+--------+-----+
| Sno    | Cno |
+--------+-----+
| 20123  | 004 |
| 20132  | 001 |
| 312001 | 001 |
| 312028 | 003 |
| 312029 | 006 |
+--------+-----+

• 多重条件查询

AND 和 OR 用来连接多个查询条件. AND 的优先级高于 OR，用括号可以改变优先级

SELECT Sname FROM Student WHERE Sdept='ELE' AND Sage<22;

+--------+
| Sname  |
+--------+
| Jason  |
| Taylor |
| dsf    |
+--------+

4.ORDER BY 子句

对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，默认升序.

SELECT * FROM Student ORDER BY Sage DESC; // DESC降序

+--------+--------+------+------+-------+
| Sno    | Sname  | Ssex | Sage | Sdept |
+--------+--------+------+------+-------+
| 31223  | dsf    | ma   |   23 | IS    |
| 312028 | Taylor | fe   |   21 | ELE   |
| 312001 | Jason  | fe   |   19 | CS    |
+--------+--------+------+------+-------+

5.GROUP BY 子句

将查询结果按某一列或多列的值分组，属性值相等的为一组。 对查询结果进行分组是为了细化聚集函数的作用对象，分组后，聚集函数将作用于每一个分组,即每一组都有一个函数值。

SELECT Cno,COUNT(Sno) FROM Sc GROUP BY Cno; 
// 按照属性 Cno 进行分组，再对每一个分组计算 Sno的个数

+-----+------------+
| Cno | COUNT(Sno) |
+-----+------------+
| 001 |          2 |
| 003 |          1 |
| 004 |          1 |
| 006 |          1 |
+-----+------------+

如果分组后还要求按一定的条件对这些分组进行筛选，最终只输出满足条件的组，则使用 HAVING 短语指定筛选条件。

SELECT * From Sc;

+--------+------+-------+ 
| Sno    | Cno  | Grade |
+--------+------+-------+
| 20123  | 004  |    96 |
| 20132  | 001  |    88 |
| 312001 | 001  |    98 |
| 312028 | 003  |    99 |
| 312028 | 004  |   100 |
| 312028 | 006  |   100 |
| 312028 | 009  |   100 |
| 312028 | 010] |   100 |
| 312029 | 006  |   100 |
+--------+------+-------+

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

先用 GROUP BY 子句按 Sno 进行分组，再用聚集函数对每一个分组计数，HAVING短语给出选择组的条件，满足条件(元组个数>3)的组才会被选出来。

+--------+
| Sno    |
+--------+
| 312028 |
+--------+

注意： WHERE 子句与 HAVING 短语的区别在于 作用对象不同，WHERE 子句作用于 基本表或者视图，从中选择满足条件的元组。HAVING 短语作用于分组，从中选出满足条件的组。

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

SELECT Sno,AVG(Grade) FROM Sc GROUP BY Sno HAVING AVG(Grade)>=90;
// 先按学号进行分组，得出每一个学生课程组，再计算平均值，根据 HAVING 条件，输出平均分>=90分的分组.

+--------+------------+
| Sno    | AVG(Grade) |
+--------+------------+
| 20123  |    96.0000 |
| 312001 |    98.0000 |
| 312028 |    99.8000 |
| 312029 |   100.0000 |
+--------+------------+

6.嵌套查询

在 SQL 语言中， 一个 SELECT-FROM-WHERE 语句成为一个查询块。将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING短语的条件中 的查询称为嵌套查询（nested query）。

select Sname from Student where Sno IN   // 外层查询或父查询
            (select Sno from Sc where Cno='006');        // 内层查询或子查询

SQL 允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。

注意： 子查询的 SELECT 语句中不能使用 ORDER BY 子句， ORDER BY 子句只能对最终查询结果排序。

嵌套查询使用户可以用多个简单的查询构成复杂的查询，从而增强SQL的查询能力。 以层层嵌套的方式来构造程序正是 SQL 中 “结构化” 的含义所在。

• 带有 IN 谓词的子查询

在嵌套查询中，子查询的结果往往是元组的集合，所以 IN 在嵌套查询中最经常使用。

[例]

SELECT Sno,Sname,Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname='huangxin');  
// 与 huangxin 在同一个系的学生信息

+--------+---------------+-------+
| Sno    | Sname         | Sdept |
+--------+---------------+-------+
| 312019 | Linus Travlds | Linux |
| 312022 | huangxin      | Linux |
+--------+---------------+-------+

这种子查询的条件不依赖于父查询，称为不相关子查询。（ 执行过程： 由里向外处理，即先执行子查询，子查询的结果用于建立父查询的查找条件）.

如果子查询的查询条件依赖于父查询，这类子查询称为 相关子查询. 整个查询语句称为 相关嵌套查询（correlated nested query）.

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

指的是父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单个值时，可以用（>< >= <= !=或<> ）等比较运算符。

[例] 相关子查询，子查询的查询条件 x.Sno 依赖于父查询传进来的元组 x，是与父查询相关的，> 用于连接父查询 、子查询

SELECT Sno,Cno FROM Sc x WHERE Grade > (SELECT AVG(Grade) FROM Sc y WHERE y.Sno = x.Sno);  
// x,y 是表的别名

+--------+-----+
| Sno    | Cno |
+--------+-----+
| 312028 | 004 |
| 312028 | 006 |
| 312028 | 009 |
+--------+-----+

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

子查询返回单值时，可以用比较运算符，但返回 多个值时 要用ANY、ALL来进行连接。 而是用ANY/ALL 时，必须同时使用 比较运算符。

[例] 查询 ELE 系 中任意一个年龄小于ELE系学生的信息

SELECT Sname,Sage,Sdept FROM Student WHERE Sage<ANY(SELECT Sage FROM Student WHERE Sdept='ELE') AND Sdept != 'ELE';

+-------+------+-------+
| Sname | Sage | Sdept |
+-------+------+-------+
| Jason |   19 | CS    |
+-------+------+-------+

也可以用聚集函数实现：

SELECT Sname,Sage,Sdept FROM Student WHERE Sage < (SELECT MAX(Sage) FROM Student WHERE Sdept='ELE') AND Sdept != 'ELE';

+-------+------+-------+
| Sname | Sage | Sdept |
+-------+------+-------+
| Jason |   19 | CS    |
+-------+------+-------+

小结： 使用聚集函数实现子查询通常比 直接用 ANY/ALL 查询效率要高！

    =ANY 等价于 IN 
    <ANY 等价于 <MAX 
    !=ALL 等价于 NOT IN
    <ALL 等价于 <MIN

• 带有 EXISTS 的子查询

这种子查询不返回任何数据，只产生 逻辑真值‘true’ 或逻辑假值 ‘false’.

[例] 相关子查询，子查询依赖于父查询的 Student.Sno 进行条件判断，若成功，则 WHERE子句 返回 '真'，对“父”的每一个元组，重复完成这一过程。

SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM Sc WHERE Sno=Student.Sno AND Cno='004');

+--------+                      // WHERE 返回真值后，外层查询从元组中提取 Sname 列
| Sname  |
+--------+
| Taylor |
+--------+

NOT EXIST ： 内层查询结果为空时，则外层的 WHERE 子句返回真值。

SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Sc WHERE Sno=Student.Sno AND Cno='004');

+---------------+
| Sname         |
+---------------+
| dsf           |
| huangxin      |
| Jason         |
| Linus Travlds |
+---------------+

7. 集合查询

SELECT 语句查询的结果是元组的集合，所以多个SELECT 语句的结果可以进行 集合操作。

注意: 参加集合操作的各查询结果的列数必须相同，对应的数据类型也必须相同。

• UINON 会自动去掉重复元组, 如果要保留重复元组则使用 UNION ALL 操作符。

select * from Student where Sdept='CS' UNION select * from Student where Sage <=19;

+--------+-------+------+------+-------+
| Sno    | Sname | Ssex | Sage | Sdept |
+--------+-------+------+------+-------+
| 312001 | Jason | fe   |   19 | CS    |
+--------+-------+------+------+-------+

MySQL 不支持集合的 INTERSECT 、EXCEPT 操作。

可以用 WHERE 的 条件句实现：

select * from Student where Sage <= 22 AND Sdept='CS'; 
// 年龄小于22并且是CS系

8. 基于派生表的查询

子查询出现在 FROM 子句中， 这时子查询生成的临时派生表（derived table）成为主查询的查询对象。

[例] mysql 不能指定派生表的属性列，子查询SELECT后面的列名为其默认属性。 可以在子查询指定别名

SELECT Sname FROM Student,(SELECT Sno FROM Sc WHERE Cno='001') AS Sc1 WHERE Student.Sno=Sc1.Sno;
//派生表

+-------+
| Sname |
+-------+
| Jason |
+-------+

AS 关键字可以省略，但是必须为 派生关系指定一个别名。

SELECT Sname FROM Student,(SELECT Sno www FROM Sc WHERE Cno='001') Sc1 WHERE Student.Sno=Sc1.www;

+-------+
| Sname |
+-------+
| Jason |
+-------+

参考文章

数据库复习笔记——SELECT 大全