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 |
+-------+-------------+------+-----+---------+-------+
|> | name | varchar(16) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.10 sec)
|> | 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 version5.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 |
+-------+-------------+------+-----+---------+-------+
|> | 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>