创建测试表

CREATE TABLE bailan_student (
 id int, -- 编号
 name varchar(20), -- 姓名
 age int, -- 年龄
 sex varchar(5), -- 性别
 address varchar(100), -- 地址
 math int, -- 数学
 english int -- 英语
);

INSERT INTO bailan_student(id,NAME,age,sex,address,math,english) VALUES
 (1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);

CREATE TABLE `bailan_things` (
  `id` int NOT NULL COMMENT '主键',
  `bs_id` int NOT NULL COMMENT '外键',
  `things` varchar(50) DEFAULT NULL COMMENT '拥有的东西',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO bailan_things VALUES (1, 3, '铅笔'),(2, 1, '铅笔'), (3, 1, '钱包'), (4, 2, '钱包'), 
(5, 3, '手表'),(6, 3, '钱包'), (7, 4, '钱包'), (8, 4, '电脑');

CREATE TABLE `bailan_student2` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL，
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO bailan_student2(id,NAME) VALUES
 (1,'马云'),(2,'马化腾');

1、order by

asc升序（默认）
desc降序

select * from bailan_student order by age desc;
select * from bailan_student order by math desc,english asc;

2、limit

limit y 分句表示: 读取 y 条数据
limit x, y 分句表示: 跳过 x 条数据，读取 y 条数据
limit y offset x 分句表示: 跳过 x 条数据，读取 y 条数据
注：开始索引=（当前页码 - 1）* 读取条数

检索5条记录(1-5)
select * from bailan_student limit 5; 
select * from bailan_student limit 0,5;     
select * from bailan_student limit 5 offset 0;
检索记录4条记录（4-7）
select * from bailan_student limit 3,4; 
select * from bailan_student limit 4 offset 3; 

3、聚合函数

avg(字段名) : 求字段的平均值
sum(字段名) : 求字段的和
max(字段名) : 求字段的最大值
min(字段名) : 求字段的最小值
count(字段名) : 统计该字段的记录的个数

select avg(math) from bailan_student;
select sum(english) from bailan_student;
select max(age) from bailan_student;
select min(age) from bailan_student;
select count(id) from bailan_student;

4、distinct

作用：过滤字段的重复值

select distinct address from bailan_student;

5、group by

通常配合聚合函数使用

求各地区的的数学平均分，及人数
select address ,avg(math) ,count(id) from bailan_student group by address;

6、between

取介于两个值之间的数据范围。数值、文本或者日期。

取20岁到50岁之间的学生
select name from bailan_student  where age  BETWEEN 20 and 50  ;

7、where/having

这两个放在一起讲
where在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来。
where后不可以跟聚合面数，having可以进行聚合面数的判断。

求各地区的的数学平均分，及人数
select address ,avg(math) ,count(id) from bailan_student group by address;
求各地区的的数学平均分，但人数要大于等于2的
select address ,avg(math) ,count(id) from bailan_student  where age >17 group by address having count(id)>=2;

8、Alias

简写 as 更简写可以不写
给列名表名一个别名

求各地区的英语总分别名total
select address ,sum(english) as total from bailan_student group by address;

9、like

多用于模糊查询
_等于一个
%等于多个

把姓马的查出来
select name from bailan_student WHERE   name like   '马%';
把姓马的，并且是是两个字的查出来
select name from bailan_student WHERE   name like   '马_';

10. inner join

内连接 两个表的交集 inner可省略

隐式写法（常见子查询做为虚拟表）
select * from bailan_student as bs,bailan_things  as bh  WHERE bs.id =bh.bs_id;
显式写法
select * from bailan_student as bs inner join bailan_things  as bh  on bs.id =bh.bs_id;

11.left join

左连接 left join 是left outer join的简写
(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

select * from bailan_student as bs left join bailan_things as bh on bs.id = bh.bs_id;

12、right join

右连接 right join是right outer join的简写
与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

select bs.id as bsid,bh.*  from bailan_student as bs  right join bailan_things as bh on bs.id = bh.bs_id;

13、union

[all | distinct] 这两个是可选的

 单union并不会吧重复的值查询出来，也就是加不加distinct不影响结果
 select name from bailan_student  where address="深圳"
 union 
 select name from  bailan_student2
 加上all会拿到全部值
 select name from bailan_student where address="深圳"
 union all
 select name from  bailan_student2

14、null值

MySQL中判断null 是不能用！=这样子判断的，无效
需要使用is null is not null

查询英语成绩为null的
select name  from  bailan_student where english is null;
查询英语成绩不为null的
select name  from  bailan_student where english is not null;

15、in /not in

相当于多个or

两条语句的结果是相反的
select name from bailan_student where id in(2,3,4)
select name from bailan_student where id not in(2,3,4)
in的子查询返回必须只有一个字段
select name from bailan_student where id in(select bs_id from bailan_things);

16、exists

exists不关心子查询的返回值内容是什么，只关系存在不存在返回值，既对应返回真假
注：通常查询表大的用exists，子查询表小的用in

此条效率效果要比上述的in高，子查询还走了索引。
select name from bailan_student bs where exists(select name from bailan_things bh where bh.bs_id =bs.id)