Basic knowledge of mysql _ MySQL

The basic knowledge of mysql blog here is purely for extracting some common mysql operations. if you understand it, 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;

When you see this, you can add some text knowledge points that are not taken seriously:

1) mysql is case insensitive;

2) for command line operations, you need to understand the meanings of the following indicators:

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;

3. select the database use database;

4. delete the database drop database;

Mysql data table operations

1. create table

Create [temporary] table [if not exist] database table name [(create_definition,…)] [Table_options] [select_statement]

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