Chapter 9: Structured Query Language(SQL)
SOME MySQL SQL ELEMENT
Literals – It generally, refer to a fixed data value which may be character type or
numeric literal.
Datatypes – Are means to identify the type of data and associated operations for
handling it.
Nulls – If a column in a row has no value, then columnis said to be null, or to
contain a null.
Any arithmetic expression containing a null, always evaluates to null.
Comments- A comment is a text that is not executed; it is only for documentation
purpose.
SQL Command Syntax
The SQL provides a predefined set of commands that help us work on relational
database.
In commands different terminology used are keywords, commands, or
statements.
Clauses – Commands consist of one or more logically distinct parts called
clauses.
“FROM sales” AND “WHERE value=1500.00” here from and where are clauses.
Commands in SQL are not case –sensitive.
Making Simple Queries
To fully use the power of an RDBMS, we need to communicate with it.A powerful
way of communicating with it is making queries.
We can see desired data in desired format with help of SELECT command.
Other commands are :-
Distinct command
All keyword
Show tables- Viewing Structure of Table
Simple calculations( eg select 1+6)
Scalar Expressions with Selected Fields
Using Column Aliases
Handling nulls
Putting Text in the Query Output
Selecting Specific Rows-Where clause
Relational Operators
Logical Operators
Condition based on a Range (eg BETWEEN)
Condition Based on a List
Condition Based on Pattern Matches (%, _, LIKE, NOT LIKE )
Searching for NULL
Operator Precedence
Sorting Results—ORDER BY clause
Sorting by Column Alias
MySQL functions available in SQL
Function- A function is a special type of predefined command set that performs
some operation and returns a single value.
Functions operate on zero, one, two or more values that are provided to them.
The values that are provided to functions are called parameters or arguments.
STRING FUNCTION
CHAR( )
CONCAT ( )
LOWER/LCASE( )
SUBSTR( )
UPPER/UCASE( )
LTRIM( )
RTRIM( )
TRIM( )
INSTR( )
LENGTH( )
LEFT( )
RIGHT( )
MID( )
NUMERIC FUNCTIONS
MOD()
POWER/POW
ROUND()
SIGN()
DATE/TIME FUNCTIONS
CURDATE()/CURRENT _DATE()
DATE()
MONTH()
YEAR()
DAYNAME()
DAYOFMONTH( )
DAYOFWEEK()
DAYOFYEAR()
NOW()
SYSDATE()
DATABASE IN MySQL
Creating Database
Opening Database
Removing Database
CREATING TABLES
Syntax
Data Integrity Through Constraints
Different Constraints
Unique constraint
Primary key constraint
Default constraint
Check constraint
Foreign key constraint
Changing Data with DML Commands
INSERT INTO Command , Inserting Null values, Inserting Dates, Inserting Data from
Another Table.
Modifying Data with UPDATE Command
Deleting Data with DELETE Command
More DDL Commands
ALTER TABLE Command
Modifying Column Definitions
Order of Column
Changing a Column Name
Removing Table Components
DROP TABLE Command
