WebDay17 MySQL 约束多表查询视图数据备份与还原

技术2022-07-10 114

MySQL 约束多表查询视图数据备份与还原

一约束1.约束的概念和分类2.主键约束3.主键自动增长约束4.唯一约束5.非空约束6.默认值7.外键约束8.外键的级联更新和级联删除(了解) 二表关系(重点)2.1概述2.2一对一(了解)2.3一对多2.4多对多三、多表查询(重点)1.多表查询-数据准备2.多表查询-笛卡尔积查询(了解)3.多表查询-内连接查询4.多表查询-外连接查询5.多表查询-子查询6.多表查询练习7.多表查询-自关联查询四、视图1.视图的概念2.视图的好处3.视图数据准备4.视图的创建5.视图的查询6.视图的修改7.视图的删除8.视图的总结五、备份与还原六用户权限 DCL【了解】七总结

一约束

1.约束的概念和分类

约束的概念对表中的数据进行限定，保证数据的正确性、有效性、完整性！约束的分类约束说明PRIMARY KEY主键约束PRIMARY KEY AUTO_INCREMENT主键、自动增长UNIQUE唯一约束NOT NULL非空约束FOREIGN KEY外键约束FOREIGN KEY ON UPDATE CASCADE外键级联更新FOREIGN KEY ON DELETE CASCADE外键级联删除

2.主键约束

主键约束特点主键约束包含：非空和唯一两个功能一张表只能有一个列作为主键主键一般用于表中数据的唯一标识建表时添加主键约束 -- 标准语法 CREATE TABLE 表名( 列名数据类型 PRIMARY KEY, 列名数据类型, ... ); -- 创建student表 CREATE TABLE student( id INT PRIMARY KEY -- 给id添加主键约束 ); -- 添加数据 INSERT INTO student VALUES (1),(2); -- 主键默认唯一，添加重复数据，会报错 INSERT INTO student VALUES (2); -- 主键默认非空，不能添加null的数据 INSERT INTO student VALUES (NULL); -- 查询student表 SELECT * FROM student; -- 查询student表详细 DESC student; 删除主键 -- 标准语法 ALTER TABLE 表名 DROP PRIMARY KEY; -- 删除主键 ALTER TABLE student DROP PRIMARY KEY; 建表后单独添加主键 -- 标准语法 ALTER TABLE 表名 MODIFY 列名数据类型 PRIMARY KEY; -- 添加主键 ALTER TABLE student MODIFY id INT PRIMARY KEY;

3.主键自动增长约束

建表时添加主键自增约束 -- 标准语法 CREATE TABLE 表名( 列名数据类型 PRIMARY KEY AUTO_INCREMENT, 列名数据类型, ... ); -- 创建student2表 CREATE TABLE student2( id INT PRIMARY KEY AUTO_INCREMENT -- 给id添加主键自增约束 ); -- 添加数据 INSERT INTO student2 VALUES (1),(2); -- 添加null值，会自动增长 INSERT INTO student2 VALUES (NULL),(NULL); -- 查询student2表 SELECT * FROM student2; -- student2表详细 DESC student2; 删除自动增长 -- 标准语法 ALTER TABLE 表名 MODIFY 列名数据类型; -- 删除自动增长 ALTER TABLE student2 MODIFY id INT; 建表后单独添加自动增长 -- 标准语法 ALTER TABLE 表名 MODIFY 列名数据类型 AUTO_INCREMENT; -- 添加自动增长 ALTER TABLE student2 MODIFY id INT AUTO_INCREMENT;

4.唯一约束

建表时添加唯一约束 -- 标准语法 CREATE TABLE 表名( 列名数据类型 UNIQUE, 列名数据类型, ... ); -- 创建student3表 CREATE TABLE student3( id INT PRIMARY KEY AUTO_INCREMENT, tel VARCHAR(20) UNIQUE -- 给tel列添加唯一约束 ); -- 添加数据 INSERT INTO student3 VALUES (NULL,'18888888888'),(NULL,'18666666666'); -- 添加重复数据，会报错 INSERT INTO student3 VALUES (NULL,'18666666666'); -- 查询student3数据表 SELECT * FROM student3; -- student3表详细 DESC student3; 删除唯一约束 -- 标准语法 ALTER TABLE 表名 DROP INDEX 列名; -- 删除唯一约束 ALTER TABLE student3 DROP INDEX tel; 建表后单独添加唯一约束 -- 标准语法 ALTER TABLE 表名 MODIFY 列名数据类型 UNIQUE; -- 添加唯一约束 ALTER TABLE student3 MODIFY tel VARCHAR(20) UNIQUE;

5.非空约束

建表时添加非空约束 -- 标准语法 CREATE TABLE 表名( 列名数据类型 NOT NULL, 列名数据类型, ... ); -- 创建student4表 CREATE TABLE student4( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL -- 给name添加非空约束 ); -- 添加数据 INSERT INTO student4 VALUES (NULL,'张三'),(NULL,'李四'); -- 添加null值，会报错 INSERT INTO student4 VALUES (NULL,NULL); 删除非空约束 -- 标准语法 ALTER TABLE 表名 MODIFY 列名数据类型; -- 删除非空约束 ALTER TABLE student4 MODIFY NAME VARCHAR(20);

建表后单独添加非空约束

-- 标准语法 ALTER TABLE 表名 MODIFY 列名数据类型 NOT NULL; -- 添加非空约束 ALTER TABLE student4 MODIFY NAME VARCHAR(20) NOT NULL;

疑问：唯一 + 非空 = 主键？？回答：不等于，主键约束一张表只能有一个，唯一+非空设置多个

6.默认值

作用：限定某一列的默认值，再没有指定的情况下所有列的默认值为null

1. 创建表设置默认值 create table 表名( 列名数据类型 default 默认值, ... ... ); -- 默认值 CREATE TABLE stu6( id INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(32), sex VARCHAR(5) DEFAULT '男' ); INSERT INTO stu6(id,`name`) VALUES(1,'小张'); INSERT INTO stu6(id,`name`,sex) VALUES(2,'小刘','女'); -- 因为我们指定了默认值为男，你再插入null，会把默认值覆盖... INSERT INTO stu6 VALUES(3,'小王',NULL);

7.外键约束

外键约束概念

让表和表之间产生关系，从而保证数据的准确性！

建表时添加外键约束

为什么要有外键约束 -- 创建db2数据库 CREATE DATABASE db2; -- 使用db2数据库 USE db2; -- 创建user用户表 CREATE TABLE USER( id INT PRIMARY KEY AUTO_INCREMENT, -- id NAME VARCHAR(20) NOT NULL -- 姓名 ); -- 添加用户数据 INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四'),(NULL,'王五'); -- 创建orderlist订单表 CREATE TABLE orderlist( id INT PRIMARY KEY AUTO_INCREMENT, -- id number VARCHAR(20) NOT NULL, -- 订单编号 uid INT -- 订单所属用户 ); -- 添加订单数据 INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1), (NULL,'hm003',2),(NULL,'hm004',2), (NULL,'hm005',3),(NULL,'hm006',3); -- 添加一个订单，但是没有所属用户。这合理吗？ INSERT INTO orderlist VALUES (NULL,'hm007',8); -- 删除王五这个用户，但是订单表中王五还有很多个订单呢。这合理吗？ DELETE FROM USER WHERE NAME='王五'; -- 所以我们需要添加外键约束，让两张表产生关系外键约束格式 CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名) 创建表添加外键约束 -- 创建user用户表 CREATE TABLE USER( id INT PRIMARY KEY AUTO_INCREMENT, -- id NAME VARCHAR(20) NOT NULL -- 姓名 ); -- 添加用户数据 INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四'),(NULL,'王五'); -- 创建orderlist订单表 CREATE TABLE orderlist( id INT PRIMARY KEY AUTO_INCREMENT, -- id number VARCHAR(20) NOT NULL, -- 订单编号 uid INT, -- 订单所属用户 CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) -- 添加外键约束 ); -- 添加订单数据 INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1), (NULL,'hm003',2),(NULL,'hm004',2), (NULL,'hm005',3),(NULL,'hm006',3); -- 添加一个订单，但是没有所属用户。无法添加 INSERT INTO orderlist VALUES (NULL,'hm007',8); -- 删除王五这个用户，但是订单表中王五还有很多个订单呢。无法删除 DELETE FROM USER WHERE NAME='王五';

删除外键约束

-- 标准语法 ALTER TABLE 表名 DROP FOREIGN KEY 外键名; -- 删除外键 ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1; 建表后添加外键约束 -- 标准语法 ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名); -- 添加外键约束 ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id); 外键分类物理外键真正使用foreign key在数据库层面关联表；互联网系统用户量大，并发度高。物理外键需要数据库维护事物的特性，资源消耗大。不易扩展，数据库性能容易成为系统的瓶颈。传统行业软件项目并发量可控，数据量也一般不会超大。物理外键可以借助数据库产品自身的特性实现表与关联表之间的数据一致性和更新，降低开发成本不适用于互联网项目，适用于普通项目逻辑外键有外键字段，但是不会在数据库层面通过foreign key关联，在程序中维护表的关系数据库表的关系通过逻辑外键，在项目内控制，让应用服务器承担此部分的压力，且应用服务器易拓展。提高了开发成本，降低了开发效率和速度。不适用于普通项目，适用于互联网项目

8.外键的级联更新和级联删除(了解)

什么是级联更新和级联删除当我想把user用户表中的某个用户删掉，我希望该用户所有的订单也随之被删除当我想把user用户表中的某个用户id修改，我希望订单表中该用户所属的订单用户编号也随之修改添加级联更新和级联删除 -- 添加外键约束，同时添加级联更新标准语法 ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE; -- 添加外键约束，同时添加级联删除标准语法 ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON DELETE CASCADE; -- 添加外键约束，同时添加级联更新和级联删除标准语法 ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE ON DELETE CASCADE; -- 删除外键约束 ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1; -- 添加外键约束，同时添加级联更新和级联删除 ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) ON UPDATE CASCADE ON DELETE CASCADE; -- 将王五用户的id修改为5 订单表中的uid也随之被修改 UPDATE USER SET id=5 WHERE id=3; -- 将王五用户删除订单表中该用户所有订单也随之删除 DELETE FROM USER WHERE id=5;

二表关系(重点)

2.1概述

现实生活中，（班级）实体与（学生）实体之间肯定是有关系的，那么我们在设计表的时候，就应该体现出（班级）表与（学生）表之间的这种关系！

简称：关系型数据库（Relation DBMS）

1. 一对多应用场景：班级和学生、部门和员工解释：一个班级下面有多名同学，多名同学属于某一个班级 2. 多对多应用场景：老师和学生、学生和课程解释：一名老师可以教导多名学生，一名学生可以被多个老师教导 3. 一对一应用场景：公民和身份证号、公司和注册地解释：一个公民只能有一个身份证号，一个身份证号对应一个公民

2.2一对一(了解)

分析人和身份证。一个人只有一个身份证，一个身份证只能对应一个人！实现原则在任意一个表建立外键，去关联另外一个表的主键 SQL演示 -- 创建db5数据库 CREATE DATABASE db5; -- 使用db5数据库 USE db5; -- 创建person表 CREATE TABLE person( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); -- 添加数据 INSERT INTO person VALUES (NULL,'张三'),(NULL,'李四'); -- 创建card表 CREATE TABLE card( id INT PRIMARY KEY AUTO_INCREMENT, number VARCHAR(50), pid INT UNIQUE, CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id) -- 添加外键 ); -- 添加数据 INSERT INTO card VALUES (NULL,'12345',1),(NULL,'56789',2);

2.3一对多

分析用户和订单。一个用户可以有多个订单！商品分类和商品。一个分类下可以有多个商品！实现原则在多的一方，建立外键约束，来关联一的一方主键 SQL演示 /* 用户和订单 */ -- 创建user表 CREATE TABLE USER( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); -- 添加数据 INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四'); -- 创建orderlist表 CREATE TABLE orderlist( id INT PRIMARY KEY AUTO_INCREMENT, number VARCHAR(20), uid INT, CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) -- 添加外键约束 ); -- 添加数据 INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),(NULL,'hm003',2),(NULL,'hm004',2); /* 商品分类和商品 */ -- 创建category表 CREATE TABLE category( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10) ); -- 添加数据 INSERT INTO category VALUES (NULL,'手机数码'),(NULL,'电脑办公'); -- 创建product表 CREATE TABLE product( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(30), cid INT, CONSTRAINT pc_fk1 FOREIGN KEY (cid) REFERENCES category(id) -- 添加外键约束 ); -- 添加数据 INSERT INTO product VALUES (NULL,'华为P30',1),(NULL,'小米note3',1), (NULL,'联想电脑',2),(NULL,'苹果电脑',2);

2.4多对多

分析学生和课程。一个学生可以选择多个课程，一个课程也可以被多个学生选择！实现原则需要借助第三张表中间表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键 SQL演示 -- 创建student表 CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); -- 添加数据 INSERT INTO student VALUES (NULL,'张三'),(NULL,'李四'); -- 创建course表 CREATE TABLE course( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10) ); -- 添加数据 INSERT INTO course VALUES (NULL,'语文'),(NULL,'数学'); -- 创建中间表 CREATE TABLE stu_course( id INT PRIMARY KEY AUTO_INCREMENT, sid INT, -- 用于和student表的id进行外键关联 cid INT, -- 用于和course表的id进行外键关联 CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id), -- 添加外键约束 CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id) -- 添加外键约束 ); -- 添加数据 INSERT INTO stu_course VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);

三、多表查询(重点)

1.多表查询-数据准备

SQL语句 -- 创建db6数据库 CREATE DATABASE db6; -- 使用db6数据库 USE db6; -- 创建user表 CREATE TABLE USER( id INT PRIMARY KEY AUTO_INCREMENT, -- 用户id NAME VARCHAR(20), -- 用户姓名 age INT -- 用户年龄 ); -- 添加数据 INSERT INTO USER VALUES (1,'张三',23); INSERT INTO USER VALUES (2,'李四',24); INSERT INTO USER VALUES (3,'王五',25); INSERT INTO USER VALUES (4,'赵六',26); -- 订单表 CREATE TABLE orderlist( id INT PRIMARY KEY AUTO_INCREMENT, -- 订单id number VARCHAR(30), -- 订单编号 uid INT, -- 外键字段 CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) ); -- 添加数据 INSERT INTO orderlist VALUES (1,'hm001',1); INSERT INTO orderlist VALUES (2,'hm002',1); INSERT INTO orderlist VALUES (3,'hm003',2); INSERT INTO orderlist VALUES (4,'hm004',2); INSERT INTO orderlist VALUES (5,'hm005',3); INSERT INTO orderlist VALUES (6,'hm006',3); INSERT INTO orderlist VALUES (7,'hm007',NULL); -- 商品分类表 CREATE TABLE category( id INT PRIMARY KEY AUTO_INCREMENT, -- 商品分类id NAME VARCHAR(10) -- 商品分类名称 ); -- 添加数据 INSERT INTO category VALUES (1,'手机数码'); INSERT INTO category VALUES (2,'电脑办公'); INSERT INTO category VALUES (3,'烟酒茶糖'); INSERT INTO category VALUES (4,'鞋靴箱包'); -- 商品表 CREATE TABLE product( id INT PRIMARY KEY AUTO_INCREMENT, -- 商品id NAME VARCHAR(30), -- 商品名称 cid INT, -- 外键字段 CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id) ); -- 添加数据 INSERT INTO product VALUES (1,'华为手机',1); INSERT INTO product VALUES (2,'小米手机',1); INSERT INTO product VALUES (3,'联想电脑',2); INSERT INTO product VALUES (4,'苹果电脑',2); INSERT INTO product VALUES (5,'中华香烟',3); INSERT INTO product VALUES (6,'玉溪香烟',3); INSERT INTO product VALUES (7,'计生用品',NULL); -- 中间表 CREATE TABLE us_pro( upid INT PRIMARY KEY AUTO_INCREMENT, -- 中间表id uid INT, -- 外键字段。需要和用户表的主键产生关联 pid INT, -- 外键字段。需要和商品表的主键产生关联 CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES USER(id), CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id) ); -- 添加数据 INSERT INTO us_pro VALUES (NULL,1,1); INSERT INTO us_pro VALUES (NULL,1,2); INSERT INTO us_pro VALUES (NULL,1,3); INSERT INTO us_pro VALUES (NULL,1,4); INSERT INTO us_pro VALUES (NULL,1,5); INSERT INTO us_pro VALUES (NULL,1,6); INSERT INTO us_pro VALUES (NULL,1,7); INSERT INTO us_pro VALUES (NULL,2,1); INSERT INTO us_pro VALUES (NULL,2,2); INSERT INTO us_pro VALUES (NULL,2,3); INSERT INTO us_pro VALUES (NULL,2,4); INSERT INTO us_pro VALUES (NULL,2,5); INSERT INTO us_pro VALUES (NULL,2,6); INSERT INTO us_pro VALUES (NULL,2,7); INSERT INTO us_pro VALUES (NULL,3,1); INSERT INTO us_pro VALUES (NULL,3,2); INSERT INTO us_pro VALUES (NULL,3,3); INSERT INTO us_pro VALUES (NULL,3,4); INSERT INTO us_pro VALUES (NULL,3,5); INSERT INTO us_pro VALUES (NULL,3,6); INSERT INTO us_pro VALUES (NULL,3,7); INSERT INTO us_pro VALUES (NULL,4,1); INSERT INTO us_pro VALUES (NULL,4,2); INSERT INTO us_pro VALUES (NULL,4,3); INSERT INTO us_pro VALUES (NULL,4,4); INSERT INTO us_pro VALUES (NULL,4,5); INSERT INTO us_pro VALUES (NULL,4,6); INSERT INTO us_pro VALUES (NULL,4,7);

2.多表查询-笛卡尔积查询(了解)

有两张表，获取这两个表的所有组合情况要完成多表查询，需要消除这些没有用的数据多表查询格式 SELECT 列名列表 FROM 表名列表 WHERE 条件... 笛卡尔积查询 -- 标准语法 SELECT 列名 FROM 表名1,表名2,...; -- 查询user表和orderlist表 SELECT * FROM USER,orderlist;

3.多表查询-内连接查询

查询原理内连接查询的是两张表有交集的部分数据(有主外键关联的数据) 显式内连接 -- 标准语法 SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 条件; -- 查询用户信息和对应的订单信息 SELECT * FROM USER INNER JOIN orderlist ON user.id=orderlist.uid; SELECT * FROM USER JOIN orderlist ON user.id=orderlist.uid; -- 查询用户信息和对应的订单信息，起别名 SELECT * FROM USER u JOIN orderlist o ON u.id=o.uid; -- 查询用户姓名，年龄。和订单编号 SELECT u.`name`, -- 姓名 u.`age`, -- 年龄 o.`number` -- 订单编号 FROM USER u -- 用户表 JOIN orderlist o -- 订单表 ON u.`id` = o.`uid`; 隐式内连接 -- 标准语法 SELECT 列名 FROM 表名1,表名2 WHERE 条件; -- 查询用户姓名，年龄。和订单编号 SELECT u.`name`, -- 姓名 u.`age`, -- 年龄 o.`number` -- 订单编号 FROM USER u, -- 用户表 orderlist o -- 订单表 WHERE u.`id`=o.`uid`;

4.多表查询-外连接查询

左外连接

查询原理查询左表的全部数据，和左右两张表有交集部分的数据基本演示 -- 标准语法 SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件; -- 查询所有用户信息，以及用户对应的订单信息 SELECT u.`name`, -- 姓名 u.`age`, -- 年龄 o.`number` -- 订单编号 FROM USER u -- 用户表 LEFT OUTER JOIN orderlist o -- 订单表 ON u.`id`=o.`uid`;

右外连接

查询原理查询右表的全部数据，和左右两张表有交集部分的数据基本演示 -- 基本语法 SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件; -- 查询所有订单信息，以及订单所属的用户信息 SELECT u.`name`, -- 姓名 u.`age`, -- 年龄 o.`number` -- 订单编号 FROM USER u -- 用户表 RIGHT OUTER JOIN orderlist o -- 订单表 ON u.`id`=o.`uid`;

5.多表查询-子查询

子查询介绍

查询语句中嵌套了查询语句。我们就将嵌套查询称为子查询！

子查询-结果是单行单列的

可以作为条件，使用运算符进行判断！基本演示 -- 标准语法 SELECT 列名 FROM 表名 WHERE 列名=(SELECT 聚合函数(列名) FROM 表名 [WHERE 条件]); -- 查询年龄最高的用户姓名 SELECT MAX(age) FROM USER; -- 查询出最高年龄 SELECT NAME,age FROM USER WHERE age=26; -- 根据查询出来的最高年龄，查询姓名和年龄 SELECT NAME,age FROM USER WHERE age = (SELECT MAX(age) FROM USER);

子查询-结果是多行单列的

可以作为条件，使用运算符in或not in进行判断！基本演示 -- 标准语法 SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]); -- 查询张三和李四的订单信息 SELECT id FROM USER WHERE NAME='张三' OR NAME='李四'; -- 查询张三和李四用户的id SELECT number,uid FROM orderlist WHERE uid=1 OR uid=2; -- 根据id查询订单 SELECT number,uid FROM orderlist WHERE uid IN (SELECT id FROM USER WHERE NAME='张三' OR NAME='李四');

子查询-结果是多行多列的

可以作为一张虚拟表参与查询！基本演示 -- 标准语法 SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件]; -- 查询订单表中id大于4的订单信息和所属用户信息 SELECT * FROM USER u,(SELECT * FROM orderlist WHERE id>4) o WHERE u.id=o.uid;

6.多表查询练习

查询用户的编号、姓名、年龄。订单编号 /* 分析：用户的编号、姓名、年龄 user表订单编号 orderlist表条件：user.id = orderlist.uid */ SELECT t1.`id`, -- 用户编号 t1.`name`, -- 用户姓名 t1.`age`, -- 用户年龄 t2.`number` -- 订单编号 FROM USER t1, -- 用户表 orderlist t2 -- 订单表 WHERE t1.`id` = t2.`uid`; 查询所有的用户。用户的编号、姓名、年龄。订单编号 /* 分析：用户的编号、姓名、年龄 user表订单编号 orderlist表条件：user.id = orderlist.uid 查询所有用户，使用左外连接 */ SELECT t1.`id`, -- 用户编号 t1.`name`, -- 用户姓名 t1.`age`, -- 用户年龄 t2.`number` -- 订单编号 FROM USER t1 -- 用户表 LEFT OUTER JOIN orderlist t2 -- 订单表 ON t1.`id` = t2.`uid`; 查询所有的订单。用户的编号、姓名、年龄。订单编号 /* 分析：用户的编号、姓名、年龄 user表订单编号 orderlist表条件：user.id = orderlist.uid 查询所有订单，使用右外连接 */ SELECT t1.`id`, -- 用户编号 t1.`name`, -- 用户姓名 t1.`age`, -- 用户年龄 t2.`number` -- 订单编号 FROM USER t1 -- 用户表 RIGHT OUTER JOIN orderlist t2 -- 订单表 ON t1.`id` = t2.`uid`; 查询用户年龄大于23岁的信息。显示用户的编号、姓名、年龄。订单编号 /* 分析：用户的编号、姓名、年龄 user表订单编号 orderlist表条件：user.age > 23 AND user.id = orderlist.uid */ /* select t1.`id`, -- 用户编号 t1.`name`, -- 用户姓名 t1.`age`, -- 用户年龄 t2.`number` -- 订单编号 from user t1, -- 用户表 orderlist t2 -- 订单表 where t1.`age` > 23 and t1.`id` = t2.`uid`; */ SELECT t1.`id`, -- 用户编号 t1.`name`, -- 用户姓名 t1.`age`, -- 用户年龄 t2.`number` -- 订单编号 FROM USER t1 -- 用户表 LEFT OUTER JOIN orderlist t2 -- 订单表 ON t1.`id` = t2.`uid` WHERE t1.`age` > 23; 查询张三和李四用户的信息。显示用户的编号、姓名、年龄。订单编号 /* 分析：用户的编号、姓名、年龄 user表订单编号 orderlist表条件：user.id = orderlist.uid AND user.name IN ('张三','李四'); */ SELECT t1.`id`, -- 用户编号 t1.`name`, -- 用户姓名 t1.`age`, -- 用户年龄 t2.`number` -- 订单编号 FROM USER t1, -- 用户表 orderlist t2 -- 订单表 WHERE t1.`id` = t2.`uid` AND -- (t1.`name` = '张三' OR t1.`name` = '李四'); t1.`name` IN ('张三','李四'); 查询商品分类的编号、分类名称。分类下的商品名称 /* 分析：商品分类的编号、分类名称 category表分类下的商品名称 product表条件：category.id = product.cid */ SELECT t1.`id`, -- 分类编号 t1.`name`, -- 分类名称 t2.`name` -- 商品名称 FROM category t1, -- 商品分类表 product t2 -- 商品表 WHERE t1.`id` = t2.`cid`; 查询所有的商品分类。商品分类的编号、分类名称。分类下的商品名称 /* 分析：商品分类的编号、分类名称 category表分类下的商品名称 product表条件：category.id = product.cid 查询所有的商品分类，使用左外连接 */ SELECT t1.`id`, -- 分类编号 t1.`name`, -- 分类名称 t2.`name` -- 商品名称 FROM category t1 -- 商品分类表 LEFT OUTER JOIN product t2 -- 商品表 ON t1.`id` = t2.`cid`; 查询所有的商品信息。商品分类的编号、分类名称。分类下的商品名称 /* 分析：商品分类的编号、分类名称 category表分类下的商品名称 product表条件：category.id = product.cid 查询所有的商品信息，使用右外连接 */ SELECT t1.`id`, -- 分类编号 t1.`name`, -- 分类名称 t2.`name` -- 商品名称 FROM category t1 -- 商品分类表 RIGHT OUTER JOIN product t2 -- 商品表 ON t1.`id` = t2.`cid`; 查询所有的用户和所有的商品。显示用户的编号、姓名、年龄。商品名称 /* 分析：用户的编号、姓名、年龄 user表商品名称 product表中间表 us_pro 条件：us_pro.uid = user.id AND us_pro.pid = product.id */ SELECT t1.`id`, -- 用户编号 t1.`name`, -- 用户名称 t1.`age`, -- 用户年龄 t2.`name` -- 商品名称 FROM USER t1, -- 用户表 product t2, -- 商品表 us_pro t3 -- 中间表 WHERE t3.`uid` = t1.`id` AND t3.`pid` = t2.`id`; 查询张三和李四这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称 /* 分析：用户的编号、姓名、年龄 user表商品名称 product表中间表 us_pro 条件：us_pro.uid = user.id AND us_pro.pid = product.id AND user.name IN ('张三','李四') */ SELECT t1.`id`, -- 用户编号 t1.`name`, -- 用户名称 t1.`age`, -- 用户年龄 t2.`name` -- 商品名称 FROM USER t1, -- 用户表 product t2, -- 商品表 us_pro t3 -- 中间表 WHERE (t3.`uid` = t1.`id` AND t3.`pid` = t2.`id`) AND -- (t1.`name` = '张三' or t1.`name` = '李四'); t1.`name` IN ('张三','李四');

7.多表查询-自关联查询

自关联查询介绍同一张表中有数据关联。可以多次查询这同一个表！自关联查询演示 -- 创建员工表 CREATE TABLE employee( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), mgr INT, salary DOUBLE ); -- 添加数据 INSERT INTO employee VALUES (1001,'孙悟空',1005,9000.00), (1002,'猪八戒',1005,8000.00), (1003,'沙和尚',1005,8500.00), (1004,'小白龙',1005,7900.00), (1005,'唐僧',NULL,15000.00), (1006,'武松',1009,7600.00), (1007,'李逵',1009,7400.00), (1008,'林冲',1009,8100.00), (1009,'宋江',NULL,16000.00); -- 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询 /* 分析：员工姓名 employee表直接上级姓名 employee表条件：employee.mgr = employee.id 查询左表的全部数据，和左右两张表交集部分数据，使用左外连接 */ SELECT t1.name, -- 员工姓名 t1.mgr, -- 上级编号 t2.id, -- 员工编号 t2.name -- 员工姓名 FROM employee t1 -- 员工表 LEFT OUTER JOIN employee t2 -- 员工表 ON t1.mgr = t2.id;

四、视图

1.视图的概念

视图是一种虚拟存在的数据表这个虚拟的表并不在数据库中实际存在作用是将一些比较复杂的查询语句的结果，封装到一个虚拟表中。后期再有相同复杂查询时，直接查询这张虚拟表即可说白了，视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上

2.视图的好处

简单对于使用视图的用户不需要关心表的结构、关联条件和筛选条件。因为这张虚拟表中保存的就是已经过滤好条件的结果集安全视图可以设置权限 , 致使访问视图的用户只能访问他们被允许查询的结果集数据独立一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

3.视图数据准备

-- 创建db7数据库 CREATE DATABASE db7; -- 使用db7数据库 USE db7; -- 创建country表 CREATE TABLE country( id INT PRIMARY KEY AUTO_INCREMENT, country_name VARCHAR(30) ); -- 添加数据 INSERT INTO country VALUES (NULL,'中国'),(NULL,'美国'),(NULL,'俄罗斯'); -- 创建city表 CREATE TABLE city( id INT PRIMARY KEY AUTO_INCREMENT, city_name VARCHAR(30), cid INT, -- 外键列。关联country表的主键列id CONSTRAINT cc_fk1 FOREIGN KEY (cid) REFERENCES country(id) ); -- 添加数据 INSERT INTO city VALUES (NULL,'北京',1),(NULL,'上海',1),(NULL,'纽约',2),(NULL,'莫斯科',3);

4.视图的创建

创建视图语法 -- 标准语法 CREATE VIEW 视图名称 [(列名列表)] AS 查询语句; 普通多表查询，查询城市和所属国家 -- 普通多表查询，查询城市和所属国家 SELECT t1.*, t2.country_name FROM city t1, country t2 WHERE t1.cid = t2.id; -- 经常需要查询这样的数据，就可以创建一个视图创建视图基本演示 -- 创建一个视图。将查询出来的结果保存到这张虚拟表中 CREATE VIEW city_country AS SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id; 创建视图并指定列名基本演示 -- 创建一个视图，指定列名。将查询出来的结果保存到这张虚拟表中 CREATE VIEW city_country2 (city_id,city_name,cid,country_name) AS SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

5.视图的查询

查询视图语法 -- 标准语法 SELECT * FROM 视图名称; 查询视图基本演示 -- 查询视图。查询这张虚拟表，就等效于查询城市和所属国家 SELECT * FROM city_country; -- 查询指定列名的视图 SELECT * FROM city_country2; -- 查询所有数据表，视图也会查询出来 SHOW TABLES; 查询视图创建语法 -- 标准语法 SHOW CREATE VIEW 视图名称; 查询视图创建语句基本演示 SHOW CREATE VIEW city_country;

6.视图的修改

修改视图表中的数据 -- 标准语法 UPDATE 视图名称 SET 列名=值 WHERE 条件; -- 修改视图表中的城市名称北京为北京市 UPDATE city_country SET city_name='北京市' WHERE city_name='北京'; -- 查询视图 SELECT * FROM city_country; -- 查询city表,北京也修改为了北京市 SELECT * FROM city; -- 注意：视图表数据修改，会自动修改源表中的数据修改视图表结构 -- 标准语法 ALTER VIEW 视图名称 [(列名列表)] AS 查询语句; -- 查询视图2 SELECT * FROM city_country2; -- 修改视图2的列名city_id为id ALTER VIEW city_country2 (id,city_name,cid,country_name) AS SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

7.视图的删除

删除视图 -- 标准语法 DROP VIEW [IF EXISTS] 视图名称; -- 删除视图 DROP VIEW city_country; -- 删除视图2，如果存在则删除 DROP VIEW IF EXISTS city_country2;

8.视图的总结

视图是一种虚拟存在的数据表这个虚拟的表并不在数据库中实际存在说白了，视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上视图的好处简单安全数据独立

五、备份与还原

dos命令行【了解】

1. 备份格式： mysqldump -u用户名 -p 需要备份数据库名 > 导出路径(*.sql) 实例： mysqldump -uroot -p day20_pro > d:bak.sql 缺点：通过命令备份的只有表结构和数据，没有建库语句... 2. 还原格式： mysql -u用户名 -p < 导入路径(*.sql) 实例： mysql -uroot -p < d:bak.sql

图形化工具【sqlyog】

六用户权限 DCL【了解】

我们进入公司后，有专门的DBA（数据库管理员），为你创建一个新用户，授予一定的权限… 1. 创建用户语法： create user '用户名'@'主机名' identified by '密码'; 注意：主机名：限定客户端登录ip 指定ip：127.0.0.1 （localhost）任意ip：% 2. 授权用户语法： grant 权限1,权限2... on 数据库名.表名 to '用户名'@'主机名'; 注意：权限： select、insert、delete、update、create... all 所有权限数据库名.* 指定库下面所有的表 3. 查看权限语法： show grants for '用户名'@'主机名'; 4. 撤销授权语法： revoke 权限1,权限2... on 数据库名.表名 from '用户名'@'主机名'; 注意：权限： select、insert、delete、update、create... all 所有权限数据库名.* 指定库下面所有的表 5. 删除用户语法： drop user '用户名'@'主机名'; 6. 密码管理 1.超级管理员 set password for '用户名'@'主机名'=password('新密码'); 2.普通用户 set password=password('新密码'); -- 创建用户 CREATE USER 'tom'@'%' IDENTIFIED BY '123'; -- 授权 GRANT SELECT ON day20.dept TO 'tom'@'%' ; -- 查看权限 SHOW GRANTS FOR 'tom'@'%' ; -- 撤销权限 REVOKE SELECT ON day20.dept FROM 'tom'@'%' ; -- 密码管理 -- 加密函数 SELECT PASSWORD('123'); -- 超级管理帮你找回密码 SET PASSWORD FOR 'tom'@'%'= PASSWORD('999'); -- 删除用户 DROP USER 'tom'@'%' ;

七总结

约束:防止非法数据进入到表中,确保数据的正确性和一致性; 单表 PRIMARY KEY auto_inrement 主键自增唯一 UNIQUE 唯一约束 NOT NULL 非空约束多用于创建的时候 CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(30), age INT ); 多表 CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名) 多表操作一对一:在任意一个表建立外键，去关联另外一个表的主键。一对多:在多的一方，建立外键约束，关联一的一方主键。多对多:需要借助第三张中间表，中间表至少包含两个列，作为外键分别关联两张多方表的主键多表查询优先使用连接查询，然后考虑子查询,吃内存内连接是交集部分,如果某个用户没订单,该用户将查询不到显式内连接：SELECT * FROM t1 JOIN t2 ON 主外键对等 [ JOIN t3 ON 主外键对等] WHERE 其他条件; 隐式内连接：SELECT * FROM t1,t2,... WHERE 主外键对等外链接查询的结果是主表的全部 + 其他表的交集部分。左外左为主，右外右为主。左外：SELECT * FROM t1 LEFT JION t2 ON 主外键对等 [ LEFT JOIN t3 ON 主外键对等 ] WHERE 其他条件视图封装复杂查询语句的结果,简化查询代码的编写.修改视图数据后,数据源也会修改!! CREATE VIEW 视图名称 [(列名列表)] AS 查询语句; 如果指定了列名，视图列和查询语句的结果集列匹配数据库备份登录到 MySQL 服务器，输入： mysqldump –u root –p 数据库名称 > 文件保存路径登录Mysql服务：source 备份文件全路径。

对于mysql中一些基础概念不是很懂,自己整理了下.如有问题,望大佬指出

CREATE TABLE student 确定表名 id,name,age 确定字段名称 INT VARCHAR(20) 确定字段类型 PRIMARY KEY auto_increment,not null,UNIQUE 确定字段属性 PRIMARY KEY auto_increment 确定主键

字段确定好后,在第一行规定了下面的字段是什么类型和属性,数据一行一行插入,字段的值叫数据查询时按列查询

还有一种迁移理解,把每张表看成一个类,类中有属性(成员变量),表中有一个个字段,对应一列一列,每个对象可以创建成员变量,赋值.每个表有N条记录,每个记录可以针对自己的字段去赋值 N条记录赋值的时候是一行一行赋值的中间又有很多个字段,每一个字段又有对应的字段属性和字段类型(就像对象一样),

Processed: 0.029, SQL: 9

WebDay17 MySQL 约束 多表查询 视图 数据备份与还原

MySQL 约束 多表查询 视图 数据备份与还原

一 约束