MySQL operation on a table

MySQL operation on a table

1. The concept of a table
   Represents a database object that contains all the data in the database. The database objects in the table contain columns, indexes, triggers. Where triggers are user-defined sets of transaction commands that are automatically executed when data in a table is inserted, updated, or deleted, ensuring the security and integrity of the data.
2. Create a table
   CREATE TABLE TableName (
   Attribute Name field type,
   Attribute Name field type,
   ......
   Attribute Name field type
   );
   There is no comma after the last field type, and the entire statement has a semicolon behind the parentheses.

For example: Create a database and create a table:
Create database name;
show databases;//view all databases in MySQL under the current user
Use database name;//using this database
CREATE TABLE Class (
ID Integer primary KEY auto_increment,
Name Varvhar (32),
Number int
);//Create a three-column table with a table named class. ID is integer, primary key and self-growing.

3. View the table
Describe table_name; To view a table with the table named table_name

If you need to know more about the table structure:
Show CREATE TABLE table_name; View trusted information for table structure

You can see that this statement can see the detailed statement of the construction, at work, you can use this statement to get a table statement, the build table statements in other databases can also be created in other databases in the same table.
4. Delete a table
DROP TABLE table_name; Delete a table
The table you want to delete must be a table that already exists in the database, and this SQL statement will delete the structure information of the table and the data in the table, so delete the table with care. After deletion, you can use describe table_name to confirm whether or not to delete.
TRUNCATE TABLE table_name; Clears all data in a table
5. Modify the table
1> Modifying table names
ALTER TABLE Old_table_name Rename [to] new_table_name;
The table name is unique in the database, and the table name can be changed with the top statement.
2> Add Field
ALTER TABLE table_name The Add property Name property type;//Add field at the last position of the table.
ALTER TABLE table_name Add property Name property type first; Add a field to the first position in a table
ALTER TABLE table_name Add property Name property type after an existing property name;//Add a field behind the attribute that the keyword refers to

3> Delete a field
ALTER TABLE table_name drop property name;

4> modifying fields
To modify the data type of a field:
ALTER TABLE table_name Modify property name data type;
Modify the name of a field
ALTER TABLE TABLE_NAME change old property name new property name old data type;
Modify the name and data type of a field at the same time
ALTER TABLE TABLE_NAME change old property name new data type with new property name;

To modify the order of fields:
ALTER TABLE table_name Modify property name 1 Data type First/after property name 2;//property name 1 represents the field to be modified, "first" represents placing attribute 1 at the top of the table, "after property name 2" represents the adjustment of attribute 1 to the property 2 behind.

1. Constraints on action tables
   MySQL-Supported integrity constraints:
   Completeness refers to the accuracy and consistency of the data.
   

   其中主键 primary key可以设置但字段主键也可以设置多字段主键。单字段主键直接在字段数据类型后加 primary key就可以了，比如class表的id字段。多字段主键：

   When a primary key is composed of multiple fields, it is implemented with SQL constraint:
   CREATE TABLE table_name (
   Property name Data type,
   ......
   [constraint constraint name] PRIMARY KEY (property name, property name ...)
   );

Bibliography: MySQL database application from getting started to mastering the second edition

MySQL action on a table