首页 > 代码库 > MySQL常用命令和语句
MySQL常用命令和语句
1、常用SQL语句
1)常用函数
/*
type可取值为:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
*/
/*以unix时间戳形式返回当前时间*/
select unix_timestamp();
/*以unix时间戳形式返回当前时间*/
select unix_timestamp(now());
/*将 以unix时间戳形式返回当前时间 转换为 普通日期时间*/
select from_unixtime(unix_timestamp(now()));
/*返回日期与0年1月1日之间的天数*/
select TO_DAYS(now());
/*返回在0年1月1日之后n天的日期*/
select FROM_DAYS(1000);
/*返回指定格式的日期时间字符串*/
select str_to_date(‘2013-09-15 09:00:00‘, ‘%Y-%m-%d %H:%i:%S‘);
/*返回UTC日期 2016-08-23*/
SELECT UTC_DATE();
/*返回UTC时间 17:55:19*/
select UTC_TIME();
/*返回日期对应的时间数字 64519*/
select TIME_TO_SEC(UTC_TIME());
select TIME_TO_SEC(CURTIME());
select TIME_TO_SEC(CURRENT_TIME);
/*返回时间数字对应的时间 01:55:19 AM 01:55:19 AM*/
select TIME_FORMAT(UTC_TIME(),‘%r‘);
select TIME_FORMAT(CURTIME(),‘%r‘);
select TIME_FORMAT(CURRENT_TIME,‘%r‘);
/*返回时间 01:00:00*/
select SEC_TO_TIME(3600);
/*返回时间 838:59:59*/
select SEC_TO_TIME(UNIX_TIMESTAMP());
select SEC_TO_TIME(UNIX_TIMESTAMP(now()));
/*返回时间 17:57:26*/
select SEC_TO_TIME(TIME_TO_SEC(UTC_TIME()));
select SEC_TO_TIME(TIME_TO_SEC(CURTIME()));
select SEC_TO_TIME(TIME_TO_SEC(CURRENT_TIME));
/*返回指定日期加1天后的日期时间 2016-08-25 01:58:37*/
select ADDDATE(now(),1);
/*返回指定日期加1秒后的日期时间 2016-08-24 01:58:38*/
select ADDDATE(CURRENT_TIMESTAMP(),INTERVAL 1 second);
/*返回指定日期加1分钟的日期时间 2016-08-24 01:59:37*/
select DATE_ADD(LOCALTIME(),INTERVAL 1 minute);
/*返回指定日期减1天后的日期时间 2016-08-23 01:58:37*/
select SUBDATE(SYSDATE(),INTERVAL 1 day);
/*返回指定时间加5秒后的日期时间 01:58:42*/
select ADDTIME(CURTIME(),5);
/*返回指定时间减10秒后的日期时间 01:58:27*/
select SUBTIME(CURRENT_TIME,10);
/*返回指定2个日期时间相差的天数 11*/
select DATEDIFF(current_date()+1,current_date()-10);
/*返回指定格式的日期时间 2016-08-24 01:58:37*/
select DATE_FORMAT(LOCALTIMESTAMP(),‘%Y-%m-%d %H:%i:%S‘);
/*返回字符串中字符的个数*/
SELECT CHAR_LENGTH(‘mysql数据函数‘);
/*返回字符串中字节的个数*/
SELECT LENGTH(‘mysql数据函数‘);
返回第1个参数字符串在第2个参数字符串中的位置 3
SELECT LOCATE(‘sq‘,‘mysql‘);
返回第1个参数字符串在第2个参数字符串中的位置 3
SELECT POSITION(‘s‘ IN ‘mysql‘);
返回第2个参数字符串在第1个参数字符串中的位置 3
SELECT INSTR(‘mysql‘,‘sq‘);
返回与第1个参数的字符串匹配的位置
SELECT FIELD(‘my‘,‘mql‘,‘ysql‘,‘my‘,‘mysql‘,‘my‘);
返回以指定第3个参数替换第2个参数后的字符串 mysql数据语句
SELECT REPLACE(‘mysql数据函数‘,‘函数‘,‘语句‘);
返回从第2个参数指定的值开始截取指定第3个参数长度的字符串 ql数据函
SELECT SUBSTRING(‘mysql数据函数‘,4,5);
返回从第2个参数指定的值开始截取指定第3个参数长度的字符串 sq
SELECT MID(‘mysql数据函数‘,3,2);
返回指定间隔符出现的位置左边或右边的字符串,第3个参数为整数,则为左边,否则为右边
SELECT SUBSTRING_INDEX(‘my@sql@数@据函数@命令‘,‘@‘,4);
/*返回相连接的2个字符串*/
SELECT CONCAT(‘mysql‘,‘数据函数‘);
/*返回以@符号连接2个字符串*/
SELECT CONCAT_WS(‘@‘,‘mysql‘,‘数据函数‘);
SELECT INSERT(‘mysql数据函数‘,1,4,‘非常实用的‘);
/*从左边开始返回2个长度的字符串*/
SELECT LEFT(‘mysql数据函数‘,2);
/*从右边开始返回2个长度的字符串*/
SELECT RIGHT(‘mysql数据函数‘,2);
/*返回以第3个参数从左边开始填充后的字符串,长度为10 好mysql数据函数/*
SELECT LPAD(‘mysql数据函数‘,10,‘好的‘);
/*返回以第3个参数从左右边开始填充后的字符串,长度为10 mysql数据函数好/*
SELECT RPAD(‘mysql数据函数‘,10,‘好的‘);
/*返回翻转字符串后的字符串 数函据数lqsym/*
SELECT REVERSE(‘mysql数据函数‘);
/*返回指定位置的字符串 数据/*
SELECT ELT(2,‘mysql‘,‘数据‘,‘函数‘);
/*返回字符串比较后的数字 -1 不等 0 相等/*
select STRCMP(‘mysql数据函数‘,‘mysql数据函数‘);
/*返回指定重复次数的字符串 mysql数据函数mysql数据函数mysql数据函数/*
SELECT REPEAT(‘mysql数据函数‘,3);
/*返回100次空格/*
SELECT SPACE(100);
/*将IP地址转换为数字 2130706433*/
select INET_ATON(‘127.0.0.1‘);
/*将数字转换为IP地址 127.0.0.1*/
select INET_NTOA(INET_ATON(‘127.0.0.1‘));
/*返回第1个字符的ascii码*/
select ASCII(‘myql‘);
/*返回数字的2进制*/
select BIN(10);
/*返回数字的8进制*/
select HEX(10);
/*返回数字的16进制*/
select OCT(10);
/*返回将数字p1,从p2进制转换为p3进制*/
select CONV(10,10,2);
/*数字保留2位小数*/
select FORMAT(1949001,2);
/*对字符串进行encode编码 编码密码为 encode*/
select ENCODE(‘123456‘,‘encode‘);
/*对字符串进行dencode编码 解码密码为 encode*/
select DECODE(‘123456‘,‘encode‘);
/*返回字符串的md5码 dad661767e9d552e4b689cd98536b8f7*/
select MD5(user());
/*返回加密字符串后的编码 *3F81B1F810FF5EFEF95211C8034BF1F0C1EAB7A8*/
select PASSWORD(user());
/*函数适用于:BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER*/
/*将指定字段转换为date类型*/
select CAST(now() AS date);
/*将指定字段转换为date类型*/
select CONVERT(now(),date);
/*指定数据库的字符集*/
select CONVERT(‘数据库‘ USING utf8mb4);
/*返回 指定运行now() 100次数后的时间*/
select BENCHMARK(100,now());
/*查看当前数据库的字符集*/
select CHARSET(CONVERT(‘数据库‘ USING utf8mb4));
/*返回数据的版本 版本号 5.5.38-log 24*/
select version(),CONNECTION_ID();
/*返回当前使用的数据库名称 mysql*/
select DATABASE()、SCHEMA;
/*返回当前使用的数据库用户 root@ip root@ip root@ip root@% root@% */
select USER(),SYSTEM_USER(),SESSION_USER(),CURRENT_USER(),CURRENT_USER;
/*返回当前使用的数据库使用的字符集 排序方式 自增长ID值 utf8mb4 utf8mb4_general_ci 1*/
select CHARSET(‘数据库‘),COLLATION(‘数据库‘),LAST_INSERT_ID();
/*参数1为真,返回参数1的值,否则返回参数2的值*/
select if(2=1,‘成立‘,‘不成立‘);
/*参数1不为null,返回参数1的值,否则返回参数2的值*/
select ifnull(‘非空‘,‘空‘);
/*创建表并插入数据*/
create table person(
id smallint(5) unsigned auto_increment primary key not null comment ‘主键id‘,
name varchar(50) not null comment ‘用户名‘,
sex tinyint(2) not null comment ‘性别‘,/*1 男 2 女 3 未知*/
age tinyint(2) not null comment ‘年龄‘,
phone varchar(11) comment ‘手机号‘,
address varchar(128) comment ‘当前住址‘,
statusinfo varchar(128) comment ‘各种状态简述:婚姻 收入 职业 信仰 兴趣爱好 信用状况 资产状况等‘,
describes varchar(1024) comment ‘当前所处的状况描述‘
)ENGINE=innodb default charset=utf8;
insert into person(name,sex,age,phone,address,statusinfo,describes)
values(1,‘地**‘,1,32,‘150****9871‘,‘某市某区某大厦某东某室‘,‘未婚,***万/月,技术工程师,无,购物,良好,有房贷无车贷,有***万存款,***万投资‘),
(2,‘地**‘,1,32,‘150****9871‘,‘某市某区某大厦某东某室‘,‘未婚,***万/月,技术工程师,无,购物,良好,有房贷无车贷,有***万存款,***万投资‘);
commit;
/*修改列名和列类型*/
alter table person change address personaddress varchar(128) default ‘无‘;
alter table person modify statusinfo varchar(256) default ‘各种状态简述;无‘;
/*创建主键索引*/
alter table person add primary key(id);
/*创建普通索引*/
create index idx_phone_00 on person(phone);
alter table person add index idx_phone_01(phone);
/*创建唯一索引*/
create unique index idx_phone_02 on person(phone);
alter table person add unique index idx_phone_03(phone);
/*查询表所有中索引*/
show index from person;
/*删除指定索引*/
drop index idx_phone_00 on person;
2)常用命令
1)查看数据库支持的存储引擎
show engines;
2)查看数据库当前的存储引擎
show variables like ‘%storage_engine%‘;
3)查看创建表的sql语句
show create table comment\G;
4)查看存储过程简单信息
show create procedure p1;
5)查看存储过程详细信息
select * from mysql.proc where db=‘mysql‘;
show procedure status where db=‘mysql‘;
6)显示表结构
desc person;
7)查看数据库是否支持分区
show plugins;
8)授予权限
grant all privileges on *.* to ‘root‘@‘%‘ identified by password ‘*996DC2EF930AE7BC46F2FE0040A42EB7BBEA1B3D‘ with grant option;
9)备份数据库
mysqldump -u管理员 -p 备份的数据库 > 备份的文件,以sql结尾
mysqldump -u root -p persondb > persondb_2017-04-03.sql
10)还原数据库
mysql -u管理员 -p 还原的数据库 < 还原的文件,以sql结尾
mysql -u root -p persondb <persondb_2017-04-03.sql
MySQL常用命令和语句