DDWiki/分享/速e/10.Mysql的一些建表规则.md

选择数据库。

登录：mysql -u root -p
使用某个数据库：use classicmodels;
获取目前连接的数据库：select database();

创建数据库。

• mysql -u root -p
• show databases;
• create database testdb;

删除数据库

• DROP DATABASE testdb;

数字类型

• TINTYINT
• SMALLINT
• MEDIUMINT
• INT
• BIGINT
• DECIMAL
• FLOAT
• DOUBLE
• BIT

bool类型

• TINYINT(1)

字符串类型

• CHAR
• VARCHAR
• BINARY
• VARBINARY
• TINTYBOLOB
• BLOG
• MEDIUMBLOB
• LONGBLOG
• TINYTEXT
• EXT
• MEDIUMTEXT
• LONGTEXT
• ENUM
• SET
  

时间类型

• DATE
• TIME
• DATETIME
• TIMESTAMP
• YEAR

创建表简单表

CREATE TABLE IF NOT EXISTS tasks (
    task_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    start_date DATE,
    due_date DATE,
    status TINYINT NOT NULL,
    priority TINTYINT NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=INNODB;

创建表包含外键

CREATE TABLE IF NOT EXISTS checklists (
    todo_id INT AUTO_INCREMENT,
    task_id INT,
    todo VARCHAR(255) NOT NULL,
    is_completed BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (todo_id, task_id),
    FOREIGN KEY (task_id)
        REFERENCES tasks (task_id)
        ON UPDATE RESTRICT ON DELETE CASCADE
)

主键

主键是唯一的，不能为NULL。如果主键有多个，多个主键组合需要是唯一的。

只有一个主键。

CREATE TABLE table_name (
    primary_key_column datatype PRIMARY KEY,
)

或者

CREATE TABLE table_name (
    priamry_key_column  datatype,
    ...
    PRIMARY KEY(primary_key_column)
)

多个主键。

CREATE TABLE table_name (
    primary key_column1 datatype,
    priamry key_column2 datatype,
    ...
    PRIMARY KEY(key_column1, key_column2)
)

实际例子。

CREATE TABLE users(
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(40),
    password VARCHAR(255),
    email VARCHAR(255)
);

CREATE TABLE roles(
    role_id INT AUTO_INCREMENT,
    role_name VARCHAR(50),
    PRIMARY KEY(role_id)
)

CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    PRIMARY KEY(user_id, role_id)
    FOREIGN KEY(user_id)
        REFERENCES users(user_id)
    FOREIGN KEY(role_id)
        REFERENCES roles(role_id)
)

为一个表增加PRIMARY KEY

CREATE TABLE pkdemos(
    id INT,
    title VARCHAR(255) NOT NULL
)

ALTER TABLE pkdemos
ADD PRIMARY KEY(id);

增加唯一约束

ALTER TABLE users
ADD UNIQUE INDEX username_unique (username ASC)

ALTER TABLE users
ADD UNIQUE INDEX email_unique (email ASC)

外键

CREATE DATABASE fkdemo;

USE fkdemo;

CREATE TABLE categories(
    categoryId INT AUTO_INCREMENT PRIMARY KEY,
    categoryName VARCHAR(100) NOT NULL
) ENGINE=INNODB;

CREATE TABLE products(
    productId INT AUTO_ICREMENT PRIMARY KEY,
    productName varchar(100) NOT NULL,
    categoryId INT,
    CONSTRAINT fk_category
    FOREIGN KEY(categoryId)
        REFERENCES categoreis(categoryId)
) ENGINE=INNODB;

INSERT INTO categories(cateogryName) VALUES(''),('');

SELECT * FROM categories;

INSERT INTO products(productName, categoryId) VALUES ('i',1);

INSERT INTO products(productName, categoryId) VALUES ('',3)
Error Code:1452. Cannot add or update a child row: a forein key constrain fails

UPDATE categories SET categoryId=100 WHERE categoryId=1;
Error Code:1451. 因为categoryId被products表引用着，不能修改

DROP TABLE produducts;

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT NOT NULL,
    CONSTRAINT fk_category
    FOREIGN KEY(categoryId)
    REFERENCES categories(categoryId)
        ON UPDATE CASCASE
        ON DELETE CASCADE
) ENGINE=INNODB;

INSERT INTO products(productName, categoryId)
VALUES
    ('',1),
    ('',1),
    ('',2),
    ('',2);

SELECT * FROM products;

UPDATE categories 
SET categoryId=100
WHERE categoryId=1;

SELECT * FROM categories;
SELECT * FROM products;

DELETE FROM categories 
WHERE categoryId=2;

SELECT * FROM categories;
SELECT * FROM products;

接下来尝试可以设置为NULL

DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS products;

CREATE TABLE categories(
    categoryId INT AUTO_INCREMENT PRIMARY KEY,
    categoryName VARCHAR(100) NOT NULL
)ENGINE=INNODB;

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT,
    CONSTRAINT fk_category
    FORIEGN KEY(categoryId)
        REFERENCE categories(categoryId)
        ON UPDATE SET NULL 当外键表这个键没有的时候这里设置为null
        ON DELETE SET NULL
)ENGINE=INNODB;

INSERT INTO categories(categoryName)
VALUES
    (''),
    ('')

INSERT INTO products(productName, categoryId)
VALUES
   ('iPhone', 1), 
    ('Galaxy Note',1),
    ('Apple Watch',2),
    ('Samsung Galary Watch',2);

UPDATE categories 
SET categoryId =100
WHERE categoryId=1;

SELECT * FROM categories;
SELECT * FROM products; 发现categoryId为null

展示外键
SHOW CREATE TABLE products;

删除外键
ALTER TABLE products
DROP FOREIGN KEY fk_category;

禁用外键
SET foreign_key_checks=0;

启用外键
SET foreign_key_checks=1;

唯一约束

CREATE TABLE suppliers (
    supplier_id INT AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(15) NOT NULL UNIQUE,
    address VARCHAR(255) NOT NULL,
    PRIMARY KEY(supplier_id),
    CONSTRAINT uc_name_address UNIQUE (name, address)
)

展示约束
SHOW CREATE TABLE suppliers;

展示索引
SHOW INDEX FROM suppliers;

删除约束
DROP INDEX uc_name_address ON suppliers;

添加唯一约束
ALTER TABLE supplers
ADD CONSTRAINT uc_name_address
UNIQUE(name, address)