SQL常用语句

于 2022-09-25 12:50:14 发布
阅读量185 收藏 1
点赞数
文章标签： sql 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44786330/article/details/127036628
版权

SQL插入

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

SQL删除

DELETE FROM table_name
WHERE some_column=some_value;

sql更新

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

SQL查询

img

img

Select+聚合函数

总数

select count(*) from StudentBindPaperTypeEntity

最大值

select max(StudentID) from StudentBindPaperTypeEntity

最小值

select min(StudentID) from StudentBindPaperTypeEntity

求和

例4.查询当前在线的学生(IsUse=0表示未在线，1表示在线) 只能计算数字

select SUM(IsUse) from StudentBindPaperTypeEntity

平均值

avg(列) 表示求此列的平均值（注：avg运算符与数字类型连用）

select avg(StudentID) from StudentBindPaperTypeEntity

from(表)+连接查询

1.内连接：Inner join
2.左连接：Left join
3.右连接：Right join

from+inner join

img

-- 获取男生和女生的平均成绩
SELECT a.s_sex,AVG(b.s_score) as avg FROM student as a  
INNER JOIN score as b
ON a.s_id = b.s_id
GROUP BY a.s_sex

SQL插入

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

SQL删除

DELETE FROM table_name
WHERE some_column=some_value;

sql更新

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

SQL查询

img

img

Select+聚合函数

总数

select count(*) from StudentBindPaperTypeEntity

最大值

select max(StudentID) from StudentBindPaperTypeEntity

最小值

select min(StudentID) from StudentBindPaperTypeEntity

求和

例4.查询当前在线的学生(IsUse=0表示未在线，1表示在线) 只能计算数字

select SUM(IsUse) from StudentBindPaperTypeEntity

平均值

avg(列) 表示求此列的平均值（注：avg运算符与数字类型连用）

select avg(StudentID) from StudentBindPaperTypeEntity

from(表)+连接查询

1.内连接：Inner join
2.左连接：Left join
3.右连接：Right join

from+inner join

img

-- 获取男生和女生的平均成绩
SELECT a.s_sex,AVG(b.s_score) as avg FROM student as a  
INNER JOIN score as b
ON a.s_id = b.s_id
GROUP BY a.s_sex

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zwI9Qpsd-1664081217020)(C:\Users\蔡小艺\AppData\Roaming\Typora\typora-user-images\image-20220831225605376.png)]

from+left join

在这里插入图片描述

select score.studentID,score.score,s.CollegeID,s.major,s.majorClass from  StudentInfoEntity as s 
left join ScoreEntity as score 
on s.studentID=score.studentID

在这里插入图片描述

from+right join

在这里插入图片描述

select score.studentID,score.score,s.CollegeID,s.major,s.majorClass from  ScoreEntity as score  
right join StudentInfoEntity as s 
on s.studentID=score.studentID

在这里插入图片描述

Where（条件语句查询）

在这里插入图片描述 
-- IN的使用
SELECT * FROM student WHERE s_id IN (01,05,03);

模糊查询

  1. _ ：下划线 代表匹配任意一个字符；
  2. % ：百分号 代表匹配0个或多个字符；
  3. []: 中括号 代表匹配其中的任意一个字符；
  4. [^]: ^尖冒号 代表 非，取反的意思；不匹配中的任意一个字符。

例1.查询1月8号考试的学生

select * from StudentBindPaperTypeEntity where TimeTamp like '2020-01-08%'

例2.查询不是1月8号考试的学生

select * from StudentBindPaperTypeEntity where TimeTamp not like '2020-01-08%'

范围查询

in关键字为非连续查询
例1.查询两个不相邻的学号的学生

select * from StudentBindPaperTypeEntity where StudentID in('19100142001','19100142006')

Between…and…为连续查询(注：sql软件情况不一样，可能不包含and后的值)
例2.查询两个学号之间的学生

select * from StudentBindPaperTypeEntity where StudentID Between 19100142001 and 19100142006

查询结果限制返回行数

1.top不适用于所有的数据库语言。 1）SQL SERVER里可以使用。使用方式如下：

select top number/percent column(s) from user-profile;

2)在MySQL就不适用。在MySQL中使用的是 limit 。

select device_id from user_profile limit 2;

group by(分组)

作用：将字段间一对多的关系，向一的方向靠拢分组

SELECT s_sex,count(*) as sum FROM student group by s_sex;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VGBomsHw-1664081217022)(C:\Users\蔡小艺\AppData\Roaming\Typora\typora-user-images\image-20220831230329471.png)]

group by+max(字段名)

按分组并取每组中‘字段名’的值最大那个数据

select max(user_id) from student group by student_id

group by+聚合函数

-- 每位学生的平均成绩
SELECT a.*,AVG(b.s_score) as avg FROM student as a  
INNER JOIN score as b
ON a.s_id = b.s_id
GROUP BY a.s_id

显示结果：

其实group by + 聚合函数是从group by + group_concat()演变过来的，SqlServer不支持这个函数

group by+having (分组过滤)

having的作用跟where子句功能一样，只不过having只用在group by

  • 限定条件：平均发贴数低于5或平均回帖数小于20的学校，avg(question_cnt)<5 or avg(answer_cnt)<20，聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可；
  • 按学校输出：需要对每个学校统计其平均发贴数和平均回帖数，因此group by university
select
    university,
    avg(question_cnt) as avg_question_cnt,
    avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university
having avg_question_cnt<5 or avg_answer_cnt<20

group by + Order by（分组排序）

  • asc从小到大，默认
  • desc从大到小
select university,
    avg(question_cnt) as avg_question_cnt
from user_profile
group by university
order by avg_question_cnt

实际案例

双重group by

题目：现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

  • 用户信息表：user_profile
  • 30天内活跃天数字段（active_days_within_30）
  • 发帖数量字段（question_cnt）
  • 回答数量字段（answer_cnt）
select 
    gender, university,
    count(device_id) as user_num,
    avg(active_days_within_30) as avg_active_days,
    avg(question_cnt) as avg_question_cnt
from user_profile
group by gender, university
image-20220831234336654

指定大学平均刷题数

题目：运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，请取出相应数据

问题分解：

  • 限定条件：山东大学的用户 up.university="山东大学"；
  • 不同难度：按难度分组group by difficult_level
  • 平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id) 来自上面信息三个表，需要联表，up与qpd用device_id连接并限定大学，qd与qpd用question_id连接。
方法一：group by+having
SELECT t1.university,
t3.difficult_level,
COUNT(t2.result) / COUNT(DISTINCT t2.device_id) AS avg_answer_cnt
FROM question_practice_detail AS t2
inner JOIN user_profile AS t1
ON t2.device_id = t1.device_id
inner JOIN question_detail AS t3
ON t2.question_id = t3.question_id
GROUP BY t1.university, t3.difficult_level
HAVING t1.university = '山东大学';

方法二：
SELECT
    t1.university,
    t3.difficult_level,
    COUNT(t2.question_id) / COUNT(DISTINCT(t2.device_id)) as avg_answer_cnt
from
    user_profile as t1,
    question_practice_detail as t2,
    question_detail as t3
WHERE
    t1.university = '山东大学'
    and t1.device_id = t2.device_id
    and t2.question_id = t3.question_id
GROUP BY
    t3.difficult_level;
image-20220901141054808

from+left join

在这里插入图片描述

select score.studentID,score.score,s.CollegeID,s.major,s.majorClass from  StudentInfoEntity as s 
left join ScoreEntity as score 
on s.studentID=score.studentID

在这里插入图片描述

from+right join

在这里插入图片描述

select score.studentID,score.score,s.CollegeID,s.major,s.majorClass from  ScoreEntity as score  
right join StudentInfoEntity as s 
on s.studentID=score.studentID

在这里插入图片描述

Where（条件语句查询）

在这里插入图片描述 
-- IN的使用
SELECT * FROM student WHERE s_id IN (01,05,03);

模糊查询

  1. _ ：下划线 代表匹配任意一个字符；
  2. % ：百分号 代表匹配0个或多个字符；
  3. []: 中括号 代表匹配其中的任意一个字符；
  4. [^]: ^尖冒号 代表 非，取反的意思；不匹配中的任意一个字符。

例1.查询1月8号考试的学生

select * from StudentBindPaperTypeEntity where TimeTamp like '2020-01-08%'

例2.查询不是1月8号考试的学生

select * from StudentBindPaperTypeEntity where TimeTamp not like '2020-01-08%'

范围查询

in关键字为非连续查询
例1.查询两个不相邻的学号的学生

select * from StudentBindPaperTypeEntity where StudentID in('19100142001','19100142006')

Between…and…为连续查询(注：sql软件情况不一样，可能不包含and后的值)
例2.查询两个学号之间的学生

select * from StudentBindPaperTypeEntity where StudentID Between 19100142001 and 19100142006

查询结果限制返回行数

1.top不适用于所有的数据库语言。 1）SQL SERVER里可以使用。使用方式如下：

select top number/percent column(s) from user-profile;

2)在MySQL就不适用。在MySQL中使用的是 limit 。

select device_id from user_profile limit 2;

group by(分组)

作用：将字段间一对多的关系，向一的方向靠拢分组

SELECT s_sex,count(*) as sum FROM student group by s_sex;

group by+max(字段名)

按分组并取每组中‘字段名’的值最大那个数据

select max(user_id) from student group by student_id

group by+聚合函数

-- 每位学生的平均成绩
SELECT a.*,AVG(b.s_score) as avg FROM student as a  
INNER JOIN score as b
ON a.s_id = b.s_id
GROUP BY a.s_id

显示结果：

其实group by + 聚合函数是从group by + group_concat()演变过来的，SqlServer不支持这个函数

group by+having (分组过滤)

having的作用跟where子句功能一样，只不过having只用在group by

  • 限定条件：平均发贴数低于5或平均回帖数小于20的学校，avg(question_cnt)<5 or avg(answer_cnt)<20，聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可；
  • 按学校输出：需要对每个学校统计其平均发贴数和平均回帖数，因此group by university
select
    university,
    avg(question_cnt) as avg_question_cnt,
    avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university
having avg_question_cnt<5 or avg_answer_cnt<20

group by + Order by（分组排序）

  • asc从小到大，默认
  • desc从大到小
select university,
    avg(question_cnt) as avg_question_cnt
from user_profile
group by university
order by avg_question_cnt

实际案例

双重group by

题目：现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

  • 用户信息表：user_profile
  • 30天内活跃天数字段（active_days_within_30）
  • 发帖数量字段（question_cnt）
  • 回答数量字段（answer_cnt）
select 
    gender, university,
    count(device_id) as user_num,
    avg(active_days_within_30) as avg_active_days,
    avg(question_cnt) as avg_question_cnt
from user_profile
group by gender, university

指定大学平均刷题数

题目：运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，请取出相应数据

问题分解：

  • 限定条件：山东大学的用户 up.university="山东大学"；
  • 不同难度：按难度分组group by difficult_level
  • 平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id) 来自上面信息三个表，需要联表，up与qpd用device_id连接并限定大学，qd与qpd用question_id连接。
方法一：group by+having
SELECT t1.university,
t3.difficult_level,
COUNT(t2.result) / COUNT(DISTINCT t2.device_id) AS avg_answer_cnt
FROM question_practice_detail AS t2
inner JOIN user_profile AS t1
ON t2.device_id = t1.device_id
inner JOIN question_detail AS t3
ON t2.question_id = t3.question_id
GROUP BY t1.university, t3.difficult_level
HAVING t1.university = '山东大学';

方法二：
SELECT
    t1.university,
    t3.difficult_level,
    COUNT(t2.question_id) / COUNT(DISTINCT(t2.device_id)) as avg_answer_cnt
from
    user_profile as t1,
    question_practice_detail as t2,
    question_detail as t3
WHERE
    t1.university = '山东大学'
    and t1.device_id = t2.device_id
    and t2.question_id = t3.question_id
GROUP BY
    t3.difficult_level;

SuperYiY
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值