SQL Queries

NOTE: Syntax may be slightly different for each database.

Queries for table values

List values for a specific column

select _COLUMN_NAME_ from _DATABASE_NAME_._TABLE_NAME_;

List values for a range of rows

select _COLUMN_NAMES_ from _DATABASE_NAME_._TABLE_NAME_ limit _START_, _NUM_ROWS_;

NOTE: The first record in the results will be one row after the START value. If limit 20, 5, it will list rows 21-25.

Select and Sort

sort by id for subset

select _COLUMN_NAMES_ from _TABLE_NAME_ where _WHERE_CLAUSE_ order by id;

NOTE: where clause has to come before sorting

sort by id and list last N rows

select _COLUMN_NAMES_ from _TABLE_NAME_ order by id desc limit _NUM_ROWS_;

NOTE: limit has to come after sorting

Select with WHERE filter

Filter with wildcard using LIKE

% is wildcard. To force results to start with the query string, eliminate the first %

select _COLUMN_NAMES_ from _TABLE_NAME_ where _COLUMN_NAME_ like '%_QUERY_STRING_%';

Get all that don't start with X using <> operator

select id, COL2 from users where substr(COL2,1,5) <> 'guest';

Get all that have one of a set of values using IN

select id, COL2 from users where COL2 IN ('value1','value2','value3');

Select and Where for JSON Columns

Reference: https://ubiq.co/database-blog/how-to-query-json-column-in-mysql/

Select value from json blob...

select json_column_name->'$.jsonField' limit 1;

Apply where based on value in json blob...

select id where json_column_name->'$.jsonField'="jsonFieldValue" limit 1;

Example:

select data->'$.firstName' as FirstName, data->'$.lastName' as LastName from customers where data->'$.state'="GA";

Counting results

Count all matching the query

select count(id) from TABLE_NAME;

Count distinct values

select count(distinct COLUMN_NAME) from TABLE_NAME;

Modifying values in tables

Add a record

insert into TABLE_NAME (id, COL2, COL3) values(214,2,'three');

Edit a record

replace entire record

replace into TABLE_NAME values(54,2,'new three');

NOTE: The first value is the record id, which limits the update to that specific record.

update value in column

update TABLE_NAME set COL_NAME = 'new three' where id = 54;

NOTE: where id = 54 limits the update to specific record.

search and replace part of a value in a record's column

update TABLE_NAME set COLUMN_NAME = replace(COLUMN_NAME,'OLD_SUBSTRING', 'NEW_SUBSTRING') where id = 54;