This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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?
You can use SQL_CALC_FOUND_ROWS in SELECT statement and execute FOUND_ROWS().
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.
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; |
How to see a description or definition of the Table?
|
1 |
describe tablename; |
How to find top N records for each group?
Please visit this post:
How to arrange rows to columns Pivot table in MySQL?
Please visit this post:
How to calculate Rank in MySQL Query?
Please visit this post:
How to write Insert-Update, merge statement in MySQL?
Please visit this post:
How to insert a record if not exists in MySQL?
Please visit this post:
