68>>SQL 语句
时间：2021-07-01 10:21:17
帮助过：5人阅读
create schema <schema_name
> authorization <username
> 没有指定schema_name时默认是用户名
删除模式
drop schema <schema_name
> <cascade | restrict>
创建表
create table student.sc ;定义表中的两个主码以及外码
(sno char(
7),
cno char(
4),
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 <列名> <数据类型> ];
创建索引
create [unique] [cluster] index <索引名
>
on <表名
>(
<列名
> [ <次序> ] [, <列名> [ <次序> ] ] ....);
删除索引
drop index <索引名
>
插入元组
insert
into <表名
> [ ( <属性列1> [, <属性列2>...] ) ]
values (
<常量1
> [, <常量2>] ... )
insert into sc(sno,cno)
values(
‘200215128‘,
‘1‘)
修改元组
update <表名
>
set <列名
> = <表达式
> [, <列名> = <表达式> ]...
[where <条件>];
update student
set sage
= 22
where sno
= ‘200215121‘
删除元组
delete
from <表名
>
[where <条件> ];
delete
from student
where sno
= ‘200215121‘
使用视图创建语句建视图，通过视图查询数据:
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 char(
7)
not null,
cno char(
4)
not null,
grade smallint not null,);
create table dept
(deptno number,
dname varchar(
9)
unique,
);
create table student
(sno char(
7)
primary key,
ssex char(
2)
check (ssex
in (
‘男‘,
‘女‘)),);
表级用户定义完整性
check (ssex
= ‘女‘ or sname
not like ‘ms.%‘ );
/*男性名字不能以ms.开头*/
完整性约束命名子句
constraint <完整性约束条件名
> [primary key短语 | foreign key 短语 | check 短语]
create stable student
(sno number(
5)
constraint c1
check (sno
between 90000 and 99999),
sname varchar(
20)
constraint c2
not null,
sage number(
3)
constraint c3
check (sage
<30),
ssex varchar(
2)
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 (
‘男‘,
‘女‘));
域中完整性限制
create domain genderdomain
char(
2)
constraint gd
check (value
in (
‘男‘,
‘女‘));
alter domain genderdomain
add constraint gd
check (value
in (
‘1‘,
‘0‘));
alter domain genderdomain
drop constraint gd;
查询
seletct [ all | distinct ] <目标列表达式
> [, <目标列表达式> ]...
from <表名或视图名
> [, <表名或视图名> ]...
[ where <条件表达式> ]
[ group by <列名1> [ having <条件表达式> ] ]
[ order by <列名2> [ asc | desc ] ]
表单查询
select sname name,
2015-sage
year
from student
where sno
= 200215121
select sname, sdept, sage
from student
where sage
between 20 and 23 /*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
where cno
= ‘3‘
order by grade
desc,sno
select cno,
count(
distinct sno )
from sc
group by cno
连接查询，嵌套查询
select sname
from student
where sdept
= ‘cs‘ and sage
< 20 /*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
where cno
= ‘2‘ );
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
where sage
<=19;
select *
from student
where sdept
=‘cs‘
intersect
select *
from student
where sage
<=19;
select *
from student
where sdept
=‘cs‘
except
select *
from student
where sage
<=19;
数据类型
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 char(
4)
primary key,
SNAME char(
10),
STATUS smallint,
CITY char(
12))
insert into s
values(
‘S1‘,
‘精益‘,
‘20‘,
‘天津‘);
insert into s
values(
‘S2‘,
‘盛锡‘,
‘10‘,
‘北京‘);
Create table p
(PNO char(
4)
primary key,
PNAME char(
10),
COLOR char(
4),
WEIGHT smallint)
insert into P
values(
‘p1‘,
‘螺母‘,
‘红‘,
‘12‘);
insert into P
values(
‘p2‘,
‘螺栓‘,
‘绿‘,
‘17‘);
insert into P
Create table J
(JNO char(
4)
primary key,
PNAME char(
10),
CITY char(
10))
insert into J
values(
‘J1‘,
‘三建‘,
‘北京‘);
insert into J
values(
‘J2‘,
‘一汽‘,
‘长春‘);
insert into J
Create table SPJ
(SNO char(
2),
PNO char(
2),
JNO char(
2),
QTY smallint)
insert into SPJ
values(
‘S1‘,
‘P1‘,
‘J1‘,
‘200‘);
insert into SPJ
values(
‘S1‘,
‘P1‘,
‘J3‘,
‘100‘);
68>>SQL 语句
标签：
