使用MySQL命令行,如何在Select语句中仅显示日期或金额(十进制)列?

时间:2021-10-29 01:42:58

I am new to SQL and I realize I have a lot to learn. I am in the midst of converting a set of ISAM files into MySQL tables. Some of the tables I am creating have hundreds of columns. To make it easier to see if my conversions are working properly, I would like to display only a subset of columns at a time in my SELECT results.

我是SQL新手,我意识到我有很多需要学习的东西。我正在将一组ISAM文件转换为MySQL表。我正在创建的一些表有数百列。为了更容易查看我的转换是否正常工作,我想在SELECT结果中一次只显示一列列。

For example, I would like to see only Dates or only Amounts (i.e., Decimals). This would make it easier to see if correct values are in those fields.

例如,我只想看Dates或Amounts(即Decimals)。这样可以更容易地查看这些字段中是否存在正确的值。

I am working from the MySQL command line and I would rather not specify a long list of column names if it can be avoided. Is there a way to get my Select statements to display only the types of columns I am interested in?

我正在从MySQL命令行工作,如果可以避免,我宁愿不指定一长列列名。有没有办法让我的Select语句只显示我感兴趣的列类型?


(Added 5/12/16) I used a variant of Uueerdo's syntax to create a view:

(已添加5/12/16)我使用了Uueerdo语法的变体来创建视图:

create view dates_view as concat('select ', group_concat(column_name), ' ',' from ', table_schema, '.', table_name, ';') from information_schema.columns where table_name = 'binders_tbl' and data_type in ('date');

创建视图dates_view为concat('select',group_concat(column_name),'','from',table_schema,'。',table_name,';')来自information_schema.columns,其中table_name ='binders_tbl'和data_type in('date “);

When I enter select * from dates_view; I get this value:

当我从dates_view输入select *时;我得到这个值:

select binder_effectiveDate,binder_expirationDate,binder_submissionCreatedDate,binder_f‌​irstQuotedDate,binder_boundDate,binder_invoicedDate,binder_terminatedDate,binder_‌​cancelledDate from aesdb.binders_tbl;

从aesdb.binders_tbl中选择binder_effectiveDate,binder_expirationDate,binder_submissionCreatedDate,binder_f irstQuotedDate,binder_boundDate,binder_invoicedDate,binder_terminatedDate,binder_ cancelledDate;

I tried but could not find a way to use this view with "select * from binders_tbl;" to display only the above date fields and their values.

我试过但是找不到使用“select * from binders_tbl”这个视图的方法。仅显示上述日期字段及其值。

If anyone can guide me to a "join" or "subquery" solution, I would appreciate it. For now, I am going to stick with listing all the individual columns as needed. Plus, I will study more basic and intermediate SQL. Thank you to those who responded.

如果有人可以引导我进入“加入”或“子查询”解决方案,我将不胜感激。现在,我将坚持根据需要列出所有单独的列。另外,我将学习更多基础和中级SQL。谢谢那些回复的人。


(Added 5/20/16) I found a sort-of work-around to my problem. Here it is in case it helps someone else:

(已添加5/20/16)我发现了一个解决问题的方法。这是为了以防万一:

CREATE VIEW DateCols_View AS SELECT Table1_SelectionCriteriaField, Table1_Date1, Table1_Date2, Table1_Date3 FROM Table1;

CREATE VIEW DateCols_View AS SELECT Table1_SelectionCriteriaField,Table1_Date1,Table1_Date2,Table1_Date3 FROM Table1;

SELECT * FROM DateCols_View WHERE Table1_SelectionCriteriaField (matches some criteria);

SELECT * FROM DateCols_View WHERE Table1_SelectionCriteriaField(匹配某些条件);

My work-around has 2 drawbacks. The first is that I have to include any selection criteria fields that I plan to use later. But with a little forethought I can create multiple views with different sets of columns for different displays. This will let me display only those columns I wish to see while working with a set of records. I plan to create separate views for Dates, Amounts, Flags, etc.

我的解决方法有两个缺点。首先,我必须包括我计划稍后使用的任何选择标准字段。但是稍微考虑一下,我可以为不同的显示器创建具有不同列的多个视图。这将让我只显示我希望在处理一组记录时看到的列。我打算为Dates,Amounts,Flags等创建单独的视图。

The other drawback is that I have to create a separate set of views for each table or join combination I plan to work with.

另一个缺点是我必须为我计划使用的每个表或联接组合创建一组单独的视图。

I hope as I learn more about SQL, I will find a way to generalize this technique (to make it more like a script or macro) and simplify it further.

我希望随着我对SQL的了解越来越多,我将找到一种方法来推广这种技术(使其更像脚本或宏)并进一步简化它。

1 个解决方案

#1


0  

Nope, you have to specify column names. There is no syntax for dynamic field inclusion, however you could maybe work something out with information_schema (like below) to generate a query for you to use.

不,你必须指定列名。动态字段包含没有语法,但是您可以使用information_schema(如下所示)来生成查询以供您使用。

SELECT CONCAT('SELECT ', GROUP_CONCAT(column_name), ' '
,'FROM `', table_schema, '`.`', table_name, '`;'
FROM information_schema.columns 
WHERE table_schema = 'yourschemaname' 
AND table_name = 'yourtablename' 
AND data_type IN ('timestamp', 'date'[, etc....])
;

#1


0  

Nope, you have to specify column names. There is no syntax for dynamic field inclusion, however you could maybe work something out with information_schema (like below) to generate a query for you to use.

不,你必须指定列名。动态字段包含没有语法,但是您可以使用information_schema(如下所示)来生成查询以供您使用。

SELECT CONCAT('SELECT ', GROUP_CONCAT(column_name), ' '
,'FROM `', table_schema, '`.`', table_name, '`;'
FROM information_schema.columns 
WHERE table_schema = 'yourschemaname' 
AND table_name = 'yourtablename' 
AND data_type IN ('timestamp', 'date'[, etc....])
;