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

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 |+---------+---------+