Mysql基础查询与高级查询

短命女 2023-06-30 02:52 7阅读 0赞

#### 一. 基础查询 ####

##### 1.简单查询 #####

*  select ename from emp\_xu;//单列查询
 *  select empno,ename from emp\_xu;//多列查询
 *  nselect \* from emp\_xu;//查询所有
 *  select empno,ename from emp\_xu where empno=10004;//条件查询

条件中的操作符

= 等于 ，> 大于， >= 大于等于， < 小于， <=小于等于 ，!= <> 不等于

通配符 \* 通常不建议使用，会降低查询速度和影响应用程序的性能

写法顺序

select -> from -> where

执行顺序

from(指定表) -> where（纪录过滤）->select（结果集）

##### 2.查询员工的月薪（salary）和年薪(salary\*12) #####

select ename salary,salary*12 from emp_xu;

可以给表起别名

select e.ename from emp_xu e;

查询员工的月薪

select salary+bonus as s where emp_xu;

##### 3.空值null #####

*  如果存在null，则运算为null
 *  任何数据类型都可以为空
 *  空值参与连接操作结果为空

##### 4.空值处理函数 #####

ifnull（e1,e2）:如果e1为空则取代e2代替

select ename, salary, (salary+ifnull(bonus,0)) as month_salary from emp_xu;

##### 5.插入一个数据 #####

1013 “欧阳锋” 其他为空值

insert into emp\_xu values(1013,“欧阳锋”);

查询员工姓名，职位，无职位的显示：“NO Position”

select ename, ifnull(position,"NO Position") from emp_xu;

##### 6.连接操作 #####

`concat(a,b.....)`

测试：

select concat("a","b") from dual;

select concat("a","b", null) from dual;//结果为空
    select concat("a","b", ifnull(null,"")) from dual;//ab

查询员工信息，要求员姓名和职位连接在一起

select concat(ename,ifnull(position,"no position")) from emp_xu;

查询职位发现有重复

select position from emp_xu;

##### 7.去重 #####

distinct 去重，只能放在select后面

select distinct position from emp_xu;

查询每个部门不重复的职位

select distinct deptno, position from emp_xu;

distinct后面跟的是两个字段，需要对这两个字段进行联合去重，全部列的唯一组合

##### 8.大小写 #####

mysql中默认是不区分大小写的，如果需要区分大小写时，在创建表的时候通过binary标识敏感字段

查询职位为‘Analyst’的职位信息

select ename  position from emp_xu where position = "Analyst";

##### 9.介于两者之间 #####

between … and … 数据为闭区间，临界值可以取到的

并且 and 同时满足

或者 or 满足一个即可

查询薪水大于等于5000并且小于等于10000的员工信息

select * from emp_xu where salary>=5000 and salary<=10000;
    select * from emp_xu where not(salary<5000 or salary >10000);
    select * from emp_xu where salary between 5000 and 10000;

##### 10.in的使用 #####

in(列表项)：**判断等于列表项中的任意一项**

查询职位是“Analyst”或者“Manager”的员姓名和职位

select ename, position from emp_xu where position ="Analyst" or position="Manager";
    select ename,position from emp_xu where position in("Analyst","Manager");
    select ename,position from emp_xu where position in("Analyst","Manager",null);

使用in时列表项中的有空值对结果没有影响，如果职位是空值，该记录不会被查询出来

结论：有空值，对结果没有影响，空值不可用等于或者不等于任何数据作比较

##### 11 模糊查询 #####

*  使用like
 *  占位符 \_表示1个字符
 *  %表示0个或者多个字符

查询员工姓名中包含‘张’字员工信息

select ename from emp_xu where ename like "%张%"；

查询职位中第二个字符是“a”的员工姓名和职位

select ename,position from emp\_xu where position like “\_a%”;

##### 12 is null #####

空值判断，肯定用is null 否定用 is not null

查询哪些员工没有奖金

select ename  bonus from emp_xu where bonus is null;

##### 13 is not null #####

查询哪些人有奖金（不为空）

select ename,bonus from emp_xu where bonus is not null;

##### 14.not in(列表项)：判断不等于全部，要同时满足 #####

查询员工工资不在5000-10000之间的员工信息

select ename ,salary from emp_xu where salary not between 5000 and 10000;

查询不是20号部门和30号部门的员工信息

select ename,deptnpo from emp_xu where deptnpo !=20 and deptnpo!=30;

改动：

select ename, deptnpo from emp_xu where deptnpo not in(20,30);

使用not in 时，列表项中如果有空值，最终一条记录都不会被查询出来，列表项中的空值必须去掉

*  in（列表项）：判断不等于任意一项，列表项中有空值没有影响
 *  not in（列表项）判断不等于所有项，列表项中的空值必须去掉

#### 二.基础查询\_常用函数 ####

单行函数

每一行数据处理后将返回一个结果

**1）数字函数**

round(数字，位数)；用于对数字进行四舍五入处理，保留小数点指定的位数

select round(123.455,2) from dual;

truncate(数字,位数)：用于对数字进行截取，截取到小数点指定位数

select truncate(123.456,2) from dual;

abs(数字)：返回数据的绝对值

rand(): 返回0-1随机数

sqrt(数字)：返回数据的平方根

pow(x,y):返回x的y次方

mod(x,y):返回x除以y

**2）去除空格函数**

trim(字符串)

select concat('(',trim(' abc '),')');

**3）文本处理函数**

upper(小写)：转成大写

lower(大写)：转成小写

length(字符串)：返回字符串字节长度

select ename,length(ename) from emp_xu;

substring(字符串，起始位置，最大字符数量)：返回指定字符串内容

select ename, substring(ename,2,2) from emp_xu;

**4)日期、时间**

date(时间)：返回日期部分

time(时间)：返回时间部分

select date(now());

adddate():增加日期（年，月，周）

select addate(now(),interval 3 day);

第二个参数表示‘天’

select adddate('2020-08-04',3);

data\_add:增加日期

select adddate('2020-08-04', interval 3 day);

重要

date\_format(时间，格式)；格式日期数据

select date_format(now(), '%x-%m-%d');
    select date_format(now(), '%h-%m-%s');

组函数【重点】

多行数据处理后返回一个结果

mysqlcount(): 求记录数  
sum(): 求和  
avg() 平均数  
max(): 求最大  
min:求最小

查询员工表中的记录数

*  select count(empno）from emp\_xu;//13
 *  select count(position）from emp\_xu;//12 会忽略空值

查询员工表中的薪水总和

*  select count(salary) from emp\_xu;

查询员工表中的人数总和，薪水总和，平均薪水

*  select count(empno),count(salary), avg(ifnull(salary,0))from emp\_xu;

查询员工表中的最大薪水，最低薪水

*  select ename, max(ifnull(salary,0)), min(ifnull(salary,0)) from emp\_xu;//mysql中没有报错，数据会产生问题

**总结：**  
count sum avg max min 如果函数中写的是列名，默认都是忽略空值  
count(\*)不忽略空值  
sum\\avg 用于处理数值类型

#### 三. 基础内容\_排序 ####

排序：对查询的结果集进行排序（先有结果集再排序）  
使用：order by 列名  
规则：asc(升序 默认) desc(降序)

**查询员工表中的员工姓名，薪水，薪水由低到高进行排序**

select ename, salary from emp_xu order by salary asc;//升序
    select ename，salary from emp_xu order by salary desc;//降序

**按照部门号升序，要求同一个部门按照薪水降序**  
//多个排序使用’,'隔开  
//使用字段名排序

select deptnpo d, salary s
    from emp_xu 
    where deptnpo is not null 
    order by d asc, s desc;
    
    select deptnpo, salary
    from emp_xu 
    where deptnpo is not null 
    order by 1 asc, 2 desc;

**总结**：排序语句的执行在select之后，因此排序可以使用列名，列别名，函数，表达式，  
数字（查询结果集列的顺序，第一列用1，第二列用2）

#### 四. 基础查询\_分组 ####

使用group by

**查询每个部门的最高和最低薪水，没有部门的不算在内**

select deptnpo,max(salary),min(salary) 
    from emp_xu 
    where deptnpo is null
    group by deptnpo;

**查询每个部门的薪水总和平均薪水要求没有部门的不算在内**

select deptnpo, sum(salary),avg(ifnull(salary,0))
    from emp_xu
    where deptnpo is not null
    group by deptnpo;

**按照职位分组，每个职位的最高薪水，最低薪水，人数总和。要求无职位的不算在内**

select position, max(salary), min(salary), count(*)
    from emp_xu
    where position is not null
    group by position;

ps:  
select后面的内容，要么被组函数包围，要么出现在gruop by 之后  
//group\_concat()处理多个数据

select position,group_concat(ename)
    from emp_xu
    where position is not null
    group by position;

having: 对分组之后的数据再进行过滤

**查询平均薪水大于5000的部门，要求没有部门的不算在内**

select deptnpo, avg(ifnull(salary)) avgsalary
    from emp_xu
    where deptnpo is not null
    group by deptnpo
    having avgsalary>5000;

**查询薪水总和大于20000的部门号和薪水总和，没有部门的不算在内**

select deptnpo, sum(salary) sumsalary
    from emp_xu
    where deptnpo is not null
    group by deptnpo
    having sumsalary>20000

**查询哪些职位的人数超过2个人，没有职位的不算在内。计算每种职位  
的平均薪水并且按照平均薪水升序排序**

select position, count(*) countpos, avg(ifnull(salary,0) avgsalary
    from emp_xu
    where position is not null
    group by position
    having countpos > 2
    order by avgsalary;

总结：  
写法顺序：  
select>from>where>group by>having>order by  
执行顺序  
from(表名)>where(记录过滤)>group by(分组)>having(分组后过滤)>select(结果集)>order by(排序)

#### 五.高级查询\_子查询 ####

*  子查询：一条sql语句当中嵌套select查询语句
 *  非关联子查询：嵌套的子查询是独立语句，不依赖主查询可以单独执行

**1.查询最高薪水是谁**

select max(salary) from emp_xu; 
    select ename, salary from emp_xu where salary = 99999.99;
    //合并
    select ename, salary 
    from emp_xu 
    where salary = (
    	select max(salary) from emp_xu
    );

非关联子查询执行过程:

*  先执行嵌套子查询(独立的sql语句)
 *  返回查询结果作为主查询的条件
 *  再执行主查询

嵌套子查询是一条独立的sql语句，不依赖主查询能单独执行，并且只能执行一次。

**2.查询最低薪水的人是谁**

select ename, salary from emp_xu
    where salary = (
    	select min(salary) from emp_xu
    );

**3.查询谁的薪水比"张无忌"高**

select ename, salary
    from emp_xu
    where salary > (
    	select salary from emp_xu where ename ="张无忌"
    );

**4.研发部有哪些职位**

select position
    from emp_xu
    where deptnpo = (
    	select deptno from dept_xu where dname="研发部门"
    );

**5.查询谁的薪水比"张无忌"高，如果有多个"张无忌"**

//插入一条记录
    insert into emp_xu values(1014,"张无忌","clerk",8000,600,now(),1013,null);
    
    select salary from emp_xu where ename = "张无忌"
    
    //满足大于所有
    select ename,salary 
    from emp_xu
    where salary>all(
    	select salary from emp_xu where ename="张无忌"
    );
    
    //满足大于最大的
    select ename,salary 
    from emp_xu
    where salary>(
    	select max(salary) from emp_xu where ename="张无忌"
    );

**6.查询哪些人的薪水比"张无忌"高，如果有多个"张无忌"**

//满足大于任意一个
    select ename,salary 
    from emp_xu
    where salary>any(
    	select salary from emp_xu where ename="张无忌"
    );
    
    //满足大于最小的
    select ename,salary 
    from emp_xu
    where salary>(
    	select min(salary) from emp_xu where ename="张无忌"
    );

**7.查询谁和"郭靖"同部门，列出除了"郭靖"之外的员工(单值)**

select ename, deptnpo
    from emp_xu
    where deptnpo = (
    	select deptnpo from emp_xu where ename = "郭靖"
    ) and ename != "郭靖";

**8.查询谁和"郭靖"同部门，列出除了"郭靖"之外的员工(多值)**

select ename, deptnpo
    from emp_xu
    where deptnpo in(
    	select deptnpo from emp_xu where ename = "郭靖"
    ) and ename != "郭靖";

`=any等价于in`

**9.查询谁是"张三丰"的下属**  
找员工 ->满足该员工的leader字段等于"张三丰"的员工号

select ename, leader
    from emp_xu
    where leader = (
    	select empno from emp_xu where ename="张三丰"
    );//单值
    
    select ename, leader
    from emp_xu
    where leader in (
    	select empno from emp_xu where ename="张三丰"
    );//多值

非关联子查询中比较运算符选择，根据子查询返回的值所决定的  
单值：= < > !=  
多值：>all =any in >any

1.  **查询每个部门拿最高的薪水的是谁**

分布：
    //查询每个部门最高薪水
    select deptnpo, max(salary)
    from emp_xu
    where deptnpo is not null
    group by deptnpo;
    //根据部门，最高薪水找人
    select deptnpo, ename, salary 
    from emp_xu
    where (deptnpo,salary) = any ();
    
    合并：
    select deptnpo, ename, salary 
    from emp_xu
    where (deptnpo,salary) = any (
    	select deptnpo, max(salary)
    	from emp_xu
    	where deptnpo is not null
    	group by deptnpo
    );

//多列：使用多列时，比较规则相同  
//多值：主查询不关心子查询返回的字段，只关心查询返回单值还是多值

**11.查询哪些部门的人数比30号部门人数多**

select deptnpo, count(*)
    from emp_xu
    where deptnpo is not null
    group by deptnpo
    having count(*)>(
    select count(*) from emp_xu
    where deptnpo = 30
    );

**12.查询哪些部门的平均薪水比20号部门平均薪水高**

select deptnpo, avg(ifnull(salary,0))
    from emp_xu
    where deptnpo is not null
    group by deptnpo
    having avg(ifnull(salary,0))>(
    	select avg(ifnull(salary,0)) from emp_xu where deptnpo = 20
    );

**13.查询员工所在部门的平均薪水大于5000的员工姓名和职位**

select ename,position,deptnpo
    from emp_xu
    where deptnpo in(
    	select deptnpo
    	from emp_xu
    	where deptnpo is not null
    	group by deptnpo
    	having avg(ifnull(salary,0)) > 5000;
    );

**14.查询哪些员工的薪水是本部门的平均薪水值**

select ename, deptnpo, salary
    from emp_xu
    where (deptnpo,salary) in (
    	select deptnpo, avg(ifnull(salary,0))
    	from emp_xu
    	where deptnpo is not null
    	group by deptnpo;
    );

##### 关联子查询 #####

嵌套子查询不是独立语句依赖主查询

**1.查询哪些员工的薪水比本部门平均薪水值低**

select ename, salary,deptno
    from emp_xu e
    where salary < (
    	select avg(ifnull(salary)) from emp_xu where deptno = e.deptno
    );

e.deptno表示动态的数据。由主查询传递过来的

执行过程：先执行主查询（传递数据）=> 执行嵌套子查询 => 返回查询结果（主查询条件）=> 再执行主查询

**2,查询哪些人有下属**

找领导，满足该员工的empno等于别人的leader

非关联

select ename,empo from emp_xu
    where empo in(
        select leader from emp_xu
    );

关联

select ename,empo,leader from emp_xu e
    where exists(
    	select 1 from emp_xu where leader = e.empo
    );

说明：

exists用于判断子查询有没有数据返回，如果满足关系则有数据返回，不满足则没有数据返回。

不关心子查询返回的结果，子查询中select后面写什么都可以，通常写1

**3 查询哪些人没有下属**

非关联

select ename,empo from emp_xu
    where empo not in(
        select leader from emp_xu
      	where leader is not null
    );

关联

select ename,empo,leader from emp_xu e
    where not exists(
    	select 1 from emp_xu where leader = e.empo
    );

**4.查询哪些部门有员工**

非关联

select deptno, dname from dept_xu
    where deptno in(
    	select deptno from emp_xu
    );

关联

select deptno, dname from dept_xu d
    where exists(
    	select 1 from emp_xu e
        where e.deptno = d.deptno
    );

**5.查询哪些部门没有员工**

非关联

select deptno, dname from dept_xu
    where deptno not in(
    	select deptno from emp_xu
    );

·关联

select deptno, dname from dept_xu d
    where not exists(
    	select 1 from emp_xu e
        where e.deptno = d.deptno
    );

#### 六. 组合查询 ####

组合查询由两条或者两条以上的select 语句所组成的，中间用`union`分隔开

组合查询的前提条件，两个结果集必须有相同结构（列的个数，顺序，类型相同）

**查询10号部门的员工姓名和薪水**

select ename,salary from emp_xu where deptno = 10;

**查询薪水大于6000的员的姓名和薪水**

select ename,salary from emp_xu where salary>6000

合并

select ename,salary from emp_xu where deptno = 10
    union 
    select ename,salary from emp_xu where salary>6000;

`union all`是所有不去重，`union`是去重的

改动：结构不相同,查出的语句是有误的

select ename,salary from emp_xu where deptno = 10
    union 
    select ename,position from emp_xu where salary>6000;

组合查询中只能使用一个order by，跟在最后一个select后面

select ename,salary from emp_xu where deptno = 10
    union 
    select ename,salary from emp_xu where salary>6000
    order by salary;

##### 七. mysql数据库分页查询 #####

`limit`：限制查询的记录数

`select 字段 from 表 limit 数量；`

常用：

`select 字段 from 表 limit 开始,数量；`

select empo,ename from emp_xu limit 4;
    
    select empo,ename from emp_xu limit 1,4;

注意：“开始”表示每页开始的下标，从0开始

找规律

//查询每页显示5条记录

第1页 0-4 limit 0,5

第2页 5-9 limit 5,5

第3页 10-14 limit 10,5

第4页 15-19 limit 15,5

公式：

page：第几页

pagesize：每页的记录数

每页的开始下标：

`int begin = （page- 1）*pagesize`

mysql分页查询的核心sql语句

select * from emp_xu limlit begin,pagesize;

Mysql基础查询与高级查询

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL-基础查询

相关 MySQL高级查询操作

相关 Mysql基础查询与高级查询

相关 mysql基础查询

相关 MySQL高级查询与编程笔记 • 【第3章子查询】

相关 MySQL高级查询与编程笔记 • 【目录】

相关 MySQL数据库基础——高级查询1

相关 mysql基础—查询

相关 MySQL数据高级查询之连接查询、联合查询、子查询

相关 MySQL数据库 : 高级查询