SQL实例整理
最后更新于：2022-04-01 09:55:21
本文适合将w3school的SQL教程（[http://www.w3school.com.cn/sql/sql_create_table.asp](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），边学习边写博客真的很花时间，累并快乐着～～～