-- 需求6:查询价格高于平均价的商品信息
select * from goods where price >(select avg(price) from goods);
-- 需求7:查询所有来自拼多多的商品信息,包含商品分类
select * from goods;
select * from goods left join category on goods.typeId=category.typeId where company in ('拼多多','拼夕夕');
-- select * from goods where company in('拼多多','拼夕夕');
select * from category
inner join (select * from goods where company in('拼多多','拼夕夕')) a on category.typeId=a.typeId;
-- 需求8:查询价格在25-100之间的商品信息
-- in(范围)
select * from goods where price between 25 and 100;
select price from goods where price between 25 and 100;
select * from goods where price in(25,30,77,30,72,25);
select * from goods where price in (select price from goods where price between 25 and 100);
-- some /any
select * from goods where price =some(select price from goods where price between 25 and 100);
select * from goods where price =any(select price from goods where price between 25 and 100);
-- all
select * from goods where price =all (select price from goods where price between 25 and 100);
select * from goods where price !=all (select price from goods where price between 25 and 100);
-- --------------------------外键--------------------
drop table if exists class;
create table class(
id int unsigned primary key auto_increment,
name varchar(10)
);
drop table if exists stu;
create table stu(
name varchar(10),
class_id int unsigned
);
-- 外键-------------------------------------------------------------------------------------
-- 扩展1:对于已经存在的表添加外键
-- alter table 从表名 add foreign key(从表字段) references 主表名(主表主键)
alter table stu add foreign key (class_id) references class(id);
-- 扩展2:查看外键和删除外键
show create table stu;
-- CREATE TABLE `stu` (
-- `name` varchar(10) DEFAULT NULL,
-- `class_id` int unsigned DEFAULT NULL,
-- KEY `class_id` (`class_id`),
-- CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
-- 删除外键
alter table stu drop foreign key stu_ibfk_1;
-- -----------------------------索引----------------------------------------
-- 开启时间检测
set profiling =1;
-- 查询示例数据 num=1000的值
select * from test_index where num =100;
-- 查看运行时间
show profiles;
-- 添加索引
create index num_index on test_index(num);
-- 再次执行查询数据操作
select * from test_index where num =100;
-- 查看运行时间
show profiles;
-- 扩展1:查看索引
show index from test_index;
-- 扩展2:创建表时添加索引
create table create_index(
id int primary key,
name varchar(10) unique, -- unique : 设置端唯一值
age int,
key(age) -- 指定添加索引方法
);
-- 查看索引
show index from create_index;
-- 扩展3 : 删除索引
-- drop index 索引名称 on 表名;
drop index age on create_index;
sql语句之子查询,外键和索引
于 2023-03-23 21:34:50 首次发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)