MySQL一些花样招式

他说,这里似乎很多玩的,有没有数据库语句好玩的呢?然而随手写了几条,我觉得呢看看书再实践实践应该很不错的。


  • CASE WHEN 条件判断表达式
# 字段的值使用代号,节省内存
mysql> SELECT `name`,`age`,`sex` FROM `admin`;
+----------+-----+-----+
| name     | age | sex |
+----------+-----+-----+
| AlicFeng |  21 |   1 |
| Alice    |  20 |   0 |
+----------+-----+-----+

# 通过CASE WHEN条件判断获取目标值
mysql> SELECT `name`,`age`,CASE `sex` WHEN 1 THEN '男' WHEN 0 THEN '女' ELSE '不公开' END AS `sex` FROM `admin`;
+----------+-----+-----+
| name     | age | sex |
+----------+-----+-----+
| AlicFeng |  21 | 男  |
| Alice    |  20 | 女  |
+----------+-----+-----+
  • IFNULL 判断是否为空并处理,判断空值的处理,有允许空值的列是会自动移除索引的,因此不推荐使用。知道就好!
# 直接获取字段值,有些为空值
mysql> SELECT `name`,`message` FROM `admin`;
+----------+--------------------------------------------+
| name     | message                                    |
+----------+--------------------------------------------+
| AlicFeng | 价值源于技术,贡献源于分享。               |
| Alice    | NULL                                       |
+----------+--------------------------------------------+

# 查询数据,处理空值
mysql> SELECT `name`,IFNULL(`message`,"这人很懒,没有留下任何东西。") AS `message` FROM `admin`;
+----------+--------------------------------------------+
| name     | message                                    |
+----------+--------------------------------------------+
| AlicFeng | 价值源于技术,贡献源于分享。               |
| Alice    | 这人很懒,没有留下任何东西。                |
+----------+--------------------------------------------+
  • IF(EXP1,VALUE1,VALUE2) IF条件判断,EXP表达式成立的话值为VALUE1,否则为VALUE2
# default SELECT
mysql> SELECT `name`,`age` FROM `admin`;
+----------+-----+
| name     | age |
+----------+-----+
| AlicFeng |  21 |
| Alice    |  20 |
+----------+-----+

# 我要根据年龄来判断这个家伙是不是小屁孩
mysql> SELECT `name`,IF(`age`>20,"这个很很黑","小屁孩一个") AS `who` FROM `admin`;
+----------+-----------------+
| name     | who             |
+----------+-----------------+
| AlicFeng | 这个很很黑      |
| Alice    | 小屁孩一个      |
+----------+-----------------+
  • UNION 以及 UNION ALL 就是合并,这个没什么好玩的。但是注意查询的字段名必须相同,不管来自哪一个表, ALL的话就是不消除相同行。使用是尽量使用括号括起来,不然有LIMIT、ORDER BY会提示错误。
# UNION 
mysql> (SELECT `name` FROM `admin`) UNION (SELECT `name` FROM `user`);
+----------+
| name     |
+----------+
| AlicFeng |
| Alice    |
| 大傻     |
+----------+

# UNION ALL
mysql> (SELECT `name` FROM `admin`) UNION (ALL SELECT `name` FROM `user`);
+----------+
| name     |
+----------+
| AlicFeng |
| Alice    |
| 大傻     |
| AlicFeng |
+----------+
  • GROUP BY+SUM+COUNT+ORDER BY 这个没什么说的,经常会用到就熟悉了。分组+求和+计数+排序
mysql> SELECT `age`,COUNT(*) AS `number` FROM `admin` GROUP BY `age` ORDER BY `number` DESC;
+-----+--------+
| age | number |
+-----+--------+
|  21 |      2 |
|  20 |      1 |
+-----+--------+
  • WHEREHAVING联合使用 说明having的话肯定有ORDER BY,分组后再帅选
mysql> SELECT `age`,COUNT(*) FROM `admin` WHERE `age`>18 GROUP BY `age` HAVING COUNT(*) >=1;
+-----+----------+
| age | COUNT(*) |
+-----+----------+
|  20 |        1 |
|  21 |        2 |
+-----+----------+
  • FROM 这里说的是FROM子查询。举个例子好了,现在我要查询小学小孩子有两科以及以上不及格的同学的平均分成绩和名字。一步一步来!
# 首先我要查询出有两个科目不及格的同学
mysql> SELECT `name`,COUNT(*) AS `number_class` FROM `user` WHERE `score`<60 GROUP BY `name` HAVING `number_class`>=2;
+----------+--------------+
| name     | number_class |
+----------+--------------+
| AlicFeng |            2 |
| 大傻     |            2 |
+----------+--------------+

# 既然可以查询出名字了,我们只获取名字即可
mysql> SELECT `name` FROM (SELECT `name`,COUNT(*) AS `number_class` FROM `user` WHERE `score`<60 GROUP BY `name` HAVING `number_class`>=2) AS `user_t`;
+----------+
| name     |
+----------+
| AlicFeng |
| 大傻     |
+----------+

# 名字有了,那么就可以获得最后的结果了
mysql> SELECT `name`,AVG(`score`) FROM `user`  WHERE `name` IN (SELECT `name` FROM (SELECT `name`,COUNT(*) AS `number_class` FROM `user` WHERE `score`<60 GROUP BY `name` HAVING `number_class`>=2) AS `user_t`) GROUP BY `name`;
+----------+--------------+
| name     | AVG(`score`) |
+----------+--------------+
| AlicFeng |      59.6667 |
| 大傻     |      61.6667 |
+----------+--------------+

# PS:这里只是举个例子,正确做法根据学号,这里的数据表结构是这样的(冗余、数据类型不严谨)。
mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     | NULL    |                |
| sub   | varchar(10) | NO   |     | NULL    |                |
| score | int(11)     | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
  • WHERE WHERE子查询,一句话,内层查询的结果供外层使用。不多话,举个例子!
# 查询一个user表效绩最高的人在另一个表的名言
mysql> SELECT `name`,`message` FROM `admin` WHERE `name`=(SELECT `name` FROM (SELECT `name`,max(`score`) FROM `user`) AS `temp_t`);
+--------+---------+
| name   | message |
+--------+---------+
| 大傻   | 追求    |
+--------+---------+
# PS:可以使用连表查询,还可以分组查找。THIS IS DEMO
  • EXISTS 简单说明:外层查询的结果拿到内层查询,是否成立。
mysql> SELECT `name`,`message` FROM `admin` WHERE EXISTS(SELECT `name` FROM `user` WHERE `user`.`name`=`admin`.`name`);
+----------+--------------------------------------------+
| name     | message                                    |
+----------+--------------------------------------------+
| AlicFeng | 价值源于技术,贡献源于分享。               |
| 大傻     | 追求                                       |
+----------+--------------------------------------------+

一下操作,我们先打印一下数据表数据,便于观察

mysql> SELECT * FROM `admin`;
+----+----------+-----+-----+--------------------------------------------+
| id | name     | age | sex | message                                    |
+----+----------+-----+-----+--------------------------------------------+
|  1 | AlicFeng |  21 |   1 | 价值源于技术,贡献源于分享。               |
|  2 | Alice    |  20 |   0 | NULL                                       |
|  3 | 大傻     |  21 |   1 | 追求                                       |
+----+----------+-----+-----+--------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM `user`;
+--------+----------+---------+-------+
| id     | name     | sub     | score |
+--------+----------+---------+-------+
|      1 | 大傻     | chinese |    48 |
|      2 | AlicFeng | math    |    28 |
|      3 | 大傻     | math    |    38 |
|      4 | AlicFeng | english |    98 |
|      5 | AlicFeng | math    |    53 |
|      6 | 大傻     | english |    99 |
|      7 | 世界     | math    |    88 |
+--------+----------+---------+-------+
  • 简单连表查询 在简单的两个表连接查询中,使用WHEREJOIN ON查询结果是等效的。
# JOIN ON
mysql> SELECT `admin`.`name`,`admin`.`message`,`user`.`score` FROM `admin` JOIN `user` ON `admin`.`name`=`user`.`name`;
+----------+--------------------------------------------+-------+
| name     | message                                    | score |
+----------+--------------------------------------------+-------+
| 大傻     | 追求                                       |    48 |
| AlicFeng | 价值源于技术,贡献源于分享。               |    28 |
| 大傻     | 追求                                       |    38 |
| AlicFeng | 价值源于技术,贡献源于分享。               |    98 |
| AlicFeng | 价值源于技术,贡献源于分享。               |    53 |
| 大傻     | 追求                                       |    99 |
+----------+--------------------------------------------+-------+

# WHERE
mysql> SELECT `admin`.`name`,`admin`.`message`,`user`.`score` FROM `admin`, `user` WHERE `admin`.`name`=`user`.`name`;
+----------+--------------------------------------------+-------+
| name     | message                                    | score |
+----------+--------------------------------------------+-------+
| 大傻     | 追求                                       |    48 |
| AlicFeng | 价值源于技术,贡献源于分享。               |    28 |
| 大傻     | 追求                                       |    38 |
| AlicFeng | 价值源于技术,贡献源于分享。               |    98 |
| AlicFeng | 价值源于技术,贡献源于分享。               |    53 |
| 大傻     | 追求                                       |    99 |
+----------+--------------------------------------------+-------+
  • 左连接,推荐 特点:以左表为准,去右表找数据,如果没有匹配的数据,则以null补空位,输出结果数>=左表原元祖数
mysql> SELECT `admin`.`name`,`admin`.`message` FROM `admin` LEFT JOIN `user` ON `admin`.`name`=`user`.`name`;
+----------+--------------------------------------------+
| name     | message                                    |
+----------+--------------------------------------------+
| AlicFeng | 价值源于技术,贡献源于分享。               |
| AlicFeng | 价值源于技术,贡献源于分享。               |
| AlicFeng | 价值源于技术,贡献源于分享。               |
| Alice    | NULL                                       |
| 大傻     | 追求                                       |
| 大傻     | 追求                                       |
| 大傻     | 追求                                       |
+----------+--------------------------------------------+
  • 右连接 特点:以右表为准,去左表找数据,如果没有匹配的数据,则以null补空位,输出结果数>=右表原元祖数
mysql> SELECT `admin`.`name`,`admin`.`message` FROM `admin` RIGHT JOIN `user` ON `admin`.`name`=`user`.`name`;
+----------+--------------------------------------------+
| name     | message                                    |
+----------+--------------------------------------------+
| 大傻     | 追求                                       |
| AlicFeng | 价值源于技术,贡献源于分享。               |
| 大傻     | 追求                                       |
| AlicFeng | 价值源于技术,贡献源于分享。               |
| AlicFeng | 价值源于技术,贡献源于分享。               |
| 大傻     | 追求                                       |
| NULL     | NULL                                       |
+----------+--------------------------------------------+
  • 内连接 特点:左右连接的交集。其实与WHEREJOIN ON的查询结果一样。
mysql> SELECT `admin`.`name`,`admin`.`message` FROM `admin` INNER JOIN `user` ON `admin`.`name`=`user`.`name`;
+----------+--------------------------------------------+
| name     | message                                    |
+----------+--------------------------------------------+
| 大傻     | 追求                                       |
| AlicFeng | 价值源于技术,贡献源于分享。               |
| 大傻     | 追求                                       |
| AlicFeng | 价值源于技术,贡献源于分享。               |
| AlicFeng | 价值源于技术,贡献源于分享。               |
| 大傻     | 追求                                       |
+----------+--------------------------------------------+

  • 玩玩效率的查询 玩这个肯定需要海量的数据了,好了~搞一个sheel脚本插入海量的数据。
# 先来设置MySQL的最大连接数
mysql> set global max_connections=100000;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connections';
+-----------------+--------+
| Variable_name   | Value  |
+-----------------+--------+
| max_connections | 100000 |
+-----------------+--------+
1 row in set (0.00 sec)

shell

#!/bin/bash  
date
for i in `seq 1 100000`
do
{
    mysql -usamego -p!@#$%^&123 demo -e "insert into user(name,score,sub)   values('ALicFeng',78,'math');"  
    sleep 0.01
} &
done
wait
date
exit 0

这里的数据还是很少的,最后很多了,执行这个sh时,我眼睛时刻盯着系统温度等信息、手已经放在CTRL+C上面了,O(∩_∩)O哈哈~

待续... ...

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Grace development

电商系统设计之订单

用户交易将经历一段艰辛的历程,一般用户感觉不到,实际程序是经历了一段生死离别。具体付款流程如下

602
来自专栏Grace development

电商系统设计之商品 (下)

完成上述流程则是完成了一笔交易,经常网上购物的童鞋都懂这个。今天我们讲下从商品系统到交易系统和订单系统的存储过程及其设计上的应该注意的“坑”。

1371
来自专栏数据和云

元宵快乐:看SQL大师们用SQL绘制的团圆

题记:在多年以前,论坛活跃的时代,在ITPUB上你能看到各种新奇有趣的知识,及时新鲜的信息,出类拔萃的技巧,有很多让人多年以后还记忆犹新。 这个帖子让我忍不住在...

3047
来自专栏闻道于事

数据库 105道题目整理与吐血总结

第一波题目 drop table PRODUCT cascade constraints; create table PRODUCT ( id NUMBER n...

5049
来自专栏java学习

Oracle基础试题与答案!

表结构: create table tbEmp --职员表 ( eID number(7) primarykey, ...

29512
来自专栏影子

PostgreSQL=>递归查询

转载请注明源地址:http://www.cnblogs.com/funnyzpc/p/8232073.html

943
来自专栏影子

PostgreSQL=>递归查询

895
来自专栏杨建荣的学习笔记

特殊的物化视图刷新 (r4笔记第77天)

现在有一个需求,某个环境中存在两个用户,一个用户中存在物化视图,另一个用户中存在源表,根据业务的需要,需要做一种特别的物化视图刷新。 ? 物化视图用户中的物化...

3367
来自专栏Grace development

电商系统设计之用户系统

设计以以下为工具讲起 – PHP为开发语言 – 基于Laravel框架 – MySQL为数据存储

1625
来自专栏程序员宝库

电商系统设计之订单

用户交易将经历一段艰辛的历程,一般用户感觉不到,实际程序是经历了一段生死离别。具体付款流程如下:

1062

扫码关注云+社区