SQL初学者的极教材.doc

SQL初学者的终极教材 。 use master go --创建数据库book_manage create database book_manage on ( name = book_manage_primary, filename = d:\data\book_manage.mdf, size = 10, maxsize = 20, filegrowth = 5 ) log on ( name = book_manage_log, filename = d:\data\book_manage.ldf, size = 5, maxsize = 20, filegrowth = 5 ) go --查看book_manage数据库信息 exec sp_helpdb book_manage --修改数据库日志文件扩展空间 alter database book_manage modify file ( name = book_manage_log, filegrowth = 5 ) --创建新表 use book_manage go create table tb_bookinfo ( book_ID char(6) not null, bookname char(30) not null, price decimal(18,2) not null, authorID char(4), publishID char(4) ) create table tb_authorinfo ( authorID char(4) not null, authorname char(20) not null, sex char(2), age tinyint, authaddress char(30) ) go create table tb_pubinfo ( publishID char(4) not null, pubname char(20) not null, pubaddress char(30) ) create table temp1 ( temID char(4) not null, temname varchar(30) not null default 默认名称 ) create table tbl_a ( emp_id char(2) not null, emp_name char(10) not null, emp_age char(2) ) create table tbl_b ( emp_id char(2) not null, spend char(10) not null, level char(10) ) --删除表 drop table temp1 --添加tb_authorinfo表信息 insert into tb_bookinfo values(000008,asp.net,54.5,A004,P106) insert into tb_authorinfo values(A004,黄薇,男,25,济南) insert into tb_pubinfo values(P106,山东出版社,济南) --查询各个表信息 select * from tb_bookinfo select * from tb_pubinfo select * from tb_authorinfo --备份一个新表 select * into Btb_bookinfo from tb_bookinfo select * into Btb_authorinfo from tb_authorinfo select * into Btb_pubinfo from tb_pubinfo --查看单个表信息 exec sp_help tb_authorinfo --修改表,在temp1中添加一列 alter table temp1 add sage char(2) --删除temp1中的以列 alter table temp1 drop column col_new --添加一个约束 alter table temp1 add col_new varchar(20) null constraint tem_unique unique --升序 --修改类型 (有错误) --alter table temp1 modify sage bit --唯一元素( distinct ) 属性 select distinct publishid from tb_bookinfo select publishid from tb_bookinfo select top 3 b