数据库
创建数据库
1.创建一个名为“sale”的数据库。
要求：该数据库由
主数据文件 5MB、2MB的次数据文件和1MB的日志文件组成。并且主数据文件以2MB的增长速度增长，其最大容量为25MB；
次数据文件以10%的增长速度增长，其最大容量为20MB；
事务日志文件以1MB增长速度增长，其最大日志文件大小为10MB，存放位置在“d:\saledata”文件夹中，上述没有说明的选项都采用默认值。
2.在Sale数据库下新增名为UserGroup的文件组。
3.以增加次数据库文件方式扩充Sale数据库的容量。次数据文件容量为5MB，最大容量为10MB，文件增量为1MB。要求将次数据文件保存在与事务日志文件不同的存储设备上，次数据文件作为UserGroup文件组的成员。
4.使用存储过程显示Sale数据库信息。
create database sale
on
(
name = sale,
FileName='d:\bookDB\saledata\sale.mdf',
size=5mb,
Maxsize=25mb,
filegrowth=2mb
),
(
name = sale1,
FileName='d:\bookDB\saledata\sale1.ndf',
size=2mb,
Maxsize=20mb,
filegrowth=10%
),
filegroup UserGroup --文件组UserGroup
(
name = UserGroup,
FileName='d:\bookDB\saledata\UserGroup.ndf',
size=20mb,
Maxsize=20mb,
filegrowth=10%
)
log on
(
name = sale_Log,
FileName='d:\bookDB\saledata\sale_Log.ldf',
size=10mb,
Maxsize=10mb,
filegrowth=1mb
)
alter database sale
add file
(
name = sale2,
FileName='d:\bookDB\saledata\sale2.ndf',
size=5mb,
Maxsize=10mb,
filegrowth=1mb
)
to filegroup UserGroup
sp_helpdb sale
数据库表创建
use bookDB --使用数据库
go
create table 读者类型
(
读者类型编号 varchar(2) not null,
读者类型名称 varchar(10) not null,
限借数量 smallint not null,
限借期限 smallint not null,
超期日罚金 money not null,
借书证有效期 smallint not null,
)
create table 借书证
(
借书证编号 varchar(7) not null,
读者编号 varchar(15) not null,
姓名 varchar(20)not null,
办证日期 date not null,
读者类型编号 varchar(2) not null,
借书证状态 char not null,
证件类型 varchar(20) null,
证件编号 varchar(20) null,
办证操作员 varchar(20) null,
)
create table 藏书信息
(
图书编号 char(12) not null,
ISBN编号 varchar(15) not null,
总藏书量 varchar(20)not null,
管内剩余 varchar(50) not null,
藏书位置 char(6) not null,
入库时间 datetime null,
)
create table 图书借阅
(
借阅ID int identity(1,1) not null,
借书证编号 varchar(7) not null,
图书编号 smallint not null,
借出数量 date not null,
借出日期 date not null,
应还日期 char not null,
借阅操作员 varchar(20) null,
归还操作员 varchar(20) null,
图书状态 varchar(2)not null
)
删除数据库表
drop table 借书证表
增加列
use bookDB --使用数据库
go
alter table 出版社
add 联系电话 varchar(15) not null;
删除列
use bookDB --使用数据库
go
alter table 出版社
drop column 联系电话
修改表中列的类型和长度
use bookDB --使用数据库
go
alter table 借书证表
alter column 借书证编号 varchar(7)
alter table 借书证表
alter column 读者编号 varchar(15)
alter table 借书证表
alter column 姓名 varchar(20)
重命名列名
sp_rename '读者类型表.借阅日期'.'借阅期'
重命名表名
sp_rename '读者类型表'.'读者表'
主键、外键、检查约束
use bookDB
go
create table 图书类型
(
图书类型代号 varchar (15) not null,
图书类型名称 varchar(15) not null,
描述信息 varchar(max) null,
constraint PK_图书类型 primary key(图书类型代号)
)
create table 图书信息
(
ISBN编号 varchar(15) not null,
图书名称 varchar(20) not null,
作者 char(2) not null,
出版社ID int not null,
出版日期 varchar(20) not null,
图书类型代码 varchar(15) not null,
封面图片 image null,
图书简介 varbinary(max) null,
价格 money not null,
constraint PK_图书信息 primary key(ISBN编号),
constraint PK_图书信息_出版社 foreign key(出版社ID) references 出版社(出版社ID),
constraint PK_图书信息_图书类型 foreign key(图书类型代码) references 图书类型(图书类型代号),
constraint CK_出版日期 check(出版日期<getdate())
)
create table 藏书信息
(
图书编号 varchar(12) not null,
ISBN编号 varchar(15) not null,
总藏书量 varchar(20) not null,
馆内剩余 varchar(50) not null,
藏书位置 char(6) not null,
入库时间 datetime not null,
constraint PK_藏书信息 primary key(图书编号),
constraint PK_藏书信息_图书信息 foreign key(ISBN编号) references 图书信息(ISBN编号),
constraint CK_馆内剩余 check(馆内剩余<=总藏书量),
constraint CK_入库时间 check(入库时间<getdate())
)
use bookDB
go
--alter table 读者信息
--add
--constraint PE_读者信息 primary key(读者编号),
--constraint PE_读者信息_部门 foreign key (部门编号) references 部门(部门编号),
--constraint PE_读者信息_读者类型 foreign key(读者类型编号) references 读者类型(读者类型编号),
--constraint CE_性别 check(性别='男' or 性别 = '女')
--alter table 借书证1
--add
--constraint PE_借书证_读者信息 foreign key (读者编号) references 读者信息(读者编号),
--constraint PE_借书证_读者类型 foreign key(读者类型代码) references 读者类型(读者类型编号),
--constraint DE_证书类型 default '身份证' for 证件类型,
--constraint CE_办证日期 check(办证日期<=getdate()),
--constraint DF_图书状态 default '1' for 借书证状态
--alter table 图书借阅
--add
--constraint PE_图书借阅 primary key(借阅ID),
--constraint PE_图书借阅_借书证 foreign key (借书证编号) references 借书证1(借书证编号),
--constraint PE_图书借阅_图书信息 foreign key (图书编号) references 图书信息(ISBN编号)
--alter table 读者信息
--add
--constraint PE_读者信息_部门 foreign key (部门编号) references 部门(部门编号),
--constraint PE_读者信息_读者类型 foreign key (读者类型编号) references 读者类型(读者类型编号)
alter table 藏书信息
add
constraint PE_藏书信息_图书信息 foreign key (ISBN编号) references 图书信息(ISBN编号)
7.2 插入数据
use bookDB
go
--insert into 部门(部门编号,部门名称,部门简称)
--values ('20','信息工程学院','信工学院'),
-- ('21','经济与管理学院','经管学院'),
-- ('22','艺术与设计学院','艺术学院'),
-- ('23','机电工程学院','机电学院')
insert into 部门
select '24','外语与商贸学院','外贸学院'
union
select '25','建筑工程学院','建工学院'
union
select '11','教务处','教务处'
union
select '12','信息管理中心','信息中心'
union
select '13','图书馆','图书馆'
7.4 更新（修改）数据
use bookDB
go
update 读者信息
set 性别='女'
where 读者姓名='白晓鸥'
update 藏书信息
set 藏书位置='A-1-2'
where 藏书位置='A-1-1'
update 读者类型
set 限借期限=限借期限/2
7.5 删除数据
select* from 图书信息
where 图书名称= '电脑故障急救箱'
delete from 图书信息
where 出版日期<'2001'
9.1查询固定列信息，并起别名
use bookDB
-- select *form 查询所有
-- 检索“图书信息”数据表中全部图书，查询结果只包含“ISBN编号”、“作者”、“图书名称”和“出版社”4列数据，
--要求这4个字段输出时分别以“ISBN”、”Author”“BookName”和“PublishingHouse”英文名称作为其标题。
第一种
select ISBN=ISBN编号 ,Author = 作者 ,BookName=图书名称 ,PublishingHouse=出版社ID from 图书信息
第二种
select ISBN编号 as ISBN ,作者 as Author ,图书名称 as BookName,出版社ID as PublishingHouse from 图书信息
查询
9.1.2 查询不重复的信息
use bookDB
select distinct 藏书位置 from 藏书信息
9.1.3 查询前五个，且显示固定列的数据
use bookDB
select top 5 出版社名称,出版社简称,出版社地址 from 出版社
9.1.4 查询前百分之10 且显示所有列的数据
use bookDB
select top 10 percent * from 图书信息
9.1.5 在显示列前面加上我们要求的字符串
use bookDB
select 读者编号,读者姓名,性别,出生日期,所属部门,'该读者所在部门编号为：',部门编号
from 读者信息
9.2 多种查询
查询姓名
use bookDB
select *from 图书信息
where 作者='陈振宇'
查询日期
select *from 图书信息
where 出版日期<'2006-12-31'
and 查询
select *from 图书信息
where 作者='陈振宇'and YEAR(出版日期)>'2009'
between 查询
select *from 图书信息
where 出版日期 between '2008-1-1' and'2010-1-1'
in 查询
select *from 图书信息
where 作者 in ('陈振宇','陈启安','陈海林')
模糊查询：%
select *from 图书信息
where 作者 like '陈%'
模糊查询：not
select *from 图书信息
where 作者 not like '陈%'
非空查询
select *from 图书信息
where 图书简介 is not null
动态年龄查询
select 读者姓名 as '姓名', year(getdate())-year(出生日期) as '年龄'from 读者信息 -- getdate获取系统时间，动态设置时间
9.3 order by（升降序条件）
-- 从“图书信息”数据表中检索价格在30元以上的图书信息，要求按价格的升序输出。
select*from 图书信息
where 价格>30
order by 价格
--检索2006年以后出版的图书信息，要求按作者姓名的降序输出。
select*from 图书信息
where 出版日期>'2006-1-1'
order by 作者 desc -- 降序 ，默认（升序asc）
--从“图书信息”数据表中检索所有的图书信息，要求按出版日期的升序输出，
--出版日期相同的按价格的降序输出
select*from 图书信息
order by 出版日期 ,价格 desc
--从“图书信息”数据表中查询图书的最高价、最低价和平均价
select 读者编号,读者姓名 as '姓名',year(getdate())-year(出生日期)as 年龄 from 读者信息
order by year(getdate())-year(出生日期)
9.4 count 、 sum 、distinct、max/min/avg
使用上面聚合函数后得到的结果可以当做数来进行处理
如：
select * from A where num>(select avg(wage) from B);
（1）count
从“图书信息”数据表中查询价格在“20元”至“45元”之间的图书种数。
select count(*) as '图书种类' from 图书信息
where 价格 between 20 and 50
（2）sum
从“藏书信息”数据表中查询图书的藏书总数量。
select sum(总藏书量) from 藏书信息
（3）distinct 无重复
从“藏书信息”数据表中查询无重复的藏书位置的数量。
select count(distinct 藏书位置) from 藏书信息
（4）max、min、avg
从“图书信息”数据表中查询图书的最高价、最低价和平均价
select max(价格) as '最高价格' , min(价格)as '最低价格' , avg(价格) as '平均价格' from 图书信息
使用计算公式或统计函数（聚合函数）。怎么才能统计读者信息表中年龄最大和最小的读者以及读者的人数？
select max(year(getdate())-year(出生日期)),
min(year(getdate())-year(出生日期)) from 读者信息
（5）sql语句每个产品类别的产品数量,平均价格及最高价格
这个问题用产品类别分组汇总就行了。具体的SQL代码需要有表结构才好给出。
假设有数据表 销售记录（产品ID，产品类别，售价权，数量）
检索出每个产品类别的数量、均价和最高价格
select 产品类别, sum(数量) as 分类总数,
avg(售价) as 平均价格, max(售价) as 最高价格
from 销售记录 group by 产品类别;
9.5 groud by查询数据的分组与汇总
（1）groud by 的理解：大概是分类汇总的 意思。
A表
示例1
select 类别, sum(数量) as 数量之和
from A
group by 类别
返回结果如下表，实际上就是分类汇总。
（2）
在“图书信息”数据表中统计各个出版社出版的图书的平均定价和图书种数。
use bookDB
go
select 出版社ID,avg(价格) as '图书平均价格',count(*)as '图书种类'
from 图书信息
group by 出版社ID
with cube
（3）
在“图书信息”数据表中查询图书平均定价在20元并且图书种数在6种以上的出版社，查询结果按平均定价降序排列。
use bookDB
go
select 出版社ID,avg(价格) as '图书平均价格',count(*)as '图书种类'
-- into + 表名 ：就可以将查询的数据保存在这个新表中
from 图书信息
group by 出版社ID
having avg(价格)>20 and count(*)>6
（4）
在“bookDB”数据库中的“图书信息”数据表中，检索图书类型代码为“G”和“T”的图书的种数。
select 图书类型代码,count(*)as 图书种类 from 图书信息
where 图书类型代码='G' or 图书类型代码='T'
group by 图书类型代码
-- groud by all 图书类型代码 这样的话那会显示所有的图书类型代码
9.6 内连接+相等连接+自连接查询
（1） 内连接+相等连接查询
内连接省去不必要的信息
相等连接：先对夺标查询进行笛卡尔积计算，并对结果是满足相等连接的公共列条件的筛选出来
相等连接的缺陷：
查询结果会出现重复列即公共列
select *from 图书信息 inner join 出版社
on 出版社.出版社ID =图书信息.出版社ID
（2）
自然连接：在数据库bookDB中，从“图书信息”和“出版社”两个数据表，查询图书的详细信息。要求查询结果中包含ISBN编号、图书名称、作者、价格、出版社名称、出版日期等字段。
select 图书信息.* ,出版社.出版社简称,出版社.出版社名称,出版社.出版社地址,出版社.邮政编码
from 图书信息 inner join 出版社
on 出版社.出版社ID =图书信息.出版社ID
-- 前表是显示所有列，后表是显示指定列
(3) 跨三表查询
自然连接：在数据库bookDB中，从“藏书信息”、“图书信息”和“出版社”3个数据表，查询图书的详细信息。要求查询结果中包含图书编号、ISBN编号、图书名称、出版社名称、总藏书量等字段
select 图书编号,藏书信息.ISBN编号,图书名称,出版社名称,总藏书量
from 藏书信息
inner join 图书信息
on 藏书信息.ISBN编号=图书信息.ISBN编号
inner join 出版社
on 出版社.出版社ID =图书信息.出版社ID
（4）对多表查询进行条件限制
非等值连接：从“图书信息”和“图书类型”两个数据表中查询2008年1月1日到2011年1月1日之间出版的价格在30元以上的“工业技术”类型的图书信息，要求查询结果显示图书名称、价格、出版日期和图书类型名称4列数据。
select 图书名称,价格,出版日期,图书类型名称 as '图书类型'
from 图书信息 inner join 图书类型
on 图书信息.图书类型代码 = 图书类型.图书类型代号and 出版日期 between '2008-1-1' and '2011-1-1'
and 价格>30
and 图书类型名称 = '工业技术'
（5）难点（待解决）
自连接：在“BookDB”数据库图书借阅表中，检索所有借阅了图书编号TP7111172760和TP7040281286的读者的借书证编号。
select book1.借书证编号 from 图书借阅 as book1, 图书借阅 as book2
where book1.借书证编号= book2.借书证编号 and book1.图书编号='TP7111172760' or book1.图书编号='TP7040281286'
9.7 左外+右外+全连接
参考地址
(1)左外连接 left join：即左边为主表
左外连接：从“图书类型”和“图书信息”两个数据表中查询所有图书类型的图书信息，查询结果显示图书类型名称、图书名称和价格3列数据。
（即图书类型是主表）
use bookDB
go
select 图书类型名称,图书名称,价格 from 图书类型 left join 图书信息
on 图书类型.图书类型代号 = 图书信息.图书类型代码
结果
（2）右外连接 right join
从“图书借阅”和“借书证”两个数据表中查询所有借书证的借书情况，查询结果显示借书证编号、姓名、图书编号和借出数量4列数据。
select 图书借阅.借书证编号,姓名,图书编号,借出数量 from 图书借阅 right join 借书证1
on 图书借阅.借书证编号=借书证1.借书证编号
（3）全外连接
相当于左外+右外
全外连接：从“借书证”、“图书借阅”“藏书信息”和“图书信息”4个数据表中查询所有借书证借阅图书的情况和所有图书被借阅的情况，查询结果包括借书证编号、借出日期、图书名称和总藏书量4列数据。
use bookDB
go
select 借书证1.借书证编号,借出日期,图书名称,总藏书量 from 借书证1
full join 图书借阅 on 借书证1.借书证编号=图书借阅.借书证编号
full join 藏书信息 on 图书借阅.图书编号=藏书信息.图书编号
full join 图书信息 on 图书信息.ISBN编号 = 藏书信息.ISBN编号
9.8 嵌套查询
（1）单值嵌套：
查找图书《C#程序设计案例教程》是由哪一家出版社出版的。
use bookDB
go
select 出版社名称 as '出版社' from 出版社
where 出版社ID = (select 出版社ID from 图书信息 where 图书名称='C#程序设计')
（2）
单值嵌套：从“图书信息”数据表中查找价格最低并且出版日期最晚的图书信息。
use bookDB
go
select *from 图书信息
where 出版日期=
(select max(出版日期)from 图书信息 where 价格 =
(select min(价格)from 图书信息))
（3）多值嵌套：
查询所有借阅了图书的借书证信息。
use bookDB
go
select *from 借书证1
where 借书证编号 in (select 借书证编号 from 图书借阅)
（4）
多值嵌套：查询由“高等教育出版社”出版已被借出的图书信息
use bookDB
go
select *from 图书信息
where 出版社ID=(select 出版社ID from 出版社 where 出版社名称='高等教育出版社'and ISBN编号 in (select ISBN编号 from 藏书信息 where 图书编号
in(select 图书编号 from 图书借阅)))
9.9相关子查询
利用相关子查询，查询所有借阅了图书的借书证信息。
use bookDB
go
select *from 借书证1
where exists (select *from 图书借阅 where 借书证1.借书证编号=图书借阅.借书证编号)
9.10 having分组后再条件
where 是先条件再分组
例：
建表：职工表employee和仓库表warehouse
create table warehouse(
w_id number,
city varchar(20),
area number
);
#修改表得到主键
ALTER TABLE warehouse ADD CONSTRAINT constraint_name PRIMARY KEY (w_id);
insert into warehouse (w_id,city,area) values(1,'北京',370);
insert into warehouse (w_id,city,area) values(2,'上海',500);
insert into warehouse (w_id,city,area) values(3,'广州',200);
insert into warehouse (w_id,city,area) values(4,'广州',300);
insert into warehouse (w_id,city,area) values(5,'天津',340);
insert into warehouse (w_id,city,area) values(6,'上海',350);
insert into warehouse (w_id,city,area) values(7,'上海',600);
insert into warehouse (w_id,city,area) values(8,'天津',300);
commit；
create table employee
(
emp_id number(15),
name varchar(20),
w_num number(15) references warehouse (w_id) not null,
wage number,
sex varchar(5)
);
insert into employee (emp_id,name,w_num,wage,sex) values(1,'朱迪',2,2220,'女');
insert into employee (emp_id,name,w_num,wage,sex) values(2,'牛丽丽',1,1810,'女');
insert into employee (emp_id,name,w_num,wage,sex) values(3,'李安',2,1850,'男');
insert into employee (emp_id,name,w_num,wage,sex) values(4,'王进步',3,1530,'男');
insert into employee (emp_id,name,w_num,wage,sex) values(5,'李光明',1,1550,'男');
insert into employee (emp_id,name,w_num,wage,sex) values(6,'赵芙蓉',4,2300,'女');
insert into employee (emp_id,name,w_num,wage,sex) values(7,'刘山',4,2000,'男');
insert into employee (emp_id,name,w_num,wage,sex) values(8,'张尚琳',5,2050,'女');
insert into employee (emp_id,name,w_num,wage,sex) values(9,'王玛丽',5,1900,'女');
insert into employee (emp_id,name,w_num,wage,sex) values(10,'胡尼克',6,2100，'男');
insert into employee (emp_id,name,w_num,wage,sex) values(11,'古梅',7,1700,'女');
insert into employee (emp_id,name,w_num,wage,sex) values(12,'胡俊',5,1780,'男');
insert into employee (emp_id,name,w_num,wage,sex) values(13,'胡轩',1,1600,'男');
commit；
问1：检索出哪些仓库女职工的人数达到了3人。
视图
10.1 创建视图
（1）SSMS方式创建视图
（2）T-SQL 创建视图
T-SQL创建视图：在“bookDB”数据库中，创建一个包含列ISBN,图书名称,作者,出版社，出版日期，图书类型，价格和版次，并且书名包含“数据库”课程的视图，视图命名为v_view_bookInfo 。
use bookDB
go
create view v_view_bookInfo
--WITH ENCRYPTION 加密
as
select ISBN编号 as 'ISNB',图书名称,作者,出版社名称 as'出版社',出版日期,图书类型名称 as '图书类型',价格
from 出版社 inner join 图书信息
on 出版社.出版社ID = 图书信息.出版社ID
inner join 图书类型
on 图书信息.图书类型代码=图书类型.图书类型代号
where 图书名称 like '%数据库%'
10.1.2 修改视图
alter view v_view_bookInfo
as
10.1.3 重命名视图
将v_图书信息视图重命名为v_bookInfo
sp_rename v_图书信息,v_bookInfo
函数
12.1 T-SQL
（1）查看版本信息
print @@version
print @@servicename --消息形式显示出
select @@version --表格形式显示出
（2）常量赋值
declare @homepage char(100)
set @homepage = 'http://www.163.com' --赋值语句
-- select @homepage= 'http://www.163.com' --第二种方式
print @homepage --输出常量值
（3）变量赋值
declare @i int,@j int ,@sum int --定义的时候可以连续定义
set @i=50 -- 赋值的时候必须分开
set @j=60
set @sum =@i+@j
print @sum
--select @sum -- 表格形式显示
（4）数据库查询赋值给变量
use bookDB
declare @count int
set @count=(select count(ISBN编号)as'图书数量' from 图书信息)
print '图书种类'+ltrim(str(@count)) -- str函数将数值转化为字符型,ltrim函数，去掉左边的空格
--select @count'图书总数' --表格显示
（5）变量在sql语句中的使用
例：
为 “bookDB” 数据库声明两个变量@publisher,@bookType，并为它们赋值，然后将他们应用到select语句中，用来查询指定出版社和图书类型图书信息,要求显示ISBN、图书名称、出版社、图书类型、价格和版次。（提示：出版社及图书类型自定）
use bookDB
go
declare @publishname varchar(30),@booktype varchar(20)
set @publishname = '高等教育出版社'
set @booktype ='工业技术'
select ISBN编号,图书名称,出版社名称 as '出版社',图书类型名称 as '图书类型',价格 from 图书信息 inner join 出版社
on 出版社.出版社ID = 图书信息.出版社ID
inner join 图书类型
on 图书类型.图书类型代号=图书信息.图书类型代码
where 出版社名称=@publishname and 图书类型名称=@booktype
（6）if 、begin…end…（相当于花括号）、print 语句综合运用
例：
在“bookDB”数据库中检索查询“石磊”同学是否借阅了图书，如果已借阅图书则显示其借阅的总数量。
use bookDB
go
declare @count2 int ,@readerName varchar(20)
set @readerName = '石磊'
if exists (select * from 图书借阅 where 借书证编号 in (select 借书证编号 from 借书证1 where 姓名 = 'readerName'))
begin
set @count2=(select sum(借出数量) from 图书借阅 where 借书证编号 in (select 借书证编号 from 借书证1 where 姓名='readerName'))
print @readerName+'一共借了'+Ltrim(str(@count2))+'本书'
end
else
print @readerName+'没有借书记录'
（7）case语句
select 借书证编号,图书编号,借出数量,图书状态=
case 图书状态
when '0' then '借出'
when '1' then '续借'
when '2' then '损坏'
when '3' then '丢失'
end
from 图书借阅
（8）Waitfor
时间设置语句后，后面只能跟一条语句，所以可以用begin …end ，结尾记得加上end
waitfor
delay'00:00:10'--延迟10秒执行
-- time '20:44:10' --到这个时间才开始执行
begin
select 借书证编号,图书编号,借出数量,图书状态=
case 图书状态
when '0' then '借出'
when '1' then '续借'
when '2' then '损坏'
when '3' then '丢失'
end -- begin end
from 图书借阅
end --这里不加它报错
（9）while 语句
例：
编写程序计算1+2+3+4+…+10000的和，并显示计算结果
declare @i int,@sum int
set @i=0
set @sum=0
while @i<10000
begin
set @i=@i+1
set @sum = @sum+@i
end
print @sum
12.2 数据库方法
12.2.1 数学方法
12.2.2 字符串方法
12.2.3 日期与时间函数
12.2.4 系统函数
print round(3.1415926,2) --保留两位小数
print sqrt(3) --开平方
print ceiling(134.393) --返回大于本身的最小整数 135
print floor(134.393) -- 返回小于本身的最大整数 134
print power(3,4) -- 3的4次方
print char(96) -- 返回ASCII 表中对应的字符
print replicate('a',10) -- 重复出现a 十次
print len('SQL Server 数据库技术及应用')-- 计算字符串长度，空格也算
print replicate('hello',2)+replicate('#',10)+replicate('word',2)--结果：hellohello##########wordword
print charindex('数据库','SQL Server 数据库技术及应用') --找出数据库在‘SQL Server 数据库技术及应用’ 在数据库第一次出现的位置
print dateadd(mm,10,' 2020-5-1') -- 第二个参数根据第一个参数的值（YY，MM，DD),来返回增加后的时间
print getdate() --返回服务器当前日期
print month(getdate()) --显示当前时间的月份数
print datename(MM,getdate()) --显示当前时间的月份名字
--计算年龄
print datediff(YY, '1999/12/24','2020/12/24')
print year(getdate()) - year('1999/12/24')
-- 字符串转化为数字
select CONVERT (decimal(10,4),'10.34567') -- 显示10位宽度 4位小数
-- 计算字符串所占用的字节数
select datalength('SQL Server 数据库技术及应用')
索引
13.1.1 SMSS创建非聚集索引
13.1.2 T-SQL创建非聚集索引
用T-SQL语句“BookDB”数据库中，对于“出版社”表，定义列出版社名称非聚集索引
use bookDB
go
create nonclustered
index Ix_读者姓名
on 读者信息(读者姓名)
13.1.3 重命名索引
将出版社表名为IX_出版社名称的索引重命名为“IX_publisher_name”
use bookDB
go // 表.旧索引名，新索引名
sp_rename '出版社.Ix_出版社名称','Ix_publisher_name'
13.1.4 删除索引
删除表出版社中出版社名称的唯一性非聚集索引。 IX_publisher_name
use bookDB
go
drop index 出版社.Ix_publisher_name
13.1.5 索引查询
在BookDB数据库中的读者信息表上查询姓“李”的读者信息，并分析哪些索引被系统引用。
use bookDB
go
set showplan_all ON
go
select* from 读者信息
where 读者姓名 like '李%'
go
set showplan_all off
13.1.6 索引磁盘活动亮查看
对“bookDB”数据库中的“读者信息”表上查询姓名为“王永林”的读者信息，并分析执行该数据查询所花费的磁盘活动量信息。
use bookDB
go
set statistics IO on
go
select*from 读者信息 where 读者姓名='王永林'
go
set statistics IO off
触发器
for 、after 是后触发器，即先执行sql 语句再执行触发器内容
instead of 则是先执行触发器内容再执行sql语句
14.1.1 创建insert触发
在BookDB数据库创建1个命名为“borrow_insert”的DML触发器，当向“图书借阅”数据表插入1条借阅记录时，返回1条提示信息“已成功插入1条记录”。
use bookDB
go
create trigger trig_dep_insert
on 部门
for insert
as
print '成功插入一条记录'
insert into 部门
values('15','教务处','学生处')
14.1.2 创建delete触发器
创建1个命名为“booktype_delete”的DML触发器，该触发器实现以下功能：限制用户删除“图书类型”数据表中的记录，当用户删除时出现“不能删除图书类型数据表中的记录”的提示信息。
use bookDB
go
create trigger trig_booktype_delete
on 图书类型
instead of delete
as
print '不能删除图书类型表中的数据'
delete from 图书类型 where 图书类型代号='z'
14.1.3 创建update触发器：
应用数据库“bookDB”中的“图书信息”表，定义一个触发器，当用户修改“图书信息”表中的图书名称时将触发禁止修改的事件。
use bookDB
go
create trigger trig_图书信息修改
on 图书信息
for update
as
if update(图书名称)
begin -- 相当于 {
print('图书名称不能被修改，该事务不能被处理')
rollback transaction
end -- 相当于 }
//先执行update 语句，然后触发触发器执行as 内容，触发器发现你要修改禁止的字段，执行事务回滚
update 图书信息 set 图书名称='软件工程基础'
where 图书名称 = '软件工程基础'
14.1.4 应用触发器同步更新多个数据表中的数据
在图书管理数据库的“图书借阅”数据表中创建1个触发器，当读者借出1本图书时，对应的“藏书信息”数据表的“馆内剩余”字段值也同步更新。
use bookDB
go
create trigger trig_borrow_store
on 图书借阅
after insert
as
update 藏书信息
set 藏书信息.馆内剩余 = 藏书信息.馆内剩余-temp.借出数量
from 藏书信息 join inserted as temp on 藏书信息.图书编号 = temp.图书编号
-- insert 语句 会被触发器单独放在一张临时表，inserted 中 ，所以这里只是给它取别名 temp
select * from 藏书信息 where 图书编号 = 'C7501163723'
insert into 图书借阅(借阅ID,借书证编号,图书编号,借出数量,借出日期,应还日期,借阅操作员,归还操作员,图书状态)
values('16','0016585','C7501163723',2,'2020-5-1','2020-7-1','江河','蓝天','1')
14.1.5 创建DDL触发器：
创建一个DDL触发器，用于防止用户删除或更改bookDB数据库中的任意一个数据表。
use bookDB
go
create trigger protect_tiger
on database
for drop_table,alter_table
as
print '不能删除或修改表'
rollback
drop table 图书借阅
练习一
-- （1）创建数据库
create database 学号44
on
(
name = 学号-44-1,
FileName='d:\bookDB\学号44\学号44-1.mdf',
size=3mb,
Maxsize=100mb,
filegrowth=2%
),
(
name = 学号-44-2,
FileName='d:\bookDB\学号44\学号44-2.ndf',
size=2mb,
Maxsize=50mb,
filegrowth=2mb
),
(
name = 学号-44-3,
FileName='d:\bookDB\学号44\学号44-3.ndf',
size=2mb,
filegrowth=1mb
)
--（2）创建表
use 学号44
go
create table 班级
(
班级编号 varchar(50) not null,
班级名称 varchar(10) not null unique,
辅导员 varchar(10)
primary key(班级编号,班级名称)
)
create table 教师表
(
教师编号 varchar(50) not null,
教师姓名 varchar(10) not null unique,
性别 varchar(10) ,
生日 datetime,
毕业院校 varchar(50) ,
primary key(教师编号)
)
create table 教室表
(
教室编号 varchar(50) not null,
教室类型 varchar(10) not null unique,
座位数 smallint
constraint PK_教室 primary key(教室编号)
)
create table 课程表
(
课程编号 varchar(50) not null,
课程名 varchar(50) not null unique,
课程类型 varchar(10) ,
课时 smallint
constraint PK_课程 primary key(课程编号)
)
create table 排课表
(
学年 varchar(10),
学期 varchar(10),
周次 varchar(10) not null,
节次 varchar(10) not null,
班级名称 varchar(10) not null,
教师姓名 varchar(10) not null,
课程名 varchar(50) not null,
教室类型 varchar(50) not null,
primary key(学年,学期,周次,节次),
constraint PK_排课_班级 foreign key(班级名称) references 班级表(班级名称),
constraint PK_排课_教师 foreign key(教师姓名) references 教师表(教师姓名),
constraint PK_排课_教室 foreign key(教室类型 ) references 教室表(教室类型 ),
constraint PK_排课_课程 foreign key(课程名) references 课程表(课程名)
)
-- （3）插入数据
insert into 班级表(班级编号,班级名称,辅导员)
values('B001','18计科1班','谢芬'),
('B002','18计科2班','谢芬')
('B003','网络技术1班','谢芬')
insert into 教师表(教师编号,教师姓名,性别)
values('0407','范新刚','男')
insert into 课程表(课程编号,课程名,课时,课程类型)
values('C001','数据库技术',64,'必修课'),
('C002','Linux操作系统',64,'必修课')
('C003','Java教程',70,'必修课')
insert into 教室表(教室编号,教室类型,座位数)
values('信工财经楼301','操作系统实训室',60),
('信工财经楼305','数据库设计实验室',60)
insert into 排课表(学年,学期,周次,节次,班级名称,课程名,教师姓名,教室类型)
values('2019-2020','上','周一','1-2节','18计科1班','Linux操作系统','范新刚','信工财经楼305'),
('2019-2020','上','周三','5-6节','18计科2班','Linux操作系统','范新刚','信工财经楼305'),
('2019-2020','上','周二','5-6节','18计科2班','数据库技术','范新刚','信工财经楼301'),
('2019-2020','上','周二','7-8节','18计科1班','数据库技术','范新刚','信工财经楼301'),
('2019-2020','上','周四','7-8节','网络技术1班','Java教程','范新刚','信工财经楼301')
alter table 教室表
alter column 教室类型 varchar(50) null
-- （4）查询
select *from 课程表 where 课时>64
select 学年,学期,班级名称,教师姓名 from 排课表 where 课程名='数据库技术'
select 课程类型,avg(课时)as 平均课时 from 课程表 group by 课程类型
select 课程名,教师姓名,节次,周次,教室类型 as 课室 from 排课表 where 班级名称='网络技术1班'
select count(*) as 总数 from 排课表 where 周次='周三' and 教室类型='信工财经楼305' group by 教室类型
-- （5）视图创建
CREATE View View_排课
as
SELECT 班级名称, 课程名, 教师姓名, 教室类型, 学期, 学年, 周次, 节次
FROM 排课表
这里少写了一些代码，有时间再来补
练习二
在该数据库中导入以下的仓库表5.1、职工表5.2、订购表5.3和供应商表5.4。
表5.1仓库表
仓库号 城市 面积
WH1 北京 3700
WH2 上海 5000
WH3 广州 2000
WH4 武汉 4000
WH5 上海 4560
WH6 广州 6700
WH7 珠海 4800
表5.2职工表
仓库号 职工号 月工资
WH2 E1 2220
WH1 E2 2210
WH2 E3 4050
WH3 E4 3230
WH1 E5 3250
WH1 E6 2300
WH4 E7 5000
WH5 E8 4000
WH5 E9 3400
WH6 E10 3800
表5.3 订购表
职工号 供应商号 订购单号 订购日期
E3 S7 OR091204 2009-12-4
E1 S4 OR090101 2009-4-1
E7 S4 OR100402 2010-4-2
E6 S6 OR100121 2010-1-21
E3 S4 OR091115 2009-11-15
E1 S6 OR060201 2006-2-1
E3 S6 OR100312 2010-3-12
E3 S3 OR090302 2009-3-2
E8 S7 OR100928 2010-9-28
E6 S7 OR100712 2010-7-12
E5 S3 OR100507 2010-5-7
表5.4 供应商表
供应商号 供应商名 地址
S3 振华电子厂 西安
S4 华通电子公司 北京
S6 607厂 郑州
S7 爱华电子厂 北京
用SQL语句完成以下的练习题：
1.查询每个城市的仓库总面积。
2.查询每个仓库的职工人数，如果该仓库没有职工，也要列出人数为0。
3.查询在上海工作的职工有多少个。
4.查询哪些职工在上海工作，列出他们的职工号，仓库号。
5.查询与E3号职工有订购业务联系的供应商号，供应商名。
6.查询哪些职工与爱华电子厂有订购业务联系，列出他们的职工号，仓库号。
7.查询每个供应商的订购单数目，列出供应商号和他们的订购单数目。
8.查询工资在3000元以上，并在北京或上海工作的职工，列出他们的职工号和工资。
9.查询与供应商为S3的供应商有业务联系的职工他们的职工号、订购单号、仓库号、城市。
10.查询出哪些仓库没有分配职工。
11.查询出哪些职工的工资高于全体职工平均工资。
12.查询出哪些职工的工资高于他所在仓库的职工的平均工资。
13.求出哪个职工所发出的的订购单最多。
建表
create table warehouse(
w_id number,
city varchar(20),
area number
);
ALTER TABLE warehouse ADD CONSTRAINT constraint_name PRIMARY KEY (w_id);
insert into warehouse (w_id,city,area) values(1,'北京',370);
insert into warehouse (w_id,city,area) values(2,'上海',500);
insert into warehouse (w_id,city,area) values(3,'广州',200);
insert into warehouse (w_id,city,area) values(4,'广州',300);
insert into warehouse (w_id,city,area) values(5,'天津',340);
insert into warehouse (w_id,city,area) values(6,'上海',350);
insert into warehouse (w_id,city,area) values(7,'上海',600);
insert into warehouse (w_id,city,area) values(8,'天津',300);
create table employee
(
emp_id number(15),
name varchar(20),
w_num number(15) references warehouse (w_id) not null,
wage number,
sex varchar(5)
);
ALTER TABLE employee ADD CONSTRAINT PK_empid PRIMARY KEY (emp_id);
insert into employee (emp_id,name,w_num,wage,sex) values(1,'朱迪',2,2220,'女');
insert into employee (emp_id,name,w_num,wage,sex) values(2,'牛丽丽',1,1810,'女');
insert into employee (emp_id,name,w_num,wage,sex) values(3,'李安',2,1850,'男');
insert into employee (emp_id,name,w_num,wage,sex) values(4,'王进步',3,1530,'男');
insert into employee (emp_id,name,w_num,wage,sex) values(5,'李光明',1,1550,'男');
insert into employee (emp_id,name,w_num,wage,sex) values(6,'赵芙蓉',4,2300,'女');
insert into employee (emp_id,name,w_num,wage,sex) values(7,'刘山',4,2000,'男');
insert into employee (emp_id,name,w_num,wage,sex) values(8,'张尚琳',5,2050,'女');
insert into employee (emp_id,name,w_num,wage,sex) values(9,'王玛丽',5,1900,'女');
insert into employee (emp_id,name,w_num,wage,sex) values(10,'胡尼克',6,2100，'男');
insert into employee (emp_id,name,w_num,wage,sex) values(11,'古梅',7,1700,'女');
insert into employee (emp_id,name,w_num,wage,sex) values(12,'胡俊',5,1780,'男');
insert into employee (emp_id,name,w_num,wage,sex) values(13,'胡轩',1,1600,'男');
create table productor(
p_id varchar(10),
p_name varchar(20),
p_address varchar(50)
);
ALTER TABLE productor ADD CONSTRAINT PK_pid PRIMARY KEY (p_id);
insert into productor (p_id,p_name,p_address) values('S3','振华电子厂','西安');
insert into productor (p_id,p_name,p_address) values('S4','华通电子公司','北京');
insert into productor (p_id,p_name,p_address) values('S6','607厂','郑州');
insert into productor (p_id,p_name,p_address) values('S7','爱华电子厂','北京');
create table subscriber(
s_id varchar(20),
s_emp_id number(15) references employee (emp_id) not null,
s_pro_id varchar(10) references productor (p_id) not null,
s_date date
);
insert into subscriber (s_id,s_emp_id,s_pro_id,s_date) values('OR091204',3,'S7',to_date('2009-12-4','yyyy-mm-dd'));
insert into subscriber (s_id,s_emp_id,s_pro_id,s_date) values('OR090101',1,'S4',to_date('2009-4-1','yyyy-mm-dd'));
insert into subscriber (s_id,s_emp_id,s_pro_id,s_date) values('OR100402',7,'S4',to_date('2010-4-2','yyyy-mm-dd'));
insert into subscriber (s_id,s_emp_id,s_pro_id,s_date) values('OR100121',6,'S6',to_date('2010-1-21','yyyy-mm-dd'));
insert into subscriber (s_id,s_emp_id,s_pro_id,s_date) values('OR091115',3,'S4',to_date('2009-11-15','yyyy-mm-dd'));
insert into subscriber (s_id,s_emp_id,s_pro_id,s_date) values('OR060201',1,'S6',to_date('2006-2-1','yyyy-mm-dd'));
insert into subscriber (s_id,s_emp_id,s_pro_id,s_date) values('OR100312',3,'S6',to_date('2010-3-12','yyyy-mm-dd'));
insert into subscriber (s_id,s_emp_id,s_pro_id,s_date) values('OR090302',3,'S3',to_date('2009-3-2','yyyy-mm-dd'));
insert into subscriber (s_id,s_emp_id,s_pro_id,s_date) values('OR100928',8,'S7',to_date('2010-9-28','yyyy-mm-dd'));
insert into subscriber (s_id,s_emp_id,s_pro_id,s_date) values('OR100712',6,'S7',to_date('2010-7-12','yyyy-mm-dd'));
insert into subscriber (s_id,s_emp_id,s_pro_id,s_date) values('OR100507',5,'S3',to_date('2010-5-7','yyyy-mm-dd'));
查询每个城市的仓库总面积。
select city,sum(area) from warehouse group by city;
查询每个仓库的职工人数，如果该仓库没有职工，也要列出人数为0。
select w_id,count(emp_id) as allpeople from (select * from warehouse w left join employee e on w.w_id=e.w_num) group by w_id;
查询在上海工作的职工有多少个。
select count(emp_id) as allpeople from (select w_id,emp_id from (select w_id from warehouse where city='上海' ) w left join employee e on w.w_id=e.w_num);
查询哪些职工在上海工作，列出他们的职工号，仓库号。
select*from (select w_id from warehouse where city='上海') w left join employee e on w.w_id=e.w_num;
查询与E3号职工有订购业务联系的供应商号，供应商名。
select p_id,p_name from (select s_pro_id from subscriber where s_emp_id = 3) s left join productor p on s.s_pro_id = p.p_id;
查询哪些职工与爱华电子厂有订购业务联系，列出他们的职工号，仓库号。
查询每个供应商的订购单数目，列出供应商号和他们的订购单数目。
select s_pro_id,count(s_id) from (select s_id,s_pro_id from productor p left join subscriber s on p.p_id = s.s_pro_id) group by s_pro_id;
查询工资在3000元以上，并在北京或上海工作的职工，列出他们的职工号和工资。
select * from
(select * from (select * from warehouse w left join employee e on w.w_id=e.emp_id) c1 where c1.wage>2000) where city='北京' or city='上海';
查询与供应商为S3的供应商有业务联系的职工他们的职工号、订购单号、仓库号、城市。
select * from (select p_id,e.name,e.emp_id,s.s_id,w.city from productor p full join subscriber s on p.p_id = s.s_pro_id
left join employee e on s.s_emp_id=e.emp_id left join warehouse w on e.w_num=w.w_id ) where p_id='S3';
查询出哪些仓库没有分配职工。
select w_id from (select * from warehouse w left join employee e on w.w_id=e.w_num) where emp_id is null;
查询出哪些职工的工资高于全体职工平均工资。
select * from employee where wage>(select avg(wage) as all_avg_wage from employee);
查询出哪些职工的工资高于他所在仓库的职工的平均工资。
select* from employee e join(select w_num,avg(wage) as avg_wage from employee group by w_num) avg_table on e.w_num=avg_table.w_num where e.wage>avg_table.avg_wage;
求出哪个职工所发出的的订购单最多。
select * from (select max(items) as maxitems from (select s_emp_id,count(*) items from subscriber group by s_emp_id)) c1 left join
(select s_emp_id,count(*) items from subscriber group by s_emp_id) c2 on c1.maxitems = c2.items;
版权声明：本文为Roe_D原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。