subquery in mysql | update join query | mysql substring

subquery in mysql | update join query | mysql substring

case :- inserting records from one table to another (select insert)

•  hear, i have one table named 'initial_keyword_test'. it contains data.
•  and have another table named 'normal_keyword_test' which is empty table.
•  both of the table haven't same table structure.
•  but, some fields names are matching.
•  i want to insert data in normal_keyword_test from initial_keyword_test.

Example :-   

INSERT INTO `normal_keyword_test`
(`final_keyword_id`,
`keyword`)
select
`final_keyword_id`,
`keyword`
from initial_keyword_test

-----------------------------------------------------------------------------------------------------------------------------------

case :- update join query

Problem :- i want update
                 keyword_id(normal_keyword_test)
                 same as final_keyword_id(initial_keyword_test)

initial_keyword_test
-----------------------------------------------------------------------------------------------------
|final_keyword_id | keyword | keyword_rank  | keyword_exist  |
-----------------------------------------------------------------------------------------------------
| 2                        | A             | 5                       | yes                   |
| 5                        | M            | 8                       | no                     |
| 7                        | X             | 11                     | yes                   |
| 9                        | N             | 15                     | yes                  |            
| 10                      | G             | 25                     | yes                   |
-----------------------------------------------------------------------------------------------------


normal_keyword_test
--------------------------------------
|keyword_id | keyword |
--------------------------------------
| 1                | A            |
| 2                | M           |
| 3                | X            |
| 4                | N           |
| 5                | G            |
--------------------------------------

Example :-

UPDATE   
normal_keyword_test km
INNER JOIN initial_keyword_test fkm
ON km.keyword = fkm.keyword
SET km.keyword_id= fkm.final_keyword_id ;

Output :-

normal_keyword_test
--------------------------------------
|keyword_id | keyword |
--------------------------------------
| 2                | A            |
| 5                | M           |
| 7                | X            |
| 9                | N           |
| 10              | G            |
--------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------

case :- load csv in mysql table

Example :-

load data local infile '/home/vishal.khokhar/department.csv' into table department_master
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(`department_id`,
`department_name`,
`firm_name`,
`firm_field`,
`number_of_employee`)

-----------------------------------------------------------------------------------------------------------------------------------

case : howmany tables in database ?

Syntax  :- 

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'databasename';

Example :-

SELECT COUNT(*) as 'Totaltables' FROM information_schema.tables WHERE table_schema = 'lead';
here, lead is database name.

Output :-

----------------------
| Totaltables |
----------------------
| 105            |
----------------------
-----------------------------------------------------------------------------------------------------------------------------------

case : tunnel one database to another database table data transfer

Syntax :-

mysqldump --compress -h<host> -u <username> -p<password>  --no-create-info  <databasename>  <tablename>  | mysql --compress -h <host> -u <username> -p<password> <databasename> ;

-----------------------------------------------------------------------------------------------------------------------------------

case : get database dump on one database server and extract into another database server

this command get database dump in gz compression(gz is reduced size of dump easy to move).

/usr/bin/mysqldump -u<username> -p<password> -h<host> <databasename> | gzip -9 > <dumpname_whateveryouwant>.dmp.gz

copy dump in destination server
fire those commands in destination server.

this command get database dump in dmp format.
gunzip <dumpname>.dmp.gz

this command insert database dump in your destination server.
source <dumpname>.dmp
-----------------------------------------------------------------------------------------------------------------------------------

case : update table on praticular string in columns.

Syntax :- 

update table
          set column_name = REPLACE (column_name, 'searchingString', 'replaceingString')
          where condition;

Example :- 

update urlinfo
           set url = REPLACE (url, 'http://', 'https://')
           where url_id between 1 AND 100;

here, urlinfo  :- table name
         url        :- field which is contain url
         url_id   :- field which is contain unique_id.

Output:-

before query
-------------------------------------------------------
|        url                                        |
-------------------------------------------------------
| http://scrapemania.blogspot.in/  |
-------------------------------------------------------

after query
-------------------------------------------------------
|        url                                        |
-------------------------------------------------------
| https://scrapemania.blogspot.in/ |
-------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------

case :- casting String type numbered value into float

Syntax :-

CAST(value AS TYPE);

Example :-

select price,CAST(IFNULL(price,'9999') AS DECIMAL(10,2)) as 'FloatPrice' FROM price_master;

Output:-

-------------------------------------------

|  price          |  FloatPrice |

-------------------------------------------
| 42.066667  | 42.07          |
| 36               | 36.00          |
| 37.42          | 37.42          |
| NULL         | 9999.00      |
| 46               | 46.00          |
-------------------------------------------

Example :- 

select price,CAST(price AS DECIMAL(10,2)) as 'FloatPrice' FROM price_master;

Output:-

------------------------------------------

|  price         |  FloatPrice |
------------------------------------------
| 42.066667 | 42.07         |
| 36              | 36.00         |
| 37.42         | 37.42         |
| NULL        | NULL        |
| 46              | 46.00         |
------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

case :- getting some string portion from string (substring)

Syntax  :- 

select SUBSTRING(field,starting_position,number_of_character);

Example :- 

select SUBSTRING('2017-02-02 08:11:35',12,8) as 'Time';

Output :-

--------------------

|  Time        |
--------------------

| 08:11:35   |
--------------------