数据库增删改查等基本操作

完整性约束         DBMS为了保证存储的数据都是完整有效的，避免存放垃圾数据，所以提供针对插入的数据进行检查。         开发人员指定对应的规则，由DBMS 负责检查，如果试图插入不合法的数据，则会自动报错，拒绝插入 实际有6种约束，可以分为 3 大类完整性约束。有些老外的书种提出的是 4 种【域完整性】                 实体完整性，由主键约束实现                 参照完整性，由外键约束实现                 用户自定义完整性，由其他4种约束实现         实际开发中除了主键约束外，其它约束一概不添加。其他的数据检查一般依赖应用程序实现，为了代码测试方便 1 、非空约束，指定列值不允许为空

create table t1(id int not null); --不允许id列值为null 
create table t2(id int null); -- 允许id列值为null 
create table 3(id int);-- 允许id列值为null
           

2 、默认约束 default 一般默认约束经常和非空约束一起使用，当不插入数据时，默认值生效

create table t1(id int not null default 0);
           

3 、主键约束，又叫做实体完整性，不允许数据重复存储         主键可以唯一标识一行数据         主键约束含义是非空、唯一

create table t1(id int primary key,....);
 create table t2( id int not null,... 
primary key(id) -- 注意这里的主键约束允许使用复合主键,多个列构成的主键 );
           

4 、唯一约束 unique         不允许添加唯一性约束的列出现重复值         可以没有null 值约束，而且也不能针对 null 进行唯一性判定

create table t1(id int unique,name varchar(20)); 
insert into t1 values(1,'zhangsan');

 insert into t1(name) values('zhao4');-- id为null 

insert into t1(name) values('zhao4');-- id为null，两次null并不会报错 

insert into t1 values(1,'li4'); -- 报错
           

5 、外键约束，又叫做参照完整性 6 、检查约束 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 tables;
           

查看表结构

desc 表名称;
           

查看创建表的 sql 语句

show create table tb_student;
           

表操作 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