mysql常用的sql语句

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