数据库复习二

条件查询

distinct

使用distinct关键字去除loc字段里的重复的记录行

select distinct loc from dept;

where

select * from emp

select * from emp where 1=1 --类似没条件

select * from emp where 1=0 --条件不成立

select * from emp where empno=100 --唯一条件

select * from emp where ename='tony' and deptno=2 --相当于两个条件的&关系

select * from emp where ename='tony' or deptno=1 --相当于两个条件的|关系

select name, sal from emp where sal=1400 or sal=1600 or sal=1800

-- 或

select name, sal from emp where sal in(1400,1600,1800) --sal的值在1400，1600，1800中

select name, sal from emp where sal not in(1400,1600,1800) --sal的值不在1400，1600，1800中

like

模糊查询

通配符%代表0到n个字符，通配符下划线_代表1个字符

select * from emp where ename like 'l%' --以l开头的

select * from emp where ename like '%a' --以a结束的

select * from emp where ename like '%a%' --中间包含a的

select * from emp where ename like 'l\_\_' --l后面有两个字符的 _代表一个字符位置

null

select * from emp where mgr is null --过滤字段mgr的值为空的

select * from emp where mgr is not null --过滤字段mgr的值不为空的

between and

select * from emp where sal<=3000 and sal>=10000--等效

select * from emp where sal between 3000 and 10000--等效

limit

在mysql中通过limit进行分页查询

select * from emp limit 2--列出前两条
select * from emp limit 1,3 --从第二条开始展示两条记录

order by

根据某一字段的值进行排序

select * from emp order by sal --默认升序
select * from emp order by sal desc--降序 

聚合

count  统计总记录数

select count(\*) from emp 
select count(1) from emp

max/min  求字段的最大值/最小值

select max(sal) from emp
select min(sal) from emp

sun/avg  求和/求平均值

select sum(字段名) from 表名
select avg(字段名) from 表名

分组

1.当查询结果出现了聚合列和非聚合列时使用分组

.2.通常按照非聚合列进行分组（使用了max,min,sum,avg,count 的就是聚合列）

group by  

#求出字段1的最大值和平均值并根据字段2分组。
select max(字段名1),avg(字段名1) from 表名
group by 字段名2

having  

重点：having和where的区别

having的作用:用来对分组后的数据,进一步过滤

#统计每年的入职人数,只要人数>1的记录
SELECT COUNT(1),YEAR(hiredate) FROM emp
GROUP BY YEAR(hiredate)
HAVING COUNT(1) > 1 
#不能改成where,后面出现了聚合函数

#统计每年的入职人数,只要2017年以后的记录
SELECT COUNT(1),YEAR(hiredate) FROM emp
WHERE YEAR(hiredate) > 2017 #高效,只是where里不能出现聚合函数
GROUP BY YEAR(hiredate)
#having YEAR(hiredate)> 2017 
#having里使用的过滤条件必须是查到的结果

事务

概念与特性

保证SQL语句,要么全执行成功,要么全失败
有四个特征ACID:
A：是原子性: 是指多条SQL是一个原子,密不可分.如果都正确,就操作了数据.如果有错误的都会发         生回滚,回到事务执行之前.
C：是一致性: 保证了数据的一致性和完整性.
I：是隔离性: 保证多线程并发时的数据安全,多个操作之间是被隔离的.
D：是持久性: 是指对数据CDU的影响是持久生效的.
        隔离级别:读未提交 读已提交 可重复读 串行化
        从前往后,性能越来越差,安全性越来越高.MySQL默认是可重复读

事务处理

• 在MySQL中只有使用了Innodb数据库隐形的数据库或表才支持事务
• 事务处理可以用来维护数据的完整性，保证成批的SQL语句要么全部执行，要么不执行
• 事务用来管理insert、update、delete语句，因为这些操作才会“破坏”数据，查询select语句是不会的
• MySQL默认数据库的事务是开启的，执行SQL后自动提交
• MySQL的事务也可以改成手动提交，那就有两个步骤：先开启，写完SQL后再手动提交。

提交 commit

特点：

        1.多条语句时，批量执行，事务提交

        2.有了事务时，多步操作就形成了原子性操作，高并发下也不会引起数据混乱

        3.mysql的事务默认就是开启的--多条语句一起操作时，要么一起成功要么一起失败

BEGIN; #关闭事务的自动提交
 
INSERT INTO user (id) VALUES(5)#成功
 
INSERT INTO user (id) VALUES(5)#已经存在5了，会失败
 
COMMIT #手动提交事务

回滚 rollback

当多条语句批量执行时，insert擦汗如重复的主键导致失败时，事务回滚

BEGIN
 
INSERT INTO user (id) VALUES(15);
 
INSERT INTO user (id) VALUES(35);#存在了
 
ROLLBACK#事务回滚，就不会再提交了

索引 （index）

定义

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

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

单值索引：一个索引只包括一个列，一个表可以有多个列

复合索引：一个索引同时包括多列

唯一索引：索引列的值必须唯一，但允许有空值（主键会自动创建唯一索引）

创建索引

1.查看索引（主键会自动创建一个索引）

show index from demp

2.创建索引

#create index  索引名 on 表名（字段名）
create index loc_index on dept(loc)

3.修改表结构，添加普通索引

alter table dept add index loc_index(loc)

4.创建唯一索引

alter table dept add unique(loc)

5.创建复合索引

alter table dept add index fuhe_index (dname,loc)

6.创建复合唯一索引

alter table dept add unique fuhe_index(dname,loc)

7.删除索引

alter table dept drop index fuhe_index

最左特性

当我们创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则，也称为最左特性。

索引的优点

• 索引是数据库优化
• 表的主键会默认自动创建索引
• 每个字段都可以被索引
• 大量降低数据库的IO磁盘读写成本，极大提高了检索速度
• 索引事先对数据进行了排序，大大提高了查询效率

缺点

• 索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
• 索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”
• 虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件
• 随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引

SQL语句是否需要加分号

一般不加分号，当在一些数据库可视化工具（SQLyogEnt）中一次执行多条语句时，每条语句间加上分号，用于表示一条语句的结束。