SQLServer学习笔记
（1）在这里编写SQL命令大小写和数据库排序规则有关
（2）SQL SerVer中常用数据类型
char nchar varchar nvarchar int bool decimal float money
（3）加不加N和数据库排序规则有关
1、创建数据库
--在这里编写SQl命令大小写和数据库排序规则有关
--SQL SerVer中常用数据类型
/*
char nchar varchar nvarchar int bool decimal float money
*/
--加不加N和数据库排序规则有关
--1.创建数据库
CREATE DATABASE MyData;
--2.删除数据库
DROP DATABASE MyData;
--3.创建数据库时候设置一些参数选项
CREATE DATABASE MyData ON PRIMARY ( NAME = N'MyData', --主数据库文件的逻辑名称
FILENAME = N'E:\SQLSERVER\MyData.mdf', --主数据库的实际保存路径
SIZE=5MB, --数据库文件初始大小
MAXSIZE= 150MB, --数据库文件的最大尺寸
FILEGROWTH=20%--数据库的增长量 ) log ON ( NAME='MyData_log', --日志文件的逻辑名称
filename='E:\SQLSERVER\MyData_log.ldf', --日志文件的实际保存路径
size=5mb, --日志文件的初始大小
filegrowth=5mb--日志文件库的增长量
);
--仅2017经过测试
--查看MyData排序规则
SELECT Databasepropertyex('MyData', 'Collation');
--修改MyData排序规则为Chinese_PRC_CI_AS
ALTER DATABASE MyData COLLATE Chinese_PRC_CI_AS;
2、创建表
--创建表的SQL语句
--切换空间到需要创建表的数据库
USE MyData;
--create table 表名(
--字段名数据类型约束（可有可无）,
--字段名数据类型约束（可有可无）,
--)
CREATE TABLE Department
(
--identity(初始值,增长量) 自动增长
--identity只可存储数值的时候使用
--字符串类自动增长需要使用存储过程
--字段名数据类型约束（可有可无）
--设置自动增长，在插入数据的时候一定要写入字段，不然插入会失败
AutoID INT IDENTITY(1, 1) PRIMARY KEY,
DepartmentName NVARCHAR(25) NOT NULL,
);
3、删除表
--删除表
DROP TABLE Department;
4、修改表
--修改表
USE MyData;
--DROP TABLE student;
CREATE TABLE student
(
sno INT IDENTITY(20190001, 1) NOT NULL,
sname NVARCHAR(25) NOT NULL,
age INT NOT NULL,
sgender NCHAR(1) NOT NULL,
sgrade NVARCHAR(10) NOT NULL,
sclass NVARCHAR(15) NOT NULL,
saddress NVARCHAR(100),
sbirthday DATE
);
--添加字段ALTER TABLE 表名 ADD 字段名 数据类型 约束
ALTER TABLE student
ADD id INT NOT NULL;
ALTER TABLE student
ADD email VARCHAR(20);
--删除字段ALTER TABLE 表名 DROP 字段名
ALTER TABLE student
DROP COLUMN id;
--修改字段ALTER TABLE 表名 ALTER COLUMN 字段名 数据类型
ALTER TABLE student
ALTER COLUMN sgrade VARCHAR(6);
5、添加约束
--添加约束的时候可以用逗号隔开创建多个约束
--添加约束ALTER TABLE 表名ADD CONSTRAINT 约束名关键字(非空约束除外)
--非空约束(与修改字段一致)
ALTER TABLE student
ALTER COLUMN saddress NVARCHAR(100) NOT NULL;
--唯一约束UNIQUE
ALTER TABLE student
ADD CONSTRAINT uq_student_email UNIQUE(email);
--检查约束CHECK
ALTER TABLE student
ADD CONSTRAINT ck_student_age CHECK(age>1 AND age<120);
--默认约束DEFAULT
ALTER TABLE student
ADD CONSTRAINT de_student_sgender DEFAULT('男') FOR sgender;
ALTER TABLE student
ADD CONSTRAINT de_student_saddress DEFAULT('星界') FOR saddress;
--添加主键PRIMARY KEY
ALTER TABLE student
ADD CONSTRAINT pk_student_sno PRIMARY KEY(sno);
--外键约束
--ALTER TABLE 外键表名 ADD CONSTRAINT 约束名FOREIGN KEY(外键表字段) REFERENCES 主键表(字段名)
--ALTER TABLE City ADD CONSTRAINT FK_Province_City FOREIGN KEY(PID) REFERENCES Province(PID)
--删除约束
--ALTER TABLE 表名drop 约束名，约束名,....约束名n
6、基本增删改查
插入
--SQL insert(插入语句)
--INSERT INTO 表名(字段，字段，.....字段n)VALUES(值,值,...值n);
--省略字段名的时候值一定要和数据表中的字段顺序对应
--INSERT INTO 表名VALUES(值,值...值n);
INSERT INTO [student]
VALUES ('裁决',18,'男','2018级','18A','宗玄大陆','2018-09-01','455518341@qq.com');
INSERT INTO student(sname,age,sgender,sgrade,sclass,saddress,sbirthday,email)
VALUES ('战帝',18,'男','2018级','18A','宗玄大陆','1997-05-16','2551024253@qq.com');
INSERT INTO student(sname,age,sgender,sgrade,sclass)
VALUES ('研华',18,'女','2018级','18A');
INSERT INTO student(sname,age,sgender,sgrade,sclass,saddress,sbirthday,email)
VALUES ('虚空大帝',18,'男','2018级','18B','大陆','1997-05-16','44554545154@qq.com');
INSERT INTO student(sname,age,sgender,sgrade,sclass,saddress,sbirthday,email)
VALUES ('青木神帝',18,'男','2018级','18B','第十界-十三洲','1997-05-16','435518341@qq.com');
--多记录插入
INSERT INTO [student]
VALUES ('裁决',18,'男','2018级','9A','宗玄大陆','2000-09-01','555518441@qq.com'),
('杨凯',18,'男','2019级','19A','宗玄大陆','2000-09-01','455518441@qq.com');
INSERT INTO student
VALUES ('青帝',18,'男','2018级','18B','第十界-十三洲','1997-05-16','465518441@qq.com');
INSERT INTO student
VALUES ('慕芷璃',18,'女','2018级','18B','第十界-十三洲','1997-05-16','4655141@qq.com');
INSERT INTO student
VALUES ('苗婵',18,'女','2018级','18B','第十界-十三洲','1997-05-16','46441@qq.com');
INSERT INTO student
VALUES ('青木神帝',18,'男','2018级','18B','第十界-十三洲','1997-05-16','43556341@qq.com');
INSERT INTO student
VALUES ('青木神帝',19,'男','2018级','18B','第十界-十三洲','1997-05-16','43841@qq.com');
INSERT INTO student
VALUES ('青木神帝',13,'男','2018级','18B','第十界-十三洲','1997-05-16','435841@qq.com');
INSERT INTO student
VALUES ('青木神帝',100,'男','2018级','18B','第十界-十三洲','1997-05-16','435541@qq.com');
删除
--truncate table 表名;(初始化表,DELETE只是删除数据，不可以初始化表)
--不加入WHERE条件表示干掉所有数据，慎用
--DELETE FROM 表名 WHERE 条件(字段)1=值;
--DELETE FROM 表名 WHERE 条件(字段)1<值;
--DELETE FROM 表名 条件(字段)1=值 OR 条件(字段)2=值;
--DELETE FROM 表名 条件(字段)1=值 AND 条件(字段)2=值;
SELECT*
FROM student;
DELETE FROM student
WHERE sno = 20190011;
DELETE FROM student
WHERE sno = 20190012
OR sno = 20190013;
DELETE FROM student
WHERE sno = 201900014
AND sname = '青木神帝';
更新
--UPDATE 表名SET 字段=值,字段=值,....字段n=值n;
--UPDATE 表名SET 字段=值,字段=值,....字段n=值n WHERE 条件(字段)1=值;
--UPDATE 表名SET 字段=值,字段=值,....字段n=值n WHERE 条件(字段)1=值OR 条件(字段)2=值;
--UPDATE 表名SET 字段=值,字段=值,....字段n=值n WHERE 条件(字段)1=值AND 条件(字段)2=值;
--如果省略WHERE后面的字段则表示，修改所有字段的值,所以WHERE后面的条件需要谨慎
--下面的语句就表示数据表值sbrithday字段的值都改为'1997-05-18'
UPDATE student
SET sbirthday = '1997-05-18';
SELECT*
FROM student;
--更新条件需要谨慎，最好字段唯一，只可以更改一条记录,
--多条记录更新可以使用以上方法，但是也许谨慎，需做一些判断，如下
UPDATE student
SET sbirthday = '1997-05-16'
WHERE sno = 20190002;
UPDATE student
SET saddress = '玄黄大陆'
WHERE sno = 20190003;
SELECT*
FROM student;
UPDATE student
SET saddress = '虚空大陆'
WHERE sname = '虚空大帝'
AND sclass = '18B';
UPDATE student
SET saddress = '星界'
WHERE sname = '虚空大帝'
OR sno = 20190004;
查询
--SELECT*FROM 表名;
--SELECT*FROM 表名WHERE 条件(字段)1=值;
--SELECT*FROM 表名WHERE 条件(字段)1=值OR 条件(字段)2=值;
--SELECT*FROM 表名WHERE 条件(字段)1=值AND 条件(字段)2=值;
--SELECT 字段,字段，...字段n FROM 表名WHERE 条件(字段)1=值OR 条件(字段)2=值;
--SELECT 字段,字段，...字段n FROM 表名WHERE 条件(字段)1=值AND 条件(字段)2=值;
--SELECT 字段,字段，...字段n FROM 表名WHERE 条件(字段)1=值;
SELECT*
FROM student;
SELECT*
FROM student
WHERE sno = 20190006;
SELECT*
FROM student
WHERE sno = 20190007
OR sno = 20190010;
SELECT*
FROM student
WHERE sno = 20190007
OR sname = '青木神帝';
SELECT*
FROM student
WHERE sno = 20190005
AND sname = '青木神帝';
SELECT sno,
sname,
age,
sbirthday
FROM student
WHERE sno = 20190006;
--SELECT查询语句别名
--SELECT 字段名 AS 别名,字段 AS 别名,...字段n AS 别名n FROM 表名(或者表名WHERE 条件表达式)
--SELECT 别名=字段,别名=字段,...别n=字段n FROM 表名(或者表名WHERE 条件表达式)
--SELECT 别名=字段,别名=字段,...别n=字段n,别名=值FROM 表名
--DISTINCT对查询出来的结果去重复
--SELECT DISTINCT 字段,字段,...字段n FROM student
SELECT DISTINCT sname,
age,
sgender
FROM student
--排序(DESC(降),ASC(升))
--ORDER BY 默认升序排序
--ORDER BY语句必须在整个sql语句最后
--根据多列进行排序
--SELECT 字段,字段,...字段n FROM 表名ORDER BY 字段排序方式
SELECT*
FROM student
ORDER BY sbirthday;
SELECT*
FROM student
ORDER BY saddress DESC,
sclass DESC;
--TOP 选择前n条记录
--百分比向上取整数条
--若果TOP后面不是数字(数字或百分比(不加百分号))需要用括号把表达式括起来
--SELECT TOP 表达式FROM 表名表达式
SELECT TOP 3 *
FROM student;
SELECT TOP 7 *
FROM student
ORDER BY sbirthday;
--DELETE FROM student WHERE sno in(20190007,20190009,20190001);
--UPDATE student SET saddress='十三洲' WHERE sno in(20190007,20190009,20190001);
--模糊查询
--通配符:_（任意字符）% [] ^
--SELECT 字段,字段,...字段n FROM 表名WHERE sname like '(通配符表达式)'
--%值(以这个值开头) 值%(以这个值结尾的) %值%(包含这个值的)
--如下
--通配符表达式很多，可以根据需要的数据去修改
SELECT*
FROM student
WHERE saddress LIKE '%玄%';
SELECT*
FROM student
WHERE saddress LIKE '玄%';
SELECT*
FROM student
WHERE saddress LIKE '%界';
SELECT*
FROM student
WHERE saddress LIKE '_玄__';
SELECT*
FROM student
WHERE sname LIKE '%帝';
SELECT*
FROM student
WHERE sname LIKE '青[^0-9][^0-9]帝';
SELECT*
FROM student
WHERE sname LIKE '青[0-9][0-9]帝';
SELECT*
FROM student
7、常用聚合函数
--常用的个聚合函数(聚合函数不统计空值，sum认为是)
--SELECT 关键字(字段) FROM 表名
--count 统计
SELECT Count(sname) AS 记录条数
FROM student;
--sum 求和
SELECT Sum(age) AS 年龄总和
FROM student;
--max 最大值
SELECT Max(sbirthday) AS 最大日期
FROM student;
SELECT Max(age) AS 最大年龄
FROM student;
--min 最小值
SELECT Min(sbirthday) AS 最大日期
FROM student;
--avg 平均值
SELECT 平均年龄=(SELECT Sum(age) AS 年龄总和
FROM student) / (SELECT Count(sname) AS 记录条数
FROM student);
SELECT Avg(age)
FROM student;
SELECT*
FROM student;
--between 值AND 值
--SELECT 字段FROM 表名WHERE between 值AND 值条件表达
SELECT sname
FROM student
WHERE age BETWEEN 18 AND 24;
--in
--字段in(值..值,..值n)
SELECT*
FROM student
WHERE sno IN( 20190007, 20190009, 20190001 );
开窗函数
---------------------------------------开窗函数---------------------------------
-----------------测试数据---------------
--DROP TABLE T_Person;
--DROP TABLE T_Person_Two;
CREATE TABLE T_Person
(
ID INT IDENTITY(1, 1) PRIMARY KEY,
FName NVARCHAR(20),
FCity NVARCHAR(20),
FAge INT,
FSalary INT,
CreateTime DATETIME
);
CREATE TABLE T_Person_Two
(
ID INT IDENTITY(1, 1) PRIMARY KEY,
FName NVARCHAR(20),
FCity NVARCHAR(20),
FAge INT,
FSalary INT,
CreateTime DATETIME
);
INSERT INTO T_Person
(FName,FCity,FAge,FSalary,CreateTime)
VALUES
('Tom','BeiJing',20,3000,Getdate()),
('Tim','ChengDu',21,4000,Getdate()),
('Jim','BeiJing',22,3500,Getdate()),
('Lily','London',21,2000,Getdate()),
('John','NewYork',22,1000,Getdate()),
('YaoMing','BeiJing',20,3000,Getdate()),
('Swing','London',22,2000,Getdate()),
('Guo','NewYork',20,2800,Getdate()),
('YuQian','BeiJing',24,8000,Getdate()),
('Ketty','London',25,8500,Getdate()),
('Kitty','ChengDu',25,3000,Getdate()),
('Merry','BeiJing',23,3500,Getdate()),
('Smith','ChengDu',30,3000,Getdate()),
('Bill','BeiJing',25,2000,Getdate()),
('Jerry','NewYork',24,3300,Getdate());
INSERT INTO T_Person_Two(FName,FCity,FAge,FSalary,CreateTime)
VALUES
('Tom','BeiJing',20,3000,Getdate()),
('Tim','ChengDu',21,4000,Getdate()),
('Jim','BeiJing',22,3500,Getdate()),
('Lily','London',21,2000,Getdate()),
('John','NewYork',22,1000,Getdate()),
('YaoMing','BeiJing',20,3000,Getdate()),
('Swing','London',22,2000,Getdate()),(
'Guo','NewYork',20,2800,Getdate()),
('YuQian','BeiJing',24,8000,Getdate()),
('Ketty','London',25,8500,Getdate()),
('Kitty','ChengDu',25,3000,Getdate()),
('Merry','BeiJing',23,3500,Getdate()),
('Smith','ChengDu',30,3000,Getdate()),
('Bill','BeiJing',25,2000,Getdate()),
('Jerry','NewYork',24,3300,Getdate());
------------------------------------------案例------------------------
SELECT *
FROM t_person
----------工资小于5000的人数-----------------------
SELECT fname,
fcity,
fsalary,
Count(FCity)
OVER() AS '工资小于5000员工数'
FROM t_person
WHERE fsalary < 5000
--所在城市人数
SELECT*
FROM (SELECT fname,
fcity,
fage,
fsalary,
Count(FCity)
OVER(
partition BY fcity) '所在城市人数'
FROM t_person) AS A
WHERE A.所在城市人数 = 6
---------------------------合并两个表数据取最新值------------------------------
SELECT*
FROM (SELECT *,
Row_number()
OVER (
PARTITION BY FName
ORDER BY CreateTime DESC ) AS RID
FROM (SELECT*,
'表1' AS BH
FROM T_Person
UNION ALL
SELECT*,
'表2' AS BH
FROM T_Person_Two) AS C)AS C
WHERE c.RID = 1
8、CASE
---------------CASE--------
USE MyData;
CREATE TABLE [user]
(
uId INT IDENTITY(1, 1) PRIMARY KEY,
name VARCHAR(50),
level INT
);
--DELETE FROM [user] WHERE uId in(1,2,3,4,5)
INSERT INTO [user]
VALUES '犀利哥',1)，('小月月',2)，('小媛媛',3)，('裁决',1),('研华',1),('研华',4);
CREATE TABLE [score]
(
uId INT IDENTITY(1, 1) PRIMARY KEY,
math INT,
english INT,
chinese INT
);
INSERT INTO [score]
VALUES (147,136,133),(117,95,133),(127,78,129);
--CASE相当于C# SWITCH
SSELECT *,
头衔=CASE
WHEN [level] = 1 THEN '菜鸟'
WHEN [level] = 2 THEN '老鸟'
WHEN [level] = 3 THEN '大师'
ELSE '骨灰级'
END
FROM [user];
SELECT *,
CASE [level]
WHEN 1 THEN '菜鸟'
WHEN 2 THEN '老鸟'
WHEN 3 THEN '大师'
ELSE '骨灰级'
END AS 头衔
FROM [user];
SELECT *,
等级=CASE
WHEN english >= 120 THEN '优秀'
WHEN english = 90 THEN '及格'
WHEN english < 90 THEN '不及格'
ELSE '良好'
END
FROM [score];
9、多表查询
内连接返回的是关键信息相等的两个以上表记录，不分主从表，只要两个表中都没有关键信息，则查询返回为控制；
左连接返回的是关键信息相等的两个以上表记录，左边的为主表，右边的为从表，主表中关键信息在从表中没有，怎从表以空值填充；
右连接返回的是关键信息相等的两以上个表记录，和左连接一致，主从表位置相反，右边为主表。
内连接操作案例
数据
USE MyData;
CREATE TABLE Province
(
PID CHAR(2) PRIMARY KEY,
PName NVARCHAR(15) NOT NULL
);
CREATE TABLE City
(
CID CHAR(4) PRIMARY KEY,
CName NVARCHAR(20) NOT NULL,
PID CHAR(2) NOT NULL
);
CREATE TABLE Area
(
AID CHAR(6) PRIMARY KEY,
AName NVARCHAR(25) NOT NULL,
CID CHAR(4) NOT NULL
);
ALTER TABLE City
ADD CONSTRAINT FK_Province_City FOREIGN KEY(PID) REFERENCES Province(PID);
ALTER TABLE Area
ADD CONSTRAINT FK_City_Area FOREIGN KEY(CID) REFERENCES City(CID);
--省
INSERT INTO Province(PName,PID)VALUES '云南省','53');
--市
INSERT INTO City (CName,CID,PID)VALUES
('昆明市','5301','53'),('曲靖市','5303','53'),('玉溪市','5304','53'),('保山市','5305','53')
,('昭通市','5306','53'),('丽江市','5307','53'),('普洱市','5308','53'),('临沧市','5309','53')
,('楚雄彝族自治州','5323','53'),('红河哈尼族彝族自治州','5325','53')
,('文山壮族苗族自治州','5326','53'),('西双版纳傣族自治州','5328','53')
,('大理白族自治州','5329','53'),('德宏傣族景颇族自治州','5331','53')
,('怒江傈僳族自治州','5333','53'),('迪庆藏族自治州','5334','53');
--县
INSERT INTO Area(AName,AID,CID)VALUES
('五华区','530102','5301'),('盘龙区','530103','5301'),('官渡区','530111','5301'),('西山区','530112','5301')
,('东川区','530113','5301'),('呈贡区','530114','5301'),('晋宁区','530115','5301'),('富民县','530124','5301')
,('宜良县','530125','5301'),('石林彝族自治县','530126','5301'),('嵩明县','530127','5301')
,('禄劝彝族苗族自治县','530128','5301'),('寻甸回族彝族自治县','530129','5301')
,('安宁市','530181','5301'),('麒麟区','530302','5303'),('沾益区','530303','5303')
,('马龙区','530304','5303'),('陆良县','530322','5303'),('师宗县','530323','5303')
,('罗平县','530324','5303'),('富源县','530325','5303'),('会泽县','530326','5303')
,('宣威市','530381','5303'),('红塔区','530402','5304'),('江川区','530403','5304')
,('澄江县','530422','5304'),('通海县','530423','5304'),('华宁县','530424','5304')
,('易门县','530425','5304'),('峨山彝族自治县','530426','5304'),('新平彝族傣族自治县','530427','5304')
,('元江哈尼族彝族傣族自治县','530428','5304'),('隆阳区','530502','5305'),('施甸县','530521','5305')
,('龙陵县','530523','5305'),('昌宁县','530524','5305'),('腾冲市','530581','5305'),('昭阳区','530602','5306')
,('鲁甸县','530621','5306'),('巧家县','530622','5306'),('盐津县','530623','5306'),('大关县','530624','5306')
,('永善县','530625','5306'),('绥江县','530626','5306'),('镇雄县','530627','5306'),('彝良县','530628','5306')
,('威信县','530629','5306'),('水富市','530681','5306'),('古城区','530702','5307')
,('玉龙纳西族自治县','530721','5307'),('永胜县','530722','5307'),('华坪县','530723','5307')
,('宁蒗彝族自治县','530724','5307'),('思茅区','530802','5308'),('宁洱哈尼族彝族自治县','530821','5308')
,('墨江哈尼族自治县','530822','5308'),('景东彝族自治县','530823','5308')
,('景谷傣族彝族自治县','530824','5308'),('镇沅彝族哈尼族拉祜族自治县','530825','5308')
,('江城哈尼族彝族自治县','530826','5308'),('孟连傣族拉祜族佤族自治县','530827','5308')
,('澜沧拉祜族自治县','530828','5308'),('西盟佤族自治县','530829','5308'),('临翔区','530902','5309')
,('凤庆县','530921','5309'),('云县','530922','5309'),('永德县','530923','5309'),('镇康县','530924','5309')
,('双江拉祜族佤族布朗族傣族自治县','530925','5309'),('耿马傣族佤族自治县','530926','5309')
,('沧源佤族自治县','530927','5309'),('楚雄市','532301','5323'),('双柏县','532322','5323')
,('牟定县','532323','5323'),('南华县','532324','5323'),('姚安县','532325','5323'),('大姚县','532326','5323')
,('永仁县','532327','5323'),('元谋县','532328','5323'),('武定县','532329','5323'),('禄丰县','532331','5323')
,('个旧市','532501','5325'),('开远市','532502','5325'),('蒙自市','532503','5325'),('弥勒市','532504','5325')
,('屏边苗族自治县','532523','5325'),('建水县','532524','5325'),('石屏县','532525','5325')
,('泸西县','532527','5325'),('元阳县','532528','5325'),('红河县','532529','5325')
,('金平苗族瑶族傣族自治县','532530','5325'),('绿春县','532531','5325'),('河口瑶族自治县','532532','5325')
,('文山市','532601','5326'),('砚山县','532622','5326'),('西畴县','532623','5326')
,('麻栗坡县','532624','5326'),('马关县','532625','5326'),('丘北县','532626','5326')
,('广南县','532627','5326'),('富宁县','532628','5326'),('景洪市','532801','5328')
,('勐海县','532822','5328'),('勐腊县','532823','5328'),('大理市','532901','5329')
,('漾濞彝族自治县','532922','5329'),('祥云县','532923','5329'),('宾川县','532924','5329')
,('弥渡县','532925','5329'),('南涧彝族自治县','532926','5329'),('巍山彝族回族自治县','532927','5329')
,('永平县','532928','5329'),('云龙县','532929','5329'),('洱源县','532930','5329')
,('剑川县','532931','5329'),('鹤庆县','532932','5329'),('瑞丽市','533102','5331'),('芒市','533103','5331')
,('梁河县','533122','5331'),('盈江县','533123','5331'),('陇川县','533124','5331')
,('泸水市','533301','5333'),('福贡县','533323','5333'),('贡山独龙族怒族自治县','533324','5333')
,('兰坪白族普米族自治县','533325','5333'),('香格里拉市','533401','5334'),('德钦县','533422','5334')
,('维西傈僳族自治县','533423','5334');
案例
--内连接
--显示全部
SELECT *
FROM Province
INNER JOIN City
ON Province.PID = City.PID
INNER JOIN Area
ON Area.CID = City.CID;
--显示需要的字段
SELECT PName,
CName
FROM Province
INNER JOIN City
ON Province.PID = City.PID;
--防止冲突,查询的时候可以写别名
SELECT PName,
CName
FROM Province AS pn
INNER JOIN City AS ct
ON pn.PID = ct.PID;
--列名重名时使用表名.列名指定是哪一张表
SELECT pn.PName,
ct.CName
FROM Province AS pn
INNER JOIN City AS ct
ON pn.PID = ct.PID;
--多表连接条件查询
/*
SELECT
字段,字段,...字段n(防止字段冲突一般需要这么写：表名.字段名
FROM
(关联表名inner join 表名on 表名.字段名=关联表名.字段名inner join 表名on 表名.字段名=关联表名.字段名)
WHERE 条件语句;
*/
SELECT pname AS 省,
cname AS 市区州,
aname AS 县市
FROM (city
INNER JOIN province
ON province.pid = city.pid
INNER JOIN area
ON city.cid = area.cid )
WHERE city.cid = '5308';
SELECT pname AS 省,
cname AS 市区州,
aname AS 县市
FROM (city
INNER JOIN province
ON province.pid = city.pid
INNER JOIN area
ON city.cid = area.cid )
WHERE area.AName = '景谷傣族彝族自治县';
SELECT province.pid,
pname AS 省,
city.cid,
cname AS 市区州,
area.aid,
aname AS 县市
FROM (city
INNER JOIN province
ON province.pid = city.pid
INNER JOIN area
ON city.cid = area.cid )
WHERE area.aid = '530824';
--查询第N到M条记录
/*id最好为自动编号，方便查询
SELECT T.* FROM
(
SELECT *,row_number() over(ORDER BY id) AS rn FROM tablename
) T
WHERE rn between N AND M
*/
USE MyData;
SELECT*
FROM City
ORDER BY CID
SELECT T.*
FROM (SELECT *,
Row_number()
OVER(
ORDER BY CID) AS rn
FROM City) T
WHERE rn BETWEEN 15 AND 20;
左右连接操作案例
数据
--查看MyData排序规则
SELECT Databasepropertyex('MyData', 'Collation');
--修改MyData排序规则为Chinese_PRC_CI_AS
ALTER DATABASE MyData COLLATE Chinese_PRC_CI_AS;
/*DROP TABLE persons;
DROP TABLE dict_jobs;
DROP TABLE dict_posts;
DROP TABLE dict_politics;
DROP TABLE dict_nations;
DROP TABLE dict_educations;
DROP TABLE dict_titles;
DROP TABLE dict_relations;*/
USE MyData;
--人员信息表
CREATE TABLE persons
(
--人员编号
person_id INT IDENTITY(1, 1) PRIMARY KEY,
--人员名称
person_name VARCHAR(20) NOT NULL,
--人员性别
person_gender INT NOT NULL,
--出生日期
person_birthday DATE NOT NULL,
--民族
nation_id INT NOT NULL,
--学历
education_id INT NOT NULL,
--婚姻状态
person_hy INT NOT NULL,
--政治
politics_id INT NOT NULL,
--户口类型
person_hk INT NOT NULL,
--职位
jobs_id INT NOT NULL,
--岗位
posts_id INT NOT NULL,
--职称
title_id INT NOT NULL,
--在职状态
person_state INT NOT NULL,
--人员关系
relation_id INT NOT NULL
);
INSERT INTO persons(
person_name,
person_gender,
person_birthday,
nation_id,
education_id,
person_hy,
politics_id,
person_hk,
jobs_id,
posts_id,
title_id,
person_state,
relation_id
)VALUES('裁决',1,'1970-01-01',1,1,1,1,1,1,1,1,1,1),
('裁决',1,'1970-01-01',1,1,1,1,1,1,1,1,1,1),
('研华',1,'1970-01-01',0,1,1,1,1,1,1,1,1,1),
('叶玲珑',1,'1970-01-01',0,1,1,1,1,1,1,1,1,1),
('叶凌云',1,'1970-01-01',1,1,1,1,1,1,1,1,1,1),
('青木神帝',1,'1970-01-01',1,1,1,1,1,1,1,1,1,1),
('李七夜',1,'1970-01-01',1,1,1,1,1,1,1,1,1,1),
('叶小小',1,'1970-01-01',0,1,1,1,1,1,1,1,1,1),
('裁决1',1,'1970-01-01',1,1,1,1,1,1,1,1,1,1),
('裁决2',1,'1970-01-01',1,1,1,1,1,1,1,1,1,1),
('裁决3',1,'1970-01-01',1,1,1,1,1,1,1,1,1,1),
('裁决4',1,'1970-01-01',0,1,1,1,1,1,1,1,1,1),
('裁决5',1,'1970-01-01',1,1,1,1,1,1,1,1,1,1),
('裁决6',1,'1970-01-01',1,1,1,1,1,1,1,1,1,1);
--职位表
CREATE TABLE dict_jobs
(
jobs_id INT IDENTITY(1, 1) PRIMARY KEY,
jobs_name VARCHAR(50) NOT NULL,
jobs_content VARCHAR(100) NULL
);
SET IDENTITY_INSERT dict_jobs ON;
--需要带上插入列名，否则关闭自增长无效
INSERT INTO dict_jobs(jobs_id,jobs_name,jobs_content)
VALUES (1,'主任',''),(2,'副主任',''),(3,'财务总监',''),(4,'无','');
SET IDENTITY_INSERT dict_jobs OFF;
--岗位表
CREATE TABLE dict_posts
(
posts_id INT IDENTITY(1, 1) PRIMARY KEY,
posts_name VARCHAR(50) NOT NULL,
posts_content VARCHAR(100) NULL
);
SET IDENTITY_INSERT dict_posts ON;
INSERT INTO dict_posts(posts_id,posts_name,posts_content)
VALUES (1,'信息技术',''),(2,'出纳',''),(3,'会计',''),(4,'专职',''),(5,'无','');
SET IDENTITY_INSERT dict_posts OFF;
--政治面貌
CREATE TABLE dict_politics
(
politics_id INT IDENTITY(1, 1) PRIMARY KEY,
politics_name VARCHAR(20) NOT NULL
);
SET IDENTITY_INSERT dict_politics ON;
INSERT INTO dict_politics(politics_id,politics_name)
VALUES (1,'中国共产党党员'),(2,'中国共青团团员'),(3,'中国国民党革命委员会'),
(4,'中国民主同盟'),(5,'中国民主建国会'),(6,'中国民主促进会'),
(7,'中国农工民主党'),(8,'中国致公党'),(9,'九三学社'),(10,'台湾民主自治同盟'),
(11,'群众'),(12,'其它党派');
SET IDENTITY_INSERT dict_politics OFF;
--民族表
CREATE TABLE dict_nations
(
nation_id INT IDENTITY(1, 1) PRIMARY KEY,
nation_name VARCHAR(10) NOT NULL
);
SET IDENTITY_INSERT dict_nations ON;
INSERT INTO dict_nations(nation_id,nation_name)
VALUES(1,'汉族'),(2,'蒙古族'),(3,'回族'),(4,'藏族'),(5,'维吾尔族'),(6,'苗族'),(7,'彝族')
,(8,'壮族'),(9,'布依族'),(10,'朝鲜族'),(11,'满族'),(12,'侗族'),(13,'瑶族'),(14,'白族')
,(15,'土家族'),(16,'哈尼族'),(17,'哈萨克族'),(18,'傣族'),(19,'黎族'),(20,'傈僳族'),(21,'佤族')
,(22,'畲族'),(23,'高山族'),(24,'拉祜族'),(25,'水族'),(26,'东乡族'),(27,'纳西族'),(28,'景颇族')
,(29,'柯尔克孜族'),(30,'土族'),(31,'达斡尔族'),(32,'仫佬族'),(33,'羌族'),(34,'布朗族')
,(35,'撒拉族'),(36,'毛难族'),(37,'仡佬族'),(38,'锡伯族'),(39,'阿昌族'),(40,'普米族')
,(41,'塔吉克族'),(42,' 怒族'),(43,'乌孜别克族'),(44,'俄罗斯族'),(45,'鄂温克族'),(46,'崩龙族')
,(47,'保安族'),(48,'裕固族'),(49,'京族'),(50,'塔塔尔族'),(51,'独龙族'),(52,'鄂伦春族')
,(53,'赫哲族'),(54,'门巴族'),(55,'珞巴族'),(56,'基诺族'),(57,'其他');
SET IDENTITY_INSERT dict_nations OFF;
--学历表
CREATE TABLE dict_educations
(
education_id INT IDENTITY(1, 1) PRIMARY KEY,
education_name VARCHAR(20) NOT NULL,
education_content VARCHAR(50) NULL
);
SET IDENTITY_INSERT dict_educations ON;
select*from dict_educations
INSERT INTO dict_educations(education_id,education_name,education_content)
VALUES (1,'博士研究生',''),(2,'硕士研究生',''),(3,'本科',''),(4,'专科',''),(5,'高中/中专',''),(6,'初中','')
,(7,'小学',''),(8,'无','');
SET IDENTITY_INSERT dict_educations OFF;
--职称表
CREATE TABLE dict_titles
(
title_id INT IDENTITY(1, 1) PRIMARY KEY,
title_name VARCHAR(20) NOT NULL,
title_content VARCHAR(50) NULL
);
SET IDENTITY_INSERT dict_titles ON;
INSERT INTO dict_titles(title_id,title_name,title_content)
VALUES (1,'正高级工程师',''),(2,'高级工程师',''),(3,'工程师',''),(4,'教授','')
,(5,'副教授',''),(6,'助工',''),(7,'技术员',''),(8,'无',''),(9,'讲师','');
SET IDENTITY_INSERT dict_titles OFF;
--人员关系表
CREATE TABLE dict_relations
(
relation_id INT IDENTITY(1, 1) PRIMARY KEY,
relation_name VARCHAR(10) NOT NULL
);
SET IDENTITY_INSERT dict_relations ON;
INSERT INTO dict_relations(relation_id,relation_name)
VALUES (1, '妻子'),(2, '丈夫'),(3, '父亲'),(4, '母亲'),(5, '哥哥'),(6, '姐姐')
,(7, '弟弟'),(8, '妹妹'),(9, '女友'),(10, '男友'),(11, '同学/朋友')
,(13, '老师'),(14, '女儿'),(15, '儿子'),(16, '无');
SET IDENTITY_INSERT dict_relations OFF;
案例
-----------------------------------------------左连接--------------------------------------------
SELECT p.person_name AS '姓名'
,CASE p.person_gender
WHEN 1
THEN '男'
ELSE '女'
END AS '性别'
,Datediff(YEAR, p.person_birthday, Getdate()) AS '年龄'
,N.nation_name AS '民族'
,E.education_name AS '学历'
,CASE p.person_hy
WHEN 1
THEN '已婚'
ELSE '未婚'
END AS '婚姻状况'
,POL.politics_name AS '政治面貌'
,CASE p.person_hk
WHEN 1
THEN '城镇'
ELSE '农村'
END AS '户口类型'
,J.jobs_name AS '职位'
,PO.posts_name AS '岗位'
,T.title_name AS '职称'
,CASE p.person_state
WHEN 1
THEN '在职'
ELSE '离职'
END AS '在职状态'
,R.relation_name AS '紧急联系人关系'
FROM persons AS p
LEFT JOIN dict_jobs AS J ON j.jobs_id = p.jobs_id
LEFT JOIN dict_educations AS E ON E.education_id = p.education_id
LEFT JOIN dict_nations AS N ON N.nation_id = P.nation_id
LEFT JOIN dict_politics AS POL ON POL.politics_id = p.politics_id
LEFT JOIN dict_posts AS PO ON PO.posts_id = p.posts_id
LEFT JOIN dict_titles AS T ON T.title_id = p.title_id
LEFT JOIN dict_relations AS R ON R.relation_id = p.relation_id
------------WHERE
SELECT p.person_name AS '姓名'
,CASE p.person_gender
WHEN 1
THEN '男'
ELSE '女'
END AS '性别'
,Datediff(YEAR, p.person_birthday, Getdate()) AS '年龄'
,N.nation_name AS '民族'
,E.education_name AS '学历'
,CASE p.person_hy
WHEN 1
THEN '已婚'
ELSE '未婚'
END AS '婚姻状况'
,POL.politics_name AS '政治面貌'
,CASE p.person_hk
WHEN 1
THEN '城镇'
ELSE '农村'
END AS '户口类型'
,J.jobs_name AS '职位'
,PO.posts_name AS '岗位'
,T.title_name AS '职称'
,CASE p.person_state
WHEN 1
THEN '在职'
ELSE '离职'
END AS '在职状态'
,R.relation_name AS '紧急联系人关系'
FROM persons AS p
LEFT JOIN dict_jobs AS J ON j.jobs_id = p.jobs_id
LEFT JOIN dict_educations AS E ON E.education_id = p.education_id
LEFT JOIN dict_nations AS N ON N.nation_id = P.nation_id
LEFT JOIN dict_politics AS POL ON POL.politics_id = p.politics_id
LEFT JOIN dict_posts AS PO ON PO.posts_id = p.posts_id
LEFT JOIN dict_titles AS T ON T.title_id = p.title_id
LEFT JOIN dict_relations AS R ON R.relation_id = p.relation_id
WHERE p.person_name = '青木神帝'
-----------------------------------------------------右连接--------------------------------------------------
SELECT p.person_name AS '姓名'
,CASE p.person_gender
WHEN 1
THEN '男'
ELSE '女'
END AS '性别'
,Datediff(YEAR, p.person_birthday, Getdate()) AS '年龄'
,N.nation_name AS '民族'
,E.education_name AS '学历'
,CASE p.person_hy
WHEN 1
THEN '已婚'
ELSE '未婚'
END AS '婚姻状况'
,POL.politics_name AS '政治面貌'
,CASE p.person_hk
WHEN 1
THEN '城镇'
ELSE '农村'
END AS '户口类型'
,J.jobs_name AS '职位'
,PO.posts_name AS '岗位'
,T.title_name AS '职称'
,CASE p.person_state
WHEN 1
THEN '在职'
ELSE '离职'
END AS '在职状态'
,R.relation_name AS '紧急联系人关系'
FROM persons AS p
RIGHT JOIN dict_jobs AS J ON j.jobs_id = p.jobs_id
LEFT JOIN dict_educations AS E ON E.education_id = p.education_id
RIGHT JOIN dict_nations AS N ON N.nation_id = P.nation_id
RIGHT JOIN dict_politics AS POL ON POL.politics_id = p.politics_id
RIGHT JOIN dict_posts AS PO ON PO.posts_id = p.posts_id
RIGHT JOIN dict_titles AS T ON T.title_id = p.title_id
RIGHT JOIN dict_relations AS R ON R.relation_id = p.relation_id
10、创建视图
--创建视图
CREATE VIEW CityNameView
AS
SELECT pname AS 省,
cname AS 市区州,
aname AS 县市
FROM city
INNER JOIN province
ON province.pid = city.pid
INNER JOIN area
ON city.cid = area.cid;
SELECT *
FROM CityNameView;
11、T-SQL
/*----------------------T-SQL编程---------------*/
/*1、声明变量*/
/*单个变量*/
DECLARE @name NVARCHAR(50)
DECLARE @age INT
/*多个变量*/
DECLARE @name NVARCHAR(50),
@age INT
/*2、为变量赋值*/
SET @name='刘燕龙'
SELECT @age = 18
/*3、输出*/
SELECT '姓名',
@name
SELECT '年龄',
@age
/*while循环*/
DECLARE @i INT=1/*声明变量*/
WHILE @i <= 100
BEGIN
PRINT 'Hello'
SET @i=@i + 1
END
事务
/*事务*/
/*
开始事务：BEGIN TRANSACTION
提交事务：COMOIT TRANSACTION
回滚事务：ROLLBACK TRANSACTION
*/
CREATE TABLE bank
(
cid CHAR(4) PRIMARY KEY NOT NULL,
balance DECIMAL CHECK(balance>=0)
);
INSERT INTO bank
VALUES ('0001',
910.0);
INSERT INTO bank
VALUES ('0002',
100.0);
--通过事务转账
--显示事务
BEGIN TRANSACTION
DECLARE @sum INT =0
UPDATE bank
SET balance = balance - 100
WHERE cid = '0002'
SET @sum=@sum + @@ERROR
UPDATE bank
SET balance = balance + 100
WHERE cid = '0001'
SET @sum=@sum + @@ERROR
IF @sum <> 0
BEGIN
/*出错了回滚事务*/
ROLLBACK
END
ELSE
/*成功了提交事务*/
COMMIT
SELECT *
FROM bank;
--自动提交事务
--当执行一条sql语句的时候，数据库自动帮我们打开一个事务，当语句执行成功，数据库自动提交事务，执行失败自动回滚事务
--隐式事务
--每一次执行一条sql语句的时候，数据库自动帮我们打开事务，但是需要我们手动提交事务，或者回滚事务
SET IMPLICIT_TRANSACTIONS{ON|OFF}
SET IMPLICIT_TRANSACTIONS ON
INSERT INTO bank
VALUES ('0003',
1000);
COMMIT
SET IMPLICIT_TRANSACTIONS OFF
--显示事务：需要手动打开事务，手动提交
bengin TRANSACTION
---.....
COMMIT TRANSACTION
TSQL-工作日案例
---------工作日--------------
--创建表
--工作日
CREATE TABLE WorkDays
(
ID NVARCHAR(50) NOT NULL PRIMARY KEY,--主键
Dates DATETIME NOT NULL,--日期
Years INT NOT NULL,--年份
Months INT NOT NULL,--月份
WorkDay INT NOT NULL--天数
);
--修改数据类型为DATE
ALTER TABLE WorkDays ALTER COLUMN Dates DATE;
--工作日SQL
DECLARE @sql NVARCHAR(MAX)
--查询的记录条数
DECLARE @count INT
--查询的年份
DECLARE @years INT
--查询的月份
DECLARE @months INT
--给参数赋值
SET @sql =
'INSERT INTO WorkDays(ID, Dates, Years, Months,WorkDay) VALUES(''20231101'',''2023-11-01'',''2023'',''11'',22),(''20231102'',''2023-11-02'',''2023'',''11'',22),(''20231103'',''2023-11-03'',''2023'',''11'',22),(''20231110'',''2023-11-10'',''2023'',''11'',22),(''20231108'',''2023-11-08'',''2023'',''11'',22),(''20231106'',''2023-11-06'',''2023'',''11'',22),(''20231113'',''2023-11-13'',''2023'',''11'',22),(''20231114'',''2023-11-14'',''2023'',''11'',22),(''20231107'',''2023-11-07'',''2023'',''11'',22),(''20231115'',''2023-11-15'',''2023'',''11'',22),(''20231116'',''2023-11-16'',''2023'',''11'',22),(''20231117'',''2023-11-17'',''2023'',''11'',22),(''20231124'',''2023-11-24'',''2023'',''11'',22),(''20231123'',''2023-11-23'',''2023'',''11'',22),(''20231122'',''2023-11-22'',''2023'',''11'',22),(''20231121'',''2023-11-21'',''2023'',''11'',22),(''20231120'',''2023-11-20'',''2023'',''11'',22),(''20231127'',''2023-11-27'',''2023'',''11'',22),(''20231128'',''2023-11-28'',''2023'',''11'',22),(''20231129'',''2023-11-29'',''2023'',''11'',22),(''20231130'',''2023-11-30'',''2023'',''11'',22),(''20231109'',''2023-11-09'',''2023'',''11'',22);'
SET @years=2023
SET @months=11
--查询传入日期是否存在记录
SET @count = (SELECT Count(ID)
FROM WorkDays
WHERE Years = @years
AND Months = @months)
IF @count > 0
BEGIN
--删除已存在的记录
DELETE WorkDays
WHERE Years = @years
AND Months = @months
END
--使用系统存储过程插入数据
EXEC sp_executesql @sql
--查看记录
SELECT*FROM WorkDays;
12、存储过程
测试数据
--测试案例
USE MyData;
CREATE TABLE Students
(
No INT IDENTITY(20190001, 1) NOT NULL PRIMARY KEY,
Name NVARCHAR(50) NOT NULL,
Age INT NOT NULL,
Gender NCHAR(1) NOT NULL,
Grade INT NOT NULL,
Class NVARCHAR(15) NOT NULL,
Address NVARCHAR(100),
Birthday DATE,
Email varchar(20) UNIQUE
);
INSERT INTO Students VALUES
('裁决',18,'男',2018,'9A','宗玄大陆','2000-09-01','555518441@qq.com')
,('杨凯',18,'男',2019,'19A','宗玄大陆','2000-09-01','455518441@qq.com')
,('青帝',18,'男',2018,'18B','第十界-十三洲','1997-05-16','465518441@qq.com')
,('慕芷璃',18,'女',2018,'18B','第十界-十三洲','1997-05-16','4655141@qq.com')
,('苗婵',18,'女',2018,'18B','第十界-十三洲','1997-05-16','46441@qq.com')
,('青木神帝',18,'男',2018,'18B','第十界-十三洲','1997-05-16','43556341@qq.com')
,('青木神帝',19,'男',2018,'18B','第十界-十三洲','1997-05-16','43841@qq.com')
,('青木神帝',13,'男',2018,'18B','第十界-十三洲','1997-05-16','435841@qq.com')
,('青木神帝',100,'男',2018,'18B','第十界-十三洲','1997-05-16','435541@qq.com');
CREATE TABLE bank
(
cid CHAR(4) PRIMARY KEY NOT NULL,
balance DECIMAL CHECK(balance>=0)
);
INSERT INTO bank
VALUES ('0001',910.0),('0002',100.0);
案例
------------------------------系统的存储过程-------------------
--查看所有数据库
EXEC Sp_databases;
--查看所有表
--语法EXEC Sp_tables 表名;
--不带表名查看所有表
EXEC Sp_tables;
EXEC Sp_tables Students;
--查看列
--语法EXEC Sp_columns 表名;
EXEC Sp_columns Students;
--查看索引
--语法EXEC Sp_helpindex 表名;
EXEC Sp_helpindex Students;
--约束
--语法EXEC Sp_helpconstraint 表名;
EXEC Sp_helpconstraint Students;
--查看存储过程创建、定义语句
--语法EXEC Sp_helptext '存储过程名'
--存储过程名称有空格的需要用中括号括起来
--入'[sp stored procedures]'
EXEC Sp_helptext 'sp_stored_procedures';
--数据库帮助，查询数据库信息
EXEC Sp_helpdb;
EXEC Sp_helpdb master;
------------------------------------创建自己的存储过程-------------------------------------------
--创建语法
/*create proc | procedure pro_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
....
]
as
SQL_statements*/
--------------------------无参数存储过程---------------
CREATE PROC Usp_say_hello
AS
BEGIN
PRINT 'HELLO WORLD'
END
--------------------------调用存储过程----------------
--exec 存储过程名
EXEC Usp_say_hello
--------------------------修改存储过程--------------------
--alter 存储过程名
ALTER PROC Usp_say_hello
AS
PRINT 'say hello'
--删除存储过程
--drop 存储过程名
DROP PROC usp_say_hello
-------------------------有参数的存储过程----------------
/*create 存储过程名
@参数1 数据类型,
@参数2 数据类型,
,,,,,
@参数n 数据类型
as
sql表达式*/
CREATE PROC Usp_addnumber @a INT
,@b INT
AS
PRINT @a + @b
--调用带参数的存储过程
--exec 存储过程名 @参数1,@参数2,,,,,@参数n
EXEC Usp_addnumber @a = 1
,@b = 2
--或者
EXEC Usp_addnumber 1
,2
---------------------------------有输出参数的存储过程，参数后面加output---------------
CREATE PROC Usp_show_students @sgender NCHAR(1)
,@recordcount INT OUTPUT
AS
BEGIN
SELECT *
FROM Students
WHERE Gender = @sgender
SET @recordcount = (
SELECT Count(*)
FROM Students
WHERE Gender = @sgender
)
END
--使用----------------
DECLARE @rc INT
EXEC Usp_show_students @sgender = '男'
,@recordcount = @rc OUTPUT
PRINT @rc
---------------------------------使用存储过程编写分页------------------
CREATE PROC Usp_student_pageindex @pageSize INT = 3
,--每页尺寸
@pageIndex INT = 1
,--第几页
@pageCount INT OUTPUT
,--总页数
@recordCount INT OUTPUT --总记录条数
AS
BEGIN
--第几页显示
SELECT *
FROM (
SELECT *
,rn = Row_number() OVER (
ORDER BY sno ASC
)
FROM Students
) AS t
WHERE t.rn BETWEEN (@pageIndex - 1) * @pageSize + 1
AND @pageSize * @pageIndex
--总记录条数
SET @recordCount = (
SELECT Count(*)
FROM Students
)
--总页数
SET @pageCount = Ceiling(@recordCount * 1.0 / @pageSize)
END
--使用--------
DECLARE @rc INT
,@pc INT
EXEC Usp_student_pageindex @pageSize = 3
,@pageIndex = 1
,@pageCount = @rc OUTPUT
,@recordCount = @pc OUTPUT
PRINT @rc
PRINT @pc
-------------------------创建存储过程实现转账-------------------------
CREATE PROC Usp_transfer @from CHAR(4)
,--转出ID
@to CHAR(4)
,--转入ID
@balance DECIMAL
,--余额
@isOK INT OUTPUT --判断是否成功
AS
BEGIN
--判断余额是否充足
DECLARE @money DECIMAL
SELECT @money = balance
FROM bank
WHERE cid = @from
IF (@money - @balance >= 10)
BEGIN
BEGIN TRANSACTION
DECLARE @sum INT = 0
--减钱
UPDATE bank
SET balance = balance - @balance
WHERE cid = @from
SET @sum = @sum + @@ERROR
--加钱
UPDATE bank
SET balance = balance + @balance
WHERE cid = @to
SET @sum = @sum + @@ERROR
--判断转账是否成功
IF @sum <> 0
BEGIN
SET @isOk = 1
ROLLBACK
END
ELSE
BEGIN
SET @isOk = 2
COMMIT
END
END
ELSE
BEGIN
SET @isOK = 3 --余额不足
END
END
--使用-------
DECLARE @pc INT
EXEC Usp_transfer @from = '0001'
,@to = '0002'
,@balance = 100
,@isOk = @pc OUTPUT
PRINT @pc
13、游标
数据使用存储过程的
语法
--定义变量
DECLARE 变量1，变量2，变量3,,,,变量N
--定义游标并读取数据
DECLARE curl CURSOR FOR (
SELECT
你要的字段
FROM
你的表名)
SET @Msg='';
--打开游标
OPEN curl;
--读取数据
--赋值的时候字段数量一定要和变量数量对应
FETCH NEXT FROM curl INTO 变量1，变量2，变量3,,,,变量N;
WHILE @@FETCH_STATUS = 0
BEGIN
--这里为要做的操作
--读取下一条数据
FETCH NEXT FROM curl INTO变量1，变量2，变量3,,,,变量N;
END
--关闭游标
CLOSE curl;
--释放游标
DEALLOCATE curl;
案例
--定义变量
DECLARE @No INT,@Name NVARCHAR(50),@Birthday DATE,@Msg NVARCHAR(MAX)
--定义游标并读取数据
DECLARE curl CURSOR FOR (
SELECT
No,Name,Birthday
FROM
MyData.dbo.Students)
SET @Msg='';
--打开游标
OPEN curl;
--读取数据
FETCH NEXT FROM curl INTO @No,@Name,@Birthday;
WHILE @@FETCH_STATUS = 0
BEGIN
--这里为要做的操作
SET @Msg=@Msg+'姓名为：'+@Name+',出生日期为：'+CONVERT(VARCHAR(10), @Birthday , 23);
--读取下一条数据
FETCH NEXT FROM curl INTO @No,@Name,@Birthday;
END
--关闭游标
CLOSE curl;
--释放游标
DEALLOCATE curl;
PRINT @Msg
扫一扫
304
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
