MySQL中比较运算符的使用
1. 加号 “+” 在MySQL中没有拼接的意思
如果是数值和字符 相+ 则字符会隐式转换为0
mysql> select 1+'hello' from dual;
+-----------+
| 1+'hello' |
+-----------+
| 1 |
+-----------+
1 row in set, 1 warning (0.04 sec)
mysql>
2. 只要有null值参与,运算结果都为null
mysql> select null = null from dual;
+-----------+
| null=null |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)
3. 安全等于 <=> 为null而生!!
mysql> select null <=> null from dual;
+---------------+
| null <=> null |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
4. IS NULL 或 ISNULL() 为null的值
练习:查询表中commission_pct为null的数据有哪些?
mysql> select last_name,salary,commission_pct from employees
-> where commission_pct is null;
或
mysql> select last_name,salary,commission_pct from employees
-> where isnull(commission_pct );
+-------------+----------+----------------+
| last_name | salary | commission_pct |
+-------------+----------+----------------+
| King | 24000.00 | NULL |
| Hartstein | 13000.00 | NULL |
| Gietz | 8300.00 | NULL |
+-------------+----------+----------------+
......
72 rows in set (0.00 sec)
5. IS NOT NULL 不为空的值
练习:查询表中commission_pct不为null的数据有哪些?
mysql> select last_name,salary,commission_pct from employees
-> where commission_pct is not null;
或
mysql> select last_name,salary,commission_pct from employees
-> where not commission_pct <=> null;
+------------+----------+----------------+
| last_name | salary | commission_pct |
+------------+----------+----------------+
| Russell | 14000.00 | 0.40 |
| Partners | 13500.00 | 0.30 |
| Errazuriz | 12000.00 | 0.30 |
+------------+----------+----------------+
......
35 rows in set (0.00 sec)
6. least(获取最小值)
mysql> select least(1,2,3,4,5) from dual;
+------------------+
| least(1,2,3,4,5) |
+------------------+
| 1 |
+------------------+
1 row in set (0.11 sec)
7. greatest(获取最大值)
mysql> select greatest(1,2,3,4,5) from dual;
+---------------------+
| greatest(1,2,3,4,5) |
+---------------------+
| 5 |
+---------------------+
1 row in set (0.00 sec)
8. between…and… 查询区间
(1、查询中,between 后面跟小的数,and 后面跟大的数!!!)
(2、查询结果 含头也含尾!!!)
练习:查询工资6000到8000的员工信息
mysql> select employee_id,last_name,salary
-> from employees
-> where salary between 6000 and 8000;
+-------------+-----------+---------+
| employee_id | last_name | salary |
+-------------+-----------+---------+
| 104 | Ernst | 6000.00 |
| 203 | Mavris | 7500.00 |
| 179 | Johnson | 6200.00 |
| 202 | Fay | 8000.00 |
+-------------+-----------+---------+
......
24 rows in set (0.22 sec)
练习:查询工资不在6000到8000的员工信息
mysql> select employee_id,last_name,salary
-> from employees
-> where salary not between 6000 and 8000;
+-------------+-------------+----------+
| employee_id | last_name | salary |
+-------------+-------------+----------+
| 100 | King | 24000.00 |
| 206 | Gietz | 8300.00 |
+-------------+-------------+----------+
......
83 rows in set (0.00 sec)
9. in(查询具体值的区间)
练习:查询部门为10,20,30号部门的员工信息
mysql> select last_name,salary,department_id
-> from employees
-> where department_id in(10,20,30);
+------------+----------+---------------+
| last_name | salary | department_id |
+------------+----------+---------------+
| Whalen | 4400.00 | 10 |
| Hartstein | 13000.00 | 20 |
| Fay | 6000.00 | 20 |
| Himuro | 2600.00 | 30 |
| Colmenares | 2500.00 | 30 |
+------------+----------+---------------+
......
9 rows in set (0.13 sec)
10. not in(查询不在具体值的区间)
练习:查询部门工资不为6000,7000,8000的员工信息
mysql> select last_name,salary,department_id
-> from employees
-> where salary not in(6000,7000,8000);
+-------------+----------+---------------+
| last_name | salary | department_id |
+-------------+----------+---------------+
| King | 24000.00 | 90 |
| Kochhar | 17000.00 | 90 |
| De Haan | 17000.00 | 90 |
| Hunold | 9000.00 | 60 |
| Gietz | 8300.00 | 110 |
+-------------+----------+---------------+
106 rows in set (0.34 sec)
......
11. like 模糊查询
(1、%代表任意字符)
(2、_代表一个字符)
(3、\ 代表转意字符)
练习1:查询last_name中包含字母 a 的员工姓名
mysql> select last_name from employees
-> where last_name like '%a%';
+------------+
| last_name |
+------------+
| Kochhar |
| Whalen |
| Hartstein |
| Fay |
| Mavris |
| Baer |
+------------+
.....
56 rows in set (0.00 sec)
练习2:查询第二个字符是a的员工信息
mysql> select last_name from employees
-> where last_name like '_a%';
练习3:查询第二个字符是 _ 的员工信息
mysql> select last_name from employees
-> where last_name like '_\_%';
12. MySQL在Dos命令窗口清屏使用:system cls
13. regexp\rlike :正则表达式
(1、^a, 匹配与a开头的字符串)
mysql> select
-> 'hello_mysql' regexp '^h'
-> from dual;
+---------------------------+
| 'hello_mysql' regexp '^h' |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.00 sec)
(2、a$, 匹配以a结尾的字符串)
mysql> select
-> 'hello_mysql' rlike 'l$'
-> from dual;
+--------------------------+
| 'hello_my' rlike 'y$' |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)
(3、a.b 匹配以a和b之间有任意一个单字符的字符串)
mysql> select
-> 'hello_java' regexp 'he..o'
-> from dual;
+-----------------------------+
| 'hello_java' regexp 'he..o' |
+-----------------------------+
| 1 |
+-----------------------------+
1 row in set (0.00 sec)
(4、[abc], 匹配包含字符 “a” 或 “b” 或 “c” 的字符串)
mysql> select
-> 'huangzuang' rlike '[hz]'
-> from dual;
+---------------------------+
| 'huangzuang' rlike '[hz]' |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.00 sec)