Oracle-SQL语法基础【以一个完整项目为例】

--log in
conn / as sysdba;
show user;

在这里插入图片描述

conn /as sysdba 无需密码直接可以连接

SYS用数据库的超级用户,数据库内很多重要的东西(数据字典表、内置包、静态数据字典视图等)都属于这个用户,SYS用户必须以SYSDBA身份登录。

SYSTEM是数据库内置的一个普通管理员,你手工创建的任何用户在被授予DBA角色后都跟这个用户差不多(赋予DBA角色相当于给予了一组数据操作的权限)。

相当于sys是qq群主,system是qq群管理员
sysoper身份主要用来启动、关闭数据库

--create user
select username from dba_users;
drop user robot cascade;

create user robot identified by robot;
select username FROM dba_users where username='ROBOT';

grant connect TO robot with admin option;
select * from dba_role_privs where grantee='ROBOT';

grant resource to robot;
select * from dba_role_privs where grantee='ROBOT';

conn robot/robot;

在这里插入图片描述
drop user ; 仅仅是删除用户,
drop user ×× cascade ;会删除此用户名下的所有表和视图。

create table student(
	s_no varchar2(10),
	s_name varchar2(50),
	s_sex varchar2(4),
	s_birthday date,
	s_addr varchar2(100),
	class_no varchar2(8)
);
desc student;

alter table student 
	add constraint pk_stu
	primary key(s_no);

alter table student
	modify s_name not null;

alter table student 
	add constraint c_sex
	check(s_sex in ('男','女'));

select constraint_name, constraint_type 
	from user_constraints 
	where table_name='STUDENT';

create table class(
	class_no varchar2(8),
	class_name varchar2(10),
	dept_no varchar2(5)
);

desc class;

select constraint_name, constraint_type 
	from user_constraints
	where table_name='CLASS';

alter table class
	add constraint pk_cla
	primary key(class_no);

alter table class
	modify class_name not null;

alter table class 
	add constraint uni_class_name
	unique(class_name);

select constraint_name, constraint_type 
	from user_constraints
	where table_name ='CLASS';


alter table student 
	add constraint stu_fk_class
	foreign key(class_no)
	references class(class_no);

select table_name, constraint_name, 
	constraint_type, r_constraint_name 
	from user_constraints
	where table_name in ('STUDENT', 'CLASS');

create table department(
	dept_no varchar2(5),
	dept_name varchar2(50),
	dept_addr varchar2(100)
);

desc department;

alter table department
	add constraint pk_dept
	primary key(dept_no);

alter table department
	modify dept_name not null;

alter table department 
	add constraint uni_dept_name
	unique(dept_name);

select constraint_name, constraint_type
	from user_constraints 
	where table_name='DEPARTMENT';

select column_name, constraint_name
	from user_cons_columns 
	where table_name='DEPARTMENT';

select a.column_name, a.table_name, 
	a.constraint_name, b.constraint_type 
	from user_cons_columns a, user_constraints b
	where a.table_name = b.table_name
	and a.table_name='DEPARTMENT'
	and a.constraint_name = b.constraint_name;

alter table class
	add constraint class_fk_dept
	foreign key(dept_no)
	references department(dept_no);

select a.column_name, a.table_name,
	a.constraint_name, b.constraint_type, b.r_constraint_name
	from user_cons_columns a, user_constraints b
	where a.table_name = b.table_name
	and a.table_name='CLASS'
	and a.constraint_name=b.constraint_name;

create table course(
	course_no varchar2(12),
	course_name varchar2(50)
);
desc course;

alter table course
	add constraint pk_course
	primary key(course_no);

alter table course
	modify course_name not null;

select a.column_name, a.table_name, 
	a.constraint_name, b.constraint_type
	from user_cons_columns a, user_constraints b
	where a.constraint_name=b.constraint_name
	and a.table_name='COURSE';

create table teacher(
	t_no number(8),
	t_name varchar2(50),
	t_sex varchar2(4),
	t_birthday date,
	title_no number(2),
	dept_no varchar2(5)
);
desc teacher;

alter table teacher
	add constraint pk_t
	primary key(t_no);

alter table teacher 
	modify t_name not null;

alter table teacher
	add constraint c_t_sex
	check(t_sex='男' or t_sex='女');

alter table teacher
	add constraint teacher_fk_dept
	foreign key(dept_no)
	references department(dept_no);

select a.table_name, a.column_name, 
	b.constraint_name, b.constraint_type, b.r_constraint_name
	from user_cons_columns a, user_constraints b
	where a.constraint_name=b.constraint_name
	and b.table_name='TEACHER';


create table title(
	title_no number(2),
	title_name varchar2(10)
);
desc title;

alter table title
	add constraint pk_title
	primary key(title_no);

alter table title
	modify title_name not null;

alter table title
	add constraint uni_title_name
	unique(title_name);

select a.table_name, a.column_name,
	b.constraint_name, b.constraint_type, b.r_constraint_name
	from user_cons_columns a, user_constraints b
	where a.constraint_name = b.constraint_name
	and a.table_name='TITLE';

alter table teacher
	add constraint teacher_fk_title
	foreign key(title_no)
	references title(title_no);

select a.table_name, a.column_name, 
	b.constraint_name, b.constraint_type, b.r_constraint_name
	from user_cons_columns a, user_constraints b
	where a.constraint_name = b.constraint_name
	and a.table_name = 'TEACHER';

create table choice(
	s_no varchar2(10),
	course_no varchar2(12),
	score number(4,1)
);
desc choice;

alter table choice 
	add constraint pk_choice
	primary key(s_no,course_no);

alter table choice
	add constraint choice_fk_stu
	foreign key(s_no)
	references student(s_no);

alter table choice
	add constraint choice_fk_course
	foreign key(course_no)
	references course(course_no);

alter table choice
	modify score default 0;

select a.table_name, a.column_name,
	b.constraint_name, b.constraint_type, b.r_constraint_name
	from user_cons_columns a, user_constraints b
	where a.constraint_name = b.constraint_name
	and a.table_name = 'CHOICE';

select data_default 
	from user_tab_columns 
	where table_name ='CHOICE' AND column_name = 'SCORE';

create table teaching(
	t_no number(8),
	course_no varchar2(12),
	rating number(2)
);
desc teaching;

alter table teaching
	add constraint pk_teaching primary key(t_no, course_no)
	add constraint teaching_fk_teacher 
	foreign key(t_no) references teacher(t_no)
	add constraint teaching_fk_course
	foreign key(course_no) references course(course_no);

select a.table_name, a.column_name,
	b.constraint_name, b.constraint_type, 
	b.r_constraint_name
	from user_cons_columns a, user_constraints b
	where a.constraint_name = b.constraint_name
	and a.table_name = 'CHOICE';

alter table teaching
	modify rating default 0;

select data_default 
	from user_tab_columns
	where table_name = 'TEACHING' 
	and column_name='RATING';

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
实现查寻某一列的约束类型,通过连接操作。

--verify create sequence privilege
select * from role_sys_privs 
	where role in (select granted_role from user_role_privs)
	and privilege ='CREATE SEQUENCE';

create sequence add_title_no_plan_sequence 
	minvalue 1 maxvalue 99 
	start with 1 increment by 1 cache 10;
select * from user_sequences;

select * from role_sys_privs 
	where role in (select granted_role from user_role_privs)
	and privilege ='CREATE TRIGGER';

create or replace trigger add_title_no_plan 
	before insert on title
	for each row when (new.title_no is null)
begin
	select add_title_no_plan_sequence.nextval 
	into :new.title_no from dual;
end add_teache_no_plan;
/
select trigger_name, trigger_body from user_triggers;

ORACLE触发器和new、old特殊变量
:new --为一个引用最新的列值;
:old --为一个引用以前的列值;

两个变量只有在使用了关键字 "FOR EACH ROW"时才存在
for each row 的意思是:这个触发器是行触发器。
后面的when指定了 触发条件。

-------------teacher_no increase-------------
create sequence add_teacher_no_plan_sequence 
	minvalue 1 maxvalue 99999999 
	start with 1 increment by 1 cache 10;
select * from user_sequences;

create or replace trigger add_teacher_no_plan 
	before insert on teacher
	for each row when (new.t_no is null)
begin
	select add_teacher_no_plan_sequence.nextval 
	into :new.t_no from dual;
end add_teacher_no_plan;
/
select trigger_name, trigger_body from user_triggers;
--insert
insert into department(dept_no, dept_name) 
	values('1','软件工程系');
insert into course(course_no, course_name)
	values('300715XI12R6','ORACLE');
insert into class(class_no, class_name,dept_no)
	values('17300907','软件17-1','1');
insert into student(s_no, s_name,s_sex,s_birthday,s_addr,class_no)
	values('1730090714','屈一鸣','男',to_date('2000-12-1','yyyy-mm-dd'),'黑龙江','17300907');
insert into choice(s_no,course_no,score)
	values('1730090714','300715XI12R6',70);
insert into title(title_name)
	values('助教');
insert into teacher(t_name, t_sex, t_birthday,title_no, dept_no)
	values('于谦','女',to_date('11-04-1978','dd-mm-yyyy'),1,'1');
insert into teaching(t_no, course_no)
	values(1,'300715XI12R6');
-----------------------create user with only Object Priviledge-------------------------
conn / as sysdba;

drop role m_robot;
drop user maria cascade;


select * from dba_roles;
create role m_robot;
select * from dba_roles;

select * from role_sys_privs 
	where role='M_ROBOT';
grant create session to m_robot;
select * from role_sys_privs 
	where role='M_ROBOT';

select username from dba_users;
create user maria identified by maria;
select username from dba_users;

grant m_robot to maria;
select * from dba_role_privs where grantee='MARIA';


conn robot/robot;
--rename tables
rename student to students;
rename class to classes;
rename course to courses;
rename choice to choices;
rename department to departments;
rename teacher to teachers;
rename teaching to teachings;
rename title to titles;
select table_name from user_tables;
--add new columns for students
alter table robot.students add monitor_no varchar2(10);
desc robot.students;

alter table robot.students 
	add constraint stu_fk_stu
	foreign key(monitor_no)
	references robot.students(s_no);
select a.table_name, a.column_name,
	b.constraint_name, b.constraint_type, b.r_constraint_name
	from all_cons_columns a, all_constraints b
	where a.constraint_name = b.constraint_name
	and a.table_name = 'STUDENTS' 
	and a.column_name = 'MONITOR_NO';
--add new columns for teachers
alter table robot.teachers add director_no number(8);
desc robot.teachers;

alter table robot.teachers
	add constraint t_fk_t
	foreign key(director_no)
	references robot.teachers(t_no);
select a.table_name, a.column_name,
	a.constraint_name, b.constraint_type, b.r_constraint_name
	from all_cons_columns a, all_constraints b
	where a.constraint_name = b.constraint_name
	and a.table_name = 'TEACHERS'
	and a.column_name = 'DIRECTOR_NO';
--copy data
update teachings set s_no= '1730090714', score=70;


select constraint_name 
	from all_constraints 
	where table_name='TEACHINGS'
	and constraint_type='P';
alter table robot.teachings 
	drop constraint PK_TEACHING;

alter table teachings
	add constraint pk_teaching
	primary key(s_no, course_no, t_no);
select CONSTRAINT_NAME,column_name, position
	from all_cons_columns
	where table_name='TEACHINGS';

drop table choices;
select table_name from user_tables;

在这里插入图片描述

---------------reset sequence-------------
drop sequence add_title_no_plan_sequence;
create sequence add_title_no_plan_sequence
	minvalue 1 maxvalue 99999999 
	start with 1 increment by 1 cache 10;
select * from user_sequences 
	where sequence_name ='ADD_TITLE_NO_PLAN_SEQUENCE'; 

grant select, alter on add_title_no_plan_sequence 
	to m_robot;
select table_name, privilege, grantee from user_tab_privs 
	where table_name='ADD_TITLE_NO_PLAN_SEQUENCE'
	and grantee='M_ROBOT';

grant select, alter on add_teacher_no_plan_sequence
	to m_robot;
select table_name, privilege, grantee from user_tab_privs 
	where table_name='ADD_TEACHER_NO_PLAN_SEQUENCE'
	and grantee='M_ROBOT';
------------assign privileges to role
select grantee,privilege from user_tab_privs
	where grantee ='M_ROBOT';
grant select on students to m_robot;
grant insert on students to m_robot;
grant delete on students to m_robot;
grant update on students to m_robot;
grant alter on students to m_robot;
select grantee,privilege from user_tab_privs
	where grantee ='M_ROBOT' and table_name='STUDENTS';

grant select on classes to m_robot;
grant insert on classes to m_robot;
grant delete on classes to m_robot;
grant update on classes to m_robot;
grant alter on classes to m_robot;
select grantee,privilege from user_tab_privs
	where grantee ='M_ROBOT' and table_name='CLASSES';

grant select on DEPARTMENTS to m_robot;
grant insert on DEPARTMENTS to m_robot;
grant delete on DEPARTMENTS to m_robot;
grant update on DEPARTMENTS to m_robot;
grant alter on DEPARTMENTS to m_robot;
select grantee,privilege from user_tab_privs
	where grantee ='M_ROBOT' and table_name='DEPARTMENTS';

grant select on COURSES to m_robot;
grant insert on COURSES to m_robot;
grant delete on COURSES to m_robot;
grant update on COURSES to m_robot;
grant alter on COURSES to m_robot;
select grantee,privilege from user_tab_privs
	where grantee ='M_ROBOT' and table_name='COURSES';

grant select on TEACHINGS to m_robot;
grant insert on TEACHINGS to m_robot;
grant delete on TEACHINGS to m_robot;
grant update on TEACHINGS to m_robot;
grant alter on TEACHINGS to m_robot;
select grantee,privilege from user_tab_privs
	where grantee ='M_ROBOT' and table_name='TEACHINGS';

grant select on TEACHERS to m_robot;
grant insert on TEACHERS to m_robot;
grant delete on TEACHERS to m_robot;
grant update on TEACHERS to m_robot;
grant alter on TEACHERS to m_robot;
select grantee,privilege from user_tab_privs
	where grantee ='M_ROBOT' and table_name='TEACHERS';

grant select on TITLES to m_robot;
grant insert on TITLES to m_robot;
grant delete on TITLES to m_robot;
grant update on TITLES to m_robot;
grant alter on TITLES to m_robot;
select grantee,privilege from user_tab_privs
	where grantee ='M_ROBOT' and table_name='TITLES';

conn maria/maria;
select * from user_role_privs;
select table_name,count(privilege),
	rtrim(xmlagg(xmlparse(content privilege || ',' wellformed)).getclobval(),',') as privilege
	from role_tab_privs 
	where role='M_ROBOT' group by table_name;

user_tab_privs用户权限表。
DISABLE NOVALIDATE:
1、约束不起作用
2、无法保证约束是真实的(不对任何数据做约束检查
disables the constraint and drops the index on the constraint, but keeps the constraint valid. This feature is most useful in data warehousing situations, because it lets you load large amounts of data while also saving space by not having an index. This setting lets you load data from a nonpartitioned table into a partitioned table using the exchange_partition_clause of the ALTER TABLE statement or using SQL*Loader. All other modifications to the table (inserts, updates, and deletes) by other SQL statements are disallowed.

ENABLE VALIDATE:
1、对现有数据检查约束。如果有违反约束限制的,则约束不能建立/启用。
2、启用约束。
3、对新插入数据检查约束。如果有违反约束限制的数据,将不能插入。
specifies that all old and new data also complies with the constraint. An enabled validated constraint guarantees that all data is and will continue to be valid.

--delete data from tables
--search constraints
select a.table_name, a.constraint_name
	from all_cons_columns a, all_constraints b 
	where a.constraint_name = b.constraint_name 
	and b.r_constraint_name  in 
	(select constraint_name from all_constraints 
		where constraint_type='P' 
		and table_name = 'STUDENTS')
	and a.table_name != 'STUDENTS';
alter table robot.teachings 
	disable constraint TEACHING_FK_STU;


delete robot.students;
select count(*) from robot.students;
delete robot.classes;
select count(*) from robot.classes;


select a.table_name, a.constraint_name
	from all_cons_columns a, all_constraints b
	where a.constraint_name = b.constraint_name
	and b.r_constraint_name in (
		select constraint_name from all_constraints
		where constraint_type='P'
		and table_name='DEPARTMENTS')
	and a.table_name !='DEPARTMENTS' 
	and a.table_name !='CLASSES';

alter table robot.teachers
	disable constraint TEACHER_FK_DEPT;
delete robot.departments;

select a.table_name, a.constraint_name
	from all_cons_columns a, all_constraints b
	where a.constraint_name = b.constraint_name
	and b.r_constraint_name in (
		select constraint_name from all_constraints
		where constraint_type='P'
		and table_name='TEACHERS')
	and a.table_name !='TEACHERS';
alter table robot.TEACHINGS
	disable constraint TEACHING_FK_TEACHER;
delete robot.teachers;
select count(*) from robot.teachers;


delete robot.teachings;
select count(*) from robot.teachings;
alter table robot.TEACHINGS
	enable constraint TEACHING_FK_TEACHER;
alter table robot.teachers
	enable constraint TEACHER_FK_DEPT;
alter table robot.teachings 
	enable constraint TEACHING_FK_STU;

delete robot.courses;
select count(*) from robot.courses;

delete robot.titles;
select count(*) from robot.titles;
------------------verify constraint status------------
select constraint_name,owner,status
	from all_constraints
	where CONSTRAINT_TYPE='R'
	and owner ='ROBOT'
	and constraint_name in ('TEACHING_FK_STU',
		'TEACHER_FK_DEPT','TEACHING_FK_TEACHER');


------------------verify table row num------------
select count(*) from robot.students;
select count(*) from robot.classes;
select count(*) from robot.TEACHINGS;
select count(*) from robot.DEPARTMENTS;
select count(*) from robot.COURSES;
select count(*) from robot.TEACHERS;
select count(*) from robot.TITLES;
----------------------insert data------------------------

---------departments---------------
insert into robot.departments(DEPT_NO,DEPT_NAME,DEPT_ADDR) 
	values('rc003', '软件工程系', '实验楼1号');
insert into robot.departments(DEPT_NO,DEPT_NAME,DEPT_ADDR) 
	values('rc002', '电力工程系', '实验楼2号');			
insert into robot.departments(DEPT_NO,DEPT_NAME,DEPT_ADDR) 
	values('rc001', '机械工程系', '实验楼1号');		
select * from robot.departments;



------------titiles-----------------------
insert into robot.titles(title_name) values('助教');
insert into robot.titles(title_name) values('讲师');
insert into robot.titles(title_name) values('副教授');
insert into robot.titles(title_name) values('教授');
select * from robot.titles;

------------teachers-----------------------
--reset sequence
select robot.add_teacher_no_plan_sequence.nextval from dual;
alter sequence robot.add_teacher_no_plan_sequence 
	increment by 115101;
select robot.add_teacher_no_plan_sequence.nextval from dual;
alter sequence robot.add_teacher_no_plan_sequence 
	increment by 1;

insert into robot.teachers(T_NAME,t_sex,t_birthday,
	title_no,dept_no) values('蔺相如','女',
	to_date('1967/2/12','yyyy/mm/dd'),
	4,'rc003');
insert into robot.teachers(T_NAME,t_sex,t_birthday,
	title_no,dept_no) values('赵括','男',
	to_date('1998/10/11','yyyy/mm/dd'),
	1,'rc003');
insert into robot.teachers(T_NAME,t_sex,t_birthday,
	title_no,dept_no) values('廉颇','男',
	to_date('1968/5/28','yyyy/mm/dd'),
	3,'rc003');
insert into robot.teachers(T_NAME,t_sex,t_birthday,
	title_no,dept_no) values('赵孝','女',
	to_date('1997/11/11','yyyy/mm/dd'),
	2,'rc003');

update robot.teachers set director_no=115107
	where t_no=115104 or t_no=115107;
update robot.teachers set director_no=115106
	where t_no=115105 or t_no=00115106;

select * from robot.teachers;

--------classes---------------------
insert into robot.classes(CLASS_NO,CLASS_NAME,DEPT_NO) 
	values('183001','软件18-1','rc003');
insert into robot.classes(CLASS_NO,CLASS_NAME,DEPT_NO) 
	values('183002','软件18-2','rc003');
insert into robot.classes(CLASS_NO,CLASS_NAME,DEPT_NO) 
	values('181001','机械18-1','rc001');
insert into robot.classes(CLASS_NO,CLASS_NAME,DEPT_NO) 
	values('181002','机械18-2','rc001');
select * from robot.classes;

----------courses-----
insert into robot.courses(COURSE_NO,COURSE_NAME)
	values('300715HI17R5','软件测试');
insert into robot.courses(COURSE_NO,COURSE_NAME)
	values('300715XI12R6','ORACLE');

select * from robot.courses;

-------------students-------------
insert into robot.students(s_no,s_name,s_sex,
	s_birthday, s_addr,class_no)
	values('1830020130','于谦','女',
		to_date('2000/4/1','yyyy/mm/dd'),
		'北京市','183002');
insert into robot.students(s_no,s_name,s_sex,
		s_birthday, s_addr,class_no)
	values('1830010101','郭德纲','男',
 		to_date('2000/10/1','yyyy/mm/dd'),'天津市','183001');
insert into robot.students(s_no,s_name,s_sex,
		s_birthday, s_addr,class_no)
	values('1830010102','郭麒麟','男',
 		to_date('2000/10/1','yyyy/mm/dd'),'北京市','183001');
insert into robot.students(s_no,s_name,s_sex,
		s_birthday, s_addr,class_no)
	values('1830020125','于洋','女',
 		to_date('1998/5/1','yyyy/mm/dd'),'黑龙江省哈尔滨市','183002');

update robot.students set monitor_no='1830010101'
	where s_no in ('1830010101', '1830010102');
 select * from robot.students;

-----------teachings---------------
insert into robot.teachings(COURSE_NO,T_NO,
	S_NO,SCORE,RATING) 
	values('300715HI17R5',115104,'1830020130',50,6);
insert into robot.teachings(COURSE_NO,T_NO,
	S_NO,SCORE,RATING) 
	values('300715XI12R6',00115105,'1830010101',57,1);
insert into robot.teachings(COURSE_NO,T_NO,
	S_NO,SCORE,RATING) 
	values('300715HI17R5',00115106,'1830010102',59,1);
insert into robot.teachings(COURSE_NO,T_NO,
	S_NO,SCORE,RATING) 
	values('300715XI12R6',00115107,'1830020125',60,10);
insert into robot.teachings(COURSE_NO,T_NO,
	S_NO,SCORE,RATING) 
	values('300715HI17R5',00115104,'1830020125',92,9);
insert into robot.teachings(COURSE_NO,T_NO,
	S_NO,SCORE,RATING) 
	values('300715XI12R6',00115105,'1830020130',78,6);
insert into robot.teachings(COURSE_NO,T_NO,
	S_NO,SCORE,RATING) 
	values('300715HI17R5',00115106,'1830010101',84,8);
insert into robot.teachings(COURSE_NO,T_NO,
	S_NO,SCORE,RATING) 
	values('300715XI12R6',00115107,'1830010102',64,6);

select * from robot.teachings;


-------------select------------------
--简单查询
--1.列出所有学生的学号、姓名、年龄,并根据学号和年龄进行排序
--排序(多列) + 列别名
select s_no as "学号", s_name as "姓名",
	extract(year from sysdate)-extract(year from s_birthday) as "年龄"
	from robot.students
	where extract(year from sysdate)-extract(year from s_birthday) between 16 and 21
	order by s_no, extract(year from sysdate)-extract(year from s_birthday);

.在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

-------------select------------------
--简单查询
--1.列出所有学生的学号、姓名、年龄,并根据学号和年龄进行排序
--排序(多列) + 列别名
select s_no as "学号", s_name as "姓名",
	extract(year from sysdate)-extract(year from s_birthday) as "年龄"
	from robot.students
	where extract(year from sysdate)-extract(year from s_birthday) between 16 and 21
	order by s_no, extract(year from sysdate)-extract(year from s_birthday);


--2.下半年出生的学生有哪些
--单条件 + 算术比较 + 时间处理函数 + 排序(单列)
select s_name
	from robot.students
	where extract(month from s_birthday)>=7
	order by s_no; 

--3.年龄22岁以上(包含22岁)的学生有哪些
--单条件 + 算术比较 + 时间处理函数
select s_name
	from robot.students
	where extract(year from sysdate)-extract(year from s_birthday)>=22;

--4.查询所有年龄小于22岁的女学生
--复合条件(2) + 算术比较 + 时间处理函数 + 逻辑运算
select s_name
	from robot.students
	where extract(year from sysdate)-extract(year from s_birthday)<22
	and s_sex='女';


--5.所有年龄小于22岁且姓于的女学生
--复合条件(3) + 算术比较 + 匹配测试 + 时间处理函数+ 逻辑运算
select s_name
	from robot.students
	where extract(year from sysdate)-extract(year from s_birthday)<22
	and s_sex='女'
	and s_name like '于%';


--分组聚合

--列函数
--6.求学生的数量
select count(*) as "学生数量" from robot.students;

--7.年龄最大的学生的生日是什么时候
select to_char(min(s_birthday),'yyyy-mm-dd') 
	from robot.students;

--group by
--8.所有课程,分别由几位老师同时上,有几位学生选,课程的平均分是多少
select course_no, count(t_no),count(s_no),avg(score)
	from robot.teachings
	group by course_no;

--9.每位老师所教课程中,选课人数、平均成绩、平均评分
select course_no, t_no,count(s_no),avg(score),avg(rating)
	from robot.teachings
	group by course_no, t_no;

--10.找出平均成绩高于60的学生的学号及其平均成绩
--having + order by desc
select s_no as student_num, avg(score) as avg_score
	from robot.teachings
	group by s_no
	having avg(score)>=60
	order by avg_score desc;

--11.找出班级的班长及其班级号
--自然连接+表别名
select distinct s2.class_no, s2.s_name,s1.s_no
	from robot.students s1, robot.students s2
	where s1.monitor_no = s2.s_no;

--12.交叉连接
select t_name, course_name
	from robot.teachers, robot.courses;


--13.打印所有学生的成绩
--内连接 + 相等连接
select stu.s_no, stu.s_name, cou.course_name, tea.score
	from robot.students stu, robot.courses cou, 
		robot.teachings tea
	where stu.s_no = tea.s_no
	and cou.course_no = tea.course_no
	order by tea.s_no, tea.course_no; 

--14.打印所有学生的总成绩
--内连接+相等连接+排序+分组
select stu.s_no as "学号", stu.s_name as "姓名",
		sum(tea.score) as "总成绩"
	from robot.students stu, robot.courses cou, 
		robot.teachings tea
	where stu.s_no = tea.s_no
	and cou.course_no = tea.course_no
	group by stu.s_no, stu.s_name
	order by "总成绩" desc; 

--外连接

--15.系部老师的信息
--左外连接(oracle版本)
select dept_name, tea.t_no, tea.t_name
	from robot.departments dept, robot.teachers tea
	where dept.dept_no = tea.dept_no(+);


--16.没有学生的班级信息及其系部信息
--左外连接(标准) + 单行子查询
select dept.dept_name, class.class_name
	from robot.departments dept left outer join robot.classes class
	on dept.dept_no = class.dept_no
	where ( select count(*) from robot.students stu
		where stu.class_no = class.class_no) = 0;


--17.请列出没有班长的班级
--多行子查询 + 空值测试 + 包含测试 + 去重
select class_name from robot.classes
	where class_no in (
		select distinct class_no
		from robot.students
		where monitor_no is null);

--18.每课成绩的最高分数
--多行子查询(all)
select stu.s_name, course.course_name, tea.score
	from robot.students stu, robot.teachings tea,
		robot.courses course
	where stu.s_no = tea.s_no
	and course.course_no = tea.course_no
	and tea.score >= all (
		select t.score  from robot.teachings t
		where t.course_no = tea.course_no);


--多列子查询
--select 
--	from robot.teachings  tea, robot.courses cou
--	where tea.

--嵌套子查询
1.1 Synonym
conn system/lixiang666;
grant create synonym to maria;
conn maria/maria;
create synonym students for robot.students;
create synonym classes for robot.classes;
create synonym teachings for robot.teachings;
create synonym teachers for robot.teachers;
create synonym courses for robot.courses;
create synonym titles for robot.titles;
create synonym departments for robot.departments;
1.2 Sequence
conn system/lixiang666;
grant create sequence to maria;
select * from user_sequences;
select add_teacher_no_plan_sequence.nextval from dual;
select add_teacher_no_plan_sequence.currval from dual;
1.3 View
create view lx_view as
       select s.s_no,s.s_name,teachings.score,teachings.rating,courses.course_no,courses.course_name,teachers.t_no
       from students s,teachings,teachers,courses
       where s.s_no=teachings.s_no and courses.course_no=teachings.course_no
      and teachers.t_no= teachings.t_no;
1.4 Index
desc students;
create index lx_index on students(s_name);
1.5 运行结果
SQL> conn system/lixiang666;
已连接。
SQL> grant create synonym to maria;

授权成功。

SQL> conn maria/maria;
已连接。
SQL> create synonym students for robot.students;

同义词已创建。
SQL> select * from students;

S_NO
--------------------
S_NAME
--------------------------------------------------------------------------------
S_SEX    S_BRITHDAY
-------- --------------
S_ADDR
--------------------------------------------------------------------------------
CLASS_NO         MONITOR_NO
---------------- --------------------
1730090714
李响
男       01-12-00

S_NO
--------------------
S_NAME
--------------------------------------------------------------------------------
S_SEX    S_BRITHDAY
-------- --------------
S_ADDR
--------------------------------------------------------------------------------
CLASS_NO         MONITOR_NO
---------------- --------------------
黑龙江
17300907         1730090714


SQL> create synonym classes for robot.classes;

同义词已创建。

SQL> select * from classes;

CLASS_NO         CLASS_NAME           DEPT_NO
---------------- -------------------- ----------
17300907         软件17-1             1
SQL> create synonym teachings for robot.teachings;

同义词已创建。

SQL> create synonym teachers for robot.teachers;

同义词已创建。

SQL> create synonym courses for robot.courses;

同义词已创建。

SQL> create synonym titles for robot.titles;

同义词已创建。

SQL> create synonym departments for robot.departments;

同义词已创建。
SQL> conn system/lixiang666;
已连接。
SQL> grant create sequence to maria;

授权成功。

SQL> create sequence add_teacher_no_plan_sequence
  2  minvalue 1 maxvalue 99999999
  3  start with 1 increment by 1 cache 10;

序列已创建。

SQL> select * from user_sequences;

SEQUENCE_NAME                                                 MIN_VALUE
------------------------------------------------------------ ----------
 MAX_VALUE INCREMENT_BY CY OR CACHE_SIZE LAST_NUMBER
---------- ------------ -- -- ---------- -----------
MVIEW$_ADVSEQ_GENERIC                                                 1
4294967295            1 N  N          50           1

MVIEW$_ADVSEQ_ID                                                      1
4294967295            1 N  N          20           1

LOGMNR_EVOLVE_SEQ$                                                    1
1.0000E+27            1 N  Y          20           1


SEQUENCE_NAME                                                 MIN_VALUE
------------------------------------------------------------ ----------
 MAX_VALUE INCREMENT_BY CY OR CACHE_SIZE LAST_NUMBER
---------- ------------ -- -- ---------- -----------
LOGMNR_SEQ$                                                           1
1.0000E+27            1 N  Y          20           1

LOGMNR_UIDS$                                                          1
1.0000E+27            1 N  Y          20         100

REPCAT$_FLAVORS_S                                            -2.147E+09
2147483647            1 N  N           0           1


SEQUENCE_NAME                                                 MIN_VALUE
------------------------------------------------------------ ----------
 MAX_VALUE INCREMENT_BY CY OR CACHE_SIZE LAST_NUMBER
---------- ------------ -- -- ---------- -----------
REPCAT$_FLAVOR_NAME_S                                                 1
1.0000E+27            1 N  N           0           1

REPCAT$_REPPROP_KEY                                                   1
1.0000E+27            1 N  N          20           1

REPCAT_LOG_SEQUENCE                                                   1
1.0000E+27            1 N  N          20           1


SEQUENCE_NAME                                                 MIN_VALUE
------------------------------------------------------------ ----------
 MAX_VALUE INCREMENT_BY CY OR CACHE_SIZE LAST_NUMBER
---------- ------------ -- -- ---------- -----------
REPCAT$_REFRESH_TEMPLATES_S                                           1
1.0000E+27            1 N  N          20           1

REPCAT$_USER_AUTHORIZATIONS_S                                         1
1.0000E+27            1 N  N          20           1

REPCAT$_TEMPLATE_REFGROUPS_S                                          1
1.0000E+27            1 N  N          20           1


SEQUENCE_NAME                                                 MIN_VALUE
------------------------------------------------------------ ----------
 MAX_VALUE INCREMENT_BY CY OR CACHE_SIZE LAST_NUMBER
---------- ------------ -- -- ---------- -----------
REPCAT$_TEMPLATE_OBJECTS_S                                            1
1.0000E+27            1 N  N          20           1

REPCAT$_TEMPLATE_PARMS_S                                              1
1.0000E+27            1 N  N          20           1

REPCAT$_USER_PARM_VALUES_S                                            1
1.0000E+27            1 N  N          20           1


SEQUENCE_NAME                                                 MIN_VALUE
------------------------------------------------------------ ----------
 MAX_VALUE INCREMENT_BY CY OR CACHE_SIZE LAST_NUMBER
---------- ------------ -- -- ---------- -----------
REPCAT$_TEMPLATE_SITES_S                                              1
1.0000E+27            1 N  N          20           1

REPCAT$_TEMP_OUTPUT_S                                                 1
1.0000E+27            1 N  N          20           1

REPCAT$_RUNTIME_PARMS_S                                               1
1.0000E+27            1 N  N          20           1


SEQUENCE_NAME                                                 MIN_VALUE
------------------------------------------------------------ ----------
 MAX_VALUE INCREMENT_BY CY OR CACHE_SIZE LAST_NUMBER
---------- ------------ -- -- ---------- -----------
TEMPLATE$_TARGETS_S                                                   1
1.0000E+27            1 N  N          20           1

REPCAT$_EXCEPTIONS_S                                                  1
1.0000E+27            1 N  N          20           1

ADD_TEACHER_NO_PLAN_SEQUENCE                                          1
  99999999            1 N  N          10           1


已选择21行。

SQL> select add_teacher_no_plan_sequence.nextval from dual;

   NEXTVAL
----------
         1

SQL> select add_teacher_no_plan_sequence.currval from dual;

   CURRVAL
----------
         1
SQL> create view lx_view as
  2     select s.s_no,s.s_name,teachings.score,teachings.rating,courses.course_no,courses.course_name,teachers.t_no
  3     from students s,teachings,teachers,courses
  4     where s.s_no=teachings.s_no and courses.course_no=teachings.course_no
  5    and teachers.t_no= teachings.t_no;

视图已创建。
SQL> desc students;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 S_NO                                      NOT NULL VARCHAR2(10)
 S_NAME                                    NOT NULL VARCHAR2(50)
 S_SEX                                              VARCHAR2(4)
 S_BRITHDAY                                         DATE
 S_ADDR                                             VARCHAR2(100)
 CLASS_NO                                           VARCHAR2(8)
 MONITOR_NO                                         VARCHAR2(10)

SQL> create index lx_index on students(s_name);

索引已创建。

李响Superb CSDN认证博客专家 机器学习 TensorFlow 图像处理
成为一名优秀的算法工程师⬆️ ,
目前还在读软件工程,
AI攻防、算法和深度学习方向,
微博同名❤️ :李响Superb,
(记得关注,有问题微博私信!)
我们一起努力呀!
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 猿与汪的秘密 设计师:白松林 返回首页
实付 9.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值