My_SQL day2

知识点一：mysq查询方法

        排序

        限制

        去重

        模糊查询

        范围查询

        聚合函数

        分组

知识点二：连接查询

        1.内连接

        2.外连接

知识点一：mysql查询方法

is null            is not null

create table t1(id int,name varchar(10));
insert into ti(id)  values(1),(2);
insert into t1 values(3,"jianqi");

select * from t1 where name is not null;
+------+--------+
| id   | name   |
+------+--------+
|    3 | jianqi |
+------+--------+

select * from t1 where name is null;
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
|    2 | NULL |
+------+------+

and or not

#and
select  *from Student where 18<=Sage and Sage<=19;  #既符合左边又符合右边

#or
select  *from Student where 18<=Sage or Sage<=19;   #两边有一个等式成立

#not
mysql> select  *from Student where not Sage<=19;    #排除

排序

#正序
select * from Student order by Sage;
#倒序
select * from Student order by Sage desc;

限制

select * from Student limit 2;

#从第三行开始显示两条数据
select * from Student limit 2;

去重

                字段       表名
select distinct Sage from Student;

模糊查询

select * from Student where Sname like "王%";   #任意多个
select * from Student where Sname like "刘_";   #匹配单个字符

范围查询

select * from Student where Sage in (18,20);
#18-----19
select * from Student where Sage between 18 and 19;

聚合函数

#求和
select sum(Sage) from Student;

#统计数量
select count(*) from Student;

#求平均
select avg(Sage) from Student;

#连接字符串
select group_concat(Sname) from Student;

#最小Sage
select min(Sage) from Student;

#最大Sage
select max(Sage) from Student;

分组

#group by
select subject_number from grades group by subject_number;
以什么分组  就要用什么来显示

select subject_number,count(*) from grades group by subject_number;

#having  可以筛选
select student_number,count(*) from grades group by student_number,grade having grade>=80;

子查询

select distinct Sage from (select Sage from Student order by Sage)as stu_age;

内连接

#两张表里面相同的名字
select * from t1 join t2 on t1.name=t2.name;

外连接

#左外连接
select * from t1 left join t2 on t1.name=t2.name;

+------+--------+------+--------+
| id   | name   | id   | name   |
+------+--------+------+--------+
|    1 | NULL   | NULL | NULL   |
|    2 | NULL   | NULL | NULL   |
|    3 | jianqi |    6 | jianqi |
+------+--------+------+--------+

#右外连接
select * from t1 right join t2 on t1.name=t2.name;
+------+--------+------+--------+
| id   | name   | id   | name   |
+------+--------+------+--------+
| NULL | NULL   |    4 | fufu   |
| NULL | NULL   |    5 | beibei |
|    3 | jianqi |    6 | jianqi |
+------+--------+------+--------+

视图

什么是视图：视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。
视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

好处：提高重用性；对数据库进行重构（不会影响数据运行）；提高安全性（给不同用户提供视图表格）；让数据更加清晰（想要什么样的数据就建什么样的表格）

缺点：表依赖关系（根据数据库中的基础表创建了视图，每当改变有关相联表的时候，都会改变视图；性能（从数据库的视图查询数据可能会很慢）

create view view_tb_paixu as select * from Student order by Sage;