Before you deal with accessing and modifying databases, you first need to know the basics of SQL (Structured Query Language). This is the language used for querying databases. SQL is simple and easy to understand. SQL is not only used for querying data, it can pretty much do anything from creating, updating, and deleting databases and tables.
Creating Database and Tables
You can create a database using the following syntax. Please do note that SQL is case insensitive. So it doesn't matter if you write the keywords in lowercase.CREATE DATABASE <Name of Database>;
The <Name of
Database> is the name that you want to give to your database. Here’s an
example.
CREATE DATABASE MyDatabase;
For us to store a
Database, we need to create a table. We use the following syntax.
The <Name of
Database> is the name that you want to give to your database. Here’s an
example.
CREATE DATABASE MyDatabase;
For us to store a Database, we need to create a table. We use the following syntax.
CREATE TABLE <Name of Table>
(
<Column1>
<Datatype>,
<Column2>
<Datatype>,
.
.
.
<ColumnN>
<Datatype>
);
Data Type
|
Description
|
Example
|
int
|
integer data
|
-3, 1, 12, 57
|
char(n)
|
fixed-length string
where n is the maximum length
|
‘hello’, ‘goodbye’
|
varchar(n)
|
variable length string
where n is the maximum length
|
‘hello’, ‘goodbye’
|
DateTime
|
stores date and time
|
Jan 1, 2010 3:00PM
|
date
|
stores date only
|
Jan 1, 2010
|
time
|
stores time only
|
3:00PM
|
money
|
stores monetary data
|
123.45
|
You might notice that
char(n) and varchar(n) seems to be the same. char(n) is fixed and once you give a size to it, it will take up memory depending on that size. If the size you have
given is 100, and the string you store only has 3 characters, then char(n) will
still use memory for 100 characters. varchar(n) is different. It only takes up
what is needed. Also, note that character strings are enclosed inside single
quotes.
Let’s create an example of the table that can store the FirstName, LastName, and Age of employees.
CREATE TABLE Employees
(
EmployeeID int primary key,
FirstName varchar(100),
LastName varchar(100),
Age int
);
Notice that we added the primary keyword after the data type of EmployeeID. This will signify that EmployeeID will be the primary key of the Employees table. A primary key is a value that identifies a row or record. The primary key must be unique for each of the records. We used EmployeeID as the primary key and not, for example, the FirstName, because multiple employees could have the same name therefore, that will conflict the rule that primary keys need to unique. The FirstName and LastName fields have a data type of varchar(100), which means the length has a maximum of 100 characters.
You can use the
following SQL Command to Delete an existing table.
DROP TABLE Employees;
That will delete the
Employees table and all of its records.
Inserting Records
We use the following syntax to insert records into a table.
INSERT INTO <Table Name> VALUES (<Value1>, <Value2>, ... , <ValueN>);
The <TableName> is the name of the table where we want to insert our data into. Inside the parenthesis after VALUES, you list all the values for each of the fields of the table. The order of data is important. You must follow the order defined when you created the Table. Let’s add some records to our Employee Table.
INSERT INTO Employees VALUES (1, "John", "Smith", 21);
INSERT INTO Employees VALUES (2, "Mark", "Mayer", 23);
INSERT INTO Employees VALUES (3, "Alvin", "Minsky", 27);
This will insert the 3 employees to our Employees table. Note the order of the parameters. Our table definition defines that the first parameter is the EmployeeID, the second is FirstName, LastName and then the last is the Age. Again, the EmployeeID must be unique because that is the primary key
To delete a record, we used this syntax.
DELETE FROM <Table Name> WHERE <Condition>;
INSERT INTO <Table Name> VALUES (<Value1>, <Value2>, ... , <ValueN>);
The <TableName> is the name of the table where we want to insert our data into. Inside the parenthesis after VALUES, you list all the values for each of the fields of the table. The order of data is important. You must follow the order defined when you created the Table. Let’s add some records to our Employee Table.
INSERT INTO Employees VALUES (1, "John", "Smith", 21);
INSERT INTO Employees VALUES (2, "Mark", "Mayer", 23);
INSERT INTO Employees VALUES (3, "Alvin", "Minsky", 27);
This will insert the 3 employees to our Employees table. Note the order of the parameters. Our table definition defines that the first parameter is the EmployeeID, the second is FirstName, LastName and then the last is the Age. Again, the EmployeeID must be unique because that is the primary key
Deleting Records
DELETE FROM <Table Name> WHERE <Condition>;
The condition tells what record you want to delete. Most of the
time, you use the primary key field to delete a record. As an example, Let’s
delete John from our table.
DELETE FROM Employees WHERE EmployeeID = 1;
The above statement reads as “Delete a record from the Employees
table whos EmployeeID is equal to 1. Since John has an EmployeeID of 1, he is
deleted from the table.
Updating Records
If you want
to change the value of a field of an existing record, you can do that using the
following syntax.
UPDATE <Table Name> SET <Column> = <Value> WHERE <Condition>;
For example, if you want to change the FirstName of Mark, use the following code.
UPDATE Employees SET FirstName = 'Marco' WHERE EmployeedID = 2;
We set the FirstName field of the employee with EmployeeID that is equal to 2 to ‘Marco’.
UPDATE <Table Name> SET <Column> = <Value> WHERE <Condition>;
For example, if you want to change the FirstName of Mark, use the following code.
UPDATE Employees SET FirstName = 'Marco' WHERE EmployeedID = 2;
We set the FirstName field of the employee with EmployeeID that is equal to 2 to ‘Marco’.
Querying Records
We can use
the SELECT statements to query or get data from our database. The syntax is as
follows:
SELECT <Column1>, <Column2>, ... <ColumnN> FROM <TableName>; For example, if we want to query all the FirstNames of the employee, we can write this code:
SELECT FirstName FROM Employees; FirstName John Mark Alvin You can select multiple columns just like this.
SELECT FirstName, LastName FROM Employees;
FirstName LastName John
Smith Mark Mayer
Alvin Minsky
You can also use the WHERE keyword to only select those that meet the condition.
SELECT FirstName, LastName FROM Employees WHERE FirstName = 'John';
FirstName LastName
John Smith
To Select Everything from the table, you can use the * character.
SELECT * FROM Employees
EmployeeID FirstName LastName Age
1 John Smith 21
2 Mark Mayer 23
3 Alvin Minsky 27
You can arrange the result by using the ORDER BY keyword.
SELECT FirstName, LastName FROM Employees ORDER BY FirstName
FirstName LastName
Alvin Minsky
John Smith
Mark Mayer
If you want to arrange the records in descending order, you can use the DESC keyword.
SELECT FirstName, LastName FROM Employees ORDER BY FirstName DESC
FirstName LastName
Mark Mayer
John Smith
Alvin Minsky
SQL alone is a very big language. There are many more not discussed in this lesson. Now that you have learned the basics of SQL, we can now proceed to how we can access databases from our application.
SELECT <Column1>, <Column2>, ... <ColumnN> FROM <TableName>; For example, if we want to query all the FirstNames of the employee, we can write this code:
SELECT FirstName FROM Employees; FirstName John Mark Alvin You can select multiple columns just like this.
SELECT FirstName, LastName FROM Employees;
FirstName LastName John
Smith Mark Mayer
Alvin Minsky
You can also use the WHERE keyword to only select those that meet the condition.
SELECT FirstName, LastName FROM Employees WHERE FirstName = 'John';
FirstName LastName
John Smith
To Select Everything from the table, you can use the * character.
SELECT * FROM Employees
EmployeeID FirstName LastName Age
1 John Smith 21
2 Mark Mayer 23
3 Alvin Minsky 27
You can arrange the result by using the ORDER BY keyword.
SELECT FirstName, LastName FROM Employees ORDER BY FirstName
FirstName LastName
Alvin Minsky
John Smith
Mark Mayer
If you want to arrange the records in descending order, you can use the DESC keyword.
SELECT FirstName, LastName FROM Employees ORDER BY FirstName DESC
FirstName LastName
Mark Mayer
John Smith
Alvin Minsky
SQL alone is a very big language. There are many more not discussed in this lesson. Now that you have learned the basics of SQL, we can now proceed to how we can access databases from our application.
Read More
Share this article with your friends
Post A Comment:
0 comments: