MySQL进阶多表操作二

MySQL进阶

一、多表操作

-- 多对多
-- 要创建一张外键连接表
-- 一本数可以有多个作者 ， 一个作者也可以拥有多本书

create table book(
	id int primary key auto_increment,
	b_name char(20),
	price float(5,2)
);

create table author(
	id int primary key auto_increment,
	a_name char(10),
	age int(3)
);

insert into book(b_name , price) VALUES
('钢铁是怎样炼成的' , 99.9),
('MySQL删除到跑路' , 69.99),
('骆驼祥子' , 59.99),
('飞鸟集' , 79.99),
('哈利波特' , 179.99),
('python从入门到精通' , 59.99),
('三体' , 259.99);

insert into author(a_name , age) values
('泰戈尔',89),
('奥斯特洛夫斯基',89),
('老舍',89),
('jk罗琳',89),
('阿宸',89),
('刘慈欣',89),
('小川',89);

update author set age=24 where a_name='阿宸';
update author set age=25 where a_name='小川';

-- 建立一个外键连接表
create table t1(
	id int primary key auto_increment,
	book_id int,
	author_id int,
	-- 设置author表跟book连接的外键
	foreign key (book_id) references book(id)
	on delete cascade
	on update cascade,
	-- 设置book表跟author连接的外键
	foreign key (author_id) references author(id)
	on delete cascade
	on update cascade
);

insert into t1(book_id , author_id) values 
(1,2),
(2,5),
(2,7),
(7,6),
(3,3),
(4,1),
(5,4);

二、多表查询

select * from book , author;
select count(*) from book , author;
select * from book , author where a_name='阿宸';
select * from book , author where a_name='小川';

这样查询会出现笛卡尔积反应，也就是将两个表格中的每一条数据进行强行拼接在一起，返回数据的条数就是两个表数据的条数的乘积

inner join  -- 内连接
left join 	-- 左连接
right join	-- 右连接

-- 内连接 , 将两表关联的数据拼接在一起
select * from 表1 inner join 表2 on 连接条件
select * from book inner join author 
on book.b_id=author.a_id;

select book.b_name,author.a_name from book 
inner join author on book.b_id=author.a_id;

-- 左连接 ， 以左表为主 ，左表有数据没有对应到 ， 会出现为NULL
select * from (左)表1 left join 表2 on 连接条件

select * from  author left join book 
on book.b_id=author.a_id;

-- 右连接 ， 以右表为主， 右表有数据没有对应到 ， 会出现为NULL
select * from (左)表1 right join 表(右)2 on 连接条件

select * from  author right join book 
on book.b_id=author.a_id;

三、函数和存储过程

函数以及存储过程是事先经过编译并存储在数据库中的一段sql语句的集合，调用存储过程或者函数可以简化代码，提高数据处理效率。

函数有且只有一个返回值，并且要的是一个结果；

存储过程不能右返回值要的是执行过程；

1、函数

在my.ini中的[mysqld]后面增加一条
lower_case_table_names=1
log_bin_trust_function_creators=1

-- 函数
delimiter 符号 -- 自定义语句结束符号
create function 函数名() returns 返回类型
begin
	函数体
	return 返回值
end 符号
delimiter ;
select 函数名();


-- 在函数内创建变量
declare 变量名 ， 数据类型

2、存储过程

delimiter 符号 -- 自定义语句结束符号
create procedure 存储过程名称()
begin
	存储过程
end 符号
delimiter ;
call 存储过程名称();


delimiter $$
create procedure ss()
begin
	select * from author where age<30;
end $$
delimiter ;

call ss();