MySQL数据库学习（6） -- 表查询关键词

最新推荐文章于 2024-09-12 09:40:12 发布

世界的隐喻

最新推荐文章于 2024-09-12 09:40:12 发布

阅读量481

点赞数 1

分类专栏： MySQL数据库学习文章标签： mysql 数据库学习

本文链接：https://blog.csdn.net/shijiedeyinyu/article/details/125105376

版权

本文详细介绍了MySQL数据库的查询操作，包括表查询关键词的执行顺序，如where进行数据过滤，group by进行数据分组，having在分组后设置筛选条件，distinct实现数据去重，order by用于结果排序，limit限制返回记录数，还涉及到正则表达式和连表、子查询等高级查询技巧，是MySQL学习者的重要参考资料。

摘要由CSDN通过智能技术生成

前期表准备

# 如果cmd窗口展示数据时，窗口最大，展示数据的虚表仍有错位，应使用 \G 分行显示
select * from t1\G;

# 个别电脑在插入中文的时候会出现乱码或者空白的现象，可以将字符编码统一设置成 gbk

create table emp (
    id int primary key auto_increment,
	name varchar(20) not null,
    sex enum("male", "female", "other") default "male", # 大部分是男的
    age int 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
);


# 插入记录
# 三个部门：教学，销售，运营
insert into emp(name, sex, age, hire_date, post, salary, office, depart_id) values
("jason", "male", 18, "20170301", "奥特曼", 7300.33, 401, 1), # 以下是教学部
("tom", "male", 78, "20150302", "teacher", 1000000.31, 401, 1),
("kevin", "male", 81, "20130305", "teacher", 8300, 401, 1),
("tony", "male", 73, "20140701", "teacher", 3500, 401, 1),
("owen", "male", 28, "20121101", "teacher", 2100, 401, 1),
("jack", "female", 18, "20110211", "teacher", 9000, 401, 1),
("jenny", "male", 18, "19000301", "teacher", 30000, 401, 1),
("sank", "male", 48, "20101111", "teacher", 10000, 401, 1),
("哈哈", "female", 48, "20150311", "sale", 3000.13, 402, 2), # 以下是销售部门
("呵呵", "female", 38, "20101101", "sale", 2000.35, 402, 2),
("西西", "female", 18, "20110312", "sale", 1000.37, 402, 2),
("乐乐", "female", 18, "20160513", "sale", 3000.29, 402, 2),
("拉拉", "female", 28, "20170127", "sale", 4000.33, 402, 2),
("僧龙", "male", 28, "20160311", "opeartion", 10000.13, 403, 3), # 以下是运营部
("程咬金", "male", 18, "19970312", "opeartion", 20000, 403, 3),
("程咬银", "female", 18, "20130311", "opeartion", 19000, 403, 3),
("程咬铜", "male", 18, "20150411", "opeartion", 18000, 403, 3),
("程咬铁", "female", 18, "20140512", "opeartion", 17000.13, 403, 3)；

几个关键字的执行顺序

# 书写顺序(英语语法)
select id,name from emp where id > 3;

# 执行顺序
from
where
select

# 执行顺序和书写顺序不一致，就按照书写顺序写 sql
	select * 先用 * 来占位
	之后补全后面的 sql 语句
	最后将 * 号替换成想要的具体字符

where过滤

# 作用：对整体数据的一个筛选操作（可以理解为 python 中的 if）

# 查询 id 大于等于 3 小于等于 6 的数据
select id,name,age from emp where id >= 3 and id <= 6;
select id,name,age from emp where id between 3 and 6; # 两种等价
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  3 | kevin |  81 |
|  4 | tony  |  73 |
|  5 | owen  |  28 |
|  6 | jack  |  18 |
+----+-------+-----+

# 查询薪资是 20000 或 18000 或 17000 的数据
select id, name, salary from emp where salary = 20000 or salary = 18000 or salary = 17000;
select id, name, salary from emp where salary in (20000, 18000, 17000);
+----+-----------+----------+
| id | name      | salary   |
+----+-----------+----------+
| 15 | 程咬金    | 20000.00 |
| 17 | 程咬铜    | 18000.00 |
+----+-----------+----------+

# 查询员工姓名中包含字母 o 的员工的姓名和薪资
select name, salary from emp where name like "%o%";
+-------+------------+
| name  | sa