------------------SQL基础和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
 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/xiaoping8411/archive/2008/07/09/2629478.aspx