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 大全

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值