Cpsc 3220 the language of sql
Download
1 / 20

CpSc 3220 The Language of SQL - PowerPoint PPT Presentation


  • 74 Views
  • Uploaded on

CpSc 3220 The Language of SQL. Chapters 7-9. The WHERE Clause. Determines which rows of table are to be selected. Syntax of the SELECT statement. SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN]

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 ' CpSc 3220 The Language of SQL' - vashon


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
Cpsc 3220 the language of sql

CpSc 3220The Language of SQL

Chapters 7-9


The where clause
The WHERE Clause

  • Determines which rows of table are to be selected


Syntax of the select statement
Syntax of the SELECT statement

SELECT

[ALL | DISTINCT | DISTINCTROW ]

[HIGH_PRIORITY]

[STRAIGHT_JOIN]

[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

select_expr [, select_expr ...]

[FROM table_references

[WHERE where_condition]

[GROUP BY {col_name | expr | position}

[ASC | DESC], ... [WITH ROLLUP]]

[HAVING where_condition]

[ORDER BY {col_name | expr | position}

[ASC | DESC], ...]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

[PROCEDURE procedure_name(argument_list)]

[INTO OUTFILE 'file_name'

[CHARACTER SET charset_name]

export_options

| INTO DUMPFILE 'file_name'

| INTO var_name [, var_name]]

[FOR UPDATE | LOCK IN SHARE MODE]]


Row based logic
Row-based Logic

  • The where_condition term in the WHERE clause is applied to each row and only those for which the condition is true are selected

  • The where_conditionis very similar to conditional expressions in other languages such as Java and PHP

  • It can be comprised of column variables, literals and operators


Where clause comparison operators
WHERE Clause Comparison Operators

= equals

<> does not equal

!= does not equal

> is greater than

< is less than

>= is greater than or equal to

<= is less than or equal to


Limiting rows
Limiting Rows

  • In MySQL the LIMIT clause can be used to return only a specified number of rows

  • LIMIT n returns the ‘first’ n rows

  • If used after an ORDER clause it will return the first n rows of the sorted table

  • Other DBMSs handle this process differently


Boolean logic
Boolean Logic

  • Boolean operators can be combined with conditional operators to express more complex logical conditions


The and operator
The AND Operator

  • Returns true if both operands are true

    WHERE age > 21 ANDclass = ‘FRESHMAN’


The or operator
The OR Operator

  • Returns true if either or both operands are true

  • Returns false otherwise

    WHERE age > 21 ORclass = ‘FRESHMAN’


The xor operator
The XOR Operator

  • Returns true if one but not both operands are true

  • Returns false otherwise

    WHERE age > 21 XOR class = ‘FRESHMAN’

  • Available in MySQL but not all other DBMSs


The not operator
The NOT Operator

  • A unary operator

  • Returns true if operand is false

  • Returns false if operand is ture


Precedence
Precedence

  • AND takes precedence over OR and XOR

  • NOT takes precedence over AND

  • All boolean operators take precedence over conditional operators

  • Parentheses can be used to override precedence


The between operator
The BETWEEN Operator

  • Example:

    WHERE hours BETWEEN 30 AND 59


The in operator
The IN Operator

  • Examples

    WHERE age IN (20,24,45)

    WHERE color IN (‘Red’,’White’,’Blue’)


Boolean logic and null values
Boolean Logic and NULL Values

  • The conditional operators do not work on NULL values

  • The ISNULL function can be used

  • ISNULL(operand,result) returns result if operand is NULL, returns operand otherwise

  • Example:

    ISNULL(Weight,0)


Inexact matches
Inexact Matches

  • SQL has several ways of handling inexact matches

    • LIKE

    • REGEXP (supported by MySQL)

    • SOUNDEX

    • DIFFERENCE (not supported by MySQL)


Pattern matches using like
Pattern Matches Using LIKE

  • WHERE operand LIKE ‘pattern’

  • pattern has two special symbols, % and _

  • % matches any string of any length

  • _ matches a single character


Example matches using like
Example Matches Using LIKE

WHERE name LIKE ‘%ie’

finds all names ending in ie

WHERE name LIKE ‘TH%’

finds all names starting with th

WHERE name LIKE ‘%ing%’

finds all name containing ing

Matches are case-insensitive


Pattern matches using regexp
Pattern Matches Using REGEXP

  • MySQL supports a REGEXP operator that functions much like the REGEXP in PHP

  • WHERE name REGEXP regexpPatternuses the regexpPattern to match the name operand

  • REGEXP is much the same as the regular expression function in PHP


Matches by sound
Matches by Sound

  • There is a SOUNDEX function which converts a string into a code that reflects its ‘sound value’ and this can be used to find two strings that have the same sound code.


ad