5 MySql useful queries during project development

by Posted on

Here I am not going to show most common query like INSERT, UPDATE, DELETE etc. I’ll show you some other one step up queries which I feel we required these during development, Generally we create common function and classes at once and we uses these functions and classes many times as per need. But in some cases you need to run mysql queries forcefully in database, So these are the some mysql queries which required often.



5-mysql-useful-query

1. Insert and Update query

This query is very useful to prevent from duplicate entry.
If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;
 
UPDATE table SET c=c+1 WHERE a=1;
INSERT INTO employee (id,name,lastname,dept) values('key that already exists', 'new name','new last name','Accounts') 
on duplicate KEY UPDATE  name='default name', lastname='default last name';

2. Create and Drop Constraints

Often times you need to add/edit/delete constraints applied on a table. You can do so using the following queries:
Add a primary key:

ALTER TABLE 'TABLE_NAME'  DROP PRIMARY KEY, ADD PRIMARY KEY ('COLUMN_NAME');

Drop a Primary key:

ALTER TABLE 'TABLE_NAME'  DROP PRIMARY KEY;

Drop a Unique key:

ALTER TABLE companies DROP INDEX COLUMN_NAME;



3. Order By, Having Clauses

Order by is used to arrange data by a specific column and then having is used to filter that result set. These are really useful when you need some specific information from a large database.

SELECT * FROM TABLE_NAME group_by DEPARTMENT  HAVING salary > 25000;

4. Alter and Update Columns of a Table

We often change property of the table column so with the help of these query you can easily add/edit/delete existing table column property.
Add a column

ALTER TABLE 'employee' ADD COLUMN department VARCHAR(100);

Edit a column

ALTER TABLE 'employee' MODIFY COLUMN department VARCHAR(50);

Rename a column

ALTER TABLE 'employee' CHANGE department dept VARCHAR(50);

Drop a column

ALTER TABLE 'employee' DROP COLUMN department;

5. Creating Dump/backup of Your Database

You can set below query on cronjob to backup you database automatically. just use below useful query and it’ll take backup of your mysql database regular bases.

mysqldump –h localhost –u username –ppassword databasename > backup_file_datatime.sql

If you like this post please don’t forget to subscribe my public notebook for more useful stuff

Published by admin