MYSQL BASICS

DDL Statements/Commands

Some DDL statements are as follows

CREATE Statement

The CREATE statement is used to create a table in a database. In this command, we need to give information about table like number of columns, rows and its types and constraints.

Syntax:

CREATE TABLE tablename

( column_name datatype (size)  [constraints],

column_name datatype (size)  [constraints],

column_name datatype (size)  [constraints], …. ) ;

The data type specifies what type of data, the column can hold and the size or constraint is optional.

e.g. If we want to create a table PERSONS that contains five columns: P_Id, FirstName, LastName, Address and City.

We use the following

CREATE statement:

CREATE TABLE PERSONS (

P_Id INT Primary Key,

FirstName VARCHAR(25)NOT NULL,

LastName VARCHAR(25),

Address VARCHAR(30),

City VARCHAR(25) );

Constraints in SQL

Constraints are the conditions that the table must satisfy. These can be enforced on the attributes of a relation. These can be specified at the time of creating table. They are used to ensure integrity of a relation, hence named as integrity constraints

Some types of constraints are:

• NOT NULL Constraint It ensures that a column cannot  store NULL value.
• UNIQUE Constraint It is used to uniquely identify each record in a database.
• PRIMARY KEY Constraint It ensures that a column have an unique identity, which helps to find a particular record in a table and no column that is part of the primary key constraint can contain a NULL value.
• FOREIGN KEY Constraint It designates a column or combination of columns as a foreign key and establishes its relationship with a primary key in different tables.
• CHECK KEY Constraint It is used to define condition, which column in each row must satisfy.
• DEFAULT Constraint It inserts default value into a column.

Show Statement

Getting listings of database and tables

mysql>SHOW TABLES;

mysql>SHOW DATABASES;

DESCRIBE Statement

DESCRIBE or DESC command is used to verify the structure of a table that you have created.

Syntax DESCRIBE ; or DESC ;

For example, DESC SCHOOL;

DROP Statement

The DROP statement is used to remove the table definition and all data, constraints and permission specified for that table.

Syntax DROP TABLE table_name;
e.g. DROP TABLE EMPLOYEE;

ALTER Statement

The ALTER statement is used to add, delete or modify columns and constraints in the existing table.

To ADD a Column

Syntax
ALTER TABLE table_name ADD column_name data_type;
e.g. ALTER TABLE STUDENT ADD Section CHAR;
The above query will add column Section to STUDENT table, whose data type is character.

To DROP Column

Syntax ALTER TABLE table_name DROP COLUMN column_name;
e.g. ALTER TABLE STUDENT DROP COLUMN location; 
The above query will delete a column location from STUDENT table.

To MODIFY Column Data Type

Syntax ALTER TABLE table_name MODIFY column_name data_type;
e.g. ALTER TABLE STUDENT MODIFY Fee NUMBER(15,2); The above query will modify the data type of Fee column, of STUDENT table.

To DELETE a Constraint

Syntax
A L T E R T A B L E t a b l e _ n a m e D R O P
C o n s t r a i n t _ N a m e ;
e.g. ALTER TABLE STUDENT DROP Primary Key;
The above query will delete the primary key constraint from STUDENT table.

RENAME Statement

The RENAME statement is used to rename a table.
Syntax
RENAME old_table_name TO new_table_name;
e.g. RENAME STUDENT TO CANDIDATE;
The above query will rename STUDENT table to CANDIDATE