mysql - 是否可以使用 SELECT * from TableName 显示文本而不是 ID

Question

我有两个表格，如下所示:

人物详情

id     name    role_id
---------------------------
1      Hamdan    1
2      Sara      2
3      John      2

tbl_user_roles

id     role
---------------
1      Admin
2      Author

我需要的结果:

id     name    user_role
---------------------------
1      Hamdan    Admin
2      Sara      Author
3      John      Author

Note: is it possible? to use on [Select * from tblPerson] statement, because I do not want to type each field name like: I have these Two(2) solutions, but I want to use SELECT statement only.

解决方案一

SELECT p.id, p.person_name, roles.role
FROM person_detail p
INNER join user_roles roles
ON p.role_id = roles.id

解决方案2

SELECT p.id,
p.person_name,
(SELECT user_roles.role FROM dbo.user_roles WHERE id = p.role_id) As 'user_role' 
FROM dbo.person_detail p

::两者都很好，但我不想使用以上两种解决方案

Answer 1

这是另一种没有JOIN关键字的解决方案

示例 1:

SELECT
  *
FROM
  `person_detail`,
  `tbl_user_roles`
WHERE
  person_detail.role_id = tbl_user_roles.id

Note : Above query will return all columns from both tables

示例 2:

SELECT
  person_detail.id,person_detail.name,tbl_user_roles.role AS user_role
FROM
  `person_detail`,
  `tbl_user_roles`
WHERE
  person_detail.role_id = tbl_user_roles.id

Note : Above query will return selected columns from both tables

重要说明:但是您不应该使用这种类型的查询。操作多表数据时应使用JOIN关键字