June 2020
M T W T F S S
« Mar    
1234567
891011121314
15161718192021
22232425262728
2930  

Categories

WordPress Quotes

A person who has a cat by the tail knows a whole lot more about cats than someone who has just read about them.
Mark Twain
June 2020
M T W T F S S
« Mar    
1234567
891011121314
15161718192021
22232425262728
2930  

Short Cuts

2012 SERVER (64)
2016 windows (9)
AIX (13)
Amazon (40)
Ansibile (19)
Apache (135)
Asterisk (2)
cassandra (2)
Centos (211)
Centos RHEL 7 (270)
centos8 (3)
chef (3)
cloud (2)
cluster (3)
Coherence (1)
DB2 (5)
DISK (25)
DNS (9)
Docker (30)
Eassy (11)
ELKS (1)
EXCHANGE (3)
Fedora (6)
ftp (5)
GIT (3)
GOD (2)
Grub (1)
Hacking (10)
Hadoop (6)
health (2)
horoscope (23)
Hyper-V (10)
IIS (15)
IPTABLES (15)
JAVA (7)
JBOSS (32)
jenkins (1)
Kubernetes (7)
Ldap (5)
Linux (188)
Linux Commands (166)
Load balancer (5)
mariadb (14)
Mongodb (4)
MQ Server (24)
MYSQL (84)
Nagios (5)
NaturalOil (13)
Nginx (35)
Ngix (1)
openldap (1)
Openstack (6)
Oracle (35)
Perl (3)
Postfix (19)
Postgresql (1)
PowerShell (2)
Python (3)
qmail (36)
Redis (12)
RHCE (28)
SCALEIO (1)
Security on Centos (29)
SFTP (1)
Shell (64)
Solaris (58)
Sql Server 2012 (4)
squid (3)
SSH (10)
SSL (14)
Storage (1)
swap (3)
TIPS on Linux (28)
tomcat (62)
Ubuntu (1)
Uncategorized (30)
Veritas (2)
vfabric (1)
VMware (28)
Weblogic (38)
Websphere (71)
Windows (19)
Windows Software (2)
wordpress (1)
ZIMBRA (17)

WP Cumulus Flash tag cloud by Roy Tanck requires Flash Player 9 or better.

Who's Online

0 visitors online now
0 guests, 0 bots, 0 members

Hit Counter provided by dental implants orange county

MySQL Terminal Commands

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)

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

  

  

  

Blue Captcha Image
Refresh

*

Protected by WP Anti Spam