数据库与表结构相关的常用命令

一查看现有数据库

mysql> show databases;

二指定当前缺省数据库

mysql> use datatest;
Database changed

三查看当前使用的数据库

mysql> select database();
+------------+
| database() |
+------------+
| datatest   |
+------------+

四查看当前库中的表

mysql> show tables;
+--------------------+
| Tables_in_datatest |
+--------------------+
| dept               |
| emp                |
| salgrade           |
+--------------------+

五查看其它数据库中的表

mysql> show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| class          |
| course         |
| dept           |
| employee       |
| score          |
| student        |
| teacher        |
| user           |
+----------------+

六查看表的结构

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   | PRI | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| JOB      | varchar(9)  | YES  |     | NULL    |       |
| MGR      | int(4)      | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| SAL      | double(7,2) | YES  | MUL | NULL    |       |
| COMM     | double(7,2) | YES  |     | NULL    |       |
| DEPTNO   | int(2)      | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+

七查看表的创建语句

mysql> show create table emp;
+-------+---------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------+
| Table | Create Table



                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------+
| emp   | CREATE TABLE `emp` (
  `EMPNO` int(4) NOT NULL COMMENT '员工编号',
  `ENAME` varchar(10) DEFAULT NULL COMMENT '员工姓名',
  `JOB` varchar(9) DEFAULT NULL COMMENT '工作岗位',
  `MGR` int(4) DEFAULT NULL COMMENT '上级经理',
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int(2) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`),
  KEY `DEPTNO` (`DEPTNO`),
  KEY `SAL` (`SAL`),
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工编号'                                    |
+-------+---------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)