MySQL查询处理

最新推荐文章于 2022-10-28 22:00:00 发布
最新推荐文章于 2022-10-28 22:00:00 发布
阅读量289 收藏
点赞数
分类专栏： MySQL 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43853447/article/details/109128858
版权

MySQL查询处理

基本的select语句

SELECT *|{[DISTINCT] column|expression [alias],...}FROM tabl
  • select :标识选择那些列表
  • from : 标识从那个表中选择
  1. 选择表中所有内容
SELECT *FROM department;

  • select * from table_name;表示查询一个表中的所有内容

    #查询jobs表中的所有内容
MariaDB [myemployees]> select * from jobs;
+------------+---------------------------------+------------+------------+
| job_id     | job_title                       | min_salary | max_salary |
+------------+---------------------------------+------------+------------+
| AC_ACCOUNT | Public Accountant               |       4200 |       9000 |
| AC_MGR     | Accounting Manager              |       8200 |      16000 |
| AD_ASST    | Administration Assistant        |       3000 |       6000 |
| AD_PRES    | President                       |      20000 |      40000 |
| AD_VP      | Administration Vice President   |      15000 |      30000 |
| FI_ACCOUNT | Accountant                      |       4200 |       9000 |
| FI_MGR     | Finance Manager                 |       8200 |      16000 |
| HR_REP     | Human Resources Representative  |       4000 |       9000 |
| IT_PROG    | Programmer                      |       4000 |      10000 |
| MK_MAN     | Marketing Manager               |       9000 |      15000 |
| MK_REP     | Marketing Representative        |       4000 |       9000 |
| PR_REP     | Public Relations Representative |       4500 |      10500 |
| PU_CLERK   | Purchasing Clerk                |       2500 |       5500 |
| PU_MAN     | Purchasing Manager              |       8000 |      15000 |
| SA_MAN     | Sales Manager                   |      10000 |      20000 |
| SA_REP     | Sales Representative            |       6000 |      12000 |
| SH_CLERK   | Shipping Clerk                  |       2500 |       5500 |
| ST_CLERK   | Stock Clerk                     |       2000 |       5000 |
| ST_MAN     | Stock Manager                   |       5500 |       8500 |
+------------+---------------------------------+------------+------------+
19 rows in set (0.00 sec)

  1. 选择特定的的列

    语法

    SELECT department_id, location_id
FROM department

    示例:

    MariaDB [myemployees]> select job_id ,min_salary from jobs;
+------------+------------+
| job_id     | min_salary |
+------------+------------+
| AC_ACCOUNT |       4200 |
| AC_MGR     |       8200 |
| AD_ASST    |       3000 |
| AD_PRES    |      20000 |
| AD_VP      |      15000 |
| FI_ACCOUNT |       4200 |
| FI_MGR     |       8200 |
| HR_REP     |       4000 |
| IT_PROG    |       4000 |
| MK_MAN     |       9000 |
| MK_REP     |       4000 |
| PR_REP     |       4500 |
| PU_CLERK   |       2500 |
| PU_MAN     |       8000 |
| SA_MAN     |      10000 |
| SA_REP     |       6000 |
| SH_CLERK   |       2500 |
| ST_CLERK   |       2000 |
| ST_MAN     |       5500 |
+------------+------------+
19 rows in set (0.00 sec)

  2. 注意

    • SQL语言大小写不敏感
    • SQL可以写在一行或者多行
    • 关键字不能呗缩写也不能分行
    • 各子句一般要分行写
    • 使用缩进极高语句的可读性

  3. 列的别名

    1. 紧跟列名，也可以在列名和别名之间加入关键字
      ‘AS’，别名使用双引号，以便在别名中包含空
      格或特殊的字符并区分大小写。

    2. 作用

      • 可以重写命名一个列
      • 便于计算

    3. 使用语法

      • SELECT last_name AS name, commission_pct comm
FROM employee

      • 实例:

        
MariaDB [myemployees]> select job_id id ,min_salary salary from jobs;
+------------+--------+
| id         | salary |
+------------+--------+
| AC_ACCOUNT |   4200 |
| AC_MGR     |   8200 |
| AD_ASST    |   3000 |
| AD_PRES    |  20000 |
| AD_VP      |  15000 |
| FI_ACCOUNT |   4200 |
| FI_MGR     |   8200 |
| HR_REP     |   4000 |
| IT_PROG    |   4000 |
| MK_MAN     |   9000 |
| MK_REP     |   4000 |
| PR_REP     |   4500 |
| PU_CLERK   |   2500 |
| PU_MAN     |   8000 |
| SA_MAN     |  10000 |
| SA_REP     |   6000 |
| SH_CLERK   |   2500 |
| ST_CLERK   |   2000 |
| ST_MAN     |   5500 |
+------------+--------+
19 rows in set (0.00 sec)

过滤和排序的数据

  1. select过滤

    • 介绍:使用where关键字进行过滤,where会将不满足的数据过滤掉

    • 语法:

      SELECT *|{[DISTINCT] column|expression [alias],...}FROM table
[WHERE condition(s);
注意:• WHERE 子句紧随 FROM 子句。

    • 示例

      MariaDB [myemployees]> select job_id id ,min_salary salary from jobs where min_salary = 2500;
+----------+--------+
| id       | salary |
+----------+--------+
| PU_CLERK |   2500 |
| SH_CLERK |   2500 |
+----------+--------+
2 rows in set (0.00 sec)

  2. MySQL中的比较运算符

    img

  3. 使用比较运算符进行数据过滤

    • 语法:

      SELECT last_name, salaryFROM employeesWHERE salary <= 300

    • 实例

      MariaDB [myemployees]> select job_id id ,min_salary salary from jobs where min_salary >5000;
+---------+--------+
| id      | salary |
+---------+--------+
| AC_MGR  |   8200 |
| AD_PRES |  20000 |
| AD_VP   |  15000 |
| FI_MGR  |   8200 |
| MK_MAN  |   9000 |
| PU_MAN  |   8000 |
| SA_MAN  |  10000 |
| SA_REP  |   6000 |
| ST_MAN  |   5500 |
+---------+--------+
9 rows in set (0.00 sec)

  4. 特殊比较运算符between,in,like,is null的使用

    1. between … and …

      • 含义:在两个值之间(包含边界)

      • 语法

        SELECT last_name, salaryFROM employeesWHERE salary BETWEEN 2500 AND 350;

      • 示例

        MariaDB [myemployees]> select job_id id ,min_salary salary from jobs where min_salary between 5000 and 10000;
+--------+--------+
| id     | salary |
+--------+--------+
| AC_MGR |   8200 |
| FI_MGR |   8200 |
| MK_MAN |   9000 |
| PU_MAN |   8000 |
| SA_MAN |  10000 |
| SA_REP |   6000 |
| ST_MAN |   5500 |
+--------+--------+
7 rows in set (0.00 sec)

注意:较小的数字必须放在前面

    2. in

      • 含义:使用 IN运算显示列表中的值。

      • 语法:

        SELECT employee_id, last_name, salary, manager_id
FROM employeesWHERE manager_id IN (100, 101, 201);

      • 示例

        MariaDB [myemployees]> select job_id id ,min_salary salary from jobs where min_salary in( 5500, 10000,8000);
+--------+--------+
| id     | salary |
+--------+--------+
| PU_MAN |   8000 |
| SA_MAN |  10000 |
| ST_MAN |   5500 |
+--------+--------+
3 rows in set (0.00 sec)

    3. like

      • 使用 LIKE 运算选择类似的值

      • 选择条件可以包含字符或数字:“%” 代表零个或多个字符(任意个字符)。
        “_” 代表一个字符

      • 语法

        SELECT first_nameFROM employeesWHERE firstname LIKE 'S%';

      • 示例:查询以S开头的id

        MariaDB [myemployees]> select job_id id from jobs where job_id like "S%";
+----------+
| id       |
+----------+
| SA_MAN   |
| SA_REP   |
| SH_CLERK |
| ST_CLERK |
| ST_MAN   |
+----------+
5 rows in set (0.00 sec)

    4. is (not) null

      • 判断空值

      • 语法

        SELECT last_name, manager_id
FROM employeesWHERE managerid IS NULL;

      • 实例:查询emloyees表中manager_id为空的数据

        MariaDB [myemployees]> select manager_id  from employees where manager_id is null; 
+------------+
| manager_id |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec)

    5. 逻辑运算

      运算符作用
      NOT 或！逻辑非
      AND 或&&逻辑与
      OR 或 ||逻辑或
      XOR逻辑异或

      1. and 逻辑或

        • AND 要求并的关系

        • 语法

          SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary >=10000AND job_id LIKE '%MAN%

        • 示例:查询jobs表中的所有的min_salary > 6000 并且job_id以A开头的数据信息

          
MariaDB [myemployees]> select * from jobs where min_salary > 6000 and job_id like "A%";  
+---------+-------------------------------+------------+------------+
| job_id  | job_title                     | min_salary | max_salary |
+---------+-------------------------------+------------+------------+
| AC_MGR  | Accounting Manager            |       8200 |      16000 |
| AD_PRES | President                     |      20000 |      40000 |
| AD_VP   | Administration Vice President |      15000 |      30000 |
+---------+-------------------------------+------------+------------+
3 rows in set (0.00 sec)

      2. or

        • OR 要求或关系为真。

        • 语法

          SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN%';

        • 实例查询jobs表中的所有的min_salary > 8000 或者job_id以A开头的数据信息

          MariaDB [myemployees]> select * from jobs where min_salary > 8000 or job_id like "A%";  
+------------+-------------------------------+------------+------------+
| job_id     | job_title                     | min_salary | max_salary |
+------------+-------------------------------+------------+------------+
| AC_ACCOUNT | Public Accountant             |       4200 |       9000 |
| AC_MGR     | Accounting Manager            |       8200 |      16000 |
| AD_ASST    | Administration Assistant      |       3000 |       6000 |
| AD_PRES    | President                     |      20000 |      40000 |
| AD_VP      | Administration Vice President |      15000 |      30000 |
| FI_MGR     | Finance Manager               |       8200 |      16000 |
| MK_MAN     | Marketing Manager             |       9000 |      15000 |
| SA_MAN     | Sales Manager                 |      10000 |      20000 |
+------------+-------------------------------+------------+------------+
8 rows in set (0.00 sec)

      3. not

        • 非

        • 语法

          SELECT last_name, job_id
FROM employees
WHERE job_id 
NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

        • 示例查询jobs表中不是A开头的所有数据

          MariaDB [myemployees]> select * from jobs where  job_id not  like "A%";  
+------------+---------------------------------+------------+------------+
| job_id     | job_title                       | min_salary | max_salary |
+------------+---------------------------------+------------+------------+
| FI_ACCOUNT | Accountant                      |       4200 |       9000 |
| FI_MGR     | Finance Manager                 |       8200 |      16000 |
| HR_REP     | Human Resources Representative  |       4000 |       9000 |
| IT_PROG    | Programmer                      |       4000 |      10000 |
| MK_MAN     | Marketing Manager               |       9000 |      15000 |
| MK_REP     | Marketing Representative        |       4000 |       9000 |
| PR_REP     | Public Relations Representative |       4500 |      10500 |
| PU_CLERK   | Purchasing Clerk                |       2500 |       5500 |
| PU_MAN     | Purchasing Manager              |       8000 |      15000 |
| SA_MAN     | Sales Manager                   |      10000 |      20000 |
| SA_REP     | Sales Representative            |       6000 |      12000 |
| SH_CLERK   | Shipping Clerk                  |       2500 |       5500 |
| ST_CLERK   | Stock Clerk                     |       2000 |       5000 |
| ST_MAN     | Stock Manager                   |       5500 |       8500 |
+------------+---------------------------------+------------+------------+
14 rows in set (0.00 sec)

    6. ORDER BY(排序)

      • 使用 ORDER BY 子句排序(– ASC（ascend）: 升序– DESC（descend）: )

      • ORDER BY 子句在SELECT语句的结尾。

      • 语法

        SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date

      • 示例:查询jobs表中不是A开头的所有数据,并以min_salary降序排序

        MariaDB [myemployees]> select * from jobs where  job_id not  like "A%" order by min_salary;  
+------------+---------------------------------+------------+------------+
| job_id     | job_title                       | min_salary | max_salary |
+------------+---------------------------------+------------+------------+
| ST_CLERK   | Stock Clerk                     |       2000 |       5000 |
| PU_CLERK   | Purchasing Clerk                |       2500 |       5500 |
| SH_CLERK   | Shipping Clerk                  |       2500 |       5500 |
| HR_REP     | Human Resources Representative  |       4000 |       9000 |
| IT_PROG    | Programmer                      |       4000 |      10000 |
| MK_REP     | Marketing Representative        |       4000 |       9000 |
| FI_ACCOUNT | Accountant                      |       4200 |       9000 |
| PR_REP     | Public Relations Representative |       4500 |      10500 |
| ST_MAN     | Stock Manager                   |       5500 |       8500 |
| SA_REP     | Sales Representative            |       6000 |      12000 |
| PU_MAN     | Purchasing Manager              |       8000 |      15000 |
| FI_MGR     | Finance Manager                 |       8200 |      16000 |
| MK_MAN     | Marketing Manager               |       9000 |      15000 |
| SA_MAN     | Sales Manager                   |      10000 |      20000 |
+------------+---------------------------------+------------+------------+
14 rows in set (0.00 sec)

      • 按照ORDER BY 列表的顺序排序。

      • 语法

        SELECT last_name, department_id, salaryFROM employeesORDER BY department_id, salary DESC;

      • 实例:查询min_salary大于8000的数据,并且按照min_salary降序排序,如果min_salary相同,按照max_salary降序排序

        ariaDB [myemployees]> select * from jobs where min_salary > 8000 order by min_salary ,max_salary;
+---------+-------------------------------+------------+------------+
| job_id  | job_title                     | min_salary | max_salary |
+---------+-------------------------------+------------+------------+
| AC_MGR  | Accounting Manager            |       8200 |      16000 |
| FI_MGR  | Finance Manager               |       8200 |      16000 |
| MK_MAN  | Marketing Manager             |       9000 |      15000 |
| SA_MAN  | Sales Manager                 |      10000 |      20000 |
| AD_VP   | Administration Vice President |      15000 |      30000 |
| AD_PRES | President                     |      20000 |      40000 |
+---------+-------------------------------+------------+------------+
6 rows in set (0.00 sec)

    7. 查询不重复的数据,使用关键字distinct

      MariaDB [myemployees]> select distinct job_id from employees;
+------------+
| job_id     |
+------------+
| AC_ACCOUNT |
| AC_MGR     |
| AD_ASST    |
| AD_PRES    |
| AD_VP      |
| FI_ACCOUNT |
| FI_MGR     |
| HR_REP     |
| IT_PROG    |
| MK_MAN     |
| MK_REP     |
| PR_REP     |
| PU_CLERK   |
| PU_MAN     |
| SA_MAN     |
| SA_REP     |
| SH_CLERK   |
| ST_CLERK   |
| ST_MAN     |
+------------+
19 rows in set (0.00 sec)

分组函数

什么是分组

分组函数作用于一组数据，并对一组数据返回一个值

组函数类型

• AVG()

• COUNT()

• MAX()

• MIN()

• SUM

组函数语法

SELECT [column,] group_function(column), ...FROM table
[WHERE condition][GROUP BY column][ORDER BY column;

AVG(平均值) 和SUM(合计)函数

  • 可以对数值型数据使用AVG 和 SUM 函数

  • 语法

    SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)FROM employeesWHERE job_id LIKE '%REP%;

  • 示例:

    MariaDB [myemployees]> select AVG(min_salary),SUM(max_salary) from jobs;
+-----------------+-----------------+
| AVG(min_salary) | SUM(max_salary) |
+-----------------+-----------------+
|       6568.4211 |          251000 |
+-----------------+-----------------+
1 row in set (0.00 sec)

MIN（最小值）和 MAX（最大值）

  • 可以对任意数据类型的数据使用 MIN 和 MAX 函数。

  • 语法

    SELECT MIN(hire_date), MAX(hire_date)FROM employees;

  • 示例

    MariaDB [myemployees]> select MIN(min_salary),MAX(max_salary) from jobs;
+-----------------+-----------------+
| MIN(min_salary) | MAX(max_salary) |
+-----------------+-----------------+
|            2000 |           40000 |
+-----------------+-----------------+
1 row in set (0.01 sec)

COUNT（计数）

  • COUNT(*) 返回表中记录总数,适用于任意数据类型

  • 语法一

    SELECT COUNT(*)FROM employeesWHERE departmentid = 50;

  • 示例:

    MariaDB [myemployees]> select count(*) from jobs where max_salary=16000;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

  • COUNT(expr) 返回expr不为空的记录总

  • 语法二

    SELECT COUNT(commission_pct)FROM employeesWHERE departmentid = 50;

  • 示例

    MariaDB [myemployees]> select count(max_salary) from jobs ;
+-------------------+
| count(max_salary) |
+-------------------+
|                19 |
+-------------------+
1 row in set (0.00 sec)

分组查询

  1. 分组数据: GROUP BY 子句语法

  2. 可以使用GROUP BY子句将表中的数据分成若

  3. 作用:在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子

  4. 语法

    SELECT column, group_function(column)FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

  5. 明确：WHERE一定放在FROM之后

    示例

    MariaDB [myemployees]> select department_id ,AVG(salary) from employees group by department_id;
+---------------+--------------+
| department_id | AVG(salary)  |
+---------------+--------------+
|          NULL |  7000.000000 |
|            10 |  4400.000000 |
|            20 |  9500.000000 |
|            30 |  4150.000000 |
|            40 |  6500.000000 |
|            50 |  3475.555556 |
|            60 |  5760.000000 |
|            70 | 10000.000000 |
|            80 |  8955.882353 |
|            90 | 19333.333333 |
|           100 |  8600.000000 |
|           110 | 10150.000000 |
+---------------+--------------+
12 rows in set (0.00 sec)

注意:

• 不能在 WHERE 子句中使用组函数。

• 可以在 HAVING 子句中使用组函

过滤分组:HACING子句

使用 HAVING 过滤分组:

  1. 行已经被分组。

  2. 使用了组函数。

  3. 满足HAVING 子句中条件的分组将被显示

  4. 语法

    SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

    示例:

    MariaDB [myemployees]> select department_id dept_id , job_id ,sum(salary) from employees group by department_id ,job_id having sum(salary) > 10000; 
+---------+------------+-------------+
| dept_id | job_id     | sum(salary) |
+---------+------------+-------------+
|      20 | MK_MAN     |    13000.00 |
|      30 | PU_CLERK   |    13900.00 |
|      30 | PU_MAN     |    11000.00 |
|      50 | SH_CLERK   |    64300.00 |
|      50 | ST_CLERK   |    55700.00 |
|      50 | ST_MAN     |    36400.00 |
|      60 | IT_PROG    |    28800.00 |
|      80 | SA_MAN     |    61000.00 |
|      80 | SA_REP     |   243500.00 |
|      90 | AD_PRES    |    24000.00 |
|      90 | AD_VP      |    34000.00 |
|     100 | FI_ACCOUNT |    39600.00 |
|     100 | FI_MGR     |    12000.00 |
|     110 | AC_MGR     |    12000.00 |
+---------+------------+-------------+
14 rows in set (0.00 sec)

多表查询

MySQL连接

• 在 WHERE 子句中写入连接条件。

• 在表中有相同列时，在列名之前加上表名

语法

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;

示例

MariaDB [girls]> select beauty.id ,beauty.name,boys.boyName from beauty ,boys where beauty.id = boys.id;
+----+------------+-----------+
| id | name       | boyName   |
+----+------------+-----------+
|  1 | 柳岩       | 张无忌    |
|  2 | 苍老师     | 鹿晗      |
|  3 | Angelababy | 黄晓明    |
|  4 | 热巴       | 段誉      |
+----+------------+-----------+
4 rows in set (0.00 sec)

区分重复的

• 使用表名前缀在多个表中区分相同的列。

• 在不同表中具有相同列名的列可以用表的别名加以区 分。

• 如果使用了表别名，则在select语句中需要使用表别名代替表名

• 表别名最多支持32个字符长度，但建议越少

表的别名

• 使用别名可以简化查询。

• 使用表名前缀可以提高执行

SELECT bt.id,NAME,boyname 
FROM beauty bt,boys b;WHERE bt.`boyfriend_id`=b.id;

join连接

连接的主要作用是根据两个或多个表中的列之间的关系，获取存在于不同表中的数据。连接分为三类：内连接、外连接、全连接

– 内连接 [inner] join on

– 外连接

​ • 左外连接 left [outer] join on

​ • 右外连接 right [outer] join on

MariaDB [girls]> SELECT bt.id,NAME,boyname   FROM beauty bt Inner join boys b On bt.`boyfriend_id`=b.id;
+----+------------+-----------+
| id | NAME       | boyname   |
+----+------------+-----------+
|  6 | 周芷若     | 张无忌    |
|  8 | 小昭       | 张无忌    |
| 12 | 赵敏       | 张无忌    |
|  4 | 热巴       | 鹿晗      |
|  3 | Angelababy | 黄晓明    |
| 10 | 王语嫣     | 段誉      |
+----+------------+-----------+
6 rows in set (0.00 sec)

练习1

  1. 下面的语句是否可以执行成功select last_name , job_id , salary as salfrom employees;

  2. 下面的语句是否可以执行成功
    select * from employees;

  3. 找出下面语句中的错误select employee_id , last_name，salary * 12 “ANNUAL SALARY”from employees;

  4. 显示表 departments 的结构，并查询其中的全部数据

MariaDB [myemployees]> desc employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employee_id    | int(6)       | NO   | PRI | NULL    | auto_increment |
| first_name     | varchar(20)  | YES  |     | NULL    |                |
| last_name      | varchar(25)  | YES  |     | NULL    |                |
| email          | varchar(25)  | YES  |     | NULL    |                |
| phone_number   | varchar(20)  | YES  |     | NULL    |                |
| job_id         | varchar(10)  | YES  | MUL | NULL    |                |
| salary         | double(10,2) | YES  |     | NULL    |                |
| commission_pct | double(4,2)  | YES  |     | NULL    |                |
| manager_id     | int(6)       | YES  |     | NULL    |                |
| department_id  | int(4)       | YES  | MUL | NULL    |                |
| hiredate       | datetime     | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
11 rows in set (0.01 sec)
  1. 显示出表 employees 中的全部 job_id（不能重复）
MariaDB [myemployees]> select distinct job_id from employees;
+------------+
| job_id     |
+------------+
| AC_ACCOUNT |
| AC_MGR     |
| AD_ASST    |
| AD_PRES    |
| AD_VP      |
| FI_ACCOUNT |
| FI_MGR     |
| HR_REP     |
| IT_PROG    |
| MK_MAN     |
| MK_REP     |
| PR_REP     |
| PU_CLERK   |
| PU_MAN     |
| SA_MAN     |
| SA_REP     |
| SH_CLERK   |
| ST_CLERK   |
| ST_MAN     |
+------------+
19 rows in set (0.00 sec)
  1. 显示出表 employees 的全部列，各个列之间用逗号连接，列头显示成 OUT_P
SELECT * FROM employees;
SELECT CONCAT(employee_id,",",first_name,",",last_name,",",email,",
",phone_number,",",job_id,",",salary,",",IFNULL(commission_pct,0),",",
IFNULL(manager_id,0),",",department_id,",",hiredate) out_put FROM employees;
#注意使用concat连接的时候，如果连接的字段中存在NULL的字段，那么连接起来的字符串将会是NULL


MariaDB [myemployees]> SELECT CONCAT(employee_id,",",first_name,",",last_name,",",email,",
    "> ",phone_number,",",job_id,",",salary,",",IFNULL(commission_pct,0),",",
    -> IFNULL(manager_id,0),",",department_id,",",hiredate) out_put FROM employees;
+----------------------------------------------------------------------------------------------------+
| out_put                                                                                            |
+----------------------------------------------------------------------------------------------------+
| 100,Steven,K_ing,SKING,
515.123.4567,AD_PRES,24000.00,0.00,0,90,1992-04-03 00:00:00                |
| 101,Neena,Kochhar,NKOCHHAR,
515.123.4568,AD_VP,17000.00,0.00,100,90,1992-04-03 00:00:00            |
| 102,Lex,De Haan,LDEHAAN,
515.123.4569,AD_VP,17000.00,0.00,100,90,1992-04-03 00:00:00               |
| 103,Alexander,Hunold,AHUNOLD,
590.423.4567,IT_PROG,9000.00,0.00,102,60,1992-04-03 00:00:00         |
| 104,Bruce,Ernst,BERNST,
590.423.4568,IT_PROG,6000.00,0.00,103,60,1992-04-03 00:00:00               |
| 105,David,Austin,DAUSTIN,
590.423.4569,IT_PROG,4800.00,0.00,103,60,1998-03-03 00:00:00             |
| 106,Valli,Pataballa,VPATABAL,
590.423.4560,IT_PROG,4800.00,0.00,103,60,1998-03-03 00:00:00         |
| 107,Diana,Lorentz,DLORENTZ,
590.423.5567,IT_PROG,4200.00,0.00,103,60,1998-03-03 00:00:00           |
| 108,Nancy,Greenberg,NGREENBE,
515.124.4569,FI_MGR,12000.00,0.00,101,100,1998-03-03 00:00:00        |
| 109,Daniel,Faviet,DFAVIET,
515.124.4169,FI_ACCOUNT,9000.00,0.00,108,100,1998-03-03 00:00:00        |
| 110,John,Chen,JCHEN,
515.124.4269,FI_ACCOUNT,8200.00,0.00,108,100,2000-09-09 00:00:00              |
| 111,Ismael,Sciarra,ISCIARRA,
515.124.4369,FI_ACCOUNT,7700.00,0.00,108,100,2000-09-09 00:00:00      |
| 112,Jose Manuel,Urman,JMURMAN,
515.124.4469,FI_ACCOUNT,7800.00,0.00,108,100,2000-09-09 00:00:00    |
| 113,Luis,Popp,LPOPP,
515.124.4567,FI_ACCOUNT,6900.00,0.00,108,100,2000-09-09 00:00:00              |
| 114,Den,Raphaely,DRAPHEAL,
515.127.4561,PU_MAN,11000.00,0.00,100,30,2000-09-09 00:00:00            |
| 115,Alexander,Khoo,AKHOO,
515.127.4562,PU_CLERK,3100.00,0.00,114,30,2000-09-09 00:00:00            |
| 116,Shelli,Baida,SBAIDA,
515.127.4563,PU_CLERK,2900.00,0.00,114,30,2000-09-09 00:00:00             |
| 117,Sigal,Tobias,STOBIAS,
515.127.4564,PU_CLERK,2800.00,0.00,114,30,2000-09-09 00:00:00            |
| 118,Guy,Himuro,GHIMURO,
515.127.4565,PU_CLERK,2600.00,0.00,114,30,2000-09-09 00:00:00              |
| 119,Karen,Colmenares,KCOLMENA,
515.127.4566,PU_CLERK,2500.00,0.00,114,30,2000-09-09 00:00:00       |
| 120,Matthew,Weiss,MWEISS,
650.123.1234,ST_MAN,8000.00,0.00,100,50,2004-02-06 00:00:00              |
| 121,Adam,Fripp,AFRIPP,
650.123.2234,ST_MAN,8200.00,0.00,100,50,2004-02-06 00:00:00                 |
| 122,Payam,Kaufling,PKAUFLIN,
650.123.3234,ST_MAN,7900.00,0.00,100,50,2004-02-06 00:00:00           |
| 123,Shanta,Vollman,SVOLLMAN,
650.123.4234,ST_MAN,6500.00,0.00,100,50,2004-02-06 00:00:00           |
| 124,Kevin,Mourgos,KMOURGOS,
650.123.5234,ST_MAN,5800.00,0.00,100,50,2004-02-06 00:00:00            |
| 125,Julia,Nayer,JNAYER,
650.124.1214,ST_CLERK,3200.00,0.00,120,50,2004-02-06 00:00:00              |
| 126,Irene,Mikkilineni,IMIKKILI,
650.124.1224,ST_CLERK,2700.00,0.00,120,50,2004-02-06 00:00:00      |
| 127,James,Landry,JLANDRY,
650.124.1334,ST_CLERK,2400.00,0.00,120,50,2004-02-06 00:00:00            |
| 128,Steven,Markle,SMARKLE,
650.124.1434,ST_CLERK,2200.00,0.00,120,50,2004-02-06 00:00:00           |
| 129,Laura,Bissot,LBISSOT,
650.124.5234,ST_CLERK,3300.00,0.00,121,50,2004-02-06 00:00:00            |
| 130,Mozhe,Atkinson,MATKINSO,
650.124.6234,ST_CLERK,2800.00,0.00,121,50,2004-02-06 00:00:00         |
| 131,James,Marlow,JAMRLOW,
650.124.7234,ST_CLERK,2500.00,0.00,121,50,2004-02-06 00:00:00            |
| 132,TJ,Olson,TJOLSON,
650.124.8234,ST_CLERK,2100.00,0.00,121,50,2004-02-06 00:00:00                |
| 133,Jason,Mallin,JMALLIN,
650.127.1934,ST_CLERK,3300.00,0.00,122,50,2004-02-06 00:00:00            |
| 134,Michael,Rogers,MROGERS,
650.127.1834,ST_CLERK,2900.00,0.00,122,50,2002-12-23 00:00:00          |
| 135,Ki,Gee,KGEE,
650.127.1734,ST_CLERK,2400.00,0.00,122,50,2002-12-23 00:00:00                     |
| 136,Hazel,Philtanker,HPHILTAN,
650.127.1634,ST_CLERK,2200.00,0.00,122,50,2002-12-23 00:00:00       |
| 137,Renske,Ladwig,RLADWIG,
650.121.1234,ST_CLERK,3600.00,0.00,123,50,2002-12-23 00:00:00           |
| 138,Stephen,Stiles,SSTILES,
650.121.2034,ST_CLERK,3200.00,0.00,123,50,2002-12-23 00:00:00          |
| 139,John,Seo,JSEO,
650.121.2019,ST_CLERK,2700.00,0.00,123,50,2002-12-23 00:00:00                   |
| 140,Joshua,Patel,JPATEL,
650.121.1834,ST_CLERK,2500.00,0.00,123,50,2002-12-23 00:00:00             |
| 141,Trenna,Rajs,TRAJS,
650.121.8009,ST_CLERK,3500.00,0.00,124,50,2002-12-23 00:00:00               |
| 142,Curtis,Davies,CDAVIES,
650.121.2994,ST_CLERK,3100.00,0.00,124,50,2002-12-23 00:00:00           |
| 143,Randall,Matos,RMATOS,
650.121.2874,ST_CLERK,2600.00,0.00,124,50,2002-12-23 00:00:00            |
| 144,Peter,Vargas,PVARGAS,
650.121.2004,ST_CLERK,2500.00,0.00,124,50,2002-12-23 00:00:00            |
| 145,John,Russell,JRUSSEL,
011.44.1344.429268,SA_MAN,14000.00,0.40,100,80,2002-12-23 00:00:00       |
| 146,Karen,Partners,KPARTNER,
011.44.1344.467268,SA_MAN,13500.00,0.30,100,80,2002-12-23 00:00:00    |
| 147,Alberto,Errazuriz,AERRAZUR,
011.44.1344.429278,SA_MAN,12000.00,0.30,100,80,2002-12-23 00:00:00 |
| 148,Gerald,Cambrault,GCAMBRAU,
011.44.1344.619268,SA_MAN,11000.00,0.30,100,80,2002-12-23 00:00:00  |
| 149,Eleni,Zlotkey,EZLOTKEY,
011.44.1344.429018,SA_MAN,10500.00,0.20,100,80,2002-12-23 00:00:00     |
| 150,Peter,Tucker,PTUCKER,
011.44.1344.129268,SA_REP,10000.00,0.30,145,80,2014-03-05 00:00:00       |
| 151,David,Bernstein,DBERNSTE,
011.44.1344.345268,SA_REP,9500.00,0.25,145,80,2014-03-05 00:00:00    |
| 152,Peter,Hall,PHALL,
011.44.1344.478968,SA_REP,9000.00,0.25,145,80,2014-03-05 00:00:00            |
| 153,Christopher,Olsen,COLSEN,
011.44.1344.498718,SA_REP,8000.00,0.20,145,80,2014-03-05 00:00:00    |
| 154,Nanette,Cambrault,NCAMBRAU,
011.44.1344.987668,SA_REP,7500.00,0.20,145,80,2014-03-05 00:00:00  |
| 155,Oliver,Tuvault,OTUVAULT,
011.44.1344.486508,SA_REP,7000.00,0.15,145,80,2014-03-05 00:00:00     |
| 156,Janette,K_ing,JKING,
011.44.1345.429268,SA_REP,10000.00,0.35,146,80,2014-03-05 00:00:00        |
| 157,Patrick,Sully,PSULLY,
011.44.1345.929268,SA_REP,9500.00,0.35,146,80,2014-03-05 00:00:00        |
| 158,Allan,McEwen,AMCEWEN,
011.44.1345.829268,SA_REP,9000.00,0.35,146,80,2014-03-05 00:00:00        |
| 159,Lindsey,Smith,LSMITH,
011.44.1345.729268,SA_REP,8000.00,0.30,146,80,2014-03-05 00:00:00        |
| 160,Louise,Doran,LDORAN,
011.44.1345.629268,SA_REP,7500.00,0.30,146,80,2014-03-05 00:00:00         |
| 161,Sarath,Sewall,SSEWALL,
011.44.1345.529268,SA_REP,7000.00,0.25,146,80,2014-03-05 00:00:00       |
| 162,Clara,Vishney,CVISHNEY,
011.44.1346.129268,SA_REP,10500.00,0.25,147,80,2014-03-05 00:00:00     |
| 163,Danielle,Greene,DGREENE,
011.44.1346.229268,SA_REP,9500.00,0.15,147,80,2014-03-05 00:00:00     |
| 164,Mattea,Marvins,MMARVINS,
011.44.1346.329268,SA_REP,7200.00,0.10,147,80,2014-03-05 00:00:00     |
| 165,David,Lee,DLEE,
011.44.1346.529268,SA_REP,6800.00,0.10,147,80,2014-03-05 00:00:00              |
| 166,Sundar,Ande,SANDE,
011.44.1346.629268,SA_REP,6400.00,0.10,147,80,2014-03-05 00:00:00           |
| 167,Amit,Banda,ABANDA,
011.44.1346.729268,SA_REP,6200.00,0.10,147,80,2014-03-05 00:00:00           |
| 168,Lisa,Ozer,LOZER,
011.44.1343.929268,SA_REP,11500.00,0.25,148,80,2014-03-05 00:00:00            |
| 169,Harrison,Bloom,HBLOOM,
011.44.1343.829268,SA_REP,10000.00,0.20,148,80,2014-03-05 00:00:00      |
| 170,Tayler,Fox,TFOX,
011.44.1343.729268,SA_REP,9600.00,0.20,148,80,2014-03-05 00:00:00             |
| 171,William,Smith,WSMITH,
011.44.1343.629268,SA_REP,7400.00,0.15,148,80,2014-03-05 00:00:00        |
| 172,Elizabeth,Bates,EBATES,
011.44.1343.529268,SA_REP,7300.00,0.15,148,80,2014-03-05 00:00:00      |
| 173,Sundita,Kumar,SKUMAR,
011.44.1343.329268,SA_REP,6100.00,0.10,148,80,2014-03-05 00:00:00        |
| 174,Ellen,Abel,EABEL,
011.44.1644.429267,SA_REP,11000.00,0.30,149,80,2014-03-05 00:00:00           |
| 175,Alyssa,Hutton,AHUTTON,
011.44.1644.429266,SA_REP,8800.00,0.25,149,80,2014-03-05 00:00:00       |
| 176,Jonathon,Taylor,JTAYLOR,
011.44.1644.429265,SA_REP,8600.00,0.20,149,80,2014-03-05 00:00:00     |
| 177,Jack,Livingston,JLIVINGS,
011.44.1644.429264,SA_REP,8400.00,0.20,149,80,2014-03-05 00:00:00    |
| NULL                                                                                               |
| 179,Charles,Johnson,CJOHNSON,
011.44.1644.429262,SA_REP,6200.00,0.10,149,80,2014-03-05 00:00:00    |
| 180,Winston,Taylor,WTAYLOR,
650.507.9876,SH_CLERK,3200.00,0.00,120,50,2014-03-05 00:00:00          |
| 181,Jean,Fleaur,JFLEAUR,
650.507.9877,SH_CLERK,3100.00,0.00,120,50,2014-03-05 00:00:00             |
| 182,Martha,Sullivan,MSULLIVA,
650.507.9878,SH_CLERK,2500.00,0.00,120,50,2014-03-05 00:00:00        |
| 183,Girard,Geoni,GGEONI,
650.507.9879,SH_CLERK,2800.00,0.00,120,50,2014-03-05 00:00:00             |
| 184,Nandita,Sarchand,NSARCHAN,
650.509.1876,SH_CLERK,4200.00,0.00,121,50,2014-03-05 00:00:00       |
| 185,Alexis,Bull,ABULL,
650.509.2876,SH_CLERK,4100.00,0.00,121,50,2014-03-05 00:00:00               |
| 186,Julia,Dellinger,JDELLING,
650.509.3876,SH_CLERK,3400.00,0.00,121,50,2014-03-05 00:00:00        |
| 187,Anthony,Cabrio,ACABRIO,
650.509.4876,SH_CLERK,3000.00,0.00,121,50,2014-03-05 00:00:00          |
| 188,Kelly,Chung,KCHUNG,
650.505.1876,SH_CLERK,3800.00,0.00,122,50,2014-03-05 00:00:00              |
| 189,Jennifer,Dilly,JDILLY,
650.505.2876,SH_CLERK,3600.00,0.00,122,50,2014-03-05 00:00:00           |
| 190,Timothy,Gates,TGATES,
650.505.3876,SH_CLERK,2900.00,0.00,122,50,2014-03-05 00:00:00            |
| 191,Randall,Perkins,RPERKINS,
650.505.4876,SH_CLERK,2500.00,0.00,122,50,2014-03-05 00:00:00        |
| 192,Sarah,Bell,SBELL,
650.501.1876,SH_CLERK,4000.00,0.00,123,50,2014-03-05 00:00:00                |
| 193,Britney,Everett,BEVERETT,
650.501.2876,SH_CLERK,3900.00,0.00,123,50,2014-03-05 00:00:00        |
| 194,Samuel,McCain,SMCCAIN,
650.501.3876,SH_CLERK,3200.00,0.00,123,50,2014-03-05 00:00:00           |
| 195,Vance,Jones,VJONES,
650.501.4876,SH_CLERK,2800.00,0.00,123,50,2014-03-05 00:00:00              |
| 196,Alana,Walsh,AWALSH,
650.507.9811,SH_CLERK,3100.00,0.00,124,50,2014-03-05 00:00:00              |
| 197,Kevin,Feeney,KFEENEY,
650.507.9822,SH_CLERK,3000.00,0.00,124,50,2014-03-05 00:00:00            |
| 198,Donald,OConnell,DOCONNEL,
650.507.9833,SH_CLERK,2600.00,0.00,124,50,2014-03-05 00:00:00        |
| 199,Douglas,Grant,DGRANT,
650.507.9844,SH_CLERK,2600.00,0.00,124,50,2014-03-05 00:00:00            |
| 200,Jennifer,Whalen,JWHALEN,
515.123.4444,AD_ASST,4400.00,0.00,101,10,2016-03-03 00:00:00          |
| 201,Michael,Hartstein,MHARTSTE,
515.123.5555,MK_MAN,13000.00,0.00,100,20,2016-03-03 00:00:00       |
| 202,Pat,Fay,PFAY,
603.123.6666,MK_REP,6000.00,0.00,201,20,2016-03-03 00:00:00                      |
| 203,Susan,Mavris,SMAVRIS,
515.123.7777,HR_REP,6500.00,0.00,101,40,2016-03-03 00:00:00              |
| 204,Hermann,Baer,HBAER,
515.123.8888,PR_REP,10000.00,0.00,101,70,2016-03-03 00:00:00               |
| 205,Shelley,Higgins,SHIGGINS,
515.123.8080,AC_MGR,12000.00,0.00,101,110,2016-03-03 00:00:00        |
| 206,William,Gietz,WGIETZ,
515.123.8181,AC_ACCOUNT,8300.00,0.00,205,110,2016-03-03 00:00:00         |
+----------------------------------------------------------------------------------------------------+
107 rows in set (0.01 sec)

练习2

  1. 查询工资大于 12000 的员工姓名和工资

    
MariaDB [myemployees]> select last_name ,salary from employees where salary > 12000;
+-----------+----------+
| last_name | salary   |
+-----------+----------+
| K_ing     | 24000.00 |
| Kochhar   | 17000.00 |
| De Haan   | 17000.00 |
| Russell   | 14000.00 |
| Partners  | 13500.00 |
| Hartstein | 13000.00 |
+-----------+----------+
6 rows in set (0.00 sec)

  2. 查询员工号为 176 的员工的姓名和部门号和年薪

    MariaDB [myemployees]> select last_name ,department_id ,salary from employees where employee_id = 176;
+-----------+---------------+---------+
| last_name | department_id | salary  |
+-----------+---------------+---------+
| Taylor    |            80 | 8600.00 |
+-----------+---------------+---------+
1 row in set (0.00 sec)

  3. 选择工资不在 5000 到 12000 的员工的姓名和工资

    
MariaDB [myemployees]> select last_name ,salary from employees where salary  < 5000 or salary > 12000;
+-------------+----------+
| last_name   | salary   |
+-------------+----------+
| K_ing       | 24000.00 |
| Kochhar     | 17000.00 |
| De Haan     | 17000.00 |
| Austin      |  4800.00 |
| Pataballa   |  4800.00 |
| Lorentz     |  4200.00 |
| Khoo        |  3100.00 |
| Baida       |  2900.00 |
| Tobias      |  2800.00 |
| Himuro      |  2600.00 |
| Colmenares  |  2500.00 |
| Nayer       |  3200.00 |
| Mikkilineni |  2700.00 |
| Landry      |  2400.00 |
| Markle      |  2200.00 |
| Bissot      |  3300.00 |
| Atkinson    |  2800.00 |
| Marlow      |  2500.00 |
| Olson       |  2100.00 |
| Mallin      |  3300.00 |
| Rogers      |  2900.00 |
| Gee         |  2400.00 |
| Philtanker  |  2200.00 |
| Ladwig      |  3600.00 |
| Stiles      |  3200.00 |
| Seo         |  2700.00 |
| Patel       |  2500.00 |
| Rajs        |  3500.00 |
| Davies      |  3100.00 |
| Matos       |  2600.00 |
| Vargas      |  2500.00 |
| Russell     | 14000.00 |
| Partners    | 13500.00 |
| Taylor      |  3200.00 |
| Fleaur      |  3100.00 |
| Sullivan    |  2500.00 |
| Geoni       |  2800.00 |
| Sarchand    |  4200.00 |
| Bull        |  4100.00 |
| Dellinger   |  3400.00 |
| Cabrio      |  3000.00 |
| Chung       |  3800.00 |
| Dilly       |  3600.00 |
| Gates       |  2900.00 |
| Perkins     |  2500.00 |
| Bell        |  4000.00 |
| Everett     |  3900.00 |
| McCain      |  3200.00 |
| Jones       |  2800.00 |
| Walsh       |  3100.00 |
| Feeney      |  3000.00 |
| OConnell    |  2600.00 |
| Grant       |  2600.00 |
| Whalen      |  4400.00 |
| Hartstein   | 13000.00 |
+-------------+----------+
55 rows in set (0.00 sec)

    方法二

    
MariaDB [myemployees]> select last_name ,salary from employees where salary not between 5000 and 12000;
+-------------+----------+
| last_name   | salary   |
+-------------+----------+
| K_ing       | 24000.00 |
| Kochhar     | 17000.00 |
| De Haan     | 17000.00 |
| Austin      |  4800.00 |
| Pataballa   |  4800.00 |
| Lorentz     |  4200.00 |
| Khoo        |  3100.00 |
| Baida       |  2900.00 |
| Tobias      |  2800.00 |
| Himuro      |  2600.00 |
| Colmenares  |  2500.00 |
| Nayer       |  3200.00 |
| Mikkilineni |  2700.00 |
| Landry      |  2400.00 |
| Markle      |  2200.00 |
| Bissot      |  3300.00 |
| Atkinson    |  2800.00 |
| Marlow      |  2500.00 |
| Olson       |  2100.00 |
| Mallin      |  3300.00 |
| Rogers      |  2900.00 |
| Gee         |  2400.00 |
| Philtanker  |  2200.00 |
| Ladwig      |  3600.00 |
| Stiles      |  3200.00 |
| Seo         |  2700.00 |
| Patel       |  2500.00 |
| Rajs        |  3500.00 |
| Davies      |  3100.00 |
| Matos       |  2600.00 |
| Vargas      |  2500.00 |
| Russell     | 14000.00 |
| Partners    | 13500.00 |
| Taylor      |  3200.00 |
| Fleaur      |  3100.00 |
| Sullivan    |  2500.00 |
| Geoni       |  2800.00 |
| Sarchand    |  4200.00 |
| Bull        |  4100.00 |
| Dellinger   |  3400.00 |
| Cabrio      |  3000.00 |
| Chung       |  3800.00 |
| Dilly       |  3600.00 |
| Gates       |  2900.00 |
| Perkins     |  2500.00 |
| Bell        |  4000.00 |
| Everett     |  3900.00 |
| McCain      |  3200.00 |
| Jones       |  2800.00 |
| Walsh       |  3100.00 |
| Feeney      |  3000.00 |
| OConnell    |  2600.00 |
| Grant       |  2600.00 |
| Whalen      |  4400.00 |
| Hartstein   | 13000.00 |
+-------------+----------+
55 rows in set (0.00 sec)

  4. 选择在 20 或 50 号部门工作的员工姓名和部门号

    MariaDB [myemployees]> select last_name ,department_id from employees where department_id in (20 ,50); 
+-------------+---------------+
| last_name   | department_id |
+-------------+---------------+
| Weiss       |            50 |
| Fripp       |            50 |
| Kaufling    |            50 |
| Vollman     |            50 |
| Mourgos     |            50 |
| Nayer       |            50 |
| Mikkilineni |            50 |
| Landry      |            50 |
| Markle      |            50 |
| Bissot      |            50 |
| Atkinson    |            50 |
| Marlow      |            50 |
| Olson       |            50 |
| Mallin      |            50 |
| Rogers      |            50 |
| Gee         |            50 |
| Philtanker  |            50 |
| Ladwig      |            50 |
| Stiles      |            50 |
| Seo         |            50 |
| Patel       |            50 |
| Rajs        |            50 |
| Davies      |            50 |
| Matos       |            50 |
| Vargas      |            50 |
| Taylor      |            50 |
| Fleaur      |            50 |
| Sullivan    |            50 |
| Geoni       |            50 |
| Sarchand    |            50 |
| Bull        |            50 |
| Dellinger   |            50 |
| Cabrio      |            50 |
| Chung       |            50 |
| Dilly       |            50 |
| Gates       |            50 |
| Perkins     |            50 |
| Bell        |            50 |
| Everett     |            50 |
| McCain      |            50 |
| Jones       |            50 |
| Walsh       |            50 |
| Feeney      |            50 |
| OConnell    |            50 |
| Grant       |            50 |
| Hartstein   |            20 |
| Fay         |            20 |
+-------------+---------------+
47 rows in set (0.00 sec)

  5. 选择公司中没有管理者的员工姓名及 job_id

    
MariaDB [myemployees]> select last_name ,job_id from employees where manager_id is null;
+-----------+---------+
| last_name | job_id  |
+-----------+---------+
| K_ing     | AD_PRES |
+-----------+---------+
1 row in set (0.00 sec)

  6. 选择公司中有奖金的员工姓名，工资和奖金级别

    MariaDB [myemployees]> select salary ,commission_pct from employees where commission_pct is not null;
+----------+----------------+
| salary   | commission_pct |
+----------+----------------+
| 14000.00 |           0.40 |
| 13500.00 |           0.30 |
| 12000.00 |           0.30 |
| 11000.00 |           0.30 |
| 10500.00 |           0.20 |
| 10000.00 |           0.30 |
|  9500.00 |           0.25 |
|  9000.00 |           0.25 |
|  8000.00 |           0.20 |
|  7500.00 |           0.20 |
|  7000.00 |           0.15 |
| 10000.00 |           0.35 |
|  9500.00 |           0.35 |
|  9000.00 |           0.35 |
|  8000.00 |           0.30 |
|  7500.00 |           0.30 |
|  7000.00 |           0.25 |
| 10500.00 |           0.25 |
|  9500.00 |           0.15 |
|  7200.00 |           0.10 |
|  6800.00 |           0.10 |
|  6400.00 |           0.10 |
|  6200.00 |           0.10 |
| 11500.00 |           0.25 |
| 10000.00 |           0.20 |
|  9600.00 |           0.20 |
|  7400.00 |           0.15 |
|  7300.00 |           0.15 |
|  6100.00 |           0.10 |
| 11000.00 |           0.30 |
|  8800.00 |           0.25 |
|  8600.00 |           0.20 |
|  8400.00 |           0.20 |
|  7000.00 |           0.15 |
|  6200.00 |           0.10 |
+----------+----------------+
35 rows in set (0.00 sec)

  7. 选择员工姓名的第三个字母是 a 的员工姓名

    MariaDB [myemployees]> select last_name from employees where last_name like "__a%";
+-----------+
| last_name |
+-----------+
| Grant     |
| Grant     |
| Whalen    |
+-----------+
3 rows in set (0.00 sec)

  8. 选择姓名中有字母 a 和 e 的员工姓名

  9. 显示出表 employees 表中 first_name 以 'e’结尾的员工信息

  10. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位

  11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位

【灯灯】
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql 查询处理过程
weixin_33675507的博客
09-01 210
说明：本文来自《Mysql技术内幕：sql编程》，一本不错的书，讲解mysql的sql原理， 推荐阅读问题1： 有一条sql语句，那么是怎么返回查询结果的了？数据表1：CREATE TABLE `test1` ( `id` int(4) NOT NULL, `age` int(4) DEFAULT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8;...
mysql 查询数据的处理
weixin_41572561的博客
07-26 157
<?php $sql = "select `name`,`sex` from `detail`"; $result = mysql_query($sql); // 直接输出 while($row=mysql_fetch_array($result)){ $name = $row['name']; $sex = $row['s...
mysql 逻辑处理_MySQL查询处理——逻辑查询处理和物理查询处理
weixin_36173545的博客
01-19 260
对于查询处理，可将其分为逻辑查询处理和物理查询处理。逻辑查询处理表示执行查询应该产生什么样的结果，而物理查询代表MySQL数据库是如何得到结果的。逻辑查询处理MySQL真正的执行顺序如下：(8)SELECT (9)DISTINCT(1)FROM (3)JOIN(2) ON (4)WHERE (5)GROUP BY (6)WITH {CUBE|ROLLUP}(7)HAVING(10)ORDE...
mysql查询处理的步骤
weixin_30721077的博客
08-14 139
构建表 通过如下语句查询出来自杭州且订单数少于2的客户，并且查询出他们的订单数量，查询结果按点单从小到大排序。 执行如下图所示 转载于:https://www.cnblogs.com/qiaoshanzi/p/5771389.html...
MySQL_数据处理之查询
Fish_Vast的博客
08-19 245
数据处理之查询分为以下几个步骤进行学习： ● 基本的SELECT语句 ● 过滤和排序数据 ● 分组函数 ● 分组查询 ● 多表查询 ●分页查询 （1）基本的SELECT语句 • SELECT 标识选择哪些列。 • FROM 标识从哪个表中选择。 选择全部列 SELECT * FROM transportation; 选择特定列 SELECT name, id FROM transportation; 显示表的结构 DESCRIBE transportation （2）过滤和排序数据 过滤 • 使用WH
shell简单处理mysql查询结果的方法
09-09
这篇关于“shell简单处理mysql查询结果的方法”的文章旨在介绍如何在shell脚本中有效地处理MySQL查询返回的数据。 首先，我们需要理解shell脚本中的数组和字符串特性。在shell中，数组可以用来存储一组相关的值，...
Mysql开启慢查询
12-14
- **监控与报警**: 可以结合其他监控工具（如Nagios、Zabbix等），设定阈值，当慢查询达到一定数量或总耗时超过一定时间时发出报警，以便及时处理。 总之，MySQL的慢查询功能是数据库性能优化的重要组成部分，合理...
Mysql查询流程分析
04-09
通过对MySQL查询流程的深入了解，我们可以更好地理解MySQL是如何高效地处理查询请求的。从客户端与服务器之间的通信到查询结果的返回，每个环节都至关重要。通过合理地利用查询优化技术、权限管理和缓存机制等功能，...
大幅优化MySQL查询性能的奇技淫巧
09-10
本文将探讨如何大幅优化MySQL查询性能，特别是针对InnoDB存储引擎的优化策略。 首先，MySQL 5.6版本对只读（RO）性能的提升显著，这主要得益于对read-only事务的优化。在5.7版本中，虽然对read+write（RW）性能的...
MYSQL查询客户端
08-15
MySQL查询客户端是数据库管理人员和开发人员用来连接和管理MySQL服务器的工具，提供了直观的图形用户界面，使得数据库操作更为简便。SQLyog是其中一款知名的MySQL客户端，它结合了高效和易用性，广泛用于数据库的...
MySql中通用查询处理流程
A Flying Bird
04-01 843
描述MySql中通用的查询及查询结果处理流程。
mysql知识点
ZbyFt
01-23 288
windows平台下下载 http://dev.mysql.com/downloads/mysql my.ini文件作用 更改mysql的配置信息 mysql安装 # 安装url: https://blog.csdn.net/a774630093/article/details/79270080 # 卸载mysql 1.卸载 先停掉mysql进程   没有安装过的可以直接跳过 ki...
mysql创表的工种_mysql测试数据库employees一些sql语句
weixin_34345695的博客
01-30 549
一套SQL笔试题1、查找整个职员表的所有内容。select *from employees2、查看雇员名字(last_name)。select last_namefrom employees3、查看雇员编号、名字和工种。select last_name，job_id，employee_idfrom employees4、显示所有雇员的姓名、工资并将DEPARTMENT_ID显示为(Departme...
MySQL基础_单行函数练习
weixin_48378125的博客
04-19 460
练习 # 显示系统的时间 SELECT NOW(); # 查询员工 姓名 工资 以及工资提高百分之20%之后的结果 # 1.2 工资本身 在加上 0.2 结果 SELECT last_name,salary, salary * 1.2 AS towResult FROM employees # 将员工的姓名按首字母排序 并写出姓名的长度(length) # 默认asc SUBSTRING() 截取出首字母 SELECT last_name ,LENGTH(last_name) na FROM e
mysql基础篇 - SELECT 语句详解
weixin_33835690的博客
01-24 111
基础篇 - SELECT 语句详解         SELECT语句详解 一、实验简介 SQL 中最常用的 SELECT 语句，用来在表中选取数据，本节实验中将通过一系列的动手操作详细学习 SELECT 语句的用法。 二、实验准备 在正式开始本实验内容之前，需要先下载相关数据库表，搭建好一个名为mysql_shiyan 的数据库(有三张表：department，employee，proje...
MySQL数据库的查询操作
最新发布
m0_52660785的博客
10-28 1万+
数据库中的查询操作包括单表查询、连接查询、嵌套查询、集合查询和基于派生表的查询。
mysql查询收入最多的员工_查找员工表MySQL的最大和第二个最高工资
weixin_34966742的博客
01-19 1412
假设您获得了以下名为Employee的简单数据库表，该表有2列名为Employee ID和Salary：EmployeeEmployee ID Salary3 2004 8007 450我希望写一个查询select max(salary)as max_salary，2nd_max_salary from employee然后它...
查询操作练习
whylzzh的博客
07-25 3556
查询操作练习 1、导入数据 2、数据sql文件 3、了解表结构 4、基础查询 #单行注释 /* 多行注释 */ – 单行注释 #基础查询 /* select:查看、选择、显示 语法： select 要显示的东西 类似于 System.out.println(要打印的东西); 要显示的东西： ①常量 ②表达式 ③函数 ④字段，但要显示字段必须添加from 表名 语法： select 字段 from 表; */ #1.显示常量 SELECT 100; SELECT ‘john’; #2.显示表达式 SELECT
SA_JOB_QUEUE查找队列状态表（查询数据库表的方法）
Grace_1203的博客
10-16 1081
SA_JOB_QUEUE查找队列状态表 今天突然有人问了我一个问题，我们系统的队列状态表在哪一个表中，瞬间懵逼，这对一个时刻操纵着数据库的人来说是多么大的打击。然后我立马找到user_tables的这个表(这个是查询当前用户下的所有的表)。 select * from user_tables ut where ut.TABLE_NAME like '%UEUE%'--SA_JOB_QUEUE...
MySQL查询处理与EXPLAIN分析
"本文主要介绍了查询处理的概念，特别是与MySQL数据库相关的逻辑查询处理和物理查询处理，以及如何通过EXPLAIN命令解析查询执行计划。重点讨论了`EXPLAIN`输出的各种字段，包括`select_type`、`table`、`type`等，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值