DDL、DML、DQL

DDL表操作

创建操作

create table tb_student(
	id bigint primary key auto_increment,
	name varchar(10) not null comment '说明信息',
	birth date,
	sex boolean default 1,
	salary numeric(8,2)
)engine=innodb default charset utf8;

快速创建一个表和另外一个表的结构相同，只是克隆表结构，不会克隆数据

create table 新表名称 like 已经存在的旧表名称;

修改表结构，语法规则为alter table 表名称 add/drop/modify 列名称 [类型 约束]

alter table tb_student add class_name varchar(20) default '软件19'; -- 新增列
alter table tb_student drop column salary; -- 删除指定列

alter table tb_student modify class_name int; -- 修改列定义时，要求已经存在的数据必须符合规则，否则修改失败。modify一般用于修改列的数据类型和长度，但是如果有数据则必须和目标 类型匹配，否则修改失败

修改列的名称

 alter table 表名称 change 旧有列名称 新列名称 类型 约束;

删除表drop table 表名称

drop table tb_student; 
drop table if exists tb_student;

查看当前数据库种的所有表

show tyables;

查看表结构

desc 表名称;

查看创建表的sql语句

show create table tb_student;

表操作DML/DQL

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

样例表

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

增加数据

语法1： inser into 表名称 values(值1,值2,… ) 用于针对表中的每个列指定对应的值，例如insert into tb_users values(null,‘zhangsan’,‘123456’,‘1989-2-3’,1,12345.67); ，注意

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

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

个括号一一对应，一般用于不是每个列都需要插入数据时使用。例如insert into tb_users(username,password,birth) values(‘zhangsan’,‘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

​