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.
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.
1, the database (database) management
1.1 create the database is created
1.2 show View all databases
1.3 alter modify the database
1.4 use to use the database
1.5 View database currently in use
1.6 drop delete database
2, the data table (table) management
2.1 create create table
2.2 show display table
2.3 desc view the table structure
2.4 alter modify table structure (add, delete, change)
2.4.1 insert in the table to add columns (fields)
2.4.2 alter modify tables (column) field
2.4.3 delete delete a table (column) field
2.4.4 rename Rename table
2.5 create use of existing data to create a new table
3, operation and management data
3.1 increases the data (by)
3.2 Delete Data (deleted)
3.3 modify the data (change)
3.4 query data (check)
1, the database (database) management
1.1 create the database is created
create the database is created
create database mohandb;
[root@cluster1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> create database mohandb;
Query OK, 1 row affected (0.00 sec)
1.2 show View all databases
mysql > show databases;
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mohandb |
| mysql |
| test |
+——————–+
4 rows in set (0.00 sec)
mysql>
1.3 alter modify the database
alter database command to change the code:
How to convert an entire MySQL database characterset and collation to UTF-8
se the ALTER DATABASE and ALTER TABLE commands.
ALTER DATABASE mohandb CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
mysql> ALTER DATABASE mohandb CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0.00 sec)
mysql>
1.4 use to use the database
use mohandb;
mysql> use mohandb;
Database changed
mysql>
1.5 View database currently in use
mysql> select database ();
+————-+
| database () |
+————-+
| mohandb |
+————-+
1 row in set (0.00 sec)
mysql>
mysql>
1.6 drop delete database
drop database mohandb;
mysql> drop database mohandb;
Query OK, 0 rows affected (0.01 sec)
The data table (table) management
We first create a database, provided that we use in the future:
mysql> create database testDB;
Query OK, 1 row affected (0.00 sec)
mysql> use testDB;
Database changed
create create table
mysql> create table PEOPLE (
-> ID int AUTO_INCREMENT PRIMARY KEY,
-> NAME varchar(20) not null,
-> AGE int not null,
-> BIRTHDAY datetime);
Query OK, 0 rows affected (0.01 sec)
Show Table 2.2 show
Displays the current database of all the data tables
mysql> show tables;
+——————+
| Tables_in_testDB |
+——————+
| PEOPLE |
+——————+
1 row in set (0.00 sec)
mysql>
desc view the table structure
desc PEOPLE;
mysql> desc PEOPLE;
+———-+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———-+————-+——+—–+———+—————-+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| NAME | varchar(20) | NO | | NULL | |
| AGE | int(11) | NO | | NULL | |
| BIRTHDAY | datetime | YES | | NULL | |
+———-+————-+——+—–+———+—————-+
4 rows in set (0.00 sec)
alter modify table structure (add, delete, change)
ALTER TABLE PEOPLE CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
mysql> ALTER TABLE PEOPLE CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
insert in the table to add columns (fields)
alter table PEOPLE add star BOOL;
mysql> alter table PEOPLE add star BOOL;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table PEOPLE add star BOOL;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc PEOPLE;
+———-+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———-+————-+——+—–+———+—————-+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| NAME | varchar(20) | NO | | NULL | |
| AGE | int(11) | NO | | NULL | |
| BIRTHDAY | datetime | YES | | NULL | |
| star | tinyint(1) | YES | | NULL | |
+———-+————-+——+—–+———+—————-+
5 rows in set (0.00 sec)
mysql>
alter modify tables (column) field
mysql> alter table PEOPLE MODIFY star int ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc PEOPLE;
+———-+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———-+————-+——+—–+———+—————-+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| NAME | varchar(20) | NO | | NULL | |
| AGE | int(11) | NO | | NULL | |
| BIRTHDAY | datetime | YES | | NULL | |
| star | int(11) | YES | | NULL | |
+———-+————-+——+—–+———+—————-+
5 rows in set (0.00 sec)
mysql>
delete delete a table (column) field
mysql> alter table PEOPLE DROP column star;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc PEOPLE;
+———-+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———-+————-+——+—–+———+—————-+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| NAME | varchar(20) | NO | | NULL | |
| AGE | int(11) | NO | | NULL | |
| BIRTHDAY | datetime | YES | | NULL | |
+———-+————-+——+—–+———+—————-+
4 rows in set (0.00 sec)
mysql>
rename Rename table
mysql> RENAME TABLE PEOPLE TO NEW_PEOPLE;
Query OK, 0 rows affected (0.00 sec)
2.5 create use of existing data to create a new table
mysql> create table newTable select * from NEW_PEOPLE;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
2.5 create use of existing data to create a new table
mysql> create table newTable select * from NEW_PEOPLE;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show tables
-> ;
+——————+
| Tables_in_testDB |
+——————+
| NEW_PEOPLE |
| newTable |
+——————+
2 rows in set (0.00 sec)
operation and management data
The basic operation of data tables, including add, delete, change, check data.
The following commands are operating in the PEOPLE table.
3.1 increases the data (by)
PEOPLE table is currently no data, it is empty of data tables, we first add some data.
insert into command to add data:
mysql> insert into NEW_PEOPLE VALUES (null, ‘mohan’, 22, ‘1993-03-23’);
Query OK, 1 row affected (0.00 sec)
Data show that there is one in the data.
We add a few more data, such as:
insert into NEW_PEOPLE VALUES (null, ‘mohan’, 22, ‘1993-03-23’);
insert into NEW_PEOPLE VALUES (null, ‘raj’, 22, ‘1991-03-23’);
insert into NEW_PEOPLE VALUES (null, ‘ravi’, 22, ‘1992-03-23’);
insert into NEW_PEOPLE VALUES (null, ‘jack’, 22, ‘1913-03-23’);
mysql> insert into NEW_PEOPLE VALUES (null, ‘mohan’, 22, ‘1993-03-23’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into NEW_PEOPLE VALUES (null, ‘raj’, 22, ‘1991-03-23’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into NEW_PEOPLE VALUES (null, ‘ravi’, 22, ‘1992-03-23’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into NEW_PEOPLE VALUES (null, ‘jack’, 22, ‘1913-03-23′);
Query OK, 1 row affected (0.00 sec)
mysql > select * from NEW_PEOPLE;
mysql> select * from NEW_PEOPLE;
+—-+——-+—–+———————+
| ID | NAME | AGE | BIRTHDAY |
+—-+——-+—–+———————+
| 1 | Anny | 22 | 1992-05-22 00:00:00 |
| 2 | mohan | 22 | 1993-03-23 00:00:00 |
| 3 | raj | 22 | 1991-03-23 00:00:00 |
| 4 | ravi | 22 | 1992-03-23 00:00:00 |
| 5 | jack | 22 | 1913-03-23 00:00:00 |
+—-+——-+—–+———————+
5 rows in set (0.00 sec)
modify the data (change)
update command to modify the data:
update NEW_PEOPLE set name=’Anny’ where name = ‘rcs’;
mysql> update NEW_PEOPLE set name=’Anny’ where name = ‘rcs’;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
select * from NEW_PEOPLE;
mysql> select * from NEW_PEOPLE;
+—-+——-+—–+———————+
| ID | NAME | AGE | BIRTHDAY |
+—-+——-+—–+———————+
| 1 | Anny | 22 | 1992-05-22 00:00:00 |
| 2 | mohan | 22 | 1993-03-23 00:00:00 |
| 3 | raj | 22 | 1991-03-23 00:00:00 |
| 4 | ravi | 22 | 1992-03-23 00:00:00 |
| 5 | jack | 22 | 1913-03-23 00:00:00 |
+—-+——-+—–+———————+
5 rows in set (0.00 sec)
select NAME, AGE, BIRTHDAY from NEW_PEOPLE;
mysql> select NAME, AGE, BIRTHDAY from NEW_PEOPLE;
+——-+—–+———————+
| NAME | AGE | BIRTHDAY |
+——-+—–+———————+
| Anny | 22 | 1992-05-22 00:00:00 |
| mohan | 22 | 1993-03-23 00:00:00 |
| raj | 22 | 1991-03-23 00:00:00 |
| ravi | 22 | 1992-03-23 00:00:00 |
| jack | 22 | 1913-03-23 00:00:00 |
+——-+—–+———————+
5 rows in set (0.00 sec)
Recent Comments