搜索
1SQL数据类型：1.数据类型面加“n”的表示字符类型是字符串的，2.前有“var”的表示可变的如:varchar(10)和char(10)是不同的char(10)一定是10个字符如果不够的用空格填充，而varchar(10)不够就不够。3.bit:bool类型可选值,1,0SQL语句(增删改就不写了)1.创建表personcreatedatabasepersongocreatetableT_person(idintnotnullPrimarykey,namenvarchar(50)null,ageintnull)2.删除表droptableT_person3.注意：设置主键是只有int,uniquidentfier(Guid)两种类型其他都是不合理的,要想主键是自动增长的在表，设计中把主键标示规范改为”是“一张表只能有一个标识列在SQL中Guid的调用方法是：newid(),在VS中是GuidID=Guid.NewGuid();所以在表设计时把表的主键类型设为uniquidentfier，之后再插入数据时这样写：insertintoPerson4(ID,Name,Age)values(newid(),'Tom','15')SQL语句的检索(一般检索select在此不写)createtableT_Employee(FNumbervarchar(20)priMarykey,FNamevarchar(20),FAgeint,FSalaryint)insertintoT_Employee(Fnumber,Fname,FAge,FSalary)values('Dev001','Tom',22,8300)insertintoT_Employee(Fnumber,FName,FAge,FSalary)values('Dev002','kom',24,3300)insertintoT_Employee(Fnumber,Fname,FAge,FSalary)values('Dev003','jim',25,2000)insertintoT_Employee(Fnumber,FName,FAge,FSalary)values('Dev004','mary',27,2300)insertintoT_Employee(Fnumber,Fname,FAge,FSalary)values('Dev005','kuki',240,2500)insertintoT_Employee(Fnumber,FName,FAge,FSalary)values('Dev006','lisa',25,1800)1.给个个列取别名：SelectFnameas姓名,Fageas年龄,Fsalaryas月薪fromT_employeewhereFsalary500022.检索是时不一定要检索表的信息select1+1as壹加壹,getdate()as日期,newid()asGuid3.检索表中共有多少条数据selectcount(*)fromT_EmployeewhereFsalary50004.聚合函数的用法：主要对表中的数据进行统计“最大值，最小值，平均值等”.聚合函数不能出现在where语句中selectmax(fsalary)fromT_Employeeselectmin(fsalary)fromT_Employeeselectsum(fAge)fromT_Employeeselectavg(fsalary)fromT_Employee5.升序降序，orderby+字段名+排序方式，Acs升序，desc降序，进行过滤式where在orderby之前select*fromT_EmployeewhereFAge24orderbyFAgeDesc,FsalaryAsc36.模糊查询：多字符通配符为“%”。其中“k%”匹配以K开头的任意长度字符。select*fromT_EmployeewhereFNumberlike'Dev%'表示已Dev开头的匹配7.空值处理我要查询表中Fname不为空的值我这这样写：select*fromT_EmployeewhereFnamenull这样是错误的select*fromT_EmployeewhereFnameisnotnull4SQL数据的分组Groupby关见词。注意Select中的东西或是聚合函数，或是GroupBy中的，不会是其他的，where要放在Groupby前面例1，selectFAgeas年龄,count(*)as人数fromT_EmployeeGroupbyFAge例2selectFAgeas年龄,max(Fsalary)as人数fromT_EmployeeGroupbyFAge例3selectFAgeas年龄,count(*)as人数fromT_EmployeewhereFsalary2000GroupbyFAgeselectFAgeas年龄,count(*)as人数fromT_Employeewherecount(*)1GroupbyFAge聚合函数不能出现在where语句中，若要用用Having,放在GroupBy之后having不能代替where只是用聚合函数时用having,having是对分组织后的数据进行过滤，where是对原始数据过滤selectFAgeas年龄,count(*)as人数fromT_EmployeeGroupbyFAgehavingcount(*)15SQL限制检索如要查询表中工资由低到高检索第3名开始一共取2人的信息selecttop2*fromT_EmployeewhereFNumbernotin(selecttop3FNumberfromT_Employee)orderbyFsalaryDescSQL的过滤重复：过滤重复用到的关键字是distinct,distinct只能过滤整行重复的数据，而不是某一个数据相同修改表altertableT_EmployeeAddFsubCompanyvarchar(20)altertableT_EmployeeAddFDepartmentvarchar(20)updateT_EmployeesetFsubCompany='beijing',FDepartment='development'whereFNumber='Dev001'updateT_EmployeesetFsubCompany='shenzhen',FDepartment='development'whereFNumber='Dev002'updateT_EmployeesetFsubCompany='beijing',FDepartment='development'whereFNumber='Dev003'updateT_EmployeesetFsubCompany='beijing',FDepartment='Infotech'whereFNumber='Dev004'updateT_EmployeesetFsubCompany='shenzhen',FDepartment='Infotech'whereFNumber='Dev005'updateT_EmployeesetFsubCompany='beijing',FDepartment='Sale'whereFNumber='Dev006'updateT_EmployeesetFsubCompany='beijing',FDepartment='Sale'whereFNumber='Dev007'1.selectFDepartment,FsubCompanyfromT_Employee2.selectdistinctFDepartment,FsubCompanyfromT_Employee6SQl联合结果集关键词：union作用是两张表某些字段和在一起这些字段个数要相同，类型要相容。用union连接时会自动过滤掉两表中重复的字段这样会导致字段文件个数的丢失，为了解决用unionall如果不想去掉重复行不能去all新建表createtableT_TemEmployee(FldCardNumbervarchar(20)Primarykey,Fnamevarchar(20),FAgeint)insertintoT_TemEmployee(FldCardNumber,Fname,FAge)values('162736187286','lili',33)insertintoT_TemEmployee(FldCardNumber,Fname,FAge)values('234123232323','lisa',22)insertintoT_TemEmployee(FldCardNumber,Fname,FAge)values('545465676768','xiaoli',26)insertintoT_TemEmployee(FldCardNumber,Fname,FAge)values('434556576754','maomao',29)insertintoT_TemEmployee(FldCardNumber,Fname,FAge)values('443244354323','wawa',21)selectFname,FAgefromT_TemEmployeeunionallselectFname,FAgefromT_Employee有时使用到报表select'正式员工最高年龄',max(FAge)fromT_Employeeunionallselect'正式员工最小年龄',min(FAge)fromT_Employeeunionallselect'临时工最大年龄',max(FAge)fromT_Employeeunionallselect'临时工最小年龄',min(FAge)fromT_Employee7SQL中的函数1.Ceiling():舍入最大整数，3.33设为4,-3.33舍为-3；selectceiling(4.65)2.Floor():舍入最小整数。selectfloor(4.65)3.Len():计算字符串的长度。selectlen('abj')4.Lower(),upper():转小写，转大写。Selectlower('ASddd')5.Ltrim():去掉字符串左边的空格。Selectltrim('ASddd')6.SubString(string,start_posistion,length)Selectsubstring('ASddd',2,2)7.Getdate():取得当前时间。8.DateAdd(datepart,number,date)计算增加以后的日期selectDateAdd(month,1,getdate())9.DateDiff(datepart,stardate,enddate):计算两个日期间的差距。在T_Employee表中加入入职时间FInDate计算员工入职年限selectFname,FInDate,Datediff(year,Findate,getdate())fromT_Employee10.Datepart(datepart,date):返回一个日期的特定部分计算每一年入职人数selectdatepart(year,FIndate),count(*)//按每一年进行分组fromT_EmployeegroupbydatePart(year,FIndate)8SQL空值处理函数关键字：isnull,SQL语句中的Case(重点)注意若是逻辑判断case后什么都没有，selectFNameas姓名,(casewhenFsalary2000then'低收入'whenFsalary=2000andFsalary5000then'中等收入'else'高收入'end)as收入水平fromT_Employee9SQL表连接关键字Join--客户表create