数据库原理笔记（II）_数据库中任意字符怎么表示-CSDN博客

本文链接：https://blog.csdn.net/sxH3O/article/details/123722904

ch3.关系数据库标准语言SQL（部分）

1. Sql语言的动词

Sql功能	动词
数据定义	create，drop，alter（更改表的结构）
数据查询	select
数据操纵	insert，update，delete
数据控制	grant, revoke

2.定义、修改与删除基本表

定义（创建）

定义基本表：

CREATE TABLE <表名>

(<列名> <数据类型>[ <列级完整性约束条件> ]

[,<列名> <数据类型>[ <列级完整性约束条件>] ] …

[,<表级完整性约束条件> ] )；

其中：表名为所要定义的基本表的名字，列名为组成该表的各个属性(列)，列级完整性约束条件为涉及相应属性列的完整性约束条件，表级完整性约束条件为涉及一个或多个属性列的完整性约束条件。常用的完整性约束有：主码约束PRIMARY KEY、唯一性约束UNIQUE、非空值约束NOT NULL、参照完整性约束FOREIGN、KEY、REFERENCES。

加入约束：CONSTRAINT salary_cap CHECK (salary < 100000) --加入约束名字
修改基本表：

ALTER TABLE <表名>

[ ADD <新列名> <数据类型> [ 完整性约束 ] ]

[ DROP <完整性约束名> ] [ DROP column <列名> ]

[ MODIFY <列名> <数据类型> ]；

其中：表名为要修改的基本表，ADD子句为增加新列和新的完整性约束条件，DROP子句为删除指定的完整性约束条件，MODIFY子句

为用于修改列名和数据类型。

alter table student add comdate datetime --增加一个属性
alter table student drop column comdate --删除一个属性
alter table student add 姓名 char(40)
update student set 姓名=sname

sp_help 快速查看表结构、视图信息

删除基本表：DROP TABLE <表名>;
建立索引：

CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…)；

用<表名>指定要建索引的基本表。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔

用<次序>指定索引值的排列次序，升序ASC，降序DESC。缺省ASC。

UNIQUE表明此索引的每一个索引值只对应唯一的数据记录；

CLUSTER表示要建立的索引是聚簇索引。

优点：可以提高查询速度（系统内部有B+树or二分法查找）；

缺点：每次执行insert,update,delete,都会将index表重新排序，会降低操作效率。

删除索引：DROP INDEX <索引名>；
调用函数：select xx()

例：select getdate(),day(getdate())

查询

SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] … --目标列表达式可以是列名（属性）、函数、算术表达式、字符串

FROM <表名或视图名>[， <表名或视图名> ] …

[ WHERE <条件表达式> ]

[ GROUP BY <列名1> [ HAVING <条件表达式> ] ] --分组

[ ORDER BY <列名2> [ ASC|DESC ] ]； --含第一排序位、第二排序位……

其中：SELECT子句指定要显示的属性列；FROM子句指定查询对象(基本表或视图)；WHERE子句指定查询条件；GROUP BY子句对查询结果按指定列的值分组，该属性列值相等的元组为一个组，通常会在每组中作用集函数；HAVING短语筛选出只有满足指定条件的组；ORDER BY子句对查询结果表按指定列值的升序或降序排序；DISTINCT表示只显示完全不相同的行（相同的只出现一次）。如：select distinct sno from sc代表显示所有（不同的）学号。

使用集合函数：
- 集合函数作用于每一组
- where后面不能跟集合函数，比如显示score表中的最高分的学生学号和课程号，需使用语句：
  
  select sno,cno from sc where grade = (select max(grade) from sc)。
- 计数：COUNT([DISTINCT|ALL] *) / COUNT([DISTINCT|ALL] <列名>)（NULL不算入）
  - 如查询学生总人数，即为SELECT COUNT(*) FROM SStudent;
- 计算总和：SUM([DISTINCT|ALL] <列名>)
- 计算平均值：AVG([DISTINCT|ALL] <列名>)
- 求最大值：MAX([DISTINCT|ALL] <列名>)
- 求最小值：MIN([DISTINCT|ALL] <列名>)

对查询结果分组：

使用group by
值相等的为一组，若有两个，则为排列组合值相等的为一组：select cno,class from sc group by cno, class
select后面只能出现分组的属性或集合函数

select cno,count(*) from sc group by cno having count(*)>=3  --显示有三个人以上选的课,count(*)表示分组后该组有多少元组
select sno,avg(grade) from sc group by sno having min(grade)>70 and avg(grade)>85	--查找每个人所有课的平均分，在这些人中选出每门课大于70且平均分大于85的人
select cno,avg(grade) from sc group by cno	--每门课的平均分

having和where的区别：

having对每组（分组后）限定，where对每个元组限定（筛选），where筛选完了才会分组。
查询结果排序：使用order by字句，可以按一个或多个（第一排序位优先）属性列排序，升序ASC，降序DESC，缺省值为升序。当排序列含空值时，ASC排序列为空值的元组最后显示，DESC排序列为空值的元组最先显示。例如order by sage ASC,sname DESC。
别名：
- 三种形式：
  - 字段名 as 别名
  - 字段名（空格）别名
  - 别名**=**字段名
- 若使用别名，则语句中每处（如select后）应改为别名后的名称
[例]显示score中选修多门课程的同学中分数为非最高分成绩的记录。

select a.sno,a.degree,a.cno from sc a,sc b where a.sno=b.sno and a.degree<b.degree（“<”号保证了该同学选修了多门课程）
字符串匹配：
- 通配符**%**：代表任意长度(可以为0)的字符串。如a%b表示以a开头、以b结尾的任意长度的字符串
- 通配符**_** ：代表任意单个字符。如a_b表示以a开头、以b结尾的长度为3的任意字符串
- 通配符**[]：代表满足其中任何一个的被筛选，[]中加^**表示不为[]中任何一个的被筛选
- 利用escape关键字，使用换码字符将通配符转义为普通字符
  [例1] 查询DB_Design课程的课程号和学分。
  SELECT Cno,Ccredit FROM SCourse WHERE Cname LIKE 'DB\_Design' ESCAPE '\';
  
  [例2] 询以DB_开头、且倒数第3个字符为 i的课程的详细情况。
  SELECT * FROM SCourse WHERE Cname LIKE 'DB\_%i__' ESCAPE '\';

select * from student where sage>20 and sname like '刘%' --'%'代表一个或多个字符，查找姓刘的人,'刘_'代表刘+一个字符,不姓刘即NOT LIKE
select * from student where sname like '[李王张刘]%' 	  --查找姓氏为这四个姓的人
select * from student where sname like '[^李王张刘]%' 	  --查找姓氏不为这四个姓的人

涉及空值的查询：在WHERE子句的<比较条件>中使用谓词IS NULL 或 IS NOT NULL
多重条件查询：使用逻辑运算符and和or联结多个查询条件。
连接查询：同时涉及多个表的查询。根据连接条件进行拼接。

[例1]查询每个学生及其选修课程的情况。
SELECT SStudent.*,SC.* FROM SStudent,SC WHERE SStudent.Sno = SC.Sno;

[例2]查询每一门课的间接先修课(即先修课的先修课)。

select first.cno,second.cpno from course first,scource second where first.cpno = second.cno

内连接还可以使用**join…on…**方法，如：
```
select sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno = course.cno
--可写为
select sname,cname,grade from student join sc on student.sno=sc.sno join course on sc.cno = course.cno
```
外连接：（Oracle）(+)，即给其加了一个万能的空行，拼不上则使用，拼不上的情况下Student表中的记录只拼一次（在这里即为未选课）

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student,SC WHERE Student.Sno = SC.Sno(+);

(Sql Server)SELECT SStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM SStudent LEFT OUTER JOIN SC on SStudent.Sno = SC.Sno;

外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN) 和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。

嵌套查询：

外层查询/父查询+内层查询/子查询

select * from student where sno in (select sno from sc where cno in 
(select cno from course where cname='数据库原理'))

带有in谓词的子查询：

[例] 查询与刘晨在同一个系学习的学生。

（1）确定刘晨所在系名。

（2）查找所有在IS系学习的学生。

SELECT Sno,Sname,Sdept FROM SStudent WHERE Sdept IN (SELECT Sdept FROM SStudent WHERE Sname=‘刘晨’);

带有any或all谓词的子查询：any任意，all所有

	=	<>或!=	<	<=	>	>=
any	in	–	<max	<=max	>min	>=min
all	–	not in	<min	<=min	>max	>=max

[例]查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄。

法1：用All谓词

select Sname,Sage from SStudent where sage < any(select Sage from SStudent where Sdept = 'IS') and Sdept <> 'IS'

法2：用集合函数

select Sname,Sage from student where Sage < (select min(Sage) from student where Sdept ='IS') and Sdept<>'IS'

带有exists谓词的子查询：

每一条、每一条地去拼接。

相当于存在量词 $\exists$ 。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑true或false，若内层查询结果非空则返回真值，否则返回假值。由EXISTS引出的子查询的目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。

[例]查询所有选修了1号课程的学生姓名。

首先在Student中依次取每个元组的Sno值，用此值去检查SC关系，若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= '1'，则取此Student.Sname送入结果关系。

--用嵌套查询 
SELECT Sname FROM Student WHERE EXISTS 	/*相关子查询*/
   (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= ‘1’)；
--用连接查询
SELECT Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno= ‘1’；

[例2]查询选修了全部课程的学生姓名。

法1：首先找出Student中的一条信息，到中间层语句，找出一个课程信息，若是该学生有某一门课程没有选，则内层向中间层返回false，not exists为true，直接向外层返回true，最终not exists为false，不符合条件；若符合条件，则查找下一个课程，若其中所有课程查完，中间层语句都为false，则向外层返回true，not exists结果为true，符合条件，输出。

select sname from student where not exists
	(select * from course where not exists
		(select * from sc where sno = student.sno and cno = course.cno)

法2：

select sname from student where sno in (
	select sno from sc group by sno having count(*) = (select count(*) from course))