Archive

Posts Tagged ‘unnao’

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: , , , , ,
%d bloggers like this: