七.基本查询

1.基本查询

(1).概念

[1].数据库管理系统一个重要功能就是数据查询，数据查询不应只是简单返回数据库中存储的数据，还应该根据需要对数据进行筛选以及确定数据以什么样的格式显示。

[2].MySQL提供了功能强大，灵活的语句来实现这些操作

[3].MySQL数据库使用select语句来查询数据。

(2).语法格式

select
  [all|distinct]
  <目标列的表达式1> [别名],
  <目标列的表达式1> [别名] ...
from <表名或视图名> [别名],  <表名或视图名> [别名] ...
[where<条件表达式>]
[group by <列名>
[having <条件表达式>]]
[order by <列名> [asc|desc]]
[limit <数字或者列表>]

简化版语法:

select *| 列名 from 表 where 条件

(3).示例

[1].数据准备
[a].创建数据库和表

-- 创建数据库和表
create database if not exist mydb2;
use mydb2;
-- 创建商品表:
create table product(
  pid int primary key auto_increment, -- 商品编号
  pname varchar(20) not null, -- 商品名字
  price double, -- 商品价格
  category_id varchar(20) -- 商品所属分类  
);

[b].添加数据

insert into product values(null, '海尔洗衣机', 5000, 'c001');
insert into product values(null, '美的冰箱', 3000, 'c001');
insert into product values(null, '格力空调', 5000, 'c001');
insert into product values(null, '九阳电饭煲', 5000, 'c001');

insert into product values(null, '啄木鸟衬衣', 300, 'c002');
insert into product values(null, '恒源祥西裤', 800, 'c002');
insert into product values(null, '花花公子夹克', 440, 'c002');
insert into product values(null, '劲霸休闲裤', 266, 'c002');
insert into product values(null, '海澜之家卫衣', 180, 'c002');
insert into product values(null, '杰克琼斯运动裤', 430, 'c002');

insert into product values(null, '兰蔻面霜', 300, 'c003');
insert into product values(null, '雅诗兰黛精华水', 200, 'c003');
insert into product values(null, '香奈儿香水', 350, 'c003');
insert into product values(null, 'SK-II神仙水', 350, 'c003');
insert into product values(null, '资生堂粉底液', 180, 'c003');

insert into product values(null, '老北京方便面', 56, 'c004');
insert into product values(null, '良品铺子海带丝', 17, 'c004');
insert into product values(null, '三只松鼠坚果', 88, null);

[2].简单查询

-- 1.查询所有的商品
select * from product;
-- 2.查询商品名和商品价格
select pname, price from product;
-- 3.别名查询 使用的关键字是as (as可以省略的)
-- 3.1 表别名
select * from product as p;
-- 3.2 列别名
select pname as pn from product;
-- 4.去掉重复值
select distinct price from product;
-- 5.查询结果是表达式(运算查询):将所有商品的价格+10元
select pname, price+10 from product;

2.运算符

(1).简介

数据库中的表结构确立后，表中的数据代表的意义就已经确定。通过MySQL运算符进行运算，就可以获取到表结构以外另一种数据。

例如，学生表中存在一个birth字段，这个字段表示这个学生的出生年份。而运用MySQL运算符用当前年份减去学生出生的年份，那么得到的就是这个学生的实际年龄数据

(2).MySQL支持4种运算符

[1].算术运算符

[2].比较运算符

[3].逻辑运算符

[4].位运算符

(3).算术运算符

[1].分类

算术运算符	说明
+	加法运算
-	减法运算
*	乘法运算
/ 或 DIV	除法运算，返回商
% 或 MOD	求余运算，返回余数

[2].示例

select 6 + 2;
select 6 - 2;
select 6 * 2;
select 6 / 2;
select 6 % 2;

-- 将每件商品的价格加10
select name, price + 10 as new_price from product;
-- 将所有商品的价格上调10%
select pname, price * 1.1 as new_price from product;

(4).比较运算符

[1].分类

比较运算符	说明
=	等于
< 和 <=	小于和小于等于
> 和 >=	大于和大于等于
<=>	安全的等于，两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
<>或!=	不等于
IS NULL 或 ISNULL	判断一个值是否为NULL
IS NOT NULL	判断一个值是否不为NULL
LEAST	当有两个或多个参数时，返回最小值
GAEATEST	当有两个或多个参数时，返回最大值
BETWEEN AND	判断一个值是否落在两个值之间
IN	判断一个值是IN列表中的任意一个值
NOT IN	判断一个值不是IN列表中的任意一个值
LIKE	通配符匹配
REGEXP	正则表达式匹配

[2].示例

-- 查询商品名称为"海尔洗衣机"的商品所有信息
select * from product where pname = '海尔洗衣机';

-- 查询价格为800商品
select * from product where price = 800;

-- 查询价格不是800的所有商品
select * from product where price != 800;
select * from product where price <> 800;
select * from product where not(price = 800);

-- 查询商品价格大于60元的所有商品信息
select * from product where price > 60

-- 查询商品价格在200到1000之间所有商品
select * from product where price >= 200 and price <= 1000;
select * from product where price between 200 and 1000;

-- 查询商品价格是200或800的所有商品
select * from product where price = 200 or price = 800;
select * from product where price in (200, 800);

-- 查询含有'鞋'字的所有商品
select * from product where pname like '%鞋%';

-- 查询以'海'开头的所有商品
select * from product where pname like '海%';

-- 查询第二字为'蔻'的所有商品
select * from product where pname like '_蔻%';

-- 查询category_id为null的商品
select * from product where category_id is null;

-- 查询category_id不为null的商品
select * from product where category_id is not null;

-- 使用least求最小值
select least(10, 20, 30); -- 10
select least(10, null, 30); -- null

-- 使用greatest求最大值
select greatest(10, 20, 30); -- 30
select greatest(10, null, 30); -- null

(5).逻辑运算符

[1].分类

逻辑运算符	说明
NOT 或者 |	逻辑非
AND 或者 &&	逻辑与
OR 或者 ||	逻辑或
XOR	逻辑异或

(6).位运算

[1].分类

位运算符	说明
|	按位或
&	按位与
^	按位异或
<<	按位左移
>>	按位右移
~	按位取反，反转所有比特

注意:位运算符是在二进制数上进行计算的运算符。

[2].示例

select 3&5; -- 位与
select 3|5; -- 位或
select 3^5; -- 位异或
select 3>>1; -- 位左移
select 3<<1; -- 位右移
select ~3; -- 位取反

3.排序查询

(1).概念

如果我们需要对读取的数据进行排序，我们就可以使用MySQL的order by子句来设定你想那个字段那种排序方式来进行排序，再返回搜索结果。

(2).语法

select 
   字段名1, 字段名2, ...
from 表名
order by 字段名1 [asc|desc], 字段名2 [asc|desc] ... 

(3).特点

[1].asc代表升序， desc代表降序，如果不写默认升序。

[2].order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名

[3].order by子句，放在查询语句的最后面，LIMIT子句除外

(4).示例

-- 1.使用价格顺序(降序)
select * from product order by price desc;
-- 2.在价格排序(降序)的基础上，以分类排序(升序)
select * from product order by price desc, category asc;
-- 3.显示商品的价格(去重复)，并排序(降序)
 select distinct price from product order by price desc;

4.聚合查询

(1).概念

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

(2).聚合函数

聚合函数	作用
count()	统计指定列不为NULL的记录行数
sum()	计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
max()	计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
min()	计算指定列的最小值，如果指定的列是字符串类型，那么使用字符串排序运算；
avg()	计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

(3).示例

-- 1.查询商品的总条数
select count(pid) from product;
select count(*) from product;

-- 2.查询价格大于200商品的总条数
select count(pid) from product where price > 200;

-- 3.查询分类为'c001'的所有商品价格的总和
select sum(price) from product where category_id = 'c001';

-- 4.查询商品的最大价格
select max(price) from product;

-- 5.查询商品的最小价格
select min(price) from product;

-- 6.查询分类为'c002'所有商品的平均价格
select avg(price) from product where category_id = 'c002';

(4).Null值处理

[1].简介

[a]. count函数对null值的处理：

​ 如果count函数的参数为星号(*), 则统计所有记录的个数。而如果参数为某个字段，不统计含null值的记录个数。

[b].sum和avg函数对null值的处理

​ 这两个函数忽略null值的存在，就好像该条记录不存在一样。

​ [c].max和min函数对null值的处理

​ max和min两个函数同样忽略null值的存在

[2].示例

-- 创建表
create table test_null(
  c1 varchar(20),
  c2 int  
);

-- 插入数据
insert into test_null values('aaa', 3);
insert into test_null values('bbb', 3);
insert into test_null values('ccc', null);
insert into test_null values('ddd', 6);

-- 测试
select count(*), count(1), count(c2) from test_null;
select sum(c2), max(c2), min(c2), avg(c2) from test_null;

5.分组查询

(1).简介

分组查询是指使用group by子句对查询信息进行分组

(2).格式

select 字段1, 字段2 ... from 表名 group by 分组字段 having 分组条件;

(3).示例

-- 1.统计各个分类商品的个数
select category_id, count(*) from product group by category_id;

(4).分组之后的条件筛选-having

[1].分组之后对统计结果进行筛选的话必须使用having, 不能使用where

[2].where子句用来筛选FROM子句中指定的操作所产生的行

[3].group by子句用来分组WHERE子句的输出

[4].having子句用来从分组结果中筛选行。

-- 统计各个分类商品的个数，且只显示个数大于4的信息
select category_id, count(*) from product group by category_id having count(*) > 4;

6.分页查询

(1).简介

分页查询在项目中常见，由于数据量大，显示屏长有限，因此对数据采取分页显示方式。例如，数据共有30条，每页显示5条，第一页显示1_{5条，第二页显示6}10条

(2).格式

-- 方式1 - 显示前n条
select 字段1, 字段2 ... from 表名 limit n
-- 方式2 - 分页显示
select 字段1, 字段2 ... from 表名 limit m, n
参数:
  m: 整数, 表示从第几条索引开始，计算方式(当前页 - 1) * 每页显示条数
  n: 整数, 表示查询多少条数据   

(3).示例

-- 查询product表的前5条记录
select * from product limit 5

-- 从第4条开始显示，显示5条
select * from product limit 3,5

7.insert into select语句

(1).简介

将一张表的数据导入到另一张表中，可以使用insert into select语句

(2).格式

insert into table2(field1, field2, ...) select value1, value2, ... from table1;
或者
insert into table2 select * from table1;

8.举例

(1).案例1

[1].数据准备

-- 创建数据库
create database mydb2;
use mydb2;

-- 创建表
create table student(
    id int,
    name varchar(20),
    gender varchar(20),
    chinese int,
    english int,
    math int  
);

-- 插入数据
insert into student(id, name, gender, chinese, english, math) values(1, '张明', '男', 89, 78, 90); 
insert into student(id, name, gender, chinese, english, math) values(2, '李进', '男', 67, 53, 95); 
insert into student(id, name, gender, chinese, english, math) values(3, '王五', '女', 87, 78, 77); 
insert into student(id, name, gender, chinese, english, math) values(4, '李一', '女', 88, 98, 92); 
insert into student(id, name, gender, chinese, english, math) values(5, '李财', '男', 82, 84, 67); 
insert into student(id, name, gender, chinese, english, math) values(6, '张宝', '男', 55, 85, 45); 
insert into student(id, name, gender, chinese, english, math) values(7, '黄蓉', '女', 75. 65, 30); 
insert into student(id, name, gender, chinese, english, math) values(7, '黄蓉', '女', 75, 65, 30); 

[2].查询

-- 查询表中所有学生的信息
select * from student;

-- 查询表中所有学生和对应的英语成绩
select name, english from student;

-- 过滤表中重复数据
select distinct * from student;

-- 统计每个学生的总分
select name, (chinese + english + math) as total_score from student;

-- 在所有学生总分数上加10分特长分
select name, (chinese + english + math + 10) as total_score from student;

-- 使用别名表示学生分数
select name, chinese '语文成绩', english '英语成绩', math '数学成绩' from student

-- 查询英语成绩大于90分的同学
select * from student where english > 90;

-- 查询总分大于200分的所有同学
select * from student where (chinese + english + math) > 200;

-- 查询英语分数在80~90之间的同学
select * from student where english between 80 and 90;
或者
select * from student where english >=80 and english <= 90;

-- 查询英语分数不在80~90之间同学
select * from student where not (english between 80 and 90);
或者
select * from student where english not between 80 and 90;
或者
select * from student where not (english >=80 and english <= 90);
或者
select * from student where english < 80 or english >90;

-- 查询数学分数为89, 90, 91的同学
select * from student where math in (89, 90, 91);

-- 查询数学不为分数为89, 90, 91的同学
select * from student where math not in (89, 90, 91);

-- 查询所有姓李的学生英语成绩
select name, english from student where name like '李%';

-- 查询数学分为80并且语文分为80的同学
select * from student where math = 80 and chinese = 80;

-- 查询英语80或者总分200同学
select * from student where math = 80 or (chinese + math + english) = 200;

-- 对数学成绩降序排序输出
select * from student order by math desc;

-- 对总分排序后输出，然后再从高到低的顺序输出
select * from student order by (chinese + math + english) desc;

-- 对姓李的同学总分成绩排序输出
select * from student where name like '李%' order by (chinese + math + english) desc;

-- 查询男生和女生分别有多少人，并将人数降序排序排序输出，并查询出人数大于4的性别人数信息
select gender, count(*) as total_cnt from student group by gender having total_cnt >4 order by total_cnt desc;

(2).案例2

[1].准备数据

create table emp(
     empno INT, -- 员工编号
     ename varchar(50), -- 员工姓名
     job varchar(50), -- 工作名字
     mgr INT, -- 上级领导编号
     hiredate DATE, -- 入职日期
     sal INT, -- 薪资
     comm INT, -- 奖金
     deptno INT -- 部门编号
);
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2450, NULL, 10);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 3000, NULL, 20);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 5000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 2000, NULL, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-19-08', 1500, 0, 30);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);
INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

[2].按员工编号升序排列不在10号部门工作的员工信息

select * from emp where deptno != 10 order by empno asc; 

[3].查询姓名第二个字母不是’A’且薪水大于1000元的员工信息，按年薪降序排列

 -- 年薪:12*月薪 + 奖金 
 select * from emp where ename not like '_A%' and sal > 1000 order by(12*sal + ifnull(comm, 0)) desc;

[4].求每个部门的平均薪水

 select deptno, avg(sal) as avg_sal from emp group by deptno;  

[5].求各个部门的最高薪水

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

[6].求每个部门每个岗位的最高薪水

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

[7].求平均薪水大于2000的部门编号

select deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal > 2000;      

[8].将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列

select deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal > 2000 order by avg_sal desc;  

[9].选择公司中有奖金的员工姓名，工资

select * from emp where comm is not null;

[10].查询员工最高工资和最低工资的差距

select max(sal) - min(sal) as '薪资差距' from emp;