use master
go
/*sqlserver中不区分大小写*/
--这里是单行注释
/*这里是多行注释*/
create database test --创建数据库
drop database test --删除数据库
/*以下创建数据库的语法可以不用记，选中CREATE DATABASE,按F1就可查看帮助文档，可
以直接拷贝文档中的demo语句，然后稍微修改下即可*/
CREATE DATABASE testDB
ON
( NAME = testDB_dat,
FILENAME = 'D:\test\testDBdat.mdf', --D盘下要有test文件夹
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = testDB_log,
FILENAME = 'D:\test\testDBlog.ldf', --D盘下要有test文件夹
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB );
GO
drop database testDB
--查看当前系统中数据库信息
select * from sysdatabases
if exists(select * from sysdatabases)
print '有数据'
else
print '无数据'
if exists(select * from sysdatabases where name = 'testDB')
print 'testDB已存在'
else
print 'testDB不存在'
use master
go
if exists(select * from sysdatabases where name = 'testDB')--默认只执行后面一行，所以要用begin end
begin
print '数据库已存在，准备删除'
drop database testDB
end
go
print '创建数据库成功'
create database testDB
use testDB
/*
数据完整性=数据的有效性和正确性(完整性=有效,正确)
完整性约束
1.唯一约束(允许一行数据为空)
2.主键约束(唯一约束 + 非空约束)
3.默认值约束
4.check约束
5.主外键约束
6.非空约束
一个表在不同的关系中既可以是主表又可以是子表
主外键是一对多关系
*/
create table student(
--字段名 数据类型 约束 (一般在此只加非空约束)
stuId int not null,
stuName varchar(20) not null,
stuAge int not null,
stuSex bit not null,
stuTel varchar(13) not null,
stuAddress varchar(30)
)
go
--添加主键约束
alter table student
add constraint PK_stuId primary key (stuId)
--添加唯一约束
alter table student
add constraint UQ_stuName unique (stuName)
--添加check约束
alter table student
add constraint CK_stuAge check (stuAge >=18 and stuAge <= 35)
--添加默认约束
alter table student
add constraint DF_stuAddress default ('地址不详') for stuAddress
go
create table exam(
examId int not null,
stuId int not null, --外键
labExam int not null,
writtenAxam int not null
)
go
--添加主键约束
alter table exam
add constraint PK_examId primary key (examId)
--添加check约束
alter table exam
add constraint CK_labExam check (labExam between 0 and 100)
--添加非空约束
alter table exam
add constraint NN_labExam check (labExam is not null)
--设置非空(alter table 表名 alter column 列名 该列的类型 NOT NULL)
alter table exam
alter column writtenAxam int not null
go
--添加check约束
alter table exam
add constraint CK_writtenAxam check (writtenAxam >= 0 and writtenAxam <= 100 )
--添加外键约束
alter table exam --修改外键表
add constraint FK_stuId
foreign key(stuId) references student(stuId)
--外键表字段主键表字段
go
--删除约束(alter table 表名 drop constraint 约束名)
alter table exam
drop constraint FK_stuId
go
/*哪个是外键表，就给它添加外键约束
给表添加主外键约束的时候遵循3个一致
1.外键表外键与主键表主键数据类型一致(语法规范)
2.长度一致(语法规范)
3.名称一致(一般是项目规范，名称最好是一致)
*/
use testDB
/*
启动停止服务的3中方式
1.控制面板-->管理工具-->服务
2.Sql Server 配置管理器
3.使用命令启动和停止服务 net start mssqlserver (这是启动服务)，net stop mssqlserver(这是停止服务)
*/
/*
打开sqlserver数据库的操作界面也可以使用命令：如下
win +R 输入cmd
然后输入ssms 打开2008及以上版本的sqlserver管理器
然后输入sqlwb 打开2005版本的sqlserver管理器
也可以win +R 后，直接输入ssms或者sqlwb (ssms是2008及以上版本，sqlwb是2005版本)
*/
/*
sqlserver中有些命令，如下：
sqlcmd 类似于sqlserver的控制台，可以删除表，查询表等等操作
osql 使用外部文件执行sql,自己可以写一个bat(批处理)文件来执行sql,以下是教你怎
么写批处理文件
新建一个批处理文件，以bat后缀结尾，文件名可以自己取，我这里就叫做create.bat,在该文
件中编写以下内容
net start mssqlserver
@echo off
echo ==============正在初始化数据库，请稍后.....==============================
osql -E -i student.sql
echo ==============数据库初始化完毕==============================
*/
go
/*
sysobjects表示对象信息表，准确来说本质上是一个视图(该视图存
放的是表，视图，索引，约束等等)每个数据库都有一个此表
*/
select * from sysobjects
go
--查看exam表在数据库中是否存在
if exists(select * from sysobjects where name = 'exam')--默认只执行后面一行，所以要用begin end
/*
begin end 类似于java中的{}花括号，begin类似于java中的开
始花括号{,end类似于java中的结束花括号}
*/
begin
print '数据库表已存在，准备删除'
--删除exam表
drop table exam
end
go
create table exam(
examId int not null,
stuId int not null, --外键
labExam int not null,
writtenAxam int not null
)
print '创建表成功'
go
--添加主键约束
alter table exam
add constraint PK_examId primary key (examId)
--添加check约束
alter table exam
add constraint CK_labExam check (labExam between 0 and 100)
--添加非空约束
alter table exam
add constraint NN_labExam check (labExam is not null)
--设置非空(alter table 表名 alter column 列名 该列的类型 NOT NULL)
alter table exam
alter column writtenAxam int not null
go
--添加check约束
alter table exam
add constraint CK_writtenAxam check (writtenAxam >= 0 and writtenAxam <= 100 )
--添加外键约束
alter table exam --修改外键表
add constraint FK_stuId
foreign key(stuId) references student(stuId)
--外键表字段主键表字段
go
/*
标识列必须是int类型的
标识列不可以编辑
标识列先自增，再验证，最后插入数据
*/
drop table employee
create table employee(
empId int identity(6,2) primary key, --如果种子和自增量都是1的话，可以省略不写
empName varchar(30),
empAge int,
empAddress varchar(30)
--[name] varchar(20)
/*
字段名和表名起名字的时候最好不要起有可能是关键字的名字，如
果怕是关键字或者你非要起这种敏感的名字最好是用[]括起来
*/
)
select * from employee
/*into关键字可以省略，但是最好不要省略，为
了规范，每行写5个字段，这样比较好对齐和匹配
*/
insert into employee
(empName,empAge,empAddress)
values
('李四',21,'北门')
select * from student
insert into student
(stuId,stuName,stuAge,stuSex,stuTel,stuAddress)
values
(9,'王五',28,0,'1378888888',default)
/*
select * into 新表 from 旧表 (相当于把表备份了,只备份
表结构与数据，约束消失)
*/
select * into student2 from student
select * from student2
----select 列名 into 新表 from 旧表 (相当于把表备份了，只需要其中几列)
select stuName,stuSex,stuAddress into student3 from student
select * from student3
----select 列名 as 别名 into 新表 from 旧表 (相当于把表备份了，只需要其中几列,可用as加别名)
select stuName as '姓名',stuSex as 性别,stuAddress 地址 into student4 from student
select * from student4
create table myUsers(
userName varchar(20),
userAddress varchar(30)
)
select * from myUsers
/*
insert into 表名 (列名1, 列名2) select
列名1, 列名2 from 表名
注意3点：
1.列的个数一致
2.对应列的数据类型一致
3.数据类型的长度一致(目标表的长度大于等于源表的长度)
*/
insert into myUsers
(userName, userAddress)
select
stuName, stuAddress
from
student
where
stuAddress = '地址不详'
/*
insert into 表名 (列名1, 列名2)
select 列名1, 列名2
union
select 列名3, 列名4
注意：
1.不支持default关键字
2.当两行数据相同时理解为一行(即union关
键字会自动过滤掉重复行，所以只插入一行数据)
3.使用union all关键字才可以插入多行重
复的记录，所以是插入多行数据
*/
/*
使用union关键字的话，union关键字会自动过
滤掉重复行，所以只插入一行数据
*/
insert into myUsers
(userName, userAddress)
select '二狗子', '深圳' union
select '二狗子', '深圳' union
select '二狗子', '深圳'
/*
使用union all关键字才可以插
入多行重复的记录，所以是插入多行数据
*/
insert into myUsers
(userName, userAddress)
select '二狗子', '江西省赣州市于都县' union all
select '二狗子', '江西省赣州市于都县' union all
select '二狗子', '江西省赣州市于都县'
select * from myUsers
insert into myUsers
(userName, userAddress)
select '令狐冲', '华山' union all
select '杨过', '山谷' union all
select '张无忌', '光明顶' union all
select '韦小宝', 'default'
/*
模糊查询，使用通配符
_下划线代表任意一个字符,该符号只能匹配一个字符
%百分号表示0个或多个字符，即任意长度的字符串
[]表示范围
^[^]表示不属于范围，即范围取反
*/
select * from myUsers where userName like '%[1-2]%'
select * from myUsers where userName not like '%[1-2]%'
select * from myUsers where userAddress like '%[^1-2]'
select * from myUsers where userName like '%[^1-2]%'
select * from myUsers where userName like '%[刘_久_]%'
select * from myUsers
/*
更新操作
update 表名 set 字段名1 = 值
update 表名 set 字段名1 = 值,字段名2 = 值
update 表名 set 字段名1 = 值,字段名2 = 值 where 条件
*/
update myUsers set userName = '赵六哥哥' where userName = '赵1六'
update myUsers set userAddress = userAddress + '人民', userName = userName + '测试'
update myUsers set userAddress = subString(userAddress, 0, 100),
userName = userName + '测试'
where userName like '%二狗子%'
/*
truncate table 表名(删除表中数据,和delete table 表名 是一样的，效率
要比delete高，即删除速度比delete快)
*/
truncate table student4
SELECT * FROM dbo.student4
/*
起别名：2种方式
可以使用as关键字起别名
也可以用=来起别名
*/
SELECT *,stuAddress AS '地址' FROM student
SELECT *,stuAddress AS 地址 FROM student
SELECT stu.*, '地址' = stu.stuAddress FROM student AS stu
SELECT stu.*,'地址' = stu.stuName + '籍贯在' + stu.stuAddress FROM student AS stu
UPDATE student SET stuAddress = '江西省赣州市' WHERE stuId = '9'
--添加列
ALTER TABLE student ADD hobby VARCHAR(30) NULL
ALTER TABLE student ADD schoolName VARCHAR(30) NULL
ALTER TABLE student ADD groupId VARCHAR(5)
--修改列
ALTER TABLE student ALTER COLUMN hobby VARCHAR(30) NULL
ALTER TABLE student ALTER COLUMN schoolName VARCHAR(25) NOT NULL
--
UPDATE student SET schoolName = '城关小学' WHERE stuId = '5'
UPDATE student SET schoolName = '' WHERE stuId = '5'
UPDATE student SET schoolName = NULL WHERE stuId = '1'
UPDATE student SET schoolName = 'null' WHERE stuId = '1'
--
SELECT * FROM student WHERE schoolName is NULL
SELECT * FROM student WHERE schoolName is NOT NULL
--常量列
SELECT *, 学校名称 = schoolName, '城关小学' AS 小学名称 FROM student
SELECT *, 学校名称 = schoolName, '实验小学' AS '校名' FROM student
/*
以下是一个sql的完整写法
SELECT
列名1 AS 别名,
列名2 AS 别名
FROM
表名
WHERE
条件
GROUP BY
分组
HAVING
分组后的筛选
ORDER BY desc / asc
排序
*/
--限制固定行数
SELECT TOP 5 * FROM student
--返回百分之多少行
SELECT TOP 5 PERCENT * FROM student
/*
升降序
order by关键字可以用来排序
默认是asc升序
降序是desc
*/
--降序
SELECT * FROM student
ORDER BY
stuAge DESC
--升序(默认就是升序，所以ASC可以省略不写)
SELECT TOP 3 * FROM student
ORDER BY
stuAge ASC
--
SELECT '我家在江西省赣州市于都县'
SELECT 'hello world!' AS '列名'
SELECT 'ok' AS 列名
/*
字符串函数
*/
/*
CHARINDEX函数,下标从1开始，如果没找
到的话返回0，而在java中下标从0开始，没找到的话返回-1
*/
SELECT CHARINDEX('on', 'my name is jiongjiong', 15)
--LEN函数
SELECT LEN('jack')
--LEN函数是求长度
SELECT stuName, stuAddress, LEN(stuAddress) AS '长度' FROM student
--RTRIM函数和LTRIM函数是去掉左右两边的空格
SELECT * FROM student WHERE RTRIM(LTRIM(stuAddress)) = '江西省赣州市'
SELECT * FROM student WHERE LTRIM(stuAddress) = '江西省赣州市'
UPDATE student SET stuAddress = ' 江西省 赣州市 ' WHERE stuId =19
SELECT * FROM student WHERE REPLACE(RTRIM(LTRIM(stuAddress)),' ','') = '江西省赣州市'
SELECT RIGHT('你好，世界', 3)
SELECT LEFT('你好，世界', 3)
SELECT REPLACE('如果你想哭的时候就倒立吧', '哭的', 'cry')
/*
日期函数
*/
SELECT GETDATE()
SELECT DATEADD(DD,10000,'1989-08-27')
/*
DATEADD()比较有用，比如求一个商品的保质期，从今天开
始计算，保质期是68天，那么用该函数就很有用了
*/
SELECT DATEADD(DD,100, GETDATE())
SELECT DATENAME(DW, GETDATE())
SELECT DATENAME(WK, GETDATE())
SELECT DATEPART(DAY,GETDATE())
--
SELECT CURRENT_USER
SELECT SYSTEM_USER
SELECT HOST_NAME()
--
--SELECT stuAge + stuName FROM student --这样会出错
--使用CONVERT()函数，做类型转换
SELECT CONVERT(VARCHAR(4),stuAge) + stuName FROM student
SELECT * FROM student
ORDER BY stuId ASC,
stuAge ASC
SELECT * FROM student
ORDER BY stuId,stuAge ASC
SELECT * FROM student
ORDER BY
stuAge asc,
stuId DESC
/*
聚合函数
*/
SELECT COUNT(*),
AVG(stuAge),
SUM(stuAge) / COUNT(stuAge),
SUM(stuAge),
MAX(stuAge),
MIN(stuAge)
FROM
student
SELECT * FROM student
/*
需要注意的
NUll不计算在内，如下示例
*/
SELECT COUNT(schoolName),COUNT(hobby), COUNT(*) FROM student
/*
分组和聚合函数同时使用，看下面示例
要显示其他列，必须使用那个列进行分组GROUP BY
*/
SELECT groupId, stuAddress, AVG(stuAge)
FROM
student
GROUP BY
groupId,stuAddress
--
SELECT * FROM student
--
SELECT groupId, AVG(stuAge)
FROM
student
GROUP BY
groupId
HAVING AVG(stuAge) >=25
/*
where 和 having 的区别
where 对表中的原始数据进行的筛选
having 对分组后使用聚合函数计算后的数据进行的筛选，与GROUP BY结合使用
*/
SELECT groupId, AVG(stuAge)
FROM
student
WHERE
stuAge > 20
GROUP BY
groupId
HAVING
AVG(stuAge) >=25
/*
in可以用or代替
*/
--使用or关键字
SELECT * FROM student WHERE stuAge = 18 OR stuAge = 27 OR stuAge = 26
--使用in关键字
SELECT * FROM student WHERE stuAge IN (18, 27, 26)
--使用BETWEEN AND 关键字
SELECT * FROM student WHERE stuAge BETWEEN 18 AND 27
--
SELECT * FROM student WHERE stuAge >= 18 AND stuAge <= 27
/*
如果要把某列显示在使用聚合函数的列
中，那么必须对他进行GROUP BY分组
*/
SELECT * FROM student WHERE NOT stuAge = 18
SELECT * FROM student WHERE stuAge <> 18
SELECT * FROM student WHERE stuAge != 18
SELECT * FROM student WHERE NOT (stuAge = 18)
SELECT * FROM student WHERE (NOT stuAge = 18)
SELECT * FROM student WHERE (stuAge <> 18)
SELECT * FROM student WHERE (stuAge != 18)
/*
联接查询 ：当要同时显示多个表数据的情况下
*/
/*
外联接
左外联接
右外联接
*/
/*
左外联接：改变表出现的顺序有影响，因
为前面出现的是左表
左外联接
第1种说法
第1步显示内联接所有数据
第2步看左表中是否有没匹配的，有的话原样列出，右表部分用NULL补齐
第2种说法
第1步列出左表所有数据
第2步右表与之匹配的显示，其它用NULL补齐
最好是用第一种说法，第二种说法容易被迷惑，因为左表中的同一条数据可能出现多次
*/
SELECT
*
FROM
student AS stu --左表
LEFT JOIN
exam exa --右表 可以省略as关键字
ON
(stu.stuId = exa.stuId)
SELECT
*
FROM
student AS stu --左表
LEFT JOIN
exam exa --右表 可以省略as关键字
ON
(stu.stuId = exa.stuId)
/*
右外联接
第1种说法
第1步显示内联接所有数据
第2步看右表中是否有没匹配的，有的话原样列出，左表部分用NULL补齐
*/
SELECT
*
FROM
student AS stu --左表
LEFT JOIN
exam exa --右表 可以省略as关键字
ON
(stu.stuId = exa.stuId)
--可以调换下顺序
SELECT
*
FROM
exam exa --左表
RIGHT JOIN
student AS stu--右表 可以省略as关键字
ON
(stu.stuId = exa.stuId)
/*
内联接 as可以省略,on后面的小括号()也可以省略，但是不建议省略小括号()
内联接 显示外键表所有数据，并显示主键表与之匹配的数据
内联接：改变表出现顺序没有影响
*/
SELECT
stu.stuAddress,stu.stuName,exa.labExam, exa.writtenAxam, *
FROM
student AS stu
INNER JOIN
exam exa --可以省略as关键字
ON
(stu.stuId = exa.stuId)
/*
内联接的另一种写法，这种写法不推荐使用,2个表没啥问题，3个表或3个表以上就容
易出错，没出错算你运气好
*/
SELECT *
FROM
student AS stu ,exam AS exa
WHERE
stu.stuId = exa.stuId
/*
完全外联接 = 左外联接 + 右外联接
*/
SELECT
*
FROM
student AS stu
FULL JOIN
exam exa --可以省略as关键字
ON
(stu.stuId = exa.stuId)
/*
交叉联接(笛卡尔积)
*/
SELECT
student.*, exam.*
FROM
exam,student
--
SELECT
student.*, exam.*
FROM
student
CROSS JOIN
exam
CROSS JOIN
student2
/*
T-SQL定义变量
声明变量 DECLARE 变量名 数据类型
定义变量使用@符号
给变量赋值使用set关键字
*/
DECLARE @age INT
SET @age = 10
PRINT @age
--SELECT @age
PRINT '@age = ' + CONVERT(VARCHAR(5), @age)
SELECT @age = stuAge FROM student WHERE stuId = 10 --必须确保只有一条记录
PRINT @age
PRINT '@age = ' + CONVERT(VARCHAR(5), @age)
--
DECLARE @stuAge INT
SELECT @stuAge = stuAge FROM student WHERE stuName = '李四'
SELECT * FROM student WHERE stuAge > @stuAge
/*
子查询(一个查询的结果作为另外一个查询的条件
一般子查询要保证返回结果只有一个(即一行,一列)in子查询返回一列多行
子查询从内往外读(写)
当要显示多个表数据时使用联结，只显示一个表数据时使用子查询
)
*/
SELECT * FROM student WHERE
stuAge > (SELECT stuAge FROM student WHERE stuName = '李四')
--
SELECT
*
FROM
student stu
INNER JOIN
exam exa
ON
(stu.stuId = exa.stuId)
WHERE
exa.writtenAxam > 95
--
SELECT
stu.*, exa.*
FROM
student AS stu, exam exa
WHERE
stu.stuId IN (SELECT stuId FROM exam WHERE exa.writtenAxam > 95)
AND
stu.stuId = exa.stuId
--
SELECT
*
FROM
student
WHERE
stuId IN (SELECT stuId FROM exam WHERE writtenAxam > 95)
--
SELECT
*
FROM
student
WHERE
stuId NOT IN (SELECT stuId FROM exam WHERE writtenAxam > 95)
--
IF EXISTS(SELECT * FROM student)
PRINT '有数据'
ELSE
PRINT '无数据'
--
IF EXISTS(SELECT * FROM student WHERE stuId = '199')
PRINT '有数据'
ELSE
PRINT '无数据'
--取反NOT EXISTS
IF NOT EXISTS(SELECT * FROM student WHERE stuId = '199')
PRINT '有数据'
ELSE
PRINT '无数据'
--第一种写法
IF EXISTS(SELECT * FROM exam WHERE writtenAxam >= 80)
UPDATE exam SET writtenAxam = writtenAxam + 2
ELSE
UPDATE exam SET writtenAxam = writtenAxam + 5
--第二种写法
DECLARE @score INT
SET @score = 5
IF EXISTS(SELECT * FROM exam WHERE writtenAxam >= 80)
SET @score = 2
UPDATE exam SET writtenAxam = writtenAxam + @score
--取常量列的2种方式如下
SELECT *, '是否通过' = '是' FROM student
SELECT *, 是否通过 = '是' FROM student
SELECT *, '是' AS '是否通过' FROM student
SELECT *, '是' AS 是否通过 FROM student
SELECT *, '是' '是否通过' FROM student --as关键字也可以省略
--
SELECT
* ,
'等级' =
CASE
WHEN labExam >= 90 THEN '优秀'
WHEN labExam >= 80 AND labExam <= 90 THEN '及格'
ELSE '不及格'
END
FROM exam
--
SELECT *, '计算结果' = 2 / 5 FROM student
SELECT *, '计算结果' = (2 * 1.0) / (5 * 1.0) FROM student
/*
执行顺序
1.where
2.GROUP BY
3.having
4.ORDER BY
*/
/*
where和having都是条件筛选，不过它们有区别
where是对表里原始的数据进行的筛选，一般最先执行
having是对表中数据分组使用聚合函数计算之后(经过加工)
的数据进行的筛选，having在where之后，having与GROUP BY配
合使用
*/
SELECT TOP 3 * FROM student
SELECT TOP 60 PERCENT * FROM student
SELECT * FROM student
/*
DISTINCT关键字是去掉重复值
*/
SELECT DISTINCT stuAddress AS '地址' FROM student
SELECT DISTINCT stuAddress '地址',stuAge AS 年龄 FROM student --相当于这2个字段都要满足重复值才会去重
select stuAddress,COUNT(distinct stuAddress) from student group by stuAddress
/*
左外联接
1.首先列出内联接数据
2.看左表是否还有没匹配的，有的话原样列出，右表部分用NULL补齐
总结:左外联接要显示左表的所有数据
*/
/*
当我们在一个结果集中显示多张表数据的情况下，只能使用联
结(子查询做不到，因为子查询只能显示一张表的数据)
*/
/*
T-SQL : sqlserver特有
PL/SQL :Oracle特有
*/
/*
交叉联接(基本不用)
一般不使用，返回数据的行数=左表行数 * 右表数据行数
*/
/*
复习一些知识
CONVERT()函数,CAST()函数都是数据类型转换函数
CONVERT(数据类型(长度), 变量或列名)
CAST(变量或列名 AS 数据类型(长度))
*/
/*
变量知识复习
变量：
局部变量：
用户自定义的变量，变量名以@开头，可以读写
语法定义：DECLARE @变量名 数据类型(长度)
赋值有2中方式：第一种 set @变量名 = 值
第二种 以查询结果为值
全局变量：系统提供，以@@开头，只读的，也就是说由系
统赋值，我们取值
全局变量由系统定义和维护，我们只能读取，不能修改全
局变量的值
*/
DECLARE @myName VARCHAR(20)
SET @myName = '令狐冲'
PRINT @myName
DECLARE @myAge INT
SET @myAge = 19
SELECT @myAge AS '年龄'
--PRINT '年龄' + @myAge
--这里使用CONVERT()函数
PRINT 'my年龄' + CONVERT(VARCHAR(3), @myAge)
--也可以使用CAST()函数
PRINT '我的年龄' + CAST(@myAge AS VARCHAR(3))
--
SELECT stuName, * FROM student
SELECT stuName FROM student
--
DECLARE @stuName VARCHAR(20)
SET @stuName = 'jack'
SELECT @stuName AS '英文名'
SET @stuName = 'tom'
PRINT @stuName
PRINT '**************'
SELECT @stuName = stuName FROM student WHERE stuId = '33'
SELECT @stuName AS '英文名'
/*
以下看起来像是声明了全部变量，实际上还是局部变量，只不过变量名叫作@testName这个名字了
*/
DECLARE @@testName VARCHAR(20)
SET @@testName = '张无忌'
PRINT @@testName
/*
SELECT在网格窗口显示结果
PRINT在消息窗口显示结果
*/
/*
全局变量
*/
--最后一个T-SQL错误的错误号
SELECT @@ERROR
--最后一次插入的标识值
SELECT @@IDENTITY
--当前使用的语言的名称
SELECT @@LANGUAGE
--sqlserver的版本信息
SELECT @@VERSION
--可以创建的同时连接的最大数目
SELECT @@MAX_CONNECTIONS
--受上一个sql语句影响的行数
SELECT @@ROWCOUNT
--本地服务器的名称
SELECT @@SERVERNAME
--服务名称
SELECT @@SERVICENAME
--当前连接打开的事务数
SELECT @@TRANCOUNT
/*
sql中的流程控制
把{}改成BEGIN AND
*/
--
SELECT * FROM exam
--
SELECT *, '等级' =
CASE
WHEN labExam < 60 THEN 'E'
WHEN labExam > 60 AND labExam <= 69 THEN 'D'
WHEN labExam BETWEEN 70 AND 79 THEN 'C'
WHEN labExam BETWEEN 80 AND 89 THEN 'B'
ELSE 'A'
END
FROM exam
/*
回顾下以前的知识
流程控制，分支判断
多重判断：嵌套if,阶梯式if(可以是区间范围，可以是定值),switch(只能是某个定值)
*/
/*
WHILE循环
*/
DECLARE @testIndex int
SET @testIndex = 0
WHILE(@testIndex < 10)
BEGIN
PRINT '第' + CAST((@testIndex + 1 ) AS VARCHAR(3))+ '次打印hello'
SET @testIndex = @testIndex + 1
END
/*
DDL 数据定义语言，比如建库，建表，存储过程，视图后面加go(批处理)
*/
/*
视图的优点或用途：
1.筛选表中的行
2.防止未经许可的用户访问敏感数据
3.降低数据库的复杂程度(或者说降低sql的复杂度)
4.将多个物理数据库抽象为一个逻辑数据库
视图特点：
视图是为查询服务的，不能通过视图新增、修改、删除数据，同一张
表可以根据需求创建不同的视图
*/
/*
将多个物理数据库抽象为一个逻辑数据库,以下示例
CREATE VIEW 视图名
AS
SELECT 表A.列1,表A.列2,表B.列1,表B.列2 FROM 数据库名1.角色名.表名1 AS 表A
INNER JOIN 数据库名2.角色名.表名2 AS 表B
ON (表A.主键 = 表B.外键)
*/
--以下是数据库名.角色名.表名
SELECT * FROM testDB.dbo.exam
SELECT * FROM testDB.dbo.student
go
--
CREATE VIEW test1 AS SELECT * FROM student WHERE stuSex = 0
GO
SELECT * FROM test1
go
alter VIEW test1 AS SELECT *,stuId AS 'id', stuName AS 'myName' FROM student WHERE stuSex = 0
GO
--更新视图中的数据，对视图中的数据的增加，删除，修改时也会影响原表
UPDATE test1 SET stuName = '风清扬老前辈' WHERE id = 5
IF('a' = 'a')
PRINT '相等'
ELSE
PRINT '不相等'
IF('a' = 'A')
PRINT '相等'
ELSE
PRINT '不相等'
IF(1 = 1)
PRINT '相等'
ELSE
PRINT '不相等'
/*
事务知识复习：
事务什么时候用：同时执行多个insert,update,delete,sql语句时使用
事务是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系
统提交，要么都执行，要么都不执行，事务是一个不可分割的工作逻辑单元
事务必须具备一下4个属性，简称ACID
1.原子性A
2.一致性C
3.隔离性I
4.永久性D
BEGIN TRANSACTION 开始事务
COMMIT TRANSACTION 提交事务
ROLLBACK TRANSACTION 回滚事务
事务分类
1.显示事务：用BEGIN TRANSACTION 关键字明确指定事务的开始和明
确指定提交事务(COMMIT TRANSACTION )和明确指定回滚事务(ROLLBACK TRANSACTION)
2.隐式事务：不需要指定事务的开始即不需要使用BEGIN TRANSACTION关键字开始，但
是需要手工提交事务和手工回滚事务，如果要用隐式事务的话，需要设
置一个开关(即SET IMPLICIT_TRANSACTIONS ON)。你也可以理解为显示事务和隐式事务没什么区
别，不同的地方就是，显示需要用BEGIN TRANSACTION 关键字，隐式不需要用BEGIN TRANSACTION 关键字。
因为它已经是隐式事务了，就没有必要每次都加上BEGIN TRANSACTION 了，但是我们必须在后面加
上提交事务(COMMIT TRANSACTION )和回滚事务(ROLLBACK TRANSACTION)，必须手工去提交事务或手工回滚事务了
3.自动提交事务：这是sqlserver的默认模式，它将每条单独的T-SQL语句视为一个事务
*/
/*
开启隐式事务：SET IMPLICIT_TRANSACTIONS ON
关闭隐式事务：SET IMPLICIT_TRANSACTIONS OFF
*/
/*
还有一个问题：
我们暂时只能在一条conn上做事务，如果要在多条conn上做事务，一般借
助框架或容器，使用JTA(java TRANSACTION API)即java事务接口
*/
SELECT * FROM student
--
SELECT * FROM sys.sysindexes
/*
在sysobjects系统表中存储着数据库的所有对象,每个对象都有一个唯一的id号进行标识.
object_id就是根据对象名称返回该对象的id.反之,object_name是根据对象id返回对象名称.
select object_id(对象名)等同于:
select id from sysobjects where name=对象名
注: 如果参数是一个临时表名,则必须指定所属数据库.你举出的OBJECT_ID('Tempdb..##flowcount') 就属于这
种情况.说明这个临时表是数据库Tempdb的.
select object_name(id号)等同于:
select name from sysobjects where id=id号
*/
--
SELECT * FROM sys.sysindexes WHERE id=object_id('student')
--
SELECT * FROM sysobjects WHERE id = object_id('student')
--
SELECT * FROM sysobjects WHERE name = OBJECT_NAME('2105058535')
/*
索引知识(可参考该网址 http://www.cnblogs.com/soundcode/p/4458974.html )
创建索引语法
CREATE [索引类型] INDEX 索引名称
ON 表名(列名)
WITH FILLFACTOR = 填充因子值0~100
GO
索引分类
1.唯一索引(UNIQUE)：不允许两行具有相同的索引值（创建了唯一约束，系统将自动创建唯一索引）
2.主键索引：主键索引要求主键中的每个值是唯一的，（创建主键自动创建主键索引）
3.聚集索引(CLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，表中只能包含一个聚集索引，主键列默认为聚集索引
4.非聚集索引(NONCLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序不匹配，表中可以有249个非聚集索引
*/
--检测是否已经存在名字为IX_stuName的索引
IF EXISTS(SELECT * FROM sysindexes WHERE name = 'IX_stuName')--检测名字为IX_stuName的索引是否存在，索引存放在sysindexes中
DROP INDEX student.IX_stuName --如果存在则删除
/*
为姓名列创建非聚集索引，填充因子为%(百分比)
*/
--创建索引
CREATE NONCLUSTERED INDEX IX_stuName --创建一个非聚集索引
ON student(stuName) --为student表的stuName字段创建索引
WITH FILLFACTOR = 60 -- 填充因子为60%
GO
STATISTICS_NORECOMPUTE
/*
指定按照某个索引进行查询如下
SELECT 字段1,字段2 FROM 表名 WITH (INDEX = 索引名) WHERE 查询条件
SELECT 字段1,字段2 FROM 表名 WITH (INDEX(索引名)) WHERE 查询条件
指定按'IX_stuName'索引查询
*/
--指定按索引IX_stuName进行查询
SELECT * FROM student WITH(INDEX(IX_stuName))
WHERE stuName = '令狐冲'
--指定按索引IX_stuName进行查询
SELECT * FROM student WITH(INDEX = IX_stuName)
WHERE stuName = '令狐冲'
--
SELECT * FROM student
GO
/*
存储过程知识复习
存储过程在业界以前常常作为衡量程序员水平的标准，大虾们喜欢吹嘘自己写过几千行的存储过程，但是现在作为
java程序员，此功能被弱化，因为java程序讲究的是与具体的数据库相分离，但是作为.net程序员，依然要掌握好
此知识
PROCEDURE 也可以简写成 proc
创建存储过程语法
CREATE PROCEDURE 存储过程名
参数列表
--输入参数 按值传递 (参数也可以给默认值)
--输出参数 按引用传递
@参数名 数据类型 output --有output关键字就是输出参数
@参数名 数据类型 --没有output关键字就是输入参数
@参数名 数据类型 [output] --如果是输出参数的话，在创建存储过程的时候需要加output关键字进行声明,输入参数不需要加output关键字
as
T-SQL编程，定义变量，流程控制，循环等等
GO
调用存储过程
execute 存储过程名称，execute 可以简写成exec
调用存储过程的时候，如果在创建存储过程的时候有输出参数，那么在调用的时候也需要使用output关键字
*/
--
/*
创建无参数的存储过程
*/
--
CREATE PROCEDURE test2
AS
SELECT * FROM student
GO
--调用存储过程
EXECUTE test2
GO
/*
创建带输入参数的存储过程
*/
CREATE PROCEDURE test3
@age INT,
@myScore INT
AS
PRINT '传进来的参数age = ' + CONVERT(VARCHAR(4), @age)
PRINT '传进来的参数myScore = ' + CAST(@myScore AS VARCHAR(4))
SET @age = @age + 10
SET @myScore = @myScore + 5
PRINT '修改后的参数age = ' + CONVERT(VARCHAR(4), @age)
PRINT '修改后的参数myScore = ' + CAST(@myScore AS VARCHAR(4))
GO
/*
调用存储过程
*/
--第一种方式调用(建议使用这种方式调用)
EXECUTE test3 @age = 6,@myScore = 8
--第二种方式调用(不建议用这种方式调用)
EXEC test3 6,8
--
DECLARE @a INT --变量名字也可以叫@age，但是为了和存储过程中的参数名区分，最好不要取和存储过程中的参数名一样的名字
DECLARE @b INT --变量名字也可以叫@myScore，但是为了和存储过程中的参数名区分，最好不要取和存储过程中的参数名一样的名字
SET @a = 6
SET @b = 8
--EXEC test3 @a,@b
PRINT '-----------------我是忧伤的分割线-----------------'
EXEC test3 @age = @a,@myScore = @b
PRINT '----------调用完test3存储过程之后-------------------'
PRINT @a
PRINT @b
GO
/*
测试存储过程的按值和按引用传递
调用之后，可以看出，输入参数是按值传递的，因为调用完之后，值没有改变
调用之后，可以看出，输出参数是按引用传递的，因为调用完之后，值改变了
*/
/*
创建带输入参数,输出参数的存储过程
调用之后，可以看出，输入参数是按值传递的，因为调用完之后，值没有改变
调用之后，可以看出，输出参数是按引用传递的，因为调用完之后，值改变了
*/
ALTER PROCEDURE test4
@age INT, --输入参数按值传递
@myScore INT OUTPUT --输出参数按引用传递
--输入参数 按值传递 (参数也可以给默认值)
--输出参数 按引用传递
AS
PRINT '传进来的参数age = ' + CONVERT(VARCHAR(4), @age)
PRINT '传进来的参数myScore = ' + CAST(@myScore AS VARCHAR(4))
SET @age = @age + 10
SET @myScore = @myScore + 5
PRINT '修改后的参数age = ' + CONVERT(VARCHAR(4), @age)
PRINT '修改后的参数myScore = ' + CAST(@myScore AS VARCHAR(4))
GO
--
DECLARE @userAge INT
DECLARE @userScore INT
SET @userAge = 5
--SET @userScore = 13
EXECUTE test4 @age = @userAge,@myScore = @userScore OUTPUT
PRINT @userAge
PRINT @userScore
--
PRINT '-----------------我是忧伤的分割线-----------------'
EXECUTE test4 @userAge,@userScore OUTPUT
PRINT '-----------------我是忧伤的分割线-----------------'
EXECUTE test4 @userAge,@myScore = @userScore OUTPUT
PRINT '-----------------我是忧伤的分割线-----------------'
/*
调用存储过程的时候需要注意的点
注意点：1.最好使用这种方式 EXECUTE 存储过程名 @输入参数名称1 = 值1,@输出参数名称2 = 值2 output
2.也可以这样调用存储过程 EXECUTE 存储过程名 值1,值2 output
3.也可以这样调用存储过程 EXECUTE 存储过程名 值1,@输出参数名称2 = 值2 output
4.但是不可以这样调用存储过程 EXECUTE 存储过程名 @输入参数名称1 = 值1, 值2 output
5.如果调用存储过程的时候有多个参数的话，如果第一个参数你使用@输入参数名称1 = 值1 这样的形式的话，后面的所
有参数都必须按照@参数名称 = 值这样的形式传递
6.所以调用存储过程要么就这样调用(EXECUTE 存储过程名 @输入参数名称1 = 值1,@输出参数名称2 = 值2 output )，要么就这
样调用(EXECUTE 存储过程名 值1,值2 output),这2种调用方式比较一致和规范，但是还是建
议使用(EXECUTE 存储过程名 @输入参数名称1 = 值1,@输出参数名称2 = 值2 output )这样的方式调用，这种方式比
较好,参数名1=值1,参数名2=值2看起来比较清晰，一一对应，不容易把参数传错。
*/
--EXECUTE test4 @age = @userAge,@userScore OUTPUT --不能这样调用，语法不允许
PRINT '-------------------调用完test4存储过程之后-------------------'
PRINT @userAge
PRINT @userScore
/*
带默认值的存储过程
*/
CREATE PROCEDURE test5
@age INT = 20, --输入参数按值传递,有默认值
@myScore INT OUTPUT --输出参数按引用传递
--输入参数 按值传递 (参数也可以给默认值)
--输出参数 按引用传递
AS
PRINT '传进来的参数age = ' + CONVERT(VARCHAR(4), @age)
PRINT '传进来的参数myScore = ' + CAST(@myScore AS VARCHAR(4))
SET @age = @age + 10
SET @myScore = @myScore + 5
PRINT '修改后的参数age = ' + CONVERT(VARCHAR(4), @age)
PRINT '修改后的参数myScore = ' + CAST(@myScore AS VARCHAR(4))
GO
--
DECLARE @userAge INT
DECLARE @userScore INT
SET @userAge = 5
SET @userScore = 13
--
EXECUTE test5 @myScore = @userScore OUTPUT
PRINT @userAge
PRINT @userScore
PRINT '-----------------我是忧伤的分割线-----------------'
EXECUTE test5 @age = @userAge,@myScore = @userScore OUTPUT
PRINT '-------------------调用完test5存储过程之后-------------------'
PRINT @userAge
PRINT @userScore
--银行账户信息表
CREATE TABLE bank(
cid INT IDENTITY PRIMARY KEY,
customerName VARCHAR(20), --顾客姓名
currentMoney MONEY --当前余额
)
GO
SELECT * FROM bank
--添加约束
alter TABLE bank
ADD CONSTRAINT CK_currentMoney
CHECK(currentMoney >= 1)
GO
--银行账户交易信息表
CREATE TABLE traninfo(
tid INT IDENTITY PRIMARY KEY,
cid INT NOT NULL,
cdate DATETIME NOT NULL,
transType VARCHAR(20),
transMoney MONEY NOT NULL
)
SELECT * FROM traninfo
GO
--在bank表上创建新增触发器
ALTER TRIGGER banktrigger1
ON bank
FOR INSERT
as
PRINT '新增触发器触发了，看看新增的数据'
SELECT * FROM inserted
GO
CREATE TRIGGER banktrigger2
ON bank
FOR UPDATE
AS
SELECT * FROM deleted
PRINT '修改触发器触发了，看看修改的数据'
SELECT * FROM inserted
GO
UPDATE bank SET currentMoney = currentMoney + 150 WHERE cid = 2
INSERT INTO bank(customerName,currentMoney) VALUES ('卓八',700.56)
SELECT * FROM bank
INSERT INTO bank(customerName,currentMoney) VALUES ('张三',1200.55)
INSERT INTO bank(customerName,currentMoney) VALUES ('李四',600.28)
INSERT INTO bank(customerName,currentMoney) VALUES ('王五',800.69)
GO
/*
触发器知识复习
触发器语法
CREATE TRIGGER trigger_name
ON table_name
[WITH encryption] --是否加密
FOR [DELETE,INSERT,UPDATE]
AS
T_SQL语句
GO
触发器中涉及到2张虚拟表(deleted,inserted)
*/
--
ALTER TRIGGER traninfotrigger1
ON traninfo
FOR INSERT
AS
DECLARE @type VARCHAR(20)
DECLARE @money MONEY
DECLARE @cid int
SELECT @cid = cid, @type = transType, @money = transMoney FROM inserted
IF(@type = '存')
BEGIN
UPDATE bank SET currentMoney = currentMoney + @money WHERE cid = @cid
END
ELSE
BEGIN
UPDATE bank SET currentMoney = currentMoney - @money WHERE cid = @cid
END
GO
--
CREATE TRIGGER traninfotrigger2
ON traninfo
FOR INSERT
AS
DECLARE @type VARCHAR(20)
DECLARE @money MONEY
DECLARE @cid int
SELECT @cid = cid, @type = transType, @money = transMoney FROM inserted
IF(@type = '取')
BEGIN
SET @money = -@money
END
UPDATE bank SET currentMoney = currentMoney + @money WHERE cid = @cid
GO
--
INSERT INTO traninfo (cid, cdate, transType, transMoney) VALUES (1, GETDATE(), '取', 200)
SELECT * FROM traninfo
SELECT * FROM bank
INSERT INTO traninfo (cid, cdate, transType, transMoney) VALUES (3, GETDATE(), '存', 800)
GO
/*
注意点：
当SET IDENTITY_INSERT 表名 ON的时候,如果向数据表中插入数据，不能直接写
INSERT INTO 表名2 SELECT * FROM 表名1这样的语句来进行插入数据，要将表名2和表1的列显示的写出才行。
*/
--
CREATE TRIGGER traninfotrigger3
ON traninfo
FOR DELETE
as
IF(NOT EXISTS(SELECT * FROM sysobjects WHERE name = 'traninfoBackup'))
BEGIN
SELECT * INTO traninfoBackup FROM deleted
END
ELSE
BEGIN
--SET IDENTITY_INSERT traninfoBackup ON
INSERT INTO
traninfoBackup(cid, cdate, transType, transMoney)
SELECT
cid, cdate, transType, transMoney
FROM
deleted
--INSERT INTO traninfoBackup SELECT * FROM traninfo
END
GO
SELECT * FROM traninfo
--SET IDENTITY_INSERT traninfoBackup ON
DELETE FROM traninfo WHERE tid <= 3
DELETE FROM traninfo WHERE tid = 27
SELECT * FROM traninfoBackup
--SET IDENTITY_INSERT traninfoBackup OFF
INSERT INTO traninfoBackup (cid, cdate, transType, transMoney) VALUES (1, GETDATE(), '取', 200)
GO
--
CREATE TRIGGER bank5
ON bank
FOR UPDATE
as
DECLARE @oldMoney MONEY
DECLARE @newMoney MONEY
DECLARE @tranMoney MONEY
SELECT @oldMoney = currentMoney FROM deleted
SELECT @newMoney = currentMoney FROM inserted
SET @tranMoney = @oldMoney - @newMoney
PRINT '******************************************************************'
PRINT 'oldMoney = ' + CONVERT(VARCHAR(20), @oldMoney) + '' +
'newMoney = ' + CONVERT(VARCHAR(20), @newMoney) + '' +
'交易金额 = ' + CONVERT(VARCHAR(20), @tranMoney)
PRINT '******************************************************************'
IF(@tranMoney >= 20000 OR @tranMoney <= -20000)
BEGIN
PRINT '发生错误，交易金额最多两万'
SELECT * FROM bank
ROLLBACK TRANSACTION --回滚
END
GO
INSERT INTO traninfo (cid, cdate, transType, transMoney) VALUES (1, GETDATE(), '存', 13000)
SELECT * FROM bank
GO
/*
列级触发器
UPDATE(列名) 判断该列是否修改
*/
CREATE TRIGGER tri_update_traninfo
ON traninfo
FOR UPDATE
AS
IF UPDATE(cdate)
BEGIN
PRINT '交易失败'
RAISERROR('安全警告:交易日期不能修改，由系统自动产生', 16, 1)
SELECT * FROM traninfo
ROLLBACK TRANSACTION
END
GO
UPDATE traninfo SET cdate = GETDATE()
SELECT * FROM traninfo
-- TRUNCATE TABLE traninfo
/*
TRUNCATE TABLE 表名 虽然也能删除表中的数据，但是TRUNCATE TABLE是DDL(数据定义语言，所以不能带where条件)，如果要根据条件来删
除表中的某些数据的话必须要使用delete(DML 数据操作语言)
*/
