14 在test表中字段name前插入age字段 类型tinyint(2)

mysql> create table test (id int(4) not null, name varchar(16) 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> 

15 不退出数据库备份oldboy数据库

mysql> show databases;

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

| Database           |

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

| information_schema |

| bbs                |

| mysql              |

| oldboe             |

| oldbou             |

| oldboy             |

| performance_schema |

| test               |

| wordpress          |

| www                |

| xinjia             |

| xu                 |

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

12 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 be insecure.

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that 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

[root@root ~]# cd /opt

[root@root opt]# ll

total 8

-rw-r--r--  1 root root 2222 May 15 11:09 oldboy.sql

drwxr-xr-x. 2 root root 4096 Mar 26  2015 rh

[root@root opt]# cat oldboy.sql 

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

--

-- Host: localhost    Database: oldboy

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

-- Server version 5.6.34-log


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

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

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CH

16 删除test表中的所有数据,并查看

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; 查看删除test表中的数据后查看的内容

Query OK, 0 rows affected (0.19 sec)


mysql> 

17 删除表test和oldboy数据库并查看

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                 |

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

12 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 an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to 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                 |

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

11 rows in set (0.00 sec)


mysql> 


18 不退出数据库回复以上删除数据库

mysql> source /opt/oldboy.sql 恢复数据库

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 在把id列设置为主键,在name字段上创建普通索引

mysql> create table test(id int(4) not null,name char(16) not null,primary key (id));

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

mysql> create table coco(id int(4) not null,name char(16) not null,primary key (iid)); 添加主键

Query OK, 0 rows affected (0.11 sec)


mysql> 

mysql> alter table coco add index index_name(name);创建普通索引

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0


mysql> 

20 在字段name后插入手机号字段(shouji)类型char(11)

mysql> alter table coco add shouji char(11) 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 所有字段上插入2条记录(自行设定数据)

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;刷新数据库

Query OK, 0 rows affected (0.01 sec)

22 删除name列的索引



23 查询手机号以12开头的。名字为keke的记录

mysql> select * from coco where shouji like '12%' and name like 'keke';

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

| id | name | shouji  |

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

|  1 | keke | 1211211 |

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

1 row in set (0.10 sec)


mysql> 

24 删除oldboy用户

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       |

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

15 rows in set (0.10 sec)


mysql> drop user oldboy@'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       |

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

14 rows in set (0.00 sec)


mysql> 

25 收回oldboy用户的select权限

revoke select on oldboy.* from oldboy@'localhost';

shell终端执行  使用-e参数调用mysql内部命令

mysql -uroot -poldboy123 -e "show grants for root@'localhost'" | grep -i select


26 删除oldboy数据库

mysql> show databases;

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

| Database           |

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

| information_schema |

| bbs                |

| mysql              |

| oldboe             |

| oldbou             |

| oldboy             |

| performance_schema |

| test               |

| wordpress          |

| www                |

| xinjia             |

| xu                 |

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

12 rows in set (0.01 sec)


mysql> drop database oldboy;

Query OK, 4 rows affected (0.13 sec)


mysql>