trdsql simple SQL 2

Search condition

Last time, we sorted columns, extracted, and sorted rows, so this time we will extract rows. To extract rows, add WHERE and write the search condition.

We will use the same example file as last time.

trdsql -ih "SELECT id, \`name\` FROM header.csv WHERE id=1"

1,Orange

This is the main feature of SQL. Just write the search condition and you can output the corresponding rows.

AND, OR

You can write complex conditions by using AND, OR, and () parentheses.

trdsql -ih "SELECT id, \`name\` FROM header.csv WHERE id='1' OR id='2'"

1,Orange
2,Melon

trdsql -ih "SELECT id, \`name\` FROM header.csv " "WHERE (id='1' OR id='2') AND \`name\`='Orange'"

1,Orange

As mentioned last time, trdsql treats the values of CSV, LTSV, and JSON as text types. Therefore, when you write the condition with “=”, you don’t have to be aware of the type so much, but when you specify the range, the result will change, so you need to CAST.

trdsql -ih "SELECT id,\`name\` FROM header.csv " "WHERE CAST(id as int)>1"

2,Melon
3,Apple

When using SELECT, you can use CAST in the column specification to specify the CAST column for the search condition or ORDER BY.

In that case, the original column name refers to the column before CAST, so use AS alias to use the column name after CAST (you can give the original name to the column name after CAST).

trdsql -ih "SELECT CAST(id AS int) AS id,\`name\` FROM header.csv WHERE id>1"

2,Melon
3,Apple

IN

IN is a convenient operator that allows you to specify multiple values in a single condition.

trdsql -ih "SELECT * FROM header.csv WHERE id IN ('1','3')"

1,Orange
3,Apple

IN can also be used to specify multiple columns at once.

trdsql -ih "SELECT * FROM header.csv WHERE 'Apple' IN (id,name)"

3,Apple