SQL语言（一）

1.数据类型

 

2.模式

[例1] 为用户WANG定义一个学生-课程模式S-T

            CREATE SCHEMA “S-T” AUTHORIZATION WANG;

[例2] CREATE SCHEMA AUTHORIZATION WANG;

  该语句没有指定<模式名>，<模式名>隐含为<用户名>

3.创建模式

 

 CREATE SCHEMA <模式名>  AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>]

[例]  为用户ZHANG创建了一个模式TEST，并且在其中定义一个表TAB1

CREATE SCHEMA TEST AUTHORIZATION ZHANG

CREATE TABLE TAB1   ( COL1 SMALLINT,

                                            COL2 INT,

                                            COL3 CHAR(20),

                                            COL4 NUMERIC(10,3),

                                            COL5 DECIMAL(5,2)

                                          );

 

 4.删除模式

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

（1）CASCADE（级联）

删除模式的同时把该模式中所有的数据库对象全部删除

（2）RESTRICT（限制）

如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。

仅当该模式中没有任何下属的对象时才能执行。

5.定义基本表

  CREATE TABLE <表名>

      (<列名> <数据类型>[ <列级完整性约束条件> ]

      [,<列名> <数据类型>[ <列级完整性约束条件>] ]

   …

      [,<表级完整性约束条件> ] );

如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上

[例1]  建立“学生”表Student。学号是主码，姓名取值唯一。

CREATE TABLE Student         

      (Sno   CHAR(9) PRIMARY KEY,      /* 列级完整性约束条件,Sno是主码*/                  

        Sname CHAR(20) UNIQUE,             /* Sname取唯一值*/

        Ssex    CHAR(2),

        Sage   SMALLINT,

        Sdept  CHAR(20)

      );

 [例2]  建立一个学生选课表SC

   CREATE TABLE  SC

          (Sno  CHAR(9),

           Cno  CHAR(4), 

           Grade  SMALLINT，

           PRIMARY KEY (Sno,Cno),             /* 主码由两个属性构成，必须作为表级完整性进行定义*/

           FOREIGN KEY (Sno) REFERENCES Student(Sno),

                                                                  /* 表级完整性约束条件，Sno是外码，被参照表是Student */

           FOREIGN KEY (Cno) REFERENCES Course(Cno)

                                                                 /* 表级完整性约束条件， Cno是外码，被参照表是Course*/

        );

 

6.模式与表

（1）每一个基本表都属于某一个模式

（2）一个模式包含多个基本表

（3）定义基本表所属模式

方法一：在表名中明显地给出模式名
Create table"S-T".Student(......);     /*模式名为 S-T*/

Create table "S-T".Cource(......);

Create table "S-T".SC(......);

（4)）创建基本表（其他数据库对象也一样）时，若没有指定模式

 方法一：系统根据搜索路径来确定该对象所属的模式

 方法二：在创建模式语句中同时创建表

 方法三：设置所属的模式

  (5)关系数据库管理系统会使用模式列表中第一个存在的模式作为数据库对象的模式名

  (6) 若搜索路径中的模式名都不存在，系统将给出错误

显示当前的搜索路径： SHOW search_path;
搜索路径的当前默认值是：$user， PUBLIC

  (7)数据库管理员用户可以设置搜索路径，然后定义基本表

     SET search_path TO "S-T",PUBLIC;

     Create table Student(......);    结果建立了S-T.Student基本表。

关系数据库管理系统发现搜索路径中第一个模式名S-T，就把该模式作为基本表Student所属的模式。

 

7.修改基本表

ALTER TABLE <表名>

[ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]

[ ADD <表级完整性约束>]

[ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]

[ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]

[ALTER COLUMN <列名><数据类型> ] ;

<表名>是要修改的基本表
ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件

DROP COLUMN子句用于删除表中的列
如果指定了CASCADE短语，则自动删除引用了该列的其他对象

如果指定了RESTRICT短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列

DROP CONSTRAINT子句用于删除指定的完整性约束条件
ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型

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

 ALTER TABLE Student ADD S_entrance DATE;

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

 [例2] 将年龄的数据类型由字符型改为整数。

      ALTER TABLE Student ALTER COLUMN Sage INT;

 [例3] 增加课程名称必须取唯一值的约束条件。

      ALTER TABLE Course ADD UNIQUE(Cname);

8.删除基本表

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

①RESTRICT：删除表是有限制的。

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

在删除基本表的同时，相关的依赖对象一起删除

9.建立索引

谁可以建立索引：数据库管理员 或 表的属主（即建立表的人）
谁维护索引：关系数据库管理系统自动完成 
使用索引：关系数据库管理系统自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引
 

CREATE [UNIQUE] [CLUSTER] INDEX <索引名>

ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…);

<表名>：要建索引的基本表的名字
索引：可以建立在该表的一列或多列上，各列名之间用逗号分隔
<次序>：指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC
UNIQUE：此索引的每一个索引值只对应唯一的数据记录
CLUSTER：表示要建立的索引是聚簇索引

例：SC表按学号升序和课程号降序建唯一索引

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

10.修改索引

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

[例] 将SC表的SCno索引名改为SCSno

 ALTER INDEX SCno RENAME TO SCSno;

11.删除索引

DROP INDEX <索引名>;    

删除索引时，系统会从数据字典中删去有关该索引的描述。

12.数据查询

       SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …

       FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)     

                   [AS]<别名>

[ WHERE <条件表达式> ]

[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]

[ ORDER BY <列名2> [ ASC|DESC ] ];

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

 

13. 单表查询

1.选择表中的若干列

（1）查询指定列

  [例]  查询全体学生的学号与姓名。

  SELECT Sno,Sname

  FROM Student; 

（2）查询全部列

①在SELECT关键字后面列出所有列名

②将<目标列表达式>指定为  *

[例]  查询全体学生的详细记录

SELECT  Sno,Sname,Ssex,Sage,Sdept

FROM Student;

或

SELECT  *

FROM Student;

（3）查询经过计算的值

SELECT子句的<目标列表达式>不仅可以为表中的属性列，也可以是表达式

【例1】 查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名。

SELECT Sname,'Year of Birth: ',2014-Sage,LOWER(Sdept)

FROM Student;

输出结果：

  Sname   'Year of Birth:'  2014-Sage   LOWER(Sdept)

      李勇    Year of Birth:    1994         cs

注意：出生年份即当前年份-年龄，此题目假设当时为2014年

【例2】使用列别名改变查询结果的列标题:

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

      FROM Student;

输出结果：

    NAME      BIRTH         BIRTHDAY   DEPARTMENT  

     李勇    Year of Birth:    1994             cs

 

2.选择表中的若干元组

（1）消除取值重复的行

 ①  如果没有指定DISTINCT关键词，则缺省为ALL

[例1]  查询选修了课程的学生学号。

    SELECT Sno   FROM SC;    等价于：SELECT ALL  Sno  FROM SC;

  执行上面的SELECT语句后，结果为：

      Sno

  201215121

  201215121

  201215121

  201215122

  201215122

 ②  指定DISTINCT关键词，去掉表中重复的行

   SELECT DISTINCT Sno    FROM SC;

 执行结果：

      Sno

  201215121

  201215122

（2）查询满足条件的元组

常用的查询条件：

 

[例1] 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄

SELECT Sname, Sdept, Sage

FROM     Student

WHERE   Sage BETWEEN 20 AND 23;

[例2] 查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。

  SELECT Sname, Ssex

  FROM  Student

  WHERE Sdept IN ('CS','MA’,'IS' );

where就是搜索查询的核心，满足where条件的元组按照select后面的格式输出

where是横向拿取行，select是纵向拿取列，两者相互配合

Ⅰ 字符匹配：

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

<匹配串>可以是一个完整的字符串，也可以含有通配符%和 _

① % （百分号）  

代表任意长度（长度可以为0）的字符串

 例如a%b表示以a开头，以b结尾的任意长度的字符串

②_ （下横线）  

代表任意单个字符。

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

[例1]  查询所有姓刘学生的姓名、学号和性别。

       SELECT Sname, Sno, Ssex

      FROM Student

      WHERE  Sname LIKE '刘%';

[例2]  查询姓"欧阳"且全名为三个汉字的学生的姓名。

       SELECT Sname

      FROM   Student

      WHERE  Sname LIKE '欧阳__';

[例3]  查询名字中第2个字为"阳"字的学生的姓名和学号。

      SELECT Sname，Sno

      FROM     Student

      WHERE  Sname LIKE '__阳%';

[例4]  查询所有不姓刘的学生姓名、学号和性别。

      SELECT Sname, Sno, Ssex

      FROM     Student

      WHERE  Sname NOT LIKE '刘%';

 Ⅱ：字符匹配

使用换码字符将通配符转义为普通字符

[例1]  查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。

      SELECT  *

      FROM    Course

      WHERE  Cname LIKE  'DB\_%i_ _' ESCAPE '\ ' ;

  ESCAPE '＼' 表示“ ＼” 为换码字符

Ⅲ：涉及空值的查询

谓词： IS NULL 或 IS NOT NULL 

注意： “IS” 不能用 “=” 代替

 [例]  查所有有成绩的学生学号和课程号。

      SELECT Sno，Cno

      FROM     SC

      WHERE  Grade IS NOT NULL;

Ⅳ：多重条件查询

逻辑运算符：AND和 OR来连接多个查询条件

 AND的优先级高于OR
可以用括号改变优先级

 [例1]  查询计算机系年龄在20岁以下的学生姓名。

      SELECT Sname

       FROM  Student

       WHERE Sdept= 'CS' AND Sage<20;

[例2]  查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。

SELECT Sname, Ssex

FROM     Student

WHERE  Sdept= ' CS' OR Sdept= ' MA' OR Sdept= 'IS ';

 3.ORDER BY子句

可以按一个或多个属性列排序
升序：ASC;降序：DESC;缺省值为升序
[例3.40]查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

        SELECT  *

        FROM  Student

        ORDER BY Sdept, Sage DESC;   //Sdept默认为升序；

 4.聚集函数

①统计元组个数

COUNT(*)

②统计一列中值的个数

COUNT([DISTINCT|ALL] <列名>)

③计算一列值的总和（此列必须为数值型）

SUM([DISTINCT|ALL] <列名>) 

④计算一列值的平均值（此列必须为数值型）

AVG([DISTINCT|ALL] <列名>)

⑤求一列中的最大值和最小值

    MAX([DISTINCT|ALL] <列名>) 

   MIN([DISTINCT|ALL] <列名>)

    [例1]  查询学生总人数。

    SELECT COUNT(*)

    FROM  Student; 

     [例2]  查询选修了课程的学生人数。

     SELECT COUNT(DISTINCT Sno)

     FROM SC;

     [例3]  计算1号课程的学生平均成绩。

          SELECT AVG(Grade)

          FROM    SC

          WHERE Cno= ' 1 ';

  [例4]  查询选修1号课程的学生最高分数。

   SELECT MAX(Grade)

   FROM SC

   WHERE Cno='1';

  [例5 ] 查询学生201215012选修课程的总学分数。

  SELECT SUM(Ccredit)

  FROM  SC,Course

  WHERE Sno='201215012' AND SC.Cno=Course.Cno;   //注意连接条件；

5.GROUP BY子句

GROUP BY子句分组：  细化聚集函数的作用对象

使用groupby主要是因为纵向查询的两组最终所获取的想要一对一但实际为一对多的情况，

一对多其中的多为一的限定条件，则使用having语句

 如果未对查询结果分组，聚集函数将作用于整个查询结果
 对查询结果分组后，聚集函数将分别作用于每个组
按指定的一列或多列值分组，值相等的为一组

[例1]  求各个课程号及相应的选课人数。

这里是想要一个课程号对应一个选课人数的总和，但实际上一个课程号对应多个选课人 故使用

     SELECT Cno，COUNT(Sno)

     FROM    SC

     GROUP BY Cno; 

[例2]  查询选修了3门以上课程的学生学号。

这里是想要一个学生号，但实际上一个学生对应多门课程，课程数量为限定学生号的条件，故使用

     SELECT Sno

     FROM  SC

     GROUP BY Sno

     HAVING  COUNT(*) >3;       //看哪一组sno的行数大于3

图解：假如查询出以下三组

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

这里是想要一个学号和一个平均成绩，而实际上一个学号对应多个成绩，平均成绩为学号的限定条件，故使用

    SELECT  Sno, AVG(Grade)

    FROM  SC

    GROUP BY Sno    //每个学生所有选课的平均成绩大于等于90，所以按照学号分组；

    HAVING AVG(Grade)>=90;

注意：不能把 WHERE AVG(Grade)>=90作为条件，因为WHERE子句中是不能用聚集函数作为条件表达式

HAVING短语与WHERE子句的区别：

• 作用对象不同
• WHERE子句作用于基表或视图，从中选择满足条件的元组
• HAVING短语作用于组，从中选择满足条件的组。