数据库管理系统mysql基础知识（二）

mysql8.0 workbench
接上一篇

# 例3.27查询所有考试不及格学生的学号
SELECT 
    sno
FROM
    sc
WHERE
    grade < 60;

# 例3.27查询所有年龄在20到23之间的学生
SELECT 
    sname
FROM
    student
WHERE
    sage BETWEEN 20 AND 23;/* 等价于 sage>=20 and sage <=23 */

# 例3.28 查询计算机科学系CS,数学系MA,信息系IS学生的姓名
SELECT 
    sname, ssex
FROM
    student
WHERE
    sdept IN ('CS' , 'MA', 'IS'); /* 等价于 sdept = 'CS' or sdept = 'MA' or sdept = 'IS' */

# 例3.29 查询年龄不在在20~23之间的学生姓名以及其性别
SELECT 
    sname, ssex
FROM
    student
WHERE
    sdept NOT BETWEEN 20 AND 23; /* 等价于 sage<20 or sage >23 */

#例3.30 查询学号为201215121的学生 
SELECT 
    *
FROM
    student
WHERE
    sno LIKE '201215121'; /* 等价于sno = '201215121' */

# 例3.31 查询选择了2号课程且成绩大于90的学生姓名及其学号    
SELECT 
    student.sno, sname
FROM
    student,
    sc
WHERE
    student.sno = sc.sno AND sc.cno = '2'
        AND sc.grade >= 90;
        
set foreign_key_checks = 0; # 关闭外键约束插入数据        
INSERT INTO student VALUES ('201215135','欧阳风','男',19,'IS');
INSERT INTO student VALUES ('201215138','欧%风','男',19,'IS');
set foreign_key_checks = 1; # 插入外币，打开外键约束 
# 后面使用

# 例3.32 查询所有姓刘的学生的姓名以及学号
SELECT 
    sname, sno
FROM
    student
WHERE
    sname LIKE '刘%';
/* 这里使用了模糊查询，其中%为通配符,只要姓是'刘'的学生会被全部找出，而不关心其后面的名有几个字,没有也行 */

# 例3.32 查询姓欧阳，且全名为三个汉字的学生姓名和学号
SELECT 
    sname, sno
FROM
    student
WHERE
    sname LIKE '欧阳_';
/* 这里使用了模糊查询，其中_为占位符,等于其占了一个位置，
那么这个字符串的长度也就确定了，长度为3，但如果是通配符%就无法确定 */

# 例3.33 查询第二个字为阳的学生学号和姓名
SELECT 
    sname, sno
FROM
    student
WHERE
    sname LIKE '_阳%';
/* 占位符和通配符配合使用我们只关心第二个字是阳，至于阳后面的是什么无关紧要*/

# 例3.34 那么如何查询含有%的姓名呢？
SELECT 
    sname, sno
FROM
    student
WHERE
    sname LIKE '%';
    
/* 
显然如此做法是错误的，查询的结果是全部学生，
应为%是通配符,那么要想让其成为普通字符必须将其转义成普通字符，
mysql中提供的转移字符是 '\' ，默认的
*/

SELECT 
    sname, sno
FROM
    student
WHERE
    sname LIKE '%\%%';
    
/* 
这个语句中第一个%是通配符,而第二个百分号由于被转义，
就是普通%，第三个百分号是通配符,就等于查询含有%号的姓名
 */

SELECT 
    sname, sno
FROM
    student
WHERE
    sname LIKE '%$%%' escape'$';
    

/* 
当然也可以人为的定义转义字符比如这里使用的$。
事实上还有一个问题，比如转义字/符如何转义？
有一点需要特别注意，千万不要在字符串末尾加\,否着会报错，因为为单引号会被转义。比如
INSERT INTO student VALUES ('201215139','我们\','男',19,'IS'); 会明显报错
*/

# 例3.35 查询全体学生的情况，查询结果按所在系的系号升序排列，同一系中的学生按照年龄排序
SELECT 
    *
FROM
    student
ORDER BY sdept , sage DESC;
/* 升序为asc，mysql默认的排序是升序，因此可以省略*/

# 例3.36 查询学生总人数
SELECT 
    COUNT(*)  /* 聚集函数，用来计算表中存在多少条记录的,也可以使用count(1) */
FROM
    student;

# 例3.37 查询选修了课程的总人数    
SELECT 
    COUNT(distinct sno)
FROM
    sc;
/* 其中distinct是用来去除重复的学号,因为某些学生可能选择了多门课 */

# 例3.38 计算选修一号课程的学生的平均成绩
SELECT 
    AVG(grade)
FROM
    sc
WHERE
    cno = '1';

/* 除了上述的函数以外还有，max，sum,min等，课本98页 */

# 例3.40 查询平均成绩大于90的学号和平均成绩 
SELECT 
    sno, AVG(grade)
FROM
    sc
GROUP BY sno  /* group by 用于分组，将记录按照sno分成多组 */
HAVING AVG(grade) > 90; /* 对每个分组的约束，不能写在where子句中 */


# 例3.41 等值连接(旧版本）
SELECT 
    *
FROM
    student,
    sc
WHERE
    student.sno = sc.sno;
    
# 例3.42 等值连接(新版本)
SELECT 
    *
FROM
    student
        INNER JOIN
    sc USING (sno);

# 例3.43 查询选修了2号课程且成绩高于90分的学生姓名和学号
SELECT 
    sc.sno, sname
FROM
    student,
    sc
WHERE
    sc.cno = '2' AND sc.sno = student.sno
        AND sc.grade > 90;

# 例3.44 自身连接(旧版本)
SELECT 
    *
FROM
    course x,
    course y
WHERE
    x.cpno= y.cno;
    
# 例3.45 自身连接(新版本)
SELECT 
    *
FROM
    course x
        INNER JOIN
    course y ON x.cpno = y.cno;
    
# 例3.46 多表外链接 具体语法看课本
SELECT 
    *
FROM
    student s
        LEFT OUTER JOIN
    sc ON (s.sno = sc.sno)
        LEFT OUTER JOIN
    course ON (sc.cno = course.cno);
    
/* 
上述语句中的outer是可以省略的,外连接也可以多表连接
*/

# 例3.47 使用using外连接但连接结果不会出现重复列
SELECT 
    *
FROM
    student s
        LEFT JOIN
    sc USING (sno);
    
/*
上述外连接的不同之处在于连接后不会形成多余的学号
如果采用前文中的方法便会含有多余
*/

# 例3.48 笛卡尔乘积
SELECT 
    *
FROM
    test t1,
    test t2;
    
/* 
这种操作结果返回的是test与自身的笛卡尔积
*/

# 例3.49 多表等值连接
SELECT 
    student.sno, sname, cname, grade
FROM
    student,
    sc,
    course
WHERE
    student.sno = sc.sno
        AND sc.cno = course.cno;
        
# 例3.49 嵌套查询，查询选修了2号课程的学生学号
SELECT 
    sname
FROM
    student
WHERE
    sno IN (SELECT 
            sno
        FROM
            sc
        WHERE
            cno = '2');
            
/*
一种嵌套操作，其实可以使用exists来进行表示
这里需要知道能用in的一般都可以转换为exists
反之亦然,二者在不同场合的查询效率不同
改后的代码如下：

SELECT 
    sname
FROM
    student
WHERE
    exist (SELECT 
            *
        FROM
            sc
        WHERE
            student.sno = sc.sno and cno = '2');
            
这种方法的执行顺序与前面的操作有所不同
这里嵌套的子查询称为相关子查询，与父查询有关系

参考数目《数据库系统概论第五版》
上一篇基础知识（一）