数据库系统--SQL语句

比眉伴天荒 2022-05-11 15:52 403阅读 0赞

# SQL数据定义 #

## 基本类型 ##

*  char(n)：固定长度的字符串，用户指定长度。
 *  varchar(n)：可变长度的字符串，用户指定最大长度n。等价于全称character varying。
 *  Int：整数类型。
 *  smallInt：小证书类型。
 *  numeric(p, d)：定点数，精度由用户指定。这个数有p位数字（加上一个符号位）。其中d位数字在小数点右边。
 *  real, double precision：浮点数与双精度浮点数。
 *  float(n)：精度至少为n位的浮点数。

## 基本模式定义 ##

**使用 create table 命令定义SQL关系。**

create table department
            (dept_name varchar(20),
            building varchar(15),
            budget numeric(12, 2)m
            primary key (dept_name));

**上述代码创建了一个department关系，具有dept\_name,building,budget三个属性，主码为dept\_name。**

### 完整性约束： ###

*  primary key(a1,a2,a3,…,an)：该声明表示属性a1,a2,a3,…,an构成关系的主码。主码属性必须非空且唯一。
 *  foreign key(a1,a2,a3,…,an) references r1：声明表示关系中任意元组在属性a1,a2,a3,…,an上的取值必须对应于关系r1中某元组在主码属性上的取值。
 *  not null：一个属性上的not null约束表明在该属性上不允许空值。

**注：SQL禁止破坏完整性约束的任何数据库更新。**

**Insert命令可将数据加载到关系中。例如**

Insert into instructor
        values(10211, 'Smith', 'Biology', 66000);

**delete命令可从关系中删除元组。**

delete from student

**注：上述命令将会删除student中所有元组。**

**drop table命令可以在数据库中删除一个关系**

drop table student

**注：上述命令将会从数据库中删除student表。**

**使用alter table命令可以为已有关系添加属性。新属性在关系中所有元组上的属性的值将为null。**

alter table r add A D

**r是现有关系的名字，A是待添加属性的名字，D是将添加属性的域。而通过**

alter table r drop A

**则可以从关系中删除属性。**

## SQL查询的基本结构 ##

### 单关系查询 ###

select name
    from instructor

**上面的代码即为从instructor中查询所有的name，有时候我们想要删掉重复，可加入关键词distinct，例如**

select distinct name
    from instructor

**select子句还可带有算术表达式，例如**

select ID, name, dept_name, salary*1.1
    from instructor

**上面这段代码会返回一个与insructor一样的关系，只是salary属性是原来的1.1倍，注意原关系并不发生改变。**

**where子句允许我们只需选出那些在from子句的结果关系中满足特定谓词的元组。例如**

select name
    from instructor
    where dept_name = 'Comp.Sci' and salary > 70000;
    **上面这段代码意为找出所有在Computer Science系并且工资超过70000d的教师的名字。**

### 多关系查询 ###

select name, instructor. dept_name, building
    from instructor, department
    where instructor.dept_name = department.dept_name;

**上述代码的意图是“找出所有教师的姓名，以及他们所在系的名称和系所在建筑的名称。”**

**注：1、dept\_name属性既出现在instructor关系中，也出现在department中，关系名被用作前缀来说明我们是用的是哪个属性。2、尽管子句是以select,from,where的次序写出，但是实际运算的顺序是from,where,select。即先求出from选中的关系的笛卡尔积，这个笛卡尔积有大量无效的元组，然后where子句中的位于可用来限制笛卡尔积所建立的组合，最后输出select子句中指定的属性。**

### 自然连接(natural join) ###

**自然连接运算作用于两个关系，并产生一个关系作为结果。自然连接只考虑那些在两个关系模式中都出现的属性上取值相同的元组对。所以对于查询“大学中所有讲授课程的教师，找出他们的姓名以及所讲述的所有课程标识”，我们可以这么写**

select name, course_id
    from instructor natural join teaches

**为了避免不必要的相等属性带来的危险，我们还可以使用自然连接的另一种构造形式**

select name, title
    from (instructor natural join teaches) join course using(course_id);

**注：join…using…运算中需要给定一个属性名列表，其两个输入都必须具有指定名称的属性。（using用来指定需要相同的是哪个属性）**

## 附加的基本运算 ##

### 更名运算 ###

**我们并不总能直接使用属性名。原因在于首先，from子句中的两个关系可能存在同名的属性。其次，如果在select子句中使用了算术表达式，那么属性就没有名字。因此，我们可以使用old-name as new-name的语法来改变结果中属性的名字**

select name as instructor_name, course_id
    from instructor, teaches
    where instructor.ID = teaches.ID;

**重命名还可以把一个长的关系名替换成短的，这样在查询的其他地方使用时就会方便。例如**

select T.name , S.course_id
    from instructor as T, teaches as S
    where T.ID = S.ID;

**重命名的另一个重要原因是为了适用于需要比较同一个关系中元组的情况。为此我们需要把一个关系与他自身进行笛卡尔积运算，如果不重命名就不可能把一个元组与另一个区分开来。例如我们要查询“满足条件的所有教师的名字，他们的工资至少比Biology系的某一个教师的工资要高”**

select distinct T.name
    from instructor as T, instructor as S
    where T.ID = S.ID;

**T、S准确的说是被声明为instructor的别名。在SQL标准中被称作相关名称(correlation name)，但也经常被称为表别名(table alias)，或相关变量(correlation variable)，或者 元组变量(tuple variable)**

### 字符串运算 ###

**在字符串上可以使用like操作符实现模式匹配：**

*  百分号(%)：匹配任意子串。
 *  下划线(\_)：匹配任意一个字符。

**注：模式匹配是大小写敏感的。**

*  'Intro%'匹配任何以Intro打头的字符串。
 *  '%Comp%'匹配任何包含’Comp’的字符串。
 *  '\_\_\_'匹配只含三个字符的字符串。
 *  '\_\_\_%'匹配至少含三个字符的字符串。

**查询写法如下：**

select dept_name
    from department
    where building like '%Waston%';

**SQL在like比较运算中使用escape关键词来定义转义字符。例如**

like 'ab\%cd%' escape '\'

用来匹配所有以’ab%cd’开头的字符串。

**注：SQL同样也支持not like语法。**

### 排列元组的显示次序 ###

**order by子句可以让查询结果按排列顺序显示，例如**

select name
    from instructor
    where dept_name = 'Physics'
    order by name;

**该段代码可以按字母序列出Physics系的所有教师名字。**

**order by语句默认使用升序，要使用降序可以用desc来表示，或者使用asc来明确是升序。假设我们想按salary的降序列出整个instructor关系，如果有工资相同的，则按姓名升序排列，则表示如下：**

select *
    from instructor
    order by salary desc, name asc;

### where子句谓语 ###

**SQL提供betwenn比较运算符来说明一个值是小于或等于某个值，同时大于或等于某个值。如果我们想找出工资在90000美元和100000美元之间的教师的名字，可以这样写：**

select name
    from instructor
    where salary between 90000 and 100000;

**类似的也可以使用not between语法**

## 集合运算 ##

### 并运算 ###

**为了找出在2009年秋季，或者在2010年春季开课或两个学期都开课的所有课程。代码如下：**

（select course_id
      from section
      where semester = 'Fall' and year = 2009
    ）
    union
    （select course_id
      from section
      where semester = 'Spring' and year = 2010
    ）;

**注：union运算自动去重。若想保留重复则用union all语句。**

### 交运算 ###

**为了找出在2009年秋季和2010年春季同时开课的所有课程的集合。代码如下：**

（select course_id
      from section
      where semester = 'Fall' and year = 2009
    ）
    Intersect
    （select course_id
      from section
      where semester = 'Spring' and year = 2010
    ）;

**同样的若想保留重复，可以使用Intersect all语句。**

### 差语句 ###

**为了找出在2009年秋季开课但没有在2010年春季开课的所有课程，代码如下：**

（select course_id
      from section
      where semester = 'Fall' and year = 2009
    ）
    except
    （select course_id
      from section
      where semester = 'Spring' and year = 2010
    ）;

**注：使用except all可以保留重复。**

## 聚集函数 ##

*  平均值： avg
 *  最小值：min
 *  最大值：max
 *  总和：sum
 *  计数：count

### 基本聚集 ###

**找出Computer Science系老师的平均工资，代码如下：**

select avg(salary)
    from instructor
    where dept_name = 'Comp.Sci';

**有些时候计算聚集函数是我们想去掉重复元组，可以使用distinct关键词。，若我们想找出2010年春季讲授一门课程的老师总数，代码如下：**

select count(distinct ID)
    from teaches
    where semester = 'Spring' and year = 2010;

### 分组聚集 ###

**group by子句中给出的属性是用来构造分组的，在group by子句中所有属性上取值相同的元组将被分在一个组中。例如**

select dept_name ,avg(salary), as avg_salary
    from instructor
    group by dept_name;

**以上代码可以显示所有部门的平均工资**

### having子句 ###

**having子句类似where，但只针对group by形成的分组，having字句中可以使用聚集函数。例如**

select dept_name, avg(salary) as avg_salary
    from instructor
    group by dept_name
    having avg(salary) > 42000;

## 嵌套子查询 ##

### 集合成员资格 ###

**连接词In可以测试元组是否是集合中的成员。查询在2009年秋季和2010年春季同时开课的所有课程，代码如下：**

select distinct course_id
    from section
    where semester = 'Fall' and year = 2009 and course_id in (select course_id
    from section
    where semester = 'Spring ' and year = 2010
    )；

**按照SQL实际执行的逻辑顺序来理解，where语句是返回元组（即选择表中的行），而where语句的判断该取哪些行的标准，则是判断这些行（元组）中的属性是不是能使where语句里的条件为真。那么在这段代码的where语句中，我们是要选出所有的semester属性为“Fall”，year属性为2009，并且course\_id还要出现在所有在2010年春天开过课的关系中。**

### 集合的比较 ###

**查询满足条件的所有老师的姓名，他们的工资至少比Biology系某一个教师的工资要高，代码如下：**

select name
    from instructor
    where salary > some (select salary
                         from instructor
                         where dept_name = 'Biology'
    );

**>some在SQL中表示“至少比某一个大”，当然也有<some,<=some,>=some的用法。同时还有>all的用法，表示“比所有的都大”。**

### 空关系测试 ###

**通过exist关键词可以查询是否为空，例如**

select course_id
    from section as S
    where semester = 'Fall' and year = 2009 and exists(select *
                    from section as T
                    where semester = 'Spring' and year = 2010 and S.course_id = T.course_id
    );

**个人理解这段代码的意思是从2009年秋天开课的课程中查找是否2010年春天开设的同样的课程。**

### 重复元素存在性测试 ###

select T.course_id
    from course as T
    where unique(select R.course_id
                 from section as R
                 where T.course_id = R.course_id and
                 R.year = 2009);

**上述代码是找出所有在2009年最多开设过一次的课程，查询结果中没有重复的元组，unique结构将会返回true，反之返回false。**

### from子句中的子查询 ###

**SQL允许在from子句中使用子查询表达式。因为在任何select-from-where 结构中返回的结果都是关系，因而可以被插入到另一个select-from-where中任何关系可以出现的位置。例如查询找出系平均工资超过42000美元的那些系中教师的平均工资**

select dept_name, avg_salary
    from (select dept_name, avg(salary) as avg_salary
          from instructor
          group by dept_name)
          as dept_avg(dept_name, avg_salary)
    where avg_salary > 42000;

**上述代码中子查询的结果关系被命名为dept\_avg，其属性名是dept\_name和avg\_salary。**

## 数据库的修改 ##

### 删除 ###

**使用下列语句表示删除**

delete from r
    where P;

**delete语句将删除所有满足p条件的元组。若不加where语句则是删除关系r中的所有元组。**

### 插入 ###

**要往关系中插入数据，我们可以指定待插入的元组，或者写一条查询语句来生成待插入的元组集合。例如：**

Insert into course
        values('CS-437', 'Database Systems', 'Comp.Sci', 4)

Insert into instructor
        select ID, name, dept_name, 18000
        from student 
        where dept_name = 'Music' and tot_cred > 144;

**在执行插入之前先执行完select语句是非常重要的。如果在执行select 语句的同时执行插入语句，且在student上没有主码约束的话，就会无限插入。**

**注：若待插入元组中只给出了模式中的部分属性的值，那么其余属性的值将为null。**