MySQL common commands (i)

Source: Internet
Author: User
Tags one table

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.