MySQL is a database
management system (DBMS), which is a collection of data used to store,
retrieve, delete, update and process. To add, access, and process data stored
in a computer database, you need a database management system such as MySQL Server.
MySQL is
a relational database management system (RDBMS), which stores data in separate
tables rather than which will be more effective to retrieve, update the date
rather than putting all the data as a bunch. This adds speed and flexibility to
process the data base. The SQL part of
``MySQL'' stands for ``Structured Query Language''.
Features
of MySQL:-
The
following are the important features available in MYSQL databases.
·
It works on many different platforms
and is easy to use multiple CPUS.·
It has support for group functions such as
count (), avg (), sum (), max (), min ().·
It also supports for Group by and
Order by clauses.·
It has support for Left outer join,
Right outer join with both the standard SQL and ODBC syntax.
·
Written in C and C++. Tested with a broad
range of different compilers.
·
Some of the statements like DELETE, INSERT,
REPLACE, and UPDATE return the number of rows that were changed (affected).
·
IN MySQL, SHOW is the command which can be
used to retrieve information about databases, tables, and indexes and the
EXPLAIN command can be used to determine how the optimizer resolves a query.
·
By using MYSQL, users can save time,
improve the performance of application.
·
Decreases the cost, it enhance the
security, reduce or eliminate the need for additional hardware.
·
You can mix tables from different databases
in the same query, syntax is as follows:
SYNTAX:
·
databasename.tablename
MYSQL handles large data bases like
maintaining database for a supermarket, banking and corporate groups. In Mysql,
privilege and password system is very flexible and secure, and allows
host-based verification. Passwords are secure because all password traffic is
encrypted when you connect to a server.
MySQL
Server
allows with databases that contain 50 million records. We also know of users
that use MySQL Server with 60,000 tables
and about 5,000,000,000 rows.
MySQL Table Types
1.
When you create a new table, you can tell MySQL what type of table to create.
The default table type is usually
MyISAM. MyISAM supports 3 different table types. Two of them are
chosen automatically depending on the type of columns you are using. The third,
compressed tables can only be created with the “myisampack” tool.
2.
Merge table is the second type which is a collection of MYISAM tables.we can
select, delete and update the tables.
3.
ISAM is one of the types of tables that are available in MYSQL. In this data is
stored in machine format and is OS dependent.
4. HEAP
table is one of the most important tables which is used to hash indexed which
makes transactions very fast.
The
above are some of the types of tables in MYSQL...
DATE: - DATE is a command
used to check the system date also allows user to update the date in below
format only.MySQL displays DATE values
in 'YYYY-MM-DD' format, but allows
you to assign values to DATE columns
using either strings or numbers.
DATETIME:-DATETIME is a
command used to check the combination of date and time. Even it allows to
update the date and time in the below format only.
MySQL
displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format,
but allows you to assign values to DATETIME columns
using either strings or numbers.
SYNTAXES
Create DATABASE:-
CREATE DATABASE [IF NOT EXISTS] db_name;
Create a table:-
CREATE
table table_name;
Ex: mysql> CREATE TABLE t1 (a INTEGER, b CHAR (10));
Syntax for ALTER:-
ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
mysql> ALTER TABLE t1 RENAME t2;
Syntax to Rename Tables:-
RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2...]
Ex: RENAME TABLE old_table TO backup_table,
New_table TO old_table,
Backup_table TO new_table;
Syntax to Drop a table:-
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name,...] [RESTRICT | CASCADE]
DROP
TABLE removes one or more tables. All table data and the table
definition are removed,
so be careful
with this commandCREATE INDEX Syntax
:CREATE [UNIQUE|FULLTEXT] INDEX index_name
ON tbl_name (col_name [(length)]...)
Ex: mysql> CREATE INDEX part_of_name ON customer (name (10));
DROP INDEX Syntax:-
DROP INDEX index_name ON tbl_name
DROP
INDEX drops the index named index_name from the table tbl_name.
(a) USE Syntax:-
USE db_name
The
USE db_name statement tells
MySQL to use the db_name database as the default database for subsequent queries. The
database remains current until the end of the session or until another USE statement is issued: mysql> USE db1;
mysql> SELECT COUNT(*) FROM mytable; # selects from db1.mytable
mysql> USE db2;
mysql>
SELECT COUNT(*) FROM mytable; #
selects from db2.mytable
(b) DESCRIBE Syntax (Get Information about Columns)
{DESCRIBE | DESC} tbl_name [col_name | wild]
DESCRIBE is a shortcut for SHOW
COLUMNS FROM. DESCRIBE provides
us the information about the table columns.
(c) BEGIN/COMMIT/ROLLBACK Syntax
MYSQL
runs in auto commit mode by default. this means that once you execute a query
automatically the MYSQL will get updated on to disk.
If
you are using transactions safe tables (like
InnoDB, BDB, you can put MySQL into non-auto
commit mode with the following command: SET AUTOCOMMIT=0
Once
you set the above command, then if you want to store your changes which you
have made to the data, then you have to commit. If you don’t want to set update
the autocommit for some series of commands then you have to set
STARTTRANSACTION;
SELECT * from employee;
UPDATE employee set salary =20000 where empno =80901;
COMMIT;
The LOCK/UNLOCK tables are used lock the
tables for the current thread and releases any locks held by the current
thread. All the tables that are locked by the current thread are automatically
unlocked when the thread issues another LOCK TABLES.
LOCK TABLES/UNLOCK TABLES Syntax
LOCK TABLES table name high Priority READ [LOW_PRIORITY] WRITE;
.........
Statements;
UNLOCK TABLES;
You
need to have
SELECT, INSERT, DELETE and UPDATE privileges for the
tables. The main reasons to use LOCK TABLES are for emulating transactions or
getting more speed when updating tables.
SELECT
and WHERE Clauses:-
A select expression is
used to show all the details of the table (database).Where clause is used in
select expression when we need to see the particular details of the database
rather to show all the details of the table.
Example
select empno, empname, dept from employee where empno >=100;
Logical Operators:-
In
MySQL, all logical operators results in true (0), false (1) or null. Some time
it may return any non-zero values for True.
NOT (Logical NOT):-Not
is a logical operator which results in 1 if the operand is 0, if the operand is
non-zero then it gives 0 and for not null it returns null and vice versa.
AND ( Logical AND,
&&):- And is a logical operator which
results in 1 if all operands are non-zero and not null , if one or more
operands are 0 then it results 0,other
wise NULL is returned.
OR (Logical OR): - OR
is a logical operator which evaluates to 1 if any operand is non-zero, to NULL
if any operand is NULL, otherwise it returns 0.
XOR (Logical XOR):
- XOR is a logical operator which returns NULL if either operand is NULL. For
non-null operands, it results to 1 if
the operand is odd number otherwise it returns 0.
LCASE (str):-
LOWER
(str):- It returns the string STR and changes changes to lowercases.
Returns
the string str with all characters changed to lowercase according to the
current character set mapping (the default is ISO-8859-1 Latin1):
mysql>
SELECT LCASE('INFORMATION’);
-> 'information’
UCASE (str):-
UPPER
(str):- It returns the string STR and changes all the character to uppercases.
Returns
the string str with all characters changed to uppercase according to the
current character set mapping.
Mysql>
SELECT UCASE ('Madhu’);
->’MADHU’
REPLACE (str, from_str, to_str):-Returns
the string str with all occurrences of the string from_str replaced by the
string to_str:
Mysql>
SELECT REPLACE ('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
REPEAT (str, count):-Returns
a string consisting of the string str repeated count times. If count <= 0,
returns an empty string. Returns NULL if str or count are NULL:
mysql>
SELECT REPEAT('MySQL', 3);
-> 'MySQLMySQLMySQL'
REVERSE (str):-Returns the
string str with the order of the characters reversed.
mysql>
SELECT REVERSE('abc');
-> 'cba'
SPACE (N):- This function returns a
string consisting of N space characters,
mysql>
SELECT SPACE(6);
-> ' '
LTRIM (str):- This function
returns a string consisting with leading space characters removed.
mysql>
SELECT LTRIM(' barbar');
-> 'barbar'
RTRIM (str):-
Returns
the string str with trailing space characters removed:
mysql>
SELECT RTRIM('barbar ');
-> 'barbar'
TRIM ([[BOTH | LEADING | TRAILING] [remstr] FROM] str):-
Returns
the string str with all remstr prefixes and/or suffixes removed. If none of the
specifiers BOTH, LEADING or TRAILING are given, BOTH is assumed. If remstr is
not specified, spaces are removed:
mysql>
SELECT TRIM(' bar ');
-> 'bar'
mysql>
SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql>
SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql>
SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'
LPAD (str, len, padstr):-
The LPAD () function returns the string str, left-padded with the string which
is given operator until the str is length characters long.
mysql>
SELECT LPAD('inf',4,'??');
-> '?inf'
RPAD (str, len, padstr):- The
RPAD () function returns the string str, right-padded with the string given by
the operator until the str length characters long.
mysql>
SELECT RPAD('Inf',5,'?');
-> 'Inf??'
These
are some of the examples of String functions in MySql.
No comments:
Post a Comment