• home > DB > slq >

    SQL语句大全中的sql语句备忘录—sql日常使用指北

    Author:zhoulujun@live.cn Date:

    sql语句大全,备忘查询。对于经常使用的一些sql语句分类整理,​按CRUD的的规律,对行列、数据、表 基本操作语句 范例 整理归集。平时开发备忘速查

    CRUD:增查改删,即,create/read/update/delate 主要被用在描述软件系统中数据库或者持久层的基本操作功能

    而一般所说的数据库的增删改查,即insert/delate/update/select是sql语言中的DML(data manipulation))语言

    列操作

    • 查看表结构 mysql> desc myTable;  || describe myTable

    • 添加列 mysql> alter table test add(tage int(3));
    • 删除列 mysql> alter table test drop column tage;


    • 修改原表结构 mysql> alter table test modify tage int(5) not null;
    • 修改列的默认值mysql> alter table test alter tage set default '0';
    • 去掉列的默认值 mysql> alter table test alter tage drop default; 

    设置主键

    ALTER TABLE `table_name` ADD PRIMARY KEY(`field_name`);
    ALTER TABLE `table` CHANGE `field` `field_new_name` INT(4) NOT NULL DEFAULT '0' FIRST;
    然后这里有个坑, id 必须反引号,不然报错。
    alter table fuck change id `id` int(4) not null defualt '0' frist; 

    数据增删改查

    • 插入数据mysql> insert into test(tid,tname,tdate) value(1,'yangjuqi','2008-03-21');
    • 删除数据mysql> delete from test where tid='2';

    • 修改数据 mysql> update test set tname='张三' where tid='2';
    • 查询数据 mysql> select * from test;

      • 模糊查询 mysql> select * from test where tname like '%杨%';
      • 分页查询(limit 起始行,取多少行)mysql> select * from testbak limit 2,1;
      • 将查询出的数据写入文件 mysql> select * from user into outfile "/etc/test.txt" fields terminated by ",";

      • 排序:select * from table1 order by field1,field2 [desc]
      • 总数:select count as totalcount from table1
      • 求和:select sum(field1) as sumvalue from table1
      • 平均:select avg(field1) as avgvalue from table1
      • 最大:select max(field1) as maxvalue from table1
      • 最小:select min(field1) as minvalue from table1

    几个高级查询运算词

    • UNION 运算符 
      UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 
    • EXCEPT 运算符 
      EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 
    •  INTERSECT 运算符
      INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 
    注:使用运算词的几个查询结果行必须是一致的。 

    使用外连接 

    left (outer) join: 
    左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 
    SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
    right (outer) join: 
    右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 
    full/cross (outer) join: 

    全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。

    分组:Group by

    一张表,一旦分组 完成后,查询后只能得到组相关的信息。

    SELECT `username`,`age`,`tel`,`sex` FROM testusers GROUP BY `sex`;

    GROUP_CONCAT()函数

    根据sex 字段进行分组并查看username字段和age字段的详细信息。(这下对基本用法就很好理解了。因为他默认只显示了组中一条记录,如果想看组内所以的用户信息,就需要用到GROUP_CONCAT()函数。接着往下↓)

    SELECT GROUP_CONCAT(username),GROUP_CONCAT(age),sex FROM testusers GROUP BY sex

    统计记录总数 COUNT()函数:

    SELECT COUNT(*) AS totalUsers, GROUP_CONCAT(username) AS userDetail ,GROUP_CONCAT(age) AS userAge,sex FROM testusers GROUP BY sex;

    分组结果进行二次筛选 HAVING 子句

    相当于WHERE是一次筛选,HAVING是在二/再次筛选.

    SELECT COUNT(*) AS totalUsers,GROUP_CONCAT(age) AS userAge,GROUP_CONCAT(username) AS userDetail,sex FROM testusers GROUP BY sex HAVING totalUsers>=5;

    表操作 

    • 查看表 mysql > show tables;

    • 删除表 mysql > drop table test;
    • 重命名表 mysql > alter table test rename testbak;

    数据库操作

    • 刷新数据库 mysql> flush privileges;

    • 显示数据库版本 mysql> select version();
    • 显示当前时间 mysql> select current_date;
    • 查看数据库状态 mysql> status;
    • 查看所有编码 mysql> show variables like 'character_set_%';
    • 导入sql文件命令 mysql> source /etc/MySQL.sql;   mysqlimport -u root -p dbname filename.sql 
    • 备份数据库: mysqldump -u root -h host -p dbname > backdb.sql
      • 备份数据库中的某个表 mysqldump -u root -h host -p dbname tbname1, tbname2 > backdb.sql

      • 备份多个数据库 mysqldump -u root -h host -p --databases dbname1, dbname2 > backdb.sql

      • 备份系统中所有数据库 mysqldump -u root -h host -p --all-databases > backdb.sql

    sql操作范例

    一、基础
    1、说明:创建数据库
    CREATE DATABASE database-name 
    1:建立一个名为xhkdb的数据库
       mysql> create database xhkdb;
    例2:创建数据库并分配用户
    ①CREATE DATABASE 数据库名;
    ②GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON 数据库名.* TO 数据库名@localhost IDENTIFIED BY '密码';
    ③SET PASSWORD FOR '数据库名'@'localhost' = OLD_PASSWORD('密码');
    依次执行3个命令完成数据库创建。 
    2、说明:删除数据库
    drop database dbname
    例如:删除名为 xhkdb的数据库
    mysql> drop database xhkdb; 
    例子2:删除一个不确定存在的数据库
       mysql> drop database drop_database;
       ERROR 1008 (HY000): Can't drop database 'drop_database'; database doesn't exist
          //发生错误,不能删除'drop_database'数据库,该数据库不存在。
       mysql> drop database if exists drop_database;
       Query OK, 0 rows affected, 1 warning (0.00 sec)//产生一个警告说明此数据库不存在
       mysql> create database drop_database;
       Query OK, 1 row affected (0.00 sec)
       mysql> drop database if exists drop_database;//if exists 判断数据库是否存在,不存在也不产生错误
       Query OK, 0 rows affected (0.00 sec) 
    3、说明:备份sql server
    --- 创建 备份数据的 device
    USE master
    EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
    --- 开始 备份
    BACKUP DATABASE pubs TO testBack 
    4、说明:创建新表
    create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 
    根据已有的表创建新表: 
    A:create table tab_new like tab_old (使用旧表创建新表)
    B:create table tab_new as select col1,col2… from tab_old definition only
    5、说明:删除新表
    drop table tabname 
    例如:删除表名为 MyClass 的表
       mysql> drop table MyClass;
    6、说明:增加一个列
    Alter table tabname add column col type
    注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。
    7、说明:添加主键: Alter table tabname add primary key(col) 
    说明:删除主键: Alter table tabname drop primary key(col) 
    8、说明:创建索引:create [unique] index idxname on tabname(col….) 
    删除索引:drop index idxname
    注:索引是不可更改的,想更改必须删除重新建。
    9、说明:创建视图:create view viewname as select statement 
    删除视图:drop view viewname
    10、说明:几个简单的基本的sql语句
    选择:select * from table1 where 范围
    插入:insert into table1(field1,field2) values(value1,value2)
    例如:往表 MyClass中插入二条记录, 这二条记录表示:编号为1的名为Tom的成绩为96.45, 编号为2 的名为Joan 的成绩为82.99, 编号为3 的名为Wang 的成绩为96.5。
       mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);
    删除:delete from table1 where 范围
      6:显示表的结构: 
      mysql> DESDRIBE MYTBBLE; 
        7:往表中插手记录 
      mysql> insert into MYTBBLE values ("hyq","M");

     

    INSERT INTO users (firstLoadTime,loadTimeDay) VALUES ('".date('Y-m-d H:i:s',time())."','".date('Y-m-d H:i:s',time())."',0,2,'");
      8:用文本方式将数据装入数据库表中(例如D:/mysql.txt) 
      mysql> LOBD DBTB LODBL INFILE "D:/mysql.txt" INTO TBBLE MYTBBLE; 
      9:导入.sql文件号令(例如D:/mysql.sql) 
      mysql>use database; 
      mysql>source d:/mysql.sql;  
    更新:update table1 set field1=value1 where 范围
    mysql>update MYTABLE set sex=”f” where name="hyq"
    查找:select * from table1 where field1 like "%value1%" ---like的语法很精妙,查资料!

    slq操作技能提升

    1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用)

    法一:select * into b from a where 1<>1(仅用于SQlServer)
    法二:select top 0 * into b from a


    2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用)

    insert into b(a, b, c) select d,e,f from b;

     

    3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
    insert into b(a, b, c) select d,e,f from b in &lsquo;具体数据库&rsquo; where 条件
    例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..

     

    4、说明:子查询(表名1:a 表名2:b)
    select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)

     

    5、说明:显示文章、提交人和最后回复时间
    select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

     

    6、说明:外连接查询(表名1:a 表名2:b)
    select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

     

    7、说明:在线视图查询(表名1:a )
    select * from (SELECT a,b,c FROM a) T where t.a > 1;

     

    8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括
    select * from table1 where time between time1 and time2
    select a,b,c, from table1 where a not between 数值1 and 数值2

     

    9、说明:in 的使用方法
    select * from table1 where a [not] in (&lsquo;值1&rsquo;,&rsquo;值2&rsquo;,&rsquo;值4&rsquo;,&rsquo;值6&rsquo;)

     

    10、说明:两张关联表,删除主表中已经在副表中没有的信息 
    delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

     

    11、说明:四表联查问题:
    select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

     

    12、说明:日程安排提前五分钟提醒 
    SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

     

    13、说明:一条sql 语句搞定数据库分页
    select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
    具体实现:
    关于数据库分页:
      declare @start int,@end int
      @sql  nvarchar(600)
      set @sql=&rsquo;select top&rsquo;+str(@end-@start+1)+&rsquo;+from T where rid not in(select top&rsquo;+str(@str-1)+&rsquo;Rid from T where Rid>-1)&rsquo;
      exec sp_executesql @sql

     

    注意:在top后不能直接跟一个变量,所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列,如果top后还有具体的字段,这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的,查询的结果后实际表中的不一致(逻辑索引中的数据有可能和数据表中的不一致,而查询时如果处在索引则首先查询索引)
    14、说明:前10条记录
    select top 10 * form table1 where 范围

     

    15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
    select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

     

    16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
    (select a from tableA ) except (select a from tableB) except (select a from tableC)

     

    17、说明:随机取出10条数据
    select top 10 * from tablename order by newid()

     

    18、说明:随机选择记录
    select newid()

     

    19、说明:删除重复记录
    1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
    2),select distinct * into temp from tablename
      delete from tablename
      insert into tablename select * from temp
    评价: 这种操作牵连大量的数据的移动,这种做法不适合大容量但数据操作
    3),例如:在一个外部表中导入数据,由于某些原因第一次只导入了一部分,但很难判断具体位置,这样只有在下一次全部导入,这样也就产生好多重复的字段,怎样删除重复字段

     

    alter table tablename
    --添加一个自增列
    add  column_b int identity(1,1)
     delete from tablename where column_b not in(
    select max(column_b)  from tablename group by column1,column2,...)
    alter table tablename drop column column_b

     

    20、说明:列出数据库里所有的表名
    select name from sysobjects where type='U' // U代表用户

     

    21、说明:列出表里的所有的列名
    select name from syscolumns where id=object_id('TableName')

     

    22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。
    select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
    显示结果:
    type vender pcs
    电脑 A 1
    电脑 A 1
    光盘 B 2
    光盘 A 2
    手机 B 3
    手机 C 3

     

    23、说明:初始化表table1

     

    TRUNCATE TABLE table1

     

    24、说明:选择从10到15的记录
    select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

     

    sql操作技巧 

    1、1=1,1=2的使用,在SQL语句组合时用的较多

     

    &ldquo;where 1=1&rdquo; 是表示选择全部    &ldquo;where 1=2&rdquo;全部不选,
    如:
    if @strWhere !='' 
    begin
    set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere 
    end
    else 
    begin
    set @strSQL = 'select count(*) as Total from [' + @tblName + ']' 
    end

     

    我们可以直接写成

     

    错误!未找到目录项。
    set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 2、收缩数据库
    --重建索引
    DBCC REINDEX
    DBCC INDEXDEFRAG
    --收缩数据和日志
    DBCC SHRINKDB
    DBCC SHRINKFILE

     

    3、压缩数据库
    dbcc shrinkdatabase(dbname)

     

    4、转移数据库给新用户以已存在用户权限
    exec sp_change_users_login 'update_one','newname','oldname'
    go

     

    5、检查备份集
    RESTORE VERIFYONLY from disk='E:\dvbbs.bak'

     

    6、修复数据库
    ALTER DATABASE [dvbbs] SET SINGLE_USER
    GO
    DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
    GO
    ALTER DATABASE [dvbbs] SET MULTI_USER
    GO

     

    7、日志清除
    SET NOCOUNT ON
    DECLARE @LogicalFileName sysname,
     @MaxMinutes INT,
     @NewSize INT

      

    USE tablename -- 要操作的数据库名
    SELECT  @LogicalFileName = 'tablename_log', -- 日志文件名
    @MaxMinutes = 10, -- Limit on time allowed to wrap log.
     @NewSize = 1  -- 你想设定的日志文件的大小(M)

     

    Setup / initialize
    DECLARE @OriginalSize int
    SELECT @OriginalSize = size 
     FROM sysfiles
     WHERE name = @LogicalFileName
    SELECT 'Original Size of ' + db_name() + ' LOG is ' + 
     CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 
     CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
     FROM sysfiles
     WHERE name = @LogicalFileName
    CREATE TABLE DummyTrans
     (DummyColumn char (8000) not null)

      

    DECLARE @Counter    INT,
     @StartTime DATETIME,
     @TruncLog   VARCHAR(255)
    SELECT @StartTime = GETDATE(),
     @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

     

    DBCC SHRINKFILE (@LogicalFileName, @NewSize)
    EXEC (@TruncLog)
    -- Wrap the log if necessary.
    WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
     AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  
     AND (@OriginalSize * 8 /1024) > @NewSize  
     BEGIN -- Outer loop.
    SELECT @Counter = 0
     WHILE   ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
     BEGIN -- update
     INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans
     SELECT @Counter = @Counter + 1
     END
     EXEC (@TruncLog)  
     END
    SELECT 'Final Size of ' + db_name() + ' LOG is ' +
     CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 
     CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
     FROM sysfiles 
     WHERE name = @LogicalFileName
    DROP TABLE DummyTrans
    SET NOCOUNT OFF

     

    8、说明:更改某个表
    exec sp_changeobjectowner 'tablename','dbo'

     

    9、存储更改全部表

     

    CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
    @OldOwner as NVARCHAR(128),
    @NewOwner as NVARCHAR(128)
    AS

     

    DECLARE @Name    as NVARCHAR(128)
    DECLARE @Owner   as NVARCHAR(128)
    DECLARE @OwnerName   as NVARCHAR(128)

     

    DECLARE curObject CURSOR FOR 
    select 'Name'    = name,
       'Owner'    = user_name(uid)
    from sysobjects
    where user_name(uid)=@OldOwner
    order by name

     

    OPEN   curObject
    FETCH NEXT FROM curObject INTO @Name, @Owner
    WHILE(@@FETCH_STATUS=0)
    BEGIN     
    if @Owner=@OldOwner 
    begin
       set @OwnerName = @OldOwner + '.' + rtrim(@Name)
       exec sp_changeobjectowner @OwnerName, @NewOwner
    end
    -- select @name,@NewOwner,@OldOwner

     

    FETCH NEXT FROM curObject INTO @Name, @Owner
    END

     

    close curObject
    deallocate curObject
    GO

      

    10、SQL SERVER中直接循环写入数据
    declare @i int
    set @i=1
    while @i<30
    begin
        insert into test (userid) values(@i)
        set @i=@i+1
    end
    案例:
    有如下表,要求就裱中所有沒有及格的成績,在每次增長0.1的基礎上,使他們剛好及格:
        Name     score
        Zhangshan   80
        Lishi       59
        Wangwu      50
        Songquan    69
    while((select min(score) from tb_table)<60)
    begin
    update tb_table set score =score*1.01
    where score<60
    if  (select min(score) from tb_table)>60
      break
     else
        continue
    end

      

    数据开发-经典sql语句

      

    1.按姓氏笔画排序:
    Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多

     

    2.数据库加密:
    select encrypt('原始密码')
    select pwdencrypt('原始密码')
    select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 encrypt('原始密码')
    select pwdencrypt('原始密码')
    select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同

     

    3.取回表中字段:
    declare @list varchar(1000),
    @sql nvarchar(1000) 
    select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'
    set @sql='select '+right(@list,len(@list)-1)+' from 表A' 
    exec (@sql)

     

    4.查看硬盘分区:
    EXEC master..xp_fixeddrives

     

    5.比较A,B表是否相等:
    if (select checksum_agg(binary_checksum(*)) from A)
         =
        (select checksum_agg(binary_checksum(*)) from B)
    print '相等'
    else
    print '不相等'

     

    6.杀掉所有的事件探察器进程:
    DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses
    WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
    EXEC sp_msforeach_worker '?'

     

    7.记录搜索:
    开头到N条记录
    Select Top N * From 表
    -------------------------------
    N到M条记录(要有主索引ID)
    Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID   Desc
    ----------------------------------
    N到结尾记录
    Select Top N * From 表 Order by ID Desc
    案例
    例如1:一张表有一万多条记录,表的第一个字段 RecID 是自增长字段, 写一个SQL语句, 找出表的第31到第40个记录。
     select top 10 recid from A where recid not  in(select top 30 recid from A)
    分析:如果这样写会产生某些问题,如果recid在表中存在逻辑索引。
        select top 10 recid from A where&hellip;&hellip;是从索引中查找,而后面的select top 30 recid from A则在数据表中查找,这样由于索引中的顺序有可能和数据表中的不一致,这样就导致查询到的不是本来的欲得到的数据。
    解决方案
    1, 用order by select top 30 recid from A order by ricid 如果该字段不是自增长,就会出现问题
    2, 在那个子查询中也加条件:select top 30 recid from A where recid>-1
    例2:查询表中的最后以条记录,并不知道这个表共有多少数据,以及表结构。
    set @s = 'select top 1 * from T   where pid not in (select top ' + str(@count-1) + ' pid  from  T)'
    print @s      exec  sp_executesql  @s
    9:获取当前数据库中的所有用户表
    select Name from sysobjects where xtype='u' and status>=0

     

    10:获取某一个表的所有字段
    select name from syscolumns where id=object_id('表名')

     

    select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名')

     

    两种方式的效果相同

     

    11:查看与某一个表相关的视图、存储过程、函数
    select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

     

    12:查看当前数据库中所有存储过程
    select name as 存储过程名称 from sysobjects where xtype='P'

     

    13:查询用户创建的所有数据库
    select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
    或者
    select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

     

    14:查询某一个表的字段和数据类型
    select column_name,data_type from information_schema.columns
    where table_name = '表名'

     

    15:不同服务器数据库之间的数据操作

     

    --创建链接服务器

     

    exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '

     

    exec sp_addlinkedsrvlogin  'ITSV ', 'false ',null, '用户名 ', '密码 '

     

    --查询示例

     

    select * from ITSV.数据库名.dbo.表名

     

    --导入示例

     

    select * into 表 from ITSV.数据库名.dbo.表名

     

    --以后不再使用时删除链接服务器

     

    exec sp_dropserver  'ITSV ', 'droplogins '

       

    --连接远程/局域网数据(openrowset/openquery/opendatasource)

     

    --1、openrowset

     

    --查询示例

     

    select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

     

    --生成本地表

     

    select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

       

    --把本地表导入远程表

     

    insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)

     

    select *from 本地表

     

    --更新本地表

     

    update b

     

    set b.列A=a.列A

     

     from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b

     

    on a.column1=b.column1

     

    --openquery用法需要创建一个连接

     

    --首先创建一个连接创建链接服务器

     

    exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '

     

    --查询

     

    select *

     

    FROM openquery(ITSV,  'SELECT *  FROM 数据库.dbo.表名 ')

     

    --把本地表导入远程表

     

    insert openquery(ITSV,  'SELECT *  FROM 数据库.dbo.表名 ')

     

    select * from 本地表

     

    --更新本地表

     

    update b

     

    set b.列B=a.列B

     

    FROM openquery(ITSV,  'SELECT * FROM 数据库.dbo.表名 ') as a 

     

    inner join 本地表 b on a.列A=b.列A

       

    --3、opendatasource/openrowset

     

    SELECT   *

     

    FROM   opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta

     

    --把本地表导入远程表

     

    insert opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名

     

    select * from 本地表 

     

    SQL Server基本函数

     

    SQL Server基本函数

     

    1.字符串函数 长度与分析用

     

    1,datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格
    2,substring(expression,start,length) 取子串,字符串的下标是从&ldquo;1&rdquo;,start为起始位置,length为字符串长度,实际应用中以len(expression)取得其长度
    3,right(char_expr,int_expr) 返回字符串右边第int_expr个字符,还用left于之相反
    4,isnull( check_expression , replacement_value )如果check_expression為空,則返回replacement_value的值,不為空,就返回check_expression字符操作类

     

    5,Sp_addtype 自定義數據類型
    例如:EXEC sp_addtype birthday, datetime, 'NULL'

     

    6,set nocount {on|off}使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据,则该设置由于大量减少了网络流量,因此可显著提高性能。SET NOCOUNT 设置是在执行或运行时设置,而不是在分析时设置。SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。

    转载本站文章《SQL语句大全中的sql语句备忘录—sql日常使用指北》,
    请注明出处:https://www.zhoulujun.cn/html/DB/sql/2015_0821_242.html