SQL server 4

用内置的日期和时间函数返回当天日期
select GETDATE()

建立数据库
create database question4 on primary(
NAME='question4_data.mdf',
FILENAME='D:\A1\question4_data.mdf',
SIZE=5MB,
MAXSIZE=11MB,
FILEGROWTH=10%
)
log on(
NAME='question4_log4.df',
FILENAME='D:\A1\question4_log4.df',
SIZE=5MB,
MAXSIZE=11MB,
FILEGROWTH=20%
) 

建立数据表和添加约束
use question4
create table category(
CategoryID int NOT NULL primary key,
CategoryName Varchar(50) NOT NULL)

修改表添加列
use question4
alter table category add remark varchar(50)

修改表修改列
alter table category alter column remark varchar(60)

修改表删除列
use question4
alter table category drop column remark

删除数据表
drop table category

删除数据库
use master
drop database question4

添加记录
use examDB
insert into titles values('T014','网络数据库开发与设计','史俊','2','HXTD','28','2013-10-01')

更新记录
use examDB
update titles set price=38 where Author='刘东' and CategoryID=3

删除记录
delete from titles where TitleCode='T014'

显示所有表列并排序
select * from city order by CityCode desc

去掉重复数据行
select distinct categoryID from category

数据行筛选
select TitleName as 名称,author as 作者,price as 价格,pubCode as 出版社代码 from titles
where Author='王江江'

4-1
select TitleName,Author,price
from titles where price between 30 and 50
order by price asc

4-2
select author,titleName,PubDate
from titles
where author in('朱丽叶','谢海','刘东')

4-3
select author,titleName,PubDate
from titles
where titleName like '%程序设计%'

4-4
select author,titleName,price
from titles
where author like '张_辉'

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

4-6
select CHARINDEX('程序设计',titlename)as 位置,titleName
from titles
where CHARINDEX('程序设计',titlename)>0

4-7
select rand()
select SQUARE(5)

4-8
use examDB
select ISNULL(price,0),titlename
from titles

5-1
select max(price) as 最高价格
from titles

5-2
select max(price) as 最高价格,pubCode
from titles
group by PubCode

5-3
select max(price) as 最高价格,pubCode
from titles
group by PubCode
having max(price)>50

5-4
select TitleName,Author,Price
from titles t inner join category c
on t.CategoryID=c.CategoryID
where CategoryName='C#基础'

5-5
select titleName,Author,Price
from titles
where CategoryID=(
select CategoryID
from category
where CategoryName='JAVA'
)

5-6
select TitleCode,TitleName,Author,Price
from titles
where Author='王江江'
union
select TitleCode,TitleName,Author,Price
from titles
where Author='斯考特'

6-1
declare @vaddr varchar(30),@vdate date
set @vaddr='北京路68号'
set @vdate='2014-12-24'

6-2
declare @vaddr varchar(30),@vdate date
set @vaddr='北京路68号'
set @vdate='2014-12-24'
print'局部变量：@vaddr：'+@vaddr+'@vdate:'+convert(varchar,@vdate,120)
print''
print'全局变量：@@Language(当前所用语言的名称):'+@@Language

6-3
declare @vcode varchar(6),@vdate date
set @vcode='T011'
set @vdate=(
select pubdate
from titles
where titleCode=@vcode
)
select * from titles
select @vcode+'出版物的出版日期是：'+convert(varchar,@vdate,120)as 显示出版日期

6-4
select titleName as 出版物名称,CategoryID as 类别ID, price as价格,PubDate as 出版日期
from titles 
where price between 40 and 50

6-5
create view view2 as(
select p.CityCode as 出版社代码,PubName,AVG(price) as 平均价格
from publishers p inner join titles t
on p.PubCode=t.PubCode
group by p.CityCode,PubName
)

6-6
create index newidx on titles(price)

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

exec sp_addLogin 'Login4','444444'

exec sp_grantdbaccess 'Login4','dbUser4'

grant select,update on titles to dbUser4