sql

drop、delete与truncate的区别
SQL中的drop、delete、truncate都表示删除，但是三者有一些差别
delete和truncate只删除表的数据不删除表的结构
速度,一般来说: drop> truncate >delete
delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;
如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到
rollback segment中,不能回滚. 操作不触发trigger.
使用场景
不再需要一张表的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate

SELECT
case left(id_card,2)
when ‘11’ then ‘北京市’
when ‘12’ then ‘天津市’
when ‘13’ then ‘河北省’
when ‘14’ then ‘山西省’
when ‘15’ then ‘内蒙古自治区’
when ‘21’ then ‘辽宁省’
when ‘22’ then ‘吉林省’
when ‘23’ then ‘黑龙江省’
when ‘31’ then ‘上海市’
when ‘32’ then ‘江苏省’
when ‘33’ then ‘浙江省’
when ‘34’ then ‘安徽省’
when ‘35’ then ‘福建省’
when ‘36’ then ‘江西省’
when ‘37’ then ‘山东省’
when ‘41’ then ‘河南省’
when ‘42’ then ‘湖北省’
when ‘43’ then ‘湖南省’
when ‘44’ then ‘广东省’
when ‘45’ then ‘广西壮族自治区’
when ‘46’ then ‘海南省’
when ‘50’ then ‘重庆市’
when ‘51’ then ‘四川省’
when ‘52’ then ‘贵州省’
when ‘53’ then ‘云南省’
when ‘54’ then ‘西藏自治区’
when ‘61’ then ‘陕西省’
when ‘62’ then ‘甘肃省’
when ‘63’ then ‘青海省’
when ‘64’ then ‘宁夏回族自治区’
when ‘65’ then ‘新疆维吾尔自治区’
when ‘71’ then ‘台湾省’
when ‘81’ then ‘香港特别行政区’
when ‘82’ then ‘澳门特别行政区’
else ‘未知’
end AS 地域 ,
year(curdate())-
if(length(id_card)=18,substring(id_card,7,4),if(length(id_card)=15,concat(‘19’,substring(i
d_card,7,2)),null)) as 年龄,
case if(length(id_card)=18, cast(substring(id_card,17,1) as UNSIGNED)%2,
if(length(id_card)=15,cast(substring(id_card,15,1) as UNSIGNED)%2,3))
when 1 then ‘男’
when 0 then ‘女’
else ‘未知’
end AS 性别
FROM user_profile WHERE id_card !=’’
mysql 分组排序
2016-11-23
表结构、及表数据如下
CREATE TABLE t(user_id VARCHAR(10) CHARSET utf8,TYPE VARCHAR(20) CHARSET
utf8,score INT);
INSERT INTO t
SELECT ‘A’ user_id,‘语文’ AS TYPE,95 score
UNION ALL
SELECT ‘A’ user_id,‘数学’ AS TYPE,96 score
UNION ALL
SELECT ‘A’ user_id,‘英语’ AS TYPE,86 score
UNION ALL
SELECT ‘A’ user_id,‘化学’ AS TYPE,87 score
UNION ALL
SELECT ‘B’ user_id,‘语文’ AS TYPE,85 score
UNION ALL
SELECT ‘B’ user_id,‘数学’ AS TYPE,56 score
UNION ALL
SELECT ‘B’ user_id,‘英语’ AS TYPE,75 score
UNION ALL
SELECT ‘B’ user_id,‘化学’ AS TYPE,55 score
UNION ALL
SELECT ‘C’ user_id,‘数学’ AS TYPE,68 score
UNION ALL
SELECT ‘C’ user_id,‘政治’ AS TYPE,78 score ;
解法一：初级解法（只适用于面试题，不适用于生产环境）
SELECT * FROM (SELECT * FROM t WHERE user_id = ‘A’ ORDER BY score DESC LIMIT
3) a
UNION ALL
SELECT * FROM (SELECT * FROM t WHERE user_id = ‘B’ ORDER BY score DESC LIMIT
3) b
UNION ALL
SELECT * FROM (SELECT * FROM t WHERE user_id = ‘C’ ORDER BY score DESC LIMIT
3) c;

解法二：关联子查询
SELECT
user_id,
TYPE,
score
FROM
(
SELECT
t., (
SELECT
COUNT()
FROM
t tt
WHERE
tt.user_id = t.user_id
AND tt.score >= t.score
) rn
FROM
t
) t
WHERE
rn <= 3;

解法三：自连接
SELECT
t1.* FROM
t t1,
t t2
WHERE
t1.user_id = t2.user_id
AND t2.score >= t1.score
GROUP BY
t1.user_id,
t1.TYPE
HAVING
COUNT(t2.score) <= 3
ORDER BY
user_id,
rn;

解法四：子查询
SELECT
*
FROM
t a
WHERE
EXISTS (
SELECT
COUNT(1)
FROM
t b
WHERE
b.user_id = a.user_id
AND b.score > a.score
HAVING
COUNT(1) < 3
)