注意事项：
1.结束语句使用分号，可使用也可不使用
2.不区分大小写
1. 数据库操作
查询有所有数据库
use master
select * from sysdatabases
select name from sysdatabases
创建数据库
createdatabasedbname1
on primary(
name='dbname1_data',
filename='E:\myDB\dbname1.mdf',
size=5,
maxsize=5,
filegrowth=10%
)
logon(
name='dbname1_log',
filename='E:\myDB\dbname1.ldf',
size=10,
filegrowth=5%
)
删除数据库
drop database dbname1
修改数据库名
exec sp_renamedbdbname1,'dbname2'
查询现有的备份设备
select * from sys.backup_devices
增加一个备份设备
exec sp_addumpdevice'disk','testdisk','e:\testdisk1'
备份数据库 到设备
backup database dbname2 to testdisk
备份数据库 到文件
完整备份
use master
backupdatabasedbname2 to disk='e:\bkdbname2_test.bak'
差异备份
use master
backupdatabasedbname2 to disk='e:\bkdbname2_test1.bak'withdifferential
日志备份，会截断日志
use master
backuplogdbname2 to disk='e:\bkdbname2_log.bak'
日志备份，不会截断
use master
backuplogdbname2 to disk='e:\bkdbname2_log.bak'withno_truncate
恢复数据库 从设备
use master
restoredatabasedbname2 from testdisk withfile=1,norecovery
恢复数据库 从文件
use master
restoredatabasedbname2 from disk='e:\bkdbname2_test.bak'withfile=1,norecovery
还原事务日志
restorelogdbname2 from disk='e:\bkdbname2_log.bak'
2. 数据表
查询所有数据表
use dbname2
select * from sysobjects
创建数据表
createtabletable1(
idintnotnull primary key identity,
namenvarchar(20),
telnvarchar(15)
)
删除数据表
drop table tbname
修改数据表
exec sp_renametable2,'tbname'
添加一个表字段
alter table table2addmobilenvarchar(50)nullunique
删除一个字段
alter table table2dropcolumnname
查询表的全部字段
select * fromsyscolumnswhereid=object_id('table1')
更改字段属性
alter table tbname1 altercolumnage int
更改字段名称
exec sp_rename'table1.tel','telephone'
3. 增删改查
1.查询
select*fromtbname1
前10条
select top 10 * fromtbname1
常用聚合函数
selectMAX(id)fromtbname1
MAX,MIN,AVG,SUM,COUNT,GROUPING
内连接 inner join
select*fromtbname1asa inner join tbname2asb on a.id=b.id
select*from tbname1 as ainner join tbname2 asbon a.id=b.idwherea.id>10andb.id>20
外连接 分为左外连接 left join， 右外连接 right join ，全连接 full join
左外连接
select*from tbname1 as aleft outer join tbname2asb on a.id=b.id
右外连接
select*from tbname1 as aright outer join tbname2asb on b.id=a.id whereb.id>60
全外连接 where条件
select*fromtbname1 as afull jointbname2 as bon b.id=a.idwhereb.id>60
where条件
whereidbetween 12 and 24
whereidin(12,12,145,45)
whereidnot in(12,12,145,45)
whereid>=12 and id <= 20
排序
orderbyid asc
orderbyid desc
orderbyid,name,agedesc
2. 插入
insert into tbname1(telephone,age)values('1365989523',18)
insert into tbname1 values('1365989523',18)
insert into tbname1(age)values(18) //指定字段插入
选择一张表的数据，插入另一张表
insertintoUserGroups selectName,CreateTimefromUserGroup
大小写转换
将小写转换为大写：UPPER(cust_id)
将大写转换为小写:LOWER(cust_id)