I require selecting 10 records at a time and at the same time I also require to know the total count of records.
How would you do this?
How would you do this?
You can use SQL_CALC_FOUND_ROWS in SELECT statement and execute FOUND_ROWS().
Sample query:
Sample query:
1
2
|
SELECT SQL_CALC_FOUND_ROWS * FROM Test LIMIT 10;
SELECT FOUND_ROWS();
|
The First statement will return your 10 records and second statement will return total count of records.
The FOUND_ROWS() function will not internally use COUNT so it performs better.
The FOUND_ROWS() function will not internally use COUNT so it performs better.
Find all database starting with ‘db’.
1
|
SHOW DATABASES LIKE 'db%';
|
How to concatenate this two string?
String 1: ‘My name is Anvesh.’
String 2: ‘I am Database Administrator.’
1
|
SELECT CONCAT('My name is Anvesh.',' I am Database Administrator.');
|
How to get a portion of a string by specifying position?
1
|
SELECT SUBSTR('I am Anvesh Patel',6,6);
|
How to select month from given date?
1
2
|
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());
|
How to format date in MM-DD-YY format?
1
|
SELECT DATE_FORMAT(NOW(),'%m-%d-%y');
|
How to format date in week – day – month – year format?
1
|
SELECT DATE_FORMAT(NOW(),'%W %D %M %Y');
|
How to copy the only table structure and create a new table?
1
|
CREATE TABLE Test2 AS SELECT *FROM Test WHERE 1=2;
|
How to make copies of the table with data and structure?
1
|
CREATE TABLE Test2 AS SELECT *FROM Test;
|
What are the result of below queries?
1
|
SELECT NULL + 8 FROM DUAL;
|
Result : NULL
1
|
SELECT NULL OR TRUE FROM DUAL;
|
Result : 1
1
|
SELECT NULL AND TRUE FROM DUAL;
|
Result : NULL
How to convert numeric values to strings?
1
|
SELECT CAST(88.808 AS CHAR) FROM DUAL;
|
How to convert string values to Date format?
1
|
SELECT STR_TO_DATE('08-Aug-2015 8:08:08', '%d-%b-%Y') FROM DUAL;
|
How to add new column to an existing table?
1
|
ALTER TABLE test ADD COLUMN RecordDateTime DATETIME;
|
How to drop column from an existing table?
1
|
ALTER TABLE test DROP COLUMN RecordDateTime;
|
How to rename a column in an existing table?
1
|
ALTER TABLE Test CHANGE COLUMN OldColumnName NewColumnName INTEGER;
|
How to rename existing Table?
1
|
ALTER TABLE Test rename to Test2;
|
How to drop index of a table?
1
|
DROP INDEX idx_tbl_test_rno ON test;
|
Check running all MySQL Server Processes.
1
|
mysqladmin -u root -p processlist
|
Command to shutdown MySQL.
1
|
mysqladmin -u root -p shutdown
|
Check MySQL current version.
1
|
mysqladmin -u root -p version
|
Check MySQL is running or not.
1
|
mysqladmin -u root -p ping
|
How to list all databases from the MySQL Server?
1
|
Show databases;
|
How to switch or use to a database?
1
|
Use MyDatabase;
|
How to list all tables from the MySQL Server?
1
|
Show tables;
|
Which tool are you using to monitor MySQL Server?
SQLyog is a GUI tool for the RDBMS MySQL. SQLyog is being used by more than 30,000 customers worldwide and has been downloaded more than 2,000,000 times. It is one of the best tool to monitor MySQL Server.
What are your steps to handle foreign key constraint error during deletion of data?
Step 1: If cascade delete is require, then we can create foreign key with ON DELETE CASCADE.
Step 2: We can also set SET foreign_key_checks=0 before the delete command.
By default it is, SET foreign_key_checks=1.
Using this option you can disable referential constraint temporary.
But as a result, you can insert data that violate foreign key constraints, and when you enable the referential constraints (set FOREIGN_KEY_CHECKS to 1), MySQL does not re-validate the inserted rows.
Step 2: We can also set SET foreign_key_checks=0 before the delete command.
By default it is, SET foreign_key_checks=1.
Using this option you can disable referential constraint temporary.
But as a result, you can insert data that violate foreign key constraints, and when you enable the referential constraints (set FOREIGN_KEY_CHECKS to 1), MySQL does not re-validate the inserted rows.
Does MySQL foreign_key_checks affect the entire database?
Actually, there are two foreign_key_checks variables: a global variable and a local variable.
The command SET foreign_key_checks modifies the session variable.
To modify the global variable, use SET GLOBAL foreign_key_checks.
The command SET foreign_key_checks modifies the session variable.
To modify the global variable, use SET GLOBAL foreign_key_checks.
Which are the different tools to manage MySQL Server?
mysqld: MySQL server daemon. Used to start your MySQL server.
mysql : A command-line interface to manage mysql objects.
mysqldump: A command-line interface to export data or database backup.
mysqlimport: A command-line interface to import data into MySQL Database.
mysqlshow : A command-line interface to show meta data information of MySQL Server.
mysqlcheck : A command-line interface to check and repair database or table.
mysqladmin: A command-line interface for Database Administrator to perform administrator task.
mysql : A command-line interface to manage mysql objects.
mysqldump: A command-line interface to export data or database backup.
mysqlimport: A command-line interface to import data into MySQL Database.
mysqlshow : A command-line interface to show meta data information of MySQL Server.
mysqlcheck : A command-line interface to check and repair database or table.
mysqladmin: A command-line interface for Database Administrator to perform administrator task.
Explain detailed use of Mysqld.
The Mysqld is a server daemon program which runs in the background of your computer. To invoke Mysqld will start the MySQL Server and To terminate Mysqld will shutdown the MySQL Server.
The Mysqld program has many options that can be specified at startup.
Mysqld is the main program that does most of the work in a MySQL installation like, create data directory for databases and tables, create a directory for log files and status files.
The Mysqld program has many options that can be specified at startup.
Mysqld is the main program that does most of the work in a MySQL installation like, create data directory for databases and tables, create a directory for log files and status files.
Explain detailed use of Mysqladmin.
The Mysqladmin is a command – line interface for Database Administrators to perform administrator related task.
A Database Administrator can perform this task:
A Database Administrator can perform this task:
- Check server configuration.
- Check whether the server is available.
- Check current status of the server.
- Create / Drop database.
- Check the version information on the server.
- Tell the server to write debug information to the error log.
- Flush all information in the host cache.
- Flush all logs.
- Reload the grant tables.
- Show a list of active server threads.
- Kill server threads.
- Stop replication on a slave server.
What is BDB (BerkeleyDB)?
The BDB is a one type of storage engine, which is developed by Sleepycat Software.
The BDB tables may have a greater chance of surviving crashes and are also capable of COMMIT and ROLLBACK operations on transactions.
Support for the BDB storage engine is included in MySQL source distributions, which come with a BDB distribution that is patched to make it work with MySQL.
The BDB tables may have a greater chance of surviving crashes and are also capable of COMMIT and ROLLBACK operations on transactions.
Support for the BDB storage engine is included in MySQL source distributions, which come with a BDB distribution that is patched to make it work with MySQL.
What are the characteristics of BDB tables?
Each BDB table store two different files on disk, one is .frm file stores the table format and second is .db file stores table and index data.
The BDB storage engine provides transactional table in which you can enable/disable auto-commit mode, you can write transaction statements like, BEGIN / ROLLBACK.
The primary key is to require on each BDB table.
The BDB table perform locking at page level.
The BDB storage engine provides transactional table in which you can enable/disable auto-commit mode, you can write transaction statements like, BEGIN / ROLLBACK.
The primary key is to require on each BDB table.
The BDB table perform locking at page level.
What is MySQL Query Cache ?
Sort & Simple, MySQL Query Cache stores some frequent SELECT statements with its corresponding results.
When table data are not changing frequently and the server receives many identical queries then only Query Cache works for better performance.
Whenever table modified with data, then Query cache will be flush.
When table data are not changing frequently and the server receives many identical queries then only Query Cache works for better performance.
Whenever table modified with data, then Query cache will be flush.
Does MySQL Query Cache improve the database performance?
Yes,
When you trigger any select statement and if it is available in the cache then it will return from there only not require parsing and fetching from disk so its improve a query performance.
Please do not assume that this will work under all circumstances because this is also depends on your load of memory and server.
When you trigger any select statement and if it is available in the cache then it will return from there only not require parsing and fetching from disk so its improve a query performance.
Please do not assume that this will work under all circumstances because this is also depends on your load of memory and server.
In which conditions MySQL Query Cache will not work?
Not use when multiple server hitting to one query.
Not use with Stored Procedure and UDF.
Not use with any type of Variables.
Not use with any table of default database like, MySQL, INFORMATION_SCHEMA.
Not use with partition tables.
Not use with Temporary tables.
Not use with Prepared Statements.
Not use with Stored Procedure and UDF.
Not use with any type of Variables.
Not use with any table of default database like, MySQL, INFORMATION_SCHEMA.
Not use with partition tables.
Not use with Temporary tables.
Not use with Prepared Statements.
The command to set size of the MySQL Query Cache:
SET GLOBAL query_cache_size = 1000000;
The command to check size of the MySQL Query Cache:
SHOW VARIABLES LIKE ‘query_cache_size’;
What is SQL_CACHE and SQL_NO_CACHE?
SQL_CACHE:
The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON or DEMAND.
SQL_CACHE:
The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON or DEMAND.
SQL_NO_CACHE:
The server does not use the query cache. It neither checks the query cache to see whether the result is already cached,nor does it cache the query result.
The server does not use the query cache. It neither checks the query cache to see whether the result is already cached,nor does it cache the query result.
What is the difference between VARCHAR(3) and CHAR(3).
No difference: VARCHAR with length less than 4 become CHAR.
What is the difference between VARCHAR(10) and CHAR(10).
No difference: CHAR with length more than 3 become VARCHAR.
What is Access Control List(ACL)?
MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that users can attempt to perform. There is also support for SSL-encrypted connections between MySQL clients and servers.
MySQL keeps the ACLs (also called grant tables) cached in memory.
When a user tries to authenticate or run a command, MySQL checks the authentication information.
MySQL keeps the ACLs (also called grant tables) cached in memory.
When a user tries to authenticate or run a command, MySQL checks the authentication information.
What is the difference between MySQL_connect and MySQL_pconnect?
MySQL_Connect opens a new connection in the database and every time you need to open and close connection.
MySQL_PConnect for the persistent connection so it open one time and use for different running sessions. You do not require open and close again and again.
MySQL_PConnect for the persistent connection so it open one time and use for different running sessions. You do not require open and close again and again.
What is the meaning of ” i_am_a_dummy flag” ?
This is one flag in MySQL and its enables MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present.
Command to change a password for an existing user using Mysqladmin.
Mysqladmin -u root -p password “newpassword”.
Which command you are using to show all indexes of a table?
SHOW INDEX FROM table_name;
How to change storage engine of a table?
ALTER TABLE table_name ENGINE Innodb;
What are the drawbacks to creating a large number of tables in the same database.
MySQL requires to open, close and create files for every single table and even this can be multiple because of multi-threaded so this will degrade the performance.
You should also increase the size of table_cache otherwise it will be full because of many table file operations.
You should also increase the size of table_cache otherwise it will be full because of many table file operations.
The User is created and Host column contains %, Is it good or bad practice?
This is bad practice, % indicates that user can login from anywhere in the network or internet. This should be best practice only for specific user like admin or root.
What happens when you reach maximum value for AUTO_INCREMENT field?
It stops the incrementing and generate an error like, the key is already in use.
Using which command, you can get the last inserted id by AUTO_INCREMENT?
LAST_INSERT_ID will return the last value assigned by Auto_increment.
Command to check indexes for a table.
SHOW INDEX FROM <table_name>;
Is Mysql query is case sensitive?
No:
Explain any three date function of MySQL.
DATEDIFF(expr1,expr2):
DATEDIFF() returns expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
DATEDIFF() returns expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
1
2
3
4
|
mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
-> 1
mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
-> -31
|
ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)
1
2
3
4
5
|
mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
-> '2008-02-02'
mysql> SELECT ADDDATE('2008-01-02', 31);
-> '2008-02-02'
|
EXTRACT(unit FROM date)
It extracts parts from the date rather than performing date arithmetic.
It extracts parts from the date rather than performing date arithmetic.
1
2
3
4
|
mysql> SELECT EXTRACT(YEAR FROM '2009-07-02');
-> 2009
mysql> SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');
-> 200907
|
How you can perform pagination in MySQL Query?
Using LIMIT and OFFSET.
You can skip some number of rows by OFFSET and set the LIMIT for the next number of rows.
You can skip some number of rows by OFFSET and set the LIMIT for the next number of rows.
What is the difference between NOW() and CURRENT_DATE()?
NOW() format : YYYY-MM-DD HH:MM:SS
CURRENT_DATE() format: YYYY-MM-DD
CURRENT_DATE() format: YYYY-MM-DD
What is heap table ?
Table without a cluster index is called as Heap Table. Data is stored in the heap without specifying an order.
Initially, data stored in the order of its inserted, but later database engine can move data around in the heap so you cannot predict the order of data and because of this you have to apply ORDER BY clause for each and every use.
Please visit this post for more details:
Initially, data stored in the order of its inserted, but later database engine can move data around in the heap so you cannot predict the order of data and because of this you have to apply ORDER BY clause for each and every use.
Please visit this post for more details:
Is MySQL supporting Heap Stroage Engine?
Yes,
MySQL Memory Storage Engine also called as Heap Storage structure.
Using Memory Storage Engine you can store table of data in server memory for fast retrieval. This table of data also supports B-Tree index so you can get results in the best optimized way. Auto_Increment also supported by the Tables of Memory Engine.
There might be chances of loss data when any hardware crashes or power outage of Server Memory.
You can store and use only those data which are in temporary use and make sure that data in memory engine has always back up in physical stored table.
MySQL Memory Storage Engine also called as Heap Storage structure.
Using Memory Storage Engine you can store table of data in server memory for fast retrieval. This table of data also supports B-Tree index so you can get results in the best optimized way. Auto_Increment also supported by the Tables of Memory Engine.
There might be chances of loss data when any hardware crashes or power outage of Server Memory.
You can store and use only those data which are in temporary use and make sure that data in memory engine has always back up in physical stored table.
What is difference between Temporary Table and Memory Table ?
Temporary Table is only for running current session. Once running session completed its execution than Temporary Table will be discarded. You cannot use Temporary Table for multiple sessions.
Memory Table can hold data for across all running sessions and connections.
Memory Table can hold data for across all running sessions and connections.
How you can set the max size of Heap Table?
Using max_heap_table_size this MySQL Config variable.
May I store BLOB into HEAP Table?
No, You can not store BLOG or Text Data.
Auto_Increment is also not supported by HEAP table.
Auto_Increment is also not supported by HEAP table.
May I connect MySQL with any other RDBMS product like, PostgreSQL.
Yes, You can connect using three different ways ODBC Connection , DSN and Foreign Data Wrapper(FDW).
What is the login command to connect MySQL in Unix?
# [mysql-dir]/bin/mysql -h hostname -u <UserName> -p <password>
Have you ever used myisamchk command?
Yes, its compress the MyISAM tables by reducing disk and memory usage.
Which are the key advantages of InnoDB?
InnoDB is a high-reliability and high-performance storage engine for MySQL.
Its design follows the ACID model, with transaction features like to commit, rollback.
Row-level locking and Oracle-style consistent reads increase multi-user concurrency and performance.
InnoDB tables, arrange your data on disk to optimize common queries based on primary keys. Each InnoDB table has a primary key index called the clustered index.
You can freely mix InnoDB tables with tables from other MySQL storage engines, even within the same statement. For example, you can use a join operation to combine data from InnoDB and MEMORY tables in a single query.
Its design follows the ACID model, with transaction features like to commit, rollback.
Row-level locking and Oracle-style consistent reads increase multi-user concurrency and performance.
InnoDB tables, arrange your data on disk to optimize common queries based on primary keys. Each InnoDB table has a primary key index called the clustered index.
You can freely mix InnoDB tables with tables from other MySQL storage engines, even within the same statement. For example, you can use a join operation to combine data from InnoDB and MEMORY tables in a single query.
What is MyISAM table and explain storage structure for it?
This is one of Table Stroage Engine.
All data values are stored with the low byte first. This makes the data machine and operating system independent.
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. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.
All data values are stored with the low byte first. This makes the data machine and operating system independent.
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. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.
How many indexes you can create on MyISAM table?
The maximum number of indexes per MyISAM table is 64.
Is MVCC supported by MyISAM?
No:
Is ENUM supported by MySQL?
Yes, you can use for predefine values during table creation.
How to get current MySQL version?
SELECT VERSION ();
What is the difference between BLOB , CLOB and TEXT?
TEXT is a data-type of text based input. On the other hand, you have BLOB and CLOB, which are more suitable for data storage (images, etc.) due to their larger capacity limits (4GB for example).
TEXT and CHAR will convert to/from the character set they have associated with time. BLOB and CLOB simply store bytes.
As for the difference between BLOB and CLOB, I believe CLOB has a character encoding associated with it, which implies it can be suited well for very large amounts of text.
TEXT and CHAR will convert to/from the character set they have associated with time. BLOB and CLOB simply store bytes.
As for the difference between BLOB and CLOB, I believe CLOB has a character encoding associated with it, which implies it can be suited well for very large amounts of text.
What is table_cache and max_connections in MySQL?
It is about to Opens and Closes Tables by MySQL.
MySQL is multi-threaded, so there are different connection which are accessing the same table simultaneously. MySQL open this table into cache for all different connections.
table_cache is a system variable and this is number of open tables for all threads. This can be changed base on the requirement.
max_connection is a system variable and this is the maximum permitted number of simultaneous client connections.
MySQL is multi-threaded, so there are different connection which are accessing the same table simultaneously. MySQL open this table into cache for all different connections.
table_cache is a system variable and this is number of open tables for all threads. This can be changed base on the requirement.
max_connection is a system variable and this is the maximum permitted number of simultaneous client connections.
What is MySQL?
MySQL is an open source Relational Database Management System (RDBMS).
It is based on the structure query language (SQL), which is used for adding, removing, and modifying information in the database.
It is most popular for its reliability, flexibility and fast processing. MySQL can be used for a variety of applications, but is most commonly found on Web servers.
It is based on the structure query language (SQL), which is used for adding, removing, and modifying information in the database.
It is most popular for its reliability, flexibility and fast processing. MySQL can be used for a variety of applications, but is most commonly found on Web servers.
In which language MySQL is written?
MySQL is written in C and C++ and SQL parser is written in yacc.
What is one of the best powerful feature of MySQL?
MySQL provides flexibility to choose the data/table storage engine as per your requirement. MySQL supports different storage engine like, InnoDB, MyIsam, Memory/Heap.
What is the default port for MySQL Server?
The default port is 3306.
What is Multiversion Concurrency Control (MVCC) and is it supported by MySQL?
Yes, MySQL InnoDB Engine supports MVCC architecture.
What is difference between DATETIME and TIMESTAMP in MySQL?
TIMESTAMP used to track changes to records, and update every time when the record is changed.
DATETIME used to store specific and static value which is not affected by any changes in records.
TIMESTAMP also affected by different TIME ZONE related setting.
DATETIME is constant.
DATETIME used to store specific and static value which is not affected by any changes in records.
TIMESTAMP also affected by different TIME ZONE related setting.
DATETIME is constant.
What is difference between CHAR_LENGTH and Length?
CHAR_LENGTH is for character count and the LENGTH is for byte count.
If you are checking Latin characters, then the number will be same, but its difference for other unicode and encoded string.
If you are checking Latin characters, then the number will be same, but its difference for other unicode and encoded string.
What is the difference between the LIKE and REGEXP operators?
The like operator allows for specifying wildcards using the % operator.
If for instance you need to specify all words starting with the character a, you can do so by using the value “a%”.
If for instance you need to specify all words starting with the character a, you can do so by using the value “a%”.
Regexp allows you to specify a regular expression in comparing values in a column with a parameter. For instance, if you need to retrieve all records that match a phone number in the format 555-666-7777 you can use the parameter “[[:digit:]]{3}\-[[:digit:]]{3}\-[[:digit:]]{4}”.
How to see a description or definition of the Table?
1
|
describe tablename;
|
Posts
![IVRAMANA](https://draft.blogger.com/img/profile_blank_gray.jpg" title="IVRAMANA)
0 comments:
Post a Comment