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) > 30 select 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=“上海市”;
