mysql查询语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

-- 学生表
create table `student` (
`id` int unsigned PRIMARY KEY auto_increment,
`name` char(32) not null unique,
`sex`  enum('男','女') not null,
`city` char(32) not null,
`description` text,
`birthday` date not null default '1995-1-1',
`money` float(7,2) default 0,
`only_child` boolean
)charset=utf8mb4;

-- 学生数据
insert into `student` 
(`name`,`sex`,`city`,`description`,`birthday`,`money`,`only_child`)
VALUES 
('郭德纲', '男','北京','班长','1997/10/1',rand()*100,True),
('陈乔恩', '女', '上海', NULL, '1995/3/2', rand() * 100,True),
('赵丽颖', '女', '北京', '班花, 不骄傲', '1995/4/4', rand()* 100, False),
('王宝强', '男', '重庆', '超爱吃火锅', '1998/10/5', rand() *100, False),
('赵雅芝', '女', '重庆', '全宇宙三好学生', '1996/7/9',rand() * 100, True),
('张学友', '男', '上海', '奥林匹克总冠军！', '1993/5/2',rand() * 100, False),
('陈意涵', '女', '上海', NULL, '1994/8/30', rand() * 100,True),
('赵本山', '男', '南京', '副班长', '1995/6/1', rand() *100, True),
('张柏芝', '女', '上海', NULL, '1997/2/28', rand() * 100,False),
('吴亦凡', '男', '南京', '大碗宽面要不要？', '1995/6/1', rand() * 100, True),
('鹿晗', '男', '北京', NULL, '1993/5/28', rand() * 100,True),
('关晓彤', '女', '北京', NULL, '1995/7/12', rand() * 100,True),
('周杰伦', '男', '台北', '小伙人才啊', '1998/3/28', rand() *100, False),
('马云', '男', '南京', '一个字：贼有钱', '1990/4/1', rand()* 100, False),
('马化腾', '男', '上海', '马云死对头', '1990/11/28', rand()* 100, False);


成绩表
create table score (
   `id` int unsigned primary key auto_increment,
   `math` float not null default 0,
   `english` float not null default 0
)charset=utf8mb4;

insert into score (`math`, `english`)
values
(49, 71), (62, 66.7), (44, 86), (77.5, 74), (41, 75),
(82, 59.5), (64.5, 85), (62, 98), (44, 36), (67, 56),
(81, 90), (78, 70), (83, 66), (40, 90), (90, 90);

1
2
3

-- 查询每个城市的所有人员姓名,人数,总金额,平均金额
-- 按照城市分组
select city as '城市',group_concat(money) as '金额',group_concat(name) as '姓名', count(name) as '人数',sum(money) as '总金额',avg(money) as '平均金额' from student group by city;

1
2
3
4
5

-- 升序
select * from student order by money;

-- 降序
select * from student order by money desc;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 查找最有钱的5个人
select * from student order by money desc limit 5 ;

-- 可用于分页查询
-- 第3行开始，往后查7行，不包括第3行
select * from student limit 3,7;

-- 可用于分页查询
-- offset=3,就是从第3个开始查，查找5行。不包括第3行
select * from student limit 5 offset 3;

1
2

-- 根据city字段去重
select distinct city from student;

1
2
3
4
5
6

-- 添加测试数据
insert into student values(18,"小红","女","北京","小学生","1998-05-02",88,0);
insert into student values(19,"小蓝","男","河北","小学学渣","1998-02-07",86,0);
insert into student values(22,"小贼","男","东土","hys","1996-03-02",11,0);
insert into score values(17,82,42);
insert into score values(26,45,34);

1
2

-- 两张表字段数量要一样。两张表上下拼接
select student.id,student.name from student union select score.id,score.math from score;

1
2

-- 将student.id、score.id相等的记录左右合并为一条
select student.*,score.* from student inner join score on student.id=score.id ;

1
2

-- 将student.id、score.id相等的记录左右合并为一条，不相等的记录以左表为准显示,右边字段为null
select student.*,score.* from student left join score on student.id=score.id;

1
2

-- 将student.id、score.id相等的记录左右合并为一条，不相等的记录以右表为准显示,左边字段为null
select student.*,score.* from student left join score on student.id=score.id;

1
2

-- 查出男生女生的数学英语平均分
select student.sex,round(avg(score.math),1),round(avg(score.english),1) from student inner join score on student.id=score.id group by sex;