一、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 内连接之等值连接

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

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

有以下两张表deptemp
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,条数;
声明:本站所有文章,如无特殊说明或标注,均为本站原创发布。任何个人或组织,在未征得本站同意时,禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。