While working on the Data Warehouse project I compiled a quick reference of the MySQL datatypes. This can save time when making decision on which datatypes to use at database creation stage. I have also added some general MySQL tips and notes after the Data Types Summary.

1.     MySQL Data-types Summary
2.     Revision Information
3.     Introduction
4.     Numeric with Decimals
5.     Numeric without Decimals (Whole Numbers)
6.     Text String
7.     Binary String
8.     Special String
9.     Date/Time
10. Additional Data-Type Notes
11. References
12. Extras - General MySQL tips and notes.

Introduction

Following tables summarise the data-types used in the MySQL DBMS. Information in this summary is applicable on MySQL 5.0.3 and above versions (still applicable in 2013).

Numeric with Decimals

Datatype	Size	Range	Description	Attributes	Default
float	4 bytes	Decimal Precision upto 24 places. Min=+/-1.175E-38 Max=+/-3.403E+38	*float(<size>, <digits>) [attributes]* · A single-precision floating-point number, 4 bytes. For storing small floating-point numbers.	unsigned, zerofill	null (0 if not null) Default Precision: 10,2
double	8 bytes	Decimal Precision upto 53 places. Min= +/-2.225E-308 Max= +/-1.798E+308	double(<size> [<digits>]) [attributes] · For double-precision floating-point numbers. This is for storing very large floating-point numbers. synonyms: double precision, real	unsigned, zerofill	null (0 if not null) Default Precision: 16,4
decimal	<size> +1 byte OR <size> +2 bytes	Max: DOUBLE range Fixed point unlike DOUBLE (A DOUBLE stored as a string, allowing fixed decimal points.)	decimal(<size>, [<digits>]) [attributes] · For storing floating-point numbers where precision is critical, such as monetary values. Especially useful for storing "exact" digits after decimal point. · <size> is the total precision i.e. total number of digits (Max 65 excluding the decimal point and "-" sign. If omitted default is 10) · [<digits>] are number of digits after the decimal point (Max 30. If omitted default is 0). synonyms: dec, numeric	unsigned, zerofill	null (0 if not null) Default Precision: 10,0

Numeric without Decimals (Whole Numbers)

tinyint	1 byte	Signed: -128 to 127 Unsigned: 0 to 255	tinyint(<size>) [attributes] · A whole number, 1 byte, with a maximum range of -128 to 127 (unsigned: 0 to 255)	auto_increment, unsigned, zerofill	null (0 if not null)
smallint	2 bytes	Sign: -32,768 to 32,767 Unsi: 0 to 65,535	smallint(<size>) [attributes] · A whole number, 3 bytes, with a maximum range of -32,768 to 32,767 (unsigned: 0 to 65,535)	auto_increment, unsigned, zerofill	null (0 if not null)
mediumint	3 bytes	-8,388,608 to 8,388,607 Unsi: 0 to 16,777,215	mediumint(<size>) [attributes] · A whole number, 3 bytes, with a maximum range of -8,388,608 to 8,388,607,647 (unsigned: 0 to 16,777,215)	auto_increment, unsigned, zerofill	null (0 if not null)
int	4 bytes	Sign: -/+2.147E+9 Unsi: 0 to 4.294E+9	int(<size>) [attributes] · A whole number, 4 bytes, with a maximum range of -2,147,483,648 to 2,147,483,647 (unsigned: 0 to 4,294,967, 295) synonyms: integer	auto_increment, unsigned, zerofill	null (0 if not null)
bigint	8 bytes	Sign: -/+9.223E+18 Unsi: 0 to 18.45E+18	bigint(<size>) [attributes] · The largest integer type. A whole number, 8 bytes, with a maximum range that is so big that i don't want to type it. Doubtful that any of us mortals will need to use this data type.	auto_increment, unsigned, zerofill	null (0 if not null)

Text String

Regular text datatypes sort & compare characters in a case-insensitive manner in accordance with the sorting rules for the default character set.

char	length bytes	255 characters	char([<length>]) [attribute] · Fixed length - for storing strings that doesn't vary much in size. Range of 0 to 255, stores that amount in bytes synonyms: character, national char, national character	Binary, <CharacterSet>	null ("" if not null) Default Length: 1
tinytext	String length + 1 byte	255 characters	tinytext · Variable-length storage for short text values. Range of 0 to 255 characters, like varchar.	binary, <CharacterSet>	null ("" if not null)
varchar	String length + 1 byte	65,535 characters	varchar(<length>) [attribute] · Variable length – for storing strings that will vary in size. Range of 0 to 65,535. Stores that amount in bytes, plus 1 byte. synonyms: character varying, national varchar	binary, <CharacterSet>	null ("" if not null)
Text	String length + 2 byte	65,535 characters	Text · Variable-length storage for most text values. Range of 0 to 65,535 characters.	binary, <CharacterSet>	null ("" if not null)
mediumtext	String length + 3 bytes	16,777,215 characters 16.77 million	mediumtext · Variable-length storage for medium-sized text values. · Range of 0 to 16,777,215 characters.	binary, <CharacterSet>	null ("" if not null)
longtext	String length + 4 bytes	4,294,967,295 characters 4.29 billion	longtext · Variable-length storage for very large text values. · Max 4GB capacity may practically be capped due to available memory and mysql protocol limits.	binary, <CharacterSet>	null ("" if not null)

Binary String

Binary datatypes store and compare data strictly byte-by-byte (hence are "case-sensitive").

binary	length bytes	255 characters	binary([<length>]) · Fixed length - for storing binary strings that doesn't vary much in size. · Range of 0 to 255, stores that amount in bytes.	-	null ("" if not null) Default Length: 1
tinyblob	String length + 1 byte	255 characters	tinyblob · Variable-length storage for short binary values · Range of 0 to 255 characters.	-	null ("" if not null)
varbinary	String length + 1 byte	65,535 characters	varbinary(<length>) · Variable length – for storing binary strings that will vary in size. · Range of 0 to 65,535. Stores that amount in bytes, plus 1 byte.	-	null ("" if not null)
blob	String length + 2 byte	65,535 characters	blob · Variable-length storage for most binary values · Range of 0 to 65,535 characters.	-	null ("" if not null)
mediumblob	String length + 3 bytes	16,777,215 characters 16.77 million	mediumblob · Variable-length storage for medium-sized binary values · Range of 0 to 16,777,215 characters.	-	null ("" if not null)
longblob	String length + 4 bytes	4,294,967,295 characters 4.29 billion	longblob · Variable-length storage for very large binary values. · Max 4GB capacity may practically be capped due to available memory.	-	null ("" if not null)

Special String

Used for selective strings, these use less space in the database because the fields are stored as an index number, not as the strings. Sorting therefore is more efficient as it occurs according to the index, not the character values in the string.

enum	1-2 bytes	Max 65,535 enum elements	enum(<value1>, <value2>, ...) · When inserting in an enum field, only one value from predefined list can be inserted. · Maximum number of elements are: 65,535. · E.g.: enum('pear', 'mango', 'apple', 'orange') now, in an enum field only one of these fruits may be inserted, i.e.: insert into tbl_fruit values ('mango');	<character set>	null (1st value if not null)
set	1-8 bytes	Max 64 members	set(<value1>, <value2>, ...) · Similar to enum(), except set() allows you to insert more than one value from your predefined list. · Maximum number of elements are: 64. · E.g.: set('pear', 'mango', 'apple', 'orange') now you can put in 'set' field one or more of these fruits, like: insert into my_table (fruits) values ('pear, apple');	<character set>	null ("" if not null)

Date/Time

date	3 bytes	"1000-01-01" - "9999-12-31"	Date · Format: YYYY-MM-DD Example: 2006-09-23 · Range of years 1000 to 9999	-	null ("0000-00-00" if not null)
datetime	8 bytes	"1000-01-01 00:00:00" - "9999-12-31 23:59:59"	Datetime · Format: YYYY-MM-DD hh:mm:ss Example: 2006-09-23 01:15:23 · Stores date and time from years 1000 to 9999	-	null ("0000-00-00 00:00:00" if not null)
time	3 bytes	"-838:59:59" - "838:59:59"	Time · Format: hh:mm:ss Example: 01:15:23 · Stores time from midnight (00:00:00) to one second before midnight (23:59:59)	-	null ("0000-00-00" if not null)
timestamp	4 bytes	19700101000000 - 2037+	timestamp (<size>) · Format: YYYYMMDDhhmmss Example: 20120822153055 · Stores a timestamp from years 1970 to 2037 (unix year). · If NULL value is inserted into a timestamp column, the current date and time are inserted instead of the NULL. · If you modify any field in a row that has a timestamp column, the first timestamp column will automatically be updated with the current date and time.	-	Current date & time
year	1 byte	year(4): 1901 - 2155 year(2): 70 - 69 (representing 1970 to 2069)	Year [(2\|4)] if year(2) is used. · Format: YY if year(2) is used. Example: 76 if year(4) is used. · Format: YYYY if year(4) is used. Example: 1876	2, 4	null ("0000" if not null)

Additional Data-Type Notes

· Case Insensitive Datatypes: tinytext, text, mediumtext, longtext, enum and set are NOT case sensitive, unless given the "binary" attribute or have a case-sensitive CHARACTER SET collation.

· BOOL and BOOLEAN data types are synonyms for TINYINT(1).

· REAL[(M,D)] and DOUBLE PRECISION[(M,D)] datatypes are synonyms for DOUBLE[(M,D)].

· REAL_AS_FLOAT system variable can make REAL[(M,D)] a synonym for FLOAT[(M,D)].

· "SERIAL DEFAULT VALUE" attribute is an alias for "AUTO_INCREMENT NOT NULL UNIQUE".

· "SERIAL" data type is a synonym for "BIGINT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE".

· Default Values: All fields are by default auto-populated with MySQL 'NULL', however if 'NOT NULL' attribute is used then fields are assigned with the following:

o Numeric -> 0. (i.e. all numeric types, if omitted, are auto-assigned with a zero).

o Sting/Set -> "" (i.e. all string types or sets, if omitted, are auto-assigned with a blank space).

o Enum -> 1st value of enum.

o Datetime -> "0000-00-00 00:00:00".

o Time -> "00:00:00".

o Timestamp -> Current Date & Time.

o Year -> "0000".

· Java's null is NOT same as MySQL 'NULL' hence requires conversion.

· Most commonly used data types are: decimal, int, char, varchar, text, enum and date.

References

ü http://dev.mysql.com/doc/refman/5.5/en/data-type-overview.html

ü http://tibetangeeks.com/technologies/database/datatypes-quickref.html

ü http://kimbriggs.com/computers/computer-notes/mysql-notes/mysql-data-types-50.file

----------------- x -----------------

General MySQL tips and notes

Import - Export using CLI (Command Line Interface):

EXPORTING:

- MySQLDump (EdChoice): Mysqldump is standalone CLI tool for exporting the entire database into a sql file (it shouldn't be runing inside mysql> prompt), e.g. # mysqldump -u usrname -p yourpassword dbName >file.sql;

- Exporting (CLI) to CSV File Using Select .. Into Outfile Statement:

The SELECT ... INTO OUTFILE statement is a quick way to dump a table to a text file on the same server machine.

e.g.:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ' " '

LINES TERMINATED BY '\n'

FROM test_table;

Notes:

- Use INTO DUMPFILE instead of INTO OUTFILE, to write the whole data in only one row (in output file), without any escape processing, column or line termination, useful with BLOB values.

- To dump (or create) the resulting outfile on some client host (other than the server host) instead use : mysql -e "SELECT ..." > file_name.

- For very large backups "mysqldump" utility should be used instead of "Select .. Into Outfile".

IMPORT:

- Same syntax as running a sql-script file except here you don't mention the "dbname" & use '<' instead of '>'.

CLI:

i.e. # mysql -u usrname -p password < file.sql OR $ mysql --user=yourname --password=password < script_file.sql OR # On mysql client's Prompt: mysql> source script_file.sql OR mysql> \. script_file.sql (the '\. is equivalent 'source' command). Caution: Prior to running a script in all options above, no database must be selected.

GUI: Open "Query Browser" > File > Open Script > Click on "Execute" button.

See also: Running SQL-Script-File in MySQL:

IMPORTING XML: Loading xml data

Note: table must pre-exist, other wise you will get error. Also don't forget to escape (using /backslash) white spaces and special characters including back-slashes:

LOAD XML LOCAL INFILE 'C:\\Documents\ and\ Settings\\eddie\\My\ Documents\\tmpxml\\condolence_pers.xml'

INTO TABLE condolance_person(CP_ID, CP_Person, CP_Order, CP_Valid, cp_end_date);

DATA MIGRATION - MS Access to MySQL:

There are basically four ways to migrate a database from Access to MySQL: (Source: http://25yearsofprogramming.com/blog/2010/20100718.htm)

- Use a MySQL (or other) utility program to pull the structure and data out of the .mdb file.

- Use Access to push the structure and data into a MySQL database via ODBC.

- Create the new empty MySQL database and its table structures manually with hand-coding; export the data from Access to a text file; import from the text file to MySQL.

- Use a commercial converter program.

- Many tools are available to migrate data from MS Access to MySQL, such as:

# Talend Open Studio: open source Talend ETL tool is great for "data integration and migration". It's easy to learn for a non-technical user, its 'tMap' component allows the user to get a graphical and functional view of integration processes.

# Navicat (www.navicat.com)

+ Very fast

+ Allows you to check and edit how to convert tables

- Doesn't convert autonumbers to autoincrement

- Doesn't seem to convert OLEs to BLOBs properly

# Access-To-MySQL (Intelligent Converters) (www.convert-in.com/acc2sql.htm)

+ Seems to convert everything properly

- Unbearably slow

# Bullzip's Access-To-MySQL (www.bullzip.com//products/msa2mys/info.php)

+ Very fast

+ FREE

- Truncates currency fields

# MySQL Migration Toolkit-deprecated now (Workbench will eventually have built in Migration tool.)

+ Very fast

+ FREE

- Leaves some tables unconverted (perhaps fixed in later versions)

Other Copying Methods:

- To Copy A Table (within the same server): Simply use: CREATE TABLE <destination_tbl> SELECT <col_names> FROM <source_table>;

- To Copy A Table (to a different server): One of the easiest ways is to export the source-data as resultset (that is made look like a script by appending or concatenating every row of resultset with INSERT statement) into a flat text file (i.e. a script file) and simply run that script in the target server's database. e.g.:

SELECT CONCAT("INSERT INTO tbl_rumours_qa (qa_title, qa_status, qa_date, qa_question) VALUES (' ", qa_title, " ',' ", qa_status, " ',' ", qa_date, " ',' ", qa_question, " ');" )

FROM tbl_rumours_qa WHERE YEAR(qa_date) >= 2009 ORDER BY qa_id DESC;

Note: One benefit of using this approach is that you don't need any write-permission (DDL or DCL Privilege) on the database server, and only insert-permission (DML-Privilege) will be sufficient.

* Create a duplicate table (with certain order):

CREATE TABLE tbl_replica

SELECT * FROM tbl_original ORDER BY 2;

* MySql Query Browser - Features & Functionalities:

- Running Script: File > Open Script > Click on "Execute" button. (See also: "Running SQL-Script-File in MySQL").

* Setting Global Variable in MySql:

GUI: my.cnf ( or my.ini in Windows): All MySql's configuration is written in "/etc/my.cnf" file. As per E's sugestion we must set 'auto_increment_increment' to 10 in this file.

CUI: Developing for MySQL production server? then a global variable is required to be set on the localhost server. Steps:

- Log in to MySQL server. Using 'mysql -h localhost -u root -p'

- MySQL-client will require you to enter a password, do so.

- Next set the following command:

# Set @@global.auto_increment_increment=10;

# To check if the set has been committed do the following:

select @@global.auto_increment_increment;

- This will set the incrementation off all auto increment to a plus ten, i.e if the first record inserted is 1 then the second record inserted will be 11 then 21 then 31 and so forth.

Important: Why is this required? For disaster recovery and replication. Since the secondary server can not insert the same number as the primary server (key violation) then the primary will insert a new record on every 10 and secondary on every 20.

Note: If you are developing code in VB or Access where Integer is limited to 32768 numbers then the code will brake with over flow error as the auto incrementation is bigger then allowed. Therefore alter it to Long or Double datatype instead.

* Altering column type: (clob into blob)

ALTER TABLE tbl_rumours_qa

MODIFY col1 blob;

* Online Help Trick (for MySql):

- This often works with many portal sites. You can use URL www.mysql.com/SOMETOPIC to search the online manual for SOMETOPIC. If there is an exact match, you will be taken straight to the appropriate page; otherwise, you will see a list of possible matches.

- Developer Zone: The MySQL website, www.mysql.com, contains a section called Developer Zone (which you can access directly at http://dev.MySQL.com/) where you can find online support in the form of forums, mailing lists, and user groups. Consider joining one of the online communities; they are a great way to learn more about MySQL from other users.

MySQL Configuration

* Location of MySQL installation & libraries on Linux

In a unix based systems, MySQL is typically installed at either /usr/MySQL/ or /usr/local/MySQL/. In case of RPM (packaged installations), the "data directory" is typically /var/lib/mysql/. In Windows it is usually C:\Program Files\MySQL\MySQL Server 5.0\ with a program group (in the Start menu) contains a shortcut to the MySQL monitor program and the configuration wizard.

Note: Make sure that the MySQL bin directory is in your operating system's path.

* MySQL Directory Structure on Linux

The following are subdirectory's structure:

- bin: Contains the MySQL executables, including the MySql server and all the client programs.

- lib: Contains the development libraries used to communicate with a MySQL database from your own program-codes.

- include: Contains the header files required to use MySQL APIs.

- data: The MySQL data directory, containing the actual database files.

- support_files: A number of sample configuration files.

* my.cnf / my.ini:

Resides under:

· Linux: /etc/my.cnf

· Windows: C:\Program Files\MySQL\MySQL Server 5.5\my.ini

The MySQL configuration file (my.cnf or my.ini) contains global settings used by all the running MySql-servers in a machine (it is possible to have multiple MySQL servers on one machine), but you can also create 'my.cnf' file in the 'data' directory (under MySql-installation folder) that applies only to that particular MySQL-server. In Windows systems, the current configuration is found in "my.ini" located in the installation directory, which also contains a number of sample configuration files.

bind-address parameter my.ini:

· For example, if the server (where mysql server is running) has ip address 10.66.#.##, then my.ini should use:

bind-address=10.66.#.##

· You can see which ip it's currently listening on (if any) by running:

ss -l # here -L stands for Listening.

e.g.:

· --bind-address=127.0.0.1 # localhost only

· --bind-address=0.0.0.0 # listen on all interfaces

· --bind-address=192.168.1.120 # listen on that IP only

* Connecting to MySQL-server using CLI:

The 'mysql' CLI Client Program:

The 'mysql' Client Program (usually written in lower case) is a command-line client that is used for sending commands to a "MySQL server". It can be used to enter SQL commands and also it has its own set of commands to control its operation/environment. To start it, simply run 'mysql' (either by clicking or typing on CLI). If anonymous connections is allowed then you can invoke mysql without any additional switches, and you will get mysql prompt, e.g.: $ mysql. Remember mysql CLI commands end with semi-colon (;) or \g. Type 'help;' or '\h' for help. Type '\c' to clear the buffer. (In Windows it is called: "MySQL Command Line Client".)

MySQL Login

Syntax:

mysql -h<hostname_or_ipAddr> -u<usr> -p<pwd> [dbname] (Notice there is no space between switches and its values passed, e.g. "-uroot" NOT "-u root").

e.g. # > mysql -h10.14.#.## -umyusrname -pmypass mydbname

To connect to MySQL server running locally, we only need username (-u) and password (-p) nothing else, but for remotely running MySql server we also need hostname (-h).

- To connect MySQL-server running over a network, you must specify the hostname (with -h option you can either use the "IP address" or "hostname"). If you are connecting to a MySQL server running on the local machine, the hostname is localhost, which is default anyway thus can be omitted.

- You also need to supply a username and password to authenticate with the database server. A username and password to access a database can be locked down to a location so that they work only when they connect from a specific location.

- Finally, you can supply the database name to connect to. Each username may have access to one or more databases on the server; you must authenticate to gain access to your own databases. The mysql program accepts a number of switches to specify how to connect to a database:

Use the -u (or --user) switch to supply a username, and use the -p (or --password) switch to supply a password, e.g.:

$ mysql --user=yourname --password=yourpass

Use the -h (or --host) switch to connect to a database on a remote server, e.g.:

$ mysql --host=host.yourdomain.com --user=yourname --password=yourpass

Use --database switch can be abbreviated to -D. In fact, the --database or -D switch can be omitted, and you can just specify the database at the end of the mysql command, e.g.:

$ mysql -hwww.yourdomain.com -uyourname -ppassword databasename

Beware-About-Password-Argument:

When using the short connection switches, make sure that there is no space between - p and the password. Otherwise, you will be prompted to enter a password, and the password you gave on the command line will be treated as the database name.

* Anonymous Connections to MySQL:

Usually anonymous connections is allowed by default, however for security purpose, it is a good idea to remove anonymous user access.

* MySQL Default Port:

The default port for connections to MySQL over a network is 3306. If your server uses a different port number, specify it using -P (or --port=) when connecting using mysql.

MySQL Commandline Parameters

(Note: These are specific to MySql - Not part of standardised SQL) including "Backslash-Commnads":

show databases; Lists available databases (Notice databases is plural).

\u <DatabaseName>; Selects specified database.

\s Displays the current status of the current database connection, including the current database name and the connection parameters. If you are using several databases at the same time, issuing \s is a handy way to find out which one you are working on at any given time.

\r Forces reconnect to the database server.

\g (Lower-case g) Terminates & executes a command, same as semi-colon (;).

\G (Upper-case G) Terminates & executes a command, but the query results are displayed in a vertical format unlike \g (or ;) that displays results in tabular-layout. \G format produces one row of output for each column in the database. This formats the query output nicely, so you can read through it easier. To use it, you just replace the semi-colon at the end of the query with ‘\G’.

\c (terminate without execution) Clears the current line(s) and return to the mysql> prompt without exicuting current SQL query (unlike \g or ; that execute the query).

\e Opens a text-editor containing the current query. The actual editor used depends on EDITOR environment variable (usually vi or vim). On return, you get the prompt, to execute the query again, enter a semicolon or use the \g or \G commands.

\T <textfilename> Diverts or re-directs the output of a SQL query into a text-file. For instance, to write the output of a query to 'query.txt': "\T output.txt", now all subsequent queries executed in mysql-client will be appended to 'query.txt' as well as will be displayed onscreen. Both the retrieved data and the query itself are written to the file, in fact, the log file records exactly what is displayed onscreen.

\t Stops writing to a log file, the \t command cancels any previously issued \T commands.

\! Executes a host command (i.e. unix shell / DOS command) without exiting mysql prompt (of mysql client), e.g. mysql> \! pwd. or \!clear : will clear all previously entered commands on the mysql command-line interface.

\q | quit | exit Exits mysql client.

Locating MySQL binary (in Linux):

# which mysql : This CLI command displays the path of the mysql binary. E.g. /local/bin/mysql

# mysql --version : This displays the MySQL version, this command can also be used to find out if mysql is installed at all.

Output Format for Query-Results:

1) Horizontal Tabular-Layout: When mysql's client is started normally and when statements are terminated normally (i.e. with ';' or \g), query-results are displayed in tabular-layout.

2) Vertical Format: The vertical query format is very useful when you want to view a single row of data from a table. The tabular layout might produce output that is too wide for your screen if the table contains many columns.

2.a) When mysql's client is started normally and when statements are terminated with \G, query-results are displayed in vertical-layout.

2.b) When mysql's client is started with "--vertical" switch and when statements are terminated normally (i.e. with ';' or \g), query-results are displayed in vertical-layout.

3) HTML Format: When mysql is invoked with switch --html, query-results are produced in HTML table format.

4) XML Format: When mysql is invoked with switch --xml, query-results are produced in XML data output format.

* Running SQL-Script-File in MySQL:

- GUI: Open "Query Browser" > File > Open Script > Click on "Execute" button.

- CLI:

# On OS-Terminal's Prompt (one liner): $ mysql --user=yourname --password dbname < script_file.sql (The '<' symbol causes the contents of script_file.sql to be passed to the mysql client program all at once.)

# On mysql client's Prompt: mysql> source script_file.sql OR mysql> \. script_file.sql (the '\. is equivalent 'source' command).

Caution: Prior to running a script in all options above, either a database must be selected manually or "use dbname" command must be present (at top) in the script-file, otherwise "No database selected" error will occur.

MySQL Question - Answers (from book):

- Why do we need DBMSs at all, can't we just use flat files or XML files?

Ans: DBMS has many benefits such as: Security, multi-user customisable access control, robustness & resilience, fast transactions (by pools & caches), remote access (over the network) and Scalability. Scalability is an important advantage of DBMS over a flat table systems, using DBMS very large databases can be constructed from different tables. (A table must contains only information relevant to that table.)

- Why MySql:

Ans: MySQL is distributed as open-source software under the GNU General Public License, so you can actually use MySQL free of charge. MySQL is robust, powerful, and scalable. Ranging from tiny web databases to very large data warehouses with terabytes of data. You can use MySQL replication or clustering to guarantee 100% availability. A number of case studies published on MySQL.com give an example of just what MySQL is capable of. You can read about how MySQL has been used in large projects at http://www.mysql.com/why-mysql/case-studies/. MySQL is a multiuser database yet provides full access control over stored information i.e. we can restrict access to each database to only specific users.

- Is MySQL case-sensitive?

Ans: No, in all new database-clients (regardless of OS even on Linux), interaction with the MySQL server is NOT case-sensitive. This includes: standard SQL keywords, entity names, function-names etc. However certain switches used in mysql command line (in terminal) ARE case-sensitive, such as a statement terminated with \G will format the output differently than a lower-case \g or a semicolon ( ; ).

- How to perform a case-sensitive search on data stored in a MySQL database table?

To search a value in a particular case, we can use the binary() function, e.g. to find "text" but not "TEXT" or "Text", you can use:

SELECT * FROM table_name WHERE binary(field_name)='text';

- Can WHERE clause's key-values be inverted?

Ans: Yes instead of writing WHERE name='aditya' you can write WHERE 'aditya'=name;

- Can we write string-values without quotes?

Ans: No, all string-values MUST be enclosed within 'single' or "double" quotes.

- When using "ORDER BY" clause, do I need to compulsorily retrieve the column on which query-result is being sorted?

Ans: No, the sort column specified in "ORDER BY" does not even need to appear in the list of columns after the SELECT keyword. This means, you can specify a sort-order using a column that is not even being retrieved by the query. But remember, when using "GROUP BY" clause you DO need to select that column.

- Can I sort query-results in descending order based on multiple columns?

Ans: Yes, a comma-separated list of column-names can be written after "ORDER BY" clause to sort result on multiple columns, and we can use 'DESC' keyword for descending sorting order. When you specify more than one sort column, the keywords ASC and DESC can be used after each column name.

- Is it compulsory to use the dummy table called 'dual' to mimic a real-table?

Ans: No, unlike Oracle you can perform a query in MySQL without supplying any table name, however it is perfectly valid to use 'dual' dummy table in MySQL, it always returns a single-value (unlike arrays-of-rows returned by real tables). This is useful only when you have to produce a result of an expression being created (using fixed values) on the fly. e.g. SELECT 15 + 28, 94 - 55;

- Can I use equality operator (=) with NULL?

Ans: No, comparing columnname = NULL does not work. If you want to check for NULL in a query, use IS NULL or IS NOT NULL.

* Facts about MySQL:

- Naming Convention:

# Database: In MySQL, the database name must be unique, upto 64 characters long and it cannot use the / and \ characters and "SQL reserved words".

# Table: the tablename must be unique (in a database), upto 64 characters long and it cannot use the / and \ characters and "reserved words", and a tablename must enclose the column name in quotes (") or backticks (`) if it contains a space.

- Boolean:

There is no Boolean data type in MySQL, TRUE equates to 1, and FALSE equates to 0. e.g. select 4 < PI(); -> 0.

- NULL:

NULL is NULL, it is niether empty-string ("") nor zero (0).

- Date Time Formats:

A date value in MySQL takes the international format YYYY-MM-DD or YYYYMMDD. Times take the format HH:MM:SS or HHMMSS.

- Auto Type Conversion:

If you use a string as a number, MySQL does its best to convert that string-numeral into the closest numeric equivalent. See examples below:

# SELECT '100' + 45; -> 145.

# SELECT '100 abc' + 45; -> 145. # Only first number is converted.

# SELECT 'abc 100' + 45; -> 45.

# SELECT '4 * 2' + 1; -> 5. # Only first number is converted.

# SELECT '2005-06-01' + 10; -> 5. # Only first number is converted, use INTERVAL keyword for data manipulation.

- BINARY:

MySQL does not perform case sensitive comparison for characters and strings. To force a case-sensitive string comparison, use the BINARY operator to instruct MySQL to compare the characters in the string using their underlying ASCII values rather than just their letters, see examples below:

SELECT 'abc' IN ('ABC', 'cba'); -> 1.

SELECT 'abc' = 'ABC'; -> 1.

SELECT BINARY 'abc' = 'ABC'; -> 0.

Note: Be default 'ORDER BY' performs case-insensitive/alphabetic sorts, for case-sensitive ordering, use BINARY keyword AFTER the 'ORDER BY' clause, e.g. ORDER BY BINARY.

- Alphabet-Range:

You can define a range using alphabets, but there is one thing to watchout, consider the following part of query:

WHERE first_name BETWEEN 'C' and 'J';

Caution: Resultset from above query will not display 'John'. Although the first letter falls within the specified range, John is considered a greater value than simply the letter 'J' because it contains more characters. Solution is to use two letters in query to specify range and 'John' will fall within the range CA to JZ, as below:

WHERE first_name BETWEEN 'CA' and 'JZ'.

- Comments in MySQL:

· single line comment : # or --

· multi-line or in-line comment: /* */

Examples of all 3 ways to comment with in code:

mysql> SELECT 1+1; # This comment continues to the end of line

mysql> SELECT 1+1; -- This comment continues to the end of line

mysql> SELECT 1 /* this is an in-line comment */ + 1;

mysql> SELECT 1+

this is a

multiple-line comment

Quotes in MySQL statments

· Don't quote:

o Don't quote database, table, or column names

o Don't quote column types or modifiers

o Don't quote numerical values.

· Do Quote

o Quote (single or double) non-numeric values

o Quote file names and passwords

· Don't oh but do quote, What !

o User names are NOT quoted in GRANT or REVOKE statements, but they are quoted in other statements.

* MySQL Commands List (commonly used):

(ref: www.bios.niu.edu/johns/bioinform/mysql_commands.htm " Short List of MySQL Commands ")

Show Commands:

SHOW DATABASES

Lists all MySQL databases on the system. Note: to start using a database use the USE <dbname> command.

SHOW TABLES [FROM database_name]

Lists all tables from the current database or from the database given in the command.

DESCRIBE table_name | SHOW FIELDS FROM table_name | SHOW COLUMNS FROM table_name:

These commands list all columns (fields) from the given table, along with column type and other info.

SHOW INDEX FROM table_name

Lists all indexes from this tables.

SHOW CREATE:

· SHOW CREATE <object_type> <object_name> : Shows the CREATE statement of the given object.

<object_type> can be: DATABASE, EVENT, FUNCTION, PROCEDURE, TABLE, TRIGGER and VIEW

e.g.:

SHOW CREATE TABLE <tbl_name> : Shows the CREATE TABLE statement that creates the given table. This command requires user to have the SELECT privilege on the table.

Table Commands

CREATE TABLE table_name (create_clause1, create_clause2, ...)

Creates a table with columns as indicated in the create clauses.

create_clause

column name followed by column type, followed optionally by modifiers. For example, "gene_id INT AUTO_INCREMENT PRIMARY KEY" (without the quotes) creates a column of type integer with the modifiers described below.

create_clause modifiers

AUTO_INCREMENT : each data record is assigned the next sequential number when it is given a NULL value.
PRIMARY KEY : Items in this column have unique names, and the table is indexed automatically based on this column. One column must be the PRIMARY KEY, and only one column may be the PRIMARY KEY. This column should also be NOT NULL.
NOT NULL : No NULL values are allowed in this column: a NULL generates an error message as the data is inserted into the table.
DEFAULT value : If a NULL value is used in the data for this column, the default value is entered instead.

DROP TABLE table_name

Removes the table from the database. Permanently! So be careful with this command!

ALTER TABLE table_name ADD (create_clause1, create_clause2, ...)

Adds the listed columns to the table.

ALTER TABLE table_name DROP column_name

Drops the listed columns from the table.

ALTER TABLE table_name MODIFY create_clause

Changes the type or modifiers to a column. Using MODIFY means that the column keeps the same name even though its type is altered. MySQL attempts to convert the data to match the new type: this can cause problems.

ALTER TABLE table_name CHANGE column_name create_clause

Changes the name and type or modifiers of a column. Using CHANGE (instead of MODIFY) implies that the column is getting a new name.

ALTER TABLE table_name ADD INDEX [index_name] (column_name1, column_name2, ...)

CREATE INDEX index_name ON table_name (column_name1, column_name2, ...)

Adds an index to this table, based on the listed columns. Note that the order of the columns is important, because additional indexes are created from all subsets of the listed columns reading from left to write. The index name is optional if you use ALTER TABLE, but it is necesary if you use CREATE INDEX. Rarely is the name of an index useful (in my experience).

Data Commands

INSERT [INTO] table_name VALUES (value1, value2, ...)

Insert a complete row of data, giving a value (or NULL) for every column in the proper order.

INSERT [INTO] table_name (column_name1, column_name2, ...) VALUES (value1, value2, ...)

Insert data into the listed columns only. Alternate forms, with the SET form showing column assignments more explicitly.

INSERT [INTO] table_name (column_name1, column_name2, ...) SELECT list_of_fields_from_another_table FROM other_table_name WHERE where_clause

Inserts the data resulting from a SELECT statement into the listed columns. Be sure the number of items taken from the old table match the number of columns they are put into!

DELETE FROM table_name WHERE where_clause

Delete rows that meet the conditions of the where_clause. If the WHERE statement is omitted, the table is emptied, although its structure remains intact.

UPDATE table_name SET column_name1=value1, column_name2=value2, ... [WHERE where_clause]

Alters the data within a column based on the conditions in the where_clause.

LOAD DATA LOCAL INFILE 'path to external file' INTO TABLE table_name

Loads data from the listed file into the table. The default assumption is that fields in the file are separated by tabs, and each data record is separated from the others by a newline. It also assumes that nothing is quoted: quote marks are considered to be part of the data. Also, it assumes that the number of data fields matches the number of table columns. Columns that are AUTO_INCREMENT should have NULL as their value in the file.

LOAD DATA LOCAL INFILE 'path to external file' [FIELDS TERMINATED BY 'termination_character'] [FIELDS ENCLOSED BY 'quoting character'] [LINES TERMINATED BY 'line termination character'] FROM table_name

Loads data from the listed file into the table, using the field termination character listed (default is tab \t), and/or the listed quoting character (default is nothing), and/or the listed line termination chacracter (default is a newline \n).

SELECT column_name1, column_name2, ... INTO OUTFILE 'path to external file' [FIELDS TERMINATED BY 'termination_character'] [FIELDS ENCLOSED BY 'quoting character'] [LINES TERMINATED BY 'line termination character'] FROM table_name [WHERE where_clause]

Allows you to move data from a table into an external file. The field and line termination clauses are the same as for LOAD above. Several tricky features:

1. Note the positions of the table_name and where_clause, after the external file is given.

2. You must use a complete path, not just a file name. Otherwise MySQL attempts to write to the directory where the database is stored, where you don't have permission to write.

3. The user who is writing the file is 'mysql', not you! This means that user 'mysql' needs permission to write to the directory you specify. The best way to do that is to creat a new directory under your home directory, then change the directory's permission to 777, then write to it. For example: mkdir mysql_output, chmod 777 mysql_output.

Privilege Commands

Most of the commands below require MySQL root access

GRANT USAGE ON *.* TO user_name@localhost [IDENTIFIED BY 'password']

Creates a new user on MySQL, with no rights to do anything. The IDENTIFED BY clause creates or changes the MySQL password, which is not necessarily the same as the user's system password. The @localhost after the user name allows usage on the local system, which is usually what we do; leaving this off allows the user to access the database from another system. User name NOT in quotes.

GRANT SELECT ON *.* TO user_name@localhost

In general, unless data is supposed to be kept private, all users should be able to view it. A debatable point, and most databases will only grant SELECT privileges on particular databases. There is no way to grant privileges on all databses EXCEPT specifically enumerated ones.

GRANT ALL ON database_name.* TO user_name@localhost

Grants permissions on all tables for a specific database (database_name.*) to a user. Permissions are for: ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT, UPDATE.

FLUSH PRIVILEGES

Needed to get updated privileges to work immediately. You need RELOAD privileges to get this to work.

SET PASSWORD=PASSWORD('new_password')

Allows the user to set his/her own password.

mysqladmin -u<urs> -p<old_pwd> password <new_pwd>

Changes password OUTSIDE the MySQL client i.e. from the OS command prompt.

e.g.: C:\Program Files\MySQL\MySQL Server 5.5\bin> mysqladmin -uroot -proot password toor

REVOKE ALL ON [database_name.]* FROM user_name@localhost

Revokes all permissions for the user, but leaves the user in the MySQL database. This can be done for all databases using "ON *", or for all tables within a specific databse, using "ON database_name.*".

DELETE FROM mysql.user WHERE user='user_name@localhost'

Removes the user from the database, which revokes all privileges. Note that the user name is in quotes here.

UPDATE mysql.user SET password=PASSWORD('my_password') WHERE user='user_name'

Sets the user's password. The PASSWORD function encrypts it; otherwise it will be in plain text.

SELECT user, host, password, select_priv, insert_priv, shutdown_priv, grant_priv FROM mysql.user

A good view of all users and their approximate privileges. If there is a password, it will by an encrytped string; if not, this field is blank. Select is a very general privlege; insert allows table manipulation within a database; shutdown allows major system changes, and should only be usable by root; the ability to grant permissions is separate from the others.

SELECT user, host, db, select_priv, insert_priv, grant_priv FROM mysql.db

View permissions for individual databases.

* SQL-Joins (Joining Table & Multiple-Queries):

Joining Table:

- Cartesian product v/s Cross Joins:

A Cartesian product is sometimes known as a cross join, which is a product (multiplication) of number of rows returned from tables that are being incorrectly joined, it occurs when two (or more) tables are joined without the "WHERE" or "ON" cluase. Adding an appropriate WHERE condition to such join filters out the pairs of records for which there is no relationship between the data.

- Equijoin or inner-join:

# A join that involves a condition in which columns in two tables are specified as equal, is known as an equijoin or inner-join. e.g. WHERE t1.id = t2.id; it basically sets a relationship between two tables in which a column of first-table is equal to one of the second-table's column. In this method the WHERE clause is used to join tables.

# "INNER JOIN .. ON": Alternative syntax of Equijoin uses the keywords "INNER JOIN" to specify a table to join to, and another keyword "ON" to specify a conditional clause that indicates the relationship between the tables. e.g.: select * from t1 INNER JOIN t2 ON t2.id = t1.id; In this method the keywords "INNER JOIN" appear immediately after the FROM clause and before WHERE clause (or any filtering) is specified. When you use "JOIN" keyword, the relationship-condition between tables is defined just after the "ON" keyword instead of WHERE, leaving WHERE clause to be used only for filtering the resultset. This syntax for a join is longer, but often it is more readable. e.g. following is multi-table inner join:

select * from t1 INNER JOIN t2 ON t2.id = t1.id INNER JOIN t3 ON t3.code = t2.code;

Notes:

1) "ON" Clause: The ON clause works just like "WHERE" clause but is specific to a join-conditions only. If you need to, you can add extra conditions to the ON clause using AND, just as you can with WHERE.

2) Whether you put tables in the FROM clause and specify the join-condition in the WHERE clause, or use "JOIN .. ON" is a matter of preference, there is no performance difference.

- Self-Joins:

To join a table to itself, either by putting the same table-name after a JOIN keyword or in the FROM clause twice (wich aliases). You can often achieve the same result using a Subqueries.

# Self-Join v/s Sub-queries: Depending on the data-modelled, either may perform faster than the other, you have to try and see which one is faster.

- Natural Joins:

The keywords "NATURAL JOIN" performs a join on two tables without the need of an ON or WHERE clause to specify the relationship between the tables. Instead, MySQL assumes that the tables are related by all the columns that have the same name in both tables. However, whether you can use "NATURAL JOIN" depends on the column-naming convention used. e.g.:

select * from t1 NATURAL JOIN t2; # assuming both tables have a common column-name.

- Outer Joins:

Most joins return only the rows that have matching pairs of records from the two joined tables. In contrast the outer join returns all the rows from one table, regardless of whether the relationship-condition finds a matching row in the second table. When no corresponding record is found, columns that would otherwise contain values from the second table contain NULL. Outer joins are useful in producing reports when you do not want to exclude a record from the result if it does not have any corresponding data in a joined table and you don't mind NULL filling the space. e.g.: select * from t1 LEFT OUTER JOIN t2 ON t2.id = t1.id;

# LEFT v/s RIGHT OUTER JOINs:

LEFT OUTER JOIN, (often shortened to "LEFT JOIN"). The LEFT keyword tells MySQL that the left table, the one specified first, in the FROM clauseshould return every row with NULL in the right table when no match is found. Whereas the RIGHT OUTER JOIN (or "RIGHT JOIN") is used to reverse this behavior so that the table given after JOIN must return every row with NULL in the first (or left) table when no match is found.

# Important-Note: When using OUTER JOIN, any filtering-conditions must be written within the ON clause after the join-condition, and appended using 'AND' operator. This means that any filtering-conditions must not be written within the WHERE clause. Because the query returns a NULL value for rows in 2nd table, putting this filter in the WHERE clause would prevent those NULL values from being returned.

Joining Multiple-Queries:

Following SQL operations can be performed on two queries or two resultsets to merge them to render a single resultset, using: UNION, UNION ALL, and concepts of MINUS, EXCEPT, and INTERSECT.

- UNION:

The UNION operator is placed between two SQL queries to return a single result i.e. all the rows from the first query are returned first, followed by all the rows from the second query. A UNION is possible only if both queries return the same number of columns (i.e. both the SELECT clauses have same number of columns) otherwise, MySQL produces an error. e.g.:

SELECT name, telephone FROM new_customers

UNION

SELECT name, telephone FROM old_customers;

# UNION ALL Operator: By default, the UNION operator returns only unique values, to get every value (including duplicate values) use the "UNION ALL" operator.

# Mixing Different Columns: We can select two different columns from same or different tables, but remember that only the column headings (or aliases) from the first query is taken by the UNION. Therefore, to specify a sort order on a UNION, you must use the column name from the first query in the ORDER BY clause, however sort-order is applies to the entire resultset. e.g.:

SELECT first_name FROM cust # Caution: You can't have ORDER BY clause in the first query in a UNION, otherwise error is flagged.

UNION

SELECT last_name FROM cust

ORDER BY first_name; # Caution: You can't use 'last_name' here as sorting-order.

Caution-about-Sorting: You cannot specify a sort order on individual queries in a UNION. MySQL will give an error if ORDER BY appears anywhere other than after the last SELECT statement.

- MINUS or EXCEPT:

(MySQL does not have keywords MINUS or EXCEPT but we can use alternative methods to achieve these conceptual-resultsets) In Oracle-DBMS, MINUS renders a final resultset that contains only the rows from the first resultset excluding the resultset being appeared in second resultset, i.e. the result of the first query is returned, excluding any identical rows that are produced by the second query. In standard SQL it can be written as follows:

SELECT name FROM customer

MINUS

SELECT name FROM old_customer;

# In MySQL we can achieve the funcionality of MINUS (or EXCEPT) using "subqueries and LEFT JOIN":

1) subselect or sub-query along with "NOT IN" operator, e.g.:

SELECT name FROM customer WHERE name NOT IN (SELECT name FROM old_customer);

2) (although not as readable), using LEFT JOIN, e.g.:

SELECT c1.name FROM customer c1

LEFT JOIN old_customer c2 ON c2.name = c1.name

WHERE c2.name IS NULL;

- INTERSECT:

In standard SQL or Oracle-DBMS, keyword INTERSECT can be used to render the final resultset that contains common rows i.e. the rows that appear in both results. To simulate an INTERSECT in MySQL, use the JOIN, e.g.:

SELECT c1.name FROM customer c1

JOIN old_customer c2

ON c2.name = c1.name;

# Optimizing MINUS operation: Both the techniques of MySQL can produce very slow queries: The "NOT IN" operator cannot use an index (hence slow), and a "LEFT JOIN" often has to process very large amounts of data in memory. To get faster performance, consider using a "temporary table" instead.

- Using Temporary Tables:

Temporary tables in MySQL are a powerful means of fast data-manipulation. A temporary table exists only for one MySQL session, after which (i.e. next reconnection to MySQL server) it is automatically deleted. Otherwise, it works just the same as a regular database table. To create a temporary table simply insert keyword 'TEMPORARY' between CREATE TABLE, e.g. : CREATE TEMPORARY TABLE <...>.

# Performing a UNION: To unite or join two or more queries using a temporary table, you need to insert the data rows from both queries into the same tamporary table.

First, create a new table using the first query:

CREATE TEMPORARY TABLE myunion AS SELECT first_name FROM customer_contacts;

Now insert the result of the second query as follows:

INSERT INTO myunion SELECT last_name FROM customer_contacts;

The table myunion now contains the union. In fact, if you performed a SELECT on this table, it would give the same result that a UNION ALL operation on the two queries would produce. To simulate a UNION, in which only distinct values are returned, use keyword DISTICT, e.g.:

SELECT DISTINCT first_name FROM myunion;

# Performing a MINUS: To simulate a MINUS using a temporary table, first create a new table using the first query to define the columns and populate the initial data rows.

CREATE TEMPORARY TABLE myminus AS

SELECT first_name FROM customer_contacts;

Then delete the rows that also appear in the second query using a subquery, as follows:

DELETE FROM myminus WHERE first_name IN (

SELECT last_name FROM customer_contacts

);

The data remaining in the myminus table is the same result that you would have achieved using a MINUS operator on the two queries.

# Performing an INTERSECT: To simulate an INTERSECT using a temporary table, first create a new table using the first query to define the columns and populate the initial data rows.

CREATE TEMPORARY TABLE myminus AS

SELECT first_name FROM customer_contacts;

Then delete the rows that do not also appear in the second query using a subquery and the NOT IN operator, as follows:

DELETE FROM myminus WHERE first_name NOT IN (

SELECT last_name FROM customer_contacts

);

The data remaining in the myminus table is the same result that you would have achieved using an INTERSECT operator on the two queries.

* Operators, Keywords & Constructs (MySQL & SQL Clauses):

- UNIQUE:

UNIQUE keyword can use used in various places: 1) in queries (to eliminate duplicates). 2) in table creation (as a constraint).

- AND v/s OR Operator:

AND operator further restricts the filtering-condition, whereas the OR operator is used to relax the filtering criteria by specifying alternative filter-conditions.

- IN Operator:

1) Multiple ORs: First use of IN operator works like multiple equality-tests separated by OR operator, it takes a comma-separated list of values enclosed in parentheses. e.g. age IN (12,22,32);

2) Sub-query: Second use of IN operator is to create a sub-query, see "SQL Sub-Queries".

- SQL Sub-queries: Sub-queries are formed when the result of one query is passed into another query. e.g. SELECT name from tbl1 WHERE id IN (SELECT id from tbl2);

Notes-about-SubQuery:

a) Correct Syntax: The subquery must appear in parentheses after the IN operator or one of the comparison operators, e.g. > < <= >= etc.

b) Compatible match: Make sure that the resultset produced by sub-query must be comparable with what outter query is expecting.

c) AS: The 'AS' keyword is optional for table aliases. e.g. tbl t, OR tbl AS t.

d) Correlated subquery: You MUST use alias-name for a table-name if that table is being used in both inner-subquery as well as outter-query, ("co-related subqueries" are slower and should be avaoided where possible). e.g. following query lists the orders which are higher than average quantity of each product:

SELECT * FROM orders ord1 WHERE quantity > ( SELECT AVG(quantity) FROM orders ord2 WHERE ord2.product_code = ord1.product_code );

e) Subqueries as Anonymous Tables

An anonymous table is a virtual table that is created on the fly, it is basically just the resultset without a table-name, which is produced by a subquery and is used by the outter query as a table within its FROM clause, e.g. following virtual table holds only one row which is used by outter query:

SELECT * FROM (SELECT first_name, last_name FROM customers WHERE cust_code = 'A002') AS my_vitrual_tbl;

Note: When using a subquery as a (anonymous) table, you must give the subquery a table-alias otherwise you will get an error.

f) Subquery in FROM clause: You basically eliminate the need to create a new view or table by placing a sub query in the 'FROM' clause.

- BETWEEN operator:

(Range) BETWEEN operator defines a range, enabling you to search for data within a given range. It takes two target values separated by keyword AND. e.g. BETWEEN 12 AND 32;

- NOT operator (Negation):

Not operator negates a condition, while used with IN operators, NOT excludes the range inversing the effect.

- ENGINE:

the ENGINE keyword tells MySQL that tables should be stored in which "storage engine". See also: "Storage Engines of MySQL".

- Operator-Precedence:

# AND-OR-NOT: AND has a higher precedence than OR, which, in turn, has a higher precedence than NOT. To override the natural order of evaluation, you can put parentheses around conditions to indicate that they should be evaluated together. In fact, use of parantheses is always a good practice.

# ORDER BY-LIMIT: "ORDER BY" clause has a higher precedence than "LIMIT <int>" operator.

- DIV v/s '/' operator:

# Division (/) operator: / numeric operator always returns a decimal number, i.e. it does not truncate the result or round it to the nearest whole number.

# DIV operator: (similar to 'MOD' or remainder operator) always return an integer or whole number, that is, all decimals are simply discarded.

- LIKE operator (with "% and _" Wildcard Operators):

The LIKE operator works just like the = operator except it allows us to use wildcards in the string to be matched.

# An underscore (_) matches a single character, whereas the percent symbol (%) matches any number of characters. e.g. WHERE last_name LIKE 'L%'; will find all the contacts that have a last name beginning with letter 'L'.

Qu: How can I select a String-record in which a particular substring is contained?

Ans: Use the 'LIKE' operator with "%SUBSTRING%" as search-string.

# Case-Sensivity: As with other string operators, 'LIKE' operator also is not case sensitive unless you also use the 'BINARY' operator.

# Exclusion: Use NOT LIKE with a wildcard pattern to exclude matching records from the query result.

- GROUP BY:

"GROUP BY" clause is used to group the data in a query, it must be written after the WHERE clause and before any ORDER BY and must be used in combination with "aggregation functions". GROUP BY must be followed by column-names. The GROUP BY clause collates rows in which the column specified contains the same value so that many rows are grouped into a single row/cell. On data grouped in this way, you can apply an aggregate function (in SELECT statement), and that function will be evaluated for each table row that was included in the group. But remember that GROUP BY clause makes more sense when you have duplicate data / row in a column and when you select the same column-name that is being grouped. E.g. :

SELECT comany_name, COUNT(*) FROM orders GROUP BY comany_name; # finds the total number of orders for each company_name:

Note: When you want to group data on a calculated column, it's a good idea to use a column-alias to give the column a meaningful name.

SELECT DATE_FORMAT (order_date, '%M %Y') AS order_month, COUNT(*) FROM orders GROUP BY order_month ORDER BY order_date; # selects the number of orders grouped by month. Note Omitting the "ORDER BY" clause here will sort the result alphabetically (month name) instead of order_date.

Caution: Without an aggregate function, the GROUP BY clause simply causes MySQL to return only unique rows, just like the DISTINCT keyword. It's possible to group by more than one columns in the "GROUP BY" clause, but remember that grouping requires that all columns specified must contain some duplicate values. In other words, a query that includes a GROUP BY clause cannot select (or retrieve) nonaggregated columns that are not written in the GROUP BY clause. (aggregated-column means a column with an aggregated function).

Sub-clauses of GROUP BY

GROUP BY can further have 3 following sub-clauses:

1. Group By (Aggregate) Functions

2. Group By 'With Rollup' Modifier.

3. Group By HAVING.

1. Group By (Aggregate) Functions: sum(), average(), count, min/max() etc. (see "group-by-functions" in manual).

2. Group By 'WITH ROLLUP' Modifiers: The GROUP BY clause permits a WITH ROLLUP modifier that inserts extra rows holding the sum (or aggregated summary) of few rows above it. ROLLUP helps quary multiple levels especially for analysis (i.e. OLAP). (Note: WITH ROLLUP only works within 'GROUP BY' it cannot be used independently.)

3. HAVING Clause: Generally in order to filter result-set we usually use "WHERE <condition>" clause, in which we cannot use any aggregate-function (in <condition> of "WHERE" clause), whereas we can do so with "HAVING Clause." (Note: The HAVING clause must appear after the GROUP BY clause.)

# Following finds dates on which more than one order was placed.

SELECT order_date, COUNT(*) FROM orders GROUP BY order_date HAVING COUNT(*) > 1; -> 2006-01-23 | 2 |.

# Following finds products in which at least 30 units have been shipped.

SELECT product_code, SUM (quantity) as num_shipped FROM order_lines GROUP BY product_code HAVING num_shipped > 30; -> | MAXI | 48 |./

- AS :

# 'AS' keyword - to create an aliase: AS keyword can be used to format the title of a column heading in SELECT statement, this column-renaming is often called 'Aliase', e.g. SELECT price*VAT/100 AS 'Tax'; Note: we can use the column-alias in the other clauses such as: "ORDER BY", "GROUP BY" etc.

# 'AS' keyword - to create new table: To use a resultset (produced by a query) to create a new table, simply put a SELECT statement inside the CREATE TABLE command. The keyword AS is optional but can be included to make the command more readable. e.g.: CREATE TEMPORARY TABLE tmp_cust AS SELECT * FROM customer;

Important-Note: Keys (Primary, foreign keys etc.) are not copied when you create a table using a query (with 'AS' keyword), however MySQL does retain the data-types of the columns and any 'NOT NULL' constraints.

- CASE:

Used in place of multiple IF statements,

Syntax:

CASE

WHEN <condi> THEN <output>

WHEN <condi> THEN <output> ...

ELSE <output>

END

Example:

SELECT

CASE

WHEN PI() < 4 THEN 'smaller'

WHEN PI() > 4 THEN 'bigger'

ELSE 'equal'

END

; -> smaller.

- DDLs in MySQL:

# Creating Database:

~ CLI: Simply use 'CREATE DATABASE <dbname>' syntax, user must have the CREATE privilege to be able to create a database.

~ GUI: In query-browser, right-click in the blank area of "Schemata" > Click on "New Schema".

The optional attribute IF NOT EXISTS can be added to a CREATE DATABASE statement to avoid an error if the database already exists. Instead, a warning is generated, which you can view using the SHOW WARNINGS command.

# Altering Database: ALTER DATABASE <dbname>;

# Dropping Database: DROP DATABASE <dbname>;

* Internationalization in MySQL Databases:

Qu: What are Character-Sets, Collations and Maxlen?

Ans: MySQL supports different character-sets and collations to facilitate internationalization.

Collation: If you do not specify a collation with the COLLATE keyword, the default collation for a character setas shown in the output from SHOW CHARACTER SET will be used.

'COLLATE' keyword: A collation is a set of rules for comparing characters in a character-set, essentially, it defines the alphabetical order for a character set. For instance, the character Ö in a German character set is usually treated as OE for ordering purposes. Remember: string comparisons are not case sensitive unless you include the BINARY keyword.

Maxlen: The Maxlen column (in the result of SHOW CHARACTER SET) indicates the number of bytes required to store a character using each character set. Some complex alphabets require 2 or 3 bytes per character.

# I18n Commands:

To set the default character-set and collation for a new database, use the CHARACTER SET and COLLATION attributes in the CREATE DATABASE statement.

For instance, to create a new database using a Greek character-set and the greek_general_ci collation:

CREATE DATABASE greekdb CHARACTER SET greek COLLATE greek_general_ci;

To change greekdb to use the ASCII character set. You would issue the following command:

ALTER DATABASE greekdb CHARACTER SET ascii;

* Table-Constaints in MySQL:

# UNIQUE :

# PRIMARY KEY :

# AUTO_INCREMENT :

# NOT NULL :

* Storage Engines of MySQL:

· InnoDB: (provides transactional support by means of logging every transaction in log-files) is the default storage engine from MySQL 5.5 and above. InnoDB is a disk-based table handler, hence (unlike MyISAM) supports transactions-support. (Note: A permanent engine for MySQL can be set at installation of your MySQL server however you can pick an engine for each table at table-creation.)

· MyISAM: (does NOT provides transactional support) was the default storage engine for MySQL 5.1 and below. MyISAM stores each table on hard-drive in three files, with extensions .MYD (data), .MYI (index), and .frm. (ISAM stands for "Indexed Sequential Access Method").

· MEMORY (or HEAP): The MEMORY storage engine is a very fast storage engine that stores tables in memory only. However, because no data is written to disk, the contents of your tables are destroyed when the MySQL server is stopped. MEMORY is usually used for temporary tables.

· INFOBRIGHT: Infobright Community Edition is an open source Column-oriented DBMS for data warehousing and business intelligence. It also offers just its engine to be embedded in another DMBS which procides exceptional query performance for analytic applications against large amounts of data.

· BLACKHOLE: storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals from it always returns an empty result.

Using a storage engine:

the ENGINE keyword tells MySQL that tables should be stored in which "storage engine". The two table creation systaxes the ENGINE keyword is used:

1) Empty Table Creation: simply use ENGINE keyword at end, e.g.:

CREATE TABLE mytable ( id INTEGER PRIMARY KEY, name TEXT) ENGINE=InnoDB;

2) Table creation using a query (requires 'AS' keword): note that ENGINE keyword must appear before the query.

CREATE TEMPORARY TABLE allnames ENGINE=MEMORY AS (SELECT name FROM customer);

* Table Manipulation:

# CREATE TABLE <tablename> <tbl-definition>; or CREATE TABLE <tablename> AS <query>;

# DROP TABLE <tablename>;

# ALTER TABLE <tablename> ADD | CHANGE | MODIFY | DROP <column-definition>; You can ADD, MODIFY, or DROP a column on a live table (but be careful in altering tables).

~ ADD: Adds a new column, e.g.:

ALTER TABLE customer

ADD fax VARCHAR(12);

~ MODIFY or CHANGE: use MODIFY (or CHANGE) to rename a column as well as to change its definition.

Syntax:

ALTER TABLE tablename

CHANGE oldcolumnname newcolumnname type;

e.g.:

ALTER TABLE customer_contacts

CHANGE fax facsimile TEXT;

~ DROP: To drop a column from a table, e.g.:

ALTER TABLE customer

DROP fax;

Benchmarking MySQL Server:

Benchmarking refers to the assessment of the comparative performance of a software entity. This is typically done by running a number of tests against that entity.

In Data Warehouse (DW) following are benchmarked:

· Database architecture

· Hardware architecture

· ETL Processes

Skipping Query Cache

MySQL's query cashe is created once a query is executed, this means subsequent same query will run much faster skewing benchmarking results. Skipping MySQL query caching will help producing more accurate benchmarking results, however be aware that other cashes also impact performance. To skip query cache following can be done:

1. Use SQL_NO_CACHE option just preceding to the retrieving <column_list> in the query:

a. SELECT SQL_NO_CACHE * FROM <table>

2. Setting a session variable, remember this only affects the current connection and current session:

a. SET SESSION query_cache_type=0;

3. Resetting or flushing the query cache using commands:

a. RESET QUERY CACHE

b. FLUSH QUERY CACHE

4. Include to current time in query: Any reference to current date/time will disable the query cache for that selection:

a. SELECT *,NOW() FROM <table>

Other Caches:

A query always runs much faster second (or subsequent) time even after bypassing query cache (using the 'SQL_NO_CACHE' etc.). This is due to the other caches, such as:

· MySQL Caches:

o storage engines buffers: e.g. Innodb Buffer Pool and Key Buffer.

o table_cache: can affect query execution speed.

· OS Caches: (buffers in RAM or swap area on disk) Operation Systems typically cache file IO unless you explicitely bypass it by using O_DIRECT flag or mounting file system in direct IO mode.

· Hardware Caches:

o Hard-driver caches (HDD's inter buffer): Apart from RAID cache, the more important SAN caches can be pretty effective.

o CPU caches: affect execution speed only lightly.

Guaranteed cleaning of Caches:

For guaranteed cleaning of all MySQL Caches, restarting the MySQL server is the only way (which can be overly time consuming). However individual cashes can be flushed:

· MySQL table cache: can be flushed using FLUSH TABLES (but this won't flush Innodb table meta data)

· “set global key_buffer_size=0; instead of setting "global key_buffer_size=DEFAULT” to zero out key buffer. However, there is no way to clean Innodb Buffer Pool without restarting MySQL server. So as

· OS Caches can be flushed using OS commands.

· Hardware Cashes: might get cleaned if other I/O operation is performed, but not guaranteed.

MySQL Database Benchmarking Techniques

Database benchmarking normally includes the measurement of the throughput and response times. Following are toolset, functions and commands that can be used for benchmarking MySQL server:

MySQL Benchmark Suite

MySQL benchmark suite advises what operations perform well or poorly. This suite includes useful scripts and utilities that can be used to benchmark a MySQL database. These utilities includes: sql-bench, run-all-tests and crash-me.

MySQL Functions, Keywords and Schema

BENCHMARK() function

The built-in MySQL function Benchmark() is an excellent tool for benchmarking an operation.

Syntax: BENCHMARK (<time>, <query>)

Example: SELECT BENCHMARK(1000000, (SELECT * FROM mytable) );

EXPLAIN keyword:

The EXPLAIN provides basic information about an SQL statement, including execution plan and execution time.

1. EXPLAIN [EXTENDED] <SELECT statement>

displays information from the optimizer about the query execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order.

2. EXPLAIN <tbl_name>

a synonym for DESCRIBE keyword. Displays table's columns.

MySQL performance_schema:

The MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level. The tables in the performance_schema are queried to see real-time information about the performance characteristics of a MySQL server and the applications it is running.

Stress Test Tools

Problems often occur when a system is heavily loaded especially due to the issues of database architecture which can be identified and benchmarked using stress test (putting system under the worst possible load). There are number of stress test utilities that simulate a high load produced by multiple clients issuing queries simultaneously. such as: mysqlslap, Open Source Database Benchmark and SysBench.

Monitoring MySQL Server Performance

The set of threads currently executing within the MySQL server can be monitored while performing benchmarking tests. The information on the processes can be availed from the following sources:

· SHOW PROCESSLIST statement

· SHOW PROFILE statement

· INFORMATION_SCHEMA PROCESSLIST table

· 'mysqladmin processlist' command

Information_Schema in MySQL:

In MySQL 5.02 and above, an additional built-in database called "information_schema" is available, which provides access to the database metadata. This includes: database-names, table-names, data types of columns, access privileges etc. The MySQL server automatically populates the tables in the information_schema, which you can only query but cannot change.

SQL Queries to monitor the growth of data:

All the databases:

· SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, INDEX_LENGTH, DATA_FREE as UNUSED_SPACE, CREATE_TIME, UPDATE_TIME, TABLE_COMMENT
FROM information_schema.TABLES;

Single database:

· SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CARDINALITY
FROM information_schema.STATISTICS S
WHERE TABLE_SCHEMA = "rcc_risks" AND TABLE_NAME = "tbl_postcode";

Note: Cardinality is the uniqueness of data values in a column (attribute). Low-cardinality means a lot of duplicated elements in a column, whereas high-cardinality means more uniqueness (such as primary key column).

Tables & Columns in information_schema (Commonly Used)

Table Name	Column Name	Usage / Comments	Example Query
SCHEMATA	SCHEMA_NAME	Displays all databases. Same as 'SHOW DATABASES' command.	SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA;
TABLES	TABLE_SCHEMA	Displays all databases.	SELECT TABLE_SCHEMA,TABLE_NAME FROM `TABLES`;
TABLES	TABLE_NAME	Displays all databases. Same as 'SHOW TABLES' command.	SELECT TABLE_SCHEMA,TABLE_NAME FROM `TABLES`;
COLUMNS	TABLE_SCHEMA, TABLE_NAME	Displays all databases & tables.	SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM COLUMNS;
COLUMNS	COLUMN_NAME	Displays all columns. Same as 'DESC <tbl_name>' command.	SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM COLUMNS;

* Environment Variables in mysql client:

- show VARIABLES; Displays all the environment variables set in mysql client, e.g.: SHOW VARIABLES LIKE "%version%"; will display only string matching with %version%.

* Retrieval commands:

- Show Databases; Displays available databases.

- Show Tables; Displays available tables, e.g. SHOW TABLES FROM trialdb;

- Desc <tablename>; Displays available columns in specified tables, the 'DESCRIBE' command is shortcut to 'SHOW COLUMN FROM' command.

- LIMIT <[skip_rows], display_rows>: Limits number of rows in retrieved-resultset, the 1st [optional] argument by default always has value 0, it specifies number of rows to omit, 2nd argument sets starting-point from where rows will be displayed. e.g.: LIMIT 3; is equal to: LIMIT 0,3; meaning skip 0 rows.

* MySQL Functions (commonly used):

INFORMATION FUNCTIONS:

- STATUS; Displays the current status of mysql client, including current user, database, version, connection etc.

- DATABASE() Returns currently being used database-name. If no database is in use then returns NULL. e.g.: select DATABASE();

- USER() Returns the current MySQL "username and hostname". e.g. select substring_index(USER(),'@',1);

- VERSION() Returns MySQL server version (as string). e.g. select VERSION();

NUMERIC / MATH FUNCTIONS:

- RAND() Returns a random double/decimal number between 0 and 1. RAND() function can be used in "ORDER BY" clause to randomly shuffle the order in which records are returned. Simply use ORDER BY RAND(); and the resultset will be sorted randomly.

- ROUND(num [, num_of_decimals]) Rounds off 1st argument, 2nd optional argument specifies number of digits to display after decimal point. e.g. ROUND(3.76, 1) -> 3.8.

- CEILING() & FLOOR() Round a decimal number up and down to the nearest integer, respectively. e.g. CEILING(-2.6) returns -2 (not -3 because -2 is greater than -3).

- TRUNCATE(num [, num_of_decimals]) Truncates 1st argument to (2nd argument) specified number of digits to display after decimal point. e.g. TRUNCATE(3.76, 1) -> 3.7.

- POW(num, to_power_of) Raise 1st arg to the power of 2nd arg. e.g. POW(4,2) -> 16.

- SQRT(num) Returns square root of passed number. e.g. SQRT(POW(8,2)) -> 8.

- PI() Returns value of pi, e.g. PI() -> 3.141593.

CONDITIONAL FUNCTIONS:

- IF() (Similar to ternary function (condi?true:false)) e.g. select IF(4 < PI(), 'yes', 'no'); -> no.

- NULLIF() Returns NULL if passed two arguments are equal/same, otherwise returns 1st argument. e.g. NULLIF(PI(), 3.141593); -> NULL.

STRING FUNCTIONS:

- CONCAT() Joins two or more strings. CONCAT('exam', 'ple') -> 'example', beware that if you concatenate NULL with anything, all you will get is NULL.

- CONCAT_WS() Joins strings with a space character (as 1st arg) to separate the words in the result (WS = "with separator"). e.g. CONCAT_WS('-', 'Catch', 'it', 'up') -> Catch-it-up. (Note: In - MySQL '+' or '||' symbols can NOT be used for concatenation, you must use the CONCAT() function.)

- LTRIM(<string>) & RTRIM(<string>) Removes white-spaces from the left/right of the string.

- TRIM(LEADING | TRAILING | BOTH <char> FROM <string>) Enables you to trim other characters than whitespace from either or both ends of a string. The keywords LEADING, TRAILING, and BOTH are used to specify which part of the string to trim, e.g.: TRIM(LEADING '/' FROM '/dev/null') -> dev/null

- LPAD(<string>, fixed_length, <char_to_pad>) & RPAD() Pads a string (1st arg), to make upto specified fixed-length (2nd arg), padding with specified character (3rd arg) on the left and right sides, respectively. e.g. RPAD('ed', 5, '.') -> ed...

- LENGTH(<String>) Returns total number of characters in a string.

- LOCATE(<str_to_find>, <String>) Returns the position of a char/substring within a string. returns zero (0) if it is not found (same as indexOf() from JavaScript). e.g. LOCATE('@', "ed@site.com") -> 2.

- REPLACE(<String>, 'replace_this', 'with_this') Replaces one substring with another within a string. e.g. REPLACE("ed@site.com", '@', ' at ') -> ed at site.com.

Using replace within a SQL command e.g.:

UPDATE tbl_contacts SET contact_phone = REPLACE(contact_phone, '01323 46','')

- SUBSTRING(<string>, <int_from_position>, <int_to_position>) Returns the portion of the string beginning at the specified position, up to the specified length. Both position and length start with 1, they are not 0 based. To extract a fixed length substring use SUBSTRING(), e.g. for last three characters specify int_from_position as -3 i.e. SUBSTRING('abcdef', -3) -> def.

- SUBSTRING_INDEX(<string>, <delimiter_char>, <length>) Break up then returns the substring using the delimiter upto specified number of characters, (instead of using clumsy combination of LOCATE() within SUBSTRING() function). e.g. SUBSTRING_INDEX('www.aditya.com', '.',-1); -> com. This function is very useful when you don't know the fixed positioning.

- LOWER(<str>) & UPPER(<str>) Converts a string to lower or upper case.

DATE FUNCTIONS:

- CURDATE(), CURTIME(), NOW(): CURDATE() returns the current date; CURTIME() returns the current time, whereas, NOW() function returns BOTH current date and time as a single value. The actual date and time value is determined by system's time zone setting, i.e. NOW() on two separate servers in different parts of the world will return two different values.

- UTC_DATE(), UTC_TIME() & UTC_TIMESTAMP(): UTC_DATE() & UTC_TIME() return the current Coordinated Universal Time (same vas GMT) date and time respectively, whereas UTC_TIMESTAMP() returns a single date and time value.

- CONVERT_TZ(<data_object>, <from_time_zone>, <to_time_zone>): Returns the date converted from the first time zone to the second, e.g.: CONVERT_TZ('2004-01-01 12:00:00', 'US/Pacific', 'US/Eastern'); -> 2004-01-01 15:00:00.

- DATE_FORMAT(<datetime_object>, <date_time_format>) Displays a date in customised format. See: "Date Time Format Characters".

- EXTRACT(<datetime_unit> FROM <datetime_object>); Returns a passed datatime_unit as numeric result (unlike DATE_FORMAT() that returns "formatted-string"). Examples:

EXTRACT(DAY FROM NOW()) -> 28.

EXTRACT(YEAR_MONTH FROM NOW()) -> 200510

- DATEDIFF(<datetime_object>, <datetime_object>) Returns the number of days between the second date and the first. e.g. SELECT DATEDIFF('2005-12-31', '2006-01-01'); -> -1.

- TIMEDIFF() Returns a time value after calculating the difference between two times. e.g.: TIMEDIFF('12:00', '10:30') -> 01:30:00

- UNIX_TIMESTAMP() Returns the current time stamp value (from Epoch). e.g. : UNIX_TIMESTAMP('2006-01-01 12:45:31') -> 1136119531.

- FROM_UNIXTIME() Returns after converting passed time-stamp into a MySQL date. e.g. : FROM_UNIXTIME(1136119531) -> 2006-01-01 12:45:31.

AGGREGATION FUNCTIONS

- COUNT(): Counts values in a query.

# COUNT(<col-name>): Counts a single column passed in parentheses.

# COUNT(*) returns the total number of rows returned by a query, If the data in that column is NULL, it is not counted.

# COUNT(DISTINCT <col-name>): To find the number of unique values in a column.

- SUM():

# SUM(<col-name> | <expr>): Computes a total from the values in a specified column or from expression. e.g. SUM(quantity * 4) -> 48.

# SUM(<condi>): We can pass conditional expression in parantheris to get the number of rows that meet the condition. Because the expression evaluates to 1 each time the condition is true and 0 each time it is false, hence query finds the number of cells that satisfy the criteria. e.g. SUM(weight < 5) -> 2.

- AVG(): Computes (a mean) average of the data in a specified columns, e.g. AVG(weight) -> 5.5, this is same as SUM(num) / COUNT(num). (NULL values are excluded when computing an average with AVG()).

- MIN() & MAX(): Return the smallest and greatest values from a column, respectively, e.g. MIN(price) -> 2.00.

* Date Datatypes in MySQL:

See table (in external file under EdNotes) "MySQL Data-types Summary".

- INTERVAL <datetime_unit>: Always use INTERVAL keyword to perform all date arithematics. e.g.:

'2005-06-01' + 10; -> 2015.

'2005-06-01' + INTERVAL 10 DAY; -> 2005-06-11.

- List of units (used with INTERVAL): MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR. Remember all datetime_units are written as singular (without 's').

Compound-units: Number of compound-units are made up of two units (separated by an underscore). e.g. to add 2 hours and 30 minutes we can use HOUR_MINUTE unit:

SELECT '2005-12-31 22:30' + INTERVAL '2 30' HOUR_MINUTE;

Note: when using a compound unit, the two unit values must be enclosed in single quotes and separated using a space character.

- Date Time Format Characters

Date Format Characters Used by DATE_FORMAT()

%a : Abbreviated weekday name (Sun,Sat)

%b : Abbreviated month name (Jan,Dec)

%c : Month number, no leading zero (1,12)

%D : Day of the month with ordinal suffix (1st,31st)

%d : Day of the month (1,31)

%j : Day of the year, three digits (001,366)

%M : Month name (January,December)

%m : Month number, two digits (01,12)

%U : Week number using Sunday as first day of week (01,53)

%u : Week number using Monday as first day of week (01,53)

%W : Weekday name (Sunday,Saturday)

%Y : Year, four digits

%y : Year, two digits

%% : To include a literal percent character in a date format string, use %%.

Example: DATE_FORMAT(NOW(), 'The date is %W %D %M %Y'); -> The date is Friday 28th October 2005.

Time Format Characters Used by DATE_FORMAT()

%H : Hour on 24-hour clock, two digits (00,23)

%h : Hour on 12-hour clock (01,12)

%i : Minutes, two digits (01,59)

%l : Hour on 12-hour clock, no leading zero (1,12)

%p : A.M. or P.M.

%r : Time on 12-hour clock as HH:MM:SS

%s : Seconds, two digits (00,59)

%T : Time on 24-hour clock as HH:MM:SS

Example: DATE_FORMAT(NOW(), 'The time is %h:%i:%s %p'); -> The time is 04:19:36 PM.

GK-MySQL-Administrator-Tool / GK-MySQL Admin Tool:

The MySQL Admin Tool comes very handy for MySQL database administration, which includes following features:

- Server Info: Displays currently running database's version, user-name, host-name, IP address etc.

- Start/Stop MySQL "Service", (see also: Staring/Stopping/Restarting-Mysql-Server)

- Startup Parameters.

- User Admin.

- Server Connections.

- Backup & Restore.

- Catalogs: Displays: (on LeftNav:) All Schemata, (on RightPane) Table Names, Engine, Rows, Data Length, Index Length, Update Time and all the aggregations totals at the bottom.

* Server-Connections: To see currently running database connections goto: "Server Connections", here you can monitor the currently running database connections threads. Here you can "Kill Thread" or "Kill User".

* For import-export see: "MySQL: Import - Export".

GK-MySQL-Query-Browser-Tool / GK-MySQL Query Browser Tool:

* Trouble-Shooting-Query-Browser:

Bug about Saving-Blob: Query browser does not save changes if you clicked "Apply Changes" (after clicking "Start Editing" at the bottom-bar), solution is to follow the steps below:

- Click on "Start Editing" button.

- Click on (a small) floppy-save-icon (at the begining of that row), and temporary save that row as sql script.

- Make (manual) changes in that (exported) sql script file.

- Now back in Query Browser, open the altered script file by clicking on (the small) open-icon on that row.

- Click on "Apply Changes", and that's it.

MySQL-Trouble-Shooting:

MySQL-Error-Codes:

· Error #1064: Syntax error: often missing ; or , in wrong place. Remember in a multi-line statement tne Line number shown will be the 1st line.

· Errno 150: Can't create table: Column in a table that is being used as a Foreign key in another table, must be indexed (It is required to police the FOREIGN KEY constraint).

----------------- x -----------------

List of my IT blogs:

List of my other blogs:

Steps to heal tennis elbow

Monday, 7 January 2013

MySQL Data Types Summary, General Tips & Useful Notes

Table of Contents

Introduction

Numeric with Decimals

Numeric without Decimals (Whole Numbers)

Text String

Binary String

Special String

Date/Time

Additional Data-Type Notes

References

General MySQL tips and notes

Import - Export using CLI (Command Line Interface):

EXPORTING:

IMPORT:

IMPORTING XML: Loading xml data

DATA MIGRATION - MS Access to MySQL:

Other Copying Methods:

* Online Help Trick (for MySql):

MySQL Configuration

* Location of MySQL installation & libraries on Linux

* MySQL Directory Structure on Linux

* my.cnf / my.ini:

bind-address parameter my.ini:

* Connecting to MySQL-server using CLI:

The 'mysql' CLI Client Program:

* Anonymous Connections to MySQL:

* MySQL Default Port:

MySQL Commandline Parameters

Locating MySQL binary (in Linux):

Output Format for Query-Results:

* Running SQL-Script-File in MySQL:

MySQL Question - Answers (from book):

- Why do we need DBMSs at all, can't we just use flat files or XML files?

- Why MySql:

- Is MySQL case-sensitive?

- How to perform a case-sensitive search on data stored in a MySQL database table?

- Can WHERE clause's key-values be inverted?

- Can we write string-values without quotes?

- When using "ORDER BY" clause, do I need to compulsorily retrieve the column on which query-result is being sorted?

- Can I sort query-results in descending order based on multiple columns?

- Is it compulsory to use the dummy table called 'dual' to mimic a real-table?

- Can I use equality operator (=) with NULL?

* Facts about MySQL:

- Naming Convention:

- Boolean:

- NULL:

- Date Time Formats:

- Auto Type Conversion:

- BINARY:

- Alphabet-Range:

- Comments in MySQL:

Examples of all 3 ways to comment with in code:

Quotes in MySQL statments

* MySQL Commands List (commonly used):

Show Commands:

Table Commands

Data Commands

Privilege Commands

* SQL-Joins (Joining Table & Multiple-Queries):

Joining Table:

- Equijoin or inner-join:

- Self-Joins:

- Natural Joins:

- Outer Joins:

# LEFT v/s RIGHT OUTER JOINs:

Joining Multiple-Queries:

- UNION:

- MINUS or EXCEPT:

- INTERSECT:

- Using Temporary Tables:

* Operators, Keywords & Constructs (MySQL & SQL Clauses):

- UNIQUE:

- AND v/s OR Operator:

- IN Operator:

- BETWEEN operator:

- NOT operator (Negation):

- ENGINE:

- Operator-Precedence: