数据库系统（三）SQL语句全解析
Published in：2023-12-03 | category： 数据库系统

1 SQL表内类型

char(n) 固定长度n的字符

varchar（n） 最大长度n的可变字符

比如我char(20) 的一个元素值为“vs”则占用20字节，如果varchar（20）的一个元素值为”vs”，则占用字节为2

int 整形

smallint 小整形

numeric（p,d ） 固定小数点位数，特定精度 如 numeric(3,1) 支持 44.5

float

double

2 SQL增删改语句

2.1 创建表 create

语法

注意不要丢掉分号

示例：

1
2
3
4
5
6
create table department(
	dept_name varchar(20),
	building varchar(15),
	budget numeric (12,2),
	primary key(dept_name)
);

指定主键

primary key(dept_name)

指定非空属性

dept_name varchar(20) not null

制定外键

foreign key (name) references instructor

2.2 删除表 drop

1
drop table r;

删除原本表

2.3 表中添加属性 alter… add

1
alter table r add A D;

为表添加A D属性

2.4 添加新的元组信息 insert…into…

1
insert into instructors values("10211","Simith","Biology")

2.5 删除表所有元组 delete from …

1
delete from instructor

删除元组，但表还在

2.6 更改元组 update

示例：给收入低于 70000 的教师加薪 5%

1
2
3
4
update instructor
set salary = salary * 1.05
where salary < 70000;

3 查询语句

查询结构

select 属性

from 表

where 谓词条件

多个表的时候，需要加表名.属性区分

1
2
3
4
select name, instructor.dept_name, building
from instructor, department
where instructor.dept_name= department.dept_name;

注意

1 SQL语句大小写不敏感 name=NAME

2 选择结果可能会包含重复的结果

使用distinct去除重复

1
2
select distinct dept_name
from instructor;

3 使用*查询全部

1
select * from instructor;

4 选择中可以包括运算表达式

1
2
3
select ID, name, salary/12 as monthly_salary  
from instructor;

表示选择工资除以12后选择出来

5 where谓词

1
2
3
4
5
6
7
8
select name
from instructor
where dept_name = 'Comp. Sci.'  and salary > 70000;

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

4 重命名

4.1 为什么用

as

1 在相同的关系中比较元组

示例：查找 Comp.Sci 部门中薪水高于至少一名讲师的所有讲师的姓名。

1
2
3
4
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Comp. Sci';

2 将长的名字改短

1
2
3
4
select T.name, S.course_id
from instructor as T, teaches as S
where T.ID= S.ID;

3 结果属性修改名称

1
2
3
4
select T.name as instructor_name, S.course_id
from instructor as T, teaches as S
where T.ID= S.ID;

5 字符操作

5.1 寻找匹配

like

% 子字符串匹配

__ 匹配任何字符串

|| 运算符用于附加两个字符串

举例

1
2
3
4
5
6
'Intro%' matches any string beginning with “Intro”.
'%Comp%' matches any string containing “Comp” as a substring.
'_ _ _' matches any string of exactly three characters.
'_ _ _ %' matches any string of at least three characters.


注意

1 大小写敏感

2 转义字符区分%

Match the string “100%”
like ‘100 %‘

5.2 function

Trim用来 移除字符串后面的空格

6 生序降序

order关键字

desc 降序

asc升序

示例1：按字母顺序列出物理系的所有教师

1
2
3
4
5
select name
from instructor
where dept_name = 'Physics'
order by name;

示例2：按薪水和姓名列出讲师

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

between

1
2
3
4
select name 
from instructor
where salary between 90000 and 100000

等价于

1
2
3
4
select name
from instructor
where salary <= 100000 and salary >= 90000;

7 联合操作

7.1 并集Union

可以直接自动消除重复，不像选择子句。

例子1 ：查找 2017 年秋季或 2018 年春季开设的课程

1
2
3
4
5
6
7
8
select course_id  
 from section
 where semester = 'Fall' and year = 2017
 union
select course_id  
 from section
 where semester = 'Spring' and year = 2018;

如果我们想要保留所有重复值

用union all

1
2
3
4
5
6
7
8
select course_id  
 from section
 where semester = 'Fall' and year = 2017
 union all 
select course_id  
 from section
 where semester = 'Spring' and year = 2018;

7.2 交集 INTERSECT

1
2
3
4
5
6
7
8
select course_id  
 from section
 where semester = 'Fall' and year = 2017
intersect
select course_id  
 from section
 where semester = 'Spring' and year = 2018;

7.3 差集 EXCEPT

1
2
3
4
5
6
7
8
select course_id  
 from section
 where semester = 'Fall' and year = 2017
except
select course_id  
 from section
 where semester = 'Spring' and year = 2018;

7.4 对于空值补充

1 任何包含Null的表达式返回为Null

例：

1
5 + null  returns null

2 任何包含Null的比较返回的值是未知的

例 

1
5 < null  or  null <> null   or   null = null  returns unknown

3 对于布尔运算

1
2
3
4
5
6
7
and : (true and unknown)  = unknown,  
	  (false and unknown) = false,
      (unknown and unknown) = unknown
or: (unknown or true)   = true,
	 unknown or false)  = unknown
    (unknown or unknown) = unknown
not:  =unknown

空值，未知检测

示例：查找工资为空的所有教师。

1
2
3
select name
from instructor
where salary is null/ unknown

都是用is的，注意不能用=来替代is 在null的情况下

8 聚合函数

Average: avg
Minimum: min
Maximum: max
Total: sum
Count: count

示例：查找计算机科学系教师的平均工资

1
2
3
4
select avg (salary) as avg_salary
from instructor
where dept_name = 'Comp. Sci.';

8.1 group by

group by是一个限定函数，意味着在这种情况下，限定在哪里的一个范围内进行比较寻找

比如下面的例子，要找到每一个部门的平均值

示例:查找每个部门讲师的平均工资

1
2
3
4
select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name;

8.2 having

having相当于要加一个限定条件

示例：查找平均工资大于42000的所有部门的名称和平均工资

having相当于就是给Group加一个条件，比如在这个范围内大于42000的

1
2
3
4
5
select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name
having avg (salary) > 42000;

9 Nested Subqueries 嵌套子查询

9.1 Set Membership

in 和not in

示例： 查找所有姓名为“莫扎特”或“爱因斯坦”的教师

1
2
3
select distinct name
from instructor
where name in("Mozart","Einstein")

示例：查找已参加 ID 为 10101 的教师教授的课程部分的（不同）学生总数

1
2
3
4
5
6
select count (distinct ID)
from takes
where (course_id, sec_id, semester, year) in  (select course_id, sec_id, semester, year
                                 from teaches
                                 where teaches.ID= 10101);

9.2 Set Comparison

SQL also allows < some, <= some, >= some, = some, and <> some
定义： y < some (X) : y小于X中任一元素，则结果为true

比较

some 只要一个满足即可

image-20231211114912021

all 所有满足

image-20231211114933982

9.3 exist or not exists

exists 它的作用是检查子查询是否返回结果。如果子查询返回至少一行数据（即至少存在一个满足条件的匹配项），那么”EXISTS”返回TRUE，否则返回FALSE。

示例: 查找学过生物系所有课程的学生（ID、姓名）。

1
2
3
4
5
6
7
8
9
10
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));

9.4 with as

临时的关系，一会儿的查询用到

示例: 查找预算最高的所有部门

1
2
3
4
5
6
with max_budget (value) as       
	(select max(budget)
    from department)
select department.name
from department, max_budget
where department.budget = max_budget.value;

题目

答案在最后

1 This Query can be replaced by which one of the following?

1
2
3
SELECT name, course_id
FROM instructor, teaches
WHERE instructor_ID= teaches_ID;

a) Select name,course_id from teaches,instructor where instructor_id=course_id;
b) Select name, course_id from instructor natural join teaches;
c) Select name, course_id from instructor;
d) Select course_id from instructor join teaches;

2 In the SQL given above there is an error . Identify the error.

1
SELECT * FROM employee WHERE dept_name="Comp Sci";

a) Dept_name
b) Employee
c) “Comp Sci”
d) From

Explanation: For any string operations single quoted(‘) must be used to enclose.

3 Which one of the following has to be added into the blank to select the dept_name which has Computer Science as its ending string?
a) %
b) _
c) ||
d) $

4 If we want to retain all duplicates, we must write ________ in place of union.
a) Union all
b) Union some
c) Intersect all
d) Intersect some

5 _____ clause is an additional filter that is applied to the result.
a) Select
b) Group-by
c) Having
d) Order by

6 A _____ indicates an absent value that may exist but be unknown or that may not exist at all.
a) Empty tuple
b) New value
c) Null value
d) Old value

7 Using the ______ clause retains only one copy of such identical tuples.
a) Null
b) Unique
c) Not null
d) Distinct

8 In an employee table to include the attributes whose value always have some value which of the following constraint must be used?
a) Null
b) Not null
c) Unique
d) Distinct

9 If the attribute phone number is included in the relation all the values need not be entered into the phone number column. This type of entry is given as
a) 0
b) –
c) Null
d) Empty space

10 Aggregate functions are functions that take a ___________ as input and return a single value.
a) Collection of values
b) Single value
c) Aggregate value
d) Both Collection of values & Single value

11 

1
2
3
SELECT __________
FROM instructor
WHERE dept name= ’Comp. Sci.’;

Which of the following should be used to find the mean of the salary ?
a) Mean(salary)
b) Avg(salary)
c) Sum(salary)
d) Count(salary)

12 All aggregate functions except _____ ignore null values in their input collection.
a) Count(attribute)
b) Count(*)
c) Avg
d) Sum

13

A Boolean data type that can take values true, false, and________
a) 1
b) 0
c) Null
d) Unknown

14

Which of the following is used to find all courses taught in both the Fall 2009 semester and in the Spring 2010 semester .
a)

1
2
3
4
5
6
7
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);

b)

1
2
3
4
5
SELECT name
FROM instructor
WHERE salary > SOME (SELECT salary
FROM instructor
WHERE dept name = ’Biology’);

c)

1
2
3
4
5
SELECT COUNT (DISTINCT ID)
FROM takes
WHERE (course id, sec id, semester, YEAR) IN (SELECT course id, sec id, semester, YEAR
FROM teaches
WHERE teaches.ID= 10101);

d)

1
2
3
(SELECT course id
FROM SECTION
WHERE semester = ’Spring’ AND YEAR= 2010)

15

1
2
3
4
SELECT dept_name, ID, avg (salary)
FROM instructor
GROUP BY dept_name;
This statement IS erroneous because

a) Avg(salary) should not be selected
b) Dept_id should not be used in group by clause
c) Misplaced group by clause
d) Group by clause is not valid in this query

答案

1-5 B C A A C 6-10 C D B C

11- 15 B B D A B

对exit比较模糊

从Nested -2 开始只做后五道题

Prev:
嵌入式系统（三）嵌入式中断
Next:
嵌入式系统实验-GPIO实验

    • Playing with cute fish