mysql学习手册
约束
外键约束
为什么要有外键约束？
在我们以后开发项目的过程中，操作的肯定不止一张表数据，所以当表与表之间的数据有相关联性的时候，如果没有相关的数据约束，则无法保证数据的准确性！
外键约束的作用
让表和表之间产生关系，从而保证数据的准确性！
建表时添加外键约束
CREATE TABLE 表名( 列名1 数据类型1, 列名2 数据类型2, .... 列名n 数据类型n CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名) );
建表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
外键的级联更新和级联删除
什么是级联更新和级联删除？
如图：比如我有一张用户表，有一张订单表，订单表关联着用户表，一个用户可以有好几个订单，如果我要把用户表的某个用户的信息删掉或者更新，我也希望与该用户关联的数据表的数据会也随着删除或者更新。所以这就是及联删除和及联更新。
添加外键约束，同时添加级联更新
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;
提示：外键名你是可以根据自己的情况随便取的。
多表设计
多表的概念
说白了就是多张表数据，而表与表直接是有一定的关联关系，这种关联关系是通过外键约束实现。
一对一
应用场景：
人和身份证。一个人只有一个身份证，一个身份证只能对应一个人！
建表原则：
在任意一个表建立外键，去关联另外一个表的主键
图解：
一对多
应用场景：
用户和订单。一个用户可以有多个订单！商品分类和商品。一个分类下可以有多个商品！
建表原则：
在多的一方，建立外键约束，来关联一的一方主键
图解：
多对多
应用场景：
学生和课程。一个学生可以选择多个课程，一个课程也可以被多个学生选择！
建表原则：
需要借助第三张表中间表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键
图解：
多表查询
内连接查询
查询原理：
内连接查询的是两张表有交集的部分数据(有主外键关联的数据)
显式内连接查询语法：
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 条件;
隐式内连接查询语法：
SELECT 列名 FROM 表名1,表名2 WHERE 条件;
多表查询-外连接查询
左外连接
查询原理：
查询左表的全部数据，和左右两张表有交集部分的数据
左外连接查询语法：
SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;
右外连接
查询原理：
查询右表的全部数据，和左右两张表有交集部分的数据右外连接查询语法：
SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;
多表查询-子查询
什么是子查询？
查询语句中嵌套了查询语句。我们就将嵌套查询称为子查询！
自查询的三种结果：
1、结果是单行单列的可以作为条件，使用运算符进行判断！
语法：
SELECT 列名 FROM 表名 WHERE 列名=(SELECT 聚合函数(列名) FROM 表名 [WHERE 条件]);
2、结果是多行单列的可以作为条件，使用运算符in或not in进行判断！
语法：
SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]);
3、结果是多行多列的可以作为一张虚拟表参与查询！
语法：
SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];
语法提示：带[ ]表示可要可不要。
多表查询练习
为了巩固大家对查询语句的掌握，还是需要多多联系
建表语句：
-- 创建user表 CREATE TABLE USER( id INT PRIMARY KEY AUTO_INCREMENT, -- 用户id NAME VARCHAR(20), -- 用户姓名 age INT -- 用户年龄 ); -- 添加数据 INSERT INTO USER VALUES (1,'aa',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,'javaxuexi001',1); INSERT INTO orderlist VALUES (2,'javaxuexi002',1); INSERT INTO orderlist VALUES (3,'javaxuexi003',2); INSERT INTO orderlist VALUES (4,'javaxuexi004',2); INSERT INTO orderlist VALUES (5,'javaxuexi005',3); INSERT INTO orderlist VALUES (6,'javaxuexi006',3); INSERT INTO orderlist VALUES (7,'javaxuexi007',NULL); INSERT INTO orderlist VALUES (7,'javaxuexi008',NULL); INSERT INTO orderlist VALUES (7,'javaxuexi009',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);
多表查询语句练习
1.查询用户的编号、姓名、年龄。订单编号
/* 分析 用户的编号、姓名、年龄 user表 订单编号 orderlist表 条件：user.id=orderlist.uid */ SELECT u.id, u.name, u.age, o.number FROM USER u, orderlist o WHERE u.id=o.uid;
2.查询所有的用户。用户的编号、姓名、年龄。订单编号
/* 分析 用户的编号、姓名、年龄 user表 订单编号 orderlist表 条件：user.id=orderlist.uid 查询所有的用户，左外连接 */ SELECT u.id, u.name, u.age, o.number FROM USER u LEFT OUTER JOIN orderlist o ON u.id=o.uid;
3.查询所有的订单。用户的编号、姓名、年龄。订单编号
/* 分析 用户的编号、姓名、年龄 user表 订单编号 orderlist表 条件：user.id=orderlist.uid 查询所有的订单，右外连接 */ SELECT u.id, u.name, u.age, o.number FROM USER u RIGHT OUTER JOIN orderlist o ON u.id=o.uid;
4.查询用户年龄大于21岁的信息。显示用户的编号、姓名、年龄。订单编号
/* 分析 用户的编号、姓名、年龄 user表 订单编号 orderlist表 条件：user.id=orderlist.uid AND user.age > 23 */ SELECT u.id, u.name, u.age, o.number FROM USER u, orderlist o WHERE u.id=o.uid AND u.age > 23;
5.查阿牛和阿花用户的信息。显示用户的编号、姓名、年龄。订单编号
/* 分析 用户的编号、姓名、年龄 user表 订单编号 orderlist表 条件：user.id=orderlist.uid AND user.name IN ('阿牛','阿花') */ SELECT u.id, u.name, u.age, o.number FROM USER u, orderlist o WHERE u.id=o.uid AND u.name IN ('阿牛','阿花');
6.查询商品分类的编号、分类名称。分类下的商品名称
/* 分析 商品分类的编号、分类名称 category表 商品名称 product表 条件：category.id=product.cid */ SELECT c.id, c.name, p.name FROM category c, product p WHERE c.id=p.cid;
7.查询所有的商品分类。商品分类的编号、分类名称。分类下的商品名称
/* 分析 商品分类的编号、分类名称 category表 商品名称 product表 条件：category.id=product.cid 查询所有的商品分类，左外连接 */ SELECT c.id, c.name, p.name FROM category c LEFT OUTER JOIN product p ON c.id=p.cid;
8.查询所有的商品信息。商品分类的编号、分类名称。分类下的商品名称
/* 分析 商品分类的编号、分类名称 category表 商品名称 product表 条件：category.id=product.cid 查询所有的商品信息，右外连接 */ SELECT c.id, c.name, p.name FROM category c RIGHT OUTER JOIN product p ON c.id=p.cid;
9.查询所有的用户和该用户能查看的所有的商品。显示用户的编号、姓名、年龄。商品名称
-- 9.查询所有的用户和该用户能查看的所有的商品。显示用户的编号、姓名、年龄。商品名称 /* 分析 用户的编号、姓名、年龄 user表 商品名称 product表 中间表 us_pro 条件：us_pro.uid=user.id AND us_pro.pid=product.id */ SELECT u.id, u.name, u.age, p.name FROM USER u, product p, us_pro up WHERE up.uid=u.id AND up.pid=p.id;
10.查询阿牛和小明这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称
/* 分析 用户的编号、姓名、年龄 user表 商品名称 product表 中间表 us_pro 条件：us_pro.uid=user.id AND us_pro.pid=product.id AND user.name IN ('阿牛','小明') */ SELECT u.id, u.name, u.age, p.name FROM USER u, product p, us_pro up WHERE up.uid=u.id AND up.pid=p.id AND u.name IN ('阿牛','小明');
视图
视图的概念
1、视图是一种虚拟存在的数据表
2、这个虚拟的表并不在数据库中实际存在
3、作用是将一些比较复杂的查询语句的结果，封装到一个虚拟表中。后期再有相同复杂查询时，直接查询这张虚拟表即可
4、说白了，视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上
视图的好处
简单
对于使用视图的用户不需要关心表的结构、关联条件和筛选条件。因为这张虚拟表中保存的就是已经过滤好条件的结果集
安全
视图可以设置权限 , 致使访问视图的用户只能访问他们被允许查询的结果集
数据独立
一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响
视图的创建
CREATE VIEW 视图名称 [(列名列表)] AS 查询语句;
视图的查询
查询视图中的数据
SELECT * FROM 视图名称;
查询视图创建语法
SHOW CREATE VIEW 视图名称;
视图的修改
修改视图表中的数据
UPDATE 视图名称 SET 列名=值 WHERE 条件;
注意：修改视图中的数据，源数据表中的数据也会随着修改。
修改视图表结构
ALTER VIEW 视图名称 [(列名列表)] AS 查询语句;
视图的删除
DROP VIEW [IF EXISTS] 视图名称;
备份与还原
命令方式的备份（以你服务器的Linux为例）：
1、先远程连接你的linux系统2、使用SecureCRT工具连接到Linux系统3、输入：mysqldump -u root -p 数据库名称 > 文件保存路径\数据库名称.sql4、输入数据库的密码即可操作命令：mysqldump -u root -p blog > /Users/javaxuexi/Desktop/blog.sql
命令方式还原备份的数据库
1、需要先登录数据库mysql -u root -p2、创建一个新的数据库create database java;3、使用该数据库use java;4、导入w文件执行还原数据库:source 备份文件的路径source /Users/javaxuexi/Desktop/blog.sql
2、图形界面方式的备份和还原
你可以通过navicat或者sqlyog工具进行备份或者还原，具体操作我这里就不操作了 。。
