数据库概论笔记第三章（SQL）

SQL

一、数据的定义

1.数据定义语言

SQL的数据定义语言能够自定义一组关系，和关系的信息（关系的模式、属性的取值类型、完整性约束、每个关系维护的索引集合、安全性和权限信息、每个关系在磁盘上的物理存储结构）

2.数据存储类型

char(n)型：固定长度的字符串，可以制定长度为n，n最大取值为255

varchar(n)型：可变长度的字符串

int：整数类型（-2³¹~2³²-1），占4个字节

smallint：小整数类型(-2¹⁵~2¹⁵-1),占两个字节

numeric(p,d)：定点数，p位数字，d位在小数点右边，数目不足补0，超过按照四舍五入保留d位

real，double precision：浮点数和双精度浮点数，精度与机器相关

float(n)：精度为至少为n位的浮点数

二、关系表的增删改查

1.关系表table的构建

r：关系名 A：r的一个属性名 D：属性A的一个域（指定A的类型和可选的约束）

在A和D后面可以接上完整性约束

2.完整性约束Integrity Constraints

not null：不为空

Primary key(A1…An)：属性A1~An构成主码，非空且唯一

3.删除drop一个关系表

不仅删除了r的元组，还删除了r的模式。（全删）

只删除所有元组，还留下一张空表

delete from r -> 类似于select

4.插入数据

a1~an可以不写，插入的属性对应的值v，要保证属性对应

5.修改Alter属性

A：待添加属性的名称 D：待添加属性的域

新加属性后的元组对应添加null

从r中删除属性A。

三、基本操作

1.查询

select A1…An (distinct/all) from r1…rn where P

A:属性 r：关系 P：查询条件

a.select：就是投影运算

distinct：删除查询后的重复结果

all是默认情况

不识别大小写。

select * from r：提取全部属性 * = 所有列

A表达式可以实现**±*/**，属性名不变，属性值进行相应的改变

b.from

对于from后面的r关系，多个关系就相当于笛卡尔积运算。

c.where

等同于选择运算。

可以使用and、not、or、<、>、<=、==、<>

可以实现**字符串、算数表达式、特殊类型（时间）**的比较

d.between

where amount between a and b

就是amount属性值大于等于a，小于等于b的条件

2.更名运算

old-name as new-name

既可以对属性进行更名，也可以对关系进行更名。

as可以进行省略，就是old-name new-name

3.字符串操作

字符串：‘aa’，如果单引号是字符串中的一部分，就是用两个单引号进行表示

可以使用like进行模式的匹配

%就是匹配任意的字符且字符长度可以为0

_表示匹配任意一个字符

street中包含子串‘main’的

不匹配notlike

使用%作为字符时，需要通过escape定义转移字符，并将转义字符位于%前（mysql可以直接使用\）

order by：通过某种属性进行排序，asc表示升序（默认），desc表示降序

先按照amount降序，然后对amount相同的通过loan升序排列

4.多重集Multiset

存在重复的集合就是多重集。

多重关系代数:

r1中元组t1有c1个拷贝，t1满足选择运算条件θ，代数中就有c1个t1拷贝副本。

对r1中元组t1的每一个拷贝，在πAr1都有一个πAt1（单个元组t1的投影）与之对应

r1中元组t1有c1个拷贝，r2中元组t2有c2个拷贝，在r1×r2的笛卡尔积运算中有c1乘c2份t1t2拷贝。

四、集合操作

union并、intersect交、except差运算，都是自动消除冗余的。

可使用union all、intersect all、except all来避免消除冗余.

r中出现m次，s中出现n次，in r union all s（m+n次），intersect（**min（m，n）**次），except（**max（0，m-n）**次）

五、聚集函数Aggregate functions

**group by:**元素值相同的将会被划分到一个组中

having：对groupby分组条件进行约束,对整组的特征进行划分

SELECT DEP,JOB,AVG(SAL)
FROM EMPL
WHERE JOB<>'M'
GROUP BY DEP,JOB
HAVING AVG(SAL) > 28000
ORDER BY 3 DESC

3指的是AVG(SAL)

一组只能显示出一行（显示必须要使用组的聚集函数）

六、NULL

使用：

where amount is null

5 + null returns null

所有带null的比较都是unknown

null is null 为真

true相当于1，false相当于0，unknown相当于0.5（与或非比较时）

所有聚集函数除了count(*)都忽略null

七、嵌套式查询Nested Subqueries

1.in

where name in (select name 
						from depositor)

2.嵌套

寻找在这个银行里有账户和loan的用户

select distinct customer_name
from borrower, loan
where borrower.loan_number = loan.loan_number and
			branch_name = 'Perryridge'  and
            (branch_name, customer_name ) in
           		 (select branch_name, customer_name
              	  from depositor, account
            	  where depositor.account_number = 
            	  account.account_number )

3.集合比较

some:某个

where asset > some
			(select asset
			from branch
			where city = 'cvbn')

all：小于全部数值。

4.exist

exist r ：r不为空
not exist r: r为空

SELECT Sname
FROM Student
Where not exist(
		SELECT *
		FROM CLASS
		WHERE NOT EXIST				//and STD.sno = '95002'
			(SELECT *
			FROM SC
			WHERE Sno = STD.sno
				and CNO = CLass.Cno)
)

选修全部课程的学生

5.Except

就是减法，集合a-集合b

选择aa地区所有支行都有账户的客户

select distinct S.customer_name
from depositor as S
where not exist(
		(select branch_name
		from branch
		where branch_city = 'aa')
			except
			(select R.branch_name
			from depositor as S,account as R
			where T.account_number = R.account_number and
					S.customer_name = T.customer_name)
		)

八、其他表达式

1.unique

是否唯一。后面跟集合。

where unique(...)

2.from

select branch_name, avg_balance
from (
	  select branch_name, avg (balance)
	  from account
	  group by branch_name )
		as branch_avg ( branch_name, avg_balance )
where avg_balance > 1200

3.with

with E(A,B) as SELECT...

可以先通过with建表

4.删除

delete from account

delete后面不能有*

5.插入

insert into account
values('aaa',1200)
或
insert into account(balance,name)
values(1200,'aaa')

values中可以是null

insert into account
select...

6.修改

update account
set b = b+1
where b>0

先判断where后判断set

update account
set balance = case 
	 when balance <= 10000 
		then balance *1.05
	 else   balance * 1.06
	end

insert入视图就是插入了视图的基本表（from的表）中，少的值为null。

向视图中插入不合条件的数据是不允许的。

九、视图

就是select后的表。

create view v as <select语句>

视图存储的是一个定义（select语句）。

用途基本与表一致。

视图上的修改无法影响到数据库的修改。

v1是由v2定义的，v1就是直接依赖于v2。

依赖于自己就是recursive。

十、关系的连接操作Joined Relation

连接操作将两个关系合并成一个关系。

通常用于from查询。

连接情况：定义两个关系中哪些元组匹配，以及连接结果中存在哪些属性

on：按照某种条件

loan inner join borrower on
		loan.loan_number = borrower.loan_number

natural：自然连接

loan natural right outer join borrower

using：使用的同名字段进行表的连接

loan full outer join borrower using (loan_number)

寻找所有拥有account或loan（not both）的顾客姓名

select customer_name
from (depositor natural full outer join borrower )
where account_number is null or loan_number is null

内连接不消掉重复列。