MySQL命令大全(非常详细)零基础入门到精通,收藏这一篇就够了

引言

相信大家在编写SQL时一定有一个困扰,就是明明记得数据库中有个命令/函数,可以实现自己需要的功能,但偏偏不记得哪个命令该怎么写了,这时只能靠盲目的去百度,以此来寻找自己需要的命令。

时间是最厉害的武器,少年定会白首,鲜花亦会凋零,沧海会演变桑田,高山也会化作平原。

而我们每一位开发者,作为人类也不例外,无法抵挡时间的流逝,其记忆力会随着时间逐渐推移不断下降,而MySQL中的命令/函数那么多,咱们也并不能完全记住,所以对于前面的那种情况,在实际开发中也属常事,所以本章则会将一些常用的SQL命令/函数全部罗列出来,以后当需要用到时只需回来搜索即可。

PS:个人编写的《技术人求职指南》小册已完结,其中从技术总结开始,到制定期望、技术突击、简历优化、面试准备、面试技巧、谈薪技巧、面试复盘、选Offer方法、新人入职、进阶提升、职业规划、技术管理、涨薪跳槽、仲裁赔偿、副业兼职……,为大家打造了一套“从求职到跳槽”的一条龙服务,同时也为诸位准备了七折优惠码:3DoleNaE,近期需要找工作的小伙伴可以复制链接了解详情:https://s.juejin.cn/ds/USoa2R3/

当大家以后需要使用某条命令/函数时,可以很好的利用这篇命令大全来辅助您,方式有两种:

  • • ①按下Ctrl+F搜索快捷键,搜索关键词用于定位相应的命令。

  • • ②本文会以功能对所有命令进行分类,通过右侧的文章目录可按功能快捷调整命令位置。

当然,如若本文对你有些许帮助,那请不要忘了点赞支持一下噢~

一、基础操作与库命令

首先来介绍一些关于MySQL基础操作的命令,以及操作数据库相关的命令,MySQL中的所有命令默认是以;分好结尾的,因此在执行时一定要记得带上分号,否则MySQL会认为你这条命令还未结束,会继续等待你的命令输入,如下:

等待输入

1.1、MySQL基础操作命令
  • net start mysqlWindows系统启动MySQL服务。

  • 安装目录/mysql startLinux系统启动MySQL服务。

  • shutdown:后面的start换成这个,表示关闭MySQL服务。

  • restart:换成restart表示重启MySQL服务。

  • ps -ef | grep mysqlLinux查看MySQL后台进程的命令。

  • kill -9 MySQL进程ID:强杀MySQL服务的命令。

  • mysql -h地址 -p端口 -u账号 -p:客户端连接MySQL服务(需要二次输入密码)。

  • show status;:查看MySQL运行状态。

  • SHOW VARIABLES like %xxx%;:查看指定的系统变量。

  • show processlist;:查看当前库中正在运行的所有客户端连接/工作线程。

  • show status like "Threads%";:查看当前数据库的工作线程系统。

  • help data types;:查看当前版本MySQL支持的所有数据类型。

  • help xxx:查看MySQL的帮助信息。

  • quit:退出当前数据库连接。

1.2、MySQL库相关的命令
  • show databases;:查看目前MySQL中拥有的所有库。

  • show engines;:查看当前数据库支持的所有存储引擎。

  • use 库名;:使用/进入指定的某个数据库。

  • show status;:查看当前数据库的状态信息。

  • show grants;:查看当前连接的权限信息。

  • show errors;:查看当前库中记录的错误信息。

  • show warnings:查看当前库抛出的所有警告信息。

  • show create database 库名;:查看创建某个库的SQL详细信息。

  • show create table 表名;:查看创建某张表的SQL详细信息。

  • show tables;:查看一个库中的所有表。

  • desc 表名;:查看一张表的字段结构。除开这种方式还有几种方式:

  • describe 表名;:查看一张表的字段结构。

  • show columns from 表名;:查看一张表的字段结构。

  • explain 表名;:查看一张表的字段结构。

  • create database 库名;:新建一个数据库,后面还可以指定编码格式和排序规则。

  • drop database 库名;:删除一个数据库。

  • ALTER DATABASE 库名 DEFAULT CHARACTER SET 编码格式 DEFAULT COLLATE 排序规则:修改数据库的编码格式、排序规则。

1.3、MySQL表相关的命令

对于MySQL表相关的命令,首先来聊一聊创建表的SQL命令,如下:

CREATE TABLE `库名`.`表名`  (  
    字段名称1 数据类型(精度限制) [字段选项],  
    字段名称2 数据类型(精度限制) [字段选项]  
) [表选项];

对于表中的每个字段,都需要用,分割,但最后一个字段后面无需跟,逗号,同时创建表时,对于每个字段都有多个字段选项,对于一张表而言也有多个表选项,下面一起来看看。

  • • 字段选项(可以不写,不选使用默认值):

  • NULL:表示该字段可以为空。

  • NOT NULL:表示改字段不允许为空。

  • DEFAULT 默认值:插入数据时若未对该字段赋值,则使用这个默认值。

  • AUTO_INCREMENT:是否将该字段声明为一个自增列。

  • PRIMARY KEY:将当前字段声明为表的主键。

  • UNIQUE KEY:为当前字段设置唯一约束,表示不允许重复。

  • CHARACTER SET 编码格式:指定该字段的编码格式,如utf8

  • COLLATE 排序规则:指定该字段的排序规则(非数值类型生效)。

  • COMMENT 字段描述:为当前字段添加备注信息,类似于代码中的注释。

  • • 表选项(可以不写,不选使用默认值):

  • ENGINE = 存储引擎名称:指定表的存储引擎,如InnoDB、MyISAM等。

  • CHARACTER SET = 编码格式:指定表的编码格式,未指定使用库的编码格式。

  • COLLATE = 排序规则:指定表的排序规则,未指定则使用库的排序规则。

  • ROW_FORMAT = 格式:指定存储行数据的格式,如Compact、Redundant、Dynamic....

  • AUTO_INCREMENT = n:设置自增列的步长,默认为1

  • DATA DIRECTORY = 目录:指定表文件的存储路径。

  • INDEX DIRECTORY = 目录:指定索引文件的存储路径。

  • PARTITION BY ...:表分区选项,后续讲《MySQL表分区》再细聊。

  • COMMENT 表描述:表的注释信息,可以在这里添加一张表的备注。

整体看下来会发现选项还蛮多,下面贴个例子感受一下:

-- 在 db_zhuzi 库下创建一张名为 zz_user 的用户表  
CREATETABLE`db_zhuzi`.`zz_user`(  
-- 用户ID字段:int类型、不允许为空、设为自增列、声明为主键  
`user_id`int(8)NOTNULL AUTO_INCREMENT PRIMARY"i_p_id" COMMENT '用户ID',  
-- 用户名称字段:字符串类型、运行为空、默认值为“新用户”  
`user_name`varchar(255)NULLDEFAULT"新用户" COMMENT '用户名'  
)  
-- 存储引擎为InnoDB、编码格式为utf-8、字符排序规则为utf8_general_ci、行格式为Compact  
ENGINE =InnoDB  
CHARACTERSET= utf8   
COLLATE= utf8_general_ci   
ROW_FORMAT =Compact;

上述代码块中就贴出了一个创建表的例子,大家在创建表时可根据需求自行选择需要的字段选项、表选项。

接下来一起来看看其他关于表操作的SQL命令,但对于增删改查的命令会放在后面讲。

  • show table status like 'zz_users'\G;:纵排输出一张表的状态信息。

  • alter table 表名 表选项;:修改一张表的结构,如alter table xxx engine=MyISAM

  • rename table 表名 to 新表名;:修改一张表的表名。

  • alter table 表名 字段操作;:修改一张表的字段结构,操作如下:

  • add column 字段名 数据类型:向已有的表结构添加一个字段。

  • add primary key(字段名):将某个字段声明为主键。

  • add foreing key 外键字段 表名.字段名:将一个字段设置为另一张表的外键。

  • add unique 索引名(字段名):为一个字段创建唯一索引。

  • add index 索引名(字段名):为一个字段创建普通索引。

  • drop column 字段名:在已有的表结构中删除一个字段。

  • modify column 字段名 字段选项:修改一个字段的字段选项。

  • change column 字段名 新字段名:修改一个字段的字段名称。

  • drop primary key:移除表中的主键。

  • drop index 索引名:删除表中的一个索引。

  • drop foreing key 外键:删除表中的一个外键。

  • drop table if exists 表名:如果一张表存在,则删除对应的表。

  • truncate table 表名:清空一张表的所有数据。

  • create table 表名 like 要复制的表名:复制一张表的结构,然后创建一张新表。

  • create table 表名 as select * from 要复制的表名:同时复制表结构和数据创建新表。

1.4、表的分析、检查、修复与优化操作

MySQL本身提供了一系列关于表的分析、检查与优化命令:

  • • ①分析表:分析表中键的分布,如主键、唯一键、外键等是否合理。

  • • ②检查表:检查表以及表的数据文件是否存在错误。

  • • ③修复表:当一个表的数据或结构文件损坏时,可以修复表结构(仅支持MyISAM表)。

  • • ④优化表:消除delete、update语句执行时造成的空间浪费。

分析表

语法如下:

analyze [local | no_write_to_binlog] table 表名1;

其中的可选参数local、no_write_to_binlog代表是否将本条SQL记录进bin-log日志,默认情况下是记录的,加上这两个参数中的其中一个后则不会记录,执行效果如下:

分析表

如果Msg_text显示的是OK,则代表这张表的键不存在问题,存在问题的情况我这边就不模拟了,后面举例聊。

检查表

语法如下:

check table 表名1,表名2... [检查选项];

分析、检查、优化、修复的命令都支持同时操作多张表,不同的表之间只需用,逗号隔开即可。检查命令有多个可选项,如下:

  • quick:不扫描行数据,不检查链接错误,仅检查表结构是否有问题。

  • fast:只检查表使用完成后,是否正确关闭了表文件的FD文件描述符。

  • changed:从上述检查过的位置开始,只检查被更改的表数据。

  • medium:检查行数据,收集每一行数据的键值(主键、外键…),并计算校验和,验证数据是否正确。

  • extended:对每行数据的所有字段值进行检查,检查完成后可确保数据100%正确。

先来看看执行结果吧,如下:

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

检查表

这回的结果出现了些许不同,Msg_text中出现了一个Error信息,提示咱们检查的zz_u表不存在,而对于一张存在的zz_users表,则返回OK,表示没有任何问题。

当然,这里对于其他的检查选项就不做测试了,大家可以自行实验,比如把表的结构文件或数据文件,在本地打开手动删除前面的一点点数据,然后再执行检查命令,其实你也可以观察到,提示“数据不完整”的信息(但需要先停止运行MySQL,并且用本地表测试,不要用线上表瞎搞)。

修复表

语法如下:

repair [local | no_write_to_binlog] table 表名 [quick] [extended] [use_frm];

值得一提的是,修复表的命令不支持InnoDB引擎,仅支持MyISAM、CSV、引擎,比如基于InnoDB引擎的表执行修复命令时,提示如下:

修复表

上述Msg_text信息翻译过来的意思是:选择的表其引擎并不支持修复命令。

InnoDB引擎其实也有修复机制,可以在my.ini/my.conf文件中加一行配置:[mysqld]innodb_force_recovery = 1,这样在启动时会强制恢复InnoDB的数据。

上述这个修复机制默认是不开启的,因为InnoDB不需要这个恢复机制,毕竟之前在《引擎篇》中聊过:InnoDB有完善的事务和持久化机制,客户端提交的事务都会持久化到磁盘,除非你人为损坏InnoDB的数据文件,否则基本上不会出现InnoDB数据损坏的情况。

优化表

语法如下:

optimize [local | no_write_to_binlog] table 表名;

这里值得一提的是:此优化非彼优化,并不意味着你的表存在性能问题,执行后它会自动调优,而是指清除老数据,执行效果如下:

优化表

还记得之前在《MVCC机制》中聊过的隐藏字段嘛?其实删除一条数据本质上并不会立马从磁盘移除,而是会先改掉隐藏的删除标识位,执行这条优化命令后,MySQL会将一些已经delete过的数据彻底从磁盘删除,从而释放这些“废弃数据”占用的空间。

上面的执行结果显示:“目前表的数据已经是最新的了”,这是啥原因呢?因为我这张表中压根没有数据,哈哈哈,没有插入过数据,自然也不会有删除数据的动作,因此就会出现这个提示。

OK~,到这里对于分析表、检查表、修复表以及优化表就已经介绍清楚啦!其实这几个功能,在mysqlcheck工具中也有提供。

1.5、MySQL忘记密码怎么办?

到这里,对于一些MySQL基础命令、库表命令就打住了,最后再来讲一个比较实用的知识点:**MySQL忘记密码怎么办**?对于这种情况其实也十分常见,哪忘记时该如何处理呢?可以重置密码!

①先停掉MySQL的后台服务:

  • Windows系统请执行:net stop mysql

  • Linux系统请执行:安装目录/mysql shutdownkill强杀进程也可以)

②进入到MySQL安装目录下的bin文件夹内,执行mysqld --skip-grant-tables去掉连接认证。

③因为上面关掉了连接认证,接着输入mysql敲下回车,进入mysql终端命令行。

④输入use mysql;,进入MySQL自身的系统数据库,然后输入show tables;查看所有表。

⑤查询MySQL中注册的所有用户:select user,host,password from user;

⑥使用update语句,更改root超级管理员的账号密码,如下:

update user set password=password('123') where user="root" and host="localhost";

因为MySQL本身会用一张用户表来存储所有已创建的账号信息,连接时的效验基准也是来自于该表中的数据,因此在这里修改密码后,再用新密码登录即可!

如果不是root账号的密码忘记了,则可以直接登录root账号修改其他用户的密码,如果是root账号则按照上述流程操作。

完成之后可以用mysql -uroot -p123连接一下,测试密码是否被重置。

二、增删改查语句

2.1、基本的增删改查语句
插入数据

增删改查俗称为CRUD,这也是MySQL运行之后执行次数最多的一类SQL语句,同时也是每位开发者写的最多的SQL语句,接下来则说说这块的语句,首先登场的是咱们的几位老伙伴,即insert、delete、update、select...这类普通SQL语句。

  • insert into 表名(字段名...) values(字段值...);:向指定的表中插入一条数据。

  • insert into 表名(字段名...) values(字段值...),(...)...;:向表中插入多条数据。

  • insert into 表名 set 字段名=字段值,...;:插入一条数据,但只插入某个字段的值。

如果要插入一条完整的数据,字段名可以用*代替所有字段,除开上述两种插入数据的基本方式外,还有几种批量插入的方式,如下:

-- 使用insert语句批量插入另一张表中查询的数据  
insert into 表名(字段名...) select 字段名... from 表名...;  
  
-- 使用replace语句来实现批量插入  
replace into 表名(字段名1,字段名2...) values(字段值....),(字段值...),...;

上述批量插入数据的方式中,还可以通过replace关键字来实现插入,它与insert有啥区别呢?答案在于它可以实现批量更新,使用replace关键字来插入数据的表必须要有主键,MySQL会根据主键值来决定新增或修改数据,当批量插入的数据中,主键字段值在表中不存在时,则会向表中插入一条相应的数据,而当插入数据中的主键值存在时,则会使用新数据覆盖原有的老数据。

删除数据
  • delete from 表名;:删除一张表的所有数据。

  • delete from 表名 where 条件;:根据条件删除一条或多条数据。

  • truncate table 表名:清空一张表的所有数据。

修改数据
  • update 表名 set 字段名=字段值,...;:修改表中所有记录的数据。

  • update 表名 set 字段名=字段值,... where 条件;:根据条件修改一条或多条记录的数据。

  • replace 表名(字段名1,...) values(字段值...),...;:批量修改对应主键记录的数据。

查询数据
  • select * from 表名;:查询一张表的所有数据。

  • select * from 表名 where 条件;:根据条件查询表中相应的数据。

  • select 字段1,字段2... from 表名 where 条件;:根据条件查询表中相应数据的指定字段。

  • select 函数(字段) from 表名;:对查询后的结果集,进行某个函数的特殊处理。

上述三种是最基本的查询方式,接着来看一些高级查询语法,如下:

-- 为查询出来的字段取别名  
select字段1as别名,...from表名where条件;  
select字段1别名,...from表名;  
  
-- 为查询出的表取别名  
select*from表名as别名;  
  
-- 以多条件查询数据  
select*from表名where字段1=值1and字段2=值2and...;-- 所有条件都符合时才匹配  
select*from表名where字段1=值1or字段2=值2or...;-- 符合任意条件的数据都会返回  
-- =符号,可以根据情况换为>、<、>=、<=、!=、between and、is null、not is null这些  
  
-- 对查询后的结果集使用函数处理  
select函数(字段)from表名where条件;  
  
-- 对查询条件使用函数处理  
select*from表名where函数(条件);  
  
-- 模糊查询  
select*from表名where字段like"%字符";-- 查询字段值以指定字符结尾的所有记录  
select*from表名where字段like"字符%";-- 查询字段值以指定字符开头的所有记录  
select*from表名where字段like"%字符%";-- 查询字段值包含指定字符的所有记录  
  
-- 按照多值查询对应行记录  
select*from表名where字段in(值1,值2,...);  
-- 按照多值查询相反的行记录  
select*from表名where字段notin(值1,值2,...);  
-- 基于多个字段做多值查询  
select*from表名where(字段1,字段2...)in((值1,值2,...),(...),...);  
  
-- 只需要查询结果中的前N条数据  
select*from表名 limit N;  
-- 返回查询结果中 N~M 区间的数据  
select*from表名 limit N,M;  
  
-- 联合多条SQL语句查询(union all表示不去重,union表示对查询结果去重)  
select*from表名where条件  
unionall  
select*from表名where 条件;

当然,对于MySQL中支持的函数稍后再展开聊,下面再聊聊一些其他的高级查询语法,如分组、过滤、子查询、关联查询等。

分组过滤、数据排序

SQL语句时,有些需求往往无法通过最基本的查询语句来实现,因此就需要用到一些高级的查询语法,例如分组、过滤、排序等操作,接着先聊聊这个,语法如下:

-- 基于一个字段进行排序查询  
select*from表名orderby字段名asc;-- 按字段值正序返回结果集  
select*from表名orderby字段名desc;-- 按字段值倒序返回结果集  
select*from表名orderby字段1asc,字段2desc;-- 按照多字段进行排序查询  
  
-- 基于字段进行分组  
select*from表名groupby字段1,字段2....;  
  
-- 基于分组查询后的结果做条件过滤  
select*from表名groupby字段1having 条件;

实际上group by、having这些语句,更多的要配合一些聚合函数使用,如min()、max()、count()、sum()、avg()....,这样才能更符合业务需求,但对于聚合函数后面再介绍,先简单说说where、having的区别:

这两个关键字都是用来做条件过滤的,但where优先级会比group by高,因此当分组后需要再做条件过滤时,就无法使用where来做筛选,而having就是用来对分组后的结果做条件过滤的。查询语句中的各类关键字执行优先级为:from → where → select → group by → having → order by

子查询

子查询也可以理解成是查询嵌套,是指一种由多条SQL语句组成的查询语句,语法如下:

-- 基于一条SQL语句的查询结果进一步做查询  
select*from(select*from表名where条件)as别名where条件;  
  
-- 将一条SQL语句的查询结果作为条件继续查询(只适用于子查询返回单值的情况)  
select*from表名where字段名=(select字段名from表名where条件);  
  
-- 将一条SQL语句的查询结果作为条件继续查询(适用于子查询返回多值的情况)  
select*from表名where字段名exists(select字段名from表名where条件);  
-- 上述的exists可以换为not exists,表示查询不包含相应条件的数据  
  
-- 将一条SQL语句的多个查询结果,作为条件与多个字段进行范围查询  
select*from表名where(字段1,字段2...)in(select字段1,字段2...from 表名);

在上述子查询语法中,exists的作用和in大致相同,只不过not in时会触发全表扫描,而not exists依旧可以走索引查询,因此通常情况下尽量使用not exists代替not in来查询数据。

关联查询

关联查询也被称之为连表查询,也就是指利用主外键连接多张表去查询数据,这几乎也是日常开发中写的最多的一类查询语句,MySQL中支持多种关联类型,如:

  • • 交叉连接

  • • 内连接

  • • 外连接:

  • • 左连接

  • • 右连接

  • • 全连接

语法如下:

-- 交叉连接:默认把前一张表的每一行数据与后一张表的所有数据做关联查询  
select*from表1,表2...;-- 这种方式默认采用交叉连接的方式  
select*from表1crossjoin表2;-- 显式声明采用交叉连接的方式  
  
-- 内连接:只返回两张表条件都匹配的数据  
-- 隐式的内连接写法  
select*from表1,表2...where表1.字段=表2.字段...;  
-- 等值内连接  
select*from表1别名1innerjoin表2别名2on别名1.字段=别名2.字段;  
-- 不等式内连接  
select*from表1别名1innerjoin表2别名2on别名1.字段<别名2.字段;  
  
-- 左外连接:左表为主,右表为次,无论左表在右表是否匹配,都返回左表数据,缺失的右表数据显示NULL  
select*from表1leftjoin表2on表1.字段=表2.字段;  
  
-- 右外连接:和左连接相反,右表为主,左表为次,永远返回右表的所有数据  
select*from表1rightjoin表2on表1.字段=表2.字段;  
  
-- 全外连接:两张表没有主次之分,每次查询都会返回两张表的所有数据,不匹配的显示NULL  
-- MySQL中不支持全连接语法,只能通过union all语句,将左、右连接查询拼接起来实现  
select*from表1leftjoin表2on表1.字段=表2.字段  
unionall  
select*from表1rightjoin表2on表1.字段=表2.字段;  
  
-- 继续拼接查询两张以上的表  
select*from表1leftjoin表2on表1.字段=表2.字段leftjoin表3on表2.字段=表3.字段;  
-- 通过隐式连接的方式,查询两张以上的表  
select*from表1,表2,表3...where表1.字段=表2.字段and表1.字段=表3.字段...;  
-- 通过子查询的方式,查询两张以上的表  
select*from  
(表1as别名1leftjoin表2as别名2on别名1.字段=别名2.字段)  
leftjoin  
表3as别名3on别名1.字段=别名3.字段;

对于连表查询的语法相信大家都并不陌生,因此不做过多产生,重点讲一下多表联查时的笛卡尔积问题,所谓的笛卡尔积问题就是指两张表的所有数据都做关联查询,一般连表查询都需要指定连接的条件,但如果不指定时,MySQL默认会将左表每一条数据挨个和右表所有数据关联一次,然后查询一次数据。比如左表有3条数据,右表有4条数据,笛卡尔积情况出现时,一共就会查询出3 x 4 = 12条数据。

笛卡尔积现象出现时,会随着表数据增长越来越大,因此在连表查询时一定要消除笛卡尔积问题,咋消除呢?其实就是指定加上关联条件即可。

至此,对于一些增删改查的基本语句就已介绍清楚啦~,但实际业务开发过程中,往往会结合数据库所提供的函数一起操作,因此接下来再聊聊MySQL支持的函数。

三、MySQL数据库函数

MySQL中提供了丰富的函数支持,包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等,通过这些函数,一方面可以简化业务的代码量,另一方面还能更好的实现各类特殊业务需求,下来一起来聊聊MySQL支持的函数。

3.1、数学函数

数学函数是MySQL中最常用的一类函数,主要用来处理所有数值类型的字段值,一起来看看。

  • abs(X):返回X的绝对值,如传进-1,则返回1

  • ln(X):返回X的自然相对数。

  • log(X,Y):返回以X的以Y为底的对数。

  • log10(X):返回以X基数为10的对数。

  • bin(X):返回X的二进制值。

  • oct(X):返回X的八进制值。

  • hex(X):返回X的十六进制值。

  • mod(X,Y):返回X除以Y的余数。

  • ceil(X) | ceiling(X):返回不小于X的最小整数,如传入1.23,则返回2

  • round(X):返回X四舍五入的整数。

  • floor(X):返回X向下取整后的值,如传入2.34,会返回2

  • greatest(X1,X2....,Xn):返回集合中的最大整数值。

  • least(X1,X2....,Xn):返回集合中的最小整数值。

  • rand(N):返回一个0~N``0~1之间的随机小数(不传参默认返回0~1之间的随机小数)。

  • sign(X):传入正数,返回1;传入负数,返回-1;传入0,返回0

  • pow(X,Y) | power(X,Y):返回XY次方值。

  • pi():返回四舍五入后的圆周率,3.141593

  • sin(X):返回X的正弦值。

  • asin(X):返回X的反正弦值。

  • cos(X):返回X的余弦值。

  • acos(X):返回X的反余弦值。

  • tan(X):返回X的正切值。

  • atan(X):返回X的反正切值。

  • cot(X):返回X的余切值。

  • radians(x):返回x由角度转化为弧度的值。

  • degrees(x):返回x由弧度转化为角度的值。

  • sqrt(X):返回X的平方根。

  • exp(e,X):返回ex乘方的值。

  • truncate(X,N):返回小数X保留N位精准度的小数。

  • format(x,y):将x格式化位以逗号隔开的数字列表,y是结果的小数位数。

  • inet_aton(ip):将IP地址以数字的形式展现。

  • inet_ntoa(number):显示数字代表的IP地址。

  • ......

3.2、字符串函数
  • ascii(C):返回字符CASCII码。

  • length(S):返回字符串的占位空间,传入“竹子爱熊猫”,返回15,一个汉字占位3字节。

  • bit_length(S):返回字符串的比特长度。

  • concat(S1,S2,...):合并传入的多个字符串。

  • concat_wa(sep,S1,S2...):合并传入的多个字符串,每个字符串之间用sep间隔。

  • position(str,s) | locate(str,s):返回sstr中第一次出现的位置,没有则返回0

  • find_in_set(S,list):返回字符串Slist列表中的位置。

  • insert(S1,start,end,S2):使用S2字符串替换掉S1字符串中start~end的内容。

  • lcase(S) | lower(S):将传入的字符串中所有大写字母转换为小写。

  • ucase(S) | upper(S):将传入的字符串中所有小写字母转换为大写。

  • left(S,index):从左侧开始截取字符串Sindex个字符。

  • right(S,index):从右侧开始截取字符串Sindex个字符。

  • trim(S):删除字符S左右两侧的空格。

  • rtrim(S):删除字符S右侧的空格。

  • replace(S,old,new):使用new新字符替换掉S字符串中的old字符。

  • repeat(str,count):将str字符串重复count次后返回。

  • substring(S,index,N):截取S字符串,从index位置开始,返回长度为N的字符串。

  • reverse(S):将传入的字符串反转,即传入Java,返回avaJ

  • quote(str):用反斜杠转移str中的英文单引号。

  • strcmp(S1,S2):比较两个字符是否相同。

  • lpad(str,len,s):对str字符串左边填充lens字符。

  • rpad(str,len,s):对str字符串右边填充lens字符。

  • ......

3.3、日期和时间函数
  • curdate() | current_date():返回当前系统的日期,如2022-10-21

  • curtime() | current_time():返回当前系统的时间,如17:30:52

  • now() | sysdate():返回当前系统的日期时间,如2022-10-21 17:30:59

  • unix_timestamp():获取一个数值类型的unix时间戳,如1666348711

  • from_unixtime():将unix_timestamp()获取的数值时间戳,格式化成日期格式。

  • month(date):获取date中的月份。

  • year(date):获取date中的年份。

  • hour(date):获取date中的小时。

  • minute(date):获取date中的分钟。

  • second(date):获取date中的秒数。

  • monthname(date):返回date中月份的英文名称。

  • dayname(date):获取日期date是星期几,如Friday

  • dayofweek(date):获取date位于一周的索引位置,周日是1、周一是2…周六是7

  • week(date):获取date是本年的第多少周。

  • quarter(date):获取date位于一年中的哪个季度(1~4)。

  • dayofyear(date):获取date是本年的第多少天。

  • dayofmonth(date):获取date是本月的第多少天。

  • time_to_sec(time):将传入的时间time转换为秒数,比如"01:00:00" = 3600s

  • date_add(date,interval 时间 单位) | adddate(...):将date与给定的时间按单位相加。

  • date_sub(date,interval 时间 单位) | subdate(...):将date与给定的时间按单位相减。

  • addtime(date,time):将date加上指定的时间,如addtime(now(),"01:01:01")

  • subtime(date,time):将date减去指定的时间。

  • datediff(date1,date2):计算两个日期之间的间隔天数。

  • last_day(date):获取date日期这个月的最后一天。

  • date_format(date,format):将一个日期格式化成指定格式,format可选项如下:

  • %a:工作日的英文缩写(Sun~Sat)。

  • %b:月份的英文缩写(Jan~Dec)。

  • %c:月份的数字格式(1~12)。

  • %M:月份的英文全称(January~December)。

  • %D:带有英文后缀的数字月份(1th、2st、3nd....)。

  • %d:一个月内的天数,双数形式(01、02、03....31)。

  • %e:一个月内的天数,单数形式(1、2、3、4....31)。

  • %f:微妙(000000~999999)。

  • %H:一天内的小时,24小时的周期(00、01、02...23)。

  • %h | %I:一天内的小时,12小时的周期(01、02、03...12)。

  • %i:一小时内的分钟(00~59)。

  • %j:一年中的天数(001~366)。

  • %k:以24小时制显示时间(00~23)。

  • %l:以12小时制显示时间(01~12)。

  • %m:月份的数字形式,双数形式(01~12)。

  • %p:一天内的时间段(上午AM、下午PM)。

  • %r12小时制的时间(12:01:09 AM)。

  • %S | %s:秒数,双数形式(00~59)。

  • %T24小时制的时间(23:18:22)。

  • %U:一年内的周(00~53)。

  • time_format(time,format):将一个时间格式化成指定格式。

  • str_to_date(str,format):将日期字符串,格式化成指定格式。

  • timestampdiff(unit,start,end):计算两个日期之间间隔的具体时间,unit是单位:

  • year:年。

  • quarter:季度。

  • month:月。

  • week:周。

  • day:天。

  • hour:小时。

  • minute:分钟。

  • second:秒数。

  • microsecond:微妙。

  • weekday(date):返回date位于一周内的索引(0是周一…6是周日)。

3.4、聚合函数

聚合函数一般是会结合select、group by having筛选数据使用。

  • max(字段名):查询指定字段值中的最大值。

  • min(字段名):查询指定字段值中的最小值。

  • count(字段名):统计查询结果中的行数。

  • sum(字段名):求和指定字段的所有值。

  • avg(字段名):对指定字段的所有值,求出平均值。

  • group_concat(字段名):返回指定字段所有值组合成的结果,如下:

  • distinct(字段名):对于查询结果中的指定的字段去重。

这里稍微介绍一个日常业务中碰到次数较多的需求:

select *from zz_users;  
+---------+-----------+----------+----------+---------------------+  
| user_id | user_name | user_sex | password | register_time       |  
+---------+-----------+----------+----------+---------------------+  
|1|熊猫|女|6666|2022-08-1415:22:01|  
|2|竹子|男|1234|2022-09-1416:17:44|  
|3|子竹|男|4321|2022-09-1607:42:21|  
|4|黑熊|男|8888|2022-09-1723:48:29|  
|8|猫熊|女|8888|2022-09-2717:22:29|  
|9|棕熊|男|0369|2022-10-1723:48:29|  
+---------+-----------+----------+----------+---------------------+  
  
-- 基于性别字段分组,然后显示各组中的所有ID  
select  
convert(  
        group_concat(user_id orderby user_id asc separator ",")  
using utf8)as"分组统计"  
from`zz_users`groupby user_sex;  
+-------------+  
|分组统计|  
+-------------+  
|1,8|  
|2,3,4,9|  
+-------------+

上述利用了group_concat()、group by实现了按照一个字段分组后,显示对应分组的所有ID

3.5、控制流程函数
  • if(expr,r1,r2)expr是表达式,如果成立返回r1,否则返回r2

  • ifnull(v,r):如果v不为null则返回v,否则返回r

  • nullif(v1,v2):如果v1 == v2,则返回null,如果不相等则返回V1

-- if的用例  
selectif(user_id >3,"√","×")from zz_users;  
  
-- ifnull的用例  
select ifnull(user_id,"×")from zz_users;  
  
-- case语法1:  
case<表达式>  
when<值1>then<操作>  
when<值2>then<操作>  
...  
else<操作>  
end;  
-- 用例:判断当前时间是星期几  
selectcase weekday(now())  
when0then'星期一'  
when1then'星期二'  
when2then'星期三'  
when3then'星期四'  
when4then'星期五'  
when5then'星期六'  
else'星期天'  
endas"今天是星期几?";  
  
-- case语法2:  
case  
when<条件1>then<命令>  
when<条件2>then<命令>  
...  
else commands  
end;  
-- 用例:判断今天是星期几  
selectcase  
when weekday(now())=0then'星期一'  
when weekday(now())=1then'星期二'  
when weekday(now())=2then'星期三'  
when weekday(now())=3then'星期四'  
when weekday(now())=4then'星期五'  
when weekday(now())=5then'星期六'  
else'星期天'  
endas"今天是星期几?";

简单聊一下CASE语法,第一种语法就类似于Java中的switch,而第二种语法就类似于多重if,通过CASE语法能够让SQL更加灵活,完成类似于存储过程的工作。

3.6、加密函数
  • password(str):将str字符串以数据库密码的形式加密,一般用在设置DB用户密码上。

  • md5(str):对str字符串以MD5不可逆算法模式加密。

  • encode(str,key):通过key密钥对str字符串进行加密(对称加密算法)。

  • decode(str,key):通过key密钥对str字符串进行解密。

  • aes_encrypt(str,key):通过key密钥对str字符串,以AES算法进行加密。

  • aes_decrypt(str,key):通过key密钥对str字符串,以AES算法进行解密。

  • sha(str):计算str字符串的散列算法校验值。

  • encrypt(str,salt):使用salt盐值对str字符串进行加密。

  • decrypt(str,salt):使用salt盐值对str字符串进行解密。

3.7、系统函数
  • version():查询当前数据库的版本。

  • connection_id():返回当前数据库连接的ID

  • database() | schema():返回当前连接位于哪个数据库,即use进入的库。

  • user():查询当前的登录的所有用户信息。

  • system_user():返回当前登录的所有系统用户信息。

  • session_user():查询所有连接的用户信息。

  • current_user():查询当前连接的用户信息。

  • charset(str):返回当前数据库的编码格式。

  • collation(str):返回当前数据库的字符排序规则。

  • benchmark(count,expr):将expr表达式重复运行count次。

  • found_rows():返回最后一个select查询语句检索的数据总行数。

  • cast(v as 类型):将v转换为指定的数据类型。

3.8、…

除开上述这些函数之外,其实在MySQL还有很多很多的函数,但目前几乎已经将所有常用的函数全部列出来了,因此对于其他偏冷门一些的函数就不再介绍。当然,就算你需要的某个功能在MySQL中没有提供函数支持,你也可以通过create function的方式自定义存储函数,其逻辑与上篇讲到的《MySQL存储过程》大致相同。

四、MySQL支持的数据类型

这里所谓的数据类型,也就是只在创建表时可以选择的列字段类型,在MySQL中其实可以通过:

  • help data types;:查看当前版本支持的所有数据类型。如下(MySQL5.1版本):

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

数据类型

总体可分为数值类型、字符/串类型、时间/日期类型、其他类型四种,下面一起来聊聊吧。

4.1、数值类型
  • tinyint:小整数类型,占位1Bytes,取值范围-128~127

  • smallint:中整数类型,占位2Bytes,取值范围-32768~32767

  • mediumint:中大整数类型,占位3Bytes,取值范围-8388608~8388607

  • int | integer:常用整数类型,占位4Bytes,取值范围-2147483548~2147483647

  • bigint:超大整数类型,占位8Bytes,取值范围-9223372036854775808~9223372036854775807

  • float:单精度浮点数类型,占位4Bytes,取值范围-3.4E+38 ~ 3.4E+38

  • double:双精度浮点数类型,占位8Bytes,取值范围-1.7E-308~1.7E+308

  • decimal(m,d):小数类型,占位和取值范围都依赖m、d值决定,m是小数点后面的精度,d是小数点前面的标度。

  • bit(m):存储位值,可存储m个比特位,取值范围是1~64

4.2、字符串类型
  • char:定长字符串类型,存储空间0~255Bytes

  • varchar:变长字符串类型,存储空间0~65535Bytes

  • tinyblob:二进制短字符串类型,存储空间0~255Bytes

  • tinytext:短文本字符串类型,存储空间0~255Bytes

  • blob:二进制长字符串类型,存储空间0~65535Bytes

  • text:长文本字符串类型,存储空间0~65535Bytes

  • mediumblob:二进制大字符串类型,存储空间0~16777215Bytes

  • mediumtext:大文本字符串类型,存储空间0~16777215Bytes

  • longblob:二进制超大字符串类型,存储空间0~4294967295Bytes

  • longtext:超大文本字符串类型,存储空间0~4294967295Bytes

  • binary(m):定长字符串类型,存储空间为M个字符。

  • varbinary(m):定长字符串类型,存储空间为M个字符+1个字节。

一般在为列指定数据类型时,都会varchar(255)这样写,其实中间的这个数字限制的并不是字节长度,而是字符数量,比如varchar(255),表示该列最大能存储255个字符。

4.3、时间/日期类型
  • date:日期类型,占位3Bytes,格式为YYYY-MM-DD

  • time:时间类型,占位3Bytes,格式为hh:mm:ss

  • year:年份类型,占位1Bytes,格式为YYYY

  • datetime:日期时间类型,占位8Bytes,格式为YYYY-MM-DD hh:mm:ss

  • timestamp:时间戳类型,占位4Bytes,格式为YYYYMMDDhhmmss,最大可精确到微妙。

4.4、其他类型
  • jsonMySQL5.7版本引入的,在此之前只能用字符串类型来存储json数据,需要通过函数辅助使用:

  • json_array(...):存储一个json数组的数据。

  • json_array_insert(字段,'$[下标]',"值"):在指定的json数组下标位置上插入数据。

  • json_object(...):存储一个json对象。

  • json_extract(字段,'$.键'):查询键为某个值的所有数据。

  • json_search(....):通过值查询键。

  • json_keys(字段):获取某个字段的所有json键。

  • json_set(字段,'$.键',"值"):更新某个键的json数据。

  • json_replace(...):替换某个json中的数据。

  • json_remove(字段,'$.键'):删除某个json数据。

  • .....:还有一些其他json类型的函数,这里不再说明,一般json类型用的较少。

  • enum(选项1,选项2...选项n):新增数据时只能从已有的选项中选择一个并插入。

  • set(选项1,选项2...选项n):新增数据时可以从已有的选项中选择多个并插入。

  • eometry、point、linestring、polygon:空间类型(接触不多)。

稍微解释一下enum、set类型,这两种类型就类似于平时的单选框和多选框,必须从已有的选项中选择,两者的区别在于:enum枚举类型只能选择一个选项,而set集合类型可以选择多个选项(其实用的比较少,多数情况下都是直接在客户端中处理)。

OK~,简单了解MySQL支持的数据类型后,接着再来看看其他一些常用的SQL命令。

五、索引相关的命令

-- 创建一个普通索引(方式①)  
create index 索引名ON表名(列名(索引键长度)[ASC|DESC]);  
-- 创建一个普通索引(方式②)  
altertable表名add index 索引名(列名(索引键长度)[ASC|DESC]);  
-- 创建一个普通索引(方式③)  
CREATETABLE tableName(  
  columnName1 INT(8)NOTNULL,  
  columnName2 ....,  
.....,  
  index [索引名称](列名(长度))  
);  
-- 后续其他类型的索引都可以通过这三种方式创建  
  
  
-- 创建一个唯一索引  
createunique索引名ON表名(列名(索引键长度)[ASC|DESC]);  
  
-- 创建一个主键索引  
altertable表名addprimary key 索引名(列名);  
  
-- 创建一个全文索引  
create fulltext index 索引名ON表名(列名);  
  
-- 创建一个前缀索引  
create index 索引名ON表名(列名(索引键长度));  
  
-- 创建一个空间索引  
altertable表名add spatial key 索引名(列名);  
  
-- 创建一个联合索引  
create index 索引名ON表名(列名1(索引键长度),列名2,...列名n);

上面将MySQL中创建各类索引的多种方式都列出来了,接着再聊聊索引查看、使用与管理的命令。

-- 查看一张表上的所有索引  
show index from表名;  
  
-- 删除一张表上的某个索引  
drop index 索引名on表名;  
  
-- 强制指定一条SQL走某个索引查找数据  
select*from表名 force index(索引名)where.....;  
  
-- 使用全文索引(自然搜索模式)  
select*from表名wherematch(索引列) against('关键字');  
-- 使用全文索引(布尔搜索模式)  
select*from表名wherematch(索引列) against('布尔表达式'inboolean mode);  
-- 使用全文索引(拓展搜索模式)  
select*from表名wherematch(索引列) against('关键字'with query expansion);  
  
-- 分析一条SQL是否命中了索引  
explain select*from表名where 条件....;

六、事务与锁相关的命令

  • start transaction; | begin; | begin work;:开启一个事务。

  • commit;:提交一个事务。

  • rollback;:回滚一个事务。

  • savepoint 事务点名称;:添加一个事务点。

  • rollback to 事务点名称;:回滚到指定名称的事务点。

  • release savepoint 事务点名称;:删除一个事务点。

  • select @@tx_isolation;:查询事务隔离级别(方式一)。

  • show variables like '%tx_isolation%';:查询事务隔离级别(方式二)。

  • set transaction isolation level 级别:设置当前连接的事务隔离级别。

  • set @@tx_isolation = "隔离级别";:设置当前会话的事务隔离级别。

  • set global transaction isolation level 级别;:设置全局的事务隔离级别,选项如下:

  • read uncommitted:读未提交级别。

  • read committed:读已提交级别。

  • repeatable-read:可重复读级别。

  • serializable:序列化级别。

  • show variables like 'autocommit';:查看自动提交事务机制是否开启。

  • set @@autocommit = 0|1|ON|OFF;:开启或关闭事务的自动提交。

  • select ... lock in share mode;:手动获取共享锁执行SQL语句。

  • select ... for share;MySQL8.0之后优化版的共享锁写法。

  • select ... for update;:手动获取排他锁执行。

  • lock tables 表名 read;:获取表级别的共享锁。

  • lock tables 表名 write;:获取表级别的排他锁。

  • show open tables where in_use > 0;:查看目前数据库中正在使用的表锁。

  • flush tables with read lock;:获取全局锁。

  • unlock tables;:释放已获取的表锁/全局锁。

  • update 表名 set version=version+1 ... where... and version=version;:乐观锁模式执行。

七、存储过程、存储函数与触发器

-- 创建一个存储过程  
DELIMITER $  
CREATE  
PROCEDURE存储过程名称(返回类型参数名1参数类型1,....)  
[......]  
BEGIN  
-- 具体组成存储过程的SQL语句....  
END $  
DELIMITER ;  
  
-- 创建一个存储函数  
DELIMITER $  
CREATE  
FUNCTION存储函数名称(参数名1参数类型1,....)  
RETURNS数据类型  
[NOT]DETERMINISTIC statements  
BEGIN  
-- 具体组成存储函数的SQL语句....  
END $  
DELIMITER ;  
  
-- 创建一个触发器  
CREATETRIGGER触发器名称  
{BEFORE | AFTER}{INSERT|UPDATE|DELETE}ON表名  
FOREACHROW  
-- 触发器的逻辑(代码块);  
  
-- ------------- 用户变量与局部变量 ---------------  
-- 定义、修改用户变量  
set@变量名称=变量值;  
-- 查询用户变量  
select@变量名称;  
  
-- 定义局部变量  
DECLARE变量名称数据类型default默认值;  
-- 为局部变量赋值(方式1)  
SET变量名=变量值;  
-- 为局部变量赋值(方式2)  
SET变量名:=变量值;  
-- 为局部变量赋值(方式3)  
select查询结果字段into变量名from表名;  
  
-- ------------- 流程控制 ---------------  
-- if、elseif、else条件分支语法  
IF 条件判断THEN  
-- 分支操作.....  
ELSEIF 条件判断 THWN  
-- 分支操作.....  
ELSE  
-- 分支操作.....  
END IF  
  
-- case分支判断语句  
-- 第一种语法  
CASE变量  
WHEN值1THEN  
-- 分支操作1....  
WHEN值2THEN  
-- 分支操作2....  
.....  
ELSE  
-- 分支操作n....  
ENDCASE;  
  
-- 第二种语法  
CASE  
WHEN条件判断1THEN  
-- 分支操作1....  
WHEN条件判断2THEN  
-- 分支操作2....  
.....  
ELSE  
-- 分支操作n....  
ENDCASE;  
  
-- 循环:LOOP、WHILE、REPEAT  
-- loop循环  
循环名称:LOOP  
-- 循环体....  
END LOOP 循环名称;  
  
-- while循环  
【循环名称】:WHILE 循环条件 DO  
-- 循环体....  
END WHILE 【循环名称】;  
  
-- repeat循环  
【循环名称】:REPEAT  
-- 循环体....  
UNTIL 结束循环的条件判断  
END REPEAT 【循环名称】;  
  
-- 循环跳转  
LEAVE 【循环名称】;-- 结束某个循环体  
ITERATE 【循环名称】;-- 跳出某个循环体,继续下次循环  
  
-- ------------- 存储过程的游标 ---------------  
-- ①声明(创建)游标  
DECLARE游标名称CURSORFORselect...;  
  
-- ②打开游标  
OPEN游标名称;  
  
-- ③使用游标  
FETCH游标名称INTO变量名称;  
  
-- ④关闭游标  
CLOSE 游标名称;

在上面列出了MySQL中存储过程、存储函数与触发器的相关语法,接着再来聊聊管理的命令:

  • SHOW PROCEDURE STATUS;:查看当前数据库中的所有存储过程。

  • SHOW PROCEDURE STATUS WHERE db = '库名' AND NAME = '过程名';:查看指定库中的某个存储过程。

  • SHOW CREATE PROCEDURE 存储过程名;:查看某个存储过程的源码。

  • ALTER PROCEDURE 存储过程名称 ....:修改某个存储过程的特性。

  • DROP PROCEDURE 存储过程名;:删除某个存储过程。

  • SHOW FUNCTION STATUS;:查看当前数据库中的所有存储函数。

  • SHOW CREATE FUNCTION 存储过程名;:查看某个存储函数的源码。

  • ALTER FUNCTION 存储过程名称 ....:修改某个存储函数的特性。

  • DROP FUNCTION 存储过程名;:删除某个存储函数。

  • SHOW TRIGGERS;:查看当前数据库中定义的所有触发器。

  • SHOW CREATE TRIGGER 触发器名称;:查看当前库中指定名称的触发器。

  • SELECT * FROM information_schema.TRIGGERS;:查看MySQL所有已定义的触发器。

  • DROP TRIGGER IF EXISTS 触发器名称;:删除某个指定的触发器。

当然,如若你对这块感兴趣,详细的教程可参考上篇:《MySQL存储过程与触发器》。

八、MySQL用户与权限管理

  • create user 用户名@'IP' identified by 密码;:创建一个新用户。

  • drop user 用户名@'IP';:删除某个用户。

  • set password = password(新密码);:为当前用户设置新密码。

  • set password for 用户名 = password(新密码);:为指定用户设置新密码(需要权限)。

  • alter user 用户名@'IP' identified by 新密码;:使用root账号修改密码。

  • mysqladmin -u用户名 -p旧密码 password 新密码;:使用mysqladmin工具更改用户密码。

  • rename user 原用户名 to 新用户名;:对某个用户重命名。

  • show grants;:查看当前用户拥有的权限。

  • show grants for 用户名;:查看指定用户拥有的权限。

  • grant 权限1,权限2... on 库名.表名 to 用户名;:为指定用户授予权限。

  • *.*:全局权限,表示该用户可对所有库、所有表进行增删改查操作。

  • 库名.*:单库权限,表示该用户可对指定库下的所有表进行增删改查操作。

  • 库名.表名:单表权限,表示该用户可对指定表进行增删改查操作。

  • insert:插入表数据的权限。

  • delete:删除表数据的权限。

  • update:修改表数据的权限。

  • select:查询表数据的权限。

  • alter:修改表结构的alter权限。

  • alter routine:修改子程序(存储过程、函数、触发器)的alter权限。

  • create:创建表的create权限。

  • create routine:创建存储过程、存储函数、触发器的权限。

  • create temporary tables:创建临时表的权限。

  • create user:创建/删除/重命名/授权用户的权限。

  • create view:创建视图的权限。

  • drop:删除表的权限。

  • execute:执行存储过程的权限。

  • file:导出、导入表数据的权限。

  • index:创建和删除索引的权限。

  • lock tables:获取表锁的权限。

  • process:查询工作线程的权限。

  • references:这个在MySQL中没有。

  • reload:请空表的权限。

  • replication clinet:获取主节点、从节点地址的权限。

  • replication slave:复制主节点数据的权限。

  • show databases:查看所有数据库的权限。

  • show view:查看所有视图的权限。

  • shutdown:关闭数据库服务的权限。

  • super:修改主节点信息的权限。

  • all privileges:所有权限。

  • • 权限可选项:

  • usage:不授予这些权限。其他权限全部授予。

  • grant option:授予这些权限,其他权限全部不授予。

  • • 权限范围可选项:

  • revoke 权限1,权限2... on 库名.表名 from 用户名;:撤销指定用户的指定权限。

  • revoke all privileges from 用户名 with grant option;:撤销一个用户的所有权限。

  • flush privileges;:刷新权限。

  • select user,password,host from mysql.user;:查询当前库中的所有用户信息。

  • MySQL8.0版本后推出的密码管理机制:

  • set persist default_password_lifetime=90;:设置所有用户的密码在90天后失效。

  • create user 用户@IP password expire interval 90 day;:创建用户时设置失效时间。

  • alter user 用户名@IP password expire interval 90 day;:设置指定用户密码失效。

  • alter user 用户名@IP password expire never;:设置指定用户的密码永不失效。

  • alter user 用户名@IP password expire default;:使用默认的密码失效策略。上述给出了一系列的用户管理和权限管理的命令,最后稍微提一下创建用户时的注意事项:```sql

– 创建一个名为 zhuzi 的用户 create user ‘zhuzi’@‘196.xxx.xxx.xxx’ identified by “123456”;

在创建用户时需要在用户名称后面跟一个`IP`地址,这个`IP`的作用是用来限制登录用户的机器,如果指定为具体`IP`,则表示只能由该`IP`的机器登录该用户,如果写`%`表示任意设备都能使用该用户名登录连接。  
>同时也最后提一嘴,`MySQL`对于所有的用户信息,都会放在自带的`mysql`库的`user`表中存储,因此也可以对表执行`insert、delete、update、select`操作,来实现管理用户的功能。  
  
## 九、MySQL视图与临时表  
-`create view 视图名 as select ...;`:对查询出的结果集建立一个指定名称的视图。  
-`select * from 视图名;`:基于某个已经创建的视图查询数据。  
-`show create view 视图名;`:查看某个已存在的视图其详细信息。  
-`desc 视图名;`:查看某个视图的字段结构。  
-`alter view 视图名(字段1,...) as select 字段1...;`:修改某个视图的字段为查询字段。  
-`drop view 视图名;`:删除某个视图。  
-`create temporary table 表名(....);`:创建一张临时表(方式1)。  
-`create temporary view 表名 as select ...;`:创建一张临时表(方式2)。  
-`truncate table 临时表名;`:清空某张临时表的数据。  
  
`MySQL`的临时表本质上是一种特殊的视图,被称为不可更新的视图,也就是临时表只支持查询数据,不支持增删改操作,因此也可以通过创建视图的方式创建临时表,在创建语句中加入`temporary`关键字即可,不指定默认为`undedined`,意思是自动选择视图结构,一般为`merge`结构,表示创建一个支持增删改查的视图。  
  
## 十、数据的导出、导入与备份、还原  
数据库的备份其实本质上就是指通过导出数据的形式,或者拷贝表文件的方式来制作数据的副本,数据恢复/还原即是指在数据库故障、异常、错误的情况下,通过导入原本的数据副本,将数据恢复到正常状态,下面来介绍`MySQL`中提供的相关命令。  
```sql  
-- --------使用 mysqldump 工具做数据的逻辑备份(导出的是sql语句)-----------  
-- 导出MySQL中全部的库数据(使用--all-databases 或者 -A 参数)  
mysqldump -uroot -p密码 --all-databases > 备份文件名.sql  
  
-- 导出MySQL中一部分的库数据(使用--databases 或者 -B 参数)  
mysqldump -uroot -p密码 --databases > 备份文件名.sql  
  
-- 导出MySQL单库中的一部分表数据  
mysqldump –u 用户名 –h主机名 –p密码 库名[表名1,表名2...]> 备份文件名.sql  
  
-- 导出MySQL单表的部分数据(使用 --where 参数)  
mysqldump -u用户名 -p 库名 表名 --where="条件" > 备份文件名.sql  
  
-- 排除某些表,导出库中其他的所有数据(使用 --ignore-table 参数)  
mysqldump -u用户名 -p 库名 --ignore-table=表名1,表名2... > 备份文件名.sql  
  
-- 只导出表的结构(使用 --no-data 或者 -d 选项)  
mysqldump -u用户名 -p 库名 --no-data > 备份文件名.sql  
  
-- 只导出表的数据(使用 --no-create-info 或者 -t 选项)  
mysqldump -u用户名 -p 库名 --no-create-info > 备份文件名.sql  
  
-- 导出包含存储过程、函数的库数据(使用--routines 或者 -R选项)  
mysqldump -u用户名 -p -R --databases 库名 > 备份文件名.sql  
  
-- 导出包含事件(触发器)的库数据(使用 --events 或者 -E选项)  
mysqldump -u用户名 -p -E --databases 库名 > 备份文件名.sql  
  
  
-- --------使用 mysql 工具来恢复备份的数据(导入xx.sql文件执行)-----------  
-- 恢复库级别的数据(包含了建库语句的情况下使用)  
mysql -u用户名 -p < xxx.sql  
  
-- 恢复库中表级别的数据  
mysql -u用户名 -p 库名 < xxx.sql  
  
  
-- ----------以物理形式备份数据(导出的是表数据) ------------  
-- 查看数据库导出数据的路径(如果没有则需在`my.ini/my.conf`中配置)  
show variables like '%secure_file_priv%';  
  
-- 导出一张表的数据为txt文件(使用 select ... into outfile 语句)  
select * from 表名 into outfile "备份文件名.txt";  
  
-- 导出一张表的数据为txt文件(使用 mysql 工具)  
mysql -u用户名 -p --execute="select ...;" 库名 > "数据存放目录/xxx.txt"  
  
-- 导出一张表的结构和数据为sql、txt文件(使用 mysqldump -T 的方式)  
mysqldump -u用户名 -p -T "数据存放目录" 库名 文件名  
  
-- 导出一张表的数据为txt文件,以竖排形式存储(使用 mysql –veritcal 的方式)  
mysql -u用户名 -p -veritcal --execute="select ...;" 库名 > "数据存放目录/xxx.txt"  
  
-- 导出一张表的数据为xml文件(使用 mysql -xml 的方式)  
mysql -u用户名 -p -xml --execute="select ...;" 库名 > "数据存放目录/xxx.xml"  
  
  
-- -----------通过物理数据文件恢复数据----------------  
-- 使用load data infile 的方式导入.txt 物理数据  
load data infile "数据目录/xxx.txt" into table 库名.表名;  
  
-- 使用 mysqlimport 工具导入xxx.txt物理数据  
mysqlimport -u用户名 -p 库名 '数据存放目录/xxx.txt'   
    --fields-terminatedby=','   
    --fields-optionally-enclosed-by='\"'  
  
-- 使用 mysqldump 工具迁移数据  
mysqldump –h 地址1 –u用户名 –p密码 –-all-databases | mysql –h地址2 –u用户名 –p密码

上述列出了一系列数据导出导入、备份恢复、迁移等命令,这些都是MySQL自身就支持的方式,但这些自带的命令或工具,在一些情况下往往没有那么灵活、方便,因此在实际情况下,可以适当结合第三方工具来完成,比如:

  • • 较大的数据需要做物理备份时,可以通过xtrabackup备份工具来完成。

  • MySQL5.5版本之前的MyISAM表,可以通过MySQLhotcopy工具做逻辑备份(速度最快)。

  • • 不同版本的MySQL可以使用XtraBackup备份工具来做数据迁移。

  • MySQL、Oracle之间可以通过MySQL Migration Toolkit工具来做数据迁移。

  • MySQL、SQL Server之间可以通过MyODBC工具来做数据迁移。

当然,无论是MySQL自身提供的工具也好,亦或是第三方提供的工具也罢,因为本身就写死了逻辑,因此在有些场景下依旧存在局限性,因此有时咱们也需要写自动化脚本,以此来完成一些特殊的需求。

十一、表分区相关的命令

-- 创建范围分区  
createtable`表名`(  
`xxx` xxx notnull,  
....  
)  
partitionbyrange(xxx)(  
partition分区名1values less than (范围) data directory ="/xxx/xxx/xxx",  
partition分区名2values less than (范围) data directory ="/xxx/xxx/xxx",  
......  
);  
  
-- 创建枚举分区  
createtable`表名`(  
`xxx` xxx notnull,  
....  
)  
partitionby list(xxx)(  
partition分区名1valuesin(枚举值1,枚举值2...),  
partition分区名2valuesin(枚举值),  
......  
);  
  
-- 创建常规哈希分区  
createtable`表名`(  
`xxx` xxx notnull,  
....  
)  
partitionby hash(xxx)  
partitions 分区数量;  
  
-- 创建线性哈希分区  
createtable`表名`(  
`xxx` xxx notnull,  
....  
)  
partitionby linear hash(xxx)  
partitions 分区数量;  
  
-- 创建Key键分区  
createtable`表名`(  
`xxx` xxx notnull,  
....  
)  
partitionby key(xxx)  
partitions 分区数量;  
  
-- 创建Sub子分区  
createtable`表名`(  
`xxx` xxx notnull,  
....  
)  
partitionbyrange(父分区键)  
subpartition by hash(子分区键)(  
partition分区名1values less than (范围1)(  
        subpartition 子分区名1,  
        subpartition 子分区名2,  
......  
),  
partition分区名2values less than (范围2)(  
        subpartition 子分区名1,  
        subpartition 子分区名2,  
......  
),  
......  
);  
  
-- 查询一张表各个分区的数据量  
select  
    partition_name as"分区名称",table_rows as"数据行数"  
from  
    information_schema.partitions   
where  
    table_name ='表名';  
  
-- 查询一张表父子分区的数据量  
select  
    partition_name as"父分区名称",  
        subpartition_name as"子分区名称",  
        table_rows as"子分区行数"  
from  
    information_schema.partitions   
where  
    table_name ='表名';  
  
-- 查询MySQL中所有表分区的信息  
select*from information_schema.partitions;  
  
-- 查询一张表某个分区中的所有数据  
select*from表名partition(分区名);  
  
-- 对于一张已存在的表添加分区  
altertable表名 reorganize partition分区名into(  
partition分区名1values less than (范围) data directory ="/xxx/xxx/xxx",  
partition分区名2values less than (范围) data directory ="/xxx/xxx/xxx",  
......  
);  
  
-- 将多个分区合并成一个分区  
altertable表明 reorganize partition分区名1,分区名2...into(  
partition新分区名values less than (范围)  
);  
  
-- 清空一个分区中的所有数据  
altertable表名truncatepartition分区名;  
  
-- 删除一个表的指定分区  
altertable表名droppartition分区名;  
  
-- 重建一张表的分区  
altertable表名 rebuild partition分区名;  
  
-- 分析一个表分区  
altertable表名 analyze partition分区名;  
-- 优化一个表分区  
altertable表名 optimize partition分区名;  
-- 检查一个表分区  
altertable表名checkpartition分区名;  
-- 修复一个表分区  
altertable表名 repair partition分区名;  
  
-- 减少hash、key分区方式的 n 个分区  
altertable表名 coalesce partition n;  
  
-- 将一张表的分区切换到另一张表  
altertable表名1 exchange partition分区名withtable表名2;  
  
-- 移除一张表的所有分区  
altertable表名 remove partitioning;

十二、MySQL、InnoDB、MyISAM的参数

``参数,也被称之为MySQL的系统变量,这些变量是影响MySQL运行的关键,对每个参数做出不同调整,都有可能直接影响到线上数据库的性能,具体的完整系统参数可参考《MySQL官网文档-系统变量》,官方提供的系统参数数量大致如下:

MySQL系统变量

通过xpath的方式提取数据,大概能够得知MySQL系统变量大概一千个上下,因此这里就不做过多的详细介绍,简单介绍一些较为常用的即可,其他具体的可参考前面给出的官网链接。

但是要注意,虽说MySQL中有一千多个对外暴露的系统参数,但并不是所有的参数都可以让用户调整,MySQL的系统参数分为了三类:
一类是由MySQL自己维护的参数,这类参数用户无法调整。
第二类是以配置文件的形式加载的参数,这类参数必须在MySQL停机的情况下才能更改。
第三类是运行时的系统参数,这类是可以由用户去做动态调整的。

咱们需要关心的重点就是第三类参数,那如何观察这类参数呢?方式如下:

  • show global variables;:查看全局所有用户级别可以看到的系统变量。

  • show session variables; | show variables;:查看当前会话的所有系统变量。

  • show variables like '%关键字%';:使用模糊查询搜索某个系统变量。

MySQL5.1MySQL8.0版本的执行结果如下:

可调整的系统变量

可以很明显的从结果中得知:MySQL5.1版本中存在278个系统变量,MySQL8.0版本中存在557个系统变量,这仅仅只是社区版,而在商业版的MySQL中,其系统参数会更多,下面调出一些重点来聊一聊。

  • max_connectionsMySQL的最大连接数,超出后新到来的连接会阻塞或被拒绝。

  • version:当前数据库的版本。

  • ft_min_word_len:使用MyISAM引擎的表中,全文索引最小搜索长度。

  • ft_max_word_len:使用MyISAM引擎的表中,全文索引最大搜索长度。

  • ft_query_expansion_limitMyISAM中使用with query expansion搜索的最大匹配数。

  • innodb_ft_min_token_sizeInnoDB引擎的表中,全文索引最小搜索长度。

  • innodb_ft_max_token_sizeInnoDB引擎的表中,全文索引最大搜索长度。

  • optimizer_switchMySQL隐藏参数的开关。

  • skip_scan:是否开启索引跳跃扫描机制。

  • innodb_page_sizeInnoDB引擎数据页的大小。

  • tx_isolation:事务的隔离级别。

  • autocommit:事务自动提交机制。

  • innodb_autoinc_lock_mode:插入意向锁的工作模式。

  • innodb_lock_wait_timeoutInnoDB锁冲突时,阻塞的超时时间。

  • innodb_deadlock_detect:是否开启InnoDB死锁检测机制。

  • innodb_max_undo_log_size:本地磁盘文件中,Undo-log的最大值,默认1GB

  • innodb_rollback_segments:指定回滚段的数量,默认为1个。

  • innodb_undo_directory:指定Undo-log的存放目录,默认放在.ibdata文件中。

  • innodb_undo_logs:指定回滚段的数量,默认为128个,也就是之前的innodb_rollback_segments

  • innodb_undo_tablespaces:指定Undo-log分成几个文件来存储,必须开启innodb_undo_directory参数。

  • back_log:回滚日志的最大回撤长度(一条数据的最长版本链长度)。

  • innodb_undo_log_truncate:是否开启Undo-log的压缩功能,即日志文件超过一半时自动压缩,默认关闭。

  • innodb_flush_log_at_trx_commit:设置redo_log_buffer的刷盘策略,默认每次提交事务都刷盘。

  • innodb_log_group_home_dir:指定redo-log日志文件的保存路径,默认为./

  • innodb_log_buffer_size:指定redo_log_buffer缓冲区的大小,默认为16MB

  • innodb_log_files_in_group:指定redo日志的磁盘文件个数,默认为2个。

  • innodb_log_file_size:指定redo日志的每个磁盘文件的大小限制,默认为48MB

  • innodb_log_write_ahead_size:设置checkpoint刷盘机制每次落盘动作的大小。

  • innodb_log_compressed_pages:是否对Redo日志开启页压缩机制,默认ON

  • innodb_log_checksumsRedo日志完整性效验机制,默认开启。

  • log_bin:是否开启bin-log日志,默认ON开启,表示会记录变更DB的操作。

  • log_bin_basename:设置bin-log日志的存储目录和文件名前缀,默认为./bin.0000x

  • log_bin_index:设置bin-log索引文件的存储位置,因为本地有多个日志文件,需要用索引来确定目前该操作的日志文件。

  • binlog_format:指定bin-log日志记录的存储方式,可选Statment、Row、Mixed

  • max_binlog_size:设置bin-log本地单个文件的最大限制,最多只能调整到1GB

  • binlog_cache_size:设置为每条线程的工作内存,分配多大的bin-log缓冲区。

  • sync_binlog:控制bin-log日志的刷盘频率。

  • binlog_do_db:设置后,只会收集指定库的bin-log日志,默认所有库都会记录。

  • log-errorerror-log错误日志的保存路径和名字。

  • slow_query_log:设置是否开启慢查询日志,默认OFF关闭。

  • slow_query_log_file:指定慢查询日志的存储目录及文件名。

  • general_log:是否开启查询日志,默认OFF关闭。

  • general_log_file:指定查询日志的存储路径和文件名。

  • innodb_buffer_pool_sizeInnoDB缓冲区的大小。

  • innodb_adaptive_hash_index:是否开启InnoDB的自适应哈希索引机制。

  • innodb_compression_level:调整压缩的级别,可控范围在1~9,越高压缩效果越好,但压缩速度也越慢。

  • innodb_compression_failure_threshold_pct:当压缩失败的数据页超出该比例时,会加入数据填充来减小失败率,为0表示禁止填充。

  • innodb_compression_pad_pct_max:一个数据页中最大允许填充多少比例的空白数据。

  • innodb_log_compressed_pages:控制是否对redo-log日志的数据也开启压缩机制。

  • innodb_cmp_per_index_enabled:是否对索引文件开启压缩机制。

  • character_set_client:客户端的字符编码格式。

  • character_set_connection:数据库连接的字符编码格式。

  • character_set_database:数据库的字符编码格式。

  • character_set_results:返回的结果集的编码格式。

  • character_set_serverMySQL-Server的字符编码格式。

  • character_set_system:系统的字符编码格式。

  • collation_database:数据库的字符排序规则。

  • ......:剩下的就不再列出来了,大家可根据查询出的变量名,去官网文档查询释义即可。

十三、MySQL常见的错误码

程序Bug、错误、异常…,这些词汇天生与每位程序员挂钩,当一个程序出现问题时,用户第一时间想到的是这个平台的程序员不行,而身为开发者逃不开一点是:又得背锅和加班解决问题

MySQL作为整个系统的后方大本营,自然也无法避免会出现错误与异常,在MySQL内部其实定义了一系列错误码,当运行过程中发生错误,或执行语句时出现异常时,一般情况下都会向客户端返回错误码以及错误信息。

但往往很多时候有些错误咱们没见过,所以面对问题时难免有些束手无策,也正因为如此,本节将罗列MySQL中常见的错误码,当你碰到问题时可以直接通过错误码在此搜索,以此来定位问题出现的原因,以此进一步推导出问题的解决方案。

MySQL的错误信息由ErrorCode、SQLState、ErrorInfo三部分组成,即错误码、SQL状态、错误信息三部分组成,如下:

ERROR 1045 (28000): Access denied for user 'zhuzi'@'localhost' (using password: YES)

其中1045属于错误状态码,28000属于SQL状态,后面跟着的则是具体的错误信息,不过MySQL内部大致定义了两三千个错误码,其错误码的定义位于include/mysqld_error.h、include/mysqld_ername.h文件中,而SQLState的定义则位于include/sql_state.h文件中,所有的错误信息列表则位于share/errmsg.txt文件中,因此大家感兴趣的可自行编译MySQL源码查看。

OK,由于本章篇幅过长,就不再罗列细致的错误码详情了,毕竟当出现错误时,百度、谷歌才是最好的选择,毕竟从上面不仅仅能找到错误码的含义,同时还能找到错误码的解决方案,但为了文章的完整性,再贴一位大佬的→《错误码大全》←的整理:
![字数](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/3cf38af323984c8486444d28f00d4b25tplv-k3u1fbpfcp-watermark.image?)
通篇下来共计16.07W字,里面对于MySQL90%+的错误码都罗列出来了,但我大致看了一些,有些错误码的描述并不恰当,应该是直接根据share/errmsg.txt文件中的错误信息直接翻译过来的,大家有兴趣可以看看~

十四、MySQL命令大全总结

到这里为止,对于MySQL中大部分常用的命令基本上都已经罗列出来啦!以后如若忘记某个函数名称、某条语句的语法等等,都可以直接在本章中快捷搜索,但本篇仅仅只写出了基本的语法,实际数据库开发中往往还需要结合业务,来编写更为复杂的SQL语句~。本章的主要目的是当成《MySQL命令手册》,本质上没有太多可以学习的知识点,但它却非常实用,能够协助咱们的日常开发。

题外话

黑客&网络安全如何学习

今天只要你给我的文章点赞,我私藏的网安学习资料一样免费共享给你们,来看看有哪些东西。

1.学习路线图

在这里插入图片描述

攻击和防守要学的东西也不少,具体要学的东西我都写在了上面的路线图,如果你能学完它们,你去就业和接私活完全没有问题。

2.视频教程
网上虽然也有很多的学习资源,但基本上都残缺不全的,这是我们和网安大厂360共同研发的的网安视频教程,之前都是内部资源,专业方面绝对可以秒杀国内99%的机构和个人教学!全网独一份,你不可能在网上找到这么专业的教程。

内容涵盖了入门必备的操作系统、计算机网络和编程语言等初级知识,而且包含了中级的各种渗透技术,并且还有后期的CTF对抗、区块链安全等高阶技术。总共200多节视频,200多G的资源,不用担心学不全。
在这里插入图片描述
因篇幅有限,仅展示部分资料,需要见下图即可前往获取

🐵这些东西我都可以免费分享给大家,需要的可以点这里自取👉:网安入门到进阶资源

3.技术文档和电子书
技术文档也是我自己整理的,包括我参加大型网安行动、CTF和挖SRC漏洞的经验和技术要点,电子书也有200多本,由于内容的敏感性,我就不一一展示了。

在这里插入图片描述

因篇幅有限,仅展示部分资料,需要点击下方链接即可前往获取

🐵这些东西我都可以免费分享给大家,需要的可以点这里自取👉:网安入门到进阶资源

4.工具包、面试题和源码
“工欲善其事必先利其器”我为大家总结出了最受欢迎的几十款款黑客工具。涉及范围主要集中在 信息收集、Android黑客工具、自动化工具、网络钓鱼等,感兴趣的同学不容错过。

还有我视频里讲的案例源码和对应的工具包,需要的话也可以拿走。

🐵这些东西我都可以免费分享给大家,需要的可以点这里自取👉:网安入门到进阶资源

最后就是我这几年整理的网安方面的面试题,如果你是要找网安方面的工作,它们绝对能帮你大忙。

这些题目都是大家在面试深信服、奇安信、腾讯或者其它大厂面试时经常遇到的,如果大家有好的题目或者好的见解欢迎分享。

参考解析:深信服官网、奇安信官网、Freebuf、csdn等

内容特点:条理清晰,含图像化表示更加易懂。

内容概要:包括 内网、操作系统、协议、渗透测试、安服、漏洞、注入、XSS、CSRF、SSRF、文件上传、文件下载、文件包含、XXE、逻辑漏洞、工具、SQLmap、NMAP、BP、MSF…

在这里插入图片描述

因篇幅有限,仅展示部分资料,需要点击下方链接即可前往获取

🐵这些东西我都可以免费分享给大家,需要的可以点这里自取👉:网安入门到进阶资源
————————————————

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
侵权,请联系删除。

MYSQL常用命令 1.导出整个数据库 mysqldump -u 用名 -p –default-character-set=latin1 数据库名 > 导出的文件名(数据库默认编码是latin1) mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql 2.导出一个表 mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名 mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql 3.导出一个数据库结构 mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:wcnc_db.sql -d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table 4.导入数据库 A:常用source 命令 进入mysql数据库控制台, 如mysql -u root -p mysql>use 数据库 然后使用source命令,后面参数为脚本文件(如这里用到的.sql) mysql>source wcnc_db.sql B:使用mysqldump命令 mysqldump -u username -p dbname < filename.sql C:使用mysql命令 mysql -u username -p -D dbname 2、退出MySQL:quit或exit 二、库操作 1、创建数据库 命令:create database 例如:建立一个名为xhkdb的数据库 mysql> create database xhkdb; 2、显示所有的数据库 命令:show databases (注意:最后有个s) mysql> show databases; 3、删除数据库 命令:drop database 例如:删除名为 xhkdb的数据库 mysql> drop database xhkdb; 4、连接数据库 命令:use 例如:如果xhkdb数据库存在,尝试存取它: mysql> use xhkdb; 屏幕提示:Database changed 5、查看当前使用的数据库 mysql> select database(); 6、当前数据库包含的表信息: mysql> show tables; (注意:最后有个s) 三、表操作,操作之前应连接某个数据库 1、建表 命令:create table ( [,.. ]); mysql> create table MyClass( > id int(4) not null primary key auto_increment, > name char(20) not null, > sex int(4) not null default ’′, > degree double(16,2)); 2、获取表结构 命令:desc 表名,或者show columns from 表名 mysql>DESCRIBE MyClass mysql> desc MyClass; mysql> show columns from MyClass; 3、删除表 命令:drop table 例如:删除表名为 MyClass 的表 mysql> drop table MyClass; 4、插入数据 命令:insert into [( [,.. ])] values ()[, ( 值n )] 例如,往表 MyClass中插入二条记录, 这二条记录表示:编号为的名为Tom的成绩为.45, 编号为 的名为Joan 的成绩为.99,编号为 的名为Wang 的成绩为.5. mysql>insert into MyClass values(1,’Tom’,96.45),(2,’Joan’,82.99), (2,’Wang’, 96.59); 5、查询表中的数据 1)、查询所有行 命令:select from where 例如:查看表 MyClass 中所有数据 mysql> select * from MyClass; 2)、查询前几行数据 例如:查看表 MyClass 中前行数据 mysql> select * from MyClass order by id limit 0,2; 或者: mysql> select * from MyClass limit 0,2; 6、删除表中数据 命令:delete from 表名 where 表达式 例如:删除表 MyClass中编号为 的记录 mysql> delete from MyClass where id=1; 7、修改表中数据:update 表名 set 字段=新值,…where 条件 mysql> update MyClass set name=’Mary’where id=1; 7、在表中增加字段: 命令:alter table 表名 add字段 类型 其他; 例如:在表MyClass中添加了一个字段passtest,类型为int(4),默认值为 mysql> alter table MyClass add passtest int(4) default ’′ 8、更改表名: 命令:rename table 原表名 to 新表名; 例如:在表MyClass名字更改为YouClass mysql> rename table MyClass to YouClass; 更新字段内容 update 表名 set 字段名 = 新内容 update 表名 set 字段名 = replace(字段名,’旧内容’,'新内容’) 文章前面加入个空格 update article set content=concat(‘  ’,content); 字段类型 1.INT[(M)] 型:正常大小整数类型 2.DOUBLE[(M,D)] [ZEROFILL] 型:正常大小(双精密)浮点数字类型 3.DATE 日期类型:支持的范围是-01-01到-12-31。MySQL以YYYY-MM-DD格式来显示DATE值,但是允许你使用字符串或数字把值赋给DATE列 4.CHAR(M) 型:定长字符串类型,当存储时,总是是用空格填满右边到指定的长度 5.BLOB TEXT类型,最大长度为(2^16-1)个字符。 6.VARCHAR型:变长字符串类型 5.导入数据库表   ()创建.sql文件   ()先产生一个库如auction.c:mysqlbin>mysqladmin -u root -p creat auction,会提示输入密码,然后成功创建。   ()导入auction.sql文件   c:mysqlbin>mysql -u root -p auction grant select,insert,delete,create,drop   on *.* (或test.*/user.*/..)   to 用户名@localhost   identified by ‘密码’;   如:新建一个用户帐号以便可以访问数据库,需要进行如下操作:   mysql> grant usage   -> ON test.*   -> TO testuser@localhost;   Query OK, 0 rows affected (0.15 sec)   此后就创建了一个新用户叫:testuser,这个用户只能从localhost连接到数据库并可以连接到test 数据库。下一步,我们必须指定testuser这个用户可以执行哪些操作:   mysql> GRANT select, insert, delete,update   -> ON test.*   -> TO testuser@localhost;   Query OK, 0 rows affected (0.00 sec)   此操作使testuser能在每一个test数据库中的表执行SELECT,INSERT和DELETE以及UPDATE查询操作。现在我们结束操作并退出MySQL客户程序:   mysql> exit   Bye9! 1:使用SHOW语句找出在服务器上当前存在什么数据库mysql> SHOW DATABASES; 2:2、创建一个数据库MYSQLDATA mysql> Create DATABASE MYSQLDATA; 3:选择你所创建的数据库 mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功!) 4:查看现在的数据库中存在什么表 mysql> SHOW TABLES; 5:创建一个数据库mysql> Create TABLE MYTABLE (name VARCHAR(20), sex CHAR(1)); 6:显示表的结构: mysql> DESCRIBE MYTABLE; 7:往表中加入记录 mysql> insert into MYTABLE values (“hyq”,”M”); 8:用文本方式将数据装入数据库表中(例如D:/mysql.txt) mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt”INTO TABLE MYTABLE; 9:导入.sql文件命令(例如D:/mysql.sql) mysql>use database; mysql>source d:/mysql.sql; 10:删除表 mysql>drop TABLE MYTABLE; 11:清空表 mysql>delete from MYTABLE; 12:更新表中数据 mysql>update MYTABLE set sex=”f”where name=’hyq’; 以下是无意中在网络看到的使用MySql的管理心得, 在windows中MySql以服务形式存在,在使用前应确保此服务已经启动,未启动可用net start mysql命令启动。而Linux中启动时可用“/etc/rc.d/init.d/mysqld start”命令,注意启动者应具有管理员权限。 刚安装好的MySql包含一个含空密码的root帐户和一个匿名帐户,这是很大的安全隐患,对于一些重要的应用我们应将安全性尽可能提高,在这里应把匿名帐户删除、root帐户设置密码,可用如下命令进行: use mysql; delete from User where User=”"; update User set Password=PASSWORD(‘newpassword’) where User=’root’; 如果要对用户所用的登录终端进行限制,可以更新User表中相应用户的Host字段,在进行了以上更改后应重新启动数据库服务,此时登录时可用如下类似命令mysql -uroot -p; mysql -uroot -pnewpassword; mysql mydb -uroot -p; mysql mydb -uroot -pnewpassword; 上面命令参数是常用参数的一部分,详细情况可参考文档。此处的mydb是要登录的数据库的名称。 在进行开发和实际应用中,用户不应该只用root用户进行连接数据库,虽然使用root用户进行测试时很方便,但会给系统带来重大安全隐患,也不利于管理技术的提高。我们给一个应用中使用的用户赋予最恰当的数据库权限。如一个只进行数据插入的用户不应赋予其删除数据的权限。MySql的用户管理是通过User表来实现的,添加新用户常用的方法有两个,一是在User表插入相应的数据行,同时设置相应的权限;二是通过GRANT命令创建具有某种权限的用户。其中GRANT的常用用法如下: grant all on mydb.* to NewUserName@HostName identified by “password”; grant usage on *.* to NewUserName@HostName identified by “password”; grant select,insert,update on mydb.* to NewUserName@HostName identified by “password”; grant update,delete on mydb.TestTable to NewUserName@HostName identified by “password”; 若要给此用户赋予他在相应对象上的权限的管理能力,可在GRANT后面添加WITH GRANT OPTION选项。而对于用插入User表添加的用户,Password字段应用PASSWORD 函数进行更新加密,以防不轨之人窃看密码。对于那些已经不用的用户应给予清除,权限过界的用户应及时回收权限,回收权限可以通过更新User表相应字段,也可以使用REVOKE操作。 下面给出本人从其它资料(www.cn-java.com)获得的对常用权限的解释: 全局管理权限: FILE: 在MySQL服务器上读写文件。 PROCESS: 显示或杀死属于其它用户的服务线程。 RELOAD: 重载访问控制表,刷新日志等。 SHUTDOWN: 关闭MySQL服务。 数据库/数据表/数据列权限: Alter: 修改已存在的数据表(例如增加/删除列)和索引。 Create: 建立新的数据库或数据表。 Delete: 删除表的记录。 Drop: 删除数据表或数据库。 INDEX: 建立或删除索引。 Insert: 增加表的记录。 Select: 显示/搜索表的记录。 Update: 修改表中已存在的记录。 特别的权限: ALL: 允许做任何事(和root一样)。 USAGE: 只允许登录–其它什么也不允许做。 ——————— MYSQL常用命令 有很多朋友虽然安装好了mysql但却不知如何使用它。在这篇文章中我们就从连接MYSQL、修改密码、增加用户等方面来学习一些MYSQL的常用命令。   有很多朋友虽然安装好了mysql但却不知如何使用它。在这篇文章中我们就从连接MYSQL、修改密码、增加用户等方面来学习一些MYSQL的常用命令。    一、连接MYSQL    格式:mysql -h主机地址-u用户名-p用户密码     、例:连接到本机上的MYSQL   首先在打开DOS窗口,然后进入目录mysqlbin,再键入命令mysql -uroot -p,回车后提示你输密码,如果刚安装好MYSQL,超级用户root是没有密码的,故直接回车即可进入到MYSQL中了,MYSQL的提示符是:mysql>      、例:连接到远程主机上的MYSQL   假设远程主机的IP为:.110.110.110,用户名为root,密码为abcd123。则键入以下命令:      mysql -h110.110.110.110 -uroot -pabcd123      (注:u与root可以不用加空格,其它也一样)     、退出MYSQL命令:exit (回车)   二、修改密码     格式:mysqladmin -u用户名-p旧密码password 新密码    、例:给root加个密码ab12。首先在DOS下进入目录mysqlbin,然后键入以下命令     mysqladmin -uroot -password ab12      注:因为开始时root没有密码,所以-p旧密码一项就可以省略了。     、例:再将root的密码改为djg345   mysqladmin -uroot -pab12 password djg345 MYSQL常用命令(下)   一、操作技巧   、如果你打命令时,回车后发现忘记加分号,你无须重打一遍命令,只要打个分号回车就可以了。也就是说你可以把一个完整的命令分成几行来打,完后用分号作结束标志就OK。   、你可以使用光标上下键调出以前的命令。但以前我用过的一个MYSQL旧版本不支持。我现在用的是mysql-3.23.27-beta-win。   二、显示命令   、显示数据库列表。   show databases;   刚开始时才两个数据库mysql和test。mysql库很重要它里面有MYSQL的系统信息,我们改密码和新增用户,实际上就是用这个库进行操作。   、显示库中的数据表:   use mysql;//打开库,学过FOXBASE的一定不会陌生吧   show tables;   、显示数据表的结构:   describe 表名;   、建库:   create database 库名;   、建表:   use 库名;   create table 表名(字段设定列表);   、删库和删表:   drop database 库名;   drop table 表名;   、将表中记录清空:   delete from 表名;   、显示表中的记录:   select * from 表名; 三、一个建库和建表以及插入数据的实例   drop database if exists school; //如果存在SCHOOL则删除   create database school; //建立库SCHOOL   use school; //打开库SCHOOL   create table teacher //建立表TEACHER   (   id int(3) auto_increment not null primary key,   name char(10) not null,   address varchar(50) default ‘深圳’,   year date   ); //建表结束   //以下为插入字段   insert into teacher values(”,’glchengang’,'深圳一中’,’-10-10′);   insert into teacher values(”,’jack’,'深圳一中’,’-12-23′);   注:在建表中()将ID设为长度为的数字字段:int(3)并让它每个记录自动加一:auto_increment并不能为空:not null而且让他成为主字段primary key   ()将NAME设为长度为的字符字段   ()将ADDRESS设为长度的字符字段,而且缺省值为深圳。varchar和char有什么区别呢,只有等以后的文章再说了。   ()将YEAR设为日期字段。   如果你在mysql提示符键入上面的命令也可以,但不方便调试。你可以将以上命令原样写入一个文本文件中假设为school.sql,然后复制到c:\下,并在DOS状态进入目录\mysql\bin,然后键入以下命令:   mysql -uroot -p密码school.bbb   注释:将数据库school备份到school.bbb文件,school.bbb是一个文本文件,文件名任取,打开看看你会有新发现。 一.SELECT语句的完整语法为: SELECT[ALL|DISTINCT|DISTINCTROW|TOP] {*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]} FROM tableexpression[,…][IN externaldatabase] [WHERE…] [GROUP BY…] [HAVING…] [ORDER BY…] [WITH OWNERACCESS OPTION] 说明: 用中括号([])括起来的部分表示是可选的,用大括号({})括起来的部分是表示必须从中选择其中的一个。 1 FROM子句 FROM 子句指定了SELECT语句中字段的来源。FROM子句后面是包含一个或多个的表达式(由逗号分开),其中的表达式可为单一表名称、已保存的查询或由INNER JOIN、LEFT JOIN 或RIGHT JOIN 得到的复合结果。如果表或查询存储在外部数据库,在IN 子句之后指明其完整路径。 例:下列SQL语句返回所有有定单的客户: SELECT OrderID,Customer.customerID FROM Orders Customers WHERE Orders.CustomerID=Customers.CustomeersID 2 ALL、DISTINCT、DISTINCTROW、TOP谓词 (1) ALL 返回满足SQL语句条件的所有记录。如果没有指明这个谓词,默认为ALL。 例:SELECT ALL FirstName,LastName FROM Employees (2) DISTINCT 如果有多个记录的选择字段的数据相同,只返回一个。 (3) DISTINCTROW 如果有重复的记录,只返回一个 (4) TOP显示查询头尾若干记录。也可返回记录的百分比,这是要用TOP N PERCENT子句(其中N 表示百分比) 例:返回%定货额最大的定单 SELECT TOP 5 PERCENT* FROM [ Order Details] ORDER BY UnitPrice*Quantity*(1-Discount) DESC 3 用AS 子句为字段取别名 如果想为返回的列取一个新的标题,或者,经过对字段的计算或总结之后,产生了一个新的值,希望把它放到一个新的列里显示,则用AS保留。 例:返回FirstName字段取别名为NickName SELECT FirstName AS NickName ,LastName ,City FROM Employees 例:返回新的一列显示库存价值 SELECT ProductName ,UnitPrice ,UnitsInStock ,UnitPrice*UnitsInStock AS valueInStock FROM Products 二.WHERE 子句指定查询条件 1 比较运算符 比较运算符含义 = 等于 > 大于 = 大于等于 <= 小于等于 不等于 !> 不大于 !#1/1/96# AND OrderDate#96-1-1# 也可以表示为: WHERE OrderDate>Datevalue(‘/1/96’) 使用NOT 表达式求反。 例:查看年月日以后的定单 WHERE Not OrderDateQuantity 另一种方法是用Microsof JET SQL 独有的JNNER JOIN 语法: FROM table1 INNER JOIN table2 ON table1.field1 comparision table2.field2 其中comparision 就是前面WHERE子句用到的比较运算符。 SELECT FirstName,lastName,OrderID,CustomerID,OrderDate FROM Employees INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID 注意: INNER JOIN不能连接Memo OLE Object Single Double 数据类型字段。 在一个JOIN语句中连接多个ON子句 语法: SELECT fields FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field1 AND ON table1.field2 compopr table2.field2 OR ON table1.field3 compopr table2.field3 也可以 SELECT fields FROM table1 INNER JOIN (table2 INNER JOIN [( ]table3 [INNER JOER] [( ]tablex[INNER JOIN] ON table1.field1 compopr table2.field1 ON table1.field2 compopr table2.field2 ON table1.field3 compopr table2.field3 外部连接返回更多记录,在结果中保留不匹配的记录,不管存不存在满足条件的记录都要返回另一侧的所有记录。 FROM table [LEFT|RIGHT]JOIN table2 ON table1.field1comparision table.field2 用左连接来建立外部连接,在表达式的左边的表会显示其所有的数据 例:不管有没有定货量,返回所有商品 SELECT ProductName ,OrderID FROM Products LEFT JOIN Orders ON Products.PrductsID=Orders.ProductID 右连接与左连接的差别在于:不管左侧表里有没有匹配的记录,它都从左侧表中返回所有记录。 例:如果想了解客户的信息,并统计各个地区的客户分布,这时可以用一个右连接,即使某个地区没有客户,也要返回客户信息。 空值不会相互匹配,可以通过外连接才能测试被连接的某个表的字段是否有空值。 SELECT * FROM talbe1 LEFT JOIN table2 ON table1.a=table2.c 1 连接查询中使用Iif函数实现以值显示空值 Iif表达式:Iif(IsNull(Amount,0,Amout) 例:无论定货大于或小于¥,都要返回一个标志。 Iif([Amount]>50,?Big order?,?Small order?) 五. 分组和总结查询结果 在SQL的语法里,GROUP BY和HAVING子句用来对数据进行汇总。GROUP BY子句指明了按照哪几个字段来分组,而将记录分组后,用HAVING子句过滤这些记录。 GROUP BY 子句的语法 SELECT fidldlist FROM table WHERE criteria [GROUP BY groupfieldlist [HAVING groupcriteria]] 注:Microsoft Jet数据库Jet 不能对备注或OLE对象字段分组。 GROUP BY字段中的Null值以备分组但是不能被省略。 在任何SQL合计函数中不计算Null值。 GROUP BY子句后最多可以带有十个字段,排序优先级按从左到右的顺序排列。 例:在‘WA’地区的雇员表中按头衔分组后,找出具有同等头衔的雇员数目大于人的所有头衔。 SELECT Title ,Count(Title) as Total FROM Employees WHERE Region = ‘WA’ GROUP BY Title HAVING Count(Title)>1 JET SQL 中的聚积函数 聚集函数意义 SUM ( ) 求和 AVG ( ) 平均值 COUNT ( ) 表达式中记录的数目 COUNT (* ) 计算记录的数目 MAX 最大值 MIN 最小值 VAR 方差 STDEV 标准误差 FIRST 第一个值 LAST 最后一个值 六. 用Parameters声明创建参数查询 Parameters声明的语法: PARAMETERS name datatype[,name datatype[, …]] 其中name 是参数的标志符,可以通过标志符引用参数. Datatype说明参数的数据类型. 使用时要把PARAMETERS 声明置于任何其他语句之前. 例: PARAMETERS[Low price] Currency,[Beginning date]datatime SELECT OrderID ,OrderAmount FROM Orders WHERE OrderAMount>[low price] AND OrderDate>=[Beginning date] 七. 功能查询 所谓功能查询,实际上是一种操作查询,它可以对数据库进行快速高效的操作.它以选择查询为目的,挑选出符合条件的数据,再对数据进行批处理.功能查询包括更新查询,删除查询,添加查询,和生成表查询. 1 更新查询 UPDATE子句可以同时更改一个或多个表中的数据.它也可以同时更改多个字段的值. 更新查询语法: UPDATE 表名 SET 新值 WHERE 准则 例:英国客户的定货量增加%,货运量增加% UPDATE OEDERS SET OrderAmount = OrderAmount *1.1 Freight = Freight*1.03 WHERE ShipCountry = ‘UK’ 2 删除查询 DELETE子句可以使用户删除大量的过时的或冗于的数据. 注:删除查询的对象是整个记录. DELETE子句的语法: DELETE [表名.*] FROM 来源表 WHERE 准则 例: 要删除所有年前的定单 DELETE * FROM Orders WHERE OrderData<#94-1-1# 3 追加查询 INSERT子句可以将一个或一组记录追加到一个或多个表的尾部. INTO 子句指定接受新记录的表 valueS 关键字指定新记录所包含的数据值. INSERT 子句的语法: INSETR INTO 目的表或查询(字段,字段,…) valueS(数值,数值,…) 例:增加一个客户 INSERT INTO Employees(FirstName,LastName,title) valueS(‘Harry’,’Washington’,’Trainee’) 4 生成表查询 可以一次性地把所有满足条件的记录拷贝到一张新表中.通常制作记录的备份或副本或作为报表的基础. SELECT INTO子句用来创建生成表查询语法: SELECT 字段,字段,… INTO 新表[IN 外部数据库] FROM 来源数据库 WHERE 准则 例:为定单制作一个存档备份 SELECT * INTO OrdersArchive FROM Orders 八. 联合查询 UNION运算可以把多个查询的结果合并到一个结果集里显示. UNION运算的一般语法: [表]查询UNION [ALL]查询UNION … 例:返回巴西所有供给商和客户的名字和城市 SELECT CompanyName,City FROM Suppliers WHERE Country = ‘Brazil’ UNION SELECT CompanyName,City FROM Customers WHERE Country = ‘Brazil’ 注: 缺省的情况下,UNION子句不返回重复的记录.如果想显示所有记录,可以加ALL选项 UNION运算要求查询具有相同数目的字段.但是,字段数据类型不必相同. 每一个查询参数中可以使用GROUP BY 子句或HAVING 子句进行分组.要想以指定的顺序来显示返回的数据,可以在最后一个查询的尾部使用OREER BY子句. 九. 交叉查询 交叉查询可以对数据进行总和,平均,计数或其他总和计算法的计算,这些数据通过两种信息进行分组:一个显示在表的左部,另一个显示在表的顶部. Microsoft Jet SQL 用TRANSFROM语句创建交叉表查询语法: TRANSFORM aggfunction SELECT 语句 GROUP BY 子句 PIVOT pivotfield[IN(value1 [,value2[,…]]) ] Aggfounction指SQL聚积函数, SELECT语句选择作为标题的的字段, GROUP BY 分组 说明: Pivotfield 在查询结果集中创建列标题时用的字段或表达式,用可选的IN子句限制它的取值. value代表创建列标题的固定值. 例:显示在年里每一季度每一位员工所接的定单的数目: TRANSFORM Count(OrderID) SELECT FirstName&’’&LastName; AS FullName FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID WHERE DatePart(“yyyy”,OrderDate)= ‘’ GROUP BY FirstName&’’&LastName; ORDER BY FirstName&’’&LastName; POVOT DatePart(“q”,OrderDate)&’季度’ 十.子查询 子查询可以理解为套查询.子查询是一个SELECT语句. 1 表达式的值与子查询返回的单一值做比较 语法: 表达式comparision [ANY|ALL|SOME](子查询) 说明: ANY 和SOME谓词是同义词,与比较运算符(=,,,=)一起使用.返回一个布尔值True或False.ANY的意思是,表达式与子查询返回的一系列的值逐一比较,只要其中的一次比较产生True结果,ANY测试的返回True值(既WHERE子句的结果),对应于该表达式的当前记录将进入主查询的结果中.ALL测试则要求表达式与子查询返回的一系列的值的比较都产生True结果,才回返回True值. 例:主查询返回单价比任何一个折扣大于等于%的产品的单价要高的所有产品 SELECT * FROM Products WHERE UnitPrice>ANY (SELECT UnitPrice FROM[Order Details] WHERE Discount>0.25) 2 检查表达式的值是否匹配子查询返回的一组值的某个值 语法: [NOT]IN(子查询) 例:返回库存价值大于等于的产品. SELECT ProductName FROM Products WHERE ProductID IN (SELECT PrdoctID FROM [Order DEtails] WHERE UnitPrice*Quantity>= 1000) 3检测子查询是否返回任何记录 语法: [NOT]EXISTS (子查询) 例:用EXISTS检索英国的客户 SELECT ComPanyName,ContactName FROM Orders WHERE EXISTS (SELECT * FROM Customers WHERE Country = ‘UK’AND Customers.CustomerID= Orders.CustomerID)
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值