对表中数据的操作
-- 向表中插入数据
insert [ignore] into 表名(字段列表)
values (值列表)
use scoredb;
show tables;
desc departments;
insert into departments (id, deptno, deptname, director, location)
values (1,101,"张xx","张院长","新乡");
select * from departments;
-- 向表中所有字段都插入数据可以省略字段列表
insert into departments
values (2,102,"张x","张院长","商丘");
-- 向表中部分字段添加数据
insert into departments (id, deptno)
values (3,103);
-- 一次性添加多条数据,可以合并成一个插入语句,只需要追加值列表即可,多个值列表使用逗号进行分隔
insert into departments
values
(4, 104,"王xx","王院长","周口"),
(5, 105,"袁xx","元院长","洛阳");
-- 修改数据
update departments set
director = "袁xx",
deptname = "袁xx"
where id = 5;
-- 删除
delete from departments
where id = 3;
-- 逐行删除全表
delete from departments;
-- 删除全表数据 (先删除表,在创建表)、效率高
truncate departments;
-- 删除的数据,找回是很麻烦的
-- 有些企业会对数据执行逻辑删除,而不是物理删除(delete)
-- 逻辑删除 : 每张表都增加一个字段, is_delete 表示该行数据是否被逻辑删除
-- is_delete = 0 表示被逻辑删除,否则没有被删除