creating user on mysql

CREATE USER 'freepbxuser'@'localhost' IDENTIFIED BY '2366';




SET PASSWORD FOR 'freepbxuser'@'localhost' = PASSWORD('Linux456');




GRANT ALL PRIVILEGES ON asterisk.* TO freepbxuser@localhost IDENTIFIED BY 'Linux456';


dandole permiso a todas las tables

CREATE USER 'admin'@'localhost' IDENTIFIED BY '2366';

mysql> GRANT ALL PRIVILEGES ON *.*  TO admin@localhost IDENTIFIED BY '012130';

grant   to login from any IP

GRANT ALL PRIVILEGES ON *.*  TO admin@'%' IDENTIFIED BY '7812333';

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

mysql 8

mysql> CREATE USER 'phpmyadmin'@'%' IDENTIFIED BY '9YB11111';

GRANT ALL ON *.* TO 'phpmyadmin'@'%';

FLUSH PRIVILEGES;

USE mysql;

CREATE USER 'user'@'localhost' IDENTIFIED BY 'P@ssW0rd';

GRANT ALL ON *.* TO 'user'@'localhost';

FLUSH PRIVILEGES;

Comparison Functions and Operators

Table 13.3 Comparison Operators

Name	Description
BETWEEN ... AND ...	Check whether a value is within a range of values
COALESCE()	Return the first non-NULL argument
=	Equal operator
<=>	NULL-safe equal to operator
>	Greater than operator
>=	Greater than or equal operator
GREATEST()	Return the largest argument
IN()	Check whether a value is within a set of values
INTERVAL()	Return the index of the argument that is less than the first argument
IS	Test a value against a boolean
IS NOT	Test a value against a boolean
IS NOT NULL	NOT NULL value test
IS NULL	NULL value test
ISNULL()	Test whether the argument is NULL
LEAST()	Return the smallest argument
<	Less than operator
<=	Less than or equal operator
LIKE	Simple pattern matching
NOT BETWEEN ... AND ...	Check whether a value is not within a range of values
!=, <>	Not equal operator
NOT IN()	Check whether a value is not within a set of values
NOT LIKE	Negation of simple pattern matching
STRCMP()	Compare two strings

SELECT * FROM tbl_name WHERE date_column IS NULL

http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_is-null

checking mysq logs

cat /var/log/mysqld.log | grep -i error | less

checking diffrence of hours and days

Hour differences
mysql> select * ,TIMEDIFF(now(),time),now()  from calls order by time desc;
+----+-------------+-------------+---------------------+------------+------------+----------------------+---------------------+
| id | phone       | did         | time                | rerserved2 | rerserved3 | TIMEDIFF(now(),time) | now()               |
+----+-------------+-------------+---------------------+------------+------------+----------------------+---------------------+
|  7 | 18099221258 | 13065201258 | 2016-02-19 20:09:46 |       NULL |       NULL | 00:38:37             | 2016-02-19 20:48:23 |
|  6 | 15122361010 | 14587143030 | 2016-02-19 20:09:27 |       NULL |       NULL | 00:38:56             | 2016-02-19 20:48:23 |
|  5 | 9100        | 101         | 2016-02-17 00:27:25 |       NULL |       NULL | 68:20:58             | 2016-02-19 20:48:23 |
|  4 | 9100        | 101         | 2016-02-17 00:27:22 |       NULL |       NULL | 68:21:01             | 2016-02-19 20:48:23 |
|  3 | 13052361010 | 100         | 2016-01-22 21:58:49 |       NULL |       NULL | 670:49:34            | 2016-02-19 20:48:23 |
|  2 | 13052361010 | 18007143030 | 2016-01-22 21:57:34 |       NULL |       NULL | 670:50:49            | 2016-02-19 20:48:23 |
|  1 | 13052361010 | 18007143030 | 2016-01-22 21:56:21 |       NULL |       NULL | 670:52:02            | 2016-02-19 20:48:23 |
+----+-------------+-------------+---------------------+------------+------------+----------------------+---------------------+
7 rows in set (0.00 sec)

Day difference

mysql> select * ,datediff(now(),time),now()  from calls order by time desc;

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

| id | phone       | did         | time                | rerserved2 | rerserved3 | datediff(now(),time) | now()               |

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

|  7 | 18099221258 | 13065201258 | 2016-02-19 20:09:46 |       NULL |       NULL |                    0 | 2016-02-19 20:50:18 |

|  6 | 15122361010 | 14587143030 | 2016-02-19 20:09:27 |       NULL |       NULL |                    0 | 2016-02-19 20:50:18 |

|  5 | 9100        | 101         | 2016-02-17 00:27:25 |       NULL |       NULL |                    2 | 2016-02-19 20:50:18 |

|  4 | 9100        | 101         | 2016-02-17 00:27:22 |       NULL |       NULL |                    2 | 2016-02-19 20:50:18 |

|  3 | 13052361010 | 100         | 2016-01-22 21:58:49 |       NULL |       NULL |                   28 | 2016-02-19 20:50:18 |

|  2 | 13052361010 | 18007143030 | 2016-01-22 21:57:34 |       NULL |       NULL |                   28 | 2016-02-19 20:50:18 |

|  1 | 13052361010 | 18007143030 | 2016-01-22 21:56:21 |       NULL |       NULL |                   28 | 2016-02-19 20:50:18 |

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

numbers of hours

mysql> select * ,hour(TIMEDIFF(now(),time)),now()  from calls order by time desc;
+----+-------------+-------------+---------------------+------------+------------+----------------------------+---------------------+
| id | phone       | did         | time                | rerserved2 | rerserved3 | hour(TIMEDIFF(now(),time)) | now()               |
+----+-------------+-------------+---------------------+------------+------------+----------------------------+---------------------+
|  7 | 18099221258 | 13065201258 | 2016-02-19 20:09:46 |       NULL |       NULL |                          1 | 2016-02-19 21:40:20 |
|  6 | 15122361010 | 14587143030 | 2016-02-19 20:09:27 |       NULL |       NULL |                          1 | 2016-02-19 21:40:20 |
|  5 | 9100        | 101         | 2016-02-17 00:27:25 |       NULL |       NULL |                         69 | 2016-02-19 21:40:20 |
|  4 | 9100        | 101         | 2016-02-17 00:27:22 |       NULL |       NULL |                         69 | 2016-02-19 21:40:20 |
|  3 | 13052361010 | 100         | 2016-01-22 21:58:49 |       NULL |       NULL |                        671 | 2016-02-19 21:40:20 |
|  2 | 13052361010 | 18007143030 | 2016-01-22 21:57:34 |       NULL |       NULL |                        671 | 2016-02-19 21:40:20 |
|  1 | 13052361010 | 18007143030 | 2016-01-22 21:56:21 |       NULL |       NULL |                        671 | 2016-02-19 21:40:20 |
+----+-------------+-------------+---------------------+------------+------------+----------------------------+---------------------+
7 rows in set (0.00 sec)

selecting fields    with insertion date no longer than 1 hour

mysql> select * ,hour(TIMEDIFF(now(),time)),now()  from calls where hour(TIMEDIFF(now(),time))>=0 and hour(TIMEDIFF(now(),time)) <= 1 order by time desc;
+----+-------------+-------------+---------------------+------------+------------+----------------------------+---------------------+
| id | phone       | did         | time                | rerserved2 | rerserved3 | hour(TIMEDIFF(now(),time)) | now()               |
+----+-------------+-------------+---------------------+------------+------------+----------------------------+---------------------+
|  8 | 12122361010 | 16587143030 | 2016-02-19 21:43:18 |       NULL |       NULL |                          0 | 2016-02-19 21:45:08 |
|  7 | 18099221258 | 13065201258 | 2016-02-19 20:09:46 |       NULL |       NULL |                          1 | 2016-02-19 21:45:08 |
|  6 | 15122361010 | 14587143030 | 2016-02-19 20:09:27 |       NULL |       NULL |                          1 | 2016-02-19 21:45:08 |
+----+-------------+-------------+---------------------+------------+------------+----------------------------+---------------------+

substracting time and days from a date

mysql> select now() as currenttime, date_sub(now() , interval 5 day ) as daypassed;
+---------------------+---------------------+
| currenttime         | daypassed           |
+---------------------+---------------------+
| 2016-02-19 20:33:35 | 2016-02-14 20:33:35 |
+---------------------+---------------------+
1 row in set (0.00 sec)






mysql> select now() as currenttime, date_sub(now() , interval 3 hour ) as hourpassed;
+---------------------+---------------------+
| currenttime         | hourpassed          |
+---------------------+---------------------+
| 2016-02-19 20:34:06 | 2016-02-19 17:34:06 |
+---------------------+---------------------+
1 row in set (0.00 sec)

select rows between range of time

mysql> select * from calls where   time between '2016-01-22 21:56:21' and now();

mysql> select * from calls where   time between '2016-01-22 21:56:21' and '2016-01-22 21:57:34';

Creating and Selecting a Database

If the administrator creates your database for you when setting up your permissions, you can begin using it. Otherwise, you need to create it yourself:

mysql> CREATE DATABASE menagerie;

Under Unix, database names are case sensitive (unlike SQL keywords), so you must always refer to your database as menagerie, not as Menagerie, MENAGERIE, or some other variant. This is also true for table names. (Under Windows, this restriction does not apply, although you must refer to databases and tables using the same lettercase throughout a given query. However, for a variety of reasons, the recommended best practice is always to use the same lettercase that was used when the database was created.)

Note

If you get an error such as ERROR 1044 (42000): Access denied for user 'micah'@'localhost' to database 'menagerie' when attempting to create a database, this means that your user account does not have the necessary privileges to do so. Discuss this with the administrator or see Section 6.2, “The MySQL Access Privilege System”.

Creating a database does not select it for use; you must do that explicitly. To make menagerie the current database, use this statement:

mysql> USE menagerie
Database changed

Your database needs to be created only once, but you must select it for use each time you begin a mysql session. You can do this by issuing a USE statement as shown in the example. Alternatively, you can select the database on the command line when you invoke mysql. Just specify its name after any connection parameters that you might need to provide. For example:

shell> mysql -h host -u user -p menagerie
Enter password: ********

Important

menagerie in the command just shown is not your password. If you want to supply your password on the command line after the -p option, you must do so with no intervening space (for example, as -pmypassword, not as -p mypassword). However, putting your password on the command line is not recommended, because doing so exposes it to snooping by other users logged in on your machine.

Note

You can see at any time which database is currently selected using SELECT DATABASE().