- /*
- 第一学期SQL Server应用开发
- 第三章 SQL Server数据管理
- 3.3 使用T-SQL插入数据
- 3.3.1 使用Insert插入数据行
- 3.3.2 一次插入多行数据
- 3.4 使用T-SQL更新数据
- 3.5 使用T-SQL删除数据
- 3.5.1 使用delete删除数据
- 3.5.2 使用truncate table 删除数据
- 第4章 数据查询(1)
- 4.2 T-SQL查询基础
- 4.2.2 使用select语句进行查询
- 4.3 查询排序
- 4.4 在查询中使用函数
- 4.4.1 字符串函数
- 4.4.2 日期函数
- 4.4.3 数学函数
- 4.4.4 系统函数
- 第五章 数据查询(2)
- 5.1 模糊查询
- 5.1.1 使用like进行模糊查询
- 5.1.2 使用between在某个范围内进行查询
- 5.2 SQL Server中的聚合函数
- 5.2.1 SUM(求和)
- 5.2.2 AVG(平均值)
- 5.2.3 MAX/MIN(最大值/最小值)
- 5.2.4 count(计数)
- 5.3 分组查询
- 5.3.1 使用Group by进行分组查询
- 5.3.2 使用having子句进行分组
- 5.4 多表连接查询d
- 5.4.1 多表连接查询的分类
- 5.4.2 内联接查询
- 5.4.3 外联接查询
- */
- /*
- 第三章 SQL Server数据管理
- */
- --3.3 使用T-SQL插入数据
- ---3.3.1 使用insert插入数据行
- insert [into] <表名>[列名] values <值列表>
- insert into jobs(job_id,job_desc,min_lvl,max_lvl) values(1,'aa',12,12)
- ---3.3.2 一次插入多行数据
- ----1.通过Insert select 语句将现有表中的数据添加到物理表a中
- insert into a(a.job_id) select job_id from jobs
- ----2.通过select into 语句将现有表中的数据添加到虚拟表aaa中
- select jobs.job_id,jobs.job_desc,jobs.min_lvl,jobs.max_lvl into aaa from jobs
- select indentity(int,1,1) as 列名 into 新表 from 原始表
- ----3.通过union关键字并数据进行插入与insert into.....select的效果一样
- insert a(job_id)
- select 111 union
- select 211 union
- select 311
- --3.4 使用T-SQL更新数据
- update <表名> set <列名 = 更新值> [where <更新条件>]
- update jobs set jobs_desc = 'aa'
- update jobs set job_desc = job_desc+'aa' where job_id = 1
- --3.5 使用T-SQL删除数据
- ---3.5.1 使用delete删除数据
- --delete from <表名> [where <删除条件>]
- delete from jobs where job_id = 1
- ---3.5.2 使用truncate table 删除数据
- truncate table a
- /*
- 第4章 数据查询(1)
- */
- --4.2 T-SQL查询基础
- ---4.2.2 使用select语句进行查询
- select <列名> from <表名> [where <查询条件表达式>] [order by <排序的列名> [asc or desc]]
- ----1. 查询所有的数据行和列
- select * from jobs
- ----2. 查询部分行列 ----条件查询
- select job_id,job_desc,min_lvl,max_lvl from jobs where job_id = 1
- ----3. 在查询中使用列名
- select job_id as 编号,job_desc as 描述 from jobs where job_id <> 1
- ----4. 查询空行
- select job_id from jobs where job_desc is null
- ----5. 在查询中使用常量列
- select 编号=job_id,描述=job_desc,'无列名' as 其他 from jobs
- ----6. 查询返回限制的行数
- select top 5 job_id,job_desc from jobs where job_id <> 1
- -----6.1使用percent关键字限制%
- select top 50 percent job_id from jobs where job_id <> 2
- --4.3 查询排序
- select job_id as 编号,job_desc as 描述,min_lvl as 最小值,max_lvl as 最大值 from jobs where min_lvl > 100 order by max_lvl
- ---合并查询 job_desc1 +'1'+ job_desc2 表内容连接查询(字符串 整型和字符串 不能匹配)
- ---查询非重复项
- select max_lvl as 描述 from jobs union
- select max_lvl as 描述 from aaa
- --4.4 在查询中使用函数
- ---4.4.1 字符串函数
- /*
- charindex 用来寻找一个制定的字符串在另一个字符串中的起始位置
- select charindex('accp','my accp course',1)
- 返回:4
- len 返回传递给它的字符串长度
- select len('sql server 课程')
- 返回:13
- lower 把传递给它的字符串转换成小写
- select lower('SQL Server 课程')
- 返回:sql server 课程
- upper 把传递给它的字符串转换成大写
- select upper('sql server 课程')
- 返回:SQL SERVER 课程
- ltrim 消除字符左边的空格
- select ltrim(' sql server 课程 ')
- 返回:'sql server 课程 '(后面的空格保留)
- rtrim 消除字符右边的空格
- select rtrim(' sql server 课程 ')
- 返回:' sql server 课程'(前面的空格保留)
- right 从字符串右边返回指定数目的字符
- select right('买买提.吐尔松',3)
- 返回:吐尔松
- replace 替换一个字符串中的字符
- select replace('莫勒可且.杨兰','兰','蓝')
- 返回:莫勒可且.杨蓝
- stuff 在一个字符串中,删除指定长度的字符,并在该位置插入一个新的字符串
- select stuff('ABCDEFG',3,3,'我的音乐我的世界')
- 返回:A我的音乐我的世界EFG
- */
- select charindex('accp','my accp course',1)
- select len('sql server课程')
- select lower('SQL Server 课程')
- select upper('sql server 课程')
- select ltrim(' sql server 课程 ')
- select rtrim(' sql server 课程 ')
- select right('买买提.吐尔松',3)
- select replace('莫勒可且.杨兰','兰','蓝')
- select stuff('ABCDEFG',2,3,'我的音乐我的世界')
- ---4.4.2 日期函数
- /*
- getdate 取得当前的系统日期
- select getdate()
- 返回:今天的日期
- dateadd 将制定的数值添加到指定的日期部分后的日期
- select dateadd(mm,4,'01/01/09')
- 返回:以当前的日期格式返回01/05/09
- datediff 两个日期之间的制定日期部分的区别
- select datediff(mm,'01/01/09','01/03/10')
- 返回:2
- datename 日期中指定日期部分的字符串形式
- select datename(dw,'01/02/2000')
- 返回:Saturday(星期6)
- datepart 日期中指定日期部分的整数形式
- select datepart(day,'01/15/2000')
- 返回:15
- */
- select getdate()
- select dateadd(mm,4,'01/01/09')
- select datediff(mm,'01/01/09','01/03/10')
- select datediff(yyyy,'07/01/09','01/02/10')
- select datename(dw,getdate())
- select datepart(day,'01/16/2000')
- ---4.4.3 数学函数
- /*
- abs 取数值表达式的绝对值
- select abs(-43)
- 返回:43
- ceiling 取大于或等于指定数值、表达式的最小整数
- select ceiling(43.5)
- 返回:44
- floor 取小于或等于制定表达式的最大整数
- select floor(43.5)
- 返回:43
- power 取数值表达式的幂值(平方)
- select power(5,2)
- 返回:25
- round 将数值表达式的四舍五入为指定精度
- select round(43.543,1)
- 返回:43.500
- sign 对于正整数返回+1,对于负数返回-1,对于0则返回0
- select sign(-43)
- 返回:-1
- sqrt 取浮点表达式的平方根
- select sqrt(9)
- 返回:3.0
- */
- select abs(-43)
- select ceiling(43.5)
- select floor(43.5)
- select power(5,3)
- select round(43.543,1)
- select sign(-43)
- select sqrt(9)
- ---4.4.4 系统函数
- /*
- convert 用来转变数据类型
- select convert(varchar(5),12345)
- 返回:字符串12345
- current_user 返回当前用户的名字
- select current_user
- 返回:你登录的用户名
- datalength 返回用于指定表达式的字节数
- select datalength('中国A联盟')
- 返回:5
- host_name 返回当前用户所登录的计算机名字
- select host_name()
- 返回:你登录的计算机的名字
- system_user 返回当前所登录的用户名称
- select system_user
- 返回:你当前所登录的用户名
- user_name 从给定的用户ID返回用户名
- select user_name(1)
- 返回:从任意数据库中返回"dbo"
- */
- select convert(varchar(5),12345)
- select current_user
- select datalength('中国A联盟')
- select host_name()
- select system_user
- select user_name(1)
- /*
- 第五章 数据查询(2)
- */
- --5.1 模糊查询
- ---5.1.1 使用like进行模糊查询
- select * from jobs where job_desc like 'a%'
- ---查询id不是1的
- select * from jobs where job_id like '[^1]%'
- ---5.1.2 使用between在某个范围内进行查询
- select * from jobs where job_id between 1 and 5
- ---5.1.3 使用In在列举值内进行查询
- select job_id as 编号,job_desc as 描述 from jobs where job_id in (1,2,3,4) order by job_id
- --5.2 SQL Server中的聚合函数
- ---5.2.1 SUM(求和)
- select sum(job_id) from jobs where job_id in (1,2,3,4)
- ---5.2.2 AVG(平均值)
- select avg(min_lvl) from jobs where job_id in (1,2,3,4,5,6)
- ---5.2.3 MAX/MIN(最大值/最小值)
- select max(max_lvl) as 最大值, min(min_lvl) as 最小值 from jobs where min_lvl>10
- ---5.2.4 count(计数)
- select count(*) as 总数 from jobs where job_id <10
- --5.3 分组查询
- ---5.3.1 使用Group by进行分组查询
- select avg(min_lvl) as 平均数 from jobs group by min_lvl
- ---5.3.2 使用having子句进行分组
- /*
- where: 子句从数据源中去掉不符合其搜索条件的数据;
- group by:子句搜索数据行到各个组中,统计函数为各个组计算统计值;
- having: 子句去掉不符合其组搜索条件的各组的搜索行
- */
- select min_lvl from jobs where min_lvl > 20 group by min_lvl having count(min_lvl)>2
- --5.4 多表连接查询
- ---5.4.1 多表连接查询的分类
- ----1.内联接 inner join
- ----2.外联接
- -----1).左外联接:left join 或 left outer join
- -----2).右外联接:right join 或 right outer join
- -----3).完整外联接: full join 或 full outer join
- ----3.交叉联接
- ---5.4.2 内联接查询
- ----1.在where子句中指定联接条件
- select Students.SName,Score.CourseID,Score.Score from Students,Score where Students.SCode = Score.StudentID
- ----2.在From子句中使用join..on
- select S.SName,C.CourseID,C.Score from Students as S inner join Score as C on (S.Scode = C.StudentID)
- ---5.4.3 外联接查询
- ----1. 左外联接查询(Students主表,Score从表)Students主表所有的数据都会被列出 如果从表中没有与主表对应的数据则为NULL
- select S.SName,C.CourseID,C.Score from Students as S left outer join Score as C on S.Scode = C.StudentID
- ----2. 右外联接查询(Titles从表,Publishers从表)Publishers主表所有的数据都会被列出 如果从表中没有与主表对应的数据则为NULL
- select Titles.Title_id,Titles.Title,Publishers.Pub_name from Titles right outer join Publishers on Titles.Pub_id = Publishers.Pub_id
- /*
- 第二学期 SQL Server数据库设计和实现
- 第二章 数据库的实现
- 2.1 T-SQL语句回顾
- 添加数据
- 修改数据
- 查询数据
- 删除数据
- 2.2 使用SQL语句创建和删除数据库
- 2.2.1 创建数据库
- 2.2.2 删除数据库
- 2.3 使用SQL语句创建和删除表
- 2.3.1 创建表
- 2.3.2 删除表
- 2.4 使用SQL语句创建和删除约束
- 2.4.1 添加约束
- 2.4.2 删除约束
- 2.5 使用SQL语句创建登录
- 2.5.1 创建登录账户
- 2.5.2 创建数据库用户
- 2.5.3 向数据库用户授权
- 第三章 T-SQL编程
- 3.1 使用变量
- 3.1.1 局部变量
- 3.1.2 全局变量
- 3.2 输出语句
- 3.3 逻辑控制语句
- 3.3.1 IF-ELSE 条件语句
- 3.3.2 WHILE 循环语句
- 3.3.3 CASE多分支语句
- 3.4 批处理语句
- 第四章 高级查询
- 4.1 简单子查询
- 4.2 IN和NOT IN子查询
- 4.3 EXISTS 和 NOT EXISTS 子查询
- 第五掌 事务、索引和视图
- 5.1 事务
- 5.2 索引
- 5.2.1 什么是索引
- 5.2.2 如何创建索引
- 5.3 视图
- 5.3.2 如何创建视图
- 第六章 存储过程
- 6.2 常用的系统存储过程
- 6.3 用户自定义存储过程
- 6.3.1 创建不带参数的存储过程
- 6.3.2 创建带输出参数的存储过程
- 6.3.3 创建带输出参数的存储过程
- 6.3.4 处理错误信息
- */
- --第二章 数据库的实现
- --2.1 T-SQL语句回顾
- ---添加数据
- insert into jobs(job_id,job_desc,min_lvl,max_lvl) values(1,'bbb',12,12)
- ---修改数据
- update jobs set job_desc = 'bbbbc' where job_id = 1
- ---查询数据
- select job_id,job_desc from jobs where job_id > 4 order by job_desc
- ---删除数据
- delete from jobs where job_id = 2
- --2.2 使用SQL语句创建和删除数据库
- ---2.2.1 创建数据库
- /*
- create database 数据库名
- on [primary]
- (
- <数据文件参数>,[……n]
- [name = 逻辑文件名,]
- filename = 物理文件名
- [,size = 大小]
- [,maxsize = {最大容量|unlimited}]
- [,filegrowth = 增长量]
- [,……n]
- [<文件组参数>]
- filegroup 文件组名 <文件参数>[,……n]
- )
- [log on]
- (
- {<日志文件参数>,[……n]}
- )
- */
- create database stuDB
- on primary
- (
- name = 'stuDB_data',
- filename = 'D:/project/stuDB_data.mdf',
- size = 5mb,
- maxsize = 100mb,
- filegrowth = 15%
- ),
- (
- /*多个数据文件*/
- )
- log on
- (
- name = 'stuDB_log',
- filename = 'D:/project/stuDB_log.ldf',
- size = 2mb,
- filegrowth = 1mb
- ),
- (
- /*多个日志文件*/
- )
- go
- ---2.2.2 删除数据库
- --drop database 数据库名
- drop database stuDB
- --完整T-SQL
- use pubs
- go
- if exists (select * from sysdatabases where name = 'stuDB')
- drop database stuDB
- create database stuDB
- on
- (
- ...
- )
- log on
- (
- ...
- )
- go
- --2.3 使用SQL语句创建和删除表
- ---2.3.1 创建表
- use stuDB
- go
- create table stuInfo
- (
- stuName varchar(20) not null,
- stuNo char(6) not null,
- stuAge int not null,
- stuID numeric(18,0) --身份证号,numeric(18,0)代表18位数字,小数位数为0
- stuSeat smallint identity(1,1),
- stuAddress text
- )
- go
- create table stuMarks
- (
- ExamNo char(7) not null,
- stuNo char(6) not null,
- writtenExam int not null,
- LabExam int not null
- )
- go
- ---2.3.2 删除表
- drop table stuInfo
- --完整T-SQL
- use stuDB
- go
- if exists(select * from sysobjects where name = 'stuInfo')
- drop table stuInfo
- create table stuInfo
- (
- ...
- )
- go
- --2.4 使用SQL语句创建和删除约束
- ---2.4.1 添加约束
- /*
- alter table 表名
- add constraint 约束名 约束类型 具体的约束说明
- */
- ----添加主键约束(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
- ----添加检查约束,要求年龄只能在15-40岁之间
- 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)
- go
- ---2.4.2 删除约束
- /*
- alter table 表名
- drop constraint 约束名
- */
- alter table stuInfo
- drop constraint DF_stuAddress
- --2.5 使用SQL语句创建登录
- ---2.5.1 创建登录账户
- --exec sp_grantlogin 'windos 域名/域账户'
- --exec sp_addlogin '账户名','密码'
- exec sp_grantlogin 'G/cjf'
- exec sp_addlogin 'congcong','2664'
- go
- ---2.5.2 创建数据库用户
- --exec sp_grantdbaccess '登录账户','数据库用户'
- /*--在stuDB数据库中添加两个用户--*/
- use stuDB
- go
- exec sp_grantdbaccess 'G/cjf','stuDB'
- exec sp_grantdbaccess 'zhangsan','zhangsanDBUser'
- --2.5.3 向数据库用户授权
- 授权的语法为:
- grant 权限 [on 表名] to 数据库用户
- use stuDB
- /*--为zhangsanDBUser分配对表stuInfo的select,insert,update权限--*/
- grant select,insert,update on stuInfo to zhangsanDBUser
- /*--为S26301DBUser分配建表的权限--*/
- grant create table to S26301DBUser
- --第三章 T-SQL编程
- --3.1 使用变量
- ---3.1.1 局部变量
- --语法:declare @variable_name DataType
- declare @name varchar(20)
- declare @seat int
- --局部变量赋值两种方法 set语句或select语句
- --语法:set @variable_name = value或 select @variable = value
- ---3.1.2 全局变量
- print @@error --最后一个T-SQL错误的错误号
- print @@identity --最后一次插入的标识值
- print @@language --当前使用的语言的名称
- print @@max_connections --可以创建的同时连接的最大数据
- print @@rowcount --受上一次SQL语句影响的行数
- print @@servername --本地服务器的名称
- print @@servicename --该计算机上的SQL服务的名称
- print @@timeticks --当前计算机上每刻度的微秒数
- print @@trancount --当前连接打开的事务数
- print @@version --SQL Server的版本信息
- --3.2 输出语句
- --print 局部变量或字符串
- --select 局部变量 AS 自定义列名
- --3.3 逻辑控制语句
- ---3.3.1 IF-ELSE 条件语句
- /*语法:
- if(条件)
- 语句或语句块
- else
- 语句或语句块
- */
- ---3.3.2 WHILE 循环语句
- /*语法:
- while (条件)
- 语句或语句块
- [break]
- */
- ---3.3.3 CASE多分支语句
- /*语法:
- case
- when 条件1 then 结果1
- when 条件2 then 结果2
- end
- */
- --3.4 批处理语句
- go
- --第四章 高级查询
- ---4.1 简单子查询
- --实现1:采用T-SQL变量实现,SQL语句如下
- declare @age int
- select @age=stuAge from stuInfo where stuName='cjf'
- select * from stuInfo where stuAge>@age
- go
- --实现2:采用子查询实现,SQL语句如下
- select * from stuInfo
- where stuAge>@age
- ---示例2:
- select * from stuInfo
- where stuAge>(select stuAge from stuInfo where stuName='cjf')
- ---示例3:采用表连接
- select stuName from stuInfo inner join stuMarks
- on stuInfo.stuNo = stuMarks.stuNo where writtenExam=60
- go
- ---示例4:采用子查询
- select stuName from stuInfo
- where stuNo=(select stuNo from stuMarks where writtenExam=60)
- go
- --4.2 IN和NOT IN子查询
- ---示例5: 采用IN子查询
- select stuName from stuInfo
- where stuNo in (select stuNo from stuMarks where writtenExam=60)
- go
- --4.3 EXISTS 和 NOT EXISTS 子查询
- if exists(select * from sysdatabases where name='stuDB')
- drop database stuDB
- create database stuDB
- ...
- if exists(子查询)
- 语句
- --第五掌 事务、索引和视图
- --5.1 事务
- /*Transact-SQL使用下列语句来管理事务
- 开始事务:begin transaction
- 提交事务:commit transaction
- 回滚事务: roolback transaction
- */
- --5.2 索引
- ---5.2.1 什么是索引
- --索引:是SQL Server编排数据的内部方法,它为SQL Server提供一中方法来编排查询数据的路由。
- /*索引三大类
- 唯一索引、主键索引、聚集索引
- */
- ---5.2.2 如何创建索引
- /* 语法
- create [unique][clustered][nonclustered] index index_name
- on table_name (column_name[,column_name].....)
- [with
- fillfactor=x
- ]
- */
- --5.3 视图
- ---5.3.2 如何创建视图
- /*语法:
- create view view_name
- as
- <select 语句>
- */
- --第六章 存储过程
- --6.2 常用的系统存储过程
- /* 系统存储过程名 说明
- sp_databases 列出服务器上的所有数据库
- sp_helpdb 报告有关指定数据库或所有数据库的信息
- sp_renamedb 更改数据库的名称
- sp_tables 返回当前环境下可查询的对象的列表
- sp_columns 返回某个表列的信息
- sp_help 返回某个表的所有信息
- sp_helpconstraint 查看某个表的约束
- sp_helpindex 查看某个表的索引
- sp_stored_procedures 列出当前环境中的所有存储过程
- sp_password 添加或修改登录账户的密码
- sp_helptext 显示默认值、未加密的存储过程、用户自定义的存储过程、触发器或视图的实际文本
- */
- exec sp_databases
- exec sp_helpdb
- exec sp_renamedb
- exec sp_tables
- exec sp_columns
- exec sp_help
- exec sp_helpconstraint
- exec sp_helpindex
- exec sp_stored_procedures
- exec sp_password
- exec sp_helptext
- --扩展
- exec xp_cmdshell 'mkdir d:/bank',no_output--无输出
- --6.3 用户自定义存储过程
- ---6.3.1 创建不带参数的存储过程
- /*语法:
- create procedure 存储过程名
- [{@参数1 数据类型}[= 默认值] [output],
- ....,
- {@参数n 数据类型}[= 默认值] [output]
- ]
- as
- SQL 语句
- */
- if exists(select * from sysobjects where name = 'proc_stu')
- drop procedure proc_stu
- go
- create procedure proc_stu
- as
- ...
- go
- exec proc_stu
- ---6.3.2 创建带输入参数的存储过程
- if exists(select * from sysobjects where name='proc_stu')
- drop procedure proc_stu
- go
- create procedure proc_stu
- @writtenPass int=60,
- @labPass int=60
- as
- ...
- go
- exec proc_stu 60,55
- --exec proc_stu @labPass=55,@writtenPass=60
- --exec proc_stu --都采用默认值:笔试和即使及格线都为60分
- --exec proc_stu 64 --机试采用默认值:笔试及格线64分,机试及格线60分
- --exec proc_stu 60,55 --都不采用默认值
- --错误的调用方式: exec proc_stu ,55 --希望笔试采用默认值,机试及格线55分
- --正确的调用方式: exec proc_stu @labPass=55 --笔试采用默认值,机试及格线55分
- ---6.3.3 创建带输出参数的存储过程
- if exists(select * from sysobjects where name='proc_stu')
- drop procedure proc_stu
- go
- create procedure proc_stu
- @notpassSum int output,--output关键字,否则视为输入参数
- @writtenPass int=60, --默认参数放后
- @labPass int=60 --默认参数放后
- as
- ....
- go
- declare @sum int
- exec proc_stu @sum output,64
- ---6.3.4 处理错误信息
- if(not @writtenPass between 0 and 100) or (not @labPass between 0 and 100)
- begin
- raiserror('错误!',16,1)
- return
- end
- --第七章 触发器
- --7.3 如何创建触发器
- ---7.3.1 创建INSERT、DELETE、UPDATE触发器
- /*语法:
- create trigger Trigger_name
- on table_name
- [with encryption]--加密 可以防止触发器作为SQL Server复制的一部分发布
- for {[delete,insert,update]}
- as
- SQL 语句
- */
【MSSQLServer】 T-SQL完整整理
最新推荐文章于 2024-09-19 19:11:44 发布