Getting started with JDBC

Structured Query Language (SQL) is a language that allows performing operations on database such as reading content, updating content, deleting and creating entries.

Note: To create or drop a database user should have administrator privilege on database server.

Create Database:

Creating a new database can be done using CREATE DATABASE statement. The syntax is:

SQL>CREATE DATABASE DATABASE_NAME;

Example

The below SQL statement create Database named User:

SQL>CREATE DATABASE User;

Drop Database:

Deleting an existing database can be done using DROP DATABASE statement. The syntax is:

SQL>DROP DATABASE DATABASE_NAME;

Create Table:

Creating a new table can be done using CREATE TABLE statement . The syntax is:

SQL> CREATE TABLE table_name(column_name1 column_data_type,column_name2 column_data_type,column_name3 column_data_type   ...);

Example

The following SQL statement creates a table named Users with four columns:

SQL> CREATE TABLE Users(user_id INT NOT NULL,user_age INT NOT NULL,user_firstVARCHAR(255),user_lastVARCHAR(255),   PRIMARY KEY ( user_id ));

Drop Table:                                                     

Deleting an existing table can be done using DROP TABLE statement. The syntax is:

SQL>DROP TABLE table_name;

Example

The following SQL statement deletes a table named Users:

SQL>DROP TABLE Users;

INSERT Data:

The syntax for INSERT looks similar to the following where column1, column2, and so on represent the new data to appear in the respective columns:

SQL> INSERT INTO table_name VALUES (column1, column2, ...);

Example

The following SQL INSERT statement inserts a new row in the Users database created earlier:

SQL> INSERT INTO Users VALUES (100, 18, 'Tom', 'Harry');

SELECT Data:

Retrieve data from a database can be done using The SELECT statement. The syntax for SELECT is:

SQL> SELECT column_name, column_name, ...     FROM table_name     WHERE conditions;

The WHERE clause can use the comparison operators such as =, !=, <, >, <=,and >=, as well as the BETWEEN and LIKE operators.

Example

The following SQL statement selects the age, first and last columns from the Users table where id column is 100:

SQL> SELECT user_age, user_first, user_last,      FROM Users      WHERE id = 20;

The following SQL statement selects the age, first and last columns from the Users table where first column contains Tom:

SQL> SELECT user_age,user_first, user_last,      FROM Users      WHERE first LIKE '%Tom%';

UPDATE Data:

Update data to the table is done using UPDATE statement. The syntax for UPDATE is:

SQL>UPDATE table_name     SET column_name = value, column_name = value, ...     WHERE conditions;

The WHERE clause can use the comparison operators such as =, !=, <, >, <=,and >=, as well as the BETWEEN and LIKE operators.

Example

Below SQLUPDATE statement changes the age column of User whose id is 20:

SQL>UPDATE Users SET user_age=25 WHERE user_id=20;

DELETE Data:

Delete data from tables using The DELETE statement. The syntax for DELETE is:

SQL>DELETE FROM table_name WHERE conditions;

The WHERE clause can use the comparison operators such as =, !=, <, >, <=,and >=, as well as the BETWEEN and LIKE operators.

Example

Below SQL DELETE statement delete the record of User whose id is 20:

SQL>DELETE FROM Users WHERE user_id=100;