MySQL3_外键及查询

2023-03-07 14:37:24 浏览数 (2)

文章目录
  • MySQL_外键及查询
    • 1.数据的完整性
      • (1).保证实体的完整
      • (2).保证域的完整性
      • (3).引用的完整性
      • (4).自定义完整性
  • 2.外键
  • 3.实体之间的关系
    • (1).一对一:主键关系
    • (2)一对多|多对一
    • (3)多对多

  • 4.数据库的设计
  • 5.数据的规范
    • (1)第一范式
    • (2)第二范式
    • (3)第三范式
  • 6.规范化和性能
  • 7.查询语句
    • 语句位置顺序
      • (1)字段表达式
      • (2)from子句
      • (3)dual(肚哦)表
      • (4)where子句
      • (5)运算符
      • (6)聚合函数
      • (7)通配符
      • (8)模糊查询
  • 8.分组查询
  • 9.回溯统计
  • 10.having(条件)
  • 11.order by
  • 12.limit
  • 13.插入语句的其它用法

MySQL_外键及查询

1.数据的完整性

代码语言：javascript复制
1.实体的完整性,一条记录,就是一个实体,如果记录无法区分,则失去了实体的完整性
2.域完整性:如果有两个字段无法区分,则失去了域完整性
3.引用的完整性:两个表的对应记录不完整,则失去了引用完整性
4.自定义完整性:自己定义的一套规则
(1).保证实体的完整
代码语言：javascript复制
1.主键的约束(primary key)
2.自动增长的列(auto_increment)
3.唯一键(unique)
(2).保证域的完整性
代码语言：javascript复制
1.数据类型的约束
2.默认值(default)
3.非空约束(not null)
(3).引用的完整性
代码语言：javascript复制
应用外键(foreign key)
(4).自定义完整性
代码语言：javascript复制
1.存储过程(相当于python中的自定义函数)
2.触发器

2.外键

外键:从表的公共字段

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

代码语言：javascript复制
#特点
1.主表中不存在的记录,从表中不能插入
2.从表已存在的记录,主表中不能删除
3.先删除从表,再删除主表
代码语言：javascript复制
#学生表
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.实体之间的关系

实体的关系:

代码语言：javascript复制
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.数据库的设计

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

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

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

码农看到E-R图可以干嘛,我们根据E-R图上的需求写代码
  • E-R图
代码语言：javascript复制
E-R图是描述实体和实体之间的关系的

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

博客的E-R图:

代码语言：javascript复制
#用户和板块之间的关系
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.查询语句

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

语句位置顺序
代码语言：javascript复制
select 字段(结果集) from 表名(数据源) 
[where 条件] 
[group by 分组]
[having 条件]
[order by 排序 asc|desc]
[limit 限制 m,n];
代码语言：javascript复制
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)字段表达式
代码语言：javascript复制
select 既可以做查询,也可以做输出

select now();  #显示当前时间
select rand();   #随机数
select unix_timestamp(); # 显示Unix时间戳
(2)from子句
代码语言：javascript复制
from 后面是数据源
数据源可以写多个,返回的是一个笛卡尔积
select * from A,B,C;
(3)dual(肚哦)表
代码语言：javascript复制
dual是一个语法,是一个关键字

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

select now() from dual;
(4)where子句
代码语言：javascript复制
where是做条件查询,只返回结果为True的数据

select * from stuinfo where age <50;
  • is null | is not null
代码语言：javascript复制
where条件使用的比较运算符

select * from score where java is null;
select * from score where java is not null; #返回不为空的所有结果
  • between | not between
代码语言：javascript复制
#between是where一个查询方式
#查询某一个范围
select * from stuinfo where age between 18 and 20;

select * from stuinfo where age>=18 and age<=20;
(5)运算符
  • 算术运算符
代码语言：javascript复制
  - * / %    --
  • 比较运算符
代码语言：javascript复制
= > < >= <= != <>
  • 逻辑运算符
代码语言：javascript复制
and 与
or  或
not 非
(6)聚合函数
代码语言：javascript复制
max() #最大值
min() #最小值
sum() #求和
avg() #平均值
count() #计数

#聚合函数使用在结果集上
(7)通配符
代码语言：javascript复制
_ #一次只匹配一个字符
% #一次匹配任意数量的字符

#在模糊查询的时候使用
(8)模糊查询
代码语言：javascript复制
关键字:like
#like写在where后面

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

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

8.分组查询

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

代码语言：javascript复制
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.回溯统计

代码语言：javascript复制
在统计的基础上,在做一次统计
with rollup
添加在group by之后

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

10.having(条件)

代码语言：javascript复制
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

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

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

12.limit

代码语言：javascript复制
#主要作用,限制数据的显示数量,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.插入语句的其它用法

代码语言：javascript复制
#拷贝数据
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='大阪';
account com mysql

0 人点赞