Basic Mysql commands

Posted under » MySQL on 4 March 2014

Start mysql connection

$ mysql -u root --host=127.0.0.1 -p
mysql> show databases;
mysql> use taik;
mysql> show tables;
mysql> desc anjing; // describe or
mysql> show columns from anjing;
mysql> INSERT INTO `anjing` (`id`,`name`,`food`) VALUES (NULL, "John", "Casserole");
mysql> DELETE from anjing  where name='Sandy';

There may be times when you want to update or insert more than 1 row.

mysql> INSERT INTO `anjing` (`id`,`name`,`food`) VALUES (NULL, "John", "Casserole"), (NULL, "Sandy", "Key Lime Tarts");

Please note the trailing semicolon. Without it, the command is not complete. Values can be NULL when the field is AUTO_INCREMENT.

mysql> SELECT * FROM anjing;
+----+-------+----------------+
| id | name  | food           |
+----+-------+----------------+
|  1 | John  | Casserole      |
|  2 | Sandy | Key Lime Tarts |
|  3 | Tom   | BBQ            |
|  4 | Tina  | Salad          |
+----+-------+----------------+
4 rows in set (0.00 sec)

To show only the field names

SELECT column_name FROM information_schema.columns WHERE 
table_name = 'actuals' AND table_schema = 'shimano';

+-------------------+
| column_name       |
+-------------------+
| id                |
| teacher_id        |
| training_type     |
| actual_name       |
| duration_time     |
| max_attempt       |
| start_datetime    |
| end_datetime      |
| questions_per_set |
| unlimited         |
| grading_options   |
| created           |
| modified          |
+-------------------+

You may save this to a text file.

It is important to put table_schema as above because there could be other similar tables and thus duplicates will occur. You may also be interested in List table columns article.

Now update

UPDATE `anjing` 
SET `food` = 'Baong' 
WHERE `anjing`.`name` ='Sandy';

Show grants

mysql> SHOW GRANTS FOR 'user'@'localhost'; 

Drop and copy

DROP database taik;
CREATE DATABASE `tae`;

After that you mysqldump and restore to your new database.

Sometimes you need to drop tables instead of databases so you have to 'alter' it.

ALTER TABLE `student_answer` DROP `time_sent`, DROP `api_call_time`;