Steven Shatz Steven Shatz - 2 years ago 141
MySQL Question

Using the MySQL command line, how can I display only date or amount (Decimal) columns in a Select statement?

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.

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.

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?

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

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');

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

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

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.

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.

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

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);

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.

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.

Answer Source

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.

,'FROM `', table_schema, '`.`', table_name, '`;'
FROM information_schema.columns 
WHERE table_schema = 'yourschemaname' 
AND table_name = 'yourtablename' 
AND data_type IN ('timestamp', 'date'[, etc....])
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download