Category: mysql


Example:

mysql> select id,name from student_detail limit 20;
+—-+—————–+
| id | name            |
+—-+—————–+
|  1 | SIVASANKARAN.S. |
|  2 | SELVAM P.       |
|  3 | MANIKANDAN  A   |
|  4 | KIRUTHIKA K     |
|  5 | PERIYANNAN K    |
|  6 | ANBU CHOZHAN    |
|  7 | DIVYA R         |
|  8 | ANJALAI M       |
|  9 | VASANTHI S      |
| 10 | BHUVANESHWARI M |
| 11 | SATHYA S        |
| 12 | KANAGA M        |
| 13 | JHON PALRAJ.A   |
| 14 | AMSAVALLI K     |
| 15 | RENGANATHAN M   |
| 16 | ANTHONI MANOJ.F |
| 17 | MANJU S         |
| 18 | VIJAYAKUMAR     |
| 19 | SELVAM S        |
| 20 | MARIYAPPAN P    |
+—-+—————–+
If you want to delete the records from 11 to 15 use the following query

mysql> delete from student_detail where id between 11 and 15;

mysql> select id,name from student_detail limit 20;
+—-+—————–+
| id | name            |
+—-+—————–+
|  1 | SIVASANKARAN.S. |
|  2 | SELVAM P.       |
|  3 | MANIKANDAN  A   |
|  4 | KIRUTHIKA K     |
|  5 | PERIYANNAN K    |
|  6 | ANBU CHOZHAN    |
|  7 | DIVYA R         |
|  8 | ANJALAI M       |
|  9 | VASANTHI S      |
| 10 | BHUVANESHWARI M |
| 16 | ANTHONI MANOJ.F |
| 17 | MANJU S         |
| 18 | VIJAYAKUMAR     |
| 19 | SELVAM S        |
| 20 | MARIYAPPAN P    |
+—-+—————–+

If a field contains date values with varchar data type, the following is the query to get year from that field

mysql> select identification_date from table1;

+———————+
| identification_date |
+———————+
| 11/05/2013          |
| 12/04/2012          |
| 02/06/2011          |
| 11/04/2012          |
| 11/04/2010          |
+———————+
5 rows in set (0.00 sec)

mysql > select YEAR(STR_TO_DATE(identification_date,’%d/%m/%Y’)) from table1;

+—————————————————+
| YEAR(STR_TO_DATE(identification_date,’%d/%m/%Y’)) |
+—————————————————+
|                                              2013 |
|                                              2012 |
|                                              2011 |
|                                              2012 |
|                                              2010 |
+—————————————————+
5 rows in set (0.03 sec)

MySql                                              Postgres

show database                                  \l

show tables;                                       \d

desc table table_name;                    \d+  table_name    or   \d  table_name

Backuping a single table from a database

mysqldump -u -p database1 table_name > /home/backups/table_name.sql

Restoring the table into another database

mysql -u -p database2 < /home/backups/table_name.sql

$ cd ~
$ cat .mysql_history

This is similar to .bash_history file that is logged for each user.
You can direct the history to another log file by using:

$ export MYSQL_HISTFILE=/home/webtest/dblog

Create new user in MySql

mysql> CREATE USER ‘monty’@’localhost’ IDENTIFIED BY ‘some_pass’;
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘monty’@’localhost’
->     WITH GRANT OPTION;
mysql> CREATE USER ‘monty’@’%’ IDENTIFIED BY ‘some_pass’;
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘monty’@’%’
->     WITH GRANT OPTION;
mysql> CREATE USER ‘admin’@’localhost’;
mysql> GRANT RELOAD,PROCESS ON *.* TO ‘admin’@’localhost’;
mysql> CREATE USER ‘dummy’@’localhost’;