#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
#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 也会设计
#二. 数据行的操作:
增:
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 表名;
删除:
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;
# 补充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;
ps:
a.只需要记住左连接 left join
b.可以连接多张表 通过某一个特定的条件
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
浙公网安备 33010602011771号