SQL 学习笔记

1、集合运算的优先级：Intersect >union =except  不能用order by 子句逻辑

2、apply 运算符：cross apply 和out apply

3、tran 事务管理： begin tran  commit tran  rollback

设置隔离级别： set transaction isolation level repeatable read/seriallzable

4、 1、数据库中的对象级别shcema和database？操作merge
User :仓库的主人，带有房间钥匙  
Database  ：仓库
Schema：仓库中的房间
Table：房间里的床
行和列：床上的物品
<2>
Merge的用法    
Merge可以完成以下功能：    
a>两个表之间数据的更新   
b>进行进销存更新库存   
c>进行表之间数据的复制  5、 (1)、查看SQL server日志："Management--->SQL Server Log"
(2)、SQL代理："SQL Server Agent"
(3)、透视转换：pivot ---分组、扩展、聚合
（1）是数字 用Sum()
（2）是字符串用MAX()6、数据库的备份方式数据库备份：
1、Task------>Back Up------gaochao.bak----恢复restore---恢复到数据备份原点
2、Task------>copy database------V-GAOCLI,1433
3、Task------> Export Wizard------localhost,1433---导入到本地后主外键关系不存在了？
Copy Database----->?7、SQL中的truncate ，delete，drop 区别：
truncate和不带where子句的delete、以及drop都会删除表内的数据
. truncate 和 delete 只删除数据不删除表的结构(定义)
drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。8、导入Excel到SQL时遇到问题：
http://www.cnblogs.com/shenyixin/p/3333428.html9、收缩日志（1）、MVP_Dashboard中Task----->shrink------>File,数据库的收缩功能
（2）关掉SQL的log功能
点击数据库---》“属性”----》options----》Recovery mode改为Simple，Auto Shrink改为True/ log右击去掉“Enable AutoGrowth”
3、数据库的名字和SQL中的库名可能会存在对不住的情况10、各个连接之间的区别(1)left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 
(2)right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
(3)inner join(等值连接) 只返回两个表中联结字段相等的行

-----------------------------------------------------------------------------------------------------------------------------------

 

Professional Microsoft SQL Server 2012 Administration

 

1、修改  sa1 密码

 

"exec sq_password  null" ,"NewPassword ,sa1"

 

2、Excel导入SQL中。excepl版本问题

 

未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序。（System.Data）

 

http://www.cnblogs.com/shenyixin/p/3333428.html

 

解决办法：

 

下载安装”2007 Office system 驱动程序：数据连接组件“:下载地址

 3、SQL Server 错误大全

 

http://www.2cto.com/database/201504/388233.html

 

4、使用不同账户登陆SQL ：任务栏右击，右击+shift，“一个小窗口”

 

1、登陆远程的服务器 ：Remote Desktop connection ----账户和密码

 

12-15

 

1、修改字段为NOT NULL

 

2、Send Mail Task 可以监控子包状态

 

3、alter table [dbo].[CRM_Contributions]    alter column [IsDelete] bit not null

 

4、Adventurework2012 attach 问题

 

http://blog.csdn.net/buptzhengchaojie/article/details/50452569

 

5、跨服务器做软链接，访问SQL数据库

 

6、查看SQL服务器角色

 

http://blog.csdn.net/yabingshi_tech/article/details/18730679

 

 1、查看SQL server日志："Management--->SQL Server Log"

 

2、SQL代理："SQL Server Agent"

 

3、透视转换：pivot ---分组、扩展、聚合

 

（1）是数字 用Sum()

 

（2）是字符串用MAX()

 

------9-22-------------------------------------------------------------

 

数据库备份：

 

1、Task------>Back Up------gaochao.bak----恢复restore---恢复到数据备份原点

 

2、Task------>copy database------V-GAOCLI,1433

 

3、Task------> Export Wizard------localhost,1433---导入到本地后主外键关系不存在了？

 

Copy Database----->?

 

4、整体生成数据库脚本（建表结构）---Generate Scripts …运行脚本时注意路径

 

D:\MVP_Dashboard.mdf

 

---------------------------------计算数据表中的行数-----------------

 

 

 

2、SQL中强制转换数据类型函数，

 

（1）cast 和convert

 

（2）派生列函数

 

3、SQL中的truncate ，delete，drop

 

truncate和不带where子句的delete、以及drop都会删除表内的数据

 

. truncate 和 delete 只删除数据不删除表的结构(定义)

 

drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

 

-------------------------------10-17---------------------------------------------------------

 

1、用C#语句实现Excel中的数据到数据库中，NOPI

 

2、创建普通视图 ，简化语句View

 

create view Bview

 

as 

 

SELECT   [Pid]

 

      ,[Pname]

 

      ,[Price]

 

  FROM [Boyan].[dbo].[Product]

 

 

 

  select *

 

  from  Bview

 

3、创建带有参数的存储过程  Procedure

 

create procedure sp_text4(@startid int,@endid int)

 

 as

 

 SELECT   [Pid]

 

      ,[Pname]

 

      ,[Price]

 

  FROM [Boyan].[dbo].[Product]

 

where Pid between @startid and  @endid

 

 

 

  exec sp_text4 1,3;

 

4、触发器是特殊的存储过程trigger

 

if object_id ('tgr_Student','TR') is not null

 

    drop trigger tgr_Student

 

go

 

create trigger tgr_Student

 

on  [dbo].[Student]

 

  for delete

 

as 

 

  if (object_id ('StudentBackup','U')is not null )

 

       insert into Student select Sname,Sage from deleted;

 

   else

 

      select * into StudentBackup from deleted;

 

go

 

--------------------10-19----------------------------------------------

 

1、导入Excel到SQL时遇到问题：

 

http://www.cnblogs.com/shenyixin/p/3333428.html

 

2、打开SQL监视器 montior

 

3、join 连接表中 条件用on

 

delete from  [MVP_Dashboard].[dbo].[CRM_AwardCategory]

 

where exists( select *

 

from  [tempdb].[dbo].[CRM_AwardCategory] as T

 

where [MVP_Dashboard].[dbo].[CRM_AwardCategory].[AwardCategoryId]=T.AwardCategoryId  

 

)

 

 

 

----------------------------10-20--------------------------------------------------------------------

 

1、MVP_Dashboard中Task----->shrink------>File,数据库的收缩功能

 

2、关掉SQL的log功能

 

点击数据库---》“属性”----》options----》Recovery mode改为Simple，Auto Shrink改为True

 

log右击去掉“Enable AutoGrowth”

 

3、数据库的名字和SQL中的库名可能会存在对不住的情况

 

4、delete XXX  与truncate table XXX 删除速度的区别  ，drop table XXX 删除表中的结构

 

删除数据库 drop database XXX 清空数据库中表的结构

 

------------------------------10-31--------------------------------------------------------------------------

 

1、清空主外键关系的表：和载入数据表的顺序反过来

 

2、去掉外键约束条件

 

ALTER TABLE [MVP_DashboardTest].[dbo].[CRM_Candidate]

 

NOCHECK constraint [FK_CRM_Candidate_CRM_AwardCategory]

 

3、存在A表不存在B表

 

select t.[msft_mvpcandidateid],t.[msft_fullname]

 

from [InfluencerTest_MSCRMUAT].[dbo].[msft_candidate] as t

 

where not exists(select c.Id from [dbo].[CRM_Candidate] as c where c.[Id]=t.[msft_candidateid] )

 

and t.[msft_fullname] is not null

 

4、如何删除CandidateId为null的字段

 

 

 

5、各个连接之间的区别

 

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 

 

right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

 

inner join(等值连接) 只返回两个表中联结字段相等的行

 

 

 

From <http://www.cnblogs.com/pcjim/articles/799302.html> 

 

 

 

12-6 数据库笔记

 

1、数据库中的对象级别shcema和database？操作merge

 

User :仓库的主人，带有房间钥匙  

 

Database  ：仓库

 

Schema：仓库中的房间

 

Table：房间里的床

 

行和列：床上的物品

 

<2>

 

Merge的用法    

 

Merge可以完成以下功能：    

 

a>两个表之间数据的更新   

 

b>进行进销存更新库存   

 

c>进行表之间数据的复制   

 

---------------------------------------------------------------------------------------

 

2、数据的read 和write，pull

 

生成数据库中的表，实例化对象，拉取数据

 

3、如何从SSIS中拿数据和API拿数据

 

Package----Data Flow ----OLE DataSource

 

4、关于sp拿数据的问题

 

Sp_helpdb  数据库  表、规则   sp_helpindex,sp_helptext,查看视图/存储过程文本 sp_helptrigger

 

--------------------------------------------------SQL清单

 

1、查询 ：select(group by,order by,top ,over(),having,like,case,distinct,as,substring(),replace(),like,datetime(),cast(),convert

 

Dateadd(),datediff(),datepart(),schema view)

 

2、连接 ；join(left join,cross join,right join,inset join,out join,full join,count(),sum,max,min,avg,,apply)

 

3、表表达式:CTE(with,function,row_number)

 

4、集合:union(intersect,except,)

 

5、透视:pivot(unpivot,unpivot,grouping set)

 

6、修改:alter(inset,delete,update,bulk insert,Merge,identity,truncate,output)

 

7、事务:(tran,commit,rollback,lock_timeout,transaction,serializable,snapshot)

 

8、可编程:(declare,cursor,go,if..else,while,exec,sp_executesql,trigger,try …catch)

 

9、子查询:(exists)

 

---------------------------------------------------------------12-21

 

1、事务 begin Transaction  /commit Transaction/rollback Transaction 

 

2、数据库中的角色：dbo  sysadmin public

 

3、名词：GRANT Revoke Deny Constraint  Primary key/  identity/foregin key

 

---------------------------------------------sql 用法积累、

 

1、表中的两列做差集

 

（1）用Except差集

 

SELECT  DISTINCT [University]

 

FROM [dbo].[Sheet1$]

 

EXCEPT

 

SELECT DISTINCT [msft_name]

 

FROM [dbo].[Sheet2$]

 

（2）EXCEL表格函数Vlookup

 

2、ISnull函数     用法 isnull（country,'为空'）

 

3、CTE临时表

 

;with cte_temptable as

 

( select ID,name

 

from A

 

)

 

select * from cte_temptable

 

4、游标  cursor

 

5、存储过程

 

6、创建返回值函数FN

 

7、表函数TF

 

8、创建触发器

 

9、传时间参数

 

10、数据库分页

 

11、数据库物理大小统计

 

12、日志收缩

 

13、连接服务器

 

14、Saas模式

 

15、获取字符串中的数字

 

16、数据库还原、备份

 

17、读取CSV文件

 

18、自定义排序

 

19、表拆分、合并

 

20、透视转换

 

21、数据库加密

 

2、透视与逆透视

 

透视

 

SELECT empid, A, B, C, D

 

INTO dbo.EmpCustOrders

 

FROM (SELECT empid, custid, qty

 

      FROM dbo.Orders) AS D

 

  PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;

 

逆透视

 

SELECT empid, custid, qty

 

FROM dbo.EmpCustOrders

 

  UNPIVOT(qty FOR custid IN(A, B, C, D)) AS U;

 

3、事务

 

Begin tran  事务开始，commit tran 提交事务，完整的事务机制

 

Begin tran 事务开始，rollback tran 回滚事务，不保存修改

 

事务会造成进程 延时。

 

事务是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。通过事务，SQL Server能将逻辑相关的一组操作绑定在一起，以便服务器保持数据的完整性。

 

4、锁

 

5、子句

 

(1)output

 

(2)group set

 

(3)truncate table

 

12-27  特殊查询,找表名和列名

 

Search a Table by table name

select * from INFORMATION_SCHEMA.Tables

where TABLE_NAME like '%reco%'

Search a Column by column name

select * from INFORMATION_SCHEMA.Columns 

where Column_NAME like '%msft_localizationid%'

 ------------------------------------------------------------------------------------SQL金典

substring('JanFebMar-----Dec',month(startdate)*3-2,3) as StartMonthEnglish

concat(filed1,filed2,filed3) as ZUHE