Oracle的Sql语句进阶

发布时间:2022-12-19 08:00

废话不多说直接进正题

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题答案不低,有待后期更正!!

ItVuer - 免责声明 - 关于我们 - 联系我们

本网站信息来源于互联网,如有侵权请联系:561261067@qq.com

桂ICP备16001015号