MySQL综合应用与要点归纳-CSDN博客

本文链接：https://blog.csdn.net/sinat_30844883/article/details/140730343

内容导读

MySQL多种查询

行列转换查询

MySQL要点归纳

一、MySQL多种查询

mysql 有四种常见查询：等值查询、子查询、联合查询、关联查询

1、等值查询，通过等值条件查询一个或几个表的数据

（1）通过 id 相等,查询两个表

mysql> select * from uu,user where uu.id=user.id;
+-----+----------+----------+-----+-----+----------+----------+-----+-----------+
| id | username | password | age | id | username | password | age | nickname |
+-----+----------+----------+-----+-----+----------+----------+-----+-----------+
| 106 | corday | 4567 | 21 | 106 | corday | 1111 | 21 | corday123 |
+-----+----------+----------+-----+-----+----------+----------+-----+-----------+
1 row in set (0.03 sec)

（2）请用一条 sql 语句查出部门名称为"销售部"的员工的最高工资

mysql> select * from emp;
+-------+--------+--------+------+
| eno | ename | salary | dno |
+-------+--------+--------+------+
| 10000 | tom  | 1500 | 1001 |
| 10001 | bean | 1800 | 1001 |
| 10002 | mary | 2500 | 1002 |
| 10003 | tina | 2300 | 1002 |
| 10004 | xm   | 1900 | 1003 |
| 10005 | coco | 1700 | 1003 |
| 10006 | corday | 2100 | 1001 |
+-------+--------+--------+------+
7 rows in set (0.02 sec)
mysql> select * from dept;
+------+------------+---------+
| dno | dname | manager |
+------+------------+---------+
| 1001 | 销售部 | xs100 |
| 1002 | 公关部 | gg100 |
| 1003 | 技术部 | js100 |
| 1004 | 人力资源部 | rl100 |
+------+------------+---------+
4 rows in set (0.03 sec)
# 此处员工姓名不准确
mysql> select d.dname,e.ename,max(e.salary) salary from dept d,emp e where d.dno=e.dno and d.dname='销售部';  
+--------+-------+--------+
| dname | ename | salary |
+--------+-------+--------+
| 销售部 | tom | 2100 |
+--------+-------+--------+
1 row in set (0.02 sec)

2、子查询，一个查询是另一个查询的条件

说明：继续以上边的查询为例

优化 1：查询销售部门最高工资人员信息:

select e.ename,d.dname,e.salary from emp e,dept d where e.dno=d.dno and d.dname='销售
部' order by salary desc limit 0,1;

# 子查询之后再设置条件一定要用having来设置

优化 2：查询销售部门最高工资人员信息:

mysql> select e.ename,d.dname,e.salary from emp e join dept d on e.dno=d.dno
where(e.dno,e.salary) in(select dno,max(salary) from emp group by dno having dno=1001);
# 或
mysql> select d.dno,d.dname,e.ename,e.salary from emp e join dept d on e.dno=d.dno
where(e.salary)in(select max(salary) from emp group by dno having d.dname='销售部');
+------+--------+--------+--------+
| dno | dname | ename | salary |
+------+--------+--------+--------+
| 1001 | 销售部 | corday | 2100 |
+------+--------+--------+--------+
1 row in set (0.03 sec)

优化 3：查询每个部门的最高工资人员信息:

select e.ename,d.dname,e.salary from emp e join dept d on e.dno=d.dno
where(e.dno,e.salary) in(select dno,max(salary) from emp group by dno);

（1）查询数学成绩最大的男生与女生的信息

mysql> select * from test where math in(select max(math) from test group by sex);
+------+-------+-----+------+---------+-------+
| id | name | sex | math | english | music |
+------+-------+-----+------+---------+-------+
| 1005 | nick  | 男 | 98 | 81 | 79 |
| 1008 | kitty | 女 | 99 | 95 | 98 |
+------+-------+-----+------+---------+-------+

说明： 分组与排序都有效,务必先分组后排序

（2）查询男女学生数学的平均成绩

mysql> select name,sex, avg(math) from test group by sex;
+------+-----+-----------+
| name | sex | avg(math) |
+------+-----+-----------+
| mary | 女 | 89.5000 |
| tom  | 男 | 85.2500 |
+------+-----+-----------+

（3）查询每个学生三科的平均成绩

mysql> select *,(math+english+music)/3 as avg from test;
+------+--------+-----+------+---------+-------+---------+
| id | name | sex | math | english | music | avg |
+------+--------+-----+------+---------+-------+---------+
| 1001 | tom  | 男 | 89 | 91 | 86 | 88.6667 |
| 1002 | bean | 男 | 86 | 92 | 90 | 89.3333 |
| 1003 | mary | 女 | 91 | 86 | 88 | 88.3333 |
| 1004 | coco | 女 | 82 | 98 | 91 | 90.3333 |
+------+--------+-----+------+---------+-------+---------+

3、联合查询(union)，连接两个以上的 select 语句的结果组合到一个结果集中，会删除重复的数据,
两个表的查询的字段数相同即可。

mysql> select * from u union select * from uu;
+-----+----------+----------+-----+
| id | username | password | age |
+-----+----------+----------+-----+
| 101 | mary | 1234 | 19 |
| 102 | bean | 1234 | 20 |
| 103 | tom  | 1234 | 18 |
| 106 | corday| 4567 | 21 |
| 107 | 李帅  | 1234 | 25 |
+-----+----------+----------+-----+

4、关联查询，也称为连接，在两个或多个表个查询

常用的有四种：

内联查询：inner join... on、左关联查询： left join...on

右关联查询：right join...on、全联查询：full outer join...on

例：建两张表

T1 表结构(用户名,密码)

user id(int) username varchar(20) password varchar(20)
       1       jack                jackpwd
       2       owen                owenpwd

T2 表结构(用户名,密码)

user id(int) jifen varchar(20) dengji varchar(20)
      1       20                  3
      3       50                  6

（1）内联查询(inner join)，可以省略 inner

把用户信息、积分、等级都列出来

select * from T1 inner join T2 on T1.userid = T2.userid

（2）左联查询(left outer join)，可以省略 outer

显示左表 T1 中的所有行，并把右表 T2 中符合条件加到左表 T1 中，右表 T2 中不符合条件，就不用加入结果表中，并且 NULL 表示。

select * from T1 left outer join T2 on T1.userid = T2.userid;

（3）右联查询(right outer join)，可以省略 outer

显示右表 T2 中的所有行，并把左表 T1 中符合条件加到右表 T2 中，左表 T1 中不符合条件,就不用加入结果表中，并且 NULL 表示。

select * from T1 right outer join T2 on T1.userid = T2.userid;

（4）全联查询(full outer join)，可以省略 outer

显示左表 T1、右表 T2 两边中的所有行,即把左联结果表 + 右联结果表组合在一起,然后过滤掉重
复的。

select * from T1 full outer join T2 on T1.userid = T2.userid;

二、行列转换查询

1、mysql 行转列

如图按行显示：

建表 test1：

CREATE TABLE `test1` (
`id` int NOT NULL,
`name` varchar(8) DEFAULT NULL,
`course` varchar(6) DEFAULT NULL,
`score` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

添加数据：

INSERT INTO `test1`(id,name,course,score) VALUES
('1', '张三', '语文', '87'),
('2', '张三', '数学', '97'),
('3', '张三', '英语', '87'),
('4', '李四', '语文', '99'),
('5', '李四', '数学', '96'),
('6', '李四', '英语', '89'),
('7', '王五', '语文', '90'),
('8', '王五', '数学', '92'),
('9', '王五', '英语', '94');

行转列 sql 实现：

select name,
max(case course when '语文' then score else 0 end)语文,
max(case course when '数学' then score else 0 end)数学,
max(case course when '英语' then score else 0 end)英语
from test1 group by name;

说明： 此处用之所以用 MAX 是为了将无数据的点设为 0，防止出现 NULL

行转列后的效果：

2、mysql 列转行

建表 test2，结构与内容如下：

列转行的 sql 实现：

select user_name,'语文' COURSE,CN_SCORE as SCORE from test2
union select user_name,'数学' COURSE,MATH_SCORE as SCORE from test2
union select user_name,'英语' COURSE,EN_SCORE as SCORE from test2 order by
user_name,COURSE;

列转行的效果：

三、MySQL要点归纳

1、MySQL数据库作为数据存储的介质为应用系统提供数据存储的服务，我们如何设计出合理的数据库、数据表以满足应用系统的数据存储需求呢？

2、案例分析

车库：是用来存放车辆的，车库都需要划分车位，如果不划分车位，车子杂乱无章的存放可能会导致车辆堵塞，同时也可能造成场地的浪费，好的车库的规划应该是有限的场地能够停放最多的车辆，同时方便每一辆车的出入。

我们需要设计合理的数据表，能够完成数据的存储，同时能够方便的提取应用系统所需的数据。