【9.0】MySQL之过滤条件

最新推荐文章于 2024-08-17 19:36:04 发布

Chimengmeng

最新推荐文章于 2024-08-17 19:36:04 发布

阅读量1k

点赞数 11

文章标签： mysql oracle 数据库

本文链接：https://blog.csdn.net/Chimengmeng/article/details/136012493

版权

【一】查询语法

【1】语法

select */字段名 from 表名 where 筛选条件;

【2】执行顺序

from 
where
select

【3】模版

虽然执行顺序和书写顺序不一致，但是可以按照书写顺序写SQL语句

# 先用 * 占位，再去补全完整的 SQL 语句
select * from * where *
# * 替换成想要的字段

【二】数据准备

【1】创建数据库

drop table if exists emp_data;

create database emp_data;

【2】创建表

use emp_data;

create table emp(
	  id int not null unique auto_increment,
    name varchar(20) not null,
    sex enum("male","female") not null default "male",
    age int(3) unsigned not null default 28,
    hire_date date not null,
    post varchar(50),
    post_comment varchar(100),
    salary double(15,2),
    office int,
    depart_id int
);

查看表结构

desc emp;

+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum('male','female') | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)

【3】插入测试数据

insert into emp(name, sex, age, hire_date, post, salary, office, depart_id) values
("dream", "male", 78, '20220306', "陌夜痴梦久生情", 730.33, 401, 1), # 以下是教学部
("mengmeng", "female", 25, '20220102', "teacher", 12000.50, 401, 1),
("xiaomeng", "male", 35, '20190607', "teacher", 15000.99, 401, 1),
("xiaona", "female", 29, '20180906', "teacher", 11000.80, 401, 1),
("xiaoqi", "female", 27, '20220806', "teacher", 13000.70, 401, 1),
("suimeng", "male", 33, '20230306', "teacher", 14000.62, 401, 1), # 以下是销售部
("娜娜", "female", 69, '20100307', "sale", 300.13, 402, 2),
("芳芳", "male", 45, '20140518', "sale", 400.45, 402, 2),
("小明", "male", 34, '20160103', "sale", 350.80, 402, 2),
("亚洲", "female", 42, '20170227', "sale", 320.99, 402, 2),
("华华", "female", 55, '20180319', "sale", 380.75, 402, 2),
("田七", "male", 44, '20230808', "sale", 420.33, 402, 2), # 以下是运行部
("大古", "female", 66, '20180509', "operation", 630.33, 403, 3),
("张三", "male", 51, '20191001', "operation", 410.25, 403, 3),
("李四", "male", 47, '20200512', "operation", 330.62, 403, 3),
("王五", "female", 39, '20210203', "operation", 370.98, 403, 3),
("赵六", "female", 36, '20220724', "operation", 390.15, 403, 3);

# Query OK, 17 rows affected (0.17 sec)
# Records: 17  Duplicates: 0  Warnings: 0

查看数据

select * from emp;

格式化美化数据

select * from emp\G;

【三】筛选条件之where

【1】作用

对整体数据的筛选

【2】查询`3<=id<=6`的数据

查询数据方式一

select id,name,age from emp where id >=3 and id <=6;

+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  3 | xiaomeng |  35 |
|  4 | xiaona   |  29 |
|  5 | xiaoqi   |  27 |
|  6 | suimeng  |  33 |
+----+----------+-----+
4 rows in set (0.00 sec)

查询数据方式二

select id,name,age from emp where id between 3 and 6;

+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  3 | xiaomeng |  35 |
|  4 | xiaona   |  29 |
|  5 | xiaoqi   |  27 |
|  6 | suimeng  |  33 |
+----+----------+-----+
4 rows in set (0.00 sec)

【3】查询薪资是1w2或者1w3或者7300 的数据

查询数据方式一

select * from emp where salary=12000.50 or salary = 13000.70 or salary = 7300.33;

+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name     | sex    | age | hire_date  | post                  | post_comment | salary   | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+|  1 | dream    | male   |  78 | 2022-03-06 | 陌夜痴梦久生情        | NULL         |  7300.33 |    401 |         1 ||  2 | mengmeng | female |  25 | 2022-01-02 | teacher               | NULL         | 12000.50 |    401 |         1 ||  5 | xiaoqi   | female |  27 | 2022-08-06 | teacher               | NULL         | 13000.70 |    401 |         1 ||  8 | mengmeng | female |  25 | 2022-01-02 | teacher               | NULL         | 12000.50 |    401 |         1 || 11 | xiaoqi   | female |  27 | 2022-08-06 | teacher               | NULL         | 13000.70 |    401 |         1 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+5 rows in set (0.00 sec)

查询方式二

select * from emp where salary in (12000.50,13000.70,7300.33);

+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name     | sex    | age | hire_date  | post                  | post_comment | salary   | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+|  1 | dream    | male   |  78 | 2022-03-06 | 陌夜痴梦久生情        | NULL         |  7300.33 |    401 |         1 ||  2 | mengmeng | female |  25 | 2022-01-02 | teacher               | NULL         | 12000.50 |    401 |         1 ||  5 | xiaoqi   | female |  27 | 2022-08-06 | teacher               | NULL         | 13000.70 |    401 |         1 ||  8 | mengmeng | female |  25 | 2022-01-02 | teacher               | NULL         | 12000.50 |    401 |         1 || 11 | xiaoqi   | female |  27 | 2022-08-06 | teacher               | NULL         | 13000.70 |    401 |         1 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+5 rows in set (0.00 sec)

【3】查询员工姓名中包含字母o的姓名和薪资

模糊查询：like

% 任意

- 任意单个字符

查询数据

select name,salary from emp where name like "%o%";

+----------+----------+
| name     | salary   |
+----------+----------+
| xiaomeng | 15000.99 |
| xiaona   | 11000.80 |
| xiaoqi   | 13000.70 |
| xiaomeng | 15000.99 |
| xiaona   | 11000.80 |
| xiaoqi   | 13000.70 |
+----------+----------+
6 rows in set (0.00 sec)

【4】查询员工姓名是由六个字符组成的姓名和薪资

查询数据方式一

select name,salary from emp where name like "______";

+--------+----------+
| name   | salary   |
+--------+----------+
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
+--------+----------+
4 rows in set (0.00 sec)

查询数据方式二

select name,salary from emp where char_length(name) = 6;

+--------+----------+
| name   | salary   |
+--------+----------+
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
+--------+----------+
4 rows in set (0.00 sec)

【5】查询 id<3 或者 id>6 的数据

select * from emp where id not between 3 and 6;

+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name     | sex    | age | hire_date  | post                  | post_comment | salary   | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+|  1 | dream    | male   |  78 | 2022-03-06 | 陌夜痴梦久生情        | NULL         |  7300.33 |    401 |         1 ||  2 | mengmeng | female |  25 | 2022-01-02 | teacher               | NULL         | 12000.50 |    401 |         1 ||  7 | dream    | male   |  78 | 2022-03-06 | 陌夜痴梦久生情        | NULL         |   730.33 |    401 |         1 ||  8 | mengmeng | female |  25 | 2022-01-02 | teacher               | NULL         | 12000.50 |    401 |         1 ||  9 | xiaomeng | male   |  35 | 2019-06-07 | teacher               | NULL         | 15000.99 |    401 |         1 || 10 | xiaona   | female |  29 | 2018-09-06 | teacher               | NULL         | 11000.80 |    401 |         1 || 11 | xiaoqi   | female |  27 | 2022-08-06 | teacher               | NULL         | 13000.70 |    401 |         1 || 12 | suimeng  | male   |  33 | 2023-03-06 | teacher               | NULL         | 14000.62 |    401 |         1 || 13 | 娜娜     | female |  69 | 2010-03-07 | sale                  | NULL         |   300.13 |    402 |         2 || 14 | 芳芳     | male   |  45 | 2014-05-18 | sale                  | NULL         |   400.45 |    402 |         2 || 15 | 小明     | male   |  34 | 2016-01-03 | sale                  | NULL         |   350.80 |    402 |         2 || 16 | 亚洲     | female |  42 | 2017-02-27 | sale                  | NULL         |   320.99 |    402 |         2 || 17 | 华华     | female |  55 | 2018-03-19 | sale                  | NULL         |   380.75 |    402 |         2 || 18 | 田七     | male   |  44 | 2023-08-08 | sale                  | NULL         |   420.33 |    402 |         2 || 19 | 大古     | female |  66 | 2018-05-09 | operation             | NULL         |   630.33 |    403 |         3 || 20 | 张三     | male   |  51 | 2019-10-01 | operation             | NULL         |   410.25 |    403 |         3 || 21 | 李四     | male   |  47 | 2020-05-12 | operation             | NULL         |   330.62 |    403 |         3 || 22 | 王五     | female |  39 | 2021-02-03 | operation             | NULL         |   370.98 |    403 |         3 || 23 | 赵六     | female |  36 | 2022-07-24 | operation             | NULL         |   390.15 |    403 |         3 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+19 rows in set (0.00 sec)