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

2021/4/23 19:29:02

本文主要是介绍数据库系统概念笔记——第三章: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语句(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关键字给约束命名
在这里插入图片描述
在这里插入图片描述
2.表空间(tablespace)

表空间是数据库的逻辑划分,一个表空间只能属于一个数据库,以一个数据库可以拥有多个表空间
表空间分为系统表空间和用户表空间
一个表空间可以包含多张表,一张表只能属于一个表空间
在这里插入图片描述
3.表的属主Owner

每个数据库对象都有自己的属主,表也有自己的属主。数据库对象默认属主是创建该对象的用户
属主拥有表的所有权限
在这里插入图片描述
属主为user1

4.修改表的定义alert

格式:
在这里插入图片描述
例:
在这里插入图片描述
在这里插入图片描述
5.删除表中的属性或约束
在这里插入图片描述
在这里插入图片描述
删除表中的约束
在这里插入图片描述
在这里插入图片描述

3.3 SQL查询的基本结构

3.3.1 单关系查询

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

1.找出所有教师的名字

select name 
from instructor;


2.找出所有教师的系名

select dept_name 
from instructor;


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

select distinct dept_name 
from instructor;

在这里插入图片描述
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;


这篇关于数据库系统概念笔记——第三章:SQL的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程