数据库
一、数据库事务特性
- 原子性：原子性是指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败
- 一致性：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态
- 隔离性：隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
- 持久性：持久性是指一个事务一旦被提交，它对数据库中的改变就是永久性的
二、SQL语句
1、常用语句
#数据库操作 #创建数据库 CREATE DATABASE IF NOT EXISTS 数据库名; #查看数据库 SHOW DATABASES; #删除数据库 DROP DATABASE IF EXISTS 数据库名; #选择数据库 USE 数据库名;
#数据表操作 #创建数据表 CREATE TABLE IF NOT EXISTS test（ test_id INT NOT NULL AUTO_INCREMENT, test_title VARCHAR(100) NOT NULL, test_author VARCHAR(40) NOT NULL, submissiont_date DATE, PRIMARY KEY(test_id) )ENGINE=InnDB DEFAULT CHARSET=utf8; #删除数据表 DROP TABLE 表名; #查看表结构 DESC 表名; #查看数据表的创建语句 SHOW CREATE TABLE 表名; #修改表名 ALTER TABLE RENAME TO/AS NEW_TABLENAME;
#数据表字段操作 #增加字段 ALTER TABLE 表名 ADD 字段 VARCHAR(20) DEFAULT NULL; #删除字段 ALTER TABLE 表名 DROP 字段; #修改字段类型 ALTER TABLE 表名 MODIFY 字段 VARCHAR(10); #修改字段名 ALTER TABLE 表名 CHANGE 旧字段 新字段 INT; (修改一个字段的名称，一定要重新指定该字段的类型) #修改字段的排列位置 ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2;
#表中数据操作 #插入数据 INSERT INTO 表名字段1,字段2) VALUES (值1,值2); #删除数据 DELETE FROM 表名 WHERE 字段=条件; #删除全表数据 DELETE FROM 表名; #清空表数据 TRUNCATE TABLE 表名; #查找数据 SELECT * FROM 表名 WHERE 字段=条件; #修改数据 UPDATE 表名 SET 字段=新值 WHERE 字段=旧值;
select distinct 字段1,字段2 from 表名; # 过滤字段重复数据
select * from 表名 order by 列名 ASC|DESC; # 用于对结果集按照一个列或多个列进行排序 ，默认ASC升序，需要降序排列时使用DESC关键字
select * from 表名 group by 列名; # group by语句用于结合聚合函数，根据一个或多个列对结果集进行排序
group by 语句分组原理：先按照第一个字段分组，如果第一个字段有相同值，则把分组结果再按第二个字段分组，以此类推；如果第一个字段分组每个值都是唯一的，则不会再按照第二个字段分组了
group_concat 可以将分组后每个组内的值都显示出来
group by 后面可以使用having来过滤数据
having 和where 的区别
From 表 where 条件(表数据过滤) group by 分组标准 having (组数据过滤)
| where | having |
| 不可以使用聚合函数 | 可以使用聚合函数 |
| 数据group by之前过滤 | 数据group by后过滤 |
| 查询条件中不可以使用字段别名 | 查询条件中可以使用字段别名 |
| 用于过滤数据行 | 用于过滤分组后的结果集 |
| 根据数据表的字段直接过滤 | 根据已查询出的字段进行过滤 |
select top 规定要返回的记录的数目
like在where子句中搜索列中的指定模式，like关键字中%通配符，代表任意长度的字符。like匹配的字符串不区分大小写，但可以添加binary关键字区分大小写
where ** in（）in允许在where子句中规定多个值
2、SQL语句执行顺序
- from
- on
- join
- where
- group by
- having
- select
- distinct
- order by
- limit
3、多表查询
left join：左连接，获取左表全部记录，即使右表无匹配记录
right join：右连接，获取右表全部积极，即使左表无匹配记录
inner join：内连接，获取两个表中字段匹配关系的记录
union 、union all：外连接，union用于连接2个以上的select语句组合到一个结果集中，多个select语句会删除重复的值，union all会选取所有，包括重复
cross join：交叉连接
select * from 表1 left join 表2 on 条件(匹配条件)
左右连接跟内连接的区别：左右连接为外连接，而内连接最大的不同在于，匹配成功的数据，只有两个表都有才会返回，相当于交集
左右连接跟全连接的区别：mysql中的全连接可以用union实现，全连接是左连接和右连接的结合，只要有匹配成功的数据，无论两个表是否都有匹配的数据，都会返回，如果没有则显示null，相当于并集
4、嵌套查询
一个查询语句块可以嵌套在另外一个查询块的where子句中，称为嵌套查询，外层查询叫父查询，内层查询叫子查询，从查询
嵌套查询工作方式，先处理内查询，由内向外处理，外层查询利用内层查询的结果嵌套不仅仅可以用于父查询select语句使用，还可以用于insert、update、delete语句或其它子查询中，因此在写子查询语句时，可以先测试下内层的子查询语句是否查询出了想要的内容，再一层层往外测试，增加子查询正确率，否则多层的嵌套语句可读性很低
什么是子查询？
- 将一个查询语句嵌套在另一个查询语句中
- 子查询也可以实现多表查询
- 子查询也能在select、update、delete语句中使用，还能进行多层嵌套
三、索引
1、索引的概念
索引是对数据库表中一列或者多列的值进行排序的一种数据结构，使用索引可以快速访问数据表中的特定信息。数据库的索引类似于书籍的目录，可以根据目录的某个页码立即找到对应的内容。索引是在数据库存储的数据之外，额外保存一些路标（一般是B+树），以减少检索数据的时间。所以索引是主数据衍生的附加结构
2、索引分类
- 主键索引
- 唯一索引
- 普通索引
- 组合索引
3、索引的特点
- 索引可以加快数据库的检索速度
- 索引降低了数据库插入、修改、删除等维护任务的速度
- 索引创建在表上，不能创建在视图上
- 索引即可以直接创建，也可以间接创建
- 可以在优化隐藏中，使用索引
- 使用查询处理器执行sql语句，在一个表上，一次只能使用一个索引
4、索引的优缺点
索引的优点
- 创建唯一性索引，保证数据库表中每一行数据的唯一性
- 大大加快数据的检索速度，这也是创建索引的最主要的原因
- 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义
- 在使用分组和排序子句进行数据检索时，同样可以显著减少查找中分组的排序的时间
- 通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
索引的缺点
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
- 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引，那么需要的空间会更大
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度
5、索引失效的场景
在mysql中，如果想查看某条sql语句是否使用了索引，或者已建好的索引是否失效，可以通过explain关键字，查看该sql语句的执行计划，来判断索引使用情况
索引优化
索引失效的场景：
- 使用or关键字，可能会导致索引失效。如果使用了or关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效
- where语句中索引列使用了负向查询，负向查询包括：NOT、!=、<>、!< 、!>、 NOT IN 、NOT LIKE等
- 使用联合索引时，where中索引违背了最左匹配原则，会导致索引生效
- 在索引列上使用函数，会导致索引失效
- 对索引列进行计算时，会导致索引失效
- like查询以%开头时，会导致索引失效。因为索引就像字典中的目录，一般目录是按字母或拼音从小到大、从左到右，是有顺序的，在查目录时，通常会先从左边第一个字母进行匹配
- 如何列类型是字符串，但在条件中没有将数据用引号引用起来，也会导致索引失效
6、优化索引的一些建议
1）禁止在更新十分频繁、区分度不高的属性上建立索引
- 更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能
- 有些区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似
2）建立组合索引，必须把区分度高的字段放在前面
四、存储过程和触发器
五、数据库优化
