单表查询

1丶基本查询

  1. 查询所有列:

select * from 表名;其中＊表示查询所有列，而不是所有行的意思。

  1. 查询指定列:
select 列1，列2，列n from 表名；
  1. distinct

完全重复的记录只显示一次:在查询的列之前添加distinct

select distinct name from teacher #把重复

2、排序(所谓升序和降序都是从上往下排列)

  • 1.升序:select * form 表名 order by 列名 (ASC ); ()里面的内容为缺省值；
  • 2.降序:select * from 表名 order by 列名 DESC;
  • 3.使用多列作为排序条件： 当第一列排序条件相同时，根据第二列排序条件排序(当第二列依旧相同时可视情况根据第三例条件排序)。eg:select * from 表名 order by 列名1 ASC, 列名2 DESC;意思是当列名1的值相同时按照列名2的值降序排。

3、聚合函数

把多条数据聚合成一条

  • 1.count:select count(列名) from 表名；，记录行数。
  • 2.max:select max(列名) from 表名；，列中最大值。
  • 3.min:select min(列名) from 表名；，列中最小值。
  • 4.sum:select sum(列名) from 表名；，求列的总值，null 和字符串默认为0。
  • 5.avg:select avg(列名) from 表名;，一列的平均值。

4、分组查询

分组查询的信息都是组的信息，不能查到个人的信息，其中查询组的信息是通过聚合函数得到的。

语法：select 分组列名，聚合函数1,聚合函数2 from 表名 group by 该分组列名；其中分组列名需要的条件是该列名中有重复的信息。

查询的结果只能为：作为分组条件的列和聚合函数；查出的信息都是组的信息。

分组查询前，还可以通过关键字where先把满足条件的人分出来，再分组。语法为:select 分组列，聚合函数 from 表名 where 条件 group by 分组列；

分组查询后，也可以通过关键字having把组信息中满足条件的组再细分出来。语法为：select 分组列，聚合函数 from 表名 where 条件 group by 分组列 having 聚合函数或列名(条件)；

select gander,avg(age) avg_age,sum(age) sum_age from student GROUP BY gander HAVING  gander = '男'

!!!!!理解

select gander,avg(age) from student group by gander having gander = '男' #先把男和女的分组,再把男的留下了,再求平均年龄
select gander,avg(age) from student where gander = '男' #先把男女筛选,留下男的  再求平均年龄

 

 

5、LIMIT子句(mysql中独有的语法)

LIMIT用来限定查询结果的起始行，以及总行数。

例如：select * from 表名 limit 4，3；表示起始行为第5行，一共查询3行记录。

--如果一个参数 说明从开始查找三条记录
SELECT id,name,age,gander FROM student limit 3
--如果两个参数 说明从第三行起（不算），向后查三条记录
SELECT id,name,age,gander FROM student limit 3,3

6丶联合查询

union 过滤

select id ,name from teacher union select id ,name from teacher2 #把两个表联合查询 把重复的过滤

union all 不过滤

select id ,name from teacher union select id ,name from teacher2 #把两个表联合查询 不进行过滤

多表查询

笛卡尔积：简单来说就是两个集合相乘的结果，集合A和集合B中任意两个元素结合在一起。

1、内连接

内连接

内连接查询操作只列出与连接条件匹配的数据行，使用INNER JOIN或者直接使用JOIN 进行连接。

内连接可以没有连接条件，没有条件之后的查询结果，会保留所有结果（笛卡尔集），与后面分享的交叉连接差不多。

在连接条件中使用等于号（=）运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

查询结果，注意列数是 4 列，两张表的字段直接拼接在一起，重复的字段在后面添加数字序列以做区分

通俗讲就是根据条件，找到表 A 和 表 B 的数据的交集

例子：

普通的多表查，课内连接接通相同
SELECT * from teacher t , course c where t.id = c.t_id   
（这样会先生成笛卡尔积，效率可能略低）

SELECT * from teacher t JOIN course c on t.id = c.t_id 
SELECT * from teacher t inner JOIN course c on t.id = c.t_id 

结果：只有满足条件的会显示，5号老师没课程，5号课程没老师都不会显示
1   王宝强 1   数学  1
2   贾宝玉 2   语文  2
3   温迪  3   c++ 3
4   路人甲 4   java    4

2、外连接（常用）

外连接不只列出与连接条件相匹配的行，而且还加上左表(左外连接时)或右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。

（1）左连接（左外连接）

查询结果如下

根据条件，用右表（B）匹配左表（A），能匹配，正确保留，不能匹配其他表的字段都置空 Null。

也就是，根据条件找到表 A 和 表 B 的数据的交集，再加上左表的数据集， Venn 图表示就是

红色部分代表查询结果

例子：

SELECT * from teacher t LEFT JOIN course c on t.id = c.t_id 
结果：只有满足条件的会显示，5号老师没课程，依然显示，5号课程没老师都不会显示，
左边表的所有数据都显示

1   王宝强 1   数学  1
2   贾宝玉 2   语文  2
3   温迪  3   c++ 3
4   路人甲 4   java    4
5   路人乙
（2）右连接（右外连接）

根据条件，用左表（A）匹配右表（B），能匹配，正确保留，不能匹配其他表的字段都置空 Null。

也就是，根据条件找到表 A 和 表 B 的数据的交集，再加上右表的数据集， Venn 图表示就是

例子：

SELECT * from teacher t right JOIN course c on t.id = c.t_id

结果：只有满足条件的会显示，5号老师没课程不显示，5号课程没老师都，依然显示，
右边表的所有数据都显示

1   王宝强 1   数学  1
2   贾宝玉 2   语文  2
3   温迪  3   c++ 3
4   路人甲 4   java    4
          5    php

 

3、全连接，mysql不支持，oracle支持

目前我的 MySQL 不支持此种方式，可以用其他方式替代解决，使用union。

理论上是根据条件找到表 A 和 表 B 的数据的交集，再加上左右表的数据集

总结:

  1. 查询交集可以使用union
  2. 查询并集使用inner（内连接）
  3. left join（左连接）左表全显示，右边为空不显示
  4. right join（右边）右表全显示，左表为空不显示

 

 

子查询

1、where 型子查询

将查询结果当条件

例子：查询有一门学科分数大于八十分的学生信息

SELECT * from student where id in
(select DISTINCT s_id from scores where score > 90);

1 2

where 型子查询，如果是 where 列 =（内层 sql） 则内层 sql 返回的必须是单行单列，单个值。

where 型子查询，如果是 where 列 in（内层 sql） 则内层 sql 返回的必须是单列，可以多行。

2、from 型子查询

在学习 from 子查询之前，需要理解一个概念：查询结果集在结构上可以当成表看，那就可以当成临时表对他进行再次查询：

取排名数学成绩前五名的学生，正序排列。

select * from  (SELECT s.id,s.name,e.score,c.`name` cname from student s LEFT JOIN scores e
on s.id = e.s_id left JOIN course c on e.c_id = c.id
where c.`name` = '数学' order by e.score desc limit 5 ) t ORDER BY t.score

 

 

 

视图入门

视图其相当于从原来的数据表中获取部分数据，然后新建一个只可创建、查询和删除的新表来存放这些数据（一般情况下)，可以理解成把想要的数据部分截图下来保存，供以后查询用，此时视图只是为了满足某些数据查询而建立的对象。 当然，也存在可以修改的视图，并且当修改视图的数据时，原来表里的数据也会一同修改，此时视图的作用就是为了保护数据的安全性而只把部分数据展示出来供他人使用，有兴趣的自学。 其和表的区别如下:

  1. 表直接将数据存储在磁盘上，视图是将sql语句存储到磁盘上
  2. 视图是建立在表的基础上，表存储数据库中的数据，而视图显示已经在表中的数据的外观
  3. 表是静态的，而视图是动态的，意思是表中数据发生了改变，其建立在表基础的视图跟着改变
  4. 通过视图不能改变表中数据（—般情况下)
  5. 删除视图，表不受影响，而删除表，视图不再起作用
  6. 视图本身没有数据，只保存了sql语句(本质)

其和表的区别如下:

  1. 举个例子，你们公司使用了别人的开票系统，开票需要一些信息，你能把的的用户信息，订单信息等等这些表的权限给那个开票公司吗，就能能给，私密信息比如密码能给吗?肯定不行
     
    解决的方案就是使用视图，把他需要的数据搞成一张表，给他个权限，他只能看这个视图的数据，而不能看整个表的数据。
1.创建视图

基本格式：

create view 视图名(列名1，列名2，...)as select 语句
2.显示视图

基本格式：

show create view 视图名

在视图创建完成后，就可以把视图当作一个表来查询

sq1> create VIEw temp(id ,name ,age) as SELECT id, name ,age from student where age > 20
建视图temp，里面记录了student表的id,name，age
sq1> select * from temp ;
现可以正常查询
删除视图

基本格式：

drop view 视图名