目录
前言
本章将详细介绍关于数据R的操作,即查询数据记录操作。内容包括:
- 简单数据记录查询
- 条件数据记录查询
- 排序数据记录查询
- 限制数据记录查询数量
- 统计函数和分组数据记录查询
为了便于实例讲解,需在数据库 company 建立一个雇员信息表(t_employee),其结构如下:
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno | int | YES | | NULL | |
| ename | varchar(20) | YES | | NULL | |
| job | varchar(40) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| Hiredate | date | YES | | NULL | |
| sal | double(10,2) | YES | | NULL | |
| comm | double(10,2) | YES | | NULL | |
| deptno | int | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
内容:
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | MGR | Hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1981-03-12 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1982-03-12 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1983-03-12 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-03-12 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-03-12 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-03-12 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1985-03-12 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1981-03-12 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-03-12 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1989-03-12 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1998-03-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1997-03-12 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 0000-00-00 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1981-03-12 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
具体步骤:
mysql> CREATE TABLE t_employee(
-> empno int(11),
-> ename VARCHAR(20),
-> job VARCHAR(40),
-> MGR INT(11),
-> Hiredate DATE,
-> sal double(10,2),
-> comm double(10,2),
-> deptno INT(11)
-> );
Query OK, 0 rows affected, 5 warnings (0.49 sec)
mysql> INSERT INTO t_employee
-> VALUES(7369,'SMITH','CLERK',7902,'1981-03-12',800.00,NULL,20);
-> (7499,'ALLEN','SALESMAN',7698,'1982-03-12',1600.00,300.00,30),
-> (7521,'WARD','SALESMAN',7698,'1983-03-12',1250.00,500.00,30),
-> (7566,'JONES','MANAGER',7839,'1981-03-12',2975.00,NULL,20),
-> (7654,'MARTIN','SALESMAN',7698,'1981-03-12',1250.00,1400.00,30),
-> (7698,'BLAKE','MANAGER',7839,'1981-03-12',2850.00,NULL,30),
-> (7782,'CLARK','MANAGER',7839,'1985_03-12',2450.00,NULL,10),
-> (7788,'SCOTT','ANALYST',7566,'1981-03-12',3000.00,NULL,20),
-> (7839,'KING','PRESIDENT',NULL,'1981-03-12',5000.00,NULL,10),
-> (7844,'TURNER','SALESMAN',7698,'1989-03-12',1500.00,0.00,30),
-> (7876,'ADAMS','CLERK',7788,'1998-03-12',1100.00,NULL,20),
-> (7900,'JAMES','CLERK',7698,'1997-03-12',950.00,NULL,30),
-> (7902,'FORD','ANALYST',7566,'0000-00-00',3000.00,NULL,20),
-> (7934,'MILLER','CLERK',7782,'1981-03-12',1300.00,NULL,10);
Query OK, 14 rows affected (0.02 sec)
Records: 14 Duplicates: 0 Warnings: 0
简单数据记录查询
查询数据记录:指从数据库队形表中获取所要求的的数据记录。
语法形式:
SELECT field1,field2,……fieldn
FROM table_name
fieldn 表示要查询的字段名字
一、简单数据查询
1、 查询所有字段数据
mysql> #查询表数据#
mysql> SELECT empno,ename,job,MGR,Hiredate,sal,comm,deptno
-> FROM t_employee;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | MGR | Hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1981-03-12 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1982-03-12 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1983-03-12 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-03-12 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-03-12 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-03-12 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1985-03-12 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1981-03-12 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-03-12 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1989-03-12 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1998-03-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1997-03-12 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 0000-00-00 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1981-03-12 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
在上述的语句中,由于要查询所有的字段的数据,所以关键字SELECT 后面的字段列表包含了表中所有字段。
注: 调整SELECT关键字后面的字段顺序,可以较灵活的显示查询结果的所显示的字段顺序。
2、“*”符号的使用
语法形式:
SELECT *
FROM table_name;
上述的语句中用符号 “ * ” 可以表示参数 table_name 表中的所有字段。
例:
mysql> SELECT *
-> FROM t_employee;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | MGR | Hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1981-03-12 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1982-03-12 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1983-03-12 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-03-12 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-03-12 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-03-12 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1985-03-12 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1981-03-12 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-03-12 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1989-03-12 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1998-03-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1997-03-12 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 0000-00-00 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1981-03-12 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
注: 与上一种方式相比,“ * ” 符号方式虽然比较简洁,但是这种方式只能按照表中字段的固定顺序进行显示,不能随意改变字段的显示顺序。
3、查询指定字段数据
从简单记录数据查询的语法形式可知,我们可以查询指定某些字段的的数据。
例:查询 “ empno ” 、" sal " 、“ ename ” 等三个字段端的数据
mysql> #查询指定字段数据#
mysql> SELECT empno,sal,ename
-> FROM t_employee;
+-------+---------+--------+
| empno | sal | ename |
+-------+---------+--------+
| 7369 | 800.00 | SMITH |
| 7499 | 1600.00 | ALLEN |
| 7521 | 1250.00 | WARD |
| 7566 | 2975.00 | JONES |
| 7654 | 1250.00 | MARTIN |
| 7698 | 2850.00 | BLAKE |
| 7782 | 2450.00 | CLARK |
| 7788 | 3000.00 | SCOTT |
| 7839 | 5000.00 | KING |
| 7844 | 1500.00 | TURNER |
| 7876 | 1100.00 | ADAMS |
| 7900 | 950.00 | JAMES |
| 7902 | 3000.00 | FORD |
| 7934 | 1300.00 | MILLER |
+-------+---------+--------+
14 rows in set (0.00 sec)
可以看出来,以上三个字段的顺序与表中的是不一样的。
二、避免重复数据查询-DISTINCT
当在MySQL软件中执行简单数据查询时,有时候会显示出重复数据,为了实现查询不重复的数据,MySQL软件提供了关键字----DISTINCT.
例如,如果我们想找出工作中涉及到的岗位就有哪几种,如果单单查询字段 job 的数据,其结果是这样的:
mysql> SELECT job
-> FROM t_employee;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| SALESMAN |
| MANAGER |
| SALESMAN |
| MANAGER |
| MANAGER |
| ANALYST |
| PRESIDENT |
| SALESMAN |
| CLERK |
| CLERK |
| ANALYST |
| CLERK |
+-----------+
14 rows in set (0.00 sec)
结果中列出了job字段下所有的记录,这些记录有重复的,这显然不是我们所期盼的。
因此我们可以用 DISTINCT 关键字
mysql> #查找字段中不重复的数据记录#
mysql> SELECT DISTINCT job
-> FROM t_employee;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
5 rows in set (0.01 sec)
从结果中我们可以看出 job 字段所涉及的岗位共有5种。
三、实现数学四则运算数据查询
在MySQL 软件中执行简单数据查询时,有时会需要实现数学四则运算(加 +、减 - 、乘 * 、除 /),该软件所支持的四则运算符如下表所示:
运算符 | 描述 |
---|---|
+ | 加法 |
- | 减法 |
* | 乘法 |
/(DIV) | 除法 |
%(MOD) | 求余 |
例:在雇员表(t_employee)中查询每个雇员的年薪,我们知道原表中代表月薪的字段是 “sal” ,因此,如果要查询年薪则需要乘以12个月。这就需要一个简单的四则运算。
mysql> SELECT ename,sal*12
-> FROM t_employee;
+--------+----------+
| ename | sal*12 |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.02 sec)
在上述的语句中,通过表达式“sal*12” 来查询年薪。从结果中看,显示的查询字段为“sal * 12” ,不方便用户浏览。在MySQL软件中,提供了一种方法来实现修改查询的字段名,其语法形式为:
SELECT field1 [AS] otherfield1,field2 [AS] otherfield2,……fieldn [AS] otherfieldn
FROM table_name
在上述的语句中,参数field 为字段原来的名字,参数 otherfield 为字段新的名字。
例:我们将刚才的查询改为:ename----姓名 , sal*12 -----年薪
mysql> SELECT ename 姓名,sal*12 年薪
-> FROM t_employee;
+--------+----------+
| 姓名 | 年薪 |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.01 sec)
或者加上AS
mysql> SELECT ename AS 姓名,sal*12 AS 年薪
-> FROM t_employee;
+--------+----------+
| 姓名 | 年薪 |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19200.00 |
| WARD | 15000.00 |
| JONES | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)
四、设置显示格式数据查询
为了更为方便用户浏览所查询到的数据,需要设置显示格式,这就需要MySQL 软件中提供的函数 CONCAT() 来连接字符串,从而实现设置显示数据的格式。
例:
mysql> SELECT CONCAT(ename,'雇员的年薪为:',sal*12) 年薪
-> FROM t_employee;
+------------------------------+
| 年薪 |
+------------------------------+
| SMITH雇员的年薪为:9600.00 |
| ALLEN雇员的年薪为:19200.00 |
| WARD雇员的年薪为:15000.00 |
| JONES雇员的年薪为:35700.00 |
| MARTIN雇员的年薪为:15000.00 |
| BLAKE雇员的年薪为:34200.00 |
| CLARK雇员的年薪为:29400.00 |
| SCOTT雇员的年薪为:36000.00 |
| KING雇员的年薪为:60000.00 |
| TURNER雇员的年薪为:18000.00 |
| ADAMS雇员的年薪为:13200.00 |
| JAMES雇员的年薪为:11400.00 |
| FORD雇员的年薪为:36000.00 |
| MILLER雇员的年薪为:15600.00 |
+------------------------------+
14 rows in set (0.01 sec)
上面的例子,是用函数 CONCAT() 将字段 ename 与字符串“雇员的年薪为:” 以及sal*12 运算结果连接起来,并将字段的名字重新命名为年薪。
根据上面的例子,可以思考一下,应用下面的语句,会得到什么样的结果:
mysql> SELECT CONCAT(ename,'雇员的年薪为:'),sal*12 年薪
-> FROM t_employee;
生词表
单词 | 读音 | 译文 | MySQL |
---|---|---|---|
Distinct | 英[dɪˈstɪŋkt] | adj. 不同的; 明显的; 清晰的; 清楚的; 明白的; 有区别的; 不同种类的; 确定无疑的; 确切的; | 不重复的 |
Concat | 英[kɒn’kætɪneɪt] | concatenate的缩写 v.连接;使连续(衔接)起来;连锁;串级 | 连接字符串函数 |
如有错误敬请高人指点,书写的易读性可否,希望大家多提意见。
上一篇【MySQL 触发器的操作】
下一篇【单表数据记录查询(二)】