创建模式
create schema <schema_name> authorization <username> 没有指定schema_name时默认是用户名
删除模式
drop schema <schema_name> <cascade | restrict>
创建表
create table student.sc ;定义表中的两个主码以及外码
(sno ),
cno ),
grade smallint,
primary key(sno, cno),
foreign key sno references student(sno),
foreign key sno references course(cno)
); /*最后一行没有逗号,外码引用的必须是主码*/
修改表
alter table <table_name>
[ add <新列名> <数据类型> [完整性约束] ]
[ drop [完整性约束名] [列名] ]
[ modify <列名> <数据类型> ];
删除表
drop table <表名>;
创建索引
create [unique] [cluster] index <索引名>
on <表名>( <列名> [ <次序> ] [, <列名> [ <次序> ] ] ....);
删除索引
drop index <索引名>
插入元组
insert
into <表名> [ ( <属性列1> [, <属性列2>...] ) ]
values ( <常量1> [, <常量2>] ... )
insert into sc(sno,cno)
')
修改元组
update <表名>
set <列名> = <表达式> [, <列名> = <表达式> ]...
[where <条件>];
update student
'
删除元组
delete
from <表名>
[where <条件> ];
delete
from student
'
使用视图创建语句建视图,通过视图查询数据:
create view <视图名> [(<列名>[,<列名>]...)] ;列名要么全部指定,要么全部不指定
as
<子查询>
[with check option];
drop view <视图名>;
创建用户
create user <username> [with] [DBA | RESOURCE | CONNECT];
create user zx_root IDENTIFIED by 'xxxxx@localhost';
删除用户
drop user <username>;
授权
grant <权限> [,<权限> ]... ;all privileges, select, update, insert, delete
on <对象类型> <对象名> [,<对象类型> <对象名>]...
to <用户> [,<用户>]... ;public
[with grant option];
grant all privileges
on table student, course
to u2, u3;
grant update(sno)
on table student
to u4;
grant insert
on table sc
to u5
with grant option
回收授权
revoke <权限>[,<权限>]...
on <对象类型> <对象名> [,<对象类型> <对象名>]...
from <用户> [,<用户>]...
revoke select
on table sc
from public
创建角色
create role <rolename>
给角色授权
grant <权限> [,<权限>]...
on <对象类型> <对象名>
to <角色> [,<角色>]...
grant <角色1> [,<角色2>]...
to <角色3> [,<角色4>]...
[with admin option]
收回角色权限
revoke <权限> [,<权限>]...
on <对象类型> <对象名>
from <角色> [,<角色>]...
create role r1;
grant select, update, insert
on table student
to r1;
grant r1
to 王平,张明
revoke select
on table student
from r1;
审计
audit alert,update
on sc;
noaudit all
on sc;
实体完整性
primary key(sno,cno);
参照完整性
foreign key sno reference student(sno);
用户定义完整性
create table sc
(sno ) not null,
cno ) not null,
grade smallint not null,);
create table dept
(deptno number,
dname ) unique,
);
create table student
(sno ) primary key,
ssex ) check (ssex in ('男','女')),);
表级用户定义完整性
check (ssex = '女' or sname not like 'ms.%' ); /*男性名字不能以ms.开头*/
完整性约束命名子句
constraint <完整性约束条件名> [primary key短语 | foreign key 短语 | check 短语]
create stable student
(sno )
),
sname )
constraint c2 not null,
sage )
),
ssex )
constraint c4 check (ssex in ('男','女'),
constraint studentKey primary key(sno),
);
alter table student
drop constraint c4;
alter table student
add constraint c4 check (ssex in ('男','女'));
域中完整性限制
)
constraint gd check (value in ('男','女'));
alter domain genderdomain
'));
alter domain genderdomain
drop constraint gd;
查询
seletct [ all | distinct ] <目标列表达式> [, <目标列表达式> ]...
from <表名或视图名> [, <表名或视图名> ]...
[ where <条件表达式> ]
[ group by <列名1> [ having <条件表达式> ] ]
[ order by <列名2> [ asc | desc ] ]
表单查询
-sage year
from student
select sname, sdept, sage
from student
/*not between and*/
select sname, ssex
from student
where sdept in ('cs','ma','is') /*not in*/
select *
from student
where sno like '2002%21' /*%任意多个字符,_单个字符, [ escape '\' ] 表示'\'为换码字符,not like */
select sno,cno
from sc
where grade is null /*is not*/
select sno,grade
from sc
'
order by grade desc,sno
select cno, count( distinct sno )
from sc
group by cno
连接查询,嵌套查询
select sname
from student
/*or, not*/
select first.cno, second.cpno
from course first, course second
where first.cpno = second.cno /*<>是不等于*/
select sname
from student
where sno in
(select sno
from sc
' );
select sno,cno
from sc x
where grade >=
(select avg(grade)
from sc y
where y.sno = x.sno);
select sname,sage
from student
where sage < any
(select sage
from student
where sdept = 'cs'); /*all*/
select sname
from student
where not exists
(select *
from course
where not exists
(select *
from sc
where sno = student.sno
and cno = course.cno )); /*not exists 没有*/
集合查询:
select *
from student
where sdept='cs'
union
select *
from student
;
select *
from student
where sdept='cs'
intersect
select *
from student
;
select *
from student
where sdept='cs'
except
select *
from student
;
数据类型
char(n) 长度为n的定长字符串
varchar(n) 最大长度为n的可变字符串
int 长整形,可写作integer
smallint 短整形
numberic(p,d) 定点数,有p位数字(不包括符号,小数点)组成,小数点后有d位小数
real 取决于机器精度的浮点数
double precision 取决于机器精度的双精度浮点数
float(n) 浮点数,精度至少为n为数字
date 日期,YYYY-MM-DD
time 时间,HH:MM:SS
小问题
"=" 两边可以没有空格
实例:
create DataBase SpjDB
on (name=spj_dat,
filename='D:\Sql_Server\spj_data.mdf',
size=10mb)
log
on (name=spj_log,
filename='D:\Sql_Server\spj_log.ldf',
size=3mb)
Create table S
(SNO ) primary key,
SNAME ),
STATUS smallint,
CITY ))
insert into s
','天津');
insert into s
','北京');
Create table p
(PNO ) primary key,
PNAME ),
COLOR ),
WEIGHT smallint)
insert into P
');
insert into P
');
insert into P
Create table J
(JNO ) primary key,
PNAME ),
CITY ))
insert into J
values('J1','三建','北京');
insert into J
values('J2','一汽','长春');
insert into J
Create table SPJ
(SNO ),
PNO ),
JNO ),
QTY smallint)
insert into SPJ
');
insert into SPJ
');