数据库SQL语句宝典.ppt

1、SQL 语句数据库设计,本章重点内容： 1：使用SQL语句创建和删除数据库、表以及约束。 2：学会使用SQL Server文档,回顾,第一学期T-SQL语句的数据操作语法 1.添加数据 语法： INSERT INTO 表名（列1，列2， ）VALUES (值1，值2 ) 例如： INSERT INTO Student (StuName,StuNo,BornDate) VALUES (张三,1000,1990-2-9) 2.修改数据 语法： UPDATE 表名 SET 列1 = 值1，列2 = 值2， WHERE (条件) 例如： UPDATE Student SET BornDate = 19

2、91-2-28 WHERE StudentName = 张三,回顾,第一学期T-SQL语句的数据操作语法 3.查询数据 语法： SELECT 列1,列2， FROM 表名 WHERE (条件) ORDER BY 列名 例如： SELECT StuName,StuNo FROM Student WHERE BornDate = 1990-1-1 ORDER BY StuNo 4.删除数据 语法： DELETE FROM 表名 WHERE (条件) 例如： DELETE FROM Student WHERE BornDate 1990-1-1,知识点一：使用SQL语句创建和删除数据库,回顾 数据库

3、文件由以下三部分组成： 1：主数据文件：.mdf。 2：次要数据文件：.ndf。 3：日志文件：.ldf。 每个数据库至少要包含两个文件：一个数据文件和一个日志文件。数据文件中包含了数据库的数据和对象，如表、视图和索引等，日志文件中包含有用于恢复数据库所需的信息。创建数据库时，次要数据文件可选。一个数据库可以有多个数据库文件和多个日志文件。,知识点一：使用SQL语句创建和删除数据库,创建数据库 语法： CREATE DATABASE 数据库名 ON PRIMARY ( , n ) LOG ON ( , n ),知识点一：使用SQL语句创建和删除数据库,示例 -sysdatabases存储在Ma

4、ster数据库中 -创建数据库 CREATE DATABASE MySchool ON ( -主数据文件 NAME=MySchool, FILENAME=D:appleMySchool.mdf, SIZE=5MB, MAXSIZE=100MB, FILEGROWTH=10% ) LOG ON ( -日志文件 NAME=MySchool_log, FILENAME=D:appleMySchool_log.ldf, SIZE=3MB, MAXSIZE=20MB, FILEGROWTH=10% ),知识点一：使用SQL语句创建和删除数据库,删除数据库 如果SQL Server中已存在MySchool

5、数据库，再次运行示例中的创建数据库的语句，就会发现系统提示错误：该数据库已存在，创建失败。SQL Server 将数据库的清单存放在master系统数据库的sysdatabases表中，只需要借组SELECT语句查看sysdatabases表中是否存在数据库MySchool的记录就可以了。,知识点一：使用SQL语句创建和删除数据库,删除数据库 所以，示例1完整的T-SQL语句即如下所示。 USE master GO IF EXISTS (SELECT * FROM sysdatabases WHERE name = MySchool) DROP DATABASE MySchool CREATE

6、 DATABASE MySchool ON ( ) LOG ON ( ) GO EXISTS (查询语句)检测某个查询是否存在。如果查询语句返回的记录结果不为空，则表示存在；否则表示不存在。,知识点二：使用SQL语句创建和删除表,创建表 语法： CREATE TABLE 表名 （ 列1 数据类型 列的特征, 列2 数据类型 列的特征, ） 其中，“列的特征”包括该列是否为空（NULL），是否是标识列（自动编号），是否有默认值以及是否为主键等。 考虑各种开发语言的兼容性，表中各列名称推荐使用英文缩写。,知识点二：使用SQL语句创建和删除表,示例 CREATE TABLE stuMarks ( m

7、arksId INT PRIMARY KEY IDENTITY(1,1) NOT NULL, mathScore INT NOT NULL, chineseScore INT NOT NULL, sId INT NOT NULL ) 同创建数据库一样，如果当前数据库中已存在stuMarks表，再次创建时系统将提示错误。我们需要预先检测当前数据库中是否存在该表，如果存在，则先删除，然后，再创建。 删除表的语法如下： DROP TABLE 表名 例如： DROP TABLE stuMarks,知识点三：使用SQL语句创建和删除约束,添加约束 在创建表时，我们可以在列后添加各种约束，但一般不这样混合

8、使用，推荐将添加约束和创建表的语句分开编写。 添加约束的语法如下。 语法： ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 具体的约束说明,知识点三：使用SQL语句创建和删除约束,示例 -创建主键约束 ALTER TABLE student ADD CONSTRAINT PK_stuID PRIMARY KEY(stuID) -添加唯一约束。特点：不允许相同，但允许有一个空值 ALTER TABLE student ADD CONSTRAINT UQ_stuCardNo UNIQUE (stuCardNo) -添加默认约束 ALTER TABLE student

9、ADD CONSTRAINT DF_stuAddress DEFAULT(北京海淀中关村) FOR stuAddress -添加检查约束 ALTER TABLE student ADD CONSTRAINT CK_stuSex CHECK(stuSex=男 OR stuSex=女) -添加主外键关系FOREIGN KEY (stuID)中的stuID是子表中的stuID -添加主外键的步骤： -1、确定谁是主表、子表 -2、在子表当中添加一个字段，用来跟主表中的主键建立关系 ALTER TABLE stuMarks ADD CONSTRAINT FK_Id FOREIGN KEY (sId)

10、REFERENCES student(stuID),知识点三：使用SQL语句创建和删除约束,删除约束 如果错误地添加了约束，则可以删除约束。 删除约束的语法如下： 语法： ALTER TABLE 表名 DROP CONSTRAINT 约束名 例如，删除Student表中地址列默认约束的语句如下。 ALTER TABLE Student DROP CONSTRAINT DF_stuAddress,知识点二：使用SQL Server 文档,如何查看SQL Server F1,第二章总结,1：数据库的物理实现一般步骤如下。 （1）创建数据库。 （2）创建表。 （3）添加各种约束。 （4）创建数据库的

11、登录账户并授权。 2：创建数据库或表时一般需要预先检测是否存在该对象，SQL Server中的哪些数据库可以从master系统数据库的sysdatabases表中查询，而一个数据库中的哪些表可以从该数据库的 系统表sysobjects中查询。 3：使用CREATE DATABASE语句可以创建数据库，使用DROP DATABASE语句可以删除数据库。 4：使用CREATE TABLE语句可以创建数据库的表结构，使用DROP TABLE语句可以删除表结构。 5：使用ALTER TABLE 语句可以创建以下约束。 （1）主键约束（Primary Key Constraint）。 （2）唯一约束（U

12、nique Constraint）。 （3）检查约束（Check Constraint）。 （4）默认约束（Default Constraint）。 （5）外键约束（Foreign Key Constraint）。,第三章目标,本章重点内容： 1：T-SQL中变量的声明与赋值 2：PRINT、SELECT输出语句 3：逻辑控制语句和批处理指令,知识点一：使用变量,T-SQL中的变量分为局部变量和全局变量。局部变量的使用也是先声明，再赋值。而全局变量由系统定义和维护，我们可以直接使用，但一般不自定义全局变量。 一：局部变量 T-SQL中，局部变量的名称必须以标记作为前缀。语法如下： DECLAR

13、E 局部变量名称 数据类型 例如： DECLARE name VARCHAR(8) DECLARE number INT,知识点一：使用变量,T-SQL语言中，为局部变量赋值语句有SET语句和SELECT语句。其中： 1：SET赋值语句一般用于赋给变量指定的数据常量 2：SELECT赋值语句一般用于从表中查询数据，然后再赋给变量。 SELECT语句需要确保筛选的记录不多于一条，如果查询的记录 多于一条，将把最后一条记录的值赋给变量。 SET语句与SELECT的区别 1：SELECT支持同时对多个变量赋值，而SET语句不支持。 2：表达式返回多个值时，SET将出错，而SELECT将返回的最后一个

14、值赋给变量。 3：表达式未返回值时，SET语句变量被赋NULL值，SELECT语句变量保持原值。,知识点一：使用变量,二：全局变量 SQL Server中的所有全局变量都使用两个标志作为前缀。常用的全局变量如下所示： 1：ERROR 最后一个T-SQL错误的错误号 2：IDENTITY 最后一次插入的标识值 3：LANGUAGE 当前使用的语言的名称 4：MAX_CONNECTIONS 可以创建的同时连接的最大数目 5：ROWCOUNT 受上一个SQL语句影响的行数 6：SERVERNAME 本地服务器的名称 7：SERVICENAME 该计算机上的SQL服务的名称 8：TIMETICKS 当

15、前计算机上每刻度的微妙数 9：TRANSCOUNT 当前连接打开的事务数 10：VERSION SQL Server的版本信息,知识点二：输出语句,T-SQL中支持输出语句，用于输出显示处理的数据结果。常用的输出语句有两种，它们的语法分别如下： 语法： 1：PRINT 局部变量或字符串 2：SELECT 局部变量 AS 自定义列名 与C#语言类似，T-SQL语言中，数据类型的转换也分为隐式转换和显式转换。在T-SQL语言中除了我们在第一学期中学习的CONVERT()函数能够实现数据类型显式转换功能之外，T-SQL还提供了CAST()函数。 语法： CAST(表达式 AS 数据类型) CONVE

16、RT (数据类型(长度),长度,样式) CAST()函数用于将某种数据类型的表达式显式转换为另一种数据类型。CONVERT()函数的功能是将某种数据类型的表达式显式转换为另一种数据类型，第三个参数只限于对日期格式的转换。,知识点三：逻辑控制语句,在T-SQL语言中，常用的逻辑控制语句有以下几种。 1：顺序机构控制语句：BEGIN-END语句 2：分支结构控制语句：IF-ELSE语句和CASE-END语句 3：循环结构控制语句：WHILE语句 一：BEGIN-END语句 语法： BEGIN 语句或语句块 END BEGIN-END语句的作用类似于C#语言的“”符号，它经常在分支结构语句和循环语句

17、中出现，表示语句块的开始和结束。在一个语句块中可以包含另一个语句块,知识点三：逻辑控制语句,二：IF-ELSE条件语句 IF-ELSE语句属于分支结构，它与C#编程语言的IF语句类似，也是根据条件是否成立来确定程序的执行方向。 语法： IF (条件) 语句或语句块1 ELSE 语句或语句块2 当IF语句中的条件表达式为true时，执行语句或语句块1的代码；否则执行语句或语句块2.同C#语言类似，ELSE为可选。,知识点三：逻辑控制语句,三：WHILE循环语句 WHILE循环语句可以根据某些条件重复执行一条SQL语句或一个语句块。通过使用WHILE关键字，可以确保只要指定的条件为true，就会重

18、复执行语句，直至指定条件为false为止。 语法： WHILE (条件) BEGIN 语句或语句快 BREAK | CONTINUE END,知识点三：逻辑控制语句,四：CASE多分支语句 CASE-END语句计算一组条件表达式，并返回其中一个符合条件的结果。 语法： CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ELSE 其他结果 END CASE语句表示如果“条件1”成立，则执行“结果1”，其余类推。如果WHEN的条件都不匹配，则执行ELSE后面的结果。ELSE可以省略。如果省略ELSE并且WHEN的条件表达式的结果都不为TRUE，则CASE-END语句

19、返回NULL。,知识点三：批处理,GO指令 GO就是批处理的标志。它是一条或多条SQL语句的集合，SQL Server 将批处理指令编译成一个可执行单元，此单元称为执行计划。 批处理的主要好处是能够简化数据库的管理。,第三章总结,1：变量的使用是先声明，再赋值。局部变量前必须有“” 作为前缀，全局变量必须有两个“”作为前缀。 2：变量的赋值有两种方式：SET语句和SELECT语句。 3：输出结果也有两种方式：PRINT语句和SELECT语句。 4：数据类型转换的两个函数：CAST()和CONVERT()。 5：控制流语句提供了条件操作所需的顺序和逻辑。 6：语句快使用BEGIN-END。 7：

20、批处理可以提高语句执行的效率，批处理结束的标志是“GO”。,第四章目标,本章重点内容： 介绍子查询的概念，讨论简单子查询，研究 EXISTS 和 IF 子查询的用法。,知识点一：子查询,语法： SELECT FROM 表1 WHERE 列1 (子查询) 子查询语句必须放置在一对圆括号内。在列1后面除了“”运算符外，还可以使用其他运算符号。习惯上，外面的查询称为父查询，圆括号中嵌入的查询称为子查询。SQL Server执行时，先执行子查询部分，求出子查询部分的值，然后再执行整个父查询，返回最后的结果。因为子查询作为WHERE条件的一部分，所以还可以和UPDATE、INSERT、DELETE一起使

21、用。 示例： SELECT StuNo,StuName,StuSex,BornDate,Address FROM Student WHERE BornDate (SELECT BornDate FROM Student WHERE StuName = 张三),知识点一：IN和NOT IN子查询,1：IN 子查询 使用=、等比较运算符时，要求子查询只能返回一条或空的记录。在SQL Server中，当子查询跟随在 = 、!= 、和=之后，不允许子查询返回多条记录。所以，只需将“=”改为“IN”就可以了。 示例： SELECT StuName FROM Student WHERE StuNo IN

22、( SELECT StuNo FROM Result WHERE SubjectNo = ( SELECT SubjectNo FROM Subject WHERE SubjectName = Java ) AND StuScore = 60 ),知识点一：IN和NOT IN子查询,2：NOT IN 子查询 如何查询得到没有参加Java课程考试的在读学生名单？在上示例代码的IN关键字之前加上否定的NOT就能获得未参加考试的学生名单了。 示例： SELECT StuName FROM Student WHERE StuNo NOT IN ( SELECT StuNo FROM Result WH

23、ERE SubjectNo = ( SELECT SubjectNo FROM Subject WHERE SubjectName = Java ) AND StuScore = 60 ),知识点二：EXISTS和NOT EXISTS子查询,1：EXISTS子查询 语法： IF EXISTS (子查询) 语句 如果子查询的结果非空，则EXISTS(子查询)将返回真(true)，否则返回假(false)。 示例： IF EXISTS (SELECT * FROM sysDatabases WHERE name = MySchool) DROP DATABASE MySchool 2: NOT E

24、XISTS子查询 EXISTS 和 IN 一样，同样允许添加NOT取反，NOT EXISTS表示不存在。,知识点三：子查询注意事项,1：子查询语句可以嵌套在SQL语句中任何表达式出现的位置。 2：嵌套在父查询SELECT语句的子查询可包括以下几类。 （1）：SELECT 字句。 （2）：FROM 字句。 （3）：WHERE 字句。 （4）：GROUP BY 字句。 （5）：HAVING 字句。 3：在子查询的SELECT子句不能出现TEXT、NTEXT或IMAGE数据类型的列。 4：只出现在子查询中而没有出现在父查询中的表不能包含在输出列中。,第四章总结,1：总结我们曾学过的查询，合并多个表中

25、的数据有以下三种方法。 （1）联合 （UNION）合并多个数据集中的行。 （2）子查询 将一个查询嵌套在另一个查询中。 （3）联接 合并多个数据表中的列。 2：比较运算符后面的子查询只能返回单个数值。 3：IN子查询后面可跟随返回多条记录的子查询，用于检测某列的值是否存在于某个范围中。 4：通过在子查询中使用EXISTS子句，可以对子查询中的行是否存在进行检查。 5：子查询可以出现在表达式出现的任何位置。,第六章：事务、视图、索引,一：事务 事务是一种机制、一个操作序列，它包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不

26、执行。 （1）事务的四个属性 1、原子性 事务是一个完整的操作，事务的个元素是不可分的（原子的）。 2、一致性 当事务完成时，数据必须处于一致状态。 3、隔离性 对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。 4、持久性 事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。,第六章：事务、视图、索引,（2）如何执行事务 语法： 1、开始事务 BEGIN TRANSACTION 这个语句显式地标记一个事务的起始点。 2、提交事务 COMMIT TRANSACTION 这个语句标志一个事务成功结束。 3、回滚（撤销）事务 ROL

27、LBACK TRANSACTION,第六章：事务、视图、索引,（3）事务应用示例 BEGIN TRANSACTION DECLARE errorSum INT SET errorSum = 0 UPDATE Bank SET currentMoney = currentMoney 1000 WHERE customerName = 张三 SET errorSum = errorSum + ERROR UPDATE Bank SET currentMoney = currentMoney + 1000 WHERE customerName = 李四 SET errorSum = errorSum

28、 + ERROR IF (errorSum 0) BEGIN PRINT 交易失败，回滚事务。 ROLLBACK TRANSACTION END ELSE BEGIN PRINT 交易成功，提交事务。 COMMIT TRANSACTION END,第六章：事务、视图、索引,二：视图 视图是另一种查看数据库中一个或多个表中的数据的方法。视图是一种虚拟表，通常是作为来自一个或多表的行或列的子集创建的。它限制数据检索更容易。开发人员有时需要隐藏某些行或列中的信息，通过使用视图，用户可以灵活地访问他们需要的数据，同时保证同一个表或其他表中的其他数据的安全性。 （1） 语法：创建视图 CREATE VI

29、EW view_name AS 使用T-SQL语句删除视图 DROP VIEW view_name 通常，我们可以通过EXISTS关键字检测sysobjects表中是否存在特定的视图。如果存在，则可以利用DROP VIEW 语句删除。代码如下： IF EXISTS (SELECT * FROM sysobjects WHERE name = view_name) DROP VIEW view_name,第六章：事务、视图、索引,（2） 示例 USE MySchool GO IF EXISTS (SELECT * FROM sysobjects WHERE name = vw_student_r

30、esult) DROP VIEW vw_student_result GO -创建视图 CREATE VIEW vw_student_result AS SELECT stuName,stuAge,Address,stuSex,stuEmail FROM Student WHERE stuName = 张三 GO -查看视图结果 SELECT * FROM vw_student_result,第六章：事务、视图、索引,三：索引 索引是SQL Server编排数据的内部方法，它为SQL Server提供一种方法来编排查询数据的路由。索引页是数据库中存储索引的数据页。索引页存放检索数据行的关键字页

31、以及该数据行的地址指针。 （1）索引分类 1、唯一索引 不允许两行具有相同的索引值 2、主键索引 在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。 3、聚集索引 在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。 4、非聚集索引 非聚集索引建立在索引页上，在查询数据时可以从索引中找到记录存放的位置。 5、复合索引 在创建索引时，并不是只能对其中一列创建索引，与创建主键一样，可以将多个列组合作为索引，这种索引称为复合索引。 6、全文索引 全文索引是一种特殊类型的基于标记的功能性索引，由SQL Server中全文引擎服务创建和维护。,第六章：事务、视图

32、、索引,（2）创建索引 语法： CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON table_name (column_name,column_name) WITH FILLFACTOR = X 1、NUIQUE指定唯一索引，可选。 2、CLUSTERED、NONCLUSTERED指定是聚集索引还是非聚集索引，可选。 3、FILLFACTOR表示填充因子，指定一个0100的值，该值指示索引页填满的空间所占的百分比。 （3）删除索引 语法： DROP INDEX table_name.index_name 删除索引时需要主意以下

33、几点。 1、删除表时，该表的所有索引同时会被删除。 2、如果要删除表的所有索引，首先要删除非聚集索引，然后再删除聚集索引。,第六章：事务、视图、索引,（4）查看索引 1、用系统存储过程sp_helpIndex查看 语法： sp_helpIndex Table_name 2、用视图sys.indexes查看 语法： SELECT * FROM sys.indexes,第六章总结,1：数据库事务具有如下特性。 （1）原子性 （2）一致性 （3）隔离性 （4）持久性 2：事务可以分为如下类型。 （1）显式事务 （2）隐式事务 （3）自动提交事务 3：Transact-SQL使用下列语句来管理事务 （

34、1）BEGIN TRANSACTION （2）COMMIT TRANSACTION （3）ROLLBACK TRANSACTION 4：视图是另一种查看数据库中一个或多个表中的数据方法 5：视图是一种虚拟表，通常是作为执行查询的结果而创建的 6：视图充当着对查询中指定的表的筛选器 7：使用CREATE VIEW语句创建视图 8：使用SELECT语句查看视图的查询结果 9：建立索引有助于快速检索数据，索引分为唯一索引、主键索引、聚集索引和非聚集索引,第七章目标,本章重点内容： 本章将讲解什么是存储过程，同时介绍常用的系统存储过程和扩展存储过程，然后讲解如何创建并调用自定义存储过程，包括存储过程的

35、输入参数和输出参数，以及为输入参数赋默认值。,第七章：存储过程,（1）什么是存储过程 存储过程（Procedure）类似于C#语言中 的方法，它是SQL语句和控制语句的预编译集合。存储过程保存在数据库里，可由应用程序通过一个调用执行，而且允许用户声明变量、逻辑控制语句以及其他强大的编程功能。 存储过程可以只包含一条SELECT语句，也可以包含一系列使用控制流的SQL语句。 存储过程分为系统存储过程和用户自定义存储过程 使用存储过程有以下优点。 1、模块化程序设计 只需创建一次存储过程并将其存储在数据库中，以后即可在程序中调用该过程任意此。 2、执行速度快、效率高 3、减少网络流量 4、具有良好

36、的安全性,第七章：存储过程,（2）系统存储过程 SQL Server提供系统存储过程，它们是一组预编译的T-SQL语句。系统存储过程提供了管理数据库和更新表的机制，并充当从系统表中检索信息的快捷方式。 常用的系统存储过程有以下。 1、sp_databases 列出服务器上的所有数据库信息，包括数据库名和数据大小。 2、 sp_helpdb 报告有关指定数据库或所有数据库的信息 3、sp_renamedb 更改数据库的名称 4、sp_tables 返回当前环境下可查询的表或视图的信息 5、sp_columns 返回某个表或视图的列信息，包括列的数据类型和长度等。 6、sp_stored_proc

37、edures 显示存储过程的列表,第七章：存储过程,（3）用户自定义的存储过程 1、创建不带参数的存储过程 语法： CREATE PROCEDURE 存储过程名 参数1 数据类型 = 默认值 OUTPUT, , 参数n 数据类型 = 默认值 OUTPUT AS SQL语句 2、删除存储过程 DROP PROCEDURE 存储过程名 一个完整的删除过程最好是先判断该存储过程是否存在，然后再执行删除操作，其SQL语句如下。 IF EXISTS (SELECT * FROM sysobjects WHERE name = 存储过程名) DROP PROCEDURE 存储过程名 GO,第七章：存储过程

38、,3、示例： USE MySchool GO IF EXISTS (SELECT * FROM sysobjects WHERE name = proc_studentsInfo) DROP PROCEDURE proc_studentsInfo GO CREATE PROCEDURE proc_studentsInfo AS SELECT * FROM Student WHERE stuName = 张三 GO -调用执行存储过程 EXEC proc_studentsInfo,第七章：存储过程,4、创建带输入参数的存储过程 存储过程中的参数分为： （1）输入参数：可以在调用时向存储过程传递参

39、数，此类参数可用来在存储过程中传入值。 （2）输出参数：如果希望返回值，则可以使用输出参数，输出参数后有”OUTPUT“标记，执行存储过程后，将把返回值存放在输出参数中，可供其他T-SQL语句读取访问。 如果存储过程的参数后面有”OUTPUT“关键字，表示此参数为输出参数，否则视为输入参数，输入参数还可以设置默认值。 语法： EXECUTE 返回变量 = 存储过程名 参数1 = 参数值1 OUTPUT | DEFAULT, ， 参数1 = 参数值 n OUTPUT | DEFAULT,第七章：存储过程,5、示例： IF EXISTS (SELECT * FROM sysobjects WHERE name = pro_query_num) DROP PROCEDURE pro_query_num GO CREATE PROCEDURE pro_query_num UnpassNum INT OUTPUT, TotalNum INT OUTPUT, SubjectName NVARCHAR(50), Pass INT AS DECLARE Date datetime DECLARE subjectNo INT SELECT Date = MAX(Result.ExamDate) FROM Result INNER JOIN Sub