一、mysql的查询

sql语句的分类

MySQL语句大概有以下几种分类

DQL：查数据。如select
DDL：数据定义语言。凡是(create,drop,alter)的都是DDL，操作的是表的结构，不是数据
DML：数据操作语言，对表中的数据进行增删改的都是DML（insert,delete,update）
TCL：事务控制(commit)、事务回滚(rollback)
DCL：数据控制语言。授权（grant）、撤销权限（revoke）

1.1 基础语法

# 查看当前实例里有哪些数据库
show databases;
# 进入某个数据库
use test;
# 查看当前在哪个数据库
select database();
# 创建数据库
create database d_box;
# 查看当前数据库下有哪些表
show tables;

#每个表都有字段，相当于Excel的列名。字段里包含此列的数据类型、约束等属性。

#查看表的结构：
#该命令可查看表字段名、约束、类型等信息。见下图
desc 表名;

#查看表中的数据：
select * from 表名; (星号代表所有字段，可以自定义字段名)
select id,name,age from t_students;
#精确查询
select name,age from t_studests where name = '张三' and age>=18;

#给查询的结果列 取别名
select id,warehouse,customer_name,delivery_person as name from  t_outbound_detail limit 2;
#将查询的结果delivery_person列以name这个别名显示
#（as关键字可以不写）

1.1.1 将查询结果进行运算

#查询语句中可以用数学表达式进行数学运算。如：
select name,age*2 from t_students;
#得到的结果是age结果被乘2

1.1.2 对查询结果进行过滤

#可以在查询的结果中再通过where关键字进行条件过滤
select 字段1,字段2,字段3  from  表名  where  条件;
#where条件中可以用大于(>)、小于(<)、等于(=)、不等于(!=或<>)、大于等于(>=)、小于等于(<=) 、在什么之间(between...and)、判断是否NULL(is null)空不能用等号衡量

#多条件查询
where xx = xx and xx = xx; #和。两者都满足
where xx = xx or xx =xx;   #或。任意一个满足
#优先级and高于or。若and和or都有，可以按需求加括号

#查询是否包含在指定集合中（in）（相当于or的用法）
select * from t_students where name in ('张三','李四');
# 和in相反相反的写法是not in

2 合并查询（union）

# 查询AA和BB的部门，方法一:
select * from tt where bumen='AA' and bumen='BB';
# 方法二：（速度更快）但是合并结果集的时候，需要查询字段对应个数相同。
select * from tt where bumen='AA'
union
select * from tt where bumen='BB';

3 模糊查询（like）

# 百分号(%)：任意多个字符# 下划线(_)：任意一个字符select * from t_students where name like '%婷%'; #找名字里包含婷的select * from t_students where name like '_婷%'; # 找名字里第二个字是婷的​#如果要查询字符串_或%需转义select *from t_students where name like '\_婷%';

4 排序

对查询的结果进行排序展示

4.1 单字段排序

# order by xxx asc(升序)(asc关键字可以不写，默认升序)
select name,age from t_students order by age asc;

#order by xxx desc (降序)
select name,age from t_students order by age desc;

4.2 多字段排序

如果多行有一个字段一样，则以另一个字段比较

# 查询学生的年龄升序排序，如果年龄一样，则按姓名降序排序
select id,name,age from t_students order by age asc,name desc;

# 除了指定字段的排序外，还可以通过查询结果的字段的第几列进行排序（不建议这么写）
select id,name,age from t_students order by 3 asc,2 desc;

5 综合练习

练习：找出员工名字3个字，且中间一个是婷的学生，按年龄升序排序

(select id,name,age from t_students where name like '_婷_') order by name asc;

二、分组查询

2.1 数据处理函数

2.1.1 单行处理函数

  • 特点：一个输入对应一个输出。数据是一行行处理的。
    常见的单行处理函数：
#转换小写：lower
#语法：lower(字段名)
select lower(name),age from t_stu;
#转换大写：upper
#语法：upper(字段名)
select upper(name),age from t_stu;
#取下标：substr
#语法：substr(字段名,起始下表,结束下标)（注：开始下标从1开始）
select substr(name,1,2),age from t_stu;
#得到的结果是：周杰、林俊、谢霆
select name from t_stu where substr(name,1,1)='王';
#得到的结果是所有第一个字符是‘王’的名字
#字符串拼接：concat
#语法：concat(字段或字符串,字段或字符串)
select concat(name,age) from t_stu limit 1;
# 结果： 张三14
#取长度：length
#语法：length(字段名)
select length(name) from t_stu;
# 结果是表示长度的数字
#去除空格：trim
#语法：trim(name)
select name from t_stu where name=trim('  张三  ');
# 得到的结果是前后没有空白的'张三'
#四舍五入：round
#语法：round(字段名,保留几位小数)
select round(weight,1) from t_stu;
#结果是12.3如果不写，默认保留0位小数，如果写成-1，则表示四舍五入到十位
#生成1以内的随机数：rand
#语法：rand()
select round(rand()*10) from t_stu;
#空处理函数：ifnull
#在数据库处理中 ，如果参与运算中有一个值是NULL，最终的结果就是NULL
#语法ifnull(数据,被当做哪个值)
select name,ifnull(age,'未输入') from t_stu;
#判断当什么时候做什么：case...when..then..else..end
#比如：当学生年龄等于10岁的时候，当年龄等于15岁的时候，...
select name,age,(case age when '10' then concat('1',name) when '20' then concat('2'),name) else name) as n from t_stu;

2.1.2 多行处理函数

特点：多个输入对应一个输出。比如，将结果进行数学运算等。
在使用时必须先进行分组才能使用。如果没有对数据进行分组，整张表默认为一组。
以下5个分组函数在处理数据时都会自动忽略null，不需要提前对null进行处理

#计数：count
select count(name) from t_stu;
#得到的结果是name字段下所有不为NULL的有多少行
select count(*) from t_stu;
#得到的结果是表的总行数

#求和：sum
select sum(age) from t_stu;
#得到的结果是所有age字段下的值相加的结果

#求平均值：avg
select avg(age) from t_stu;
#得到的结果是所有age字段的平均值

#最大值：max
select max(age) from t_stu;
#得到的结果是最大的age数

#最小值：min
select min(age) from t_stu;
#得到的结果是最小的age值

#把所有分组函数组合使用
select min(age),max(age),count(age),avg(age),sum(age) from t_stu;

2.2 分组查询

分组查询的语法：

select 字段 from 表名 where 条件 group by 分组字段;

分组查询的执行先后顺序：

from –> where –> group by –> select –> order by

举例：
有表数据如下：

# 通过工作岗位进行分组，对工资求和。
# 查看上面写的执行顺序可知，实际是对分组后得到的数据再进行求和(即先group by 再select)。
select job,sum(sal) from emp group by job;

# 以下语句是错的，因为name字段条目有8条，而分组后只有4组，条目对不上，会报错
select name,job,sum(sal) from emp group by job;

# 找出每个部门不同工作岗位的最高工资(按照多个字段进行分组)
# group by后面可以跟多个字段。联合分组查询有执行顺序。对第一个字段分组后再对第二个进行分组
select max(gongzi) from emp group by bumen,gangwei;

# 求每个岗位工资的最高工资，并只显示大于1500的数据
# having可以对order by的结果进行再过滤。
select job,max(sal) from emp order by job having max(sal)>1500;
#另一种写法效率更高：
select job,max(sal) from emp where sal > 1500 order by job;

# 综合案例
#找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除企划部之外，要求按平均薪资降序排序
(select job,avg(sal) from emp where job != '企划' group by job having avg(sal)>1500) order by avg(sal) desc;

结论：
在一条select语句中，如果有group by语句，select后面只能跟参与分组的字段以及分组函数。
having的执行效率比where低，优先使用where。（where是先筛选再分组，having是先分组再筛选）

2.2.1 查询结果去重：distinct

注意：distinct只能出现在所有要查询的字段的最前方。如果两个字段，则表示两个字段联合去重。（两个字段都相同的行才会去除）

# 查询job和name重复的数据
select distinct job,name from emp;
# 查询工种的数量（去重工种）
select count(distinct job) from t_stu;

三、连接查询

3.1 什么是连接查询

从一张表里取数据叫单表查询。多张表联合查询叫连接查询
表连接的方式分类：
- 内连接：
- 等值连接
- 非等值连接
- 自连接
- 外连接：
- 左连接
- 右连接

3.2 内连接

3.2.1 内连接之等值连接

等值，即等于某个值。即查询两张表的字段值相等
案例：查询每个员工所在部门名称，显示员工名和部门名

当两张表进行连接查询的时候，如果没有任何条件限制，最终的查询结果条数是两张表条数的乘积。这种现象叫笛卡尔积现象。怎么避免呢？连接时加条件，把满足条件的记录筛选出来

有以下两张表dept和emp
dept里记录了部门编号和部门名称
emp里记录了员工姓名和其他信息
现在要将两张表联合起来查询，得到姓名、部门编号、部门名称

select ename,job,dname from dept,emp where emp.deptno = dept.deptno;
# 查到的结果就是两张表里联合的名字、岗位、部门名称
# 上面这条一般通过其别名的方式进行查询，可以提升效率
select a.ename,b.job,dname from dept a,emp b where a.deptno = b.deptno;
↑↑↑↑↑↑上面是SQL92的语法，缺点是结构不清晰，后续再用where筛选不方便

SQL99的语法这么表示：优点是如果后续想再进行筛选，可以用where
select ename,dname from emp a join dept b on  a.deptno = b.deptno;

3.2.2 内连接之非等值连接

非等值，即不等于某个值。即查询两张表的字段值不相等
案例：找出每个员工的薪资等级，要求显示员工名字、薪资、薪资等级

select a.ename,a.sal,b.grade from emp a join salgrade b on a.sal between b.losal and b.hisal;

结果：

3.2.3 内连接之非自连接

自连接顾名思义就是自己连接自己。也就是一张表看成两张表来查询
案例：找出员工的上级领导，显示领导名字和编号

select a.empno as '员工编号',a.ename as '员工姓名',a.mgr as '领导编号',b.ename as '领导姓名' from emp a join emp b on a.mgr = b.empno;

结果：

不会查出KING这个员工。因为他的领导是NULL，所以不会查出来

3.3 外连接

内连接是把两张表匹配的才显示出来
外连接是把两张表匹配到的显示出来之外，还显示出没有匹配到的数据

左外连接：left,表示将join关键字左边的这张表看做是主表，主要是为了将这张表的数据全查出来，捎带关联查询右边的表。
右外连接：right。和上面对应。
外连接中，有主次关系，内连接是没有主次关系的。
比如上面的案例中，员工KING是不显示的，因为没有匹配到（领导是NULL）
如果使用外连接：

select a.empno as '员工编号',a.ename as '员工姓名',a.mgr as '领导编号',b.ename as '领导姓名' from emp a left  join emp b on a.mgr = b.empno;

四、子查询

子查询就是嵌套的select语句

4.1 在where中使用子查询

select name from t_stu where id in (select id from t_stu where id >=10)

4.2 在from中使用子查询

select name from (select * from t_stu where id >=10) where name like '%张%';

4.3 过滤结果条数 limit

语法：select * from t_stu limit 开始索引,条数

# 显示十条数据。开始索引省略，则表示从0索引开始
select * from t_stu limit 10;
# 显示页码，每页10条数据，显示第一页
select * from t_stu limit 0,10;
# 显示页码，每页10条数据，显示第n页
select * from t_stu limit 页码-1,条数;
声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。