使用where子句对表中的数据筛选，结果为true的行会出现在结果集中
select * from 表名 where 条件;
select * from students where id=1；
select * from students where id > 3;
select * from students where id <= 4;
select * from students where name != '小明';
select * from students where is_delete=0;
select * from students where id >3 and gender=0;
select * from students where id <4 or is_delete=0;
select * from students where not id = 15;
%表示任意多个任意字符_表示一个任意字符select * from students where name like '黄%';
select * from students where name like '黄_';
select * from students where name like '黄%' or name like '%靖';
in表示在一个非连续的范围内between ... and ...表示在一个连续的范围内select * from studnets where id in(1,3,8);
select * from students where id between 3 and 8;
select * from students where (id between 3 and 8) and gender=1;
is null 判空is not null 判非空null与''是不同的select * from students where height is null;
select * from students where height is not null;
select * from students where height is not null and gender=1;
优先级由高到低的顺序为： 小括号--not--比较运算符--逻辑运算符
and 比 or 先运算，如果同时出现并希望先算or，需要结合()使用
语法:
select * from 表名 order by 列1 asc|desc [, 列2 asc|desc, ...]
说明:
将每行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
默认按照列值从小到大排序(asc)
asc从小到大排序，即升序
desc从大到小排序，即降序
select * from students where gender=1 and is_delete=0 order by id desc;
select * from students where is_delete=0 order by name;
select * from students order by age desc, height desc;
*与列名，结果是相同的select count(*) from studnets;
select max(id) from students where gender=2;
select min(id) from students where is_delete=0;
select sum(age) from students where gender=1;
select sum(age)/count(*) from students where gender=1; # 平均年龄
select avg(height) from students where is_delete=0 and gender=2;
group by的含义：将查询结果按照1个或多个字段进行分组，字段值相同的为一组
group by可用于单个字段分组，也可用于多个字段分组
select gender from students group by gender;
group_concat(字段名)可以作为一个输出字段来使用，
表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合
select gender, group_concat(name) from students group by gender;
select gender, group_concat(id) from students group by gender;
select gender, avg(age) from students group by gender;
select gender, count(*) from students group by gender;
having 条件表达式: 用来分组查询后指定一些条件来输出查询结果
having作用和where一样，但having只能用于group by
select gender, count(*) from students group by gender having count(*)>2;
select gender,count(*) from students group by gender with rollup;
select gender, group_concat(age) from students group by gender with rollup;
当数据量过大时，在一页中查看数据是一件非常麻烦的事情
语法:
select * from 表名 limit start,count;
select * from students where gender=1 limit 0,3;
已知：每页显示m条数据，当前显示第n页
求总页数：
查询总条数p1
使用p1除以m得到p2
如果整除则p2为总页数
如果不整除则p2+1为总页数
求第n页的数据
select * from students where is_delete=0 limit (n-1)*m,m;
当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回
MySQL支持三种类型的连接查询
内连接查询： 查询的结果为两个表匹配到的数据
右连接查询： 查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充
左连接查询： 查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充
语法：
select * from 表1 [inner | left | right] join 表2 on 表1.列 = 表2.列
# 使用内连接查询班级表与学生表
select * from students inner join classes on students.class_id = classes.id;
# 使用左连接查询班级表与学生表
select * from students as s left join classes as c on s.class_id = c.id;
# 使用右连接查询班级表与学生表
select * from students as s right join classes as c on s.class_id = c.id;
# 查询学生姓名及班级名次
select s.name,c.name from students as s inner join classes as c on s.class_id = c.id;
表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的。这就是自关联
create table areas(
aid int primary key,
atitle varchar(20),
pid int
);
select count(*) from areas where pid is null;
select city.* from areas as city
inner join areas as province on city.pid=province.aid
where provice.atitle='河南省';
select dis.* from areas as dis
inner join areas as city on city.aid=dis.pid
where city.atitle='广州市';
在一个select语句中，嵌入了另外一个select语句，那么被嵌入的select语句称之为
子查询语句
主要查询的对象，第一条select语句被称之为
主查询语句
子查询是嵌入到主查询中
子查询是辅助主查询的，要么充当条件，要么充当数据源
子查询是可以独立存在的语句，是一条完整的select语句
标量子查询：子查询返回的结果是一个数据(一行一列)
列子查询：返回的结果是一列(一列多行)
行子查询： 返回的结果是一行(一行多列)
select * from students where age > (select avg(age) from students);
select name from classes where id in (select cls_id from students);
select * from students where (height,age) = (select max(height), max(age) from students);
主查询 where 条件 in (列子查询)
SELECT select_expr [,select_expr,...] [ FROM tb_name [WHERE 条件判断] [GROUP BY {col_name | postion} [ASC | DESC], ...] [HAVING WHERE 条件判断] [ORDER BY {col_name|expr|postion} [ASC | DESC], ...] [ LIMIT {[offset,]rowcount | row_count OFFSET offset}] ]
select distinct *
from 表名
where ...
group by ... having...
order by ...
limit start, count;
执行顺序为：
在创建数据表的时候设置外键约束
create table goods(
id int unsigned primary key auto_increment not null,
name varchar(40) default '',
price decimal(5,2),
cate_id int unsigned,
brand_id int unsigned,
is_show bit default 1,
is_saleoff bit default 0,
foreign key(cate_id) references goods_cates(id),
foreign key(brand_id) references goods_brands(id)
);
对于已经存在的数据表更新外键约束
alter table 表名 add foreign key (外键字段名) references 表名(字段名)
alter table goods add foreign key (brand_id) references goods_brands(id);
alter table goods add foreign key (cate_id) references goods_cates(id);
取消外键约束
alter table 表名 drop foreign key 外键名称;
基于Nginx+Supervisord+uWSGI+Django1.11.1+Python3.6.5构建
