day42——外键的限制和解决方法、外键的三种约束模式、修改表（单表查询）

时间：2020-11-25 05:48:41

day42

外键的限制和解决方法

可以添加外键关联的那个字段可以是 被唯一（unique）约束的字段 或者 主键

限制：+

由于外键的使用，致使多个表之间产生了联系，当我们对这些表进行更新或删除操作的时候一直报错，无法达到我们的目的。

解决方法：

删除关联字段再进行自己想完成的操作，接着添加外键关系。

场景

book表和publish表为多对一关系，book表的pid字段外键关联到了publish表的id字段

  • 查看外键关系名称

    外键关系名称：在我们创建外键的时候，mysql帮我们自动生产一个外键名称

查看一个外键关系名称

show create table book(表名);

查看所有外键的名称

select REFERENCED_TABLE_SCHEMA,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME,table_name,CONSTRAINT_NAME from information_schema.key_column_usage;
  • 删除外键关系
alter table book drop foreign key book_ibfk_1(外键名称);
  • 删除字段
alter table  publish drop id(字段名称);
  • 添加字段
alter table publish add id(字段名称) int(数据类型) primary key auto_increment(约束条件);
  • 创建外键时指定外键名称

外键名称不能直接修改只能先删除外键关系再添加来实现

创建表时：

create table t1(

	id int,

    pid int,

    constraint fk_t1_publish foreign key(pid) references publish(id)

);

创建表完成之后

alter table book add constraint fk_t1_publish foreign key(pid) references publish(id);

外键的三种约束模式

约束模式和外键名称一样，只能先删除外键关系再进行添加

  • 严格模式（默认的）：district ，父表不能删除或者更新已经被子表数据引用的记录

  • 级联模式：cascade，父表的操作，对应的子表关联的数据也跟着操作

    创建表时，添加外键关系：
    
create table t1(
    
	id int,
    
    pid int,
    
    foreign key(pid) references publish(id) on delete cascade on update cascade
    
);

创建表完成之后，添加外键关系
    
alter table book add foreign key(pid) references publish(id) on delete cascade on update cascade;

  • 置空模式：set null，父表操作之后，子表对应的数据（外键字段）也跟着被置空

创建表时，添加外键关系：

create table t1(

	id int,

    pid int,

    foreign key(pid) references publish(id) on delete set null on update set null

);

创建表完成之后，添加外键关系

alter table book add foreign key(pid) references publish(id) on delete set null on update set null;
注意
  • 通常一个合理的约束模式是：删除的时候子表置空，更新的时候子表级联
  • 指定模式的语法：foreign key(外键字段) references 父表(主键字段) on delete 模式 on update 模式;
  • 删除置空的前提条件是 外键字段允许为空，不然外键会创建失败
  • 外键虽然很强大，能够进行各种约束，但是外键的约束降低了数据的可控性和可扩展性。通常在实际开发时，很少使用外键来约束

修改表——单表查询

增加insert
insert into 表名 values(字段1，字段2.。。);

insert into 表名(id,name) values(字段1，字段2),(xx1,xx2); id,name,age(默认值)

插入查询结果：

	insert into 表名(字段1，字段2，。。。)

			select (字段1，字段2，。。。) from 表2 where ...;

将表2里面查询出来的结果插入到表1中，但是注意查询出来的数据要和我们表1的字段对应好
修改update
update 表名 set 字段1=值1,字段2=值2 where 条件; 

update t2 set name='xxoo' where id = 1;

注意：不指定后面的where条件的话，会修改这个字段所有的数据
删除记录delete
delete from t3; 删除所有的数据，但是不会重置自增字段的数据号

delete from t3 where id = 1; 删除指定的数据，删除id字段数据为1的那一行记录

# 清空表

truncate 表名; 自增字段会重置
查询
# 四则运算：

	select salary*12 from employee;

# 自定义显示格式 concat用法：

	select concat('姓名：',name,'年薪：',salary*12) as Annual_salary from employee;
where 条件
# 比较运算符：> < >= <= <> !=

	select name from employee where post='sale';

# between 的用法

	select * from employee where id between 10 and 15;

	id值在10到15之间——大于等于和小于等于的区间

# in的用法

	select * from employee where id in(1,3,6);

	等价于id = 1 or id = 3 or id = 6

# like的用法

	pattern可以是 % 或者 _

	% 表示任意多个字符

		select * from employee where name like 'wu%';

	_ 表示一个字符

		select * from employee where name like 'al_'; 结果为空

		select * from employee where name like 'al__'; 结果为alex

		select * from employee where name like 'al___'; 结果为空

# 逻辑运算符：在多个条件下直接可以使用逻辑运算符 and or not

	select * from employee id > 10 and name like 'al%';

	select * from employee not id > 10; id小于或等于10的， not取反
分组group by
实例：

	统计每个岗位的名称已经最高工资

		select post,max(salary) from employee group by post;

	分组时可以跟多个条件，那么现在是多个条件同时重复才算一组，group by 后面多条件用逗号隔开

		select post,max(salsary) from employee group by post,id;

ONLY_FULL_GROUP_BY模式

set global sql_mode = 'ONLY_FULL_GROUP_BY';

如果设置了这个模式，那么select后面只能写group by后面的分组依据字段和聚合函数统计结果
分组再过滤having
select post,max(salary) from employee group by post having max(salary)>20000;

having过滤后面的条件可以使用聚合函数，where 不行
去重 distinct
实例：

	select distinct post from employee;

注意问题：select的字段必须写在distinct的后面，并且如果写了多个字段，比如：select distinct post,id from employee; 这句话意思是post和id两个组合在一起同时重复的才算是重复数据
排序order by
实例：

	升序

	select * from employee order by age;

	select * from employee order by age asc;

	降序

	select * from employee order by age desc;

	多条件排序

	按照age字段升序，age相同的数据，按照salary降序排序

	select * from employee order by age asc,salary desc;
限制查询的记录数：LIMIT
实例：

	取出工资最高的前三位

	select * from employee order by salary desc limit 3;

	默认初始位置为0，从第一条开始顺序取出三条

	select * from employee order by salary desc limit 0,5;

	从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

	select * from employee order by salary desc limit 5,5;

	从第5开始，即先查询第6条，然后包含这一条在内往后查5条 （6,7,8,9,10）
使用正则表达式查询
#之前我们用like做模糊匹配，只有%和_，局限性比较强，所以我们说一个正则，之前我们是不是学过正则匹配，你之前学的正则表达式都可以用，正则是通用的

SELECT * FROM employee WHERE name REGEXP '^ale';

SELECT * FROM employee WHERE name REGEXP 'on$';

SELECT * FROM employee WHERE name REGEXP 'm{2}';

小结：对字符串匹配的方式

WHERE name = 'egon';

WHERE name LIKE 'yua%';

WHERE name REGEXP 'on$';

相关文章