第一章
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表
|
|
添加记录时
|
存放新增的记录
|
。。。。。。。。
|
|
删除时
|
。。。。。。
|
存放被删除的记录
|
|
修改时
|
存放用来更新的新记录
|
存放更新前的记录
|
Create trigger 触发器名
On 表名
[With encryption] ----------加密
For insert
As
SQL语句
Go
Create trigger 触发器名
On 表名
[With encryption] ----------加密
For delete
As
SQL语句
Go
Create trigger 触发器名
On 表名
[With encryption] ----------加密
For update
As
SQL语句
Go
扫一扫
2184
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
