Mysql——数据库/命令/查询/分组函数

1237-曾同学

发表文章数:45

热门标签

首页 » MySQL » 正文

数据库相关概念

1.SQL、DB、DBMS

DB:Database 数据库,通常是一个或一组文件,保存了一些符合特定规格的数据,数据库对应的英语单词(在硬盘上以文件的形式存在)
DBMS:DataBase Management System数据库管理系统,如MySQL、Oracle。
SQL:结构化查询语言,是一门标准通用的语言。标准的sql适合于所有的数据库产品,SQL属于高级语言。
—>DBMS负责执行sql语句,通过执行sql语句来操作DB当中的数据。

2.表
表:table是数据库的基本组成单元,所有的数据都以表格的形式组织,目的是可读性强。

一个表包括行和列:
  行:被称为数据/记录(data)
  列:被称为字段(column)

每一个字段应该包括字段名、数据类型(int,varchar)、相关的约束。

3.SQL的分类

DQL数据查询语言(Data Query Language) :查询语句 select 。
DML数据操纵语言(Data Manipulation Language):insert delete update,对表当中的数据进行增删改。
DDL数据定义语言(Data Definition Language):create drop alter,对表结构的增删改。
TCL事务控制语言(Transactional Control Language):commit提交事务,rollback回滚事务。
DCL数据控制语言(Data Control Language):grant授权、revoke撤销权限等。

4.导入数据
第一步:登录mysql数据库管理系统
第二步:查看有哪些数据库 show databases; (这个不是SQL语句,属于MySQL的命令。)
第三步:创建属于我们自己的数据库 create database bjpowernode; (这个不是SQL语句,属于MySQL的命令。)
第四步:使用bjpowernode数据 use bjpowernode; (这个不是SQL语句,属于MySQL的命令。)
第五步:查看当前使用的数据库中有哪些表? show tables; (这个不是SQL语句,属于MySQL的命令。)
第六步:初始化数据 mysql> source D:/course/05-MySQL/resources/bjpowernode.sql
5.sql脚本
bjpowernode.sql,这个文件以sql结尾,这样的文件被称为“sql脚本”。

当一个文件的扩展名是.sql,并且该文件中编写了大量的sql语句,我们称这样的文件为sql脚本。

PS:直接使用source命令可以执行sql脚本。(sql脚本中的数据量太大的时候,无法打开,使用source命令完成初始化。)

初始化完成后有三张表:
Mysql——数据库/命令/查询/分组函数

6.删除数据库
drop database bjpowernode;

7.查看表结构

desc dept;
desc emp;
desc salgrade;
Mysql——数据库/命令/查询/分组函数

8.查看表中数据

select * from emp;
select * from dept;
select * from salgrade;

Mysql——数据库/命令/查询/分组函数

常用命令

1.常用命令

#查看当前使用的是哪个数据库
mysql> select database(); 

#查看mysql的版本号
mysql> select version(); 

exit #命令,退出mysql。

#创建数据库
mysql> create database 数据库名称

#use 数据库名称
mysql> use bjpowernode;

2.查看表结构

#查看现有数据库
mysql> show databases;

#指定使用数据库
mysql> use bjpowernode;

#查看当前使用的数据库
mysql> select database();

#查看当前库中的表
mysql> show tables;

#查看其他库中的表
mysql> show tables from <database name>;

#查看表的结构
mysql> desc <table name>;

#查看表的创建语句
mysql> show create table <table name>;

简单查询

语法格式:

select 字段名1,字段名2,字段名3,… from 表名;
提示:
1、任何一条sql语句以“;”结尾。
2、sql语句不区分大小写。

mysql> select ename,empno from emp;

1.查询员工的年薪?(字段可以参与数学运算。)

mysql> select ename,sal * 12 from emp;

2.给查询结果的列重命名(使用as关键字)

mysql> select ename,sal * 12 as yearsal from emp;

3.使用中文重命名列

mysql> select ename,sal * 12 as '年薪' from emp;

PS:注意:标准sql语句中要求字符串使用单引号括起来。虽然mysql支持双引号,尽量别用。
4.省略as关键字

mysql> select empno,ename,sal * 12 yearsal from emp;

5.查询所有字段

mysql> select * from emp; #实际开发中不建议使用*,效率较低。

条件查询

语法格式

语法格式:
select
  字段,字段…
from
  表名
where
  条件;
–>执行顺序:先from,然后where,最后select

条件查询需要用到where语句,where必须放到from语句表的后面。
Mysql——数据库/命令/查询/分组函数

1.查询工资等于5000的员工姓名

mysql> select ename from emp where sal = 5000;

2.查询SMITH的工资

mysql> select sal from emp where ename = 'SMITH'; 
# 字符串使用单引号括起来。

3.找出工资高于3000的员工

mqsql> select ename,sal from emp where sal > 3000;

#工资不等于3000的员工
mysql> select ename,sal from emp where sal <> 3000;
mysql> select ename,sal from emp where sal != 3000;

4.找出工资在1100和3000之间的员工,包括1100和3000

mysql> select ename,sal from emp where sal >= 1100 and sal <= 3000;
mysql> select ename,sal from emp where sal between 1100 and 3000; 
#between...and...是闭区间 [1100 ~ 3000]

mysql> select ename,sal from emp where sal between 3000 and 1100; 
#查询不到任何数据,between and在使用的时候必须左小右大。

#between and除了可以使用在数字方面之外,还可以使用在字符串方面。
mysql> select ename from emp where ename between 'A' and 'C';
#字符串是左闭右开的

5.找出那些人津贴为NULL/没有津贴
在数据库当中NULL不是一个值,代表什么也没有,为空。
空不是一个值,不能用等号衡量,必须使用 is null或者is not null。

#找出哪些人津贴为NULL(不含0.00)
mysql> select ename,sal,comm from emp where comm is null;

#找出哪些人津贴不为NULL
mysql> select ename,sal,comm from emp where comm is not  null;

#找出哪些人没有津贴(or)
mysql> select ename,sal,comm from emp where comm is null or comm = 0;

6.and和or联合使用问题

#找出工作岗位是MANAGER和SALESMAN的员工
mysql> select ename,job from emp where job = 'manager' or job = 'salesman';
#and的并且,or是或

#找出薪资大于1000的并且部门编号是20或30部门的员工
mysql> select ename,sal,deptno from emp where sal >1000 and (deptno =20 or deptno = 30);

PS:注意当运算符的优先级不确定的时候加小括号。(不然上面优先执行了and,筛选错误)
7.in 和 not in 的使用
in 等同于or
not in 不在这几个值中

#找出工作岗位是MANAGER和SALESMAN的员工
mysql> select ename,job from emp where job in ('SALESMAN','MANAGER');
mysql> select ename,job from emp where job = 'SALESMAN' or job = 'MANAGER';
#上面两种效果相同

mysql> select ename,job from emp where sal in(800, 5000); 
#in后面的值不是区间,是具体的值。

#not in: 不在这几个值当中。
mysql> select ename,job from emp where sal not in (800, 3000);

8.模糊查询 like
两个特殊符号:%代表任意多个字符,_代表任意1个字符。

#找出名字当中含有O的
mysql> select ename from emp where ename like '%O%';

#找出名字中第二个字母是A的
mysql> select ename from emp where ename like '_A%';

#找出名字中有下划线的
mysql> select ename from emp where ename like '%_%';
#这个可以找出所有的名字
mysql> select ename from emp where ename like '%/_%';
#加转义字符,这样能够把下划线变成真的下划线

#找出名字中最后一个字母是T的
mysql> select ename from emp where ename like '%T';

排序数据

PS:order by 默认排序是升序,可以用 asc 升序,desc 降序来指定排序顺序。

#按照工资的降序排列,当工资相同的时候再按照名字的升序排列。
mysql> select ename,sal from emp order by sal;
#指定升序
mysql> select ename,sal from emp order by sal asc;
#指定降序
mysql> select ename,sal from emp order by sal desc;
#按照字段顺序排列(这种排序方式不稳健)
mysql> select * from emp order by 6

#将薪水按降序排列
mysql> select * from emp order by sal desc;

#找出工作岗位是SALESMAN的员工,并且要求按照薪资的降序排列。
mysql> select ename,job,sal from emp where job = 'SALESMAN' order by sal desc;

越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候,才会启用后面的字段。

执行顺序
select
  字段  3
from
  表名  1
where
  条件  2
order by
  …   4
order by是最后执行的。

分组函数/聚合函数/单行处理函数

1.分组函数(多行处理函数)
(1)基本使用
所有的分组函数都是对“某一组”数据进行操作的。
一共五个分组函数,分组函数也叫做多行处理函数(输入多行,输出一行结果)。

count 计数
sum 求和
avg 平均值
max 最大值
min 最小值

#找出工资总和?
mysql> select sum(sal) from emp;
#找出最高工资?
mysql> select max(sal) from emp;
#找出最低工资?
mysql> select min(sal) from emp;
#找出平均工资?
mysql> select avg(sal) from emp;
#找出总人数?
mysql> select count(*) from emp;
mysql> select count(ename) from emp;

(2)分组函数自动忽略NULL。(NULL不参与运算)

mysql> select count(comm) from emp;
#结果不是NULL,自动忽略了空,计算了其他的。

mysql> select sum(comm) from emp where comm is not null; 
#不需要额外添加这个过滤条件。sum函数自动忽略NULL。

(3)count(*)和count(具体的某个字段)的区别

count(*):不是统计某个字段中数据的个数,而是统计总记录条数。(和某个字段无关)
count(comm): 表示统计comm字段中不为NULL的数据总数量。

(4)分组函数不可使用在where子句当中
where后面绝对不能跟分组函数(五个)!!

#找出工资高于平均工资的员工
mysql> select ename,sal from emp where sal > avg(sal);
#这样写会报错

原因:因为goupby是在where执行之后才会执行的。

执行顺序:
select   5

from   1

where   2

group by  3

having   4

order by  6

解决办法:

#找出工资高于平均工资的员工
#第一步 找出平均薪资
mysql> select avg(sal) from emp;
#第二步 找出高于平均工资的员工
mysql> select ename,sal from emp where sal > 2073.214286;

#或者:嵌套子查询也可实现
mysql> select ename,sal from emp where sal > (select avg(sal) from emp);

(5)分组函数组合使用

mysql> select count(*),sum(sal),avg(sal),min(sal),max(sal) from emp;

2.单行处理函数
输入一行,输出一行。

#计算每个员工的年薪(下面的处理方式会出现空值)
mysql> select ename,(sal+comm)*12 as yearsal from emp;
#重点:所有数据库都是这样规定的,只要有NULL参与的运算结果一定是NULL。

ifnull() 空处理函数
ifnull(可能为NULL的数据,被当做什么处理) : 属于单行处理函数。

mysql> select ename,ifnull(comm,0) as comm from emp;

#计算每个员工的年薪
mysql> select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;

分组查询

group by : 按照某个字段或者某些字段进行分组。
having : having是对分组之后的数据进行再次过滤。

1.group by
注意:分组函数一般都会和group by联合使用,这也是为什么它被称为分组函数的原因。

并且任何一个分组函数(count sum avg max min)都是在group by语句执行结束之后才会执行的。

当一条sql语句没有group by的话,整张表的数据会自成一组。(分组函数默认有一个自成一组的groupby函数)

案例:找出每个工作岗位的最高薪资。

mysql> select max(sal),job from emp group by job;

select ename,max(sal),job from emp group by job;

以上在mysql当中,查询结果是有的,但是结果没有意义,在Oracle数据库当中会报错,语法错误。Oracle的语法规则比MySQL语法规则严谨。

记住一个规则:当一条语句中有group by的话,
select后面只能跟分组函数和参与分组的字段。

#每个工作岗位的平均薪资
mysql> select job,avg(sal) from emp group by job;

多个字段联合起来一块分组

select
  deptno,job,max(sal)
from
  emp
group by
  deptno,job;

2.having
如果想对分组函数再进行过滤使用having子句。(它是group by 的搭档,不出现group by不使用having)

例:找出每个部门的最高薪资,要求显示薪资大于2900的数据。

#第一步:找出每个部门的最高薪资
mysql> select max(sal),deptno from emp group by deptno;

#第二步:找出薪资大于2900
mysql> select max(sal),deptno from emp group by deptno having max(sal) > 2900; 
#使用having这种方式效率低。
#或
mysql> select max(sal),deptno from emp where sal > 2900 group by deptno;  
#效率较高,建议能够使用where过滤的尽量使用where。

例:找出每个部门的平均薪资,要求显示薪资大于2000的数据。

#第一步:找出每个部门的平均薪资
mysql> select deptno,avg(sal) from emp group by depno;
	
#要求显示薪资大于2000的数据
mysql> select deptno,avg(sal) from emp where avg(sal)>2000 group by deptno;
#上面这种表达是错误的,where后面不能使用分组函数,这个只能用having过滤
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

【总结一个完整的sql语句】

select    5

from     1

where     2

group by   3

having(不能单一出现) 4

order by   6

1.首先执行where语句过滤原始数据
2.执行group by进行分组
3.执行having对分组数据进行操作
4.执行select选出数据
5.执行order by排序

原则:能在where中过滤的数据,尽量在where中过滤,效率较高。having的过滤是专门对分组之后的数据进行过滤的。

去除重复

关于查询结果集的去重,使用 distinct 关键字。
注意:distinct只能出现在所有字段的最前面。

#查询所有岗位,不重复
mysql> select distinct job from emp;

#出现在dictinct后的字段联合查询
mqsql> select distinct deptno,job from emp;

#查询工作岗位的数量
mysql> select count(distinct job) from emp;
标签:

未经允许不得转载:作者:1237-曾同学, 转载或复制请以 超链接形式 并注明出处 拜师资源博客
原文地址:《Mysql——数据库/命令/查询/分组函数》 发布于2020-12-01

分享到:
赞(0) 打赏

评论 抢沙发

评论前必须登录!

  注册



长按图片转发给朋友

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

Vieu3.3主题
专业打造轻量级个人企业风格博客主题!专注于前端开发,全站响应式布局自适应模板。

登录

忘记密码 ?

您也可以使用第三方帐号快捷登录

Q Q 登 录
微 博 登 录