Find the text or value in MYSQL table using REGEXP

We can use REGEXP command to find the string value in MYSQL table.
SELECT * FROM <table> WHERE <column> REGEXP '<string>';

Ex: SELECT * FROM watchdog WHERE message REGEXP 'message'

Find table column in MySQL

The following query is used to show all columns in each table in a database

SELECT * FROM information_schema.columns
WHERE table_schema = 'db_name'
ORDER BY table_name,ordinal_position

CASE WHEN option in MySQL

MySQL will return conditional based values without using if condition. Without using if conditions, we can use CASE WHEN option in MySQL.

For example,

For example: table name: students

sno	name	mark
1	Sachin	39
2	Dravid	90
3	Kohli	30
4	Ganguly	66

Output: Pass mark is above 40

sno	name	result
1	Sachin	Fail
2	Sachin	Pass
3	Kohli	Fail
4	Ganguly	Pass

Query:

SELECT sno, name, CASE WHEN  mark >= 40 THEN 'Pass' ELSE 'Fail' END AS Result FROM student

Remove or Delete duplicate records in a table in MySQL

Remove the duplicate records in a table, it will delete records which is having greater than the sno

For example: table name: players

sno	name	coach_id
1	Sachin	1
2	Sachin	5
3	Kohli	1
4	Ganguly	2
5	Kohli	4
6	Gambir	3

Query:

DELETE FROM players USING players , players AS virtualtable

WHERE players .sno > virtualtable.sno

AND players .name = virtualtable.name

Output:

sno	name	coach_id
1	Sachin	1
3	Kohli	1
4	Ganguly	2
6	Gambir	3

Display limited records with out using LIMIT in MySQL

If you want to get display limited records with out using LIMIT option in MySQL.

For example: table name: players

sno	name	coach_id
1	Sachin	1
2	Dravid	5
3	Kohli	1
4	Ganguly	2
5	Sehwag	4
6	Gambir	3

Query:

SELECT one.sno, one.name FROM players AS one

WHERE ( SELECT COUNT(*) FROM players AS two WHERE two.sno <= one.sno ) <= 3

Output:

sno	name	coach_id
1	Sachin	1
2	Dravid	5
3	Kohli	1

Get data condition related information from a single table

If you want to display the data with condition based from a single table.

For example: table name: players

sno	name	coach_id
1	Sachin	1
2	Dravid	5
3	Kohli	1
4	Ganguly	2
5	Sehwag	4
6	Gambir	3

Query:

SELECT player.sno, player.name, coach.name AS coachname

FROM players AS coach

JOIN players AS player ON coach.sno = player.coach_id

Output:

sno	name	coach_id
1	Sachin	Sachin
2	Dravid	Sehwag
3	Kohli	Sachin
4	Ganguly	Dravid
5	Sehwag	Ganguly
6	Gambir	Kohli

List out the drupal query functions

The following query functions are used in

db_connect - Initialize a database connection.

db_query - Execute query

db_result - Return an individual result field from the previous query.

db_affected_rows - Determine the number of rows changed by the preceding query.

db_rewrite_sql - Rewrites node, taxonomy and comment queries. Use it for listing queries

db_create_table -Create a new table from a Drupal table definition.

db_field_names - Return an array of field names from an array of key/index column specifiers.

db_query_range - Runs a limited-range query in the active database.

db_fetch_array - Fetch one result row from the previous query as an array

db_fetch_object - Fetch one result row from the previous query as an object.

db_error - Determine whether the previous query caused an error.

db_lock_table - Lock a table.

db_unlock_tables - Unlock all locked tables.

db_table_exists - Check if a table exists.

db_column_exists - Check if a column exists in the given table.

Manage MySQL using shell scripting.

Connect MySQL : mysql -h -u -p

Create a DB: mysql> create database

Use a DB for operate: mysql> use

Show all databases: mysql> show databases;

Show the running quries: mysql> process list

Kill the query: mysql> kill

Difference between MySQL DELETE and MySQL TRUNCATE

DELETE deletes row by row, while TRUNCATE deletes all rows at once.

For example:

DELETE * FROM table WHERE status =0;

TRUNCATE table;

MySQL DB Engines.

List of MySQL DB engines,

MyISAM - Default database engine. Table-Level locking mechanism. Doesn't support transactions. consume less disk space. speedy read operations,so popular for Web development

InnoDB - Row level locking mechanism. Transaction-safe (commit, rollback, and crash recovery capabilities). consume more disk space. Slower than the MyISAM engines.

ISAM - ISAM performs very fast read operations. Doesn't support transactions. If your hard drive crashes, the data files will not be recovered.

Memory (Heap) - HEAP allows for temporary tables that reside only in memory. Residing in memory makes HEAP faster than ISAM or MyISAM. Its volatile and will be lost if it's not saved prior to shutdown.

Archive - Stores very large volumes of infrequently-or-never updated information

Merge - The MERGE engine type allows you to combine a number of identical tables into a single table.

Federated - Accesses data in tables of remote databases. No data is stored on the local tables

NDB Cluster - For distributed computing environment(running several MySQL servers in a cluster).

CSV - Data from any CSV engine file can easily be imported into any standard spreadsheet.

BDB (Berkeley DB) - Each BDB table is stored on disk in two 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, and a .db file contains the table data and indexes. To support transaction rollback, the BDB storage engine maintains log files

Advantages of InnoDB and MyISAM

Advantages of InnoDB over MyISAM:

Row-level locking, transactions, foreign key constraints and crash recovery

Advantages of MyISAM over InnoDB:

Disk space management

All data except for TEXT and BLOB can occupy 8,000 bytes at most

No full text indexing is available for InnoDB

COUNT(*)s execute slower than in MyISAM due to tablespace complexity

How to create a copy of the database in MySQL in command line?

You can create a copy of mysql database using mysqldump command line.

mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql