VPS for Traders





Search This Blog

Loading...

Pages

Google

MySql HowTo

http://www.lankalinux.com/?q=node/6

MySql HowTo

This document is reviewed by M.B.G.Suranga De Silva on 18-07-2007

Working with MySQL

This document descrbes the commands used in MySQL under following topics.


Creating a Database in MySQL
Creating Tables
A Special Feature in MySQL
Viewing the Tables and Databases Created
Inserting Values to the Tables
Querying the Tables
Comparison Operators
Logical Operators
Limiting the Search Area of the Table
Pattern Matching
User Defined Variables
Summarizing Techniques
Deteling and Updating the Rows
How You Can Simplify the Connection Process
Storing the Parameters in an Option File
Repeating Parameters using History
Ways of Avoiding Long Typing for the Statements
Running in Batch Mode
MySQL Syntax
Naming Conventions
Case Sensitive SQL Statements
The Mode of SQL
Character sets Supported by SQL
Handling Tables
Creation of the Tables
Merging Tables
Federated Tables
Dropping the Tables
Retrieving Information about Databases and Tables
Joins
Trivial Join
Cross Join
Left Joins and Right Joins
Usage of Sub Queries
In and Not In
Exists and Not Exists
Multiple table Deletion and Updating
Transactions in Mysql
Transaction Save Points
Transaction Isolation
Relative Updates Instead of Absolute Updates
Foreign Keys and Referential Integrity
Fulltext Searches
Natural Language Searching
Boolean Mode Searching
Query Expansion Searching
Things to Consider for Boolean Mode Searches
Query Expansion Fulltext Search
New Features in Mysql
Stored Procedures
Triggers
Data Value in Mysql
Numerical Values
String Values
Binary and Non Binary Strings
Date and Time Values
Spatial Values
Null Values
Data Types in Mysql
Data Types for Numerical Values
Data Types for String Values
Data Types for Date and Time Values
Data Types for Spatial Values
Numerical Data Types
Integer Data Type
Floating Point and Fixed Point Data Types
Fixed Point Type –Decimal
Bit Data Type
Attributes of Numerical Data Types
String Data Types
Char and Varchar Data Types
Binary and Varbinary Data Types
The BLOB and TEXT Data Types
The ENUM and SET Data Types
String Data Type Attributes
Date and Time Data Types
Date, Time and Datetime data types
Timestamp Data Type
Year Data Type
Handling Invalid Data Types
Working with Sequences in Mysql
Auto_increment
Expression Evaluation and Type Conversion
Arithmetic Operators
Logical Operators
Bit Operators
Comparison Operators
REGEXP
Type Conversion
Query Optimization
Loading Data Efficiently and Scheduling and Locking Issues

MySQL is a database management system that is used for relational databases. This document lists options available in MySQL.

After a fresh installation you need to change the password of the MySQL super user. The super user "root" doesn't has a password by default. Hence change it by executing the following command.


#mysqladmin -u root password 'ceylonlinux'

In this case the password has been changed to "ceylonlinux".

After that you can craete new users. First you need to login to MySQL as the root user.


#mysql -u root -p

Then enter the password "ceylonlinux".

Then in the mysql prompt enter the following command.


mysql>grant all on *.* to suranga@localhost identified by 'ceylonlinux';

A new user called "suranga" is added with all the privileges on all the databases and is assigned the password "ceylonlinux".

Creating a Database in MySQL

To execute mysql commands first you have to login to the mysql prompt. It can be done using the following command.


#mysql -u root -p

-u is used to specify the user and -p is used to get the prompt to enter the password. After executing the above command you will be asked to enter the password.

Before creating the tables to insert values you need to create a database to store the tables. The following example illustrates how to come up with a database.


mysql> create database nir;

where nir is the name of the database.

However creating a database doesn’t make it as the default database. To work with the newly created database first you need to select the it. You can check what the current default database is before making selecting, using the command


mysql> select database();

and the output will be


+------------+
| database() |
+------------+
| |
+------------+

which indicates that no database is selected presently.

Now select your database and check the result using the following commands.


mysql> use nir;
Database changed
mysql> select database();

use nir; will make the newly created database as the default one. The output of the select statement will be


+------------+
| database() |
+------------+
| nir |
+------------+

and it will be clear that nir is now the default database.

There exists another way of making a database as the default one. This method can be used only for the existing databases. When you invoke the mysql write


% mysql nir

If you want to give any parameters they also should be given in this line. For an example if you want to connect to the mysql server running at aaa.com use the following command.


% mysql -h aaa.com -p -u username nir

To connect to the server running on the same host where you are working type


% mysql -p -u username nir

Back to the top

Creating Tables

Imagine that the nir database should contain information about the schools and their principles. Then you need to create two tables for the schools and for the principles. Create the schools table using the following code.


mysql> create table schools
-> (
-> name varchar(20) NOT NULL,
-> location varchar(20) NOT NULL,
-> startdate date NOT NULL,
-> grades varchar(10) NOT NULL
-> );

Then create the principle table

You can execute pre written queries also. For an example consider that these commands are written in the query.sql file which reside in the nir directory you can execute the file using the following commands. First change to the nir directory and then type


% mysql nir <>

Then create the principle table.


mysql> create table principles(
-> principleID int unsigned NOT NULL auto_increment,
-> primary key(principleID),
-> name varchar(30)NOT NULL,
-> title varchar(5) NOT NULL,
-> age int NOT NULL,
-> school varchar(20) NOT NULL,
-> since date NOT NULL
-> );

The syntax used can be explained using the principles table. The field name of this table are name, title, age, school, and the since. The name, title and school fields are of varchar type which indicates that they are characters and the maximum number that can have for the value is specified in the parenthesis. Age and principleID is of integer type and indicates that the values are numbers. Since is the field which stores the date values. NOT NULL constraint emphasizes that these fields must have values and cannot be null.
Back to the top

A Special Feature in MySQL

Some times you may have field whose values should be incremented sequentially when you insert new rows. MySQL convince this task by providing the option


AUTO_INCREMENT

When this option is used with a certain field the values will be automatically generated and be assigned to the column.

The primary key in this table is principleID. Hence the values of this table can be neither null nor repeated.
The values of since field will be stored according to the format YY-MM-DD.
After creating the tables you can view the structure of it using the command


mysql>describe principles;

The output will be


+-------------+------------------+------+-----+------------+---------------- +
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+------------+------------- --- +
| principleID | int(10) unsigned | | PRI | NULL | auto_increment |
| name | varchar(30) | | | | |
| title | varchar(5) | | | | |
| age | int(11) | | | 0 | |
| school | varchar(20) | | | | |
| since | date | | | 0000-00-00 | |
+-------------+------------------ +---- --+-----+---------- --+---------------- +

The same can be achieved in many ways


DESCRIBE principles;
DESC principles;
EXPLAIN principles;
SHOW COLUMNS FROM principles;
SHOW FIELDS FROM principles;

All these queries will give the same output. You can restrict the output to certain values using these commands. For an example to view only the fields of a particular pattern you can type


mysql>show columns from principles like '%name';

and the output will be


+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(30) | | | | |
+-------+-------------+------+-----+---------+-------+

Back to the top

Viewing the Tables and Databases Created

You can view all the tables you have created using the command


mysql>show tables;

The output will be


+---------------+
| Tables_in_nir |
+---------------+
| principles |
| schools |
+---------------+

To view the databases you have created use the command


mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| nir |
| test |
+----------+

You can use mysqlshow command to view the databases and the tables when you are not in the sql mode. To view the databases type


% mysqlshow

and to view the tables of a particular database type


% mysqlshow nir

Back to the top

Inserting Values to the Tables

You can insert the values using the format
Insert into table(field1,field2,…..) values(value1,value2,…..);

Then we can insert values to the schools table as mentioned below


mysql> insert into schools(name, location, startdate, grades)
? values('Musaeus', 'Colombo 7', 1893-8-15, 'upto 13');

You can insert the values using files also
For that use


% mysql nir <>

If you are already running mysql execute the command


mysql> source insert_principles.sql;

If you have the records stored in a file as raw data values rather than as INSERT statements, you can load them with the LOAD DATA statement or with the mysqlimport client program.


mysql> load data local infile 'data.txt' into table principles;

This LOAD DATA statement assumes that field values are separated by tabs and the lines end with newline characters. It also assumes that the values are in the same order as that the columns are stored in the table. It's possible to read files in other formats or to specify a different column order.
The keyword LOCAL makes the client program to read the data file and send to the server to be loaded. You can omit LOCAL if the file is located on the server host and you need the FILE server access privilege that most MySQL users don't have. You should also specify the full pathname to the file so that the server can find it.
Back to the top

Querying the Tables

You can select the values from the table using
Select field1,field2,…… from table
query

To select all the fields from the table use * instead of the field names.


mysql> select * from schools;
+----------+-----------+------------+--------- +
| name | location | startdate | grades |
+----------+-----------+------------+--------- +
| Musaeus | Colombo 7 | 0000-00-00 | upto 13 |
| Sirimavo | Colombo 7 | 0000-00-00 | upto 13 |
| Nalanda | Colombo 4 | 1895-04-18 | upto 13 |
+----------+-----------+------------+-------- -+

You can query the tables by concatenating them (Query on multiple tables). For an example consider the following query and its output.


mysql> select schools.* ,principles.name from schools,principles
-> where schools.name=principles.school;
+----------+-----------+------------+---------+------------------- +
| name | location | startdate | grades | name |
+----------+-----------+------------+---------+------------------ -+
| Musaeus | Colombo 7 | 0000-00-00 | upto 13 | N.K.Pilapitiya |
| Sirimavo | Colombo 7 | 0000-00-00 | upto 13 | Pushpa Kalubowila |
| Nalanda | Colombo 4 | 1895-04-18 | upto 13 | A.Abeykoon |

This query concatenates the two tables and selects all the fields from the schools table and the name from the principles table where the name of the school is equal to the school of the principle.

You can specify more information when querying the tables. The following illustrates an example.


mysql> select schools.*, principles.name from schools, principles
-> where schools.name=principles.school
-> and schools.location='Colombo 7';
+----------+-----------+------------+---------+------------------- +
| name | location | startdate | grades | name |
+----------+-----------+------------+---------+------------------- +
| Musaeus | Colombo 7 | 0000-00-00 | upto 13 | N.K.Pilapitiya |
| Sirimavo | Colombo 7 | 0000-00-00 | upto 13 | Pushpa Kalubowila |
+----------+-----------+------------+---------+------------------- +

For this selection criteria a set of operators is used . Following describes the operator and its functionality.
Arithmetic Operators
Operator Meaning
+ Addition
- Subtraction
* Multiplication
/ Division
DIV Integer division
% Modulo (remainder after division)
Back to the top

Comparison Operators

Operator Meaning
< Less than <= Less than or equal to = Equal to <=> Equal to (applies for NULL values also)
<> or != Not equal to
>= Greater than or equal to
> Greater than
Back to the top

Logical Operators
Operator Meaning
AND Logical AND (true if both operators are true)
OR Logical OR (true if either operator is true)
XOR Logical exclusive-OR (true only if one operator is true)
NOT Logical negation (Gives the complementary value)

For NULL values you cannot use these operators. Instead you have to use IS NULL or IS NOT NULL

You can arrange the output as you like. The clauses used are ORDER BY and GROUP BY

ORDER BY Example


mysql> select * from schools order by name;
+----------+-----------+------------+---------+
| name | location | startdate | grades |
+----------+-----------+------------+---------+
| Musaeus | Colombo 7 | 0000-00-00 | upto 13 |
| Nalanda | Colombo 4 | 1895-04-18 | upto 13 |
| Sirimavo | Colombo 7 | 0000-00-00 | upto 13 |
+----------+-----------+------------+---------+

In this example the output is ordered in the ascending order of the name. If you want it in the descending order use the DESC keyword.


mysql> select * from schools order by name desc;
+----------+-----------+------------+---------+
| name | location | startdate | grades |
+----------+-----------+------------+---------+
| Sirimavo | Colombo 7 | 0000-00-00 | upto 13 |
| Nalanda | Colombo 4 | 1895-04-18 | upto 13 |
| Musaeus | Colombo 7 | 0000-00-00 | upto 13 |
+----------+-----------+------------+---------+

You can apply this to multiple fields also. In that case the output is first ordered according to the first field and then according to the second field.


mysql> select * from schools order by name, startdate desc;
+----------+-----------+------------+---------+
| name | location | startdate | grades |
+----------+-----------+------------+---------+
| Musaeus | Colombo 7 | 0000-00-00 | upto 13 |
| Nalanda | Colombo 4 | 1895-04-18 | upto 13 |
| Sirimavo | Colombo 7 | 0000-00-00 | upto 13 |
+----------+-----------+------------+---------+

If you have NULL values in the table they will appear first when ordered by the ascending order.

GROUP BY Example


select * from schools group by location
-> ;
+---------+-----------+------------+---------+
| name | location | startdate | grades |
+---------+-----------+------------+---------+
| Nalanda | Colombo 4 | 1895-04-18 | upto 13 |
| Musaeus | Colombo 7 | 0000-00-00 | upto 13 |
+---------+-----------+------------+---------+

Back to the top

Limiting the Search Area of the Table


mysql> select * from schools where name='Nalanda' limit 0;
Empty set

Here although the school Nalanda is in the table since we have restricted the search to 0 rows it gives an empty set as the output.
LIMIT also allows you to pull a section of records out of the middle of a result set. To do this, you must specify two values. The first value is the number of records to skip at the beginning of the result set, and the second is the number of records to return.
You can rename the name of the output column using statements like this


mysql> select name as 'School-Name' from schools;
+-------------+
| School-Name |
+-------------+
| Musaeus |
| Sirimavo |
| Nalanda |
+-------------+

Here you can omit ‘as’ keyword.
Working with DATE
You can divide the date field into 3 parts when it is used for selecting queries. As an example consider the following query and its output.


mysql> select * from schools where month(startdate)='August';
+----------+-----------+------------+---------+
| name | location | startdate | grades |
+----------+-----------+------------+---------+
| Musaeus | Colombo 7 | 0000-00-00 | upto 13 |
| Sirimavo | Colombo 7 | 0000-00-00 | upto 13 |
+----------+-----------+------------+---------+

In the same manner you can use DAYOFMONTH and YEAR also.
You can retrieve the current date by curdate() function. This can be used when calculating the duration, time periods etc.


mysql> select name,floor((to_days(curdate()-to_days(startdate)))/365) as age
? from schools;
+----------+------+
| name | age |
+----------+------+
| Musaeus | 113 |
| Sirimavo | 34 |
| Nalanda | 112 |
+----------+------+

The floor function round off the values after division occurs. The to_days function coverts the date into the days.
Date_sub and date_add also functions in the same manner.
e.g.


mysql>SELECT DATE_SUB('1970-1-1', INTERVAL 10 YEAR);

Back to the top

Pattern Matching

You can execute queries with pattern matching even though you cannot remember the actual pattern. For that pattern matching phrases such as ‘like’, ’not like’ are used. The symbol % is used with the pattern. For an example if you want to match a word starting with a type the pattern as a%. If the pattern you need ends with ‘a’ type it as %a.


e.g. mysql> select * from schools where name like 'M%';
+---------+-----------+------------+---------+
| name | location | startdate | grades |
+---------+-----------+------------+---------+
| Musaeus | Colombo 7 | 1893-11-15 | upto 13 |

mysql> select * from schools where name like '%s';
+---------+-----------+------------+---------+
| name | location | startdate | grades |
+---------+-----------+------------+---------+
| Musaeus | Colombo 7 | 1893-11-15 | upto 13 |
+---------+-----------+------------+---------+

Back to the top

User Defined Variables
You can use variables when querying to store the results of the query. Later you can use the variable in other queries. Define the variable using @ sign. The values can be assigned using := sign.


mysql> select @l:=location from schools where name='Musaeus';
+--------------+
| @l:=location |
+--------------+
| Colombo 7 |
+--------------+

Then you can use @l value in any other query.


mysql> select * from schools where location=@l;
+----------+-----------+------------+---------+
| name | location | startdate | grades |
+----------+-----------+------------+---------+
| Musaeus | Colombo 7 | 1893-11-15 | upto 13 |
| Sirimavo | Colombo 7 | 1972-08-25 | upto 13 |
+----------+-----------+------------+---------+

Another way of assigning values to the variables is using the set keyword. In that case either = or : = can be used to assign the values.


mysql> set @today= curdate();

Back to the top

Summarizing Techniques

MySQL allows you to summarize the table information using special types of options. These options are described below.
DISTINCT
This is the phrase used to eliminate the redundant information from the result set. Consider the student table which has only the name and the age fields.


mysql> select * from student;
+------+------+
| name | age |
+------+------+
| Ama | 22 |
| Mala | 22 |
| Anju | 18 |
| Anju | 20 |
+------+------+

Then execute the query with distinct keyword


mysql> select distinct name from student;
+------+
| name |
+------+
| Ama |
| Mala |
| Anju |
+------+

As it can be seen although the table consists of two people called ‘Anju’ when the query is executed with the distinct keyword it shows only one instance of ‘Anju’.
COUNT
This option is used to get the number of instances in the table. An example is illustrated below.


mysql> select count(*) from student where name='Anju';
+----------+
| count(*) |
+----------+
| 2 |
+----------+

Here count(*) indicates that the entire table is scanned. If the option is written with the column name (count(name)) it returns the number of not null values in that column. On the other hand the same option can be combined with other options also.


mysql> select count(distinct name) from student;
+---------------------- +
| count(distinct name) |
+---------------------- +
| 3 |
+---------------------- +

The count option can be used to get the number of instances of each age group in the following manner.


mysql> select age,count(*) from student group by age;
+------+----------+
| age | count(*) |
+------+----------+
| 18 | 1 |
| 20 | 1 |
| 22 | 2 |
+------+----------+

Other Summarizing Options


mysql> select * from marks;
+------+-------+-------+
| name | marks | grade |
+------+-------+-------+
| Ama | 89 | 12 |
| Mala | 88 | 12 |
| Anju | 76 | 10 |
| Anju | 67 | 9 |
+------+-------+-------+

Consider the above table for the following queries.
An important thing to consider here is that to use these options you need to specify the group by clause.
MAX()


mysql> select name, max(marks)as maximum from marks group by grade;
+------+---------+
| name | maximum |
+------+---------+
| Anju | 67 |
| Anju | 76 |
| Ama | 89 |
+------+---------+

MIN( )


mysql> select name, min(marks)as minimum from marks group by grade;
+------+---------+
| name | minimum |
+------+---------+
| Anju | 67 |
| Anju | 76 |
| Mala | 88 |
+------+---------+

SUM( )


mysql> select sum(marks)as total from marks group by grade;
+-------+
| total |
+-------+
| 67 |
| 76 |
| 177 |
+-------+

AVG( )


mysql> select avg(marks)as average from marks group by grade;
+---------+
| average |
+---------+
| 67.0000 |
| 76.0000 |
| 88.5000 |
+---------+

WITH ROLLUP
This generates meta summaries of the summaries.
LEFT JOINS
When left joins are used each row in the table mentioned to the right if the join get concatenated with each row on the mentioned field in the table specified to the let to the join.
Back to the top

Deteling and Updating the Rows

You can delete a row in the table using the following statement.
Delete from table where ‘the selection criteria


e.g.
mysql>delete from marks where grade=9;
+------+-------+-------+
| name | marks | grade |
+------+-------+-------+
| Ama | 89 | 12 |
| Mala | 88 | 12 |
| Anju | 76 | 10 |
+------+-------+-------+

You can update the rows using the following statement.

Update table set column-name where ‘selection criteria’


e.g. mysql>update marks set marks=90 where name= 'Mala';
+------+-------+-------+
| name | marks | grade |
+------+-------+-------+
| Ama | 89 | 12 |
| Mala | 90 | 12 |
| Anju | 76 | 10 |
+------+-------+-------+

Back to the top

How You Can Simplify the Connection Process

When you get connected to the SQL server it is needed to specify several parameters such as host name, user name and password. Some times it may be tiresome to write all these information. There are several ways of avoiding such lengthy writing. These options are listed below.
1. You can store the connection parameters in an option file.
2. You can repeat the parameters by taking the use of the history capability of the shell.
3. You can define a mysql shortcut using a shell alias or script.
A detailed description on how to use those options is given below.
Back to the top

Storing the Parameters in an Option File.

Mysql allows you to store the connection parameters in an option file by releasing you from writing those each time you run the process. These parameters act in the same manner as they are entered in the command line. Mysql allows other users such as mysqlimport or mysqlshow to use these option files. In other words this allows you to use not only mysql but also other services. Follow the steps specified here to create an option file.
First create a file named .my.cnf in your root directory.
You can use any name instead of my for this purpose.
Then enter the following fields in that file.


[client]
host=server_host
user=your_name
password=your_pass

[client] indicates the beginning of the client option group. ‘host’ indicates the host name of the server. 'user’ denotes the user name and the ‘password’ field indicate the password. An example is given below.


[client]
host=clinux.net
user=ulinux
password=plinux

[client] is the field that must be there while the other filed are optional. You can specify only the field that you need to include in the option file and can give the other values in the normal way. For an example if you are running the server at the localhost the host filed can be ignored because it is the default host. After creating the option file give the access to the file only to the authorized users using chmod command.
Back to the top

Repeating Parameters using History

Using this option you can invoke mysql without typing all the parameters again. If you have recently invoked mysql invoke it using the command


% !my

Here the ! sign will tell the shell to search the history and find the command which starts with my and to execute it.
Else use the up and down arrow keys and select the command you want to execute and execute it.
Creating Aliases
You can set an alias to the long command you execute using the alias keyword. Set the alias as follows
alias als 'mysql -h aaa.com -p -u username nir'

After that you can use %als instead of the long command.

If you are in bash the syntax that you have to use is slightly different.


alias als ='mysql -h aaa.com -p -u username nir'

Using Scripts
You can write a script and execute it instead of the long command.


#! /bin/sh
exec mysql -h aaa.com -p -u username nir

Give a name to the script and make it executable using the chmod command. Then instead of the long command just type the name of the script in the command prompt.
Back to the top

Ways of Avoiding Long Typing for the Statements

There are several ways to avoid retyping specially when an error occurs in a statement. The options are listed below.
1. Use mysql's input line-editing facility.
2. Run mysql in batch mode
Mysql has a built in GNU Readline library which allows you to modify the current statement or to call the previous statement and reenter them either as it is or after modifying.
Some of the important commands are listed below.
Key Sequence Meaning
Up arrow or Ctrl-P Recall previous line
Down arrow or Ctrl-N Recall next line
Left arrow or Ctrl-B Move cursor left (backward)
Right arrow or Ctrl-F Move cursor right (forward)
Escape b Move backward one word
Escape f Move forward one word
Ctrl-A Move cursor to beginning of line
Ctrl-E Move cursor to end of line
Ctrl-D Delete character under cursor
Delete Delete character to left of cursor
Escape D Delete word
Escape Backspace Delete word to left of cursor
Ctrl-K Erase everything from cursor to end of line
Ctrl-_ Undo last change; may be repeated
Back to the top

Running in Batch Mode

You don’t need to write the commands in interactive mode always. Instead you can use batch mode also. For an example if you have written a query and saved it using the .sql extension you can run it as follows.


% mysql nir<>

If you want to save the output, redirect it to a file:


% mysql -t nir output_file

If you are already running mysql, execute the contents of the file by using a source command:


mysql> source query.sql

MySQL Syntax
Back to the top

Naming Conventions
You should pay more attention on the characters you can use and the number of characters that can be used when you are dealing with the identifiers for the databases. Another factor that affect the naming rules is that the server can be configured to be used for several sql modes.

Unquoted identifiers can use any character of the system default character set along with the _ and $ signs. An identifier can start even with a digit However you cannot have identifiers that are built solely with digits. The reason for this is that then it will be undistinguishable with the numbers.

Identifiers can be quoted in backtick characters so that then any character can be used except the backtick character itself and the byte characters. This is especially use when the identifier is a sql keyword or if it contains spaces or other special characters. If you want to use the quote within the quoted identifier just type it twice.
e.g. ‘sam’’s’

In addition to these rules some more rules apply for the databases and the tables. One of those is that you cannot start the identifier with the . character. This is because . is used as a separator character (i.e. databasename.tablename.column name).
The second rule is that you cannot use the Unix or Windows path separating characters. (i.e./ or \).
You can use aliases to the columns freely.

Note that if the ANSI_QUOTES sql mode is opened you can use “ as well as `. There you have to write the string values in single quotes. Otherwise it will be interpreted as an identifier.
Function names in sql are not reserved and they can be used as identifiers.

Important Things to Remember

Identifier length

The length of the identifier should not be more than 64 characters.

Identifier Qualifiers

Usually you have to give a name which makes it easier to identify to what it refers to. For an example it can be understood the an identifier stating with db_name refers to a database while an identifier starting with tb_name refers to a table.
Back to the top

Case Sensitive SQL Statements

Case sensitivity rules in SQL statements vary for different parts of the statement, and also depend on what you are referring to and the operating system of the machine on which the server is running

The keywords and the function name in sql are not case sensitive and hence they can be written in either case.

Names of databases and tables
The names of the databases and tables are case sensitive by default. Hence they have to be referred exactly in the same manner as they have been defined. An example is given below.


mysql> select * from schools;
+----------+-----------+------------+---------+
| name | location | startdate | grades |
+----------+-----------+------------+---------+
| Musaeus | Colombo 7 | 1893-11-15 | upto 13 |
| Sirimavo | Colombo 7 | 1972-08-25 | upto 13 |
| Nalanda | Colombo 4 | 1895-04-18 | upto 13 |
+----------+-----------+------------+---------+
3 rows in set (0.10 sec)

mysql> select * from Schools;
ERROR 1146: Table 'nir.Schools' doesn't exist

However this case sensitivity depends on the way the operating system treats the file names. For an example Widow file names are not case sensitive. Hence a server running on Windows will not treat the database and table names as case sensitive. But You should remember that the Unix file names are case sensitive and pay much attention to the case of the database and table names.

If you want to force the user to use lower case letters for database and table name configure the server using lower_case_table_name system variable.

Column and Index Names
Names of the fields and the indexes are not case sensitive.

Alias Names
Names of the aliases are case sensitive by default.
Back to the top

The Mode of SQL

The mode of sql affects how it treats the identifiers and hoe it executes the sql statements. Initially the mode could be fixed using the --sql-mode startup option. As MySQL 4.1.1
The mode can be changed runtime and by the individual users in a manner that it will affect only to their connection. Some of the available modes are listed below

• ANSI_QUOTES makes the server to recognize double quote as an identifier quoting character.
• PIPES_AS_CONCAT causes || to be treated as the standard SQL string concatenation operator rather than as a synonym for the OR operator.
• ANSI is a composite mode. It turns on ANSI_QUOTES, PIPES_AS_CONCAT, and several other mode values that result in server behavior more like standard SQL than how it operates by default.
• STRICT_ALL_TABLES and STRICT_TRANS_TABLES enable "strict" mode. In strict mode, the server is more restrictive about accepting bad data values. (Specifically, it rejects bad values rather than changing them to the closest legal value.) TRADITIONAL is another composite mode. It is like strict mode, but enables other modes that impose additional constraints for even stricter data checking. Traditional mode causes the server to behave like more traditional SQL servers with regard to how it handles bad data values. These modes are available as of MySQL 5.0.2.
When you set the SQL mode, specify a value consisting of one or more mode values separated by commas, or an empty string to clear the value. Mode values are not case sensitive.
To set the mode type as follows in the command line or in an option file.


--sql-mode="ANSI"
--sql-mode="ANSI_QUOTES,PIPES_AS_CONCAT"

If you want to change the mode at run time use the ‘set’ keyword.
To set a client’s own session:


SET sql_mode = 'TRADITIONAL';

To change the mode globally: Use the ‘Global’ keyword.


SET GLOBAL sql_mode = 'TRADITIONAL';

To set the global mode you need super administrative privileges. After setting the global mode it will be the default mode for the user that login afterwards.

You can view the state of the session or global mode in the following manner.


SELECT @@SESSION.sql_mode;
SELECT @@GLOBAL.sql_mode;

Back to the top

Character sets Supported by SQL

Prior to SQL 4.1 the database server operated using a single character set for a time. But after SQL 4.1 servers can support multiple characters sets simultaneously. These sets can be specified independently in databases, tables, columns or String values.

Character Sets Prior to SQL 4.1
There the mysql didn’t support any specific character set. Instead it used the server’s character set. Hence the only way to change the character set of Mysql was to change it of the server by using the option --default-character-set. This method was very limited. For an example there you cannot have different character types for different columns in the same table. This also creates index problems if you change the server’s character set after creating the tables and inserting the values to it. So then the index based queries will not work correctly. To over come that problem you have to recreate the indexes according to the collating order of the new character set. Altering the table can be done in several ways.
1. Dump the table with mysqldump, drop it, and reload it from the dump file. This operation causes the indexes to be rebuilt as the file is reloaded. It works for any storage engine.
2. Drop the indexes and add them again.
3. For MyISAM tables, you can rebuild indexes by running myisamchk with the --recover and --quick options, together with a --set-character-set option that specifies the character set to use. This is as same as using mysqlcheck program with the --repair and --quick options, or a REPAIR TABLE statement with the QUICK option.

Character Set Support in MySQL 4.1 and Up

To overcome the above mentioned difficulties some new features are added to the Mysql versions 4.1 and up.
Here the server supports the use of multiple character sets simultaneously. Utf8 and ucs2 character sets supports the Unicode character set. The character set for the database can be specified by the create database statement and the table’s character set can be specified at the create table statement. These character sets can be changed using
Alter database or alter table respectively. An important ting to note is that create table and alter table statements have clauses for column level assignment.

There are functions for altering one character set to another or to check the character set of a value.

A single character set may be consists of more than one collation and you can chose the best one for your application. Collation operator allows you to alter the collation of a value and collation() function returns the collation of a particular value.

To view the available character sets and the available collation you can use the statements
Show character set and show collation

You have to remember that it is impossible to have multiple character sets in a string value and in rows in the same column.

Assigning Character Sets and Collations
This can be done in several levels. In this part it will be described how to achieve it.
Server’s Character Set and the Collation
This is built in at the compile time. Using the options --character-set-server and
--collation-server you can change them. If you specify only the character set, its default collation becomes the server's default collation. If you specify a collation, it must be compatible with the character set. (A collation is compatible with a character set if its name begins with the character set name. For example, utf8_danish_ci is compatible with utf8 but not with latin1.)
There are two types of sql statements used for giving the character set and the collation to a database, table or to a column. They are
CHARACTER SET character_set_type
COLLATE collationtype
You can specify only the character set type. Then the default collation of the type will be used. If you specify the collation also it should be compatible with the character set type. The collation which is compatible with the specified character set type has the name of that character set as the first part of the collation name. You can specify only the collation also. If you haven’t specified the character set type and the collation server’s defaults will be used. To change the default values use the following command when you are creating the database.
CREATE DATABASE db_name CHARACTER SET charset COLLATE collation;

In the same manner you can specify the default character set and a default collation for the tables also For that use the following statement when you are creating the tables.

CREATE TABLE tbl_name (field type,…) CHARACTER SET charset COLLATE collation;
If they are not specified the default values of the database will be used for the table also.

You can order the values of a column which are specified in a specific collation using another collation also. As an example assume that the ‘name’ column in students table is in the collation ‘latin1_swedish_ci’. You can order the values in the latin1_spanish_ci
Collation using the following command.

SELECT name FROM students ORDER BY name COLLATE latin1_spanish_ci;

Viewing the Character Sets and the Collations Available

To view the available character sets type


show character sets;

To view the collations use the command


SHOW COLLATION;

Unicode Support in Mysql

Unicode is a character set that is used to represent almost all the languages in the world. Mysql supports this Unicode through two types of character sets.

ucs2 This character set correspond to the Unicode UCS-2 encoding. It uses two bytes for each character and the values are stored in the big-endian format. (i.e. The most significant bit is stored in the lowest memory location.)

ucs8 This is a variable length character representation which uses 1,2 or three bytes for a character. This corresponds to the Unicode UTF-8 encoding.

Creating, Selecting, Altering and Dropping the Databases.

Storage Engines

Although you see the data are stored in a structure called database internally they are stored in files as in usual manner. But as the end user you don’t have to pay attention to those file handling. (e.g. How you write the data to the files). A mechanism called ‘Storage Engines’ handle that task on behalf of you. Some example storage engines are listed below.

MyISAM
Heap
Memory
Merge

Creation
For creating the databases you can use the following commands.

create database databasename;

The databasename field indicates the name you want for the database and for that field you can use any proffered name.
When this command is executed the SQL server will create a new directory under its data directory with the same name specified in the databasename filed. A new file named db.opt will also be created to store the attributes of the database.
An important thing to remember is that the database has to be unique. Mysql doesn’t allow you to use the same database name that already exists.

As mentioned earlier you can specify the character set and the collation for the database when you are creating the database. The full syntax is given below.


CREATE DATABASE [IF NOT EXISTS] db_name
[CHARACTER SET charset] [COLLATE collation];

the clause IF NOT EXISTS is optional. If it is used the database will only be created if there is no database with same manner. After including this option an error message will not be prompted to the user even there exists a database with the same name. It will simply create the database if it is unique.

Rules for specifying the character set and the collation is as same as mentioned in the previous section.

Selection

As mentioned in the earlier section we can change the database using

use databasename;
statement. Here the databasename field indicates the name of the data store you want to use.

However you can select the tables of a particular database even without changing the database. The following sql statement illustrates an example.


select name from nir.schools;

Another important thing to consider is that you can select the table values of other databases also while staying inside a particular database. This can be done by specifying the table name along with the database name as specified earlier.

You have to select the database for every new connection as it is considered as the default database only for that particular connection.

Alternation

Using this option you can change the character set and the collation of the database.

alter database db_name [character set charset] [collate collation];

Deletion

Dropping a database can be done by using the following statement.

drop database db_name;

If the database directory is consist of non table files then the drop database command fails. The reason for this is that the server is unable to remove such files. Due to that the whole directory will remain without dropping.
Back to the top

Handling Tables

This part deals with the mechanisms used for handling the tables.
Back to the top

Creation of the Tables
As mentioned in the earlier parts you can create the tables using the statement,

create table table-name (field1 type1,…..);
You can give the storage engine for the table as you create the tables in the following manner,
Create table table-name(field1 type1,….) engine=engine-name;
Instead of ‘engine’ keyword you can use ‘type’ also.
You can state the default engine by setting the storage-engine variable.
To view the storage engine of a table you can use ‘show create table’ or ‘show table status’
You can specifically mention to create a table if it is not existing by mentioning,
Create table if not exists;
This will hide the warning generated if we try to create table with the same name which is already in use.
Temporary Tables

You can use temporary tables to store the data. The main feature of these tables is that they will be deleted automatically when the connection to the server is terminated. You can create the temporary tables using the following statements,


create temporary table;

These tables need not to be deleted using the drop table commands.
As same as for the normal tables you can specify the characteristics such as the storage engine even for the temporary tables.
Creating the Copies of Tables
Some times you may need to load data which already exist to other tables. Here the table format should be the same as it of the original table. Hence you have to create a copy of the original table to load the data. That can be achieved through the following statement,


mysql> create table table-name like original-table;

In this statement table-name indicates the new table to be created. Original-table refers to the table that already exists. This statement will create an empty copy of the original table of the same structure.
To populate the newly created table use the following statement,


mysql> Insert into table-name values (select * from original-table);

Here Mysql will select all the values from the original table and will insert to the new table. You can create table as a temporary table also, by specifying the ‘temporary’ keyword.
You can insert only some specific rows into the table from the original table using the clauses such as ‘where’.
There is another way of specifying to create a copy of a table. The following is an example of such a statement.


mysql> create table table-name select * from original-table;

The main difference with this statement is that it does not have the same structure of the columns in the original table.
The names of the columns will be the same as those of the original table. But if the resultant column is the output of a certain calculation the column name will be expression.
e.g.


mysql> create table table-name select number*2 from original-table,

When you use this statement it will create a table named table-name with a column whose name is number*2. To avoid such column name use the ‘as’ keyword. Consider the same example, it can be rewritten as follows,


mysql> Create table table-name select number*2 as result from original-table;

Then the column name will be ‘result’ rather than ‘number*2’.
You can create tables by combining more than one table also. An important thing to consider here is that if the two tables have columns with the same name then the query fails. Consider the following statement as an example,


mysql> Create table table-name select * from original-table1, original-table2;

If the two original tables have columns with the same name an error will be prompted as there will be two columns with the same name in the new table. To solve the above problem use aliases for the column names in the new table.
e.g.


mysql> create table table-name select original-table1.c as col1, original-table2.c as col2 from original-table1,original-table2;

You can use aliases for the table names also. For an example we can use ‘ot1’ alias for the original-table1 and ot2 for the original-table2;


e.g.
mysql> create table table-name select ot1.c as col1, ot2.c as col2 from original-table1, original-table2;

You have to remember that when copying the tables using select statement, the attributes such as indexes will not be copied to the new table. Further more the default values also will get lost. However you can specify new attributes to the columns using the cast(), function.
e.g. Consider a table named tab1 which has three columns named a, b, c. You can give types for those columns when they are selected using cast( ) keyword.


mysql> Create table tab1 select
Cast (a as unsigned),
Cast(b as binary),
Cast(c as date);

The only cast types that are allowed are,
BINARY (binary string), DATE, DATETIME, TIME, SIGNED, SIGNED INTEGER, UNSIGNED, and UNSIGNED INTEGER.
Back to the top

Merging Tables

You can merge tables in MyISAM engine using the Merge engine.
Suppose that you have a set of table that indicate the student entries in a particular year. Then there will be separate tables for the years 200, 2001, …. You can merge all those tables to come up with a single table as described below.
e.g. student entry table for 2000,


mysql> create table student-entry-2000
(name varchar,
year int,
age int) engine = MyISAM;
You can merger the tables as follows,
create table all-entries(
name varchar,
year int,
age int)engine = Merge union = (student-entry-2000,student-entry-2001,..) ;

This merged table works in the same manner as a normal table for deletion and updating. But when inserting values it should be specifically stated to which table that value should be inserted. For that purpose you can use a special method called ‘Insert-method’ The values used with this method are no , first, or last.


e.g.
mysql> create table all-entries(
name varchar,
year int,
age int)engine = Merge union = (student-entry-2000,student-entry-2001,..)
insert-method = last ;

This states that the new values entered to the table should be written to the last table.
Back to the top

Federated Tables

These table are use din the Federate engine and they will allow you to access the data tables managed by other servers also.


e.g.
mysql> create table student(
name varchar ,
age int,
school varchar)engine = Innodb;

suppose that the database of this table is not in the local server but in other server called
abc.samser.net.
Then to access that table you have to create a federate table under federate engine and use a comment clause.


e.g.
mysql> create table fed-student(
name varchar ,
age int,
school varchar) engine = federated
comment='mysql://user-name:password@abs.samser.net/nir/student';

The user-name indicates the user name given for the database and he password is for the password field in the database.
Back to the top

Dropping the Tables

You can drop the tables using the following statement,


mysql> drop table table-name;

You can drop multiple tables simultaneously
drop table table1,table2,…;
Back to the top

Retrieving Information about Databases and Tables

This section deals with the retrieval of information about the databases and the tables. To view all the databases you can use the following command,


show databases;

This will list all the databases managed by the server.
To view the create database query written to create the database you can type,


mysql> show create database database-name;

To view the tables in the current database use


mysql> show tables;

statement.
If you want to view the tables in another database type,


show tables from database-name;

You have to note that these two types of statements don’t display the temporary tables. As same as for the databases you can view the create table statements written for a particular table using the query,


show create table table-name;

To view the information of the columns of the table use the statement,


mysql> show columns from table-name;

The same thing can be done by the following two statements also.


mysql> describe table-name;
explain table-name;

To view the information about the indexes used in the table use the following syntax.


mysql> show index from table-name;

You can view the status of the tables using the following commands,


mysql> show table status;
mysql> show table status from database-name;

The difference between these two statements is that the second statement gives the details of the tables in a given database while the first one gives the information about the tables in the current database.
The show statement takes the clauses such as like pattern which allows to search only for the tables with the given pattern.
Following example illustrates the use of the ‘like’ clause in the ‘show table’ statement.


mysql> show tables like ‘sch%’;

This query will list all the tables in the current database whose name start with ‘sch’.
To match the special characters like ‘_’ which have a special meaning in Mysql you have to use backslash character before the character to be compared.


e.g.
mysql> show tables like ‘/_name’;

Commands That Can be Executed in the Command Prompt
The following commands can be executed in the command prompt to gain the same results.


%mysqlshow

This will list all the databases managed by the server.
If you want to view the tables in a database you have to specify


%mysqlshow database-name

If you want to get the information about the columns in a table use the following command,


%mysqlshow database-name table-name;

To view the information about the keys of a table use the following command,


%mysqlshow –keys database-name table-name;

To display the status of the database you have to type,


%mysqlshow status database-name;

Viewing the Structure of the Tables

You can view the structure of the tables using %mysqldump in the form of create table statements. When using this command use - -no-data option with it so that the table data are not mixed with it.
The format of the query is given below,


%mysqldump - -no-data database-name [table-name] ….;

You can use only the database name for this query. Then the information of all the tables will be prompted. But if you specify the table names then the information about those tables only will be given.
If you want to check the existence of a table while staying inside an application you can simply execute a query. If the query succeeds it implies that the tables is existing. If the query fails then the table does not exist. For an example consider the following query,


mysql> select count(*) from schools;

To determine the storage engine of the table you should execute either,


mysql> Show table status;

or


mysql> show create table;

Back to the top

Joins
Joins are used to retrieve values from combining several tables. This part will give information on the various joins that are used for the queries.
Back to the top

Trivial Join
This is the simplest form of a join and it takes only one table as the parameter and selects the values from the mentioned table.


e.g.
mysql> select * from table1;

Back to the top

Cross Join
This join will simply combine the rows of the first mentioned table with all the rows mentioned secondly.
You can simply specify the cross join in the following manner,


mysql> Select table1.*, table2.* from table1,table2;

Here the all rows in the second table will be combined with each row in the first table to form the answer. If you add a where clause to the above join then a special name called ‘Equi-join’ is given to that query because then only the rows which satisfy the condition will be selected.


mysql> Select table1.* ,table2.* from table1,table2 where table1.name=table2.names;

There are many other join types which are similar to cross join type. They are mentioned below.
Inner join
Cross join
Join.
These three joins except the comma operator allows you to use different syntax to get the same output.
One such way is using the ‘On’ clause rather than the ‘where’ clause.


e.g.
mysql> Select table1.* ,table2.* from table1 inner join table2 on table1.name=table2.names;

‘On’ can be used regardless whether the columns have the same names.
The other method is ‘Using( )’ clause. Although this is same as the ‘on’ clause here the names of the columns in the tables have to be the same.


mysql> Select table1.* ,table2.* from table1 inner join table2 using(column-name);

Here the rows with the same value will be extracted.
Back to the top

Left Joins and Right Joins

The functionality of these two joins are almost the same. For the convenience consider only the left join.
Left Join
When left join is used all along with the matching rows the non matching rows in the table that is mentioned in the left to the join will also will be displayed.
The only difference of the right join is the role of the two tables is interchanged. The following statement illustrates the format of writing the left joins and the right joins.


mysql> Select table1.*, table2.* from table1 left join table2 on table1.name=table2.names;

This will give all the rows of the table1 and the matching rows of table2 as the output.
Back to the top

Usage of Sub Queries

Sub query is a technique of writing a query inside a query in nested pattern. As an example consider the following query,


mysql> Select * from schools where schools.name in( select school from principles);

This query will print all the schools that have a principle.
Normally there is no limit for the nesting purpose. Sub queries return different kinds of results. They are listed below.
A scalar sub query returns a single value.
A column sub query returns a single column of one or more values.
A row sub query returns a single row of one or more values.
A table sub query returns a table of one or more rows of one or more columns.
These subqueries can be evaluated in different ways. Those methods are discussed below.
Using Comparison Operators
=, <>, >, >=, <, and <= are the comparison operators that are used. The statement below is an example of the usage of the operators. Consider two tables which have the examination marks for two different grades and the student names and the student name. If you want to retrieve the information from subject1 table constrained by that the average of the marks should be greater than the average marks for the next subject, you can do it in the following manner.


Select * from subject1 where avg(marks)>(select avg(marks) from subject2);

The meanings of the operators are listed below.
= Checks whether the two values are equal
<> Checks whether the two values are not equal
> Checks whether the left hand value is greater than the right hand value
>= Checks whether the left hand value is greater than or equal to the right hand value
< Checks whether the left hand value is lesser than the right hand value <= Checks whether the left hand value is less than or equal to the right hand value. Back to the top

In and Not In

When ‘in’ is used it gives only the rows that carry the same values as the values resulted from the subquery.
Not In does the reverse of this. It results only the rows that do not have the same values as the subquery outputs.
mysql> Select * from schools where schools.name in( select school from principles);
All, Any and Some
These are used along with the comparison operators. As an example consider the following statement.
mysql> Select * from grade student-marks where marks


mysql> Select * from grade student-marks where marks

On he other hand this will return all the rows instead of the maximum value because every mark is less than some other value except for the maximum value.
Back to the top

Exists and Not Exists

These are used to check whether the query is returning any values. If the query is not outputting any value this will return 0, otherwise it will return 1. An example is given below.


mysql> Select exists (select * from student-marks where marks>90);

Correlation of Subqueries
Subqueries can be either correlated or not correlated. If the subquery is uncorrelated the it can be executed as a separate query. Consider the following query,


mysql> Select a from tab1 where a in (select b from tab2);

This is an uncorrelated subquery because it can be executed as a different query.


mysql> select j from t2 where (select i from t1 where i = j);

In this case since the subquery cannot be executed as a different query it is correlated with the first one.
Subqueries in the From Clause
Subqueries in the from clause acts as tables and they can participate in joins etc.
An important thing you have to remember is that the results obtained here can be obtained with the joins also.
Union
Union is used when you want to combine the outputs of more than one query. The column names of the resultant table will be the column names of the table in the first select statement. The names and the data types of the second table don not have to be the same as in the first table. However the number of columns selected in both tables has to be the same.


mysql> Select a,b from table1 union select c,d from table2;

Here the resultant table’s columns will have the names a and b.
Union eliminates the duplicated values by default. Hence union distinct is same as the union. To keep the duplicated values type union all. To sort the values place the statements in parenthesis and use order by clause at the end.
Limit is another option that can be used with the UNION operator. If the select statements are written in parenthesis and the limit is used at the end of he statement then the limit applies to the whole query. If it is used for a particular query it will affect only the table written in that query.
Back to the top

Multiple table Deletion and Updating

In this section it will be described how to delete and update the values in a particular table based on the values of another table. To delete the values from a table based on the values of another table you have to specify both tables in the where clause. An example is given below.
Delete value from table1 ,table2 where table1.value=table2.value;
This allows you to delete columns from multiple tables at once. For an example consider the following statement.
Delete table1,table2 from table1,table2 where table1.value=table2.value;
To delete the non matching values you can simply use left joins or right joins.


mysql> Delete table1 from table1 left join table2 on table1.value=table2.value where table2.value is null;

You can delete the values using the ‘using’ clause also. There you have to specify the tables from which you have to delete the values inside the ‘from’ clause while the tables which make the comparison in the ‘using’ clause.


mysql> Delete table1 using table1,table2 where tanle1.value=table2.value;

Multiple updation is quite similar to the deletion. An example is given below.


Update table1, table2 set table1.value=table2.value where table1.id=table2.id;

If you have foreign keys on the tables then you have to use the clauses such as ‘on delete cascade’ etc.
Back to the top

Transactions in Mysql

When dealing with transactions you have to preserve the ACID properties. They are briefly described below.
A-Atomic – The transaction has to either be fully completed or nothing completed. You cannot have partially completed transactions.
C-Consistency – After doing a certain transaction the database should be in a consistence state.
I-Isolation- Transactions should perform in isolation. That is one transaction can have no effect on another transaction.
D-Durability- When a transaction is performed its effects are recoded permanently in the database.

In Mysql these properties are preserver by the commit and roll back clauses. If a transaction is completed up to the end it is committed and the effect is stored in the database permanently. If it is not fully completed then the transaction is rolled back and no changes will be done to the database.

By default Mysql commits the transactions as soon as a single query is executed. That is the query itself behaves as a transaction. To explicitly make a transaction first you have to disable the auto-commit property, and then place the statements of the transaction inside the ‘start transaction’ and ‘commit’. To disable the auto commit property type as follows,


Set autocommit=0;

An example is given below.


Start transaction;
Update students set school=’Nalanda’ where name=’A’;
Update students set grade=13 where name=’A’;
Commit;

If an error occurs during the transaction you have to roll back the transaction using the
‘rollback;’ clause.
Then all the old values of the database will remain the same and no change will be done.

To enable the auto commit property again set the value as,


Set autocommit =1;

If you enter the following command to the server while a transaction is progressing the server will commit the transaction first and then will execute the new sql statement.
ALTER TABLE
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
LOAD MASTER DATA
LOCK TABLES
RENAME TABLE
TRUNCATE TABLE
UNLOCK TABLES
Back to the top

Transaction Save Points

Some times it may be uneconomical to roll back the whole transaction. In those cases you can have a save point in the transaction and only roll back up to that point. For that use the ‘savepoint’ keyword. You can use that in the following manner.
Savepoint savepointname;
Back to the top

Transaction Isolation

There occurs several problems when isolation is not preserved. Some of them are described below.
Dirty Reads This happens when another user reads the values of the changed column before committing the transaction. For an example a user may read the value of a particular field and think that it is the final value. But at the end the transaction may roll up and changes also will be disappeared while the above user is thinking that he has the correct value.

Nonrepeatable Reads This occurs when a user is unable to retrieve the same value for two consecutive select statements.

Phantom Rows A phantom row is a row that suddenly becomes visible to a transaction although it was not visible earlier. This happens when a transaction is selecting rows while another transaction inserts rows.

To deal with these problems Mysql provides isolation levels. They specify which modifications made by a certain transaction can be seen by the other transactions.
READ UNCOMMITTED
A transaction can see row modifications made by other transactions even before they have been committed.
READ COMMITTED
A transaction can see row modifications made by other transactions only if they were committed before the transaction began.
REPEATABLE READ
If a transaction performs a given SELECT twice, the result is repeatable. That is, it gets the same result each time, even if other transactions have changed or inserted rows in the meantime.
SERIALIZABLE
This isolation level is similar to REPEATABLE READ but isolates transactions more completely: Rows examined by one transaction cannot be modified by other transactions until the first transaction completes. This allows one transaction to read rows and at the same time prevent them from being modified by other transactions until it is done with them.
Dealing with Transactional Process Without the Concept of Transaction
There are several ways of dealing with transactional features without using the actual concept. Some of them are described below.

Using Locks
There are two types of locks that can be attained for the tables when you are either reading or writing to the table. They are shared lock and

exclusive lock.
You must get an exclusive lock before you write something to the table. When you obtain the lock to the table no other user can either read or write to the table until you release the lock. (i.e. Only the lock holder can work with the table until thy lock is released).

If you want to read the table you have to get a shared lock to the table. Many shared locks can be obtained for a single table simultaneously. An important thing you have to remember is that no one can write to the table while someone is having a shared lock on the table, but any number of users can obtain the shared locks and read the table while you are reading the table.

These two locks are used to avoid dirty reads, nonrepeatable reads and phantom rows. The way of attaining the locks are described below.


lock tables table-name write;
//sql statements to write to the table
unlock tables;

To obtain the shared locks the structure is the same. The only difference occurs at the place where you have to specify what you need to do with the table.


e.g. lock tables table-name read;

Back to the top

Relative Updates Instead of Absolute Updates

You can use relative updates to change the database values. Relative Updates refers to the method of writing a single sql statement for updation instead of writing several statements. An example is illustrated below.

Consider that you are handling an inventory database and you release 3 items in at three instances. If you are using absolute updates you have to write three update statements for each updation. But if you are using relative updation you have to write only one sql statement which updates the table by three at once. This method avoids the inconsistencies that will occur when writing three update statements. But you have to remember that not all the updations can be done using this relative updating methodology.
Back to the top

Foreign Keys and Referential Integrity.

Foreign keys are used to denote the relationship between two tables. Hence by using foreign keys you can link two tables. Normally the foreign key in one table is the primary key of the other table which is participating in the relationship.

If the two tables are in a one-one relationship either table can keep the foreign key. If they are in a one-many relationship the many side table will have to keep the foreign key. If the relationship is many-many then both tables have to keep the foreign keys.

There are special mechanisms to deal with the deletions when you are using the foreign keys. For an example you can instruct the sql server to delete the corresponding rows in the other table when you are deleting the rows from one row who is linked to the second table via a foreign key.

e.g. Suppose that the school attribute is the foreign key in the principle table which refers to the schools table. You can instruct the server to delete the principle automatically when you delete school although they are in two different tables. Such deletions are called cascade deletions.

You can instruct the server not to allow the deletions of the rows that are if they are referred from a foreign key

The structure of writing the foreign key constraints is given below.

constraint constraint-name
foreign key column-name references table-name primary-key
on delete action
on update action

The following parts in this statement are optional.
constraint constraint-name – This is used to give a name to the constraint. If you don’t specify a name the server will just refer to this constraint as a foreign key constraint. Hence if an error occurs it will be difficult to identify in which foreign key constraint it has occurred.
on delete action
on update action – These two are used to specify what are the actions to be taken when deleting or updating the rows which are referred to by the foreign keys. If these are not specified the default actions will be taken.

The options that are used with the on delete clause are listed below.

on delete cascade – This will make the referencing rows in the other table to be deleted when the referred rows are deleted

on delete set null – This will make the referencing rows to be set to null when the referred rows are deleted.

on delete no action, on delete restrict - these are the same as omitting the on delete clause.

Foreign key is the key word that is use to specify the column that will be used as the foreign key. references is the key word that is used to indicate the table and the column that is being referred.
An important thing to remember is that you must create and insert the values to the table that is being referred before creating the table that is referencing. If you try to insert a value to the referencing table that has no matching values in the referred table sql server will prompt an error.
Back to the top

Fulltext Searches

This option allows you to look for words without the usage of pattern matching operators. There are three types of fulltext searches.
Back to the top

Natural Language Searching
Here the phrase is parsed into words and it will search for the words with the matching words.
Back to the top

Boolean Mode Searching
Here also the phrase is parsed into words. But here you are allowed to modify the word using the operators such that you can indicate whether to check that the word is available in the record or not etc.
Back to the top

Query Expansion Searching
This is done in two phases. First a natural language search is done. Then the records of the first search are concatenated with the original string.

These fulltext searches are based on fulltext indexes. But these indexes can be built only in MyISAM tables. These indexes can be only of the types char, varchar and text. The words that are in almost half of the tables are ignored in the fulltext search and the words such as ‘the’ , ‘an’ etc and the words with less than four characters are always ignored.

After creating the tables and the indexes you can use those to search the words. The syntax is given below.


mysql> Select * from table-name where match (column-name) against (word-to-be-found);

Things to Consider for Boolean Mode Searches

When using Boolean mode searching the 50% rule is ignored. An example is illustrated below.


mysql> Select * from table-name where match (attribute-name) against (‘+computer –book’ in Boolean mode );

This will result all the phrases that contain computer but not book.

Consider the following example


mysql> Select * from table-name where match (attribute-name) against (‘common*’ in Boolean mode);

This will match all the words which are starting with common such as commonly, etc.
If you want to search for the entire phrase in the exact order you have to use the double quotes instead of the single quotes.
Back to the top

Query Expansion Fulltext Search
This searching is done in two phases. First the natural search is done and the results are combined with the original phrase to be checked.
Back to the top

New Features in Mysql

The following features are the new contributions of Mysql
Views
Stored Procedures and Functions
Triggers

Views
Views are not actual base tables. They are used to restrict the users accessing the tables that are not allowed to be accessed by them. Normally views are not stored in the database. Only the structure of the table is stored. When a user query from a view only it is created. Following examples illustrates how to create and maintain the views.
Create view view-principles as select name, school from principles;
The end user is unaware of whether he is using a base table or a view in his query. When he query the view it will be created with the attributes name and the school. The end user feels that those are the only attributes of a principle. He can query from this view as with a base table.
You can rename the attributes of the view in the following manner when you are creating it.
Create view view-principle (principle-name, working-school) as select name, school from principles;

A view can be created using multiple tables also. An example is given below,
Create view view-school as select student.name, school.name from schools, students where school.name=student.school;
By using the updatable views you can update, delete values from the underlying base table.
Back to the top

Stored Procedures

These are the functions and procedures that are stored in the server side. They can be invoked as normal built in functions. However they do not return the values directly. Stored procedures are important for the following functionalities.

They allow using concepts like looping and branching.
They allow using error handling mechanisms.
Since they are stored in the server side it has to be sent only once via the network and hence it reduces the overhead.
These stored procedures can be stored as libraries so that many applications can use them.

You can create a stored function using the keyword ‘create function’ or ‘create procedure’. The following section illustrates a sample stored procedure.


delimiter $
create function average-marks (int m1,int m2)
returns int
begin
declare average-result;
average-result=(m1+m2)/2;
return average-result;
end
delimiter ;

You have to insert the body of the procedure in ‘begin’ and ‘end’ clauses. A function must have a return statement to return the result to the caller. You can call this function in the following manner.


Select name, average-marks(maths, Sinhala) from students;

The use of delimiter is to avoid the conflict of interpreting the ‘;’ as a normal statement terminator. But if the procedure has only one statement there exists no need of this keyword.

The difference between a stored procedure is that it doesn’t has a return value. Only the functions have return values.

The parameters passed to the function can be of IN type or OUT type. IN type parameters takes values in to the function while the OUT type parameters takes the values out from the function. This feature is important when you want to return several values from a function. Then you can use OUT type parameters to output them as a return statement can output only one value.
Back to the top

Triggers
Unlike stored procedures or functions triggers are invoked automatically when an insertion, updation or deletion is done. They check whether the specified rules are violated by the taken action.
Using a BEFORE trigger you can examine the values before they are being inserted to the database.
An example trigger is given below.


delimiter $
create trigger t_ins before insert on t
for each row begin
set new.dt = current_timestamp;
if new.i < i =" 0;">

Back to the top

Data Value in Mysql

Mainly there are five categories of the data values in Mysql. They are numerical values, String values, spatial values, temporal values and null values. Brief Description of those values is given in the following section.
Back to the top

Numerical Values

These values include integer values with no fractional part and floating point values which has a fractional part. An integer can be represented in decimal format or in hexadecimal format. For the floating point numbers you can use the normal dotted decimal format or scientific notation. For an example Mysql understands the following statement as a valid floating point number. 1.23e-1. The exponential can be denoted in either case.(i.e. E or e).
Back to the top

String Values

String values in Mysql have to be encloses in quotes. For an example ‘1234’ will be a string in Mysql because it is written inside the quotes. You can use either single or double quotes for the strings. But the most preferred way is using the single quotes rather than the double quotes. There are two main reasons for this. First one is that the single quotes is the standard. Hence it increases the portability of the database from one engine to another engine. The second reason is that if ANSI-QUOTES mode is enabled the string values enclosed with double quotes will be treated as table names or as database names.

There are several special characters that have a different meaning from than the actual meaning. These characters should be included after the escape character ‘/’. ‘/’ indicates a temporary escape from the actual meaning of the character. These characters are listed below.

Sequence Meaning
\0 NUL (zero-valued byte)
\' Single quote
\" Double quote
\b Backspace
\n Newline (linefeed)
\r Carriage return
\t Tab
\\ Single backslash
\Z Ctrl-Z (Windows EOF character)

These characters are case sensitive. If you want to use the quote as a part of the sentence while the sentence is enclosed with the quotes simply double it.


e.g. ‘it’’s my book’
or
“ The teacher said “”take your books out”””

If the quotes you want to use are different from the quotes that are used to enclose the string then you need not to double the quotes.


e.g. “ I’ll go there”

If you want to treat the escape character as a normal character you have to enable the
NO_BACKSLASH_ESCAPES sql mode.
Back to the top

Binary and Non Binary Strings

Strings are categorized in to two types. They are binary and non binary strings. Binary strings is a sequence of bytes. They have no special comparison or sorting properties. Comparisons are done by byte by byte depending on the numerical value of the byte. Non binary set is a sequence of characters associated with a particular character set.

The server maintains several system variables for the character attributes. Usually it has three variables for the character type and three for the collation. The collation variables are associated with the character variables.
Back to the top

Date and Time Values

These values refer to the date and time variables. An interesting thing to note here is that the date is representing in the year-month-date format. You can change this default format using the DATE-FORMAT( ) function
Back to the top

Spatial Values
These are used to represent the values such as points, lines and polygons. An example which uses the text format of a point is given in the following example.


Set @pointrep = pointfromtext(‘point ( 20, 30) ‘);

Back to the top

Null Values

Null values do not have a type. You have to understand that null is not the same as zero. Normally null indicates that there is no value. You cannot perform calculation for the null values. To check whether a particular value is null or not you have to use IS NULL or IS NOT NULL.
Back to the top

Data Types in Mysql

For the above mentioned data values you can use several data types. These data types indicate the properties such as the number of bytes taken to store the value and how those values are handled by Mysql. The data types for each data value mentioned in the earlier part will be described below.
Back to the top

Data Types for Numerical Values.

Type Name Meaning
TINYINT A very small integer
SMALLINT A small integer
MEDIUMINT A medium-sized integer
INT A standard integer
BIGINT A large integer
FLOAT A single-precision floating-point number
DOUBLE A double-precision floating-point number
DECIMAL A fixed-point number
BIT A bit field
Back to the top

Data Types for String Values.

Type Name Meaning
CHAR A fixed-length non-binary string
VARCHAR A variable-length non-binary string
BINARY A fixed-length binary string
VARBINARY A variable-length binary string
TINYBLOB A very small BLOB (binary large object)
BLOB A small BLOB
MEDIUMBLOB A medium-sized BLOB
LONGBLOB A large BLOB
TINYTEXT A very small non-binary string
TEXT A small non-binary string
MEDIUMTEXT A medium-sized non-binary string
LONGTEXT A large non-binary string
ENUM An enumeration; each column value may be assigned one enumeration member
SET A set; each column value may be assigned zero or more set members
Back to the top

Data Types for Date and Time Values.

Type Name Meaning
DATE A date value, in 'CCYY-MM-DD' format
TIME A time value, in 'hh:mm:ss' format
DATETIME A date and time value, in 'CCYY-MM-DD hh:mm:ss' format
TIMESTAMP A timestamp value, in 'CCYY-MM-DD hh:mm:ss' format
YEAR A year value, in CCYY format
Back to the top

Data Types for Spatial Values.

Type Name Meaning
GEOMETRY A spatial value of any type
POINT A point (a pair of X,Y coordinates)
LINESTRING A curve (one or more POINT values)
POLYGON A polygon
GEOMETRYCOLLECTION A collection of GEOMETRY values
MULTILINESTRING A collection of LINESTRING values
MULTIPOINT A collection of POINT values
MULTIPOLYGON A collection of POLYGON values

You can specify the column type when you are creating the table.


e.g.
create table table-name (field-name type, ….);

You can specify the default value for the column using the default keyword.


e.g.
create table table-name (field type default default-value, …);

If the default value is not explicitly specified the following values will be used for each data type.

Numerical values – 0
String values – null
Date and Time Values – 0000-00-00
Back to the top

Numerical Data Types

Following table gives the ranges of the data types.
Type Specification Range
TINYINT[(M)] Signed values: 128 to 127 (27 to 271); Unsigned values: 0 to 255 (0 to 281)
SMALLINT[(M)] Signed values: 32768 to 32767 (215 to 2151); Unsigned values: 0 to 65535 (0 to 2161)
MEDIUMINT[(M)] Signed values: 8388608 to 8388607 (223 to 2231); Unsigned values: 0 to 16777215 (0 to 2241)
INT[(M)] Signed values: 2147683648 to 2147483647 (231 to 2311); Unsigned values: 0 to 4294967295 (0 to 2321)
BIGINT[(M)] Signed values: 9223372036854775808 to 9223372036854775807 (263 to 2631); Unsigned values: 0 to 18446744073709551615 (0 to 2641)
FLOAT[(M,D)] Minimum non-zero values: ±1.175494351E38; Maximum non-zero values: ±3.402823466E+38
DOUBLE[(M,D)] Minimum non-zero values: ±2.2250738585072014E308; Maximum non-zero values: ±1.7976931348623157E+308
DECIMAL([M[,D]]) Varies; range depends on M and D
BIT[(M)] 0 to 2M1

The following table gives the storage requirements of the data types.

Type Specification Storage Required
TINYINT[(M)] 1 byte
SMALLINT[(M)] 2 bytes
MEDIUMINT[(M)] 3 bytes
INT[(M)] 4 bytes
BIGINT[(M)] 8 bytes
FLOAT[(M,D)] 4 bytes
DOUBLE[(M,D)] 8 bytes
DECIMAL([M[,D]]) M+2 bytes
BIT[(M)] Varies depending on M
Back to the top

Integer Data Type

TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT are the integer type data types. They differ from each other depending on the values that can be represented using the type and the storage requirements. You can explicitly state the display size of the data types. For an example if you use int (2) the display size will be of two characters. However there is a different in the usage of that pattern when it is used with the floating point numbers. Float (x) indicates the precision you want to use for the data. X can vary from 0 to53. If x lies between 0 and 24 then the value will be of single precision and if it is in between 25 and 53 it is double precision. If you haven’t specified the size the default length will be used. The default length is the maximum length of that data type.
Back to the top

Floating Point and Fixed Point Data Types

There are two floating point type and one fixed point type.
Floating Point Types – float
Double
Back to the top

Fixed Point Type –Decimal

These are used to store the values with decimal places. Floating point and Fixed point values also can be defined as unsigned and it will simply eliminate the negative part of the value. A fixed type differs from float or double from the facts that is stored as strings and have a fixed number of decimals. The fact you have to remember is that fixed points are less efficient than floats or doubles. Decimal data type is treated in the same way as in normal sql. But it represents only the unsigned values. In normal sql if you specify decimal (4,2) it should have four numbers in which two are decimal values. Since the sign and the decimal point also have to be stored it will require six bytes altogether. Since Mysql doesn’t has a sign that byte is used to increase the range. For an example if you use float(4,2) in Mysql the maximum number you can store is 999.99 where as it is 99.99 in standard sql.
Back to the top

Bit Data Type

This is used to store the bit values. If you specify a number along with the bit type that indicates the number of bit type values you can have in that field. For an example if you specify Bit(3) that will indicate that you can use three bit type values in that column.
The values of bit type are not printable by default. To make them printable add to zero or use the bin ( ) function.
e.g. select bin(attribute) from table table-name;
Back to the top

Attributes of Numerical Data Types

Sign can be considered as the main attribute of this data type. There can be signed and unsigned values. In unsigned values only the positive values are allowed. But in signed values both negative and positive values are allowed.

Zerofill attribute can be used for every numerical data type other than the ‘ bit ‘ data type. It makes all the columns to be padded with zeros at the beginning. If you use this attribute to a column it automatically become unsigned.
e.g. create table table-name (attribute int zerofill);

The next attribute is auto_increment. This is used only with the integer data type. The value in this column will be increased automatically when a new row is inserted. The column have to be unsigned. You can use this attribute for the columns like primary key, etc.


e.g. mysql> e.g. create table table-name (attribute int unsigned auto_increment primary key);

Mysql adds not null constraint to this column automatically.
Back to the top

String Data Types

Type Specification Maximum Size
BINARY[(M)] M bytes
VARBINARY(M) M bytes
CHAR[(M)] M characters
VARCHAR(M) M characters
TINYBLOB 281 bytes
BLOB 2161 bytes
MEDIUMBLOB 2241 bytes
LONGBLOB 2321 bytes
TINYTEXT 281 characters
TEXT 2161 characters
MEDIUMTEXT 2241 characters
LONGTEXT 2321 characters
ENUM('value1','value2',...) 65,535 members
SET('value1','value2',...) 64 members
Back to the top

Char and Varchar Data Types

They are non binary data types and have character sets and collations. The main difference between char and varchar is that if you use char type the number of bytes you specify will be allocated regardless of whether your phrase has the specified number of characters (i.e. char (15) will allocate spaces for 15 characters). But if you use varchar the number you specify will act as an upper limit and if your phrase hasn’t that number of characters it will automatically deallocate the spaces. Important thing to remember is that you cannot convert a char variable to varchar. But you can do the reverse. BLOB and TEXT are also variable type characters.

An important thing to remember is that if you have both char type and varchar type in the same column Mysql automatically convert the char column to varchar. If you have only char columns then such a conversion will not be done. For an example if you create a table in the following manner


Create table table-name (field1 char(10), field2 varchar(10));

then filed1 will automatically be converted into varchar(10). This is something that is not done by the other DBMS’s.

If you modify the table in the following manner,


Alter table table-name modify field1 char(10), modify field2 char(10);

then such a conversion will not be done. This same rule applies when you use BLOB and TEXT type variable with char variables because they are also variable type characters. But if the char column is shorter than four characters then such a conversion will not be done.

For an example consider the field of char(2) type. Even if it is included in a table which contain variable type characters it won’t be converted in to varchar type. Instead if you have varchar type characters which are shorter than four characters then Mysql will convert them in to char type characters. This is done to save the storage used to store the length of the phrase when you use varchar.

For an example field1 varchar(2) will be converted to char(4). But a variable of varchar(4) will remain as it is.
Back to the top

Binary and Varbinary Data Types

These are the same as char and varchar data types. In binary type variables if the number of bytes you specify will be allocated regardless of the number of characters you have in the variable. But in varbinary data type the unused spaces will be deallocated. The automatic type conversion described for char and varchar applies here also.
Back to the top

The BLOB and TEXT Data Types.

BLOB stands for Binary Large Object. It is a container that holds any thing you want to store. There are four types of BLOBS. They are tinyblob, blob, mediumblob and longblob. They vary according to the storage capacity. Blob columns are used to store the binary strings whose size may vary from row to row and is quite large in size. They may store encrypted data, images, sounds, etc.

Text type is very much similar to blob type. The only difference is that they store non-binary strings rather than binary-strings. There also you have four types of text types. They are tinytext, text, mediumtext and longtext.

Depending on the storage engine you are using you can index these columns. For an example MyISAM, InnoDB, and BDB support indexing these columns.
Back to the top

The ENUM and SET Data Types

These are special type of data types which hold string values. Here you are allowed to specify the group of values that are accessible when using these two data types. As an example consider the following statements.


Colour enum (red, blue, green, yellow);

Here ‘Colour’ is the name of the column. But the values that can be stored in colour column can only be red or blue or green or yellow. Actually enum and set are the methodologies that allow you to define your own data type. One of the differences between enum and set is that when using set methodology any subset of the specified values can be used at a time, whereas in enum only one value out of the specified values can be used at a time.

Enum referrers to enumerator. Enum can be used when you need mutual exclusion. Normally enum and set are considered as string types. This is because the values you give are of string types. But internally these values are stored as numbers and you can do numerical operations to these values. The enum members are numbered sequentially starting from 1. Number 0 is reserved for the error member. This numbering is done to determine the storage capacity needed. For numbering two bytes are given. Hence you can have 216 that is 65536 members in an enum.

You can compare the enum members either by name or value.


e.g.
mysql> select name from table-name where name=’Ama’;

the same thing can be done in the following manner. Assume that the 4 is the number of the value ‘Ama’. Then you can select it ,


mysql> select name from table-name where name=4;

Here the name is the enum column.

You can assign empty string also as an enumerator. But this causes confusion between the error member used and the member you assign, because the error member also has an empty string. The only way to avoid this confusion is to obtain the value by the number assigned.

Another difference between set and enum is that set values are not numbered like enum values. Instead they correspond to the successive bit values. The first member corresponds to the bit 0, second member to the bit 1 and so on. Hence there can be eight members per one byte. Hence the storage size is determine by the number of set members. Set values take 1,2,4 or 8 bytes to store the set sizes.

The main advantage of using these bit values to represent the set members is that then any combination of the values can be obtained.

As an example consider the following table.


mysql> Create table furniture (f set(‘chair’, ‘table’, ‘stool’, ‘cupboard’));

mysql> Insert into furniture values (‘chair’),(‘stool’),(‘table,stool’),(‘chair,cupboard’);

This will insert four rows to the table.

Then if we execute the query,


mysql> Select f, f+0, bin(f+0) from furniture;
+----------------+------+----------+
| f | f+0 | bin(f+0) |
+---------------- +------+----------+
| chair | 1 | 1 |
| stool | 4 | 100 |
| table,stool | 6 | 110 |
| chair,cupboard | 9 | 1001 |
+---------------- +------ +----------+

Here the bin(f+0) gives the binary format of the index. For an example in the number of the chair,cupboard row is 9 and its binary representation is 1001.

If you assign the same value for a row (i.e. stool, stool) it will be simply stores as stool, and if you try to insert a value that is not a member in the defined list then an error will be prompted. You have to specify the values to be retrieved in the same order as they are inserted. Otherwise it will give an error saying that such a row does not exist.

If you want to convert an enum column to a normal char column it can be done using the cast( ) function. An example is given below.


mysql> Select cast( enum-column as char) as usual-col from enumerator-table;

Back to the top

String Data Type Attributes

Character set and the collation can be considered as the attributes of the string data type. They are applied to the char, varchar, text, enum and set data types. However they do not apply for the binary, varbinary and blob data types. You can specify the character set and the collation of a column as you define it.


e.g.
create table table-name (field1 char(20) character set latin1 collate latin1_german1_ci,…);

If the specified character set is as same as the default character set then show create table statement will not show the character set and the collation. To view that you have to use


mysql> Show full columns from table-name;

Back to the top

Date and Time Data Types

Type Specification Range
DATE '1000-01-01' to '9999-12-31'
TIME '-838:59:59' to '838:59:59'
DATETIME '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP '1970-01-01 00:00:00' to partially through the year 2037
YEAR[(M)] 1901 to 2155 for YEAR(4), and 1970 to 2069 for YEAR(2)
Storage Requirements

Type Specification Storage Required
DATE 3 bytes
TIME 3 bytes
DATETIME 8 bytes
TIMESTAMP 4 bytes
YEAR 1 byte

If you want to specify the date and time together you have to separate them using the T character rather than the space character.
Back to the top

Date, Time and Datetime data types

The formats used to store the date and time values are as follows.
Date- CCYY-MM-DD (CC- century, YY-year, MM-month, DD- date)
Time- HH:MM:SS (HH-hours, MM-Minutes, SS-seconds)
Datetime – CCYY-MM-DD HH:MM:SS

The time part in the datetime field can have values only from 00:00:00 to 23:59:59. But the time field can have negative values also.
Back to the top

Timestamp Data Type

This data type is used for storing both time and date. Timestamp values start from 1970-01-01 00:00:00 .These values are tied to the UNIX time and are stored in four byte numbers. These values are stored in the Universal Coordinated Time order. When you give the time the server will automatically convert it to the UTC format and when you are retrieving the values they will be converted in to the current time zone values. This Timestamp variables have automatic initializing and update properties. The automatic initialization gives the current time to a newly inserted row if you don’t specify the time values or set it to null. Automatic updation will set the values to the current timestamp when you access another column.

An important thing to remember is that only one column can have the automatic properties. The syntax needed to specify the timestamp values is as follows.
Assume that the column name is clo1.

col1 timestamp [default value] [on update current_timestamp]
Back to the top

Year Data Type

This is used to store the values of the year. The syntax of this is as follows. YEAR[m]. The ‘m’ specifies the year type you want to use. It can be either YEAR[2] or YEAR[4]. If you use year[2] values the range of years you can use is 1901 to 2115. If you use year[4] the range is 1970-2069. Year data type uses only one byte for the storage.
Back to the top

Handling Invalid Data Types

This section gives details about how Mysql handles invalid data type. For the time and numeric columns the values which are out of range will be truncated to the nearest value.

For string values other than ENUM and SET values the variable will ea adjusted to fit the maximum length of the column. For ENUM columns if you list the values that are not specified at the declaration an error will be prompted. If you try to use the values that are not specified at the declaration for SET values those values will be removed and only the valid variables will be stored.

For date and time columns the invalid value will be converted in the valid type and the value will be zero.

If you want to be more strict for checking for the data types you have to enable the following two modes.


mysql> SET sql_mode = 'STRICT_ALL_TABLES';
mysql> SET sql_mode = 'STRICT_TRANS_TABLES';

For transactional table both these modes are the same. If an invalid value is found an error will be prompted and the transaction will be rolled back. But foe non transactional table these two are different. If the error occurs at the first row the then the statement execution will be rolled back and no changes will be done. But if the error occurs after the first row then
for 'STRICT_ALL_TABLES' the modified tables will carry the modification and the rows are partially updated.
for 'STRICT_TRANS_TABLES' mode the changes already done cannot be changed. But the statement will be executed to avoid the partial updates.
Some additional modes are listed below.
• ERROR_FOR_DIVIDE_BY_ZERO causes errors for divide-by-zero operations.
• NO_ZERO_DATE prevents entry of the "zero" date value.
• NO_ZERO_IN_DATE prevents entry of incomplete date values that have a month or day part of zero.
If you want to enable the strict mode for the divide by zero error for all the tables then you have to specify,
SET sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVIDE_BY_ZERO';
To obtain the traditional constraints you have to invoke the traditional mode.


SET sql_mode = 'TRADITIONAL';

Back to the top

Working with Sequences in Mysql

Auto_increment

This is used by Mysql to increase the values of a numerical column sequentially. When using this option you have to remember that the values in the column cannot be negative or floating point type. Only one column in a table can have this attribute. That column must have the not null constraint. The value of the last generated number can be obtained from the LAST_INSERT_ID() function. This will return 0 if an auto_increment value has not been generated for the current connection. For multiple values that are generated as auto_increment values this function will return the first one.

Inserting zero for an auto_increment value is same as inserting null value. But if you enable the NO_AUTO_VALUE_ON_ZERO inserting 0 will be stored as 0 itself. If you specify a non-zero value for an auto increment column and if that value exists an error will be displayed. If the value you inserted is greater than the value that will be generated automatically then the following rows will be indexed from the values starting from the value you have specified.

Dealing with Auto_Increment Columns

For creating the table with auto_increment columns is as follows.


Create table table-name (field1 type1, …, aifield int not null auto_increment, ….);

If you already have an auto_inrement column and if you want to rearrange it to eliminate the gaps hat have occurred due to the deletion of some rows the easiest method is to drop the column and to redeclare it. Dropping an auto increment column and redeclaring it can be done in the following manner.


mysql> Alter table table-name drop column-name, add column-name not null auto_increment;

You can generate sequences without using the auto_increment option also. For that last_insert_id() function is used.
Back to the top

Expression Evaluation and Type Conversion

Expression are used in Mysql to generate values for the user. These expressions can be single constants, function calls etc. When writing function calls you must start the parenthesis just after the function name. If you keep a blank space between the function name and the parenthesis Mysql will consider the function name as a column name. By enabling IGNORE_SPACE mode you can allow to have the white spaces between the name and the parenthesis.

Expressions can refer to single or multiple tables or columns. When an expression is written for a single table no ambiguities arise between the column names. But if you are dealing with multiple table then you have to pay a special attention to the column names of each table because it is possible to have the same column name in both tables. Handling such situations is described in the following example.


mysql> Select school.name, principle.name from school, principle where school.name= principle.school;

Several operators are used for these expressions. Those operators and the use of them are listed below.
Back to the top

Arithmetic Operators

Operator Syntax Meaning
+ a + b Addition; sum of operands
- a - b Subtraction; difference of operands
- -a Unary minus; negation of operand
* a * b Multiplication; product of operands
/ a / b Division; quotient of operands
DIV a DIV b Division; integer quotient of operands
% a % b Modulo; remainder after division of operands
Back to the top

Logical Operators
Operator Syntax Meaning
AND, && a AND b, a && b Logical intersection; true if both operands are true
OR, || a OR b, a || b Logical union; true if either operand is true
XOR a XOR b Logical exclusive-OR; true if exactly one operand is true
NOT, ! NOT a, !a Logical negation; true if operand is false

An important thing to note is that although ‘||’ is used as the or operator in Mysql, in traditional it is used for string concatenation.
For string concatenation Mysql uses the function CONCAT(‘string1’, ‘string2’).
To treat that operator as in traditional sql enable PIPES_AS_CONCAT SQL mode.
Back to the top

Bit Operators
Operator Syntax Meaning
& a & b Bitwise AND (intersection); each bit of result is set if corresponding bits of both operands are set
| a | b Bitwise OR (union); each bit of result is set if corresponding bit of either operand is set
^ a ^ b Bitwise exclusive-OR; each bit of result is set only if exactly one corresponding bit of the operands is set
<< a <<>> a >> b Right shift of a by b bit positions

Bit operators are used to compare two operands bitwise. The corresponding bits in the result will be set or unset according to the value of the operands.
Back to the top

Comparison Operators
Operator Syntax Meaning
= a = b True if operands are equal
<=> a <=> b True if operands are equal (even if NULL)
<>, != a <> b, a != b True if operands are not equal
< a <>= a >= b True if a is greater than or equal to b
> a > b True if a is greater than b
IN a IN (b1, b2, ...) True if a is equal to any of b1, b2, ...
BETWEEN a BETWEEN b AND C True if a is between the values of b and c, inclusive
NOT BETWEEN a NOT BETWEEN b AND C True if a is not between the values of b and c, inclusive
LIKE a LIKE b SQL pattern match; true if a matches b
NOT LIKE a NOT LIKE b SQL pattern match; true if a does not match b
REGEXP a REGEXP b Regular expression match; true if a matches b
NOT REGEXP a NOT REGEXP b Regular expression match; true if a does not match b
IS NULL a IS NULL True if operand is NULL
IS NOT NULL a IS NOT NULL True if operand is not NULL

Some of these operators can be used with wild card characters. For an example if you check ‘Mother’ LIKE ‘Moth%’ it will return 1. But if you compare it with a null value the result will be null.
e.g. ‘Father’ LIKE NULL will result NULL
Unlike the traditional sql this LIKE operator is not case sensitive.
e.g. ‘MOTHER’ LIKE ‘mother’ will return 1

To match a single character ‘_’ operator is used.
e.g. ‘cat’ LIKE ‘c_t’ will return 1.

If you want to match those operators itself use the escape character.
e.g. ‘a%b’ LIKE ‘a\%b’ will return 1
Back to the top

REGEXP

This operator can be used instead of ‘LIKE’. ‘.’ is a wild card character that is used with this operator to match a single character.
e.g. ‘abc’ REGEXP ‘a.b’ will return 1.

If you want to check whether a letter appears in a particular set of letters you can specify that using the square brackets.

‘a’ REGEXP ‘[asdfg]’ will output 1.

You can specify the range of letters that have to be looked in to check whether the specified character is available by using the negative sign in between the starting letter and the last letter.

E.g. ‘asd’ REGEXP ‘[a-z]’ will return 1
You can check whether a particular letter is not available in a set of letters by using the ‘^’ sign.

e.g. ‘abc’ REGEXP ‘[g-z]’ will return 1.

‘*’ is used to match any number of previous occurrences of the letter or the phrase that have been specified along with the * wild card character.

To specify one or more occurrences you have to use ‘+’ instead of ‘*’.

When more than one operator is used in a single expression then operator precedence will be used to evaluate the expression. The operators with higher precedence will be evaluated first. If it come across two operators with the same precedence then the expression will be evaluated from left to right. The precedence order is given below.

!
- (unary minus) ~ (unary bit negation)
^
* / DIV % MOD
+ -
<< >>
&
|
< <= = <=> <> != >= > IN IS LIKE REGEXP RLIKE
BETWEEN CASE WHEN THEN ELSE
NOT
AND &&
OR || XOR
:=

Any operation done with null values will result null. Exceptions are listed below.

NULL <=> NULL 1
1 IS NULL 0
NULL IS NULL 1
Back to the top

Type Conversion

Mysql performs automatic type conversion according to the operation you are performing.

For an example consider the following operations.
1+’2’
Here 1 is of integer type and ‘2’ is of string type. Since + is a numerical operator Mysql will convert the string value to numerical value and forms the output as 3.

CONCAT(1,2,3)
Here although the operands are numerical values Mysql will treat them as integer values because the operator is a string operator.
Back to the top

Query Optimization

Indexing plays an important role in optimizing the queries.(i.e. reducing the time taken for searching and retrieving data). Mysql supports indexing the tables to optimize the queries. In the same manner the indexes reduce the searching time if you don’t use them properly indexes will consume much more time than the query is executed plainly. However indexes will speed up only the queries which are based on the built index. Indexes have their own disadvantages too. Some of them are that index table also consume space. Some times it will be an additional cost because you have to read the two table to retrieve data.(i.e. index table and the data table). This cost depends on the size of the data table and the number of the rows that have to retrieved. For an example if almost all the rows satisfy the where condition it costs much less if you scan the data table directly without looking into the index table.

Things to Remember When Choosing a Column to be Indexed

When selecting a column to be indexed consider the columns that are used for selecting, sorting and grouping. Indexing the columns that are used to display will not help you to reduce the searching time.

Another factor is that select the rows that have a higher cardinality relative to the table size. Cardinality indicates the number of unique values in that column.

Always select a small data type to be indexed, and if you are indexing a string column don’t use the entire column to be indexed if most of the values can be differentiate by the first few characters.

Optimizing the Queries

This section gives information about how the sql optimizer works. TO view the details of how the query analyzer works use the keyword EXPLAIN.


e.g explain select * from table-name where 0\G;

Then you will get some details on how the query is executed. The field you get for tis query are explained below.
Type Indicates that it can use indexes to search the values.
Possible Keys, Keys Indicate that they are the candidate keys and the actual keys that will be used.
Rows This indicates the estimation made by the optimizer for the number of rows that have to selected to execute the query.
In the query optimizers point of view it will first check whether the where clause is valid. Since no column satisfies the condition it will not scan the table. Instead it will just output zero rows.

Mysql query optimizer works mainly using the indexes for reducing the time taken to search. It selects which index to be evaluated first in order to optimize the query. For an example consider that you are querying from two columns where an index is built on each column.
Select column1 from table-name where column2=value2 and column3=value3;
Assume that 600 rows satisfy the value2 in the column 2 , 200 rows satisfy the value3 in column 3 and 50 rows satisfy both conditions. If you execute the query based on the first sub condition 600 rows will be selected first. Then from that 600 rows 550 rows will be removed and only the 50 rows that are satisfying the condition will be remained.
On the other hand if you use the second sub condition for the initial evaluation only 200 will be selected and out of that only 150 will be rejected. Hence the optimizer will execute the query by evaluating the second sub condition first.

You can optimize the query by making the indexed column stand alone in the comparison. As an example consider the following two statements. Here an index is built on the col.

…. where col<>

In the first one mysql will compute 4/2 and using the result it will compare the index table. But in the second one Mysql has to retrieve the values of mycol from each row and multiply it by 4 and compare it with 2. In this case no index table can be used.

To optimize the queries always try to avoid automatic type conversion.
Back to the top

Loading Data Efficiently and Scheduling and Locking Issues

This issue will be much important when you are dealing with bulks of data.
When inserting huge amount of data LOAD DATA is much efficient than ‘insert’ statements because they insert the values in bulks.

When considering the scheduling issues Mysql has the following priorities by default.
• Writes have higher priority than reads.
• Writes to a table must occur one at a time, and write requests are processed in the order in which they arrive.
• Multiple reads from a table can be processed simultaneously.
This priorities are maintained by the locks. If a client wants to access a table he must obtain a lock on that table. This can be done manually. But server’s lock manager automatically handles them. To write to a table you must have an exclusive lock and to read from a table a shared lock is needed. When a person is having an exclusive lock on a table on one else can either read or write to that table. But it someone is having a shared lock on the table any number of other users can read the table, but no one can write to the table.

Mysql has several priority options to reschedule its default priority settings.
• The LOW_PRIORITY keyword applies to DELETE, INSERT, LOAD DATA, REPLACE, and UPDATE statements.
• The HIGH_PRIORITY keyword applies to SELECT and INSERT statements.
• The DELAYED keyword applies to INSERT and REPLACE statements.
Using the options Low_priority and high_priority you can reset the default priority settings of reads and writes. Using the ‘delayed’ keyword you can handle dirty reads. When a request comes for an insertion or a replacement the rows are put in to a queue until there are non more readers. Then the rows are inserted in to the table.

In MyISAM tables you can read and write simultaneously. This is possible because the rows are always inserted to the end of that table.

Optimization Options Available for Administrators

Since data in the memory can be accessed much faster than the data in the hard disk keeping the information as much as possible in the memory will make the operations much faster. Hence to increase the speed of query execution you can increase the cache size.
Back to the top

Backing up a Database

To backup a database you can use "mysqldump" utility. As an example suppose that you want to backup a database named test. Then execute the following command.


#mysqldump -p test>testBackup.sql

The backup file is named as testBackup.sql

To restore the backup database execute the following command.


#mysql test&lttestBackup.sql

No comments:

LifesDream - earn money each month





Verifield - Innovative Wealth Management

Verifield - your proven partners on stock markets Hasilkan uang dari Internet? Why Not? AVAZZY.com - Real business and real incomes

Alert Pay.. Get paid for subscribe...

DbClix Join 4Shared Now!

Liberty Reserve - Online Investment


Head Tail Game Liberty Reserve LR

Flip N Win

DepositFiles

Google

Get Paid to sign Up.

Internet Pay to Click

YOU can make $THOUSANDS of dollars per month...Starting from $0.00!

LinkGrand.com


Baca iklan .. dibayar loh...!

www.easyhits4u.com

EasyHits4U.com - Your Free Traffic Exchange - 1:1 Exchange Ratio, 5-Tier Referral Program. FREE Advertising! DonkeyMails.com: No Minimum Payout

Yahoo! Movies News & Gossip

Alexa

Alexa review on mInvestment