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. The commands are intuitive, and there aren't too many of them. 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. The problem is, when people need to add SQL code to their PHP pages, or run a simple SQL query inside phpMyAdmin to look something up, they're out of luck. Learning to write your own SQL scripts isn't too time consuming, and will prove to be enormously helpful when creating data-filled websites.
Things You'll Need
- Text editor
- Web server
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. You can use "AUTO_INCREMENT" to automatically create a new number whenever you create a new record. (This will become clearer in the example code below.)
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. Here's what it would look like:DROP TABLE IF EXISTS movies;CREATE TABLE movies (id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(60),director VARCHAR(30),year INT);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. (Why not just use the maximum amount of allowed characters every time? Because that takes up space and will ultimately slow things down.)
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); Notice that in the ID column, instead of a value, it says "null." That's because when the ID column was created as the primary key field, the AUTO_INCREMENT command was included, which means SQL will assign each one a number automatically. Writing "null" is like saying, "This field exists, but you're going to figure out what goes in it for me."
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';That's one phrase, but it's pretty long. Remember you can use line breaks wherever you want. To make it easier to read, you might instead type:UPDATE moviesSET 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.
Tips & Warnings
- There are a few more SQL commands than are explained here. To learn about them and find out the more advanced uses of SQL, explore the SQL tutorials provided by the World Wide Web Consortium (see Resources).