子乌笔记-MySQL-数据查询DQL篇

已于 2023-11-07 15:19:47 修改
阅读量159 收藏
点赞数
分类专栏： Python+大数据-精通SQL 文章标签： 数据库 python 大数据 mysql
于 2023-08-19 17:08:31 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ziwu1226/article/details/132368369
版权

DQL 数据查询语言导读

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

内容用法
用来查询数据表的数据记录（条数）例如：查询数据、某条件等
常用关键字例如，select、from、where*

在开始本章学习前，我们先准备一串数据，便于后我们进行查询学习！
例如：
（1）先来创建一个数据库班级db_product1，设定编码为utf8；
（2）在库中创建一个product表，字段有pid商品编号、pname商品名称、price商品价格、category_id商品分类；
（3）当成功添加数据内容后，查看数据结果。

##########################添加数据######################################
# 创建数据库
create database if not exists sz53db_product1 charset utf8;

# 使用数据库
use sz53db_product1;

# 创建表
create table product
(
    pid         int primary key auto_increment,
    pname       varchar(20),
    price       double,
    category_id varchar(32)
) engine = InnoDB default charset utf8;

# 插入数据
insert into product values (1,'联想',5000,'c001');
insert into product values (2,'海尔',3000,'c001');
insert into product values (3,'雷神',5000,'c001');
insert into product values (4,'杰克琼斯',800,'c002');
insert into product values (5,'真维斯',200,'c002');
insert into product values (6,'花花公子',440,'c002');
insert into product values (7,'劲霸',2000,'c002');
insert into product values (8,'香奈儿',800,'c003');
insert into product values (9,'相宜本草',200,'c003');
insert into product values (10,'面霸',52,'c003');
insert into product values (11,'好想你枣',56,'c004');
insert into product values (12,'香飘飘奶茶',6,'c005');
insert into product values (13,'海澜之家',190,'c002');
insert into product values (14,'三只松鼠核桃',120,'c005');
insert into product values (15,'洞庭湖鱼',69,'c005');
insert into product values (16,'三文鱼',220,'c005');
insert into product values (17,'蓝月亮',54,'c002');
insert into product values (18,'越南青芒',78,'c00

1. DQL简单查询

简单查询有两种方式：1、查询所有数据；2、按照不同字段来查询数据

SELECT * FROM 表名;	#查询所选表所有字段信息
SELECT [DISTINCT]字段1 AS 别名,字段2,字段3…… FROM 表名;	#查询所选表所选字段信息

注意：
DISTINCT:消除字段字段中重复的值
字段名 AS 别名：查询的结果以别名的方式展现，不改变表结构

例如，使用命令完成：
（1）在数据库班级db_product1中，查询商品表的所有数据信息；
（2）查询商品表的所有名称信息；
（3）查询商品名称、价格、分类的所有结果；
（4）将所有的商品名称、价格+20进行显示出结果；
（5）思考：如何去除重复的价格值并显示所有价格信息？

# （1）在数据库`班级`db_product1中，查询商品表的所有数据信息；
SELECT * FROM product;
# （2）查询商品表的所有名称信息；
SELECT pname FROM product;
# （3）查询商品名称、价格、分类的所有结果；
SELECT pname as '商品名称',price as '价格' FROM product;
# （4）将所有的商品名称、价格+20进行显示出结果；
SELECT pname as '商品名称',price+20 as '价格' FROM product;
# （5）思考：如何去除重复的价格值并显示所有价格信息？
SELECT DISTINCT price FROM product;

1.1DQL比较查询

对数据进行条件筛选处理，通用语法

SELECT * FROM 表名 WHERE 条件

满足WHERE条件的才会输出数据结果

MySQL 中的比较运算符有:

比较运算符含义
=等于
>(=)大于(等于)
<(=)小于(等于)
!= 或 <>不等于，常用 !=

例如，使用命令完成：
（1）在操作商品表时，查询商品表中的所有商品信息；
（2）查询商品名称为"花花公子"的商品所有信息；
（3）查询价格为800的商品信息；
（4）查询价格不是800的所有商品信息；
（5）查询商品价格大于60元的所有商品信息；
（6）查询商品价格小于等于800元的所有商品信息。

# （1）在操作商品表时，查询商品表中的所有商品信息；
SELECT * FROM product;
# （2）查询商品名称为"花花公子"的商品所有信息；
SELECT * FROM product WHERE pname = '花花公子';
# （3）查询价格为800的商品信息；
SELECT * FROM product WHERE price = 800;
# （4）查询价格不是800的所有商品信息；
SELECT * FROM product WHERE PRICE != 800;
# （5）查询商品价格大于60元的所有商品信息；
SELECT * FROM product WHERE price > 60;
# （6）查询商品价格小于等于800元的所有商品信息。
SELECT * FROM product WHERE price <= 800;

1.2 DQL范围查询

范围查询是指在某个范围内进行查询，分别由 IN 和 BETWEEN…AND…

  1. IN适用于非连续性的范围查询，语法：
SELECT * FROM 表名 WHERE 字段名 IN （范围1，范围2）;
  1. BETWEEN…AND…适用于值在一个连续的范围内，语法：
SELECT * FROM 表名 WHERE 字段名 BETWEEN 范围1 AND 范围2;

例如，使用命令完成：
（1）在操作商品表时，查询商品价格是200或800的所有商品信息；
（2）查询商品价格在200-1000之间所有商品信息；
（3）思考：有其他方法来完成查询商品价格在200-1000之间所有商品信息吗？

# （1）在操作商品表时，查询商品价格是200或800的所有商品信息；
SELECT * FROM product WHERE price IN (200,800);
# （2）查询商品价格在200-1000之间所有商品信息；
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
# （3）思考：有其他方法来完成`查询商品价格在200-1000之间所有商品信息`吗？
SELECT * FROM product WHERE price >= 200 AND PRICE <= 1000;

1.3 DQL逻辑查询

MySQL逻辑运算符有：

逻辑查询符含义
AND 与、和、且；都满足才成立
OR或者；其中之一满足则成立
NOT一般情况下，要设定not取反，对整个操作结果做mot处理

例如，使用命令完成：
（1）查询商品价格在200到1000之间所有商品信息；
（2）查询商品价格是200或800的所有商品信息；
（3）查询价格不是800的所有商品；
（4）思考：如果要查询不是200或800的所有商品信息，该怎么做呢？

# （1）查询商品价格在200到1000之间所有商品信息；
SELECT * FROM product WHERE price >= 200 and price <= 1000;
# （2）查询商品价格是200或800的所有商品信息；
SELECT * FROM product WHERE price = 200 or price = 800;
# （3）查询价格不是800的所有商品；
SELECT * FROM product WHERE price != 800;
# （4）思考：如果要查询不是200或800的所有商品信息，该怎么做呢？
SELECT * FROM product WHERE price != 800 and price != 200;

1.4 DQL模糊查询

模糊查询语法：

SELECT * FROM 表名 WHERE 字段名 LIKE '%某字段%';
或
SELECT * FROM 表名 WHERE 字段名 LIKE '某个字_';
字符含义
%表示任意字符
_表示一位字符

例如，使用命令完成：
（1）查询商品名称含有"香"字的所有商品信息；
（2）查询商品名称为三个字的商品信息；
（3）查询商品名称以"斯"结尾，并且是三个字的商品信息；
（4）思考1：查询以"香"开头，且是三个字的商品信息；
（5）思考2：查询以"香"开头的所有商品信息。

# （1）查询商品名称含有"香"字的所有商品信息；
SELECT * FROM product WHERE pname LIKE '%香%';
# （2）查询商品名称为三个字的商品信息；
SELECT * FROM product WHERE pname LIKE '___';
# （3）查询商品名称以"斯"结尾，并且是三个字的商品信息；
SELECT * FROM product WHERE pname LIKE '__斯';
# （4）思考1：查询以"香"开头，且是三个字的商品信息；
SELECT * FROM product WHERE pname LIKE '香__';
# （5）思考2：查询以"香"开头的所有商品信息。
SELECT * FROM product WHERE pname LIKE '香%';

1.5 DQL非空查询查询

非空运算符有：

非空运算符含义
IS NULL
IS NOT NULL判断为非空

例如，使用非空查询来完成：
（1）将商品名称为"香奈儿"的分类category_id修改为null；
（2）查询分类为空的商品信息；
（3）查询分类不为空的所有商品信息。

# （1）将商品名称为"香奈儿"的分类category_id修改为null；
UPDATE product SET category_id = NULL WHERE pname = '香奈儿';
# （2）查询分类为空的商品信息；
SELECT * FROM product WHERE category_id IS NULL;
# （3）查询分类不为空的所有商品信息。
SELECT * FROM PRODUCT WHERE category_id IS NOT NULL;

 

2. DQL高级查询数据

2.1 分页查询

当数据量太多时，为了更快的加载数据内容，可以进行分页处理。

limit分页查询语法：

SELECT * FROM 表名 LIMIT M,N;
  • M为整数，表示从第几条索引开始，计算方式:(当前页 -1)* 每页显示条数;
  • N为整数，表示查询多少条数据

例如，使用命令完成：
（1）从商品信息中开始处查询5条数据；
（2）从商品信息中索引值为5开始，共查询10条数据；
（3）获取当前商品中价格最低的2件商品。

# （1）从商品信息中开始处查询5条数据；
SELECT * FROM product LIMIT 5;
# （2）从商品信息中索引值为5开始，共查询10条数据；
SELECT * FROM product LIMIT 5,10;
# （3）获取当前商品中价格最低的2件商品。
SELECT * FROM product ORDER BY price DESC LIMIT 2;

 

2.2 分组查询

  • 准备数据
    （1）在 班级 db_product2库中，新建一个人物表person，信息有编号id、姓名name、年龄
    age、性别gender、身高height；
    （2）编号id为整型、主键且自动增长，其他信息视具体情况而定；
    （3）往人物表person中添加多条数据后，查询所有数据结果。
# 使用库
use sz53db_product2;
show tables;
# 创建数据表
create table if not exists person
(
id int primary key auto_increment not null,
name varchar(18),
age int,
gender varchar(10),
height double
)engine = InnoDB default charset utf8;
# 插入数据
insert into person(name, age, gender, height) values ('洪七公', 76,
'Male', 1.6);
insert into person(name, age, gender, height) values ('欧阳峰', 68,
'Male', 1.65);
insert into person(name, age, gender, height) values ('郭靖', 43,
'Male', 1.7);
insert into person(name, age, gender, height) values ('黄蓉', 39,
'Female', 1.59);
insert into person(name, age, gender, height) values ('杨过', 26,
'Male', 1.8);
insert into person(name, age, gender, height) values ('小龙女', 33,
'Female', 1.65);
insert into person(name, age, gender, height) values('郭芙', 18,
'Female', 1.60);
insert into person(name, age, gender, height) values ('李莫愁', 35,
'Female', 1.65);
# 查询数据
select * from person;

2.2.1 分组查询group by

分组查询指的是：将查询结果按照指定字段分组，其中，数值相等可划分为同一组
分组查询的语法：

SELECT 字段 FROM 表名 [WHERE] GROUP BY 分组字段名 [HAVING 分组条件]

说明：

  1. 分组字符串：表示按照指定的字段值进行分组；
  2. group by的作用是实现分组，默认可以实现去重操作（DISTINCT）
  3. HAVING 分组条件是用来过滤分组后的数据

例如，使用命令完成：
（1）在人物表person中执行分组查询；
（2）按性别字段进行分组查询；
（3）观察查询结果。

# （1）在人物表person中执行分组查询；
# （2）按性别字段进行分组查询；
# （3）观察查询结果。
SELECT gender FROM person GROUP BY gender;

 

2.2.2 分组查询 + 聚合函数

分组查询+聚合函数的语法：

SELECT 聚合函数（字段列表） FROM 表名;

1、什么是聚合函数
将一列数据作为一个整体，进行纵向计算（列）。
 
2、常见聚合函数有?

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

例如，使用命令完成：
（1）计算平均年龄值；
（2）计算总人物个数；
（3）请统计出不同性别的人平均年龄值；
（4）请统计出不同性别的人总个数。

# （1）计算平均年龄值；
SELECT AVG(age) FROM person;
# （2）计算总人物个数；
SELECT COUNT(`name`) AS '出现个数' FROM person;
# （3）请统计出不同性别的人平均年龄值；
SELECT gender,AVG(age) AS '平均年龄' FROM person GROUP BY gender;
# （4）请统计出不同性别的人总个数。
SELECT gender,count(*) FROM person GROUP BY gender;

 

2.2.3 group by + having的使用

HAVING 用于过滤分组数据，是 GROUP BY 专用，其语法为：

SELECT * FROM GROUP BY 分组字段名 HAVING 条件;

例如，使用命令完成：
（1）请统计各个性别下人物的总个数；
（2）请统计各个性别中当性别为Female时，人物的总个数；
（3）请统计各个性别下人物的总个数，且只显示总个数大于2的信息。

# （1）请统计各个性别下人物的总个数；
SELECT gender, COUNT(*) AS '总个数'
FROM person
GROUP BY gender;
# （2）请统计各个性别中当性别为Female时，人物的总个数；
SELECT gender, COUNT(*) AS '总个数'
FROM person
GROUP BY gender
HAVING gender = 'Female';
# （3）请统计各个性别下人物的总个数，且只显示总个数大于2的信息。
SELECT gender, COUNT(*)
FROM person
GROUP BY gender
HAVING COUNT(*) > 2;

HAVING 与 WHERE 的区别（优先级问题）：

关键字作用
WHERE性能高；先过滤数据，在分组
HAVING先分组，再过滤

 

2.3 排序查询

1、语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式, 字段2 排序方式2;

2、排序方式

关键字作用
ASC升序排序（默认值）
DESC降序排序

如果是多段排序，当第一个字段排序相同，再进行第二个字段进行排序，如“查询公司员工根据年龄进行降序排序，如果有相同，则按照入职时间进行升序排序！”
例如，使用命令完成：
（1）请按照年龄降序输出内容；

# （1）请按照年龄降序输出内容；
SELECT * FROM person
ORDER BY age DESC;

 

2.4 外键和关系

2.4.1 外键约束简介

在MySQL中有两个键FOREIGN KEY与PRIMARY KEY分别代表着外键和主键，而本章学的外键多用于多张数据表之间进行相互联系使用。
比如，有两张数据表，且他们之间有联系。可以通过某个字段来建立连接，这个字段在第一张表A是主键PRIMARY KEY ，在第二章表B中，就被称之为外键约束 FOREIGN KEY。
在这里插入图片描述
添加外键约束语法:

CREATE TABLE 表名(
	…
	字段 数据类型 [约束]
	FOREIGN KEY(字段名) REFERENCES 另外表名(主键列名)
) ENGINE = InnoDB DEFAULT CHARSET utf8;

在通常情况下，FOREIGN KEY 声明一般在末尾。
单独添加外键约束：

ALTER TABLE 表名 ADD FOREIGN KEY(字段名) REFERENCES 另外表名(主键列名);

外键注意事项：

  1. 外键字段名的类型、长度要与另一个表主键保持一致
  2. 当添加了外键约束后，不能轻易修改删除数据。

2.4.2 表与表之间的联系

在正式运用FOREIGN KEY之前，我们需要先了解表与表之间的关系，这种关系是指代表与表之间数据的关联信，分别有：
（1）一对一关系（1:1）
（2）一对多关系（1:n） [常见]
（3）多对多关系（n:m） [常见]

一对一关系
一对一关系，就是在数据表A中的一条数据，在数据表B中都有一个与之对应的结果。
例如，用户表与用户详情表的关系。
请添加图片描述请添加图片描述

一对多关系
一对多是实际开发中，是表与表之间关系里应用最多的。例如分类表与商品。

备注：小米店铺里的分类表与商品表

请添加图片描述
接着，来看看一对多关系的字段表示情况
例如，分类表category与商品表product，其中商品里的cno就是外键约束字段。
请添加图片描述

总结：

  1. 通常情况下，一对多建表关系原则；在【多】的一方有个字段，作为外键字段指向【一】的乙方的主键字段。

多对多关系
多对多关系复杂，通常拆分成多个一对多关系来关联处理
例如，老师与学生
一个老师可以给多名同学上课，同时一名同学可以有多个老师。
请添加图片描述
多对多关系创建表原则：

通常需要创建 第三张表（综合表），作为中间表，至少有两个字段（都是外键），分别☞作各一方的主键
请添加图片描述

2.4.3 一对多关系案例(1:N)

添加外键约束
给数据添加外键约束的语法：

CREATE TABLE IF NOT EXISTS 表名(
	字段1 数据类型.
	…
	字段n 数据类型
	FOREIGN KEY 字段 REFERENCES 另外表名(主键名)
)ENGINE = InnoDB DEFAULT CHARSET utf8;

说明：

  1. 给字段添加外键约束，通常放在所有字段定义结尾处
  2. 要记得设定InnoDB，否则可能无法成功设定外键约束

除了在字段定义中可以设定外键约束外，还可以单独添加外键约束（只需了解）。

ALTER TABLE 表名 ADD FOREIGN KEY(外键字段名) REFERENCES 另一表名(主键名);

例如，使用命令完成：
我们知道，一本书籍要关联出版社，此时多本书可以出自同一个出版社，一个出版社也可以出版多本书。
（1）根据书籍与出版社的关系，设计【书籍-出版社】一对多的关联表；
（2）试着给数据表添加对应的数据值；
（3）查询出清华大学出版社出版的所有图书信息；
（4）查询出《MySQL从入门到精通》书籍的出版社信息。
在这里插入图片描述

# （1）根据书籍与出版社的关系，设计【书籍-出版社】一对多的关联表；
CREATE DATABASE db_foreignkey_test CHARSET utf8;
USE db_foreignkey_test;
# 先创建 出版社表
CREATE TABLE tb_press(
    pid INT AUTO_INCREMENT PRIMARY KEY,
    `pname` VARCHAR(20),
    address VARCHAR(20)
)ENGINE = InnoDB DEFAULT CHARSET utf8;
# 再创建 包含外键的书籍表
CREATE TABLE tb_book(
    bid INT AUTO_INCREMENT PRIMARY KEY,
    bname VARCHAR(20),
    price DOUBLE,
    use_id INT,
    FOREIGN KEY(use_id) REFERENCES tb_press(pid)
)ENGINE = InnoDB DEFAULT CHARSET utf8;
# （2）试着给数据表添加对应的数据值；
INSERT INTO tb_press
VALUES (1,'清华大学出版社','北京市海淀区'),
       (NULL,'北京航空大学出版社','北京市海淀区'),
       (NULL,'机械工业出版社','北京市西城区'),
       (NULL,'新华出版社','北京市石景山区');

INSERT INTO tb_book
VALUES (1,'大数据开发导论',49.90,1),
       (NULL,'Hadoop从入门到精进',58.60,1),
       (NULL,'MySQL从入门到精通',42.00,2),
       (NULL,'Python爬虫开发',99.00,2);
# （3）查询出清华大学出版社出版的所有图书信息；
SELECT * FROM tb_book join tb_press on use_id = pid;
# （4）查询出《MySQL从入门到精通》书籍的出版社信息。
SELECT * FROM tb_book join tb_press on use_id = pid WHERE bname = 'MySQL从入门到精通';

子查询（了解）：
指的是将一条select语句的结果，作为另一条select语句的一部分（如：查询条件、查询结果、表等），来进行数据查询,即SELECT的嵌套查询。
子查询语法：

SELECT * FROM 表1 WHERE 字段名 = (另外SELECT语句)

例如，借用上文的3、4题
（3）查询出清华大学出版社出版的所有图书信息；
（4）查询出《MySQL从入门到精通》书籍的出版社信息。

USE db_foreignkey_test;
# （3）查询出清华大学出版社出版的所有图书信息；
SELECT bname FROM tb_book WHERE bid = (select pid from tb_press where pname = '清华大学出版社');
# （4）查询出《MySQL从入门到精通》书籍的出版社信息。
SELECT * FROM tb_press WHERE pid = (SELECT bid FROM tb_book WHERE bname = 'MySQL从入门到精通');

2.4.2 多对多案例(M:N)

为了更好的理解多对多关系与设计原则。

创建表原则:引入一个第三方中间表,且这个表至少有两个字段。

分析：订单表和货物表之间的多对多关系。
（1）货物表的字段信息有编号、名称、品牌、价格；
（2）订单表的字段信息有编号、订单金额。
请添加图片描述
例如，使用命令完成：
（1）新建数据库班级db_multi_product，并在其中创建货物表、订单表；
（2）货物表的字段信息有编号、名称、品牌、价格，其中货物编号为整型且自动增长的主键；
（3）订单表的字段信息有编号、订单金额，其中订单编号为字符串型的主键；
（4）创建一个中间表，并关联货物表、订单表；
（5）当成功创建表后，观察效果。

# （1）新建数据库`班级`db_multi_product，并在其中创建货物表、订单表；
USE db_multi_product;
CREATE DATABASE db_multi_product;
# （2）货物表的字段信息有编号、名称、品牌、价格，其中货物编号为整型且自动增长的主键；
CREATE TABLE tb_goods(
    gid INT AUTO_INCREMENT PRIMARY KEY,
    gname VARCHAR(255),
    brand VARCHAR(255),
    g_price DOUBLE
)ENGINE InnoDB DEFAULT CHARSET utf8;
# （3）订单表的字段信息有编号、订单金额，其中订单编号为字符串型的主键；
CREATE TABLE tb_oder(
    oid VARCHAR(255) PRIMARY KEY,
    o_price DOUBLE
)ENGINE InnoDB DEFAULT CHARSET utf8;
# （4）创建一个中间表，并关联货物表、订单表；
CREATE TABLE goods_oder(
    go_id INT AUTO_INCREMENT Primary Key ,
    goods_id INT,
    oder_id VARCHAR(255),
    FOREIGN KEY (goods_id) REFERENCES tb_goods(gid),
    FOREIGN KEY (oder_id) REFERENCES tb_oder(oid)
)ENGINE InnoDB DEFAULT CHARSET utf8;
# （5）当成功创建表后，观察效果。

2.4 多表查询

多表查询，也可称为连接查询，可用于实现多个数据表之间的数据查询。

注意：当查询的字段数据来自不同的数据表时，就可以使用多表查询来完成。

多表查询可以分为4类：

名称作用
交叉连接查询
内连接
外连接
自连接

在学习多表查询操作前，我们先准备基础数据并添加至表中。

例如，使用命令完成：
（1）商品类别表信息有分类编号cid、名称cname；
（2）商品表信息有商品编号pid、名称pname、价格price、是否上架flag、外键category_id（关联商品类别表的分类编号cid）；
（3）添加数据到两个数据表中。

# 多表查询
create table table_category
(
    cid   varchar(20) primary key,
    cname varchar(40)
);

create table table_product
(
    pid         varchar(20) primary key,
    pname       varchar(40),
    price       double,
    flag        varchar(2), # 是否上架标记为：1表示上架、0表示下架
    category_id varchar(20),
    foreign key (category_id) references table_category(cid)  # constraint products_fk
);

# 分类
insert into table_category(cid, cname) values('c001', '电器');
insert into table_category(cid, cname) values('c002', '服装');
insert into table_category(cid, cname) values('c003', '化妆品');

# 商品
insert into table_product(pid, pname, price, flag, category_id) values('p001', '联想', 5000, '1', 'c001');
insert into table_product(pid, pname, price, flag, category_id) values('p002', '海尔', 3000, '1', 'c001');
insert into table_product(pid, pname, price, flag, category_id) values('p003', '雷神', 5000, '1', 'c001');

insert into table_product (pid, pname, price, flag, category_id) values('p004', 'JACK JONES', 800, '1', 'c002');
insert into table_product (pid, pname, price, flag, category_id) values('p005', '真维斯', 200, '1', 'c002');
insert into table_product (pid, pname, price, flag, category_id) values('p006', '花花公子', 440, '1', 'c002');
insert into table_product (pid, pname, price, flag, category_id) values('p007', '劲霸', 2000, '1', 'c002');

insert into table_product (pid, pname, price, flag, category_id) values('p008', '香奈儿', 800, '1', 'c003');
insert into table_product (pid, pname, price, flag, category_id) values('p009', '相宜本草', 200, '1', 'c003');
insert into table_product (pid, pname, price, flag, category_id) values('p010', '迪奥', 1600, '1', 'c003');
insert into table_product (pid, pname, price, flag, category_id) values('p011', '一叶子', 799, '1', 'c003');

2.4.1 交叉查询

在MySQL多数据表查询操作中，交叉连接查询操作几乎不会使用，因为交叉连接没有意义，但是它是所有连接查询的基础。在这里仅作为了解即可。

通常地，交叉连接查询的结果是：两个数据表的并集，也称为笛卡尔积。
请添加图片描述
交叉连接查询的语法：

SLELCT 字段名 FROM A表名,B表名;

或者

SELECT 字段名 FROM A表名 CROSS JOIN B表名;

例如，使用交叉查询命令完成：
（1）使用语法1查询类别表和商品表中的所有商品信息；
（2）使用语法2查询类别表和商品表中的所有商品信息。

# （1）使用语法1查询类别表和商品表中的所有商品信息；
SELECT * FROM table_category,table_product ;
# （2）使用语法2查询类别表和商品表中的所有商品信息。
SELECT * FROM table_category CROSS JOIN table_product;

2.4.2 内连接查询

内连接查询是指两个数据表中，符合条件的共有记录，即交集
在这里插入图片描述
对于内连接查询，可分为两类，分别是：隐式内连接和显式内连接。
（1）作为了解，隐式内连接是指没有inner join关键字，而条件直接使用where来指定，语法：

SELECT 字段名 FROM A表名 别名, B表名 别名 WHERE 条件;

（2）显式内连接是指需要使用inner join … on语句，可以省略inner(推荐)，语法：

SELECT 字段名 FROM A表名 别名 [INNER] JOIN B表名 别名 ON 条件 [where 查询条件];
# 在编程过程中，一般省略INNERQ，且结尾处处可用where或ON

例如，使用内连接查询命令完成：
（1）使用隐式内连接查询类别表和商品表的共有数据信息；
（2）使用显式内连接来查询类别表和商品表的公共商品信息；
（3）使用显式内连接来查询类别表中cid与商品表category_id相等的公共商品信息的类别编号信息，并去除重复值。

# （1）使用隐式内连接查询类别表和商品表的共有数据信息；
SELECT p.pname, p.price, c.cname
FROM table_product p,
     table_category c
WHERE p.category_id = c.cid;
# （2）使用显式内连接来查询类别表和商品表的公共商品信息；
SELECT p.pname, p.price, c.cname
FROM table_product p
         INNER JOIN table_category c ON p.category_id = c.cid;
# （3）使用显式内连接来查询类别表中cid与商品表category_id相等的公共商品信息的类别编号信息，并去除重复值。
SELECT DISTINCT c.cname
FROM table_product p
         INNER JOIN table_category c ON p.category_id = c.cid;

2.4.3 外连接查询

在多表查询操作中，外连接查询需要用到关键字 outer join，一般运用中会省略outer，而使用john。
此外，外连接查询还可以分成两类，分别是:① 左外连接查询（LEFT JOIN）、② 右外连接查询（RIGHT JOIN）
了区分A表名和B表名，简易命名为左表和右表
在这里插入图片描述

  • ① 左外连接查询
    左外连接也简称为左连接，是以坐标为主并根据条件查询右表数据
    在这里插入图片描述

说明：
如果根据条件查询，当右表数据不存在时，则会使用null值来填充。

左外连接查询需要使用left join关键字，语法：

SELECT 字段名 FROM A表名 LEFT [OUTER] JOIN B表名 别名 ON 条件;

说明：
（1）left join指的是左外连接查询关键字；
（2）on后面的是连接查询条件；
（3）A表名是指左表，B表名是指右表。

例如，使用左外连接查询命令完成：
（1）以左表为主，连接查询类别表和商品表中的所有商品数据信息。
在这里插入图片描述

# 使用库
USE db_ziwu01;
# 查看数据表
SELECT * FROM table_category;
SELECT * FROM table_product;
# 左连接数据表
SELECT * FROM table_category LEFT JOIN table_product ON table_product.category_id=table_category.cid;
  • ② 右外连接查询
    在这里插入图片描述
    左外连接查询需要使用left join关键字，语法：
SELECT 字段名 FROM A表名 RIGHT [OUTER] JOIN B表名 别名 ON 条件;

说明：
（1）right join指的是右外连接查询关键字；
（2）on后面的是连接查询条件；
（3）A表名是指左表，B表名是指右表。

例如，使用右外连接查询命令完成：
（1）以右表为主，连接查询类别表和商品表中的所有类别数据信息。

# 使用库
USE db_ziwu01;
# outer
SELECT *
FROM table_category c
RIGHT OUTER JOIN table_product p ON c.cid = p.category_id;
# 省略outer
SELECT *
FROM  table_category c
RIGHT JOIN table_product p ON c.cid=p.category_id;

左外连接与右外连接的区别
仔细观察操作的数据结果，会发现左外连接与右外连接都是一样的。
那么，它们有什么区别呢？

（1）当左表有数据为null时，左外连接查询的结果中，右表的数据结果都为null值；
（2）当右表有数据为null时，右外连接查询的结果中，左表的数据结果都为null值。

例如，使用命令完成：
（1）给类别表添加一条名称为null的数据；
（2）给商品表添加一条外键category_id为null的数据；
（3）查询类别表、商品表的数据结果，观察数据是否已成功添加；
（4）使用左外连接以左表为主，连接查询类别表和商品表中的所有商品数据信息，观察查询结果；
（5）使用右外连接以右表为主，连接查询类别表和商品表中的所有类别数据信息，观察查询结果。

# 使用库
USE db_ziwu01;
# （1）给类别表添加一条名称为null的数据；
INSERT INTO table_category
values ('coo4',NULL);
# （2）给商品表添加一条外键category_id为null的数据；
INSERT INTO table_product
VALUES ('p0012','玛莎拉蒂',100000,1,Null);
# （4）使用右外连接以右表为主，连接查询类别表和商品表中的所有商品数据信息，观察查询结果；
SELECT *
FROM table_category c
RIGHT OUTER JOIN table_product p 
ON c.cid = p.category_id;
# （5）使用左外连接以左表为主，连接查询类别表和商品表中的所有类别数据信息，观察查询结果。
SELECT *
FROM table_category c
LEFT JOIN table_product p
ON c.cid = p.category_id;

2.4.4 自连接查询

自连接查询，顾名思义，就是数据表自己和自己链接，并进行数据比较；自连接的左表和右表都是同一个表，更具查询条件来查询两个表之间的数据。

在进行自连接操作时，必须要对查询表进行别名操作！

接下来我们使用自连接完成一个简易的区域表
在这里插入图片描述
例如，使用自连接查询命令完成：
（1）创建一个区域表tb_area，信息有省编号pid为整型自增且主键、名称name为字符串、城
市编号city_id为整型；
（2）给区域表tb_area添加批量数据，以(1,‘广东省’,null)、(4,‘深圳市’,1)形式让城市和省份进行关联
（3）把区域表tb_area分别理解成两个表：省表province、城市表city；
（4）自连接查询省份编号、省名、城市名、城市编号的展示结果；
（5）自连接查询省的名称为广东省的所有城市信息。

# 创建库
CREATE DATABASE db_self_join CHARSET utf8;
Use db_self_join;
# (1)创建一个区域表tb_area，信息有省编号pid为整型自增且主键、名称name为字符串、城市编号city_id为整型；
CREATE TABLE weather
(
    pid     INT AUTO_INCREMENT PRIMARY KEY,
    `name`  VARCHAR(20),
    city_id INT
) ENGINE InnoDB
  DEFAULT CHARSET utf8;
# (2)给区域表tb_area添加批量数据，以(1,'广东省',null)、(4,'深圳市',1)形式让城市和省份进行
# 关联；
INSERT INTO weather (name, city_id)
VALUES ('广东省', NULL),
       ('福建省', NULL),
       ('广西省', NULL),
       ('深圳市', 1),
       ('东莞市', 1),
       ('柳州市', 3),
       ('桂林市', 3),
       ('厦门市', 2),
       ('莆田市', 2);
SELECT *
FROM weather;
# （3）把区域表tb_area分别理解成两个表：省表province、城市表city；
# （4）自连接查询省份编号、省名、城市名、城市编号的展示结果；
SELECT *
FROM weather province
         JOIN weather city
              ON city.city_id = province.pid;
# （5）自连接查询省的名称为广东省的所有城市信息。
SELECT *
FROM (SELECT province.pid pid, province.name pname, city.name cname
      FROM weather province
               JOIN weather city
                    ON city.city_id = province.pid) AS temp
WHERE pname = '广东省';
ziwu1226
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
python 读取mysql大量数据处理_Python 频繁读取Mysql相关问题
weixin_39981093的博客
12-05 1240
1、需要频繁select大量数据，时间长、消耗内存大，如何解决mysql性能问题？如果对返回的结果数量没有要求，可以控制返回的数量：cursor.fetchmany(size=1000)这样是只返回1000条数据，如果返回的结果小于size，则返回所有数据；如果你只需要一条，则更简单：fetchone()2、每次插入的数据过大，MySQL server has gone away　如何解决？存储为...
python 读取mysql大量数据处理,在python中读取巨大的MySQL表的最快方法
weixin_39623350的博客
03-01 1253
I was trying to read a very huge MySQL table made of several millions of rows. I have used Pandas library and chunks. See the code below:import pandas as pdimport numpy as npimport pymysql.cursorsconn...
【数据平台】基于pymysql库python连接mysql
医疗影像检索
09-14 348
1、场景：python连接mysql的API使用pymysql库       https://pypi.python.org/pypi/PyMySQL/      安装：pip install pymysql 即可 2、代码示例： #!/usr/bin/env python # --coding = utf-8 # Author Jason.F import pymysql
python通过什么连接数据库_如何使用python连接数据库？
weixin_39730911的博客
11-20 390
数据分析离不开数据库，如何使用python连接数据库呢？听我娓娓道来哈该笔记参考了PyMySQL官方文档和《python数据采集》关于数据存储的部分，欢迎大家去阅读原著，相信会理解的更加透彻。补充：文末增加Oracle数据库的连接方式，大同小异。背景：我是在Anaconda notebook中进行连接实验的，环境Python3.6，当然也可以在Python Shell里面进行操作。最常用也最稳定的...
Python_MySQL 数据库操作
cwy0502的博客
06-17 837
数据库操作 一、创建数据库 CREATE DATABASE 库名 DEFAULT CHARSET=UTF8; mysql> create DATABASE python21 default charset=utf8; 二、选择 / 切换 数据库 在你连接到 MySQL 数据库后，可能有多个可以操作的数据库，所以需要选择即将要操作的数据库。 mysql> USE python21; ...
Python-关于子乌书简的一个小爬虫
08-10
3. **数据存储**：抓取到的数据需要存储起来，可以是文本文件、CSV、JSON，或者直接存入数据库如SQLite、MySQL。对于大量数据，考虑使用数据库会更合适。 4. **异常处理**：在爬虫运行过程中，可能会遇到各种异常，...
javascript的函数第1/3页
12-12
Permadi译者：Sheneyan(子乌)英文原文： INTRODUCTION TO JavaScript Functions中文译文(包括示例)：javascript的函数子乌注：一篇相当不错的function入门文章，个人感觉相当经典。 词语翻译列表: function:函数...
javascript的函数
12-08
 Permadi 译者：Sheneyan(子乌) 时间：2006.01.03 英文原文： INTRODUCTION TO JavaScript Functions 中文译文(包括示例)：javascript的函数 子乌注：一篇相当不错的function入门文章，个人感觉相当经典。...
Python批量删除mysql中千万级大量数据
python爬虫人工智能大数据
01-29 1083
场景描述线上mysql数据库里面有张表保存有每天的统计结果，每天有1千多万条，这是我们意想不到的，统计结果咋有这么多。运维找过来，磁盘占了200G，最后问了运营，可以只保留最近3天的，前...
python连接操作mysql数据库
大数据ING的博客
07-05 412
用python来实现对mysql增删改查的操作，具体代码如下：#!/usr/bin/python # -*- coding: UTF-8 -*- import MySQLdb #打开数据库连接 db = MySQLdb.connect("localhost","root","1181ff69","test_demo",charset='utf8' ) # 使用cursor()方法获取操作游...
[bigdata-027] 最好用的 python+mysql的包：pymysql
大数据和机器学习研究
01-11 1132
pip3 install pymysql import pymysql
大数据测试之使用python快速插入mysql 1万条数据
小生测试的博客
10-07 2857
背景 大数据测试，有时候需要造大量数据（造数可以看我另外一篇文章），而且需要经常使用脚本快速插入数据，插入数据一般常用的两种方式，一种是接口，一种是数据库插入。按照正常的逻辑，使用接口造数是最好的方式，因为不需要去关注数据库字段关联的问题，但接口会有瓶颈，需要依赖接口的稳定型以及性能。如果接口的稳定性，性能不行，那就只能是使用数据库插入的方式进行了。 脚本逻辑 根据业务逻辑，一个流程当作一个事务，...
Python大数据处理方案
热门推荐
大数据指北
03-26 3万+
Mysql SQLyog导入导出csv文件SQLyog 导出表中数据存为csv文件1.    选择数据库表 --&gt; 右击属性 --&gt; 备份/导出 --&gt; 导出表数据作为 --&gt; 选择cvs --&gt; 选择下面的“更改” --&gt; 字段 --&gt; 可变长度--&gt; 字段终止与 --&gt;输入逗号，(这是重点，否则导出的csv文件内容都在一列中，而不是分字段分...
python基础：与mysql连接，大数据分析等大型项目不可或缺的功能
07-15 179
python基础：与mysql连接，大数据分析等大型项目不可或缺的功能 登陆mysql 本人用的时ubuntu系统，试图登陆mysql时失败。于是去问度娘，度娘没让我失望。 首先sudo vim /etc/mysql/debian.cnf 这个文件里有登陆用的用户名和密码 一般文件的内容时这样的： Automatically generated for Debian scripts. DO NOT TOUCH! [client] host = localhost user = debian-sys-main
简单的 mongoDB 学习
qq_19342829的博客
07-31 418
mongodb入门学习整理
社区养老服务小程序的设计
最新发布
Karen198的博客
07-31 308
管理员账户功能包括：系统首页，个人中心，用户管理，服务人员管理，服务产品管理，服务预约管理，服务状态管理，服务退订管理，活动管理，视频管理。主要技术：Java,Spring,mybatis,mysql,jquery,html。服务器：SpringBoot自带 apache tomcat。微信端账号功能包括：系统首页，服务产品，活动，视频，我的。JDK版本：Java JDK1.8。数据库可视化工具： navicat。数据库版本： mysql5.7。开发工具：IDEA(推荐)开发系统：Windows。
二进制部署k8s集群之master节点和etcd数据库集群（上）
zx52306的博客
07-30 1111
【代码】二进制部署k8s集群之master节点和etcd数据库集群（上）
【iOS】——持久化
m0_73974920的博客
07-30 1035
因为plist文件不能够保存自定义对象。如果一个字典中保存有自定义对象，如果把这个对象写入到文件当中，它是不会生成 plist文件的。如果对象是NSString、NSDictionary、NSArray、NSData、NSNumber等类型，可以直接用NSKeyedArchiver进行归档和恢复。
已经10岁的K8S，在很多方面仍需努力
u012516914的专栏
07-28 338
/ K8S 已经 10 岁了，但仍然有许多方面要继续努力 /Kubernetes 于 2014 年 6 月推出，自那时起，它在推广云原生应用设计和支持更多微服务部署方面发挥了巨大作用。容器部署的增长非常迅速，而 Kubernetes 对于企业管理这些部署至关重要——根据 CNCF 上一份报告的调查结果，84% 的组织正在使用或评估 Kubernetes，而 66% 的潜在和实际消费者在生产中使用 ...
MySQL-数据操作语言
一直走的很慢的鸭子
07-27 715
DML英文全称是Data Manipulation Language（数据操作语言），用来对数据库中表的数据记录进行增删改操作。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值