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值就一样

评论