最实用的mysql 技巧总结。会时常更新的。包括收集的自己总结的。
1. 使用CASE来重新定义数值类型
SELECT id,title,
(CASE date WHEN '0000-00-00' THEN '' ELSE date END) AS date
FROM your_table
SELECT id,title,
(CASE status WHEN 0 THEN 'open' WHEN 1 THEN 'close' ELSE 'standby' END) AS status
FROM user
SELECT email, COUNT(email) AS q
FROM emails_table GROUP BY email HAVING q > 1
ORDER BY q DESC
3. 取出随机顺序得到记录
SELECT * FROM user ORDER BY RAND()
4. 使用update替换指定字段里的字符
UPDATE user
SET name=REPLACE(name, 'gbin1.com', 'gbtags.com')
WHERE name LIKE '%John%';
5. 重设置指定table里的自动增加数值
ALTER TABLE user AUTO_INCREMENT = 2
下一次你插入数据,那么id自动增加为2
6.为select语句添加一个自动增加的字段列
set @N = 0;
SELECT @N := @N +1 AS number, name, surname FROM gbtags_users;
7. 使用concat来连接字段
SELECT CONCAT(name,' ',surname) AS complete_name FROM user
8. 使用date方法来取出日期类型的指定部分
SELECT id,title, YEAR(date_field) FROM your_table
SELECT id,title,
CONCAT(MONTH(date_field),'/',YEAR(date_field)) as new_date
FROM your_table
上面语句,第一个select取出了年份,第二取出了月份和年份组合
9. 针对unique key(唯一键)类型字段的重复插入不报错的insert语句
INSERT IGNORE INTO tags (tag) VALUES ('good');
这个insert语句可以执行多次,不会报错,重复的插入会被忽略
10.使用全文索引并且匹配搜索
select * from articles where MATCH(content_column) AGAINST ('music')
你需要首先添加全文搜索索引到指定的列(content_column)。注意如果你表里已经有数据的话,不会创建索引,所以你需要使用一个空的表来执行这个语句
11. 查询time为一个月以前的数据
SELECT user, count(*) AS logins
FROM stat_log
WHERE action='LOGIN' AND dt_when >= DATE_ADD(CURDATE(), INTERVAL -1 MONTH)
GROUP BY user
这个语句能够让你查询字段dt_when一个月以前的所有记录
12. 设置正确的字符集
SET NAMES 'utf8';
请在php中连接开始后执行这个语句
13. 从一个表插入另外一个表
INSERT INTO yourtable (field1,field2,field3)
SELECT newfield1,newfield2,'fixed value'
FROM yourtable2
14 . 批量插入
INSERT INTO user(name,sex,age) VALUES
('小明',1,21),('小明',1,21),('小明',1,21),('小明',1,21),('小明',1,21),('小明',1,21),('小明',1,21),('小明',1,21),('小明',1,21),('小明',1,21),('小明',1,21),('小明',1,21),('小明',1,21)
REPLACE INTO `think_profile`(`member_id`,`email`,`nickname`)
VALUES ('2','236','236');
member_id 是一个唯一unique的索引键。发现有2存在,则删除那一行数据,然后再插入。
15 .批量update。
我们知道update 是没办法像insert into values(..),(..),(..) 这样批量更新的,只能一个一个的update。但是我们可以利用ON DUPLICATE KEY 和 批量插入以及unique或者是primary key来完成。
INSERT INTO product(id,gender,tag_id,collect_shownum) VALUES
(1,1,12,34),
(2,1,12,34),
(3,1,12,34),
(4,1,12,100),
(5,1,12,34),
(6,1,13,34),
(7,2,12,43),
(8,1,13,63),
(9,1,12,34)
ON DUPLICATE KEY UPDATE
gender=values(gender),
tag_id = values(tag_id),
collect_shownum = collect_shownum + values(collect_shownum)
id 是primary key 主键。插入时候,如果发现id 这一行如果存在,则用
ON DUPLICATE KEY tag_id = values(tag_id) 去update 替换。效率很高。
16. 多表联合删除
DELETE channel,channel_bpp FROM channel,channel_bpp WHERE channel.id = channel_bpp.channel_id AND channel.id=15
17. 合并多列为一个字段:group_concat
SELECT channel.*,group_concat(channel_bpp.bpp_name) as bpp_name FROM channel left join channel_bpp ON channel.id = channel_bpp.channel_id GROUP BY channel.id ORDER BY channel.id DESC
18. SHOW信息
SHOW DATABASES //列出 MySQL Server 数据库。
SHOW TABLES [FROM db_name] //列出数据库数据表。
SHOW CREATE TABLES tbl_name //导出数据表结构。
SHOW TABLE STATUS [FROM db_name] //列出数据表及表状态信息。
SHOW COLUMNS FROM tbl_name [FROM db_name] //列出资料表字段
SHOW FIELDS FROM tbl_name [FROM db_name],DESCRIBE tbl_name [col_name]。 //列出字段
SHOW FULL COLUMNS FROM tbl_name [FROM db_name]//列出字段及详情
SHOW FULL FIELDS FROM tbl_name [FROM db_name] //列出字段完整属性
SHOW INDEX FROM tbl_name [FROM db_name] //列出表索引。
SHOW STATUS //列出 DB Server 状态。
SHOW VARIABLES //列出 MySQL 系统环境变量。
SHOW PROCESSLIST //列出执行命令。
SHOW GRANTS FOR user //列出某用户权限