vlambda博客
学习文章列表

--使用数据库

use test;

#创建大气质量表

create table Monthly_Indicator(

city_name varchar(20) not null,

    month_key date not null,

    aqi int(4) not null default 0,

    aqi_range varchar(20) not null default '-',

    air_quality varchar(20) not null default '-',

    pm25 float(6,2) not null default 0,

    pm10 float(6,2) not null default 0,

    so2 float(6,2) not null default 0,

    co float(6,2) not null default 0,

    no2 float(6,2) not null default 0,

    o3 float(6,2) not null default 0,

    ranking int(4) not null default 0,

    primary key(city_name,month_key)

    );

    

-- 为Monthly_Indicator表导入外部txt文件

load data local infile 'C:/Users/Administrator/Desktop/SQL/data/all.txt' 

into table Monthly_Indicator 

    fields terminated by '\t'

    ignore 1 lines;

    

-- 降序查询不同城市PM2.5的平均值

select city_name, avg(pm25) from monthly_indicator

group by city_name

order by avg(pm25) desc;

use test;

#查询PM10污染最严重的前三个月份

select month_key, avg(pm10) from Monthly_Indicator

group by month_key

order by month_key desc

limit 3;

use test;

-- 查询北京之外其他城市O3所有月份的总平均值，并对查询结果进行升序排序

select city_name,month_key,avg(o3)from Monthly_Indicator

group by city_name, month_key having city_name <> '北京'

order by avg(o3) asc;

-- 创建学员信息表

create table 学员信息表(学号 varchar(5), 学员姓名 varchar(10), 年龄 int);

-- 为学员信息表导入数据

load data local infile 'C:/Users/Administrator/Desktop/SQL/data/xyxx.csv' 

into table 学员信息表

    fields terminated by ','

    ignore 1 lines;

-- 创建学员成绩表

create table 学员成绩表(

学号 varchar(5),成绩 int);

-- 为学员成绩表导入数据

load data local infile 'C:/Users/Administrator/Desktop/SQL/data/xycj.csv' 

into table 学员成绩表

fields terminated by ','

ignore 1 lines;

-- 用左连接连接两表，查询每名学员的姓名及他们的平均成绩

select 学员姓名,avg(成绩)from 学员信息表 left join 学员成绩表 on 学员信息表.学号 = 学员成绩表.学号;

-- 用内连接连接两表，查询不同年龄学员的平均成绩

select 学员姓名,年龄,avg(成绩)from 学员信息表 inner join 学员成绩表 on 学员信息表.学号 = 学员成绩表.学号;

-- 创建fruits数据表

create table fruits(

f_id char(10) not null,

s_id int not null,

f_name varchar(255) not null,

f_price decimal(8,2) not null,

primary key(f_id));

-- 插入数据

insert into fruits(f_id,s_id,f_name,f_price)

values('a1',101,'apple',5.2),

('b1',101,'blackberry',10.2),

('bs1',102,'orange',11.2),

('bs2',105,'melon',8.2),

('t1',102,'banana',10.3),

('t2',102,'grape',5.3),

('o2',103,'coconut',9.2),

('c0',101,'cherry',3.2),

('a2',103,'apricot',25.2),

('l2',104,'lemon',6.4),

('b2',104,'berry',7.6),

('m1',106,'mango',15.6),

('m2',105,'xbabay',2.6),

('t4',107,'xbababa',3.6),

('b5',107,'xxxx',3.6);

-- 用in操作符与子查询连用的方法查询所有价格在15元以上的水果信息

select * from fruits

where f_price in(select f_price FROM fruits WHERE f_price>15);

-- 检查fruits表中是否有水果名称中有字母z的水果存在，如果存在则返回水果表中所有记录内容，不存在则返回null

select * from fruits where exists

(select * from fruits where f_name = 'z');

-- 查询最低水果价格与最高水果价格的差值

select abs(min(f_price)-max(f_price)) from fruits;

-- 使用group_concat函数查询不同s_id下对应的所有f_name信息

SELECT s_id, max(f_price),GROUP_CONCAT(f_name) FROM fruits

GROUP BY s_id;