1. 初始MySQL
Java EE:企业级Java开发 web
前端(页面:展示,数据)
后台:(连接点:连接数据库JDBC,连接前端:控制,控制视图跳转,和给前端传数据)
数据库(DataBase):存数据
1.1 连接数据库
mysql -u root -p --连接数据库
--------------------------------------------
--所有语句使用;结尾
show databases; -----查看所有数据库 use 切换数据库
describe student; ----显示数据库中所有的表的信息
create database db1 -----创建数据库
2. 操作数据库
操作数据库>操作数据库中的表> 操作数据库中表的数据
2.1操作数据库
- 创建数据库
CREATE DATABASE [IF not EXISTS] db1;
- 使用数据库
USE db1;
- 删除数据库
DROP DATABASE [IF EXISTS] db1;
- 查看数据库
SHOW DATABASES;
2.2 数据库的列类型
数组
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小的数据 3个字节
- int 标准的整形 4个字节
- bigint 较大的数据 8个字节
- float[M,D] 浮点数 4个字节 M表示数字总数,D表示小数点后的位数
- double[M,D] 浮点数 8个字符
- decimal 字符串形式的浮点数 金融计算使用
字符串
- char 字符串固定大小 0-255
- varchar 可变字符串
- tinytext 微型文本
- text 文本串
时间日期
- date YYYY-MM-DD 日期
- time HH:MM;SS 时间
- datetime YYYY-MM-DD HH:MM:SS
- timestamp 时间戳
- year 年份表示
null
没有 未知
2.3 数据库的字段属性
UNSIGNED
:
- 无符号的整数
- 不能为负数
ZEROFILL
:
- 0填充
- 不足的位数,使用0填充,int(3) ,6 — 006
自动填充 AUTO_INCREMENT
- 自动在上条记录的基础上 + 1(默认)
- 通常用来设计唯一的主键,必须是整数类型
- 必须和主键一起使用
- 可以自定义设计主键自增的起始值和步长
非空 NILL NOT NULL
- 设置为NOT NULL,不给值报错
- NULL 不填写,默认为NULL
默认 DEFAULT
:
- 设置默认值
- 如果不指定该列的值,则会有默认的值
2.4 创建数据表
CREATE TABLE [IF NOT EXISTS] 表名(
字段名 列类型 [属性] [索引] [注释 comment],
字段名 列类型 [属性] [索引] [注释 comment],
.....
字段名 列类型 [属性] [索引] [注释 comment]
)[表类型][字符集设置][注释]
常用命令:
SHOW CREATE DATABASE db1; --- 查看数据库创建命令
SHOW CREATE TABLE tb1; ---- 查看数据表的创建命令
DESC tb1; ---- 查看数据表的结构信息
2.5 数据库引擎
: MYSIAM 和INNODB的区别
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大(2倍的MYISAM) |
优点 | 节约空间,速度快 | 安全性高,事务处理,多表多用户操作 |
2.6 数据库引擎在文件上的区别
- INNODB 在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
- MYISAM对应的文件
- *.frm 表结构的定义文件
- *.MYD 数据文件
- *.MYI 索引文件
2.7 设置数据库表的字符集编码
DEFAULT CHARSET = utf8
可以在my.ini 中配置默认编码
character-set-server=utf8
2.8 修改表
--修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
--增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
--修改表的字段 ALTER TABLE 表名 MODIFY 字段名 列属性
-- ALTER TABLE 表名 CHANGE 旧表名 新表名 列属性
--删除表的字段 ALTER TABLE 表名 DROP 字段名
2.9 删除表
-- 删除表
DROP TABLE [IF EXISTS] tb1;
3. MySQL数据管理
3.1 外键 (了解即可)
方式一,在创建表的时候,增加约束
KEY `FK_gradeid` (gradeid),
CONSTRAINT `FK_gradeid` FOREIGN (gradeid) REFERENCES grade (gradeid)
删除有外键约束的表时,需要先删除从表 ,在删除主表
方式二,添加外键约束
ALTER TABLE student
ADD CONSTRAINT FK_gradeid FOREIGN KEY(geadeid) REFERENCES grade (gradeid)
-- ALTER TABLE 表名
ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 引用的表(引用的字段)
最佳实践
- 数据库就是单纯的表,只用来存数据,只有行和列
- 我们想使用多种表的数据,使用程序去实现
3.2 DML语言 数据库操作语言
DML语言:数据操作语言
- INSERT
- ALTER
- DROP
3.3 插入
语法:INSERT INTO 表名([字段名1,字段2,字段3....]) VALUES('值1'),('值2');
注意事项:
- 字段之间用逗号隔开
- 不写字段是,但是后面的值必须要一一对应
- 可以同时插入多条数据,VALUES后面可以输入多个值,(‘值1’),(‘值2’)…
3.4 更新
UPDATE
语法:UPDATE 表名 SET 列名 = VALUE[,列名 = VALUE,.....] WHERE 条件
条件:where 子句
操作符有:
- =
- <>或!=
>
- <
- <=
>=
- BETWEEN…AND… 在某个范围内
- AND 相当于&&
- OR 相当于||
3.5 删除
delete命令
语法:delete from 表名 [where 条件]
TRUNCATE 命令
作用:完全清空一个数据库表,表的结构和索引约束不会变
TRUNCATE student;
delete与TRUNCATE区别
- 相同点:都能删除数据,都不会删除表结构
- 不同:
- TRUNCATE 重新设置 自增列 计数器归零
- TRUNCATE 不会影响事务
4. DQL查询数据
4.1 DQL 数据查询语言
(Data Query Language : 数据查询语言)
所有查询都使用 : select
完整语法:
select [ALL | DISTINCT] ---默认为查询全部也可以选择去重
--要查询的字段
from table_name ----查询的表(表和字段都可以取别名)
[left | right | inner JOIN table2_name] ---链表查询
[where ...] ---指定结果满足的条件
[group by ...] ---指定结果按照哪几个字段分组
[having ....] ---过滤分组结果满足的条件
[order by ...] ---指定查询记录按一个或多个条件排序 ASC 升序 DESC 降序
[limit ...] ---指定查询的结果从哪条至哪条显示
4.2 指定查询
语法:SELECT 字段,...FROM 表
去重 distinct
作用:去除SELECT查询结果中重复的数据
select distinct *** from ***;
数据库的列(表达式)
数据库中的表倒数:文本值,列,NULL,函数,计算表达式,系统变量…
select 表达式 from表
4.3 where条件子句
作用:检索数据中符合条件的值
搜索的条件由一个或多个表达式组成,返回的结果为布尔值
逻辑运算符
- and &&
- or ||
- Not !
尽量使用英文字母
比较运算符
- IS NULL
- IS NOT NULL
- BETWEEN…AND… 区间
- LIKE 如果a匹配b,则结果为真
- %代表0到任意一个字符
- _代表一个字符
- IN a in(a1,a2,a3…) a在a1,a2,a3… 中的某一个结果为真
4.4 连接查询
操作 | 描述 |
---|---|
INNER Join | 如果表中至少有一个匹配,就返回 |
LEFT JOIN | 会从左表中返回所有的值,即使右表没有匹配 |
RIGHT JOIN | 会从右表中返回所有的值,即使左表没有匹配 |
自连接
SELECT a.categoryName AS '父结点', b.categoryName AS '子节点'
form category AS a,category AS b
WHERE a.categoryid = b.pid;
4.5 分页和排序
- 分页:limit
- 排序:order by
- 升序:ASC
- 降序:DESC
排序
语法:ORDER BY 字段 排序顺序
分页
语法:LIMIT 起始位置 结束位置 (记录从0开始)
分页原理:
pageSize:页面大小
起始值:(n-1)*pageSize
当前页:n
总页数:数据总数/页面大小
第N页:(n-1)*pageSize,pageSize
4.6 子查询
本质:在where中嵌套一个查询
4.7 分组和分组后的条件过滤
分组:GROUP BY 字段
过滤:HAVING 条件
5.MySQL函数
5.1 常用函数
- 数学运算函数
- ABS() 绝对值
- ceiling() 向上取整
- floor() 向下取整
- rand()随机数0-1之间
- sign()判断数的符号 负数返回 -1 正数返回 1 0返回0
- 字符串函数
- char_length() 字符串长度
- concat() 拼接字符串
- insert()插入
- replace()替换
- lower()转小写
- upper()转大写
- substr()返回指定字符串
- 时间和日期函数
- CURRENT_DATE() 获取当前日期
- now() 获取当前时间
- LOCALTIME() 本地时间
- 系统
- UESR() 当前用户
- VERSION() 版本号
5.2 聚合函数
函数名称 | 描述 |
---|---|
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
6. 事务
6.1 什么是事务
要么都成功,要么都失败
将一组SQL放在一个批次中去执行
6.2 事务原则
ACID原则:
- 原子性
- 要么都成功,要么都失败
- 一致性
- 事务前后的数据完整性要保持一至
- 隔离性
- 事务的隔离性是多个用户并发访问数据库,数据库为每个用户开启的事务,不能被其他事务的财政数据所干扰,所以事务之间要相互隔离
- 持久性
- 事务一旦提交不可逆,被持久化到数据库中
隔离导致的问题
- 脏读
- 指一个事务读取了另一个事务未提交的数据
- 不可重复读
- 在一个事务内读取表中的某一行数据,多次读取结果不同,(这个不一定是错误,只是某些场合不对)
- 虚读(幻读)
- 指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致
6.3 事务创建
---Mysql是默认开启事务自动提交
SET autocommit = 0 --- 关闭
SET autocommit = 1 ---开启
----创建事务流程
--- 1 关闭自动提交
SET autocommit = 0
--- 2 开启事务
START TRANSACTION
--- 3 事务内容
INSERT xxx
--- 4 提交或回滚
COMMIT --- 提交
ROLLBACK --- 回滚
--- 5 事务结束 开启自动提交
SET autocommit = 1
---了解
SAVEPOINT 保存点名 --- 设置一个事务保存点
ROLLBACK TO SAVEPOINT --- 回滚到保存点
RELEASE SAVEPOINT 保存点名 --- 删除保存点
6.4 事务练习
--- 模拟A给B转账
--- 先关闭自动提交
SET autocommit = 0;
--- 开启事务
STRAT TRANSACTION;
--- 开始转账
UPDATE 表 SET money = money-500 where name = 'A' --- A减500
UPDATE 表 SET money = money+500 where name = 'B' --- B加500
--- 提交事务 持久化
COMMIT;
--- 回滚
ROLLBACK;
--- 恢复默认值
SET autocommit = 1;
7. 索引
7.1 索引分类
- 主键索引(PRIMARY KYE)
- 唯一标识,主键不可重复,只有一个列作为主键
- 唯一索引(UNIQUE KEY)
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识唯一索引
- 常规索引(KEY/INDEX)
- 默认的
- 全文索引(FULLTEXT)
- 在特定的数据库引擎下才有,快速定位数据
--- 显示索引信息
show index from table_name
--- 添加一个索引
ALTER TABLE table_name ADD 索引名称 INDEX 索引别名 (字段名)
--- 方法二
CREATE INDEX 索引名 ON 表(字段)
--- EXPLAIN 分析SQL执行情况
EXPLAIN SELECT * FROM student --- 分析SQL语句执行情况
7.2 索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加的常用来查询的字段上
8. 权限管理和备份
8.1 用户管理
--- 创建用户
CREATE USER 用户名 IDENTIFIED BY '123456'
---修改密码
SET PASSWORD = PASSWORD('123456')
--- 修改指定用户密码
SET PASSWORD FOR ROOT = PASSWORD('123456')
--- 重命名
RANAME USER 原名 TO 新名
--- 用户权限授予 ALL PRIVILEGES 全部权限
GRANT ALL PRIVLEGES ON *.* TO uesr_name
--- 查看权限
SHOW GRANTS FOR user_name
--- 撤销权限
REVOKE ALL PRIVLEGES ON *.* FROM user_name
8.2 MySQL数据备份
方式:
-
拷贝物理文件
-
可视化工具中导出
-
使用命令行导出 mysqldump
-
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 > 物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -pxakxak school student > D:/1.sql #导入 #登录的情况下导入 source d:/1.sql
-
9. 规范数据库设计
9.1 数据库设计
良好的数据库设计
- 节省内存空间
- 保证数据库完整性
- 方便开发系统
数据库设计
- 分析需求:分析业务和需要处理的数据库的需求
- 概要设计:设计关系E—R图0.
9.2 三大范式
10.JDBC
10.1 数据库驱动
程序需要导入数据库驱动,但每个数据库都有不同的驱动,需要编写多套程序
SUN公司提供了一个Java操作数据库规范:JDBC
现在开发人员只需要学习JDBC,数据库驱动会
10.2 第一个JDBC程序
package com.xu.Demo1;
import java.sql.*;
/**
* 第一个JDBC程序
*/
public class JDBCFirstDemo {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
// 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 用户信息
// useUnicode=true&characterEncoding=utf8&useSSL=true 参数一 支持中文编码 参数二 设置字符集为utf-8 参数三 使用安全链接
String url = "jdbc:mysql://localhost:3306/t1?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT%2B8";
// 用户名
String user = "root";
String password = "xakxak";
// 连接信息,数据库对象
// DriverManager 驱动程序管理器
// Connection 连接
Connection connection = DriverManager.getConnection(url,user,password);
// 执行SQL的对象 Statement
Statement statement = connection.createStatement();
// 执行SQL
//SQL 语句
String sql = "SELECT * FROM chn_city";
// 得到结果集 resultset 结果集
// executeQuery 执行查询
ResultSet resultSet = statement.executeQuery(sql);
//打印结果集
while(resultSet.next()){
System.out.println("id=" + resultSet.getObject("id"));
System.out.println("name=" + resultSet.getObject("name"));
System.out.println("district_id=" + resultSet.getObject("district_id"));
}
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
- 加载驱动
- 连接数据库 DriverManager 得到一个connection(连接)对象
- 通过connection对象创建一个statement对象 statement可以执行SQL语句
- 获取返回的ResultSet(结果集)
- 关闭连接
10.3 对象分析
DriverManager 驱动管理
// DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver);
Class.forName("com.mysql.cj.jdbc.Driver"); // 加载驱动
URL
String url = "jdbc:mysql://localhost:3306/t1?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT%2B8";
// mysql ---3306
// jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&
connection 连接
// connection 代表数据库
// 就是一个数据库对象
// 可以设置自动提交
connection.setAutocommit();
// 提交
connection.commit();
// 回滚
connection.rollback();
Statement 执行SQL的对象 PrepareStatement 执行SQL的对象
statement.executeQuery(); // 查询操作返回 resultset
statement.execute(); // 执行任何SQL
statement.executeUpdate(); // 更新,插入,删除。都用这个,返回一个受影响的行数
ResultSet 查询结果集:封装了所有的查询结果
遍历:
使用next() 移动到下一个数据
获得指定数据类型
getString();
getInt();
getFloat();
…
10.4 代码练习
提取工具类
- 配置文件
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/t1?useUnicode=ture&characterEncoding=utf8&useSSL=true&serverTimezone=GMT%2B8
username = root
password = xakxak
- 工具类
package com.xu.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* JDBC工具类
*/
public class Jdbcutils {
private static String driver;
private static String username;
private static String url;
private static String password;
static {
try{
InputStream inputStream = Jdbcutils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
// 加载驱动
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接
public static Connection gerconnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
// 释放资源
public static void release(Connection con, Statement st, ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(st != null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(con != null){
try {
con.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
10.6 SQL注入
package com.xu.Demo2;
import com.xu.utils.Jdbcutils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* SQL注入
*/
public class SQLinjection {
public static void main(String[] args) {
//login("徐安康","123456");
//SQL注入问题,下面这个输入可以获取数据库里面所有的记录
login("' or '1=1","' or '1=1");
}
// 登录业务
public static void login(String name,String password){
Connection connection = null;
Statement st = null;
ResultSet rs = null;
try {
// 工具类 获取连接
connection = Jdbcutils.gerconnection();
// 获取Statement对象
st = connection.createStatement();
// 编写SQL
String sql = "select * from users where name = '" + name + "'and password = '" + password + "';";
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
}
} catch (Exception throwables) {
throwables.printStackTrace();
}
}
}
10.7 PreparedStatement对象
因为SQL注入问题,需要采用PreparedStatement来执行SQL
public static void loginPlus(String name,String password){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = Jdbcutils.gerconnection();
String sql = "select * from users where name = ? and password = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,name);
preparedStatement.setString(2,password);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getInt("id"));
System.out.println(resultSet.getString("name"));
System.out.println(resultSet.getString("password"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
Jdbcutils.release(connection,preparedStatement,resultSet);
}
}
10.8 事务
ACID原则:
- 原子性
- 同时成功,同时失败
- 一致性
- 总和不变
- 隔离性
- 多个进程之间互不干扰
- 持久性
- 提交不可逆
隔离性问题:
- 脏读
- 一个事务读取了另一个没有提交的事务
- 不可重复读
- 同一个事务,重复读取了表中的事务,表的数据发生了改变
- 虚读(幻读)
- 在一个事务内,读取到别人插入的数据,导致前后不一致
package com.xu.Demo2;
import com.xu.utils.Jdbcutils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement =null;
ResultSet resultSet = null;
try {
connection = Jdbcutils.gerconnection();
// 关闭自动提交 打开事务
connection.setAutoCommit(false);
String sql1 = "update users set name = '123456' where id = 1";
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
String sql2 = "update users set name = 'xakxak' where id = 2";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
// 提交
connection.commit();
// 失败默认回滚
} catch (Exception throwables) {
throwables.printStackTrace();
}
}
}