Select 条件查询

Select 条件查询

语法：

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

where分类：

• 按条件表达式筛选
  • 条件运算符：
    • >
    • <
    • =
    • <>
• 按逻辑表达式筛选
  • 逻辑运算符：(用于连接条件表达式）
    • &&
    • ||
    • ！
    • and
    • or 
    • not  (取反）
• 模糊查询
  • like
  • between and
  • in
  • is null

源数据表

mysql> select * from TABLES limit 1 \G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: information_schema
     TABLE_NAME: CHARACTER_SETS
     TABLE_TYPE: SYSTEM VIEW
         ENGINE: MEMORY
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: 384
    DATA_LENGTH: 0
MAX_DATA_LENGTH: 16434816
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2021-06-14 13:49:08
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: max_rows=43690
  TABLE_COMMENT: 
1 row in set (0.02 sec)

查询所有是innodb引擎的表

//查询引擎是innodb的
select
table_schema,
table_name,
engine,
sys.format_bytes(data_length) as data_size
FROM
	TABLES
where
	engine ='InnoDB'
	AND table_schema NOT IN ('mysql','performance_schema',
	'information_schema');

mysql>use information_schema;
mysql> select                                                                                                                      
    -> table_schema,
    -> table_name,
    -> engine,
    -> sys.format_bytes(data_length) as data_size
    -> FROM
    -> TABLES
    -> where
    -> engine ='InnoDB'
    -> AND table_schema NOT IN ('mysql','performance_schema',
    -> 'information_schema');
+--------------+-----------------------+--------+-----------+
| table_schema | table_name            | engine | data_size |
+--------------+-----------------------+--------+-----------+
| sys          | sys_config            | InnoDB | 16.00 KiB |
| wordpress    | wp_commentmeta        | InnoDB | 16.00 KiB |
| wordpress    | wp_comments           | InnoDB | 16.00 KiB |
| wordpress    | wp_links              | InnoDB | 16.00 KiB |
| wordpress    | wp_options            | InnoDB | 1.52 MiB  |
| wordpress    | wp_postmeta           | InnoDB | 1.52 MiB  |
| wordpress    | wp_posts              | InnoDB | 4.52 MiB  |
| wordpress    | wp_term_relationships | InnoDB | 16.00 KiB |
| wordpress    | wp_term_taxonomy      | InnoDB | 16.00 KiB |
| wordpress    | wp_termmeta           | InnoDB | 16.00 KiB |
| wordpress    | wp_terms              | InnoDB | 16.00 KiB |
| wordpress    | wp_tm_taskmeta        | InnoDB | 16.00 KiB |
| wordpress    | wp_tm_tasks           | InnoDB | 16.00 KiB |
| wordpress    | wp_usermeta           | InnoDB | 16.00 KiB |
| wordpress    | wp_users              | InnoDB | 16.00 KiB |
+--------------+-----------------------+--------+-----------+
15 rows in set (0.02 sec)

按条件表达式筛选

练习1，查询工资大于12000的员工信息

mysql> select * from employees where salary>12000;

练习2，查询不等于90的员工名，和部门编号

mysql> select last_name,department_id from employees where salary<>90;

按逻辑查询表达式筛选

练习1，查询工资z在10000到2w之间的员工名，工资及奖金

select last_name,salary,commission_pct FROM employees WHERE salary>=10000 and salary<=20000;

练习2，查询部门编号不是在90-110之间，或者工资高与15000的员工信息

方法一：

SELECT department_id,salary FROM employees WHERE department_id<90 or department_id>110 or salary>15000;

方法二：

SELECT department_id,salary FROM employees WHERE not(department_id<90 and department_id>110) or salary>15000;

模糊查询

一般和通配符搭配使用：

• % 任意多个字符
• _   任意单个字符，就一个

1.like

练习1，查询员工名中包含字符a的员工信息(%代表通配符)

SELECT * FROM employees WHERE last_name LIKE '%a%';

练习2，查询员工名中第三个字符为e，第五个字符为a的员工们和工资

SELECT last_name，salary FROM employees WHERE last_name LIKE '__e_a%'

练习3，查询员工名中第二个字符为_的员工名字

SELECT laste_name FROM employees WHERE last_name LIKE '_\_%'

方法二：

escape 代表说明，a为转移符号

SELECT laste_name FROM employees WHERE last_name LIKE '_a_%' ESCAPE 'a';

2.between and

在什么什么之间

练习1，查询员工编号在100-200之间的员工信息

条件判断的写法

SELECT  * FROM employees WHERE xx>=100 and xx<=200

between and写法

SELECT * FROM employees WHERE  xx BETWEEN 100 AND 120；

3.in

in列表的值类型要统一，不支持通配符

含义：判断某字段的值是否属于in列表中的某一项

练习1，查询员工的工种编号是 IT_PROG,AD_VP,AD/-PRESd中的一个员工名和工种编号

方法一：

SELECT last_name,job_id FROM employees WHERE job_id='IT_PROT' OR job_id='AD_VP' OR job_id='AD_PRES';

方法二：

SELECT last_name,job_id FROM employees WHERE job_id IN('IT_PROT','AD_VP','AD_PRES');

4. is null

查看数据类型为NULL的

练习1，查询没有奖金的员工和奖金名单

SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;

练习2，查询有奖金的员工和奖金名单

SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;

安全等于 <=>

作用：判断是否等于

练习1，查询没有奖金的员工和奖金名单

SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;

函数ISNULL

判断返回值，如果数据类型为NULL，返回值为1，不为NULL返回值为0

SELECT ISNULL(commission_pct),commission_pct FROM employess;

条件查询练习题

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

SELECT last_name,salary FROM employees WHERE salary>=12000;

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

SELECT last_name,
       deparment_id,
       salary*12*(1+IFNULL(commission_pct,0) AS 年薪
FROM
       employees;

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

SELECT last_name,salary FROM employees WHERE salary>5000 and  salary<12000;

4.查询没有奖金，且工资小于18000的salary，last_name

SELECT
      last_name,
      salart,
FROM
      employees
WHERE
       commission_pct IS NULL and salary<18000;

5.查询一下employees表中，job_id不为'IT'或者工资为12000的员工信息

SELECT
       *
FROM
       employeess
WHERE
       job_id<>'IT' OR salart=12000;

6.试问SELECT * FROM employees;和SELECT * FROM employees WHERE commission_pct LIKE '%%' and last_name LIKE '%%';结果是否一样？并说明原因

不一样，如果判断的字段中有NULL值的就不一样，没有NULL值就一样