mac终端操作数据库--(3)增删改查

mac终端操作数据库–(3)增删改查

1. 插入记录(insert into)

insert 表名 into (列名1,列名2) values(值1,值2);

//插入单条全列记录
insert into user_infos (id,name,sex,age) values(002,"zhouyu",1,28);
insert into user_infos values(003,"lisi",1,33);

//插入多条全列记录
insert into user_infos values
(004,"xiaoming",0,16),
(005,"xiaohua",0,19),
(006,"qiaoqin",0,23);


//插入单条部分列记录
insert into user_infos (id,name) values(004,"xiaoming");

//插入多条部分列记录;
insert into user_infos (id,name) values
(007,"xiaoming"),(008,"xiaohua"),(009,"qiaoqin");

常见错误 : 基本都是语法错误引起的,也有主键重复引起的

1. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘vaules(010,“xiaoqin”,0,21)’ at line 1
2. ERROR 1062 (23000): Duplicate entry ‘4’ for key ‘PRIMARY’

2. 删除记录(delete)

delete from 表名 [where 条件]

//删除表的一条记录 delete from 表名 条件
delete from user_infos where id=2;

//删除表的全部记录
delete from user_infos;

3. 更新记录(update set)

update 表名 set 列名1=列1的值,列名2=列1的值 [where 条件]

//修改指定条件记录
update user_infos set sex=1 where name="xiaohua";
//不指定条件,会修改全部记录
update user_infos set sex=1; 

4. 查询记录(select from)

select查询结合条件语句非常丰富,这里不再意义列举
select [distinct] [*] [列名1,列名2] from 表名 [where 条件]

//查询全部
select * from user_infos; 
//查询指定条件
select * from user_infos where name="xiaohua";

5. as别名(用于多表查询)

//表的别名
select u.name,u.age from user_infos as u;
//列的别名
select u.name as 姓名, u.age as 年龄 from user_infos as u;

6. distinct去重复数据

//去重
select distinct name from user_infos;