经典T_SQL简单的语法知识

一、简单的语法知识 1 先举个例子Codeuse myshopdeclare ident intinsert into orders(customerid,orderdate)values(25,dateadd(day,-1,getdate()-当前时间减去1天，就是昨天select ident = identityinsert into details(orderid,productid,unitprice,quantity)values(ident,1,50,25)select the orderid of the inserted row is + convert(varchar(8),ident)把数据插入定单表,得到新插入的记录的id然后插入定单明细表2 定义变量给变量赋值declare myval int;set myval = 10;set myval1 = myval2*12;-下面语句是在查询中赋值变量select myval = max(id)+1 form mytable3 if和elseCodeuse xlandif not exists(select id from mytable) print 暂时没有数据else print cast(rowcount as varchar)+被检索到上面语句执行结果是 0行被检索到4 begin和end如果条件内部要跟代码块(多条语句)就要用到begin和end 5 case语句第一种case语句 Codeuse AdventureWorksselect SalesOrderID ,Position = Case SalesOrderID%10 when 1 then First when 2 then Second when 3 then third else some thing elseend from Sales.SalesOrderHeader第二种case语句Codeuse AdventureWorksselect SalesOrderID ,Position = Case when SalesOrderID%10 = 1 then First when SalesOrderID%10 = 2 then Second when SalesOrderID%10 = 3 then third else some thing elseend from Sales.SalesOrderHeader第一种CASE when子句中可以跟表达式第二种case when子句中表达式要返回true 或者false当前面的条件和后面的条件同时满足时按前面的条件处理,不需要break语句6 循环语句while 1=1begin waitfor time 01:00update mytable set num = 12 where id = 13end上面语句是在每天凌晨1点执行指定的操作while允许break和continue的存在Codeuse xlandif not exists(select id from mytable) print 没?有?检?索?到?数?据?else begin select id from mytable print cast(rowcount as varchar)+行?被?检?索?到? end上面语句执行结果是:2行被检索到二、简单的增 删 改 查 一：insert语句into 关键字是可选的values关键字前面的（）是可选的，这里是要接收数据的列values后面，有两种方式提供值1：显式的给出值 2：从select语句中导出值insert语句注意几点1：不要理标志列，系统会给你插入的2：给出实际的值，如果没有，那就null3：给出默认的值，default关键字，告诉数据库取默认值insert into . select什么时候会这么用,当成批的数据来自1：数据库中的另一个表2：同一台服务器完全不同的数据库3：另一个SQLSERVER的数据库4：来自同一个表中的数据说实在的2和3我从来没用过好，看一个例子declare mytable table( id int, sortid int);insert into mytable (id,sortid) select id,classid from product;select * from mytable;注意我定义了一个表的对象每一句之间是用分号隔开的(id,sortid) 是可以忽略的二：update语句看例子update e set e.flag = m from employee e join contact ce on e.id = ce.employeeid where = jo这里用到了join子句，当然是可以用的如果修改不止一列 只要加一个逗号set num = num * 1.2当然可以写表达式三：delete语句delete from actors from actors a left join film f on a.filmid = f.filmid where f.filmid is nullouter连接将在没有匹配的一端返回null，这里也就是film表没有匹配的行是null注意 is null 的写法四: select语句这里还是不说了,还是多说说子句吧五:where子句1：= = = != ! !其中 与 !=都是不相等的意思!与!5group by得到了一个记录的集合然后通过having子句，再在这个集合上做筛选三、复杂一点的查询 一:inner joininner join 是在做排除，如果任一行在两个表中不匹配，则注定将从最终的结果中排除掉例子1：select * from employee e inner join employee m on e.managerid = m.employeeid这是从一个表里查询了两次得到的一行记录将包括两个employee的信息 前面的是经理 后面的是打工的注意from哪个表 哪个表的信息就在前面其中e和m分别是表的别名，这里的别名和列的别名不同，不用写as例子2 select e.employeeid, ce.firstname, m.employeeid as managerid, cm.firstname as managerfirst from employee e inner join employee m on e.managerid = m.employeeidinner join contact ce on e.contactid = ce.contactidinner join contact cm on m.contactid = cm.contactid第一个联接是把一个表与他自身进行连接 这个叫自引用(注意表的别名)第二个连接得到经理的名字第三个连接得到雇员的名字 看似很复杂的连接 其实很简单最后说一点inner join 是默认的连接类型 inner 关键字是可选的 二:outer join先看例子 select e.employeeid, m.employeeid as managerid from employee e left outer join employee m on e.managerid = m.employeeid无论左侧表(e)中的行是否与右侧表中的行相匹配都要显示如果左侧表中的行在右侧表中找不到相匹配的数据, 那么右侧表的数据为nullright outer join 也类似 outer是可以忽略的三：full join 与 cross join这两个其实都不必多说full join 是 left join 和 right join 的结合full join将包含位于连接两侧的表的所有行不存在的行就用null补齐cross join 没有on操作符得到的是两测表中所有行的 笛卡儿积就是把两册的行排列组合一下一般不会存在null的行这是相当罕见的用法只有科学家或者来搞样本数据的才会用到这个用法四：unionunion更像是从一个查询直接向另一个查询进行的数据追加（差别还是有的）join更像是水平的合并数据（添加更多的列），union是垂直的合并数据（添加更多的行）先看例子：select col2 from table1 union all select col4 from table21：select 列表中字段的数量必须相同2：字段的数据类型必须隐士兼容3：返回的结果集的标头取自第一个查询4：默认返回方式是distinct，union alll返回全部的集合五：子查询返回单个值先看例子：declare hits intselect hits = min(hitnum) from articlesselect distinct A.title from articles A join users U on A.uid = U.id where A.hitnum = hits这是完全可行的，但是我们可以用另一种形式：select distinct A.title from articles A join users U on U.id = A.uid where A.hitnum = (select min(hitnum) from articles )这就是子查询六：子查询返回多个值接着看例子（子查询里返回多个值）use databaseselect A.title from articles A join users U on A.uid = U.id where A.id in (select id from articles where istop = true)再看一个例子（用子查询找出孤立的记录）select A.title from article A join users U on A.uid = U.id where A.id not in (select id from articles where istop = true)这个例子写的有点牵强但是这里注意 not in 子查询得到的字段 不能有null直 存在，如果有 那么整个句子将返回空细心的人大概看出来了，前面提到的两个子查询的例子几乎都可以用内联（join）的方式替换掉出于性能上的考虑，我们应该首先选择联结的解决方案 而不是子查询七：any some 和 allany与some功能上是相同的，推荐用someany与some一般都是和比较运算符一起使用的(= = ! 等 )some (1,2,3)意味着大于1 some就是大于最小的一个值=some和in 起的作用是一样的not in (a,b,c)展开为 a and b and csome (a,b,c)展开为 a or b or call (1,2,3)意味着大于3八：where子句中的相关子查询（外部查询和内部查询）先看个例子Codeuse adventureworksdrop table #MinOrderdatesselect CustomerID, min(OrderDate)as orderdate into #MinOrderdatesfrom Sales.SalesOrderHeadergroup by CustomerIDorder by CustomerIDselect o.CustomerID,o.SalesOrderID,o.OrderDatefrom Sales.SalesOrderHeader ojoin #MinOrderdates ton o.CustomerID = t.CustomerIDand o.OrderDate = t.orderdateorder by o.CustomerIDdrop table #MinOrderdates 每个顾客在系统中的第一张定单的orderid 和orderdate用到了临时表，执行了两次查询看下面的例子Codeselect o1.CustomerID,o1.SalesOrderID,o1.OrderDatefrom Sales.SalesOrderHeader o1where o1.OrderDate = (select min(o2.OrderDate) from Sales.SalesOrderHeader o2where o2.CustomerID = o1.CustomerID )order by CustomerID 执行了一次查询注意内部查询对外部查询有一个显示的引用 o2.CustomerID = o1.CustomerID 当然外部查询也可以引用内部查询中的列第二个例子 Codeselect c.LastName,(select min(OrderDate) from Sales.SalesOrderHeader owhere o.ContactID = c.ContactID) as orderdatefrom Person.Contact c 九：派生表先看例子： 订购过某种特定产品的顾客列表Codeuse AdventureWorks;select c.FirstName,c.LastNamefrom Person.Contact As cjoin Sales.SalesOrderHeader as sohon c.ContactID = soh.ContactIDjoin Sales.SalesOrderDetail as sodon soh.SalesOrderID = sod.SalesOrderIDjoin Production.Product as pon sod.ProductID =p.ProductIDwhere p.Name = Minipump查找顾客的名字以及首次订购的日期90行受影响现在我们想查找即订购过Minipump又订购过AWC Logo Cap的顾客如果最后是where p.Name = Minipump or p.Name = AWC Logo Cap2412行受影响这样做是错误的这样得到的结果是订购过Minipump和订购过AWC Logo Cap的顾客下面来看我们的解决方法 Codeuse AdventureWorksselect distinct c.FirstName,c.LastNamefrom Person.Contact As cjoin 四、约束 一：类型约束的类型一共分三种域约束：涉及一个或多个列，（限制某一列的数据大于0）实体约束：相同的值不能存在于其他的行中引用完整性约束：一个表中的一个列与某个表中的另一个列的值匹配二：命名约束是可以命名的 一般这样命名：pk_customer_*pk代表主键 customer代表主键所在的表 后面是你自己定义的（要确保整个名称的唯一性）三：主键约束主键约束：一般就是id, 一个表中最多有一个主键例子1use accountingcreate table employee(id int identity not null,firstname varchar(20) not null)例子2use accountingalter table employeeadd constraint pk_employeeidprimary key (id)四：外键约束外键约束用在确保数据完整性和两个表之间的关系上先看例子create table orders(id int identity not null primary key,customerid int not null foreign key references customer(id),orderdate smalldatetime not null,eid int not null)注意：这个表的外键必须是另一个表的主键！在现有表上添加外键alter table ordersadd constraint fk_employee_creator_orderforeign key (eid) references employee(employeeid)使用表自引用表内至少要有一行数据才可以这么做alter table employeeadd constraint fk_employee_has_managerforeign key (managerid) references employee(employeeid)创建表的时候做表自引用 就可以忽略 foreign key 语句表自引用的外键列 必须允许为null 要不是不允许插入的（避免对最初行的需要）一个表与另一个表有约束，这个表是不能被删除的 级联操作先看例子create table orderdetails( orderid int not null , id int not null , description varchar(123) not null, -设置主键 constraint pkOrderdetails primary key (orderid,id), -设置外键，级联操作 constraint fkOrderContainsDetails foreign key (orderid) references orders(orderid) on update no action on delete cacade)on delete cacade 当删除父记录时 同时删除该记录也就是当删除orders表中的一条记录，与之相关的orderdetails表中的记录也将被删除级联的深度是没有限制的,但是每个外键都必须设置on delete cacade no action是可选的五：unique约束unique约束与主键约束类似，同样也是要求指定的列有唯一的值但是一个表中可以有多个unique约束的列，同时这个列允许存在null值。（最多有一个null值）看例子：create table shippers( id int indentity not null primery key,zip varchar(10) not null ,phoneno varchar(14) not null unique)例子二：alter table employeeadd constraint ak_employeeSSNunique(ssn)六：check约束check不局限于一个特定的列，可以约束一个列，也可以通过某个列来约束另一个列定义check约束使用的规则与where子句中的基本一样下面我写几个between 1 and 12like 0-90-90-9-0-90-90-90-90-90-90-9in (ups,fed ex,usps)price =0shipdate = orderdate看例子：alter table customersadd constraint cn_customerDateinsystemcheck(dateinsystem 0;sp_bindrule SalaryRule , Employee.Salary第一句定义了一个规则叫SalaryRule进行比较的事物是一个变量这个变量的值是所检查的列的值第二句把规则绑定到某个表的一个列上规则和ckeck约束很相似，但是规则只作用在一个列上一个规则可以绑定在多个列上，但是它不会意识到其他列的存在check可以定义column1=column2取消规则exec sp_unbindrule Employee.Salary删除规则Drop rule SalaryRule十：默认值默认值与default约束类似（有区别的，但是我说不清楚）先看例子：create default salarydefaultas 0;exec sp_binddefault salarydefault , employee.salary;取消默认值：exec sp_unbinddefault employee.salary删除默认值：drop default salarydefault五、create drop与alter 一：dropuse accounting drop table customers删除表use masterdrop database accounting删除数据库这里删除数据库 use 的是master一定要关闭外部连接 才能删除数据库二：create先看例子create database accounting on (name = accounting, filename = c:databaseaccountingdata.mdf, size = 10, maxsize = 50, filegrowth = 5)log on (name = accountinglog, filename = c:databaseaccountinglog.ldf, size = 5MB, maxsize = 25MB, filegrowth = 5MB )gocreate database完整的语法有很多子句的我上面写的那个例子只涉及到几个常用的下面解释一下:on指存储数据文件的位置，可以把数据存储到多个文件上（这只应用在超大型数据库上）name是文件的逻辑名称，调整数据库文件大小时要用到这个名称filename可选的参数，如果不使用这个参数，那么这个文件就存在MSSQL.1MSSQLDATA目录下 如果是数据文件那么名称与数据库名称相同，扩展名是.mdf如果是日志文件那么名称是数据库名后跟一个_log，扩展名是.ldfsize数据库的大小，可以带单位也可以直接写整数，不能写小数maxsize数据库能增大到的最大大小，如果不设置这个参数，那么就是磁盘的大小，建议设置一个数字比磁盘小一点filegrowth数据库每次增长将增长多少，可以是整数，百分数log on 是设置日志的，默认设置日志文件是数据文件大小的25%日志文件最好不要和数据文件在同一个磁盘上，避免竞争，安全保障Create table先看例子use accountingcreate table customers(id int idetity not null primary key,username varchar(30) not null)就不多说了说一下命名规范1：保持名称简短，长度要足以使名称具有描述性2：当基于其他表来构造表时，新表的名称中其包含父表的名称3：当名称中有两个单词时，不要使用任何分隔符，每个单词首字母大写三：ALTERalter database accounting modify file (name = accounting , size = 100MB)上面是把数据库文件扩展到100MBalter table customers add fedid varchar(9) nullalter table customers add contact varchar(25) null,lastraisedate datetime not null default 2005-12-4下面看个关于主键的alter语句use accountingalter table employee add constraint pk_employeeidprimary key (employeeid)要向表中添加约束，添加约束的名字约束的类型将约束应用到的列六、视图 一:简单的视图use xlandgocreate view my_vwas select a.id, a.title,u.username from mytable a join user u on u.id = a.uid执行这个视图use xlandgoselect * from my_vw结果跟直接运行select a.id, a.title,u.username from mytable a join user u on u.id = a.uid是一样的二:删除和修改视图alter view yourviewname as.drop view yourviewname as.三:加密视图alter view yourviewname with encryption as.加密了之后连你自己也看不到原代码了七、存储过程和用户自定义函数 一:存储过程的简单创建,修改与删除1.创建简单的存储过程use AdventureWorksgocreate proc spEmployeeasselect * from HumanResources.Employee执行上面的代码就创建了一个存储过程如果想运行这个存储过程可以直接执行exec spEmployee这个语句2.更改存储过程ALTER proc dbo.spEmployeeasselect top 13 * from HumanResources.Employee3.删除存储过程drop p