MySql数据库常用高级命令-2

-- 创建数据库
create database test;
-- 使用数据库
use test;
-- 创建students学生表
create table students(
id int primary key auto_increment,
name varchar(20)
);
-- 插入学生表数据
insert into students values(5,'大牛');
insert into students values(1,'小明'),(2,'小强'),(3,'小李'),(4,'小黄');
-- 查询学生表全部数据
select *from students;
-- 创建学科表
create table subjects(
id int primary key auto_increment,
sbname varchar(50)
);
-- 插入学科表数据
insert into subjects values(1,'语文'),(2,'数学'),(3,'英语'),(4,'物理');
-- 查询学科表全部数据
select *from subjects;
-- 创建分数表
-- 外键stuid关联学生表的id
-- 外键subid关联学科表的id
-- score分数为长度为5，2位小数的数字
create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2),
foreign key(stuid) references students(id),
foreign key(subid) references subjects(id)
);
-- 查询分数表全部数据
select *from scores;
-- 插入分数表数据
insert into scores values(2,1,2,80),(3,2,2,90),(4,3,4,60),(5,4,3,69);
-- 关联学生表，学科表形成一张大表来查询学生姓名，学科名称，分数（inner join后面的表的列名在on后面一定要出现，否则报错）
select students.name,subjects.sbname,scores.score 
from scores 
inner join students on scores.stuid = students.id
inner join subjects on scores.subid = subjects.id;
-- 关联学生表形成的大表并查询大表所有数据
select *from scores inner join students on students.id = scores.stuid;
-- 左连接，以left join左边的表为主表，右边的为副表进行关联，主表数据全部存在，副表数据关联以后不存在数据的用NULL填充
select *from students left join scores on students.id = scores.stuid;
-- 右连接，以right join右边的表为主表，左边的为副表进行关联，主表数据全部存在，副表数据关联以后不存在数据的用NULL填充
select *from scores right join students on students.id = scores.stuid;
-- 关联学生表，查询学生姓名并计算学生的平均分，然后通过学生id进行分组，按照平均分大小升序显示
select name,avg(score) as avg from scores
inner join students on scores.stuid=students.id
group by stuid 
order by avg;
-- 关联学生表，查询学生姓名并计算学生的总分，然后通过学生姓名进行分组，按照总分大小升序显示
select name,sum(score) as sum from scores
inner join students    on scores.stuid = students.id
group by name 
order by sum;
-- 关联学科表，查询学科名称并计算学科的平均分，然后通过学科名称进行分组，按照学科平均分大小降序显示
select sbname,avg(score) as sbavg from scores
inner join subjects on subjects.id = scores.subid
group by sbname
order by sbavg desc;
-- 自关联，定义一个外键pid关联自身主键id
create table areas(
id int primary key auto_increment not null,
title varchar(20),
pid int,
foreign key(pid) references areas(id)
);
-- 查询所有区域名称
select *from areas;
-- 查询pid为空的区域的数量
select count(*) from areas where pid is null;
-- 查询主键id为440100的区域数据
select * from areas where id=440100;
-- 查询山西省的所有城市
select city.* from areas as city
inner join areas as province on city.pid = province.id
where province.pid is null and province.title='山西省';
-- 查询南昌市的所有区县
select dis.* from areas as dis
inner join areas as city on dis.pid = city.id
where city.title='南昌市';
-- 创建视图用来保存一段查询语句
create view v_stu_sub_sco as 
select stu.*,sub.sbname,sco.score from scores as sco
inner join students as stu on sco.stuid = stu.id
inner join subjects as sub on sco.subid = sub.id;
-- 修改视图保存的查询语句
alter view v_stu_sub_sco as 
select stu.*,sub.sbname,sco.score from scores as sco
inner join students as stu on sco.stuid = stu.id
inner join subjects as sub on sco.subid = sub.id
where sco.score<=90;
-- 通过视图执行查询
select *from v_stu_sub_sco;
-- 展示分数表的创建代码
show create table scores;
-- 事务*
-- 开启事务
begin; 
-- 更新数据
update students set name='咯明' where id = 1;
-- 事务提交
commit;
-- 事务回滚（只回滚最近的一条数据）
rollback;
-- 索引*（加快表的查询速度）
-- 展示学生表的索引
show index from students;
-- 测试创建索引后的数据查询时间
set profiling = 1;
-- 查询title为北京市的全部区域
select *from areas where title = '北京市';
show profiles;
-- 创建索引
create index titleIndex on areas(title(20));
-- 删除索引
drop index titleIndex on areas;
-- areas测试数据附加在后面，需要的下载