SQL server 数据库基础

b站课程
https://b23.tv/q4pEmM 的课程笔记

创建数据库

数据库-右击-新建
新建查询 写查询语句

--创建学校数据库
create database school 
on  primary  -- 默认就属于primary文件组,可省略
(
 /*--数据文件的具体描述--*/
    name='School',  -- 主数据文件的逻辑名称,就是数据库的名
    filename='D:\stuDB_data.mdf', -- 主数据文件的物理名称,就是存到哪个目录下
    size=5mb, --主数据文件的初始大小
    maxsize=100mb, -- 主数据文件增长的最大值
    filegrowth=15%--主数据文件的增长率
)
log on
(
/*--日志文件的具体描述,各参数含义同上--*/
    name='stuDB_log',
    filename='D:\stuDB_log.ldf',
    size=2mb,
    filegrowth=1mb
)

--删除数据库
use master -- 设置当前数据库为master,所有的表文件名都存在sysdatabases表下
go--执行批处理
if exists(select * from sysdatabases where name=school)--查询sysdatabases表下有无这个数据库有则删除
drop database stuDB
go

右键 属性 可查看数据库 修改数据库大小
右键 重命名
右键 备份
分离数据库
数据库文件-右键-任务-分离
附加
数据库-附加-添加文件
还原
数据库文件-任务-还原-数据库

数据库备份，还原

数据类型

索引

创建
1.数据库文件-列-选择一个列-右键-索引
2.数据库文件-索引-新建索引
3.

create index ix_xs_date
on xs(出生日期)

修改
1.索引-索引文件-属性
2.

alter index all on rebuild--重建索引

约束

check约束
表文件-设计-选中一个列-check约束-添加-表达式
唯一性约束
表文件-设计-选中一个列-索引/键-添加-表达式
主键约束
表文件-设计-选中一个列-设置主键

查询

case

select xs.学号,姓名,课程名,成绩,
case--多项选择的开始   新建了一列
when 成绩>=90 then '优秀'
when 成绩>=80 and 成绩<=89 then '良好'
when 成绩>=70 and 成绩<=79 then '中点'
when 成绩>=60 and 成绩<=69 then '及格'
else '不及格'
end

from xs
--内连接
join xk on xs.学号=xk.学号
join kc on kc.课程号=xk.课程号
where 班级='14图形'

gruop by

select 课程号，avg(成绩) as 平均成绩
from xk
gruop by 课程号

having

select 班级.count(*) as 女生数
from xs
where 性别='女'
group by 班级
having count(*)>2

order by

select 学号,姓名,出生日期
from xs
where 班级='14计应'
order by 出生日期 desc

top

select top 5 学号,姓名,出生日期
from xs
where 班级='14计应'
order by 出生日期 desc

union all
合并查询结果

内联接

1
select xs.学号,姓名,班级,课程号,成绩
from xs inner join xk
on xs.学号=xk.学号
where 成绩<60
2
select xs.学号,姓名,班级,课程号,成绩
from xs,xk
where xs.学号=xk.学号 and 成绩<60

嵌套查询

总

select *
from sc,student
where student.son=sc.sno and sc.cno=course.cno grade <60 or grade is null

--逃逸字符
select * from course where cname like 'DB\_%' escape '\'

--聚类函数、排序、分组查询
select sno,max(grade),avg(grade),min(grade)
from sc
group by sno having count(*)>3
order by max(grade) desc

/*=====多表连接查询======*/
select *
from sc,student
where student.son=sc.sno and sc.cno=course.cno grade <60 or grade is null

--自身连接：查询没门课的直接先修课
select c1.cno,c1.cname,c2.cname
from course c1,course c2
where c1.cpno=c2.cno
--左外连接
select c1.cno,c1.cname,c2.cname
from course c1 left outer join course c2 on (c1.cpno=c2.cno)
--右外连接 right outer join
--全连接 full outer join
--自身连接：查询没门课的间接先修课课程号和名称
select c1.cno,c1.cname,c2.cname 直接先修课课程名,c3.cname as 间接先修课程名
from course c1,course c2,course c3
where c1.cpno=c2.cno and c2.cpno=c3.cno

sclect * from course

/*=======嵌套查询=======*/
--查询和‘张三’在同一个系的学生的个人信息
select *
from student
where sdept in (select sdept from student where sname='张三')

--查询每个学生超过他自己选修课程的平均分数的课程号（相关子查询）
select x.sno,x.cno,x.grade
from sc x
where grade >=(select avg(grade) 
               from sc y
		       where y.sno=x.sno
			   group by y.sno)
select * from sc

--基于派生表的查询
select 
from sc,(select sno,AVG(grade) from sc group bu sno) as avg_grade(sno,avggrade)
where sc.sno=avg_grade.sno and sc.grade>avggrade

/*========集合查询========*/
--查询即选修了数据库，又选修了‘数学’的学生的学号
select sno from sc.course where sc.cno=course.cno and cname='数据库'
intersect--减法 except 并 union
select sno from sc.course where sc.cno=course.cno and cname='数学'

--基于派生表的查询
select sno
from (select sno from sc.course where sc.cno=course.cno and cname='数据库') as sc1
     (select sno from sc.course where sc.cno=course.cno and cname='数学') as sc2
where sc1.sno=sc2.sno and sc1.sno=student.sno

--嵌套查询
select sno 
from sc
select sno in (select sno from sc.course
               where sc.cno=course.cno and cname='数据库')
			and sc.cno=course.cno and cname='数学'
		 and sc.sno=student.sno

--多表连接查询
select student.sno,sname
from student,course,sc x,sc y
where student.sno=x.sno and student.sno=y.sno
   and x.cno=(select cno from course where cname='数据库')
   and y.cno=(select cno from course where cname='数学'

应用

use 学生管理系统
go

sclect * from sc

insert into sc values('2020010101','10104',NULL)
insert into sc (cno,sno) values('10104','2020010101')

delete from sc where sno='2020010101' and  cno='10105'

insert into sc values('2020010101','10104',NULL),
                        ('2020010101','10105',NULL),
                        ('2020010101','10106',NULL)
select * from sc

insert into student (sno,sname ,sbirth)
values('2020010100','test','2020-01-01')
delete from student where sno='2020010100'
select * from student 

--插入子查询的结果
--创建一个新表，来记录目前学生一个选修了几门课，获得了几个学分
create table totalCredit
(snno char(10) primary key,
totalCourse tinyint,
totalCredit tinyint,
foreign key (sno) references student (sno)
     on delete no action on update cascade,
)

sclect * from StuCredit

--添加数据
insert into StuCredit
select sno.0.0
from student

sclect * from StuCredit

--更新数据
update StuCredit
set totalCourse=x.cnum
from (select sno.count(cno) as cnum from sc group by sno) as x
where StuCredit.sno=x.sno

update StuCredit
set totalCourse=y.cnum
from (select sno.sum(ccredit) as cnum 
         from sc,course
		 where sc.cno=course.cno and grade >=60
		 group by sno) as y
where StuCredit.sno=y.sno

select * from StuCredit
select * from sc,course where sc.cno=course.cno

--建立一个表，记录修改成绩时间的操作
create table update_info
(serino int primary key,
oldgrade int,
newgade int,
updatetime data
)

触发器

enable trigger 触发器名 on 表名 --启用触发器 
--禁用 disable
--管理器 触发器表右键

新建触发器

管理器方式
表-表名-触发器-右键新建触发器
代码

create trigger no_insert on xs
after insert
as
begin 
raiserror('xs表中不允许插入新的记录',1,1);--提示信息
rollback transaction
end

--eg
insert into xs values('14000001','男','1998-11-02','14信管'）

修改
右击触发器文件进行修改
删除
右击删除
drop trigger no_insert

存储过程

批量执行
建立
数据库名-可编程性-存储过程-右击新建

create procedure 存储过程名
	--@参数 参数类型
	@name char (8)--用户输入
as
begin
	--执行命令
	set nocount on
	select * from xs where '姓名'=@name
end
go

使用
exec/execute

execute 存储过程名称 参数

代码新建

create procedure querygrade
	--@参数 参数类型
	@s_grede char (10)--用户输入
	@grade_count int OUTPUT
as
select @grade_count=count(*) from xs where 班级=@s_grade

应用

declare @grade_count int;
declare @s_grade char(10)='14信管';
exec querydrade @s_grade,@grade_count output
select '该班级一共有'+ltrim(str(@grade_count))+'人'

修改
选择存储过程文件 右键修改 修改后执行
alter 需要写全部代码

视图

create view view_xs
as select 学号，姓名 from xs


go
select * from view_xs

创建
数据库-试图-右击新建
删除
drop view 视图名
修改
alter view view_xs
as select 学号，姓名，班级 from xs

选中试图-右击-设计

表

数据库-表-右击-新建表
删除
drop table dbo.表名

选中 右击 删除 查看依赖关系

--打开数据库
use 数据库名
--创建表
 create table StuCredit
(
sno char(10) primary key,
totalCourse tinyint,
totalCredit tinyint,
foreign key(sno) references Student(sno)
	on delete no action on update cascade
)


update StuCredit
set totalCourse = x.cnum
from (select sno,count(cno) as cnum from SC group by sno) as x
where StuCredit.sno=x.sno


update StuCredit
set totalCredit = x.sumcredit
from (select sno,sum(credit) as sumcredit
		from SC,Course 
		where SC.cno=Course.cno and grade>60
		group by sno) as x
where StuCredit.sno=x.sno


create table update_info
(
serino int primary key,
oldgrade tinyint,
newgrade tinyint,
updatetime date
)

修改
选中表 右击 设计 保存 刷新

alter table xsll
add '手机号' nchar(20) not null
--删除
alter table xsll
drop column 手机号