MySQL常用命令和語句

標籤：版本號碼   編碼   ddd   ssi   常用sql語句   prim   status   md5   general   

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(‘[email protected]@數@據函數@命令‘,‘@‘,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;

/*返回當前使用的資料庫使用者 [email protected] [email protected] [email protected] [email protected]% [email protected]% */
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常用命令和語句