MySQL is a multithreaded, multi-user SQL database management system which has more than 11 million installations. This is the world's second most popular and widely used open source database.
MySQL has the following technical specifications -
There are only six Triggers allowed to use in MySQL database.
Tables that are present in memory is known as HEAP tables. When you create a heap table in MySQL, you should need to specify the TYPE as HEAP. These tables are commonly known as memory tables. They are used for high speed storage on temporary basis. They do not allow BLOB or TEXT fields.
BLOB is an acronym stands for binary large object. It is used to hold a variable amount of data.
There are four types of BLOB.
The differences among all these are the maximum length of values they can hold.
TEXT is case-insensitive BLOB. TEXT values are non-binary strings (character string). They have a character set and values are stored and compared based on the collation of the character set.
There are four types of TEXT.
A trigger is a set of codes that executes in response to some events.
Heap tables: Heap tables are found in memory. They are used for high speed storage on temporary basis. They do not allow BLOB or TEXT fields.
Heap tables do not support AUTO_INCREMENT.
Indexes should be NOT NULL.
The temporary tables are used to keep the temporary data. Sometimes it is very useful in cases to keep temporary data. Temporary table is deleted after current client session terminates.
The heap tables are shared among clients while temporary tables are not shared.
Heap tables are just another storage engine, while for temporary tables you need a special privilege (create temporary table).
FLOAT stores floating point numbers with accuracy up to 8 places and allocates 4 bytes, on the other hand DOUBLE stores floating point numbers with accuracy up to 18 places and allocates 8 bytes.
1) CHAR and VARCHAR are differ in storage and retrieval.
2) CHAR column length is fixed while VARCHAR length is variable.
3) The maximum no. of character CHAR data type can hold is 255 character while VARCHAR can hold up to 4000 character.
4) CHAR is 50% faster than VARCHAR.
5) CHAR uses static memory allocation while VARCHAR uses dynamic memory allocation.
The " i_am_a_dummy flag" enables MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present.
To get current date, use the following syntax:
Install antivirus and configure the operating system's firewall.
Never use the MySQL Server as the UNIX root user.
Change root username and password.
Restrict or disable remote access.
Mysqladmin -u root -p password "newpassword".
Actually both Unix timestamp and MySQL timestamp are stored as 32-bit integers but MySQL timestamp is represented in readable format of YYYY-MM-DD HH:MM:SS format.
Let us take a table named employee.
To find Nth highest salary is:
select distinct(salary) from employee order by salary desc limit n-1,1if you want to find 3rd largest salary:
select distinct(salary) from employee order by salary desc limit 2,1