Logo

菜单

MySQL学习笔记（二）：DQL

MirrorYuChen
MirrorYuChen
发布于 2024-11-12 / 12 阅读
0
0

MySQL学习笔记（二）：DQL

#mysql#DQL

内容概览

1 概念

​ 数据库系统一个重要功能就是数据查询，数据查询不应是简单返回数据库中存储的数据，还应该根据需求对数据进行筛选，并将数据按照格式进行显示。MySQL提供了功能强大、灵活的语句来实现这些操作，MySQL数据库使用 select语句来查询数据。

2 语法格式

  • 完整语法：
select 
	[all | distinct]
	<目标列表达式1> [别名],
	<目标列表达式2> [别名], ...
from <表名或视图名> [别名], <表名或视图名> [别名] ...
[where<条件表达式>]
[group by <列名> [having <条件表达式>]]
[order by <列名> [asc|desc]]
[limit <数字或列表>];

3 基本排序

select *| 列名 from 表 where 条件;
  • 例子
-- 1.创建数据库
create database if not exists mydb;

-- 2.切换数据库
use mydb;

-- 3.创建商品表
create table product(
	pid int primary key auto_increment,
	pname varchar(20) not null,
	price double,
	category_id varchar(20)
);

-- 4.添加数据
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, '花花公子夹克', 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, '三只松鼠坚果', 80, 'c004');

-- 5.简单查询
select * from product;

-- 6.查询商品名和价格
select pname, price from product;

-- 7.别名查询
-- 7.1 表别名：输出结果显示别名，其中as可以省略掉
select * from product as p;
-- 7.2 列别名：输出结果显示别名
select pname as pn from product;

-- 8.去除重复值
select distinct price from product;

-- 9.查询结果进行表达式运算：将查询后结果+10，不会更改原表内容
select pname, price + 10 from product;

-- 10.查询商品名为海尔洗衣机的商品
select * from product where pname = '海尔洗衣机';

-- 11.查询价格不是800的商品
select * from product where price != 800;
select * from product where price <> 800;

-- 12.查询商品价格大于60的商品
select * from product where price > 60;

-- 13.查询价格在200到800之间的商品
select * from product where price between 200 and 800;
select * from product where price >= 200 and price <= 800;

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

-- 15.查询包含'裤'字的所有商品
select * from product where pname like '%裤%';  -- % 表示匹配任意字符

-- 16.查询第二个字为'蔻'的所有商品
select * from product where pname like '_蔻%'; -- 下划线表示匹配单个字符

4 排序查询

  • 如果我们需要对读取数据进行排序，就可以使用MySQL的 order by子句来设定你按照哪个字段哪种方式进行排序，再返回搜索结果。
select 
	字段名1, 字段名2, ...
	from 表名
	order by 字段名1 [asc|desc], 字段名2 [asc|desc] ...
  • asc代表升序，desc代表降序，默认为升序，order by子句放在查询语句最后面，LIMIT子句除外。
-- 1.按照价格排序(降序)
select * from product order by price desc;

-- 2.在价格排序(降序)基础上，以pid排序(降序)
select * from product order by price desc, pid desc;

-- 3.去重排序
select distinct price from product order by price desc;

5 聚合查询

  • 之前查询都是横向查询，他们都是根据条件一行一行的进行判断，而使用聚合查询函数查询是纵向查询，它对一列值进行计算，然后返回一个单一值，另外聚合函数会忽略空值。
聚合函数 描述
count() 统计指定列不为null的记录行数
sum() 计算指定列的数值和，若指定列类型不是数值类型，计算结果为0
max() 计算指定列的最大值，若指定列是字符串类型，哪个使用字符串排序运算
min() 计算指定列的最小值，若指定列是字符串类型，那么使用字符串排序运算
avg() 计算指定列的平均值，若指定列类型不是数值类型，那么计算结果为0 
-- 1.查询商品总条数
select count(pid) 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 min(price) from product;
select max(price) max_price, min(price) min_price from product;

-- 5.查询分类为'c002'所有商品的平均价格
select avg(price) from product where category_id = 'c002';
  • 聚合函数对于null处理：直接忽略null

6 分组查询

  • 分组查询是指使用 group by字段对查询信息进行分组。

​ 语法格式：

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

​ 示例：

-- 1.统计各个分类商品个数
select category_id, count(pid) from product group by category_id;
  • 若进行分组，select子句后只能出现分组字段和统计函数，其它字段不能出现。group by子句后面可以跟多个字段，字段间使用逗号隔开。
  • 分组后条件筛选使用 having关键字：where子句用来筛选 from子句指定操作所产生结果，having子句用于从分组结果中筛选结果。
-- 2.统计各个分类商品个数，且只显示个数大于4的信息
select category_id, count(*) from product group by category_id having count(*) > 4;

-- 3.统计各个分类商品个数，取一个别名cnt，且只显示个数大于4的信息，并进行降序排序
select category_id, count(*) cnt from product group by category_id having cnt > 2 order by cnt desc;

7 分页查询

  • 分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需求采取分页显示方式，如，数据共30条，每页显示5条，第一页1-5条，第二页6-10条。

​ 语法格式

# 方式一：显示前n条
select 字段1, 字段2, ... from 表名 limit n;

# 方式二：分页显示
select 字段1, 字段2, ... from 表名 limit m, n;
m: 整数，表示从第m条索引开始，注意：索引从0开始计数
n: 整数，表示查询多少条数

​ 示例

-- 1.查询product前5条数据
select * from product limit 5;

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

8 数据导入

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

​ 语法格式

# 格式一：
insert into Table2(field1, field2, ...) select value1, value2, ... from Table1;
# 格式二：
insert into Table2 select * from Table1;
  • 要求目标表Table2必须存在。
  • 示例
-- 1.创建新表
create table product1(
	pname varchar(20),
	price double
);

-- 2.将product表插入到product1
insert into product1(pname, price) select pname, price from product;

-- 3.查询product1表内容
select * from product1;

-- 4.创建新统计表
create table product2(
	category_id varchar(20),
	product_count int
);

-- 5.进行统计
insert into product2 select category_id, count(*) from product group by category_id;

-- 6.查看统计结果
select * from product2;

​ 综合练习一：

-- 1.切换数据库
use mydb;

-- 2.创建表
create table student(
	id INT,
	name VARCHAR(20),
	gender VARCHAR(20),
	chinese INT,
	english INT,
	math INT
);

-- 3.插入数据
INSERT INTO student VALUES
	(1, '张三', '男', 89, 90, 100),
	(2, '李四', '男', 70, 90, 63),
	(3, '刘小花', '女', 60, 95, 80),
	(4, '徐三毛', '男', 89, 89, 100),
	(5, '刘小刚', '男', 89, 90, 80),
	(6, '陈明', '男', 79, 90, 88),
	(7, '徐三', '男', 89, 90, 71),
	(8, '冯宝宝', '女', 89, 90, 99),
	(9, '熊桥', '男', 60, 90, 77),
	(10, '徐甜甜', '女', 89, 90, 65);

INSERT INTO student VALUES(10, '徐甜甜', '女', 89, 90, 65);

-- 4.查询所有信息
select * from student;

-- 5.查询学生姓名和英语成绩
select name, english from student;

-- 6.过滤掉重复成绩
select distinct * from student;

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

-- 8.在每个学生总学分上加10特长分
select name, (chinese + english + math + 10) as total_score from student;

-- 9.使用别名表示学生分数，as可以省略
select name as '姓名', chinese as '中文', english as '英语', math as '数学' from student;

-- 10.查询英语成绩大于90
select * from student where english > 90;

-- 11.查询总分大于250
select * from student where (chinese + english + math) > 250;

-- 12.查询中文成绩在80-90之间的
select * from student where chinese between 80 and 90;

-- 13.查询中文成绩不在80-90之间的
select * from student where chinese not between 80 and 90;

-- 14.查询数学成绩为88, 99, 100的
select * from student where math in (88, 99, 100);

-- 15.查询所有姓徐的学生成绩
select * from student where name like '徐%';

-- 16.查询英语成绩为90或总分大于250的学生
select * from student where english = 90 or (chinese + math + english) > 250;


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

-- 18。对总分排序降序输出
select * from student order by (chinese + math + english) desc;

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

-- 20.查询男生和女生分别多少人，并将人数降序输出
select gender, count(*) as '人数' from student group by gender order by count(*) desc;

​ 综合练习二：

-- 1.切换数据库
use mydb;

-- 2.创建表
create table employee(
	empno int,
	ename varchar(50),
	job varchar(50),
	mgr int,
	hiredate date,
	salary int,
	price int,
	deptno int
);

-- 3.插入一些数据
insert into employee values(1101, 'smith', 'CLERK', 1104, '1980-12-17', 800, NULL, 20);
insert into employee values(1102, 'allen', 'SALESMAN', 1104, '1980-12-17', 1600, 300, 30);
insert into employee values(1103, 'ward', 'SALESMAN', 1104, '1980-12-17', 1250, 500, 30);
insert into employee values(1104, 'jones', 'MANAGER', NULL, '1980-12-17', 2975, NULL, 10);
insert into employee values(1105, 'martin', 'SALESMAN', 1106, '1980-12-17', 1250, 1400, 30);
insert into employee values(1106, 'blake', 'MANAGER', NULL, '1980-12-17', 2850, NULL, 10);
insert into employee values(1101, 'clark', 'MANAGER', NULL, '1980-12-17', 2450, NULL, 10);
insert into employee values(1107, 'scott', 'ANALYST', 1101, '1980-12-17', 3000, NULL, 40);

-- 4.按照员工编号升序排列不在10号部门工作的员工信息
select * from employee where deptno != 10 order by empno asc;


-- 5.查询姓名第二个字母不是'a'且薪水大于1000的员工信息，按年薪降序排列
select * from employee where ename not like '_a%' and salary > 1000 order by (salary * 12 + ifnull(price, 0)) desc;

-- 6.计算每个部分的平均薪水
select deptno, avg(salary) as avg_salary from employee group by deptno;

-- 7.计算每个部分最高薪水
select deptno, max(salary) as max_salary from employee group by deptno;

-- 8.计算每个部分，每个岗位最高薪水
select deptno, job, max(salary) as max_salary from employee group by deptno, job;

-- 9.求平均薪水大于2000的部分编号，并降序排序
select deptno, avg(salary) as avg_salary from employee group by deptno having avg(salary) > 2000 order by avg_salary desc;


-- 10.选择公司最终有奖金员工名字和工资
select * from employee where price is not null;

-- 11.查询公司最好工资和最低工资的差距
select max(salary) - min(salary) as '薪资差距' from employee;

9 正则表达式

  • 正则表达式(regular expression)描述了一种字符串匹配规则，正则表达式本身就是一个字符串，使用这个字符串来描述和定义匹配规则，进而匹配一系列符合某个句法规则的字符串。在开发过程中，正则表达式常用来检索、替换某些符合规则的文本。MySQL通过 REGEXP关键字支持正则表达式进行字符串匹配。
模式 描述
^ 匹配输出字符串开始位置
$ 匹配输出字符串结束位置
. 匹配除"\n"之外的任何单个字符
[...] 字符集合，匹配所包含的任意一个字符，如，'[abc]'可以匹配"plain"中的'a'。
[^...] 负值字符集合，匹配未包含的任意字符，如，'[^abc]'可以匹配"plain"中的'p'。
`p1 p2
* 匹配前面子表达式零次或多次，如，'zo*'能匹配'z'以及'zoo'。*等价于{0,}。
+ 匹配前面子表达式一次或多次，如，'zo+'能匹配'zo'即'zoo'，但不能匹配'z'。+等价于{1,}。
{n} n是一个非负整数，匹配确定的n次，如，'o{2}'不能匹配'Bob'中的'o'，但能匹配'food'中的两个o。
{n,m} m和n均为非负整数，其中n<=m，最少匹配n次且最多匹配m次。

​ 示例：

use mydb;

-- 1 ^在字符串开始处进行匹配
select 'abc' REGEXP '^a';
select * from product where pname REGEXP '^海';

-- 2 $在字符串末尾开始匹配
select 'abc' REGEXP 'a$';  -- 0 是否以a结尾
select 'abc' REGEXP 'c$';  -- 1 是否以c结尾
select * from product where pname REGEXP '衣$';

-- 3 .匹配除了换行符外的任意单个字符
select 'abc' REGEXP '.b';  -- 1 是否b的前面有个字符
select 'abc' REGEXP '.c';  -- 1 是否c的前面有个字符
select 'abc' REGEXP 'a.';  -- 1 是否a的后面有个字符

-- 4 [...]匹配括号内任意单个字符
select 'abc' REGEXP '[xyz]';  -- 0 xyz中任意一个字符有没有在前面出现
select 'abc' REGEXP '[xaz]';  -- 1 xaz中任意一个字符有没有在前面出现

-- 5 [^...]是否没有出现括号中任意单个字符
select 'a' REGEXP '[^abc]';   -- 0 是否包含至少一个非abc的字符串
select 'ac' REGEXP '[^abc]';  -- 0 是否包含至少一个非abc的字符串
select 'ab' REGEXP '[^abc]';  -- 0 是否包含至少一个非abc的字符串
select 'x' REGEXP '[^abc]';   -- 1 是否包含至少一个非abc的字符串
select 'abc' REGEXP '[^a]';   -- 1 是否包含至少一个非a的字符串

-- 6 a*匹配0个或多个a，包括空字符串，可以作为占位符使用，有没有指定字符都可以匹配到数据
select 'stab' REGEXP '.ta*b';  -- 1 a出现0次或多次
select 'stb' REGEXP '.ta*b';   -- 1 a出现0次或多次
select '' REGEXP 'a*';         -- 1 a出现0次或多次

-- 7 a+匹配1次或多次a
select 'stab' REGEXP '.ta+b';  -- 1 a出现1次或多次
select 'stb' REGEXP '.ta+b';   -- 0 a出现1次或多次


-- 8 a?匹配0个或1个a
select 'stb' REGEXP '.ta?b';     -- 1 a出现0次或1次
select 'stab' REGEXP '.ta?b';    -- 1 a出现0次或1次
select 'staab' REGEXP '.ta?b';   -- 0 a出现0次或1次

-- 9 a1|a2匹配a1或a2
select 'a' REGEXP 'a|b';         -- 1 出现a或b
select 'b' REGEXP 'a|b';         -- 1 出现a或b
select 'b' REGEXP '^(a|b)';      -- 1 以a或b开头
select 'a' REGEXP '^(a|b)';      -- 1 以a或b开头
select 'c' REGEXP '^(a|b)';      -- 0 以a或b开头

-- 10 a{m} a出现m次 
select 'auuuuc' REGEXP 'au{4}c';  -- 1
select 'auuuuc' REGEXP 'au{3}c';  -- 0

-- 11 a{m,} a出现次数大于等于m 
select 'auuuuc' REGEXP 'au{4,}c';  -- 1
select 'auuuuc' REGEXP 'au{3,}c';  -- 1

-- 12 a{m, n} a至少出现m次，至多出现n次
select 'auuuuc' REGEXP 'au{2,3}c';  -- 0
select 'auuuc' REGEXP 'au{2,3}c';   -- 1

-- 13 (abc) abc作为整体序列匹配
select 'xababy' REGEXP 'x(abab)y';       -- 1 
select 'xababy' REGEXP 'x(ab)*y';        -- 1 ab整体出现0次或多次
select 'xababy' REGEXP 'x(ab){1,2}y';    -- 1 ab整体出现1次到2次

10.参考资料

Socket网络编程学习笔记
MySQL学习笔记（一）：DDL和DML

评论