wordpress theme sage,广州网站优化网站,库尔勒网站建设电话,什么是网络营销策略在前面几章的内容中我们学习了数据库的增删查改#xff0c;表的增删查改#xff0c;这一篇我们来学习一下对表中的内容做增删查改。
CRUD : Create(创建), Retrieve(读取)#xff0c;Update(更新)#xff0c;Delete#xff08;删除#xff09; 1.创建Create
我们先创建…在前面几章的内容中我们学习了数据库的增删查改表的增删查改这一篇我们来学习一下对表中的内容做增删查改。
CRUD : Create(创建), Retrieve(读取)Update(更新)Delete删除 1.创建Create
我们先创建一张表
mysql create table student(- id int unsigned primary key auto_increment,- sn int unique comment 学号,- name varchar(20) not null,- qq varchar(20)- );
Query OK, 0 rows affected (0.01 sec)1.1 单行插入
全列插入
mysql insert into student values(100, 10000, 张三, null);
Query OK, 1 row affected (0.00 sec)mysql insert into student values(101, 10001, 李四, null);
Query OK, 1 row affected (0.00 sec)mysql select * from student;
--------------------------
| id | sn | name | qq |
--------------------------
| 100 | 10000 | 张三 | NULL |
| 101 | 10001 | 李四 | NULL |
--------------------------
2 rows in set (0.00 sec)这里的into是可以省略的。 指定列插入
数量必须和指定列数量及顺序一致
mysql insert into student(sn, name) values(10002, 王五);
Query OK, 1 row affected (0.00 sec)mysql insert into student(sn, name) values(10003, 赵六);
Query OK, 1 row affected (0.00 sec)mysql select * from student;
--------------------------
| id | sn | name | qq |
--------------------------
| 100 | 10000 | 张三 | NULL |
| 101 | 10001 | 李四 | NULL |
| 102 | 10002 | 王五 | NULL |
| 103 | 10003 | 赵六 | NULL |
--------------------------
4 rows in set (0.00 sec)1.2 多行插入
mysql insert into student(id, sn, name) values
mysql (120, 10004, 曹操),
mysql (121, 10005, 孙权);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql select * from student;
--------------------------
| id | sn | name | qq |
--------------------------
| 100 | 10000 | 张三 | NULL |
| 101 | 10001 | 李四 | NULL |
| 102 | 10002 | 王五 | NULL |
| 103 | 10003 | 赵六 | NULL |
| 120 | 10004 | 曹操 | NULL |
| 121 | 10005 | 孙权 | NULL |
--------------------------
6 rows in set (0.00 sec)1.3 插入否则更新
由于 主键 或者 唯一键 对应的值已经存在而导致插入失败
mysql insert into student (id, sn, name) values (100, 10010, 张飞);
ERROR 1062 (23000): Duplicate entry 100 for key PRIMARYmysql insert into student(sn, name) values (10004, 曹阿瞒);
ERROR 1062 (23000): Duplicate entry 10004 for key sn第一个是主键冲突第二个是唯一键冲突。
如果我们想插入新的数据旧的数据不再关心如果没有直接插入如果有就更新并且不会插入失败那我们可以使用插入否则更新
insert... on duplicate key update 要更新的数据
mysql insert into student (id, sn, name) values (100, 10010, 张飞) on duplicate key update sn10083, name张飞;
Query OK, 2 rows affected (0.00 sec)mysql insert into student(sn, name) values (10004, 曹阿瞒) on duplicate key update sn10056, name曹阿瞒;
Query OK, 1 row affected (0.00 sec)mysql select * from student;
-----------------------------
| id | sn | name | qq |
-----------------------------
| 100 | 10083 | 张飞 | NULL |
| 101 | 10001 | 李四 | NULL |
| 102 | 10002 | 王五 | NULL |
| 103 | 10003 | 赵六 | NULL |
| 120 | 10012 | 曹操 | NULL |
| 121 | 10005 | 孙权 | NULL |
| 124 | 10004 | 曹阿瞒 | NULL |
-----------------------------
7 rows in set (0.00 sec)-- 0 row affected: 表中有冲突数据但冲突数据的值和 update 的值相等
-- 1 row affected: 表中没有冲突数据数据被插入
-- 2 row affected: 表中有冲突数据并且数据已经被更新1.4替换 替换的原理是 如果主键或者唯一键没有发生冲突则直接插入 如果主键或者唯一键发生了冲突则删除后再次插入 mysql select * from student;
-----------------------------
| id | sn | name | qq |
-----------------------------
| 100 | 10083 | 张飞 | NULL |
| 101 | 10001 | 李四 | NULL |
| 102 | 10002 | 王五 | NULL |
| 103 | 10003 | 赵六 | NULL |
| 120 | 10012 | 曹操 | NULL |
| 121 | 10005 | 孙权 | NULL |
| 124 | 10004 | 曹阿瞒 | NULL |
-----------------------------
7 rows in set (0.00 sec)mysql replace into student values(128, 10013, 孙策, null);
Query OK, 1 row affected (0.00 sec)mysql replace into student values(124, 10014, 曹操, null);
Query OK, 2 rows affected (0.00 sec)mysql select * from student;
--------------------------
| id | sn | name | qq |
--------------------------
| 100 | 10083 | 张飞 | NULL |
| 101 | 10001 | 李四 | NULL |
| 102 | 10002 | 王五 | NULL |
| 103 | 10003 | 赵六 | NULL |
| 120 | 10012 | 曹操 | NULL |
| 121 | 10005 | 孙权 | NULL |
| 124 | 10014 | 曹操 | NULL |
| 128 | 10013 | 孙策 | NULL |
--------------------------
8 rows in set (0.00 sec)2.读取Retrieve
我们先创建一个表并插入一些数据
mysql create table exam_result (- id int unsigned primary key auto_increment,- name varchar(20) not null comment 同学姓名,- chinese float default 0.0 comment 语文成绩,- math float default 0.0 comment 数学成绩,- english float default 0.0 comment 英语成绩- );
Query OK, 0 rows affected (0.01 sec)mysql insert into exam_result (name, chinese, math, english) values- (唐三藏, 67, 98, 56),- (孙悟空, 87, 78, 77),- (猪悟能, 88, 98, 90),- (曹孟德, 82, 84, 67),- (刘玄德, 55, 85, 45),- (孙权, 70, 73, 78),- (宋公明, 75, 65, 30);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 02.1 select 查询
2.1.1全列查询 通常情况下不建议使用 * 进行全列查询 1. 查询的列越多意味着需要传输的数据量越大 2. 可能会影响到索引的使用。索引待后面课程讲解 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)2.1.2 指定列查询
mysql select id, name, english from exam_result;
------------------------
| id | name | english |
------------------------
| 1 | 唐三藏 | 56 |
| 2 | 孙悟空 | 77 |
| 3 | 猪悟能 | 90 |
| 4 | 曹孟德 | 67 |
| 5 | 刘玄德 | 45 |
| 6 | 孙权 | 78 |
| 7 | 宋公明 | 30 |
------------------------
7 rows in set (0.00 sec)2.1.3 查询字段为表达式
查询字段也可以为一个表达式。
mysql select id, name, chinesemathenglish from exam_result;\
-------------------------------------
| id | name | chinesemathenglish |
-------------------------------------
| 1 | 唐三藏 | 221 |
| 2 | 孙悟空 | 242 |
| 3 | 猪悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 刘玄德 | 185 |
| 6 | 孙权 | 221 |
| 7 | 宋公明 | 170 |
-------------------------------------
7 rows in set (0.00 sec)2.1.4 为查询结果指定别名
mysql select id, name, chinesemathenglish 总分 from exam_result;
-----------------------
| id | name | 总分 |
-----------------------
| 1 | 唐三藏 | 221 |
| 2 | 孙悟空 | 242 |
| 3 | 猪悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 刘玄德 | 185 |
| 6 | 孙权 | 221 |
| 7 | 宋公明 | 170 |
-----------------------
7 rows in set (0.00 sec)2.1.5 结果去重distinct
mysql select * from num;
------
| a |
------
| 10 |
| 10 |
| 10 |
| 10 |
| 20 |
| 20 |
------
6 rows in set (0.00 sec)mysql select distinct a from num;
------
| a |
------
| 10 |
| 20 |
------
2 rows in set (0.00 sec) 2.2where条件判断
比较运算符 逻辑运算符 案例
1.英语不及格的同学及英语成绩 ( 60 )
select name, english from exam_result where english 60;
-------------------
| name | english|
-------------------
| 唐三藏 | 56 |
| 刘玄德 | 45 |
| 宋公明 | 30 |
-------------------
3 rows in set (0.01 sec) 2.语文成绩在 [80, 90] 分的同学及语文成绩
方法一 使用 AND 进行条件连接
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)方法二 使用 BETWEEN ... AND ... 条件
mysql select name, chinese from exam_result where chinese between 80 and 90;
--------------------
| name | chinese |
--------------------
| 孙悟空 | 87 |
| 猪悟能 | 88 |
| 曹孟德 | 82 |
--------------------
3 rows in set (0.00 sec)3.数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
方法一 使用 OR 进行条件连接
mysql select name, math from exam_result where math58 or math59 or math98 or math99;
-----------------
| name | math |
-----------------
| 唐三藏 | 98 |
| 猪悟能 | 98 |
-----------------
2 rows in set (0.00 sec)
方法二 使用 IN 条件
mysql select name, math from exam_result where math in (58,59,98,99);
-----------------
| name | math |
-----------------
| 唐三藏 | 98 |
| 猪悟能 | 98 |
-----------------
2 rows in set (0.00 sec)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 |
--------
| 孙权 |
--------
1 row in set (0.00 sec) 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 |
-----------------------------
5 rows in set (0.00 sec)6.总分在 200 分以下的同学
mysql select name, chinese, math, english, chinesemathenglish total from exam_result where total 200;
ERROR 1054 (42S22): Unknown column total in where clause别名在where语句中是无法使用的。 主要还是执行顺序不同。 1.执行from 2.执行where语句 3.执行要打印的信息 而别名total是出现在第三步要打印的信息里面的第二步的时候还没有出现所以在where中不能使用total
mysql select name, chinese, math, english, chinesemathenglish total from exam_result where chinesemathenglish 200;
------------------------------------------
| name | chinese | math | english | total |
------------------------------------------
| 刘玄德 | 55 | 85 | 45 | 185 |
| 宋公明 | 75 | 65 | 30 | 170 |
------------------------------------------
2 rows in set (0.00 sec)7.语文成绩 80 并且不姓孙的同学
mysql select name, chinese from exam_result where name not like 孙% and chinese 80;
--------------------
| name | chinese |
--------------------
| 猪悟能 | 88 |
| 曹孟德 | 82 |
--------------------
2 rows in set (0.00 sec)8.NULL 的查询
查询表中qq号不为空的同学
错误示范
mysql select * from exam_result;
-----------------------------------------------
| id | name | chinese | math | english | qq |
-----------------------------------------------
| 1 | 唐三藏 | 67 | 98 | 56 | NULL |
| 2 | 孙悟空 | 87 | 78 | 77 | NULL |
| 3 | 猪悟能 | 88 | 98 | 90 | NULL |
| 4 | 曹孟德 | 82 | 84 | 67 | NULL |
| 5 | 刘玄德 | 55 | 85 | 45 | NULL |
| 6 | 孙权 | 70 | 73 | 78 | NULL |
| 7 | 宋公明 | 75 | 65 | 30 | NULL |
| 8 | 张翼德 | 34 | 84 | 48 | 11111 |
| 9 | 关云长 | 75 | 31 | 84 | 222222 |
-----------------------------------------------
9 rows in set (0.00 sec)mysql select name, qq from exam_result where qq ! null;
Empty set (0.00 sec)可以看到直接使用!这种方式是是错误的因为null是不会参与运算的。where后面的结果就是null。
正确写法
mysql select name, qq from exam_result where qq is not null;
-------------------
| name | qq |
-------------------
| 张翼德 | 11111 |
| 关云长 | 222222 |
-------------------
2 rows in set (0.00 sec)那如果是查询表中qq号为空的同学呢
方法一
mysql select name, qq from exam_result where qq null;
-----------------
| name | qq |
-----------------
| 唐三藏 | NULL |
| 孙悟空 | NULL |
| 猪悟能 | NULL |
| 曹孟德 | NULL |
| 刘玄德 | NULL |
| 孙权 | NULL |
| 宋公明 | NULL |
-----------------
7 rows in set (0.00 sec)方法二 is
mysql select name, qq from exam_result where qq is null;
-----------------
| name | qq |
-----------------
| 唐三藏 | NULL |
| 孙悟空 | NULL |
| 猪悟能 | NULL |
| 曹孟德 | NULL |
| 刘玄德 | NULL |
| 孙权 | NULL |
| 宋公明 | NULL |
-----------------
7 rows in set (0.00 sec)方法三 is not
mysql select name, qq from exam_result where !(qq is not null);
-----------------
| name | qq |
-----------------
| 唐三藏 | NULL |
| 孙悟空 | NULL |
| 猪悟能 | NULL |
| 曹孟德 | NULL |
| 刘玄德 | NULL |
| 孙权 | NULL |
| 宋公明 | NULL |
-----------------
7 rows 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 |
---------------------------------------
1 row in set (0.00 sec) 2.3 order by排序
注意没有 ORDER BY 子句的查询返回的顺序是未定义的永远不要依赖这个顺序 ASC为升序从小到大 DESC为降序从大到小 默认为ASC 同学及数学成绩按数学成绩升序显示
mysql select * from exam_result order by math;
-----------------------------------------------
| id | name | chinese | math | english | qq |
-----------------------------------------------
| 9 | 关云长 | 75 | 31 | 84 | 222222 |
| 7 | 宋公明 | 75 | 65 | 30 | NULL |
| 6 | 孙权 | 70 | 73 | 78 | NULL |
| 2 | 孙悟空 | 87 | 78 | 77 | NULL |
| 4 | 曹孟德 | 82 | 84 | 67 | NULL |
| 8 | 张翼德 | 34 | 84 | 48 | 11111 |
| 5 | 刘玄德 | 55 | 85 | 45 | NULL |
| 1 | 唐三藏 | 67 | 98 | 56 | NULL |
| 3 | 猪悟能 | 88 | 98 | 90 | NULL |
-----------------------------------------------
9 rows in set (0.00 sec)同学及 qq 号按 qq 号排序显示
mysql select name, qq from exam_result order by qq desc;
-------------------
| name | qq |
-------------------
| 关云长 | 222222 |
| 张翼德 | 11111 |
| 唐三藏 | NULL |
| 孙悟空 | NULL |
| 猪悟能 | NULL |
| 曹孟德 | NULL |
| 刘玄德 | NULL |
| 孙权 | NULL |
| 宋公明 | NULL |
-------------------
9 rows in set (0.00 sec)NULL 视为比任何值都小降序出现在最下面
查询同学各门成绩依次按 数学降序英语升序语文升序的方式显示
mysql select name,math,english,chinese from exam_result order by math desc, english asc, chinese asc;
-----------------------------------
| name | math | english | chinese |
-----------------------------------
| 唐三藏 | 98 | 56 | 67 |
| 猪悟能 | 98 | 90 | 88 |
| 刘玄德 | 85 | 45 | 55 |
| 张翼德 | 84 | 48 | 34 |
| 曹孟德 | 84 | 67 | 82 |
| 孙悟空 | 78 | 77 | 87 |
| 孙权 | 73 | 78 | 70 |
| 宋公明 | 65 | 30 | 75 |
| 关云长 | 31 | 84 | 75 |
-----------------------------------
9 rows in set (0.00 sec)查询同学及总分由高到低
mysql select name, chinesemathenglish total from exam_result order by total desc;
------------------
| name | total |
------------------
| 猪悟能 | 276 |
| 孙悟空 | 242 |
| 曹孟德 | 233 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 关云长 | 190 |
| 刘玄德 | 185 |
| 宋公明 | 170 |
| 张翼德 | 166 |
------------------
9 rows in set (0.00 sec)
前面的where语句不能使用别名是因为顺序问题这里可以使用别名也是因为顺序问题。 执行顺序 找到具体是哪一张表where语句select要打印的信息order by排序limit显示n行 2.4筛选分页结果limit
语法
-- 从 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 * from exam_result;
-----------------------------------------------
| id | name | chinese | math | english | qq |
-----------------------------------------------
| 1 | 唐三藏 | 67 | 98 | 56 | NULL |
| 2 | 孙悟空 | 87 | 78 | 77 | NULL |
| 3 | 猪悟能 | 88 | 98 | 90 | NULL |
| 4 | 曹孟德 | 82 | 84 | 67 | NULL |
| 5 | 刘玄德 | 55 | 85 | 45 | NULL |
| 6 | 孙权 | 70 | 73 | 78 | NULL |
| 7 | 宋公明 | 75 | 65 | 30 | NULL |
| 8 | 张翼德 | 34 | 84 | 48 | 11111 |
| 9 | 关云长 | 75 | 31 | 84 | 222222 |
-----------------------------------------------
9 rows in set (0.00 sec)当数据不多的情况下可以全部显示但是如果数据很多有几万十几万行如果全部显示不但不方便看而且要耗费很多的资源。
所以我们可以使用limit。
从第一含开始
mysql select * from exam_result limit 5;
---------------------------------------------
| id | name | chinese | math | english | qq |
---------------------------------------------
| 1 | 唐三藏 | 67 | 98 | 56 | NULL |
| 2 | 孙悟空 | 87 | 78 | 77 | NULL |
| 3 | 猪悟能 | 88 | 98 | 90 | NULL |
| 4 | 曹孟德 | 82 | 84 | 67 | NULL |
| 5 | 刘玄德 | 55 | 85 | 45 | NULL |
---------------------------------------------
5 rows in set (0.00 sec)从中间开始
mysql select * from exam_result limit 3, 4;
---------------------------------------------
| id | name | chinese | math | english | qq |
---------------------------------------------
| 4 | 曹孟德 | 82 | 84 | 67 | NULL |
| 5 | 刘玄德 | 55 | 85 | 45 | NULL |
| 6 | 孙权 | 70 | 73 | 78 | NULL |
| 7 | 宋公明 | 75 | 65 | 30 | NULL |
---------------------------------------------
4 rows in set (0.00 sec)mysql select * from exam_result limit 4 offset 3;
---------------------------------------------
| id | name | chinese | math | english | qq |
---------------------------------------------
| 4 | 曹孟德 | 82 | 84 | 67 | NULL |
| 5 | 刘玄德 | 55 | 85 | 45 | NULL |
| 6 | 孙权 | 70 | 73 | 78 | NULL |
| 7 | 宋公明 | 75 | 65 | 30 | NULL |
---------------------------------------------
4 rows in set (0.00 sec)
建议对未知表进行查询时最好加一条 LIMIT 1避免因为表中数据过大查询全表数据导致数据库卡死 3. 更新Update
update table_name set column expr;
直接举个例子将孙悟空同学的数学成绩变更为 80 分
mysql update exam_result set math80 where name孙悟空;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select name, math from exam_result where name孙悟空;
-----------------
| name | math |
-----------------
| 孙悟空 | 80 |
-----------------
1 row in set (0.00 sec)将曹孟德同学的数学成绩变更为 60 分语文成绩变更为 70 分
mysql update exam_result set math60,chinese70 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 |
--------------------------
1 row in set (0.00 sec)将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
mysql select * from exam_result order by chinesemathenglish limit 3;
----------------------------------------------
| id | name | chinese | math | english | qq |
----------------------------------------------
| 8 | 张翼德 | 34 | 84 | 48 | 11111 |
| 7 | 宋公明 | 75 | 65 | 30 | NULL |
| 5 | 刘玄德 | 55 | 85 | 45 | NULL |
----------------------------------------------
3 rows in set (0.00 sec)mysql update exam_result set mathmath30 order by chinesemathenglish limit 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0mysql select * from exam_result order by chinesemathenglish limit 3;
-----------------------------------------------
| id | name | chinese | math | english | qq |
-----------------------------------------------
| 9 | 关云长 | 75 | 31 | 84 | 222222 |
| 8 | 张翼德 | 34 | 114 | 48 | 11111 |
| 4 | 曹孟德 | 70 | 60 | 67 | NULL |
-----------------------------------------------
3 rows in set (0.00 sec)注意更新全表的语句慎用 4.删除delete
4.1删除数据
delete from table_name [where ...] [order by ...] [limit ...]
注意删除整表操作要慎用
删除孙悟空同学的考试成绩
mysql delete from exam_result where name孙悟空;
Query OK, 1 row affected (0.00 sec)mysql select name from exam_result;
-----------
| name |
-----------
| 唐三藏 |
| 猪悟能 |
| 曹孟德 |
| 刘玄德 |
| 孙权 |
| 宋公明 |
| 张翼德 |
| 关云长 |
-----------
8 rows in set (0.00 sec)4.2删除整张表数据
delete from table_name;只是将表的数据全部清空但是还是会存在AUTO_INCREMENT自增长约束不会随着数据被清空而被置0再次插入就是在上一次的自增值1 4.3截断表
truncate [TABLE] table_name 1. 只能对整表操作不能像 delete一样针对部分数据操作 2. 实际上 MySQL 不对数据操作所以比 delete更快但是TRUNCATE在删除数据的时候并不经过真正的事物所以无法回滚 3. 会重置 AUTO_INCREMENT 项 5. 插入查询结果
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, a),- (100, a),- (200, a),- (200, b),- (200, b),- (200, b),- (300, c);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0去重思路
1.创建一个属性和原表相同的表
mysql create table no_duplicate_table like duplicate_table;
Query OK, 0 rows affected (0.02 sec)mysql desc duplicate_table;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
-----------------------------------------------
2 rows in set (0.00 sec)mysql desc no_duplicate_table;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
-----------------------------------------------
2 rows in set (0.00 sec)
2.insertdistinct去重后插入新表
mysql insert into no_duplicate_table select distinct * from duplicate_table;
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql select * from no_duplicate_table;
------------
| id | name |
------------
| 100 | a |
| 200 | a |
| 200 | b |
| 300 | c |
------------
4 rows in set (0.00 sec)3.将原表名字改为其他的将新表名字改为旧表名字
mysql rename table duplicate_table to old_duplicate_table;
Query OK, 0 rows affected (0.00 sec)mysql rename table no_duplicate_table to duplicate_table;
Query OK, 0 rows affected (0.01 sec)mysql select * from duplicate_table;
------------
| id | name |
------------
| 100 | a |
| 200 | a |
| 200 | b |
| 300 | c |
------------
4 rows in set (0.00 sec)6.聚合函数 1.统计班级共有多少同学
mysql select * from exam_result;
-----------------------------------------------
| id | name | chinese | math | english | qq |
-----------------------------------------------
| 1 | 唐三藏 | 67 | 98 | 56 | NULL |
| 3 | 猪悟能 | 88 | 98 | 90 | NULL |
| 4 | 曹孟德 | 70 | 60 | 67 | NULL |
| 5 | 刘玄德 | 55 | 115 | 45 | NULL |
| 6 | 孙权 | 70 | 73 | 78 | NULL |
| 7 | 宋公明 | 75 | 95 | 30 | NULL |
| 8 | 张翼德 | 34 | 114 | 48 | 11111 |
| 9 | 关云长 | 75 | 31 | 84 | 222222 |
-----------------------------------------------
8 rows in set (0.00 sec)mysql select count(*) from exam_result;
----------
| count(*) |
----------
| 8 |
----------
1 row in set (0.00 sec)
2.统计数学成绩总分
mysql select sum(math) from exam_result;
-----------
| sum(math) |
-----------
| 684 |
-----------
1 row in set (0.00 sec)
3.统计平均总分
mysql select avg(chinesemathenglish) from exam_result;
---------------------------
| avg(chinesemathenglish) |
---------------------------
| 214.5 |
---------------------------4.返回英语最高分
mysql select max(english) from exam_result;
--------------
| max(english) |
--------------
| 90 |
--------------
1 row in set (0.00 sec)5.返回 70 分以上的数学最低分
mysql select min(math) from exam_result where math70;
-----------
| min(math) |
-----------
| 73 |
-----------
1 row in set (0.00 sec)7. group by子句的使用
在select中使用group by 子句可以对指定列进行分组查询
select column1, column2, .. from table group by column;
准备工作创建一个雇员信息表 EMP员工表 DEPT部门表 SALGRADE工资等级表 mysql select * from emp;
--------------------------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
--------------------------------------------------------------------------------
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
--------------------------------------------------------------------------------
14 rows in set (0.00 sec)mysql select * from dept;
------------------------------
| deptno | dname | loc |
------------------------------
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
------------------------------
4 rows in set (0.00 sec)mysql select * from salgrade;
---------------------
| grade | losal | hisal |
---------------------
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
---------------------
5 rows in set (0.00 sec)如何显示每个部门的平均工资和最高工资
我们先试一下找到所有员工中最高工资和平均工资
mysql select max(sal) 最高, avg(sal) 平均 from emp;
----------------------
| 最高 | 平均 |
----------------------
| 5000.00 | 2073.214286 |
----------------------
1 row in set (0.00 sec)
如果想要找到每一组的最高工资和平均工资就要分组
mysql select max(sal) 最高, avg(sal) 平均 from emp group by deptno;
----------------------
| 最高 | 平均 |
----------------------
| 5000.00 | 2916.666667 |
| 3000.00 | 2175.000000 |
| 2850.00 | 1566.666667 |
----------------------
3 rows in set (0.00 sec)显示每个部门的每种岗位的平均工资和最低工资
mysql select deptno, job, avg(sal) 平均, min(sal)最低 from emp group by deptno, job;
-----------------------------------------
| deptno | job | 平均 | 最低 |
-----------------------------------------
| 10 | CLERK | 1300.000000 | 1300.00 |
| 10 | MANAGER | 2450.000000 | 2450.00 |
| 10 | PRESIDENT | 5000.000000 | 5000.00 |
| 20 | ANALYST | 3000.000000 | 3000.00 |
| 20 | CLERK | 950.000000 | 800.00 |
| 20 | MANAGER | 2975.000000 | 2975.00 |
| 30 | CLERK | 950.000000 | 950.00 |
| 30 | MANAGER | 2850.000000 | 2850.00 |
| 30 | SALESMAN | 1400.000000 | 1250.00 |
-----------------------------------------注意select后面的字段在group by中必须要出现。
mysql select deptno, job, ename, avg(sal) 平均, min(sal)最低 from emp group by deptno, job;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column scott.emp.ename which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_modeonly_full_group_bymysql select deptno, job, ename, avg(sal) 平均, min(sal)最低 from emp group by deptno, job, ename;
-------------------------------------------------
| deptno | job | ename | 平均 | 最低 |
-------------------------------------------------
| 10 | CLERK | MILLER | 1300.000000 | 1300.00 |
| 10 | MANAGER | CLARK | 2450.000000 | 2450.00 |
| 10 | PRESIDENT | KING | 5000.000000 | 5000.00 |
| 20 | ANALYST | FORD | 3000.000000 | 3000.00 |
| 20 | ANALYST | SCOTT | 3000.000000 | 3000.00 |
| 20 | CLERK | ADAMS | 1100.000000 | 1100.00 |
| 20 | CLERK | SMITH | 800.000000 | 800.00 |
| 20 | MANAGER | JONES | 2975.000000 | 2975.00 |
| 30 | CLERK | JAMES | 950.000000 | 950.00 |
| 30 | MANAGER | BLAKE | 2850.000000 | 2850.00 |
| 30 | SALESMAN | ALLEN | 1600.000000 | 1600.00 |
| 30 | SALESMAN | MARTIN | 1250.000000 | 1250.00 |
| 30 | SALESMAN | TURNER | 1500.000000 | 1500.00 |
| 30 | SALESMAN | WARD | 1250.000000 | 1250.00 |
-------------------------------------------------
14 rows in set (0.00 sec)显示平均工资低于2000的部门和它的平均工资 可以分为两步 统计各个部门的平均工资having和group by配合使用对group by结果进行过滤 having的作用就是对聚合后的数据进行条件筛选类似于where。
1.统计各个部门平均工资。
mysql select deptno, avg(sal) 平均 from emp group by deptno;
---------------------
| deptno | 平均 |
---------------------
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
---------------------
3 rows in set (0.00 sec)
2.having和group by配合使用对group by结果进行过滤
mysql select deptno, avg(sal) as myavg from emp group by deptno having myavg2000;
---------------------
| deptno | myavg |
---------------------
| 30 | 1566.666667 |
---------------------
1 row in set (0.00 sec)where和having的区别 where是对任意列进行条件筛选筛选之后才会进行分组having是分组聚合之后才会筛选分组之后才会筛选 执行顺序 1.数据来源于哪个表中from 2.对数据进行条件筛选where 3.group up进行分组 4.having条件筛选 5.select进行聚合统计 6.distinct去重 7.order by排序 8.limit得到需要行数
