Sql2008--操作语言

--数据库操作语言
--INSERT 插入数据
use AdventureWorks;
go --插入还有函数的数据
insert into dbo.Departments
values(),(),();
--修改数据  UPDATE
--删除数据 DELETE
--查询语句 SELECT
USE AdventureWorks;
GO
SELECT * FROM Production.ProductPhoto P
WHERE P.LargePhotoFileName LIKE '%greena_' escape 'a'; --ESCAPE 'escape_character' 允许在字符串中搜索通配符，而不是将其作为通配符使用

USE AdventureWorks;
GO
select AVG(OrderQty) as 'Average Quantity',
 NonDiscountSales = (OrderQty * UnitPrice)
from Sales.SalesOrderDetail sod
group by (OrderQty * UnitPrice)
order by (OrderQty * UnitPrice) desc;
go

--HAVING 子句是专为SELECT 语句而生的，因为它只能与SELECT语句一起使用。而且通常在GROUP BY 子句中。
use AdventureWorks;
go
select ss.SalesOrderID,SUM(ss.LineTotal) as SubTotal
from Sales.SalesOrderDetail ss
group by SalesOrderID
having SUM(LineTotal) > 100000.00 -- 使用HAVING子句 指定组或聚合应满足的查询条件
order by SalesOrderID;

--在查询中使用COMPUTE子句返回合计
use AdventureWorks;
go
select sso.CustomerID,sso.OrderDate,sso.SubTotal,sso.TotalDue
from Sales.SalesOrderHeader sso
where SalesPersonID =276
order by OrderDate
compute sum(SubTotal),SUM(TotalDue);--使用COMPUTE子句

--在查询中使用COMPUTE BY 返回合计
use AdventureWorks;
go
select s.SalesPersonID,s.CustomerID,s.OrderDate,s.SubTotal,s.TotalDue
from Sales.SalesOrderHeader s
order by SalesPersonID,OrderDate
compute sum(SubTotal),sum(TotalDue) by SalesPersonID;--使用COMPUTE BY 子句

--联接查询
--1、内联接 包括同联接和自然联接
--2、外联接 包括左外联接，右外联接和完全联接
--3、交叉联接 笛卡尔积联接

--内联接和等值联接
use AdventureWorks;
go
select e.EmployeeID
from HumanResources.Employee as e
inner join Sales.SalesPerson as s
on e.EmployeeID = s.SalesPersonID

--左外联接
use AdventureWorks;
go
select p.Name,pr.ProductReviewID
from Production.Product p
left outer join Production.ProductReview pr
on p.ProductID = pr.ProductID

--右外联接
use AdventureWorks;
go
select st.Name as Territory,sp.SalesPersonID
from Sales.SalesTerritory st
right outer join Sales.SalesPerson sp
on st.TerritoryID =sp.TerritoryID;
--完全联接：将返回左表和右表中的所有行。如果左表在右表中没有匹配行，则右表显示空值，同理，如果右表在左表没有匹配行，左表也显示空值
use AdventureWorks;
go
--full 关键词后面的OUTER关键词可以省略不写
select p.Name,sod.SalesOrderID
from Production.Product p
full outer join  Sales.SalesOrderDetail sod
on p.ProductID = sod.ProductID
where p.ProductID is null or sod.ProductID is null order by p.Name;
--交叉联接
use AdventureWorks;
go
select p.SalesPersonID,t.Name as Territory
From Sales.SalesPerson p
cross join Sales.SalesTerritory t --使用cross关键词指定交叉联接
order by p.SalesPersonID;
--组合查询 union
use AdventureWorks;
go --判断表Gloves是否存在，如果存在则将其删除
if OBJECT_ID('dbo.Gloves','U') is not null
drop table dbo.Gloves;
go
--通过select into 语句 创建表Gloves表
SELECT P.ProductModelID,P.Name
INTO dbo.Gloves
FROM Production.ProductModel P
where P.ProductModelID in(3,4)
go
/*不正确的用法*/
use AdventureWorks;
go
select p.ProductModelID,p.Name
from Production.ProductModel p
where p.ProductModelID not in (3,4)
order by Name --这是错误的关键
union
select ProductModelID,Name
from dbo.Gloves;
go
/*正确用法*/
use AdventureWorks;
go
select p.ProductModelID,p.Name
from Production.ProductModel p
where p.ProductModelID not in(3,4)
union
select ProductModelID,Name
from dbo.Gloves
order by Name;--order by 语句放在最后，因为排序永远都是对已经查出的结果列
go

if not exists (select * from publishers where state ='NY')
begin
select 'Sales force needs to penetrate New York market'
end
else
begin
select 'we have publishers in new york'
end

if exists(select * from publishers where state ='NY')
begin
select 'we have publishers in new york'
end
else
begin
select 'sales force needs to penetrate new york market'
end
--两个语句之间的不同点包括执行的事务数，处理时间，从客户端到服务器端传送的数据量大小
--使用SQL语句或存储过程来计算任意一个月的天数？实现功能的SQL语句如下：
select DATEPART(DD,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)+'-'+CAST(month(getdate())as varchar)+'-01' as datetime))))
--控制SQL语句操作--DCL
--数据控制语言 GRANT语句 和 DENY 语句
-- GRANT 语句授予数据库和表对象权限；
--DENY 语句拒绝授予他们权限
--1、授予权限（GRANT）:对主体授予安全对象的权限
--2、拒绝权限（DENY）：对主体授予安全对象的否定权限
--3、收回权限（REVOKE）:收回（或撤销）已经授予的权限

--1、授予权限（GRANT）:对主体授予安全对象的权限
--授予创建表的权限
use AdventureWorks;
grant create table to zhao;
go
--2、使用GRANT OPTION 授予 CREATE VIEW
use AdventureWorks;
GRANT CREATE VIEW TO CandyWITH GRANT OPTION;
GO
--GRANT 表级对象权限
--1、授予对表的查询权限
use AdventureWorks;
GRANT SELECT ON OBJECT:: Person.Address To Rose;
go
--2、授予对存储过程的执行权限。
use AdventureWorks;
GRANT EXECUTE ON OBJECT :: HumanResources.uspUpdateEmployeeHireInfo
To Roma;
go
--GRANT 数据库主体权限 主要是对操作对象的管理，包括授予对数据库用户、数据库角色或应用程序角色的权限
--1、将某个用户的控制权限授予一个用户
use AdventureWorks;
GRANT CONTROL ON USER:: Ben To Roma;
go
--2、将对用户的IMPERSONATE权限授予应用程序角色
USE AdventureWorks;
GRANT IMPERSONATE ON USER:: Rose TO　Uncle;
go
--DENY语句 不允许具备某种权限
--1、拒绝创建证书的权限 拒绝用户Ben对AdventureWors数据库的CREATE CERTIFICATE权限
use AdventureWorks;
go
DENY CREATE CERTIFICATE TO Ben;
go
--2、使用CASCADE拒绝VIEW DEFINITION 权限
use AdventureWorks;
DENY VIEW DEFINITION TO Roma CASCADE;
GO
--DENY表级对象权限，表级对象就是表、视图、表值函数、存储过程、扩展存储过程等对象，拒约对以上对象
--1、拒绝对表的选择权限
use AdventureWorks;
DENY SELECT ON OBJECT:: Person.Address To Roma;
go
--2、拒绝对存储过程的执行权限
use AdventureWorks;
DENY EXECUTE ON OJBECT::HumanResources.uspUpdateEmployeeHiredInfo
to Ben;
go
--DENY数据库主体权限：就是对数据库的管理和操作的权限
use AdventureWorks;
DENY CONTROL ON USER:: MadaTo Acce;
go
--REVOKE 语句 剥夺权限 ，收回权限
--1、撤销创建证书的权限
use AdventureWorks;
REVOKE CREATE CERTIFICATE FROM Roma;
go
--2、从应用程序角色中撤销REFERENCES权限。
use AdventureWorks;
REVOKE REFERENCES FROM Acce;
go
--REVOKE 表级对象权限
--1、撤销对表的选择权限
use AdventureWorks;
REVOKE SELECT ON OBJECT:: Person.Address from Roma;
go
--2、撤销对存储过程的执行权限
use AdventureWorks;
REVOKE EXECUTE ON OBJECT:: HumanResources.uspUpdateEmployeeHireInfo
from Acce;
go
--REVOKE 数据库主体权限
--1、撤销一个用户对另一个用户的
use AdventureWorks;
REVOKE CONTORL ON USER:: Cendy FROM Roma;
go
--2、撤销数据库用户对数据库角色的权限
use AdventureWorks;
REVOKE VIEW DEFINITION ON ROLE:: AcceParking
from Rose CASCADE;
GO