MySQL數(shù)據(jù)庫學(xué)習(xí)之查詢操作詳解

更新時(shí)間：2022年07月24日 09:29:20 作者：世界盡頭與你

這篇文章主要為大家詳細(xì)介紹一下MySQL數(shù)據(jù)庫中一些查詢操作，文中的示例代碼講解詳細(xì)，對我們學(xué)習(xí)MySQL有一定幫助，需要的可以參考一下

1.示例表內(nèi)容

dept表：

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

emp表：

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

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

2.簡單查詢

MySQL 數(shù)據(jù)庫使用SQL SELECT語句來查詢數(shù)據(jù)。

例如我們要查詢一個(gè)表的全部信息，可以這樣做：

當(dāng)然，這種方式進(jìn)行查詢的效率較低，我們更推薦您使用多列查詢的方式：

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)

那么，如果想查詢單個(gè)列的信息呢？

可以指定列名進(jìn)行查詢：

mysql> select DNAME from dept;
+------------+
| DNAME      |
+------------+
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+
4 rows in set (0.00 sec)

查詢多個(gè)列的信息，可以這樣寫：

mysql> select DNAME , LOC from dept;
+------------+----------+
| DNAME      | LOC      |
+------------+----------+
| ACCOUNTING | NEW YORK |
| RESEARCH   | DALLAS   |
| SALES      | CHICAGO  |
| OPERATIONS | BOSTON   |
+------------+----------+
4 rows in set (0.00 sec)

3.給列起別名

在查詢的過程中，我們還可以選擇給列起一個(gè)別名：

mysql> select DNAME as NAME from dept;
+------------+
| NAME       |
+------------+
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+
4 rows in set (0.00 sec)

也可以不加 as 關(guān)鍵字：

mysql> select DNAME NAME from dept;
+------------+
| NAME       |
+------------+
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+
4 rows in set (0.00 sec)

在所有的數(shù)據(jù)庫中，字符串統(tǒng)一使用單引號，這是一個(gè)標(biāo)準(zhǔn)

4.列運(yùn)算

在查詢的時(shí)候，我們也可以直接進(jìn)行列運(yùn)算操作：

比如，我們想計(jì)算員工的年薪：

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的信息：

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）的信息：

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：查詢員工補(bǔ)助為空的（不為空為is not null）：

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的信息：

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的員工：（使用關(guān)鍵字in）（不在某幾個(gè)值之間使用not in）

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的：

mysql> select ename from emp where ename like '%o%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD  |
+-------+
3 rows in set (0.00 sec)

示例7：模糊查詢，找出名字以T結(jié)尾的：

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

示例8：模糊查詢，找出名字以K開頭的：

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

示例9：模糊查詢，找出名字第二個(gè)字母是A的：

mysql> select ename from emp where ename like '_A%';
+--------+
| ename  |
+--------+
| WARD   |
| MARTIN |
| JAMES  |
+--------+
3 rows in set (0.00 sec)

示例10：模糊查詢，找出名字第三個(gè)字母是A的：

mysql> select ename from emp where ename like '__A%';
+-------+
| ename |
+-------+
| BLAKE |
| CLARK |
| ADAMS |
+-------+
3 rows in set (0.00 sec)

到此這篇關(guān)于MySQL數(shù)據(jù)庫學(xué)習(xí)之查詢操作詳解的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)庫查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

軟件下載

源碼下載

軟件編程

網(wǎng)絡(luò)編程

在線工具

數(shù)據(jù)庫

CMS

常用工具

MySQL數(shù)據(jù)庫學(xué)習(xí)之查詢操作詳解

目錄

1.示例表內(nèi)容

2.簡單查詢

3.給列起別名

4.列運(yùn)算

5.條件查詢

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具