精华内容
下载资源
问答
  • 经典sql语句大全
    千次阅读
    2020-12-25 20:56:44

    下列语句部分是Mssql语句,不可以在access中使用。
      SQL分类:
      DDL—数据定义语言(CREATE,ALTER,DROP,DECLARE)
      DML—数据操纵语言(SELECT,DELETE,UPDATE,INSERT)
      DCL—数据控制语言(GRANT,REVOKE,COMMIT,ROLLBACK)
      首先,简要介绍基础语句:
      1、说明:创建数据库
    CREATE DATABASE database-name
      2、说明:删除数据库
    drop database dbname
      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
      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)
      删除:delete from table1 where 范围
      更新:update table1 set field1=value1 where 范围
      查找:select * from table1 where field1 like ’%value1%’ —like的语法很精妙,查资料!
      排序: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
      11、说明:几个高级查询运算词
      A: UNION 运算符
      UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
      B: EXCEPT 运算符
      EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。
      C: INTERSECT 运算符
      INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。
      注:使用运算词的几个查询结果行必须是一致的。
      12、说明:使用外连接
      A、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
      B:right outer join:
      右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。
      C:full outer join:
      全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。
      其次,大家来看一些不错的sql语句
      1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用)
      法一:select * into b from a where 1<>1
      法二: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 ‘具体数据库’ 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 (‘值1’,’值2’,’值4’,’值6’)
      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.排序字段
      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、说明:删除重复记录
    Delete from tablename where id not in (select max(id) from tablename group by col1,col2,…)
      20、说明:列出数据库里所有的表名
    select name from sysobjects where type=’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
    随机选择数据库记录的方法(使用Randomize函数,通过SQL语句实现)
      对存储在数据库中的数据来说,随机数特性能给出上面的效果,但它们可能太慢了些。你不能要求ASP“找个随机数”然后打印出来。实际上常见的解决方案是建立如下所示的循环:
    Randomize
    RNumber = Int(Rnd499) +1
     
    While Not objRec.EOF
    If objRec(“ID”) = RNumber THEN
    … 这里是执行脚本 …
    end if
    objRec.MoveNext
    Wend
      这很容易理解。首先,你取出1到500范围之内的一个随机数(假设500就是数据库内记录的总数)。然后,你遍历每一记录来测试ID 的值、检查其是否匹配RNumber。满足条件的话就执行由THEN 关键字开始的那一块代码。假如你的RNumber 等于495,那么要循环一遍数据库花的时间可就长了。虽然500这个数字看起来大了些,但相比更为稳固的企业解决方案这还是个小型数据库了,后者通常在一个数据库内就包含了成千上万条记录。这时候不就死定了?
      采用SQL,你就可以很快地找出准确的记录并且打开一个只包含该记录的recordset,如下所示:
    Randomize
    RNumber = Int(Rnd499) + 1
     
    SQL = "SELECT * FROM Customers WHERE ID = " & RNumber
     
    set objRec = ObjConn.Execute(SQL)
    Response.WriteRNumber & " = " & objRec(“ID”) & " " & objRec(“c_email”)
      不必写出RNumber 和ID,你只需要检查匹配情况即可。只要你对以上代码的工作满意,你自可按需操作“随机”记录。Recordset没有包含其他内容,因此你很快就能找到你需要的记录这样就大大降低了处理时间。
    再谈随机数
      现在你下定决心要榨干Random 函数的最后一滴油,那么你可能会一次取出多条随机记录或者想采用一定随机范围内的记录。把上面的标准Random 示例扩展一下就可以用SQL应对上面两种情况了。
      为了取出几条随机选择的记录并存放在同一recordset内,你可以存储三个随机数,然后查询数据库获得匹配这些数字的记录:
      SQL = "SELECT * FROM Customers WHERE ID = " & RNumber & " OR ID = " & RNumber2 & " OR ID = " & RNumber3
      假如你想选出10条记录(也许是每次页面装载时的10条链接的列表),你可以用BETWEEN 或者数学等式选出第一条记录和适当数量的递增记录。这一操作可以通过好几种方式来完成,但是 SELECT 语句只显示一种可能(这里的ID 是自动生成的号码):
    SQL = "SELECT * FROM Customers WHERE ID BETWEEN " & RNumber & " AND " & RNumber & “+ 9”
      注意:以上代码的执行目的不是检查数据库内是否有9条并发记录。
      随机读取若干条记录,测试过
    Access语法:SELECT top 10 * From 表名 ORDER BY Rnd(id)
    Sql server:select top n * from 表名 order by newid()
    mysql select * From 表名 Order By rand() Limit n
      Access左连接语法(最近开发要用左连接,Access帮助什么都没有,网上没有Access的SQL说明,只有自己测试, 现在记下以备后查)
      语法 select table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where …
      使用SQL语句 用…代替过长的字符串显示
      语法:
      SQL数据库:select case when len(field)>10 then left(field,10)+’…’ else field end as news_name,news_id from tablename
      Access数据库:SELECT iif(len(field)>2,left(field,2)+’…’,field) FROM tablename;
      Conn.Execute说明
      Execute方法
      该方法用于执行SQL语句。根据SQL语句执行后是否返回记录集,该方法的使用格式分为以下两种:
      1.执行SQL查询语句时,将返回查询得到的记录集。用法为:
      Set 对象变量名=连接对象.Execute(“SQL 查询语言”)
      Execute方法调用后,会自动创建记录集对象,并将查询结果存储在该记录对象中,通过Set方法,将记录集赋给指定的对象保存,以后对象变量就代表了该记录集对象。
      2.执行SQL的操作性语言时,没有记录集的返回。此时用法为:
      连接对象.Execute “SQL 操作性语句” [, RecordAffected][, Option]
      ·RecordAffected 为可选项,此出可放置一个变量,SQL语句执行后,所生效的记录数会自动保存到该变量中。通过访问该变量,就可知道SQL语句队多少条记录进行了操作。
      ·Option 可选项,该参数的取值通常为adCMDText,它用于告诉ADO,应该将Execute方法之后的第一个字符解释为命令文本。通过指定该参数,可使执行更高效。
      ·BeginTrans、RollbackTrans、CommitTrans方法
      这三个方法是连接对象提供的用于事务处理的方法。BeginTrans用于开始一个事物;RollbackTrans用于回滚事务;CommitTrans用于提交所有的事务处理结果,即确认事务的处理。
      事务处理可以将一组操作视为一个整体,只有全部语句都成功执行后,事务处理才算成功;若其中有一个语句执行失败,则整个处理就算失败,并恢复到处里前的状态。
      BeginTrans和CommitTrans用于标记事务的开始和结束,在这两个之间的语句,就是作为事务处理的语句。判断事务处理是否成功,可通过连接对象的Error集合来实现,若Error集合的成员个数不为0,则说明有错误发生,事务处理失败。Error集合中的每一个Error对象,代表一个错误信息。
    SQL语句大全精要
    2006/10/26 13:46
    DELETE语句
    DELETE语句:用于创建一个删除查询,可从列在 FROM 子句之中的一个或多个表中删除记录,且该子句满足 WHERE 子句中的条件,可以使用DELETE删除多个记录。
    语法:DELETE [table.*] FROM table WHERE criteria
    语法:DELETE * FROM table WHERE criteria=’查询的字’
    说明:table参数用于指定从其中删除记录的表的名称。
    criteria参数为一个表达式,用于指定哪些记录应该被删除的表达式。
    可以使用 Execute 方法与一个 DROP 语句从数据库中放弃整个表。不过,若用这种方法删除表,将会失去表的结构。不同的是当使用 DELETE,只有数据会被删除;表的结构以及表的所有属性仍然保留,例如字段属性及索引。
    UPDATE
    有关UPDATE,急!!!!!!!!!!!
    在ORACLE数据库中
    表 A ( ID ,FIRSTNAME,LASTNAME )
    表 B( ID,LASTNAME)
    表 A 中原来ID,FIRSTNAME两个字段的数据是完整的
    表 B中原来ID,LASTNAME两个字段的数据是完整的
    现在要把表 B中的LASTNAME字段的相应的数据填入到A表中LASTNAME相应的位置。两个表中的ID字段是相互关联的。
    先谢谢了!!!
    update a set a.lastname=(select b.lastname from b where a.id=b.id)
      掌握SQL四条最基本的数据操作语句:Insert,Select,Update和Delete。
      练掌握SQL是数据库用户的宝贵财富。在本文中,我们将引导你掌握四条最基本的数据操作语句—SQL的核心功能—来依次介绍比较操作符、选择断言以及三值逻辑。当你完成这些学习后,显然你已经开始算是精通SQL了。
      在我们开始之前,先使用CREATE TABLE语句来创建一个表(如图1所示)。DDL语句对数据库对象如表、列和视进行定义。它们并不对表中的行进行处理,这是因为DDL语句并不处理数据库中实际的数据。这些工作由另一类SQL语句—数据操作语言(DML)语句进行处理。
      SQL中有四种基本的DML操作:INSERT,SELECT,UPDATE和DELETE。由于这是大多数SQL用户经常用到的,我们有必要在此对它们进行一一说明。在图1中我们给出了一个名为EMPLOYEES的表。其中的每一行对应一个特定的雇员记录。请熟悉这张表,我们在后面的例子中将要用到它。

    The Execute method executes a specified query, SQL statement, stored procedure, or provider-specific text.
    Execute的作用是:执行一个查询语句、陈述语句、程序或技术提供对象[provider]的详细文本。

    The results are stored in a new Recordset object if it is a row-returning query. A closed Recordset object will be returned if it is not a row-returning query.
    如果返回行[row-returning]查询语句,那么结果将被存储在一个新的记录对象中;如果它不是一个返回行[row-returning]查询语句,那么它将返回一个关闭的记录对象。

    Note: The returned Recordset is always a read-only, forward-only Recordset!
    注意:返回的Recordset是一个只读的、只向前兼容的Recordset。

    Tip: To create a Recordset with more functionality, first create a Recordset object. Set the desired properties, and then use the Recordset object’s Open method to execute the query.
    提示:在第一次创建Recordset对象时,需要将它创建为一个更具功能性的Recordset对象。设置一个我们所希望的属性,使用Recordset对象的Open方法去执行查询语句。
    Syntax for row-returning
    row-returning[返回行]语法
    |Set objrs=objconn.Execute(commandtext,ra,options)|
    Syntax for non-row-returning
    non-row-returning[非返回行]语法
    |–Syntax for non-row-returning
    non-row-returning[非返回行]语法|
    |objconn.Execute commandtext,ra,options|
    | Parameter参数 | Description描述 |
    |-commandtext

    -|-Required. The SQL statement, stored procedure, or provider-specific text to execute
    必要参数。指定需要执行的SQL语句,现存的程序或技术提供对象[provider]的详细文本-|
    | ra | Optional. Sets how the provider should evaluate the commandtext parameter. Can be one or more CommandTypeEnum or ExecuteOptionEnum values. Default is adCmdUnspecified
    可选参数。设置技术提供对象[provider]应该如何评估CommandText属性的功能。它可以是一个或多个CommandTypeEnum 或 ExecuteOptionEnum的值。默认值是adCmdUnspecified |
    Example
    案例
    | <%

    sql=“SELECT companyname FROM Customers”

    Set rs=conn.Execute(sql)

    %> |
    转自

    更多相关内容
  • 经典SQL语句大全

    万次阅读 多人点赞 2016-10-01 13:59:02
    SQL语句参考,包含Access、MySQL 以及 SQL Server基础创建数据库CREATE DATABASE database-name 删除数据库drop database dbname 备份sql server 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', ...

    SQL语句参考,包含Access、MySQL 以及 SQL Server


    基础

    创建数据库

    CREATE DATABASE database-name 
    

    删除数据库

    drop database dbname
    

    备份sql server

    1. 创建 备份数据的 device

       USE master
      EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
      
    2. 开始 备份

      BACKUP DATABASE pubs TO testBack 
      

    创建新表

    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
    

    删除新表

    drop table tabname 
    

    增加一个列

    Alter table tabname add column col type
    
    注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。
    

    添加主键

    Alter table tabname add primary key(col)
    

    删除主键

    Alter table tabname drop primary key(col)
    

    创建索引

    create [unique] index idxname on tabname(col….)
    

    删除索引

    drop index idxname
    
    注:索引是不可更改的,想更改必须删除重新建。
    

    创建视图

    create view viewname as select statement
    

    删除视图

    drop view viewname
    

    几个简单的基本的sql语句

    选择:select * from table1 where 范围
    插入:insert into table1(field1,field2) values(value1,value2)
    删除:delete from table1 where 范围
    更新:update table1 set field1=value1 where 范围
    查找:select * from table1 where field1 like ’%value1%’ ---like的语法很精妙,查资料!
    排序: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

    一张表,一旦分组 完成后,查询后只能得到组相关的信息。
    组相关的信息:(统计信息) count,sum,max,min,avg 分组的标准)
    在SQLServer中分组时:不能以text,ntext,image类型的字段作为分组依据
    在selecte统计函数中的字段,不能和普通的字段放在一起;

    对数据库进行操作

    分离数据库: sp_detach_db;
    附加数据库:sp_attach_db 后接表明,附加需要完整的路径名

    如何修改数据库的名称

    sp_renamedb 'old_name', 'new_name'
    

    提升

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

    • select * into b from a where 1<>1(仅用于SQlServer)
    • select top 0 * into b from a

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

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

    跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)

    insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
    

    子查询(表名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)
    

    显示文章、提交人和最后回复时间

    select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
    

    外连接查询(表名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
    

    在线视图查询(表名1:a )

    select * from (SELECT a,b,c FROM a) T where t.a > 1;
    

    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
    

    in 的使用方法

    select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
    

    两张关联表,删除主表中已经在副表中没有的信息

    delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
    

    四表联查问题

    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 .....
    

    日程安排提前五分钟提醒

    SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
    

    一条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=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’
        
        exec sp_executesql @sql
        

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

    前10条记录

    select top 10 * form table1 where 范围
    

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

    select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
    

    包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表

    (select a from tableA ) except (select a from tableB) except (select a from tableC)
    

    随机取出10条数据

    select top 10 * from tablename order by newid()
    

    随机选择记录

    select newid()
    

    删除重复记录

    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
    

    列出数据库里所有的表名

    select name from sysobjects where type='U' // U代表用户
    

    列出表里的所有的列名

    select name from syscolumns where id=object_id('TableName')
    

    列示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

    初始化表table1

    TRUNCATE TABLE table1
    

    选择从10到15的记录

    select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
    

    技巧

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

    “where 1=1” 是表示选择全部 “where 1=2”全部不选,

    如:

    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 and '+ @strWhere
    

    收缩数据库

    --重建索引
        DBCC REINDEX
        DBCC INDEXDEFRAG
    --收缩数据和日志
        DBCC SHRINKDB
        DBCC SHRINKFILE
    

    压缩数据库

    dbcc shrinkdatabase(dbname)
    

    转移数据库给新用户以已存在用户权限

    exec sp_change_users_login 'update_one','newname','oldname'
    go
    

    检查备份集

    RESTORE VERIFYONLY from disk='E:\dvbbs.bak'
    

    修复数据库

    ALTER DATABASE [dvbbs] SET SINGLE_USER
    GO
    DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
    GO
    ALTER DATABASE [dvbbs] SET MULTI_USER
    GO
    

    日志清除

    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
    

    更改某个表

    exec sp_changeobjectowner 'tablename','dbo'
    

    存储更改全部表

    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
    

    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
    

    数据开发-经典

    按姓氏笔画排序

    Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多
    

    数据库加密

    select encrypt('原始密码')
    select pwdencrypt('原始密码')
    select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 encrypt('原始密码')
    select pwdencrypt('原始密码')
    select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同
    

    取回表中字段

    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)
    

    查看硬盘分区

    EXEC master..xp_fixeddrives
    

    比较A,B表是否相等

    if (select checksum_agg(binary_checksum(*)) from A)
        =
       (select checksum_agg(binary_checksum(*)) from B)
        print '相等'
    else
        print '不相等'
    

    杀掉所有的事件探察器进程

    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 '?'
    

    记录搜索

    • 开头到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……是从索引中查找,而后面的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

    获取当前数据库中的所有用户表

    select Name from sysobjects where xtype='u' and status>=0
    

    获取某一个表的所有字段

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

    两种方式的效果相同

    查看与某一个表相关的视图、存储过程、函数

    select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
    

    查看当前数据库中所有存储过程

    select name as 存储过程名称 from sysobjects where xtype='P'
    

    查询用户创建的所有数据库

    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
    

    查询某一个表的字段和数据类型

    select column_name,data_type from information_schema.columns
    where table_name = '表名'
    

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

    --创建链接服务器
        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基本函数

    字符串函数

    长度与分析用

    • datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格
    • substring(expression,start,length) 取子串,字符串的下标是从“1”,start为起始位置,length为字符串长度,实际应用中以len(expression)取得其长度
    • right(char_expr,int_expr) 返回字符串右边第int_expr个字符,还用left于之相反
    • isnull( check_expression , replacement_value )如果check_expression為空,則返回replacement_value的值,不為空,就返回check_expression字符操作类
    • Sp_addtype 自定義數據類型
      例如:EXEC sp_addtype birthday, datetime, ‘NULL’
    • set nocount {on|off}
      使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据,则该设置由于大量减少了网络流量,因此可显著提高性能。SET NOCOUNT 设置是在执行或运行时设置,而不是在分析时设置。
      SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。
      SET NOCOUNT 为 OFF 时,返回计数

    常识

    • 在SQL查询中:from后最多可以跟多少张表或视图:256
    • 在SQL语句中出现 Order by,查询时,先排序,后取
    • 在SQL中,一个字段的最大容量是8000,而对于nvarchar(4000),由于nvarchar是Unicode码。
    展开全文
  • 经典sql语句大全经典sql语句大全经典sql语句大全经典sql语句大全经典sql语句大全经典sql语句大全经典sql语句大全经典sql语句大全经典sql语句大全经典sql语句大全经典sql语句大全
  • 经典SQL语句大全.rar

    2021-11-16 14:29:19
    经典SQL语句大全.rar
  • 经典SQL语句大全(绝对的经典)适用SQL Sever
  • 经典SQL语句大全经典SQL语句大全经典SQL语句大全经典SQL语句大全经典SQL语句大全
  • 经典SQL语句大全 含注解示例
  • 经典sql语句大全

    2013-09-03 16:56:06
    该资源包括了一些基本的省sql语句操作,以及一些比较难以理解的sql语句讲解,是一份不可多得的sql语句文档
  • 经典SQL语句大全----经典SQL语句大全 经典SQL语句大全---经典SQL语句大全
  • 经典SQL语句大全 - 数据库 经典SQL语句大全 - 数据库 经典SQL语句大全 - 数据库 经典SQL语句大全 - 数据库
  • 多种对数据库操作的Sql语句供你参考和实用,有基础部分 提升和技巧等模块 和相关的复杂操作
  • 经典SQL语句大全。。。。。。。。。.。。。
  • 经典SQL语句大全.chm

    2010-09-02 09:14:28
    经典SQL语句大全(全面提升你的SQL水平) 适用于初学者与中等水平的人..
  • 2012-经典SQL语句大全-数据库.zip;[本站资源全部免费];2012年最新整理;2012最新...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,894,980
精华内容 757,992
关键字:

经典sql语句大全

友情链接: 36.zip