13个mysql数据库的实用SQL小技巧-优快云博客

出处：http://www.gbtags.com/gb/share/2351.htm

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 your_table

2.查找重复的email记录字段

SELECT email, COUNT(email) AS q
   FROM emails_table GROUP BY email HAVING q > 1
   ORDER BY q DESC

3.取出随机顺序得到记录

SELECT * FROM your_table ORDER BY RAND()

4.使用update替换指定字段里的字符

UPDATE your_table
   SET name=REPLACE(name, 'gbin1.com', 'gbtags.com')
   WHERE name LIKE '%John%';

5.重设置指定table里的自动增加数值

ALTER TABLE your_table AUTO_INCREMENT = 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 users

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

9.针对unique key(唯一键)类型字段的重复插入不报错的insert语句

INSERT IGNORE INTO tags (tag) VALUES ('good');

10.使用全文索引并且匹配搜索

select * from articles where MATCH(content_column) AGAINST ('music')

11.如果查询一个月以前

SELECT user, count(*) AS logins
   FROM stat_log
   WHERE action='LOGIN' AND dt_when >= DATE_ADD(CURDATE(), INTERVAL -1 MONTH)
   GROUP BY user

12.设置正确的字符集

SET NAMES 'utf8';

13.从一个表插入另外一个表

INSERT INTO yourtable (field1,field2,field3)
   SELECT newfield1,newfield2,'fixed value'
   FROM yourtable2