MySQL - 组合 INSERT、VALUES 和 SELECT？

发布于 2024-11-09 05:45:57 字数 635 浏览 0 评论 0原文

我对 SQL 有点陌生，我实际上只是与它进行交互，足以让高分数据库正常工作。我正在使用 php 脚本来访问我的在线 MySQL 数据库。无论如何。

我有 2 个表，玩家和得分。玩家有一个 id、uniqueDeviceId 和 selectedname。 Score 具有您所期望的常见功能。

我真正想在单个查询中做的事情（以降低复杂性......）是将语法组合

INSERT INTO scores VALUES
and INSERT INTO scores SELECT...

成某种怪物，例如“

INSERT INTO scores(score,difficulty,playerid)
   VALUES(TheScoreThatImProviding,TheDifficultyThatImProviding, (SELECT 
       id FROM player WHERE uniqueDeviceId = TheUniqueIdThatImProviding)
     )

如果这是有道理的”。我想查找playerId（第三个“值”），并将该选择的结果与提供的VALUES的输入混合。

这可能吗？我发现的所有谷歌搜索结果要么都是值，要么都是选择。

原文

I'm kind of new to SQL, and I'm really only interacting with it enough to get a high score database working. I'm using a php script to access my online MySQL db. Anyways.

What I have is 2 tables, Player and Score. Player has an id, uniqueDeviceId, and chosenname.
Score has the usual things youd expect.

What I'd really like to do in a single query (to reduce complexity...) is to combine the syntaxes of

INSERT INTO scores VALUES
and INSERT INTO scores SELECT...

Into some sort of monster, like

INSERT INTO scores(score,difficulty,playerid)
   VALUES(TheScoreThatImProviding,TheDifficultyThatImProviding, (SELECT 
       id FROM player WHERE uniqueDeviceId = TheUniqueIdThatImProviding)
     )

If that makes sense. I want to lookup the playerId (3rd "value"), and mix the result of that select with the input of the VALUES provided.

Is this possible? All the googling results ive found either have it all VALUES or all SELECT.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

伤痕我心 2024-11-16 05:45:57

这是有道理的，被称为INSERT SELECT。这是对 uniqueDeviceId 123、分数 5 和难度“easy”的示例查询：

INSERT INTO scores (score, difficulty, playerid)
  SELECT 5, 'easy', id
  FROM player WHERE uniqueDeviceId = 123;

Makes sense and is called INSERT SELECT. This is an example query for the uniqueDeviceId 123, Score 5 and Difficulty 'easy':

INSERT INTO scores (score, difficulty, playerid)
  SELECT 5, 'easy', id
  FROM player WHERE uniqueDeviceId = 123;

回复收藏 0 原文

回眸一笑 2024-11-16 05:45:57

根据此页面您很接近。但将所有值放入您的选择中。像这样的东西：

insert into scores (score, difficulty, playerid )
    select TheScoreThatImProviding, TheDifficultyThatImProviding, player.id 
      from player where uniqueDeviceId = TheUniqueIdThatImProviding

According to this page you're close. But put all the values into your select. Something like:

insert into scores (score, difficulty, playerid )
    select TheScoreThatImProviding, TheDifficultyThatImProviding, player.id 
      from player where uniqueDeviceId = TheUniqueIdThatImProviding

回复收藏 0 原文

浅听莫相离 2024-11-16 05:45:57

这种方法怎么样：

INSERT INTO `tableA` SET 
           columnA =  (SELECT `columnY` FROM `tableY` WHERE `id` = 2), 
           columnB =  (SELECT `columnZ` FROM `tableB` WHERE `id` = 3);

我还没有对其进行基准测试。但使用多个 SELECT 语句会给数据库服务器带来负担。这种方法的优点是可读性。

What about this approach:

INSERT INTO `tableA` SET 
           columnA =  (SELECT `columnY` FROM `tableY` WHERE `id` = 2), 
           columnB =  (SELECT `columnZ` FROM `tableB` WHERE `id` = 3);

I didn't benchmarked it yet. But using multiple SELECT statements will burden the Database server. The plus side of this approach is the readability.

回复收藏 0 原文

~没有更多了~