• mariadb(三)查


    -查询基本使用(条件,排序,聚合函数,分组,分页)
    1)创建一个表结构然后添加数据
    create table baba (id int unsigned not null auto_increment primary key,    #设置id为不能空的约束和自增主键
        ->     name varchar(20) default '',          #varchar(20)的意思为最多20个字符,根据字符个数占据内存大小
        ->     age tinyint unsigned default 0,      #年纪默认值设为0
        ->     high decimal(5,2),                   #小数类型为5进2
        ->     gender enum('', '', '中性', '保密') default '保密',    设置枚举类型
        ->     cls_id int unsigned default 0,
        ->     is_delete bit default 0
        -> );
    2)添加字段内数据
    insert into baba values
    (0,'小明',18,180.00,1,1,0),
    (0,'小月月',19,180.00,1,2,0),
    (0,'彭于晏',28,185.00,1,1,0),
    (0,'刘德华',58,175.00,1,2,0),
    (0,'黄蓉',108,160.00,2,1,0),
    (0,'凤姐',44,150.00,4,2,1),
    (0,'王祖贤',52,170.00,2,1,1),
    (0,'周杰伦儿',34,null,1,1,0),
    (0,'程坤',44,181.00,1,2,0),
    (0,'和珅',55,166.00,1,2,0),
    (0,'刘亦菲',29,162.00,2,3,0),
    (0,'金星',45,180.00,3,4,0),
    (0,'静香',18,170.00,2,4,0),
    (0,'郭靖',22,167.00,1,5,0),
    (0,'周杰',33,178.00,1,1,0),
    (0,'钱小豪',56,178.00,1,1,0),
    (0,'谢霆锋',38,175.00,1,1,0),
    (0,'陈冠希',38,175.00,1,1,0);
    4)查看全部字段内容:
    select * from baba;            #将字段为列标题,内容行输出
    +----+--------------+------+--------+--------+--------+-----------+
    | id | name         | age  | high   | gender | cls_id | is_delete |
    +----+--------------+------+--------+--------+--------+-----------+
    |  1 | 小明         |   18 | 180.00 | 男     |      1 |           |
    |  2 | 小月月       |   19 | 180.00 | 男     |      2 |           |
    |  3 | 彭于晏       |   28 | 185.00 | 男     |      1 |           |
    |  4 | 刘德华       |   58 | 175.00 | 男     |      2 |           |
    |  5 | 黄蓉         |  108 | 160.00 | 女     |      1 |           |
    |  6 | 凤姐         |   44 | 150.00 | 保密   |      2 |          |
    |  7 | 王祖贤       |   52 | 170.00 | 女     |      1 |          |
    |  8 | 周杰伦儿     |   34 |   NULL | 男     |      1 |           |
    |  9 | 程坤         |   44 | 181.00 | 男     |      2 |           |
    | 10 | 和珅         |   55 | 166.00 | 男     |      2 |           |
    | 11 | 刘亦菲       |   29 | 162.00 | 女     |      3 |           |
    | 12 | 金星         |   45 | 180.00 | 中性   |      4 |           |
    | 13 | 静香         |   18 | 170.00 | 女     |      4 |           |
    | 14 | 郭靖         |   22 | 167.00 | 男     |      5 |           |
    | 15 | 周杰         |   33 | 178.00 | 男     |      1 |           |
    | 16 | 钱小豪       |   56 | 178.00 | 男     |      1 |           |
    | 17 | 谢霆锋       |   38 | 175.00 | 男     |      1 |           |
    | 18 | 陈冠希       |   38 | 175.00 | 男     |      1 |           |
    5)查看前三行内容
    select * from baba where id<3 ;      用where来限定范围
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | high   | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  1 | 小明      |   18 | 180.00 | 男     |      1 |           |
    |  2 | 小月月    |   19 | 180.00 | 男     |      2 |           |
    +----+-----------+------+--------+--------+--------+-----------+
    6)查看指定字段:
    select name,age from baba;       #查看指定的name,age段
    +--------------+------+
    | name         | age  |
    +--------------+------+
    | 小明         |   18 |
    | 小月月       |   19 |
    | 彭于晏       |   28 |
    | 刘德华       |   58 |
    | 黄蓉         |  108 |
    | 凤姐         |   44 |
    | 王祖贤       |   52 |
    | 周杰伦儿     |   34 |
    | 程坤         |   44 |
    | 和珅         |   55 |
    | 刘亦菲       |   29 |
    | 金星         |   45 |
    | 静香         |   18 |
    | 郭靖         |   22 |
    | 周杰         |   33 |
    | 钱小豪       |   56 |
    | 谢霆锋       |   38 |
    | 陈冠希       |   38 |
    7)查看指定字段并设置别名: 
    7)查看指定字段并设置别名:
    select name as '姓名' ,age as '年龄‘ from baba;      #字段中间用逗号隔开
    +--------------+--------+
    | 姓名         | 年龄   |
    +--------------+--------+
    | 小明         |     18 |
    | 小月月       |     19 |
    | 彭于晏       |     28 |
    | 刘德华       |     58 |
    | 黄蓉         |    108 |
    | 凤姐         |     44 |
    | 王祖贤       |     52 |
    | 周杰伦儿     |     34 |
    | 程坤         |     44 |
    | 和珅         |     55 |
    | 刘亦菲       |     29 |
    | 金星         |     45 |
    | 静香         |     18 |
    | 郭靖         |     22 |
    | 周杰         |     33 |
    | 钱小豪       |     56 |
    | 谢霆锋       |     38 |
    | 陈冠希       |     38 |
    8)通过表名字段查询
    select baba.name from baba;         
    9) 给表起别名查询
    select mama.name from baba as mama;
    10)消除重复行查询(distinct)
    select distinct age from baba;
    +------+
    | age  |
    +------+
    |   18 |
    |   19 |
    |   28 |
    |   58 |
    |  108 |
    |   44 |
    |   52 |
    |   34 |
    |   55 |
    |   29 |
    |   45 |
    |   22 |
    |   33 |
    |   56 |
    |   38 |
    条件查询

    比较运算符:
    *查看所有数据中年纪大于40的数据。
    MariaDB [mysql]> select * from baba where age >40 ;
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | high   | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  4 | 刘德华    |   58 | 175.00 | 男     |      2 |           |
    |  5 | 黄蓉      |  108 | 160.00 | 女     |      1 |           |
    |  6 | 凤姐      |   44 | 150.00 | 保密   |      2 |          |
    |  7 | 王祖贤    |   52 | 170.00 | 女     |      1 |          |
    |  9 | 程坤      |   44 | 181.00 | 男     |      2 |           |
    | 10 | 和珅      |   55 | 166.00 | 男     |      2 |           |
    | 12 | 金星      |   45 | 180.00 | 中性   |      4 |           |
    | 16 | 钱小豪    |   56 | 178.00 | 男     |      1 |           |
    12)*查看所有数据中身高在170到180之间的。
    MariaDB [mysql]> select * from baba  where high>170 and high < 180;     #注意不能写成170<high<180
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | high   | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  4 | 刘德华    |   58 | 175.00 | 男     |      2 |           |
    | 15 | 周杰      |   33 | 178.00 | 男     |      1 |           |
    | 16 | 钱小豪    |   56 | 178.00 | 男     |      1 |           |
    | 17 | 谢霆锋    |   38 | 175.00 | 男     |      1 |           |
    | 18 | 陈冠希    |   38 | 175.00 | 男     |      1 |           |
    13)*查看所有数据中id>15,或者性别为男的数据;
    select * from baba where id >15 or gender=1;  
    +----+--------------+------+--------+--------+--------+-----------+
    | id | name         | age  | high   | gender | cls_id | is_delete |
    +----+--------------+------+--------+--------+--------+-----------+
    |  1 | 小明         |   18 | 180.00 | 男     |      1 |           |
    |  2 | 小月月       |   19 | 180.00 | 男     |      2 |           |
    |  3 | 彭于晏       |   28 | 185.00 | 男     |      1 |           |
    |  4 | 刘德华       |   58 | 175.00 | 男     |      2 |           |
    |  8 | 周杰伦儿     |   34 |   NULL | 男     |      1 |           |
    |  9 | 程坤         |   44 | 181.00 | 男     |      2 |           |
    | 10 | 和珅         |   55 | 166.00 | 男     |      2 |           |
    | 14 | 郭靖         |   22 | 167.00 | 男     |      5 |           |
    | 15 | 周杰         |   33 | 178.00 | 男     |      1 |           |
    | 16 | 钱小豪       |   56 | 178.00 | 男     |      1 |           |
    | 17 | 谢霆锋       |   38 | 175.00 | 男     |      1 |           |
    | 18 | 陈冠希       |   38 | 175.00 | 男     |      1 |           |

    模糊查询(like)

    %代表0个或多个

    *查询所有数据中与’月‘相关的数据
    MariaDB [mysql]> select * from baba where name like '%月%';
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | high   | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  2 | 小月月    |   19 | 180.00 | 男     |      2 |           |
    *查询两个字的人名
    ariaDB [mysql]> select * from baba where name like '__';    '_'为一个字符的为一个字符的占位符
    +----+--------+------+--------+--------+--------+-----------+
    | id | name   | age  | high   | gender | cls_id | is_delete |
    +----+--------+------+--------+--------+--------+-----------+
    |  1 | 小明   |   18 | 180.00 | 男     |      1 |           |
    |  5 | 黄蓉   |  108 | 160.00 | 女     |      1 |           |
    |  6 | 凤姐   |   44 | 150.00 | 保密   |      2 |          |
    |  9 | 程坤   |   44 | 181.00 | 男     |      2 |           |
    | 10 | 和珅   |   55 | 166.00 | 男     |      2 |           |
    | 12 | 金星   |   45 | 180.00 | 中性   |      4 |           |
    | 13 | 静香   |   18 | 170.00 | 女     |      4 |           |
    | 14 | 郭靖   |   22 | 167.00 | 男     |      5 |           |
    | 15 | 周杰   |   33 | 178.00 | 男     |      1 |           |
    *查询三个字以及以上的人名:
    MariaDB [mysql]> select * from baba where name like '_ _ _%';     
    +----+--------------+------+--------+--------+--------+-----------+
    | id | name         | age  | high   | gender | cls_id | is_delete |
    +----+--------------+------+--------+--------+--------+-----------+
    |  2 | 小月月       |   19 | 180.00 | 男     |      2 |           |
    |  3 | 彭于晏       |   28 | 185.00 | 男     |      1 |           |
    |  4 | 刘德华       |   58 | 175.00 | 男     |      2 |           |
    |  7 | 王祖贤       |   52 | 170.00 | 女     |      1 |          |
    |  8 | 周杰伦儿     |   34 |   NULL | 男     |      1 |           |
    | 11 | 刘亦菲       |   29 | 162.00 | 女     |      3 |           |
    | 16 | 钱小豪       |   56 | 178.00 | 男     |      1 |           |
    | 17 | 谢霆锋       |   38 | 175.00 | 男     |      1 |           |
    | 18 | 陈冠希       |   38 | 175.00 | 男     |      1 |           |
    范围查询 in(1,,5,9) 查询非连续范围内的数据
    *查询年纪是18和52的数据
    MariaDB [mysql]> select * from baba where age=18 or age=52;         #注意这里和查询用的是or
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | high   | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  1 | 小明      |   18 | 180.00 | 男     |      1 |           |
    |  7 | 王祖贤    |   52 | 170.00 | 女     |      1 |          |
    | 13 | 静香      |   18 | 170.00 | 女     |      4 |           |
    *查询不在20到50岁的数据
    MariaDB [mysql]> select * from baba where age not between 20 and 50;
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | high   | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  1 | 小明      |   18 | 180.00 | 男     |      1 |           |
    |  2 | 小月月    |   19 | 180.00 | 男     |      2 |           |
    |  4 | 刘德华    |   58 | 175.00 | 男     |      2 |           |
    |  5 | 黄蓉      |  108 | 160.00 | 女     |      1 |           |
    |  7 | 王祖贤    |   52 | 170.00 | 女     |      1 |          |
    | 10 | 和珅      |   55 | 166.00 | 男     |      2 |           |
    | 13 | 静香      |   18 | 170.00 | 女     |      4 |           |
    | 16 | 钱小豪    |   56 | 178.00 | 男     |      1 |           |
    空判断(字符内容是否是null   用is)
    MariaDB [mysql]> select * from baba where high is null;
    +----+--------------+------+------+--------+--------+-----------+
    | id | name         | age  | high | gender | cls_id | is_delete |
    +----+--------------+------+------+--------+--------+-----------+
    |  8 | 周杰伦儿     |   34 | NULL | 男     |      1 |           |
    +----+--------------+------+------+--------+--------+-----------+
    排序(order by) asc从小到大,desc从大到小排序
    *查询年纪在18到34岁的男性,身高从高到矮排序,如果身高相同的情况下按照年纪从小到大排序,如果年龄也相等那么按照id从小到大排序;
    MariaDB [mysql]> select * from baba where age between 18 and 34 and gender=1 order by high desc,age asc,id asc;
    +----+--------------+------+--------+--------+--------+-----------+
    | id | name         | age  | high   | gender | cls_id | is_delete |
    +----+--------------+------+--------+--------+--------+-----------+
    |  3 | 彭于晏       |   28 | 185.00 | 男     |      1 |           |
    |  1 | 小明         |   18 | 180.00 | 男     |      1 |           |
    |  2 | 小月月       |   19 | 180.00 | 男     |      2 |           |
    | 15 | 周杰         |   33 | 178.00 | 男     |      1 |           |
    | 14 | 郭靖         |   22 | 167.00 | 男     |      5 |           |
    |  8 | 周杰伦儿     |   34 |   NULL | 男     |      1 |           |
    多个排序方法用逗号可开

    聚合函数(count,max,min) where用于限定范围,聚合函数用于比较统计
    *查询男性有多少人
    MariaDB [mysql]> select count(*) from baba where gender=1;  #chount统计个数
    +---------+
    | count(*) |
    +----------+
    |       12 |
    *查询年纪最大的
    MariaDB [mysql]> select max(age) from baba;    #max(字段),缩小了搜索范围
    +----------+
    | max(age) |
    +----------+
    |      108 |
    *查询身高最小的
    MariaDB [mysql]> select min(high) from baba;       #min(字段)
    +-----------+
    | min(high) |
    +-----------+
    |    150.00 |
    *查看女性中身高最高的
    MariaDB [mysql]> select max(high) from baba where gender=2; 
    +-----------+
    | max(high) |
    +-----------+
    |    170.00 |
    *计算序号大于1的男性的平均年纪
    MariaDB [mysql]> select sum(age)/count(*),2 from baba where gender=1 and id >1;
    MariaDB [mysql]> select sum(age)/count(*),2 from baba where gender=1 and id >1;
    +-------------------+---+
    | sum(age)/count(*) | 2 |
    +-------------------+---+
    |           38.6364 | 2 |
    也可以将sum(age)/count(*) 写作avg(age)    ,2为保留两位小数
    分组(group by)
    *查询每组性别中的人数
    MariaDB [mysql]> select gender ,count(*) from baba  group by gender;     #中间逗号隔开;查看以性别分的组,统计每组人数个数
    +--------+----------+
    | gender | count(*) |
    +--------+----------+
    | 男     |       12 |
    | 女     |        4 |
    | 中性   |        1 |
    | 保密   |        1 |
    *查看男性组的成员姓名    #附带的字段内容用group——concat()来指定
    MariaDB [mysql]> select gender ,group_concat(name) from baba where gender=1 group by gender;
    +--------+-------------------------------------------------------------------------------------------------------------+
    | gender | group_concat(name)                                                                                          |
    +--------+-------------------------------------------------------------------------------------------------------------+
    | 男     | 小明,谢霆锋,钱小豪,周杰,郭靖,和珅,程坤,周杰伦儿,刘德华,彭于晏,小月月,陈冠希
    *查询男性中的人数多于4个的组的信息(这里就将性别看作成一个组)
    MarmariaDB [mysql]> select gender ,group_concat(name,'|',age,'|',high) from baba where gender=1 group by gender having count(*)>4;
    | gender | group_concat(name,'|',age,'|',high)       #查看附加信息为年纪,姓名,身高,中间用’|‘分开,附加信息跟在组后面用逗号隔开,限定条件放在后面。
    | 男     | 小明|18|180.00,谢霆锋|38|175.00,钱小豪|56|178.00,周杰|33|178.00,郭靖|22|167.00,
    和珅|55|166.00,程坤|44|181.00,刘德华|58|175.00,彭于晏|28|185.00,小月月|19|180.00,陈冠希|38|175.00
    分页
    *分页显示,每页显示2条数据
    MariaDB [mysql]> select * from baba limit n,2;        #4代表的是数据从n+1行开始显示,2代表的是每页显示两行数据(这建立设置了主键自增的前提下,否则从n开始)
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | high   | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    |  n+1 | 小明      |   18 | 180.00 | 男     |      1 |           |
    |  n+2 | 小月月    |   19 | 180.00 | 男     |      2 |           |
    *按照身高从高到矮排序,查找出所有女性,并且分页显示,每页显示2条数据
    MariaDB [mysql]> select * from baba where gender=2 order by high desc limit 2;  #一个数字默认为(0,2)
    +----+-----------+------+--------+--------+--------+-----------+
    | id | name      | age  | high   | gender | cls_id | is_delete |
    +----+-----------+------+--------+--------+--------+-----------+
    | 13 | 静香      |   18 | 171.00 | 女     |      4 |           |
    | 14 | 王祖贤    |   52 | 170.00 | 女     |      1 |          |
     
     
     


     
  • 相关阅读:
    CSS中position小解
    position
    mac默认安装postgresql, 如何让postgresql可以远程访问
    The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 10.0.
    active admin gem error
    psql 无法添加超级用户
    ubuntu 15.04 安装Balsamiq Mockups 3
    Rails html 写public里图片的路径
    rails c 历史命令
    undefined local variable or method `per' for []:ActiveRecord::Relation
  • 原文地址:https://www.cnblogs.com/zzzynx/p/10846504.html
Copyright © 2020-2023  润新知