开发者社区> 王小王-123> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

初识MySQL之综合复习篇(干货)

简介: 初识MySQL之综合复习篇(干货)
+关注继续查看

在日常开发和数据查询当中,我们需要等值连接或者左右连接多个表进行相关的字段查询

当然我们在MySQL语法知识点里面我们需要对它的整个框架要有一个比较熟悉的掌握程度,这样才能在比较的熟悉的操作我们的数据

建表

# 根据E-R图创建表
DROP TABLE IF EXISTS wxw_data;
CREATE TABLE wxw_data (
    SN CHAR(20) PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL,
    PRICE DOUBLE,
    EDITOR varchar(50)
);

DROP TABLE IF EXISTS wxw_CANKAO;
CREATE TABLE wxw_CANKAO (
  JIAOCAI_SN CHAR(20),
    COURSE_CODE char(8),
    type varchar(50),
    PRIMARY KEY (JIAOCAI_SN,COURSE_CODE),
    CONSTRAINT fk_jiaocai_sn FOREIGN KEY (JIAOCAI_SN) REFERENCES wxw_data(SN),
    CONSTRAINT fk_course_code FOREIGN KEY (COURSE_CODE) REFERENCES tb_course(CODE)
);

USE XSXK;
INSERT INTO wxw_data VALUES ('978-7-115-48910-4','MySQL数据库技术与应用',42.00,'张素青');
INSERT INTO wxw_data SELECT '978-7-115-48910-5','MySQL数据库技术与应用',42.00,'张素青';

# 插入自己的信息
INSERT INTO tb_student SELECT '2019888888','tanguangyu','男','2010-10-10','15902333748',
 ID from tb_major_class where COLLEGE='数理与大数据学院' and grade=2019 and class=1 and
 major='大数据';
SELECT * FROM tb_student where ID='2019888888';

INSERT INTO tb_student SELECT '2018888888','tanguangyu','男','2010-10-10','15902333748',
 ID from tb_major_class where COLLEGE='数理与大数据学院' and grade=2018 and class=1 and 
 major='大数据';
 
SELECT * FROM tb_student where ID='2018888888';

USE XSXK;
DELETE FROM tb_student where ID='2018888888';
• 1
• 2

删除该条件下的一行数据

UPDATE tb_student SET GENDER='女' WHERE ID='2019888888';
--定位到ID为这个的行,然后把性别改为女即可,基本的操作及时

update 表名 set 需要改变的字段='新字段属性' where 条件定位

SELECT * FROM tb_student where ID='2019888888';

# 查询每个老师带了哪些课程班
select CODE 班号, TEACHER_ID 教师编号 from tb_course_class;
# 1, 返回的字段有哪些
SELECT t.NAME 教师姓名, cc.CODE 班号
# 2, 要返回的字段从哪些表(临时的表、视图、表连接、子查询结果集)中得到
FROM tb_teacher t, tb_course_class cc
# 3, 判断要返回的字段从哪些行取得
WHERE t.ID = cc.TEACHER_ID;


# 查询每个老师带了多少课程班
# 1, 返回的字段有哪些
SELECT t.NAME 教师姓名, COUNT(cc.CODE) 班级数量 
# 2, 要返回的字段从哪些表(临时的表、视图、表连接、子查询结果集)中得到
FROM tb_teacher t, tb_course_class cc
# 3, 判断要返回的字段从哪些行取得
WHERE t.ID = cc.TEACHER_ID
# 4, 按什么条件分组聚合
GROUP BY t.NAME;

# 查询每个老师带了多少课程班,按数量多少排序
# 1, 返回的字段有哪些
SELECT t.NAME 教师姓名, COUNT(cc.CODE) 班级数量 
# 2, 要返回的字段从哪些表(临时的表、视图、表连接、子查询结果集)中得到
FROM tb_teacher t, tb_course_class cc
# 3, 判断要返回的字段从哪些行取得
WHERE t.ID = cc.TEACHER_ID
# 4, 按什么条件分组聚合
GROUP BY t.NAME
# 5, 排序默认正序从小到大
ORDER BY 班级数量 DESC;

# 查询每个老师带了多少课程班,按数量多少排序, 筛选大于5个班的老师
# 1, 返回的字段有哪些
SELECT t.NAME 教师姓名, COUNT(cc.CODE) 班级数量 
# 2, 要返回的字段从哪些表(临时的表、视图、表连接、子查询结果集)中得到
FROM tb_teacher t, tb_course_class cc
# 3, 判断要返回的字段从哪些行取得
WHERE t.ID = cc.TEACHER_ID
# 4, 按什么条件分组聚合
GROUP BY t.NAME
# 5, 对查询结果集再次筛选
HAVING 班级数量>5;

# 查询每个老师带了多少课程班,按数量多少排序, 筛选大于5个班的老师
# 1, 返回的字段有哪些
SELECT t.NAME 教师姓名, COUNT(cc.CODE) 班级数量 
# 2, 要返回的字段从哪些表(临时的表、视图、表连接、子查询结果集)中得到
FROM tb_teacher t, tb_course_class cc
# 3, 判断要返回的字段从哪些行取得
WHERE t.ID = cc.TEACHER_ID
# 4, 按什么条件分组聚合
GROUP BY t.NAME
# 5, 对查询结果集再次筛选
HAVING 班级数量>5
ORDER BY 班级数量
LIMIT 1;


# 子查询
# where子句中的子查询
# 查询某门课程是那个学院开设的
SELECT DISTINCT COLLEGE
FROM tb_major_class
WHERE ID IN 
    (SELECT MAJOR_CLASS FROM tb_student WHERE ID IN
        (SELECT STUDENT_ID FROM tb_electives WHERE COURSE_CLASS_CODE IN 
            (SELECT CODE FROM tb_course_class WHERE COURSE_CODE IN
                (
                SELECT CODE FROM tb_course WHERE NAME='Web程序设计基础'
                )
            )
        )
    );

# 表连接(内连接)
# 查询某门课程是那个学院开设的
SELECT DISTINCT mc.COLLEGE
FROM tb_major_class mc, tb_student s, tb_electives e, tb_course_class cc, tb_course c
WHERE 
    mc.ID = s.MAJOR_CLASS and
    s.ID = e.STUDENT_ID and
    e.COURSE_CLASS_CODE = cc.CODE and
    cc.COURSE_CODE = c.CODE and
    c.NAME like 'Web%';
    
# 查询某门课程是那个学院开设的
SELECT DISTINCT c.NAME 课程,mc.COLLEGE 学院
FROM tb_major_class mc, tb_student s, tb_electives e, tb_course_class cc, tb_course c
WHERE 
    mc.ID = s.MAJOR_CLASS and
    s.ID = e.STUDENT_ID and
    e.COURSE_CLASS_CODE = cc.CODE and
    cc.COURSE_CODE = c.CODE;
# SELECT 生成一个结果集(二维表)
SELECT 1,2;
SELECT 'abc';
SELECT CURRENT_TIME();

# FROM 扫描一个二维表结构(实体表、临时表、查询结果集)
SELECT tb_course.NAME, CODE
FROM tb_course;

# FROM 表连接(新的临时表)
-- 交叉连接(笛卡尔积) A CROSS JOIN B => 列数=A+B 行数=A*B
SELECT * from tb_course,tb_teacher;
SELECT * FROM tb_course CROSS JOIN tb_teacher;
# 内连接
SELECT * from tb_course as c,tb_course_class as cc WHERE c.CODE=cc.COURSE_CODE;

# 查询课程班级的课程名称
# 查课程班级对应的课程编号
SELECT COURSE_CODE,CODE FROM tb_course_class WHERE CODE='3SL1113A.09';
# 查课程名称
SELECT * FROM tb_course WHERE CODE = (SELECT COURSE_CODE FROM tb_course_class WHERE CODE='3SL1113A.09');

SELECT a.*,b.NAME FROM 
        (SELECT COURSE_CODE,CODE FROM tb_course_class WHERE CODE='3SL1113A.09') as a
     , 
        tb_course b
WHERE a.COURSE_CODE=b.CODE;

SELECT a.*,b.NAME FROM 
        (SELECT Teacher_id,CODE FROM tb_course_class WHERE CODE='3SL1113A.09') as a
     INNER JOIN
        tb_teacher b
   ON a.teacher_id=b.ID;  -- join 条件
     
     
SELECT cc.CODE,cc.COURSE_CODE,t.NAME FROM 
    tb_teacher t INNER JOIN tb_course_class cc ON t.ID=cc.teacher_id
WHERE cc.CODE='3SL1113A.09';

SELECT cc.CODE,c.NAME,t.NAME from 
    tb_course c, tb_teacher t, tb_course_Class cc
WHERE c.CODE=cc.COURSE_CODE and t.ID=cc.Teacher_id;

SELECT cc.CODE 班级编号,c.NAME 课程名称,t.NAME 授课老师 from 
   tb_course_class cc 
     JOIN tb_course c ON cc.COURSE_CODE=c.CODE 
     JOIN tb_teacher t ON cc.teacher_id=t.ID
where cc.CODE='3SL1113A.09';
     
# 外连接 (左连接,右连接)



# WHERE 条件判断 => 是否SELECT
SELECT 'a' FROM tb_course WHERE 1=1;
# 查询2学分的课程信息
SELECT * FROM tb_course WHERE CREDIT=2;
# 查询不及格的学生成绩
SELECT * FROM tb_electives WHERE SCORE < 60;
# LIKE (不是喜欢,是像)  % 通配符,匹配任意0个或多个字符
SELECT * FROM tb_course WHERE NAME like '%程序设计';
# 查询和‘我’同姓的学生信息
SELECT * FROM tb_student WHERE NAME like '谭%飞';
# IN  ==> OR OR OR 
SELECT * FROM tb_student WHERE NAME='谭飞' OR NAME='谭华' OR NAME='谭贝';
SELECT * FROM tb_student WHERE NAME IN ('谭飞','谭华','谭贝');

# 子查询
# 查询3SL1037A.01班分数最高的学生的ID
# 1.先要知道最高分
SELECT MAX(SCORE) FROM tb_electives WHERE COURSE_CLASS_CODE='3SL1037A.01';
# 2.再知道谁是最高分
SELECT * FROM tb_electives WHERE SCORE=100 AND COURSE_CLASS_CODE='3SL1037A.01';
SELECT * FROM tb_electives WHERE COURSE_CLASS_CODE='3SL1037A.02' ORDER BY SCORE ASC LIMIT 1;

UPDATE tb_electives SET SCORE=100 WHERE COURSE_CLASS_CODE='3SL1037A.01' and SCORE=99;



SELECT * FROM tb_electives 
WHERE 
        SCORE IN (SELECT MIN(SCORE) FROM tb_electives WHERE COURSE_CLASS_CODE like '3SL1037A.0%') 
        AND 
        COURSE_CLASS_CODE like '3SL1037A.0%';


# 查询最低分
SELECT MIN(SCORE) FROM tb_electives ;
# 分班查询最低分
SELECT MIN(SCORE),COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE;

# 分班查询最低分的学生
SELECT MIN(SCORE),COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE;

select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE 
from 
    tb_electives t 
        INNER JOIN 
    (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
        ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore ;

        
select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME 
from 
    tb_electives t 
    INNER JOIN 
    (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
        ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore 
    LEFT JOIN tb_student s 
        ON t.STUDENT_ID=s.ID;

select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME 学生姓名,c.NAME 课程名
from 
    tb_electives t 
    INNER JOIN 
    (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
        ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore 
    LEFT JOIN tb_student s 
        ON t.STUDENT_ID=s.ID
    LEFT JOIN tb_course c
     ON instr(t.COURSE_CLASS_CODE,c.CODE);
    
select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME 学生姓名,c.NAME 课程名,cc.TEACHER_ID
from 
    tb_electives t 
    INNER JOIN 
    (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
        ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore 
    LEFT JOIN tb_student s 
        ON t.STUDENT_ID=s.ID
    LEFT JOIN tb_course c
     ON instr(t.COURSE_CLASS_CODE,c.CODE)
  LEFT JOIN tb_course_class cc 
     ON t.COURSE_CLASS_CODE=cc.CODE;


select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME 学生姓名,c.NAME 课程名,te.NAME 教师姓名,te.TITLE 教师职称
from 
    tb_electives t 
    INNER JOIN 
    (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
        ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore 
    LEFT JOIN tb_student s 
        ON t.STUDENT_ID=s.ID
    LEFT JOIN tb_course c
     ON instr(t.COURSE_CLASS_CODE,c.CODE)
  LEFT JOIN tb_course_class cc 
     ON t.COURSE_CLASS_CODE=cc.CODE
  LEFT JOIN tb_teacher te 
     ON cc.TEACHER_ID=te.ID;
     
select 
  mc.COLLEGE 学院,
  concat(mc.MAJOR,mc.GRADE,'-',mc.CLASS) as 专业班级,
  t.STUDENT_ID 学生学号,
    s.NAME 学生姓名,
    t.COURSE_CLASS_CODE 课程班编号,
    c.NAME 课程名称,
    t.SCORE 分数,
    te.NAME 教师姓名,
    te.TITLE 教师职称
from 
    tb_electives t 
    INNER JOIN 
    (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms 
        ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms.minscore 
    LEFT JOIN tb_student s 
        ON t.STUDENT_ID=s.ID
    LEFT JOIN tb_course c
     ON instr(t.COURSE_CLASS_CODE,c.CODE)
  LEFT JOIN tb_course_class cc 
     ON t.COURSE_CLASS_CODE=cc.CODE
  LEFT JOIN tb_teacher te 
     ON cc.TEACHER_ID=te.ID
  LEFT JOIN tb_major_class mc 
     ON mc.ID=s.MAJOR_CLASS;
     
 
# 查询‘我’所在专业班级的信息
# 专业班在哪儿找: tb_major_class
# 已知条件:我=》 数理学院,大数据专业,2019级, 3班
SELECT * 
FROM tb_major_class 
WHERE COLLEGE='数理与大数据学院' 
                AND 
            MAJOR='大数据' 
                AND 
            GRADE=2019 
                AND 
            CLASS=3;

# 我的专业班级编号为43
# 统计我这个班有多少人
# 统计数量COUNT()
# 人-学生人数,学生在哪里找: tb_student
SELECT COUNT(*) AS 学生人数, MAJOR_CLASS AS 班级编号
FROM tb_student
WHERE MAJOR_CLASS=43;

# 分组统计每个班的学生人数
SELECT COUNT(*) AS 学生人数, MAJOR_CLASS AS 班级编号
FROM tb_student
GROUP BY MAJOR_CLASS;

# 分组统计每个班的学生人数, 按学生人数从多到少排序
# major class 专业班人数
SELECT COUNT(*) AS 学生人数, MAJOR_CLASS AS 班级编号
FROM tb_student
GROUP BY MAJOR_CLASS
ORDER BY 学生人数 DESC;

# course class 课程班人数
# 课程班学生在哪里找: tb_electives
SELECT COUNT(*) as 学生人数, COURSE_CLASS_CODE as 课程班编号
FROM tb_electives
GROUP BY COURSE_CLASS_CODE;

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
多解法综合题:「动态规划」&「前缀和 二分」&「双指针」| Java 刷题打卡
多解法综合题:「动态规划」&「前缀和 二分」&「双指针」| Java 刷题打卡
12 0
MySQL备份与恢复方案验证
MySQL备份与恢复方案验证http://www.bieryun.com/3351.html mysqlbackup+xtrabackup (RHEL6X86_64) 之前针对mysql的备份做了个简单测试,与大家分享下          目前关于MySQL备份工具最流行的主要有三种 1.xtrabackup  -----Percona opensource 2.mysqlbackup -----mysql Enterprise 3.mysqldump -----mysql 自带工具 三种工具都支持热备;全备和增备。
2331 0
MultiRow中文版技术白皮书
引言 Microsoft .NET是一项比较成熟的技术开发框架,很多的商业应用都是基于.NET开发的,在这些商业应用中,作为数据展示和录入的表格控件,是不可或缺的。大多数的表格控件在数据展示上都是比较规整的,即单行单列的展示数据。
918 0
+关注
王小王-123
快乐代码,精彩人生!
268
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载