MySQL useful command

How to use MySQL command : Basic understanding

MySQL statements

• MySQL key words are not a case sensitive
• Optional items are enclosed in square brackets [ ]
• Don’t quote database, table, or column names
• Don’t quote column types or modifiers
• Don’t quote numerical values
• Quote (single or double) non-numeric values
• Quote file names and passwords

MySQL command

Login in to MySql database

[root@master1 ~]# mysql -u root -p

Create a database on the mysql server

Syntax,

create database [databasename];

Example,

mysql> create database kvit;

Query OK, 1 row affected (0.00 sec)

mysql>  create database openpath;

Query OK, 1 row affected (0.00 sec)

Show available  databases on mysql server

mysql> show databases;
+————————+
|   Database                 |
+————————-+
|   information_schema|
|   kvit                          |
|   mysql                      |
|   openpath                |
+————————-+
4 rows in set (0.00 sec)

Switch to a database

Syntax,

use [db name];

mysql> use kvit;
Database changed

Show the present use/selected  database

mysql> select database() from dual;
+————+
| database() |
+————-+
|   kvit          |
+————-+
1 row in set (0.00 sec)

or,

mysql> SELECT DATABASE();
+————+
| DATABASE() |
+—————+
|   kvit             |
+—————+
1 row in set (0.00 sec)

Show  tables inside database

mysql> show tables;

Empty set (0.00 sec)

Create table inside database

Syntax,

CREATE TABLE table_name

( column_name1 data_type(size),

column_name2 data_type(size),

……

);

mysql> create table class (roll_no int, name varchar(20));

Query OK, 0 rows affected (0.05 sec)

create one more table

mysql> create table book (name varchar(20), price int);
Query OK, 0 rows affected (0.03 sec)

Show  the details of  table field formats

Syntax,

describe [table name];

mysql> describe class;
+———+————–+——–+——+———+——–+
| Field     |       Type     |   Null  |  Key | Default | Extra  |
+———+————–+——–+——+———+——–+
| roll_no  |  int(11)        |  YES   |         | NULL   |             |
| name    |  varchar(20) |  YES   |         | NULL   |             |
+———+—————+——-+——+———+———+
2 rows in set (0.00 sec)

Show  tables inside database

mysql> show tables;
+—————–+
| Tables_in_kvit  |
+—————– +
|    book              |
|    class              |
+——————+
2 rows in set (0.00 sec)

Insert data in table

Syntax,

insert into table_name
values (value1,value2,…);

mysql> insert into class values(01,”Gopal”);
Query OK, 1 row affected (0.00 sec)

mysql> insert into class values(02,”Gopal”);
Query OK, 1 row affected (0.00 sec)

Show data of table 

Syntax,

select * from [table name];

mysql> select * from class;
+———+——-+
| roll_no | name |
+———+——-+
| 1 | Gopal |
+———+——-+
1 row in set (0.00 sec)

Show certain row value from table

Syntax,

SELECT * FROM [table name] WHERE [field name] = “whatever”;

mysql> select * from class where name = ‘gopal';
+———+——-+
| roll_no | name |
+———+——-+
| 1 | Gopal |
+———+——-+
1 row in set (0.00 sec)

Show/filter certain selected row value from table

mysql> select roll_no from class where name=’gopal';
+———+
| roll_no |
+———+
| 1 |
+———+
1 row in set (0.00 sec)

Select all record containing name(column) with “anykeyword”

Syntax,

SELECT * FROM [table name] WHERE column1 = “anykeyword” AND column2 = ‘anykeyword';

mysql> select * from class where name = “Gopal” and roll_no = ‘2’;
+———+——-+
| roll_no | name |
+———+——-+
| 2 | Gopal |
+———+——-+
1 row in set (0.00 sec)

Show all records not containing the name “Gopal” and the roll_no “2” order by the roll_no field.

mysql>SELECT * FROM class WHERE name != “Gopal” AND roll_no = ‘2’ order by roll_no;

Show all records starting with the letters ‘Gopal’ 

mysql> SELECT * FROM class where name like “Gopal%”;

+———+——–+
| roll_no | name  |
+———+——–+
| 1 | Gopal          |
| 2 | Gopal          |
+———+——–+
2 rows in set (0.00 sec)

Show all records starting with the letters ‘Gopal’ AND the roll_no ‘2’

mysql> SELECT * FROM class where name like “Gopal%” AND roll_no =”2″;
+———+——–+
| roll_no | name |
+———+——-+
| 2 | Gopal           |
+———+——–+
1 rows in set (0.00 sec)

Show all records starting with the letters “Gopal” AND the roll_no  ‘2’ limit to records 1 through 3

Syntax,

SELECT * FROM [table name] WHERE name like “Gopal%” AND phone_number = ‘2’ limit 1,3;

mysql> SELECT * FROM class WHERE name like “Gopal%” AND roll_no = ‘2’ limit 1,3;
+———+——-+
| roll_no | name |
+———+——-+
| 2 | Gopal         |
+———+——-+
1 row in set (0.00 sec)

Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with “G”

mysql> SELECT * FROM [table name] WHERE name RLIKE “^g”;

mysql> SELECT * FROM class WHERE name RLIKE “^G”;
+———+——–+
| roll_no | name |
+———+——–+
| 1 | Gopal |
| 2 | Gopal |
+———+——–+
2 rows in set (0.00 sec)

Show unique records

Syntax,

SELECT DISTINCT [column name] FROM [table name];

Note: insert a new value with name “gopal1″

mysql> SELECT DISTINCT name FROM class;
+——–+
| name |
+——–+
| Gopal |
| Gopal1 |
+——–+
2 rows in set (0.00 sec)

Show selected records sorted in an ascending (asc) or descending (desc)

Syntax,

SELECT [col1] FROM [table name] ORDER BY [col2] DESC;

Or, you can try if you have more column

SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

mysql> SELECT roll_no FROM class ORDER BY name DESC;

Return number of rows

Syntax,

SELECT COUNT(*) FROM [table name];

mysql> SELECT COUNT(*) FROM class;
+———-+
| COUNT(*) |
+———-+
| 3 |
+———-+
1 row in set (0.00 sec)

Delete MySQL database

Syntax,

drop database [database name];

mysql> drop database openpath;
Query OK, 0 rows affected (0.03 sec)

Delete a table from MySQL Database

Syntax,

drop table [table name];

mysql> drop table book;
Query OK, 0 rows affected (0.00 sec)

Delete Statement in MySQL

The Delete statement is used to delete records in a table.

Delete a row in table

Syntax,
delete from table_name
where some_column=some_value;

mysql> delete from class where name=”Gopal1″;
Query OK, 1 row affected (0.00 sec)

Delete all record from table

Syntax,

delete from table_name;

mysql> delete from book;
Query OK, 0 rows affected (0.00 sec)

Or,

mysql> delete * from book;