1. 登陆与退出

（1） 登陆

mysql –u用户名–p密码

此命令密码是明文

mysql –u用户名–p

Enter Password

登陆的同时指明主机号：

mysql –h主机名–u用户名 –p密码

登陆的同时指明端口号

mysql –h主机名–u用户名 –p密码–P端口号（3306）

登陆的同时修改命令提示符：

mysql –h主机名–u用户名 –p密码–P端口号（3306）--prompt=

命令提示符包括：

\u当前用户名

\h当前主机名

\D当前日期时间

\d当前打开的数据库

也可以在登陆后，修改命令提示符：

Promptkaikeba>

命令提示符改为kaikeba>

登陆的同时修改命令分隔符：

mysql –h主机名–u用户名 –p密码–P端口号（3306）--prompt= --delimiter=

也可以再登陆之后修改命令分隔符

Delimiter @

命令结束符变为：@

在登陆的同时打开数据库：

Mysql -u用户名 -p密码 -h主机名 -D数据库名

Eg:

Mysql -uroot-proot -hlocalhost -Dtest;

（2） 退出

Exit

\q

Quit

Ctrl+C快捷键

（3） SQL规范

数据库名、表名小写；关键字、保留字、函数名称大写；

SQL命令支持折行操作，但不能将单词、名称、或成对引号折行写

当名称与保留字冲突时，需用‘’括上名称

（4） 开启输出日志的形式

\T 文件保存位置及文件名称

\t 结束输出日志

按上下键调查之前写过的命令

2. DDL操作

（1） 创建数据库

CREATE{DATABASE|SCHEMA} db_name;

CREATE{DATABASE|SCHEMA} [IF NOT EXISTS] db_name;

CREATE{DATABASE|SCHEMA} [IF NOT EXISTS] db_name [[DEFAULT] CHARACTER SET [=] 字符集];

（2） 查看当前服务器下已有的数据库

SHOW{DATABASES|SCHEMA};

（3） 查看上一步操作得到的警告

SHOW WARNINGS

（4） 查看已创建数据库的编码方式

SHOW CREATEDATABASE db_name;

综合案例1：

mysql> CREATEDATABASE randongmei;

Query OK, 1 rowaffected (0.00 sec)

mysql> SHOWDATABASES;

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

| Database |

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

|information_schema |

| mysql |

|performance_schema |

|randongmei |

| test |

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

5 rows in set(0.00 sec)

mysql> CREATESCHEMA king;

Query OK, 1 rowaffected (0.00 sec)

mysql> SHOWSCHEMAS;

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

| Database |

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

|information_schema |

| king |

| mysql |

| performance_schema|

|randongmei |

| test |

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

6 rows in set(0.00 sec)

mysql> CREATESCHEMA king;

ERROR 1007(HY000): Can't create database 'king'; database exists

mysql> CREATESCHEMA IF NOT EXISTS king;

Query OK, 1 rowaffected, 1 warning (0.00 sec)

mysql> SHOWWARNINGS;

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

| Level | Code |Message |

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

| Note | 1007 | Can't create database 'king';database exists |

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

1 row in set(0.00 sec)

mysql> SHOWDATABASES;

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

| Database |

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

| information_schema|

| king |

| mysql |

|performance_schema |

|randongmei |

| test |

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

6 rows in set(0.00 sec)

mysql> SHOWCREATE DATABASE randongmei;

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

| Database | Create Database |

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

| randongmei |CREATE DATABASE `randongmei` /*!40100 DEFAULT CHARACTER SET utf8 */ |

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

1 row in set(0.00 sec)

mysql> CREATEDATABASE IF NOT EXISTS test1 DEFAULT CHARACTER SET ='gbk';

Query OK, 1 rowaffected (0.00 sec)

mysql> SHOWCREATE DATABASE test1

-> ;

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

| Database |Create Database |

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

| test1 | CREATE DATABASE `test1` /*!40100 DEFAULTCHARACTER SET gbk */ |

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

1 row in set(0.00 sec)

mysql> CREATESCHEMA IF NOT EXISTS test3 CHARACTER SET 'GBK';

Query OK, 1 rowaffected (0.00 sec)

mysql> \t

（5） 修改数据库的编码方式

ALTER{DATABASE|SCHEMA} db_name [[DEFAULT] CHARACTER SET [=] 字符集];

mysql> SHOWDATABASES;

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

| Database |

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

|information_schema |

| king |

| mysql |

|performance_schema |

|randongmei |

| test |

| test1 |

| test3 |

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

8 rows in set(0.02 sec)

mysql> SHOWCREATE DATABASE test1;

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

| Database |Create Database |

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

| test1 | CREATE DATABASE `test1` /*!40100 DEFAULTCHARACTER SET gbk */ |

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

1 row in set(0.00 sec) 

mysql> ALTERDATABASE test1 DEFAULT CHARACTER SET 'UTF8';

Query OK, 1 rowaffected (0.00 sec) 

mysql> SHOWCREATE DATABASE test1;

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

| Database |Create Database |

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

| test1 |CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ |

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

1 row in set(0.00 sec) 

mysql> SHOWCREATE SCHEMA test3;

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

| Database |Create Database |

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

| test3 | CREATE DATABASE `test3` /*!40100 DEFAULTCHARACTER SET gbk */ |

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

1 row in set(0.00 sec) 

mysql> ALTERDATABASE test3 CHARACTER SET 'UTF8';

Query OK, 1 rowaffected (0.00 sec) 

mysql> SHOWCREATE DATABASE test3;

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

| Database |Create Database |

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

| test3 | CREATE DATABASE `test3` /*!40100 DEFAULTCHARACTER SET utf8 */ |

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

1 row in set(0.00 sec) 

mysql> \t

（6） 打开指定数据库

USE db_name;

（7） 得到当前已经打开的数据库

SELECT{DATABASE()|SCHEMA()}; 

mysql> SHOWDATABASES;

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

| Database |

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

|information_schema |

| king |

| mysql |

|performance_schema |

|randongmei |

| test |

| test1 |

| test3 |

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

8 rows in set(0.00 sec) 

mysql> USEtest1;

Database changed

mysql> SELECTDATABASE();

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

| DATABASE() |

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

| test1 |

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

1 row in set(0.00 sec) 

mysql> USErandongmei;

Database changed

mysql> SELECTSCHEMA();

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

| SCHEMA() |

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

| randongmei |

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

1 row in set(0.00 sec)

mysql> \t

（8） 删除指定的数据库

DROP {DATABASE|SCHEMA} db_name;

DROP {DATABASE|SCHEMA} [IF EXISTS] db_name;

不可以一次删除多个数据库