Mysql (五)

create table info (
id int primary key,
name varchar(10),
score decimal(5,2),
address varchar(20),
hobbid int(5));

SELECT * FROM info;

排序语法：关键字排序
升序ASC     降序DESC
默认排序方式 升序
配合order by语法使用

SELECT * FROM info ORDER BY name DESC;

多列关键字排序，第一个参数有相同的值，第二个字段才有意义(才会排序) 
SELECT * FROM info ORDER BY hobbid DESC,id;#第一个有相同值，第二个字段升序排
SELECT * FROM info ORDER BY score DESC,id;  #第一个没有相同值，第二个没有升序排

where 条件的筛选(比较符 > < =  区间判断)
and     or
 

嵌套多条件
SELECT * from info where score > 70 or ( score > 0 and score < 60);
分组查询 对查询结果进行分组  使用group by语句
group by 只能配合 聚合函数一起使用
聚合函数类型 ：
统计 count()  求和 sum()  求平均数 avg()  最大值max() 最小值min()
聚合函数分组语句中,所有非聚合函数列只要出现在函数后面 ，后面也要在grop by语句中
SELECT count(name),hobbid,name from info group by hobbid,name;
where条件不能放在group by里面  要放条件只能写 having xxx
SELECT count(name),hobbid,score from info group by hobbid,score having score>=80;
 

limit1,3        1是位置偏移量 可选参数 可以不写  不写 默从是0, 即从第一行开始
SELECT * from info limit 1,3;

表和列的别名  在实际工作中，表和列名可能很长，写起来不方便，如果需要多次声明表和列时，全部展示太复杂，设置别名使书写简化
#可读性增加，更简洁明了
SELECT name as 姓名,score as 成绩 from info;
SELECT name  姓名,score  成绩 from info;
表的别名
SELECT i.name 姓名,i.score 成绩 from info as i;
SELECT i.name 姓名,i.score 成绩 from info i;

复制表  
只能复制数据，不能复制结构  约束 索引
create table test as select * from info;

复制筛选的数据到新表
create table test1 as select * from info where score >= 60;

通配符：
配合like 模糊查询 使用  
 %表示 0个,1个或者多个字符    x%以x为开头     %x以x结尾
  _ 表示单个字符 
select * from info where address like 's%';
select * from info where address like 's_';
 

子查询   内查询  嵌套查询 select（select）  select语句中又嵌套了一个select
先查询里面 再查询外面,先执行子查询，外面再根据子查询条件的结果进行查找
子查询可以说多个表，也可以是同一张表
关联语句 in     not in        exists
子查询的结果只能有一个列
update info set score=80 where id in(select id from test where id=4);
exists 判断子查询的结果是否为空  空 false   不为空 true
select count(*) from info where exists(  select id from test where score >80  );
这里不是 in  或not in 值没有传递给主表
这里只是判断，结果为空 则不执行  ，如果不为空则执行前面的 结果是7 只要不为空就是7 执行的是count(*)

查询分数 ，如果分数小于50  则统计info的总字段数
count 如果加了特定字段必须用group by
select count(*) from info where exists(  select score from test where score <50  );
select count(*) from info where score <50;

子查询中  多表查询和别名
多表查询不超过3个，超过会降低速度

视图
视图是一个虚拟表 表的数据基于查询的结果生成
视图可以简化复杂的查询，隐藏复杂的细节。访问数据更安全。
视图是多表数据的集合体

视图和表之间的区别
1.存储方式：表是实际数据行，视图不存储数据，仅仅是查询结果的虚拟表
2.数据更新：更新表，视图数据也更新  
3.占用空间:表实际占用空间，视图表不占用空间，只是动态结果的展示
视图表的数据可能是一张表的部分查询数据，也可能是多个表的部分查询数据

查询库中所有视图
show full tables in xy102 where table_type like 'VIEW';

创建视图
create view test2 as select * from info where score >=80;
select * from test2;  #  等于select * from info where score >=80
5.7之后更新视图原表也变化
update test2 set score=90 where id=2;
没有原表的索引 约束
desc test2;
删除视图
drop view test2;

视图表就是查询语句的别名，有了视图表可以简化查询语句
表的权限不一样 因为库的权限有控制   查询视图表的权限相对低 既保证原表数据安全，也简化查询过程

连接查询
两张表或多个表的记录结合起来，基于这些表的共同字段，进行数据拼接
首先要确定一个主表作为结果集，然后把其他表的行有选择性的选到主表的结果上

内连接：两表或多表之间符合条件的数据记录的集合 取交集  
INNER JOIN 

左连接 左外连接 left join  或 left outer join
以左边的表为基础，接收左表的所有行，以左表的记录与右表的记录进行匹配，匹配左表的所有，以及右表中符合条件的行，不符合的显示null值
SELECT * from test1 a left join test2 b on a.a_name=b.b_name;
#以比较条件为标准 只要条件符合就展示结果

#右连接 右外连接 right join  或 right outer join 同左连接
SELECT * from test1 a right join test2 b on a.a_name=b.b_name;