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:
CREATE TABLE tablename (name VARCHAR(20));
CREATE DATABASE dbname;
CREATE USER username;
CREATE USER username@localhost;
DROP USER username;
DROM TABLE tablename;
Grand access rights to the database:
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
[...]@localhost --> Allows the user to log on FROM localhost.
GRANT ALL PRIVILEGES ON *.* TO 'youUsername'@'localhost';
SELECT * FROM tablename;
Delete specific data in a table:
DELETE FROM tablename WHERE property_name='bob';
Delete all data in table:
DELETE FROM tablename;