MySQL数据库对数据库的操作

MySQL数据库对数据库的操作

1.创建数据库
mysql> create database if not exists tongcheng;
Query OK, 1 row affected (0.01 sec)
mysql>

2.查看创建数据库时的选项
mysql> show create database tongcheng;
+-----------+----------------------------------------------------------------------+
| Database  | Create Database                                                      |
+-----------+----------------------------------------------------------------------+
| tongcheng | CREATE DATABASE `tongcheng` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+-----------+----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

3.连接数据库
mysql> use tongcheng;
Database changed
mysql>

4.查看当前的数据库
mysql> select database();
+------------+
| database() |
+------------+
| tongcheng  |
+------------+
1 row in set (0.01 sec)
mysql>

5.显示所有数据库
mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| mysql              |
| test              |
| tongcheng          |
+--------------------+
4 rows in set (0.00 sec)
mysql>

6.修改数据库的属性
mysql> alter database tongcheng character set gbk;
Query OK, 1 row affected (0.00 sec)
mysql>

7.删除数据库
mysql> drop database if exists tongcheng;
Query OK, 0 rows affected (0.02 sec)
mysql>

8.用mysqladmin创建数据库
123 [root@localhost ~]# mysqladmin create tongcheng -u root -p
Enter password:
[root@localhost ~]#

9.用mysqlshow查看数据库
[root@localhost ~]# mysqlshow -u root -p tongcheng
Enter password:
Database: tongcheng
+--------+
| Tables |
+--------+
+--------+
[root@localhost ~]#

10.用mysqlshow查看数据库有多少表
[root@localhost ~]# mysqlshow -u root -p tongcheng
Enter password:
Database: tongcheng
+--------+
| Tables |
+--------+
| t      |
+--------+
[root@localhost ~]#

11.用mysqlshow查看数据库中表的信息
[root@localhost ~]# mysqlshow -u root -p tongcheng t
Enter password:
Database: tongcheng  Table: t
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type    | Collation | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
| a    | int(11) |          | YES  |    |        |      | select,insert,update,references |        |
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
[root@localhost ~]#

12.用mysqlshow查看数据库中表的索引信息
[root@localhost ~]# mysqlshow  -u root -p -k tongcheng t
Enter password:
Database: tongcheng  Table: t
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type    | Collation | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
| a    | int(11) |          | YES  | MUL |        |      | select,insert,update,references |        |
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t    | 1          | in_tong  | 1            | a          | A        |            |          |        | YES  | BTREE      |        |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
[root@localhost ~]#

13.用mysqladmin删除数据库
[root@localhost ~]# mysqladmin drop  tongcheng  -u root -p
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'tongcheng' database [y/N] y
Database "tongcheng" dropped
[root@localhost ~]#

14.用mysqladmin关闭数据库
[root@localhost ~]# mysqladmin  -u root -p shutdown
Enter password:
[root@localhost ~]#