MySQL数据库高级查询和多表查询

MySQL多表查询

添加练习表

-- 用户表（user）
CREATE TABLE `user`(
	`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户id(主键)',
	`username` VARCHAR(50) COMMENT '用户姓名',
	`age` CHAR(3) COMMENT '用户年龄'
);

-- 订单表（orders）
CREATE TABLE `orders`(
	`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单id(主键)',
	`price` DOUBLE COMMENT '订单价格',
	`user_id` INT COMMENT '用户id(外键)'
);

-- 给已经存在的表添加外键，语法如下
-- alter table 表名 add constraint [外键名字] foreign key (外键字段) references 父表(主键字段);
ALTER TABLE orders ADD CONSTRAINT user_fk FOREIGN KEY (user_id) REFERENCES `user` (id);

-- 向user表中添加数据
INSERT INTO USER VALUES(1,'第一',11);
INSERT INTO USER VALUES(2,'小二',12);
INSERT INTO USER VALUES(3,'张三',33);
INSERT INTO USER VALUES(4,'李四',24);
INSERT INTO USER VALUES(5,'王五',17);
INSERT INTO USER VALUES(6,'赵六',36);
INSERT INTO USER VALUES(7,'七七',18);
INSERT INTO USER VALUES(8,'粑粑',NULL);

-- 向orders 表中插入数据
INSERT INTO orders VALUES(111,1314,3);
INSERT INTO orders VALUES(112,122,3);
INSERT INTO orders VALUES(113,15,4);
INSERT INTO orders VALUES(114,315,5);
INSERT INTO orders VALUES(115,1014,NULL);
INSERT INTO orders VALUES(116,666,6);
INSERT INTO orders VALUES(117,1111,1);
INSERT INTO orders VALUES(118,8888,NULL);

笛卡尔积

  • 笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。（笛卡尔积）百度百科
  • 什么笛卡尔积，如下所示
SELECT * FROM `user`,`orders`;

  • 像如上图查出来的数据，对我们程序员是没啥用的。
  • 哪如何消除笛卡尔积呢？需要主外键的约束，去重复数据。
SELECT * FROM `user` AS u,`orders` AS o WHERE u.`id`=o.`user_id`;

1.内连接

1.1隐式内连接

  • from 后面直接出现多表表名，这个属于隐式内连接
  • select * from 表a，表b where a.id = b.a_id;
SELECT * FROM `user` AS u,`orders` AS o WHERE u.`id`=o.`user_id`;

1.2显示内连接（推荐使用）

  • 使用 inner join 来链接表，后面 on 跟条件。（inner 可以省略）
  • select * from 表a inner join 表b on a.id = b.a_id;
  • 查询成年用户和订单数据；
SELECT * FROM `user` u JOIN `orders` o ON u.`id`=o.`user_id` WHERE age >= 18;

2.外连接

  • 外链接可以显示单表的全部数据，包括null；

2.1右外链接

  • 显示右边表的全部数据
  • 使用 right outer join 来链接表，后面 on 跟条件。（outer 可以省略）
  • select * from 表a right outer join 表b on a.id=b.a_id;
SELECT * FROM `user` u RIGHT JOIN `orders` o ON u.`id`=o.`user_id`;

左边表数据（user）

右边表数据（orders）

2.2左外链接（推荐使用）

  • 显示左边表的全部数据
  • 使用 left outer join 来链接表，后面 on 跟条件。（outer 可以省略）
  • select * from 表a left outer join 表b on a.id=b.a_id;
SELECT * FROM `user` u LEFT JOIN `orders` o ON u.`id`=o.`user_id`;

左边表数据（user）

右边表数据（orders）

3.子查询

  • 子查询，嵌套的感觉。查询出来的结果给另外一个查询当条件使用。
  • 查询年龄最大的用户的订单数据
SELECT * FROM orders o WHERE o.`user_id` IN (
	SELECT u.`id` FROM `user` u WHERE u.`age` IN(
		SELECT MAX(u.`age`) FROM `user` u
	)
);

4.全连接（MySQL不支持）

  • 全连接，左右两张表的全部数据包括null。相当于右外链接和左外链接的结合。
  • select * from 表a full outer join 表b on a.id=b.a_id;（MySQL不支持，不做演示）

MySQL其它文章，请看下面链接

MySQL DDL 语句

MySQL CRUD 语句

MySQL 聚合函数

MySQL 多表查询

END…

到此这篇关于MySQL数据库高级查询和多表查询的文章就介绍到这了,更多相关MySQL高级查询和多表查询内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL多表查询详解上

    时光在不经意间,总是过得出奇的快.小暑已过,进入中暑,太阳更加热烈的绽放着ta的光芒,...在外面被太阳照顾的人们啊,你们都是勤劳与可爱的人啊.在房子里已各种姿势看我这篇这章的你,既然点了进来,那就由我继续带你回顾MySql的知识吧! 回顾练习资料girls库以及两张表的脚本: 链接: https://pan.baidu.com/s/1bgFrP7dBBwk3Ao755pU4Qg 提取码: ihg7 引题:笛卡尔现象,先来观看一下两张表. SELECT * FROM boys; SELECT *

  • MySQL多表查询详解下

    好好吃饭,好好休息,听着很简单,实际落实缺失不那么容易. 继续回顾MySql的多表查询之1999语法 #二,SQL1999语法 语法: SELECT 查询列表 FROM 表1 别名 [连接类型] JOIN 表2 别名 ON 链接条件 [WHERE 筛选条件] [GROUP BY 分组] [HAVING 筛选条件] [ORDER BY 排序列表ASC|DESC] 分类(连接类型): 内连接(★): INNER 外联结 左外(★):LEFT [OUTER] 右外(★):RIGHT [OUTER] 全

  • 浅谈MySQL使用笛卡尔积原理进行多表查询

    MySQL的多表查询(笛卡尔积原理) 先确定数据要用到哪些表. 将多个表先通过笛卡尔积变成一个表. 然后去除不符合逻辑的数据(根据两个表的关系去掉). 最后当做是一个虚拟表一样来加上条件即可. 注意:列名最好使用表别名来区别. 笛卡尔积 Demo: 左,右连接,内,外连接 l 内连接: 要点:返回的是所有匹配的记录. select * from a,b where a.x = b.x ////内连接 l 外连接有左连接和右连接两种. 要点:返回的是所有匹配的记录 外加 每行主表外键值为null的

  • MySQL左联多表查询where条件写法示例

    MySQL左联多表查询where条件写法示例

    复制代码 代码如下: select * from _test a left join _test b on a.id=b.id where a.level='20' and a.month='04' and b.level='20' and b.month='03'; select a.*,b.* from (select * from _test where level='20' and month='04') as a left join (select * from _test where

  • 解析Mysql多表查询的实现

    查询是数据库的核心,下面就为您介绍Mysql多表查询时如何实现的,如果您在Mysql多表查询方面遇到过问题,不妨一看.Mysql多表查询: 复制代码 代码如下: CREATE TABLE IF NOT EXISTS contact( contact_id int(11) NOT NULL AUTO_INCREMENT, user_name varchar(255), nom varchar(255), prenom varchar(255), mail varchar(64), passcode

  • MySQL高级查询之与Group By集合使用介绍

    1 GROUP_CONCAT mysql> SELECT student_name,    ->     GROUP_CONCAT(test_score)    ->     FROM student    ->     GROUP BY student_name; Or: mysql> SELECT student_name,    ->     GROUP_CONCAT(DISTINCT test_score    ->               ORDER

  • MySQL多表查询实例详解【链接查询、子查询等】

    本文实例讲述了MySQL多表查询.分享给大家供大家参考,具体如下: 准备工作:准备两张表,部门表(department).员工表(employee) create table department( id int, name varchar(20) ); create table employee( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'ma

  • 详解MySQL数据库--多表查询--内连接，外连接，子查询，相关子查询

    多表查询 使用单个select 语句从多个表格中取出相关的查询结果,多表连接通常是建立在有相互关系的父子表上; 1交叉连接 第一个表格的所有行 乘以 第二个表格中的所有行,也就是笛卡尔积 创建一个消费者与顾客的表格: 代码如下: -- create table customers( -- id int primary key auto_increment, -- name VARCHAR(20)not null, -- address VARCHAR(20)not NULL -- ); -- C

  • MySQL 多表查询实现分析

    我们继续使用前面的例子.前面建立的表中包含了员工的一些基本信息,如姓名.性别.出生日期.出生地.我们再创建一个表,该表用于描述员工所发表的文章,内容包括作者姓名.文章标题.发表日期. 1.查看第一个表 mytable 的内容: mysql> select * from mytable; +----------+------+------------+-----------+ | name | sex | birth | birthaddr | +----------+------+-------

  • MySQL数据库设计概念及多表查询和事物操作

    MySQL数据库设计概念及多表查询和事物操作

    目录 数据库设计概念 数据库设计简介 表关系(多对多) 表关系(一对多) 表关系之一对一 多表查询 笛卡尔积现象 内连接查询 嵌套查询(子查询) 事务操作 事务的概念 手动提交事务 自动提交事务 事务原理和四大特征 事务原理 事务的四大特征 事务的并发访问引发的三个问题(面试) 事务的隔离级别 数据库设计概念 数据库设计简介 1.数据库设计概念 数据库设计就是根据业务系统具体需求,结合我们所选用的DBMS,为这个业务系统构造出最优的数据存储模型. 建立数据库中的表结构以及表与表之间的关联关系的过

  • MySQL数据库高级查询和多表查询

    MySQL多表查询 添加练习表 -- 用户表(user) CREATE TABLE `user`( `id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户id(主键)', `username` VARCHAR(50) COMMENT '用户姓名', `age` CHAR(3) COMMENT '用户年龄' ); -- 订单表(orders) CREATE TABLE `orders`( `id` INT AUTO_INCREMENT PRIMARY KEY

  • MySQL数据库查询之多表查询总结

    MySQL数据库查询之多表查询总结

    目录 多表关系 表与表之间的联系: 一对多(多对一) 多对多 一对一 多表查询 多表查询的分类 1.连接查询: 2.子查询 内连接 外连接 自连接 联合查询 子查询 标量子查询 列子查询 行子查询 表子查询 多表查询案例 总结 多表关系 在进行数据库表结构的设计时,会根据业务的需求和业务模块之间的关系,分析设计表结构,由于业务之间相互关联,所以各个表结构之间也存在各种联系 表与表之间的联系: 1.一对多(多对一) 2.多对多 3.一对一 一对多(多对一) 例如,一个员工对应一个部门,一个部门可以

  • mysql中各种常见join连表查询实例总结

    mysql中各种常见join连表查询实例总结

    本文实例讲述了mysql中各种常见join连表查询.分享给大家供大家参考,具体如下: 通常我们需要连接多个表查询数据,以获取想要的结果. 一.连接可以分为三类: (1) 内连接:join,inner join (2) 外连接:left join,left outer join,right join,right outer join,union,union all (3) 交叉连接:cross join 二.准备需要演示的表: CREATE TABLE `a` ( `id` int(11) uns

  • MySQL数据库学习之去重与连接查询详解

    目录 1.去重 2.连接查询 使用where进行多表连接查询 内连接 - 等值连接 内连接 - 非等值连接 内连接 - 自连接 外连接 - 左右外连接 三表连接 1.去重 示例表内容参考此文章 有些 MySQL 数据表中可能存在重复的记录,有些情况我们允许重复数据的存在,但有时候我们也需要删除这些重复的数据. 例如:去重显示岗位信息: mysql> select distinct job from emp; +-----------+ | job | +-----------+ | CLERK

  • MySQL数据库之union,limit和子查询详解

    目录 1.where中的子查询 2.from子句后的子查询 3.union 4.limit查询 5.分页 1.where中的子查询 示例数据参见此文章 案例:查询比最低工资高的员工姓名和薪资 子查询,先查询子查询括号里的,再向上级进行查询 mysql> select ename,sal from emp where sal -> > -> (select min(sal) from emp); +--------+---------+ | ename | sal | +------

  • MySQL使用聚合函数进行单表查询

    聚合函数 作用于一组数据,对那组数据返回一个值 count :统计结果记录多少条数, max:统计最大值 min:统计最小值 sum:计算求和 avg: 计算平均值 注意,分组函数 group by.如果要对 分组后 的数据进行筛选,那么必须使用 having 关键字,条件写在 having 后面: where:先过滤已有的数据,在分组,在聚合函数计算: having:过滤分组之后的数据.插入我们需要练习的表city 表 CREATE TABLE `city` ( `id` INT AUTO_I

  • Mysql数据库高级用法之视图、事务、索引、自连接、用户管理实例分析

    Mysql数据库高级用法之视图、事务、索引、自连接、用户管理实例分析

    本文实例讲述了Mysql数据库高级用法之视图.事务.索引.自连接.用户管理.分享给大家供大家参考,具体如下: 视图 视图是对若干张基本表的引用,一张虚表,只查询语句执行结果的字段类型和约束,不存储具体的数据(基本表数据发生了改变,视图也会跟着改变),方便操作,特别是查询操作,减少复杂的SQL语句,增强可读性. 1.----创建视图: create view 视图名称(一般以v_开头) as 查询语句; 2.----查看视图: select * from 视图名称; 3.----删除视图: dro

  • MySQL数据库高级数据操作之新增数据

    目录 多数据插入 主键冲突 1.主键冲突更新 2.主键冲突替换 蠕虫复制 多数据插入 只要写一次insert,可以插入多条数据 基本语法: insert into 表名 [(字段列表)] values (值列表), (值列表)...; create table my_student( id int primary key auto_increment, name varchar(10) ); insert into my_student (name) values ('张三'), ('李四'),

随机推荐