SQL-DQL之条件查询(2)

1.条件查询:

语法:
#(1)(2)(3) 代表执行步骤。
  select
        查询列表 (3)
   from
       表名  (1)
   where
      筛选条件;(2)

2.筛选条件的分类

  • 一. 按条件表达式筛选
    简单条件运算符:> , <, = , >= , <= , <>(不等于), !=,<=>(安全等于)。

  • 二. 按逻辑表达式筛选(用于连接条件表达式)
    逻辑运算符:&& , || , !(与,或,非)
    或者是 :and ,or,not(mysql推荐使用)

  • 三. 模糊查询:

    • like(通常与通配符使用'%','_')
    • between and(包含临界值)
    • in(判断某一字段的值是否属于in列表中的某一项)
    • is null && is not null 可以判断Null值。
  • 注意

  • is null pk <=>.

  • is null:仅仅可以判断NULL值,可读性高。

  • <=>:既可以判断null值,也可以判断普通数值,可读性较低。

√一. 按条件表达式筛选的练习

 # 案例1 查询月薪大于12000的员工信息;
 mysql> select * from employees where salary >12000;
+-------------+------------+-----------+----------+--------------------+---------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email    | phone_number       | job_id  | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+------------+-----------+----------+--------------------+---------+----------+----------------+------------+---------------+---------------------+
|         100 | Steven     | K_ing     | SKING    | 515.123.4567       | AD_PRES | 24000.00 |           NULL |       NULL |            90 | 1992-04-03 00:00:00 |
|         101 | Neena      | Kochhar   | NKOCHHAR | 515.123.4568       | AD_VP   | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
|         102 | Lex        | De Haan   | LDEHAAN  | 515.123.4569       | AD_VP   | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
...
...


 #案例2 查询部门编号不等于90的员工名和部门编号  
 mysql> select last_name,department_id from employees where department_id <>90;
+-------------+---------------+
| last_name   | department_id |
+-------------+---------------+
| Hunold      |            60 |
| Ernst       |            60 |
| Austin      |            60 |
| Pataballa   |            60 |
| Lorentz     |            60 |
| Greenberg   |           100 |
| Faviet      |           100 |
| Chen        |           100 |
| Sciarra     |           100 |
| Urman       |           100 |
| Popp        |           100 |
| Raphaely    |            30 |
| Khoo        |            30 |
| Baida       |            30 |
 ...
 ...
 ...

103 rows in set (0.37 sec)

√二. 按逻辑表达式筛选的练习

 案例一:查询工资在10000到20000之间的员工名,工资以及奖金
 mysql> select last_name, salary ,commission_pct from employees where salary >=10000 AND salary <=20000;
+-----------+----------+----------------+
| last_name | salary   | commission_pct |
+-----------+----------+----------------+
| Kochhar   | 17000.00 |           NULL |
| De Haan   | 17000.00 |           NULL |
| Greenberg | 12000.00 |           NULL |
| Raphaely  | 11000.00 |           NULL |
| Russell   | 14000.00 |           0.40 |
| Partners  | 13500.00 |           0.30 |
| Errazuriz | 12000.00 |           0.30 |
| Cambrault | 11000.00 |           0.30 |
| Zlotkey   | 10500.00 |           0.20 |
| Tucker    | 10000.00 |           0.30 |
| K_ing     | 10000.00 |           0.35 |
| Vishney   | 10500.00 |           0.25 |
| Ozer      | 11500.00 |           0.25 |
| Bloom     | 10000.00 |           0.20 |
| Abel      | 11000.00 |           0.30 |
| Hartstein | 13000.00 |           NULL |
| Baer      | 10000.00 |           NULL |
| Higgins   | 12000.00 |           NULL |
+-----------+----------+----------------+
18 rows in set (0.00 sec)

案例2:查询部门编号不在90到110之间,工资高于15000的员工信息;
mysql> select * from employees where department_id <= 90 AND department_id >=110 or salary > 15000;
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email    | phone_number | job_id  | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
|         100 | Steven     | K_ing     | SKING    | 515.123.4567 | AD_PRES | 24000.00 |           NULL |       NULL |            90 | 1992-04-03 00:00:00 |
|         101 | Neena      | Kochhar   | NKOCHHAR | 515.123.4568 | AD_VP   | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
|         102 | Lex        | De Haan   | LDEHAAN  | 515.123.4569 | AD_VP   | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
3 rows in set (0.01 sec)

√三. 模糊查询的练习

#√1.like案例:
案例一:查询员工名字含有a字母的员工信息。
mysql> select * from employees where last_name like'%a%';
+-------------+-------------+------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name  | last_name  | email    | phone_number       | job_id     | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+-------------+------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
|         101 | Neena       | Kochhar    | NKOCHHAR | 515.123.4568       | AD_VP      | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
|         102 | Lex         | De Haan    | LDEHAAN  | 515.123.4569       | AD_VP      | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
|         105 | David       | Austin     | DAUSTIN  | 590.423.4569       | IT_PROG    |  4800.00 |           NULL |        103 |            60 | 1998-03-03 00:00:00 |
|         106 | Valli       | Pataballa  | VPATABAL | 590.423.4560       | IT_PROG    |  4800.00 |           NULL |        103 |            60 | 1998-03-03 00:00:00 |
|         109 | Daniel      | Faviet     | DFAVIET  | 515.124.4169       | FI_ACCOUNT |  9000.00 |           NULL |        108 |           100 | 1998-03-03 00:00:00 |
|         111 | Ismael      | Sciarra    | ISCIARRA | 515.124.4369       | FI_ACCOUNT |  7700.00 |           NULL |        108 |           100 | 2000-09-09 00:00:00 |
|         112 | Jose Manuel | Urman      | JMURMAN  | 515.124.4469       | FI_ACCOUNT |  7800.00 |           NULL |        108 |           100 | 2000-09-09 00:00:00 |
|         114 | Den         | Raphaely   | DRAPHEAL | 515.127.4561       | PU_MAN     | 11000.00 |           NULL |        100 |            30 | 2000-09-09 00:00:00 |
|         116 | Shelli      | Baida      | SBAIDA   | 515.127.4563       | PU_CLERK   |  2900.00 |           NULL |        114 |            30 | 2000-09-09 00:00:00 |
|         117 | Sigal       | Tobias     | STOBIAS  | 515.127.4564       | PU_CLERK   |  2800.00 |           NULL |        114 |            30 | 2000-09-09 00:00:00 |
|         119 | Karen       | Colmenares | KCOLMENA | 515.127.4566       | PU_CLERK   |  2500.00 |           NULL |        114 |            30 | 2000-09-09 00:00:00 |
|         122 | Payam       | Kaufling   | PKAUFLIN | 650.123.3234       | ST_MAN     |  7900.00 |           NULL |        100 |            50 | 2004-02-06 00:00:00 |
...
...

案例二:查询员工名字中第三个字母为n,第五个字母为l的员工名和工资

mysql> select last_name,salary from employees where last_name like '__i_a%';
+-----------+---------+
| last_name | salary  |
+-----------+---------+
| Baida     | 2900.00 |
+-----------+---------+
1 row in set (0.00 sec)

案例三:查询员工名中第二个字母为_的员工名
mysql> select last_name from employees where last_name like '_$_%' ESCAPE'$';
+-----------+
| last_name |
+-----------+
| K_ing     |
| K_ing     |
+-----------+
2 rows in set (0.00 sec)

#√2.between and
案例一:查询编号在100到120之间的员工信息
mysql> select * from employees where department_id between 100 and 120;
+-------------+-------------+-----------+----------+--------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name  | last_name | email    | phone_number | job_id     | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+-------------+-----------+----------+--------------+------------+----------+----------------+------------+---------------+---------------------+
|         108 | Nancy       | Greenberg | NGREENBE | 515.124.4569 | FI_MGR     | 12000.00 |           NULL |        101 |           100 | 1998-03-03 00:00:00 |
|         109 | Daniel      | Faviet    | DFAVIET  | 515.124.4169 | FI_ACCOUNT |  9000.00 |           NULL |        108 |           100 | 1998-03-03 00:00:00 |
|         110 | John        | Chen      | JCHEN    | 515.124.4269 | FI_ACCOUNT |  8200.00 |           NULL |        108 |           100 | 2000-09-09 00:00:00 |
|         111 | Ismael      | Sciarra   | ISCIARRA | 515.124.4369 | FI_ACCOUNT |  7700.00 |           NULL |        108 |           100 | 2000-09-09 00:00:00 |
|         112 | Jose Manuel | Urman     | JMURMAN  | 515.124.4469 | FI_ACCOUNT |  7800.00 |           NULL |        108 |           100 | 2000-09-09 00:00:00 |
|         113 | Luis        | Popp      | LPOPP    | 515.124.4567 | FI_ACCOUNT |  6900.00 |           NULL |        108 |           100 | 2000-09-09 00:00:00 |
|         205 | Shelley     | Higgins   | SHIGGINS | 515.123.8080 | AC_MGR     | 12000.00 |           NULL |        101 |           110 | 2016-03-03 00:00:00 |
|         206 | William     | Gietz     | WGIETZ   | 515.123.8181 | AC_ACCOUNT |  8300.00 |           NULL |        205 |           110 | 2016-03-03 00:00:00 |
+-------------+-------------+-----------+----------+--------------+------------+----------+----------------+------------+---------------+---------------------+
8 rows in set (0.37 sec)

mysql> 

#√3.in
案例一:查询员工工种号是 IT_PROG,AD_VP,AD_PRES中的员工名和工种编号;
mysql> select last_name,job_id from employees where job_id in('IT_PROG','AD_VP','AD_PRES');//里面的字符值要用单引号引起来。
+-----------+---------+
| last_name | job_id  |
+-----------+---------+
| K_ing     | AD_PRES |
| Kochhar   | AD_VP   |
| De Haan   | AD_VP   |
| Hunold    | IT_PROG |
| Ernst     | IT_PROG |
| Austin    | IT_PROG |
| Pataballa | IT_PROG |
| Lorentz   | IT_PROG |
+-----------+---------+
8 rows in set (0.34 sec)

mysql> 

#√4.is null
案例1:查询没有奖金的员工名和奖金率;
mysql> select last_name ,commission_pct from employees where commission_pct is null;
+-------------+----------------+
| last_name   | commission_pct |
+-------------+----------------+
| K_ing       |           NULL |
| Kochhar     |           NULL |
| De Haan     |           NULL |
| Hunold      |           NULL |
| Ernst       |           NULL |
| Austin      |           NULL |
| Pataballa   |           NULL |
| Lorentz     |           NULL |
| Greenberg   |           NULL |
| Faviet      |           NULL |
| Chen        |           NULL |
| Sciarra     |           NULL |
| Urman       |           NULL |
| Popp        |           NULL |
| Raphaely    |           NULL |
...
...
或者用<=>也可以达到想要的效果。
mysql> select last_name ,commission_pct from employees where commission_pct <=> null;


#√is not null
案例1:查询有奖金的员工名和奖金率;
mysql> select last_name ,commission_pct from employees where commission_pct is not null;
+------------+----------------+
| last_name  | commission_pct |
+------------+----------------+
| Russell    |           0.40 |
| Partners   |           0.30 |
| Errazuriz  |           0.30 |
| Cambrault  |           0.30 |
| Zlotkey    |           0.20 |
| Tucker     |           0.30 |
| Bernstein  |           0.25 |
| Hall       |           0.25 |
| Olsen      |           0.20 |
| Cambrault  |           0.20 |
| Tuvault    |           0.15 |
| K_ing      |           0.35 |
| Sully      |           0.35 |
| McEwen     |           0.35 |
| Smith      |           0.30 |
| Doran      |           0.30 |
| Sewall     |           0.25 |
| Vishney    |           0.25 |
| Greene     |           0.15 |
| Marvins    |           0.10 |
| Lee        |           0.10 |
| Ande       |           0.10 |
| Banda      |           0.10 |
| Ozer       |           0.25 |
| Bloom      |           0.20 |
| Fox        |           0.20 |
| Smith      |           0.15 |
| Bates      |           0.15 |
| Kumar      |           0.10 |
| Abel       |           0.30 |
| Hutton     |           0.25 |
| Taylor     |           0.20 |
| Livingston |           0.20 |
| Grant      |           0.15 |
| Johnson    |           0.10 |
+------------+----------------+
35 rows in set (0.00 sec)

√四.其它练习

查询员工的名字和部门号和年薪
mysql> select last_name, department_id, salary*12*(1+Ifnull(commission_pct,0)) as 年薪 from employees;
+-------------+---------------+-----------+
| last_name   | department_id | 年薪      |
+-------------+---------------+-----------+
| K_ing       |            90 | 288000.00 |
| Kochhar     |            90 | 204000.00 |
| De Haan     |            90 | 204000.00 |
| Hunold      |            60 | 108000.00 |
| Ernst       |            60 |  72000.00 |
| Austin      |            60 |  57600.00 |
| Pataballa   |            60 |  57600.00 |
| Lorentz     |            60 |  50400.00 |
| Greenberg   |           100 | 144000.00 |
| Faviet      |           100 | 108000.00 |
| Chen        |           100 |  98400.00 |
| Sciarra     |           100 |  92400.00 |
| Urman       |           100 |  93600.00 |
| Popp        |           100 |  82800.00 |
| Raphaely    |            30 | 132000.00 |
| Khoo        |            30 |  37200.00 |
| Baida       |            30 |  34800.00 |
...
...

注:这是本人的学习笔记及练习,如果有错误的地方望指出一起讨论,谢谢!

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,511评论 1 330
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,495评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,595评论 0 225
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,558评论 0 190
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,715评论 3 270
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,672评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,112评论 2 291
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,837评论 0 181
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,417评论 0 228
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,928评论 2 232
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,316评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,773评论 2 234
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,253评论 3 220
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,827评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,440评论 0 180
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,523评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,583评论 2 249

推荐阅读更多精彩内容