sql:条件查询

条件查询
SELECT
    查询列表
FROM
    表名
WHERE
    筛选条件;
筛选条件：
1.按条件表达式筛选：
    ><= != <> >= <=
查询工资大于1.2w的员工信息
SELECT * FROM namelist WHERE salary>12000;
查询部门名不是光学组的员工编号
SELECT id AS '员工编号' FROM namelist WHERE departmentName!='光学组';

2.按逻辑表达式筛选：
查询工资在1w到2w之间的员工名，工资以及年薪
SELECT NAME AS '员工名',salary AS '工资',yearSalary AS '年薪' FROM namelist WHERE salary>10000&&salary<=20000;
查询id不等于1且工资高于15000的员工信息
SELECT *FROM namelist WHERE id!=1&&salary>=15000;
SELECT *FROM namelist WHERE NOT(id=1)&&salary>=15000;
    && || !
    AND OR NOT
SELECT *FROM namelist;
3.模糊查询：
SELECT * FROM namelist WHERE NAME LIKE '_v%';
SELECT *FROM namelist WHERE id BETWEEN 2 AND 3;
查询员工中名字包含字符a的员工信息：
SELECT *FROM namelist WHERE NAME LIKE '%a%';
查询员工编号在【2，3】之间的员工信息
SELECT * FROM namelist WHERE id BETWEEN 2 AND 3;
查询员工名中第三个字符为e，第五个字符为a的员工名和工资：
SELECT NAME,salary FROM namelist WHERE NAME LIKE 'e_a%';
查询员工名中第二个字符为_的员工名：
SELECT NAME FROM namelist WHERE NAME LIKE '_\_%';
查询员工的部门名称是光学组，材料组的员工姓名
SELECT NAME FROM namelist WHERE departmentName IN ('光学组','材料组');
查询没有年薪的员工名称：
SELECT NAME FROM namelist WHERE yearSalary IS NULL;
查询有年薪的员工名称：
SELECT NAME FROM namelist WHERE yearSalary IS NOT NULL;

    LIKE
    BETWEEN AND
    IN
    IS NULL
    
安全等与<=>
查询没有年薪的员工名称：
SELECT NAME FROM namelist WHERE yearSalary<=>NULL;