What's the default port for MySQL Server?
3306
How do you start and stop MySQL on Windows?
net start MySQL, net stop MySQL
How do you start MySQL on Linux?
/etc/init.d/mysql start
What does tee command do in MySQL?
tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command notee
Can you save your connection settings to a conf file?
Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it's not readable by others
What does myisamchk do?
It compressed the MyISAM tables, which reduces their disk usage
Use MySQLdump to create a copy of the database?
mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql
If the value in the column is repeatable, how do you find out the unique values?
Use DISTINCT in the query, such as SELECT DISTINCT user_firstname FROM users; You can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT user_firstname) FROM users;
How do you return the a hundred books starting from 25th?
SELECT book_title FROM books LIMIT 25, 100. The first number in LIMIT is the offset, the second is the number.
SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8)
SELECT user_name FROM users WHERE ISNULL(user_phonenumber);
When would you use ORDER BY in DELETE statement?
When you’re not deleting by row ID. Such as in DELETE FROM techpreparation_com_questions
ORDER BY timestamp LIMIT 1. This will delete the most recently posted question in the table
techpreparation_com_questions
How would you change a column from VARCHAR(10) to VARCHAR(50)?
ALTER TABLE techpreparation_questions CHANGE techpreparation_content
techpreparation_CONTENT VARCHAR(50).
How would you delete a column?
ALTER TABLE techpreparation_answers DROP answer_user_id
How can you see all indexes defined for a table?
SHOW INDEX FROM techpreparation_questions;
How would you change a table to InnoDB?
ALTER TABLE techpreparation_questions ENGINE innodb;
How do I find out all databases starting with ‘tech’ to which I have access to?
SHOW DATABASES LIKE ‘tech%’;
CONCAT (string1, string2, string3)
SELECT SUBSTR(title, 1, 10) from techpreparation_questions;
What do % and _ mean inside LIKE statement?
% corresponds to 0 or more characters, _ is exactly one character.
ADDDATE(techpreparation_publication_date, INTERVAL 3 MINUTE)
http://www.tecmint.com/basic-mysql-interview-questions-for-database-administrators/
How would you check if MySql service is running or not?
Issue the command “service mysql status” in ‘Debian’ and “service mysqld status” in RedHat.
If the service is running/stop how would you stop/start the service?
To start MySql service use command as service mysqld start and to stop use service mysqld stop.
To connect or login to MySQL service, use command: mysql -u root -p.
To list all currently running databases run the command on mysql shell as: show databases;
To list all the tables of a database use the command on mysql shell as: show tables;
To get the Field Name and Type of a table use the command on mysql shell as: describe table_name;
To delte a specific table use the command on mysql shell as: drop table table_name;
To delte a specific database use the command on mysql shell as: drop database database-name;
http://javarevisited.blogspot.sg/2010/10/frequently-used-mysql-commands-part-1.html
MySql Commands
Viewing mysql processlist and kiling offending mysql process
./mysqladmin -u root processlist
Kill processes
./mysqladmin -u root kill ProcessID
./mysqladmin -u root shutdown
./mysqladmin --host=localhost --port=3305 -u root shutdown //for second instance listening on port 3305
How to see mysql help
mysql> help alter;
Getting mysql server version
select version();
Displaying index from a mysql table
SHOW INDEX FROM database.ORDERS;
Adding Keys(Indexes) to a table
alter table ORDERS add KEY `BY_CLIENT` (`CLIENT_ID`)
Increasing no of connections for mysql
You can increase this value in main config file (e.g., /etc/my.cnf) using this syntax:
[mysqld]
set-variable=max_connections=250
UNIX_TIMESTAMP function
SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
Diff between 2 dates
SELECT TIMEDIFF('1997-12-31 23:59:59.000001','1997-12-30 01:01:01.000002');
Returns Time to seconds
SELECT TIME_TO_SEC('22:23:00');
Running mysql query from unix command prompt
mysql -u root -h <hostname> <database name >-e "UPDATE ORDERT SET TYPE ='PARTIAL' WHERE TYPE='FULL'
Viewing mysql command history
There is one hidden file called .mysql-history ,on which all commands are stored which we typed in MySQL console. its generally resides in home directory.
copying data from one table to another
insert into ORDERS_TMP select * from ORDERS
Rename Table
ALTER TABLE new_table RENAME old_table;
modifying a column
alter table ORDERS modify column BRAND varchar(15) default NULL
Dropping columns from the table
ALTER TABLE `database`.`ORDERS` DROP COLUMN `BRAND`;
Repair the table
REPAIR TABLE TableNAme
Adding Keys(Indexes) to a table
alter table ORDERS add KEY `BY_CLIENT` (`CLIENT_ID`) (here CLIENT_ID is a column in ORDers table)
What is Heap table?
Tables that are present in the memory are called as HEAP tables. These tables are commonly known as memory tables. These memory tables never have values with data type like “BLOB” or “TEXT”. They use indexes which make them faster.
How many TRIGGERS are possible in MySql?
There are only six triggers are allowed to use in MySQL database and they are.
Before Insert, After Insert, Before Update, After Update, Before Delete, After Delete
What is BLOB?
- BLOB stands for binary large object.
- It that can hold a variable amount of data.
There are four types of BLOB based on the maximum length of values they can hold:
- TINYBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB
What is TEXT?
TEXT is case-insensitive BLOB. The four types of TEXT are:
- TINYTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT
What is the difference between BLOB and TEXT?
- In BLOB sorting and comparison is performed in case-sensitive for BLOB values
- In TEXT types sorting and comparison is performed case-insensitive.
How would you get the current date in Mysql?
By using SELECT CURRENT_DATE();
How would concatenate strings in MySQL?
With the use of - CONCAT (string1, string2, string3)
- MySQL time stamps are seen to a user in a readable format : YYYY-MM-DD HH:MM:SS.
MySQL Transaction
CREATE TABLE EMPLOYEE
(
EMPLOYEE_ID CHAR(5) NOT NULL,
EMPLOYEE_NAME VARCHAR(50) NOT NULL,
PRIMARY KEY (EMPLOYEE_ID))
ENGINE = INNODB;
Autocommit mode
SET AUTOCOMMIT=0;
SET AUTOCOMMIT=1;
Implicit mode:
COMMIT
ROLLBACK
SAVEPOINT
START TRANSACTION
References
http://www.tecmint.com/basic-mysql-interview-questions-for-database-administrators/
http://javarevisited.blogspot.sg/2010/10/frequently-used-mysql-commands-part-1.html
http://javarevisited.blogspot.com/2010/10/frequently-used-mysql-commands-part-2.html
3306
How do you start and stop MySQL on Windows?
net start MySQL, net stop MySQL
How do you start MySQL on Linux?
/etc/init.d/mysql start
What does tee command do in MySQL?
tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command notee
Can you save your connection settings to a conf file?
Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it's not readable by others
What does myisamchk do?
It compressed the MyISAM tables, which reduces their disk usage
Use MySQLdump to create a copy of the database?
mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql
If the value in the column is repeatable, how do you find out the unique values?
Use DISTINCT in the query, such as SELECT DISTINCT user_firstname FROM users; You can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT user_firstname) FROM users;
How do you return the a hundred books starting from 25th?
SELECT book_title FROM books LIMIT 25, 100. The first number in LIMIT is the offset, the second is the number.
SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8)
SELECT user_name FROM users WHERE ISNULL(user_phonenumber);
When would you use ORDER BY in DELETE statement?
When you’re not deleting by row ID. Such as in DELETE FROM techpreparation_com_questions
ORDER BY timestamp LIMIT 1. This will delete the most recently posted question in the table
techpreparation_com_questions
How would you change a column from VARCHAR(10) to VARCHAR(50)?
ALTER TABLE techpreparation_questions CHANGE techpreparation_content
techpreparation_CONTENT VARCHAR(50).
How would you delete a column?
ALTER TABLE techpreparation_answers DROP answer_user_id
How can you see all indexes defined for a table?
SHOW INDEX FROM techpreparation_questions;
How would you change a table to InnoDB?
ALTER TABLE techpreparation_questions ENGINE innodb;
How do I find out all databases starting with ‘tech’ to which I have access to?
SHOW DATABASES LIKE ‘tech%’;
CONCAT (string1, string2, string3)
SELECT SUBSTR(title, 1, 10) from techpreparation_questions;
What do % and _ mean inside LIKE statement?
% corresponds to 0 or more characters, _ is exactly one character.
ADDDATE(techpreparation_publication_date, INTERVAL 3 MINUTE)
http://www.tecmint.com/basic-mysql-interview-questions-for-database-administrators/
How would you check if MySql service is running or not?
Issue the command “service mysql status” in ‘Debian’ and “service mysqld status” in RedHat.
If the service is running/stop how would you stop/start the service?
To start MySql service use command as service mysqld start and to stop use service mysqld stop.
To connect or login to MySQL service, use command: mysql -u root -p.
To list all currently running databases run the command on mysql shell as: show databases;
To list all the tables of a database use the command on mysql shell as: show tables;
To get the Field Name and Type of a table use the command on mysql shell as: describe table_name;
To delte a specific table use the command on mysql shell as: drop table table_name;
To delte a specific database use the command on mysql shell as: drop database database-name;
http://javarevisited.blogspot.sg/2010/10/frequently-used-mysql-commands-part-1.html
MySql Commands
Viewing mysql processlist and kiling offending mysql process
./mysqladmin -u root processlist
Kill processes
./mysqladmin -u root kill ProcessID
./mysqladmin -u root shutdown
./mysqladmin --host=localhost --port=3305 -u root shutdown //for second instance listening on port 3305
How to see mysql help
mysql> help alter;
Getting mysql server version
select version();
Displaying index from a mysql table
SHOW INDEX FROM database.ORDERS;
Adding Keys(Indexes) to a table
alter table ORDERS add KEY `BY_CLIENT` (`CLIENT_ID`)
Increasing no of connections for mysql
You can increase this value in main config file (e.g., /etc/my.cnf) using this syntax:
[mysqld]
set-variable=max_connections=250
UNIX_TIMESTAMP function
SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
Diff between 2 dates
SELECT TIMEDIFF('1997-12-31 23:59:59.000001','1997-12-30 01:01:01.000002');
Returns Time to seconds
SELECT TIME_TO_SEC('22:23:00');
Running mysql query from unix command prompt
mysql -u root -h <hostname> <database name >-e "UPDATE ORDERT SET TYPE ='PARTIAL' WHERE TYPE='FULL'
Viewing mysql command history
There is one hidden file called .mysql-history ,on which all commands are stored which we typed in MySQL console. its generally resides in home directory.
copying data from one table to another
insert into ORDERS_TMP select * from ORDERS
Rename Table
ALTER TABLE new_table RENAME old_table;
modifying a column
alter table ORDERS modify column BRAND varchar(15) default NULL
Dropping columns from the table
ALTER TABLE `database`.`ORDERS` DROP COLUMN `BRAND`;
Repair the table
REPAIR TABLE TableNAme
Adding Keys(Indexes) to a table
alter table ORDERS add KEY `BY_CLIENT` (`CLIENT_ID`) (here CLIENT_ID is a column in ORDers table)
What is Heap table?
Tables that are present in the memory are called as HEAP tables. These tables are commonly known as memory tables. These memory tables never have values with data type like “BLOB” or “TEXT”. They use indexes which make them faster.
How many TRIGGERS are possible in MySql?
There are only six triggers are allowed to use in MySQL database and they are.
Before Insert, After Insert, Before Update, After Update, Before Delete, After Delete
What is BLOB?
- BLOB stands for binary large object.
- It that can hold a variable amount of data.
There are four types of BLOB based on the maximum length of values they can hold:
- TINYBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB
What is TEXT?
TEXT is case-insensitive BLOB. The four types of TEXT are:
- TINYTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT
What is the difference between BLOB and TEXT?
- In BLOB sorting and comparison is performed in case-sensitive for BLOB values
- In TEXT types sorting and comparison is performed case-insensitive.
How is MyISAM table stored?
MyISAM table is stored on disk in three formats.
- ‘.frm’ file – storing the table definition
- ‘.MYD’ (MYData) - data file
- ‘.MYI’ (MYIndex) – index file
- ‘.frm’ file – storing the table definition
- ‘.MYD’ (MYData) - data file
- ‘.MYI’ (MYIndex) – index file
Explain advantages of MyISAM over InnoDB?
- MyISAM follows a much more conservative approach to disk space management – storing each MyISAM table in a separate file, which can be further compresses, if required.
- InnoDB stores the tables in tablespace. Further optimization is difficult with them.
- InnoDB stores the tables in tablespace. Further optimization is difficult with them.
How would you get the current date in Mysql?
By using SELECT CURRENT_DATE();
How would concatenate strings in MySQL?
With the use of - CONCAT (string1, string2, string3)
- MySQL time stamps are seen to a user in a readable format : YYYY-MM-DD HH:MM:SS.
MySQL Transaction
CREATE TABLE EMPLOYEE
(
EMPLOYEE_ID CHAR(5) NOT NULL,
EMPLOYEE_NAME VARCHAR(50) NOT NULL,
PRIMARY KEY (EMPLOYEE_ID))
ENGINE = INNODB;
Autocommit mode
SET AUTOCOMMIT=0;
SET AUTOCOMMIT=1;
Implicit mode:
COMMIT
ROLLBACK
SAVEPOINT
START TRANSACTION
References
http://www.tecmint.com/basic-mysql-interview-questions-for-database-administrators/
http://javarevisited.blogspot.sg/2010/10/frequently-used-mysql-commands-part-1.html
http://javarevisited.blogspot.com/2010/10/frequently-used-mysql-commands-part-2.html
