create database studentdb
use studentdb
go
create login zhou1 with password=‘mypassword123’
go
create user zhou1 for login zhou1
1.2 创建与删除
create schema sch1 authorization zhou1
drop schema sch1
数据库-数据表增删查改
Create,Alter,Drop,Add…
1.创建表的语法
Create Table Users
{
vUserName varchar(18) Not NULL,
vPassword varchar(20) Not NULL
}
2.学生表实例
2.1 创建带约束和主关键字的学生表
Create Table Student
{
Sno char(10) NOT NULL Constraint PK_stu_NO Primary Key,
Sname char(20) NOT NULL,
Ssex cahr(2) NOT NULL Constraint CK_Stu_Sex Check(Ssex in('男','女')),
Sage tinyint Constraint CK_Stu_Age Check(Sage between 1 and 80),
Tel char(15) NOT NULL
}
2.2 修改学生表
Alter Table Student Drop Constraint CK_Stu_Age
2.3 删除Sage列
Alter Table Student Drop Column Sage
2.4 添加列
Alter Table Student Add dBirth datetime
2.5 修改列字段大小
Alter Table Student Alter Column Smajor varchar(20)
2.6 删除先前的学生表
drop table student
5.24
数据库-单表查询
and,or,in,distinct,like,@,is null,is not null,where,GROUP BY,聚合函数MAX,MIN,AVG
1.基本概念
SELECT 表上哪些列显示
*表示所有列
//投影运算 指的是选择部分列数据
//友好列标题 SELECT Sno as 学号
//top关键字 SELECT Top 3 * from Student 只显示前3条数据
FROM 这个表来源
WHERE 这个表的情况,进行筛选
2.简单匹配查询
2.1 检索年龄为20岁的学生信息
select * from student where Sage=20
2.2 and
连接多个条件
select * from student where ssex='女'and sage>21
2.3 or
表示达到一个条件即可
between and 在什么条件之间, not between and 取反条件
select sno,sname,ssex from student whrer sno between '2' and '4'
2.4 in
检索一系列的取值列表
select * from teacher where tropt in('教授','副教授')
2.5 distinct
表示不检索出相同信息
select distinct tropt from teacher
2.6 like
模糊检索
select sname,sdept from student where sdept like'%学%'
2.7 @
一个字符串与一个下划线进行匹配
select sname,sdept from student where emall like '__@%'
2.8 is null
字段为空
select *from student where low is null
2.9 is not null
字段不为空
select *from student where high is not null
2.10 where
等于单个值 查询有某科目考试分数为48分的学生信息
select *from student where sno=(select sno from sc where grade=48)
3.分组聚合查询
2.1 GROUP BY
进行分组 按照by之后的选择进行分组统计
2.2 实例
对course表,按照必修和选修进行分类,统计每种类别的课程数量
select xklb as 类别,count(cname) as 数量 from course group by xklb
HAVING
ORDER BY ASC||DESC
ASC表示升序,DESC表示降序
select *from student order by sage desc
select *from student order by sage desc,sno asc在sage相同情况下按snow升序对比排列
COUNT
count函数返回匹配行数
select count(*) from teacher where tropt=‘教授’
2.3 聚合函数MAX,MIN,AVG
显示教师的最大最小平均年龄
select max(tage),min(tage),avg(tage) from teacher
指定条件求和
select sum(credit) from course where xklb='必修' 对course表中的必修课的学分进行求和
2.4 混合应用
select smajor,ssex,count(sno) from student group by Smajor ,sex order by count(sno) desc
对student表,按照专业和性别进行分组,显示每个专业,每种性别的学生数量,按照学生数量的降序显示结果
select tropt,count(tropt) from teacher group by tropt having count(tropt)>=5
对teacher表,显示职称和对应的认识,要求只有统计人数大于等于5人才显示
对sc和student表进行内连接,显示学生的学号,姓名,课程号,分数。
语法1:
select student.sno,student.sname,sc.sno,sc.grage from sc join student on sc.sno=student.sno
语法2:
select student.sno,student.sname,sc.sno,sc.grage from sc,student where sc.sno=student.sno
1.3 实例:三张表连接
显示学生的学号,姓名,课程名,考试分数
语法1:
select student.sno,student.sname,course.cname,sc.grage from sc join student on sc.sno=student.sno join course on sc.sno=course.sno
语法2:
select student.sno,student.sname,course.cname,sc.grage from sc,student,course where sc.sno=student.sno and sc.sno=course.sno
2.外连接
2.1 左外连接(left join)
让student表和sc表进行左外连接,即不管是学生是否有选修课程,该学生的信息的都会显示出来
select student.sno,student.sname,sc.sno,sc.grade from student left outer join sc on student.sno=sc.sno
2.2 右外连接(right join)
让sc表和teacher表进行右外连接,显示教师编号,教师姓名,讲师教授的课程号
select teacher.tno,teacher.tname,sc.cno from sc right outer join teacher on sc.tno=teachaer.tno
2.3 全外连接(full join)
让sc表和teacher表进行全外连接,显示教师编号,教师姓名,讲师教授的课程号。
teacher.tno,teacher.tname,sc.cno from sc full outer join teacher on sc.tno=teacher.tno
在教师列表中,检索比任何一个女教师年龄都大的男教师的信息
select *from teacher where tsex='男' and tage>all(select tage from teacher where tsex='女')子查询得到每一位女教师的年龄,外层查询使用“>all”的语法,即比集合中最大值还大
3.使用exists的子查询
使用exists关键字引入子查询后,子查询的作用就相当于进行存在测试
外部查询的where子句测试子查询返回的行是否存在
子查询实际上不产生任何数据,它只返回TRUE或flase值
3.1 exists
查询选修了B004课程的学生的基本信息
select *from student where exists (select *from sc where sno=student.sno and cno='B004')
3.2 not exists
查询没有选修X001课程的学生的基本信息
select *from student where not exists (select *from sc where sno=student.sno and cno='X001')
3.3 where exists
查询与王国在同一个专业学习的所有学生的基本信息
select sno,sname,smajor from student s1 where exists (select *from student s2 where s1.smajor=s2.smajor and s2.name='王国')
5.27
数据库-集合查询
并union 交intersect 差except
1.前提
select语句必须拥有相同数量的列
2.并运算
将学生的学号,姓名,与教师的教工号,姓名,在一个检索结果中显示出来
select Sno,Sname from Student
union
select Tno,Tname from Teacher
//union 将多个查询结果合并起来时系统自动去掉重复元组
//union all 将多个查询结果合并起来时,保留重复元组
3.交运算
对专业名以计算机开头的学生,及年龄为21的学生,用交运算求二者的交集
select Sno,Sname,Sage,Smajor from Student
where Smajor like'计算机%'
intersect
select Sno,Sname,Smajor from Student
where Sage=21
4.差运算
查询专业名以计算机开头的学生,但不包括年龄是21的学生
select Sno,Sname,Sage,Smajor from Student
where Smajor like '计算机%'
except
select Sno,Sname,Sage,Smajor from Student
where Sage=21
未发待续
5.28
数据库-数据更新
插入数据,更新数据,删除数据
1.插入数据
1.1 插入单行数据
在course中插入一行数据,四项数据为('X004','计算机前沿',2,'选修')
insert into Course(Cno,Cname,Ccredit,XKLB)
values('X004','计算机前沿',2,'选修')
1.2 插入子查询结果
将学生表中的学号,姓名,性别抽取出来,插入到Teacher表中,所有新插入的数据,职称为讲师
insert into Teacher(Tno,Tname,Tsex)
select Sno,Sname,Ssex,'讲师' from Studnet
1.3 查询创建新表
将teacher中职称为教授的信息,存入到一张目前还不存在的experts表
select * into experts from teacher
where Tprot='教授'
2.更新数据
2.1 修改特定行
将course表中编号是B002的课程,学分修改为3分
update course set ccredit=3
where cno='B002'
2.2 带子查询的修改
对学生表,将现有的专业字段,用来存放学生选修的第一门课程的编号
update student
set smajor
(select top 1 cno from sc where sc.sno=student.sno)
3.删除数据
3.1 删除满足条件的行
删除course表中编号为B009的记录
delete from course
where cno='B009'
3.2 带子查询的删除
对course表中,没有任何学生选修过的课程,执行删除操作
delete from course where cno not in
(select cno from sc)
5.29
数据库-视图
创建视图,更新视图,查询视图
1.创建视图
创建一个名为vwscs的视图,是将学生表中院系是计算机科学学院的学生的学号,姓名,性别,专业四个字段显示出来
create view vwscs
as
select sno,sname,ssex,tel,emall from student
where sdept='计算机科学学院'
2.更新视图
通过vwscs视图进行数据更新,将杨华的电话修改为13966667777
update vwscs
set tel='13966667777'
where sname='杨华'
3.查询视图
select *from vwscore where score grade<85
5.30
数据库-索引
主键索引,唯一索引unique,聚集索引clustered,非聚集索引nonclustered
1.索引分类
主键索引
唯一索引unique
聚集索引clustered
非聚集索引nonclustered
2.建立与删除索引
create index
drop index
3.聚集索引
alter table course2
add constraint pk_course2_cno primary key clustered(cno)
4.唯一索引
create unique index idxcoursename
on course2(name)
5.删除索引
drop index idxcoursename on course2
drop index course2.idxcoursename