MySQL学习（基础知识）

忆故人的梦

已于 2022-06-15 15:00:25 修改

阅读量277

点赞数

文章标签： mysql 学习

于 2022-06-11 17:05:21 首次发布

本文链接：https://blog.csdn.net/qq_43880100/article/details/125235765

版权

-- 插入语句（添加）要保证数据和字段一一对应

JDBC实现增删改查（preparedStatement）preparedStatement可以防止注入问题

1、针对表的一些操作

show create database school -- 查看创建数据库的语句

show create table student -- 查看创建数据表的定语语句

desc student -- 显示表的结构

-- 增加表的字段

alter table teacher1 add age int(11)

-- 删除表字段

alter table teacher1 drop age1

-- 删除表

drop table if exists teacher1

-- 修改表名

alter table teacher rename as teacher1

-- 修改表的字段

alter table teacher1 modify age varchar(10) -- 修改约束

alter table teacher1 change age age1 int(11) -- 字段重命名

/*注意点：字段名用``包裹，sql建议用小写，所有符号全部英文 */

创建一张表例子：

-- 添加外键

alter table `student` add constraint `FK_gradeid` foreign key (`gradeid`) references `grade` (`gradeid`);

2、表中数据的增删改查

-- 插入语句（添加）要保证数据和字段一一对应

INSERT INTO `grade` (`gradeid`, `gradename`) VALUES ('1', '大四')

insert into `student` (`name`, `pwd`, `gradeid`) values ('李四', 'aaaa', '1'), ('王五', 'aaaa', '1')

-- 删除数据

DELETE FROM `student` WHERE id = 5

--清空表

TRUNCATE `student`

delete和truncate的区别

DELETE FROM `test` -- 不会影响自增

TRUNCATE `test` -- 自增会归零

--修改

update `student` set `name` = '张三2' where `name` = '张三1' and `sex` = '男'

--查询

SELECT `id` FROM `test`

-- 别名 as 可以给字段也可以给表，起别名

SELECT `id` AS 学号 FROM `test`

--查询完整语法

--模糊查询

-- 查询张姓同学 %代表任意个字符 _代表一个字符

select `id`, `name` from `student`

where `name` like '张%' -- 张姓为张的人

-- 姓为张，但是是名字是两个字的人

SELECT `id`, `name` FROM `student`

WHERE `name` LIKE '张_'

-- 姓为张，但是是名字是三个字的人

SELECT `id`, `name` FROM `student`

WHERE `name` LIKE '张__'

-- 查询名字中间有小的人

SELECT `id`, `name` FROM `student`

WHERE `name` LIKE '%小%'

-- 查询id为1， 2， 3的学生 in()里面是具体的一个或多个值

SELECT `id`, `name` FROM `student`

WHERE `id` in (1, 2, 3)

联表查询

SELECT `id`,`name`,s.gradeid

FROM `student` AS s

INNER JOIN `grade` AS g

ON s.gradeid = g.gradeid (ON 判断的条件)

WHERE (WHERE 等值查询)

例子：

自查询例子：

查询的各种案例：

3、一些可能常用函数：

函数应用例子：

4、加密的一些操作

MD5加密

CREATE TABLE IF NOT EXISTS `testmd5`(

`id` INT(4) NOT NULL,

`name` VARCHAR(20) NOT NULL,

`pwd` VARCHAR(50) NOT NULL,

PRIMARY KEY (`id`)

)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 明文密码

INSERT INTO `testmd5` VALUES (1, 'zhangsan', '123456'), (2, 'lisi', '123456'), (3, 'wangwu', '123456')

-- 加密

UPDATE `testmd5` SET pwd=MD5(pwd) WHERE id=1

UPDATE `testmd5` SET pwd=MD5(pwd) -- 加密全部密码

-- 插入的时候加密

INSERT INTO `testmd5` VALUES (4, 'xiaoming', MD5('123456'))

5、事务

流程

事务案例：

6、索引

-- 增加一个全文索引

alter table account add fulltext index `name` (`name`);

-- 显示所有索引信息

SHOW INDEX FROM account

索引基础语法

使用索引查询速度快。例子：

创建需要的表：

插入数据：

没有索引的查询结果：

SELECT * FROM app_user WHERE `name` = '用户9999'; -- 总耗时 : 1.096 sec

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

添加索引：

有索引的查询结果：

SELECT * FROM app_user WHERE `name` = '用户9999'; -- 总耗时 : 0 sec

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

7、对用户的操作

8、MySQL备份

可视化工具导出：

命令行导出：

导出

导入

9、数据库设计和三大范式

关系型数据库设计：三大范式的通俗理解 - 景寓6号 - 博客园 (cnblogs.com)https://www.cnblogs.com/wsg25/p/9615100.html

10、JDBC

添加jar包的注意点：

JDBC代码实现：第一个最简单的版本

package com.zhou.lesson01;
import com.mysql.jdbc.Driver;
import java.sql.*;
// 我的第一个JDBC程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1. 加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2. 用户信息和url  useUnicode=true&characterEncoding=utf8&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?   useUnicode=true&characterEncoding=utf8&useSSL=true";
        String usename = "root";
        String password = "123456";
        // 3. 连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url, usename, password);
        // 4. 执行SQL的对象
        Statement statement = connection.createStatement();
//        statement.executeQuery(); 查询操作，返回resuletSet
//        statement.execute(); 执行任何sql
//        statement.executeUpdate(); 更新、插入删除，都是这个，返回一个受影响的行数

        // 5. 执行SQL的对象 去  执行SQL,可能存在结果，查看返回结果
        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("pwd=" + 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实现增删改查（preparedStatement）preparedStatement可以防止注入问题

有封装的JDBC写法

首先创建db.properties文件放在src目录下，里面包含一些登录信息

其次创建util包里面创建JdbcUtils工具类

代码：

package com.zhou.lesson02.utils;

import java.io.IOException;

import java.io.InputStream;

import java.sql.*;

import java.util.Properties;

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");

// 1、驱动只要加载一次

Class.forName(driver);

} catch (Exception e) {

throw new RuntimeException(e);

}

// 获取连接

public static Connection getConnection() throws SQLException {

return DriverManager.getConnection(url, username, password);

}

// 释放连接资源

public static void release(Connection conn, Statement st, ResultSet rs){

if (rs != null){

try {

rs.close();

} catch (SQLException e) {

throw new RuntimeException(e);

}

if (st != null){

try {

st.close();

} catch (SQLException e) {

throw new RuntimeException(e);

}

if (conn != null){

try {

conn.close();

} catch (SQLException e) {

throw new RuntimeException(e);

}

之后实现增删改查操作

添加：

package com.zhou.lesson03;

import com.zhou.lesson02.utils.JdbcUtils;

import java.sql.Connection;

import java.util.Date;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public class TestInsert {

public static void main(String[] args) {

Connection conn = null;

PreparedStatement st = null;

try {

conn = JdbcUtils.getConnection();

// 区别使用？占位符代替参数

String sql = "insert into users(id, `NAME`, `PASSWORD`, `email`, `birthday`) values (?, ?, ?, ?, ?)";

st = conn.prepareStatement(sql); // 预编译sql，先写sql，然后不执行

// 手动给参数赋值

st.setInt(1, 4);

st.setString(2, "zhoujie");

st.setString(3, "123456");

st.setString(4, "1034369165@qq.com");

// 注意点： sql.Date 数据库 java.sql.Date()

// util.Date java new Date().getTime() 获得时间戳

st.setDate(5, new java.sql.Date(new Date().getTime()));

// 执行

int i = st.executeUpdate();

if (i > 0){

System.out.println("插入成功");

}

} catch (SQLException e) {

throw new RuntimeException(e);

}finally {

JdbcUtils.release(conn, st, null);

}

删除：

package com.zhou.lesson03;

import com.zhou.lesson02.utils.JdbcUtils;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public class TestDelete {

public static void main(String[] args) {

Connection conn = null;

PreparedStatement st = null;

try {

conn = JdbcUtils.getConnection();

String sql = "delete from users where id=?";

st = conn.prepareStatement(sql);

st.setInt(1, 4);

int i = st.executeUpdate();

if (i > 0){

System.out.println("删除成功");

}

} catch (SQLException e) {

throw new RuntimeException(e);

}finally {

JdbcUtils.release(conn, st, null);

}

修改：

package com.zhou.lesson03;

import com.zhou.lesson02.utils.JdbcUtils;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public class TestUpdate {

public static void main(String[] args) {

Connection conn = null;

PreparedStatement st = null;

try {

conn = JdbcUtils.getConnection();

String sql = "update users set `NAME` = ? where id = ?";

st = conn.prepareStatement(sql);

st.setString(1, "zhangsan");

st.setInt(2, 2);

int i = st.executeUpdate();

if (i > 0){

System.out.println("更新成功");

}

} catch (SQLException e) {

throw new RuntimeException(e);

}finally {

JdbcUtils.release(conn, st, null);

}

查询：

package com.zhou.lesson03;

import com.zhou.lesson02.utils.JdbcUtils;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class TestQuery {

public static void main(String[] args) {

Connection conn = null;

PreparedStatement st = null;

ResultSet rs = null;

try {

conn = JdbcUtils.getConnection();

String sql = "select * from users where id=?";

st = conn.prepareStatement(sql);

st.setInt(1, 1);

rs = st.executeQuery();

if (rs.next()){

System.out.println(rs.getString("NAME"));

}

} catch (SQLException e) {

throw new RuntimeException(e);

}finally {

JdbcUtils.release(conn, st, rs);

}

JDBC实现事务操作：

package com.zhou.lesson04;

import com.zhou.lesson02.utils.JdbcUtils;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class TestTransaction1 {

public static void main(String[] args) {

Connection conn = null;

PreparedStatement st = null;

ResultSet rs = null;

try {

conn = JdbcUtils.getConnection();

// 关闭数据库的自动提交，自动会开启事务

conn.setAutoCommit(false); // 开启事务

String sql1 = "update account set money = money - 100 where name = 'A'";

st = conn.prepareStatement(sql1);

st.executeUpdate();

String sql2 = "update account set money = money + 100 where name = 'B'";

st = conn.prepareStatement(sql2);

st.executeUpdate();

// 业务完毕，提交事务

conn.commit();

System.out.println("成功");

} catch (SQLException e) {

try {

conn.rollback(); // 如果失败则回滚事务

} catch (SQLException ex) {

throw new RuntimeException(ex);

}

throw new RuntimeException(e);

}finally {

JdbcUtils.release(conn, st, rs);

}

数据库连接池：

DBCP实现操作：

（1）首先在src目录下创建文件dbcpconfig.properties:

#连接设置

driverClassName=com.mysql.jdbc.Driver

url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true

username=root

password=123456

#

initialSize=10

#最大连接数量

maxActive=50

#

maxIdle=20

#

minIdle=5

#

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）之后编写JdbcUtils_DBCP类，跟前面我们自己写的JdbcUtils，差不多，就是更简化

package com.zhou.lesson05.utils;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;

import java.io.InputStream;

import java.sql.*;

import java.util.Properties;

public class JdbcUtils_DBCP {

private static DataSource dataSource = null;

static{

try {

InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");

Properties properties = new Properties();

properties.load(in);

// 创建数据源工厂模式--->创建

dataSource = BasicDataSourceFactory.createDataSource(properties);

} catch (Exception e) {

throw new RuntimeException(e);

}

// 获取连接

public static Connection getConnection() throws SQLException {

return dataSource.getConnection(); // 从数据源中获取连接

}

// 释放连接资源

public static void release(Connection conn, Statement st, ResultSet rs){

if (rs != null){

try {

rs.close();

} catch (SQLException e) {

throw new RuntimeException(e);

}

if (st != null){

try {

st.close();

} catch (SQLException e) {

throw new RuntimeException(e);

}

if (conn != null){

try {

conn.close();

} catch (SQLException e) {

throw new RuntimeException(e);

}

（3）测试DBCP,TestDBCP

package com.zhou.lesson05;

import com.zhou.lesson05.utils.JdbcUtils_DBCP;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public class TestDBCP {

public static void main(String[] args) {

Connection conn = null;

PreparedStatement st = null;

try {

conn = JdbcUtils_DBCP.getConnection();

String sql = "insert into users (id, `NAME`, `PASSWORD`, `email`, `birthday`) values (?,?,?,?,?)";

st = conn.prepareStatement(sql);

st.setInt(1, 4);

st.setString(2, "zj");

st.setString(3,"123456");

st.setString(4, "1034369165@qq.com");

st.setString(5, "2020-01-01");

int i = st.executeUpdate();

if (i > 0){

System.out.println("插入成功");

}

} catch (SQLException e) {

throw new RuntimeException(e);

}finally {

JdbcUtils_DBCP.release(conn, st, null);

}

C3P0实现操作：

（1）首先创建cp30-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?useUnicode=true&characterEncoding=utf8&useSSL=true</property>

</default-config>

<!--

c3p0的命名配置

如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");"这样写就表示使用的是mysql的缺省（默认）

-->

<named-config name="MySQL">

<property name="driverClass">com.mysql.jdbc.Driver</property>

<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true</property>

</named-config>

</c3p0-config>

（2）之后编写JdbcUtils_C3P类，跟前面我们自己写的JdbcUtils，差不多，就是更简化

package com.zhou.lesson05.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Properties;

public class JdbcUtils_C3P0 {

private static ComboPooledDataSource dataSource = null;

static{

try {

// 代码版配置

// dataSource = new ComboPooledDataSource();

// dataSource.setDriverClass();

// 创建数据源工厂模式--->创建

dataSource = new ComboPooledDataSource("MySQL"); // 配置文件写法

} catch (Exception e) {

throw new RuntimeException(e);

}

// 获取连接

public static Connection getConnection() throws SQLException {

return dataSource.getConnection(); // 从数据源中获取连接

}

// 释放连接资源

public static void release(Connection conn, Statement st, ResultSet rs){

if (rs != null){

try {

rs.close();

} catch (SQLException e) {

throw new RuntimeException(e);

}

if (st != null){

try {

st.close();

} catch (SQLException e) {

throw new RuntimeException(e);

}

if (conn != null){

try {

conn.close();

} catch (SQLException e) {

throw new RuntimeException(e);

}

（3）测试C3P0,TestC3P0类

package com.zhou.lesson05;

import com.zhou.lesson05.utils.JdbcUtils_C3P0;

import com.zhou.lesson05.utils.JdbcUtils_DBCP;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public class TestC3P0 {

public static void main(String[] args) {

Connection conn = null;

PreparedStatement st = null;

try {

conn = JdbcUtils_C3P0.getConnection(); //原来是自己实现的，现在用自己实现的

String sql = "insert into users (id, `NAME`, `PASSWORD`, `email`, `birthday`) values (?,?,?,?,?)";

st = conn.prepareStatement(sql);

st.setInt(1, 5);

st.setString(2, "zj");

st.setString(3,"123456");

st.setString(4, "1034369165@qq.com");

st.setString(5, "2020-01-01");

int i = st.executeUpdate();

if (i > 0){

System.out.println("插入成功");

}

} catch (SQLException e) {

throw new RuntimeException(e);

}finally {

JdbcUtils_C3P0.release(conn, st, null);

}

忆故人的梦

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL学习（基础知识）

B站狂神学Java的MySQL部分个人学习笔记
复制链接

扫一扫

MySQL学习（基础知识）

1、针对表的一些操作

-- 增加表的字段

-- 删除表字段

-- 删除表

-- 修改表名

-- 修改表的字段

2、表中数据的增删改查

-- 插入语句（添加） 要保证数据和字段一一对应

-- 删除数据

--清空表

--修改

--查询

--查询完整语法

--模糊查询

3、一些可能常用函数：

4、加密的一些操作

5、事务

6、索引

7、对用户的操作

8、MySQL备份

9、数据库设计和三大范式

10、JDBC

添加jar包的注意点：

JDBC代码实现：第一个最简单的版本

JDBC实现增删改查（preparedStatement）preparedStatement可以防止注入问题

添加：

删除：

修改：

查询：

JDBC实现事务操作：

数据库连接池：

DBCP实现操作：

C3P0实现操作：

“相关推荐”对你有帮助么？

-- 插入语句（添加）要保证数据和字段一一对应