MySQL之各种查询

一、数据库的简单查询
单表查询
1.select 字段1， 字段2， from 表； //查询表里的指定字段
如，select pwd from member;
2.select * from 表 ; //查询所有字段 (不推荐，性能低下)
3.select … from 表 where 表达式A and 表达式B and…; //同时满足多个条件来进行查询
如，select pwd from member where id >2 and pwd=123456 ;
4.select … from 表 where 字段a = 值a or 字段b= 值b or 字段c = 值c ;//满足其中一个或多个条件进行查询
如，select pwd from member where id >2 or pwd=123456 ;
注：3,4中的… 可以是所有字段或者是指定字段

多表查询
1.select … from member,loan; //笛卡尔积，无关联的时候，结果展示的是member的记录数乘以loan的记录数
2.select … from member ,loan where member.Id=loan.MemberID; // 从笛卡尔积再用where过滤
3.select … from member t1 ,loan t2 where t1.Id=t2.MemberID; // 使用别名的方式 即用t1代替member ,用t2代替loan
4.select … from member as t1 ,loan as t2 where t1.Id=t2.MemberID; // 使用as的方式 即用t1代替member ,用t2代替loan
注：1~4中的… 可以是所有字段或者是指定字段

排序

1. ORDER BY 字段1，字段2,…； DESC 按字段倒序排列
   如，order by ID desc; // 按ID倒序排列
2. ORDER BY字段1，字段2,…； ASC 按字段 升序排列
   如，order by ID asc; //按ID升序排列
   注： 1）2个字段的排序：以第一个先排序，当第一个相同时，再以第二个排序。
   2）默认升序
   3）多个字段时：从左到右进行排序。

二、数据库的高级查询
1、in的用法
select … from 表名 where 条件字段 in （数据集）
注：数据集可以是具体的某几个值：值a，值b，…，值n;也可以是通过一个子查询得到的数据集。

2、like—模糊查询
% —占位，表示任意字符，可为空，不加%即完全匹配
select * from member where RegName like’%陈%’; 查询member表中RegName 字段包含陈的信息
select * from member where RegName like’%菲’; 查询member表中RegName 字段以菲结尾的信息
select * from member where RegName like’刘%’; 查询member表中RegName 字段以刘开始的信息

3、group by—分组
用法：select 字段1，字段 2, 聚合函数 from 表 group by 分组的字段 having 过滤条件；
注：聚合函数：sum()、count()、avg()、min()、max()等

4、between – 条件字段处于两个数据范围内
用法：select … from 表 where 条件字段 between 数值A and 数值B;

5、limit —分页即取查询结果的前n条
用法：select … from 表名 limit m,n;
注：m----索引值从m开始
n----要获取的数据数目
M是索引值，从0开始，想从第二条数据开始获取，则M为1，N为要获取数据的数目,如果N大于总数，则显示所有剩余的数。
该命令只能放在命令行的最后。

6、常用MySQL函数
min(字段A) //字段A的最小值
max(字段A) //字段A的最大值
avg(字段A) //字段A的平均值
sum(字段A) //字段A的和，，括号也可以是表达式
sysdate() //系统当前日期时间
curdate() //系统当前日期
curtime() //系统当前时间
year(date) //给定日期的年份
month(date) //给定日期的月份
date_add(date,interval expr unint) //为指定日期增加一个时间间隔，expr unint可以写 1day类似的
如，selectdate_add(‘2017-4-24 14:36:12’,interval 1 day); // 结果为2017-4-25 14:36:12
concat(字段A，字段B) //字符串拼接
select substr(字段A from 1 for1),字段A from 表； //从字段A的第一列开始每一列截取1个字符
length（字段A） //字符串长度
count() //统计个数
distinct //去重，放在select之后

select distinct MemberID from invest; //查询已经投资的用户的id
SELECT * FROM member ORDER BY id desc LIMIT 1;//查询id值最大的数据
SELECT * FROM member LIMIT 5 OFFSET 0;//分页显示

7、union：联合查询
多个逻辑冲突或很难在一个逻辑实现，可以拆分实现再合并展示。
当存在重复记录时，用union all
子语句的排序：
子语句包裹在括号内；与limit 搭配才会生效（unino内部优化导致）
对总结果统一排序：在语句最后进行排序
注意：
子语句的括号不是必须的；
字段类型和数据类型要一致；
检索结果的显示的列名称由第一个select语句来决定。

(select * from class12 where class_name = 302 order by days DESC)
union
(SELECT * FROM class12 WHERE class_name = 301 ORDER BY days DESC )

(SELECT * FROM class12 WHERE class_name = 302 ORDER BY days DESC LIMIT 12)
UNION all
(SELECT * FROM class12 WHERE class_name = 301 ORDER BY days DESC LIMIT 12)

(SELECT * FROM class12 WHERE class_name = 302 ORDER BY days DESC)
UNION
(SELECT * FROM class12 WHERE class_name = 301 ORDER BY days DESC )ORDER BY days DESC;
8、子查询
语句内部的查询语句
获得值后，用关系运算符进行判断。
【返回一列】
【in】
列子查询一定要用集合类（in/not in）来查询:子查询的返回结果是一列。
=any（集合）（相当于 in）等于集合中的任意一个
!=all（集合）（相当于not in）不等于集合中的任意一个。
any （集合）集合中的任意一个
some（集合）集合中的任意一些
!=any（）

SELECT * FROM class12;

SELECT * FROM class12 WHERE days = (SELECT MAX(days) FROM class12);

SELECT * FROM class12 WHERE teacher_name IN (SELECT teacher_name FROM class12 WHERE class_name = 302);

SELECT * FROM class12 WHERE teacher_name NOT IN (SELECT teacher_name FROM class12 WHERE class_name = 302);
9、子查询之返回一行
用括号（字段1，字段2.。。）表示一行

SELECT * FROM class12 WHERE teacher_name='李白’AND class_name = 301;

SELECT * FROM class12 WHERE (class_name,gender)=(SELECT class_name,gender FROM class12 WHERE teacher_name='李白’AND class_name = 301);

10、子查询之返回一张表
SELECT * FROM (SELECT class_name,gender ,days FROM class12 WHERE class_name = 301)AS tmep WHERE days = 12;

11、子查询之exisis
SELECT *FROM class12 WHERE EXISTS (SELECT * FROM class12 WHERE teacher_name='李白’AND class_name = 301);

12、连接查询
1）连接查询–无连接条件的内连接（无连接条件即为笛卡尔积）不建议采用：
交叉连接：cross join
内连接：inner join（inner可以省略）默认连接方案
2）有连接条件的内连接
3）连接查询–外连接
在join的左边为左表，右边为右表，
左外连接：左表与右表比对，存在则保留左表信息
右外连接：左表与右表比对，存在则保留右表信息
全外连接：将左外连接与 右外连接用unino联合
【连接条件】
on（通用条件时采用，连接时对数据过滤），
using（2个实体之间的字段名称一致，去重，显示字段在首列），
where（交叉完成之后，对数据过滤。不能在外连接使用）
3）连接查询–自然连接