MySQL简介与安装
MySQL安装
SQLyog使用流程
基本命令行操作
两种数据库类型对比
设置数据库表字符集编码格式
修改和删除表(数据库操作)
外键(了解)
DML语言(重要)
DML语言之添加(增)
DML语言之修改(改)
DML语言之删除(删)
DQL语言(查询)(最重要的数据库语言)
查询select完整语法(暂时了解)
where条件子句
模糊查询
联表查询
自连接(了解)
分页和排序
子查询
MySQL函数
数据库MD5加密
Select查询小结
事务
ACID原则
索引
MySQL用户管理
MySQL备份
项目数据库设计步骤
数据库三大范式
JDBC(重点)
JDBC程序
第一个JDBC程序
public class demo1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动(固定写法)
Class.forName("com.mysql.jdbc.Driver");
//2.用户信息和url useUnicode=true&characterEncoding=utf8&useSSL=false:固定写法
//useSSL=false:当MySQL版本高于驱动版本使用false
String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
String name="root";
String password="123456";
//3.连接成功返回数据库对象:connection
Connection connection = DriverManager.getConnection(url, name, password);
//4.执行SQL对象:statement代表执行SQL对象
Statement statement = connection.createStatement();
//5.执行SQL对象去执行SQL,可能存在结果查看返回结果 resultSet代表返回的结果集,有全部的查询结果
String sql="SELECT * FROM users";
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("password="+resultSet.getObject("PASSWORD"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birthday="+resultSet.getObject("birthday"));
System.out.println("=========================================");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
JDBC对象解释
Statement对象解释
JDBC代码实现(优化)
1. 提取工具类
把连接信息放到配置文件db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456
工具类代码
public class jdbcUtils {
private static String driver=null;
private static String url=null;
private static String username=null;
private static String password=null;
static {
try {
InputStream in = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
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 getConnection () throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//释放连接资源
public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
if(resultSet!=null){
resultSet.close();
}
if(statement!=null){
statement.close();
}
if(connection!=null){
connection.close();
}
}
}
2. 增删改的方法
- 增的方法
public class TestInsert {
public static void main(String[] args) throws SQLException {
Connection con=null;
Statement sta=null;
ResultSet res=null;
//获取数据库连接
try {
con= jdbcUtils.getConnection();
//获得SQL执行对象
sta = con.createStatement();
//SQL执行对象去执行SQL
String sql="INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)" +
"VALUES(4,'zhangwu','123456','324334@qq.com','2020-12-03')";
int i = sta.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
jdbcUtils.release(con, sta, res);
}
}
}
- 删的方法
public class TestDelete {
public static void main(String[] args) throws SQLException {
Connection con=null;
Statement sta=null;
ResultSet res=null;
//获取数据库连接
try {
con= jdbcUtils.getConnection();
//获得SQL执行对象
sta = con.createStatement();
//SQL执行对象去执行SQL
String sql="DELETE FROM users WHERE id=4";
int i = sta.executeUpdate(sql);
if(i>0){
System.out.println("删除成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
jdbcUtils.release(con, sta, res);
}
}
}
- 改的方法
public class TestUpdate {
public static void main(String[] args) throws SQLException {
Connection con=null;
Statement sta=null;
ResultSet res=null;
//获取数据库连接
try {
con= jdbcUtils.getConnection();
//获得SQL执行对象
sta = con.createStatement();
//SQL执行对象去执行SQL
String sql="UPDATE users SET `NAME`='WANG',`PASSWORD`='123',`email`='r43554@qq.com',`birthday`='2020-12-01' WHERE id=1";
int i = sta.executeUpdate(sql);
if(i>0){
System.out.println("更改成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
jdbcUtils.release(con, sta, res);
}
}
}
3. 查询的方法
public class TestSelect {
public static void main(String[] args) throws SQLException {
Connection con=null;
Statement sta=null;
ResultSet res=null;
try {
con = jdbcUtils.getConnection();
sta = con.createStatement();
String sql="select * from users where id=1";
res = sta.executeQuery(sql);
while (res.next()){
System.out.println(res.getString("NAME"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
jdbcUtils.release(con,sta,res);
}
}
}
SQL注入
SQL存在漏洞,会被攻击导致数据泄露——SQL会被拼接 or
public class SqlInput {
public static void main(String[] args) throws SQLException {
//login("WANG","123");正常登录
//SQL注入登录
login(" 'or'1=1"," 'or'1=1");
}
//登录业务
public static void login(String username,String password) throws SQLException {
Connection con=null;
Statement sta=null;
ResultSet res=null;
try {
con = jdbcUtils.getConnection();
sta = con.createStatement();
//SELECT * FROM `users` WHERE `NAME`='WANG' AND `PASSWORD`='123'
//SELECT * FROM `users` WHERE `NAME`=' 'or'1=1' AND `PASSWORD`=' 'or'1=1'
String sql="select * from users where `NAME`='"+username+"' and `PASSWORD`='"+password+"'";
res = sta.executeQuery(sql);
while (res.next()){
System.out.println(res.getString("NAME"));
System.out.println(res.getString("PASSWORD"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
jdbcUtils.release(con,sta,res);
}
}
}
PreparedStatement对象
PreparedStatement可以防止SQL注入而且效率更高
插入
public class TestInsert {
public static void main(String[] args) {
Connection con=null;
PreparedStatement psta=null;
try {
con = jdbcUtils.getConnection();
//注意下面PreparedStatement与Statement区别
//使用?占位符 代替参数
String sql="insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`)values(?,?,?,?,?)";
//预编译SQL,先写sql,然后不执行
psta = con.prepareStatement(sql);
//手动给参数赋值
psta.setInt(1,4);
psta.setString(2, "zz");
psta.setString(3,"123456");
psta.setString(4,"23424@qq.com");
//注意点:sql.Date 数据库 java.sql.Date
// util.Date java new Date().getTime()获得时间戳
psta.setDate(5,new java.sql.Date(new Date().getTime()));
//执行sql
int i = psta.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
jdbcUtils.release(con,psta,null);
}
}
}
删除
public class TestDelete {
public static void main(String[] args) throws SQLException {
Connection con=null;
PreparedStatement psta=null;
try {
con = jdbcUtils.getConnection();
//注意下面PreparedStatement与Statement区别
//使用?占位符 代替参数
String sql="delete from users where id=?";
//预编译SQL,先写sql,然后不执行
psta = con.prepareStatement(sql);
//手动给参数赋值
psta.setInt(1, 4);
//执行sql
int i = psta.executeUpdate();
if(i>0){
System.out.println("删除成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
jdbcUtils.release(con,psta,null);
}
}
}
更新
public class TestUpdate {
public static void main(String[] args) throws SQLException {
Connection con=null;
PreparedStatement psta=null;
try {
con = jdbcUtils.getConnection();
//注意下面PreparedStatement与Statement区别
//使用?占位符 代替参数
String sql="update users set `name`=? where id=? ";
//预编译SQL,先写sql,然后不执行
psta = con.prepareStatement(sql);
//手动给参数赋值
psta.setString(1,"zhemo");
psta.setInt(2,1);
//执行sql
int i = psta.executeUpdate();
if(i>0){
System.out.println("更新成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
jdbcUtils.release(con,psta,null);
}
}
}
查询
public class TestSelect {
public static void main(String[] args) throws SQLException {
Connection con=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
con = jdbcUtils.getConnection();
String sql="select * from users where id=?";
st = con.prepareStatement(sql);
st.setInt(1, 2);
rs = st.executeQuery();
if(rs.next()){
System.out.println(rs.getString("name"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
jdbcUtils.release(con,st,rs);
}
}
}
防止SQL注入
public class SqlInput {
public static void main(String[] args) throws SQLException {
//login("lisi","123456");正常登录
login("' ' or 1=1","123456");
}
//登录业务
public static void login(String username,String password) throws SQLException {
Connection con=null;
PreparedStatement sta=null;
ResultSet res=null;
try {
con = jdbcUtils.getConnection();
//PreparedStatement防止SQL注入的本质:把传递进来的参数当做字符
//假设其中存在转义字符,比如'会被直接转义
String sql="select * from users where `NAME`=? and `PASSWORD`=?";
sta = con.prepareStatement(sql);
sta.setString(1,username);
sta.setString(2,password);
res = sta.executeQuery();
while (res.next()){
System.out.println(res.getString("NAME"));
System.out.println(res.getString("PASSWORD"));
System.out.println("=====================");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
jdbcUtils.release(con,sta,res);
}
}
}
使用IDEA连接数据库
-
连接数据库
输入用户名和密码,点击TestConnection,若连接失败,可以更改数据库驱动配置和时区 -
连接成功后可以更换数据库
-
双击数据库
-
更改后更新数据
-
编写SQL代码的地方
-
连接失败查看原因
JDBC操作事务
- 开启事务
- 一组业务执行完毕,提交业务
- 可以在catch语句中显式定义回滚,但默认失败即回滚
public class TestTransactions1 {
public static void main(String[] args) {
Connection con=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
con = jdbcUtils.getConnection();
//关闭自动提交,开启事务
con.setAutoCommit(false);
String sql1="update account set money =money-100 where name='A'";
st = con.prepareStatement(sql1);
st.executeUpdate();
String sql2="update account set money =money+100 where name='B'";
st = con.prepareStatement(sql2);
st.executeUpdate();
//业务完毕,提交事务
con.commit();
System.out.println("提交成功");
} catch (SQLException throwables) {
try {
//如果失败则回滚
con.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
}finally {
try {
jdbcUtils.release(con,st,rs);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
DBCP与C3P0连接池
数据库连接池
注意:导入jar包时复制粘贴到工程的lib目录下,右键Add as library解压
DBCP连接池测试代码
1. 配置文件:dbcpconfig.properties
注意:mysql5.x版本中driverClassName=com.mysql.jdbc.Driver;8.0版本driverClassName=com.mysql.cj.jdbc.Driver,
同时要注意DataBase中数据库的驱动配置情况
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=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)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
2. 工具类
public class jdbcUtils_dbcp {
private static DataSource dataSource=null;
static {
try {
InputStream in = jdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties properties = new Properties();
properties.load(in);
//创建数据源:工厂模式创建
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection () throws SQLException {
//从数据源中获取连接
return dataSource.getConnection();
}
//释放连接资源
public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
if(resultSet!=null){
resultSet.close();
}
if(statement!=null){
statement.close();
}
if(connection!=null){
connection.close();
}
}
}
3. DBCP测试
public class TestDBCP {
public static void main(String[] args) throws SQLException {
Connection con=null;
PreparedStatement psta=null;
try {
con = jdbcUtils_dbcp.getConnection();
//注意下面PreparedStatement与Statement区别
//使用?占位符 代替参数
String sql="insert into jdbcstudy.users(id,`NAME`,`PASSWORD`,`email`,`birthday`)values(?,?,?,?,?)";
//预编译SQL,先写sql,然后不执行
psta = con.prepareStatement(sql);
//手动给参数赋值
psta.setInt(1,5);
psta.setString(2, "zz");
psta.setString(3,"123456");
psta.setString(4,"23424@qq.com");
//注意点:sql.Date 数据库 java.sql.Date
// util.Date java new Date().getTime()获得时间戳
psta.setDate(5,new java.sql.Date(new Date().getTime()));
//执行sql
int i = psta.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
jdbcUtils_dbcp.release(con,psta,null);
}
}
}
C3P0连接池测试代码
1. 配置文件:c3p0-config.xml
注意:配置文件中&连接符用&;代替。
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!--
c3p0的缺省(默认)配置
如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource();"这样写就表示使用的是c3p0的缺省(默认)-->
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquiredIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<!--
c3p0的命名配置
如果在代码中"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/jdbcstudy?userUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquiredIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</named-config>
</c3p0-config>
2. 工具类
public class jdbcUtils_c3p0 {
private static ComboPooledDataSource dataSource=null;
static {
try {
//.xml文件不需要像.properties配置文件一样读取,会自动加载
//代码方式实现配置(不建议使用)
/*dataSource = new ComboPooledDataSource();
dataSource.setDriverClass();
dataSource.setUser();
dataSource.setPassword();
dataSource.setJdbcUrl();
dataSource.setMaxPoolSize();
dataSource.setMinPoolSize();
*/
//创建数据源:工厂模式创建
//配置文件写法
dataSource = new ComboPooledDataSource("MySQL");
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection () throws SQLException {
//从数据源中获取连接
return dataSource.getConnection();
}
//释放连接资源
public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
if(resultSet!=null){
resultSet.close();
}
if(statement!=null){
statement.close();
}
if(connection!=null){
connection.close();
}
}
}
3.C3P0测试
测试运行不成功的原因有以下几点:
1.配置文件中url,usename,password是否正确,url注意mysql5.x版本和8.0版本区别;
2.配置文件.xml是否在src根目录下
3.数据库配置的驱动是否与数据库版本匹配,导入的有关C3P0的jar包是否匹配mysql版本
4.配置文件中&连接符是否用&;代替
5.配置文件中useSSL=false还是true,5.x版本为true可能导致运行失败
6.配置文件中serverTimezone=UTC设置时区,时区错误也会导致运行失败
7.配置文件名要和配置内容的开始闭合标签名字一致,否则可能会出错
public class Testc3p0 {
public static void main(String[] args) throws SQLException {
Connection con=null;
PreparedStatement psta=null;
try {
con = jdbcUtils_c3p0.getConnection();
//注意下面PreparedStatement与Statement区别
//使用?占位符 代替参数
String sql="insert into jdbcstudy.users(id,`NAME`,`PASSWORD`,`email`,`birthday`)values(?,?,?,?,?)";
//预编译SQL,先写sql,然后不执行
psta = con.prepareStatement(sql);
//手动给参数赋值
psta.setInt(1,11);
psta.setString(2, "z8");
psta.setString(3,"123456");
psta.setString(4,"23424@qq.com");
//注意点:sql.Date 数据库 java.sql.Date
// util.Date java new Date().getTime()获得时间戳
psta.setDate(5,new java.sql.Date(new Date().getTime()));
//执行sql
int i = psta.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
jdbcUtils_c3p0.release(con,psta,null);
}
}
}