MySQL入门

本文介绍了什么是sql和nosql以及两者区别；介绍了mysql的特点、函数和SQL语句操作数据库；讲解了mysql事务及ACID原则和事务的4种隔离级别，索引的作用和原理；mysql的权限管理和数据备份恢复；数据库的设计和三大范式；SQL注入问题和避免；JDBC以及DBCP连接池连接数据库。

sql与nosql

关系型数据库SQL是使用关系模式组织数据的结构化数据库系统。

非关系型数据库（NoSQL）是一类用于存储和管理非结构化数据的数据库系统。

关系数据库和非关系数据库区别体现在三个方面：数据存储方式不同；扩展方式不同；对事务性的支持不同。具体可参考：区别

关系型数据库的实现：Mysql，SQLserver，Oracle等。

非关系数据库实现：Redis（键值对），Mongodb（文档型），hbase（列存储），neo4j（Graph图像）。

mysql

SQL语言的特点：高度非过程化、面向集合操作、可移植性强、简洁灵活、支持三级模式结构、ACID（原子性，一致性，隔离性，持久性）。

SQL语言支持三级模式结构，通过外模式对应视图、基本表对应存储文件，以及内模式对应存储文件的方式实现。

Mysql默认不支持中文，在my.ini中配置编码character-set-server=utf8或，编写sql代码时加上DEFAULT CHARSET=UTF8。Mysql默认localhost本地连接，root为最高权限用户，默认数据库引擎为Innodb（支持事务，行级锁，外键，但是占用空间大）。

注释：–，/**/

数据库数值类型：tinyint(1byte)、smallint(2byte)、int(4byte)、float(4byte)、double(8byte)、decimal、varchar(0-65535)、text文本串、null

date：YYYY-MM-DD

time: HH:mm:ss

datetime：YYYY-MMM-DD HH:mm:ss

timestamp：时间戳，1970到现在的毫秒数

文档：reference

DDL定义

数据定义语言（DDL）：create，drop，alter

创建数据库：create database 数据库名

切换数据库：USE DATABASE

修改表：alter table '表名' rename as xxx

增加字段：alter table '表名' add Phone INT(11)

修改字段：alter table '表名' modify Phone VARCHAR(11)；

修改字段名：alter table '表名' change Phone Phone1 INT(11)

删除字段：alter table '表名' drop Phone INT(11)

--创建表---
CREATE TABLE `categoryin`
(
categoryin_id INT(20) NOT NULL AUTO_INCREMENT COMMENT '收入分类id',
categoryin_name VARCHAR(20) CHARACTER SET utf8 COMMENT '收入分类名',
PRIMARY KEY (categoryin_id)
)
ENGINE=INNODB AUTO_INCREMENT=3 CHARACTER SET = utf8 COMMENT='收入分类';

DML操作管理

数据操作语言（DML）：insert , delete , update，truncate

insert into 表名 (字段1,字段2) values(值1，值2);

INSERT INTO `categoryin` VALUES (1,'工资');
INSERT INTO `categoryin` VALUES (2,'彩票');

update 表名 set '字段1'='值1','字段2'='值2' where id=xxx;

where条件运算符：=，!=(<>)， >，<，between and，and，or。

delete from 表名 where [条件]

清空表（自增变量归0）：truncate table '表名'

DQL查询

数据查询语言（DQL）：select

select 字段1,字段2 from 表名 as 别名

字段去重，select distinct '字段' from 表名

--查询系统版本
select version();
--计算
select 2*3;
--查询变量
select @@auto_increment_increment
--函数
select 函数

模糊查询：

select 字段 from 表名 where xxx like 'a__'

select 字段 from 表名 where xxx like '%a%'

select 字段 from 表名 where xxx in (xxx,xxx,xxx)

select 字段 from 表名 where xxx is 'xxx'

多表查询：

联接查询：join 表名 as 别名 on [条件]

select  mi.in_id, mi.single_in, mi.zhanghu_id,   		        	 mi.categoryin_id, mi.date, mi.beizhu,
        z.zhanghu_name,z.zhanghu_money,
        z.create_time,
        cin.categoryin_name
        from moneyin as mi
        join zhanghu as z
        on mi.zhanghu_id=z.zhanghu_id
        join categoryin cin
        on mi.categoryin_id=cin.categoryin_id

inner join：只返回两个表中联结字段相等的行。

left join：返回左表所有值，即使没有匹配(使用null填充)。

right join：返回右表所有值，即使没有匹配(使用null填充)。

注意顺序：

where指定结果满足条件；

group by按照哪个字段分组 ；

having过滤分组满足的次要条件；

order by排序；

limit分页。

分页limit和排序order by:

--ASC升序--DESC降序
where ....
order by xxx desc
limit 0,10
--limit 起始下标,pageSize页大小

子查询：

在where ..(select …)中嵌套一个查询

select sName
from Student
where sId in (select distinct sId from score where tId=(select tId from teacher where tName='Alice'))

DCL控制

数据控制语言（DCL）：grant，revoke 。

五个约束

主键约束（唯一约束）（Primay Key Counstraint）：唯一性，非空性。

唯一约束（Unique Counstraint）：唯一性，可以空，但只能有一个。

检查约束 (Check Counstraint)

alter table user
add constraint CK_Sex check (sex='男' or sex='女')

默认约束 (Default Counstraint)

alter table user
add constraint df_age default ('0') for age

外键约束 (Foreign Key Counstraint)

函数

usage: select [函数];

常用函数：

abs绝对值，floor向下取整，celling向上取整，rand产生0-1随机数，char_length字符串长度，concat拼接，sign符号函数，current_date当前日期，now当前时间，localtime本地时间，sysdate系统时间，md加密。

first：返回指定的字段中第一个记录的值；

last：返回指定的字段中最后一个记录的值；

len：返回文本字段中值的长度；

聚合函数(计算最大、最小、平均值)：

count计数

select count(字段或p-k) from stduent;--忽略null
select count(*) from student;--不会忽略null
select count(1) from student;--不忽略null，本质：计算行数

sum求和；avg求平均；max；min

select sum(‘字段’) as 总和 from 表;
select avg(‘字段’) as 平均 from 表;
select max(‘字段’) as 最大 from 表;
select min(‘字段’) as 最小 from 表;

--查询不同课程的平均分>80，最高分，最低分
select subjectName,avg(studentResult) as 平均分,max(studentResult) as 最高分,min(studentResult) as 最低分 
from result r
inner join 'subject' sub 
on r.'subjectNo'=sub.'subjectNo'
--根据不同课程分组
group by r.'subjectNo'
having 平均分>80

事务

数据库事务(Transaction)：指由一个或多个数据库操作组成的逻辑单位。

mysql默认开启事务自动提交！

set autocommit=0;设置手动提交事务。

start transaction开启事务。

commit事务提交，事务一旦提交，数据将持久化，回滚也无法恢复。

rollback事务回滚。

事务原则ACID

ACID（原子性，一致性，隔离性，持久性）。

原子性atomic：事务要么所有的操作都执行成功,要么所有的操作都不执行,保证数据库的一致性和完整性。

一致性consistency：事务完成后符合逻辑运算，数据前后状态要一致。

持久性durability：事务没有提交则数据恢复原状，事务提交则数据不可逆，不会因宕机外部因素丢失数据。

隔离性isolation：排除其他事务对本次事务的影响。

事务隔离级别

脏读：一个事务读取另一个事务未提交的数据。

不可重复读：一次事务内多次读取某行的数据不同。

虚读(幻读)：一次事务中读取了其他事务插入的数据。

4 种隔离级别：读未提交 (READ UNCOMMITTED)、读已提交 (READ COMMITTED)、可重复读 (REPEATABLE READ)、串行化 (SERIALIZABLE)。

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读提交	不可能	可能	可能
可重复读	不可能	不可能	可能
串行化	不可能	不可能	不可能

索引

索引定义：：Index是帮助MySQL高效获取数据的数据结构。
索引作用：通过索引可以大大的提高数据库的检索速度，提高数据库的性能。

索引类型

唯一索引unique：唯一，可重复(多个列都可以标识为unique)

主键索引primary：唯一，不可重复(一张表只能有一个)。定义表主键的时候，会自动创建主键索引。

常规索引key：默认

全文索引FullText：快速定位数据。

--1、创建表时添加索引
--2、alter 
alter table 表名 add Index_TYPE index '索引名'('字段');

--3、create
-- 索引名：id_表名_字段名
create index 索引名 on 表('字段');

聚集索引clustered index：表中各行的物理顺序与键值的逻辑顺序相同，每个表只能有一个。在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引。

非聚集索引：非聚集索引指定表的逻辑顺序，数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。

一个表只能创建1个聚集索引，多个非聚集索引。设置某列为主键，该列就默认为聚集索引。

索引数据结构：InnoDB使用聚集索引，数据记录本身被存于一棵B+树的叶子节点上，同一个叶子节点内的各条数据记录按主键顺序存放。每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子，则开辟一个新的页（节点）。

权限与DCL

数据库mysql中user表记录了root、mysql.sys、mysql.session等用户的操作权限、访问主机host（默认localhost，只能本地连接）和各自密码(加密)。其中，root用户为最高权限用户，只有root有grant授权权限。

创建用户：create user xxx identified by 'PASSWORD';

删除用户：drop user xxx;

修改密码：update user set password='123456' where user='root';

set password for root = password('123456');

修改当前用户密码：set password = password('123456');

重命名用户：rename user xxx to xx;

数据控制语言（DCL）：grant，revoke 。

授权：grant 某权限[all privileges] on 库.表 to 用户;

查看权限：show grants for xxx

特殊的show grants for root@localhost

撤销权限：revoke 权限[privileges] on 库.表(*.*) from 用户;

当出现1130-host “xxx” is not allowed to connetced to this Mysql错误提示，表示访问拒绝非本地连接，需要修改host为%，可执行以下步骤。

--1. use mysql;
--2. update user set host='%' where user='root';
--3. flush privileges;
/*在不重启mysql下使修改生效*/

备份恢复

备份

方式一、物理拷贝

直接将mysql中的data目录下的文件数据拷贝出去。

方式二、mysqldump命令

mysqldump -h 主机 -u 用户 -p 密码 数据库 表名> 导出位置/xx.sql

mysqldump -h 主机 -u 用户 -p 密码 数据库> 导出位置/xx.sql

例：mysqldump -u root -p 123456 mydb > /home/db.bak

方式三、sqlyog可视化导出转储

恢复

登录数据库，使用source命令导入备份的sql或bak数据。

source 备份文件

或未登录下

mysqldump -h 主机 -u 用户 -p 密码 数据库< xx.sql

数据库设计

良好的设计将节省内存，提高查询速度，便于系统开发。

注意点：表名命名清晰、表结构合理、表关系(实体关系)设计、字段命名规范(不要大小写，统一小写加下划线)、字段类型及长度、主键设计、数据库引擎使用(通常为innodb)、字符集规范utf8。

三大范式

数据库三大范式包含：

**第一范式(1NF)**：属性不可分割，每一列都是不可分的原子数据项。

**第二范式(2NF)**：满足第一范式，且不存在部分依赖，每一列必须和主键相关。非码属性必须完全依赖于候选码。

**第三范式(3NF)**：满足第二范式，且不存在传递依赖，每一列必须和主键直接相关，不存在间接相关。任何非主属性不依赖于其它非主属性，即在2NF基础上消除传递依赖。

设计注意事项：关联性查询最好不要超过三张表，设计时应该考虑性能与规范的问题。

JDBC

Java DataBase Connectivity（Java数据库连接），是 JavaEE 平台下的技术规范， 定义了在 Java 语言中连接数据库，执行数据库操作的标准 ，可以为多种关系数据库提供统一访问，其本质是sun公司制定的一套接口。

因为每一个数据库的底层实现原理都不一样，每一个数据库产品都有自己独特的实现原理！所以为了方便java的开发，制定一个统一的连接数据库的规范势在必行，这就是JDBC。只要数据库厂商面向JDBC编写驱动，就能让开发者使用JDBC方便地操作各种数据库。

其中，java.sql.*；javax.sql.*里定义了与数据库操作有关的接口和方法。mysql-connector-java-8.0.11.jar包是标准JDBC的驱动程序，用于连接数据库。JdbcUtils工具类封装加载驱动，获取连接，释放资源。

//加载驱动，执行静态代码块
Class.forName("com.mysql.jdbc.Driver");
//url，数据库信息
String url="jdbc:mysql://localhost:3360/数据库?useSSL=true&
   characterEncoding=utf-8 & serverTimezone=Asia/Shanghai";
String user="root";
String password="123456";
//数据库连接对象
Connection connection =
DriverManager.getConnection(url,user,password);
//定义statement对象
Statement statement=connection.createStatement();
String sql = "select * from user";
//定义结果集
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
 System.ou.println("id="+resultSet.getObject("id"));
}
//释放连接
resultSet.close();
statement.close();
connection.close();
//jdbcUtils.release(connetcion,statement,resultSet);

oracle数据库连接：jdbc:oracle:thin:@//<host>:<port>/<SERVICE_NAME>

sid连接：jdbc:oracle:thin:@localhost:1521:sid

JDBC参考资料

SQL注入问题

指攻击者在查询信息的结尾上添加额外的SQL语句以实现非法操作，执行非授权的任意查询，实质是web应用对用户输入的数据合法性判断不严。

Statement 采取直接编译 SQL 语句的方式，扔给数据库去执行，而 PreparedStatement 则先将 SQL 语句预编译一遍，再填充参数，这样效率会高一些。

Statement对象可能进行字符串与变量的拼接，很容易进行 SQL 注入攻击。采用PreparedStatement访问数据库不仅能防止sql注入，还是预编译的，不用重新编译整个sql语句，效率高。此外执行查询语句得到的结果集是离线的，连接关闭后，仍然可以访问结果集。

注意：PreparedStatement防止sql注入实质是将传入的参数看作字符串处理。${}是拼接，#{}整体当做字符串处理。

//删除表中id=1且name=admin的记录

Class.forName(com.mysql.jdbc.Driver);     
Connection con = DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/xxx..."); 
//以？作为占位符，值待设置
String sql = "delete from user where id=? and name=?";
//创建PreparedStatement时就传入sql语句，实现了预编译 
PreparedStatement p = con.prepareStatement(sql);    
//传入参数
p.setString(1,"1");
p.setString(2,"admin");
//设置sql语句的占位符的值，注意第一个参数位置是1不是0
p.execute(); //执行

同时，mybatis-plus框架也会进行sql的预编译。Mybatis（plus）使用参数化查询，#{xxx} 是参数化查询的占位符，它将传入的参数插入到占位符中，而不会将其解释为 SQL 代码。在自定义查询语句时，为确保参数化查询，须使用 @Param 注解将参数传递给 sql查询。这些措施让Mybatis-plus能防止sql注入。

文章：PreparedStatement防止SQL注入原因

​ MybatisPlus如何解决SQL注入

​ Mybatis-plus sql注入及防止sql注入详解

DBCP数据库连接池

DBCP连接池提高资源利用率，避免重复创建和销毁连接资源，减少系统开销。使用DBCP连接池后，免去了编写连接数据库的代码。使用DBCP需要导入dbcp相关jar包，导入依赖到项目中。

commons-dbcp-1.4.jar
commons-pool-1.5.4jar

创建多个dbcp.properties文件，存储多个不同的连接池

连接池的工作原理：
①使用连接池前：用jdbc连接数据库时，先getConnection获取连接，用完后要release释放连接，再次连接重复前两步，对程序性能影响很大。
②使用连接池后：在程序开始之前，先创建几个连接，将连接放入到连接池中，连接池中缓存了一定量的Connection对象，当用户需要使用连接时，从连接池中获取，使用完毕之后将连接还回连接池。

连接池的种类：DBCP，C3P0，Druid，Tmcat内置连接池等。

dhcp配置:

#导入dbcp的配置文件dbcp.properties
driverclassName=com.mysql.jbcp.Driver
url=jdbc:mysql://localhost:3306/xxx?useUnicode=true
&characterEncoding=utf-8
user=root
password=123456
initialSize=10 #初始连接数量
maxActive=50 #最大活跃连接数量
maxIdle=20 #最大空间连接数
minIdle=5 #最小空间连接数
maxWait=60000 
#最大等待时间ms（毫秒），从连接池获取连接，如果连接池空了，等待的最大时间
connectionProperties=useUnicode=true;characterEncoding=utf-8
defaultAutoCommit=true
defaultTransactionIsolation=READ_COMMITTED

InputStream in = xxx.class.getClassLoader().
    getResourceAsStream("dbcp.properties");
Properties pro = new Properties();
pro.load(in);
//声明连接池
static DataSource datasource=null;
//创建连接池
datasource=BasicDataSourceFactory.createDataSource(pro);
//从连接池获取连接
conn=datasource.getConnection()；

DBCP参考资料