mysql_quick_guide

A quick guide to MySQL

A quick guide to MySQL

MySQL, the most popular Open Source SQL database management system, is developed, distributed, and supported by Oracle Corporation.

Main features of MySQL

  • Written in C and C++.
  • Tested with large range of different compilers.
  • Works on many cross platforms.
  • Provides transactional and non-transactional storage engines.
  • Uses a very fast thread-based memory allocation system.
  • Executes very fast joins using an optimized nested-loop join.
  • Implements in-memory hash tables, which are used as temporary tables.
  • And there are many more(Read here).

Basic MySQL Commands

Access MySQL server from mysql client using a username and password (MySQL will prompt for a password):

[pastacode lang=”sql” manual=”mysql%20%20-u%20%20%5Busername%5D%20%20-p%3B” message=”Access MySQL server from mysql client using a username and password (MySQL will prompt for a password):” highlight=”” provider=”manual”/]


MySQL Data Types

String data Type in mysql

Data Type Syntax Explanation
CHAR(size) Where size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters.
VARCHAR(size) Where size is the number of characters to store. Variable-length string.
TINYTEXT(size) Where size is the number of characters to store.
TEXT(size) Where size is the number of characters to store.
MEDIUMTEXT(size) Where size is the number of characters to store.
LONGTEXT(size) Where size is the number of characters to store.
BINARY(size) Where size is the number of binary characters to store. Fixed-length strings. Space padded on right to equal size characters.
(Introduced in MySQL 4.1.2)
VARBINARY(size) Where size is the number of characters to store. Variable-length string.
(Introduced in MySQL 4.1.2)

Numeric Datatypes in mysql

Data Type Syntax Explanation
BIT
TINYINT(m)
SMALLINT(m)
MEDIUMINT(m)
INT(m)
INTEGER(m) This is a synonym for the INT datatype.
BIGINT(m)
DECIMAL(m,d) Where m is the total digits and d is the number of digits after the decimal.
DEC(m,d) Where m is the total digits and d is the number of digits after the decimal.

This is a synonym for the DECIMAL datatype.

NUMERIC(m,d) Where m is the total digits and d is the number of digits after the decimal.

This is a synonym for the DECIMAL datatype.

FIXED(m,d) Where m is the total digits and d is the number of digits after the decimal.
(Introduced in MySQL 4.1)This is a synonym for the DECIMAL datatype.
FLOAT(m,d) Where m is the total digits and d is the number of digits after the decimal.
DOUBLE(m,d) Where m is the total digits and d is the number of digits after the decimal.
DOUBLE PRECISION(m,d) Where m is the total digits and d is the number of digits after the decimal.

This is a synonym for the DOUBLE datatype.

REAL(m,d) Where m is the total digits and d is the number of digits after the decimal.

This is a synonym for the DOUBLE datatype.

FLOAT(p) Where p is the precision.
BOOL Treated as a boolean data type where a value of 0 is considered to be FALSE and any other value is considered to be TRUE.
BOOLEAN Treated as a boolean data type where a value of 0 is considered to be FALSE and any other value is considered to be TRUE.

Date/Time Datatypes in mysql

Data Type Syntax Explanation
DATE Displayed as ‘YYYY-MM-DD’.
DATETIME Displayed as ‘YYYY-MM-DD HH:MM:SS’.
TIMESTAMP(m) Displayed as ‘YYYY-MM-DD HH:MM:SS’.
TIME Displayed as ‘HH:MM:SS’.
YEAR[(2|4)] Default is 4 digits.

Large Object (LOB) Datatypes in mysql

Data Type Syntax Explanation
TINYBLOB
BLOB(size) Where size is the number of characters to store (size is optional and was introduced in MySQL 4.1)
MEDIUMBLOB
LONGTEXT

MySQL Select Queries

Here you will see various select statement used in mysql.

[pastacode lang=”sql” manual=”SELECT%20*%20FROM%20table_name%3B” message=”Selecting all data from a table” highlight=”” provider=”manual”/]

[pastacode lang=”sql” manual=”SELECT%20col1%2C%20col2%E2%80%A6.%0AFROM%20table_name%3B” message=”Selecting specific columns from a table” highlight=”” provider=”manual”/]

[pastacode lang=”sql” manual=”SELECT%20DISTINCT%20(column)%0AFROM%20table_name%3B” message=”Selecting unique records” highlight=”” provider=”manual”/]

[pastacode lang=”markup” manual=”SELECT%20*%0AFROM%20table%0AWHERE%20condition%3B” message=”Selecting data using where clause” highlight=”” provider=”manual”/]

[pastacode lang=”markup” manual=”SELECT%20column_1%20AS%20new_column_1%2C%20…%0AFROM%20table_name%3B” message=”Selecting data using alias” highlight=”” provider=”manual”/]

 

[pastacode lang=”sql” manual=”SELECT%0A%20productCode%2C%0A%20productName%2C%0A%20textDescription%0AFROM%0A%20products%20T1%0AINNER%20JOIN%20productlines%20T2%20ON%20T1.productline%20%3D%20T2.productline%3B” message=”Select data using Inner Join on two tables” highlight=”” provider=”manual”/]

[pastacode lang=”sql” manual=”SELECT%20col1%2C%20col2%2C%20…%0AFROM%20table%0AORDER%20BY%20col1%20ASC%20%5BDESC%5D%2C%20col2%20ASC%20%5BDESC%5D%2C…%3B” message=”Selecting data using ORDER BY clause” highlight=”” provider=”manual”/]


CREATE and MODIFY IN MySQL

Now we will CREATE and MODIFY statements in MySQL.

[pastacode lang=”sql” manual=”CREATE%20DATABASE%20db_name%3B” message=”CREATE a database” highlight=”” provider=”manual”/]

[pastacode lang=”sql” manual=”USE%20db_name%3B” message=”SELECT a database” highlight=”” provider=”manual”/]

[pastacode lang=”sql” manual=”SHOW%20DATABASES%3B” message=”List the databases on the DB server” highlight=”” provider=”manual”/]

[pastacode lang=”sql” manual=”DESCRIBE%20Table_name%3B” message=”Describe a Table” highlight=”” provider=”manual”/]

[pastacode lang=”sql” manual=”CREATE%20TABLE%20authors%20(%0Aauthor_id%20INT%20NOT%20NULL%20AUTO_INCREMENT%2C%0Aauthor_name%20VARCHAR(20)%2C%20%0Aemail%20VARCHAR(20)%0APRIMARY%20KEY%20(author_id))%3B” message=”Create a table in MySQL” highlight=”” provider=”manual”/]

[pastacode lang=”markup” manual=”INSERT%20INTO%20authors%20(author_id%2C%20author_name%2Cemail)%20VALUES(101%2C%22Aswathy%22%2C%22aswathy11%40abc.com%22)%3B” message=”Inserting a single row in Table ” highlight=”” provider=”manual”/]

some more section will be added soon….

 

One Reply to “A quick guide to MySQL”

Leave a Reply