MySQL基础操作--join连接及关键字查询

（一）各种join连接

1.内连接（inner join）:产生的结果是A和B的交集（相同列里面的相同值）

select * from TableA as A inner join TableB B on A.PK = B.PK;

例子：

 

SELECT * FROM  a1  a INNER JOIN  b1  b on a.sname=b.sname;   结果如下：

2.Full/Left/Right/ Join:

select * from TableA a left(right/full) join TableB as B on A.PA = B.PK;

Full Join:产生的结果是A和B的并集（如果没有相同的值会用null作为值）

Left Join:产生表B的完全集，而A表中匹配的则有值（没有匹配的则以null值取代）

Right Join:产生表A的完全集，而B表中匹配的则有值（没有匹配的则以null值取代）

 

3.交叉连接（cross join）
又称笛卡尔连接，交叉连接返回两个集合的笛卡尔积。
select * from TableA cross join TableB;

 

（二）关键字

where子句：按照“条件表达式”指定的条件进行查询。

group by子句：按照“属性名”指定的字段进行分组。

having子句：有group by才能having子句，只有满足“条件表达式”中指定的条件的才能够输出。

group by子句通常和count()、sum()等聚合函数一起使用。

order by子句：按照“属性名”指定的字段进行排序。排序方式由“asc”和“desc”两个参数指出，默认是按照“asc”来排序，即升序。

order by 排序查询、asc升序、desc降序by 排序查询、asc升序、desc降序

例子：

select 编号,AVG(年龄) from 信息表 group by 编号 having AVG(年龄)>(select AVG(年龄) from 信息表 where 编号='01') order by AVG(年龄) descAVG(年龄) from 信息表 group by 编号 having AVG(年龄)>(select AVG(年龄) from 信息表 where 编号='01') order by AVG(年龄) desc

4.distinct:

消除重复行

5.case...when...else...end

例子1：

 

SELECT age,sname,hobby, CASE sex WHEN 'M' THEN '女' WHEN 'F' THEN '男' ELSE '其它' END 别名 FROM b1;

例子2：字段age中为'M'、'F'的年龄加起来

 

SELECT  SUM(CASE sex WHEN 'M' THEN age ELSE 0 END ) as '女', SUM(CASE sex WHEN 'F' THEN age ELSE 0 END )as '男' FROM b1;

例子3：如果根据两个条件修改同一个字段中的值，执行两条SQL语句是不可以的，因为比如条件为：修改年龄大于35的人物减于10，小于35的年龄加上10，有可能执行第一条SQL语句后年龄小于35的大于了35，再执行第二条语句，之前所做的修改会恢复到原状，则这时候应该做到保持两个条件是同时执行的：

update b1  SET age=CASE WHEN age <=35 THEN age+10 WHEN age >=35 THEN age-10 ELSE age END;

例子4：每个年级统计男生和女生的数量各是多少

SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1   ELSE NULL    END) 男生数,                COUNT (CASE WHEN sex = 2 THEN 1   ELSE NULL    END) 女生数 FROM students   GROUP BY grade;

6.distinct if(参数,参数，参数)

这三个参数从左到右分别的意义是：另外一个字段的条件筛选，要计算的字段，null不计算，加distinct就是对if第二个参数计算的时候去重，例子：

SELECT COUNT(DISTINCT IF(score>70,name,NULL)) as score FROM student; 表示查询分数大于70分的人，且名字不为空的name的个数

7.union 跟 union All的区别？

①union不允许有重复值，union All 允许有重复值；

②union 对结果会去重，union All会列出所有结果，结果会重复；

③Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。

④从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。

 

select * from student where id < 4 union select * from student where id>2 and id<6;

select * from student where id < 4 union all select * from student where id>2 and id<6; union  all要求所有表的列数要相同，最后的结果只显示第一个表的列明

8.@代表声明一个变量；

：=代表给变量赋值；