• SQL实例整理


    本文适合将w3school的SQL教程(http://www.w3school.com.cn/sql/sql_create_table.asp)都基本看过一遍的猿友阅读。

    说说博主的情况吧。毕业找工作之前确实有大概看过w3school的SQL教程,然后参加校园招聘,每次遇到一些SQL笔试题,立马懵逼了(大写的)。其实我那时候大概知道怎么写的,只是总是写不正确,或者是对一些特定的而且没有见过的场景的SQL语句,根本写不出来。相信不少猿友工作之后,其实挺多都用得不熟吧(如果白板编写的话)。

    因为大部分Java猿友工作做的事情,其实比较少情况自己去动手写特定场景的SQL(可能有也是百度,接触过一个会一个),简单SQL也是直接由框架(hibernate和Mybatis)提供接口。当然,那种专门做后台,经常跟数据打交道的Java猿友除外,因此只能说大部分。

    如果还是继续保持这样的状态的话,下次自己找工作遇到SQL笔试题,估计也会继续懵逼(大写的)。

    下面小宝鸽整理了一些实例(实例主要来自网上),以提升自己写SQL的某些关键字的理解。

    1、用一条SQL 语句 查询出每门课都大于80 分的学生姓名。(表结构如下图)

    这里写图片描述

    答案可以有如下两种:

    select distinct student_name from table_test_one where student_name not in 
    (select distinct student_name from table_test_one where score<=80);

    或者

    select student_name from table_test_one group by student_name having min(score)>80;

    第二种方法是group by 、min函数 结合 having的使用,w3school教程里面也提到过(在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用)

    似乎看懂了,但是还是没有自己运行一遍深刻!!!自己能动手敲一遍就更好了!
    下面我们自己造数据,后面的例子也会用到。

    建表然后倒入初始数据:

    DROP TABLE IF EXISTS `table_test_one`;
    CREATE TABLE `table_test_one` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `student_no` varchar(10) NOT NULL,
      `student_name` varchar(10) NOT NULL,
      `subject_no` varchar(10) NOT NULL,
      `subject_name` varchar(10) NOT NULL,
      `score` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
    INSERT INTO `table_test_one` VALUES ('1', '201601', '张三', '0001', '数学', '98');
    INSERT INTO `table_test_one` VALUES ('2', '201601', '张三', '0002', '语文', '66');
    INSERT INTO `table_test_one` VALUES ('3', '201602', '李四', '0001', '数学', '60');
    INSERT INTO `table_test_one` VALUES ('4', '201602', '李四', '0003', '英语', '78');
    INSERT INTO `table_test_one` VALUES ('5', '201603', '王五', '0001', '数学', '99');
    INSERT INTO `table_test_one` VALUES ('6', '201603', '王五', '0002', '语文', '99');
    INSERT INTO `table_test_one` VALUES ('7', '201603', '王五', '0003', '英语', '98');

    可以运行一下上面两个语句试试结果是不是你想要的。

    2、删除除了id不同, 其他都相同的学生冗余信息,表如下:

    这里写图片描述

    答案:

    delete table_test_one where id not in 
    (select min(id) from table_test_one group by 
    student_no, student_name, subject_no, subject_name, score);

    是否有看懂?如果没能看懂的话,继续往下看:

    先来造数据,题1中的数据只需要执行如下SQL就变成题2中的数据了:

    update table_test_one set subject_no = '0001', subject_name = '数学' where id = 6;

    然后我们先执行这个看看:

    select min(id) from table_test_one group by 
    student_no, student_name, subject_no, subject_name, score

    这个的执行结果如下:

    这里写图片描述

    如果还不懂就再看看几次吧。

    PS:GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组。刚刚就是GROUP BY 对多列的使用场景。

    3、行转列:

    表数据如下:

    这里写图片描述

    希望查询到结果如下:

    这里写图片描述

    答案:

    select year,
    (select amount from table_test_two t where t.month = 1 and t.year = table_test_two.year) as month1,
    (select amount from table_test_two t where t.month = 2 and t.year = table_test_two.year) as month2,
    (select amount from table_test_two t where t.month = 3 and t.year = table_test_two.year) as month3
    from table_test_two group by year;

    利用group by 实现行转列,这种场景在数据统计的时候经常用到。

    猿友可以造数据自己运行试试:

    -- ----------------------------
    -- Table structure for `table_test_two`
    -- ----------------------------
    DROP TABLE IF EXISTS `table_test_two`;
    CREATE TABLE `table_test_two` (
      `year` int(11) NOT NULL,
      `month` int(11) NOT NULL,
      `amount` decimal(10,1) NOT NULL,
      PRIMARY KEY (`year`,`month`,`amount`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of table_test_two
    -- ----------------------------
    INSERT INTO `table_test_two` VALUES ('1991', '1', '1.1');
    INSERT INTO `table_test_two` VALUES ('1991', '2', '1.2');
    INSERT INTO `table_test_two` VALUES ('1991', '3', '1.3');
    INSERT INTO `table_test_two` VALUES ('1992', '1', '2.1');
    INSERT INTO `table_test_two` VALUES ('1992', '2', '2.2');
    INSERT INTO `table_test_two` VALUES ('1992', '3', '2.3');

    4、复制表( 只复制结构, 源表名:table_test_two 新表名:table_test_three)

    答案:

    create table table_test_three as 
    select * from table_test_two where 1=2;

    PS:如果需要将数据也复制过去,则上面改成where 1=1

    5、复制表数据(将表 table_test_two 的数据复制到表table_test_three 里面)

    答案:

    insert into table_test_three (year,month,amount) 
    select year,month,amount from table_test_two;

    6、两张关联表,删除主表中已经在副表中没有的信息

    答案:

    delete from table_test_student where not exists 
    (select * from table_test_class where table_test_student.class_id = table_test_class.calss_id); 

    我们先造点数据吧:

    -- ----------------------------
    -- Table structure for `table_test_class`
    -- ----------------------------
    DROP TABLE IF EXISTS `table_test_class`;
    CREATE TABLE `table_test_class` (
      `calss_id` int(11) NOT NULL AUTO_INCREMENT,
      `calss_name` varchar(10) CHARACTER SET utf8 NOT NULL,
      PRIMARY KEY (`calss_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of table_test_class
    -- ----------------------------
    INSERT INTO `table_test_class` VALUES ('1', '一班');
    -- ----------------------------
    -- Table structure for `table_test_student`
    -- ----------------------------
    DROP TABLE IF EXISTS `table_test_student`;
    CREATE TABLE `table_test_student` (
      `student_id` int(11) NOT NULL AUTO_INCREMENT,
      `student_name` varchar(10) CHARACTER SET utf8 NOT NULL,
      `class_id` int(11) NOT NULL,
      PRIMARY KEY (`student_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of table_test_student
    -- ----------------------------
    INSERT INTO `table_test_student` VALUES ('1', '罗国辉', '1');
    INSERT INTO `table_test_student` VALUES ('2', '小宝鸽', '2');

    执行后数据如下:

    这里写图片描述

    这里写图片描述

    显然副表student中小宝鸽这条数据的calss_id,主表没有对应的class_id.

    执行对应SQL语句就会把小宝鸽这条数据删除掉了。


    未完待续……….(TODO),边学习边写博客真的很花时间,累并快乐着~~~

  • 相关阅读:
    xcode command line 安装
    Android 报错:error: too many padding sections on bottom border
    一文深入浅出理解国产开源木兰许可系列协议
    SpringBoot接口 如何生成接口文档之非侵入方式(通过注释生成)SmartDoc?
    SpringBoot接口 如何优雅的对接口返回内容统一封装?
    SpringBoot数据库管理 用flyway对数据库管理和迁移
    SpringBoot接口 如何优雅的写Controller并统一异常处理?
    SpringBoot开发 什么是热部署和热加载?devtool的原理是什么?
    SpringBoot接口 如何优雅的对参数进行校验?
    SpringBoot接口 如何生成接口文档之Swagger技术栈?
  • 原文地址:https://www.cnblogs.com/aiwz/p/6154532.html
Copyright © 2020-2023  润新知