mysql多表操作及练习笔记

1、mysql多表操作与练习数据库备份与恢复u 数据库备份u 数据库恢复多表操作u 多表设计u 外键介绍与创建u 多表查询介绍u 多表查询-内连接u 多表查询-外连接u 多表查询-子查询SQL练习【内容:数据库备份与恢复】数据库备份数据库的备份是指将数据库转换成对应的sql文件。数据库导出sql脚本的命令如下：l mysqldump u用户名 p密码 数据库名>生成的脚本文件路径;以上备份数据库的命令中需要用户名和密码，即表明该命令要在用户没有登录的情况下使用数据库恢复数据库的恢复指的是使用备份产生的sql文件恢复数据库，即将sql文件中的sql语句执行就可以恢复数据库内容。因为数据库备份只

2、是备份了数据库内容，所以备份产生的sql文件中没有创建数据库的sql语句，在恢复数据库之前需要自己动手创建数据库。l 在数据库外恢复mysql -u root -p 数据库名 < 文件路径注意:要求数据库必须先创建出来.l .在数据库内恢复source SQL脚本路径：使用这种方式恢复数据，首先要登录数据库【重点总结】备份Mysqldump u root p abc mydb>c:/mydb.sql恢复1. mysql u root p mydb<c:/mydb.sql2. source c:/mydb.sql掌握多表设计掌握外键的作用及创建方式了解多表查询原理及迪卡尔积掌握

3、内连接掌握外连接掌握子查询【内容:多表操作】多表设计当我们要完成一个软件系统时，需要把系统中的实体抽取出来，形成概念模型。例如部门、员工都是系统中的实体。概念模型中的实体最终会成为Java中的类、数据库中的表。今天我们需要了解的概念模型包括以下两个：l 对象模型：针对java中的实体类，例如我们之前学习的domain层中的User类等；l 关系模型：对应数据库中的表。在java中有以下三个关系描述：l is a：例如猫是动物，表示继承关系；l has a：例如人有两只手，表示的是类与成员的关系；l use a：表示类与方法之间的关系；我们今天主要关注的是第二个关系：类与成员的关系。这种关联关系

4、可以分为如下三种：l 一对一关联：例如身份证与人就是一对一的关系，一个人只有一个身份证，一个身份证号只属于一个人。l 一对多关联：例如用户与订单，一个用户可以拥有多个订单，多个订单可以属于同一个用户，这是一个很明显的一对多关联。l 多对多关联：例如商品与订单，一个订单中可以包含多个商品，而一个商品也可以存在于多个订单中，这就是一个多对多关联。概念模型在java中对应这实体类，在实体类中使用成员变量完成关联关系，而且一般都是双向关联，以下是java中实体类与成员之间的关联关系，如下所示：一对一关联/身份证类 一的一方public class IDCard private Person perso

5、n; /身份证关联人类 一对一/人类 一的一方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> produc

6、ts; /订单中可以包含多个商品，订单关联商品/ 商品 多的一方public class Product private List<Order> orders; /一个商品可以包含在多个订单中 在商品关联订单外键约束概念模型在数据库中就对应数据表，那么表与表之间的关系也包括：一对一，一对多，多对多。而表与表之间关系是通过外键来维护的。外键约束特性如下：l 外键必须是另一表或自身表的主键的值；l 外键可以重复；l 外键可以为空；l 一张表中可以有多个外键。表之间关联关系l 一对一person表CREATE TABLE person(id INT PRIMARY KEY AUTO_IN

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

8、CHAR(20), - 姓名idcard_id int)idcard表CREATE TABLE idcard(id INT PRIMARY KEY AUTO_INCREMENT,num VARCHAR(20), - 身份证号pid int)l 一对多用户表CREATE TABLE USER(id INT PRIMARY KEY AUTO_INCREMENTNAME VARCHAR(20)订单表CREATE TABLE orders(id INT PRIMARY KEY AUTO_INCREMENT,price DOUBLE,)上述是订单与用户表，一个用户是可以有多个订单的，它们是很明显的一个一

9、对多(多对一)的关系，那么我们怎样在表中描述它们的对应关系。CREATE TABLE USER(id INT PRIMARY KEY AUTO_INCREMENTNAME VARCHAR(20)CREATE TABLE orders(id INT PRIMARY KEY AUTO_INCREMENT,price DOUBLE,uid INT)l 多对多订单表CREATE TABLE orders(id INT PRIMARY KEY AUTO_INCREMENT,price DOUBLE)商品表CREATE TABLE products(id INT PRIMARY KEY AUTO_INCR

10、EMENT,NAME VARCHAR(30)订单与商品之间是一个很明显的多对多关系，那么我们怎样在表中描述它们的关系订单表CREATE TABLE orders(id INT PRIMARY KEY AUTO_INCREMENT,price DOUBLE)商品表CREATE TABLE products(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(30)订单与商品关联表-中间表CREATE TABLE orderitem(oid INT,pid INT,PRIMARY KEY(oid,pid)总结:对于一对一关系，我们在表中描述时，可以在任意一

11、方描述对于一对多关系，我们在表中描述时，在多的一方描述对于多对多关系，我们在表中描述时，会产生一个中间表外键约束作用:通过外键约束可以保证数据的完整性。我们以用户与订单（一对多关联）来说明一下关于外键约束作用：- 用户表CREATE TABLE USER(uid INT PRIMARY KEY AUTO_INCREMENT, - 用户编号NAME VARCHAR(20) - 用户姓名)- 订单表CREATE TABLE orders(id INT PRIMARY KEY AUTO_INCREMENT, - 订单编号money DOUBLE, - 订单总价receiveraddress VARC

12、HAR(50), - 订单收货地址uid INT - 用户id)我们向表中插入数据INSERT INTO USER VALUES(NULL,"tom");INSERT INTO USER VALUES(NULL,"fox");INSERT INTO orders VALUES(NULL,1000,"北京",1);INSERT INTO orders VALUES(NULL,2000,"上海",1);INSERT INTO orders VALUES(NULL,3000,"广州",2);INSER

13、T INTO orders VALUES(NULL,4000,"深圳",2);通过数据很明显可以看出，tom有两个订单，订单编号为1,2.fox有两个订单，订单编号为3,4那么我们执行下面这条sql：DELETE FROM USER WHERE uid=1;这时，我们会发现，在user表中已经将uid=1的用户删除了，但是在orders表中还存在uid=1的用户的订单，破坏了数据的完完整性。（不应该出现无用户的订单）那么如果我们修改一下表结构，将orders表中的uid设置成外键，在看一下结果。对于添加外键方式我们可以在创建表时添加，也可以在表创建后在添加外键。l 表创建后

14、添加外键ALTER TABLE orders ADD CONSTRAINT FK_UID FOREIGN KEY(uid) REFERENCES USER(uid);注意：上面操作中已经将uid=1的用户删除了，所以在添加外键时，会报错，将数据重新添加或重新建表就可以。l 创建表时指定外键CREATE TABLE orders(id INT PRIMARY KEY AUTO_INCREMENT, - 订单idmoney DOUBLE, - 订单总价receiveraddress VARCHAR(50), - 订单收货地址uid INT, - 用户idCONSTRAINT FK_UID FORE

15、IGN 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)原因

16、就是因为我们添加了外键，所以我们在删除主表中数据时，要先将子表中数据删除后，才可以删除主表数据。也就是说，我们如果在删除uid=1的用户必须先在orders表将将uid=1的数据删除，然后才可以在user表中删除。多表查询-内连接内连接(inner join)是应用程序中用的普遍的"连接"操作，它一般都是默认连接类型。内连接基于连接谓词将两张表(如 A 和 B)的列组合在一起，产生新的结果表。查询会将 A 表的每一行和 B 表的每一行进行比较，并找出满足连接谓词的组合。当连接谓词被满足，A 和 B 中匹配的行会按列组合(并排组合)成结果集中的一行。连接产生的结果集，可以定义

17、为首先对两张表做笛卡尔积(交叉连接) - 将 A 中的每一行和 B 中的每一行组合，然后返回满足连接谓词的记录。实际上 SQL 产品会尽可能用其他方式去实现连接，笛卡尔积运算是非常没效率的.SQL 定义了两种不同语法方式去表示"连接"。首先是"显式连接符号"，它显式地使用关键字 JOIN，其次是"隐式连接符号"，它使用所谓的"隐式连接符号"。隐式连接符号把需要连接的表放到 SELECT 语句的 FROM 部分，并用逗号隔开。这样就构成了一个"交叉连接&qu

18、ot;，WHERE 语句可能放置一些过滤谓词(过滤条件)。那些过滤谓词在功能上等价于显式连接符号.内连接"可以进一步被分为: 相等连接，自然连接，和交叉连接.我们还是使用订单与用户表来说明内连接。1.交叉连接 cross join交叉连接(cross join)，又称笛卡尔连接(cartesian join)或叉乘(Product)，它是所有类型的内连接的基础。把表视为行记录的集合，交叉连接即返回这两个集合的笛卡尔积。这其实等价于内连接的链接条件为"永真"，或连接条件不存在.笛卡尔积引用自数学，在数学中，两个集合X和Y的笛卡尔积（Cartesian pr

19、oduct），又称直积。假设集合A=a,b，集合B=0,1,2，则两个集合的笛卡尔积为(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)。可以扩展到多个集合的情况。SELECT * FROM USER CROSS JOIN orders;2.自然连接 natural join 相等连接,是比较连接一种特例，它的连接谓词只用了相等比较。l natural join：它可以通过两张表中的相等字段(关联字段自动消除迪卡尔积)SELECT * FROM USER NATURAL JOIN orders;得到了两条正确结果,natural join会将两张表中的关联字段 uid自动关

20、联 消除了迪卡尔积3.相等连接 inner joinon inner join usingl innser join onSELECT * FROM USER INNER JOIN orders ON user.uid=orders.uid;通过on后面的条件，将两张表中的数据过滤。l inner join using：它可以通过指定两张表中比较的字段来进行等式消除迪卡尔积SELECT * FROM USER INNER JOIN orders USING(uid);上面我们讲过inner join on，inner join using，可以将inner省略，因为join默认就是内连接(in

21、ner join),，省略inner后的语句变为SELECT * FROM USER JOIN orders ON user.uid=orders.uid;SELECT * FROM USER JOIN orders USING(uid);.效果与写上inner是一样的。4.隐式内连接我们在实际开发中，使用最多的还有一种叫做隐匿内连接，其实就是将inner join省略，也不在使用on进行条件过滤，而是直接使用where进行过滤。如下面SELECT * FROM USER,orders WHERE user.uid=orders.uid;结果与inner join on一样。总结:cross

22、join 它只是将两个表连接起来，产生笛卡尔积。natural join 它是使用表中主外键关联来消除迪卡尔积。inner join using 可以省略inner ,在using后面直接书写比较的字段名称，注意要加括号 inner join on 它可以省略inner,在on后面直接书写比较条件，通过条件来消除迪卡尔积。隐式内连接，它在开发应用比较多，我们可以省略inner join 表与表之间使用逗号分开，通过where条件来消除迪卡尔积多表查询-外连接下面我们通过一个示例来说明一下关于外连接操作。向user表中插入一条记录INSERT INTO USER VALUES(NULL,&quo

23、t;james");User表中的记录如下:Orders表中记录如下:如果我们还是使用内连接来查询所有用户的订单那么结果如下:SELECT * FROM USER,orders WHERE user.uid=orders.uid;我们会发现，没有查找到james的信息，原因就是它没有订单，而内连接只是将有关联的数据汇集查询出来。那么如果我们在开发中需要将所有用户及其订单查询出来，如果用户没有订单，也要查询出用户信息。那么使用内连接很显然是做不到了，这时就需要使用外连接。外连接并不要求连接的两表的每一条记录在对方表中都一条匹配的记录. 连接表保留所有记录 - 甚至这条记录没有匹配的记录

24、也要保留. 外连接可依据连接表保留左表, 右表或全部表的行而进一步分为左外连接, 右外连接和全连接.注意:mysql只支持左外连接与右外连接，而不支持全连接。1. 左外连接 left outer join on 注意outer 可以省略2. 右外连接 right outer join on 注意 outer可以省略我们执行下面sql语句查看一下结果:SELECT * FROM USER LEFT OUTER JOIN orders ON user.uid=orders.uid;我们发现，这时查询出了james的信息，但是因为它没有订单，所有关于orders表中的数据以null显示出来。SELE

25、CT * FROM USER LEFT OUTER JOIN orders ON user.uid=orders.uid;这段sql我们使用的是左外连接，也就是说以user表为准，保留user表中所有数据，右表orders中没有关联的数据，那么我们就以null关联显示出来,右外连接也是同理,如下:SELECT * FROM orders RIGHT OUTER JOIN USER ON orders.uid=user.uid;多表查询-子查询在sql语言中，selectfrom语为一个查询块，将一个查询块嵌套在另一个查询块中作为条件称为嵌套查询,也称为子查询.外层的查询块称为父查询，内层的查询

26、块称为子查询。按查询结果分类Ø 单行单列子查询n 可以使用关系运算符操作例如:查询出订单id=1的用户的名称。i. 先根据订单的id查询出用户的uidSelect uid from orders where id=1;-得到了一个结果 1ii. 根据用户的uid查询出用户的名称Select name from user where uid=(Select uid from orders where id=1);Ø 单列多行子查询n 可以使用in,any或all操作>any：大于子查询中的最小值。>all： 大于子查询中的最大值。<any：小于子查询中的最大

27、值。<all： 小于子查询中的最小值。>=any：大于等于子查询中的最小值。>=all：大于等于子查询中的最大值。<=any：小于等于子查询中最大值。<=all：小于等于子查询中最小值。!=any或<>any：不等于子查询中的任意值。!=all或<>all：不等于子查询中的所有值。=any：等于子查询中任意值。=all：等于子查询中所有值（无意义）例如:查询出订单的价格>=2000的所有用户信息。i. 先查询出所有订单价格>=200的用户的uid.Select distinct uid from orders where mon

28、ey>=2000;ii. 根据上面查询出的结果，在用户表中查询出用户信息Select * from user where uid in (Select distinct uid from orders where money>=2000)Ø 多行多列子查询子查询返回的就是一张表，我们直接对这张表操作即可。例如:将订单价格大于2000的订单信息得到，并查询出用户的相关信息。i. 先得到订单价格大于2000的订单信息Select * from orders where money>2000;ii. 在查询出用户信息，与上面的多行多列关联Select * from use

29、r,( Select * from orders where money>2000) myorder where user.uid=myorder.uid;上面的操作，不使用子查询也可以完成:Select * from user,orders where user.uid=orders.uid and orders.money>2000;【笔试面试题】描述外键约束的作用怎样添加外键【重点总结】表与表之间存在一对多，多对多，一对一的关系，在表中描述它们的关系有以下规则：一对一 在任意一方添加外键一对多 在多的一方添加外键多对多 产生一个中间表，在表中描述对应关系掌握添加外键的语句多表

30、查询方式1. 内连接2. 外连接3. 子查询【第三阶段】【学习目标】完成所有练习题【内容:SQL练习】基本表结构与数据部门表CREATE TABLE DEPT(DEPTNO INT PRIMARY KEY, - 部门编号 DNAME VARCHAR(14) , - 部门名称 LOC VARCHAR(13) ) ; - 部门地址INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');IN

31、SERT INTO DEPT VALUES (30,'SALES','CHICAGO');INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');员工表CREATE TABLE EMP (EMPNO INT PRIMARY KEY, - 员工编号 ENAME VARCHAR(10), - 员工名称 JOB VARCHAR(9), - 工作 MGR DOUBLE, - 直属领导编号 HIREDATE DATE, - 入职时间 SAL DOUBLE, - 工资 COMM DOUBLE, -

32、 奖金 DEPTNO INT, - 部门号 FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO);SELECT * FROM emp; INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);INSERT INTO EM

33、P VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);INSERT

34、INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20);I

35、NSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,2

36、0);INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,

37、10); 工资等级表CREATE TABLE SALGRADE ( GRADE INT, - 工资等级 LOSAL DOUBLE, - 最低工资 HISAL DOUBLE ); - 最高工资INSERT INTO SALGRADE VALUES (1,700,1200);INSERT INTO SALGRADE VALUES (2,1201,1400);INSERT INTO SALGRADE VALUES (3,1401,2000);INSERT INTO SALGRADE VALUES (4,2001,3000);INSERT INTO SALGRADE VALUES (5,3001,99

38、99);SELECT * FROM salgrade;第一部分练习1、查找部门30中员工的详细信息。2、找出从事clerk工作的员工的编号、姓名、部门号。3、检索出奖金多于基本工资的员工信息。4、检索出奖金多于基本工资60%的员工信息。5、找出10部门的经理、20部门的职员 的员工信息。6、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。7、找出获得奖金的员工的工作。8、找出奖金少于100或者没有获得奖金的员工的信息。9、找出姓名以A、B、S开始的员工信息。10、找到名字长度为7个字符的员工信息。11、名字中不包含R字符的员工信息。12、返回员工的

39、详细信息并按姓名排序。13、返回员工的信息并按员工的工作年限降序排列。14、返回员工的信息并按工作降序工资升序排列。15、计算员工的日薪(按30天)。16、找出姓名中包含A的员工信息。第二部分练习1、返回拥有员工的部门名、部门号。(dept,emp)2、工资水平多于smith的员工信息。3、返回员工和所属经理的姓名。（自连接）4、返回雇员的雇佣日期早于其领导雇佣日期的员工及其领导姓名。(在日期类型可以直接比较)5、返回员工姓名及其所在的部门名称。6、返回从事clerk工作的员工姓名和所在部门名称。7、返回部门号及其本部门的最低工资。8、返回销售部(sales)所有员工的姓名。9、返回工资多于平

40、均工资的员工。10、返回与SCOTT从事相同工作的员工。11、返回与30部门员工工资水平相同的员工姓名与工资。12、返回工资高于30部门所有员工工资水平的员工信息。13、返回部门号、部门名、部门所在位置及其每个部门的员工总数。14、返回员工的姓名、所在部门名及其工资。15、返回员工的详细信息。(包括部门名)16、返回员工工作及其从事此工作的最低工资。17、返回不同部门经理的最低工资。18、计算出员工的年薪，并且以年薪排序。19、返回工资处于第四级别的员工的姓名。20、返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资21、返回工资为二等级的职员名字、部门所在地、二等级员工工资

41、的最低工资和最高工资SELECT * FROM emp WHERE DEPTNO=30;SELECT EMPNO,ENAME,DEPTNO FROM emp WHERE job = 'CLERK'SELECT * FROM emp WHERE comm > sal;SELECT * FROM emp WHERE comm > sal*1.6;SELECT * FROM emp WHERE (job='MANAGER' AND deptno=10) OR (job='CLERK' AND deptno=20);SELECT * FROM emp WHERE (job!='MANAGER&#