MySQL three major paradigms and database design and table creation common statements

"Three major paradigms of database design"

1. First normal form (1NF fromate): Each column (field) in the datasheet must be the smallest unit that cannot be split. That is to ensure the atomicity of each column.
For example: UserInfo: ' Shandong province Yantai 13181621008 '
=>userads: ' Yantai of Shandong Province '
电话: ' 13181621008 '

2. Second paradigm (2NF): After 1NF is satisfied, the requirement: all columns in the table must be functionally dependent on the primary key, and no one column has no relation to the primary key. (a table value describes one thing)

3. Third paradigm (3NF): After 2NF is satisfied, the requirement: Each column in the table is directly related to the primary key, not indirectly. (each column in the table can depend on the primary key only)

For example: In the Order form, customer-related information is required, after the Customer table is detached. In the Orders table, only one user ID is required
Can. and cannot have other customer information. Because, other user information is directly associated with the user ID. Instead of being associated with the order ID.

"The essential difference between the second paradigm and the third paradigm"
Is whether there are two tables, and the second paradigm is that a table contains the attributes of a variety of different entities, so it has to be divided into multiple tables. The third paradigm requires that multiple tables have been divided, so that only one table can have an ID (primary key) in another table, and no other information (other information, all using the primary key in another query).

*/

Use MyDB;

--Create a table:
Definition columns: Column name data type column definition keywords
Common Column Definition Keywords:
UNSIGNED: Set column as unsigned. Only columns of type number type can be set
Auto_increment: Sets the auto-Grow column. The auto-grow column must be a primary key.

"PRIMARY Key"
1. What are the primary key considerations? ① primary key is not empty by default! ② only primary key to set autogrow (primary key does not automatically grow, auto grow must be primary key)
2. How do I set the primary key? ① set when the column is defined: ID INT PRIMARY KEY
② set after the column definition is complete: PRIMARY KEY (ID)
Unique: Sets a uniqueness constraint. Duplicate values cannot occur for this field.
NOT NULL: Sets a non-null constraint. The field cannot be empty.
Default: Sets the defaults constraint. Hight DOUBLE (3,2) Default (1.2) height If you do not enter the defaults 1.2
FOREIGN key: Sets the foreign KEY constraint.
"Foreign Key"
1. What are the precautions for setting foreign keys?
1. What are the precautions for setting foreign keys?
Only the INNODB database engine supports foreign keys.
Modify the My.ini file Settings Default-storage-engine=innodb
② the data type of the foreign key and the reference column must be the same
③ the field that sets the foreign key must have an index. If there is no index, an index is automatically generated when the foreign key is set

[Numeric type requires the same length and unsigned, string requires the same type, the length can be different]

2, set the foreign key syntax?
[CONSTRAINT foreign Key name] FOREIGN Key (foreign key field) REFERENCES reference table (reference field)
[on DELETE set NULL on UPDATE CASCADE]--Set referential integrity

3, FOREIGN KEY constraints of the reference operation.
Referential actions: How foreign keys in the foreign key table should be addressed when deleting or updating reference fields for reference tables.
Reference operation Optional Value: DESTRTCT deny reference table delete or update reference field
NO ACTION is the same as restrict, but this command only takes effect in MySQL
CASCADE Delete or update a reference field for a reference table, the record for the foreign key table is synchronized to delete the update
Set NULL when deleting or updating a reference field for a reference table, the foreign key of the foreign key table is set to NULL
*/

CREATE TABLE IF  not EXISTSTB1 (--IF not EXISTS can be omitted, omit the following repeatedly create an error. If not omitted, whether the table already exists at the time of creation, and if the table exists, no longer does the CreateIdINTUNSIGNED not NULLAuto_incrementPRIMARY KEY, ' name 'VARCHAR(255),--name is a system keyword, so use the anti-quote ' ' WrapAgeSMALLINT UNIQUE, HightDOUBLE(3,2)DEFAULT(1.2)--PRIMARY KEY (ID));DROP TABLE IF EXISTSCLASSES;CREATE TABLE IF  not EXISTSCLASSES (IDINTUNSIGNEDPRIMARY KEYAuto_increment,classnameVARCHAR(255) not NULL);DROP TABLE IF EXISTS`USER`;CREATE TABLE IF  not EXISTS`USER' (IDINTUNSIGNEDPRIMARY KEYAuto_increment,clsidINTUNSIGNED not NULL, ' NAME 'VARCHAR(255) not NULL,CONSTRAINTUser_fk_classesFOREIGN KEY(CLSID)REFERENCESclasses (ID)); SHOW COLUMNS fromCLASSES;

SHOW TABLES;

--Display table structure

 from TB1;

--Display the table statement

CREATE TABLES TB1;

--Delete Table

DROP TABLE IF EXISTS tb1;

--Modify Table name ALTER TABLE name RENAME [to] new table name;

ALTER TABLE TB1 RENAME TB2;

--Simultaneous modification of multiple table names RENAME table TB3 to tb1[, ' USER ' to USER1 ...];

TABLE  to TB1, 'USER to USER1;

--Modify field columns
--ALTER table name change old column name new column list definition [First | After a column]
--First the field is adjusted to a column after one of the table columns: Place This field after a column

ALTER TABLE VARCHAR (No.NULL after age;

--MODIFY only modify the definition, cannot change the name

ALTER TABLE VARCHAR (No.NULL after age;

--Delete a column in a table

ALTER TABLE DROP HEIGHT;

--Add a column must part: ALTER TABLE tb1 ADD HEIGHT DOUBLE (8,2)

ALTER TABLE ADD DOUBLE (8,2DEFAULT1.2 after age;

--Added multiple columns can not adjust the position of the column, can only be inserted in the last.

ALTER TABLE ADD  DOUBLE(3,2VARCHAR(255))

--Increase the PRIMARY KEY constraint

ALTER TABLE ADD PRIMARY KEY (ID);

--Delete primary KEY constraint

ALTER TABLE DROP PRIMARY KEY;

--New Uniqueness Constraint

ALTER TABLE ADD UNIQUE KEY (USERNAME);

--Delete Uniqueness constraint: Because creating a uniqueness constraint creates an index by default, you delete the index when you delete it

ALTER TABLE DROP INDEX USERNAME;

--Set Default value constraint

ALTER TABLE ALTER SET DEFAULT ;

--Delete default value constraint

ALTER TABLE ALTER DROP DEFAULT;

--Set FOREIGN KEY constraints required: ALTER TABLE tb1 ADD FOREIGN KEY (CLSID) REFERENCES CLASSES (ID)

ALTER TABLE ADD FOREIGN KEY REFERENCES  on DELETE SET NULL  on UPDATE CASCADE;

--Delete the foreign KEY constraint. Because the foreign key is created by default

ALTER TABLE DROP FOREIGN KEY   from TB1; SHOW TABLES;

MySQL three major paradigms and database design and table creation common statements