LiNuX - To modify [ Hour / date ]

$ sudo su
# date 061016592012
Sun Jun 10 16:59:00 PDT 2012

Desc : month day hour minute year

Read More

Mysql && Perl / [ toGenerate_TIME | Show ]

toGenerate_TIME.pl
-------------------------------------------------------------------------------------------------------------------------
#!/usr/bin/perl
use strict;
use warnings;

my $myInterval = shift or die print "Usage : ./toGenerate_TIME.pl 30\n $!";

my $Time_start = 0;

my $Time_stop = 23;

my $interval = 0;

print "create table horas(id int(10) primary key  auto_increment, horario time);\n";

while($Time_start <= $Time_stop){
        if($interval){
                if( $Time_start < 10){
                        print "INSERT INTO horas (horario) VALUES ('0$Time_start:$interval:00');\n";
                }else{
                        print "INSERT INTO horas (horario) VALUES ('$Time_start:$interval:00');\n";
                }
        }else{
                if($Time_start < 10){
                        print "INSERT INTO horas (horario) VALUES ('0$Time_start:00:00');\n";
                }else{
                        print "INSERT INTO horas (horario) VALUES ('$Time_start:00:00');\n";
                }
        }

        $interval += $myInterval;

        if($interval >= 60) {

                $Time_start++;
                $interval = 0;
        }
}

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

$ perl toGenerate_TIME.pl 30

create table horas(id int(10) primary key  auto_increment, horario time);

INSERT INTO horas (horario) VALUES ('00:00:00');

INSERT INTO horas (horario) VALUES ('00:30:00');

INSERT INTO horas (horario) VALUES ('01:00:00');

INSERT INTO horas (horario) VALUES ('01:30:00');

INSERT INTO horas (horario) VALUES ('02:00:00');

INSERT INTO horas (horario) VALUES ('02:30:00');

INSERT INTO horas (horario) VALUES ('03:00:00');

INSERT INTO horas (horario) VALUES ('03:30:00');

INSERT INTO horas (horario) VALUES ('04:00:00');

INSERT INTO horas (horario) VALUES ('04:30:00');

INSERT INTO horas (horario) VALUES ('05:00:00');

INSERT INTO horas (horario) VALUES ('05:30:00');

INSERT INTO horas (horario) VALUES ('06:00:00');

INSERT INTO horas (horario) VALUES ('06:30:00');

INSERT INTO horas (horario) VALUES ('07:00:00');

INSERT INTO horas (horario) VALUES ('07:30:00');

INSERT INTO horas (horario) VALUES ('08:00:00');

INSERT INTO horas (horario) VALUES ('08:30:00');

INSERT INTO horas (horario) VALUES ('09:00:00');

INSERT INTO horas (horario) VALUES ('09:30:00');

INSERT INTO horas (horario) VALUES ('10:00:00');

INSERT INTO horas (horario) VALUES ('10:30:00');

INSERT INTO horas (horario) VALUES ('11:00:00');

INSERT INTO horas (horario) VALUES ('11:30:00');

INSERT INTO horas (horario) VALUES ('12:00:00');

INSERT INTO horas (horario) VALUES ('12:30:00');

INSERT INTO horas (horario) VALUES ('13:00:00');

INSERT INTO horas (horario) VALUES ('13:30:00');

INSERT INTO horas (horario) VALUES ('14:00:00');

INSERT INTO horas (horario) VALUES ('14:30:00');

INSERT INTO horas (horario) VALUES ('15:00:00');

INSERT INTO horas (horario) VALUES ('15:30:00');

INSERT INTO horas (horario) VALUES ('16:00:00');

INSERT INTO horas (horario) VALUES ('16:30:00');

INSERT INTO horas (horario) VALUES ('17:00:00');

INSERT INTO horas (horario) VALUES ('17:30:00');

INSERT INTO horas (horario) VALUES ('18:00:00');

INSERT INTO horas (horario) VALUES ('18:30:00');

INSERT INTO horas (horario) VALUES ('19:00:00');

INSERT INTO horas (horario) VALUES ('19:30:00');

INSERT INTO horas (horario) VALUES ('20:00:00');

INSERT INTO horas (horario) VALUES ('20:30:00');

INSERT INTO horas (horario) VALUES ('21:00:00');

INSERT INTO horas (horario) VALUES ('21:30:00');

INSERT INTO horas (horario) VALUES ('22:00:00');

INSERT INTO horas (horario) VALUES ('22:30:00');

INSERT INTO horas (horario) VALUES ('23:00:00');

INSERT INTO horas (horario) VALUES ('23:30:00');

Read More

Mysql [ if / else | DATA / TIME ]

Sintaxe : IF(<condition>, <value if true>, <value if false>)

mysql> create table reserva(id int(10) primary key  auto_increment, tempo time, data date);

Query OK, 0 rows affected (0.08 sec)

mysql> desc reserva;

+-------+---------+------+-----+---------+----------------+

| Field | Type    | Null | Key | Default | Extra          |

+-------+---------+------+-----+---------+----------------+

| id    | int(10) | NO   | PRI | NULL    | auto_increment |

| tempo | time    | YES  |     | NULL    |                |

| data  | date    | YES  |     | NULL    |                |

+-------+---------+------+-----+---------+----------------+

3 rows in set (0.04 sec)

mysql> insert into reserva (tempo,data) values (CURTIME(),CURDATE());

Query OK, 1 row affected (0.00 sec)

mysql> insert into reserva (tempo,data) values ('10:33:40','2010-06-10');

Query OK, 1 row affected (0.39 sec)

mysql> insert into reserva (tempo,data) values ('05:43:40','2000-03-10');

Query OK, 1 row affected (0.00 sec)

mysql> select curtime();

+-----------+

| curtime() |

+-----------+

| 00:11:42  |

+-----------+

1 row in set (0.03 sec)

mysql> select * from reserva where id = 1 and if(tempo > curtime() , 1 , 0 );

+----+----------+------------+

| id | tempo    | data       |

+----+----------+------------+

|  1 | 17:33:40 | 2012-06-08 |

+----+----------+------------+

1 row in set (0.00 sec)

mysql> select * from reserva where id = 1 and if(tempo > curtime() , 0 , 1 );

Empty set (0.00 sec)

mysql> select CURDATE();

+------------+

| CURDATE()  |

+------------+

| 2012-06-09 |

+------------+

1 row in set (0.00 sec)

mysql> select * from reserva where id = 1 and if(data < curdate() , 1 , 0 );

+----+----------+------------+

| id | tempo    | data       |

+----+----------+------------+

|  1 | 17:33:40 | 2012-06-08 |

+----+----------+------------+

1 row in set (0.00 sec)

mysql> select * from reserva where id = 1 and if(data < curdate() , 0 , 1 );

Empty set (0.00 sec)

mysql> select * from reserva where id = 1 and if(data > curdate() , 1 , 0 );

Empty set (0.00 sec)

mysql> select * from reserva where id = 1 and if(data > curdate() , 0 , 1 );

+----+----------+------------+

| id | tempo    | data       |

+----+----------+------------+

|  1 | 17:33:40 | 2012-06-08 |

+----+----------+------------+

1 row in set (0.00 sec)

Read More

Mysql [Time/Data] / Select|Insert

mysql> create table reserva(id int(10) primary key  auto_increment, tempo time, data date);
Query OK, 0 rows affected (0.08 sec)


mysql> desc reserva;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(10) | NO   | PRI | NULL    | auto_increment |
| tempo | time    | YES  |     | NULL    |                |
| data  | date    | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.04 sec)

mysql> insert into reserva (tempo,data) values (CURTIME(),CURDATE());
Query OK, 1 row affected (0.00 sec)

mysql> select * from reserva;
+----+----------+------------+
| id | tempo    | data       |
+----+----------+------------+
|  1 | 17:33:40 | 2012-06-08 |
+----+----------+------------+
1 row in set (0.00 sec)

mysql> select * from reserva where tempo < CURDATE();

Empty set (0.00 sec)

mysql> select * from reserva where tempo > CURDATE();

+----+----------+------------+

| id | tempo    | data       |

+----+----------+------------+

|  1 | 17:33:40 | 2012-06-08 |

+----+----------+------------+

1 row in set (0.00 sec)

mysql> select * from reserva where data = CURDATE();

+----+----------+------------+

| id | tempo    | data       |

+----+----------+------------+

|  1 | 17:33:40 | 2012-06-08 |

+----+----------+------------+

1 row in set (0.00 sec)

mysql> select * from reserva where data > CURDATE();

Empty set (0.00 sec)

mysql> select * from reserva where data <  CURDATE();

Empty set (0.00 sec)

mysql> insert into reserva (tempo,data) values ('10:33:40','2010-06-10');

Query OK, 1 row affected (0.39 sec)

mysql> insert into reserva (tempo,data) values ('05:43:40','2000-03-10');

Query OK, 1 row affected (0.00 sec)

Read More

Mysql [ Date ] Insert/Select

mysql> create table usr( id int(10) primary key auto_increment, nome longtext, data date);
Query OK, 0 rows affected (0.10 sec)


mysql> insert into usr (nome,data) values('andre','1990-04-07');
Query OK, 1 row affected (0.00 sec)

mysql> insert into usr (nome,data) values('marcelo','1994-03-31');
Query OK, 1 row affected (0.00 sec)

mysql> insert into usr (nome,data) values('baby',CURDATE());
Query OK, 1 row affected (0.00 sec)

mysql> select * from usr;

+----+---------+------------+

| id | nome    | data       |

+----+---------+------------+

|  1 | andre   | 1990-04-07 |

|  2 | marcelo | 1994-03-31 |

|  3 | baby    | 2012-06-07 |

+----+---------+------------+

3 rows in set (0.00 sec)

mysql> select * from usr where month(data) = 4;

+----+-------+------------+

| id | nome  | data       |

+----+-------+------------+

|  1 | andre | 1990-04-07 |

+----+-------+------------+

1 row in set (0.00 sec)

mysql> select * from usr where month(data) > 3;

+----+-------+------------+

| id | nome  | data       |

+----+-------+------------+

|  1 | andre | 1990-04-07 |

|  3 | baby  | 2012-06-07 |

+----+-------+------------+

2 rows in set (0.00 sec)

mysql> select * from usr where month(data) = 3 or month(data) = 6;

+----+---------+------------+

| id | nome    | data       |

+----+---------+------------+

|  2 | marcelo | 1994-03-31 |

|  3 | baby    | 2012-06-07 |

+----+---------+------------+

2 rows in set (0.00 sec)

mysql> select * from pessoa where month(data) = 4 AND year(data) = 1990;

+----+-------+------------+

| id | nome  | data       |

+----+-------+------------+

|  2 | andre | 1990-04-07 |

+----+-------+------------+

1 row in set (0.00 sec)

mysql> insert into usr (nome,data) values('people','1995-09-20');

Query OK, 1 row affected (0.00 sec)

mysql> select * from usr;

+----+---------+------------+

| id | nome    | data       |

+----+---------+------------+

|  1 | andre   | 1990-04-07 |

|  2 | marcelo | 1994-03-31 |

|  3 | baby    | 2012-06-07 |

|  4 | people  | 1995-09-20 |

+----+---------+------------+

4 rows in set (0.00 sec)

mysql> select * from pessoa where month(data) = 4 AND year(data) = 1990;

+----+-------+------------+

| id | nome  | data       |

+----+-------+------------+

|  2 | andre | 1990-04-07 |

+----+-------+------------+

1 row in set (0.00 sec)

mysql> select * from usr where year(data) > 1990 AND year(data) < 2012;

+----+---------+------------+

| id | nome    | data       |

+----+---------+------------+

|  2 | marcelo | 1994-03-31 |

|  4 | people  | 1995-09-20 |

+----+---------+------------+

2 rows in set (0.00 sec)

Its share

Read More

Mysql / LOAD DATA INFILE

mysql> LOAD DATA INFILE 'file.db.txt' INTO TABLE nameTABLE FIELDS TERMINATED BY '\t';

Read More

PERL / DBI / MYSQL - [Select | Insert]

mysql> create table pessoa (id int(10) primary key auto_increment, nome varchar(45) , telefone int(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into pessoa (nome,telefone) values ('Command Line',0000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from pessoa;
+----+--------------+----------+
| id | nome         | telefone |
+----+--------------+----------+
|  1 | Command Line |        0 |
+----+--------------+----------+
1 row in set (0.00 sec)

mysql> desc pessoa;

+----------+-------------+------+-----+---------+----------------+

| Field      | Type         | Null   | Key   | Default | Extra            |

+----------+-------------+------+-----+---------+----------------+

| id          | int(10)        | NO   | PRI | NULL    | auto_increment |

| nome     | varchar(45) | YES  |       | NULL    |                      |

| telefone  | int(10)       | YES  |       | NULL    |                       |

+----------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

file.txt

Kaila   1111

Kalila  2222

Kaori   3333

Karen   4444

Karina  5555

Karine  6666

Karla   7777

Karoline        8888

Kássia 9999

Kate    1010

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

#SELECT 

#!/usr/bin/perl

use strict;

use warnings;

use DBI;

my $database = 'Agenda';

my $user = 'java';

my $password = 'java';

my $dbh = DBI->connect("DBI:mysql:$database", "$user", "$password" ) || die "Could not connect to database: $DBI::errstr";

my $sth = $dbh->prepare('SELECT *  FROM pessoa');

$sth->execute();

while(my @result = $sth->fetchrow_array() ){

        print join ("\t",@result),"\n";

}

$dbh->disconnect();

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

als:/blog$ perl select_mysql_PERL.pl 

1 Command Line 0

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

#INSERT

#!/usr/bin/perl

use strict;

use warnings;

use DBI;

my $database = 'Agenda';

my $user = 'java';

my $password = 'java';

my $dbh = DBI->connect("DBI:mysql:$database", "$user", "$password" ) || die "Could not connect to database: $DBI::errstr";

my $sth = $dbh->prepare( q{ INSERT INTO pessoa (nome,telefone) VALUES (?, ?)}) or die $dbh->errstr;

open (File,"file.txt") or die $!;

while (<File>) {

        chomp;

        my ($nome,$telefone) = split (/\t/,$_);

        $sth->execute($nome,$telefone) or die $dbh->errstr;

}

close();

$dbh->disconnect();

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

als:/blog$ perl insert_mysql_PERL.pl 

als:/blog$ perl select_mysql_PERL.pl 

1 Command Line 0

2 Kaila 1111

3 Kalila 2222

4 Kaori 3333

5 Karen 4444

6 Karina 5555

7 Karine 6666

8 Karla 7777

9 Karoline 8888

10 Kássia 9999

11 Kate 1010

mysql> select * from pessoa;

+----+--------------+----------+

| id | nome         | telefone |

+----+--------------+----------+

|  1 | Command Line |        0 |

|  2 | Kaila        |     1111 |

|  3 | Kalila       |     2222 |

|  4 | Kaori        |     3333 |

|  5 | Karen        |     4444 |

|  6 | Karina       |     5555 |

|  7 | Karine       |     6666 |

|  8 | Karla        |     7777 |

|  9 | Karoline     |     8888 |

| 10 | Kássia      |     9999 |

| 11 | Kate         |     1010 |

+----+--------------+----------+

11 rows in set (0.00 sec)

It shares.

Read More