MySQL3_外键及查询

MySQL_外键及查询

1.数据的完整性

1.实体的完整性,一条记录,就是一个实体,如果记录无法区分,则失去了实体的完整性
2.域完整性:如果有两个字段无法区分,则失去了域完整性
3.引用的完整性:两个表的对应记录不完整,则失去了引用完整性
4.自定义完整性:自己定义的一套规则

(1).保证实体的完整

1.主键的约束(primary key)
2.自动增长的列(auto_increment)
3.唯一键(unique)

(2).保证域的完整性

1.数据类型的约束
2.默认值(default)
3.非空约束(not null)

(3).引用的完整性

应用外键(foreign key)

(4).自定义完整性

1.存储过程(相当于python中的自定义函数)
2.触发器

2.外键

外键:从表的公共字段

外键的约束主要是用来保证引用的完整性的,主外键的名字可以不一样,但是数据类型可以一样.

#特点
1.主表中不存在的记录,从表中不能插入
2.从表已存在的记录,主表中不能删除
3.先删除从表,再删除主表

#学生表
create table stuinfo(
	id int primary key auto_increment,
	name char(32) not null
);

insert into stuinfo set name='孙正';
insert into stuinfo(name)  values('周鹏'),('李');
insert into stuinfo set name='李嘉';

#成绩表
	#foreign key(本表的外键) references 主表(关联字段)
	#foreign key(stuno) references stuinfo(id)
	#cascade 联动操作
create table score(
	nid int primary key auto_increment,
	stuno int ,
	ch float,
	math float
	#foreign key(stuno) references stuinfo(id) on delete cascade on update cascade
);

insert into score values(null,1,100,100),(null,2,100,100),(null,3,100,100),(null,4,100,100);


#两种串联的操作:
1.set null: 让一个字段设置为NUll
2.cascade : 跟着主表的变化而变化


#添加外键
alter table score add foreign key(stuno) references stuinfo(id) on delete cascade on update cascade;
#添加外键,并指定外键的名称
alter table score add CONSTRAINT `stuno` FOREIGN KEY (`stuno`) REFERENCES `stuinfo` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

#删除外键
#score_ibfk_1 外键的名字,外键可以有多个
alter table score drop foreign key score_ibfk_1;

#外键只能在innodb的引擎上使用

3.实体之间的关系

实体的关系:

1.一对一
2.一对多
3.多对一
4.多对多

(1).一对一:主键关系

stuinfo

stuno(学号)	name(姓名)
1	王健林
2	许家印

stuno(期末考试)	score
1	100
2	120

(2)一对多|多对一

user

uid	account	pwd	email
1	admin	123456	123@qq.com
2	root	123456	126@126.com

order(订单)

oid(订单的编号)	uid	gid	create_at
201904037000001	1	7000	2019/04/03
201904047000001	1	7000	2019/04/04

(3)多对多

user

uid	account	pwd	email
1	admin	123456	123@qq.com
2	root	123456	126@126.com

address

address	uid	mobile	name
上海徐汇区1208弄A小区3栋1608	1	13555555555	马云
上海徐汇区1208弄A小区3栋1608	1	13666666666	雷军
上海徐汇区1208弄B小区3栋1608	1	13777777777	吴军
上海徐汇区1208弄B小区3栋1608	1	13888888888	李斌
上海徐汇区1208弄A小区3栋1608	2	13555555555	马云
上海徐汇区1208弄A小区3栋1608	2	13666666666	雷军

4.数据库的设计

公司要做一个项目,首先项目管理获得需求,知道项目是什么类型的,然后产品经理负责产品的规划,设计原型

UI将需求的草图给UI,UI可以绘制E-R图,或者是DB自己构建E-R图

DB自己根据E-R图设计数据库,建立表,设定关联度.

码农看到E-R图可以干嘛,我们根据E-R图上的需求写代码

• E-R图

E-R图是描述实体和实体之间的关系的

语法:
1.矩形代表实体
2.椭圆形代表实体拥有的属性
3.菱形代表实体之间的关系

博客的E-R图:

#用户和板块之间的关系
1.某个用户是版主,版主管理板块
2.普通用户和版块之间没有直接的关系,用户发帖或者用户评论间接的和版块之间形成关系

#用户和帖子之间的关系
1.用户发表了帖子
2.用户评论了某个帖子

#用户和评论之间的关系
1.用户发表了评论
2.用户发表了帖子,被其他人评论了
3.如果有二级评论,你的评论被人喷了

#帖子和版块之间的关系
帖子属于版块

5.数据的规范

(1)第一范式

第一范式:确保每一列原子化(不可分割)

(2)第二范式

第二范式:,基于第一范式,一张表只能描述一件事情,非主键字段必须依赖主键字段(不论在什么情况下主键都是唯一的)

(3)第三范式

第三范式:基于第二范式,消除传递依赖(一个主键字段可以确定其它的信息)

6.规范化和性能

高考成绩查询系统:高并发

不符合三范式

stuno(考号)	姓名	语文	数学	总分
1	小明	130	120	250

select * from gaokao where stuno=1;

规范化:

stuno(考号)	姓名
1	小明
2	小强

stuno(考号)	语文	数学
1	130	120

select *,ch+math as score from A left join B on A.stuno = B.stuno where A.stuno = 1;

总结:性能的完备良好的时候,选择规范化;性能不足,优先考虑性能

7.查询语句

所有的查询都依赖统计分析:

语句位置顺序

select 字段(结果集) from 表名(数据源) 
[where 条件] 
[group by 分组]
[having 条件]
[order by 排序 asc|desc]
[limit 限制 m,n];

create table stuinfo(
	sid int primary key auto_increment comment'学号(主键)',
    sname varchar(32) not null comment'姓名',
    sex enum('男','女','不详') default '不详',
    age tinyint unsigned not null comment'年龄',
    city varchar(64) comment'地级市'
);

create table score(
	stuno int not null comment'学号',
    python float,
    java float
);

insert into stuinfo values(null,'挺正',1,18,'重庆'),(null,'李野',1,60,'北京'),(null,'劲宇',1,81,'深圳'),(null,'杨幂',1,18,'重庆'),(null,'赵薇',1,20,'北京'),(null,'迪丽热巴',1,18,'深圳');

insert into score values(1,88,99),(2,78,100),(3,30,60),(4,100,99),(5,70,69),(6,100,0);

(1)字段表达式

select 既可以做查询,也可以做输出

select now();  #显示当前时间
select rand();   #随机数
select unix_timestamp(); # 显示Unix时间戳

(2)from子句

from 后面是数据源
数据源可以写多个,返回的是一个笛卡尔积
select * from A,B,C;

(3)dual(肚哦)表

dual是一个语法,是一个关键字

dual表示为了保证select完整性的

select now() from dual;

(4)where子句

where是做条件查询,只返回结果为True的数据

select * from stuinfo where age <50;

• is null | is not null

where条件使用的比较运算符

select * from score where java is null;
select * from score where java is not null; #返回不为空的所有结果

• between | not between

#between是where一个查询方式
#查询某一个范围
select * from stuinfo where age between 18 and 20;

select * from stuinfo where age>=18 and age<=20;

(5)运算符

• 算术运算符

+ - * / % ++ --

• 比较运算符

= > < >= <= != <>

• 逻辑运算符

and 与
or  或
not 非 

(6)聚合函数

max() #最大值
min() #最小值
sum() #求和
avg() #平均值
count() #计数

#聚合函数使用在结果集上

(7)通配符

_ #一次只匹配一个字符
% #一次匹配任意数量的字符

#在模糊查询的时候使用

(8)模糊查询

关键字:like
#like写在where后面

select * from stuinfo where sname like '_丽__';

select * from stuinfo where sname like '%丽%';

8.分组查询

将查询的结果分类显示,为了方便统计

group by,如果有where要放在where的后面

select * from stuinfo group by sex;


#mysql57默认不支持group by
#修改配置文件
#vim /etc/my.cnf

#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

select count(sid) from stuinfo group by sex;

#在group将需要的结果拼接
select group_concat(sid) from stuinfo group by sex;

#添加where语句
select group_concat(sid) from stuinfo where age>20 group by sex ;

9.回溯统计

在统计的基础上,在做一次统计
with rollup
添加在group by之后

select count(sid) from stuinfo group by sex with rollup;

10.having(条件)

select * from stuinfo where age>=20;
select * from stuinfo having age>=20;

select sid from stuinfo where age>=20;
select sid from stuinfo having age>=20;   #错误

select * from stuinfo where age>=20 having city='北京';
select * from stuinfo where age>=20 and city='北京';

select * from stuinfo having sum(age>=20);
select * from stuinfo where max(age);  #错误

select * from stuinfo group by sex where age >=20;  #错误的
select * from stuinfo group by age having age >=20; 


where:条件的查询,where后面不能加上聚合函数,只能写在.数据源的后面
having:条件查询,having条件字段必须要在结果集中,having可以写在group by的后面

11.order by

order by 写在 groupby后面 ,如果有having也要写在having的后面
#主要作用是排序
#拍讯分为升序asc 降序desc,默认asc(可以不写)

select * from stuinfo order by age;
select * from stuinfo order by age desc;

12.limit

#主要作用,限制数据的显示数量,limit位置放在最后

select * from stuinfo limit 3;   #显示前三行

#从索引为0开始,向后取3行
select * from stuinfo limit 0,3;
#从索引为3的开始,向后取3条
select * from stuinfo limit 3,3;

select * from stuinfo where city='北京' order by age desc limit 3; 

#distinct去除相同的字段值
#我们需要查询数据表中一共有哪些那些地方的人注册了
select distinct city from stuinfo;
select city from stuinfo group by city; 

13.插入语句的其它用法

#拷贝数据
insert....select.....
#复制表
create table stuinfo1 like stuinfo;

insert into stuinfo1 select * from stuinfo;

#插入重复值
on duplicate key update....

#如果主键已经存在,则不能覆盖
insert into stuinfo values(1,'tom',1,18,'大阪');

#如果不存在直接插入,如果存在则更新
insert into stuinfo values(7,'tom',1,18,'大阪') on duplicate key update sname='tom',city='大阪';