查看“Mysql常见查询示例”的源代码
←
Mysql常见查询示例
跳到导航
跳到搜索
因为以下原因,您没有权限编辑本页:
您请求的操作仅限属于该用户组的用户执行:
用户
您可以查看和复制此页面的源代码。
以下是使用MySQL解决一些常见问题的示例。 ===创建表=== 其中一些示例使用表格shop来存储每个交易者(dealer)的每个商品(item number)的价格。假设每个交易者对于每个商品都有一个固定的价格,那么(article,dealer)是记录的主键。 启动命令行工具mysql并选择一个数据库: $> mysql your-database-name 使用以下语句创建并填充示例表格: CREATE TABLE shop ( article INT UNSIGNED DEFAULT '0000' NOT NULL, dealer CHAR(20) DEFAULT '' NOT NULL, price DECIMAL(16,2) DEFAULT '0.00' NOT NULL, PRIMARY KEY(article, dealer)); INSERT INTO shop VALUES (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45), (3,'C',1.69),(3,'D',1.25),(4,'D',19.95); 执行完语句后,表格应该有以下内容: SELECT * FROM shop ORDER BY article; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 1 | A | 3.45 | | 1 | B | 3.99 | | 2 | A | 10.99 | | 3 | B | 1.45 | | 3 | C | 1.69 | | 3 | D | 1.25 | | 4 | D | 19.95 | +---------+--------+-------+ ===查询列的最大值=== SELECT MAX(article) AS article FROM shop; +---------+ | article | +---------+ | 4 | +---------+ ===找到某一列最大值所在行=== 这可以通过子查询轻松完成: SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop); +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0004 | D | 19.95 | +---------+--------+-------+ 另一种解决方案是使用 LEFT JOIN,如下所示: SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL; 你也可以通过按价格降序排序所有行,并使用 MySQL 特定的 LIMIT 子句仅获取第一行,像这样: SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1; 注意 如果有几个价格都是 19.95 的最贵商品,则 LIMIT 解决方案只会显示其中一个。 ===找到每个组的最大列值=== SELECT article, MAX(price) AS price FROM shop GROUP BY article ORDER BY article; +---------+-------+ | article | price | +---------+-------+ | 0001 | 3.99 | | 0002 | 10.99 | | 0003 | 1.69 | | 0004 | 19.95 | +---------+-------+ ===找到持有某一列分组最大值的行=== 可以使用以下子查询解决此问题: SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article) ORDER BY article; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | C | 1.69 | | 0004 | D | 19.95 | +---------+--------+-------+ 上述示例使用了相关子查询,这可能效率不高。解决该问题的其他可能性是在FROM子句中使用非相关子查询、LEFT JOIN或带有窗口函数的公共表达式。 非相关子查询: SELECT s1.article, dealer, s1.price FROM shop s1 JOIN ( SELECT article, MAX(price) AS price FROM shop GROUP BY article) AS s2 ON s1.article = s2.article AND s1.price = s2.price ORDER BY article; LEFT JOIN: SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article; LEFT JOIN基于这样的事实:当s1.price达到最大值时,没有s2.price的值更大,因此相应的s2.article值为NULL。 带有窗口函数的公共表达式: WITH s1 AS ( SELECT article, dealer, price, RANK() OVER (PARTITION BY article ORDER BY price DESC ) AS `Rank` FROM shop ) SELECT article, dealer, price FROM s1 WHERE `Rank` = 1 ORDER BY article; ===使用用户定义变量=== 您可以使用MySQL用户变量来记住结果,而无需将其存储在客户端的临时变量中。 例如,要查找价格最高和最低的文章,可以执行以下操作: mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop; mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0003 | D | 1.25 | | 0004 | D | 19.95 | +---------+--------+-------+ 注意 还可以将数据库对象(例如表或列)的名称存储在用户变量中,然后在SQL语句中使用此变量;但是,这需要使用准备好的语句。 ===使用外键=== MySQL支持外键,允许跨表引用相关数据,并且外键约束可以帮助保持相关数据的一致性。 外键关系涉及一个保存初始列值的父表和一个引用父列值的子表。外键约束定义在子表上。 以下示例通过单列外键关系关联父表和子表,并展示了外键约束如何强制引用完整性。 创建父表和子表: CREATE TABLE parent ( id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE child ( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ) ENGINE=INNODB; 向父表中插入一行: mysql> INSERT INTO parent (id) VALUES (1); 验证数据是否插入成功: mysql> SELECT * FROM parent; +----+ | id | +----+ | 1 | +----+ 向子表中插入一行: mysql> INSERT INTO child (id,parent_id) VALUES (1,1); 插入操作成功,因为父表中存在parent_id为1的记录。 向子表中插入一个parent_id值在父表中不存在的行: mysql> INSERT INTO child (id,parent_id) VALUES(2,2); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (test.child, CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id)) 该操作失败,因为指定的parent_id值在父表中不存在。 尝试从父表中删除先前插入的行: mysql> DELETE FROM parent WHERE id VALUES = 1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (test.child, CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id)) 该操作失败,因为子表中的记录包含引用的id(parent_id)值。 当一项操作影响父表中具有匹配行的键值时,其结果取决于FOREIGN KEY子句中的ON UPDATE和ON DELETE子句指定的引用操作。省略ON DELETE和ON UPDATE子句(与当前子表定义相同)等同于指定RESTRICT选项,该选项拒绝影响父表中具有匹配行的键值的操作。 为了演示ON DELETE和ON UPDATE引用操作,删除子表并重新创建它以包括ON UPDATE和ON DELETE子句及CASCADE选项。CASCADE选项在删除或更新父表中的行时自动删除或更新子表中的匹配行。 DROP TABLE child; CREATE TABLE child ( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=INNODB; 向子表中插入以下行: mysql> INSERT INTO child (id,parent_id) VALUES(1,1),(2,1),(3,1); 验证数据是否插入成功: mysql> SELECT * FROM child; +------+-----------+ | id | parent_id | +------+-----------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +------+-----------+ 更新父表中的id,将其从1更改为2。 mysql> UPDATE parent SET id = 2 WHERE id = 1; 验证更新是否成功: mysql> SELECT * FROM parent; +----+ | id | +----+ | 2 | +----+ 验证ON UPDATE CASCADE引用操作是否更新了子表: mysql> SELECT * FROM child; +------+-----------+ | id | parent_id | +------+-----------+ | 1 | 2 | | 2 | 2 | | 3 | 2 | +------+-----------+ 为演示ON DELTE CASCADE引用操作,从父表中删除parent_id = 2的记录,这将删除父表中的所有记录。 mysql> DELETE FROM parent WHERE id = 2; 由于子表中的所有记录都与parent_id = 2相关联,因此ON DELETE CASCADE引用操作将从子表中删除所有记录: mysql> SELECT * FROM child; Empty set (0.00 sec) ===在两个键上搜索=== 使用单个键的OR和AND处理都经过了优化。 唯一棘手的情况是在两个不同的键上进行OR组合搜索: SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' OR field2_index = '1' 这种情况已经经过了优化。 您还可以通过使用联合将两个单独的SELECT语句的输出组合来有效地解决问题。 每个SELECT仅搜索一个键,并且可以进行优化: SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' UNION SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1'; ===计算每日访问量=== 以下示例展示了如何使用位组函数来计算用户每月访问网页的天数。 CREATE TABLE t1 (year YEAR, month INT UNSIGNED, day INT UNSIGNED); INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2), (2000,2,23),(2000,2,23); 该示例表包含表示用户访问页面的年-月-日值。要确定每个月这些访问发生了多少不同的天数,使用以下查询: SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month; 返回: +------+-------+------+ | year | month | days | +------+-------+------+ | 2000 | 1 | 3 | | 2000 | 2 | 2 | +------+-------+------+ 该查询计算了每年/月组合在表中出现了多少不同的天数,并自动删除重复条目。 ===使用AUTO_INCREMENT=== AUTO_INCREMENT属性可用于为新行生成唯一标识符: CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ); INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'), ('lax'),('whale'),('ostrich'); SELECT * FROM animals; 返回: +----+---------+ | id | name | +----+---------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | +----+---------+ 没有为AUTO_INCREMENT列指定值,因此MySQL自动分配序列号。您还可以显式地将0分配给该列以生成序列号,除非启用了NO_AUTO_VALUE_ON_ZERO SQL模式。例如: INSERT INTO animals (id,name) VALUES(0,'groundhog'); 如果列声明为NOT NULL,则还可以将NULL分配给该列以生成序列号。例如: INSERT INTO animals (id,name) VALUES(NULL,'squirrel'); 当您将任何其他值插入AUTO_INCREMENT列时,该列将设置为该值,并将序列重置,以便下一个自动生成的值从最大列值顺序地跟随。例如: INSERT INTO animals (id,name) VALUES(100,'rabbit'); INSERT INTO animals (id,name) VALUES(NULL,'mouse'); SELECT * FROM animals; +-----+-----------+ | id | name | +-----+-----------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | | 7 | groundhog | | 8 | squirrel | | 100 | rabbit | | 101 | mouse | +-----+-----------+ 更新现有的AUTO_INCREMENT列值还会重置AUTO_INCREMENT序列。 您可以使用LAST_INSERT_ID() SQL函数或mysql_insert_id() C API函数检索最近自动生成的AUTO_INCREMENT值。这些函数是特定于连接的,因此它们的返回值不受另一个执行插入的连接的影响。 使用最小的整数数据类型作为AUTO_INCREMENT列,该类型足够大以容纳所需的最大序列值。当列达到数据类型的上限时,下一个生成序列号的尝试将失败。如果可能的话,请使用UNSIGNED属性以允许更大的范围。例如,如果使用TINYINT,则最大允许的序列号为127。对于TINYINT UNSIGNED,最大值为255。 注意 对于多行插入,LAST_INSERT_ID()和mysql_insert_id()实际上返回插入行中的第一个AUTO_INCREMENT键。这使得可以在复制设置中的其他服务器上正确重现多行插入。 要从1开始的AUTO_INCREMENT值开始,请使用CREATE TABLE或ALTER TABLE设置该值,如下所示: mysql> ALTER TABLE tbl AUTO_INCREMENT = 100; MyISAM注释 对于MyISAM表,您可以在多列索引中的次要列上指定AUTO_INCREMENT。在这种情况下,为AUTO_INCREMENT列生成的值被计算为MAX(auto_increment_column) + 1 WHERE prefix=given-prefix。当您想要将数据放入有序组中时,这非常有用。 CREATE TABLE animals ( grp ENUM('fish','mammal','bird') NOT NULL, id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (grp,id) ) ENGINE=MyISAM; INSERT INTO animals (grp,name) VALUES ('mammal','dog'),('mammal','cat'), ('bird','penguin'),('fish','lax'),('mammal','whale'), ('bird','ostrich'); SELECT * FROM animals ORDER BY grp,id; 返回: +--------+----+---------+ | grp | id | name | +--------+----+---------+ | fish | 1 | lax | | mammal | 1 | dog | | mammal | 2 | cat | | mammal | 3 | whale | | bird | 1 | penguin | | bird | 2 | ostrich | +--------+----+---------+ 在这种情况下(当AUTO_INCREMENT列是多列索引的一部分时),如果您删除任何组中具有最大AUTO_INCREMENT值的行,则会重用AUTO_INCREMENT值。即使对于MyISAM表,AUTO_INCREMENT值通常也不会被重用。 如果AUTO_INCREMENT列是多个索引的一部分,则MySQL使用以AUTO_INCREMENT列开头的索引生成序列值(如果有)。例如,如果animals表包含PRIMARY KEY (grp, id)和INDEX (id)索引,则MySQL将忽略PRIMARY KEY以生成序列值。因此,表将包含单个序列,而不是每个grp值的序列。
返回至“
Mysql常见查询示例
”。
导航菜单
个人工具
登录
命名空间
页面
讨论
大陆简体
查看
阅读
查看源代码
查看历史
更多
搜索
导航
首页
基础知识
正则表达式
Markdown
分布式
项目管理
系统集成项目管理基础知识
云原生
Docker
云原生安全
云原生词汇表
十二因素应用
Kubernetes
音频处理
音频合成
Edge-tts
CMS系统
Docsify
VuePress
Mediawiki
自动生成
Marp
CI/CD
GitLab
设计
颜色
平面设计
AI
数字人
操作系统
GNU/Linux
数据库
Mysql
工具
链入页面
相关更改
特殊页面
页面信息