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 数据库名称 > 文件保存路径数据库名称.sql

4、输入数据库的密码即可操作命令：mysqldump -u root -p blog > /Users/javaxuexi/Desktop/blog.sql

命令方式还原备份的数据库

1、需要先登录数据库mysql -u root -p

2、创建一个新的数据库create database java;

3、使用该数据库use java;

4、导入w文件执行还原数据库:source 备份文件的路径source /Users/javaxuexi/Desktop/blog.sql

2、图形界面方式的备份和还原

你可以通过navicat或者sqlyog工具进行备份或者还原，具体操作我这里就不操作了 。。

MySQL存储过程和函数

复习mysql的存储过程，看这个就够了 

MySQL触发器

复习mysql的触发器，看这个就够了