/* 由于数据较多，请通过查找下面的备选项可以定位想找的内容:【跨数据库操作】【跨IP操作】【INNER JOIN】【取得当前数据库中的所有表】【取得指定表的字段定义】【修改字段长度】【新增字段】【删除字段】*/--CREATE DATABASE TestSQL
--USE TestSQL--CREATE TABLE T_Employee (
-- FNumber VARCHAR(20),FName VARCHAR(20),-- FAge INT,FSalary NUMERIC(10,2),-- PRIMARY KEY (FNumber))--INSERT INTO T_Employee(FNumber,FName,FAge,FSalary)--VALUES('DEV001','Tom',25,8300);
--INSERT INTO T_Employee(FNumber,FName,FAge,FSalary)--VALUES('DEV002','Jerry',28,2300.80);
--INSERT INTO T_Employee(FNumber,FName,FAge,FSalary)--VALUES('SALES001','John',23,5000);
--INSERT INTO T_Employee(FNumber,FName,FAge,FSalary)--VALUES('SALES002','Kerry',28,6200);
--INSERT INTO T_Employee(FNumber,FName,FAge,FSalary)--VALUES('SALES003','Stone',22,1200);
--INSERT INTO T_Employee(FNumber,FName,FAge,FSalary)--VALUES('HR001','Jane',23,2200.88);
--INSERT INTO T_Employee(FNumber,FName,FAge,FSalary)--VALUES('HR002','Tina',25,5200.36);
--INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) --VALUES('IT001','Smith',28,3900);
-- --INSERT INTO T_Employee(FNumber,FAge,FSalary) VALUES('IT002',27,2800)-- SELECT * FROM T_Employee--WHERE NOT(FName LIKE 'S%') AND NOT(FName LIKE 'J%')
-- SELECT * FROM T_Employee--WHERE FName LIKE '[^SJ]%'
--修改已有字段长度
--ALTER TABLE dbo.T_DIRECTORY ALTER COLUMN cname VARCHAR(256) NULL;
--新增字段
--ALTER TABLE T_Employee ADD FSubCompany VARCHAR(20);
--ALTER TABLE T_Employee ADD FDepartment VARCHAR(20);
--UPDATE T_Employee SET FSubCompany='Beijing',FDepartment='Development'
-- WHERE FNumber='DEV001';--UPDATE T_Employee SET FSubCompany='ShenZhen',FDepartment='Development'
-- WHERE FNumber='DEV002';--UPDATE T_Employee SET FSubCompany='Beijing',FDepartment='HumanResource'
-- WHERE FNumber='HR001';--UPDATE T_Employee SET FSubCompany='Beijing',FDepartment='HumanResource'
-- WHERE FNumber='HR002';--UPDATE T_Employee SET FSubCompany='Beijing',FDepartment='InfoTech'
-- WHERE FNumber='IT001';--UPDATE T_Employee SET FSubCompany='ShenZhen',FDepartment='InfoTech'
-- WHERE FNumber='IT002';--UPDATE T_Employee SET FSubCompany='Beijing',FDepartment='Sales'
-- WHERE FNumber='SALES001';--UPDATE T_Employee SET FSubCompany='Beijing',FDepartment='Sales'
-- WHERE FNumber='SALES002';--UPDATE T_Employee SET FSubCompany='ShenZhen',FDepartment='Sales'
-- WHERE FNumber='SALES003';--SELECT FAge FROM T_Employee
-- GROUP BY FAge--SELECT FAge FROM T_Employee
-- WHERE FSubCompany = 'Beijing'-- GROUP BY FAge--SELECT FAge,AVG(FSalary) '平均工资' FROM T_Employee
-- GROUP BY FAge----查看每个年龄段的员工的人数
--SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee
-- GROUP BY FAge----统计每个公司中的工资的总值：
--SELECT FSubCompany,SUM(FSalary) AS FSalarySUM FROM T_Employee
--GROUP BY FSubCompany
----统计每个垂直部门中员工年龄的最大值和最小值：
--SELECT FDepartment,MIN(FAge) AS FAgeMIN,MAX(FAge) AS FAgeMAX FROM T_Employee
-- GROUP BY FDepartment--检索人数多余1个的年龄段
--SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee
-- GROUP BY FAge-- HAVING COUNT(*)>1----在HAVING语句中不能包含未分组的列名
--ROW_NUMBER()函数可以计算每一行数据在结果集中的行号（从1开始计数）
--语法：ROW_NUMBER OVER(排序规则) --SELECT ROW_NUMBER() OVER(ORDER BY FSalary),FNumber,FName,FSalary,FAge FROM T_Employee
--用子查询，返回第3行到第5行的数据
--SELECT * FROM
--(
-- SELECT ROW_NUMBER() OVER(ORDER BY FSalary DESC) AS rownum,-- FNumber,FName,FSalary,FAge FROM T_Employee--) AS a-- WHERE a.rownum>=3 AND a.rownum<=5----常量字段
--SELECT 'CowNew集团',918000000,FName,FAge,FSubCompany FROM T_Employee
----函数LEN 的使用
--SELECT FName, LEN(FName) AS namelength FROM T_Employee
--WHERE FName IS NOT NULL
----函数SUBSTRING 的使用 (起始位置为2,长度为3) (程序中起始位置从0开始,数据库中从1开始)
--SELECT FName, SUBSTRING(FName,2,3) FROM T_Employee
--WHERE FName IS NOT NULL
--正弦函数值的函数SIN和计算绝对值的函数ABS
--允许使用不带FROM子句的SELECT语句来查询这些不属于任何实体表的数据
--SELECT 1--SELECT LEN('abc')
--------------------------------------------------------------------------------------------------------------------------------------------CREATE TABLE T_TempEmployee (FIdCardNumber VARCHAR(20),FName
--VARCHAR(20),FAge INT, PRIMARY KEY (FIdCardNumber))
--INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)--VALUES('1234567890121','Sarani',33);
--INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)--VALUES('1234567890122','Tom',26);
--INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)--VALUES('1234567890123','Yalaha',38);
--INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)--VALUES('1234567890124','Tina',26);
--INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)--VALUES('1234567890125','Konkaya',29);
--INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)--VALUES('1234567890126','Fotifa',46);
--用UNION操作符连接这两个查询语句就可以将两个查询结果集联合为一个结果集
--使用UNION仍然有两个基本的原则需要遵守：一是每个结果集必须有相同的列数；二是每个结果集的列必须类型相容。
--SELECT FNumber,FName,FAge FROM T_Employee
--UNION--SELECT FIdCardNumber,FName,FAge FROM T_TempEmployee ORDER BY FAge
--SELECT FNumber,FSalary FROM T_Employee
--UNION--SELECT '工资合计',SUM(FSalary) FROM T_Employee
-- 默认情况下，UNION运算符合并了两个查询结果集，其中完全重复的数据行被合并为了一条。--如果需要在联合结果集中返回所有的记录而不管它们是否唯一，则需要使用UNION ALL操作符
--SELECT '以下是正式员工的姓名'
--UNION ALL
--SELECT FName FROM T_Employee
--UNION ALL
--SELECT '以下是临时工的姓名'
--UNION ALL
--SELECT FName FROM T_TempEmployee
--------------------------------------------------------------------------------------------------------------------------------------------CREATE TABLE T_Person (FIdNumber VARCHAR(20),
--FName VARCHAR(20),FBirthDay DATETIME,--FRegDay DATETIME,FWeight NUMERIC(10,2))--INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)--VALUES ('123456789120','Tom','1981-03-22','1998-05-01',56.67);
--INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)--VALUES ('123456789121','Jim','1987-01-18','1999-08-21',36.17);
--INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)--VALUES ('123456789122','Lily','1987-11-08','2001-09-18',40.33);
--INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)--VALUES ('123456789123','Kelly','1982-07-12','2000-03-01',46.23);
--INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)--VALUES ('123456789124','Sam','1983-02-16','1998-05-01',48.68);
--INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)--VALUES ('123456789125','Kerry','1984-08-07','1999-03-01',66.67);
--INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)--VALUES ('123456789126','Smith','1980-01-09','2002-09-23',51.28);
--INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)--VALUES ('123456789127','BillGates','1972-07-18','1995-06-19',60.32);
--求绝对值：ABS(-123) --结果为123
--求指数：POWER(A,B)表示A的B次方
--SELECT POWER(9,2) --结果为81--求平方根：SQRT(A)表示根号下A
--SELECT SQRT(9) --结果为3--求随机数：RAND(A) 或 RAND()；A是随机数种子(返回的是0-1之间的小数)
--SELECT RAND(9)--SELECT RAND()--向上取整CEILING(1.1)为2,向下取整FLOOR(1.1)为1
--SELECT CEILING(1.1)--SELECT FLOOR(1.1)--四舍五入：ROUND()(第三个参数默认为0表示四舍五入，如果为0以外的则表示截断表达式)
--SELECT ROUND(3456.123456,-2,0) --结果为3500.000000--SELECT ROUND(3456.123456,-2) --结果为3500.000000----ROUND(m,d)，其中m为待进行四舍五入的数值，而d为计算精度，即进行四舍五入时保留的小数位数
--SELECT ROUND(2.1234,2) --结果为2.1200--ROUND (表达式 ,精度(为负则从小数点左边四舍五入) ,[ function ] )--function默认为0，表达式 将四舍五入。当指定0以外的值时,将截断表达式
--select round(150.75,0,0) --151.00--select round(150.75,1,0) --150.80--select round(150.75,-1,0) --150.00--select round(150.75,0,1) --150.00 即75被去掉了--select round(150.75,1,1) --150.70 即5被去掉了--select round(156.75,-1,1) --150.00 即675被去掉了--求正弦值SIN()，余弦值COS(),反正弦值ASIN(),反余弦值ACOS(),正切值TAN(),反正切值ATAN(),
--求2个变量的反正切：ATAN2(X,Y),函数返回2个变量X和Y的反正切。它类似于计算Y/X的反正切,除了两个参数的符号被用来决定结果的象限。
--求余切COT() (余切是正切TAN的倒数),,,,,,,,,,,,,
--求圆周率π值: SELECT PI() --弧度制转换为角度制:用来将一个数值从弧度制转换为角度制的函数为DEGREES()，[角度制=弧度制*180/π]
--角度制转换为弧度制:用来将一个数值从角度制转换为弧度制的函数为RADIANS()，[弧度制=角度制*π/180]
--比如: SELECT RADIANS(90.0),DEGREES(1.0)--求符号:SIGN(-100) --结果为-1 [SIGN(A)取值1,0,-1]
--求余'%': SELECT 8%3 --结果为2
--求自然对数：LOG(A) 表示以e为底,e的X次方为A，X=lnA --SELECT LOG(2.71828) --结果为0.999999327347282--求以10为底的对数:LOG10(A) --SELECT LOG10(100.00) --结果为2--求幂:POWER(X,Y),表示X的Y次幂 --SELECT POWER(2,5) --结果为32--计算字符串长度:LEN('A') --SELECT LEN('12345.1') --结果为7
--字符串转换为大/小写:UPPER('A'),LOWER('A')
--SELECT LOWER('abcdEf'),UPPER('abcdEf') --结果为abcdef ABCDEF
--截去字符串左侧空格:LTRIM(); LTRIM(RTRIM('A'))表示去掉左右空格,实际上LEN()就已经去掉右空格了--SELECT ' 123 abc ' 字符串,LEN(' 123 abc ') 长度,LEN(LTRIM(' 123 abc ')) 去左空格后长度 --结果为: 123 abc 9 7
--select LEN(RTRIM(' 123 abc ')),LEN(' 123 abc ') --结果为:9 9,系统默认不计算最右边的空格长度
--SELECT ' 123 abc ',LEN(' 123 abc '),LEN(LTRIM(RTRIM(' 123 abc '))),LEN(LTRIM(' 123 abc ')) --结果为: 123 abc 8 7 7
--SELECT SUBSTRING('abcdef111',2,3)
--SELECT CHARINDEX('cd','abcdef') --结果为3
--SELECT REPLACE('abcdefg','cde','123') --结果为ab123fg
--SELECT REPLACE(' abcd e fg',' ','') --结果为abcdefg;[清除了字符串内的空格]
--SELECT LEFT('abcdef',2),RIGHT('abcdef',2) --结果为ab ef
--SELECT ASCII('a'),ASCII('abc')--结果为:97 97 ;如果参数为一个字符串则函数返回第一个字符的ASCII码
--SELECT FName,LEFT(FName,1),ASCII(LEFT(FName,1)),ASCII(FName) FROM T_Person --结果为,比如:Tom T 84 84
--得到一个ASCII码数字对应的字符CHAR(97)为'a'--SELECT CHAR(56),ASCII(8),CHAR(90),ASCII('Z'),CHAR(97),ASCII('a') --结果为:8 56 Z 90 a 97
--SOUNDEX()函数用于计算一个字符串的发音特征值,为一个四个字符的字符串,
--其中第一个字符总是初始字符串中的第一个字符，而其后则是一个三位数字的数值
--SELECT FName, SOUNDEX(FName) FROM T_Person --比如 Tom T200 ;Jim J520
--DIFFERENCE()用来简化两个字符串的发音相似度比较,可以计算两个字符串的发音特征值；
--值越大则表示两个字符串发音相似度越大
--SELECT FName,DIFFERENCE(FName,'Tim') FROM T_Person --比如:Tom 3;Jim 3
--SELECT * FROM T_Person WHERE DIFFERENCE(FName,'Tim')>=3 --结果仅有Tom和Jim两行记录
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SELECT GETDATE() as 当前日期时间 --结果为：2012-04-08 08:48:30.193--SELECT CONVERT(VARCHAR(50),GETDATE(),101) as 当前日期 --结果为：04/08/2012
--SELECT CONVERT(VARCHAR(50),GETDATE(),108) as 当前时间 --结果为：08:52:07
--SELECT CONVERT(VARCHAR(50),GETDATE(),11) as 当前日期 --111 102 23 11 2
--DATEADD()格式为：DATEADD(datepart,number,GETDATE())
/* datepart取值：取值 别名 说明year yy,yyyy 年份quarter qq,q 季度month mm,m 月份dayofyear dy,y 当年度的第几天day dd,d 日week wk,ww 当年度的第几周weekday dw,w 星期几[唯一与DATEDIFF()不同的]hour hh 小时minute mi,n 分second ss,s 秒millisecond ms 毫秒*/--SELECT DATEADD(day, 3,GETDATE()) --结果为：2012-04-11 09:02:50.653 --为计算日期date的3天后的日期
--SELECT DATEADD(day, -3,'2012-04-08 09:02:50') --结果为：2012-04-05 09:02:50.000
----月初、月末
--SELECT DATEADD(MONTH,-1, DATEADD(DAY,1-DATEPART(DAY,GETDATE()),DATEADD(MONTH, 1,GETDATE()))) --2012-07-01 09:33:42.513
--SELECT DATEADD(DAY,-DATEPART(DAY,GETDATE()),DATEADD(MONTH, 1,GETDATE())) --2012-07-31 09:33:42.513
--SELECT CAST(CONVERT(VARCHAR(20), DATEADD(MONTH,-1, DATEADD(DAY,1-DATEPART(DAY,GETDATE()),DATEADD(MONTH, 1,GETDATE()))) ,112) AS DATETIME)--2012-07-01 00:00:00.000
--SELECT CAST(CONVERT(VARCHAR(20),DATEADD(DAY,-DATEPART(DAY,GETDATE()),DATEADD(MONTH, 1,GETDATE())) ,112) AS DATETIME)--2012-07-31 00:00:00.000
--去掉小时分钟秒 --2012-07-18 00:00:00.000
--SELECT CAST(CONVERT(VARCHAR(20), GETDATE(),112) AS DATETIME)
--DATEDIFF()用于指定计算差额时使用的单位,其格式：DATEDIFF(datepart,startdate,enddate)
/* datepart取值：单位 别名 说明year yy,yyyy 年quarter qq,q 季度month mm,m 月dayofyear dy,y 工作日day dd,d 天数week wk,ww 周Hour hh 小时minute mi,n 分钟second ss,s 秒millisecond ms 毫秒*/--SELECT DATEDIFF(day, '2012-04-10','2012-04-08') --结果为：-2
--SELECT DATEDIFF(day, '2012-04-04 0:00:00','2012-04-08 23:59:59') --结果为：4 --可见和HH,MI,SS无关
--SELECT DATEDIFF(week,'2012-04-04','2012-04-08') --结果为：1
--SELECT DATEDIFF(YEAR,'2012-04-04','2012-04-08') --结果为：0
--计算一个日期是星期几DATENAME(datepart,date) --返回一个日期的特定部分，并且尽量用名称来表述这个特定部分
/* datepart取值：可选值 别名 说明Year yy、yyyy 年份Quarter qq,q 季度Month mm,m 月份Dayofyear dy,y 每年的某一日Day dd,d 日期Week wk,ww 星期Weekday dw 工作日Hour hh 小时Minute mi,n 分钟Second ss,s 秒Millisecond ms 毫秒*/ --SELECT DATENAME(Weekday,'2012-04-08') --结果为:星期日 [Weekday 可以换成别名dw]
--取得日期的指定部分
--week 每周六为最后一天，周日为下一周
--Weekday取值1-7，7表示星期六,1表示星期日(按照西方的方式周日为第一天)
--SELECT '2012-01-14' AS '日期',DATENAME(YEAR,'2012-01-14') AS '年份',
-- DATENAME(dayofyear,'2012-01-14') AS '每年中的某一日',-- DATENAME(week,'2012-01-14') AS '星期',-- DATENAME(weekday,'2012-01-14') AS '工作日'----结果为：
----日期 年份 每年中的某一日 星期 工作日
----2012-01-14 2012 14 2 星期六
--返回一个日期的特定部分(返回值是数字),格式为：DATEPART(datepart,date) [datepart的取值与DATENAME相同]--SELECT '2012-01-14' AS '日期',DATEPART(YEAR,'2012-01-14') AS '年份',
-- DATEPART(dayofyear,'2012-01-14') AS '每年中的某一日',-- DATEPART(week,'2012-01-14') AS '星期',-- DATEPART(weekday,'2012-01-14') AS '工作日' ----结果为：
----日期 年份 每年中的某一日 星期 工作日
----2012-01-14 2012 14 2 7
--类型转换函数：CAST(expression AS data_type), CONVERT(data_type,expression) --type为转换的目标类型
--SELECT--CAST('-30' AS INTEGER) as '整型',
--CAST('-30.1' AS float) as '小数',
--CONVERT(DECIMAL(2,1),'3.1415726') as 'decimal',
----decimal[(p[,s])] ,其中p(有效位数),s(小数位数), 0<=s<=p,预设小数位数是0----decimal关键字表示128位数据类型;同浮点型相比,decimal类型具有更高的精度和更小的范围,这使它适合于财务和货币计算。
--CONVERT(DATETIME,'2008-08-08 08:09:10') as '日期'
----结果为：
----整型 小数 decimal 日期-----30 -30.1 3.1 2008-08-08 08:09:10.000
----SELECT FIdNumber,--RIGHT(FIdNumber,3) as 后三位,
--CAST(RIGHT(FIdNumber,3) AS INTEGER) as 后三位的整数形式,
--CAST(RIGHT(FIdNumber,3) AS INTEGER)+1 as 后三位加1,
--CONVERT(INTEGER,RIGHT(FIdNumber,3))/2 as 后三位除以2
--FROM T_Person--COALESCE()函数,主要用来进行空值处理,格式：COALESCE(expression,value1,value2……,valuen) return the first nonnull expression
--COALESCE()函数可以用来完成几乎所有的NULL空值处理,其简化版格式:--ISNULL(check_expression,replace_value),如果check_expression为NULL,则用replace_value替换
--SELECT COALESCE(NULL,NULL,'c',''),ISNULL(NULL,'123') --结果为: c 123 [注意一定要是NULL,空字符''无效]
--NULLIF(expression1,expression2)--SELECT NULLIF('a','ab') --结果为:a --如果两个表达式不等价,则NULLIF返回第一个expression1的值。
--SELECT NULLIF('a','a') --结果为:NULL --如果两个表达式等价,则NULLIF返回NULL
--流程控制函数/流程控制语句 CASE--用法1：
--CASE expression--WHEN value1 THEN returnvalue1
--WHEN value2 THEN returnvalue2
--WHEN value3 THEN returnvalue3
--ELSE defaultreturnvalue--END--用法2：
--CASE--WHEN condition1 THEN returnvalue1
--WHEN condition 2 THEN returnvalue2
--WHEN condition 3 THEN returnvalue3
--ELSE defaultreturnvalue--END----SELECT FName,--(CASE FName WHEN 'Tom' THEN 'GoodBoy'
-- WHEN 'Lily' THEN 'GoodGirl'-- WHEN 'Sam' THEN 'BadBoy'-- WHEN 'Kerry' THEN 'BadGirl'-- ELSE 'Normal' END) as isgood--FROM T_Person----SELECT FName,FWeight,--(CASE WHEN FWeight<40 THEN 'thin'
-- WHEN FWeight>50 THEN 'fat'-- ELSE 'ok' END) as IsNormal--FROM T_Person--结果为：
--FName FWeight IsNormal
--Tom 56.67 fat
--Jim 36.17 thin
--Lily 40.33 ok
--Kelly 46.23 ok
--Sam 48.68 ok
--Kerry 66.67 fat
--Smith 51.28 fat
--BillGates 60.32 fat
--PATINDEX('%pattern%',expression)用来进行模式字串匹配--它返回指定表达式中模式'%pattern%'第一次出现的起始位置；--如果在全部有效的文本和字符数据类型中没有找到该模式,则返回零。在模式中可以使用通配符,比如“_”等
--SELECT PATINDEX('%cde%','abcdefghijklmn') --结果为:3
--SELECT PATINDEX('%_cde%','abcdefghijklmn') --结果为:2 --"_"表示任意单字符
--REPLICATE(str,count)函数用来得到一个子字符串重复了若干次所组成的字符串，[str为子字符串,count为重复次数]--SELECT REPLICATE('abcd_',3) --结果为：abcd_abcd_abcd_
--SELECT REPLICATE('abcd_',CAST(3 AS INT)) --结果为：abcd_abcd_abcd_ [注意复习并活用函数]
--REVERSE(),将一个字符串的顺序颠倒
--SELECT REVERSE('1234567abc') --结果为：cba7654321
----ISDATE(expression)用来确定输入表达式是否为有效日期;如果输入表达式是有效日期,ISDATE返回1;否则返回0
--SELECT--ISDATE(NULL) as T1,
--ISDATE('12/23/2025') as T2, --有效
--ISDATE('1995-10-1a') as T3,
--ISDATE(19920808) as T4, --有效--ISDATE('1/23/95') as T5,
--ISDATE('1995-10-1') as T6, --有效
--ISDATE('19920808') as T7, --有效
--ISDATE(' Abc') as T8
----结果为：
----T1 T2 T3 T4 T5 T6 T7 T8
----0 1 0 1 0 1 1 0
----ISNUMERIC(expression)用来确定表达式是否为有效的数值类型。
----如果表达式的计算值为有效的整数、浮点数、money或decimal类型时,ISNUMERIC返回1;否则返回0
--SELECT--ISNUMERIC(NULL) as T1,
--ISNUMERIC('13/43/3425') as T2,
--ISNUMERIC('30a.8') as T3,
--ISNUMERIC('1/23/95') as T4,
--ISNUMERIC(19920808) as T5, --有效--ISNUMERIC('3E-3') as T6, --有效
--ISNUMERIC('19920808') as T7, --有效
--ISNUMERIC('-30.3') as T8 --有效
----结果为：
----T1 T2 T3 T4 T5 T6 T7 T8
----0 0 0 0 1 1 1 1
----辅助功能函数
----APP_NAME()函数返回当前会话的应用程序名称；
----CURRENT_USER函数（注意这个函数不能带括号调用）返回当前登陆用户名；
----HOST_NAME()函数返回工作站名
--SELECT APP_NAME() as '应用程序名称',
--CURRENT_USER as '当前用户',
--HOST_NAME() as '主机名'
----结果为：
----应用程序名称 当前用户 主机名
----Microsoft SQL Server Management Studio - 查询 dbo PC_ABC----生成GUID:全局唯一字符串的函数NEWID()
--SELECT NEWID() --结果为：65E05167-EB0D-4D08-8695-ED5B2E1A86AB--IF EXISTS( SELECT * FROM T_Person)
--DROP TABLE T_Person
-------------------------------------------------------------------------------------------------索引用来提高数据的检索速度，而约束则用来保证数据的完整性。---------------------------------------------------------------------------------------------------------------- --CREATE TABLE T_Person2 (FNumber VARCHAR(20),
--FName VARCHAR(20),FAge INT)
------CREATE INDEX 索引名 ON 表名(字段1, 字段2,……字段n)----创建索引[允许创建1到多个索引]
--CREATE INDEX idx_person_name ON T_Person2(FName)
--CREATE INDEX idx_person_nameage ON T_Person2(FName,FAge)
----删除索引
--DROP INDEX T_Person2.idx_person_name;
--DROP INDEX T_Person2.idx_person_nameage;
----DROP TABLE T_Person2--约束：非空约束；唯一约束； CHECK约束；主键约束；外键约束
------【非空约束NOT NULL】:--CREATE TABLE T_Person2 (FNumber VARCHAR(20) NOT NULL,FName VARCHAR(20),FAge INT)
----使用Insert语句进行测试：
--INSERT INTO T_Person2 (FNumber, FName, FAge) VALUES ( NULL , 'kingchou', 20)
----执行结果为：不能将值NULL插入列'FNumber'，表'TestSQL.dbo.T_Person2'；列不允许有Null值。INSERT 失败。
--INSERT INTO T_Person2 (FNumber, FName, FAge) VALUES ( '1' , 'kingchou', 20)--结果为：(1 行受影响)
--DROP TABLE T_Person2
----【唯一约束/UNIQUE约束】，用于防止一个特定的列中两个记录具有相同值,分为单字段唯一约束与复合唯一约束--CREATE TABLE T_Person2 (FNumber VARCHAR(20) UNIQUE,FName VARCHAR(20),FAge INT)
----INSERT INTO T_Person2 (FNumber, FName, FAge)VALUES ( '1' , 'kingchou', 20);--INSERT INTO T_Person2 (FNumber, FName, FAge)VALUES ( '2' , 'stef', 22);
--INSERT INTO T_Person2 (FNumber, FName, FAge)VALUES ( '3' , 'long', 26);
--INSERT INTO T_Person2 (FNumber, FName, FAge)VALUES ( '4' , 'yangzk', 27);
--INSERT INTO T_Person2 (FNumber, FName, FAge)VALUES ( '5' , 'beansoft', 26);
----INSERT INTO T_Person2 (FNumber, FName, FAge) VALUES ( '2' , 'kitty', 20) --注意FNumber为UNIQUE类型--结果为：违反了UNIQUE KEY约束'UQ__T_Person__3EB5391909DE7BCC'。不能在对象'dbo.T_Person2'中插入重复键。
--DROP TABLE T_Person2
------【复合唯一约束】是建立在多个字段上的约束，被约束的字段在不能同时重复----CONSTRAINT 约束名UNIQUE(字段1,字段2……字段n)--CREATE TABLE T_Person2(FNumber VARCHAR(20),FDepartmentNumber VARCHAR(20),FName VARCHAR(20),FAge INT,
--CONSTRAINT unic_dep_num UNIQUE(FNumber,FDepartmentNumber))--在FNumber,FDepartmentNumber这2个字段上设置复合唯一约束
------INSERT INTO T_Person2(FNumber,FDepartmentNumber,FName,FAge)VALUES ('1','dev001','kingchou',20);--INSERT INTO T_Person2(FNumber,FDepartmentNumber,FName,FAge)VALUES ('2','dev001','stef',22);
--INSERT INTO T_Person2(FNumber,FDepartmentNumber,FName,FAge)VALUES ('1','sales001','long',26);
--INSERT INTO T_Person2(FNumber,FDepartmentNumber,FName,FAge)VALUES ('2','sales001','yangzk',27);
--INSERT INTO T_Person2(FNumber,FDepartmentNumber,FName,FAge)VALUES ('3','sales001','beansoft',26);
----INSERT INTO T_Person2(FNumber,FDepartmentNumber,FName,FAge)VALUES('2','sales001','daxia',30);----结果为：违反了UNIQUE KEY 约束'unic_dep_num'。不能在对象'dbo.T_Person2'中插入重复键。
----原因是，FNumber,FDepartmentNumber这2个字段是复合唯一约束，不能同时有重复的记录
--DROP TABLE T_Person2
------在一个表中添加多个复合唯一约束，并为其指定不同的名称--CREATE TABLE T_Person2(FNumber VARCHAR(20),FDepartmentNumber VARCHAR(20),FName VARCHAR(20),FAge INT,
--CONSTRAINT unic_1 UNIQUE(FNumber,FDepartmentNumber),
--CONSTRAINT unic_2 UNIQUE(FDepartmentNumber, FName))
----ALTER TABLE T_Person2 ADD CONSTRAINT unic_3 UNIQUE(FName, FAge)--在已创建好的数据表上添加新的唯一约束----ALTER TABLE T_Person2 DROP CONSTRAINT unic_1; --在已经创建好的数据表上删除唯一约束--ALTER TABLE T_Person2 DROP CONSTRAINT unic_2; --在已经创建好的数据表上删除唯一约束
--ALTER TABLE T_Person2 DROP CONSTRAINT unic_3; --在已经创建好的数据表上删除唯一约束
--DROP TABLE T_Person2
------【CHECK约束】，在表创建的时候，字段后面添加CHECK(条件)--CREATE TABLE T_Person2(FNumber VARCHAR(20),FName VARCHAR(20),
--FAge INT CHECK(FAge>0),FWorkYear INT CHECK(FWorkYear>0))
----INSERT INTO T_Person2(FNumber,FName,FAge,FWorkYear)VALUES('001','John',25,-3)----结果会报错，原因在于，有约束CHECK(FWorkYear>0)，使得工作年限要大于0
--INSERT INTO T_Person2(FNumber,FName,FAge,FWorkYear)VALUES('001','John',25,3)--结果为：(1 行受影响)
--DROP TABLE T_Person2
------也可以在CHECK中使用函数--CREATE TABLE T_Person2(FNumber VARCHAR(20) CHECK(LEN(FNumber)>12),
--FName VARCHAR(20),FAge INT CHECK(FAge>0),FWorkYear INT CHECK(FWorkYear>0))
--DROP TABLE T_Person2
----直接在列定义中通过CHECK子句添加CHECK约束的方式的缺点是约束条件不能引用其他列--CREATE TABLE T_Person2(FNumber VARCHAR(20),FName VARCHAR(20),FAge INT,FWorkYear INT CHECK(FWorkYear<FAge))
----结果为：表'T_Person2'的列'FWorkYear'的列CHECK约束引用了另一列。
------CHECK子句中是不能引用其他列,如果希望CHECK子句中的条件语句中使用其他列,则必须在CREATE TABLe的末尾使用CONSTRAINT关键字定义它。----语法为:CONSTRAINT 约束名 CHECK(约束条件)
--CREATE TABLE T_Person2(FNumber VARCHAR(20),FName VARCHAR(20),FAge INT,FWorkYear INT,
--CONSTRAINT ck_1 CHECK(FWorkYear<FAge))
----测试语句：(结果会报错,因为违反了约束条件)
--INSERT INTO T_Person2(FNumber,FName,FAge,FWorkYear)VALUES('001','John',25, 30)
----ALTER TABLE T_Person2 ADD CONSTRAINT ck_2 CHECK(FAge>14)--ALTER TABLE T_Person2 DROP CONSTRAINT ck_2;
--DROP TABLE T_Person2
----【主键约束】在字段定义后添加PRIMARY KEY 关键字即可----除了由单一字段组成的主键之外，还可以由多个字段来组成主键，这样的主键被称为复合主键或者联合主键
--CREATE TABLE T_Person2(FNumber VARCHAR(20),FName VARCHAR(20),FAge INT,
--CONSTRAINT pk_1 PRIMARY KEY(FNumber,FName))--其中，将字段FNumber和FName设置为复合主键
----DROP TABLE T_Person2
----CREATE TABLE T_Person2(FNumber VARCHAR(20) NOT NULL,FName VARCHAR(20) NOT NULL,FAge INT)--ALTER TABLE T_Person2 ADD CONSTRAINT pk_1 PRIMARY KEY(FNumber,FName)
----在创建表时,PRIMARY KEY()中的字段FNumber,FName均要定义为NOT NULL,否则报错
------ALTER TABLE T_Person2 DROP CONSTRAINT pk_1;--DROP TABLE T_Person2
------【外键约束】 FOREIGN KEY 外键字段REFERENCES 外键表名(外键表的主键字段)----比如：FOREIGN KEY (FAuthorId) REFERENCES T_AUTHOR(FId)表示本表中FAuthorId是外键,对应表T_AUTHOR表中的(主键)FId
--CREATE TABLE T_AUTHOR(FId VARCHAR(20) PRIMARY KEY,FName VARCHAR(100),FAge INT,FEmail VARCHAR(20));
--CREATE TABLE T_Book(FId VARCHAR(20) PRIMARY KEY,FName VARCHAR(100),FPageCount INT,FAuthorId VARCHAR(20),
--FOREIGN KEY (FAuthorId) REFERENCES T_AUTHOR(FId));--
----INSERT INTO T_AUTHOR(FId,FName,FAge,FEmail)VALUES('1','lily',20,'lily@cownew.com');
--INSERT INTO T_AUTHOR(FId,FName,FAge,FEmail)VALUES('2','kingchou',23,'kingchou@cownew.com');
--INSERT INTO T_AUTHOR(FId,FName,FAge,FEmail)VALUES('3','stef',28,'stef@cownew.com');
--INSERT INTO T_AUTHOR(FId,FName,FAge,FEmail)VALUES('4','long',26,'long@cownew.com');
--INSERT INTO T_AUTHOR(FId,FName,FAge,FEmail)VALUES('5','badboy',31,'badboy@cownew.com');
--INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES('1','About Java',300,'1');
--INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES('2','Inside Ruby',330,'2');
--INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES('3','Inside Curses',200,'5');
--INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES('4','Python InAction',450,'4');
--INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES('5','WPF Anywhere',250,'1');
--INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES('6','C# KickStart',280,'3');
--INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES('7','Compling',800,'1');
--INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES('8','Faster VB.Net',300,'5');
--测试语句：结果会报错，因为T_AUTHOR表中没有FId为9的，有外键约束限制
--INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES('9','AboutWinCE',320,'9');--报错
--INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES('9','AboutWinCE',320,'3');--正常
----DELETE FROM T_AUTHOR WHERE FName='badboy'--应当先删除T_Book书，后删除作者T_AUTHOR----在删除一个表中的数据的时候，如果有其他的表中存在指向这些数据的外键关系，那么这个删除操作会是失败的
----ALTER TABLE T_Book ADD CONSTRAINT fk_book_author FOREIGN KEY (FAuthorId) REFERENCES T_AUTHOR(FId)----本表中的外键对应表T_AUTHOR中的FId
----DROP TABLE T_Book;--DROP TABLE T_AUTHOR;
----------------------------------------------------------------------------------------------------------表连接:内连接INNER JOIN,外连接OUTTER JOIN,LEFT JOIN,RIGHT JOIN,交叉连接CROSS JOIN,联合连接UNION JOIN------------------------------------------------------------------------------------------------------------CREATE TABLE T_Customer(FId INT NOT NULL,FName VARCHAR(20) NOT NULL,FAge INT,PRIMARY KEY(FId))--CREATE TABLE T_OrderType(FId INT NOT NULL,FName VARCHAR(20) NOT NULL,PRIMARY KEY (FId))
--CREATE TABLE T_Order(FId INT NOT NULL,FNumber VARCHAR(20) NOT NULL,
-- FPrice NUMERIC(10,2),FCustomerId INT, FTypeId INT,PRIMARY KEY(FId))----INSERT INTO T_Customer(FId,FName,FAge)VALUES(1,'TOM',21);--INSERT INTO T_Customer(FId,FName,FAge)VALUES(2,'MIKE',24);
--INSERT INTO T_Customer(FId,FName,FAge)VALUES(3,'JACK',30);
--INSERT INTO T_Customer(FId,FName,FAge)VALUES(4,'TOM',25);
--INSERT INTO T_Customer(FId,FName,FAge)VALUES(5,'LINDA',NULL);
--INSERT INTO T_OrderType(FId,FName)VALUES(1,'MarketOrder');
--INSERT INTO T_OrderType(FId,FName)VALUES(2,'LimitOrder');
--INSERT INTO T_OrderType(FId,FName)VALUES(3,'Stop Order');
--INSERT INTO T_OrderType(FId,FName)VALUES(4,'StopLimit Order');
--INSERT INTO T_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)VALUES(1,'K001',100,1,1);
--INSERT INTO T_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)VALUES(2,'K002',200,1,1);
--INSERT INTO T_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)VALUES(3,'T003',300,1,2);
--INSERT INTO T_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)VALUES(4,'N002',100,2,2);
--INSERT INTO T_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)VALUES(5,'N003',500,3,4);
--INSERT INTO T_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)VALUES(6,'T001',300,4,3);
--INSERT INTO T_Order(FId,FNumber,FPrice,FCustomerId, FTypeId)VALUES(7,'T002',100,NULL,1);
--SELECT * FROM T_Customer
--SELECT * FROM T_OrderType
--SELECT * FROM T_Order
--表连接使用JOIN关键字将一个或者多个表按照彼此间的关系连接为一个结果集。
----【INNER JOIN】: INNER JOIN table_name ON condition,其中table_name为被关联的表名,condition则为进行连接时的条件 --SELECT FNumber,FPrice FROM T_Order INNER JOIN T_Customer ON FCustomerId= T_Customer.FId
----WHERE T_Customer.FName='TOM'
------INNER JOIN(检索所有的表A以及它们对应的表B的相关信息) 同时支持不等值连接----下面的语句做了笛卡尔运算
--SELECT T_Order.FNumber,T_Order.FPrice,T_Customer.FName,T_Customer.FAge FROM T_Order
-- INNER JOIN T_Customer ON T_Order.FPrice< T_Customer.FAge*5---- 添加等值连接匹配条件后，消除了许多类似重复的数据--SELECT T_Order.FNumber,T_Order.FPrice,T_Customer.FName,T_Customer.FAge FROM T_Order
-- INNER JOIN T_Customer ON T_Order.FPrice< T_Customer.FAge*5 and T_Order.FCustomerId=T_Customer.FId------以下2行sql执行的结果是一样的：(可以加深对INNER JOIN的理解)[注①]--SELECT o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAge--FROM T_Order o INNER JOIN T_Customer c ON o.FCustomerId=c.FId
--SELECT o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAge--FROM T_Order o ,T_Customer c where o.FCustomerId=c.FId
----【CROSS JOIN】：支持显示和隐式2种方式(尽量用隐式)-- SELECT T_Customer.FId, T_Customer.FName, T_Customer.FAge,T_Order.FId, T_Order.FNumber, T_Order.FPrice--FROM T_Customer CROSS JOIN T_Order --显示(DB2不支持)
--SELECT T_Customer.FId, T_Customer.FName, T_Customer.FAge,T_Order.FId, T_Order.FNumber, T_Order.FPrice --FROM T_Customer, T_Order --隐式(都支持)----【自连接】--SELECT o1.FNumber,o1.FTypeId,--o1.FPrice,-- o2.FNumber,o2.FTypeId --o2.FPrice,--FROM T_Order o1 INNER JOIN T_Order o2
--ON o1.FTypeId=o2.FTypeId and o1.FId>o2.FId --如果此处为o1.FId<>o2.FId,则会出现A匹配B，B匹配A的重复记录
--ORDER BY o1.FNumber
----结果为4行不重复的记录
--【外部连接】 主要就是用来解决空值匹配问题,(见【INNER JOIN】注①,就漏掉了空值的一行记录)
--外部连接不需要两个表具有匹配记录，这样可以指定某个表中的记录总是放到结果集中。
--根据哪个表中的记录总是放到结果集中，外部连接分为三种类型：
-- --外连接 OUTER JOIN
--在外连接中，不仅包括那些满足条件的数据，而且某些表不满足条件的数据也会显示在结果集中。
--也就是说，外连接只限制其中一个表的数据，而不限制另外一个表中的数据。
--外连接分: 左外部连接、右外部连接、全外部连接：
--1）左外部连接 LEFT OUTER JOIN
--左外部连接包括了左表A中全部不满足条件的数据，对应另外一个表B中的数据为NULL。
--select *from 表名A left outer join 表名B on 条件表达式
--2）右外部连接 RIGHT OUTER JOIN
--右外部连接包括了右表B中全部不满足条件的数据，对应另外一个表A中的数据为NULL。
--select *from 表名A right outer join 表名B on 条件表达式
--3）全外部连接 FULL OUTER JOIN
--全外部连接包括了左表A和右表B中所有不满足条件的数据，这些数据在另外一个表中的对应值是NULL。
--select *from 表名A full outer join 表名B on 条件表达式
--其中,左表和右表是相对于JOIN关键字来说的,比如上述表A则为左表,B则为右表
---- 通俗的讲：-- A left join B 的连接的记录数与A表的记录数相同 -- A right join B 的连接的记录数与B表的记录数相同 -- A left join B 等价B right join A ----用来实现“查询每张订单的订单号、价格、对应的客户姓名以及客户年龄，如果没有对应的客户，则在客户信息处显示空格”：--SELECT o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAge,c.FId--FROM T_Order o LEFT OUTER JOIN T_Customer c ON o.FCustomerId=c.FId --则左表T_Order为主表
/*LeftOuterJoin的结果：K001 100.00 1 TOM 21 1K002 200.00 1 TOM 21 1T003 300.00 1 TOM 21 1N002 100.00 2 MIKE 24 2N003 500.00 3 JACK 30 3T001 300.00 4 TOM 25 4T002 100.00 NULL NULL NULL NULLT_Order:FId FNumber FPrice FCustomerId1 K001 100.00 12 K002 200.00 13 T003 300.00 14 N002 100.00 25 N003 500.00 36 T001 300.00 47 T002 100.00 NULLT_Customer:FId FName FAge1 TOM 212 MIKE 243 JACK 304 TOM 255 LINDA NULL*/----使用右外部连接查询每张订单的信息以及对应的客户信息：
--SELECT o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAge FROM T_Order o
-- RIGHT OUTER JOIN T_Customer c ON o.FCustomerId=c.FId --则右表T_Customer为主表----在数据库中没有利用清晰、规范的约束来防范错误数据情况下，全外连接变得非常有用，可以使用它来清理数据库中的数据。----使用全外部连接查询每张订单的信息以及对应的客户信息：
--SELECT o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAge FROM T_Order o FULL OUTER JOIN T_Customer c
--ON o.FCustomerId=c.FId------SELECT o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAge FROM T_Order o--LEFT OUTER JOIN T_Customer c ON o.FCustomerId=c.FId
--UNION--SELECT o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAge FROM T_Order o
--RIGHT OUTER JOIN T_Customer c ON o.FCustomerId=c.FId
--以上2句执行的结果是一样的
--SELECT * FROM T_Order
--SELECT * T_OrderType;--SELECT * FROM T_Customer
--DROP TABLE T_Order;
--DROP TABLE T_OrderType;
--DROP TABLE T_Customer;
/* 补充说明1、交叉连接CROSS JOIN如果不带条件子句，交叉连接将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积；select *from 表名 cross join 表名 on 条件表达式 2、内连接 INNER JOIN内连接仅返回那些满足连接条件的数据行。在内连接中，使用INNER JOIN连接运算符和ON关键字指定连接条件，如果没有指定连接条件，则和交叉连接的结果一样。在SQL语句中，如果JOIN关键字前面没有明确指定连接类型，那么默认为内连接。select *from 表名inner join 表名 on 条件表达式*/-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------子查询：--CREATE TABLE T_Reader (FId INT NOT NULL ,FName VARCHAR(50),FYearOfBirth INT,FCity VARCHAR(50),FProvince VARCHAR(50), FYearOfJoin INT)
--CREATE TABLE T_Book (FId INT NOT NULL ,FName VARCHAR(50),FYearPublished INT,FCategoryId INT)
--CREATE TABLE T_ReaderFavorite (FCategoryId INT,FReaderId INT)
--CREATE TABLE T_Category (FId INT NOT NULL ,FName VARCHAR(50))
----INSERT INTO T_Category(FId,FName)VALUES(1,'Story');--INSERT INTO T_Category(FId,FName)VALUES(2,'History');
--INSERT INTO T_Category(FId,FName)VALUES(3,'Theory');
--INSERT INTO T_Category(FId,FName)VALUES(4,'Technology');
--INSERT INTO T_Category(FId,FName)VALUES(5,'Art');
--INSERT INTO T_Category(FId,FName)VALUES(6,'Philosophy');
--INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)VALUES(1,'Tom',1979,'TangShan','Hebei',2003);
--INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)VALUES(2,'Sam',1981,'LangFang','Hebei',2001);
--INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)VALUES(3,'Jerry',1966,'DongGuan','GuangDong',1995);
--INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)VALUES(4,'Lily',1972,'JiaXing','ZheJiang',2005);
--INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)VALUES(5,'Marry',1985,'BeiJing','BeiJing',1999);
--INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)VALUES(6,'Kelly',1977,'ZhuZhou','HuNan',1995);
--INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)VALUES(7,'Tim',1982,'YongZhou','HuNan',2001);
--INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)VALUES(8,'King',1979,'JiNan','ShanDong',1997);
--INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)VALUES(9,'John',1979,'QingDao','ShanDong',2003);
--INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)VALUES(10,'Lucy',1978,'LuoYang','HeNan',1996);
--INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)VALUES(11,'July',1983,'ZhuMaDian','HeNan',1999);
--INSERT INTO T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FYearOfJoin)VALUES(12,'Fige',1981,'JinCheng','ShanXi',2003);
--INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)VALUES(1,'About J2EE',2005,4);
--INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)VALUES(2,'Learning Hibernate',2003,4);
--INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)VALUES(3,'Two Cites',1999,1);
--INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)VALUES(4,'Jane Eyre',2001,1);
--INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)VALUES(5,'Oliver Twist',2002,1);
--INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)VALUES(6,'History of China',1982,2);
--INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)VALUES(7,'History of England',1860,2);
--INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)VALUES(8,'History of America',1700,2);
--INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)VALUES(9,'History of TheWorld',2008,2);
--INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)VALUES(10,'Atom',1930,3);
--INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)VALUES(11,'RELATIVITY',1945,3);
--INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)VALUES(12,'Computer',1970,3);
--INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)VALUES(13,'Astronomy',1971,3);
--INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)VALUES(14,'How To Singing',1771,5);
--INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)VALUES(15,'DaoDeJing',2001,6);
--INSERT INTO T_Book(FId,FName,FYearPublished,FCategoryId)VALUES(16,'Obedience toAuthority',1995,6);
--INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)VALUES(1,1);
--INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)VALUES(5,2);
--INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)VALUES(2,3);
--INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)VALUES(3,4);
--INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)VALUES(5,5);
--INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)VALUES(1,6);
--INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)VALUES(1,7);
--INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)VALUES(4,8);
--INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)VALUES(6,9);
--INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)VALUES(5,10);
--INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)VALUES(2,11);
--INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)VALUES(2,12);
--INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)VALUES(1,12);
--INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)VALUES(3,1);
--INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)VALUES(1,3);
--INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)VALUES(4,4);
----几个简单的测试语句：
--SELECT 1 AS f1,2,(SELECT MIN(FYearPublished) FROM T_Book),(SELECT MAX(FYearPublished) FROM T_Book) AS f4
--SELECT 1 AS f1,2,(SELECT FYearPublished FROM T_Book)
--SELECT 1 AS f1,2,(SELECT MAX(FYearPublished),MIN(FYearPublished) FROM T_Book)
--SELECT 1 AS f1,2,(SELECT FYearPublished FROM T_Book WHERE FYearPublished<1750)
--SELECT T_Reader.FName,t2.FYearPublished,t2.FName--FROM T_Reader,--(SELECT * FROM T_Book WHERE FYearPublished < 1800) t2 WHERE T_Reader.FYearOfJoin<1997
--注意上1句与下2句的关系
--SELECT * FROM T_Reader WHERE T_Reader.FYearOfJoin<1997
--SELECT * FROM T_Book WHERE FYearPublished < 1800
--SELECT T_Reader.FName,t2.FYear,t2.FName ,t2.F3 FROM T_Reader,--注意涉及到子查询的别名的使用,如t2.F3
--(SELECT FYearPublished AS FYear,FName,1+2 as F3 FROM T_Book WHERE FYearPublished < 1800) t2 WHERE T_Reader.FYearOfJoin<1997
----SELECT FId,FName,--(
-- SELECT MAX(FYearPublished) --相当于常数-- FROM T_Book-- WHERE T_Book. FCategoryId= T_Category.FId--)
--FROM T_Category--各种子查询举例:
--SELECT FReaderId FROM T_ReaderFavorite
-- WHERE FCategoryId=(SELECT FId FROM T_Category WHERE FName='Story')----SELECT T_Category.FId,MIN(T_Book.FYearPublished) FROM T_Category--INNER JOIN T_Book ON T_Category.FId=T_Book.FCategoryId GROUP BY T_Category.FId
----SELECT T_Category.FId,T_Book.FName,MIN(T_Book.FYearPublished) FROM T_Category--INNER JOIN T_Book ON T_Category.FId=T_Book.FCategoryId GROUP BY T_Category.FId,T_Book.FName
----SELECT T_Category.FId, T_Book.FName,T_Book.FYearPublished FROM T_Category--INNER JOIN T_Book ON T_Category.FId=T_Book.FCategoryId
--WHERE T_Book.FYearPublished=--(SELECT MIN(T_Book.FYearPublished) FROM T_Book WHERE T_Book.FCategoryId=T_Category.FId)
----ANY关键字("=ANY"等价于IN,而“<>ANY”则等价于NOT IN); SOME和ANY类似--SELECT * FROM T_Reader WHERE FYearOfJoin =ANY(select FYearPublished FROM T_Book)
--SELECT * FROM T_Reader WHERE FYearOfJoin IN(select FYearPublished FROM T_Book)
--(另外ANY后面也可以使用>,<,=,>=,<=)
------和IN不同，ANY,ALL均不能与固定的集合相匹配--SELECT * FROM T_Book WHERE FYearPublished<ANY(2001,2003,2005)--该句是错误的
--SELECT * FROM T_Book WHERE FYearPublished<ALL(2001,2003,2005)--该句是错误的
--SELECT * FROM T_Book WHERE FYearPublished IN(2001,2003,2005) --该句正确
----ALL关键字: --以下2行执行结果相同
--SELECT * FROM T_Book WHERE FYearPublished<ALL(SELECT FYearOfJoin FROM T_Reader)
--SELECT * FROM T_Book WHERE FYearPublished<(SELECT MIN(FYearOfJoin) FROM T_Reader)
--EXISTS就是用来测试子查询的结果是否为空，如果结果集为空则匹配结果为false,否则为true。
--EXIST的几种用法:
--SELECT * FROM T_Book WHERE EXISTS (SELECT * FROM T_Reader WHERE FProvince='ShanDong')
--SELECT * FROM T_Book WHERE EXISTS(SELECT * FROM T_Reader WHERE FProvince='YunNan')
----SELECT * FROM T_Category WHERE EXISTS(SELECT * FROM T_Book WHERE -- T_Book.FCategoryId = T_Category.FId AND T_Book.FYearPublished<1950)----即对SELECT * FROM T_Category按照FId筛选,FId=FCategoryId且---- 满足SELECT * FROM T_Book WHERE T_Book.FYearPublished<1950的所有数据----其实子查询完全可以应用在INSERT、UPDATE 以及DELETE 等语句中----INSERT..VALUES, INSERT..SELECT
--CREATE TABLE T_ReaderFavorite2 (FCategoryId INT,FReaderId INT)--建表
----使用INSERT..SELECT..--INSERT INTO T_ReaderFavorite2(FCategoryId,FReaderId)--SELECT FCategoryId,FReaderId FROM T_ReaderFavorite
----DELETE FROM T_ReaderFavorite2 --清空记录,保留表----插入前,对数据进行预处理--INSERT INTO T_ReaderFavorite2(FCategoryId,FReaderId)--SELECT FCategoryId,--(CASE WHEN FReaderId<=10 THEN FReaderId
-- ELSE FReaderId- FCategoryId-- END)--FROM T_ReaderFavorite----DROP TABLE T_ReaderFavorite2 --删除表--INSERT INTO T_ReaderFavorite(FCategoryId,FReaderId)--SELECT 1,FId FROM T_Reader WHERE NOT EXISTS(SELECT * FROM T_ReaderFavorite WHERE --1-12中,不存在以下几个的
--T_ReaderFavorite.FCategoryId=1 AND T_ReaderFavorite.FReaderId= T_Reader.FId)--1,6,7,12,3----结果为:2,4,5,8,9,10,11
----即使重复执行这个SQL 语句也都不会添加新的记录,因为这个SQL语句已经对数据的重复做了检查
--UPDATE T_Book SET FYearPublished=(SELECT MAX(FYearPublished) FROM T_Book)
----UPDATE b1 SET b1.FYearPublished=2008 FROM T_Book b1--WHERE(SELECT COUNT(*) FROM T_Book b2 WHERE b1.FCategoryId=b2.FCategoryId)>=3
----update指定表的别名的用法
----update a set a.name=b.name from table1 a inner join table2 b on a.id=b.id
--DELETE b1 FROM T_Book b1
--WHERE(SELECT COUNT(*) FROM T_Book b2 WHERE b1.FCategoryId=b2.FCategoryId)>=3 --把数据为'2008'的都删除了
----删除用到过的表--DROP TABLE T_Reader;
--DROP TABLE T_Book;
--DROP TABLE T_Category;
--DROP TABLE T_ReaderFavorite;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------数据库系统的差异性：--整数类型：
--MYSQL 中整数相关的类型有tinyint、smallint、mediumint、int、integer和bigint；
--MSSQLServer 中整数相关的类型有bit、int、smallint、tinyint 和bigint；
--Oracle中整数相关的类型有number；
--DB2中整数相关的类型有smallint、integer 和bigint。--数值类型：
--MYSQL中数值相关的类型有float、double、real、decimal和numeric；
--MSSQLServer 中数值相关的类型有decimal、numeric、money、smallmoney 、float 和real；
--Oracle中数值相关的类型有number；
--DB2中数值相关的类型有decimal、numeric、real和double。--字符类型：
--MYSQL中字符相关的类型有char、varchar、tinytext、text、mediumtext、longtext、enum和set；--MSSQLServer 中字符相关的类型有char、varchar、text、nchar、nvarchar和ntext；
--Oracle中字符相关的类型有char、varchar2、nvarchar2、clob 和nclob；--DB2 中字符相关的类型有CHARACTER、VARCHAR、LONG VARCHAR、CLOB、GRAPHIC、VARGRAPHIC和LONG VARGRAPHIC。
--日期时间类型：
--MYSQL 中日期时间相关的类型有date、time、datetime、timestamp和year；--MSSQLServer 中日期时间相关的类型有datetime、smalldatetime 和timestamp；
--Oracle 中日期时间相关的类型有date 和timestamp；
--DB2 中日期时间相关的类型有DATE、TIME 和TIMESTAMP。--二进制类型：
--MYSQL、Oracle和DB2都支持Blob类型，
--MSSQLServer中支持image类型。
----对字符串拼接的支持：--MYSQL：函数CONCAT支持一个或者多个参数,比如CONCAT('Hello',1,'World')；
-- 函数CONCAT_WS可以在待拼接的字符串之间加入指定的分隔符，比如CONCAT_WS ('Hello',1,'World')。--MSSQLServer：可直接使用加号“+”来拼接字符串，比如'Hello'+'World'。
--Oracle:使用“||”进行字符串拼接,比如'Hello'||'World'；
-- CONCAT()进行字符串拼接,与MYSQL 的CONCAT()不同,Oracle的CONCAT()只支持两个参数，不支持两个以上字符串的拼接。--DB2： 使用“||”进行字符串拼接，比如'Hello'||'World'。
----限制结果集行数:--SELECT * FROM T_Employee ORDER BY FSalary DESC LIMIT 2,5 --MYSQL中
--select top 5 * from T_Employee order by FSalary Desc； --MSSQLServer中
--SELECT ROW_NUMBER() OVER(ORDER BY FSalary),FNumber,FName,FSalary,FAge FROM T_Employee--MSSQLSERVER中
----SELECT * FROM T_Employee WHERE rownum<=6 ORDER BY FSalary DESC --ORACLE中,rownum为系统关键字
--SELECT * FROM T_Employee ORDER BY FSalary DESC FETCH FIRST 6 ROWS ONLY --DB2中,FETCH提取结果集的前N行
----删除索引:--MYSQL: DROP INDEX idx1 ON T_Person
--MSSQLServer: DROP INDEX T_Person.idx1
--Oracle,DB2: DROP INDEX idx1
----取数据库信息：--MSSQLServer中：
--SELECT APP_NAME()--函数返回当前会话的应用程序名称；结果为:Microsoft SQL Server Management Studio - 查询
--SELECT CURRENT_USER --函数（注意这个函数不能带括号调用）返回当前登陆用户名；结果为:dbo
--SELECT HOST_NAME() --函数返回工作站名。结果为:PC_JX--MYSQL中：
--DATABASE()函数返回当前数据库名；--VERSION()函数以一个字符串形式返回MySQL 服务器的版本；
--USER()函数（这个函数还有SYSTEM_USER、SESSION_USER两个别名）返回当前MySQL 用户名。--MSSQLSERVER查询当前数据库的名字：结果为：dbname 为 TestSQL
--SELECT dbname =case when dbid = 0 then null
-- when dbid <> 0 then db_name(dbid)--end--from master..sysprocesses--where spid=@@SPID --@@SPID表示当前进程--【取得当前数据库中的所有表】 【....................................................】
--SELECT name FROM sysobjects where xtype='U' --MSSQLSERVER中(sysobjects为系统表名)
--SHOW TABLES --MYSQL中
--select Object_Name from all_objects where Object_Type='TABLE' --Oracle中
--SELECT TABNAME FROM syscat.tables where TYPE='T' --DB2中
--取得指定Schema下的表: (假设Schema名为demoschema)
--SELECT name FROM demoschema.sysobjects where xtype='U'--MSSQL中(其中xtype字段等于U的记录为表定义)
--SHOWTABLES FROM demoschema --MYSQL中--select Object_Name from all_objects where Object_Type='TABLE' and OWNER='demoschema'--ORACLE中
--SELECT TABNAME FROM syscat.tables where TYPE='T' and TABSCHEMA='demoschema' --DB2中
--【取得指定表的字段定义】
--MSSQLSERVER中：
--SELECT syscols.name AS COLUMN_NAME,
-- st.name AS DATA_TYPE,-- syscomm.text AS DATA_DEFAULT,-- syscols.isnullable AS NULLABLE--FROM syscolumns syscols-- LEFT JOIN systypes st ON syscols.xusertype = st.xusertype-- LEFT JOIN syscomments syscomm ON syscols.cdefault = syscomm.id--WHERE syscols.id = OBJECT_ID(N'T_Person')
--ORDER BY syscols.id,
-- syscols.colorder--/* 结果为:--FIdNumber varchar NULL 1--FName varchar NULL 1--FBirthDay datetime NULL 1--FRegDay datetime NULL 1--FWeight numeric NULL 1--*/--MYSQL中： DESCRIBE T_Person--ORACLE中：select COLUMN_NAME,DATA_TYPE,DATA_DEFAULT,NULLABLE from all_tab_columns
-- where TABLE_NAME ='T_Person'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------消除数据库系统差异性的方案：为每种数据库编写不同的SQL语句；使用语法交集；使用抽象SQL；-- 使用ORM工具；使用SQL翻译--/* 为每种数据库编写不同的SQL语句: (要求开发人员对每种数据库的语法差异性非常精通)if(currentdatabase='MYSQL')
{ executeQuery(' SELECT * FROM T_Person LIMIT 0, 10'); }else if(currentdatabase='MSSQLServer')
{ executeQuery(' SELECT TOP 10 * FROM T_Person'); }else if(currentdatabase='Oracle')
{ executeQuery(' SELECT * FROM T_Person WHERE ROWNUM <= 10'); }else if(currentdatabase='DB2')
{ executeQuery(' SELECT * FROM T_Person FETCH FIRST 10 ROWS ONLY'); }*/
--使用语法交集 ,不太可行,较多约束,必须使用各个数据库公共的语法
--使用SQL实体对象
----使用ORM工具:--Java中的Hibernate、EJB以及.Net中的Linq、NHibernate等都是非常优秀的ORM工具
/*比如:Person person = new Person();person.Name="Tom";person.Age=22;ormTool.Save(person);ORM工具会将其翻译成如下的SQL语句：INSERT INTO T_Person(FName,FAge) VALUES('Tom',22);--ORM工具将对实体对象的操作翻译成SQL语句，这本质上也是一种“使用SQL实体对象”的解决方案。*/----使用SQL翻译器:/*开发人员只要熟悉一种SQL语法就可以了，无需对SQL语句在不同数据库系统中的实现差异性有了解。 目前SQL翻译器产品有三个，分别是SwisSQL、LDBC和CowNewSQL，SwisSQL是一个 非开源的商业公司的公开产品，LDBC和CowNewSQL(优势较大)是开源项目。*/-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------“SQL注入漏洞：--输入用户名：1' or 1='1 密码 admin，即为 where UserID='1' or 1='1' AND Password='admin'
--但是对于2次验证的，比如先验证用户名是否存在，存在则继续验证密码的，此用户名无效
--可考虑改为已知的用户名，把密码改为1' or 1='1 可以试试(一般都无效,可考虑对自己做的登录系统试试)----对付SQL注入漏洞有两种方式：过滤敏感字符和使用参数化SQL/* (1)过滤敏感字符:if(user.contains("or","and","select","delete") or password.contains("or","and","select","delete")){ ShowMessage("可能存在注入漏洞攻击！"); return;}缺陷是：1)给用户带来了不必要的麻烦，比如用户名中不能包含or，比如用户名为horizon，则报错 2)逻辑难以严谨。尽管过滤了大部分的敏感字符串，但是..*//* (2)通过使用存储过程：(推荐使用)Java、C#:将参数化SQL以及对应的参数值传递给(存储过程)DBMS，在DBMS中会将参数值当成一个普通的值来处理而不是将它们拼接到参数化SQL中※建议开发人员使用参数化SQL来代替字符串拼接，不过如果开发的时候采用的ASP、PHP等语言,那么由于这些语言没有提供参数化SQL机制，因此只能采用其它方式来避免了SQL注入漏洞攻击。*/-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------调优:如果系统开发出来不能满足要求的所有性能指标，则必须对系统进行调整，这个工作被称为调优。--性能决定于硬件和软件，
--其中硬件调优方式：使用频率高的CPU、使用多处理器、加大内存容量、增加Cache、提高网络速度等
--另一方面SQL的调优也很重要
--用EXISTS替代IN,更加高效
--用表连接替换EXISTS,更加高效
----避免在索引列上使用计算/函数，因为函数也是一种计算，会造成全表扫描--例如，WHERE ABS(FAmount)=300改写成WHERE FAmount=300 OR FAmount=-300后更高效(FAmount为索引列)
----如果检索结果中不会有重复的记录的话，应该用UNION ALL替代UNION,这样效率就会因此得到提高。----当 SQL 语句需要UNION 两个查询结果集合时，即使检索结果中不会有重复的记录，----如果使用UNION这两个结果集同样会尝试进行合并，然后在输出最终结果前进行排序。
----避免隐式类型转换造成的全表扫描----防止检索范围过宽----如果DBMS 优化器认为检索范围过宽，那么它将放弃索引查找而使用全表扫描，比如：
----使用IS NOT NULL或者不等于判断，可能造成优化器假设匹配的记录数太多。----使用LIKE 运算符时,"a%"将会使用索引,而"a%c","%c"则会使用全表扫描,因此"a%c"和"%c"不能被有效的评估匹配的数量
--事务将锁分为两种类型：只读锁和写入锁(只读锁是非独占的,写入锁是独占的)
--事务的隔离级别分为四种：READ_UNCOMMITED 、READ_COMMITED 、REPEATABLE_READ、SERIALIZABLE
--(隔离级别与并发性是互为矛盾的：隔离程度越高,数据库的并发性越差;隔离程度越低,数据库的并发性越好)
/*脏(dirty)读:假设同一个A和B两个同时并发操作数据库，A和B执行的任务如下：从数据库中读取整数N，将N 加上10，将新的N 更新回数据库。这两个并发执行的实例可能发生下面的执行顺序。(1)A从数据库中读取整数N，当前数据库中N=0；(2)N 加上10，并将其更新到数据库中，当前数据库中N=10。然而由于A 的事务还没有提交，所以数据库更新还没有称为持久性的；(3)B从数据库中读取整数N，当前数据库中N=10；(4)A回滚了事务，所以N 恢复到了N=0；(5)B将N 加上10，并将其更新到数据库中，当前数据库中N=20；这里出现了B在A提交之前读取了A所更新的数据，由于A回滚了事务，所以数据库中出现了错误的数据20。尽管A回滚了事务，但是A更新的数据还是间接的通过B被更新到了数据库中。这种读取了未提交的数据的方法就叫脏(dirty)读问题。幻影读取:当一个用户从数据库中读取数据的时候，另外一个用户修改了这条数据，所以数据发生了改变，当再次读取的时候就出现了不可重复读取问题。比如：(1)A从数据库中读取整数N；(2)B以一个新的值更新N；(3)当A再次从数据库中读取N 的时候，会发现N 的值变了；幻影读取指的是在两次数据库操作读取操作之间，一组新的数据会出现在数据库中。比如：(1)A从数据库检索到了一些数据；(2)B通过Insert语句插入了一些新数据；(3)A再次查询的时候，新的数据就会出现；更新丢失：当系统允许两个事务同时更新同一数据是，发生更新丢失。脏读：当一个事务读取另一个事务尚未提交的修改时，产生脏读。非重复读：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生非重复读。幻像：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻像读。*//*四种事务的隔离级别的区别：l 使用READ_UNCOMMITED(未提交读)级别，会导致脏读问题、幻影读取问题和不可重复读取问题。在需要敏感计算任务的事务中，这样的模式是不太适合的；l 使用READ_COMMITED(提交读)级别，可以解决脏读问题，但是还会有幻影读取问题和不可重复读取问题。这种级别一般用于制作报表。这种模式是大部分系统的默认级别；l 使用REPEATABLE_READ(重复读)级别，可以解决脏读问题和不可重复读取问题，但是会有幻影读取问题；l 使用SERIALIZABLE(序列化)级别可以解决脏读问题、幻影读取问题和不可重复读取问题。这是最严格级别的隔离级别；*/----执行一个READ_UNCOMMITTED级别事务的SQL语句:
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--BEGIN TRANSACTION
----具体的操作代码
--COMMIT--自动增长字段,格式为:IDENTITY(startvalue,step),其中startvalue为起始数字,step为步长--比如：CREATE TABLE T_Person (FId INT PRIMARY KEY IDENTITY(100,3),FName VARCHAR(20),FAge INT);
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE TABLE T_Person (FName VARCHAR(20),FCity VARCHAR(20),FAge INT,FSalary INT)--INSERT INTO T_Person(FName,FCity,FAge,FSalary)VALUES('Tom','BeiJing',20,3000);
--INSERT INTO T_Person(FName,FCity,FAge,FSalary)VALUES('Tim','ChengDu',21,4000);
--INSERT INTO T_Person(FName,FCity,FAge,FSalary)VALUES('Jim','BeiJing',22,3500);
--INSERT INTO T_Person(FName,FCity,FAge,FSalary)VALUES('Lily','London',21,2000);
--INSERT INTO T_Person(FName,FCity,FAge,FSalary)VALUES('John','NewYork',22,1000);
--INSERT INTO T_Person(FName,FCity,FAge,FSalary)VALUES('YaoMing','BeiJing',20,3000);
--INSERT INTO T_Person(FName,FCity,FAge,FSalary)VALUES('Swing','London',22,2000);
--INSERT INTO T_Person(FName,FCity,FAge,FSalary)VALUES('Guo','NewYork',20,2800);
--INSERT INTO T_Person(FName,FCity,FAge,FSalary)VALUES('YuQian','BeiJing',24,8000);
--INSERT INTO T_Person(FName,FCity,FAge,FSalary)VALUES('Ketty','London',25,8500);
--INSERT INTO T_Person(FName,FCity,FAge,FSalary)VALUES('Kitty','ChengDu',25,3000);
--INSERT INTO T_Person(FName,FCity,FAge,FSalary)VALUES('Merry','BeiJing',23,3500);
--INSERT INTO T_Person(FName,FCity,FAge,FSalary)VALUES('Smith','ChengDu',30,3000);
--INSERT INTO T_Person(FName,FCity,FAge,FSalary)VALUES('Bill','BeiJing',25,2000);
--INSERT INTO T_Person(FName,FCity,FAge,FSalary)VALUES('Jerry','NewYork',24,3300);
--SELECT FCITY, FAGE, COUNT(*) FROM T_Person WHERE FSALARY<5000 GROUP BY FCITY , FAGE
--SELECT FCITY , FAGE ,(SELECT COUNT(* ) FROM T_Person WHERE FSALARY<5000) FROM T_Person WHERE FSALARY<5000
--SELECT FCITY , FAGE , COUNT(*) OVER() FROM T_Person WHERE FSALARY<5000
--SELECT FCITY , FAGE , COUNT(*) OVER(partition BY fcity) AS '每个城市符合条件的人数' FROM T_Person WHERE FSALARY<5000
----COUNT(*) OVER(PARTITION BY FCITY)表示对结果集按照FCITY进行分区，并且计算当前行所属的组的聚合计算结果。
----比如对于FName等于Tom的行，它所属的城市是BeiJing，同属于BeiJing的人员一共有6个，所以对于这一列的显示结果为6。
----PARTITION BY 子句创建的分区是独立于结果集的，创建的分区只是供进行聚合计算的，---- 而且不同的开窗函数所创建的分区也不互相影响--SELECT FName,FCITY, FAGE, FSalary,--COUNT(*) OVER(PARTITION BY FCITY) AS '以城市分区',
--COUNT(*) OVER(PARTITION BY FAGE) AS '以年龄分区'
--FROM T_Person --在同一个SELECT语句中可以同时使用多个开窗函数，而且这些开窗函数并不会相互干扰。----RANK()和DENSE_RANK()函数都可以用于计算一行的排名，不过对于并列排名的处理方式不同
--SELECT FName, FSalary,FAge,--RANK() OVER(ORDER BY FAGE), --不连续记录,如1 1 1 4 4 6 6 6 9 10 ... 名次顺延
--DENSE_RANK() OVER(ORDER BY FAGE), --连续记录,如 1 1 1 2 2 3 4 ... 名次的位置会被占用
--ROW_NUMBER() OVER(ORDER BY FAGE) --直接记录行号,如 1 2 3 4 5 ...
--FROM T_Person;----【SQL优化】使用WITH AS实现“一次定义多次使用”：
--WITH person_tom(FFName,FFCity,FFAge,FFSalary) AS(SELECT FName,FCity,FAge,FSalary FROM T_Person WHERE FName='TOM')
--SELECT T_Person.* FROM T_Person,person_tom
--WHERE T_Person.FAge=person_tom.FFAge--AND T_Person.FSalary=person_tom.FFSalary-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------【基本操作】 【...............................................】--delete from tableA --清空表tableA中的数据
--drop table tableA --删除表tableA
--alter table T_DOC_EXTEND alter column CNAME varchar(256) null; --【修改字段长度】
--ALTER TABLE T_Employee ADD FSubCompany VARCHAR(20); --【新增字段】
--alter table TableA drop column clumns_nameA; --【删除字段】
--【取得指定表的字段定义】
--SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,COLUMN_DEFAULT,IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Person'--'表A'
--结果为: CID varchar 36-- CNAME varchar 128--select * from sys.objects where type ='D' --约束
--select * from sys.objects where type ='U' --表
--SELECT ROW_NUMBER() OVER (ORDER BY name) AS t, name FROM sysobjects where xtype='U' --表(添加一列自增序号)
--【取得指定表的字段定义】
--SELECT a.name AS 字段名,b.name AS 数据类型, a.length AS 长度,
-- a.isnullable AS 允许空, ISNULL(e.text, '') AS 默认值, ISNULL(g.value, '') AS 字段说明 --FROM dbo.syscolumns AS a
-- LEFT OUTER JOIN dbo.systypes AS b ON a.xtype = b.xusertype-- INNER JOIN dbo.sysobjects AS d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0 -- LEFT OUTER JOIN dbo.syscomments AS e ON a.cdefault = e.id -- LEFT OUTER JOIN sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.minor_id -- LEFT OUTER JOIN sys.extended_properties AS f ON d.id = f.major_id AND f.minor_id = 0 where d .name='T_DOC'--【取某个表的原始列信息】
-- SELECT * FROM syscolumns WHERE id = OBJECT_ID('T_DOC_EXTEND')--修改字段名：
--修改默认值
----alter table T_DOC_EXTEND add constraint df default('1') for thisname;
--select * into T_ROLEDIRECTORY_old from T_ROLEDIRECTORY
--【跨数据库操作】
--复制表结构及内容到另一个数据库中
--SELECT * INTO TargetDB.dbo.IpVotes FROM OldDB.dbo.IpVotes --注意在TargetDB中，执行前不存在表IpVotes
--【跨IP操作】
----远程机器设置远程链接服务器:
----假设远程机器IP为61.188.*.*
----建立连接服务器
--EXEC sp_addlinkedserver '61.188.*.*','SQL Server'
----创建链接服务器上远程登录之间的映射
--EXEC sp_addlinkedsrvlogin '61.188.*.*','false','sa','账号','密码'
----查询数据
--select top 10 * from [61.188.*.*].数据库.dbo.X_Users
----前面2行仅需执行一次，然后接着写下面类似的查询语句
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------分页的简单SQL,当前显示1-10条记录
--SELECT *--FROM ( SELECT Id,
-- IPAddr,-- Msg,-- PostDate,-- Row_Number() OVER ( ORDER BY PostDate ) rownum-- FROM T_Posts-- ) t--WHERE t.rownum >= 1-- AND t.rownum <= 10-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------server1中执行----看数据件数
--select count(*) from database1..table1
--select count(*) from openrowset('sqloledb','S2IP';'S2userid';'S2psd',select * from database1..table1)
----找不同的数据
--select * from database1..table1 t
--where exists
--( select 1 from opendatasource('sqloledb','data source=.;user id=sa;password=123456').database1..table1)
-- where pkey=t.pkey and date=t.date and number<>t.number)----openrowset与opendatasource性质一样,语法不同
--/* --未测试--如果有语法错误，请参见联机帮助--应该没有问题--*/--select * FROM DB0831.dbo.T_DIRECTORY t
--where exists
--( select 1 from opendatasource('sqloledb','data source=.;user id=sa;password=123').DB0831.dbo.T_DIRECTORY
-- where cid=t.cid and date=t.date and number<>t.number)----openrowset与opendatasource性质一样,语法不同
exec sp_configure-- EXEC sp_helptext EXEC sp_help--SELECT a.CID,a.CNAME '0831的库',b.CNAME '0828的库' FROM DB0831.dbo.T_DOC_EXTEND a,DB0828.dbo.T_DOC_EXTEND b WHERE a.cid=b.cid
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SELECT name FROM sysobjects where xtype='U'
--USE TestSQL --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --用openrowset:
----支持存儲過程
--select *from openrowset('SQLOLEDB','Roy\SQL2005DE';'sa';'Test2005','test.dbo.p')a
----select --select *from openrowset('SQLOLEDB','Roy\SQL2005DE';'sa';'Test2005',test.dbo.Tab)a
----insert
--insert openrowset('SQLOLEDB','Roy\SQL2005DE';'sa';'Test2005',test.dbo.Tab)(Name)values('EE')
----update --update openrowset('SQLOLEDB','Roy\SQL2005DE';'sa';'Test2005',test.dbo.Tab) set Name='EEE' where Name=N'EE'
----delete--delete openrowset('SQLOLEDB','Roy\SQL2005DE';'sa';'Test2005',test.dbo.Tab) where Name=N'EEE'
--用openquery:--支持存儲過程
----支持存儲過程
--select * from openquery(roy_lnk, 'test.dbo.P')
----select--SELECT * FROM openquery(roy_lnk, 'SELECT * FROM test.dbo.tab')
----insert
--insert openquery(roy_lnk, 'SELECT * FROM test.dbo.tab') (Name) values('F')
----update --update openquery(roy_lnk, 'SELECT * FROM test.dbo.tab') set Name='FF' where Name='F'
----delete不支持是根據 OLE DB 提供者的功能而定
----delete openquery(roy_lnk, 'SELECT * FROM test.dbo.tab') where Name='FF'
--用OPENDATASOURCE:
----支持存儲過程
--select * from OPENDATASOURCE('SQLOLEDB','Data Source=Roy\SQL2005DE;User ID=sa;Password=Test2005').'test.dbo.p'
----select--select * from OPENDATASOURCE('SQLOLEDB','Data Source=Roy\SQL2005DE;User ID=sa;Password=Test2005').test.dbo.tab
----insert
--insert OPENDATASOURCE('SQLOLEDB','Data Source=Roy\SQL2005DE;User ID=sa;Password=Test2005').test.dbo.tab(Name) values('H')
----update--update OPENDATASOURCE('SQLOLEDB','Data Source=Roy\SQL2005DE;User ID=sa;Password=Test2005').test.dbo.tab set Name='HH' where Name='H'
----delete --delete OPENDATASOURCE('SQLOLEDB','Data Source=Roy\SQL2005DE;User ID=sa;Password=Test2005').test.dbo.tab where Name='HH'
----與本機表數據對比時用別名
--select * from roy_lnk.test.dbo.Tab a join tab b on a.ID=b.ID
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------未完待续..①②③
通配符：(较慢，尽量避免使用)
使用'in'： SELECT FAge,FNumber,FName FROM T_Employee
WHERE FAge IN (23,25,28)
使用'between and': SELECT * FROM T_Employee
WHERE FAGE BETWEEN 23 AND 27
“不等于”的运算符“<>”
单字符匹配：b_d : "_"表示单个字符
多字符匹配：使用"%"
"[bt]%"：第一个字符为b或者t、长度不限的字符串
FName LIKE '[^SJ]%' 等同于 NOT(FName LIKE 'S%') AND NOT(FName LIKE 'J%'),表示不以S或J开头的(否定符)
使用"is null"或"is not null"查询字段为空的数据，不要简单的使用 "=null"这样会查不出来
给表新增字段：ALTER TABLE T_Employee ADD FSubCompany VARCHAR(20);
(由于每组中人员的员工工资都不一样，所以就不存在能够统一代表本组工资水平的FSalary字段了)
每组中员工的平均工资却是能够代表本组统一工资水平的
SELECT FAge,AVG(FSalary) '平均工资' FROM T_Employee GROUP BY FAge
