SQL CREATE TABLE
SQL
CREATE TABLE statement is used to create table in a database.
If you
want to create a table, you should name the table and define its column and
each column's data type.
Let's
see the simple syntax to create the table.
1.
create table "tablename"
2.
("column1" "data type",
3.
"column2" "data type",
4.
"column3" "data type",
5.
...
6.
"columnN" "data type");
The
data type of the columns may vary from one database to another. For example,
NUMBER is supported in Oracle database for integer value whereas INT is supported
in MySQL.
SQL CREATE TABLE
Example in MySQL
1.
CREATE TABLE Employee
2.
(
3.
EmployeeID int,
4.
FirstName varchar(255),
5.
LastName varchar(255),
6.
Email varchar(255),
7.
AddressLine varchar(255),
8.
City varchar(255)
9.
);
SQL DROP TABLE
A SQL
DROP TABLE statement is used to delete a table definition and all data from a
table.
Let's see the syntax to
drop the table from the database.
DROP TABLE "table_name";
SQL DELETE TABLE
The
DELETE statement is used to delete rows from a table. If you want to remove a
specific row from a table you should use WHERE condition.
DELETE FROM table_name [WHERE condition];
But if you do not specify
the WHERE condition it will remove all the rows from the table.
DELETE FROM table_name;
Difference between
DELETE and TRUNCATE statements
There
is a slight difference b/w delete and truncate statement. The DELETE statement only deletes the rows from the table based
on the condition defined by WHERE clause or delete all the rows from the table
when condition is not specified.
But it
does not free the space containing by the table.
The TRUNCATE statement: it is used to delete all the rows from the
table and
free the containing space.
Execute the following query
to truncate the table:
TRUNCATE TABLE employee;
Difference b/w DROP
and TRUNCATE statements
When you use the drop
statement it deletes the table's row together with the table's definition so
all the relationships of that table with other tables will no longer be valid.
When you
drop a table:
- Table structure will be dropped
- Relationship will be dropped
- Integrity constraints will be
dropped
- Access privileges will also be
dropped
On the other hand when we TRUNCATE a table,
the table structure remains the same, so you will not face any of the above
problems.
SQL RENAME TABLE
SQL
RENAME TABLE syntax
is used to change the name of a table. Sometimes, we choose non-meaningful name
for the table. So it is required to be changed.
Let's
see the syntax to rename a table from the database.
1.
ALTER TABLE table_name
2.
RENAME TO new_table_name;
SQL TRUNCATE TABLE
A
truncate SQL statement is used to remove all rows (complete data) from a table.
It is similar to the DELETE statement with no WHERE clause.
TRUNCATE TABLE Vs DELETE TABLE
Truncate
table is faster and uses lesser resources than DELETE TABLE command.
TRUNCATE TABLE Vs DROP TABLE
Drop
table command can also be used to delete complete table but it deletes table
structure too. TRUNCATE TABLE doesn't delete the structure of the table.
Let's
see the syntax to truncate the table from the database.
1.
TRUNCATE TABLE table_name;
For
example, you can write following command to truncate the data of employee table
1.
TRUNCATE TABLE Employee;
Note: The rollback process is not possible after
truncate table statement. Once you truncate a table you cannot use a flashback
table statement to retrieve the content of the table.
SQL COPY TABLE
If you
want to copy a SQL table into another table in the same SQL server database, it
is possible by using the select statement.
The
syntax of copying table from one to another is given below:
1.
SELECT * INTO <destination_table> FROM <source_table>
For
example, you can write following command to copy the records of hr_employee
table into employee table.
1.
SELECT * INTO admin_employee FROM hr_employee;
Note: SELECT INTO is
totally different from INSERT INTO statement.
SQL TEMP TABLE
The
concept of temporary table is introduced by SQL server. It helps developers in
many ways:
Temporary
tables can be
created at run-time and can do all kinds of operations that a normal table can
do. These temporary tables are created inside tempdb database.
There
are two types of temp tables based on the behavior and scope.
- Local Temp Variable
- Global Temp Variable
Local Temp Variable
Local
temp tables are only available at current connection time. It is automatically
deleted when user disconnects from instances. It is started with hash (#) sign.
1.
CREATE TABLE #local temp table (
2.
User id int,
3.
Username varchar (50),
4.
User address varchar (150)
5.
)
Global Temp Variable
Global
temp tables name starts with double hash (##). Once this table is created, it
is like a permanent table. It is always ready for all users and not deleted
until the total connection is withdrawn.
1.
CREATE TABLE ##new global temp table (
2.
User id int,
3.
User name varchar (50),
4.
User address varchar (150)
5.
)
SQL ALTER TABLE
The
ALTER TABLE statement is used to add, modify or delete columns in an existing
table. It is also used to rename a table.
You can
also use SQL ALTER TABLE command to add and drop various constraints on an
existing table.
SQL ALTER TABLE Add Column
If you
want to add columns in SQL table, the SQL alter table syntax is given below:
1.
ALTER TABLE table_name ADD column_name column-definition;
If you
want to add multiple columns in table, the SQL table will be
1.
ALTER TABLE table_name
2.
ADD (column_1 column-definition,
3.
column_2 column-definition,
4.
.....
5.
column_n column-definition);
SQL ALTER TABLE Modify Column
If you
want to modify an existing column in SQL table, syntax is given below:
1.
ALTER TABLE table_name MODIFY column_name column_type;
If you
want to modify multiple columns in table, the SQL table will be
1.
ALTER TABLE table_name
2.
MODIFY (column_1 column_type,
3.
column_2 column_type,
4.
.....
5.
column_n column_type);
SQL ALTER TABLE DROP Column
The
syntax of alter table drop column is given below:
1.
ALTER TABLE table_name DROP COLUMN column_name;
SQL ALTER TABLE RENAME Column
The
syntax of alter table rename column is given below:
1.
ALTER TABLE table_name
2.
RENAME COLUMN old_name to new_name
No comments:
Post a Comment