iMisty的技术栈

iMisty的技术栈

MySql基础之SELECT相关操作

MySql
893
2019-06-14

SELECT相关

WHERE条件会筛选出符合条件的记录

  1. 比较运算符：> ,>= ,< ,<=, !=, <>, <=>;

  2. IS [NOT] NULL 的形式检测字段是否为null或者不为null;

  3. 指定范围的方式 [NOT] BETWEEN... AND

  4. 指定集合的方式 [NOT] IN (值...)

  5. 逻辑运算 ,逻辑与AND，逻辑或 OR;

  6. 匹配字符 [NOT] LIKE ,% 匹配任意长度的字符 _ 匹配一个字符

    -- 测试where条件，条件的比较运算符;

    USE king;
    DESC user1;
    SELECT id,username,age FROM user1 WHERE id = 1;

    ALTER TABLE user1
        ADD userDesc VARCHAR(100);

    DESC user1;

    -- 更新ID <= 9 的用户userDesc = 'this is a test'
    UPDATE user1 SET userDesc = 'this is a test!' WHERE id <= 9;

    SELECT * FROM user1;

    -- 查询用户的个人描述为null 的用户信息

    SELECT id ,username,age,addr,userDesc FROM user1 WHERE userDesc = null ;-- 这种情况不可以查询出指定字段为null的记录
    SELECT id ,username,age,addr,userDesc FROM user1 WHERE userDesc <=> null ;-- 这种就可以正确查询，当然NUll也可以；
    SELECT id ,username,age,addr,userDesc FROM user1 WHERE userDesc IS NULL ;-- 这种关键字语句也是可以的，IS NOT NULL 取反

    -- 测试范围 BETWEEN... AND
    SELECT id,username,age,sex FROM user1
       WHERE age BETWEEN 18 and 30;-- 年龄在 18 - 30 之间

    SELECT id,username,age,salarry FROM  user1
      WHERE salarry BETWEEN 8000 AND 50000;  -- 薪水在 8000 - 50000 之间

    SELECT id,username,age,salarry FROM  user1
      WHERE salarry NOT BETWEEN 8000 AND 50000;-- 薪水不在 8000 - 50000之间

    -- 测试指定集合 IN
    SELECT id,username,age,sex FROM user1
      WHERE id IN(1,3,5,30,31);-- 查询出在括号范围内的记录,当然没有匹配的也没有关系

    -- 逻辑运算符
    SELECT id,username,age ,sex FROM user1
      WHERE sex = '男' AND age > 25;

    SELECT id,username,age,sex,addr FROM user1
      WHERE id > 5 AND age<30;
    SELECT id,username,age,sex,addr FROM user1
      WHERE id > 5 AND age<30 AND sex = '男';
    SELECT id,username,age,sex,addr FROM user1
      WHERE sex = '女' OR addr  = '武汉';

    -- 匹配字符配合通配符使用 测试模糊查询

    SELECT id,username,age,addr FROM user1
      WHERE username LIKE 'king'; -- 这里相当于等于，没有使用通配符
    SELECT id,username,age,addr FROM user1
      WHERE username LIKE '%i%';
    SELECT id,username,age,addr FROM user1
      WHERE username LIKE '王%'; -- 查询出姓张的用户,同理也可以查询出以某个字符结尾的记录

    -- 查询出用户名长度为2的用户
    SELECT id,username,age,addr FROM user1
      WHERE username LIKE '__'; -- 可以配合%使用 
    SELECT id,username,age,addr FROM user1
      WHERE username LIKE '%K%'; -- 默认不区分大小写

GROUP BY

  1. 把值相同的数据放到一个组中，最终查询出的结果只会显示组中的一条记录

  2. 分组配合GROUP_CONCAT(）查看某个字段的详细信息;

  3. 配合聚合函数使用

    • COUNT() 统计记录总数，Count(字段名称) 字段中的值为null不统计进来，Count(*)统计记录总条数包括null值
    • SUM()
    • MAX()
    • MIN()
    • AVG()

  4. 配合WITH ROLLUP 关键字使用 ，会在记录末尾添加一条记录，是上面所有记录的总和；

  5. 按照字段的位置分组

HAVING 子句对分组结果进行二次筛选

       -- 测试分组

        -- 按照性别分组
        SELECT id,username,age ,sex FROM user1 GROUP BY sex;
        -- [42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'king.user1.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

        -- 按性别分组，查询组中的用户名有哪些
        SELECT GROUP_CONCAT(username) ,age,sex,addr FROM user1
          GROUP BY sex;

        -- 测试聚合函数
        SELECT COUNT(*) FROM user1; -- * 统计过程会包含null值
        SELECT COUNT(id) FROM user1; -- 字段方式，会自动过滤null值，不计入统计

        -- 按照sex分组，得到用户名详情，并且得到分组中的总人数
        SELECT sex,GROUP_CONCAT(username)AS usersDetail , COUNT(*) AS totalUsers FROM user1
          GROUP BY sex;

        -- 按照addr分组，得到用户名的详情，总人数，得到组中的年龄总和，年龄的最大值，最小值，平均值
        SELECT addr ,GROUP_CONCAT(username) AS usersDetail,
          COUNT(*) AS totalUsers,
          SUM(age) AS sum_age,
          MAX(age) AS max_age,
          MIN(age) AS min_age,
          AVG(age) As avg_age FROM user1
          GROUP BY addr;

        -- 按照sex分组，统计组中的总人数，用户名详情，得到的薪水总和，薪水最大值，最小值，平均值;
        SELECT sex,COUNT(*) AS totalUsers,
          GROUP_CONCAT(username) AS userDetails,
          SUM(salarry) AS total_salary,
          MAX(salarry) AS max_salary,
          MIN(salarry) AS min_salary,
          AVG(salarry) AS avg_salary
          FROM user1
          GROUP BY sex;

        -- WITH ROLLUP 测试
        SELECT GROUP_CONCAT(username) AS userDetail,
          COUNT(*) AS totalUsers
          FROM user1
          GROUP BY sex
          WITH ROLLUP ;

        -- 按照字段的位置分组
        SELECT id,sex,
          COUNT(*) AS totalUsers,
          GROUP_CONCAT(username) AS userDetails,
          SUM(salarry) AS total_salary,
          MAX(salarry) AS max_salary,
          MIN(salarry) AS min_salary,
          AVG(salarry) AS avg_salary
        FROM user1
        GROUP BY 1; -- 指定字段的位置

        -- 测试HAVING子句
        -- 按照addr分组，统计总人数
        SELECT addr ,
          GROUP_CONCAT(username) AS usersDetail,
          COUNT(*) AS totalUsers
          FROM user1
          GROUP BY addr
          -- HAVING COUNT(*) >= 2;
          HAVING totalUsers >= 2;-- 对于分组结果进行二次筛选，条件是总人数>=2;

        SELECT addr,
          COUNT(*) AS totalUsers,
          GROUP_CONCAT(username) AS userDetails,
          SUM(salarry) AS total_salary,
          MAX(salarry) AS max_salary,
          MIN(salarry) AS min_salary,
          AVG(salarry) AS avg_salary
        FROM user1
        GROUP BY addr
        HAVING avg_salary > 40000; -- 得到平均薪酬水平大于40000

注意：MySql 5.7.5及其以后的版本，默认的Sql模式配置是 ONLY_FULL_GROUP_BY，
在sql规则中 ，除了聚合函数之外，Select 语句中的每个字段都必须在GroupBy 中给出

GROUP BY子句重要规则

 1. 包含任意数目的列
 2. 如果在GROUP BY子句中嵌套入分组，数据会将最后规定的分组上进行总汇
 3. 大多数SQL语句允许GROUPBY带有长度可变的数据类型（文本，备注类型）
 4. 除了聚合函数之外，SELECT 语句中的每个字段都必须在GROUPBY语句中给出
 5. 如果分组带有NULL值，则作为一个分组返回，如果多个将成一组；
 6. GROUP BY子句必须出现在WHERE子句之后

ORDER BY 排序 ；

语法：ORDER BY 字段名称 [ASC | DESC ] 默认ASC 升序排列; 可以根据多个字段进行排序，可以用随机函数实现随机记录，还可以配合分组使用

    -- 测试排序
    -- 按照id降序排列,不写DESC的话默认就是升序排列
    SELECT id,username ,age FROM user1
      ORDER BY id DESC;
    -- 按照age升序
    SELECT id,username,age FROM user1
      ORDER BY age ;
    -- 按照多字段排序 年龄和id
    SELECT id,username,age FROM  user1
        ORDER BY age ASC ,id ASC; -- 这里也可以使用位置，但是不常用，建议还是使用字段名
    -- 和GROUP BY配合使用
    SELECT  addr ,GROUP_CONCAT(username) AS userDestails  FROM user1
      WHERE age > 30
      GROUP BY addr
      ORDER BY age DESC;
    -- 随机函数 产生一个0-1之间的随机数
    SELECT RAND();
    -- 实现随机记录,每次产生的结果都不一样
    SELECT id,username,age FROM user1
      ORDER BY RAND();

LIMIT 限制结果集条数

  1. LIMIT 值 显示结果集的前几条数据

  2. LIMIT offset ,row_count 从offset开始显示几条记录 ,limit从0开始;

  3. update 或者delete时limit只支持一个参数形式

    -- 测试LIMIT语句
-- 显示结果集中的前五条记录;
SELECT  id ,username,age ,sex
  FROM user1
  LIMIT 5;-- 这里和编号没有关系，只是和结果集有关
-- 结果集从0开始显示五条;
SELECT id,username,age,sex
  FROM user1
  LIMIT 0,5; -- 注意这里的偏移量是从0开始的类似于下标

-- 分页实现
-- 显示前三条记录
SELECT id,username,age,sex
FROM user1
LIMIT 0,3;
SELECT id,username,age,sex
FROM user1
LIMIT 3,3;
SELECT id,username,age,sex
FROM user1
LIMIT 6,3;
SELECT id,username,age,sex
FROM user1
LIMIT 9,3;

-- 更新user1 表中的前三条记录，将age +5
UPDATE user1 SET age = age +5 LIMIT 3;
-- 将user1表中的字段降序排列，更新前三条记录，将age减10；
UPDATE user1 SET age = age - 10
  ORDER BY id
  LIMIT 3;
-- 删除user1 表中的ID字段降序排列的前三条记录
DELETE FROM user1
  ORDER BY id DESC
  LIMIT 3;  -- 注意关键字的书写顺序 先分组排序然后limit;
-- 注意： update 或者delete时limit只支持一个参数形式

-- 测试完整SELECT 语句的形式;
SELECT addr ,
  GROUP_CONCAT(username) AS userDetails,
  COUNT(*) AS totalUsers,
  SUM(age) AS sum_age,
  MAX(age) AS max_age,
  MIN(age) AS min_age,
  AVG(age) AS avg_age
  FROM user1
  WHERE id > 2
  GROUP BY addr
  HAVING totalUsers >= 1  -- 进行二次筛选
  ORDER BY totalUsers ASC
  LIMIT 0,2; -- 显示前两条记录，相当于 LIMIT 2


### 多表查询

1.  笛卡尔积形式:一个表中的所有字段，对应另一个表中的所有字段，查询的字段结果为表中的字段乘积;
2.  <p>内连接形式

      *   查询表中符合连接条件的记录 `SELECT 字段名称 ,... FROM tb_name1 [INNER] JOIN tb_name2 ON 连接条件`
  > 注意表与表之间的关系，连接最好不要超过三个表，影响性能
3.  外连接形式

      *   左外连接 `SELECT 字段名称,... FROM tb_name1 LEFT [OUTER] JOIN tb_name2 ON 条件`,
  先显示左表中的全部记录,再去右表中查询符合条件的记录，不符合的以Null代替
  *   右外链接 `SELECT 字段名称,... FROM tb_name1 RIGHT [OUTER] JOIN tb_name2 ON 条件`,
   先显示右表中的全部记录,再去左表中查询符合条件的记录，不符合的以Null代替
   
> 注意表之间的主从关系,先查询主表然后查询从表;

```sql
 -- 多表联查
  CREATE DATABASE IF NOT EXISTS test2 DEFAULT CHARACTER SET 'UTF8';
  USE test2;
  CREATE TABLE emp (
    id TINYINT UNSIGNED AUTO_INCREMENT KEY ,
    username VARCHAR(20) NOT NULL UNIQUE COMMENT '编号',
    age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
    sex ENUM ('男','女' ,'保密') NOT NULL DEFAULT '保密' COMMENT '性别',
    addr VARCHAR(20) NOT NULL DEFAULT '北京',
    depId TINYINT UNSIGNED NOT NULL COMMENT '部门对应的编号'
  )ENGINE = INNODB CHARSET = UTF8;

  INSERT emp(username, age,  depId) VALUES ('king',19,1),
     ('xiaoqiang',25,2),
     ('dagang',22,3),
     ('huahua',23,4),
     ('qiuqiu',22,1),
     ('iMist',21,2);
  -- 添加一条测试记录,这条记录的部门不存在，但是不影响插入(之后考虑外键关联这样就无法插入了);
  INSERT emp(username, age,  depId) VALUES ('测试用户',35,6);
  CREATE TABLE dep(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY ,
    depName VARCHAR(50) NOT NULL UNIQUE ,
    depDesc VARCHAR(100) NOT NULL DEFAULT ''
  )ENGINE = INNODB CHARSET = UTF8;

  INSERT dep(depName, depDesc) VALUES ('研发部','开发部门'),
    ('运维部','运维部门'),
    ('测试部','测试部门'),
    ('市场部','市场调研部门');
  INSERT dep(depName, depDesc) VALUES ('财务部','财务部门');

  SELECT id,username,age ,depName FROM emp,dep; -- Column 'id' in field list is ambiguous
  -- 笛卡尔积形式
  SELECT emp.id,emp.username,emp.age,dep.depName FROM emp,dep;

  -- 内连接形式
  SELECT e.id,e.username,e.age,d.depName
  FROM emp AS e
  INNER JOIN dep AS d
  ON e.depId = d.id; -- 不合法的数据不会被查询出来,因为内连接会查询出符合连接条件的记录，也就是表之间的关系;

   -- 查询 e.id,e.username,e.age,d.id ,d.depName,d.depDesc
  SELECT e.id,e.username,e.age,d.id ,d.depName,d.depDesc
  FROM dep AS d
    JOIN emp AS e
    ON  d.id = e.id;

  -- 测试左外连接
  SELECT e.id,e.username,e.age,d.id ,d.depName,d.depDesc
  FROM emp AS e
  LEFT OUTER JOIN dep as d
  On e.depId = d.id;

  -- 测试右外连接
  SELECT e.id,e.username,e.age,d.id ,d.depName,d.depDesc
  FROM emp AS e
    RIGHT OUTER JOIN dep as d
      ON e.depId = d.id;
MySql基础之存储引擎
Shell编程之正则表达式与字符处理