520 likes | 638 Views
SQL Select. See documentation on mysql web site (google mysql select) http://dev.mysql.com/doc/refman/5.5/en/select.html. Retrieve Entire Table. Retrieve the entire customer table: SELECT * FROM table_name SELECT * FROM customer;. To sort results.
E N D
See documentation on mysql web site(google mysql select)http://dev.mysql.com/doc/refman/5.5/en/select.html
Retrieve Entire Table Retrieve the entire customer table: SELECT * FROM table_name SELECT * FROM customer;
To sort results ORDER BY colname [DESC] [, colname [DESC] ] SELECT * FROM customer ORDER BY name; • To put in reverse order , use DESC SELECT * FROM customer ORDER BY name DESC;
Order by more than one column • Order first by state, then by name to break any tie SELECT * FROM customer ORDER BY state, name; • Same as above, except state values are in descending order, name in ascending order SELECT * FROM customer ORDER BY state DESC, name;
Distinct • DISTINCT ( col ) • Return distinct rows only: • If value appears more than once in the table, list it only once.
Distinct SELECT DISTINCT ( state ) FROM customer; • Compare to SELECT state FROM customer; Note: Be careful; DISTINCT can slow down complex queries
Where clause SELECT * FROM tablename WHERE condition; STRINGS • Need single quotes • Case sensitive NUMBERS • Just type value
Where clause Retrieve the customer with id 4 SELECT * FROM customer WHERE customerId = 4; Retrieve all customers from CA SELECT * FROM customer WHERE name = ‘CA';
Comparison operators = Equal > Greater than >= Greater than or equal <= Less than or equal < Less than <> Not equal != Not equal
Comparison operators Retrieve customers whose id is less than 5 SELECT * FROM customer WHERE customerId < 5; Retrieve customers whose id is greater than or equal 3 SELECT * FROM customer WHERE customerId >= 3;
Comparison operators Retrieve customers outside the state of California SELECT * FROM customer WHERE state != ‘CA’;
IN WHERE col IN ( value1, value2, …) Data must match one value in the list Equivalent to: WHERE col = value1 OR col = value2 OR…
IN example: • What customers are either in Maryland, New York, or California? SELECT * FROM customer WHERE state IN ( ‘MD’, ‘NY’, ‘CA’ );
NOT IN example: • What customers are outside Maryland, New York, or California? SELECT * FROM customer WHERE state NOT IN ( ‘MD’, ‘NY’, ‘CA’ );
RANGES - BETWEEN • BETWEEN x AND y Also (opposite) • NOT BETWEEN x AND y
RANGES - BETWEEN • Retrieve purchase orders whose ids are between 102 and 105 SELECT * FROM order WHERE orderId BETWEEN 102 AND 105;
Wildcards for conditions • Like pattern • used instead of = when you don't have exact value % match any number of chars _ (underscore) match one char.
Wildcards examples • Which products are made of oak? SELECT * FROM product WHERE finish LIKE ‘%oak’; • Which products are sofas, list descriptions and prices only? SELECT description, price FROM product WHERE finish LIKE ‘%sofa%’;
More wildcards • Which customer names are in a state that start with C (remember the state column has only 2 chars)? SELECT name FROM customer WHERE state LIKE ‘C_';
Case sensitivity in strings • In mysql, default is case insensitive • Can use BINARY to make it case sensitive SELECT * FROM customer WHERE BINARY state = ‘ca’; vs SELECT * FROM customer WHERE state = ‘CA’;
String functions • There is a whole set of string functions (look up documentation) • lower( ), upper( ), length( ), substring( ), trim( ) replace( ), reverse( ), .. and many others
String functions • How many customers have a name with 3 or fewer characters? SELECT * FROM customer WHERE LENGTH( name ) <= 3;
ANDs and ORs • .. where_condition1 AND where_condition2 • .. Where_condition1 OR where_condition2
ANDs and ORs • List info about sofas made of cherry SELECT * FROM product WHERE description = ‘sofa' AND finish = ‘cherry’;
ANDs and ORs • List info about products that are either sofas or are cheaper than $100 SELECT * FROM product WHERE description = ‘sofa' OR price <= 100;
AND has precedence over OR SELECT * FROM productWHERE description = ‘sofa' AND finish = ‘cherry’ OR price <= 100; Is equivalent to SELECT * FROM productWHERE (description = ‘sofa' AND finish = ‘cherry’) OR price <= 100;
NOT Negates an expression Retrieve customers whose names do not start with X SELECT * FROM customerWHERE NOT ( name LIKE ‘X%’);
Renaming a column AS newColumnName Select description, finish AS wood from product; In resulting table, column name is wood
Renaming a table AS newTableName after table name in SELECT statement Select productId, finish AS wood from product AS chairs where description = ‘chair’;
Using expressions We are selecting a select expression, not necessarily a column Can use math symbols such as +, -, *, /, % in the select expressions
Using expressions SELECT now( ); SELECT 3 + 4; SELECT price, price * 1.1 from product; SELECT price, price * 1.1 as ‘price + 10%’ from product;
Formatting numbers Use FORMAT function 1st parameter: number to be formatted 2nd parameter: number of digits after decimal point SELECT productId, FORMAT( price * 1.1, 2 ) from product; Note: rounds up if there is a “tie”
Using dates Many functions are available for the date data type: • Can format dates • Can get date info: day of the week, .. • Can add, subtract dates, ..
Using dates Retrieve day of the week for a date SELECT DAYNAME( ‘2013-10-23’); • Wednesday SELECT DAYOFWEEK( ‘2013-10-23’); • 4
Using dates Subtracting from and adding to a date DATE_SUB (or SUBDATE), DATE_ADD (or ADDDATE) functions Syntax: ADDDATE( date, INTERVAL expression unit ); ADDDATE( expression, days );
Using dates Unit could be MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR, and more SELECT DATE_ADD( ‘2013-10-23’, INTERVAL 2 MONTH ); • 2013-12-23
Using dates In the second form of ADDDATE, the 2nd parameter is a number of days SELECT ADDDATE( ‘2012-10-29’, 5 ); • 2012-11-03 Note: this form is not available for DATE_ADD
Using dates Subtracting from and adding to a date: expression can be negative SELECT DATE_SUB( ‘2013-10-23’, INTERVAL 3 DAY ); • 2013-10-20 SELECT DATE_SUB( ‘2013-10-23’, INTERVAL -4 DAY ); • 2013-10-27
Using dates Subtracting from and adding to a date SELECT DATE_ADD( ‘2013-10-23’, INTERVAL -4 YEAR ); • 2009-10-23 SELECT DATE_ADD( now( ), INTERVAL 1 DAY );
Using dates We can compare dates Retrieve purchase orders placed before 4/1/2013 SELECT * FROM order WHERE orderDate < ‘2013-04-01’;
Using dates Retrieve purchase orders placed in the last 210 days SELECT * FROM order WHERE DATE_SUB( current_date, INTERVAL 210 DAY ) <= orderDate; Note: can also use current_date( ) , curdate( ) or now( )
Using dates We can use DATEDIFF to subtract one date from another SELECT DATEDIFF( ‘2013-10-29’, ‘2013-10-24’ ); 5 SELECT DATEDIFF( ‘2013-10-29’, ‘2013-11-2’ ); -4
Formatting dates We can use DATE_FORMAT function to format a date DATE_FORMAT( date, format ) Look up documentation for date_format: Can format year (4 vs 2 digits), month (name, abbr. name, numeric), day of week (name, abbr. name, numeric), AM/PM, …
Formatting dates SELECT DATE_FORMAT( ‘2013-10-23’, ‘%W, %M %d, %Y’ ); Wednesday, October 23, 2013 SELECT DATE_FORMAT( ‘2013-10-23’, ‘%w, %m %D, %y’ ); 3, 10 23th, 13
Formatting dates What day of the week do customers place their orders? SELECT orderId, DATE_FORMAT( orderDate, ‘%W’) FROM order;
Aggregate Functions: COUNT() How many records SUM () Combined total value AVG() Average value MIN() Minimum value MAX() Maximum value
Aggregate Functions: • SUM and AVG can only be used with numeric columns • The others can be used with any data type
Aggregate Function examples: • How many customers do we have? SELECT COUNT(customerId) FROM customer; Note: different from (does not make sense) SELECT SUM(customerId) FROM customer; • How many customers are from California? SELECT COUNT(customerId) FROM customer WHERE state = ‘CA';
Aggregate Function examples: • In how many states do we have customers? • use COUNT and DISTINCT SELECT COUNT(DISTINCT state) FROM customer; Note: different from SELECT COUNT(state) FROM customer;