Oracle —— 约束｜查询语句

一、表的约束

Oracle五大约束

• 主键约束：唯一且非空
• 唯一约束：唯一
• 非空约束：不能为空
• 检查约束：表中插入的数据必须符合检查的条件才能插入
• 外键约束：一张表的某一列依赖于另一张表当中的数据

1. 创建表的时候给表添加约束

（1）列级约束

create table t_student1(
	sid number(5) primary key,----主键约束
	sname varchar2(20) unique,----唯一约束
	ssex char(1) check(ssex='f' or ssex='m'),--检查约束：性别只能式f或者m
	sclass number(1) check(sclass in(1,2)),--检查约束的另一种写法
	saddr varchar2(50) not null,   ---非空约束
	---ssid  number(5) references t_student2(sid)
);
-----------------以上约束称为列级约束：约束只对某一列起作用

（2）表级约束

创建表的时候添加的约束，约束可能对多个列起作用

drop table t_student2;

create table t_student2(
	sid number(5),
	sname varchar2(20),
	ssex char(1),
	sclass number(1),
	saddr varchar2(50),
	sage number(3),
	---表级约束
	primary key（sid,sname）,
	unique(saddr),
	check(ssex='f'and sclass=1),
	foreign key(sid) references t_student1(sid)
);
---非空约束不能写成表级约束

2. 先创建表，后期添加约束

select * from user_constraints;--查看用户下面的所有约束
--默认创建表时候添加的约束，默认名：SYS_C随机编号，不好辨别到底是什么约束，可以在后期添加约束，同时给约束命名
对约束进行命名的规则：

• 主键约束：PK_表名_列名
• 唯一约束：UK_表名_列名
• 非空约束：NN_表名_列名
• 检查约束：CK_表名_列名
• 外键约束：FK_从表名_列名

创建一个没有约束的表

create table t_student2(
	sid number(5),
	sname varchar2(20),
	ssex char(1),
	sclass number(1),
	saddr varchar2(50),
	sage number(3)
);

• 语法：alter table 表名 add constraint 约束名 具体约束;

（1）主键约束

alter table t_student2 add constraint PK_t_student2_sid primary key(sid);

（2）唯一约束

alter table t_student2 add constraint UK_t_student2_sname unique(sname);

（3）检查约束

alter table t_student2 add constraint CK_t_student2_ssex check(ssex='f');

（4）外键约束

• 语法：alter table 表名 add constraint 约束名 foreign key(从表表列) references 主表名（表列）；

alter table t_score add constraint FK_t_score_sid foreign key(sid) 
references t_student1(sid); 

（5）非空约束

• 语法：alter table 表名 modify 列名 constraint 约束名 not null;

alter table t_student2 modify saddr constraint NN_t_student2_saddr not null;

（6）删除约束

• 语法：alter table 表名 drop constraint 约束名;

alter table t_student2 drop constraint NN_t_student2_saddr;

（7）重命名约束

• 语法：alter table 表名 rename constraint 旧约束名 to 新约束名;

alter table t_student2 rename constraint CK_t_student2_ssex to CK_t_student2_sex;

二、sql查询

1. 简单查询：单表查询

• 查询表中的全部记录
• 语法：select * from 表名;

select * from t_student;
select * from t_course;
select * from t_score;
select * from t_teacher;
select * from t_teachercourse;

2.带条件查询

• 语法：select * from 表名 where 条件;

从学生表中检索出2班性别为女性的学生信息
select * from t_student where ssex='f' and sclass=2;

3.查询表中的某几列

• 语法：select 列1，列2 from 表名 where 条件;

---从学生表中检索出2班性别为女性的学生的学号和姓名
select sid,sname from t_student where ssex='f' and sclass=2;

• 查询表中某几列数据的时候，可以给列起别名
  • 语法：select 列1 as 别名1，列2 as 别名2 from 表名 where 条件；(as可省略)

select sid as 学号,sname as 姓名 from t_student where ssex='f' and sclass=2;--给表列临时起别名，并没有真正给列改名
select sid 学号,sname 姓名 from t_student where ssex='f' and sclass=2;

4.查询表中不重复的记录

• 语法：select distinct 列名 from 表名;

select * from t_score;
select sid from t_score;
select distinct sid from t_score;

select score,sid from t_score;
---显示每个学生的所有成绩，学号不重复
select score,distinct sid from t_score;---报错：score有28条记录，distinct sid就四条
select distinct sid,score from t_score;

5.查询表中的数据，按一列排序

• 语法：select * from 表名 order by 列名 asc/desc;

select * from t_score;
--对t_score表按成绩排序

select * from t_score order by score;---对t_score表按照score进行升序排列
select * from t_score order by score asc;---对t_score表按照score进行升序排列,asc可以省略
select * from t_score order by score desc;---对t_score表按照score进行降序排列

6.模糊查询

• % 表示任意0个或多个字符
• _ 表示任意单个字符
• [ ] 表示括号内所列字符中的任意一个
• [^ ] 表示不在括号所列之内的单个字符
• 语法：select * from 表名 where 列名 like '值%';

--正常查询：查询学生表中名字是杨澜的学生信息；
select * from t_student where sname='杨澜';
--模糊查询：查询学生表中名字姓张的学生信息；
select * from t_student where sname like '张%';--%表示当前位置有0个或者多个字符
select * from t_student where sname like '张_';--_表示当前位置有且只有1个字符

select * from t_student;
insert into t_student values('10010','张咪','f','10-9月-1970','13012341234',2);
insert into t_student values('10011','阳光','f','10-9月-1970','13012341234',2);
--查询学生表的名字中含有“阳”字的学生信息，以下哪种是正确的？
select * from t_student where sname like '阳%';--查询名字首位是阳
select * from t_student where sname like '%阳';--查询名字末尾是阳
select * from t_student where sname like '%阳%';---对
select * from t_student where sname like '阳_';
select * from t_student where sname like '_阳';
select * from t_student where sname like '_阳_';

7.旧表生成新表

• 创建一张与t_student结构一致，数据也一致的表，作为t_student表的备份

create table tt_student as (select * from t_student);

• 创建一张与t_student表结构一致的空表

create table tt_student1 as (select * from t_student where 1=2);  
create table tt_student2 as (select * from t_student where sid='10001');  
select * from tt_student2;
drop table tt_student;

三、多表查询

1.内连接

• 把2张表中符合条件的数据筛选出来。
• 语法1：select * from 表1 join 表2 on 表1.列=表2.列;(常用)
• 语法2：select * from 表1，表2 where 表1.列=表2.列;

select t_score.sid,sname,score from t_student 
join t_score on t_student.sid=t_score.sid;

select * from t_student,t_score where t_student.sid=t_score.sid;

2.外连接

• 语法：select * from 表1 left/right/full join 表2 on 条件;

---想把t_teacher表数据都显示
select * from t_teacher left join t_teachercourse on 
t_teacher.tid=t_teachercourse.tid;
---想把t_teachercourse表数据都显示
select * from t_teacher right join t_teachercourse on 
t_teacher.tid=t_teachercourse.tid;
---想把2张表数据都显示
select * from t_teacher full join t_teachercourse on 
t_teacher.tid=t_teachercourse.tid;

3.交叉连接(笛卡尔积)

• 语法1：select * from 表1 cross join 表2;
• 语法2：select * from 表1，表2;
• 把表1中的每条记录与表2中的每条记录做笛卡尔积

select * from t_student cross join t_teacher;

4.自然连接

• 等值的内连接

select * from 表1 natural join 表2;
select * from 表1 join 表2 on 条件;

select * from 表1 join 表2 on 表1.id=表2.sid;---
select * from 表1 natural join 表2；---两张表需要有公共列，自动匹配并显示

5.自连接

• 自己与自己进行连接

--以scott用户的emp表为例去看：
select * from user_tables;
select * from emp;
select * from dept;
---自连接的应用：查询emp表中员工编号，员工姓名，上级领导编号，以及上级领导姓名
select empno 员工编号,ename 员工姓名 ,mgr 上级领导编号 from emp;

select e1.empno,e1.ename,e1.mgr,e2.ename from emp e1 join emp e2 on e1.mgr=e2.empno;

—给表起别名

select * from 表1 表别名1 join 表2 表别名2 on 条件;--给表起别名不能加as

四、分组查询

1. 聚合函数

• min()
• max()
• sum()
• avg()
• count()

select * from t_score where sid='10001';
---查询成绩表中所有学生所有科目的最低分，最高分，平均分，总分
select min(score),max(score),sum(score),avg(score) from t_score;
--------------------------------
---显示表中有sid值的记录数
select count(sid) from t_student;
---显示表中的全部记录数
select count(*) from t_student;

select * from t_student;
update t_student set stel='' where sid='10002';
select count(stel) from t_student;
select count(*) from t_student;

--查询每个学生的所有科目的最低分，最高分，平均分，总分
select score,sid,cid from t_score where sid='10003';
select sid,min(score),max(score),avg(score),sum(score) 
from t_score group by sid;

--查询每门课程的最高分，最低分，平均分
select cid,min(score),max(score),avg(score) from t_score group by cid;
--查询每个学生，每门课的最高分，最低分，平均分
select sid,cid,min(score),max(score),avg(score) from t_score group by sid,cid;

2. having关键字

• 与where关键字的区别
  • where 关键字无法与聚合函数一起使用。
  • having 子句可以让我们筛选分组后的各组数据。
• 语法：select 列名,聚合函数 from 表名 group by 列名 having 条件过滤----分组查询

---查询每个学生的平均分
select sid,avg(score) from t_score group by sid;

---查询每个学生的平均分超过60分的学生学号和成绩
select sid,avg(score) from t_score group  by sid having avg(score)>60;
select sid,score from t_score where score>60;

五、子查询

• 即嵌套的查询

1. 在where后添加子查询

-查询比张老师年龄大的教师信息
select tage from t_teacher where tname='张老师';
select * from t_teacher where tage>张老师的年龄;

select * from t_teacher where tage>
(select tage from t_teacher where tname='张老师');

--查询参加过课程编号为1的考试的学员
select sid from t_score where cid=1;  ---参加了1号课程考试的学生的学号       

select * from t_student where sid in
(select sid from t_score where cid=1); ---子表中返回多条记录，用in去查询

--查询没参加过课程编号为1的考试的学员
select * from t_student where sid not in
(select sid from t_score where cid=1); 

--查询所有已经安排教师上课的课程信息
select cid from t_teachercourse;

select * from t_course where cid in
 (select cid from t_teachercourse);

select * from t_course where exists
(select * from t_teachercourse
 where t_course.cid=t_teachercourse.cid);

2. 在from后面添加子查询

--在成绩表中查询出所有学生中平均分最低的是多少
--先求出来每个学生的所有课程的平均分
select sid,avg(score) from t_score group by sid;

select min(avgs) from (select sid,avg(score) avgs from t_score group by sid);

3. 在select语句后面添加子查询

select * from t_score;
--显示学生姓名，课程名和成绩

4. 表的内连接实现子查询

• 语法：select * from 表1 join 表2 on 表1.列=表2.列 join 表3 on 表1.列=表3.列;

select sname,cname,score from 
t_student t1 join t_score t2 on t1.sid=t2.sid  join t_course t3 on t2.cid=t3.cid;

• 子查询实现

select sname,cname,score from t_score;

select 
      (select sname from t_student where t_student.sid=t_score.sid),
      (select cname from t_course where t_course.cid=t_score.cid),
score from t_score;