SQL server 1

//第1题 打开查询窗口
select year(replace(replace(replace('2012年5月18日','年','-'),'月','-'),'日',''))

//2-1-1 建立数据库
create database question1 on primary(
NAME='question1_data.mdf',
FILENAME='D:\A\question1_data.mdf',
SIZE=5MB,
FILEGROWTH=10%,
MAXSIZE=10MB
)
LOG ON(
NAME='question1_log.ldf',
FILENAME='D:\A\question1_log.ldf',
SIZE=5MB,
FILEGROWTH=15%,
MAXSIZE=10MB
)

//2-1-2 建立数据表和添加约束
create table city(

CityCode char(4) not NULL,
CityNAme varchar(80) not NUll,
primary key(CityCode)
)
CREATE  TABLE pubishers(
PubCode CHAR(4) Not NULL,
pubName CHAR(50) Not NULL,
Phone CHAR(15),
Address VARCHAR(100),
CityCode CHAR(4),
Primary KEY(PubCode),//主键
FOREIGN KEY (CityCode) REFERENCES city(CityCode)//外键
)

//2-1-3 修改表添加列
alter TABLE pubishers ADD comment varchar(100)

//2-1-4 修改表修改列
alter table publishers alter column comment varchar(120)

//2-1-5 修改表删除列
alter table publishers drop column comment

//2-1-6 删除数据表
drop table publishers 
drop table city

//2-1-7
drop database questionl
//删除数据库

//3-1-1 添加记录
insert into publishers values('ZLTC','蓝天出版社','020-83546627','越秀区果戈里大街12号','AGZ')

//3-1-2 更新记录
update titles set price=36 where titlecode='T012'

//3-1-3 删除记录
delete from publishers where PubCode='ZLTC'

//3-1-4 显示表所有列并排序
select * from titles order by price desc

//3-1-5 去掉重复数据行
select distinct(citycode) from publishers

//3-1-6 数据行筛选
SELECT TitleName 名称,Author 作者,Price 价格 FROM titles where CategoryID='1'

//4-1-1 选取介于某一范围的数据
SELECT * FROM titles Where PubDate >'2008-12-31' and PubDate <'2010-01-01' order by PubDate ASC

//4-1-2 选取属于某一子集合的数据
SELECT * FROM publishers where CityCode in('AGZ','DSZ','LGY')

//4-1-3 包含Like和通配符“%”的模糊查询
select * from titles where titleName like '%数据库%'

//4-1-4 包含Like和通配符“_”的模糊查询
select titleName,author,pubcode from titles where PubCode like '_X%'

//4-1-5 在查询中使用算术运算符
select TitleName 书名,price 全价,price * 0.85'0.85折' from titles where price >35

//4-1-6 使用常用的字符串函数
select TitleCode,RIGHT(TitleCode,5) ID1,right(TitleCode,3)ID2,TitleName 名称,LEN(TitleName)长度
FROM titles where LEN(TitleName) >7 ORDER BY LEN(TitleName) DESC

//4-1-7 使用常用的数学函数
select FLOOR(4.5)
select floor(-7.6)
select CEILING(8.1)
select power(2,5)

//4-1-8 使用常用的其他函数
select CONVERT(varchar(100),GETDATE(),23)

select DATEDIFF(MM,'2008-01-01','2009-01-01') 

//5-1-1 使用常用的其他函数
select avg(price) from titles

//5-1-2 在SELECT语句中使用GROUP BY
select count(pubName) 出版社数量,CityCode from publishers group by CityCode

//5-1-3 在SELECT语句中使用HAVING
select count(pubName) 出版社数量,CityCode from publishers group by CityCode having count(PubName) >1

//5-1-4 在一个SELECT语句中使用多个表（内联接）：
select a.CityName,b.PubName from city a,publishers b where a.CityCode = b.CityCode

//5-1-5 在SELECT语句中使用子查询
select top 1 titleName,author,pubdate from titles order by price desc

//5-1-6 使用UNION运算符的集合运算
select PubCode PubName,Phone,CityCode from publishers where cityCode='AGZ'
UNION
select PubCode PubName,Phone,CityCode from publishers where cityCode='HBJ'

//6-1-1 变量的定义、赋值和查看
declare @vname varchar(10), @vnum int
set @vname='张三'
set @vnum=12

//6-1-2 使用PRINT语句输出局部变量和全局变量的值
USE examDB
GO
DECLARE @vname varchar(10),@vnum int
set @vname='张三'
set @vnum=12

print '局部变量:@vname:'+@vname+'  '+'@vnum+:'+convert(varchar,@vnum)
print'全局变量：@@Language(当前系统所用语言的名称):'+@@Language

//6-1-3 使用CAST转换函数：新建查询窗口
use examDB
go
declare @vname varchar(10),@vnum float
set @vname='T002'
set @vnum=(select price from titles where TitleCode =@vname)
select TitleCode,TitleName,author,categoryId,PubCode,cast(price as varchar(10)) Price,PubDate from titles

//6-1-4 创建简单视图
create VIEW view1 as
select TitleName,Author,Price,PubDate FROM titles where pubDate > '2010-12-31'

select * from view1

//6-1-5 创建复杂视图
use examDB
go
CREATE VIEW view2 as
select A.*,ISNULL(B.CountNum,0)数量 from category A LEFT JOIN
(select CategoryID,COUNT(TitleCode) CountNum From titles Group by CategoryID)
B ON A.CategoryID = B.CategoryID

select * from view2

//6-1-6 创建索引
create nonclustered index newidx
on titles(pubdate)
with(drop_existing=on)

//6-1-7 删除视图和索引
drop view view1
drop view view2
（drop index newidx on titles）索引名在前表名在后

drop index newidx on title

//7-1-1 创建不带参数的存储过程
create proc sproc1
as
begin
select A.PubName,A.Address from publishers A,city B where A.CityCode = B.CityCode and B.CityName='北京'
end

USE examDB
EXEC sproc1
GO

//7-1-2 创建带输入参数的存储过程
create proc sproc2 @cityname varchar(20)
as
begin
 select A.PubName,A.address from publishers A,city B where A.CityCode = B.CityCode and B.CityName = @cityname
 end

 exec sproc2'北京'

//7-1-3 创建带输出参数的存储过程
create proc sproc3
@titlecode varchar(20),
@price float output
as
begin
select @titlecode=@titlecode,@price=Price from titles where TitleCode=@titlecode
print @titlecode+'的价格是'+cast(@price as varchar(20))
end

declare @p decimal(5,2)
exec sproc3 'T002', @p output

//7-1-4 创建触发器
Create trigger newtrigger
on titles
for update
as
begin
print('出版日期已更改，触发器起到作用')
end

update titles set price=36 where titlecode='T001'

//7-1-5 删除存储过程和触发器
drop procedure sproc1,sproc2,sproc3
drop trigger newtrigger 

//8-1-1
exec sp_addlogin 'Login1','123456'//创建数据库登录帐户

//8-1-2
exec sp_grantdDBaccess 'Login1','dbUser1'//创建数据库用户

//8-1-3
grant select,update on publishers to dbUser1//给数据库用户授权