mysql数据库相关知识点

MyISAM与InnoDB的区别是

MyISAM引擎特点
1.不支持事务
（事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全成功要么全失败）
2.表级锁定，数据更新时锁定整个表：其锁表机制是表级锁定，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能。
3.读写互相阻塞：不仅会在写入的时候阻塞读取，MyIASM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。
4.只会缓存索引：MyIASM可以通过key_buffer_size缓存索引，以大大提高访问性能减少磁盘IO。但是这个缓存区只会缓存索引，而不会缓存数据。
5.读取速度较快，占用资源相对少
6.不支持外键约束，但支持全文索引
7.MyISAM引擎是MySQL5.5.5 前缺省的存储引擎
InnoDB引擎特点
1.支持事务：支持4个事务隔离级别，支持多版本读。
2.行级锁定（更新时一定是锁定当前行）：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响。
3.读写阻塞与事务隔离级别相关。
4.具有非常搞笑的缓存特性：能缓存索引，也能缓存数据。
5.整个表和主键以Cluster方式存储，组成一颗平衡树。
6.所有Secondary Index都会保存主键信息
7.支持分区，表空间，类似oracle数据库
8.支持外键约束 5.5 以后不支持全文索引，以后支持了。
9.和MyIASM引擎相比，InnoDB对硬件资源要求比较高。
MyIASM引擎适用的生产业务场景
1）不需要事务支持的业务（例如转账就不行）
2）一般为读数据比较多的应用，读写都频繁场景不适合，读多或者写多的都适合。
3）读写并发访问相对较低的业务（纯读纯写高并发也可以）（锁定机制问题）
4）数据修改相对较少的业务（阻塞问题）
5）以读为主的业务，例如：数据库系统表、www、blog图片信息数据库，用户数据库。商品库等业务
6）对硬件一致性要求不是非常高的业务（不支持事务）
7）硬件资源比较差的机器可以用MySAM（占用资源少）
8）使用读写分离的MySQL从库可以使用MyIASM
小结：单一对数据库的操作都可以使用MyIASM，所以单一就是尽量纯读，或纯写（insert，update，delete）等
InnoDB引擎使用的生产业务场景
1、需要事务支持的业务（具有较好的事务特性）
2、行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成
3、数据读写及更新都较为频繁的场景，如：BBS。SNA。微博，微信等。
4、数据一致性要求较高的业务，例如：充值转账、银行卡转账。
5、硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘IO

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 分组排序

解法一：初级解法（只适用于面试题，不适用于生产环境）
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
)