Chapter 7 data filtering

Combine WHERE clause

AND operator

SELECT prod_price,prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <=10;

OR operator

SELECT prod_price,prod_name
FROM products
WHERE vend_id = 1003 OR prod_price <=10;

Calculator counts

SELECT prod_price,prod_name,vend_id
FROM products
WHERE vend_id = 1003 OR vend_id = 1002 AND prod_price <=10;

SELECT prod_price,prod_name,vend_id
FROM products
WHERE (vend_id = 1003 OR vend_id = 1002) AND prod_price <=10;

IN operator

SELECT prod_price,prod_name,vend_id
FROM products
WHERE vend_id IN (1002,1003) 
ORDER BY prod_name;

NOT operator

SELECT prod_price,prod_name,vend_id
FROM products
WHERE vend_id NOT IN (1002,1003) 
ORDER BY prod_name;

Chapter 8 filter with wildcards

LIKE operator

1. Wildcards: special characters used to match a part of a value
2. Search mode: a search term composed of literal value, wildcard character or a combination of the two

Percentage sign%: any character appears any number of times

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE 'jet%';

Underline: any single character

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';

When using wildcards, do not place them at the beginning of the search pattern if not necessary

Search with regular expressions

Using MySQL regular expressions

SELECT prod_id,prod_name
FROM products
WHERE prod_name REGEXP '1000';

(.) match any character

SELECT prod_id,prod_name
FROM products
WHERE prod_name REGEXP '.000';

OR(|) matching

SELECT prod_id,prod_name
FROM products
WHERE prod_name REGEXP '1000|2000';

[] matches one of several characters

SELECT prod_id,prod_name
FROM products
WHERE prod_name REGEXP '[123]000';

Matching range

SELECT prod_id,prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton';

Match special characters

SELECT prod_id,prod_name
FROM products
WHERE prod_name REGEXP '\\.';

Meta character	Explain
\\f	Page change
\\n	Line feed
\\r	Enter
\\t	Tabulation
\\v	Vertical tabulation

Match multiple instances

Meta character	Explain
*	0 or more matches
+	1 or more matches
?	0 or 1 matches
{n}	Specified number match
{n, }	No less than the specified number of matches
{n,m}	Specified number range match

SELECT prod_id,prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)';

Locator

Meta character	Explain
^	Beginning of text
$	End of text
[[:<;]]	Beginning of word
[[:>;]]	Word ending

SELECT prod_id,prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]';

Create calculated fields

Splicing field

Concat() splices two columns

SELECT Concat(vend_name,'(',vend_country,')')
FROM vendors
ORDER BY vend_name;

RTrim() removes extra space to the right of data

SELECT RTrim(vend_country)
FROM vendors
ORDER BY vend_name;

LTrim() removes extra space to the left of data
Trim() removes redundant spaces on both sides of the data

AS alias

Perform arithmetic calculation

SELECT 	prod_id,
				quantity,
				item_price,
				quantity*item_price AS Total price
FROM orderitems
WHERE order_num = 20005;

Chapter 11 using data processing functions

function

Upper() converts text to uppercase

SELECT	vend_name,Upper(vend_name)
FROM vendors
ORDER BY vend_name;

Left (string,) returns the character to the left of the string

SELECT	Left('1234',2)

Length() returns the length of the string

SELECT	Length('One'),Length('1'),Length('a'),Length('a One 1')

###Locate() finds the substring of a string

SELECT	Locate('12','323123'),
				Locate('1','32121'),
				Locate('a','a')