SQL Basic Commands

SQL is a computer language that can be used to store and manage data. It can be used to store and retrieve information without having to worry about the internal representation of the data, SQL will choose the best way to manage it. Now, with the release of SQLite and bindings for most popular programming languages, it can be very useful in small applications as it will store the database locally and hence does not need a server to work. I have used it in many small applications, one of which I will probably release in this blog in the following weeks.

There are many free version available, my two favorites being PostgreSQL and SQLite. MySQL is freely available, but it has the drawback of being licensed under the GPL, which means that if you used it in your application, you *have* to release the source code.

Here are some of the commands you need to get started with SQL:

Show databases:
SHOW DATABASES;
Change database:
USE dbname;
Create table:
CREATE TABLE tablename (name VARCHAR(20));
Create database:
CREATE DATABASE dbname;
Create user:
CREATE USER username;
CREATE USER username@localhost;
Remove user:
DROP USER username;
Rename table:
DROM TABLE tablename;
Grand access rights to the database:
(http://dev.mysql.com/doc/refman/4.1/en/adding-users.html)
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost'; #No password
GRANT USAGE ON *.* TO 'dummy'@'localhost'; #No password, has to log from localhost, usage = no privileges
Notes:
[...]@localhost --> Allows the user to log on FROM localhost.
GRANT ALL PRIVILEGES ON *.* TO 'youUsername'@'localhost';
Select data:
SELECT * FROM tablename;
Delete specific data in a table:
DELETE FROM tablename WHERE property_name='bob';
Delete all data in table:
DELETE FROM tablename;
TRUNCATE tablename;

With these commands you can start building your own custom application. I will post more information on how to embed it in a python program soon.