CAUVERY GLOBAL SENIOR SECONDARY SCHOOL
MYSQL
SQL - Structured Query Language
Structured Query Language (SQL) is a specialized language for accessing and manipulating databases.
SQL commands are classified by function:
Data definition language (DDL) - used to define or change database structure(s)
(e.g., CREATE, ALTER, DROP)
Data manipulation language (DML) - used to select or change data (e.g., INSERT,
UPDATE, DELETE, SELECT)
Transaction Control language - used to control logical units of work (e.g., COMMIT, ROLLBACK)
CREATE DATABASE statement:
The CREATE DATABASE statement is used to create a new SQL database. Syntax:
CREATE DATABASE databasename;
CREATE DATABASE school;
SHOW DATABASES statement
The SHOW DATABASES statement is used to know the names of existing databases.
SHOW DATABASES;
USE statement
In order to use the database, the following SQL statement is required.
Syntax: USE databasename;
DROP DATABASE statement
The DROP DATABASE statement is used to delete a database from system.
Syntax:
DROP DATABASE databasename;
DROP DATABASE school;
CREATE TABLE statement
A database consists of many tables. In order to create a table in database
CREATE TABLE statement is used. Syntax:
CREATE TABLE table_name
(
column_name1 data_type (size) constraint,
column_name2 data_type (size) constraint,
column_name3 data_type (size) constraint,
....
);
CREATE TABLE student
(
rollnumber int primary key,
name char(25) NOL NULL,
fees int DEFAULT 7000,
dob date,
class char(3),
);
DESCRIBE statement
Provides a description of the specified table. Syntax:
DESCRIBE table_name;
DESCRIBE student;
INSERT INTO statement
Inserting a new row at the bottom of the table.
Syntax:INSERT INTO table_name VALUES (value1, value2, value3,...);
INSERT INTO table_name (column1, column2, column3,...) VALUES(value1,value2,value3,...);
INSERT INTO student VALUES(10, 'Alex', 7800, '1998-10-03','K12');
INSERT INTO student(rollnumber, name, fees, dob, class) values(11, 'Peter', 6700,
'1997-11-15',
ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing [Link] ALTER TABLE
statement is also used to add and drop various constraints on an existing table.
ALTER TABLE - ADD Column/Attribute To add a column in a table, use the following
syntax:
ALTER TABLE table_name ADD column_name datatype;
Example ALTER TABLE Customers ADD Email varchar(255);
ALTER TABLE - DROP COLUMN To delete a column in a table, use the following
syntax
ALTER TABLE table_name DROP COLUMN column_name;
Example ALTER TABLE Customers DROP COLUMN Email;
Adding a column with default value:
ALTER TABLE command can be used to add a new column to an existing table with default values.
Syntax for adding a column with a default value:
ALTER TABLE <table_name>
ADD ([column_name1]<datatype1>default data);
For example, ALTER TABLE student ADD (City char(6) DEFAULT “DELHI”);
Modifying an existing column definition:
The MODIFY clause can be used with ALTER TABLE command to change the datatype, size, constraint related to any
column of the table.
Syntax for modifying existing column datatype:
ALTER TABLE <table_name> MODIFY([column_name1] <datatype1>);
For example,
ALTER TABLE student MODIFY (Name varchar(25));
The above command will modify the datatype size for the Name field from 20 to 25 characters.
Renaming a column:
The existing column in a relation can be renamed using ALTER TABLE command.
Syntax for renaming an existing column:
ALTER TABLE <table_name> CHANGE [COLUMN] <old-column-name> <new-column-name> column_definition;
For example,
ALTER TABLE student
CHANGE City State varchar(10);
The above command shall rename the City column to State.
SQL PRIMARY KEY on ALTER TABLE
To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the following SQL:
ALTER TABLE table_name ADD PRIMARY KEY (Column_name);
Example ALTER TABLE Persons ADD PRIMARY KEY (ID);
DROP a PRIMARY KEY Constraint
To drop a PRIMARY KEY constraint, use the following SQL:
ALTER TABLE table_name DROP PRIMARY KEY;
Example : ALTER TABLE Persons DROP PRIMARY KEY;
The UPDATE command :
The UPDATE command is used to change some values in existing rows. The UPDATE
command specifies the rows to be changed using the WHERE clause, and new data using the SET keyword.
Syntax :
UPDATE <table-name>
SET <column-name=value>
WHERE <column-name=value>;
UPDATE student SET Marks = 90 WHERE Rollno = 8;
UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1;
UPDATE student SET Marks = Marks + 10 WHERE (Rollno = 5 or Rollno =10);
UPDATE student SET Marks = NULL WHERE Rollno = 9;
The DELETE COMMAND:Removing Data from a Table
The DELETE statement is used to delete rows from a table.
Syntax for DELETE Statement:
DELETE FROM <table_name> WHERE <condition>;
here <table_name> is the table whose records are to be deleted.
DELETE FROM student WHERE Rollno = 10;
To delete all the rows from the student table, the DELETE statement will be:
DELETE FROM student;
