Transcript Open Source Database Systmes

Open Source Database Systems
Relational Databases
• Advantages over using flat files
– RDB can provide faster access to data
– RDB can be easily queried to extract sets of data
that fits certain criteria
– RDB have built-in mechanism for dealing with
concurrent access (so you don’t have to worry
about it)
– RDB have built-in privilege system.
Relational Databases
• The RDB engine that we will use in the module
is MySQL
• Before getting into the MySQL specifics, we
need to cover:
– RDB Concepts and Terminology
– Web Database Design
RDB Concepts and Terminology
Relational DB Concepts
• Relational Databases (RDB) are the most
common type of databases, they depend on
on a sound theoretical basis in relational
algebra.
• However, we don’t need to understand
relational theory to use them.
• However, some basic concepts need to be
understood
Tables
• Relational databases are made up of relations,
more commonly called tables
• A table is exactly that: A table of data just like
the one produced in a
CUSTOMERS
CustomerID
Name
Address
1
Bill Jobs
2011 Apple Way,
SF, 10064 CA
2
Steve Gates
1 Microsoft Drive,
SD, 98711 CA
Columns
• Columns in the table have unique names and
contain different data (each column has an
associated data type) – Columns are called
FIELDS
– On the previous example, the first column
(customerID) is an integer and the other two
Fields contain text strings
Rows & Values
• Each row in the table represents all the data
relating to an individual (in this example, a
customer) Rows are also called RECORDS or
TUPLES
• Each row consists of a set of individual values
that correspond to columns. Each value musst
have the data type specified by its column.
Keys
• Usually, in a table, we need a way to uniquely
identify each individual row.
• If the table is one like the example, using names
might not be the best way of doing this. (think of
a 3rd entry: Peter Smith with address 15 Main
Street).
• What are the chances of finding another
customer named Peter Smith?
• Peter this peter might be the only one living at
the given address, but then we are using 2 fields
Keys
• The way around this is to create an additional
field (customerID) in this example that
contains this unique identifier to the subject
• This identifier field on the table is called the
key or the PRIMARY KEY.
• A Key can also be made of more than one
field.
Keys
• Databases usually consist of multiple tables
and use a keys as a reference from one table
to another.
• For example, the following table represents
orders placed by the customers:
ORDERS
orderID
customerID
Amount
Date
1
3
27000
10 Aug 2011
2
3
45000
22 Aug 2011
3
2
14270
1 Oct 2011
CUSTOMERS
CustomerID
Name
Address
1
Bill Jobs
2011 Apple Way, SF, 10064 CA
2
Steve Gates
1 Microsoft Drive, SD, 98711 CA
3
Peter Smith
15 Main Street
ORDERS
orderID
customerID
Amount
Date
1
3
27000
10 Aug 2011
2
3
45000
22 Aug 2011
3
2
14270
1 Oct 2011
Schemas
• The complete set of table designs for a
database is called the database schema.
• A schema shows tables along with their
columns, data types of the column and
indicate the primary key (PK) of each table
(and any foreign key)
• Keys on the schema are recognised because
they are underlined
Database Schema
• Schema Example
Primary Key
Customers (customerID, Name, Address)
Orders (orderID,customerID*, Amount, Date)
Primary Key
Foreign Key
Note, the * besides customerID represents a foreign key
How to Design your Web
Database
Wed DB Design
• Knowing when you need a new table and
what the key should be can be something of
an art.
• Most of the time, a few design principles can
be followed
Principles
1. Think about the real world objects you are
modelling.
– Rule of Thumb: Each class of real world objects
modelled will need its own table.
Principles
2. Avoid storing redundant data
– On the working example, the customer
information could be stored in the orders table,
but now imagine a more complex customer
information table with 5 fields and then the
order table with another 2 fields
– This “super table” will not only be huge but will
also contain redundant data which is a waste of
space (imagine Peter Smith doing weekly orders
and you having to store all his details every time)
Principles
– This kind of super tables can also lead to what is
known as update anomalies (situations where we
change the database and end up with inconsistent
data)
– There are 3 kinds of update anomalies that need to
be avoided:
•
•
•
Modification (i.e. a customer moves and we need to
update the address fields…)
Insertion (i.e. a new customer is inserted several times and
the address needs to be entered correctly each time)
Deletion (occurs when deleting rows from the database;
imagine we delete the fulfilled orders from the database,
when all the orders for a single customer have been
fulfilled, we will no longer have his address on file)
Principles
3. Use Atomic Colum Values
– In each attribute, in each row, store only ONE thing
4. Choose Sensible KEYS
– Make sure that the keys you choose are unique
5. Thinks about the questions you want to ask the
database
– Make sure that the database contains all the data
required, and the appropriate links exists between
tables to answer the questions you have
Principles
6. Avoid Designs with Many Empty Attributes
– Having many NULL values in the database is a
bad idea, it wastes storage space and causes
problems when working out totals and other
functions on numerical columns.
– When a user sees a null in a database, they don’t
know if it’s because this attribute is irrelevant,
whether there is a mistake in the database, or
whether the data just has not been entered yet.
MySQL Databases
SQL Language Tutorial
Web Database
Architecture
server system
web client
(e.g browser)
HTTP request
web server
PHP
Preprocessor
HTTP response
Database
23
Web Database
Architecture
• Typical web database transaction stages:
– HTTP request issued from client
– Web Server receives request for .php script and passes it
for processing
– PHP engine begins parsing the script. The script includes
a command to connect to the database and execute a
query. PHP opens a connection to the MySQL server
– MySQL server receives the database query, processes it
and sends the result back to the PHP Engine
– PHP Engine finishes running the script – this usually
involves the formatting of the results in HTML. Returns
resulting HTML to server
– Server passes the HTML back to the browser.
24
Web Database
Architecture
• Process is basically same regardless of scripting
engine or database server
• Often Web Server, script engine and database server
all run on the same machine
• As your application grows in size, you may want to
separate your PHP application into tiers – a database
layer (deals exclusively wit MySQL), a business logic
layer (core of application), and a presentation layer
(manages the HTML output).
25
Managing access to
the Database
• Access to the database can be provided
by using the root administrator level or by
creating a user for this database
• For testing purposes, admin access is
enough
• For security reasons, deploying using
admin level access is NOT recommended.
• MySQL provides different security levels
for users, called privileges
26
MySQL Privilege
System
• MySQL supports a sophisticated privilege
system
• Principle of least privilege
– A user (or process) should have the lowest level
of privilege required to perform his assigned task.
– For example, to run queries, a user does not
need all privileges.
• Three basic types of privileges
– Regular Users
– Administrators
– Special
27
MySQL Privilege
System - USERS
28
Privilege
Applies to
Description
SELECT
Tables, columns
Allows users to select rows from tables
INSERT
Tables, columns
Allows users to insert new rows into tables
UPDATE
Tables, columns
Allows users to modify values in existing table
rows
DELETE
Tables
Allows users to delete existing table rows
INDEX
Tables
Allows users to create and drop indexes on
particular tables
ALTER
Tables
Allows users to alter the structure of existing
tables
CREATE
Databases, tables
Allows users to create new databases or tables
DROP
Databases, tables
Allows users to drop (delete) databases or
tables.
MySQL Privilege System ADMINISTRATORS
Privilege
Description
CREATE TEMPORARY TABLES
Allows an administrator to use the keyword TEMPORARY in a CREATE TABLE
statement
FILE
Allows data to be read into tables from files and vice-versa
LOCK TABLES
Allows the explicit use of a LOCK TABLES statement
PROCESS
Allows an administrator to view server processes belonging to all user
RELOAD
Allows an administrator to reload grant tables and flush privileges, hosts, logs and
tables
REPLICATION CLIENT
Allows use of Show Status on replication masters and slaves
REPLICATION SLAVE
Allows replication slave servers to connect to the master server.
SHOW DATABASES
Allows a list of all databases to be seen with a SHOW DATABASE statement. Without
this, users see only databases to which they have other privileges
SHUTDOWN
Allows an administrator to shut down the MySQL server
SUPER
Allows an administrator to kill threads belonging to any user
29
MySQL Privilege
System – SPECIAL
Privilege
Description
ALL
Grants all privileges listed in previous tables
USAGE
Grants no privileges. This privilege creates a user and allows him to log on,
but it doesn’t allow him to do anything. Usually you will add more privileges
later.
30
MySQL Database Manipulation
Strings
MySQL Databases
• Most commonly used database program for
developing database-driven web sites with PHP
• MySQL is open source and runs on a majority of
operating systems (UNIX, Linux, Mac Os and
Windows)
• Although PHP can be used with any database
through its set of ODBC functions, it comes
loaded with MySQL specific functions which
makes for a tight integration between PHP and
MySQL
SQL Command/Queries
• SQL is a computer language, and like
languages in general, SQL has its rules,
grammar, and a set of special or reserved
words.
• Different variants of the language have
evolved over the years because different
vendors offer additional features to
manipulate data in the name of competition
SQL Command/Queries
• SQL has many commands, but they can be
divided in two major categories:
– The Commands to Manipulate the Tables in the
database
– The Commands to Manipulate the Database itself
• There are many excellent tutorials on the web
that cover all the SQL commands and how to
use them:
• http://www.w3schools.com/sql/default.asp
English Like Grammar
• An SQL statement makes a “REQUEST” or “QUERIES”
the database in the form of a statement
• The first word is an English verb, an action word called
a command such as (show, use, select, drop, etc.)
• The command is followed by a list of noun-like words,
such as show database, use database, or create
database.
• The statement might contain prepositions such as in or
from; for example:
– show tables in database
– Select phones from customer_table
English Like Grammar
• The language also allows for conditional clauses to
refine the queries such as:
– select companyname from suppliers where suppllierid > 20
• When listing multiple items in a query, the items are
separated by commas
– select companyname, phone, address from suppliers
• Semicolon (;) is the standard way to terminate each
query statement. Some database systems do not
require it, but MySQL does (except for the USE and
QUIT command).
SQL Reserved Words
ALTER
CROSS JOIN
FULL JOIN
JOIN
ON
SELECT
AND
DELETE
GROUP BY
LEFT JOIN
OR
SET
AS
DROP
INSERT
LIKE
ORDER BY
UPDATE
CREATE
FROM
INTO
LIMIT
RIGHT JOIN
WHERE
Case Use
• Database and table names are case sensitive on UNIX but not in
WINDOWS.
• A convention is to always use lowercase names for databases and
their tables
• SQL commands are not case sensitive, but by convention SQL
commands are CAPITALIZED for clarity while only the first letter of
the field, table, and database names is capitalized
– SELECT * FROM Persons WHERE FirstName=‘John’;
• If Performing pattern matching with the LIKE and NOT LIKE
commands, the the pattern being searched for is case sensitive
when using MySQL
Useful SQL Commands
• SHOW DATABASES: Used to see what
databases are available on your database
server
SHOW databases;
• USE: The USE command makes the specified
database your default database
USE database_name;
Useful SQL Commands
• SHOW TABLES IN: Displays all the tables within
a database
SHOW TABLES IN database;
• SHOW/DESCRIBE: Either of these commands is
used to see what type of data can be assigned
to a table. The DESCRIBE command is specific
to MySQL databases
DESCRIBE table_name;
SQL Data Manipulation
Language (DML)
• SQL is a non procedural language providing
syntax for extracting data, including a sytax to
update, insert, and delete records.
• The Query and Update commands together
form the Data Manipulation (DML) part of SQL
– SELECT
– UPDATE
– DELETE
– INSERT INTO
SQL-DML
SELECT
• SELECT: This command is mandatory when
performing a query; it is used to retrieve data
from a table based on some criteria
• It specifies a coma separated list of fields to be
retrieved, and the FROM clause specifies the
table(s) to be accessed.
• The results are stored in a result table known as
the result-set.
• The * symbol can be used to represent all of the
fields
SQL-DML
SELECT
Format:
SELECT column_name(s) FROM table_name
Example:
SELECT LastName, FirstName, Address FROM Students;
To select specified columns, the SELECT command
is followed by a comma separated list of fields to
be selected from the table
SQL-DML
SELECT
• SELECT DISTINC: this keyword is used to return
only distinct (unique) values from the table.
• If there are multiple values of a specified field,
the distinct result-set will display only one.
Format:
SELECT DISTINCT column_name(s) FROM table_name
Example:
SELECT DISTINCT ShipName FROM Orders;
SQL-DML
SELECT
• LIMIT(X): this keyword specifies the number of
rows to be returned from the beginning of the
result-set. X specifies the rows returned
Format:
SELECT column_name(s) FROM table_name LIMIT X;
Example:
SELECT ShipName FROM Orders LIMIT 10;
SQL-DML
SELECT
• WHERE Clause: It is used to select a field when
a certain criteria set of conditions are desired
• The WHERE Clause is optional
• To create the conditions (called selection
criteria) SQL provides a set of operators to
further qualify what criteria should be
specified
SQL-DML
Where Operators
Operator
Description
Example
=
Equal to
WHERE country = ‘ireland’
<>, !=
Not equal to
WHERE country != ‘USA’
>
Greater than
WHERE salary > 28000
<
Less than
WHERE age < 35
>=, <=
Greater/Less Than or Equal
WHERE cost >=1200
IS [NOT] NULL
Is NULL (vo value) or Not NULL
WHERE birth = NULL
BETWEEN
Between an inclusive range
WHERE last_name BETWEEN ‘Doherty’ AND ‘McDAID’
LIKE
Search for a value like a pattern
WHERE name LIKE ‘D%’
NOT LIKE
Search for a value not like a pattern
WHERE country NOT LIKE ‘Sw%’
! , NOT
Logical not for negation
WHERE age ! 10;
||, OR
Logical OR
WHERE order_number > 10 || part_number = 80
&&, AND
Logical AND
WHERE age>12 && age < 21
XOR
Exclusive OR
WHERE status XOR
SQL-DML
• Using Quotes: Quotes are always an issue in
programming languages. (single quotes?,
double quotes?, when?)
• SQL uses single quotes around text values
(MySQL also accepts double quotes)
• Numeric Values should not be enclosed in
quotes.
SQL-DML
• Comparing Strings: When comparing strings
using =, the string must be exactly as typed for
the condition to be true – this include length
and type of characters.
• NULL: Null means that there is not a value in
the field, or it is unknown, but does not mean
a value of zero.
DML-SQL
• LIKE – NOT LIKE: The pattern matching operator
can be used as a condition in the WHERE clause,
allowing the selection of rows that are ‘like’ or
match a pattern
• A percent sign (%) can be used as a wildcard to
match any possible character that might appear
before and/or after the character(s) specified.
• A _ is used to match a single character.
• The LIKE/NOT LIKE condition can be used in any
valid SQL statement, including SELECT, INSERT,
UPDATE or DELETE.
DML-SQL
• Examples of the wildcard % uses:
• SELECT CompanyName, Country FROM Customers WHERE
country LIKE ‘SW%’;
– Returns all the customers and countries in which the country starts
with “Sw” i.e. Sweden, Switzerland
• SELECT City, Country FROM suppliers WHERE City LIKE ‘%o’;
– Returns all cities and countries where the % matches any city that
ends with a letter o.
• SELECT CompanyName FROM customers WHERE
CompanyName LIKE ‘%Super%’
– Returns all company names where the % matches any company name
that contains the pattern “Super”
DML-SQL
• Examples of the wildcard _ uses:
• SELECT Extension, Firstname FROM Employees WHERE
extension LIKE ‘4_ _’;
– Returns all extensions and first names where the exetension has three
characters and the first character is a 4.
DML-SQL
• ORDER BY: Used to sort the output of a query
in either ascending (ASC, the default) or
descending (DESC) order where the values
being sorted are either strings or numbers
Format:
SELECT column_name(s) FROM table_name [WHERE condition] ORDER BY
column [ASC, DESC]
Example:
SELECT Company,Ordernumber FROM Orders ORDER BY Company;
SQL-DML
INSERT
• The INSERT statement is used ot insert new
rows into a table.
• After the VALUES keyword, a commaseparated list of column names follows
Format:
INSERT INTO table_name VALUES (value1, value2, … )
INSERT INTO table_name (column1, column2,…) VALUES (value1, value2, …
)
Example:
INSERT INTO Shippers (CompanyName, Phone) VALUES (‘FEDEX’,’416555-1221’);
SQL-DML
INSERT
• Usually, the tables have a primary key column
that is usually set to auto-increment; when
this is the case, the id of the table is created
by the database engine automatically
• Letting the database increment the PRIMARY
KEY ensures that the value is always unique.
SQL-DML
UPDATE
• The UPDATE statement is used to modify data in a
table.
• The UPDATE command is followed by the name
of the table where the data will be changed,
followed by the SET statement to inidcate what
field will be changed, and then the new value
that will be assigned to the field
• The WHERE clause further qualifies what data is
to be modified, thereby limiting the scope of the
UPDATE
SQL-DML
UPDATE
Format:
UPDATE table_name
SET column_name = new value
WHERE column_name = some_value;
Example:
UPDATE orders SET ShipCountry=‘Spain’ WHERE CustomerId = ‘whitc’;
SQL-DML
DELETE
• The DELETE statement is used to delete rows in a
table and returns the number of rows that were
deleted.
• DELETE uses the FROM clause to specify the
name of the table that contains the data you
want to delete
• The WHERE clause specifies the criteria to
identify what data should be removed.
BE CAREFUL: Without the WHERE clause ALL ROWS
are DELETED
SQL-DML
DELETE
• If the ORDER BY clause is specified, the rows
are deleted in the order that is specified.
• The LIMIT clause places a limit on the number
of rows that can be deleted.
Format:
DELETE FROM table_name
WHERE column_name = some_value;
Example:
DELETE FROM orders WHERE ShipCountry = ‘Greenland’;
Database Creation using SQL
Data Definition Language
Data Definition Language
• The DDL part of SQL permits database objects to
be created or destroyed
• Indexes (keys) can be defined
• Links between table can be specified
• Constraints between database tables can be
imposed
• Often decisions to create and remove databases
are handled by a database administrator and
having permission to create and drop tables
depends on what access rights are granted.
Data Definition Language
• The most important Data Definition Statements
in SQL are:
–
–
–
–
–
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
DROP INDEX
• DML and DDL can be executed on the MySQL
command line or in the MySQL tab of
PHPMyAdmin
MySQL Command Line
• Windows
1.
2.
3.
Start WAMP Server
On the command prompt browse to find the mysql.exe file (usually
located in c:\wamp\bin\mysql\mysqlX.X.X\bin\) – (X.X.X=5.5.8)
At the end of the command line add the following:
..\mysql.exe --user=student --password=student
If it is your own laptop, then
user=root -- password=
• MAC
1.
2.
3.
Start MAMP and Start the Servers
Go to Terminal
Type in the following command:
/Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot
DDL
DATABASES on SERVER
• Once on the MySQL
command line the
SHOW DATABASES
command will show you
the existing databases
on the server:
• mysql> show databases;
DDL
CHOOSING a DATABASE
• In order to select a
database from the list
to use it, you will need
the USE command:
• mysql> use test;
DDL
Show & Describe
• To see what tables are
in the database, use the
SHOW TABLES
command:
• mysql> show tables;
• To display the contents
of a table, you can use
the SELECT command.
DDL
Show & Describe
• To see what type of
data can be assigned to
a table, use the
DESCRIBE command
(specific to MySQL) or
the SHOW FIELDS IN
command (standard
SQL)
• mysql> describe
table_name;
DDL
Creating a Database
• To create a new database, use the CREATE
DATABASE command:
• CREATE DATABASE database_name;
DDL
Creating a Database
• mysql>CREATE DATABASE gallerydb;
• mysql>USE DATABASE gallerydb;
Art Gallery Database
Creating Tables
• In order to create the tables, the database should
be first properly designed:
• For each field on each table you should know
what type of data is it going to store
• After that is done, then the required datatype
should be selected
Note: it is a waste of space (memory) to, for
example, declare a field as integer when you know
beforehand that it will not store a number bigger
that 1000.
SQL Data Types
Numbers
72
Type
Range
Storage
(Bytes)
Description
INTEGER
-231..231-1 / 0.. 232-1
4
Whole Number
INT UNSIGNED
0..232-1
4
Non Negative Whole
Number
TINYINT
-127..128 / 0..255
1
Very small integers
SMALLINT
-32768..32767
/0..65535
2
Small Integers
MEDIUMINT
-8388608..8388607
/ 0..16777215
3
Medium Sized Integers
BIGINT
-263..263-1 / 0..264-1
8
Big Integers
SQL Data Types
Numbers
Type
Range
Storage
(Bytes)
Description
FLOAT (precision)
Depends on precision
Varies
Can be used to specify single or double
precision floating point numbers
FLOAT [(M,D)]
±1.175494351E-38 ..
±3.402823466E+38
4
Single precision floating point number.
These numbers are equivalent to
FLOAT(4) but with a specified display
width(M) and number of decimal
places(D).
DOUBLE[(M,D)]
±1.7E+308 .. ±2.2E-308
8
Double precision floating point number.
These numbers are equivalent to
FLOAT(8) but with a specified display
width (M) and number of decimal
places(D).
DECIMAL[(M,D)]
Varies
M+2
Floating point stored as a CHAR
NUMERIC / DEC / FIXED
As Above
73
Synonym for DECIMAL
SQL Data Types
STRINGS
74
Type
Range
Description
CHAR (M)
0 – 255
Characters
Fixed-length string of
length M, where M is
between 0 and 255.
Using the BINARY
keyword specifies that
the data should be
considered
casesensitive.
VARCHAR (M)
1 - 255
Same as above, except
they are variable length.
SQL Data Types
STRINGS
75
Type
Range
Description
BLOB
65535 characters
A normal sized Binary
Large Object (BLOB) (Case
sensitive)
TEXT
65535 characters
Normal Sized TEXT object.
(Case insensitive)
MySQL Data Types –
DATE and TIME
Type
Range
Description
DATE
1000-01-01 .. 9999-12-31
A date. Will be displayed as YYYY-MM-DD
TIME
-838:59:59 .. 838:59:59
A time. Will be displayed as HH:MM:SS. Note
that the range is much wider than you probably
will ever want to use.
DATETIME
1000-01-01 00:00:00 ..
9999-12-31 23:59:59
A date and time. Will be displayed as YYYYMM-DD HH:MM:SS
TIMESTAMP (M)
1970-01-01 00:00:00 ..
Sometime in 2037
A timestamp useful for transaction reporting.
The display format depends on the value of M.
The top range depends on the limit on Unix
YEAR[(2|4)]
70 – 69 (1970 – 2069)
1901 - 2155
A year. You can specify two or four digits
format. Each has a different range.
76
SQL Data Types
Timestamp Examples
77
Type
Display
TIMESTAMP
YYYYMMDDHHMMSS
TIMESTAMP(14)
YYYYMMDDHHMMSS
TIMESTAMP(12)
YYMMDDHHMMSS
TIMESTAMP(10)
YYMMDDHHMM
TIMESTAMP(8)
YYYYMMDD
TIMESTAMP(6)
YYMMDD
DML-DDL-Example
• We are going to create a database to store pets
information and on it we are going to create a table
called dog
PETS
dog(name,owner,breed,sex,birth,death)
• Note: when designing your database, be mindful of all
the already learned database design techniques like
normalization.
PET DB
Dog Table
• SQL Instructions:
mysql>create database pets;
mysql>use pets;
mysql>create table dog (
-> name varchar(20),
-> owner varchar(20),
-> breed varchar(20),
-> sex char(1),
-> birth date,
-> death date);
mysql>describe dog;
Altering a Table
Adding a Primary Key
• On the previous example, we forgot to add a
primary key field; we are going to correct that
example using the ALTER TABLE command:
• When altering a table, we redefine its
structure by adding or dropping columns,
keys, indexes and tables.
• The Alter command can also be used to
change column names, types and the table
name.
Altering a Table
Adding a Primary Key
• First, we need to add a new column to serve as the primary key; we
will call it pet_id:
mysql>alter table dog add pet_id int(11) first;
• Now we need to modify this new column to make it the primary key
– primary key fields should not be null and should have an
auto_increment property set:
mysql>alter table dog modify column pet_id int(11)
-->not null auto_increment primary key;
• The following command accomplishes the same task in a single line:
mysql>alter table dog add pet_id int(11) not null auto_increment first,
-->add primary key(pet_id);
Adding PK
Dropping Tables
Dropping Databases
• To delete a table use the DROP command:
mysql>drop table dog;
• To delete a database use the DROP command:
mysql>drop database pets;
SQL Functions
• The following functions are used to alter or
format the output of a SQL query. Functions
are provided for strings, numbers, dates,
server and information, and so on. They
return a result-set.
• When using SELECT with a function, the
function, as it was called, is displayed as the
name of the column in the result-set
Numeric Functions
String Functions
Date & Time Functions
Function
Example
NOW()
select NOW()  2012-01-17 15:58:45
CURDATE()
select CURDATE()  2012-01-17
CURTIME()
select CURTIME()  15:58:45
DAYOFYEAR(date)
select DAYOFYEAR(‘2006-12-15’)349
DAYOFMONTH(date)
select DAYOFMONT(‘2012-01-19’) 19
DAYOFWEEK(date)
select DAYOFWEEK(‘2012-01-19’) 5
(Thursday); Sunday is 1
WEEKDAY(date)
select WEEKDAY(‘2012-01-19’) 3
(week starts at 0 on Monday)
MONTHNAME(date)
select MONTHNAME(‘2012-01-19’)  January
DAYNAME(date)
select DAYNAME(‘2012-01-19’)Thursday
YEAR(date)
select YEAR(‘2012-01-19’)2012
QUARTER(date)
select QUARTER(‘2012-01-19)1