SQL Server宝典(1)

/*

北大青鸟 株洲奥特培训中心

黄宁 老师

2004年04月01日

SQL Server 数据库的基本操作
(1) 数据库的创建
(2) 数据表的创建以及相关约束的指定(含临时表)
(3) 数据的添/删/改
(4) 数据的查询

*/

(0)创建数据库
-- 指定数据库名称
-- (注：如果数据库名中包含空格可以使用[]将其标示)
create database [Super WC]
-- 关于数据文件的定义
on
(
name = Super_WC_Data,    -- 逻辑名
filename = 'C:/Super_WC_Data.MDF',  -- 物理路径以及物理名
size = 2MB,     -- 初始大小
maxsize = 4MB,     -- 最大限制
filegrowth = 1MB           -- 增长大小
)
-- 关于日志文件的定义
log on
(
name = Super_WC_Log,
filename = 'C:/Super_WC_Log.LDF',
size = 3MB,
maxsize = 7MB,
filegrowth = 20%           -- 增长比例
)

-- 附加数据库
execute sp_attach_db '[Super WC]', 'C:/Super_WC_Data.MDF','C:/Super_WC_Log.LDF'
-- 分离数据库
execute sp_detach_db '[Super WC]'
-- 复制数据库
execute master.dbo.xp_cmdshell 'copy C:/Super_WC_Data.MDF D:/Super_WC_Data.MDF'
execute master.dbo.xp_cmdshell 'copy C:/Super_WC_Log.LDF D:/Super_WC_Log.LDF'

(1)创建数据表

创建一个数据表：学生（students）
结构如下：
字段       类型        是否允许为空     约束           备注
no         char(4)     No               主键           学号

name       nvarchar(8) No               唯一           姓名

birthday   datetime    No               检查（至少18年前） 生日

age        tinyint     No               缺省（默认等于当前时间减去生日） 年龄

sex        nchar(1)    No               缺省（默认'女'）         性别

phone      char(11)    Yes              检查（要么没有，要么长度等于11） 电话

address    nvarchar(24)No                                地址

没有特别约束的情况：
create table student
(
no        char(4)  not null,
name      nvarchar(8)  not null,
birthday  datetime  not null,
phone     char(11)  null,
address   nvarchar(24)  null
)

注意：没有特别约束的情况下，创建数据表可以参考“企业管理器”中“设计表”的操作格式！

包含约束的情况：
create table students
(
no        char(4)   primary key,
name      nvarchar(8)   unique,
birthday  datetime   check(datediff(year, birthday, getdate()) >= 18),
age       as datediff(year, birthday, getdate()),
sex   nchar(1)  default('女') check(sex = '女' or sex = '男')
phone     char(11)   check((phone is null) or (len(phone) = 11)),
address   nvarchar(24)
)

create table scores
(
no    char(4)    foreign key references students(no),
chinese  numeric(4,1) check(chinese >= 0 and chinese <= 100),
english  numeric(4,1) check(english >= 0 and english <= 100)  
)

以上答案只是最简单的描述形式！

比较规范的写法是
先用create table声明数据表的结构，

CREATE TABLE students
(
no       char(4),
name     nvarchar(8),
birthday datetime,
age      as DATEDIFF(year, birthday, getdate()),
sex    nchar(1),
phone    char(11),
address  nvarchar(24)
)

然后再ALTER TABLE  ADD CONSTRAINT 分别指定每个字段的约束:
每个约束都有一个独特的名称，其中，命名规范采用以下格式：
约束类型的简写_表名_字段名
pk_students_no

ALTER TABLE students
ADD CONSTRAINT pk_students_no PRIMARY KEY (no)

ALTER TABLE students
ADD CONSTRAINT uq_students_name UNIQUE (name)

ALTER TABLE students
ADD CONSTRAINT ck_students_birthday CHECK (datediff(year,[birthday],getdate()) >= 18)

ALTER TABLE students
ADD CONSTRAINT df_students_sex default ('女') for sex

ALTER TABLE students
ADD CONSTRAINT ck_students_sex CHECK ([sex] = '男' or [sex] = '女')

ALTER TABLE students
ADD CONSTRAINT ck_students_phone CHECK ([phone] is null or len([phone]) = 11)

相对应的对约束进行删除，则是通过DROP CONSTRAINT子句来完成：
ALTER TABLE students
DROP CONSTRAINT pk_students_no

ALTER TABLE students
DROP CONSTRAINT uq_students_name

注意：
约束只有添加与删除操作，没有修改操作！

注意：
其中，age(年龄)采用了“计算列”的表示方法！
“计算列”的定义：
在表中某个字段的值源于某一表达式的值（某一函数的运算结果或是其他字段的运算结果）！
比如：
某学生的成绩表结构如下：
数学
语文
体育
总分

创建命令如下：
create table scores
(
math      numeric(3, 1),
chinese  numeric(3, 1),
sport     numeric(3, 1),
total      as math + Chinese + sport
)

insert into scores values (80, 69, 95)

total 部分的值会自动计算，为 244

-- 创建临时表
-- 临时表将会存储在TempDB的临时数据库中
-- 当用户断开连接后，就会自动删除
-- 语法：在表名前加上#
create table #tt
(
a int,
b int
)

insert into #tt values (1,1)
insert into #tt values (2,2)

select * from #tt

(2)数据操作(添加/删除/修改)

添加操作(insert)的语法格式：
Insert [into] 数据表 (字段) values (数据)

-- 添加记录(一般情况)
insert into students
(no,name,birthday,sex,phone,address)
values
('0001', 'AHuang', '2000-01-01', '男', '13307331100', '株洲')

(注意: into 可以省略 )

-- 添加记录(如果是给所有字段添加数据，可以省略字段标示)
insert into students
values
('0002', 'ANing', '2008-08-08', '女', '13307330011', '北京')

-- 添加记录(如果是给具有默认约束的字段添加数据，想利用默认约束,可以利用default)
insert into students
values
('0002', 'ANing', '2008-08-08', default, '13307330011', '北京')

删除操作(delete)的语法格式：
Delete [from] 数据表 where 条件

-- 删除记录(删除所有)
delete from students

(注意: from 可以省略,即可以: delete students )

-- 删除记录(删除特定记录,可以通过 where 条件来过滤,比如:学号'0001'的学生记录)
delete from students where no = '0001'

修改操作(update)的语法格式：
update 数据表 set 字段 = 新值 where 条件

-- 修改记录(修改所有)
update students set 性别 = '女'

-- 修改记录(修改特定记录,可以通过 where 条件来过滤,比如:学号'0001'的学生记录)
update students set 性别 = '男' where no = '0001'

(3)数据查询

查询操作(select)的语法格式：
select 字段 from 数据表 where 条件 order by 字段

-- 查询记录(查询所有行与所有列,指定数据表所有字段)
select no, name, birthday, sex, phone, address from students

-- 查询记录(查询所有行与所有列,除了指定数据表所有字段,还可以通过 * 来指代所有字段)
select * from students

-- 查询记录(查询所有行与特定列,指定数据表特定字段)
select no, name, phone, address from students

-- 查询记录(给字段指定别名)
select no as '学号', name as '姓名', phone as '电话', address as '地址' from students

-- 查询记录(合并字段并指定别名)
select no as '学号', name as '姓名', address + ' ' + phone as '地址 电话' from students

(注意:合并字段的前提是字段类型必须一致,否则需要通过类型转换函数convert来实现!)

-- 类型转换函数 convert 语法
convert(目标类型, 数据)

convert(varchar(12), 1234)

(4)数据查询的高级应用(利用PUBS数据库作为演示数据)

-- 根据价格对书籍表记录排序
select * from titles order by price

(注意：默认数据排序采用升序：由低到高或是有小到大，可以通过设定关键字来调整)

-- 根据价格对书籍表记录排序(降序)
select * from titles order by price desc

-- 根据价格对书籍表记录排序(升序)
select * from titles order by price asc

-- 找出书籍表中最高与最低的价格
select max(price), min(price) from titles

-- 统计书籍表中书籍总数以及总价格和平均价格
select count(title_id), sum(price), avg(price) from titles

-- 找出书籍表中最贵3本书
select top 3 title_id, price from titles order by price desc

select * from titles
select * from jobs
--

-- 统计书籍表中每种类型的书籍总量
select type as '书籍类型', count(title_id) '书籍数量'
from titles group by type

select type, title_id from titles order by type

-- 统计书籍表中每种类型的书籍的预付款(advance)的总和
select type as '书籍类型', sum(advance) '预付款总和'
from titles group by type

-- 列出所有书籍类型
select type as '书籍类型' from titles
select distinct type as '书籍类型' from titles
select distinct type as '书籍类型', title_id from titles

-- 列出所有作者所在州
select distinct state as '州' from authors
select distinct state as '州', city as '城市' from authors where  state = 'CA'
select state as '州', city as '城市' from authors where state = 'CA'

(注: distinct 列出指定字段的值同时剔出所有重复的！)

-- 根据给定的作者姓名列出其所著书籍名称
select * from authors
select * from titles

select a.au_lname as '名', a.au_fname as '姓', t.title as '书籍名称'
from authors as a join titleauthor as ta on a.au_id = ta.au_id
 join titles as t on ta.title_id = t.title_id
where a.au_lname = 'Green' and a.au_fname = 'Marjorie'

-- 根据给定的出版社名称列出其所有的出版书籍名称
select * from publishers

select p.pub_name as '出版社', t.title as '书籍名称'
from publishers as p join titles as t on p.pub_id = t.pub_id
where pub_name = 'New Moon Books'

select pub_name as '出版社', title as '书籍名称'
from publishers as p join titles as t on p.pub_id = t.pub_id
where pub_name = 'New Moon Books'

-- 在销售表中挑选1993年度的订单
select ord_num as '订单编号', title_id as '书籍编号', ord_date as '订购日期' 
from sales
where ord_date between '1993-01-01' and '1993-12-31'

(注意：between and 之间的值必须满足"开始点"<="结束点")
select ord_num as '订单编号', title_id as '书籍编号', ord_date as '订购日期' 
from sales
where ord_date between '1993-12-31' and '1993-1-1'

-- 在销售表中统计每本书的订单数量
select title_id as '书籍编号', count(ord_num) as '订单总数'  from sales group by title_id

-- 在销售表中统计每本书的总订购量
select title_id as '书籍编号', sum(qty) as '总定购量'  from sales group by title_id

-- 在销售表中统计每个商店的总订购量
select stor_id as '商店编号', sum(qty) as '总定购量'  from sales group by stor_id

-- 在销售表中查询每个商店定购了那几本书以及每本书相应的订购量
select stor_id as '商店编号', title_id as '书籍编号', sum(qty) as '总定购量'  from sales group by stor_id, title_id order by stor_id

-- 查询指定出版社出版的书
select p.pub_name as '出版社', t.title as '书籍'
from publishers as p join titles as t on p.pub_id = t.pub_id
where pub_name = 'New Moon Books'

-- 查询指定出版社出版的书籍的总数目
select p.pub_name as '出版社', count(t.title) as '书籍数目'
from publishers as p join titles as t on p.pub_id = t.pub_id
group by p.pub_name

-- 统计每个作者的版权税总和
select au_id as '作者编号', sum(royaltyper)  from titleauthor group by au_id

select * from authors
select * from titles
select * from publishers

select * from publishers where  = 'New Moon Books'
select * from titles where pub_id = '0736'

-- 子查询
-- 根据出版社的名称查询其出版的书籍
-- 首先，根据出版社的名称在publisher中找到相应的出版社编号
-- 然后，在根据出版社编号在titles中找到相应的书籍信息
select * from titles
where pub_id =
 (select pub_id from publishers
  where pub_name = 'New Moon Books')

select title_id, type, price from titles

select title_id, type, price
from titles
order by price desc

select top 4 title_id, type, price
from titles
order by price desc

-- 子查询
-- 找寻最贵的书籍
-- 先通过聚合函数获取最高价格
-- 然后，将其作为查询条件，找出相应的书籍
select title_id, type, price from titles
where price = (select max(price) from titles)

select au_id,au_lname from authors where au_lname = 'Green'

-- 子查询
-- 根据作者的名查找其编写的书籍
-- 先通过子查询获取作者编号
-- 然后，将其作为查询条件，找出相应的书籍编号
-- 最后，在利用所得到的书籍编号来得到书籍信息
select au_id, title_id from titleauthor
where au_id =
 (select au_id from authors where au_lname = 'Green')

select * from titles
where title_id in
 (
 select title_id from titleauthor
  where au_id =
  (select au_id from authors where au_lname = 'Green')
 )

-- 打印输出
-- Print
print 'Hello world'

-- 获取系统时间
print getdate()

-- 获取3天前的时间
print dateadd(day, -3 , getdate())
-- 获取3天后的时间
print dateadd(day, 3 , getdate())
-- 获取3年前的时间
print dateadd(year, -3 , getdate())
-- 获取3年后的时间
print dateadd(year, 3 , getdate())

-- 获取3月后的时间
print dateadd(month, 3 , getdate())
-- 获取9小时后的时间
print dateadd(hour, 9 , getdate())
-- 获取9分钟后的时间
print dateadd(minute, 9 , getdate())

-- 获取指定时间之间相隔多少年
print datediff(year, '2005-01-01', '2008-01-01')
-- 获取指定时间之间相隔多少月
print datediff(month, '2005-01-01', '2008-01-01')
-- 获取指定时间之间相隔多少天
print datediff(day, '2005-01-01', '2008-01-01')

-- 获取给定字符串的长度
print len('abcdef')
-- 字符串合并
print 'abc' + 'def'

print 'abcder'

print 'abc' + '456'
print 'abc' + 456
-- 类型转换
print 'abc' + convert(varchar(10), 456)

print '123' + '456'
print '123' + 456
print 123 + '456'

-- 利用系统函数作为默认值约束
drop table ttt

create table ttt
(
stu_name varchar(12),
stu_birthday datetime default (getdate())
)

alter table ttt
add constraint df_ttt_stu_birthday default  (getdate()) for stu_birthday

insert into ttt values ('ANiu', '2005-04-01')
insert into ttt values ('ANiu', getdate())

insert into ttt values ('AZhu', default)

sp_help ttt

select * from ttt

-- 计算列
create table scores
(
chinese int,
english int,
total int
)

insert into scores values (80, 90, 170)

select * from scores

drop table scores

create table scores
(
chinese int,
english int,
total as chinese + english -- 计算列
)

insert into scores values (80, 90)

-- 故意添加，结果出错
insert into scores values (80, 90, 250)

select * from scores