Select 条件查询

root
233
文章
0
评论
2021年3月16日16:07:31 评论 4283字阅读14分16秒

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

 

 

 

 

 

 

 

继续阅读
weinxin
我的微信
这是我的微信扫一扫
  • 文本由 发表于 2021年3月16日16:07:31
  • 除非特殊声明,本站文章均为原创,转载请务必保留本文链接
Mysql 创建表 MySQL

Mysql 创建表

Mysql 创建表   Mysql创建数据库 CREATE DATABASE db_name //字符集 CHARACTER SET charset_name //校验规则 COLLATE ...
Mysql数据类型 MySQL

Mysql数据类型

Mysql数据类型   INT整形类型---属性 unsigned/signed 无符号/有符号 unsigned无符号,创建一个无符号的列 mysql> create table z...
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: