ORACLE sql常用语句

数据定义

1.创建外键，主键为s表的sno
2.创建主键sno 创建主键sno,cno
3.取值不能为空并且唯一
4.创建3位整数，3位小数
5.创建表：零件表p由零件代码（pno）、零件名（pname）、颜色（color）、重量（weight）日期birth组成，p表中weight属性列的取值范围在1-50之间。
删除表
6.修改表：
添加新列
添加表级完整性约束
删除列
删除完整性约束
修改表的数据类型

7.建立唯一性（位图）索引 删除

数据查询

1.字符串匹配’DB_(n个字符）i（2个字符）
2.查询1994年1月1日以前出生的学生的姓名和专业
3.不及格的课在3门以上的学号
4.查询结果为空值
5.查询零件重量在10-13整数之间（包括10和13）的零件名和颜色。
6.查询“信息管理与信息系统”专业学生的姓名和年龄
7.查询选修了001号课程和003号课程的学生学号。
8.查询每个学生及其选修课程的情况,包括没有选修课程的学生
9.查询与“董顺”在同一个学院学习的学生
10.找出每个学生超过他选修课程平均成绩的学号和课程号
11. 查询成绩比选修了006号课程中任意一个成绩低的选课信息
12.查询成绩比选修了006号课程中所有成绩低的选课信息
13.查询没有选修001号课程的学生姓名
14. 查询学习全部课程的学生姓名。
15.查询选修了5门以上课程的学生学号和姓名。
16.查询未选修“政治经济学”课程的学生情况

17.查询选修了001号或者003号课程的学生学号。
18.查询选修了001号和003号课程的学生学号
19.查询选修了003号课程与成绩低于60分的学生选课信息的差集
20.将“计算机科学与技术”专业的学生按出生时间先后排序

答案

数据定义

完整性约束：
1.实体完整性约束：PRIMARY KEY
2.参照完整性约束：FOREIGH KEY
3.用户自定义的完整性约束：
check
unique
not null(只能用列级约束）

1.sno varchar2(40) references s(sno);

2.sno varchar2(40)not null;
constraint sno pk_s_sno primary key(sno);
sno varchar2(40)primary key;
primary key(sno,cno);

3.sno varchar2(40)not null;
constraint sno u_s_sno unique(sno);
not null只能用列级约束,unique都可

4.num number(3);
num number(3,3);

5.create table p(
pno varchar2(40) primary key;
pname varchar2(40);
color varchar2(40);
weight number check (weight<0 and weight>0) ;
birth date;
);

drop table p;
6.alter table s add sno varchar2;
alter table s drop column sno; alter table s drop(sno,cno); alter table s drop(sno);
alter table s add constraints pk_s_sno primary key(sno);
alter table s drop constraints pk_s_sno;
alter table s modify sno char(20); alter table s modify(sno char(20));

7.create unique/bitmap index ind_sno on s(sno asc,cno dasc);
drop index ind_sno;

数据查询

单表查询,连接查询，嵌套查询，集合查询
数据查询
（一）单表查询
（1）查询若干列
（2）查询若干元组

1）字符匹配
select sname from s where sname like ‘DB_%i_ _’ ESCAPE ';

DB_…i…
通配符：
% 长度为0-n任意字符
_ 单个任意字符
当出现通配符%_本身，用ESCAPE定义换码字符,格式为末尾加ESCAPE’\’
2）空值
is null 不能用= null代替

3）多重条件
and优先级高于or,可以使用（）改变优先级

（3）使用集函数
count（distinct|all sno）
sum
avg
max
min

（1）对查询结果进行分组
group by
+排序
order by x asc|desc 缺省值为升序
+消除取值重复的行
+查询条件

1.select sname from s where sname (not) like ‘DB_%i__’ESCAPE’\’;
2.select sname,major from s where birth>to_date(‘1994-1-1’,’yyyy-mm-dd’);

3.select sno from sc where grade<60 group by sno having count(sno)>3
将查询结果分组后，集函数分别作用于每个组

having是对分的组进行筛选，作用于查询结果，最终输出满足条件的组，在分组之后进行
where 作用于基本表或视图，从中选出满足条件的元组，在分组之前进行
select列表项中出现的列必须全部出现在group by后面(聚合函数除外)

未对查询结果分组，集函数将作用于整个查询结果。
对查询结果分组后，集函数将分别作用于每个组。

4.select sno from s where sno is null
5.select pname from p where weight (not) in(10,11,12,13);
btween 10 and 20

6.select sname,2021-to_char(birth,‘yyyy’)sage from S where major =‘信息管理与信息系统’;
2021-to_char(birth,’yyyy’)sage
to_date(‘2021412’，‘yyyymdd’)+{空格+别名};
to_date(‘2021/4/12’，‘yyyy/mm/dd’);
hh24 mi ss
to_char(‘2021/04/11’,’yyyy’);
to_char(sysdate,’yyyy’)系统日期

连接查询：①等值与非等值连接查询 笛卡尔积与连接
select * from s,sc 只是做了广义卡尔积，需要加入连接条件
select * from s,sc where s.sno=sc.sno

②自身连接查询
7.select a.sno from sc a,sc b where a.sno=b.sno and a.cno='001’and b.cno=‘003’;
③外连接查询
8.select * from s left outer join sc on(s.cno=sc.cno)
④复合条件链接查询

嵌套查询:①使用IN谓词的不相关子查询,也可用自身连接实现
9.select sname from s where college in(select college from s where sname=’董顺’）
select sname from s where college =（select college from s where sname=’董顺’）
②当子查询查询结果为单值，可使用带比较运算符的子查询

10.select sno,cno from sc a where grade>(select avg(grade) from sc b where a.sno=b.sno)
③带有ALL,ANY谓词的子查询
11.select * from sc where grade<any(select grade from sc where sno=’006’)
12.select * from sc where grade<all(select grade from sc where sno=’006’)
④带有EXIST谓词的子查询，子查询不返回任何数据，只返回True or False，通常为相关子查询，最难，其余为不相关子查询
not只是给子查询的查询结果取反，加不加not子查询的查询结果都一样

13.select sname from s where not exists(select * from where sno=s.sno and cno=’001’)
14.
思路1：查询一个人的姓名，这个人不存在课程没选择
select sname from sc where not exists (select * from c where not exists(select * from sc where sno=s.sno and cno=c.cno))
最内层:所有选过课的选课信息
最内层：选过课的选课信息：感觉跟sc表并无不同
中间：取反 每个学生没选过的课程
最外：取反 没有没选过的课=全选
集合查询 各结果表的列数必须相同；对应项的数据类型也必须相同。

思路2：查询一个学生的姓名，该学生选修课的门数等于所有课程的门数
select sname from sc group by sno having count(cno) = (select count(cno) from c)
15.
select sno,sname from s,sc where s.sno=sc.sno group by s.sno,sname having count(cno)>5

select sno,sname from s, sc where s.sno=sc.sno where exists(select * from sc group by sno group by sno having count(cno)>5)

16.select sname from s where sno not in(select sno from sc where cno in(select cname=’政治经济学’))

①并
17.select sno from sc where sno=’001’union select sno from sc where sno=’003’a
select distinct sno from sc where cno=’001’ or cno=’003’

②交
18.select sno from sc where cno=’001’ intersect select sno from sc where cno=’003’
select sno from sc where cno=’001’ and sno in(select sno from sc where sno=’003)
select a.sno from sc a,sc b where a.sno=b.sno and a.cno=’001’ and b.sno=’003’
③差
19.select * from sc where cno=’003’ minus select * from sc where grade <60
20.select sname,birth from S where major=‘计算机科学与技术’ order by birth (desc|asc);