7.1 基本查询语句
使用SELECT语句。其基本格式:
SELECT
{*|<字段列表>}
[
FROM <表1>,<表2>...
[WHERE <表达式>
HAVING____
GROUP BY__
ORDER BY__
LIMIT__
]
SELECT[字段1,字段2,...]
FROM[表或视图]
WHERE[查询条件]
WHERE子句是可选项,如果选择该选项,将限定查询行必须满足的查询条件。
下面使用一个例子说明如何使用SELECT从单个表中获取数据。
首先定义数据表,输入如下语句:
mysql> CREATE TABLE fruits(
-> f_id char(10) NOT NULL,
-> s_id INT NOT NULL,
-> f_name char(255) NOT NULL,
-> f_price decimal(8,2) NOT NULL,
-> PRIMARY KEY(f_id)
-> );
Query OK, 0 rows affected (0.36 sec)
插入数据:
mysql> INSERT INTO fruits(f_id,s_id,f_name,f_price)
-> VALUES('a1',101,'apple',5.2),
-> ('b1',101,'blackberry',10.2),
-> ('bs1',102,'orange',11.2),
-> ('bs2',105,'melon',8.2),
-> ('t1',102,'banana',10.3),
-> ('t2',102,'grape',5.3),
-> ('o2',103,'coconut',9.2),
-> ('c0',101,'cherry',3.2),
-> ('a2',103,'apricot',2.2),
-> ('l2',104,'lemon',6.4),
-> ('b2',104,'berry',7.6),
-> ('m1',106,'mango',15.6),
-> ('m2',105,'xbabay',2.6),
-> ('t4',107,'xbababa',3.6),
-> ('m3',105,'xxtt',11.6),
-> ('b5',107,'xxxx',3.6);
Query OK, 16 rows affected (0.09 sec)
Records: 16 Duplicates: 0 Warnings: 0
使用SELECT语句查询f_id字段:
mysql> SELECT f_id FROM fruits;
+------+
| f_id |
+------+
| a1 |
| a2 |
| b1 |
| b2 |
| b5 |
| bs1 |
| bs2 |
| c0 |
| l2 |
| m1 |
| m2 |
| m3 |
| o2 |
| t1 |
| t2 |
| t4 |
+------+
16 rows in set (0.13 sec)
7.2 单表查询
SELECT * FROM db;比列出所有属性名效率低,因为*有一个转换的过程。
在MySQL中,可通过SELECT __ FROM __ WHERE__来查询指定记录。
WHERE后面的操作符有:
IN:用来查询满足指定范围内的条件的记录;
BETWEEN AND:用来查询某个范围内的值;
LIKE:使用通配符进行匹配查找;
(通配符%用于匹配在指定位置的任意数目的字符。b%:b开头;%g%:包含g;b%y:b开头,y结尾。)
(_通配符,只能匹配任意单个字符,即指定个数)
AND:限定只有满足所有查询条件的记录才会被返回,AND可以连接两个甚至两个以上的查询条件。
OR:只需满足其中一个条件即可返回。(注意:AND的优先级大于OR)
7.2.10查询结果不重复
DISTINCT:使查询结果不重复
mysql> SELECT DISTINCT Id,name FROM worker;
+----+--------+
| Id | name |
+----+--------+
| 1 | jimy |
| 2 | Tom |
| 3 | Keiven |
| 4 | Michal |
| 5 | Nick |
+----+--------+
5 rows in set (0.00 sec)
7.2.11 对查询结果排序
- 单列排序
mysql> SELECT name FROM worker ORDER BY name;
+--------+
| name |
+--------+
| jimy |
| Keiven |
| Michal |
| Nick |
| Tom |
+--------+
5 rows in set (0.10 sec)
- 多列排序
mysql> SELECT name,Id FROM worker ORDER BY name,Id;
+--------+----+
| name | Id |
+--------+----+
| jimy | 1 |
| Keiven | 3 |
| Michal | 4 |
| Nick | 5 |
| Tom | 2 |
+--------+----+
5 rows in set (0.00 sec)
注:在对多列进行排序的时候,首先排序的第一列必须有相同的列值,才会对第二列进行排序。如果第一列所有值都是唯一的,将不再对第二列进行排序。
- 指定排序方向
排序默认为升序:ASC;可以自己指定升降序,ORDER BY__ ASC/DESC。
mysql> SELECT Id FROM worker ORDER BY Id DESC;
+----+
| Id |
+----+
| 5 |
| 4 |
| 3 |
| 2 |
| 1 |
+----+
5 rows in set (0.11 sec)
7.2.12 分组查询
- 创建分组
GROUP关键字通常和集合函数一起使用,如:MAX()、MIN()、COUNT()、SUM()、AVG()。
mysql> SELECT Id,COUNT(*) AS Total FROM worker GROUP BY Id;
+----+-------+
| Id | Total |
+----+-------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
+----+-------+
5 rows in set (0.00 sec)
- 使用HAVING过滤分组
HAVING 在数据分组之后进行过滤来分组。 - 在GROUP BY子句中使用WITH ROOLUP
使用WITH ROLLUP关键字之后,在所有查询出的分组记录之后增加一条记录,该记录计算查询出的所有记录的总和。 - 多字段分组
即GROUP BY 后面接需要的分组字段。 - GROUP BU和ORDER BY 一起使用
注:使用ROOLUP时,不能同时使用ORDER BY子句进行结果排序。
7.4 内连接查询
内连接(INNER JOIN)使用比较运算符进行表间某些列数据的比较操作,并列出这些表中与连接相匹配的数据行,组合成新的记录。p212.
7.7 为表和字段取别名
使用AS。
7.8 使用正则表达式查询
REGEXP关键字指定正则表达式的字符匹配模式。
^:匹配文本的开始字符
$:匹配文本的结束字符
.:匹配任何单个字符,如b.t:bit、bat等
*:匹配零个或多个在它前面的字符
+:匹配前面的字符一次或多次,如:ba+:b开头后面紧跟至少一个a
<字符串>:匹配包含指定的字符串的文本
[字符集]:匹配字符集中的任意一个字符
[^]:匹配不在括号中的任何字符
字符串{n,}:匹配前面的字符串至少n次
字符串{n,m}:匹配前面的字符串至少n次,至多m次
7.9 综合案例
- 创建数据表employee和dept
mysql> CREATE TABLE dept(
-> d_no INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> d_name VARCHAR(50),
-> d_location VARCHAR(100)
-> );
Query OK, 0 rows affected (0.37 sec)
mysql> CREATE TABLE employee(
-> e_no INT NOT NULL PRIMARY KEY,
-> e_name VARCHAR(100) NOT NULL,
-> e_gender CHAR(2) NOT NULL,
-> dept_no INT NOT NULL,
-> e_job VARCHAR(100) NOT NULL,
-> e_salary SMALLINT NOT NULL,
-> hireDate DATE,
-> CONSTRAINT dno_fk FOREIGN KEY(dept_no)
-> REFERENCES dept(d_no)
-> );
Query OK, 0 rows affected (0.35 sec)
- 将指定记录分别插入两个表中
mysql> INSERT INTO dept
-> VALUES (10,'ACCOUNTING','ShangHai'),
-> (20,'RESEARCH','BeiJing'),
-> (30,'SALES','ShenZhen'),
-> (40,'OPERATIONS','FuJian');
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO employee
-> VALUES(1001,'SMITH','m',20,'CLERK',800,'2005-11-12'),
-> (1002,'ALLEN','f',30,'SALESMAN',1600,'2001-05-12'),
-> (1003,'WARD','f',30,'SALESMAN',1250,'2001-05-12'),
-> (1004,'JONES','m',20,'MANAGER',2975,'1998-05-18'),
-> (1005,'MARTIN','m',30,'SALSMAN',1250,'2001-06-12'),
-> (1006,'BLAKE','f',30,'MANAGER',2850,'1997-02-15'),
-> (1007,'CLARK','m',10,'MANAGER',2450,'2002-09-12'),
-> (1008,'SCOTT','m',20,'ANALYST',3000,'2003-05-12'),
-> (1009,'KING','f',10,'PRESIDENT',5000,'1995-01-01'),
-> (1010,'TURNER','f',30,'SALESMAN',1500,'1997-10-12'),
-> (1011,'ADAMS','m',20,'CLERK',1100,'1999-10-05'),
-> (1012,'JAMES','m',30,'CLERK',950,'2008-06-15');
Query OK, 12 rows affected (0.46 sec)
Records: 12 Duplicates: 0 Warnings: 0
- 在employee表中,查询有记录的e_no、e_name和e_salary字段
mysql> SELECT e_no,e_name,e_salary FROM employee;
+------+--------+----------+
| e_no | e_name | e_salary |
+------+--------+----------+
| 1001 | SMITH | 800 |
| 1002 | ALLEN | 1600 |
| 1003 | WARD | 1250 |
| 1004 | JONES | 2975 |
| 1005 | MARTIN | 1250 |
| 1006 | BLAKE | 2850 |
| 1007 | CLARK | 2450 |
| 1008 | SCOTT | 3000 |
| 1009 | KING | 5000 |
| 1010 | TURNER | 1500 |
| 1011 | ADAMS | 1100 |
| 1012 | JAMES | 950 |
+------+--------+----------+
12 rows in set (0.00 sec)
- 在employee表中,查询dept_no=10和20的所有记录
mysql> SELECT * FROM employee WHERE dept_no IN (10,20);
+------+--------+----------+---------+-----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job | e_salary | hireDate |
+------+--------+----------+---------+-----------+----------+------------+
| 1001 | SMITH | m | 20 | CLERK | 800 | 2005-11-12 |
| 1004 | JONES | m | 20 | MANAGER | 2975 | 1998-05-18 |
| 1007 | CLARK | m | 10 | MANAGER | 2450 | 2002-09-12 |
| 1008 | SCOTT | m | 20 | ANALYST | 3000 | 2003-05-12 |
| 1009 | KING | f | 10 | PRESIDENT | 5000 | 1995-01-01 |
| 1011 | ADAMS | m | 20 | CLERK | 1100 | 1999-10-05 |
+------+--------+----------+---------+-----------+----------+------------+
6 rows in set (0.01 sec)
- 在employee表中,查询工资范围在800~2500之间的员工信息
mysql> SELECT * FROM employee WHERE e_salary BETWEEN 800 AND 2500;
+------+--------+----------+---------+----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job | e_salary | hireDate |
+------+--------+----------+---------+----------+----------+------------+
| 1001 | SMITH | m | 20 | CLERK | 800 | 2005-11-12 |
| 1002 | ALLEN | f | 30 | SALESMAN | 1600 | 2001-05-12 |
| 1003 | WARD | f | 30 | SALESMAN | 1250 | 2001-05-12 |
| 1005 | MARTIN | m | 30 | SALSMAN | 1250 | 2001-06-12 |
| 1007 | CLARK | m | 10 | MANAGER | 2450 | 2002-09-12 |
| 1010 | TURNER | f | 30 | SALESMAN | 1500 | 1997-10-12 |
| 1011 | ADAMS | m | 20 | CLERK | 1100 | 1999-10-05 |
| 1012 | JAMES | m | 30 | CLERK | 950 | 2008-06-15 |
+------+--------+----------+---------+----------+----------+------------+
8 rows in set (0.00 sec)
- 在employee表中,查询部门编号为20的部门中的员工信息
mysql> SELECT * FROM employee WHERE dept_no=20;
+------+--------+----------+---------+---------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job | e_salary | hireDate |
+------+--------+----------+---------+---------+----------+------------+
| 1001 | SMITH | m | 20 | CLERK | 800 | 2005-11-12 |
| 1004 | JONES | m | 20 | MANAGER | 2975 | 1998-05-18 |
| 1008 | SCOTT | m | 20 | ANALYST | 3000 | 2003-05-12 |
| 1011 | ADAMS | m | 20 | CLERK | 1100 | 1999-10-05 |
+------+--------+----------+---------+---------+----------+------------+
4 rows in set (0.00 sec)
- 在employee表中,查询每个部门的最高工资的员工信息
mysql> SELECT dept_no,MAX(e_salary) FROM employee GROUP BY dept_no;
+---------+---------------+
| dept_no | MAX(e_salary) |
+---------+---------------+
| 10 | 5000 |
| 20 | 3000 |
| 30 | 2850 |
+---------+---------------+
3 rows in set (0.00 sec)
- 查询员工BLAKE所在部门和部门所在地
mysql> SELECT d_no,d_location FROM dept WHERE d_no
-> =(SELECT dept_no FROM employee WHERE e_name='BLAKE');
+------+------------+
| d_no | d_location |
+------+------------+
| 30 | ShenZhen |
+------+------------+
1 row in set (0.00 sec)
- 使用连接查询,查询所有员工的部门和部门信息
mysql> SELECT e_no,e_name,dept_no,d_name,d_location
-> FROM employee,dept WHERE dept.d_no=employee.dept_no;
+------+--------+---------+------------+------------+
| e_no | e_name | dept_no | d_name | d_location |
+------+--------+---------+------------+------------+
| 1007 | CLARK | 10 | ACCOUNTING | ShangHai |
| 1009 | KING | 10 | ACCOUNTING | ShangHai |
| 1001 | SMITH | 20 | RESEARCH | BeiJing |
| 1004 | JONES | 20 | RESEARCH | BeiJing |
| 1008 | SCOTT | 20 | RESEARCH | BeiJing |
| 1011 | ADAMS | 20 | RESEARCH | BeiJing |
| 1002 | ALLEN | 30 | SALES | ShenZhen |
| 1003 | WARD | 30 | SALES | ShenZhen |
| 1005 | MARTIN | 30 | SALES | ShenZhen |
| 1006 | BLAKE | 30 | SALES | ShenZhen |
| 1010 | TURNER | 30 | SALES | ShenZhen |
| 1012 | JAMES | 30 | SALES | ShenZhen |
+------+--------+---------+------------+------------+
12 rows in set (0.00 sec)
- 在employee表中,计算每个部门各有多少名员工
mysql> SELECT dept_no,COUNT(*) FROM employee GROUP BY dept_no;
+---------+----------+
| dept_no | COUNT(*) |
+---------+----------+
| 10 | 2 |
| 20 | 4 |
| 30 | 6 |
+---------+----------+
3 rows in set (0.00 sec)
- 在employee表中,计算不同类型职工的总工资数
mysql> SELECT e_job,SUM(e_salary) FROM employee GROUP BY e_job;
+-----------+---------------+
| e_job | SUM(e_salary) |
+-----------+---------------+
| ANALYST | 3000 |
| CLERK | 2850 |
| MANAGER | 8275 |
| PRESIDENT | 5000 |
| SALESMAN | 4350 |
| SALSMAN | 1250 |
+-----------+---------------+
6 rows in set (0.00 sec)
- 在employee表中,计算不同部门的平均工资
mysql> SELECT dept_no,AVG(e_salary) FROM employee GROUP BY dept_no;
+---------+---------------+
| dept_no | AVG(e_salary) |
+---------+---------------+
| 10 | 3725.0000 |
| 20 | 1968.7500 |
| 30 | 1566.6667 |
+---------+---------------+
3 rows in set (0.00 sec)
- 在employee表中,查询工资低于1500的员工信息
mysql> SELECT * FROM employee WHERE e_salary<1500;
+------+--------+----------+---------+----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job | e_salary | hireDate |
+------+--------+----------+---------+----------+----------+------------+
| 1001 | SMITH | m | 20 | CLERK | 800 | 2005-11-12 |
| 1003 | WARD | f | 30 | SALESMAN | 1250 | 2001-05-12 |
| 1005 | MARTIN | m | 30 | SALSMAN | 1250 | 2001-06-12 |
| 1011 | ADAMS | m | 20 | CLERK | 1100 | 1999-10-05 |
| 1012 | JAMES | m | 30 | CLERK | 950 | 2008-06-15 |
+------+--------+----------+---------+----------+----------+------------+
5 rows in set (0.00 sec)
- 在employee表中,将查询记录先按部门编号由高到低排列,再按员工工资由高到低排列
mysql> SELECT e_name,dept_no,e_salary FROM employee
-> ORDER BY dept_no DESC,e_salary DESC;
+--------+---------+----------+
| e_name | dept_no | e_salary |
+--------+---------+----------+
| BLAKE | 30 | 2850 |
| ALLEN | 30 | 1600 |
| TURNER | 30 | 1500 |
| WARD | 30 | 1250 |
| MARTIN | 30 | 1250 |
| JAMES | 30 | 950 |
| SCOTT | 20 | 3000 |
| JONES | 20 | 2975 |
| ADAMS | 20 | 1100 |
| SMITH | 20 | 800 |
| KING | 10 | 5000 |
| CLARK | 10 | 2450 |
+--------+---------+----------+
12 rows in set (0.00 sec)
- 在employee表中,查询员工姓名以字母‘A’或‘S’开头的员工信息
mysql> SELECT * FROM employee WHERE e_name REGEXP'^[AS]';
+------+--------+----------+---------+----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job | e_salary | hireDate |
+------+--------+----------+---------+----------+----------+------------+
| 1001 | SMITH | m | 20 | CLERK | 800 | 2005-11-12 |
| 1002 | ALLEN | f | 30 | SALESMAN | 1600 | 2001-05-12 |
| 1008 | SCOTT | m | 20 | ANALYST | 3000 | 2003-05-12 |
| 1011 | ADAMS | m | 20 | CLERK | 1100 | 1999-10-05 |
+------+--------+----------+---------+----------+----------+------------+
4 rows in set (0.10 sec)
- 在employee表中,查询到目前为止,工龄大于等于十年的员工信息
mysql> SELECT * FROM employee WHERE YEAR(CURDATE())-YEAR(hireDate)>=10;
+------+--------+----------+---------+-----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job | e_salary | hireDate |
+------+--------+----------+---------+-----------+----------+------------+
| 1001 | SMITH | m | 20 | CLERK | 800 | 2005-11-12 |
| 1002 | ALLEN | f | 30 | SALESMAN | 1600 | 2001-05-12 |
| 1003 | WARD | f | 30 | SALESMAN | 1250 | 2001-05-12 |
| 1004 | JONES | m | 20 | MANAGER | 2975 | 1998-05-18 |
| 1005 | MARTIN | m | 30 | SALSMAN | 1250 | 2001-06-12 |
| 1006 | BLAKE | f | 30 | MANAGER | 2850 | 1997-02-15 |
| 1007 | CLARK | m | 10 | MANAGER | 2450 | 2002-09-12 |
| 1008 | SCOTT | m | 20 | ANALYST | 3000 | 2003-05-12 |
| 1009 | KING | f | 10 | PRESIDENT | 5000 | 1995-01-01 |
| 1010 | TURNER | f | 30 | SALESMAN | 1500 | 1997-10-12 |
| 1011 | ADAMS | m | 20 | CLERK | 1100 | 1999-10-05 |
| 1012 | JAMES | m | 30 | CLERK | 950 | 2008-06-15 |
+------+--------+----------+---------+-----------+----------+------------+
12 rows in set (0.17 sec)