水一程--软件开发--数据库--mysql

目的：如 Oracle 般精研 MySql DB 技术栈

---

1.MySql workbench

schema, table,

 

 

workbench update 更新触发 safe mode, 解决：

SET SQL_SAFE_UPDATES=0;
update practice_transaction.book_stock set STOCK = 10 WHERE ISBN = 0001

USE ENTITYMAPPINGS;

CREATE TABLE Publishers (
    PublisherId BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    PublisherName varchar(100) NOT NULL,
    Address varchar(100) not null,
    INDEX Publishers_Names (PublisherName)
)ENGINE = InnoDB;

CREATE TABLE Authors(
 AuthorId BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
 AuthorName varchar(100) not null,
 EmailAddress varchar(255) NOT NULL,
 INDEX Publisers_Names (AuthorName)
)engine = InnoDB;

CREATE TABLE Books(
 Id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
 Isbn varchar(13) not null,
 Title varchar(255) not null,
 Author varchar(100) not null,
 Price decimal(6,2) not null,
 Publisher varchar(100) not null,
 unique KEY Books_ISBNs (Isbn),
 INDEX Books_Titles (Title)
)engine = InnoDB;

JPA 为 Publiser 代理键使用表生成器，需要创建一个生成器表。

USE ENTITYMAPPINGS;

CREATE TABLE SurrogateKeys(
    TableName varchar(64) not null primary key,
    KeyValue BIGINT UNSIGNED NOT NULL,
    INDEX SurrogateKeys_Table_Values (TableName, KeyValue)
)engine = InnoDB