知识梳理：数据库(Mysql)基本命令&SQL语句&视图&事务&存储过程&函数&反射&Java内存模型&数据库设计三大范式weixin47255857的博客-

目录：数据库(Mysql)基本命令&SQL语句&视图&事务&存储过程&函数&反射&Java内存模型&数据库设计三大范式

数据库

1. 查看mysql数据库服务器中的所有实例

   show databases; 

2. 创建mysql实例

   create database mydb; 

3. 查看创建实例的命令

   show create database mydb; 

4. 显示创建表的命令

   show create table emp; 

5. 使用数据库实例

   use mydb; 

6. 当输入了错误命令，需要取消时

   show databaes xxx c 

   c表示取消当前的输入

7. 显示当前数据库实例的编码信息

   show variables like '%char%'; 

   ‘%char%’ ：通配符，表示匹配包含了’char’字符的数据

8. 退出mysql命令行

   #方式一 exit； #方式二 quit； 

9. 显示实例下的所有表(前提先use过了)

   show tables; 

SQL语句入门

1. DDL语句（数据定义语句）**

   数据定义语句主要应用于对数据表的结构操作：比如建表，删除表，修改表的结构等；DDL语句包含以下命令：

   • create
   • drop
   • alter
   • add
   • mofidy

2. DML语句（数据操作语句）

   数据操作语句一般用于对的语句数据库表中数据进行更新操作，比如添加，删除，修改数据等，DML语句包含以下命令:

   • insert
   • update
   • delete

create table emp       -- 创建表，名称叫emp (     eno int, --创建eno列，类型是int     ename varchar(30), --创建ename列，类型是varchar长度是30字符     job varchar(30),     sex char(2) default '1', --创建sex列，类型是char     sal double,     birth date ); 

关于mysql中字符长度问题：

如果是utf8编码下，默认的中文字符占3个字节；如果是gbk编码，默认的中文占2个字节

关于char类型和varchar类型：

• char类型是定长字符串，类似于java中String；长度一旦定义则无法改变，并且无论数据是否够指定长度，都会占满，不够的用空格填充；char类型一般应用于长度确定的字符串类型，比如：性别，手机号，身份证号等；
• varchar类型是可变长度字符串，类似java中StringBuffer；长度定义之后会根据实际填充的内容，选择占用多大空间，比较节省空间；varchar类型一般使用在长度不确定的字符串类型，比如:姓名，邮箱地址，评论信息等。

修改表结构

1. 新增一个列

   alter table emp add hiredate date; 

2. 删除列

   alter table emp drop column birth; 

3. 修改列的类型

    alter table emp modify sal varchar(20); 

4. 修改列名称

   alter table emp change job  ejob varchar(30); 

5. 修改表名称

    alter table emp rename to tbemp; 

DML语句

1. 显示表中所有的数据

   select * from tbemp; 

insert语句（插入）

1. 向表中添加数据(所有列)

   insert into tbemp values(1,'james','manager','1',8796,'2018-01-22',28); 

2. 向某一些列插入值

   insert into tbemp(eno,ename,ejob,sal) values(4,'softeem','CEO',1500000); 

3. 同时插入多条数据

   insert into tbemp(eno,ename) values(5,'来福'),(6,'狗蛋'),(7,'王二狗'); 

   插入数据出现中文乱码时解决方案：

   由于安装mysql使用的是UTF8编码，但是cmd命令行中默认为GBK编码，因此在命令行中

   使用中文数据是会出现乱码；解决方式只需要在打开cmd命令行时输入以下命令：

   • set names gbk；

   然后再进行插入操作即可解决（但是以上修改只对一次会话生效；如果只会再次使用cmd需要重新设置）

update语句（更新）

1. 修改表中的一条记录

   update tbemp  set hiredate='2006-03-01',age=45 where eno=4; 

2. 修改数据(将员工姓名叫旺财的人的月薪提高2000)

   update tbemp set sal=sal+2000 where ename='旺财'; 

3. 修改数据(将员工生日为null的人的生日全部设置为2020-01-01)

    update tbemp set hiredate='2020-01-01' where hiredate is null; 

   注意事项：

   数据库中是否为空（null）的判断不能使用“=”或“!=”应该使用 is null或者 is not null

delete语句（删除）

1. 删除一行指定id的数据

    delete from tbemp where eno=5; 

2. 删除所有月薪为null的员工

   delete from tbemp where sal is null; 

   注意事项:

   实际开发中，数据积累不容易，一般不会轻易使用物理删除；大多数时候会选择使用逻辑删除；所谓逻辑删除实际就是在表中增加一个标识列，通过该列的取值标记该列是否应该被查询到

   因此针对删除需求，可以在表中新增一列用于标记该列是否被删除

   alter table tbemp add isdel tinyint; 

mysqldump(备份)

1. 备份指定实例到指定目录中

mysqldump -uroot -p123456 mydb > d:/mydb.sql 

1. 从指定的sql文件中恢复备份数据

source d:/mydb.sql 

mysqladmin

1. 使用mysqladmin创建一个数据库实例

   mysqladmin -uroot -p123456 create mydb2 

2. 使用mysqladmin删除一个数据库实例

   mysqladmin -uroot -p123456 drop mydb2 

3. 修改密码

   mysqladmin -uroot -p password root 

   将root用的密码改为”root”

日期时间类型

获取当前的系统时间

select now() 

常用日期类型：

• date 用于表示日期 如：2020-01-01

• datetime 用于表示日期时间 如：2020-01-01 10:11:12

• timestamp 用于表示时间戳，格式等同datetime，支持默认值 CURRENT_TIMESTMP

  ​ 另外该类型也支持自动更新（on update CURRENT_TIMESTAMP）当前行 数据更新时，该列也会自动更新为最新的时间

注意事项：

• 一张表中只能有一个字段的timestamp可以设置默认值

字符函数

数值函数

日期函数

加密函数

复制表与数据复制

-- 复制表（同时复制数据） create table tbemp select * from emp; -- 复制表结构（只需要结构不要数据） create table tbemp select * from emp where 1=0; -- 复制数据(蠕虫复制) insert into tbemp(ename,job,hiredate,age,sex,sal,dno) select ename,job,hiredate,age,sex,sal,dno from tbemp; 

索引

​ 当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

​ 索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

索引分类

1、普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点

2、唯一性索引：索引列中的值必须是唯一的，但是允许为空值

3、主键索引(聚簇索引)：即表中主键列

4、全文索引：全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。

5、组合索引：
在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

创建普通索引

create index index_ename on tbemp(ename); 

创建唯一索引

create unique index index_username on tbuser(username); 

唯一索引要求被添加索引的列值必须唯一

创建组合索引

create index index_emp on tbemp(ename,sal,age); 

组合索引的使用一般在进行多条件查询时提升查询效率

视图

​ 视图实际上就是一张虚拟的表，视图是针对基表的一部分字段的缩影，一般用于对一些字段比较多的表，通过视图可以简化表结构；另外针对一些涉及到多表的查询操作时，建立视图可以简化查询语句；

创建语法

-- 创建视图 create view v$emp as select ename,job,hiredate,sal from emp; select * from v$emp; update v$emp set hiredate=now() where ename='孙悟空'; -- 复杂视图创建 create view v$details as select  e.eno,e.ename,e.job,e.hiredate,e.age,e.sal, d.dno,d.dname,d.tel, s.level from emp e,dept d,sallevel s where e.dno=d.dno and  e.sal between s.lowsal and s.hisal; select level from v$details where eno=6; -- 查询研发部所有人的薪资等级和部门信息 select level,ename from v$details where dname='研发部' 

注意事项：

视图不占据存储空间，只是一种逻辑存在（非物理存储）；只有在使用视图时才通过视图的定义，加载对应的数据

对视图的操作会影响基表(物理表，实际开发中一般针对视图做查询，避免基于视图做修改

索引提高查询速度，视图简化了查询的方式

事务特性

数据库事务包含四大特征(ACID)：

1. 原子性：对于事务中的多次更新操作要么同时成功，要么同时失败
2. 一致性：保证事务操作完成之后，所有的结果一致
3. 隔离性：事物之间各自独立存在相互不影响
4. 持久性：事务完成之后，确保所有的数据长期持久的存在

Mysql事务的使用

mysql中只有使用InnoDB引擎才支持事务；MyISAM引擎不支持事务

-- 开启事务(事务一旦开，后续的所有更新操作都在这个事务中，直到提交后才会对物理表产生影响) start transaction; -- 设置保存点(在当前位置设置保存点，通过rollback to 保存点，可以回滚到该位置) savepoint p1; -- 回滚保存点（回滚到指定的保存点：一旦回滚，则当前保存点会撤销） rollback to p1; -- 回滚到事务开始的位置 rollback; -- 提交事务 commit; 

存储过程（Procedure）

-- 查询指定部门名的员工信息？如何使用存储过程实现 create procedure sp_emp1($dname varchar(30)) begin -- 声明临时变量 declare $dno int; -- 根据指定的部门名称查询到部门号并赋值到临时变量中 select dno into $dno from dept where dname=$dname; select * from emp where dno=$dno; end call sp_emp1('研发部'); -- 如何利用存储过程实现一个分页操作：输入一个每页数据行数和页码数，显示该页数据 create procedure sp_page(pagesize int,pagenum int) begin -- 声明临时变量 declare startNum int; -- 对变量赋值操作 set startNum = (pagenum - 1) * pagesize; select * from emp limit startNum,pagesize; end -- 删除存储过程 drop procedure sp_page; call sp_page(3,3) -- 针对以上分页操作，如何能够获取总页码数并且返回？ create procedure sp_page2(in pagesize int,in pagenum int,out pagecount int) begin -- 声明临时变量:开始查询位置 declare startNum int; -- 总记录行数 declare totalnum int; -- 对变量赋值操作 set startNum = (pagenum - 1) * pagesize; -- 查询总数据行数 select count(*) into totalnum from emp; -- 计算并获取总页码数 set pagecount = CEILING(totalnum/pagesize); select * from emp limit startNum,pagesize; end -- 调用包含输出参数的过程 call sp_page2(10,1,@pagecount); select @pagecount; 

自定义函数（Function）

-- 根据提供了员工姓名返回该员工的月薪 create function sal($ename varchar(30)) returns double begin declare $sal double; select sal into $sal from emp where ename=$ename; return $sal; end -- 调用函数 select sal('孙悟空') 

存储过程与函数区别：

1）一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。存储过程，功能强大，可以执行包括修改表等一系列数据库操作；用户定义函数不能用于执行一组修改全局数据库状态的操作。

2）对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类，存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURNS语句。

3）存储过程，可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数。

4）存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。

数据库设计三大范式

第一范式

要求建立的数据库表中所有的列是原子的，每一列不可再拆分；目前的关系型数据库默认都是满足第一范式（不可能创建出不满足第一范式的数据表）

第一范式：列不可再分

观察以上表，满足第一范式，但是存在以下问题：

1. 大量的数据冗余
2. 进行插入操作时会出现插入的异常
3. 在删除操作时会将一些不能删除列也一并删除(删除异常)

第二范式

​ 数据库表中不存在非关键字段(主键)对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字(联合主键)中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。

• 关键字段: 主键
• 组合关键字：联合主键
• 函数依赖：字段A->字段B，那么B依赖A，称之为函数依赖
• 部分依赖：(字段A，字段B)->字段C ，字段C部分依赖A和B，称之为部分依赖
• 完全依赖：要求非联合主键的字段完全依赖主键，或者字段完全依赖联合主键

第二范式：在满足第一范式的情况下，表中的非主键列对主键列必须完全依赖

经过第二范式的规范，数据冗余的问题解决了，但是：

1. 进行插入操作时会出现插入的异常
2. 在删除操作时会将一些不能删除列也一并删除(删除异常)

两个问题还是存在

第三范式

​ 在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在”A → B → C”的决定关系，则C传递函数依赖于A

• 传递函数依赖：表中的列存在A决定B，B决定C的关系

第三范式: 满足第二范式的基础上，要求表中列不能存在传递依赖关系

范式总结

​ 在实际开发中，一般情况只要满足三大范式即可；另外，由于程序对查询的需求(处于便捷性考虑)可能会出现违背三大范式的情况；因此三大范式只是设计数据时候的一种参考，并不是定律。

范式的存在主要解决了：

1. 数据冗余
2. 更新(insert,delete)操作异常

反射

​ java是一门静态语言，在操作类中的成分时都需要先获得对象，对于变量来说需要在编译期间确定类型，而且在运行期间不能修改变量类型，相比一些其他语言(javascript，ruby等)来说不具备动态性。但是Java提供了一种称之为反射的机制，能够让我们在运行期间动态的获取到类的成分(属性，方法，构造器，注解等信息)。

获取Class对象

需要使用java的反射机制，首先需要获取到类的Class对象，获取Class对象包含三种方式：

1. Class.forName(类路径) Class.forName("com.softeem.entity.User")
2. 类名.class User.class
3. 对象.getClass() u.getClass()

获取Class中的成分

获取类中的属性

• getField(String fieldName) 根据属性的名称获取公共的属性对象（Field）
• getDeclaredField(String fieldName) 根据属性名获取指定的属性对象（忽略访问修饰符）
• getFields() 获取所有公开的属性
• geDeclaredFields() 获取所有的属性(忽略访问修饰符)

获取类中的方法

• Method getMethod(String methodName) 根据提供的方法名称获取公共的方法对象
• Method getDeclaredMethod(String methodName) 在不考虑访问修饰符的情况下获取方法对象
• Method[] getMethods() 获取所有公开的方法
• Method[] getDeclaredMethods() 在不考虑访问修饰符的情况下获取所有的方法对象

获取类中的构造器

通过反射除了可以获取类中的属性和方法之外，还能够获取构造器信息，相关的API跟属性和方法的获取方式一致，区别在于返回值类型是Constructor或者Constructor[].

Java内存

Java代码是运行在Java虚拟机(JVM)上的，Java虚拟机通过解释执行（解释器）或编译执行（编译器）来完成。Java内存模型分为5个部分： 

​ 方法区（Method Area），Java堆（Heap），Java栈（VM Stack），本地方法栈（Native Method Stack），程序计数器（PC 寄存器）

虚拟机栈（Stack）

​ 线程私有的，与线程在同一时间创建。管理JAVA方法执行的内存模型。每个方法执行时都会创建一个桢栈来存储方法的的变量表、操作数栈、动态链接方法、返回值、返回地址等信息。栈的大小决定了方法调用的可达深度（递归多少层次，或嵌套调用多少层其他方法，-Xss参数可以设置虚拟机栈大小）。栈的大小可以是固定的，或者是动态扩展的。如果请求的栈深度大于最大可用深度，则抛出StackOverflowError；如果栈是可动态扩展的，但没有内存空间支持扩展，则抛出OutOfMemoryError.

堆（Heap）

​ 线程共享的，存放所有对象实例和数组。垃圾回收的主要区域。可以分为新生代(Eden)、老年代(tenured)、永久代(PermGen，Java8废弃，替换为Metaspace：本地内存)。新生代用于存放刚创建的对象以及年轻的对象，如果对象一直没有被回收，生存得足够长，老年对象就会被移入永久区。

方法区（Method Area）

​ 线程共享的，用于存放被虚拟机加载的类的元数据信息：如常量、静态变量、即使编译器编译后的代码。也成为永久代。如果hotspot虚拟机确定一个类的定义信息不会被使用，也会将其回收。回收的基本条件至少有：所有该类的实例被回收，而且装载该类的ClassLoader被回收

程序计数器（Program Count Register）

​ 多线程时，当线程数超过CPU数量或CPU内核数量，线程之间就要根据时间片轮询抢夺CPU时间资源。因此每个线程有要有一个独立的程序计数器，记录下一条要运行的指令。线程私有的内存区域。如果执行的是JAVA方法，计数器记录正在执行的java字节码地址，如果执行的是native方法，则计数器为空。

本地方法区（Native Method Area）

​ 本地方法栈和JVM栈非常相似，它们之间的区别不过是jvm栈是为执行java方法服务，而本地方法栈是为jvm使用到对的本地方法服务(由C/C++编写)。HotSpot虚拟机中直接把本地方法栈和JVM栈合二为一了。

垃圾回收机制（GC）

回收算法

对于java堆中的垃圾对象，GC提供了几种回收策略（回收算法）:

• 标记清除法
• 复制算法
• 标记压缩法
• 分代收集

标记清除法（Mark-Sweep）

​ 从根节点开始标记所有可达对象，其余没标记的即为垃圾对象，执行清除。但回收后的空间是不连续的。

复制算法（Copying）

​ 为了解决Mark-Sweep算法的缺陷，Copying算法就被提了出来。它将可用内存按容量划分为大小相等的两块，每次只使用其中的一块。当这一块的内存用完了，就将还存活着的对象复制到另外一块上面，然后再把已使用的内存空间一次清理掉，这样一来就不容易出现内存碎片的问题。

标记压缩法（Mark-Compact）

​ 适合用于老年代的算法（存活对象多于垃圾对象）。标记后不复制，而是将存活对象压缩到内存的一端，然后清理边界外的所有对象。

分代收集法（Generational Collection）

分代收集算法是目前大部分JVM的垃圾收集器采用的算法。它的核心思想是根据对象存活的生命周期将内存划分为若干个不同的区域。一般情况下将堆区划分为老年代（Tenured Generation）和新生代（Young Generation），老年代的特点是每次垃圾收集时只有少量对象需要被回收，而新生代的特点是每次垃圾回收时都有大量的对象需要被回收，那么就可以根据不同代的特点采取最适合的收集算法。 

类加载机制

​ 类的加载指的是将类的.class文件中的二进制数据读入到内存中，将其放在运行时数据区的方法区内，然后在堆区创建一个java.lang.Class对象，用来封装类在方法区内的数据结构。类的加载的最终产品是位于堆区中的Class对象，Class对象封装了类在方法区内的数据结构，并且向Java程序员提供了访问方法区内的数据结构的接口；