SQL SERVER学习记录

SQL SERVER学习记录

//创建数据库,请先在D盘手动建立一个 backup的文件夹
create database sl
on (name = 'sl',filename = 'd:\backup\sldata.mdf',size=10,maxsize=50,filegrowth=5mb)
log on
(name = 'sllog',filename = 'd:\backup\sllog.ldf',size=5,maxsize=25,filegrowth=5mb);

--查看指定数据库或所有数据库的信息。
exec sp_helpdb 'sl'

--创建数据库的表段参数
use sl
create table book
(
book_id int identity(1,1) primary key not null,
book_name nvarchar(50),
author_id int,
price money,
publisher nvarchar(20)
)

--插入数据
insert into book(book_name,author_id,price,publisher)
values('Windows 2000 Professional 看图速成','1','30.0','明耀工作室'),
('3D Studio MAX实例精选','2','35.0','明耀工作室'),
('Windows 2000 网络管理','1','45.0','唐唐出版社'),
('MAthematica 4.0入门与提高','1','30.0','东东出版社'),
('书籍','1','30.0','测试删除')

--从其他表插入数据到现有表：

insert into dbo.人员表(CustomerID,CompanyName) 
select customerid,companyname from Northwind.dbo.Customers


create table authors
(
author_id int identity(1,1) primary key not null,
author_name nvarchar(20),
address nvarchar(30),
telphone nvarchar(15)
)

insert into authors(author_name,address,telphone)
values('刘崇儒','北京市海淀区','010-66886688'),
('王小明','北京市东城区','010-66888888'),
('张英魁',null,null)

--查询刚刚已生成的表 
use sl
select * from book

--查询需要的表段
select book_name,price from book

--给表名赋予新的别名
select book_name 书名,price 价格 from book

--用where关键语句来限定查询的条件
select * from book 
where book_name = 'windows 2000 网络管理'

--用order by对结果进行排序，用价格来排序
select * from book order by price desc

--多表查询
select book_name,author_name from book,authors
where book.author_id=authors.author_id

--或用join 内部链接进行查询
select book_name,author_name from book
inner join authors
on book.author_id = authors.author_id

--消除重复行 distinct
select distinct publisher 发行单位 from book

--插入数据 insert into
insert into authors(author_name,address,telphone)
values('李四',null,null)

--删除数据 drop table或truncate table 或删除某一行数据 delete book
drop table book
truncate table book      --立即删除
delete book where book_name = '书籍'

--修改数据 update
update authors set author_name = '王晓明'
where author_name = '王小明'

--使用函数  getdate()  获取当前时间,类型必须为时间类型 例如:date datetime
create table orderform
(
order_id int,
book_id int,
book_number int,
order_date date,
client_id int
)

insert orderform 
values(1,2,50,getdate(),1)

--使用公式 查询书籍数量的总金额
select book.book_name,orderform.book_number,(book.price * orderform.book_number) '总金额:' ,book.book_id ,order_id from orderform,book
where book.book_id=order_id

--删除数据库
use database sl

--或者
drop database sl

--添加一个字段为外键
create table au
(
au_id int not null primary key,
au_name varchar(20),
)

create table sj
(
sj_id int not null primary key,
sj_name varchar(20),
au_id int foreign key references au(au_id)
)

--修改某一个表的字段为：其他表的关联(外键)
alter table book alter column author_id int foreign key references authors(author_id)

--使用变量 如果超出将会被截断
declare @test varchar(10)
set @test = 'Hello Word！'
print @test

--使用变量获取 唯一标识
declare @myid uniqueidentifier
set @myid = newid()
print '本机ID为:' + convert(varchar(255),@myid)

--使用变量 计算书本合计总价
declare @s int, @price money
set @s = 5
set @price =35.0
print '合计总价为:' + convert(varchar(255),@s*@price)

--while语句 计算1-100的和
declare @c int,@h int
set @c = 0
set @h = 0
while @c <= 100
begin
set @h = @h +@c
set @c = @c +1
end

print cast(@h as varchar(25))
print '1-100的和为：'+convert(char(25),@h)
--或者使用游标
declare @c int,@h int
set @c = 0
set @h = 0
yb:
set @h = @h +@c
set @c = @c +1
while @c <=100
goto yb

print cast(@h as char(25))
print '1-100的和为：'+convert(char(25),@h)

--if begin语句
declare @price float
set @price = 50
if(select price from book where book_name like '%网络%' )>@price
begin
print '这本书太贵了'
end
else
begin
print '这边性价比还可以'
end

--case语句
select book_name 书名,price 价格,
case
when price >= 45 then'太贵了我买不起'
when price >= 35 then '还可以考虑一下'
else '挺便宜的，可以买一本'
end as 想法
from book

--return语句
--创建存储过程
USE sl
GO
CREATE PROC pro @bookname CHAR(50)
AS
IF(SELECT price FROM book WHERE book_name LIKE @bookname)>=50
RETURN 1
ELSE
RETURN 2
--使用存储过程

DECLARE @return_value int
EXEC @return_value=pro '%网络管理%'
IF @return_value=1
PRINT '这本书太贵了!'
ELSE
PRINT '这本书还可以 考虑考虑！'
GO

--自定义函数
CREATE FUNCTION lf(
@higth DECIMAL(4,1),
@width decimal(4,1),
@height DECIMAL(4,1)
)
RETURNS DECIMAL(12,4)
AS
BEGIN
RETURN (@higth * @width * @height)
END
GO

PRINT '立方：' + CONVERT(CHAR(25),dbo.lf(10,2,5))

--自定义函数返回结果集，存储过程则不能。
USE sl
go
CREATE FUNCTION selectbook(@bookprice FLOAT)
RETURNS @bookinfo TABLE(
book_name CHAR(25),
au_name CHAR(10),
price FLOAT,
pub CHAR(50)
)
AS
BEGIN
INSERT @bookinfo
SELECT book.book_name,authors.author_name,book.price,book.publisher from book,authors
WHERE book.author_id = authors.author_id AND book.price > @bookprice
return
END
GO

SELECT * FROM dbo.selectbook(30) 

--编制一个函数，用于给定的分数进行判断，返回‘及格’；小于60，则返回‘不及格’。
CREATE function fs(@cj CHAR(25))
returns CHAR(25)
as
BEGIN
DECLARE @fz varCHAR(25)
IF @cj >=60
SET @fz = '及格'
else
SET @fz = '不及格'
RETURN @fz
END

PRINT dbo.fs(60)