--------------------------------------------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 MyIndexSELECT LEFT('WOW:34',charindex(':','WOW:34')-1) AS LName--获取字符串长度SELECT len('WOW:34') AS LengSELECT 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 ) --20040912select 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-yyselect 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 1select 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--MySqlrownum<=5--OracleTOP 5 --SQL Server--<5>DDL相关--主键自增SET IDENTITY_INSERT gjp_test.dbo.ptype ON--设置主键可显式插入1.MySqlcreate table student(id int auto_increment primary key not null,)2.SqlserverCreate 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()--MySqlINSERT 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 intSET @i=0UPDATE test01 SET [email protected],@[email protected]+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 ASET A.dd=B.prod_codefrom 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 INTSET @MyCounter = 0WHILE (@MyCounter < 26)BEGIN-- select SELECT @MyCounter,CHAR(@MyCounter + ASCII('a') ) -- Increment the variable to count this iteration -- of the loop. SET @MyCounter = @MyCounter + 1END--存储过程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)<14open mycur fetch next from mycur into @name,@prod_code,@upcwhile @@fetch_status=0 begin fetch next from mycur into @name,@prod_code,@upcend 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 unionselect *from stuMarks--重命名，表名exec sp_rename '原表名','改后表名'--重命名，列名exec sp_rename '表名.原列名','改后列名','column' ?