一、视图
视图是一种虚拟表
视图建立在已有表的基础上,视图建立依赖的这些表称为基表
向视图提供数据内容的语句为SELECT语句，可以将视图理解为存储起来的SELECT语句
视图向用户提供基表数据的另一种表现形式
●视图的作用
简化复杂的多表查询
•视图本身就是一条查询SQL, 我们可以将一次复杂的查询构建成一张视图,用户只要查询视图就可以获取想要得到的信息(不需要再编写复杂的SQL), 可以理解为查询视图就相当于将创建视图的SQL再次执行一次
视图主要就是为了简化多表的查询，简化复杂查询
●语法格式：
// 创建视图
create view 视图名称 as select语句；
//view:表示视图
//as:表示视图要执行的操作
//select：向视图提供数据内容
//查询视图和查询表一样
select * from 视图名称；
//删除视图
drop view 视图名称；
●视图与表的区别
•视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示
•通过视图不能改变表中数据(一般情况下视图中的数据都是表中的列 经过计算得到的结果，不允许 更新)
•删除视图，表不受影响，而删除表，视图不再起作用
二、存储过程
存储过程是数据库中保存(Stored)的一系列SQL命令(Procedure)的集合。也可以将其看作相互之间有关系的SQL命令组织在一起形成的一个方法。
●存储过程的优点
•提高执行性能。存储过程执行效率之所高，在于普通的SQL语句，每次都会对语法分析，编译，执行，而存储过程只是在第一次执行语法分析，编译，执行，以后都是对结果进行调用。
•可减轻网络负担。使用存储过程，复杂的数据库操作也可以在数据库服务器中完成。只需要从客户端(或应用程序)传递给数据库必要的参数就行，比起需要多次传递SQL命令本身，这大大减轻了网络负担。
•可将数据库的处理黑匣子化。应用程序中完全不用考虑存储过程的内部详细处理，只需要知道调用哪个存储过程就可以了。
三、数据控制语言(DCL)
●用户管理
MySQL中可创建不同的用户，并分配不同的权限，保证MySQL中数据的安全性
MySQL用户主要包括两种：
•root用户为超级管理员
拥有MySQL提供的所有权限
•普通用户：
权限取决于该用户在创建时被赋予的权限有哪些，没有赋予任何权限，只有可以登录mysql的权限
用户表存在于mysql库中的user表中，要先选择mysql库再进行操作
•语法格式：
//查询所有用户
use mysql;
select * from user;
//创建用户
create user’用户名'@'主机名’identified by‘密码’；
//主机名：localhost 只能本机连接 % 所有都可以连接
//修改用户密码
alter user‘用户名'@'主机名’identified by‘密码’；
//删除用户
drop user‘用户名’@'主机名’；
●权限管理
•MySQL通过权限管理机制可以给不同的用户授予不同的权限，从而确保数据库中数据的安全性
•只能给存在的用户授权
•新创建的用户只有登录的权限(USAGE)
•语法格式：
//为某个用户授权
grant 权限1，权限2... on 数据库名.表名 to‘用户名'@”主机名’；
//查看某个用户有哪些权限
show grants for'用户名’@'主机名’；
//撤销用户权限
revoke 权限1，... on 数据库名.表名from‘用户名’@’主机名’；
//刷新权限(添加，撤销授权之后一定要刷新权限)
flush privileges;
●角色管理
MySQL数据库中通常都会出现多个拥有相同权限集合的用户，在之前版本中只有分别向用户授予权限和撤销权限.在用户数量比较多的时候，这样的操作是非常耗时的。
MySQL8.0为了用户权限管理更容易，提供了一个角色管理的新功能。角色是指定的权限集合，和用户帐户一样可以对角色进行权限的授予和撤销。
如果用户被授予角色，则该用户就拥有了该角色的权限。
●语法格式：
//创建角色
create role‘角色名称’，..；
//为角色授权
grant 权限1，权限2，... on 数据库名.表名to‘角色名称’；
//为用户分配角色
grant‘角色名称’to‘用户名'@'主机名’；
//撤销角色的权限
revoke 权限1，... on 数据库名.表名from‘角色名称’；
●开启远程连接
语法格式：
//创建用户
create user 'cy'@'%’identified by'123456';
//授权
#grant all on *.to 'cy'@'%';
grant select on *.* to 'cy'@ '%';
//刷新权限
flush privileges;
四、数据库设计
●数据库三范式
三范式就是设计数据库的规则，是符合某一种设计要求的总结
•为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式
•满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的 称为第二范式(2NF)，其余范式以此类推。一般说来，数据库只需满足第三范式(3NF)就行了
●第一范式 1NE
原子性，做到列不可拆分
第一范式是最基本的范式。数据库表里面字段都是单一属性的，不可再分，如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式
●第二范式2NE
•在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关
•一张表只能描述一件事
●第三范式3NE
• 消除传递依赖
•表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放
五、MySQL的日志类型
日志是MySQL数据库的重要组成部分。日志文件中记录着MySQL数据库运行期间发生的变化,记录MySQL数据库的客户端连接状况、SQL语句的执行情况和错误信息等。
当数据库遭到意外的损坏时，可以通过日志查看文件出错的原因，并且可以通过日志文件进行数据恢复。
●日志分类
MySQL官方文档中说明MySQL共有六种日志类型。
•错误日志(Errorlog)：记录MySQL运行过程中较为严重的警告和错误信息，以及MySQL启动和关闭的详细信息。
•通用查询日志(General query log)：记录建立的客户端连接和执行的语句。可以通过show variables like"%general%'查看通用查询日志是否开启，默认是不开启的。
•二进制日志(Binarylog)：包含所有更新数据或表结构(新增、删除、修改、改表等)SQL信息的记录。可通过show variables like '%log_bin%";查看，由于文件是二进制文件，所以是无法直接使用记事本查看的。在搭建MySQL主从时就要求开启二进制日志。
•中继日志(Relay log)：在MySQL主从复制实现中，记录从数据库数据改变。
•慢查询日志(Slow query log)：记录所有执行时间超过long_query_time秒的所有查询或不适用于索引的查询。可通过show variables like'%quer%；查看。
•DDL日志(DDLlog)：DDL日志或元数据日志记录由数据定义语句(如DROPTABLE和ALTERTABLE)生成的元数据操作。元数据操作的记录被写入MySQL数据目录(datadir)中的文件ddLlog.log中。这是一个二进制文件。在实际需要记录元数据语句之前，不会创建ddLlog.log，并在mysqld成功启动后被删除。
六、MySQL的执行计划
就是SQL在数据库中执行时的表现情况，通常用于SQL性能分析、优化等场景。
●优化与执行
MySQL会对SQL进行解析，解析为MySQL支持的数据结构(解析树)，并对其进行各种优化，表的读取顺序、选择合适的索引等。
●id: select的序列号，有几个select就有几个id，并且id是按照select出现的顺序增长的，id列的值越大优先级越高，id相同则是按照执行计划列从上往下执行，id为空则是最后执行。
●select_type:表示查询的类型，常用的值如下：
•SIMPLE；表示查询语句中不包括子查询或union
•PRIMARY：表示此查询是最外层的查询
•UNION：表示此查询是UNION的第二个或后续的查询
•UNION RESULT:UNION的结果
•SUBQUERY:SELECT子查询语句
•最常见的查询类型是SIMPLE，表示我们的查询没有子查询也没有用到UNION查询
●table:表示当前行访问的是哪张表。当有union查询时，UNION RESULT的table列的值为<union1，2>，1和2表示参与union的行id。partitions:查询将匹配记录的分区。对于非分区表，该值为NULL。
●type：表示存储引擎查询数据时采用的方式，是比较重要的一个属性。通过它可以判断出查询是全表扫描还是基于索引的扫描。常见的值如下：
•system：逻辑表中限制只返回第一行数据，属于const的特例。
•const：表最多有一个匹配行。因为只有一行，只读一次，const查询速度非常快。一般情况下把主键或唯一索引作为唯一条件的查询都是const。
•oeq_ref：经常出现在表连接中，另外一张表中只有一条数据与之匹配。但是需要注意，如果另一个表中所有行都被读取到了，就是ALL了。
•ref：查询时，使用非唯一索引或主键索引时，其他的索引作为查询条件。
•fulltext:只要是全文索引使用的就是fulltext类型。
•ref_or_null：以外键列作为条件，搜索时包含null值。
注意：只搜索外键列是null为ref
•range：把这个列当作条件只检索其中一个范围。常见where从句中出现between、<、in等。主要应用在具有索引的列中。
•index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，因为索引文件通常比数据文件小。(Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取)
•ALL：Full Table Scan，遍历全表以找到匹配的行
●possible_keys：表示查询时能够使用到的索引，注意并不一定会真正使用，显示的是索引名称
●key：表示查询时真正使用到的索引，显示的是索引名称
●rows：MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率的高低
●key_len：表示索引字段的最大可能长度，并非实际使用长度，即key_Jen是根据表定义计算而得，并不是通过表内检索出的
●Extra：表示额外的信息
七、MySOL的优化
●MySQL优化主要从两方面入手：碑件级别(Hardware Level)和数据库级别(Database Level)。
•硬件方面的优化
①磁盘寻找：磁盘需要一段时间才能找到一段数据。优化查找时间的方法是将数据分发到一个以上的磁盘上。
②磁盘读写：当磁盘处于正确位置时，读取或写入数据。
③CPU：当数据在主存储器中时，必须处理它以得到结果。与内存量相比，具有大数据库量的表是最常见的限制因素。
④存储带宽：当CPU需要比CPU缓存中更多的数据时，主存储器带宽成为瓶颈。
•软件(数据库)方面的优化
①表结构是否正确。特别的是列是否具有正确的类型，和表中列的个数是否正确。而对于需要进行大量分析的应用通常设计更少的表，而每个表的列更多一些。
②正确的设置索引达到查询高效。
③对于不同情况选择不同的存储引擎。选择不同的存储引擎对性能和可伸缩性具有较大的影响。
④每张表是否具有适当的行格式。主要取决于适当的存储引擎。压缩表可以占用更低的磁盘空间和更少的1/O操作。
⑤应用程序是否使用适当的锁策略。在具有高并发、分布式应用程序中，选择适当的锁策略以保证数据的共享性和特定的情况下独占数据。
⑤所有缓存区域使用的大小是否都正确。配置的原则是缓存区域大到足以容纳所有频繁访问的数据，但又不能太大，否则导致过量占用物理内存而导致分页。
●优化可能带来的问题
•优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统。
•优化手段本来就有很大的风险，只不过你没能力意识到和预见到。
•任何的技术可以解决一个问题，但必然存在带来一个问题的风险。
•对于优化来说解决问题而带来的问题，控制在可接受的范围内才是有成果。
●优化的需求
•稳定性和业务可持续性，通常比性能更重要。
•优化不可避免涉及到变更，变更就有风险。
•优化使性能变好，维持和变差是等概率事件。
•切记优化,应该是各部门协同，共同参与的工作，任何单一部门都不能对数据库进行优化。
八、SQL优化
●表设计
•NULL值
可能出现“无法预料的结果”。所以在建立表时给列添加not null 约束或default默认值是非常好的优化手段。
•字段类型选择
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
•复合索引使用
在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
•避免全表扫描
对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引。
●select 字段列表
任何地方都不要使用select*from表，用具体的字段列表代替“*”，不要返回用不到的任何字段。
●where条件部分
•避免负向条件
应尽量避免在where 子句中使用负向条件操作符，否则引擎将放弃使用索引而进行全表扫描。负向条件有： not like、not in、not exists、！=、<>、等。
•避免使用or逻辑
应尽量避免在 where 子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。
•避免查询条件中字段计算
应尽量避免在where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
•避免查询条件中对字段进行函数操作
应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
•WHERE子句“=”左边注意点
不要在where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
•注意模糊查询，模糊查询如果是必要条件时，可以使用select id from t where name like 'abc%来实现模糊查询，此时索引将被使用。
•索引也可能失效
并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
九、数据库锁
锁是用于管理不同事务对共享资源的并发访问，保证数据的完整性和一致性。
在数据库中，锁保护的对象是事务，锁持续的时间是整个事务过程，只有在整个事务commit之后，锁才会释放，也就是说如果当前事务执行时间很长，锁将会被一直持有。
●锁的分类
MySQL中锁按照不同分类标准有多种分类。
按照锁的级别分为：共享锁、排它锁。这也是最根本的分类标准。
按照锁的目标分为：行级锁、表级锁。锁影响到整个表叫做表级锁。锁影响到某一行或某几行叫做行级锁。
按照锁是否自动添加分为：自动锁、显示锁。MySQLInnoDB引擎会自动对Insert、Delete、Update添加排它锁，Select操作不会自动添 加锁，但可以通过命令添加锁。
一个锁操作可能即是共享锁，又是行级锁，有是显示锁。
•共享锁又称读锁，表示在读取数据时不允许其他事务对数据进行修改。
SQL：select.... lock in share mode;
•排它锁又称独占锁。在一个事务操作时，其他事务不允许操作数据。
SQL:select .... for update;
十、Java数据库连接技术-JDBC
•JDBC(Java Data Base Connectivity)，是Java连接数据库的技术。·是一种执行SQL的API，可以为多种关系型数据库提供统一的访问。.它是由一组用java语言编写的类和接口组成，是Java访问数据库的标准规范。
JDBC的用处：希望使用统一的一套代码，就可以操作所有的关系型数据库
•好处：
①面向接口编程
②代码不依赖于任何的数据库
③只需要稍作修改，就可以切换到其它数据
●数据库驱动程序
•数据库驱动就是直接操作数据库的一个程序
•不同数据产品的数据库驱动名字有差异
•在程序中需要依赖数据库驱动来完成对数据库的操作
●使用JDBC增删改数据：
•加载驱动
•获取连接
•获取发送执行器
•将sql发送到数据库执行，返回影响的行数
executeUpdate()执行增删改操作 返回 影响的行数
•释放资源
●获取连接
JDBC提供了Connection接口,代表一个数据库连接接口
•MySQL通过DriverManager类中的静态方法getConnection可以获取连接
•url：连接数据库的地址
MySQL的格式为：jdbc:mysql://ip地址：端口号/数据库名
•user：数据库用户名
•password：数据库密码
•JDBC规定url的格式由三部分组成，每个部分中间使用冒号分隔
第一部分是协议 jdbc，这是固定的
第二部分是子协议，就是数据库名称
第三部分是由数据库厂商规定的，mysql的第三部分分别由数据库服务器的IP地址、端口号，以及要使用的数据库名称组成。
●资源的释放
•需要释放的对象：ResultSet结果集，Statement语句，Connection连接
•ResultSet结果集：当它的 Statement关闭、重新执行或用于从多结果序列中获取下一个结果时，该ResultSet将被自动关闭
•释放原则：先开的后关，后开的先关Statement ==> Connection
十一、ORM编程思想
对象关系映射，是一种为了解决面向对象语言与关系数据库存在的互不匹配的现象。
•实体类
实体类是一个定义了属性，拥有getter、setter、无参构造方法(基本必备)的一个类。
实体类可以在数据传输过程中对数据进行封装，能存储、传输数据，能管理数据。
十二、PreparedStatement预处理对象
●PreparedStatement接口
•PreparedStatement是Statement接口的子接口，继承于父接口中所有的方法。它是一个预编译的SQL语句对象。
•预编译：是指SQL 语句被预编译，并存储在PreparedStatement对象中。然后可以使用此对象多次高效地执行该语句。
●PreparedStatement特点
•因为有预先编译的功能，提高SQL的执行效率
•可以有效的防止SQL 注入的问题，安全性更高
●获取PreparedStatement对象
通过Connection创建PreparedStatement对象。
●PreparedStatement常用方法
•int executeUpdate() 执行insert、delete、update语句
•ResultSet executeQuery0 执行select语句，返回ResultSet结果集对象
●如何使用PreparedStatement
•编写SQL语句，未知内容使用？占位
String sql="select * from jdbc_user where username=? and password=?";
•获取PreparedStatement对象
•设置实际参数：setXxx(占位符的位置，真实的值)
•执行SQL
●JDBC提供的批处理功能(Batch)。
•批处理是指将关联的SQL语句组合成一个批处理，并将他们当成一次调用提交给数据库，一次发送多个SQL语句到数据库，可以减少通信的资源消耗，从而提高了性能
•executeBatch()方法用于启动执行所有组合在一起的语句。
•executeBatch()方法返回一个整数数组，数组中的每个元素代表了各自的影响行数
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
