数据库复习-----SQL语句 汇总

SQL语句

SQL的定义功能

1.表结构的定义

CREAT TABLE 表名
（
   属性名  数据类型     PRIMARY KEY,
   属性名  数据类型，
   属性名  数据类型，
   PRIMARY KEY( 属性名，属性名)/*关键字由多个属性构成时使用该方法*/
）;
NOT NULL   UNIQUE/*等同于主码*/	

/*外码的定义*/
CREAT TABLE 表名 
（
   属性名  数据类型     REFERENCES 表名 （属性名）,
   属性名  数据类型，
   属性名  数据类型，
   FOREIGN KEY (属性名)  REFERENCES 表名 （属性名）
）;
/*默认值的定义*/
CREAT TABLE 表名
（
   属性名  数据类型   DEFAULT  默认值;
   属性名  数据类型   DEFAULT  默认值;
   属性名  数据类型   DEFAULT  默认值;
）;

2.基本表的修改和删除

1.修改基本表
/*给表增加属性*/、
ALTER TABLE  学生  ADD COLUMN 年龄 SMALLINT;    --给学生表增加年龄属性
/*删除表中的属性*/
ALTER TABLE  学生  DROP COLUMN 年龄;  --在学生表中删除年龄属性
/*修改列定义，包括修改列名和数据类型*/
ALTER TABLE  课程  ALTER COLUMN  课程名称   CHAR(20)；  --修改课程表中的课程名称属性的长度为20位
2.删除基本表
DROP TABLE 表名  RESTRICT || CASCADE；
/*RESTRICT 表示如果有视图或者约束条件涉及要删除的表时，就禁止DBMS执行该命令，CASCADE表示将该表和其涉及的对象一起删除*/
3.索引的建立与删除
CREATE UNIQUE || CLUSTER INDEX  索引名 ON 表名  （属性名）；
/*1. 索引可以建立在该表的一列或者多列上，各列名之间用逗号分隔，这种由两列或者多列属性组成的索引称为复合索引。
  2. UNIQUE 表明此索引的每个索引值只对应唯一的数据记录
  3. CLUSTER 表明建立聚簇索引，聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织*/
DROP INDEX 索引名；     --删除索引

数据查询

1.单表查询

1. SQL中的投影
SELECT  属性名
FROM 表名；
/*通配符 * 代表所有的属性*/
/*SELECT 后面不仅可以是表中已经有的列名，也可以是对列进行运算后得到的表中不存在的信息，通过运算得到的列，系统你都会自动地赋给它一个列名，但是这样的列名晦涩难懂，可以通过使用　AS　语句来给列起别名　*/
SELECT 学号 AS abc
2.SQL中的选择运算
WHERE  条件；
WHERE 出生年份 BETWEEN 1996 AND 2000;
WHERE 学院 IN ('信电'，'计科');   ------查找属性值在给定集合的元组 （NOT IN 与之相反）
/*字符匹配可用LIKE*/
WHERE 姓名 LIKE '王%'，/*查姓王的*/
WHERE 姓名 LIKE '_小'，/*查第二个字是小的人*/-----通配符"%"代表任意长度的字符串包括0；通配符 "_"用来代表任何单个字符。
/*如果对于通配符只需要它表示本来的字符意思，不需要执行通配符的功能，可用ESCAPE'\' 对通配符进行转义，紧跟在'\'后面的通配符变为普通的字符*/
WHERE 课程名 LIKE 'DB\_Design' ESCAPE '\ ';
/*空值*/
WHERE 成绩 IS NULL;   ----没成绩的，IS NOT NULL相反；
3.对查询结果进行分组
GROUP BY 子句可以将查询结果的各行，按照一列或者多列取值相等的原则进行分组，对查询结果分组的目的是为了细化集函数的作用对象。
SELECT 课程号，COUNT(学号) AS 选课人数
FROM 学习
GROUP BY 课程号；
HAVING 条件     /*查询结果为满足条件的组*/
/*WHERE 语句作用对象是基本表或者视图，从中选出满足条件的元组
  HAVING 语句作用对象是组，从中选出满足条件的组*/
4.
COUNT (*): 统计元组个数
COUNT (列名): 统计该列中值的个数
SUM (列名): 计算一列值的总和（必须是数值型）
AVG （列名）：计算一列值的平均值
MAX (列名)： 求一列中的最大值
MIN （列名）：求一列中的最小值
/*如果指定DISTINCT 短语，则表示在计算时要取消指定列的重复值，如果没指定或者指定ALL，则不去掉重复值*/
5. 使用 ORDER BY短语对查询结果排序
ORDER BY ASC 升序  （从上往下看，数值越来越大）
ORDER BY DESC 降序  （从上往下看，数值越来越小）

2.连接查询

1.等值连接非等值连接
表名.列名 比较运算符 表名. 列名
/*
   1.连接谓词中的列名称为连接字段，连接条件中的各连接字段类型必须是可比的但不必相同。
   2.DBMS执行连接操作的过程，首先在表１中找到第一个远足，然后从头开始顺序扫描或者索引扫描表２，查找满足连接条件的元组，每找到一个满足条件的元组，就将表１中的第一个元组与该元组连接起来，形成结果表中的一个元组.............
*/
2.自身连接查询
3.外链接查询
/*左外连接*/
---theta 方式
SELECT 姓名，课程号，成绩
FROM 学生，学习
WHERE 学习.学号（+）=学生.学号     ------等号那边是开口方向
---ANSI方式
SELECT 姓名，课程号，成绩
FROM 学生   （开口所向的那个表）
LEFT OUTER JOIN 学习 ON 学生.学号=学习.学号；
/*右外连接*/
---theta方式
SELECT 姓名，课程号，成绩
FROM 学生，学习
WHERE 学生.学号=（+）学习.学号
---ANSI方式
SELECT 姓名，课程号，成绩
FROM 学生
RIGHT OUTER JOIN 学习 ON 学习.学号= 学生.学号
4.复合条件连接
5.集合运算连接查询
并：UNION
交：INTERSECT
差：EXCEPT
( SELECT 学号
  FROM 学习
  WHERE 课程号= '180101')
  UNION
( SELECT 学号
  FROM 学习
  WHERE 课程号 ='080102'
)
UNION ,INTERSECT,EXCEPT 都自动去除重复；如果想保留全部，UNION ALL.

3.嵌套查询

分类： 1. 不相关子查询：子查询独立于上层的父查询，每个子查询在其上一级查询处理之前可以完成求解，子查询的结果用于建立其父查询的查找条件
       2. 相关子查询：子查询依赖于父查询，最内层子查询的执行需要用到上层父查询中的某些属性值，因此最内层的子查询不能独立于父查询先完成。
1. 带有IN 谓词的子查询
   父查询和子查询之间用IN 进行连接，判断某个属性列值是否在子查询的结果中
2. 带有比较运算符的子查询   （子查询返回单值时可用）
3. 带有ANY 或ALL谓词的子查询   ANY 代表一些，某个；ALL 代表全部。
4. 带有EXISTS谓词的子查询
   EXISTS 代表存在量词，带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值true，或逻辑假值false
   SELECT 学号，姓名
   FROM 学生
   WHERE EXISTS 
      (  SELECT *
         FROM 学习
         WHERE 学生.学号 = 学习.学号
         AND  学习.课程号 = '180101'
      );
      /*执行顺序：从学生表中依次取出每个元组的学号，用此值去检测学习表，如果学习表中存在这样的元组，其学号与此学生.学号相同，并且该学生学习的课程的课程号是1080101 ，则取此学生的学号和姓名送入结果表中*/
      与EXISTS 相反的是NOT EXISTS ，当内层查询的结果为空，则NOT EXISTS 返回结果为真，外层WHERE 字句返回真值，否自返回假值。
      
      
！！！ 除法问题
 除法问题必定涉及一个集合包含另一个集合的概念，首先，把这两个集合找出来。集合	A 包含集合 B 则说明，B-A为空。
 例题：查询至少选修了091501号学生选修的全部课程的学生学号。
     集合A 某个学生选修的课程号；集合B 学号为091501 的学生选修的课程的课程号
     A:
        SELECT 课程号
        FROM 学习
        WHERE 学习.学号 = 'xxxx' (某个学生的学号)
     B:
        SELECT 课程号
        FROM 学习
        WHERE 学习.学号 = '091501';
    然后：B-A 为空
        NOT EXISTS
        (  (SELECT 课程号
            FROM 学习 AS First
            WHERE First.学号 = '091501'
            )
        EXCEPT
           (  SELECT 课程号
              FROM 学习 AS Second
              WHERE Second.学号 = 'xxxx');
        )
        
    最后：
        SELECT 学号
        FROM 学生
        WHERE NOT EXISTS
        (  (SELECT 课程号
            FROM 学习 AS First
            WHERE First.学号 = '091501'
            )
        EXCEPT
           (  SELECT 课程号
              FROM 学习 AS Second
              WHERE Second.学号 = 学生.学号);
        )
        /*从学生表中依次取出每个学生的学号，送入内层查询中进行检验*/
         
        另一种：
            SELECT 学号
            FROM 学生
            WHERE NOT EXISTS
              (SELECT *
                FROM 学习 AS First
                WHERE First.学号 = '091501' AND NOT EXISTS
               (  SELECT *
                  FROM 学习 AS Second
                  WHERE Second.学号 = 学生.学号
                  AND   First.课程号 = Second.课程号
               )）；

数据查询

1.插入数据

1.插入单个元组
INSERT 
INTO 表名  （属性列，属性列，属性列）
VALUES  (常量，常量，常量)；
例如： INSERT 
       INTO 学生
       VALUES ('091530','夏雨','男','海南','1999','计算机')
2.插入子查询结果
INSERT 
INTO 表名 （属性列，属性列）
子查询；
/*子查询的SELECT 的属性列应该与 INTO 的属性列对应*/
3.删除数据
DELETE 
FROM 表名
WHERE 条件；
/*省略WHERE语句时，删除所有的元组值*/
4.修改数据
UPDATE 表名
SET 列名 = 表达式
WHERE 条件；
修改指定表中满足WHERE条件的元组的某些属性的值。
如： UPDATE 学生
     SET 籍贯 = '江苏'
     WHERE  学号 = '091611';

视图

1.建立视图

CREATE VIEW 视图名（列名）
AS 子查询
/*注意，视图的列名或者全部省略，或者全部给出，没有其他选择。下列三种情况必须指定组成视图的所有列名
 1.某个目标列是奇函数或列表达式
 2.多表连接时选出了几个同名的列作为视图的字段
 3.需要在视图中为某个列启用新的更合适的名字
*/
例如：
CREATE VIEW CS_VIEW
AS SELECT *
FROM 学生
WHERE 学院 = '计算机'；
/*
 1.视图可以建立在多个基本表上
 2.视图可以建立在视图上
*/
/*
  定义视图时只是把视图的定义放入数据字典，并不执行子查询语句。只有在运行视图时，才会得到数据；不运行时视图只是一个表的模式的定义
*/

2.删除视图

DROP VIEW 视图名
/*一个视图被删除后，由它导出的其他视图也将失效，用户应该使用DROP VIEW 语句将他们一一删除*/

3.查询视图

DBMS在查询视图时，首先先对其有效性进行检查，看其涉及的视图或者基本表是否在数据库中存在，如果存在，则从数据字典中取出查询涉及的视图的定义，把定义中的子查询和用户对视图的查询结合起来，转化成对基本表的查询，然后执行这个修正过的查询。

4.视图更新

插入（INSERT）,删除（DELETE）,修改（UPDATE）.
一个视图是从单表导出的，并且只是去掉了某些行和列（不包括关键字），称这类视图为行列子集视图，是可更新的视图。

数据控制

1.授权
GRANT 权限 ，权限
ON 对象类型 对象名
TO 用户，用户
/*语义为：将对指定操作对象的指定操作权限授予指定的用户*/
例如：
GRANT SELECT ON TABLE 学生 TO User1;    ---- 把对学生表的查询权限授予用户1；
GRANT ALL PRIVILEGES ON TABLE 学生，课程 TO User2,User3;  ---把对学生，课程表的所有权限授予用户23；
GRANT SELECT ON TABLE 学习 TO PUBLIC;-----把对学生表的查询权限授予所有人；
2.收回权限
REVOKE 权限
ON 对象类型 对象名
FROM 用户
例如：
REVOKE UPDATE(成绩) ON TABLE 学习 FROM user4；  ----把用户4修改成绩的权限收回