SQL语句相关概念及练习之基础篇(1)

发布时间：2011-08-10 13:24 来源：未知

写在过去不久的文章里：最近在上海找工作，前前后后面试了N家单位，发现各个公司对程序员的数据库知识尤其是SQL语句的掌握程度有很高的要求，作为一名光荣的程序员，不会玩儿SQL语句走在街上根本不好意思和人打招呼!好了，废话不多说，新手菜鸟同志们了注意了，该文提供的例子很简单，但是也很重要，请认真练习!别等到面试的时候被某些人嘲讽"唉!这年头，会写SQL语句的程序员越来越少了!L"老鸟高手同志们，你们可以优雅地飘过，但是有什么意见或建议都要提出来哦，大家一起进步嘛J，让菜鸟变成高手，提高我国的编程水平。

一、SQL 基础知识

1、DDL(数据定义语言)

1)创建数据表

--创建数据表 create table Test(Id int not null, Age char(20))；  --创建数据表 create table T_Person1(Id int not null, Name nvarchar(50), Age int null)；  --创建表，添加外键 Create table T_Students( StudentNo char(4), CourseNo char(4), Score int, Primary key(StudentNo), Foreign key(CourseNo) References T_Course(CourseNo) ); 

2)修改表结构

--修改表结构，添加字段 Alter table T_Person add NickName nvarchar(50) null;  --修改表结构，删除字段 Alter table T_Person Drop NickName; 

3)删除数据表

--删除数据表 Drop table T_Person;  --删除数据表 drop table test 

4)创建索引

Create [Unique] Index <索引名> on <基本表名>(<列明序列>); 

2、DML（数据操纵语言）

二、SQL Server 中的数据类型

三、SQL中的内置函数

-------------------------------------- -----数据汇总-聚合函数--------- --------------------------------------  --查询T_Employee表中数据条数 selectCOUNT(*)fromT_Employee  --查询工资最高的人 selectMAX(FSalary)asTop1fromT_Employee  --查询工资最低的人 selectMin(FSalary)asBottom1fromT_Employee  --查询工资的平均水平 selectAvg(FSalary)as平均水平fromT_Employee  --所有工资的和 selectSUM(FSalary)as总工资fromT_Employee  --查询工资大于5K的员工总数 selectCOUNT(*)astotalfromT_Employee whereFSalary>5000  ------------------------------ -----数据排序------- ------------------------------  --按年龄排序升序,默认是升序 select*fromT_Employee orderbyFAgeASC  --多个条件排序,先什么,后什么,在前一个条件相同的情况下,根据后一个条件进行排列  --where在orderby之前 select*fromT_Employee orderbyFAgeASC,FSalaryDESC  ------------------------------ -----模糊匹配------- ------------------------------  --通配符查询 --1.单字符通配符_ --2.多字符通配符% --以DEV开头的任意个字符串 select*fromT_Employee whereFNumberlike'DEV%'  --以一个字符开头,om结尾的字符串 select*fromT_Employee whereFNamelike'_om'  --检索姓名中包含m的字符 select*fromT_Employee whereFNamelike'%m%'  ------------------------------ -----空值处理------- ------------------------------ --null表示不知道,不是没有值 --null和其他值计算结果是null selectnull+1  --查询名字是null的数据 select*fromT_Employee whereFNameisnull  --查询名字不为空null的数据 select*fromT_Employee whereFNameisnotnull  --年龄是23,25,28中的员工 select*fromT_Employee whereFAge=23orFAge=25orFAge=28  --或者用in集合查询 --年龄是23,25,28中的员工 select*fromT_Employee whereFAgein(23,25,28)  --年龄在20到25之间的员工信息 select*fromT_Employee whereFAge>20andFAge<25  --年龄在20到25之间的员工信息,包含25 select*fromT_Employee whereFAgebetween20and25  ------------------------------ -----数据分组------- ------------------------------ SelectFAge,COUNT(*)fromT_Employee groupbyFAge  --1.根据年龄进行分组 --2.再取出分组后的年龄的个数 --注意:没有出现在groupby子句中的字段,不能出现在select语句后的列名列表中(聚合函数除外) --groupby必须出现在where后面 SelectFAge,AVG(FSalary),COUNT(*)fromT_Employee groupbyFAge  --错误用法 SelectFAge,FName,COUNT(*)fromT_Employee groupbyFAge  --加上where的groupby子句 --groupby必须出现在where后面 SelectFAge,AVG(FSalary),COUNT(*)fromT_Employee whereFAge>=25 groupbyFAge  --Having不能包含查不到的字段,只能包含聚合函数和本次查询有关的字段 selectFAge,COUNT(*)fromT_Employee groupbyFAge HavingCOUNT(*)>1 selectFAge,COUNT(*)fromT_Employee whereFSalary>2500 groupbyFAge  --HAVING子句中的列'T_Employee.FSalary'无效，因为该列没有包含在聚合函数或GROUPBY子句中 --Having是对分组后信息的过滤，能用的列和select中能有的列是一样的。 --因此，having不能代替where selectFAge,COUNT(*)fromT_Employee groupbyFAge HavingFSalary>2500  ------------------------------ -----确定结果集行数------- ------------------------------ --取出所有员工的信息,根据工资降序排列 select*fromT_Employee orderbyFSalaryDESC  --取出前三名员工的信息,根据工资降序排列 selecttop3*fromT_Employee orderbyFSalaryDESC  --根据工资取出排名在6-8的员工信息,按工资降排列 selecttop3*fromT_Employee whereFNumbernotin (selecttop5FNumberfromT_EmployeeorderbyFSalaryDESC) orderbyFSalaryDESC  ---修改数据表，添加字段，更新字段的值等操作。 altertableT_EmployeeaddFSubCompanyvarchar(20) altertableT_EmployeeaddFDepartmentvarchar(20) updateT_EmployeesetFSubCompany='Beijing',FDepartment='Development' whereFNumber='DEV001'; updateT_EmployeesetFSubCompany='ShenZhen',FDepartment='Development' whereFNumber='DEV002'; updateT_EmployeesetFSubCompany='Beijing',FDepartment='HumanResource' whereFNumber='HR001'; updateT_EmployeesetFSubCompany='Beijing',FDepartment='HumanResource' whereFNumber='HR002'; updateT_EmployeesetFSubCompany='Beijing',FDepartment='InfoTech' whereFNumber='IT001'; updateT_EmployeesetFSubCompany='ShenZhen',FDepartment='InfoTech' whereFNumber='IT002' updateT_EmployeesetFSubCompany='Beijing',FDepartment='Sales' whereFNumber='SALES001'; updateT_EmployeesetFSubCompany='Beijing',FDepartment='Sales' whereFNumber='SALES002'; updateT_EmployeesetFSubCompany='ShenZhen',FDepartment='Sales' whereFNumber='SALES003'; select*fromT_Employee  ------------------------------ ------去掉重复数据------ ------------------------------ --所有员工的部门信息 selectDistinctFDepartmentfromT_Employee; selectFDepartment,FSubCompany fromT_Employee --以上两个例子结合起来比较，Distinct针对的是整行进行比较的 selectDistinctFDepartment,FSubCompany fromT_Employee  ----------------------------- -----联合结果集Union-------- ------------------------------ --创建一个测试表T_TempEmployee，并插入数据 CreateTableT_TempEmployee(FIdCardNumbervarchar(20),FNamevarchar(20),FAgeint,Primarykey(FIdCardNumber)); insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890121','Sarani',33); insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890122','Tom',26); insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890123','Yamaha',38); insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890124','Tina',36); insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890125','Konkaya',29); insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890126','Foortia',29); select*fromT_TempEmployee  --Union关键字，联合2个结果 --把2个查询结果结合为1个查询结果 --要求:上下2个查询语句的字段(个数,名字,类型相容)必须一致 selectFName,FagefromT_TempEmployee union selectFName,FagefromT_Employee selectFNumber,FName,Fage,FDepartmentfromT_Employee union selectFIdCardNumber,FName,Fage,'临时工，无部门'fromT_TempEmployee  ---UnionAll:不合并重复数据 --Union：合并重复数据 selectFName,FAgefromT_Employee unionall selectFName,FAgefromT_TempEmployee selectFAgefromT_Employee union selectFAgefromT_TempEmployee  --注意:Union因为要进行重复值扫描,所以效率低,因此假如不是确定要合并重复,那么就用Unionall --例子:报名 select'正式员工最高年龄',MAX(FAge)fromT_Employee unionall select'正式员工最低年龄',MIN(FAge)fromT_Employee unionall select'临时工最高年龄',MAX(FAge)fromT_TempEmployee unionall select'临时工最低年龄',MIN(FAge)fromT_TempEmployee  --查询每位正式员工的信息,包括工号,工资,并且在最后一行加上员工工资额合计 selectFNumber,FSalaryfromT_Employee unionall select'工资额合计',SUM(FSalary)fromT_Employee 

------------------------------ -----SQL其他内置函数------ ------------------------------  --1.数学函数 --ABS():求绝对值 --CEILING():舍入到最大整数 --FLOOR():舍入到最小整数 --ROUND():四舍五入  selectABS(-3) selectCEILING(3.33) selectCEILING(-3.61) selectFLOOR(2.98) selectFLOOR(-3.61) selectROUND(-3.61,1)--第二个参数是精度，小数点后的位数 selectROUND(-3.61,0) selectROUND(3.1415926,3)  --2.字符串函数 --LEN():计算字符串长度 --LOWER(),UPPER():转大小写 --LTRIM():去掉字符串左侧的空格 --RTRIM():去掉字符串右侧的空格 --SUBSTRING(string,start_positoin,length): --索引从1开始 selectSUBSTRING('abc111',2,3)--结果是bc1 selectFName,SUBSTRING(FName,2,2)fromT_Employee selectLEN('abc')--结果是3 selectFName,LEN(FName)fromT_Employee  --没有可以同时既去掉左边空格、又去掉右边空格的TRIM()内置函数，所以先左后右的进行TRim，当然，你也可以先右后左 selectLTRIM('abc'),RTRIM('abc'),LEN(LTRIM(RTRIM('abc')))  --3.日期函数 --GETDATE():获取当前日期时间 --DATEADD(datepart,numbre,date):计算增加以后的日期， --参数date为待计算的日期；参数number为增量；参数datepart为计量单位,时间间隔单位； --DATEDIFF(datepart,startdate,enddate):计算2个日期之间的差额 --DATEPART(datepart,date):返回一个日期的特定部分,比如年月日,时分秒等.  /*  值缩写（SqlServer）(Access和ASP)说明 YearYyyyyy年1753~9999 QuarterQqq季1~4 MonthMmm月1~12 DayofyearDyy一年的日数,一年中的第几日1-366 DayDdd日，1-31 WeekdayDww一周的日数，一周中的第几日1-7 WeekWkww周，一年中的第几周0~51 HourHhh时0~23 MinuteMin分钟0~59 SecondSss秒0~59 MillisecondMs-毫秒0~999 */  selectDATEADD(DAY,3,getdate()) selectDATEADD(MONTH,-3,getdate()) selectDATEADD(HOUR,8,getdate()) selectDATEDIFF(YEAR,'1989-05-01',GETDATE()) selectDATEDIFF(HH,GETDATE(),DATEADD(DAY,-3,GETDATE()))  --查询员工的工龄,年为单位 selectFName,FInDate,DATEDIFF(year,FInDate,getdate())as工龄fromT_Employee  --取出每一年入职员工的个数V1 selectDATEDIFF(year,FInDate,getdate()),COUNT(*) fromT_Employee groupbyDATEDIFF(year,FInDate,getdate())  --取出每一年入职员工的个数V2 selectDATEPART(YEAR,FInDate),COUNT(*) fromT_Employee groupbyDATEPART(YEAR,FInDate) selectDATEPART(YEAR,GETDATE()) selectDATEPART(MONTH,GETDATE()) selectDATEPART(DAY,GETDATE()) selectDATEPART(HH,GETDATE()) selectDATEPART(MINUTE,GETDATE()) selectDATEPART(SECOND,GETDATE())  --4.类型转换函数 --CAST(expressionasdata_type) --CONVERT(data_type,expression) selectCAST('123'asint),CAST('2010-09-08'asdatetime), CONVERT(datetime,'2010-09-08'),CONVERT(varchar(20),123)  --5.空值处理函数isNull --ISNULL(expression,value) selectISNULL(FName,'佚名')as姓名fromT_Employee  --6.CASE函数用法： --1.单值判断：相当于switch.case --CASEexpression --WHENvalue1thenreturnvalue1 --WHENvalue2thenreturnvalue2 --WHENvalue3thenreturnvalue3 --ELSEdefault_return_value --END  --判断客户类型 selectFName, ( caseFLevel when1then'普通客户' when2then'会员' when3then'VIP' else'未知客户类型' End )as客户类型 fromT_Customer  --收入水平查询 selectFName, ( case whenFSalary<2000then'低收入' whenFSalary>=2000andFSalary<=5000then'中等收入' else'高收入' end )as收入水平 fromT_Employee  --这里有一道关于CASE用法的面试题 --表T中有ABC三列，用SQL语句实现:当A列大于B列时选择A列，否则选择B列； --当B列大于C列时选择B列，否则选择C列。 select ( case whena>bthenaelseb end ), ( case whenb>cthenbelsec end ) fromT  ---------------------------------------  selectFNumber, ( case whenFAmount>0thenFAmount else0 end )as收入, ( case whenFAmount<0thenABS(FAmount) else0 end )as支出 fromT -----------------------------------------  --球队比赛那个题 --有一张表T_Scroes，记录比赛成绩： --DateNameScroe --2008-8-8拜仁胜 --2008-8-9奇才胜 --2008-8-8湖人胜 --2008-8-10拜仁负 --2008-8-8拜仁负 --2008-8-12奇才胜 --要求输出下面格式： --Name胜负 --拜仁12 --湖人10 --奇才20 --注意：在中文字符串前加N，比如N'胜' createtableT_Scores( [Date]datetimenullcollate [Name]nvarchar(50) ) CREATETABLE[T_Scores]([Date][datetime]NULL, [Name][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL, [Score][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL ); INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF200000000ASDateTime),N'拜仁',N'胜'); INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF300000000ASDateTime),N'奇才',N'胜'); INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF300000000ASDateTime),N'湖人',N'胜'); INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF400000000ASDateTime),N'拜仁',N'负'); INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF200000000ASDateTime),N'拜仁',N'负'); INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF600000000ASDateTime),N'奇才',N'胜'); select*fromT_Scores  --列出第一个表格 --统计每支队伍的胜负情况 selectName, ( caseScore whenN'胜'then1 else0 end )as胜, ( caseScore whenN'负'then1 else0 end )as负 fromT_Scores selectName, sum ( caseScore whenN'胜'then1 else0 end )as胜, sum ( caseScore whenN'负'then1 else0 end )as负 fromT_Scores groupbyName --根据每个队的胜负判断出胜负的场数 

--题5)创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号，对方号码，通话开始时间，通话结束时间，。 --创建一张表T_Callers，记录电话呼叫员的工作流水，记录呼叫员编号、对方号码、通话开始时间、通话结束时间。建表、插数据等最后都自己写SQL语句。 --要求： --1)输出所有数据中通话时间最长的5条记录。 --2)输出所有数据中拨打长途号码（对方号码以0开头）的总时长。 --3)输出本月通话总时长最多的前三个呼叫员的编号。  --4)输出本月拨打电话次数最多的前三个呼叫员的编号。  --5)输出所有数据的拨号流水，并且在最后一行添加总呼叫时长。  --记录呼叫员编号、对方号码、通话时长  --......  --汇总[市内号码总时长][长途号码总时长]   --IdCallerNumberTellNumberStartDateTimeEndDateTime  --1001020888888882010-7-1010:012010-7-1010:05  --2001020888888882010-7-1113:412010-7-1113:52  --3001898989892010-7-1114:422010-7-1114:49  --4002021883689812010-7-1321:042010-7-1321:18  --5002767676762010-6-2920:152010-6-2920:30  --6001022888782432010-7-1513:402010-7-1513:56  --7003672546862010-7-1311:062010-7-1311:19  --8003862314452010-6-1919:192010-6-1919:25  --9001874223682010-6-1919:252010-6-1919:36  --10004400458622452010-6-1919:502010-6-1919:59  --创建表  createtableT_CallRecords(  idintnotnull,  CallerNumbervarchar(3),  TellNumbervarchar(13),  StartDateTImedatetime,  EndDateTimedatetime,  Primarykey(Id)  ); --插入数据  insertintoT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTIme)  values(1,'001','02088888888','2010-7-1010:01','2010-7-1010:05');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(2,'002','02088888888','2010-7-1113:41','2010-7-1113:52');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(3,'003','89898989','2010-7-1114:42','2010-7-1114:49');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(4,'004','02188368981','2010-7-1321:04','2010-7-1321:18');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(5,'005','76767676','2010-6-2920:15','2010-6-2920:30');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(6,'006','02288878243','2010-7-1513:40','2010-7-1513:56');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(7,'007','67254686','2010-7-1311:06','2010-7-1311:19');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(8,'008','86231445','2010-6-1919:19','2010-6-1919:25');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(9,'009','87422368','2010-6-1919:25','2010-6-1919:36');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(10,'010','40045862245','2010-6-1919:50','2010-6-1919:59');  --修改呼叫员编号  UPDATET_CallRecordsSETCallerNumber='001'WHEREIdIN(1,2,3,6,9);  UPDATET_CallRecordsSETCallerNumber='002'WHEREIdIN(4,5);  UPDATET_CallRecordsSETCallerNumber='003'WHEREIdIN(7,8);  UPDATET_CallRecordsSETCallerNumber='004'WHEREId=10;  --数据汇总  select*fromT_CallRecords    --题1)：输出所有数据中通话时间最长的5条记录。  --@计算通话时间；  --@按通话时间降序排列；  --@取前5条记录。  selecttop5CallerNumber,DATEDIFF(SECOND,StartDateTime,EndDateTime)as总时长  fromT_CallRecords  orderbyDATEDIFF(SECOND,StartDateTime,EndDateTime)DESC  --题2)：输出所有数据中拨打长途号码（对方号码以0开头）的总时长  --@查询拨打长途号码的记录；  --@计算各拨打长途号码的通话时长；  --@对各拨打长途号码的通话时长进行求和。  selectSUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))as总时长fromT_CallRecords  whereTellNumberlike'0%'  --题3)：输出本月通话总时长最多的前三个呼叫员的编号。  --@按呼叫员编号进行分组；  --@计算各呼叫员通话总时长；  --@按通话总时长进行降序排列；  --@查询前3条记录中呼叫员的编号。  selectdatediff(month,convert(datetime,'2010-06-01'),convert(datetime,'2010-07-22'))--测试  selectCallerNumber,TellNumber,datediff(month,StartDateTime,EndDateTime)  fromT_CallRecords  selecttop3CallerNumberfromT_CallRecords  wheredatediff(month,StartDateTime,getdate())=12--一年前的  groupbyCallerNumber  orderbySUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))DESC 

--题4)输出本月拨打电话次数最多的前三个呼叫员的编号.  --@按呼叫员编号进行分组；  --@计算个呼叫员拨打电话的次数；  --@按呼叫员拨打电话的次数进行降序排序；  --@查询前3条记录中呼叫员的编号。  selecttop3CallerNumber,count(*)  fromT_CallRecords  wheredatediff(month,StartDateTime,getdate())=12--一年前的  groupbyCallerNumber  orderbycount(*)DESC    --题5)输出所有数据的拨号流水，并且在最后一行添加总呼叫时长：    --记录呼叫员编号、对方号码、通话时长  --......  --汇总[市内号码总时长][长途号码总时长]    --@计算每条记录中通话时长；  --@查询包含不加0号码，即市内号码的记录；  --@计算市内号码通话总时长；  --@查询包含加0号码，即长途号码的记录；  --@计算长途号码通话总时长；  --@联合查询。  select'汇总'as汇总,  convert(varchar(20),  sum((  case  whenTellNumbernotlike'0%'thendatediff(second,StartDateTime,EndDateTime)  else0  end  )))as市内通话,  sum((  case  whenTellNumberlike'0%'thendatediff(second,StartDateTime,EndDateTime)  else0  end  ))as长途通话  fromT_CallRecords  unionall  selectCallerNumber,TellNumber,datediff(second,StartDateTime,EndDateTime)as通话时长  fromT_CallRecords      --客户和订单表的练习  --建立一个客户表  createtableT_Customers(  idintnotnull,  namenvarchar(50)collatechinese_prc_ci_asnull,  ageintnull  );  insertT_Customers(id,name,age)values(1,N'tom',10);  insertT_Customers(id,name,age)values(2,N'jerry',15);  insertT_Customers(id,name,age)values(3,N'john',22);  insertT_Customers(id,name,age)values(4,N'lily',18);  insertT_Customers(id,name,age)values(5,N'lucy',18);    select*fromT_Customers    --建立一个销售单表  createtableT_Orders(  idintnotnull,  billnonvarchar(50)collatechinese_prc_ci_asnull,  customeridintnull);    insertT_Orders(id,billno,customerid)values(1,N'001',1)  insertT_Orders(id,billno,customerid)values(2,N'002',1)  insertT_Orders(id,billno,customerid)values(3,N'003',3)  insertT_Orders(id,billno,customerid)values(4,N'004',2)  insertT_Orders(id,billno,customerid)values(5,N'005',2)  insertT_Orders(id,billno,customerid)values(6,N'006',5)  insertT_Orders(id,billno,customerid)values(7,N'007',4)  insertT_Orders(id,billno,customerid)values(8,N'008',5)    select*fromT_Orders    selecto.billno,c.name,c.age  fromT_OrdersasojoinT_Customersascono.customerid=c.id  --查询订单号,顾客名字,顾客年龄    selecto.billno,c.name,c.age  fromT_OrdersasojoinT_Customersascono.customerid=c.id  wherec.age>15  --显示年龄大于15岁的顾客姓名、年龄和订单号    selecto.billno,c.name,c.age  fromT_OrdersasojoinT_Customersascono.customerid=c.id  wherec.age>(selectavg(age)fromT_Customers)  --显示年龄大于平均年龄的顾客姓名、年龄和订单号    --子查询练习  --新建一个数据库，名为BookShop  CreatedatabaseBookShop    --创建4张表  createtableT_Reader(FIdINTNOTNULL,FNamevarchar(50),FYearOfBirthINT,FCityvarchar(50),FProvincevarchar(50),FYearOfJoinINT);  createtableT_Book(FIdintnotnull,FNamevarchar(50),FYearPublishedint,FCategoryIdint);  createtableT_Category(FIdintnotnull,FNamevarchar(50));  createtableT_ReaderFavorite(FCategoryIdint,FReaderIdint);    --分别为4张表插入数据  insertintoT_Category(FId,FName)values(1,'Story');  insertintoT_Category(FId,FName)values(2,'History');  insertintoT_Category(FId,FName)values(3,'Theory');  insertintoT_Category(FId,FName)values(4,'Technology');  insertintoT_Category(FId,FName)values(5,'Art');  insertintoT_Category(FId,FName)values(6,'Philosophy');    insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(1,'Tom',1979,'TangShan','Hebei',2003);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(2,'Sam',1981,'LangFang','Hebei',2001);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(3,'Jerry',1966,'DongGuan','GuangDong',1995);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(4,'Lily',1972,'JiaXing','ZheJiang',2005);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(5,'Marry',1985,'BeiJing','BeiJing',1999);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(6,'Kelly',1977,'ZhuZhou','HuNan',1995);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(7,'Tim',1982,'YongZhou','HuNan',2001);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(8,'King',1979,'JiNan','ShanDong',1997);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(11,'John',1979,'QingDao','ShanDong',2003);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(9,'Lucy',1978,'LuoYang','HeNan',1996);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(10,'July',1983,'ZhuMaDian','HeNan',1999);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,fProvince,FyearOfJoin)values(12,'Fige',1981,'JinCheng','ShanXi',2003);    insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(1,'AboutJ2EE',2005,4);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(2,'LearningHibernate',2003,4);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(3,'TowCites',1999,1);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(4,'JaneEyre',2001,1);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(5,'OliverTwist',2002,1);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(6,'HistoryofChina',1982,2);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(7,'HistoryofEngland',1860,2);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(8,'HistoryofAmerica',1700,2);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(9,'HistoryofTheVorld',2008,2);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(10,'Atom',1930,3);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(11,'RELATIVITY',1945,3);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(12,'Computer',1970,3);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(13,'Astronomy',1971,3);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(14,'HowTosinging',1771,5);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(15,'DaoDeJing',2001,6);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(16,'ObediencetoAu',1995,6);      insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,1);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(5,2);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(2,3);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(3,4);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(5,5);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,6);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,7);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(4,8);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(6,9);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(5,10);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(2,11);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(2,12);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,12);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(3,1);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,3);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(4,4);    select*fromT_Book    select*fromT_Category    select*fromT_Reader    select*fromT_ReaderFavorite    --并列查询  select1asf1,2,(selectMIN(FYearPublished)fromT_Book),  (selectMAX(FYearPublished)fromT_Book)asf4    --查询入会日期在2001或者2003年的读者信息  select*fromT_Reader  whereFYearOfJoinin(2001,2003)    --与between...and不同  select*fromT_Reader  whereFYearOfJoinbetween2001and2003    --查询有书出版的年份入会的读者信息  select*fromT_Reader  whereFYearOfJoinin  (  selectFYearPublishedfromT_Book  )    --SQLServer2005之后的版本内置函数：ROW_NUMBER()，称为开窗函数，可以进行分页等操作。  selectROW_NUMBER()over(orderbyFSalaryDESC)asRow_Num,  FNumber,FName,FSalary,FAgefromT_Employee --特别注意，开窗函数row_number()只能用于select或orderby子句中，不能用于where子句中  --查询第3行到第5行的数据 select*from ( selectROW_NUMBER()over(orderbyFSalaryDESC)asRow_Num, FNumber,FName,FSalary,FAgefromT_Employee )ase1 wheree1.Row_Num>=3ande1.Row_Num<=5 

四、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

写在过去不久的文章里：最近在上海找工作，前前后后面试了N家单位，发现各个公司对程序员的数据库知识尤其是SQL语句的掌握程度有很高的要求，作为一名光荣的程序员，不会玩儿SQL语句走在街上根本不好意思和人打招呼!好了，废话不多说，新手菜鸟同志们了注意了，该文提供的例子很简单，但是也很重要，请认真练习!别等到面试的时候被某些人嘲讽"唉!这年头，会写SQL语句的程序员越来越少了!L"老鸟高手同志们，你们可以优雅地飘过，但是有什么意见或建议都要提出来哦，大家一起进步嘛J，让菜鸟变成高手，提高我国的编程水平。

一、SQL 基础知识

1、DDL(数据定义语言)

1)创建数据表

--创建数据表 create table Test(Id int not null, Age char(20))；  --创建数据表 create table T_Person1(Id int not null, Name nvarchar(50), Age int null)；  --创建表，添加外键 Create table T_Students( StudentNo char(4), CourseNo char(4), Score int, Primary key(StudentNo), Foreign key(CourseNo) References T_Course(CourseNo) ); 

2)修改表结构

--修改表结构，添加字段 Alter table T_Person add NickName nvarchar(50) null;  --修改表结构，删除字段 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)更新语句

--修改列，把所有的age字段改为30 update T_Person1 set age=30  --把所有的Age字段和Name字段设置为... update T_Person1 set Age=50,Name='Lucy'  update T_Person1 set Name='Frankie' where Age=30  update T_Person1 set Name=N'中文字符' where Age=20  --中文字符前面最好加上N，以防出现乱码 update T_Person1 set Name=N'成年人' where Age=30 or Age=50 

3)删除语句

delete from T_Person1 --删除表中全部数据 delete from T_Person1 where Name='Tom' --根据条件删除数据 

4)查询语句

查询语句非常强大，几乎可以查任意东西！

----------------- ---- 数据检索 ----- ----------------- --查询不与任何表关联的数据. SELECT 1+1; --简单运算 select 1+2 as 结果 SELECT newid();--查询一个GUID字符创 select GETDATE() as 日期 --查询日期 --可以查询SQLServer版本 select @@VERSION as SQLServer版本 --一次查询多个 select 1+1 结果, GETDATE() as 日期, @@VERSION as 版本, NEWID() as 编号 --简单的数据查询.HelloWorld级别 SELECT * FROM T_Employee; --只查询需要的列. SELECT FNumber FROM T_Employee; --给列取别名.As关键字 SELECT FNumber AS 编号, FName AS 姓名 FROM T_Employee; --使用 WHERE 查询符合条件的记录. SELECT FName FROM T_Employee WHERE FSalary<5000; --对表记录进行排序，默认排序规则是ASC SELECT * FROM T_Employee ORDER BY FAge ASC,FSalary DESC; --ORDER BY 子句要放在 WHERE 子句之后. SELECT * FROM T_Employee WHERE FAge>23 ORDER BY FAge DESC,FSalary DESC; --WHERE 中可以使用的逻辑运算符：or、and、not、<、>、=、>=、<=、!=、<>等. --模糊匹配，首字母未知. SELECT * FROM T_Employee WHERE FName LIKE '_arry'; --模糊匹配，前后多个字符未知. SELECT * FROM T_Employee WHERE FName LIKE '%n%'; --NULL 表示"不知道"，有 NULL 参与的运算结果一般都为 NULL. --查询数据是否为 NULL，不能用 = 、!= 或 <>，要用IS关键字 SELECT * FROM T_Employee WHERE FName IS NULL; SELECT * FROM T_Employee WHERE FName IS NOT NULL; --查询在某个范围内的数据，IN 表示包含于，IN后面是一个集合 SELECT * FROM T_Employee WHERE FAge IN (23, 25, 28); --下面两条查询语句等价。 SELECT * FROM T_Employee WHERE FAge>=23 AND FAge<=30; SELECT * FROM T_Employee WHERE FAge BETWEEN 23 AND 30; ----创建一张Employee表，以下几个Demo中会用的这张表中的数据 ----在SQL管理器中执行下面的SQL语句，在T_Employee表中进行练习 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.83) insert into T_Employee(FNumber,FName,FAge,FSalary) values('SALES001','Lucy',25,5000) insert into T_Employee(FNumber,FName,FAge,FSalary) values('SALES002','Lily',25,6200) insert into T_Employee(FNumber,FName,FAge,FSalary) values('SALES003','Vicky',25,1200) insert into T_Employee(FNumber,FName,FAge,FSalary) values('HR001','James',23,2200.88) insert into T_Employee(FNumber,FName,FAge,FSalary) values('HR002','Tom',25,5100.36) insert into T_Employee(FNumber,FName,FAge,FSalary) values('IT001','Tom',28,3900) insert into T_Employee(FNumber,FAge,FSalary) values('IT002',25,3800) --开始对T_Employee表进行各种操作 --检索所有字段 select * from T_Employee --只检索特定字段 select FName,FAge from T_Employee --带过滤条件的检索 select * from T_Employee where FSalary<5000 --可更改显示列名的关键字as，as—起别名 select FName as 姓名,FAge as 年龄,FSalary as 薪水 from T_Employee  

二、SQL Server 中的数据类型

1、精确数字类型

bigint int smallint tinyint bit money smallmoney 

2、字符型数据类型，MS建议用VarChar(max)代替Text

Char VarChar Text 

3、近似数字类型

Decimal Numeric Real Float 

4、Unicode字符串类型

Nchar NvarChar Ntext 

5、二进制数据类型,MS建议VarBinary(Max)代替Image数据类型，max=231-1

Binary(n) 存储固定长度的二进制数据 VarBinary(n) 存储可变长度的二进制数据，范围在n~(1,8000) Image 存储图像信息 

6、日期和时间类型,数据范围不同，精确地不同

DateTime SmallDateTime 

7、特殊用途数据类型

Cursor Sql-variant Table TimeStamp UniqueIdentifier XML  

三、SQL中的内置函数

-------------------------------------- -----数据汇总-聚合函数--------- --------------------------------------  --查询T_Employee表中数据条数 selectCOUNT(*)fromT_Employee  --查询工资最高的人 selectMAX(FSalary)asTop1fromT_Employee  --查询工资最低的人 selectMin(FSalary)asBottom1fromT_Employee  --查询工资的平均水平 selectAvg(FSalary)as平均水平fromT_Employee  --所有工资的和 selectSUM(FSalary)as总工资fromT_Employee  --查询工资大于5K的员工总数 selectCOUNT(*)astotalfromT_Employee whereFSalary>5000  ------------------------------ -----数据排序------- ------------------------------  --按年龄排序升序,默认是升序 select*fromT_Employee orderbyFAgeASC  --多个条件排序,先什么,后什么,在前一个条件相同的情况下,根据后一个条件进行排列  --where在orderby之前 select*fromT_Employee orderbyFAgeASC,FSalaryDESC  ------------------------------ -----模糊匹配------- ------------------------------  --通配符查询 --1.单字符通配符_ --2.多字符通配符% --以DEV开头的任意个字符串 select*fromT_Employee whereFNumberlike'DEV%'  --以一个字符开头,om结尾的字符串 select*fromT_Employee whereFNamelike'_om'  --检索姓名中包含m的字符 select*fromT_Employee whereFNamelike'%m%'  ------------------------------ -----空值处理------- ------------------------------ --null表示不知道,不是没有值 --null和其他值计算结果是null selectnull+1  --查询名字是null的数据 select*fromT_Employee whereFNameisnull  --查询名字不为空null的数据 select*fromT_Employee whereFNameisnotnull  --年龄是23,25,28中的员工 select*fromT_Employee whereFAge=23orFAge=25orFAge=28  --或者用in集合查询 --年龄是23,25,28中的员工 select*fromT_Employee whereFAgein(23,25,28)  --年龄在20到25之间的员工信息 select*fromT_Employee whereFAge>20andFAge<25  --年龄在20到25之间的员工信息,包含25 select*fromT_Employee whereFAgebetween20and25  ------------------------------ -----数据分组------- ------------------------------ SelectFAge,COUNT(*)fromT_Employee groupbyFAge  --1.根据年龄进行分组 --2.再取出分组后的年龄的个数 --注意:没有出现在groupby子句中的字段,不能出现在select语句后的列名列表中(聚合函数除外) --groupby必须出现在where后面 SelectFAge,AVG(FSalary),COUNT(*)fromT_Employee groupbyFAge  --错误用法 SelectFAge,FName,COUNT(*)fromT_Employee groupbyFAge  --加上where的groupby子句 --groupby必须出现在where后面 SelectFAge,AVG(FSalary),COUNT(*)fromT_Employee whereFAge>=25 groupbyFAge  --Having不能包含查不到的字段,只能包含聚合函数和本次查询有关的字段 selectFAge,COUNT(*)fromT_Employee groupbyFAge HavingCOUNT(*)>1 selectFAge,COUNT(*)fromT_Employee whereFSalary>2500 groupbyFAge  --HAVING子句中的列'T_Employee.FSalary'无效，因为该列没有包含在聚合函数或GROUPBY子句中 --Having是对分组后信息的过滤，能用的列和select中能有的列是一样的。 --因此，having不能代替where selectFAge,COUNT(*)fromT_Employee groupbyFAge HavingFSalary>2500  ------------------------------ -----确定结果集行数------- ------------------------------ --取出所有员工的信息,根据工资降序排列 select*fromT_Employee orderbyFSalaryDESC  --取出前三名员工的信息,根据工资降序排列 selecttop3*fromT_Employee orderbyFSalaryDESC  --根据工资取出排名在6-8的员工信息,按工资降排列 selecttop3*fromT_Employee whereFNumbernotin (selecttop5FNumberfromT_EmployeeorderbyFSalaryDESC) orderbyFSalaryDESC  ---修改数据表，添加字段，更新字段的值等操作。 altertableT_EmployeeaddFSubCompanyvarchar(20) altertableT_EmployeeaddFDepartmentvarchar(20) updateT_EmployeesetFSubCompany='Beijing',FDepartment='Development' whereFNumber='DEV001'; updateT_EmployeesetFSubCompany='ShenZhen',FDepartment='Development' whereFNumber='DEV002'; updateT_EmployeesetFSubCompany='Beijing',FDepartment='HumanResource' whereFNumber='HR001'; updateT_EmployeesetFSubCompany='Beijing',FDepartment='HumanResource' whereFNumber='HR002'; updateT_EmployeesetFSubCompany='Beijing',FDepartment='InfoTech' whereFNumber='IT001'; updateT_EmployeesetFSubCompany='ShenZhen',FDepartment='InfoTech' whereFNumber='IT002' updateT_EmployeesetFSubCompany='Beijing',FDepartment='Sales' whereFNumber='SALES001'; updateT_EmployeesetFSubCompany='Beijing',FDepartment='Sales' whereFNumber='SALES002'; updateT_EmployeesetFSubCompany='ShenZhen',FDepartment='Sales' whereFNumber='SALES003'; select*fromT_Employee  ------------------------------ ------去掉重复数据------ ------------------------------ --所有员工的部门信息 selectDistinctFDepartmentfromT_Employee; selectFDepartment,FSubCompany fromT_Employee --以上两个例子结合起来比较，Distinct针对的是整行进行比较的 selectDistinctFDepartment,FSubCompany fromT_Employee  ----------------------------- -----联合结果集Union-------- ------------------------------ --创建一个测试表T_TempEmployee，并插入数据 CreateTableT_TempEmployee(FIdCardNumbervarchar(20),FNamevarchar(20),FAgeint,Primarykey(FIdCardNumber)); insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890121','Sarani',33); insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890122','Tom',26); insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890123','Yamaha',38); insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890124','Tina',36); insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890125','Konkaya',29); insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890126','Foortia',29); select*fromT_TempEmployee  --Union关键字，联合2个结果 --把2个查询结果结合为1个查询结果 --要求:上下2个查询语句的字段(个数,名字,类型相容)必须一致 selectFName,FagefromT_TempEmployee union selectFName,FagefromT_Employee selectFNumber,FName,Fage,FDepartmentfromT_Employee union selectFIdCardNumber,FName,Fage,'临时工，无部门'fromT_TempEmployee  ---UnionAll:不合并重复数据 --Union：合并重复数据 selectFName,FAgefromT_Employee unionall selectFName,FAgefromT_TempEmployee selectFAgefromT_Employee union selectFAgefromT_TempEmployee  --注意:Union因为要进行重复值扫描,所以效率低,因此假如不是确定要合并重复,那么就用Unionall --例子:报名 select'正式员工最高年龄',MAX(FAge)fromT_Employee unionall select'正式员工最低年龄',MIN(FAge)fromT_Employee unionall select'临时工最高年龄',MAX(FAge)fromT_TempEmployee unionall select'临时工最低年龄',MIN(FAge)fromT_TempEmployee  --查询每位正式员工的信息,包括工号,工资,并且在最后一行加上员工工资额合计 selectFNumber,FSalaryfromT_Employee unionall select'工资额合计',SUM(FSalary)fromT_Employee 

------------------------------ -----SQL其他内置函数------ ------------------------------  --1.数学函数 --ABS():求绝对值 --CEILING():舍入到最大整数 --FLOOR():舍入到最小整数 --ROUND():四舍五入  selectABS(-3) selectCEILING(3.33) selectCEILING(-3.61) selectFLOOR(2.98) selectFLOOR(-3.61) selectROUND(-3.61,1)--第二个参数是精度，小数点后的位数 selectROUND(-3.61,0) selectROUND(3.1415926,3)  --2.字符串函数 --LEN():计算字符串长度 --LOWER(),UPPER():转大小写 --LTRIM():去掉字符串左侧的空格 --RTRIM():去掉字符串右侧的空格 --SUBSTRING(string,start_positoin,length): --索引从1开始 selectSUBSTRING('abc111',2,3)--结果是bc1 selectFName,SUBSTRING(FName,2,2)fromT_Employee selectLEN('abc')--结果是3 selectFName,LEN(FName)fromT_Employee  --没有可以同时既去掉左边空格、又去掉右边空格的TRIM()内置函数，所以先左后右的进行TRim，当然，你也可以先右后左 selectLTRIM('abc'),RTRIM('abc'),LEN(LTRIM(RTRIM('abc')))  --3.日期函数 --GETDATE():获取当前日期时间 --DATEADD(datepart,numbre,date):计算增加以后的日期， --参数date为待计算的日期；参数number为增量；参数datepart为计量单位,时间间隔单位； --DATEDIFF(datepart,startdate,enddate):计算2个日期之间的差额 --DATEPART(datepart,date):返回一个日期的特定部分,比如年月日,时分秒等.  /*  值缩写（SqlServer）(Access和ASP)说明 YearYyyyyy年1753~9999 QuarterQqq季1~4 MonthMmm月1~12 DayofyearDyy一年的日数,一年中的第几日1-366 DayDdd日，1-31 WeekdayDww一周的日数，一周中的第几日1-7 WeekWkww周，一年中的第几周0~51 HourHhh时0~23 MinuteMin分钟0~59 SecondSss秒0~59 MillisecondMs-毫秒0~999 */  selectDATEADD(DAY,3,getdate()) selectDATEADD(MONTH,-3,getdate()) selectDATEADD(HOUR,8,getdate()) selectDATEDIFF(YEAR,'1989-05-01',GETDATE()) selectDATEDIFF(HH,GETDATE(),DATEADD(DAY,-3,GETDATE()))  --查询员工的工龄,年为单位 selectFName,FInDate,DATEDIFF(year,FInDate,getdate())as工龄fromT_Employee  --取出每一年入职员工的个数V1 selectDATEDIFF(year,FInDate,getdate()),COUNT(*) fromT_Employee groupbyDATEDIFF(year,FInDate,getdate())  --取出每一年入职员工的个数V2 selectDATEPART(YEAR,FInDate),COUNT(*) fromT_Employee groupbyDATEPART(YEAR,FInDate) selectDATEPART(YEAR,GETDATE()) selectDATEPART(MONTH,GETDATE()) selectDATEPART(DAY,GETDATE()) selectDATEPART(HH,GETDATE()) selectDATEPART(MINUTE,GETDATE()) selectDATEPART(SECOND,GETDATE())  --4.类型转换函数 --CAST(expressionasdata_type) --CONVERT(data_type,expression) selectCAST('123'asint),CAST('2010-09-08'asdatetime), CONVERT(datetime,'2010-09-08'),CONVERT(varchar(20),123)  --5.空值处理函数isNull --ISNULL(expression,value) selectISNULL(FName,'佚名')as姓名fromT_Employee  --6.CASE函数用法： --1.单值判断：相当于switch.case --CASEexpression --WHENvalue1thenreturnvalue1 --WHENvalue2thenreturnvalue2 --WHENvalue3thenreturnvalue3 --ELSEdefault_return_value --END  --判断客户类型 selectFName, ( caseFLevel when1then'普通客户' when2then'会员' when3then'VIP' else'未知客户类型' End )as客户类型 fromT_Customer  --收入水平查询 selectFName, ( case whenFSalary<2000then'低收入' whenFSalary>=2000andFSalary<=5000then'中等收入' else'高收入' end )as收入水平 fromT_Employee  --这里有一道关于CASE用法的面试题 --表T中有ABC三列，用SQL语句实现:当A列大于B列时选择A列，否则选择B列； --当B列大于C列时选择B列，否则选择C列。 select ( case whena>bthenaelseb end ), ( case whenb>cthenbelsec end ) fromT  ---------------------------------------  selectFNumber, ( case whenFAmount>0thenFAmount else0 end )as收入, ( case whenFAmount<0thenABS(FAmount) else0 end )as支出 fromT -----------------------------------------  --球队比赛那个题 --有一张表T_Scroes，记录比赛成绩： --DateNameScroe --2008-8-8拜仁胜 --2008-8-9奇才胜 --2008-8-8湖人胜 --2008-8-10拜仁负 --2008-8-8拜仁负 --2008-8-12奇才胜 --要求输出下面格式： --Name胜负 --拜仁12 --湖人10 --奇才20 --注意：在中文字符串前加N，比如N'胜' createtableT_Scores( [Date]datetimenullcollate [Name]nvarchar(50) ) CREATETABLE[T_Scores]([Date][datetime]NULL, [Name][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL, [Score][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL ); INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF200000000ASDateTime),N'拜仁',N'胜'); INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF300000000ASDateTime),N'奇才',N'胜'); INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF300000000ASDateTime),N'湖人',N'胜'); INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF400000000ASDateTime),N'拜仁',N'负'); INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF200000000ASDateTime),N'拜仁',N'负'); INSERT[T_Scores]([Date],[Name],[Score])VALUES(CAST(0x00009AF600000000ASDateTime),N'奇才',N'胜'); select*fromT_Scores  --列出第一个表格 --统计每支队伍的胜负情况 selectName, ( caseScore whenN'胜'then1 else0 end )as胜, ( caseScore whenN'负'then1 else0 end )as负 fromT_Scores selectName, sum ( caseScore whenN'胜'then1 else0 end )as胜, sum ( caseScore whenN'负'then1 else0 end )as负 fromT_Scores groupbyName --根据每个队的胜负判断出胜负的场数 

--题5)创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号，对方号码，通话开始时间，通话结束时间，。 --创建一张表T_Callers，记录电话呼叫员的工作流水，记录呼叫员编号、对方号码、通话开始时间、通话结束时间。建表、插数据等最后都自己写SQL语句。 --要求： --1)输出所有数据中通话时间最长的5条记录。 --2)输出所有数据中拨打长途号码（对方号码以0开头）的总时长。 --3)输出本月通话总时长最多的前三个呼叫员的编号。  --4)输出本月拨打电话次数最多的前三个呼叫员的编号。  --5)输出所有数据的拨号流水，并且在最后一行添加总呼叫时长。  --记录呼叫员编号、对方号码、通话时长  --......  --汇总[市内号码总时长][长途号码总时长]   --IdCallerNumberTellNumberStartDateTimeEndDateTime  --1001020888888882010-7-1010:012010-7-1010:05  --2001020888888882010-7-1113:412010-7-1113:52  --3001898989892010-7-1114:422010-7-1114:49  --4002021883689812010-7-1321:042010-7-1321:18  --5002767676762010-6-2920:152010-6-2920:30  --6001022888782432010-7-1513:402010-7-1513:56  --7003672546862010-7-1311:062010-7-1311:19  --8003862314452010-6-1919:192010-6-1919:25  --9001874223682010-6-1919:252010-6-1919:36  --10004400458622452010-6-1919:502010-6-1919:59  --创建表  createtableT_CallRecords(  idintnotnull,  CallerNumbervarchar(3),  TellNumbervarchar(13),  StartDateTImedatetime,  EndDateTimedatetime,  Primarykey(Id)  ); --插入数据  insertintoT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTIme)  values(1,'001','02088888888','2010-7-1010:01','2010-7-1010:05');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(2,'002','02088888888','2010-7-1113:41','2010-7-1113:52');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(3,'003','89898989','2010-7-1114:42','2010-7-1114:49');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(4,'004','02188368981','2010-7-1321:04','2010-7-1321:18');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(5,'005','76767676','2010-6-2920:15','2010-6-2920:30');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(6,'006','02288878243','2010-7-1513:40','2010-7-1513:56');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(7,'007','67254686','2010-7-1311:06','2010-7-1311:19');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(8,'008','86231445','2010-6-1919:19','2010-6-1919:25');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(9,'009','87422368','2010-6-1919:25','2010-6-1919:36');  INSERTINTOT_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)  VALUES(10,'010','40045862245','2010-6-1919:50','2010-6-1919:59');  --修改呼叫员编号  UPDATET_CallRecordsSETCallerNumber='001'WHEREIdIN(1,2,3,6,9);  UPDATET_CallRecordsSETCallerNumber='002'WHEREIdIN(4,5);  UPDATET_CallRecordsSETCallerNumber='003'WHEREIdIN(7,8);  UPDATET_CallRecordsSETCallerNumber='004'WHEREId=10;  --数据汇总  select*fromT_CallRecords    --题1)：输出所有数据中通话时间最长的5条记录。  --@计算通话时间；  --@按通话时间降序排列；  --@取前5条记录。  selecttop5CallerNumber,DATEDIFF(SECOND,StartDateTime,EndDateTime)as总时长  fromT_CallRecords  orderbyDATEDIFF(SECOND,StartDateTime,EndDateTime)DESC  --题2)：输出所有数据中拨打长途号码（对方号码以0开头）的总时长  --@查询拨打长途号码的记录；  --@计算各拨打长途号码的通话时长；  --@对各拨打长途号码的通话时长进行求和。  selectSUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))as总时长fromT_CallRecords  whereTellNumberlike'0%'  --题3)：输出本月通话总时长最多的前三个呼叫员的编号。  --@按呼叫员编号进行分组；  --@计算各呼叫员通话总时长；  --@按通话总时长进行降序排列；  --@查询前3条记录中呼叫员的编号。  selectdatediff(month,convert(datetime,'2010-06-01'),convert(datetime,'2010-07-22'))--测试  selectCallerNumber,TellNumber,datediff(month,StartDateTime,EndDateTime)  fromT_CallRecords  selecttop3CallerNumberfromT_CallRecords  wheredatediff(month,StartDateTime,getdate())=12--一年前的  groupbyCallerNumber  orderbySUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))DESC 

--题4)输出本月拨打电话次数最多的前三个呼叫员的编号.  --@按呼叫员编号进行分组；  --@计算个呼叫员拨打电话的次数；  --@按呼叫员拨打电话的次数进行降序排序；  --@查询前3条记录中呼叫员的编号。  selecttop3CallerNumber,count(*)  fromT_CallRecords  wheredatediff(month,StartDateTime,getdate())=12--一年前的  groupbyCallerNumber  orderbycount(*)DESC    --题5)输出所有数据的拨号流水，并且在最后一行添加总呼叫时长：    --记录呼叫员编号、对方号码、通话时长  --......  --汇总[市内号码总时长][长途号码总时长]    --@计算每条记录中通话时长；  --@查询包含不加0号码，即市内号码的记录；  --@计算市内号码通话总时长；  --@查询包含加0号码，即长途号码的记录；  --@计算长途号码通话总时长；  --@联合查询。  select'汇总'as汇总,  convert(varchar(20),  sum((  case  whenTellNumbernotlike'0%'thendatediff(second,StartDateTime,EndDateTime)  else0  end  )))as市内通话,  sum((  case  whenTellNumberlike'0%'thendatediff(second,StartDateTime,EndDateTime)  else0  end  ))as长途通话  fromT_CallRecords  unionall  selectCallerNumber,TellNumber,datediff(second,StartDateTime,EndDateTime)as通话时长  fromT_CallRecords      --客户和订单表的练习  --建立一个客户表  createtableT_Customers(  idintnotnull,  namenvarchar(50)collatechinese_prc_ci_asnull,  ageintnull  );  insertT_Customers(id,name,age)values(1,N'tom',10);  insertT_Customers(id,name,age)values(2,N'jerry',15);  insertT_Customers(id,name,age)values(3,N'john',22);  insertT_Customers(id,name,age)values(4,N'lily',18);  insertT_Customers(id,name,age)values(5,N'lucy',18);    select*fromT_Customers    --建立一个销售单表  createtableT_Orders(  idintnotnull,  billnonvarchar(50)collatechinese_prc_ci_asnull,  customeridintnull);    insertT_Orders(id,billno,customerid)values(1,N'001',1)  insertT_Orders(id,billno,customerid)values(2,N'002',1)  insertT_Orders(id,billno,customerid)values(3,N'003',3)  insertT_Orders(id,billno,customerid)values(4,N'004',2)  insertT_Orders(id,billno,customerid)values(5,N'005',2)  insertT_Orders(id,billno,customerid)values(6,N'006',5)  insertT_Orders(id,billno,customerid)values(7,N'007',4)  insertT_Orders(id,billno,customerid)values(8,N'008',5)    select*fromT_Orders    selecto.billno,c.name,c.age  fromT_OrdersasojoinT_Customersascono.customerid=c.id  --查询订单号,顾客名字,顾客年龄    selecto.billno,c.name,c.age  fromT_OrdersasojoinT_Customersascono.customerid=c.id  wherec.age>15  --显示年龄大于15岁的顾客姓名、年龄和订单号    selecto.billno,c.name,c.age  fromT_OrdersasojoinT_Customersascono.customerid=c.id  wherec.age>(selectavg(age)fromT_Customers)  --显示年龄大于平均年龄的顾客姓名、年龄和订单号    --子查询练习  --新建一个数据库，名为BookShop  CreatedatabaseBookShop    --创建4张表  createtableT_Reader(FIdINTNOTNULL,FNamevarchar(50),FYearOfBirthINT,FCityvarchar(50),FProvincevarchar(50),FYearOfJoinINT);  createtableT_Book(FIdintnotnull,FNamevarchar(50),FYearPublishedint,FCategoryIdint);  createtableT_Category(FIdintnotnull,FNamevarchar(50));  createtableT_ReaderFavorite(FCategoryIdint,FReaderIdint);    --分别为4张表插入数据  insertintoT_Category(FId,FName)values(1,'Story');  insertintoT_Category(FId,FName)values(2,'History');  insertintoT_Category(FId,FName)values(3,'Theory');  insertintoT_Category(FId,FName)values(4,'Technology');  insertintoT_Category(FId,FName)values(5,'Art');  insertintoT_Category(FId,FName)values(6,'Philosophy');    insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(1,'Tom',1979,'TangShan','Hebei',2003);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(2,'Sam',1981,'LangFang','Hebei',2001);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(3,'Jerry',1966,'DongGuan','GuangDong',1995);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(4,'Lily',1972,'JiaXing','ZheJiang',2005);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(5,'Marry',1985,'BeiJing','BeiJing',1999);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(6,'Kelly',1977,'ZhuZhou','HuNan',1995);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(7,'Tim',1982,'YongZhou','HuNan',2001);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(8,'King',1979,'JiNan','ShanDong',1997);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(11,'John',1979,'QingDao','ShanDong',2003);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(9,'Lucy',1978,'LuoYang','HeNan',1996);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(10,'July',1983,'ZhuMaDian','HeNan',1999);  insertintoT_Reader(FId,FName,FYearOfBirth,FCity,fProvince,FyearOfJoin)values(12,'Fige',1981,'JinCheng','ShanXi',2003);    insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(1,'AboutJ2EE',2005,4);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(2,'LearningHibernate',2003,4);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(3,'TowCites',1999,1);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(4,'JaneEyre',2001,1);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(5,'OliverTwist',2002,1);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(6,'HistoryofChina',1982,2);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(7,'HistoryofEngland',1860,2);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(8,'HistoryofAmerica',1700,2);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(9,'HistoryofTheVorld',2008,2);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(10,'Atom',1930,3);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(11,'RELATIVITY',1945,3);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(12,'Computer',1970,3);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(13,'Astronomy',1971,3);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(14,'HowTosinging',1771,5);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(15,'DaoDeJing',2001,6);  insertintoT_Book(FId,FName,FYearPublished,FCategoryId)values(16,'ObediencetoAu',1995,6);      insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,1);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(5,2);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(2,3);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(3,4);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(5,5);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,6);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,7);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(4,8);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(6,9);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(5,10);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(2,11);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(2,12);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,12);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(3,1);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(1,3);  insertintoT_ReaderFavorite(FCategoryId,FReaderId)values(4,4);    select*fromT_Book    select*fromT_Category    select*fromT_Reader    select*fromT_ReaderFavorite    --并列查询  select1asf1,2,(selectMIN(FYearPublished)fromT_Book),  (selectMAX(FYearPublished)fromT_Book)asf4    --查询入会日期在2001或者2003年的读者信息  select*fromT_Reader  whereFYearOfJoinin(2001,2003)    --与between...and不同  select*fromT_Reader  whereFYearOfJoinbetween2001and2003    --查询有书出版的年份入会的读者信息  select*fromT_Reader  whereFYearOfJoinin  (  selectFYearPublishedfromT_Book  )    --SQLServer2005之后的版本内置函数：ROW_NUMBER()，称为开窗函数，可以进行分页等操作。  selectROW_NUMBER()over(orderbyFSalaryDESC)asRow_Num,  FNumber,FName,FSalary,FAgefromT_Employee --特别注意，开窗函数row_number()只能用于select或orderby子句中，不能用于where子句中  --查询第3行到第5行的数据 select*from ( selectROW_NUMBER()over(orderbyFSalaryDESC)asRow_Num, FNumber,FName,FSalary,FAgefromT_Employee )ase1 wheree1.Row_Num>=3ande1.Row_Num<=5 

四、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