SQLServer常用语法和函数及练习

1、SQL数据类型：1.数据类型面加“n”的表示字符类型是字符串的，2.前有“var”的表示可变的如:varchar(10)和char(10)是不同的char(10)一定是10个字符如果不够的用空格填充，而varchar(10)不够就不够。3.bit:bool类型可选值,1,0 SQL语句(增删改就不写了)1.创建表personcreate database persongocreate table T_person(id int not null Primary key,name nvarchar(50)null,age int null)2.删除表drop table T_person3.注意

2、：设置主键是只有int,uniquidentfier(Guid)两种类型其他都是不合理的,要想主键是自动增长的在表，设计中把主键标示规范改为”是“一张表只能有一个标识列在SQL中Guid的调用方法是：newid(),在VS中是Guid ID=Guid.NewGuid();所以在表设计时把表的主键类型设为uniquidentfier，之后再插入数据时这样写：insert into Person4(ID,Name,Age)values(newid(),Tom,15) SQL语句的检索(一般检索select在此不写)create table T_Employee(FNumber varchar(20

3、) priMary key,FName varchar(20),FAge int,FSalary int)insert into T_Employee(Fnumber,Fname,FAge,FSalary)values(Dev001,Tom,22,8300)insert into T_Employee(Fnumber,FName,FAge,FSalary)values(Dev002,kom,24,3300)insert into T_Employee(Fnumber,Fname,FAge,FSalary)values(Dev003,jim,25,2000)insert into T_Emplo

4、yee(Fnumber,FName,FAge,FSalary)values(Dev004,mary,27,2300)insert into T_Employee(Fnumber,Fname,FAge,FSalary)values(Dev005,kuki,240,2500)insert into T_Employee(Fnumber,FName,FAge,FSalary)values(Dev006,lisa,25,1800)1.给个个列取别名：Select Fname as 姓名,Fage as 年龄,Fsalary as 月薪from T_employee where Fsalary50004

5、.聚合函数的用法：主要对表中的数据进行统计“最大值，最小值，平均值等”.聚合函数不能出现在where语句中select max(fsalary) from T_Employee select min(fsalary) from T_Employeeselect sum(fAge)from T_Employeeselect avg(fsalary) from T_Employee5.升序降序，order by +字段名+排序方式，Acs升序，desc降序，进行过滤式where在order by之前select * from T_Employee where FAge24 order by FAg

6、e Desc,Fsalary Asc6.模糊查询：多字符通配符为“%”。其中“k%”匹配以K开头的任意长度字符。select * from T_Employee where FNumber likeDev%表示已Dev开头的匹配7.空值处理我要查询表中Fname不为空的值我这这样写：select * from T_Employee where Fname null这样是错误的select * from T_Employee where Fname is not null SQL数据的分组Group by 关见词。注意Select中的东西或是聚合函数，或是Group By 中的，不会是其他的，w

7、here要放在Group by前面例1，select FAge as 年龄, count(*) as 人数from T_EmployeeGroup by FAge例2 select FAge as 年龄, max(Fsalary) as 人数from T_EmployeeGroup by FAge例3 select FAge as 年龄, count(*) as 人数from T_Employeewhere Fsalary2000Group by FAge select FAge as 年龄, count(*) as 人数from T_Employeewhere count(*)1Group

8、by FAge聚合函数不能出现在where语句中，若要用用Having,放在Group By之后having不能代替where只是用聚合函数时用having,having是对分组织后的数据进行过滤，where是对原始数据过滤select FAge as 年龄, count(*) as 人数from T_EmployeeGroup by FAgehaving count(*)1SQL限制检索如要查询表中工资由低到高检索第3名开始一共取2人的信息select top 2 * from T_Employeewhere FNumber not in(select top 3 FNumber from

9、T_Employee )order by Fsalary Desc SQL的过滤重复：过滤重复用到的关键字是distinct , distinct只能过滤整行重复的数据，而不是某一个数据相同修改表alter table T_Employee Add FsubCompany varchar(20)alter table T_Employee Add FDepartment varchar(20)update T_Employee set FsubCompany=beijing,FDepartment=developmentwhere FNumber=Dev001update T_Employee

10、 set FsubCompany=shenzhen,FDepartment=developmentwhere FNumber=Dev002update T_Employee set FsubCompany=beijing,FDepartment=developmentwhere FNumber=Dev003update T_Employee set FsubCompany=beijing,FDepartment=Infotechwhere FNumber=Dev004update T_Employee set FsubCompany=shenzhen,FDepartment=Infotechw

11、here FNumber=Dev005update T_Employee set FsubCompany=beijing,FDepartment=Salewhere FNumber=Dev006update T_Employee set FsubCompany=beijing,FDepartment=Salewhere FNumber=Dev007select FDepartment,FsubCompany from T_Employeeselect distinct FDepartment,FsubCompany from T_Employee SQl联合结果集关键词：union 作用是两张

12、表某些字段和在一起这些字段个数要相同，类型要相容。用union连接时会自动过滤掉两表中重复的字段这样会导致字段文件个数的丢失，为了解决用union all如果不想去掉重复行不能去all新建表create table T_TemEmployee(FldCardNumber varchar(20) Primary key ,Fname varchar(20),FAge int)insert into T_TemEmployee(FldCardNumber,Fname,FAge)values(162736187286,lili,33)insert into T_TemEmployee(FldCard

13、Number,Fname,FAge)values(234123232323,lisa,22)insert into T_TemEmployee(FldCardNumber,Fname,FAge)values(545465676768,xiaoli,26)insert into T_TemEmployee(FldCardNumber,Fname,FAge)values(434556576754,maomao,29)insert into T_TemEmployee(FldCardNumber,Fname,FAge)values(443244354323,wawa,21)select Fname,

14、FAge from T_TemEmployeeunion allselect Fname,FAge from T_Employee有时使用到报表select 正式员工最高年龄,max(FAge) from T_Employeeunion allselect 正式员工最小年龄,min(FAge) from T_Employeeunion allselect 临时工最大年龄,max(FAge) from T_Employeeunion allselect 临时工最小年龄,min(FAge) from T_EmployeeSQL中的函数Ceiling():舍入最大整数，3.33设为4 , -3.33

15、舍为-3；select ceiling(4.65)Floor():舍入最小整数。select floor(4.65)Len():计算字符串的长度。select len(abj)Lower(), upper():转小写，转大写。Select lower(ASddd)Ltrim():去掉字符串左边的空格。Select ltrim( ASddd)SubString(string,start_posistion,length)Select substring(ASddd,2,2)Getdate():取得当前时间。DateAdd(datepart,number,date) 计算增加以后的日期select DateAdd(month,1,getdate()DateDiff(datepart,stardate,enddate):计算两个日期间的差距。在T_Employee表中加入入职时间FInDate计算员工入职年限select Fname,FInDate,Datediff(year,Findate,getdate()from T_EmployeeDatepart(datepart,date):返回一个日期的特定部分计算每一年入职人数select datepart(year