1.约束分为列级约束和表级约束

	-- 列级约束:列级约束是行定义的一部分，只能应用于一列。-- 表级约束:表级约束是独立于列的定义，可以应用在一个表中的多列。-- 主键(primary key) 唯一(unique key) 外键(foreign key)-- 举例：unique（项目名称，项目负责人）

2.外键约束的要求：

-- 1.父表和子表必须使用相同的存储引擎，而且禁止使用临时表。-- 查看表的存储引擎，show create table classes;-- 2.数据表的存储引擎必须为InnoDB。-- 只有InnoDB支持外键功能-- 3.外键列和参数列必须具有相似的数据类型。-- 其中数字的长度或是否有符合位必须相同；而字符的长度则可以不同。-- 4.外键列和参照列必须创建索引。-- 如果外键列不存在索引的话，mysql将自动创建索引。--（'索引'是什么? 先别管）-- 5.在实际开发中,很少会使用到外键约束,会极大的降低表更新的效率。

foreign key的基本语法规则。
foreign key (index_col_name, …)
references tbl_name (index_col_name, …)

 案例：# 创建表教师表create table classes(id int unsigned auto_increment primary key not null,name varchar(50));insert classes values (0, "python_1611班"), (0, "python_1612班");# 创建学生表create table students(student_id int auto_increment primary key not null,student_name varchar(255),class_id int unsigned,foreign key(class_id) references classes(id));1.id=1,2在classes中有对应班级，可插入：insert students values(0,'小明',2),(0,'彭于晏',1);2.id=7,5在classes中没有，插入报错：insert students values(0,'小明',7),(0,'彭于晏',5);3.取消外键：-- 先查看外键名称：-- show create table students;alter table students drop foreign key students_ibfk_1;

3.存储引擎特点和对比：

-- 查看引擎：show engines；InnoDB 优点:支持事务，外键; 高性能(CPU效率高)缺点: 慢，占空间(行级别锁）--锁住一条记录，别人可以操作其他行MyISAM优点:快速读取数据, 占用空间小缺点：不支持事务，外键（表级别锁) --锁住表，其他进程只能读，不能写

4.mysql数据库的查询：

准备数据:

	--	创建数据库create database python03 charset=utf8;--	使用数据库use	 python03;--	classes表create table classes(id int unsigned auto_increment primary key not null,name varchar(30) not null);--	向classes表中插入数据insert classes values (0, "python_1607班"), (0, "python_1608班");--	students表create table  students (id int unsigned auto_increment primary key not null,name varchar(20) default '',age tinyint unsigned default 0,height decimal(5,2),gender enum('男','女','中性','保密') default '保密',cls_id int unsigned default 0,is_delete bit default 0);		--	向students表中插入数据insert students values(0,'小明',18,180.00,2,1,0),(0,'小月月',18,180.00,2,2,1),(0,'彭于晏',29,185.00,1,1,0),(0,'刘德华',59,175.00,1,2,1),(0,'黄蓉',38,160.00,2,1,0),(0,'凤姐',28,150.00,4,2,1),(0,'王祖贤',18,172.00,2,1,1),(0,'周杰伦',36,NULL,1,1,0),(0,'程坤',27,181.00,1,2,0),(0,'刘亦菲',25,166.00,2,2,0),(0,'金星',33,162.00,3,3,1),(0,'静香',12,180.00,2,4,0),(0,'郭靖',12,170.00,1,4,0),(0,'周杰',34,176.00,2,5,0);

查询:

	-- 可以通过 as 给表和字段起别名字段起别名：-- select 字段 as 别名 ...from 表名;select name as '姓名', gender as '性别' from students;表起别名：select name, gender from students;select students.name, students.gender from students;select s.name, s.gender from students as s;-- 失败 select students.name, students.gender from students as s;-- 消除重复行-- distinct 字段select gender from students;select distinct gender from students;

条件查询(where):
优先级
优先级由高到低的顺序为:小括号,not,比较运算符,逻辑运算符
and比or先运算,如果同时出现并希望先算or,需要结合()使用

	1.比较运算符-- >-- <-- >=-- <=-- =-- != 或者 <>-- 查询小于或者等于18岁的信息select * from students where age<=18;2.逻辑运算符-- and-- 18岁以上的女性select * from students where age>18 and gender=2;-- or-- 18岁以上或者身高超过180(包含)以上select * from students where age>18 or height>=180;-- not-- 不在 18岁以上的女性 这个范围内的信息select * from students where not (age>18 and gender=2);3.模糊查询-- like-- % 表示任意多个任意字符-- 查询姓名中 以 "小" 开始的名字select name from students where name like "小%";-- _ 表示一个任意字符-- 查询有3个字的名字-- select name from students where name like "___"; (5.5可能不支持)-- rlike 正则-- 查询以 周开始以伦结尾的姓名select name from students where name rlike "^周.*伦$";4.范围查询-- in表示在一个非连续的范围内-- 查询 年龄为18、34的姓名select name from students where age in (18, 34);-- not in 不非连续的范围之内-- 年龄不是 18、34岁之间的信息select name, age from students where age not in (18, 34);-- between ... and ...表示在一个连续的范围内-- 查询 年龄在18到34之间的的信息select * from students where age between 18 and 34;-- not between ... and ...表示不在一个连续的范围内-- 查询 年龄不在在18到34之间的的信息select * from students where age not between 18 and 34;-- 失败select * from students where age not (between 18 and 34);5.空判断-- 判空is null-- 查询身高为空的信息-- 	select * from students where height is null;-- 判非空is not null-- 	select * from students where height is not null;

排序:

	-- order by 字段-- asc从小到大排列，即升序，默认为升序-- desc从大到小排序，即降序查询年龄在18到34岁之间的男性，按照年龄从小到到排序select * from students where (age between 18 and 34) and gender=1 order by age;select * from students where (age between 18 and 34) and gender=1 order by age desc;-- order by 多个字段按照年龄从小到大、身高从高到矮的排序select * from students order by age asc, height desc;查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序select * from students where (age between 18 and 34) and gender=2 order by height desc, age asc;

聚合函数:

	-- 总数1.count-- 查询男性有多少人select count(*) as '男生的人数' from students where gender=1;select count(name) as '男生的人数' from students where gender=1;2.最大值 max最小值 min-- 查询最大的年龄select max(age) from students;-- 查询女性的最高 身高select max(height) from students where gender=2;-- 求和3.sum-- 计算所有人的年龄总和select sum(age) from students;-- 平均值4.avg-- 计算平均年龄select avg(age) from students;-- 计算平均年龄 sum(age)/count(*)select sum(age) / count(*) from students;5.四舍五入 round(123.23 , 1) 保留1位小数-- 计算男性的平均身高 保留2位小数select round(avg(height), 2) from students where gender=1;

分组:

	1.group by  只显示各组第一条记录-- 按照性别分组select gender from students group by gender;select distinct gender from students;2.group by + group_concat(...)-- 查询同种性别中的姓名select gender, group_concat(name) from students group by gender;select gender, group_concat(name, age) from students group by gender;3.group by + 聚合函数-- 查询同种性别中的人数count(*)select gender, count(*) from students group by gender;-- 查询每种性别中的平均年龄avg(age)select gender, avg(age) from students group by gender;4.group by + having-- having 条件表达式：用来分组查询后指定一些条件来输出查询结果-- having类似于where（唯一的差别是where过滤行，having过滤组）-- 查询平均年龄超过30岁的性别，以及姓名 having avg(age) > 30select gender, group_concat(name) from students group by gender having avg(age)>30;5.group by + with rollup-- with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和select gender,count(*) from students group by gender with rollup;

分页:

	-- limit start, count-- 每页显示2个，第1个页面select * from students limit 0,2;----->每页的数量*(页数-1), 每页的数量-- 每页显示2个，显示第4页的信息, 按照年龄从小到大排序select * from students limit 6,2;	---2*(4-1)=6-- 失败select * from students limit 6,2 order by age asc;select * from students order by age asc limit 6,2;	---2*(4-1)=6

链接查询:

	1.inner join ... on-- 查询 有能够对应班级的学生以及班级信息select * from students inner join classes on students.cls_id=classes.id;-- 给数据表起名字select s.name from students as s inner join classes as c on s.cls_id=c.id;	select s.*, c.name from students as s inner join classes as c on s.cls_id=c.id;-- 将班级姓名显示在第1列select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id;-- 查询 有能够对应班级的学生以及班级信息, 按照班级进行排序:-- select c.xxx s.xxx from student as s inner join clssses as c on .... order by ....;select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name;2.left join ... on-- 查询每位学生对应的班级信息select s.*, c.name from students as s left join classes as c on s.cls_id=c.id;-- 查询没有对应班级信息的学生-- select ... from xxx as s left join xxx as c on..... where .....-- select ... from xxx as s left join xxx as c on..... having .....select s.*, c.name from students as s left join classes as c on s.cls_id=c.id where c.name is null;select s.*, c.name from students as s left join classes as c on s.cls_id=c.id having c.name is null;3.right join ... on-- 将数据表名字互换位置，用left join完成

子查询:

	-- 子查询是辅助主查询的,要么充当条件,要么充当数据源1.子查询分类:-- 标量子查询:	子查询返回的结果是一个数据(一行一列)-- 列子查询:	子查询返回返回的结果是一列(一列多行)-- 行子查询:	子查询返回返回的结果是一行(一行多列)2.标量子查询:-- 查询出高于平均身高的信息-- 失败select * from students height > avg(height);-- 失败select * from students where height > select avg(height) from students;select * from students where height > (select avg(height) from students);3.列子查询:-- 查询学生的班级号能够对应的学生信息select * from students where cls_id in (1, 2);select * from students where cls_id in (select id from classes);4.行级子查询:-- 需求:	查找班级年龄最大,身高最高的学生select * from students where (height,age) = (select max(height),max(age) from students);

完整的select语句:
select distinct *
from …(表名)…
where …(条件)…
group by …(列名)… having …(条件)…
order by …(列名)…
limit …(开始行,行数)…

	-- 可应用限定条件进行分组，以便系统仅对满足条件的组返回结果。-- having类似于where（唯一的差别是where过滤行，having过滤组）

5.创建表格并同时写入数据:

-- create table tbl_name (create_definition,...) select 语句；create table genders (
id int unsigned primary key auto_increment,
gender enum('男','女','中性','保密') default '保密') 
select distinct gender from students;

6.将查询结果写入到数据表：

 -- create table studentsB (--        id int unsigned auto_increment primary key not null,--        name varchar(20) default '',--        age tinyint unsigned default 0,--        height decimal(5,2),--        gender enum('男','女','中性','保密') default '保密',--        cls_id int unsigned default 0,--        is_delete bit default 0--    );	insert studentsB select * from students;

自关联:
– --创建areas表的语句如下：
– create table areas(
– aid int primary key,
– atitle varchar(20),
– pid int);
– --从sql文件中导入数据
– source areas.sql;
– – 查询所有省份
– select * from areas where pid is null;
– – 查询出山东省有哪些市（mysql5.5可能不支持自关联查询）
– select * from areas as city inner join areas as province on city.pid=province.aid where province.atitle=“江苏省”;
– – 查询出青岛市有哪些县城（mysql5.5可能不支持自关联查询）
– select * from areas as city inner join areas as province on city.pid=province.aid where province.atitle=“上海市”;