第五知识单元

# sudo apt update
sudo apt install mysql-server
systemctl status mysql.service
# sudo systemctl start mysql.service

sudo mysql
# Welcome to the MySQL monitor.  Commands end with ; or \g.
# Your MySQL connection id is 10
# Server version: 8.0.31-0ubuntu0.22.04.1 (Ubuntu)
# 
# Copyright (c) 2000, 2022, 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> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
# Query OK, 0 rows affected (0.01 sec)
# 
mysql> exit
# Bye

mysql -u root -p
# Enter password:
# Welcome to the MySQL monitor.  Commands end with ; or \g.
# Your MySQL connection id is 18
# Server version: 8.0.31-0ubuntu0.22.04.1 (Ubuntu)
# 
# Copyright (c) 2000, 2022, 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>

mysql> show databases;
# +--------------------+
# | Database           |
# +--------------------+
# | information_schema |
# | mysql              |
# | performance_schema |
# | sys                |
# +--------------------+
# 4 rows in set (0.00 sec)

mysql> CREATE DATABASE test DEFAULT CHARSET UTF8;
# Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show databases;
# +--------------------+
# | Database           |
# +--------------------+
# | information_schema |
# | mysql              |
# | performance_schema |
# | sys                |
# | test               |
# +--------------------+
# 5 rows in set (0.00 sec)

use test;
-- Database changed

CREATE TABLE `websites` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
`url` varchar(255) NOT NULL DEFAULT '',
`alexa` int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa 排名',
`country` char(10) NOT NULL DEFAULT '' COMMENT '国家',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
-- Query OK, 0 rows affected, 3 warnings (0.02 sec)

select * from websites;
-- Empty set (0.00 sec)

INSERT INTO `websites` VALUES
('1', 'Google', 'https://www.google.com/', '1', 'USA'),
('2', '淘宝', 'https://www.taobao.com/', '13', 'CN'),
('3', '微博', 'http://weibo.com/', '20', 'CN'),
('4', 'Facebook', 'https://www.facebook.com/', '3', 'USA');
-- Query OK, 4 rows affected (0.01 sec)
-- Records: 4  Duplicates: 0  Warnings: 0

select * from websites;
-- +----+----------+---------------------------+-------+---------+
-- | id | name     | url                       | alexa | country |
-- +----+----------+---------------------------+-------+---------+
-- |  1 | Google   | https://www.google.com/   |     1 | USA     |
-- |  2 | 淘宝     | https://www.taobao.com/   |    13 | CN      |
-- |  3 | 微博     | http://weibo.com/         |    20 | CN      |
-- |  4 | Facebook | https://www.facebook.com/ |     3 | USA     |
-- +----+----------+---------------------------+-------+---------+
-- 4 rows in set (0.00 sec)

import java.sql.*;
public class MySQLDemo {
    // MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
    // static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    // static final String DB_URL = "jdbc:mysql://localhost:3306/test";
    // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL =
        "jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
    static final String USER = "root"; // MySQL用户名
    static final String PASS = "password"; // MySQL密码
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName(JDBC_DRIVER); // 注册JDBC驱动
            conn = DriverManager.getConnection(DB_URL, USER, PASS); // 连接数据库
            stmt = conn.createStatement(); // 实例化Statement对象
            String sql = "SELECT id, name, url FROM websites"; // 定义将要执行的SQL语句
            ResultSet rs = stmt.executeQuery(sql); // 执行SQL语句
            while(rs.next()){ // 展开结果集
                System.out.print("ID: " + rs.getInt("id"));
                System.out.print(", Website Name: " + rs.getString("name"));
                System.out.print(", Website URL: " + rs.getString("url"));
                System.out.print("\n");
            }
            rs.close(); // 关闭结果集
        } catch(SQLException e) { // 处理 JDBC 错误
            e.printStackTrace();
        } catch(Exception e) { // 处理 Class.forName 错误
            e.printStackTrace();
        } finally { // 关闭资源
            try {
                if(stmt != null) stmt.close(); // 关闭Statement对象
            } catch(SQLException e) {
            }
            try {
                if(conn != null) conn.close(); // 关闭连接
            } catch(SQLException e) {
                e.printStackTrace();
            }
        }
        System.out.println("Bye!");
    }
}

java -cp .:mysql-connector-j-8.0.31.jar MySQLDemo
# ID: 1, Website Name: Google, Website URL: https://www.google.com/
# ID: 2, Website Name: 淘宝, Website URL: https://www.taobao.com/
# ID: 3, Website Name: 微博, Website URL: http://weibo.com/
# ID: 4, Website Name: Facebook, Website URL: https://www.facebook.com/
# Bye!