Mysql数据库原理与应用第九章-CSDN博客

本文链接：https://blog.csdn.net/2401_87883125/article/details/144470937

第九章 SQL语言之DQL

查询操作

create database 数据库名 charset utf8;

create database if not exists 数据库名 charset utf8;

drop database 数据库名;

drop database if exists 数据库名;

1.简介 1.1 语法

select 列名 from 表名;

select 列名1,列名2,... from 表名;

select 列名1 别名1，列名2 别名2，.... from 表名;

示例:

1查询所有雇员的姓名

select ename from emp;

select ename,job,hiredate from emp;

select * from emp;

select ename xm,job zw,hiredate rzsj from emp;

select ename "姓名",job "职位",hiredate "入职时间" from emp; select empno,ename,sal your salary from emp;----语法错误别名中有空格，需要使用双撇号

select empno,ename,sal "your salary" from emp;

1.2 用法

字符串连接concat()

示例: 编号为7369的雇员，姓名为smith,职位为clerk

select concat("编号为",empno,"的雇员，姓名为",ename,",职位为",jo

四则运算 + - * / 例:查询雇员的姓名和年薪?

select ename "雇员姓名", sal*12 "年薪" from emp;

select ename "雇员姓名", (sal+comm)*12 "年薪" from emp; ---- select ename "雇员姓名", (sal+ifnull(comm,0))*12 "年薪" from

在MySQL中，null与任何值进行运算，结果都为null。例:查询所有的职位

2.限定查询语法:

2.1 比较运算符

select job from emp;-----有重复值 select distinct job from emp;

select 列名1,列名2,... from 表名

where 条件;

> > = < <= = !=或<> >>

> > ```

> > ``` 例:查询工资大于1500的雇员信息

```mysql

select * from emp where sal>1500;

select * from emp where sal>=1500;

例:查询雇员编号不是7369的雇员信息 select * from emp where empno!=7369;

例:查询姓名是smith的雇员编号，姓名，工资和入职时间。

select empno,ename,sal,hiredate from emp where ename='smith'; 注:字符串要用单撇号或双撇号括起来，同时MySQL中不区分大小写

2.2 null或not null

例:查询每月可以获得奖金的雇员信息?

select * from emp where comm is not null;

select * from emp where comm is null;

注:判断是否为null时使用的是is,不能使用比较运算符。

2.3 and

例:查询基本工资大于1000，并且可以获取奖金的雇员姓名、工资、奖金 select ename,sal,comm from emp where sal>1000 and comm is not

2.4 or

例:查询从事销售工作，或工资大于等于2000的雇员信息?

select * from emp where job = "salesman" or sal>=2000;

2.5 not

例:查询从事销售工作，并且工资不小于1500的雇员编号，姓名、职位和入职时间

2.6 between ...and... 在...与...之间例:查询基本工资大于1500，但小于3000的雇员信息

select empno,ename,job,sal,hiredate from emp where job != "sal

select empno,ename,job,sal,hiredate from emp where not(job = "

select * from emp where sal>1500 and sal<3000;

select * from emp where sal between 1500 and 3000;

select * from emp where sal>=1500 and sal<=3000;

注:between ... and ...包含临界值例:查询1981年入职的雇员编号、姓名、入职时间、所在部门编号?

select empno,ename,hiredate,deptno from emp where hiredate bet 注意:日期必须使用单撇号或双撇号括起来

2.7 in 或 not in

例:查询编号为7369、7499、7788的雇员信息

例:查询姓名为smith，allen，king的雇员编号、姓名、入职时间 select empno,ename,hiredate from emp where ename in ('smith','

2.8 like

用来进行模糊查询，需要结合通配符一起使用

select * from emp where empno='7369' or empno ='7499' or empno

select * from emp where empno in ('7369','7499','7788');

常用的通配符:

% 匹配任意长度的字符

_ 只能匹配单个字符例:查询雇员姓名以S开头的雇员信息

select * from emp where ename like 's%'; 例:查询雇员姓名中包含M的雇员信息

select * from emp where ename like '%M%'; 例:查询从事销售工作，并且姓名长度为4个字符的雇员信息

select * from emp where job='salesman' and ename like '____'; 例:查询1981年入职的雇员编号、姓名、入职时间、所在部门编号

select empno,ename,hiredate,deptno from emp where hiredate lik

3. 排序

3.1 语法:

select 列名1,列名2,...

from 表名

where 条件

order by 排序字段1 asc|desc,排序字段2 asc|desc...;

默认按升序排列;

3.2 示例

例:查询所有雇员信息，按工资由低到高进行排序

select * from emp order by sal; 例:查询部门10的雇员信息，按工资由高到低进行排序，如果工资相同，

则按入职时间由早到晚进行排序。

select * from emp where deptno=10 order by sal desc,hiredate; 例:查询雇员编号、姓名、年薪按年薪由高到低排序

select empno,ename,(sal+ifnull(comm,0))*12 as '年薪' from emp

多表查询

1.简介同时从多张表中查询数据，一般来说多张表之间都会存在某种关系

2.基本用法 2.1 语法

select 列名1,列名2,...

from 表名1 别名1,表名2 别名2,...

where 条件

order by 排序字段1 asc|desc,排序字段2 asc|desc...;

例:将emp表和dept表进行多表查询(笛卡尔积)

select * from emp,dept ;-- 笛卡尔积，在生产环境中，应尽量避免

通过两张表的关联字段进行比较，去掉笛卡尔积。多表查询时一般都会存

在某种关系。

select * from emp,dept where emp.deptno=dept.deptno;

2.2 示例例:查询雇员编号、雇员姓名、工资、所在部门名称及位置。

select empno,ename,sal,dname,loc

from emp,dept

where emp.deptno=dept.deptno;

select empno,ename,sal,dname,loc

from emp e,dept d

where e.deptno=d.deptno;

例:查询雇员姓名、工资、入职时间、所在部门编号、部门名称。

select ename,sal,hiredate,d.deptno,dname

from emp e,dept d

where e.deptno=d.deptno;-- 如果多张表中出现同名的列，在查询时需要

select e.ename,e.sal,e.hiredate,d.deptno,d.dname from emp e,dept d

where e.deptno=d.deptno;-- 开发时通用写法

提示:对于两个表中都有的字段，我们一般选择父表的。例:查询雇员姓名、雇员工资、领导姓名，领导工资。

子查询

1、简介一个查询嵌套着另一个查询，称为子查询

子查询必须放在小括号中子查询可以出现在任意位置，如select、where、having等

指

2、基本用法

2.1 语法

select (子查询) from (子查询) 别名 where (子查询) group by

having (子查询)

2.2 示例

例:查询工资比7566雇员工资高的员工信息?

-- 使用连接查询

select e2.*

from emp e2,emp e1

where e1.empno=7566 and e2.sal>e1.sal

-- 使用子查询 select * from emp where sal>(

select sal from emp where empno=7566

);

例:查询工资比部门30员工的工资高的雇员信息? select sal from emp where deptno=30;

select *

from emp

where sal>(

select sal from emp where deptno=30 ) -- 错误的

注意:将子查询与比较运算符一起使用时，必须保证子查询返回的结果不能多于1个

例:查询雇员的编号、姓名、部门名称。

-- 使用多表连接

select e.empno,e.ename,d.dname

from emp e,dept d

where d.deptno=e.deptno;

-- 使用子查询

select empno,ename,(select dname from dept where deptno=e.dept from emp e;

总结:

一般来说，多表连接查询可以使用子查询替换，但有的子查询不能使用

多表连接查询来替换

子查询的特点:灵活、方便，一般常作为增、删、改、查询操作的条

件，适合于操作一个表的数据

多表连接查询更适合于查看多表中的数据

3、子查询的分类可以分为三列:

单列子查询

返回单行单列，使用频率最高

多行子查询

返回多行单列

多列子查询

返回单行多列或多行多列

3.1单列子查询例:查询工资比7654雇员工资高的，同时又与雇员7900从事相同工作的雇

员信息?

select *

from emp

where sal > (

select sal from emp where empno=7654

) and job = (

select job from emp where empno=7900

);

例:查询工资最低的雇员的姓名、职位和工资?

select ename,job,sal

from emp

where sal = (

select min(sal) from emp

);

例:查询工资高于公司平均工资的雇员信息?

select *

from emp

where sal>(

select avg(sal) from emp

);

例:查询每个部门的编号和最低工资，要求最低工资大于等于部门30的最低工资?

select deptno,min(sal)

from emp

group by deptno

having min(sal)>(

select min(sal) from emp where deptno=30

);

例:查询部门的名称、部门的员工数、部门的平均工资、部门的最低收入

雇员的姓名。

-- 拆分

select deptno,count(empno),avg(sal),min(sal) from emp

group by deptno;

-- 方式1:使用子查询 select

(select dname from dept where deptno=e.deptno) dname,

count(empno),

avg(sal),

min(sal),

(select ename from emp where sal=min(e.sal)) ename

from emp e

group by deptno;

-- 方式2:使用多表连接查询

select d.dname,temp.cnt,temp.avg,temp.min,e.ename from (

select deptno,count(empno) cnt,avg(sal) avg,min(sal) min f

) temp,dept d,emp e

where d.deptno=temp.deptno and e.sal=temp.min

例:查询平均工资最低的职位及平均工资?

-- 拆分

select min(t.avg) from (

select avg(sal) avg from emp group by job

) t;

select job,avg(sal)

from emp

group by job

having avg(sal)=(

select min(t.avg)

3.2多行子查询对于多行子查询，可以使用如下三种操作符

in 例:查询所在部门编号大于等于20的雇员信息?

select * from emp where deptno>=20;

select *

from emp

where deptno in(

select deptno from dept where deptno>=20

);

例:查询工资与部门20中的任意员工相同的雇员信息。

select *

from emp

where sal in(

select sal from emp where deptno=20

);

any|some

三种用法:

from (

select avg(sal) avg from emp group by job

)t );

=any|some 与任意一个相同，此时与in操作符功能一样 >any|some 只要比结果中最小的大即可

<any|some 只要比结果中最大的小即可

select *

from emp

where sal =any(

select sal from emp where deptno=20

);

all

两种用法:

>all 比结果中最大的值要大 <all 比结果中最小的值要小

select *

from emp

where sal >all(

select sal from emp where deptno=20

);

3.3 多列子查询多列子查询一般出现在from子句中，作为查询结果集

例:在所从事销售工作的雇员中找出工资大于1500的雇员?

select temp.*

from (select * from emp where job="salesman") temp

where temp.sal>1500;

分页查询

1、limit关键字作用:用来限制查询返回的记录数

语法规则:

select 列名1,列名2,...

from 表名1 别名1,表名2 别名2,...

where 条件

group by 分组字段

having 分组限定条件

order by 排序字段1 asc|desc,排序字段2 asc|desc... limit [参数1,] 参数2

可以接收一个或两个数字

参数1用来指定起始行的索引(下标)，索引是从0开始，即第一行的索引或下标为0

能数2用来指定返回的记录条数

例:查询工资最高的前3名员工的信息?

select * from emp order by sal desc limit 0,3;

select * from emp order by sal desc limit 3; -- 如果省略参数1

例:查询工资大于1000的第4-8个雇员的信息。 select * from emp where sal>1000 limit 3,5;

例:查询工资最低的用户?

select * from emp order by sal limit 1; 2、分页

例:每页显示4条(pagesize每页的大小),显示第3页的内容(pageindex页码)

注意:在mysql中limit后面的参数不能包含任何运算，实际开发时都是在编程语言中进行计算，然后将结果发送给数据库执行。