SQL学习笔记

一、数据定义

1、定义模式

模式相当于一个数据空间，目录，没有具体内容

把一个模式授权给一个用户

create schema <模式名> authorization <用户名>

如果模式名为空则模式名隐含为用户名

删除模式

drop schema <模式名> <cascade(删除所有数据库对象)/restrict(只有在该模式下没有对象时执行删除)>

2、基本表的定义

create table <表名>
(<列名><数据类型>[<列级完整性约束条件>]
[,<列名><数据类型>[<列级完整性约束条件>]]
[,<表级完整性约束条件>]
);

列级完整性约束：primary key , unique
表级完整性约束：foreign key (列) references 表（列），primary key（列1，列2），unique（列）

模式与表，每个表属于一个模式
方法一：create table <模式名>.<表名>(…);
方法二：创建模式时同时创建表
方法三：set search_path to <模式名>,<表名>;

3、修改基本表

alter table <表名> 操作;

操作	语句
新加列	add <新列名><数据类型>[完整性约束]
加表级完整性约束	add<表级完整性约束>
删除列	drop <列名> [cascade(删除引用该列的其他对象)
删除完整性约束	drop constraint <完整性约束> [cascade
修改列	alter column <列名><数据类型>

删除基本表

drop table <表名> [restrict|cascade];

4、索引

加快查询速度
B+树索引，Hash散列索引

create [unqiue每个索引值只对应唯一的数据记录][cluster聚簇索引] index <索引名> on <表名>([<列名><次序asc/desc>])

删除索引

drop index <索引名>;

二、数据查询

1、单表查询

语句	含义
select	指定要显示的属性列
from	指定查询对象（基本表或视图）
where	指定查询条件
group by	对查询结果按指定列的值分组，该属性列值相同的分为一组。通常在每组中作用聚集函数
having	只有满足指定条件的组才予以输出
order by	对查询结果表按指定列的值的升序或降序排序

（1）选择表中若干列

在table中查询指定列aaa，bbb，ccc

select aaa,bbb,ccc
from table;

在table中查询全部列

select *
from table;

虚列
select子句的目标列表达式不仅可以是属性，也可以是表达式。也可以加入常量值作为新的列。
列别名
在属性后空格加别名可以改变查询结果的列标题

select 属性名1 别名1,属性名2 别名2
from table;

（2）选择表中若干元组

去重
在属性前加distinct去掉重复行

select distinct a
from table;

常用查询条件

查询条件	谓词
比较	=,<,>,<=,>=,!=,<>,!>,!<,NOT+上述比较运算符
确定范围	between and ,not between and
确定集合	in, not in <值表>（a,b,c）
字符匹配	like ,not like ‘匹配串’
空值	is null, is not null
多重条件	and,or,not（and优先级高于or）

select x
from table 
where 条件语句;

关于匹配串
可以是完整字符串，或者含有%（任意长度字符串）和_(任意单个字符)
匹配串中如果有通配符则需要转义 ‘_’ escape ‘’

（3）order by子句

可按一个或多个属性列排序
对于控制显示次序由具体系统实现决定
升序：asc；降序：desc；缺省值为升序
多个属性按出现顺序作为优先级排序

select x
from table
order by a,b asc(desc); 

（4）聚集函数

语句	含义
count(*)	统计元组个数
count([distinct/all] <列名>)	统计一列中值的个数
sum([distinct/all] <列名>)	计算一列值的和（必须为数值类型）
avg([distinct/all] <列名>)	计算一列值的平均值（必须为数值类型）
max/min([distinct/all] <列名>)	求最大值/最小值

（5）group by 子句

细化聚集函数，按指定列或多列值相等的分为一组
若没有分组，聚集函数作用于整个聚集函数
分组后作用于每个组

（6）having

条件语句
与where不同的地方在于，having作用于组，where作用于记录元组

聚集函数在条件语句中只能用having

select a
from table
group by a
having count(*)>1;

2、连接查询

一般格式：
[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>

（1）等值与非等值连接

运算符为"="

select table1.*,table2.*
from table1,table2
where table1.a=table2.b

自然连接可以通过select中去掉重复字段来实现
where可以同时支持连接和选择

（2）自身连接

通过别名来实现区分

select tablea.a,tableb.b
from table tablea,table tableb
where tablea.c=tableb.d

tablea和tableb是表table的别名

（3）外连接

select x
from tablea left/right out join tableb on
	(tablea.x=tableb.x);

左外连接左表保留右表为null
右外连接右表保留左表为null

（4）多表连接

通过and连接连接条件

3、嵌套查询

将一个查询嵌套在另一个查询中的查询称为嵌套查询
不相关查询子查询和父查询属性不相关
相关查询可用别名加条件

（1）in谓词

select a	/*父查询*/
from table1
where b in
		(select b	/*子查询*/
		from table2
		where ...
		);

一般可以通过连接查询实现

（2）比较运算符

确切知道内层查询返回单值使用比较运算符

（3）带有any或all

any和all的对象为某个查询结果，表示其中任意一个值或者全部值

any和all必须与比较运算符同时使用
any和all运算符等价于min和max聚集函数的某种形式

（4）带有exists

exist对象为查询结果，表示这个查询结果是否为空，返回真值
内存查询非空返回真值
内存查询为空返回假值
子查询目标列表达式通常用*，因为给出列名没有意义
一般用于相关查询

所有其他子查询都能用exists谓词的子查询等价替换

4、集合查询

种类	代码
并	union
交	intersect
差	except

参加操作的各查询结果列数必须相同，对应数据类型也必须相同

查询块1
操作
查询块2