MySQL之增删改查

1.插入数据

INSERT INTO table_name (field1, field2,...fieldN) VALUES (value1, value2,...valueN);

单条插入

INSERT INTO blog (author, phone, title, content, publish_date, age, balance) 
VALUES ("admin", "12352080912", "管理守则", "遵纪守法，共建文明健康网络环境", "2022-01-20", 35, 10000.00);

如果所有的列都要添加数据可以不规定列进行添加数据
第一列自增主键，可以写为0或者null

INSERT INTO blog 
VALUES (0, "yzm", "23352080912", "mysql学习", "学习mysql的第N天", "2022-01-28", 25, 300.00);

批量插入

INSERT INTO blog 
VALUES 
(0, "police", "110", "警察", "维护治安", CURRENT_DATE(), 21, 1000.00), 
(null, "doctor", "120", "医生", "救死扶伤", CURRENT_DATE(), 22, 1500.00), 
(null, "firemen", "119", "消防员", "英勇无畏", CURRENT_DATE(), 20, 1250.00);

2.查询数据

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

只返回查询结果集的前2条数据

select * from blog limit 2;

offset：跳过结果集的前m条，不可单独使用，而且需要在limit后面

select * from blog limit 2 offset 2;

第一个参数是跳过，第二个参数是限制显示个数

select * from blog limit 2,2;

in()：指定多条件

select * from blog where author in('police', 'admin', 'firemen');

between… and…：范围条件，包含区间

select * from blog where age between 21 and 25;

and：连接条件，需要满足两边的条件

select * from blog where age = 35 and balance = 10000.00;

or：或者条件，满足其中一边的条件即可

select * from blog where age = 35 or balance = 1500.00;

like：模糊查询

%：通配符，表示0个或多个字符 
_：占位符，只表示一个字符

查询包含i字符的数据

select * from blog where author like '%i%';

查询以n字符结尾的数据

select * from blog where author like '%n';

查询第二个字符是o的数据

select * from blog where author like '_o%';

3.更新数据

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

update blog set balance = 500.00 where author = 'yzm';

update blog set balance = balance + 200.00 where author = 'yzm';

4.删除数据

DELETE FROM table_name [WHERE Clause]

相关链接

首页
上一篇：表结构
下一篇：复杂查询