insert
- 单条插入
INSERT INTO tasks(subject,start_date,end_date,description) VALUES('Learn MySQL INSERT','2018-08-02','2018-08-03','Start learning..');
- 批量插入
insert into tasks(subject,start_date,end_date,description) VALUES('hadoop','2018-07-02','2018-07-22','difficult'),
('hdfs','2018-07-02','2018-07-22','difficult'),('mapreduce','2018-07-02','2018-07-22','difficult');
update
- 更新单列，将名叫LISA的人的最后更新时间改为现在
update actor set last_update='2018-08-02 15:06:06' where first_name='LISA';
- 表名 actor
- 更新多列，将id号为7的人改名叫LANNISTER，时间改为现在
update actor set first_name='LANNISTER',last_update='2018-08-02 15:13:13' where actor_id='7';
delete
- 删除某一行
delete from tasks where subject='hadoop';
- 删除表中全部行
delete from tasks;
- 排序后删除前十行,一次顺序删除多行一定要和order by连用
delete from actor order by actor_id limit 10;
INNER JOIN基本语法如下：
select store_id,address from address inner join store on address.address_id=store.address_id;
- 表名 address,store 字段:store_id,address
LEFT JOIN的基本语法如下：
select address,store_id from address left join store on address.address_id=store.address_id limit 20;
- 表名 address,store 字段:store_id,address
RIGHT JOIN的基本语法如下：
select address,store_id from store right join address on address.address_id=store.address_id limit 10;
- 表名 address,store 字段:store_id,address
union的作用是合并两个查询的结果
select address,store_id from store right join address on address.address_id=store.address_id
union
select address,store_id from store left join address on address.address_id=store.address_id limit 10;
- 表名 address,store 字段:store_id,address
ORDER BY子句用于按升序或降序排序数据在一列或多列的基础上。一些数据库默认排序查询结果按升序排列
select * from actor order by last_update desc,actor_id desc limit 15;
- 表名 actor 字段:last_update,actor_id
GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组，在使用group by的时候一般表中会有多条数据的值是相同的
select customer_id,sum(amount) from payment group by customer_id order by customer_id;
- 表名 payment 字段:customer_id
