SQLServer 2008 (六) 基础总结

对视图的操作：
>>>>>创建视图：
if exists(select name from sysobjects where name='v_student' and xtype='v')
drop view v_student


create view v_student 
as select sid as 学号,sname 姓名 from student
go
------------------------------------------------------------------
对存储过程procedure的操作：
>>>>>创建procedure
if exists(select name from sysobjects where name='show_item' and xtype='p')
drop procedure show_item


create procedure show_item @tbname nvarchar(30)
as
begin
declare @str nvarchar(200);
set @str = 'select * fom ' +@tbname
execute (@str,)
end
go


>>>>>调用procedure
execute show_item sales
go


>>>>>分页procedure
create procedure split_page 
@tbname nvarchar(30),
@id nvarchar(20),
@start int,
@end int
as
begin
declare @str nvarchar(300);
set @str=' select * from 
(
select row_number() over(order by '+@id+') as rowid,* from '+@tbname
+') as temp
where 
rowid between '+cast(@start as varchar)+' and '+cast(@end as varchar)
execute (@str)
end
go








------------------------------------------------------------------
对数据库的操作:
>>>>>创建数据库:
if exists(select name from sysdatabases where name='mydb')
drop database mydb
go
create databASE mydb on primary
(
name='mydb_dat',  --逻辑名称
filename='e:\db\mydb.mdf',  --物理名称
size=5Mb, --最小值
maxsize=20mb, --最大值
filegrowth=10%--2mb
)log on
(
name='mydb_log_dat',  --逻辑名称
filename='f:\db\mydb_log.ldf',  --物理名称
size=2Mb, --最小值
maxsize=5mb, --最大值
filegrowth=1mb
)
go


>>>>>创建模式：
create schema class1


>>>>>附加数据库
execute sp_attach_db @dbname='pubs',
@filename1='d:\abc.mdf',
@filename2='d:\abc.ldf',
go


>>>>>分离数据库
execute sp_detach_db 'pubs','true'
go


>>>>>查看数据库信息：
execute sp_hepldb mydb
go


>>>>>数据库重命名
execute sp_renamedb 'jobs','工作数据库'
go


------------------------------------------------------------------
对表的操作：
>>>>>创建表：
if exists(select name from sysobjects where name='sales' and xtype='u')
drop table sales


create table sales(
id int not null constraint pk_id primary key,
name nvarchar(3) constraint df_name default 'name',
phone char(11) constraint uq_phone unique,
gender char(5) constraint ck_gender check(gender in ('男','女'))，
age int check(age>0 and age<120),
worktime int check(worktime between 0 and 10),
workernumber char(5) check(workernumber like '[0-9]{5}'),
)
go


>>>>>查询表的信息：
execute sp_help mytable


>>>>>对表重命名：
execute sp_rename 'jobs','工作表'
go


>>>>>创建表，列相关：
create table score(
chinses int not null,
math int not null,
english int not null,
total_score as(chinese+math+english),
average as(chinese+math+english)/3.0,
)
go


>>>>>表的拷贝
1.数据库内拷贝：
select * into student_copy from student
go
2.跨数据库拷贝：
select * into student_copy from mydb.dbo.student
go


>>>>>添加单表约束：
create table sales(
id int identity(1,1) not null constraint pk_id primary key ,
name nvarchar(3) constraint df_name default 'name',
enroltime datetime default getdate(),
phone char(11) constraint uq_phone unique,
gender char(5) constraint ck_gender check(gender in ('男','女'))，
age int check(age>0 and age<120),
worktime int check(worktime between 0 and 10),
workernumber char(5) check(workernumber like '[0-9]{5}'),
)
go


>>>>>添加外键约束：
create table sales(
saleid int identity primary key,
item char(3) not null constraint fk_item foreign key references item(itemid) on delete cascade on update cascade,
)


>>>>>追加约束：
alter table jobs
add
primary key(job_id),
default 'default information' for job_desc,
unique(job_title),
check(woker_age between 1 and 120)
go


>>>>>删除约束：
alter table jobs
drop constraint [约束名]
go


------------------------------------------------------------------
对表中数据的操作：


>>>>>修改列名：
execute sp_rename 'jobs.job_id','编号','column'


>>>>>单表无条件查询查询：
select * from jobs
go
>>>>>单表有条件查询：
1.select * from employees where reportsto is null
  go
2.select * from employees where reportsto is not null
  go
3.select count(*) from orders where employeeid=5
  go
4.(注意查询语句执行先后顺序)
  select employeeid 工号,count(*) 订单数 --4
  from orders --1
  group by employeeid --2
  having count(*)>100 --3
  order by 订单数 asc--desc --5
  go
5.(注意select的套用)
  select * from jos
  where max_lvl=(select max(max_lvl) from jobs)
  go
6.select * from authors 
  where state in('俄亥俄','纽约','华盛顿','加州')
  go
7.select * from student
   where sex='女' and name like'兰%'
   go
8.select top 4 * from sysobjects
   go
9.select top 4 name,id from sysobjects
   go
10.select top 5% name,id from sysobjects
   go
11.select top 5 au_id,au_fname+' '+au_lname as 'au_name' from authors
   go
12.select * from authors where substring(au_lname,1,1)='s'
   go
13.select * from authors where len(au_fname)>7
   go
14.select id,name,phone,address,city,zip
   from (select row_number() over(order by id) as rowid, *from authors) as temp 
   where rowid between 4 and 6 
   go




>>>>>级联查询(注意使用别名)：
select sid,sname 
from student as s,teacher as t,teacher_student as ts
where
t.tname='李老师'
and
t.tid=ts.teacher_id
and
s.sid=ts.student.id
go




>>>>>插入一条记录：
1.insert into sales(itemid,quantity) values('001',2)
go
2.添加一行记录，有一个’member’,要求密码中的六个数字是用随机函数产生。
declare @password varchar(6);
declare @digit char(1);
declare @i int;
select @i=0,@password='';
while @i<6
begin
set @digit=cast(floor(rand()*10) as char(1));
set @password = @password + @digit;
set @i = @i + 1;
end
insert into users(username, password) values('mumber',@password)
go
3.一个电话局用来生成北京市的电话号码10000个。
declare @i int, @end int;
select @end = 1000, @i = 0;
while@<@end
begin
declare @phone varchar(12);
set @phone = '010-';
set @phone=@phone + cast(1+floor(rand()*10) as char(1))
+cast(floor(rand()*10) as char(1))
+cast(floor(rand()*10) as char(1))
+cast(floor(rand()*10) as char(1))
+cast(floor(rand()*10) as char(1))
+cast(floor(rand()*10) as char(1))
+cast(floor(rand()*10) as char(1))
+cast(floor(rand()*10) as char(1))
insert into phone(phone) values(@phone)
set @i = @ + 1;
end
go
4.编号50个，用100以内的随机数表示；成绩在0-100之间，包括两者。
while(select count(*) from score)<>50
begin
declare @score int,@id int;
set @id=1+floor(rand()*100);
set @score=ceiling(rand()*100);
insert into score values(@id,@score)
end
go


>>>>>删除记录(注意级联删除)：
delete from item where (删除条件)
go


>>>>>更新一条记录
update item set itemid='012' where itemid='002'
go


>>>>>向表中批量插入记录：
bulk insert item from 'd:\abc.sql'
with(
fieldterminator=',',
rowterminator='\n'
)
go


------------------------------------------------------------------
各类函数：
>>>>>统计函数：
1.最小函数：min([列名])
2.最大函数：max([列名])
3.平均函数：avg([列名])
4.求和函数：sum([列名])
5.行记录数：count(*),count([列名]),后者不统计空值所在行
5.pi()
7.power()
8.square()
9.log() log10()
10.rand()
11.ceiling() floor()
12.left()   right()
13.len()
14.ascii() unicode()
   char() nchar()
15.reverse()
16.substring()
17.cast(123 as char(3))


------------------------------------------------------------------
运算符：
1.四则运算： + - * / %
2.逻辑运算： and or not
3.比较运算： > < >= <= = != <> 
4.范围运算：[]




------------------------------------------------------------------
数据类型：
1.整型：
bigbit 
int
smallint
tinyint
bit
2.实型：
real
float
3.时间与日期：getdate()
datetime
smalldatetime
4.货币：
money
smallmoney
5.字符型：
char() varchar()text
nchar() nvarchar()ntext
6.唯一标识符：newid()
uniqueidentifier
7.image类型：
图片 流媒体