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常用命令和语句

标签：版本号   编码   ddd   ssi   常用sql语句   prim   status   md5   general