Skip to content

SQL Statements and Commands for Beginners

April 11, 2010

CREATE – creates a table within your database with the specified columns.

Syntax:

CREATE TABLE [table_name] ([column1] [data_type1], [column2 [data_type2]);

Example:

CREATE TABLE names (id INTEGER PRIMARY KEY, name TEXT);

You’ll notice that I define a column names “id” of type INTEGER and PRIMARY KEY. This is SQL common practice used to better allow the iteration through rows and to ensure that no row is exactly the same.

SELECT – used for retrieving data from the SQL database keeping in mind certain user-defined parameters.

Syntax:

SELECT ([column1], [column2]) FROM [table_name]; — selects [column1] and [column2] columns and all rows from the table

-or-

SELECT * FROM [table_name]; — selects all rows and columns from the table

Also, some extra statements at the end can be added to filter through the rows, most commonly the WHERE statement.

Example:

SELECT * FROM names WHERE id=1; — select all the columns for the result with id equal to 1

UPDATE – changes an already-existent row within the database, given user-defined parameters

Syntax:

UPDATE [table_name] SET [column1]=[value1], [column2]=[value2] WHERE [column3]=[value3]; —updates the columns to the specified values at the row with the request value in the table.

Example:

UPDATE names SET name=”Connor” WHERE id=1; —changes the name on row with id of 1 to “Connor”. Notice how the text string is within quotes.

While using the SQL UPDATE command, be sure that you correctly utilize the WHERE statement so that your whole table does not get overwritten (unless you want your whole table to be changed).

DELETE — used to delete a row or a set of rows.

Syntax:

DELETE FROM [table_name] WHERE [column1]=[value1];

Example:

DELETE FROM names WHERE id=1; —deletes the item with id equal to 1

With the DELETE command, you especially want to be careful with filtering the rows to be deleted.

INSERT — adds a row at the end of the table.

Syntax:

INSERT INTO [table_name] ([column1], [column2]) VALUES ([value1], [value2]);

Example:

INSERT INTO names (name) VALUES (“John”); —adds a row with the name column equal to the string “John”. Notice how we do not need to specify the id integer/primary key column, because its data type(s) allows it to auto-increment for each row.

*statements are not required to be capitalized as shown in this tutorial, but I used them just so you can see the difference between SQL statements and user-defined fields

Advertisements

From → regular

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: