关于Mysql的那些事

执行流程：
FROM -> 决定从哪一个表查询
WHERE -> 决定从表中哪一些开始查询（条件过滤，学会善用条件过滤），从数据库表文件加载到内存中的原生数据过滤
GROUP BY -> 决定按照什么分组进行组内查询，一般跟聚集函数联合使用
SELECT -> 决定显示哪一些字段，在内存中形成一张临时表
HAVING -> 在组内条件过滤，一般使用SELECT语句中的聚集函数作为过滤条件。HAVING 是对SELECT 语句执行之后的临时表中的数据过滤
ORDER BY -> 决定显示的排序规则
LIMIT 决定每一页显示多少条件记录

– 查询所有员工信息，以及所属的部门
SELECT * FROM emp,dept;

– 几个表之间有某种关系，根据这种关系查询满足条件的记录
– 内连接查询(显式与隐式) （掌握）
select * from emp e INNER JOIN dept d on e.dept_id=d.id;-- 基本不用

– 显式内连接
SELECT * FROM emp e JOIN dept d ON e.dept_id = d.id;-- 经常用

– 隐式内连接
SELECT * FROM emp e,dept d WHERE e.dept_id = d.id;-- 经常用

SELECT e.name ‘员工名’,e.age ‘员工年龄’,d.name ‘部门名’ FROM emp e,dept d WHERE e.dept_id = d.id;

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

---

需求:
查询货品id，货品名称，货品所属分类名称
SELECT p.id,p.productName,pd.dirName FROM product p,productdir pd
WHERE p.dir_id = pd.id;

SELECT p.id,p.productName,pd.dirName FROM product p JOIN productdir pd
ON p.dir_id = pd.id;

查询零售价大于200的无线赛睿鼠标

select p1.*,p2.dirName
from product p1,productdir p2
where p1.salePrice>200
AND p2.dirName=‘无线赛睿鼠标’
AND p1.dir_id=p2.parent_id;

– 另一种写法
select p1.*,p2.dirName
from product p1
JOIN productdir p2
on p1.dir_id=p2.parent_id
where p1.salePrice>300
and p2.dirName=‘无线赛睿鼠标’;

查询每个货品对应的分类以及对应的库存

SELECT p.*,pd.dirName,ps.store_num
FROM product p
JOIN productdir pd ON p.dir_id = pd.id
JOIN product_stock ps ON p.id = ps.product_id;

如果库存货品都销售完成,按照利润从高到低查询货品名称,零售价,货品分类（三张表）.

– 外连接 （掌握）
外连接查询:
左外连接:查询出JOIN左边表的全部数据查询出来,JOIN右边的表不匹配的数据使用NULL来填充数据.
右外连接:查询出JOIN右边表的全部数据查询出来,JOIN左边的表不匹配的数据使用NULL来填充数据.

语法格式
SELECT
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

– 查询每种商品分类的名称和包含的商品库存总数：
SELECT p.*,pd.dirName,ps.store_num
FROM product p
LEFT JOIN productdir pd ON p.dir_id = pd.id
LEFT JOIN product_stock ps ON p.id = ps.product_id;

– IFNULL(表达式 ,值)函数，如果表达式为null则显示值，否则显示表达式结果值
SELECT p.*,IFNULL(pd.dirName,‘没有分类’),IFNULL(ps.store_num,0)
FROM product p
LEFT JOIN productdir pd ON p.dir_id = pd.parent_id
LEFT JOIN product_stock ps ON p.id = ps.product_id;

SELECT p.*,pd.dirName,IFNULL(ps.store_num ,0)
FROM product p
RIGHT JOIN productdir pd ON p.dir_id = pd.id
RIGHT JOIN product_stock ps ON p.id = ps.product_id;

=======================================================================
SELECT p.*,pd.dirName,IFNULL(ps.store_num ,0) + 全外关联是oracle支持的查询 mysql不支持
FROM product p
FULL OUTER JOIN productdir pd ON p.dir_id = pd.id
FULL OUTER JOIN product_stock ps ON p.id = ps.product_id;

– 自连接查询 – 适用于查询上下级关系、菜单和子菜单树桩结构的场景 （掌握）
需求:
查询每个商品分类的名称和父分类名称（所属分类的名称）:
SELECT son.dirName ‘子表商品分类名’,father.dirName ‘父表商品分类名’
FROM productdir son
LEFT JOIN productdir father
ON son.parent_id = father.id;

子查询（嵌套查询）：一个查询语句嵌套在另一个查询语句中，内层查询的结果可以作为外层查询条件。
一般的，子查询嵌套在WHERE或者FROM字句中。也可以在select中
分析：
从最里面的这一先查，查询出来后看看效果（现在就是一张临时表了）。
再从基于最里面的查询分析，继续查。

为什么使用子查询：
多表连接查询过程：
1）：两张表做笛卡尔积。
2）：筛选匹配条件的数据记录。（使用等等值连接筛选）
若，笛卡尔积记录数比较大，可能造成服务器崩溃。

– 需求: 查询分类编号和折扣与罗技M100相同的所有商品信息。
分析：
1.先查罗技M100的分类编号和折扣
2.查询所有商品信息条件where是：分类编号和折扣与罗技M100的分类编号和折扣相同

– 第一种效率太低下，因为比后面两种多了一次查询
– 方式1：基本不用
SELECT *
FROM product
WHERE dir_id = (SELECT dir_id FROM product WHERE productName = ‘罗技M100’) – 子查询作为一个结果值使用
AND cutoff = (SELECT cutoff FROM product WHERE productName = ‘罗技M100’);

– 一定要保证 = 左右两边的字段的类型顺序一致
– 方式2：常用
SELECT p.*
FROM product p,(SELECT dir_id,cutoff FROM product WHERE productName = ‘罗技M100’) temp – 子查询可以作为临时表
WHERE p.dir_id = temp.dir_id
AND p.cutoff = temp.cutoff;

– 方式3：不常用
SELECT p.*
FROM product p – 子查询可以作为临时表 = 左右两边()内的字段的类型顺序一致
WHERE (dir_id,cutoff) = (SELECT dir_id,cutoff FROM product WHERE productName = ‘罗技M100’);-- 子查询可以作为临时表

– 需求: 查询零售价比罗技Mx1100更高的所有商品信息。

– DML：数据操作语言 ：insert插入、delete删除、update修改 （掌握）
插入数据（添加）
插入语句:一次插入操作只插入一行.
– 方式1 标准插入方式: 值和字段类型要一致 建议
语法：
INSERT INTO table_name (column1,column2,column3…)
VALUES (value1,value2,value3…);

– 插入数据
– 方式1
语法：
INSERT INTO table_name (column1,column2,column3…)
VALUES (value1,value2,value3…);

INSERT INTO user (id,name,pwd,gender,age) VALUES (1,‘某文’,‘696969’,1,22);
INSERT INTO user (id,name,pwd,gender,age) VALUES (2,‘官总’,‘6699’,0,17);
INSERT INTO user (id,name,pwd,gender,age) VALUES (69,‘苍苍’,‘69’,0,45);

– 方式2 下面是所有字段都插入：必须是全部的字段都有匹配有值 不建议
INSERT INTO table_name VALUES (value1,value2,value3…);

INSERT INTO user VALUES (4,‘刘某凡’,‘996’,1,20);

– 方式3 mysql特有方式
INSERT INTO table_name (column1,column2,column3…)
VALUES (value1,value2,value3…),
(value1,value2,value3…),
(value1,value2,value3…)…;

INSERT INTO user (id,name,pwd,gender,age)
VALUES (8,‘吕小布’,‘66’,2,22),
(10,‘董卓’,‘99’,2,33),
(11,‘赵云’,‘666666’,2,32);

– 方式4 插入查询结果，注意，必须保证字段数量和类型完全一致 建议
INSERT INTO table_name (column1,column2,column3…)
SELECT column1,column2,column3…
FROM table_name;

INSERT INTO user (name,age)
SELECT name,age FROM emp;

– DML：数据操作语言 ：insert插入、delete删除、update修改 （掌握）
– 删除一条数据，必须要有条件判断where， 否则就是全表删除
– 这种方式可以用事务回滚将删除的数据还原（前提要关闭自动提交业务）。
DELETE FROM 表名 条件判断一般用where

– 例如：删除表user中id是11的记录
DELETE FROM user WHERE id = 11;
DELETE FROM user;-- 没有条件判断就是全表删除

– 例如：删除表user中姓名是苍苍的记录
DELETE FROM user WHERE name =‘苍苍’;

– 更高效的删库的方式,这种方式，不能通过事务回滚恢复数据
TRUNCATE 表名;
TRUNCATE user;

– DML：数据操作语言 ：insert插入、delete删除、update修改 （掌握）
– 修改数据，必须要有条件判断where， 否则就是全表修改
UPDATE 表名
SET columnName = value [, columnName = value,columnName = value] …
[WHERE condition];
– 如果省略了where子句，则全表的数据都会被修改。注意:没有FROM

– 需求:将零售价大于300的货品零售价上调0.2倍
UPDATE product p SET salePrice = salePrice * 1.2 WHERE salePrice > 300;

– 需求:将零售价大于300的有线鼠标的货品零售价上调0.1倍
– 1. 先找有线鼠标id 子查询
SELECT id FROM productdir WHERE dirName = ‘有线鼠标’;
– 2. 基于子查询修改数据
UPDATE product p SET salePrice = salePrice * 1.2
WHERE p.salePrice > 300 AND p.dir_id = (SELECT id FROM productdir WHERE dirName = ‘有线鼠标’);

事务：就是一系列完整的sql操作的语句，不可以被打断。否则回滚
以后开发，事务是在Java后台代码中控制回滚或者提交

注意：MySQL默认是自动提交模式。也就是一条SQL语句完成后，系统就自动提交了。
– 自动提交优点：可以及时同步数据，安全
– 自动提交缺点：每次修改数据库，都要提交，导致频繁的IO操作，浪费资源
查看当前的模式是否为自动提交模式：
SHOW VARIABLES LIKE ‘%autocommit’; – autocommit ON 表示是自动提交 off表示关闭自动提交

– 设置非自动提交事务 以后的事务是通过java代码控制（java是连接的数据库） 查询不需要事务，增删改必须有事务
SET autocommit=0;-- 0表示关闭自动提交，1表示开启自动提交

COMMIT; – 提交事务
ROLLBACK;-- 回滚事务

– CREATE TABLE account_shop (
– id int(11) NOT NULL AUTO_INCREMENT,
– acc double(20,2) DEFAULT NULL,
– PRIMARY KEY (id)
– ) ENGINE=InnoDB CHARSET=utf8;

//开启事务
START TRANSACTION;
update account_cu SET acc=acc-500;

update account_shop SET acc=acc+500;
– 如果有异常就回滚
ROLLBACK;-- 事务回滚
– 如果没有异常就整体提交，以后都是通过java代码判断回滚或者提交
COMMIT;-- 事务提交

– 数据库备份和还原 （掌握）
通过Navicat工具（掌握）
删库跑路必备技能：
在cmd命令行窗口进行，若操作系统版本高，则使用管理员模式
导出：
mysqldump -u账户 -p密码 数据库名称>脚本文件存储地 用管理员启动cmd
mysqldump -uroot -proot my0215 > E:/my0215_bak.sql
导入：
mysql -u账户 -p密码 数据库名称< 脚本文件存储地址
mysql -uroot -proot my0215 < E:/my0215_bak.sql

– DDL语句（掌握）
1：删除列
ALTER TABLE 【表名字】 DROP 【列名称】
ALTER TABLE user DROP age;

2：增加列
ALTER TABLE 【表名字】 ADD 【列名称】 INT NOT NULL  COMMENT ‘注释说明’
ALTER TABLE user ADD age int(3) COMMENT ‘年龄’;

3：修改列的类型信息
ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称（这里可以用和原来列同名即可）】 约束 COMMENT ‘注释说明’
ALTER TABLE test CHANGE name name VARCHAR(20) NOT NULL DEFAULT ‘随便’ COMMENT ‘姓名’;

4：重命名列
ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称】 约束 COMMENT ‘注释说明’

5：重命名表
ALTER TABLE 【表名字】 RENAME 【表新名字】
ALTER TABLE test RENAME test2;

6：删除表中主键
Alter TABLE 【表名字】 drop primary key
Alter TABLE test2 drop primary key

7：添加主键
ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY (字段)
ALTER TABLE test2 ADD CONSTRAINT pk_id PRIMARY KEY(id);

8：添加索引
ALTER TABLE 表名 add index INDEX_字段 (字段);

9: 添加唯一限制条件索引
ALTER TABLE 表名 add unique u_字段(字段);

10: 删除索引
alter table 表名 drop index 索引名;

执行流程：
FROM -> 决定从哪一个表查询
JOIN -> 决定跟哪一个表关联
ON -> 决定关联表的共同条件
WHERE -> 决定从表中哪一些开始查询（条件过滤，学会善用条件过滤）
GROUP BY -> 决定按照什么分组进行组内查询，一般跟聚集函数联合使用
SELECT -> 决定显示哪一些字段
HAVING -> 在组内条件过滤，一般使用SELECT语句中的聚集函数作为过滤条件
DISTINCT -> 根据select中的字段决定显示哪一些不重复的字段
ORDER BY -> 决定显示的排序规则
LIMIT -> 决定每一页显示多少条件记录

– 删除表标准写法
DROP TABLE IF EXISTS user;

– 创建表的标准写法
DROP TABLE IF EXISTS user;
CREATE TABLE user (…)

– ---------- mysql中的函数-------------可以自己扩展---------------------------------
– 调用函数的方式：
SELECT 函数名(…);

cast函数
CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。
语法如下：
cast(值 as 类型)
cast(‘123’ as SIGNED) 将123转换为int类型，返回值为整型类型
select cast(salePrice as decimal)---->数据类型 ----Java中的数据类型
支持以下类型：
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

– -- 调用函数的方式：
SELECT 函数名(…);

SELECT CAST(‘123’ AS SIGNED);-- 将varchar转换为int类型

SELECT CONVERT(‘你好吗’ USING utf8);

SELECT CAST(‘123.4015’ AS DECIMAL(10,3));-- 5是一共位数， 1是小数位数

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

– 作业：将字符串转换为日期类和，将日期转换为字符串类型

– mysql中视图 虚拟的表，用来简化查询的
视图：就是一个查询出来的结果集，为了方便查询的
如果视图包含下述结构中的任何一种，那么它就是不能修改
（1）聚合函数；
（2）DISTINCT关键字；
（3）GROUP BY子句；
（4）ORDER BY子句；
（5）HAVING子句；
（6）UNION运算符；
（7）位于选择列表中的子查询；
（8）FROM子句中包含多个表；
（9）SELECT语句中引用了不可更新视图；

语法：定义一视图
create view 视图名 as select 查询语句(表、视图);

举例：创建一个视图,包含商品信息、商品分类、库存信息
CREATE VIEW v_product_productdir_productstock AS
SELECT p.productName,pc.dirName,IFNULL(ps.store_num ,0) 库存 FROM product p
LEFT JOIN productdir pc ON p.dir_id = pc.id
LEFT JOIN product_stock ps ON p.id = ps.product_id;

使用视图:跟表一样的方式使用，但是尽量不要做增删改操作
SELECT 库存 FROM v_product_productdir_productstock;

修改视图
语法如下：
alter view 视图名 as select 查询语句;

删除视图
语法如下：
drop view 视图名;

视图注意点
1.视图的名字必须唯一，不能和其他表或视图重名。
2.视图可以嵌套使用，也就是视图里面可以再次调用视图。
3.视图的创建和删除只影响视图本身，不影响所使用的基本表
（这里只是对视图删除或修改，不是操作视图中字段）create view drop view alter view
4.如果对视图进行数据的增删改，就会影响原表

DELETE FROM v_product_productcate_productstock WHERE productName=‘罗技M90’;

视图作用：
1.使用视图，可以简化复杂查询逻辑。
2.使用视图，可以隐藏真实的表结构。起到安全的作用。

– mysql中索引
– MYSQL索引有四种
– PRIMARY（唯一且不能为空；一张表只能有一个主键索引）、
– INDEX（普通索引）、
– UNIQUE（唯一性索引）、
– FULLTEXT（全文索引：用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以）
索引只要出现在where语句中，就会自动使用该索引（模糊查询等除外）
数据量小的时候，不要用索引，根据我的经验一般几万条数据就可以用索引了

ALTER TABLE 表名 ADD INDEX index_索引名(多字段组合) //普通复合索引

ALTER TABLE 表名 ADD INDEX index_索引名(单字段) //普通单索引

ALTER TABLE t_user ADD INDEX name_city_phone(USERNAME,CITY,PHONE) //唯一复合索引
索引这是因为MYSQL组合查询只从最左边开始组合：始终包含username 【最左前缀匹配原则】
查询的时候，查询条件中有:USERNAME,CITY,PHONE 查询效率高
查询的时候，查询条件中有:USERNAME,CITY 查询效率高
查询的时候，查询条件中有:USERNAME,PHONE 查询效率高

查询的时候，查询条件中有:CITY,PHONE 查询效率不高

//添加索引
ALTER TABLE product ADD INDEX index_productName(productName); //唯一复合索引

复合索引的优势：
使用多个字段，查询更高效，所以尽量使用复合索引
mysql的两种索引方法：
1.BTREE（用于对等比较，如"=“和” <=>"）   //<=> 安全的比对   ，用与对null值比较，语义类似is null（）
2.HASH（用于非对等比较，比如范围查询）>，>=，<，<=、BETWEEN、Like

//删除索引
ALTER TABLE product drop INDEX index_productName; //唯一复合索引

– mysql中关联查询
create table order2014(id int,name varchar(50));
insert into order2014(id,name) values (1,‘订单一’);
insert into order2014(id,name) values (2,‘订单二’);
insert into order2014(id,name) values (3,‘订单三’);
insert into order2014(id,name) values (4,‘订单四’);
insert into order2014(id,name) values (5,‘订单五’);

create table order2015(id int,name varchar(50));
insert into order2015(id,name) values (6,‘订单六’);
insert into order2015(id,name) values (7,‘订单七’);
insert into order2015(id,name) values (8,‘订单八’);
insert into order2015(id,name) values (9,‘订单九’);
insert into order2015(id,name) values (4,‘订单四’);
– UNION 联合查询，去重 判断重复的机制：根据字段的组合顺序
SELECT * FROM order2014
UNION – UNION 去重复记录,效率低
SELECT * FROM order2015;

SELECT * FROM order2014
UNION ALL – UNION ALL不去重复记录,效率高，没有去重要求，建议使用
SELECT * FROM order2015;

– mysql中自定义函数和自定义存储过程 扩展
自定义函数========

自定义函数
语法格式
create function(参数名 参数类型,…)
returns 返回值类型
/*
从begin到end都是函数体
*/
begin
一些sql…语句
return 0;//返回值
end;

DELIMITER // – 这句话表示将重新定义结束标志//
CREATE FUNCTION show_time3() – show_time函数名
returns date – 返回值类型是date类型
BEGIN
– 定义一个Date变量来接受查询的结果值
DECLARE shi_jian DATE;
SET shi_jian = (SELECT NOW());

RETURN shi_jian; – 将当期时间查询出来
end //

– SELECT show_time();

DELIMITER // – 这句话表示将重新定义结束标志//
CREATE FUNCTION add_num(a int,b int) – show_time函数名
returns INT – 返回值类型是date类型
BEGIN
RETURN a+b; – 将当期时间查询出来
end //

//声明变量
SELECT add_num(1,2);-- 调用函数

===存储过程=
存储过程基本语法
DELIMITER //
create procedure 存储过程名([存储过程参数列表])
begin
set 参数名=参数;
SQL语句
end //

定义自定义存储过程
DELIMITER $-- 这句话表示将重新定义结束标志// CREATE procedure show_date() -- show_time函数名 BEGIN SELECT NOW(); END$−−这句话表示将重新定义结束标志//CREATEprocedureshowd​ate()−−showt​ime函数名BEGINSELECTNOW();END

调用存储过程:调用存储过程时，如果没有参数，可以不写()
CALL show_date();

DELIMITER // – 这句话表示将重新定义结束标志//
CREATE procedure get_product_name(IN product_id BIGINT,OUT product_name VARCHAR(50)) – show_time函数名
BEGIN
– 可以定义任意一下DDL，DQL、DML语句
SET product_name = (SELECT productName FROM product WHERE id = product_id);
END //

SET @p_name = null;//设置变量名时@p_name的值为null
– DECLARE @p_name VARCHAR(50);//DECLARE表示定义一个变量名叫@p_name，类型是VARCHAR(50)
CALL get_product_name(4,@p_name); – 调用存储过程
SELECT @p_name; – 查看变量的值

删除存储过程:
DROP procedure IF EXISTS get_product_name; //IF EXISTS避免出现当要删除的表、数据库、索引、自定义函数、自定义存储过程 SQL异常的情况

定义一个表（存储过程、索引等）标准语法

DROP procedure IF EXISTS get_product_name;
DELIMITER //
CREATE procedure get_product_name(IN product_id BIGINT,OUT product_name VARCHAR(50)) – show_time函数名
BEGIN
– 可以定义任意一下DDL，DQL、DML语句
SET product_name = (SELECT productName FROM product WHERE id = product_id);
END //

– //sql注入 了解
SELECT * FROM order2014 where id = 9 OR 1=1
UNION ALL
SELECT * FROM order2015 where id = 10 OR 1=1;