当前位置：首页 > news >正文

MySQL之表内容的增删改查(含oracel 9i经典测试雇佣表下载)

news 2024/11/15 21:22:34

一:Create

二:Retrieve

1.select列

2.where条件

3.结果排序

4. 筛选分页结果

三:Update

四:Delete

1.删除数据

2. 截断表

五：插入查询结果

六：聚合函数

七:group by子句的使用

表内容的CRUD操作 : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）

一:Create

(1)语法: insert into 表名 (列名) values (对应的列属性值)

(2)单行及多行插入

mysql> system clear;
mysql> create table student(-> id int unsigned primary key auto_increment,-> sn int unsigned not null unique key,-> name varchar(20) not null,-> qq varchar(20) unique key-> );
Query OK, 0 rows affected (0.03 sec)#单行指定列插入
mysql> insert into student (id,sn,name,qq) values (10,201,'刘备','230211');
Query OK, 1 row affected (0.01 sec)#全列插入
mysql> insert into student values (20,202,'关羽','230212');
Query OK, 1 row affected (0.00 sec)#单行指定列插入
mysql> insert into student (sn,name,qq) values (203,'张飞','230213');
Query OK, 1 row affected (0.01 sec)#多行指定列插入
mysql> insert into student (sn,name,qq) values (204,'赵云','230214'),(205,'黄忠','230215');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select* from student;
+----+-----+--------+--------+
| id | sn  | name   | qq     |
+----+-----+--------+--------+
| 10 | 201 | 刘备   | 230211 |
| 20 | 202 | 关羽   | 230212 |
| 21 | 203 | 张飞   | 230213 |
| 22 | 204 | 赵云   | 230214 |
| 23 | 205 | 黄忠   | 230215 |
+----+-----+--------+--------+

(3) 插入否则更新

由于主键或者唯一键对应的值已经存在而导致插入失败

#主键冲突
mysql> insert into student (id,sn,name,qq) values (10,206,'马超','230216');
ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'
#唯一键冲突
mysql> insert into student (id,sn,name,qq) values (24,205,'马超','230216');
ERROR 1062 (23000): Duplicate entry '205' for key 'sn'

插入替换1：不存在则直接插入，存在的话修改下里面某一列的数据

mysql> select* from student;
+----+-----+--------+--------+
| id | sn  | name   | qq     |
+----+-----+--------+--------+
| 10 | 201 | 刘备   | 230211 |
| 20 | 202 | 关羽   | 230212 |
| 21 | 203 | 张飞   | 230213 |
| 22 | 204 | 赵云   | 230214 |
| 23 | 205 | 黄忠   | 230215 |
+----+-----+--------+--------+
5 rows in set (0.00 sec)mysql> insert into student values (23,205,'huangzhong','230216') on duplicate key update name='huanghzong',qq='230216';
Query OK, 2 rows affected (0.00 sec)mysql> select* from student;
+----+-----+------------+--------+
| id | sn  | name       | qq     |
+----+-----+------------+--------+
| 10 | 201 | 刘备       | 230211 |
| 20 | 202 | 关羽       | 230212 |
| 21 | 203 | 张飞       | 230213 |
| 22 | 204 | 赵云       | 230214 |
| 23 | 205 | huanghzong | 230216 |
+----+-----+------------+--------+
5 rows in set (0.00 sec)mysql> insert into student values (24,207,'马超','230217') on duplicate key update name='马超';
Query OK, 1 row affected (0.01 sec)mysql> select* from student;
+----+-----+------------+--------+
| id | sn  | name       | qq     |
+----+-----+------------+--------+
| 10 | 201 | 刘备       | 230211 |
| 20 | 202 | 关羽       | 230212 |
| 21 | 203 | 张飞       | 230213 |
| 22 | 204 | 赵云       | 230214 |
| 23 | 205 | huanghzong | 230216 |
| 24 | 207 | 马超       | 230217 |
+----+-----+------------+--------+
6 rows in set (0.00 sec)mysql> insert into student values (10,201,'刘备','230211') on duplicate key update name='刘备',qq='230211';
Query OK, 0 rows affected (0.00 sec)-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，并且数据已经被更新
-- 通过 MySQL 函数获取受到影响的数据行数
SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|      2 |
+-------------+

要保证更新后的数据不能与其它主键,唯一键冲突

插入替换2：

replace into 表名 (列名) values (对应列名属性值)

-- 主键 或者 唯一键 没有冲突，则直接插入；
-- 主键 或者 唯一键 如果冲突，则删除后再插入mysql> replace into student values (24,207,'machao','230217');
Query OK, 2 rows affected (0.00 sec)mysql> select* from student;
+----+-----+------------+--------+
| id | sn  | name       | qq     |
+----+-----+------------+--------+
| 10 | 201 | 刘备       | 230211 |
| 20 | 202 | 关羽       | 230212 |
| 21 | 203 | 张飞       | 230213 |
| 22 | 204 | 赵云       | 230214 |
| 23 | 205 | huanghzong | 230216 |
| 24 | 207 | machao     | 230217 |
+----+-----+------------+--------+-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入

二:Retrieve

1.select列

(1) 全列查询 select* from 表名

通常情况下不建议使用 * 进行全列查询
-- 1. 查询的列越多，意味着需要传输的数据量越大；
-- 2. 可能会影响到索引的使用

(2)指定列查询

mysql> select id,name from student;
+----+------------+
| id | name       |
+----+------------+
| 10 | 刘备       |
| 20 | 关羽       |
| 21 | 张飞       |
| 22 | 赵云       |
| 23 | huanghzong |
| 24 | machao     |
+----+------------+

(3) 查询字段为表达式

mysql> select* from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   78 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
|  7 | 宋公明    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)mysql> select id,name,math+10 from exam_result;
+----+-----------+---------+
| id | name      | math+10 |
+----+-----------+---------+
|  1 | 唐三藏    |     108 |
|  2 | 孙悟空    |      88 |
|  3 | 猪悟能    |     108 |
|  4 | 曹孟德    |      94 |
|  5 | 刘玄德    |      95 |
|  6 | 孙权      |      83 |
|  7 | 宋公明    |      75 |
+----+-----------+---------+
7 rows in set (0.00 sec)//查询结果取别名，也可以不加as
mysql> select id,name,chinese+math+english as total from exam_result;
+----+-----------+-------+
| id | name      | total |
+----+-----------+-------+
|  1 | 唐三藏    |   221 |
|  2 | 孙悟空    |   242 |
|  3 | 猪悟能    |   276 |
|  4 | 曹孟德    |   233 |
|  5 | 刘玄德    |   185 |
|  6 | 孙权      |   221 |
|  7 | 宋公明    |   170 |
+----+-----------+-------+

(4) 结果去重

mysql> select math from exam_result;
+------+
| math |
+------+
|   98 |
|   78 |
|   98 |
|   84 |
|   85 |
|   73 |
|   65 |
+------+
7 rows in set (0.00 sec)mysql> select distinct math from exam_result;
+------+
| math |
+------+
|   98 |
|   78 |
|   84 |
|   85 |
|   73 |
|   65 |
+------+

2.where条件

比较运算符：

逻辑运算符：and or not

举例:

mysql> select* from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   78 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
|  7 | 宋公明    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+

(1)英语不及格的同学及英语成绩 ( < 60 )

mysql> select id,name,english from exam_result where english<60;
+----+-----------+---------+
| id | name      | english |
+----+-----------+---------+
|  1 | 唐三藏    |      56 |
|  5 | 刘玄德    |      45 |
|  7 | 宋公明    |      30 |
+----+-----------+---------+

(2)语文成绩在 [80, 90] 分的同学及语文成绩

mysql> select name,chinese from exam_result where chinese>=80 and chinese<=90;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
3 rows in set (0.00 sec)mysql> select name,chinese from exam_result where chinese between 80 and 90;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+

(3)数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

mysql> select name,math from exam_result where math=58 or math=59 or math=98 or math=99;
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.00 sec)mysql> select name,math from exam_result where math in(58,59,98,99);
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+

(4)姓孙的同学及孙某同学

#-- % 匹配任意多个（包括 0 个）任意字符
mysql> select name from exam_result where name like '孙%';
+-----------+
| name      |
+-----------+
| 孙悟空    |
| 孙权      |
+-----------+
2 rows in set (0.00 sec)#-- _ 匹配严格的一个任意字符
mysql> select name from exam_result where name like '孙_';
+--------+
| name   |
+--------+
| 孙权   |
+--------+

(5) 语文成绩好于英语成绩的同学

mysql> select name,chinese,english from exam_result where chinese>english;
+-----------+---------+---------+
| name      | chinese | english |
+-----------+---------+---------+
| 唐三藏    |      67 |      56 |
| 孙悟空    |      87 |      77 |
| 曹孟德    |      82 |      67 |
| 刘玄德    |      55 |      45 |
| 宋公明    |      75 |      30 |
+-----------+---------+---------+

(6)总分在 200 分以下的同学

mysql> select name,chinese+math+english total from exam_result where chinese+english+math<200;
+-----------+-------+
| name      | total |
+-----------+-------+
| 刘玄德    |   185 |
| 宋公明    |   170 |
+-----------+-------+#这里不可以使用前面起的别名在where子句中做判断
#这里有执行顺序的:from语句 ->  where语句 ->select语句
mysql> select name,chinese+math+english total from exam_result where total<200;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'

(7)语文成绩 > 80 并且不姓孙的同学

mysql> select name,chinese from exam_result where chinese>80 and (name not like '孙%');
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+

(8) 孙某同学，否则要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80

mysql> select name,chinese+math+english total from exam_result where(chinese+english+math>200 and math>chinese and english>80) or name like '孙_';
+-----------+-------+
| name      | total |
+-----------+-------+
| 猪悟能    |   276 |
| 孙权      |   221 |
+-----------+-------+

(9)NULL查询

SELECT name, qq FROM students WHERE qq IS NOT NULL;
+-----------+-------+
| name | qq |
+-----------+-------+
| 孙悟空 | 11111 |
+-----------+-------+
1 row in set (0.00 sec)-- NULL 和 NULL 的比较，= 和 <=> 的区别
SELECT NULL = NULL, NULL = 1, NULL = 0;
+-------------+----------+----------+
| NULL = NULL | NULL = 1 | NULL = 0 |
+-------------+----------+----------+
| NULL | NULL | NULL |
+-------------+----------+----------+
1 row in set (0.00 sec)SELECT NULL <=> NULL, NULL <=> 1, NULL <=> 0;
+---------------+------------+------------+
| NULL <=> NULL | NULL <=> 1 | NULL <=> 0 |
+---------------+------------+------------+
| 1 | 0 | 0 |
+---------------+------------+------------+

3.结果排序

语法:

-- asc 为升序（从小到大）
-- desc 为降序（从大到小）
-- 默认为 ascselect ... from... order by 列名 (asc/desc),...

(1)同学及数学成绩，按数学成绩升序显示

mysql> select name,math from exam_result order by math asc;
+-----------+------+
| name      | math |
+-----------+------+
| 宋公明    |   65 |
| 孙权      |   73 |
| 孙悟空    |   78 |
| 曹孟德    |   84 |
| 刘玄德    |   85 |
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+

(2) 同学及 qq 号，按 qq 号排序显示

-- NULL 视为比任何值都小，升序出现在最上面select name,qq from students order by qq;
+-----------+-------+
| name | qq |
+-----------+-------+
| 唐大师 | NULL |
| 孙仲谋 | NULL |
| 曹阿瞒 | NULL |
| 孙悟空 | 11111 |

(3) 查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

mysql> select name,math,chinese,english from exam_result order by math desc, english asc,chinese asc;
+-----------+------+---------+---------+
| name      | math | chinese | english |
+-----------+------+---------+---------+
| 唐三藏    |   98 |      67 |      56 |
| 猪悟能    |   98 |      88 |      90 |
| 刘玄德    |   85 |      55 |      45 |
| 曹孟德    |   84 |      82 |      67 |
| 孙悟空    |   78 |      87 |      77 |
| 孙权      |   73 |      70 |      78 |
| 宋公明    |   65 |      75 |      30 |
+-----------+------+---------+---------+

(4)查询同学及总分，由高到低

-- ORDER BY 子句中可以使用列别名
#语句的执行顺序为: form语句 -> where语句 -> select语句 -> order by语句
mysql> select name,chinese+english+math total from exam_result order by total desc;
+-----------+-------+
| name      | total |
+-----------+-------+
| 猪悟能    |   276 |
| 孙悟空    |   242 |
| 曹孟德    |   233 |
| 唐三藏    |   221 |
| 孙权      |   221 |
| 刘玄德    |   185 |
| 宋公明    |   170 |
+-----------+-------+

(5) 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

mysql> select name,math from exam_result where name like '孙%' or name like '曹%' order by math desc;
+-----------+------+
| name      | math |
+-----------+------+
| 曹孟德    |   84 |
| 孙悟空    |   78 |
| 孙权      |   73 |
+-----------+------+

4. 筛选分页结果

语法：

-- 起始下标为 0-- 从 0 开始，筛选 n 条结果
select ... from table_name [where ...] [order by ...] limit n;-- 从 s 开始，筛选 n 条结果
select ... from table_name [where ...] [order by ...] limit s, n;-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
select ... from table_name [where ...] [order by ...] limit n offset s;

mysql> select id,name,chinese,math,english from exam_result limit 3 offset 0;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   78 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)mysql> select id,name,chinese,math,english from exam_result limit 3 offset 3;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)mysql> select id,name,chinese,math,english from exam_result limit 3 offset 6;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  7 | 宋公明    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+

建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死
按 id 进行分页，每页 3 条记录，分别显示第 1、2、3 页

三:Update

语法: update 表名 set 列名=列属性值 where 匹配条件 (order by ..) (limit ...)

对查询到的结果进行列值更新

举例:

(1)将孙悟空同学的数学成绩变更为 80 分

mysql> select* from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   78 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
|  7 | 宋公明    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)mysql> update exam_result set math=80 where name='孙悟空';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select name,math from exam_result where name='孙悟空';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   80 |
+-----------+------+

(2)将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

mysql> select name,math,chinese from exam_result where name='曹孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    |   84 |      82 |
+-----------+------+---------+
1 row in set (0.00 sec)mysql> update exam_result set math=60,chinese=70 where name='曹孟德';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select name,math,chinese from exam_result where name='曹孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    |   60 |      70 |
+-----------+------+---------+

(3)将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

mysql> select name,chinese+math+english total from exam_result order by total asc limit 3;
+-----------+-------+
| name      | total |
+-----------+-------+
| 宋公明    |   170 |
| 刘玄德    |   185 |
| 曹孟德    |   197 |
+-----------+-------+
3 rows in set (0.00 sec)mysql> update exam_result set math=math+30 order by math+chinese+english asc limit 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0mysql> select name,chinese+math+english total from exam_result order by total asc limit 3;
+-----------+-------+
| name      | total |
+-----------+-------+
| 宋公明    |   200 |
| 刘玄德    |   215 |
| 唐三藏    |   221 |
+-----------+-------+

(4) 将所有同学的语文成绩更新为原来的 2 倍

-- 没有 WHERE 子句，则更新全表mysql> select name,chinese from exam_result;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 唐三藏    |      67 |
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      70 |
| 刘玄德    |      55 |
| 孙权      |      70 |
| 宋公明    |      75 |
+-----------+---------+
7 rows in set (0.00 sec)mysql> update exam_result set chinese=chinese*2;
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0mysql> select name,chinese from exam_result;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 唐三藏    |     134 |
| 孙悟空    |     174 |
| 猪悟能    |     176 |
| 曹孟德    |     140 |
| 刘玄德    |     110 |
| 孙权      |     140 |
| 宋公明    |     150 |
+-----------+---------+

四:Delete

1.删除数据

语法: delete from 表名 where 匹配条件 (order by...) (limit ...)

举例： (1) 删除孙悟空同学的考试成绩

mysql> select* from exam_result where name='孙悟空';
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  2 | 孙悟空    |     174 |   80 |      77 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)mysql> delete from exam_result where name='孙悟空';
Query OK, 1 row affected (0.01 sec)mysql> select* from exam_result where name='孙悟空';
Empty set (0.00 sec)

(2)删除整张表数据

mysql> CREATE TABLE for_delete (-> id INT PRIMARY KEY AUTO_INCREMENT,-> name VARCHAR(20)-> );
Query OK, 0 rows affected (0.02 sec)mysql> INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> select* from for_delete;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.00 sec)mysql> show create table for_delete\G;
*************************** 1. row ***************************Table: for_delete
Create Table: CREATE TABLE `for_delete` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)#删除整表数据
mysql> delete from for_delete;
Query OK, 3 rows affected (0.01 sec)#再插入一条数据，自增 id 在原值上增长
mysql> insert into for_delete (name) values ('W');
Query OK, 1 row affected (0.00 sec)mysql> select* from for_delete;
+----+------+
| id | name |
+----+------+
|  4 | W    |
+----+------+
1 row in set (0.00 sec)mysql> show create table for_delete \G;
*************************** 1. row ***************************Table: for_delete
Create Table: CREATE TABLE `for_delete` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

这里是不会将自增序列对应的值置为0的

2. 截断表

语法:truncate (table) 表名

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚
3. 会重置 AUTO_INCREMENT 项

4.作用也是会将表的数据内容清空

mysql> CREATE TABLE for_truncate (-> id INT PRIMARY KEY AUTO_INCREMENT,-> name VARCHAR(20)-> );
Query OK, 0 rows affected (0.02 sec)mysql> INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> select* from for_truncate;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.00 sec)-- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作
mysql> truncate table for_truncate;
Query OK, 0 rows affected (0.01 sec)mysql> select* from for_truncate;
Empty set (0.00 sec)-- 再插入一条数据，自增 id 在重新增长
mysql> insert into for_truncate (name) values ('Q');
Query OK, 1 row affected (0.00 sec)mysql> select* from for_truncate;
+----+------+
| id | name |
+----+------+
|  1 | Q    |
+----+------+

五：插入查询结果

语法: insert into table_name [(column [, column ...])] select ...

举例:删除表中的的重复复记录，重复的数据只能有一份

mysql> CREATE TABLE duplicate_table (id int, name varchar(20));
Query OK, 0 rows affected (0.02 sec)mysql> INSERT INTO duplicate_table VALUES-> (100, 'aaa'),-> (100, 'aaa'),-> (200, 'bbb'),-> (200, 'bbb'),-> (200, 'bbb'),-> (300, 'ccc');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0# 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样
mysql> create table no_duplicate_table like duplicate_table;
Query OK, 0 rows affected (0.02 sec)# 将duplicate_table 的去重数据插入到 no_duplicate_table
mysql> INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0# 通过重命名表，实现原子的去重操作
mysql> RENAME TABLE duplicate_table TO old_duplicate_table,-> no_duplicate_table TO duplicate_table;
Query OK, 0 rows affected (0.02 sec)mysql> SELECT * FROM duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+

六：聚合函数

举例:

(1)统计班级共有多少同学

mysql> select* from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |     134 |   98 |      56 |
|  3 | 猪悟能    |     176 |   98 |      90 |
|  4 | 曹孟德    |     140 |   90 |      67 |
|  5 | 刘玄德    |     110 |  115 |      45 |
|  6 | 孙权      |     140 |   73 |      78 |
|  7 | 宋公明    |     150 |   95 |      30 |
+----+-----------+---------+------+---------+
6 rows in set (0.00 sec)mysql> select count(*) from exam_result;
+----------+
| count(*) |
+----------+
|        6 |
+----------+

(2)统计班级收集的id 号有多少

mysql> select count(id) from exam_result;
+-----------+
| count(id) |
+-----------+
|         6 |
+-----------+

(3)统计本次考试的数学成绩分数个数

mysql> select count(math) from exam_result;
+-------------+
| count(math) |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)//统计去重成绩数量
mysql> select count( distinct math) from exam_result;
+-----------------------+
| count( distinct math) |
+-----------------------+
|                     5 |
+-----------------------+

(4)统计数学成绩总分

mysql> select sum(math) from exam_result;
+-----------+
| sum(math) |
+-----------+
|       569 |
+-----------+

(5)统计平均分

mysql> select avg(chinese+math+english) total from exam_result;
+-------+
| total |
+-------+
| 297.5 |
+-------+

(6)得到英语最高分

mysql> select max(english) from exam_result;
+--------------+
| max(english) |
+--------------+
|           90 |
+--------------+

(7)返回 > 70 分以上的数学最低分

mysql> select math from exam_result where math>70;
+------+
| math |
+------+
|   98 |
|   98 |
|   90 |
|  115 |
|   73 |
|   95 |
+------+
6 rows in set (0.00 sec)mysql> select  min(math) from exam_result where math>70;
+-----------+
| min(math) |
+-----------+
|        73 |
+-----------+

七:group by子句的使用

在select中使用group by 子句可以对指定列进行分组查询
语法：select column1, column2, .. from table group by column;

分组的目的是为了进行分组之后，方便进行聚合统计。分组实质就是把一组按照条件拆成了多个组，进行各自组内的统计,也可以理解为是把一张表按照条件在逻辑上拆成了多个子表，然后分别对各自的子表进行聚合统计

这里指定列名，是在分组时用该列的不同行数据来进行分组的，同组可以聚合压缩

举例：

准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）
EMP员工表
DEPT部门表
SALGRADE工资等级表

经典测试雇佣表

选择一个文件目录，左键在终端中打开

将复制后的指令在终端框中执行，这样在该文件目录下就会生成对应的文件

接下来进入Linux用户的家目录，进行rz上传这个文件

最后在mysql中soruce 文件目录导入表

[root@hcss-ecs-889f MySQL]# cd ~
[root@hcss-ecs-889f ~]# ll
total 39092
-rw-r--r--  1 root root        0 May 14 22:49 test1.c
[root@hcss-ecs-889f ~]# rz[root@hcss-ecs-889f ~]# ll
total 39096
-rw-r--r--  1 root root     3878 Sep 17 11:03 scott_data.sql
-rw-r--r--  1 root root        0 May 14 22:49 test1.c
[root@hcss-ecs-889f ~]# pwd
/root[root@hcss-ecs-889f ~]# mysql -uroot -p;mysql> use d2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> source /root/scott_data.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)Query OK, 1 row affected (0.00 sec)mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| salgrade        |
+-----------------+

(1)显示每个部门的平均工资和最高工资

select deptno,avg(sal),max(sal) from EMP group by deptno;

(2)显示每个部门的每种岗位的平均工资和最低工资

select avg(sal),min(sal),job, deptno from EMP group by deptno, job;

(3)显示平均工资低于2000的部门和它的平均工资

#统计各个部门的平均工资
select avg(sal) from EMP group by deptno;#having和group by配合使用，对group by结果进行过滤，条件筛选
select avg(sal) as myavg from EMP group by deptno having myavg<2000;#having经常和group by搭配使用，作用是对分组聚合之后的结果进行条件筛选，
#where是对具体的任意列进行条件筛选,两者条件筛选的阶段不同

查看全文

http://www.mrgr.cn/news/28699.html