单表查询

MitSeek 发布于 2025-11-8 13:13 阅读：79 MYSQL

基础查询

mysql> CREATE DATABASE mitseek;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mitseek            |
| mysql              |
| performance_schema |
| qianfeng           |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> USE mitseek;
Database changed

mysql> CREATE TABLE stu(
sid CHAR(6) COMMENT '学生学号',
sname VARCHAR(100) COMMENT '学生姓名',
age TINYINT COMMENT '学生年龄',
gender VARCHAR(100) COMMENT '学生性别'
);

INSERT INTO stu(sid,sname,age,gender) VALUES('001','liyi1',21,'male'),
('002','liyi2',22,'female'),('007','liyi7',27,'male'),
('003','liyi3',23,'male'),('008','liyi8',28,'female'),
('004','liyi4',24,'male'),('009','liyi9',29,'male'),
('005','liyi5',25,'female'),('010','liyi10',30,'female'),
('006','liyi6',26,'male');

mysql> SELECT * FROM stu;
+------+--------+------+--------+
| sid  | sname  | age  | gender |
+------+--------+------+--------+
| 001  | liyi1  |   21 | male   |
| 002  | liyi2  |   22 | female |
| 007  | liyi7  |   27 | male   |
| 003  | liyi3  |   23 | male   |
| 008  | liyi8  |   28 | female |
| 004  | liyi4  |   24 | male   |
| 009  | liyi9  |   29 | male   |
| 005  | liyi5  |   25 | female |
| 010  | liyi10 |   30 | female |
| 006  | liyi6  |   26 | male   |
+------+--------+------+--------+
10 rows in set (0.00 sec)

SELECT * FROM stu ORDER BY CAST(sid AS UNSIGNED);
+------+--------+------+--------+
| sid  | sname  | age  | gender |
+------+--------+------+--------+
| 001  | liyi1  |   21 | male   |
| 002  | liyi2  |   22 | female |
| 003  | liyi3  |   23 | male   |
| 004  | liyi4  |   24 | male   |
| 005  | liyi5  |   25 | female |
| 006  | liyi6  |   26 | male   |
| 007  | liyi7  |   27 | male   |
| 008  | liyi8  |   28 | female |
| 009  | liyi9  |   29 | male   |
| 010  | liyi10 |   30 | female |
+------+--------+------+--------+
10 rows in set (0.03 sec)

CREATE TABLE emp(
empno INT COMMENT '员工编号',
ename VARCHAR(50) COMMENT '员工姓名',
job VARCHAR(50) COMMENT '员工工作',
mgr INT COMMENT '领导编号',
hiredate DATE COMMENT '入职日期',
sal DECIMAL(7,2) COMMENT '月薪',
comm DECIMAL(7,2) COMMENT '奖金',
deptno INT COMMENT '部门编号'
);

INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7769,'smith1','clerk',7912,'1981-11-24',1800,NULL,10),
(7379,'smith2','clerk',7922,'1982-02-23',2800,NULL,20),
(7367,'smith3','clerk',7932,'1983-01-22',3800,NULL,30),
(7389,'smith4','salesman',7942,'1984-03-21',4800,NULL,40),
(7869,'smith5','president',7952,'1985-04-20',4800,NULL,50),
(7368,'smith6','salesman',7962,'1986-05-19',5800,NULL,60),
(7399,'smith7','president',7972,'1987-07-18',6800,NULL,10),
(7969,'smith8','president',7982,'1988-06-17',7800,NULL,20),
(7339,'smith9','manager',7992,'1989-09-16',8800,NULL,30),
(7319,'smith10','salesman',7202,'1910-08-15',9800,NULL,30),
(7329,'smith11','manager',7802,'1920-10-14',8100,NULL,40),
(7169,'smith12','salesman',7302,'1930-11-13',8070,NULL,40),
(7069,'smith13','manager',7402,'1940-11-12',8001,NULL,50),
(7123,'smith14','manager',1902,'1950-09-11',7800,NULL,30),
(7360,'smith15','salesman',8902,'1960-09-10',8200,NULL,30);

CREATE TABLE dept(
deptno INT COMMENT '部门编码',
dname VARCHAR(50) COMMENT '部门名称',
loc VARCHAR(50) COMMENT '部门所在地址'
);

INSERT INTO dept(deptno,dname,loc) 
VALUES
(10,'ACCONUTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON');

查询所有字段

SELECT 字段名1，字段名2 FROM 表名;

SELECT sid,sname FROM stu;
+------+--------+
| sid  | sname  |
+------+--------+
| 001  | liyi1  |
| 002  | liyi2  |
| 007  | liyi7  |
| 003  | liyi3  |
| 008  | liyi8  |
| 004  | liyi4  |
| 009  | liyi9  |
| 005  | liyi5  |
| 010  | liyi10 |
| 006  | liyi6  |
+------+--------+
10 rows in set (0.00 sec)

SELECT * FROM stu;
+------+--------+------+--------+
| sid  | sname  | age  | gender |
+------+--------+------+--------+
| 001  | liyi1  |   21 | male   |
| 002  | liyi2  |   22 | female |
| 007  | liyi7  |   27 | male   |
| 003  | liyi3  |   23 | male   |
| 008  | liyi8  |   28 | female |
| 004  | liyi4  |   24 | male   |
| 009  | liyi9  |   29 | male   |
| 005  | liyi5  |   25 | female |
| 010  | liyi10 |   30 | female |
| 006  | liyi6  |   26 | male   |
+------+--------+------+--------+
10 rows in set (0.00 sec)

条件查询

SELECT 字段名1，字段名2 FROM 表名
WHERE 条件表达式

SELECT * FROM stu
WHERE gender='female';
+------+--------+------+--------+
| sid  | sname  | age  | gender |
+------+--------+------+--------+
| 002  | liyi2  |   22 | female |
| 008  | liyi8  |   28 | female |
| 005  | liyi5  |   25 | female |
| 010  | liyi10 |   30 | female |
+------+--------+------+--------+
4 rows in set (0.00 sec)

带AND关键字的查询

SELECT 字段名1，字段名2 FROM 表名
WHERE 条件表达式1 AND 条件表达式2；

SELECT * FROM stu
WHERE age>20 AND gender='male';
+------+-------+------+--------+
| sid  | sname | age  | gender |
+------+-------+------+--------+
| 001  | liyi1 |   21 | male   |
| 007  | liyi7 |   27 | male   |
| 003  | liyi3 |   23 | male   |
| 004  | liyi4 |   24 | male   |
| 009  | liyi9 |   29 | male   |
| 006  | liyi6 |   26 | male   |
+------+-------+------+--------+
6 rows in set (0.03 sec)

带OR关键字的查询

SELECT 字段名1，字段名2 FROM 表名
WHERE 条件表达1 OR 条件表达式2；

SELECT * FROM stu
WHERE sid='001' OR sname='liyi6';
+------+-------+------+--------+
| sid  | sname | age  | gender |
+------+-------+------+--------+
| 001  | liyi1 |   21 | male   |
| 006  | liyi6 |   26 | male   |
+------+-------+------+--------+
2 rows in set (0.00 sec)

带IN或NOT IN 关键字的查询

SELECT 字段名1，字段名2 FROM 表名
WHERE 字段名 [NOT]IN(元素1，元素2);

SELECT * FROM stu
WHERE sid in(001,002,003);
+------+-------+------+--------+
| sid  | sname | age  | gender |
+------+-------+------+--------+
| 001  | liyi1 |   21 | male   |
| 002  | liyi2 |   22 | female |
| 003  | liyi3 |   23 | male   |
+------+-------+------+--------+
3 rows in set (0.03 sec)

SELECT * FROM stu
WHERE sid NOT IN(001,002,003);
+------+--------+------+--------+
| sid  | sname  | age  | gender |
+------+--------+------+--------+
| 007  | liyi7  |   27 | male   |
| 008  | liyi8  |   28 | female |
| 004  | liyi4  |   24 | male   |
| 009  | liyi9  |   29 | male   |
| 005  | liyi5  |   25 | female |
| 010  | liyi10 |   30 | female |
| 006  | liyi6  |   26 | male   |
+------+--------+------+--------+
7 rows in set (0.00 sec)

带IS NULL或IS NOT NULL关键字的查询

SELECT 字段名1，字段名2 FROM 表名
WHERE 字段名 IS [NOT] NULL

SELECT * FROM stu
WHERE age IS NULL;

SELECT * FROM stu
WHERE age IS NOT NULL;

带BETWEEN AND 关键字的查询（范围之内）

SELECT * FROM stu
WHERE age BETWEEN 21  AND  25;
+------+-------+------+--------+
| sid  | sname | age  | gender |
+------+-------+------+--------+
| 001  | liyi1 |   21 | male   |
| 002  | liyi2 |   22 | female |
| 003  | liyi3 |   23 | male   |
| 004  | liyi4 |   24 | male   |
| 005  | liyi5 |   25 | female |
+------+-------+------+--------+
5 rows in set (0.00 sec)

带LIKE 关键字的查询（模糊查询）

——代表一个字符
%表示0-N个字符

SELECT * FROM stu
WHERE sname LIKE '________';
+------+-------+------+--------+
| sid  | sname | age  | gender |
+------+-------+------+--------+
| 001  | liyi1 |   21 | male   |
| 002  | liyi2 |   22 | female |
| 007  | liyi7 |   27 | male   |
| 003  | liyi3 |   23 | male   |
| 008  | liyi8 |   28 | female |
| 004  | liyi4 |   24 | male   |
| 009  | liyi9 |   29 | male   |
| 005  | liyi5 |   25 | female |
| 006  | liyi6 |   26 | male   |
+------+-------+------+--------+
9 rows in set (0.00 sec)

SELECT * FROM stu
WHERE sname LIKE '%i%';
+------+--------+------+--------+
| sid  | sname  | age  | gender |
+------+--------+------+--------+
| 001  | liyi1  |   21 | male   |
| 002  | liyi2  |   22 | female |
| 007  | liyi7  |   27 | male   |
| 003  | liyi3  |   23 | male   |
| 008  | liyi8  |   28 | female |
| 004  | liyi4  |   24 | male   |
| 009  | liyi9  |   29 | male   |
| 005  | liyi5  |   25 | female |
| 010  | liyi10 |   30 | female |
| 006  | liyi6  |   26 | male   |
+------+--------+------+--------+
10 rows in set (0.00 sec)

带DISTINCT关键字的查询（去除重复数据）

SELECT DISIONCT sal FROM emp;

高级查询

排序查询

升序

SELECT * FROM stu
ORDER BY age ASC;
+------+--------+------+--------+
| sid  | sname  | age  | gender |
+------+--------+------+--------+
| 001  | liyi1  |   21 | male   |
| 002  | liyi2  |   22 | female |
| 003  | liyi3  |   23 | male   |
| 004  | liyi4  |   24 | male   |
| 005  | liyi5  |   25 | female |
| 006  | liyi6  |   26 | male   |
| 007  | liyi7  |   27 | male   |
| 008  | liyi8  |   28 | female |
| 009  | liyi9  |   29 | male   |
| 010  | liyi10 |   30 | female |
+------+--------+------+--------+
10 rows in set (0.00 sec)

降序
SELECT * FROM stu
ORDER BY sid DESC;
+------+--------+------+--------+
| sid  | sname  | age  | gender |
+------+--------+------+--------+
| 010  | liyi10 |   30 | female |
| 009  | liyi9  |   29 | male   |
| 008  | liyi8  |   28 | female |
| 007  | liyi7  |   27 | male   |
| 006  | liyi6  |   26 | male   |
| 005  | liyi5  |   25 | female |
| 004  | liyi4  |   24 | male   |
| 003  | liyi3  |   23 | male   |
| 002  | liyi2  |   22 | female |
| 001  | liyi1  |   21 | male   |
+------+--------+------+--------+
10 rows in set (0.00 sec)

聚合函数

COUNT() 返回某列的行数
SUM() 返回某列值的和
AVG()返回某列的平均值
MAX()返回某列的最大值
MIN()返回某列的最小值

 SELECT COUNT(*) FROM emp;
 +----------+
| COUNT(*) |
+----------+
|       15 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT * FROM emp;
+-------+---------+-----------+------+------------+---------+------+--------+
| empno | ename   | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+---------+-----------+------+------------+---------+------+--------+
|  7769 | smith1  | clerk     | 7912 | 1981-11-24 | 1800.00 | NULL |     10 |
|  7379 | smith2  | clerk     | 7922 | 1982-02-23 | 2800.00 | NULL |     20 |
|  7367 | smith3  | clerk     | 7932 | 1983-01-22 | 3800.00 | NULL |     30 |
|  7389 | smith4  | salesman  | 7942 | 1984-03-21 | 4800.00 | NULL |     40 |
|  7869 | smith5  | president | 7952 | 1985-04-20 | 4800.00 | NULL |     50 |
|  7368 | smith6  | salesman  | 7962 | 1986-05-19 | 5800.00 | NULL |     60 |
|  7399 | smith7  | president | 7972 | 1987-07-18 | 6800.00 | NULL |     10 |
|  7969 | smith8  | president | 7982 | 1988-06-17 | 7800.00 | NULL |     20 |
|  7339 | smith9  | manager   | 7992 | 1989-09-16 | 8800.00 | NULL |     30 |
|  7319 | smith10 | salesman  | 7202 | 1910-08-15 | 9800.00 | NULL |     30 |
|  7329 | smith11 | manager   | 7802 | 1920-10-14 | 8100.00 | NULL |     40 |
|  7169 | smith12 | salesman  | 7302 | 1930-11-13 | 8070.00 | NULL |     40 |
|  7069 | smith13 | manager   | 7402 | 1940-11-12 | 8001.00 | NULL |     50 |
|  7123 | smith14 | manager   | 1902 | 1950-09-11 | 7800.00 | NULL |     30 |
|  7360 | smith15 | salesman  | 8902 | 1960-09-10 | 8200.00 | NULL |     30 |
+-------+---------+-----------+------+------------+---------+------+--------+
15 rows in set (0.00 sec)

mysql> SELECT SUM(sal) FROM emp;
+----------+
| SUM(sal) |
+----------+
| 97171.00 |
+----------+
1 row in set (0.00 sec)

分组查询（group by分组查询，order排序查询）

SELECT * FROM stu
GROUP BY gender;
+------+-------+------+--------+
| sid  | sname | age  | gender |
+------+-------+------+--------+
| 002  | liyi2 |   22 | female |
| 001  | liyi1 |   21 | male   |
+------+-------+------+--------+
2 rows in set (0.00 sec)
SELECT deptno,SUM(sal) FROM emp
GROUP BY deptno;
+--------+----------+
| deptno | SUM(sal) |
+--------+----------+
|     10 |  8600.00 |
|     20 | 10600.00 |
|     30 | 38400.00 |
|     40 | 20970.00 |
|     50 | 12801.00 |
|     60 |  5800.00 |
+--------+----------+
6 rows in set (0.00 sec)
SELECT deptno,COUNT(*) FROM emp
WHERE sal>1500
GROUP BY deptno;
+--------+----------+
| deptno | COUNT(*) |
+--------+----------+
|     10 |        2 |
|     20 |        2 |
|     30 |        5 |
|     40 |        3 |
|     50 |        2 |
|     60 |        1 |
+--------+----------+
6 rows in set (0.00 sec)

HAVING子句

查询员工表中工资总和大于9000元的部门编号及工资和

SELECT deptno,SUM(sal) FROM emp
GROUP BY deptno
HAVING SUM(sal)>7200;
+--------+----------+
| deptno | SUM(sal) |
+--------+----------+
|     10 |  8600.00 |
|     20 | 10600.00 |
|     30 | 38400.00 |
|     40 | 20970.00 |
|     50 | 12801.00 |
+--------+----------+
5 rows in set (0.00 sec)

LIMIT 分页查询

SELECT * FROM stu LIMIT 0,5;

mysql> SELECT * FROM stu;
+------+--------+------+--------+
| sid  | sname  | age  | gender |
+------+--------+------+--------+
| 001  | liyi1  |   21 | male   |
| 002  | liyi2  |   22 | female |
| 007  | liyi7  |   27 | male   |
| 003  | liyi3  |   23 | male   |
| 008  | liyi8  |   28 | female |
| 004  | liyi4  |   24 | male   |
| 009  | liyi9  |   29 | male   |
| 005  | liyi5  |   25 | female |
| 010  | liyi10 |   30 | female |
| 006  | liyi6  |   26 | male   |
+------+--------+------+--------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM stu LIMIT 0,5;
+------+-------+------+--------+
| sid  | sname | age  | gender |
+------+-------+------+--------+
| 001  | liyi1 |   21 | male   |
| 002  | liyi2 |   22 | female |
| 007  | liyi7 |   27 | male   |
| 003  | liyi3 |   23 | male   |
| 008  | liyi8 |   28 | female |
+------+-------+------+--------+
5 rows in set (0.00 sec)

查后5条记录

SELECT * FROM stu
LIMIT 5,5;
+------+--------+------+--------+
| sid  | sname  | age  | gender |
+------+--------+------+--------+
| 004  | liyi4  |   24 | male   |
| 009  | liyi9  |   29 | male   |
| 005  | liyi5  |   25 | female |
| 010  | liyi10 |   30 | female |
| 006  | liyi6  |   26 | male   |
+------+--------+------+--------+
5 rows in set (0.00 sec)

MySQL