SQL SERVER学习杂记
来源:互联网 发布:电脑屏幕直播软件 免费 编辑:程序博客网 时间:2024/05/22 02:17
Database SQL Server
目录
随手记.1
SQL EXEC 命令用法.3
Sql_Case_When用法.6
随手记
连接查询:
Select a.name,b.password From Table_1 asa,Table_2 as b where a.ID=b.ID
Join 关键字查询涉及到表的分类,JOIN关键字左边的表叫左表,右边的表叫右表。
笛卡尔积
Selecta.containNum,b.classTimefromTAble_1 as a Join Table_2 asbon a.classNo=b.classNo
select b.classNo,b.classNo,a.containNum,b.classTimefromclass as a Cross Join courseas b Where a.classNo=b.classNo
使用Join时,如果要指定连接条件,则必须使用on。使用CrossJoin时,如果要指定连接条件,则必须使用Where。
内部连接:按照比较方式不同,可以分为等值连接、自然连接和不等连接。
1.等值连接:Inner Join关键字必须使用On来设置连接条件,而不能使用Where子句。
2.自然连接:是一种特殊的等值连接,要求两个表的相同列(不仅类型相同,而且名称相同)在Select中只出现一次。
外部连接:(Outer Join) 左外连接(Left Outer Join) 右外连接(Right Outer Join) 左外连接就是在连接时,左表的所有记录主动与右表的所有记录进行组合。在这些组合中,满足连接条件的记录直接返回;在不满足连接条件的记录中,左表的列直接显示,而右表的相应列被填充为NULL。
自连接:表与自身的连接,是内部连接的一种。可以使用户操作同一个表的不同行,其使用较少,但是很重要。
联合查询:并Union 交Intersection 差Except
Select语句 UnionSelect语句
Union 的一些限制条件:
1.结果集必须都具有相同的结构 ,列数必须相同,而且列的数据类型必须是兼容的。
2.Union结果集的列名与Union运算符中第一个Select语句中的列名相同。
3.默认情况下,Union运算符将从结果集中删除重复的行,如果用户使用All关键字,那么结果集中将包含所有行。
主键:
在多个列的组合上创建主键:但仍然只能有一个主键。按住Ctrl键,一次选中多个列值,右键设置主键。
查看当前SQL Server 实例的所有数据库的SQL语句:Exec sp_databases
查看数据库的一些属性:
select name,database_id,owner_sid,create_date,compatibility_level,collation_name,user_access_descfromsys.databases
查看当前SQL Server实例的某个数据库的信息:
Exec sp_helpdbN'AEMS2' --(查看数据库AEMS2的信息)
查看当前数据库下的所有表:
use AEMS2
GO
Select name,OBJECT_ID,TYPE,create_date,modify_datefromsys.tables
查看数据库中定义了主键的表:
use AEMS2
GO
Select t.nameas'含主键的表' fromsys.tablestwhere object_idin(Selectparent_object_idfromsys.key_constraintswheretype='PK')
开启自动收缩数据库:alter Database AEMS2 sETaUTO_SHRINKON
或者在数据库上右击属性—选项—自动—自动收缩设为true
手动收缩数据库或文件:在数据库上右击--任务--收缩
Varchar和Nvarchar的区别
varchar(n)
长度为 n 个字节的可变长度且非 Unicode的字符数据。n必须是一个介于 1 和 8,000之间的数值。存储大小为输入数据的字节的实际长度,而不是 n个字节。
nvarchar(n)
包含 n 个字符的可变长度 Unicode 字符数据。n 的值必须介于 1与 4,000之间。字节的存储大小是所输入字符个数的两倍。
两字段分别有字段值:我和coffee
那么varchar字段占2×2+6=10个字节的存储空间,而nvarchar字段占8×2=16个字节的存储空间。
如字段值只是英文可选择varchar,而字段值存在较多的双字节(中文、韩文等)字符时用nvarchar
上面是一个总结介绍,通过上面的介绍,可以知道。
varchar(4) 可以输入4个字线,也可以输入两个汉字
nvarchar(4) 可以输四个汉字,也可以输4个字母,但最多四个
SQLEXEC 命令用法
EXEC命令有两个用法:执行一个存储过程,或者执行一个动态批次。批次是一个内容为SQL语句的字符串。
例如:
declare @schemaName varchar(80),@tableNamevarchar(80),@objName varchar(512);
set @schemaName='dbo';
set @tableName='Orders';
set @objName=@schemaName+'.'+@tableName;
exec('select count(*) from '+@objName);
注意,在exec的括号里,只允许是字符串变量、字符串常量存在。不允许在这里调用函数或者使用case表达式。
下面的代码是错误的:
exec('select count(*) from'+quotename(@objName));
所以,基本的方法是将语句保存到一个变量里,例如:
set @sql='...';
exec(@sql);
这就不存在上述限制了。
1 ,EXEC没有接口
Exec的唯一输入是字符串。一个动态批次无权访问调用它的批次内所定义的局部变量:
declare @i int
set @i=1;
declare @sql varchar(255)
set @sql='select * from dbo.orderswhere rderid=@i';
exec(@sql);
出错:必须声明标量变量@i
原因还是@i不能放在''之内,它只能通过字符串连接动态的嵌入到SQL语句里:
set @sql='select * from dbo.orderswhere rderid='+cast(@i as varchar(10));
变量与字符串连接会引发所谓的SQL注入安全隐患,如果变量包含了字符串。要防患SQL注入的一个办法是限制字符串的大小。
这种连接会对性能造成映像。SS会为每个字符串单独创建一个执行计划,无论两个字符串是否是一个模式。为此,下面显示了一个例子:
首先清除cache里的执行计划:
DBCC freeproccache;
然后执行下面的代码三次,分别将@i设置为10248,10249,10250
DECLARE @i AS INT;
SET @i = 10248;
DECLARE @sql AS VARCHAR(52);
SET @sql = 'SELECT * FROMdbo.Orders WHERE rderID = '
+ CAST(@i AS VARCHAR(10)) + N';';
EXEC(@sql)
最后查询sys.syscacheobjects:
SELECT cacheobjtype, objtype,usecounts, sql
FROM sys.syscacheobjects
WHERE sql NOT LIKE '%cache%'
AND sql NOT LIKE '%sys.%';
结果显示为:
它会为每次查询生成一个执行计划。以及一个参数化的执行计划。
EXEC也没有输出参数。缺省情况下,exec将返回该次查询的输出给调用者。如果希望将该结果保存到变量里,那就必须使用InsertEXEC语法,并从表里从新读取该数据,然后储存到目标变量里。
DECLARE
@schemaname AS NVARCHAR(128),
@tablename AS NVARCHAR(128),
@colname AS NVARCHAR(128),
@sql AS NVARCHAR(805),
@cnt AS INT;
SET @schemaname = N'dbo';
SET @tablename = N'Orders';
SET @colname = N'CustomerID';
SET @sql = N'SELECT COUNT(DISTINCT '
+ QUOTENAME(@colname) + N') FROM '
+QUOTENAME(@schemaname)
+ N'.'
+ QUOTENAME(@tablename)
+ N';';
CREATE TABLE #T(cnt INT);
INSERT INTO #T
EXEC(@sql);
SET @cnt = (SELECT cnt FROM #T);
SELECT @cnt;
DROP TABLE #T;
注意如果忘记输入最后的一条语句“Drop 。。。”,那么就会出现下面的令人讨厌的错误:数据库中已存在名为 '#T' 的对象。
在上面这段程序里,创建了一个临时表,它对于动态批次是可见的。所以可以修改上面的程序为:
SET @sql = N'INSERT INTO #T(cnt) SELECTCOUNT(DISTINCT '
+ QUOTENAME(@colname) + N') FROM '
+ QUOTENAME(@schemaname)
+ N'.'
+ QUOTENAME(@tablename)
+ N';';
CREATE TABLE #T(cnt INT);
EXEC(@sql);
SET @cnt = (SELECT cnt FROM #T);
SELECT @cnt;
DROP TABLE #T;
因为EXEC的执行是在Create Table之后,所以Insert语句可以移到@sql的定义里。
2,变量的连接
在SS2000里,EXEC优于sp_executesql的一点是,它支持输入的代码长度要长一些。虽然,从技术上说后者的输入代码字符串是NTEXT类型,通常希望用一个局部变量来保存它。但是,局部变量是不能声明成大对象类型的。
所以,实际上sp_executesql的最大支持的字符串长度是Unicode字符串的长度(NVARCHAR,4000个字符)。而EXEC,则支持常规的字符串(VARCHAR),即8000个字符。
而且,EXEC支持多个变量的连接,每个变量最大有8000个字符。
不过在SS2005里,变量类型可以是VARCHAR(max),最大为2G。
3 ,EXEC AT
这是05里的新语法。执行远程主机上的动态SQL语句
Sql_Case_When用法
SQL中Case的使用方法
Case具有两种格式。简单Case函数和Case搜索函数。
--简单Case函数
CASE sex
WHEN '1'THEN'男'
WHEN '2'THEN'女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
WHEN sex ='2'THEN '女'
ELSE '其他'END
这两种方式,可以实现相同的功能。简单Case函数的写法相对比较简洁,但是和Case搜索函数相比,功能方面会有些限制,比如写判断式。
还有一个需要注意的问题,Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略。
--比如说,下面这段SQL,你永远无法得到“第二类”这个结果
CASE WHEN col_1IN( 'a','b')THEN'第一类'
WHEN col_1 IN('a') THEN'第二类'
ELSE'其他' END
下面我们来看一下,使用Case函数都能做些什么事情。
一,已知数据按照另外一种方式进行分组,分析。
有如下数据:(为了看得更清楚,我并没有使用国家代码,而是直接用国家名作为PrimaryKey)
国家(country)人口(population)
中国 600
美国 100
加拿大 100
英国 200
法国 300
日本 250
德国 200
墨西哥 50
印度 250
根据这个国家人口数据,统计亚洲和北美洲的人口数量。应该得到下面这个结果。
洲人口
亚洲1100
北美洲250
其他700
想要解决这个问题,你会怎么做?生成一个带有洲Code的View,是一个解决方法,但是这样很难动态的改变统计的方式。
如果使用Case函数,SQL代码如下:
SELECT SUM(population),
CASE country
WHEN '中国' THEN'亚洲'
WHEN '印度' THEN'亚洲'
WHEN '日本' THEN'亚洲'
WHEN '美国' THEN'北美洲'
WHEN '加拿大' THEN'北美洲'
WHEN '墨西哥' THEN'北美洲'
ELSE '其他'END
FROM Table_A
GROUP BY CASE country
WHEN '中国' THEN'亚洲'
WHEN '印度' THEN'亚洲'
WHEN '日本' THEN'亚洲'
WHEN '美国' THEN'北美洲'
WHEN '加拿大' THEN'北美洲'
WHEN '墨西哥' THEN'北美洲'
ELSE '其他'END;
同样的,我们也可以用这个方法来判断工资的等级,并统计每一等级的人数。SQL代码如下;
SELECT
CASE WHEN salary<= 500THEN'1'
WHENsalary > 500 ANDsalary <= 600 THEN'2'
WHENsalary > 600 ANDsalary <= 800 THEN'3'
WHEN salary > 800 AND salary <= 1000THEN '4'
ELSE NULLEND salary_class,
COUNT(*)
FROM Table_A
GROUP BY
CASE WHEN salary<= 500THEN '1'
WHEN salary > 500 AND salary <=600 THEN'2'
WHENsalary > 600 ANDsalary <= 800 THEN'3'
WHEN salary > 800 AND salary <= 1000THEN '4'
ELSE NULLEND;
二,用一个SQL语句完成不同条件的分组。
有如下数据
国家(country)性别(sex)人口(population)
中国1 340
中国2 260
美国1 45
美国2 55
加拿大1 51
加拿大2 49
英国1 40
英国2 60
按照国家和性别进行分组,得出结果如下
国家男女
中国340 260
美国45 55
加拿大51 49
英国40 60
普通情况下,用UNION也可以实现用一条语句进行查询。但是那样增加消耗(两个Select部分),而且SQL语句会比较长。
下面是一个是用Case函数来完成这个功能的例子
SELECT country,
SUM(CASEWHEN sex = '1' THEN
population ELSE0 END), --男性人口
SUM(CASEWHEN sex = '2' THEN
population ELSE0 END) --女性人口
FROM Table_A
GROUP BY country;
这样我们使用Select,完成对二维表的输出形式,充分显示了Case函数的强大。
三,在Check中使用Case函数。
在Check中使用Case函数在很多情况下都是非常不错的解决方法。可能有很多人根本就不用Check,那么我建议你在看过下面的例子之后也尝试一下在SQL中使用Check。
下面我们来举个例子
公司A,这个公司有个规定,女职员的工资必须高于块。如果用Check和Case来表现的话,如下所示
CONSTRAINT check_salary CHECK
( CASEWHEN sex= '2'
THENCASEWHENsalary> 1000
THEN 1 ELSE 0 END
ELSE 1 END = 1 )
如果单纯使用Check,如下所示
CONSTRAINT check_salary CHECK
( sex ='2'AND salary > 1000)
女职员的条件倒是符合了,男职员就无法输入了。
四,根据条件有选择的UPDATE。
例,有如下更新条件
工资以上的职员,工资减少%
工资在到之间的职员,工资增加%
很容易考虑的是选择执行两次UPDATE语句,如下所示
--条件
UPDATE Personnel
SET salary = salary* 0.9
WHERE salary >= 5000;
--条件
UPDATE Personnel
SET salary = salary* 1.15
WHERE salary >= 2000AND salary< 4600;
但是事情没有想象得那么简单,假设有个人工资块。首先,按照条件,工资减少%,变成工资。接下来运行第二个SQL时候,因为这个人的工资是在到的范围之内,需增加%,最后这个人的工资结果是,不但没有减少,反而增加了。如果要是反过来执行,那么工资的人相反会变成减少工资。暂且不管这个规章是多么荒诞,如果想要一个SQL 语句实现这个功能的话,我们需要用到Case函数。代码如下:
UPDATE Personnel
SET salary = CASE WHEN salary >=5000
THENsalary * 0.9
WHEN salary >= 2000AND salary< 4600
THEN salary * 1.15
ELSE salary END;
这里要注意一点,最后一行的ELSE salary是必需的,要是没有这行,不符合这两个条件的人的工资将会被写成NUll,那可就大事不妙了。在Case函数中Else部分的默认值是NULL,这点是需要注意的地方。
这种方法还可以在很多地方使用,比如说变更主键这种累活。
一般情况下,要想把两条数据的Primary key,a和b交换,需要经过临时存储,拷贝,读回数据的三个过程,要是使用Case函数的话,一切都变得简单多了。
p_key col_1 col_2
a 1 张三
b 2 李四
c 3 王五
假设有如上数据,需要把主键a和b相互交换。用Case函数来实现的话,代码如下
UPDATE SomeTable
SET p_key = CASE WHEN p_key ='a'
THEN 'b'
WHEN p_key = 'b'
THEN 'a'
ELSE p_key END
WHERE p_key IN('a','b');
同样的也可以交换两个Unique key。需要注意的是,如果有需要交换主键的情况发生,多半是当初对这个表的设计进行得不够到位,建议检查表的设计是否妥当。
五,两个表数据是否一致的检查。
Case函数不同于DECODE函数。在Case函数中,可以使用BETWEEN,LIKE,ISNULL,IN,EXISTS等等。比如说使用IN,EXISTS,可以进行子查询,从而实现更多的功能。
下面具个例子来说明,有两个表,tbl_A,tbl_B,两个表中都有keyCol列。现在我们对两个表进行比较,tbl_A中的keyCol列的数据如果在tbl_B的keyCol列的数据中可以找到,返回结果'Matched',如果没有找到,返回结果'Unmatched'。
要实现下面这个功能,可以使用下面两条语句
--使用IN的时候
SELECT keyCol,
CASE WHEN keyColIN( SELECT keyCol FROMtbl_B )
THEN 'Matched'
ELSE 'Unmatched'END Label
FROM tbl_A;
--使用EXISTS的时候
SELECT keyCol,
CASE WHEN EXISTS ( SELECT*FROM tbl_B
WHERE tbl_A.keyCol= tbl_B.keyCol)
THEN 'Matched'
ELSE 'Unmatched'END Label
FROM tbl_A;
使用IN和EXISTS的结果是相同的。也可以使用NOTIN和NOTEXISTS,但是这个时候要注意NULL的情况。
六,在Case函数中使用合计函数
假设有下面一个表
学号(std_id) 课程ID(class_id) 课程名(class_name) 主修flag(main_class_flg)
100 1 经济学Y
100 2 历史学N
200 2 历史学N
200 3 考古学Y
200 4 计算机N
300 4 计算机N
400 5 化学N
500 6 数学N
有的学生选择了同时修几门课程(100,200)也有的学生只选择了一门课程(300,400,500)。选修多门课程的学生,要选择一门课程作为主修,主修flag里面写入Y。只选择一门课程的学生,主修flag为N(实际上要是写入Y的话,就没有下面的麻烦事了,为了举例子,还请多多包含)。
现在我们要按照下面两个条件对这个表进行查询
只选修一门课程的人,返回那门课程的ID
选修多门课程的人,返回所选的主课程ID
简单的想法就是,执行两条不同的SQL语句进行查询。
条件
--条件:只选择了一门课程的学生
SELECT std_id,MAX(class_id)AS main_class
FROM Studentclass
GROUP BY std_id
HAVING COUNT(*)= 1;
执行结果
STD_ID MAIN_class
------ ----------
300 4
400 5
500 6
条件
--条件:选择多门课程的学生
SELECT std_id, class_idAS main_class
FROM Studentclass
WHERE main_class_flg = 'Y' ;
执行结果
STD_ID MAIN_class
------ ----------
100 1
200 3
如果使用Case函数,我们只要一条SQL语句就可以解决问题,具体如下所示
SELECT std_id,
CASE WHEN COUNT(*) = 1 --只选择一门课程的学生的情况
THEN MAX(class_id)
ELSE MAX(CASEWHEN main_class_flg='Y'
THEN class_id
ELSE NULL END
)
END AS main_class
FROM Studentclass
GROUP BY std_id;
运行结果
STD_ID MAIN_class
------ ----------
100 1
200 3
300 4
400 5
500 6
通过在Case函数中嵌套Case函数,在合计函数中使用Case函数等方法,我们可以轻松的解决这个问题。使用Case函数给我们带来了更大的自由度。
最后提醒一下使用Case函数的新手注意不要犯下面的错误
CASE col_1
WHEN 1 THEN'Right'
WHEN NULL THEN 'Wrong'
END
在这个语句中When Null这一行总是返回unknown,所以永远不会出现Wrong的情况。因为这句可以替换成WHEN col_1=NULL,这是一个错误的用法,这个时候我们应该选择用WHENcol_1ISNULL。
数据库备份还原
备份之后----restore----from device(记得选择所有文件,否则可能会找不到)
- SQL SERVER学习杂记
- SQL Tuning学习杂记
- SQL Tuning学习杂记
- SQL Server 性能调优杂记(三)
- Sql Server杂记1--数据库变量
- 学习杂记--- 贫血 充血 sql join
- 【C++、SQL学习笔记3】杂记
- SQL杂记
- sql杂记
- SQL杂记
- sql杂记
- 学习杂记
- 学习杂记
- 学习杂记
- 学习杂记
- 学习杂记
- 学习杂记
- 学习杂记
- poj2576二维01背包
- PLL Simulink行为模型
- 响应式web设计之CSS3 Media Queries
- 聊聊移动端跨平台开发的各种技术
- leetcode之路009 Palindrome Number
- SQL SERVER学习杂记
- 学习笔记——mybatis的sql拼接与log4j调试
- MarqueeTextview(跑马灯)
- UIScrollView的属性总结
- hdu 1495 非常可乐(广搜)
- 7月份复习总结&8月份复习计划
- 随笔
- 设计模式(23) - 命令模式
- 五布局之表格布局TableLayout