MySQL

MySQL is a Case-insensitive language i.e commands can written in either lower or uppar letter case and query search also be independent of its case.

The basic commands can be categorise, they are

General Commands

1. USE : we need to select to some database when we first start or connect to MySQL.

Syntax

USE database_name;

2. SHOW: Listing all databases on the system.

Syntax

SHOW DATABASES;

3. SHOW TABLES: Listing of all tables from the specified database given in the command.

Syntax

SHOW TABLES FROM databasename;

4. DESCRIBE or DESC: This commands gives the description of all the columns from the given table name along with its type and other information.

Syntax

DESCRIBE (or DESC) table_name;

SHOW FIELDS or COLUMNS FROM table_name gives the exact result as DESC command gives.

Table Commands

1. CREATE: This command is used to create a new table or database.

Syntax for creating database

CREATE DATABASE database_name;

Syntax for creating table

CREATE TABLE table_name (create_clause1, create_clause2, ...);

Example

CREATE TABLE studadr (said int(5) not null auto_increment
 Primary key, aid int not null references studinfo(sid),
 blck varchar(5) not null,hno int(5) not null, 
 street varchar(20) not null, locatn varchar(20) not null,
state varchar(10) not null, country varchar(20) not null default'India');

In the above create command certain clause we have use whose description is given below:

1. AUTO_INCREMENT : It is used to increase its value into which is assigned along with NOT NULL value.
2. PRIMARY KEY : It is a uniquily identified key in every table, which helps in retrieving date. This column should also be NOT NULL.
3. NOT NULL : No NULL values are allowed in this column.It generates an error message as the data is inserted into the table.
4. DEFAULT value : If a NULL value is used in the data for this column, the default value is entered instead.
2. DROP: Remove the table from the database we are in permanently. It can also remove full database, So be careful while using this command

Syntax

DROP TABLE table_name;
or
DROP DATABASE database_name;

3. ALTER: The type of alter define and perform it in this command.

Syntax

//Adds the define columns to the table.
ALTER TABLE table_name ADD (create_clause1, create_clause2, ...);

//Drops the define columns from the table. 
ALTER TABLE table_name DROP column_name;

//Modifiers define to a column 
ALTER TABLE table_name MODIFY create_clause;

1. INSERT: This command is use to insert a complete row of data for every column in the order in which the table is define.

Syntax

INSERT INTO table_name VALUES (value1, value2, ...);

Example

//inserting all values in table define order
INSERT INTO studadr VALUES(409,103,'d',52,
'centralmarket','south','odisa','India');

//inserting values in another way
INSERT INTO studadr(said,aid,blck,hno,street,locatn,state, country)
 values(410,106,'i',45,'gmd','audin','Sydney','autralia');

2. DELETE: Delete the entire row which meets the where clause.

DELETE FROM table_name WHERE where_clause;

Example

DELETE FROM studadr WHERE said=410;

Privilege Commands

Most of these commands require MySQL root access

1. Grant: It is used to provide access or privilages on the databases object to the users.

Example

GRANT privilegename
ON objectname
TO {username |PUBLIC |rolename}
[WITH GRANT OPTION];

2. Revoke: It is used to remove access or privilages to the database object.

Example

REVOKE privilegename
ON objectname
FROM {username |PUBLIC |rolename}