w3mentor
Learning web technologies simplified!
 
Home / MYSQL / Archive by category 'MYSQL Basics'

MYSQL Basics

What is the First Normal Form in SQL

For a database to satisfy the First Normal Form, it must fulfill three requirements:
1. There should be no repeating columns containing the same kind of data.
2. All columns should contain a single value.
3. There should be a primary key to uniquely identify each row.

Example of IN and NOT IN SQL Queries

SELECT id
FROM stats
WHERE POSITION IN ('Manager', 'Staff')
SELECT ownerid, 'is in both orders & antiques'
FROM orders, antiques WHERE ownerid = buyerid
UNION
SELECT buyerid, 'is in antiques only'
FROM antiques WHERE buyerid NOT IN (SELECT ownerid FROM orders)

Join two MySQL tables together

SELECT 
       a.column1, 
       b.column1, 
       b.column2                          
 FROM 
       tablename a 
        JOIN
       tablename b 
         ON
       a.column1 = b.column1
       WHERE a.column1 LIKE '%something%'

Using MySQL SELECT to count rows in a table

MySQL count() Returns a count of the total number of rows in a table.

SELECT COUNT(*) from TableName

Using MySQL SELECT to return first 10 rows

To return the first 10 rows we should run a SQL Select statement and limit the number of rows returned. This is useful when we only want to return a few rows to do some initial analysis.

SELECT 
    column1, column2, column3
FROM 
    tblname
LIMIT 10

MySQL SELECT all columns

MySQL Select star return all columns.

SELECT 
   *  
FROM TblName

Advantages of using stored procedures

The use of stored programs can lead to a more secure database.

Stored programs offer a mechanism to abstract data access routines, which can improve the maintainability of your code as underlying data structures evolve.

Stored programs can reduce network traffic, because the program can work on the data from within the server, rather than having to transfer the data across the network.

Stored programs can be used to implement common routines accessible from multiple applicationspossibly using otherwise incompatible frameworksexecuted either within or from outside the database server.

Database-centric logic can be isolated in stored programs and implemented by programmers with more specialized, database experience.

The use of stored programs can, under some circumstances, improve the portability of your application.

What are triggers?

Triggers are stored programs that are activated in response to, or are triggered by, an activity within the database. Typically, a trigger will be invoked in response to a DML operation (INSERT, UPDATE, DELETE) against a database table. Triggers can be used for data validation or for the automation of denormalization.

CREATE VIEW privilege in MySql

In the MySql security model, the ability to create views requires a greater privilege than normally provided to create tables.

Create user and grant privileges:

CREATE USER test@localhost IDENTIFIED BY ‘test123’;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE ON products.* TO test@localhost;

Running the following command will error out since the CREATE VIEW privilege is not given to the user.

CREATE VIEW active_items AS
SELECT i_id AS id, s_nm AS name FROM products WHERE s_nm = `eatables’;

The error that will be generated is

ERROR 1142 (42000): CREATE VIEW command denied to user ‘test’@’localhost’ for table ‘products’

To grant privileges to create views we use the following statement:

CREATE USER test@localhost IDENTIFIED BY ‘test123’;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,CREATE VIEW ON products.* TO test@localhost;

Find date difference in MySQL

We can use MySQL datediff function to find the number of days between two dates.

Prior to MySQL 4.1:

SELECT TO_DAYS('2010-11-08') - TO_DAYS('2010-07-26');

Output:
105

MySQL 4.1.1+:

SELECT DATEDIFF('2010-11-08','2010-07-26');

Output:
105

Find difference in dates in mysql

We can use the TIMEDIFF and TIME_TO_SEC functions to find the time difference in seconds.

SELECT TIME_TO_SEC(TIMEDIFF('2010-01-09 10:24:46','2010-01-09 10:23:46'));

Installing MySQL on Linux/Unix

The recommended way to install MySQL on a Linux system is via RPM. MySQL AB makes the following RPMs available for download on its web site:

MySQL – The MySQL database server, which manages databases and tables, controls user access, and processes SQL queries.

MySQL-client – MySQL client programs, which makes it possible to connect to, and interact with, the server.

MySQL-devel – Libraries and header files that come in handy when compiling other programs that use MySQL.

MySQL-shared – Shared libraries for the MySQL client

MySQL-bench – Benchmark and performance testing tools for the MySQL database server.

The MySQL RPMs listed here are all built on a SuSE Linux system, but they.ll usually work on other Linux variants with no difficulty.

Now follow the following steps to proceed for installation:

Login to the system using root user.

Switch to the directory containing the RPMs:

Install the MySQL database server by executing the following command. Remember to replace the filename in italics with the file name of your RPM.

[root@host]# rpm -i MySQL-5.0.9-0.i386.rpm
Above command takes care of installing MySQL server, creating a user of MySQL, creating necessary configuration and starting MySQL server automatically.

You can find all the MySQL related binaries in /usr/bin and /usr/sbin. All the tables and databases will be created in /var/lib/mysql directory.

This is optional but recommended step to install the remaining RPMs in the same manner:

[root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm

Escape special characters in SQL statements

There are a number of special characters that needs to be escaped , if you want to include them in a character string.

Example character escaping rules:

  • The escape character (\) needs to be escaped as (\\).
  • The single quote (‘) needs to be escaped as (\’) or (”) in single-quote quoted strings.
  • The double quote (“) needs to be escaped as (\”) or (“”) in double-quote quoted strings.
  • The wild card character for a single character (_) needs to be escaped as (\_).
  • The wild card character for multiple characters (%) needs to be escaped as (\%).
  • The tab character needs to be escaped as (\t).
  • The new line character needs to be escaped as (\n).
  • The carriage return character needs to be escaped as (\r).

Here are some examples of how to include special characters:

SELECT 'It''s Sunday!' FROM DUAL;

Output:
It’s Sunday!

SELECT 'Hello, Nam\'este ree.' FROM DUAL;

Output:
Hell, Nama’est ree.

SELECT 'Mon\tTue\tWed\tThu\tFri' FROM DUAL;

Output:
Mon Tue Wed Thu Fri

Including comments in SQL Statements

To include comments in a SQL statement, we use the “–” symbol, then enter your comment until the end of the line.

SELECT 'Hello world!' FROM DUAL; -- My first SQL statement!
INSERT INTO tutorials VALUES ('w3mentor.com'); -- Top rated!
CREATE TABLE tutorial (
   id INTEGER, -- primary key
   title VARCHAR(80) -- tutorial title
);

Evaluate expressions with SQL Statements

we can use the “SELECT expression FROM DUAL” statement to return the calculated value of an expression. “DUAL” is a dummy table in the server. Below are a few examples of evaluating expressions:

Example code:

SELECT 'Hello world!' FROM DUAL;

Output:
Hello world!

SELECT (1+2)*3/4 FROM DUAL;

Output:
2.2500

SELECT TRUE FROM DUAL;

Output:
1

SELECT TRUE AND FALSE FROM DUAL;

Output:
0

SELECT TIME(SYSDATE()) FROM DUAL;

Output:
21:30:26

Date and Time data types in MySql

MySQL supports the following date and time data types:

  • DATE – A date in the range of ’1000-01-01′ and ’9999-12-31′. Default DATE format is “YYYY-MM-DD”.
  • DATETIME – A date with the time of day in the range of ’1000-01-01 00:00:00′ and ’9999-12-31 23:59:59′. Default DATETIME format is “YYYY-MM-DD HH:MM:SS”.
  • TIMESTAMP – A timestamp. The range is ’1970-01-01 00:00:00′ to partway through the year 2037. Default DATETIME format is “YYYY-MM-DD HH:MM:SS”.
  • TIME – A time. The range is ‘-838:59:59′ to ’838:59:59′. Default TIME format is “HH:MM:SS”.
  • TIME – A time. The range is ‘-838:59:59′ to ’838:59:59′. Default TIME format is “HH:MM:SS”.
  • YEAR – A year in 4 digits in the range of 1901 and 2155. Default YEAR format is “YYYY”.

Numeric Data Types in MySql

MySQL supports the following numeric data types:

  • BIT(n) – An integer with n bits.
  • BOOL same as BOOLEAN – Boolean values stored in 1 bit.
  • TINYINT – A small integer stored in 1 byte.
  • SMALLINT – A small integer stored in 2 bytes.
  • MEDIUMINT – A medium integer stored in 3 bytes.
  • INT same as INTEGER – An integer stored in 4 bytes.
  • BIGINT – An integer stored in 8 bytes.
  • FLOAT – A single-precision floating-point number stored in 4 bytes.
  • DOUBLE same as DOUBLE PRECISION – A double-precision floating-point number stored in 8 bytes.
  • REAL – Same DOUBLE by default.
  • DECIMAL(m,d) – A fixed-point number with m as the precision (total number of digits) and d as the scale (number of digits after the decimal point).

Differences between BINARY and VARBINARY in MySql

Both BINARY and VARBINARY are both binary byte data types. But they have the following major differences:

  • BINARY stores values in fixed lengths. Values are padded with 0×00.
  • VARBINARY stores values in variable lengths. Values are not padded with any bytes. But 1 or 2 extra bytes are added to store the length of the data.

Differences between CHAR and NCHAR in MySql

Both CHAR and NCHAR are fixed length string data types. But they have the following differences:

  • CHAR’s full name is CHARACTER.
  • NCHAR’s full name is NATIONAL CHARACTER.
  • By default, CHAR uses ASCII character set. So 1 character is always stored as 1 byte.
  • By default, NCHAR uses Unicode character set. NCHAR data are stored in UTF8 format. So 1 character could be stored as 1 byte or upto 4 bytes.
  • Both CHAR and NCHAR columns are defined with fixed lengths in units of characters.

The following column definitions are the same:

CREATE TABLE tableName (columnName NCHAR(80));
CREATE TABLE tableName (columnName NATIONAL CHAR(80));
CREATE TABLE tableName (columnName NATIONAL CHARACTER(80));
CREATE TABLE tableName (columnName CHAR(80) CHARACTER SET utf8);
CREATE TABLE tableName (columnName CHARACTER(80) CHARACTER SET utf8);

Types of string data in MySql

MySQL supports the following string data types:

  • CHAR(n) same as CHARACTER(n) – Fixed width and ” ” padded characters strings. Default character set is ASCII.
  • NCHAR(n) same as NATIONAL CHARACTER(n) – Fixed width and ” ” padded character strings with UTF8 character set.
  • VARCHAR(n) same as CHARACTER VARYING(n) – Variable width character strings. Default character set is ASCII.
  • NVARCHAR(n) same as NATIONAL CHARACTER VARYING(n) – Variable width character strings with UTF8 character set.
  • BINARY(n) – Fixed width and 0×00 padded byte strings.
  • VARBINARY(n) same as BINARY VARYING(n) – Variable width byte string.
  • TINYBLOB – BLOB (Binary Large Object) upto 255 bytes.
  • BLOB – BLOB (Binary Large Object) upto 64K bytes.
  • MEDIUMBLOB – BLOB (Binary Large Object) upto 16M bytes.
  • LONGBLOB – BLOB (Binary Large Object) upto 4G bytes.
  • TINYTEXT – CLOB (Binary Large Object) upto 255 characters.
  • TEXT – CLOB (Binary Large Object) upto 64K characters.
  • MEDIUMTEXT – CLOB (Binary Large Object) upto 16M characters.
  • LONGTEXT – CLOB (Binary Large Object) upto 4G characters.
  • ENUM – An enumeration to hold one entry of some pre-defined strings.
  • SET – A set to hold zero or more entries of some pre-defined strings.

Latest Tutorials & Code Snippets

Reference Guides