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

痛定思痛。 2022-11-19 04:27 128阅读 0赞

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

![在这里插入图片描述][20210414161406996.png]

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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgzODc4NQ_size_16_color_FFFFFF_t_70]  
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';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgzODc4NQ_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgzODc4NQ_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgzODc4NQ_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgzODc4NQ_size_16_color_FFFFFF_t_70 4]  
实现查寻某一列的约束类型，通过连接操作。

--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;

![在这里插入图片描述][20210414183234681.png]

---------------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;

.![在这里插入图片描述][20210414190249417.png]  
![在这里插入图片描述][20210414190416551.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgzODc4NQ_size_16_color_FFFFFF_t_70 5]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgzODc4NQ_size_16_color_FFFFFF_t_70 6]  
![在这里插入图片描述][20210414191005993.png]  
![在这里插入图片描述][20210414193139801.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgzODc4NQ_size_16_color_FFFFFF_t_70 7]  
![在这里插入图片描述][20210414193513894.png]

-------------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);
    
    索引已创建。

[20210414161406996.png]: /images/20221022/8617b708a9b24a979bdbb8753faca87f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgzODc4NQ_size_16_color_FFFFFF_t_70]: /images/20221022/1224ad5458c34a66abd54e4963c3d8c9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgzODc4NQ_size_16_color_FFFFFF_t_70 1]: /images/20221022/c478559cebae46c2ae920e3876f4e1f2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgzODc4NQ_size_16_color_FFFFFF_t_70 2]: /images/20221022/da0acafa05924907b079c06ac6058a7f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgzODc4NQ_size_16_color_FFFFFF_t_70 3]: /images/20221022/f214614f39d540049e37491115fad2ed.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgzODc4NQ_size_16_color_FFFFFF_t_70 4]: /images/20221022/c82e572285364c0e8e06015ed2ddc554.png
[20210414183234681.png]: /images/20221022/5179c6b919294eaa80b8f130a1c8eed5.png
[20210414190249417.png]: /images/20221022/75f6e075f87540b18248931ed6560bf1.png
[20210414190416551.png]: /images/20221022/c578d45e264e46be87a3eeac913e3e42.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgzODc4NQ_size_16_color_FFFFFF_t_70 5]: /images/20221022/a917d7fadfb9486aa7e67de31e6a2df6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgzODc4NQ_size_16_color_FFFFFF_t_70 6]: /images/20221022/e66432a881c94083b168fa7650ba20d1.png
[20210414191005993.png]: /images/20221022/6fe8ed6eb8e8466ab8bb0780fc07e74f.png
[20210414193139801.png]: /images/20221022/373973e75147412eaea15ac3f2b4a1eb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgzODc4NQ_size_16_color_FFFFFF_t_70 7]: /images/20221022/85d823a1d56b4547bf031c575ea9396e.png
[20210414193513894.png]: /images/20221022/dad4b3b067c3452fb1773c0e4a1bd8e2.png