MySQL: Basic commands (slowly accumulating)

I. Operational database

1. Log in to the database

$ mysql-u Root-p

2. View all current databases

SHOW DATABASES;

3. Create a Database

CREATE DATABASE test;

3. Select a database

Use test;

4. Deleting a database

DROP DATABASE test;

Two. Operation table

1. Create a table

Creating tables requires table field Name field details (mainly including: whether the type can be null primary or foreign) and so on.

Mysql> CREATE TABLEinfo ( -info_idINT  not NULLAuto_increment, -Info_nameVARCHAR( -) not NULL,     -Info_ageINT,     -Info_birthday DATE, - PRIMARY KEY(info_id) -);

Auto_increment defines a property that is self-increasing, typically used for a primary key, and the value is automatically added to 1.

The PRIMARY key keyword is used to define the column as the primary key. You can use multiple columns to define a primary key, and the columns are separated by commas.

2. View table structure

Mysql> DESCinfo;+---------------+-------------+------+-----+---------+----------------+|Field|Type| Null | Key | Default |Extra|+---------------+-------------+------+-----+---------+----------------+|info_id| int( One)|NO|Pri| NULL    |Auto_increment||Info_name| varchar( -)|NO|     | NULL    |                ||Info_age| int( One)|YES|     | NULL    |                ||Info_birthday|Date|YES|     | NULL    |                |+---------------+-------------+------+-----+---------+----------------+4Rowsinch Set(0.01Sec

or SHOW COLUMNS from info; Same effect

3. Delete a table

　　　　DROP TABLE info;

4. View all tables under the current database

SHOW TABLES;

5. Modify the table (not the contents of the table)

(1) Modify table name

ALTER TABLE info RENAME information;

(2) Modify the field names in the table

ALTER TABLE information change info_id ID SMALLINT;

ALTER Table name change old property name new data type with new property name;

(3) Modifying the data type of a field

ALTER TABLE information MODIFY info_id TINYINT;

(4) Add field

ALTER Table Table Name property name 1 data type [integrity constraint] [first | After property name 2];

The "Property name 1" parameter refers to the field name that needs to be added

The data type parameter refers to the data type of the new field

An "integrity constraint" is an optional parameter that sets the integrity constraints for a new field.

MySQL> ALTER TABLEInformationADDInfo_addrVARCHAR( -) not NULLAfter Info_age; Query OK,0Rows Affected (0.23sec) Records:0Duplicates:0Warnings:0MySQL> DESCinformation;
+---------------+-------------+------+-----+---------+-------+|Field|Type| Null | Key | Default |Extra|+---------------+-------------+------+-----+---------+-------+|Id| smallint(6)|NO|Pri| 0       |       ||Info_name| varchar( -)|NO|     | NULL    |       ||Info_age| int( One)|YES|     | NULL    |       ||Info_addr| varchar( -)|NO|     | NULL    |       ||Info_birthday|Date|YES|     | NULL    |       |+---------------+-------------+------+-----+---------+-------+5Rowsinch Set(0.00Sec

You can also add multiple fields at once:

MySQL: Basic commands (slowly accumulating)