##Mysql基本语法（二）
#Mysql基本语法（二） #一. 外键及外键的变种: (*********************************************************) #缘由：把所有数据都存放于一张表的弊端 1、表的组织结构复杂不清晰 2、浪费空间 3、扩展性极差 解决方法: 重新设计一张表, 这张表 中存放部门的相关信息 #1. 唯一索引（还有多个字段进行联合唯一）: create table t5( id int, num int, unique(num) )engine=Innodb charset=utf8; 作用: num列的值不能重复 加速查找 create table t6( id int, num int, unique(id, num) )engine=Innodb charset=utf8; 联合唯一索引作用: num列和id列的值不能重复 加速查找 create table t6( id int, num int, unique(id, num......) )engine=Innodb charset=utf8; #2. 一对多: #2-1 #部门表: create table department ( id int auto_increment primary key, depart_name varchar(32) not null default '' )engine=Innodb charset=utf8; insert into department (depart_name) values ('公关'), ('关关'),('关公'); #用户信息表： create table userinfo ( id int auto_increment primary key, name varchar(32) not null default '', depart_id int not null default 1, # constraint 外键名(fk_userinfo_depart) foreign key (列名(depart_id)) references 表名(department)(关联的列名(id)), constraint fk_userinfo_depart foreign key (depart_id) references department(id) )engine=Innodb charset=utf8; insert into userinfo (name, depart_id) values ('root1', 1); insert into userinfo (name, depart_id) values ('root2', 2); 错误的 注意: 创建多个外键的时候, 名称不能一样 #ps: 1. 不能将创建外键的语句单独拿出来 alter table userinfo add constraint fk_userinfo_depart foreign key (depart_id) references department(id); alter table userinfo drop foreign key 外键名称(fk_userinfo_depart ); 2. 外键关联的时候, 必须关联的是表的主键ID 3. 练习的时候, 将语句写在文本中, 然后考过去执行 4. 主键索引 : 加速查找 + 不能为空 + 不能重复 #2-2、寻找表与表之间的关系的套路 举例：emp表 dep表 步骤一： part1： 1、先站在左表emp的角度 2、去找左表emp的多条记录能否对应右表dep的一条记录 3、翻译2的意义： 左表emp的多条记录==》多个员工 右表dep的一条记录==》一个部门 最终翻译结果：多个员工是否可以属于一个部门？ 如果是则需要进行part2的流程 part2： 1、站在右表dep的角度 2、去找右表dep的多条记录能否对应左表emp的一条记录 3、翻译2的意义： 右表dep的多条记录==》多个部门 左表emp的一条记录==》一个员工 最终翻译结果：多个部门是否可以包含同一个员工 如果不可以，则可以确定emp与dep的关系只一个单向的多对一 如何实现？ 在emp表中新增一个dep_id字段，该字段指向dep表的id字段 # 2-3、foreign key会带来什么样的效果？ #1、约束1：在创建表时，先建被关联的表dep，才能建关联表emp create table dep( id int primary key auto_increment, dep_name char(10), dep_comment char(60) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female') not null default 'male', dep_id int, foreign key(dep_id) references dep(id) ); #2、约束2：在插入记录时，必须先插被关联的表dep，才能插关联表emp insert into dep(dep_name,dep_comment) values ('sb教学部','sb辅导学生学习，教授python课程'), ('外交部','老男孩上海校区驻张江形象大使'), ('nb技术部','nb技术能力有限部门'); insert into emp(name,gender,dep_id) values ('alex','male',1), ('egon','male',2), ('lxx','male',1), ('wxx','male',1), ('wenzhou','female',3); #3、约束3：更新与删除都需要考虑到关联与被关联的关系 解决方案： 1、先删除关联表emp，再删除被关联表dep，准备重建 mysql> drop table emp; Query OK, 0 rows affected (0.11 sec) mysql> drop table dep; Query OK, 0 rows affected (0.04 sec) #2-4、重建：新增功能，同步更新，同步删除 create table dep( id int primary key auto_increment, dep_name char(10), dep_comment char(60) ); create table emp( id int primary key auto_increment, name char(16), gender enum('male','female') not null default 'male', dep_id int, foreign key(dep_id) references dep(id) on update cascade on delete cascade ); insert into dep(dep_name,dep_comment) values ('sb教学部','sb辅导学生学习，教授python课程'), ('外交部','老男孩上海校区驻张江形象大使'), ('nb技术部','nb技术能力有限部门'); insert into emp(name,gender,dep_id) values ('alex','male',1), ('egon','male',2), ('lxx','male',1), ('wxx','male',1), ('wenzhou','female',3); #2-5、同步删除 mysql> select * from dep; +----+------------------+------------------------------------------------------------------------------------------+ | id | dep_name | dep_comment | +----+------------------+------------------------------------------------------------------------------------------+ | 1 | sb教学部 | sb辅导学生学习，教授python课程 | | 2 | 外交部 | 老男孩上海校区驻张江形象大使 | | 3 | nb技术部 | nb技术能力有限部门 | +----+------------------+------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> select * from emp; +----+------------------+--------+--------+ | id | name | gender | dep_id | +----+------------------+--------+--------+ | 1 | alex | male | 1 | | 2 | egon | male | 2 | | 3 | lxx | male | 1 | | 4 | wxx | male | 1 | | 5 | wenzhou | female | 3 | +----+------------------+--------+--------+ 5 rows in set (0.00 sec) mysql> delete from dep where id=1; Query OK, 1 row affected (0.02 sec) mysql> select * from dep; +----+------------------+------------------------------------------------------------------------------------------+ | id | dep_name | dep_comment | +----+------------------+------------------------------------------------------------------------------------------+ | 2 | 外交部 | 老男孩上海校区驻张江形象大使 | | 3 | nb技术部 | nb技术能力有限部门 | +----+------------------+------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select * from emp; +----+------------------+--------+--------+ | id | name | gender | dep_id | +----+------------------+--------+--------+ | 2 | egon | male | 2 | | 5 | wenzhou | female | 3 | +----+------------------+--------+--------+ 2 rows in set (0.00 sec) #2-6、同步更新 mysql> select * from emp; +----+------------------+--------+--------+ | id | name | gender | dep_id | +----+------------------+--------+--------+ | 2 | egon | male | 2 | | 5 | wenzhou | female | 3 | +----+------------------+--------+--------+ 2 rows in set (0.00 sec) mysql> update dep set id=200 where id =2; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from dep; +-----+------------------+------------------------------------------------------------------------------------------+ | id | dep_name | dep_comment | +-----+------------------+------------------------------------------------------------------------------------------+ | 3 | nb技术部 | nb技术能力有限部门 | | 200 | 外交部 | 老男孩上海校区驻张江形象大使 | +-----+------------------+------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select * from emp; +----+------------------+--------+--------+ | id | name | gender | dep_id | +----+------------------+--------+--------+ | 2 | egon | male | 200 | | 5 | wenzhou | female | 3 | +----+------------------+--------+--------+ 2 rows in set (0.00 sec) #3. 一对一: 左表的一条记录唯一对应右表的一条记录，反之也一样 用户表: id name age 1 zekai 23 2 eagon 34 3 lxxx 45 4 owen 83 博客表: id url user_id (外键 + 唯一约束unique) 1 /linhaifeng 2 2 /zekai 1 3 /lxxx 3 4 /lxxx 4 #示例
#一对一：
左表的一条记录唯一对应右表的一条记录，反之也一样
左表的一条记录唯一对应右表的一条记录，反之也一样
create table customer(
id int primary key auto_increment,
name char(20) not null,
qq char(10) not null,
phone char(16) not null
);
id int primary key auto_increment,
name char(20) not null,
qq char(10) not null,
phone char(16) not null
);
create table student(
id int primary key auto_increment,
class_name char(20) not null,
customer_id int unique, #该字段一定要是唯一的
foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
on delete cascade
on update cascade
);
id int primary key auto_increment,
class_name char(20) not null,
customer_id int unique, #该字段一定要是唯一的
foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
on delete cascade
on update cascade
);
insert into customer(name,qq,phone) values
('李飞机','31811231',13811341220),
('王大炮','123123123',15213146809),
('守榴弹','283818181',1867141331),
('吴坦克','283818181',1851143312),
('赢火箭','888818181',1861243314),
('战地雷','112312312',18811431230)
;
('李飞机','31811231',13811341220),
('王大炮','123123123',15213146809),
('守榴弹','283818181',1867141331),
('吴坦克','283818181',1851143312),
('赢火箭','888818181',1861243314),
('战地雷','112312312',18811431230)
;
#增加学生
insert into student(class_name,customer_id) values
('脱产3班',3),
('周末19期',4),
('周末19期',5)
;
#4. 多对多: 两张表之间是一个双向的多对一关系，称之为多对多 如何实现？ 建立第三张表，该表中有一个字段fk左表的id，还有一个字段是fk右表的id 用户表: id name phone 1 root1 1234 2 root2 1235 3 root3 1236 4 root4 1237 5 root5 1238 6 root6 1239 7 root7 1240 8 root8 1241 主机表: id hostname 1 c1.com 2 c2.com 3 c3.com 4 c4.com 5 c5.com 为了方便查询, 用户下面有多少台主机以及某一个主机上有多少个用户, 我们需要新建第三张表: user2host: id userid hostid 1 1 1 2 1 2 3 1 3 4 2 4 5 2 5 6 3 2 7 3 4 创建的时候, userid 和 hostid 必须是外键, 然后联合唯一索引 unique(userid, hostid) Django orm 也会设计
#示例：
create table author(
id int primary key auto_increment,
name char(16)
);
id int primary key auto_increment,
name char(16)
);
create table book(
id int primary key auto_increment,
bname char(16),
price int
);
id int primary key auto_increment,
bname char(16),
price int
);
insert into author(name) values
('egon'),
('alex'),
('wxx')
;
insert into book(bname,price) values
('python从入门到入土',200),
('葵花宝典切割到精通',800),
('九阴真经',500),
('九阳神功',100)
;
('egon'),
('alex'),
('wxx')
;
insert into book(bname,price) values
('python从入门到入土',200),
('葵花宝典切割到精通',800),
('九阴真经',500),
('九阳神功',100)
;
create table author2book(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
);
#二. 数据行的操作: 增: insert into 表名 (列名1, 列名2,) values(值1, 值2); insert into 表名 (列名1, 列名2,) values(值1, 值2),(值1,值2),(值n,值n);
#将别的表的数据插入到该表里面： insert into 表名 (列名1, 列名2,) select 列名1, 列名2 from 表名;
#只copy别的表的结构：后面条件为false，表示没有数据，但是表结构以copy到新表了
insert into 表名 (列名1, 列名2,) select 列名1, 列名2 from 表名 where 1=0; 删除: delete from 表名; delete from 表名 where id > 10 delete from 表名 where id < 10 delete from 表名 where id <= 10 delete from 表名 where id >= 10 delete from 表名 where id != 10 delete from 表名 where id = 10 and name='xxx'; and : 并且 两个条件都必须要成立 delete from 表名 where id = 10 or name='xxx'; or : 或者 只要满足一个条件成立 修改: update 表名 set name='zekai', age=23 where id > 10; 查询: 基本: select * from 表名; select name , age from 表名; 高级: a. where 条件查询: select * from 表名 where id=10; select * from 表名 where id >10 and id<15; select * from 表名 where id > 10; != : 不等与 >= <= between and: 闭区间 select * from t4 where id between 9 and 12; in: 在某一个集合中 select * from t4 where id in (9,10,11....); select * from t4 where id in (select id from t3 where id between 2 and 4) 是可以这样使用的, 但是不建议大家使用; b. 通配符: alex select * from 表 where name like 'ale%' - ale开头的所有（多个字符串） select * from 表 where name like 'ale_' - ale开头的所有（一个字符） c. 限制取几条: select * from 表名 limit 索引偏移量, 取出多少条数据; select * from t3 limit 0, 10; 第一页 select * from t3 limit 10, 10; 第二页 page = input('page:') page 索引偏移量 数据量(offset) 1 0 10 2 10 10 3 20 10 4 30 10 page (page-1)*offset offset 分页核心SQL: select * from t3 limit (page-1)*offset, offset; d. 排序: order by 降序: select * from t4 order by 列名 desc; descending 升序: select * from t4 order by 列名 asc; ascending 多列: create table t7( id int auto_increment primary key, num int not null default 0, age int not null default 0 )charset=utf8; insert into t7 (num, age) values (2, 12),(3,13),(4, 12); select * from t4 order by num desc, name asc; 如果前一列的值相等的话, 会按照后一列的值进行进一步的排序. e. 分组 select age, 聚合函数(count(num)/sum(num)/max(num)/min(num)/avg(num)) from 表名 group by 列名; select age, avg(num) from t7 group by age; select age, count(num) from t7 group by age; select age, count(num) as cnt from t7 group by age; 显示别名 as having的二次删选: select age, count(num) as cnt from t7 group by age having cnt>1; where 和 having的区别: 1). having与where类似,可筛选数据 2). where针对表中的列发挥作用,查询数据 3). having针对查询结果中的列发挥作用,二次筛选数据, 和group by配合使用 4). where不能用聚合函数，而having是可以用聚合函数，这也是他们俩最大的区别 #设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据 mysql> set global sql_mode="strict_trans_tables,only_full_group_by"; #每个部门的最高工资 select post,max(salary) from emp group by post; select post,min(salary) from emp group by post; select post,avg(salary) from emp group by post; select post,sum(salary) from emp group by post; select post,count(id) from emp group by post; #group_concat（分组之后用） select post,group_concat(name) from emp group by post; select post,group_concat(name,"_SB") from emp group by post; select post,group_concat(name,": ",salary) from emp group by post; select post,group_concat(salary) from emp group by post; # 补充concat（不分组时用） select name as 姓名,salary as 薪资 from emp; select concat("NAME: ",name) as 姓名,concat("SAL: ",salary) as 薪资 from emp;
select concat_ws(":",name,age,sex,post) as info from emp; # 补充as语法 mysql> select emp.id,emp.name from emp as t1; # 报错 mysql> select t1.id,t1.name from emp as t1; # 查询四则运算 select name,salary*12 as annual_salary from emp; f. 连表操作 select * from userinfo, department; (笛卡尔积) select * from userinfo, department where userinfo.depart_id=department.id; 1、1、内连接：把两张表有对应关系的记录连接成一张虚拟表 select * from emp inner join dep on emp.dep_id = dep.id; 2、左连接: select * from userinfo left join department on userinfo.depart_id=department.id; 左边的表全部显示, 右边没有用到不显示 3、右连接: select * from userinfo right join department on userinfo.depart_id=department.id; 右边的表全部显示, 左边没关联的用null表示 4、全连接：在内连接的基础上，保留左、右边没有对应关系的记录 select * from emp left join dep on emp.dep_id = dep.id union select * from emp right join dep on emp.dep_id = dep.id;
5、#补充：多表连接可以不断地与虚拟表连接
查找各部门最高工资
select t1.* from emp as t1
inner join
(select post,max(salary) as ms from emp group by post) as t2
on t1.post = t2.post
where t1.salary = t2.ms
;
inner join
(select post,max(salary) as ms from emp group by post) as t2
on t1.post = t2.post
where t1.salary = t2.ms
;
ps:
a.只需要记住左连接 left join
b.可以连接多张表 通过某一个特定的条件
6、自连接（自己连接自己，比较少见）
select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id;
e. distinct去重 select distinct post,avg(salary) from emp where age >= 30 group by post having avg(salary) > 10000; 注意查询的顺序: 语法： select distinct 查询字段1，查询字段2，。。。 from 表名 where 分组之前的过滤条件 group by 分组依据 having 分组之后的过滤条件 order by 排序字段 limit 显示的条数; #用函数模拟语法的执行顺序 def from(dir,file): open('%s\%s' %(dir,file),'r') return f def where(f,pattern): for line in f: if pattern： yield line def group(): pass def having(): pass def distinct(): pass def order(): pass def limit(): pass def select(): res1=from() res2=where(res1,pattern) res3=group(res2,) res4=having(res3) res5=distinct(res4) res6=order(res5) limit(res6) select name,sum(score) from 表 where id > 10 group by score having age> 12 order by age desc limit 2, 10