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 <排序的列名>[ASCDESC]]
例如:
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 |
+---------+---------+

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值