详实的SQL学习笔记
写在前面：最近在上海找工作，前前后后面试了N家单位，发现各个公司对程序员的数据库知识尤其是SQL语句的掌握程度有很高的要求，作为一名光荣的程序员，不会玩儿SQL语句走在街上根本不好意思和人打招呼!好了，废话不多说，新手菜鸟同志们了注意了，这篇文章提供的例子很简单，但是也很重要，请认真练习!别等到面试的时候被某些人嘲讽"唉!这年头，会写SQL语句的程序员越来越少了!L"老鸟高手同志们，你们可以优雅地飘过，但是有什么意见或建议都要提出来哦，大家一起进步嘛J，让菜鸟变成高手，提高我国的编程水平。
一、SQL 基础知识
1、DDL(数据定义语言)
1)创建数据表
1 --创建数据表 2 create table Test(Id int not null, Age char(20))； 3 4 --创建数据表 5 create table T_Person1(Id int not null, 6 Name nvarchar(50), 7 Age int null)； 8 9 --创建表，添加外键 10 Create table T_Students( 11 StudentNo char(4), 12 CourseNo char(4), 13 Score int, 14 Primary key(StudentNo), 15 Foreign key(CourseNo) References T_Course(CourseNo) 16 );
2)修改表结构
1 --修改表结构，添加字段 2 Alter table T_Person add NickName nvarchar(50) null; 3 4 --修改表结构，删除字段 5 Alter table T_Person Drop NickName;
3)删除数据表
--删除数据表 Drop table T_Person; --删除数据表 drop table test
4)创建索引
Create [Unique] Index <索引名> on <基本表名>(<列明序列>);
2、DML（数据操纵语言）
1)插入语句
insert into T_Person1(Id,Name,Age) values(1,'Vicky',20) --插入一条据数，字段和值必须前后对应 insert into T_Preson1(Id,Name,Age) values(2,'Tom',19) insert into T_Person1(Id,Name,Age) values(4,'Jim',19) insert into T_Person1(Id,Name,Age) values(5,'Green',20) insert into T_Person1(Id,Name,Age) values(6,'Hanmeimei',21) insert into T_Person1(Id,Name,Age) values(7,'Lilei',22) insert into T_Person1(Id,Name,Age) values(8,'Sky',23) insert into T_Person1(Id,Name,Age) values(newid(),'Tom',19)
2)更新语句
1 --修改列，把所有的age字段改为30 2 update T_Person1 set age=30 3 4 --把所有的Age字段和Name字段设置为... 5 update T_Person1 set Age=50,Name='Lucy' 6 7 update T_Person1 set Name='Frankie' where Age=30 8 9 update T_Person1 set Name=N'中文字符' where Age=20 10 11 --中文字符前面最好加上N，以防出现乱码 12 update T_Person1 set Name=N'成年人' where Age=30 or Age=50
3)删除语句
1 delete from T_Person1 2 --删除表中全部数据 3 delete from T_Person1 where Name='Tom' 4 --根据条件删除数据
4)查询语句
查询语句非常强大，几乎可以查任意东西！
1 ----------------- 2 ---- 数据检索 ----- 3 ----------------- 4 --查询不与任何表关联的数据. 5 SELECT 1+1; --简单运算 6 select 1+2 as 结果 7 SELECT newid();--查询一个GUID字符创 8 select GETDATE() as 日期 --查询日期 9 --可以查询SQLServer版本 10 select @@VERSION as SQLServer版本 11 --一次查询多个 12 select 1+1 结果, GETDATE() as 日期, @@VERSION as 版本, NEWID() as 编号 13 --简单的数据查询.HelloWorld级别 14 SELECT * FROM T_Employee; 15 --只查询需要的列. 16 SELECT FNumber FROM T_Employee; 17 --给列取别名.As关键字 18 SELECT FNumber AS 编号, FName AS 姓名 FROM T_Employee; 19 --使用 WHERE 查询符合条件的记录. 20 SELECT FName FROM T_Employee WHERE FSalary<5000; 21 --对表记录进行排序，默认排序规则是ASC 22 SELECT * FROM T_Employee ORDER BY FAge ASC,FSalary DESC; 23 --ORDER BY 子句要放在 WHERE 子句之后. 24 SELECT * FROM T_Employee WHERE FAge>23 ORDER BY FAge DESC,FSalary DESC; 25 --WHERE 中可以使用的逻辑运算符：or、and、not、<、>、=、>=、<=、!=、<>等. 26 --模糊匹配，首字母未知. 27 SELECT * FROM T_Employee WHERE FName LIKE '_arry'; 28 --模糊匹配，前后多个字符未知. 29 SELECT * FROM T_Employee WHERE FName LIKE '%n%'; 30 --NULL 表示"不知道"，有 NULL 参与的运算结果一般都为 NULL. 31 --查询数据是否为 NULL，不能用 = 、!= 或 <>，要用IS关键字 32 SELECT * FROM T_Employee WHERE FName IS NULL; 33 SELECT * FROM T_Employee WHERE FName IS NOT NULL; 34 --查询在某个范围内的数据，IN 表示包含于，IN后面是一个集合 35 SELECT * FROM T_Employee WHERE FAge IN (23, 25, 28); 36 --下面两条查询语句等价。 37 SELECT * FROM T_Employee WHERE FAge>=23 AND FAge<=30; 38 SELECT * FROM T_Employee WHERE FAge BETWEEN 23 AND 30; 39 ----创建一张Employee表，以下几个Demo中会用的这张表中的数据 40 ----在SQL管理器中执行下面的SQL语句，在T_Employee表中进行练习 41 create table T_Employee(FNumber varchar(20), 42 FName varchar(20), 43 FAge int, 44 FSalary Numeric(10,2), 45 primary key (FNumber) 46 ) 47 insert into T_Employee(FNumber,FName,FAge,FSalary) values('DEV001','Tom',25,8300) 48 insert into T_Employee(FNumber,FName,FAge,FSalary) values('DEV002','Jerry',28,2300.83) 49 insert into T_Employee(FNumber,FName,FAge,FSalary) values('SALES001','Lucy',25,5000) 50 insert into T_Employee(FNumber,FName,FAge,FSalary) values('SALES002','Lily',25,6200) 51 insert into T_Employee(FNumber,FName,FAge,FSalary) values('SALES003','Vicky',25,1200) 52 insert into T_Employee(FNumber,FName,FAge,FSalary) values('HR001','James',23,2200.88) 53 insert into T_Employee(FNumber,FName,FAge,FSalary) values('HR002','Tom',25,5100.36) 54 insert into T_Employee(FNumber,FName,FAge,FSalary) values('IT001','Tom',28,3900) 55 insert into T_Employee(FNumber,FAge,FSalary) values('IT002',25,3800) 56 --开始对T_Employee表进行各种操作 57 --检索所有字段 58 select * from T_Employee 59 --只检索特定字段 60 select FName,FAge from T_Employee 61 --带过滤条件的检索 62 select * from T_Employee 63 where FSalary<5000 64 --可更改显示列名的关键字as，as—起别名 65 select FName as 姓名,FAge as 年龄,FSalary as 薪水 from T_Employee
二、SQL Server 中的数据类型
1、精确数字类型
1 bigint 2 int 3 smallint 4 tinyint 5 bit 6 money 7 smallmoney
2、字符型数据类型，MS建议用VarChar(max)代替Text
Char VarChar Text
3、近似数字类型
1 Decimal 2 Numeric 3 Real 4 Float
4、Unicode字符串类型
1 Nchar 2 NvarChar 3 Ntext
5、二进制数据类型,MS建议VarBinary(Max)代替Image数据类型，max=231-1
1 Binary(n) 存储固定长度的二进制数据 2 VarBinary(n) 存储可变长度的二进制数据，范围在n~(1,8000) 3 Image 存储图像信息
6、日期和时间类型,数据范围不同，精确地不同
1 DateTime 2 SmallDateTime
7、特殊用途数据类型
1 Cursor 2 Sql-variant 3 Table 4 TimeStamp 5 UniqueIdentifier 6 XML
三、SQL中的内置函数
1 -------------------------------------- 2 -----数据汇总-聚合函数--------- 3 -------------------------------------- 4 5 --查询T_Employee表中数据条数 6 selectCOUNT(*)fromT_Employee 7 8 --查询工资最高的人 9 selectMAX(FSalary)asTop1fromT_Employee 10 11 --查询工资最低的人 12 selectMin(FSalary)asBottom1fromT_Employee 13 14 --查询工资的平均水平 15 selectAvg(FSalary)as平均水平fromT_Employee 16 17 --所有工资的和 18 selectSUM(FSalary)as总工资fromT_Employee 19 20 --查询工资大于5K的员工总数 21 selectCOUNT(*)astotalfromT_Employee 22 whereFSalary>5000 23 24 ------------------------------ 25 -----数据排序------- 26 ------------------------------ 27 28 --按年龄排序升序,默认是升序 29 select*fromT_Employee 30 orderbyFAgeASC 31 32 --多个条件排序,先什么,后什么,在前一个条件相同的情况下,根据后一个条件进行排列 33 34 --where在orderby之前 35 select*fromT_Employee 36 orderbyFAgeASC,FSalaryDESC 37 38 ------------------------------ 39 -----模糊匹配------- 40 ------------------------------ 41 42 --通配符查询 43 --1.单字符通配符_ 44 --2.多字符通配符% 45 --以DEV开头的任意个字符串 46 select*fromT_Employee 47 whereFNumberlike'DEV%' 48 49 --以一个字符开头,om结尾的字符串 50 select*fromT_Employee 51 whereFNamelike'_om' 52 53 --检索姓名中包含m的字符 54 select*fromT_Employee 55 whereFNamelike'%m%' 56 57 ------------------------------ 58 -----空值处理------- 59 ------------------------------ 60 --null表示不知道,不是没有值 61 --null和其他值计算结果是null 62 selectnull+1 63 64 --查询名字是null的数据 65 select*fromT_Employee 66 whereFNameisnull 67 68 --查询名字不为空null的数据 69 select*fromT_Employee 70 whereFNameisnotnull 71 72 --年龄是23,25,28中的员工 73 select*fromT_Employee 74 whereFAge=23orFAge=25orFAge=28 75 76 --或者用in集合查询 77 --年龄是23,25,28中的员工 78 select*fromT_Employee 79 whereFAgein(23,25,28) 80 81 --年龄在20到25之间的员工信息 82 select*fromT_Employee 83 whereFAge>20andFAge<25 84 85 --年龄在20到25之间的员工信息,包含25 86 select*fromT_Employee 87 whereFAgebetween20and25 88 89 ------------------------------ 90 -----数据分组------- 91 ------------------------------ 92 SelectFAge,COUNT(*)fromT_Employee 93 groupbyFAge 94 95 --1.根据年龄进行分组 96 --2.再取出分组后的年龄的个数 97 --注意:没有出现在groupby子句中的字段,不能出现在select语句后的列名列表中(聚合函数除外) 98 --groupby必须出现在where后面 99 SelectFAge,AVG(FSalary),COUNT(*)fromT_Employee 100 groupbyFAge 101 102 --错误用法 103 SelectFAge,FName,COUNT(*)fromT_Employee 104 groupbyFAge 105 106 --加上where的groupby子句 107 --groupby必须出现在where后面 108 SelectFAge,AVG(FSalary),COUNT(*)fromT_Employee 109 whereFAge>=25 110 groupbyFAge 111 112 --Having不能包含查不到的字段,只能包含聚合函数和本次查询有关的字段 113 selectFAge,COUNT(*)fromT_Employee 114 groupbyFAge 115 HavingCOUNT(*)>1 116 selectFAge,COUNT(*)fromT_Employee 117 whereFSalary>2500 118 groupbyFAge 119 120 --HAVING子句中的列'T_Employee.FSalary'无效，因为该列没有包含在聚合函数或GROUPBY子句中 121 --Having是对分组后信息的过滤，能用的列和select中能有的列是一样的。 122 --因此，having不能代替where 123 selectFAge,COUNT(*)fromT_Employee 124 groupbyFAge 125 HavingFSalary>2500 126 127 ------------------------------ 128 -----确定结果集行数------- 129 ------------------------------ 130 --取出所有员工的信息,根据工资降序排列 131 select*fromT_Employee 132 orderbyFSalaryDESC 133 134 --取出前三名员工的信息,根据工资降序排列 135 selecttop3*fromT_Employee 136 orderbyFSalaryDESC 137 138 --根据工资取出排名在6-8的员工信息,按工资降排列 139 selecttop3*fromT_Employee 140 whereFNumbernotin 141 (selecttop5FNumberfromT_EmployeeorderbyFSalaryDESC) 142 orderbyFSalaryDESC 143 144 ---修改数据表，添加字段，更新字段的值等操作。 145 altertableT_EmployeeaddFSubCompanyvarchar(20) 146 altertableT_EmployeeaddFDepartmentvarchar(20) 147 updateT_EmployeesetFSubCompany='Beijing',FDepartment='Development' 148 whereFNumber='DEV001'; 149 updateT_EmployeesetFSubCompany='ShenZhen',FDepartment='Development' 150 whereFNumber='DEV002'; 151 updateT_EmployeesetFSubCompany='Beijing',FDepartment='HumanResource' 152 whereFNumber='HR001'; 153 updateT_EmployeesetFSubCompany='Beijing',FDepartment='HumanResource' 154 whereFNumber='HR002'; 155 updateT_EmployeesetFSubCompany='Beijing',FDepartment='InfoTech' 156 whereFNumber='IT001'; 157 updateT_EmployeesetFSubCompany='ShenZhen',FDepartment='InfoTech' 158 whereFNumber='IT002' 159 updateT_EmployeesetFSubCompany='Beijing',FDepartment='Sales' 160 whereFNumber='SALES001'; 161 updateT_EmployeesetFSubCompany='Beijing',FDepartment='Sales' 162 whereFNumber='SALES002'; 163 updateT_EmployeesetFSubCompany='ShenZhen',FDepartment='Sales' 164 whereFNumber='SALES003'; 165 select*fromT_Employee 166 167 ------------------------------ 168 ------去掉重复数据------ 169 ------------------------------ 170 --所有员工的部门信息 171 selectDistinctFDepartmentfromT_Employee; 172 selectFDepartment,FSubCompany 173 fromT_Employee 174 --以上两个例子结合起来比较，Distinct针对的是整行进行比较的 175 selectDistinctFDepartment,FSubCompany 176 fromT_Employee 177 178 ----------------------------- 179 -----联合结果集Union-------- 180 ------------------------------ 181 --创建一个测试表T_TempEmployee，并插入数据 182 CreateTableT_TempEmployee(FIdCardNumbervarchar(20),FNamevarchar(20),FAgeint,Primarykey(FIdCardNumber)); 183 insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890121','Sarani',33); 184 insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890122','Tom',26); 185 insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890123','Yamaha',38); 186 insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890124','Tina',36); 187 insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890125','Konkaya',29); 188 insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890126','Foortia',29); 189 select*fromT_TempEmployee 190 191 --Union关键字，联合2个结果 192 --把2个查询结果结合为1个查询结果 193 --要求:上下2个查询语句的字段(个数,名字,类型相容)必须一致 194 selectFName,FagefromT_TempEmployee 195 union 196 selectFName,FagefromT_Employee 197 selectFNumber,FName,Fage,FDepartmentfromT_Employee 198 union 199 selectFIdCardNumber,FName,Fage,'临时工，无部门'fromT_TempEmployee 200 201 ---UnionAll:不合并重复数据 202 --Union：合并重复数据 203 selectFName,FAgefromT_Employee 204 unionall 205 selectFName,FAgefromT_TempEmployee 206 selectFAgefromT_Employee 207 union 208 selectFAgefromT_TempEmployee 209 210 --注意:Union因为要进行重复值扫描,所以效率低,因此如果不是确定要合并重复,那么就用Unionall 211 --例子:报名 212 select'正式员工最高年龄',MAX(FAge)fromT_Employee 213 unionall 214 select'正式员工最低年龄',MIN(FAge)fromT_Employee 215 unionall 216 select'临时工最高年龄',MAX(FAge)fromT_TempEmployee 217 unionall 218 select'临时工最低年龄',MIN(FAge)fromT_TempEmployee 219 220 --查询每位正式员工的信息,包括工号,工资,并且在最后一行加上员工工资额合计 221 selectFNumber,FSalaryfromT_Employee 222 unionall 223 select'工资额合计',SUM(FSalary)fromT_Employee
1 ------------------------------ 2 -----SQL其他内置函数------ 3 ------------------------------ 4 5 --1.数学函数 6 --ABS():求绝对值 7 --CEILING():舍入到最大整数 8 --FLOOR():舍入到最小整数 9 --ROUND():四舍五入 10 11 selectABS(-3) 12 selectCEILING(3.33) 13 selectCEILING(-3.61) 14 selectFLOOR(2.98) 15 selectFLOOR(-3.61) 16 selectROUND(-3.61,1)--第二个参数是精度，小数点后的位数 17 selectROUND(-3.61,0) 18 selectROUND(3.1415926,3) 19 20 --2.字符串函数 21 --LEN():计算字符串长度 22 --LOWER(),UPPER():转大小写 23 --LTRIM():去掉字符串左侧的空格 24 --RTRIM():去掉字符串右侧的空格 25 --SUBSTRING(string,start_positoin,length): 26 --索引从1开始 27 selectSUBSTRING('abc111',2,3)--结果是bc1 28 selectFName,SUBSTRING(FName,2,2)fromT_Employee 29 selectLEN('abc')--结果是3 30 selectFName,LEN(FName)fromT_Employee 31 32 --没有可以同时既去掉左边空格、又去掉右边空格的TRIM()内置函数，所以先左后右的进行TRim，当然，你也可以先右后左 33 selectLTRIM('abc'),RTRIM('abc'),LEN(LTRIM(RTRIM('abc'))) 34 35 --3.日期函数 36 --GETDATE():获取当前日期时间 37 --DATEADD(datepart,numbre,date):计算增加以后的日期， 38 --参数date为待计算的日期；参数number为增量；参数datepart为计量单位,时间间隔单位； 39 --DATEDIFF(datepart,startdate,enddate):计算2个日期之间的差额 40 --DATEPART(datepart,date):返回一个日期的特定部分,比如年月日,时分秒等. 41 42 /* 43 44 值缩写（SqlServer）(Access和ASP)说明 45 YearYyyyyy年1753~9999 46 QuarterQqq季1~4 47 MonthMmm月1~12 48 DayofyearDyy一年的日数,一年中的第几日1-366 49 DayDdd日，1-31 50 WeekdayDww一周的日数，一周中的第几日1-7 51 WeekWkww周，一年中的第几周0~51 52 HourHhh时0~23 53 MinuteMin分钟0~59 54 SecondSss秒0~59 55 MillisecondMs-毫秒0~999 56 */ 57 58 selectDATEADD(DAY,3,getdate()) 59 selectDATEADD(MONTH,-3,getdate()) 60 selectDATEADD(HOUR,8,getdate()) 61 selectDATEDIFF(YEAR,'1989-05-01',GETDATE()) 62 selectDATEDIFF(HH,GETDATE(),DATEADD(DAY,-3,GETDATE())) 63 64 --查询员工的工龄,年为单位 65 selectFName,FInDate,DATEDIFF(year,FInDate,getdate())as工龄fromT_Employee 66 67 --取出每一年入职员工的个数V1 68 selectDATEDIFF(year,FInDate,getdate()),COUNT(*) 69 fromT_Employee 70 groupbyDATEDIFF(year,FInDate,getdate()) 71 72 --取出每一年入职员工的个数V2 73 selectDATEPART(YEAR,FInDate),COUNT(*) 74 fromT_Employee 75 groupbyDATEPART(YEAR,FInDate) 76 selectDATEPART(YEAR,GETDATE()) 77 selectDATEPART(MONTH,GETDATE()) 78 selectDATEPART(DAY,GETDATE()) 79 selectDATEPART(HH,GETDATE()) 80 selectDATEPART(MINUTE,GETDATE()) 81 selectDATEPART(SECOND,GETDATE()) 82 83 --4.类型转换函数 84 --CAST(expressionasdata_type) 85 --CONVERT(data_type,expression) 86 selectCAST('123'asint),CAST('2010-09-08'asdatetime), 87 CONVERT(datetime,'2010-09-08'),CONVERT(varchar(20),123) 88 89 --5.空值处理函数isNull 90 --ISNULL(expression,value) 91 selectISNULL(FName,'佚名')as姓名fromT_Employee 92 93 --6.CASE函数用法： 94 --1.单值判断：相当于switch.case 95 --CASEexpression 96 --WHENvalue1thenreturnvalue1 97 --WHENvalue2thenreturnvalue2 98 --WHENvalue3thenreturnvalue3 99 --ELSEdefault_return_value 100 --END 101 102 --判断客户类型 103 selectFName, 104 ( 105 caseFLevel 106 when1then'普通客户' 107 when2then'会员' 108 when3then'VIP' 109 else'未知客户类型' 110 End 111 )as客户类型 112 fromT_Customer 113 114 --收入水平查询 115 selectFName, 116 ( 117 case 118 whenFSalary<2000then'低收入' 119 whenFSalary>=2000andFSalary<=5000then'中等收入' 120 else'高收入' 121 end 122 )as收入水平 123 fromT_Employee 124 125 --这里有一道关于CASE用法的面试题 126 --表T中有ABC三列，用SQL语句实现:当A列大于B列时选择A列，否则选择B列； 127 --当B列大于C列时选择B列，否则选择C列。 128 select 129 ( 130 case 131 whena>bthenaelseb 132 end 133 ), 134 ( 135 case 136 whenb>cthenbelsec 137 end 138 ) 139 fromT 140 141 --------------------------------------- 142 143 selectFNumber, 144 ( 145 case 146 whenFAmount>0thenFAmount 147 else0 148 end 149 )as收入, 150 ( 151 case 152 whenFAmount<0thenABS(FAmount) 153 else0 154 end 155 )as支出 156 fromT 157 ----------------------------------------- 158 159 --球队比赛那个题 160 --有一张表T_Scroes，记录比赛成绩： 161 --DateNameScroe 162 --2008-8-8拜仁胜 163 --2008-8-9奇才胜 164 --2008-8-8湖人胜 165 --2008-8-10拜仁负 166 --2008-8-8拜仁负 167 --2008-8-12奇才胜 168 --要求输出下面格式： 169 --Name胜负 170 --拜仁12 171 --湖人10 172 --奇才20 173 --注意：在中文字符串前加N，比如N'胜' 174 createtableT_Scores( 175 [Date]datetimenullcollate 176 [Name]nvarchar(50) 177 ) 178 CREATETABLE[T_Scores]([Date][datetime]NULL, 179 [Name][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL, 180 [Score][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL 181 ); 182 INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF200000000ASDateTime),N'拜仁',N'胜'); 183 INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF300000000ASDateTime),N'奇才',N'胜'); 184 INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF300000000ASDateTime),N'湖人',N'胜'); 185 INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF400000000ASDateTime),N'拜仁',N'负'); 186 INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF200000000ASDateTime),N'拜仁',N'负'); 187 INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF600000000ASDateTime),N'奇才',N'胜'); 188 select*fromT_Scores 189 190 --列出第一个表格 191 --统计每支队伍的胜负情况 192 selectName, 193 ( 194 caseScore 195 whenN'胜'then1 196 else0 197 end 198 )as胜, 199 ( 200 caseScore 201 whenN'负'then1 202 else0 203 end 204 )as负 205 fromT_Scores 206 selectName, 207 sum 208 ( 209 caseScore 210 whenN'胜'then1 211 else0 212 end 213 )as胜, 214 sum 215 ( 216 caseScore 217 whenN'负'then1 218 else0 219 end 220 )as负 221 fromT_Scores 222 groupbyName 223 --根据每个队的胜负判断出胜负的场数
1 --题4)输出本月拨打电话次数最多的前三个呼叫员的编号. 2 3 --@按呼叫员编号进行分组； 4 5 --@计算个呼叫员拨打电话的次数； 6 7 --@按呼叫员拨打电话的次数进行降序排序； 8 9 --@查询前3条记录中呼叫员的编号。 10 11 selecttop3CallerNumber,count(*) 12 13 fromT_CallRecords 14 15 wheredatediff(month,StartDateTime,getdate())=12--一年前的 16 17 groupbyCallerNumber 18 19 orderbycount(*)DESC 20 21 22 23 --题5)输出所有数据的拨号流水，并且在最后一行添加总呼叫时长： 24 25 26 27 --记录呼叫员编号、对方号码、通话时长 28 29 --...... 30 31 --汇总[市内号码总时长][长途号码总时长] 32 33 34 35 --@计算每条记录中通话时长； 36 37 --@查询包含不加0号码，即市内号码的记录； 38 39 --@计算市内号码通话总时长； 40 41 --@查询包含加0号码，即长途号码的记录； 42 43 --@计算长途号码通话总时长； 44 45 --@联合查询。 46 47 select'汇总'as汇总, 48 49 convert(varchar(20), 50 51 sum(( 52 53 case 54 55 whenTellNumbernotlike'0%'thendatediff(second,StartDateTime,EndDateTime) 56 57 else0 58 59 end 60 61 )))as市内通话, 62 63 sum(( 64 65 case 66 67 whenTellNumberlike'0%'thendatediff(second,StartDateTime,EndDateTime) 68 69 else0 70 71 end 72 73 ))as长途通话 74 75 fromT_CallRecords 76 77 unionall 78 79 selectCallerNumber,TellNumber,datediff(second,StartDateTime,EndDateTime)as通话时长 80 81 fromT_CallRecords 82 83 84 85 86 87 --客户和订单表的练习 88 89 --建立一个客户表 90 91 createtableT_Customers( 92 93 idintnotnull, 94 95 namenvarchar(50)collatechinese_prc_ci_asnull, 96 97 ageintnull 98 99 ); 100 101 insertT_Customers(id,name,age)values(1,N'tom',10); 102 103 insertT_Customers(id,name,age)values(2,N'jerry',15); 104 105 insertT_Customers(id,name,age)values(3,N'john',22); 106 107 insertT_Customers(id,name,age)values(4,N'lily',18); 108 109 insertT_Customers(id,name,age)values(5,N'lucy',18); 110 111 112 113 select*fromT_Customers 114 115 116 117 --建立一个销售单表 118 119 createtableT_Orders( 120 121 idintnotnull, 122 123 billnonvarchar(50)collatechinese_prc_ci_asnull, 124 125 customeridintnull); 126 127 128 129 insertT_Orders(id,billno,customerid)values(1,N'001',1) 130 131 insertT_Orders(id,billno,customerid)values(2,N'002',1) 132 133 insertT_Orders(id,billno,customerid)values(3,N'003',3) 134 135 insertT_Orders(id,billno,customerid)values(4,N'004',2) 136 137 insertT_Orders(id,billno,customerid)values(5,N'005',2) 138 139 insertT_Orders(id,billno,customerid)values(6,N'006',5) 140 141 insertT_Orders(id,billno,customerid)values(7,N'007',4) 142 143 insertT_Orders(id,billno,customerid)values(8,N'008',5) 144 145 146 147 select*fromT_Orders 148 149 150 151 selecto.billno,c.name,c.age 152 153 fromT_OrdersasojoinT_Customersascono.customerid=c.id 154 155 --查询订单号,顾客名字,顾客年龄 156 157 158 159 selecto.billno,c.name,c.age 160 161 fromT_OrdersasojoinT_Customersascono.customerid=c.id 162 163 wherec.age>15 164 165 --显示年龄大于15岁的顾客姓名、年龄和订单号 166 167 168 169 selecto.billno,c.name,c.age 170 171 fromT_OrdersasojoinT_Customersascono.customerid=c.id 172 173 wherec.age>(selectavg(age)fromT_Customers) 174 175 --显示年龄大于平均年龄的顾客姓名、年龄和订单号 176 177 178 179 --子查询练习 180 181 --新建一个数据库，名为BookShop 182 183 CreatedatabaseBookShop 184 185 186 187 --创建4张表 188 189 createtableT_Reader(FIdINTNOTNULL,FNamevarchar(50),FYearOfBirthINT,FCityvarchar(50),FProvincevarchar(50),FYearOfJoinINT); 190 191 createtableT_Book(FIdintnotnull,FNamevarchar(50),FYearPublishedint,FCategoryIdint); 192 193 createtableT_Category(FIdintnotnull,FNamevarchar(50)); 194 195 createtableT_ReaderFavorite(FCategoryIdint,FReaderIdint); 196 197 198 199 --分别为4张表插入数据 200 201 insertintoT_Category(FId,FName)values(1,'Story'); 202 203 insertintoT_Category(FId,FName)values(2,'History'); 204 205 insertintoT_Category(FId,FName)values(3,'Theory'); 206 207 insertintoT_Category(FId,FName)values(4,'Technology'); 208 209 insertintoT_Category(FId,FName)values(5,'Art'); 210 211 insertintoT_Category(FId,FName)values(6,'Philosophy'); 212 213 214 215 insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(1,'Tom',1979,'TangShan','Hebei',2003); 216 217 insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(2,'Sam',1981,'LangFang','Hebei',2001); 218 219 insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(3,'Jerry',1966,'DongGuan','GuangDong',1995); 220 221 insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(4,'Lily',1972,'JiaXing','ZheJiang',2005); 222 223 insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(5,'Marry',1985,'BeiJing','BeiJing',1999); 224 225 insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(6,'Kelly',1977,'ZhuZhou','HuNan',1995); 226 227 insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(7,'Tim',1982,'YongZhou','HuNan',2001); 228 229 insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(8,'King',1979,'JiNan','ShanDong',1997); 230 231 insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(11,'John',1979,'QingDao','ShanDong',2003); 232 233 insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(9,'Lucy',1978,'LuoYang','HeNan',1996); 234 235 insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(10,'July',1983,'ZhuMaDian','HeNan',1999); 236 237 insertintoT_Reader(FId,FName,FYearOfBirth,FCity,fProvince,FyearOfJoin)values(12,'Fige',1981,'JinCheng','ShanXi',2003); 238 239 240 241 insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(1,'AboutJ2EE',2005,4); 242 243 insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(2,'LearningHibernate',2003,4); 244 245 insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(3,'TowCites',1999,1); 246 247 insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(4,'JaneEyre',2001,1); 248 249 insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(5,'OliverTwist',2002,1); 250 251 insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(6,'HistoryofChina',1982,2); 252 253 insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(7,'HistoryofEngland',1860,2); 254 255 insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(8,'HistoryofAmerica',1700,2); 256 257 insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(9,'HistoryofTheVorld',2008,2); 258 259 insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(10,'Atom',1930,3); 260 261 insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(11,'RELATIVITY',1945,3); 262 263 insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(12,'Computer',1970,3); 264 265 insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(13,'Astronomy',1971,3); 266 267 insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(14,'HowTosinging',1771,5); 268 269 insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(15,'DaoDeJing',2001,6); 270 271 insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(16,'ObediencetoAu',1995,6); 272 273 274 275 276 277 insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,1); 278 279 insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(5,2); 280 281 insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(2,3); 282 283 insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(3,4); 284 285 insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(5,5); 286 287 insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,6); 288 289 insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,7); 290 291 insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(4,8); 292 293 insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(6,9); 294 295 insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(5,10); 296 297 insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(2,11); 298 299 insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(2,12); 300 301 insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,12); 302 303 insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(3,1); 304 305 insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,3); 306 307 insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(4,4); 308 309 310 311 select*fromT_Book 312 313 314 315 select*fromT_Category 316 317 318 319 select*fromT_Reader 320 321 322 323 select*fromT_ReaderFavorite 324 325 326 327 --并列查询 328 329 select1asf1,2,(selectMIN(FYearPublished)fromT_Book), 330 331 (selectMAX(FYearPublished)fromT_Book)asf4 332 333 334 335 --查询入会日期在2001或者2003年的读者信息 336 337 select*fromT_Reader 338 339 whereFYearOfJoinin(2001,2003) 340 341 342 343 --与between...and不同 344 345 select*fromT_Reader 346 347 whereFYearOfJoinbetween2001and2003 348 349 350 351 --查询有书出版的年份入会的读者信息 352 353 select*fromT_Reader 354 355 whereFYearOfJoinin 356 357 ( 358 359 selectFYearPublishedfromT_Book 360 361 ) 362 363 364 365 --SQLServer2005之后的版本内置函数：ROW_NUMBER()，称为开窗函数，可以进行分页等操作。 366 367 selectROW_NUMBER()over(orderbyFSalaryDESC)asRow_Num, 368 369 FNumber,FName,FSalary,FAgefromT_Employee 370 --特别注意，开窗函数row_number()只能用于select或orderby子句中，不能用于where子句中 371 372 --查询第3行到第5行的数据 373 select*from 374 ( 375 selectROW_NUMBER()over(orderbyFSalaryDESC)asRow_Num, 376 FNumber,FName,FSalary,FAgefromT_Employee 377 )ase1 378 wheree1.Row_Num>=3ande1.Row_Num<=5
1 --题5)创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号，对方号码，通话开始时间，通话结束时间，。 2 --创建一张表T_Callers，记录电话呼叫员的工作流水，记录呼叫员编号、对方号码、通话开始时间、通话结束时间。建表、插数据等最后都自己写SQL语句。 3 --要求： 4 --1)输出所有数据中通话时间最长的5条记录。 5 --2)输出所有数据中拨打长途号码（对方号码以0开头）的总时长。 6 --3)输出本月通话总时长最多的前三个呼叫员的编号。 7 8 --4)输出本月拨打电话次数最多的前三个呼叫员的编号。 9 10 --5)输出所有数据的拨号流水，并且在最后一行添加总呼叫时长。 11 12 --记录呼叫员编号、对方号码、通话时长 13 14 --...... 15 16 --汇总[市内号码总时长][长途号码总时长] 17 18 19 --IdCallerNumberTellNumberStartDateTimeEndDateTime 20 21 --1001020888888882010-7-1010:012010-7-1010:05 22 23 --2001020888888882010-7-1113:412010-7-1113:52 24 25 --3001898989892010-7-1114:422010-7-1114:49 26 27 --4002021883689812010-7-1321:042010-7-1321:18 28 29 --5002767676762010-6-2920:152010-6-2920:30 30 31 --6001022888782432010-7-1513:402010-7-1513:56 32 33 --7003672546862010-7-1311:062010-7-1311:19 34 35 --8003862314452010-6-1919:192010-6-1919:25 36 37 --9001874223682010-6-1919:252010-6-1919:36 38 39 --10004400458622452010-6-1919:502010-6-1919:59 40 41 --创建表 42 43 createtableT_CallRecords( 44 45 idintnotnull, 46 47 CallerNumbervarchar(3), 48 49 TellNumbervarchar(13), 50 51 StartDateTImedatetime, 52 53 EndDateTimedatetime, 54 55 Primarykey(Id) 56 57 ); 58 --插入数据 59 60 insertintoT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTIme) 61 62 values(1,'001','02088888888','2010-7-1010:01','2010-7-1010:05'); 63 64 INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime) 65 66 VALUES(2,'002','02088888888','2010-7-1113:41','2010-7-1113:52'); 67 68 INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime) 69 70 VALUES(3,'003','89898989','2010-7-1114:42','2010-7-1114:49'); 71 72 INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime) 73 74 VALUES(4,'004','02188368981','2010-7-1321:04','2010-7-1321:18'); 75 76 INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime) 77 78 VALUES(5,'005','76767676','2010-6-2920:15','2010-6-2920:30'); 79 80 INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime) 81 82 VALUES(6,'006','02288878243','2010-7-1513:40','2010-7-1513:56'); 83 84 INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime) 85 86 VALUES(7,'007','67254686','2010-7-1311:06','2010-7-1311:19'); 87 88 INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime) 89 90 VALUES(8,'008','86231445','2010-6-1919:19','2010-6-1919:25'); 91 92 INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime) 93 94 VALUES(9,'009','87422368','2010-6-1919:25','2010-6-1919:36'); 95 96 INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime) 97 98 VALUES(10,'010','40045862245','2010-6-1919:50','2010-6-1919:59'); 99 100 --修改呼叫员编号 101 102 UPDATET_CallRecordsSETCallerNumber='001'WHEREIdIN(1,2,3,6,9); 103 104 UPDATET_CallRecordsSETCallerNumber='002'WHEREIdIN(4,5); 105 106 UPDATET_CallRecordsSETCallerNumber='003'WHEREIdIN(7,8); 107 108 UPDATET_CallRecordsSETCallerNumber='004'WHEREId=10; 109 110 --数据汇总 111 112 select*fromT_CallRecords 113 114 115 116 --题1)：输出所有数据中通话时间最长的5条记录。 117 118 --@计算通话时间； 119 120 --@按通话时间降序排列； 121 122 --@取前5条记录。 123 124 selecttop5CallerNumber,DATEDIFF(SECOND,StartDateTime,EndDateTime)as总时长 125 126 fromT_CallRecords 127 128 orderbyDATEDIFF(SECOND,StartDateTime,EndDateTime)DESC 129 130 --题2)：输出所有数据中拨打长途号码（对方号码以0开头）的总时长 131 132 --@查询拨打长途号码的记录； 133 134 --@计算各拨打长途号码的通话时长； 135 136 --@对各拨打长途号码的通话时长进行求和。 137 138 selectSUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))as总时长fromT_CallRecords 139 140 whereTellNumberlike'0%' 141 142 --题3)：输出本月通话总时长最多的前三个呼叫员的编号。 143 144 --@按呼叫员编号进行分组； 145 146 --@计算各呼叫员通话总时长； 147 148 --@按通话总时长进行降序排列； 149 150 --@查询前3条记录中呼叫员的编号。 151 152 selectdatediff(month,convert(datetime,'2010-06-01'),convert(datetime,'2010-07-22'))--测试 153 154 selectCallerNumber,TellNumber,datediff(month,StartDateTime,EndDateTime) 155 156 fromT_CallRecords 157 158 selecttop3CallerNumberfromT_CallRecords 159 160 wheredatediff(month,StartDateTime,getdate())=12--一年前的 161 162 groupbyCallerNumber 163 164 orderbySUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))DESC
四、SQL其他概念
--索引
1、什么是索引?优缺点是什么?
索引是对数据库表中一列或多列的值进行排序的一种单独的、物理的数据库结构。
优点：
1) 大大加快数据的检索速度;
2) 创建唯一性索引，保证数据库表中每一行数据的唯一性;
3) 加速表和表之间的连接;
4) 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
缺点：
1) 索引需要占物理空间;
2) 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。
--创建索引，在列上点击右键，写一个名称，选定列即可。
2、业务主键和逻辑主键
业务主键是使用有业务意义的字段做主键，比如身份证号，银行账号等;
逻辑主键是使用没有任何业务意义的字段做主键。因为很难保证业务主键不会重复(身份证号重复)、不会变化(账号升位)，因此推荐使用逻辑主键。
3、SQL Server 两种常用的主键数据类型
1) int(或 bigint) + 标识列(又称自动增长字段)
用标识列实现字段自增可以避免并发等问题，不要开发人员控制自增。用标识列的字段在Insert的时候不用指定主键的值。
优点：占用空间小、无需开发人员干预、易读;
缺点：效率低，数据导入导出的时候很痛苦。
设置："修改表"->选定主键->"列属性"->"标识规范"选择"是"
2) uniqueidentifier(又称GUID、UUID)
GUID算法是一种可以产生唯一表示的高效算法，它使用网卡MAC、地址、纳秒级时间、芯片ID码等算出来的，这样保证每次生成的GUID永远不会重复，无论是同一计算机还是不同计算机。在公元3400年前产生的GUID与任何其他产生过的GUID都不相同。
SQL Server中生成GUID的函数newid()。
优点：效率高、数据导入导出方便;
缺点：占用空间大、不易读。
业界主流倾向于使用GUID。
写在后面：看着洋洋洒洒的一大篇，除了一些常识性的东西和涉及到数据库的增删改查之外，其实没什么新鲜东西，但判断一个程序员水平的高低不仅是做过多么大的一个项目，更是对基础知识的掌握程度。初出茅庐，一家之言，欢迎赐教!J
原文链接：http://www.cnblogs.com/fanyong/archive/2011/08/08/2131316.html
