Basic CRUD operations, F
unctions, Expressions an
d Clauses
Agenda
• DDL,
• DML,
• Query and Operations,
• Clauses,
• Aggregate Functions,
• String Functions,
• GROUP BY,
• Having,
• Rank Functions
DDL
Queries in MySQL
• In relational database management systems, a query is an
y command used to retrieve data from a table.
• In Structured Query Language (SQL), queries are almost a
lways made using the SELECT statement.
• A list of commonly used MySQL queries to create databas
e, use database, create table, insert record, update recor
d, delete record, select record, truncate table
Clauses
• WHERE Clause in MySQL is a keyword used to speci
fy the exact criteria of data or rows that will be affe
cted by the specified SQL statement.
• The WHERE clause can be used with SQL statement
s like INSERT, UPDATE, SELECT, and DELETE to filter r
ecords and perform various operations on the data.
WHERE clause Syntax
SELECT * FROM tableName WHERE condition;
• "SELECT * FROM tableName" is the standard SELECT state
ment
• "WHERE" is the keyword that restricts our select query res
ult set and "condition" is the filter to be applied on the res
ults. The filter could be a range, single value or sub query.
WHERE clause combined with
- AND LOGICAL Operator
• The WHERE condition in MySQL when used together wi
th the AND logical operator, is only executed if ALL filter
criteria specified are met.
WHERE clause combined with
- IN Keyword
• The WHERE clause when used together with the N
OT IN keyword DOES NOT affects the rows whose v
alues matches the list of values provided in the NOT
IN keyword.
• The following query gives rows where membership
_number is NOT 1 , 2 or 3
Aggregate Functions
• MySQL's aggregate function is used to perform calculations
on multiple values and return the result in a single value like
the average of all values, the sum of all values, and maximu
m & minimum value among certain groups of values.
• We mostly use the aggregate functions with SELECT stateme
nts in the data query languages.
Syntax
• function_name (DISTINCT | ALL expression)
• First, we need to specify the name of the aggregate function.
• Second, we use the DISTINCT modifier when we want to calcu
late the result based on distinct values or ALL modifiers when
we calculate all values, including duplicates. The default is ALL
.
• Third, we need to specify the expression that involves column
s and arithmetic operators.
String Functions
• SQL Server has many built-in functions.
• This reference contains string, numeric, date, conversion, a
nd some advanced functions in SQL Server.
Group By
• The MYSQL GROUP BY Clause is used to collect data from
multiple records and group the result by one or more colu
mn. It is generally used in a SELECT statement.
• You can also use some aggregate functions like COUNT, SU
M, MIN, MAX, AVG etc. on the grouped column.
• expression1, expression2, ... expression_n: It specifies the
expressions that are not encapsulated within an aggregat
e function and must be included in the GROUP BY clause.
• aggregate_function: It specifies a function such as SUM, C
OUNT, MIN, MAX, or AVG etc. tables: It specifies the table
s, from where you want to retrieve the records.
• There must be at least one table listed in the FROM claus
e.
Having
• MySQL HAVING Clause is used with GROUP BY clause. It al
ways returns the rows where condition is TRUE.
• aggregate_function: It specifies any one of the aggregate
function such as SUM, COUNT, MIN, MAX, or AVG.
• expression1, expression2, ... expression_n: It specifies the
expressions that are not encapsulated within an aggregat
e function and must be included in the GROUP BY clause.
• WHERE conditions: It is optional. It specifies the condition
s for the records to be selected
• HAVING condition: It is used to restrict the groups of retu
rned rows. It shows only those groups in result set whose
conditions are
Rank Functions
• The RANK() function is a window function that assigns a ra
nk to each row in the partition of a result set.
• The rank of a row is determined by one plus the number of
ranks that come before it
• First, the PARTITION BY clause distributes the rows in the resu
lt set into partitions by one or more criteria.
• Second, the ORDER BY clause sorts the rows in each a partitio
n.
• The RANK() function is operated on the rows of each partition
and re-initialized when crossing each partition boundary.
