MySQL Database basic command Rollup 2

14 Insert the Age field type before the field name in the test table tinyint (2)

Mysql> CREATE TABLE test (ID int (4) not NULL, name varchar (+) not NULL);

Query OK, 0 rows affected (0.36 sec)

mysql> desc test;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| ID | Int (4) |     NO | |       NULL | |

| name | varchar (16) |     NO | |       NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in Set (0.10 sec)

mysql> ALTER TABLE test add age tinyint (2) after ID;

Query OK, 0 rows affected (0.47 sec)

records:0 duplicates:0 warnings:0

mysql> desc test;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| ID | Int (4) |     NO | |       NULL | |

| Age | tinyint (2) |     YES | |       NULL | |

| name | varchar (16) |     NO | |       NULL | |

+-------+-------------+------+-----+---------+-------+

3 Rows in Set (0.00 sec)

Mysql>

do not exit database backup Oldboy database

Mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| BBS |

| MySQL |

| Oldboe |

| Oldbou |

| Oldboy |

| Performance_schema |

| Test |

| WordPress |

| www |

| Xinjia |

| Xu |

+--------------------+

Rows in Set (0.00 sec)

mysql> system Mysqldump-uroot-poldboy123-b Oldboy >/opt/oldboy.sql;

Warning:using a password on the command line interface can is insecure.

Warning:a partial dump from A server which has gtids would by default include the Gtids of all transactions, even those tha T changed suppressed parts of the database. If you don ' t want to restore gtids, pass--set-gtid-purged=off. To make a complete dump, pass--all-databases--triggers--routines--events.

Mysql> \q

Bye

[Email protected] ~]# cd/opt

[email protected] opt]# LL

Total 8

-rw-r--r--1 root root 2222 may 11:09 Oldboy.sql

Drwxr-xr-x. 2 root root 4096 Mar-RH

[Email protected] opt]# cat Oldboy.sql

--MySQL dump 10.13 distrib 5.6.34, for linux-glibc2.5 (x86_64)

--

--Host:localhost Database:oldboy

-- ------------------------------------------------------

--Serverversion 5.6.34-log

/*!40101 SET @[email protected] @CHARACTER_SET_CLIENT */;

/*!40101 SET @[email protected] @CHARACTER_SET_RESULTS */;

/*!40101 SET @[email protected] @COLLATION_CONNECTION */;

/*!40101 SET NAMES UTF8 */;

/*!40103 SET @[email protected] @TIME_ZONE */;

/*!40103 SET time_zone= ' +00:00 ' */;

/*!40014 SET @[email protected] @UNIQUE_CH

16 Delete all data from the test table and view

mysql> desc test;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| ID | Int (4) |     NO | |       NULL | |

| Age | tinyint (2) |     YES | |       NULL | |

| name | varchar (16) |     NO | |       NULL | |

+-------+-------------+------+-----+---------+-------+

3 Rows in Set (0.00 sec)

mysql> truncate test; View what you see after you delete data from the test table

Query OK, 0 rows affected (0.19 sec)

Mysql>

17 Delete the table test and Oldboy database and view

Mysql> Show tables;

+------------------+

| Tables_in_oldboy |

+------------------+

| Test |

+------------------+

1 row in Set (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| BBS |

| MySQL |

| Oldboe |

| Oldbou |

| Oldboy |

| Performance_schema |

| Test |

| WordPress |

| www |

| Xinjia |

| Xu |

+--------------------+

Rows in Set (0.00 sec)

mysql> drop table test;

Query OK, 0 rows affected (0.14 sec)

Mysql> Show tables;

Empty Set (0.00 sec)

mysql> drop databases Oldboy;

Error 1064 (42000): You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use-near ' databases-Oldboy ' at line 1

mysql> drop Database Oldboy;

Query OK, 0 rows affected (0.12 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| BBS |

| MySQL |

| Oldboe |

| Oldbou |

| Performance_schema |

| Test |

| WordPress |

| www |

| Xinjia |

| Xu |

+--------------------+

Rows in Set (0.00 sec)

Mysql>

18 Do not exit database reply above Delete database

mysql> source/opt/oldboy.sql Recovery Database

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

19 Set the ID column as the primary key and create a normal index on the name field

Mysql> CREATE TABLE test (ID int (4) not null,name char (+) not null,primary key (ID));

ERROR 1050 (42S01): Table ' test ' already exists

Mysql> CREATE TABLE coco (id int (4) not null,name char (+) not null,primary key (IID)); Add primary Key

Query OK, 0 rows affected (0.11 sec)

Mysql>

mysql> ALTER TABLE Coco Add index index_name (name); Create normal index

Query OK, 0 rows affected (0.03 sec)

records:0 duplicates:0 warnings:0

Mysql>

20 Insert Phone Number field after field name (Shouji) type char (11)

mysql> ALTER TABLE coco add Shouji char (one) after name;

Query OK, 0 rows affected (0.10 sec)

records:0 duplicates:0 warnings:0

mysql> desc Coco;

+--------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+----------+------+-----+---------+-------+

| ID | Int (4) | NO | PRI |       NULL | |

| name | char (16) | NO | MUL |       NULL | |

| Shouji | char (11) |     YES | |       NULL | |

+--------+----------+------+-----+---------+-------+

3 Rows in Set (0.00 sec)

Mysql>

21 Insert 2 records on all fields (self-setting data)

mysql> insert INTO Coco (Id,name,shouji) VALUES (1, ' Keke ', ' 1211211 '), (2, ' Xinpan ', ' 112322112 ');

Query OK, 2 rows affected (0.03 sec)

Records:2 duplicates:0 warnings:0

Mysql> flush Privileges; Refresh the database

Query OK, 0 rows affected (0.01 sec)

22 Delete the index of the name column

23 Check the phone number starts with 12. A record named Keke.

Mysql> SELECT * from the coco where Shouji like ' 12% ' and the name like ' Keke ';

+----+------+---------+

| ID | name | Shouji |

+----+------+---------+

| 1 | Keke | 1211211 |

+----+------+---------+

1 row in Set (0.10 sec)

Mysql>

24 Deleting Oldboy users

Mysql> select User,host from Mysql.user;

+--------+------------+

| user | Host |

+--------+------------+

| Keke | %          |

| Oldboy | %          |

| Xinjia | %          |

| MHA | 10.0.0.% |

| Rep | 10.0.0.% |

| Root | 127.0.0.1 |

| BBS | 172.16.1.% |

| BBS | 172.16.1.8 |

| Root | :: 1 |

| | localhost |

| BBS | localhost |

| Oldboy | localhost |

| Root | localhost |

| | Root |

| Root | Root |

+--------+------------+

Rows in Set (0.10 sec)

mysql> drop user [email protected] ' localhost ';

Query OK, 0 rows affected (0.14 sec)

Mysql> select User,host from Mysql.user;

+--------+------------+

| user | Host |

+--------+------------+

| Keke | %          |

| Oldboy | %          |

| Xinjia | %          |

| MHA | 10.0.0.% |

| Rep | 10.0.0.% |

| Root | 127.0.0.1 |

| BBS | 172.16.1.% |

| BBS | 172.16.1.8 |

| Root | :: 1 |

| | localhost |

| BBS | localhost |

| Root | localhost |

| | Root |

| Root | Root |

+--------+------------+

Rows in Set (0.00 sec)

Mysql>

25 Recover Select permissions for Oldboy users

Revoke select on oldboy.* from [email protected] ' localhost ';

Shell terminal execution uses the-e parameter to invoke the MySQL internal command

MYSQL-UROOT-POLDBOY123-E "Show grants for [email protected] ' localhost ' | Grep-i Select

26 Deleting a Oldboy database

Mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| BBS |

| MySQL |

| Oldboe |

| Oldbou |

| Oldboy |

| Performance_schema |

| Test |

| WordPress |

| www |

| Xinjia |

| Xu |

+--------------------+

Rows in Set (0.01 sec)

mysql> drop Database Oldboy;

Query OK, 4 rows affected (0.13 sec)

Mysql>

This article from "Xiao Xin" blog, declined reprint!

MySQL Database basic command Rollup 2