SQL学习笔记

---恢复内容开始---

数据查询 select_SELECT

数据定义 create_CREATE  /  drop_DROP  /  alter_ALTER

数据操纵 insert_INSERT /  update_UODATE / delete_DELETE

数据控制 grant_GRANT / revoke_REVOKE

 

 

数据库脚本：

use jxgl;
select * from student;
select ssex, sdept, count(*) 
from student
group by ssex, sdept

 

select  sdept, count(*) 
from student
group by sdept
having count(*) >= 2

 

--查找平均年龄最小的学院

--查出来每个学院的平均年龄是多少
select  sdept, avg(sage)
from student
group by sdept

--在上面的结果中，查找最小的
select min(avg_age) from 
  ( select  sdept, avg(sage) avg_age
    from student
    group by sdept ) a
--在第一步中的结果中，用having 进行筛选
select  sdept from student
group by sdept
having avg(sage) = 
(
   select min(avg_age) from 
  ( select  sdept, avg(sage) avg_age
    from student
    group by sdept ) a
)

having是对你分组后的，聚集函数算出来的结果
进行一次筛选，查找我们感兴趣的数据

 

--查询每个学生及其选修课程的情况

--=确定需要查询的数据，是在那些表中的，
--学生信息是在student表中，选课是在sc

select * from student join sc on 
    student.sno = sc.sno
select * from student, sc
　　where student.sno = sc.sno

 

两张表进行连接后，其实就是生成了一个新的关系

这个新的关系再去和一张表连接，就是三表连接

--学生姓名、选课课程名和成绩
--姓名来自student, 课程名来自course, 成绩来自sc,三表连接

select *
 from student a join sc b on a.sno = b.sno
       join course c on b.cno = c.cno

----------------我是分割线------------------------------------------------------------------------------------
use wzjw

select top 10  * from xscjb
select top 10 * from xsb

--查询课程的直接先行课程的名字是什么

select * from course

只要根据cpno, 去course表中查对应的cpno的课程名，
就知道先行课程的名字了。

select a.cname, b.cname from course a join course b
   on a.cpno = b.cno

--查找学生表中同名的学生的姓名

select distinct a.sname from student a join student b
on a.sname = b.sname and a.sno != b.sno
或者：
select distinct sdept from student where sname in 
(
select sname 
from student 
group by sname
having count(*) > 1
)

 

--查看某个学生的成绩
select * from student  right join sc
on student.sno = sc.sno

select * from sc   full  join student 
on sc.sno = student.sno

--查询选了课程C2的学生的姓名

select sname from student a join sc b on a.sno = b.sno
where cno = 'C2'
select * from student where sno in
(select sno from sc where cno = 'C2')

select * from student where exists 
  ( select * from sc where 
                sc.sno = student.sno 
                     and sc.cno = 'c2')

--------------------我是分割线--------------------------------------------------------------------

 

insert into student
values (6, '王五', '女', 20, '经管')

 

select * from student
where sdept = (

      select sdept from student
       where sname = '张三'
  )

在SQl语句中，= 表示标量值相同，就是数值相同，
一个值，是不能用=去和一个集合的值去比较
张三有同名，返回了2个院系（计算机，外语），
对应于student每行中的sdept是一个值，没法和集合值去
比较，所以出错。

当我们不确定到底查询的结果返回的是一行，还是多行，
那么我们用in来表示，表示在这个集合中

 

select * from sc;
select * from student;
select * from course;

--查的信息是在几个表中的时候，那么必定需要表的连接
--查所有学生的学号、姓名、课程名称以及他的成绩

--首先分析，查询的字段来自哪些表
--学号、姓名来自student,
--课程名称来自 course
--成绩来自于sc表
--判断连接条件。因为sc表中只有学号，
--而student表中有学号、姓名，所以要查找学生的
--姓名，那么sc表和student表要在sno上进行连接

select * from (sc a join student b on a.sno = b.sno)

--可以想象，上面的两张表join后，形成了一个新的关系，表，
--其列分别来自于sc表和student表,这个新的关系可以
--去和别的表进行连接

select b.sno, sname, a.cno, c.cname, grade 
  from sc a right join  student b on a.sno = b.sno
  　　　　　left join course c on a.cno = c.cno              
             
select b.sno, sname, c.cno, grade 
  from student b left join sc a  on a.sno = b.sno
                left join course c on a.cno = c.cno
order by sno

--找到选择了课程号为c2的学生的详细信息
--确定表， c2是sc， 学生信息在student,

select * from sc a join student b
on a.sno = b.sno
where a.cno = 'c2'
   
select * from student where exists 
  ( select * from sc where 
                sc.sno = student.sno 
                     and sc.cno = 'c2') 

for (数组 in sstudent)
{
 if (select * from sc where
      sc.sno = 数组[学号']
      and sc.cno = 'c2')
   为真
   显示数组

}

--exists 表示存在，判断后面的括号里是否
--有值。如果有，为真，没有值，为假。
--有值，表示可能有1行，2行或者多行数据

相关子查询。因为子查询中， sc.sno=student.sno
中，用到了外面父查询表student的当前行的数据
student.sno.

1）要遍历student表中所有记录。
1.1首先是第一行记录，
    1 张三 男 19 计算机
所以，当前行student.sno = 1,然后把这个值，
带入到子查询中
 select * from sc where
                sc.sno = 1
                     and sc.cno = 'c2'
去执行这个子查询，结果为
1 C2 60
存在一行返回值，所以 exists (select * from sc where
                sc.sno = 1
                     and sc.cno = 'c2')  
为真，那么就把student的当前行，做为结果返回 

1.2测试下一行
2 李四 女 18 经管
student.sno = 2带入到子查询
 select * from sc where
                sc.sno = 2
                     and sc.cno = 'c2'  
有返回值 ，所以此行记录也显示

1.3再测试下面一行

3 王五 男 18 计算机      
 select * from sc where
                sc.sno = 3
                     and sc.cno = 'c2'
有返回值 ，所以此行记录也显示

1.4 再测试下一行
4 张三 女 18 外语
 select * from sc where
                sc.sno = 4
                     and sc.cno = 'c2'
此时，查询结果没有值。没有值意味着，
学号为4号的学生，没有学习了c2这门课。
exists为假， 此行不返会

1.5 5 李四 男 20 建筑
 select * from sc where
                sc.sno =5
                     and sc.cno = 'c2'
此时，查询结果没有值。没有值意味着，

6 王五 女 20 经管

 select * from sc where
                sc.sno =6
                     and sc.cno = 'c2'
此时，查询结果没有值。

 

查询所有选修了1号课程的学生姓名。

--涉及到2张表， student姓名， 选修 sc
select * from sc a join student b on a.sno = b.sno
where cno = 'c1'
select * from student where sno in (
    select sno from sc where cno = 'c1')
--我要到学生表中，找满足下面的条件的学生
--这些学生选修了1号课程。
select * from student 
   where exists
      ( select * from sc where sc.sno = student.sno
            and cno = 'c1')

 

找出每个学生超过他选修课程平均成绩的课程号

--查询只涉及到sc表
select sno, avg(grade) from sc
group by sno

select * from sc a
where grade > ( select avg(grade) from sc b 
                where a.sno =  b.sno)

--相关子查询
步骤1，取a表的第一条记录
1 C1 91，
所以a.sno = 1
带入到后面的select中
select avg(grade) from sc b
                where 1 =  b.sno
就是计算1号同学的平均成绩，计算结果为64，
此时， where 条件就是  grade > 64,
当前记录的grade为91， 所以满足条件，
显示出来。

第二步
1 C2 60
此时, a.sno =1
select avg(grade) from sc b
                where 1 =  b.sno
就是计算1号同学的平均成绩，计算结果为64，
此时,where grade > 64, 但是当前的grade为60，
不满足条件，因此不显示。

sc表中有多少行记录，求平均成绩就执行了多少次

select * from sc a join 
     (  select sno, avg(grade) avg_cj from 
       sc  group by sno) b
on a.grade > b.avg_cj and a.sno = b.sno

with avgCj as 
( select sno, avg(grade) avg_cj from 
       sc  group by sno
)
select * from sc join avgCj on sc.sno = avgCj.sno
         and sc.grade > avgCj.avg_cj

 

------------------------我是分割线-----------------------------------------------------------------

any all用法

select * from student
where sdept != '计算机'
   and sage <any(select sage from student where sdept 
                           = '计算机')
update student set sage = 16 where sno = 4
select * from student
where sdept != '计算机'
   and sage <all(select sage from student where sdept 
                           = '计算机')

 ------------------我是分割线--------------------------------------------

------------------以下是10.20内容---------------------------------------

 找到所有参加选课的学生的详细信息。

我们要找到这些学生，这些学生满足他要在选课表中出现。

select * from student 
where exists 
( select * from sc where sc.sno = student.sno)

 

--union集合交 union all 不去掉重复值

select * from student where sdept = '计算机'
union
select * from student where sage <=18

 

--消息 245，级别 16，状态 1，第 24 行
--在将 varchar 值 '经管' 转换成数据类型 smallint 时失败。

①
select sno, sname, sage from student where sdept = '计算机'
union
select sno, sname, sdept from student where sage <=18

②
select sno, sname, sage from student where sdept = '计算机'
union
select sno, sname, sno from student where sage <=18
--上面的例子中， sage是int, sno是varchar, 类型不同，
--但是在现有的数据下，能够执行。原因是，sql server
--试图去把union后面的查询的字段，转换为int,由于
--在我们的例子中， sno正好全是整数，转换成功。

update student set sno='S3' where sno = 3
--此时上面查询又出错。

--------------------------------------------------------------------------------------
SELECT Sno FROM SC WHERE Cno='C1 '
UNION
SELECT Sno FROM SC WHERE Cno= 'C2';

会访问sc表两次，第一次，找c1的学号，第二次，找c2的学号，
然后还要进行合并，效率很差

select distinct sno from sc where cno in ('c1','c2')
只需要访问sc表一次就可以了

--------------------------------------------------------------------------------------

intersect交集

select * from student where sdept = '计算机'
intersect
select * from student where sage < 19

select * from student where sdept = '计算机' and sage <19

 

--超难exists
这个查询，相当于就是计算关系代数 sc / course
相当于，sno和course的连接，需要包含在sc表中

--对于1号学生而言，他学的课程为
select cno from course
except
select cno from sc where sno = '1'

我用所有的课程，减去1号学生学的课程。如果是空集，
就说明，1号学生学习了所有的课程。如果不是空集，
比如剩下来c3这门课，这就说明，c3这门课，1号同学没有学。

--对于2号学生而言，他学的课程为

select cno from sc where sno = '2'
用所有的课程，减去其学过的课程，
select cno from course
except
select cno from sc where sno = '2'
结果就是，在所有课程中，他没有学过的课程，此时不为空，
显然，2号同学不能够列出来。

最终，就是要判断
select cno from course
except
select cno from sc where sno = 每个学生的学号

的结果集是否为空。如果为空，表示学习了所有的课程。

select * from student 
where not exists 
(
select cno from course
except
select cno from sc where sno = student.sno
)

 

--选修了所有课程的学生的名字 翻译为：

--没有一门课程，这个学生没有学过

select * from student 
where not exists 
　　( select * from course 
　　　　where not exists 
　　　　　　( select * from sc where 
　　　　　　　　sc.sno = student.sno 
　　　　　　　　and sc.cno = course.cno)
)

 

先看1号学生， 此时student.sno = 1
course表中，需要进行遍历，从c1到c4

1.1 course.cno = c1

select * from sc where sc.sno = 1
and sc.cno = 'c1'
查看1号同学，是否学过c1这门课

1.2 coure.cno = c2
select * from sc where sc.sno = 1
and sc.cno = 'c2'
查看1号同学，是否学过c2这门课

select * from course
where not exists
( select * from sc where
sc.sno = 1
and sc.cno = course.cno)

接下来，看2号同学，此时student.sno = 1
对于course 表， C1-C4一条条遍历

select * from sc where
sc.sno = 2
and sc.cno = 'C3'

select * from course
where not exists
( select * from sc where
sc.sno = 2
and sc.cno = course.cno)

依次遍历student表，对于student中的每行，
依次遍历course. 对于course表的每行，去测试
( select * from sc where
sc.sno = student.sno
and sc.cno = course.cno)条件

-------------------------------------------------------------------------

查询至少选修了学生2号同学选修的全部课程的学生号码。

包含2号学生的课程

确定2号同学学了那些课
select cno from sc where sno = 2

确定每个同学(比如1号同学)，学了哪些课程

select cno from sc where sno = 1

如果1号同学学了2号的所有课程，那么2号同学减去一号同学的
课程的结果集就应该为空
select cno from sc where sno = 2 --A集合
except
select cno from sc where sno = 1 --B集合

A集合减去B集合的运算时：在A集合中，去掉B集合中出现的值
A {C2, C3}, B ={ C1, C2, C3, C4}
A-B= 空集

测试是否为空集，就等价于 not exists (集合）

select * from student
where not exists
(
select cno from sc where sno = 2 --A集合
except
select cno from sc where sno = student.sno --B集合
)

select * from sc

select * from sc

insert into sc values ('2', 'C3', 80)

如果88号同学学了2号的所有课程，那么2号同学减去88号同学的
课程的结果集就应该为空
select cno from sc where sno = 2 --A集合
except
select cno from sc where sno = 88 --B集合

A集合减去B集合的运算时：在A集合中，去掉B集合中出现的值
A {C2, C3}, B ={ C4}
A-B= {C2, C3}

----------------------------------------------------------------------

SELECT Sno, Cno 
FROM SC,(SELECTSno, Avg(Grade) 
　　　　　 FROM SC
          GROUP BY Sno)
　　　　　　AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno = Avg_sc.avg_sno
and SC.Grade >=Avg_sc.avg_grade

 

---------------------------------------------------------------

with avg_sc as
(
select sno, avg(grade) avg_grade from sc 
group by sno
)
select * from sc join avg_sc on sc.sno = avg_sc.sno
and sc.Grade > avg_sc.avg_grade

 

任意一个查询的结果，都可以套上小括号后，加上
一个虚拟的名字，当成表来使用

SELECT Sno, Cno
FROM
( select * from sc where cno in ('c1','c2')) b
,
(SELECT Sno, Avg(Grade) FROM SC
GROUP BY Sno ) AS avg_sc(avg_sno,avg_grade)

WHERE b.Sno = Avg_sc.avg_sno
and b.Grade >=Avg_sc.avg_grade

select * from student
---------------------------------------------------------
insert into student(sno, sname,)
values('9', '999', '女', 20, '计算机')

SELECT [Sno]
,[Sname]
,[Ssex]
,[Sage]
,[Sdept]
FROM [dbo].[Student]
GO

--有值没有给，自动填写为null
------默认值或绑定
insert into course(Cno, Cname)
values ('C21', '马原')
select * from course where cno = 'c21'

create table aa
( a int default 2
);
--默认值为2

-----------------------------------------------------------

 

--查询结果放到表里

create table dept_age
(
sdept varchar(20) primary key,
age int 
)
select * from dept_age

--查询结果放到表里
insert into dept_age(sdept, age)
select sdept, avg(sage)
from student
group by sdept

 

--sql server 专用的
--数据库里创建s_a表

select sdept, avg(sage) age 
into s_a
from student
group by sdept

select s_a 

 

----------------------我是分割线-----------------------------------------------------------

----------------------以下是10.26内容-----------------------------------------------------

use jxgl;

select * from sc

--查找至少选修了2号同学学习的所有课程的学生的名单

B集合{1,2,3}是否包含了A{2,3}集合
只要看A-B是否是空集。对于Sql而言，
就是测试 not exists ( A except B)

在这个题目中， B集合是某个学生的学的课程
A集合是2号同学学的课程

select * from student where 
not exists (
select cno from sc where sno = '2'
except
select cno from sc where sno = student.sno
) and sno != '2'

---------------------------------------------------------------

select * from student

只能在sql server中用
select * into student_bk from student

就会把查询的结果建立一张叫做 student_bk的表
select * from student_bk

drop table student_bk;

 

--建立一张表叫student_bk, 结构和student表一样，但是里面没有数据。

select * into student_bk from student where 1=2

select * from student_bk

 

--用into建立的表，结构和数据类型一样，但是主键外键各种约束都是没有的

insert into student_bk
　　　　　　select * from student

select * from student_bk

我们再去执行一次插入，由于没有主键，允许插入

insert into student_bk
　　　　　　select * from student

select * from student_bk  

truncate table student_bk;
--删除所有数据

---------------------------------------------------------

插入数据
insert into student_bk
select * from student

要求，插入的时候，已经在student_bk中的学号的数据，
不要进行插入

因此，在插入数据的时候，要判断数据是否在student_bk

insert into student_bk
select * from student 
where not exists ( select * from student_bk 
where student.sno = student_bk.sno)

 

insert into student(sno, sname, Ssex, sage, Sdept)
values ('101' ,'我是101', '男',18, '外语')

 

-----------------------------------------------------------
update student set sage = 22, sdept = '计算机',
ssex = '女'
where sno = '101'

update student set sdept = '计算机科学与技术'
where sdept='计算机'

update student set sage = sage + 100

update student set sage = sage -100

-----------------------------------------------------------------
将计算机科学系全体学生的成绩置零。

成绩是在sc表中，但是计算机系这个列是在student表中
这两张表中，可以通过sno进行联系

update 后面一定是要实际修改数据的表。每次只能有一个表

update sc set grade = 0
where sno in ( select sno from student 
where sdept = '计算机科学与技术')

 

select * from sc

update sc set grade = 0 
where exists ( select * from student where 
student.sno = sc.sno and 
student.sdept = '计算机科学与技术'
)

 

update sc set grade = 0 
where exists ( select * from student b where 
b.sno = sc.sno and 
b.sdept = '计算机科学与技术'
)

--修改的表不能取别名

 

修改一个表的数据时，要根据其他表的数据来筛选条件的话，
肯定要用到子查询,.where 后面跟子查询

select * from student
--主码是可以修改的，但不可以重复值
update student set sno = '200' where sno = '2'

--不允许为空值的时候，不能设置成null
update student set sname = null where sno = '1'

select * from student

select * from sc

--把所有小于80分的成绩提高10%, 大于等于80分的，加1分。
begin tran
/*BEGIN TRAN标记事务开始
COMMIT TRAN 提交事务
一般把DML语句(select ,delete,update,insert语句)
放在BEGIN TRAN...COMMIT TRAN 之间作为一个事务处理
也就是那些语句执行过程中如果遇到错误，无论哪句遇到错误，
所有语句都会回滚到BEGIN TRAN之前的状态。*/

1)update sc set grade = grade + 1 where grade >=80;

2)update sc set grade = grade*1.1 where grade < 80;

一条update语句，是扫描全表，把符合条件的记录进行修改，
因此，用2条update语句做，要扫描2次，效率低了。

更关键的是，在执行完1后，如果用户修改了成绩怎么办？

能够用一条sql语句执行么？

case 语句

select * from student
--------------------------------------------------------
with xb as
( select '男' xb, 'male' sex
union
select '女' xb, 'female' sex
)
select * from student a join xb b on a.Ssex = b.xb
----------------------------------------------------------

select *, case 
when ssex = '男' then 'male' 
when ssex = '女' then 'female'
else 
'unknown'
end as engsex --列取别名engsex
from student

 

select *, case 
when ssex = '男' and sage <20 then 'male' 
when ssex = '女' then 'female'
else 
'unknown'
end as engsex
from student

 

select * from sc

比如给出等级分

select *, 
case 
when grade between 90 and 100 then 'A'
when grade between 80 and 89 then 'B'
when grade between 70 and 79 then 'C'
when grade between 60 and 69 then 'D'
else 'F'
end as 等级分
from sc

 

--把所有小于80分的成绩提高10%, 大于等于80分的，加1分。

update sc set grade = 
case 
when grade >=80 then grade + 1
else 
grade*1.1
end

 

-------------------------------------------------------------
delete from student where sno = '101'

delete from sc
--sc中所有记录被删

begin tran
rollback

--删除计算机科学系所有学生的选课记录

法一：

delete from sc
where sno in
(select Sno from Student 
where sdept='cs');

法二：（错误示范）

delete from sc where exists 
( select sno from student 
where sdept='计算机科学与技术')
--会删除所有记录，因为条件永远是真（有1个学生是计算机…就满足条件）

法三：

delete from sc where exists
(select * from student where sc.sno = sno
and sdept = '计算机科学与技术')

 

select * from sc

子查询中，这里的sno是student.sno还是sc表中的sno?
是student的，因为from student 表，数据来与student
在子查询中，sno不加任何表名，默认是from后面的表的
字段。因此，这里sno 就是student.sno
delete from sc where exists
(select * from student where student.sno
= student.sno
and sdept = '计算机科学与技术')

---------------------------------------------------------
update student set sdept = null
where sno = '100'

 

select * from student
where sdept = null

select * from student
where sdept != null
--不能写成 =null，也不能写成 ！=null

--只能写成 is null ,或 is not null

select * from student where sdept is null

select 2+3;

select 2+ null --结果为null

select sname + '(' + sdept + ')' from student
--如果院系是null，则整体为null
--应该用case

select sname + case 
　　　　　　　　when sdept is null then ''
　　　　　　　　else  '('+ sdept + ')'
　　　　　　　　end
from student

---------------------------

select sname + '(' + case 
　　　　　　　　　　　　when sdept is null then '未定'
　　　　　　　　　　　　else sdept 
　　　　　　　　　　　　end 
　　　　　　　　+ ')'
from student

 

--sql servr中，有个函数，isnull用来对空值进行上述处理
--oracle nvl()
--如果为空，返回后面值，否则是本来值

select sname + isnull(sdept,'未定')
from student    

 

--nuknowm 为false
select * from student where
sdept = '计算机科学与技术'

select * from sc

--把1号同学c1,c1 课改为null
update sc set grade = null
where sno = '1' and cno in ('c1','c2')

select * from sc 
　　where sno = '1' and (grade < 90 or grade is null)

--或者null改成0分

select * from sc
　　　　where sno = '1' and isnull(grade, 0)< 90

 

-------------------------------------------------------------
视图 一个逻辑概念，定义一个查询

create view v_学生成绩 as 
select a.sno, sname, cname, grade from student a 
join sc b on a.sno = b.sno 
join course c on b.cno = c.cno

 

select * from v_学生成绩
--换成：
select * from (select a.sno, sname, cname, grade from student a 
join sc b on a.sno = b.sno 
join course c on b.cno = c.cno) a

select * from v_学生成绩
--换成：
select * from (select a.sno, sname, cname, grade from student a 
join sc b on a.sno = b.sno 
join course c on b.cno = c.cno) a