27中级 - SQL语言【爬虫项目实战】

DDL(Data Definition Language)

• 一般就是用一次，用完就不需要动了

create table 建表语句
drop table 删表语句
alter table 修改表

基本SQL

• insert into - 增
• delete from - 删
• update - 改
• select - 查

使用JDBC访问

• Java Database Connection
  • 连接串
  • 用户名
  • 密码,一般有这三样东西就可以连接数据库,类似这种jdbc:h2:file:/Users/ories/Downloads/java-zhangbo-project/26/1/database-fix-exception-handling/xdml
• Statement
• PrepareStatement，和Statement的区别，这个可以防sql注入
• ResultSet

用户表是最常见的

• id是唯一的create table user (id bigint primary key auto_increment)
• 创建用户表

create table user (id bigint primary key auto_increment, 
    name varchar(100),
    password varchar(100),
    tel varchar(20),
    avatar varchar(100),
    created_at timestamp,
    update_at timestamp
)

• 重要的需要记住的知识
  • SQL语句的关键词不需要区分大小写，
  • 命名风格下划线，约定用下划线去进行区分大小写,camel case, snake case
  • 数据库的字符串是单引号
  • 数据库的注释是--

插入语句

• 数据库的函数now()

insert into user
    (name, PASSWORD,TEL,AVATAR,CREATED_AT,UPDATE_AT)
    values ( 'zhangsan', 'password123', '1234567890','http://123.com/1.jpg', now(), now() )

删除用户

delete from user where id=1

• 1 row affect表示影响了1行，如果update的时候是0 row affect就表示没有生效，没这条数据
• 很多情况下不用这么极端的删除，这种删除称为物理删除，数据被从数据库中抹去了
• 逻辑删除 数据还在数据库中，知识我们假装看不见而已

修改表

alter table user add status tinyint not null default 1

• 这时候删除就可以用

update user set STATUS = 0 where id = 2;

• delete和update非常危险一定要加where

数据库的查询快慢

• 一次查询差不多1ms
• 和数量有关
• 上亿之后才有必要优化，过早的优化是万恶之源

select 查询

• select * from USER where AVATAR is not null
• select count(*) from USER where AVATAR is not null
• 选出最大的id是哪个

Select *
Select count(*) count(1)
Select max/min/avg
Select limit 分页
select order by 排序
select is null/is not null
select where id in

用户表和订单表关联

• 方案一，缺点1.非常冗余，很多字段存了两遍2.怎么保证数据一致，用户表的手机改了，商品表手机没变

• 方案二,通过外键关联，外键的意思是一个表的某个列是其他表的主键

create table "order" (
    id bigint auto_increment,
    name varchar(100),
    user_id bigint,
    updated_at timestamp not null default now(),
    created_at timestamp not null default now(),
    constraint order_pk primary key (id)
)

• 钱这个东西一定不能用double或者float存，用big decimal，对应数据库decimal存

-- 补上用户表空的所有的时间
-- update user set UPDATE_AT=now(), CREATED_AT = now() where UPDATE_AT is NULL;

-- 把所有下过单的用户捞出来,并且按照注册时间去排序
select * from user where id in (
    select USER_ID from "order"
    )
order by CREATED_AT ASC

• 新需求，订单编号 订单金额 用户名 用户头像，来源于两张表如何去查

如何用java的jdbc去查数据

• 很大安全隐患的写法

package com.github.hcsp.exception;

import java.io.File;
import java.sql.*;

public class DatabaseReader {
    public static void main(String[] args) throws SQLException {
        System.out.println(isCorrectPassword("zhangsan", "password123"));
        System.out.println(isCorrectPassword("zhangsan", "654321"));
    }

    private static boolean isCorrectPassword(String username, String password) throws SQLException {
        File projectDir = new File(System.getProperty("basedir", System.getProperty("user.dir")));
        String jdbcUrl = "jdbc:h2:file:" + new File(projectDir, "xdml").getAbsolutePath();

        Connection connection = DriverManager.getConnection(jdbcUrl);

        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from user where name = '" + username + "'" + " and PASSWORD = '" + password + "'");

        // 结果集有没有值来判断是不是正确
        return resultSet.next();

    }

}

• 永远不要相信用户传入的数据
• sql注入，攻击者通过精心设计的字符串，欺骗服务器，执行一些sql语句

// 1 = 1`表示永远生效，`--`表示注释之后不生效
System.out.println(isCorrectPassword("zhangsan", "' or 1=1 --")); // true
System.out.println(isCorrectPassword("zhangsan", "' or 1=1; delete from user where id=5; --")); // true

用完数据库一定要关系

• 两种东西要关闭，一种是connection,一种是statement

private static boolean isCorrectPassword(String username, String password) throws SQLException {
        File projectDir = new File(System.getProperty("basedir", System.getProperty("user.dir")));
        String jdbcUrl = "jdbc:h2:file:" + new File(projectDir, "xdml").getAbsolutePath();

        try (Connection connection = DriverManager.getConnection(jdbcUrl); Statement statement = connection.createStatement()) {
            String sql = "select * from user where name = '" + username + "'" + " and PASSWORD = '" + password + "'";
            System.out.println(sql);
            ResultSet resultSet = statement.executeQuery(sql);
            // 结果集有没有值来判断是不是正确
            return resultSet.next();
        }
    }

• 用PreparedStatement去防止sql注入,最终会被sql语句解析

package com.github.hcsp.exception;

import java.io.File;
import java.sql.*;

public class DatabaseReader {
    public static void main(String[] args) throws SQLException {
        System.out.println(isCorrectPassword("zhangsan", "' or 1=1; delete from user where id=5; --")); // true
    }

    private static boolean isCorrectPassword(String username, String password) throws SQLException {
        File projectDir = new File(System.getProperty("basedir", System.getProperty("user.dir")));
        String jdbcUrl = "jdbc:h2:file:" + new File(projectDir, "xdml").getAbsolutePath();

        try (Connection connection = DriverManager.getConnection(jdbcUrl);
             PreparedStatement statement = connection.prepareStatement("select * from user where name = ? and PASSWORD = ?")) {
            statement.setString(1, username);
            statement.setString(2, password);
            ResultSet resultSet = statement.executeQuery();
            // 结果集有没有值来判断是不是正确
            return resultSet.next();
        }
    }

}

为什么第二种情况可以防止sql注入

• 第一种情况，写的sql最终会被sql引擎解析成ast

• 第二种情况，使用的时候已经被编译成了ast,参数传递进来的时候直接替换就行

Docker 方式安装书库与第三方客户端

• docker安装的好处百分之百兼容
• 百分百无残留
• 百分百统一，方便
• MySQL/PostgreSQL为例

docker内部就是一个独立的linux系统

• 运行docker,安装mysql5.7.27 docker run --name mysql -e MYSQL_ROOT_PASSWORD=123qweasd -d mysql:5.7.27
• docker ps 查看docker运行的进程，
• docker rm -f mysql 杀掉docker运行的进程
• 加上额外的-p参数，表示port端口，把docker容器的端口运行到机器的端口

• docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123qweasd -d mysql:5.7.27

连接mysql的方法

• 方法一,sequel pro
• 方法二,idea自带的数据库连接

指定数据库，mysql的jdbc的连接

• 碰到问题，通过修改url成jdbc:mysql://localhost:3306?autoReconnect=true&useSSL=false解决
• 创建数据库,create database mydb;,执行一些命令

# show databases;
# create database mydb;
# show databases
# 使用mydb
# use mydb; 

# 创建数据库user
# create table user (id int primary key, name varchar(20));
# 查询user
# select * from `user`;

• idea直接连接刚才创建的mydb jdbc:mysql://localhost:3306/mydb?autoReconnect=true&useSSL=false
• 也可以用sql工具sequel pro去连接

用docker安装postgres

• docker run --name postgres -p 5432:5432 -e POSTGRES_PASSWORD=123qweasd -d postgres:12
• docker rm -f postgres

• 现在启动的数据库的数据是不持久化的，docker重启了数据就没了
  • 除非在启动容器的时候用-v参数

docker rm -f postgres
mkdir pgdata
docker run --name postgres -v `pwd`/pgdata:/var/lib/postgresql/data -p 5432:5432 -e POSTGRES_PASSWORD=123qweasd -d postgres:12

• mysql 持久化与之类似 docker run --name mysql -v pwd/mysql-data:/var/lib/mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123qweasd -d mysql:5.7.27
• H2数据库不需要安装

squirrel sql的使用，支持所有jdbc数据库

• 要改下安装脚本，搜索squirrelsql start script,stackover flow
• 总结先选择一个driver，相关驱动加入里面，创建数据库连接，比较丑，比较麻烦，但是支持所有jdbc，ide自带的数据库连接工具比较方便

SQL的高级SELECT语句与JOIN详解

• select 比较常用且比较容易被老板的需求影响
• docker stop mysqldocker关掉数据库

数据库的表设计原则

• 每个实体一张表，(用户/商品)
  • 每个实体都有一个主键
  • 按照业务需要建索引
• 每个关系用一张表联系，保持数据的一致性是很麻烦的
  • 关系型数据库

-- 查到全部用户
select * from user;
-- 查到有多少用户
select count(*) from user;
-- 查地址在上海的用户的数量
select count(*) from user where ADDRESS='shanghai' 
-- 查找具体的列，并且id为降序
select id,name from user where ADDRESS='shanghai' order by id desc
-- 分页 
select * from user limit <从第几个元素开始查找>,<最多返回几个元素>
select * from user limit 0,2
--  每个用户有多少个用户,group by，按照列去分组
select ADDRESS, count(*) from user group by ADDRESS
select GOODS_ID, count(*) from `order` group by GOODS_ID
-- 别名 
select GOODS_ID, count(*) as count from `order` group by GOODS_ID
-- 乘起来
select id, GOODS_ID, GOODS_NUM*GOODS_PRICE from "ORDER"
-- 按照商品id分组，并且把每一组的成交金额统计出来
select GOODS_ID, SUM(GOODS_NUM*GOODS_PRICE) as TOTAL from "ORDER" group by GOODS_ID order by TOTAL desc;
-- join操作
select "ORDER".id,"ORDER".USER_ID,"ORDER".GOODS_ID, GOODS.NAME from "ORDER"
    join GOODS
        on "ORDER".GOODS_ID = GOODS.id;

• 默认inner join 内连，a和b都有
• left join只选择a链有

select "ORDER".id,"ORDER".USER_ID,"ORDER".GOODS_ID, GOODS.NAME from "ORDER"
    left join GOODS
        on "ORDER".GOODS_ID = GOODS.id;

• 将三张表制作成一张大表

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;

• 查询三张表，用户是深圳

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
     left join USER
          on "ORDER".USER_ID = USER.ID
where user.ADDRESS = 'shenzhen'

• 总结: sql是描述性语言，比较简单

使用JDBC从数据库读取数据

• 写java sql之前先确保sql引擎没问题，再搬到java代码

-- 查询那几个用户下过单,第一种方法
select count(distinct USER_ID) from `ORDER` where GOODS_ID = 1;
-- 子查询
select count(*) from user where id in
(
    select USER_ID from `ORDER` where GOODS_ID = 1
    )

select "ORDER".id, user.name as user_name, GOODS.NAME as goods_name, "ORDER".GOODS_ID, "ORDER".GOODS_NUM * "ORDER".GOODS_PRICE from "ORDER"
         join GOODS
              on "ORDER".GOODS_ID = GOODS.id
         join USER
                   on "ORDER".USER_ID = USER.ID

• 分页查询所有用户，按照ID倒序排列

select * from user order by ID desc limit (2-1) * 3, 2 * 3;

• 查询所有的商品及其销售额，按照销售额从大到小排序

select GOODS_ID, GOODS.NAME, GOODS_PRICE * SUM(`ORDER`.GOODS_NUM) as GMV
from GOODS join "ORDER" on GOODS.ID = "ORDER".GOODS_ID GROUP BY GOODS.NAME order by GMV desc ;

课后练习

• 2-practise-select-sql

package com.github.hcsp.sql;


import java.io.File;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Sql {
// 用户表：
// +----+----------+------+----------+
// | 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                            |
// +------------+-----------------+------------------+---------------------+-------------------------------+

    // 用户信息
    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 goodsId 指定的商品ID
     * @param databaseConnection 数据库连接
     * @return 有多少用户买过这个商品
     */
// 例如，输入goodsId = 1，返回2，因为有2个用户曾经买过商品1。
// +-----+
// |count|
// +-----+
// | 2   |
// +-----+

    public static int countUsersWhoHaveBoughtGoods(Connection databaseConnection, Integer goodsId) throws SQLException {
        try (PreparedStatement statement = databaseConnection.prepareStatement("select count(distinct USER_ID) from `ORDER` where GOODS_ID = ?")) {
            statement.setInt(1, goodsId);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                return resultSet.getInt(1);
            }
        }
        return -1;
    };

    /**
     * 题目2：
     * 分页查询所有用户，按照ID倒序排列
     *
     * @param pageNum  第几页，从1开始
     * @param pageSize 每页有多少个元素
     * @param databaseConnection 数据库连接
     * @return 指定页中的用户
     */
// 例如，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 {
        List<User> Users = new ArrayList<>();
        try (PreparedStatement statement = databaseConnection.prepareStatement("select * from user order by ID desc limit ?, ?;")) {
            statement.setInt(1, (pageNum - 1) * pageSize);
            statement.setInt(2, pageNum * pageSize);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                User User = new User();
                User.id = resultSet.getInt(1);
                User.name = resultSet.getString(2);
                User.address = resultSet.getString(4);
                User.tel = resultSet.getString(3);
                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 + '}';
        }
    }


// 预期的结果应该如图所示
//  +----+--------+------+
//  | ID | NAME   | GMV  |
//  +----+--------+------+
//  | 2  | goods2 | 2080 |
//  +----+--------+------+
//  | 1  | goods1 | 80   |
//  +----+--------+------+
//  | 4  | goods4 | 20   |
//  +----+--------+------+
//  | 3  | goods3 | 20   |
//  +----+--------+------+
    /**
     * 题目3：
     * 查询所有的商品及其销售额，按照销售额从大到小排序
     * @param databaseConnection 数据库连接
     * @return 售卖的商品的总金额
     */
    public static List<GoodsAndGmv> getGoodsAndGmv(Connection databaseConnection) throws SQLException {
        try (PreparedStatement statement = databaseConnection.prepareStatement("select GOODS_ID, GOODS.NAME, \n" +
                "       GOODS_PRICE * SUM(`ORDER`.GOODS_NUM) as GMV\n" +
                "from GOODS join \"ORDER\" on GOODS.ID = \"ORDER\".GOODS_ID \n" +
                "GROUP BY GOODS.NAME order by GMV desc ;")) {
            ResultSet resultSet = statement.executeQuery();
            List<GoodsAndGmv> GoodsAndGmvList = new ArrayList<>();

            while (resultSet.next()) {
                GoodsAndGmv GoodsAndGmv = new GoodsAndGmv();
                GoodsAndGmv.goodsId = resultSet.getInt(1);
                GoodsAndGmv.goodsName = resultSet.getString(2);
                GoodsAndGmv.gmv = resultSet.getBigDecimal(3);
                GoodsAndGmvList.add(GoodsAndGmv);
            }
            return GoodsAndGmvList;
        }
    }


    // 订单详细信息
    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 + '}';
        }
    }


// 预期的结果为：
// +----------+-----------+------------+-------------+
// | 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          |
// +----------+-----------+------------+-------------+

    /**
     * 题目4：
     * 查询订单信息，只查询用户名、商品名齐全的订单，即INNER JOIN方式
     * @param databaseConnection 数据库连接
     * @return 用户名订单详情
     */
    public static List<Order> getInnerJoinOrders(Connection databaseConnection) throws SQLException {
        try (PreparedStatement statement = databaseConnection.prepareStatement("select \"ORDER\".id, user.name as user_name, GOODS.NAME as goods_name, \"ORDER\".GOODS_ID, \"ORDER\".GOODS_NUM * \"ORDER\".GOODS_PRICE from \"ORDER\"\n" +
                "         join GOODS\n" +
                "              on \"ORDER\".GOODS_ID = GOODS.id\n" +
                "         join USER\n" +
                "                   on \"ORDER\".USER_ID = USER.ID")) {
            return getResultSet(statement);
        }
    }

    private static List<Order> getResultSet(PreparedStatement statement) throws SQLException {
        ResultSet resultSet = statement.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;
    }


// 预期的结果为：
// +----------+-----------+------------+-------------+
// | 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          |
// +----------+-----------+------------+-------------+

    /**
     * 题目5：
     * 查询所有订单信息，哪怕它的用户名、商品名缺失，即LEFT JOIN方式
     * @param databaseConnection 数据库连接
     * @return 用户名订单详情
     */
    public static List<Order> getLeftJoinOrders(Connection databaseConnection) throws SQLException {
        try (PreparedStatement statement = databaseConnection.prepareStatement("select \"ORDER\".id, user.name as user_name, GOODS.NAME as goods_name, \"ORDER\".GOODS_ID, \"ORDER\".GOODS_NUM * \"ORDER\".GOODS_PRICE from \"ORDER\"\n" +
                "         left join GOODS\n" +
                "              on \"ORDER\".GOODS_ID = GOODS.id\n" +
                "         left join USER\n" +
                "                   on \"ORDER\".USER_ID = USER.ID")) {
            return getResultSet(statement);
        }
    }

    // 注意，运行这个方法之前，请先运行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)); // done
            System.out.println(getUsersByPageOrderedByIdDesc(connection, 2, 2)); // done
//            System.out.println(getGoodsAndGmv(connection));
            System.out.println(getInnerJoinOrders(connection)); // done
//            System.out.println(getLeftJoinOrders(connection)); // done
        }
    }

}