创建Student表:
create table Student(
Sno char(7) not null primary key,
Sname char(6) ,
Ssex char(2) ,
Sage int,
Sdept char(10)
)
创建Course表:
create table Course(
Cno char(4) not null primary key,
Cname char(20) ,
Credit int ,
Semester int
)
创建Sc表:
create table Sc(
Sno char(7)not null,
Cno char(4)not null,
Grade int ,
primary key(Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key (Cno) references Course(Cno)
)
使用SCS3数据库:
use SCS3
查询全体学生的学号和姓名:
select Sno, Sname from Student
查询全体学生的姓名、学号和所在系:
select Sname, Sno, Sdept from Student
查询全部列:
select *from Student
增加计算列 查询全体学生的姓名及出生年份 例:
select
Sname ,2015-Sage
from
Student
增加常量列 例1:
select
Sname ,'出生年份', 2015-Sage
from
Student
例2:
select
Sname as 姓名 , '出生年份' as 常量列, 2015-Sage as 年份
from
Student
在选课表中查询那些学生选修了课程,列出选课学生的学号:
select
Sno
from
Sc
distinct 去掉重复列
如下 学号不重复:
select
distinct Sno
from
Sc
例1查询计算机系全体学生的姓名:
select
Sname
from
Student
where
Sdept = '计算机系'
例2 查询年纪在岁以下的学生的姓名和年纪:
select
Sname, Sage
from
Student
where
Sage <20
例3 查询考试成绩不及格的学生的学号:
select
distinct Sno
from
Sc
where
Grade <60
使用BETWEEN ... AND
和NOT BETWEEN ... AND
来确定范围
查询年纪在~30岁之间的学生姓名、所在系和年纪:
方法1:
select
Sname, Sdept,Sage
from
Student
where
Sage between 20 and 30
方法2:
select
Sname,Sdept, Sage
from
Student
where
Sage >=20 and Sage <=30
查询年纪不在~30 之间的学生姓名、所在系和年纪:
方法1:
select
Sname,Sdept,Sage
from
Student
where
Sage not between 20 and 30
方法2:
select
Sname,Sdept,Sage
from
Student
where
Sage <20 or Sage >30
确定集合使用IN运算符
查询”信息系“、”数学系“、”计算机系“的学生的姓名和性别
方法1:
select
Sname,Ssex
from
Student
where
Sdept in('信息系', '数学系', '计算机系')
方法2:
select
Sname, Ssex
from
Student
where
Sdept='信息系'or Sdept='数学系'or Sdept ='计算机系'
查询既不是”信息系“、”数学系“,也不是”计算机系“的学生的姓名和性别
方法1:
select
Sname, Ssex
from
Student
where
Sdept not in('信息系', '数学系', '计算机系')
方法2:
select
Sname, Ssex
from
Student
where
Sdept!='信息系' and Sdept !='数学系'and Sdept !='计算机系'
字符串匹配 LINK 运算符
列名[NOT] LINK <匹配串>
例查询姓’张’的学生的详细信息:
select *from
Student
where
Sname like '张%'
查询姓”张“或”李“或”刘“的学生的详细信息:
方法1:
select
*
from
Student
where
Sname like '[张李刘]%'
方法2:
select
*
from
Student
where
Sname like '张%' or Sname like '李%' or Sname like '刘%'
查询名字中第二个字为”小“或”大“的学生的姓名和学号:
select
Sname, Sno
from
Student
where
Sname like '_[大小]%'
查询所有不姓王也不姓张的学生姓名:
方法1:
select
Sname
from
Student
where
Sname not like '[王张]%'
方法2:
select
Sname
from
Student
where
Sname like '[^王张]%'
方法3:
select
Sname
from
Student
where
Sname not like '王%'and Sname not like '张%'
查询姓”王“且名是一个字的学生的姓名
select
Sname
from
Student
where
Sname like ' 王_'
查询姓”王“且名是一个字的学生的姓名,下述查询结果也会包含名为两个字的学生姓名
select
Sname
from
Student
where
Sname like ' 王__' --未去空格
使用去掉尾随空格的函数:rtrim
使用格式:RTRIM (列名)
select
Sname
from
Student
where
rtrim(Sname) like '王__' --去空格
查询没有考试成绩的学生的学号和课程号:
select
Sno, Cno
from
Sc
where
Grade is null
查询所有考试成绩的学生的学号和课程号:
select
Sno, Cno
from
Sc
where
Grade is not null
多重查询where 字句
中可以使用逻辑运算符AND
和OR
来组成多条查询
例1 查询计算机系年龄在岁以下的学生的姓名和年龄:
select
Sname, Sage
from
Student
where
Sdept = '计算机系'and Sage <20
例2 查询计算机系和信息系年龄大于等于岁的学生姓名、所在系和年龄:
select
Sname, Sdept, Sage
from
Student
where
(Sdept = '计算机系' or Sdept = '信息系')and Sage >=20
对查询结果进行排序
ORDER BY <列名> [ASC/DESC] 默认排列方式为升序排序ASC
例1 将学生按年龄的升序排序
select
*
from
Student
order by
(Sage) ASC
例2 查询选修了“c002”号课程的学生的学号及其成绩,查询结果按成绩降序排列
select
Sno, Grade
from
Sc
where
Cno ='C002'
order by
(Grade)DESC
查询全体学生的信息,查询结果按所在系的系名升序排列,同一系学生按年龄降序排列
select
*
from
Student
order by Sdept,Sage ASC
--使用聚合函数汇总数据
--统计学生的总人数
select
COUNT(*) as学生人数
from
Student
统计选修课程的学生人数
select
COUNT(distinct Sno) --去掉重复学号
from
Sc
-计算“”学生的选修门数和考试总成绩
select
COUNT(*) as 选修门数, SUM(Grade) as 总成绩
from
Sc
where
Sno= '1512101'
计算“c001”课程的考试平均成绩
select
AVG(Grade)
from
Sc
where
Cno = 'C001'
查询“c001”课程的考试最高分和最低分
select
MAX(Grade) as 最高分 , MIN(Grade) as 最低分
from
Sc
where
Cno = 'c001'
查询“”学生的选课门数、已考试课程门数以及考试最高分、最低分和平均分
select
COUNT(*)as 选课门数, COUNT(Grade)as 考试门数,
MAX(Grade) as 最高分, MIN(Grade)as 最低分 ,
AVG(Grade)as 平均分
from
Sc
where
Sno = '1512101'
group by 分组
统计每门课程的选课人数,列出课程号和选课人数
select
Cno as 课程号, COUNT(Sno) as 选课人数
from
Sc
group by(Cno)
统计每个学生的选课门数和平均成绩
select
Sno as 学号,COUNT(*) as 选课门数,AVG(Grade) as 平均成绩
from
Sc
group by Sno
统计每个系学生的人数和平均成绩
select
COUNT(*) as 学生人数, AVG(Sage)as 平均年龄
from
Student
group by Sdept
带where字句的分组
。统计每个系的女生人数
select
Sdept ,COUNT(*) as 女生人数
from
Student
where Ssex = '女'
group by Sdept
统计每个系男生人数和女生人数,以及男生的最大年龄和女生的最大年龄
select
Sdept,Ssex,COUNT(*) as 人数,MAX(Sage)
from
Student
group by Sdept, Ssex
使用having 语句
查询选修了门以上课程的学生的学号和选课门数
select
Sno ,COUNT(*) 选课门数
from
Sc
group by Sno
having COUNT(*)>3
查询考试平均成绩超过的学生的学号、选课门数和平均成绩
select
Sno, COUNT(*)as 选课门数,AVG (Grade)as平均成绩
from
Sc
group by Sno
having AVG(grade)>= 80
统计每个系的男生人数,只列出男生人数大于等于的系
select
Sdept,COUNT(*)as 男生人数
from
Student
where Ssex = '男'
group by Sdept
having Count(*) >= 2
查询计算机系和信息管理系的学生的学生人数,语句有两种写法
方法1:
select
Sdept,COUNT(*)
from
Student
group by Sdept
having Sdept in('计算机系','信息管理系')
方法2:
select
Sdept , COUNT(*)
from
Student
where Sdept in('计算机系','信息管理系')
group by Sdept
多表连接查询
内连接
例查询每个学生及其选课的详细信息
select * from
Student join Sc on Student.Sno = Sc.Sno --将Student 与Sc 连接起来
去掉上述的重复列
select
Student.Sno, Sname,Ssex,Sage,Sdept,Cno,Grade
from Student join Sc on Student.Sno = Sc.Sno;
例查询计算机系学生的修课情况,要求列出学生的学号、姓名、所选的课程号和成绩
select
Student.Sno, Sname, Cno, Grade
from
Student join Sc on Student.Sno = Sc.Sno
查询计算机系学生的修课情况,要求列出学生的学号、名字、所选课程号和成绩
select
Student.Sno ,Sname, Cno, Grade
from
Student join Sc on Sc.Sno = Sc.Sno
where
Sdept = '计算机系'
为表取别名
为表指定了别名时,所有用到表的地方都要使用别名
select
S.Sno ,Sname, Cno, Grade
from
Student S join Sc on S.Sno = Sc.Sno
where
Sdept = '计算机系'
查询"信息系"选修了"计算机文化学"课程的学生的成绩,要求列出学生的姓名、课程名和成绩
--每连接一张表,就需要加一个join子句
select
Sname, Cname, Grade
from
Student S join Sc on S.Sno = Sc.Sno
join Course C on C.Cno = Sc.Cno
where
Sdept = '计算机系' and Cname = '计算机文化学'
查询所有选修了Java课程的学生情况,列出学生姓名和所在系
select
Sname, Sdept
from
Student S join Sc on S.Sno = Sc.Sno
join Course C on C.Cno = Sc.Cno
where
Cname = 'Java'
用有分组的多表连接查询
,统计每个系的学生的考试平均成绩p64
select Sdept ,AVG(Grade) as 平均成绩
from Student s join Sc on s.Sno = Sc.Sno
group by Sdept
有分组和行选择条件的多表连接查询
统计计算机系每门课程的选课人数、平均成绩、最高成绩和最低成绩
select
Cno, COUNT(*) as 选课人数,AVG(Grade) 平均成绩,
MAX(Grade)as 最高成绩, MIN(Grade)as 最低成绩
from
Student s join Sc on s.Sno = Sc.Sno
where Sdept = '计算机系'
group by Cno
自连接 相互连接的表在物理上为同一张表,但在逻辑上看成两张表
自连接一定要为表取别名
-查询与刘晨在同一个系学习的学生的姓名和所在系
select
S2.Sname, S2.Sdept
from
Student s1 join Student s2 on s1.Sdept = s2.Sdept
where
s1.Sname = '刘晨'and s2.Sname !='刘晨'
查询与"数据结构"学分相同的课程的课程名和学分
select
c2.Cname, c2.Credit
from
Course c1 join Course c2 on c1.Credit = c2.Credit
where
c1.Cname = '数据结构'
外连接 只限制一张表中的数据必须满足连接条件,而另一张表中的数据可以不满足连接条件
查询学生的选课情况,包括选了课程的学生和没有选课程的学生
左外连接:
select
Student.Sno ,Sname, Cno ,Grade
from
Student Left outer join Sc on Student.Sno = Sc.Sno
此查询也可以用右外连接
实现
select
Student.Sno, Sname, Cno ,Grade
from
Sc right outer join Student on Student.Sno = Sc.Sno
查询哪些课程没有人选。列出其课程名
select Cname from Course C left join Sc on C.Cno = Sc.Cno
where Sc.Cno is null --或Sno is null
使用TOP 限制结果集
查询年级最大的三个学生的姓名、年龄及所在系
select Top 3 Sname, Sage, Sdept
from Student
order by Sage DESC
若要包括年龄并列第三名所有的学生
select TOP 3 with ties Sname, Sage, Sdept
from Student
order by Sage DESC
查询Java课程考试成绩前三名的学生的姓名、所在系和成绩
select top 3 with ties Sname,Sdept,Grade
from
Student S join Sc on S.Sno = Sc.Sno
join Course C on C.Cno = Sc.Cno
where Sdept = 'Java'
order by Grade DESC
子查询
查询与’刘晨’在同一个系学习的学生
方法1:
select Sno, Sname, Sdept from Student
where Sdept in (
select Sdept from Student where Sname = '刘晨') --(包括刘晨)
方法2:
使用表的链接来查询
select S1.Sno, S1.Sname, S1.Sdept
from
Student S1 join Student S2 on S1.Sdept =S2.Sdept
where s2.Sname = '刘晨'
方法3:
select s1.Sno,S1.Sname ,S1.Sdept
from Student s1,Student S2
where S2.Sname = '刘晨'and s1.Sdept = S2.Sdept
不包括刘晨查询方法
方法1:
select Sno, Sname, Sdept from Student
where Sdept in (
select Sdept from Student where Sname = '刘晨')
and Sname !='刘晨'
方法2:
表的链接来查询
select S1.Sno, S1.Sname, S1.Sdept
from
Student S1 join Student S2 on S1.Sdept =S2.Sdept
where s2.Sname = '刘晨' and s2.Sname != '刘晨'
方法3:
select s1.Sno,S1.Sname ,S1.Sdept
from Student s1,Student S2
where S2.Sname = '刘晨'and s2.Sname != '刘晨' and s1.Sdept = S2.Sdept
查询考试成绩大于分的学生的学号和姓名
方法1:子查询
select Sno,Sname
from Student
where Sno in(
select Sno from Sc where Grade > 90)
方法2:表的连接
select S.Sno ,Sname
from Student S join Sc on S.Sno = Sc.Sno
where Grade > 90
方法3:
select Student.Sno,Sname
from Student,Sc
where Student.Sno = Sc.Sno and Grade > 90
子查询的多表连接
查询选修了Java课程的学生的学号和姓名p70
方法1:子查询
select Sno, Sname from Student
where Sno in (
select Sno from Sc
where Cno in (
select Cno from Course where Cname ='Java'))
方法2:多表连接来查询
select Sc.Sno, Sname
from
Student join Sc on Student.Sno = Sc.Sno
join Course on Course.Cno = Sc.Cno
where Cname ='Java'
例统计选修了Java 课程的学生的选课门数和平均成绩
select
Sno 学号,COUNT (*) as 选课门数, AVG(Grade)as 平均成绩
from Sc where Sno in (
select Sno from Sc join Course
on Sc.Cno = Course.Cno
where Cname ='Java')
group by Sno
嵌套的子查询
select
Sno 学号,COUNT (*) as 选课门数, AVG(Grade)as 平均成绩
from Sc
where Sno in (
select Sno from Sc
where Cno in (
select Cno from Course where Cname = 'Java'))
group by Sno
使用子查询进行比较测试
where表达式 比较运算符 (子查询)
例查询选了"c005"课程且成绩高于此课程的平均成绩的学生的学号和成绩
计算c005号课程的平均成绩
select AVG(Grade)
from Sc
where Cno = 'c005' --结果为
查询c005号课程所有的考试成绩中,高于分的学生的学号和成绩
select Sno,Grade
from Sc
where Cno = 'c005' and Grade > 73
将上述两个查询语句合起来
select Sno,Grade
from Sc
where Cno = 'c005' and Grade > (
select AVG(Grade)
from Sc
where Cno = 'c005' )
例查询计算机系年龄最大的学生的姓名和年龄
select Sname, Sage
from Student
where Sdept = '计算机系' and Sage = (
select MAX(Sage) from Student where Sdept = '计算机系')
例查询Java考试成绩高于Java平均成绩的学生的姓名、所有系和Java成绩p72
select Sname, Sdept, Grade
from Student join Sc on Student.Sno = Sc.Sno
join Course on Course.Cno = Sc.Cno
where Cname = 'Java' and Grade > (
select AVG(Grade) from Sc join Course on Sc.Cno = Course.Cno
where Cname = 'Java' )
使用子查询进行存在性测试
where [NOT] exists(子查询)
查询选修了"c001"号课程的学生姓名
带有exists谓词的查询是先执行外层查询,然后再执行内层查询
select Sname
from Student
where exists (
select* from Sc
where Sno = Student.Sno
and Cno = 'c001')
上述的查询等价于以下语句
select Sname from Student join Sc
on Sc.Sno = Student.Sno where Cno = 'c001'
或
select Sname from Student
where Sno in (
select Sno from Sc where Cno = 'c001')
查询没选c001号课程的学生的姓名和所在系
方法1:普通查询 (结果是错误的)
select distinct Sname, Sdept
from Student,Sc
where Student.Sno = Sc.Sno and Cno !='c001'
方法2:多表连接实现(结果是错误的)
select distinct Sname,Sdept
from Student join Sc on Student.Sno = Sc.Sno
where Cno !='c001'
用嵌套子查询实现
方法1:在子查询中否定 (错误的)
select Sname,Sdept
from Student
where Sno in (
select Sno from Sc where Cno !='c001')
2、在外层查询中否定 问题:结果和其他的不一样具体原因如p75
select Sname,Sdept
from Student
where Sno NOT IN (select Sno from Sc where Cno ='c001')
用相关子查询实现
1、在子查询中否定 (错误的)
select Sname, Sdept
from Student
where exists (
select * from Sc
where Sno = Student.Sno and Cno !='c001')
2、在外层查询中否定 问题:结果和其他的不一样 具体原因如p75
select Sname, Sdept
from Student
where not exists (
select * from Sc
where Sno = Student.Sno and Cno ='c001')
注意:否定放置在外层查询中时,其结果是正确的
例查询计算机系没有选修Java课程的学生的姓名和性别
方法1: (结果错误的)
select distinct Sname,Ssex,Sdept
from Student join Sc on Student.Sno = Sc.Sno
join Course on Course.Cno = Sc.Cno
where Sdept = '计算机系'and Cname !='Java'
方法2: (结果错误的)
select distinct Sname, Ssex,Sdept
from Student,Sc,Course
where Student.Sno = Sc.Sno and Sc.Cno = Course.Cno
and Sdept = '计算机系'and Cname !='Java'
方法3: (结果错误的)
select Sname,Ssex,Sdept
from Student
where Sno in (
select Sno from Sc where Cno in(
select Cno from Course where Cname !='Java'))
and Sdept = '计算机系'
方法4: (结果错误的)
select Sname,Ssex,Sdept
from Student
where Sno in (
select Sno from Sc join Course on Sc.Cno = Course.Cno
where Cname!= 'Java')
and Sdept = '计算机系'
以上几种结果是错误的 否定放置在外层查询中时,其结果是正确的
, 如下:
select Sname, Ssex,Sdept p75
from Student
where Sno not in (
select Sno from Sc join Course on Sc.Cno = Course.Cno
where Cname = 'Java')
and Sdept = '计算机系'
数据更改
插入数据 INSERT 语句的格式:
INSERT [INTO] <表名> [(<列名表> [,..., ... , n])] VALUES (值列表)
例将新生记录(1521104,陈东,男,岁,信息系) 插入Student表中 p75
insert into Student values('1521104', '陈东','男',18,'信息系')
select * from Student
给Sc表中的成绩设置默认值为null
(alter 用来修改表中列的,可以删除/添加列,也可以为列添加约束,我这里好像没详细写,约束之类均没有详细写,重点说的是查询、修改、删除、更新等)
alter table Sc
add default null for Grade
-例子在Sc表中插入一条新记录,学号为"1521104",选的课程号为"c001",成绩暂缺
因为提供的值的个数与表中的列的个数不一致,因此在插入语句中必须列出列名
insert Sc(Sno ,Cno ) values ('1521104','c001')
相当于
insert Sc values ('1521104','c001',null)
更新数据
Update <表名> set <列名> = 表达式[,...,...n]
[where 更新条件]
-例将所有学生的年龄加
update Student set Sage = Sage +1
将"1512101"学生的年龄改为岁
update Student set Sage = 21
where Sno = '1521101'
将计算机系全体学生成绩加分
方法1:子查询语句实现
update Sc set Grade = Grade+5
where Sno in (
select Sno from Student where Sdept = '计算机系' )
方法2:用多表连接实现语句
update Sc set Grade = Grade+5
from Student join Sc on Student.Sno = Sc.Sno
where Sdept = '计算机系'
删除数据
DELETE 语句的格式: DELETE [from] <表名> {WHERE删除条件}
无条件删除
例删除所有学生的选课记录
delete from Sc
有条件的删除
删除所有不及格学生的选课记录
delete from Sc
where Grade < 60
例子删除计算机系不及格学生的选课情况
方法1:用子查询实现
delete from Sc
where Grade < 60 and Sno in (
select Sno from Student where Sdept = '计算机系'
方法2:用多表连接来实现语句
delete from Sc
from Sc join Student on Sc.Sno = Student.Sno
where Sdept = '计算机系'and Grade < 60
数据查询扩展
将查询结果保存到新表中
使用select查询数据时,要将结果保存到一个表中,可以通过select语句和into子句实现
select 查询列表序列 into <新表名>
from 数据源
-- ...... 其他行选择、分组等语句
例查询计算机系学生的姓名、选修的课程名和成绩,并将查询结果保存到永久表S_C_G中语句
select Sname, Cname, Grade into S_C_G
from Student join Sc on Student.Sno = Sc.Sno
join Course on Course.Cno = Sc.Cno
where Sdept = '计算机系'
-例子统计每个系的学生人数,并将结果保存到永久表dept_cnt中
b必须为COUNT(*) 列取别名,否则无法创建新表
select Sdept, COUNT(*) as 学生人数 into dept_cnt
from Student
group by Sdept
CASE表达式
例查询选了Java课程的学生的学号、姓名、所在系和成绩,并对所在系进行如下处理:
当所在系为计算机系时,在查询结果中显示CS;当所在系为信息系时,在查询结果中显示IM
当所在系为数学系时,在查询结果中显示MA
select Sc.Sno 学号,Sname 姓名,
CASE Sdept
when '计算机系' then 'CS'
when '信息系' then 'IM'
when '数学系'then 'MA'
END as 所在系, Grade 成绩
from Student join Sc on Student.Sno = Sc.Sno
join Course on Course.Cno = Sc.Cno
where Cname = 'Java'
搜索CASE表达式
比如成绩在-90之间,则简单CASE表达式实现不了,这是就要使用搜索CASE表达式
用搜索CASE表达式的语句为
select Sc.Sno 学号,Sname 姓名,
CASE
when Sdept = '计算机系' then 'CS'
when Sdept = '信息系' then 'IM'
when Sdept = '数学系'then 'MA'
END as 所在系,Grade 成绩
from Student join Sc on Student.Sno = Sc.Sno
join Course on Course.Cno = Sc.Cno
where Cname = 'Java'
例查询"c001"课程的考试情况,列出学号、成绩以及成绩等级,对成绩等级的处理如下:
成绩大于等于为优,成绩-89之间为良,成绩-79之间为中,成绩在-69之间为及格,成绩小于为不及格
select Sc.Sno 学号, Grade 成绩,
CASE
when Grade >=90 then '优'
when Grade between 80 and 89 then '良'
when Grade between 70 and 79 then '中'
when Grade between 60 and 69 then '及格'
when Grade < 60 then '不及格'
END as 成绩等级
from Sc
where Cno = 'c001'
这里主要涉及查询、修改、删除、更新问题,并不具体阐述创建表、修改表字段、约束、存储过程、触发器、游标、视图、创建新的数据库用户、设置权限、Top、having,group by等等,想了解的可行自行网上寻找资源。
另外,这里的总结,是我学生时期做的笔记,有不足之处还望各位大佬予以指正!