查询 DQL(data query language）

1、基础查询

    格式： select <查询列表> [ from 表名 ]

     1、查询列表的内容可以为：表的字段，常量值，表达式，函数.

     2、查询的结果是一个虚拟的表格。

1.1 表字段查询

select 字段1，字段2 from 表名；   -- 查询指定的字段

select * from 表名； -- 查询所有字段，字段顺序和定义的顺序相同。

1.2 查询常量

mysql>  select 100;  --- 查询数值常量
+-----+
| 100 |
+-----+
| 100 |
+-----+
1 row in set (0.00 sec)
mysql> select 'join';   --查询字符串常量
+------+
| join |
+------+
| join |
+------+
1 row in set (0.00 sec)

1.3 查询表达式

 

mysql> select 100%98;  -- 查询表达式
+--------+
| 100%98 |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

mysql>

1.4 查询函数

select verison(); -- 查询版本信息

1.5 起别名

    1.5.1 方式一：使用as

mysql> select 100%98 as 结果;  -- 对常量结果起别名
+--------+
| 结果   |
+--------+
|      2 |
+--------+
1 row in set (0.01 sec)

mysql>

mysql> select user, host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql> select user as 用户名, host as 主机名 from user;  -- 用户名和主机名可以用双引号或者单引号括起来
+------------------+-----------+
| 用户名           | 主机名    |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql>

1.5.2 方式二：字段后面直接跟别名

mysql> select user 用户名, host 主机名 from user;
+------------------+-----------+
| 用户名           | 主机名    |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

1.6 去重

    <1> 去重select的后面只能存在一个字段，

select distinct department_id from employees;  -- department_id代码部门编号， employees表示员工数据库，对部门编号去重

1.7 +号的作用

仅仅只有一个功能，普通运算符，用于相加数值类型。

 （1）不能用于拼接字符串，如果参数中是字符串，则尝试将字符串转换为对应的数值，如果转换失败，则为0。

 （2）如果其中一个参数为null，则结果为null

mysql> select 100 + 90;
+----------+
| 100 + 90 |
+----------+
|      190 |
+----------+
1 row in set (0.29 sec)

mysql> select  '123' + 100;
+-------------+
| '123' + 100 |
+-------------+
|         223 |
+-------------+
1 row in set (0.28 sec)

mysql> select  '123' + "av";
+--------------+
| '123' + "av" |
+--------------+
|          123 |
+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> select 1 + null;
+----------+
| 1 + null |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

mysql>

1.8 函数拼接

函数concat(str1, str2, ...)

mysql> select concat("ke", "heyang");
+------------------------+
| concat("ke", "heyang") |
+------------------------+
| keheyang               |
+------------------------+
1 row in set (0.00 sec)

mysql>

mysql> create table t1(first_name char(10), last_name char(10));
Query OK, 0 rows affected (0.54 sec)
mysql> insert into t1(first_name, last_name) values("heyang", "ke");
Query OK, 1 row affected (0.08 sec)

mysql> select * from t1;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| heyang     | ke        |
+------------+-----------+
1 row in set (0.00 sec)

mysql> select concat(last_name, first_name) as 姓名 from t1; --as可以省略
+----------+
| 姓名     |
+----------+
| keheyang |
+----------+
1 row in set (0.28 sec)

mysql> select concat(last_name, first_name) from t1;
+-------------------------------+
| concat(last_name, first_name) |
+-------------------------------+
| keheyang                      |
+-------------------------------+
1 row in set (0.00 sec)

mysql>

注意点：concat 拼接的字符串不能有null，如果有任何一个参数为null，则返回null。

问题，如果要拼接的表字段中有内容为null的项，该如果处理？？

    使用ifnull(str1, str2)，str1不为null，返回str1, str1为null，返回str2；

 

mysql> select ifnull('str1', 'str2');
+------------------------+
| ifnull('str1', 'str2') |
+------------------------+
| str1                   |
+------------------------+
1 row in set (0.00 sec)
mysql> select ifnull(null, 'str2');
+----------------------+
| ifnull(null, 'str2') |
+----------------------+
| str2                 |
+----------------------+
1 row in set (0.00 sec)

mysql>

 

2、条件查询

2.1 查询格式

select 查询列表 from 表名  where 筛选条件;

筛选条件 分类：

    <1> 按照条件表达式筛选：

            条件运算符： >, <, =, != (<>也表示不等于), >=, <=

    <2> 安装逻辑表达式筛选：

            逻辑运算符： &&，||， ！，mysql推荐使用 and，or，not。

    <3> 模糊查询

           模糊查询关键字：like, between and, in, is null(is not null)

2.2 按照条件表达式筛选

    示例：

        select * from employees where salary>12000; -- 查询工资大于12000的员工。

2.3 按照逻辑表达式筛选

示例：

    select * from employees where salary>=10000 and salary <= 20000;   -- 查询工资在10000 到20000之间的员工信息。

    select * from employees where not(salary <= 10000 and salary <= 20000); -- 使用not 和and 查询工资小于10000 或者 大于20000的员工信息。

2.3 模糊查询

2.3.1 like

    <1> 一般和通配符配合使用，可判断字符型或者数值型：

        %：匹配任意多个字符(包含0个字符）

        _:   下划线，匹配任意单个字符。

        如果要匹配的字符就是%，_，需要使用转义字符 \_, \%，还可以使用 escape指定转义字符（参考后面的例子）。

select  * from employees where last_name like '%a%'; -- 查询员工名中包含字符a的员工信息, %号表示通配符。

select * from employees where last_name like '_$_%' escape '$';  --查询员工名字第二个字符为下划线，使用$作为转义字符。

2.3.2 [not] between and

    <1> 包含临界值（闭区间）

    <2> 左操作数小于右操作数，不能互换。

select * from employees where employee_id between 100 and 120; -- 查询员工编号在100到120之间的员工(包含100和120）。

2.3.3 in

    <1> in 列表不能使用通配符。

    <2>in列表的值类型必须一致或者兼容。

select * from employees where job_id in('IT', 'AD');   -- 查询工种编号为 IT, AD的员工信息。

2.3.4 is null/is not null

    <1> 判断和null相等不能使用条件运算符 =，x=null不能工作，=运算符不能判断是否为null，要判断null，必须用 is null。

示例：

select * from employees where commission IS NULL;    -- 查询没有奖金的员工信息（假设没有奖金，奖金列就为null）

select * from employees where commission IS NOT NULL;    -- 查询有奖金的员工信息（假设没有奖金，奖金列就为null）

2.3.5 安全等于  <=> 

    <1> 可以用于判断是否和null相等

    select * from employees where commission <=> NULL;    -- 查询没有奖金的员工信息（假设没有奖金，奖金列就为null）

 

3、排序查询

   查询格式： select 查询列表 from 表名 [where 筛选条件] order by 排序列表/表达式  [asc | desc]

                      asc：升序（默认情况）

                      desc:降序

select * from employees where department_id >=90 order by hiredate asc;   ---查询部门编号>=90的员工信息，按照入职时间先后排序

select *, salary*12*(1+ifnull(commission_pct, 0)) 年薪 from employees order by salary*12*(1+ifnull(commission_pct, 0)) desc; --按照年薪高低显示员工的信息和年薪（commission_pct表示奖金率）。

select *, salary*12*(1+ifnull(commission_pct, 0)) 年薪 from employees order by 年薪 desc;  --使用别名(年薪为别名）。

select last_name, salary from employees order by length(last_name) desc; -- 按照姓名显示员工的姓名和工资，使用了length(str)函数。

select * from employees order by salary ASC, employee_id desc;  --支持多字段排序，这里先按照工资升序，再按照员工ID从降序。