sql知识的小结（一）

1.数据库的备份
1)新建数据库操作
对于操作的数据库——》右击“编写数据库脚本”--->"create 到（c）"——》“新建查询编辑器窗口”

2)在创建新的数据库后，"任务"-》“生成脚本”，这一步操作是为了在已生成的数据库中创建新的表、导入存储过程，注意导入的只是数据库的结构

3)导入数据到表中
在源数据库上右击任务-》导入数据


2.将表1的数据放到表2
--允许将显式值插入表的标识列中 ON-允许  OFF-不允许
set identity_insert 表2 ON--打开
insert  into  [表2所在的数据库].dbo.表2(表2中需要加数据的字段) 
select 表1中的字段 from  [表1所在的数据库].dbo.表1
set identity_insert 表2 OFF--关闭


3.sql复制表结构，但是不复制数据
select *  into 数据库名.dbo.新表名   from  数据库名.dbo.原表名 where 1=0


4.复制表结构和表数据
1）如果新表不存在
select *  into 数据库名.dbo.新表名   from  数据库名.dbo.原表名


2）如果新表存在

insert into 数据库名.dbo.新表名 select * from 数据库名.dbo.原表名  ---表结构一致

insert into 新表名(列名1,列名2,.....) select 列名1,列名2....from 旧表名 --表结构不一致



5.数据库原有ID字段,是自增列的,后来把数据全删除后,想让ID自增列从1开始算起
dbcc checkident('表名',reseed,0)---ID自增列从1开始算起


6.备份表的一部分列（不写*而写出列的列表）
select 列名1,列名2,列名3 into 数据库名.dbo.新表名 from 数据库名.dbo.原表名


7.备份表的一部分行（加WHERE条件）
select * into 数据库名.dbo.新表名 from 数据库名.dbo.原表名 where id<10


8.在存储过程中，如果某个字段出错了，可以采用“新建查询”，打印出当前的变量
eg.DECLARE @tbGetFields nvarchar(max) = N'*';
   print @tbGetFields;


9.从项目表中读出开发人员
eg.
SELECT     TOP (200) 项目_编号, 项目_名称,
(select Top 1 负责人= stuff((select  ',' + (select top 1 姓名 from 员工表 where 员工编号= 任务_负责人)  from 任务表 t 
where t.负责人 = 负责人 and 
charindex(','+ltrim(项目编号)+',',','+convert( nvarchar(50),项目_编号)+',')>0 
 for xml path('')) 
 , 1 , 1 , '')  
from 任务表 ) as aaaaa 
FROM         项目表

10.在sql中的int转string的语句
  convert( nvarchar(50),要转的字段)

11.读取项目表的最新进度
select (select SUM(任务_所占百分比) from 任务表 where ((select top 1 状态_类型 from 任务状态表 where 状态_任务编号 = 任务_编号 order by 状态_编号 desc) = 3 ) and (任务_项目编号 = 项目_编号)) as 进度 from 项目表


12.存储过程中的变量
#变量类型为nvarchar(50)，则放在字符串中，用“set where += And 对应的字段名 = '+@变量名+'';”

#变量判断是否为空，变量类型为nvarchar(50)：“@变量名<>''”

#判断为并且用“and”


13.临时表的建立
1.#判断临时表是否存在
IF OBJECT_ID('tempdb..#临时表') is not null
drop table #临时表

#向临时表中加字段（适合临时表中的字段是从表中读取的）
create table #临时表(公司名称2 nvarchar(150),合同号21 int,合同号22 int)

2.#将客户订单的数据加到临时表中
Select * into #临时表 FROM 客户订单

3.#读取临时表中的数据
Select *  FROM #客户订单Temp;


14数据库的建表中
字段中需要添加时间:1.基础表（比如个人信息）2.需要不断向其中添加数据的表（比如商品的出入库）


15.charindex函数
CHARINDEX函数返回字符或者字符串在另一个字符串中的起始位置
CHARINDEX ( expression1 , expression2 [ , start_location ] )

说明：
Expression1是要到expression2中寻找的字符中，start_location是CHARINDEX函数开始在
expression2中找expression1的位置。
CHARINDEX函数返回一个整数，返回的整数是要找的字符串在被找的字符串中的位置


16.用SQL语句备份一张数据表 ：
select * into 备份表_backup from 备份表


17.用sql语句查询某天的数据条数
SELECT     COUNT(*) AS Expr1
FROM         GongHai
WHERE     (ghk_addtime >= '2017-05-05') AND (ghk_addtime < '2017-05-06')


18.数据库中的MD5加密：SUBSTRING(sys.fn_VarBinToHexStr(hashbytes('MD5', 变量名)),3,32)


19.根据部门编号读取包含的所有职位,函数STUFF()可以用来组合字符串，charindex()用于依次读取到数据
select *,
(select top 1 配置_职位名称 = STUFF((select  ',' + 配置_职位名称 from 配置_职位
where 
charindex(','+ltrim(配置_职位所属部门)+',',','+convert( nvarchar(50),配置_部门编号)+',')
>0 
 for xml path('')),1,1,'')  from 配置_职位)
from 配置_部门


20.读取重复项，根据重复项判断重复
select  重复项  from  表名  group  by  重复项  having  count(重复项) > 1


21.联系人和联系电话重复，并把重复的数据留一条，其他的状态更新为1

----sql中循环9次
declare @i int=0;


while(@i<9)
begin
 循环的语句
set @i+=1;
end


---更新字段
UPDATE  需要更新的表名 set 状态 = 1,时间=GETDATE(), 错误原因='联系人和联系电话重复' where  表名_ID in(

--重复项的读取
select   max(表名_ID)  from   表1  as h1
where (select COUNT(*) from 表1 as h2 where h2.重复字段1=h1.重复字段1 and h2.重复字段

2=h1.重复字段2 )>1

and (错误状态 = 0)        ---排除数据在错误表中的
group by 重复字段1,重复字段2)---重复项分组


22.程序员是做功能，删除数据是不行的，除非确定是测试数据


23.#读取某个字段上的数据，呈字符串显示，如“1，2，3”
select stuff((select  ','+ 学校 from 表名  t where t.表名_编号 = 表名_编号 group by 学校  for xml path('')) , 1 , 1 , '') as 校区;


24.#sql中charindex的用法：
charindex(','+ltrim(查询的值)+',',','+convert( nvarchar(50),查询的数值组)+',')>0 


25.#sql拼接字符串的用法：
SELECT 
STUFF
(
    (
        SELECT 
        ',' + --分隔符 
　　　　 KeyID --查询字段
        FROM #tmp --查询数据表
        WITH(NOLOCK) 
        WHERE 1=1 --查询条件
        FOR XML PATH('')
    ),
    1,-- 0:NULL 1:原样输出 2++:首位添加分隔符
    1,-- N:截取 N - Len 的字符
    ''-- 在字符串首尾添加的符号
)


26.#SQL字符串截取（SubString）的用法:
SubString(需要截取的字符串，截取指定的位置，截取的子字符串长度）


#SQL中DATENAME函数的用法:
DATENAME(param,date);
param是指定要返回日期部分的参数
date就是指定的日期


27.sql自动备份的教程
http://jingyan.baidu.com/article/6525d4b132ea10ac7c2e947c.html


28.查询表中下月生日的人:
比如生日字段是 DOB, 表名 TABLE ,那就是
SELECT * FROM TABLE WHERE Datediff(month,getdate(),DOB)  = 1 ; 这个是 SQL Server 的 
SELECT * FROM TABLE WHERE EXTRACT(MONTH FROM DOB) + 1 = EXTRACT(MONTH FROM CURDATE)  ;  这个是 mysql 的


29.SQL Server中截取字符串常用函数
1）LEFT ( character_expression , integer_expression )  
函数说明：LEFT ( '源字符串' , '要截取最左边的字符数'  )  
返回从字符串左边开始指定个数的字符  
select LEFT('SQL_Server_2008',4 );  
返回结果：SQL_  
  
  
2）RIGHT ( character_expression , integer_expression )  
函数说明：RIGHT ( '源字符串' , '要截取最右边的字符数'  )  
返回字符串中从右边开始指定个数的 integer_expression 字符  
select RIGHT('SQL_Server_2008',4 );  
返回结果：2008  
  
  
3）SUBSTRING ( character_expression , start , length )  
函数说明：SUBSTRING ( '源字符串' , '截取起始位置(含该位置上的字符)' , '截取长度' )  
返回字符、binary、text 或 image 表达式的一部分  
select SUBSTRING('SQL_Server_2008',5 ,6);  
返回结果：Server  



30. 名称 ****不是有效的标识符 sql
解决方法：
http://blog.csdn.net/xb12369/article/details/8202703


40.数据库、mysql的学习网站
http://www.yiibai.com/


50.SQL 指的是Structured Query Language(结构化查询语句)
用SQL从 "Persons" 表中选取 "FirstName" 列的值以 "a" 开头的所有记录：
SELECT * FROM Persons WHERE FirstName LIKE 'a%'


51.读取某个数据库中所有的表名称
SELECT Name FROM SysObjects Where XType='U' ORDER BY Name


52.读取某个数据库中所有的存储过程名称
SELECT Name FROM SysObjects Where XType='P' ORDER BY Name


53.表1和表2是没有关联的，表1中的字段有字段1、字段2、字段3，表2中的字段有字段1、字段2，用null来补足表
select 字段1,字段2,字段3 from 表1
UNION ALL
SELECT 字段1,字段2,null FROM 表2


54.year(时间字段)=2015:某一年的记录，按照每月来统计
----根据选择的年份统计出该年下每个月的销售额
select month(sdate) as 月份,SUM(money) as 销售额  from sale 
where year(sdate)=2015 group by month(sdate)


55.读取某个表中的所有字段：
Select Name FROM SysColumns Where id=Object_Id('表名')


56.sql数据库中的循环：
declare @sum int=4;---总的循环次数
declare @i int;---循环的变量
set @i=0
while @i<@sum---循环条件
begin
print @i;
set @i=@i+1
end


57.sql delete删除表中的最后一条语句为：
delete from table_Name where Id like (select top 1 Id from table_Name order by Id desc)
思路为：查找倒序的第一个，即上面代码中的子sql语句select top 1 Id from table_Name order by Id desc


58.---sql语句查询某个id值是否在字符串中
方法1：select 查询条件 from 表名 where (CHARINDEX(',id值,', ','+字段名称+',') > 0)
方法2：select 查询条件 from 表名 where ','+字段名称+',' like '%,id值,%'


59.SQLServer数据库和Access数据库的区别
SQLServer数据库比Access数据库更完整一些 功能更强大，更复杂，所以一般大型的公司都用SQLServer数据库，而中小型公司都用SQLServer数据库

Access是一种桌面数据库，只适合数据量少的应用，在处理少量数据和单机访问的数据库时是很好的，效率也很高。但是它的同时访问客户端不能多于4个。 access数据库有一定的极限，如果数据达到100M左右，很容易造成服务器iis假死，或者消耗掉服务器的内存导致服务器崩溃。

SQL Server是基于服务器端的中型的数据库，可以适合大容量数据的应用，在功能上管理上也要比Access要强得多。在处理海量数据的效率，后台开发的灵活性，可扩展性等方面强大。因为现在数据库都使用标准的SQL语言对数据库进行管理，所以如果是标准SQL语言，两者基本上都可以通用的。SQL Server还有更多的扩展，可以用存储过程，数据库大小无极限限制。

对过去用过的sql的一个小结，可能会存在某些问题，请大家帮忙指正。