目录
建表语句
CREATE TABLE `user-test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(255) NOT NULL DEFAULT '' COMMENT '姓名',
`sex` tinyint(4) NOT NULL DEFAULT '0' COMMENT '性别',
`province` varchar(255) NOT NULL COMMENT '省份',
`city` varchar(255) NOT NULL COMMENT '城市',
`created_at` datetime NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_p_c` (`province`,`city`) USING BTREE COMMENT '省份城市'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4select
INSERT INTO `anna`.`orgs`(`id`, `name`, `code`, `pid`) VALUES (3, '浦东新区', '101103', 1);
INSERT INTO `anna`.`orgs`(`id`, `name`, `code`, `pid`) VALUES (4, '田林', '101102104', 2);
INSERT INTO `anna`.`orgs`(`id`, `name`, `code`, `pid`) VALUES (5, '徐家汇', '101102105', 2);
insert into 表名(字段1，字段2...) values(值1，值2，....)，(值1，值2，....)，(值1，值2，....);update
update 表名 set字段1 = 值1, 字段2 = 值2 where 条件left join
select * from user left join goods on user.gid=goods.gid;常用语句
show databases;查看所有的数据库create database test;创建一个叫test的数据库drop database test;删除一个叫test的数据库use test;选中库 ,在建表之前必须要选择数据库show tables;在选中的数据库之中查看所有的表desc 表名;查看所在的表的字段drop table 表名;删除表show create database 库名;查看创建库的详细信息show create table 表名;查看创建表的详细信息
#新增记录
insert into exam_record (`uid`,`exam_id`,`start_time`,`submit_time`,`score`) values
(1001,9001,"2021-09-01 22:11:12", "2021-09-01 23:01:12", 90),
(1002, 9002, "2021-09-04 07:01:02", NULL, NULL);#一张新表exam_record_before_2021用来备份2021年之前的试题作答记录，结构和exam_record表一致，请将2021年之前的已完成了的试题作答纪录导入到该表。
insert into exam_record_before_2021 (`uid`,`exam_id`,`start_time`,`submit_time`,`score`) select `uid`,`exam_id`,`start_time`,`submit_time`,`score` from exam_record where exam_record.submit_time <= "2021-01-01 00:00:00"#现在有一套ID为9003的高难度SQL试卷，时长为一个半小时，请你将 2021-01-01 00:00:00 作为发布时间插入到试题信息表examination_info（其表结构如下图），不管该ID试卷是否存在，都要插入成功，请尝试插入它。
replace into examination_info values (null,'9003','SQL','hard',90,'2021-01-01 00:00:00');REPLACE(str,from_str,to_str)
#请把examination_info表中tag为PYTHON的tag字段全部修改为Python。
update examination_info set tag = Replace(tag, "PYTHON", "Python");#请把exam_record表中2021年9月1日之前开始作答的未完成记录全部改为被动完成，即：将完成时间改为'2099-01-01 00:00:00'，分数改为0。
update exam_record set submit_time="2099-01-01 00:00:00", score=0 where start_time<="2021-09-01 00:00:00" and submit_time is NUllTIMESTAMPDIFF(MINUTE,减数,被减数);
#请删除exam_record表中作答时间小于5分钟整且分数不及格（及格线为60分）的记录；
delete from exam_record where score < 60 and submit_time is not NULL and TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5#请删除exam_record表中未完成作答或作答时间小于5分钟整的记录中，开始作答时间最早的3条记录。
delete from exam_record where TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5 or submit_time is NULL order by start_time limit 3#请删除exam_record表中所有记录，并重置自增主键。
truncate table exam_record定义唯一索引
unique key xxx(column)定义索引key xxx(column)
# 这里在写 索引的时候 语法又错了 要记得复习
create table if not exists user_info_vip(
`id` int not NULL auto_increment comment'自增ID',
`uid` int not NULL comment'用户ID',
`nick_name` varchar(64) comment'昵称',
`achievement` int DEFAULT 0 comment'成就值',
`level` int comment'用户等级',
`job` varchar(32) comment'职业方向',
`register_time` datetime DEFAULT CURRENT_TIMESTAMP comment'注册时间',
primary key(`id`),
unique key u_uid(`uid`) USING BTREE
)engine=InnoDB charset=utf8alter table Add/change/modify
#请在用户信息表，字段level的后面增加一列最多可保存15个汉字的字段school；并将表中job列名改为profession 10个字符，achievement的默认值设置为0。
alter table user_info Add Column `school` varchar(15) After `level`;
alter table user_info change `job` `profession` varchar(10);
alter table user_info modify `achievement` int(11) default 0;#exam_record_{YEAR}，{YEAR}为对应年份。 现在随着数据越来越多，存储告急，请你把很久前的（2011到2014年）备份表都删掉（如果存在的话）。
drop table if exists exam_record_2011;
drop table if exists exam_record_2012;
drop table if exists exam_record_2013;
drop table if exists exam_record_2014;#需要在examination_info表创建以下索引，规则如下：
#在duration列创建普通索引idx_duration、在exam_id列创建唯一性索引uniq_idx_exam_id、在tag列创建全文索引full_idx_tag。
alter table examination_info add KEY idx_duration(`duration`) using btree;
alter table examination_info add UNIQUE KEY uniq_idx_exam_id(`exam_id`) using btree;
alter table examination_info add FULLTEXT KEY full_idx_tag(`tag`);#请删除examination_info表上的唯一索引uniq_idx_exam_id和全文索引full_idx_tag。
#注意 这里是 drop key 不是 delete 也不用跟上 key的类型
alter table examination_info drop KEY uniq_idx_exam_id;
alter table examination_info drop KEY full_idx_tag;#在SQL类别中高难度试卷的得分情况。
#请你帮她从exam_record数据表中计算所有用户完成SQL类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）。
select i.tag, i.difficulty, round((sum(e.score) - min(e.score) - max(e.score))/(count(*)-2),1) clip_avg_score from exam_record e left join examination_info i on e.exam_id = i.exam_id where i.tag="SQL" and i.difficulty="hard" and e.score is not NULL 这里学习了if的用法 if(true,a,b) 类比PHP true?a:b
#从中统计出总作答次数total_pv、试卷已完成作答数complete_pv、已完成的试卷数complete_exam_cnt
select count(start_time) total_pv, count(score) complete_pv, count(distinct if(score is not null, exam_id, null)) complete_exam_cnt from exam_record子查询作为where语句中的一部分
#请从试卷作答记录表中找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。
select min(e.score) min_score_over_avg from exam_record e left join examination_info i on i.exam_id = e.exam_id where i.tag="SQL" and e.score is not null and e.score >= ( select sum(et.score)/count(et.score) avg from exam_record et left join examination_info it on it.exam_id = et.exam_id where it.tag="SQL" and et.score is not null)
count(distinct uid, date_format(start_time, "%Y%m%d")这里之前统计天数漏了 uid, 还漏了 2021年这个条件限制
#计算2021年每个月里试卷作答区用户平均月活跃天数avg_active_days和月度活跃人数mau
select date_format(start_time, "%Y%m") month, round((count(distinct uid, date_format(start_time, "%Y%m%d"))/count(distinct uid)),2) avg_active_days ,count(distinct uid) mau from exam_record where submit_time is not null and start_time >= "2021-01-01 00:00:00" group by monthcoalesce() 返回参数列表中的非null值 with rollup 在group分组字段的基础上再进行统计数据 date_format(时间，时间格式化) round(数字，保留几位小数)
select coalesce(date_format(submit_time, "%Y%m"), "2021汇总") month, count(uid) month_q_cnt , round(count(uid) / date_format(last_day(submit_time), "%d"),3) avg_day_q_cnt from practice_record where submit_time is not null and submit_time >= "2021-01-01 00:00:00" group by date_format(submit_time, "%Y%m") with rolluphaving concat GROUP_CONCAT
where 子句的作用是对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。
having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。
#CONCAT(str1,str2,...)
#GROUP_CONCAT([DISTINCT] expr [,expr ...]
# [ORDER BY {unsigned_integer | col_name | expr}
# [ASC | DESC] [,col_name ...]]
# [SEPARATOR str_val])
select e.uid, count(if(submit_time is null, 1, null)) as incomplete_cnt, count(submit_time) as complete_cnt, group_concat(distinct CONCAT(DATE_FORMAT(start_time, '%Y-%m-%d'),':',tag) separator ';') as detail from exam_record e left join examination_info i on e.exam_id = i.exam_id group by e.uid having incomplete_cnt < 5 and incomplete_cnt > 1 and complete_cnt >= 1 order by incomplete_cnt desc
子查询知识点 SELECT * FROM t2 WHERE t2.a IN (SELECT a FROM t3);
#月均完成试卷数不小于3的用户爱作答的类别及作答次数，按次数降序输出
SELECT i.tag, count(e.start_time) tag_cnt from exam_record e left join examination_info i on e.exam_id = i.exam_id where uid in (select uid from (select uid, month(submit_time) m, count(submit_time) ans_times from `exam_record` GROUP BY uid, m HAVING ans_times>=3) as uids) GROUP BY i.tag ORDER BY tag_cnt desc;取几位小数的函数是 round 不是 float 参考别人的答案可优化的点： 用date直接求日期 avg直接求平均值
#mysql> SELECT DATE('2003-12-31 01:02:03');
#-> '2003-12-31'
#AVG([DISTINCT] expr)
#请计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score，按人数降序，相同人数的按平均分升序，示例数据结果输出如下
select e.exam_id, count(DISTINCT e.uid) uv, round(sum(e.score)/count(e.score),1) avg_score from exam_record e LEFT JOIN examination_info i on i.exam_id = e.exam_id LEFT JOIN user_info u on u.uid = e.uid where u.level > 5 and DATE_FORMAT(e.start_time,"%Y%m%d") = DATE_FORMAT(i.release_time,"%Y%m%d") and i.tag = "SQL" GROUP BY e.exam_id ORDER BY uv desc, avg_score asc
#统计作答SQL类别的试卷得分大于过80的人的用户等级分布，按数量降序排序
select `level`, count(u.`level`) level_cnt from exam_record e left join examination_info i on e.exam_id = i.exam_id left join user_info u on u.uid = e.uid where i.tag = "SQL" and e.score > 80 GROUP BY u.`level` ORDER BY level_cnt desc