本文共 5727 字，大约阅读时间需要 19 分钟。
数据库语言：
DDL(Data Defination Language):
数据定义语言，主要用于定义数据库、表、视图、索引和触发器等。像DROP/CREATE/ALTER.
DML(Data Manipulation Language):
主要包括对数据的增删改查。INSERT插入数据、UPDATE更新数据、DELETE删除数据。
DQL(Data Query Language)：
数据检索语句，用来从表中获取数据，确定数据怎么在应用程序中给出。像SELECT查询语句。
DCL(Data Control Language):
数据控制语言，主要用于控制用户访问的权限。像GRANT/REVOKE/COMMIT/ROLLBACK等语句。
--prompt 修改msql命令提示符，只对当前登录有效。
mysql --prompt meng
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
meng
meng
meng
mengshow schemas;
退出：(三选一都行)
\q
quit
exit
mysql>select version(); #查看当前版本
mysql>select user(); #查看当前的用户
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql>select now(); #查看当前的时间
+---------------------+
| now() |
+---------------------+
| 2017-03-07 22:05:43 |
+---------------------+
1 row in set (0.01 sec)
mysql语句规范：
1、关键字与函数名称全部大写；
2、数据库名称、表字段、字段名称等全部小写；
3、sql语句必须以分隔符结尾。
4、sql语句支持折行操作，只要不把单词、标记或者引号字符串分割为2部分。可以在下一行继续写。
5、数据库名称、表名称、字段名称等尽量不要使用mysql的保留字，如果需要使用的时候使用梵音好(``)将名称括起来。
\c #此命令不执行
mysql> select now()\c
delimiter (\d) Set statement delimiter. #修改默认的分隔符，只对当前登录有效。
mysql> delimiter //
mysql> select now();
-> //
+---------------------+
| now() |
+---------------------+
| 2017-03-07 22:29:19 |
+---------------------+
1 row in set (0.00 sec)
mysql> select now()//
+---------------------+
| now() |
+---------------------+
| 2017-03-07 22:29:29 |
+---------------------+
1 row in set (0.00 sec)
mysql> \q
Bye
#命令提示符修改
\h 主机
\D 日期
\d 数据库
\u 用户
mysql> prompt \h~\D~\d
PROMPT set to '\h~\D~\d'
localhost~Wed Mar 8 03:31:43 2017~(none)use zabbix
Database changed
localhost~Wed Mar 8 03:31:52 2017~zabbixshow tables;
Empty set (0.00 sec)
localhost~Wed Mar 8 03:32:01 2017~zabbix
数据库中引号：(引号需要成对出现，sql语句支持折叠)
mysql>select
->
-> now()'
'> ';
+---------------------+
| |
+---------------------+
| 2017-03-08 03:53:24 |
+---------------------+
1 row in set, 1 warning (0.00 sec)
mysql>
#操作日志输出
mysql>\T /data/www/
mysql: Can't create/write to file '/data/www/' (Errcode: 21 - Is a directory)
Error logging to file '/data/www/'
mysql>\T /data/www/mysql1.txt
Logging to file '/data/www/mysql1.txt'
mysql>select now();
+---------------------+
| now() |
+---------------------+
| 2017-03-08 04:04:48 |
+---------------------+
1 row in set (0.00 sec)
mysql>\t #结束操作日志输出
Outfile disabled.
mysql>\q
Bye
[root@localhost ~]# cat /data/www/mysql1.txt
mysql>select now();
+---------------------+
| now() |
+---------------------+
| 2017-03-08 04:04:48 |
+---------------------+
1 row in set (0.00 sec)
mysql>\t
[root@localhost ~]#
#\w关闭警告 \W 开启警告
mysql> \w
Show warnings disabled.
mysql> \W
Show warnings enabled.
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | 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 'warings' at line 1 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#创建数据库，如果不存在则创建。
mysql> create schema if not exists test;
Query OK, 1 row affected, 1 warning (0.00 sec)
Note (Code 1007): Can't create database 'test'; database exists
mysql>
#查看数据库的定义：
mysql> show create schema test2;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| test2 | CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
#修改指定数据库字符集编码：
mysql> alter schema test2 default character set utf8;
Query OK, 1 row affected (0.00 sec)
mysql> alter schema test2 default character set gbk;
Query OK, 1 row affected (0.00 sec)
#查看指定数据库创建信息：
mysql> show create schema test2;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| test2 | CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
#查看当前所在的数据库：
mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
mysql> status;
--------------
mysql Ver 14.14 Distrib 5.6.29, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 13
Current database: test
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.29 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 1 day 7 hours 40 min 4 sec
Threads: 1 Questions: 107 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.000
--------------
#删除指定数据库
mysql> drop schema test2;
Query OK, 0 rows affected (0.03 sec)
mysql> show schemas;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| zabbix |
+--------------------+
5 rows in set (0.00 sec)
本文转自青衫解衣 51CTO博客，原文链接:http://blog.51cto.com/215687833/1904653
转载地址：http://jpgjx.baihongyu.com/