MySQL的学习及JDBC的使用

MySQL

学习视频: bilibili

C:\Users\Admin>mysql -h xxx.xxx.xxx.xxx -u test -p密码
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38404
Server version: 5.6.50-log Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;		--显示所有数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.03 sec)

mysql> use test;			--打开test数据库
Database changed
mysql> show tables;			--显示数据库test中所有的表
+----------------+
| Tables_in_test |
+----------------+
| Collection     |
| Comment        |
| Letter         |
| LikeComment    |
| Note           |
| Stories        |
| Talk           |
| TalkDetails    |
| Test           |
| User           |
+----------------+
10 rows in set (0.02 sec)

mysql> describe Test		--显示表test数据库中Test表的列信息
    -> ;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| id       | int(11)  | NO   | PRI | NULL    | auto_increment |
| logBgPos | int(11)  | NO   |     | NULL    |                |
| title    | text     | NO   |     | NULL    |                |
| time     | text     | NO   |     | NULL    |                |
| text     | longtext | NO   |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
5 rows in set (0.03 sec)

mysql> exit
Bye

笔记: csdn链接1

笔记: csdn链接2

-- 目标 : 创建一个school数据库
-- 创建student表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 创建表之前 , 一定要先选择数据库
 
CREATE TABLE IF NOT EXISTS `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  `address` varchar(100) DEFAULT NULL COMMENT '地址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


-- 查看数据库的定义
SHOW CREATE DATABASE school;
-- 查看数据表的定义
SHOW CREATE TABLE student;
-- 显示表结构
DESC student; 

格式:

CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    ......
    `字段名` 列类型 [属性] [索引] [注释],
    PRIMARY KEY (`字段名`)
)[表类型(引擎)][字符集设置][注释]

JDBC使用

new Thread(){
                @Override
                public void run() {
                    super.run();
                    try {
                        //1.加载驱动
                        Class.forName("com.mysql.jdbc.Driver");
                        //2.用户信息和url
                        String url = "jdbc:mysql://xxx.xxx.xxx.xxx:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=true";
                        String name = "test";
                        String passWd = "123456";
                        //3.连接成功 获得数据库对象 ==> Connection
                        Connection connection = DriverManager.getConnection(url,name,passWd);
                        //4.执行SQL的对象
                        String sql = "SELECT * FROM `test`.`MyTest`";
                        PreparedStatement preparedStatement = connection.prepareStatement(sql);
                        //5.执行SQL语句
                        ResultSet resultSet = preparedStatement.executeQuery();
                        while (resultSet.next()){
                            Log.e("TAG", "id: " + resultSet.getObject("id"));
                            Log.e("TAG", "name: " + resultSet.getObject("name"));
                            Log.e("TAG", "title: " + resultSet.getObject("title"));
                            Log.e("TAG", "time: " + resultSet.getObject("time"));
                            Log.e("TAG", "text: " + resultSet.getObject("text"));
                            Log.e("TAG", "============================================");
                        }
                        //6.释放连接
                        preparedStatement.close();
                        connection.close();
                    } catch (ClassNotFoundException | SQLException e) {
                        e.printStackTrace();
                        Log.e("TAG", "error: " + e);
                    }
                }
            }.start();

preparedStatement.executeQuery(); //查询操作返回 ResultSet

preparedStatement.execute(); //执行任何SQL

preparedStatement.executeUpdate(); //更新 插入 删除, 都是用这个, 返回一个受影响的行数

resultSet.beforeFirst(); //移动到最前面

resultSet.afterLast(); //移动到最后面

resultSet.next(); //移动下一个

resultSet.previous(); //移动到前一行

resultSet.absolute(row); //移动到指定行

JDBCUtils

public class JDBCUtils {

    private static String TAG = "TAG ==> JDBCUtils: ";

    private JDBCUtils() {
    }
    
    public static void loadDriver() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        try {
            return DriverManager.getConnection("jdbc:mysql://xxx.xxx.xxx.xxx:3306/数据库名?useUnicode=true&characterEncoding=utf-8&useSSL=true", "用户名", "密码");		//如果连接不上尝试useSSL=true改为false
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void release(Connection conn, PreparedStatement stat, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stat != null) {
                    stat.close();
                    stat = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (conn != null) {
                        conn.close();
                        conn = null;
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void release(Connection conn, PreparedStatement stat) {
        try {
            if (stat != null) {
                stat.close();
                stat = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

JDBC使用模板

//模板
private void mTemplate() {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet rs = null;
    //1.加载驱动(只用加载一次)
    JDBCUtils.loadDriver();
    try {
        //2. 获取数据库链接对象
        connection = JDBCUtils.getConnection();
        //3. 得到执行sql语句的对象
        String sql = "SQL语句 (?,?,?)";
        preparedStatement = connection.prepareStatement(sql);
        //4. 传递参数(SQL语句中有多少问号就要传递多少参数)
        preparedStatement.setInt(1, 123);
        preparedStatement.setInt(2, 321);
        preparedStatement.setInt(3, 213);
        //5. 执行sql语句 (增删改)
        int t = preparedStatement.executeUpdate();
        if (t > 0) {
            Log.e(TAG, "执行成功");
        }
        //5. 执行sql语句 (查)
        rs = preparedStatement.executeQuery();
        if (rs.next()) {
            //做点什么
        }
    } catch (Exception e) {
        e.printStackTrace();
        Log.e(TAG, "执行失败: " + e);
    } finally {
        //最后释放连接
        JDBCUtils.release(connection, preparedStatement, rs);
    }
}

1.增(插入)

//把 [A] = ?, [B] = ?, [C] = ? 插入 [user] 表中
String sql = "INSERT INTO `数据库名`.`user` (`A`, `B`, `C`) VALUES (?,?,?)";

2.删

//删除 [user] 表中 [id] 为?的值 
String sql = "DELETE FROM `数据库名`.`user` WHERE `id` = ?";

3.改(更新)

//更新 [user] 表中 [id] 为?的 [name] = ?的值
String sql = "UPDATE `数据库名`.`user` SET `name` = ? WHERE `id` = ?";

4.查

//查询 [user] 表中 [id] 为?的值
String sql = "SELECT * FROM `数据库名`.`user` WHERE `id` = ?";

错误:
java.sql.SQLException: No suitable driver found for…
解决方法: 使用前需要先导入jar包