day022

(*)SQL语句的基本特性
1、 SQL 是一种声明式语言
2、 SQL 的语法并不按照语法顺序执行
3、 SQL 语言的核心是对表的引用（table references）
4、 灵活引用表能使 SQL 语句变得更强大
5、 SQL 语句中推荐使用表连接，而不是逗号分隔表
6、 SQL 语句中不同的连接操作
7、 SQL 中的派生表如同表的变量
8、 SQL 语句中的 GROUP BY 是对表引用的转换
9、 SQL 语句中的 SELECT 实质上是对关系的映射
10、SQL 语句中的几个简单的关键词： DISTINCT ，UNION ，ORDER BY 和 OFFSET

sql去重

distinct用来查询不重复记录的条数,
即distinct来返回不重复字段的条数
（count(distinct id)）,其原因是distinct只能返回他的目标字段，而无法返回其他字段

字段去重---->语句：SELECT DISTINCT 字段名 FROM 表名;

select deptno, AVG(sal) from emp
group by deptno #按部门分组
having AVG(sal)<8000

找表—>分组—>过滤—>搜索

################################################

外键约束 foreign key

外键–>避免在多个表里存重复的数据

foreign key(当前表的主键)reference 对方表(对方表的主键)

CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
CREATE TABLE tb_user_addr(
user_id INT PRIMARY KEY,
addr VARCHAR(20),
#通过特殊字段描述两张表之间的关系
#foreign key(当前表的主键)reference 对方表(对方表的主键)
FOREIGN KEY(user_id,…,…)REFERENCES tb_user(id,…,…)
);

系统报错:不能添加或更新一个与外键约束冲突的数据

1)外键约束字段的值必须取自另一张表对应字段中出现过的值(否则报错)
2)想删除主表的数据必须先删除字表的对应数据

外键约束后—>两张表拥有了新的身份:主表,子表

字表的外键字段值 必须 取自 主表的对应字段值.

通过外键约束,两张表的信息可以连起来读

################################################

索引 index

定义

索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构，通俗来讲索引就好比书本的目录，加快数据库的查询速度。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。
目前大多数索引都采用BTree树方式构建。

分类
单值索引：一个索引只包括一个列，一个表可以有多个列
唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
复合索引：一个索引同时包括多列

创建索引
#查看索引，主键会自动创建索引

索引的作用

优点:

索引是数据库优化

表的主键会默认自动创建索引

每个字段都可以被索引

大量降低数据库的IO磁盘读写成本，极大提高了检索速度

索引事先对数据进行了排序，大大提高了查询效率

缺点:

索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间

索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”

虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件

随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引

例子:
user表有3个字段（id、name、age），存储8条记录，当我们查找age为20的记录时，sql语句 select * from user where age=20；

1 在没有索引情况下需要依次遍历表里的记录，第6次时找到age=20的记录；
2 如果我们在age列添加索引，只需3次，就可以找到age=20的记录。

#练习3:查询部门地址在二区的所有员工信息
#笛卡尔积查询
SELECT * FROM dept,emp
WHERE dept.deptno = emp.deptno
AND dept.loc=“二区”

#连接查询(小表驱动大表,就是左边写一个小一点的表)
SELECT * FROM dept LEFT JOIN emp
ON dept.deptno = emp.deptno
WHERE dept.loc=“二区”

SELECT * FROM dept RIGHT JOIN emp
ON dept.deptno = emp.deptno – 表关系
WHERE dept.loc=“二区” – 业务关系

#子查询
SELECT * FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE loc = ‘二区’);

注意:当上一次查到多个结果时,用’IN’代替’=’.

################################################

实现过程:设置索引,使用索引
分类
单值索引：一个索引只包括一个列，一个表可以有多个列
唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引
复合索引：一个索引同时包括多列

#查看索引
SHOW INDEX FROM 表名;

#创建索引(给常用来作为查询条件的字段添加)
#create index 索引名 on 表名(字段名)

唯一索引(此索引列的值不能重复)

CREATE UNIQUE INDEX 索引名 ON 表名(值唯一的列);

复合索引

CREATE INDEX 索引名 ON 表名(列名1,列名2);

#删除索引
ALTER TABLE 表名 DROP INDEX 索引名;

#使用索引(背后技术,无法直接体现出来,但速度是加快了)
SELECT * FROM dept WHERE dname = ‘test1’;

#观察sql的性能,执行计划(执行过程中用到索引了吗?)(找possible_keys的值)
EXPLAIN ----------------------------------->索引状态解析关键字
SELECT * FROM dept WHERE dname = ‘test1’;
#解释↑
– possible_keys 可能用到的索引

注意:复合索引可能会导致索引失效
#使用复合索引(最左特性)
#想生效则在查时查询条件里必须包含dept(test2,test3)里test2位置上的元素.
EXPLAIN
SELECT * FROM dept WHERE test3 = 3 OR test2 = 2;-----索引正常
EXPLAIN
SELECT * FROM dept WHERE test3 = 3;----索引失效

################################################

表关联 association

多表联查

多表联查 join

笛卡尔积 , where后面写两张表的关系,如果有外键就更方便,否则观察得出

SELECT * FROM 表1,表2
WHERE 表名1.列名X = 表名2.列名X

出来的都是笛卡尔数据,不经过筛选过滤一般会很大,缺乏实际的业务意义.

三种连接 join

(小表驱动大表,就是左边写一个小一点的表)

SELECT * FROM 表1 JOIN 表二
ON 表名1.列名X = 表名2.列名X

实例:
#查询部门,名称叫accounting的员工姓名
#笛卡尔积
SELECT emp.enameFROM dept,emp
WHERE dept.deptno = emp.deptno– 表关系
AND dept.dname = ‘accounting’-- 业务关系
#连接查询
SELECT EMP.enameFROM DEPT JOIN EMP
ON DEPT.deptno = EMP.deptno
WHERE DEPT.dname= ‘ACCOUNTING’

内连接 inner join
SELECT * FROM dept INNER JOIN emp
ON DEPT.deptno = EMP.deptno

左（外）连接 left join
SELECT * FROM dept LEFT JOIN emp
ON DEPT.deptno = EMP.deptno

右（外）连接 right join
SELECT * FROM dept RIGHT JOIN emp
ON DEPT.deptno = EMP.deptno

三张表以上:
select 表a.列名1,…,表b.列名1,…,表c.列名1,…
from table1 表a,table2 表b,table3 表c
where 表a.列名X=表b.列名X and 表a.列名X = 表c.列名X

select 表a.列名1,…,表b.列名1,…,表c.列名1,…
from table1 表a left join table2 表b
on 表a.列名X = 表b.列名X
left join table3 表c
on 表a.列名X=表c.列名X

################################################

子查询 subquery
概念
子查询是指嵌入在其他select语句中的select语句，也叫嵌套查询。
子查询执行效率低慎用。记录少时效率影响不大、图方便直接使用，记录多时最好使用其它方式替代。

select * from emp where deptno = (select deptno from emp where ename=‘tony’);

把上次查询的结果作为下次查询的材料

练习:
#子查询 查询部门名称叫accounting的员工姓名
– 1.查部门表, 根据部门名称accounting查deptno
– 2.查询员工表,根据deptno查姓名
SELECT ename FROM emp WHERE deptno =
(SELECT deptno FROM dept
WHERE dname = ‘accounting’);

(先查 寻找最终目标的材料/条件 ,再实际找最终目标)

################################################

(1) FROM [left_table] 选择表

(2) ON <join_condition> 链接条件

(3) <join_type> JOIN <right_table> 链接

(4) WHERE <where_condition> 条件过滤

(5) GROUP BY <group_by_list> 分组

(6) AGG_FUNC(column or expression),… 聚合

(7) HAVING <having_condition> 分组过滤

(8) SELECT (9) DISTINCT column,… 选择字段、去重

(9) ORDER BY <order_by_list> 排序

(10) LIMIT count OFFSET count; 分页

sql会尽可能先过滤再查

################################################

EVENT LOG ----> IDEA撤回一些系统设置更改.