mysql多表操作与练习笔记

mysql多表操作与练习数据库备份与恢复◆数据库备份◆数据库恢复多表操作◆多表设计◆外键介绍与创建◆多表查询介绍◆多表查询-内连接◆多表查询-外连接◆多表查询-子查询SQL练习【内容:数据库备份与恢复】数据库备份数据库的备份是指将数据库转换成对应的sql文件。

数据库导出sql脚本的命令如下：●mysqldump –u用户名–p密码数据库名>生成的脚本文件路径;以上备份数据库的命令中需要用户名和密码，即表明该命令要在用户没有登录的情况下使用数据库恢复数据库的恢复指的是使用备份产生的sql文件恢复数据库，即将sql文件中的sql语句执行就可以恢复数据库内容。

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

●在数据库外恢复mysql -u root -p 数据库名< 文件路径注意:要求数据库必须先创建出来.●.在数据库内恢复source SQL脚本路径：使用这种方式恢复数据，首先要登录数据库【重点总结】备份Mysqldump –u root –p abc mydb>c:/mydb.sql恢复1.mysql –u root –p mydb<c:/mydb.sql2.source c:/mydb.sql掌握多表设计掌握外键的作用及创建方式了解多表查询原理及迪卡尔积掌握内连接掌握外连接掌握子查询【内容:多表操作】多表设计当我们要完成一个软件系统时，需要把系统中的实体抽取出来，形成概念模型。

例如部门、员工都是系统中的实体。

概念模型中的实体最终会成为Java中的类、数据库中的表。

今天我们需要了解的概念模型包括以下两个：●对象模型：针对java中的实体类，例如我们之前学习的domain层中的User类等；●关系模型：对应数据库中的表。

在java中有以下三个关系描述：●is a：例如猫是动物，表示继承关系；●has a：例如人有两只手，表示的是类与成员的关系；●use a：表示类与方法之间的关系；我们今天主要关注的是第二个关系：类与成员的关系。

这种关联关系可以分为如下三种：●一对一关联：例如身份证与人就是一对一的关系，一个人只有一个身份证，一个身份证号只属于一个人。

●一对多关联：例如用户与订单，一个用户可以拥有多个订单，多个订单可以属于同一个用户，这是一个很明显的一对多关联。

●多对多关联：例如商品与订单，一个订单中可以包含多个商品，而一个商品也可以存在于多个订单中，这就是一个多对多关联。

概念模型在java中对应这实体类，在实体类中使用成员变量完成关联关系，而且一般都是双向关联，以下是java中实体类与成员之间的关联关系，如下所示：一对一关联//身份证类一的一方public class IDCard {private Person person; //身份证关联人类一对一}//人类一的一方public class Person {private IDCard idcard; //人类关联身份证类一对一}一对多关联//用户一的一方public class User {private List<Order> orders; //用户可以有多个订单，在用户中关联订单}//订单多的一方public class Order {private User user; //多个订单可以属于一个用户，在订单中关联用户}多对多关联// 订单多的一方public class Order {private List<Product> products;//订单中可以包含多个商品，订单关联商品// 商品多的一方public class Product {private List<Order> orders;//一个商品可以包含在多个订单中在商品关联订单}外键约束概念模型在数据库中就对应数据表，那么表与表之间的关系也包括：一对一，一对多，多对多。

而表与表之间关系是通过外键来维护的。

外键约束特性如下：●外键必须是另一表或自身表的主键的值；●外键可以重复；●外键可以为空；●一张表中可以有多个外键。

表之间关联关系●一对一person表CREATE TABLE person(id INT PRIMARY KEY AUTO_INCREMENT,NAME V ARCHAR(20) -- 姓名)idcard表CREATE TABLE idcard(id INT PRIMARY KEY AUTO_INCREMENT,num V ARCHAR(20) -- 身份证号)上述是我们创建的person表与idcard表，它们之间是一对一关系，那么我们怎样来描述一个人只有一个身份证，而一个身份证只能属于一个人哪？我们只需要在任意一张表中添加一个外键字段，与主表中的主键字段对应就可以描述其一对一的关系。

person表CREATE TABLE person(id INT PRIMARY KEY AUTO_INCREMENT,NAME V ARCHAR(20), -- 姓名idcard_id int)idcard表CREATE TABLE idcard(id INT PRIMARY KEY AUTO_INCREMENT,num V ARCHAR(20), -- 身份证号pid int)●一对多用户表id INT PRIMARY KEY AUTO_INCREMENTNAME V ARCHAR(20))订单表CREATE TABLE orders(id INT PRIMARY KEY AUTO_INCREMENT,price DOUBLE,)上述是订单与用户表，一个用户是可以有多个订单的，它们是很明显的一个一对多(多对一)的关系，那么我们怎样在表中描述它们的对应关系。

CREATE TABLE USER(id INT PRIMARY KEY AUTO_INCREMENTNAME V ARCHAR(20))CREATE TABLE orders(id INT PRIMARY KEY AUTO_INCREMENT,price DOUBLE,uid INT)多对多订单表CREATE TABLE orders(id INT PRIMARY KEY AUTO_INCREMENT,price DOUBLE)商品表CREATE TABLE products(id INT PRIMARY KEY AUTO_INCREMENT,NAME V ARCHAR(30))订单与商品之间是一个很明显的多对多关系，那么我们怎样在表中描述它们的关系订单表CREATE TABLE orders(id INT PRIMARY KEY AUTO_INCREMENT,price DOUBLE)商品表CREATE TABLE products(id INT PRIMARY KEY AUTO_INCREMENT,NAME V ARCHAR(30))订单与商品关联表-中间表oid INT,pid INT,PRIMARY KEY(oid,pid))总结:对于一对一关系，我们在表中描述时，可以在任意一方描述对于一对多关系，我们在表中描述时，在多的一方描述对于多对多关系，我们在表中描述时，会产生一个中间表外键约束作用:通过外键约束可以保证数据的完整性。

我们以用户与订单（一对多关联）来说明一下关于外键约束作用：-- 用户表CREATE TABLE USER(uid INT PRIMARY KEY AUTO_INCREMENT, -- 用户编号NAME V ARCHAR(20) -- 用户姓名)-- 订单表CREATE TABLE orders(id INT PRIMARY KEY AUTO_INCREMENT, -- 订单编号money DOUBLE, -- 订单总价receiveraddress V ARCHAR(50), -- 订单收货地址uid INT -- 用户id)我们向表中插入数据INSERT INTO USER V ALUES(NULL,"tom");INSERT INTO USER V ALUES(NULL,"fox");INSERT INTO orders V ALUES(NULL,1000,"北京",1);INSERT INTO orders V ALUES(NULL,2000,"上海",1);INSERT INTO orders V ALUES(NULL,3000,"广州",2);INSERT INTO orders V ALUES(NULL,4000,"深圳",2);通过数据很明显可以看出，tom有两个订单，订单编号为1,2.fox有两个订单，订单编号为3,4 那么我们执行下面这条sql：DELETE FROM USER WHERE uid=1;这时，我们会发现，在user表中已经将uid=1的用户删除了，但是在orders表中还存在uid=1的用户的订单，破坏了数据的完完整性。

（不应该出现无用户的订单）那么如果我们修改一下表结构，将orders表中的uid设置成外键，在看一下结果。

对于添加外键方式我们可以在创建表时添加，也可以在表创建后在添加外键。

●表创建后添加外键ALTER TABLE orders ADD CONSTRAINT FK_UID FOREIGN KEY(uid) REFERENCES USER(uid);注意：上面操作中已经将uid=1的用户删除了，所以在添加外键时，会报错，将数据重新添加或重新建表就可以。

●创建表时指定外键CREATE TABLE orders(id INT PRIMARY KEY AUTO_INCREMENT, -- 订单idmoney DOUBLE, -- 订单总价receiveraddress V ARCHAR(50), -- 订单收货地址uid INT, -- 用户idCONSTRAINT FK_UID FOREIGN KEY orders(uid) REFERENCES USER(uid) )当我们给orders表中的uid添加外键后，我们在执行DELETE FROM USER WHERE uid=1;会发现报错Query : delete from user where uid=1Error Code : 1451Cannot delete or update a parent row: a foreign key constraint fails (`mydbtest`.`orders`, CONSTRAINT `FK_UID` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`))原因就是因为我们添加了外键，所以我们在删除主表中数据时，要先将子表中数据删除后，才可以删除主表数据。