目录
1.3数据库管理系统DataBase Management System,DBMS
消除重复项:select distinct(列名1，列名2...) from 表名
显示查询结果的前n行(或%）:用 top n [percent]
将两个或多个基表、视图的连接组成的复杂查询创建为视图——最常用的情况
将两个或多个基表、视图的连接组成的复杂查询创建为视图——最常用的情况
2. 显示磁盘读取信息：分析执行该数据查询所花费的磁盘活动量
1、基本数据库相关概念
1.1数据Data
数据是数据库中存储的基本对象，是信息的一种表现形式。
可以包括：数字、文字，声音、图形、图像、视频等其他信息。
信息则是数据的内涵,是对数据语义的解释。
A：数据有“型”和“值”之分。
B：数据受数据类型和取值范围的约束
C：数据需要载体并具有多种形式。
D：数据有定性和定量之分。
1.2数据库DataBase,DB
数据库是长期储存在计算机内、有组织的、统一管理的、可共享的大量数据的集合。
基本特征：
1、数据按一定的数据模型组织、描述和储存
2、冗余度较小、有较高的数据独立性和易扩展性
3、具有集成性、共享性、海量性和持久性
数据独立性包括：物理独立性、逻辑独立性。
数据模型：确定系统中数据的定义和格式。
数据模型的类型：概念数据模型、逻辑数据模型（层次、网状、关系）、物理数据模型
数据模型的三要素：数据结构（描述数据库对象静态特征，如类型、内容、性质、与数据之间的练习）、数据操作（描述数据库对象动态特征，如查询、插入、删除、修改等操作。）、数据完整性约束(描述数据结构内数据间的语法、词义联系等等）。
层次模型：树状结构，参考数据结构中的树概念。
网状模型：网一样的结构，参考数据结构中的图。
关系模型：数据库、表、记录、字段。参考excel的样子。行为记录，列为字段，表就是表，数据库就是多个表。
1.3数据库管理系统DataBase Management System,DBMS
介于用户与操作系统之间的一层数据管理软件。DBMS为用户提供访问DB的方法，能为数据库提供数据定义、维护、查询、更新、统计等操作功能,并具有管理数据的完整性、安全性以及各种数据控制。
典型数据库产品：ACCESS（也是微软的，数据量比较小）、MYSQL（企业用的多）、SQL Sever、Oracle、MongoDB等。
1.4数据库系统 DataBase System DBS
DBS是实现有组织地、动态地存储大量关联数据、方便多用户访问的计算机硬件、软件和数据资源组成的系统。即引入数据库技术的计算机系统。
构成：数据库(DB)、数据库管理系统(DBMS)(及其开发工具)、应用系统、数据库管理员（DBA）
[硬件->操作系统->数据库DB->数据库管理软件(DBMS)]DBS->DBA
1.5数据库系列架构
C/S二层，客户端-数据库服务器。C/S三层，客户端-应用服务器-数据库服务器。
B/S，浏览器-WEB服务器-数据库服务器。
2、SQL sever
基本概念
Transact-sql，是结构化查询语言，是一种用于存取和查询数据、更新并管理关系数据库系统的数据库查询和编程语言。
sql不是独立的程序设计语言，通常嵌入到其他程序设计语言（java、python等）中使用。
sql是标准数据库语言，从而对数据库的随机查询到数据库的管理和程序设计，sql功能想当丰富。
sql分数据定义语言、数据操纵语言、数据控制语言。
数据定义：create table //创建表、alter table//修改表的属性、drop table//删除表。等等
数据操纵：select//查询、delete//删除数据、insert//向一个表中添加数据、update//更新数据
数据控制：grant//授予权限、remove//回收授予的权限
如果想要复制已经附加进去的数据库，需要先将其分离。
日志库不一定要附加，没有的话会自动生成新的。
这里是选择在哪个数据库中查询。但如果不是选错了。可以强行在查询页中 Use 数据库名即可，可以切换数据库。
以下是使用的数据库
单表查询
select *from 表名 可以查询表中所有信息。
select <列名> [{,<列名>] from <表名或视图名> [{,<表名或视图名>] where <检索条件> [group by <列名1> [Having<条件表达式>]] [order by <列名2> [ASC|DESC]]查询的结果是一个表。
查询的过程：
1、根据where子句的检索条件，从from子句指定的基本表或视图中选取满足条件的元组，再按照select子句中指定的列，投影得到结果表。
2、如果有group子句，则将查询结果按照<列名1>相同的值进行分组。
3、如果group后面有having ，则只输出满足having条件的元组。
4、如果有order，查询结果还要按照<列名2>的值进行排序
查询所有列的信息，用*
查询部分列：列名1，列名2
消除重复项:select distinct(列名1，列名2...) from 表名
显示查询结果的前n行(或%）:用 top n [percent]
--查询结果前6行 select top 6 * from student --查询前10%行 select top 6 percent * from student修改查询结果中列的标题
--将要显示的列标题用单引号括起来后，写在列名后面，两者之间使用空格隔开。 select StuNo '学号',StuName '姓名',ClassNo '班级号',Pwd '密码' from Student --将要显示的列标题用单引号括起来后接等号(=),后接要检索的列名 select '学号'=StuNo ,'姓名'=StuName from Student --将要显示的列标题用单引号括起来后,写在列名后面，两者之间用as关键字 select StuNo as '学号',StuName as '姓名' from Student在查询结果中增加显示的字符串:
select couno,'课程名为：',CouName from Course
表达式作为select语句的列：
select *,WillNum/LimitNum '报名人数与限选人数之比' from Course
使用聚合函数及统计汇总查询作为select语句结果的列：
统计课程的总门数； select COUNT(*) '总课程数' from Course 如果计算的列中有值是null，那么null是不会计入的使用where限制查询的条件:
相等条件： 显示部门编号为“01”的部门名称； select DepartName from Department where DepartNo='01' !=和<>都是不等于 比较大小： 查询限选人数大于10以上的课程名称 select CouName from Course where LimitNum>=10 如果遇到多重查询条件 需要and(左右皆为真，则符合条件) or(左右有1为真，则符合条件) not(符合条件的变为不符合，相反) 默认优先级not and or,括号可改优先级。 --查询课程类别为信息技术且学分为2的数据。 select * from Course where Kind='信息技术' and Credit='2' --查询课程类别为信息技术或者学分为2的数据。 select * from Course where Kind='信息技术' or Credit=2 --查询课程类别为信息技术或者学分不为2的数据。 select * from Course where Kind='信息技术' or Credit!=2 确定范围： 使用between ... and (或not between ... and) 在范围内或不在范围内 select * from Course where WillNum between 15 and 25 select * from Course where WillNum>=15 and WillNum<=25 select * from Course where WillNum not between 15 and 25 select * from Course where WillNum<15 or WillNum>25 in: 查询课程编号为004,007 select * from Course where CouNo='004' or CouNo='007' select * from Course where CouNo in ('004','007') select * from Course where CouNo not in ('004','007')模糊查询
like (或者not like) <属性名> like <字符串常量> --不姓李的学生信息 select * from Student where StuName like '[^李]%' --查询以“制作”两字结尾的课程名（使用LIKE） select CouName from Course where CouName like '%制作' --查询姓名的第二个字为“丽”的学生信息，要求显示学生学号和姓名 select StuNo,StuName from Student where StuName like '_丽%' --查询不姓“刘”的学生信息（使用NOT LIKE） select * from Student where StuName like '[^刘]%'使用IS NULL关键字 查询列中没有赋值的数据行
某个字段没有值称之为具有空值(NULL)
通常没有为一个列输入值时,该列的值就是空值。
空值不同于零和空格,它不占任何存储空间。
--教师为空的 select * from Course where Teacher is null --非空 select * from Course where not Teacher is null总的举例：
--从studen表中查询学号为00000001的的学生的姓名（StuName）和选课密码(Pwd) select StuName,Pwd from Student where StuNo='00000001' --（1）从课程表中查询课程类别，要求值相同的数据行只保留一行。 select distinct (Kind) from Course --（2）从学生表中查询所有信息，要求只显示查询结果的前5行数据。 select top 5 * from Student --（3）从学生表中查询所有信息，要求只显示查询结果的前15%的数据。 select top 15 percent * from Student --（4）查询学生表中的学号、班级编号和姓名信息，并修改列标题为汉字标题； select StuNo '学号',ClassNo '班级编号',StuName '姓名'from Student --（5）查询所有课程的课程编号、课程名称以及课程报名人数与限选人数之比； select CouNo,CouName,WillNum/LimitNum '报名人数与限选人数之比'from Course --（6）统计课程的总门数； select COUNT(*) '总课程数' from Course --（7）统计所有课程的平均报名人数、报名总人数和平均限选人数； select AVG(WillNum) '平均报名人物',SUM(WillNum) '报名总人数', AVG(LimitNum) '平均限选人数' from Course --（8）所有课程的总的限制选修人数是多少？ select SUM(LimitNum) '总的限制选修人数' from Course --（9）显示部门编号为“01”的部门名称； select DepartName from Department where DepartNo='01' --（10）查询部门编号为“02”的班级名称； select ClassName from Class where DepartNo='02' --（11）“信息技术”类课程的报名总人数为多少？ select sum(WillNum) from Course where Kind='信息技术' --（12）查询出课程类型为“管理”的所有课程信息； select * from Course where Kind='管理' --（13）查询部门名称为“信息工程学院”的部门编号。 select DepartNo from Department where DepartName='信息工程学院' --（14）查询上“数据库原理与设计”课程的课程编号、上课时间和教师； select CouNo,SchoolTime,Teacher from Course where CouName='数据库原理与设计' --（15）查询限报人数在10到20人之间的课程信息。 select * from Course where LimitNum between 10 and 20 --（16）查询限报人数不在10到20人之间的课程信息。 select * from Course where LimitNum not between 10 and 20 --（17）查询限选人数在15人及以上的课程名称、学分及上课时间。 select CouName,Credit,SchoolTime from Course where LimitNum>=15 --（18）查询学分大于等于2.0，且限选人数在15人以上的课程名称。 select CouName from Course where LimitNum>15 and Credit>=2.0 --（19）查询选课表中随机数为空的选课信息。 select * from StuCou where RandomNum is null --（20）查询课程编号为'001'、'004'、'008'的课程名称。（两种方法） select CouName from Course where CouNo='001' or CouNo='004' or CouNo='008' select CouName from Course where CouNo in('001','004','008') --（21）查询学号不是00000012或00000004的学生的学号、姓名和班级号。 select StuNo,StuName,ClassNo from Student where StuNo not in('00000012','00000004') --（22）查询包含“应用”两字的课程名； select CouName from Course where CouName like '%应用%' --（23）查询姓名的第二个字为“丽”的学生信息，要求显示学号、姓名和班级； select StuNo,StuName,ClassNo from Student where StuName like '%丽%' --（24）查询不姓“刘”的学生信息（两种方法）； select * from Student where StuName like '[^刘]%' select * from Student where StuName not like '[刘]%' --（25）查询部门名称中含有“工程”两个字的部门名称。 select DepartName from Department where DepartName like '%工程%' --（26）查询姓“张”、“陈”、“黄”同学的基本信息。 select * from Student where StuName like '[张,陈,黄]%' --（27）查询姓名的第三个字为“华”的学生信息，要求显示学生学号和姓名 select StuNo,StuName from Student where StuName like '__华%' --（28）信息技术”类课程的报名总人数为多少？ select sum(WillNum) from Course where Kind='信息技术'Order by
select <列名> [{,<列名>] from <表名或视图名> [{,<表名或视图名>] where <检索条件> [group by <列名1> [Having<条件表达式>]] [order by <列名2> [ASC|DESC]] [order by <列名1> [ASC|DESC]] <列名2> [ASC|DESC]] ASC是升序，DESC是降序。 先按列名1排序，列名1相同，再按列名2排序 --1、查询课程信息、报名人数与限选人数之比，要求查询结果按报名人数升序，限选人数降序排序。 select *,WillNum/LimitNum from Course order by WillNum asc,LimitNum desc --2、查询是周二上课的课程名称、学分、上课时间和教师姓名，要求查询结果按学分升序，课程名称降序排序。 select CouName,Credit,SchoolTime,Teacher from Course where SchoolTime like '%周二%' order by Credit asc,CouName desc排序函数
SQL Server排序函数能将查询结果按照所指定的列排出顺序，还可根据需要给出有间断的排序和没有间断的排序。
--查询课程表的所有信息，要求查询结果按照报名人数排序，并且返回每一行的序号 SELECT ROW_NUMBER() OVER(ORDER BY WillNum) 'Row Number',* FROM Course --查询课程表的所有信息，要求查询结果按照报名人数排序，并且返回有间断的每一行的排序。 SELECT RANK() OVER(ORDER BY WillNum) 'RANK Number',* FROM Course --查询课程表的所有信息，要求查询结果按照报名人数排序，并且返回没有间断的每一行的排序。 SELECT DENSE_RANK() OVER(ORDER BY WillNum) 'DENSE_RANK Number',*FROM CourseGroup by
select <列名> [{,<列名>] from <表名或视图名> [{,<表名或视图名>] where <检索条件> [group by <列名1> [Having<条件表达式>]] [order by <列名2> [ASC|DESC]] [group by 列名1，列名2 [Having<条件表达式>]] 按列名1、列名2分组。 Having里面的条件表达式，是针对前面的分组条件的。 HAVING子句中的列只能是GROUP BY子句中的列或者聚合函数 聚合函数可以在SELECT语句和HAVING语句中使用，但是不能在WHERE子句中使用 GROUP BY子句经常用于SELECT子句中包含有聚合函数的情况。此时，SELECT子句中选项列表中出现的列，只能是GROUP BY子句中的列或者包含在聚合函数内部中。子查询
先执行子查询再执行父查询
独立子查询：子查询的结果不依赖父查询
带比较运算符的子查询
查询报名人数大于平均报名人数的课程编号、课程名称和报名人数。 SELECT Couno,Couname,Willnum FROM Course WHERE WillNum>(SELECT AVG(WillNum) FROM Course ) !!!:from后面也可以跟子查询谓词IN
查询选修了“002”课程的学生学号和姓名 SELECT StuNo, StuName FROM Student WHERE StuNo IN ( SELECT StuNo FROM StuCou WHERE CouNo='002') 查询没有选修 “002”课程的学生学号和姓名 SELECT StuNo, StuName FROM Student WHERE StuNo not IN ( SELECT StuNo FROM StuCou WHERE CouNo='002')量词ANY或ALL
查询其他系中比01系某一课程报名人数小的课程信息。 SELECT * FROM Course WHERE Departno <> '01' AND Willnum<ANY ( SELECT WillNum FROM Course WHERE Departno='01') 查询其他系中比01系所有课程报名人数都小的课程信息。 SELECT * FROM Course WHERE Departno <> '01' AND Willnum<ALL ( SELECT WillNum FROM Course WHERE Departno='01') 查询其他系中比01系某一课程报名人数小的课程信息。（即查询其他系课程报名人数小于01系最大报名人数的课程信息） SELECT * FROM Course WHERE Departno <> '01' AND Willnum<( SELECT MAX(WillNum) FROM Course WHERE Departno='01') 查询其他系中比01系课程报名人数都小的课程信息。（即查询其他系课程报名人数小于01系最小报名人数的课程信息） SELECT * FROM Course WHERE Departno <> '01' AND Willnum<( SELECT MIN(WillNum) FROM Course WHERE Departno='01')
谓词EXISTS
查询选修了“002”课程的学生学号和姓名 SELECT StuNo, StuName FROM Student WHERE StuNo IN ( SELECT StuNo FROM StuCou WHERE CouNo='002') 或者这样写 SELECT StuNo, StuName FROM Student WHERE EXISTS ( SELECT * FROM StuCou WHERE StuCou.StuNo=Student.StuNoAND CouNo='002') 查询没有选修“002”课程的学生学号和姓名 SELECT StuNo, StuName FROM Student WHERE NOT EXISTS ( SELECT * FROM StuCou WHERE StuCou.StuNo=Student.StuNoAND CouNo='002')举例：
1、查询“工程技术”类课程总的限制选修人数和平均限制选修人数，并按总的限制选修人数降序排序。 select sum(LimitNum) '总的限制选修人数', avg(LimitNum) '平均限制选修人数'from Course where Kind='工程技术' order by sum(LimitNum) desc 2、查询课程信息、报名人数与限选人数之比，要求查询结果按报名人数升序，限选人数降序排序。 select *,WillNum/LimitNum from Course order by WillNum asc,LimitNum desc 3、查询是周二上课的课程名称、学分、上课时间和教师姓名，要求查询结果按学分升序，课程名称降序排序。 select CouName,Credit,SchoolTime,Teacher from Course where SchoolTime like '%周二%' order by Credit asc,CouName desc 8、按课程类别统计每类课程的平均限选人数。 select Kind,avg(LimitNum) '平均限选人数'from Course group by Kind 9、按系部编号统计各系的班级个数。 select DepartNo,count(ClassName) '班级个数'from Class group by DepartNo 10、查询平均报名人数大于10人并且平均限选人数大于15的课程类别和每类平均限选人数。 select Kind,avg(LimitNum) '平均限选人数'from Course group by Kind having avg(WillNum)>10 and avg(LimitNum)>15 11、查询选课门数最多的学生的学号。 select StuNo from StuCou group by StuNo having StuNo in (select StuNo from StuCou group by StuNo having count(CouNo)= (select top 1 count(CouNo) from StuCou group by StuNo order by count(CouNo) desc)) 12、查询选课门数最多的学生的学号、姓名。 select StuNo '学号',StuName '姓名'from Student where StuNo in(select StuNo from StuCou group by StuNo having StuNo in (select StuNo from StuCou group by StuNo having count(CouNo)= (select top 1 count(CouNo) from StuCou group by StuNo order by count(CouNo) desc))) 13、查询选修了课程号为’004’课程或选修了’017’课程的学生的学号。 select distinct(StuNo) from StuCou where CouNo in('004','017') 14、查询既选修了课程号为’004’课程又选修了’017’课程的学生的学号。 select distinct(StuNo) from StuCou where StuNo in (select StuNo from StuCou where CouNo in('004')) and StuNo in (select StuNo from StuCou where CouNo in('017')) 15、查询选修了课程名包含‘Linux’的学生学号、姓名。 select StuNo '学号',StuName '姓名' from Student where StuNo in (select StuNo from StuCou where CouNo in (select CouNo from Course where CouName like '%Linux%')) 16、按课程类别统计每类课程的平均限选人数和平均报名人数。 select Kind,avg(LimitNum) '平均限选人数',avg(WillNum) '平均报名人数' from Course group by Kind 17、按系部编号统计各系的班级个数。 select DepartNo,COUNT(ClassName) from Class Group by DepartNo 18、分组统计各班的学生人数。 select ClassNo,count(*) '学生人数' from Student group by ClassNo 19、在课程表中，按所开设课程的系部编号分别统计总的报名人数。 select DepartNo,sum(WillNum) '报名人数' from Course group by DepartNo 20、查询平均报名人数大于10人并且平均限选人数大于15的课程类别和每类平均限选人数。 select Kind,avg(LimitNum) '平均限选人数' from Course group by Kind having avg(LimitNum)>15 and avg(WillNum)>10 22、查询平均限选人数小于等于20并且最大限选人数大于10的各类课程的平均限选人数和最大限选人数。 select Kind,avg(LimitNum) '平均限选人数',max(LimitNum) '最大限选人数' from Course group by Kind having avg(LimitNum)<=20 and max(LimitNum)>10 查询既选修了课程名为’ Linux操作系统’课程又选修了’世界旅游’课程的学生的学号。 select StuNo from Student where StuNo in (select StuNo from StuCou where CouNo in(select CouNo from Course where CouName='Linux网络操作系统')) and StuNo in (select StuNo from StuCou where CouNo in(select CouNo from Course where CouName='世界旅游 '))关系模型及其定义
关系模型由关系数据结构、关系操作集合和完整性约束三部分组成
某个域的基数，就是分量的数量。
候选码（码、关键字）：关系中能唯一地标识一个元组的属性或属性组。 主码（主键、主关键字）：当一个关系有多个候选码时，应选定其中一个候选码为主码。 主属性：候选码中的属性称为主属性。 全码：若关系中只有一个侯选码，且这个侯选码中包括全部属性，则这种侯选码称为全码。 候选码是一个属性或一组属性，主属性则指单个的属性。 如果候选码由单个属性组成，主属性就是候选码。 如果候选码由多个属性组成，主属性就不是候选码。
关系代数
传统关系代数
行相乘，列相加
专门关系运算
选择
投影
连接
除
综合举例
多表查询
多表查询来自多个基本表或视图的信息，需要用到基本表的连接
SELECT * FROM Department, Class 结果中，行数是2个表的行数相乘，列数2个表的列数相加。 结果就是个笛卡尔积
相等连接查询
左图加入了连接条件，成为相等连接，右图去掉了相同列，并且在连接的基础上加入了选择条件。
举例：
查询学生选修课程的情况，要求显示班级名称、学号、姓名、课程名称和上课时间。 SELECT ClassName,Student.StuNo,StuName,CouName,SchoolTime FROM Class,Student,StuCou,Course WHERE Class.ClassNo=Student.ClassNo and Student.StuNo=StuCou.StuNo and StuCou.CouNo=Course.CouNo
说明：在引用的多表中，如果列名在多个表中同名，为了避免列名不明确，SELECT子句中必须在列名前加上表的前缀，即“表名.列名”。
比较连接查询
连接条件不是等号，而是比较运算符
举例：
查询每个班可以选修的、不是本系开设的选修课程信息， 显示信息包括班级名称、课程名、课程类别、学分、老师、上课时间和报名人数。 SELECT ClassName,CouName,Kind,Credit,Teacher,SchoolTime,WillNum FROM Class, Course WHERE Class.DepartNo <> Course.DepartNo
使用别名查询
简化连接条件的书写
举例：
查询学生选课信息，要求显示姓名、课程名称、志愿号，按姓名降序和志愿号升序排序。 SELECT StuName,CouName,WillOrder FROM Student S,StuCou SC,Course C WHERE S.StuNo=SC.StuNo and SC.CouNo=C.CouNo ORDER BY StuName DESC,WillOrder自连接查询
一个表和自身进行连接
举例：
查询课程类别相同而系部编号不同的课程信息， 要求显示课程编号、课程名称、课程类别和系部编号， 并按照课程编号升序排序查询结果。 SELECT C1.CouNo,C1.CouName,C1.Kind,C1.DepartNo FROM Course C1,Course C2 WHERE C1.Kind=C2.Kind and C1.DepartNo<>C2.DepartNo ORDER BY C1.CouNo所有举例：
--查询班级信息，要求显示班级编号、班级名称、班级系部编号、系部名称。 select ClassNo '班级编号',ClassName '班级名称',Class.DepartNo '班级系部编号',DepartName '系部名称' from Class,Department where Class.DepartNo=Department.DepartNo 查询学生信息，显示信息包括学生基本信息和班级名称。 select Student.*,Class.ClassName from Student,Class where Student.ClassNo=Class.ClassNo 查询学生选课信息，要求显示学生姓名、课程名称、志愿号，按姓名和志愿号排序。 select Student.StuName,CouName,ChoseNum from Student,StuCou,Course where Student.StuNo=StuCou.StuNo and StuCou.CouNo=Course .CouNo order by Student.StuName,Course.ChooseNum asc 查询学生报名“信息工程学院”开设的选修课程情况，显示信息包括学生姓名、课程名称和授课教师。 select StuName,CouName,Teacher from Student,StuCou,Course,Department where Student.StuNo=StuCou and StuCou.CouNo=Course.CouNo and Department.DepartName='信息工程学院' 查询“林斌”同学的选修课信息包括课程编号、课程名称、上课时间。 select Course.CouNo,SchoolTime from Student,StuCou,Course where Student.StuName='林斌' and Student.StuNo =StuCou.StuNo and StuCou.CouNo=Course.CouNo 查询学分大于等于2.5的课程信息，显示课程名称、学分、开设该课程的系部名称、报名人数，要求使用别名。 select CouName,Credit,DepartName ,WillNum from Department dp,Course cs where dp.DepartNo =cs.DepartNo and Credit>=2.5 1、查询每个班级可以选修的、不是自己所在部门开设的选修课程的信息，显示信息包括班级信息、课程名、课程类别、学分、教师，上课时间、报名人数、限选人数。 select ClassName,CouName,Kind,credit,Teacher,SchoolTime,WillNum,LimitNum from Class,Course where Class.DepartNo<>Course.DepartNo 2、查询“金融贸易学院”学生选修课程的情况，要求显示学号、姓名、部门名称、班级名称、课程名称、上课时间和教师. select Student.StuNo,Student.StuName,DepartName,CouName,SchoolTime,Teacher from Department,StuCou,Student,Course where Course.CouNo=StuCou.CouNo and Student.StuNo=StuCou.StuNo and Department.DepartName='金融贸易学院' 3、查询“00多媒体”班学生选修课程的情况，要求显示班级名称、学号、姓名、课程名称和上课时间. select ClassName,Student.StuNo,Student.StuName,CouName,SchoolTime from StuCou,Student,Course,Class where Student.StuNo=StuCou.StuNo and StuCou.CouNo=Course.CouNo and Class.ClassNo=Student.ClassNo 4、查询班级信息，要求显示班级编号、班级名称、班级部门编号、部门名称。 select ClassNo,ClassName,Department.DepartNo,DepartName from Class,Department where Class.DepartNo=Department.DepartNo 5、查询“00数据库”班学生信息，显示信息包括学生基本信息和班级名称。 select Student.*,ClassName from Student,Class where Student.ClassNo=Class.ClassNo and Class.ClassName='00数据库' 6、查询学生选课信息，要求显示学生姓名、课程名称、志愿号，按姓名和志愿号排序。 select StuName,Course.CouName,WillOrder from Student,StuCou,Course where Student.StuNo=StuCou.StuNo and StuCou.CouNo=Course.CouNo order by Student.StuName,WillOrder asc 7、查询学生选课表中报名状态为“报名”的课程名称。 select CouName from StuCou,Course where StuCou.CouNo=Course.CouNo and StuCou.State='报名' 8、查询“管理学院”学生选修课程情况,显示学生姓名、课程名称、授课教师和学生所在部门名称信息。 select StuName,CouName,Teacher,DepartName from Student,Course,Department,StuCou,Class where Student.StuNo=StuCou.StuNo and StuCou.CouNo= Course.CouNo and Student.ClassNo=Class.ClassNo and Class.DepartNo=Department.DepartNo and Department.DepartName='管理学院' 9、查询“管理学院”学生选修课程情况，显示学生姓名、学生所在部门名称信息、课程名称、授课教师和课程开设部门名称信息。 select StuName,d1.DepartName,CouName,Teacher,d2.DepartName from Student,Course,Department d1,StuCou,Class,Department d2 where Student.ClassNo=Class.ClassNo and Class.DepartNo=d1.DepartNo and Student.StuNo=StuCou.StuNo and StuCou.CouNo=Course.CouNo and d1.DepartName='管理学院' and d2.DepartNo=Course.DepartNo 10、按部门统计课程表的相关信息，要求显示部门编号、部门名称、课程门数、平均报名人数、报名总人数、最多报名人数、最小报名人数，即结果显示类似如下图。 select DepartNo '部门编号',(select DepartName from Department where Course.DepartNo=Department.DepartNo) '部门名称', count(CouName) '课程门数',avg(WillNum) '平均报名人数',sum(WillNum) '报名总人数' ,MAX(WillNum) '最多报名人',min(WillNum) '最小报名人数' from Course group by DepartNo 11、查询选课门数最多的学生的学号。 select StuNo from StuCou group by StuNo having count(*)=(select top 1 count(*) from StuCou group by StuNo order by count(*) desc) 12、查询选课门数最多的学生的学号、姓名。 select StuNo,StuName from Student where StuNo in (select StuNo from StuCou group by StuNo having count(*)=(select top 1 count(*) from StuCou group by StuNo order by count(*) desc)) 13、查询报名人数大于平均报名人数的课程编号、课程名称和报名人数和所开设课程的部门名称。 select Course.CouNo,Course.CouName,WillNum,DepartName from Course,Department where Course.DepartNo=Department.DepartNo and WillNum>(select avg(WillNum) from Course)
外连接查询和内连接
外连接
SELECT 〈目标列表达式1〉[,〈目标列表达式2 〉,…] FROM〈左表〉LEFT|RIGHT|FULL [OUTER] JOIN 〈右表〉 ON 〈连接条件〉
左外连接
举例：
查询所有学生选修课程的情况，要求显示学号、姓名和课程名称， 没有选修课程的学生，课程名称显示为空。 SELECT Student.StuNo,StuName,CouName FROM Student LEFT JOIN StuCou ON Student.StuNo=StuCou.StuNo LEFT JOIN Course ON StuCou.CouNo=Course.CouNo
右外连接
举例：
查询所有学生选修课程的情况，要求显示学号、姓名和课程名称， 没有选修课程的学生，课程名称显示为空。 SELECT Student.StuNo,StuName,CouName FROM Student RIGHT JOIN StuCou ON StuCou.StuNo=Student.StuNo RIGHT JOIN Course ON Course.CouNo=StuCou.CouNo
全外连接
举例：
查询所有学生选修课程和所有课程被选修的情况，要求显示学号、姓名和课程名称， 没有选修课程的学生，课程名称显示为空，课程没有被选修时，学生学号和姓名为空。 SELECT Student.StuNo,StuName,CouName FROM Student FULL JOIN StuCou ON Student.StuNo=StuCou.StuNo FULL JOIN Course ON StuCou.CouNo=Course.CouNo
内连接
常规连接查询也称为内连接查询，在FROM子句中指定。
SELECT 〈目标列表达式1〉[,〈目标列表达式2 〉,…] FROM〈左表〉[INNER] JOIN 〈右表〉 ON 〈连接条件〉举例：
查询选修“数据库原理与设计”课程的学生学号、姓名和志愿号。 自然连接 SELECT Student.StuNo,StuName,WillOrder FROM Student,StuCou,Course WHERE Student.StuNo=StuCou.StuNo AND StuCou.CouNo=Course.CouNo AND CouName='数据库原理与设计' 这两种是一样的。 SELECT Student.StuNo,StuName,WillOrder FROM Student INNER JOIN StuCou ON Student.StuNo=StuCou.StuNo INNER JOIN Course ON StuCou.CouNo=Course.CouNo WHERE CouName='数据库原理与设计'
举例:
--1、查询学生报名“信息工程学院”开设的选修课程情况，显示学生姓名、课程名称、授课教师和课程开设部门名称等信息，并按课程名称降序排序。 select StuName,CouName,Teacher,DepartName from student join stucou on student.StuNo=StuCou.StuNo join course on StuCou.CouNo=Course.CouNo join Department on Course.DepartNo=Department.DepartNo where DepartName='信息工程学院' order by couname desc --2、查询学生报名“管理学院”开设的选修课程情况，显示学生姓名、学生所在部门名称、课程名称、授课教师和课程开设部门名称等信息 --，并按学生所在部门名称升序，课程名称降序排序。 select StuName,d1.DepartName,CouName,Teacher,d2.DepartName from student join Class on student.ClassNo=Class.ClassNo join Department d1 on Class.DepartNo=d1.DepartNo join StuCou on Student.StuNo=StuCou.StuNo join Course on StuCou.CouNo=Course.CouNo join Department d2 on Course.DepartNo=d2.DepartNo where d2.DepartName='管理学院' order by d1.DepartName asc,CouName desc --3、查询“管理学院”学生报名的选修课程情况，显示学生姓名、学生所在部门名称、课程名称、授课教师和课程开设部门名称等信息。 select StuName,d1.DepartName,CouName,Teacher,d2.DepartName from student join Class on student.ClassNo=Class.ClassNo join Department d1 on Class.DepartNo=d1.DepartNo join StuCou on Student.StuNo=StuCou.StuNo join Course on StuCou.CouNo=Course.CouNo join Department d2 on Course.DepartNo=d2.DepartNo where d1.DepartName='管理学院'; --4、查询“彭少帆”同学的选修课信息包括课程编号、课程名称、上课时间。 select Course.CouNo,CouName,SchoolTime from Student join stucou on Student.StuNo=StuCou.StuNo join course on StuCou.CouNo=Course.CouNo where StuName='彭少帆' --5、查询选课表中报名状态为‘报名’的学生学号、姓名以及课程编号和课程名称。 select student.StuNo,StuName,Course.CouNo,CouName from student join stucou on Student.StuNo=StuCou.StuNo join Course on StuCou.CouNo=Course.CouNo where State='报名' --6、查询学生选课情况，包含学生学号、姓名以及课程编号和课程名称，还要求显示未选课学生的学号和姓名以及未被学生选修的课程号和课程名称。 select Student.StuNo,StuName,Course.CouNo,CouName from student full join StuCou on Student.StuNo=StuCou.StuNo full join Course on StuCou.CouNo=Course.CouNo
前面内容小结：
会将要使用的数据库切换为当前数据库 查询时会使用星号（*）显示表的全部列。 会根据需要改变查询结果的列标题。 会使用DISTINCT消除查询结果的重复数据行 会使用TOP n [PERCENT]仅返回查询结果的前n（%）行。 会在查询结果中增加要显示的字符串。 会使用WHERE写出限制查询的条件。 会将表达式作为查询的列。 会使用ORDER BY按要求重新排序查询结果。 会在查询中根据需要使用列表运算符IN。 会在查询中根据需要使用范围运算符BETWEEN。 了解精确查询和模糊查询的区别。会使用LIKE及四个通配符（%、_、[ ]、 [^]）实现模糊查询。 会使用IS NULL查询指定列未输入值的数据行。 会根据需要使用聚合函数（SUM函数、MAX函数、MIN函数、COUNT函数、AVG函数）进行统计或汇总。 会根据需要分组查询结果。掌握GROUP BY 子句的使用。了解HAVING与WHERE的区别。 会实现子查询。 能根据需要按照要求排名查询结果。 了解笛卡儿积的概念。会实现交叉连接查询。 会根据需要实现多表连接查询，写出正确的表与表之间的连接条件。会实现内连接查询中的相等连接查询、自然连接查询、比较连接查询、自连接查询。 了解外连接要解决的问题。会实现外连接中的左外连接查询、右外连接查询和全外连接查询。 会定义表别名及使用表别名进行查询。
数据维护
1.直接用管理平台维护数据
2.sql语句进行数据维护
INSERT语句插入数据
不指定列的话，默认按插入表的列顺序赋值
-------------------------------------------------------------------------------------------------
举例：
INSERT Class VALUES ('20190103','19计本3班','01') INSERT Student VALUES('19010301','郑铭伟','20190103','123456') INSERT Student VALUES('19010302','徐依汲','20190103','123456') INSERT Student VALUES('19010303','张梓潼','20190103','123456') INSERT Student VALUES('19010304','周俊峰','20190103','123456') 使用INSERT语句和子查询添加班级信息：给信息工程学院添加一个新的班级， 班级编号为20190104，班级名称为19计本4班。 INSERT Class VALUES ('20190104', '19计本4班', (SELECT DepartNo FROM Department WHERE DepartName='信息工程学院'))
SELECT INTO语句添加数据
举例：
将Class表中2019级的班级数据抽取为Class19表中，该表在此数据库中不存在。 SELECT * INTO Class19 FROM Class WHERE ClassNo LIKE '2019%'
例：将Class表中的2001级的班级信息插入到Class19表中，该表在此数据库中已经存在。 INSERT Class19 SELECT * FROM Class WHERE ClassNo LIKE '2001%'
UNION语句合并数据
举例：
查询选修了课程号为“001”或者“002”的学生学号。 SELECT StuNo FROM StuCou WHERE CouNo = '001' UNION [all] SELECT StuNo FROM StuCou WHERE CouNo = '002' 加All不会去重，不加all会去重
上面的不加ALL等价于:
SELECT DISTINCT StuNo FROM StuCou WHERE CouNo = '001' OR CouNo = '002'
举例：
查看课程编号、课程名称及报名状态的情况，要求按课程号排序， 并将数据保存到CourseState表中，CourseState表在数据库中不存在。 ---------------------------------------------- 不保存 SELECT CouNo,CouName FROM Course UNION SELECT CouNo, State FROM StuCou ORDER BY CouNo --------------------------------------------- 保存 SELECT CouNo,CouName INTO CourseState FROM Course UNION SELECT CouNo,State FROM StuCou ORDER BY CouNo
根据结果：union是优先于插入表的，先合并了2个查询结果，再把结果插入到表中，并且对于合并，不同名同顺序的列，合并的时候会直接混在一起，选前一个列名.
UPDATE语句修改数据
举例：
例：用UPDATE语句修改学生表中数据：修改学号为19010301的学生姓名为“郑铭辉”， 并修改该学生所在班级的选课密码为“abc,123”。 UPDATE Student SET StuName = '郑铭辉' WHERE StuNo = '19010301' --------------------------- UPDATE Student SET PWD= '123456' WHERE ClassNo= (SELECT ClassNo FROM Student WHERE StuNo= '19010301')
举例：
例：用UPDATE语句修改“00多媒体”班学生的选课密码与郑铭辉同学的选课密码一样。 UPDATE Student SET PWD= (SELECT PWD FROM Student WHERE StuName = '郑铭辉') FROM Class, Student WHERE Class.ClassNo=Student.ClassNo and ClassName='00多媒体'
DELETE语句删除数据
举例：
例：学号为‘00000005’的学生因故取消编号为“004”的选修课程。 步1：删除学号为‘00000005’，课程编号为“004”的选课信息 Delete StuCou Where StuNo='00000005' and CouNo='004' 步2：更新Course表中课程编号为“004”课程的报名人数 Update Course Set WillNum=WillNum-1 Where CouNo='004' 例：学号为‘00000004’的学生因故取消 “中餐菜肴制作”的选修课程。 步1：删除学号为‘00000004’， 课程名称“中餐菜肴制作”的选课信息 Delete StuCou From StuCou,Course Where StuCou.CouNo=Course.CouNo and StuNo='00000004' and CouName='中餐菜肴制作' 步2：更新Course表中课程名称为“中餐菜肴制作” 的报名人数 Update Course Set WillNum=WillNum-1 Where CouName = '中餐菜肴制作'举例：
例：林斌同学因个人原因，申请退学，请帮忙在数据库中进行处理。 Delete Student Where StuName='林斌'会直接报错：
这个错误的原因是数据库中存在一个外键约束，它将两个表连接在一起。外键是一个字段或字段组合，它使数据库能够保持表之间的关系。当我们试图删除一个表中的数据时，外键约束将检查其他表中是否有引用了被删除数据的行。如果有引用，则会发生冲突并返回上述错误。
得这样弄
步1: 更新Course表中学生'林斌'选修课程的报名人数 Update Course Set WillNum=WillNum-1 From StuCou,Course,Student Where StuCou.CouNo=Course.CouNo and Student.StuNo=StuCou.StuNo and StuName='林斌' 步2: 删除学生'林斌'的选课记录 Delete StuCou From Student,StuCou Where Student.StuNo=StuCou.StuNo and StuName='林斌' 步3: 删除‘林斌’同学的基本信息 Delete Student Where StuName='林斌'针对这种错误信息，我们可以先把有涉及相关引用内容的数据先删掉，然后再删除该学生的信息。
总举例：
1、林斌所在的班级来了两位新同学，请你帮忙在数据库中进行操作。两位同学一个叫杨尚瑾，另外一位叫莫好园，学号按现有班级已有的顺延，密码设置为123456。 insert Student(StuNo,StuName,ClassNo,Pwd) values('00000061','杨尚瑾',(select classno from Student where StuName='林斌'),'123456' ) insert Student(StuNo,StuName,ClassNo,Pwd) values('00000062','莫好园',(select classno from Student where StuName='林斌'),'123456' ) 2、查询课程编号、课程名称以及报名状态的情况，并将查询数据按课程编号升序排序保存到名为“CourseState”新表中。 select Course.CouNo,CouName,State into CourseState from Course,StuCou where Course.CouNo=StuCou.CouNo order by course.CouNo asc 3、查询报名人数大于平均报名人数的课程编号、课程名称和报名人数和所开设课程的系部名称，并将查询结果抽取到“New_Course2”新表中。 select CouNo,CouName,WillNum,DepartName into New_Course2 from Course,Department where Course.DepartNo=Department.DepartNo and WillNum>(select avg(WillNum) from course) 4、将”02电子商务”班级学生的学号、姓名和选修的课程编号、课程名称抽取到名为My_Class新表中。 select student.StuNo,stuname,StuCou.CouNo,CouName into My_Class from Course,StuCou,Class,Student where Student.StuNo=StuCou.StuNo and StuCou.CouNo=Course.CouNo and Student.ClassNo=Class.ClassNo and ClassName='02电子商务' 5、查询报名人数小于等于平均报名人数的课程编号、课程名称和报名人数和所开设课程的系部名称，并将查询结果插入到“New_Course2”表中。 select Course.CouNo,CouName,WillNum,DepartName into New_Course2 from Course,Department where Course.DepartNo=Department.DepartNo and WillNum<=(select avg(WillNum) from course) 6、从Course表中将报名人数在40人或以上的数据复制到CourseWillNum新表中。 select * into CourseWillNum from course where WillNum>=40 7、从Course表中将报名人数在大于等于30和小于40之间的数据插入到CourseWillNum表中。 select * into CourseWillNum from course where WillNum>=30 and WillNum<40 8、把学号为’00000003’，选修课为’Java程序设计’的志愿号（WillOrder）改为4。 update stucou set WillOrder=4 where StuNo='00000003' and CouNo=(select couno from course where CouName='Java程序设计') 9、将“民俗风情旅游”课程的上课时间修改为“周四晚”。（提示：“民俗风情旅游”前后有空格或者换行。） update course set SchoolTime='周四晚' where CouName='民俗风情旅游 ' 10、将所在系部编号为“01”的学生选课密码初始化为“123@#456”。 update Student set Pwd='123@#456' where StuNo in(select stuno from Student,class where Student.ClassNo=class.ClassNo and DepartNo='01') 11、取消“民俗风情旅游”课程。（提示：“民俗风情旅游”前后有空格。） delete StuCou from StuCou where CouNo=(select couno from course where CouName='民俗风情旅游 ') delete Course from Course where CouName='民俗风情旅游 ' 12、“00000003”同学取消已选的”003”的课程。 delete StuCou from StuCou where StuNo='00000003' and CouNo='003' 13、”林斌”取消”世界旅游”的选修课程。 delete StuCou from Student,StuCou,Course where Student.StuNo=StuCou.StuNo and StuCou.CouNo=Course.CouNo and CouName='世界旅游 ' and StuName='林斌' 14、显示"00电子商务"班的选修报名结果。要求有学号、姓名、课程编号、课程名称、志愿号，并按学号、志愿号排序。 select Student.StuNo,StuName,Course.CouNo,CouName,WillOrder from Student,StuCou,Course,Class where Student.StuNo=StuCou.StuNo and StuCou.CouNo=Course.CouNo and Student.ClassNo=Class.ClassNo and ClassName='00电子商务' order by Student.StuNo,WillOrder 15、学号为'00000023'的学生第一志愿报名选修“001”这门课程，请在数据库中进行处理。 insert StuCou(StuNo,CouNo,WillOrder,State) values('00000023','001',1,'报名') 16、学号为“00000024”的学生因故取消了其所有的选修课。 delete StuCou from StuCou where StuNo='00000024' 17、将“00多媒体”班级“杜晓静”同学的名字修改为“杜小静”。 update Student set StuName='杜小静' where StuName='杜晓静' and ClassNo=(select ClassNo from Class where ClassName='00多媒体') 18、查询“信息工程学院”学生的选课情况，显示学生班级情况、学生姓名、课程名称、授课教师和部门名称，并按班级名称进行降序排序。 select class.ClassNo,ClassName,class.DepartNo,stuname,CouName,Teacher,DepartName from Student,Class,Course,Department,StuCou where Student.ClassNo=Class.ClassNo and Student.StuNo=StuCou.StuNo and StuCou.CouNo=Course.CouNo and Course.DepartNo=Department.DepartNo and DepartName='信息工程学院' order by ClassName desc 19、统计各部门开设课程的情况，要求显示部门编号、部门名称、课程门数、总的报名人数、总的限报人数以及总报名人数与总限报人数之差。 select department.DepartNo,DepartName,count(CouNo) '课程门数',sum(WillNum) '总的报名人数',sum(LimitNum) '总的限报人数',sum(WillNum)-sum(LimitNum) '总报名人数与总限报人数之差' from Department,Course where Course.DepartNo=Department.DepartNo group by Department.DepartNo,DepartName 20、“00电子商务”班的“林斌”同学申请将已选修的“中餐菜肴制作”课程修改为“Linux网络操作系统”。 update StuCou set CouNo=(select couno from course where CouName='Linux网络操作系统') where StuNo=((select StuNo from Student,Class where Student.ClassNo=Class.ClassNo and StuName='林斌' and ClassName='00电子商务')) and CouNo=(select CouNo from Course where CouName='中餐菜肴制作' )
阶段总结
能根据实际需要对所自己使用的数据库，进行编辑（输入、修改、删除）。 会使用INSERT语句向数据表插入数据。 将INSERT与SELECT配合使用向表中添加查询结果的多行数据 会使用带有INTO的SELECT语句将查询抽取出来的数据存储到一个新表中。 能使用UNION运算符将两个或更多查询结果合并为一个查询结果。 会使用UPDATE语句更新数据表中的数据。 会使用DELETE语句删除数据表中的数据。
创建和管理数据库
每个 SQL Server数据库至少具有两个操作系统文件：一个数据文件和一个日志文件。数据文件包含数据和对象，例如表、索引、存储过程和视图。日志文件包含恢复数据库中的所有事务所需的信息。
为了便于分配和管理，可以将数据文件集合起来，放到文件组中。利用文件组，可以改善数据库的性能
创建数据库需要一定许可，在默认情况下，只有系统管理员和数据库拥有者可以创建数据库。数据库被创建后，创建数据库的用户自动成为该数据库的所有者
创建数据库的过程实际上就是为数据库设计名称、设计所占用的存储空间和存放文件位置的过程等
创建数据库之前，首先需通过分析确定数据库的名字、所有者、大小、数据文件、日志文件存放位置、表和索引是否需要单独放在某个磁盘上等。
用管理平台直接创建数据库
注：创建之前请检查是否存在相同名字的数据库，如存在则删除
sql创建数据库
举例：
创建一个Student1数据库：该数据库的主数据文件逻辑名称为Student1_data， 物理文件名为Student1.mdf， 初始大小为10MB，最大文件大小为无限大，增长速度为10%； 数据库的日志文件逻辑名称为Student1_log，物理文件名为Student1.ldf， 初始大小为1MB，最大文件大小为5MB，增长速度为1MB。 将数据文件和日志文件存储在E:\DB目录下(如果此路径不存在，请先手动创建目录)。 --设置当前数据库为master，以便访问sysdatabases表 USE master GO --判断当前数据库是否存在 If exists (Select * From sysdatabases Where name='Student1') Drop Database Student1 --删除数据库 GO --创建数据库 CREATE DATABASE Student1 ON Primary ( NAME =Student1_data, /*注意有逗号*/ FILENAME ='E:\DB\Student1.mdf', /*注意有逗号*/ SIZE =10, /*注意有逗号*/ FILEGROWTH=10% /*注意没有逗号*/ ) /*注意要用半角状态 LOG ON ( NAME =Student1_log, /*注意有逗号*/ FILENAME='E:\DB\Student1.ldf', /*注意有逗号*/ SIZE=1, /*注意有逗号*/ MAXSIZE=5, /*注意有逗号*/ FILEGROWTH=1 /*注意没有逗号*/ ) /*注意要用半角状态下的引号*/ GO创建数据库名称为students，有1个10MB和1个20MB的数据文件和2个10MB的事务日志文件。 数据文件逻辑名称为students和student2，物理文件名为students.mdf和student2.ndf。 主文件是students，由primary指定，两个数据文件的最大尺寸分别为无限大和100MB， 增长速度分别为10%和1MB。 事务日志文件的逻辑名为studentlog1和studentlog2， 物理文件名为studentlog1.ldf和studentlog2.ldf，最大尺寸均为50MB， 文件增长速度为1MB。将数据文件和日志文件存储在E:\DB目录下。 --设置当前数据库为master，以便访问sysdatabases表 USE master GO --判断当前数据库是否存在 If exists (Select * From sysdatabases Where name='Students') Drop Database Students --删除数据库 GO --创建数据库 CREATE DATABASE Students ON Primary ( NAME =Students, /*注意有逗号*/ FILENAME ='E:\DB\Students.mdf', /*注意有逗号*/ SIZE =10, /*注意有逗号*/ FILEGROWTH=10% /*注意没有逗号*/ ), /*注意有逗号*/ ( NAME =Student2, /*注意有逗号*/ FILENAME ='E:\DB\Student2.ndf', /*注意有逗号*/ SIZE =20, /*注意有逗号*/ Maxsize=100, FILEGROWTH=1 /*注意没有逗号*/ ) LOG ON ( NAME =Studentlog1, /*注意有逗号*/ FILENAME='E:\DB\studentlog1.ldf', /*注意有逗号*/ SIZE=10, /*注意有逗号*/ MAXSIZE=50, /*注意有逗号*/ FILEGROWTH=1 /*注意没有逗号*/ ), /*注意有逗号*/ ( NAME =Studentlog2, /*注意有逗号*/ FILENAME='E:\DB\studentlog2.ldf', /*注意有逗号*/ SIZE=10, /*注意有逗号*/ MAXSIZE=50, /*注意有逗号*/ FILEGROWTH=1 /*注意没有逗号*/ ) GO
查看数据库
/*显示数据库信息*/ sp_helpdb /*显示指定数据库信息*/ sp_helpdb 数据库名举例：
使用SP_HELPDB，显示StuXk数据库的信息 SP_HELPDB StuXk
修改数据库
使用系统存储过程SP_RENAMEDB将t1更名为NewStudents SP_RENAMEDB 't1','NewStudents' GO用管理平台将数据库StuXk重命名为XK，后续使用名称XK
删除数据库
Drop语句可以从SQL Server中一次删除一个或多个数据库。其语法如下： Drop database database_name[,…n] 只有处于正常状态下的数据库，才能使用DROP语句删除。 当数据库处于以下状态时不能被删除：
使用DROP DATABASE删除数据库NewStudents USE Master DROP DATABASE NewStudents使用管理平台删除数据库test
其他数据库操作
创建和管理数据表
数据类型
行：记录、元组
列：字段、属性
一个数据包含：
数据类别：
空值：没有输入的值，未知或未定义。避免用空值直接参与运算，尽量不要允许用空值（用默认值解决没有输入的问题）
用管理平台创建数据表
举例：
用管理平台在学生管理数据库中建立如下基本表： 学生（学号，姓名，年龄，性别，所在系） 要求：学生表中学号、姓名、年龄、性别不为空
用管理平台在学生管理数据库中建立如下基本表： 课程（课程号，课程名，先行课） 要求：课程号不为空
用管理平台在学生管理数据库中建立如下基本表： 选课（学号，课程号，成绩） 要求：学号、课程号、成绩均不为空
使用sql语句创建数据表
create table 表名 ( 字段1 数据类型1 [ null | not null ]， 字段2 数据类型2 [ null | not null ]， …… )举例：
用SQL在学生管理数据库中建立如下基本表： 学生（学号，姓名，年龄，性别，所在系） 要求：学生表中学号、姓名、年龄、性别不为空 ----切换当前数据库为学生管理数据库StudentManage Use StudentManage -----创建学生数据表 Create table student ( stuno char(11) not null, stuname char(8) not null , age smallint not null, sex char(2) not null, departname varchar(20) )
数据完整性和约束
数据库中的完整性约束
数据库中的完整性约束是为保证数据库中数据的正确性、一致性和可靠性，对关系模型提出的某种约束条件或者规则
实体完整性、域完整性、参照完整性、用户定义完整性
实体完整性:
实体完整性用于保证数据库中数据表的每一个特定实体的记录都是唯一的 约束种类
域完整性:
域完整性是指保证指定列的数据具有正确的数据类型、格式和有效的数据范围。
约束种类:
参照完整性:
参照完整性是指一个表（从表）中的某一列或多列（从键）是引自另一个表（主表）
约束种类:
FOREIGN KEY(外键)约束：一个表（从表）的外键（FORENIGN KEY） 引自另一个表（主表）中的主键（PRIMARY KEY）
用户定义完整性:
这是由用户定义的完整性。用户定义完整性可以定义不属于其他任何完整性分类的特定业务规则
数据完整性的实现方式
在SQL SERVER中，对于基本表的约束分为列约束和表约束。
列级约束：是列定义的一部分，只能应用于一列上。
定义在列级，是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名；
表级约束：作为表定义的一部分，可以应用在一个表中的多列上
定义在表级，与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，与列定义用’,’分隔，需指定列名
完整性约束的基本语法格式为: [ CONSTRAINT <约束名> ] <约束类型>定义表约束时必须指出要约束的那些列的名称；约束不指定名称时，系统会给定一个名称
主键约束（primary key）
用于定义基本表的主键，它是唯一确定表中每一条记录的标识符，其值不能为NULL，也不能重复，以此来保证实体的完整性
基本语法： [CONSTRAINT 约束名] PRIMARY KEY [CONSTRAINT 约束名] PRIMARY KEY (列名)举例：
建立一个StuCou表，定义stuno，couno共同组成StuCou表的主键 create table StuCou ( stuno char(11), couno char(3), score int, constraint PK_stucou primary key(stuno,couno) )
唯一性约束（unique）
唯一性约束用于指定一个或者多个列的组合值具有唯一性，以防止在列中输入重复的值。
基本语法： [ CONSTRAINT 约束名 ] UNIQUE [ CONSTRAINT 约束名 ] UNIQUE （列名）
检查约束（check）
检查约束对输入列或者整个表中的值设置检查条件，以限制输入值，保证数据库数据的完整性
基本语法： [ CONSTRAINT 约束名 ] CHECK （逻辑表达式）
默认约束（ default ）
默认约束指定在插入操作中如果没有提供输入值时，则系统自动指定值
基本语法： [ CONSTRAINT 约束名 ] Default (表达式） 也可以向已经创建好的表添加缺省值： CONSTRAINT 约束名 DEFAULT 表达式 [FOR 列名]
空值约束
空值约束用来控制是否允许该字段的值为NULL。NULL值不是0也不是空白，更不是填入字符串的“NULL”字符串，而是表示“不知道”、“ 不确定”或“没有数据”的意思。
当某一字段的值一定要输入才有意义的时候，则可以设置为NOT NULL。如主键列就不允许出现空值，否则就失去了唯一标识一条记录的作用。
空值约束只能用于定义列约束。
[CONSTRAINT 约束名 ] [NULL|NOT NULL]
外键约束（foreign key）
外键约束确保同一个表或者不同表之间的参照完整性
基本语法: constraint <约束名> foreign key (<外键>) references <主表> (<与外键对应的主键 >)
使用管理平台添加数据完整性约束
举例：
主键约束
StudentManage数据库中，通过管理平台，设置Student表以学号stuno为主键
唯一性约束
通过管理平台，设置course表的课程名couname不允许重复
检查约束
通过管理平台，设置student表的性别只能输“男”或“女”
默认约束
通过管理平台，设置student表的age默认20，性别默认为’男’
外键约束
通过管理平台，要求选课表中的学号与学生表中的学号建立参照关系， 选课表中的课程号与课程表中的课程号建立参照关系
使用管理平台修改数据表
增加属性
【例1】向学生表中增加“家庭地址”和“电话”
删除属性
注：不允许删除已定义列级完整性约束或表级完整性约束的属性，NOT NULL 约束除外；要删除这些属性必须先删除该属性上的约束条件
【例2】删除刚才增加的“家庭地址”和“电话”属性
修改属性
【例3】改变学生表中“所在系”的宽度为30，且要求非空
删除完整性约束条件
【例4】对学生表中删除“性别”属性上的约束，然后删除“性别”属性
添加标识列INDENTITY（注意此类型要设置为int）
【例5】为选课表添加一标识列ID，种子为1，增量为1
使用管理平台删除数据表
注：基本表一旦被删除，表中的数据及在此表基础上建立的索引，视图将自动地全部被删除，所以要特别小心
不能删除已被定义为其它表的参照表的表
使用sql语句修改数据表
增加属性
alter table <表名> add <新列名> <数据类型> [<列级完整性约束条件>] [，…n]【例1】向学生表中增加“家庭地址”addr和“电话”tel Alter table student add addr varchar(60), tel char(11)
删除属性
alter table <表名> drop column <列名>【例2】删除刚才增加的“家庭地址”和“电话”属性 Alter table student drop column addr, tel注：不允许删除已定义列级完整性约束或表级完整性约束的属性，NOT NULL 约束除外，要删除这些属性必须先删除该属性上的约束条件
修改属性
alter table <表名> alter column <列名> <数据类型>注：改变宽度，增加not null约束；对于已有数据的表，只能将属性的宽度改为已有数据的宽度
【例5】改变学生表中“所在系”的宽度为30，不为空 Alter table student Alter column departname varchar(30) not null
删除完整性约束条件
alter table <表名> drop <约束名>【例6】对学生表中删除“性别”属性上的约束，然后删除“性别”属性 ---删除check约束 Alter table Student Drop ck_sex ---删除default约束 Alter table Student Drop DF__student__sex__1F98B2C1 ---删除性别属性 Alter table Student Drop column sex
添加完整性约束条件
alter table <表名> Add constraint <约束名> 约束条件
【例7】为学生表添加“性别”属性，不可为空，默认值为“男”，且只能输 “男”或“女” Alter table student add sex char(2) not null Alter table student add constraint DF_sex default('男') For sex Alter table student add constraint CK_sex Check(sex in ('男','女'))
添加标识列
alter table <表名> Add <列名> int IDENTITY(种子,增量)【例8】为选课表添加一标识列ID，种子为1，增量为1。 Alter table stucou add Id int identity(1,1) not null
使用sql语句删除数据表
drop table <表名>注：基本表一旦被删除，表中的数据及在此表基础上建立的索引，视图将自动地全部被删除，所以要特别小心
不能删除已被定义为其它表的参照表的表
drop table StuCou; drop table course; drop table student;
总结
举例：
create table Employees ( employeeid char(6) not null, name char(10) not null, birthday datetime not null, sex bit not null, address varchar(max), zip char(6), phonenumber char(11), emailaddress varchar(max), department char(3), constraint ck_phonenumber check(phonenumber like('[1,2,3,4,5,6,7,8,9][1,2,3,4,5,6,7,8,9][1,2,3,4,5,6,7,8,9][1,2,3,4,5,6,7,8,9][1,2,3,4,5,6,7,8,9][1,2,3,4,5,6,7,8,9][1,2,3,4,5,6,7,8,9][1,2,3,4,5,6,7,8,9][1,2,3,4,5,6,7,8,9][1,2,3,4,5,6,7,8,9][1,2,3,4,5,6,7,8,9]')), constraint pk_employees primary key(employeeid) ) 这个11位的，还可以这样phonenumber not like '%[^0-9]%' and (len(phonenumber)==11)
使用管理平台创建、修改和删除视图
视图概述
基表（base table)：独立存在的表 视图——虚拟表。在SQL中只存储视图的定义，不存储视图所对应的任何物理数据 它只是用来查看数据的窗口而已。
注：视图是存储在数据库中的查询SQL语句，使用视图的主要原因：安全原因， 视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址， 而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。特点：
视图是由基表（实表）产生的表（虚表） 同一张基表，根据不同用户的不同需求，可以创建不同的视图； 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。 视图的建立和删除不影响基表 对视图内容的更新（添加、删除和修改）直接影响基表；但当视图来自多个基表时， 不允许添加和删除数据
使用管理平台创建、修改、删除视图
创建视图：显示来自基表的部分行数据
【例1】使用管理平台，在Xk数据库中创建视图V_Student， 该视图只显示班级编号为“20000001”的学生信息
创建视图：显示来自基表的部分列数据
【例2】使用管理平台，在Xk数据库中创建视图V_CourseSub， 该视图仅显示课程名称和学分两列。
将两个或多个基表、视图的连接组成的复杂查询创建为视图——最常用的情况
【例3】使用管理平台，创建视图v_StuCou，显示学生的学号、姓名和所选课程名称
对基表的统计、汇总创建为视图
【例4】使用管理平台，创建视图v_CouByKind，按课程类别汇总每类平均报名人数
修改视图
删除、重命名视图
使用SQL语句创建、修改和删除视图
一、使用SQL语句创建、修改和删除视图
语法如下： Create view view_name [(column [,…n])] [with encryption] ---表示对视图的定义进行加密 As Select_statement
创建视图：显示来自基表的部分行数据
【例1】在Xk数据库中创建视图V_Student，该视图只显示班级编号为“20000001”的学生信息Create View v_student AS Select * From Student Where ClassNo='20000001‘
创建视图：显示来自基表的部分列数据
【例2】在Xk数据库中创建视图V_CourseSub，该视图仅显示课程名称和学分两列。Create view v_courseSub(课程名称,学分) AS Select CouName,Credit From Course
将两个或多个基表、视图的连接组成的复杂查询创建为视图——最常用的情况
【例3】创建视图v_StuCou，显示学生的学号、姓名和所选课程名称Create view v_StuCou(学号,姓名,课程名称) AS Select Student.StuNo,StuName,CouName From Student,StuCou,Course Where Student.StuNo=StuCou.StuNo and StuCou.CouNo=Course.CouNo
对基表的统计、汇总创建为视图
【例4】创建视图v_CouByKind，按课程类别汇总每类平均报名人数CREATE VIEW v_CouByKind AS SELECT kind 课程类别,avg(willnum) '每类平均报名人数' FROM Course GROUP BY kind
注意
修改视图
ALTER VIEW view_name AS select_statement删除视图
DROP VIEW view_name重命名视图
sp_rename 旧视图名, 新视图名显示视图
显示视图的定义信息： sp_helptext 视图名
二、视图数据的插入、修改和删除
总结
视图是一个虚拟表，创建视图的目的是为了方便查看数据和提高数据的安全性 创建视图的方法 创建视图的注意事项 （1）对视图的操作与对表的操作一样，可以对其进行查询、修改和删除， 但对数据的操作要满足一定的条件。 （2）对视图所引用的基础表来说，视图的作用类似于筛选。 （3）通过视图修改表中数据时，不能违反数据完整性规则。不能在视图上创建全文索引。
索引实现
（一）索引的概念
什么是索引？索引是对数据库表中一个列或多个列的值（称为索引值）进行排序的结构（索引是建立在列的上面）。检索数据时，通过相应索引的索引值所指向的表中的数据行，能快速查找到所需要的数据。一个表可以创建多个索引。
何时使用索引？在那些经常被用来查询的列上建立索引，以提高查找效率。查询时SQL Server会自动选择与查询相匹配的索引。
1. SELECT * FROM Student WHERE StuNo='00000001' 执行如上查询语句时，可能会使用基于StuNo列的索引。 2. SELECT * FROM Student WHERE StuName='林斌' 执行如上查询语句时，可能会使用基于 StuName的索引。索引自身也是通过文件来保存的，需要占用磁盘空间。为了提高系统的性能， 可将索引创建在与数据文件、日志文件不同的存储设备上 创建索引和维护索引都要耗费时间。当对表中的数据进行增加、删改和修改时， 索引都要动态维护，这样就降低了数据的维护速度 对于经常进行添加、删除和更新的表，最好是慎用索引
（二）使用索引的注意事项
建立索引的原则
在经常需要搜索的列上创建索引 在主键上创建索引 在经常用于连接的列上创建索引，也就是在外键上创建索引 在经常需要根据范围进行搜索的列上创建索引（因为索引已经排序，其指定的范围是连续的） 在经常需要排序的列上创建索引 （因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间） 在经常用在Where子句的列上创建索引不创建索引的原则
对于那些在查询中很少使用和参考的列不应该创建索引，这是因为既然这些列很少使用， 所以有无索引并不能提高查询速度，相反由于增加了索引，反而降低了系统的维护速度和增大了空间需求 对于那些只有很少值的列也不应该增加索引，这是因为由于这些列的的取值很少， 例如学生表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例， 即需要在表中搜索的数据行的比例很大，增加索引并不能明显加快检索速度 只有少数行的表没必要创建索引 由text、ntext或image数据类型定义的列不创建索引 当Update、Insert、Delete等性能远远大于Select性能时，不应该创建索引。 这是因为Update、Insert、Delete的性能和Select的性能是相互矛盾的。 当增加索引时，会提高Select的性能，但是会降低Update、Insert、Delete的性能。 当减少索引时，会提高Update、Insert、Delete的性能，降低Select的性能。 因此当Update、Insert、Delete的性能远远大于Select的性能时， 即经常进行添加、删除和更新的表，则不应该创建索引。
（三）索引的分类
聚集索引和非聚集索引
聚集（ CLUSTERED）索引：数据行的物理存储顺序与索引顺序完全相同。 每个表只能有一个聚集索引。 非聚集（ NONCLUSTERED ）索引：不改变表中数据行的物理存储数序。 它建立一个逻辑表，记录索引值在表中的实际存储位置。 一个表上最多可以建立249个非聚集索引。 创建索引时如果没给出CLUSTERED 或NONCLUSTERED，则默认创建非聚集索引。主键索引和非主键索引
主键索引： 默认情况下，创建主键约束时自动创建基于主键的聚集索引。 在创建聚集索引时，可以指定填充因子（默认值为0），以便在索引页上保留一定百分比的空间， 减少发生索引页拆分的情况。 非主键索引：在非主键的属性列上创建的索引，一般都是非聚集索引。小tip：对聚集索引列,行的排列的物理顺序与索引排列的顺序是一致的。这就引起一个问题：如果要向一个表插入一个记录，有可能由于插入在表的中间某个位置，而要使其后的所有列都要向后移动一个行的空间，这种操作是很费时间的。 为此，先考虑在列的物理顺序排列位置上空出一些能插入记录的空间（要了解页的概念），然后，要插入的话，就可以不动全表，而只动动页上的内容就行了。 一页上，默认占用的存储空间（不含空的地方）占该页总存储空间的百分之几，就是填充因子。
----
唯一索引和非唯一索引
唯一（ UNIQUE ）索引：索引列值不会出现重复值 在创建主键约束或唯一约束时，会自动为这些约束创建唯一索引；当用户删除主键或唯一约束时， 创建这些列上的唯一索引也会自动删除 Unique索引，既可以采用聚集索引，也可以采用非聚集索引 建有Unique索引的表在执行Insert语句或Update语句时， SQL Server将自动检验新的数据中是否存在重复值，如果存在返回错误信息 非唯一索引：索引列值不唯一单列索引和复合索引
单列索引：基于表中单列创建的索引 复合索引：基于表中多个列所创建的索引 在（字段1，字段2）上创建的复合索引和在（字段2，字段1）上创建的复合索引是不同的 复合索引中字段的顺序很重要：在次序上首先定义最具唯一性的字段列 查找数据时，只有在where子句中指定了索引的第一个字段时才使用该复合索引小tip：索引的分类是针对不同维度而言 的，比如可以建立单列、非聚集、非主键的唯一索引
----------------------
（四）创建索引
【任务1】如用户需要查询同学姓名，如果因数据行非常多，需要提高查询速度。 分析：Student表已经有主键PK_Student，它是聚集索引 因姓名可以有重复值，所以本题要完成的是：在姓名列上，创建非聚簇、值不唯一的索引
使用sql语句创建索引 Create [unique] --(默认情况为非唯一) [clustered | nonclustered ] --(默认情况为非聚集) Index index_name on {table_name | view_name}(column_name [asc | desc] [,…n])举例：
【例2】使用T-SQL语句为Student的StuName创建索引IX_StuNameUse Xk GO --判断是否存在索引IX_StuName，如存在则删除IX_StuName索引 IF exists (select name from sysindexes where name = 'IX_StuName') Drop Index Student.IX_StuName --删除IX_StuName索引 GO ----创建索引 CREATE NONCLUSTERED INDEX IX_StuName --NONCLUSTERED可以省略 ON Student(StuName) GO【例3】使用SQL语句在StuCou表上创建名为IX_StuNoCouNo的聚集、惟一、复合索引， 该索引基于StuNo列和CouNo列创建 由于系统在数据表的主键列上自动创建聚集索引，而且每个表中只能创建一个聚集索引。 因此，此时如果一定要创建此IX_StuNoCouNo，首先应该删除StuCou表的主键。 --(1)查看StuCou表的索引信息 SP_HELPINDEX Stucou GO --(2)删除StuCou表的主键 Alter table stucou drop constraint PK_StuCou --drop PK_StuCou GO --(3)在StuCou表上创建名为IX_StuNoCouNo的聚集、惟一、复合索引(StuNo列和CouNo列) create unique clustered index IX_StuNoCouNo on stucou(Stuno,couno) go
（五）删除索引
使用Management Studio XK——相应的表——右击“设计”——右击“索引/键”——“删除” 或者在相应的表下的“索引”目录，直接右击相应的“索引/键”——删除 使用SQL语句 DROP INDEX table_name.index_name举例：
【例4】使用T-SQL删除课程表的IX_CouName索引。 USE Xk GO DROP INDEX Course.IX_CouName GO不能用DROP INDEX语句删除由PRIMARY KEY约束或UNIQUE约束创建的索引。 要删除这些索引必须先删除PRIMARY KEY约束或UNIQUE约束。 创建非聚集索引之前需要先创建聚集索引；在删除聚集索引时，表中的所有非聚集索引都将被重建
（六）管理索引
重命名索引 sp_rename 'table_name.old_index_name', 'new_index_name' 显示索引信息（定义） sp_helpindex 索引名
（七）分析索引
1. 显示查询计划： 查看查询时系统是否使用了所创建的索引
--设置显示查询计划的开关为“ON” set showplan_all on go SELECT 语句 go --设置显示查询计划的开关为“OFF” set showplan_all off goSET SHOWPLAN 语句必须是批处理中仅有的语句
举例：
【例8】查询姓“林”同学的信息，查看IX_StuName起作用了吗？ USE Xk GO --设置显示查询计划的开关为“ON” SET SHOWPLAN_ALL ON GO SELECT StuName FROM Student WHERE StuName LIKE '林%' GO --设置显示查询计划的开关为“OFF” SET SHOWPLAN_ALL OFF GO-----------
2. 显示磁盘读取信息：分析执行该数据查询所花费的磁盘活动量
SET STATISTICS IO ON SELECT 语句 SET STATISTICS IO OFF【例9】查询姓名为林斌学生的信息，并分析执行该数据查询所花费的磁盘活动量 SET STATISTICS IO ON GO SELECT StuName FROM Student WHERE StuName ='林斌' GO SET STATISTICS IO OFF GO--清空缓存计划 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO
（八）维护索引
1. 更新统计信息
查询优化器会利用索引统计信息估算使用该索引进行查询的成本。 但随着用户在数据库上进行的一系列操作，该统计信息可能已经过时， 导致查询优化器判断失误，造成索引性能的下降。 因此，有必要对数据库中的这些统计信息进行更新。 统计信息更新有两种方式：自动更新和手动更新① 自动更新
【例10】在SQL Server Management Studio中 通过设置Xk数据库的属性决定是否实现统计的自动更新。 方法：右击数据库——属性——选项——自动创建统计信息行和自动更新统计信息行， 默认为TRUE（自动更新）。 注：应避免频繁进行索引统计更新，特别是数据库操作比较集中的时间段。
② 手动更新
【例11】使用UPDATE STATISTICS命令。 更新Xk数据库中的Student表的PK_Student索引的统计信息。 USE Xk GO UPDATE STATISTICS Student PK_Student GO2. 扫描表的碎片
对表进行数据操作时可能会导致表碎片，而表碎片会导致额外的页读取，从而造成数据库查询性能的降低。此时用户可通过使用DBCC SHOWCONTIG扫描表，并通过其返回值确定该表的索引页是否已经严重不连续。
【例12】使用DBCC语句扫描表的碎片：如利用DBCC SHOWCONTIG获取Xk数据库中Student表的 PK_Student索引碎片信息。 USE Xk GO DBCC SHOWCONTIG(Student,PK_Student) GO
3. 碎片整理
当表或视图上的聚焦索引和非聚焦索引上存在碎片时，可通过DBCC INDEXDEFRAG对其进行碎片整理。
【例13】用DBCC INDEXDEFRAG命令对Xk数据库中Student表的PK_Student索引 进行碎片整理。 DBCC INDEXDEFRAG(Xk,Student,PK_Student) GO 小TIP：此时，再扫描表的碎片，扫描密度显示为100% USE Xk GO DBCC SHOWCONTIG(Student,PK_Student) GO4. 索引使用情况统计
如何找出 SQL Server 资料库是否建立了多余的索引 右击当前库——报表——标准报表——索引使用情况统计
总结
1．创建索引的好处 提高查询速度 加速表与表之间的连接 2．创建索引的代价 占用存储空间 在增、删、改操作中索引的存在会增加一定的工作量。 3. 创建索引的原则 在经常用来检索的列上创建索引（如经常在where子句中出现的列） 在表的主键、外键上创建索引 在经常用于表间连接的字段上建立索引 小表不需要创建索引
概念模型与数据模型
概念模型（Conceptual Model）
数据模型（Data Model）
逻辑数据模型（Logical Data Model）
数据模型实现了信息世界向计算机世界的过渡。
数据模型是按计算机的观点对数据建模，是概念模型的数据化。
数据模型提供了表示和组织数据的方法，描述数据的逻辑结构。
DBMS都是基于某种数据模型或是支持某种数据模型的。
物理数据模型（Physical Model）
物理数据模型是对最底层数据的抽象，描述数据在系统内部的表示方式和存取方法，如数据在磁盘上的存储方式和存取方法。
物理数据模型是面向计算机系统的，由DBMS具体实现。
模型转换
数据模型的组成要素
数据结构
数据操作
是指对数据库中各种对象（型）的实例（值）允许执行的操作及操作规则的集合。
反映的是系统的动态特性。
主要有查询和更新（插入、删除和修改）两大类操作。
数据模型必须定义这些操作的确切含义、操作符号、操作规则以及实现操作的语言。
完整性约束
数据模型中数据及其联系所具有的制约和依存关系，用以限定符合数据模型的数据库状态以及状态的变化，以保证数据的正确、有效、相容。
数据库必须遵守的基本的通用的完整性约束条件。
还应该提供定义完整性约束条件的机制，以反映具体应用所涉及的数据必须遵守的特定的语义约束条件。
概念模型设计——绘制ER图
概念模型（Conceptual Model）
实体（Entity）
现实世界中客观存在并能相互区分的事物经过加工，抽象成为信息世界的实体。
实体是信息世界的基本单位。
可以是具体的，也可以是抽象的。
实体型与实体集（Entity Type and Set）
现实世界的事物所具有的特征在概念模型中通过与其对应的实体反映出来，称之为属性。
一个实体可以由若干属性来刻画，用属性名表示。
例：学生可用“姓名”、“学号”、“性别”、“年龄”等来描述， 而“李明”、“2019012408”、“男”、“20”为属性值。属性（Attribute）
实体型（Entity Type） 用实体名及其属性名集合来抽象和刻画同类实体，称为实体型。 学生（学号，姓名，性别，所在系） 实体集(Entity Set) 同一类型实体值的集合称为实体集，如全体学生等。 （20180122145, 王巍, 男, 1998-05-23, 计算机应用工程）域（Domain）
属性的取值范围称为该属性的域。 性别的域为（男、女）关键字(key，码)
关键字(key，又称侯选码或码)
能唯一标识一个实体的属性或属性组。每一个实体集一定有关键字。
一个实体可有多个关键字，可选其中一个作为主关键字。
联系（Relationship）
现实世界中事物彼此的联系在概念模型中反映为实体间的联系。
两个实体集间联系的类型
1、一对一（1：1）：A中任意实体至多对应B中的一个实体， 反之B中的任意实体至多对应A中的一个实体。 举例： 班级与班长 学校与校长 观众与座位 乘客与车票 病人与病床 灯泡与灯座
2、一对多（1：n）：A中至少有一个实体对应B中的多个实体， 反之B中的任意实体至多对应A中的一个实体 举例： 班级与学生 城市与街道 宿舍与学生 父亲与子女
3、多对多（n：m）：A中任意实体至少有一个实体对应B中的多个实体， 反之B中的任意实体至少对应A中的多个实体。 举例： 学生与课程 供应商与零件 顾客与商品
两个以上实体之间的联系类型（1:1,1:n,m:n）
两个以上实体之间一对多联系 实例： 课程、教师与参考书三个实体型 一门课程可以有若干个教师讲授， 使用若干本参考书， 每一个教师只讲授一门课程， 每一本参考书只供一门课程使用
两个以上实体间的多对多联系 实例： 供应商、项目、零件三个实体型， 一个供应商可以供给多个项目多种零件； 每个项目可以使用多个供应商供应的零件； 每种零件可由不同供应商供给。
单个实体间的一对多联系 实例： 同一实体集“职工”与“职工”间的联系 职工实体型内部具有领导与被领导的关系 某一职工（干部）“领导”若干名职工 一个职工仅被另外一个职工直接领导 这是一对多的联系
单个实体间的一对一联系 如职工间的“配偶”关系：一夫一妻
单个实体间的多对多联系
实例 同一实体集“零件”间的“构成”关系： 一个大型零件由多个小型零件构成，反之，一种小型零件可以用于构成多种大型零件。 这就是零件内部的多对多联系。
实体联系模型（E-R模型）
DBS的核心问题之一：如何表示和处理实体及实体间的联系。
实体－联系模型(Entity Relationship Model，简记为E-R模型)
E-R图中用菱形表示联系，菱形框内写明联系名，并将参与联系的实体用无向线段连接，同时在无向线段旁标上联系的类型（1:1、1:n或m:n）。
概念模型实例练习
某工厂物资管理涉及的实体如下，试给出仓库、职工、项目、供应商和零件的E-R图。
仓库：仓库号、仓库面积、电话号码； 零件：零件号、名称、规格、单价、描述； 供应商：供应商号、姓名、地址、电话号码、帐号； 项目：项目号、预算、开工日期； 职工：职工号、姓名、年龄、职称。1、一个仓库可以存放多种零件，一种零件可存放在多个仓库；
2、一个仓库有多个职工当仓库保管员，一个职工只能在一个仓库工作；另仓库主任可领导若干保管员。
3、一个供应商可以为多个项目提供多种零件，一个项目可以有多个供应商，一种零件可以由多个供应商供应。
结果：
小结
关系数据库设计
关系模型
定义：关系模型(Relational Model)用二维表（关系）来描述实体及实体间联系的模型。
关系模型：实体和联系均用二维表来表示，数据操作为集合操作。
1）关系（表）：一个关系对应通常所说的一张二维表。
2）元组 （记录）：表中的一行称为一个元组。
3）属性 （字段）：表中的一列称为一个属性。一个由n个属性的关系称为n元关系。
4）主码 （主键）：表中的某个属性或属性组，值可以唯一确定一个元组，且属性组中不包含多余的属性。
5）域： 属性的取值范围称为域。
6) 分量： 元组中的一个属性值称为分量或数据项。
7) 关系模式： 关系的型称为关系模式，是对关系的描述。
关系模式一般的表示是：关系名(属性1，属性2，…，属性n). 例： 学生（学号，姓名，年龄，性别，系号，年级） 课程（课程号，课程名，学分） 选修（学号，课程号，成绩）优点：无论实体还是实体之间的联系都用统一的数据结构（二维表、关系）来表示，可方便地表示m:n联系，因此概念简单，用户易懂易用.
缺点： 由于存取路径对用户透明，查询效率不高，必须对查询请求进行优化。
关系必须规范化，关系的每个分量必须是一个不可分的数据项，不允许表中套表。
1、实体集的转换规则 一个实体集转换为一个关系，实体集的属性即为关系的属性，实体的码即为关系的码。2、实体集间联系的转换规则
（1）1：1联系的转换方法，有两种转换方法
将1：1联系转换为一个独立的关系，与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，每个实体的码均为该关系的候选码。
或者：将1：1联系与任一端实体集所对应的关系合并，则在被合并关系中增加联系本身的属性和与联系另一个实体集的码。
举例：
例：下图转换为关系模型
方案1： 职工（职工号，姓名，年龄） 产品（产品号，产品名，价格） 负责（职工号，产品号） 方案2： 职工（职工号，姓名，年龄，产品号） 产品（产品号，产品名，价格） 方案3： 职工（职工号，姓名，年龄） 产品（产品号，产品名，价格，职工号） 方案3比较合理（2）1：n联系的转换方法，有两种转换方法
将1：n联系转换为一个独立的关系，与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，n端实体集的码为该关系的候选码。
将1：n联系与n端实体集所对应的关系合并，则在n端实体集中增加联系本身的属性和与联系对应的1端实体集的码。
例：下图转换为关系模型
方案1： 仓库（仓库号，地点，面积） 产品（产品号，产品名，价格） 仓储（产品号，仓库号，数量） 方案2： 仓库（仓库号，地点，面积） 产品（产品号，产品名，价格，仓库号，数量） 注： 方案1适合仓储变化比较大的应用场合； 方案2适合仓储变化比较小的应用场合（3）n：m联系的转换方法
将n：m联系转换为一个独立的关系。与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，关系的码为各实体码的组合
例： 学生、课程、学生与课程之间的多对多联系： 学生（学号，姓名，性别，年龄） 课程（课程号，课程名，学分） 选修（学号，课程号，成绩）（4）三个或三个以上实体集间的多元联系的转换方法
对于一对多的多元联系，修改n端实体集对应的关系，即将与联系相关的1端实体集的码和联系本身的属性加入到n端的实体集中。
对于多对多的多元联系，新建一个独立的关系，该关系的属性为多元联系相连的各实体的码以及联系本身的属性，码为各实体码的组合。
概念设计与逻辑设计实例
假设一个学生可选多门课程，而一门课程又有多个学生选修，每个学生每选一门课只有一个成绩， 一个教师只能讲一门课程，一门课程也可有多个教师讲授，一门课使用多本参考书，一个班级由多名学生组成，且每个班级的人数不同。要求给出E-R图，完成概念设计和逻辑设计。
课程表（课程号，课程名，学分）
班级表（班级号，专业，人数）
参考书表（书号，书名，价格，摘要，课程号）
学生表（学号，姓名，性别，年龄、班级号）
教师表（职工号，姓名，职称，性别，课程号）
选修表（学号，课程号，成绩）
-------------
小结
数据库设计的过程：
需求分析------->概念模型 设计------->逻辑结构 设计------->物理结构 设计------->数据库 实施------->数据库运行和维护
候选码（关键字（Key））：用来惟一标识表中每一行的属性或属性的组合。
主码（主关键字（Primary Key，PK，主键））：被选中的那个候选关键字称为主关键字（选择值短的那个），值惟一且不允许为空。
外码（外关键字（Foreign Key ，FK，外键））：不是本表的主键，是其它表的主键。外键值可以为空（NULL）但这时没有意义。
公共码（公共关键字）：连接两个表的公共属性，建立起表与表之间的联系。公共键是某个表的主键也是另一个表的外键。
主表：主键所在的表，也称为父表，被参照表。
从表：外键所在的表，也称为子表，参照表。
数据完整性：数据的有效性、正确性和一致性。数据完整性分为域数据完整性、表数据完整性和参照完整性。
域（列）数据完整性（用户定义完整性）：保证列数据的值是正确的、有意义的。
表（实体）数据完整性：表必须有一个主关键字，且不允许为空值。
参照（引用）完整性：指外键值与主键值之间必须相互参照，以保证主键与外键的值是一致的，外键的值必须存在于主键值中。
在对表的添加（INSERT）、修改（UPDATE）和删除（DELETE）数据时需要考虑保证数据的完整性。
要保证列数据的值是正确的和有意义的。 要保证每个表中都有主键，且值不允许为空。 当修改主表的主键值时，如果从表中的外键值存在， 或者同步修改外键值，或者禁止修改主表的该主键值。 当删除主表数据时，如果从表中存在该外键值， 或者禁止删除主表数据，或者同步删除从表中的该外键值的数据行。 当在从表中添加数据时，要保证外键值一定要在主键值中存在。 当在从表中修改外关键字值，要保证修改的值在主表中存在。
关系数据库优化与范式分解
关系模式的评价
（一）关系模式 关系模式是对关系（表）的描述，为了能够清楚地刻划出一个关系， 关系模式需要由五部分组成， 即：R（U，D，DOM，F） 其中：R：关系名 U：全体属性集合 D：属性域的集合 DOM：U和D之间的映射关系的集合 F：属性间数据的依赖关系的集合，即组成关系的各个元组必须满足的完整性约束条件。 这些约束或者通过对属性取值范围的限定， 例如学生成绩必须在0—100之间， 或者通过属性值间的相互关联（主要体现于值的相等与否）反映出来，后者称为数据依赖。由于在关系模式R（U，D，DOM，F）中，影响数据库模式设计的主要是U和F，D和DOM对其影响不大，关系模式简化为三元组：R（U，F）
关系是关系模式在某一时刻的状态或内容，不同时刻关系模式中的关系可能有所不同，但它们必须满足关系模式中F所指定的完整性约束条件。即当且仅当U上的一个关系r满足F时，r称为关系模式R（U，F）的一个关系。
1、关系数据库设计的核心：关系模式的设计 2、关系模式的设计目标：按照一定的原则从数量众多而又相互关联的数据中， 构造出一组既能较好地反映现实世界，而又有良好的操作性能的关系模式。 3 、关系模式的评价标准 例1：要求设计一个教学管理数据库，希望从该数据库中得到 学生学号、学生姓名、年龄、系别、系主任姓名、学生学习的课程和该课程的成绩等信息。 若将这些信息设计为一个关系，关系模式为： 教学（学号，姓名，年龄，系名，系主任，课程名，成绩）
以上关系存在下面几个问题 (1)数据冗余较大。一个学生只有一个姓名，但上面的表中若一个学生选几门课， 则该学生的姓名就要重复几次。 同样一个系也只有一个系主任，上表中系主任的姓名重复就更多了。 (2)修改异常。假如计算机系的系主任换了，那么上表中的四条记录的系主任都需要修改， 假如改得不一样，或少改一处，就会造成数据不一致。 (3)插入异常。假如新成立了一个系：数据科学系，并且也有了系主任， 但还没有招学生，所以不能在上表中插入数据科学系的记录， 也就不能在数据库中保存数据科学系的系名和系主任的信息。 同样如果新增一门课，但还没有学生选修，所以也不能插入该课程。 (4)删除异常。如果数学系的学生全毕业了，则需要删除该系的学生记录， 但如果该系的学生全删除了，则该系的系名、系主任信息也从数据库中删除。 结论：以上关系模式不是一个好的关系模式。 一个好的关系模式，除了能满足用户对信息存储和查询的基本要求外，还应具备下列条件： （1）尽可能少的数据冗余； （2）没有插入异常； （3）没有删除异常； （4）没有更新异常。对于有问题的关系模式，可以通过模式分解的方法使之规范化， 上述关系模式如果分解为如下三个关系则可以克服以上出现的问题。 学生（学号，姓名，年龄，系名） 系（系名，系主任） 选课（学号，课程名，成绩）
函数依赖
数据依赖
数据依赖就是指同一关系中属性值的相互依赖和相互制约，即一个关系中属性间值的相等与否体现出来的数据间的相互关系。如学生的学号将决定学生姓名，课程名将决定有哪些参考书等等。 数据依赖分函数依赖、多值依赖和连接依赖等，其中函数依赖是最基本的一种数据依赖。
函数依赖的定义： 设有关系模式R（U），U是属性集，X和Y是U的子集，r是R（U）的任一个关系。如果r中不可能存在两个元组在X上的属性值相等，而Y上的属性值不等，即任意的两个元组t1和t2，若t1(X)=t2(X)，必有t1(Y)=t2(Y)，那么我们称属性组X函数确定属性组Y，或者说Y函数依赖于X。记为X
Y，其中X叫决定因素，Y叫依赖因素。
简单地说，对于任意两个元组，如果它们的X属性组值相同，则它们的Y属性组值也相同，我们就说X函数确定Y，或者说Y函数依赖于X。 更简单的表达：对于每一个确定的X，Y的值就被唯一地确定，则说X函数确定Y，或者说Y函数依赖于X。
如关系：公民（身份证号，姓名，地址，工作单位） 身份证号一确定，则其地址就唯一确定，因此身份证号函数确定地址。 而姓名一确定，不一定能确定地址。练习：说出下列关系模式中的一些函数依赖。 学生（学号，姓名，年龄，性别） 选课（学号，课程号，成绩）结论： 如果属性组A
属性组B，则A → B 函数依赖关系是属性间的一种多对一的关系。 如：学号，课程号和成绩
如果X →Y，且X←Y，则X和Y是一对一关系。 如：学号与身份证号
函数依赖的几种特例
范式
范式：关系模式满足的约束条件称为范式。根据满足规范化的程度不同，范式由低到高分为1NF，2NF，3NF，BCNF，4NF，5NF
关系模式的规范化：把一个低一级的关系模式分解为高一级关系模式的过程。
规范化的目的：尽可能地减少数据冗余，消除存储异常（插入、更新、删除），保证数据完整性。
第一范式（1NF）
如果关系模式R，其所有属性都是不可再分的基本数据项，则称R属于第一范式，R∈1NF 例2：R（姓名，身份，年龄） 姓名 身份 年龄 ---------------------------------------- 张三 男学生 17 李四 女教师 27 林林 女作家 29 R不属于1NF第一范式是作为一个关系模式的最起码要求，是一定要满足的。 第一范式仅是关系模式的最低要求，仅仅满足第一范式是不够的。 如前面所讲的关系模式： 如：教学（学号，姓名，年龄，系名，系主任，课程名，成绩） 它满足第一范式，但存在较大数据冗余和插入、删除、修改异常。
第二范式（2NF）
如关系模式R∈1NF，且每个非主属性完全函数依赖于候选码，则称R属于第二范式，R∈2NF。
主属性：候选码中的属性称为主属性。
非主属性：不包含在任何候选码中的属性称为非主属性
例3：判断R（教师编号，教师地址，课程号，课程名）是否属于第二范式。
例4：判断 选课（学号，课程号，成绩）是否属于2NF ， 假如规定一个学生一门课只有一个成绩
不满足第二范式的关系模式必然存在各种存储异常。如果满足了2NF是不是就不存在存储异常呢？不是。
例5：学生（学号，姓名，年龄，系名，系主任，系办电话）
因为它是1NF，而且不存在部分依赖，故满足2NF，但还存如下问题；
存在数据冗余：大量的系信息冗余；
它存在插入异常：系刚成立，没有学生时不能添加系；
它存在删除异常：某系学生全部毕业，系的信息丢失；
它存在修改异常：系办电话改动，需要改动多处。 因此，满足了2NF还不够。
第三范式（3NF）
如果关系模式R∈2NF，且R的任一非主属性都不传递函数依赖于任何候选码，则称R属于第三范式，R∈3NF
例6：判断上述关系模式 R1（学号，姓名，年龄，系名，系主任，系办公电话）是否满足3NF。
步骤：它满足2NF（例5已证明） 因为学号→系名 系名→系主任 所以系主任传递函数依赖于学号 即本关系模式不满足3NF。
例7：判断关系模式 选课（学号，姓名，课程号，成绩）∈3NF？
步骤：它是否满足2NF？不是。 既然不满足2NF，当然不满足3NF。
不满足3NF的关系模式存在各种存储异常。那么满足3NF的关系模式是不是就不存在存储异常呢？一般情况下是可以了，但有些特殊情况下依然还存在存储异常。
如：教学（学生，教师，课程），假定每一教师只能讲一门课，每门课由若干教师讲授，每个学生选修某门课时就对应一个固定的教师。 候选码：（学生，教师），（学生，课程） 三个属性都是主属性，没有非主属性，满足2NF和3NF。
但该关系模式还是有数据冗余和存储异常。如
插入异常：无法存储不选课的学生和不开课的教师
删除异常：无法删除一个学生的选课信息
更新异常：某门课的某位教师换了，则选该教师的所有记录均需修改。
数据冗余大：一个学生选多门课，需重复存放该学生的信息
问题存在的原因： 主属性部分函数依赖于侯选码 （注2NF，3NF是要求非主属性对侯选码的要求，而不是主属性对侯选码的要求）
如（学生，教师）→课程，而教师→课程，所以课程部分依赖于码（学生，教师）
（四）BC范式（BCNF）
设关系模式R（U）∈1NF，如果对于R的每个非平凡函数依赖X → Y（Y 不属于 X）， 则X必包含候选码，那么R∈BCNF。即若每一个决定因素都包含码，则R∈BCNF。 由BCNF的定义可以看出，每个BCNF的关系模式都具有如下性质： 所有非主属性都完全函数依赖于每个侯选码 所有主属性都完全函数依赖于每个不包含它的侯选码 没有任何属性完全函数依赖于非码的任何一组属性(即每一个决定因素都包含码) 从函数依赖范畴内，BCNF已经达到了最高的要求。 推论：如果R只有一个侯选码，且R ∈3NF，则R必属于BCNFBCNF与3NF的区别：3NF只强调非主属性对码的完全直接依赖，而BCNF不仅强调非主属性对码的完全直接依赖，而且强调主属性对码的完全直接依赖，它包括3NF。
（五）关系模式的规范化
将关系模式转化为较高级的范式称为关系模式的规范化。
关系模式属于范式的级别越高，出现异常的概率越小，所以我们应该让关系模式尽可能属于较高级的范式。
关系模式的规范化通常是采用分解的办法，也就是将关系模式分解为几个关系模式，使每个关系模式都达到更高的范式级别。但这种分解要求信息不能有损失。
小结
例：设关系模式R（学号，课程号，成绩，教师姓名，教师地址） 规定：每个学生每学一门课只有一个成绩，每门课只有一个教师任教， 每个教师只有一个地址，且教师没有同名同姓。 要求： （1）写出R的基本函数依赖：
（2）写出R的侯选码：
R的侯选码： （学号，课程号）
（3）确定R属于第几范式，并说明理由
（4）若R不属于2NF，则将R分解为2NF，并说明理由
（5）若R不属于3NF，则将R分解为3NF，并说明理由
将学生选课E-R图转为关系数据模型如下： 学生表（学号，姓名，班级名称，选课密码） 课程表（课程编号，课程名称，课程类别，学分，教师，系部编号， 系部名称，上课时间，限选人数，报名人数，被选中人数） 选课表（学号，课程编号，课程名称，姓名，志愿号，选课状态） 问题1：课程表、学生表、学生选课表是不是I范式？ I范式:一个关系的每个属性都是不可再分的基本数据项。 II范式：首先是I范式，并且关系中的每个非主属性完全函数依赖于主关键字。 问题2：课程表、学生表、学生选课表是不是II范式？如果不是，请将其规范为II范式。 将非Ⅱ范式规范为Ⅱ范式的方法： 将部分函数依赖关系中的主属性（决定方）和非主属性从关系中提取出来，单独构成一个关系； 将关系中余下的其他属性加上主关键字，构成关系。 解决：课程表、学生表是II范式。 选课表（学号，课程编号，课程名称，姓名，志愿号，选课状态） 因为姓名属性只函数依赖于主关键字（学号，课程编号）中的学号， 与课程编号无关，故姓名是部分依赖于（学号，课程编号）， 同理课程名称部分依赖于（学号，课程编号），因此，学生选课表不是II范式。 所以将学生选课表中的学号和姓名分离出来，单独构成一个关系， 但是分离出来的学号和姓名这个关系已被学生表关系包含，故废弃此关系。 同理：课程编号和课程名称也需要分离出来，但被课程表包含， 故废弃此关系。剩余的志愿号，选课状态加上主关键字构成关系，即： 即：选课表（学号，课程编号，志愿号，选课状态）是II范式。 问题：II范式仍存在数据不一致性，故需进一步规范为III范式。 III范式： 首先是II范式，且关系中的任何一个非主属性都不函数传递依赖于主关键字。 问题3：学生表、课程表、学生选课表是不是III范式？如果不是，请将其规范为III范式（消除传递依赖关系）。 将非III范式规范为III范式的方法： 将传递依赖关系中的传递依赖的非主属性和中间属性从关系中提取出来，单独构成一个关系；将关系中余下的其他属性加上传递依赖的中间属性和主关键字，构成关系。 解决：学生表、选课表是III范式。 课程表（课程编号，课程名称，课程类别，学分，教师，系部编号，系部名称，上课时间，限选人数，报名人数，被选中人数） 因为课程编号决定系部编号，系部编号决定系部名称，系部名称是通过系部编号的传递而依赖主关键字课程编号，即系部名称和课程编号间存在函数传递依赖关系，因此，课程表不是III范式。 所以将课程表中的系部编号和系部名称分离出来，单独构成一个关系，剩余的属性加上中间属性和主关键字构成关系，即： 课程表（课程编号，课程名称，课程类别，学分，教师，系部编号，上课时间，限选人数，报名人数，被选中人数）是III范式； 系部表（系部编号，系部名称）是III范式。 选课表（学号，课程编号，志愿号，选课状态）是III范式。其他考虑：
学生表（学号，姓名，班级名称，选课密码） 因为学生表在更新班级属性值时可能会出现数据不一致的情况，故需要将班级属性分离出来，与班级的系部编号，共同构成：
班级表（班级编号，班级名称，系部编号），
学生表则变为： 学生表（学号，姓名，班级编号，选课密码）
