天天看点
返回

数据库系统概念笔记——第三章：SQL

文章目录

    • 第三章：SQL
      • 3.2 SQL数据定义
        • 3.2.1 基本类型
        • 3.2.2 基本模式定义
      • 3.3 SQL查询的基本结构
        • 3.3.1 单关系查询
        • 3.3.2 多关系查询
        • 3.3.3 自然连接
      • 3.4 附加的基本运算
        • 3.4.1 更名运算
        • 3.4.2 字符串运算
        • 3.4.4 排列元组的显示次序
        • 3.4.5 where子句谓词
      • 3.5 集合运算
        • 3.5.1 并运算
        • 3.5.2 交，差运算
      • 3.6 空值
      • 3.7 聚集函数
        • 3.7.1 基本聚集
        • 3.7.2 分组聚集
        • 3.7.3 having子句
        • 3.7.4 对空值和布尔值的聚集
      • 3.8 嵌套子查询
      • 3.8.1 集合成员资格
      • 3.8.2 集合的比较
      • 3.8.3 空关系测试
      • 3.8.6 with子句
      • 3.9 数据库的修改
        • 3.9.1 删除
        • 3.9.2 插入
        • 3.9.3 更新

第三章：SQL

3.2 SQL数据定义

3.2.1 基本类型

SQL标准中支持多种固有类型

  • char(n)：固定长度的字符串，用户指定长度n
  • varchar(n)：可变长度的字符串，用户指定最大长度n（注意在Oracle数据库中写作varchar2(n)）
  • int：整数类型
  • smallint：小整数类型
  • numeric(p,d)：定点数，这个数有p位数字（加上符号位），其中d位在小数点右边
  • real，double precision：浮点数与双精度浮点数
  • float(n)：精度至少为n位的浮点数

注意点：

  1. char类型存储固定长度的字符串，如果存入字符串的长度不够，则会以空格补齐
  2. varchar类型不会补充空格，根据用户填入的字符串长度保存
  3. 定点数numeric(3,1)可以精确存储44.5，但却不能精确存储444.5或0.32这样的数

3.2.2 基本模式定义

使用create table命令定义SQL关系

数据库系统概念笔记——第三章：SQL

课本中的大学数据库的部分建表sql语句（MySQL）

create table classroom
	(building		varchar(15),
	 room_number		varchar(7),
	 capacity		numeric(4,0),
	 primary key (building, room_number)
	);
create table department
	(dept_name		varchar(20), 
	 building		varchar(15), 
	 budget		        numeric(12,2) check (budget > 0),
	 primary key (dept_name)
	);
create table course
	(course_id		varchar(8), 
	 title			varchar(50), 
	 dept_name		varchar(20),
	 credits		numeric(2,0) check (credits > 0),
	 primary key (course_id),
	 foreign key (dept_name) references department(dept_name)
		on delete set null
	);
create table instructor
	(ID			varchar(5), 
	 name			varchar(20) not null, 
	 dept_name		varchar(20), 
	 salary			numeric(8,2) check (salary > 29000),
	 primary key (ID),
	 foreign key (dept_name) references department(dept_name)
		on delete set null
	);
  • primary key(A1,A2,A3…)：表明属性A1,A2,A3…构成表的主码，主码的属性必须非空且唯一
  • foreign key(A1,A2,A3…) references ：声明表中这些属性的取值必须和另一个关系上某些属性的取值对应
  • not null：限制不能为空
  • check：自定义限制，括号中填限制的内容

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

使用alert table可以为已有关系增加属性

alert table r add A D

A是待添加属性的名字，D是属性的数据类型

如果表中原本就有数据，则原有元组在新添加的属性上的值全部为null

补充

1.使用constraint关键字给约束命名

数据库系统概念笔记——第三章：SQL
数据库系统概念笔记——第三章：SQL

2.表空间（tablespace）

表空间是数据库的逻辑划分，一个表空间只能属于一个数据库，以一个数据库可以拥有多个表空间

表空间分为系统表空间和用户表空间

一个表空间可以包含多张表，一张表只能属于一个表空间

数据库系统概念笔记——第三章：SQL

3.表的属主Owner

每个数据库对象都有自己的属主，表也有自己的属主。数据库对象默认属主是创建该对象的用户

属主拥有表的所有权限

数据库系统概念笔记——第三章：SQL

属主为user1

4.修改表的定义alter

格式：

数据库系统概念笔记——第三章：SQL

例：

数据库系统概念笔记——第三章：SQL
数据库系统概念笔记——第三章：SQL

5.删除表中的属性或约束

数据库系统概念笔记——第三章：SQL
数据库系统概念笔记——第三章：SQL

删除表中的约束

数据库系统概念笔记——第三章：SQL
数据库系统概念笔记——第三章：SQL

3.3 SQL查询的基本结构

3.3.1 单关系查询

单关系查询比较简单，举几个简单的例子即可

1.找出所有教师的名字

select name 
from instructor;
数据库系统概念笔记——第三章：SQL

2.找出所有教师的系名

select dept_name 
from instructor;
数据库系统概念笔记——第三章：SQL

3.使用distinct关键字查找教师所在的系名

select distinct dept_name 
from instructor;
数据库系统概念笔记——第三章：SQL

4.在select中使用算数表达式

select name,salary*1.1 
from instructor;

5.找出所有在Comp. Sci.系且工资超过70000美元的老师的姓名

select name 
from instructor 
where dept_name = 'Comp. Sci.' and salary > 70000;

3.3.2 多关系查询

1.找出所有教师的姓名，所在系，和系所在的建筑

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

3.3.3 自然连接

1.找出教师的姓名和他们所教的课程标识

select name,course_id 
from instructor natural join teaches;

2.找出教师的姓名和他们所教的课程名称（三张表自然连接）

select name,title 
from instructor natural join teaches natural join course;

3.4 附加的基本运算

3.4.1 更名运算

更名运算使用as关键字

例

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

3.4.2 字符串运算

1.字符串的匹配

  • %：匹配任意字符
  • _：匹配一个字符

找出所在建筑名称中包含a的所有系名

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

3.4.4 排列元组的显示次序

1.order by，asc，desc

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

3.4.5 where子句谓词

1.使用between and简化查询

select name 
from instructor 
where salary between 90000 and 100000;

3.5 集合运算

3.5.1 并运算

找出在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);

3.5.2 交，差运算

MySQL中不支持交和差运算运算，使用intersect和except会报错

3.6 空值

  • 如果算数表达式的任意输入为空，则该算数表达式(+ , - , * , /等)的结果也为空
  • 1 < null的结果为unknown
  • and：true and unknown的结果为unknown，false and unknown的结果为false，unknown and unknown的结果是unknown
  • or：true or unknown的结果是true，false or unknown的结果是unknown，unknown or unknown的结果是unknown
  • not：not unknown的结果是unknown 
select name 
from instructor 
where salary is null;

3.7 聚集函数

3.7.1 基本聚集

1.找出历史系教师的平均工资

select avg(salary) 
from instructor 
where dept_name = 'History';

2.找出在2010年春季教授一门课程的教师总数

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

3.7.2 分组聚集

1.找出每个系的平均工资

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

3.7.3 having子句

1.找出每个系的平均工资且平均工资大于4000的系名

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

3.7.4 对空值和布尔值的聚集

除了count(*)以外的所有聚集函数均忽略输入集合中的空值

3.8 嵌套子查询

3.8.1 集合成员资格

  • 连接词in可以用于测试元组是否是集合中的成员
  • 连接词not 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);

例：找出在2009年秋季开课但不在2010年春季学期开课的所有课程

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

3.8.2 集合的比较

1.>some表示至少比某一个大

例：找出至少比Biology系的某一个教师工资要高的教师姓名

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

2.SQL也允许使用<some,<=some,>=some,=some,<>some等的使用

3.与some类似，<all,<=all,>=all,=all,<>all的功能类似

找出平均工资大于所有系平均工资的系

select dept_name 
from instructor 
group by dept_name 
having avg(salary) >=all
(select avg(salary) 
from instructor 
group by dept_name);

3.8.3 空关系测试

使用exists，当子查询非空时返回true，子查询为空时返回false

例：找出选修了Biology系开设的所有课程的学生

select S.ID,S.name 
from student as S 
where not exists(
(select course_id 
from course 
where dept_name='Biology') 
except 
(select T.course_id 
from takes as T 
where S.ID = T.ID));

exists的执行逻辑和原理要求完全掌握

3.8.6 with子句

使用with子句可以新建一张临时表

例：找出具有最大运算的系

with max_budget(value) as 
(select max(budget) 
from department) 
select budget 
from department 
where department.budget = max_budget.value;

3.9 数据库的修改

3.9.1 删除

删除语句的基本语法格式

delete 
from 表名 
where 条件

没有where时默认删除整张表

3.9.2 插入

1.最基本的插入语句

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

2.可以在insert语句中指定要插入的属性

insert into course(course_id,title,dept_name,credits)
values('CS-437','Database Systems','Comp. Sci. ',4);

3.9.3 更新

1.一般的update语句

update 表名 
set xxx 
where xxx;

2.升级的update语句

update table_name
set xxx = case
	when pred1 then result1
	when pred2 then result2
	...
	else result0
end;

3.例：给工资超过100000美元的教师涨3%的工资，其余教师涨5%

update instructor
set salary = case
	when salary > 100000 then salary*1.03
	else salary*1.05
end;
# 数据库系统笔记 数据库 mysql sql
上一篇: 数据库系统概念：CH2：关系模型
下一篇: 【系统设计与数据库系统】Advanced SQL LAB前言一、实验目的与要求：二、实验过程及内容三、数据处理分析

继续阅读