Ultimate Web Guide to SQL Database Language
SQL stands for Structured Query Language and is a computer language that is used to interact with and manage a database. It is the most widely used language used to manage databases. One thing that is important to note is that SQL is not a specific database system, there are many database systems that use SQL. The most commonly used is MySQL, however there are dozens of different systems, including Microsoft SQL, and Oracle as well as many lesser known systems.
When installing SQL, it is important to first decide which database system you want to install. MySQL is likely a good place to start as it is both powerful and easy to work with. When using Windows as your operating system it is easiest to install an entire AMP stack. An AMP stack will install Apache, a web server, MYSQL, your database, and PHP, a web programming language. Popular AMP stacks include XAMPP and WAMP. When using Linux, you'll want to head over to mysql.com and download the latest version as a tar.gz and install it.
Creating a Database
Creating a database with SQL is incredibly easy. If you are using an AMP stack, you may have PHPMyAdmin installed which is a web interface for interacting with databases. Creating a database with PHPMyAdmin is as simple as typing in a database name and hitting the submit button. Without using PHPMyAdmin you'll have to use straight SQL code. The following code with create a database: "create database databasename;"
Creating a Database Table
Creating a table within a database is the next step in the process. A table can be loosely compared to an Excel spreadsheet and is the place where the data is actually stored. To create a table, the following example code can be used:
"CREATE TABLE tablename ( id INT, data VARCHAR(100));"
In the example, there is an column named 'id' that can contain integers, and a column named 'data' that can contain variable characters up to a length of 100.
Primary and Foreign Key
A primary key is a field that is used to unique identify data within your database tables. When processing and displaying data from your database, the primary key is likely the field that your relational database will depend on to operate. A foreign key is used when tying together two related tables. A foreign key is used when a second table references a piece of information within the first table. Foreign keys can begin to get a little complicated. Foreign keys are a more advanced feature of SQL and it is likely you won't be using them in your first databases.
Adding data with SQL
When inserting data into SQL, you'll be using the INSERT INTO statement.
"INSERT INTO tablename VALUES (value1, value2, value3...);
When inserting data into a database it is important to make sure that you are inserting the data into the correct fields. In the first example the data is not told which columns to fill, only their values. It is usually best to specify the column names to ensure the data is being stored correctly. This second example will specify the column name.
"INSERT INTO tablename (column1, column2, column3) VALUES (value1, value2, value3);"
Deleting data with SQL
When deleting data with SQL it is absolutely vital that you are deleting the correct data. It is best if you know what the primary key value is for the data you want to delete. Because the primary key is unique, when you delete data matching the primary key it should only match the one row of data. It is also a good idea to put a limit of 1 on the query just in case. A limit of 1 will ensure that your query will only delete one row of data. An example is:
"DELETE * FROM tablename WHERE id=1 LIMIT 1;"
Emptying an entire table can be done with
"DELETE * FROM tablename;"
Updating Data with SQL
The update query is used when you want to change existing data within the database. Again you should use the primary key to limit your query to only one row of data. An example of an update query is:
"UPDATE tablename SET column1=value1, column2=value2... WHERE id=1"
Retrieving Data with SQL
The query used to withdraw data from the database is called the select query and is where the magic of SQL happens. Select statements can be incredibly simple or deviously complex. To select information from a database you should use the following query:
"SELECT * FROM tablename
You can select specific columns by substituting column names for the * in the example query.
PHP and SQL
SQL is a great language, however it doesn't interact with the web at all. In order to build a web page that uses a SQL database you need to use a language that interacts with the SQL and can print out HTML. The most commonly used language for this purpose is PHP. Unfortunately this guide is not long enough to teach you how to interact with your SQL server, however the following links should help you.