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]