MySQL表操作DML/DQL

目录

表操作DML/DQL 

样例表

增加数据

修改数据

删除数据  

整表删除操作 

逻辑删除和物理删除 

逻辑删除的具体实现：

---

表操作DML/DQL 

• DML数据操纵语言，用于对数据库表中的记录进行更新操作，插入insert、修改update、删除 delete
• DQL数据查询语言，用于对数据库表中的记录进行查询操作，select

样例表

create table tb_users(
id bigint primary key auto_increment,
username varchar(20) not null,
password varchar(24) not null,
birth date,
sex boolean default 1,
score numberic(8,2)
)engine=innodb default charset utf8;

增加数据

语法 1 ： inser into 表名称 values( 值 1, 值 2,....) 用于针对表中的每个列指定对应的值，例如

insert into tb_users values(null,'yanjun','123456','1989 - 2 - 3',1,12345.67) ; ，注意

values 后面括号中的值和列一一对应，注意数据类型

---

语法2： insert into 表名称(列名1,列名2,...) values(值1,值2,...) 人为指定列插入数据，注意两

个括号一一对应，一般用于不是每个列都需要插入数据时使用。例如 insert into

tb_users(username,password,birth) values('yanjun','123456','1989-2-3') 。如果没有出现

的列而且列上没有default约束，则自动插入值null；如果有default约束则插入默认值

注意：

• 值与字段必须对应，包括个数和数据类型
  • 值必须在合理的字段类型范围内
  • varchar、char和日期类型必须使用单双引号包裹
  • 如果没有任何限制，控制允许插入
• 没有指定列名称时，必须给出完整数据，而且对应顺序
• 优化写法：指定列名称。无须遵守定义表时列的顺序
• 批量插入查询出的数据
  •  insert into table1(列名1,列名2,...) select 列1,列2,... from table2；
• 将一个表的查询出的内容插入创建的新表中。不仅克隆表结构，同时克隆表中数据。但是约束会有丢失问题
  • create table newTable as select * from oldTable ;

修改数据

语法 1 ： update 表名称 set 列名 1= 值 1, 列名 2= 值 2,... 。修改表中所有行的指定列对应的值，等号后面可以是一个表达式进行计算。例如score 新增 10% 写法 update tb_users set score=score*1.1 允许同时修改多个列： update tb_users set score=500, sex=0

语法 2 ： update 表名称 set 列名 1= 值 1, 列名 2= 值 2,... where 条件 用于修改表中满足条件的行指定列的值；例如1989 年之前的用户积分新增 10% ， update tb_users set score=score*1.1 where year(birth)<1989 ， year 是系统函数，用于获取指定列的年份值

删除数据  

语法 1 ： delete from 表名称 ; 删除表中所有数据。例如 delete from tb_users ;

语法 2 ： delete from 表名称 where 条件 ; 删除表中满足条件的所有行。例如删除 1990 年的所有女生， delete from tb_users where sex=0 and year(birth)=1990

整表删除操作 

方法 1 ： delete from tb_student ; 可以删除表中的所有数据，属于 DML 语句，所以支持事务，支持撤销回滚操作，但是大量的删除数据时需要记录日志，所以执行效率很低。优势在于可以反悔，缺点在于 记录日志需要时空代价

方法 2 ： truncate table tb_student ; 表的截断操作，可以删除表中的所有数据，属于 DDL 语句，没有事务，不会记录操作日志，所以大量删除数据时执行效率高。优势是执行效率，缺点在于没有反悔的机会。底层原理就是创建一个相同的空表

逻辑删除和物理删除 

所谓的物理删除就是从表中直接删除数据；逻辑删除类似于回收站，只是给某行数据上添加一个删除标志，表示该行数据已经删除了

逻辑删除的具体实现：

1 、修改表 tb_users 新增一个额外列，用于表示该行数据是否已经删除

alter table tb_users add deleted boolean default 0; -- 0表示没有删除

2 、逻辑删除，并没有真实删除数据，只是修改一个标识列的值

update tb_users set deleted=1 where id=5; -- 删除5号用户信息 delete from tb_users
where id=5;物理删除

3 、真正查询时为了避免查到已经删除的数据，所以需要额外的查询条件

select * from tb_users where deleted=0; --只查询deleted值为0的行，不要已经标识删除的函
数deleted=1