教学课件：《SQL

1、计算机应用基础第一章 SQL Server概述本章主要内容SQL及其特点SQL Server的结构SQL Server的安装几个问题什么是数据？什么是数据库？数据库有什么用处？日常生活中与自己密切相关的数据库有哪些？数据：存储在某一种媒体上能够识别的物理符号。数据有数字、文字、图形、图象、声音等多种表现形式。数据库：(DataBase，DB )是指存储在计算机存储设备上结构化的相关数据集合。它不仅包括描述事物的数据本身，而且还包括相关事物之间的联系。可以被多个用户共享的、与应用程序相互独立。1.1 SQL与SQL Server 1. 概念 SQL是用来对存放在计算机中的数据库进行组织、管理和检

2、索的语言。SQL一词是“Structured Query Language（结构式查询语言）”的缩写1.1.1 SQL及其特点 ANSI 着手SQL标准化工作 ANSI 第一个SQL标准 ISO SQL标准 1992 ISO SQL-92至今- SQL 标准计算机数据库查询语言 数据定义(Definition)数据操纵(Manipulation）数据管理(Management)存取保护(Access Protection)处理控制(Control) SQL功能：如何确定数据存储位置？表(table)索引(index)码(keys)行(rows)列(columns)namesexageIDJoh

3、nf18Jiferm17mayam16SQL本身并不是一个很完整的编程语言，如不支持流控制等。一般它都与其他编程语言(如DELPHI、Powerbuilder、VB、VC等)结合来使用，这些用来作为前台开发语言，而这些语言大多数是面向对象的语言2.特点（1）一体化的特点（2）统一的语法结构，多种使用方式（3）高度非过程化（4）语言简洁（5）客户机/服务器（Client/Server）结构（6）支持异类复制（7）Internet数据库功能的集成(1)一体化的特点SQL语言能完成数据库生命期当中的全部活动，如：定义关系模式录入数据以建立数据库查询、更新、维护数据库重构数据库安全性控制(2)统一的语

4、法结构，多种使用方式SQL有两种使用方式联机使用方式嵌入程序方式大多数的程序接口采用嵌入的SQL虽然使用方式不同，SQL的语法结构是一致的。(3)高度非过程化 在SQL中，只需用户提出“干什么”，而无须指出“怎么干” 存取路径的选择和SQL语句操作的过程由系统自动完成 如： 查询等，不必指明路径(4)语言简洁SQL语法简单、易学SQL功能动词数据定义CREATE数据操纵INSERT、UPDATE、DELETE、SELECT 数据控制 GRANT SQL按其功能可以分为三大部分：数据定义语言(DDL)定义、撤销和修改数据库对象数据操纵语言(DML)数据库中数据的修改和检索数据控制语言(DCL)用

5、于数据访问权限的控制（5）SQL Server 的应用结构 目前数据库应用系统的开发一般是基于Client/Server（简称C/S）模式的，这样的系统结构有3个部分：服务器（Server）、客户端（Client）和网络(6)支持异类复制： 它可以将SQL Server数据复制到其他的数据库中 包括Access、Oracle、Sybase和DB2，并采用ODBC作为其连接机制(7)Internet数据库功能的集成： SQL Server的数据库引擎提供对Web技术的支持，支持数据库信息自动发布到HTML文档1.1.2 SQL Server的特点数据库管理10个最重要的特点有关开发的10个最重要

6、的特点有关商业智能特征的10个最重要的特点1.1.3关系数据库模型简介 关系数据库是一种所有用户可见数据都严格按表的形式组织起来的表，且所有库操作都针对这些表中的数据 关系数据模型是以集合论中的关系(relation)概念为基础发展起来的数据模型。关系是由若干个二维表组成的集合。每个二维表又称为关系。关系模型是用二维表格结构来表示实体以及实体联系间模型几个概念数据模型： 是数据库管理系统用来表示实体与实体之间联系的方法。 实体： 客观事物在信息世界中称为实体（Entity），它是现实世界中任何可区分、识别的事物。实体可以是具体的人或物，也可以是抽象概念 例： 图书 联系人 实体具有许多特性，实

7、体所具有的特性称为属性（Attribute）。一个实体可用若干属性来刻画 例： 联系人 图书数据库系统所支持的模型主要有关系模型层次模型网状模型通过关系，按给定的选择条件，选出符合条件的元组，较灵活要查找一个记录必须从根记录开始，按给定条件沿一个层次路径进行查找在查找语句中要说明查找的对象和存取的路径，操作较繁琐 班级社团学生关系模型是RDBMS的基础，它包括三部分数据结构关系的完整性规则 关系操作集合 一般ODBC所支持的数据源大部分是关系数据库管理系统(RDBMS)。 关系数据库是应用数学方法来处理数据库数据的，与层次模型和网状模型相比，有很大改进。数据库管理系统：是指数据库系统中对数据进

8、行管理的软件系统改进表现在如下几点：面向集合的处理，可以一次操作多个行数据的逻辑独立性，使得应用程序不随数据库的改变而改变数据的自动导航，数据的访问路径由数据库优化器决定，方便了用户操作 关系模型的数据结构为单一的数据结构由行和列组成的两维表，任意两行互不相同，列值是不可分的数据项，行和列的次序可任意 abcdabcdaxyz两行相同d=m+n列值可分关系模型的完整性实体完整性参照完整性用户定义的完整性用主键唯一标志表中行和列，主键的任一属性不能为空外键或者为空，或者等于它所参照的表的主键的某个值对某一具体的数据库的约束条件 关系模型的操作表达能力非常强大，定义了很多的操作，其中主要有选择(s

9、elect operation)、投影(project operation)、集合、连接等操作 1.2 SQL Server 的结构 目前数据库应用系统的开发一般是基于Client/Server（简称C/S）模式的，这样的系统结构有3个部分：服务器（Server）、客户端（Client）和网络SQL使应用程序采取分布式C/S结构前端(pc端)：交互式查询、报表打印和应用程序后端(服务器端) ：存储和数据管理的数据库引擎SQL作为前端工具和后端引擎之间通信的桥梁分布式：在一个或多个网络中可有多个SQL Server ，用户可以将数据分别存放在各个SQL Server上，成为分布式数据结构1.3

10、SQL Server 2005的安装 SQL Server 2005 版本（1）SQL Server 2005 Enterprise Edition(32位和64位)（2）SQL Server 2005 Standard Edition(32位和64位)（3）SQL Server 2005 Workgroup Edition(仅适用于32位)（4） SQL Server 2005 Developer Edition(32位和64位)（5） SQL Server 2005 Express Edition(仅适用于32位)32位平台上安装和运行SQL Server 2005的硬件要求1.3.1 S

11、QL2005的安装要求SQL Server 2005各组件对磁盘空间的要求64位平台上安装和运行SQL Server 2005的硬件要求1.2.2 SQL Server 2005的安装内容数据库包括： 系统数据库+用户数据库在安装SQL Server时，安装程序创建系统数据库master数据库model数据库msdb数据库tempdb数据库示例数据库PubsNorthwind master数据库：记录SQL Server的所有系统级信息，包括：master数据库记录SQL Server的初始化信息实例范围的元数据端点链接服务器系统配置设置其他数据库 model数据库用作在SQL Server实

12、例上创建的所有数据库模板 由于SQL Server每次启动时都要创建 tempdb 数据库，model 数据库必须一直存在于SQL Server系统中创建数据库时系统自动复制Model中的内容，然后用空页填充新数据库的剩余部分msdb 数据库供 SQL Server 代理计划警报和作业和作业 tempdb数据库保存所有的临时表和临时存储过程，并满足任何其它临时存储要求 tempdb 数据库是全局资源，在SQL Server每次启动时都重新创建，属于无垃圾数据库 pubs 示例数据库以一个图书出版公司为模型，用于演示SQL Server数据库中可用的许多选项 该数据库及其中的表经常在文档内容所介

13、绍的示例中使用 如果更改了 pubs 数据库，可以使用 SQL Server 安装中 Install 目录下的文件重新进行安装 1.3.3 SQL Server 2005的安装过程48第二章 数据库的基本操作 49本章主要内容文件和文件组创建数据库管理数据库50SQL Server 2005 数据库至少具有两个系统文件：表、存储过程、触发器、视图2.1 文件和文件组数据文件日志文件恢复数据库中的所有事务所需的信息51 SQL Server中常用的数据库对象对象 作用表 数据库中数据的实际存放处所视图 定制复杂或常用的查询，以便用户使用； 限定用户只能查看表中的特定行或列； 为用户 提供统计数据

14、而不展示细节索引 加快从表或视图中检索数据的效率存储过程 提高性能；封装数据库的部分或全部细 节；帮助在不同的数据库应用程序之间实 现一致的逻辑约束、规则、 确保数据库的数据完整性；强制执行业务 默认值和触发器 规则登陆、用户、角色 保障数据安全的基础和组52主要数据文件： 该文件包含数据库的启动信息，并用于存储数据，扩展名是.mdf次要数据文件(可选)：由用户定义并存储，这些文件含有不能置于主要数据文件中的所有数据,扩展名是.ndf 事务日志：这些文件包含用于恢复数据库的日志信息。每个数据库都必须至少有一个日志文件,扩展名是.ldf53并将这三个文件指派到文件组group中文件组：为了方便数

15、据的分配、放置和管理，SQL Server允许对文件进行分组处理。在同一个组里的文件组成文件组Data1.ndfHDDData3.ndfHDDData2.ndfHDD然后，可以在文件组group上创建表。对表中数据的查询将被分散到三个磁盘上，查询性能将得到提高54 数据库的文件组成事务日志文件*.ldf一到多个次数据文件*.ndf零到多个次数据文件*.ndf零到多个主数据文件*.mdf仅有一个主文件组次文件组事务日志仅有一个零到多个55需要注意的有：文件或文件组不能由一个以上的数据库使用例如:如果文件 sales.mdf 和 sales.ndf 包含了数据库sales中的数据和对象，那么任何其

16、它数据库都不能使用这两个文件件不能属于任件组 562.2.1以图形化界面创建数据库2.2.2用T-SQL命令创建数据库 2.2 创建数据库Transact-SQL交互式查询语言572.2.1以图形化界面创建数据库演示582.2.2用T-SQL命令创建数据库CREATE DATABASE database_name ON PRIMARY ,n ,n LOG ON,n COLLATE collation_name WITH;数据库名称指定关联的定义主文件59CREATE DATABASE database_name ON ,n FOR ATTACH WITH |ATTACH_REBUILD_LOG

17、;数据库名称通过附加命令创建数据库的命令格式：指定从现有的一组操作系统文件中附加数据库 60:=( (NAME=logical_file_name, FILENAME=os_file_name,数据文件或日志文件的定义数据库的启动位置，也是数据库中其他文件的入口位置 指定文件的操作系统文件名和路径 61,SIZE=sizeKB|MB|GB|TB,MAXSIZE=max_sizeKB|MB|GB|TB|UNLIMITED,FILEGROWTH=grow_incrementKB|MB|GB|TB|%),.n指定数据或日志文件的大小 ，缺省为1MB 指定文件能够增长到的最大长度 ，若不指定长度，将一

18、直增长到盘满 文件的增长增量 ，缺省为10%，最小值为64KB 62:=FILEGROUP filegroup_nameDEFAULT ,:= DB_CHAINIGON|OFF|TRUSTWORTHYON|OFF 控制外部与数据库之间的双向访问数据库文件组的定义63:= ENABLE_BROKER|NEW_BROKER|ERROR_BROKER_CONVERSATIONS功能：帮助数据库人员构建安全、可靠和可伸缩性的应用程序64LOG ON (NAME=logical_file_name,FILENAME=os_file_name,SIZE=size,MAXSIZE=max_size|UNLI

19、MITED,FILEGROWTH=growth_increment),.nCOLLATE collation_name指定建立数据库的日志文件 指定数据库的缺省排序方式包含控制语言或字母表用法的规则 65CREATE DATABASE My_DBON PRIMARY （NAME My_DBData,FILENAME=C:My_DB .mdf,SIZE=10MB,MAXSIZE=30MB,FILEGROWTH=2MB)例：在C盘根目录下创建名为My_DB的数据库，其主文件大小为10MB，最大大小为30MB，日志文件大小为10MB。66LOG ON （NAME My_DB Log,FILENAME

20、=C:My_DB.ldf,SIZE=10MB,MAXSIZE=20MB,FILEGROWTH=25%)COLLATE Chinese_PRC_CI_AS67 2.2.3 事务日志 每个 SQL Server 2005 数据库必须都具有事务日志。事务日志是数据库的一个重要组件，如果系统出现故障，它将成为最新数据库的唯一来源前面的例子，创建数据库时指定了一个日志文件事务日志是数据库中已发生的所有修改和执行每次修改的事务的一连串记录。事务日志记录每个事务的开始。68事务日志支持的操作恢复个别事务SQL Server启动时恢复所有未完成的事务将还原的数据库、文件、文件组或页前滚到故障点692.2.4

21、查看数据库信息 （1)图形化界面查看数据库信息 演示70(2) T-SQL命令查看数据库信息 在T-SQL中，常用的: 函数DATABASEPROPERTYEX或 系统存储过程sp_helpdb来显示数据库和数据库参数的有关信息71使用函数DATABASEPROPERTYEX的语法为：SELECT DATABASEPROPERTYEX （database,property）其中：Database：需要查看的数据库的名称Property：数据库的属性 使用系统存储过程sp_helpdb的语法为：EXEC sp_helpdb database_name 72例：分别用函数DATABASEPROPE

22、RTYEX和系统存储过程sp_helpdb两种方法来查看数据库My_DB的属性SELECT DATABASEPROPERTYEX （My_DB,Collation)goEXEC sp_helpdb My_DBgo732.3 管理数据库 打开数据库 增加数据库容量 查看及修改数据库选项缩减数据库容量 更改数据库名称 查看数据库个数 删除数据库74打开数据库：use database_name 在连接SQL Server时，假如没有预先指定用户连上哪个数据库，系统会自动连上master数据库，此时要防止由于用户操作不当而造成master数据库的破坏2.3.1打开数据库75(1)用T-SQL命令扩增

23、数据库容量使用权限默认为dbo(数据库拥有者)扩增数据库命令语句如下：ALTER DATABASE database_nameMODIFY FILE( NAME=file_name,SIZE=newsize)要扩容的数据库要扩容的数据库文件为该文件指定新容量2.3.2增加数据库容量76例：数据库文件My_DBData的初始分配空间大小为10MB，指派给my_DB数据库使用，现在想将其大小扩充至20MB，则具体操作命令如下：USE My_DBgoALTER DATABASE My_DBMODIFY FILE NAME = My_DBData, SIZE = 20 MB 77(2)通过图形化界面增

24、加容量演示782.3.3查看及修改数据库选项（1）T-SQL界面查看用sp_options系统存储程序查看：Exec sp_dboption mastergo79(2)用图形化界面查看及修改选项演示 80(3) 用T-SQL命令更改数据库选项更改命令如下(使用权限：sa, dbo)：sp_dboptiondname,optname,true|false其中：dname:想操作的数据库名称Optname：欲更改的数据库选项true,false：设定数据库选项的值例：更改My_DB为只读状态Exec sp_dboption My_DB,read only, truego812.3.4缩减数据库容量

25、 用户可以通过缩减数据库容量来节省设备空间的浪费，其操作也可以通过T-SQL命令和图形化方式来完成82 执行DBCC SHRINKDN命令来完成，但在此之前需用sp_dboption命令，将想缩减容量的数据库设定为单用户模式缩减数据库容量命令语句：DBCC SHRINKDATABASE(database_name,new_size,MASTEROVERRIDE)(1)用T-SQL命令想缩减的数据库数据库剩下的容量缩减master系统数据库默认权限为sa,dbo83例：缩减My_DB数据库的容量Exec sp_dboptionMy_DB,single user,truegouse My_DBgo

26、DBCC SHRINKDATABASE(My_DB)goexec sp_dboption My_DB,single user,falsego84(2) 通过图形化界面操作演示 852.3.5更改数据库名称 通过T-SQL命令更改名称语句如下：sp_renamedb OldName,NewName使用权限：仅sa可以更改数据库名称例：更改数据库“My_DB”名称为 “tsinghua”Exec sp_renamedb My_DB,tsing huaGo862.3.6查看数据库数量 在SQL查询分析器中执行如下命令：USE master goSELECT NAME FROM sys.sysdata

27、bases返回结果：MasterTempdbModelMsdbPubsNorthwindLinguistics872.3.7数据库的删除 当删除一个数据库后，仅能用备份数据重建以前的数据库中的数据 如果还有用户正在使用此数据库，则无法删除 删除权限仅限于dbo, sa也可模拟成dbo执行此命令 88删除数据库命令如下: DROP DATABASE database_name,database_name.或：sp_dbremove database_name例:删除名字为tsinghua的数据库USE mastergoDROP DATABASE tsinghua 要删除的数据库名数据文件和日志文

28、件都清除 89第三章数据表的建立90本章主要内容表的概念SQL Server 的数据类型数据库中表的操作91数据库表记录字段属性nameagetel.john18123marry20345bookAuth.priceEngjohn123.0toolmarry345.53.1 表的概念字段join18123记录92DBComp.priceuserSql Ser.MS10000Qin Hai Univ.OracleOracle20000Beijing Univ.AccessMs300Peoples Univ.93设计数据库时，首先应该做什么？确定数据库所需的表每个表中数据的类型可以访问每个表的用户

29、94创建表及其对象之前做什么？数据类型列数那些列允许空值是否要使用以及何处使用约束、默认值和规则所需索引的类型95创建表的方法?创建一个基表添加数据96 数据类型定义了各列允许使用的数据值。3.2 SQL Server 的数据类型97指定数据类型的方法？使用SQL Server 2005 系统数据类型创建基于系统数据类型的别名数据类型从在Microsoft .NET Framework公共语言运行时创建的用户定义类型98需要定义数据类型的情况？建立表字段申请局部变量申请存储过程中的局部变量转换数据类型本章讨论的内容99数据类型 二进制型 binary(n), varbinary(n)，imag

30、e字符型 char(n)，varchar(n)日期及时间 datetime, smalldatetime 精确数值型 decimal(p,s),numeric(p,s)int等近似数值型 float(n)，Real 货币型 money, smallmoney 特殊类型 bit,timestamp100binary的存取速度比varbinary快，当表中数据长度很固定时，应考虑用binary类型。二进制数binaryvarbinary存储空间固定的数据类型，指定长度8KB时，image数据101字符数据由字母、符号和数字的任意组合组成由单引号括起来字符数据char(n):列大小固定varchar

31、(n):列大小不固定最多为8000字节查询速度较快输入字符长度8KB的ASCII字符，text数据102 text和image数据类型 text:最多为231-1个符号，存文字image:最多为231-1个binary数据，存图形 两者均是可变长度的数据类型,占较多空间text字段专用于存储字符（非Unicode）数据，image字段专用于存储二进制数据，每条记录的text字段或image字段均有一个或多个存储页(2KB)存放它的值 103日期及时间型数据 datetimeSmalldatetime数字 分隔符 数字 分隔符 数字 时间 时间dmy: 15/04/1996ydm: 1996/1

32、5/04mdy04/15/199604-15-19964.15.1996myd：04/1996/15斜杠、连字符、句号 dym: 15/1996/04 ymd: 1996/04/15 104p：精度值s:小数位数 4.5精确数值数据 decimal(p,s)numeric(p,s)近似数值数据 real 精度在17之间float 精度在815之间 货币数据： money、smallmoney 加前缀，如 $123负货币值，加-123105整型数据类型(1) bigint：占8字节的存储空间，存储数据范围为2632631。(2) int：占4字节的存储空间，存储数据范围为 2312311。(3)

33、 smallint：占2字节的存储空间，存储数据范围为2152151。(4) tinyint：占1字节的存储空间，存储数据范围为0255。106Unicode字符数据 使用Unicode数据类型，列可存储由Unicode标准定义的任何字符，包含由不同字符集定义的所有字符nvarchar 列大小不固定 4000个字符数nchar 列大小固定 4000个字符数107特殊类型数据bit:存放1或0的逻辑类型数据，对于0以外的值，一律以1表示User-Defined DataType: SQL Server还允许用户依据系统数据类型自定义数据类型 timestamp:时间标签类型与系统时间无关, 只是

34、个单调增加的计数器，其值在数据库中是唯一的若有名为timestamp的字段而未定义类型,自动为timestamp类型，若定义为其它类型也可。为免混乱，最好不这样做108关于自定义数据类型sp_addtype typename, phystype,nulltype 用户自定义数据类型的名称 SQL Server提供的数据类型名称 是否允许用户自定义数据类型为NULL值 把zipcode作为自定义数据类型,长度为6EXEC sp_addtype zipcode , char(6)删除自定义的数据类型zipcodeEXEC sp_droptypezipcode109xml数据类型xml数据类型允许用

35、户在SQL Server数据库中存储XML文档和片段110SQL SERVER对象标识符需要命名的对象数据库 表 列 文件组 文件约束 规则 默认值 索引 视图 触发器 存储过程 用户自定义函数服务器 角色 登陆 全文本目录 用户自定义类型111命名规则1.对象的名字必须以Uunicode2.0规范定义的任意字母开头。2.正常对象的名字可多大128个字符，而临时对象的名字可多大116个字符。3.与SQL Server关键字相同或包含内嵌空格的名字必须使用双引号（“ ”）或方括号（ ）。1123.3 数据库中表的操作 在SQL Server中创建数据库可以通过Microsoft SQL Serv

36、er Management Studio图形化界面和在SQL查询分析器输入T-SQL语言假定已经建好三个数据库My_DB1、My_DB2和My_DB3 1133.3.1表的创建 create table database. owner.table_name ( col_name column_properties constraintconstraint | ,constraint ,next_col_name|next_constraint )表所在的数据库 表的所有者 列名列属性 （1）使用T-SQL114CREATE TABLE My_DB1.dbo.stud_info(stud_id

37、int CONSTRAINT perid_chk NOT NULL PRIMARY KEY,name nvarchar(5) NOT NULL,birthday datetime,gender nchar(1), address nvarchar(20),telcode char(12),约束名 例：在数据库My_DB1中创建stud_info表，表中包括：学号、姓名、出身年月、性别、地址、电话号码、邮编，所在系代号，每月生活补贴等。115zipcode char(6) CONSTRAINT zip_chk CHECK(zipcode LIKE0-90-90-90-90-90-9), Dept

38、Code tinyint CONSTRAINT DeptCode_chk CHECK(DeptCode=0 and math_score=0 and engl_score=0 and comp_score=0 and chem_score=0 and phys_score=100),CONSTRAINT pk_chk PRIMARY KEY(year,stud_id) 约束名119本例中的主键定义为primary key(year,stud_id)以year和stud_id的组合作为主键一个表只能有一个主键且值唯一需要多列组合作为主键时要把作为主键的列用括号组合起来120带有参照性约束的表的创

39、建 例：在数据库My_DB2中建一个名为device_manage的表，包括的信息有：仪器编号仪器名称仪器所属试验室的实验室名称同种仪器的数量仪器的购买价格仪器的供货商 121CREATE TABLE My_DB2.dbo.device_manage(dev_id varchar(15) CONSTRAINT pk_chk NOT NULL PRIMARY KEY,dev_name varchar(20) NOT NULL,lab_id varchar(20) NOT NULL,dev_qty int,unit_price money,supply_id varchar(15) )122再在同

40、一数据库中创建一张用于记录试验仪器使用情况的表device_use。信息有：实验名称实验地点实验日期试验学生学号该学生使用的仪器编号123假设每次实验中每个学生用且只用同一台仪器，该仪器必须为device_manage表中所记录的仪器，即表device_use中的仪器编号必须参照表device_manage中的仪器编号。 124CREATE TABLE My_DB2.dbo.device_use(experiment_name varchar(20)，experiment_lab varchar(20),experiment_date datetime,stud_id int ,dev_id

41、varchar(15) CONSTRAINT fk_chk REFERENCES device_manage(dev_id) 125（2）使用图形化界面指定数据库表单击右键新建表：字段、完整性约束、表名 1263.3.2 表的删除 USE My_DB1godrop table stud_infogo 删除stud_info表： 1273.3.3 数据库中表的修改 (1)ALTER 命令 ALTER TABLE database.owner.table_nameWITH NOCHECKADDcolumn_name datatype column_constraints |,table_const

42、raint |不指定此选项时(默认情况)，增加的约束要对现有数据进行检查，若发现违反约束，ALTER TABLE语句将中止运行ADD允许在现存的表中增加新数据项或者一个约束 128指定删除数据库中的约束 DROP CONSTRAINTconstraint_name| COLUMN column_name ,.n DROP指定删除现存表中的一个字段或约束指定删除数据库中的列 129例：向表stud_info中加入字段名为dormitory 的一列，以记录该学生的宿舍号。该列的属性为nvarchar(10)，缺省值为紫荆公寓8号楼 ALTER TABLE My_DB1.dbo.stud_infoA

43、DD dormitory nvarchar(20) NULL DEFAULT(紫荆公寓8号楼)drop和add子句不要同时出现130值得注意的是：在删除某列时，基于该列的所有索引和约束必须首先被删除掉。例如，如果用户运行下面的命令想删除新增加列dormitory时，将出现错误。ALTER TABLE my_DB1.dbo.stud_info DROP COLUMN dormitory 由于默认值约束的存在使删除该列失败。这就需要在删除该列之前，先将其上的约束删除可以手动将字段dormitory上的默认值删除，然后再运行上述命令 131(2) INSERT命令 通过INSERT语句，可以向表中增

44、添新的记录或在记录中插入部分字段的数据INSERT命令的基本语法如下：INSERT INTO table_name(column1,column2) values(value1,value2)表名 字段列表 相应的值列表 132向My_DB1中的stud_info表插入记录 use My_DB1goINSERT stud_info values(0811,张源, 12/15/1979, 1, 北京市海淀区, 100080,3, 260)goINSERT stud_info values(970890,赵明, 8/5/1979,1,上海市浦东区/p>

45、2,201700,2, 260)133输入项的顺序和数据类型必须与表中字段的顺序和数据类型相对应定义表结构时如果定义了缺省值，此时如对相应字段不输入值，则系统会用缺省值插入表中如果插入的数据不能满足约束性条件，就出错134由于参照性约束的存在，必须先向被参照的表中插入数据，然后再向定义了参照性约束的表中输入数据 问题：定义了参照性约束的表，输入数据时应注意些什么？135例：表device_manage中的第一个字段与表device_use中的最后一个字段是参照性约束的关系，被参照的表为device_manage，在表device_use中定义了参照性约束，其输入方法如下： 136use My_

46、DB2goINSERT device_manage values(0789,分光计,光学实验室,20, 5000,北京光学仪器厂)goINSERT device_manage values(0284,高能电子管,电子实验室, 50,900,中科院电子所) 先向device_manage表中插入记录137然后再向device_use表中增加记录 use My_DB2goINSERT device_use values(原子光谱分析,化学实验室, 5/20/2000,970811,0789)goINSERT device_use values(原子光谱分析, 化学实验室, 5/20/2000,97

47、0890,0789) go138由于存在参照约束关系，device_use表中的dev_id字段的值必须是device_manage表的dev_id字段中的值 INSERT device_use values(发动机振动分析,发动机实验室, 3/5/2001, 980609,0234) 139如何插入部分数据？可在INSERT子句中指定列名，values子句中的数据项与指定的列相对应，未列出的列必须具有允许NULL或timestamp或IDENTITY或DEFAULT四种定义的条件之一。跳过的列将以缺省值或者用NULL填充 140例：向dev_manage表新增一行，但只输入dev_id、de

48、v_name、lab_id字段的值。use My_DB2goINSERT device_manage (dev_id,dev_name,lab_id) values(0201,滤光片,光学实验室)141如何把查询结果插入到另一张表中 在INSERT语句中可用SELECT从同一张表或其他表中选择字段插入到表结构与SELECT查询返回的结果相容的表中，可插入多行142INSERT table_name inserttable_column_listSELECT selectable_column_listFROM table_lsitWHERE Search_conditions 需插入记录的表的

49、名称 插入到被插入表中的字段名 从检索表中取出字段名 检索条件 把表table_list中符合检索条件的记录中的column_list字段插入表table_name 143例：查询数据库My_DB2中表device_manage中的dev_id信息，然后将查询结果插入到表device_use中的dev_id字段中use My_DB2INSERT into device_use(dev_id)select dev_id from device_manage 144(3) UPDATE语句 用UPDATE可以改变表中现存行的数据。UPDATE table_nameSET column1=modif

50、ied_value1,column2=modified_values,WHERE column1=value1,column2=value2 指定要更新数据的表名 指定要更新的列及该列改变后的值 指定被更新的记录所应满足的条件 145把上例中在表device_use中新添加的记录进行修改以方便以后的使用 use My_DB2UPDATE device_useSET experiment_name=激光波长测定, experiment_date=6/5/2000,stud_id=970811WHERE (stud_id is NULL and dev_id =0201) 146表device_u

51、se中字段dev_id=0201且字段stud_id为空的记录被更改为如下图所示的最后一行的内容 147(4) DELETE语句 DELETE子句将删除一行或多行记录DELETE FROM table_nameWHERE column1=value1,column2=value2WHERE子句用来指定删除行的条件 148例: 删除数据库My_DB1中表stud_score中的所有记录 USE My_DB1DELETE from stud_score 149例：删除数据库My_DB2中表device_use中学号为空的记录 Use My_DB2DELETE from device_useWHER

52、E stud_id is NULL 150第四章 数据检索151本章主要内容SELECT语句带条件的查询多表查询UNION查询子查询152 在了解了表的创建和修改之后，为获取必要信息，还必须对表进行查询等操作。 查询主要是根据用户提供的限定条件进行，查询的结果还是一张表。153select * from tsing_DB1.dbo.stud_score 1544.1 SELECT 语句 SELECT ALL | DISTINCT select_listINTO new_table_nameFROM table_name,table_name16要查询的字段名，可以是从多个表中取出来的字段 查询

53、结果放到新的临时表中欲查询数据的表的名称 155WHERE conditionGROUP BY clauseHAVING clause依字段类别作总计函数处理 功能同WHERE,不同在于可再次过滤WHERE语句查到的结果 156ORDER BY COMPUTE clause FOR BROWSE 允许用一条SELECT命令同时查询数据，并作数据总计动作 157例：查询数据库My_DB2中表device_manage中的所有信息 use My_DB2select * from device_manage 158例：把表stud_score中comp_score字段名在查询结果的显示中改为comp

54、uter_score,本查询要求列出学年度(year)、学号(stud_id)、计算机科目(computer#)及计算机科目的成绩(表中字段名为comp_score，在输出时改为computer_score)等信息 159select year, stud_id, computer#=Computer score,computer_score=comp_scorefrom stud_score 160例：输出数学成绩math_score，且输出值在原有值得基础上提高了5% SELECT year, stud_id, new_score=math_score*1.05FROM stud_scor

55、e 161在查询结果中还可以引入某些函数进行计算，如：SELECT caption=function_name(col_name)162总计函数有六个，它们分别是：SUM():求总和AVG():求平均值MIN():求最小值MAX():求最大值COUNT():传回非NULL值的字段数目COUNT(*):传回符合查询条件的数目 163例：通过AVG()函数返回每年度学生的平均成绩 select year,average=avg(comp_score)from stud_score group by(year) order by(year) 164例：查询stud_score 表中成绩字段comp_

56、score中的总成绩、最高分、最低分、最高分和最低分的差额 SELECT sum=sum(comp_score),max=max(comp_score),min=min(comp_score),sub=max(comp_score)-min(comp_score)FROM stud_score 1654.2 带条件的检索 在检索过程中，经常需要对数据根据一定的条件进行过滤，成为带条件的检索WHEREHAVINGGROUP BY主要子句1664.2.1 WHERE 子句 它常与NOT/AND/OR一起使用例：查询计算机课成绩(comp_score)不大于数学成绩(math_score)大于化学成

57、绩(chem_score)，或者化学成绩大于数学成绩的学生 167USE My_DB1SELECT year,stud_id,comp_score FROM stud_scoreWHERE (comp_scorechem_score)ORchem_scoremath_score 括号换 一个位置呢？ 168USE My_DB1SELECT year,stud_id,comp_score FROM stud_score WHERE comp_scorechem_score OR chem_scoremath_score) 1694.2.2 Where子句中条件的指定 SQL_Server 支持的

58、运算符如下：1)逻辑运算符：AND/OR/NOT2)比较符：，!，,=,= in,not in,any,all判断为集合成员否 between and判断满足指定区间否 like 匹配模式 is not null 测试空值 170例：模式匹配举例，查询stud_info中的姓氏为陈的学生姓名USE My_DB1SELECT nameFROM stud_infoWHERE name LIKE 陈%171关于字符匹配：匹配任意长度的字符串 _ ：只匹配一个字符 _ean ：匹配对指定范围 (例如a-f) 或集合 (例如abcdef) 中的任何单个字符可以匹配不属于指定范围的任何单个字符 172包含

59、区间的两个端点 例：查询stud_score表中学年年度在1997（含1997）到1998（含1998）的学生信息USE My_DB1SELECT year, stud_id, comp_scoreFROM stud_score WHERE year BETWEEN 1997 AND 1998173例：查询表stud_info中出生日期在1979.1.1到1980.6.1之间的学生的学号，姓名，以及出生年月 USE My_DB1SELECT stud_id, name, birthdayFROM stud_infoWHERE birthday BETWEEN 1-1-1979 AND 6-1-

60、1980 174例：查询数据库My_DB2中表device_manage 中supply_id 字段值为null的设备信息，请读者注意对字段为空或非空时的判断时应该在WHERE子句中使用 IS NULL 或 IS NOT NULL判断 175Use My_DB2SELECT dev_id,dev_name,lab_id FROM device_manage WHERE supply_id is null 176例：查询数据库My_DB1中表stud_score中学生数学成绩为60,70或80的学生的姓名,学年年度和该学年的数学成绩 Use My_DB1SELECT year, stud_id,