Here I am not going to indicate most regular query like INSERT, UPDATE, DELETE and so on. I’ll demonstrate to you some other one stage up query which I feel we required these amid advancement, Generally we make basic function and classes on the double and we utilizes these capacities and classes commonly according to require. In any case, now and again you have to run mysql query powerfully in database, So these are the some mysql queries which required regularly.
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