前言:
此刻大家对“mysql数据库行转列”都比较讲究,同学们都想要了解一些“mysql数据库行转列”的相关资讯。那么小编也在网上搜集了一些有关“mysql数据库行转列””的相关内容,希望我们能喜欢,我们一起来了解一下吧!第一节 前言
本文总结了我们在开发中比较常用的一些查询SQL,有简单的分组统计、开发中常用的删除重复数据、实用的行转列且字段合并等等。这些查询SQL语句非常适合准备换工作的同学用来练习巩固自己的SQL知识。当然,这些查询SQL语句也是我们开发中经常会用到的,建议收藏方便查阅。
第二节 推荐SQL在线编辑工具
如果只是练习SQL,不需要在本地搭建环境安装数据库,SQL Fiddle提供了在线编辑SQL功能,如下图:
左边窗口写DDL and DML statements,右边窗口写select statements。
第三节 SQL题集
说明:以下SQL主要针对MySQL数据库。
题目1
NO1.1 问题描述
用一条SQL语句查出商品A、B、C目前还剩多少?
NO1.2 数据准备
CREATE TABLE t_book_store ( vc_name varchar(20), n_num int);INSERT INTO t_book_store (vc_name, n_num)VALUES ('A', '100');INSERT INTO t_book_store (vc_name, n_num)VALUES ('B', '120');INSERT INTO t_book_store (vc_name, n_num)VALUES ('C', '50');CREATE TABLE t_book_sell ( vc_name varchar(20), n_sellnum int);INSERT INTO t_book_sell (vc_name, n_sellnum)VALUES ('A', '10');INSERT INTO t_book_sell (vc_name, n_sellnum)VALUES ('A', '20');INSERT INTO t_book_sell (vc_name, n_sellnum)VALUES ('B', '10');INSERT INTO t_book_sell (vc_name, n_sellnum)VALUES ('B', '20');INSERT INTO t_book_sell (vc_name, n_sellnum)VALUES ('B', '30');
NO1.3 参考答案
SELECT t.vc_name, t.n_num, COALESCE(t2.sum_sell_num, 0) sum_sell_num, (t.n_num - COALESCE(t2.sum_sell_num, 0)) AS numFROM t_book_store t LEFT JOIN (SELECT vc_name, SUM(n_sellnum) sum_sell_num FROM t_book_sell GROUP BY vc_name) t2 ON t.vc_name = t2.vc_name
NO1.4 补充说明
在本题案例中,产品C是没有销售记录的,所以我们用库存表去左关联销量表时,需要处理C产品销量为null的情况。这里我们用到了 COALESE(field,0)函数,这个函数会在fiele 为null时将值修改为后面指定的值0,当然还有 ifnull 和 is_null 函数有类似功能。
题目2
NO2.1 问题描述
查询有多个员工的工资不低于2000的部门编号(一个部门的员工大于2000的人数有两个或两个以上就查询出来)
NO2.2 数据准备
CREATE TABLE t_payroll_sheet ( vc_dep_id varchar(20), vc_name varchar(20), n_num int);INSERT INTO t_payroll_sheetVALUES ('1', 'aa', 2000);INSERT INTO t_payroll_sheetVALUES ('2', 'bb', 1200);INSERT INTO t_payroll_sheetVALUES ('1', 'cc', 2100);INSERT INTO t_payroll_sheetVALUES ('2', 'dd', 1800);INSERT INTO t_payroll_sheetVALUES ('1', 'ee', 2100);INSERT INTO t_payroll_sheetVALUES ('3', 'ff', 8000);INSERT INTO t_payroll_sheetVALUES ('2', 'gg', 2200);INSERT INTO t_payroll_sheetVALUES ('3', 'hh', 4500);
NO2.3 参考答案
SELECT vc_dep_id, COUNT(1) AS count_numFROM t_payroll_sheetWHERE n_num > 2000GROUP BY vc_dep_idHAVING count_num > 1
NO2.4 补充说明
where 针对每一条记录筛选,而 having 对分组后的结果集进行筛选。
问题3
NO3.1 问题描述
删除重复数据(id没有重复)
NO3.2 数据准备
CREATE TABLE t_records ( vc_id int, vc_name varchar(20), vc_age varchar(20));INSERT INTO t_recordsVALUES (1, 'a', '20');INSERT INTO t_recordsVALUES (2, 'a', '20');INSERT INTO t_recordsVALUES (3, 'b', '25');INSERT INTO t_recordsVALUES (4, 'c', '27');INSERT INTO t_recordsVALUES (5, 'd', '24');INSERT INTO t_recordsVALUES (6, 'd', '24');INSERT INTO t_recordsVALUES (7, 'd', '24');INSERT INTO t_recordsVALUES (8, 'e', '28');
NO3.3 易错SQL
DELETE FROM t_records WHERE vc_id NOT IN (SELECT vc_id FROM t_records GROUP BY vc_name , vc_age HAVING MAX(vc_id));
提示错误信息
You can't specify target table 't_records' for update in FROM clause
在同一语句中,不能先select出同一表中的某些值,再update这个表。也就是说将select出的结果再通过中间表select一遍,这样就规避了错误。注意,这个问题只出现于MySQL , Oracle不会出现此问题。
NO3.4 参考答案
DELETE FROM t_records WHERE vc_id NOT IN (SELECT t.vc_id FROM (SELECT MAX(vc_id) FROM t_records GROUP BY vc_name , vc_age HAVING MAX(vc_id)) t);
NO3.5 补充说明
如果id也重复,变通找到不同的字段,oracle中有现成的一个字段:oracle的行号 rowid,所以可以很快得到删除语句。如果其他数据库没有这种字段,则可以自己新建一个临时行号字段:rowid,此时删除也可以继续进行了。
题目4
NO4.1 问题描述
找出忘记填写性别的员工
NO.4.2 数据准备
CREATE TABLE t_emp ( vc_id varchar(20), vc_name varchar(20));CREATE TABLE t_sex ( vc_id varchar(20), vc_sex varchar(20));INSERT INTO t_empVALUES ('1', 'a');INSERT INTO t_empVALUES ('2', 'b');INSERT INTO t_empVALUES ('3', 'c');INSERT INTO t_empVALUES ('4', 'd');INSERT INTO t_sexVALUES ('1', '男');INSERT INTO t_sexVALUES ('4', '女');INSERT INTO t_sexVALUES ('5', '男');
NO4.3 参考答案
用not in
SELECT *FROM t_empWHERE vc_id NOT IN (SELECT vc_id FROM t_sex)
用not exists
SELECT *FROM t_emp t1WHERE NOT EXISTS( SELECT * FROM t_sex t2 WHERE t1.vc_id = t2.vc_id)
用left join
SELECT t1.*FROM t_emp t1 LEFT JOIN t_sex t2 ON t1.vc_id = t2.vc_idWHERE t2.vc_sex IS NULL题目5
NO5.1 问题描述
算出部门2中得到奖金最多的员工姓名及奖金
NO5.2 数据准备
CREATE TABLE t_emp ( emp_no int, e_name VARCHAR(10), job VARCHAR(10), mgr int, hiredate DATE, sal int, comm int, deptno int);INSERT INTO t_empVALUES (1, '员工1', '员工', 3, NULL , 2000, 500, 1);INSERT INTO t_empVALUES (2, '员工2', '员工', 3, NULL , 2500, 500, 1);INSERT INTO t_empVALUES (3, '员工3', '经理', 5, NULL , 4000, 1000, 1);INSERT INTO t_empVALUES (4, '员工4', '财务', 5, NULL , 3500, 500, 1);INSERT INTO t_empVALUES (5, '员工5', '主任', NULL, NULL , 5000, 2000, 1);INSERT INTO t_empVALUES (6, '员工6', '员工', 8, NULL , 2100, 500, 2);INSERT INTO t_empVALUES (7, '员工7', '员工', 8, NULL , 2300, 500, 2);INSERT INTO t_empVALUES (8, '员工8', '经理', 10, NULL , 5500, 1500, 2);INSERT INTO t_empVALUES (9, '员工9', '财务', 10, NULL , 3000, 500, 2);INSERT INTO t_empVALUES (10, '员工10', '主任', NULL, NULL , 6000, 2500, 2);
NO5.3 易错SQL写法
SELECT e_name, MAX(comm) max_commFROM t_empWHERE deptno = 2;
该SQL由直观的题目要求得到,其实这是一条有语法错误的SQL,因为用到了聚合函数max,所以需要group by。e_name是取的第一条记录的员工姓名,而不是奖金最多的员工姓名,所以是错的。
NO5.4 参考答案
SELECT e_name, commFROM t_empWHERE comm = (SELECT MAX(comm) AS max_comm FROM t_emp WHERE deptno = 2);题目6
NO6.1 问题描述
行转列查询
NO6.2 数据准备
CREATE TABLE stu_score ( grade_id varchar(10) DEFAULT NULL, subject_name varchar(10) DEFAULT NULL, max_score int(10) DEFAULT NULL);INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)VALUES ('1', '语文', 98);INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)VALUES ('2', '数学', 95);INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)VALUES ('2', '政治', 87);INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)VALUES ('5', '语文', 97);INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)VALUES ('5', '数学', 100);INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)VALUES ('5', '政治', 92);
NO6.3 参考答案
SELECT CASE grade_id WHEN 1 THEN '一年级' WHEN 2 THEN '二年级' WHEN 5 THEN '五年级' END AS '年级', MAX(IFNULL(CASE subject_name WHEN '语文' THEN max_score END, 0)) AS '语文' , MAX(IFNULL(CASE subject_name WHEN '数学' THEN max_score END, 0)) AS '数学' , MAX(IFNULL(CASE subject_name WHEN '政治' THEN max_score END, 0)) AS '政治'FROM stu_scoreGROUP BY grade_id;
题目7
NO7.1 问题描述
行转列字段合并
NO7.2 数据准备
CREATE TABLE t_class ( n_class varchar(20), n_time varchar(20), n_stu_name varchar(20));INSERT INTO t_class (n_class, n_time,n_stu_name)VALUES ('101', '2019-01-01 12:12:12','张三');INSERT INTO t_class (n_class, n_time,n_stu_name)VALUES ('101', '2019-01-01 11:12:12','李四');INSERT INTO t_class (n_class, n_time,n_stu_name)VALUES ('101', '2019-01-01 14:12:12','王五');INSERT INTO t_class (n_class, n_time,n_stu_name)VALUES ('104', '2019-01-01 15:12:12','李白');
NO7.3 参考答案
SELECT n_class, GROUP_CONCAT(n_stu_name) AS stuNameFROM t_classGROUP BY n_class;
题目8
NO8.1 问题描述
max函数去重,取一条记录,只有ID不同。
NO8.2 数据准备
CREATE TABLE t_records ( vc_id int, vc_name varchar(20), vc_age varchar(20));INSERT INTO t_recordsVALUES (1, 'a', '20');INSERT INTO t_recordsVALUES (2, 'a', '20');INSERT INTO t_recordsVALUES (3, 'b', '25');INSERT INTO t_recordsVALUES (4, 'c', '27');INSERT INTO t_recordsVALUES (5, 'd', '24');INSERT INTO t_recordsVALUES (6, 'd', '24');INSERT INTO t_recordsVALUES (7, 'd', '24');INSERT INTO t_recordsVALUES (8, 'e', '28');
NO8.3 参考答案
SELECT MAX(vc_id) vc_id, vc_name, vc_ageFROM t_recordsGROUP BY vc_name , vc_age;
题目9
NO9.1 问题描述
max函数去重,取一条记录。
NO9.2 数据准备
CREATE TABLE t_user_bank ( vc_id int, vc_user_id varchar(20), vc_name varchar(20), vc_bank varchar(20));INSERT INTO t_user_bankVALUES (1, 'A001','AC', '浦发银行');INSERT INTO t_user_bankVALUES (2, 'A001','AC', '农业银行');INSERT INTO t_user_bankVALUES (3, 'A002','Alan Chen', '邮政');INSERT INTO t_user_bankVALUES (4, 'A002','Alan Chen', '招商银行');INSERT INTO t_user_bankVALUES (5, 'A003','Kyra', '建设银行');
NO9.3 参考答案
一个用户有多个银行账号,但我们只想取用户的一条银行账号。
SELECT MAX(vc_id) vc_id, vc_user_id, MAX(vc_name) vc_name, MAX(vc_bank) vc_bankFROM t_user_bankGROUP BY vc_user_id;
题目10
NO10.1 问题描述
统计历史累计数据,每天的数据需要进行累加。例如:
5⽉1号 = 5⽉1号的数据
5⽉2号 = 5⽉1号+5⽉2号
5⽉3号 =5⽉1号+5⽉2号+5⽉3号
…
如果当天没有新增数据则跳过统计
NO10.2 数据准备
CREATE TABLE t_login_log ( id INT, user_id INT, login_time DATETIME);INSERT INTO t_login_logVALUES (1, '1001','2023-01-01 12:12:23');INSERT INTO t_login_logVALUES (2, '1001','2023-01-01 14:12:23');INSERT INTO t_login_logVALUES (3, '1002','2023-01-01 10:12:23');INSERT INTO t_login_logVALUES (4, '1001','2023-01-02 11:12:23');INSERT INTO t_login_logVALUES (5, '1002','2023-01-02 09:12:23');INSERT INTO t_login_logVALUES (6, '1001','2023-01-04 15:12:23');INSERT INTO t_login_logVALUES (7, '1002','2023-01-04 17:12:23');
NO10.3 参考答案
SELECT c.tart_dat AS item, COUNT(c.tart_dat) AS total_countFROM (SELECT l_tab.tart_dat, r_tab.login_time FROM (SELECT DATE_FORMAT(t.login_time, '%Y-%m-%d') AS tart_dat FROM t_login_log t GROUP BY tart_dat) l_tab INNER JOIN (SELECT DATE_FORMAT(login_time, '%Y-%m-%d') AS login_time FROM t_login_log) r_tab ON l_tab.tart_dat >= r_tab.login_time) cGROUP BY c.tart_datORDER BY c.tart_dat
第四节 系列专题系列一:case when系列专题
NO1.1 数据准备
CREATE TABLE t_user ( id INT, ch_name VARCHAR(20), en_name VARCHAR(20), sex INT, phone VARCHAR(20), age int);INSERT INTO t_userVALUES (1, '张三', 'AC',0,'1331781016x',17);INSERT INTO t_userVALUES (2, '', 'AlanChen',1,'1341781016x',9);INSERT INTO t_userVALUES (3, '李四', 'Kyra',2,'1301781016x',28);INSERT INTO t_userVALUES (4, '王五', 'Tom',1,'1332981014x',62);
NO1.2 数据统计
SELECT COUNT(*) '总用户数', COUNT(CASE WHEN sex = 0 THEN sex END) AS '未知', COUNT(CASE WHEN sex = 1 THEN sex END) AS '男', COUNT(CASE WHEN sex = 2 THEN sex END) AS '女'FROM t_user;
NO1.3 数据转换
select id, ch_name, en_name, sex, case sex when 0 then '未知' when 1 then '男' when 2 then '女' end as sex_namefrom t_user;
NO1.4 数据转换用like
select id, ch_name, en_name, phone, case when phone like '133%' then '电信' when phone like '134%' then '移动' when phone like '130%' then '联通' end as phone_channelfrom t_user;
NO1.4 数据转换用比较运算
select id, ch_name, en_name, age, case when age<18 then '未成年人' when age>=18 and age < 60 then '青年' when age>=60 then '老年' else '其他' end as age_typefrom t_user;
NO1.4 选取字段
SELECT id, CASE WHEN ch_name IS NOT NULL AND ch_name != '' THEN ch_name ELSE en_name END AS user_nameFROM t_user;
系列2:查询统计系列专题
NO2.1 数据准备
-- 学生表CREATE TABLE student ( sid varchar(10), sName varchar(20), sAge varchar(200), sSex varchar(10) DEFAULT NULL, PRIMARY KEY (sid));-- 课程表CREATE TABLE course ( cid varchar(10) NOT NULL, cName varchar(10) DEFAULT NULL, tid int(20) DEFAULT NULL, PRIMARY KEY (cid));-- 成绩表CREATE TABLE sc ( sid varchar(10) DEFAULT NULL, cid varchar(10) DEFAULT NULL, score int(10) DEFAULT NULL);-- 教师表CREATE TABLE teacher ( tid int(10) DEFAULT NULL, tName varchar(10) DEFAULT NULL);INSERT INTO teacher (tid, tName)VALUES (1, '李老师'), (2, '何以琛'), (3, '叶平');INSERT INTO student (sid, sName, sAge, sSex)VALUES ('1001', '张三丰', '1980-10-12 23:12:36', '男'), ('1002', '张无极', '1995-10-12 23:12:36', '男'), ('1003', '李奎', '1992-10-12 23:12:36', '女'), ('1004', '李元宝', '1980-10-12 23:12:36', '女'), ('1005', '李世明', '1981-10-12 23:12:36', '男'), ('1006', '赵六', '1986-10-12 23:12:36', '男'), ('1007', '田七', '1981-10-12 23:12:36', '女');INSERT INTO sc (sid, cid, score)VALUES ('1001', '001', 80), ('1001', '002', 60), ('1001', '003', 75), ('1002', '001', 85), ('1002', '002', 70), ('1003', '004', 100), ('1003', '001', 90), ('1003', '002', 55), ('1004', '002', 65), ('1004', '003', 60);INSERT INTO course (cid, cName, did)VALUES ('001', '企业管理', 3), ('002', '马克思', 3), ('003', 'UML', 2), ('004', '数据库', 1), ('005', '英语', 1);
NO2.2 查询“001”课程比“002”课程成绩高的所有学生的学号
SELECT t1.*, t2.*FROM sc t1, sc t2WHERE t1.sid = t2.sid AND t1.cid = '001' AND t2.cid = '002' AND t1.score > t2.score;
NO2.3 查询平均成绩大于60分的同学的学号和平均成绩
方式一,自己算平均数
SELECT sid, SUM(score) / COUNT(1) AS averageFROM scGROUP BY sidHAVING average > 60;
方式二,用平均数函数
SELECT sid, AVG(score) AS averageFROM scGROUP BY sidHAVING average > 60;
NO2.4 查询所有同学的学号、姓名、选课数、总成绩
SELECT t2.sid, t2.sName, t1.score_count, t1.score_sumFROM (SELECT sid, COUNT(1) AS score_count, SUM(score) AS score_sum FROM sc GROUP BY sid) t1, student t2WHERE t1.sid = t2.sid;
NO2.5 查询姓“李”的老师的个数
SELECT COUNT(1)FROM teacherWHERE tName LIKE '李%';
NO2.6 查询没学过“叶平”老师课的同学的学号、姓名
方式一:两表关联+ group by
select sid, sNamefrom studentwhere sid not in ( select sid from ( select sid from sc where cid in ( select t1.cid from course t1, teacher t2 where t1.tid = t2.tid and t2.tName = '叶平' ) group by sid ) t);
方式二:三表关联+ distinct
SELECT t.sid, t.sNameFROM student tWHERE t.sid NOT IN (SELECT DISTINCT sid FROM sc t1, course t2, teacher t3 WHERE t1.cid = t2.cid AND t2.tid = t3.tid AND t3.tName = '叶平')
NO2.7 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
易错SQL写法
SELECT t1.sid, t1.sNameFROM student t1, (SELECT DISTINCT sid AS sid FROM sc WHERE cid IN ('001' , '002')) t2WHERE t1.sid = t2.sid;
in是“或”的意思,这个查的是:学过“001” 或者 学过编号“002”课程的同学的学号、姓名
参考答案
SELECT t3.sid, t3.sNameFROM sc t1, sc t2, student t3WHERE t1.sid = t2.sid AND t2.sid = t3.sid AND t1.cid = '001' AND t2.cid = '002'
NO2.8 查询所有课程成绩小于80分的同学的学号、姓名
SELECT t1.sid, t1.sNameFROM student t1, (SELECT DISTINCT sid AS sid FROM sc WHERE sid NOT IN (SELECT DISTINCT sid AS sid FROM sc WHERE score >= 80)) t2WHERE t1.sid = t2.sid;