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