当前位置:   article > 正文

个人数据库类知识整理

个人数据库

数据库知识汇总

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
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/笔触狂放9/article/detail/839259
推荐阅读
相关标签
  

闽ICP备14008679号