Mysql笔记（4）【DML】

目录

 

十五、添加/修改/删除 表数据

1）insert 添加数据

2）update 修改数据

3）delete 删除数据

4）创建表加入约束（表约束 constraint）

一、非空约束（not null）

二、唯一性约束（unique）

三、主键约束PK（primary key）

四、外键约束FK（foreign key）

---

十五、添加/修改/删除 表数据

DML数据操作语句：insert、update、delete；（增删改表中的数据与表结构无关）

1）insert 添加数据

语法：insert into 表名(字段名1,字段名2,…) values(值1,值2,…);

（字段和数值必须一一对应，字段与数据个数必须相同，数据类型必须一致；若没有添加字段值，默认插入null值）

  insert into 表名(值1,值2,…);

（必须添加所有字段的值，否则添加失败）

1.向t_student 表格中所有字段插入数据

insert into t_student(no,name,sex,birth,email) values(1,'原','男',str_to_date('1999-01-12','%Y-%m-%d'),'yuan@163.com');

2.向t_student 表格中部分字段插入数据

insert into t_student(name,birth,email) values('国','1989-12-12','guo@163.com');

3.向t_student表格中所有字段插入数据

insert into t_student  values(2,'号','女','1990-11-11','hao@163.com');

结论：不建议使用字段省略，若省略就表示要将表中所有字段必须赋值，并且程序不健壮，若修改表结构，将导致insert语句执行失败；

4.如何插入日期

a）第一种方式：插入的日期格式与MySQL默认日期格式一样

insert into t_student(no,name,birth) values(1,'jack','1980-12-12');

b）第二种方式：采用str_to_date

insert into t_student(no,name,birth) values(1,'jack',str_to_date('1980-12-12','%Y-%m-%d'));

c）第三种方式：

insert into t_student(no,name,birth) values(1,'jack',now());

5.查看变量：

show variables like '%char%';

设置结果编码集（叧对当前窗口有效）set character_set_results = 'GBK';

2）update 修改数据

语法格式：update 表名 set 字段名=字段值,字段名=字段值 where 条件；

注意：update如果没有条件限制，将把整张表的数据全部更新

1.将编号no为3的记录name改为zhangsan，email改为zhangsan@163.com;

update t_student set name='zhangsan',email='zhangsan@163.com' where no=3;

2.将所有名字name改为lisi

update t_student set name='lisi';

3.将emp1表中的 name 包含“o”字母的改为wanngwu；

update emp1 set ename='wangwu' where ename like '%o%';

4.将emp1表中工作岗位为MANAGER和SALESMAN员工的工资上调10%；

update emp1 set sal=sal*1.1 where job in('MANAGER','SALESMAN');

3）delete 删除数据

语法格式：delete from 表名 where 条件限制；

注意：若没有条件限制，会将表中所有记录全部删除；

1.将t_student表中no为3的学生删除

delete from t_student where no=3;

2.将t_student表中的数据全部删除

delete from t_student;

3.将emp1表中部门编号为20的MANAGER删除掉；

delete from emp1 where deptno=20 and job='MANAGER';

4）创建表加入约束（表约束 constraint）

Ø 什么是约束？

     实际上是对表中数据的限制条件；

Ø 设计表时加入约束的目的？

      保证表中数据的完整和有效；

一、非空约束（not null）

作用：not null约束字段不能为 null 值，必须赋具体数据；

示例：创建t_user表，name字段不能为空

表名：t_user

字段名	字段类型	约束
id	int(10)
name	varchar(32)	not null 不为空
email	varchar(128)

create table t_user(id int(10),name varchar(32) not null,email varchar(128));

向t_user 表中插入数据

二、唯一性约束（unique）

作用：unique约束的字段具有唯一性，不能重复；

示例：创建t_user表，name不能为空，email保证唯一

1. 方法一：【列约束】

create table t_user(id int(10),name varchar(32) not null,email varchar(128) unique);

向t_user表中插入数据

注意：“unique”约束的字段不能重复，但可以为null，null不是一个值，也不能用等号比较；

2.方法二：【表级约束】unique(email)

create table t_user(id int(10),name varchar(32) not null,email varchar(128),unique(email));

3.使用表级约束给多个字段添加约束

【给name和email两个字段联合唯一】

create table t_user(id int(10),name varchar(32) not null,email varchar(128),unique(name,email));

向t_user表中插入数据

注意：多个字段设置唯一约束，只有同时相同才会报错

4.表级约束还可以给约束起名，原因：以后可以通过名字操作这个约束

constraint 表名 unique(约束字段)

create table t_user(id int(10),name varchar(32) not null,email varchar(128),constraint t_user_email_unique unique(email));

5.查看唯一性约束名字

1）查看唯一性约束名字

2）选择information_schema数据库

3）查看information_schema库的表

4）table_constraint 该表专门存储约束信息

5）查询出表t_user中的唯一约束名称

6.组合使用 not null 和 unique

作用：被not null 和unique约束的字段，该字段即不能为null也怒能重复；

示例：需求，创建t_user表，用户编号为id即不能为空也不能重复

create table t_user(id int(10) not null unique,name varchar(32) not null,email varchar(128) unique);

向t_user表中插入数据（id字段即不为空也不能重复）

三、主键约束PK（primary key）

实例：中国公民身份信息

Id	Name	Sex	Age
身份证号	张三	男	20
身份证号	李四	女	22

1）主键涉及到3个术语：

主键约束：primary key；

主键字段：字段添加primary key后，该字段称为主键字段；

主键值：主键字段的每一条数据都被称为主键值；

2）主键作用

1.添加主键字段既不能重复也不能为空，自动添加”索引—index“

2.表的三大范式：第一范式：要求一张表中都应该有主键，一张表只能有一个主键；

3. 主键值是这行数据的唯一标识（相当于身份证号）；

注：即使两行数据完全相同，主键不同，我们也认为这是两行不一样的数据；

3）主键分类：

根据主键字段的数量来划分：

单一主键：给一个字段添加主键约束；

【列级】字段 字段类型(长度) primary key

创建t_user表：设置id为主键

create table t_user(id int(10) primary key,name varchar(32) not null,email varchar(128) unique);

【表级】constraint 表名_字段名_pk primary key(字段名)（实际工作常用）

create table t_user(id int(10),name varchar(32) not null,email varchar(128) unique,constraint t_user_id_pk primary key(id));

复合主键：给多个字段联合添加一个主键约束；（不建议使用，违背了表设计的第一范式）

创建t_user表：id和name是复合主键

【表级】primary key(字段1，字段2)

create table t_use(id int(10),name varchar(32) not null,email varchar(128) unique,primary key(id,name));

复合主键命名：constraint 表名_字段名1_字段名2_pk primary key(字段1，字段2)

constraint t_user_id_name_pk primary key(id,name)

根据主键性质来划分：

自然主键：主键值若是一个自然数，并且这个自然数与业务没有任何关系，这种主键称为自然主键；

业务主键：主键值若和当前表中的业务紧密相关，那么这种主键值被业务主键；

如果业务发生改变时，业务主键往往会受到影响，所以业务主键使用较少，大多情况使用自然主键。【如：身份证号位数扩展】

4）MySQL中自动生成主键值（MySQL特有）

1.定义：MySQL数据库管理系统中提供了一个自增数字auto_increment，专门用来自动生成主键值，主键值不需要用户去维护，也不需要用户去生成，MySQL会自动生成。

自增数字默认从1开始，以1递增：1、2、3、4、...

2.示例：递增关键字：auto_increment

创建t_user1表：id，name；id为自增主键

create table t_user1(id int(10) primary key auto_increment,name varchar(32));

给t_user1添加数据

insert into t_user1(name) values('原');

注意：删除一行数据，自增主键不会替代删除的自增数，会继续排下去，一般删除数据都应该是逻辑修改，而不是直接删除数据；

四、外键约束FK（foreign key）

1）外键涉及三个术语：

外键约束：foreign key；

外键字段：字段添加 foreign key 后，该字段被称为外键字段；

外键值：外键字段的每一条数据都被称为外键值；

2）外键分类：

根据外键字段的数量：

单一外键：给一个字段添加外键约束

复合外键：给多个字段添加外键约束

3）外键在同一张表中可以有对个外键存在

示例一：

1.需求：设计数据库表用来存储学生和班级信息（给出两种解决方案）

需求分析：

a）学生表 t_student 包含：sno，sname，classno，cname；

b）学生信息和班级信息之间的关系：一个班级对应多个学生，典型的一对多关系；

【第一种解决方案】：将学生信息和班级信息存储到同一张表中

学生信息表：t_student

sno（PK）	sname	classno	cname
1	张三	100	北京市101中学高三1班
2	李四	100	北京市101中学高三1班
3	王五	200	北京市101中学高三2班
4	田六	200	北京市101中学高三2班
5	小米	300	北京市101中学高三3班
6	小明	300	北京市101中学高三3班

以上设计的缺点：数据冗余

【第二种解决方案】：将学生信息和班级信息分开两张表存储：学生表 + 班级表

思路分析：

第一步：创建t_student表和t_class表

学生表 t_student

Sno	Sname
1	张三
2	李四
3	王五
4	田六
5	小米
6	小明

班级表 t_class

cno	cname
100	北京市101中学高三1班
200	北京市101中学高三2班
300	北京市101中学高三3班

第二步：t_student表要与t_class表有关系，需要在t_student表中添加一个classno字段做为外键

学生表 t_student

Sno（PK）	Sname	classno（FK）
1	张三	100
2	李四	100
3	王五	200
4	田六	200
5	小米	300
6	小明	300

班级表 t_class

Cno（PK）	Cname
100	北京市101中学高三1班
200	北京市101中学高三2班
300	北京市101中学高三3班

创建t_class 表：

create table t_class(cno int(10),cname varchar(128) not null unique,constraint t_class_cno_pk primary key(cno));

添加数据：

insert into t_class(cno,cname) values(100,'北京市101中学高三1班');

insert into t_class(cno,cname) values(200,'北京市101中学高三2班');

insert into t_class(cno,cname) values(300,'北京市101中学高三3班');

创建t_student 表：

create table t_student(sno int(10) auto_increment,sname varchar(32) not null,classno int(32),constraint t_student_sno_pk primary key(sno),constraint t_student_classno_fk foreign key(classno) references t_class(cno));

添加数据：

insert into t_student(sname,classno) values('张三',100);

insert into t_student(sname,classno) values('李四',100);

insert into t_student(sname,classno) values('王五',200);

insert into t_student(sname,classno) values('田六',200);

insert into t_student(sname,classno) values('小米',300);

insert into t_student(sname,classno) values('小明',300);

测试：在t_student表中插入一个班级编号为400的

insert into t_student(sname,classno) values('小红',400);

执行失败原因：引用外键值在外键表 t_class 中 cno数据中不存在；

结论（classno值必须来自cno）：为了保证 t_student 表中的 classno 字段中的数据必须来自 t_class 表中的 cno 字段中数据，有必要给 t_student 表中 classno 字段添加外键约束，

classno 称为外键字段；classno中的100、200、300称为外键值，classno在这里是单一外键；

请注意以下三点：

1、外键字段可以为NULL，外键为空的数据也叫孤儿数据；

2、被引用字段必须具有unique约束；

3、有了外键引用之后，表分为父表和子表，以上父表：班级表 t_class； 子表是：学生表 t_student；

     创建表时先创建父表，再创建子表；插入数据时，先插入父表数据再插入子表数据；

示例二：找出每个学生的班级名称

1.找出每个学生的班级名称

select s.*,c.* from t_student s join t_class c on s.classno=c.cno;

重点：典型的一对多设计，在多的一方加外键；

注意：在MySQL中没有提供修改外键约束的语法功能；

4）级联更新和级联删除

用法：在添加级联更新与级联删除的时候，需要在外键约束后面添加关键字；

注意：级联更新与级联删除操作谨慎使用，因为级联操作会将数据改变或删除【数据无价 】

1.级联删除：on delete cascade

定义：在删除父表数据的时候，级联删除子表中数据；

a）删除外键约束

语法：alter table 表名 drop foreign key 外键字段;

alter table t_student drop foreign key t_student_classno_fk;

b）添加外键约束和级联删除功能

语法：alter table 表名 add

constraint 外键名称 foreign key(外键字段)

references 引用表名(引用表中字段名称) on delete cascade;

alter table t_student add

constraint t_class foreign key(classno)

references t_class(cno) ondelete cascade;

c）级联删除—完整示例

第一步：删除t_student 中的外键

alter table t_student drop foreign key t_student_classno_fk;

第二步：增加t_student_classno_fk外键，并加入级联删除 on delete cascade;

alter table

t_student

add

constraint t_student_classno_fk foreign key(classno)

references t_class(cno)

on delete cascade;

第三步：删除t_class 表中班级编号为200的信息，包括所在班级的学生信息

delete from t_class where cno=200；

2.级联更新：on update cascade

定义：在更新父表中数据的时候，级联更新子表中数据；

a）删除外键约束（没有修改外键约束语句）

语法：alter table 表名 drop foreign key 外键字段;

  alter table t_student drop foreign key  t_student_classno_fk;

b）更新外键约束和添加级联更新功能

语法：alter table 表名 add

constraint 外键名称 foreign key(外键字段)

references 引用表名称(引用表字段) on update cascade；

alter table t_student add

constraint t_student_classno_fk foreign key(classno)

references t_class(cno) on update cascade;

c）级联更新—完整示例

第一步：删除 t_student 中的外键

alter table t_student drop foreign key t_student_classno_fk;

第二步：增加t_student_classno_fk 外键，并加入级联更新 on update cascade;

alter table t_student add

constraint t_student_classno_fk foreign key(classno)

references t_class(cno) on update cascade;

第三步：更新t_class表中班级编号300为1805，包括所在班级的学生信息

update t_class set cno=1805 where cno=300;