数据库基础和T-SQL语句编程

 

 

第一章 SQL Server数据库基础

1.        使用数据库的必要性：

l        可以结构化的存储大量的数据信息，方便用户进行有效的检索和访问

l        可以有效地保持数据信息的一致性、完整性、降低数据冗余

l        可以满足应用的共享和安全方面的要求

l        数据库技术能够方便智能化的分析，产生新的有用信息

2.        数据库管理系统的发展史

l        文件系统

l        第一代数据库：层次模型与网状模型的数据库系统

l        第二代数据库：关系数据库

l        第三代数据库：对象数据库

3.        数据库的基本概念：

l        实体和记录

l        数据库和数据库表

l        数据库系统和数据库管理系统

l        数据冗余和数据完整性

第二章 SQL Server数据管理

1.        T-SQL的组成：

l        DML（数据操作语言）用来查询、插入、删除和修改数据库中的数据，如SELECT、INSERT、UPDATE及DELETE

l        DCL（数据控制语言）用来控制数据库组件的存取许可、存取权限的命令，如GRANT、REVOKE等

l        DDL（数据定义语言）用来建立数据库、数据库对象和第一其列，大部分是以CREATE开头的命令，如CREATE TABLE 、CREATE VIEW及DROP TABLE等

2.        T-SQL中的条件表达式和逻辑运算符

l        条件表达式：

运算符

含义

=

等于

> 

 

< 

 

>=

 

<=

 

<> 

不等于

!

非

 

通配符

通配符

解释

示例

‘_’

一个字符

A like ‘c_’

%

任意长度的字符串

B like ‘CO%’

[]

括号中所指定范围内的一个字符

C like ‘9w0[1-2]’

[^]

不在括号内所指定范围内的任意一个字符

D like ‘9w0[^1-2]’

 

l        逻辑表达式：and、or和not

3.        使用T-SQL插入数据

l        使用INSERT插入数据行:INSERT [INTO] <表名> [列名]VALUES <值列表>

l        一次插入多行数据

1.        通过INSERT SELECT语句将现有表中的数据添加到新表

Insert into 新表[列1，列2。。。] select [列1，列2。。。] from 旧表

注意：新表一定要存在

2.        通过SELECT INTO语句将现有表中的数据添加到新表

Select [列1，列2。。。] into 新表 from 旧表

注意：新表不能够预先存在

问题：怎么将标识列？

Select identity [列1，列2。。。] into 新表 from 旧表

3.        通过UNION关键字合并数据进行插入

INSERT 表名 [列1，列2。。。]

Select 列1值，列2值。。。。UNION

Select 列1值，列2值。。。。UNION

Select 列1值，列2值。。。。UNION

Select 列1值，列2值。。。。UNION

Select 列1值，列2值。。。。

4.        使用T-SQL更新数据：UPDATE <表名> SET <l列名=更新值>[WHERE<更新条件>]

5.        使用T-SQL删除数据：

l        DELETE FROM<表名>[WHERE <删除条件>]

l        Truncate table 表名

两者区别：TRUNCATE比DELETE执行的速度快，TRUNCATE不能用于有外键约束的表。

 

第三章数据查询

1.        使用SELECT语句进行查询

格式：SELECT <列名>

        FROM<表名>

        [WHERE <查询条件表达式>]

      [ORDER BY <要排序的列名>[ASC或DESC]]

l        查询所有的数据：select * from 表名

l        查询部分的数据：select <列名> from 表名 where 查询条件

l        查询时给列取别名：select 列1 as 别名。。。where 表名

l        查询空行：使用 IS NULL

l        查询返回限制的行数：使用TOP关键字

例如：select top 5 sname,saddress FROM student

问题：怎么查询20%的数据？使用pricent关键字

例如：select top 20 pricent FROM student

2.        查询排序：使用ORDER BY字句 ASC—>升序 DESC——>降序

3.        在查询中使用函数：字符串函数、日期函数、数学函数、系统函数

l        字符串函数：用于控制返回给用户的字符串，仅用于字符型数据。

函数名

描述

举例

CHARINDEX

用来寻找一个指定的字符串在另一个字符串中的起始位置

Select charindex(‘accp’,’my accp course’,1) 返回：4

Len

返回传递给它的字符串长度

Select Len(‘SQL Server课程’)

返回12

Lower

把传递给它的字符串转换成小写

Select Lower(‘SQL Server课程’)

UPPER

把传递给它的字符串转换成大写

 

LTRIM

清除字符左边的空格

Select ltrim(‘ Xiaoping ’)

返回：xiaoping (后面的空格保留)

RTRIM

清楚字符右边的空格

 

RIGHT

从字符串右边返回指定数目的字符

Select right(‘xiaoping123’,3)

返回：123

LEFT

从字符串左边返回指定数目的字符

 

REPLACE

替换一个字符串中的字符

Select replace(‘莫乐可切.杨可’，’可’,’兰’)

返回：莫乐兰切.杨兰

STUFF

在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字符串

Select stuff(‘ABCDEFG’,2,3,’xiaoping’)

返回：AxiaopingEFG

l        日期函数：用于操纵日期值。

函数名

描述

举例

GETDATE

取得当前的系统日期

Select getdate()

返回：今天的日期

DATEADD

将指定的数值添加到指定的日期部分后的日期

Select dateadd(mm,4,’01/01/99’)

返回：以当前的日期格式返回05/01/99

DATEDIFF

两个日期之间的指定日期部分的区别

Select datediff(mm,’ 01/01/99’,’ 05/01/99’) 返回：4

DATENAME

日期中指定日期部分的字符串形式

Select datename(dw,’01/01/2000’)

返回：Saturday

DATEPART

日期中指定日期部分的整数形式

Select datepart(day,’01/15/2000’)

返回：15

l        数学函数：用于对数值进行代数运算。

函数名

描述

举例

ABS

取数值表达式的绝对值

Select ABS(-43)

返回：43

CEILING

取大于或等于指定数值、表达式的最小整数

Select ceiling(43.5)

返回：44

FLOOR

取小于或等于指定数值、表达式的最小整数

 

POWER

取数值表达式的幂值

Select power(5,2)

返回：25

ROUND

将数值表达式四舍五入为指定精度

Select round(43.543,1) 返回：43.5

Sign

对于正整数返回+1，对于负数返回-1，对于0返回0

 

Sqrt

取浮点表达式的平方根

Select sqrt(9) 返回：3

l        系统函数：用来获取有关SQL Server中对象和设置的系统信息。

函数名

描述

举例

CONVERT

用来转变数据类型

Select convert(varchar(5),12345)

返回：字符串12345

Current_user

返回当前用户的名字

 

DataLength

返回用于指定表达式的字节数

 

Host_name

返回当前用户所登陆的计算机名

 

System_user

返回当前所登陆的用户名称

 

User_name

从给定的用户ID返回用户名

 

4.        模糊查询：

l        使用LIKE进行模糊查询：LIKE运算符用于匹配字符串或字符串的一部分。仅与CHAR或VARCHAR数据类型联合使用,例如：

Select * from Student where Sname like ‘张%’

l        使用BETWEEN在某个范围内进行查询：例如：

Select * from Score where score between 60 and 80

Select * from sales where ord_date not between ‘1992-8-1’ and ‘1993-8-1’

l        使用IN在列举值内进行查询：例如：

Select Sname from Student where Saddress in(‘北京’,’上海’,’江西’)

5.        聚合函数：聚合函数不能与可能返回多行的列一起使用来进行查询，但是，在一个查询中可以使用多个聚合函数

l        SUM：返回表达式中所有数值的总和，SUM只能用于数字类型的列。例如：

Select sum(ytd_sales) from titles where type=’business’

l        AVG：返回表达式中所有数值的平均值，只能用于数字类型的列。例如：

Select avg(Score) as 平均分 from Score where Score >= 60

l        MAX和MIN：MAX返回表达式的最大值，MIN返回表达式的最小值，它们都可以用于数字型、字符型及日期/时间的列。例如：

Select avg(Score) as 平均分，max(Score) as 最高分，min(Score) as 最低分 from score where score >=60

l        Count：返回提供的表达式非空值的计数，可以用于数字和字符类型的列。例如：

Select count(*) as 及格人数 from score where Score >=60

6.        分组查询

l        使用Group by 进行分组查询：例如：

Select CourseID ,avg(Score) as 平均分 from Score group by CourseID

l        使用having 字句进行分组筛选；例如：

Select StudentID as 学员编号，CourseID as 内部测试，AVG(Score) as 内部测试平均分 from Score

Group by StudentID, CourseID

Having Count(score)>1

7.        多表联接查询：

l        内联接：inner join格式：select 列名 from 表1 inner join 表2 on 条件

l        外联接

1.        左外联接left join

2.        右外联接 right join

3.        完整外连接 full join

第四章数据库的设计

1.        设计数据库的步骤：

Ø        需求分析

Ø        概要设计

Ø        详细设计

Ø        代码编写

Ø        运行测试

Ø        打包发行

第四章数据库的实现

1．创建数据库：格式：

       If exists (select * from sysdatabases where name =’数据库名’)

              Drop database 数据库名

       Create database 数据库名

       On primary

(

              Name=’’

              ,filename=’’

              ,size=

              ,filegrowth

)     log on

(

              Name=’’

              ,filename=’’

              ,size=

              ,filegrowth

)

2.        删除数据库： drop database 数据库名

3.        创建表：建表的语法：

If exists (select * from sysObjects where name =’表名’)

       Drop table表名

Create table 表名

（

       列名1 数据类型 列的特征，

       列名1 数据类型 列的特征，

       。。。。。。

）

4.        添加约束：添加约束的语法

Alter table 表名

Add constraint 约束名 约束类型  具体的约束说明

例如：添加主键约束

Alter table stuInfo

Add constraint PK_stuNo primary key(stuNo)

添加唯一约束

Alter table stuInfo

Add constraint UQ_stuID unique(stuID)

添加默认约束

Alter table stuInfo

Add constraint DF_stuAddress default(‘地址不详’) for stuAddress

添加检查约束

Alter table stuInfo

Add constraint CK_stuAge check(stuAge between 15 and 40)

添加外键约束

Alter table stuMarks

Add constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)

5.        删除约束：alter table 表名 drop constraint 约束名

6.        使用SQL语句创建登陆

访问SQL Server某个数据库中的某个表，需要3层验证

Ø        是否是SQL Server 的登陆帐户

Ø        是否是该数据库的用户

Ø        是否有足够的权限访问该表

n        创建登陆帐户，有两种方式：

1.        SQL身份验证：适合于非Windows平台的用户或Internet用户，需要提供帐户和密码

EXEC sp_addlogin ‘帐户名’，’密码’     例如：

EXEC sp_addlogin ‘zhangsan’,’1234’-----帐户名为zhangsan 密码1234

2.        Windows身份验证：适合于Windows平台用户，不需要提供密码和Windows集成验证

EXEC sp_grantlogin ‘windows域名/域帐户’   例如：

EXEC sp_grantlogin ‘jbtraining/s26301’—windows用户为jbtraining/s26301

Jbtraining表示域

n        创建数据库用户：EXEC sp_grantdbaccess ‘登陆帐户’,’数据库用户’

EXEC sp_grantdbaccess ‘jbtraining/s26301’, ‘s263o1DBUser’

EXEC sp_grantdbaccess ‘zhangsan’,’zhangsanDBUser’

n        向数据库用户授权：grant 权限 [ON 表名] TO 数据库用户    例如：

Grant select , insert , update on stuInfo to zhangsanDBUser

Grant create table to s263o1DBUser

 

第五章 T-SQL编程

1.        使用变量：变量是可以存储数据值的对象。

n        局部变量：局部变量的使用也是先声明，在使用，必须一标记@作为前缀

定义变量：declare @变量名 数据类型   例如：declare @name varchar(8)

变量的赋值：set @变量 =值 或者 select @变量=值

SET赋值语句一般用于赋给变量指定的数据常量。

SELECT赋值语句一般用于从表中查询数据，然后在赋给变量。注意：SELECT语句需要确保筛选的记录不多于一条，如果多了，则把最后的一条记录赋值给变量

n        全局变量：由系统定义和维护，都使用两个@标志作为前缀

变量

含义

@@ERROR

最后一个T-SQL错误的错误号

@@IDENTITY

最后一个插入的标识值

@@LANGUAGE

当前使用的语言的名称

@@MAX_CONNECTIONS

可以创建的同时连接的最大数目

@@ROWCOUNT

受上一个SQL语句影响的行数

@@SERVERNAME

本地服务器的名称

@@TIMETICKS

当前计算机上每刻度的微秒数

@@TRANSCOUNT

当前连接打开的事务的事务数

@@VERSION

SQL Server的版本信息

 

2.        输出语句

n        Print局部变量或字符串

n        SELECT局部变量AS自定义列名

例如：print ‘服务器的名称：’+@@SERVERNAME

SELECT @@SERVERNAME AS ‘服务器的名称‘

3.        逻辑控制语句

n        If –else条件语句，语法：

If(条件)

       Begin

              语句1

              语句2

              。。。。。

       End

Else

       ……

n        While循环语句，语法：

While(条件)

       Begin

              语句或语句快

       end

n        CASE多分支语句，语法：

Case

       When 条件1 then 结果1

     When 条件2 then 结果2

       When 条件3 then 结果3

       Else 其他结果

4.        批处理语句：GO标志，SQL Server将批处理语句编译成一个可执行单元，每个批处理可以编译成单个执行计划，从而提供执行效率。

主要好处：就是能够简化数据库的管理。

 

 

 

第六章高级查询

 

5.        简单子查询：SQL Server执行时，先执行子查询部分，求出字查询部分的值，然后在执行整个父查询。

注意：将子查询和比较运算符（>,<,=）使用，必须保证子查询返回的值不能多于一个。

一般来说：表连接都可以用子查询替换，但反过来说却不一定。

例如：select * from stuInfo where stuAge>(select stuAge from stuInfo where stuName=’xiaoping’)

6.        IN和NOT IN 子查询：子查询中可以返回多条记录。例如：

Select * from stuInfo where stuNo in(select stuNo from stuMarks where writtenExam>60)

Select * from stuInfo where stuNo not in(select stuNo from stuMarks)

7.        EXISTS和NOT EXISTS子查询： 例如：

If exists (select * from sysObjects where name=’stuInfo’)

       Drop table stuInfo

第七章事务、索引和视图

1．事务：事务是单个的工作单元。如果事务成功，则提交数据，否则，回滚。

       一个逻辑工作单元必须有4个属性：原子性，一致性，隔离性，持久性

n        如何创建事务      

1.        开始事务：begin transaction

2.        提交事务: commit transaction

3.        回滚事务: rollback transation

n        事务的分类：

1.        显式事务：用BEGIN TRANSACTION明确指定事务的开始。

2.        隐性事务：通过设置SET IMPLICIT_TRANSACTION ON语句，

3.        自动提交事务：这是SQL Server的默认模式，它将每条单独的T-SQL语句视为一个事务。如果成功执行，则自动提交。如果错误，则自动回滚。

2．索引：

索引分为3种：

n        唯一索引：唯一索引不允许两行具有相同的索引值。

n        主键索引：在数据库中定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。

n        聚集索引：在聚集索引中，表中个行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引。

创建索引语法：

Create [unique][clustered|nonclustered] index index_name on table_name(column_name[,column_name]….) [with fillfactor=x] 例如：

If exists (select * from sysindexes wherer name =’IX_stuMarks_writtenExam’)

       Drop index stuMarks.IX_stuMarks_writtenExam

Create nonclustered index IX_stuMarks_writtenExam

       On stuMarks(writtenExam)

       With fillfactor=30

Go

3. 视图：视图只是保存在数据库中的select查询。

n        视图通常用来：

Ø        筛选表中的行

Ø        防止未经许可的用户访问敏感数据

Ø        降低数据库的复杂程度

Ø        将多个物理数据表抽象为一个逻辑数据表

n        使用视图给用户和开发人员带来很多好处。具体如下：

Ø        对最终用户的好处

1.        结果更容易理解。

2.        获得数据更容易

Ø        对开发人员的好处

1.        限制数据检索更容易

2.        维护应用程序更方便

n        如何创建视图

Ø        使用企业管理器创建视图

Ø        使用Ｔ－ＳＱＬ语句创建视图，语法：

Create view view_name

As

       <select 语句>

例如：

---检测是否存在：

If exists (select * from sysObjects wherer name=’view_stuInfo_stuMarks’)

       Drop view view_stuInfo_stuMarks

Go

--创建视图

Create view view_stuInfo_stuMarks

As

       Select 姓名=stuName,学号=stuInfo.stuNo,笔试成绩=writtenExam,机试成绩=labExam,平均分=(writtenExam+labExam)/2 from stuInfo left join stuMarks on stuInfo.stuNo=stuMaks.stuNo

Go

---使用视图：视图是一个虚拟表，可以象物理表一样打开

Select * from view_stuInfo_stuMarks

 

 

第八章存储过程

1.        什么是存储过程：存储过程类似于C语言中的函数，它是SQL语句和控制流语句的预编译集合。存储过程存储数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、逻辑控制语句以及其它强大的编程功能。

存储过程可包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快。

存储过程分为两种：系统存储过程和用户自定义的存储过程

2.        常用的系统存储过程：都以”sp_”开头，位于master数据库中。

系统存储过程

说明

Sp_databases

列出服务器上的所有数据库

Sp_helpdb

报告有关指定数据库或所有数据库的信息

Sp_renamedb

更改数据库的名称

Sp_tables

返回当前环境下可查询的对象的列表

Sp_columns

返回某个表列的信息

Sp_help

查看某个表的所有信息

Sp_helpconstraint

查看某个表的约束

Sp_helpindex

查看某个表的索引

Sp_stored_procedures

列出当前环境中的所有存储过程

Sp_password

添加或修改登陆帐户的密码

Sp_helptext

显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本

创建文件：EXEC XP_cmdshell ‘mkdir d:/bank’,no_output

3.        用户定义的存储过程

Ø        创建不带参数的存储过程，语法：

Create proc 存储过程名

As

       SQL语句

执行存储过程：EXEC 存储过程名

Ø        创建带输入参数的存储过程，语法：

Create proc 存储过程名

       @参数1 数据类型[=默认值]

       @参数2 数据类型[=默认值]

       @参数3 数据类型[=默认值]

       。。。。。

              As

                     SQL语句

执行存储过程：EXEC 存储过程名 参数1值，参数2值，参数3值。。。。

Ø        创建输出参数的存储过程，语法：

Create proc 存储过程名

       @参数1 数据类型[=默认值] output

       @参数2 数据类型[=默认值] output

       @参数3 数据类型[=默认值] output

       。。。。。

              As

                     SQL语句

       Declare @参数1 数据类型

       Declare @参数2 数据类型

       Declare @参数3 数据类型

       。。。。。

       EXEC 存储过程名 @参数1 output, @参数2 output, @参数3 output。。。。

 

第九章触发器

        触发器概念：触发器是一种特殊类型的存储过程。它是对表进行插入、更新或删除操作时自动执行的存储过程。触发器分为三种：

1.        INSERT触发器：当向表中插入数据时触发，自动执行触发器所定义的SQL语句。

2.        UPDATE触发器：当更新表中某列、多列时触发，自动执行所定义的SQL语句。

3.        DELETE触发器：当删除表中数据时触发，自动执行触发器所定义的SQL语句。

每个触发器有两个特殊的表：插入表（inserted表）和删除表（deleted表）。这两个表是逻辑表，并且是由系统管理的，存储在内存中，因此，不允许用户直接对其修改。

触发器本身就是一个特殊的事务单元。

修改操作

Inserted表

Deleted表

添加记录时

存放新增的记录

。。。。。。。。

删除时

。。。。。。

存放被删除的记录

修改时

存放用来更新的新记录

存放更新前的记录

 

        创建INSERT触发器，语法：

Create trigger 触发器名

On 表名

       [With encryption] ----------加密

       For insert

       As

              SQL语句

       Go

        创建DELETE触发器，语法：

Create trigger 触发器名

On 表名

       [With encryption] ----------加密

       For delete

       As

              SQL语句

       Go

 

        创建UPDATE触发器，语法：

Create trigger 触发器名

On 表名

       [With encryption] ----------加密

       For update

       As

              SQL语句

       Go