Category Archives: Mysql

Mysql is the world’s most popular Open Source Relational SQL database management system. It is used for enabling the cost-effective delivery of reliable, high-performance and scalable Web-based and embedded database applications.

search table columns for a word in Mysql database

Posted on by
Reply

To search the database table for the exact word.

1) Using LIKE

$query = “SELECT * FROM table_name WHERE column LIKE ‘%key_word%’ “;

The above query searches for the string which contains key_word as a single word or mixed in a word.

Example :

if you want to search for a keyword “tea”, above query will fetch records which contain words like “steak”, “teas” ect.

Alternate query is

$query = “SELECT * FROM table_name WHERE column LIKE ‘% key_word %’ “;

using space before and after key_word, which fetches the record  that matches the exact key_word.

there is a little problem with the above query, if the key_word present in the end of the sentence ( total text/string), it wont fetch that record.

Example :

if the string is “this is the test” and if searching for the ‘test’ key_word, the above query will not fetch this record because there is no trailing space after the ‘test’ key_word.

 

2) Using Regular Expression

$query = “SELECT * FROM table_name WHERE column REGEXP ‘[[:<:]]key_word[[:>:]] ‘ “;

The above query will search for the exact word.

In order to search for a column which does not contain a particular word use below query

$query = “SELECT * FROM table_name WHERE column NOT REGEXP ‘[[:<:]]key_word[[:>:]] ‘ “;

The above query will fetch the records which does not contain the key_word in the column.

 

 

 

 

 

Import big database

Posted on by
1

Uploading / Importing big database through command line.

1) Set the mysql as environment variable.

2) go to the folder where the sql dump file is stored.

3) Run the command  :\>mysql -u username -p -h hostname databasename < sql_dump_file_name.

If you get any error like “Mysql server has gone away” or “got a packet bigger than ‘max_allowed_packet’ bytes”

Please follow the below steps.

a) Run the command :\>mysql -u root -p

b) It will ask for password, if you have any password set enter it or simple click the Enter button.

c) Now you will see the command prompt as mysql>

d) Now run the below to commands 

mysql> set global max_allowed_packet=1000000000;
mysql> set global net_buffer_length=1000000;

mysql>exit;

Now follow above mentioned 2 and 3 point.

If every thing went fine your database is upload is successful.

 

Example :

Let say you are running on the xampp which is installed in the D drive.

You have placed the sql dump folder in the xampp folder

Go the command prompt.

:\>d:

D:\>cd xampp

D:\xampp>mysql -u username -p -h hostname databasename < sql_dump_file_name.sql

 

For further reference please follow the below link

http://wpmu.org/how-to-backup-and-import-a-very-large-wordpress-mysql-database/

 

 

 

Mysql commands

Posted on by
Reply

 

CREATE DATABASE IF NOT EXISTS database_name;
SHOW DATABASES;
USE database_name;
DROP DATABASE IF EXISTS database_name;

 

CREATE TABLE IF NOT EXISTS table_name (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(45) DEFAULT NULL,
date DATE DEFAULT NULL,
date_time DATETIME DEFAULT NULL,
description text DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE INDEX id_UNIQUE (id ASC)
) ENGINE=engine_name

column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT]

 

ALTER :

Alter Column:

syntax :

ALTER TABLE table_name CHANGE COLUMN column_name column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT];

Example :

ALTER TABLE table_name
CHANGE COLUMN id id INT(11) NOT NULL AUTO_INCREMENT

Add Column :

Syntax :

ALTER TABLE table_name ADD COLUMN column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT] AFTER existing_column_name;

Example :

ALTER TABLE table_name ADD COLUMN price DECIMAL(6,2) NULL AFTER description;

Drop Column :

syntax :

ALTER TABLE table_name DROP COLUMN column_name;

Example :

ALTER TABLE table_name DROP COLUMN price;

Rename Table :

syntax :

ALTER TABLE table_name RENAME TO new_table_name;

 

DROP TABLE :

syntax :

DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name1] … [RESTRICT | CASCADE]

Example :

DROP TABLE IF EXISTS table1, other_table;
DROP TABLE LIKE ‘%pattern%’

 

INDEX :

Create:

syntax:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name USING [BTREE | HASH | RTREE] ON table_name (column_name [(length)] [ASC | DESC],…)

Example :

CREATE INDEX name ON table_name(name);

Drop:

Syntax :

DROP INDEX index_name ON table_name

Example :

DROP INDEX name ON table_name

 

SELECT :

Syntax :

SELECT a.column_1,b.column_2…
FROM table_name AS a
[INNER | LEFT |RIGHT] JOIN table_name1 AS b ON join_conditions
WHERE conditions
GROUP BY group
HAVING group_conditions
ORDER BY column_1 [ASC | DESC]
LIMIT offset, row_count;

 

ORDER BY :

SELECT orderNumber, status
FROM orders
ORDER BY FIELD(status, ‘In Process’,
‘On Hold’,
‘Cancelled’);

 

 

 

 

 

 

 

http://www.pantz.org/software/mysql/mysqlcommands.html

http://www.mysqltutorial.org

 

Use ; to exit the from a query/command