数据库实操练习

-- 00 获取数据库版本信息
select version(); # 5.5.53

-- 01 查询
select * from student;

-- 02 创建表前删除表、创表语句
drop table if exists goods;

create table goods(
id int  unsigned primary key auto_increment,
goodsName varchar(20),
price decimal(6, 2),
num int,
company varchar(20),
remark varchar(30)
);

-- 03 修改数据
update goods set price=5600.00,num=50,company='某宝' where id = 2;

-- 08 添加商品
insert into goods values
(0, '台式电脑', 6000.00, 100,'某东', '某东'),
(0, '冰箱', 6000.00, 100,'某东', '某东')
;

delete from goods where id = 1;
select * from goods;

alter table goods add isdelete int;
update goods set isdelete = 1;
select * from goods where isdelete = 1;


-- 07 三种删除数据方式
-- delete from student;
select * from student;
-- truncate table student;
-- 删除表
drop table student;

-- 删除速度：drop > truncate > delete
*******************************************************************************

-- 04 排序 asc / desc
select * from goods order by price;
select * from goods order by num desc, price asc;
*******************************************************************************
-- 05 单表查询
select goodsName,price from goods;

-- 起别名
select goodsName  商品名称，price 价格 from goods;
select goodsName  商品名称 from goods;
select goodsName  商品名称, price 价格 from goods;
select goodsName as '商品名称', price '价格1' from goods;


-- 去重
select distinct(goodsName) from goods;

select * from goods;
-- 并列条件 另外还有 not / or
select * from goods where price > 100 and company = '并夕夕';

-- 模糊查询 like % _
select * from goods where remark like '%一次性口罩%';

-- _匹配单个字符
select * from goods where company like '_东';

-- 查询范围 范围从小到大
select * from goods where price between 100 and 1000;
 -- 不连续范围
select * from goods where company in ('某宝', '并夕夕');

select * from goods where remark is null;
select * from goods where remark is not null;
*******************************************************************************

-- 需求9：查询以下信息：商品信息总条数；最高商品价格；最低商品价格；商品平均价格；一次性口罩的总数量
 -- 聚合函数
 select count(*) from goods;
 select count(remark) from goods;
 
 select max(price) from goods;
 SELECT MIN(price) FROM goods;

-- 平均
select avg(price) from goods;

-- 一次性口罩的总数量
select * from goods where remark like '%一次性口罩%';
select count(*) from goods where remark like '%一次性口罩%';


-- 不生效
select sum(count) from goods where remark like '%一次性口罩%'; -- 需求实现

-- 需求10：查询每家公司的商品信息数量
select company 公司名称, count(*) 数量 from goods group by company;


-- select company 公司名称, count(*) 数量 from goods where count(*) > 2;  -- where后不可以用聚合函数
select company 公司名称, count(*) 数量 from goods group by company having count(*) > 2;

select company 公司名称, count(*) 数量 from goods group by company order by count(*) desc;

select company 公司名称, count(*) 数量 from goods group by company having company !='';
select company 公司名称, count(*) 数量 from goods group by company having company !='并夕夕';

-- 获取公司商品最贵
select company 公司名称, max(price) from goods group by company having company !='并夕夕';



-- 需求11：查询5-10行的所有数据 limit 起始索引，数据行数
select * from goods;

select * from goods limit 4, 6;
-- 起始索引可以省略 默认为0
select * from goods limit 6;

select * from goods limit 3, 3;

###############################################################################

-- 06 SQL 查询作业安排
-- 课上的后9个案例, 脱离笔记, 独立完成一遍!
-- 完成作业1之后, 完成课下作业2(晚自习发)
-- 作业完成后, 提交在线文档, 以小组形式, 进行互审
-- 互审过程中记录问题, 完成互审后, 联络老师进行问题沟通

-- 3种删除数据方法(所有数据)
-- delete from goods;
-- truncate table goods;
-- drop table goods;

-- 需求3: 准备商品数据, 查询所有数据, 查询部分字段, 起字段别名, 去重
select * from goods;
select goodsName, price from goods;
select goodsName 商品名称, price 价格 from goods;


-- 需求4: 查询价格等于30并且出自并夕夕的所有商品信息
select * from goods where price = 50 and company = '并夕夕';


-- 需求5: 查询全部一次性口罩的商品信息
select * from  goods where remark like '%一次性口罩%';


-- 需求6: 查询所有价格在30-100的商品信息
select * from goods where price between 30 and 100;


-- 需求7: 查询没有描述信息的商品信息
select * from goods where remark is null;

-- 需求8: 查询所有商品信息, 按照价格从大到小排序, 价格相同时, 按照数量少到多排序
select * from goods order by price desc, num asc;

-- 需求9：查询以下信息：商品信息总条数；最高商品价格；最低商品价格；商品平均价格；一次性口罩的总数量
select count(*) from goods;
select max(price) from goods;
select min(price) from goods;
select avg(price) from goods;
select count(*) 一次性口罩数量 from goods where remark like '%一次性口罩%';

-- 需求10：查询每家公司的商品信息数量
select company, count(company) from goods group by company;

select company 公司名称, count(*) 数量 from goods group by company having company != '并夕夕';
select company 公司名称, max(price) from goods group by company;

-- 需求11：查询5-10行的所有数据 limit 起始索引，数据行数
select * from goods limit 4, 6;

###############################################################################

-- 09 多表查询
select * from goods_breathmask;
select * from category;

-- 内连接
select * from goods_breathmask g inner join category  c on g.typeId = c.typeId;

-- select g.goodsName, c.cateName from goods_breathmask g inner join category  c on g.typeId = c.typeId;
select g.*, c.cateName from goods_breathmask g inner join category  c on g.typeId = c.typeId;

-- 左连接
select * from goods_breathmask g left join category c on g.typeId = c.typeId;

select * from category c left join goods_breathmask g on g.typeId = c.typeId;

-- 右连接
select * from goods_breathmask g right join category c on g.typeId = c.typeId;

-- 需求：显示所有价格低于30的商品信息，包含其对应的商品分类
select g.*,c.cateName from goods_breathmask g left join category c on g.typeId = c.typeId where g.price > 30;

###############################################################################

-- 10 自关联
select * from areas a inner join areas b where a.pid = b.aid;

select * from areas a1 inner join areas a2 where a1.aid = a2.pid;

select a.*, b.name 市名 from areas a inner join areas b where a.aid = b.pid;

-- 需求4：查询河南省的所有市
select * from areas a1 
inner join areas a2 on a1.aid = a2.pid 
where a1.name = '河南省';

-- 需求5：查询河南省的所有市和区
select * from areas a1 
inner join areas a2 on a1.aid = a2.pid 
left join areas a3 on a2.aid = a3.pid
where a1.name = '河南省';

###############################################################################

-- 11 子查询
-- 需求6：查询价格高于平均价的商品信息
select * from goods where goods.price > (select avg(price) from goods);

-- 需求7：查询所有来自并夕夕的商品信息，包括商品分类
select * from goods_breathmask;

select g.*, c.cateName from goods_breathmask g 
left join category c on g.typeId = c.typeId 
where g.company = '拼多多';

select * from goods_breathmask g 
left join category c on g.typeId = c.typeId 
where g.company = '拼多多';

select a.*, c.cateName from category c
inner join (select * from goods_breathmask g where g.company = '拼多多') a on c.typeId = a.typeId;

select * from (select * from goods_breathmask g where g.company = '拼多多') a
inner join category c on c.typeId = a.typeId;

-- 需求：查询在25-100之间的商品的价格
select price from goods where price between 20 and 100;
select * from goods where price in (select price from goods where price between 20 and 100);

select * from goods where price = some(select price from goods where price between 20 and 100);

select * from goods where price = any(select price from goods where price between 20 and 100);

-- 取反 查询不在25-100之间的商品的价格 的商品
select * from goods where price != all(select price from goods where price between 20 and 100);

select * from goods where price <> all(select price from goods where price between 20 and 100);
###############################################################################

--12 MySQL高级 外键
drop table if exists class;
create table class(
id int unsigned primary key auto_increment,
name varchar(10)
);

-- truncate table class;
insert into class values
(0, '一年级'),
(0, '二年级'),
(0, '三年级'),
(0, '四年级'),
(0, '五年级'),
(0, '六年级'),
(0, '七年级'),
(0, '八年级'),
(0, '九年级');

drop table if exists stu;
create table stu(
name varchar(10),
class_id int unsigned,
foreign key(class_id) references class(id)
);


-- 主表
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,
-- stu 表的 class_id 指向 class 表的 id, class_id 是

-- foreign key(自己的字段名) references 目标表名(目标表的主键)
foreign key(class_id) references class(id)
);

###############################################################################

-- 13 MySQL高级 外键索引
-- 开启时间监测
set profiling = 1;

-- 查询示例数据 num =10000的值
select * from test_index where num = 10000;

-- 查看运行时间
show profiles;

-- 对已存在的表添加索引
create index num_index on test_index(num);
select * from test_index where num = 10000;
show profiles;

show index from test_index;

--扩展2： 创表时添加
create table create_index(
id int primary key,
name varchar(10) unique,
age int,
key(age)
);

show index from create_index;

drop index age on create_index;
-- ###############################################################################
# 14 MySQL高级 循环创建数据_存储过程
drop table if exists datatest;
-- 创建 datatest 表
create table datatest(
    id int unsigned primary key auto_increment,
    num int
);

-- 修改句尾标识符为'//'
delimiter //
-- 如果存在 test 存储过程则删除
drop procedure if exists test;
-- 创建无参数的存储过程 test
create procedure test()                     
begin
        -- 声明变量 i
        declare i int; 
        -- 变量初始化赋值为 0
    set i = 0;
        -- 设置循环条件: 当 i 大于 10 时跳出 while 循环
    while i < 100 do
                -- 往 datatest 表插入数据
        insert into datatest values (null, i);
                -- 循环一次, i 加一
        set i = i + 1; 
        -- 结束 while 循环
    end while;
        -- 查看 datatest 表数据
    select * from datatest; 
-- 结束存储过程定义语句
end//
-- 恢复句尾标识符为';'
delimiter ;                                                             

-- 调用存储过程 test
call test();   
###############################################################################
-- 15 视图
create view v_goods as select g.*,c.cateName from goods_breathmask g left join category c on g.typeId = c.typeId where g.price > 1;

drop view v_goods;

-- 1060 - Dumplicate column name 'id'
-- create view v_goods as select * from goods_breathmask g left join category c on g.typeId = c.typeId;
###############################################################################

-- 16 日志
-- D:\phpStudy\MySQL\data\WINDOWS-74K72L3.log
show variables like 'general%';

set global general_log = 1;

-- 关闭日志
set global general_log = 0;
-- #################################################################
-- 17 字符串函数
-- 不是SQL语言通用语句，只是MySQL有的，了解即可

-- 把括号中的多个值连接成一个字符串
select concat('张', '三');

select concat(name, age) from student;

select length('abc');
select length('a我');
select length('和你我');
select length(name), name from student;

-- 内置函数可以出现在where后面的条件中
select * from student where length(name) = 9;

-- left字符串 从指定字符串左侧，截取指定数量的字符
select left('和你我abc', 3);

select left('和你我abc', 4);
select left(name, 1) from student;

-- right字符串 从指定字符串右侧，截取指定数量的字符
select right(name, 1) from student;

-- substring(字符串，开始位置， 截取长度）
select substring('我和你abc', 3, 2);
select substring('我和你abc', 2, 3);
select substring(name, 2,1) from student;

-- 查询李白的生日
select substring(card, 7, 8) from student where name = '李白';

-- 按生日大小排序
select * from student order by substring(card, 7, 8);

-- 去除空格
select ltrim(' abc');
select rtrim('abc ');
select trim(' abc ');