SQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

# 1. 初始化
PS C:\Windows\system32> mysqld --initialize --console
2022-01-17T12:32:47.721393Z 0 [System] [MY-013169] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.26) initializing of server in progress as process 16088
2022-01-17T12:32:47.739476Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-01-17T12:32:48.147715Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-01-17T12:32:49.184664Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-01-17T12:32:49.187008Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-01-17T12:32:49.384847Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: gMlai+AXi2rn

# 2. 安装服务
PS C:\Windows\system32> mysqld --install
Service successfully installed.

# 3. 启动服务
PS C:\Windows\system32> net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

# 4. 使用初始化密码登录
PS C:\Windows\system32> mysql -u root -p
Enter password: ************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26

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;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

# 5. 为 root 设置自己的密码，方便下次登录
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
Query OK, 0 rows affected (0.00 sec)

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

# 使用 exit 退出
mysql> exit
Bye

# 下次使用新设置的密码 'your_password' 登录即可
PS C:\Windows\system32> mysql -u root -p
Enter password: *************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26 MySQL Community Server - GPL

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>

1
2
3
4
5
6

sudo apt update && sudo apt install mysql-server # 安装
sudo service mysql status                        # 查看服务状态
# OR systemctl status mysql

sudo mysql                      # Ubuntu 默认 root@localhost 是无密码的
mysql> exit;

1
2

mysql> alert user 'root'@'localhost' identified with mysql_native_password by '你自己的mysql密码';
mysql> flush privileges;

1
2
3

mysql> create user 'root'@'%' identified by '你自己的mysql密码';
mysql> grant all privileges on *.* to 'root'@'%';
mysql> flush privileges;

1
2
3
4
5
6
7
8
9

-- 定义一个外键约束
ALTER TABLE students
ADD CONSTRAINT fk_class_id      -- 外键约束名称 fk_class_id，任意
FOREIGN KEY (class_id)          -- 指定了 class_id 作为外键
REFERENCES classes (id);        -- 指定了这个关键将关联到 classes 表的 id 列

-- 删除一个外键约束
ALERT TABLE students
DROP FOREIGN KEY fk_class_id;

1
2
3
4

-- 语法：
-- SELECT * FROM <表名>
-- 如
SELECT * FROM students; -- 所有的学生记录

1
2
3
4

-- 语法：
-- SELECT * FROM <表名> WHERE <条件表达式>
-- 如
SELECT * FROM students WHERE score > :: 80; -- 分数大于 80 的学生记录

1
2
3
4
5
6

-- 语法：
-- SELECT 列1 [别名1], 列2, 列3 FROM <表名> [...]
-- 如
SELECT id, score, name FROM students;        -- 没设置别名
SELECT id, score points, name FROM students; -- 设置别名
SELECT id, score points, name FROM students WHERE gender = 'M'; -- 可接 WHERE条件

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT id, name, gender, score FROM students ORDER BY score; -- 默认 ASC 升序，可以省略
SELECT id, name, gender, score FROM students ORDER BY score DESC; -- 降序
-- 多列，先按 score ，有相同分数的再按 gender
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

-- 如果有 WHERE 子句，那么 ORDER BY 子句要放到 WHERE 子句后面（因为是对结果集排序嘛）
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

1
2
3
4
5
6
7

-- 语法：
-- LIMIT <N-M> OFFSET <M>
-- 如
SELECT * FROM students LIMIT 3 OFFSET 0; -- 查询第 1 页
SELECT * FROM students LIMIT 3 OFFSET 3; -- 查询第 2 页
SELECT * FROM students LIMIT 3 OFFSET 6; -- 查询第 3 页
-- ...

1
2

SELECT COUNT(*) FROM students;
SELECT AVG(score) average FROM students WHERE gender = 'M';

1
2

SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
-- COUNT() 的结果不再是一个，因为 GROUP BY 子句指定了按 class_id 分组

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- 语法：
-- SELECT * FROM <表1> [表1别名], <表2> [表2别名];
-- 如
SELECT * FROM students, classes;
-- 可以利用投影查询设置别名
SELECT
	students.id sid,
	students.name,
	students.gender,
	students.score,
	classes.id cid,
	classes.name cname
FROM students, classes;
-- SQL还允许给表设置一个别名，以在投影查询中引用起来稍微简洁一点
SELECT
	s.id sid,
	s.name,
	s.gender,
	s.score,
	c.id cid,
	c.name cname
FROM students s, classes c;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 语法：
-- SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...>;
-- 如
SELECT
	s.id, s.name, s.class_id,
	c.name class_name,          --
	s.gender, s.score
FROM students s                 -- 先确定主表，仍然使用 FROM <表1>
INNER JOIN classes c            -- 再确定需要连接的表，使用 INNER JOIN <表2>
ON s.class_id = c.id;           -- 然后确定连接条件，使用 ON <条件……>
-- 可选，加上 WHERE 子句、 ORDER BY 等子句

1
2
3
4
5
6

-- 语法：
-- INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...)
-- 如
INSERT INTO students (class_id, name, gender, score) VALUES
	(1, '大宝', 'M', 87),
	(2, '二宝', 'M', 81);       -- 可以一次性添加多条记录

1
2
3
4
5
6

-- 语法：
-- UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
-- 如
UPDATE students
SET name='大牛', score=score+10 -- 可以一次性更新多条记录，且可以使用表达式
WHERE id=1;

1
2
3
4

-- 语法：
-- DELETE FROM <表名> WHERE ...;
-- 如
DELETE FROM students where id=1;

1
2

# mysql -h <mysql-server-ip> -u root -p
mysql -h 10.0.1.99 -u root -p

1
2
3
4
5
6

-- 给 students 表新增一列 birth
ALERT TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
-- 修改 birth 列名为 birthday，类型改为 VARCHAR(20)
ALERT TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT　NULL;
-- 删除列
ALERT TABLE students DROP COLUMN birthday;

1
2
3
4
5

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                         -- 试图把事务内的所有SQL所做的修改永久保存
-- ROLLBACK;                    -- 可以主动用 ROLLBACK 回滚事务

1
2
3
4
5
6

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.47</version>   <!-- 与安装的 MySQL 版本对应 -->
	<scope>runtime</scope>      <!-- 注意是 runtime -->
</dependency>

1
2
3
4
5
6
7
8
9

// JDBC 连接的 URL, 不同数据库有不同的格式:
String JDBC_URL = "jdbc:mysql://localhost:3306/test";
String JDBC_USER = "root";
String JDBC_PASSWORD = "password";
// 获取连接:
Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
// TODO: 访问数据库...
// 关闭连接:
conn.close();

1
2
3

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
	// ...
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
	try (Statement stmt = conn.createStatement()) {
		try (ResultSet rs = stmt.executeQuery("SELECT id, grade, name, gender FROM students WHERE gender=1")) {
			while (rs.next()) {
				long id = rs.getLong(1); // 注意：索引从1开始
				long grade = rs.getLong(2);
				String name = rs.getString(3);
				int gender = rs.getInt(4);
			}
		}
	}
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
	try (PreparedStatement ps = conn.prepareStatement("SELECT id, grade, name, gender FROM students WHERE gender=? AND grade=?")) {
		// 必须首先调用setObject() 设置每个占位符 ? 的值
		ps.setObject(1, "M"); // 注意：索引从1开始
		ps.setObject(2, 3);
		try (ResultSet rs = ps.executeQuery()) {
			while (rs.next()) {
				long id = rs.getLong("id");
				long grade = rs.getLong("grade");
				String name = rs.getString("name");
				String gender = rs.getString("gender");
			}
		}
	}
}

1
2
3
4

ResultSet rs = ...
if (rs.next()) {
	double sum = rs.getDouble(1);
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
	try (PreparedStatement ps = conn.prepareStatement("INSERT INTO students (id, grade, name, gendar) VALUES (?,?,?,?)")) {
		ps.setObject(1, 999);   // 注意：索引从1开始
		ps.setObject(2, 1);     // grade
		ps.setObject(3, "Bob"); // name
		ps.setObject(4, "M");   // gender

		int n = ps.executeUpdate(); // 1 返回 int，表示插入的记录数量
	}
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
	try (Preparestatement ps = conn.prepareStatement(
													"INSERT INTO students (grade, name, gender) VALUES (?,?,?)",
													Statement.RETURN_GENERATED_KEYS)) {
		ps.setObject(1, 1);     // grade
		ps.setObject(2, "Bob"); // name
		ps.setObject(3, "M");   // gender

		int n = ps.executeUpdate(); // 1

		try (ResultSet rs = ps.getGeneratedKeys()) {
			if (rs.next()) {
				long id = rs.getLong(1); // 注意：索引从1开始
			}
		}
	}
}

1
2
3
4
5
6
7

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
	try (PreparedStatement ps = conn.prepareStatement("UPDATE students SET name=? WHERE id=?")) {
		ps.setObject(1, "Bob"); // 注意：索引从1开始
		ps.setObject(2, 999);
		int n = ps.executeUpdate(); // 返回更新的行数
	}
}

1
2
3
4
5
6

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
	try (PreparedStatement ps = conn.prepareStatement("DELETE FROM students WHERE id=?")) {
		ps.setObject(1, 999); // 注意：索引从1开始
		int n = ps.executeUpdate(); // 删除的行数
	}
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

Connection conn = openConnection();
try {
	// 关闭自动提交
	conn.setAutoCommit(false);
	// 执行多条 SQL 语句
	insert(); update(); delete();
	// 提交事务
	conn.commit();
} catch (SQLException e) {
	// 回滚事务
	conn.rollback();
} finally {
	conn.setAutoCommit(true);
	conn.close();
}

1
2

// 设定隔离级别为READ COMMITTED:
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

try (PreparedStatement ps = conn.PreparedStatement("INSERT INTO students (name, gender, grade, score) VALUES (?,?,?,?)")) {
	// 对同一个 PreparedStatement 反复设置参数并调用 addBatch():
	for (Student s : students) {
		ps.setString(1, s.name);
		ps.setBoolean(2, s.gender);
		ps.setInt(3, s.grade);
		ps.setInt(4.s.score);

		ps.addBatch();          // 添加到 batch
	}
	// 执行 batch
	int[] ns = ps.executeBatch();
	for(int n : ns) {
		System.out.println(n + " insert."); // batch 中每个 SQL 执行的结果数量
	}
}

1
2
3
4
5

<dependency>
	<groupId>com.zaxxer</groupId>
	<artifactId>HikariCP</artifactId>
	<version>2.7.1</version>
</dependency>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

HikariConfig config = new HikariConfig();

config.setJdbcUrl("jdbc:mysql://localhost:3306/test");
config.setUsername("root");
config.setPassword("password");
config.addDataSourceProperty("connectionTimeout", "1000"); // 连接超时：1秒
config.addDataSourceProperty("idleTimeout", "60000");      // 空闲超时：60秒
config.addDataSourceProperty("maximumPoolSize", "10");     // 最大连接数：10

DataSource ds = new HikariDataSource(config);

1
2
3

try (Connection conn = ds.getConnection()) { // 在此处获取连接
	// ...
}   // 在此处“关闭”连接（不是真正的关闭，只是释放到连接池中，以便下次获取连接时直接返回）