MySQL Common operations (i)

"Three major paradigms of database design"
1. First paradigm (1NF): Each column in the data table must be the smallest unit that cannot be split. That is to ensure the atomicity of each column.
For example: UserInfo: ' Shandong province Yantai 18865518189 ' should be split into Userads= ' Shandong province Yantai ' usertel= ' 18865518189 '
2, the second paradigm (2NF): Meet 1NF, requirements: All the columns in the table must depend on the primary key, and not any one column is not related to the primary key, that is, a table only describes one thing;
For example: Order form, which can only describe 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
Therefore, you cannot have both order information and product information in a single table.
3. The third paradigm (1NF): 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,
Only dependent on primary key)
For example: In the order form, you need to have customer-related information, after separating out the Customer table, the order form, only need to have a customer ID.
and cannot have other customer information. Because other user information is directly related to the user ID, not to 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 attributes of many different entities that must be divided into multiple tables,
The third paradigm is the request has been divided into a number of tables, then a table can only have another table ID, and can not have any other information, (any other information, all with the primary key in another table query)

Create a table

CREATE TABLE IF  not EXISTStb1 (IDINTUNSIGNED not NULL PRIMARY KEYauto_increment, ' name 'VARCHAR(255) not NULL, AgeSMALLINT UNIQUE, HeightDOUBLE(3,2)DEFAULT 1.2--PRIMARY KEY (ID));

Definition columns: Column name data type column definition keywords
Name is a system keyword, so use the anti-quote wrapping
If not exists can be omitted, omitted if the table already exists, repeated creation will be an error
Common Column Definition Keywords:
UNSIGNED: Set column as unsigned column, only columns of type number type
PRIMARY key: Setting a PRIMARY KEY constraint
Auto_increment: Set to Auto-Grow column, auto-grow column must be primary key

"PRIMARY Key"
1, the primary key note: The primary key is not empty by default! Only gradually can the automatic growth be set (not necessarily self-increment, self-increment must be the primary key)
2, set the way: ① in the column definition is set: ID INT UNSIGNED PRIMARY KEY
② set after the column definition is complete: PRIMARY KEY (ID)
PRIMARY key: Setting a PRIMARY KEY constraint
NOT NULL: Set column as non-null constraint
Unique: Sets a uniqueness constraint. Duplicate values cannot appear in this field
Default: Sets the defaults constraint, height DOUBLE (3,2) default 1.2 height if not entered, defaults to 1.2
FOREIGN key: Sets the foreign KEY constraint.

"Foreign Key"
1. What are the precautions for setting foreign keys?
① only the INNODB database engine supports foreign keys and modifies My.ini files
② the data type of the foreign key and the reference column must be the same (the numeric type requires the same length and unsigned, the string requires the same type and the length can be different)
③ the field that sets the foreign key must have an index, and if there is no index, the index is automatically generated when the foreign key is set and the index is deleted when the foreign key is deleted
2. Setting 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. Referential operation of FOREIGN KEY constraints
Referential action: How foreign keys in the outer check table should be removed or updated when referencing a reference table.
Reference operation Optional Value: RESTRICT deny reference table delete or update reference field
NO ACTION is the same as restrict, but this command is only available 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

--Modify table name ALTER TABLE old table name RENAME [to] new table name;ALTER TABLETB2 RENAME totb3;--simultaneous modification of multiple tables RENAME table Tb3 to tb1[, ' USER ' to User1];RENAMETABLETb3 toTB1, 'USER` touser1;--Modify Field Columns--ALTER table name change old column name new column list definition [first| After a column];--Firet: Adjust this field to the first column of the table after a column: Place this field after a columnALTER TABLETB1 change ' name ' ' username 'VARCHAR( $) not NULLAfter age ;--only the column definition is modified and cannot be renamedALTER TABLETB1 MODIFY ' username 'VARCHAR( $) not NULLAfter age ;--Delete a column in a tableALTER TABLETb1DROPheight;--added a new column, default optional, and later optionalALTER TABLETb1ADDHeightDOUBLE(8,2)DEFAULT 1.2After age ;--added multiple columns, can not adjust the position of the column, can only be inserted in the lastALTER TABLETb1ADD(weightDOUBLE(3,2) UNSIGNED, schoolVARCHAR(255));--increase the PRIMARY KEY constraintALTER TABLETb1ADD PRIMARY KEY(ID);--Delete a primary KEY constraintALTER TABLETb1DROP PRIMARY KEY;--Add Uniqueness ConstraintALTER TABLETb1ADD UNIQUE KEY(username);--Remove Uniqueness constraint: Because creating a uniqueness constraint creates an index by default, deleting the index is requiredALTER TABLETb1DROP INDEXusername;--Set default value ConstraintsALTER TABLETb1ALTERAgeSET DEFAULT  -;--Delete a default value constraintALTER TABLETb1ALTERAgeDROP DEFAULT;--set FOREIGN KEY constraint: ALTER TABLE tb1 ADD FOREIGN KEY (CLSID) REFERENCES classes (ID) [on DELETE set NULL on UPDATE CASCADE];ALTER TABLETb1ADD FOREIGN KEY(CLSID)REFERENCESClasses (ID) on DELETE SET NULL  on UPDATE CASCADE;--Delete foreign key constraint, because the index is created by default when the foreign key is created, so delete the index after deleting the foreign keyALTER TABLETb1DROP FOREIGN KEYuser_fk_classes;ALTER TABLETb1DROP INDEXuser_fk_classes;

"Composition of SQL statements"
DML Data Manipulation language (inserting, deleting, and modifying data in a database) insert UPDATE Delete
DQL data Query Language (used to query data in a database) SELECT
DCL Data Control Language (used to control access permissions, access rights, etc.) GRANT REVOKE
DDL Data Definition language (columns used to build databases, database objects, and definition tables) Create DROP

--Data INSERT statement Insert--INSERT [into] table name [column name] values (column value) [, (column value),......]--NOTE: If column names are omitted, subsequent column values must be assigned values for all columns, including self-increment columns and default columns, and if column names are not omitted, subsequent column values need to correspond to the preceding column name one by one, and all non-null columns need to be assigned valuesINSERT  intoTB1 (Username,age,sex)VALUES("Zhang San", A, "female");INSERTTb1VALUES(7, "Zhang San", A, "female");INSERT  intoTB1 (Username,age,sex)VALUES("Zhang San", A, "female"), ("Zhang San", A, "female");INSERT  intoTB1 (Username,age,sex)VALUES("Zhang Zhang", A, "female");

-- Data UPDATE statement -- UPDATE table name set column name = column value [, Column name = column value,......] [While condition] -- The where condition can be omitted, but indicates that all rows in the table are modified UPDATE set Sex="male"UPDATESET username="Li two Dog", age= Sex=WHERE id=8

"Common Where Condition judgment"
1. Relational operators:> < <> =! = >= <=
2. Logical operation: Not and OR
3, ISNULL (field) detection is empty
SELECT * from Tb1 WHERE ISNULL (age); --All users with an empty age
4. Between ... And ... Between two values
SELECT * from TB1 WHERE age between and 13;
5, in the data between some values
SELECT * FROM tb1 WHERE ID in (1,3,5,7,9);
6, like: similar matching
① similar matches, use% to indicate any number of characters (0 to more)
SELECT * FROM TB1 WHERE username like "Zhang%"; Start with a sheet
SELECT * from TB1 WHERE username like "% Zhang%"; (any position included)
SELECT * FROM TB1 WHERE username like "% Zhang"; End with a sheet
② a similar match, use _ to indicate the number of characters
SELECT * from TB1 WHERE username like "_ Zhang%"; The second word is Zhang
7, EXISTS (subquery statement): If the subquery returns a data >=1 row, EXISTS returns True, otherwise returns false
SELECT * FROM Tb1 WHERE EXISTS (SELECT * from TB2);--if TB2 returns at least 1 rows of data, the condition is true, return TB1
8, all (sub-query): Sub-query returned data, need to meet all, in order to set up
SELECT * from TB1 where username!=all (SELECT username from tb1 where id>7);--user name cannot be equal to subquery returns any of the list of user names
9, all (sub-query): Sub-query returned data, meet one of them, can be set up
SELECT * from TB1 where username =any (SELECT username from tb1 where id>7);--User name equals subquery returns any of the list of user names

Delete data from a table
DELETE [from] tb1 WHERE username like "_ Zhang%";

Data query Statements
SELECT column name, [column name,......] From table name order by ID The column name is sorted by Asc/desc;
SELECT * Indicates that all fields are queried
Order by indicates that the data being detected is sorted and must be behind the where

--column-expression--based on the existing columns, the query results, we use the case structure of the virtual column, called the column expression--As keyword--used to alias a column name, the AS keyword can also be omittedSELECTUsername as 'name', sex as 'Sex', Age, (--alias "name" to username, sex lists alias "gender"     Case --Case Indicates the beginning of judgment         whenSex=Male Then 1 --if the query out sex is male, then the value of the virtual column expression is 1         whenSex=Female Then 2    ELSE 3 --when none of the above is true, the column expression value is 3    END --end indicates the ending of Judgment) asSexno fromTB1;--to the virtual out of this column, from the name Sexno--DISTINCT The result of the query (eliminate duplicate columns)

MySQL Common operations (i)