@byxiaoxie7 年前
10/11
08:47
1、主键约束（primary key）：一个表中唯一具有标识性的字段，一般一个表中只能有一个主键。设置了主键的字段，该字段的值不重复。
2、唯一约束（unique）：当表中除了主键外，还有其他字段需要保证取值不重复时，可以设置为unique约束。一个表可以定义多个unique约束
3、默认值：希望表中某个字段设置默认值，可以减少录入的时间和不必要的错误。
4、Check约束：用于限制数据值范围和格式。如设置分数在0-100分之间，采用了check约束后，用户不能输入超过该范围的数值，减少错误。
5、Foreige key约束（外键约束）：外键一般用于建立一个表或多个表的字段之间的引用联系。必须要有主键，才能有外键。
主键：CONSTRAINT PK_mytable PRIMARY KEY CLUSTERED (学号 ASC))
学号 char(6) not null PRIMARY KEY
unique约束 （唯一约束）：CONSTRAINT IX_mytable UNIQUE NONCLUSTERED (ID ASC))
学号 char(6) not null UNIQUE NONCLUSTERED
check约束：constraint CK_student_sex check (sex='男' or sex='女')
default定义（默认值设置）：
str char(10) NOT NULL CONSTRAINT DF_mytable_str DEFAULT ' undefined',
性别 char（2） not null CONSTRAINT DF_mytable_str DEFAULT ‘男’
foreign key 约束（外键约束）：
dt date NULL CONSTRAINT FK_表1_表2 FOREIGN KEY(ID) REFERENCES dbo.表2 (ID)
//创建数据库 和 数据库日志
create database stu // stu 数据库名
on(
name=stu_dat, //stu_dat 库名
filename='E:\SQL\stu_dat.mdf', //数据库文件路径
size=10, //初始大小
maxsize=100, //最大大小
filegrowth=10% //自动增长
)
log on //创建日志文件
(name=stu_log, //stu_log 数据库日志名
filename='E:\SQL\stu_log.ldf', //日志文件路径
size=2, //初始大小
maxsize=unlimited, //最大值为无限
filegrowth=10 //增长值为10MB
)
go
//创建表 和 主键与约束
create table XSDA //XSDA 表名
(
学号 char(6) not null primary key, //char(6)型 , 字符串为6字节 , not null 不可为空 , primary key 设置为主键
姓名 char(8) not null,
性别 char(2) not null constraint 性别 check (性别='男' or 性别='女'), //constraint 设置性别只为男和女
系名 char(10) not null,
出生日期 smalldatetime not null, //smalldatetime 时间型默认4字节
民族 char(4) not null,
总学分 tinyint not null,
备注 Text
)create table KCXX //KCXX表名
(
课程编号 char(3) not null primary key,
课程名称 char(20) not null,
开课学期 tinyint not null constraint 开课学期 check (开课学期 >=0 and 开课学期 <=6), //开课学期只可为1到6
学时 tinyint not null,
学分 tinyint not null
)
方法二：
create table KCXX
(
课程编号 char(3) not null primary key,
课程名称 char(20) not null,
开课学期 tinyint not null constraint 开课学期 check (开课学期 between 0 and 6),//开课学期只可为1到6
学时 tinyint not null,
学分 tinyint not null
)create table XSCJ //XSCJ表名
(
学号 char(6) not null,
课程编号 char(3) not null,
成绩 tinyint,
primary key (学号,课程编号) //组合主键 primary key ([字段1],[字段2])
)/*查看表结构*/
exec sp_help xsda
go
/*查看数据类型*/
exec sp_help
go
/*增加学分字段名*/
use stu
go
alter table xscj
add 学分 tinyint null
go
/*删除学分字段名*/
use stu
go
alter table xscj
drop column 学分
go
/*修改姓名字段名类型长度*/
use stu
go
alter table xsda
alter column 姓名 char(10)
go
/*修改出生日期字段名类型*/
use stu
go
alter table xsda
alter column 出生日期 datetime
go
/*利用[sp_rename]修改出生日期为[birthday]*/
use stu
go
sp_rename 'xsda.出生日期','birthday','column'
go
/*修改表名*/
use stu
exec sp_rename'xsda','学生档案'
go
/*外键约束*/
alter table XKXX
add
constraint kc_xsxx foreign key(学号) references XSXX(学号),
constraint kc_kcxx foreign key(课程编号) references KCXX(课程编号)
go
/*添加多个记录*/
insert into Customer(CusNo,CusName,Address,Tel)
values
('001','杨婷','深圳','0755-22221111'),
('002','陈萍','深圳','0755-22223333'),
('003','李东','深圳','0755-22225555'),
('004','叶合','广州','0755-22227777'),
('004','谭欣','广州','0755-22229999')
go/*查询xsda表为汉族的数据*/
use xs
select 学号,姓名,出生日期,民族 = '汉'
from xsda
go/*查询xsda表系名为信息的，并各列的标题分别为 mumber , name , mark*/
use xs
select 系名='信息',学号 as number,姓名 as name,总学分 as mark
from xsda
go/*查询xscj表 108号的课程的成绩，并去除重复行*/
use xs
select distinct 成绩
from xscj
where 课程编号 = 108
go/*查询xscj表的学号，课程编号和成绩，只返回结果前10%*/
use xs
select top 10 percent 学号,课程编号,成绩
from xscj
go/*查询xsda表总学分在55以上的女生的基本信息*/
use xs
select *
from xsda
where (总学分 > 55 and 性别='女')
go/*查询xsda表姓名含有林字的学生的情况*/
use xs
select *
from xsda
where 姓名 like '%林'
go/*查询xsda表出生日期为1988年 上半年出生的学生的姓名和性别和出生日期*/
use xs
select 姓名,性别,出生日期
from xsda
where 出生日期 between '1988-1-1' and '1988-6-30'
go/*查询kcxx表开课学期为第5学期的所有信息，结果按学分降序排序*/
use xs
select *
from kcxx
where 开课学期=5
order by 学分 desc
go/*查询xsda表年龄最大的3名学生的学号,姓名,出生日期*/
use xs
select top 3 学号,姓名,出生日期
from xsda
order by 出生日期 asc
go/*查询xscj表取108号课程的平均值和最高分、最低分*/
use xs
select 课程编号,AVG(成绩) 平均分,MAX(成绩) 最高分,MIN(成绩) 最低分
from xscj
where 课程编号=108
group by 课程编号
go/*查询xscj表选修108号课程的学生人数*/
use xs
select 课程编号,COUNT(学号) 人数
from xscj
where 课程编号=108
group by 课程编号
go/*查询xsda表中的所有男生的总学分的平均值*/
use xs
select AVG(总学分) 平均值
from xsda
where 性别='男'
group by 性别
go/*查询xsda表的各系男生和女生人数，并产生一个总人数*/
use xs
select 性别,COUNT(学号) 人数
from xsda
group by 性别
go
select COUNT(学号) 总人数
from xsda
go
/*查询kcxx表第5学期开设课程的课程编号，课程名称，开课学期，学分，并产生一个开设课程总门数行*/
use xs
select 课程编号,课程名称,开课学期,学分
from kcxx
where 开课学期=5
go
select COUNT(课程名称) 总行数
from kcxx
where 开课学期=5
go/*查询xscj表的成绩平均值，大于90分以上的学生学号*/
use xs
select 学号,AVG(成绩) 平均成绩
from xscj
group by 学号
having avg(成绩)>85
go/*查询xsda和xscj的表的全部字段*/
use xs
select *
from xsda inner join xscj on xsda.学号=xscj.学号
go/*查询xsda表的全部数据，并取xscj表的 课程编号 和 成绩*/
use xs
select xsda.*,xscj.课程编号,xscj.成绩
from xsda inner join xscj on xsda.学号=xscj.学号
go/*查询选修了202号课程的并成绩优秀的学生姓名及成绩*/
use xs
select 姓名,成绩
from xsda join xscj on xsda.学号=xscj.学号
where 课程编号='202' and 成绩>=90
go/*内连接查询，执行结果相同*/
use xs
select 姓名,成绩
from xsda,xscj
where xsda.学号=xscj.学号 and 课程编号='202' and 成绩>=90
go
/*查询多个表的字段，并取课程名称为 计算机文化基础 和 成绩为优秀的*/
use xs
select xsda.学号,姓名,课程名称,成绩
from xsda join xscj join kcxx on xscj.课程编号=kcxx.课程编号 on xsda.学号=xscj.学号
where 课程名称='计算机文化基础' and 成绩>=90
go
/*左外连接 left outer join */
use xs
select xsda.*,课程编号,成绩
from xsda left outer join xscj on xsda.学号=xscj.学号
go/*右外连接 right outer join*/
use xs
select xscj.*,课程名称
from xscj right outer join kcxx on xscj.课程编号=kcxx.课程编号
go/*自连接*/
use xs
select xsda1.姓名,xsda1.学号,xsda2.学号
from xsda as xsda1 join xsda as xsda2 on xsda1.姓名=xsda2.姓名
where xsda1.学号<> xsda2.学号
go/*创建视图，查询xsda表中系名为信息的数据*/
use xs
go
create view 信息系_VIEW
as
SELECT *
FROM xsda
where 系名='信息'
/*创建视图，查询学号,姓名,成绩的平均值，成绩大于60 及 平均值大于80的数据*/
use xs
go
create view 优秀学生_VIEW
as
SELECT xsda.学号,姓名,avg(成绩) as 平均成绩
FROM xsda join xscj on xsda.学号=xscj.学号
where 成绩>60
group by xsda.学号,姓名
having avg(成绩)>80
/*创建视图，查询XSCJ_VIEW视图的课程名称,成绩的最大值*/
use xs
go
create view MAX_VIEW
as
select 课程名称,MAX(XSCJ_VIEW.成绩) as 最高分
from XSCJ_VIEW
group by 课程名称
/*修改优秀学生_VIEW视图，从xscj,xsda表中查询成绩大于75 平均大于80的数据*/
use xs
go
alter view 优秀学生_VIEW
as
select xscj.学号,xsda.姓名,avg(成绩) as 平均成绩
from xscj,xsda
where 成绩>75
group by xscj.学号,姓名
having avg(成绩)>80
/*信息系_VIEW视图中插入一条数据*/
use xs
go
insert into 信息系_VIEW
values('200700','高强','1','信息','1988-10-20','苗',50,null)values('200700','高强','男','信息','1988-10-20','汉',50,null)
/*修改信息系_VIEW视图的备注为对日外包*/
use xs
go
update 信息系_VIEW
set 备注='对日外包'
/*删除视图*/
use xs
go
drop view MAX_VIEW/*创建xsxx并创建check约束*/
use xs
create table xsxx
(
学号 char(6),
姓名 char(8),
性别 char(2) check (性别='男' or 性别='女'),
入学日期 datetime
)
go
/*修改xscj表，增加成绩字段的check约束*/
use xs
alter table xscj
add constraint ck_cj check (成绩>=0 and 成绩<=100)
go
/*删除xscj表的ck_cj约束*/
alter table xscj
drop constraint ck_cj
/*定义一个规则，用限制数据输入模式，比如kcxx表的课程编号列，如果规定课程编号的第一位代表开课学期(只能输入1~6),后 2 位代表课程编号（只能输入0~9，并绑定到kcxx表的课程编号*/
use xs
go
create rule f_rule as @range like '[1-6][0-9][0-9]'
go
use xs
exec sp_bindrule 'f_rule','kcxx.课程编号'
go
/*定义一个用户数据类型，然后把规则"f_rule"绑定到用户数据类型的chourse_num上，最后创建kcxx1表,课程编号数据类型为chourse_num*/
use xs
go
exec sp_addtype 'course_num','char(3)','NOT NUll'
exec sp_bindrule 'f_rule','chourse_num'
go
create table kcxx1
(
课程编号 course_num,
课程名称 char(16) not null,
学分 tinyint
)
go
/*解除课程编号列与规则f_rule之间的绑定关系*/
use xs
if exists(
select name
from sysobjects
where name='f_rule' and type='R'
)
begin
exec sp_unbindrule 'kcxx.课程编号'
end
go/*定义用户数据类型 sex*/
use xs
exec sp_addtype sex,'char(2)','null'
go
/*定义默认值对象 xb_default*/
create default xb_default as '男'
go
/*将默认值对象 xb_default 绑定到 sex 数据类型*/
use xs
exec sp_bindefault 'xb_default','sex'
go/*解除默认值对象 xb_default 与用户定义数据类型 sex 的绑定关系，然后删除 xb_default 的默认值对象*/
use xs
if exists(
select name
from sysobjects
where name='xb_default' and type='D'
)
begin
exec sp_unbindefault 'sex'
drop default xb_default
end
go/*创建表BOOK表中包括书名、书号、类型、价格，并创建字段定义名为 min_time 的check约束，使 入库时间在2000-1-1之后*/
use xs
exec sp_addtype min_time,'datetime','null'
go
create table book
(
书名 char(10) not null,
书号 char(6) not null,
类型 char(10) not null,
价格 money,
入库时间 datetime constraint min_time check(入库时间>2000-1-1)
)
/*创建一个名为 num_rule 的规则，并绑定到book表的书号字段中,以限制书号字段由6位字符组成,前两位为大写A~Z之间的字母组成,后四位由0~9之间的数字组成*/
/*方法一*/
use xs
go
create rule num_rule as @range like '[a-zA-Z][a-zA-Z][0-9][0-9][0-9][0-9]'
go
use xs
exec sp_bindrule 'num_rule','book.书号'
go
/*方法二*/
use xs
go
create rule num_rule as @range like 'AZ[0-9][0-9][0-9][0-9]'
go
use xs
exec sp_bindrule 'num_rule','book.书号'
go
/*删除第一个和第二个的约束和规则*/
use xs
if exists(
select name
from sysobjects
where name='num_rule' and type='R'
)
begin
exec sp_unbindrule 'book.书号'
drop rule num_rule
end
go/*创建表xsda2,对学号创建PRIMARY KEY约束*/
use xs
go
create table xsda2
(
学号 char(6) not null constraint xh_pk primary key,
性别 char(2) not null,
系名 char(10) not null,
出生日期 smalldatetime not null,
民族 char(4) not null
)
go
/*给kcxx表的课程编号创建PRIMARY KEY约束*/
use xs
go
alter table kcxx
add constraint kcbg_pk primary key clustered (课程编号)
go
/*给book表的书名创建UNIQUE 约束*/
use xs
go
alter table book
add constraint book_uk unique nonclustered (书名)
go
/*删除KCXX的kcbg_pk键*/
use xs
go
alter table kcxx
drop constraint kcbg_pk
go
/*创建xsda3表，对身份证号码UNIQUE约束*/
use xs
go
create table xsda3
(
学号 char(6) not null constraint xh1_pk primary key,
姓名 char(5) not null,
性别 char(2) not null,
身份证号码 char(18) constraint sfzhm_uk unique,
系名 char(10) not null,
出生日期 smalldatetime not null,
民族 char(4) not null
)
go/*定义主键*/
use xs
create table xsda4
(
学号 char(6) not null constraint xh_pk primary key,
姓名 char (8) not null,
性别 char(2) not null,
系名 char(10) not null,
出生日期 smalldatetime not null,
民族 char(4) not null,
总学分 tinyint null,
备注 text null,
)
go
/*定义外键*/
use xs
create table xscj4
(
学号 char(6) not null foreign key references xsda4(学号) on update cascade,
课程编号 char(3) not null,
成绩 tinyint
)
go/*给book的类型设置一个默认值约束，默认值约束名为booktype，默认值为NEW BOOK*/
use xs
alter table book
add constraint booktype default 'NEW BOOK' for 类型
go/*删除book表中的约束*/
use xs
alter table book
drop constraint book_pk
go
use xs
alter table book
drop constraint book_uk
go
use xs
alter table book
drop constraint booktype
go/*定义用户数据类型 today : smalldatetime , not null ，定义默认值对象 day,取值为getdate()，将默认值对象day绑定到用户定义数据类型today上*/
use xs
exec sp_addtype today,'smalldatetime','not null'
go
create default day as 'getdate()'
go
use xs
exec sp_bindefault 'day','today'
go-----------------T-SQL语言编程-学习记录-----------------/*
*declare @test varchar(10) //定义 变量 数据类型
*@@test //全局变量
*@text //局部变量
*set @test = '测试' //赋值
*select @变量 //输出
*/
/*创建3个局部变量，并输出*/
declare @test varchar(10),@test2 varchar(10),@test3 varchar(12)
set @test='这是'
set @test2='数据库'
set @test3='serever 2012'
select @test+'一个'+@test2+'版本为'+@test3
/*创建一个名为xm的局部变量，并在select中使用该变量查找表xsda中的所有管理系学生姓名、总学分*/
use xs
declare @xm char(10)
set @xm='管理'
select 姓名,总学分
from xsda
where 系名=@xm
go
/*判断成绩，小于0错误,0-59不合格,60-80合格,80-90良好,90-100优秀*/
declare @t1 char(3),@t2 varchar(10),@t3 varchar(10),@t4 varchar(10),@t5 varchar(10),@t6 varchar(10)
select @t1=59,@t2='合格',@t3='不及格',@t4='良好',@t5='优秀',@t6='错误'
if @t1<0
print @t6
else if @t1>=0 and @t1<=59
print @t3
else if @t1>=60 and @t1<=80
print @t2
else if @t1>=80 and @t1<=90
print @t4
else if @t1>=90 and @t1<=100
print @t5
else if @t1>100
/*判断姓名为王红的成绩，大于90为平均成绩优秀,小于90为平均成绩非优秀*/
begin
declare @t1 int,@t2 varchar(20),@t3 varchar(20)
select @t1=(select avg(成绩) from xsda inner join xscj on xsda.学号=xscj.学号 where xsda.姓名='王红'),@t2='平均成绩优秀',@t3='平均成绩非优秀'
if (select avg(成绩) from xsda inner join xscj on xsda.学号=xscj.学号 where xsda.姓名='王红')>90
print @t2
else
print @t3
end
print @t6
/*编写循环语句1+2+3*/
declare @i int,@t int
set @t=0
set @i=1
while @i<=100
begin
set @t=@t+@i
set @i=@i+1
print str(@t)
end
print '100!='+str(@t)--创建一个内联表值函数CourseScore，功能是根据课程编号列出某门课程的学生成绩，包括课程编号、学生编号、课程成绩
create function CourseScore(@num char(6))
returns table
as
return(
select 课程编号,学号,成绩
from xs.dbo.xscj
where 课程编号=@num
)
--表值函数调用
select *
from CourseScore(104)
go
--在xs数据库中，创建一个标量值函数：MaxCourseScore，功能是求出某门课程的最高分。
create function MaxCourseScore(@num char(6))
returns int
as
begin
declare @aver int
select @aver=max(成绩)
from xs.dbo.xscj
where 课程编号=@num
return @aver
end
go
--标量值函数调用
select dbo.MaxCourseScore(104)
--创建一个内联表值函数MaxScore，功能是根据课程编号列出课程名称和该课程的最高分。
create function MaxScore(@num char(6))
returns table
as
return(
select xscj.课程编号,课程名称,max(成绩) as '最高分'
from xscj join kcxx on xscj.课程编号=kcxx.课程编号
where xscj.课程编号=@num
group by xscj.课程编号,课程名称
)
--表值函数调用
select *
from MaxScore(104)
go
数据库文件：点击下载