MySQL的单表操作之数据操作、排序与限量

一、数据操作
1，复制表结构和数据
复制已有的表结构
在开发时，若需要创建一个与已有数据表相同结构的数据表时，具体SQL语句如下：
已创建表sh_goods（博客中含有已创建过该表的语句）,复制一份与之相同结构的表mydb.my_goods
create table mydb.my_goods like sh_goods;
查看表结构
show create table mydb.my_goods\G

复制已有的表数据
insert into mydb.my_goods select * from sh_goods;
需要注意的是，若数据表中含有主键，而主键又具有唯一性，所以在数据复制时，还要考虑主键冲突的问题。例如通过以下方式再向my_goods表中添加数据，系统会报“主键重复”的错误。
insert into mydb.my_goods select *from sh_goods;
ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘PRIMARY’
所以数据复制时可以指定除id主键外的任意字段完成：
insert into mydb.my_goods (category_id,name,keyword,price,content)select category_id,name,keyword,price,content from sh_goods;

创建临时表
临时表指的是一种仅在当前会话中可见，并再当前会话关闭时自动删除的数据表，主要用于临时存储数据。(只需在create与table之间加上temporary即可)
create temporary table mydb.tmp_table1(id int);
或者create temporary table mydb.tmp_table2 select id,name from shop.sh_goods;

2.解决主键冲突
在对数据表插入数据时，若表中的主键含有实际的业务意义，因此在插入数据时若不能确定对应的主键是否存在，往往会出现主键冲突的情况。若要解决这类问题，MySQL中提供了两种方式，分别为主键冲突更新和主键冲突替换。

主键冲突更新
当插入数据的过程中若发生主键冲突，则插入数据操作利用更新的方式实现。
insert into mydb.my_goods(id,name,content,keyword)
values(20,‘橡皮’,‘修正书写错误’,‘文具’)
on duplicate key update name=‘橡皮’,content=‘修正书写错误’,
keyword=‘文具’;
查询插入的结果，即id=20的商品信息
select name,content,keyword from mydb.my_goods where id=20;

主键冲突替换
当插入数据的过程中若发生主键冲突，则删除此条记录，并重新插入。
replace into mydb.my_goods(id,name,content,keyword)
values(20,‘橡皮’,‘修正书写错误’,‘文具’);

两种方法都可以解决插入数据时主键冲突问题，但replace更适合插入数据字段特别多的情况。

3.清空数据
truncate table mydb.my_goods;

4.去除重复记录
实际应用中，出于对数据的分析需求，有时需要去除查询记录中重复的数据，可以使用select语句的选项。
select keyword from sh_goods;
select distinct keyword from sh_goods;


二、排序与限量
1.排序
单字排序
查询时仅按照一个指定字段进行升序或降序排列
select id,name,price from sh_goods order by price desc;
（查询到的商品显示顺序以价格为标准，从高到低依次显示。）
多字段排序
当在开发中需要根据多个条件需要对查询的数据进行排序时，可采用多字段排序。（其中asc表示升序，desc表示降序，order by默认值为asc）
select category_id,id,name,price from sh_goods
order by category_id,price desc;
（查询到的数据首先按category_id升序排序，相同category_id值得记录按照price字段降序排序）

2.限量
对于一次性查询出的大量记录，不仅不便于阅读查看，还会浪费系统效率。为此，mysql中提供了一个关键字limit，可以限定记录的数量，也可以指定查询从哪一条记录开始。
限制记录数。查询sh_goods表中价格最贵的商品。
select id,name,price from sh_goods
order by price desc limit 1;
获取指定区间的记录。获取指定区间的记录通常在项目开发中用于实现数据的分页展示，从而缓解网络和服务器的压力。例如，从第一条记录开始，获取5条商品记录，商品记录中包含id、name和price。
select id,name,price from sh_goods limit 0,5;
（0表示第一条记录的偏移量，5表示第一（偏移量+1）条记录开始最多获取5条记录。）

（参考《MySQL数据库原理、设计与应用》）