当前位置: 澳门新濠3559 > 数据库 > 正文

澳门新濠3559子查询返回了一个集合,DCL为一体的

时间:2019-10-12 05:03来源:数据库
院系表:系别(dn),系名(dname),系主任(dean) 创建表 下面来创建多个表 澳门新濠3559,教师表:教师编号(tn),教师名(tname),所属院系(dn),工资(salary) create table teacher(tn char(3), tname char(10), dn char(

院系表:系别(dn),系名(dname),系主任(dean)

澳门新濠3559 1

创建表

澳门新濠3559 2

下面来创建多个表

澳门新濠3559 3

澳门新濠3559,教师表:教师编号(tn),教师名(tname),所属院系(dn),工资(salary)

create table teacher(tn char(3), tname char(10), dn char(2), salary float(2)) engine=MYISAM character set utf8;

INSERT INTO teacher(tn, tname, dn, salary) VALUES ("001", "赵三", "01", 1200.00);

Θ SOME表示前面的记录需要与子查询结果中某个记录做运算,如果为TRUE则该记录被接受

create table dept(dn char(2), dname char(10), dean char(10)) engine=MYISAM character set utf8;

INSERT INTO dept(dn, dname, dean) VALUES("01", "机电", "李三");


  • DDL,数据库定义语言,创建,修改,删除数据库,表,视图,索引,约束条件等
  • DML,数据库操纵语言,对数据库中的数据进行增,删,改,查
  • DCL,数据库定义语言,对数据库总数据的访问设置权限

基本语法:查询语句 [NOT] IN 子查询

  • DDL语言引导词:CREATE(创建),ALTER(修改),DROP(撤销)
  • DML语言引导词:INSERT(增),DELETE(删),UPDATE(改),SELECT(查)
  • DCL语言引导词:GRANT(授权),REVOKE(调用)

              查询语句 Θ ALL 子查询


等价转换为不存在这样一门课程,该课程由001教师主讲并且该课程该同学没学过

create table student( sn char(8), sname char(10), ssex char(2), sage integer, dn char(2), sclass char(6)) engine=MYISAM character set utf8;

INSERT INTO student (sn,  sname,  ssex,  sage,  dn,  sclass) VALUES ('98030101',    '张三',    '男',    '20',    '03',    '980301'  ) ;

澳门新濠3559 4

选课表:学号(sn),课程号(cn),成绩(score)

create table sc(sn char(8), cn char(3), score float(1)) engine=MYISAM character set utf8;

INSERT INTO sc(sn, cn, score) VALUES ("980230101", "001", 92);

下面是创建后完整的表

student

澳门新濠3559 5

dept

澳门新濠3559 6

teacher

澳门新濠3559 7

course

澳门新濠3559 8

sc

澳门新濠3559 9

简单查询操作


  • 检索学生表中所有年龄小于等于19岁的学生姓名和年龄

SELECT sname, sage FROM student WHERE sage<=19;

澳门新濠3559 10

  • 检索教师表中所有工资 少于1500元或者工资大于2000元,并且是03系的教师姓名(注意括号)

SELECT tname
FROM teacher
WHERE (salary<1500 OR salary>2000) AND dn="03";

澳门新濠3559 11

  • 求既学过001课程又学过002课程的同学学号(错误版)

SELECT sn
FROM sc
WHERE cn="001" AND cn="002";

上面的语句是错误的,因为WHERE条件是对每条记录进行检查,在一条记录中cn不可能同时为001又为002

  • 在选课表中查询成绩大于80分的学号(结果重复版)

SELECT sn
FROM sc
WHERE score>80;

澳门新濠3559 12

  • 在选课表中查询成绩大于80分的学号(结果未重复)

SELECT DISTINCT sn
FROM sc
WHERE score>80;

澳门新濠3559 13

  • 按学号由小到大的顺序显示出所有学生的学号和姓名

SELECT sn,sname
FROM student
ORDER BY sn ASC;

ORDER是对查询后的结果进行排序

澳门新濠3559 14

  • 检索002号课大于80分的所有同学学号并按成绩由高到低顺序显示

SELECT sn
FROM sc
WHERE score>80 AND cn="002"
ORDER BY score DESC;

澳门新濠3559 15

模糊查询


模糊查询是当我们不能精确查询条件时,可以使用的。比如我们想找一个人,但只知道这个人姓张,这时候我们可以通过模糊查询来找出姓张的,然后做具体分析。

  • 检索所有姓张的同学

SELECT sname
FROM student
WHERE sname LIKE "张%";

%号表示匹配零个或多个字符

澳门新濠3559 16

  • 检索名为张某某的同学姓名

SELECT sname
FROM student
WHERE sname LIKE "张__";

注意张后面是两个下划线,一个下划线代表一个字符。

  • 检索不姓张的同学姓名

SELECT sname
FROM student
WHERE sname NOT LIKE "张%";

澳门新濠3559 17

多表联合查询


多表联合查询涉及到表的连接操作,通过通过将多个表连成一张大表,然后做查询操作。前面我们在关系代数中提到表的连接可以通过笛卡尔积 选择来实现,而SQL中也是这样处理的。

  • 按001号课成绩由高到低顺序显示所有学生的姓名

SELECT DISTINCT sname
FROM student,sc
WHERE student.sn=sc.sn AND sc.cn="001"
ORDER BY score DESC;

先将student和sc表进行笛卡尔积运算形成一张大表,然后执行WHERE条件判断进行选择,接着执行排序操作,最后将需要的属性值投影出来。

澳门新濠3559 18

  • 按数据库课成绩从高到底显示同学姓名

SELECT DISTINCT sname
FROM student,sc,course
WHERE student.sn=sc.sn AND sc.cn=course.cn AND course.cname="数据库"
ORDER BY score DESC;

澳门新濠3559 19

重名操作


当表自连接时需要对表进行重名操作,不仅对表起别名而且需要对投影的列也要进行别名处理。

澳门新濠3559 20

as是可以省略的

  • 查询薪水有差额的老师姓名

SELECT T1.tname AS teacher1,T2.tname AS teacher2
FROM teacher AS T1,teacher AS T2
WHERE T1.salary > T2.salary;

注意:查询后列名发生了变化

澳门新濠3559 21

  • 查询既学过001课程又学过002课程的学生学号

SELECT sc1.sn
FROM sc AS sc1, sc AS sc2
WHERE sc1.cn="001" AND sc2.cn="002" AND sc1.sn=sc2.sn;

澳门新濠3559 22

我们可以看到上面的操作中未对属性名进行别名处理,是因为查询的结果集中属性名不冲突。

插入操作


简单的插入操作我们在创建表的时候已经介绍过了,下面介绍一下更为复杂操作。

下图中是将从student表中找到的数据直接插入到st(sn,sname)当中去。由此可看出,insert语句后面是可以跟子查询的。

澳门新濠3559 23

删除操作


  • 删除98030101号同学所选的课程

DELETE FROM sc WHERE sn="98030101";

更新操作


语法格式

澳门新濠3559 24

  • 将所有教师工资上调5%

UPDATE teacher SET salary=salary*1.05;

修改表的定义


澳门新濠3559 25

SELECT SUM(salary)
FROM teacher

SQL语言是集DDL,DML,DCL为一体的数据库语言,学好数据库首先要掌握下面9个引导词

结果计算与聚类计算

澳门新濠3559 26

基本语法: [NOT] EXISTS (子查询)

功能概述

  • 求任意两名教师的薪水差额(差额>0)

相关子查询

课程表:课程号(cn),课程名(cname),教师编号(tn),学时(chours),学分(credit)

create table course(cn char(3), cname char(12), tn char(3), chours integer, credit float(1)) engine=MYISAM character set utf8;

INSERT INTO course(cn, cname, tn, chours, credit) VALUES("001", "数据库", "001", 40, 6);

  • 求数据库课程的平均成绩

学生表:学号(sn),姓名(sname),性别(sex),年龄(sage), 所属系(dn),班级(sclass)

外层查询的参数可以被带入到内层查询中,而内层查询的参数不能在外层查询中使用,这和高级编程中的循环一个道理。

  • 某一元素是否是某一集合成员
  • 某一集合是否包含另一集合
  • 测试集合是否为空
  • 测试集合是否存在另一元组

澳门新濠3559 27

等价于不存在98030101同学学过的课程,该同学没有学过

子查询是出现在WHERE子句中的SELECT语句被称为子查询,子查询返回了一个集合。

SELECT sname
FROM student
WHERE 60 > ALL(
    SELECT score
    FROM sc
    WHERE sc.sn = student.sn)

EXISTS查询


SELECT tname
FROM teacher
WHERE salary <= ALL(
    SELECT salary
    FROM teacher)

澳门新濠3559 28

结果计算和聚类计算是对查询结果集中的一些数据进行计算

Θ 是运算符:<,>,<=,>=,<>

当内层查询没有使用到外查询的参数时,我们可以内层查询是非相关子查询。上图中就是非相关子查询。判断是否相关最简单的方式就是内层查询是否能独立执行。

  • 找出001号课成绩不是最高的所有学生的学号

IN子查询

语义:查询语句产生的结果是否在子查询当中

澳门新濠3559 29

  • 查询既学过001课程又学过002课程的同学学号

子查询

澳门新濠3559 30

  • 列出选修了001号课程的学生学号和姓名

等价于不存在这样的学生,他学过李明老师的课

澳门新濠3559 31

含义:判断子查询结果集是否为空,当子查询为空时,EXISTS判断为false,而NOT EXISTS判断为true。NOT EXISTS使用情况比较多

括号中产生的结果是一个集合(这里称为子集合),集合中的元素是学过001课程的学生的学号。前半部分的查询语句是从student中每取一条记录来查看记录中的sn是否在子集合中。如果是则将该记录进行标记,否则取出下一条继续比较。最后将被标记的记录中sn和snames属性值输出。

SELECT t1.tname AS teacher1, t2.tname AS teacher2,t1.salary-t2.salary
FROM teacher AS t1, teacher AS t2
WHERE t1.salary >t2.salary

  • 列出至少学过98030101号同学学过所有课程的同学学号

澳门新濠3559 32

  • 求教师工资总和
  • 检索学过001号教师主讲的所有课程的同学姓名


  • 列出没有学过李明老师课的学生姓名

非相关子查询

SELECT sn
FROM sc
WHERE cn="001" AND sn IN (SELECT sn FROM sc WHERE cn="002");

SELECT AVG(score)
FROM sc,course
WHERE sc.cn=course.cn AND course.cname="数据库"

SELECT sname
FROM student
WHERE NOT EXISTS(
    SELECT *
    FROM course,sc,teacher
    WHERE course.cn=sc.cn AND teacher.tn=course.tn AND tname="李明" AND student.sn=sc.sn)

  • 找出张三同学成绩最低的课程号

现实中,很多情况下需要进行下述条件判断

  • 找出工资最低的教师姓名

澳门新濠3559 33

  • 找出所有成绩都不及格的学生姓名(相关查询)

SELECT sname
FROM student
WHERE NOT EXISTS(
    SELECT *
    FROM course
    WHERE tn="001" AND NOT EXISTS(
        SELECT *
        FROM sc
        WHERE sn=student.sn AND cn=course.cn)
    )

澳门新濠3559 34


上图的例子中内层子查询使用到了外层的变量(Stud),这样内层查询就不能独立执行

聚类计算

Θ ALL表示前面的记录需要与子查询结果中所有记录做运算,如果全为TRUE则该记录才被接受

SELECT sn
FROM sc
WHERE cn="001" AND score < SOME(
    SELECT score
    FROM sc
    WHERE cn = "001")

SELECT cn
FROM sc,student
WHERE sname="张三" AND sc.sn=student.sn AND score <=ALL(
    SELECT score
    FROM sc
    WHERE sn=student.sn)

查询分为外层查询和内层查询

SELECT sn, sname
FROM student
WHERE sn IN (SELECT sn FROM sc WHERE cn="001");

基本语法:查询语句 Θ SOME 子查询

澳门新濠3559 35

SOME与ALL子查询

SELECT sn
FROM sc AS sc1
WHERE sn !="98030101" AND NOT EXISTS(
     SELECT *
     FROM sc AS sc2
     WHERE sn="98030101" NOT EXISTS(
        SELECT *
        FROM sc AS sc3
        WHERE sc2.cn=cn AND sn=sc1.sn)
    )

编辑:数据库 本文来源:澳门新濠3559子查询返回了一个集合,DCL为一体的

关键词: 澳门新濠3559