mysql 修改被关联_5 删除或修改被关联字段 级联 行记录的操作 where 等条件

# 昨日内容回顾

客户端完整连接语句

```

mysql -h 127.0.0.1 -P 3306 -u root -p 密码

```

创建表

```

create table 表名(

字段1 类型(宽度) 约束条件,

字段2 类型(宽度) 约束条件,

# 添加外键

foreign key(字段2) references 表2(表2的某个字段)

)

```

数据类型:

```

数值类型(整型\浮点型) 字符串 时间日期类型 枚举和集合

无符号:字段1 类型(宽度) unsigned,

```

完整性约束

```

not null 非空

default 默认值 default 1

unique 唯一

primary key 主键 不为空且唯一

auto_increment 自增

foreign key 外键

```

# 今日内容

删除或修改被关联字段

```

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

1 查看外键关系名称:

show create table book;

| book | CREATE TABLE `book` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` char(10) DEFAULT NULL,

`pid` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `pid` (`pid`),

CONSTRAINT `book_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `publish` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |

2 删除外键关系

alter table book drop foreign key book_ibfk_1(外键名称);

3 删除字段

alter table publish drop id(字段名称);

4 添加字段

alter table publish add id(字段名称) int(数据类型) primary key auto_increment(约束条件);

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

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

```

创建外键时指定外键名称

```

创建表时:

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

```

级联

级联有几个模式:

严格模式(默认的),　　　　外键有强制约束效果,被关联字段不能随意删除和修改

模式(cascade):　　　　外键有强制约束效果,被关联字段删除或者修改,关联他的那么字段数据会随之删除或者修改

```

constraint fk_t1_publish foreign key(pid) references publish(id) on delete cascade on update cascade;

```

set null模式:　　被关联字段删除时,关联他的字段数据会置成null

修改表.

行记录的操作

增加insert

```

insert into 表名 values(字段1,字段2...);

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

插入查询结果

语法：

INSERT INTO 表名(字段1,字段2,字段3…字段n)

SELECT (字段1,字段2,字段3…字段n) FROM 表2

WHERE …; #将从表2里面查询出来的结果来插入到我们的表中，但是注意查询出来的数据要和我们前面指定的字段要对应好

```

修改update

```

语法：

UPDATE 表名 SET

字段1=值1, #注意语法，可以同时来修改多个值，用逗号分隔

字段2=值2,

WHERE CONDITION; #更改哪些数据，通过where条件来定位到符合条件的数据

mysql> 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 条件

```

1. 比较运算符：> < >= <= <> !=

SELECT name FROM employee WHERE post='sale';

2. between 10 and 15 id值在10到15之间 #大于等于和小于等于的区间

mysql> select * from employee where id between 10 and 15

3. in(1,3,6) 值是80或90或100

select * from employee where id in(1,3,6) 等价于id=1 or id=3 or id=6;

4. like 'egon%'

pattern可以是%或_，

%表示任意多字符

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

_表示一个字符

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

select * from employee where name like "al__";#结果alex

mysql> select * from employee where name like "al___"; 三个下划线的无法匹配到alex,结果为空

5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 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(salary) 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;

上面这两种写法都是按照age字段来进行升序排列

select * from employee order by age desc;

desc是降序排列

多条件排序

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

select * from employee order by age asc ,salary esc;

```

补充:级联set null的用法和示例

```

mysql> create table tt2(id int primary key auto_increment,name char(10));

mysql> create table tt3(id int,pid int,foreign key(pid) references tt2(id) on delete set null);

Query OK, 0 rows affected (1.06 sec)

mysql> desc tt3;

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| pid | int(11) | YES | MUL | NULL | |

+-------+---------+------+-----+---------+-------+

2 rows in set (0.01 sec)

mysql> insert into tt2(name) values('xx1'),('xx2');

Query OK, 2 rows affected (0.14 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into tt3 values(1,1),(2,1);

Query OK, 2 rows affected (0.12 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from tt3;

+------+------+

| id | pid |

+------+------+

| 1 | 1 |

| 2 | 1 |

+------+------+

2 rows in set (0.00 sec)

mysql> delete from tt2 where id = 1;

Query OK, 1 row affected (0.10 sec)

mysql> select * from tt3;

+------+------+

| id | pid |

+------+------+

| 1 | NULL |

| 2 | NULL |

+------+------+

2 rows in set (0.00 sec)

```