mysql支持三种类型的连接查询，分别为：
内连接，左连接，右连接
内连接： where / inner join / join
(1)select s.name,c.name from mingxingstudents as s ,classes as c where s.cls_id=c.id;
(2)select s.name,c.name from mingxingstudents as s inner join classes as c on s.cls_id=c.id;
(3)select s.name,c.name from mingxingstudents as s join classes as c on s.cls_id = c.id;
(4)select s.name,c.name from mingxingstudents as s cross join classes as c on s.cls_id = c.id;
左右连接查询
左连接查询（左边的表是主表，全部显示，右边的是从表）
select s.name,c.name from mingxingstudents as s left outer join classes as c on s.cls_id = c.id;
(上面这个查询语句中的outer可以省略select s.name,c.name from mingxingstudents as s left join classes as c on s.cls_id = c.id;
)
右连接查询（刚好与左连接相反）
select s.name,c.name from mingxingstudents as s right outer join classes as c on s.cls_id = c.id;
交叉查询（容易产生笛卡儿积现象)
select * from mingxingstudents cross join classes;
自关联查询的基本操作
（自连接，自身关联自身，自连接必然用到子查询）
select p.name as省,c.name as 市 from province as p inner join province as c on c.pid = p.id where p.name ="河北省"
子查询
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句
主查询:主要查询的对象,第一条 select 语句
主查询和子查询的关系:子查询是嵌入到主查询中;子查询是辅助主查询的,要么充当条件,要么充当数据源;子查询是可以独立存在的语句,是一条完整的 select 语句
子查询分类:
(1)标量子查询: 子查询返回的结果是一个数据(一行一列) select avg(height) from mingxingstudents;
(2)列级子查询: 返回的结果是一列(一列多行)select classes.name from classes inner join mingxingstudents on classes.id = mingxingstudents.cls_id;
(3)行级子查询: 返回的结果是一行(一行多列)select max(age),max(height) from mingxingstudents;
查询班级中年龄最小并且身高也最小的人：
select * from mingxingstudents where (age,height) = (select min(age),min(height)from mingxingstudents);
(4)表级子查询: 返回的结果是多行多列(一般不用，查询效率相对费时间。可以使用连接查询实现的)
select t.sname,t.cname from (select s.name as sname,c.name as cname from mingxingstudents as s inner join classes as c on s.cls_id = c.id) as t;
子查询中出现的关键字
any与some,any与in等价
select classes.name from classes where id = any(select cls_id from mingxingstudents);
select classes.name from classes where id in(select cls_id from mingxingstudents);
where 列 = all(列子查询)：等于里面所有
where 列 <> all(列子查询)：不等于其中所有
select classes.name from classes where id = all(select cls_id from mingxingstudents);
select classes.name from classes where id <> all(select cls_id from mingxingstudents);
in范围
select classes.name from classes where id not in(select cls_id from mingxingstudents);
select * from goods where price >(select avg(price) from goods) order by price desc;
select price from goods where cate = '超级本' order by price;
select * from goods where price >= any(select price from goods where cate ='超级本') order by price desc;
将一个表进行分表处理
判断不存在创建一个空表create table if not exists goods_cates(id int unsigned primary key auto_increment,cate_name varchar(40));
将查询到的数据插入到表中insert into goods_cates(cate_name) select cate from goods group by cate;
将原表中的字段设置成对应的新表里的id
update goods as g inner join goods_cates as c on g.cate = c.cate_name set g.cate = c.id;
再创建一个新表并插入数据
create table goods_brands(id int unsigned primary key auto_increment not null,brand_name varchar(40))select brand_name from goods group by brand_name;
再次更新原表（将brand_name = 设置为brand_id）
update goods as g inner join goods_brands as b on g.brand_name = b.brand_name set g.brand_name = b.id;
修改表结构:(修改了两个部分，cate的类型被修改成int，brand_name 修改成了brand_id，类型为int)
alter table goods change cate cate_id int unsigned,change brand_name brand_id int unsigned;
多张表的连接查询
(两个左连接的方式)select * from goods as g left join goods_cates as c on g.cate_id = c.id left join goods_brands as b on g.brand_id = b.id;
(两个右连接的方式)select * from goods as g right join goods_cates as c on g.cate_id =c.id right join goods_brands as b on g.brand_id = b.id;
(两个内连接)select * from goods as g inner join goods_cates as c on g.cate_id =c.id inner join goods_brands as b on g.brand_id = b.id;
外键约束的创建和删除
alter table goods add foreign key (brand_id) references goods_brands(id);
alter table goods add foreign key (cate_id) references goods_brands(id);
删除外键：alter table goods drop foreign key goods_ibfk_1;
删除key：alter table goods drop key catte_id;
备份和还原
备份一个表：(注意：退出mysql后使用)
mysqldump -uroot -p python students > D:\ps.sql
备份一个数据库：
mysqldump -uroot -p python > D:\ss.sql
备份多个数据库
视图
视图的创建：create view v_mstu as select * from mingxingstudents;
视图的查询：select * from v_mstu
删除视图：drop view v_mstu
索引
是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表所有记录的引用指针。
查看索引：show index from 表名；
创建索引：create index 索引名 on 表名
删除索引：drop index 索引名 on 表名
开启运行时间：set profiling=1
显示查看所需时间： show profiling；
当数据表中创建主键和外键的时候，就会自动的创建索引。
建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。建立索引会占用磁盘空间。
数据库的交互
connect对象：host,port,database,user,password,charset
对象的方法：
close
commit，
rollback，
cursor（）返回cursor对象，执行sql语句并获得结果
execute()执行语句
fetchone（）
fetchall（）
调用connection对象的cursor（）方法：cursor=conn.cursor()
对象的属性：
rowcount只读属性，表示最近一次execute（）执行后受影响的行数
connection获得当前连接对象
