- 级联删除
- 级联更新
- 多对多关系,引入中间表,添加外键约束
- 内连接
- 外连接
- 子查询
- 复制表
- 存储过程
- 变量的介绍
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
说明:mysql 这句话什么意思,SELECT @i:= 0
它的意思是为变量@i赋值(如:set @i=0;)。
在mysql中用户变量赋值有两种方式,一种是=另一种是:=其中区别在于使用set赋值时两种方式都可以使用,使用select赋值时只能使用:=。