How to Write SQL Scripts

SQL, or Structured Query Language, which allows for the creation and editing of databases, is one of the easiest Web-based programming languages to understand. Still, many people don't bother learning the ins and outs of SQL because database managers such as phpMyAdmin allow you to create, edit and view tables without knowing any code.. Learning to write your own SQL scripts isn't too time consuming, and will prove to be enormously helpful when creating data-filled websites.

Learn proper SQL syntax. Traditionally, SQL commands are all uppercase, while the names of your tables and all your personal field data is in lowercase. As in PHP, all statements end with a semicolon, but those statements can be broken up into separate lines any way you want (to aid with readability).

Familiarize yourself with SQL data types--the way SQL identifies the information you have in all the fields in your tables. The most common are INT (for integers ranging from -2 billion to 2 billion), BIGINT (for integers larger or smaller than that), VARCHAR (text up to 256 characters) and TEXT (text up to about 64,000 characters).

Understand the need for primary keys. In practice, every table you create will have an "ID" column containing a unique number--labeled the "primary key"--to ensure that no "record" (or row) is the same.

Create a table in your database by using the CREATE TABLE command. During your practice, you'll often be creating the same table over and over again, so it's often helpful to precede that with the DROP TABLE IF EXISTS command so data doesn't start to spill over and confuse you. Here's the first thing you would write to create a table called "movies": DROP TABLE IF EXISTS movies; CREATE TABLE movies ();

Add field names, or column names, to the table. So far, "movies" is completely empty. To fill it with data about your favorite movies, you might want a "Title" column, a "Director" column and a "Year" column--in addition to the "id" column that contains the primary key. You add this information inside the parentheses of the CREATE TABLE line.

Notice that after each field name, you have to declare what kind of data type it is. Furthermore, while SQL automatically determines the length of integers, you have to declare the maximum length of all text data. So in the above code, entries in the "title" field can't be longer than 60 characters, and entries in the "directors" field can't be longer than 30.

Add data to your tables. You now have a table called "movies" with columns for the movie primary key, title, director and year, but nothing in those columns. To add data, use the INSERT INTO command. Data is inserted one record (row) at a time. So underneath all the CREATE TABLE code from above, the INSERT INTO commands would look something like this: INSERT INTO movies VALUES (null, 'Casablanca', 'Michael Curtiz', 1942); INSERT INTO movies VALUES (null, 'Star Wars', 'George Lucas', 1977); INSERT INTO movies VALUES (null, 'Psycho', 'Alfred Hitchcock', 1960);

Use the SELECT command to pull up specific information from a table. When you're using a database manager such as phpMyAdmin, this allows you to quickly get the data you need. If you want to look at the titles of the movies in your table, you would write: SELECT title FROM movies; If you want to look at an entire table, use an asterisk: SELECT * FROM movies; If you want to get specific, use the WHERE command: SELECT title FROM movies WHERE director = 'Alfred Hitchcock'; That would pull up the name of every movie in your table directed by Alfred Hitchcock. If you decided you needed another Hitchcock movie, just use the INSERT command again: INSERT INTO movies VALUES (null, 'North by Northwest', 'Alfred Hitchcock', '1956');

Edit your data using the UPDATE and SET commands. In the above example, the date for "North by Northwest" is set as 1956. That's incorrect; it was actually released in 1959. In order to fix the mistake, you would write: UPDATE movies SET year = '1959' WHERE title = 'North by Northwest';

Delete data by using the DELETE FROM command. If you decide you don't like "Psycho" anymore and want to get rid of it, you would write: DELETE FROM movies WHERE id = 3; Notice in the above command, "Psycho" is deleted based on its primary key, not its title. You could just write: DELETE FROM movies WHERE title = "Psycho"; However, what if there was another movie in your table called "Psycho" that you forgot about? They would both be deleted. Be very careful whenever using DELETE.