curd的解释:创建(Create) 更新(Update) 读取(Retrieve) 和删除(delete)
增加
全列植入(输入的数和列数必须一一对应)
– insert [into] 表名 values(…)
– 主键字段 可以用0 null default来占位
– 向classes表中插入一个班级
insert into classesvalues(0,"菜鸟")
-- 枚举中的下表从1开始(1:男;2:女;3:保密)
insert into students values(0,"小李",20,160,"1",1,"2000-07-07");
部分植入(可以为空的如果没有输入或默认,直接赋null)
-- insert into 表名(列1,...)values(值1,...)
insert into students (name,gender) values("小秋",1);
多行植入
多行插入部分植入
insert into students (name,gender) values("大桥",1),("小乔",2);
多行插入全部植入
insert into students values(0,"西施",20,160,"1",1,"2000-07-01"),(0,"东施",20,160,"1",1,"2000-07-02");
修改
-- update 表名 set 列1=值1,列2=值2...where 条件;
-- update students set gender=2;--全部都该
update students set gender=2 where id=4;--只要是id=4的都修改
update students set age=22,gender=2 where id=3;--只要是id=3的都修改age和gender
删除
物理删除
**注意:**删了就不要再使用,以免出问题。
-- delete from 表名 where 条件;
-- delete from students;--整个数据表中的所有数据全部删除
delete from students where id=4;
逻辑删除
-- 用一个字段来表示,这条信息是否已经不能再使用了
-- 给students表添加is_delete字段 bit 类型
-- alter table students add is_delete bit default 0;
-- update students set is_delete=1 where id=6;
看不见值,但是可以正常使用。
update students set is_delete=1 where id=6;
查询基本使用
查询所有列
-- select * from students;
select * from students;
一定条件查询
select * from students where id<5;
查询指定列
select name,gender from students;
select students.name,students.gender from students;
select name,gender from students where id<5;
可以使用as为列或表指定列名
-- select 字段[as 别名],字段[as 别名] from students;
select name as 性名,gender as 性别 from students;
字段的顺序
select gender as 性别,name as 性名 from students;
select id as 序号,gender as 性别,name as 姓名 from students;
可以使用as为列或表指定表名
select s.name,s.gender from students as s;
消除重复行
-- distinct 字段
select distinct gender from students;