MySQL数据库学习之查询操作详解

吾爱主题阅读：142 2024-04-01 23:20:11 评论：0

1.示例表内容

dept表：

1 2 3 4 5 6 7 8 + --------+------------+----------+ | DEPTNO | DNAME | LOC | + --------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | + --------+------------+----------+

emp表：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 + -------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | + -------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | + -------+--------+-----------+------+------------+---------+---------+--------+

salgrade表：

1 2 3 4 5 6 7 8 9 + -------+-------+-------+ | GRADE | LOSAL | HISAL | + -------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | + -------+-------+-------+

2.简单查询

MySQL 数据库使用SQL SELECT语句来查询数据。

例如我们要查询一个表的全部信息，可以这样做：

当然，这种方式进行查询的效率较低，我们更推荐您使用多列查询的方式：

1 2 3 4 5 6 7 8 9 10 mysql> select * from dept; + --------+------------+----------+ | DEPTNO | DNAME | LOC | + --------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | + --------+------------+----------+ 4 rows in set (0.00 sec)

那么，如果想查询单个列的信息呢？

可以指定列名进行查询：

查询多个列的信息，可以这样写：

3.给列起别名

在查询的过程中，我们还可以选择给列起一个别名：

也可以不加 as 关键字：

在所有的数据库中，字符串统一使用单引号，这是一个标准

4.列运算

在查询的时候，我们也可以直接进行列运算操作：

比如，我们想计算员工的年薪：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 mysql> select ename,sal*12 from emp; + --------+----------+ | ename | sal*12 | + --------+----------+ | SMITH | 9600.00 | | ALLEN | 19200.00 | | WARD | 15000.00 | | JONES | 35700.00 | | MARTIN | 15000.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | + --------+----------+ 14 rows in set (0.00 sec)

5.条件查询

示例1：查询工资大于等于3000的信息：

1 2 3 4 5 6 7 8 9 mysql> select empno,ename from emp where sal >= 3000; + -------+-------+ | empno | ename | + -------+-------+ | 7788 | SCOTT | | 7839 | KING | | 7902 | FORD | + -------+-------+ 3 rows in set (0.00 sec)

示例2：查询工资在2000到3000（包含2000和3000）的信息：

1 2 3 4 5 6 7 8 9 10 11 mysql> select empno,ename from emp where sal between 2000 and 3000; + -------+-------+ | empno | ename | + -------+-------+ | 7566 | JONES | | 7698 | BLAKE | | 7782 | CLARK | | 7788 | SCOTT | | 7902 | FORD | + -------+-------+ 5 rows in set (0.00 sec)

示例3：查询员工补助为空的（不为空为is not null）：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 mysql> select empno,ename from emp where comm is null ; + -------+--------+ | empno | ename | + -------+--------+ | 7369 | SMITH | | 7566 | JONES | | 7698 | BLAKE | | 7782 | CLARK | | 7788 | SCOTT | | 7839 | KING | | 7876 | ADAMS | | 7900 | JAMES | | 7902 | FORD | | 7934 | MILLER | + -------+--------+ 10 rows in set (0.00 sec)

Mysql比较NULL值不能使用=号

示例4：查询岗位为MANAGER并且工资大于等于2500的信息：

1 2 3 4 5 6 7 8 mysql> select * from emp where JOB = "MANAGER" and SAL >= 2500; + -------+-------+---------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | + -------+-------+---------+------+------------+---------+------+--------+ | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | + -------+-------+---------+------+------------+---------+------+--------+ 2 rows in set (0.00 sec)

示例5：查询岗位为MANAGER或者SALESMAN的员工：（使用关键字in）（不在某几个值之间使用not in）

1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> select empno,ename,job from emp where job in ( 'MANAGER' , 'SALESMAN' ); + -------+--------+----------+ | empno | ename | job | + -------+--------+----------+ | 7499 | ALLEN | SALESMAN | | 7521 | WARD | SALESMAN | | 7566 | JONES | MANAGER | | 7654 | MARTIN | SALESMAN | | 7698 | BLAKE | MANAGER | | 7782 | CLARK | MANAGER | | 7844 | TURNER | SALESMAN | + -------+--------+----------+ 7 rows in set (0.00 sec)

示例6：模糊查询，找出名字中含有字母o的：

示例7：模糊查询，找出名字以T结尾的：

1 2 3 4 5 6 7 mysql> select ename from emp where ename like '%T' ; + -------+ | ename | + -------+ | SCOTT | + -------+ 1 row in set (0.00 sec)

示例8：模糊查询，找出名字以K开头的：

1 2 3 4 5 6 7 mysql> select ename from emp where ename like 'K%' ; + -------+ | ename | + -------+ | KING | + -------+ 1 row in set (0.00 sec)