Archive

Archive for the ‘Mysql’ Category

Connect Mysql from git on windows

December 16, 2016 Leave a comment

We can connect to mysql from git on window through this command.

winpty mysql -uROOT -pPASSWORD

Advertisements
Categories: Mysql Tags: , , , ,

MongoDb Introduction

April 7, 2016 Leave a comment

MongoDB Databse-
MongoDB is NoSql and document oriented database. MongoDB stores data using a flexible document data model that is similar to JSON.

Create Database-
>use yaju;

Show Database-
>show dbs

Drop Database-
To drop database, first use that database then use following command.
>db.dropDatabase();

Create Collection-
Collections are like table in MySql. We can create collection in two ways.

First, we can create empty collection
>db.createCollection(‘test’);

Second, we can create dynamic collection with insert command. Mongo check that collection if collection is exists then insert data in existing collection otherwise create new collection and insert data. That can be through followning command.
>db.testtable.insert({‘name’:’yajuvendra’},{‘name’:’yaj’});

Show collections-
Show all collection in a database use following command.
>show collections;

Insert document-
We can insert single document and multiple document as well.

Single document
>db.testtable.insert({‘name’:’yajuvendra’},{‘name’:’yaj’});
Multiple document
>db.testtable.insert([{‘name’:’yajuvendra’},{‘name’:’yaj’}, {‘name’:’yaju’},{‘name’:’yajkumar’}]);

Select document-
Select all document
>db.testtable.find();
Select particular document
> db.testtable.find({‘name’:’yaj’});

Remove document-
> db.testtable.remove({‘name’:’yaju’});

Update document-
> db.testtable.update({name:’yaj’},{$set:{name:’yaju_update’}})

Mysql Important

November 3, 2015 Leave a comment

 

#Login to mysql
mysql> -hlocalhost -P3306 -u{user name} -p

#Select Current User
mysql bin folder>select user();
Output-
+—————-+
| user() |
+—————-+
| root@localhost |
+—————-+
1 row in set (0.00 sec)

#Select Mysql Version
mysql bin folder>select version();
Output-
+———–+
| version() |
+———–+
| 5.6.17 |
+———–+
1 row in set (0.00 sec)

#Select Current Database
mysql bin folder>select database();
Output- These is not database selected.
+————+
| database() |
+————+
| NULL |
+————+
1 row in set (0.00 sec)

#Select Current date with Hour Minute and Second
mysql bin folder>select now();
Output-
+———————+
| now() |
+———————+
| 2016-04-04 12:52:55 |
+———————+
1 row in set (0.00 sec)

#Select Current date, shows only date.
mysql bin folder>select now();
Output-
+—————-+
| current_date() |
+—————-+
| 2016-04-04 |
+—————-+
1 row in set (0.00 sec)

#Export database or table
mysql bin folder>mysqldump -hlocalhost -P3306 -u{username} -p{password} {database name} {table name} > export file path/filename.sqlpath/filename.sql

#Import database or table
mysql bin folder>mysql -hlocalhost -P3306 -u{username} -p{password} {database name} {table name} < import file path/filename.sql

VIEW
Create: mysql>create view {view name} as select * from {table name};
View: mysql>show full tables;
mysql>show full tables in {database name} table_type like ‘view’;
Drop: mysql>drop view {view name};

TRIGGER
Create:
mysql>delimiter //
mysql> create trigger {trigger name}
after insert
on {table name} for each row
begin
insert into {table name} values(”, new. {new value});
end;//
mysql>delimiter ;

View:
mysql>show triggers;

Drop:
mysql>drop trigger {trigger name};

STORED PROCEDURES
Create:
mysql>delimiter //
mysql>create procedure {procedure name}(in inputvalue int, out outputvalue int)
begin
outputvalue = inputvalue;
end; //
mysql>delimiter ;

Call:
mysql>call {procedure name}(inputvalue, @output);
mysql>select @output;(result 100);

View:
mysql>show procedure status;

Drop:
mysql>drop procedure {procedure name};

STORED FUNCTIONS
Create:
mysql>delimiter //
mysql>create function {function name}({parameter name} {data type})
returns {data type}
begin
declare {variable name} {data type};
select {variable name} into {paramerter name} from {table name} where id={parameter name};
return ({variable name});
end; //
mysql>delimiter ;

 

Categories: Mysql Tags: , , , , ,

How to copy a table structure and data

November 9, 2011 5 comments

To copy table structure run this query

CREATE TABLE database_name.`new_table_name` LIKE database_name.`old_table_name` ;

To copy table data run this query

INSERT INTO database_name.`new_table_name` SELECT * FROM database_name.`old_table_name` ;

Export database from MYSQL to Ms Access

April 14, 2010 Leave a comment
Download ODBC driver from
Create a system DSN for the MySQL database.
Create a new Access database, then do
File Get External Data Import…
In the Import dialog click the “Files of type” drop-down and choose
“ODBC Databases ()”. In the “Select Data Source” dialog click the
“Machine Data Source” , select the DSN you created, and click “OK”.
Select all of the tables and import them.

Download ODBC driver from http://dev.mysql.com/downloads/connector/odbc/
Create a system DSN for the MySQL database. Create a new Access database, then doFile Get External Data Import…
In the Import dialog click the “Files of type” drop-down and choose”ODBC Databases ()”. In the “Select Data Source” dialog click the”Machine Data Source” , select the DSN you created, and click “OK”.Select all of the tables and import them.

Find second largest element

March 22, 2010 1 comment

A query to find out nth maximum number in a table.
Let consider a example:

Table name is ‘test’ and this table has two field i.e id and element. Table structure looks like..

————————
| id      | element
————————
|1        | 2
|1        | 4
|1        | 1
|1        | 7
|1        | 5
|1        | 9
————————

Now write a query to find second max element in this table.

SELECT element FROM test ORDER BY element DESC LIMIT 1,1;
above query output is:7

Now common query is:
SELECT element FROM test ORDER BY element DESC  LIMIT ( n – 1 ) , 1 ;
Here n is maximum number, which you want to find.

%d bloggers like this: