SQL - Tutorial

Lars Vogel

Version 0.4

26.01.2010

Revision History
Revision 0.1	13.09.2009	Lars Vogel
Removed from the MySQL Tutorial
Revision 0.2	14.09.2009	Lars Vogel
Corrected, added links
Revision 0.3	06.10.2009	Lars Vogel
Added update, restructured description
Revision 0.4	26.01.2010	Lars Vogel
t

SQL

This article describes the most important SQL statements.

Table of Contents

1. SQL - Structured Query Language

1.1. Overview
1.2. Additional material

2. Database setup

2.1. Database schema
2.2. User Maintenance

3. Table

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

4.1. Select
4.2. Insert
4.3. Update
4.4. Delete

5. Thank you

6. Questions and Discussion

7. Links and Literature

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.

1.2. Additional material

Please use the following resources to learn about the usage of SQL with Java and Eclipse.

See MySQL Tutorial for the installation and usage of MySQL.
See Apache Derby for the installation and usage of Apache Derby.
See MySQL and Java Tutorial for the usage of MySQL from Java.
See Eclipse DTP Tutorial for the usage of the Eclipse Data Tool Platform for handling relational databases.

2. 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.

2.1. Database schema

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

Table 1. 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".

2.2. User Maintenance

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

Table 2. 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

3. Table

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

Table 3. 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, SUMMERY VARCHAR(40) NOT NULL, COMMENTS VARCHAR(400) NOT NULL, PRIMARY KEY (ID) );	Creates a table with the listed columns.

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

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

4.1. Select

Table 4. Select

Command	Description
SELECT * FROM TABLE table_name;	Selects all the data from table "table_name"

4.2. Insert

Table 5. Insert

Command	Description
MISSING	MISSING

4.3. Update

Table 6. 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.

4.4. Delete

Table 7. 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 id "1" and "29".

5. Thank you

Thank you for practicing with this tutorial.

Please note that I maintain this website in my private time. If you like the information I'm providing please help me by donating.

6. Questions and Discussion

For questions and discussion around this article please use the www.vogella.de Google Group. Also if you note an error in this article please post the error and if possible the correction to the Group.

I believe the following is a very good guideline for asking questions in general and also for the Google group How To Ask Questions The Smart Way.

7. 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