SQL课后小笔记

整数型：按照取值范围从大到小，包括bigint，int，smallint，tinyint，bit，就是整数

数值型：分两种，一种是精确数值型，numeric，decimal 和近似数值型float，real 以numeric为例子，格式为numeric(5,1)，即表示数据长度为5，小数位为小数点后一位，多的话为四舍五入。 float和real用来存储数据的近似值，当数值的位数太多时，可用它存取数值的近似值。

日期型：按照时间范围从大到小，包括datetime和smalldatetime，smalldatetime可以精确到秒，还有一个常用的日期型是date，这种数据类型只显示日期，不显示时间

字符型：包括char，varchar，nchar，nvarchar，text，ntext。

用SQL命令创建数据库格式如下：

用 [ ]括起来的内容，表示创建数据库的过程中可以选用或者不选用，例如，在创建数据库的过程中，如果只是用第一条“creat datebase 数据库名称”语句，DBMS将会按照默认的属性创建数据库。

可以使用“alter database 数据库名称”命令修改数据库，先介绍主要参数： add file：向数据库中添加数据文件 add log file：想数据库中添加日志文件 remove file：从数据库中删除逻辑文件，并删除物理文件，如果文件不为空，则无法删除 modify file：指定要修改的文件 add filegroup：向数据库中添加文件组 remove filegroup：想数据库中删除文件组，若文件组费控，无法将其删除，需要先从文件组中删除所有文件 modify filegroup：修改文件组名称，设置文件组的制度（read_only）或者读写（read_write）属性，指定文件组为默认文件组 alter database 命令可以在数据库中添加或删除文件或文件组，更改数据库属性或其文件盒文件组，更改数据库排序规则和设置数据库选项。 举几个例子了解上述如何使用：

修改Teach数据库中的Teach_data文件增容方式为一次增加20mb

给数据库Teach添加一个次要数据文件，逻辑名称Teach_datanew，存放在e盘根目录下，文件名为Teach_datanew.ndf，数据文件初始大小100MB，最大容量为200MB，文件自动增长容量为10MB

从Teach数据库中删除上例刚增加的次要数据文件

1.可以使用系统存储过程Sp_helpdb来显示数据库结构，其语法如下：

使用sp_helpdb系统存储过程可以显示指定数据库的信息，如果不指定数据库名称，则会显示在master.dbo.sysdatabases表中存储的所有数据库信息，命令执行成功会返回0，否则返回1。

2.可以使用存储过程sp_helpfile来显示当前数据库中的文件信息，其语法如下：

如果不指定文件名称，则会显示当前数据库中所有的文件信息。

3.可以使用系统存储过程sp_helpfilegroup来显示当前数据库中的文件组信息，其语法如下：

如果不指定文件组名称，则会显示当前数据库中所有的文件组信息

其实创建数据表也是属于数据定义的内容，即是对型的操作，其sql语法格式如下：

default：若某字段设置有默认值，则当该字段未被输入数据时，以该默认值自动填入该字段 数据类型：在sql中用如下所示的格式来表示数据类型以及它所采用的长度，精度和小数位数，其中的N代表长度，P代表精度，S表示小数位数。 binary(N) ------------binary(10) //二进制字符串型 char(N)----------------char(20) //定长字符型 numeric(P,[S])---------------numeric(8,3) // 精确数值型，数据长度为8，小数点后有四位，例如12345.678

例子：

在sql server中，对于数据表的约束分为列约束和表约束，其中列约束时对某一个特定列的约束，包括在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名，而定义表约束时必须指出要约束的列的名称。 完整性约束的基本语法格式为：

约束名：约束不指定名称时，系统会给定一个名称 约束类型：在定义完整性约束时必须指定完整性约束的类型，在sql server中可以定义5中类型的完整性约束，下面分别加以介绍：

其语法格式如下：

[例子] 建立一个s表，对 sno 字段进行not null，不准为空约束

其中s_cons 为指定的约束名称，有了 not null 约束，在 s表中录入数据，当sno为空时，系统将给出错误信息，无not null约束时，系统缺省为null

unique约束用于指明基本表在某一个列或多个列的组合上的取值必须唯一，不能重复 其语法格式如下：

[例子]建立一个s表，定义sn为唯一键

[例子]建立一个s表，定义sn+sage为唯一键，此约束为表约束

primary key 约束用于定义基本表的主键，起唯一标识作用，其值不能为null，也不能重复，一次来保证实体的完整性,该约束既可以表约束也可以列约束 其列定义语法如下：

其表定义语法如下：

[例子] 建立一个s表，定义Sno为s的主键

[例子]建立一个sc表，定义sno+cno为sc的主键

foreign key 约束指定某一列或几列作为外部键，其中包含外部键的表称为从表，包含外部键所引用的主键或唯一键的表成为主表，系统保证从表在外部键上的取值时主表中某一个主键值或唯一值，或者取空值。 其语法格式如下：

[例子]建立一个sc表，定义 sno，cno为sc的外部键，主表分别时s表和c表

check约束用来检查字段值所允许的范围，如一个字段只能输入整数，而且限定在一个范围内，一次来保证域的完整性。 在建立check约束时，需要考虑几点因素 1.一个表可以有多个check 2.每个字段只能定义一个check约束 3.在多个字段上定义的check约束必须为表约束 4.当执行insert，update语句时，check约束将验证数据 其语法如下：

[例子]建立一个sc表，定义score的取值范围为0-100之间

当 where 子句需要指定一个以上的查询条件时，则需要使用逻辑运算符and，or，not将其连接成为复合的逻辑表达式

[例子]查询sc表中选修了c1或者c2，并且分数大于85分的学生的学号（sno），课程号（cno），成绩（score）

[例子] 查询teacher表中工资(sal)在1000到1500元之间的教师的教师号（Tno）和姓名（name）

[例子]查询选修c1或c2的学生的学生学号和成绩

以上各例子属于完全匹配查询，当不知道完整精确的值时，用户可以通过like关键字来进行模糊查找，like定义的一般格式为：

其中属性名必须为字符型，字符串常量中的字符可以包含通配符，利用这些通配符，可以进行模糊查询

[例子] 查询教师中第二个汉字是“力”字的姓名

[例子]求学号为s1的学生的学生的总分和平均分

[例子]求选修了C1号课程的最高分，最低分，及之间相差的分数

group by 子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值， [例子] tc表查询每个教师的教师号（tno）以及任课（cno）的门数

若在分组后还要按照一定的条件进行筛选，则需要使用having 子句 [例子]sc表查询选修两门以上含两门课程的学生的学号和选修门数

group by子句按某值进行分组，所有具有相同值的元组为一组，然后再使用库函数对组中的元素进行计算 当一个sql查询中同时使用where 语句，group语句，having语句时，其顺序为where，接着group by，最后having，where和having 子句根本区别在于作用的对象不同，where作用于基本表，从中选择满足条件的元组，having 作用于组，选择满足条件的组

当需要对查询结果排序时，应该使用order by 子句，order by子句必须出现其他子句之后，排序方法可以指定，desc为降序，asc为升序，缺省时为升序

[例子]sc表查询选修c1课程（cno）的学生学号（sno）和成绩（cno），并按成绩降序排列

[例子]查询选修了c2，c3，c4或c5课程的学号，课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排序

显示效果如下：

表的连接方式分两种 1.表之间满足一定条件的行进行连接时，from子句指明进行连接的表名，where子句指明连接的列名以及连接的条件，即内连接查询 2.利用关键字join进行连接，即外连接查询 关键字join具体的连接方法分为以下几种 inner join显示符合条件的记录，此为默认值 left outer join 成为左连接，直接理解左边为主表即可，想以左边的为基准，此时右边有不符合的条件数据行时以null来显示 right outer join 成为左连接，直接理解右边边为主表即可，想以右边的为基准，此时左边有不符合的条件数据行时以null来显示 full outer join 显示符合条件的数据行以及左边表和右边表中不符合条件的数据行，缺乏数据以null来显示

[例子]查询刘伟老师所讲授的课程，要求列出i教师号，教师名，课程号（p102）

这里tn=‘刘伟’为查询条件，而T.TNO=TC.TNO为连接条件，tno为连接字段，

[例子]查询所有选课学生的学号，姓名，选课名称以及成绩（p103）

[例子]查询每门课程的课程号，课程名和选课人数

在内连接中，不满足连接条件的元组不能作为查询结果输出，比如学生a表有若干数据，主键为学号，s1到s6，选课b表有若干数据，其中也有学号，和相对应的选课号，但是学号只有在s1和s5，如果将两表使用a.学号=b.学号的条件进行内连接，会导致s6学生消失，这种情况则推荐外连接查询。

在外部连接中，参与连接的表有主从之分，以主表的每行数据去匹配从表的数据列，不符合连接条件的列，将被填上null值。

外部连接分为左外部连接和右外部连接两种，以主表方向区分，主表在左边，则称为左外部连接，使用关键字left outer join ，主表在右边，则称为右外部连接，使用关键字right outer join 。

内部连接语法格式如下：

左：

右：

[例子]查询所有学生的学号，姓名，选课，成绩（没有选课的学生选课信息为null）(p103)

一个表与其自身连接，称之为表的自身连接,通常时自己表的同类东西对比同类东西，需要划分两个，在进行对比 [例子]查询所有比“刘伟”工资高的教师姓名，工资，和刘伟的工资（p.104）

显示效果如下：

普通子查询的执行顺序是：首先执行子查询，然后把子查询的结果作为父查询的查询条件值。

因为只返回一个值可以使用比较运算符。 [例子] 查询与“刘伟”老师职称相同的教师号，姓名

首先执行子查询，返回了刘伟老师的职称，就一个值，接着执行父查询

这种情况就没办法使用比较运算符了，因为子查询返回的是一组数据，是一个集合，得使用关键字 any或 all，具体例子如下： （any 其实就是关键字 in，用法是其中任意一个符合） （all的含义如字面意思，全部） [any例子] 查询讲授课程号为c5的教师姓名

这里子查询的结果是所有选修了c5课程的老师号，是一个集合，并不是单个数值，这里any的作用是tno只要和集合中任意一个符合即可。

[all例子]查询其他系中比计算机系所有教师工资都高的教师姓名和工资

这里all的作用代表全部，即sal要比当前集合里的所有元素都要大

有时子查询的查询条件需要引用父查询表中的属性值，我们把这类查询成为相关子查询 相关子查询的顺序为：首先选取父查询表中的第一行记录，内部的子查询利用此行中相关的属性值进行查询，查询的结果可能会成为一个集合，然后父查询根据子查询返回的结果判断此行是否满足查询条件，对即放入结果集合中，一直重复执行这一过程，直到处理完父查询表中的每一行数据。 由此可以看出在相关子查询中，子查询的执行次数是由父查询表的行数决定的

[例子] 查询不讲授课程号为C5的教师名称

首先读取父查询的第一行数据，接着将第一行数据和子查询的每行数据匹配找到对应的数据形成集合，接着判断条件not in，对则保存到结果集，接着下一行，然后重复此操作。

此外，使用exists也可以进行相关子查询，exists是表示存在的量词，带有exists的子查询不返回任何实际数据，它只能得到 “真” 或 “假” 两种逻辑值，即当子查询的查询结果集合为非空时，外层的where返回 true，反之返回 false [例子] 用含有exists的语句完成查询授课程号为C5的教师姓名

反之，要查询没有教c5课程的教师姓名也非常简单，在exists前加not 即可

[重点例子]查询选修了全部课程的学生

说句实在话就是并集，将两个或多个查询通过关键字 union 进行拼接起来

最后将两个查找结果拼接在一起

使用select ··· into ····语句可以将查询结果存储到一个新建的数据库表或者临时表

即可将每个学号和其总分存储到名为stu_table的表中

where后跟条件，即可找到想找到的对象，可以根据子查询的模式进行修改

例如创建一个计算机系教师情况的视图compter_teacher

选定视图选定需要修改的列进行修改

视图定义后，其对视图的操作和对基本表的操作一样 比如有一个所有老师数据的基本表 teacher 和一个只有计算机老师的视图compter_teacher 他们查询职称为计算机教授的代码分别如下：

由于视图是一张虚表，所以对视图的更新，最终转换成对基本表的更新，其更新操作包括添加，修改，删除数据，其语法格式与对基本表的更新操作一样

以上对视图的操作都会变成对基本表的操作

规则与check约束很相似，相比之下，在alter table 或create table 命令中使用的check约束是更标准的限制列值的方法，但check约束不能直接作用于用户自定义数据类型，规则和约束是可以同时使用的，标的列可以有一个规则及多个check约束

语法如下：

[例子]创建学生年龄规则，年龄必须大于等于18并且小于等于50