SQL编程基础常见题型练习

最新推荐文章于 2026-05-19 17:17:31 发布

原创

最新推荐文章于 2026-05-19 17:17:31 发布 · 796 阅读

标签

#sql #数据库

SQL编程基础常见题型练习

1. 基础查询

1.1. 基础查询

查询所有列

select * from user_profile;

查询多列

1.2. 简单处理查询结果

查询结果去重

select university 
from user_profile 
group by university;

查询结果限制返回行数

select device_id
from user_profile
where id in (1,2);

将查询后的列重新命名

在这里插入图片描述

select device_id
as user_infos_example 
from user_profile limit 2;

代码示例：

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',23,'复旦大学',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京师范大学',3.3);

2. 条件查询

2.1. 基础排序

查找后排序

select device_id,age
from user_profile
order by age asc
# 降序：select device_id,age from user_profile order by age desc;

查找后多列排序

select device_id,gpa,age
from user_profile
order by gpa,age

查找后降序排列

select device_id,gpa,age
from user_profile
order by gpa desc,age desc

2.2. 基础操作符

查找学校是北大的学生信息

SELECT device_id,university FROM user_profile WHERE university LIKE '%北京大学%'
#使用like运行时间会更短点,虽然多写了几个符号

SELECT device_id,university FROM user_profile WHERE university = "北京大学";

查找年龄大于24岁的用户信息

select 
    device_id,
    gender,
    age,
    university
from user_profile 
where age > 24;

查找某个年龄段的用户信息

select 
    device_id,
    gender,
    age
from user_profile
where age between 20 and 23;

查找除复旦大学的用户信息

select
    device_id,
    gender,
    age,
    university
from user_profile
where university <> "复旦大学"
# where university != '复旦大学'
# where not university = '复旦大学'
# where university not in('复旦大学')
# where university not like '复旦大学'

用 where 过滤空值练习

select
    device_id,
    gender,
    age,
    university
from
    user_profile
where age is not null and age <> "";

2.3. 高级操作符

高级操作符练习（1）

select device_id,gender,age,university,gpa
from user_profile
where gpa > 3.5 and gender in ('male');

高级操作符练习（2）

select device_id,gender,age,university,gpa
from user_profile
where university = "北京大学" or gpa > 3.7;

Where in 和 Not in

select device_id,gender,age,university,gpa
from user_profile
where university IN ("北京大学","复旦大学","山东大学");

操作符混合运用

select device_id,gender,age,university,gpa
from user_profile
where (gpa > 3.5 and university = "山东大学" ) or (gpa > 3.8 and university = "复旦大学");


#SELECT device_id, gender, age, university,gpa from user_profile where gpa > 3.8 and university = '复旦大学' UNION SELECT device_id, gender, age, university,gpa from user_profile where gpa > 3.5 and university = '山东大学'

查找学校名称中含北京的用户

代码：

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar

最低0.47元/天解锁文章