我家自动化

这个屌丝很懒，什么也没留下！

热门标签

AI测试SQL设计--常用SQL与基础知识总结_sql ai

作者：我家自动化 | 2024-04-06 06:38:01

踩

sql ai

左查询、内连接的区别 https://www.cnblogs.com/HKUI/p/8536969.html

=========================

慢查询原因：

1.没有索引或者没有用到索引

2.I/O吞吐量小，形成了瓶颈效应

3.没有创建计算列导致查询不优化

4.内存不足

5.网络速度慢

6.查询出的数据量过大（可以采取多次查询或者其他方式降低数据量）

7.锁或者死锁，程序设计的缺陷，原因是读写竞争资源

8.返回了不必要的行和列

9.查询语句不好，没有优化

优化查询方式：

1.把数据、日志、索引放到不同的I/O设备，增加读取速度，数据量越大，提高I/O越重要

2.纵向、横向分割表，减少表的尺寸

3.升级硬件

4.根据查询条件，建立索引，优化索引，优化访问方式，索引应该尽量小，使用字节小的列建索引好，不要对有限的几个值的字段建单一索引

5.提高网速

6.增加服务器CPU个数。但是必须明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MySQL自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例如单个查询的排序、连接、扫描和GROUP BY字句同时执行，SQL SERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作 UPDATE,INSERT，DELETE还不能并行处理。

7.如果是使用like进行查询的话，简单的使用index不行，全文索引耗空间

8.编写SQL时候需要注意与索引相关的规则，字段类型转换导致不用索引，如字符串类型的不用引号，数字类型的用引号等，这有可能会用不到索引导致全表扫描失败；不要使用select * 排序请尽量使用升序；or的查询尽量用union代替，rder by / group by 字段包括在索引当中减少排序，效率会更高。尽量规避大事物的SQL，大事物的SQL会影响数据库的并发性能以及主从同步；分页语句limit的问题，删除表所有记录用truncate不要用delete

mysql与redis的区别

1.mysql是关系型数据库，主要用于存放持久化数据，将数据存储在硬盘中，读取速度慢

redis是NOSQL，即非关系型数据库，也是缓存数据库，即将数据存储在缓存中，缓存的读取速度快，能够大大的提高运行效率，但是保存时间有限

2.mysql的运行机制

mysql作为持久化存储的关系型数据库，相对薄弱的地方在于每次请求访问数据库时，都存在着I/O操作。如果反复频繁的访问数据库第一会在反复连接数据库上花费大量时间，从而导致运行效率慢；第二反复的访问数据库也会导致数据库的负载过高，那么此时缓存的概念就衍生了出来

3.缓存

缓存就是数据交换的缓冲区cache，当浏览器执行请求时，首先会对在缓存中进程查找，如果存在，就获取，否则就访问数据库。缓存的好处就是读取速度快

4.redis数据库

就是一款缓存数据库，用于存储使用频繁的数据，这样减少数据库的次数，提高运行效率

5.redis和mysql的区别总结

（1）类型上

从类型上来说，mysql是关系型数据库，redis是缓存数据库

（2）作用上

mysql用于持久化的存储数据到硬盘，功能强大，但是速度较慢

redis用于存储使用较为频繁的数据到缓存中，读取速度快

（3）需求上

mysql和redis因为需求的不同，一般都是配合使用。

mysql是持久化存储，存放在磁盘里面，检索的话，会涉及到一定的IO，为了解决这个瓶颈，于是出现了缓存，比如现在用的最多的 memcached(简称mc)。首先，用户访问mc，如果未命中，就去访问mysql，之后像内存和硬盘一样，把数据复制到mc一部分。redis和mc都是缓存，并且都是驻留在内存中运行的，这大大提升了高数据量web访问的访问速度。然而mc只是提供了简单的数据结构，比如 string存储;redis却提供了大量的数据结构，比如string、list、set、hashset、sorted set这些，这使得用户方便了好多，毕竟封装了一层实用的功能，同时实现了同样的效果，当然用redis而慢慢舍弃mc。内存和硬盘的关系，硬盘放置主体数据用于持久化存储，而内存则是当前运行的那部分数据，CPU访问内存而不是磁盘，这大大提升了运行的速度，当然这是基于程序的局部化访问原理。推理到redis+mysql，它是内存+磁盘关系的一个映射，mysql放在磁盘，redis放在内存，这样的话，web应用每次只访问redis，如果没有找到的数据，才去访问Mysql。像memcache，MongoDB，Redis，都属于No sql系列。一般来说，写入数据是直接到mysql，读取类的是redis。这样就说 mysql->redis的同步用的比较多。 mysql作为数据持久化和管理比redis好太多，redis大多只用来做数据读取缓存、队列、锁、等等的使用。持久化的话也提供了rdb、aof 保证数据的实时不丢失。 redis和mysql要根据具体业务场景去选型

mysql：数据放在磁盘
redis：数据放在内存 redis适合放一些频繁使用，比较热的数据，因为是放在内存中，读写速度都非常快，一般会应用在下面一些场景
排行榜
计数器--业务需求中经常有需要用到计数器的场景：一个手机号一天限制发送5条短信、一个接口一分钟限制多少请求、一个接口一天限制调用多少次等等。使用Redis的Incr自增命令可以轻松实现以上需求。以一个接口一天限制调用次数为例

消息队列推送

联合索引 id type channel 各种组合查询哪种查询最快，有什么区别

mysql索引原理：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

https://blog.csdn.net/liutong123987/article/details/79384395

id最快。原因：id是主键，mysql联合索引是分级查询的，id,type,channel组成联合索引，则id是一级索引，type为二级索引，channel为三级索引。查询时先命中一级索引，就区分度而言，id所有值是唯一的，对于b+树而言，可以最快的找到其值。然后type和channel，根据业务需求，看哪个区分度高，如果type有100个枚举值，channel只有10个枚举值，则type区分度更高，所有用type做二级索引，channel做三级索引

mysql 严格模式下对空值的控制较严，比如时间类型的值当插入数据时如果复制空可能会导致数据插入失败

ioc包中GRPCJudgeClient 是用来校验权限的rpc服务客户端，在gRPC接口文档中有介绍需要校验权限的接口

show variables like 'character_set%'; ------------查看默认字符集创建database时记得指定字符集

------------临时修改字符集

set character_set_database=utf8;

set character_set_client=utf8;

set character_set_connection=utf8;

set character_set_results=utf8;

mysql -h10.102.1.13 -uterritory -pvuoh4oPIb85k5lovToXP -Dterritory ---------测试环境数据库

mysql -h10.102.1.13 -ujudge -pbpqJQYNGOxEEQSD1FhTc -Djudge --------注意-u -p 后面带的是用户名和密码并且中间没有空格

mysql -hhub.meiqia.com-devops-mysql -uroot -p -Dterritory_test

/etc/init.d/mysql start

error 1055:

select version(), @@sql_mode;

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

error 1205 Lock wait timeout exceeded

show full processlist;

然后kill掉相应线程ID

mysql 分页查询时，如果limit 10000,10 会扫描 10010行数据，可以在SQL中添加ID排序并计算出要扫面的起始，

比如 limit 10000,10 变为 auto_id > 10000 limit 0, 20

select distinct ui.user_id as 用户id,ui.mobile as 手机号,ui.user_name 学生姓名,c.class_name as 班级名称,ut.user_name as 老师姓名,

cci.course_name as 课程名称,cci.course_id as 课程ID,c.class_id as 班级ID,c.teacher_id as 老师ID

from online_user_info ui

inner join online_class_student cs on ui.user_id=cs.student_id

inner join online_class_info c on cs.class_id=c.class_id

inner join online_user_info ut on c.teacher_id=ut.user_id

inner join online_student_course sc on sc.student_id=ui.user_id

inner join online_student_course osc on osc.student_id=ui.user_id

inner join cms_course_info cci on cci.course_id=osc.course_id

where osc.student_course_id in (24905,23896,24667,25234,25218,25217,25258,23976,23976,22287)

and

ui.user_id in (11791,6037,3785,3796,5950, 9596, 6496, 5103, 6382)

order by cci.course_id asc

update online_user_info set password=MD5(concat(password,'zyhzAiClass')) where user_id=55;

//密码md5加密
SELECT MD5(concat('666666','zyhzAiClass'))
SELECT MD5(concat('123456','zyhzAiClass'))

update online_user_info set password='d8bc1ca06b9f0e643dbb9e7eb8e71fe4' WHERE user_id>2

//INSERT INTO online_user_info (mobile,user_name,actor) VALUES ('17600859365','佟玲','1');
//INSERT INTO online_user_info (mobile,user_name,actor) VALUES ('17600859365','老师_佟玲','2');

//根据学生手机号查询老师手机号

SELECT * FROM online_user_info WHERE user_id in
(SELECT teacher_id FROM online_class_info WHERE class_id in
(SELECT class_id FROM online_class_student WHERE student_id in
(SELECT user_id FROM online_user_info WHERE mobile='15352215650' AND actor=1)))

SELECT * FROM online_student_question WHERE student_id in (SELECT user_id FROM online_user_info WHERE mobile='13100000046' AND actor=1)

//根据老师手机号查询班级
SELECT * FROM online_class_info WHERE teacher_id in
(SELECT user_id FROM online_user_info WHERE mobile='17600859365' AND actor=2)
//根据学生账号查询老师的class_id、term_id

SELECT * FROM online_class_info WHERE class_id in
(SELECT class_id FROM online_class_student WHERE student_id in
(SELECT user_id FROM online_user_info WHERE mobile='15352215650' AND actor=1))

//根据老学生账号查询老师course_id、class_id

SELECT * FROM online_class_course WHERE teacher_id in
(SELECT teacher_id FROM online_class_info WHERE class_id in
(SELECT class_id FROM online_class_student WHERE student_id in
(SELECT user_id FROM online_user_info WHERE mobile='15352215650' AND actor=1)))

//根据班级查询某个班级的所有学生
SELECT * FROM online_user_info WHERE user_id in (SELECT student_id FROM online_class_student WHERE class_id in (128))
SELECT COUNT(*) FROM online_user_info WHERE user_id in (SELECT student_id FROM online_class_student WHERE class_id in (128))

SELECT * FROM online_class_course WHERE class_id=37

//SELECT * FROM online_class_student (SELECT user_id FROM online_user_info WHERE mobile='18701669960' AND actor='2')

//根据老师账号查询课程信息
SELECT * FROM cms_course_info WHERE course_id in
(SELECT course_id FROM online_class_course WHERE teacher_id in
(SELECT user_id FROM online_user_info WHERE mobile='18701669960' AND actor='2')) AND type = '0'

//根据老师账号查询学生账号
SELECT * FROM online_user_info WHERE user_id in
(SELECT student_id FROM online_class_student WHERE class_id in
(SELECT class_id FROM online_class_course WHERE teacher_id in
(SELECT user_id FROM online_user_info WHERE mobile='15352215650')))

//根据老师账号查询class_id、course_id、term_id信息
SELECT * FROM online_class_course WHERE teacher_id in
(SELECT user_id FROM online_user_info WHERE mobile='15922646729')

// "userId": 165 tremId为1 classId为101 "status": 0,默认创建的新课程。删除了169 临时老师"userId": 198

SELECT * FROM online_user_info ORDER BY create_time DESC LIMIT 5;

SELECT * FROM online_class_info ORDER BY create_time DESC LIMIT 5;
SELECT * FROM online_class_course ORDER BY create_time DESC LIMIT 5;
SELECT * FROM online_user_info WHERE actor='1' AND create_time>="2018-04-04 15:09:32" ORDER BY create_time DESC LIMIT 30;
SELECT * FROM online_student_info WHERE create_time>="2018-04-04 15:09:32" ORDER BY create_time DESC LIMIT 30;
SELECT * FROM online_class_student WHERE create_time>="2018-04-04 15:09:32" ORDER BY create_time DESC LIMIT 30;

SELECT * FROM online_user_info WHERE mobile='15352215650' ORDER BY create_time DESC LIMIT 5;
SELECT user_id FROM online_user_info WHERE (actor = 1 AND mobile in (15352215650))

SELECT student_id,class_id FROM online_class_student WHERE (student_id in (169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192))

SELECT * FROM online_class_student WHERE class_id='101' ORDER BY create_time DESC LIMIT 30;

SELECT * FROM online_student_course_statis WHERE student_id in
(SELECT user_id FROM online_user_info WHERE mobile='13312813055')

SELECT * FROM online_student_course WHERE student_id in
(SELECT user_id FROM online_user_info WHERE mobile='13312813055')

SELECT * FROM online_student_course_statis WHERE (course_id = 12 AND student_id = 144)

SELECT * FROM online_class_student WHERE class_id='110'

SELECT * FROM online_student_course WHERE (student_id = 301) ORDER BY create_time desc

SELECT * FROM online_student_course_statis WHERE (course_id = 2 AND student_id = 301)

create table online_student_course_temp as select * from online_student_course;
delete from online_student_course where student_course_id in
(
select student_course_id from online_student_course_temp a,
(
select student_id, course_id, class_id, term_id
from online_student_course_temp
group by student_id, course_id, class_id, term_id
having count(*) > 1
) b
where a.class_id = b.class_id
and a.course_id = b.course_id
and a.student_id = b.student_id
and a.term_id = b.term_id
and a.accuracy = 0
and a.rank = 0
and a.score = 0
and a.status = 0
and a.diamond = 0
and a.seat = 0
);
drop table online_student_course_temp;

SELECT * FROM online_user_info WHERE (user_id = 301)

SELECT course_id,expect_score,control_score FROM
online_student_course_statis WHERE (student_id = 301) ORDER BY create_time asc

SELECT status,student_id,course_id,term_id,class_id,need_supply,supply_status,supply_begin_time,supply_end_time,normal_score,hide_score,normal_rank,supply_score,class_avg_score,class_max_score,create_time,supply_expect_time,student_count FROM online_student_course
WHERE (student_id = 301) ORDER BY create_time desc

SELECT course_id,course_name,type,grade,map_url,mis_section,mis_section_id,create_time,status,major_ability_ids,update_time FROM
cms_course_info WHERE (course_id in (2,1,24,9,27))

SELECT * FROM online_student_course WHERE student_id in (
SELECT user_id FROM online_user_info WHERE mobile='18911111111')

//家长端121测试SQL设计

//student_id=371 class_id=120 course_id=25
DESC online_student_course

//学生端课程详情页，得分
SELECT * FROM online_student_course WHERE student_id in
(SELECT user_id FROM online_user_info WHERE mobile='15352215650' AND actor=1)

SELECT user_id FROM online_user_info WHERE mobile='15352215650' AND actor=1

SELECT * FROM online_student_cell WHERE (student_id = 371 AND class_id = 120 AND course_id = 25 AND question_id != 0) ORDER BY create_time asc

//学生端获得积分
SELECT SUM(score) FROM online_student_cell WHERE (student_id = 371 AND class_id = 120 AND course_id = 25 AND question_id != 0) ORDER BY create_time asc

SELECT * FROM online_course_node WHERE (course_id =25 AND student_id = 371 AND term_id = 2 )

//学生端正确率
//cms_course_node表
//node_type '节点类型，1动画，2知识切片，3知识点，4例题，5练习，6影藏，7BUFF，8例题',
SELECT * FROM online_course_node WHERE course_id =25 AND student_id = 371 AND term_id = 2 AND node_type in(3,4)

//隐藏节点正确率
SELECT * FROM online_course_node WHERE course_id =25 AND student_id = 371 AND term_id = 2 AND node_type =5 and course_node_id>=3154789

SELECT avg(accuracy) FROM online_course_node WHERE course_id =25 AND student_id = 371 AND term_id = 2 AND node_type =5

SELECT COUNT(*) FROM online_course_node WHERE course_id =25 AND student_id = 371 AND term_id = 2 AND node_type in(3,4)

SELECT * FROM cms_course_node WHERE course_id =25 AND node_type in(3,4)

SELECT * FROM cms_course_node WHERE course_id =25

SELECT 100.0/20
SELECT 100.0/6

SELECT SUM(accuracy) FROM online_course_node WHERE (course_id =25 AND student_id = 371 AND term_id = 2 )

SELECT * FROM online_user_info WHERE mobile='15922646730'

SELECT * FROM cms_knowledge_info WHERE (know_id in (240,265,266,269,0,243,247,255,254,249,248,260,258,263,261,252,250,251,274,256,262))

SELECT 85/3

SELECT course_id,class_id,expect_score,control_score,score,beyond_percent,learn_depth,ability_level FROM online_student_course_statis WHERE (student_id = 371) ORDER BY create_time asc

SELECT MD5("Z")

SELECT * FROM online_user_info WHERE user_id in (SELECT student_id FROM online_student_course WHERE class_id=37)

SELECT * FROM online_user_info WHERE mobile='18514618850' AND actor=2

//线上数据设置为不可登录
UPDATE online_user_info SET mobile = concat(2, substring(mobile, 2))
UPDATE online_user_info SET user_name = concat('脏数据', user_name)

create table online_class_info_temp as select * from online_class_info;
update online_class_info set capacity = (
select (ceil(max(student_count) / 10)) * 10 from online_class_info_temp
);
drop table online_class_info_temp;

alter table cms_cell_info modify column image varchar(128) NOT NULL DEFAULT '';
alter table cms_question_info add column question_name varchar(32) not null default '未命名' after question_id;
alter table cms_resource_upload add column status tinyint(2) not null default 0 comment "0 视频未转码 1 转码中 2 转码成功 3 转码失败" after tx_vod_urls;

//运营后台1.0.0

SELECT * FROM online_user_info WHERE (actor = 1) ORDER BY create_time DESC LIMIT 0,10
SELECT count(1) as count FROM online_user_info WHERE (actor = 1)

SELECT * FROM online_user_info WHERE user_id in (SELECT student_id FROM online_student_course WHERE class_id=110)

SELECT count(1) as count FROM online_class_student WHERE (class_id = 34)

SELECT * FROM online_class_student WHERE (class_id = 37)

SELECT * FROM online_user_info WHERE user_id in (SELECT student_id FROM online_class_student WHERE class_id not in (SELECT class_id FROM online_class_info))

SELECT * FROM online_student_info WHERE student_id in (SELECT student_id FROM online_student_course WHERE class_id not in (SELECT class_id FROM online_class_info))

//创建学生老师班级加班流程。如果此学生已在别的班级存在，删除
//用户信息表
SELECT * FROM online_user_info ORDER BY user_id DESC LIMIT 20
SELECT class_id,student_id,create_time,update_time,class_student_id FROM online_class_student WHERE (student_id = 298)

SELECT * FROM online_user_info WHERE mobile='13810034464'
//查找学生班级class_id
SELECT * FROM online_student_course WHERE student_id in (SELECT user_id FROM online_user_info WHERE mobile in ('15352215650' , '15352215651') AND actor=1)
//FE徐奇
SELECT GROUP_CONCAT(user_id) FROM online_user_info WHERE mobile='13146122297'
SELECT * FROM online_user_info WHERE mobile='13146122297' AND actor=1

SELECT * FROM online_student_course WHERE student_id in (SELECT user_id FROM online_user_info WHERE mobile in ('13146122297') AND actor=1)

//用户、班级表
SELECT a.class_id,a.class_name,a.student_count,b.user_id,b.user_name,b.mobile FROM online_class_info AS a RIGHT JOIN online_user_info as b on a.teacher_id=b.user_id WHERE b.mobile='13146122297'

//班级学生表
SELECT * FROM online_class_student WHERE (student_id = 384)
//学生信息表
SELECT * FROM online_student_info WHERE student_id in (SELECT user_id FROM online_user_info WHERE mobile='13146122297' AND actor=1)

//FE翁志松
SELECT * FROM online_user_info WHERE mobile in ('18514618850','13810034464')
SELECT a.class_id,a.class_name,a.student_count,b.user_id,b.user_name,b.mobile FROM online_class_info AS a RIGHT JOIN online_user_info as b on a.teacher_id=b.user_id WHERE b.mobile in ('18514618850','13810034464')
SELECT * FROM online_student_course WHERE student_id in (SELECT user_id FROM online_user_info WHERE mobile in ('18514618850','13810034464')AND actor=1)

//FE冀峰

SELECT * FROM online_user_info WHERE mobile in ('15011516845','15011516846','15011516847','15011516848')
SELECT a.class_id,a.class_name,a.student_count,b.user_id,b.user_name,b.mobile FROM online_class_info AS a RIGHT JOIN online_user_info as b on a.teacher_id=b.user_id WHERE b.mobile in ('15011516845','15011516846','15011516847','15011516848')

//FE田岳
SELECT * FROM online_user_info WHERE mobile in ('18600928237')
SELECT a.class_id,a.class_name,a.student_count,b.user_id,b.user_name,b.mobile FROM online_class_info AS a RIGHT JOIN online_user_info as b on a.teacher_id=b.user_id WHERE b.mobile in ('18601013193')

SELECT * FROM online_user_info ORDER BY user_id desc LIMIT 50

SELECT * FROM online_user_info WHERE (actor = 1) ORDER BY create_time DESC LIMIT 0,100

SELECT count(1) as count FROM online_user_info WHERE (actor = 2)

SELECT * FROM online_class_course WHERE (class_id = 49) ORDER BY start_time asc

SELECT * FROM online_class_course WHERE (course_id = 2 AND status = 0 AND term_id = 2 AND class_id != 49)

SELECT * FROM online_class_info WHERE (class_id in (47,44,43,45,46))

SELECT * FROM online_class_course WHERE (class_id = 49 AND status = 0) ORDER BY start_time asc

UPDATE online_class_info SET capacity=6 WHERE capacity=0

SELECT * FROM online_class_course WHERE (class_id = 63) ORDER BY start_time asc
SELECT * FROM online_class_course WHERE (course_id = 22 AND status = 0 AND term_id = 5 AND class_id != 63)

SELECT student_id FROM online_class_student WHERE (student_id = 423 AND class_id = 63)
SELECT class_id,class_name,student_count,capacity,term_id FROM online_class_info WHERE (class_id in (63,50))

SELECT start_time,end_time,teacher_id,student_count,course_id,term_id,status,create_time,update_time,class_course_id,class_id FROM online_class_course WHERE (class_id in (63,50) AND term_id = 5 AND status = 0 AND start_time > '2018-05-24 17:07:04') ORDER BY start_time asc

SELECT update_time,class_course_id,teacher_id,course_id,student_count,end_time,class_id,term_id,start_time,status,create_time FROM online_class_course WHERE (class_id = 63) ORDER BY start_time asc

SELECT * FROM online_class_course WHERE (class_id = 63)

SELECT * FROM online_class_course WHERE (course_id = 22 AND status = 0 AND term_id = 5 AND class_id != 63)
SELECT * FROM online_user_info WHERE user_id in (SELECT teacher_id FROM online_class_info WHERE class_id=63)

SELECT * FROM online_user_info WHERE user_id=394

//UPDATE online_class_info SET capacity=0 WHERE course_start_time ='0000-00-00 00:00:00' OR course_end_time= '0000-00-00 00:00:00'

alter table cms_course_node add column position_x int(4) not null default '0' COMMENT '节点渲染横坐标';
alter table cms_course_node add column position_y int(4) not null default '0' COMMENT '节点渲染纵坐标';

// node_type tinyint(4) NOT NULL DEFAULT '0' COMMENT '节点类型，1动画，2知识切片，3知识点，4例题，5练习，6影藏，7BUFF，8例题'
SELECT * FROM online_course_node WHERE student_id in (SELECT user_id FROM online_user_info WHERE mobile=15352215651)

SELECT * FROM online_course_node WHERE student_id in (SELECT user_id FROM online_user_info WHERE mobile='15352215652')

SELECT * FROM online_student_course WHERE student_id in (SELECT user_id FROM online_user_info WHERE mobile='15352215653')

SELECT user_id FROM online_user_wx WHERE mobile='15352215651'
SELECT * FROM online_user_wx WHERE user_id=454

alter table online_student_info add column score int(11) unsigned NOT NULL DEFAULT '0' COMMENT '累计积分' after diamond;

SELECT * FROM online_user_info WHERE user_id in (SELECT student_id FROM online_class_student WHERE class_id in (SELECT class_id FROM online_class_info WHERE class_id=37))

SELECT * FROM online_user_info WHERE user_id in (SELECT user_id FROM online_class_info WHERE class_id='37')

//UPDATE online_user_info SET user_name = concat('QA', user_name) WHERE user_id BETWEEN 466 AND 476

SELECT * FROM online_user_info WHERE user_id BETWEEN 466 AND 476

create table Atemp as select * from A;
update A ta, (
select t.a, B.c
from Atemp t, B
where t.a = b.b
) tb
set ta.a = tb.c
where ta.a = tb.a;
drop table Atemp;

//UPDATE cms_course_info set map_url=
(SELECT b.url FROM cms_course_info as a INNER JOIN cms_resource_upload as b ON a.map_url=b.desc ORDER BY a.course_id desc LIMIT 1)
ORDER BY course_id desc LIMIT 1

create table Atemp as select * from cms_course_info;
update cms_course_info A, (
select Atemp.map_url, B.url
from Atemp, cms_resource_upload B
where Atemp.map_url = B.desc
) tb
set A.map_url = tb.url
where A.map_url = tb.map_url;
drop table Atemp;

SELECT * FROM online_student_course WHERE (student_id = 520 AND term_id = 12 AND course_id = 25)

SELECT * FROM online_user_info WHERE user_name ='T老师_QA李杉5619'

//查询某班级下所有学生的答题分数记录
SELECT * FROM online_student_course WHERE student_id in(SELECT user_id FROM online_user_info WHERE user_id in (SELECT student_id FROM online_class_student WHERE class_id = 84)
) AND term_id = 12 AND course_id = 25

SELECT COUNT(*) FROM online_student_course WHERE student_id in(SELECT user_id FROM online_user_info WHERE user_id in (SELECT student_id FROM online_class_student WHERE class_id = 84)
) AND term_id = 12 AND course_id = 25

//根据班级查找学生手机号

SELECT * FROM online_user_info WHERE user_id in (SELECT student_id FROM online_class_student WHERE class_id = 86)

SELECT * FROM online_class_student WHERE student_id =(SELECT user_id FROM online_user_info WHERE mobile='15352215666' )

SELECT * FROM online_user_info WHERE user_name like '%老子%'

SELECT * FROM online_user_info WHERE mobile like '%0002'

SELECT * FROM `online_student_question` WHERE (`student_id` = 545 AND `class_id` = 87 AND `course_id` = 25)

SELECT * FROM `online_student_homework` WHERE (`student_id` = 545 AND `class_id` = 87 AND `course_id` = 25)

SELECT `course_id` FROM `online_class_course` WHERE (`class_id` = 87 AND `status` = 4) ORDER BY start_time desc

//查看带批改的作业

SELECT * FROM `online_student_question` WHERE (`class_id` = 87 AND `course_id` = 25 AND `student_id` = 543 AND `image_origin` != '' AND `need_correct` = 1 AND `correct_status` = 0 AND `source` = 0) ORDER BY create_time asc

// DELETE FROM online_student_homework WHERE student_id in (SELECT user_id FROM online_user_info WHERE mobile='15352215688')

// DELETE FROM `online_student_question` WHERE student_id in (SELECT user_id FROM online_user_info WHERE mobile='15352215689')

SELECT * FROM online_student_homework WHERE student_id in (SELECT user_id FROM online_user_info WHERE mobile in('15352215655'))
SELECT * FROM `online_student_question` WHERE student_id in (SELECT user_id FROM online_user_info WHERE mobile='15352215656')

SELECT `question_id`,`need_correct`,`image_origin` FROM `online_student_question` WHERE (`homework_id` = 48)

SELECT `homework_id`,`status` FROM `online_student_homework` WHERE (`student_id` = 544 AND `class_id` = 87 AND `course_id` = 25)

SELECT * FROM `online_student_question` WHERE student_id in (SELECT user_id FROM online_user_info WHERE mobile='153522156777')

SELECT `question_id`,`answer`,`is_right`,`image_origin`,`image_correction`,`comment_voice`,`comment`,`score` FROM `online_student_question` WHERE (`student_id` = 547 AND `class_id` = 87 AND `course_id` = 25)

SELECT GROUP_CONCAT(course_question_id) FROM cms_course_question ORDER BY create_time DESC LIMIT 5

SELECT * FROM online_class_student WHERE student_id =(SELECT user_id FROM online_user_info WHERE mobile='15613565665' )

SELECT user_id FROM online_user_info WHERE mobile in('15613565665','15613565666','15613565667')

SELECT * FROM online_class_course WHERE class_id=85

SELECT * FROM cms_course_info WHERE course_id in (46,25)

SELECT * FROM cms_course_node WHERE course_id in (46,25)

SELECT 14/15

select * from online_student_homework where student_id=704;

select * from online_student_question where student_id=704;
-- DELETE from online_student_question where student_id=704;

//考试系统-家长端1.4.0

SELECT * FROM online_student_course_ability WHERE class_id=96

SELECT user_id FROM online_user_info WHERE mobile='15613565666' AND actor=1

SELECT * FROM online_class_course WHERE class_id=96
SELECT * FROM online_class_info WHERE class_id=96

SELECT * FROM online_student_course WHERE class_id=96

select * from `online_course_node` where course_id=48 and student_id in (443); -- 家长端计算上课时间（每打完一题就会增加一数据）

SELECT user_id FROM online_user_info WHERE mobile='13126810007' AND actor=1

SELECT * FROM online_user_info WHERE mobile like "153%"

SELECT * FROM `strategy_score` WHERE `term_id` = 14

//学生上课纪录表

SELECT * FROM online_student_course WHERE student_id in (SELECT user_id FROM online_user_info WHERE mobile in ('15613565665','15613565666'))

SELECT * FROM online_student_course WHERE student_id in (SELECT user_id FROM online_user_info WHERE mobile in ('15613565668'))

SELECT * FROM online_student_cell WHERE class_id='125' AND student_id in
(SELECT user_id FROM online_user_info WHERE mobile in ('15613565665')) AND course_id=46
ORDER BY start_time DESC

SELECT * FROM online_user_info WHERE mobile='15613565665'

SELECT 71/(SELECT COUNT(*) FROM online_user_info WHERE user_id in
(SELECT student_id FROM online_class_student WHERE class_id in
(SELECT class_id FROM online_class_course WHERE teacher_id in
(SELECT user_id FROM online_user_info WHERE mobile='15922646729'))))

//家长端学生平均成绩
SELECT (SELECT SUM(normal_score) FROM online_student_course WHERE student_id in (SELECT user_id FROM online_user_info WHERE user_id in
(SELECT student_id FROM online_class_student WHERE class_id in
(SELECT class_id FROM online_class_course WHERE teacher_id in
(SELECT user_id FROM online_user_info WHERE mobile='15922646729')))))/(SELECT COUNT(*) FROM online_user_info WHERE user_id in
(SELECT student_id FROM online_class_student WHERE class_id in
(SELECT class_id FROM online_class_course WHERE teacher_id in
(SELECT user_id FROM online_user_info WHERE mobile='15922646729'))))

DBA 架构师之路

一、基础规范

表存储引擎必须使用InnoDB

表字符集默认使用utf8，必要时候使用utf8mb4

解读：

（1）通用，无乱码风险，汉字3字节，英文1字节

（2）utf8mb4是utf8的超集，有存储4字节例如表情符号时，使用它

禁止使用存储过程，视图，触发器，Event

解读：

（1）对数据库性能影响较大，互联网业务，能让站点层和服务层干的事情，不要交到数据库层

（2）调试，排错，迁移都比较困难，扩展性较差

禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统，数据库中存储路径
禁止在线上环境做数据库压力测试
测试，开发，线上数据库环境必须隔离

二、命名规范

库名，表名，列名必须用小写，采用下划线分隔

解读：abc，Abc，ABC都是给自己埋坑

库名，表名，列名必须见名知义，长度不要超过32字符

解读：tmp，wushan谁TM知道这些库是干嘛的

库备份必须以bak为前缀，以日期为后缀
从库必须以-s为后缀
备库必须以-ss为后缀

三、表设计规范

单实例表个数必须控制在2000个以内
单表分表个数必须控制在1024个以内
表必须有主键，推荐使用UNSIGNED整数为主键

潜在坑：删除无主键的表，如果是row模式的主从架构，从库会挂住

禁止使用外键，如果要保证完整性，应由应用程式实现

解读：外键使得表之间相互耦合，影响update/delete等SQL性能，有可能造成死锁，高并发情况下容易成为数据库瓶颈

建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据

解读：具体参加《如何实施数据库垂直拆分》

四、列设计规范

根据业务区分使用tinyint/int/bigint，分别会占用1/4/8字节
根据业务区分使用char/varchar

解读：

（1）字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高

（2）字段长度相差较大，或者更新较少的业务场景，适合使用varchar，能够减少空间

根据业务区分使用datetime/timestamp

解读：前者占用5个字节，后者占用4个字节，存储年使用YEAR，存储日期使用DATE，存储时间使用datetime

必须把字段定义为NOT NULL并设默认值

解读：

（1）NULL的列使用索引，索引统计，值都更加复杂，MySQL更难优化

（2）NULL需要更多的存储空间

（3）NULL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in时有大坑

使用INT UNSIGNED存储IPv4，不要用char(15)

使用varchar(20)存储手机号，不要使用整数

解读：

（1）牵扯到国家代号，可能出现+/-/()等字符，例如+86

（2）手机号不会用来做数学运算

（3）varchar可以模糊查询，例如like ‘138%’

使用TINYINT来代替ENUM

解读：ENUM增加新值要进行DDL操作

五、索引规范

唯一索引使用uniq_[字段名]来命名
非唯一索引使用idx_[字段名]来命名
单张表索引数量建议控制在5个以内

解读：

（1）互联网高并发业务，太多索引会影响写性能

（2）生成执行计划时，如果索引太多，会降低性能，并可能导致MySQL选择不到最优索引

（3）异常复杂的查询需求，可以选择ES等更为适合的方式存储

组合索引字段数不建议超过5个

解读：如果5个字段还不能极大缩小row范围，八成是设计有问题

不建议在频繁更新的字段上建立索引
非必要不要进行JOIN查询，如果要进行JOIN查询，被JOIN的字段必须类型相同，并建立索引

解读：踩过因为JOIN字段类型不一致，而导致全表扫描的坑么？

理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,c)，相当于建立了(a), (a,b), (a,b,c)

六、SQL规范

禁止使用select *，只获取必要字段

解读：

（1）select *会增加cpu/io/内存/带宽的消耗

（2）指定字段能有效利用索引覆盖

（3）指定字段查询，在表结构变更时，能保证对应用程序无影响

insert必须指定字段，禁止使用insert into T values()

解读：指定字段插入，在表结构变更时，能保证对应用程序无影响

隐式类型转换会使索引失效，导致全表扫描

禁止在where条件列使用函数或者表达式

解读：导致不能命中索引，全表扫描

禁止负向查询以及%开头的模糊查询

解读：导致不能命中索引，全表扫描

禁止大表JOIN和子查询
同一个字段上的OR必须改写问IN，IN的值必须少于50个
应用程序必须捕获SQL异常

解读：方便定位线上问题

说明：本军规适用于并发量大，数据量大的典型互联网业务，可直接带走参考，不谢。

军规练习：为什么下列SQL不能命中phone索引？

今天也遇到了一个问题，一条简单的查询让CPU满负荷了，原因是在大数据量的情况下没有合理的使用索引，期待来一篇索引正确使用的文章哈

踩过的坑多，经验值才涨。欢迎大家分享踩过的坑，我后续汇总出来share，共同进步。
不用外键不是数据库性能问题，除非不做完整性检查。不用外键，其它技术方案数据库压力只怕更大！不用外键，怕是为了以后分库分表留下伏笔。外键不能夸库

我们公司基本是这规则下做事，踩过的坑，血的教训得出的结论，必须支持

join对数据库性能影响较大，CPU计算能放到上游就放到上游

row格式下主库表无主键delete的坑踩过，这个坑好大，从库舜间宕住了
鼓励用户不要把所有的事务逻辑都用 web 应用程序（即用户的脚本）来实现。最好用视图（view）、触发器（trigger）或者规则（rule）在数据库层面完成。 php手册 http://php.net/manual/zh/security.database.design.php 和第一条第三小项禁止视图等冲突运维新手
字段类型不一致的坑踩过多次，大部分是跨数据库（跨应用）查询导致。一些sql的规范还是蛮实用的。还有就是不要写存储过程，视图，触发器主要是为了迁移数据库方便，特别是当你想把现有的数据库（比方说oracle）换到其他数据库（sql server, mysql...)就知道有多痛苦了。但是很多程序员为了省事（或者更好的性能）还是会留坑。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家自动化/article/detail/370343