MySQL3_外键及查询

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

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

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

应用外键(foreign key)

1.存储过程(相当于python中的自定义函数)
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的引擎上使用

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

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

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

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

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

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

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

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

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

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

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

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);

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

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

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

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

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

select now() from dual;

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

select * from stuinfo where age <50;

where条件使用的比较运算符

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

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

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

+ - * / % ++ --

= > < >= <= != <>

and 与
or  或
not 非 

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

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

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

#在模糊查询的时候使用

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

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

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

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 ;

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

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

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的后面

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

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

#主要作用,限制数据的显示数量,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; 

#拷贝数据
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='大阪';