如果你不希望你的数据每次重启后都丢失，那么你就需要SQL。
刚接触数据库，可以直接先使用H2数据库，不需要安装，直接使用 IDEA（ultimate版）中自带的 dababase 工具进行连接即可。
一、数据库基本概念
- 数据库提供结构化数据的持久化存储
- 索引保证数据查询的速度
- 事务的原子性保证数据不丢失
database
1. 数据库的类型与SQL语句
- Number 类型，例如 int/bigint/tinyint
- Text 类型，例如 varchar/text
- Date 类型，例如 timestamp()/date()/time()/datetime()
2. 数据的外键
一个表的某个列是其它表的主键
3. 数据库的表设计原则
- 每个实体⼀张表（⽤户/商品）
- 每个实体都有⼀个主键ID
- 按照业务需要建索引
- 每个关系⽤⼀张表联系
- 关系型数据库
二、SQL增删改查
获取连接URL
public static void main(String[] args) {
File projectDir = new File(System.getProperty("basedir", System.getProperty("user.dir")));
String jdbcUrl = "jdbc:h2:file:" + new File(projectDir, "xdml").getAbsolutePath();
System.out.println("jdbcUrl = " + jdbcUrl);
}
1. DDL-SQL
- create table 建表语句
- drop table 删表语句
- alter table 修改表语句
建表：
create table user
(
id bigint primary key auto_increment,
name varchar(100),
password varchar(100),
tel varchar(20),
avatar varchar(100),
created_at timestamp,
updated_at timestamp
)
删表：
drop table user
修改表：
增加 status 列，用于以后做逻辑删除：
alter table user
add status tinyint not null default 1;
2. 基本SQL
insert 增：
insert into user (name, password, tel, avatar, created_at, updated_at)
values ('lisi', '123456', '8888', null, now(), now())
delete 删：
物理删除：数据被从数据库中抹去
delete
from user
where id = 2;
逻辑删除：数据还在数据库中，只是假装看不见而已
update 改：
逻辑删实则只是将 status 字段更新为 0，此后可以假装没有这一行数据：
update user
set status = 0,
UPDATE_AT = now()
where id = 1;
完善脏数据：
update user
set UPDATE_AT = now(),
CREATE_AT = now()
where CREATE_AT is null;
3. SELECT查
- Select *
- Select count(*) count(1)
- Select max/min/avg
- Select limit 分⻚
- select order by 排序
- Select is null/is not null
- Select where id in ()
例子：
查看符合条件的行：
select *
from user
where id > 3
选择符合条件的行的部分列：
select id, name, avatar
from user
where avatar is not null
查看符合条件的行有多少：
select count(*)
from user
where avatar is not null
根据 order 表中的user_id外键，从 user 表中捞出所有下过单的用户行记录，并按照注册时间升序排序：
select *
from user
where id in (
-- 子查询
select user_id from "order"
)
order by created_at asc
4. 重要的知识
- SQL语句不区分大小写
- 命名风格是下划线分割两个单词（snake case）
- 数据库中的字符串是单引号
- 数据库的注释是
-- - 可以用分号分割多个SQL语句
5. 过早优化是万恶之源
数据库操作的典型时间 1ms，基本不会再快了，但是可以没有上限的慢...
但是对于H2、MySQL 这些数据库而言，100w次和 1000w次的查询差别不大，上亿后可能才需要优化。
三、JDBC基本使用、SQL注入与防范
假设登录验证时根据用户名和密码对数据库进行查询（实际生产中密码绝不能明文存储）：
select * from user where name = 'username' and password = 'password'
下面使用JDBC连接到数据库，尝试做登录校验，仔细体会Statement和PreparedStatement，后者会对SQL语句提前预编译，并用?符号对后续参数进行占位，相对要安全一些：
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DatabaseReader {
private static String getJDBCUrl() {
File projectDir = new File(System.getProperty("basedir", System.getProperty("user.dir")));
return "jdbc:h2:file:" + new File(projectDir, "target/tmall").getAbsolutePath();
}
private static boolean isCorrectPasswordUnsafe(String username, String password) throws SQLException {
try (Connection con = DriverManager.getConnection(getJDBCUrl());
Statement stmt = con.createStatement()) {
String sql = "select * from user where name = '" + username + "' and password = '" + password + "'";
System.out.println(sql);
ResultSet resultSet = stmt.executeQuery(sql);
return resultSet.next();
}
}
private static boolean isCorrectPasswordSafe(String username, String password) throws SQLException {
try (Connection con = DriverManager.getConnection(getJDBCUrl());
PreparedStatement pstmt = con.prepareStatement("select * from user where name = ? and password = ?")) {
pstmt.setString(1, username);
pstmt.setString(2, password);
ResultSet resultSet = pstmt.executeQuery();
return resultSet.next();
}
}
public static void main(String[] args) throws SQLException {
System.out.println(isCorrectPasswordUnsafe("wangwu", "wangwu123"));
// SQL 注入（登录）成功
System.out.println(isCorrectPasswordUnsafe("zhaosi", "' or 1=1 --"));
// SQL 注入（登录）失败
System.out.println(isCorrectPasswordSafe("zhaosi", "' or 1=1 --"));
}
}
PrepareStatement是预编译后的参数化SQL语句，语法结构不会再变，传输的参数只作为替换。
四 、SQL的高级SELECT语句和JOIN详解
1. SELECT
查询两列并排序：
select id, name from user where ADDRESS = 'shanghai' order by id desc;
分页 <从第几个元素开始找（从0开始），最多返回几个元素>：
select * from user limit 0, 4;
group by 按某个字段分组：
select ADDRESS, count(*) from user group by ADDRESS;
as 别名：
select GOODS_ID, count(*) as count from "ORDER" group by GOODS_ID;
字段相乘：
select ID, GOODS_ID, GOODS_NUM * GOODS_PRICE from "ORDER";
相乘得到订单金额然后再分组求和，比如求不同商品各自销售总额：
select GOODS_ID, SUM(GOODS_NUM * GOODS_PRICE) as TOTAL
from "ORDER"
group by GOODS_ID
order by TOTAL desc;
2. JOIN
类似于集合，求一些交并补。
JOIN
默认是 inner join，left join 只选择在左表里存在的，哪怕右表中不存在，right join 同理，这三种是最常用的，另外还有 full outer join。它们还有一些细分区别，注意看图中黄色高亮部分。
语法例子：
select "ORDER".id, "ORDER".USER_ID, "ORDER".GOODS_ID, GOODS.NAME, USER.NAME, USER.TEL, USER.ADDRESS
from "ORDER"
join GOODS -- 把商品表加入进来
on "ORDER".GOODS_ID = GOODS.ID
join USER -- 把用户表加入进来
on "ORDER".USER_ID = USER.ID
where USER.ADDRESS = 'shenzhen';
3. 综合练习(H2)
现有用户表、商品表和订单表如下：
用户表：
+----+----------+------+----------+
| ID | NAME | TEL | ADDRESS |
+----+----------+------+----------+
| 1 | zhangsan | tel1 | beijing |
+----+----------+------+----------+
| 2 | lisi | tel2 | shanghai |
+----+----------+------+----------+
| 3 | wangwu | tel3 | shanghai |
+----+----------+------+----------+
| 4 | zhangsan | tel4 | shenzhen |
+----+----------+------+----------+
商品表：
+----+--------+-------+
| ID | NAME | PRICE |
+----+--------+-------+
| 1 | goods1 | 10 |
+----+--------+-------+
| 2 | goods2 | 20 |
+----+--------+-------+
| 3 | goods3 | 30 |
+----+--------+-------+
| 4 | goods4 | 40 |
+----+--------+-------+
| 5 | goods5 | 50 |
+----+--------+-------+
订单表：
+------------+-----------------+------------------+---------------------+-------------------------------+
| ID(订单ID) | USER_ID(用户ID) | GOODS_ID(商品ID) | GOODS_NUM(商品数量) | GOODS_PRICE(下单时的商品单价) |
+------------+-----------------+------------------+---------------------+-------------------------------+
| 1 | 1 | 1 | 5 | 10 |
+------------+-----------------+------------------+---------------------+-------------------------------+
| 2 | 2 | 1 | 1 | 10 |
+------------+-----------------+------------------+---------------------+-------------------------------+
| 3 | 2 | 1 | 2 | 10 |
+------------+-----------------+------------------+---------------------+-------------------------------+
| 4 | 4 | 2 | 4 | 20 |
+------------+-----------------+------------------+---------------------+-------------------------------+
| 5 | 4 | 2 | 100 | 20 |
+------------+-----------------+------------------+---------------------+-------------------------------+
| 6 | 4 | 3 | 1 | 20 |
+------------+-----------------+------------------+---------------------+-------------------------------+
| 7 | 5 | 4 | 1 | 20 |
+------------+-----------------+------------------+---------------------+-------------------------------+
| 8 | 5 | 6 | 1 | 60 |
+------------+-----------------+------------------+---------------------+-------------------------------+
- 查询有多少所有用户曾经买过指定的商品：
-- 方法1
select count(distinct USER_ID)
from "ORDER"
where GOODS_ID = 1;
-- 方法2
select count(*)
from USER
where ID in (
select USER_ID from "ORDER" where GOODS_ID = 1
);
- 分页查询所有用户，按照ID倒序排列：
-- 分页 <从第几个元素开始找（从0开始），最多返回几个元素>
select ID, NAME, TEL, ADDRESS
from USER
order by ID desc
limit pageSize * (pageNum - 1), 3;
- 查询所有的商品及其销售额，按照销售额从大到小排序：
select GOODS.ID, GOODS.NAME, SUM("ORDER".GOODS_NUM * "ORDER".GOODS_PRICE) as GMV
from "ORDER"
join GOODS on "ORDER".GOODS_ID = GOODS.ID
group by GOODS.ID
order by GMV desc;
- 查询订单信息，只查询用户名、商品名齐全的订单，即INNER JOIN方式：
select "ORDER".ID as ORDER_ID,
USER.NAME as USER_NAME,
GOODS.NAME as GOODS_NAME,
"ORDER".GOODS_NUM * "ORDER".GOODS_PRICE as TOTAL_PRICE
from "ORDER"
join USER on "ORDER".USER_ID = USER.id
join GOODS on "ORDER".GOODS_ID = GOODS.ID;
- 查询所有订单信息，哪怕它的用户名、商品名缺失，即LEFT JOIN方式：
select "ORDER".ID as ORDER_ID,
USER.NAME as USER_NAME,
GOODS.NAME as GOODS_NAME,
"ORDER".GOODS_NUM * "ORDER".GOODS_PRICE as TOTAL_PRICE
from "ORDER"
left join USER
on "ORDER".USER_ID = USER.ID
left join GOODS
on "ORDER".GOODS_ID = GOODS.ID;
完整 Java 代码如下：
package com.github.hcsp.sql;
import java.io.File;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class Sql {
// 用户信息
public static class User {
Integer id;
String name;
String tel;
String address;
@Override
public String toString() {
return "User{" + "id=" + id + ", name='" + name + '\'' + ", tel='" + tel + '\'' + ", address='" + address + '\'' + '}';
}
}
/**
* 题目1：
* 查询有多少所有用户曾经买过指定的商品
*
* @param databaseConnection jdbc连接
* @param goodsId 指定的商品ID
* @return 有多少用户买过这个商品
* @throws SQLException if a database access error occurs
* or this method is called on a closed connection
*/
// 例如，输入goodsId = 1，返回2，因为有2个用户曾经买过商品1。
// +-----+
// |count|
// +-----+
// | 2 |
// +-----+
public static int countUsersWhoHaveBoughtGoods(Connection databaseConnection, Integer goodsId) throws SQLException {
try (PreparedStatement pstmt = databaseConnection.prepareStatement(
"select count(distinct USER_ID) from `ORDER` where GOODS_ID = ?")
) {
pstmt.setInt(1, goodsId);
ResultSet resultSet = pstmt.executeQuery();
while (resultSet.next()) {
return resultSet.getInt(1);
}
return -1;
}
}
/**
* 题目2：
* 分页查询所有用户，按照ID倒序排列
*
* @param databaseConnection jdbc连接
* @param pageNum 第几页，从1开始
* @param pageSize 每页有多少个元素
* @return 指定页中的用户
* @throws SQLException if a database access error occurs
* or this method is called on a closed connection
*/
// 例如，pageNum = 2, pageSize = 3（每页3个元素，取第二页），则应该返回：
// +----+----------+------+----------+
// | ID | NAME | TEL | ADDRESS |
// +----+----------+------+----------+
// | 1 | zhangsan | tel1 | beijing |
// +----+----------+------+----------+
public static List<User> getUsersByPageOrderedByIdDesc(Connection databaseConnection, int pageNum, int pageSize) throws SQLException {
try (PreparedStatement pstmt = databaseConnection.prepareStatement("select ID, NAME, TEL, ADDRESS\n" +
"from USER\n" +
"order by ID desc\n" +
"limit ? * (? - 1), 3")) {
pstmt.setInt(1, pageSize);
pstmt.setInt(2, pageNum);
ResultSet resultSet = pstmt.executeQuery();
List<User> users = new ArrayList<>();
while (resultSet.next()) {
User user = new User();
user.id = resultSet.getInt(1);
user.name = resultSet.getString("name");
user.tel = resultSet.getString(3);
user.address = resultSet.getString("ADDRESS");
users.add(user);
}
return users;
}
}
// 商品及其营收
public static class GoodsAndGmv {
Integer goodsId; // 商品ID
String goodsName; // 商品名
BigDecimal gmv; // 商品的所有销售额
@Override
public String toString() {
return "GoodsAndGmv{" + "goodsId=" + goodsId + ", goodsName='" + goodsName + '\'' + ", gmv=" + gmv + '}';
}
}
/***
* 题目3：
* 查询所有的商品及其销售额，按照销售额从大到小排序
*
* @param databaseConnection jdbc连接
* @return 所有商品及其GMV
* @throws SQLException if a database access error occurs
* or this method is called on a closed connection
*/
// 预期的结果应该如图所示
// +----+--------+------+
// | ID | NAME | GMV |
// +----+--------+------+
// | 2 | goods2 | 2080 |
// +----+--------+------+
// | 1 | goods1 | 80 |
// +----+--------+------+
// | 4 | goods4 | 20 |
// +----+--------+------+
// | 3 | goods3 | 20 |
// +----+--------+------+
public static List<GoodsAndGmv> getGoodsAndGmv(Connection databaseConnection) throws SQLException {
try (PreparedStatement pstmt = databaseConnection.prepareStatement("select GOODS.ID, GOODS.NAME, SUM(\"ORDER\".GOODS_NUM * \"ORDER\".GOODS_PRICE)as GMV\n" +
"from \"ORDER\"\n" +
" join GOODS on \"ORDER\".GOODS_ID = GOODS.ID\n" +
"group by GOODS.ID\n" +
"order by GMV desc")) {
ResultSet resultSet = pstmt.executeQuery();
List<GoodsAndGmv> goodsAndGmvs = new ArrayList<>();
while (resultSet.next()) {
GoodsAndGmv goodsAndGmv = new GoodsAndGmv();
goodsAndGmv.goodsId = resultSet.getInt(1);
goodsAndGmv.goodsName = resultSet.getString(2);
goodsAndGmv.gmv = resultSet.getBigDecimal(3);
goodsAndGmvs.add(goodsAndGmv);
}
return goodsAndGmvs;
}
}
// 订单详细信息
public static class Order {
Integer id; // 订单ID
String userName; // 用户名
String goodsName; // 商品名
BigDecimal totalPrice; // 订单总金额
@Override
public String toString() {
return "Order{" + "id=" + id + ", userName='" + userName + '\'' + ", goodsName='" + goodsName + '\'' + ", totalPrice=" + totalPrice + '}';
}
}
/**
* 题目4：
* 查询订单信息，只查询用户名、商品名齐全的订单，即INNER JOIN方式
*/
// 预期的结果为：
// +----------+-----------+------------+-------------+
// | ORDER_ID | USER_NAME | GOODS_NAME | TOTAL_PRICE |
// +----------+-----------+------------+-------------+
// | 1 | zhangsan | goods1 | 50 |
// +----------+-----------+------------+-------------+
// | 2 | lisi | goods1 | 10 |
// +----------+-----------+------------+-------------+
// | 3 | lisi | goods1 | 20 |
// +----------+-----------+------------+-------------+
// | 4 | zhangsan | goods2 | 80 |
// +----------+-----------+------------+-------------+
// | 5 | zhangsan | goods2 | 2000 |
// +----------+-----------+------------+-------------+
// | 6 | zhangsan | goods3 | 20 |
// +----------+-----------+------------+-------------+
/**
* @param pstmt An object that represents a precompiled SQL statement.
* @return 符合条件的订单列表
* @throws SQLException if a database access error occurs
* or this method is called on a closed connection
*/
private static List<Order> getOrders(PreparedStatement pstmt) throws SQLException {
ResultSet resultSet = pstmt.executeQuery();
List<Order> orders = new ArrayList<>();
while (resultSet.next()) {
Order order = new Order();
order.id = resultSet.getInt(1);
order.userName = resultSet.getString(2);
order.goodsName = resultSet.getString(3);
order.totalPrice = resultSet.getBigDecimal(4);
orders.add(order);
}
return orders;
}
/**
* @param databaseConnection jdbc连接
* @return 符合条件的订单列表
* @throws SQLException if a database access error occurs
* or this method is called on a closed connection
*/
public static List<Order> getInnerJoinOrders(Connection databaseConnection) throws SQLException {
try (PreparedStatement pstmt = databaseConnection.prepareStatement("select \"ORDER\".ID as ORDER_ID,\n" +
" USER.NAME as USER_NAME,\n" +
" GOODS.NAME as GOODS_NAME,\n" +
" \"ORDER\".GOODS_NUM * \"ORDER\".GOODS_PRICE as TOTAL_PRICE\n" +
"from \"ORDER\"\n" +
" join USER on \"ORDER\".USER_ID = USER.id\n" +
" join GOODS on \"ORDER\".GOODS_ID = GOODS.ID")) {
return getOrders(pstmt);
}
}
/**
* 题目5：
* 查询所有订单信息，哪怕它的用户名、商品名缺失，即LEFT JOIN方式
*
* @param databaseConnection jdbc连接
* @return 符合条件的订单列表
* @throws SQLException if a database access error occurs
* or this method is called on a closed connection
*/
// 预期的结果为：
// +----------+-----------+------------+-------------+
// | ORDER_ID | USER_NAME | GOODS_NAME | TOTAL_PRICE |
// +----------+-----------+------------+-------------+
// | 1 | zhangsan | goods1 | 50 |
// +----------+-----------+------------+-------------+
// | 2 | lisi | goods1 | 10 |
// +----------+-----------+------------+-------------+
// | 3 | lisi | goods1 | 20 |
// +----------+-----------+------------+-------------+
// | 4 | zhangsan | goods2 | 80 |
// +----------+-----------+------------+-------------+
// | 5 | zhangsan | goods2 | 2000 |
// +----------+-----------+------------+-------------+
// | 6 | zhangsan | goods3 | 20 |
// +----------+-----------+------------+-------------+
// | 7 | NULL | goods4 | 20 |
// +----------+-----------+------------+-------------+
// | 8 | NULL | NULL | 60 |
// +----------+-----------+------------+-------------+
public static List<Order> getLeftJoinOrders(Connection databaseConnection) throws SQLException {
try (PreparedStatement pstmt = databaseConnection.prepareStatement("select \"ORDER\".ID,\n" +
" USER.NAME as USER_NAME,\n" +
" GOODS.NAME as GOODS_NAME,\n" +
" \"ORDER\".GOODS_NUM * \"ORDER\".GOODS_PRICE as TOTAL_PRICE\n" +
"from \"ORDER\"\n" +
" left join USER\n" +
" on \"ORDER\".USER_ID = USER.ID\n" +
" left join GOODS\n" +
" on \"ORDER\".GOODS_ID = GOODS.ID")) {
return getOrders(pstmt);
}
}
// 注意，运行这个方法之前，请先运行mvn initialize把测试数据灌入数据库
public static void main(String[] args) throws SQLException {
File projectDir = new File(System.getProperty("basedir", System.getProperty("user.dir")));
String jdbcUrl = "jdbc:h2:file:" + new File(projectDir, "target/test").getAbsolutePath();
try (Connection connection = DriverManager.getConnection(jdbcUrl, "root", "Jxi1Oxc92qSj")) {
System.out.println(countUsersWhoHaveBoughtGoods(connection, 1));
System.out.println(getUsersByPageOrderedByIdDesc(connection, 2, 3));
System.out.println(getGoodsAndGmv(connection));
System.out.println(getInnerJoinOrders(connection));
System.out.println(getLeftJoinOrders(connection));
}
}
}
更多SQL练习可以到 leetcode上面练习。
五、使用Docker方式安装一切数据库
使用 Docker 来练习 SQL，百分百兼容、无残留、统一又方便，当然在 Windows 下需要先跟着 Docker 官方文档安装 Docker 本地服务，但折腾一番安装完毕后，就可以畅快体验 Docker 了。
下面是使用 Docker分别安装MySQL和Postgres。
使用-p映射端口（冒号左边是宿主端口）用于在宿主机上进行数据库连接，使用-v映射数据存储目录，用于持久化数据，不指定镜像的tag标签时默认拉取 latest 最新版本。
docker run --name some-mysql -v /my/own/datadir:/var/lib/mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag
创建数据库：
show databases
create database mydb
use mydb
接下来就可以继续建表等。
Postgres：
与安装 mysql 相似，不同点在于 Postgres 数据库被创建时默认用户名是 postgres：
docker run --name peng-postgres -v /my/own/datadir:/var/lib/postgresql/data -p 5432:5432 -e POSTGRES_PASSWORD=123 -d postgres:tag
