目录
- 多表查询
- 为什么要用多表查询？
- 笛卡尔积和内连接消除笛卡尔积
- 外键
- 数据库内连接练习
- 左连接查询和右连接查询
- 等值连接
- out join
- 自连接
- 子查询
- 数据操作(DML)
- 数据的插入
- 数据的删除
- 数据的修改
- 数据库的备份与恢复
- Dos命令行窗口导出
- Dos命令行窗口导入
- Navicat导出
- Navicat导入
- 函数
- 查漏补缺
- 数据库访问控制
- 视图
- 三范式
多表查询
为什么要用多表查询？
可以减少单表里面的字段 ，可以很灵活的查询相要的数据。
笛卡尔积和内连接消除笛卡尔积
-- 迪卡尔积
就是两张表组合查询时，一张表的一行数据会和另一张表的每一行都组合一下
-- 示例
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字段数据，应该通过关联查询推导出来。
