Home Tutorials Training Consulting Products Books Company Donate Contact us









NOW Hiring

Quick links

Share

SQL Statements. This tutorial lists important SQL statements which you need to use to create, update and read from database tables.

1. SQL - Structured Query Language

1.1. Overview

SQL is a query language for relational databases. This article covers the most important SQL statements and can be used to look up example for standard SQL queries.

2. Table

The following demonstrates how to create and drop (deletes) tables.

Table 1. Tables
Command Description

CREATE TABLE TEST (id INT NOT NULL AUTO_INCREMENT, MYTEST VARCHAR(30) NOT NULL, PRIMARY KEY (ID) );

Creates a table TEST with two columns (id, mytest), both are not allowed to be NULL, id is the primary key.

CREATE TABLE COMMENTS ( id INT NOT NULL AUTO_INCREMENT, MYUSER VARCHAR(30) NOT NULL, EMAIL VARCHAR(30), WEBPAGE VARCHAR(100) NOT NULL, DATUM DATE NOT NULL, SUMMARY VARCHAR(40) NOT NULL, COMMENTS VARCHAR(400) NOT NULL, PRIMARY KEY (ID) );

Creates a table with the listed columns.

3. Data maintenance - Select, Insert, Delete, Update

The following demonstrates how to insert, updates and deletes entries from a database.

3.1. Select

Table 2. Select
Command Description

SELECT * FROM TABLE table_name;

Selects all the data from table "table_name"

3.2. Insert

Table 3. Insert
Command Description

MISSING

MISSING

3.3. Update

Table 4. Update
Command Description

update table_name SET field = value WHERE condition;

Updates field with value in table_name given then the where condition is met.

3.4. Delete

Table 5. Delete
Command Description

DELETE FROM table_name WHERE id='8';

Deletes an entry from table "table_name"which has the id 8. Requires that the table has a column "id".

delete from table_name where id <> 1 and id <> 29;;

Deletes all entries from table "table_name" except the entries with the ID "1" and "29".

4. Database setup

After installation of a database management system you need to create the database itself, create users and grant access to the database. Some database management system create defaults but it is good to know how to create them yourself.

4.1. Database schema

The following explains the creation, deletion and usage of database schemas.

Table 6. Manage your databases
Command Description

show databases;

List all the databases on the SQL server

create database my_database;

Creates a new database with the name my_database

drop database my_database;

Deletes database my_database

use my_database;

Switches to my_database

show schemas;

Show all schemas in this database

SELECT * FROM INFORMATION_SCHEMA.TABLES where table_schema='myschema';

Shows all tables for the database schema "myschema".

4.2. User Maintenance

The following demonstrate how to create a user and how to grant access rights to a user.

Table 7. User maintenance
Command Description

CREATE USER my_user IDENTIFIED BY 'my_password';

Creates a user my_user on the database with the password my_password;

grant usage on . to my_user@localhost identified by 'my_password';

Allows the user my_user to connect to the database.

grant all privileges on my_database.* to my_user@localhost;

Allows the user my_user from the localhost full access to my_database

5. About this website

6. Links and Literature

MySQL Tutorial Installation and usage of MySQL.

Apache Derby Installation and usage of Apache Derby

MySQL and Java Tutorial Accessing MySQL via Java.

Eclipse DTP Tutorial Eclipse Data Tool Platform Introduction

6.1. vogella GmbH training and consulting support

TRAINING SERVICE & SUPPORT

The vogella company provides comprehensive training and education services from experts in the areas of Eclipse RCP, Android, Git, Java, Gradle and Spring. We offer both public and inhouse training. Whichever course you decide to take, you are guaranteed to experience what many before you refer to as “The best IT class I have ever attended”.

The vogella company offers expert consulting services, development support and coaching. Our customers range from Fortune 100 corporations to individual developers.

Copyright © 2012-2016 vogella GmbH. Free use of the software examples is granted under the terms of the EPL License. This tutorial is published under the Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Germany license.

See Licence.