03SQL
来源:互联网 发布:组态王软件代理 编辑:程序博客网 时间:2024/06/03 22:49
SQL组成
- 数据定义语言DDL
- 数据操纵语言DML
- 完整性
- 视图定义
- 事务控制
- 嵌入式SQL和动态SQL
- 授权
SQL数据定义语言(DDL)允许指定关系的信息
- 每个关系的模式
- 每个属性的取值类型
- 完整性约束
- 每个关系维护的索引集合
- 每个关系的安全性和授权信息
- 每个关系在磁盘上的物理存储结构
SQL基本类型
char(n)
:固定长度的字符串,会自动追加空格varchar(n)
:可变长度的字符串,最大长度为nint
:整数类型smallint
:小整数类型numeric(p,d)
:定点数,p位数字(加上一个符号位),d位小数部分real
,double precision
:浮点数float(n)
:精度至少位n的浮点数
创建表
create table r (A1 D1, A2 D2, ..., An Dn, (integrity-constraint1),..., (integrity-constraintk));
- 完整性约束
not null
:不允许空值primary key (A1, ..., An)
:主码,自动不允许空值foreign key (A1, ..., An) references r
:参照r的外码
删除表
删除整个表
drop table r;
清空表
delete from r;
- 配合where可以删除指定元组
插入元组
insert into r values (a1, a2, … , a_n);
insert into r on (A1, A2, … , An) values (a1, a2, … , an);
- 如果插入的元组中只给出了部分属性的值,其余属性将赋空值
更改属性
update rset a1 = b1, … ,an = bnwhere P
在set中使用case … when可以执行多条更新语句
casewhen p1 then result1when p1 then result1…when pn then resultnend
增加属性
alter table r add A;
删除属性
alter table r drop A;
查询
select A1, A2, … , Amfrom r1, r2, …, rmwhere P;
- 大小写不敏感
- 默认不去重
- 使用
select distinct
显式指明去重 select *
会返回所有属性- select子句允许带
+
,-
,*
,/
运算符 from
子句会对所有的关系进行笛卡尔积where
子句可以使用逻辑连词and
,or
,not
,比较运算符<
,>
,<=
,>=
,=
,<>
SQL查询的一种理解
1. 为from
子句中列出的关系产生笛卡尔积
2. 在1的结果上应用where
子句中指定的谓词
3. 对2的结果中的每个元组,输出select
子句中指定的属性
实际会通过(尽可能)只产生满足where子句谓词的笛卡尔积的元素进行优化执行
自然连接(natural join)
select A1, A2, … , Amfrom r1 natural join r2 natural join … natural join rmwhere P;
- 只考虑在多个关系模式中都出现的属性上取值相同的元组对
- 危险:关系模式中相同名称的属性可能不相关,当不相等的时候不会被选择
- 解决方法
- 使用from r1 join r1 where r1.A1 = r2.A1 代替 from r1 natural join r1
- 使用from r1 join r2 using A1 代替 from r1 natural join r2
更名
select A1, A2, … , Am as B1, B2, … , Bmfrom r1, r2, …, rmwhere P;
select A1, A2, … , Amfrom r1 as s1, r2 as s2, …, rn as snwhere P;
- as 可以省略
- 更名原因
- 把长的关系名替换成短的
- 为了适用于需要比较同一个关系中的元组的情况
- 被用来重命名关系的标识符被叫做相关名称、表别名、相关变量、元组变量
字符串运算
select namefrom instructorwhere name like '%ab\%cd%' escape '\';
- 使用一对单引号’表示字符串,用两个”表示字符串中的单引号
- 字符串的相等运算大小写敏感
- like的模式匹配
%
——匹配任意子串_
——匹配任意一个字符escape
转义字符
排列元组显示次序
select *from instructor order by salary desc, name asc;
- 默认升序(
asc
),使用desc
可以表示降序
where子句谓词
select name from instructorwhere salary between 90000 and 100000;
select name, course_idfrom instructor, teacheswhere (instructor.ID, dept_name) = (teaches.ID, 'Biology');
集合运算
并
(select course_id from section where sem = ‘Fall’ and year = 2009) union(select course_id from section where sem = ‘Spring’ and year = 2010);
- 自动去除重复,保留重复使用
union all
代替union
交
(select course_id from section where sem = ‘Fall’ and year = 2009) intersect(select course_id from section where sem = ‘Spring’ and year = 2010);
- 自动去除重复,保留重复使用
intersect all
代替intersect
差
(select course_id from section where sem = ‘Fall’ and year = 2009) except(select course_id from section where sem = ‘Spring’ and year = 2010);
- 自动去除重复,保留重复使用
except all
代替except
空值
- 算术运算:算术表达式的任一输入为空(null),该算术表达式(+, -, *, /)的结果为空(null)
- 比较运算:涉及空值的任何比较运算结果视为unknown
- 布尔运算:顺序为
- true-unknown-false
- true and unknown = unknown
- unknown and unknown = unknown
- false and unknown = false
- true or unknown = true
- unknown or unknown = unknown
- false or unknown = unknown
- not unknown = unknown
- 判断空:
A is null
当A为null的时候为真,A is unknown
当A为unknown的时候为真 - 比较元组对应属性值都为非空且相等或者都为空,认为属性值是相同的
- 谓词中
null = null
返回unknown
聚集函数(Aggregate Functions)
- 聚集函数自动忽略空值,如果没有非空元素,除了count返回0,其它返回null
求和
select sum (salary)from instructorwhere dept_name='Comp. Sci.';
平均数
select avg (salary)from instructorwhere dept_name='Comp. Sci.';
计数
select count (*)from course;
分组
select dept_name, avg (salary) from instructorgroup by dept_name;
- 出现在
select
语句中但没有被聚集的属性必须出现在group by
子句
having子句
select dept_name, avg (salary) from instructorgroup by dept_namehaving avg (salary) > 42000;
- 出现在
having
子句中但没有被聚集的属性必须出现在group by
子句
嵌套子查询
- 嵌套在另一个查询中的
select-from-where
表达式 where
语句中的嵌套子查询- 来自外层查询的相关名称可以用在where子句的子查询中,这个子查询叫做相关子查询
- 成员资格
select distinct course_idfrom sectionwhere semester = 'Fall' and year= 2009 andcourse_id in (select course_id from section where semester = 'Spring' and year= 2010);
集合比较
select namefrom instructorwhere salary > some (select salary from instructor where dept_name = 'Biology');
>some
表示至少比一个大,也有<some
,<=some
,>=some
,=some
,<>some
。其中=some
等价于in
select namefrom instructorwhere salary > all (select salary from instructor where dept_name = 'Biology');
>all
表示比所有都大,也有<all
,<=all
,>=all
,=all
,<>all
。其中<>all
等价于not in
空关系测试
select course_idfrom section as Swhere semester = 'Fall' and year= 2009 andexists(select * from section as T where semester = 'Spring' and year= 2010 and S.course_id= T.course_id);
exists r
等价于r不是空集
select distinct S.ID, S.namefrom student as Swhere 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));
not exists r
等价于r是空集not exists(B except A)
等价于B是A的子集
重复测试
select T.course_idfrom course as Twhere unique (select R.course_id from section as R where T.course_id= R.course_id and R.year = 2009);
- 当查询的结果中没有重复的元组,
unique
返回true
from语句中的嵌套子查询
select dept_name, avg_salaryfrom (select dept_name, avg (salary) as avg_salary from instructor group by dept_name) where avg_salary > 42000;
- 可以代替
having
子句 - from子句中使用关键词lateral作为前缀可以访问from子句中在它前面的表或者子查询的属性
with子句
with max_budget (value) as (select max(budget) from department) select budgetfrom department, max_budgetwhere department.budget = max_budget.value;
- with子句定义的是临时关系,只对包含with子句的查询有效
标量子查询
- 只返回单个属性的子查询出现在返回单个表达式能够出现的地方,这样的蛋哥子查询叫做标量子查询
select dept_name, (select count(*) from instructor where department.dept_name = instructor.dept_name) as num_instructorsfrom department;
- 可以出现在select, where, having子句中
0 0
- 03SQL
- SQL 03
- 精通SQL命令-03
- 03SQL编程
- 03、pl/sql小结
- PL/SQL 注意03
- Sql基础练习03
- SQL详讲03
- 03-region.sql
- SQL
- SQL
- SQL
- sql
- sql
- SQL
- sql
- SQL (-)
- sql
- Fiddler建好代理后,能连到手机,但手机不能上网了,求破有果
- 01引言
- 0006_20170228_【我是菜鸟】_NumberFormat虚拟类的小笔记
- 算法课第2周第1题——100. Same Tree
- 02关系模型的介绍
- 03SQL
- 04中级SQL
- 徐悲鸿赠画马车夫
- 06形式化关系查询语言
- 动态规划-波动数列
- 07数据库设计
- [LeetCode]496. Next Greater Element I(下一个更大的元素 1)
- 08关系数据库的设计
- 把BST转换为双向链表