数据库学习笔记
maiaimei 2022/11/20 PostgreSQLOracleMySQLH2
# Oracle
<dependency>
<groupId>com.oracle.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>12.2.0.1</version>
</dependency>
1
2
3
4
5
2
3
4
5
jdbc:oracle:thin:@//server:port/serverName
1
# MySQL
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
1
2
3
4
2
3
4
spring:
datasource:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.1.12/testdb?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8
username: root
password: 123456
1
2
3
4
5
6
7
2
3
4
5
6
7
# PostgreSQL
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
</dependency>
1
2
3
4
2
3
4
spring:
datasource:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://192.168.1.25/testdb
username: postgres
password:
1
2
3
4
5
6
7
2
3
4
5
6
7
# H2
http://www.h2database.com/html/main.html (opens new window)
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
1
2
3
4
2
3
4
spring:
datasource:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: org.h2.Driver
# 服务器模式
url: jdbc:h2:tcp://localhost/E:/app/data/testdb
username: root
password: 123456
jpa:
properties:
hibernate:
dialect: org.hibernate.dialect.H2Dialect
# 内存模式
jdbc:h2:mem:<databaseName>
jdbc:h2:mem:test
jdbc:h2:mem:test;database_to_upper=false # 设置表名和字段名是否区分大小写
# 以嵌入式(本地)连接方式连接H2数据库
jdbc:h2:[file:][<path>]<databaseName>
jdbc:h2:~/test # 连接位于用户目录下的test数据库
jdbc:h2:file:/data/testdb
jdbc:h2:file:E:/app/data/testdb # Windows only
# 使用TCP/IP的服务器模式(远程连接)方式连接H2数据库(推荐)
jdbc:h2:tcp://<server>[:<port>]/[<path>]<databaseName>
jdbc:h2:tcp://localhost/~/testdb
jdbc:h2:tcp://localhost/E:/app/data/testdb
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
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
H2 Console Sorry, remote connections ('webAllowOthers') are disabled on this server.的问题解决
修改src\tools\WEB-INF\web.xml，原来注释掉的部分去掉注释即可。
<init-param>
<param-name>webAllowOthers</param-name>
<param-value></param-value>
</init-param>
<init-param>
<param-name>trace</param-name>
<param-value></param-value>
</init-param>
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 数据类型
| Java | java.sql.Types | Oracle | MySQL | PostgreSQL | H2 |
|---|---|---|---|---|---|
| Boolean | BIT | BIT | boolean | BOOLEAN | |
| Byte | TINYINT | TINYINT | TINYINT | TINYINT | |
| Integer | SMALLINT | SMALLINT | smallint | SMALLINT | |
| Integer | INTEGER | INTEGER | INTEGER | integer | INTEGER |
| Long | BIGINT | NUMBER(19,0) | BIGINT | bigint | BIGINT |
| Double | FLOAT | ||||
| Float | REAL | real | REAL | ||
| Double | DOUBLE | double precision | |||
| BigDecimal | NUMERIC | NUMBER | numeric | NUMERIC | |
| BigDecimal | DECIMAL | decimal | |||
| String | CHAR | CHAR | CHAR | char | CHAR |
| String | VARCHAR | VARCHAR2 | VARCHAR | varchar | VARCHAR |
| Date | DATE | DATE | DATE | date | DATE |
| TIME | DATE | TIME | time | TIME | |
| TIMESTAMP | DATE | TIMESTAMP | timestamp | TIMESTAMP |
MySQL 数据类型_w3cschool (opens new window)
PostgreSQL 数据类型 | 菜鸟教程 (opens new window)
http://www.h2database.com/html/datatypes.html (opens new window)
# 数据库操作
CREATE DATABASE db_name;
DROP DATABASE db_name;
1
2
2
# 数据表操作
CREATE TABLE table_name(
column1 datatype PRIMARY KEY NOT NULL,
column2 datatype NOT NULL,
column3 datatype NOT NULL,
.....
columnN datatype NOT NULL
);
DROP TABLE table_name;
-- SCHEMA逻辑分库
CREATE SCHEMA schema_name.table_name (
...
);
-- 删除一个为空的模式（其中的所有对象已经被删除）
DROP SCHEMA schema_name;
-- 删除一个模式以及其中包含的所有对象
DROP SCHEMA schema_name CASCADE;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
示例：
-- MySQL
CREATE TABLE sys_user(
id BIGINT PRIMARY KEY NOT NULL,
nickname VARCHAR(50) NOT NULL,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
is_enabled TINYINT NOT NULL DEFAULT 1,
is_deleted TINYINT NOT NULL DEFAULT 0,
gmt_create TIMESTAMP NOT NULL,
gmt_modified TIMESTAMP NOT NULL
);
-- PostgreSQL
CREATE TABLE sys_user(
id BIGINT PRIMARY KEY NOT NULL,
nickname VARCHAR(50) NOT NULL,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
is_enabled BOOLEAN NOT NULL DEFAULT true,
is_deleted BOOLEAN NOT NULL DEFAULT false,
gmt_create TIMESTAMP NOT NULL,
gmt_modified TIMESTAMP NOT NULL
)
-- H2
CREATE TABLE sys_user(
id BIGINT PRIMARY KEY NOT NULL,
nickname VARCHAR(50) NOT NULL,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
is_enabled BOOLEAN NOT NULL DEFAULT true,
is_deleted BOOLEAN NOT NULL DEFAULT false,
gmt_create TIMESTAMP NOT NULL,
gmt_modified TIMESTAMP NOT NULL
)
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
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