目录
数据复制操作
1、复制表结构
当我们已经有一个建好的表a,但是此时我们需要建立和表a相同结构的表b时,我们可以使用复制表结构的命令减少工作量。
//首先,我们需要创建一个数据表,我们将其命名为student
mysql> create table train(
-> number int(10) primary key not null,
-> name char(5)
-> );
//然后我们就可以使用复制表结构的命名,直接创建一个具有相同结构的新表
mysql> create table teacher like student;
2、复制已有表的表数据
当我们有两个表结构相同的工作表时,其中一个有数据,另一个没有数据,我们可以通过数据拷贝的命令将数据从一个工作表拷贝到另一个工作表。但是需要注意的是,这两个表的表结构必须要相同,否则就会报错。
//首先为一个数据表插入数据
mysql> insert into student values(001,'娜娜');
Query OK, 1 row affected (0.01 sec)
//查看插入数据是否成功
mysql> select * from student;
+--------+--------+
| number | name |
+--------+--------+
| 1 | 娜娜 |
+--------+--------+
1 row in set (0.00 sec)
//进行数据拷贝
mysql> insert into teacher select * from student;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
//查看数据是否拷贝成功
mysql> select * from teacher;
+--------+--------+
| number | name |
+--------+--------+
| 1 | 娜娜 |
+--------+--------+
1 row in set (0.00 sec)
3、解决插入数据时存在的主键冲突
当数据表中的主键具有唯一性时,我们插入已有的主键数据就会发生主键冲突,针对这种情况,我们有两种解决方案。
(1)主键冲突更新
添加字段 on duplicate key update
mysql> select * from teacher;
+--------+--------+
| number | name |
+--------+--------+
| 1 | 娜娜 |
+--------+--------+
1 row in set (0.00 sec)
mysql> insert into student values(001,'健哥')
-> on duplicate key update name = '健哥';
Query OK, 2 rows affected (0.01 sec)
mysql> select * from student;
+--------+--------+
| number | name |
+--------+--------+
| 1 | 鹏鹏 |
+--------+--------+
1 row in set (0.00 sec)
(2)主键冲突替换
使用replace命令,而非insert命令
mysql> select * from student;
+--------+--------+
| number | name |
+--------+--------+
| 1 | 鹏鹏 |
+--------+--------+
1 row in set (0.00 sec)
mysql> replace into teacher values(001,'健哥');
Query OK, 2 rows affected (0.01 sec)
mysql> select * from teacher;
+--------+--------+
| number | name |
+--------+--------+
| 1 | 鹏鹏 |
+--------+--------+
1 row in set (0.01 sec)
4、清空数据
删除数据除使用drop删除外,还可以使用truncate删除全部数据
//查看原有数据表中的内容
mysql> select * from teacher;
+--------+--------+
| number | name |
+--------+--------+
| 1 | 鹏鹏 |
+--------+--------+
1 row in set (0.01 sec)
//进行删除操作
mysql> truncate teacher;
Query OK, 0 rows affected (0.03 sec)
//查看删除是否成功
mysql> select * from teacher;
Empty set (0.01 sec)
5、去除重复记录(查询时,并没有真正删除记录)
使用关键字distinct
//查看正常查询时,数据表中的数据
mysql> select * from teacher;
+--------+--------+
| number | name |
+--------+--------+
| 1 | 鹏鹏 |
| 2 | 娜娜 |
| 3 | 霞霞 |
| 4 | 鹏鹏 |
+--------+--------+
4 rows in set (0.00 sec)
//查看使用非重复查询时,数据表中的数据
mysql> select distinct name from teacher;
+--------+
| name |
+--------+
| 鹏鹏 |
| 娜娜 |
| 霞霞 |
+--------+
3 rows in set (0.00 sec)
排序与限量
1、单字段排序
使用order by指定排序字段,使用asc或者desc指定升序或者是降序
//查看正常查询结果
mysql> select * from student;
+--------+--------+------+
| number | name | year |
+--------+--------+------+
| 1 | 丽丽 | 12 |
| 2 | 明明 | 14 |
+--------+--------+------+
2 rows in set (0.01 sec)
//查看使用指定排序之后的结果
mysql> select * from student order by year desc;
+--------+--------+------+
| number | name | year |
+--------+--------+------+
| 2 | 明明 | 14 |
| 1 | 丽丽 | 12 |
+--------+--------+------+
2 rows in set (0.00 sec)
2、多字段排序
在多字段排序中,字段之间用逗号隔开,并且第一个字段的优先级更高
//查看正常查询显示的结果
mysql> select * from student;
+--------+--------+------+-------+
| number | name | year | score |
+--------+--------+------+-------+
| 1 | 周周 | 12 | 89 |
| 2 | 可可 | 15 | 90 |
| 3 | 心心 | 14 | 98 |
+--------+--------+------+-------+
3 rows in set (0.00 sec)
//查看使用指定排序使用的结果
mysql> select * from student order by year asc,score desc;
+--------+--------+------+-------+
| number | name | year | score |
+--------+--------+------+-------+
| 1 | 周周 | 12 | 89 |
| 3 | 心心 | 14 | 98 |
| 2 | 可可 | 15 | 90 |
+--------+--------+------+-------+
3 rows in set (0.00 sec)
3、限量
限量的操作使用关键字limit完成,limit后面是显示的数据条数
mysql> select * from student order by year asc,score desc;
+--------+--------+------+-------+
| number | name | year | score |
+--------+--------+------+-------+
| 1 | 周周 | 12 | 89 |
| 3 | 心心 | 14 | 98 |
| 2 | 可可 | 15 | 90 |
+--------+--------+------+-------+
3 rows in set (0.00 sec)
mysql> select * from student order by year asc,score desc limit 2;
+--------+--------+------+-------+
| number | name | year | score |
+--------+--------+------+-------+
| 1 | 周周 | 12 | 89 |
| 3 | 心心 | 14 | 98 |
+--------+--------+------+-------+
2 rows in set (0.00 sec)
分组与聚合
1、分组统计
分组统计使用关键字group by完成。分组显示后只显示每组中的第一条数据
//查看正常查询内容
mysql> select * from student;
+--------+--------+------+-------+
| number | name | year | score |
+--------+--------+------+-------+
| 1 | 周周 | 12 | 89 |
| 2 | 可可 | 15 | 90 |
| 3 | 心心 | 14 | 98 |
| 4 | 燕燕 | 14 | 97 |
+--------+--------+------+-------+
4 rows in set (0.00 sec)
//查看分组查询内容
mysql> select * from student group by year;
+--------+--------+------+-------+
| number | name | year | score |
+--------+--------+------+-------+
| 1 | 周周 | 12 | 89 |
| 2 | 可可 | 15 | 90 |
| 3 | 心心 | 14 | 98 |
+--------+--------+------+-------+
3 rows in set (0.00 sec)
2、分组排序
分组排序是将分组查询关键字 group by和排序关键字 order by 结合使用。
//查看正常查询结果
mysql> select * from student;
+--------+--------+------+-------+
| number | name | year | score |
+--------+--------+------+-------+
| 1 | 周周 | 12 | 89 |
| 2 | 可可 | 15 | 90 |
| 3 | 心心 | 14 | 98 |
| 4 | 燕燕 | 14 | 97 |
| 5 | 月月 | 14 | 98 |
+--------+--------+------+-------+
5 rows in set (0.00 sec)
//查看分组排序后查询结果
mysql> select * from student group by year;
+--------+--------+------+-------+
| number | name | year | score |
+--------+--------+------+-------+
| 1 | 周周 | 12 | 89 |
| 2 | 可可 | 15 | 90 |
| 3 | 心心 | 14 | 98 |
+--------+--------+------+-------+
3 rows in set (0.00 sec)
3、回溯统计
回溯统计使用关键字 with rollup实现
4、统计筛选
使用where 和having实现,需要注意的是,having需要在where之后使用,不可以单独使用
mysql> select * from student
-> where year >=14
-> having score >90
-> ;
+--------+--------+------+-------+
| number | name | year | score |
+--------+--------+------+-------+
| 3 | 心心 | 14 | 98 |
| 4 | 燕燕 | 14 | 97 |
| 5 | 月月 | 14 | 98 |
+--------+--------+------+-------+
3 rows in set (0.00 sec)
5、使用别名
对于别名的使用,通过as关键字实现
mysql> select number as '编号',name as '姓名',year as '年龄',score as '成绩' from student;
+--------+--------+--------+--------+
| 编号 | 姓名 | 年龄 | 成绩 |
+--------+--------+--------+--------+
| 1 | 周周 | 12 | 89 |
| 2 | 可可 | 15 | 90 |
| 3 | 心心 | 14 | 98 |
| 4 | 燕燕 | 14 | 97 |
| 5 | 月月 | 14 | 98 |
+--------+--------+--------+--------+
5 rows in set (0.00 sec)
6、聚合函数
聚合函数常用于分组统计阶段,常用的分组统计有:
函数名 | 作用 |
count() | 返回参数字段的数量 |
sum() | 返回参数字段之和 |
avg() | 返回参数字段的平均值 |
max() | 返回参数字段的最大值 |
min() | 返回参数字段的最小值 |
mysql> select max(year) from student;
+-----------+
| max(year) |
+-----------+
| 15 |
+-----------+
1 row in set (0.00 sec)
好啦好啦,关于数据库的单表操作就到这里了,你学废了没有,下篇文章见,byebye!!