JDBC SQL Syntax

Structured Query LLanguage (SQL) is a standardized language that allows you to perform operations on a database, such as creating items, reading content, updating content, and deleting entries.

Almost all databases that can be used support SQL, and it allows you to write database code independently of the underlying database.

This chapter outlines SQL, which is a prerequisite for understanding the concept of JDBC. After completing this chapter, you will be able to create,Ccreating,Rfilling, updatingU, updating andDDeleting data (usually referred to asCRUDoperations).

For more details on SQL, please read ourMySQL Tutorial.

Establish database

The CREATE DATABASE statement is used to create a new database. The syntax is-

SQL> CREATE DATABASE DATABASE_NAME;

Online Examples

The following SQL statement creates a database named EMP-

SQL> CREATE DATABASE EMP;

Delete database

The DROP DATABASE statement is used to delete an existing database. The syntax is-

SQL> DROP DATABASE DATABASE_NAME;

Note:To create or delete a database, you should have administrative privileges on the database server. Note that deleting a database will result in the loss of all data stored in that database.

Create table

The CREATE TABLE statement is used to create a new table. The syntax is-

SQL> CREATE TABLE table_name
(
　　　column_name column_data_type,
　　　column_name column_data_type,
　　　column_name column_data_type
　　　...
);

Online Examples

The following SQL statement creates a table named Employees with four columns

SQL> CREATE TABLE Employees
(
　　　id INT NOT NULL,
　　　age INT NOT NULL,
　　　first VARCHAR(255),
　　　last VARCHAR(255),
　　　PRIMARY KEY ( id )
);

Delete table

The DROP TABLE statement is used to delete an existing table. The syntax is-

SQL> DROP TABLE table_name;

Online Examples

The following SQL statement deletes the table named Employees-

SQL> DROP TABLE Employees;

Insert data

The syntax of INSERT is similar to the following, where column1, column2representing the new data to be displayed in the corresponding columns-

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

Online Examples

The following SQL INSERT statement inserts a new row into the previously created Employees database-

SQL> INSERT INTO Employees VALUES (100,　18, 'Zara', 'Ali');

Query data

SELECT statement is used to retrieve data from a database. The syntax of SELECT is-

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

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

Online Examples

The following SQL statement selects the age, first column, and last column from the Employees table, where the id column is100-

SQL> SELECT first, last, age　
　　　　　FROM Employees　
　　　　　WHERE id =　100;

The following SQL statement selects the era from the first and last columns of the employee tablefirstcolumns containingZara-

SQL> SELECT first, last, age　
　　　　　FROM Employees　
　　　　　WHERE first LIKE '%Zara%';

Update Data

UPDATE statement is used to update data. The syntax of UPDATE is-

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

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

Online Examples

The following SQL UPDATE statement changes data with ID10age column of employee 0-

SQL> UPDATE Employees SET age=20 WHERE id=100;

Delete Data

DELETE statement is used to delete data from a table. The syntax of DELETE is-

SQL> DELETE FROM table_name WHERE conditions;

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

Online Examples

The following SQL DELETE statement deletes data with ID10records of employee 0-

SQL> DELETE FROM Employees WHERE id=100;