MySql的初学习 -- 第四章 -- 使用DQL运行简单的SQL

博客说明

文章内容输出来源：拉勾教育Java就业急训营

准备表

/*
表名：staff
表中字段：
id:编号,int
name:姓名,varchar
sex:性别,char
salary:工资,double
hire_date：入职时间,date
dept_name:部门名称,varchar
*/
CREATE TABLE staff(
id INT,
name VARCHAR(20),
sex CHAR(1),
salary DOUBLE,
hire_date DATE,
dept_name VARCHAR(20));

-- 准备数据
INSERT INTO staff VALUES(1,'张三','男',7200,'2020-02-04','教学部');
INSERT INTO staff  VALUES(2,'李四','男',3600,'2020-12-02','教学部');
INSERT INTO staff VALUES(3,'王精五','男',9000,'2020-08-08','教学部');
INSERT INTO staff VALUES(4,'赵兔六','女',5000,'2020-10-07','市场部');
INSERT INTO staff VALUES(5,'李精七','女',5000,'2020-03-14','市场部');
INSERT INTO staff VALUES(6,'王兔八','女',200,'2020-03-14','市场部');
INSERT INTO staff VALUES(7,'孙九','女',10000,'2020-10-07','财务部');
INSERT INTO staff VALUES(8,'赵十','女',3500,'2020-09-14','财务部');
INSERT INTO staff VALUES(9,'吴十一','男',20000,'2020-03-14',NULL);
INSERT INTO staff VALUES(10,'孙十二','男', 10,'2020-03-14',财务部);
INSERT INTO staff VALUES(11,'钟十三','女', 300,'2010-03-14',财务部);

使用SQL对表中的数据进行查询

注意事项：查询操作不会对数据库中的数据进行修改，仅用于查看

简单查询

-- 格式：SELECT 列名1,列名2... FROM 表名;
-- *表示所有的列
-- 查询staff表中的所有数据
SELECT * FROM staff; 

-- 查询所有数据，但只显示 id 和 name
SELECT id,name FROM staff;

别名查询

/*
格式：
SELECT 
列名1 AS '别名1' ,
列名2 AS '别名2' ...
FROM 表名
*/
-- 查询所有数据，然后给列名改为中文
SELECT 
id AS '编号',
name AS '姓名',
sex AS '性别',
salary AS '工资',
hire_date AS '入职时间',
dept_name  '部门名称'-- AS可以省略不写
FROM staff;

去重查询

-- 格式：SELECT DISTINCT 列名 FROM 表名 
-- 查询一共有几个部门
SELECT DISTINCT dept_name '部门名称' FROM staff;

算术查询

-- 格式：SELECT (列名 + 数值) FROM 表
-- 格式：SELECT (列名 - 数值) FROM 表
-- 格式：SELECT (列名 * 数值) FROM 表
-- 格式：SELECT (列名 / 数值) FROM 表
-- 将员工工资+10，-10，*10，/10，进行操作
select salary + 10 AS '工资' from staff;
select salary - 10 AS '工资' from staff;
select salary * 10 '工资' from staff;
select salary / 10 '工资' from staff;

条件表达式

比较运算符	说明
>、<、<=、>=、=、<>、!=	大于、小于、大于(小于)等于、不等于
BETWEEN …AND…	显示在某一区间的值，例如: 2000-10000之间： Between 2000 and 10000
IN(集合)	集合表示多个值,使用逗号分隔,例如: name in (张三，李四)，in中的每个数据都会作为一次条件,只要满足条件就会显示
LIKE ‘%张%’	模糊查询
IS NULL	查询某一列为NULL的值, 注: 不能写 = NULL

逻辑运算符	说明
And 、&&	多个条件同时成立
Or、丨丨	多个条件任一成立
Not	不成立，取反。

条件查询

-- 格式：SELECT 列名 FROM 表名 WHERE 条件表达式
SELECT dept_name FROM staff WHERE sex = '男'
SELECT * FROM staff WHERE salary = 9000;

模糊查询

模糊查询通配符	说明
%	表示匹配任意多个字符串
_	表示匹配 一个字符

-- 模糊查询
-- %通配符：匹配一个或者多个字符串
-- 格式1：张%   -----查询开头为 张
SELECT * FROM staff WHERE name = '张%';
-- 格式2：%张   -----查询结尾为 张
SELECT * FROM staff WHERE name = '%张';
-- 格式3：%张%  -----查询包含 张
SELECT * FROM staff WHERE name = '%张%';

-- _通配符：至多匹配一个字符串
-- 格式1：张_   -----查询第一个字为 张
SELECT * FROM staff WHERE name = '张_';
-- 格式2：_张   -----查询第二个字为 张
SELECT * FROM staff WHERE name = '_张';

练习

-- SQL形成三部曲：查哪张表、查哪些字段、查询条件

-- 查询员工姓名为吴十一的员工信息
-- 查询薪水价格为5000的员工信息
-- 查询薪水价格不是5000的所有员工信息
-- 查询薪水价格大于6000元的所有员工信息
-- 查询薪水价格在5000到10000之间所有员工信息
-- 查询薪水价格是3600或7200或者20000的所有员工信息
-- 查询含有'精'字的所有员工信息 
-- 查询以'孙'开头的所有员工信息 
-- 查询第二个字为'兔'的所有员工信息 
-- 查询没有部门的员工信息
-- 查询有部门的员工信息

-- 查询员工姓名为吴十一的员工信息
SELECT * FROM staff WHERE name = '吴十一';

-- 查询薪水价格为5000的员工信息
SELECT* FROM staff WHERE salary = 5000;

-- 查询薪水价格不是5000的所有员工信息
方法一：SELECT* FROM staff WHERE salary != 5000;
方法二：SELECT* FROM staff WHERE salary <> 5000;

-- 查询薪水价格大于6000元的所有员工信息
SELECT* FROM staff WHERE salary > 6000;

-- 查询薪水价格在5000到10000之间所有员工信息
-- && 和 AND 为同一个运算符，无差别，用哪个都可以
方法一：SELECT* FROM staff WHERE salary >= 5000 && salary <= 10000;
方法二：SELECT* FROM staff WHERE salary >= 5000 AND salary <= 10000;
方法三：SELECT* FROM staff WHERE salary BETWEEN 5000 AND 10000;

-- 查询薪水价格是3600或7200或者20000的所有员工信息
-- || 和 OR 为同一个运算符，无差别，用哪个都可以
方法一：SELECT* FROM staff WHERE salary = 3600||salary = 7200||salary=20000
方法二：SELECT* FROM staff WHERE salary = 3600 OR salary = 7200 ORr salary=20000
方法三：SELECT* FROM staff WHERE salary IN(3600,7200,20000)

-- 查询含有'精'字的所有员工信息 
SELECT * FROM staff  WHERE name = '%精%';

-- 查询以'孙'开头的所有员工信息 
SELECT * FROM staff WHERE NAME = '孙%';

-- 查询第二个字为'兔'的所有员工信息 
SELECT * FROM staff WHERE NAME = '_兔%';

-- 查询没有部门的员工信息
SELECT * FROM staff WHERE dept_name IS null;

-- 查询有部门的员工信息
SELECT * FROM staff WHERE dept_name IS NOT NULL;

排序查询

-- 格式：SELECT 字段名 FROM 表名 WHERE 字段 = 值 ORDER BY 字段名 ASC/DESC
-- ASC：升序
-- DESC：降序
SELECT * FROM staff WHERE salary > 6000 ORDER BY salary DESC; 

单列排序查询

-- 格式：SELECT * FROM 表名 ORDER BY 字段
-- 默认升序(ASC)排序
SELECT * FROM staff ORDER BY salary; 
-- 降序
SELECT * FROM staff ORDER BY salary DESC; 

多列排序查询

同时对多个字段进行排序, 如果第一个字段相同 就按照第二个字段进行排序,以此类推

-- 格式：SELECT * FROM 表名 ORDER BY 字段1,ORDER BY 字段2...
SELECT * FROM staff ORDER BY salary DESC,id DESC;

聚合函数

将一列数据作为一个整体，进行 纵向 计算

聚合函数	作用
count(字段)	统计指定列不为NULL的记录行数
sum(字段)	计算指定列的数值和
max(字段)	计算指定列的最大值
min(字段)	计算指定列的最小值
avg(字段)	计算指定列的平均值

-- 格式1：SELECT 聚合函数(字段名) FROM 表名
-- 格式2：SELECT 聚合函数(字段名) FROM 表名 WHERE 条件表达式
SELECT MAX(salary) FROM staff;
SELECT SUM(salary) FROM staff WHERE id > 2;

聚合函数练习

-- 1 查询员工的总数
-- 2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值 
-- 3 查询薪水大于4000员工的个数
-- 4 查询部门为'教学部'的所有员工的个数 
-- 5 查询部门为'市场部'所有员工的平均薪水

-- 1 查询员工的总数
SELECT count(*) FROM staff;

-- 2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值 
SELECT 
SUM(salary) '总薪水',
Max(salary) '最高薪水',
min(salary) '最小薪水',
avg(salary) '平均薪水' 
from staff;

-- 3 查询薪水大于4000员工的个数
SELECT count(*) FROM staff where salary > 4000;

-- 4 查询部门为'教学部'的所有员工的个数 
select COUNT(*) from staff where dept_name ='教学部';

-- 5 查询部门为'市场部'所有员工的平均薪水
SELECT avg(salary) FROM staff WHERE dept_name ='市场部';

分组查询

分组的目的就是为了方便统计，所有一般分组会与聚合函数一起使用，单独进行分组是没有意义的

-- 格式：SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段
SELECT sex,AVG(salary) FROM staff GROUP BY sex;

分组查询练习

-- 1.查询有几个部门
-- 2.查询每个部门的平均薪资
-- 3.查询每个部门的平均薪资, 部门名称不能为null

-- 1.查询有几个部门
SELECT 
dept_name '部门名称'
FROM staff GROUP BY dept_name;

-- 2.查询每个部门的平均薪资
SELECT 
dept_name '部门名称',
AVG(salary) '平均薪资' 
FROM staff GROUP BY dept_name;

-- 3.查询每个部门的平均薪资, 部门名称不能为null
SELECT 
dept_name '部门名称',
AVG(salary) '平均薪资'
FROM staff WHERE dept_name IS NOT NULL 
GROUP BY dept_name;

having关键字与where关键字的区别

-- having：在分组之后，再进行条件过滤使用
-- 格式：在后面接上即可

-- 查询平均薪资大于6000的部门
SELECT 
dept_name '部门名称',
AVG(salary) '平均薪资'
FROM staff WHERE dept_name IS NOT NULL 
GROUP BY dept_name 
having AVG(salary) > 6000;

limit关键字

作用：使用limit去指定要查询数据的条数、行数

-- 格式：SELECT 字段 FROM 表名 LIMIT OFFSET, LENGTH;
-- OFFSET:起始行数，默认从0开始
-- LENGTH：返回的行数(要查询几条数据)
-- LIMIT OFFECT, LENGTH; 关键字可以接受一个 或者两个 为0 或者正整数的参数
-- LIMIT关键字可以进行分页操作
人话：SELECT * FROM staff LIMIT 从第几行开始显示 , 显示几条数据;
  详细用法参考下面limit关键字练习

limit关键字练习,熟悉使用limit进行分页操作

-- 查询staff表中的前5条数据
方法一：SELECT * FROM staff LIMIT 0,5;
方法二：SELECT * FROM staff LIMIT 5;--因为默认从0开始，所以可以考虑不写
-- 查询staff表中从第4条开始,查询6条
SELECT * FROM staff LIMIT 3,6;

-- 使用分页操作对staff中的数据进行分页，每页显示三行
SELECT * FROM staff LIMIT 0,3;
SELECT * FROM staff LIMIT 3,3;
SELECT * FROM staff LIMIt 6,3;

--分页公式： 起始行数 = (当前页面 - 1) * 每页显示行数
#第一页
SELECT * FROM staff LIMIT 0,3;--(1 - 1) * 3 = 0;
#第二页
SELECT * FROM staff LIMIT 3,3;--(2 - 1) * 3 = 3;
#第三页
SELECT * FROM staff LIMIt 6,3;--(3 - 1) * 3 = 6;