Database Foundation and three main paradigms and basic additions and deletions to the search command

The so-called database is the establishment of the construction of the table constraints.
① determines that the entity is determined to have several tables
② determining the properties of a table
Three levels of normalization: three paradigms of database design!!!
First Paradigm (1NF): atomicity: Each column in the data table is the smallest unit that is not to be split, that is, the atomicity of each column.
For example: UserInfo: ' Shandong province Yantai 11233445667 '
Split into: ' Shandong province Yantai ' and ' 11233445667 ':

Second Normal form (2NF): After 1NF is satisfied, the requirement: all columns in the table depend on the primary key, and no one column has no relation to the primary key.
In other words, each table can only describe one thing.
For example, the order table can only describe the order-related information, so all fields must be related to the order ID;
Product tables, which can only describe product-related information, so all fields must be related to the product ID.

Third normal form (3NF): After 2NF is required, all fields in the table are related only to the primary key (dependent on the primary key) and not to other properties.
Make sure that each column is directly related to the primary key, not indirectly.
For example: In the Order form, customer-related information is required, after the Customer table is detached. The value in the order table needs to have a user ID,
and cannot have other customer information. Because the other user information is directly related to the user ID, not to the order ID,
"The essential difference between the second and third paradigms is that there are no two tables to be separated": The second paradigm is to say that a table contains a number of different questions of the attributes, then the sigh into more
Table, the third paradigm is that the number of tables has been divided, then, a table can only have another table ID (primary key), but not
There is any other information (other information, all with the primary key in another table query)

--Create a table
--If not EXISTS can omit the ellipsis and repeat the creation when the packet error occurs. If omitted, the creation will detect if the table already exists and no longer executes the CREATE statement if the table exists.
--Definition column: Column name data type column definition keyword
--Common column definition keywords:
--UNSIGNED: Set column as unsigned column, only set type to numeric type.
--Auto_increment: Sets the column for automatic growth. The auto-grow column must be a primary key.

--PRIMARY Key: Sets the PRIMARY KEY constraint.
--"PRIMARY key"
--1, primary key considerations? The primary key is empty by default! PRIMARY key default Uniqueness constraint!
--only the primary key can be set to auto-grow (the primary key does not necessarily increment, the self-increment must be the primary key).
--2, how to set the primary key? ① is set when the column is defined: The ID INT PRIMARY Key② is set after the definition is complete: PRIMARY KEY (ID);
-Unique: Sets a uniqueness constraint. Duplicate values cannot occur for this field. For example: Age SMALLINT UNIQUE,
--Default: Sets the defaults constraint. Height DOUBLE (3,2) default 1.2 heighit if not entered defaults to 1.2
--NOT NULL: sets a non-null constraint that cannot be empty.

--FOREIGN key: setting FOREIGN KEY constraints
--"FOREIGN key"
--1. What precautions to set the foreign key
--① only the INNODB data engine supports foreign keys
--Modify the My.ini file Settings default--storage_engine
The data type of the--② foreign key and the reference column must be the same, the string requires the same type, and 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 operation:
--3, FOREIGN KEY constraint referential operation
--Reference operation: How foreign keys in a foreign key table should be removed or updated when referencing a reference table field

--Reference Operation optional value: Restrict rejects the reference table deletion or updates the reference field.
-Noaction is the same as restrict, but this command only takes effect in MySQL
---CASCADE Delete or update the reference field of the residue table, the record of the foreign key table is synchronized delete update
--Set NULL to delete or update the Reference field of the reference table, and foreign key for the foreign key table to be set to NULL
--
Use MyDB;
--Delete Table
DROP TABLE IF EXISTS tb1;

CREATE TABLE IF not EXISTS tb1 (
ID INT UNSIGNED not NULL auto_increment PRIMARY KEY,
' Name ' VARCHAR (255),--name is the keyword, so use the anti-quote ' ' Wrap
Age SMALLINT,
Height DOUBLE (3,2) DEFAULT 1.2
--PRIMARY KEY (ID)
);
CREATE TABLE IF not EXISTS CLASSES (
ID INT UNSIGNED PRIMARY KEY auto_increment,
ClassName VARCHAR (255) Not NULL
);
CREATE TABLE IF not EXISTS ' USER ' (
ID INT UNSIGNED PRIMARY KEY auto_increment,
CLSID INT UNSIGNED,
' Name ' VARCHAR (255) is not NULL,
CONSTRAINT user_fk_classes FOREIGN KEY (CLSID) REFERENCES classes (ID) on DELETE SET NULL on UPDATE CASCADE
)

/*SQY Multi-line Comment */
--SQL Single line comment
--Creating User: Create user ' username ' [@ ' host name '] identified by ' password '
--the hostname can be empty and the default is% permission, which means that all hosts can connect
CREATE USER ' jredu ' @ ' localhost ' identified by ' jredu ';

Grant assigns permissions to the User: Grant permission name on database name. Table name to ' user ' @ ' host ';
--All indicates that all permissions *. * represents all tables in all users
GRANT all on * * to ' jredu ' @ ' localhost ';

GRANT SELECT on MySQL. ' User ' to ' jredu ' @ ' localhost ';

--REVOKE from delete user rights
REVOKE all on * * from ' jredu ' @ ' localhost ';

--Creating a database, create database[if not EXISTS] database name [CHARACTER SET [=] ' UTF8 '] [] represents writable and can be saved
--If you omit the if not exists error when creating the database repeatedly
CREATE DATABASE IF not EXISTS myDB3 CHARACTER SET ' UTF8 ';

--Delete database drop databases [IF EXISTS] Database name
DROP DATABASE IF EXISTS mydb2;

--Query all the databases in this computer
SHOW DATABASES;

--Using MyDB This database means that the following query will default to the MyDB database.
Use MyDB;

--Show all data tables in the database show Tables[from database]
SHOW TABLES from MySQL;

/*
Common data types in MySQL:

First, character type:
①char (N): Represents a fixed-length string, which is automatically padded if the length is insufficient. The range of n is 0-255
②varchar (): Stores variable-length strings. Most commonly used. About->0-(2^16-1) *10;
③text: Stores variable-length strings (commonly used with large sections of published articles). 0-65535*10^2;
④tinytext:0 ~ (2^8-1) *10;
⑤mediumtext:2~ (2^24-1) * 10^3;
⑥longtext:0~ (2^32-1) * 10^4;
⑦enum ("Male", "female"): Enum type, field can only hold the data enumerated.

Second, plastic surgery
①tinyint: unsigned 0-2^8-1; Signed -2^7 ~ 2^7-1;
②smallint: Unsigned 0~2^16-1 signed -2^15 ~ 2^15-1;
③mediumint: Unsigned 0~2^24-1 signed -2^23 ~ 2^23-1;
④int: Unsigned -2^32-1 signed -2^31 ~ 2^31-1;
⑤bigint: Unsigned 0~2^64-1 signed -2^63 ~ 2^63-1;
Third, floating-point type
①float can be accurate to seven digits after the decimal point
②double can be accurate to 15 digits to 16 digits after the decimal point
Iv. Date and time data types
Note: Because the time store uses a string or a timestamp store, there is almost no date type in the database.
①date: Storing date and time data
②timestamp: more accurate than date.

*/

　　

SHOW COLUMN from classes;

SHOW TABLES;
--Display the data table structure
SHOW COLUMNS from TB1;
--Display table
SHOW CREATE TABLE 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 ...];
RENAME TABLE tb3 to tb1, ' user ' to User1;

--
--Modify field columns
--ALTER table name change old column name new column list definition [first| After a column]
--First: adjust this field to a column after one of the table columns: Place This field after a column.
ALTER TABLE tb1 change ' uername ' Uername ' VARCHAR ($) not NULL after age;
--MODIFY only modifies column definitions and cannot be renamed
ALTER TABLE tb1 MODIFY ' Uername ' VARCHAR ($) not NULL after age;
--Delete a column in a table
ALTER TABLE tb1 DROP height;
--Add a list of required sections: ALTER TABLE tb1 ADD height DOUBLE (8,2)
ALTER TABLE tb1 ADD height DOUBLE (8,2) DEFAULT 1.2 after age;
--Added multiple columns can not adjust the position of the column, can only be inserted in the last.
ALTER TABLE tb1 ADD (
Weight DOUBLE (3,2) UNSIGNED,
School VARCHAR (255)

);

--Increase the PRIMARY KEY constraint
ALTER TABLE tb1 ADD PRIMARY KEY (age);

--Remove the PRIMARY KEY constraint to delete the self-increment
ALTER TABLE tb1 DROP PRIMARY KEY;
--New Uniqueness Constraint
ALTER TABLE tb1 ADD UNIQUE KEY (username);
--Delete Uniqueness constraint: The index is created when you delete it because creating a uniqueness constraint
ALTER TABLE tb1 DROP INDEX username;

--Set Default value constraint
Alter TABLE TB1 ALTER age SET DEFAULT 20;
--Delete default value constraint
Alter TABLE TB1 alter age DROP DEFAULT;
SHOW COLUMN from TB1;
SHOW TABLES;

Database Foundation and three main paradigms and basic additions and deletions to the search command