Introduction:
MySQL is the most popular
open source SQL database management system is developed, distributed, and
supported by MySQL AB. MySQL AB is a commercial company, founded by the MySQL
developers.
A database is a collection of
data that is organized so that its contents can be easily accessed, managed and
updated. MySQL is a data storage area. In this storage area, there are small
sections called Tables.
A Relational Database
Management System (RDBMS) may be a DBMS in which data is stored in the form of
tables and the relationship among the data is also stored in the form of
tables.
The data in MySQL is stored in database
objects called tables. A table is a collection of related data entries and it
consists of columns and rows. The MySQL database has become the world's most
popular
open source database
because of its consistent fast performance, high reliability and ease of use.
Advantages:
o
MySQL is Cross-Platform.
o
MySQL is fast and free.
o
Reliable
and easy to use.
o
Multi-Threaded
multi-user and robust SQL Database server.
Disadvantages:
o Missing Sub-selects.
o
MySQL doesn't yet support the Oracle SQL extension.
o Does
not support Stored Procedures and Triggers.
o MySQL
doesn't support views, but this is on the TODO.
Account Management
Statements:
CREATE USER:
The CREATE USER
statement creates new MySQL accounts.
Syntax:-
CREATE USER user
[IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD]
'password']] ...
Example:
CREATE USER
'monty'@'localhost' IDENTIFIED BY 'some_pass';
DROPUP USER:
The DROP USER statement
removes one or more MySQL accounts and their privileges.
Syntax:-
DROP USER user [, user]
....
GRANT :
The GRANT statement enables
system administrators to grant privileges to MySQL user accounts.
Syntax:--
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]]
...
ON [object_type] priv_level
TO user [IDENTIFIED BY [PASSWORD]
'password']
[, user [IDENTIFIED BY [PASSWORD]
'password']] ...
[REQUIRE {NONE | ssl_option [[AND]
ssl_option] ...}]
[WITH with_option ...].
Example:-
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;
RENAME USER :
The RENAME USER statement renames existing MySQL accounts.
Syntax:-
RENAME USER old user TO new
user[, old user TO new user] ...
Example:-
RENAME USER
'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1';
REVOKE:
The REVOKE statement enables system administrators to revoke privileges from
MySQL accounts.
Syntax:-
REVOKE
priv_type [(column_list)]
[, priv_type
[(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT
OPTION
FROM user [, user] ...
Example:-
RENAME USER
'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1';
SET
PASSWORD :
The SET PASSWORD statement assigns a password to an
existing MySQL user.
Syntax:--
SET PASSWORD [FOR user] =
{
PASSWORD('some password')
| OLD_PASSWORD('some password')
| 'encrypted password'
}
Example:-
SET PASSWORD FOR
'bob'@'%.loc.gov' = PASSWORD('newpass');
USING SEQUENCE:-
A sequence is a
database object that generates numbers in sequential order. Applications most
often use these numbers when they require a unique value in a table such as
primary key values.
The following list describes the characteristics
of sequences.
o Sequences
are available to all users of the database
o Sequences
are created using SQL statements (see below)
o Sequences
have a minimum and maximum value (the defaults are minimum=0 and maximum=263-1);
they can be dropped, but not reset
o Once a sequence returns a
value, the sequence can never return that same value
o While
sequence values are not tied to any particular table, a sequence is usually
used to generate values for
only one table
o Sequences
increment by an amount specified when created (the default is 1).
Creating
a Sequence:-
To create
sequences, execute a CREATE SEQUENCE statement in the same way as an UPDATE or
INSERT statement. The sequence information is stored in a data dictionary file.
The format for a
CREATE SEQUENCE statement is as follows:-
CREATE SEQUENCE sequence_name
[INCREMENT BY #]
[START WITH #]
[MAXVALUE # | NOMAXVALUE]
[MINVALUE # | NOMINVALUE]
[CYCLE | NOCYCLE]
Variable
Description:--
INCREMENT BY:- The increment value. This can be a positive or
negative number.
START WITH :-The start value for the sequence.
MAXVALUE:- The maximum value that the sequence can generate. If
specifying
NOMAXVALUE”- the maximum value is 263-1.
MINVALUE:- The
minimum value that the sequence can generate. If specifying
NOMINVALUE:- the
minimum value is -263.
CYCLE:- Specify CYCLE
to indicate that when the maximum value is reached the
sequence starts over again at
the start value. Specify NOCYCLE to generate
an error upon reaching the
maximum value.
Dropping
a Sequence:
To drop a sequence,
execute a DROP SEQUENCE statement. Use this function when a sequence is
no longer useful, or
to reset a sequence to an older number. To reset a sequence, first drop the
sequence and
then recreate it.
Drop a sequence
following this format:
DROP SEQUENCE my sequence
Using
a Sequence:
Use sequences when an
application requires a unique identifier. INSERT statements, and
occasionally UPDATE
statements, are the most common places to use sequences. Two
"functions" are available on sequences:
NEXTVAL:
Returns the next value from the sequence.
CURVAL:
Returns the value from the last call to NEXTVAL by the current
user during the current connection.
EXAMPLE:-
CREATE
SEQUENCE customer_seq INCREMENT BY 1 START WITH 100
