Here, the blog simply aims to extract some common mysql operations. If you already understand this, you can ignore it. Thank you.
Basic mysql operations
1. query mysql version and current time information
Select version (), current_date, current_time, current_timestamp;
Indicator Description mysql>
Execute a new command ->
In a multi-line command, wait for the input in the next line. '>
Wait for the input in the next row, and it ends ">
Wait for the input in the next row and it ends" `>
Wait for the input in the next row, and it ends /*>
Wait for the input of the next line, and it ends with/* (comment)
2. view the database show databases;
Parameters Description Temporary Creates a temporary table. If not exist Used to avoid mysql errors when a table exists Create_definition This is the column attribute part of the table (including at least one column) Table_options Table feature parameters Select_statement Description of the select statement, which can be used to quickly create a table
Parameters of the create_definition attribute are described as follows:
Parameters |
Description |
Col_name |
Field name |
Type |
Field Type |
Not null | null |
Field Type |
DEFAULT defaule_value |
Default Value |
Auto_increment |
Auto increment. Each table can have only one autoincrement |
Primary key |
Primary key. A table can have only one primary key. If the table does not have a primary key, and some applications require a primary key, mysql uses the first unique key without a null column as the primary key. |
Although there are many parameters for creating a data table, you can create the most basic data table if there is no special requirement in actual application. How can this problem be solved:
Create table data table name (column name 1 attribute, column name 2 attribute ···);
2. view the table structure shuo colummns or describe
3. alter table
Use alter table to modify the table structure. Modifying a table structure refers to adding or deleting a field, modifying the field name or field type, setting to cancel the foreign key of the primary key, setting to cancel the index, and modifying the comment of the table. Syntax:
Alter [ignore] table data table name alter_spec [, alter_apec] ·
Appendix: When ignore is specified, if a key row is repeated, a row is executed and other duplicate rows are deleted.
The alter_spec statement defines the content to be modified. The method is as follows:
Add [column] create_definition [first | after column_name] // add a new field
Add index [index_name] (index_col_name,...) // add an index name
Add primary key (index_col_name, ·) // add a primary key
Add unique [index_name] (index_col_name,...) // add a unique index
Alter [column] col_name {set default literal | drop default} // modify the default value of a field
Change [column] old_col_name create_definition // modify the field type
Modify column create_definition // modify the clause definition Field
Drop [column] col_name // delete a field
Drop primary key // delete a primary key
Drop index index_name // delete an index
Rename [as] new_table_name // change the table name
4. rename a table
Rename table old_table_name to new_table_name
5. drop table
Drop table table_name