SQL
Create database
<dbname>;
Create database learnersnote;
Opening the database
• Use <databasename>;
• Use learnernote;
The CREATE TABLE statement is used to create a new table in a database.
CREATE TABLE table_name (
column1 datatype [(size)]
[constraint],
column2 datatype [(size)]
[constraint],
column3 datatype [(size)]
[constraint],
Constraint
MySQL Data Types
In MySQL there are three main data types:
string, numeric, and date and time.
Numeric Data Type
1. int – used for number without decimal.
2. Decimal(m,d) /Dec(m,d) – used for floating/real numbers. m denotes the total length of
number and d is number of decimal digits.
Date and Time Data Type
1. date – used to store date in YYYY-MM-DD format.
2. time – used to store time in HH:MM:SS format.
String Data Types
1. char(m) – used to store a fixed length string. m denotes max. number of characters.
2. varchar(m) – used to store a variable length string. m denotes max. no. of characters
Structure of the table
Desc <tablename>;
INSERTING DATA INTO TABLE –
The rows are added to relations(table) using INSERT
command of SQL.
Syntax of INSERT is :
INSERT INTO <tablename>
VALUES ( <value1> , <value2> , …..) ;
INSERT INTO <tablename>(<column1><column2>,
….)
VALUES ( <value1> , <value2> , …..) ;
ALTER TABLE - ADD Column
To add a column in a table, use the following
syntax:
ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE - DROP COLUMN
To delete a column in a table, use the following
syntax (notice that some database systems
don't allow deleting a column):
ALTER TABLE table_name
DROP column_name;
ALTER TABLE - MODIFY DATATYPE
To change the data type of a column in a
table, use the following syntax:
ALTER TABLE table_name
MODIFY column_name datatype;
ALTER TABLE - RENAME COLUMN
ALTER TABLE table_name
CHANGE COLUMN oldcolumn_name newcolumn_name
datatype;
