1、什么是数据库?
DB:DataBase
概念:数据仓库,软件,安装在操作系统上!(windows、Linux……)!可以存储大量的数据,500万!
作用:存储数据,管理数据
2、数据库分类
关系型数据库(SQl):
- MySQL、Oracle、SQL Server、DB2
- 通过表和表之间,行和列之间的关系镜像数据的存储
非关系型数据库(NoSQL)Not Only:
- Redis、MongoDB
- 非关系型数据库,对象存储,通过对象自身的属性来决定
DBMS(数据库管理系统)
- 数据库的管理软件,科学有效的管理我们的数据,维护和获取数据;
- MySQL,数据库管理系统
3、zip方式安装mysql
zip下载地址:
1、解压后,将解压文件移动到想要的目录
2、配置环境变量(mysql安装目录的bin)
3、在根目录下创建my.ini文件
my.ini配置文件,内容如下:
[mysqld]
# 设置3306端口
port=3306# 设置mysql的安装目录,目录对应你自己的解压目录
basedir=D:\Program Files\mysql-5.7.17
# 设置mysql数据库的数据的存放目录,目录对应你自己的解压目录
datadir=D:\Program Files\mysql-5.7.17\data# 允许最大连接数
max_connections=200# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
4、安装mysql
以管理员方式打开cmd,并在cmd面板输入 mysqld --initialize-insecure
,对mysql数据库进行初始化
当执行完这条命令后我们回到mysql的解压目录发现多出了一个data目录
继续输入 mysqld install mysql5.7 (服务名自己取,我这里安装的是5.7版本) ,添加mysql服务
删除服务命令:sc delete mysql5.7
5、连接mysql
因为我们前面使用的是mysqld --initialize-insecure
这种方式对数据库进行初始化,这种方式初始化数据库第一次是没有密码的,我们输入 mysql -uroot -p -P3306
直接回车不用输入密码,修改密码,输入 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
(123456就是密码)
输入flush privileges;(刷新)
4、命令行基本命令(部分)
mysql -u root -p123456 --连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; --修改root用户密码
flush privileges; --刷新权限
show databases; --查看所有的数据库
use 50sql; --切换数据库
show tables; --查看数据库中所有的表
describe course; --显示数据库中表的信息
create database test; --创建数据库
DROP TABLE IF EXISTS `student`; //删除数据库表
//创建数据库表
CREATE TABLE `student` (
`sid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`sname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`sage` datetime NULL DEFAULT NULL,
`ssex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;ALTER TABLE teacher RENAME AS teacher1; --修改表名
ALTER TABLE teacher1 ADD `age` INT(11); --添加字段
ALTER TABLE teacher1 MODIFY `age` VARCHAR(11) --修改约束
ALTER TABLE teacher1 CHANGE `age` `age1` INT(11) --字段重命名
ALTER TABLE teacher1 DROP `age` --删除字段
5、mysql数据库引擎
MyISAM(早期) | InnoDB(默认) | |
事务支持 | × | √ |
数据行锁定 | × | √ |
外键约束 | × | √ |
全文索引 | √ | × |
表空间的大小 | 较小 | 较大,约为2倍 |
常规操作:
- MyISAM 节约空间,速度较快
- InnoDB 安全性高,事务的操作,多表多用户操作
在物理空间存在的位置
所有数据库文件都存在data目录下
本质是文件存储!
MySql引擎在物理文件上的区别
- InnoDB在在数据库表中只有一个*.frm文件,以及上级目录的ibdata1文件
- MyISAM对应文件:*.frm --表结构的定义文件
*.MYD --数据文件(data)
*.MYI --索引文件(index)
6、DML语言(数据管理语言)
插入
insert into `表名`(字段1, 字段2, ......) values(值1, 值2, ......)
更新
update `表名` set `字段名`=值 where 条件
删除
delete from `表名` where 条件
truncate `表名` --清空表数据
delete 和 TRUNCATE区别
相同点:都能删除数据,都不会删除表结构
不同:
TRUNCATE 重新设置自增列计数器会归零TRUNCATE 不会影响事务
DELETE删除的问题题,重启数据库,现象
- lnnoDB自增列会重1开始(存在内存当中的,断电即失)
- MylSAM继续从上一个自增量开始(存在文件中的,不会丢失)
7、DQL(数据查询语言)
基本语法: select 字段1 as 别名1,字段2,.... from `表名` as 表的别名
函数concat(a, b)拼接a和b字段
select concat(字段1,字段2)from `表名`
去重distinct
select distinct 字段1 from `表名`
where条件使用
and,or,!,between 值1 and 值2,in
模糊查询like,通配符'%'表示任意字符,通配符'_'表示一个字符
联表查询:
8、MySQL函数
函数now()用于返回当前的日期和时间,example:insert into user(id, name, create_time) values(1, 'zhangsan', now());
函数date_format()用于以指定的格式显示日期/时间,example:select name, date_format(birthday, '%Y/%m/%d %H:%i:%s') from user;
聚合函数
函数ifnull()用于处理NULL值。
ifnull(v1,v2),如果 v1 的值不为 NULL,则返回 v1,否则返回 v2
case when是流程控制语句,可以在SQL语句中使用case when来获取更加准确和直接的结果。
语法:
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
CASE WHEN [expr] THEN [result1]…ELSE [default] END
9、事务
事务原则:ACID,原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)
参考博客链接:事务ACID理解_dengjili的博客-CSDN博客_事务acid
原子性(Atomicity)
事务中的操作要么都成功,要么都失败
一致性(Consistency)
事务前后数据的完整性必须保存一致
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
持久性(Durability)
事务一旦提交了,数据被持久化了,不可改变。
事务的隔离级别
脏读:
指一个事务读取了另外一个事务未提交的数据。
不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
页面统计查询值
生成报表的时候,B有人转账进来300(B事务已经提交)
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据,导致前后读取数量总量不一致。
(一般是行影响,如下图所示:多了一行)
四种隔离级别设置
set transaction isolation level 事务级别; 设置事务隔离级别
select @@tx_isolation; 查询当前事务隔离级别
设置 | 描述 |
Serializable | 可避免脏读、不可重复读、虚读情况的发生。(串行化) |
Repeatable read | 可避免脏读、不可重复读情况的发生。(可重复读) |
Read committed | 可避免脏读情况发生。(读已提交) |
Read uncommitted | 最低级别,以上情况均无法保证。(读未提交) |
java
设置 | 描述 |
TRANSACTION_SERIALIZABLE | 指示不可以发生脏读、不可重复读和虚读的常量。 |
TRANSACTION_REPEATABLE_READ | 指示不可以发生脏读和不可重复读的常量;虚读可以发生。 |
TRANSACTION_READ_UNCOMMITTED | 指示可以发生脏读 (dirty read)、不可重复读和虚读 (phantom read) 的常量。 |
TRANSACTION_READ_COMMITTED | 指示不可以发生脏读的常量;不可重复读和虚读可以发生。 |
--mysql是默认开启事务自动提交的
set autocommit = 0; --关闭自动提交
set autocommit = 1; --开启自动提交(默认)
-- 查询当前控制台(客户端)当前事务级别
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
-- 设置当前控制台(客户端)当前事务级别
SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted;
SET SESSION TRANSACTION ISOLATION LEVEL read committed;
SET SESSION TRANSACTION ISOLATION LEVEL repeatable read;
SET SESSION TRANSACTION ISOLATION LEVEL serializable;
--开启事务
start transaction;
--提交事务,同时事务结束
commit;
--回退事务,回退操作,同时事务结束
rollback;
10、索引
MySQL官方对索引的定义为: 索引(Index) 是帮助MySQL高效获取数据的数据结构。
提取句子主干,就可以得到索引的本质: 索引是数据结构。
索引分类
主键索引(PRIMARY KEY)
唯一标识,主键不可重复,只能有一个主键索引
唯一索引(UINIQUE KEY)
避免重复的列出现,唯一索引可以重复,多个列都可以标识为唯一索引
常规索引(KEY/INDEX)
默认的,index/key关键字设置
全文索引(FULLTEXT)
在特定的数据库引擎下才存在,MyISAM
快速定位数据
基本语法
--显示所有的索引信息
SHO INDEX FROM 表名
--增加一个 索引名(列名)
ALTER TABLE 表名 ADD 索引类型 INDEX ‘studentName ( ' studentName );--CREATE INDEX 索引名 on 表(字段)
CREATE INDEX id app user name ON app user ( 'name ' ) ;
--EXPLAIN分析sql执行的状况
EXPLAIN SELECT *FROM student;
索引原则
索引不是越多越好
不要对经常变动数据加索引
小数据量的表不需要加索引(数据量小于300)
索引一般加在常用来查询的字段上!
CodingLabs - MySQL索引背后的数据结构及算法原理
11、MySQL备份
备份方式:
- 直接拷贝物理文件(data目录下的文件)
- 在可视化工具中手动导出
- 使用命令行 mysqldump 命令
#导出
mysqldump -h 主机 -u用户名 -p密码 数括库 表名>物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D :/a.sql
#导入
#登录的情况下,切换到指定的数据库#source备份文件
source d : /a.sql
12、数据库设计三大范式
参考链接:三大范式的通俗解释_隔壁皮皮王的博客-CSDN博客
13、JDBC
SUN公司为了简化对数据库的统一操作,提供了一个(java操作数据库)规范,即JDBC。
DBUtil连接工具类
public class DBUtil {
//连接数据库的四大参数
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/50sql?
useUnicode=true&characterEncoding=utf8&useSSL=true";
private static String user = "qzh";
private static String password = "qzh";static {
try{
Class.forName(driver);//加载驱动
}catch (ClassNotFoundException e){
e.printStackTrace();
}
}/**
* @Description 获得数据库连接
* @return 数据库连接
*/
public static Connection getConnection(){
Connection connection = null;
try{
connection = DriverManager.getConnection(url,user,password);
}catch (ClassNotFoundException e){
e.printStackTrace();
}catch (SQLException e){
e.printStackTrace();
}
return connection;
}/**
* @Description 关闭资源
* @param resultSet 结果集
* @param statement 执行SQL的对象
* @param connection 数据库连接对象
* @return void
*/
public static void closeAll(ResultSet resultSet, Statement statement, Connection connection){
try{
if(resultSet != null){
resultSet.close();
}
if(statement != null){
statement.close();
}
if(connection != null){
connection.close();
}
} catch (SQLException e){
e.printStackTrace();
}
}
}
执行SQL方法:
- boolean execute():可以执行所有的SQL,效率低,所以很少用到
- int executeUpdate():该方法是用来更新数据的,我们用它来执行insert into、update、delete操作,它的返回值代表我们的SQL作用了几条数据
- ResultSet executeQuery():该方法一般用来执行查询操作,返回值是一个结果集
1、修改
Statement statement = connection.createStatement();
String sql = "insert into dog(id,name,health,love,strain) values(1,'豆包',90,90,'柴犬')";
int num1 = statement.executeUpdate(sql);
if(num1 == 1){
System.out.println("插入成功");
}
String sql = "update dog set health=100 where id=1";
int num = statement.executeUpdate(sql);
if(num == 1){
System.out.println("修改成功");
}
2、查询
ResultSet resultSet = statement.executeQuery(sql);
》遍历结果集
while (resultSet.next()){
System.out.print(resultSet.getInt("id") + "\t");
System.out.print(resultSet.getString("name") + "\t");
//3代表查询SQL中的第3个字段
System.out.print(resultSet.getInt(3) + "\t");
System.out.print(resultSet.getInt("love") + "\t");
System.out.println(resultSet.getString("strain"));
}
PreparedStatement防止Sql注入
String sql = "select id from users where name=? and password=?";
//在生成preparedStatement对象时就把对象交给它
PreparedStatemen preparedStatement = connection.prepareStatement(sql);
//把SQL补充完整
preparedStatement.setString(1,name);
preparedStatement.setString(2,pwd);
resultSet = preparedStatement.executeQuery();
JDBC的事务支持
try{
//关闭自动提交,开启事务
connection.setAutoCommit(false);
StringBuffer sql = new StringBuffer("update account set balance=? where id=?");
preparedStatement = connection.prepareStatement(sql.toString());
//把sql补充完整
preparedStatement.setInt(1,500);
preparedStatement.setInt(2,1);
preparedStatement.executeUpdate();preparedStatement.setInt(1,1500);
preparedStatement.setInt(2,2);
preparedStatement.executeUpdate();
//我们需要在所有sql执行之后
//调用Connection的commit()方法来提交事务
connection.commit();
}catch (SQLException e){
try{
//如果任意一条sql执行失败,则需要调用
//Connection的rollback()方法来回滚
connection.rollback();
}catch (Exception el){
el.printStackTrace();
}
e.printStackTrace();
}
14、数据库连接池
java中编写连接池,实现接口DataSource
常用开源数据源:
- DBCP
- C3P0
- Druid:阿里巴巴
DBCP
配置文件(参考):
#连接设置这里面的名字,是DBCP数据源中定义好的driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/50sql?useUnicode=true&characterEncoding=utf8&useSSL=trueusername=root
password=123456
#<!--初始化连接-->initialsize=10
#最大连接数量maxActive=50
#<!--最大空闲连接-->maxIdle=20
#<!--最小空闲连接-->minIdle=5
#<!--超时等待时间以毫秒为单位6000毫秒/1000等于60秒-->maxwait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:[属性名=property; ]
#注意: "user”与“password”两个属性会被明确地传递,因此这里不需要包含他们。connectionProperties=useUnicode=true;characterEncoding=UTF8
#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true
#driver default指定由连接池所创建的连接的只读(read-only)状态。
#如果没有设置该值,则"setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix)defaultReadOnly=
#driver default指定由连接池所创建的连接的事务级别(TransactionIsoLation)。
#可用值为下列之一:NONE,READ_UNCONITTED,READ_COMMITTED,REPEATABLE_READ等defaultTransactionIsolation=READ_UNCOMMITTED
C3P0
配置文件(参考):
<?xml version=-1.e- encoding="UTF-8"2>
<c3po-config>
<!--C3PO的命名配置,如果在代码中“ComboPooledDataSource ds = new ComboPooledDataSource()”这样写就表示使用的是default的配置信息来创建数据源
--><default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl" >jdbc:mysql://localhost:3306/50sql?
useUnicode=true&characterEncoding=utf8&useSSL=true</property>
<property name="user" >root</property>
<property name="password" >123456</property>
<property name="acquirelncrement">5</property><property name= "initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<!--C3PO的命名配置,如果在代码中“ComboPooledDataSource ds = new ComboPooledDataSource(MySQL)”这样写就表示使用的是name是MySQL的配置信息来创建数据源
-->
<named-config name="MySQL">
<property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl" >jdbc:mysql://localhost:3306/50sql?
useUnicode=true&characterEncoding=utf8&useSSL=true</property>
<property name="user" >root</property>
<property name="password" >123456</property>
<property name="acquirelncrement">5</property><property name= "initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</named-config>
</c3po-config>