• MySQL高级查询

    1.1 GROUP BY分组使用

    　　1、Group By介绍

    　　　　　　1. GROUP BY 语句根据一个或多个列对结果集进行分组,在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
    　　　　　　2. 涉及到的操作符：GROUP BY，HAVING，ORDER BY，INNER JOIN，OUT JOIN，AS，UNION。

    　　2、Group基本使用

    CREATE TABLE  user  (
   id  int(11) NOT NULL,
   name  char(10) NOT NULL DEFAULT '',
   date  datetime NOT NULL,
   singin  tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY ( id )
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    创建表
    INSERT INTO user VALUES 
('1', '小明', '2016-04-22 15:25:33', '1'),
('2', '小王', '2016-04-20 15:25:47', '3'), 
('3', '小丽', '2016-04-19 15:26:02', '2'), 
('4', '小王', '2016-04-07 15:26:14', '4'),
('5', '小明', '2016-04-11 15:26:40', '4'), 
('6', '小明', '2016-04-04 15:26:54', '2');
    向表中插入数据
    mysql> # SELECT name, COUNT(*) FROM   user GROUP BY name;
+------+----------+
| name | COUNT(*) |
+------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+------+----------+
3 rows in set (0.01 sec)
    统计同名的数据有多少条：例1
    mysql># SELECT name, SUM(singin) as singin_count FROM  user GROUP BY name WITH ROLLUP;
+------+--------------+
| name | singin_count |
+------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL |           16 |
+------+--------------+
4 rows in set (0.00 sec)

# WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
# 按姓名进行分组，再统计每个人登录的次数，其中记录 NULL 表示所有人的登录次数。
    按姓名进行分组，再统计每个人登录的次数：例2 with rollup
    mysql># SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  user GROUP BY name WITH ROLLUP;
+------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+------------------------+--------------+
4 rows in set (0.00 sec)

#1、我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：
#2、select coalesce(a,b,c);
#3、参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。
    按姓名进行分组，再统计每个人登录的次数：例3 coalesce 来设置一个可以取代 NUll 的名称
    mysql> SELECT name, SUM(singin) as singin_count FROM  user GROUP BY name HAVING COUNT(*) >= 2;
+------+--------------+
| name | singin_count |
+------+--------------+
| 小明 |            7 |
| 小王 |            7 |
+------+--------------+
2 rows in set (0.00 sec)
    过滤分组HAVING：例4 查找登录次数大于2的所有用户
    #1、创建表
create table student(
id int auto_increment,
name char(32) not null,
score int(10) not null,
course char(32) not null,
primary key (id));

#2、插入数据
insert into student(name,score,course) values('zhangsan',88,'english');
insert into student(name,score,course) values('zhangsan',98,'math');
insert into student(name,score,course) values('lisi',88,'math');

#3、查询
mysql> select *,avg(score) from student group by name;
+----+----------+-------+---------+------------+
| id | name     | score | course  | avg(score) |
+----+----------+-------+---------+------------+
|  4 | lisi     |    88 | math    |    88.0000 |
|  1 | zhangsan |    88 | english |    93.0000 |
+----+----------+-------+---------+------------+
2 rows in set (0.00 sec)
    按学生分组求各科平均成绩：例5
    mysql># select *,count(name) from user where datediff(NOW(),date)>10 group by name having count(name)>=2;
+----+------+---------------------+--------+-------------+
| id | name | date                | singin | count(name) |
+----+------+---------------------+--------+-------------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |           3 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |           2 |
+----+------+---------------------+--------+-------------+
2 rows in set (0.00 sec)

# 原题：请查找商品表中最近30天至少有20天都有销售记录的商品
    10天前注册的用户中同名用户大于或等于2的数据：例6

    1.2 mysql中NOW(),CURDATE(),CURTIME()的使用

    mysql> SELECT NOW(),CURDATE(),CURTIME();
+---------------------+------------+-----------+
| NOW()               | CURDATE()  | CURTIME() |
+---------------------+------------+-----------+
| 2018-03-18 16:28:16 | 2018-03-18 | 16:28:16  |
+---------------------+------------+-----------+
1 row in set (0.00 sec)
    例1：NOW(),CURDATE(),CURTIME()作用
    #1、创建表
CREATE TABLE goods(
id int auto_increment,
name varchar(50) NOT NULL,
date datetime NOT NULL DEFAULT NOW(),
PRIMARY KEY (id)
);

#2、插入一条数据
mysql># INSERT INTO goods(name) VALUES ('tea');   
    
#3、验证时间字段会自动插入
mysql># select * from goods;
+----+------+---------------------+
| id | name | date                |
+----+------+---------------------+
|  1 | tea  | 2018-03-18 16:37:19 |
+----+------+---------------------+
1 row in set (0.00 sec)
    例2：在表中定义时间字段自动添加

    1.3 DATEDIFF() 函数

    mysql># SELECT DATEDIFF('2008-11-30','2008-11-22') AS DiffDate;
+----------+
| DiffDate |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)
    例1：返回两个日期之间的天数
    #1、创建表
CREATE TABLE goods(
id int auto_increment,
name varchar(50) NOT NULL,
date datetime NOT NULL DEFAULT NOW(),
PRIMARY KEY (id)
);

#2、插数据
INSERT INTO goods(name) VALUES ('tea1');
INSERT INTO goods(name) VALUES ('tea2');
INSERT INTO goods(name,date) VALUES ('tea3','2018-03-02 16:37:19');
INSERT INTO goods(name,date) VALUES ('tea4','2015-05-02 16:37:19');

#3、查询五天前的数据
mysql> select * from goods where datediff(NOW(),date)>5;
+----+------+---------------------+
| id | name | date                |
+----+------+---------------------+
|  3 | tea3 | 2018-03-02 16:37:19 |
|  4 | tea4 | 2015-05-02 16:37:19 |
+----+------+---------------------+
2 rows in set (0.00 sec)

#4、查询最近五天的新数据
mysql> select * from goods where datediff(NOW(),date)<5;
+----+------+---------------------+
| id | name | date                |
+----+------+---------------------+
|  1 | tea1 | 2018-03-18 16:56:42 |
|  2 | tea2 | 2018-03-18 16:56:43 |
+----+------+---------------------+
2 rows in set (0.00 sec)
    例2：查询最近5天新数据

    1.4 DATE_FORMAT() 以不同格式显示时间

    select  
DATE_FORMAT(NOW(),'%Y-%m-%d'),
DATE_FORMAT(NOW(),'%Y-%m-%d %H:%m:%s');

+-------------------------------+----------------------------------------+
| DATE_FORMAT(NOW(),'%Y-%m-%d') | DATE_FORMAT(NOW(),'%Y-%m-%d %H:%m:%s') |
+-------------------------------+----------------------------------------+
| 2018-03-18                    | 2018-03-18 17:03:02                    |
+-------------------------------+----------------------------------------+
1 row in set (0.00 sec)
    DATE_FORMAT基本使用

    1.5 MySQL 正则表达式

    　　1、MySQL中常用正则

    模式描述
    ^ 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ' ' 或 ' ' 之后的位置。
    $ 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ' ' 或 ' ' 之前的位置。
    . 匹配除 " " 之外的任何单个字符。要匹配包括 ' ' 在内的任何字符，请使用象 '[. ]' 的模式。
    [...] 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
    [^...] 负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
    p1|p2|p3 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。
    * 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
    + 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
    {n} n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
    {n,m} m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

     　　2、MySQL正则使用举例

    　　　　1、查找name字段中以'st'为开头的所有数据：
    　　　　　　　　mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

    　　　　2、查找name字段中以'ok'为结尾的所有数据：
    　　　　　　　　mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

    　　　　3、查找name字段中包含'mar'字符串的所有数据：
    　　　　　　　　mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

    　　　　4、查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：
    　　　　　　　　mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';
  • 相关阅读:
    [UVA1149]Dominating Patterns
    [HDOJ1358]Period
    [HDOJ3065]病毒侵袭持续中
    【RQNOJ356】myt的格斗
    【rqnoj39】 饮食问题
    【rqnoj28】[Stupid]愚蠢的宠物
    【rqnoj378】 约会计划
    layer弹框在实际项目中的一些应用
    layer弹框在实际项目中的一些应用
    移动开发之css3实现背景几种渐变效果
  • 原文地址：https://www.cnblogs.com/jiaxinzhu/p/12466832.html
Copyright © 2020-2023  润新知