六、MySQL基础系列笔记之SQL DQL基本查询

DQL主要用于数据查询。数据库系统中，查询是最为复杂的，千变万化。

查询基本语法

select [字段1,字段2,...] from 表名 where [查询条件] [group by 条件] [having  表达式] [order by 字段] [limit [offset]]

简单查询

*-查询所有字段

mysql> select * from emp;

查询指定字段

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 查看单个字段</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> name <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> emp;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 查询多个字段</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> name,salary <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> emp;</span>

条件查询

where-条件查询

语法

select * from 表名 where 查询条件

where查询条件有 =、<>、<、<=、>、>=、between

查询工资大于900的

mysql> select * from emp where salary > 900;

in/not in-查询

in用来查询满足指定值内的条件记录。

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 查询工资在900-1200的记录</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> emp <span style="box-sizing: border-box;color: rgb(199, 146, 234)">where</span> salary <span style="box-sizing: border-box;color: rgb(199, 146, 234)">in</span> (<span style="box-sizing: border-box;color: rgb(255, 83, 112)">900</span>,<span style="box-sizing: border-box;color: rgb(255, 83, 112)">1200</span>);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> emp <span style="box-sizing: border-box;color: rgb(199, 146, 234)">where</span> salary <span style="box-sizing: border-box;color: rgb(199, 146, 234)">not</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">in</span> (<span style="box-sizing: border-box;color: rgb(255, 83, 112)">900</span>,<span style="box-sizing: border-box;color: rgb(255, 83, 112)">1200</span>);</span>

between and/not between and-查询

查询工资在900到1000之前的记录

mysql> select * from emp where salary between 900 and 1000;

or-查询

or只要满足其中一个条件就返回，可以使用多个or查询

mysql> select * from emp where salary < 800 or salary > 900;

and-查询

and只有满足所有条件才返回

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 查询工资等于900且名字是aimi的员工</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> emp <span style="box-sizing: border-box;color: rgb(199, 146, 234)">where</span> salary = <span style="box-sizing: border-box;color: rgb(255, 83, 112)">900</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">and</span> name = <span style="box-sizing: border-box;color: rgb(195, 232, 141)">'aimi'</span>;</span>

like-查询

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 1、百分号通配符号查询</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 查询姓名以l开头的员工</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> emp <span style="box-sizing: border-box;color: rgb(199, 146, 234)">where</span> name <span style="box-sizing: border-box;color: rgb(199, 146, 234)">like</span> <span style="box-sizing: border-box;color: rgb(195, 232, 141)">'l%'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 查询姓名中包含字母i的员工</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> emp <span style="box-sizing: border-box;color: rgb(199, 146, 234)">where</span> name <span style="box-sizing: border-box;color: rgb(199, 146, 234)">like</span> <span style="box-sizing: border-box;color: rgb(195, 232, 141)">'%i%'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(103, 110, 149)"># 2、下划线通配符，一次只能匹配一个字符</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> emp <span style="box-sizing: border-box;color: rgb(199, 146, 234)">where</span> name <span style="box-sizing: border-box;color: rgb(199, 146, 234)">like</span> <span style="box-sizing: border-box;color: rgb(195, 232, 141)">'___y'</span>;</span>

查询控制

空值不是字符串，也不是0，一般表示未知的数据、不适用或将在以后添加的数据。使用is null来查询空值。使用is not null查询不为空的值。

mysql> select * from emp where salary is null;

distinct-查询不重复的结果

语法

select distinct 字段名 from 表名;

查询工资不重复的记录

mysql> select distinct salary from emp;

order by-对查询结果排序

desc-降序排列；asc-升序排列

mysql> select * from emp order by name desc;

我是小白，期待和优秀的你一起同行!

小白

2020年10月10日