MySQL查询

Tina@Qian

已于 2023-11-01 14:13:01 修改

阅读量80

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

于 2023-06-16 09:15:56 首次发布

本文链接：https://blog.csdn.net/qq_46192417/article/details/131214336

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一、MySQL查询

1.1 实例表格结构：

emp表（员工信息表）：

Empno	员工编号
Ename	员工姓名
Job	员工职位
Mgr	员工的上级编号
Hiredate	员工入职时间
Sal	员工薪水
Comm	员工奖金
Deptno	员工部门编号

dept表（部门信息表）：

Deptno	部门编号
Dname	部门名称
Loc	部门地址

salgrade表（薪水等级表）：

Grade	等级编号
Losal	金额下限
Hisal	金额上限

sql文件可直接导入使用：

链接：https://pan.baidu.com/s/1-WiI3AkL_pVk_S1_sF19uA?pwd=20we
提取码：20we
--来自百度网盘超级会员V4的分享

1.2 基本查询：

-- （1）查询所有行所有列数据（正式情况一定不可以用*）：
select  *  from  表名;

-- （2）查询指定列数据(注意：查询指定列的效率高于查询所有列)
select  列名1,列名2,…  from  表名;

-- （3）查询且不包含重复数据（distinct，注意：该操作是以行为单位，如果作用于多列，必须所有列重复才能去掉）
select  distinct  job  from  emp;

-- （4）指定列别名（as可以省略）
select  sal*12  as  年薪  from emp; 
select  sal*12 年薪  from emp; 

-- 如果别名中含有特殊字符（如空格、单引号等），则使用双引号“”将别名引起来。
select  sal*12  as  "年'  '薪"  from emp;
select  sal*12  "年'  '薪"  from emp;

-- （5）concat方法：用于字符串拼接
select concat('员工',ename,'的薪水是',sal,'元') as 员工信息 from emp;

-- （6）加法运算符（+）：用于加法运算
select SAL+COMM month_sal , ENAME from emp;

-- （7）where条件筛选
select * from emp where deptno=10;

-- 注意：关键字不区分大小写，数据值区分大小写(例：查询SMITH的信息)
select * from emp where ename=‘SMITH’;

-- 例子: 模糊查询
select * from emp where ename like '%S%'

-- （8）排序（order by），order by子句应为查询语句的最后一个语句，可使用升序 asc，降序 desc；默认为升序

-- ① 单列排序：查询员工信息，按部门编号降序排序
select * from emp order by deptno desc;

-- ② 多列排序（在第1列排序后，对于排位相同的记录，继续使用第2列排序）：查询员工信息，按部门编号降序排序，如果部门相同，则按薪水升序排序
select * from emp order by deptno desc,sal asc;

-- （9）分组查询（group by），查询的字段只能是分组的条件，如果要使用其他字段必须要使用聚合函数
select deptno ,sum(sal) from emp group by deptno ;

-- 在分组完毕之后，根据条件进行筛选使用having
select deptno ,avg(sal) as avg_sal from 
                                    emp group by deptno 
                                    having avg_sal >2000;

查询语法（[ ]表示可选项）：

select [distinct] * | 列名

from 表名（可以写多个表名字，以逗号隔开）

[where 条件] （可以写多个条件，以and 或者是 or 进行分隔）

[group by 列名 [having 条件]] （可以写多个条件，以and 或者是 or 进行分隔）

[order by 列名 asc | desc]; （可以写多个列名，以逗号隔开）

1.3 连表查询：

是一种通过关联两个或多个数据库表的相同字段进行查询的方法。具体地说，它会在查询时将两个或多个表中的数据合并成一个结果集，从而可以根据多个条件查询出符合要求的数据。

连接类型	定义	图示	示例
内连接（INNER JOIN）	返回两个表中匹配的行		select A.c1,B.c2 from A inner join B on A.c3 = B.c3;
左外连接（LEFT JOIN）	返回左表中所有的行以及右表中匹配的行，若右表中没有匹配的行则返回 NULL		select A.c1,B.c2 from A left join B on A.c3 = B.c3;
右外连接（RIGHT JOIN）	返回右表中所有的行以及左表中匹配的行，若左表中没有匹配的行则返回 NULL		select A.c1,B.c2 from A right join B on A.c3 = B.c3;
完全外连接（FULL OUTER JOIN）	返回所有的行，若左表或右表中没有匹配的行，则返回 NULL		select A.c1,B.c2 from A full join B on A.c3 = B.c3;

交叉连接：生成笛卡尔积，不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行一一匹配

select ENAME,emp.DEPTNO,dept.DEPTNO from emp join dept;
select ENAME,emp.DEPTNO,dept.DEPTNO from emp ,dept;

筛选笛卡尔积：直接加where条件

-- 例：查询员工'KING'薪水及所在部门的名称和ID
select e.SAL,d.DNAME, e.DEPTNO from 
                               emp e left join dept d on d.DEPTNO = e.DEPTNO 
                               where e.ENAME = 'KING';

注意：对笛卡尔积筛选时，在where条件中应先使用等于操作，然后再使用其他比较操作

自连接：指表与其自身进行连接，同一表中的字段与字段之间需要存在引用关系。注意：自连接时需要给表指定别名

-- 查询并显示指定员工的上级领导的姓名
select concat(e.ENAME,'的上级是：', m.ENAME) from 
                                            emp e join emp m on e.MGR = m.EMPNO
                                            where e.ENAME = 'SMITH';
-- 查询领导有多少下属
select  e1.mgr,e2.ENAME,count(*) from
                                 emp e1 INNER JOIN emp e2 on e1.MGR = e2.EMPNO
                                 GROUP BY e1.mgr

多表查询可以直接使用表1,表2, where 的方式查询：

select * from emp,dept
         where emp.DEPTNO = dept.DEPTNO

1.4 子查询：

子查询是一种在 SQL 语句中嵌套一个 SELECT 语句的查询方式。子查询可以独立于主查询而存在，它可以被用作 WHERE 或 HAVING 子句、JOIN 表达式、INSERT 语句等场合中的子表，也可以与主表进行比较或联合查询。需要注意的几点：① 子查询可以嵌套多层，② 需要用圆括号( )括起来，③ 子查询必须放在比较操作符的右边。

子查询分类：

（1）单行子查询：子查询的只产生一行结果来作为主查询的条件进行查询，用于单行子查询的操作符有“=，>，>=，<，<=，<>”

-- 查询并显示和SMITH同部门的员工
select emp.ENAME,emp.DEPTNO, e.ENAME, e.DEPTNO from 
                                               emp join emp e on emp.DEPTNO = e.DEPTNO
                                               where emp.ENAME = 'SMITH' and e.ENAME <> 'SMITH';

（2）多行子查询：子查询会产生多行结果来作为主查询的条件来进行查询，用于多行子查询的操作符有“IN, NOT IN”

-- 查询并显示和部门10员工同岗位的员工
select * from emp where job in(select job from emp where deptno = 10);

（3）多列子查询：子查询会产生多列结果，然后作为一个新表（要取别名）被查询，主查询的where子句中有多个列，主查询where子句中的列与子查询中返回的列必须匹配

-- 查询与SMITH的部门和岗位都相同的员工信息
select * from emp where 
         job = (select job from emp where ename='SMITH')
         and deptno = (select deptno from emp where ename='SMITH')

（4）在DML中使用子查询：在 DML（数据操作语言）中使用子查询可以灵活地操作数据库，它可以在一个 SQL 语句中实现多个操作，包括插入、更新、删除等。

-- 添加新员工，员工部门为SALES（Insert中使用子查询：不需要使用values）
insert into emp
    (EMPNO,ENAME,job,MGR,HIREDATE,SAL,DEPTNO)
    (select distinct 7888,'LUCY','SALESMEN',7698,'19851203',500,deptno from emp where 
    deptno = 30)

-- 删除薪水比员工ALLEN低的员工信息（Delete中使用子查询）
delete from emp where sal < (select sal from emp where ename='ALLEN')

-- 将部门为ACCOUNTING的员工的薪水增加200（Update中使用子查询）
update emp set salary = salary + 200  where deptno = 
      (SELECT deptno FROM dept where name = 'ACCOUNTING');

1.5 limit：可用来分页

-- 语法结构：SELECT * FROM table limit m,n
-- m是从第几行开始
-- n是查询几条数据

-- 查询第二行数据到第三行数据的结果
select * from tb_sal limit 1,2; -- 从0开始计算第一行

-- 查询部分工资最高的（先进行排序操作）
select ENAME,sal from emp order by sal desc limit 0,1;
-- limit 0,1  可以被limit 1取代

分页操作:（假设页数是page，每页显示的数量是num，分页的公式 n=num, m = (page-1)*num ）

-- 第一页 从0开始 0= 0*3
select ENAME,sal from emp order by sal desc limit 0, 3;
-- 第二页 从3开始 3= 1*3
select ENAME,sal from emp order by sal desc limit 3, 3;
-- 第三页 从6开始 6= 2*3
select ENAME,sal from emp order by sal desc limit 6, 3;

1.6 比较运算符：

（1）基本比较运算符：<、 >、 = 、<>（不等于）

（2）模糊运算符like ：① '%'表示匹配任意个字符 ② ‘_’表示匹配单个字符

（3）In(值1,值2)：表示查询在指定的值列表范围内的数据

（4）is null：查询为空的结果

（5）between…and...：判断是否在两值之间

-- 查询工资高于3000的员工
select * from emp where SAL>3000;

-- 查询姓名以S开始的员工
select * from emp where ENAME like 'S%';

-- 查询员工名字第3个字符是O的员工
select * from emp where ENAME like '__O%';

-- 查询员工号为：7369、7499、7521的员工
select * from emp where EMPNO in(7369,7499,7521);

-- 查询工资在800到1600之间的员工
select * from emp where SAL between 800 and 1600;

与not连用表示取否定：not like，not in，is not null，not between

1.7 逻辑运算符：

连接多个比较运算的结果以生成一个或真或假的结果，包括and、or、not

-- 查询工资大于500或者职位为manager，并且名字以S开头的员工
select * from emp where (SAL>500 or JOB = 'manager') and ENAME like 'S%';

1.8 UNION（联合操作）：

可以将两个查询结果联合成一个结果，前提是列的数量必须一致，查询结果以第一个查询的结果作为列名称。

select * from dept
union
select EMPNO, ENAME, DEPTNO from emp

默认情况下union 去除掉重复的行，但是如何不需要去掉重复行请使用union all，union all只是合并查询结果，并不会进行去重和排序操作，在没有去重的前提下，使用union all的执行效率要比union高。

1.9 常用函数：

（1）字符串函数：CONCAT（拼接字符串）、TRIM（裁剪）、SUBSTRING（截取字符串）

（2）数字函数：sum（求和）、count（计数）

（3）日期函数：CURDATE、NOW（获取当前时间）、DATEDIFF(d1,d2)（两者之间的天数）、PERIOD_DIFF(period1, period2)（月份查）、DATE_FORMAT(d,f)（格式转行）、DATE_ADD(d，INTERVAL expr type)（计算起始日期 d 加上一个时间段后的日期）、QUARTER(d)（季度）

（4）高级函数：CASE表达式、IF实现的二选一、ISNULL判断为空

1.10 视图：

是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。（视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。）

-- 创建视图的语法
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

-- 创建案例
create view empview2 as
    select ENAME,SAL,d.DNAME from emp left join dept d
        on d.DEPTNO = emp.DEPTNO ;

-- 可以直接对视图(View)进行查询
select * from empview2;

-- 删除视图
drop view empview2;

-- 查看视图创建的详细
SHOW CREATE VIEW empview1;

-- 查看当前库中所有的视图
SHOW FULL TABLES WHERE Table_Type = 'VIEW';

和临时表的区别：

（1）CREATE TEMPORARY TABLE可以创建一个临时表，可以将查询的数据真实赋值到临时表中

（2）临时在本次session会话执行完毕之后，临时表就会被释放了