sqlserver 命令一览表
1.创建数据库：
create database Student
on
(
name=Student_Data,
filename=’f:\data\student_Data.mdf’,
size=10,
maxsize=20,
filegrowth=5
)
log on
(
name=Student_Log,
filename=’f:\data\student_Log.ldf’,
size=10,
maxsize=20,
filegrowth=5
)
2.修改数据库：
1>添加数据文件：
alter database student
add file
(
name=Student_Data2,
filename=’f:\data\student_Data2.ndf’,
size=10,
maxsize=20,
filegrowth=5
)
2>修改数据文件：
alter database student
modify file
(
name=Student_Data,
size=15
)
3.删除数据库:
drop database student
4.设置数据库选项:
sp_dboption student,’single user’,true
5.修改数据库名:
sp_renamedb ‘student’,’students’
6.查看服务器上的数据库：
sp_databases
7.查看数据库上的文件:
sp_helpdb
sp_helpdb students
8.压缩数据库:
sp_dboption student,’single user’,true
go
DBCC shrinkdatabase(students,50)
9.断开与连接数据库:
1>断开: sp_detach_db ‘students’
2>连接: sp_attach_db ‘students’,’f:\students_data.mdf’
10.备份和恢复数据库
1>备份: backup database students to disk=’h:\students_back’
2>恢复: restore database students from disk=’h:\students_back’
二.表
1.创建表：(先建主键表,再建外键表)
create table xsxxb
(
xh char(10) primary key,
xm char(8),
xb char(2),
csrq datetime,
dh char(20)
)
go
create table kmxxb
(
kmbh char(10),
kmmc char(20),
primary key(kmbh)
)
go
create table xscjb
(
xh char(10),
kmbh char(10),
fs int,
foreign key(xh)references xsxxb,
foreign key(kmbh)references kmxxb
)
2.修改表:
1>增加字段
alter table xsxxb
add bz char(50) null
2>删除字段
alter table xsxxb
drop column bz
3.删除表：(先删外键表,再删主键表)
drop table xscjb
drop table xsxxb
drop table kmxxb
4.复制一个表:
select * into xsxxb2 from xsxxb
5.创建临时表 #,##)
create table #xsxxb
(
xh char(10) primary key,
xm char(8),
xb char(2),
csrq datetime,
dh char(20)
)
select * from #xsxxb
6.创建用户定义数据类型:
use students
go
sp_addtype sts,’varchar(20)’,’not null’,’dbo’
sp_addtype sts,datatime,’null’,’dbo’
7.删除用户定义数据类型:
sp_droptype sts
三.操作表中的数据
1>使用 INSERT 语句向表中插入数据:
insert into xsxxb values(‘008’,”,”,”)
2>使用 UPDATE 语句修改表中的数据:
update xsxxb set xm=’不’ where xh=’001′
3>使用 DELETE 语句删除表中的数据:
delete from xsxxb where xh=’001′
delete from xsxxb where xh in(‘002′,’004’)
delete from xsxxb
四.系统内置函数的使用
1.聚合函数:
1>AVG（表达式） 返回表达式中所有值的平均值。仅用于数字列并自动忽略 null 值。
2>COUNT（表达式） 返回表达式中非 null 值的数量。可用于数字和字符列。
3>COUNT(*) 返回表中的行数（包括有 null 值的行）。
4>MAX（表达式） 返回表达式中的最大值，忽略 null 值。可用于数字、字符和日期时间列。
5>MIN（表达式） 返回表达式中最小值，忽略 null 值。可用于数字、字符和日期时间列。
6>SUM（表达式） 返回表达式中所有值的总和，忽略 null 值。仅用于数字列。
2.转型函数:
CONVERT(datatype[(length)], expression [,style])
select convert(char(20),getdate(),101)
3.日期函数:
1>GETDATE() 当前的系统日期。
2>DATEADD(datepart, number, date) 返回带有指定数字 (number) 的日期 (date)，
该数字添加到指定的日期部分 (date part) 。
select dateadd(yy,2,getdate()) (yy,mm,dd,ww,hh,mi,ss)
3>DATEDIFF(datepart, date1, date2) 返回两个日期中指定的日期部分之间的不同.
select datediff(yy,getdate(),’2008/09/09′)
4>DATENAME(datepart, date) 返回日期中日期部分的字符串形式。
5>DATEPART(datepart, date) 返回日期中指定的日期部分的整数形式。
4.数学函数:
5.字符串函数:
rtrim()
ltrim()
ltrim(rtrim())
space(2)
substring(列名,开始位置,长度)
right(列名,长度)
left(列名,长度)
stuff(列名,开始位置,长度,字符串)
五.表的连接
1.内部连接:
select xsxxb.xh,xm,xscjb.fs from xsxxb inner join xscjb
on xsxxb.xh=xscjb.xh
2.多表连接:
select xsxxb.xh,xm,kmmc,fs from xsxxb inner join xscjb
on xsxxb.xh=xscjb.xh
join kmxxb
on xscjb.kmbh=kmxxb.kmbh
3.左外部连接:
select xsxxb.xh,xm,fs from xsxxb left outer join xscjb
on xsxxb.xh=xscjb.xh
4.右外部连接:
(与左外部连接相反)
5.完全外部连接:
select xsxxb.xh,xm,fs from xsxxb full join xscjb
on xsxxb.xh=xscjb.xh
6.交叉连接:
select xm,fs from xsxxb cross join xscjb
7.自连接:
select A.xh,A.fs,B.xh from xscjb A join xscjb B
on A.kmbh=B.kmbh
where A.xh>B.xh
8.联合运算符(union):
select xh,xm from xsxxb
union
select xh,xm from xsxxb2
六.数据汇总
1.排序: (Asc升,desc降)
select * from xscjb order by fs Asc
2.分组 group by all 包含不符合指定的where条件的组,但将它们设为null)
select xh,sum(fs) as tol from xscjb
where xh=’004′
group by all xh
3.指定分组后,组的条件(having):
select xh,sum(fs) as tol from xscjb
group by xh
having sum(fs)>80
4.compute汇总:
select xh,fs from xscjb
order by xh
compute sum(fs)
5.compute by汇总:
select xh,fs from xscjb
order by xh
compute sum(fs) by xh
compute sum(fs)
6.rollup和cube函数:
rollup() 对group by子句中的列按不同的等级进行分组.
select xh,sum(fs) as tol from xscjb
group by xh with rollup
cube() 是rollup的扩展.
七.数据的查询
1.SELECT 语句的数据检索形式
1>显示所有列和行:
SELECT * FROM authors
2>显示所有行和特定的列:
SELECT pub_name, city FROM publishers
3>显示限定范围的行:
SELECT stor_id, qty, title_id FROM sales
WHERE qty BETWEEN 20 AND 50
4>显示与值列表相匹配的行:
SELECT * FROM publishers
WHERE state IN (‘CA’, ‘TX’, ‘NY’)
5>根据未知值显示行:
SELECT price, title FROM titles
WHERE price IS NULL
6>显示/隐藏重复的值:
SELECT DISTINCT city FROM authors
7>显示根据多个搜索条件查询的结果:
SELECT title FROM titles
WHERE pub_id = 0736 AND type = ‘business’
2.SELECT 语句中使用的关键字:
BETWEEN 测试值是否在两值之间
EXISTS 测试值是否存在
IN 测试在一定范围内的值
LIKE 测试类似这个值的值
IS NULL 测试是否为 null 值
3.查询通配符的使用:
(%,_,[],^)
1> select * from xsxxb where xm like ‘张%’
2> select * from xsxxb where xm like ‘_花%’
3> select * from xsxxb where xm like ‘_[花娇]%’
4> select * from xsxxb where xm like ‘_[^花娇]%’
4.简单子查询:
1>使用返回单个值的子查询:
select xm,xb,csrq,dh from xsxxb
where xh=(select xh from xscjb where fs=70)
5.相关子查询:
1>使用返回多行单列的子查询:
select xm,xb,csrq,dh from xsxxb
where xh in(select xh from xscjb where fs>70)
2>使用exists关键字验证存在性的子查询:
select xm,xb,csrq,dh from xsxxb
where exists (select xh from xscjb where kmbh=’3′ and fs>70
and xh=xsxxb.xh )
3>使用not exists关键字验证存在性的子查询:
select xm,xb,csrq,dh from xsxxb
where not exists (select xh from xscjb where kmbh=’3′ and fs>70
and xh=xsxxb.xh )
八.流程控制语句
1>声明和使用变量:
declare @i int
set @i=3
select @i=(select fs from xscjb where xh=’001′)
2>begin…end 语句块:
begin
print’dfdfdfd’
end
3>条件语句:
if (select fs from xscjb where xh=’002′) >70
begin
print’dfdfedfd’
end
else if (select fs from xscjb where xh=’002′) <60
begin
print’888888′
end
else
begin
print’99999999′
end
4>分支语句:
select gg=case fs ‘gg是别名
when 60 then ‘df’
when 70 then ‘xdf’
when 80 then ‘yb’
when 90 then ‘xgf’
else ‘mf’
end
from xscjb
5>循环语句:
declare @i int
declare @sum int
set @i=0
set @sum=0
while @i<10
begin
set @sum=@sum+@i
set @i=@i+1
end
print @sum
6>标签:
select * from xsxxb
goto A
select * from kmxxb
A:
select * from xscjb
九.视图的使用
1.创建视图:
1>创建基于表中指定列的视图:
create view GetFs
as
select xh,fs from xscjb
2>创建基于表中指定行的视图:
create view GetFs2
as
select xh,fs from xscjb where fs=80
3>创建基于表中汇总信息的视图:
create view GetFs3
as
select xh,sum(fs) as tol from xscjb
group by xh
4>创建基于多个表的视图:
create view GetFs4
as
select xsxxb.xh,xm,kmxxb.kmmc,xscjb.fs from xsxxb,kmxxb,xscjb
where xsxxb.xh=xscjb.xh and xscjb.kmbh=kmxxb.kmbh
5>创建基于视图的视图:
create view GetFs5
as
select * from GetFs4
where fs>75
2.更改视图:
(把create换为alter)
3.删除视图:
1>删除视图中的数据:
delete GetFs2
2>删除视图:
drop view GetFs2
4.通过视图修改数据:
create view GetFs6
as
select xh,xm from xsxxb
1>插入数据:
insert into GetFs6 values(‘005′,’黄三’)
2>更新数据:
update GetFs6 set xh=’006′ where xh=’005′
3>删除数据
delete GetFs6 where xh=’006′
十.存储过程与触发器
1.创建存储过程与执行存储过程:
1>创建一个不带参数的存储过程:
create proc Display_orders
as
select * from orders
2>创建一个带输入参数的存储过程:
create proc Display_orders
@cusid char(20)
as
select * from orders where customerid=@cusid
3>创建一个带输入，输出参数的存储过程:
create proc Display_Name
@Name char(20) output
as
select @Name=(select xm from xsxxb,kmxxb,xscjb where xsxxb.xh=xscjb.xh and
fs=(select max(fs) from xscjb where kmbh=(select kmbh from kmxxb
where kmmc=@Name))
and kmxxb.kmbh=(select kmbh from kmxxb where kmmc=@Name))
from xsxxb,kmxxb,xscjb
print @Name
2.更改存储过程:
(把create换为alter)
3.删除存储过程:
drop proc Display_Name
4.创建触发器:
1>创建INSERT 触发器:
create trigger checkFs
on xscjb
for insert
as
if(select fs from inserted)<50
begin
print’bu neng cha!’
rollback tran
end
2>创建UPDATE 触发器:
create trigger NoUPdateXm
on xsxxb
for update
as
if update(xm)
begin
print’bu neng geng xing xm!’
rollback tran
end
3>创建DELETE 触发器:
create trigger NoDelete002
on xsxxb
for delete
as
if (select xh from deleted)=’002′
begin
print’bu neng shang chu xh wei 002!’
rollback tran
end
5.更改触发器:
(把create换为alter)
6.删除触发器:
drop trigger NoDelete002
7.禁用和启用触发器:
1> 禁用:
alter table xsxxb
disable trigger NoDelete002
2> 启用:
alter table xsxxb
enable trigger NoDelete002
十一.用户自定义函数
1.创建用户自定义函数:
1>创建数量型用户自定义函数：（返回一个简单的数值,如：int,char等）
create function NumAdd
(@num1 int,@num2 int)
returns int
as
begin
return(@num1+@num2)
end
调用：select dbo.NumAdd(4.6)
2>创建表值型用户自定义函数:（返回一个Table型数据）
use northwind
go
create function DisplayXsxx
(@xh char(20))
returns table
as
return(select * from xsxxb where xh=@xh)
调用：select * from DisplayXsxx(‘002’)
2.更改用户自定义函数:
(把create换为alter)
3.删除用户自定义函数:
drop function DisplayXsxx
十二.游标
1.创建游标:
declare Fs_level cursor
static
for select xm,sum(fs) from xsxxb,xscjb where xsxxb.xh=xscjb.xh group by xm
declare
@fs int,
@Name varchar(20)
2.打开游标:
open Fs_level
3.提取游标:
fetch next from Fs_level into @Name,@fs
while(@@Fetch_status=0)
begin
if @fs<150
print’总分太低！’
else
print’高分！’
fetch next from Fs_level into @Name,@fs
end
4.关闭游标:
close Fs_level
5.销毁游标:
deallocate Fs_level
十三.数据完整性
1.缺省
1>创建缺省:
create default dd
as ‘MN’
2>绑定缺省:
sp_bindefault dd,’xsxxb.xh’
3>取消缺省:
sp_unbindefault ‘xsxxb.xh’
4>删除缺省：
drop default dd
2.规则
1>创建规则:
create rule rr
as @scode in(‘MN’,’ND’,’SD’)
2>绑定规则:
sp_binderule rr, ‘xsxxb.xh’
3>取消规则:
sp_unbindrule ‘xsxxb.xh’
4>删除规则：
drop rule rr
3.约束
1>主键约束:
primary key
2>外键约束:
foreign key(列名) references 表名
3>唯一约束:
unique
4>检查约束:
check(xb=’男’ or xb=’女’)
十四.数据库的安全性
1.帐户
1>创建一个帐户:
sp_addlogin ‘zj’,’0822′,’pubs’
2>查询帐户的相关信息:
select convert(varbinary(32),password) from syslogins where name=’zj’
3>更改，删除帐户:
sp_password ‘0822’,’888′,’zj’
2.数据库用户
1>添加数据库用户
use northwind
go
sp_grantdbaccess zj
2>删除数据库用户
use northwind
go
sp_revokedbaccess [zj]
3.角色
1>浏览服务器角色的内容：
sp_helpsrvrole
2>角色分配给帐户：
sp_addsrvrolemember zj,’sysadmin’
4.权限
1>授予权限:
use northwind
go
grant insert,update on xsxxb to zj
2>撤消权限：
revoke create table,create view from zj
3>拒绝访问:
use northwind
go
deny select,insert,update ,delete on xsxxb to zj
十五.事务与锁
1.事务
1>一个标准事务:
begin tran
select * from xsxxb
commit tran
或
begin tran insert xscjb
insert into xscjb values(‘002′,’2’,70)
commit tran
2>返回几个事务在运行:
begin tran
select * from xsxxb
select * from kmxxb
select @@trancount –执行第一次时返回值为1，每执行一次事务数量就加1。
commit tran
select @@trancount –返回值为0。
3>复杂可回滚事务:
declare @i int
set @i=0
print ltrim(str(@i))
begin tran
print ltrim(str(@i))
select @i=(select count(*) from xsxxb)
if @i>4
begin
rollback tran
return –停止过程中当前批的执行，并在下一批的开始处恢复执行。
end
else
print ltrim(str(@i))
select * from xsxxb
select @@trancount
begin tran –嵌套事务
select * from xscjb
select @@trancount
commit tran
commit tran
4>嵌套事务:
declare @i int
set @i=0
print ltrim(str(@i))
begin tran
print ltrim(str(@i))
select @i=(select count(*) from xsxxb)
if @i>4 –改为3试一试
begin
rollback tran
return –停止过程中当前批的执行，并在下一批的开始处恢复执行。
end
else
print ltrim(str(@i))
select * from xsxxb
select @@trancount
begin tran –嵌套事务
select * from xscjb
select @@trancount
commit tran
commit tran
5>与表相联系的事务:
declare @i int
set @i=0
begin tran
update xscjb set fs=85 where fs=70
set @i=2
if @i>1 –改为3试一试
begin
rollback tran
return
end
else
commit tran
go
select * from xscjb
6>设置保存点:
declare @i int
set @i=0
begin tran
update xscjb set fs=120 where fs=90
save tran s1
set @i=2
if @i>1
begin
rollback tran s1
return
end
else
commit tran
go
select * from xscjb
7>含子查询的事务:
begin tran
declare @fs int
update xscjb set fs=95 where fs=90
select @fs=(select max(fs) from xscjb)
if @fs<100 –改为90试一试
begin
rollback tran
return
end
else
commit tran
go
select * from xscjb
8>隐式事务:
[im’plisit]暗示的
set implicit_transactions on –打开
update xscjb set fs=95 where fs=90
select @@trancount
go
select * from xscjb
2.锁
事务的隔离级别：
1>读提交：
第一个窗口：
begin tran
update xscjb set fs=95 where xh=’002′
第二个窗口:
set transaction isolation level read committed
go
select * from xscjb where xh=’002′
2>读未提交:
第一个窗口：
begin tran
update xscjb set fs=80 where xh=’002′
第二个窗口:
set transaction isolation level read uncommitted
go
select * from xscjb
3>可重复读:
第一个窗口：
set transaction isolation level repeatable read
go
begin tran
select * from xscjb
update xscjb set fs=100 where xh=’002′
select * from xscjb
rollback
第二个窗口:
set transaction isolation level read committed
go
begin tran
insert into xscjb values(‘002′,’2’,120)
select * from xscjb where fs=120
rollback
4>顺序读:
第一个窗口：
set transaction isolation level serializable
go
begin tran
select * from xscjb
update xscjb set fs=100 where xh=’002′
select * from xscjb
第二个窗口:
set transaction isolation level read committed
go
begin tran
insert into xscjb values(‘002′,’2’,120)
select * from xscjb where fs=120
1. STATUS;
2. mysql> SELECT @@global.time_zone, @@session.time_zone;
+——————–+———————+
| @@global.time_zone | @@session.time_zone |
+——————–+———————+
| SYSTEM | SYSTEM |
+——————–+———————+
1 row in set (0.00 sec)
3.
mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`i` int(11) default NULL,
`j` int(11) NOT NULL, `k` int(11) default ‘-1’
) ENGINE=MyISAM DEFAULT CHARSET=latin1
4. SHOW DATABASES LIKE ‘m%’;
5.SHOW WARNINGS\G显示错误
复制创建表技巧
6. CREATE TABLE CityCopy1 SELECT * FROM City;复制表
7. CREATE TABLE CityCopy2 SELECT * FROM City where id=5;
按条件复制表:将city 表格的结构复制同时复制id=5的记录
8. CREATE TABLE CityCopy3 SELECT title FROM City where id=5;
title(是字段)指定city表中的title字段复制创建成CityCopy3表
9.重命名
(1) ALTER TABLE t1 RENAME TO t2;
(2)Rename tabae t1 to t2;
(2) RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2; 批量命名
10.删表
(1)DROP TABLE IF EXISTS t1;或者DROP TABLE t1
(2)DROP TABLE t1, t2, t3; 批量删表
11.清空表记录
(1) DELETE FROM t;或者TRUNCATE TABLE t;
(2) DELETE FROM t WHERE id=5;指定条件删除
删除表
DELETE FROM table_name;
TRUNCATE TABLE table_name;
12.select 另类用法
(1) mysql> SELECT 1 AS One, 4*3 ‘Four Times Three’;
+—–+——————+
| One | Four Times Three |
+—–+——————+
| 1 | 12 |
+—–+——————+
(2)SELECT last_name, first_name FROM t ORDER BY 1, 2;
排序ORDER BY 1, 2升序;2,1降序
(3) mysql> SELECT description, IF(‘abc’ LIKE pattern,’YES’,’NO’) FROM patlist;
在对pattern字段中别条件
+——————————–+———————————–+
| description | IF(‘abc’ LIKE pattern,’YES’,’NO’) |
+——————————–+———————————–+
| empty string | NO |
| non-empty string | YES |
| string of exactly 3 characters | YES |
+——————————–+———————————–+
(4) SELECT ABS(-14.7), ABS(14.7);
(5) select * from tablename order by data desc limit 0,20返回20条数据(同微软数据库中select top 20 * from tablename 一样)
select * from tablename limit 0,20
说明：limit 0,20 （0是从表的第一行开始，是可以指定的，20是查询返回20条记录）
13.数据库加密
SELECT MD5(‘a’);
mysql> SELECT MD5(‘a’);
+———————————-+
| MD5(‘a’) |
+———————————-+
| 0cc175b9c0f1b6a831c399e269772661 |
+———————————-+
14.随机数
SELECT RAND();
15. INSERT插入值的技术
(1) INSERT INTO people (name,age)VALUES(‘William’,25),(‘Bart’,15),(‘Mary’,12);多重插入
(2)INSERT INTO people VALUES(25,’William’);不用中间的字段名字
(3) INSERT INTO t (i) VALUES(1),(2),(3),(4),(5);
16.数据替换
(1) REPLACE INTO people (id,name,age) VALUES(12,’William’,25);
(2) 多重替换
REPLACE INTO people (id,name,age)VALUES(12,’William’,25),(13,’Bart’,15),(14,’Mary’,12);
(3)