sql select
Download
Skip this Video
Download Presentation
SQL Select

Loading in 2 Seconds...

play fullscreen
1 / 51

SQL Select - PowerPoint PPT Presentation


  • 76 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' SQL Select' - vinny


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide2
See documentation on mysql web site(google mysql select)http://dev.mysql.com/doc/refman/5.5/en/select.html
retrieve entire table
Retrieve Entire Table

Retrieve the entire customer table:

SELECT * FROM table_name

SELECT *

FROM customer;

to sort results
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 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
  • DISTINCT ( col )
  • Return distinct rows only:
    • If value appears more than once in the table, list it only once.
distinct1
Distinct

SELECT DISTINCT ( state ) FROM customer;

  • Compare to

SELECT state

FROM customer;

Note: Be careful; DISTINCT can slow down complex queries

where clause
Where clause

SELECT * FROM tablename

WHERE condition;

STRINGS

  • Need single quotes
  • Case sensitive

NUMBERS

  • Just type value
where clause1
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
Comparison operators

= Equal

> Greater than

>= Greater than or equal

<= Less than or equal

< Less than

<> Not equal

!= Not equal

comparison operators1
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 operators2
Comparison operators

Retrieve customers outside the state of California

SELECT * FROM customer

WHERE state != ‘CA’;

slide13
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
IN example:
  • What customers are either in Maryland, New York, or California?

SELECT * FROM customer

WHERE state IN ( ‘MD’, ‘NY’, ‘CA’ );

not in example
NOT IN example:
  • What customers are outside Maryland, New York, or California?

SELECT * FROM customer

WHERE state NOT IN ( ‘MD’, ‘NY’, ‘CA’ );

ranges between
RANGES - BETWEEN
  • BETWEEN x AND y

Also (opposite)

  • NOT BETWEEN x AND y
ranges between1
RANGES - BETWEEN
  • Retrieve purchase orders whose ids are between 102 and 105

SELECT * FROM order WHERE

orderId BETWEEN 102 AND 105;

wildcards for conditions
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
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
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
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
String functions
  • There is a whole set of string functions (look up documentation)
  • lower( ), upper( ), length( ), substring( ), trim( ) replace( ), reverse( ), .. and many others
string functions1
String functions
  • How many customers have a name with 3 or fewer characters?

SELECT * FROM customer WHERE LENGTH( name ) <= 3;

ands and ors
ANDs and ORs
  • .. where_condition1 AND where_condition2
  • .. Where_condition1 OR where_condition2
ands and ors1
ANDs and ORs
  • List info about sofas made of cherry

SELECT * FROM product WHERE description = ‘sofa\' AND finish = ‘cherry’;

ands and ors2
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
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;

slide28
NOT

Negates an expression

Retrieve customers whose names do not start with X

SELECT * FROM customerWHERE NOT ( name LIKE ‘X%’);

renaming a column
Renaming a column

AS newColumnName

Select description, finish AS wood from product;

 In resulting table, column name is wood

renaming a table
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
Using expressions

We are selecting a select expression, not necessarily a column

Can use math symbols such as +, -, *, /, % in the select expressions

using expressions1
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
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
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 dates1
Using dates

Retrieve day of the week for a date

SELECT DAYNAME( ‘2013-10-23’);

  • Wednesday

SELECT DAYOFWEEK( ‘2013-10-23’);

  • 4
using dates2
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 dates3
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 dates4
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 dates5
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 dates6
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 dates7
Using dates

We can compare dates

Retrieve purchase orders placed before 4/1/2013

SELECT * FROM order WHERE orderDate < ‘2013-04-01’;

using dates8
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 dates9
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
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 dates1
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 dates2
Formatting dates

What day of the week do customers place their orders?

SELECT orderId, DATE_FORMAT( orderDate, ‘%W’) FROM order;

aggregate functions
Aggregate Functions:

COUNT() How many records

SUM () Combined total value

AVG() Average value

MIN() Minimum value

MAX() Maximum value

aggregate functions1
Aggregate Functions:
  • SUM and AVG can only be used with numeric columns
  • The others can be used with any data type
aggregate function examples
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 examples1
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 aggregate function examples
More aggregate function examples
  • What is our highest price product?

SELECT MAX (price) FROM product;

  • What is the average price of our products? SELECT AVG (price) FROM product;

SELECT AVG (price) AS AVG_PRICE FROM product;

ad