Sql常用语句汇总

Sql常用语句汇总(2009-03-18 21:24:55)

标签：it

//基本语句
SqlServer 查询Access语句
    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\youfile.mdb';'admin';'youpwd',youtabname) AS a
新建表：create table [表名] ([字段一],[类型],[是否为空],…)
    例： create table mytry(
         name char(5) not null,
        code smallint identity(1000,1)) //将code设为自动递增的数据，从1000依次递增1
新增：insert into [表名] ([字段一],[字段二],…) values ('aa','bb',…) where … ;
更新：update [表名] set [字段一]='aa',[字段二]='bb' where …
删除：delete from [表名] where …
查找：select [字段一](as…),[字段二],… from [表名] where …

新加字段
    access:alter table tableName add[alter] column columnName columnType
    sqlserver:alter table tableName add[alter] columnName columnType
通配符
    like:
        反向：'037001002' like '%' + unitId + '%' //反向查找添加通配符后等于指定符的记录
        正向：unitId like '%037001002%'         //正向查找包括指定符的记录
    %:任意匹配
    _:匹配一个字符，一个汉字等于两个字符：'__'   
        WHERE au_fname LIKE '_ean' 将查找以 ean 结尾的所有 4 个字母的名字（Dean、Sean 等）。
        注：access中以?代替
    []:通配符：WHERE au_lname LIKE '[C-P]arsen' 将查找以arsen 结尾且以介于 C 与 P 之间的任何单个字符开始的作者姓氏，例如，Carsen、Larsen、Karsen 等。
    [^]:非：WHERE au_lname LIKE 'de[^l]%' 将查找以 de 开始且其后的字母不为 l 的所有作者的姓氏。
    escape:逃逸字符如 like '张#%%三' escape '#' ,为查找 张%某某的人
    select a.* from a,(……)b where a.id like b.id+'%'
条件成熟后操作：SELECT
        (CASE WHEN EXISTS (SELECT * FROM view_name WHERE user_id = '1')
        THEN user_id END),user_name    //user_id为选择字段
        FROM view_name
按组查询：(当有统计函数，普通字段)
    SELECT codeid, SUM(codelevel) AS 水平
    FROM wdc_ts_code
    GROUP BY codeid   
创建视图：
    create view myview as (select …)
最右/最左一位
    right(字段,位数) = '匹配字符串'/left(字段,位数) = '匹配字符串'
空字段
    字段 is null
建表及主键
    CREATE TABLE [dbo].[uploadFiles1]
    (
        [id] [int] IDENTITY (1, 1) NOT NULL,
        PRIMARY KEY (id，……)
    ) ON [PRIMARY]
    或
    CREATE TABLE [dbo].[uploadFiles1]
    (
        [id] [int] IDENTITY (1, 1) NOT NULL primary key
    ) ON [PRIMARY]       
批量更新，多表链接
    UPDATE A004A001
    SET status = '1000000000'
    FROM A004A001 INNER JOIN
              (SELECT MAX(id) AS id, A0100
             FROM A004A001
             GROUP BY A0100) AS b ON A004A001.A0100 = b.A0100 AND A004A001.ID = b.ID

    UPDATE SR_BuiltItem INNER JOIN SR_SourceItem ON (SR_BuiltItem.FieldName = SR_SourceItem.FieldName) AND (SR_BuiltItem.SetId = SR_SourceItem.SetId) SET SR_BuiltItem.Description = [SR_SourceItem].[Description];
参数声明
    strSql=@"declare @MaxId int;Select @MaxId="+ dr["Cx_MainID"].ToString() +";";    (strSql=@"declare @MaxId int;select @MaxId=(Select IsNull(Max(Cx_MainID),0)+1 From SR_CxMain);";)
    strSql+="Delete From SR_CxMain Where Cx_MainID=@MaxId;";
   
//精妙语句
1、复制表(创建)：
    select * into b from a where id<>1
2、复制表(新增)：
    insert into b(a, b, c) select d,e,f from g;
3、显示多表信息(不关联)：显示文章、提交人和最后回复时间
    select a.title,a.username,b.adddate from table a,
    (select max(adddate) adddate from table where table.title=a.title) b
4、显示多表信息(关联)：
    select a.id,b.name from a,b where a.id = b.cid
    --select name,sex,(select b.age from b where b.name=a.name) as age from a
5、清理多表信息(关联)：删除主表中已经在副表中没有的信息
    delete from info where not exists (select * from infobz where info.infid=infobz.infid)
6、得到表中最小的未使用的ID号
    SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID
     FROM Handle
     WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
    --SELECT personId, CASE WHEN serviceId IS NULL THEN '0000' ELSE serviceId END
        FROM pub_userLogin
7、一个表中的数据是否在另一集合中 (子查询)
    select aa,bb from aaa where aa in (select cc from bbb)    --查询的是集合
    select aa,bb from aaa where aa = (select cc from bbb)    --查询的是一条记录
8、where
    select * from aaa where (select count(*) from bbb) = (select * from ccc)
9、降序：order by aaa desc
10、多表关联：
    两表：select [表名1.字段] from [表名1] inner join [表名2] on [表名1.字段] = [表名2.字段]
    或：select *,(select description from sr_department where code = (select top 1 code from web_tbl_login where userid =2)) as description from web_tbl_login where userid = 2
11、两 表叠加：
    select "SCOTT"."web_tbl_login"."LOGINID",
        "SCOTT"."web_tbl_login"."REALNAME",
        "SCOTT"."wdc_ts_code"."CODE",
        "SCOTT"."wdc_ts_code"."DESCRIPTION",
    from "SCOTT"."web_tbl_login" ,
          "SCOTT"."wdc_ts_code"
    where "SCOTT"."web_tbl_login"."BUILDUNITID" = "SCOTT"."wdc_ts_code".code
     and "SCOTT"."wdc_ts_code".codeid = 'NM'

     union

    select "SCOTT"."web_tbl_login"."LOGINID",
        "SCOTT"."web_tbl_login"."REALNAME",
        '000' as "code",
        "SCOTT"."web_tbl_login"."BUILDUNITID",    
    from "SCOTT"."web_tbl_login"
    where "SCOTT"."web_tbl_login"."STATION" = '系统管理员'
12.反回不同的行    //有重复只显示一行
    select distinct id,name from a;
13.Inner Join 与 Left Join 与 Right Join区别
    Inner Join：二者皆符合才显示
    Left Join：以左为主，右边符合条件则显示，否则为空
    Right Join：以右为主，……
14.提示不是有效的标识符
    exec (@StrSql)
15.
    declare @user varchar(1000)
    declare @moTable varchar(20)
    select @moTable = 'MT_10'
    declare @sql nvarchar(4000) --定义变量,注意类型
    set @sql='select @user = count(distinct userid) from '+@moTable --为变量赋值-
    -执行@sql中的语句
    exec sp_executesql @sql
    ,N'@user varchar(1000) out' --表示@sql中的语句包含了一个输出参数
    ,@user out                   --和调用存储过程差不多,指定输出参数值print @user


//SQL函数
1、数据类型：(创建table时的类型)
    1、整数：int    2.浮点：float    3.二进制：binary   
    4.字符：char,varchar,nchar    5.文本、图形：text/ntext-image
    6.日期：datetime        7.货币：money
2、变量声明/赋值：
    1.全局变量：declare @@x int;
    2.局部变量：declare @x int,@mychar char(10);
    set/select @x = 1 , @mychar = '123'
3、运算符：
    > < >= <= <>    not and or
4、常用函数：
    1.平均：    avg([字段名])----(as [新字段名])
    2.列名:        count([字段名])--(as [新字段名])
    3.最大值：    max([字段名])----(as [新字段名])
    4.最少值：    min([字段名])----(as [新字段名])
    5.总和：    sum([字段名])----(as [新字段名])
    6.平均差：    stdev([字段名])--(as [新字段名])
5.系统函数：
    1.列名：col_name( ,)
    2.列宽：col_length( ,)
    3.数据表编号：object_id(<数据表名称>)
    3.数据表名称：object_name(<数据表编号>)
    4.数据库编号：db_id()
    5.数据库名称：db_name()
    6.标识增量：ident_incr(<标识字段名>)--(as …)
    7.标识初值：ident_seed(<标识字段名>)--…
6其它运算符：
    1。语然嵌套(any/some)
        select * from aaa
        where codeid >= any (select code from bbb )
    2。至少返回一行：[not] exists
        select * from aaa
        where exist(select * from bbb where …)
    3。模糊查询：m [not] like n
        select * from aaa where name like '001%'
        'x%'：匹配“x…”
        '%x%":匹配“…x…”
        "%x":匹配“…x”
    4.任意一个：all
        select * from ... where salary <= all(2500,3000,4000)
    5.界于两者之间：[not] between m and n
        select * from ... where salary between 5000 and 6000
8日期差
select datediff(yy,[startDate]'2005-01-01',[endDate]'2006-01-01')
    yyyy--年
    m--月
    d--日
    q--季度
    y--一年的日期
    w--一周的日数
    ww--周
    h--小时
    n--分种
    s--秒


//字符串
1.ascii(字段)
    返回最左端字符的ASCII码
    select ascii(Age) as 'age' from ……
2.char(字段)
    将(0-255)之间的Ascii码转换为字符
    select char(age) as 'age'
3.lower(字段)
    将字段内容小写
4.upper(字段)
    将字段内容大写
5.str(数字，字符串长度，小数位数)
6.ltrim(字段)
    字符串左部空格去掉
7.rtrim(字段)
    字符串右部空格去掉
8.left(字段，长度)
    取字段左边指定长度
    select left('sqlserver',3)     =sql
9.right(字段，长度)
    取字段右边指定长度
    select right('sqlserver',3)    =ver
10.substring(字段，指定位置(从一开始)，长度)
    截取字段长度
    select substring('sqlserver',1,3)=sql   
11.charIndex(比较字符，字段)    不能用于Text类型
    比较字符中字段中出现的位置
    select charindex('123','abc123defg')    =4
12.patindex('%比较字符%',字段)    前后须有% 可用于Text类型
    比较字符中字段中出现的位置
    select patindex('%123%','abc123defg')    =4
    charindex('%[0-2]2%','aaa22')
    不存在则返回0
13.day(字段)month(字段)year(字段)
14.len(字段)
15.replace('字段','oldStr','newStr');
    xxx 替换 abcdefghi 中的字符串 cde
    SELECT REPLACE('abcdefghicde','cde','xxx')
16.STUFF('要操作的字符串',起始位置int,删除长度int,'删除位置替换字符')
    (abcdef) 中删除从第二个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，创建并返回一个字符串。
    SELECT STUFF('abcdef', 2, 3, 'ijklmn')