Sql select
Download
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

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


Sql select
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;


Sql select
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;