Some of The Most Important SQL Commands

SQL Commands

Some of The Most Important SQL Commands

SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index

CREATE Database To create a database, we use the CREATE DATABASE statement as shown in the following syntax:

CREATE DATABASE databasename;

USE DATABASENAME;

SHOW TABLES;

CREATE Table After creating database StudentAttendance, we need to define relations (create tables) in this database and specify attributes for each relation along with data types for each attribute. This is done using the CREATE TABLE statement.

Syntax: CREATE TABLE tablename( attributename1 datatype constraint, attributename2 datatype constraint, : attributenameN datatype constraint);

mysql> CREATE TABLE STUDENT( RollNumber INT, SName VARCHAR(20), SDateofBirth DATE, GUID CHAR(12), PRIMARY KEY (RollNumber));

DESCRIBE Table We can view the structure of an already created table using the describe statement.

Syntax: DESCRIBE tablename;

mysql> DESC STUDENT;

ALTER Table After creating a table we may realize that we need to add/remove an attribute or to modify the datatype of an existing attribute or to add constraint in attribute. In all such cases, we need to change or alter the structure of the table by using the alter statement.

Syntax: ALTER TABLE tablename ADD/Modify/DROP attribute1, attribute2,..

Add primary key to a relation

mysql> ALTER TABLE STUDENT ADD PRIMARY KEY(AttendanceDate, RollNumber);

Add foreign key to a relation

ALTER TABLE table_name ADD FOREIGN KEY(attribute name) REFERENCES referenced_table_name (attribute name);

mysql> ALTER TABLE STUDENT ADD FOREIGN KEY(GUID) REFERENCES GUARDIAN(GUID);

Add constraint UNIQUE to an existing attribute;-

mysql> ALTER TABLE STUDENT ADD UNIQUE(GPhone);

Add an attribute to an existing table

mysql> ALTER TABLE STUDENT ADD income INT;

Modify datatype of an attribute

mysql> ALTER TABLE STUDENT MODIFY SName VARCHAR(20) NOT NULL;