I am mainly in the maintenance of the OpenStack cloud platform will involve the operation of MySQL database, here to share with you common simple commands, but also to do a little exercise for themselves.
1. Log in to the MySQL database
123456
Where,-h:the IP address or hostname of the MySQL server, if it is a native login, this option can be omitted;
- u: Login user Name
- p: login password for logged-in user
2. View the database
mysql> show databases; +--------------------+| Database |+--------------------+| information_schema (mysql) | | Test |+--------------------+3 rows in Set (0.00 sec)
3. Deleting a database
Command format:DROP database name;
mysql> drop database test; Query OK, 0 rows Affected (0.00 sec) mysql> Show databases;+--------------------+| Database |+--------------------+| information_schema | | mysql |+--------------------+2 rows in Set (0.00 sec )
4. Create a Database
Command format:CREATE database new name;
Mysql> Show databases;+--------------------+| Database |+--------------------+| information_schema | | mysql |+--------------------+2 rows in Set (0.00 sec) mysql> CREATE database test; Query OK, 1 row Affected (0.00 sec) mysql> Show databases;+--------------------+| Database |+--------------------+| information_schema (mysql) | | Test |+--------------------+3 rows in Set (0.00 sec)
5. Select the Operational database
Command format: Usedatabase name;
Use test; Database changed
6. Create a form in the selected database
Table creation is not a simple operation, involving entity integrity, referential integrity and other conditions, the following general commands are described below.
1) CREATE table
Command format:CREATE table table name
(
, column name 1 data type (integrity constraints),
, Column name 2 data type (integrity constraints),
Column Name 3 data type (integrity constraints)
);
CREATE TABLE students (ID int (ten), name char (), sex char ()); CREATE TABLE course (ID int (ten), name Char); Query OK, 0 rows affected (0.07 sec)
In the above example, the integrity constraint is data length, which is a relatively simple one.
2) Description of completeness constraint conditions
PRIMARY KEY |
Identifies the property as the primary key for the table and uniquely identifies a set of records |
FOREIGN KEY |
Identifies the property as a foreign key for the table, which is the primary key of a table that is associated with the table |
Not NULL |
The value that identifies the property cannot be empty |
UNIQUE |
The value that identifies the property is unique |
Auto_increment |
The value that identifies the property is automatically incremented |
DEFAULT |
Set a default value for this property |
3) Set the primary key of the table
Single field primary key format: column name data type PRIMARY key, or as multi-field primary key format
CREATE TABLE course (ID int (TEN) PRIMARY key,name char); Query OK, 0 rows affected (0.08 sec)
Or
Mysql>CREATE TABLE course (ID int (ten), name Char, PRIMARY KEY (id));
Query OK, 0 rows affected (0.08 sec)
Multi-field Primary key format:PRIMARY key (column name 1, column name 2, column name 3 ...)
mysql> CREATE table num
(
-num_id int auto_increment not NULL,
-stu_id Int (ten) is not NULL,
, name char (a) is not NULL,
PRIMARY KEY (num_id,stu_id)
)
;
Query OK, 0 rows affected (0.03 sec)
Delete primary key for existing table: ALTER TABLEname drop PRIMARY key
mysql> ALTER TABLE score drop PRIMARY KEY; 0 rows affected (0.040 0 0
Set or reset the primary key for an existing table:ALTER TABLE name add PRIMARY key (property 1, Property 2 ...);
mysql> ALTER TABLE score add PRIMARY KEY (stu_id,cour_id); 0 rows affected (0.060 0 0
4) Set the foreign key of table
Set FOREIGN KEY command format:CONSTRAINT foreign key alias FOREIGN key (attribute 1, property 2,.... attribute N) REFERENCES table name (attribute 1 ', property 2 ',... Attribute n ')
Here is just a simple foreign key Setup command, the next one will detail the foreign key constraint settings
mysql> CREATE TABLE teacher ( ID int PRIMARY KEY, stu_id int, name varchar), - > CONSTRAINT stuid FOREIGN KEY (stu_id) REFERENCES student1 (ID) ; 0 rows affected (0.00 sec)
Delete foreign Key command format:ALTER table name DROP foreingn key foreign key name;
mysql>0 rows affected (0.040 0 0
5) Set non-null constraints on the table
The simple thing is to not let the value of this property be empty, if you do not fill out the error will be reported
Format: Property name data type not NULL
6) Set the uniqueness constraint for the table
Is that the value of this property cannot be duplicated.
Format: Property name data Type UNIQUE
7) Set the property value of the table to automatically increase
The Auto_increment constrained field can be any integer type (TINYINT, SMALLINT, int, and bigint), and by default the value of the field is increased from 1
Format: Property name data Type Auto_increment
8) Set default values for the properties of the table
Format: Property name data Type default defaults
mysql> CREATE TABLE student3 ( int PRIMARY KEY auto_increment, int UNIQUE, name varchar (a) not NULL, sex varchar male ' 0 rows affected (0.01 sec)
7. View all forms in a specified database
show tables; +----------------+| Tables_in_test |+----------------+| Course | | Students |+----------------+2 rows in Set (0.00 sec)
8. Delete the specified form in the specified database
You must select the database with the use command before executing the command, or there will be an error "No database selected" appears.
Command format: DROP table Form name;
drop table course; Query OK, 0 rows Affected (0.00 sec) mysql> Show tables;+----------------+| Tables_in_test |+----------------+| Students |+----------------+1 row in Set (0.00 sec)
9. View table Structure
1) View the basic structure of the table
Command format:DESCRIBE table name;
mysql> DESCRIBE num; +--------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+----------+------+-----+---------+----------------+| num_ID | int | NO | PRI | NULL | auto_increment | | stu_id | INT (TEN) | NO | PRI | NULL | | | name | char (20) | NO | | NULL | | +--------+----------+------+-----+---------+----------------+3 rows in Set (0.00 sec)
2) View the detailed structure of the table
Command format:SHOW CREATE table name
Mysql>Show create table num;+-------+------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------+| Table | Create Table |+-------+--------------------------------------- --------------------------------------------------------------------------------------------------------------- ----------------------------------------------+|Num | CREATE TABLE ' num ' (' num_id ' int (one) not null auto_increment, ' stu_id ' int (ten) not null, ' name ' char (a) not NULL, PR Imary KEY (' num_id ', ' stu_id ') Engine=myisam DEFAULT charset=latin1 |+-------+-------------------------------------- --------------------------------------------------------------------------------------------------------------- -----------------------------------------------+1 Row in Set (0.00 sec)
10. Modifying the basic properties of a table
1) Modify table name: The table name can uniquely identify one table in the database
Command format:ALTER TABLE old name RENAME new name;
Mysql> show tables; +----------------+| Tables_in_test |+----------------+| course | | num | | teacher |+----------------+3 rows in Set (0.00 sec ) mysql> ALTER TABLE course rename courses; Query OK, 0 rows Affected (0.00 sec) mysql> Show tables;+----------------+| Tables_in_test |+----------------+| Courses | | num | | teacher |+----------------+3 rows in Set (0.00 sec)
2) Modify the data type of the attribute
Command format:ALTER table name MODIFY property name data type
Mysql>describe num;+--------+---------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+---------+------+-----+---------+----------------+| num_id | Int (11) | NO | PRI | NULL | auto_increment | | stu_id | Int (10) | NO | PRI | NULL | || name | Int (20) | YES | | NULL | |+--------+---------+------+-----+---------+----------------+3 rows in Set (0.00 sec) mysql>ALTER TABLE NUM Modify name char (a);Query OK, 0 rows affected (0.05 sec) records:0 duplicates:0 warnings:0mysql> describe num;+--------+----------+---- --+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+----------+------+-----+---------+----------------+| num_id | Int (11) | NO | PRI | NULL | auto_increment | | stu_id | Int (10) | NO | PRI | NULL | || name | char (20) | YES | | NULL | |+--------+----------+------+-----+---------+----------------+3 rows in Set (0.00 sec)
3) Modify the field name
Command format:ALTER table Name change property name new property name new data type
Mysql> describe num;+--------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+----------+------+-----+---------+----------------+| num_id | Int (11) | NO | PRI | NULL | auto_increment | | stu_id | Int (10) | NO | PRI | NULL | || name | char (20) | YES | | NULL | |+--------+----------+------+-----+---------+----------------+3 rows in Set (0.00 sec) mysql>ALTER TABLE num change name sex char (ten);Query OK, 0 rows affected (0.04 sec) records:0 duplicates:0 warnings:0mysql> describe num;+--------+----------+---- --+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+----------+------+-----+---------+----------------+| num_id | Int (11) | NO | PRI | NULL | auto_increment | | stu_id | Int (10) | NO | PRI | NULL | || sex | char (10) | YES | | NULL | |+--------+----------+------+-----+---------+----------------+3 rows in Set (0.00 sec)
4) Add Field
Command format:ALTER TABLE name ADD property name 1 [integrity constraint] [first | After property name 2]
Where the first parameter indicates that the newly added property is set to the field in the table, and after property name 2 means that the newly added field is placed after the property name 2 (already exists).
Mysql> describe num;+--------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+----------+------+-----+---------+----------------+| num_id | Int (11) | NO | PRI | NULL | auto_increment | | stu_id | Int (10) | NO | PRI | NULL | || sex | char (10) | YES | | NULL | |+--------+----------+------+-----+---------+----------------+3 rows in Set (0.00 sec) mysql>ALTER TABLE num add name char (a) not NULL first;Query OK, 0 rows affected (0.05 sec) records:0 duplicates:0 warnings:0mysql> describe num;+--------+----------+---- --+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+----------+------+-----+---------+----------------+| name | char (20) | NO | | NULL | || num_id | Int (11) | NO | PRI | NULL | auto_increment | | stu_id | Int (10) | NO | PRI | NULL | || sex | char (10) | YES | | NULL | |+--------+----------+------+-----+---------+----------------+4 rows in Set (0.00 sec)
6) Delete Field
Command format: ALTER table name DROP attribute name;
Mysql> describe num;+--------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+----------+------+-----+---------+----------------+| name | char (20) | NO | | NULL | || num_id | Int (11) | NO | PRI | NULL | auto_increment | | stu_id | Int (10) | NO | PRI | NULL | || sex | char (10) | YES | | NULL | |+--------+----------+------+-----+---------+----------------+4 rows in Set (0.00 sec) mysql>ALTER TABLE num DROP name;Query OK, 0 rows affected (0.09 sec) records:0 duplicates:0 warnings:0mysql> describe num;+--------+----------+---- --+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+----------+------+-----+---------+----------------+| num_id | Int (11) | NO | PRI | NULL | auto_increment | | stu_id | Int (10) | NO | PRI | NULL | || sex | char (10) | YES | | NULL | |+--------+----------+------+-----+---------+----------------+3 rows in Set (0.00 sec)
MySQL common commands (i)