sqlserver经典学习总结.doc

Sql server基础1 Transact-SQL 语 言SQL 语言是一种介于关系代数与关系演算之间的语言其功能包括查询操纵定义和控制4 个方面是一个通用的功能极强的关系数据库语言SQL 语言的组成: 数据定义语言DDL Data Definition Languagecreate table 创建一个数据库表drop table 从数据库中删除表alter table 修改数据库表结构create view 创建一个视图drop view 从数据库中删除视图create index 为数据库表创建一个索引drop index 从数据库中删除索引create procedure 创建一个存储过程drop procedure 从数据库中删除存储过程 . 数据操纵语言DML Data Manipulation Language select 从数据库表中检索数据行和列 insert 向数据库表添加新数据行 delete 从数据库表中删除数据行 update 更新数据库表中的数据 数据控制语言DCL Data Control Languagegrant 授予用户访问权限deny 拒绝用户访问revoke 解除用户访问权限2 条件表达式和逻辑运算符 SQL Server提供的算术运算符运 算 符功 能+完成两个数值型数据的相加操作/两个字符型数据的字符串串联操作-完成两个数值型数据的相减操作*完成两个数值型数据的相乘操作/完成两个数值型数据的相除操作%完成两个数值型数据的模运算 SQL Server提供的逻辑运算符运 算 符功 能AND二元运算，当参与运算的子表达式全部返回TRUE时，整个表达式的最终结果为TRUEOR二元运算，当参与运算的子表达式中有一个返回为TRUE时，整个表达式返回TRUENOT对参与运行的表达式结果取反IN如果操作数与表达式列表中的任何一项匹配，则返回TRUEBETWEEN如果操作数位于某一指定范围，则返回TRUEEXISTS如果表达式的执行结果不为空，则返回TRUEANY对OR操作符的扩展，将二元运算推广为多元运算ALL对AND运算符的扩展，将二元运算推广为多元运算SOME如果在一系列比较中，有某些子表达式的值为TRUE，那么整个表达式返回TRUELIKE如果操作数与一种模式相匹配，那么就为 TRUE 比较运算符运 算 符功 能!=不等于，等同于!=!不大于，等同于=注：通配符：_ % 3 T-SQL基础操作：Insert： 语法：insert into table_name(col_name1.) values (value1.) 通过insert select语句将现有表中的数据添加到新表中 例如： Insert into tongxulu (姓名,地址,电子邮件) Select SName,SAddress,SEmail From student 通过select into 语句将现有的表中的数据添加到新表中 Select student.SName,student.SAddress Into tongxuelu From student 通过union关键字合并数据进行插入Union:用于将两个不同的数据或查询结果组合成新的结果集例如：Insert student(sname,sgread)Select 张三，1 unionSelect 李四，2 unionSelect 王五，3 Update: 语法：update set where Delete: 语法：delete from where Truncate table: 语法：Truncate table 数据查询1 使用select查询 语法：select From where order by desc 或 asc A 查询数据和列 B 条件查询 C 使用别名 D 查询空行（is null） E 查询中使用常量 F 查询使用的行数（top num）2 查询排序 ：使用。order by。 3 like进行模糊查询 例如： select * from student where sname like 张%4 使用between在某个范围内查询 例如： 1 select * from SCore where score between 60 and 80 2 select * from sales where ord_date not between 1982-8-1 and 1993-8-1 5 使用in查询 例如： Select sname , saddress from student Where saddress in (北京,上海,广州) Order by saddress6 聚合函数(sum,avg,max,min,count(num或char) 例如： select sum(score) from where stuno is not null .7 having select From where order by Having 总结： 1 Where是对表里的原始数据进行的筛选，having是对分组使用聚合函数计算过的数据进行的筛选，having与group结合使用 2 聚合函数五个sum,avg,max,min,count()，其中 count不包含null列8 字符串处理 类 型函 数 名功 能转换函数CHAR将ASCII码转换为字符ASCII将字符转为ASCII码LOWER/UPPER将字符转换为小写/大写子字符操作LEFT/RIGHT从字符串的左/右端开始取n个字符形成新的子串SUBSTRING获取子字符串CHARINDEX获取子串在字符串中的起始位置REPLACE以某一字符串替换另一字符串中的指定子字符串STUFF以某一字符串替换另一字符串中的指定子字符串，需指定子字符串的起始位置和长度其他SPACE以空格填充一个字符串LTRIM/RTRIM去除字符串左/右端的空格LEN获取字符串的长度9 日期和时间函 数 名功 能GETDATE获取当前的日期和时间YEAR/MONTH/DAY获取当前的年/月/日DATEADD(unit,n,date)在date的基础上添加n(天/小时/年)后的日期DATEDIFF(unit,date1,date2)以unit为单位计算日期1与日期2之间的差距DATEPART(part,date)返回指定日期的指定部分(如年/月/日)DATENAME(part,date)与DATEPART功能大致相同，只是以字符串形式表示10专用于时间函数的常见缩写表1.11 常见缩写日 期缩 写Year(年) YyQuarter(季度) QqMonth(月) MmDay of Year(一年中的第几天) DyDay(一月的第几号) DdWeek(一年的第几周) WkWeekday(一周的星期几) DwHour(小时) HhMinute(分钟) MiSecond(秒) SS11 表连接 11.1 内连接：经常用于两个表主外键关系 例如： 1 select students.sname,score.courseId,score.score From students,score Where students.score=score.studentId 2 select s.sname , c.courseId , c.score From students as s inner join score as c On (s.score = c.studentId) 11.2外连接：左外连接，右外连接 左外连接（右外连接） 例如： Select s.name , c.courseId , c.score From students as s Left outer join score as c On s.score = c.studentId 1. 前面的是左表后面的是右表2. 显示左表的全部数据，右表与之匹配的现实，不匹配的用null补全（1显示内连接的所有数据2左表没匹配的原样显示，右表部分用null补全） 数据库的设计1 设计问题： 信息重复，更新异常，插入异常，删除异常2 规范设计：三大范式 第一范式：确保每列的原子性（每列都是不可再分的最小数据单元） 第二范式：要求每个表只描述一件事情（ 如果一个关系满足1NF，并且除了主键以外的其他列，都依赖与该主键，则满足第二范 式） 第三范式：列于主键形成依赖关系（如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式） 数据库的实现1 创建数据库：/IF EXISTS（SELECT * FROM ? WHERE name=stuDB） DROP DATABASE stuDBCREATE DATABASE stuDBON PRIMARY ( NAME=stuDB_data, -主数据文件的逻辑名 FILENAME=D:projectstuDB_data.mdf, -主数据文件的物理名 SIZE=5mb, -主数据文件初始大小 MAXSIZE=100mb, -主数据文件增长的最大值 FILEGROWTH=15% -主数据文件的增长率)LOG ON( /*-日志文件的具体描述，各参数含义同上-*/ NAME=stuDB_log, FILENAME=D:projectstuDB_log.ldf, SIZE=2mb, FILEGROWTH=1 mb)GO 2 创建表 /IF EXISTS（SELECT * FROM ? WHERE name=stuDB） DROP TABLE stuInfoUSE stuDB GOCREATE TABLE stuInfo (stuName VARCHAR(20) NOT NULL , stuNo CHAR(6) NOT NULL, stuAge INT NOT NULL, stuID NUMERIC(18,0), stuSeat SMALLINT IDENTITY (1,1), stuAddress TEXT ) GO3 添加约束：- 添加主键约束（stuNo作为主键）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-添加检查check约束，要求年龄只能在1540岁之间ALTER TABLE stuInfo ADD CONSTRAINT CK_stuAge CHECK(stuAge BETWEEN 15 AND 40)-添加外键约束（主表stuInfo和从表stuMarks建立关系，关联字段为stuNo）ALTER TABLE stuMarks ADD CONSTRAINT FK_stuNo FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo)GO4 添加帐户： /*-添加Windows登录帐户 -*/ EXEC sp_grantlogin jbtrainings26301 - windos用户：jbtrainings26301，jbtraining表示域 /*-添加SQL登录帐户-*/ EXEC sp_addlogin zhangsan, 1234 - 帐户：zhangsan 密码：1234GO5 添加用户：/*-在stuDB数据库中添加两个用户-*/USE stuDBGOEXEC sp_grantdbaccess jbtrainingS26301, S26301DBUser - S26301DBUser为数据库用户名EXEC sp_grantdbaccess zhangsan, zhangsanDBUser 6 分配权限：USE stuDBGO/*-为zhangsanDBUser分配对表stuInfo的select, insert, update权限-*/GRANT select, insert, update ON stuInfo TO zhangsanDBUser /*-为S26301DBUser分配建表的权限-*/GRANT create table TO S26301DBUser T-SQL编程1 变量分为：局部变量：局部变量必须以标记作为前缀 ，如age局部变量的使用也是先声明，再赋值 全局变量：全局变量必须以标记 作为前缀，如version全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值变量含义ERROR最后一个T-SQL错误的错误号IDENTITY最后一次插入的标识值LANGUAGE当前使用的语言的名称MAX_CONNECTIONS可以创建的同时连接的最大数目ROWCOUNT受上一个SQL语句影响的行数SERVERNAME本地服务器的名称TRANSCOUNT当前连接打开的事务数VERSIONSQL Server的版本信息变量：/*-查找李文才的信息-*/DECLARE name varchar(8)SET name=李文才 -使用SET赋值SELECT * FROM stuInfo where stuName = name/*-查找李文才的左右同桌-*/DECLARE seat intSELECT seat=stuSeat from stuInfo where stuName=name -使用SELECT赋值SELECT * FROM stuInfo where (stuSeat = seat+1) or (stuSeat = seat-1)GO全局变量和输出：INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge) VALUES(梅超风,s25318,女,23)print 当前错误号+convert(varchar(5),ERROR) -如果大于0表示上一条语句执行有错误print 刚才报名的学员，座位号为: +convert(varchar(5),IDENTITY )UPDATE stuinfo SET stuAge=85 WHERE stuName=李文才print 当前错误号+convert(varchar(5),ERROR) print SQL Server的版本+VERSION GO 2 逻辑控制语句 If语句：DECLARE myavg floatSELECT myavg=AVG(writtenExam) from stuMarksprint 本班平均分+convert(varchar(5),myavg)IF (myavg70) BEGIN print 本班笔试成绩优秀，前三名的成绩为 SELECT TOP 3 * FROM stuMarks ORDER BY writtenExam DESC ENDELSE BEGIN print 本班笔试成绩较差，后三名的成绩为 SELECT TOP 3 * FROM stuMarks ORDER BY writtenExam DESC ENDWHILE循环语句INSERT INTO stuMarks(examNo,stuNo,writtenExam,LabExam) -插入测试数据 VALUES(S271819,s25318,56,48)SELECT * FROM stuMarksDECLARE n intWHILE(1=1) -条件永远成立 BEGIN SELECT n=COUNT(*) FROM stuMarks WHERE writtenExam0) UPDATE stuMarks SET writtenExam=writtenExam+2 -每人加2分 ELSE BREAK -退出循环 ENDprint 加分后的成绩如下：SELECT * FROM stuMarksGoCASEEND多分支语句SELECT * FROM stuMarks -原始成绩print ABCDE五级显示成绩如下：SELECT stuNo,成绩= CASE WHEN writtenExam(子查询) 外面的查询称为父查询，括号中嵌入的查询称为子查询 将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个 例如：select * from stuinfoselect * from stumarks/*-实现方法一：SQL变量-*/DECLARE age INT -定义变量，用于存放李斯文的年龄SELECT age=stuAge FROM stuInfo where stuName=李斯文 -求出李斯文的年龄SELECT * FROM stuInfo WHERE stuAgeage -筛选比李斯文年龄大的学员GO/*-实现方法二：子查询-*/SELECT * FROM stuInfo WHERE stuAge( SELECT stuAge FROM stuInfo where stuName=李斯文)使用子查询替换表连接:例如：select * from stuinfoselect * from stumarks/*-实现方法一：采用表连接-*/SELECT stuName FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam=60/*-实现方法二：采用子查询-*/SELECT stuName FROM stuInfo WHERE stuNo=(SELECT stuNo FROM stuMarks WHERE writtenExam=60)GOIN子查询：SELECT * FROM stuInfo/*-插入测试数据-*/INSERT INTO stuMarks VALUES(S271817,s25318,60,52)SELECT * FROM stumarks/*-出现编译错误的子查询-*/SELECT stuName FROM stuInfo WHERE stuNo = (SELECT stuNo FROM stuMarks WHERE writtenExam=60)GO/*-采用IN子查询-*/SELECT stuName FROM stuInfo WHERE stuNo IN (SELECT stuNo FROM stuMarks WHERE writtenExam=60)GONOT IN子查询：SELECT * FROM stuInfoSELECT * FROM stumarks/*-采用IN子查询，查看参加考试的学员名单-*/SELECT stuName FROM stuInfo WHERE stuNo IN (SELECT stuNo FROM stuMarks)GO/*-采用NOT IN子查询，查看未参加考试的学员名单-*/SELECT stuName FROM stuInfo WHERE stuNo NOT IN (SELECT stuNo FROM stuMarks)GOEXISTS(NOT EXISTS)子查询: SELECT * FROM stuInfoSELECT * FROM stumarks/*-采用EXISTS子查询，进行酌情加分-*/IF EXISTS (SELECT * FROM stuMarks WHERE writtenExam80) BEGIN print 本班有人笔试成绩高于80分，每人只加2分，加分后的成绩为： UPDATE stuMarks SET writtenExam=writtenExam+2 SELECT * FROM stuMarks ENDELSE BEGIN print 本班无人笔试成绩高于80分，每人可以加5分，加分后的成绩为： UPDATE stuMarks SET writtenExam=writtenExam+5 SELECT * FROM stuMarks ENDGO/*-采用NOT EXISTS子查询，根据试题难度加分-*/IF NOT EXISTS (SELECT * FROM stuMarks WHERE writtenExam60 AND labExam60) BEGIN print 本班无人通过考试，试题偏难，每人加3分，加分后的成绩为： UPDATE stuMarks SET writtenExam=writtenExam+3,labExam=labExam+3 SELECT * FROM stuMarks ENDELSE BEGIN print 本班考试成绩一般，每人只加1分，加分后的成绩为： UPDATE stuMarks SET writtenExam=writtenExam+1,labExam=labExam+1 SELECT * FROM stuMarks ENDGO 事务、索引和视图什么是事务: 事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作 这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行 事务是一个不可分割的工作逻辑单元 事务必须具备以下四个属性，简称ACID 属性： 原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行 一致性（Consistency）：当事务完成时，数据必须处于一致状态 隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务 永久性（Durability）：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性管理事务： 开始事务：BEGIN TRANSACTION 提交事务：COMMIT TRANSACTION 回滚（撤销）事务：ROLLBACK TRANSACTION分类： 显示事务：用BEGIN TRANSACTION明确指定事务的开始，这是最常用的事务类型 隐性事务：通过设置SET IMPLICIT_TRANSACTIONS ON 语句，将隐性事务模式设置为打开，下一个语句自动启动一个新事务。当该事务完成时，再下一个 T-SQL 语句又将启动一个新事务 自动提交事务：这是 SQL Server 的默认模式，它将每条单独的 T-SQL 语句视为一个事务，如果成功执行，则自动提交；如果错误，则自动回滚创建事务，例如：USE stuDBCREATE TABLE bank( customerName CHAR(10), -顾客姓名 currentMoney MONEY -当前余额)GOALTER TABLE bank ADD CONSTRAINT CK_currentMoney CHECK(currentMoney=1)GOINSERT INTO bank(customerName,currentMoney) VALUES(张三,1000)INSERT INTO bank(customerName,currentMoney) VALUES(李四,1)go SET NOCOUNT ON -不显示受影响的行数信息print 查看转帐事务前的余额SELECT * FROM bank GO/*-开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体-*/BEGIN TRANSACTION /*-定义变量，用于累计事务执行过程中的错误-*/DECLARE errorSum INT SET errorSum=0 -初始化为0，即无错误/*-转帐：张三的帐户少1000元，李四的帐户多1000元*/UPDATE bank SET currentMoney=currentMoney-800 WHERE customerName=张三SET errorSum=errorSum+error -累计是否有错误UPDATE bank SET currentMoney=currentMoney+800 WHERE customerName=李四SET errorSum=errorSum+error -累计是否有错误print 查看转帐事务过程中的余额SELECT * FROM bank /*-根据是否有错误，确定事务是提交还是撤销-*/IF errorSum0 -如果有错误 BEGIN print 交易失败，回滚事务 ROLLBACK TRANSACTION END ELSE BEGIN print 交易成功，提交事务，写入硬盘，永久的保存 COMMIT TRANSACTION ENDGOprint 查看转帐事务后的余额SELECT * FROM bank GO什么是索引： 索引：是SQL Server编排数据的内部方法。它为SQL Server提供一种方法来编排查询数据 。 索引页：数据库中存储索引的数据页；索引页类似于汉语字（词）典中按拼音或笔画排序的目录页。 索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。索引类型： 唯一索引：唯一索引不允许两行具有相同的索引值 主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空 聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个 非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个索引的优缺点：优点加快访问速度加强行的唯一性缺点带索引的表在数据库中需要更多的存储空间操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新创建索引,例如：USE stuDBGO/*-检测是否存在该索引(索引存放在系统表sysindexes中)-*/IF EXISTS (SELECT name FROM sysindexes WHERE name = IX_stuMarks_writtenExam) DROP INDEX stuMarks.IX_stuMarks_writtenExam -删除索引/*-笔试列创建聚集索引:填充因子为30-*/CREATE NONCLUSTERED INDEX IX_stuMarks_writtenExam ON stuMarks(writtenExam) WITH FILLFACTOR= 30GO/*-指定按索引：IX_stuMarks_writtenExam查询-*/SELECT * FROM stuMarks (INDEX=IX_stuMarks_writtenExam)WHERE writtenExam BETWEEN 60 AND 90什么是视图： 视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结 构和数据是建立在对表的查询基础上 视图中并不存放数据，而是存放在视图所引用的原始表（基表）中 同一张原始表，根据不同用户的不同需求，可以创建不同的视图创建视图：语法： CREATE VIEW view_name AS 例如：USE stuDBGO/*-检测是否存在：视图存放在系统表sysobjects中-*/IF EXISTS (SELECT * FROM sysobjects WHERE name = view_stuInfo_stuMarks) DROP VIEW view_stuInfo_stuMarksGO/*-创建视图：查看学员的成绩情况-*/CREATE VIEW view_stuInfo_stuMarks AS SELECT 姓名=stuName,学号=stuInfo.stuNo,笔试成绩=writtenExam, 机试成绩=labExam,平均分=(writtenExam+labExam)/2 FROM stuInfo LEFT JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNoGO/*-使用视图：视图是一个虚拟表，可以像物理表一样打开-*/SELECT * FROM view_stuInfo_stuMarks 存储过程什么是存储过程： 存储过程（procedure）类似于C语言中的函数 用来执行管理任务或应用复杂的业务规则 存储过程可以带参数，也可以返回结果存储过程的优点： 执行速度更快 允许模块化程序设计 提高系统安全性 减少网络流通量存储过程的分类:系统存储过程 由系统定义，存放在master数据库中 类似C语言中的系统函数 系统存储过程的名称都以“sp_”开头或”xp_”开头用户自定义存储过程 由用户在自己的数据库中创建的存储过程 类似C语言中的用户自定义函数常用的系统存储过程: sp_databases = 列出服务器上的所有数据库。 sp_helpdb = 报告有关指定数据库或所有数据库的信息 sp_renamedb = 更改数据库的名称 sp_tables = 返回当前环境下可查询的对象的列表 sp_columns = 回某个表列的信息 sp_help = 查看某个表的所有信息 sp_helpconstraint = 查看某个表的约束 sp_helpindex = 查看某个表的索引 sp_stored_procedures = 列出当前环境中的所有存储过程。 sp_password = 添加或修改登录帐户的密码。 sp_helptext = 显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。创建存储过程：USE stuDBGO/*-检测是否存在：存储过程存放在系统表sysobjects中-*/IF EXISTS (SELECT * FROM sysobjects WHERE name = proc_stu ) DROP PROCEDURE proc_stuGO/*-创建存储过程-*/CREATE PROCEDURE proc_stu notpassSum int OUTPUT, -OUTPUT关键字，否则视为输入参数 writtenPass int=60, -默认参数放后 labPass int=60 -默认参数放后 AS print 笔试及格线：+convert(varchar(5),writtenPass) + 机试及格线：+convert(varchar(5),labPass) print - print 参加本次考试没有通过的学员： SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass /*-统计并返回没有通过考试的学员人数-*/ SELECT notpassSum=COUNT(stuNo)FROM stuMarks WHERE writtenExamwrittenPass OR labExam=3 print 未通过人数：+convert(varchar(5),sum)+ 人,超过60%,及格分数线还应下调ELSE print 未通过人数：+convert(varchar(5),sum)+ 人,已控制在60%以下，及格分数线适中GO