MySQL数据库操作(二)-CSDN博客

准备数据

CREATE TABLE IF NOT EXISTS exam (
  id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name varchar(50) NOT NULL,
  chinese double DEFAULT NULL,
  math double DEFAULT NULL,
  english double DEFAULT NULL
);

INSERT INTO exam (id, name, chinese, math, english) VALUES
	(1, '谢逊', 99, 70, 80),
	(2, '张无忌', 80, 95, 78),
	(3, '周芷若', 85, NULL, 91),
	(4, '张三丰', 88, 73, 78);

CREATE TABLE IF NOT EXISTS orders (
  id int(11) DEFAULT NULL,
  product varchar(50) DEFAULT NULL,
  price float DEFAULT NULL
);

INSERT INTO orders (id, product, price) VALUES
	(1, '电视', 1500),
	(2, '冰箱', 900),
	(3, '洗衣机', 1300),
	(4, '空调', 2200),
	(5, '电风扇', 180);

CREATE TABLE IF NOT EXISTS user (
  id int(11) DEFAULT NULL,
  name varchar(50) DEFAULT NULL,
  age int(11) DEFAULT NULL
) ;

INSERT INTO user (id, name, age) VALUES
	(1, '谢逊', 59),
	(2, '张无忌', 21),
	(3, '周芷若', 18),
	(4, '张三丰', 65),
	(5, '赵敏', 19),
	(6, '杨遥', 27);

基础查询

查询表中所有学生的信息

select id,name,chiese,math,english from exam;
select * from exam;

查询表中所有学生的姓名和对应的英语成绩

select name,english from exam;

查询所有英语成绩并过滤重复数据

select distinct english from exam;

显示时，给所有学生分数都加10

select name,math+10,chinese+10,english+10 from exam;

统计每个学生的总分

select 
name,
ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)
from exam;

使用别名表示学生总分

select 
name as 姓名,
ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0) as 总分 
from exam;

select 
name 姓名,
ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0) 总分 
from exam;

条件查询

查询姓名为谢逊的学生成绩

select name,math,chinese,english from exam where name='谢逊';

查询英语成绩大于80分的同学

select name,english from exam where english>80;

查询总分大于250分的同学

select 
name,
ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0) as fen 
from exam 
where ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)  > 250;

查询数学分数在 80－100之间的同学

select name,math from exam where chinese>80 and chinese<100;

select name,math from exam where chinese between 80 and 100;

查询语文分数为85-88的同学，再查询分数不在这个范围内的同学

select name,chinese from exam where chinese=85 or chinese=86 or chinese=87 or chinese=88;
select name,chinese from exam where chinese!=85 and chinese!=86 and chinese!=87 and chinese!=88;

select name,chinese from exam where chinese in (85,86,87,88);
select name,chinese from exam where chinese not in (85,86,87,88);

查询所有姓张的学生成绩

select * from exam where name like '张%';

查询英语分>70，语文分>80 且数学成绩为null的同学

select * from exam 
where english>70 and chinese>80 and math is null;

对英语成绩降序输出

select * from exam order by english desc;

对姓张的学生总分升序输出

select
name,ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0) as fen 
from exam 
where name like '张%'
order by fen asc;

聚合函数

统计学生总数

select count(*) from exam;
select count(name) from exam;
select count(1) from exam;

统计数学成绩大于75的学生数量

select count(1) from exam where math > 75;

统计总分大于200的学生数量

select count(1) from exam 
where ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)>200;

统计班级数学总成绩

select sum(math) from exam;

统计班级数学、语文、英语各科的总成绩

select sum(math),sum(chinese),sum(english) from exam;

统计班级语文成绩平均分

select avg(chinese) from exam;

统计班级总分平均分

select 
avg(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) as 平均分
from exam;

统计班级最高分和最低分

select 
max(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) as 最高分,
min(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) as 最低分
from exam;

分组查询

对订单表中商品归类后，显示每一类商品的总金额

select product,sum(price) from orders group by product;

查询总金额大于1000的商品的名称和总金额

select product,sum(price)  from orders 
group by product having sum(price)>1000;

查询单价小于1000而总金额大于1000的商品名称

select product,sum(price) from orders 
where price<1000 
group by product
having sum(price) > 1000;

分页查询

从user表中，按照每次3条取数据

select * from user limit 0,3;
select * from user limit 3,3;

从user表中查询年龄最小的3个用户

select * from user order by age limit 0,3;