--查询
select DB_ID('B2C')
--检查数据库是否存在
if DB_ID('B2C') is not null
--使用数据库
use B2C
--单引号表示字符串，双引号则不是 U 表示用户表
select OBJECT_ID('UserName','U')
--主键约束Primary key
--唯一约束Unique
--外键约束foreign key () references
--检查约束Check
--非空约束
--Select 语句的元素
--From
--Where
--Group by
--Having
--Select
--Order by
--表达式
select orderdate,year(orderdate) as 订单年份
from Orders;
--选择
select orderdate,year(orderdate) as 订单年份
from Orders
where year(orderdate)=2006
--查询同一年出生的员工人数
select YEAR(birthdate),COUNT(*)
from Employee
group by YEAR(birthdate)
--百分比
select top (20) percent lastname,firstname
from hr.employees
--开窗函数 over
select lastname,firstname,count(*) over ()
from hr.employees
--带有总价的订单
--partition by 相当于做一个相对的选择，选出等于的值
select lastname,firstname,val
,sum(val) over( partition by custid) as 顾客总价
,sum(val) over() as 总价
from Sales.OrderValues
--排名函数
--row_number 行号 必须与 over 配合
--rank 排名
--dense_rank 密集排名
--ntile 分组
select row_number() over( order by lastname ),lastname,firstname
rank() over( order by country) as 排名,
dense_rank() over( order by country) as 密集排名,
ntile(5) over( order by country) as 分组,
from hr.employees
--排名结合部分
select row_number() over( order by country)
row_number() over( partition by country order by country),
country,lastname,firstname
from hr.employees
--between and
select *
from OrderValues
where val between 1000 and 200
--in 枚举值
select *
from OrderValues
where custid in (1,2,8)
--like 字符串匹配 % 任意多个任意字符 _任意一个任意字符
--简单表达式
select lastname,region,
case region -switch
when 'WA' then '华盛顿' --case
else '其他特区'
end
from employees
--搜索表达式
select lastname,region,
case
when region='WA' then '华盛顿特区'
when region is null then '位置地区'
else '其它地区'
end
from employees;
--定义变量
declare @s char(10),
--赋值之后,实际上已经填满了10个字符
set @s='hello'
print @s
set @s = @s+'world'
print @s
--注意：在SQL 中，下标从1开始
select productname,substring(productname,9,10)
from production.Products
--left 表示左边
select productname, left(productname,8)
from production.Products
--right 表示取右边的部分
select productname,right(productname,8)
from production.Products
--模版在前，源字符串在后，空格出现在名字中的位置，从1开始
select productname,charindex('',productname)
from production.Products
--将产品名称中的product 替换为产品名
select productname,replace(productname,'Product','产品名')
from production.Products
--stuff 替换
select productname,stuff(productname,8,1,' : ')
from production.Products
--转换大写 和 小写
select productname,upper(productname),lower(productname)
from production.Products
--去空格
select datalength(rtrim(string))
select ltrim(rtrim(string))
--字符串方式表示日期
select orderid,orderdate
from Sales.orders
where orderdate > '20080301'
--cast 转换 datetime 这个和上面一个是等价的
select orderid,orderdate
from Sales.orders
where orderdate > cast ('20080301' as datetime)
-- datepart 取日期中的一部分
select getdate()
--年，月，日，小时，分钟，秒
select datepart(year,getdate()),
datepart(month,getdate()),
datepart(day,getdate()),
datepart(hour,getdate()),
datepart(minute,getdate()),
datepart(second,getdate()
--特殊
select datepart(dayofyear,getdate()),-- 从年初到现在的天数
datepart(weekday,getdate()),--星期几，从星期天开始为1
datepart(week,getdate()) --今年的第几周
select year(getdate()),month(getdate()),day(getdate()
--日期加和减 日期加法 单位
select getdate(),dateadd(month,2,getdate()) -- 加两个月
dateadd(year,3,getdate()),
dateadd(minute,30,getdate())
--日期减法 单位
select datediff(year,'19491001',getdate())
--计算今年多少岁
select datediff(year,'19800101',getdate())
--练习1
select *
from Sales.Orders
where year(orderdate)=2007 and month(orderdate)=7
select *
from Sales.Orders
where datepart(year,orderdate)=2007 and
datepart(month,orderdate)=6
--还可以 '200706'
--将日期转换成字符串
select *
from Sales.Orders
where left(convert(nvarchar,(orderdate),112),6)='200706'
select getdate(),convert(nvarchar,getdate(),112),
left(convert(nvarchar,getdate(),112),6)
select convert(int,'99')+100
select cast('99' as int)+100
select orderid,sum(unitprice * qty)
from Sales.Orderdatails
group by orderid
having sum(unitprice *qty) >10000
order by sum(unitprice * qty) desc
--3-1交叉连接和内连接
--连接查询 cross 翻译十字 所以的都做了一次匹配
select empid,shipperid
from Hr.employees cross join Sales.Shippers
--内连接 必须符合特定条件的组合
select c.categoryid,productid,p.productname,c.categoryname
from production.Categories c inner join production.products p
on c.categoryid=p.categoryid
--查询每个顾客的名称，已经下过多少张订单
select distinct c.custid,contactname,orderid,
count(*) over(partition by o.custid)
from Sales.Customers c inner join Sales.Orders o
on c.custid=o.custid
--插入10 条记录
insert into digits (digit)
values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
--生成数字
select d1.digit,d2.digit, d3.digit* 10 + d2.digit*10 +d1.digit
from digits di cross join digits d2
cross join digits d3
--实现1-1000
select row_number() over(order by d1.digit) as digit
from digits d1 cross join digits d2
cross join digits d3
order by digit
--查询每张订单多少钱
select distinct o.orderid,orderdate,
sum(qty * unitprice) over(partition by o.orderid)
from Sales.Orders o inner join Sales.OrderDatails d
on o.orderid=d.orderid
--左外连接
select count(*)
from Sales.Customers c left outer join Sales.Order o
on c.sutid =o.custid
--右外连接
select count(*)
from Sales.Customers c right outer join Sales.Order o
on c.sutid =o.custid
--组函数不包含 Null
select distinct c.contactname,
count(o.orderid) over(partition by c.custid) ordercount
from Sales.Customers c left outer join Sales.Orders o
on c.custid=o.custid
order by ordercount
--查询一下所有的雇员都处理过订单
select e.empid,count(o.orderid),lastname
from hr.employee e left outer join sales.orders o
on e.empid=o.empid
group by e.empid,lastname
having count(o.orderid)=0
--将员工表和订单表做一个外连接
select e.empid,e.lastname
from left outer join sales.orders o
where o.orderis is null
--查询有顾客但是没有供应商的国家
select distinct c.country as 客户国家,s.country as 供应商国家
from Sales.Customers c left outer join Production.Suppliers s
on c.country=s.country
where c.country is null
--全外连接，查询所有的国家，有无供应商，有无顾客
select distinct c.country as 客户国家,s.country as 供应商国家
from Sales.Customers c full outer join Production.Suppliers s
on c.country=s.country
--2008年中 ，每天有多少订单
select orderdate,count(*)
from sales.orders
where orderdate between '20080101' and '20081231'
group by orderdate
select orderdate,count(*) over (partition order by orderdate)
from sales.orders
where orderdate between '20080101' and '20081231'
--创建一个表，向表里写入400条数据
create table nums
(
n int
)
declare @i int
set @i=1
while @i<400
begin
set @i=@i+1
insert into values(@i)
end
--制作一个连续的日期
select dateadd(day,n,'20071231')
from nums
--与订单表做一个连接
select dateadd(day,nums.n,'20071231') noorderdate
from nums left outer join salas.orders
on dateadd(day,nums.n,'20071231')=sales.orders.orderdate
where sales.orders.orderid is null
order by noorderdate
--子查询
select lastname
where birthdate=(select max(birthdate) from hr.employees)
--独立子查询 没有用到外部的子查询叫独立子查询
--相关子查询 解释: 子查询查询出现了子查询以外的查询叫做相关子查询
select custid,contactname,
(
select count(*)
from Sales.Orders
where Sales.Orders.custid=Sales.Customerscustid
) as ordercount
from Sales.Customers
--还有一些复杂的情况考虑
--多值子查询 这里用 exists 也可 是否存在
select distinct country
from Sales.Customers
where country not in
(
select distinct country
from Production.Suppliers
)
--使用相关子查询
select distinct country
from Sales.Customers c
where
not exists
(
select country from Production.Suppliers s
where s.country=c.country
)
--高级子查询
--查询一个数字的上一个数和下个数 使用相关子查询
select orderid,
(
select max(orderid)
from Sales.orders innerT
where innerT.orderid < outerT.orderid
) as prevOrder,
(
select min(orderid)
from Sales.orders innerT
where innerT.orderid> outerT.orderid
) as nextOrder
from Sales.Order outerT
select e.empid,fistname,lastname
from Sales.Orders o right outer join Hr.Employees e
on o.empid=e.empid and o.orderdate>='20080501'
where o.orderid is null
--客户表中出现过,雇员表中没有出现过的国家
select distinct country
from Sales.Customers
where country not in (
select country
from hr.employees
)
--考虑用连接
select c.country
from Sales.Cutomers c left outer join Hr.Employees e
on c.country =e.country
where c.country is null
--派生表 其实就是特殊的子查询
--用在from 之后的子查询
--特殊之处
-- 1.必须起别名,派生表必须起别名, 表达式必须起别名
select *
from (
select custid,count(*) over(partition by country) as num
from Sales.Customers
where country='USA'
) t
--或者在外面起别名 t 的后面(custid,num)
--表表达式
--公用表表达式CTE 跟派生表很相似
--派生表实现
select *
from
(
select companyname,country
from Sales.Customers
where country ='USA'
) t
--使用CTE
--先定义子查询,命名
with usa_customers
as
(
select companyname,country
from Sales.Customers
where country='USA'
)
select * from usa_customers
with usa_customers
as
(
select companyname as 公司名,country 国家
from Sales.Customers
where country='USA'
)
select * from usa_customers
--在CTE 使用参数
declare @country nvarchar(30);
set @country='USA';
with usa_customers(公司名,国家)
as
(
select companyname,country
from Sales.Customers
where country=@country
)
select * from usa_customers;
select year(orderdate),custid,count(*) orderdate
from Sales.Orders
group by year(orderdate),custid
having count(*)>10
select year(orderdate),custid
from Sales.Orders
select orderyear,custid,count(*)
from
(
select year(orderdate) orderyear,custid
from Sales.Orders
) as t1
group by orderyear,custid
select orderyear,custid,orderdate
from
(
select orderyear,custid,count(*) as ordercount
from
(
select year(orderdate) orderyear,custid
from Sales.Orders
) as t1
group by orderyear,custid
) as t2
where ordercount>10
--使用CTE
with withyearorders (orderyear,custid)
as
(
select year(orderdate),custid
from Sales.Orders
),
yearorders
as
(
select orderyear,custid,count(*) as ordercount
from withyearorders
group by orderyear,custid
),
yearOrder10
as
(
select orderyear,custid,ordercount
from yearoders
where ordercount>10
)
select * from yearOrder10
--派生表查询
select t1.orderyear,t2.orderyear,t1.custcount-t2.custcount
from
)
select year(orderdate) as orderyear,count(distinct custid) countcount
from Sales.Orders
group by year(orderdate)
) as t1
left outher join
)
select year(orderdate) as orderyear,count(distinct custid) countcount
from Sales.Orders
group by year(orderdate)
) as t2
on t1.orderyear=t2.orderyear+1
--多个CTE 复用
with yearcust
as
(
select year(orderdate) as orderyear,count(distinct custid) countcount
from Sales.Orders
group by year(orderdate)
)
select curr.orderyear,curr.custcount,prev.oderyear,prev.custcount
curr.custcount-prev.custcount
from yearcust as curr left outer join yearcust prev
on curr.orderyear=prev.orderyear+1
--自连接,连接两遍的表其实是同一张表，一定要起别名
select e.empid,e.lastname,e.mgrid,e.empid,m.lastname
from Hr.Employees e inner join Hr.Employees m
on e.mgrid=e.empid
--查询员工的下属 递归
with emplist
as
(
-- 起点 ,仅仅执行一次
select empid,lastname,mgrid
from Hr.Employees
where mgrid=2
union all --合并
--可能多次执行,直到查询结果为空
select e.empid,e.lastname,e.mgrid
from Hr.Employees e inner join emplist m
on e.mgrid=m.empid
)
select * from emplist
option (maxrecursion 6); --限制递归次数
--View 视图
--把查询保存在数据库中，起名字，视图
create view usacustomers
as
select companyname,country
from Sales.Customers
where country='USA'
go --前面是单独的,后面再执行
create view myOrderValues
as
select o.oderid,custid,empid,shipperid,orderdate,
sum(d.qty * d.unitprice) as val
from Sales.Orders o inner join Sales.OrderDatails d
on o.orderid=d.orderid
group by o.orderid,custid,empid,shipperid,orderdate
--删除视图
drop view dbo.usacustomers
create view CustOrders
as
select o.custid,datediff(month,datediff(month,0,o.orderdate),0) as ordermonth,
sum(od.qty) as qty
from Sales.Orders as o inner join
Sales.OrderDetails as od on od.orderid=o.orderid
group by o.custid,datediff(month,datediff(month,0,o.orderdate),0)
select empid,max(orderdate) as maxorderdate
from Sales.Orders
group by empid
select o.empid,o.orderdate,o.orderid,o.custid
from
(
select empid,max(orderdate) as maxorderdate
from Sales.Orders
group by empid
) t
inner join Sales.Order o
on o.orderdate=t.maxorderdate and o.empid=t.empid
with empmaxorder
as
(
select empid,max(orderdate) as maxorderdate
from Sales.Orders
group by empid
)
select o.empid,o.orderdate,o.orderid,o.custid
from Sales.Orders o inner join empmaxorder em
on o.empid=em.empid and o.oderdate=em.maxorderdate;
select orderid,orderdate,custid,empid
row_number() over( order by orderdate,orderid) as rownumber
from Sales.Orders
with numorders
as
(
select orderid,orderdate,custid,empid
row_number() over(order by orderdate,orderid) as rownum
from Sales.Orders
)
select orderid,orderdate,custid,empid,rownum
from numorders
where rownum between 11 and 20
--递归CTE
with mangers
as
(
--入口点
select empid,mgrid,firstname,lastname
from Hr.Employees
where empid=9
union all
--递归查询
select e.empid,e.mgrid,e.firsname,lastname
from mangers m inner join Hr.Employees e
on m.mgrid = e.empid
)
select empid,mgrid,firsname,lastname
from mangers
go
create view Sales.VEmpOrders
as
select empidi,year(orderdate) as orderyear,sum(qty) as qty
from Sales.Orders o inner join Sales.orderdateils od
on o.orderid=od.orderid
group by empid,year(orderdate)
--连续聚合
select empid,orderyear,qty
(
select sum(qty)
from Sales.VempOrders innerT
where innerT.empid=outerT.empid
and innerT.orderyear<=outerT.orderyar
) as runqty
from Sales.VempOrders outerT
order by empid,orderyear
with vorders
as
(
select empidi,year(orderdate) as orderyear,sum(qty) as qty
from Sales.Orders o inner join Sales.orderdateils od
on o.orderid=od.orderid
group by empid,year(orderdate)
)
select empid,orderyear,qty
(
select sum(qty)
from vorders innerT
where innerT.empid=outerT.empid
and innerT.orderyear<=outerT.orderyar
) as runqty
from vorders outerT
order by empid,orderyear
go
--定义一个表函数
create function production.topProducts
(
@supid int,
@num int
)
returns table
as
return
select top(@num) *
from Production.Products
where supplierid=@supid
order by unitprice desc
--调用 表函数
select *
from production.topProducts(5,2)
--交叉连接
select s.supperlierid,companyname,
productid,productname,unitprice
from Production.Suppliers s
cross apply production.topProducts(s.supplierid,2)
--透视,逆透视及分组集合 表的转置
--分组查询
select empid ,custid,sum(qty) sumqty
from dbo.orders
group by empid,custid
--对雇员进行分组
select empid,sum(qty)
from dbo.orders
group by empid
--增加顾客的列 1 号顾客 使用相关子查询
select empid,
(
select sum(qty)
from dbo.orders innerT
where custid=1 and innerT empid=outerT empid
) as A,
(
select sum(qty)
from dbo.orders innerT
where custid=2 and innerT empid=outerT empid
) as B,
(
select sum(qty)
from dbo.orders innerT
where custid=3 and innerT empid=outerT.empid
) as C
from dbo.orders outerT
group by empid
select empid,
sum(case when custid=1 then qty end) as A,
sum(case when custid=2 then qty end) as B,
sum(case when custid=3 then qty end) as C,
sum(qty)
from dbo.orders
group by empid
使用 pivot 语法实现透视
select empid,count(*)
count(case when year(orderdate)=2006 then orderdate end)
as nums2006,
count(case when year(orderdate)=2007 then orderdate end)
as nums2007,
(
select count(*)
from Sales.Orders innerT
where innerT.empid=outerT.empid and year(orderdate)=2008
),
sum(qty)
from sales.orders outerT
group by empid
select empid,[1],[2],[3]
from (
--仅仅返回透视中用到的列
select empid,qty,custid
from dbo.orders
) t --分组是隐含的,对表中除掉聚合和条件的列进行聚合
pivot(
sum(qty) --聚合函数
from custid in([1],[2],[3]) --准备做列名
) as p;
select empid,[2006],[2007],[2008]
from
(
select empid,year(orderdae) orderyear,orderid
from Sales.Orders
) as t
piovt
(
count(orderid)
from orderyear in([2006],[2007],[2008])
) as p
order by empid;
---计算每个客户每年的支出金额
select custid, year(orderdate) as orderyear,sum(od.qty *od.unitprice) totalprice
from Salas.Orders o inner join Sales.ORderDatails od
group by custid,year(orderdate)
select custid,[2006],[2007],[2008]
from (
select custid, year(orderdate) as orderyear,sum(od.qty *od.unitprice) totalprice
from Salas.Orders o inner join Sales.ORderDatails od
group by custid,year(orderdate)
) t
pivot(
sum(totalprice)
for orderyear in([2006],[2007],[2008])
) p;
if DB_ID('school') is not null
begin
use master;
drop database school;
end
create database school;
use school;
--schema , 默认 dbo
create schema student;
go
if object_id('student.Students','U') is not null
drop table student.Students;
--考虑定义一张表
create table student.Students
(
studid int identity(1,1) not null,-- 非空约束
studentname nvarchar(20) not null,
classid int,
--外键约束
constraint fk_classid foreign key(classId)
references classs(id),
--性别
gender nvarchar(1),
--检查约束
constraint ck_gender check(gender in('男','女')),
--唯一约束
constraint uq_name unique(studentname),
--主键约束，主键中能存在一组，但是唯一约束可以由多组
constraint pk_id primary key(studid)
);
go
--插入数据 全部字段都赋值
insert into student.Students values(1,'tom');
--通常使用指定字段名的方式
insert into student.Students(studentname,studid)
values('alice',2)
--另外一种写法
insert student.Students(studid,studentname)
select '','' union,
select '','' union,
select '','' union,
select '','' union
--联合这两个表
select bookName from myrank
union --去重复
select bookName from mybook;
--联合这两个表 显示重复数据
select bookName from myrank
union all --不去重复
select bookName from mybook;
insert into select语句则是从一个表中复制数据然后插入到另一个已存在的表中，目标表中原有的行不会受到任何影响。
insert into myrank(userId) select userID from mybook
select * from student.Students;
--事务和并发
--隐式事务
--显示事务，明确指出事务的起止边界
begin transaction
delete from Sales.Shippers where shipperid=4
delete from Sales.Shippers where shipperid=5
--回滚
rollback
--提交
commit
--事务必须有四个属性
--原子性(Atomicity),一致性(Consistency),隔离性(Isolation)
--持久性(Durabillity) 这四个属性的首字母可以缩写成ACID
--设置隔离级别可以读取未提交的数据
set transaction isolation level read uncommitted
--其实就是加锁，加上共享锁
--普通情况下，查询的时候，系统加上共享锁，查询结束，
--锁撤销
--我们现在，希望在查询结束之后，共享锁依然存在，保证数据不被修改
set transaction isolation level repeatable read;
--可编程对象
--定义变量
declare @i int
set @i=10
print @i;
declare @i varchar(20)
set @i='Hello,world';
print @i;
declare @i nchar(20) --固定长度
set @i='Hello'; --已经占用了所有的空间
set @i=@2+',world';
print @i;
--还可以使用select 对变量赋值
select @i=99;
print @i;
--当前时间
declare @now datetime
set @now=getdate();
print @now;
--查询顾客的数量，保存到变量中使用
declare @customerCount int;
select @customerCount= count(*) --赋值语句
from Sales.Customers;
print @customerCount;
--选择结构
--条件
--取得当前时间的分钟部分
declare @mi int;
set @mi=datepart(minute,getdate());
if @mi>10
begin
print '下课'
end
else
begin
print '继续学习'
end
break
--游标
select companyname
from sales.customers
--简单变量，标量
declare @name nvarchar(100)
select @name=companyname
from sales.customers;
print @name;
--使用游标 是把数据逐条查询出来
--1. 声明游标，基于查询
declare c cursor
for
select custid, companyname
from sales.customer
declare @name nvarchar(100);
declare @id int;
--2.在使用之前，必须打开游标
open c;
--3. 从游标中读取查询数据，每次可以读取一条记录
fetch next from c into @id,@name
--4. 注意fetch 并不一定获取实际的数据
while @@fetch_status=0 --成功获取了数据
begin
print @name;
-- 燃火，试探获取下一条数据
fetch next from c into @id,@name;
end
--5. 游标使用完毕，一定要关闭
close c;
--6.释放游标
deallocate c;
--局部临时表 名字前面有一个#表示局部临时表
create table #temptable
(
num int
)
--使用与表相同
insert into #temptable (num) values(1),(1),(2),(3),(5);
--全局临时表
--使用方式名字前面有两个##
create table ##globaltable
(
name nvarchar(20)
)
--表变量
declare @table table
(
num int
)
insert into @table (num) values(1),(1),(2),(3),(5)
select * from @table
--静态SQL
--动态SQL
declare @sql nvarchar(200)
set @sql='select companyname from seles.customers'
print @sql;
--将一个字符串看成一个SQL 语句动态执行
exec(@sql);
--sql 注入攻击
declare @sql nvarchar(200);
set @sql='select custid,companyname from seles.customers where
custid=@id';
--用户输入查询的编号
declare @input int;
set @input=23;
--原则，用户的输入是邪恶的
--set @input='abc';
--set @input='0 or 1=1';
--set @input='0; select * from sales.customers';
print @sql;
--将一个字符串看成一个SQL 语句动态执行
exec sp_executesql
@stmt=@sql; --表示要执行的SQL语句
@params= N'@id as int'; --声明命令中需要的参数
@id=@input; --后面依次提供参数
go
--调整价格写成一段程序，保存到数据库中
--存储过程
create procedure ModifyPrice
(
@num money
)
as
update production products
set unitprice=unitprice=@num;
--存储过程保存在数据库中
exec dbo.ModifyPrice @num=10;
--参数问题
--1.传入参数
-- 传统的参数，默认的参数就是传入参数
--2.传出参数
-- out,ref 参数
--查询顾客的数量
--与方法不同，存储过程没有通常意义上的返回类型和返回值
create procedure GetCustomerCount
(
@count int output --参数有output 传出参数
)
as
declare @num int;
select @num=count(*) from sales.customers;
print @num;
--使用带有传出参数的存储过程
--必然使用变量来保存传出的结果
declare @mycount int;
--前面是参数定义的传出参数名称
--后面是 我们定义的用来保存传出结果的变量
exec dbo.GetCustomerCount @count=@mycount output;
print @mycount
create procedure createuser
(
@username nvarchar(100)
)
as
declare @namelen int;
set @namelen=len(@username)
if @namelen>=5
return 0
else
return 1;
--调用 获取返回结果是否成功
declare @returnValue int;
exec @returnValue=createuser @username='23423432'
print @returnValue
--创建函数
create function getminute
(
@datevalue datetime --传入参数
)
--函数可以直接返回一个值
returns int
as
--函数体
begin
declare @mi int
set @mi=datepart(minute,@datevalue);
return @mi;
end
--调用
select dbo.getminute(getdate())
--触发器
--创建一个日志表，记录对货运的操作
create table shipper_log
(
logid int identity(1,1) primary key,
opdate datetime default getdate(),
opuse nvarchar(20),
op nvarchar(20),
shipname nvarchar(50),
shipphone nvarchar(20)
)
--创建一个审核货运公司的触发器
create trigger tg_ship
--触发器没有参数，由于通过数据库系统调用
on Sales.Shippers after insert,delete,update
as
insert into shipper_log(opuse,op,shipname,shipphone)
select user_name(),'插入',companyname,phone
from inserted;
--标识问题
--获得系统提供的标识值,获取整个系统系统范围内
select @@identity
--应该使用这个，获取当前作用域中最新生成的标识值
select scope_identity();
select datepart(day,'20161228','20140624')
--多表查询
with
a as (select '60000' cw1, 200 cw2, '2010-12-31' cw3),
b as (select '60000' CW1, '银行' CW2),
c as (select '银行' cw1, 121 cw2, '2010-12-31' CW3)
select a.cw1 '公司',a.cw2 '财务',b.CW2 '行业',c.cw2 '平均值'
from a
inner join b on a.cw1=b.CW1
inner join c on b.CW2=c.cw1
where a.cw2>c.cw2
--参照更新格式
update a
set a.字段=b.字段
from a join b on a.xx=b.xx
where
--查询表中重复的数据
select 字段,sun(1) as c
from 表
group by 字段
having sum(1)>1
--高效分页
declare @pagesize int,@pageindex int
set @pagesize=20;
set @pageindex=1;
with temp
as
(
select ROW_NUMBER() over(order by id) [row_number],* from dbo.t_user
)
select * from temp where temp.row_number between @pagesize*(@pageindex-1)+1 and @pagesize*@pageindex
--将mybook表的数据复制到mybookcopy表中，mybookcopy表无需我们创建，执行这条语句后会自动创建mybookcopy表
select * into mybookcopy from mybook;
--复制多个表的列到新表中
select mybook.bookName,myuser.userName into newtable from mybook join myuser on mybook.userID=myuser.userId
select * from A a,B b where a.id_a=b.id_b
DECLARE @pageIndex INT,@pageSize INT
SET @pageIndex=1
SET @pageSize=10
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY [TelePhone]) AS [ROW_NUMBER],[TelePhone] FROM TB_NUSERS
) AS T WHERE [ROW_NUMBER] BETWEEN @pageSize*(@pageIndex-1)+1 AND @pageIndex*@pageSize
--删除重复记录
delete [dbo].[T_moblie_info_bak1] where UniqueId not in(
select max(UniqueId) from [dbo].[T_moblie_info_bak1] group by moblie)
--删除相同数据
delete [dbo].[T_moblie_info_bak] where UniqueId not in(
select UniqueId from [dbo].[T_moblie_info_bak] m where UniqueId in(
select top 1 UniqueId from [dbo].[T_moblie_info_bak] t where m.moblie=t.moblie))
