Query SQL

QUERY SQL

SELECT:

The following query, like the first, returns all the columns by using the selector and then the "LIMIT 1" clause forces the database only to return one row of data. Changing the          aquery to "LIMIT 1,1" forces the query to skip the first result, and then "LIMIT 2,1" skips the first two results, and so on. You need to remember the first number tells the database how   many results you wish to skip, and the second number tells the database how many rows to return.

select  from users LIMIT 1;

Lastly, we're going to utilise the where clause; this is how we can finely pick out the exact data we require by returning data that matches our specific clauses:

select  from users where username='admin';

select  from users where username != 'admin';

Using the like clause allows you to specify data that isn't an exact match but instead either starts, contains or ends with certain characters by choosing where to place the wildcard character represented by a percentage sign %. This returns any rows with username beginning with the letter a.

select  from users where username like 'a%';

select  from users where username like '%n';

This returns any rows with a username containing the characters mi within them.

select * from users where username like '%mi%';

UNION:

The UNION statement combines the results of two or more SELECT statements to retrieve data from either single or multiple tables; the rules to this query are that the UNION statement must retrieve the same number of columns in each SELECT statement, the columns have to be of a similar data type and the column order has to be the same. This might sound not very clear, so let's use the following analogy. Say a company wants to create a list of addresses for all customers and suppliers to post a new catalogue.

Using the following SQL Statement, we can gather the results from the two tables and put them into one result set: 

SELECT name,address,city,postcode from customers UNION SELECT company,address,city,postcode from suppliers;

INSERT:

The INSERT statement tells the database we wish to insert a new row of data into the table. "into users" tells the database which table we wish to insert the data into, "(username,password)" provides the columns we are providing data for and then "values ('bob','password');" provides the data for the previously specified columns.

insert into users (username,password) values ('bob','password123');

UPDATE:

The UPDATE statement tells the database we wish to update one or more rows of data within a table. You specify the table you wish to update using "update %tablename% SET" and then select the field or fields you wish to update as a comma-separated list such as "username='root',password='pass123'" then finally similar to the SELECT statement, you can specify exactly which rows to update using the where clause such as "where username='admin;".

update users SET username='root',password='pass123' where username='admin';

DELETE:

The DELETE statement tells the database we wish to delete one or more rows of data. Apart from missing the columns you wish to be returned, the format of this query is very similar to the SELECT. You can specify precisely which data to delete using the where clause and the number of rows to be deleted using the LIMIT clause.

delete from users where username='martin';