Oracle的Sql语句进阶

废话不多说直接进正题

1.先把今天需要的表创建好

CREATE TABLE "C##SCOTT1"."EMP" 
   (	"EMPNO" NUMBER(4,0), 
	"ENAME" VARCHAR2(10 BYTE), 
	"JOB" VARCHAR2(9 BYTE), 
	"MGR" NUMBER(4,0), 
	"HIREDATE" DATE, 
	"SAL" NUMBER(7,2), 
	"COMM" NUMBER(7,2), 
	"DEPTNO" NUMBER(2,0), 
	 CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"))

这个表是使用的oracle的自带的scott用户自带的表，可以先去解封scott用户登录即可

1.增(INSERT)

1.最简单的直接插入语句

--插入语句
INSERT INTO emp (
    empno,
    ename,
    deptno
) VALUES ('7936','ROSE','10');

2.插入时间为当前的系统时间

--插入时间  当前系统时间 sysdate
INSERT INTO emp (
    empno,
    ename,
    deptno,
    hiredate
) VALUES ('7936','ROSE','10',sysdate);

3.创建一个用于复制的表

--创建复制的表
  CREATE TABLE EMP2 
   (	"EMPNO" NUMBER(4,0), 
	"ENAME" VARCHAR2(10 BYTE), 
	"JOB" VARCHAR2(9 BYTE), 
	"MGR" NUMBER(4,0), 
	"HIREDATE" DATE, 
	"SAL" NUMBER(7,2), 
	"COMM" NUMBER(7,2), 
	"DEPTNO" NUMBER(2,0));

这个表结构是和emp表的结构是完全相同的（列数和列名对应）
然后通过sql将emp的全部数据插入到emp2表中

insert into emp2
select * from emp;

当然我们也可以在emp后面加上where条件，进行过滤复制
然后我们在创建一个表结构不完全相同的emp3表（只有emp的部分列）

CREATE TABLE EMP3
   (	"EMPNO" NUMBER(4,0), 
	"ENAME" VARCHAR2(10 BYTE), 
	"JOB" VARCHAR2(9 BYTE))

然后将emp中对应的这些列的数据插入进去，同样可以过滤添加

--执行插入
    insert into emp3(empno,ename,job)
    select empno,ename,job from emp ;

将固定好的数据写入到emp3表中

--执行插入 固定的数据 （这个emp表可以随便写，但是必须是有的，因为是固定的数据）
    insert into emp3(empno,ename,job)
    select 7800,'JASON','eat' from emp union
    select 7801,'xiaoming','cook' from emp;

通过union连接这两条数据（当然这种插入可能并不常用！）

2.改(UPDATE)

1.最简单的更改操作

--更新JACK的sal薪水为3000
update emp2 set sal=3000 where ename='JACK';

2.--更新JACK的sal薪水为4000而且奖金comm为1000

--更新JACK的sal薪水为4000而且奖金comm为1000
update emp2 set sal=4000,comm='1000' where ename='JACK';

3.--更新工资为2000以下的人的钱加500元

--更新工资为2000以下的人的钱加500元
update emp2 set sal=sal+500 where sal<2000;

3.删(DELETE)

1.删除没什么可说的，只需要注意delete后面不加 * ！！！
2.记得删除后面加上条件

delete from emp2 where empno=7935;

4.查(SELECT)

今天的重点（重中之重来了）
1.--查询全部

--查询
--全部
select * from emp;

2.--查询指定列

--查询指定列
select empno,ename from emp;

3.--not and or

select * from emp where ename='SMITH' and empno='7369';
select * from emp where ename='SMITH' or job='CLERK';

4.--将指定列作为另外的名字输出

--将指定列作为另外的名字输出
select deptno as did from emp;

5.--查询薪水从低到高分布(从高到低只需要在sal后面加上desc即可)（去除相同薪资列）

--查询薪水从低到高分布(从高到低只需要在sal后面加上desc即可)
（去除相同薪资列）
select distinct sal from emp order by sal;

6.从高到低

select distinct sal from emp order by sal desc;

7.--通过查询部门编号为20的薪资分布(distinct是去除重复数据的)

--通过查询部门编号为20的薪资分布(distinct是去除重复数据的)
select DISTINCT sal from emp where deptno='20' order by sal;

8.--统计工资低于2500以下的人的数量

--统计工资低于2500以下的人的数量
select count(sal) from emp2 where sal<2500;

9.模糊查询
	--%代表任意长度的字符

--例如匹配以J结尾，前面任意长度字符

select * from emp2 where ename like '%J';

--匹配以J开头，后面任意长度字符

select * from emp2 where ename like 'J%';

--匹配包含J的字符

select * from emp2 where ename like '%J%';

--_下划线表示一个字符（和%的使用规则一样）
--匹配J开头，后面三个字符的 JACK

select * from  emp2 where ename like 'J___';

--匹配J结尾，前面三个字符的

select * from emp2 where ename like '___J';

--匹配J前面两个字符，后面一个字符的

select * from emp2 where ename like '__J_';

10.--查询job不为空的数据

select * from emp2 where job is not null;

11.--查询job为空的数据

select * from emp2 where job is null; 

12.--查询将列是字符串的进行连接的数据(将ename和job进行连接作为新列newstr输出)

select  empno,ename||job as newstr from emp2;

13.--查询将列是字符串的进行连接的数据(将ename和job进行连接并用--隔开，
作为新列newstr输出)

select  empno,ename||'--'||job as newstr from emp2;

5.事务

1.先创建一个简单的表用于执行事务

create table t_user(
  id VARCHAR2(20),
  name VARCHAR2(20)
);

随便插入几天数据
1.然后执行以下代码

update t_user set name='xiaogang' where id='1';
SAVEPOINT mark1;

delete from t_user where id='1';
savepoint mark2;

注意了 savepoint是保存点，在执行这些代码之后
只要执行

rollback to savepoint 保存点名;

就可以回退到指定的位置

2.commit;
提交可以把在此之前的代码全部提交保存，在此之前的保存点都将失效，无法再次回退
3.ROLLBACK;
可以回退到上次commit提交的地方。

接下来来点练习题测试吧

1.测试一

表一
学生表：Student
字段名称	数据类型	说明
StudentID	Int	学生号，主键,自动增长（从1开始递增），不允许为空
StudentName	Char(50)	姓名，不允许为空
Grender	Bit	性别，0代表男,1代表女，默认0
Age	Int	年龄，默认19
Class	Char（50）	班级，只允许输入“计算机班”与“生物医学工程班”
Score	Int	成绩，>=0and<=100

表-2
StudentName	Grender	Age	Class	Score
詹三	男	20	计算机班	89
李明明	女	19	计算机班	79
张三丰	男	21	生物医学工程班	57
王昌	女	20	计算机班	50
李刚	男	18	生物医学工程班	45


请编写T-SQL语句来实现如下功能：
1、	创建数据库StudentDb
2、	创建表Student，按照表-1中的规范实现。字段命名、类型正确，主键及其约束必须	有
3、	用sql语句插入最少5条数据（插入表-2中的数据），每个班级最少2条数据
4、	查询显示所有“计算机班”性别为“1”的学生信息
5、	修改StudentName为“詹三”的学生成绩为“88”；
修改所有性别为“1”且学生成绩<60的学生年龄为“18” 
6、	删除所有“生物医学工程班”的学生信息

答案来了

--1.创建主键自增的sequence
create sequence seq_student_studentID
				start with 1
				increment by 1
				NOMAXVALUE
				cache 10;

--2.创建Student表
CREATE TABLE student(
    StudentID int default(seq_student_studentID.nextval)  CONSTRAINT pk_student_studentid primary key,
    StudentName char(50) not null,
    Grender int default(0) constraint ck_student_grender check(grender=0 or grender=1),
    Age int DEFAULT(19),
    Class Char(50) CONSTRAINT ck_student_class check(Class='计算机班' or Class='生物医学工程班'),
    Score int constraint ck_student_score check(score between 0 and 100)
);

--3.插入数据
insert into student(studentname,grender,age,class,score)values('詹三',0,20,'计算机班',89);
insert into student(studentname,grender,age,class,score)values('李明明',1,19,'计算机班',79);
insert into student(studentname,grender,age,class,score)values('张三丰',0,21,'生物医学工程班',57);
insert into student(studentname,grender,age,class,score)values('王昌',1,20,'计算机班',50);
insert into student(studentname,grender,age,class,score)values('李刚',0,18,'生物医学工程班',45);

--4.4、	查询显示所有“计算机班”性别为“1”的学生信息
select * from student where class='计算机班' and grender=1;

--5、	修改StudentName为“詹三”的学生成绩为“88”；
update student set score=88 where studentName='詹三';

--6、	删除所有“生物医学工程班”的学生信息
delete from student where class='生物医学工程班';

测试二

学生成绩信息三个表，结构如下： 
学生表：
字段名称	数据类型	说明
StudentID	Char(10)	学生号，主键,长度为10，不允许为空
StudentName	Char(50)	姓名，不允许为空
Grender	Bit	性别，0代表男,1代表女，默认0
Age	Int	年龄，默认19
班级表 	
字段名称	数据类型	说明
ClassID	Char(10)	班级Id，主键，不允许为空
ClassName	Char(50)	班级名，不允许为空

课程表：Course
字段名称	数据类型	说明
CourseID	Char(10)	课程Id，主键，不允许为空
CourseName	varChar(50)	课程名，不允许为空
成绩表：Score
字段名称	数据类型	说明
SCoreID	int	自动编号，主键，成绩记录号，不允许为空
CourseID	Char(10)	课程号，外键，不允许为空
StudentID	Char(10)	学生号，外键，不允许为空
Score	int	成绩，>=0 and <=100，默认0
请编写T-SQL语句来实现如下功能：
1、	创建数据库StudentDb，执行sql脚本实现数据的初始化（请执行“建表及数据初始	化.sql”文件编码创建表及数据添加）
2、	使用模糊查询所有班级名称包含“计算机”的学生信息（StudentID，StudentName，	Grender ，Age，ClassName）

3、	使用模糊查询所有班级名称包含“计算机”的学生，并且年龄最大的男生信息（StudentID，StudentName，Grender ，Age，ClassName）

4、	查询所有包含“计算机基础课程”的班级，并计算出各个班级的平均分以及学生总人数，并按平均分从高到低排序，需要显示的信息（班级名称、学生总人数、平均分）

5、	计算各个班级的男女比例（用百分比表示结果）

答案

--创建表
--student
create table student(
    studentID char(10) constraint key_student_studentID primary key,
    studentName char(50) not null,
    grender int default(0) constraint check_student_grender check(grender=0 or grender=1),
    age int default(19),
    classId char(10),
    className char(50) not null
);
--联合主键
 --   constraint key_student_studentIDwithClassId primary key(studentid,classid)

--course
create table course(
    courseId char(10) constraint key_course_courseid primary key,
    courseName varchar(50) not null
);
--score
create table score(
  scoreId int constraint key_score_scoreid primary key,
  courseId char(10) not null,
  studentId char(10) not null,
  score int constraint check_score_score check(score between 0 and 100),
  constraint fk_courseid foreign key(courseid)  REFERENCES course(courseid),
  constraint fk_studentid foreign key(studentid) references student(studentid)
);
--2、	使用模糊查询所有班级名称包含“计算机”的学生信息（StudentID，StudentName，Grender ，Age，ClassName）
select studentid,studentName,grender,age,classname from student where classname like '%计算机%';

--3.3、	使用模糊查询所有班级名称包含“计算机”的学生，并且年龄最大的男生信息（StudentID，StudentName，Grender ，Age，ClassName）

select studentid,studentName,grender,age,classname from student where classname like '%计算机%' and age=(select max(age) from student);


--4、	查询所有包含“计算机基础课程”的班级，并计算出各个班级的平均分以及学生总人数，并按平均分从高到低排序，需要显示的信息（班级名称、学生总人数、平均分）
select st.classname,c.coursename,avg(se.score),count(st.studentid) from student st,course c,score se where st.studentid=se.studentid and c.courseid=se.courseid and st.studentname='计算机基础';
select st.classname from student st,course c,score se where st.studentid=se.studentid and c.courseid=se.courseid and c.coursename='计算机基础课程' order by st.classname 

4和5题答案不低，有待后期更正！！