--1、连接到master数据库上
use master;
--2、删除数据库dwl
drop database dwl;
--3、建立数据库dwl
create database dwl on
primary
(name = 'dwl_data',filename = 'e:\database\dwl_data.mdf',size = 3mb,filegrowth = 2mb),
(name = 'dwl_data1',filename = 'e:\database\dwl_data1.ndf',size = 3mb,filegrowth = 2mb)
log on
(name = 'dwl_log',filename = 'e:\database\dwl_log.ldf',size = 3mb,filegrowth = 2mb),
(name = 'dwl_log1',filename = 'e:\database\dwl_log1.ldf',size = 3mb,filegrowth = 2mb)
---4、查询数据库的内容
sp_helpdb dwl
---5、增加数据库中数据文件的大小
alter database dwl
modify file
(name = dwl_data,
filename = 'd:\database\dwl_data.mdf',
size = 5mb,maxsize =15mb);
---6、增加数据库中的数据文件
alter database dwl
add file
(name = dwl_data2,
filename = 'd:\database\dwl_data2.ndf',
size = 2mb,maxsize = 10mb);
---7、增加数据库中的日志文件
alter database dwl
add log file
(name = dwl_log3,
filename = 'd:\database\dwl_log3.ldf',
size = 2mb,maxsize = 10mb);
---8、将数据库缩小到原来的10%
dbcc shrinkdatabase(dwl,10)
----9、将数据库中dwl_data3.ndf缩水到3m
dbcc shrinkfile(dwl_data3,3mb)
----10、删除数据库中的数据文件
alter database dwl
remove file dwl_data3;
----10.5、更改数据库的名字
sp_renamedb ddd;
----10.6、分离数据库dwl
sp_detach_db dwl;
----10.7、附加数据库dwl
sp_attach_db dwl,'d:\database\dwl_data.mdf';
--11、表1：tbl_yhjbxx----纪录本人及所有使用者的信息
create table tbl_yhjbxx
(
Yhbh    varchar(3)    not null,      --用户编号
yhmc    varchar(10)  null,          --用户名称
xb      varchar(2)    null,          --性别
jg      varchar(30)  null,          --籍贯
gly      varchar(3)    null,          --管理员标志（0：管理员，1：普通用户）
zzmm    varchar(3)    null,          --政治面貌
gx      varchar(10)  null,          --与本人关系
bz      varchar(3)    null,          --备注
constraint pk_yhjbxx primary key(yhbh)
);
--12、表2：tbl_pass  ---用户密码
create table tbl_password
(
Yhbh      varchar(3)  not null,  --用户编号
pass      varchar(10)  null,      --密码
bsm      varchar(10)  null,      --标识码
mmwt      varchar(20)  not null,  --密码问题
mmwtda    varchar(20)  not null,  --密码问题答案
bz        varchar(3)  null,      --备注
constraint pk_password primary key(yhbh),
constraint fk_yhjbxx_yhbh foreign key(yhbh) references tbl_yhjbxx(yhbh));
----13、向tbl_yhjbxx中插入数据
insert into tbl_yhjbxx (yhbh,yhmc,xb,jg,gx) values('01','王广智','男','山东','本人');
----14、向一个表中插入多行数据
----提取tbl_zcxx表中的部分数据插入到tbl_srxx表中
insert into tbl_zcxx (yhbh,zcsj,zclb,zcje) select yhbh,srsj,srlb,srje from tbl_srxx;
---15、在表中增加一个新的字段lxdh（）
alter table tbl_yhjbxx add lxdh varchar(10);
---16、修改原有字段lxdh()的数据类型，将数据长度从10增加到20个字符长。
alter table tbl_yhjbxx alter column lxdh varchar(20);
---17、为性别字段增加一个约束：（男：m，女：n）。
alter table tbl_yhjbxx add constraint ck_yhjbxx_xb check(xb='m' or xb = 'n');
---18、删除表中的列
alter table tbl_yhjbxx drop column lxdh;
---19、在表中增加一个外键约束
alter table tbl_srxx add constraint fk_srxx foreign key(srlb) references tbl_srlb(srlbbh)
---20、在表中增加一个主键约束
alter table tbl_srxx add constraint pk_srxx primary key(lsh)
---21删除表中的主键
alter table tbl_srxx drop constraint pk_srxx;
-
--22修改表中字段lsh的约束从null到not null
alter table tbl_srxx alter column lsh int not null;
--23将yhsr默认为缺省值1900-00-00
alter table tbl_yhjbxx add constraint df_yhsr default '1900-00-00' for yhsr;
---24设置yhnl(用户年龄)检查约束(yhnl>0)
alter table tbl_yhjbxx add constraint chk_yhnl check(yhnl>0);
---为tbl_srxx表中zcsj字段增加检查约束zcsj>srsj;
alter table tbl_srxx add constraint chk_srxx_zcsj check(zcsj>srsj);
---25修改表的名字。
sp_rename tbl_yhjbxx，tbl_yhxx;
---26修改表中列名
sp_rename 'tbl_yhjbxx.bz','beizhu'
---26.5为tbl_yhjbxx 增加一个唯一性约束uk_sfzh(身份证号)
alter table tbl_yhjbxx add constraint uk_sfzh unique(sfzh)
---27删除表
drop table tbl_yhjbxx;
基本的sql语句有哪些
---28查看表的定义可以使用存储过程sp_help
sp_help tbl_yhjbxx;
---29删除表中yhbh 为'08' 的数据
delete tbl_srxx where yhbh = '08';
---30删除表中所有的数据
delete tbl_srxx;
-
--31修改tbl_srxx 表，将yhbh = '01' 的srje修改成500元。
update tbl_srxx set srje = 500 where yhbh = '01';
---32修改yhbh 为‘01’的srsj=2008年6月1日
update tbl_srxx set srsj = '2008-06-01'
where yhbh ='01';
----33查询表上的所有约束
sp_helpconstraint tbl_yhjbxx;
-------------查询表中的数据----------------------
---34查询表中所有的数据
select * from tbl_yhjbxx;  --- "*" 代表所有的数据
---35查询表中字段 yhbh,yhmc 的数据
select yhbh，yhmc from tbl_yhjbxx;  --- "*" 代表所有的数据
---36查询返回结果集的前3行
select top 2 yhbh,yhmc from tbl_yhjbxx;
---37查询：将srje加上100后的结果。
select srje+100 from tbl_srxx;
---38小写转换函数lower()
select lower(yhxb) from tbl_yhjbxx;
---39大写转换函数upper()
select upper(yhxb) from tbl_yhjbxx;
---40给列加上别名的三种方法
select srje*0.1  '奖金' from tbl_srxx;
select srje*0.1 as '奖金' from tbl_srxx;
select '奖金' =  srje*0.1 from tbl_srxx;
---41将两个字段yhbh
和yhmc并到一起的语句
select yhbh+yhmc from tbl_yhjbxx;
---42将数字转换成字符串后进行合并--
alter table tbl_yhjbxx alter column bz numeric(6,2)
update tbl_yhjbxx set bz = 1;
select str(bz)  + yhmc from tbl_yhjbxx;
--思考如不进行转换会出现什么情况？？？？？
---43带有查询条件的语句
select yhbh,srlb,srje from tbl_srxx where yhbh = '01';
select yhbh,srlb,srje from tbl_srxx where yhbh = '01' and srlb = '1';
---44查询在2008年5月以后的收入明细(利用时间进行比较)
select * from tbl_srxx where srsj>'2005-05-31'；
---查询不重复的纪录
---45查询不重复的用户编号(查询表中有多少用户?)
select distinct(yhbh) from tbl_srxx;
------带有函数的查询------------------
---46查询系统时间
select getdate();
---47查询总收入
select sum(srje) from tbl_srxx;
---48查询yhbh为'01'的总收入
select sum(srje) from tbl_srxx where yhbh = '01';
---49查询每一次收入的平均值
select avg(srje) from tbl_srxx;
---50查询tbl_srxx表中收入金额最大的值
select max(srje) from tbl_srxx;
---51查询tbl_srxx表中收入金额最小的值
select min(srje) from tbl_srxx;
---52查询tbl_srxx表中一共有多少条记录？
select count(*) from tbl_srxx;
-
--53查询tbl_srxx表中yhbh为‘01’的用户一共有多少条记录？
select count(*) from tbl_srxx wehre yhbh = '01';
---54查询yhbh为'01'的这个用户的每一次收入的平均值
select avg(srje) from tbl_srxx where yhbh = '01';
---55查询收入信息，按照收入多少(srje)进行排序
select * from tbl_srxx order by srje;
---56在tbl_srxx 中查询srsj为空的数据
select * from tbl_srxx where srsj is null;
---57在tbl_srxx中查询srsj在2008年3月1日以后的所有记录。
select * from tbl_srxx where srsj > '2008-03-01';
---58在tbl_srxx中查询srsj在2008年3月1日到2008年12月31日之间的所有记录。
select * from tbl_srxx where srsj between '2008-03-01' and '2008-12-31';
----59查询年龄在50和60之间的人员信息
select * from tbl_yhjbxx where yhnl between 50 and 60;
----60查询年龄不在50和60之间的人员信息
select * from tbl_yhjbxx where yhnl between 50 and 60;
---61在tbl_yhjbxx 中查询姓‘张’的所有人员。
select * from tbl_yhjbxx where yhmc like '王%';
---62查询在地址在 '北京' '上海' '深圳' 的用户
select * from tbl_yhjbxx where yhdz in ('北京', '上海', '深圳');
---查询yhbh为01，03，05 的所有的收入信息
select * from tbl_srxx yhbh in ('01','03','05');
-
--63在tbl_srxx中查询当月的所有收入记录。
select * from tbl_srxx where year(srsj)=year(getdate()) and month(srsj)=Month(getdate())
---64将日期类型转换成字符串（注：112 为国际标准格式yyyymmdd,conver为字符转换函数）
select convert(ch
ar,getdate(),112) ;
---65将日期类型转换成字符串，并截取当月月份（注：substring()为字符串截取函数）。
select substring(convert(char,getdate(),112),5,2) ;
--------分组查询--------
---66查询每个用户的总收入(按照用户编号进行分组)
select yhbh,sum(srje) from tbl_srxx group by yhbh;
---在分组后的集合中的建立的子查询
-
--67查询收入大于1000元的用户。
select yhbh,sum(srje) from tbl_srxx group by yhbh having sum(srje)>1000;
-------子查询-------
---68查询 srje大于平均收入的所有记录。
select * from tbl_srxx where srje > (select avg(srje) from tbl_srxx);
---多表联合查询
---69把tbl_srxx和tbl_yhjbxx进行连接
select a.yhbh,b.yhmc,a.srje from tbl_srxx a,tbl_yhjbxx b where a.yhbh = b.yhbh;
---70把tbl_srxx和tbl_srlb进行连接
select a.yhbh,a.srlb,b.srlbmc,a.srje from tbl_srxx a,tbl_srlb b where a.srlb = b.srlbbh;
----左外部连接
-
---71不限制左边表的数据
select * from tbl_yhjbxx a left join tbl_password b on  a.yhbh = b.yhbh;
----左外部连接，不限制左边表的数据
----72查询在基本信息表中有的人员而在密码表中没有的人员
select * from tbl_yhjbxx a left join tbl_password b on a.yhbh = b.yhbh where b.yhbh is null;
----右外部连接
----73不限制右边表的数据
select * from tbl_yhjbxx a right join tbl_password b on  a.yhbh = b.yhbh;
---74查询在tbl_srxx表中有的yhbh，在tbl_yhjbxx表中没有的用户信息。
select * from tbl_yhjbxx a left join tbl_srxx b on a.yhbh = b.yhbh where b.yhbh is null;
----全外部连接
-
---75不限制右边表页不限制右边表的数据
select * from tbl_yhjbxx a right join tbl_password b on  a.yhbh = b.yhbh;
---76查询总收入大于1000元的用户叫什么名字？
select a.sr,b.yhmc from (select a.yhbh,sum(a.srje) sr from tbl_srxx a group by a.yhbh having sum(a.srje)>1000) a,tbl_yhjbxx b where a.yhbh = b.yhbh;
----union操作符
----77将两个结果集连接到一起
select * from tbl_srxx
union
select * from tbl_zcxx;
----78利用tbl_yhjbxx表中的字段创建一个新表tbl_bak
select * into tbl_bak from tbl_yhjbxx;
------------视图----------
----79创建视图
create view  view_srxx as select * from tbl_srxx where yhbh = '01';
--------------------索引------------
---80在tbl_yhjbxx表上的yhmc字段上创建索引
create index ix_yhjbxx on tbl_yhjbxx(yhmc)
---81在tbl_yhjbxx表上的yhmc字段上创建聚集索引
create clustered index ix_yhjbxx_yhmc on tbl_yhjbxx(yhmc);
---82在tbl_yhjbxx 表中为yhbh创建唯一索引.
create unique index ix_yhjbxx_sfzh on tbl_yhjbxx(sfzh);
---82.1更改tbl_yhjbxx表中索引ix_yhjbxx_sfzh的名字为：ix_sfzh
sp_rename 'tbl_yhjbxx.ix_yhjbxx_sfzh','ix_sfzh','INDEX';
---82.2查看tbl_yhjbxx中的索引信息
sp_helpindex tbl_yhjbxx
--83删除
名字为ix_yhjbxx_yhbh索引
drop index ix_yhjbxx_yhbh;
----------------数据库备份与恢复-------------------
----83.1分离数据库
sp_detach_db dwl;
----83.2附件数据库
sp_attach_db dwl,'d:\database\dwl_data.mdf';
-
---83.3备份数据库dwl，完整备份
backup database dwl to disk = 'd:\database\bak\dwl_bak.bak';
----83.4备份数据库dwl，差异备份
backup database dwl to disk = 'd:\database\bak\dwl_diff_bak0909.bak'
with  differential;
----83.5备份数据库dwl，备份文件或文件组
backup database dwl
'd:\database\dwl_data.mdf'
to disk = 'd:\database\bak\dwl_data_bak.bak';
----83.6备份数据库dwl，备份事务日志
backup log dwl
to disk = 'd:\database\bak\dwl_log_bak.bak';
----83.7恢复数据库dwl，完整恢复
restore database dwl
from disk = 'd:\database\bak\dwl_bak.bak'
with replace,norecovery;
--恢复差异备份
restore database dwl
from disk = 'd:\database\bak\dwl_diff_bak0909.bak'
with replace,norecovery;
--恢复日志
restore database dwl
from disk = 'd:\database\bak\dwl_log_bak.bak'
with replace,recovery;
------------------存储过程------------------------
--普通存储过程
--84建立一个向tbl_srxx表插入数据的存储过程
CREATE PROCEDURE PRO_SRXX
as
insert into tbl_srxx(yhbh,srsj,srlb,srje)
values('01',getdate(),1,100);
---85修改存数过程pro_srxx
alter procedure pro_srxx
as
insert into tbl_srxx(yhbh,srsj,srlb,srje)
values('11',getdate(),1,100);
---86执行存储过程 pro_srxx
exec pro_srxx;
---带有输入变量的存储过程
---87建立一个向tbl_srxx表插入数据的存储过程
CREATE  PROCEDURE PRO_SRXX1
@V_YHBH  VARCHAR,
@V_SRLB  numeric,
@v_srje  numeric
as
insert into tbl_srxx(yhbh,srsj,srlb,srje)
values(@v_yhbh,getdate(),@v_srlb,@v_srje);
---88执行存储过程 pro_srxx1
exec pro_srxx1 '2',1,12;
exec pro_srxx1 @v_yhbh = '3',@v_srlb = 1,@v_srxx = 10;
---89带有输入、输出变量的存储过程
CREATE  PROCEDURE PRO_SRXX2
@V_YHBH VARCHAR, 
@v_srje numeric output
as
select @v_srje = sum(srje) from tbl_srxx where yhbh = @v_yhbh;
---90执行存储过程 pro_srxx1
declare @v_srje numeric
exec pro_srxx2 '02',@v_srje output
select @v_srje
---91判断pro_srxx4存储过程是否存在，如存在将它删除掉。
if exists (select name from sysobjects where name = 'pro_srxx4' and type = 'P')
drop procedure pro_srxx4
go
use dwl
go
-
--92完整存储过程：向tbl_srxx表插入数据的同时更新tbl_srzcphb.
CREATE  PROCEDURE PRO_SRXX4
(
@V_YHBH  varchar(3),
@V_SRLB  numeric,
@v_srje  numeric
)
as
declare
@v_zsr numeric,
@v_zzc numeric,
@v_ye  numeric
--set @v_zsr = 0,
--set @v_zzc = 0,
--set @v_ye  = 0
insert into tbl_srxx(yhbh,srsj,srlb,srje)
values(@v_yhbh,getdate(),@v_srlb,@v_srje);
select @v_z

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。