SQL常用管理命令

启动停止服务

net start mysql
net stop mysql

登录

mysql -u root -p
password: ****(输入密码)

常用维护[更多信息]

show databases;
select database() as db;

use db_name;
show tables;

有哪些表

mysql> show tables;
+------------------+
| Tables_in_runoob |
+------------------+
| runoob_tbl       |
+------------------+
1 row in set (0.00 sec)

表结构

mysql> show columns from runoob_tbl; #（or desc runoob_tbl）
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| runoob_id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| runoob_title    | varchar(100)     | NO   |     | NULL    |                |
| runoob_author   | varchar(40)      | NO   |     | NULL    |                |
| submission_date | date             | YES  |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

表的创建

mysql> show create table runoob_tbl;
| runoob_tbl | CREATE TABLE `runoob_tbl` (
  `runoob_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `runoob_title` varchar(100) NOT NULL,
  `runoob_author` varchar(40) NOT NULL,
  `submission_date` date DEFAULT NULL,
  PRIMARY KEY (`runoob_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

插入数据

INSERT INTO runoob_tbl 
(runoob_title, runoob_author, submission_date)
VALUES
("学习 PHP", "菜鸟教程", NOW()); 

("学习 MySQL", "菜鸟教程", NOW());
("JAVA 教程", "RUNOOB.COM", '2016-05-06');

查询

mysql> select * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 学习 PHP     | 菜鸟教程      | 2018-07-10      |
|         2 | 学习 MySQL   | 菜鸟教程      | 2018-07-10      |
|         3 | JAVA 教程    | RUNOOB.COM    | 2016-05-06      |
+-----------+--------------+---------------+-----------------+
3 rows in set (0.00 sec)

修改提示符

1，链接客户端时通过参数指定 
#mysql -u root -p mysql --prompt 提示符参数

2,链接上客户端后，通过prompt命令修改
mysql> prompt 提示符参数；

mysql 提示符参数

\D -->完整的日期
\d -->当前数据库
\h -->服务器名称
\u -->当前用户

示例

mysql>prompt \u@\h \d>
PROMPT set to "\u@\h \d>"
root@localhost test>