DbSchema Database Designer

DbSchema Tutorial | SQL Syntax

Publish on DbSchema Blog >>>

SQL alt >

In this section, we will discuss the basic syntax of SQL and familiarize ourselves with the most common SQL commands. SQL commands can be divided into a few major categories: DDL (Data Definition Language), DML (Data Manipulation Language), DQL (Data Query Language), and DCL (Data Control Language).

SQL Syntax

A SQL query must start with a command, like SELECT, UPDATE, DELETE, followed by a specification of where that command should take effect. SQL is not case-sensitive. However, conventionally, commands are written in uppercase.

A simple SQL query could be:

SELECT * FROM Customers;

In this query, SELECT is the command, * means all columns, and Customers is the name of the table we’re querying.

SQL Commands

Data Definition Language (DDL)

DDL commands are used to create, modify, or delete structures in the database.

  • CREATE: This command is used to create a new table or a new database.
CREATE TABLE Customers (
    ID int,
    Name varchar(255),
    Email varchar(255),
    Address varchar(255)
  • ALTER: This command is used to modify an existing database object like a table.
ALTER TABLE Customers ADD Email varchar(255);
  • DROP: This command is used to delete a table or database.
DROP TABLE Customers;

Data Manipulation Language (DML)

DML commands are used for managing data within schema objects.

  • INSERT: This command is used to insert data into a table.
INSERT INTO Customers (ID, Name, Email, Address)
VALUES (1, 'John Doe', '[email protected]', '123 Street, City');
  • UPDATE: This command is used to modify existing records.
UPDATE Customers SET Email = '[email protected]' WHERE ID = 1;
  • DELETE: This command is used to delete existing records.

Data Query Language (DQL)

DQL commands are used to fetch data from the database.

  • SELECT: This command is used to select data from a database. The data returned is stored in a result table, called the result-set.
SELECT * FROM Customers;
SELECT Name, Email FROM Customers WHERE ID = 1;

Data Control Language (DCL)

DCL commands are used to grant and revoke rights and permissions.

  • GRANT: This command is used to give user’s access privileges to a database.
  • REVOKE: This command is used to take back access privileges from a user.

Remember, these are the basic commands to get you started with SQL. The SQL language is quite vast and can handle complex queries and manipulations. As you become more comfortable with the basics, you can explore the more advanced areas of the language.

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.