B027-MySQL增强

多表查询

为什么要用多表查询？

可以减少单表里面的字段 ，可以很灵活的查询相要的数据。

笛卡尔积和内连接消除笛卡尔积

-- 迪卡尔积
  就是两张表组合查询时，一张表的一行数据会和另一张表的每一行都组合一下
-- 示例 
  SELECT * from product,productdir; 
-- 消除迪卡尔积  
-- 内链接
  SELECT * from product,productdir where product.dir_id =productdir.id  -- 隐式内链接
  SELECT * from product INNER JOIN productdir ON product.dir_id =productdir.id -- 显示内链接
  SELECT * from product  JOIN productdir ON product.dir_id =productdir.id -- 显示内链接 写法2

外键

含义：A表中的某一列参考B表中的某一列(id)

外键在程序中的体现：

CASCADE：父表delete、update的时候，子表会delete、update掉关联记录；
SET NULL：父表delete、update的时候，子表会将关联记录的外键字段所在列设为null，所以注意在设计子表时外键不能设为not null；
RESTRICT：如果想要删除父表的记录时，而在子表中有关联该父表的记录，则不允许删除父表中的记录；
NO ACTION：同 RESTRICT，也是首先检查外键；

tips：MyISAM不支持外键，InnoDB才支持外键

数据库内连接练习

  -- 查询货品id，货品名称，货品所属分类名称
  SELECT product.*,productdir.dirName FROM product  INNER JOIN productdir ON product.dir_id=productdir.id
  -- 查询零售价大于200的无线鼠标
  SELECT * FROM product AS p INNER JOIN productdir AS pd  ON p.dir_id=pd.id AND p.salePrice>200 AND pd.dirName='无线鼠标'
  -- 查询零售价大于200的无线鼠标(使用表的别名)
  SELECT * FROM product AS p INNER JOIN productdir AS pd  ON p.dir_id=pd.id AND p.salePrice>200 AND pd.dirName='无线鼠标'
  -- 查询每个货品对应的分类以及对应的库存
  SELECT p.*, pd.dirName, ps.store_num FROM product p INNER JOIN productdir  pd INNER JOIN product_stock ps ON p.dir_id=pd.id AND p.id=ps.product_id
  -- 如果库存货品都销售完成,按照利润从高到低查询货品名称,零售价,货品分类（三张表）.
	SELECT
		p.productName,
		p.salePrice,
		pd.dirName,
	(p.salePrice - p.costPrice) * ps.store_num AS lr
	FROM
		product p
	INNER JOIN productdir pd
	INNER JOIN product_stock ps ON p.dir_id = pd.id
	AND p.id = ps.product_id ORDER BY lr DESC

左连接查询和右连接查询

-- 外链接
  -- 左连接  LEFT JOIN
  SELECT * FROM employee INNER JOIN department ON employee.deptid=department.id;  -- 内连接
  SELECT * FROM employee LEFT  JOIN department ON employee.deptid=department.id;  -- 左连接
  -- 右连接  RIGHT JOIN 
  SELECT * FROM employee right  JOIN department ON employee.deptid=department.id; -- 右链接

-- 总结  内连接 左连接 外连接 的区别
  内连接  多表连表查询时, 只显示他们数据有关联的部分
  左连接  多表左连接查询时,  按照左表为标准,显示左边所有的数据和右表中与左表有关系的数据
  右连接  多表右连接查询时,  按照右表为标准,显示右边所有的数据和左表中与右表有关系的数据

等值连接

  -- 等值连接  (A表的外键名 和B表的外键名一致)  USING
  SELECT * FROM employee JOIN department ON employee.deptid=department.deptid; -- 原来的写法
  SELECT * FROM employee JOIN department USING (deptid)

out join

自连接

业务场景：

  -- 自连接
  SELECT p2.*, p.dirName FROM productdir AS p JOIN productdir AS p2 ON  p.id=p2.parent_id ;

子查询

  -- 查询零售价比罗技MX1100更高的所有商品信息。
  SELECT * from product  where  salePrice > (SELECT salePrice FROM product WHERE productName='罗技MX1100')
  -- 单行多列子查询
  需求: 查询分类编号和折扣与罗技M100相同的所有商品信息。
  SELECT * FROM product
  WHERE (dir_id,cutoff)=(
	  SELECT dir_id,cutoff FROM product WHERE productName = '罗技M100'
  )
  -- 我们真实开发中要避免过多连表查询

数据操作(DML)

数据的插入

-- 增加 
  INSERT into 表名(列名1，列名2，...) values(值1,值2,.....) -- 写法一
  INSERT INTO employee(id,name,deptid) VALUES(6,'明锋',3); 
  INSERT INTO employee VALUES(7,'明锋',3); -- 写法二  如果我里面每一个字段都有值表后面可以省略字段 
  INSERT INTO employee(name,deptid) VALUES('明锋',3);  -- 写法三  如果列满可以省略前面的列名
-- 批量增加 
  INSERT INTO employee(name,deptid) VALUES('明锋',3),('xxx',4),('xxx2',5); 
  INSERT INTO employee (SELECT * FROM employee)  -- 把当前表的数据查询出来再插入进当前表
  INSERT INTO employee(name,deptid) (SELECT name,deptid FROM employee)  -- 把当前表的数据查询出来再插入进当前表

数据的删除

-- 删除数据
  DELETE FROM 表名  WHERE id = ?  -- 删除一条数据
  DELETE FROM 表名   -- 删除全部数据
 
-- 删除多条数据
  DELETE from employee WHERE id in(2,3); -- 这种写法
  DELETE from employee where id BETWEEN 4 AND 9
  DELETE from employee where id <= 12

数据的修改

   UPDATE 表名 SET 列名=值 , 列名2=值  -- 修改全部
   UPDATE 表名  SET 列名=值 , 列名2=值  where id=？ -- 按id来修改

   UPDATE employee SET name='小困困',deptid=3 WHERE id=8180
   UPDATE employee SET name='阮经天',deptid=3
   
   -- 指定id批量修改  -- 自己去研究（难）
   UPDATE employee SET name='小困困',deptid=3 WHERE id in (8180,8181,8182) -- 修改指定的id的内容
   -- 这里你们要求研究一下   扩展  -- 特难 

数据库的备份与恢复

Dos命令行窗口导出

在dos命令行窗口进行，若操作系统版本高，则使用管理员模式 --> 检索栏输入cmd - 右键以管理员身份运行

mysqldump -u账户 -p密码 数据库名称>脚本文件存储地
mysqldump -uroot -proot test1> D:\data\test.sql

Dos命令行窗口导入

速度最快(用的批量插入)，导入前可先清空数据库中的表

mysql -u账户 -p密码 数据库名称< 脚本文件存储地址
mysql -uroot -proot test1< D:\data\test.sql

Navicat导出

Navicat中右键数据库 - 转储sql文件

Navicat导入

拖拽SQL文件到navicat数据库里，速度慢(一行一行插入)

函数

  -- cast
  SELECT CAST('1234.8678' AS SIGNED) --转 整型，不会四舍五入
  SELECT  CAST(deptid AS SIGNED) AS id,deptName FROM department  -- 把字符串转成整形(deptid在这里的数据库中类型定义为varchar)
  SELECT CAST('1234.8678' AS decimal)  -- 装decimal，会四舍五入
  SELECT CAST('1234.8678' AS decimal(6,2))  -- 装decimal  第一个数字:保留数字长度，第二个数字:保留小数长度

  --  ifnull
  SELECT id,productName,dir_id,IFNULL(salePrice,0) AS salePrice FROM product	-- 如果salePrice为null，赋值0 ，防止部分不能接受null值得类型收到null报错

查漏补缺

数据库访问控制

视图

即由sql构成的临时表，用sql来创建视图

SELECT salePrice FROM product WHERE productName='罗技MX1100'
SELECT * from product  where  salePrice > (SELECT * from haha)

三范式

1.列的原子性：列不可分割
数据库表中的所有字段值都应该是不可分解的原子值

2.主键相关：表中的每列都和主键相关

如订单商品用联合主键做一张表应该换成分开的两张表

3.主键直接相关，不是间接相关：

我的表不应该有别的表的数据，别的表的数据应该通过外键来关联
A表里面的字段不能拥有B表里面的数据，数据不能够重复
如下面右表虽然希望展示deptName信息，但不该有deptName字段数据，应该通过关联查询推导出来。