What are the differences between DROP a table and TRUNCATE a table
DROP TABLE table_name - This will delete the table and its data.
TRUNCATE TABLE table_name - This will delete the data of the table, but not the table definition.
TRUNCATE TABLE table_name - This will delete the data of the table, but not the table definition.
Explain the difference between FLOAT, DOUBLE and REAL
FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now.
Explain the difference between BOOL, TINYINT and BIT.
Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT
data type can store 8 bytes of data and should be used for binary data.
Explain the difference between MyISAM Static and MyISAM Dynamic
MyISAM static all the fields have fixed width. The Dynamic MyISAM
table would include fields such as TEXT, BLOB, etc. to accommodate the
data types with various lengths. MyISAM Static would be easier to
restore in case of corruption, since even though you might lose some
data, you know exactly where to look for the beginning of the next
record.
How can we know the number of days between two given dates using MySQL?
SELECT DATEDIFF('2007-03-07','2005-01-01');
How can I load data from a text file into a table?
We can use LOAD DATA INFILE file_name;
syntax to load data
from a text file. but we have to make sure that
a) data is delimited
b) columns and data matched correctly
syntax to load data
from a text file. but we have to make sure that
a) data is delimited
b) columns and data matched correctly
What is the difference between char and varchar data types?
Set char to occupy n bytes and it will take n bytes even if u r
storing a value of n-m bytes
Set varchar to occupy n bytes and it will take only the required space
and will not use the n bytes
eg. name char(15) will waste 10 bytes if we store 'mysql', if each char
takes a byte
eg. name varchar(15) will just use 5 bytes if we store 'mysql', if each
char takes a byte. rest 9 bytes will be free.
storing a value of n-m bytes
Set varchar to occupy n bytes and it will take only the required space
and will not use the n bytes
eg. name char(15) will waste 10 bytes if we store 'mysql', if each char
takes a byte
eg. name varchar(15) will just use 5 bytes if we store 'mysql', if each
char takes a byte. rest 9 bytes will be free.
What is the difference between GROUP BY and ORDER BY in Sql?
ORDER BY [col1],[col2],…,[coln];
Tells DBMS according to what columns
it should sort the result. If two rows will have the same value in col1
it will try to sort them according to col2 and so on.
GROUP BY
[col1],[col2],…,[coln]; Tells DBMS to group results with same value of
column col1. You can use COUNT(col1), SUM(col1), AVG(col1) with it, if
you want to count all items in group, sum all values or view average
Tells DBMS according to what columns
it should sort the result. If two rows will have the same value in col1
it will try to sort them according to col2 and so on.
GROUP BY
[col1],[col2],…,[coln]; Tells DBMS to group results with same value of
column col1. You can use COUNT(col1), SUM(col1), AVG(col1) with it, if
you want to count all items in group, sum all values or view average
What is the difference between Primary Key and Unique key?
Primary Key: A column in a table whose values uniquely identify the
rows in the table. A primary key value cannot be NULL.
Unique Key: Unique Keys are used to uniquely identify each row in the
table. There can be one and only one row for each unique key value. So
NULL can be a unique key.There can be only one primary key for a table but there can be more than one unique for a table.
rows in the table. A primary key value cannot be NULL.
Unique Key: Unique Keys are used to uniquely identify each row in the
table. There can be one and only one row for each unique key value. So
NULL can be a unique key.There can be only one primary key for a table but there can be more than one unique for a table.
How many ways we can we find the current date using MySQL?
SELECT CURDATE();
CURRENT_DATE() = CURDATE()
for time use
SELECT CURTIME();
CURRENT_TIME() = CURTIME()
CURRENT_DATE() = CURDATE()
for time use
SELECT CURTIME();
CURRENT_TIME() = CURTIME()
How can we find the number of rows in a table using MySQL?
Use this for mysql
>SELECT COUNT(*) FROM table_name;
>SELECT COUNT(*) FROM table_name;
Explain Normalization concept?
The normalization process involves getting our data to conform to
three progressive normal forms, and a higher level of normalization
cannot be achieved until the previous levels have been achieved (there
are actually five normal forms, but the last two are mainly academic and
will not be discussed).The First Normal Form (or 1NF) involves removal of redundant data
from horizontal rows. We want to ensure that there is no duplication of
data in a given row, and that every column stores the least amount of
information possible (making the field atomic).Second Normal FormWhere the First Normal Form deals with redundancy of data across a
horizontal row, Second Normal Form (or 2NF) deals with redundancy of
data in vertical columns. As stated earlier, the normal forms are
progressive, so to achieve Second Normal Form, your tables must already
be in First Normal Form.Third Normal Form
I have a confession to make; I do not often use Third Normal Form. In
Third Normal Form we are looking for data in our tables that is not
fully dependant on the primary key, but dependant on another value in
the table
three progressive normal forms, and a higher level of normalization
cannot be achieved until the previous levels have been achieved (there
are actually five normal forms, but the last two are mainly academic and
will not be discussed).The First Normal Form (or 1NF) involves removal of redundant data
from horizontal rows. We want to ensure that there is no duplication of
data in a given row, and that every column stores the least amount of
information possible (making the field atomic).Second Normal FormWhere the First Normal Form deals with redundancy of data across a
horizontal row, Second Normal Form (or 2NF) deals with redundancy of
data in vertical columns. As stated earlier, the normal forms are
progressive, so to achieve Second Normal Form, your tables must already
be in First Normal Form.Third Normal Form
I have a confession to make; I do not often use Third Normal Form. In
Third Normal Form we are looking for data in our tables that is not
fully dependant on the primary key, but dependant on another value in
the table
Give the syntax of Grant and Revoke commands?
The generic syntax for grant is as following
> GRANT [rights] on [database/s] TO [username@hostname] IDENTIFIED BY
[password]
now rights can be
a) All privileges
b) combination of create, drop, select, insert, update and delete etc.We can grant rights on all databse by using *.* or some specific
database by database.* or a specific table by database.table_name
username@hotsname can be either username@localhost, username@hostname
and username@%
where hostname is any valid hostname and % represents any name, the *.*
any condition
password is simply the password of userThe generic syntax for revoke is as following
> REVOKE [rights] on [database/s] FROM [username@hostname]
now rights can be as explained above
a) All privileges
b) combination of create, drop, select, insert, update and delete etc.
username@hotsname can be either username@localhost, username@hostname
and username@%
where hostname is any valid hostname and % represents any name, the *.*
any condition
> GRANT [rights] on [database/s] TO [username@hostname] IDENTIFIED BY
[password]
now rights can be
a) All privileges
b) combination of create, drop, select, insert, update and delete etc.We can grant rights on all databse by using *.* or some specific
database by database.* or a specific table by database.table_name
username@hotsname can be either username@localhost, username@hostname
and username@%
where hostname is any valid hostname and % represents any name, the *.*
any condition
password is simply the password of userThe generic syntax for revoke is as following
> REVOKE [rights] on [database/s] FROM [username@hostname]
now rights can be as explained above
a) All privileges
b) combination of create, drop, select, insert, update and delete etc.
username@hotsname can be either username@localhost, username@hostname
and username@%
where hostname is any valid hostname and % represents any name, the *.*
any condition
What is maximum size of a database in MySQL?
If the operating system or filesystem places a limit on the number
of files in a directory, MySQL is bound by that constraint.The efficiency of the operating system in handling large numbers of
files in a directory can place a practical limit on the number of tables
in a database. If the time required to open a file in the directory
increases significantly as the number of files increases, database
performance can be adversely affected.
The amount of available disk space limits the number of tables.
MySQL 3.22 had a 4GB (4 gigabyte) limit on table size. With the MyISAM
storage engine in MySQL 3.23, the maximum table size was increased to
65536 terabytes (2567 – 1 bytes). With this larger allowed table size,
the maximum effective table size for MySQL databases is usually
determined by operating system constraints on file sizes, not by MySQL
internal limits.The InnoDB storage engine maintains InnoDB tables within a tablespace
that can be created from several files. This allows a table to exceed
the maximum individual file size. The tablespace can include raw disk
partitions, which allows extremely large tables. The maximum tablespace
size is 64TB.
The following table lists some examples of operating system file-size
limits. This is only a rough guide and is not intended to be definitive.
For the most up-to-date information, be sure to check the documentation
specific to your operating system.
Operating System File-size LimitLinux 2.2-Intel 32-bit 2GB (LFS: 4GB)
Linux 2.4+ (using ext3 filesystem) 4TB
Solaris 9/10 16TB
NetWare w/NSS filesystem 8TB
Win32 w/ FAT/FAT32 2GB/4GB
Win32 w/ NTFS 2TB (possibly larger)
MacOS X w/ HFS+ 2TB
of files in a directory, MySQL is bound by that constraint.The efficiency of the operating system in handling large numbers of
files in a directory can place a practical limit on the number of tables
in a database. If the time required to open a file in the directory
increases significantly as the number of files increases, database
performance can be adversely affected.
The amount of available disk space limits the number of tables.
MySQL 3.22 had a 4GB (4 gigabyte) limit on table size. With the MyISAM
storage engine in MySQL 3.23, the maximum table size was increased to
65536 terabytes (2567 – 1 bytes). With this larger allowed table size,
the maximum effective table size for MySQL databases is usually
determined by operating system constraints on file sizes, not by MySQL
internal limits.The InnoDB storage engine maintains InnoDB tables within a tablespace
that can be created from several files. This allows a table to exceed
the maximum individual file size. The tablespace can include raw disk
partitions, which allows extremely large tables. The maximum tablespace
size is 64TB.
The following table lists some examples of operating system file-size
limits. This is only a rough guide and is not intended to be definitive.
For the most up-to-date information, be sure to check the documentation
specific to your operating system.
Operating System File-size LimitLinux 2.2-Intel 32-bit 2GB (LFS: 4GB)
Linux 2.4+ (using ext3 filesystem) 4TB
Solaris 9/10 16TB
NetWare w/NSS filesystem 8TB
Win32 w/ FAT/FAT32 2GB/4GB
Win32 w/ NTFS 2TB (possibly larger)
MacOS X w/ HFS+ 2TB
Friday, December 4, 2009
What is the purpose of the following files having extensions 1) .frm 2) .myd 3) .myi? What do these files contain?
In MySql, the default table type is MyISAM.
Each MyISAM table is stored on disk in three files. The files have names
that begin with the table name and have an extension to indicate the
file type.
The ‘.frm’ file stores the table definition.
The data file has a ‘.MYD’ (MYData) extension.
The index file has a ‘.MYI’ (MYIndex) extension,
Each MyISAM table is stored on disk in three files. The files have names
that begin with the table name and have an extension to indicate the
file type.
The ‘.frm’ file stores the table definition.
The data file has a ‘.MYD’ (MYData) extension.
The index file has a ‘.MYI’ (MYIndex) extension,
How many tables will create when we create table, what are they?
The ‘.frm’ file stores the table definition.
The data file has a ‘.MYD’ (MYData) extension.
The index file has a ‘.MYI’ (MYIndex) extension,
The data file has a ‘.MYD’ (MYData) extension.
The index file has a ‘.MYI’ (MYIndex) extension,
How many values can the SET function of MySQL take?
MySQL set can take zero or more values but at the maximum it can
take 64 values
take 64 values
What is the maximum length of a table name, database name, and fieldname in MySQL?
The following table describes the maximum length for each type of
identifier.
Identifier Maximum Length
(bytes)
Database 64
Table 64
Column 64
Index 64
Alias 255
There are some restrictions on the characters that may appear in
identifiers:
identifier.
Identifier Maximum Length
(bytes)
Database 64
Table 64
Column 64
Index 64
Alias 255
There are some restrictions on the characters that may appear in
identifiers:
What are the advantages of stored procedures, triggers, indexes?
stored in the server. Once this has been done, clients don’t need to
keep re-issuing the entire query but can refer to the stored procedure.
This provides better overall performance because the query has to be
parsed only once, and less information needs to be sent between the
server and the client. You can also raise the conceptual level by having
libraries of functions in the server. However, stored procedures of
course do increase the load on the database server system, as more of
the work is done on the server side and less on the client (application)
side.Triggers will also be implemented. A trigger is effectively a type of
stored procedure, one that is invoked when a particular event occurs.
For example, you can install a stored procedure that is triggered each
time a record is deleted from a transaction table and that stored
procedure automatically deletes the corresponding customer from a
customer table when all his transactions are deleted.Indexes are used to find rows with specific column values quickly.
Without an index, MySQL must begin with the first row and then read
through the entire table to find the relevant rows. The larger the
table, the more this costs. If the table has an index for the columns in
question, MySQL can quickly determine the position to seek to in the
middle of the data file without having to look at all the data. If a
table has 1,000 rows, this is at least 100 times faster than reading
sequentially. If you need to access most of the rows, it is faster to
read sequentially, because this minimizes disk seeks.
How can we optimize or increase the speed of a MySQL select query?
# First of all instead of using select * from table1, use select
column1, column2, column3.. from table1
# Look for the opportunity to introduce index in the table you are
querying.
# Use limit keyword if you are looking for any specific number of
rows from the result set.
column1, column2, column3.. from table1
# Look for the opportunity to introduce index in the table you are
querying.
# Use limit keyword if you are looking for any specific number of
rows from the result set.
How can we take a backup of a MySQL table and how can we restore it. ?
To backup: BACKUP TABLE tbl_name[,tbl_name…] TO
‘/path/to/backup/directory’
RESTORE TABLE tbl_name[,tbl_name…] FROM ‘/path/to/backup/directory’mysqldump: Dumping Table Structure and DataUtility to dump a database or a collection of database for backup or
for transferring the data to another SQL server (not necessarily a MySQL
server). The dump will contain SQL statements to create the table and/or
populate the table.
-t, –no-create-info
Don’t write table creation information (the CREATE TABLE statement).
-d, –no-data
Don’t write any row information for the table. This is very useful if
you just want to get a dump of the structure for a table!
‘/path/to/backup/directory’
RESTORE TABLE tbl_name[,tbl_name…] FROM ‘/path/to/backup/directory’mysqldump: Dumping Table Structure and DataUtility to dump a database or a collection of database for backup or
for transferring the data to another SQL server (not necessarily a MySQL
server). The dump will contain SQL statements to create the table and/or
populate the table.
-t, –no-create-info
Don’t write table creation information (the CREATE TABLE statement).
-d, –no-data
Don’t write any row information for the table. This is very useful if
you just want to get a dump of the structure for a table!
How can we encrypt and decrypt a data present in a MySQL table using MySQL?
AES_ENCRYPT () and AES_DECRYPT ()
Explain advantages of MyISAM over InnoDB?
Advantages of MyISAM over InnoDB
Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.
Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.
Explain advantages of InnoDB over MyISAM?
Advantages of InnoDB over MyISAM
Row-level locking, transactions, foreign key constraints and crash recovery.
Row-level locking, transactions, foreign key constraints and crash recovery.
What is the main difference between myisam and innodb
innodb support transaction, foreign key while myisam not
support transaction.
Other differences are :
1. innodb requires more RAM than mysiam
2. myisam relies on OS for caching while innodb caches with
in the engine itself.
3. Most preffered is innodb because
- Transaction safe
-It has commit, rollback, and crash recovery capabailities
- Innodb is designed for maximum performance when processing
large volumes of data.
InnoDB: Row level locking, Transaction support, forgin key
constrant and crash recovery.
MyISAM: Much more conservate approach to disk space
management each MyISAM table store in a separate file. in
MyISAM memory and space usage, full text indexing support,
table based locking, bulk insert capabilities and speed are
plus factor but crushes recovery would be the horror story.
As general approach, if you have a more reads use MyISAM and
if you have a more update use InnoDB.
InnoDb table take More Disk space Comparing with MyISAM
InnoDB is Transactions safe means Data Integrity is
maintained Throughout entire qry process.
InnoDB also provides Row-locking as opposed to table
locking while 1 qry is busy with updating or inserting
arow,another qry can update a different row at the same
time.these features increases the multi user concurrency
and Performance
Another greate feature is InnoDB boasts is the ability to
use Foreign key constraints
কোন মন্তব্য নেই:
একটি মন্তব্য পোস্ট করুন