算数运算符

  1. SELECT 100 + '1'   # 在java语言中，结果是1001。SQL中+没有连接作用,会将字符串转换为数值（隐式转换）
  2. FROM DUAL;

分母如果为0，结果为null

  • 取模运算 
  1. 查询员工id为偶数的员工信息
  2. SELECT employee_id,last_name,salary
  3. FROM employees
  4. WHERE employee_id % 2 = 0;

比较运算符

  • 字符串存在隐式转换，如果转换数值不成功，则看作0

image.png

  • 只要有null参与判断，结果就为null

<=>：安全等于

具体符号

IS NULL \ IS NOT NULL \ISNULL

  • 查询表中commission_pct为Null的数据有哪些

    1. SELECT last_name,salary,commission_pct
    2. FROM employees
    3. WHERE commission_pct IS NULL

  • 查询表中commission_pct不为Null的数据有哪些

    1. SELECT last_name,salary,commission_pct
    2. FROM employees
    3. WHERE commission_pct IS NOT NULL

  • ISNULL类似于函数

    1. SELECT last_name,salary,commission_pct
    2. FROM employees
    3. WHERE ISNULL(commission_pct)

    LEAST() \ GREATEST

  • 最大最小

    1. SELECT LEAST('g','b','t','m'),GREATEST('g','b','t','m')
    2. FROM employees;

    BETWEEN…AND

  • 查询工资在6000到8000的员工信息

    1. SELECT employee_id,last_name,salary
    2. FROM employees
    3. WHERE salary BETWEEN 6000 AND 8000;
    4. #WHERE salary >=6000 && salary <= 8000;

    in(set) \ not in(set)

  • 查询部门为10,20,30部门的员工信息

    1. SELECT last_name,salary,department_id
    2. FROM employees
    3. WHERE department_id IN (10,20,30);

    LIKE：模糊查询

  • 查询last_name中包含字符’a’的员工信息

    1. SELECT last_name
    2. FROM employees
    3. WHERE last_name LIKE '%a%';

  • %：代表不确定个数的字符

  • _：代表一个不确定的字符

  • 查询第二个字符是’a’的员工信息

    1. SELECT last_name
    2. FROM employees
    3. WHERE last_name LIKE '_a%';

  • 查询第二个字符是且第三个字符是’a’的员工信息 ```sql 需要使用转义字符 \ SELECT last_name FROM employees WHERE last_name LIKE ‘_a%’;

  1. <a name="yZIkH"></a>
  2. ### 正则表达式 REGEXP \ RLIKE
  4. - ^   以  为开头
  5. - $    以  为结尾
  6. ```sql
  7. SELECT 'shkstart' REGEXP '^s','shkstart' REGEXP 't$','shkstart' REGEXP 'hk'
  8. FROM DUAL;
  1. SELECT 'atugigu' REGEXP 'gu.gu'

逻辑运算符

  • OR || AND && NOT ! XOR
  • 部门id等于50且工资小于6000.或部门id不等于50且工资大于6000的员工信息
    1. SELECT last_name,salary,department_id
    2. FROM employees
    3. WHERE department_id = 50 XOR salary > 6000;

    位运算符

& | ^ ~ << >>

课后练习

  • 选择工资不在5000到12000的员工的姓名和工资 ```sql SELECT last_name,salary FROM employees WHERE salary < 5000 or salary >12000

WHERE salary NOT BETWEEN 5000 AND 12000

  2. - 选择在20或50号部门工作的员姓名和部门号
  3. ```sql
  4. SELECT last_name,salary
  5. FROM employees
  6. WHERE department_id = 20 OR department_id = 50;
  8. WHERE department_id IN (20,50)

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

    1. SELECT last_name,job_id
    2. FROM employees
    3. WHERE department_id IS NULL;

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

    1. SELECT last_name,salary,
    2. FROM employees
    3. WHERE bonus IS NOT NULL

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

    1. SELECT last_name
    2. FROM employees
    3. WHERE last_name LIKE '__a%';

  • 选择姓名中有字母a和k的员工姓名

    1. SELECT last_name
    2. FROM employees
    3. WHERE last_name LIKE '%a%' AND last_name LIKE '%k%';

  • 显示表中first_name以’e’结尾的员工信息

    1. SELECT *
    2. FROM employees
    3. WHERE first_name LIKE '%e';
    4. #正则表达式
    5. WHERE first_name REGEXP 'e$';

  • 显示表中部门编号在80-100之间的姓名、工种 ```sql SELECT last_name,job_id FROM employees WHERE department_id between 80 and 100

```