.Sql 插入语句得到自动生成的递增的ID值

Insert into Table(name,des,num) values(’ltp’,’thisisbest’,10);

Select @@identity as ‘Id’ 

.实现1或0想显示为男或女

Select name,sex=case sex when ‘1’ then ‘男’ when ‘0’ then ‘女’ end from tablename 

比较两个数取较大的数

select @maxPriority=case WHEN @otherGroupPri>@currentPri then @otherGroupPri else @currentPri end 

嵌套子查询

Select a,b,c from table1 where a in (select a from table2) 

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

Select a.title ,a.username,b.adddate from tablename a,(select max(adddate) adddate from tablename where tablename.tile=a.title) b 

.随机提取条记录的例子

Sql server:select top 10 * from tablename order by NewID();

Access：Select top 10 * from tablename order by Rnd(ID)

Rnd(ID) 其中ID是自动编号字段，可以利用其他任何数值来完成，比如姓名字段username

Select top 10 * from 表 order by rnd(len(username))

MySql:Select * from 表 order by Rnd() Limit 10 

在同一表内找相同属性的记录

Select userid from accounts_users where username is not null group by userid having count(*)>1 

查询类别所有产品对应的数据

Select categoryname,productname from categories left join products on categories.categoryid=products.categoryid; 

日程安排提前5分钟提醒

Select * from tabschedule where datediff(minute,getdate(),开始时间) <5       

得出某日期所在月份的最大天数

SELECT DAY(DATEADD(dd,-DAY('2008-02-13'),DATEADD(mm,1,'2008-02-13'))) AS 'daynumber' 

Sele cxt *　from tablename order by customername Collate Chinese_PRC_Stroke_ci_as //从少到多

 

.通配符的一些用法

1．Select * from tablename where column1 like ’[A-M]%’

选择出column字段中首字母在A-M之间的记录

2.select * from tablename where column1 like ’[ABC]%’

选择出column字段中首字母是A或B或C的记录

3．Select *　from tablename where column1 like ’[A-CG]%’

选出column字段中首字母在A-C之间或者是G的记录

4. select　* from tablename where column1 like  ’[^C]%’

选 出column字段中首字母不是C的记录

单字符like_

Select * from tablename where column1 like ’M_crosoft’ 

判定数据库中是否存在某张表，与判断表中是否存在某列

IF NOT exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'tempChangeStaffNo')   and OBJECTPROPERTY(id, N'IsUserTable') = 1)

 if not exists(select * from syscolumns where id =OBJECT_ID('tempChangeStaffNo') and name='Id') 

索引

if exists(select name from sysindexes where name = 'suoyin')

drop index temp.suoyin ---如果存在这个名字的索引，则删除这个索引

create nonclustered index suoyin_name on temp(Name)

--创建索引时正确的，查询的时候出了问题

--(2)查询索引

select * from temp with(index=suoyin_name) where Name = 'A'

--查看temp表的索引信息

sp_helpindex temp 

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

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 = '表名' 

 

SQL字段（列）操作：增加一个列(字段)：alter table 表名 add 字段名 type not null default 0Alter table table1 add username varchar(30) not null default ''ALTER TABLE tempChangeStaffNo ADD Id  UNIQUEIDENTIFIER NOT NULL加主键:Alter table tempChangeStaffNo ADD constraint PK_Id primary key (Id)或者alter table tempChangeStaffNo add  Id UNIQUEIDENTIFIER primary key 修改列（字段）修改字段类型：alter table 表名 alter column 字段名 type not null修改字段名：alter table 表名 rename column A to B或者：sp_rename 'tablename.avg_grade','avg_g','column';  --tablename是表名，avg_grade是原来的列名，avg_g是新的列名  exec sp_rename 'tablename.avg_grade','avg_g','column';  返回代码值0（成功）或非零数字（失败）eg：下例将表 customers 中的列 contact title 重命名为 title。 EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN' 修改字段默认值　　alter table 表名 add default (0) for 字段名 with values　　如果字段有默认值，则需要先删除字段的约束，在添加新的默认值，　　select c.name from sysconstraints a 　　inner join syscolumns b on a.colid=b.colid 　　inner join sysobjects c on a.constid=c.id　　where a.id=object_id('表名') 　　and b.name='字段名'　　根据约束名称删除约束　　alter table 表名 drop constraint 约束名　　根据表名向字段中增加新的默认值　　alter table 表名 add default (0) for 字段名 with valueseg:ALTER TABLE Fct_Order ADD  DEFAULT ((1)) FOR IsDisplyPrice;select c.name from sysconstraints a 　　inner join syscolumns b on a.colid=b.colid 　　inner join sysobjects c on a.constid=c.id　　where a.id=object_id('Fct_Order') 　　and b.name='IsDisplyPrice'alter table Fct_Order drop constraint DF__Fct_Order__IsDis__13DCE752删除列（字段）：alter table 表名 drop column 字段名

 

数据库加密:

select encrypt('原始密码')

select pwdencrypt('原始密码')

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

select pwdencrypt('原始密码')

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

查看硬盘分区:

EXEC master..xp_fixeddrives 

比较A,B表是否相等:

if (select checksum_agg(binary_checksum(*)) from A)

     =    (select checksum_agg(binary_checksum(*)) from B)

print '相等'

else

print '不相等' 

 重命名表

下例将表 customers 重命名为 custs。 

EXEC sp_rename 'customers', 'custs'

 

 查询字符串类型的字段不为空和为空

不为空的SQL

select * from TB_CMS_FLGTINFO_A t where (t.fsta is not null and t.fsta <>' ');

为空的SQL

select * from TB_CMS_FLGTINFO_A t where (t.fsta is null or t.fsta =' ');   

如果字段为null则设定默认值0，避免出现警告: 聚合或其他 SET 操作消除了 Null 值

select  max(isnull(id,0)) from @tab 

--将筛选列表的指定数据插入另一张表，插入时某些列固定值

INSERT INTO A (a, PhotoUrl, HealthConsultTd)

SELECT

'a'

,PhotoUrl

,HealthConsultTd

FROM

(SELECT

*

FROM HEALTH_CONSULT

WHERE DOCTOR_ID = 2042) B

 

--一次插入多个示例数据

INSERT INTO HEALTH_CONSULT (CUSTOMER_ID, IS_AUTOSEND)

VALUES (2052, 0), (2053, 1), (2054, 3)

  

--1 判断数据库是否存在if exists (select * from sys.databases where name = '数据库名')    drop database [数据库名] --2 判断表是否存在if exists (select * from sysobjects where id = object_id(N'[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)    drop table [表名] --3 判断存储过程是否存在if exists (select * from sysobjects where id = object_id(N'[存储过程名]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)    drop procedure [存储过程名]--4 判断临时表是否存在if object_id('tempdb..#临时表名') is not null      drop table #临时表名--5 判断视图是否存在 --判断是否存在'MyView52'这个试图IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'MyView52')PRINT '存在'elsePRINT '不存在'--6 判断函数是否存在 --  判断要创建的函数名是否存在      if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[函数名]') and xtype in (N'FN', N'IF', N'TF'))      drop function [dbo].[函数名] --7 获取用户创建的对象信息SELECT [name],[id],crdate FROM sysobjects where xtype='U'   /*  xtype 的表示参数类型，通常包括如下这些  C = CHECK 约束  D = 默认值或 DEFAULT 约束  F = FOREIGN KEY 约束  L = 日志  FN = 标量函数  IF = 内嵌表函数  P = 存储过程  PK = PRIMARY KEY 约束（类型是 K）  RF = 复制筛选存储过程  S = 系统表  TF = 表函数  TR = 触发器  U = 用户表  UQ = UNIQUE 约束（类型是 K）  V = 视图  X = 扩展存储过程  */ --8 判断列是否存在if exists(select * from syscolumns where id=object_id('表名') and name='列名')    alter table 表名 drop column 列名--9 判断列是否自增列if columnproperty(object_id('table'),'col','IsIdentity')=1    print '自增列'  else    print '不是自增列'  SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('表名')  AND is_identity=1--10 判断表中是否存在索引if exists(select * from sysindexes where id=object_id('表名') and name='索引名')      print  '存在'    else      print  '不存在'--11 查看数据库中对象SELECT * FROM sys.sysobjects WHERE name='对象名'  SELECT * FROM sys.sysobjects WHERE name='对象名'

 

--触发器-------------------------------------------------- USE AInfoLiangGOCREATE TRIGGER tri_t1ON t1          --作用在那张表上(不能是多表张)FOR INSERT    ---执行添加的操作时执行下面的sql语句AS    SELECT * FROM t1    SELECT * FROM t2     INSERT INTO t1 VALUES('99','99')  --当对表t1进行添加数据操作时，触发器则发触发

--视图----------------------------------------------------------USE AInfoLiangGOCREATE   VIEW vi_v1 AS    SELECT * FROM t1 SELECT * FROM vi_v1   -- 执行视图DROP VIEW vi_v1       --删除视图  --存储过程------------------------------------------------------USE AInfoLiangGOIF(EXISTS(SELECT * FROM sys.objects WHERE NAME='proc_1'))    DROP PROCEDURE proc_1GOCREATE PROCEDURE proc_1AS    SELECT  * FROM t1GOEXEC proc_1  --执行存储过程DROP PROCEDURE proc_1 --删除存储过程 --带参的存储过程USE AInfoLiangGOCREATE PROCEDURE proc_2(@start int,@end int) AS    SELECT * FROM t1 WHERE loginid BETWEEN @start AND @end EXEC proc_2    @start = 1, -- int    @end = 10 -- int
--带通配符的存储过程USE AInfoLiangGO ALTER  PROCEDURE proc_3(    @name VARCHAR(20)='%j%')AS    SELECT * FROM t1 WHERE loginname LIKE @name EXEC proc_3    @name = '%1%'   -- VARCHAR(20)  --执行带通配符的存储过程 --带输出参数存储过程---------------------------------------------USE AInfoLiangGOCREATE PROCEDURE proc_4(    @loginid INT,    @loginname VARCHAR(20) OUT,    @loginpwd  VARCHAR(20) OUTPUT)AS    SELECT * FROM t1 WHERE loginid=@loginid AND loginname=@loginname AND loginpwd=@loginpwd DECLARE @loginid=1 INT,@loginname VARCHAR(30),@loginpwd VARCHAR(20)SELECT @loginid=1;set @loginname = '11';SELECT  @loginpwd = '11'; EXEC proc_4 @loginid,@loginname OUT ,@loginpwd OUTPUTSELECT @loginname AS dd,@loginpwd AS ddd  --分页------------------------------------------------------USE AInfoLiangGOCREATE PROCEDURE  proc_5(    @start INT ,    @end INT)AS    SELECT * FROM    (        SELECT ROW_NUMBER() OVER(ORDER BY loginid ) AS rowid,* FROM t1      ) AS temp    WHERE temp.rowid BETWEEN @start AND @end     EXEC proc_5    @start = 2, -- INT    @end =  -- INT     --分页2---------------------------------------- USE AInfoLiangGOCREATE PROCEDURE proc_fen2(    @pageindex INT,--起始页    @pagesize INT  -- 一页多少个)   AS         DECLARE  @startrow INT ,@endrow INT    SET @startrow=(@pageindex-1)* @pagesize +1  --得到 从那一行的数据开始    SET @endrow =@startrow+@pagesize-1             --得到  结束的行那一条数据    SELECT * FROM    (        SELECT ROW_NUMBER() OVER(ORDER BY loginid) rowid,* FROM t1    ) temp    WHERE temp.rowid BETWEEN @startrow AND @endrow      EXEC proc_fen2    @pageindex = 5, -- INT    @pagesize = 2 -- INT     ---自定义函数--定义函数分为二种，一种是标量值函数，另一种是表格值函数--表格函数又可分为内联表格值函数和多句表格值函数 --1 标量函数USE AInfoLiangGOIF(EXISTS(SELECT * FROM sys.objects WHERE TYPE='fn' AND NAME='fun_1'))BEGIN    DROP FUNCTION fun_1ENDGO CREATE FUNCTION fun_1(    @loginid INT)RETURNS VARCHAR(40)  --返回值类型    ASBEGIN    DECLARE @loginname VARCHAR (40)    SELECT @loginname=loginname FROM t1 WHERE loginid=@loginid    RETURN @loginname END     SELECT dbo.fun_1(1)   --执行函数 --表格值（内联函数）---------------------------------------------- USE AInfoLiangGOIF(EXISTS(SELECT * FROM sys.objects WHERE TYPE='fn' AND NAME='fun_3'))BEGIN    DROP FUNCTION fun_3ENDGOCREATE FUNCTION fun_3(    @loginid INT)RETURNS TABLEAS    RETURN (SELECT * FROM t1 WHERE loginid=@loginid)     SELECT dbo.fun_2(1)  --表格值（多语句函数）--------------------------------------------------USE AInfoLiangGOIF(EXISTS(SELECT * FROM sys.objects WHERE NAME ='fun_4')) DROP FUNCTION fun_4GOCREATE FUNCTION fun_4(    @loginid INT)RETURNS @t1 TABLE(a VARCHAR(20),b VARCHAR (20))AS    BEGIN        INSERT @t1        SELECT loginname ,loginpwd FROM dbo.t1 WHERE loginid=@loginid        RETURN    END     SELECT dbo.fun_4(1)

 

----添加IDENTITY列alter table tablename add id int identity(1,1)----设置IDENTITY列为主键alter table tablename add constraint [PK_tablename] PRIMARY KEY CLUSTERED ([id])--添加主键ALTER TABLE ForbiddenType ADD CONSTRAINT [PK_ForbiddenType] PRIMARY KEY CLUSTERED([Id]) --修改主键if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[主键名]') and OBJECTPROPERTY(object_id(N'[列名]'), N'IsPrimaryKey') = 1)ALTER  TABLE 表名 DROP  CONSTRAINT [主键名]ALTER  TABLE 表名 ADD   CONSTRAINT [新主键名] PRIMARY   KEY  ([列名])if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PK_orders]') and OBJECTPROPERTY(OBJECT_ID(N'dbo.[orders].[pi_no]'), N'IsPrimaryKey') = 1)ALTER  TABLE orders DROP  CONSTRAINT [PK_orders]ALTER  TABLE orders ADD   CONSTRAINT [PK_orders] PRIMARY KEY  ([form_no]) ON  [PRIMARY]

 设置bit类型字段

[IsPublic] [BIT] NOT NULL DEFAULT ((1))

[LearningAbility] [DECIMAL](19, 5) NULL, --五位小数

[BaseSalary] [DECIMAL](18, 2) NULL, --二位小数