- 论坛徽章:
- 0
|
3,删除记录
不加where条件会将表中所有记录删除!
mysql> delete from emp where ename='dony';
Query OK, 1 row affected
也可一次删除多条记录,如果from后面的表名用别名,则delete后面也要用相应的别名
同时将emp表和dept表中deptno为3的记录删除
mysql> select * from emp;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 100.00 | 1 |
| lisa | 2003-02-01 | 400.00 | 2 |
| bjguan | 2004-04-02 | 100.00 | 1 |
| bzshen | 2005-04-01 | 300.00 | 3 |
| dony | 2005-02-05 | 2000.00 | 4 |
+--------+------------+---------+--------+
5 rows in set
mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | zzx |
| 2 | lisa |
| 5 | fin |
| 3 | hr |
+--------+----------+
4 rows in set
mysql> delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;
Query OK, 2 rows affected
mysql> select * from emp;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 100.00 | 1 |
| lisa | 2003-02-01 | 400.00 | 2 |
| bjguan | 2004-04-02 | 100.00 | 1 |
| dony | 2005-02-05 | 2000.00 | 4 |
+--------+------------+---------+--------+
4 rows in set
mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | zzx |
| 2 | lisa |
| 5 | fin |
+--------+----------+
3 rows in set
4,查询记录
查询指定字段记录(所有记录字段名用*)
mysql> select ename,hiredate,sal,deptno from emp;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 100.00 | 1 |
| lisa | 2003-02-01 | 400.00 | 2 |
| bjguan | 2004-04-02 | 100.00 | 1 |
| dony | 2005-02-05 | 2000.00 | 4 |
+--------+------------+---------+--------+
4 rows in set
查询不重复记录
mysql> select distinct deptno from emp;
+--------+
| deptno |
+--------+
| 1 |
| 2 |
| 4 |
+--------+
3 rows in set
条件查询,where后面还可以使用>,=,等比较运算符,使用or,and多条件查询
如:查询deptno为1的记录
mysql> select * from emp where deptno=1;
+--------+------------+--------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+--------+--------+
| zzx | 2000-01-01 | 100.00 | 1 |
| bjguan | 2004-04-02 | 300.00 | 1 |
+--------+------------+--------+--------+
2 rows in set
mysql> select * from emp where deptno=1 and sal
;
+-------+------------+--------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+--------+--------+
| zzx | 2000-01-01 | 100.00 | 1 |
+-------+------------+--------+--------+
1 row in set
排序和限制
desc降序,asc升序,默认为升序,order by后面可以有多个不同的排序字段,如果排序字段值一样,则按照第二个排序字段进行排序...
mysql> select * from emp order by sal;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 2000.00 | 1 |
| bzshen | 2005-02-05 | 3000.00 | 3 |
| lisa | 2003-02-01 | 4000.00 | 2 |
| bjguan | 2004-04-02 | 5000.00 | 1 |
+--------+------------+---------+--------+
4 rows in set
如果只有一个排序字段,则字段相同的记录将会无序排列
mysql> select * from emp order by deptno;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 2000.00 | 1 |
| bjguan | 2004-04-02 | 5000.00 | 1 |
| lisa | 2003-02-01 | 4000.00 | 2 |
| bzshen | 2005-02-05 | 3000.00 | 3 |
+--------+------------+---------+--------+
4 rows in set
先将deptno升序排列,再将sal降序排列
mysql> select * from emp order by deptno,sal desc;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| bjguan | 2004-04-02 | 5000.00 | 1 |
| zzx | 2000-01-01 | 2000.00 | 1 |
| lisa | 2003-02-01 | 4000.00 | 2 |
| bzshen | 2005-02-05 | 3000.00 | 3 |
+--------+------------+---------+--------+
4 rows in set
对于排序后的字段,可使用limit显示一部分(limit在其它数据库不能用)
显示前3条记录
mysql> select * from emp order by sal limit 3;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 2000.00 | 1 |
| bzshen | 2005-02-05 | 3000.00 | 3 |
| lisa | 2003-02-01 | 4000.00 | 2 |
+--------+------------+---------+--------+
3 rows in set
从第2条记录开始,显示3条记录(起始偏移量为0)
mysql> select * from emp order by sal limit 1,3;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| bzshen | 2005-02-05 | 3000.00 | 3 |
| lisa | 2003-02-01 | 4000.00 | 2 |
| bjguan | 2004-04-02 | 5000.00 | 1 |
+--------+------------+---------+--------+
3 rows in set
聚合
常用的聚合函数有sum求和,count(*)记录数,max最大值,min最小值
从emp表统计人数
mysql> select count(1) from emp;
+----------+
| count(1) |
+----------+
| 4 |
+----------+
1 row in set
统计各部门(deptno)人数,group by表示要进行分类聚合的字段
mysql> select deptno,count(1) from emp group by deptno;
+--------+----------+
| deptno | count(1) |
+--------+----------+
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
+--------+----------+
3 rows in set
统计各部门(deptno)人数,并统计总人数,with rollup表示对分类聚合后的结果进行再汇总
mysql> select deptno,count(1) from emp group by deptno with rollup;
+--------+----------+
| deptno | count(1) |
+--------+----------+
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
| NULL | 4 |
+--------+----------+
4 rows in set
统计人数大于1的部门,having表示对分类后的结果再进行条件过滤
mysql> select deptno,count(1) from emp group by deptno having count(1)>1;
+--------+----------+
| deptno | count(1) |
+--------+----------+
| 1 | 2 |
+--------+----------+
1 row in set
统计员工薪水sal总额,最高,最低薪水
mysql> select * from emp;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 2000.00 | 1 |
| lisa | 2003-02-01 | 4000.00 | 2 |
| bjguan | 2004-04-02 | 5000.00 | 1 |
| bzshen | 2005-02-05 | 3000.00 | 3 |
+--------+------------+---------+--------+
4 rows in set
mysql> select sum(sal),max(sal),min(sal) from emp;
+----------+----------+----------+
| sum(sal) | max(sal) | min(sal) |
+----------+----------+----------+
| 14000.00 | 5000.00 | 2000.00 |
+----------+----------+----------+
1 row in set
表连接
当同时需要显示多个表中的字段时,就需要用到表连接,
表连接分内连接和外连接,主要区别是,内连接仅选出两张表中互相匹配的记录,而外连接会选车其它不匹配的记录
如:查询雇员名字和所在部门,
mysql> select * from emp;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 2000.00 | 1 |
| lisa | 2003-02-01 | 4000.00 | 2 |
| bjguan | 2004-04-02 | 5000.00 | 1 |
| bzshen | 2005-02-05 | 3000.00 | 3 |
+--------+------------+---------+--------+
4 rows in set
mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | tech |
| 2 | sale |
| 3 | hr |
+--------+----------+
3 rows in set
mysql> select ename,deptname from emp,dept where emp.deptno=dept.deptno;
+--------+----------+
| ename | deptname |
+--------+----------+
| zzx | tech |
| lisa | sale |
| bjguan | tech |
| bzshen | hr |
+--------+----------+
4 rows in set
外连接分为左连接和右连接
左连接:包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
右连接:包含所有的右边表中的记录甚至是左右边中没有和它匹配的记录
如:查看emp中所有用户名和所在部门名称(左连接)
mysql> select * from emp;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 2000.00 | 1 |
| lisa | 2003-02-01 | 4000.00 | 2 |
| bjguan | 2004-04-02 | 5000.00 | 1 |
| bzshen | 2005-02-05 | 3000.00 | 3 |
| dony | 2005-04-01 | 4000.00 | 4 |
+--------+------------+---------+--------+
5 rows in set
mysql> select * from dept
;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | tech |
| 2 | sale |
| 3 | hr |
+--------+----------+
3 rows in set
mysql> select ename,deptname from emp left join dept on emp.deptno=dept.deptno;
+--------+----------+
| ename | deptname |
+--------+----------+
| zzx | tech |
| lisa | sale |
| bjguan | tech |
| bzshen | hr |
| dony | NULL |
+--------+----------+
5 rows in set
右连接:
mysql> select ename,deptname from dept right join emp on dept.deptno=emp.deptno;
+--------+----------+
| ename | deptname |
+--------+----------+
| zzx | tech |
| lisa | sale |
| bjguan | tech |
| bzshen | hr |
| dony | NULL |
+--------+----------+
5 rows in set
子查询
某些情况下,当进行查询的时候,需要的条件是另外一个select语句的结果,这就需要子查询,
关键字有in,not in,=,!=,exists,not exists等
如:从emp表中查询出所有部门在dept表中的所有记录
mysql> select * from emp;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 2000.00 | 1 |
| lisa | 2003-02-01 | 4000.00 | 2 |
| bjguan | 2004-04-02 | 5000.00 | 1 |
| bzshen | 2005-02-05 | 3000.00 | 3 |
| dony | 2005-04-01 | 4000.00 | 4 |
+--------+------------+---------+--------+
5 rows in set
mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | tech |
| 2 | sale |
| 3 | hr |
+--------+----------+
3 rows in set
mysql> select * from emp where deptno in(select deptno from dept);
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 2000.00 | 1 |
| lisa | 2003-02-01 | 4000.00 | 2 |
| bjguan | 2004-04-02 | 5000.00 | 1 |
| bzshen | 2005-02-05 | 3000.00 | 3 |
+--------+------------+---------+--------+
4 rows in set
记录联合
将两个表的数据按一个查询条件查询出来后,将结果合并到一起,用union和union all实现
两者区别是,union all是把结果集直接合并在一起,而union是将union all后的结果进行一次distinct,去重复记录.
如:将emp表和dept表中的部门编号的集合显示出来
mysql> select * from emp;select * from dept;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 2000.00 | 1 |
| lisa | 2003-02-01 | 4000.00 | 2 |
| bjguan | 2004-04-02 | 5000.00 | 1 |
| bzshen | 2005-02-05 | 3000.00 | 3 |
| dony | 2005-04-01 | 4000.00 | 4 |
+--------+------------+---------+--------+
5 rows in set
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | tech |
| 2 | sale |
| 3 | hr |
+--------+----------+
3 rows in set
mysql> select deptno from emp
-> union all
-> select deptno from dept;
+--------+
| deptno |
+--------+
| 1 |
| 2 |
| 1 |
| 3 |
| 4 |
| 1 |
| 2 |
| 3 |
+--------+
8 rows in set
union去重复记录
mysql> select deptno from emp
-> union
-> select deptno from dept;
+--------+
| deptno |
+--------+
| 1 |
| 2 |
| 3 |
| 4 |
+--------+
4 rows in set
仙尘于08年3月25日
本文来自ChinaUnix博客,如果查看原文请点:http://blog.chinaunix.net/u2/63095/showart_507556.html |
|