JavaWeb06-MySQL深入学习这些就够了！

mysql-2

一.数据库备份与恢复

1. 备份

数据库的备份是指将数据库转换成对应的sql文件。

数据库导出sql脚本的格式：

l mysqldump -u用户名 -p密码 数据库名>生成的脚本文件路径

例如:

mysqldump -uroot -p1234 day0401>d:\day04.sql

以上备份数据库的命令中需要用户名和密码，即表明该命令要在用户没有登录的情况下使用

2. 恢复

数据库的恢复指的是使用备份产生的sql文件恢复数据库，即将sql文件中的sql语句执行就可以恢复数据库内容。因为数据库备份只是备份了数据库内容，所以备份产生的sql文件中没有创建数据库的sql语句，在恢复数据库之前需要自己动手创建数据库。

l在数据库外恢复

格式:mysql -uroot -p密码 数据库名 < 文件路径

例如:mysql -uroot -p1234 day0401<d:\day04.sql

注意:要求数据库必须先创建出来.

l在数据库内恢复

格式:source SQL脚本路径

例如:source d:\day0401.sql

注意:使用这种方式恢复数据，首先要登录数据库.

二.多表设计与实现(外键约束)

系统设计中，实体之间的关系有三种:一对一，一对多，多对多

也就是说,数据库开发中表与表之间的关系有三种,而表与表之间关系是通过外键来维护的。

外键约束特性如下：

外键必须是另一表主键的值；

外键可以重复；

外键可以为null；

一张表中可以有多个外键。

表之间关联关系

1. 一对多

用户表

CREATE TABLE USER(

id INT PRIMARY KEY AUTO_INCREMENT

username VARCHAR(50)

)

订单表

CREATE TABLE orders(

id INT PRIMARY KEY AUTO_INCREMENT,

price DOUBLE

)

上述是订单与用户表，一个用户是可以有多个订单的，它们是很明显的一个一对多(多对一)的关系，那么我们怎样在表中描述它们的对应关系?

我们一般在多的一方表中简称(多表),添加一个外键字段，与一方表中的主键字段对应就可以描述其一对多的关系。例如,在orders表中添加一个字段,将其设置为外键.如下图:

订单表

CREATE TABLE orders(

id INT PRIMARY KEY AUTO_INCREMENT,

price DOUBLE,

user_id INT -- 用户id

)

alter table orders add foreign key (user_id) references user(id);

2. 多对多

订单表

CREATE TABLE orders(

id INT PRIMARY KEY AUTO_INCREMENT,

price DOUBLE

)

商品表

CREATE TABLE products(

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(50)

)

订单与商品之间是一个很明显的多对多关系，那么我们怎样在表中描述它们的关系?开发中,一般新建一张中间表,这个中间表只放双方的id,然后就拆成了两个一对多的关系.然后一般将两个id设成联合主键,然后对每个id添加外键约束.如下图.

订单与商品关联表-中间表

CREATE TABLE order_product(

oid INT,

pid INT,

PRIMARY KEY(oid,pid)

)

alter table order_product add foreign key (oid) references orders(id);

alter table order_product add foreign key (pid) references products(id);

3. 一对一

person表

CREATE TABLE person(

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(20) -- 姓名

)

idcard表

CREATE TABLE idcard(

id INT PRIMARY KEY AUTO_INCREMENT,

num VARCHAR(20) -- 身份证号

)

上述是我们创建的person表与idcard表，它们之间是一对一关系，那么我们怎样来描述一个人只有一个身份证，而一个身份证只能属于一个人哪？

在开发中一般有两种思路

a.根据需求将一张表的主键设置外键，就可以描述其一对一的关系。例如,在idcard表设置外键.如下图:

idcard表

alter table idcard add foreign key (id) references person(id);

添加外键约束的格式为:

alter table 当前表 add [constraint] foreign key (需要设为外键的字段) references 指向表(指向表的主键字段)

b.两张表合二为一,例如,在person表中加入idcard的字段

person表

CREATE TABLE person(

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(20), -- 姓名

num VARCHAR(20) -- 身份证号

)

总结:

对于一对一关系，我们在表中描述时，可以在任意一方描述

对于一对多关系，我们在表中描述时，在多的一方描述

对于多对多关系，我们在表中描述时，会产生一个中间表

三.多表查询-内连接

数据准备:

-- 用户表(user)

create table `user` (

`id` int auto_increment primary key,

`username` varchar(50) -- 用户姓名

);

-- 订单表(orders)

create table `orders` (

`id` int auto_increment primary key,

`price` double,

`user_id` int

);

-- 给订单表添加外键约束

alter table orders add constraint user_fk foreign key (user_id) references user(id);

-- 向user表中添加数据

insert into user values(1,'张三');

insert into user values(2,'李四');

insert into user values(3,'王五');

insert into user values(4,'赵六');

-- 向orders 表中插入数据

insert into orders values(1,1314,1);

insert into orders values(2,1314,1);

insert into orders values(3,15,2);

insert into orders values(4,315,4);

insert into orders values(5,1014,null);

练习:

1.查询用户的订单,没有订单的用户不显示

2.查询所有用户的订单详情

3.查询所有订单的用户详情

内连接(inner join)是应用程序中用的普遍的"连接"操作，它一般都是默认连接类型。内连接基于连接谓词将两张表(如 A 和 B)的列组合在一起，产生新的结果表。查询会将 A 表的每一行和 B 表的每一行进行比较，并找出满足连接谓词的组合。当连接谓词被满足，A 和 B 中匹配的行会按列组合(并排组合)成结果集中的一行。连接产生的结果集，可以定义为首先对两张表做笛卡尔积(交叉连接) -- 将 A 中的每一行和 B 中的每一行组合，然后返回满足连接谓词的记录。实际上 SQL 产品会尽可能用其他方式去实现连接，笛卡尔积运算是非常没效率的.

SQL 定义了两种不同语法方式去表示"连接"。首先是"显式连接符号"，它显式地使用关键字 JOIN，其次是"隐式连接符号"，它使用所谓的"隐式连接符号"。隐式连接符号把需要连接的表放到 SELECT 语句的 FROM 部分，并用逗号隔开。这样就构成了一个"交叉连接"，WHERE 语句可能放置一些过滤谓词(过滤条件)。那些过滤谓词在功能上等价于显式连接符号.

常见的内连接有相等连接和交叉连接.

1. 交叉连接(cross join)

又称笛卡尔连接(cartesian join)或叉乘(Product)，它是所有类型的内连接的基础。把表视为行记录的集合，交叉连接即返回这两个集合的笛卡尔积。这其实等价于内连接的链接条件为"永真"，或连接条件不存在.笛卡尔积引用自数学，在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

2. 相等连接 inner join…on…

例如:

select * from user inner join orders on user.id =orders.user_id;

通过on后面的条件，将两张表中的数据过滤。这种写法可以将inner省略，因为join默认就是内连接(inner join),，省略inner后的语句变为

select * from user join orders on user.id=orders.user_id;

效果与写上inner是一样的。

执行的结果如下:

3. 隐式内连接

我们在实际开发中,它的使用频率是最高的，其实就是将inner join省略，也不在使用on进行条件过滤，而是直接使用where进行过滤。如下面

select * from user ,orders where user.id=orders.user_id;

结果与inner join on一样。

总结:

inner join on 它可以省略inner,在on后面直接书写比较条件，通过条件来消除迪卡尔积。

隐式内连接，它在开发应用比较多，我们可以省略inner join 表与表之间使用逗号分开，通过where条件来消除迪卡尔积

四.多表查询-外连接

如果我们在开发中需要将所有用户及其订单查询出来，如果用户没有订单，也要查询出用户信息。那么使用内连接是做不到了，这时就需要使用外连接。

外连接并不要求连接的两表的每一条记录在对方表中都一条匹配的记录. 连接表保留所有记录,甚至这条记录没有匹配的记录也要保留. 外连接可依据连接表保留左表, 右表或全部表的行而进一步分为左外连接, 右外连接和全连接.

注意:mysql只支持左外连接与右外连接，而不支持全连接。

1. 左外连接 left outer join on 注意outer 可以省略

例如:select * from user left outer join orders on user.id=orders.user_id;

上面这条sql我们使用的是左外连接，它的意思就是说以user表为准，保留user表中所有数据，右表orders中没有关联的数据，那么就以null关联显示出来,

执行的结果如下:

2. 右外连接 right outer join on 注意 outer可以省略

例如:select * from user right outer join orders on user.id=orders.user_id;

这条sql我们使用的是右外连接，它的意思就是说以orders表为准，保留orders表中所有数据，左表user中没有关联的数据，那么就以null关联显示出来

五.多表查询-子查询

练习:

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

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

3.查询订单价格大于300的订单信息及相关用户的信息。

在sql语言中，当一个查询是另一个查询的条件时，称之为子查询。

按查询结果分类

单行单列子查询

可以使用关系运算符操作

例如:查看用户为张三的订单详情。

i.先根据用户名查询出张三的id

select id from user where username='张三';----得到了一个结果 1

ii.根据用户的id查询该用户的订单

select * from orders where user_id=(select id from user where username='张三');

单列多行子查询

可以使用in,any或all操作

>any：大于子查询中的最小值。

>all： 大于子查询中的最大值。

<any：小于子查询中的最大值。

<all： 小于子查询中的最小值。

!=any或<>any：不等于子查询中的任意值。

!=all或<>all：不等于子查询中的所有值。

=any：等于子查询中任意值。

例如:查询出订单的价格>300的所有用户信息。

i.先查询出所有订单价格>300的用户的id.

select distinct user_id from orders where price >=300;

ii.根据上面查询出的结果，在用户表中查询出用户信息

select * from user where id in(select distinct user_id from orders where price >300);

多行多列子查询

子查询返回的就是一张表，我们直接对这张表操作即可。

例如:查询订单价格大于300的订单信息及相关用户的信息。

i.先得到订单价格大于300的订单信息

select * from orders where price>300;

ii.在查询出用户信息，与上面的多行多列关联

select * from user u ,(select * from orders where price>300) o where o.user_id=u.id;

上面的操作，不使用子查询也可以完成:

select * from user u,orders o where o.price>300 and o.user_id =u.id;