MySQL 多表查询--笛卡尔积--外键约束--内连接查询--自连接查询--外连接查询--DML操作--数据备份和恢复
MySQL 多表查询--笛卡尔积--外键约束--内连接查询--自连接查询--外连接查询--DML操作--数据备份和恢复
1.多表查询
通过一条SQL语句得到两张表不同的信息。这里就需要用到多表查询
单表查询语法：
SELECT<selectList>
FROM table_name
多表查询语法：
SELECT<selectList>
FROM table_nameA，table_nameB(表名A，表名B)
以下为三张表测试数据
1: 商品分类表,dir_id：是分类的编号
2: dirName:分类的名称，parent_id：分类所属的父分类
3: 商品的库存表：product_id（商品id）storeNum(库存数量),lastIncomeDate(最后的入库时间),lastOutcomeDate(最后的出库时间),warningNum(警戒值,当商品达到一定值的时候需要进货)
2.笛卡尔积
需求：查询分类为2的商品总数
SELECT count(*)FROM product WHERE dir_id=2
查询所有的的货品信息+对应的货品分类信息
笛卡尔积现象（有84条数据）
SELECT *FROM product,productdir
需求：查询所有的货品信息+ 对应的货品分类信息(20条数据)
SELECT *FROM product,productdir WHERE product.dir_id=productdir.id
笛卡尔积：两张表，A表和B表当中，所有数据它的组合关系就称之为笛卡尔积现象。
解决方案：在WHERE加入有效的连接条件----->等值连接
注意了：如果是n张表该如何处理，n张表 至少需要 n-1个连接条件。
3.外键约束
外键约束：FOREIGN KEY,A表中的外键列。A表中的外键列的值必须参照于B表中的某一列(B表主键)。
CONSTRAINT 'product_ibfk_1' FOREIGN KEY('dir_id')REFERENCES 'productdir'('id')
注意了：在MySQL中，InnoDB中支持事务和外键。修改表的存储引擎为InnoDB;ALTER TABLE 表蒙 ENGINE='InnoDB';
4.内连接查询
需求：查询所有商品的名称和分类名称：
SELECT p.productName,pd.dirName from produce p,productdir pd WHERE p.dir_id=pd.id
内连接查询：不满足条件的都查不出来。
内连接查询：是相对于外连接。
内连接分为：隐是内连接，显示内连接(推荐)，其查询效果相同，仅仅只是语法不同而已：
隐式内连接：
SELECT <selectList>
FROM A,B WHERE A.列=B.列
显示内连接(推荐写法)：[INNER] JOIN ON
SELECT <selectList>
FROM A[INNER] JOIN B ON A.列=B.列
需求：查询所有商品的名称和分类名称：
隐式：
SELECT p.productName,pd.dirName from product p,productdir pd where p.dir_id=pd.id
显示：
SELECT p.productName,pd.dirName from product p INNER JOIN productdir pd ON p.dir_id=pd.id
在做等值连接的时候，若A表中和B表中的列名相同。则可以简写：
SELECT <selectList>
FROM A[INNER]JOIN B USING(同名的列)
前提：在emp和dept表中都有deptno列。并且是外键关系。
SELECT *FROM emp JOIN dept USING(deptno)
需求：查询零售界大于200的无线鼠标
隐式：
SELECT *
FROM product p,prodctdir pd
WHERE p.dir_id=pd.id AND p.sa;ePrice>200 AND pd.dirName='无线鼠标'
显示：
SELECT *
FROM product p join productdir pd ON p.dir_id=pd.id
WHERE p.salePrice>200 AND pd.dirName='无线鼠标'
需求：查询零售价大于200的无线鼠标(使用表的别名)
需求：查询每个货品对应的分类以及对应的库存
SELECT *FROM product p,productdir pd,productstock ps
WHERE P.dir_id=pd.id AND p.id=ps.product_id
需求：如果库存货品都销售完成，按照利润从高到低查询货品名称，零售价，货品分类（三张表）
SELECT *,(p.salePrice-p.costPprice)*ps.storeNum lr FROM product p
join productdir pd ON p.dir_id=pd. id
JOIN productstock ps ON p.id=ps.product_id
ORDER BY lr DESC
不带别名出现的异常，不清楚id来自那张表。id重复 了就会报ambiguous
SELECT *,(p.salePrice-p.costPprice)*ps.storeNum lr FROM product p
join productdir pd ON p.dir_id=id
JOIN productstock ps ON p.id=ps.product_id
ORDER BY lr DESC
SELECT productName,(p.salePrice-p.costPprice)*ps.storeNum lr FROM product p
join productdir pd ON p.dir_id=id
JOIN productstock ps ON p.id=ps.product_id
ORDER BY lr DESC
5.自连接查询
自连接的用处在于，你需要的数据在一张表里面。当是又不能通过一张表中得到数据，此时就可以把它看出两张表来。
需求：查询商品分类的名称和父分类的名称
SELECT * FROM productdir child,productdir parent
WHERE child.parent_id=parent.id
6.外连接查询
左 外连接：查询出JOIN左边表的全部数据查询出啦，JOIN右边的表不匹配的数据使用NULL来填充数据。
右 外连接：查询出JOIN右边表的全部数据查询出啦，JOIN左边的表不匹配的数据使用NULL来填充数据。
语法格式：
SELECT <selectList>
FROM A LEFT/RIGHT[OUTER]JOIN B
ON (A.column_name=B.column_name)
查询所有的商品信息和对应的分类信息。
左外连接：
select *from product p LEFT OUTER JOIN productdir pd ON p.dir_id=pd.id
右外连接：
select *from product p right OUTER JOIN productdir pd ON p.dir_id=pd.id
7.DML操作（增删改）
插入语句：一次插入操作只插入一行。
INSERT INTO table_name(column1,column2, column3....) VALUES (value1,value2,value3);
>在商品分类表中插入一条数据
>INSERT INTO productdir(id,dirName,parent_id)VALUES(5,'机械鼠标',1) //插入的值的顺序不能变
>INSERT INTO productdir(id,dirName)VALUES(null,'机械鼠标')
>一条SQL插入多条语句
>INSERT INTO productdir(id,dirName)VALUES(null,'机械鼠标'),
> (null,'机械鼠标'),
> (null,'机械鼠标'),
> (null,'机械鼠标'),
> (null,'机械鼠标')
更新语句：
UPDATE table_name
SET columnName1=value1,columnName2=value2,...
[WHERE condition]；
如果省略了where子句，则全表的数据都会被修改。注意：没有FROM
//修改单列
UPDATE product SET productName='罗技001' WHERE id=23
修改多列
UPDATE product SET productName='罗技001'，dir_id WHERE id=23
需求：将零售价大于300的货品零售价上调0.2倍
UPDATE product SET salePrice=salePrice*1.2 WHERE salePrice>300
需求：将零售价大于300的有线鼠标的货品零售价上调0.1倍
UPDATE produce p,productdir pd
SET salePrice=salePrice*1.1
WHERE p.dir_id=pd.id AND p.salePrice>300 AND pd.dirName='有线鼠标'
删除语句：
DELETE FROM table_name
[WHERE condition];
如果省略了where子句，则全部的数据都会被修改
8.数据备份和恢复
8.1:MySQL自身的数据库的维护：
MySQL自带的命 令
8.2:Navicat工具的导入和导出：
8.3Navicat工具的备份和还原：生成的是psc文件
