1 / 51

SQL Select

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.

vinny
Download Presentation

SQL Select

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Select

  2. See documentation on mysql web site(google mysql select)http://dev.mysql.com/doc/refman/5.5/en/select.html

  3. Retrieve Entire Table Retrieve the entire customer table: SELECT * FROM table_name SELECT * FROM customer;

  4. 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;

  5. 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;

  6. Distinct • DISTINCT ( col ) • Return distinct rows only: • If value appears more than once in the table, list it only once.

  7. Distinct SELECT DISTINCT ( state ) FROM customer; • Compare to SELECT state FROM customer; Note: Be careful; DISTINCT can slow down complex queries

  8. Where clause SELECT * FROM tablename WHERE condition; STRINGS • Need single quotes • Case sensitive NUMBERS • Just type value

  9. 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';

  10. Comparison operators = Equal > Greater than >= Greater than or equal <= Less than or equal < Less than <> Not equal != Not equal

  11. 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;

  12. Comparison operators Retrieve customers outside the state of California SELECT * FROM customer WHERE state != ‘CA’;

  13. IN WHERE col IN ( value1, value2, …) Data must match one value in the list Equivalent to: WHERE col = value1 OR col = value2 OR…

  14. IN example: • What customers are either in Maryland, New York, or California? SELECT * FROM customer WHERE state IN ( ‘MD’, ‘NY’, ‘CA’ );

  15. NOT IN example: • What customers are outside Maryland, New York, or California? SELECT * FROM customer WHERE state NOT IN ( ‘MD’, ‘NY’, ‘CA’ );

  16. RANGES - BETWEEN • BETWEEN x AND y Also (opposite) • NOT BETWEEN x AND y

  17. RANGES - BETWEEN • Retrieve purchase orders whose ids are between 102 and 105 SELECT * FROM order WHERE orderId BETWEEN 102 AND 105;

  18. Wildcards for conditions • Like pattern • used instead of = when you don't have exact value % match any number of chars _ (underscore) match one char.

  19. 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%’;

  20. 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_';

  21. 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’;

  22. String functions • There is a whole set of string functions (look up documentation) • lower( ), upper( ), length( ), substring( ), trim( ) replace( ), reverse( ), .. and many others

  23. String functions • How many customers have a name with 3 or fewer characters? SELECT * FROM customer WHERE LENGTH( name ) <= 3;

  24. ANDs and ORs • .. where_condition1 AND where_condition2 • .. Where_condition1 OR where_condition2

  25. ANDs and ORs • List info about sofas made of cherry SELECT * FROM product WHERE description = ‘sofa' AND finish = ‘cherry’;

  26. 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;

  27. 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;

  28. NOT Negates an expression Retrieve customers whose names do not start with X SELECT * FROM customerWHERE NOT ( name LIKE ‘X%’);

  29. Renaming a column AS newColumnName Select description, finish AS wood from product;  In resulting table, column name is wood

  30. Renaming a table AS newTableName after table name in SELECT statement Select productId, finish AS wood from product AS chairs where description = ‘chair’;

  31. Using expressions We are selecting a select expression, not necessarily a column Can use math symbols such as +, -, *, /, % in the select expressions

  32. Using expressions SELECT now( ); SELECT 3 + 4; SELECT price, price * 1.1 from product; SELECT price, price * 1.1 as ‘price + 10%’ from product;

  33. 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”

  34. 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, ..

  35. Using dates Retrieve day of the week for a date SELECT DAYNAME( ‘2013-10-23’); • Wednesday SELECT DAYOFWEEK( ‘2013-10-23’); • 4

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

  37. 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

  38. 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

  39. 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

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

  41. Using dates We can compare dates Retrieve purchase orders placed before 4/1/2013 SELECT * FROM order WHERE orderDate < ‘2013-04-01’;

  42. 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( )

  43. 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

  44. 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, …

  45. 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

  46. Formatting dates What day of the week do customers place their orders? SELECT orderId, DATE_FORMAT( orderDate, ‘%W’) FROM order;

  47. Aggregate Functions: COUNT() How many records SUM () Combined total value AVG() Average value MIN() Minimum value MAX() Maximum value

  48. Aggregate Functions: • SUM and AVG can only be used with numeric columns • The others can be used with any data type

  49. 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';

  50. 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;

More Related