java入门-------MySQL（尚马day21） week5（7）

6. DQL

数据查询语言。 select

6.1.语法

SELECT [DISTINCT] 列的集合
FROM 表的集合 
[
WHERe 条件的集合 -- 过滤记录
GROUP BY 列的集合 -- 分组查询   使用指定的列进行分组
HAVINg   条件的集合 -- 类似的where 过滤的  对分组之后的数据进行过滤
ORDER BY 列 ASC/DESC  -- 根据指定的列进行升序或者降序排列 默认升序 ASC
LIMIT ?/?,? -- 限定最后记录 分页查询       
]

6.2 基础查询

SELECt * FROM A; -- 表里面列很少的时候  *
SELECt ID ,NAME ,AGE FROM A; -- 列很多  指定列查询  提高sql的查询的性能

6.3 条件查询 where

   条件查询就是在查询时给出WHERe子句，在WHERe子句中可以使用如下运算符及关键字：
   =、!=、<>、<、<=、>、>=；
   BETWEEN…AND；是否满足一个区间范围  >=  <=
   IN(set)；条件的集合
   IS NULL；
   AND； 连接多个条件的查询
   OR；or  满足其中一个条件就可以
   NOT；
​
-- 1. 条件查询
--     查询学生性别为女，并且年龄50的记录
-- SELECt * FROM stu WHERe gender='female' OR age=15;
--     查询学号为S_1001，S_1002，S_1003的记录
-- SELECt * FROM stu WHERe sid='s_1001' OR sid='s_1001'  OR sid='s_1003' ;
-- SELECt * FROM stu WHERe sid IN ('s_1001','s_1002','s_1003');
--     查询学号不是S_1001，S_1002，S_1003的记录
-- SELECt * FROM stu WHERe NOT sid  IN ('s_1001','s_1002','s_1003');
-- SELECt * FROM stu WHERe sid!='s_1001' AND sid!='s_1002'  AND sid!='s_1003' ;
​
--     查询年龄为null的记录
-- SELECt * FROM stu WHERe age IS NulL;
-- SELECt * FROM stu WHERe age IS NOT  NulL;
​
--     查询年龄在20到40之间的学生记录
-- SELECt * FROM stu WHERe age>= 20 AND age<=40;
-- SELECt * FROM stu WHERe age BETWEEN 20 AND 40;
​
--     查询性别非男的学生记录
-- SELECt * FROM stu WHERe gender!='male' OR gender is null;

6.4 模糊查询 like

与where一起使用。 模糊匹配

--     查询姓名由5个字母构成的学生记录
-- 使用通配符:  统配任意一个字符  _ 
-- SELECt * FROM stu WHERe sname LIKE '_____';
--     查询姓名以“z”开头的学生记录
-- %: 统配的是任意数量的字符数据
-- SELECt * FROM stu WHERe sname LIKE 'z%';
-- SHOW VARIABLES LIKE '%character%';
​
--     查询姓名中第2个字母为“i”的学生记录
-- SELECt * FROM stu WHERe sname LIKE '_i%';
--     查询姓名中包含“a”字母的学生记录
-- SELECt * FROM stu WHERe sname LIKE '%a%';

6.5 字段控制查询 1. distinct 去重

只是对指定的列去重。

-- 3. DISTINCT  去重  看DISTINCT有哪些列
-- SELECt DISTINCT gender FROM stu;
-- 可以去重重复的行记录

2. ifnull

-- 计算员工的工资
-- 只要是查询语句  都会出现一张临时表
-- comm有值得记录 运算是ok
-- 列与null进行任意算术运算  最后的结果都是null
-- IFNULL(列名,新的数据) 列为null  使用指定的数据进行运算  否则还是自身的数据
-- SELECt empno,ename,sal,comm, (sal+IFNULL(comm,0)) FROM emp;

6.6 排序 order by

在代码里面排序:
 Arrays.sort()
 List.sort(Comparator c)
 Collections.sort(List l)    
 Collections.sort(List l,Comparator c)    
     
 List list;      Integer implements Comparable 
 包装类型+String  
 
  List list; 

order by 字段1 ASC/DESC ， 字段2 ASC/DESC

-- 只要是查询一般都会排序 一般都是将最新的数据  展示在最前面
-- ORDER BY id/create_time(毫秒数) DESC
-- 查询学生的信息  根据年龄进行升序或者降序排列
-- SELECt * FROM stu ORDER BY age ASC, sid DESC;

6.7 分组函数

也可以称为 “聚合函数”。------> 有且只有1个记录(只有1个结果)

   COUNT(列)：统计指定列不为NULL的记录行数；
   MAX(列)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
   MIN(列)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
   SUM(列)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
   AVG(列)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
​
​
--     查询emp表中记录数：
-- SELECt COUNT(empno),COUNT(comm),COUNT(*),COUNT(1) FROM emp;
--     查询emp表中月薪大于2500的人数：
--     统计月薪与佣金之和大于2500元的人数：
-- SELECt COUNT(*) FROM emp WHERe (sal+IFNULL(comm,0))>2500;
​
-- 在查询的时候 会出现的很多新的字段 这些的名称 有可能很复杂
-- 一般进行别名处理  [AS] 
-- 对表起别名: 多表关联的时候  
-- SELECt MAX(sal),MIN(e.sal),SUM(sal),AVG(sal),SUM(sal)/COUNT(*) AS avgSal  FROM emp AS e;
​
-- SELECt * from emp where sal = (SELECt MAX(sal) FROM emp);

6.8 分组查询 group by

根据指定的列进行分组查询。

-- 分组查询
--     查询每个部门的部门编号和每个部门的工资和：
-- 先分组  再进行分组运算
-- --  查询每个部门的部门编号以及每个部门的人数：
-- SELECt deptno,sum(sal) '每个部门工资和',COUNT(*) '每个部门的员工数量'  FROM emp  GROUP BY deptno;
--     查询每个部门的部门编号以及每个部门员工工资大于1500的人数：
-- SELECt deptno,count(*) FROM emp WHERe sal>1500 GROUP BY deptno;

6.9 having

作用与where一致。

-- 查询薪资>1500 的员工信息
-- SELECt * FROM emp WHERe sal>1500;
-- SELECt * FROM emp HAVINg sal>1500;
-- 与分组相关
-- 查询工资总和大于9000的部门编号以及工资和：
-- > 1111 - Invalid use of group function  where 不能与组函数一起使用
-- 对比where vs having  : 与条件总和使用  过滤数据
-- 1. 从位置 where在GROUP BY之前  having在GROUP BY之后
-- 2. where 不能与组函数一起使用  having就是对分组之后的数据进行过滤 因此可以一起使用
-- SELECt deptno,sum(sal) AS sum FROM emp  GROUP BY deptno HAVINg sum(sal)>9000 ;

6.10 多表关联查询 1. 等值连接

连接的条件是一个 =

-- 多表关联查询
-- 1. 查询员工信息，要求显示员工号，姓名，月薪，部门名称 
-- 等值关联查询: 查询的条件是=
SELECt
  e.empno,e.ename,e.sal,d.dname
FROM emp AS e,dept AS d   -- 查询多张表 使用 , 
WHERe e.deptno=d.deptno;   -- 多表关联的时候 分析表与表的关系(外键列+主键列)
​
-- 多表关联的时候 有可能多张表里面出现很多重名列 
-- 1. 使用表名限定
-- 2. 使用别名 表的别名
-- 关联2张表查询: 条件至少1个   关联n张表查询: n-1

-- 查询指定用户的购物车信息
-- SELECt 
--  u.id,u.`name`,c.total_money
-- FROM 
--  demo.tb_userinfo AS u, demo.tb_cart AS c
-- WHERe
--  u.id =1  AND u.id = c.uid; 
-- 查询用户购买的购物项(小计)
-- SELECt
--  u.id,u.`name`,c.total_money,ci.buy_num,ci.money
-- FROM
--  demo.tb_userinfo AS u, demo.tb_cart AS c,demo.tb_cart_item AS ci 
-- WHERe
--  u.id =1 AND u.id=c.uid AND c.id=ci.cid;
-- 查询用户购买的商品信息
-- SELECt
--  u.id,u.`name`,c.total_money,p.prod_name,p.prod_price,p.prod_image,ci.buy_num,ci.money
-- FROM
--  demo.tb_userinfo AS u, demo.tb_cart AS c,demo.tb_cart_item AS ci ,demo.tb_product AS p
-- WHERe
--  u.id =1 AND u.id=c.uid AND c.id=ci.cid AND ci.pid=p.id;

2. 不等值连接

连接条件不是一个 =

-- 查询员工信息，要求显示：员工号，姓名，月薪，部门名称,薪水的级别
-- SELECt
--   e.empno,e.ename,e.sal,s.GRADE
-- FROM
--   emp AS e, salgrade AS s
-- WHERe
--   e.sal BETWEEN s.LowSAL AND s.HISAL
-- ORDER BY e.sal;
​
SELECt
  e.empno,e.ename,e.sal,s.GRADE,d.dname,d.loc
FROM
  emp AS e, salgrade AS s,dept AS d
WHERe
  e.sal BETWEEN s.LowSAL AND s.HISAL AND e.deptno=d.deptno
ORDER BY e.sal;
​

3. 自连接

本表与本表关联查询。

-- 自连接
-- 查询员工姓名和员工的老板的名称
-- 本表与本表有关系: 需要将一张表  看成多张表  别名  使用条件分析
SELECt
 e1.*,   e2.ename AS boss_name
FROM
emp AS e1, emp AS e2
WHERe e1.mgr=e2.empno;

4. 外连接

-- 查询的所有的员工信息并展示上级信息
-- 基准表是: 员工表
-- 外连接
-- 1. 内外连接  ---->普通的关联查询  INNER OUTER JOIN  INNER JOIN...ON/WHERe
-- 2. 左外连接 LEFT JOIN...ON  以左表为基准 右表里面没有的数据使用null或者0填充
-- 3. 右外连接 RIGHT JOIN...ON 以右表为基准 左表里面没有的数据使用null或者0填充
-- SELECt
--  e1.*,   e2.ename AS boss_name
-- FROM
-- emp AS e1 INNER JOIN  emp AS e2
-- ON e1.mgr=e2.empno
-- WHERe 1=1;
​
-- SELECt
--  e1.*,   e2.ename AS boss_name
-- FROM
-- emp AS e1 LEFT JOIN  emp AS e2
-- ON e1.mgr=e2.empno;
​
-- 按部门统计员工数，部门号，部门名称，人数 count()
-- 统计每个部门的员工数
-- SELECt
--   d.*,count(*) '部门人数'
-- FROM 
--  dept AS d, emp AS e
-- WHERe 
--  d.deptno=e.deptno 
-- GROUP BY d.deptno ;
​
-- SELECt
--   d.*, count(e.empno) '部门人数'
-- FROM 
--  emp AS e RIGHT JOIN  dept AS d  ON  d.deptno=e.deptno 
-- GROUP BY d.deptno ;
-- 

-- 查询员工信息: 展示员工所在的部门名称 员工的薪资等级  还有员工的上级领导名称
SELECt
 e1.*,d.dname,s.GRADE,e2.ename
FROM
emp AS e1 LEFT JOIN emp AS e2 ON e1.mgr=e2.empno
LEFT JOIN dept AS d ON e1.deptno=d.deptno,salgrade AS s 
WHERe
  e1.sal BETWEEN s.LowSAL AND s.HISAL; 
​

6.11 子查询

-- 查询员工信息  他的薪资是20号部门平均薪资
-- SELECt avg(sal) FROM emp WHERe deptno=20;
-- SELECt * FROM emp WHERe sal=2175;
-- 子查询: 查询的条件是未知
SELECt * FROM emp WHERe sal=(SELECt avg(sal) FROM emp WHERe deptno=20);

6.12. 集合查询

将多个结果集转换成1个结果集。

-- 查找所有的用户信息
-- SELECt * FROM user0;
-- SELECt * FROM user1;
-- SELECt * FROM user2;
-- 集合运算   UNIOn  vs  UNIOn ALL
-- 将多个结果集  合成1个
-- UNIOn ALL: 不去重  UNIOn: 去除重复的行记录
​
-- SELECt * FROM user0
-- UNIOn
-- SELECt * FROM user1
-- UNIOn
-- SELECt * FROM user2;
​
-- SELECt id,name FROM USER0
-- UNIOn
-- SELECt id,name FROM USER1
-- UNIOn
-- SELECt id,name FROM USER2;
​
-- 在mysql里面 有几种去重的操作?
-- 1. DISTINCT
-- 2. UNIOn
-- 3. GROUP BY

6.13 分页查询

-- 分页查询员工信息
-- size=4
-- page=1
-- SELECt count(*) FROM emp;  15/4=3
-- LIMIT size; 从第1条开始查询 查size条数据  只能查询第1页的数据
-- LIMIT startIndex,size; 从第startIndex+1条开始查询 查size条
-- 第1页
SELECt * FROM emp LIMIT 0,4;
​
-- 第2页
SELECt * FROM emp LIMIT 4,4;
​
-- 第3页
SELECt * FROM emp LIMIT 8,4;
​
-- 第4页
SELECt * FROM emp LIMIT 12,4;
​
-- SELECt * FROM emp LIMIT (page-1)*size,size;

7.函数

自学

8. 数据库存储引擎

9. 数据备份

1. 物理备份  C:ProgramDataMySQLMySQL Server 5.7Data
2. 命令行备份
    C:UsersJAVASM>mysqldump -uroot -proot demo >> D:\a.sql
    -- 将demo库里面的所有的表都进行了备份
C:UsersJAVASM>mysqldump -uroot -proot demo tb_userinfo tb_cart >> D:\b.sql
    -- 将demo库里面的指定的表的结构+数据进行备份
    
    在连接成功前提下  source 文件路径;   source d:\a.sql
    
3. 使用图形化的工具
    导入/导出sql文件。
    
4. 使用图形化的工具---->备分