mysql数据库的select单表查询语句

最新推荐文章于 2024-08-15 11:17:44 发布

men_wen

最新推荐文章于 2024-08-15 11:17:44 发布

阅读量7.8k

点赞数 1

分类专栏： MySQL 文章标签：数据 select mysql 数据库

本文链接：https://blog.csdn.net/men_wen/article/details/56481347

版权

MySQL 专栏收录该内容

8 篇文章 1 订阅

订阅专栏

SELECT单表查询

1. 添加查询数据

//先创建3个表，具有如下属性
create table DEPT
(
  deptno INT(2) zerofill not null,
  dname  VARCHAR(14),
  loc    VARCHAR(13)
);

alter table DEPT
  add constraint PK_DEPT primary key (DEPTNO);

create table EMP
(
  empno    INT(4) zerofill not null,
  ename    VARCHAR(10),
  job      VARCHAR(9),
  mgr      INT(4) zerofill,
  hiredate DATETIME,
  sal      DECIMAL(7,2),
  comm     DECIMAL(7,2),
  deptno   INT(2) zerofill
);

alter table EMP
  add constraint PK_EMP primary key (EMPNO);

create table SALGRADE
(
  grade INT,
  losal INT,
  hisal INT
);

2. 查询SELECT

2.1 查询的基本格式

SELECT <列名>
FROM <表名>
[WHERE <查询条件表达式>]
[ORDER BY <排序的列名>[ASC或DESC]]
例如：
SELECT sname //列名称
FROM t_user   //表名
WHERE SSEX = '男' //过滤条件
ORDER BY nage;   //排序条件

2.2 查询指定列

select 列名称 from 表;

mysql> select empno,ename,job from EMP;
+-------+--------+-----------+
| empno | ename  | job       |
+-------+--------+-----------+
|  7369 | SMITH  | CLERK     |
|  7499 | ALLEN  | SALESMAN  |
|  7521 | WARD   | SALESMAN  |
|  7566 | JONES  | MANAGER   |
+-------+--------+-----------+

2.3 查询所有列

select * from 表;
mysql> select * from EMP;

+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+

2.4 数据查询列（别名）

mysql> select empno as '工号', ename '姓名' from EMP;
+--------+--------+
| 工号   | 姓名   |
+--------+--------+
|   7369 | SMITH  |
|   7499 | ALLEN  |
|   7566 | JONES  |
+--------+--------+
//此时的empno显示为工号，ename显示为姓名

2.5 数据查询列（常量列）

mysql> select empno, 'mysql数据库'  from EMP;
+-------+----------------+
| empno | mysql数据库    |
+-------+----------------+
|  7369 | mysql数据库    |
|  7499 | mysql数据库    |
|  7521 | mysql数据库    |
+-------+----------------+

2.6 数据查询列（连接符函数）

mysql> select empno,concat(ename, '@') ename from EMP;
+-------+---------+
| empno | ename   |
+-------+---------+
|  7369 | SMITH@  |
|  7499 | ALLEN@  |
|  7521 | WARD@   |
+-------+---------+

3. 查询WHERE

3.1部分行查询where

mysql> select empno,ename,job from EMP where ename = 'SMITH';
//查找名字叫SMITH的empno，ename，job.
//'<>'表示不等号，或者 '!=' 号
+-------+-------+-------+
| empno | ename | job   |
+-------+-------+-------+
|  7369 | SMITH | CLERK |
+-------+-------+-------+

mysql> select empno,ename,sal from EMP where sal <= 1500;
//查找出薪水少于1500的empno，ename，sal
+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7369 | SMITH  |  800.00 |
|  7521 | WARD   | 1250.00 |
|  7654 | MARTIN | 1250.00 |
+-------+--------+---------+

3.2 数据查询条件（and, or）

mysql> select * from EMP where deptno = 30 and sal > 1500;
//查询deptno=30并且sal>1500
+-------+-------+----------+------+---------------------+---------+--------+--------+
| empno | ename | job      | mgr  | hiredate            | sal     | comm   | deptno |
+-------+-------+----------+------+---------------------+---------+--------+--------+
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 |
|  7698 | BLAKE | MANAGER  | 7839 | 1981-05-01 00:00:00 | 2850.00 |   NULL |     30 |
+-------+-------+----------+------+---------------------+---------+--------+--------+


mysql> select * from EMP where job = 'MANAGER' or job = 'SALESMAN';
//查询出job是manager或这是salesman的数据。
+-------+--------+----------+------+---------------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredate            | sal     | comm    | deptno |
+-------+--------+----------+------+---------------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |

3.3数据查询between and

mysql> select ename,sal from EMP where sal between 800 and 1500;
//查询工资介于800到1500之间的数据
//等价于select ename,sal from EMP where sal >= 800 and sal <= 1500
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
+--------+---------+

3.4数据查询查询空值

mysql> select empno,ename,sal,comm from EMP where comm is null;
//查询出comm是null的，0不属于null
//非空则用not null
+-------+--------+---------+------+
| empno | ename  | sal     | comm |
+-------+--------+---------+------+
|  7369 | SMITH  |  800.00 | NULL |
|  7566 | JONES  | 2975.00 | NULL |

3.5数据查询in

mysql> mysql> select ename,empno from EMP where ename in ('SMITH', 'KING');
//in + (元素集合)，找出ename等于SMITH或KING的数据
+-------+-------+
| ename | empno |
+-------+-------+
| SMITH |  7369 |
| KING  |  7839 |
+-------+-------+

3.6数据查询like（模糊查询）

查询时，字段中的内容并不一定与查询内容完全匹配，只要字段中含有这些内容
通配符: ‘%’ （0个或多个字符）
通配符: ‘_’ （单个字符）

mysql> select ename,empno from EMP where ename like 'S%';
//查询ename以S开头的数据
+-------+-------+
| ename | empno |
+-------+-------+
| SMITH |  7369 |
| SCOTT |  7788 |
+-------+-------+

mysql> select ename,empno from EMP where ename like 'S_ITH';
//查询ename以S开头，ITH为结尾，且两者中间差一个字符的数据，通配符'_'只能表示一个字符
+-------+-------+
| ename | empno |
+-------+-------+
| SMITH |  7369 |
+-------+-------+

3.7数据查询排序

按单列名排序

mysql> select empno,ename,job from EMP order by ename desc;
//按ename字段降序排序
//desc为降序，asc为升序
//等价于select empno,ename,job from EMP order by 2 desc
+-------+--------+-----------+
| empno | ename  | job       |
+-------+--------+-----------+
|  7521 | WARD   | SALESMAN  |
|  7844 | TURNER | SALESMAN  |
|  7369 | SMITH  | CLERK     |
+-------+--------+-----------+

按多个列名排序

mysql> select empno,ename,job from EMP order by job asc, sal desc;
//按工作名称升序，薪水降序排列
+-------+--------+-----------+
| empno | ename  | job       |
+-------+--------+-----------+
|  7788 | SCOTT  | ANALYST   |
|  7902 | FORD   | ANALYST   |
|  7369 | SMITH  | CLERK     |
|  7566 | JONES  | MANAGER   |
|  7839 | KING   | PRESIDENT |
|  7844 | TURNER | SALESMAN  |
|  7521 | WARD   | SALESMAN  |
+-------+--------+-----------+

4. 查询限制

4.1 limit关键字

limit可以限制查询结果，筛选出目标行，相当于分页查询。
SELECT * FROM table LIMIT [offset] rows | rows OFFSET offset

mysql> select empno,ename,sal from EMP ORDER BY sal limit 3;
//当limit后面只有一个参数时，限制查询结果的前三条
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7369 | SMITH |  800.00 |
|  7900 | JAMES |  950.00 |
|  7876 | ADAMS | 1100.00 |
+-------+-------+---------+

mysql> select empno,ename,sal from EMP ORDER BY sal limit 0,3;
//当limit后有两个参数时，第一个参数时行的偏移量，就是从第几行开始，第二个参数是显示多少行。
//因此，limit 3 <=等价=> limit 0,3 
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7369 | SMITH |  800.00 |
|  7900 | JAMES |  950.00 |
|  7876 | ADAMS | 1100.00 |
+-------+-------+---------+

4.2 distinct关键字

distinct关键字的英文意思是有区别的，因此在SQL语句中可以去重。
一般情况下默认为不去重，例如：
select job,deptno from EMP;
select all job,deptno from EMP;

mysql> select distinct job from EMP;
//distinct作用于单列
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+

mysql> select distinct job,deptno from EMP;
//distinct 作用于多列，使得每一行都是唯一的。
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| CLERK     |     30 |
| CLERK     |     10 |
+-----------+--------+

4.3 UNION关键字

UNION (无重复并集)：当执行UNION 时，自动去掉结果集中的重复行。
union:联合的意思，即把两次或多次查询结果合并起来。
要求：两次查询的列数必须一致

mysql> select empno,ename,job from EMP where job='SALESMAN'
    -> union
    -> select empno,ename,job from EMP where job='MANAGER';
+-------+--------+----------+
| empno | ename  | job      |
+-------+--------+----------+
|  7499 | ALLEN  | SALESMAN |
|  7521 | WARD   | SALESMAN |
|  7654 | MARTIN | SALESMAN |
|  7844 | TURNER | SALESMAN |
|  7566 | JONES  | MANAGER  |
|  7698 | BLAKE  | MANAGER  |
|  7782 | CLARK  | MANAGER  |
+-------+--------+----------+
//union关键字类似与or
//mysql> select empno,ename,job from EMP where job='SALESMAN' or job='MANAGER';
//UNION 加 ORDER BY 语句 可以对结果进行排序
mysql> select empno,ename,job from EMP where job='SALESMAN' union select empno,ename,job from EMP where job='MANAGER' ORDER BY ename;
+-------+--------+----------+
| empno | ename  | job      |
+-------+--------+----------+
|  7499 | ALLEN  | SALESMAN |
|  7698 | BLAKE  | MANAGER  |
|  7782 | CLARK  | MANAGER  |
|  7566 | JONES  | MANAGER  |
|  7654 | MARTIN | SALESMAN |
|  7844 | TURNER | SALESMAN |
|  7521 | WARD   | SALESMAN |
+-------+--------+----------+

4.4 UNION ALL关键字

UNION ALL (有重复并集)：不去掉重复行，并且不对结果集进行排序。

mysql> select job,sal from EMP where empno = 7902
    -> union
    -> select job,sal from EMP where empno = 7788;
//当用UNION语句查询的结果一致时，结果去重，只显示一行
+---------+---------+
| job     | sal     |
+---------+---------+
| ANALYST | 3000.00 |
+---------+---------+

//当使用UNION ALL语句查询的结果一致时，结果不去重，全部显示。
mysql> select job,sal from EMP where empno = 7902
    -> union all
    -> select job,sal from EMP where empno = 7788;
+---------+---------+
| job     | sal     |
+---------+---------+
| ANALYST | 3000.00 |
| ANALYST | 3000.00 |
+---------+---------+