数据库笔记（基本介绍及增删改查操作举例）
第一章
1.数据
数据是数据库中存储的基本对象。
数据定义为数据是描述事物的符号记录。
2.数据库
数据库数据具有永久存储、有组织和可共享三个基本特点。
3.数据库管理系统
数据库管理系统位于用户应用程序与操作系统软件之间。
4.数据库系统
数据库系统由数据库、数据库管理系统、应用程序、数据库管理员组成。
“列”称为字段，“行”称为记录。
文件方式管理数据有以下缺点：
1.编写应用程序不方便。2.数据冗余不可避免。3.应用程序依赖性。4.不支持对文件的并发访问。5.数据间联系弱。6.难以满足不同用户对数据的需求。7.无安全控制功能。
数据库管理，对用户来说，他只针对数据库进行操作，无需对数据文件进行操作。
与文件管理数据相比，用数据库技术管理数据具有以下特点：
1.相互关联的数据集合。2.较少的数据冗余。3.程序与数据相互独立。4.保证数据安全、可靠。5.最大限度的保证数据的正确性。6.数据可以共享并能保证数据的一致性。
SQL Server只支持在Windows平台上运行，而Oracle有支持Windows平台和Linux平台的不同版本.
第二章
1.数据库中的数据具有静态特征和动态特征
静态特征：数据的基本结构、数据间的联系以及对数据取值范围的约束。
动态特征：对数据可以进行的操作以及操作规则。
一般将对数据的静态特征和动态特征对的描述称为数据模型的三要素，即在描述数据时要包括数据的基本结构、数据的约束条件（这两个属于静态特征）和定义在数据上的操作（属于动态特征）。
2.E-R模型
实体中每个具体的记录值（一行数据），我们称为实体的一个实例。
在实体属性中，将能够唯一标识实体的一个属性或最小的一组属性（称为属性集或属性组）称为实体的标识属性，这个属性或属性组也称为实体的码。例如，“学号”就是学生实体的码。
3.层次模型
层次模型是数据库管理系统中最早出现的数据模型。
层次模型中的两点限制：1.有且仅有一个节点无父节点，这个节点即为树的根。2.其他节点有且仅有一个父节点。
层次模型的一个基本特点：任何一个给定的记录值只有从层次模型的根部开始按路径查看时，才能明确其含义，在任何节点都不能脱离父节点而存在。
4.数据的三级模式结构
数据的三级模式结构是指数据库外模式、模式和内模式。
广义地讲，内模式是最接近物理存储的，也就是数据的物理存储方式，包括数据存储位置、数据存储方式等。外模式是最接近用户的，也就是用户所看到的数据视图。模式是介于内模式和外模式之间的中间层，是数据的逻辑组织方式。
第三章
1.数据库数据的操作主要包括查询、插入、删除和更改数据四种。
2.在关系数据模型中，一般将数据完整性分为三类，即实体完整性、参照完整性和用户定义的完整性。
前两个是关系模型必须满足的完整性约束，是系统级的约束。用户定义的完整性主要是限制属性的取值在有意义的范围内，如限制性别的取值范围为“男”和“女”。属于应用级的约束。
3.如果一个属性或属性集的值能够唯一标识一个关系的元组而又不包括多余的属性，则称该属性或属性集为候选键。例如，学生（学号、姓名、年龄、性别、所在系）的候选键是学号。
候选键又称为候选关键字或候选码。在一个关系上可以有多个候选键。
当一个关系中有多个候选键时，可以从中选择一个作为主键。每个关系只能有一个主键。
主键可以由一个属性组成，也可以由多个属性组成。我们称由多个属性共同组成的主键为复合主键。
4.实体完整性是指关系数据库中所有的表都必须有主键，而且表中不允许存在以下记录。
（1）无主键值的记录。（2）主键值相同的记录。
5.设F是关系R的一个或一组属性，如果F与关系S的主键相对应，则称F是关系R的外键。
“学生”关系中的“班号”属性与“班”关系中的主键“班号”对应，因此，“学生”关系中的“班号”是外键，引用了“班”关系中的“班号”（主键）。这里，“班”关系是被参照关系，“学生”关系是参照关系。
对于外键，一般符合以下要求。
或者值为空。 或者等于其所参照的关系中的某个元组的主键值。
主键要求必须是非空且不重的，但外键无此要求。外键可以有重复值。
第四章
1.SQL中的字符串常量
SQL中的字符串常量要用单引号括起来，如‘计算机系’。
2.定义架构：CREATE SCHEMA
为用户“ZHANG”定义一个架构，架构名为“S_C” CREATE SCHEMA S_C AUTHORIZATION ZHANG
3.删除架构：DROP SCHEMA
4.定义约束
NOT NULL和DEFAULT只能定义在“列级完整性约束定义”处，其他约束均可在“列级完整性约束定义”和“表级完整性约束定义”处定义。
5.两张表的创建
CREATE TABLE Jobs(
Jid char(6) PRIMARY KEY, --在列级定义主键
Descp nchar(20) NOT NULL,
EduReq nchar(6) DEFEAT '本科'，
MinSalary int,
MaxSalary int,
CHECK(MaxSalary>=MinSalary) --多列的CHECK约束必须定义在表级
）
CREATE TABLE Employees(
Eid char(10),
Ename nvarchar(20) NOT NULL,
Sex nchar(1) CHECK(Sex='男' OR Sex='女')， --一列
BirthDate date,
JobDate datetime DEFAULT GetDate(), --默认为系统的当前日期和时间
Sid char(18) UNIQUE, --取值不重复
Jid char(6),
Tel char(11),
PRIMAARY KEY(Eid), --在表级定义主键
FOREIGN KEY(Jid) REFERENCES Jobs(Jid)
)
6.修改表结构
为Employees表添加工资列，此列的列名为Salary，数据类型为Int，允许空。
ALTER TBLE Employees
ADD Salary INT
将Jobs表的Descp列的数据类型改为NCHAR（40）。
ALTER TABLE Jobs
ALTER COLUMN Descp NCHAR(40)
删除Employees表的Tel列。
ALTER TBLE Employees
DROP COLUMN Tel
为Jobs表中MinSalary列添加约束：大于等于1600.
ALTER TABLE Jobs
ADD CHECK（MinSalary>=1600）
7.删除表
DROP TABLE Employees
删除表时必须先删外键所在的表，然后再删除被参照的主键所在表。创建表时必须先建立被参照的主键所在表，然后建立外键所在表。
第五章
1.查询全体学生的详细记录
Select Sno,Sname,Ssex,Sage,Sdept from Student
等价于 Select * from Student
2.查询全体学生的姓名及出生年份
Student表中只记录了学生的年龄，没有记录学生的出生年份，但我们可以经过计算得到出生年份，即用当前年减去年龄，得到出生年份。
Select Sname,2015-Sage from Student
3.含字符串常量的列
含字符串常量的列：查询全体学生的姓名和出生年份，并在出生年份列前加入一个列，此列的每行数据均为“出生年份”常量值。
Select Sname,'出生年份',2015-Sage from Student
备注：经过计算的列、常量列的显示结果都没有列名，可以用表达式【AS】列别名的方式起列名。
4.去掉查询语句的重复行
Select distinct Sno from SC
5.查询计算机系全体学生的姓名
Select Sname from Student where Sdept='计算机系'
6.查询所有年龄在20岁以下的学生姓名及年龄
Select sname,Sage from Student where Sage<20
7.查询考试成绩有不及格课程的学生的学号
Select distinct Sno from SC where Grade<60
8.查询年龄在20~23岁的学生的姓名、所在系和年龄
Select Sname,Sdept,Sage from Student where sage between 20 and 23
此句等价于
Select Sname,Sdept,Sage from Student where Sage>=20 and Sage<=23
9.IN运算符的含义
IN运算符的含义为：当列中的值与集合中的某个常量值相等时，结果为true,表明此记录为符合查询条件的记录。
NOT IN运算符的含义正好相反：当列中的值与集合中的某个常量值相等时，结果为false，表明此记录为不符合查询条件的记录。
10.查询信息管理系、通信工程系和计算机系学生的姓名和性别
Select Sname,Ssex from Student where Sdept=（'信息管理系','通信工程系'，‘计算机系’）
此句等价于：Select Sname,Ssex from Student where Sdept='信息管理系' OR Sdept='通信工程系'OR Sdept='计算机系'
11.查询信息管理系、通信工程系和计算机系三个系之外的其他系学生的姓名和性别
Select Sname,Ssex from Student where Sdept NOT IN('信息管理系','通信工程系'，‘计算机系’)
此句等价于：Select Sname,Ssex from Student where Sdept!='信息管理系' AND Sdept!='通信工程系'AND Sdept!='计算机系'
12.匹配串中可以包含如下4种通配符
_下画线：匹配任意一个字符 %百分号：匹配0到多个字符【】：匹配【】中的任意一个字符。 【^】：不匹配【】中的任意一个字符。
13.查询姓“张”的学生的详细信息
Select * from Student where Sname like '张%'
14.查询姓“张”、姓“李”和姓“刘”的学生的详细信息
Select * from Student where Sname like '[张李刘]%'
15.查询名字的第二个字为“小”或“大”的学生的姓名和学号
Select Sname,Sno from Student where Sname like '_[小大]%'
16.查询所有不姓“刘”的学生姓名
Select Sname from Student where Sname NOT LIKE '刘%'
17.在Student表中查询学号的最后一位不是2、3、5的学生的详细信息
Select Sname from Student where Sno like '%[^235]'
18."ESCAPE"转义字符
"ESCAPE"转义字符是任何一个有效的字符，匹配串中也包含这个字符，表明位于该字符后面的那个字符将被视为普通字符，而不是通配符。
例如：为查找field1字段中包含字符串“30%”的记录，可在where子句中指定：
Where field1 like '%30!%%' escape '!’
又如，为查找field1字段中包含下画线（_）的记录，可在where子句中指定：
Where field1 like '%!_%' escape '!'
19.判断某个值是否为NULL
由于空值是不确定的值，因此判断某个值是否为NULL,不能使用比较运算符，只能使用专门的判断NULL值的子句来完成。而且，NULL不能与确定的值进行比较。
20.查询没有考试的学生的学号和相应的课程号
select sno,cno from sc
where grade is null
21.查询所有已经考试了的学生的学号、课程号和考试成绩
select sno , cno , grade from sc
where grade is not null
22.查询计算机系年龄20岁以下的学生的姓名
select sname from student
where sdept = '计算机系' and sage <20
23.查询计算机系和信息管理系学生中年龄在18~20的学生的 、姓名、所在系和年龄
select sno,sname,sdept,sage from student
where (sdept ='计算机系' or sdept = '信息管理系'）
and sage between 18 and 20
注意：or运算符的优先级小于and，要改变运算的顺序，可以通过加括号的方式来实现。
查询语句也可以写成 where sdept in ('计算机系','信息管理系')
24.排序
ASC表示按列值进行升序排序，DESC表示按列 值进行降序排序。默认排序方式ASC。
25.将学生按年龄升序排序
select * from student order by sage
查询选修了“C002”课程的学生的学号及成绩，查询结果按成绩降序排列.
select sno,grade from sc
where cno ='C002'
order by grade desc
查询全体学生的信息，查询结果按所在系的系名升序排列，同一个系的学生按年龄降序排列.
select * from student
order by sdept ASC,sage DESC
26.统计表中元组的个数
count(*):统计表中元组的个数. 除count(*)之外，其他函数在计算过程中均忽略null值。
27.统计学生总人数
select count(*) from student
统计选了课程的学生人数
select count(distinct sno) from sc
28.统计学号为“0811101”的学生的考试总成绩
select sum(grade) from sc where sno='0811101'
统计“0831103”学生的考试平均成绩.
只计算有成绩的考试平均成绩
select avg(grade) from sc where sno = '0831103'
29.查询“C001”课程考试成绩的最高分和最低分
select max(grade) 最高分，min(grade) 最低分 from sc where cno='C001'
30.统计每门课程 的选课人数，列出课程号和选课人数
select cno as 课程号，count(sno) as 选课人数 from sc group by cno
该语句首先对sc表中的数据按cno值进行分组，所有具有相同cno值的元组归为一组，然后再对每一组使用count函数进行计算，求出每组的学生人数
31.统计每个学生 的选课门数和平均成绩
select sno 学号，count(*) 选课门数，avg(grade) 平均成绩 from sc group by sno
注意：group by 字句中的分组依据列必须是表中存在的列名，不能使用as子句指定的列别名。例如不能写成group by 学号
32.统计每个系的学生人数和平均年龄
select sdept,count(*) as 学生人数，avg(sage) as 平均年龄 from student group by sdept
33.统计每个系的女生人数
select sdept, count(*) 女生人数 from student where ssex='女' group by sdept
34.按多个列分组
按多个列分组.统计每个系的男生人数和女生人数以及男生的最大年龄和女生的最大年龄。结果按系名升序排列.
分析：这个查询首先应该按“所在系”进行分组，然后再每个系组中再按“性别”分组，而将每个系每个性别的学生聚集到一个组中，最后再对最终的分组结果进行统计.
注意：当有多个分组依据列时，统计是以最小组为单位进行的.
select sdept,ssex,count(*)人数,max(sage) 最大年龄 from student group by sdept,ssex order by sdept
35.HAVING子句
HAVING子句用于对分组后的统计结果再进行筛选，它的功能有点像WHERE子句，但它用于组，而不是单个记录. 在HAVING子句中可以使用聚合函数，但在WHERE子句中则不能.
查询选课门数超过3门的学生的学号和选课门数
select sno,count(*) 选课门数 from sc group by sno having count(*)>3
分析：此语句的处理过程为 先执行group by 子句对sc表数据按sno 进行分组统计出每个学生的选课门数，然后再用聚合函数count对每一组进行统计，最后筛选出统计结果大于3的组.
36.查询计算机系和信息管理系每个系的学生人数
select sdept,count (*) from student where sdept in ('计算机系','信息管理系')
group by sdept
37.查询每个系年龄小于等于20的学生人数
select sdept,count (*) from student where sage <=20 group by sdept
38.查询每个学生及其选课的信息
由于学生信息存放在student表中，学生选课信息存放在sc表中，因此这个查询涉及两个表，这两个表之间进行连接的条件是两个表中的sno相等.
select * from student inner join sc on student.sno=sc.sno
sno列有两个：一个来自student表，一个来自sc表，这两个列的值完全相同（因为这里的连接条件是student.sno=sc.sno）.使用多表连接查询语句时一般将这些重复的列去掉，方法是在select子句中直接写所需要的列名，而不是写“*”。另外，由于进行多表连接后，在连接生成的表中可能存在列名相同的列，因此，为了明确需要的事哪个列，可以在列名前添加表名前缀限制.
去掉例40中的重复列
select student.sno,sname,ssex,sage,sdept,cno,grade from student join sc on student.sno=sc.sno
39.查询计算机系学生的选课情况，列出学生的名字、所选课程的课程号和考试成绩
select sname,cno,grade from student join sc on student.sno=sc.sno where sdept='计算机系'
40.自连接查询中要求必须为表指定别名
select sname,cno,grade from student as s join sc on s.sno=sc.sno where sdept='计算机系'
注意：当为表指定了别名后，在查询语句中的其他地方，所有用到该表名的地方都必须使用别名，不能再使用原表名.
每连接一张表，就需使用一个join子句
select sname,cname,grade from student s join sc on s.sno=sc.sno join course c on c.cno=sc.cno where sdept ='信息管理系' and cname='计算机文化学'
41.查询所有选了vb课程的 学生姓名和所在系
select sname,sdept from student s
join sc on s.sno=sc.sno
join course c on c.cno=sc.cno
where cname='vb'
42.有分组统计的多表连接查询。统计每个系的学生的考试平均成绩
select sdept,avg(grade) as averagegrade from student s join sc on s.sno=sc.sno group by sdept
43.有分组和行选择条件的多表连接查询 。统计计算机系学生中每门课程的选课人数、平均成绩、最高成绩和最低成绩
select cno,count(*) as total,avg(grade) as avggrade,max(grade) as maxgrade,min(grade) as mingrade from student s join sc on s.sno=sc.sno where sdept = '计算机系' group by cno
44.自连接是一种特殊的内连接，是指相互连接的表在物理上为同一张表，但在其逻辑上将其看成是两张表
要让物理上的一张表在逻辑上成为两张表，必须通过为表起别名的方法
from 表1 as t1 join 表1 as t2 on 表1.列名=表2.列名
45.查询与刘晨在同一个系学习的学生姓名和所在系
select s2.sname,s2.sdept
from student s1 join student s2
on s1.sdept=s2.sdept --是同一个系的学生
where s1.sname='刘晨 ' --s1表作为查询条件表
and s2.sname!='刘晨' --s2表作为结果表，并从中去掉“刘晨”本人
46.查询与“数据结构”课程在同一个学期开设的课程的课程名和开学学期
select c1.cname,c1.semester --c1作为查询结果表
from course c1 join course c2
on c1.semester=c2.semester --是同一学期开设的课程
where c2.cname='数据结构' --c2表作为查询条件表
47.外连接是只限制一张表中的数据必须满足连接条件，而另一张表中的数据不必满足连接条件.外连接分为左外连接和右外连接两种
左外连接的含义是限制表2中的数据必须满足连接条件，而不管表1中的数据是否满足连接条件，均输出表1中的内容；右外连接的含义是限制表1中的数据必须满足连接条件，而不管表2中的数据是否满足连接条件，均输出表2中的内容.
48.查询全体学生的选课情况，包括选了课的学生和没有选课的学生
这个查询需要输出全体学生（student表中的全部数据）的信息，而不管这个学生是否选了课程（若没选课，则在sc表中将没有该学生的学号，即这些学生将不能满足连接条件：student.sno=sc.sno）
selecct s.sno,sname,cno,grade
from student s
left outer join sc
on s.sno=sc.sno
注意：在连接结果中，将一个表中不满足连接条件的数据构成的元组中的来自其他表的列均置成null
此查询也可以用右外连接实现
select s.sno,sname,cno,grade
from sc right outer join student s
on s.sno=sc.sno
49.查询没有人选的课程的课程名
分析：如果某门课程没有人选，则必定在course表中有，但在sc表中没出现的课程，即进行外连接时，没有人选的课程对应在sc表中相应的sno,cno或grade列上必定是空值，因此查询时只要在连接后的结果中选出sc表中sno为mull或者cno为mull的记录即可.
select cname from course c left join sc
on c.cno=sc.cno
where sc.cno is null
50.查询计算机系没有选课的学生，列出学生的姓名和性别
select sname,ssex
from student s left join sc on s.sno=sc.sno
where sdept='计算机系'
and sc.sno is null
51.统计计算机系每个学生的选课门数，包括没有选课的学生
select s.sno as 学号,count(sc.cno) as 选课门数
from student s left join sc on s.sno=sc.sno
where sdept='计算机系'
group by s.sno
52.查询信息管理系选课门数少于3门的学生的学号和选课门数，包括没选课的学生.查询结果按选课门数递增排序
select s.sno as 学号，count(sc.cno) as 选课门数
from student s left join sc on s.sno=sc.sno
where sdept='信息管理系'
group by s.sno
having count(sc.cno)<3
order by count(sc.cno) asc
53.top子句写在select单词的后边（如果有distinct，则在distinct单词后）,查询列表的前边
查询年龄最大的三个学生的姓名、年龄及其所在系
select top 3 sname,sage,sdept
from student
order by sage desc
若要包括年龄并列第3名的所有学生，则此句可写成
select top 3 with ties sname,sage,sdept
from student
order by sage desc
注意：如果在top子句中使用了with ties谓词，则要求必须使用order by子句对查询结果进行排序，否则会出现语法错误
54.查询vb考试成绩最高的前三名学生的姓名、所在系和vb考试成绩
select top 3 with ties sname,sdept,grade
from student s join sc on s.sno=sc.sno
join course c on c.cno=sc.cno
where cname='vb'
order by grade desc
55.查询选课人数最少的两门课程（不包括没有人选的课程）,列出课程号和选课人数
select top 2 with ties cno,count(*) 选课人数
from sc
group by cno
order by count(cno) asc
56.查询计算机系选课门数超过两门的学生中考试平均成绩最高的前两名（包括并列情况）学生的学号、选课门数和平均成绩
select top 2 with ties s.sno,count (*) 选课门数,avg(grade) 平均成绩
from student s join sc on s.sno=sc.sno
where sdept='计算机系'
group by s.sno
having count(*)>2
order by avg(grade) desc
57.查询选了vb课程的学生的学号、姓名、所在系和成绩，计算机系显示cs ,信息管理系显示im，通信工程系com
select s.sno 学号,sname 姓名,
case sdept
when '计算机系' then 'cs'
when '信息管理系' then 'im'
when '通信工程系' then 'com'
end as 所在系，grade 成绩
from student s join sc on s.sno=sc.sno
join course c on c.cno=sc.cno
where cname='vb'
58.查询计算机系学生的学号、姓名、性别和年龄，并将查询结果保存到新表student_cs中
select sno,sname,ssex,sage
into student_cs
from student where sdept = '计算机系'
59.查询与刘晨在同一系学习的学生
select sno,sname,sdept from student --外层查询
where sdept in(select sdept from student where sname='刘晨') --子查询
60.使用子查询实现：查询考试成绩大于90分的学生的学号和姓名
select sno,sname from student
where sno in(select sno from sc where grade > 90)
61.查询选了"c004"号课程且成绩高于此课程平均成绩的学生的学号和该门课程成绩
select sno,grade from sc
where cno='c004' and grade<(
select avg(grade) from sc where cno='c004')
62.exists的含义是：当子查询中有满足条件的数据时，返回真值，否则返回假值
not exists的含义是：当子查询中有满足条件的数据时，返回假值，否则返回真值
查询没选“c001”号课程的学生的姓名和所在系
a.在子查询中否定
select sname,sdept from student
where exists(
select * from sc
where sno = student.sno
and cno!='c001')
b.在外层查询中否定
select sname,sdept from student
where not exists(
select * from sc
where sno = student.sno
and cno = 'c001')
63.将一个新生插入student表中，其学号为0821105，姓名为陈冬，性别为男，年龄为18岁，信息管理系学生
insert into student values('0821105','陈冬,'男',18,'信息管理系')
在sc表中插入一条新纪录，学号为“0821105”，选修的课程号为“c001”，成绩暂缺
insert into sc(sno,cno) values('0821105','c001')
注意：由于提供的常量值个数与表中的列个数不一致，因此在插入时必须列出列名.而且，sc表中的grade列必须允许为null
64.创建一个新表，然后将计算机系每个学生的姓名、选的课程名和考试成绩插入到此表中
（1）创建表
create table cs_student(
sname varchar( 20),
cname varchar(40),
grade tinyint)
(2)插入数据
insert into cs_student
select sname,cname,grade from student s
join sc on s.sno=sc.sno
join course c on c.cno=sc.cno
where sdept='计算机系'
65.无条件更新
将所有学生的年龄加1
update student set sage=sage+1
66.有条件更新
（1）基于本表条件的更新
将“0811104”号学生的年龄改为18岁
update student set sage = 18
where sno='0811104'
(2)基于其他表条件的更新
将计算机系全体学生的成绩加5分
-用子查询实现
update sc set grade=grade+5
where sno in
(select sno from student where sdept='计算机系')
-用多表连接实现
update sc set grade=grade+5
from sc join student on sc.sno=student.sno
where sdept='计算机系'
67.修改全体学生的vb考试成绩：对通信工程系学生，成绩加10分。对信息管理系学生，成绩加5分。对其他系学生，成绩不变
update sc set grade =grade+
case sdept
when '通信工程系' then 10
when '信息管理系' then 5
else 0
end
from student s join sc on s.sno=sc.sno
join course c on c.cno=sc.cno
where cname='vb'
68.无条件删除
删除所有学生的选课记录
delete from sc --sc成空表
有条件删除
（1）基于本表条件的删除
删除所有不及格学生的修课记录
delete from sc where grade < 60
(2)基于其他表条件的删除
删除计算机系不及格学生的修课记录
-用子查询 实现
delete from sc
where grade<60 and sno in(
select sno from student
where sdept ='计算机系')
-用多表连接
delete from sc
from sc join student on sc.sno=student.sno
where sdept='计算机系' and grade<60
