SQL Day 2

Day 2: Datatypes and
commands in Mysql
1
Contents
Data Types in MySQL....................................................................................................................4
Numeric data types in SQL..................................................................................................................................................4
Date and Time Data Types.................................................................................................................................................. 5
Character Strings Data Types............................................................................................................................................ 5
Commands in SQL.........................................................................................................................6
1. Data Definition Language (DDL):................................................................................................................................6
Creating a Database:......................................................................................................................................................... 6
Dropping a database:....................................................................................................................................................... 6
Creating a table in SQL:................................................................................................................................................... 6
Dropping a table in SQL:................................................................................................................................................. 7
SQL commands for altering a table:...........................................................................................................................7
Command to change the datatype of the column...................................................................................................7
Command for adding a new column to an existing table.................................................................................7
Command for renaming a column name.................................................................................................................8
Inserting values into the created table.....................................................................................................................8
Updating the records inside the table.......................................................................................................................8
Deleting a record inside the table:..............................................................................................................................9
2
3
Data Types in MySQL
Numeric data types in SQL
Data Type From To

bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807
int -2,147,483,648 2,147,483,647
smallint -32,768 32,767
tinyint 0 255
Bit 0 1
decimal -10^38 +1 10^38 -1
money -922,337,203,685,477.5808 922,337,203,685,477.5807
float -1.79E + 308 1.79E + 308
Date and Time Data Types
Data Type From To
4
datetime Jan 1, 1753 with time Dec 31, 9999 with time
smalldatetime Jan 1, 1900 Jun 6, 2079
Date Jan 1, 1753 Dec 31, 9999
Character Strings Data Types

 Char : Maximum length of 8,000 characters.( Fixed length non-Unicode
characters)
 varchar: Maximum of 8,000 characters. (Variable-length non-Unicode data).
 varchar(max) : Maximum length of 2E + 31 characters, Variable-length non-
Unicode data (SQL Server 2005 only).
 Text : Variable-length non-Unicode data with a maximum length of 2,147,483,647
characters
Commands in SQL
 SQL commands are instructions. It is used to communicate with the database. It
is also used to perform specific tasks, functions, and queries of data.
 SQL can perform various tasks like create a table, add data to tables, drop the
table, modify the table, set permission for users.
5
1. Data Definition Language (DDL):
● DDL changes the structure of the table like creating a table, deleting a table, altering a
table, etc.
● All the commands of DDL are auto-committed, which means it permanently saves all
the changes in the database.
Creating a Database:
The below syntax will help you to create a database named Innomatics.
Dropping a database:
The syntax below will let you drop a database.
Creating a table in SQL:

The below command will help you to create a table
Example:
Creating an employee table:
6
Dropping a table in SQL:
SQL commands for altering a table:
Command to change the datatype of the column.
Example:
Command for adding a new column to an existing table

Adding a new column for email ID.
Command for renaming a column name

Renaming lastname to ‘lname’.
7
Inserting values into the created table
Syntax 1:
If in case, you are using the below syntax you must insert the values in the same order
as the columns are created.
Example:
Syntax 2:
we can insert the values into emp_t table:
Updating the records inside the table

Let’s look at the data in the employee table.
Syntax for updating a record.
8
Let’s update the salary for employee number 122 to 85000.
We can see that the salary for employeeNumber 122 as been changed to 85000.
Deleting a record inside the table:

Using delete command you can delete a specific record from a table.
Let’s delete a record for employeeNumber 122
We can now see that record for employeeNumber has been deleted

SQL Day 2

Uploaded by

Copyright:

Available Formats

SQL Day 2

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL Day 2

Uploaded by

Copyright:

Available Formats

Day 2: Datatypes and

Numeric data types in SQL

Data Type From To

int -2,147,483,648 2,147,483,647

smallint -32,768 32,767

decimal -10^38 +1 10^38 -1

money -922,337,203,685,477.5808 922,337,203,685,477.5807

float -1.79E + 308 1.79E + 308

Date and Time Data Types

Data Type From To

smalldatetime Jan 1, 1900 Jun 6, 2079

Date Jan 1, 1753 Dec 31, 9999

Character Strings Data Types

Creating a table in SQL:

SQL commands for altering a table:

Command to change the datatype of the column.

Command for adding a new column to an existing table

Command for renaming a column name

we can insert the values into emp_t table:

Updating the records inside the table

Syntax for updating a record.

Deleting a record inside the table:

Let’s delete a record for employeeNumber 122

You might also like