Mysql基础 —— 数据基础操作

一、数据基础操作

1. 插入数据

INSERT INTO `table_name` (`field1`,`field2`,……) VALUES ("value1", "value2",……);

eg：INSERT INTO `user` (`name`, `sex`, `age`) VALUES ("王三", 1, 24);

2. 修改数据

UPDATE `table_name` SET `field1`='new-value1', `field2`='new-value2' [WHERE Clause];

eg：UPDATE `user` SET `name`='李二', `sex`='女' WHERE `id`=2;

3. 删除数据

DELETE FROM `table_name` [WHERE Clause];

eg：DELETE FROM `user` WHERE id=12;

4. 普通查询数据

SELECT `field1`,`field2`,`field3`,…… FROM `table_name` [WHERE Clause];

eg：
    SELECT * FROM `user`;
    SELECT `name`,`sex`,`age` FROM `user` WHERE `sex`=1 AND `age`>20;

 补充说明：
    1. 多个 “与关系” 条件用 AND 连接；
    2. 多个 “或关系” 条件用 OR 连接;
    3. 条件子句用 "="、">"、"<"、"!=" 。

5. 分页查询

SELECT `field1`,`field2` FROM `table_name` LIMIT offset,rows;

说明：
    1. offset：开启查询的行数索引；
    2. rows：每页显示数量。

eg：
    SELECT * FROM `table_name` LIMIT 0,10;        // 从第1行开始获取10条数据
    SELECT * FROM `table_name` LIMIT 10,10;       // 从第11行开始获取10条数据

6. 查询排序

SELECT `field1`,`field2`,…… FROM `table_name` ORDER BY `order_field1`[,`order_field2`] [ASC | DESC];

说明：
    1. ASC：正序，从小到大排序；
    2. DESC：逆序，从大到小排序。

eg：
    SELECT * FROM `user` ORDER BY `create_time` DESC;            // 按照添加时间逆向排序
    SELECT * FROM `user` ORDER BY `age` ASC,`create_time` DESC;  // 年龄正序、添加时间逆序获取数据

7. 模糊查询

SELECT `field1`,`field2`,…… FROM `table_name` WHERE `like_field` LIKE 'value';

说明：'value' 是 LIKE 进行匹配的值，可以用符号 % 、_ 来进行模糊匹配，匹配符号主要有以下两种：
      1. % ：匹配任意个数、任意值；
      2. _ ：匹配一个、任意值。

如下示例：
      1. '%a'    // 以a结尾的数据
      2. 'a%'    // 以a开头的数据
      3. '%a%'   // 含有a的数据
      4. '_a_'   // 三位且中间字母是a的
      5. '_a'    // 两位且结尾字母是a的
      6. 'a_'    // 两位且开头字母是a的

二、Mysql复杂查询

1. 连表查询（JOIN）

（1）INNER JOIN，内连接查询，只获取两个表中字段匹配关系的记录，如下示例：

// JOIN 默认为INNER，可以不写
SELECT  `a`.name AS user_name,`b`.name AS hobby_name FROM `user` a INNER JOIN `hobby` b ON `user`.`id`=`hobby`.`user_id`; 

（2）LEFT JOIN，左连接查询，获取左表所有记录，即使右表没有对应匹配的记录，如下示例：

SELECT  `a`.name AS user_name,`b`.name AS hobby_name FROM `user` a LEFT JOIN `hobby` b ON `user`.`id`=`hobby`.`user_id`; 

（3）RIGHT JOIN，右连接查询，与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录，如下示例：

SELECT  `a`.name AS user_name,`b`.name AS hobby_name FROM `user` a RIGHT JOIN `hobby` b ON `user`.`id`=`hobby`.`user_id`; 

2. 连接多个查询结果（UNION）

  操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中，多个 SELECT 语句会删除重复的数据。

SELECT `field1`,`field2`,…… FROM tables [WHERE conditions]
UNION [ALL | DISTINCT]
SELECT `field1`,`field2`,…… FROM tables [WHERE conditions];

说明：
    1. DISTINCT: 可选，删除结果集中重复的数据。
   （默认情况下 UNION 操作符已经删除了重复数据，所以加上 DISTINCT 修饰符对结果没影响）
    2. ALL：可选，返回所有结果集，包含重复数据。

注意：
    1. UNION 主要用户查询多个表时，把多个表查询相同的字段结果合并且去重；
    2. 如果两个或多个查询的结果字段的数量不一致，会导致报错；
    3. 如果查询的多个字段顺序不一致会按照第一个查询的字段排序进行结果展示；
    4. UNION查询出来的结果还可以继续用ORDER BY 进行排序。

3. 分组查询（GROUP BY）

  根据一个或多个列对结果集进行分组搜索。

格式：
    SELECT COUNT(*)
    FROM `table_name` 
    [WHERE ……] 
    GROUP BY `group_field`
    [HAVING ……];

说明：
    1. 在分组的列上我们可以使用 COUNT、SUM、AVG等函数获取需要统计的值；
    2. 如果是数据表中的字段条件，用WHERE搜索；如果是统计别名字段条件，用HAVING搜索。


订单统计示例：字段：uid,account,money,time（时间戳）

    1. 凌晨00:00从订单表统计（order）统计昨日消费10元以上的付费用户账号+每个用户消费总金额+每个用户消费次数：
        SELECT account,SUM(money) total,COUNT(*) num FROM `order` WHERE FROM_UNIXTIME(time, '%Y-%m-%d') = DATE_SUB(curdate(), INTERVAL 1 DAY) GROUP BY `uid` HAVING total>10;

    2. 凌晨00:00从订单表统计（order）统计昨日消费10元以上的付费用户总数+总付费金额：
       SELECT COUNT(*) num, SUM(total) total FROM (SELECT SUM(money) total FROM `order` WHERE FROM_UNIXTIME(time, '%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 1 DAY) GROUP BY `uid` HAVING total>10) a; 
    

三、Mysql子句查询

1. WHERE子句查询：把WHERE子句查询的结果作为外层查询的比较条件。

示例商品数据表(goods)字段：id,name,price,create_time,update_time,type

1. 普通WHERE子查询

SELECT * FROM `goods` WHERE `name` = (SELECT name FROM `goods` WHERE type=1 LIMIT 1);
SELECT * FROM `goods` WHERE `price` < (SELECT MAX(price) FROM `goods`);
SELECT * FROM `goods` WHERE `price` > (SELECT MIN(price) FROM `goods`);

备注：普通where子查询，子句查询出来的的值必须为标量，不能是有多条数据的值。

2. ANY / SOME 子查询

(1) <、<= ：小于搜索出来的子句字段中的最大值：
SELECT * FROM `goods` WHERE `price` < ANY(SELECT `price` FROM `goods` WHERE `tyep`=1);
(2) >、>= ：大于搜索出来的子句字段中的最小值：
SELECT * FROM `goods` WHERE `price` > ANY(SELECT `price` FROM `goods` WHERE `tyep`=1);
(3) = ：等于搜索出来子句字段中的任意值：
SELECT * FROM `goods` WHERE `type` = ANY(SELECT `type` FROM `goods` WHERE `price`>100);
(4) <> ：不等于搜索出来子句字段中的任意值：
SELECT * FROM `goods` WHERE `type` <> ANY(SELECT `type` FROM `goods` WHERE `price`>100);

3. ALL 子查询

(1) <、<= ：小于搜索出来的子句字段中的最小值：
SELECT * FROM `goods` WHERE `price` < ALL(SELECT `price` FROM `goods` WHERE type=2);
(2) >、>= ：大于搜索出来的子句字段中的最大值：
SELECT * FROM `goods` WHERE `price` > ALL(SELECT `price` FROM `goods` WHERE tyep=2);
(3) =, <> ：ALL子查询不应该用于 等于 或 不等于 操作。

4. IN / NOT IN 子查询

(1) IN 子查询
SELECT * FROM `goods` WHERE `type` IN (SELECT `type` FROM `goods` WHERE `price`>100);

备注：In子查询 等价于 (= ANY) 子查询操作，如上查询可写为：
SELECT * FROM `goods` WHERE `type` = ANY(SELECT `type` FROM `goods` WHERE `price`>100);

(1) NOT IN 子查询
SELECT * FROM `goods` WHERE `type` NOT IN (SELECT `type` FROM `goods` WHERE `price`>100);

备注：NOT IN 子查询 等价于 (<> ANY) 子查询操作，如上查询可写为：
SELECT * FROM `goods` WHERE `type` <> ANY(SELECT `type` FROM `goods` WHERE `price`>100);

2. FROM子句查询：把FROM子句查询结果当成临时表，供外层sql再次查询；查询结果集可以当成数据表，但临时表必须使用一个别名。

// 从订单表（uid,account,money,time）统计昨日消费用户的总数量
SELECT COUNT(*) FROM (SELECT id WHERE FROM_UNIXTIME(time, '%Y-%m-%d') = DATE_SUB(curdate(),INTERVAL 1 DAY) GROUP BY `uid`) A;

3. Exists子查询：把外层sql的结果，拿到内层sql去测试，如果内层的sql成立，则该行取出，内层查询是exists后的查询。

// 搜索发过评论的用户信息
SELECT u.`account`,u.`nickname` FROM `user` u WHERE EXISTS (SELECT 1 FROM `comment` c WHERE c.uid=u.id);

 

参考文档：https://www.cnblogs.com/chiangchou/p/mysql-3.html#_label0_0