mysql查询语句

DQL：数据查询语言（Data Query Language, DQL）

示例使用表

emp员工表

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 1980-12-17 800.00 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 NULL 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 NULL 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 NULL 10
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000.00 NULL 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-05-23 1100.00 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950.00 NULL 30
7902 FORD ANALYST 7566 1981-12-03 3000.00 NULL 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 NULL 10

salgarde 工资等级表

GRADE LOSAL HISAL
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

dept 部门表

DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

1. 单表查询

=

-- 查询薪资等于800的员工姓名和编号？
select EMPNO,ENAME from emp where SAL=800;

-- 查询SMITH的编号和薪资？
select EMPNO,SAL from emp where ENAME ='SMITH';

<> !=

-- 查询薪资不等于800的员工姓名和编号？
select EMPNO,ENAME,SAL from emp where SAL!=800;
select EMPNO,ENAME,SAL from emp where SAL<> 800;

-- 查询薪资小于等于3000的员工姓名和编号？
select EMPNO,ENAME,SAL from emp where SAL<=3000;

between and

  • between and是一个闭区间，包含两边内容。
-- 查询薪资在2450和3000之间的员工信息？包括2450和3000
select empno,ename,sal from emp where sal between 2450 and 3000;

select empno,ename,sal from emp where sal >=2450 and sal <=3000;

is null

  • 数据库中查询null值不能使用=
-- 查询哪些员工的津贴/补助为null？
select empno,ename,comm from emp where comm is null;

-- 查询哪些员工的津贴/补助不为null？
select empno,ename,comm from emp where comm is not null;

and or

  • and和or同时出现，and优先级较高。如果想让or先执行，需要加“小括号”,以后在开发中，如果不确定优先级，就加小括号就行了。
-- 查询薪水大于2500并且部门编号是10或者20的员工信息
select  * from emp where sal > 2500 and (deptno = 10 or deptno = 20);

in not in

-- 查询工作岗位是MANAGER和SALESMAN的员工？
select * from emp where job in ('MANAGER','SALESMAN');
select * from emp where job='MANAGER' or job='SALESMAN';


-- 查询工作岗位不是MANAGER和SALESMAN的员工？
select * from emp where job not in ('MANAGER','SALESMAN');

like

  • %匹配任意多个字符 _下划线：任意一个字符。
-- 找出名字中含有O的y=员工信息？
select * from emp where ename like '%O%';

-- 找出名字中有"_"的？
-- select * from emp where ename like '%_%';  错误
select * from emp where ename like '%\_%';  --加转义字符

distinct

  • 把查询结果去除重复记录【distinct】
  • distinct只能出现在所有字段的最前方。
  • distinct出现在job,deptno两个字段之前，表示两个字段联合起来去重。只有两个字段都相同的数据才去重
select distinct job from emp;
select distinct job,deptno from emp;

order by

  • 默认是升序asc
-- 查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话，再按照名字升序排列
select ename,sal from emp order by sal asc,ename asc;

limit

  • limit作用：将查询结果集的一部分取出来。通常使用在分页查询当中。
  • 完整用法：limit startIndex, length startIndex是起始下标，length是长度。
  • 起始下标从0开始。缺省用法：limit 5; 这是取前5.
-- 按照薪资降序，取出排名在前5名的员工？
select * from emp order by sal desc limit 0,5;

-- 取出工资排名在[3-5]名的员工？
select * from emp order by sal asc limit 2,3;  -- 从索引2开始，取3条数据
  • 每页显示pageSize条记录
第pageNo页：limit (pageNo - 1) * pageSize , pageSize

2.函数

2.1 单行处理函数

  • 单行处理函数的特点：一个输入对应一个输出。
  • 多行处理函数特点：多个输入，对应一个输出！

lower

-- 将名字全部转为小写
select lower(ename) as low_ename from emp;

upper

-- 将名字全部转为大写
select upper(ename) as up_ename from emp;

substr

  • substr( 被截取的字符串, 起始下标,截取的长度)
  • 注意：起始下标从1开始，没有0。
-- 查询出名字中第二个字母
select substr(ename,1,1) as new_ename from emp;

concat

-- 将员工姓名和薪水放在一个列展示，中间用：隔开
select concat(concat(ename,':'),sal) as result from emp;

length

-- 查询员工姓名的长度
select length(ename) from emp;

round 四舍五入

select round(1236.567, 0) as result from emp; -- 保留0个小数 1237
select round(1236.567, 1) as result from emp; -- 保留1个小数 1236.6
select round(1236.567, 2) as result from emp; -- 保留2个小数 1236.57
select round(1236.567, -1) as result from emp; -- 保留到十位。1240

rand 生成0-1随机数

-- 生成一个100以内随机数
select round(rand()*100,0) from emp;

ifnull 将空值看做什么值

  • 在所有数据库当中，只要有NULL参与的数学运算，最终结果就是NULL。
-- 查询员工的津贴，如果为null，查询结果为0
select ename,ifnull(comm,0) from emp;

2.2 分组函数

  • count 计数
  • sum 求和
  • avg 平均值
  • max 最大值
  • min 最小值
  • 分组函数在使用的时候必须先进行分组，然后才能用。 如果你没有对数据进行分组，整张表默认为一组。
-- 找出最高工资员工？
select ename，max(sal) as max_sal from emp;

-- 计算所有员工工资和.
select sum(sal) from emp;

-- 计算平均工资
select avg(sal) from emp;

-- 查找员工总数
select count(ename) from emp;

分组函数使用事项

  • 分组函数自动忽略NULL，你不需要提前对NULL进行处理。

    • 比如查询津贴comm总数，null值不会被计算在内，查询结果是4。

  • 分组函数中count(*)和count(具体字段)有什么区别？

    • count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。
    • count(*)：统计表当中的总行数。（只要有一行数据count则++） 因为每一行记录不可能都为NULL，一行数据中有一列不为NULL，则这行数据就是有效的。

  • 分组函数不能够直接使用在where子句中。

    • 找出比最低工资高的员工信息。
    • select ename,sal from emp where sal > min(sal); 表面上没问题，运行一下？ ERROR 1111 (HY000): Invalid use of group function

3.分组查询（重要）

group by

  • 在一条select语句当中，如果有group by语句的话，select后面只能跟：参加分组的字段，以及分组函数。其它的一律不能跟。
  • 执行顺序：先从emp表中查询数据。根据job字段进行分组。然后对每一组的数据进行sum(sal)；
-- 找出每个工作岗位的工资和？
select job,avg(sal) from emp group by job;

-- 找出每个部门的最高薪资
selec deptno,max(sal) from emp group by deptno;

联合分组

-- 找出每个部门，不同工作岗位的最高薪资？
select max(sal) from emp group by deptno,job;


> having

- 使用having可以对分完组之后的数据进一步过滤。
- having不能单独使用，having不能代替where，having必须和group by联合使用。

```sql
-- 找出每个部门平均薪资，要求显示平均薪资高于2500的。
select avg(sal) from emp group by deptno having avg(sal) >2500;

-- 找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除MANAGER岗位之外，要求按照平均薪资降序排。
select avg(job) from emp group by job having avg(job) > 1500 and (job not in ('MANAGER') order by avg(job) desc)

4. 联表查询

  • 当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的乘积，这种现象被称为：笛卡尔积现象。
  • 从一张表中单独查询，称为单表查询。emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字。这种跨表查询，多张表联合起来查询数据，被称为连接查询。

emp员工表

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 1980-12-17 800.00 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 NULL 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 NULL 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 NULL 10
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000.00 NULL 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-05-23 1100.00 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950.00 NULL 30
7902 FORD ANALYST 7566 1981-12-03 3000.00 NULL 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 NULL 10

salgarde 工资等级表

GRADE LOSAL HISAL
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

dept 部门表

DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

内连接

  • 等值连接
  • 非等值连接
  • 自连接

外连接

  • 左连接
  • 右连接

全连接

  • 基本不用
posted @ 2022-03-15 10:38  初夏那片海  阅读(71)  评论(0)    收藏  举报