狂肝两万字MySQL的主要操作增删改查,你看了还学不会?

数据库练习

– use python; 打开数据库

SQL的数据定义语句

​ 创建 删除 修改

• 模式 create schema drop schema 无
• 表 create table drop table alter table
• 视图 create view drop view
• 索引 create index drop index alter index

定义模式 CREATE SCHEMA<模式名>AUTHORIZATION<用户名>

为用户zhang创建一个模式TEST,并且在其中定义一个表TAB1

create schema TEST AUTHORIZATION zhang; --创建模式

create table tab1 …; – 创建表

删除模式 DROP SCHEMA<模式名><CASCADE(级联)|RESTRICT(限制)>

cascade和restrict两者必选其一
选择了CASCADE级联表示删除模式时把该模式中所有的数据库对象全部删除
选择了RESTRICT限制表示该模式已经定义了下属的数据库对象(如表,视图等)则拒绝该删除语句的执行)只有当模式中没有任何下属的东西时才会执行DROP SCHEMA语句

创建表

create  table 表名(<列名><数据类型>[列级完整性约束条件]....,[表完整性约束条件]);

-- unique表示唯一值,把sno设置成了主键
create table  student(sno char(9) primary key ,sname char(20) unique);

-- 主键也可以在后面设置
create table  student(sno char(9) primary key ,sname char(20) unique, primary key (sno,sname))

-- sno是外键,被参照表是sc_tab
create table  student(sno char(9) ,sname char(20) unique, primary key (sno), foreign key (sno) references sc_tab(sno));

数据类型

| 数据类型  | 含义 |
| ------------- | ------------- |
| char(n),character(n)  | 长度为n的定长字符串  |
| varchar(n) charactervarying(n)  | 最大长度为n的变长字符串  |
| clob | 字符串最大对象 |
| blob | 二进制最大对象 |
| int,integer | 长整型 4字节 |
| smallint | 短整型 2字节 |
| bigint | 大整数 8字节 |
| numeric(p,d) | 定点数,由p位数字(不包括符号,小数点)组成,小数点后面有d位数字 |
| decimal(p,d),dec(p,d) | 跟上面一样 |
| real | 取决于机器精度的单精度浮点数 |
| double precision | 取决于机器精度的双精度浮点数 |
| float(n) | 可选精度的浮点数,精度至少为n位数字 |
| boolean | 逻辑布尔量 |
| date | 日期,包含年、月、日,格式为YYYY-MM-DD |
| time | 时间,包含一日的时分秒,格式为HH:MM:SS |
| timestamp | 时间戳类型 |
| interval | 时间间隔类型 |

模式与表

• 每一个基本表都属于某一个模式,一个模式包含多个基本表

  方法1: 在表名中明显的给出模式名

  create table 模式名.Student_tab(…) /* student表所属的模式是<模式名>

  方法2:在创建模式语句中同时创建表

  create schema 模式名 authoriation 用户名

  create table 表名(…)

  方法3:设置所属的模式,这样在创建表时不用给出模式名

  当用户创建基本表时若没有指定模式,系统根据搜索路径来确定该对象所属的模式.

• 显示当前的搜索路径

  show search_path; -- 默认值是$user,public
  -- 首先搜索与用户名相同的模式名,模式名如果不存在则使用public模式
  

• 数据库管理员也可以设置搜索路径

  set search_path to "S-T",public;
  -- 然后定义基本表
  create table student(....);
  -- 实际上是建立了S-T.student基本表,因为搜索路径中发现S-T模式名存在,所以把该模式作为基本表所属的模式
  

修改基本表

-- 格式 --
-- cascade 指定的东西会把它所有关联的对象都会操作掉 --
-- restrict 指定的东西会限制他的操作,除非没有关联其它的东西即可操作 --
-- constraint 用于删除完整性约束条件 --
alter table<表名>
[add [column] <新列名><数据类型> [完整性约束]]
[add <表级完整性约束>]
[drop [column]<列名>[cascade | restrict]]
[drop constraint<完整性约束> [restrict | cascade]]
[alter column<列名><数据类型>]

• 例子

-- 向student表中增加"入学时间"列,数据类型为日期型
alter table 表名 add 入学时间单词 date; -- 不论基本表中是否原来已经有数据,新增加的列一律空值

-- 将年龄的数据类型有字符型改为整形
alter table 表名 alter column sage int;

-- 增加课程名称必须取唯一值的约束条件
alter table 表名 add 课程名称单词 varchar unique;
alter table 表名 add unique(课程名称单词); -- 两者应该都可以

删除基本表

-- 格式 --
drop table <表名> [restrict|cascade]  -- 被restrict修饰的表有限制条件,被其它对象引用时不能被删除
-- 被其他表约束所引用如 check,foreign key 等约束
-- 如有视图,触发器,存储过程或函数
-- 选择cascade时表示没有限制条件,删除基本表同时其它的被一同删除
-- 默认情况是 restrict

-- 删除student表
drop table student cascade;

索引的建立与删除

• 建立索引

  -- 次序默认是asc升序
  -- unique 表明次索引的每一个索引值只对应唯一的记录数据
  -- cluster 表示要建立的索引是聚簇索引
  create [unique][cluster]index<索引名> on <表名>(<列名[<次序>].....);
  
  -- 例如
  -- 为学生-课程数据库中的student,course和sc三个表建立索引,其中
  -- student表按学号升序建唯一索引
  -- course表按课程号升序建唯一索引
  -- sc表按学号升序和课程号降序建唯一索引
  create unique index studentNo on student(sno);
  create unique index courseNo on course(cno);
  create unique index sc_sno_cno on sc(sno asc,cno desc);
  

• 修改索引

  alter index<旧的索引名字>rename to <新索引名>;
  -- 将sc表的scno索引修改成scsno
  alter index scno rename to scsno;
  

• 删除索引

  drop index <索引名>;
  -- 删除student表的stusname索引
  drop index stusname;
  

select语句

数据查询

-- 格式 -- 
select [all | distinct]<目标表达式>[....] from <表名或试图名>[....] (<select 语句>) [as] <别名>
[where <条件表达式>]
[group by <列名>[having<条件表达式>]]
[order by <列名>[asc|desc]];

-- 单表查询
select sno,sname from 表名； -- 查询所有的学号和姓名

select * from 表名;  -- 查询所有的信息

-- 目标表达式不仅可以是算术运算符还可以是字符串常量和函数等 -- 

select sname,2023-sage from 表名; -- 查询全体学生的姓名以及出生年份(注意查询出来的列名为2023-age)

select sname,2023-sage as sage from 表名; -- 可以用as取别名替换查询的字段名

-- 或将as用空格代替
select sname,2023-sage sage from 表名;

 -- 小写字母表示系名,增加了列'Year of Birth'并且每一行都会填充这个字符串
select sname,'Year of Birth:',2023-sage,lower(sdpet);

-- 选择表中的若干元组
	(1)消除取值重复的行
		select distinct sno from student_tab; -- 用distinct 消除重复数据 一般默认为ALL
	(2)查询满足条件的元组
        | 查询条件  | 谓词 |
        | 比较      | =, >, <, >=, <=, !=, <>, !>, !<, not+,前面这些运算符 |
        | 确定范围  | between and, not between and |
        | 确定集合  | in,not in |
        | 字符匹配  | like,not like |
        | 空值      | is null,is not null  |
        | 多重条件  | and,or,not |
-- 比较大小
	select sname from student where sdept = 'CS';  -- 查询系别为cs系的所有人姓名
	
	select sname,sage from student where sage < 20; -- 查询年纪小于20岁的所有人姓名和年纪
	
	select distinct sno from sc where grade < 60;  -- 查询sc表中所有成绩小于60且不重复学生的学号信息
	
-- 确定范围 between and 和not between and
	-- 查询年纪在20-23之间学生的信息
	select sname,sage,sdept from student where sage between 20 and 23;
	
	-- 查询年纪不在20-23之间学生的信息
	select sname,sage,sdept from student where sage not between 20 and 23;
	
-- 确定集合 in
	-- 查询计算机科学系(CS),数学系(MA)和信息系(IS)学生的姓名和性别
	select sname,ssex,from student where sdept in('CS','MA','IS');
	
	-- 查询不是计算机科学系(CS),数学系(MA)和信息系(IS)学生的姓名和性别
	select sname,ssex,from student where sdept not in('CS','MA','IS');
	
-- 字符匹配 like
	[not] like '<匹配串>' [ESCAPE '换码字符']
	-- 匹配串可以匹配通配符%和_
	-- % 表示 代表任意长度的字符串可以为0长度,例如a%b就是以a开头,b结尾的任意长度字符串,acb,agdffdfb都可
	-- _ 下划线表示任意单个字符 例如a_b,adb,ajb的任意三个字符串都能匹配
	
	-- 查询学号为201215121的学生的详细情况
	select * from student where sno like '201215121';
	-- 等价于
	select * from student where sno = '201215121';
	
	-- 如果like后面不含通配符,则可以用 = 运算符取代 like 谓词,用 != 或 <> 取代 not like 谓词
	-- 查询所有姓刘学生的姓名,学号,性别
	select sname,sno,ssex from student where sname like '刘%';
	
	-- 查询姓 欧阳 且全名为三个汉字的学生姓名
	-- 注意数据库字符集为ASCII时一个汉字需要两个下划线,当字符集为GBK时只需要一个下划线
	select sname from student where sname like '欧阳_'; 
	
	-- 查询 所有名字中第二个字为 阳 的学生的姓名和学号
	select sname,sno from student where sname like '_阳%';
	
	-- 查询所有不姓刘的学生姓名,学号,性别
	select sname,sno,ssex from student where sname like not like '刘%';
	
	-- 如果用户要查询的字符串本身就含有通配符%或_这时我们就要使用ESCAPE'<换码字符>'
	-- 查询DB_Design 课程的课程号和学分
	select cno,ccredit from course where cname like 'DB\_Design' ESCAPE '\\';  -- sql里面只有\一个
	
	-- 查询以'DB_'开头且倒数第三个字符为i的课程的详细情况
	select * from course where cname like 'DB\_%i_ _' ESCAPE '\\';  -- sql里面只有\一个
	
-- 涉及空值查询 not null 和 is not null
	-- 某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩,查询缺少成绩的学生学号和相对应课程号
	select sno,cno from sc where grade is null; -- 分数grade是空值 这里的is不能用 = 代替
	
	-- 查询所有有成绩的学生学号和课程号
	select sno,cno from sc where grade is not null;

-- 多重条件查询 and 和 or  and优先级高于or 可以用括号改变优先级
	-- 查询计算机科学系年纪在20岁以下的学生姓名
	select sname from student where sdept = 'CS' and sage < 20;
	select Sname from student_tab where Sdept IN ('CS') and Sage < 20;  -- 这样也可以
	
	-- in 谓词实际上是多个or运算符的缩写因此下方是等价的
	select sname,ssex,from student where sdept in('CS','MA','IS');
	select sname,ssex from student where sdept = 'CS' or sdept = 'MA' or sdept = 'IS';
	
-- order by 子句 默认ASC升序
	-- 查询选修了3号课程的学生的学号及其成绩,查询结果按分数的降序排列
	select sno,grade from sc where cno = '3' order by grade desc;
	-- 对于空值,排序时显示的次序由具体系统实现来决定
	-- 按升序排,含空值的元组最后显示
	-- 按 降序排,空值的元组则最先显示
	
	-- 查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列
	select * from student order by sdept,sage desc;
-- 聚集函数
	count(*)  -- 统计元组个数
	count([distinct | all] <列名>)  -- 统计一列中值的个数
	sum([distinct | all] <列名>)  -- 计算一列值的总和(此列必须是数值类型)
	avg([distinct | all] <列名>)  -- 计算一列值的平均值(此列必须是数值类型)
	max([distinct | all] <列名>)  -- 求算一列值的最大值
	min([distinct | all] <列名>)  -- 求一列中的最小值
	-- distinct 去除重复的计算,默认 all
	
	-- 查询学生总人数
	select count(*) from student;  -- 统计多少行
	
	-- 查询选修了课程的学生人数
	select count(distinct sno) from sc;
	
	-- 计算选修了1号课程的平均成绩
	select avg(grade) from sc where cno = '1';
	
	-- 查询选修了1号课程的学生最高分数
	select max(grade) from sc where cno = '1';
	
	-- 查询学生201215012选修课程的总学分数
	select sum(ccredit) from sc, course where sno = '201215012' and sc.cno = course.cno;
	
	-- 当聚集函数遇到空值时,除了count(*) 外,都跳过空值而只处理非空值
	-- 注意,where子句中是不能用聚集函数作为条件表达式的,聚集函数只能用于 select 子句和 group by 中的		having子句
	
-- group by子句
	-- 作用是将查询结果按某一列或多列的值分组,值相等的为一组
	
	-- 求各个课程号及相对应的选课人数
	select cno,count(sno) from sc group by cno;
	
	-- having短语指定筛选条件
	-- 查询选修了三门以上课程的学生学号
	select sno from sc group by sno having count(*) > 3;
	
	-- where子句与having短语的区别在于作用对象不同
	-- where 子句作用于基本表或视图,从中选择满足条件的元组
	-- having 短语作用于组,从中选择满足条件的组
	
	-- 查询平均成绩大于等于90分的学生学号和平均成绩
	-- 这个语句是不对的
		select sno,avg(grade) from sc where avg(grade) >= 90 group by sno;
	-- 因为 where子句中是不能用聚集函数作为条件表达式的
	-- 正确的表达式是
		select sno,avg(grade) from sc group by sno having avg(grade) >= 90;

-- 连接查询
	-- 概述  若一个查询同时涉及两个表以上的表,称之为连接查询
	-- 包括(等值连接查询,自然连接查询,非等值连接查询,自身连接查询,外连接查询,复合条件连接查询)
	
	-- 等值查询与非等值查询
		-- 连接查询的where子句中用来连接两个表的条件称为连接条件或连接谓词
		-- 格式 [<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>
		-- 其中比较运算符有 = , > , < , >= , <= , != (或<>),等
		-- 此外连接谓词还可以使用以下格式
		-- [<表名1>.]<列名1>between [<表名2>.] <列名2> and [<表名2>.]<列名3>
		-- 当连接运算符为 = 时, 称为等值连接, 使用其它运算符称为非等值连接
		-- 连接谓词中的列名称为连接字段,连接条件中的各连接字段类型必须是可比的,但是名字不必相同
		
	-- 例子
		-- 查询每个学生及其选修课程的情况(等值连接)
		select student.*,sc.* from student,sc where student.sno = sc.sno;
		-- 如果在sc表上建立了索引的话,就不用每次全表扫描sc表了,而是根据索引值通过索引找到相对应的sc元组,		  用索引查询sc表中满足条件的元组一般会比全表扫描快
		
		-- 若在目标列中重复的属性列去掉则称为自然连接
		-- 将上述的等值连接使用自然连接完成
		select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc where student.sno = 			sc.sno;
		-- 为什么student.sno后面不使用student.sname等等这些了?因为如果后面省去前缀student的属性列在两		个表中是唯一的属性名称,则可以省略前缀
		
		-- 一条sql 语句可以同时完成选择和连接查询,这时where子句是由连接谓词和选择谓词组成的符合条件
		
		-- 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
		select student.sno,sname from student,sc where student.sno = sc.sno and sc.cno = '2' and 
		sc.grade > 90;
		-- 该查询的一种优化高效的执行过程是,先从sc中挑出cno=2并且grade>90的元组形成一个中间关系，再取得		 结果
		
	-- 自身连接
		-- 不仅可以在两个表之间进行,也可以是一个表与自己进行连接
		-- 查询每一门课的间接先修课(先修课的先修课)
		select first.cno,second.cpno from course first,course second where first.cpno = 			second.cno; -- 在同一个course表中把这个表取两个别名,然后根据第一个表的cpno 是否等于第二个表的		cno,来查出cno和cpno
		
	-- 外连接
		-- 在通常的连接操作中只有满足连接条件的元组才能作为结果输出,比如两个学生没选课,导致在student结果		  中直接把这两个学生舍弃了.
		-- 有时我们要把这些结果(悬浮元组)保存在表中不舍弃,而在表中填NULL值表示没有选课,这时我们就需要使		 用外连接
		-- 左外连接
		select student.sno,sname,ssex,sdept,cno,grade from student left outer join sc on 			(student.sno = sc.sno)
		 -- 也可以用using来去掉结果中的重复值 from student left outer join sc using(sno);
		 -- 相当于查询所有为空的记录，即使sc表中没有student表中学生的cno，grade的记录也会填充null作为			 结果查出来
		 
		 -- 右外连接
		 SELECT student_tab.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM student_tab RIGHT OUTER 			 JOIN sc_tab ON (student_tab.Sno = sc_tab.Sno);
		 -- 查询所有为空的记录，只查sc表中有student表中学生的cno，grade的记录填充null作为			 		  结果查出来
		 
		 -- 左连接,右连接跟左外右外差不多
		 
	-- 多表连接
		-- 查询每个学生的学号,姓名,选修的课程名及成绩
			select student.sno,sname,cname,grade from student,sc,course where student.sno = 			sc.sno and sc.cno = course.cno;
-- 嵌套查询(重点)
	-- 概述 在sql语言中,一个select-from-where 语句称为一个查询块,将一个查询快嵌套在另一个查询块的where		子句或者having短语的条件中的查询称为嵌套查询
	-- 例如
	-- 由内到外(内查询)先查询所有cno为2的学号,然后在判断外查询的sno是否在这个元组内,在的话查询这个学号的		人名 
	-- 注意内层的select子查询不能使用order by子句,order by只对最终结果排序
	select sname from student where sno in(select sno from sc where cno = '2');
	
	-- 带有in谓词的子查询
		-- 查询与"刘晨" 在同一个系学习的学生
			1. 先确定刘晨所在系名
			(1)select sdept from student where sname = '刘晨';
		
			2.查找所有在cs系学习的学生
			(2)select sno,sname,sdept from student where sdept = 'cs';
			
		-- 将第一步的查询嵌入到第二步查询中构造嵌套查询
		-- 解法1
			-- 先查询刘晨所在的系然后再查询那些学生的学号姓名系别
			-- 子查询的查询条件不依赖与父查询,称为不相关子查询
			-- 子查询的查询条件依赖与父查询,称为相关子查询
			
			select sno,sname,sdept from student where sdept in
			(select sdept from student where sname = '刘晨');
		
		-- 解法2 自身连接
			select stu1.sno,stu1.sname,stu1.sdept  from student stu1,student stu2
			where stu1.sdept = stu2.sdept and stu2.sname = '刘晨';
			
		-- 查询选修了课程名为"信息系统"的学生学号和姓名
			-- 由里到外非常简单
			-- 先查询哪些课程名为信息系统的id
			-- 子查询的查询条件依赖与父查询,称为相关子查询，整个嵌套查询称为相关嵌套查询
			select cno from course where cname = '信息系统';
			
			-- 再查询选课情况表的学生选课情况查询选了这门课的学生id->sno
			select sno from sc where cno in
			(select cno from course where cname = '信息系统')
			
			-- 由上面得出学生学号后再从student表中查询对应信息
			select sno,sname from student where sno in
			(select sno from sc where cno in (select cno from course where cname = '信息系统'));
			
			-- 上题使用连接查询
			select stu.sno,sname from student,sc,course where student.sno = sc.sno
			and sc.cno = course.cno and course.cname = '信息系统';
			
		-- 带有比较运算符的子查询(> < = >= <= !=或<>
			-- 当一个学生只可能在一个系学习,也就是查询结果是一个值,这时可以用 = 代替 in
			select sno,sname,sdept from student where sdept =
           	 (select sdept from student where sdept = '刘晨');
           	 
           	 -- 找出每个学生超过他自己选修课程平均成绩的课程号
           	 	-- 先查询这个学生选修的平均成绩的情况
           	 	select sno,cno from sc x where grade >=
           	 	(select avg(grade) from sc y where y.sno = x.sno)
           	 	
        -- 带有any(some)或all谓词的子查询
         	> any		大于子查询结果中的某个值
         	< any		小于子查询结果中的某个值
         	>= any		大于等于子查询结果中的某个值
         	<= any		小于等于子查询结果中的某个值
         	= any		等于子查询结果中的某个值
         	!= any		不等于子查询结果中的某个值(<>any也可以)
         	
         	> all		大于子查询结果中的所有值
         	< all		小于子查询结果中的所有值
         	>= all		大于等于子查询结果中的所有值
         	<= all		小于等于子查询结果中的所有值
         	= all		等于子查询结果中的所有值
         	!= all		不等于子查询结果中的所有值(<>all也可以)
        
        -- 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄
        	select sname,sage from student from sage
        	<any(select sage from student where sdept = '计算机科学系') and sdept !=(或<>) '计算机		    科学系';
        	
        	-- 上述用聚集函数查询
        	select sname,sage from student where sage <
        	(select min(age) from student where sdept = 'CS')
        	and sdept <> 'CS';
        	-- 聚集函数比any,all等查询效率要高
        	
        	
                --  any(或some) all谓词与聚集函数,in谓词的等价转换关系
                -- [any/all]	 =		 <>或!=		<		<=		>		>=
                -----------------------------------------------------------------------
                -- any			in		 --		   <MAX	    <=MAX	 >MIN	 >=MIN
                -----------------------------------------------------------------------
                -- all		   -- 		NOT IN	   <MIN	 	<=MIN	 >MAX	 >=MAX
                
                -- 其中=any 等价于in,=all其实没实际意义,!=all 等价于 NOT IN 等等
                
                
		-- 带有EXISTS谓词的子查询
			-- 总结果不会出现重复现象
			-- 概述带有EXISTS谓词的子查询不会返回任何数据,只会返回true或false
			-- EXISTS 若内层查询结果非空,则外层的where子句返回真值,否则返回假值
			-- NOT EXISTS 若内层查询结果为空,则外层的where子句返回真值,否则返回假值
			-- 可以利用判断x∈S,S⊆R,S=R,S⋂R非空是否成立
			
			-- 查询所有选修了1号课程的学生姓名
			-- 由EXISTS引出的子查询,其目标列表达式通常用*,因为带EXISTS的子查询只返回真值或假值,给出列				名毫无意义
			-- 重点: 只会返回选了的信息且不重复,否则返回空
			select sname from student where EXISTS
			(select * from sc where cno = 1 and sno = student.sno);
			
		    -- NOT EXISTS
		    -- 重点:
            -- 内部为假,NOT EXISTS返回真,内部查询会返回所有人的信息
            -- 内部为真,NOT EXISTS返回假,内部查询会返回除这个符合条件的人外所有人的信息
		    -- 如果内层查询结果为假没有查到选课程1号的信息,则NOT EXISTS会把结果变成true,会返回所有没有				选cno为1的人信息,若内层查询为真则NOT EXISTS把返回值变成假,同时返回的结果除cno为1的所有人				信息
		    SELECT Sname FROM student_tab WHERE NOT EXISTS(SELECT * FROM sc_tab WHERE					student_tab.Sno = Sno and Cno = 1);
		    
		    
		    -- 查询选修了全部课程的学生姓名(没有一门课程是他不选修的)
		    select sname from student where
             NOT EXISTS(select * from course where NOT EXISTS
                       select * from sc where sno = student.sno and cno = course.cno);
                       
             -- 查询至少选修了学生201215122选修的全部课程的学生号码
             select distinct sno from sc scx where NOT EXISTS
             (select * from sc scy where scy.sno = '201215122' NOT EXISTS
             (select * from sc scz where scx.sno = scz.sno and scz.cno = scy.sno));
             
       -- 集合查询
       		-- select 语句的结果是元组的集合,所以多个select可以进行集合操作(union(并集),
       		-- intersect(交集),except(差集))
       		
       		-- 查询计算机科学系的学生及年龄不大于19岁的学生
       		-- union会自动去掉重复元组
       		-- union all 会保留重复元组
       		select * from student where sdept = 'CS'
       		union
       		select * from student where sage <= 19;
       		
       		
       		-- 查询选修了课程1或者选修了课程2的学生
       		select sno from sc where cno = 1
       		union
       		select sno from sc where cno = 2;
       		
       		-- 查询计算机科学系的学生年龄不大于19岁学生的交集
       		select * from student where sdept = 'CS'
       		intersect
       		select * from student where sage <= 19;
       		
       		
       		-- 查询既选修了课程1又选修了课程2的学生,就是查询选修课程1的学生集合与选修课程2的学生集合的交				集
       		select sno from sc where cno = 1
       		intersect
       		select sno from sc where cno = 2;
       		
       		-- 相当于
       		select sno from sc where cno = 1 and sno in
       		(select sno from sc where cn = 2);
       		
       		-- 查询计算机科学系的学生年龄不大于19岁的学生的差集
       		select * from student where sdept = 'CS'
       		except
       		select * from student where sage <= 19;
       		
       -- 基于派生表的查询
       		-- 子查询不仅可以出现在where子句中还可以出现在from 子句中,这时子查询生成的临时派生表成为主			查询的查询对象
       		
       		-- 例如找出每个学生超过他自己选修课程平均成绩的课程号
       		select sno,cno from sc,
       		(select sno,avg(grade) from sc group by sno)
       		as avg_sc(avg_sno,avg_grade)
       		where sc.sno = avg_sc.avg_sno and sc.grade >= avg_sc.avg_grade;
       		
       		-- 查询所有选修了1号课程的学生姓名
       		select sname from student,
       		(select sno from sc where cno = 1) as sc1
       		where student.sno = sc1.sno;
       		
       		
       		
       		
      --------------------------------------select总结-------------------------------------------
      -- 一般格式
      select [all|distinct]<目标表达式>(中间可以加as或者不加)[别名][<目标表达式>[别名],......]
      from <表名或视图名>(中间加as或不加)[别名][<表名或视图名>[别名],......]或(select..) as <别名>
      [where <条件表达式>]
      [group by <列名1>[having <条件表达式>]]
      [order by <列名1>[asc|desc]]
      
      -- 目标表达式的可选格式
      1. *
      2. <表名>.*
      3. count([distinct|all]*)
      4. [<表名>.]<属性列名表达式>[,[<表名>.]<属性列名表达式>]...
      
      -- 聚集函数的一般格式
      count([distinct|all]<列名>)
      sum([distinct|all]<列名>)
      avg([distinct|all]<列名>)
      max([distinct|all]<列名>)
      min([distinct|all]<列名>)
      
      -- where 子句的条件表达式的可选格式
      1.
      	<属性列名>[<属性列名><常量>[any|all](select语句)]
      2.
      	<属性列名>[not]between[<属性名><常量>(select语句)]and[<属性列名><常量>(select语句)]
      3.
      	<属性列名>[not] in (<值1>[,<值2>]...) (select语句)
      4. <属性列名>[not] like<匹配串>
      5. <属性列名> is [Not] null
      6. [not] exists(select子句)
      7.
      	<条件表达式>[and|or]<条件表达式>[,and|or]<条件表达式...>...]

insert 语句

数据增加

-- 通常插入有两种形式,一种是插入元组还一种是插入查询结果
-- insert 插入格式
	1. insert into <表名>[(<属性列1>[,<属性列2>]...)] values(<常量1>[,<常量2>]...);
	2. insert into <表名>[(<属性列1>[,<属性列2>]...)] values(<常量1>[,<常量2>]...),(<常量1>[,<常量			2>]...);
	
	-- 将一个学生元组(201215128,陈冬,男,IS,18)插入到student表中
	insert into student(sno,sname,ssex,sdept,sage) values('201215128','陈冬','男','IS',18);
	-- 或者只指定表名,但是元组顺序要与表内顺序一致
	insert into student values('201215128','陈冬','男','IS',18);
	
	-- 插入一条选课记录('201215128','1')
	insert into sc(sno,cno) values('201215128','1');
	-- 或者
	insert into sc values('201215128','1',null); -- 属性必须要对应
	
-- 插入子查询结果
	-- 格式
		insert into <表名>[(<属性列1>[,<属性列2>.....])] 子查询;
		
	-- 将学生表按系别分组求平均年龄存入dept_age表中
		insert into dept_age(sdept,sage)
		select sdept,avg(age) from student group by sdept;

update 语句

数据更新

-- 格式 如果省略where子句及后面条件则表示更新所有
update <表名> set <列名> = <表达式>[,<列名> = <表达式>]... [where <条件>[and|or <条件...>等等....]];

	-- 修改某一个元组的值
		update student set sage = 22 where sno = '201215121';
	
	-- 修改多个元组的值
		update student set sage = sage + 1; -- 所有人年纪+1
		
-- 带子查询的修改语句
	-- 将计算机科学系全体学生成绩归零
	update sc set grade = 0 where sno in
	(select sno from student where sdept = 'cs');

delete 语句

数据删除

-- 格式 删除的不是表的定义而是表的数据,省略where则删除全部
delete from <表名> [where <条件>[and|or <条件...>|等等....]];

	-- 删除某一个元组的值
		-- 删除学号为201215121学生信息
		delete from student where sno = '201215121';
		
	-- 删除多个元组的值
		-- 删除sc表的所有数据
		delete from sc;
-- 带子查询的删除语句
	-- 删除计算机科学系所有学生的选课记录
		delete from sc where sno in
		(select sno from student where sdept = 'cs');

空值的处理

空值的产生

-- 概念
	-- 空值就是不知道,不存在,无意义的值
	-- 一般有以下几种情况取空值
		1.该属性应该有一个值,但是目前不知道它的具体值,例如,某学生的年龄属性,因为学生登记表漏填了,不知道该		学生年龄,因此取空值
		
		2.该属性不应该有值,例如,缺考学生的成绩为空,因为他没有参加考试
		
		3. 由于某种原因不便于填写,例如,一个人的电话号码不想让大家知道,则取空值
		
		-- 空值是应该很特殊的值,含有不确定性
	
	-- 空值的产生
		-- 向sc表中插入一个元组,学生学号是201215126,课程号是1,成绩为空
		insert into sc(sno,cno,grade) values('201215126','1',null);
		-- 或
		insert into sc values('201215126','1',null);
		insert into sc(sno,cno) values('201215126','1');
		
		-- 将student表中学生学号为201215200的学生所属的系改为空值
			update student set sdept = null where sno = '201215200'
			
	-- 空值的判断
		-- 判断一个属性的值是否为空值,用 is null 或 is not null 来表示
			-- 从student表中找出漏填了数据的学生信息
				select * from student where
				sname is null or
				ssex is null or
				sage is null or
				sdept is null;
				
	-- 空值的约束条件
		-- 属性定义或者域定义中有not null约束条件的不能取空值,码属性不能取空值
		
	-- 空值的算数运算,比较运算和逻辑运算
		-- 空值与另外一个值(包括另一个空值)的算术运算结果为空值,空值与另一个(包括另一个空值)
		-- 的比较运算结果为unknown,有了unknown后,传统的逻辑运算中二值(True,False)逻辑就扩展成了三值逻辑
		
		
		-- 找出选修1号课程的不及格学生
			select sno from sc where grade < 60 and cno = 1;
			
		-- 选出选修1号课程的不及格的学生以及缺考的学生
			select sno from sc where grade < 60 and cno = 1
			union
			select sno from sc where grade is null and cno = 1;
			union
			select sno from sc where cno = 1 and (grade < 60 or grade is null);

视图

创建视图

-- 概念
	-- 视图是从一个或几个基本表(或视图)导出的表,他与基本表不同,它是一个虚表
	-- 视图一旦定义就可以跟基本表一样被增删改查但是加了with check option 则会限制一些操作
	-- 视图内容发生改变关联的基本表也会改变,如果有些内容限制了,则基本表的内容不会变
	-- 基本表发生改变如果没有限制条件,则视图对应的也会改变

-- 定义视图
	-- 建立视图
    -- 格式
    create view <视图名>[(<列名1>[,<列名2>....])]
    as <子查询>
    [with check option]; -- 限制条件
    
    -- with check option 表示对视图进行update,insert,delete操作时要保证更新,插入或删除的行满足视图定义		的谓词条件(即子查询中的条件表达式)
    
    -- 组成视图的属性列名或者全部省略或者全部指定,没有第三种选择
    -- 如果省略了视图的各个属性列名,则会对应子查询中的select目标列的字段
    
    -- 如下三种情况必须指定组成视图的所有列名
    	1.某个目标列不是单纯的属性名,而是聚焦函数或者列表达式
    	2.多表连接时选出了几个同名列作为视图的字段
    	3.需要在视图中为某个列启用新的更合适的名字
    	
    -- 建立信息系学生的视图
    create view IS_student_view as
    select sno,sname,sage,sdept from student where sdept = 'IS';
    -- 创建视图只是把视图的定义存在数据字典,并不执行其中的select语句
    
    -- 建立信息系学生的视图,并且要求进行修改和插入操作时仍需保证该视图只有信息系的学生
    create view IS_Student as
    select sno,sname,sage from student where sdept = 'IS'
    with check option;
    
    -- 视图不仅可以建立在单个基本表上,还可以建立在多个基本表上,也可以建立在一个或多个已定义好的视图上,或建		立在基本表与视图上
    -- 若一个视图是从单个基本表导出的,并且只是去掉了基本表的某些行和某些列,但保留了主码,则称这类视图为行列		子集视图
    
    -- 建立信息系选修了1号课程的学生视图(包括学号,姓名,成绩)
    create view is_s1(sno,sname,grade) as
    select student.sno,sname,grade from student,sc where 
    sdept = 'IS' and student.sno = sc.sno and sc.cno = 1;
    
    
    -- 建立信息系选修了1号课程且成绩在90分以上的学生的视图
    create view is_stu(sno,sname,grade) as
    select sno,sname,grade from is_s1 where grade >= 90;
    
    -- 定义一个反映学生出生年份的视图 这种称为表达式视图
    create view BT_stu(sno,sname,sbirth) as
    select sno,sname 2023-sage from student;
    
    -- 将学生的学号及平均成绩定义为一个视图
    create view avg_stu(sno,avg_grade) as
    select sno,avg(grade) from sc group by sno;
    
    -- 将student表中所有女生记录定义为一个视图
    create view nv as
    select * from where ssex = '女'；

删除视图

-- 格式
drop view <视图名>[CASCADE]
-- 删除视图BT_S和视图IS_S1
	drop view BT_S; -- 成功执行
	drop view IS_S1; -- 拒绝执行
	-- 由于IS_S1视图上还导出了IS_S2的视图,所以该语句被拒绝执行,如果要确定删除则必须要加CASCADE级联删除
	
-- 查询视图
-- 在信息系学生的视图中找出年龄小于20岁的学生
select sno,sage from IS_student where sage < 20;

-- 查询选修了1号课程的信息系学生
select IS_student.sno,sname from IS_student,sc where IS_student.sno = sc.sno and sc.cno = 1;

-- 在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩
select * from S_G where Gavg >= 90;
-- 或
select sno,avg(grade)>90 from sc group by sno;
select sno,avg(grade) from group by sno having avg(grade) > 90

-- 使用派生表完成
-- 派生表和视图是有区别的
-- 视图一旦定义,其定义将永久保存在数据字典中,而派生表只是在语句执行时临时定义,语句执行后该定义即被删除
select * from (select sno,avg(grade) from sc group by sno) as S_G(sno,Gavg)
where Gavg >= 90;

更新视图

-- 概念
	-- 更新视图是指视图执行Insert,删除,修改操作
	-- 由于视图是不实际存储数据的虚表,因此对视图的更新最终转换为对基本表的更新
	-- 为了防止恶意的增删改数据,可在定义是加上with check option 来检查子查询的条件是否满足
	
-- 将信息系学生视图IS_student 中学号为'201215122'的学生姓名改为'刘辰'
update IS_student set sname = '刘辰' where sno = '201215122';

-- 向信息系学生视图IS_student中插入一个新的学生记录,其学号为201215129,姓名为赵新,年龄为20岁
insert into IS_student values('201215129','赵新',20)

-- 删除信息系学生视图IS_student中学号为201215129的记录
delete from IS_student where sno = '201215129'

视图规定

• 若视图是由两个以上基本表导出的,则此视图不允许更新
• 若视图的字段来自字段表达式或常数,则不允许对此视图执行insert和update操作,但允许执行delete操作
• 若视图的字段来自聚集函数,则此视图不允许更新
• 若视图定义中包含Group By子句,则此视图不允许更新
• 若视图定义中含有District 短语,则此视图不允许更新
• 若视图定义中含有嵌套查询,并且内层查询的from子句中涉及的表也是导出该视图的基本表,则此视图不允许更新
• 例如

-- 	将sc表中的成绩在平均成绩之上的元组定义成一个视图GOOD_SC;
create view GOOD_SC
as
select sno,cno,grade from sc where grade >
(select avg(grade) from sc);
-- 导出的视图GOOD_SC的基本表是SC,内层查询中涉及的表也是SC,所以视图GOOD_SC是不允许更新的

• 一个不允许更新的视图上定义的视图也不允许更新

视图的作用

• 视图能够简化用户的操作
• 视图使用户能以多种角度看待同一数据
• 视图对重构数据库提供了一定程度的逻辑独立性
• 视图能够对机密数据提供安全保护
  中学号为’201215122’的学生姓名改为’刘辰’
  update IS_student set sname = ‘刘辰’ where sno = ‘201215122’;

– 向信息系学生视图IS_student中插入一个新的学生记录,其学号为201215129,姓名为赵新,年龄为20岁
insert into IS_student values(‘201215129’,‘赵新’,20);

– 删除信息系学生视图IS_student中学号为201215129的记录
delete from IS_student where sno = ‘201215129’;

视图规定

• 若视图是由两个以上基本表导出的,则此视图不允许更新
• 若视图的字段来自字段表达式或常数,则不允许对此视图执行insert和update操作,但允许执行delete操作
• 若视图的字段来自聚集函数,则此视图不允许更新
• 若视图定义中包含Group By子句,则此视图不允许更新
• 若视图定义中含有District 短语,则此视图不允许更新
• 若视图定义中含有嵌套查询,并且内层查询的from子句中涉及的表也是导出该视图的基本表,则此视图不允许更新
• 例如

-- 	将sc表中的成绩在平均成绩之上的元组定义成一个视图GOOD_SC;
create view GOOD_SC
as
select sno,cno,grade from sc where grade >
(select avg(grade) from sc);
-- 导出的视图GOOD_SC的基本表是SC,内层查询中涉及的表也是SC,所以视图GOOD_SC是不允许更新的

• 一个不允许更新的视图上定义的视图也不允许更新

视图的作用

• 视图能够简化用户的操作
• 视图使用户能以多种角度看待同一数据
• 视图对重构数据库提供了一定程度的逻辑独立性
• 视图能够对机密数据提供安全保护
• 适当利用视图可以更清晰地表达查询