SQL语句查询

标签：语句 goods -- 查询 students SQL where select

正文：

• 基础查询
• 复杂查询

---

基础查询

查询数据准备

 SQL语句

drop table if exists goods;
create table goods(
    id int UNSIGNED PRIMARY key auto_increment,
    goodsName varchar(10),
    price int,
    count int,
    company varchar(20),
    remark varchar(20)
);

insert into goods VALUES
(0, '三奇', 25, 100, '某东', '三奇3Q口罩'),
(0, '振德', 20, 150, 'x宝', '振德一次性口罩'),
(0, '袋鼠医生', 30, 180, '并夕夕', '袋鼠医生医用口罩'),
(0, '京东京造', 130, 100, '某东', null),
(0, '闪电卫士', 77, 160, 'x宝', '闪电卫士一次性口罩'),
(0, '安克林', 159, 190, '某东', '安克林KN95级口罩'),
(0, '科西诺', 30, 100, 'x宝', '科西诺三层防护口罩'),
(0, '维德医生', 23, 155, '并夕夕', '维德成人医用口罩'),
(0, '庭七', 110, 260, '某东', '医用一次性口罩'),
(0, '倍舒特', 72, 190, 'x宝', '三层防护一次性口罩医用口罩'),
(0, '中服绿盾', 110, 50, '某东', ''),
(0, '惠寻', 25, 130, '并夕夕', '惠寻一次性口罩');

查询目标

-- 基础查询
-- 需求1：准备商品数据，查询所有数据，查询部分字段，起字段别名，去重
-- 查询所有数据：select * from 表名;
select * from goods;
-- 查询部分字段:select 字段名，字段名 from goods;
select goodsName, price from goods;
-- 字段取别名：select 字段名 as '别名' from goods;
select goodsName as '商品名称', price as '价格' from goods;
-- 注意：别名的引号可以省略
select goodsName as 商品名称, price as 价格 from goods;
-- 注意：as关键字也可以省略
select goodsName 商品名称, price 价格 from goods;
-- 起别名的作用：1>美化数据结构的显示效果 2>可以起到隐藏真正字段名的作用
-- 另：除了可以给字段起别名以外，还可以给数据表起别名（连接查询时使用）

-- 去重:select distinct(字段名) from goods;
-- 效果：将目标字段内重复出现的数据只保留一份显示
-- 小需求：显示所有的公司名称
select distinct(company)  from goods;

 

 

 

---

 

复杂查询

条件查询

使用where子句对表中数据进行筛选，符合条件的数据会在出现结果集中

语法格式

select 字段1,字段2,... from 表名 where 条件;

1，条件构成

where后面支持多种运算符，进行条件的处理

• 比较运算
• 逻辑运算
• 模糊查询
• 范围查询
• 空判断

1.1，比较运算符

• 等于：=
• 不等于：!= 或 <>
• 大于：>
• 小于：<
• 大于等于：>=
• 小于等于：<=

例子1：查询小乔的年龄

select age from students where name='小乔'

例子2：查询20岁以下的学生

select * from students where age<20;

例子3：查询家乡不在北京的学生

select * from students where hometown!='北京'

1.2，逻辑运算符

• and
• or
• not

例子1：查询年龄小于20的女同学

select * from students where age<20 and sex='女'

例子2：查询女学生或‘1班’的同学

select * from students where sex='女' or class='1班'

例子3：查询非天津的学生

select * from students where not hometown='天津'

1.3，模糊查询

• like
• %表示任意多个字符
• _表示任意一个字符

例子1：查询姓孙的学生

select * from students where name like '孙%';

例子2：查询姓孙且名字是一个字的学生

select * from students where name like '孙_';

例子3：查询姓名以乔结尾的学生

select * from students where name like '%乔';

例子4：查询姓名含白的学生

select * from students where name like '%白%';

1.4，范围查询

• in表示在一个非连续的范围内，格式为in(..,..)
• between ... and ...表示在一个连续的范围内

例子1：查询家乡是北京或上海或广东的学生

select * from students where hometown in('北京','上海','广东')

例子2：查询年龄为18到20的学生

select * from students where age between 18 and 20

1.5，空判断

• 判空 is null
• 判非空 is not null

例子1：查询没有填写身份证的学生

select * from students where card is null

例子2：查询填写了身份证的学生

select * from students where card is not null

需求语句

-- 需求2: 查询价格等于30并且出自并夕夕的所有商品信息
-- 使用比较运算符和逻辑运算符，注意：作为查询条件使用的字符串必须带引号
select * from goods where price=30 and company='并夕夕';
-- 补充需求:查询价格等于30但不出自并夕夕的所有商品信息
-- 注意: not 与 and 和 or(双边连接条件)不同之处在于，not 只对自己右侧的条有作用(单边连接条件)
select * from goods where not company='并夕夕' and price=30;
select * from goods where price=30 and company!='并夕夕';

-- 需求3:查询全部一次性口罩的商品信息
-- 使用模糊查询,注意：作为查询条件使用的字符串必须带引号
select * from goods where remark like '%一次性%';

-- 需求4:查询所有价格在30-100的商品信息
-- 范围查询（连续或非连续）,注意: between ...and...的范围必须是从小到大
select * from goods where price between 30 and 100;

-- 需求5:查询没有描述信息的商品信息
-- 注意:在 MySQL 中，只有显示为NULL的才为空!其余空白可能是空格/制表符(tab)/换行符等空白符号 
-- 判断空：为空：is null  不为空：is not null
select * from goods where remark is null;
-- 补充需求：查询有描述信息的商品信息
select * from goods where remark is not null;

 

---

 

排序

• 默认按照列值从小到大排序
• asc从小到大排序，即升序
• desc从大到小排序，即降序

语法格式

将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推

select * from 表名
order by 列1 asc|desc,列2 asc|desc,...

例子1：查询所有学生信息，按年龄从小到大排序

select * from students order by age

例子2：查询所有学生信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序

select * from students order by age desc,studentNo

需求语句

-- 需求6: 查询所有商品信息，按照价格从大到小排序，价格相同时，按照数量少到多排序
-- 注意:排序过程中，支持连续设置多条排序规则，但离 order by 关键字越近，排序数据的范围越大
select * from goods order by price desc,count asc;
-- 注意:默认排序为升序，asc 可以省略
select * from goods order by price desc,count;

 

---

 

聚合函数

• 对于一组数据进行计算返回单个结果的实现过程
• 聚合函数不能在where子句中使用　　

常用聚合函数

• count()：查询总记录数
• max()：查询最大值
• min()：查询最小值
• sum()：求和
• avg()：求平均值

1）查询总记录数

count(*) 表示计算总行数，括号中也可以用字段名

示例：查询学生总数

select count(*) from students;

示例：查询女生的最大年龄

select max(age) from students;

示例：查询1班最小年龄

select min(age) from students;

示例：查询北京学生的年龄总和

select sum(age) from students where hometown='北京';

示例：查询女生的平均年龄

select avg(age) from students where sex='女';

需求语句

-- 需求7:查询以下信息: 商品信息总条数;最高商品价格，最低商品价格;商品平均价格，一次性口罩的总数量
-- 聚合函数,注意: 统计数据总数，建议使用*，如果使用某一特定字段，可能会造成数据总数错误!
select count(*) 商品种数,max(price) 最高价格,min(price) 最小价格,avg(price) 平均价格 from goods;
select sum(count) from goods where remark like '%一次性%';

 

---

 

分组

按照字段分组，此字段相同的数据会被放到一个组中；分组的目的是对每一组的数据进行统计（使用聚合函数）

语法格式

select 字段1,字段2,聚合... from 表名 group by 字段1,字段2...

举例说明

例子1：查询各种性别的人数

select sex,count(*) from students group by sex;

例子2：查询每个班级中各种性别的人数

select class,sex,count(*) from students group by class,sex

分组后的数据筛选

语法格式

having后面的条件运算符与where的相同

select 字段1,字段2,聚合... from 表名
group by 字段1,字段2,字段3...
having 字段1,... 聚合...

例子1：查询男生总人数

方案一：

select count(*) from students where sex='男'

方案二：

select sex,count(*) from students group by sex having sex='男'

where与having

• where 是对from 后面指定的表进行数据筛选，属于对原始数据的筛选
• having 是对group by 的结果进行筛选
• having 后面的条件中可以用聚合函数，where后面不可以

-- 查询班级平均年龄大于22岁的班级有哪些？
select class from students group by class having avg(age)>22;

需求语句

-- 需求8:查询每家公司的商品信息数量
-- 注意：一般情况，使用哪个字段进行分组，那么只有该字段可以在 * 的位置处使用
-- 注意：分组操作多和聚合函数配合使用
select company,count(*) from goods group by company;

 

---

 

分页

• 从start开始，获取count条数据
• start 索引从0开始

select * from 表名 limit start,count

例子1：查询前3行学生信息

select * from students limit 0,3

分页格式

limit典型的应用场景就是实现分页查询

已知：每页显示m条数据，求：显示第n页的数据

select * from students limit (n-1)*m,m

需求语句

-- 需求9:查询当前表当中第5-10行的所有数据
select * from goods limit 4,6;
-- 注意:计算机的计数从 0开始，因此 start 默认的第一条数据应该为 0
-- 注意:如果默认从第一条数据开始获取，则0可以省略
-- 查前5条
select * from goods limit 5;
-- 扩展 1:根据公式计算显示某页的数据
-- 已知:每页显示m条数据，求: 显示第n页的数据
-- select * from 表名 limit (n-1)*m，m
-- 示例:每页显示4条数据，求展示第 2 页的数据内容
select * from goods limit 0, 4;
select * from goods limit 4, 4;
select * from goods limit 8, 4;
-- 扩展2：分页的应用
-- 需求:要求查询商品价格最贵的数据信息
select * from goods order by price desc limit 1;
-- 进阶需求：要求查询商品价格最贵的前三条数据信息
select * from goods order by price desc limit 3;

 

---

连接查询

• 内连接
• 左连接
• 右连接
• 自关联

 内连接：查询结果为两个表匹配到的数据

左连接：查询结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用 null

右连接：查询的结果为两个表匹配到数据加右表特有的数据，对于左表中不存在的数据使用 null

数据准备：

drop table if exists category;
create table category(
id int unsigned  primary key auto_increment,
typeId int,
cateName varchar(10)
);

insert into category VALUES
(0, 001, '一次性口罩'),
(0, 002, 'KN95口罩'),
(0, 003, 'N95口罩'),
(0, 004, '医用口罩');

drop table if exists goods;
create table goods(
    id int UNSIGNED PRIMARY key auto_increment,
    goodsName varchar(10),
    price int,
    count int,
    company varchar(20),
    remark varchar(20),
    typeId int
);

insert into goods VALUES
(0, '三奇', 25, 100, '某东', '三奇3Q口罩',4),
(0, '振德', 20, 150, 'x宝', '振德一次性口罩',1),
(0, '袋鼠医生', 30, 180, '并夕夕', '袋鼠医生医用口罩',4),
(0, '京东京造', 130, 100, '某东', null,null),
(0, '闪电卫士', 77, 160, 'x宝', '闪电卫士一次性口罩',1),
(0, '安克林', 159, 190, '某东', '安克林KN95级口罩',2),
(0, '科西诺', 30, 100, 'x宝', '科西诺三层防护口罩',2),
(0, '维德医生', 23, 155, '并夕夕', '维德成人医用口罩',4),
(0, '庭七', 110, 260, '某东', '医用一次性口罩',1),
(0, '倍舒特', 72, 190, 'x宝', '三层防护一次性口罩医用口罩',4),
(0, '中服绿盾', 110, 50, '某东', '',null),
(0, '惠寻', 25, 130, '并夕夕', '惠寻一次性口罩',1);

连接查询需求

 

---

 

1，内连接

语法格式：

select * from 表1
inner join 表2 on 表1.列=表2.列

例子1：查询学生信息及学生的成绩

select * from student stu
inner join scores sc on stu.studentNo=sc.studentNo

扩展：内连接的另一种语法

select * from 表1,表2 where 表1.列=表2.列

select * from students stu,scores sc
where stu.studentNo=sc.studentNo;

例子2：查询学生信息及学生的课程对应的成绩

select * from students stu
inner join scores sc on stu.studentNo=sc.studentNo
inner join courses cs on cs.courseNo = sc.courseNo

例子3：查询王昭君的成绩，要求显示姓名、课程号、成绩

select 
    stu.name,
    sc.courseNo,
    sc.score
from
    students stu
inner join scores sc on stu.studentNo=sc.studentNo
where
    stu.name = '王昭君'

例子4：查询王昭君的数据库成绩，要求显示姓名、课程名、成绩

select 
    stu.name,
    cs.name,
    sc.score
from 
    students stu
inner join scores sc on stu.studentNo=sc.studentNo
inner join courses cs on sc.courseNo=cs.courseNo
where
    stu.name = '王昭君' and cs.name='数据库'

例子5：查询所有学生的数据库成绩，要求显示姓名、课程表、成绩

select 
    stu.name,
    cs.name,
    sc.score
from 
    students stu
inner join scores sc on stu.studentNo=sc.studentNo
inner join courses cs on sc.courseNo=cs.courseNo
where
    cs.name='数据库'

例子7：查询男生中最高成绩，要求显示姓名、课程名、成绩

select 
    stu.name,
    cs.name,
    sc.score
from 
    students stu
inner join scores sc on stu.studentNo=sc.studentNo
inner join courses cs on sc.courseNo=cs.courseNo
where
    stu.sex='男'
order by
    sc.score desc
limit 1

需求语句

 

-- 需求1: 查询所有存在商品分类的商品信息
-- 显示效果: 两张表中有对应关系的数据都会显示出来，没有对应关系的数据均不在显示
-- 扩展：给表起别名(缩短表名利于编写,用别名给表创建副本)
select * from goods go 
inner join category ca on go.typeId=ca.typeId;
-- 扩展:内连接的另一种写法(旧式写法)
select * from goods, category where goods.typeId=category.typeId;

2，左连接

语法格式：

select * from 表1
left join 表2 on 表1.列=表2.列

例子1：查询所有学生的成绩，包括没有成绩的学生

select * from student stu
left join scores sc on stu.studentNo=sc.studentNo

例子2：查询所有学生的成绩，包括没有成绩的学生，需要显示课程名

select * from students stu
left join scores sc on stu.studentNo=sc.studentNo
left join courses cs on cs.courseNo=sc.studentNo

3，右连接

语法格式：

select * from 表1
right join 表2 on 表1.列=表2.列

例子1：查询所有学生的成绩，包括没有成绩的学生

select * from scores sc
right join students stu on stu.studentNo=sc.studentNo

例子2：查询所有学生的成绩，包括没有成绩的学生，需要显示课程名

select * from scores sc
right join students stu on stu.studentNo=sc.studentNo
left join courses cs on cs.courseNo=sc.courseNo

需求语句

-- 需求2: 查询所有商品信息，包含商品分类
-- 注意:如果要保证一张数据表的全部数据都存在，则一定不能选择内连接，可以选择左连接或右连接
-- 说明:以 left join关键字为界,关键字左侧表为主表(都显示),而关键字右侧的表为从表(对应内容显示,不对应为null)
select * from goods go
left join category ca on go.typeId=ca.typeId;
-- 扩充需求: 以分类为主展示所有内容
select * from category ca
left join goods go on ca.typeId=go.typeId;

-- 需求3:查询所有商品分类及其对应的商品的信息
select * from goods go
right join category ca on go.typeId=ca.typeId;
-- 扩充需求:查询所有商品信息及其对应分类信息
select * from category ca
right join goods go on ca.typeId=go.typeId;

补充：存在左右连接的必要性

 

4，自关联

数据准备：

drop table if exists areas;
create table areas(
aid int primary key,
atitle varchar(20),
pid int
);
insert into areas VALUES
('130000', '河北省', NULL),
('130100', '石家庄市', '130000'),
('130400', '邯郸市', '130000'),
('130600', '保定市', '130000'),
('130700', '张家口市', '130000'),
('130800', '承德市', '130000'),
('410000', '河南省', NULL),
('410100', '郑州市', '410000'),
('410300', '洛阳市', '410000'),
('410500', '安阳市', '410000'),
('410700', '新乡市', '410000'),
('410800', '焦作市', '410000'),
('410101', '中原区', '410100'),
('410102', '二七区', '410100'),
('410301', '洛龙区', '410300');

案例：

设计省信息的表结构provinces

• id
• ptitle

设计市信息的表结构citys

• id
• ctitle
• proid

 citys表的proid表示城市所属的省，对应provinces表的id值

问题：能不能将两个表合成一张表呢？

思考：观察两张表发现，citys表比provinces表多了一个列proid，其他类型都是一样的

意义：存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大

答案：定义表areas，表结构如下：

•  id
• atitle
• pid

 因为省没有所属的省份，所有可以填写为null

城市所属的省份pid，填写省所对应的编号id

这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id

在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息

例子1：查询河南省所有的市

select * from  areas as  a1
inner join areas as a2 on a1.aid=a2.pid
where 
    a1.atitle='河南省';

例子2：查询郑州市的所有区

select * from areas as a1
inner join areas as a2 on a1.aid=a2.pid
where 
    a1.atitle='郑州市';

例子3：查询河南省的所有的市区

select * from areas as a1
inner join areas as a2 on a1.aid=a2.pid
inner join areas as a3 on a2.aid=a3.pid
where 
    a1.title='河南省';

需求语句

-- 需求4:查询河南省所有的市
-- 使用内连接
select * from areas a1
inner join areas a2 on a1.aid=a2.pid
where a1.atitle='河南省';
-- 使用左连接
select * from areas a1
left join areas a2 on a1.aid=a2.pid
where a1.atitle='河南省';

-- 需求5:查询河南省的所有的市和区
-- 说明:想要实现三级行政单位显示，需要分别处理省和市及市和区(三表连查)
select * from areas a1
left join areas a2 on a1.aid=a2.pid
left join areas a3 on a2.aid=a3.pid
where a1.atitle='河南省';

 

---

 

5，子查询

子查询: 在一个select 语句中，嵌入了另外一个 select 语句，那么嵌入的 select 语句称之为子查询语句

主查询:外层的 select 语句称之为主查询语

主查询和子查询的关系：

• 子查询是嵌入到主查询中的
• 子查询是辅助主查询的，要么充当条件，要么充当数据源
• 子查询是可以独立使用的语句，是一条完整的select语句

子查询充当条件

例子1：查询大于平均年龄的学生

-- 查询班级学生的平均年龄
select avg(age) from students;
-- 查询大于平均年龄的学生
select * from students where age>21.4167;

select * from students where age>(select avg(age) from students);

例子2：查询王昭君的成绩，要求显示成绩

-- 学生表中查询王昭君的学号
select studentNo from students where name='王昭君';

成绩表中根据学号查询成绩
select * from scores where studentNo='001';

select * from scores
where studentNo=(select studentNo from students where name='王昭君');

例子1和例子2中：子查询返回的结果是有一个值（一行一列），这种称之为标量子查询

需求语句

-- 需求6:查询价格高于平均价的商品信息
-- 求取平均价
select avg(price) from goods
-- 说明:充当子查询的语句需要使用括号括起来(运算优先级括号最高!)
select * from goods where price > (select avg(price) from goods);

-- 需求7: 查询所有来自并夕夕的商品信息，包含商品分类
-- 子查询语句充当数据源:
select go.*, ca.id cid,ca.typeId ctid,ca.cateName from goods go
left join category ca on go.typeId=ca.typeId;
-- 注意: 连接查询的结果中，表和表之间的字段名不能出现重复，否则无法直接使用
-- 解决:将重复字段使用别名加以区分(表名* : 当前表的所有字段)
select * from (select go.*, ca.id cid,ca.typeId ctid,ca.cateName from goods go
left join category ca on go.typeId=ca.typeId) new
where new.company='并夕夕';

 

---

 

数据库设计

E-R模型

数据库能够有效存储现实世界中有意义的数据，通过E-R图能够更加有效的模拟现实世界

E-R模型的基本元素是：实体、联系和属性

• E表示entry，实体: 描述具有相同特征事物的抽象
• 属性:每个实体的具有的各种特征称为属性
• R 表示relationship，联系: 实体之间存在各种关系，关系的类型包括包括一对一、一对多、多对多

举例说明：

学生，就是一个实体，其具有属性有：学号、姓名、年龄、班级等

学生与学生之间存在角色关系，组长和组员，他们之间有一对多的关系

1.1，一对一

实体A对实体B为1对1，则在表A或表B中创建一个字段，存储另一个表的主键值

1.2，一对多

实体A对实体B为1对多:在表B中创建一个字段，存储表A的主键值

1.3，多对多

实体A对实体B为多对多: 新建一张表C，这个表只有两个字段，一个用于存储A的主键值，一个用于存储B的主键值

 

 

---

 

主键和外键

主键：

• 定义：唯一的标识一条记录，不能重复，不能为空
• 作用：用来保证数据的完整性
• 个数：一个表主键只能有一个

外键：

• 定义：一表的属性是另一表的主键，可以重复，可以为空
• 作用：用来和其他表建立联系
• 个数：一个表可以有多个外键

设置主键：

语法格式：

在创建数据库表时，create table中指定主键

字段名 数据类型 primary key

举例说明

# 创建班级表（班级编号、班级名称）,以班级编号为主键
create table class(
    id int unsigned primary key auto_increment,
    name varchar(10)        
);

删除主键

语法格式

alter table 数据表名 drop primary key;

举例说明

alter table class drop primary key;

设置外键

语法格式

在创建数据库表时，create table中设置外键

constraint 外键名 foreign key(自己的字段) references 主表(主表字段);

举例说明

-- 创建学生表，以班级编号关联班级表
create table student(
    name varchar(10),
    class_id int unsigned,
    constraint stu_fk foreign key(class_id) references class(id)
);

删除外键

语法格式

alter table 表名 drop foreign key 外键名称;

举例说明

-- 删除表student的stu_ibfk_1外键
alter table student drop foreign key stu_fk;

 

---

 

索引 

• 定义：快速查找特定值的记录
• 作用：提供查询排序的速度
• 个数：一个表主键只能有一个
• 注意：如果大量增加索引设置，会严重影响除数据查询操作以外的其他操作(增/删/改)的操作效率，不方便过多添加。

设置索引

语法格式：

create index 索引名称 on 表名(字段名称(长度))

举例说明

create index name_index on create_index(name(10))

删除索引

语法格式：

drop index 索引名称 on 表名;

举例说明

drop index name_index on create_index;

 

案例准备：

右键点击某个数据库->运行sgl文件->选择test_index.sql->点击开始

-- 开启运行时间监测
set profiling=1;
-- 查找第一万条数据test1000
select * from test_index where title='test1000';
-- 查看执行的时间
show profiles;
-- 为表title_index的title列创建索引
create index title_index on test_index(title(10));
-- 执行查询语句
select * from test_index where title='test1000';
-- 再次查看执行的时间
show profiles;

 

---

 

Navicat打开mysql命令行模式

 

 

学习自：黑马测试

标签：语句,goods,--,查询,students,SQL,where,select
From： https://www.cnblogs.com/jiushao-ing/p/17564208.html