SQL笔记整理

--------------------------------------------SQL笔记----------------------------------------

--<1>截取字符串
--LEFT (<character_expression>， <integer_expression>)
--返回character_expression 左起 integer_expression 个字符。
SELECT LEFT('WOW:34',3) AS Str
--charindex()
--返回字符串中某个指定的子串出现的开始位置。
--CHARINDEX (<’substring_expression’>， <expression>)
--其中substring _expression 是所要查找的字符表达式，expression 可为字符串也可为列名表达式。
--如果没有发现子串，则返回0 值。
--此函数不能用于TEXT 和IMAGE 数据类型。
SELECT charindex(':','WOW:34')-1 AS MyIndex
SELECT LEFT('WOW:34',charindex(':','WOW:34')-1) AS LName
--获取字符串长度
SELECT len('WOW:34') AS Leng
SELECT right('WOW:34',len('WOW:34')-charindex(':','WOW:34')) AS RName

--<2>类型转换
--将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。
--语法
--使用 CAST：
--CAST ( expression AS data_type ) 
SELECT CAST('2009-09-24' AS DATETIME)
--使用 CONVERT：
--CONVERT (data_type[(length)], expression [, style])
SELECT convert(DATETIME,'2009-10-10')
SELECT getdate()
SELECT convert(VARCHAR(10),getdate()) 
SELECT LEFT(getdate(),10)

--<3>日期相关
--时间差 datediff（）函数
select datediff(year,'2008-10-10',getdate())
select datediff(month,'2008-10-10',getdate())
select datediff(week,'2008-10-10',getdate())
select datediff(day,'2008-10-10',getdate())
select datediff(day,'2009-10-10',getdate())
SELECT dateadd(day,-1,'2010-01-03')
--日期格式化
--0 或 100 (*) 默认值 mon dd yyyy hh:miAM（或 PM） 
select CONVERT(VARCHAR, getdate(),0)
select substring(CONVERT(VARCHAR, getdate(), 120 ),1,10)--2004-09-12 
select CONVERT(VARCHAR(10), getdate(), 120 ) --2004-09-12 11:06:08 
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),\'-\',\'\'),\' 
\',\'\'),\':\',\'\') --20040912110608 
select CONVERT(varchar(12) , getdate(), 111 ) --2004/09/12 
select CONVERT(varchar(12) , getdate(), 112 ) --20040912
select CONVERT(varchar(12) , getdate(), 102 ) --2004.09.12 ANSI yy.mm.dd 
select CONVERT(varchar(12) , getdate(), 101 ) --09/12/2004 美国 mm/dd/yyyy 
select CONVERT(varchar(12) , getdate(), 103 ) --12/09/2004 英国/法国 dd/mm/yy 
select CONVERT(varchar(12) , getdate(), 104 ) --12.09.2004 德国 dd.mm.yy 
select CONVERT(varchar(12) , getdate(), 105 ) --12-09-2004 意大利 dd-mm-yy
select CONVERT(varchar(12) , getdate(), 106 ) --12 09 2004 dd mon yy 
select CONVERT(varchar(12) , getdate(), 107 ) --09 12, 2004 mon dd, yy 
select CONVERT(varchar(12) , getdate(), 108 ) --11:06:08 hh:mm:ss 
select CONVERT(varchar(12) , getdate(), 109 ) --09 12 2004 1
select CONVERT(varchar(12) , getdate(), 110 ) --09-12-2004 
select CONVERT(varchar(12) , getdate(), 113 ) --12 09 2004 1 
select CONVERT(varchar(12) , getdate(), 114 ) --11:06:08.177
--9 或 109 (*) 默认值 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM（或 PM） 


--<4>条件相关
--替换空值函数
coalesce(name,'空名') --如果name为null则替换成'空名'否则为name
--返回限制行数
limit 5--MySql
rownum<=5--Oracle
TOP 5 --SQL Server

--<5>DDL相关
--主键自增
SET IDENTITY_INSERT gjp_test.dbo.ptype ON--设置主键可显式插入
1.MySql
create table student(
id int auto_increment primary key not null,)

2.Sqlserver
Create table student(
id int identity(1001,1) primary key not null,)
identity(基础值，增量)
3.Orcale
在Oracle中要为每个表创建一个单独序列(sequence),然后从这个序列中获取自动增加的标识符在把他赋值给主键。
例如创建一个名为stu_id_seq的序列，这个序列的起始值为1，增量为2。
create sequence stu_id_seq increment by 2 start with 1
一旦定义了stu_id_seq序列，就可以访问序列的curval和nextval属性
curval：返回序列的当前值
nextval：先增加序列的值，然后返回序列值
create table student(
id int  primary key not null,
name varchar(30) not null)
insert into student values(stu_id_seq.curval, "abc"),(stu_id_seq.nextval, "def");
然后去查询这个表的id select id from student
结果为1和3
--IDENTITY_INSERT
允许将显式值插入表的标识列中,自增字段。
SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }
任何时候，会话中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON，在执行或运行时设置，而不是在分析时设置。
--阻止对某几列插入
在表中创建一个视图，强制所有的插入操作都通过该视图进行
CREATE VIEW v_test03 AS SELECT name,tel,address FROM tb_test03
新增列
ALTER TABLE DMS_data_store ADD  IS_USE VARCHAR(2) DEFAULT('0')
--<6>插于更新与删除
--按默认值插入记录
INSERT INTO tb_test01 VALUES()--MySql
INSERT INTO tb_test01 DEFAULT VALUES--SQL Server
--DB2和MySql可一次插入多行
INSERT INTO tb_test01 VALUES(1),(2)
--复制
INSERT INTO tb_test01(address) SELECT address FROM tb_test02 --tb_test01存在的情况下
SELECT * INTO gjp_test.dbo.ptype  FROM gjp_changzhi.dbo.ptype --gjp_test.dbo.ptype不存在
--删除重复
SELECT * FROM test a WHERE id>(SELECT min(id) FROM test b WHERE a.name=b.name) --只删除多余的,保留id最小的
SELECT * FROM test a WHERE name IN (SELECT name FROM test b WHERE a.name=b.name GROUP BY name HAVING count(name)>1) --删除所有重复的
--自增rowid 从1开始
DECLARE @i int
SET @i=0
UPDATE test01 SET rowid=@i,@i=@i+1
--创建索引
create index IX_1243766481215 on ASSAY_COL_SAM_WAGON (COLLECTION_ID) 
-------------------------------------------------数据库相关--------------------------------------------------------
DUMP TRANSACTION kraft_esky WITH no_log 清空日志文件，而后收缩（Shrink-->file）
数据备份：
BACKUP DATABASE kraft_esky  TO DISK='e:\kraft_esky.BAK'
更新数据：
UPDATE   A

SET A.dd=B.prod_code

from DMS_DATA_STOCK A  
inner join DMS_DATA_STOCK B 
on A.prod_name =B.prod_name AND A.client_id =B.client_id AND A.prod_unit =B.prod_unit AND
 A.stock_date <'2009-11-22 0:00:00' AND B.stock_date ='2009-11-22 0:00:00'
 AND A.CLIENT_ID =14 AND B.CLIENT_ID = 14
----------------------
Access和其他库的函数：
SELECT cdate('2009-11-01')  --cstr()转字符
SELECT round(mid('2009-11-01',6,2),0) --mid()类似substr(),round()转成NUMERIC

 

----------------------------
SQLServer中查询数据表字段名称的查询语句
SELECT     
 SysObjects.Name as TableName,      
 SysColumns.Name as ColumnsName,      
 SysTypes.Name as DateType,      
 SysColumns.Length as DateLength,      
 SysProperties.Value as Remark     --列描述      
FROM SysObjects,SysTypes,SysColumns      
LEFT  JOIN  SysProperties          
ON (Syscolumns.Id  = Sysproperties.Id AND  Syscolumns.Colid  = Sysproperties.Smallid)      
WHERE (Sysobjects.Xtype     ='u'     OR     Sysobjects.Xtype     ='v')      
 AND     Sysobjects.Id     =     Syscolumns.Id      
 AND     SysTypes.XType     =     Syscolumns.XType      
 AND     SysTypes.Name     <>     'sysname'    
 AND    SysObjects.name    = 'jobs' 
-----------------------------
远程桌面
mstsc /v:210.51.22.38 /console
就可以连接到远程系统的的0会话
---------------------------------
SELECT @@DATEFIRST --查询当前星期的第一天是星期几
SET DATEFIRST 1 --设置星期的第一天(默认为星期日,现设为星期一)
SELECT DATEPART (weekday,getdate()) --当前日期是星期几
----------------------------------
--循环
-- Declare the variable to be used.
DECLARE @MyCounter INT
SET @MyCounter = 0
WHILE (@MyCounter < 26)
BEGIN-- select
   SELECT @MyCounter,CHAR(@MyCounter + ASCII('a') )
   -- Increment the variable to count this iteration
   -- of the loop.
   SET @MyCounter = @MyCounter + 1
END
--存储过程
CREATE PROCEDURES  dbo.procedure (@var INT) AS BEGIN END
--执行参数
procedure 1
----------------------------------
----'asd'
SELECT char(39)+'asd'+char(39) 
SELECT '''asd'''
--------------------------------------
declare @name varchar(150),@prod_code varchar(150),@upc varchar(150) 
declare mycur cursor local for 
SELECT  name,prod_code,upc FROM errorProd_code WHERE upc IS NOT NULL AND len(upc)<14
open mycur 
fetch next from mycur into @name,@prod_code,@upc
while @@fetch_status=0 
begin 

fetch next from mycur into @name,@prod_code,@upc
end 
close mycur 
deallocate mycur
---------------------------------------
--命令行执行SQL脚本
osql -E -i t_rp_LockTable_insert.sql
--------------------------------------------------基础语句-----------------------------------------------------
--如何用sql更改表的列的数据类型和添加新列和约束

--增加一列
ALTER TABLE 表名 ADD 列名 VARCHAR(20) NULL
--删除一列
ALTER TABLE 表名 drop COLUMN 列名 
--修改一列
alter TABLE 表名 ALTER COLUMN 列名 VARCHAR(40) NULL
--修改一列的类型
alter TABLE 表名 ALTER COLUMN 列名 VARCHAR(40)

--添加主键约束
alter table 表名
add constraint 约束名 primary key (列名)

--添加唯一约束
alter table 表名
add constraint 约束名 unique (列名)

--添加默认约束
alter table 表名
add constraint 约束名 default(内容) for 列名

--添加check约束
alter table 表名
add constraint 约束名 check(内容)

--添加外键约束
alter table 表名
add constraint 约束名 foreign key(列名) references 另一表名(列名)


--删除约束
alter table 表名
drop constraint 约束名


--纵向连接两个表
select *from stuInfo union
select *from stuMarks

--重命名，表名
exec sp_rename '原表名','改后表名'
--重命名，列名
exec sp_rename '表名.原列名','改后列名','column'