约束
PRIMARY KEY(主键)约束
FOREIGN KEY(外键)约束
UNIQUE (唯一)约束
CHECK (核查)约束
/////////////////////////////////////////////////
--****2核心技术*********
--修改数据库名称
use master
go
execute sp_renamedb 'newDemo','Demo'
go
-数据库文件组更名
/*use master
go
alter database school
modify filegroup teachers
name=teacher
go*/
--查看文件组属性
use school
go
exec sp_helpfilegroup
go
--更改数据文件名称
use school
go
alter database school
modify file
(
name=school2,
newname=school3
)
go
--通过sp_helpfile系统存储过程来查看数据文件属性
/*use school
go
exec sp_helpfile
go*/
--修改数据库school中逻辑名称为school2的数据文件物理位置,将其变更到c:/下
use master
go
alter database school
modify file
(
name=school2
filename='c:/school2.ndf' --filename无效
)
go
--修改数据文件的大小
use school
go
/*alter database school
modify file
(
name=school2,
size=23
)
go
--查看数据库的数据文件属性
exec sp_helpfile
go*/
--添加文件组
/*alter database school
add filegroup linshi1
go
--添加数据文件(在linshi1中)
alter database school
add file
(
name=linshi2,
filename='D:/SQL 2005/School/Data/linshi2.ndf',
size=3,
maxsize=20,
filegrowth=1
)
to filegroup linshi1
go
--添加日志文件
alter database school
add log file
(
name=school_log4,
filename='D:/SQL 2005/School/Data/school_log4.ldf',
size=3,
maxsize=20,
filegrowth=1
)
go
exec sp_helpfile
go
*/
--先删除数据文件
/*alter database school
remove file linshi2
go
--再删除文件组
alter database school
remove filegroup linshi1
go
--最后删除日志文件
alter database school
remove file school_log4
go
exec sp_helpfile
go
--查看数据库文件组的属性
exec sp_helpfilegroup
go*/
--卸载数据库
/*use master
go
execute sp_detach_db 'Demo'
go
--附加数据库
use master
go
exec sp_attach_db 'Demo',
'D:/SQL 2005/DATABASE/DATA/demo.mdf'
go*/
--查看master数据库中存储数据库的系统表
/*use master
go
select * from sysdatabases
go*/
--查看数据库school中存储文件组的系统表
use school
go
--select * from sys.filegroups
--go
--查看存储数据文件信息的系统表
select * from sys.database_files
go
/*理解sysobjects系统视图*/
--1、表中没有存储信息的情况
--在数据库中增加其他文件组
use demo
go
/*alter database demo
add filegroup test
go
--在文件组中增加一个数据文件
alter database demo
add file
(
name=test1,
filename='D:/SQL 2005/DATABASE/DATA/test1.ndf',
size=10,
maxsize=20,
filegrowth=1
)
to filegroup test
go
--建表
create table worker
(
id int,
[name] char(20)
)
on test
go
--删除test1数据文件
alter database demo
remove file test1
go
select * from worker
go*/
/*当删除数据文件test1后,这个表在数据库中仍然存在,这是因为表的名称存储在sysobjects中,
表的列的定义则存储在sys.columns中,这些系统视图实际上都存储在主文件组内*/
--2、表中有存储信息的情况
-- 增加一个数据文件
alter database demo
add file
(
name=test2,
filename='D:/SQL 2005/DATABASE/DATA/test2.ndf',
size=10,
maxsize=20,
filegrowth=1
)
to filegroup test
go
insert into worker
(id ,[name])
values(200601,'Alice')
go
insert into worker
(id ,[name])
values(200602,'Alfred')
go
--删除test2
alter database demo
remove file test2
go
/*当表中有数据以后数据文件就不能够删除,因为数据文件中的内容已经不为空.
因此我们可以看出表中的真实数据书存储在数据文件中,表的定义则是存储在主文件组中的*/
======================
use school
go
--查看表的属性
exec sp_help student
go
--修改表名称
exec sp_rename 'student','students'
go
--修改表列的名称
exec sp_rename 'student.name','学生名'
go
use demo
go
--修改表的结构--1、修改列
/*alter table worker
alter column [name] char(30)
go*/
--添加列
/*alter table worker
add city varchar(10)
go*/
--select * from worker
--删除列
/*alter table worker
drop column city
go*/
--修改表的数据类型
/*alter table worker
alter column id char(10)
go*/
/*insert into worker
(id,[name])
values
('200603a','Tom' )
go*/
--修改表的数据类型
/*alter table worker
alter column id int
go*/
/*从上面的列子我们可以看出当字段中存储了字符串数据类型的数值后,此字段就无法转换成数值类型,
能够将字符串类型的列转换成为数字类型,只能是在此列存储的数值都是数字的情况下*/
--将列变为not null
alter table worker
alter column id int not null
go
use school
go
/*alter table 学生
add 毕业时间 char(20)
go*/
--将学生表中毕业日期列数值补全
/*update 学生
set 毕业时间='2005年毕业'
where 学生编号=2006001
go
update 学生
set 毕业时间='尚未毕业'
where 学生编号=2006002
go*/
==========================
use school
go
--在家长表添加带约束的列
/*alter table parents
add salary/*工资*/ money check (salary>0)
go*/
--修改家长表中薪水列
/*update parents
set salary=100000
where id=2001
go
update parents
set salary=-200000
where id=2002
go*/
--为学生表添加限制生源地的约束
/*alter table 学生
add city char(10)
go
alter table 学生
add constraint ck_学生_city check
(city in('北京','天津','上海','广州'))
go*/
--修改学生表中的信息
/*update 学生
set city='北京'
where 学生编号=2006001
go
update 学生
set city='昆明'
where 学生编号=2006002
go*/
--两个相互矛盾的CHECK约束
/*alter table teachers
add salary money
go*/
--约束:限制其薪水>7000
/*alter table teachers
add constraint/*强制*/ ck_teachers_salary_1 check(salary>7000)
go*/
-- 约束:限制其薪水<6000
/*alter table teachers
add constraint/*强制*/ ck_teachers_salary_2 check(salary<6000)
go*/
--更改老师编号为101的薪水
/*update teachers
set salary=4000
where id=101
go
update teachers
set salary=8000
where id=102
go*/
/*从中可以看出:SQL SERVER上不会校验出约束的限制是否正确。约束的作用是叠加的,如果一个列中有多个约束,则
录入的数值就要满足所在列上的所有约束。*/
--DEFAULT约束
--为student表city列定一个默认值约束
/*alter table student
add constraint df_student_city
default('北京') for city
go*/
--录入记录
/*insert into student
(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city)
values
(2006001,'Alice','女',23,1,1,101,2001,'2005年毕业','北京')
go
insert into student
(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city)
values
(2006002,'Rose','女',24,1,2,102,2001,'尚未毕业','天津')
go
insert into student
(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city)
values
(2006008,'MR.wang','男',24,1,1,101,2003,'2006年毕业',default)
go*/
/*一个DEFAULT只能约束一列,不能用于TIMESTAMP,IDENTITY的列*/
--select * from student
--UNIQUE约束
--在student表中添加cardno列
/*alter table student
add cardno char(20)
go*/
--添加×××号
/*update student
set cardno='220200030304304033'
where id=2006001
go
update student
set cardno='220200030304304034'
where id=2006002
go
update student
set cardno='220200030304304035'
where id=2006008
go*/
-- 添加唯一约束
/*alter table student
add constraint uk_student_cardno unique(cardno)
go*/
/*insert into student
(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno)
values
(2006009,'Tank','男',25,2,2,103,2004,'2006年毕业','上海','220200030304304033')
go*/
--primary key和foreign key约束
--创建图书和作者表
/*create table author/*作者*/
(
id int,
[name] char(20)
)
go*/
/*create table titles
(
title_no char(10),
title_name char(10),
author_id int
)
go*/
--主键约束
/*alter table author
alter column id int not null
go
alter table author
add constraint pk_author_id primary key(id)
go
--外键约束
alter table titles
add constraint fk_titles_author_id foreign key(author_id)
references author(id)
go*/
========================================================
/*禁用/启用约束*/
--查看约束信息
use school
go
exec sp_helpconstraint student
go
--禁用city列上的约束
alter table student
nocheck constraint ck_student_city
go
--录入一条来自新疆学生的信息
insert into student
(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno)
values
(2006009,'Kook','男',20,1,3,102,2004,'尚未毕业','新疆','220200030304304040')
go
--启用city列上的约束
alter table student
check constraint ck_student_city
go
--录入一条来自新疆学生的信息
/*insert into student
(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno)
values
(2006010,'Took','男',22,1,2,101,2001,'尚未毕业','新疆','220200030304304040')
go*/
/*对表原始数据不校验添加约束*/
--不校验原始数据的情况下添加约束
/*alter table teachers
with nocheck add constraint
ck_teacher_teaching_age check(teaching_age>5)
go*/
-- 录入教学年龄为6年的教师
/*insert into teachers
(id,[name],gender,age,subject,teaching_age,position,salary)
values
(105,'张月','女','28','语文',6,'初级教师',null)
go
-- 录入教学年龄为4年的教师
insert into teachers
(id,[name],gender,age,subject,teaching_age,position,salary)
values
(106,'张凡','男','28','计算机',4,'初级教师',null)
go*/
/*CHECK为添加约束时的默认选项,代表的是对原始数据进行校验,NOCHECK代表的是添加新的约束时,对原始数据
不进行校验*/
/*使用级联CASCADE*/
--删除ID为101的老师的信息
/*delete from teachers
where id=101
go*/
--修改ID为101的老师的信息
update teachers
set id=110 where id=101
go
/*通过上面的实验可以看出,编号为101的老师信息,由于被学生表所引用,所以无论删除或修改这个关键
字都会报错,因为违反了信息的完整性。*/
/*为了在更改老师表编号列时,实现在学生表中同步更改老师编号,需要重新维护学生表中的外键约束*/
--删除学生表上与老师表关联的外键约束
alter table student
drop constraint fk_student_tescher_id_teacher_id
go
--exec sp_helpconstraint student
--go
--在学生表上重新创建带级联关系的外键约束
/*alter table student
add constraint fk_student_teacher_id_teacher_id
foreign key(teacher_id) references teachers(id)
on delete cascade
go*/
--;删除老师101的信息
/*delete from teachers
where id=101
go
select * from student
go
select * from teachers
go*/
/*从上面可以看出,和101有关的信息都被删除了*/
/*创建/使用规则*/
--创建年龄规则
/*create rule age_rule
as @age>0
go*/
--将规则与学生表列绑定
--exec sp_bindrule age_rule,'student.age'
--go
--录入一个学生的信息/
/*insert into student
(id,[name],gender,age,class,teacher_id,parents_id,graduation,city,cardno)
values
(2006011,'小明','男',-9,2,103,2004,'尚未毕业','北京',220200030304304342)
go*/
--将规则从列上解除绑定/删除规则
--drop rule age_rule
--go
/*可以看到运行出错*/
--解除帮定
--exec sp_unbindrule 'student.age'
--go
--录入一个学生的信息/
/*insert into student
(id,[name],gender,age,class,teacher_id,parents_id,graduation,city,cardno)
values
(2006011,'小海','男',-9,2,103,2004,'尚未毕业','北京',220200030304304342)
go*/
--删除规则
--drop rule age_rule
--go
/*可以看出,要删除,必须先解除*/
/*创建使用DEFAULT对象*/
--创建默认值对象
/*create default df_age
as 23
go
--绑定
exec sp_bindefault df_age,'student.age'
go*/
--录入一个学生的信息
insert into student
(id,[name],gender,age,class,teacher_id,parents_id,graduation,city,cardno)
values
(2006012,'小刚','男',default,2,102,2001,'尚未毕业','天津',220200030304304343)
go
--解除绑定
exec sp_unbindefault 'student.age'
go
--删除DEFAULT对象
drop default df_age
go
==================================================
--创建名称为v_student_teacher的视图
/*create view v_student_teachers
as
select s.id as 'student_id',
s.[name] as 'student_name',
s.gender as 'student_gender',
s.age as 'student_age',
s.grade,
s.class,
s.teacher_id as 's_teacher_id',
s.parents_id,
s.graduation,
s.city,
s.cardno,
t.id as 'teacher_id',
t.[name] as 'teacher_name',
t.gender as 'teacher_gender',
t.age as 'teacher_age',
t.subject,
t.teaching_age,
t.position,
t.salary
from student as s
inner join
teachers as t
on s.teacher_id=t.id
go*/
--使用视图
--select * from v_student_teachers
--go
/*创建视图:定义视图的查询不可以包含ORDER BY、COMPUTE 或 COMPUTE BY句子或INTO关键字。*/
--查询视图
select * from v_student_teachers
go
--创建视图
/*create view v_student as
select * from student
go
--访问视图
select * from v_student
go*/
/*对表student进行INSERT,UPDATE操作*/
/*select * from student
go
select * from v_student
go
--对id为2006011的学生信息进行UPATE更新操作
update student
set city='大连' where id='2006011'
go
select * from student
go
select * from v_student
go
/*对表student进行INSERT,UPDATE操作*/
select * from student
go
select * from v_student
go*/
--进行INSERT插入操作
/*insert into student(id,[name],gender,age,graduation)
values(2006014,'小萌','男','23','已经毕业')
go*/
--对id为2006011的学生信息进行UPATE更新操作
/*update student
set city='天津' where id='2006011'
go
select * from student
go
select * from v_student
go*/
--对id为2006013的学生信息进行delete删除操作
delete from student
where id='2006013'
go
select * from student
go
select * from v_student
go
--查询学生表与学生老师信息视图
/*select id,[name],age
from student
go
select student_id,student_name,student_age
from v_student_teachers
go */
--通过视图student_teacher向表student中插入一条数据
/*insert into v_student_teachers
(student_id,student_name,student_age,graduation)
values
('2006013','小萌','24','已毕业')
go
select id,[name],age
from student
go
select student_id,student_name,student_age
from v_student_teachers*/
--where student_id='2006013'
--通过视图更改数据
/*select id,[name],age
from student
go
select student_id,student_name,student_age
from v_student_teachers
go*/
--通过视图student_teacher更改表student中,学生"小萌"的年龄
/*update v_student_teachers
set student_age='23'
where student_name='小萌'
go
select id,[name],age
from student
go
select student_id,student_name,student_age
from v_student_teachers
go*/
--通过视图删除数据
delete v_student_teachers
where student_name='小萌'
go
/*说明当一个视图由两个或两个 以上基表构成时,不允许通过视图删除基表中的数据,
即只能通过单基表视图删除数据*/
--分区视图
/*创建三个数据库,并分别在三个数据库上建立表*/
/*create database jan1998
on primary
(
name=jan1998_data,
filename='D:/SQL 2005/DATABASE/DATA/jan1998_data.mdf',
size=10,
maxsize=50,
filegrowth=1
)
log on
(
name=jan1998_log,
filename='D:/SQL 2005/DATABASE/Log/jan1998_log.ldf',
size=10,
maxsize=15,
filegrowth=1
)
go
create database feb1998
on primary
(
name=feb998_data,
filename='D:/SQL 2005/DATABASE/DATA/feb1998_data.mdf',
size=10,
maxsize=50,
filegrowth=1
)
log on
(
name=feb1998_log,
filename='D:/SQL 2005/DATABASE/Log/feb1998_log.ldf',
size=10,
maxsize=15,
filegrowth=1
)
go
create database mar1998
on primary
(
name=mar1998_data,
filename='D:/SQL 2005/DATABASE/DATA/mar1998_data.mdf',
size=10,
maxsize=50,
filegrowth=1
)
log on
(
name=mar1998_log,
filename='D:/SQL 2005/DATABASE/Log/mar1998_log.ldf',
size=10,
maxsize=15,
filegrowth=1
)
go
--建立1~3月份的销售表
use jan1998
go
create table jan1998sales
(
orderid int,
customerid int not null,
orderdate datetime null,
check(datepart(yy,orderdate)=1998),
ordermonth int
check(ordermonth=1),
deliverydate datetime null,
check(datepart(mm,deliverydate)=1),
constraint OrderIDMonth primary key(orderid,ordermonth)
)
go
use feb1998
go
create table feb1998sales
(
orderid int,
customerid int not null,
orderdate datetime null,
check(datepart(yy,orderdate)=1998),
ordermonth int
check(ordermonth=2),
deliverydate datetime null,
check(datepart(mm,deliverydate)=2),
constraint OrderIDMonth primary key(orderid,ordermonth)
)
go
use mar1998
go
create table mar1998sales
(
orderid int,
customerid int not null,
orderdate datetime null,
check(datepart(yy,orderdate)=1998),
ordermonth int
check(ordermonth=3),
deliverydate datetime null,
check(datepart(mm,deliverydate)=3),
constraint OrderIDMonth primary key(orderid,ordermonth)
)
go*/
--插入数据记录
/*use jan1998
go
insert into jan1998sales
values(1,101,convert(datetime,'01/15/1998',101),'1',convert(datetime,'01/15/1998'))
go
insert into jan1998sales
values(2,101,convert(datetime,'01/11/1998',101),'1',convert(datetime,'01/17/1998'))
go
use feb1998
go
insert into feb1998sales
values(3,103,convert(datetime,'02/21/1998',101),'2',convert(datetime,'02/11/1998'))
go
use mar1998
go
insert into mar1998sales
values(6,108,convert(datetime,'03/07/1998',101),'3',convert(datetime,'03/13/1998'))
go
insert into mar1998sales
values(7,108,convert(datetime,'03/09/1998',101),'3',convert(datetime,'03/13/1998'))
go
insert into mar1998sales
values(8,108,convert(datetime,'03/10/1998',101),'3',convert(datetime,'03/13/1998'))
go
use jan1998
go
select * from jan1998sales
go
use feb1998
go
select * from feb1998sales
go
use mar1998
go
select * from mar1998sales
go*/
/*建立1~3月份的分区视图*/
use jan1998/*怎么用多个数据库??*/*************************************>????????
go
create view year1998sales
as
select * from jan1998sales
union all
select * from feb1998sales
union all
select * from mar1998sales
go
/*查询1~3月份的分区视图*/
use jan1988/*怎么用多个数据库??*/*************************************>????????
go
--建立索引视图基表,将ANSI_NULLS设置为ON
use school
go
select * from year1998sales
where ordermonth in (1,3)
go
/*set ansi_nulls on
create table student_idx
(
id int not null primary key,
[name] char(30) ,
age char(10)
)
go*/
--向基表插入数据
/*insert into student_idx
values
(2006012,'小辉','27')
go*/
--建立索引视图将ANSI_NULLS,QUOTED_IDENTIFIER设置为ON
/*set ansi_nulls on
go
set quoted_identifier on
go
create view dbo.v_student_idx
with schemabinding /*架构绑定将视图绑定到基础基表的架构*/
as
select s.id as id,s.name as [name],s.age as age
from dbo.student_idx as s
go*/
/*在视图建立索引,将ANSI_NULLS,ANSI_PADDING,ANSI_WARNING,CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER设置为ON,
将NUMERIC_ROUNDABORT设置为OFF*/
/*set ansi_nulls,ansi_padding,ansi_warnings,concat_null_yields_null,quoted_identifier on;
set numeric_roundabort off
create unique clustered index idx_v_student_idx
on v_student_idx(id)
go*/
/*修改/删除视图*/
--修改视图v_student_teachers
/*alter view v_student_teachers
as
select s.id as 'student_id',
s.[name] as 'student_name',
s.gender as 'student_gender',
s.age as 'student_age',
s.grade as 'student_grade',
s.class,
s.teacher_id as 's_teacher_id',
s.parents_id,
s.graduation,
s.city,
s.cardno,
t.id as 'teacher_id',
t.[name] as 'teacher_name',
t.gender as 'teacher_gender',
t.age as 'teacher_age',
t.subject,
t.teaching_age,
t.position,
t.salary
from student as s
inner join
teachers as t on s.teacher_id=t.id
go*/
/*通过上面的操作,视图就被修改为代码包含SELECT语句结构*/
--重命名视图
/*use school
go
exec sp_rename 'v_student_teachers' , 'student_teachers'
go*/
/*新名字就是student_teachers*/
--删除视图
--drop view v_student_teachers
--go
/*视图与DEFAULT的使用*/
--创建一个具有DEFAULT属性的表
/*create table student_default
(
id int,
[name] char(20),
gender char(2),
age char(10),
grade int default 1 /*使用DEFAULT属性创建的列*/
)
go
--插入数据
INSERT into student_default
values (1,'小刚','男','20',2)
--查询
select * from student_default
go*/
--创建以具有DEFAULT属性列的表为基表的视图
create view v_student_default
as
select * from student_default
go
--通过视图向基表中插入数据,但DEFAULT列不进行插入数值
insert into v_student_default(id,[name],gender,age)
values(1,'小萌','男','22')
go
--查询
select * from student_default
go
select * from v_student_default
go
======第12章=存储过程=====================================================================
/*存储过程*/
use school
go
--创建存储过程
/*create procedure i_student
@id int,@name char(30),@gender char(2),@age int,@grade smallint,
@class smallint,@teacher_id int,@parents_id int,@graduation char(20),
@city char(10),@cardno char(20)
as
insert into student
(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno)
values
(@id,@name,@gender,@age,@grade,@class,@teacher_id,@parents_id,
@graduation,@city,@cardno)
go*/
--使用存储过程录入信息
/*exec i_student
@id=2006014,@name='Lily',@gender='女',@age=23,@grade=1,@class=1,@teacher_id=103,
@parents_id=2003,@graduation='尚未毕业',@city='天津',@cardno=220200030304304053
go*/
/*不带参数的存储过程*/
--创建一张学生备份表
/*create table student_bak
(
id int,
[name] char(30),
gender char(2),
age char(10),
grade smallint,
class smallint,
teacher_id int,
parents_id int,
graduation char(20),
city char(10),
cardno char(20),
[sysdate] datetime
)
go*/
--创建备份学生表信息的存储过程
/*create procedure p_bak_student
as
insert into student_bak
(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno,[sysdate])
select id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno,getdate()
from student
go*/
--执行存储过程
/*exec p_bak_student
go
--查询
select * from student_bak
go*/
--创建查询学生表的存储过程、
/*create procedure s
as
select * from student
go*/
--运行存储过程
--s /*不是用EXECUTE运行名称为S的存储过程*/
--go
/*封装复杂的SELECT语句*/
--创建封装复杂的SELECT语句的存储过程
/*create procedure s_student_teacher
as
select s.id as '学生号',
s.name as '学生名',
s.teacher_id as '老师编号',
t.name as '老师姓名'
from student as s
inner join
teachers as t
on s.teacher_id=t.id
go*/
--运行
/*exec s_student_teacher
go*/
/*使用带简单参数的存储过程*/
--带学号的存储过程
/*drop procedure s
go
create procedure s @id int
as
select * from student
where id=@id
go*/
--运行
/*s 2006010
go*/
/*使用带有通配符参数的简单存储过程*/
--创建
/*create procedure query_student
@student_name varchar(20)='%'
as
select s.id as '学生号',
s.name as '学生名',
s.teacher_id as '老师编号',
t.name as '老师姓名'
from student s
inner join
teachers t
on s.teacher_id=t.id
where s.name like @student_name
go*/
--运行
--query_student '小%'
--go
/*查看存储过程源代码*/
--exec sp_helptext i_student
--go
--上面存储过程的加密
/*alter procedure i_student
@id int,@name char(30),@gender char(2),@age int,@grade smallint,
@class smallint,@teacher_id int,@parents_id int,@graduation char(20),
@city char(10),@cardno char(20)
with encryption /*这个加密算法至少现在还是安全的*/
as
insert into student
(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno)
values
(@id,@name,@gender,@age,@grade,@class,@teacher_id,@parents_id,
@graduation,@city,@cardno)
go*/
/*查看存储过程源代码*/
--exec sp_helptext i_student
--go
/*通过存储过程传递参数*/
--创建一个带返回参数的存储过程/*返回老师管理下的所有学生数*/
/*create procedure sum_student
@teacher_id int,@sum_student int output
as
select @sum_student=count(id) --COUNT是统计函数
from student
where teacher_id=@teacher_id
go*/
--运行**************????????????????????????????????没有结果显示?????????
/*declare @sum_student int --@sum_student为声明变量
exec sum_student 102/*教师编号*/,@sum_student
go*/
--重命名以及删除存储过程
drop procedure procedure_name
==========第13章=函数=============================================================
use school
go
/*使用平均函数AVG获得学生的平均成绩*/
--建表
/*create table result
(
student_id int,
subject char(10),
result float,
grade smallint,
class smallint
)
go*/
--录入成绩
/*insert into result
(student_id,subject,result,grade,class)
values
(2006002,'数学',90.5,1,1)
go
insert into result
(student_id,subject,result,grade,class)
values
(2006003,'数学',92.5,1,1)
go
insert into result
(student_id,subject,result,grade,class)
values
(2006005,'数学',80.0,1,1)
go
insert into result
(student_id,subject,result,grade,class)
values
(2006006,'数学',70.5,1,2)
go
insert into result
(student_id,subject,result,grade,class)
values
(2006002,'语文',65,1,1)
go
insert into result
(student_id,subject,result,grade,class)
values
(2006003,'语文',72,1,1)
go
insert into result
(student_id,subject,result,grade,class)
values
(2006005,'语文',88.5,1,1)
go
insert into result
(student_id,subject,result,grade,class)
values
(2006006,'语文',73,1,2)
go*/
--计算平均成绩
/*select avg(result) as '平均成绩',subject as '学科',
grade as '年级',class as '班级'
from result
where grade=1
group by grade,class,subject
go*/
--计算主科平均成绩
/*select avg(result) as '主科平均成绩',grade as '年级',class as '班级'
from result
group by grade,class
go*/
/*系统函数*/
--配置函数
--@@VERSION函数:返回产品信息
--select @@version as 'SQL SERVER版本'
--go
--@@SERVERNAME函数:返回与运行SQL SERVER本地服务名称
--select @@servername as '服务名称'
--go
/*如果是命名实例则返回值是servername/instance,如果是虚拟服务器+默认实例则返回值是vitualservername,
如果是虚拟服务器+命名实例则返回值是vitualservername/instancename*/
--@@LOCK_TIMEOUT函数:返回当前会话的当前锁定超时设置(毫秒)
--select @@lock_timeout as '锁等待时间'
--go
--设置等待时间
--set lock_timeout 100
--go
--@@LANGUAGE:返回当前所使用的语言名称
--select @@language as '当前服务使用的语言'
--go
--@@MAX_CONNECTIONS:返回SQL SERVER实例允许同时并行访问的最大用户连接
--select @@max_connections as '当前最大并行访问设置'
--go
/*可以使用系统存储过程SP_CONFIGURE来重新配置最大并行访问用户连接*/
--其他配置函数
--@@options,@@dbts,
/*日期与时间函数*/
--GETDATE()函数:返回系统当前时间
--select getdate() as '当前系统时间'
--go
--year(date)函数:返回指定日期"年"的整数
--select year('03/12/2005') as '年'
--go
--month(date)函数:返回指定日期"月"的整数
--select month('03/12/2005') as '月'
--go
--day(date)函数:返回指定日期"日"的整数
--select day('03/12/2005') as '日'
--go
--day(date)函数:返回一个整数,是该日期在当月的天数
/*select getdate() as '当前系统时间'
go
select day(getdate()) as '当天在本月的位置'
go*/
--DATEADD(datepart,number,date)函数:返回给指定日期加上一个时间间隔后的新的时间数值,类型为datetime
/*datepart参数格式:日期部分--缩写/year--yy,yyyy/quarter--qq,q/month--Mm,m/dayofyear--dy,y
day--dd,d/week--wk,ww/hour--Hh/minute--mi,n/second--ss,s/millisecond--Ms*/
--select dateadd(day,20,getdate()) as '20天后的时间'
--go
--DATEDIFF(DATEPART STARTDATE,ENDDATE):返回两个日期参数STARTDATE ENDDATE之间的间隔
--select datediff(day,getdate(),'12/25/2009') as '今天与圣诞节还差多少天'
--go
--datename(datepart date)函数:返回指定日期的指定日期部分的字符串
--select datename(month,getdate()) as '本月'
--go
/*数学函数*/
--COUNT()函数:返回组中项目的数量
--查询
/*select * from student
go
-- 统计所有
select count(*) as '学生总数' from student
go
--统计年级
select count(grade) as '有效的学生数量' from student
go*/
--AVG()函数:计算平均数
--ABS()函数:返回一个数的绝对值
--select abs(-3333.45) as '-3333.45的绝对值'
--go
--ROUND(NUMERIC_EXPRESSION,LENGTH[,FUNCTION])函数:返回一个数值表达式,舍入到指定的长度或精度
--计算平均值保留2为小数
/*select round(avg(result),2) as '主科平均分',grade as '年级',class as '班级'
from result group by grade,class
go*/
/*字符串函数*/
--SUBSTRING(EXPRESSION,START,LENGTH)函数:返回字符表达式、二进制表达式、文本表达式或图象表达式等的一部分
/*select substring([name],1,2) as '学生姓名的前两个字母'
from student
go*/
--LOWER(CHARACTER_EXPRESSION)函数:将大写字符数据转换为小写字符数据后返回字符表达式
--select lower(name) '小写学生姓名' from student
--go
--LTRIM(character_expression)函数:返回删除了前导空格之后的字符表达式
--select ltrim(' hell o ') as '使用LTRIM函数结果'
--go
--ASCII(charater_expression)函数:返回字符表达式中最左侧的字符的ASCII代码值
/*select ascii('A') as 'A的ASCII代码值'
go
select ascii('a') as 'a的ASCII代码值'
go*/
--REVERSE(charater_expression)函数:返回字符表达式的逆向表达式
--select reverse('Tom') as 'Tom的逆向结果'
--go
/*REPLACE('string_wxpression1','string_wxpression2','string_wxpression3')函数:用第三个表达式替换第一个
字符串表达式中出现的所有第二个指定字符串表达式的匹配项*/
--select replace('abcdeccc','c','x') as '结果'
--go
--LEFT(charater_expression,integer_expression):返回字符串中从左边开始指定个数的字符
--select left('advsdfdsfd',5) as '返回前5个字母'
--go
/*系统统计函数*/
--@@connections函数: 返回SQL自上次启动以来尝试的连接数,无论连接成功与否
--select @@connections as '连接数'
--go
--@@cpu_busy函数: 返回SQL自上次启动以后的工作时间
--select @@cpu_busy as 'cpu占有数量'/*滴答数*/
--go
--@@pack_received函数: 返回SQL自上次启动以后网络读取的输入数据包数
/*select @@pack_received as '数据包数'
go*/
--@@total_read函数: 返回SQL自上次启动以后读取磁盘(不是读取高速缓存)的次数
--select @@total_read as '读取磁盘次数'
--go
/*其他常用函数*/
--ISDATE(expression1)函数: 判断输入的表达式是否为有效日期
/*select isdate('2005/06/06'),
isdate('adfsf'),
isdate('6/6/2005')
go*/
/*ISNULL(expression1,expression2)函数: 判断expression1的值是否为 NULL,如果为空则返回expression2的值,
反之则返回expression1的原值*/
/*select [name] as '学生名称',grade as '年级' from student
go
select [name] as '学生名称',isnull(grade,1) as '年级' from student
go*/
--ISNUMERIC(expression)函数: 判断EXPRESSION是否为有效数据类型,如果是返回1,反之返回0
--select isnumeric('adg'),isnumeric(23432)
--go
--PRINT(expression)函数: 把消息传递到客户应用程序,消息一般是字符型文本,只能使用其来传递字符串或字符型变量
--print('hello world')
--go
--CAST(expression as datatype)函数: 将EXPRESSION转换成另一种数据类型
/*select [name] as '学生名称',
sysdate as '数据备份时间'
from student_bak
go
select [name] as '学生名称',
cast(sysdate as char(20)) as '数据备份时间'
from student_bak
go */
--CONVERT(data_type[(length)],expression[,style])函数:把表达式的值从一种数据类型转换成为另一种
--select convert(char,getdate(),101/*美国*/) as '转换成字符串的时间'
--go
/*用户自定义函数创建*/
use school
go
--创建一个二次函数名为f()
/*create function f(@x int)
returns int
as
begin
return @x*@x+2*@x+1
end;
go*/
--将@X=3带入函数
--select dbo.f(3) as '参数为3的计算结果'
--go
/*创建内嵌表值函数与使用*/
--创建带学生编号参数的函数
/*create function select_student(@id int)
returns table
as
return
select s.name as '学生姓名',
t.name as '老师姓名'
from student s inner join teachers t
on s.teacher_id=t.id
where s.id=@id
go*/
--查询使用函数select_student()
--select * from dbo.select_student(2006005)
--go
/*创建多语句表值函数与使用*/
--创建一个带学生编号参数的多语句表值函数
/*create function select_info(@id int)
returns @information table
(
学生姓名 CHAR(20),
老师姓名 CHAR(20),
父亲姓名 CHAR(20),
母亲姓名 CHAR(20)
)
as
begin
insert into @information
select s.name,t.name,p.father,p.mother
from student s inner join teachers t
on t.id=s.teacher_id inner join parents p
on s.parents_id=p.id where s.id=@id
return
end;
go
select * from dbo.select_info(2006005)
go*/
/*使用用户自定义函数时,都必须加上前面的所有者,前面涉及到dbo就是一个用户,当前创建的用户自定义函数属于这个dbo用户的*/
/*创建函数中限制语句*/
/*用户自定义函数中不能使用的内置函数包括:@@connections,@@pack_sent,@@cpu_busy,@@packet_errors,@@idle,@@timeticks
@@io_busy,@@total_errors,@@max_connections,@@total_read,@@pack_received,@@total_write。除此之外SQL SERVER函数还不能包含的
Transact-SQL有:BEGIN DIALOG CONVERSATION,END CONVERSATION, GET CONVERSATION GROUP,MOVE CONVERSATION,RECEIVE,SEND*/
/*修改/重命名/删除函数*/
--用ALTER FUNCTION更改不会更改权限,不会影响相关的函数、存储过程或者触发器
/*alter function function_name
<new fuction content/*满足,满意*/>
--重命名 使用SP_RENAME
--删除
drop function {[owner.]function_name}[,……n]*/
=====第14章==同义词===================================================================
/*同义词:在SQL2005中的意思是:表、视图、存储过程和函数的可选的别名,与这些数据对象原来的名称使用方法是完全一致的。同义词主要用于必须用多个名称引用同一个对象的场合:
1、新版本的数据库必须支持与旧的进行交互,或者在客户机应用程序中,该对象具有不同名称。
2、在查询和存储过程中,可以用同义词来替换原始对象名,以增加安全性。
3、以架构作为域的对象的备用名称,使客户机应用程序,能够用单部分同义词,而不是两、三部分 来引用某个数据对象。
4、可以简化为另一个架构的对象。
同义词不能是另一个同义词的基对象,且不能引用用户自定义的聚合函数。同义词与基对象之间只是按名称绑定。对基对象的存在性、类型和权限检查都在运行时执行。*/
use school
go
--创建同义词 s_student
/*create synonym dbo.s_student for dbo.student
go
--查询
select * from s_student
go
select * from student
go
--删除
drop synonym s_student
go*/
=====第15章==触发器===================================================================
use school
go
--在学生表上创建一个触发器
/*create trigger tg_student_del
on student
after delete
as
print('您删除了一条学生信息,此学生信息将无法恢复')
go*/
--删除学生信息
/*delete from student
where id=2006005
go*/
/*实现DML触发器*/
/*DML语句中不允许使用的SQL语句:ALTER DATABASE,CTEATE DATABASE,DROP DATABASE,LOAD DATABASE,LOAD LOG,RECONFIGURE,
RESTORE DATABASE,RESTOR LOG;触发操作的目标表上不允许使用的SQL语句:CREATE INDEX,ALTER INDEX,DROP INDEX,
DBCC DBREINDEX,ALTER PARTITION FUNCTION,DROP TABLE,用于执行以下操作的ALTER TABLE:添加、修改或删除列,切换分区,添加或删除PRIMARY
KEY或UNIQUE约束*/
/*AFTER触发器*/
--UPDATE操作
--创建表
/*create table student_update_log
(
sysdate datetime,--记录数据修改日期
log_info varchar(100),--记录修改内容
dml_user varchar(20) --记录修改人
)
go*/
--创建update类型触发器
/*create trigger tg_student_id
on student
after update
as
if update(id)
begin
insert into student_update_log
(sysdate,log_info,dml_user)
values
(getdate(),'修改了学生编号',user_name())
print('您正在更改学生表中的学生编号关键字')
end
go */
--修改
/*update student
set id=2006014
where id=2006005
go
--查询
select sysdate as '表修改时间',
log_info as '修改信息',
dml_user as '修改信息的用户'
from student_update_log
go*/
--DELETE操作
--删除tg_student_del触发器,在日志表中增加修改类型字段
/*drop trigger tg_student_del
go
create table student_del_log
(
sysdate datetime,
del_info varchar(100),
dml_user varchar(20)
)
go
--在表student上创建一个有关删除操作的触发器
create trigger tg_std_del
on student
after delete
as
begin
insert into student_del_log
( sysdate,del_info,dml_user)
values
(getdate(),'删除了学生表信息,将要影响表teachers与parents',USER_NAME())
print('您正在删除学生表中信息')
end
go
-- 删除信息
delete from student
where id=2006014
go
--查看
select sysdate as '删除操作的时间',
del_info as '删除信息日志',
dml_user as '执行删除操作的用户'
from student_del_log
go
--INSTEAD OF触发器(是代替表上的DML操作)
--创建记录删除老师信息的日志表
create table teachers_del_log
(
id int identity(1,1),--使用一个带序列的表
sysdate datetime,
del_info varchar(100),
dml_user varchar(20)
)
go
--创建触发器
create trigger tg_del_teachers
on teachers
instead of delete
as
begin
insert into teachers_del_log
(sysdate,del_info,dml_user)
values
(getdate(),'老师表执行了删除信息的操作',user_name())
print('老师表的信息现在不允许删除,如果要删除请删除或者禁用名为tg_del_teachers的触发器')
end
go
--删除
delete from teachers
where id=105
go
--查看
select sysdate as '删除信息时间',
del_info as '删除信息',
dml_user as '执行删除操作的用户'
from teachers_del_log
go*/
/*inserted与deleted表(临时表保存在高速缓存中)*/
--通过deleted表实现触发器
--查看表上的触发器
/*exec sp_helptrigger student
go
--删除查询出来的触发器
drop trigger tg_student_id
go
drop trigger tg_std_del
go
--在表上创建触发器
--在表上增加一个学生编号的列
alter table student_del_log
add student_id int
go
--清空日志表
truncate table student_del_log
go
--创建触发器
create trigger tg_student_del
on student
after delete
as
begin
--开始记录删除日志
delete from result
where student_id in
(select id from deleted)
insert into student_del_log
(sysdate,del_info,dml_user,student_id)
select getdate(),'用户运行了删除操作',user_name(),id
from deleted
print('用户运行了删除操作,将学生表中的学生信息删除了')
end
go
--查看
select * from student
go
select * from result
go
--删除
delete from student
where id=2006006
go
--重新查看
select * from student
go
select * from result
go
--查询
select student_id as '删除的学生表',
sysdate as '删除操作的时间',
del_info as '删除信息记录',
dml_user as '执行删除操作的用户'
from student_del_log
go*/
/*通过其他方法来实现上面的触发器功能(使用视图方法,以student与result为基表,做一个以student为左表,
result为右表,通过学生编号作右外联接的视图.通过访问视图的方法老实现删除功能)*/
--创建视图
/*create view query_std_rsl
as
select s.id,r.student_id from student s right outer join result r on
s.id=r.student_id
go
--创建触发器
create trigger tg_student_result_del
on student
after delete
as
begin
delete from result
where student_id in
(select student_id from query_std_rsl where id is null)
print('你删除了学生的信息,其附带的学生成绩信息也被删除了')
end
go
--删除
delete from student
where id=2006007
go*/
--创建一个数据库作用域上的触发器
--禁止在school上删除表
use school
go
/*create trigger forb_drop_table
on database for drop_table
as
begin
print'对不起这个数据库不允许删除表,如果要删除表请先删除触发器forb_drop_table'
rollback
end
go
--删除
drop table student
go*/
/*触发器的管理*/
--语法
/*sp_helptrigger [@tabname=]'table'
[,[@triggertype=]'type']
--删除
drop trigger trigger_name*/
========第16章=SQL语言编程=================================================================
use school
go
--使用变量查找学生信息
/*declare/*声明*/ @id int --使用DECLARE来定义变量
set @id=(select top 1 id from student) --为变量赋值
select * from result
where student_id=@id
go*/
/*数据库定义语言*/
--创建表语句
/*create table table_name
(column_defiination)
go
--删除表
drop table table_name
go*/
/*数据库操纵语言(DML)*/
--添加
/*insert into student
(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno)
values
(2006005,'李治','男',25,1,1,104,2004,'2006年毕业','天津',220200030304304050)
go
--查询
select id as '学号',[name] as '姓名' from student
go
--修改
update student
set name='王志' where id=2006005
go
--查询
select id as '学号',[name] as '姓名' from student
go
--删除
delete from student
where id=2006004
go
--查询
select id as '学号',[name] as '姓名' from student
go*/
/*数据库控制语言(DCL):在设置权限时使用的语句*/
--grant/*授予*/ create table to user_one --授予用户user_one创建表的权限
--revoke/*撤消*/ create table from user_one --撤消用户user_one创建表的权限
--deny/*否决*/ select on student user_on --否决用户user_one在表student上的查询权限
/*变量的使用*/
--局部变量
/*declare
{
@varaible_name datatype[,……n]
}*/
--使用局部变量
/*declare @age int
set @age=24
select * from student where age=@age
go
--或者
declare @age int
select @age=24
select * from student where age=@age
go
--变量适用范围
--处理变量与查询同步使用
declare @maxage int,@minage int
select @maxage=max(age), @minage=min(age),min(age) from student
go*/
/*系统变量*/
--使用系统变量@@connections
/*select getdate() as '当前系统运行时间',
@@connections as '自上次启动到现在的连接数'
go
--变量的使用
--创建函数
create function fun_age(@id int)
returns varchar(20)
as
begin
declare @age int,@result varchar(20)
set @age=(select age from student where id=@id)
if(@age<0 or @age=0)
set @result='错误的年龄'
if(@age>0 and @age<200)
set @result='正确的年龄'
return @result
end
go
--使用
select * from student
go
select dbo.fun_age(2006003)
go
select dbo.fun_age(2006005)
go*/
use school
go
/*控制流程语句*/
--IF ELSE条件判断
/*declare @x int
set @x=3 --赋值
if(@x*@x<8)
begin
print('运算的结果小于8')
end
else
begin
print('运算的结果大于8')
end
go
--BEGIN END 语句
declare @avg float --定义平均成绩变量
set @avg=(select round(avg(result),2) as '学生平均成绩' from result)
if(@avg>80)
begin
print('学生的成绩为:')
print(@avg)
print('学生的平均成绩达到中等以上')
end
else
begin
print('学生的成绩为:')
print(@avg)
print('学生的平均成绩达到中等以下')
end
go
--WHILE 循环
declare @i int
set @i=1
while(@i<10)
begin
print(@i)
set @i=@i+2
end
go
--CASE 关键字
select id as '学生编号',
[name] as '学生姓名',
case grade
when 1 then '一年级'
when 2 then '二年级'
when 3 then '三年级'
end
from student
go
--批(GO)
update student
set age=age+3
go
update result
set result=result/100
go
select * from student
go
--RETURN 关键字
return [integer_expression] --integer_expression是一个整数表达式
--EXISTS使用:判断函数,如果表达式存在,则返回1,反之,返回0
if exists(select count(*) from result)
begin
print('学生成绩表中存储了数据')
end
else
begin
print('学生成绩表中没有存储数据')
end
go
--使用INSERT INTO语句插入多条数据
--SELECT INTO创建表
--创建单表数据
select * into teachers_bak from teachers --快速的转移数据
go
select * from teachers_bak
go
--创建多表数据
select s.id as '学生编号',
s.name as '学生姓名',
s.age as '学生年龄',
s.grade as '年级',
s.class as '班级',
p.id as '家长编号',
p.father as '父亲姓名',
p.mother as '母亲姓名'
into family from student s inner join parents p
on s.parents_id=p.id
go
select * from family
go
--TRUNCATE与DELETE的区别
delete from table_name
where condidiont
--使用TRUNCATE清空表数据
truncate table table_name
--使用DELETE清空表数据
delete from table_name
*/
/*动态SQL语句执行*/
--使用普通SQL语句的局限
--创建表的存储过程
/*create procedure create_table @table_name varchar(30)
as
begin
create table @table_name
(
id int, --存储人员编号
[name] char(20), --存储人员名称
city char(20), --存储人员的出生地
age int --存储人员的年龄
)
end
go*/
/*上面的示例无法创建表,是因为参数 @table_name 无法在存储过程中调用,这就得要动态SQL语句来实现这个功能*/
--创建
/*create procedure create_table @table_name varchar(30)
as
begin
declare @v_sql varchar(200)
set @v_sql='create table'+@table_name+'(id int, [name] char(20), city char(20), age int )'
exec (@v_sql)
end
go*/
--执行
exec create_table @table_name='linshi' --'tablelinshi' 附近有语法错误。
go
--查看
exec sp_help create_table
go
/*嵌套查询*/
--基于列的查询
/*select id as '老师编号',
[name] as '老师姓名',
age as '老师年龄',
subject as '老师所教的科目',
teaching_age as '教学年龄'
from teachers where id
in(select teacher_id from student)
go
--基于比较的查询
select * from result
where result>(select avg(result) from result)
go*/
/*游标的使用:游标是类似于指针的存在*/
--声明游标
******************声明游标***************************
语法:declare cursor_name[insensitive][scroll]cursor for select_statement[for{
read only | update [of column_name[,…n]]}]
cursor_name:所定义的Transact-SQL服务器游标的名称.
insensitive(感觉迟钝的):定义一个游标,以创建将使用的数据库的临时复本.
scroll(滚动):指定所有的提取选项(FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE)均可用。
select_statement:定义游标结果集的标准SELECT语句.
read only:禁止通过该游标进行更新.
update [of column_name[,…n]]:定义游标中可更新的列.
*********************************************
--示例
declare cursor_student cursor
for select * from student
--使用游标
--打开/关闭/释放游标
OPEN CURSOR_NAME
CLOSE CURSOR_NAME
DEALLOCATE CURSOR_NAME
--使用游标取数
*****************使用游标取数****************************
语法:
fetch
[[next|prior|first|last|absolute{n|@nvar}|relative{n|@nvar}]
from
]
{{[global] cursor_name }|@cursor_variable_name}
[into @variable_name[,…n]]
next:紧跟当前行返回结果行,并且递增为返回行.
prior:返回紧邻当前的结果行,并且当前行递减为返回行.
first:返回游标中的第一行并将其作为当前行.
last:返回游标中的最后一行并将其作为当前行.
absolute{n|@nvar}:如果n或@nvar为正数,则返回从游标头开始的第n行,并将返回变成新的当前行.如果n 或 @nvar为负数,,则返回从游标末尾开始的第n行,并将返回变成新的当前行.如果n或@nvar为0,
则不返回行.n必须是正整数常量,并且@nvar的数据类型必须为smallint,tinyint或int.
relative{n|@nvar}:如果n或@nvar为正数,则返回从当前行开始的第n行,并将返回变成新的当前行.如果n 或 @nvar为负数,,则返回从当前之前第n行,并将返回变成新的当前行.如果n或@nvar设置为0为负 数的情况下指定FETCH RELATIVE,则不返回行.n必须是正整数常量,并且@nvar的数据类型必须为 smallint,tinyint或int.
*********************************************
use school
go
--使用游标
--以表parents为游标的基准
--声明游标
/*declare cursor_parents cursor for
select id,father,mother,f_telephone,m_telephone
from parents
--打开游标
open cursor_parents
--第一次使用游标取数
fetch next from cursor_parents
--检查@@FETCH_STATUS以确定是否还可以继续取数
while @@fetch_status=0
begin
fetch next from cursor_parents
end
--关闭游标
close cursor_parents
go*/