Database Design

Database Design

• 資料庫系統概論
• 設計資料庫的20個最佳實踐
• 不是工程師，外鍵Foreign key(FK)是什麼？從第二正規化(2NF)去除冗余資料談起吧！

jdbc設定for quarkus

MySQL

quarkus.datasource.db-kind=mssql
quarkus.datasource.username=user_name
quarkus.datasource.password=password
quarkus.datasource.jdbc.url=jdbc:sqlserver://ip_address_or_dns:port_number;databaseName=database_name
quarkus.datasource.jdbc.min-size=4
quarkus.datasource.jdbc.max-size=16

#quarkus.hibernate-orm.database.generation=drop-and-create
quarkus.hibernate-orm.log.sql=true
#quarkus.hibernate-orm.sql-load-script=import.sql

MSSQL(Azure)

quarkus.datasource.db-kind=mysql
quarkus.datasource.username=user_name
quarkus.datasource.password=password
quarkus.datasource.jdbc.url=jdbc:mysql://ip_address_or_dns:3306/database_name","username","password"
quarkus.datasource.jdbc.min-size=4
quarkus.datasource.jdbc.max-size=16

#quarkus.hibernate-orm.database.generation=drop-and-create
quarkus.hibernate-orm.log.sql=true
#quarkus.hibernate-orm.sql-load-script=import.sql

Azure Database Grammar

• 建立外部索引鍵關聯性

Link_table_PK_ID int,
CONSTRAINT FK_Name FOREIGN KEY (Link_table_PK_ID) REFERENCES table_name (Link_table_PK_ID) ON DELETE CASCADE ON UPDATE CASCADE

ex:
CREATE TABLE Device (
    PK_deviceID bigint NOT NULL PRIMARY KEY IDENTITY(1,1),
    SN varchar(32),
    PK_nkgID int, 
    CONSTRAINT FK_nkgNameID FOREIGN KEY (PK_nkgID) REFERENCES nNkgName (PK_nkgID) ON DELETE CASCADE ON UPDATE CASCADE
);

ref: 建立外部索引鍵關聯性

• Insert Data (include foreign key)

1. INSERT INTO table_name([column_name]) values ('string')

2. insert into table_name ([column_1], [FK_ID]) values ('string', (select FK_ID from FK_table_name where FK_table_name_condition));

ex:
1. INSERT INTO xxx([Names]) values ('124test')

2. insert into Device ([SN], [PK_ID]) values ('sn1234', (select PK_ID from Test where PK_ID = 1));

• Modify Column Name

EXEC sp_rename 'database_name.tabale_name.column_old_name', 'column_new_name', 'COLUMN';

ex:
EXEC sp_rename 'xxx.Device.sn', 'SN', 'COLUMN'

• Delete Data

DELETE FROM table_name WHERE the_condition;

ex:
DELETE FROM nNKGName WHERE PK_NKGID >= 0;

• Reset Count

DBCC CHECKIDENT (table_name, RESEED, the_int_value);
  
ex:
DBCC CHECKIDENT (xxx, RESEED, 0);

• Delete Table

DROP TABLE table_name;

MySQL 語法

show column

SHOW COLUMNS FROM table_name;

insert column

ALTER TABLE tb_name
ADD COLUMN new_col type FIRST|AFTER col_name;
--------------------------------------------------------
ex:
1.
ALTER TABLE vendors
ADD COLUMN phone VARCHAR(15) AFTER name;

2.
ALTER TABLE vendors
ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST name;

ref: mysql-add-column