MySQL数据库（多表查询，存储过程，变量）

最新推荐文章于 2024-04-23 09:14:20 发布

可儿可爱

最新推荐文章于 2024-04-23 09:14:20 发布

阅读量1.5k

点赞数 1

文章标签：数据库 mysql database

本文链接：https://blog.csdn.net/weixin_62520626/article/details/123029110

版权

级联删除
级联更新
多对多关系，引入中间表，添加外键约束
内连接
外连接
子查询
复制表
存储过程
变量的介绍

1.级联删除

删除用户时，把你关联的数据一并删掉。

  CREATE TABLE zhu(
    zid INT PRIMARY KEY AUTO_INCREMENT,  -- 主键
    zname VARCHAR(20)

);

CREATE TABLE cong(
    zid INT PRIMARY KEY AUTO_INCREMENT,
    zscore INT,
    zzid INT -- 外键
 -- 方式2：建表的时候就加上了外键约束
  --  FOREIGN KEY(zzid) REFERENCES zhu(zid) ON DELETE CASCADE 
);





--  删除用户时，把你关联的数据一并删掉。


   -- ON DELETE CASCADE 级联删除 
    
    ALTER TABLE cong ADD FOREIGN KEY(zzid) REFERENCES zhu(zid) ON DELETE CASCADE;

2.级联更新

-- ON UPDATE CASCADE 级联更新

 -- ON DELETE CASCADE 级联删除 
    
    ALTER TABLE cong ADD FOREIGN KEY(zzid) REFERENCES zhu(zid) ON DELETE CASCADE;
    
    -- ON UPDATE CASCADE 级联更新
    
    ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES users(id) ON UPDATE CASCADE;
    
    -- 也可以同时加上
    
     ALTER TABLE cong ADD FOREIGN KEY(zzid) REFERENCES zhu(zid) ON DELETE CASCADE ON UPDATE CASCADE;
    
    -- 级联更新
    
    
         
           
           
   CREATE TABLE zhu(
    zid INT PRIMARY KEY AUTO_INCREMENT,  -- 主键
    zname VARCHAR(20)

);

CREATE TABLE cong(
    zid INT PRIMARY KEY AUTO_INCREMENT,
    zscore INT,
    zzid INT, -- 外键
 -- 方式2：建表的时候就加上了外键约束
   FOREIGN KEY(zzid) REFERENCES zhu(zid) ON DELETE CASCADE ON UPDATE CASCADE
);
  
  
  -- 级联删除时，要注意下

3.多对多关系（引入中间表，添加外键约束）

引入一张中间表,存放两张表的主键,一般会将这两个字段设置为联合主键,这样就可以将多对多的关系拆分成两个一对多了
为了保证数据的有效性和完整性
需要在中间表上添加两个外键约束即可.

-- 创建用户表
	create  table user(
		id int primary key auto_increment,
		username varchar(20)
	);
	
	-- 创建订单表
	create  table orders(
		id int primary key auto_increment,
		totalprice double,
		user_id int   
	);
alter table orders add foreign key(user_id) references user(id);

	-- 创建商品表
	create table product(
		id int primary key auto_increment,
		name varchar(20),
		price double
	);

	-- 创建中间表
	create table orderitem(
		oid int,
		pid int
	);
		
	-- 添加外键约束 
	alter table orderitem add foreign key(oid) references orders(id);
	alter table orderitem add foreign key(pid) references product(id);

4.多表查询

内连接
外连接
子查询

笛卡尔积:
多张表无条件的联合查询.没有任何意思
select a.*,b.* from a,b;

5.内连接

-- 内连接：特点，不符合条件的数据，不会展示出来，比如下面的例子，赵六，就没有展示出来，因为赵六没有下过订单，所以赵六不展示。

-- 内连接有两种写法：
-- 1. 显示内连接
-- 格式1:显式的内连接
-- select a.*,b.* from a [inner] join b on ab的连接条件 inner 可以省略不写

SELECT user.*,orders.* FROM USER INNER JOIN orders ON user.`id`=orders.`user_id`

-- 2. 隐式内连接

-- 格式2:隐式的内连接
-- SELECT a.*,b.* FROM a,b WHERE a b的连接条件
-- 例子:
SELECT user.*,orders.* FROM USER,orders WHERE user.`id`=orders.`user_id`;

-- 查询所有用户的订单信息。
-- N张表查询，至少要有N-1个关联条件  比如：两张表关联查询，至少要有一个关联条件。

SELECT user.*,orders.* FROM USER,orders WHERE user.`id`=orders.`user_id`;

-- 查出个别字段

SELECT user.id,user.`username`,orders.price FROM USER,orders WHERE user.`id`=orders.`user_id`;

-- 你可以可以给字段起别名

SELECT user.id,user.`username` AS 用户名,orders.price FROM USER,orders WHERE user.`id`=orders.`user_id`;

-- 有时表名比较长，你可以给表起别名

SELECT u.id,u.`username` AS 用户名,o.price FROM USER AS u,orders AS o WHERE u.`id`=o.`user_id`;

-- -- 查询所有用户的订单信息，并且订单总价 大于 300的

SELECT user.*,orders.* FROM USER,orders WHERE user.`id`=orders.`user_id` AND orders.`price`>300;

SELECT user.*,orders.* FROM USER INNER JOIN orders ON user.`id`=orders.`user_id` AND orders.`price`>1000;

6.外连接

外连接:★

   左外连接:★
       select a.*,b.* from a left [outer] join b on 连接条件; outer 可以不写
       意思:先展示join左边的(a)表的所有数据,根据条件关联查询 join右边的表(b),符合条件则展示出来,不符合以null值展示.

   右外连接:
       select a.*,b.* from b right [outer] join a on 连接条件; outer 可以不写
       意思:先展示jion右边的表(a)表的所有数据,根据条件关联查询join左边的表(b),符合条件则展示出来,不符合以null值展示.

-- 外连接：左外连接，右外连接

-- 查询所有用户的订单信息，要求所有用户信息展示出来，该用户如果没有订单信息，以null展示 那这里就可以使用外连接来做。

-- 以 left 单词分左右两边，左边的表中的信息，会全部展示出来，在右边表中没有 对应的以null展示。

SELECT user.*,orders.* FROM USER LEFT OUTER JOIN orders ON user.`id`=orders.`user_id`;



-- 查询所有订单所对应的用户信息，订单信息要全部展示出来，该订单没有对应的用户，以null展示
-- 以 RIGHT 这个单词分左右，右边的表中的数据会全部展示出来，在左边表中没有对应的数据，以null展示

SELECT orders.*,user.* FROM USER RIGHT OUTER JOIN orders ON user.`id`=orders.`user_id`;


--  左外连接，右外连接 可以互换


-- 查询所有订单所对应的用户信息，订单信息要全部展示出来，该订单没有对应的用户，以null展示
-- 使用左外连接做

SELECT orders.*,user.* FROM orders LEFT OUTER JOIN USER ON user.`id`=orders.`user_id`;





-- 查询所有用户的订单信息，要求所有用户信息展示出来，该用户如果没有订单信息，以null展示 那这里就可以使用外连接来做。


SELECT user.*,orders.* FROM  orders  RIGHT OUTER JOIN USER ON user.`id`=orders.`user_id`;




-- 表可以起别名，字段可以起别名 OUTER 可以省略不写
SELECT u.id,u.`username` AS 用户名,o.* FROM  orders AS o  RIGHT OUTER JOIN USER AS u ON u.`id`=o.`user_id` AND u.`username`='张三';


SELECT u.id,u.`username` AS 用户名,o.* FROM  orders AS o  RIGHT OUTER JOIN USER AS u ON u.`id`=o.`user_id` WHERE u.`username`='张三';

7.子查询

一个查询依赖另一个查询.

一个主查询的条件的值，来自于另外一条子查询的结果。

子查询注意的问题：
1. 要有括号
2.合理的书写风格
3.可以在主查询的where ,select, having ,from后面都可以使用子查询
select 语句后面使用子查询,只能使用单行子查询,即只允许返回一条记录
4.不可以在group by后面使用子查询
5.强调from后面的子查询
6.主查询和子查询可以不是同一张表；只要子查询返回的结果主查询可以使用即可
7.一般不在子查询中排序；但在top-n分析问题中，必须对子查询排序
8.一般先执行子查询再执行主查询；但相关子查询例外
9.单行子查询只能使用单行操作符；多行子查询只能使用多行操作
主查询可以有多个子查询,即1:n关系,子查询可以嵌套用,最多855层
10.子查询中的null ,为什么集合中若有空值,不能用not in(10,20,null) 可以用in( );

-- 一. 查看用户为张三的订单详情

-- 1. 先在用户表中查出张三的id
       SELECT user.id FROM USER WHERE user.`username`='张三';
       
-- 2. 拿着张三的id去订单表中查

       SELECT orders.* FROM orders WHERE orders.user_id=3;
       
 -- 把上面两步合二为一，就是子查询
       
-- 注意：子查询语句，使用小括号，括起来

    SELECT orders.* FROM orders WHERE orders.user_id=(SELECT user.id FROM USER WHERE user.`username`='张三');



-- 查询出订单的价格大于300的所有用户信息。

-- 1. 先查询出订单价格>300的用户的id
     
     SELECT user_id FROM orders WHERE price >300;
     
 -- 2. 查出用户的信息
 SELECT user.* FROM USER WHERE id=3 OR id=5;    
 
 -- 把上面两步合二为一
 
  SELECT user.* FROM USER WHERE id IN(SELECT user_id FROM orders WHERE price >300);   
  
  
 -- 三. 查询订单价格大于300的订单信息及相关用户的信息。
 
 
 -- 子查询
 
 
SELECT user.* FROM USER WHERE id IN(SELECT user_id FROM orders WHERE price >300);  


SELECT lsb.*,orders.* FROM orders,(SELECT user.* FROM USER WHERE id IN(SELECT user_id FROM orders WHERE price >300)) AS lsb WHERE orders.`user_id`=lsb.id;





    SELECT orders.* FROM orders WHERE orders.user_id=(SELECT user.id FROM USER WHERE user.`username`='张三');
    
    SELECT user.*,lsb.* FROM USER,(SELECT orders.* FROM orders WHERE orders.user_id=(SELECT user.id FROM USER WHERE user.`username`='张三')) AS lsb WHERE user.id=lsb.user_id;






 -- 三. 查询订单价格大于300的订单信息及相关用户的信息。
 
 SELECT user.*,orders.* FROM USER,orders WHERE user.`id`=orders.`user_id` AND orders.`price`>300;

8.自查询（把一张表看做两张，找到关联条件）

通过表的别名,给一张表起两个别名,将他视为两张表,来进行查询

比如:我要查询emp表中员工姓名所对应的老板姓名

因为这些信息都在一张表 emp中
比如员工号7369 的 SMITH 他对应的老板编号是(MGR) 7902 而7902 又是员工FORD(7902) 那FORD 对应的老板编号又是 7566
所以说一个员工既是某几个员工的老板,他也有自己的老板
所以我要查询这个员工的所对应的老板就可以使用自连接查询

我们假设有两张表一张员工表,一张老板表,如果员工的老板号=老板的员工号就表示这个员工是另外一个员工的老板
select e.ename as 员工姓名,b.ename as 老板姓名 from emp e,emp b where e.mgr=b.empno;

9.四张表查询（多种查询方式）


-- 查询 所有用户的所有订单信息，以及所有订单里面包含的所有商品信息


-- 内连接：
-- 两张表
SELECT user.*,orders.* FROM USER,orders WHERE user.`id`=orders.`user_id`;

-- 把中间表加进来
SELECT user.*,orders.*,orderitem.* FROM USER,orders,orderitem WHERE user.`id`=orders.`user_id` AND orderitem.`oid`=orders.`id`;

-- 把商品表加进来
SELECT user.*,orders.*,orderitem.*,product.* FROM USER,orders,orderitem,product WHERE user.`id`=orders.`user_id` AND orderitem.`oid`=orders.`id` AND orderitem.`pid`=product.`id`;


-- 中间表不做展示

SELECT user.*,orders.*,product.* FROM USER,orders,orderitem,product WHERE user.`id`=orders.`user_id` AND orderitem.`oid`=orders.`id` AND orderitem.`pid`=product.`id`;

-- 查询 所有张三用户的所有订单信息，以及所有订单里面包含的所有商品信息
 

SELECT 
  user.*,
  orders.*,
  product.* 
FROM
  USER,
  orders,
  orderitem,
  product 
WHERE user.`id` = orders.`user_id` 
  AND orderitem.`oid` = orders.`id` 
  AND orderitem.`pid` = product.`id`
  AND user.`username`='张三' ;

 
 
 -- 显示内连接
 
 
 SELECT user.*,orders.* FROM USER INNER JOIN orders ON user.`id`=orders.`user_id`;
 
 -- 加入中间表
 
 
SELECT 
  user.*,
  orders.*,
  orderitem.*
  
FROM
  USER 
  INNER JOIN orders 
    ON user.`id` = orders.`user_id` 
  INNER JOIN orderitem 
    ON orderitem.`oid` = orders.`id` ;
    
    
 -- 加入商品表
 
    SELECT 
  user.*,
  orders.*,
  product.*
FROM
  USER 
  INNER JOIN orders 
    ON user.`id` = orders.`user_id` 
  INNER JOIN orderitem 
    ON orderitem.`oid` = orders.`id` 
   INNER JOIN product 
    ON orderitem.`pid` = product.`id` AND user.`username`='李四'
      
    ;



    SELECT 
  user.*,
  orders.*,
  product.*
FROM
  USER 
  INNER JOIN orders 
    ON user.`id` = orders.`user_id` 
  INNER JOIN orderitem 
    ON orderitem.`oid` = orders.`id` 
   INNER JOIN product 
    ON orderitem.`pid` = product.`id`
    WHERE user.`username`='李四'
    
    
        SELECT 
  user.*,
  orders.*,
  product.*
FROM
  USER 
  INNER JOIN orders 
    ON user.`id` = orders.`user_id` 
  INNER JOIN orderitem 
    ON orderitem.`oid` = orders.`id` 
   INNER JOIN product 
    ON orderitem.`pid` = product.`id`
    WHERE user.`username`='李四'




-- 外连接

    SELECT 
  user.*,
  orders.*,
  product.*
FROM
  USER 
  LEFT JOIN orders 
    ON user.`id` = orders.`user_id` 
  LEFT JOIN orderitem 
    ON orderitem.`oid` = orders.`id` 
   LEFT JOIN product 
    ON orderitem.`pid` = product.`id`
    
    
    -- 外连接

    SELECT 
  user.*,
  orders.*,
  product.*
FROM
  USER 
  LEFT JOIN orders 
    ON user.`id` = orders.`user_id` 
  LEFT JOIN orderitem 
    ON orderitem.`oid` = orders.`id` 
   LEFT JOIN product 
    ON orderitem.`pid` = product.`id` AND user.`username`='张三'
  
  -- 注意下面的写法

    SELECT 
  user.*,
  orders.*,
  product.*
FROM
  USER 
  LEFT JOIN orders 
    ON user.`id` = orders.`user_id` 
  LEFT JOIN orderitem 
    ON orderitem.`oid` = orders.`id` 
   LEFT JOIN product 
    ON orderitem.`pid` = product.`id`
  WHERE user.`username`='张三';

10.复制表

创建一张表,表的字段和数据来一条查询语句

语法：create table 表名 as select * from 另一张表 where 1=1; -- 创建一张表会把另一张表中的字段和对应的数据全部复制过去
如果我们只想要字段，不要数据
create table 表名 as select * from 另一张表 where 1=2;
如果我只想还要个别字段
create table 表名 as select 表名.字段名，表名.字段名2 from 另一张表 where 1=2;

我创建的这张表也可以来自一个子查循
create table 表名 as 子查询

11.存储过程procedure

概念：

存储过程是数据库中的一个对象，存储在服务端，用来封装多条SQL语句且带有逻辑性，可以实现一个功能，由于他在创建时，就已经对SQL进行了编译，所以执行效率高，而且可以重复调用，类似与我们Java中的方法

语法：

DELIMITER $$
CREATE
    PROCEDURE `performance_schema`.`myTestPro`()
  
    BEGIN
  
    END$$

DELIMITER ;

注意：创建存储过程需要管理员分配权限 

补充：delimiter是mysql定义结束标记的，在mysql客户端中结束标记默认是分号（；）。
如果一次输入的语句较多，并且语句中间有分号，这时需要新指定一个特殊的结束符。
delimiter $$ 表示mysql用$$表示mysql语句结束，过程结束后肯定会有一句delimiter ；
表示恢复成默认的。

参数：

in:输入参数
out：输出参数
inout：输入输出参数


例如：
DELIMITER $$

CREATE
    PROCEDURE `performance_schema`.`myTestPro`(IN num INT,OUT r INT)
    BEGIN
	DELETE FROM emp WHERE empno=num;
	SELECT COUNT(*) FROM emp INTO r;
    END$$

DELIMITER ;

调用存储过程 call：

语法：call myTestPro(9527,@rr)
查询结果： select @rr

删除存储过程 drop：

语法： drop procedure myTestPro;

查看存储过程 show：

show procedure status\G; -- 查看所有的存储过程状态
show create procedure 存储过程名字\G; -- 查看创建存储过程的语句

带有IF逻辑的存储过程 if then elseif else：

DELIMITER $$
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
	IF num=1 THEN
		SET str='星期一';
	ELSEIF num=2 THEN              --注意elseif  连在一块
		SET str='星期二';
	ELSEIF num=3 THEN
		SET str='星期三';             -- 注意要用分号结束
	ELSE
		SET str='输入错误';
	END IF;                        -- 注意要结束if  后面有分号
END $$

带有循环的存储过程 while do：

DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
	-- 定义一个局部变量
	DECLARE i INT DEFAULT 1;
	DECLARE vsum INT DEFAULT 0;
	WHILE i<=num DO
	      SET vsum = vsum+i;
	      SET i=i+1;
	END WHILE;  -- 要记得结束循环
	SET result=vsum;
END $

其他循环的写法（了解）：


例如：  REPEAT 循环 
DELIMITER $
 CREATE PROCEDURE proRepeat(OUT outnum INT)
     BEGIN
     DECLARE i INT DEFAULT 0;
     REPEAT
         SET i=i+1;
         UNTIL i>=5  -- 注意这里不要加分号,不然语法报错 UNTIL 结束条件
     END REPEAT; -- 记着结束
     SET outnum=i;
  END $
  
  
  
  CALL proRepeat(@num);
  SELECT @num;
  
  
 例如：loop 循环 
 
 DELIMITER $$

CREATE
    PROCEDURE `mydemo3`.`proLoop`(OUT outnum INT)
   
    BEGIN
	DECLARE i INT DEFAULT 0;
      myloop:LOOP    -- 这里的myloop 是我给循环起的一个标号或者说名字,这是语法要求,不起就会报错
         SET i=i+1;
        IF i>=5 THEN
            LEAVE myloop;  -- 根据我的循环标号中断循环 leave 是中断循环的意思
         END IF;
    END LOOP;
     SET outnum=i;
    END$$

DELIMITER ;
  
  CALL proLoop(@num);
  SELECT @num;

控制循环的两个关键字：

leave 相当于java中的 break

iterate相当于java中的continue

12.变量

全局变量（内置变量）：可以在多个会话中去访问他
 -- 查看所有全局变量： 
         show variables
        -- 查看某个全局变量： 
          select @@变量名
        -- 修改全局变量： 
          set 变量名=新值
        -- character_set_client: mysql服务器的接收数据的编码
        -- character_set_results：mysql服务器输出数据的编码
        SET @@character_set_client=gbk   –设置数据库编码
        set @@character_set_results=gbk
        
        SET @@character_set_client=utf8;   –设置数据库编码
        set @@character_set_results=utf8;
         SELECT @@character_set_client  --查看数据库编码
-- 会话变量： 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！
        -- 定义会话变量: 
        set @变量=值
        -- 查看会话变量：
        select @变量
        
     
        
-- 局部变量： 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失！！
		定义局部变量的语法：
		DECLARE i INT DEFAULT 1;  
		给变量设置值 set i=10;

定义一个变量，作为一个临时展示的字段

SELECT (@i:=@i+1) AS id,ename,job,sal FROM emp,(SELECT @i:=0) AS init;

说明：mysql 这句话什么意思，SELECT @i:= 0
它的意思是为变量@i赋值（如：set @i=0;）。
在mysql中用户变量赋值有两种方式，一种是=另一种是:=

其中区别在于使用set赋值时两种方式都可以使用，使用select赋值时只能使用:=。

可儿可爱

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库（多表查询，存储过程，变量）

级联删除级联更新内连接外连接子查询复制表存储过程变量的介绍1.级联删除删除用户时，把你关联的数据一并删掉。 CREATE TABLE zhu( zid INT PRIMARY KEY AUTO_INCREMENT, -- 主键 zname VARCHAR(20));CREATE TABLE cong( zid INT PRIMARY KEY AUTO_INCREMENT, zscore INT, zzid INT
复制链接

扫一扫