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.
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