Basic SQL Syntax, Structures, Key words and Queries

Submitted by Yorkiebar on Thursday, April 3, 2014 - 07:21.
Introduction: This tutorial is going to be covering the basic syntax of SQL queries in MySQL and MySQLi. MySQL and MySQLi...? MySQLi is an updated version of the original MySQL language. Both versions use almost exactly the same syntax although MySQLi should be used as it is more secure, updated and reliable. Query Syntax: To create a MySQL(i) query, you combine different MySQL(i) keywords together and give it the information required to parse for the necessary returned information. For example, to retrieve all rows where the username is "admin", it would be... 
  1. mysql_query("SELECT * FROM `tableName` WHERE `columnName`='data'");
  2. mysqli_query($connection, "SELECT * FROM `tableName` WHERE `columnName`='data'");	-	Please note, the $connection variable would already be defined AS a DATABASE AND SQL service connection TO the server.
  1. mysql_query("SELECT * FROM `users` WHERE `username`='admin'");
  2. mysqli_query($connection, "SELECT * FROM `users` WHERE `username`='admin'");
Syntax: In SQL the following syntax is used... ` Used to surround defined names within the SQL language (such as table and column names). ' or " Used to surround data whether the data is hardcoded in to the query or a variable is referenced. * Select all rows. Keywords: Here are the most common keywords to use in SQL... INSERT INTO - Used to push data in to pre-existing tables. DELETE FROM - Used to delete data from pre-existing tables. CREATE TABLE - Used to create tables. DROP TABLE - Used to delete pre-existing tables. IF EXISTS - Used to check if the previously executing query's data is existing (example below). COUNT - Used to deteremine how many data rows to return from the query WHERE - Used to specify data that must be contained within the returned rows UPDATE - Used to update an already existing row in a pre-existing table. Examples: Here are some examples of using the above keywords and syntax. Insert a new row in to the table users... 
  1. mysql_query("INSERT INTO `users` VALUES('val1', 'val2', 'val3')");
  2. mysqli_query($connection, "INSERT INTO `users` VALUES('val1', 'val2', 'val3')");
Return any users from the table users who have the level property of admin... 
  1. mysql_query("SELECT * FROM `users` WHERE `level`='admin'");
  2. mysqli_query($connection, "SELECT * FROM `users` WHERE `level`='admin'");
Delete table users if it already exists... 
  1. mysql_query("DROP TABLE `users` IF EXISTS");
  2. mysqli_query($connection, "DROP TABLE `users` IF EXISTS");

Tags

Add new comment

About text formats
  • 10 views