A027_MySQL进阶

1 内容介绍

1. 多表查询[常用]
2. 数据操作（DML）[常用]
3. 数据库的备份与恢复[掌握]
4. 函数[了解]

2 多表查询

2.1 迪卡尔积

1. 没有连接条件的表关系返回的结果（表之间数据组合的结果）。
2. 多表查询会产生笛卡尔积:
   假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}
3. 实际运行环境下，应避免使用全笛卡尔集。
4. 我们应该怎么去避免笛卡尔集？
   在WHERE加入有效的连接条件---->等值连接
   图
   

需求:查询所有的货品信息+对应的货品分类信息
注意: 连接 n张表,至少需要 n-1个连接条件。

2.2 外键约束

主键约束(PRIMARY KEY): 约束在当前表中,指定列的值非空且唯一.
外键约束(FOREIGN KEY): A表中的外键列. A表中的外键列的值必须参照于B表中的某一列(B表主键).

注意：

1. 在MySQL中,InnoDB支持事务和外键.
   修改表的存储引擎为InnoDB:
   ALTER TABLE 表名 ENGINE=‘InnoDB’;
2. 现有有些系统设计就是不用外键

2.3 内连接查询(显式与隐式)

内连接查询：是相对于外连接。
内连接分为：隐式内连接、显示内连接，其查询效果相同。

---

隐式内连接：
SELECT
FROM A ,B WHERE A.列 = B.列

---

显示内连接(推荐写法)：.
SELECT
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
显示内连接: SELECT p.productName,pd.dirName FROM product p JOIN productdir pd ON p.dir_id = pd.id

---

在做等值连接的时候,若A表中和B表中的列名相对. 则可以简写:
SELECT
FROM A [INNER] JOIN B USING (同名的列)----AB表相同的字段名 同名的那个列,做等值连接
前提:在emp和dept表中都有deptno列. 并且是外键关系。
如: SELECT * FROM emp JOIN dept USING (deptno)

---

使用表名前缀在多个表中区分相同的列。
在不同表中具有相同列名的列可以用表的别名加以区分。
使用别名可以简化查询。
使用表名前缀可以提高执行效率。
如果使用了表的别名，则不能再使用表的真名

---

需求:
查询货品id，货品名称，货品所属分类名称
查询零售价大于200的无线鼠标
查询零售价大于200的无线鼠标(使用表的别名)
查询每个货品对应的分类以及对应的库存
如果库存货品都销售完成,按照利润从高到低查询货品名称,零售价,货品分类（三张表）.

2.4 外连接

外连接查询:
左外连接:查询出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 JOIN productdir pd ON p.dir_id = pd.id
右连接:
	SELECT * FROM product p RIGHT JOIN productdir pd ON p.dir_id = pd.id
思考：查询每种商品分类的名称和包含的商品库存总数：
注意:这里有一个ifnull

---

老板希望：查询所有的员工
select * from Employee emp  inner join Depatment dep  on emp.dept_id=dep.id
发现：由于大神意：部门空缺，查不到大神意这个员工 

2.5 自连接查询

自连接查询:
  把一张表看成两张来做查询.一定要取别名...
需求: 查询每个商品分类的名称和父分类名称（所属分类的名称）:

隐式内连接：
SELECT sub.dirName,super.dirName
		FROM productdir super,productdir sub
		WHERE sub.parent_id = super.id

显示内连接：
SELECT sub.dirName,super.dirName
		FROM productdir super JOIN productdir sub
				ON sub.parent_id = super.id

2.6 子查询

什么是子查询（嵌套查询）：一个查询语句嵌套在另一个查询语句中，内层查询的结果可以作为外层查询条件。
一般的，嵌套在WHERE或者FROM字句中。
为什么使用子查询：
   多表连接查询过程：
   1）：两张表做笛卡尔积。
   2）：筛选匹配条件的数据记录。（使用等等值连接筛选）
   若，笛卡尔积记录数比较大，可能造成服务器崩溃。

单行单列子查询:
需求: 查询零售价比罗技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')

3 数据操作(DML)

3.1 插入数据（添加）

插入语句:一次插入操作只插入一行.
INSERT INTO	table_name (column1,column2,column3...) VALUES	 (value1,value2,value3...);

INSERT INTO	table_name VALUES	(value1,value2,value3...);

-------------------------------------------
1.插入完整数据记录
2.插入数据记录一部分
3.插入多条数据记录（MySQL特有）
 INSERT INTO	table_name (column1,column2,column3...)
   VALUES (value1,value2,value3...),
          (value1,value2,value3...),
          (value1,value2,value3...)..;

4.插入查询结果
 INSERT INTO	table_name (column1,column2,column3...)
     SELECT (column1,column2,column3...)
     FROM table_name 

 INSERT INTO productdir (dirName,parent_id)
   SELECT dirName,parent_id FROM productdir

3.2 修改数据

UPDATE	table_name
SET	columnName = value [, columnName = value,columnName = value] …
[WHERE	condition];
如果省略了where子句，则全表的数据都会被修改。注意:没有FROM
-----------------------------------------------------------------
需求:将零售价大于300的货品零售价上调0.2倍
需求:将零售价大于300的有线鼠标的货品零售价上调0.1倍

3.3 删除数据

DELETE  FROM	tablename
[WHERE	condition];
如果省略了where子句，则全表的数据都会被删除执行完毕，表还在吗？

4 数据的备份与恢复

在dos命令行窗口进行，若操作系统版本高，则使用管理员模式
导出：

mysqldump -u账户 -p密码 数据库名称>脚本文件存储地
mysqldump -uroot -padmin jdbcdemo> C:/shop_bak.sql

导入：

mysql -u账户 -p密码 数据库名称< 脚本文件存储地址
mysql -uroot -padmin jdbcdemo< C:/shop_bak.sql

使用可视化导入导出
Navicat工具的导入和导出/Navicat工具的备份和还原

5 函数

5.1 聚集函数[分组…group by ]

1、count
2、sum
3、avg
4、max
5、min
cast函数&decimal类型&convert函数
数据类型转换可以通过CAST()和CONVERT()函数来实现。大多数情况下，这两个函数是重叠的，它们反映了SQL语言的演化历史。这两个函数的功能相似，不过它们的语法不同。虽然并非所有类型的值都能转变为其他数据类型，但总的来说，任何可以转换的值都可以用简单的函数实现转换。

5.2 cast函数

CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。语法如下：

cast(值 as 类型)
cast(‘123’ as SIGNED) 将123转换为int类型，返回值为整型类型
select cast(salePrice as decimal)---->数据类型  ----Java中的数据类型

思考：若如果试图将一个代表小数的字符串转换为整型值，又会出现什么情况呢？

cast(‘123.4’ as SIGNED)

CAST()函数和CONVERT()函数都不能执行四舍五入或截断操作。由于123.4不能用int数据类型来表示，所以对这个函数调用将产生一个错误. 要返回一个合法的数值，就必须使用能处理这个值的数据类型。对于这个例子，存在多个可用的数据类型。如果通过CAST()函数将这个值转换为decimal类型，需要首先定义decimal值的精度与小数位数。

5.3 decimal类型

MySQL DECIMAL数据类型用于在数据库中存储精确的数值。我们经常将DECIMAL数据类型用于保留准确精确度的列，例如会计系统中的货币数据。
要定义数据类型为DECIMAL的列，请使用以下语法：

column decimal(P,D)

在上面的语法中：
P是表示有效数字数的精度。 P范围为1〜65。
D是表示小数点后的位数。 D的范围是0~30。MySQL要求D小于或等于(<=)P。
DECIMAL(P，D)表示列可以存储D位小数的P位数。十进制列的实际范围取决于精度和刻度。
综上，可以将上述小数转换为整数：

select cast(‘123.4’ as decimal(P,D))

5.4 convert()函数

对于简单类型转换，CONVERT()函数和CAST()函数的功能相同，只是语法不同。CAST()函数一般更容易使用，其功能也更简单。语法如下：

select convert(expr ,type)
select convert(‘123’,signed)

可以转换的类型是有限制的。这个类型可以是以下值其中的一个：
二进制，同带binary前缀的效果 : BINARY
字符型，可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED

5.5 if null

MySQL IFNULL函数是MySQL控制流函数之一，它接受两个参数，如果不是NULL，则返回第一个参数。 否则，IFNULL函数返回第二个参数。
两个参数可以是文字值或表达式。
以下说明了IFNULL函数的语法：
IFNULL(expression_1,expression_2);
如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2的结果。

6 课程总结

6.1 重点

1、DML操作数据
2、数据库的备份与恢复

6.2 难点

7 课后练习

1、索引的作用是什么，怎么选择给哪些列设置索引呢；
2、手抄DML语句语法5次；
3、创建视图并对视图进行增删改查的操作，总结自己发现的规律
4、假设你需要给一个卖服装的公司开发一套软件进销存软件，请设计一下需要哪些表，表中的列以及列的类型，以及多张表之间的关系
商品表[id name 零售价,成本价,分类编号dir_id,…]
分类表[id 分类的名称 ,parentid ]
库存表[id 商品的id,]
用户表[id 用户名,密码,权限字段]
供应商[id 供应商,商品id]
-----------------Java中的类的设计!
5、学习了解数据库设计的三范式

8 面试题

1、你一般都使用什么方式进行数据备份，在你之前的项目中备份的周期都是多少？
2、count(1)和count(*)的区别？
3、说说事务的ACID属性是什么意思

9 扩展知识或课外阅读推荐

1、抽空体验使用一下数据库设计软件 PowerDesign