JAVA11-SQL与JDBC

常用SQL语句

1

SELECT * FROM table_name -- 查询表中所有数据

1

SELECT table1.column_name table2.column_name FROM table1 table2 -- 多表查询

1

SELECT * FROM table_name WHERE xxx=xxx -- 条件查询

1

WHERE condition IN (SELECT ...) -- 子查询

1

ORDER BY column_name DESC -- 排序

1

GROUP BY column_name -- 分组

1

LEFT JOIN table_name alias ON column_name=alias.column_name -- 左连接

1

RIGHT JOIN table_name alias ON column_name=alias.column_name -- 右连接

1

JOIN table_name alias ON column_name=alias.column_name -- 内连接

1

LIMIT n -- 只显示最后n行

1

LIMIT n OFFSET z -- 显示n条,从第z行开始

1

DISTINCT -- 去重

常用SQL函数

1

NOW() -- 求当前时间`

1

COUNT() -- 求数量`

1

SUM() -- 求和

1

MAX() -- 求最大值`

1

MIN() -- 求最小值`

1

AVG() -- 求平均值`

JOIN/LEFT JOIN/RIGHT JOIN 的区别

join

SQL Test

请在这里编写一条SQL语句，创建一个名为USER的表

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


-- ID           BIGINT         主键,自增
-- NAME         VARCHAR(100)   不可为空
-- TEL          VARCHAR(20)    不可为空,不可重复
-- ADDRESS      VARCHAR(100)   可为空
-- CREATED_AT   TIMESTAMP      不可为空
-- UPDATED_AT   TIMESTAMP      不可为空
-- STATUS       TINYINT        不可为空
CREATE TABLE user
(
    id         BIGINT PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(100) NOT NULL,
    tel        VARCHAR(20)  NOT NULL UNIQUE,
    address    VARCHAR(100),
    created_at TIMESTAMP    NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP    NOT NULL DEFAULT NOW(),
    status     TINYINT      NOT NULL DEFAULT 1
)

请在这里编写一条SQL语句，创建一个名为GOODS的表

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


-- ID           BIGINT         主键,自增
-- NAME         VARCHAR(100)   不可为空
-- PRICE        DECIMAL        不可为空
-- LEFT         INT            不可为空,默认为0
-- CREATED_AT   TIMESTAMP      不可为空
-- UPDATED_AT   TIMESTAMP      不可为空
-- STATUS       TINYINT        不可为空
CREATE TABLE goods
(
    id         BIGINT PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(100) NOT NULL,
    price      DECIMAL      NOT NULL,
    `left`     INT          NOT NULL DEFAULT 0,
    created_at TIMESTAMP    NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP    NOT NULL DEFAULT NOW(),
    status     TINYINT      NOT NULL DEFAULT 1
)

请在这里编写一条SQL语句，创建一个名为ORDER的表

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


-- ID           BIGINT        主键,自增
-- USER_ID      BIGINT        不可为空
-- GOODS_ID     BIGINT        不可为空
-- GOODS_NUM    INT           不可为空
-- GOODS_PRICE  DECIMAL       不可为空
-- CREATED_AT   TIMESTAMP     不可为空
-- UPDATED_AT   TIMESTAMP     不可为空
-- STATUS       TINYINT       不可为空
CREATE TABLE `order`
(
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id     BIGINT    NOT NULL,
    goods_id    BIGINT    NOT NULL,
    goods_num   INT       NOT NULL,
    goods_price DECIMAL   NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMP NOT NULL DEFAULT NOW(),
    status      TINYINT   NOT NULL DEFAULT 1
)

请在这里编写一条SQL语句，向USER插入一条测试数据

1
2
3
4
5
6
7
8


-- ID           100
-- NAME         'TEST_USER'
-- TEL          '1234567890'
-- ADDRESS      NULL
-- CREATED_AT   NOW()
-- UPDATED_AT   NOW()
-- STATUS       1
INSERT INTO user ( id, name, tel, address ) VALUES (100, 'TEST_USER', '1234567890', NULL );

请在这里编写一条SQL语句，在GOODS表中插入一条数据

1
2
3
4
5
6
7
8


-- ID           200
-- NAME         'TEST_GOODS'
-- PRICE        123.00
-- LEFT         1
-- CREATED_AT   NOW()
-- UPDATED_AT   NOW()
-- STATUS       1
INSERT INTO goods ( id, name, price, `LEFT` ) VALUES ( 200, 'TEST_GOODS', 123.00, 1 );

请在这里编写一条SQL语句，插入一条ORDER

1
2
3
4
5
6
7
8
9


-- ID           1
-- USER_ID      100
-- GOODS_ID     200
-- GOODS_NUM    1
-- GOODS_PRICE  123.00
-- CREATED_AT   NOW()
-- UPDATED_AT   NOW()
-- STATUS       1
INSERT INTO `ORDER` (id, user_id, goods_id, goods_num, goods_price) VALUES ( 1, 100, 200, 1, 123.00 );

请在这里编写一条SQL语句，将ID为1的订单进行"逻辑删除"

1

UPDATE `ORDER` SET status=-1 WHERE ID=1;

JDBC(Java Database Connection)

JDBC提供了访问世界上所有数据库的能力(标准)
它是JAVA提供的一套接口，只要数据库实现了这套接口，那么你就可以通过JDBC去访问它
连接JDBC需要提供一条连接字符串

JDBC Test

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


public class Main {
    public static void main(String[] args) throws SQLException {
        String jdbcUrl = "jdbc:mysql://localhost:33066/test";
        System.out.println(verifyUser(jdbcUrl, "zhangSan", "123"));
    }

    private static boolean verifyUser(String url, String username, String password) throws SQLException {
        try (Connection connection = DriverManager.getConnection(url);
             PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM student WHERE username = ? and password = ?")) {
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);
            ResultSet resultSet = preparedStatement.executeQuery();
            return resultSet.next();
        }
    }
}

获取项目路径下的文件技巧

1
2
3
4
5
6
7
8
9


public class Main {
    private static Connection connection;
    
    public static void setConnection() throws SQLException {
        File projectDir = new File(System.getProperty("basedir", System.getProperty("user.dir")));
        String jdbcUrl = "jdbc:h2:file:" + new File(projectDir, "target/test").getAbsolutePath();
        connection = DriverManager.getConnection(jdbcUrl, "root", "Jxi1Oxc92qSj");
    }
}

MySQL Docker

1
2
3
4


# 安装MySQL
docker run -p 33066:3306 --name MYSQL5.7 -e MYSQL_ROOT_PASSWORD=123 -d mysql:5.7
# 数据持久化 -v
docker run -p 33066:3306 --name MYSQL5.7 -v D:/mysql-data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123 -d mysql:5.7

JAVA11-SQL与JDBC

相关文章：