sql语句之子查询，外键和索引

-- 需求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;