MySQL Learning notes-database Common commands

Database Common Commands
1. Add Required path: path= "$PATH":/usr/local/mysql/bin
2. Login database system: Mysql-h localhost-u Root-p
3. Creating database: Create Database name
4. Display database: Show DATABASES;
5. Delete the database: drop database name;
6. Creating Table: Create table Table name (property name data type [integrity constraint], property name data type [integrity constraint], property name data type);
Note: You first use the USE statement to select the database before using the statement, and the table name cannot be the SQL keyword.

　　Example: CREATE TABLE Example (ID int,name VARCHAR (), sex BOOLEAN);

Table of constraint conditions

1. Single Field PRIMARY key: property name data type PRIMARY key

CREATE TABLE example2 (stu_id int PRIMARY key,grade float,course_id int);

2. Multi-field PRIMARY key: PRIMARY key (property name 1, property name 2, ...). attribute name N);

Example: CREATE TABLE example2 (stu_id int,grade float,course_id int,primary KEY (stu_id,course_id));

3. Set the table foreign key: The foreign key is a special field of the table. If the field Sno is a property of table A, it depends on the primary key of table B. Then, table B is the parent table, table A is a child table, and Sno is the foreign key of table A. The parent table B and child table A are associated by using the Sno field.

Setting the foreign key principle is that you must rely on the primary key of the parent table that already exists in the database;

The role of a foreign key is to establish an association between the table and its parent table. When a message is deleted from the parent table, the corresponding information in the child table must also be changed accordingly.　　　For example, STU_ID is the primary key for the student table, and stu_id is the foreign key in the grade table. When STU_ID dropped out of the ' 123 ' class, the student's information needs to be removed from the student table. Then all the information in the grade table that stu_id is ' 123 ' should also be removed to ensure the integrity of the information.

Set FOREIGN key syntax: CONSTRAINT alias FOREIGN KEY (Attribute 1.1, Property 1.2,.... Attribute 1.N) REFERENCES table name (attribute 2.1, Property 2.2,..., property 2.N);
Property 1 is the foreign key set in the child table, which refers to the name of the parent table; The property 2 argument list is the primary key of the parent table

Example: CREATE TABLE example3 (id INT PRIMARY key,stu_id int,course_id int,constraint c_fk FOREIGN KEY (stu_id,course_id) refere NCES example2 (stu_id,course_id));

4. Set Table Uniqueness constraint: The field value cannot recur in all records
Syntax: Property name data Type UNIQUE
Example: CREATE TABLE example5 (id int PRIMARY key,stu_id int unique,name VARCHAR () not NULL);

5. Set table Properties Auto-increment: Auto_increment is a special constraint in MySQL database. Used primarily to automatically generate a unique ID for new records that are inserted into a table. The auto_increment constraint field can be any integer (Tinyint,smallint,int and bigint, etc.) a table can have only one field set auto_increment constraint and the field must be part of the primary key.
Syntax: Property name data Type Auto_increment
Example: CREATE TABLE example6 (id int PRIMARY KEY auto_increment,stu_id int unique,name VARCHAR () not NULL);

6. Set default values for the properties of a table
Syntax: Property name data Type default defaults
Example: CREATE TABLE example7 (id INT PRIMARY KEY auto_increment,stu_id int unique,name varchar) not null,english varchar (20) Default ' zero ', Math float default 0,computer float default 0);

7. View Table Structure
Syntax: DESCRIBE table name; or DESC table name;
Example: DESCRIBE example1; or desc example1;
8. View Table Detail Structure statements
Syntax: SHOW CREATE table table name;
Example: SHOW CREATE TABLE Example \g

　　　　　　　　　　　　　　　　　　　　　　　Modify Table
modifying tables using the ALTER TABLE statement
1. Modify the table name
Syntax: ALTER table Old table name RENAME [to] new show;
Example: ALTER TABLE example RENAME user;

2. modifying field data types
Syntax: ALTER table table name MODIFY property name data structure;
Example: ALTER TABLE user MODIFY name VARCHAR (30);

3. Modify field names: Field names can be unique in the table to determine a field. The database system distinguishes between different fields in a table by field names.
Syntax: ALTER table table name change old property name new property name newly data type;
1. Modify field names only
Just write the new data type and the original data type
Example: ALTER TABLE user change name Stu_name VARCHAR (30);
2. Modifying field names and field data types
Example: ALTER TABLE user Change sex Stu_sex INT (2);

4. Add fields
Syntax: ALTER table name ADD property name 1 data type [integrity constraint] [first| ALTER property name 2];
1. Add a field with no integrity constraints
Example: ALTER TABLE user ADD phone VARCHAR (20);
2. Add a field with integrity constraints
Example: ALTER TABLE user ADD phone INT (4) not NULL;
3. Add a field to the first position of the table
Example: ALTER TABLE user ADD num INT (8) PRIMARY KEY first;
4. Add a field after the table specified position
Example: ALTER TABLE user ADD address VARCHAR (+) not NULL after phone;

5. Delete a field
Syntax: ALTER table name DROP attribute name;
Example: ALTER TABLE user DROP ID;

6. Modify the position of the field
Syntax: ALTER table name MODIFY property name 1 data type First | After property name 2;
"Property name 1" refers to the name of the field where the location needs to be modified; The data type parameter refers to the data type of the property name 1 "First" parameter refers to the position of the table is positioned at the top position; The After Property name 2 parameter specifies that property name 1 is inserted after property name 2.
1. Change the field to the first position
Example: ALTER TABLE user MODIFY stu_name VARCHAR (+) first;
2. Modify the field to the specified location
Example: ALTER TABLE user MODIFY stu_sex TINYINT (1) after phone;

7. Change the storage engine for a table
Syntax: ALTER table name engine = Storage Engine name;
Example: ALTER TABLE user ENGINE = MyISAM;

8. Delete a foreign KEY constraint for a table
Syntax: ALTER table name DROP FOREIGN key foreign key alias;
Example: ALTER TABLE example3 DROP FOREIGN KEY c_fk;

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Delete a table

1. Delete normal tables that are not associated
Syntax: DROP table name;
Example: DROP TABLE example5;

2. Delete the parent table that is associated with another table
1. Delete the child table before deleting the parent table
2. Delete dependencies before deleting the parent table to ensure the security of the database
Example: ALTER TABLE example3 DROP FOREIGN KEY c_fk;
DROP TABLE example;