Mysql你必须知道的命令和函数

引言

在本文中总结一些Mysql的常用命令和函数，也许常见，也许不常见，但是重要。笔者目前整理的一些blog针对面试都是超高频出现的。大家可以点击链接：http://blog.csdn.net/u012403290

常用、重要命令

1、完整的链接mysql命令
mysql -h host -P port -u user -p

2、查看所有数据库
show databases

3、选择某一个数据库，查看所有表
use database_name;
show tables;

在上面有一个叫mysql的数据库，这个库中主要是存放一些配置和用户，我用命令选择它同时查看它有哪些表。

4、查看当前数据库
select database()

查看你目前操作的数据库，比如说前面我use mysql，那么我当前操作的数据库就是mysql

5、刷新数据库
flush privileges;

命令本质上的作用是将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里。MySQL用户数据和权限有修改后，希望在”不重启MySQL服务”的情况下直接生效，那么就需要执行这个命令。

6、退出mysql
exit或者quit或者CTRL+D退出mysql

7、创建一个用户
前面有提到，其实mysql的user表就是用户表，但是如果你不是root用户，进入mysql是没有权限操作mysql这个数据库的，并且表也是屏蔽的。接下来，我们用root用户创建一个用户:

insert into mysql.user(Host,User,Password) values(‘localhost’,’brickwork’,password(‘1234’));
先解释一下上面这个句子，Host的意思是通过哪个IP进行访问，如果你插入的时候是localhost，或者127.0.0.1，那么你只能在这台服务器本身上进行操作它，在别的ip上进行远程访问是不允许的。为什么插入password是会后需要password(‘1234’)这么写，是因为password是一个函数，它可以把你的密码进行加密：

在创建用户结束之后，一定要进行数据库用户和权限的刷新操作（flush privileges），不然是登录不了的。

8、给已有用户分配权限
如果创建一个用户之后，如果不给它赋予权限，那么它对所有的私人库表是不可见的。

如果你是DBA，那么你就需要分派用户权限。比如说这个brickwork是一个新手，那么我就只赋予它hospital数据库的所有表的读的权限，那么你就可以这么描述：
grant select on hospital.* to brickwork @localhost identified by ‘1234’;
逐字逐句翻译过来就是：赋予查询的权限在hospital这个数据库的所有表单上 给 brickwork这个只允许本地登录的用户 验证密码为1234

通用表达就是这样的：
grant 权限 on 数据库.* to 用户名@登录主机 identified by “密码”;
其中，权限包含：select,insert,update,delete,create,drop等14个权限，如果你想知道更多，请进一步研究权限系统。

接下来，我们登录brickwork这个用户，查看权限赋予之后，它可见的数据库有哪些：

可以看到区别了，在前面创建用户之后，我们并不能看到hospital库，但是权限赋予之后，我们就可以看到hospital库了。再比如说，我只开放hospital.doctor表给这个用户又如何呢？具体的学习的朋友可以自己尝试一下。在前面我们只赋予了brickwork读的权限，所以我们进行测试，尝试读取和写入：

从图中可以看出，在查询的时候是可以正常操作的，但是再插入的时候会抛出错误，提示权限不足。

9、创建用户并分配权限
难道一定要创建用户之后才可以分配权限吗？其实并不需要，在上面的权限分配的命令： grant 权限 on 数据库.* to 用户名@登录主机 identified by “密码”; 其实也保证了用户创建的所有信息。那么我们其实可以直接用这一句命令来创建一个新用户并赋予权限：

从图中可以看到，在执行该条命令的时候，如果用户不存在，它会直接新建一个用户，而且不用想前面那样刷新数据库。再者，在该创建语句中，我指定newone这个用户只能够查询hospital这个库中的doctor表，我们看看newone看到的表情况是如何的：

那么这个newone用户只能查看doctor这一张表，并且他只具有这张表的读权限。

10、支持任意IP远程访问该数据库

GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘password’ WITH GRANT OPTION
简单解释一下上面的语句，我们前面已经说过了关于权限赋予的命令，其实这条语句就是一个权限赋予的语句。在这个语句中“ALL PRIVILEGES ”表示所有权限，“ON . ”表示对所有的数据库和表单。“ ‘root’@’%’”表示远程登录的用户名是root，同时%表示任意IP，所以这句话合起来就是远程任意IP用用户名root登录。

11、修改某一个用户的密码
set password for 用户名@登录主机=password(‘新密码’)；
我要修改用户名为brickwork的密码为123456：

12、查看表结构
desc table_name

我要查看doctor这个表设计的结构：

13、显示当前数据库的详细状态
status

14、显示最近一条警告信息
show warning；

有的时候，数据库告诉你一条错误警告，但是你没有注意，或者懒得返回去查看，你就可以用这条命令，记住前提是存在最近的一条警告：
比如说我输错了命令，系统给我了我一条警告，然后我用命令打印出最近的警告：

15、清空输入字符
\c

有的时候，我们输了老长一段sql命令，你明明知道是错误的，所以并不想回车执行它，也不想按delete来删除，那么你就可以直接用\c来结尾，表示情况所有的输入字符：

在上面的句子中，你发现自己明显select少写了一个e，那么你就可以用\c来结尾，表示清空你写的命令，重置输入。注意是反斜杠哦。

16、定义用户变量
set @var_name = expr[,@var_name=expr]….

比如说我要存储的一个基准值，这个基准值用于判断医生的医生的优先级，我们定义为3。如果医生的id字段大于3表示这个医生是合法的。那么我们就可以用用户变量来表示：

函数

这里所诉述的不包含基本的算术操作符+，-，*，/。我们研究一下mysql的一些重要的数学函数，在这之前，我要说明下，因为个人习惯问题，我在写sql命令的时候喜欢用小写，但是写函数喜欢用大写。其实在mysql中不区分命令的大小写，即使大小写混合也不会有问题，但是还是建议大家都采用大写。

1、X的绝对值
ABS(X)

2、返回不小于X的最小整数值
CEIL(X)

注意，CEIL其实就是返回一个大于X的整数，比如-3.5，那么大于它的最小整数就是-3。3.5那么大于它的最小整数就是4。

3、返回不大于X的最大整数值
FLOOR(X)

FLOOR和CEIL是相对的。

4、产生随机数
RAND()，返回一个浮点值，范围0-1之间

自己观察上面的例子，你会发现RAND（N）中带参数的随机数，它的结果都是一致的，它主要是用来产生重复的序列。

5、数值符号
SIGN(X)
传入一个数值X。SIGN函数会告诉你X是正值，负值或者是0。总共只有3种情况，输入不规范的数值会报错：

6、控制数据位数
TRUNCATE(X,D)。X是一个待处理的数值，D表示保留小数点左右位数情况。比如说：
X=32.112，D=4 ====> 结果32.1120

X=32.112，D=1 ====> 结果32.1

X= 32.112， D=0 ====> 结果32

X=32.112， D=-1 =====>结果30

X =32.112，D=-2 ======>结果0

7、返回最近似的正数

ROUND(X)

在参数只有X本身的时候就如上述情况，但是这个函数可以支持两个参数。ROUND(X,D)，这个情况和我们前面说的情况类似，就不一一赘述了，请看博主的测试结果：

8、返回字符串的长度
CHAR_LENGTH(str) 与 LENGTH(str)

从上面可以看出两者的差别，对于中文字符，CHAR_LENGTH是当做一个字符记录的，而LENGTH是当做三个字符记录的。

9、链接两个字符串
CONCAT(str1,str2…)

10、获取字符串从左/右开始的第几个字符

LEFT(str，length) 和RIGHT（str，length）

11、字符串截取

SUBSTRING(str, pos,length)

12、字符串大小写转换
LOWER(str)和UPPER(str)

其实函数还有很多，包括时间和日期的函数，甚至还有用户自定义函数，这里不再继续深究了，不然篇幅太长了。

尾记

Mysql的这些命令和这些函数都是非常重要和常用的，即使我们不做DBA，但是作为一个服务端开发者，了解这些是必须的，不然你将来怎么做CTO，怎么迎娶白富美呢？
希望对大家有所帮助。