Basic mysql command and syntax for database administration

Short tutorial about how to deal with MYSQL databases.

• Creating Database

mysql> create database dexterserver;
Query OK, 1 row affected (0.00 sec)

dexterserver is the name of the database.

• Show list of all the databases.

show databases; 

• Accessing or using a particular database.

mysql> use dexterserver;
Database changed

• Creating table.

mysql>  CREATE TABLE dexter ( id Int(3), first_name Varchar (15), email Varchar(30), country Varchar(30));

Here table has 4 fields id (numeric field) , first_name (character type field) , last_name (character type field), email (character type field),  country (character type field).

• Listing all the tables in the database.

show tables;

• You can view the columns you have created in the table “dexter” as:

show columns from dexter;

• We will add a column say ‘last_name‘ after column ‘first_name‘.

mysql>  ALTER TABLE dexter ADD last_name varchar (20) AFTER first_name;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

• Inserting a row in the table.

mysql> INSERT INTO dexter VALUES ('1' , 'Ronak' , 'Yadav' , 'ronakyadav@domain.com','India' );

• listing all the entries in a table we use * for all.

select * from dexter;

• Deleting a row using the id field here id field is numeric.

DELETE FROM dexter WHERE id = 5;

• deleting a row using the field which has character value, for character we have to use '  '.

mysql> delete from dexter where first_name = 'ronak' ;

Query OK, 1 row affected (0.02 sec)

• Updating a field in the table

UPDATE dexter SET id = 5 WHERE first_name = 'Rohit';

• Deleting a column from the table.

mysql> alter table dexter drop country;

• Deleting an existing table.
mysql> drop table dexter;
Query OK, 0 rows affected (0.04 sec)


• Renaming an existing table.

mysql> rename table dexter TO dexterservers;
Query OK, 0 rows affected (0.03 sec)

• Creating primary key with auto increment, In auto increment it automatically adds 1 to the last value is nothing is provided.

mysql> CREATE TABLE dexterserver ( id Int(3) NOT NULL AUTO_INCREMENT PRIMARY KEY , name Varchar (15));

For inserting data we have to mention the column name in insert command.

INSERT INTO dexterserver (name) VALUES ('Ronak');