MySQL has a lot of visual management tools, such as “mysql-workbench” and “sequel-pro-“. Now I’m writing MySQL terminal command operation is to strengthen the understanding of yourself for MySQL, always be better than using a graphical thorough understanding, because I already prefer to write code. Meanwhile write these articles, you want to be a reference, and hopefully be helpful to everyone, has improved, which is why I write articles of MySQL terminal operation.<\/p>\n
Note: MySQL database commands are not case sensitive. But in the MAC of the terminal, if you want to use the tab to automatically complete the command, then you must use uppercase, so MAC terminal will help you complete the command, otherwise you will not be according to N times the response tab.<\/p>\n
1, the database (database) management<\/p>\n
1.1 create the database is created<\/p>\n
1.2 show View all databases<\/p>\n
1.3 alter modify the database<\/p>\n
1.4 use to use the database<\/p>\n
1.5 View database currently in use<\/p>\n
1.6 drop delete database<\/p>\n
2, the data table (table) management<\/p>\n
2.1 create create table<\/p>\n
2.2 show display table<\/p>\n
2.3 desc view the table structure<\/p>\n
2.4 alter modify table structure (add, delete, change)<\/p>\n
2.4.1 insert in the table to add columns (fields)<\/p>\n
2.4.2 alter modify tables (column) field<\/p>\n
2.4.3 delete delete a table (column) field<\/p>\n
2.4.4 rename Rename table<\/p>\n
2.5 create use of existing data to create a new table<\/p>\n
3, operation and management data<\/p>\n
3.1 increases the data (by)<\/p>\n
3.2 Delete Data (deleted)<\/p>\n
3.3 modify the data (change)<\/p>\n
3.4 query data (check)<\/p>\n
1, the database (database) management<\/p>\n
1.1 create the database is created
\ncreate the database is created<\/p>\n
create database mohandb;
\n[root@cluster1 ~]# mysql -u root -p
\nEnter password:
\nWelcome to the MySQL monitor. Commands end with ; or \\g.
\nYour MySQL connection id is 2
\nServer version: 5.1.73 Source distribution<\/p>\n
Copyright (c) 2000, 2013, Oracle and\/or its affiliates. All rights reserved.<\/p>\n
Oracle is a registered trademark of Oracle Corporation and\/or its
\naffiliates. Other names may be trademarks of their respective
\nowners.<\/p>\n
Type ‘help;’ or ‘\\h’ for help. Type ‘\\c’ to clear the current input statement.<\/p>\n
mysql> create database mohandb;
\nQuery OK, 1 row affected (0.00 sec)
\n1.2 show View all databases<\/p>\n
mysql > show databases;<\/p>\n
mysql> show databases;
\n+——————–+
\n| Database |
\n+——————–+
\n| information_schema |
\n| mohandb |
\n| mysql |
\n| test |
\n+——————–+
\n4 rows in set (0.00 sec)<\/p>\n
mysql>
\n1.3 alter modify the database<\/p>\n
alter database command to change the code:
\nHow to convert an entire MySQL database characterset and collation to UTF-8<\/p>\n
se the ALTER DATABASE and ALTER TABLE commands.<\/p>\n
ALTER DATABASE mohandb CHARACTER SET utf8 COLLATE utf8_unicode_ci;
\nALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;<\/p>\n
mysql> ALTER DATABASE mohandb CHARACTER SET utf8 COLLATE utf8_unicode_ci;
\nQuery OK, 1 row affected (0.00 sec)<\/p>\n
mysql><\/p>\n
1.4 use to use the database
\nuse mohandb;
\nmysql> use mohandb;
\nDatabase changed
\nmysql>
\n1.5 View database currently in use
\nmysql> select database ();
\n+————-+
\n| database () |
\n+————-+
\n| mohandb |
\n+————-+
\n1 row in set (0.00 sec)<\/p>\n
mysql>
\nmysql>
\n1.6 drop delete database<\/p>\n
drop database mohandb;<\/p>\n
mysql> drop database mohandb;
\nQuery OK, 0 rows affected (0.01 sec)<\/p>\n
The data table (table) management<\/p>\n
We first create a database, provided that we use in the future:
\nmysql> create database testDB;
\nQuery OK, 1 row affected (0.00 sec)<\/p>\n
mysql> use testDB;
\nDatabase changed<\/p>\n
create create table<\/p>\n
mysql> create table PEOPLE (
\n-> ID int AUTO_INCREMENT PRIMARY KEY,
\n-> NAME varchar(20) not null,
\n-> AGE int not null,
\n-> BIRTHDAY datetime);
\nQuery OK, 0 rows affected (0.01 sec)<\/p>\n
Show Table 2.2 show<\/p>\n
Displays the current database of all the data tables
\nmysql> show tables;
\n+——————+
\n| Tables_in_testDB |
\n+——————+
\n| PEOPLE |
\n+——————+
\n1 row in set (0.00 sec)<\/p>\n
mysql><\/p>\n
desc view the table structure<\/p>\n
desc PEOPLE;<\/p>\n
mysql> desc PEOPLE;
\n+———-+————-+——+—–+———+—————-+
\n| Field | Type | Null | Key | Default | Extra |
\n+———-+————-+——+—–+———+—————-+
\n| ID | int(11) | NO | PRI | NULL | auto_increment |
\n| NAME | varchar(20) | NO | | NULL | |
\n| AGE | int(11) | NO | | NULL | |
\n| BIRTHDAY | datetime | YES | | NULL | |
\n+———-+————-+——+—–+———+—————-+
\n4 rows in set (0.00 sec)
\nalter modify table structure (add, delete, change)
\nALTER TABLE PEOPLE CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;<\/p>\n
mysql> ALTER TABLE PEOPLE CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
\nQuery OK, 0 rows affected (0.00 sec)
\nRecords: 0 Duplicates: 0 Warnings: 0<\/p>\n
insert in the table to add columns (fields)<\/p>\n
alter table PEOPLE add star BOOL;<\/p>\n
mysql> alter table PEOPLE add star BOOL;
\nQuery OK, 0 rows affected (0.00 sec)
\nRecords: 0 Duplicates: 0 Warnings: 0<\/p>\n
mysql> alter table PEOPLE add star BOOL;
\nQuery OK, 0 rows affected (0.00 sec)
\nRecords: 0 Duplicates: 0 Warnings: 0<\/p>\n
mysql> desc PEOPLE;
\n+———-+————-+——+—–+———+—————-+
\n| Field | Type | Null | Key | Default | Extra |
\n+———-+————-+——+—–+———+—————-+
\n| ID | int(11) | NO | PRI | NULL | auto_increment |
\n| NAME | varchar(20) | NO | | NULL | |
\n| AGE | int(11) | NO | | NULL | |
\n| BIRTHDAY | datetime | YES | | NULL | |
\n| star | tinyint(1) | YES | | NULL | |
\n+———-+————-+——+—–+———+—————-+
\n5 rows in set (0.00 sec)<\/p>\n
mysql>
\nalter modify tables (column) field<\/p>\n
mysql> alter table PEOPLE MODIFY star int ;
\nQuery OK, 0 rows affected (0.00 sec)
\nRecords: 0 Duplicates: 0 Warnings: 0<\/p>\n
mysql> desc PEOPLE;
\n+———-+————-+——+—–+———+—————-+
\n| Field | Type | Null | Key | Default | Extra |
\n+———-+————-+——+—–+———+—————-+
\n| ID | int(11) | NO | PRI | NULL | auto_increment |
\n| NAME | varchar(20) | NO | | NULL | |
\n| AGE | int(11) | NO | | NULL | |
\n| BIRTHDAY | datetime | YES | | NULL | |
\n| star | int(11) | YES | | NULL | |
\n+———-+————-+——+—–+———+—————-+
\n5 rows in set (0.00 sec)<\/p>\n
mysql><\/p>\n
delete delete a table (column) field
\nmysql> alter table PEOPLE DROP column star;
\nQuery OK, 0 rows affected (0.00 sec)
\nRecords: 0 Duplicates: 0 Warnings: 0<\/p>\n
mysql> desc PEOPLE;
\n+———-+————-+——+—–+———+—————-+
\n| Field | Type | Null | Key | Default | Extra |
\n+———-+————-+——+—–+———+—————-+
\n| ID | int(11) | NO | PRI | NULL | auto_increment |
\n| NAME | varchar(20) | NO | | NULL | |
\n| AGE | int(11) | NO | | NULL | |
\n| BIRTHDAY | datetime | YES | | NULL | |
\n+———-+————-+——+—–+———+—————-+
\n4 rows in set (0.00 sec)<\/p>\n
mysql>
\nrename Rename table<\/p>\n
mysql> RENAME TABLE PEOPLE TO NEW_PEOPLE;
\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n
2.5 create use of existing data to create a new table
\nmysql> create table newTable select * from NEW_PEOPLE;
\nQuery OK, 0 rows affected (0.00 sec)
\nRecords: 0 Duplicates: 0 Warnings: 0<\/p>\n
2.5 create use of existing data to create a new table<\/p>\n
mysql> create table newTable select * from NEW_PEOPLE;
\nQuery OK, 0 rows affected (0.00 sec)
\nRecords: 0 Duplicates: 0 Warnings: 0<\/p>\n
mysql> show tables
\n-> ;
\n+——————+
\n| Tables_in_testDB |
\n+——————+
\n| NEW_PEOPLE |
\n| newTable |
\n+——————+
\n2 rows in set (0.00 sec)<\/p>\n
operation and management data
\nThe basic operation of data tables, including add, delete, change, check data.<\/p>\n
The following commands are operating in the PEOPLE table.<\/p>\n
3.1 increases the data (by)<\/p>\n
PEOPLE table is currently no data, it is empty of data tables, we first add some data.<\/p>\n
insert into command to add data:
\nmysql> insert into NEW_PEOPLE VALUES (null, ‘mohan’, 22, ‘1993-03-23’);
\nQuery OK, 1 row affected (0.00 sec)
\nData show that there is one in the data.
\nWe add a few more data, such as:
\ninsert into NEW_PEOPLE VALUES (null, ‘mohan’, 22, ‘1993-03-23’);
\ninsert into NEW_PEOPLE VALUES (null, ‘raj’, 22, ‘1991-03-23’);
\ninsert into NEW_PEOPLE VALUES (null, ‘ravi’, 22, ‘1992-03-23’);
\ninsert into NEW_PEOPLE VALUES (null, ‘jack’, 22, ‘1913-03-23’);<\/p>\n
mysql> insert into NEW_PEOPLE VALUES (null, ‘mohan’, 22, ‘1993-03-23’);
\nQuery OK, 1 row affected (0.00 sec)<\/p>\n
mysql> insert into NEW_PEOPLE VALUES (null, ‘raj’, 22, ‘1991-03-23’);
\nQuery OK, 1 row affected (0.00 sec)<\/p>\n
mysql> insert into NEW_PEOPLE VALUES (null, ‘ravi’, 22, ‘1992-03-23’);
\nQuery OK, 1 row affected (0.00 sec)<\/p>\n
mysql> insert into NEW_PEOPLE VALUES (null, ‘jack’, 22, ‘1913-03-23′);
\nQuery OK, 1 row affected (0.00 sec)<\/p>\n
mysql > select * from NEW_PEOPLE;<\/p>\n
mysql> select * from NEW_PEOPLE;
\n+—-+——-+—–+———————+
\n| ID | NAME | AGE | BIRTHDAY |
\n+—-+——-+—–+———————+
\n| 1 | Anny | 22 | 1992-05-22 00:00:00 |
\n| 2 | mohan | 22 | 1993-03-23 00:00:00 |
\n| 3 | raj | 22 | 1991-03-23 00:00:00 |
\n| 4 | ravi | 22 | 1992-03-23 00:00:00 |
\n| 5 | jack | 22 | 1913-03-23 00:00:00 |
\n+—-+——-+—–+———————+
\n5 rows in set (0.00 sec)
\nmodify the data (change)<\/p>\n
update command to modify the data:
\nupdate NEW_PEOPLE set name=’Anny’ where name = ‘rcs’;
\nmysql> update NEW_PEOPLE set name=’Anny’ where name = ‘rcs’;
\nQuery OK, 0 rows affected (0.00 sec)
\nRows matched: 0 Changed: 0 Warnings: 0
\nselect * from NEW_PEOPLE;<\/p>\n
mysql> select * from NEW_PEOPLE;
\n+—-+——-+—–+———————+
\n| ID | NAME | AGE | BIRTHDAY |
\n+—-+——-+—–+———————+
\n| 1 | Anny | 22 | 1992-05-22 00:00:00 |
\n| 2 | mohan | 22 | 1993-03-23 00:00:00 |
\n| 3 | raj | 22 | 1991-03-23 00:00:00 |
\n| 4 | ravi | 22 | 1992-03-23 00:00:00 |
\n| 5 | jack | 22 | 1913-03-23 00:00:00 |
\n+—-+——-+—–+———————+
\n5 rows in set (0.00 sec)
\nselect NAME, AGE, BIRTHDAY from NEW_PEOPLE;
\nmysql> select NAME, AGE, BIRTHDAY from NEW_PEOPLE;
\n+——-+—–+———————+
\n| NAME | AGE | BIRTHDAY |
\n+——-+—–+———————+
\n| Anny | 22 | 1992-05-22 00:00:00 |
\n| mohan | 22 | 1993-03-23 00:00:00 |
\n| raj | 22 | 1991-03-23 00:00:00 |
\n| ravi | 22 | 1992-03-23 00:00:00 |
\n| jack | 22 | 1913-03-23 00:00:00 |
\n+——-+—–+———————+
\n5 rows in set (0.00 sec)<\/p>\n","protected":false},"excerpt":{"rendered":"
MySQL has a lot of visual management tools, such as “mysql-workbench” and “sequel-pro-“. Now I’m writing MySQL terminal command operation is to strengthen the understanding of yourself for MySQL, always be better than using a graphical thorough understanding, because I already prefer to write code. Meanwhile write these articles, you want to be a reference, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/4503"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4503"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/4503\/revisions"}],"predecessor-version":[{"id":4504,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/4503\/revisions\/4504"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4503"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4503"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4503"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}