知识点一: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;
标签:name,Sage,day2,视图,Student,SQL,NULL,My,select
From: https://blog.csdn.net/WuMingf_/article/details/143581926