代码记录2

1
select year(replace(replace(replace('2013年10月21日','年','-'),'月','-'),'日',''))
2-1
create database question2 on primary(
Name='question2_data.mdf',
SIZE=6MB,
MAXSIZE=12MB,
fileNAME='D:\A1\question2_data.mdf',
FILEGROWTH=5%
)
log on(
NAME='question2_log.1df',
SIZE=6MB,
MAXSIZE=12MB,
FILENAME='D:\A1\question2_log.1df',
FILEGROWTH=10%
)
go

2-2
create table city(
CityCode char(4) not null primary key,
CityName Varchar(80) NOT NULL,
)
Create table category(
CategoryID int,
CategoryName varchar(50) NOT NULL,

)

2-2
alter table city add CitySurvey varchar(110)
2-3
alter table city alter column citysurvey varchar(100)
2-4
alter table city drop column citysurvey go
2-5
drop table city
drop table category
2-6
drop database question2

3-1
insert into city values('MSD','山东')

3-2
update publishers set Phone='0755-56556565' where PubCode='DGZC'
3-3
delete city where CityCode='MSD'
3-4
select * from titles order by CategoryID desc

3-5
select distinct categoryID from titles

3-6
select pubCode AS 出版社代码,pubName AS 出版社名称,Address AS 地址 from publishers where
pubName='牡丹出版社'

4-1
select titleName,pubDate from titles where MONTH(PubDate) between 3and 6
order by MONTH(pubDate) asc

4-2
select cityname,citycode from city where CityName in('沈阳','深圳','青岛')

4-3
select pubName,address from publishers where address like '%北京%'

4-4
select cityCode,cityname from city where CityCode like '_G%'

4-5
select titleName,2*price as '2倍的价格' from titles

4-6
select substring(titlename,1,6) as '前6位' from titles

4-7
select round(34.255,2) as '34.255',round(34.222,2) as '34.222'

4-8

select app_name() AS '当前应用程序名称'

5-1
select count(*) from city

5-2
select categoryID as '类别ID',COUNT(*) as '出版物数量' from titles group by categoryID

5-3
select categoryID as '类别ID',COUNT(*) as '出版物数量' from titles group by categoryID
having count(*)>2

5-4
select CategoryName,author,titlename from titles A JOIN category B on
A.CategoryID=B.CategoryID where categoryName like '%数据库%'

5-5
select titlename,Author,price from titles group by TitleName,Author,Price
Having price>(Select avg(price) from titles)

5-6
select * from city where cityCode='AGZ' union select * from city where CityCode='CSH'

6-1
declare @vaddr varchar(30),@vnum int set @vaddr='天河北路233号' set @vaddr=320

6-2
declare @vaddr varchar(30),@vnum int
set @vaddr='天河北路233号'
set @vnum=320
print '局部变量：@vaddr:'+@vaddr+' @vnum:'+cast(@vnum as char(10))
print '全局变量：@@Language(当前所用语言的名称)：'+@@Language

6-3
declare @code varchar(10),@price money
set @code='T007'
set @price=(select [price] from [titles] where [titleCode]=@code)
select * from titles
select @code+'出版物的价格是：'+cast(@price as varchar(6)) as 显示价格

6-4
create view view1(出版物名称,作者,价格,类别ID,出版日期)
as
select titleName,Author,price,categoryID,pubDate from titles where year(pubDate) between 2010 and 2012

select *from view1

6-5
CREATE VIEW view2(出版社代码,出版社名称,数量)
as
select a.pubCode,pubName,count(B.PubCode)
from publishers A left join titles B on A.PubCode=B.PubCode
group by A.PubCode,PubName

select * from view2

6-6
create index newidx on titles(pubCode)

6-7
drop view view1,view2
drop index newidx on titles

7-1
create procedure sproc1
as select titleName,price
from titles where Author='斯考特'
exec sproc1

7-2
create procedure sproc2
author char(10)
as
select titleName,price
from titles where Author=@author
exec sproc2 '斯考特'

7-3
create procedure sproc3
(@code char(6),@name varchar(80) output)
as
set @name=(select titlename from titels where titleCode=@code)
print @code+'的名称是：'+@name
declare @t varchar(80)
exec sproc3'T003',@t output

7-4
create trigger newtrigger on titles
after update as if(update(price)) print'出版物价格已更改,触发器起到作用'

select * from titles update titles set price=34.50 where titleCode='T001'

7-5
drop procedure sproc1,sproc2,sproc3
drop trigger newtrigger

8-1
sp_addLogin 'Login2','222222'
8-2
exec sp_grantDBaccess 'Login2','dbUser2'
8-3
grant select,insert, delete on publishers to dbUser2