常用SQL语句汇总整理_01
1．SQL 插入语句得到自动生成的递增ID 值
insert into Table1(Name,des,num) values ('ltp','thisisbest',10);
select @@identity as 'Id'
2．实现是1 或0 想显示为男或女
select name,Sex=(case Sex when '1' then '男' when '0' then '女' end) from Tablename
3．嵌套子查询
select a,b,c from Table1 where a IN (select a from Table2)
4．显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from tablename a,(select max(adddate) adddate from tablename where tablename.title=a.title) b
5．随机提取条记录的例子
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 Rand() Limit 10
6．在同一表内找相同属性的记录
select UserID from Accounts_Users where UserName is not null group by UserID having count (*)>1
7．查询类别所有的产品对应数据
SELECT CategoryName,ProductName FROM Categories LEFT JOIN Products ON Categories.CategoryID = Products.CategoryID;
8．按范围查询编号在2 到5 之间的用户信息
select * from UserValue where UserID between 2 and 5;
9．日程安排提前5 分钟提醒
Select * from TabSchedule where datediff(minute,getdate(),开始时间)<5
10．得出某日期所在月份的最大天数
SELECT DAY(DATEADD(dd, -DAY('2008-02-13'),DATEADD(mm, 1, '2008-02-13'))) AS 'DayNumber'
11．按姓氏笔画排序
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
12．通配符的一些用法
- select * from tablename where column1 like '[A-M]%' 这样可以选择出column 字段中首字母在A-M 之间的记录
- select * from tablename where column1 like '[ABC]%' 这样可以选择出column 字段中首字母是A 或者B 或者C 的记录
- select * from tablename where column1 like '[A-CG]%' 这样可以选择出column 字段中首字母在A-C 之间的或者是G 的记录
- select * from tablename where column1 like '[^C]%' 这样可以选择出column 字段中首字母不是C 的记录
13．复制表结构（只复制结构,源表名：a，目标表名：b）
select * into b from a where 1<>1 或 select top 0 * into [b] from [a]
14．复制表数据（复制数据，源表名：a，目标表名：b）
insert into b(Name,des,num) select Name,des,num from Table1;
15. 创建分页存储过程
--创建分页的存储过程
create proc usp_GetPagedData
@pageIndex int, --页码
@pageSize int, --每页显示几条
@pageCount int output --共多少页
as
declare @count int
select @count = count(*) from TableName
set @pageCount = ceiling(@count*1.0/@pageSize)
select * from
(select *,ROW_NUMBER() over(order by ColumnName desc) as num
from TableName) as t
where num between (@pageIndex-1)*@pageSize + 1 and @pageIndex*@pageSize
--测试存储过程
declare @n int
exec usp_GetPagedData 3,4,@n output
print @n
16.增加数据的方法
insert into 表名（列1，列2） select 列1，列2 from 表名；
select 列 into 新表名 from 旧表名 where 条件
select 列 into 新表名 from 旧表名 where 1>2; 复制空表结构，constraint不会被复制
17.删除数据的方法
truncate 与 delete 的区别；truncate 删除后，自动增长的id不会从1开始，操作不会写入log日志中。
18.为年龄增加一个检查约束 年龄必须在0-120岁之间 包括0岁和120岁
alter table EMPLOYEE add constraint CK_EMPLOYEE_EMP_AGE check(EMP_AGE>=0 and EMP_AGE<=120)
19为性别增加一个默认约束，默认为'男'
alter table EMPLOYEE add constraint DF_EMPLOYEE_EMP_GENDER default('男') for EMP_GENDER
20用一条语句为表增加多个约束
alter table EMPLOYEE add
constraint PK_EMPLOYEE_EMP_ID primary key(EMP_ID)
constraint UQ_EMPLOYEE_EMP_NAME unique(EMP_NAME)
constraint CK_EMPLOYEE_EMP_AGE check(EMP_AGE<=0 and EMP_AGE>=120)
21列出EMPLOYEES表中各部门的部门号，最高工资，最低工资
select max(SALARY) as 最高工资,min(SALARY) as 最低工资,DEPARTMENT_ID from EMPLOYEES group by DEPARTMENT_ID;
22列出EMPLOYEES表中各部门EMPLOYEE_JOB为'CLERK'的员工的最低工资，最高工资
select max(SALARY) as 最高工资,min(SALARY) as 最低工资,DEPARTMENT_ID as 部门号 from EMPLOYEES where EMPLOYEE_JOB = 'CLERK' group by DEPARTMENT_ID;
23对于EMPLOYEES中最低工资小于1000的部门，列出EMPLOYEE_JOB为'CLERK'的员工的部门号，最低工资，最高工资
select max(SALARY) as 最高工资,min(SALARY) as 最低工资,DEPARTMENT_ID as 部门号 from EMPLOYEESas b
where EMPLOYEE_JOB ='CLERK' and 1000>(select min(SALARY) from EMPLOYEES as a where a.DEPARTMENT_ID = b.DEPARTMENT_ID) group by b.DEPARTMENT_ID
24根据部门号由高而低，工资有低而高列出每个员工的姓名，部门号，工资
select DEPARTMENT_ID as 部门号,EMPLOYEE_NAME as 姓名,SALARY as 工资 from EMPLOYEES order by DEPARTMENT_ID desc,SALARY asc
25列出'张三'所在部门中每个员工的姓名与部门号
select EMPLOYEE_NAME,DEPARTMENT_ID from EMPLOYEES where DEPARTMENT_ID = (select DEPARTMENT_ID from EMPLOYEES where EMPLOYEE_NAME = '张三')
26列出每个员工的姓名，工作，部门号，部门名
select EMPLOYEE_NAME,EMPLOYEE_JOB,EMPLOYEES.DEPARTMENT_ID,DEPARTMENTS.DEPARTMENT_NAME from EMPLOYEES,DEPARTMENTS where EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
27列出EMPLOYEES中工作为'CLERK'的员工的姓名，工作，部门号，部门名
select EMPLOYEE_NAME,EMPLOYEE_JOB,DEPARTMENTS.DEPARTMENT_ID,DEPARTMENT_NAME from EMPLOYEES,DEPARTMENTS where DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID andDEPARTMENT_JOB = 'CLERK'
28对于EMPLOYEES中有管理者的员工，列出姓名，管理者姓名（管理者外键为MANAGER）
select a.EMPLOYEE_NAME as 姓名,b.EMPLOYEE_NAME as 管理者 from EMPLOYEES as a,EMPLOYEES as b where a.MANAGER is not null and a.MANAGER = b.EMPLOYEE_ID
29对于DEPARTMENTS表中，列出所有部门名，部门号，同时列出各部门工作为'CLERK'的员工名与工作
select DEPARTMENT_NAME as 部门名,DEPARTMENTS.DEPARTMENT_ID as 部门号,EMPLOYEE_NAME as 员工名,EMPLOYEE_JOB as 工作 from DEPARTMENTS,EMPLOYEES
where DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID and EMPLOYEE_JOB = 'CLERK'
30对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序
select a.DEPARTMENT_ID as 部门号,a.EMPLOYEE_NAME as 姓名,a.SALARY as 工资 from EMPLOYEES as a
where a.SALARY>(select avg(SALARY) from EMPLOYEES as b where a.DEPARTMENT_ID = b.DEPARTMENT_ID) order by a.DEPARTMENT_ID
31对于EMPLOYEES，列出各个部门中平均工资高于本部门平均水平的员工数和部门号，按部门号排序
select count(a.SALARY) as 员工数,a.DEPARTMENT_ID as 部门号 from EMPLOYEES as a
where a.SALARY>(select avg(SALARY) from EMPLOYEES as b where a.DEPARTMENT_ID = b.DEPARTMENT_ID) group by a.DEPARTMENT_ID order by a.DEPARTMENT_ID
32对于EMPLOYEES中工资高于本部门平均水平，人数多与1人的，列出部门号，人数，按部门号排序
select count(a.EMPLOYEE_ID) as 员工数,a.DEPARTMENT_ID as 部门号,avg(SALARY) as 平均工资 from EMPLOYEES as a
where (select count(c.EMPLOYEE_ID) from EMPLOYEES as c where c.DEPARTMENT_ID = a.DEPARTMENT_ID and c.SALARY>(select avg(SALARY) from EMPLOYEES as b where c.DEPARTMENT_ID = b.DEPARTMENT_ID))>1 group by a.DEPARTMENT_ID order by a.DEPARTMENT_ID
33对于EMPLOYEES中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数
select a.DEPARTMENT_ID,a.EMPLOYEE_NAME,a.SALARY,(select count(b.EMPLOYEE_NAME) from EMPLOYEES as b where b.SALARY < a.SALARY) as 人数 from EMPLOYEES as a
where (select count(b.EMPLOYEE_NAME) from EMPLOYEES as b where b.SALARY<a.SALARY)>5
34补全列的位数
方法1: select right('0000'+cast(id as varchar),4) as idx from items
方法2：select REPLICATE('0',4-len(id))+convert(varchar,id) as idx from items
