经典SQL语句
1.Create database database_name --创建数据库
On primary --创建主数据文件:mdf
（name=‘name1’，
Filename=‘磁盘名:\文件夹名\name1.mdf’,
Size=,
Maxsize=,
Filegrowth=）,
(name=‘name2’，--创建次要数据文件（可以创建多个次要数据文件）:ndf
Filename=‘磁盘名:\文件夹名\name2.ndf’,
Size=,
Maxsize=,
Filegrowth=)
Log on --创建日志文件（可以创建多个日志文件）：ldf
(name=‘name3’，
Filename=‘磁盘名:\文件夹名\name3.ldf’,
Size=,
Maxsize=,
Filegrowth=
)
注意：多个次要数据文件和日志文件之间用‘，’隔开。主要文件，次要文件，日志文件可以放在不同的磁盘里
2.创建表
Create table table_name（
列名1 类型，
列名2 类型，
列名3 类型
）
注意：
1．创建外键时该列不能为空，一个表内只能有一个主键，可以有多唯一约束
2．创建联合主键（只能创建表级约束）
Foreign key (列名1，列名2)
3.修改表示添加列、列约束、修改约束、删除约束、删除列、删除表
Alter table table_name
[add column_name date_type]
[add constraint_name for column_name]
[alter column column_name new_data_type]
[drop column column_name | drop constraint constraint_name]
4.DMLl语句（insert，update，delete）
Insert into (列名1，列名2，列名3.··)values （值1，值2，值3，···）
Update 表名set 列名=exprestion where 条件语句
Delete form title_name where 条件语句
5.
(1)查看表结构：exec sp_help 表名
(2)查看表内容：select *（列名1，列名2，。。）from 表名where 。。。。
(3)删除表中的某列：delete column_name from table_name where ….
(4)删除表中的所有内容：delete from table_name | truncate table table_name
(5)删除表：drop table table_name
(6)删除数据库：drop database database_name
6.简单查询
(1)最简单的查询语句
(2)使用top子句select[ top n | top n percent ] * from title_name (不能丢掉‘*‘)
(3)查询结果排序select column from title_name order by[asc 或者desc(降序)]
(4)带条件的查询，使用算术表达式，使用逻辑表达式，使用between 关键字
(5)使用in 关键字(in 等价于or：column_name=表达式1 or column_name=表达式2。。。)
Select column_name…from tilte_name where column_name [not] in (表达式1，表达式2，表达式3。。。) (6)模糊查询（通配符有：%，_，[ ]，[^]）Select column_name…from title_name where 表达式[not] like…
特例：查询第几(3)个字为xx的信息
Select *from title_name where name like’—xx%’
特例：查询值不以x打头但必须包含x的所有记录
Select *from title_name where column_name like ‘[^x]%x%’
特例：查询满足前两个字符为aa 第三个字符为abc中的一个第四个字符为下划线（-）
Select *from title_name where column_name like ‘aa[abc]_’
(7)使用聚合函数，使用分组查询，distinct 关键字
(8)having子句
Select column_name from title_name group by column_name having 表达式
(9) 列别名（三种方法）
(10)select 的格式总结
Select [all | distinct ]column_name1,column_name2, ….
From title_name as 别名
Where 条件表达式
Group by 列名
Having 条件表达式
Order by 列名[asc | desc]
(7)高级查询
一．嵌套子查询（执行顺序是：先执行子查询语句在执行外面的查询语句）
1.带有比较运算符的子查询例：select column_name from table_name where 表达式比较运算符（子查询）注意：（1）子查询返回的值必须为一个值
2.带有in 和not in的子查询例：select column_name from table_name where column_name [not] in(子查询) 注意：（1）子查询内返回的是单列多值
（2）in 相当于or 语句例：上述语句可以写成
Select column_name from table_name where column_name=值1 or column_name=值2 or…….
3.带有all 和any的子查询
例：select column_name from table_name where column_name 运算符all 或者any=（some）(子查询)
注意：（1）子查询返回的值是单列多值
（2）用some 代替any 只能在sql语句中使用
二．相关子查询（执行的顺序是：从内到外依次执行）
1.带有exists的子查询例：select column_name from table_name where [not] exists(子查询)
注意：（1）exists关键字前面无列名、常量、表达式
（2）exists 后子查询的select语句后面只要跟*即可即：子查询为select *from table_name where …三．多表连接的查询
1.内连接：Select 列名1、列名2…from 表1 inner Join 表2
on 表1·列名=表2·列名
inner Join 表三
on 表1·列名=表3·列名
或者
Select 列名1、列名2…from表1，表2，表3
where表1·列名=表2·列名and 表1·列名=表3·列名
2.外连接
Select 列名from 表1 left、right 、all 【outer】Join 表2 on 表1·列名=表2·列名
3.自连接（向但与特殊的自连接）
例：select 表别名1 *from 表名as 表别名1 【inner】join 表名as表别名2
On 表别名1 .列名=表别名2.列名
注意：使用表别名时只支持两种写法 1.表名as 别名 2.表名别名
4.交叉连接
例：select 列名from 表1 cross Join 表2 或者Select 列名from 表1，表2
四．补充知识点
1.在原来写标明的地方写子查询
例：select *from（Select *from 表名as 别名）注意：必须给表起别名
2.在原来写列名的地方系子查询
例：Select 列名1，（Select 列名from表名where….），列名2 …from 表名
3.创建不存在的新表
Select 列名into 新表名form 原表名
4创建与原表结构相同的新表
Select * into 新表名1 from 原表名1 where 1=2（永远成立的条件）
向新建的表中批量添加数据
Insert into 新表名1 Select *from 原表名1
--专题四
一．视图
1.创建视图
语法：create view view_name
[with encryption] 加密
As
Select 列名from 表名where ….
[with check option]
检查视图数据是否允许被修改。如果没有此句话，修改视图超出范围时，超出范围的信息会消失2.创建多表视图
Create view view_name
As
Select 列名1，列名2，列名3，…from表1，表2
Where 表1.列名=表2.列名
3.查看视图exec sp_help view_name
4.查看视图定义信息exec sp_helptext ‘view_name’
5.注意事项：（1）不能在视图中使用update 只能在表中修改
（2）在alter时不能和表一样需重写如：
Alter view view_name
As
新的查询语句
（3）在视图中未使用top子句时不能用order by 子句
二．索引
1.创建索引
Create [unique][clustered][nonclustered]index index_name
On table | view (column_name [ desc | asc ])
例：create clustered (聚集索引) index 索引名on 表名（列名desc，列名asc，….）
2.使用索引
Select …from table_name(index=index_name)
例：select *from 表名（index=index_name）where 列名=值
3.重建索引
Dbcc dbretindex ([‘table_name’] [重建的索引名] 【fillfactor】
Dbcc dbreubdex （表名，新索引名，填充因子）
4.删除索引
Drop index table.index | view.index
5.索引的例子
（1）在Pubs库中的Stud表上创建Studid_ind的聚集索引
Create clustered index studid_ind on stu(studid)
（2）在Stud表上创建Studfullname_ind的唯一索引
Create unique index studfullname_ind on stud(fname desc,lname
（3）查看Stud表上的索引
Exec sp_helpindex stud
（4）使用Studid_ind索引查询
Select *from stu(index=studid_ind) where studid=’200710090002’
(5)删除Studid_ind和Studfullname_ind索引
Drop index stud.studid_ind , stud.studfullname_ind
（6）修改stud表，设定Studid为主键
Alter table stud add constraint pk_aa primary key clustered (studid)
（7）删除PRIMARY KEY约束的PK_Studid聚集索引
Drop index stud.pk_aa
（8）重建PK_Studid索引
Dbcc dbreindex(Stud, pk_aa) 语法：dbcc dbreindex (表名，约束名)
（9）重建PK_Studid索引，设定其填充因子占50%
Dbcc dbreindex (Stud,PK_aa,50) 语法：dbcc dbreindex （表名，约束名，填充因子）（10）重建Studname_ind索引如果存在studname_ind索引则删除
Create clustered index studname_ind on stud（fname desc ，lname）with drop existing
--事物（成功全成功，失败全失败）
1事物的分类：显性事物、隐性事物和自动提交事务
（1）开启事物语法：begin tran[saction] 事物名称
（2）提交事务语法：commit tran[saction] 事物名称
（3）回滚事物语法：rollback tran[saction] 事物名称
（4）创建保存点语法：save tran[saction] 保存点名，变量名
2显性事物
例：begin transaction
Save tran a --创建保存点a
Insert into stud values(‘200710090007’,’孙’,’加’,22)
Rollback tran a --回滚到保存点，不执行上一句的插入语句
Create table E(dname varchar(10),age int) --创建表E
Insert into E values(‘lis’,1)
Select *from stud --查询stud表
Select *from E --查询E表
Commit tran
显示结果：stud表中没有添加上列内容E表中有数据
3.隐性事物隐形的开启，显性的结束
例：set implict_transactions on
/*第一次执行insert语句的时候将自动启动一个隐性事物*/
Insert into 表名values （值1，值2，….）
Select*from 表名
此时在查询分析其中可以显示结果，但再打开另一查询分析器执行select语句则不显示结果加上commit tran 语句才能显示结果
4.自动提交事务查询分析器本身是个自动提交事务
Insert into 表名values （值1，值2，….）
Select*from 表名
此时在查询分析其中可以显示结果，若再打开另一查询分析器执行select语句也可以显示结果--专题五T_SQL编程
变量：全局变量和局部变量。
1.全局变量：以@@字符开头进行声明
常用的全局变量及含义
例：Select 别名=@@errror，别名=@@identify
2.局部变量：以@字符开头进行声明
注意：在SQL中变量必须先声明再使用
●变量的声明和赋值。
1.变量的声明 declare @变量名变量的数据类型例：declare @var1 int
2.变量的赋值（select、set）
Select @var1=@var的值，@var2=@var2的值。。。
或
Set @var1=@var1的值
Set @var2=@var2的值
。
。
注意：select可以同时为多个变量赋值，set只能为一个变量赋值。
3.变量的输出（select，print）
Select 别名=@var1，别名=@var2，。。。
或
Print 别名=@var1
Print 别名=@var2
·
·
注意：select可以同时输出多个变量。且结果显示在前台程序里（网格里），print一次只能输出一个值，且结果显示在（消息里）用于调试
4.局部变量的一些特例
（1）声明三个变量@jobdesc（varchar（20））和@minlvl（INT）和@maxlvl（INT），给三个变量赋值为@jobdesc=’Computer Manager’、@minlvl=88、@maxlvl=180，使用INSERT语句向pubs数据库中的jobs 表插入数据。
DECLARE @jobdesc VARCHAR(20),@minlvl INT,@maxlvl INT
SELECT @jobdesc='Computer Manager',@minlvl=88,@maxlvl=180
INSERT INTO jobs VALUES (@jobdesc,@minlvl,@maxlvl)
SELECT * FROM jobs ORDER BY job_id DESC
（2）声明两个局部变量@name（VARCHAR（20））和@jobid（INT），通过查询在pubs数据库中的employee 表中emp_id为“PTC11962M”的fname和lname的值赋给@name和job_id的值赋给@jobid，显示@name和@jobid的值。
DECLARE @name VARCHAR(20),@jobid INT
SELECT @name=fname+' '+lname,@jobid=job_id
FROM employee WHERE emp_id='PTC11962M'
SELECT @name AS NAME,@jobid AS JOBID
GO
SELECT * FROM employee WHERE emp_id='PTC11962M'
●逻辑控制语句：if…else、while、case等。
注意：case语句不能单独在SQL语句中执行
正确写法：错误写法：
Select au_lname，au_fname，（country别名）Case state
Case state When ‘un’then ‘uath
When ‘un’then ‘uath’When‘ca‘then ’California‘
When‘ca‘then ’California‘End
End
City from authors order by state desc
常用函数：转换函数、数字函数、日期函数、字符串函数、聚合函数等。
1.转换函数（convert，cast）
Convert(数据类型，列名，样式（style））
Cast（列名 as 数据类型）
例：Select title，'price'+convert(varchar(10),price) as price from titles Select title,'price'+cast(price as varchar) as price from titles
2.日期时间函数
Select datetime （dw，getdate（））输出结果是：星期（几）
函数返回值
SQL Server识别的日期部分和缩写
3.字符串函数
例：查询pubs数据库中authors表中的au_lname、au_fname和state列的数据，显示要求 au_lname列的数据小写显示、au_fname列的数据大写显示和state列的数据要重复一遍显示输出。
SELECT LOWER(au_lname) AS LNAME,UPPER(au_fname) AS FNAME,
REPLICATE(state,2) AS STATE FROM authors
常用的字符串函数有
4.元数据函数
函数返回值
DB_ID([‘database_name’]) 返回数据库标识号。
DB_NAME([database_id]) 返回数据库名。
例题
（1）查询系统中有多少个数据库（比在master数据库中查询）
User master go
Select * from sysdatebases
（2）if exists（select *from sysobjects） where name='视图名v1' and type='视图 v' drop v1 Go
Create view v1 select * from 表名
（3）if object_id('视图名 v1'，type） is not null
●输出语句：print
●批处理：go
补充知识
1.raiserror函数
raiserror(‘数据插入失败’，16，1)
16：用户定义的与消息关联的严重级别，用户可以用从0到18之间的严重级别
1：表示有关错误调用状态的信息，从1到127之间的任意整数默认值为1
2 is null 函数
作用：将为空的数据转换为0 is null （列名，0）
3大小写的问题
默认情况下SQL server 不区分大小写，可以分别在创建数据库、修改数据库、创建表的字符型列（char\varchar\nchar\nvarchar等）时指定排序规则来实现。
例：再创建数据库时
create database test COLLA TE Chinese_PRC_CS_AS --这样创建的数据库中区分大小写
alter database test COLLATE Chinese_PRC_CS_AS --修改后的数据库中区分大小写
Create table test(tid int primary key,tname varchar(20) collate chines_prc_cs_as) --这样创建的tname列在使用SELECT 语句时就可以区分大小写了。
默认情况下是：chinese_prc_ci_as
CI：指定不区分大小写CS：指定区分大小写。
AI：指定不区分重音AS：指定区分重音。
4.日期的输入（年为两个字符时，可设定输出格式）
Set dateformat mdy(月、日、年)
Decaler @date datetime
Set @date=‘08/05/03’
Select @date 显示结果：2003-08-05
语法：Set datefromat 日期格式（mdy、dmy、ymd……..）
--专题六游标
（1）游标的使用步骤：1.定义游标2.打开游标3.提取数据4.释放游标5.删除游标
游标练习题：
1．使用只读游标，查询并显示输出pubs数据库中的authos表中state字段的知识’ca’的au_fname、au_lname 的值。打印出“author name：au_fname的值、au_lname的值”。
/*定义只读游标*/
Declare author_csr(游标名) cursor（游标标志）read_only(只读) for
Select au_fname,au_lname from authors where state=’ca’,
Order by au_fname,au_lname
/*定义变量*/
Declare @lname varchar(20),@fname varchar(20)
/*打开游标*/
Open author_csr
/*执行第一次提取操作*/
Fetch next from author_csr into @lname,@fname
/*循环游标读取操作*/
While @@fetch_status=0
Begin
Print ‘author name: ’ +@lname+’‘+@fname
Fetch next from autho_csr into @lname,@fname
End
/*关闭游标*/
Close author_csr
/*释放游标*/
Deallocate author_csr
注意：1.read_only 类型的游标只能从第一行开始然后依次向下提取
2.fetch的作用：移动指针，提取并显示数据
3.@@fetch_status 返回上一个fetch提取语句是否正确（返回只有0：fetch语句成功；-1：失败或
不在结果集中；-2.提取的喊不存在）
2.使用只读游标，查询并显示输出pubs数据库中的authos表中state字段的知识’ca’的au_fname、au_lname 的值，现实的顺序为：最后一行，最后一行的上一行，第四行，第二行，第一行。
/*声明游标*/
Declare aa scroll cursor(滚动类型的游标) for
Select au_fname,au_lname from authors where state=’ca’
Order by au_fname,au_lname
/*打开游标*/
Open aa
/*提取最后一行的数据*/
Fetch last from aa
/*提取最后一行数据的前一行*/
Fetch prior from aa
/*提取第四行数据*/
Fetch absolute 4 from aa
/*提取第一行数据*/
Fetch first from aa
/*提取第一行的后两行数据*/
Fetch relative 2 from aa
/*关闭游标*/
Close aa
/*释放游标*/
Deallocate aa
注意：1.在scroll cursor（滚动游标）中可以按要求提取数据
2.first ，last 分别是提取最后一行和第一行的数据
3.prior 返回当前行的前一行数据
4.Absolute 正整数：返回数据集的第几行数据
5.relative 正整数（负整数）：返回当前行的前（后）几行数据
3.使用游标更新pubs数据库中的titles表中类型type是business的书的价钱上涨50%，先查看pubs数据库中type是business的书的价格。
/*定义游标*/
Declare price cursor for
Select title,price from titles where type=’business’for update of price
/*打开游标*/
Open price
/*使用游标提取第一行数据*/
Fetch price
While @@fetch_status=0
Begin
Update titles set price=price*(1+.05) where current of price
Fetch price
End
/*关闭游标*/
Close price
/*释放游标*/
Deallocate price
--专题八触发器
1.自动执行完成自定义完整性
2.分类：按触时间不同可分为（insert，update，delete）按触发时机不同可分为（after，instead of）
After 先插入数据在执行
3. 触发器的特例
如果插入一条卷烟库存表记录时，相应对日志表中添加增加日志。
Create trigger trigger_name on 卷烟库存表
After insert
As
Insert into 日志表values(‘‘)
如果删除一条卷烟库存记录时，相应对日志表中添加删除日志。
Create trigger trigger_name on 卷烟库存表
After delet e
As
Insert into 日志表values(‘‘)
如果更新一条卷烟库存记录时，相应对日志表中添加更新日志。
Create trigger trigger_name on 卷烟库存表
After update
As
Insert into 日志表values(‘')
如果卷烟销售表中添加某卷烟品牌销售记录后，自动更新其销售金额。
Create trigger trigger_name on 卷烟库存表
After insert
As
Update 卷烟销售记录表set price=price*num where name=(select name from inserted)
提示：inserted 表
Create trigger trigger_name on table_name
After insert （update）
As
Select *from inserted
生成的inserted表中只含有插入的信息列或更改的信息列
Delete 表
Create trigger trigger_name on table_name
After delete （update）
As
Select *from delet ed
生成的deleted表中只含有删除的列的信息或更改的列的信息
如果卷烟库存表中的某卷烟品牌删除后，卷烟销售表中相应的卷烟品牌也必须删除掉。
Create trigger trigger_name on table_name
After delet e
As
Delet e from table_name1 where name=(select name from deleted)
如果卷烟库存表中的某卷烟品牌价格更新后，库存金额相应改变并且在卷烟销售表中的销售价格根据公式（销售价格＝库存价格*1.3）来改变销售价格和销售金额，并且添加日志。
Create trigger trigger_name on 卷烟库存表
After update
As
Declare @price money,@name varchar(40)
Select @price=price from inserted
Select @name=price from inserted
--更新卷烟库存表中的总金额
Update totalprice=num*@price where name=@name
–根据要求改变销售价格和销售总金额
Update salseprice=@price*1.3, salsetotalprice=@price*1.3*num where salesname=@name
--向日志表中添加数据
Insert into 日志表values( ‘‘)
如果卷烟销售表中的销售数量更新了，则对应的销售金额相应更新，并添加日志。
Create trigger trigger_name on 卷烟库存表
After update
As
Declar @num int ，@name varchar(40)
Select @num=num ,@name=name from inserted
--更新卷烟库存表中的销售总金额
Update销售表set salsetotalprice= @num * salseprice where name=@name
--向日志表中添加记录
Insert into 日志表values (‘‘)
--专题九：创建登陆用户
●--对象权限：update, insert into ,delete，select
--语句权限：create，drop
●--对象权限语法
--1.授予访问权限语法：grant 对象权限名（select,insert,update,delete）on 表名to用户名grant select,insert,update,delete on table1 to user1
--2.拒绝访问权限语法：deny 对象权限名（select,insert,update,delete）on 表名to用户名deny select,insert,update,delete on table1 to user1
--3.删除访问权限语法：revoke 对象权限名（select,insert,update,delete）on 表名to用户名revoke select,insert,update,delete on table1 to user1
●--语句权限语法
--1.授予访问权限语法：grant 对象权限名（create,drop) to用户名
grant create,drop to user1
--2.拒绝访问权限语法：deny 对象权限名（create,drop) to用户名
deny create,drop to user1
--3.删除访问权限语法：revoke 对象权限名（create,drop) to用户名
revoke create,drop to user1
●--数据库的备份与还原
1.创建备份设备
语法：sp_addumpdevice ‘备份设备类型’，‘设备名称’，‘存储位置’
提示：备份设备类型有（disk，tape[磁带] , pipe[命名管道] ）
例：Sp_addumpdevice ‘disk’,’pubs_back’,’d:\pubs_back’
注意：在D盘上必须存在pubs_back文件夹
2.备份全库
Backup database database_name
To 备份设备名称
With name=’‘,
Description=’‘,
Init [noinit]
2.差异备份
Backup database database_name
To 备份设备名称
With differential，
Name=’ ‘，
Description=‘’，
Init [noinit]
3.日志备份
Backup log database_name
To 备份设备名称
With name=‘’，
Description=‘’，
Init [noinit](保留原有的东西，备份时默认为noinit)
--查看备份设备中所有的备份信息
Restore headeronly form 备份设备名称
提示：如果备份时with 后添加了init 则只显示最后一步执行的备份名字（备份时with里写noinit 或者不写此语句）
恢复备份信息
注意：回复的顺序是全库的备份、之后是差异备份、之后依次还原日志备份
--恢复全库备份
Restore database database_name
From 备份设备名称
With file=file_number,
Norecovery
--恢复差异备份
Restore database database_name
From 备份设备名称
With file=file_number,
Norecovery
--恢复日志备份
Restore log database_name
From 备份设备名称
With file=file_name,
Recovery
注意：file_number 是备份时的顺序（如第一个备份全库备份它的file_number=1）
恢复备份时with里只有在最后一个回复的备份中写成recovery 前面的均写成norecovery
经典SQL语句大全
一、基础 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
Sql语句经典难题
1.找出工资最高的员工。 2.部门中工资最高的人的名单 3.求部门的平均工资的等级 4.不用组函数求工资最高的人的信息。 5.部门工资平均的等级。 6.找出那些人不是经理 7.找出员工和他的领导 8.部门平均工资最高的部门的编号。 9.部门平均工资最高的部门的名称 10.比员工最高工资还高的经理 --select * from emp where sal =(select max(sal) from emp) --select * from emp where sal in( select deptno,max(sal) from emp group by deptno) /* select * from emp e join (select deptno,max(sal) maxsal from emp group by deptno)t on (e.deptno=t.deptno and e.sal=t.maxsal) */ /* select t.deptno ,s.grade from salgrade s join (select deptno,avg(sal) avgsal from emp group by deptno) t on ( t.avgsal between s.losal and s.hisal ) */
select deptno,avg(grade) from ( select e.deptno ,s.grade from emp e join salgrade s on (e.sal between s.losal and s.hisal) ) group by deptno */ --select * from emp where empno not in (select mgr from emp where mgr is not null) --select w.ename , m.ename from emp w join emp m on (w.mgr=m.empno) /* select dname from dept where deptno=( select deptno from (select deptno,avg(sal) avgsal from emp group by deptno) where avgsal= ( /* select max(avgsal) from ( select deptno,avg(sal) avgsal from emp group by deptno ) ) ---select max(avg(sal)) from emp group by deptno ) select * from ( select * from emp where empno in (select mgr from emp) ) where sal >( select max(sal) from
数据库经典SQL语句大全
数据库经典SQL语句大全 篇一：经典SQL语句大全 下列语句部分是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:mssql7backupMyNwind_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、说明： 删除新表： 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 注：索引是不可更改的，想更改必须删除重新建。
50个经典sql语句总结
一个项目涉及到的50个Sql语句(整理版) --1.学生表 Student(S,Sname,Sage,Ssex) --S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--2.课程表 Course(C,Cname,T) --C --课程编号,Cname 课程名称,T 教师编号 --3.教师表 Teacher(T,Tname) --T 教师编号,Tname 教师姓名 --4.成绩表 SC(S,C,score) --S 学生编号,C 课程编号,score 分数 */ --创建测试数据 create table Student(S varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10)) insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男') insert into Student values('02' , N'钱电' , '1990-12-21' , N'男') insert into Student values('03' , N'孙风' , '1990-05-20' , N'男') insert into Student values('04' , N'李云' , '1990-08-06' , N'男') insert into Student values('05' , N'周梅' , '1991-12-01' , N'女') insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女') insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女') insert into Student values('08' , N'王菊' , '1990-01-20' , N'女') create table Course(C varchar(10),Cname nvarchar(10),T varchar(10)) insert into Course values('01' , N'语文' , '02') insert into Course values('02' , N'数学' , '01') insert into Course values('03' , N'英语' , '03') create table Teacher(T varchar(10),Tname nvarchar(10)) insert into Teacher values('01' , N'张三') insert into Teacher values('02' , N'李四') insert into Teacher values('03' , N'王五') create table SC(S varchar(10),C varchar(10),score decimal(18,1)) insert into SC values('01' , '01' , 80) insert into SC values('01' , '02' , 90) insert into SC values('01' , '03' , 99) insert into SC values('02' , '01' , 70) insert into SC values('02' , '02' , 60) insert into SC values('02' , '03' , 80) insert into SC values('03' , '01' , 80) insert into SC values('03' , '02' , 80) insert into SC values('03' , '03' , 80) insert into SC values('04' , '01' , 50) insert into SC values('04' , '02' , 30) insert into SC values('04' , '03' , 20) insert into SC values('05' , '01' , 76) insert into SC values('05' , '02' , 87)
强化-SQL语句强化训练(史上最全最经典,呕血推荐) sql语句练习
2010/7 1. 有4个关系模式如下：出版社（出版社编号，出版社名称）；图书（图书编号，书名，出版社编号，定价）；作者（作者编号，姓名）；著书（图书编号，作者编号，作者排序） 注：作者排序=1表示第一作者，依此类推。用SQL语句，完成第36~39题。 (1).检索所有定价超过20元的书名。 答案：SELECT书名（1分） FROM图书（1分） WHERE定价>20（2分） (2).统计每个出版社图书的平均定价。 答案：SELECT出版社编号，A VG（定价）（2分） FROM图书（1分） GROUP BY出版社编号（1分） (3).将科学出版社的所有图书定价下调5%。 答案：UPDATE图书SET定价=定价*0.95（1分） WHERE出版社编号IN（1分） （SELECT出版社编号FROM出版社（1分） WHERE出版社名称="科学"）（1分） 【说明】WHERE出版社名称LIKE"科学"也正确。 (4).列出所有图书的书名、第一作者姓名和出版社名称。 答案：SELECT书名，姓名，出版社名称（1分） FROM出版社A，图书B，作者C，著书D（1分） WHEREA.出版社编号=B.出版社编号ANDB.图书编号=D.图书编号（1分） ANDC.作者编号=D.作者编号AND作者排序=1。(1分) S(SNO,SNAME,AGE,SEX，SDEPT) SC(SNO,CNO,GRADE) C(CNO,CNAME,CDEPT，TNAME) 1.试用SQL的查询语句表达下列查询： ①检索LIU老师所授课程的课程号和课程名。 ②检索年龄大于23岁的男学生的学号和姓名。 ③检索至少选修LIU老师所授课程中一门课程的女学生姓名。 ④检索W ANG同学不学的课程的课程号。 ⑤检索至少选修两门课程的学生学号。 ⑥检索全部学生都选修的课程的课程号与课程名。 ⑦检索选修课程包含LIU老师所授课程的学生学号。 2．试用SQL查询语句表达下列对教学数据库中三个基本表S、SC、C的查询： ①统计有学生选修的课程门数。 ②求选修C4课程的学生的平均年龄。 ③求LIU老师所授课程的每门课程的学生平均成绩。 ④统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。 ⑤检索学号比WANG同学大，而年龄比他小的学生姓名。 ⑥检索姓名以WANG打头的所有学生的姓名和年龄。 ⑦在SC中检索成绩为空值的学生学号和课程号。 ⑧求年龄大于女同学平均年龄的男学生姓名和年龄。 ⑨求年龄大于所有女同学年龄的男学生姓名和年龄。 3．试用SQL更新语句表达对教学数据库中三个基本表S、SC、C的各个更新操作： ①往基本表S中插入一个学生元组（'S9'，'WU'，18）。
50个经典SQL查询语句
--一个题目涉及到的50个Sql语句 --（下面表的结构以给出,自己在数据库中建立表.并且添加相应的数据,数据要全面些. 其中Student表中,SId为学生的ID） ------------------------------------表结构-------------------------------------- --学生表tblStudent（编号StuId、姓名StuName、年龄StuAge、性别StuSex） --课程表tblCourse（课程编号CourseId、课程名称CourseName、教师编号TeaId） --成绩表tblScore（学生编号StuId、课程编号CourseId、成绩Score） --教师表tblTeacher（教师编号TeaId、姓名TeaName） --------------------------------------------------------------------------------- --1、查询“001”课程比“002”课程成绩高的所有学生的学号； Select StuId From tblStudent s1 Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId='001')> (Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId='002') --2、查询平均成绩大于60分的同学的学号和平均成绩； Select StuId,Avg(Score) as AvgScore From tblScore Group By StuId Having Avg(Score)>60 --3、查询所有同学的学号、姓名、选课数、总成绩； Select StuId,StuName, SelCourses=(Select Count(CourseId) From tblScore t1 Where t1.StuId=s1.StuId), SumScore=(Select Sum(Score) From tblScore t2 Where t2.StuId=s1.StuId) From tblStudent s1 --4、查询姓“李”的老师的个数； Select Count(*) From tblTeacher Where TeaName like '李%' --5、查询没学过“叶平”老师课的同学的学号、姓名； Select StuId,StuName From tblStudent Where StuId Not In ( Select StuID From tblScore sc Inner Join tblCourse cu ON sc.CourseId=cu.CourseId Inner Join tblTeacher tc ON cu.TeaId=tc.TeaId Where tc.TeaName='叶平' ) --6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； Select StuId,StuName From tblStudent st Where (Select Count(*) From tblScore s1 Where s1.StuId=st.StuId And
经典EXCEL VBA SQL语句
EXCEL(VBA)~SQL经典写法范本汇集2008年03月30日星期日下午07:21EXCEL(VBA)~SQL经典写法范本汇集 **************************************************************** A、根据本工作簿的1个表查询求和写法范本 Sub查询方法一() Set CONN=CreateObject("ADODB.Connection") CONN.Open"provider=microsoft.jet.oledb.4.0;extended properties=excel8.0;data source="& ThisWorkbook.FullName sql="select区域,存货类,sum(代销仓入库数量),sum(代销仓出库数量),sum(日报数量)from[sheet4$a:i]where区域='"&[b3]&"'and month(日期)='"&Month(Range("F3"))&"'group by区域,存货类" Sheets("sheet2").[A5].CopyFromRecordset CONN.Execute(sql) CONN.Close:Set CONN=Nothing End Sub ----------------- Sub查询方法二() Set CONN=CreateObject("ADODB.Connection") CONN.Open"dsn=excel files;dbq="&ThisWorkbook.FullName sql="select区域,存货类,sum(代销仓入库数量),sum(代销仓出库数量),sum(日报数量)from[sheet4$a:i]where区域='"&[b3]&"'and month(日期)='"&Month(Range("F3"))&"'group by区域,存货类" Sheets("sheet2").[A5].CopyFromRecordset CONN.Execute(sql) CONN.Close:Set CONN=Nothing End Sub ************************************************************************************************** B、根据本工作簿2个表的不同类别查询求和写法范本 Sub根据入库表和回款表的区域名和月份分别求存货类发货数量和本月回款数量查询() Set conn=CreateObject("adodb.connection") conn.Open"provider=microsoft.jet.oledb.4.0;"&_ "extended properties=excel8.0;data source="&ThisWorkbook.FullName Sheet3.Activate Sql="select a.存货类,a.fh,b.hk from(select存货类,sum(本月发货数量)"_ &"as fh from[入库$]where存货类is not null and区域='"&[b2]_ &"'and month(日期)="&[d2]&"group by存货类)as a"_ &"left join(select存货类,sum(数量)as hk from[回款$]where存货类"_ &"is not null and区域='"&[b2]&"'and month(开票日期)="&[d2]&""_ &"group by存货类)as b on a.存货类=b.存货类" Range("a5").CopyFromRecordset conn.Execute(Sql) End Sub ******************************************************************* C、根据本文件夹下其他工作簿1个表区域的区域求和 Sub在工作表1汇总本文件夹下001工作薄的表1分数列查询汇总() Set conn=CreateObject("ADODB.Connection") conn.Open"dsn=excel files;dbq="&ThisWorkbook.Path&"\001.xls" sql="select sum(分数)from[sheet1$]"
50个经典SQL语句
50个经典SQL语句 Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表 问题： 1、查询“001”课程比“002”课程成绩高的所有学生的学号； select a.S# from (select s#,score from SC where C#='001') a,(select s#,score from SC where C#='002') b where a.score>b.score and a.s#=b.s#; 2、查询平均成绩大于60分的同学的学号和平均成绩； select S#,avg(score) from sc group by S# having avg(score) >60; 3、查询所有同学的学号、姓名、选课数、总成绩； select Student.S#,Student.Sname,count(SC.C#),sum(score) from Student left Outer join SC on Student.S#=SC.S# group by Student.S#,Sname 4、查询姓“李”的老师的个数； select count(distinct(Tname)) from Teacher where Tname like '李%'; 5、查询没学过“叶平”老师课的同学的学号、姓名； select Student.S#,Student.Sname
from Student where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平'); 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002'); 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； select S#,Sname from Student where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname='叶平')); 8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名； Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2 from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2
数据库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) se lect 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,https://www.360docs.net/doc/d811718499.html,ername,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 wher e ..... 12、说明：日程安排提前五分钟提醒 sql: select * from 日程安排where datediff('minute',f开始时间,getdate())>5
50个常用的SQL语句练习
基本信息Student(`S#`,Sname,Sage,Ssex) 学生表 Course(`C#`,Cname,`T#`) 课程表 SC(`S#`,`C#`,score) 成绩表 Teacher(`T#`,Tname) 教师表 问题： 1、查询“001”课程比“002”课程成绩高的所有学生的学号； select a.`S#` from (select `S#`,score from SC where `C#`='001') a,(select `S#`,score from SC where `C#`='002') b where a.score>b.score and a.`S#`=b.`S#`; ↑一张表中存在多对多情况的 2、查询平均成绩大于60分的同学的学号和平均成绩； 答案一：select `S#`,avg(score) from sc group by `S#` having avg(score) >60; ↑一对多，对组进行筛选 答案二：SELECT s ,scr FROM (SELECT sc.`S#` s,AVG(sc.`score`) scr FROM sc GROUP BY sc.`S#`) rs WHERE rs.scr>60 ORDER BY rs.scr DESC ↑嵌套查询可能影响效率 3、查询所有同学的学号、姓名、选课数、总成绩； 答案一：select Student.`S#`,Student.Sname,count(`C#`),sum(score) from Student left Outer join SC on Student.`S#`=SC.`S#` group by Student.`S#`,Sname ↑如果学生没有选课，仍然能查出，显示总分null（边界情况） 答案二：SELECT student.`S#`,student.`Sname`,COUNT(sc.`score`) 选课数,SUM(sc.`score`) 总分FROM Student,sc WHERE student.`S#`=sc.`S#` GROUP BY sc.`S#` ↑如果学生没有选课，sc表中没有他的学号，就查不出该学生，有缺陷！ 4、查询姓“李”的老师的个数； select count(distinct(Tname)) from Teacher where Tname like '李%'; 5、查询没学过“叶平”老师课的同学的学号、姓名； select Student.`S#`,Student.Sname from Student where `S#` not in (select distinct(SC.`S#`) from SC,Course,Teacher where SC.`C#`=Course.`C#` and Teacher.`T#`=Course.`T#` and Teacher.Tname='叶平'); ↑反面思考Step1：先找学过叶平老师课的学生学号，三表联合查询 Step2：在用not in 选出没学过的 Step3：distinct以防叶平老师教多节课；否则若某同学的几节课都由叶平教，学号就会出现重复 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； select Student.`S#`,Student.Sname from Student,SC where Student.`S#`=SC.`S#` and SC.`C#`='001'and exists( Select * from SC as SC_2 where SC_2.`S#`=SC.`S#` and SC_2.`C#`='002' ); ↑注意目标字段`S#`关联 exists subquery 可以用in subquery代替，如下 select Student.`S#`,Student.Sname from Student,Sc where Student.`S#`=SC.`S#` and SC.`C#`='001'and sc.`s#` in ( select sc_2.`s#` from sc as sc_2 where sc_2.`c#`='002' ); ↑不同之处，in subquery此处就不需要关联了
sql常用语句100例
--update phoneinfo set cityname = '克孜勒苏柯尔克孜' where cityname = '克孜勒苏柯尔克孜州' --update phoneinfo set cityname = '湘西' where pad1 = '湖南 吉首' select * from dbo.PhoneInfo --update dbo.PhoneInfo set provincename=b.provincename,cityname=b.cityname from dbo.PhoneInfo a,PhoneInfo_hl b --where a.phonebound=b.phonebound --select * from dbo.UnknowPhoneBound --select * from dbo.Area --select * from phoneinfo a, phoneinfo_old b, phoneinfo_hl c where a.phonebound = b.phonebound and a.phonebound = c.phonebound and (a.cityname <> b.cityname or a.cityname <> c.cityname) --select * from phoneinfo a, phoneinfo_hl b where a.phonebound = b.phonebound and a.cityname <> b.citynameselect * from phoneinfo a, phoneinfo_old b where a.phonebound = b.phonebound and a.cityname <> b.cityname --select * into phoneinfo_bak from phoneinfo --select * from phoneinfo_bak select * from phoneinfo a, phoneinfo_old b where a.phonebound = b.phonebound and a.cityname <> b.cityname --select * from dbo.PhoneInfo_Telecom --update PhoneInfo_Telecom set provincename = '内蒙古' where pad1 = '内蒙兴安盟' update PhoneInfo set cityname = '酒泉' where pad1 = '甘肃 酒泉嘉峪关' --update dbo.PhoneInfo_old set provincename=b.provincename,cityname=b.cityname from dbo.PhoneInfo_old a,PhoneInfo_Telecom b --where a.phonebound=b.phonebound create table client_all as
经典实用SQL语句大全总结
经典实用SQL语句大全总结 [编辑语言]2015-05-26 19:56 本文导航 1、首页 2、11、说明：四表联查问题： 本文是经典实用SQL语句大全的介绍，下面是该介绍的详细信息。 下列语句部分是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、说明：
DB2常用SQL语句集
DB2常用SQL语句集 1、查看表结构： describe table tablename describe select * from tablename 2、列出系统数据库目录的内容： list database directory 3、查看数据库配置文件的内容： get database configuration for DBNAME 4、启动数据库： restart database DBNAME 5、关闭表的日志 alter table TBLNAME active not logged inially 6、重命名表 rename TBLNAME1 to TBLNAME2 7、取当前时间 select current time stamp from sysibm.sysdummy1 8、创建别名 create alias ALIASNAME for PRONAME(table、view、alias、nickname) 9、查询前几条记录 select * from TBLNAME fetch first N rows 10、联接数据库 db2 connect to DB user db2 using PWD 11、绑定存储过程命令 db2 bind BND.bnd 12、整理优化表 db2 reorgchk on table TBLNAME db2 reorg table TBLNAME db2 runstats on table TBNAME with distribution and indexes all 13、导出表 db2 export to TBL.txt of del select * from TBLNAME db2 export to TBL.ixf of ixf select * from TBLNAME 以指定分隔符‘|’下载数据： db2 "export to cmmcode.txt of del modified by coldel| select * from cmmcode”14、导入表 db2 import from TBL.txt of del insert into TBLNAME db2 import from TBL.txt of del commitcount 5000 insert into TBLNAME db2 import from TBL.ixf of ixf commitcount 5000 insert into TBLNAME db2 import from TBL.ixf of ixf commitcount 5000 insert_update into TBLNAME db2 import from TBL.ixf of ixf commitcount 5000 replace into TBLNAME db2 import from TBL.ixf of ixf commitcount 5000 create into TBLNAME (仅IXF) db2 import from TBL.ixf of ixf commitcount 5000 replace_create into TBLNAME (仅 IXF) 以指定分隔符“|”加载：