个人数据库类知识整理

数据库知识汇总

1.表字段操作

timestamp数据类型

Mysql数据库中，如果时间设置默认值，如果默认值取CURRENT_TIMESTAMP，则在insert一条记录时，此时的值自动设置为系统当前时间，一般做创建字段用(create_time)

如果是　ON UPDATE CURRENT_TIMESTAMP　，则时间字段会随着update命令进行实时更新，即当此条数据的其他字段发生变化时，此时字段的值自动更新为最新的时间，一般做更新字段用(update_time)。

#对表操作（包含crud）

1.删除表操作

Drop table 表名称;
truncate table 表名称;
1
2

2.查看表结构

desc 表名称;
1

3.修改表操作: ALTER TABLE 语句 用于在已有的表中添加、修改或删除列。

alter table 表名 add 字段名 类型(长度) [约束]              #添加列
alter table 表名 drop 列名                               #删除列
alter table 表名 modify 要修改的字段名 类型(长度) [约束]     #修改列的类型(长度、约束)
alter table 表名 change 旧列名 新列名 类型(长度) [约束]      #修改列的列名
1
2
3
4

4.修改表名

rename table 表名 to 新表名
1

5.删除表

drop table 表名         #删除表不保留任何信息
truncate (table) 表名   #删除表中的数据，保留表的结构
delete from 表名
1
2
3

对表内容整理

5.插入记录

insert into 表名(列名1,列名2,列名3……) values(值1,值2,值3……)
insert into 表名 values(值1,值2,值3……)
1
2

6.修改数据

update 表名 set 字段名=值, 字段名=值, 字段名=值……                   #不带条件
update 表名 set字段名=值, 字段名=值, 字段名=值…… where 条件          #带条件
1
2

7.删除表记录

delete from 表名 where 条件  
1

面试题

1. 说说delete与truncate的区别？

• delete删除的时候是一条一条的删除记录，它配合事务，可以将删除的数据找回。
• truncate删除，它是将整个表摧毁，然后再创建一张一模一样的表。它删除的数据无法找回。

sql练习

1.修改数据库表名字

alter table titles_test RENAME to titles_2017
1

2.查询字段然后去重(distinct 不同的)

SELECT DISTINCT university  from user_profile
1

3. 查询结果限制返回行数 (limit限制)

select device_id from user_profile limit 2
1

4.现在你需要查看2个用户明细设备ID数据，并将列名改为 'user_infors_example’

select device_id as user_infors_example from user_profile limit 2
1

5.现在运营想要针对20岁及以上且23岁及以下的用户开展分析 (BETWEEN)

select device_id,gender,age from user_profile where age BETWEEN 20 and 23
1

6.现在运营想要查看除复旦大学以外的所有用户明细 (NOT IN(‘复旦大学’))

1. select device_id,gender,age,university from user_profile where university !='复旦大学'
2. select device_id,gender,age,university from user_profile where university NOT IN ('复旦大学');
1
2

7.查看所有大学中带有北京的用户的信息

SELECT device_id,age,university from user_profile where university like '北京%'
1

8.男性用户有多少人以及他们的平均gpa是多少

SELECT COUNT(gender),round(AVG(gpa),1) from user_profile where gender in ('male')
#保留一位小数，round函数，round(a,b)返回a值保留到小数点b位
1
2

9.请取出平均发贴数低于5的学校或平均回帖数小于20的学校(having)

SELECT university ,avg(question_cnt),avg(answer_cnt) from user_profile group by university 
having avg(question_cnt)< 5 or avg(answer_cnt)<20
#聚合函数结果作为筛选条件时，不能用where，而是用having语法
1
2
3

10.查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列 (升序：ASC ; 降序：DESC)

select university,avg(question_cnt) as avg_question_cnt from user_profile
GROUP by university ORDER BY avg_question_cnt asc
1
2

11.查看所有来自浙江大学的用户题目回答明细情况

select device_id,question_id,result from question_practice_detail WHERE 
device_id IN(select device_id from user_profile where university='浙江大学') order by result	
#通过两个表的device_id进行连接，子查询是为了找出基本表中device_id是浙江大学的。
1
2
3

12.每个学校答过题的用户每个人平均答题数量情况

select university,count(b.device_id)/count(distinct a.device_id) as avg_answer_cnt from user_profile as a,question_practice_detail as b where a.device_id = b.device_id group by university
#通过id相除找出平均答题数量，然后两个表的id名是一样的，需要使用
1
2

13.分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。

select device_id,gender,age,gpa from user_profile where university = '山东大学' 
union ALL 
select device_id,gender,age,gpa from user_profile where  gender = 'male'
#要求结果不去重，union all返回所有数据， union是把集中重复的记录删掉
1
2
3
4

14.将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

本题注意：age为null 也记为 25岁以下

select if(age>=25,'25岁及以上','25岁以下') as age_cut, count(device_id) as number 
  from user_profile group by age_cut
# 使用if进行对数据进行判断，如果大于等于25就显示'25岁及以上'，其余的显示后面
1
2
3

15.将用户划分为20岁以下，20-24岁，25岁以上三个年龄段，分别查看不同年龄段用户的明细情况

select device_id,gender, 
case
 when age < 20 then '20岁以下'
 when age>=20 and age<=24 then '20-24岁'
 when age >=25 then '25岁以上'
 else '其他' end as age_cute
from user_profile
#使用case函数对数据进行判断，然后显示出相应范围
1
2
3
4
5
6
7
8

16.计算出2021年8月每天用户练习题目的数量

select DAY(date) as day,count(device_id) as question_detail from question_practice_detail 
 where month(date) = 08 and year(date) = 2021 group by day

#利用时间函数选择哪一天，然后进行判断每年每月的正确范围进行查询
1
2
3
4