Java-025-sql进阶

进阶—SQL语言

SQL 语言

SQL 全称 Structured Query Language, 主要用来查询和更新数据库的一个语言.

SQL 也就是关系数据库的一个标准化交互接口, 而关系数据库中的数据是结构化, 主要的一个操作就是从数据库中查询我们需要的内容.

结构化数据

数据表

关系数据库的结构主题是数据表, 数据库可以说是数据表的合集. 然后数据表中就包含具体的数据记录. 一个数据表会有不同的字段.

  • 数据表
  • 字段 / 主键
  • 记录

主键是每一条数据记录的唯一标志, 同一张数据表中, 所有记录的主键都是不同的!

具体实例就是用户数据库, 我们可以设置用户 id 作为主键, 然后就可以通过用户 id 来唯一确定一个用户. 其他的字段, 用来表示用户的属性, 比如用户的名字, 用户的性别, 用户密码之类属性, 从语义上说, 不同用户是可以有相同的名字, 性别或密码.

用户表 user:

id name type password content
0 Alex normal 123456 Here is the first
1 Alice normal 123456 Here is thhe first user
2 Alex vip password Here is the second Alex

数据库是表的合集, 然后不同表之间可能有相同的字段 (一般是其中一个表的主键), 也就是说, 这些表通过这些相同的字段给联系在一起了, 也就是所谓的关系. 我们查询过程中, 也是通过这些键从不同的数据表中查询到相关的数据.

订单表 order:

id user_id product_id quantity timestamp
0 0 15 5 203231232
1 2 15 1 324324234
2 2 1 3 213213219

上面两个示例表, 可以通过用户id在用户表中查询一个用户的信息, 然后再在订单表中查询这个用户的订单.

SQL数据类型
请参考: SQL数据类型

数据约束
请参考: SQL约束

常用查询(更新, 管理)语句

创建数据库
1
2
CREATE DATABASE shop;
USE shop; // 使用shop这个数据库, 之后在这个数据库下执行语句
创建数据表

用户表 user:

1
2
3
4
5
6
7
CREATE TABLE `user` (
    id          INTEGER         PRIMARY KEY NOT NULL,
    name        VARCHAR(20)     UNIQUE NOT NULL, 
    type        VARCHAR(25)     NOT NULL,
    password    VARCHAR(25)     NOT NULL,
    content     VARCHAR(255)     
);

订单表 order:

1
2
3
4
5
6
7
8
CREATE TABLE `order` (
    id          INTEGER        PRIMARY KEY NOT NULL,
    user_id     INTEGER        NOT NULL, 
    product_id  INTEGER        NOT NULL,
    quantity    INTEGER        NOT NULL,     
    time        DATETIME       NOT NULL,
    status      VARCHAR(20)    NOT NULL
);
插入数据

插入数据到user表

1
2
3
4
5
6
7
8
INSERT INTO `user` (name, type, password, content)
VALUES ('alex', 'ADMIN', 'password', '管理员账号, 属于shiyang');

INSERT INTO `user` (name, type, password, content)
VALUES ('alic', 'NORMAL', 'password', '普通账号');

INSERT INTO `user` (name, type, password, content)
VALUES ('bob', 'NORMAL', 'password', '普通账号Bob');

插入数据到order表

1
2
3
4
5
INSERT INTO `order` (user_id, product_id, quantity, time, status)
VALUES (1, 1, 5, CURRENT_TIMESTAMP, 'PENDING');

INSERT INTO `order` (user_id, product_id, quantity, time, status)
VALUES (2, 1, 1, CURRENT_TIMESTAMP, 'PENDING');
查询

基本查询语法

1
SELECT 通配符,字段或函数 FROM 表 WHERE 条件 group by 字段

实例

查询所有用户信息:

1
SELECT * FROM `user`;

通过用户id查询用户名和密码:

1
2
SELECT name FROM `user` WHERE id = 1;
SELECT name, password FROM `user` WHERE id = 1;

查询特定数量范围内的订单:

1
SELECT * FROM `order` WHERE quantity between 1 and 10;

根据用户类型分组查询用户:

1
SELECT * FROM `user` GROUP BY type;
更新数据
1
2
UPDATE `user` SET password = '123456'  WHERE id = 1;
UPDATE `user` SET content = '这是更新之后的内容', password = '09876' WHERE id = 1;
Join 查询

SQL 的 Join 子句用于结合两个或多个数据库中表的记录. JOIN 是一种通过共同值来结合两个表中字段的手段. 也就是所谓的关系.

比如: 我们需要查询一个用户的用户名简介及其所有订单商品的id和数量, 这种查询应该怎么做呢?

主要难点: 需要查询的内容来自两个表

Cross Join

Cross join把两个表的所有行进行叉乘拼接 (XY). 即表A的每一行会拼接表B的的所有行 (n行), 然后生成n行数据. 假设表A有x行, 表B有y行, 则输输出总行数为xy行.

1
2
3
SELECT * FROM `user` CROSS JOIN `order`;

SELECT * FROM `user` CROSS JOIN `order` WHERE `user`.id = 1 AND `order`.user_id = 1;

Inner Join

Inner Join和Cross Join相似, 只是可以接收一个条件, 然后把满足这个条件的行组合再一起.

比如: 我们需要查询用户的信息, 简介及其所有订单商品的id和数量, 这种查询应该怎么做呢?

1
SELECT * FROM `user` INNER JOIN `order` ON `user`.id =  `order`.user_id;

Outter Join

JAVA执行SQL

`
CREATE TABLE product (
id INTEGER PRIMARY KEY NOT NULL,
name VARCHAR(20) NOT NULL,
description VARCHAR(25) NOT NULL,
price DOUBLE NOT NULL
);
INSERT INTO product (name, description, price)
VALUES (‘milk’, ‘1 milk 1 day, healthy’, 2.50);

INSERT INTO product (name, description, price)
VALUES (‘coke’, ‘1 coke 1 day, unhealthy’, 1.50);