1、基本类型
char(n)固定长度字符串;varchar(n) 可变长度字符串;int:整数类型;smallint:小整数类型;numeric(p,d)共p位,d位小数,numeric(3,1)44.5;float(n)精度至少为n位的浮点数
2、创建表
create table 表名
(//属性名 基本类型,
dept_name varchar(20),
building varchar(15),
budget numeric(12,2),
primary key(dept_name)); //主码的声明可选
//还可以添加foreign key(Ak1,Ak2,...,Akn) references xxx 表明在该表中的属性(Ak1,Ak2,Ak3,...Akn)的取值必须对应于xxx在主码属性上的取值,该表中属性的值必须是另外一个表中的主码的取值
3、增
//最简单
insert into 表名 values(值1,值2,...,值n); //增加元组insert into instructor values(10211 'Smith', 'Biology', 66000);
//可以指定属性
insert into 表名(属性1,属性2,...属性n)
values(值1,值2,...值n); //可以先列出属性
//可以在查询结果的基础上插入元组
insert into instructor
select ID,name,dept_name,18000
from student
where dept_name = 'Music' and tot_cred > 144;
alter table r add A D; //增加属性,在表r中增加属性A,域为D
// alter table instructor gender numeric(1,0);
4、删
delete from r; //删除r中所有的元组
delete from r
where P; //把满足P的所有元组都删除
drop table 表名; //删除数据库中的某个表
alter table r drop A; //从表r中取出属性A
5、改
方法1:
//对所有元组的属性的值进行更新
update 表名
set 属性 = 新的值
方法2:
//对满足条件P的元组进行更新,先检查关系中的所有元组,然后才执行更新
update 表名
set 属性 = 新的值
where P;
方法3:
//对满足不同条件的元组执行不同的更新
update 表名
set 属性 = case
when 条件1 then 值1
when 条件2 then 值2
when 条件3 then 值3
when 条件4 then 值4
...
else 值n
end
6、查
1、from对某几个表做笛卡尔积运算,然后where作为筛选条件把满足条件的元组保留下来,然后用select从表中选择自己想要的列。(distinct 可以去除重复)
where子句中可以使用and、or、not,from其实就是一个多层循环,方法体就是将每个表的元组组合成一个新的元组。
先看涉及到哪几张表,然后where作条件判断(=等于 <>不等于 <小于 <=小于等于 >大于 >=大于等于 BETWEEN)。
2、自然连接
from形成笛卡尔积,只有那些共同属性相同的元组才会被保留下来。通常是实体和关系之间的笛卡尔积。 select name, course_id from instructor natural join teaches;
格式:from r1 natural join r2 natural join r3 natural r4... natural join rm;
from就只负责产生笛卡尔积即可,判断逻辑尽量都放在where里面
3、重命名
3.1、属性重命名old_name as new_name
select salary * 10 as new_salary
3.2、可以把一个长的表名换成短的表名
from instructor as T, teaches as S //除非有数字运算才替换
from instructor as T, instructor as S; //只有是自己和自己比较,需要给表起别名
4、 * 表示所有的属性
5、将查询结果按次序显示
where子句后面接order by子句,order by 属性名; //属性可以是字符串、数字,默认升序排列
asc表示升序,desc表示降序,order by salary desc,name asc;先按salary降序排序,再按name升序排序
6、between a and b; //在a和b之间
select name
from instructor
where salary between 1000 and 2000; // 大于等于 1000 小于等于 2000
通配符
%的意思是任意字符出现任意次数
where 属性 like 'xxx'
以指定字符串开头 where 属性 like 'abc%'
包含指定字符串 where 属性 like '%abc%'
_下划线匹配单个任意字符
7、多个比较的简写
(a1, a2) <= (b1, b2) //当a1<=b1且a2<=b2时成立
8、集合运算(对两个查询的查询结果进行运算,会自动去重)
并运算,union,对两个查询结果进行并,会自动去重
交运算,intersect,对两个查询结果进行交,
差运算,except,在前一个查询结果中取出后一个查询结果
9、聚集函数
avg、min、max、sum、count
对查询后的结果的某一个属性中的所有值进行聚集
select sum(salary) select avg(salary) as avg_salary
select count(*) // 计算元组的个数
10、对查询结果分组(group by)后聚集
分组后,处理的是每个分组,所以select 后面要么是group by后面的属性,要么是聚集函数
11、对查询结果分组后的筛选 having子句(where针对的是元组,having针对分组)
having后面要么是出现在group by后面的属性,要么是聚集函数
12、嵌套子查询(很好用)(where筛选就是选择符合条件的属性,所以可以把条件转化为子查询)
相当于多层循环,外层循环每次取出一个元组,然后执行内层循环(子查询),再根据子查询结果和外层元祖做判断。
for( from 笛卡尔积后产生的集合中的每一个元组)
where {
//执行内层子查询
//对元组的每个属性进行判断
}
①集合成员资格
in测试元组是否为集合中的成员,先利用查询语句获取一个集合,然后可以作为筛选条件,用在where中。not in 用来测试是否不在是某一个集合内。
先找到某一些条件所在的集合,然后in 或者 not in该集合作为筛选条件
例子:查询在2009年秋季和2010年春季学期同时开课的所有课程
select course_id
from section
where semester = 'Spring' and year = 2010 and
course_id in ( select course_id
from section
where semester = 'Spring' and year = 2009);
select distinct name
from instructor
where name not in ('Mozart', 'Einstein')
②集合的比较
至少比其中一个大 > some
例如: select T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology'
等同于
from instructor
where salary > some( select salar
from instructor
where dept_name = 'Biology')
> some比某一个大,<some() >=some() <=some() =some() <>some()
比所有的都大 > all() <= all() >=all() =all() <all() <>all()(比所有的都大或者都小)
③空关系测试
exists测试关系中是否存在元组(包含元组时为true),not exists测试关系中是不不包含元祖(不包含元祖时为true)
我们可以利用not exists来表达“关系A包含关系B”:not exists(B except A)
例子:找出选修了Biology系开设的所有课程的学生
即该学生选修的课程包含了Biology系开设的所有课程
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 T.ID = S.ID));
④重复元组存在性测试
利用unique可以测试在一个子查询的结果中是否存在重复元组
例如:找出所有在2009年最多开设一次的课程
from course as T
where unique( select S.course_id
from section as S
where T.course_id = S.course_id and S.year = 2009);
⑤from子句嵌套子查询
⑥with子句嵌套子查询
with子句可以定义临时关系
例如找出具有最大预算的系:
with max_budget(value) as
(select max(budget)
from department)
select budget
from department,max_budget
where department.budget = max_budget.value;