MySQL简单查询详解

客官°小女子只卖身不卖艺 2022-01-14 00:21 149阅读 0赞

**MySQL的查询操作**

单表查询:简单查询

多表查询:连接查询

联合查询

**布尔条件表达式操作符**

= 等值比较
       <=>：跟空值比较不会产生额外信息的等值比较
       <>:不等值
       <: 
       <=:
       >
       >=
       IS NULL
       IS NOT NULL    
       LIKE: 
          支持的通配符: %(任意长度的任意字符),_（任意单个字符）
       RLIKE,REGEXP: 支持使用正则表达式
       IN: 判断指定字段的值是否在给定在列表中；  
       BETWEEN ... AND ...: 位于指定的范围之间(x>=10 and x<=40 --> x BETWEEN 10 AND 20)

**示例**

新创建一个表
    mysql> create table tests (sid int unsigned auto_increment not null unique key,name char(30) 
        not null,age tinyint unsigned not null,gender enum('F','M') not null,tutor char(30));

[![wKiom1hTNDLBAEgaAAApZBokJ-s251.png-wh\_50][wKiom1hTNDLBAEgaAAApZBokJ-s251.png-wh_50]][wKiom1hTNDLBAEgaAAApZBokJ-s251.png-wh_50]

添加几个用户
    mysql> insert into tests values (1,'Guo Jing',27,'M','Song Jiang'),
        (2,'Yang Guo',28,'M','Hu Sanniang'),(3,'Guo Polu',21,'M','Jia Baoyu');
        
    mysql> insert into tests values (4,'Xue Baochai','19','F','Rong Momo'),
          (5,'Xia Yuhe',37,'F','Shi Qian'),(6,'Wu Yong',51,'M','Lin Daiyu');

[![wKiom1hTNMfyCL4zAAAkRowALu0842.png-wh\_50][wKiom1hTNMfyCL4zAAAkRowALu0842.png-wh_50]][wKiom1hTNMfyCL4zAAAkRowALu0842.png-wh_50]

BETWEEN...AND...语法演示
    
    mysql> select name,age from tests where age between 25 and 40;

[![wKioL1hTNQugwUMJAAAwjSSZK5A453.png-wh\_50][wKioL1hTNQugwUMJAAAwjSSZK5A453.png-wh_50]][wKioL1hTNQugwUMJAAAwjSSZK5A453.png-wh_50]

IN 语法演示
    
    mysql> select name,age from tests where age in (25,26,27,28,29);

[![wKiom1hTNTaj4SA-AAArigU\_LR8193.png-wh\_50][wKiom1hTNTaj4SA-AAArigU_LR8193.png-wh_50]][wKiom1hTNTaj4SA-AAArigU_LR8193.png-wh_50]

LIKE 语法演示
    
    mysql> select name from tests where name like 'x%';

[![wKioL1hTNWngiLSRAAAlWHYtHM8572.png-wh\_50][wKioL1hTNWngiLSRAAAlWHYtHM8572.png-wh_50]][wKioL1hTNWngiLSRAAAlWHYtHM8572.png-wh_50]

RLIKE 语法演示
    
    mysql> select name from tests where name rlike '^x.*';

[![wKioL1hTNZWgwvzBAAAUnsT5KQM953.png-wh\_50][wKioL1hTNZWgwvzBAAAUnsT5KQM953.png-wh_50]][wKioL1hTNZWgwvzBAAAUnsT5KQM953.png-wh_50]

//添加两行新的数据
    //NULL不是字符串 不需要加引号
    mysql> insert into tests values (7,'tom',11,'M','jerry'),(8,'tomy',13,'M',NULL);

[![wKiom1hTNbvDAH16AABaK5CMRbE229.png-wh\_50][wKiom1hTNbvDAH16AABaK5CMRbE229.png-wh_50]][wKiom1hTNbvDAH16AABaK5CMRbE229.png-wh_50]

IS NULL
    
    mysql> select name,tutor from tests where tutor is null;

[![wKiom1hTNfiyJWd2AAAYaYvv0c4906.png-wh\_50][wKiom1hTNfiyJWd2AAAYaYvv0c4906.png-wh_50]][wKiom1hTNfiyJWd2AAAYaYvv0c4906.png-wh_50]

NOT NULL
    
    mysql> select name,tutor from tests where tutor is not null;

**组合条件测试**

NOT, !
    AND, &&
    OR, ||

**示例**

mysql> select name,gender,age from tests where age > 25 and gender='M';

[![wKioL1hTNlmR8FESAABEs5MJBwY441.png-wh\_50][wKioL1hTNlmR8FESAABEs5MJBwY441.png-wh_50]][wKioL1hTNlmR8FESAABEs5MJBwY441.png-wh_50]

**排序**

order by ‘排序字段’
    默认为升序：ASC
    降序：DESC

**示例**

mysql> select name,gender,age from tests where age > 25 and gender='M' order by name;
    mysql> select name,gender,age from tests where age > 25 and gender='M' order by name desc;

[![wKiom1hTNp-Tan6qAAB-\_2XPQVw799.png-wh\_50][wKiom1hTNp-Tan6qAAB-_2XPQVw799.png-wh_50]][wKiom1hTNp-Tan6qAAB-_2XPQVw799.png-wh_50]

**聚合函数**

SUM(), AVG(), MAX(), MIN(), COUNT()

**示例**

mysql> select sum(age) from tests;
    mysql> select avg(age) from tests;
    mysql> select max(age) from tests;
    mysql> select count(age) from tests;

[![wKioL1hTNu2D21TaAABOWsrT0Io301.png-wh\_50][wKioL1hTNu2D21TaAABOWsrT0Io301.png-wh_50]][wKioL1hTNu2D21TaAABOWsrT0Io301.png-wh_50]

mysql> select count(*) from tests where age > 25;
    //年龄大于25的有4个

[![wKiom1hTNxSB0us5AAAQiDn-Pbk525.png-wh\_50][wKiom1hTNxSB0us5AAAQiDn-Pbk525.png-wh_50]][wKiom1hTNxSB0us5AAAQiDn-Pbk525.png-wh_50]

mysql> select sum(age) from tests where age > 25;
    //年龄大于25的所有年龄之和

[![wKioL1hTNzaRvkPFAAARRWQNqtg946.png-wh\_50][wKioL1hTNzaRvkPFAAARRWQNqtg946.png-wh_50]][wKioL1hTNzaRvkPFAAARRWQNqtg946.png-wh_50]

**分组**

group by

**示例**

mysql> select gender,sum(age) from tests group by gender;

[![wKiom1hTN2-A\_LB9AAAsH5KLCcQ659.png-wh\_50][wKiom1hTN2-A_LB9AAAsH5KLCcQ659.png-wh_50]][wKiom1hTN2-A_LB9AAAsH5KLCcQ659.png-wh_50]

mysql> alter table tests add classid tinyint unsigned;
    //为表新添加一个字段

[![wKioL1hTN5GAHik5AABm7GJgei8550.png-wh\_50][wKioL1hTN5GAHik5AABm7GJgei8550.png-wh_50]][wKioL1hTN5GAHik5AABm7GJgei8550.png-wh_50]

插入数据
    mysql> update tests set classid=1 where sid=1;
    ...
    ...

[![wKioL1hTN8ajzhXgAAAsyme3mF0695.png-wh\_50][wKioL1hTN8ajzhXgAAAsyme3mF0695.png-wh_50]][wKioL1hTN8ajzhXgAAAsyme3mF0695.png-wh_50]

mysql> select classid,count(*) from tests group by classid; 或者
    mysql> select classid,count(name) from tests group by classid;
    //按班级分类 每班有多少人

[![wKiom1hTN-WS7XdHAAAjIIeeers342.png-wh\_50][wKiom1hTN-WS7XdHAAAjIIeeers342.png-wh_50]][wKiom1hTN-WS7XdHAAAjIIeeers342.png-wh_50]

mysql> select classid,count(name),sum(age) from tests group by classid;
    每个班的人的年龄之和

**对分组(group by)的条件过滤**

having
        注意：使用having和不是使用where

**示例**

mysql> select classid,count(*) from tests group by classid having count(*) >= 2;

[![wKioL1hTODLwfk1vAAApEvuWx0M298.png-wh\_50][wKioL1hTODLwfk1vAAApEvuWx0M298.png-wh_50]][wKioL1hTODLwfk1vAAApEvuWx0M298.png-wh_50]

mysql> select classid,count(*),sum(age) from tests group by classid having sum(age) <= 50;

[![wKiom1hTOFPi6yAyAAAXmB8wgME791.png-wh\_50][wKiom1hTOFPi6yAyAAAXmB8wgME791.png-wh_50]][wKiom1hTOFPi6yAyAAAXmB8wgME791.png-wh_50]

mysql> select classid,count(name),sum(age) from tests where classid in (1,2) group by classid havingsum(age) <= 50;
    //注意where过滤和having过滤 分别出现的位置

**只返回有用的行**

LIMIT 
    一个数为显示的行数
    两个数字为偏移第一个数字行，显示第二个数字

**示例**

mysql> select * from tests limit 2;
    mysql> select * from tests limit 2,3;

[![wKioL1hTOLfhtCADAABlZ1WzwZg558.png-wh\_50][wKioL1hTOLfhtCADAABlZ1WzwZg558.png-wh_50]][wKioL1hTOLfhtCADAABlZ1WzwZg558.png-wh_50]

**select语句**

distinct   重复的只显示一次
    SQL_CACHE  缓存查询结果
    SQL_NO_CACHE 不缓存查询结果

**示例**

mysql> select age from tests order by age; 
    mysql> select distinct age from tests order by age;

**总结**

**select语句的执行流程**

from clause --> where clause --> group by --> having clause -->order by -->

select -->limit

**常见用法**

select
    from
    order by

select
    from
    group by
    having

select
    from
    where

select -->调用内部函数

select
    from
    where
    group by
    limit

转载于:https://blog.51cto.com/sixijie123/1883224

[wKiom1hTNDLBAEgaAAApZBokJ-s251.png-wh_50]: https://s1.51cto.com/wyfs02/M00/8B/A2/wKiom1hTNDLBAEgaAAApZBokJ-s251.png-wh_500x0-wm_3-wmp_4-s_925550764.png
[wKiom1hTNMfyCL4zAAAkRowALu0842.png-wh_50]: /images/20220113/fb47d67b5c7a4ab5a55055e6119be110.png
[wKioL1hTNQugwUMJAAAwjSSZK5A453.png-wh_50]: /images/20220113/34fffc14ac554645a0673311e7615eb5.png
[wKiom1hTNTaj4SA-AAArigU_LR8193.png-wh_50]: /images/20220113/0e0bc333692d4c758bf610e2b3eafea5.png
[wKioL1hTNWngiLSRAAAlWHYtHM8572.png-wh_50]: /images/20220113/71a30e1dc96c44c6a1fe19ac4528769f.png
[wKioL1hTNZWgwvzBAAAUnsT5KQM953.png-wh_50]: https://s1.51cto.com/wyfs02/M01/8B/9E/wKioL1hTNZWgwvzBAAAUnsT5KQM953.png-wh_500x0-wm_3-wmp_4-s_3545015822.png
[wKiom1hTNbvDAH16AABaK5CMRbE229.png-wh_50]: /images/20220113/c97a0b9555a34f8bb3dcc674ab685c67.png
[wKiom1hTNfiyJWd2AAAYaYvv0c4906.png-wh_50]: /images/20220113/6adff682c2c64785986625ce81808104.png
[wKioL1hTNlmR8FESAABEs5MJBwY441.png-wh_50]: /images/20220113/15608796617b4b94a0cbac93c1c5dcb9.png
[wKiom1hTNp-Tan6qAAB-_2XPQVw799.png-wh_50]: /images/20220113/582ff79ba79e47ca9ec49370ed783a40.png
[wKioL1hTNu2D21TaAABOWsrT0Io301.png-wh_50]: https://s1.51cto.com/wyfs02/M02/8B/9E/wKioL1hTNu2D21TaAABOWsrT0Io301.png-wh_500x0-wm_3-wmp_4-s_2658315089.png
[wKiom1hTNxSB0us5AAAQiDn-Pbk525.png-wh_50]: /images/20220113/a00a7bde64b84e0cb3bd2959df3e146c.png
[wKioL1hTNzaRvkPFAAARRWQNqtg946.png-wh_50]: /images/20220113/c0b1734e0c434705a53a35526c3fef42.png
[wKiom1hTN2-A_LB9AAAsH5KLCcQ659.png-wh_50]: /images/20220113/f2c0e225d9354a7fa301f48d767ae32d.png
[wKioL1hTN5GAHik5AABm7GJgei8550.png-wh_50]: /images/20220113/964171a1391e4aeebdd5185977647d2f.png
[wKioL1hTN8ajzhXgAAAsyme3mF0695.png-wh_50]: https://s1.51cto.com/wyfs02/M00/8B/9E/wKioL1hTN8ajzhXgAAAsyme3mF0695.png-wh_500x0-wm_3-wmp_4-s_3672451127.png
[wKiom1hTN-WS7XdHAAAjIIeeers342.png-wh_50]: https://s1.51cto.com/wyfs02/M01/8B/A2/wKiom1hTN-WS7XdHAAAjIIeeers342.png-wh_500x0-wm_3-wmp_4-s_3617116206.png
[wKioL1hTODLwfk1vAAApEvuWx0M298.png-wh_50]: /images/20220113/1bff69dc3bc648deb12f8314eadf9a73.png
[wKiom1hTOFPi6yAyAAAXmB8wgME791.png-wh_50]: /images/20220113/8ee33ff37fc8400e8d29c9584075e1c9.png
[wKioL1hTOLfhtCADAABlZ1WzwZg558.png-wh_50]: /images/20220113/47a44bd5d5ae47a28916bedc924a6f81.png