avatar

MySQL总结(三)

四、数据库的查询

select语句

> select <列名1, 列名2...> from <表名>[where子句][group子句][having子句][order by子句][limit子句]

列的选择与指定

  • 选择指定的列
    1
    2
    3
    4
    select student_name, student_age, student_major
    from mytest.stydents
    <!-- 选择所有列 -->
    select * from mytest.students
  • 定义并使用列的别名
    1
    select student_name, student_contact as '联系方式' from mytest.students;
  • 计算列值
    1
    select student_name, student_id, student_id+100 from students
  • 替换查询结果
    1
    2
    3
    4
    5
    6
    select student_name,
    case
    when student_sex='0' then '男'
    else '女'
    end as '性别'
    from mytest.students;
  • 聚合函数
    1
    select max(student_age) from students;

FROM子句与连接表

  • 连接表
    1. 交叉连接(笛卡尔积)
      1
      select * from mytest.a cross join mytest.b;
    2. 内连接(交叉连接+条件语句)
      1
      select * from mytest.a inner join mytest.b on mytest.a.id > mytest.b.id;
    3. 左连接右连接

WHERE子句

  • 比较运算
    1
    select * from mytest.student where student_sex='1';
  • 字符串匹配
    1. 百分号%
      1
      2
      3
      /* 代表任何长度的字符串 */
      select student)id, student_name from mytest.students
      where student_name like '李%'
    2. 下划线_
      1
      用途同百分号,但只代表一个字符
  • 文本匹配(这里也有正则…)
    1. 选择匹配
      1
      2
      select student_name, student_major from mytest.students
      where student_major regexp 'gy|hh';
    2. 范围匹配
      1
      2
      3
      select student_name, student_age from mytest.students
      where student_age regexp '[4-5]'
      /* 输出年龄中有数字4或5的学生信息 */
    3. 特殊字符匹配
      1
      数据库的转义用\\两个,eg'\\t', 反斜杠'\\\'
    4. 重复匹配
      1
      2
      3
      4
      * 0个或多个匹配
      + 1个或多个匹配
      ? 0个或1个匹配
      {n} 指定n个匹配
    5. 字符类匹配
      1
      任意大写字母'[A-Z]'
    6. 使用定位符匹配
      1
      2
      ^ 文本开始
      $ 文本结尾
  • 判定范围
    1. between … and
      1
      2
      select student_name, student_age from mytest.students
      where student_age between 20 and 23;
    2. in
      1
      2
      select student_name, student_age from mytest.students
      where student_age in (20,23);
    3. 判断空值
      1
      2
      select mytest.student_name, mytest.student_contact from mytest
      where student_contact is null
    4. 嵌套查询

group by子句与分组数据

  • 将算则结果,根据选择列进行逻辑分组
    1
    2
    3
    4
    5
    6
    7
    8
    9
    /* 分组统计男生人数和女生人数 */
    select student_sex, count(*) as '人数'
    from mytest.students
    group by student_sex;

    /* 统计不同专业的平均年龄 */
    select student_major, avg(student_age) as '平均年龄'
    from mytest.students
    group by student_major;

having子句

  • 过滤分组
    1
    2
    3
    4
    5
    /* 挑选专业人数大于2的专业,并分组 */
    select mytest.student_major, count(*) as '专业人数'
    from mytest.students
    group by student_major
    having count(*)>2;

order by子句

  • 将结果按一定顺序排序

    1
    2
    3
    select student_name, student_age from mytest.students
    order by student_age DESC
    /* 升序ASC 降序DESC */

    limit子句

  • 限制查询结果的返回条数

    1
    2
    3
    /* 查询第五个学生开始的3个学生的id和姓名 */
    select student_id, student_name from mytest.students
    LIMIT 4, 3; //下标从0开始 该语句等价于 LIMIT 3 OFFSET 4

    UNION语句与联合查询

  • 把多个查询结果合到一个结果集当中,作为单个查询结果集合返回

    select … union[all|distinct] select …

    1
    2
    3
    4
    5
    6
    //distinct为默认,不允许重复记录存在
    sleect student_name, student_major from mytest.students
    where student_major='物理'
    union [distinct]
    sleect student_name, student_major from mytest.students
    where student_sex='1';
文章作者: Gy
文章链接: http://sgyat.cn/2020/02/15/MySQL(三)/
版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 年轻没有梦
打赏
  • 微信
    微信
  • 支付宝
    支付宝

评论