SQL语言及其操作.ppt

暨南大学深圳旅游学院,第四章 SQL语言及其操作,暨南大学深圳旅游学院,内容,SQL语言概述 企业管理器 数据定义子语言及其操作 数据操纵子语言及其操作 T-SQL语言,暨南大学深圳旅游学院,学习要求,了解SQL语言的发展及SQL标准； 熟悉SQL Server2000的分析查询器的使用，能利用分析查询器进行数据定义，包括定义数据库、定义表、定义索引、定义视图；进行数据操作，包括数据插入、删除、修改、查询等；,暨南大学深圳旅游学院,学习要求,掌握SQL Server2000的企业管理器的使用，能利用企业管理器创建数据库、表、视图等； 了解T-SQL语言中的数据类型、编程、存储过程，熟悉游标的使用。,暨南大学深圳旅游学院,一、SQL语言概述,SQL语言及其标准 SQL语言的特点 SQL2的分级 标准SQL与实际数据库产品中的SQL,暨南大学深圳旅游学院,1. SQL语言及其标准,SQL，即结构化查询语言（Structured Query Language)。 1974年IBM圣约瑟实验室的Boyce和Chamberlin为关系数据库管理系统System-R设计的一种查询语言，当时称为SEQUEL语言（Structured English Query Language)，后简称为SQL；,暨南大学深圳旅游学院,1. SQL语言及其标准,1981年IBM推出关系数据库系统SQL/DS，得到广泛应用；著名的关系数据库管理系统陆续实现SQL语言； 1982年，ANSI着手制定SQL标准，1986年公布第一个SQL标准-SQL86；SQL86主要内容：模式定义、数据操作、嵌入式SQL等内容 ； 1987年，ISO通过SQL86标准； 1989年，ISO制定SQL89标准；SQL89标准在SQL86基础上增补完整性描述；,暨南大学深圳旅游学院,1. SQL语言及其标准,1990年，我国制定等同SQL89的国家标准； 1992年，ISO制定SQL92标准，即SQL2； 1999年，ANSI制定SQL3标准，在SQL2基础上扩充了面向对象功能，支持自定义数据类型、提供递归操作、临时视图、更新一般的授权结构、嵌套的检索结构、异步DML等。,暨南大学深圳旅游学院,2. SQL语言的特点,SQL语言是一种关系数据库语言，提供数据的定义、查询、更新和控制等功能。 SQL语言不是一个应用程序开发语言，只提供对数据库的操作能力，不能完成屏幕控制、菜单管理、报表生成等功能，可成为应用开发语言的一部分。 SQL语言不是一个DBMS，它属于DBMS语言处理程序。,暨南大学深圳旅游学院,2. SQL语言的特点,具有如下特点： 功能强大、能够完成各种数据库操作 ； 书写简单、使用方便； 可作为交互式语言独立使用、也可作为子语言嵌入宿主语言中使用； 有利于各种数据库之间交换数据、有利于程序的移植、有利于实现程序和数据间的独立性；有利于实施标准化。,暨南大学深圳旅游学院,3. SQL2的分级,SQL2相当庞大分为三个级别，实现了对远程数据库访问的支持。 初级SQL2：在SQL89中增加了某些功能，如SELECT中的AS语句为表达式命名。 中级SQL2：在初级SQL2基础上扩充数据类型、操作类型、有关完整性控制方面内容，是SQL2的最主要内容。 完全SQL2：在中级SQL2基础上放宽某些限制、增加BIT数据类型等。,暨南大学深圳旅游学院,4.标准SQL与数据库产品中的SQL,Oracle、Sybase、Informix、Ingres、DB2、SQL Server、Rdb等大型数据库管理系统都实现了SQL语言； Dbase、Foxpro、Acess等PC机数据库管理系统部分实现了SQL语言； 可以在HTML中嵌入SQL语句，通过WWW访问数据库； 在VC、VB、DELPHI也可嵌入SQL语句。 由于SQL2和SQL3过于庞大，迄今没有一个数据库管理系统完全支持这两个标准。,暨南大学深圳旅游学院,4.标准SQL与数据库产品中的SQL,实际中的数据库产品并不是完全支持SQL标准的，即SQL标准中有的功能，实际的产品中有可能没有；而实际数据库产品有的功能有可能不是SQL标准所规定的，这样会导致SQL命令语法上有细微的差别。因此在使用时要注意这些差别。若是要求移植性，应尽量使用SQL标准中包含有的功能。 SQLServer2000中使用的是Transact-SQL，简称为T-SQL。Oracle支持SQL *PLUS；IBM DB2支持SQL/DS； Sybase也是支持T-SQL。,暨南大学深圳旅游学院,二、SQL server 2000企业管理器,SQL server 2000简介 SQL server 2000的安装 SQL server 2000服务器组件 SQL server 2000主要的管理工具 SQL server 2000企业管理器,暨南大学深圳旅游学院,1. SQL server 2000简介,是新一代大型关系数据库系统，它在电子商务、数据仓库和数据库解决方案等应用中起着重要的核心作用。 它是一个基于C/S模式的关系数据库管理系统。 它分为企业版、标准版、个人版、开发版和企业评估版。它们的性能、功能、操作系统环境的要求是有差别的。但对用户来讲主要是前三个版本。,暨南大学深圳旅游学院,2. SQL server 2000的安装,个人版可有10个左右的用户，适用于单机和windows 9x用户，属于MB级的数据库；标准版可有100个左右用户，属于GB级的数据库；企业版可有1000个左右的用户，属于TB级的数据库。同学们安装个人版就可以了。 安装时需要说明的是： “服务帐户”，选择“对每个服务使用同一帐户。自动启动SQL server服务”；“服务设置”，选择“使用本地系统帐户”。 身份验证模式：若操作系统安装的是服务器版，则有两种选择：windows身份验证模式和混合模式；,暨南大学深圳旅游学院,续上,Windows验证模式：使用Windows NT,windows 2000,windows 2003上的登录帐户进行连接，SQL server利用操作系统的用户安全特性控制登录访问； 混合模式：使用windows身份验证和（或）SQL server身份验证与SQL server连接。若选择混合模式，则需设置sa用户密码并确认密码。,暨南大学深圳旅游学院,3. SQL server 2000服务器组件,SQL server 2000服务器组件是SQL server 2000系统的主要服务单元主要包括： a.SQL server 数据库引擎(MSSQL Server服务）； b.SQL server 代理程序(SQL Server Agent服务）；c.Microsoft搜索服务； d.分布式事务处理协调器(MS DTC服务）。,暨南大学深圳旅游学院,4. SQL server 2000主要的管理工具,企业管理器 查询分析器,暨南大学深圳旅游学院,5. SQL server 2000企业管理器,企业管理器的功能主要有： 注册服务器 配置本地服务器 配置远程服务器 设置登录安全性 对数据库、数据库对象管理操作,暨南大学深圳旅游学院,注册服务器,安装SQL server 2000后，必须先注册本地或远程服务器。才能使用SQL server企业管理器来管理这些服务器。 操作注意老师演示,暨南大学深圳旅游学院,配置本地服务器,编辑注册属性 SQL server的开启与停止 数据的导入与导出,暨南大学深圳旅游学院,对数据库、数据库对象管理操作,以三张表为例演示对数据库及数据库对象的操作。 学生表、课程表、成绩表,暨南大学深圳旅游学院,三、数据定义子语言及其操作,DDL语言，主要用于对关系模式及其相关对象的定义（包括创建、删除和修改），如数据库、表、视图等。 DDL可以完成数据结构和完整性约束的定义。 三个命令关键字：create（创建）、drop（删除）、alter（修改）。 对象关键字有：database、table、view、index、Trigger、procedure、rule、default、function。 引发、步骤、规则、 默认值、函数,暨南大学深圳旅游学院,三、数据定义子语言及其操作,SQL server 数据库相关的概念 定义数据库 定义表 定义视图 定义索引,暨南大学深圳旅游学院,1。SQL server 数据库相关的概念,1.1 数据库对象 1.2 对象名 1.3 数据库文件 1.4 数据库文件组 1.5 系统数据库 1.6 命令约定,暨南大学深圳旅游学院,1.1 数据库对象,暨南大学深圳旅游学院,1.2 对象名,用户可以给数据库对象以两种方式命名，即完全限定名和部分限定名。对象名区分大小写。 完全限定名。是对象的全名，包括4个部分：服务器名、数据库名、所有者名和对象名。其格式为：sever_name.database_name.owner_name.object_name。每个对象的完全限定名是唯一的。,暨南大学深圳旅游学院,1.2 对象名,部分限定名。除了对象名不能省以外，其他的限定名可根据实际情况省略掉。省略掉的部分使用默认值。 服务器默认为本地服务器，数据库默认为当前数据库，所有者默认为在数据库中与当前连接会话的登录标识相关联的数据库用户名，或者数据库所有者。若是中间部分的限定被省略掉了，但圆点符不能省。,暨南大学深圳旅游学院,1.3 数据库文件,数据库对象的保存是通过数据库文件来实现的。SQL server2000的数据库文件三类： 主数据文件。主数据文件简称为主文件，是数据库的关键文件。在该文件中包含了数据库的启动信息，并存储数据。每个数据库有且仅有一个主文件，其默认的扩展名为MDF（Main Data File）。 辅助数据文件。用于存储未包括在主文件内的其他数据。辅助文件的默认扩展名为NDF（Next Data File ）。根据具体情况，可以选用辅助数据文件，也可以不选用，甚至可选用多个。一般数据库较小时，不必选用辅助数据文件。,暨南大学深圳旅游学院,1.3 数据库文件,日志文件。日志文件用于保存恢复数据库所需的事务日志信息，其扩展名为LDF（Log Data File）。每个数据库至少有一个日志文件，也可以有多个。日志文件的存储与数据文件不同，它包含一系列记录，这些记录的存储不以页为存储单位。,暨南大学深圳旅游学院,1.4 数据库文件组,文件组是为了管理和分配数据的目的而将文件组织在一起。通常可以为一个磁盘驱动器创建一个文件组，然后将特定的表、索引等与该文件组相关联，那么对这些表的存储、查询和修改都在同一文件组中。 SQL server2000中的文件组有两个分类：主文件组和用户自定义文件组。,暨南大学深圳旅游学院,1.4 数据库文件组,主文件组。主文件组包含主数据文件和没有被指派给其他文件组的任何数据文件。 用户自定义文件组。用CREATE DATABASE和ALTER DATABASE命令创建（或修改）数据库时用FILEGROUP关键字指定。 每个数据库中都有一个文件组作为默认文件组运行。可以指定默认文件组，如果没有指定默认文件组，则主文件组是默认文件组。 一个文件只能属于一个文件组，只有数据文件才能作为文件组的成员，日志文件是不能作为文件组的成员。,暨南大学深圳旅游学院,1.5 系统数据库,SQL Server2000有4个系统数据库：master、model、msdb和tempdb。 master包含了SQL Server诸如登录号、系统配置、数据库位置及数据库错误信息等，用于控制用户数据库和SQL server的运行。 model数据库为新建的数据库提供模板。当用户创建一个新数据库时，使用model数据库复制一个初始化数据库及其元数据，并且会将model 数据库中任何用户定义的对象都复制到所创建的新数据库中。因此，可以向 model 数据库中添加任何对象，例如表、视图、存储过程、数据类型等，以将这些对象添加到所有新建数据库中。,暨南大学深圳旅游学院,1.5 系统数据库,msdb为SQL Server Agent调度信息和作业记录提供存储空间。 tempdb为临时表和临时存储过程提供存储空间，所有与系统连接的用户的临时表和临时存储过程都存储在该数据库中。,暨南大学深圳旅游学院,1.6 命令约定,方括弧项（即 ）表示是可选项，其可选的含义是根据命令的需要而加上或去掉。 花括弧项（即 ）表示为一组，即一对花括弧中的项为一组。 符号“|”表示或的关系，若一组中有若干个“|”，表示只能选取其中之一。 斜体字符为参数项，参数项是需要用户确定或定义的项。,暨南大学深圳旅游学院,2.定义数据库,2.1 创建数据库 2.2 修改数据库 2.3 删除数据库,暨南大学深圳旅游学院,2.1创建数据库,CREATE DATABASE db_name ON ,n LOG ON ,n := PRIMARY (NAME=logical_file_name, FILENAME=os_file_name ,SIZE=size ,MAXSIZE=max_size|UNLIMITED ,FILEGROWTH=growth_increment),暨南大学深圳旅游学院,创建数据库举例,Create database studata on primary (name=stufile1, filename=f:stufile1.mdf, size=10MB, maxsize=20MB, filegrowth=5%), (name=stufile2, filename=f:stufile2.ndf, size=10MB, maxsize=20MB, filegrowth=1MB),暨南大学深圳旅游学院,续前,Log on (name=stulog, filename=f:stulog.ldf, size=10MB, maxsize=20MB, filegrowth=10%),暨南大学深圳旅游学院,说明,若没有指定LOG ON，将自动创建一个日志文件，该文件使用系统生成的名称，其名称为：数据库文件名_log.ldf ，大小为数据库中所有数据文件总大小的25%。,暨南大学深圳旅游学院,2.2修改数据库,ALTER DATABASE db_name ADD FILE ,n |MODIFY FILE |REMOVE FILE logical_file_name |ADD LOG FILE ,n |MODIFY NAME=new_db_name := (NAME=logical_file_name ,FILENAME=os_file_name , NEWNAME =new_logical_name ,SIZE=size ,MAXSIZE=max_size|UNLIMITED ,FILEGROWTH=growth_increment),暨南大学深圳旅游学院,修改数据库举例,Alter database studata add file (name=stufile3, filename=f:stufile3.ndf, size=10MB, maxsize=500MB, filegrowth=1MB) Alter database studata modify file (name=stufile1, maxsize=300MB),暨南大学深圳旅游学院,修改数据库举例,alter database studata modify name=stud Alter database studata remove file stufile3,暨南大学深圳旅游学院,2.3删除数据库,DROP DATABASE db_name,n 不能删除当前正在使用的数据库，也无法删除系统数据库（msdb、model、master、tempdb)。 删除数据库将删除数据库所使用的数据库文件和磁盘文件。 因此不要轻易做删除.,暨南大学深圳旅游学院,3. 定义表,3.1 创建表 3.2 修改表 3.3 删除表,暨南大学深圳旅游学院,3.1创建表,CREATE TABLE database_name.owner.|owner.table_name ( | column_name AS expression ,n ) := NULL|NOT NULL DEFAULT |IDENTITY(initial,increment),暨南大学深圳旅游学院,3.1创建表（续）,:= CONSTRAINT constraint_name PRIMARY KEY |UNIQUE CLUSTERED|NONCLUSTERED |FOREIGN KEY REFERENCES ref_table_name ON DELETE CASCADE|NO ACTION(默认) ON UPDATE CASCADE|NO ACTION(默认) |CHECK (logical_expression) 说明：中的为一组。列级约束放在每一列的后面。对于主键默认为“CLUSTERED”，对于唯一约束默认为“NONCLUSTERED”。,暨南大学深圳旅游学院,3.1创建表（续）,:= CONSTRAINT constraint_name PRIMARY KEY |UNIQUE CLUSTERED|NONCLUSTERED (column_name) |FOREIGN KEY(ref_column) REFERENCES ref_table_name ON DELETE CASCADE|NO ACTION(默认) ON UPDATE CASCADE|NO ACTION(默认) |CHECK (logical_expression) 表约束必须放在的列定义后面。,暨南大学深圳旅游学院,创建表举例,Create table dbo.stuinfo (stu_id char(8) not null, stu_name varchar(10) not null, from_city varchar(40) not null, street varchar(20) not null, postcode char(6) not null, birthday datetime not null, sex char(2) not null default 男, deparment varchar(20) not null, end_date datetime not null, address as from_city+street+postcode, contraint stu_id_key primary key (stu_id),3.2修改表,ALTER TABLE table_name ALTER COLUMN column_name new_type |ADD |column_name AS expression ,n |WITH CHECK|WITH NOCHECKADD,n |DROP CONSTRAINT constraint_name|COLUMN column_name ,n |CHECK|NOCHECK CONSTRAINT ALL|constraint_name ,n |ENABLE|DISABLETRIGGER ALL|trigger_name ,n 增加列时：定义列列约束表约束 删除列时：约束名列名 激活约束，包括所有约束名 check 激活 检查触发器能否起作用 by ai-di,暨南大学深圳旅游学院,修改表（说明）,每次修改只能使用一个子句。 WITH CHECK|WITH NOCHECKADD ：用于增加新的表级约束。 “WITH CHECK”表示激活新增的约束，而“WITH NOCHECK”表示暂时禁止新增的约束起作用；如果没有指定，假定为 WITH CHECK，且只对FOREIGN KEY 或 CHECK 约束有效，不能对PRIMARY和UNIQUE约束用该子句。,暨南大学深圳旅游学院,修改表（续）,CHECK|NOCHECK CONSTRAINT ALL|constraint_name：用于激活或禁止表中原有的约束，或所有的约束。 当向已有数据的表中增加新列时，应注意新增列要么允许空，要么用default设定默认值。因为新增列的值系统会自动设为NULL。 在表中删除列时，在删除列之前，必须删除任何引用该列的约束、默认表达式、计算列表达式或索引。,暨南大学深圳旅游学院,续前,允许修改已有列的数据类型、大小和可空性。但要注意以下几点： 不能修改数据类型为text、ntext、image或timestamp的列； 不能修改计算的列，约束、默认值或索引中引用的列； 不能对已有空值的列设not null；,暨南大学深圳旅游学院,修改表举例,Alter table stuinfo add telephone char(13) Alter table stuinfo drop column address Alter table stuinfo add address as from_city+street+postcode Alter table stuinfo alter column from_city varchar(20),暨南大学深圳旅游学院,3.3删除表,DROP TABLE 当表删除生效后，表中的所有数据亦将不复存在，而直接或间接地建立在该表之上的视图，也将不能正常运行，并且与该表相关的所有授权将被自动撤消。,暨南大学深圳旅游学院,4. 定义视图,4.1 创建视图 4.2 修改视图 4.3 删除视图,暨南大学深圳旅游学院,4.1创建视图,CREATE VIEW owner.view_name (column_name list) AS SELECT command 如果没有指定视图的拥有者，系统默认其拥有者为当前的用户名； column_name list：为视图中的各列命名，这些列应分别与SQL命令中选出的列对应。,暨南大学深圳旅游学院,4.2修改视图,ALTER VIEW owner.view_name (column_name list) AS SELECT command,暨南大学深圳旅游学院,4.3删除视图,DROP VIEW view_name 视图的删除，不会影响基表的数据，但与该视图相关的所有授权将自动撤消。,暨南大学深圳旅游学院,5. 定义索引,5.1 索引概述 5.2 创建索引 5.3 删除索引,暨南大学深圳旅游学院,5.1 索引概述,索引是关于数据位置信息的关键字表，是数据库系统中的数据存取方法之一。利用索引，系统可较快地在磁盘上定位所需数据，而不需要在磁盘上从头到尾或从后向前，一个数据一个数据地匹配和查找，从而加快了数据查询的速度。 聚簇索引：磁盘上表的数据，与索引存储在相邻物理空间，并且表中行的物理顺序与索引顺序一致。即表中的元组是按索引顺序存放的，表中的数据一有变化，系统均须对表中数据重新排序。聚集索引每个表只能有一个。 非聚簇索引：一张表可建立多个非聚簇索引。,暨南大学深圳旅游学院,5.1 索引概述（续）,唯一索引：可用来限定聚簇索引和非聚簇索引。表示限定这两类索引所索引的列不能有相同的值。 索引的建立和删除，只能是DBA或表的拥有者。建立索引，一般可遵循下列原则： 为数据量大的表建立索引 被索引列的数据最好多而杂 一张表所建索引个数应适量 掌握建立索引的时机 优先建立基于主键的索引 若某列设为主键约束，自动被设为主键索引。,暨南大学深圳旅游学院,5.2创建索引,CREATE UNIQUECLUSTERED|NONCLUSTERED INDEX index_name ON table_name|view_name (column_name ASC|DESC,n),暨南大学深圳旅游学院,5.3删除索引,DROP INDEX table_name.index_name 索引删除后，有关索引的定义将从系统的数据字典中删除，并且包含在索引中的全部索引项将被清除。,暨南大学深圳旅游学院,四、数据操纵子语言及其操作,数据操纵子语言可以完成“插入”数据、“删除”数据、“修改”数据和“查询”数据。 数据插入 数据修改 数据删除 数据查询,暨南大学深圳旅游学院,1. 数据插入,插入一行 INSERT INTO () VALUES () “数据值表”中的数据类型与顺序应与“列名表”的数据类型和顺序一致。 Create table courses (course_id char(3),name varchar(30),level smallint) Insert courses values (1,数据库,4),如何插入一列呢？插入列是归于数据插入还是修改表呢？,暨南大学深圳旅游学院,1. 数据插入,插入多行 INSERT INTO () SELECT 语句 当想将两个具有相同结构的表合并为一个表时，可用此方法。 将ZG1表中的记录追加在ZG表的后面. Insert zg select * from zg1,暨南大学深圳旅游学院,2.数据修改,UPDATE SET = ,n FROM WHERE 每个UPDATE语句可以修改一行或多行数据，但每次仅能对一个表进行操作。 若中包含有其它表中的数据，则需要用FROM 子句。 一条命令可以同时修改多列的值。 若加上WHERE 子句，则可以一次修改满足条件的所有行。,暨南大学深圳旅游学院,数据修改举例,Use sbgl Update sb set price=price*1.5 where name=车床 当一列的数据是来自另一个表的列时的举例。 Update zg set wage=gz.jbgz+gz.glgz+gz.bt-gz.fdf from gz where gz.No=zg.No 修改多列举例，参见P171例4-11,暨南大学深圳旅游学院,3. 数据删除,DELETE FROM FROM WHERE FROM和WHERE子句的说明同UPDATE。,暨南大学深圳旅游学院,4. 数据查询,SELECT ALL|DISTINCT TOPPERCENT . AS ,|* INTO FROM INNER|LEFT|RIGHT|FULL JOIN ON WHERE GROUP BY HAVING ORDER BY ASC|DESC, COMPUTEAVG|COUNT|MAX|MIN|SUM(表达式),BY 表达式, UNION ALL ,暨南大学深圳旅游学院,SELECT子句,ALL|DISTINCT：缺省为ALL，表示选出的记录包括重复记录；DISTINCT，表示选出的记录中不包括重复记录。 TOP子句：指定输出记录为前行，或为百分比PERCENT。,暨南大学深圳旅游学院,SELECT表达式,. AS ：，可以是字段名，也可以包含用户自定义函数和聚集函数； .是字段所在的表名，若打开的表中有同名字段，则必须用标示不同表的同名字段； 用于指定输出时使用的列标题，可以不同于字段名。 若要输出的是表的全部字段，可用“*” 代替 。,暨南大学深圳旅游学院,SELECT表达式中可用的聚集函数,暨南大学深圳旅游学院,说明,上述各函数中，DISTINCT表示不计重复行和NULL行，ALL则计重复行，但不计NULL行，默认为ALL。 MIN、MAX、COUNT三个函数适合于所有数据类型的表达式，SUM和AVG只用于数值型表达式,暨南大学深圳旅游学院,INTO子句,INTO子句用于创建一张新表，并将满足查询条件的结果数据插入到新表中。 若想利用它来创建一张表结构，可使WHERE字句中的恒为假。 INTO子句必须在FROM子句前面. SELECTINTO不能与COMPUTE一起使用。,暨南大学深圳旅游学院,FROM子句,FROM子句：指定待查询的表。可以从多个表中查询，但这多个表之间一定要有联接条件。其使用方式有两种： FROM后接多个表名，用WHERE子句引出联接条件； FROM后只有一个表，用FROM后面的JOIN-ON子句引出联接的表和条件。多个JOIN-ON子句之间不要加“,”。 联接类型分四种：内联接（缺省）、左联接、右联接和完全联接。 SELECTFROM WHERE为基本形式。,暨南大学深圳旅游学院,联接条件,联接条件由WHERE子句或ON子句引出，具有如下形式： fieldname1和fieldname2分别处在不同的两个表中。 比较运算符有：=、)、=(!(!=)。,暨南大学深圳旅游学院,筛选条件,筛选条件由WHERE子句引出，具有如下形式: NOT BETWEEN AND NOT IN () NOT LIKE expC，代表字符表达式，允许使用通配符。 “_”代表0个或一个；“%”代表0个或一串字符；“ ”取其中任意单个字符字符；“”表示非；“$”表示转义字符。恰当地使用这些匹配符，可完成一定的模糊查询功能。,暨南大学深圳旅游学院,筛选条件, ALL() ANY|SOME () NOT EXISTS () NOT IN () IS NOT NULL 若有多个子筛选条件，根据需要可用AND或OR进行连接。,暨南大学深圳旅游学院,基本查询举例（基于zggl）,查出所有的职称名； 查看所有女职工的情况； 查询工资在600元以下的职工号、姓名、工资和职称； 查询所有未婚的女职工； 查询所有未婚职工和女职工； 查询工资在500元和600元之间的职工号、姓名、工资和职称； 查询所有职称为助工、技术员、技师的职工情况； 查所有姓“黄”的职工。,暨南大学深圳旅游学院,思考题（基于sbgl数据库）,查询价格小于十万元的设备的启用日期； 查询车间使用的设备的价格不小于十万元的设备的部门名； 查询1990年启用的设备的名称和部门； 查询大修过的设备的编号和名称，查询结果输出到表result中； 查询有增值的设备的设备名和部门名； 试算出1992年前启用的主要设备的台数； 查询最早启用和最晚启用的设备。,暨南大学深圳旅游学院,GROUP BY 子句,GROUP BY子句：对记录按分组，相同值的为一组，一组只输出一条记录。必须是字段名。对于使用了GROUP子句的查询，其输出列或出现在GROUP BY子句的列表中，或出现在聚集函数中。 HAVING子句：与GROUP子句配套使用，设置查询结果中的组必须满足的条件。 举例: 按部门统计男职工的人数； 找出职工工资都在600元以上的部门及人数,暨南大学深圳旅游学院,ORDER 子句,ORDER BY子句：设定查询结果中的记录按排序，缺省为升序。也可以是表示查询结果中列位置的数字。 应在SELECT子句中。 例如: 基于student数据库，查询每位同学的平均成绩，并按从高到低的顺序排列。,暨南大学深圳旅游学院,COMPUTE子句,用于对查询的结果数据集合最后进行汇总，如带有BY，则可进行分类汇总，这时应与ORDER BY子句联用。 COMPUTE 后表达式的列必须是SELECT子句中的列； COMPUTE BY 应与ORDER BY联用，且COMPUTE BY后的表达式应与ORDER BY 后的表达式内容与顺序一致。 举例： 查询所有车床的基本情况及车床的总成本； 按部门查询各种设备的基本情况及设备成本；,暨南大学深圳旅游学院,嵌套子查询与相关子查询,一个查询语句的查询结果作为另一个查询语句的条件，这时前者为子查询或内查询，又称嵌套子查询，而后者为主查询，或称为外查询。其结构参见书177。 子查询之中允许嵌套另一个子查询，但最多嵌套255层，并且总是从嵌套层次最深的一层开始执行，然后再执行它的直接上一层，直至完成整个查询。 子查询返回为单值时，比较运算符可为算术比较符，返回多列或多行值，则用IN、ANY、ALL、EXISTS等运算符。,暨南大学深圳旅游学院,UNION查询,将两个或多个查询的结果合并成一个结果返回。 UNION ALL 用UNION合并的结果集应用相同的结构，即列数相同、对应列数据类型兼容。 最后结果的列名取自第一个SELECT语句返回的列名。 默认情况下，最后结果会去掉重复行，但如有ALL选项，则保留重复行。 一般，UNION个数不限，且按从左至右顺序执行。 只有最后一个SELECT语句能用ORDER BY和COMPUTE子句。,暨南大学深圳旅游学院,综合实例,查找成绩在90分及以上的同学姓名和课程号及成绩。 select sname as 姓名, c# as 课程号,grade as 成绩 from stud,grades where stud.s#=grades.s# and grade=90 select sname as 姓名, c# as 课程号,grade as 成绩 from stud join grades on stud.s#=grades.s# where grade=90,暨南大学深圳旅游学院,综合实例,查找成绩在90分及以上的同学姓名和课程名及成绩。 select sname as 姓名,cname as 课程名,grade as 成绩 from grades,stud,course where stud.s#=grades.s# and grades.c#=course.c# and grade=90 select sname as 姓名,cname as 课程名,grade as 成绩 from grades join stud on stud.s#=grades.s# join course on course.c#=grades.c# where grade=90,暨南大学深圳旅游学院,综合实例,查出所有职称及每种职称对应的人数； 查出工程师的平均工资； 求职称类型数； 查出每门课的平均成绩； 查出每门课的最高成绩； 查出“数据库”在90分及以上的人数。 查出“数据库”在90分及以上的人名单。,暨南大学深圳旅游学院,五、T-SQL语言,T-SQL，即事务SQL(Transact-SQL)。是MS SQL Server 和Sybase对标准SQL的扩展版本。 T-SQL数据类型 自定义数据类型 T-SQL函数 T-SQL编程 T-SQL游标 T-SQL存储过程,暨南大学深圳旅游学院,1. T-SQL数据类型,暨南大学深圳旅游学院,1. T-SQL数据类型,暨南大学深圳旅游学院,1. T-SQL数据类型,Nchar(n)、 Nvarchar(n)、ntext(n)用于存放UNICODE字符集。Unicode是一种交互和显示的通用字符编码标准，它覆盖了美国、欧洲、中东、非洲、印度、亚洲和太平洋的语言，以及古文及专业符号。,暨南大学深圳旅游学院,1. T-SQL数据类型,暨南大学深圳旅游学院,2. 用户自定义数据类型,SQL Server 允许用户利用系统数据类型来定义用户自己的数据类型。 利用系统提供的存储过程sp_addtype、sp_droptype和sp_help可分别创建、删除和查看用户定义的类型。 定义数据类型格式： Sp_addtype 类型名,系统类型名,属性 其中，各参数如果不含空格、“()”、“.”时，可以不加单引号。 属性有三种取值：NULL、NOT NULL、IDENTITY。IDENTITY，表示指定列为标示列，用户不能对该列进行增、删、改。,暨南大学深圳旅游学院,2. 用户自定义数据类型,删除数据类型定义格式： Sp_droptype 类型名 查看数据类型格式： Sp_help 类型名 举例： Exec sp_addtype test,char(2),not null 用户自定义数据类型是一种数据库对象，一旦定义，即可像系统数据类型一样地使用。,暨南大学深圳旅游学院,3. T-SQL函数,3.1 字符函数 3.2 日期时间函数 3.3 数学函数 3.4 其他函数,暨南大学深圳旅游学院,3.1 字符函数,暨南大学深圳旅游学院,3.2 日期时间函数,暨南大学深圳旅游学院,3.3 数学函数,暨南大学深圳旅游学院,3.4 其他函数,类型转换函数 格式：convert(类型符,表达式,style) “类型符”指明变换后的类型； “表达式”指定将要被转换的数据，但不能是常量表达式； 当由日期型向字符型转换时可用“style”，指定日期时间的格式。其取值及样式见下表。 SQL server中有许多数据类型之间是隐式转换的。,暨南大学深圳旅游学院,Style取值,暨南大学深圳旅游学院,3.4 其他函数,聚集函数 该类函数主要用于返回统计值。 Isnull函数 格式：isnull(列名,值) 功能：当列值为空时，用指定的数值代替之。,暨南大学深圳旅游学院,3.5 数据类型隐式转换表,暨南大学深圳旅游学院,4. T-SQL编程,4.1 SQL server中的批 4.2 注释方式 4.3 变量 4.4 流程控制语句 4.5 信息显示,暨南大学深圳旅游学院,4.1 SQL server中的批处理,批处理是包含一条或多条 T-SQL 语句的组，从应用程序一次性地发送到SQL Server执行。SQL Server 将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条。因此，批处理的操作可分为两步：编译和执行。在编译时，若发现批中的T-SQL语句有语法错误，则任何一条语句都不会被执行；若编译通过，而在运行时某一语句有逻辑错误，则在它前面语句被执行，后面的语句不会被执行。,暨南大学深圳旅游学院,批处理的概念,利用SQL server的查询分析器可以实现批处理，每一个批均以GO结束。在查询分析器中，可以一次提交多个批。,暨南大学深圳旅游学院,使用批处理应遵循的规则,大多数，但不是所有的，SQL语句可放在一个批中。 批中所有未注明所属对象，均基于当前数据库； “USE 库名”命令必须自成一个批；类似的语句还有“CREATE RULE/DEFAULT/TRIGGER/VIEW”、“DELCARE CURSOR”等； 不能在一个批中删除一个对象，又创建同名的对象；,暨南大学深圳旅游学院,使用批处理应遵循的规则(续),不能在同一批中修改完一个表的结构后，马上引用刚修改的新列； 对存储过程的执行，如放在批文件中，且存储过程不是开头的第一条语句，那么应在其前加“EXEC”。,暨南大学深圳旅游学院,4.2 注释方式,/*/ 用于多行注释 - 用于单行注释,暨南大学深圳旅游学院,4.3 变量,SQL server中有两类变量：局部变量和全局变量。 局部变量 由用户定义和使用。 全局变量 用“变量名”表示。由系统定义，用户只能引用，不能修改、定义。,暨南大学深圳旅游学院,4.3.1局部变量,定义：declare 变量名 数据类型, 变量名 数据类型, 变量一旦定义，系统自动赋NULL值。 赋值：set 变量名=值 或 select 变量名=值, 变量名=值, 间接赋值，可以将查询结果的值赋给变量，若查询结果是多个值，则赋给变量的是最后一个。如：select a=level1 from stud Set一次只能给一个变量赋值，而select一次可给多个变量赋值。,暨南大学深圳旅游学院,4.3.1局部变量(续),举例：declare myqty int,msg varchar(40) set msg=电子商务 set myqty=234 查看：select 变量名,变量名, 到此为止，SELECT有四种用法： 1、查看表或视图的数据，即select查询语句； 2、给变量赋值； 3、查看变量的值； 4、执行函数，显示函数的结果。,暨南大学深圳旅游学院,4.3.2 全局变量,常用的全局变量有： error:返回最后一个语句产生的错误代码。 rowcount:返回最后一个语句执行后受影响的行数，几乎所有语句都可能改变该变量的值。 version: 返回SQL server的版本号。 系统提供的全局变量有许多，同学们可利用查询分析器的联机帮助查看。,暨南大学深圳旅游学院,4.4 流程控制语句,Beginend 界定由多条语句组成的语句块。 Ifelse if CASEEND Whilebreak/continue,暨南大学深圳旅游学院,4.4.1 BEGINEND,例如： declare avg_price money select avg_price=avg(price) from titles if avg_price=20 begin update titles set price=price*1.3 print 价格提高30%！ end else print 平均价格未知！,暨南大学深圳旅游学院,4.4.2 IFELSE,例如： if (select avg(price) from titles)=20 update titles set price=price*1.3 else print 平均价格未知！,暨南大学深圳旅游学院,4.4.3 CASEEND,（1）查询职工工资，按职称类别显示，工程师比其工资高50%，技师高50%，技术员高20%，助工高30%。（用简单CASE格式） 注意：CASEEND不能像IFELSE那样单独用，只能嵌在某一个SQL命令中作为一个表达式的构成要素用。,暨南大学深圳旅游学院,4.4.3 CASEEND,select zgh,xm,zc,gz= case zc when 工程师 then gz*1.5 when 技术员 then gz*1.2 when 技师 then gz*1.5 when 助工 then gz*1.3 end from zg order by 3,暨南大学深圳旅游学院,4.4.3 CASEEND,（2）查询成绩表，若成绩大于等于90分，则等级显示为“优秀