数据库2_单表查询语句

这里经过测试,都是正确的语句,我直接给出中文释义以及表语句的书写方式,本篇主要讲解单表查询语句以及多表设计,后续会有复杂的多表查询

-- 创建表并插入数据

create table exam(

　　id int primary key auto_increment,
　　name varchar(20) not null,
　　chinese double,
　　math double,
　　english double
);
insert into exam values(null,'关羽',85,76,60);
insert into exam values(null,'张飞',70,75,70);
insert into exam values(null,'赵云',90,65,95);
insert into exam values(null,'刘备',97,50,50);
insert into exam values(null,'曹操',90,89,80);
insert into exam values(null,'司马懿',90,67,65);

-- 查询所有数据
select * from exam;

-- 查询表中所有学生的姓名 和对应的英语成绩
select name,english from exam;

-- 查询姓名为赵云的学生成绩
select * from exam where name = '赵云';

# 运算符
-- 查询英语成绩大于90分的同学
select * from exam where english > 90;

-- 查询英语分数不等于70分的所有同学
select * from exam where english != 70;
select * from exam where english <> 70; -- 更规范的写法
select * from exam where not english = 70;

-- 查询英语分数在 80－90之间的同学(包含80和90)。
select * from exam where english >= 80 and english <= 90;
select * from exam where english between 80 and 90;

-- 查询数学分数为89,75,91的同学。
select * from exam where math = 89 or math = 75 or math = 91;
select * from exam where math in(89,75,91);

-- 插入一条数据
insert into exam values(null,'刘阿斗',86,null,83);

-- 查询所有姓刘的学生成绩。
select * from exam where name like '刘%';-- 表示0个或多个

-- 查询所有姓刘两个字的学生成绩。

select * from exam where name like '刘_'; -- 表示一个

-- 查询数学成绩不为null的学生
select * from exam where math is not null;

-- 查询数学成绩为null的学生.
select * from exam where math is null;

-- 查询数学分>80并且语文分>80的同学。
select * from exam where math > 80 and chinese > 80;

-- 查询数学分>80 或者 语文分>80的同学。
select * from exam where math > 80 or chinese > 80;

-- 查询英语分数不大于60的学生
select * from exam where english <= 60;
select * from exam where not english > 60;

# 过滤重复
-- 过滤掉重复的语文成绩.
select distinct chinese from exam; -- 遍历去重

# 别名,只会改变查询的结果，原始数据不会改变
select * from exam;
select id,name,chinese as 语文,math as 数学,english as 英语 from exam;
select id,name,chinese as '语文',math as '数学',english as '英语' from exam;

select id,name,chinese 语文,math 数学,english 英语 from exam; -- 不推荐
select name chinese from exam; -- 只查询一列，结果列名是Chinese

-- 查询在校所有学生的分数，在显示的时候每门课加10分特长分。（每一门课程都加10分）
select id,name,chinese + 10 as 语文特长分,math + 10,english + 10 from exam;

-- 查询每个学生的总分。
select id,name,chinese + math + english as 总分 from exam;

# 排序
-- 对语文成绩升序排序后输出。
select * from exam order by chinese asc;
select * from exam order by chinese; -- 如果是升序，asc可以省略
select * from exam order by chinese desc;

-- 对语文升序排序，如果语文成绩一样，按数学成绩降序排序
select * from exam order by chinese asc,math desc;

-- 对总分排序按从高到低降序输出
select name,chinese + math + english from exam order by chinese + math + english desc;
select name,chinese + math + english as 总分 from exam order by 总分 desc;

-- ifnull(列名,默认值) 解决null值问题
select name,ifnull(chinese,0) + ifnull(math,0) + ifnull(english,0) as 总分 from exam
order by 总分 desc;

-- 对姓刘的学生成绩总分进行降序排序
select name,ifnull(chinese,0) + ifnull(math,0) + ifnull(english,0) as 总分 from exam
where name like '刘%'
order by 总分 desc;


# 聚合函数
-- 统计一个班级共有多少学生？
select count(*) from exam;
select count(math) from exam; -- Count 排除了为null的记录
select count(id) from exam; -- 主键唯一不为空

-- 统计语文成绩大于等于90的学生有多少个？
select count(*) from exam where chinese >= 90;

-- 统计总分大于250的人数有多少？
select count(*) from exam
where ifnull(chinese,0) + ifnull(math,0) + ifnull(english,0) > 250;
-- Unknown column '总分' in 'where clause'
-- where语句不能使用别名
select name ,ifnull(chinese,0) + ifnull(math,0) + ifnull(english,0) as 总分 from exam
where 总分 > 250; -- 错误的

-- 统计一个班级数学总成绩？
select sum(math) from exam; -- sum 自动排除了null

-- 分别显示一个班级语文、英语、数学各科的总成绩
select sum(chinese),sum(math),sum(english) from exam;

-- 统计一个班级语文、英语、数学的成绩总和。
select sum(chinese + math + english) from exam; -- 1364

select sum(chinese) + sum(math) + sum(english) from exam; -- 1533

select sum(ifnull(chinese,0) + ifnull(math,0) + ifnull(english,0)) as 全部总分 from exam;

-- 统计一个班级语文成绩平均分
select sum(chinese) / count(*) from exam;

-- round(数值, 小数的位数) Math round ceil floor

select round(sum(chinese) / count(*),2) from exam;

-- 求一个班级数学平均分？
select avg(math) from exam; -- 70.33 avg 排除了null
select round(avg(ifnull(math,0)),2) from exam;

-- 统计英语的最高分和最低分
select max(english),min(english) from exam;

-- 统计总分的最高分和最低分
-- Invalid use of group function 非法使用：聚合函数不能嵌套
select max(sum(math) + sum(chinese) + sum(english)) from exam; -- 错误
select max(ifnull(math,0) + ifnull(chinese,0) + ifnull(english,0)) from exam;

create table orders(
id int,
product varchar(20),
price float
);

insert into orders(id,product,price) values(1,'纸巾',16);
insert into orders(id,product,price) values(2,'纸巾',16);
insert into orders(id,product,price) values(3,'红牛',5);
insert into orders(id,product,price) values(4,'洗衣粉',60);
insert into orders(id,product,price) values(5,'苹果',8);
insert into orders(id,product,price) values(6,'洗衣粉',60);

-- 查询购买的每种商品的总价。
select product,sum(price) from orders group by product;

-- 查询每一种商品的总价大于30的商品，并显示总价
select product,sum(price) from orders
where sum(price) > 30 -- 错误， where 不仅不能用别名，还不能用聚合函数
group by product;

select product,sum(price) from orders
group by product
having sum(price) > 30; -- 一般情况下，group by 和having 一起使用

-- 查询商品的单价大于10的商品
select * from orders
where price > 10;

-- 查询商品的单价大于10的商品，其总价大于30，并显示总价
select product,sum(price) from orders
where price > 16
group by product
having sum(price) > 30;

-- 查询商品的单价大于10的商品，其总价大于30，并降序显示总价
select product,sum(price)as 总价 from orders
where price > 15
group by product
having 总价 > 30
order by 总价 desc;

# 多表设计

create table dept(
id int primary key auto_increment,
name varchar(20)
);
create table employee(
id int primary key auto_increment,
name varchar(20),
age int ,
salary double
);

/*给部门表添加数据*/
insert into dept values(null,'人事部');
insert into dept values(null,'财务部');
insert into dept values(null,'公关部');
insert into dept values(null,'总经理办公室');

/*给雇员表添加数据*/
insert into employee values(null,'小乔',18,10000);
insert into employee values(null,'大乔',19,10000);
insert into employee values(null,'曹操',20,12000);
insert into employee values(null,'周瑜',21,13000);
insert into employee values(null,'刘备',22,14000);

alter table employee add dept_id int;

-- 问题：此时员工表和部门表之间有联系吗？
delete from dept where name = '人事部';

-- 添加外键，必须添加外键约束

alter table employee add foreign key (dept_id) references dept(id);
-- 添加约束失败的原因：因为主表中的没有从表中需要的主键值
insert into dept values(1,'人事部');

-- 有外键约束，能不能先删主表中的内容，应该先删除从表再删除主表（遣散员工，然后公司关门）
drop table dept;
drop table employee;

/*创建部门表*/
create table dept(
　　id int primary key auto_increment,
　　name varchar(20)
);
/*创建员工表*/
create table employee(
　　id int primary key auto_increment,
　　name varchar(20),
　　age int ,
　　salary double,
　　dept_id int,
　　foreign key (dept_id) references dept(id) -- 添加外键关联
);

# 多对多
-- 创建程序员表
create table coder (
　　id int primary key auto_increment,
　　name varchar(30),
　　salary double
);

-- 创建项目表
create table project(
　　id int primary key auto_increment,
　　name varchar(100)
);

create table coder_project(
　　id int primary key auto_increment,
　　code_id int,
　　project_id int,
　　foreign key (code_id) references coder(id),
　　foreign key (project_id) references project(id)
);