MySQL入门

MySQL

1.1 启动MySQL

设置/命令行

1.2 登录

mysql -u root -p
[yourpasswd]

1.3 mysql界面

• 创建数据库

  CREATE DATABASE name;

• 显示数据库信息

  SHOW DATABASES;

1.4 VScode 插件

• SQLTools
• MySQL插件驱动

1.5 连接数据库

• 默认设置

2.1 注释和@block

-- comment
-- @block 
-- 用于局部执行代码

2.2 创建表格

CREATE TABLE Users(
    id INT PRIMARY KEY AUTO_INCREMENT,
    -- 数据类型 主键 自动增长
    email VARCHAR(255) NOT NULL UNIQUE,
    -- 字符串长度最大255 非空 唯一
    bio TEXT,
    -- 未指定大小的字符串
    country VARCHAR(2)
);

2.3 添加数据

INSERT INTO Users(email, bio, country)
VALUES 
    ("lzh@heihei.com", "594", "US"),
    ("biu@buaa.edu.cn", "999", "EN");

2.4 检索数据

SELECT email, id FROM Users
-- 整个表单可使用*
WHERE country = "CN"
OR email LIKE "l%"
-- AND id > 1

ORDER BY id ASC
-- ASC 升序 DESC 降序
LIMIT 2;
-- 限制返回表单数据条数

2.5 建立索引

-- 顺序查找在大量数据时查找速度缓慢，可以为某列建立索引
CREATE INDEX email_index ON Users(email);

3.1 建立关系表

一个人可以拥有多个房间

CREATE TABLE Rooms(
    id INT AUTO_INCREMENT,
    street VARCHAR(255),
    owner_id INT NOT NULL,
    PRIMARY KEY(id),
    -- 主键
    FOREIGN KEY(owner_id) REFERENCES Users(id)
    -- 外键指向Users（id）
);

3.2 添加关系表数据

INSERT Rooms (owner_id, street)
VALUES
    (1, 'big street'),
    (1, 'small street'),
    (1, 'eeds street'),
    (1, 'xlht road');

3.3 关系查询JOIN

-- @block
-- 选取房东id=用户id
SELECT * FROM Users
INNER JOIN Rooms
ON Rooms.owner_id = Users.id;

-- @block
-- 左连接：选取所有用户(可能不拥有room)
SELECT * FROM Users
LEFT JOIN Rooms
ON Rooms.owner_id = Users.id;

-- @block
-- 右连接：选取所有房间（可能没有房东）
SELECT * FROM Users
RIGHT JOIN Rooms
ON Rooms.owner_id = Users.id;

3.4 检索列设置别名

SELECT
    Users.id AS user_id,
    Rooms.id AS rooms_id,
    email,
    street
FROM Users
INNER JOIN Rooms
ON Rooms.owner_id = Users.id;

4.1 预定关系——建立预定表

-- @block
-- 创建预定表
CREATE TABLE Bookings(
    id INT AUTO_INCREMENT,
    guest_id INT NOT NULL,
    room_id INT NOT NULL,
    check_in DATETIME,
    PRIMARY KEY(id),
    FOREIGN KEY(guest_id) REFERENCES Users(id),
    FOREIGN KEY(room_id) REFERENCES Rooms(id)
);

4.2 查询用户预订的房间

-- @block Rooms a user has booked
SELECT
    guest_id,
    street,
    check_in
FROM Bookings
INNER JOIN Rooms
On Rooms.owner_id = guest_id
WHERE guest_id = 1;

4.3 查询房间的用户预定记录

-- @block Guests who stayed in a room
SELECT
    room_id,
    guest_id,
    email,
    bio
FROM Bookings
INNER JOIN Users
On Users.id = guest_id
WHERE room_id = 2;