Sql

From ThaiiS Note (Wiki)

• http://www.w3schools.com/sql/

Contents 1 SELECT 1.1 Where 1.2 Operators 1.3 AND & OR 1.4 TOP 2 UPDATE 3 DELETE 4 INSERT INTO

SELECT

extracts data from a database

Syntax

SELECT column_name(s)
FROM table_name

SELECT * FROM table_name

Ex

SELECT * FROM Persons

SELECT LastName,FirstName FROM Persons

Where

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name operator value

Operators

=  	Equal
<> 	Not equal
> 	Greater than
< 	Less than
>= 	Greater than or equal
<= 	Less than or equal
BETWEEN 	Between an inclusive range
LIKE 	Search for a pattern
IN 	If you know the exact value you want to return for at least one of the columns

AND & OR

SELECT * FROM Persons
WHERE FirstName='Tove'
AND LastName='Svendson'

SELECT * FROM Persons
WHERE FirstName='Tove'
OR FirstName='Ola'

Ex.

SELECT * FROM Persons
WHERE City='Sandnes'

SELECT * FROM Persons WHERE FirstName='Tove'

SELECT * FROM Persons WHERE Year=1965

SELECT CompanyName, ContactName 
FROM customers
WHERE CompanyName > 'g'
AND ContactName > 'g'

• http://www.w3schools.com/sql/sql_where.asp

TOP

Syntax

SELECT TOP number|percent column_name(s)
FROM table_name

MySQL Syntax

SELECT column_name(s)
FROM table_name
LIMIT number

SELECT *
FROM Persons
LIMIT 5

UPDATE

updates data in a database Syntax

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Ex.

UPDATE Persons
SET Address='Nissestien 67', City='Sandnes'
WHERE LastName='Tjessem' AND FirstName='Jakob'

• http://www.w3schools.com/sql/sql_update.asp

DELETE

deletes data from a database

DELETE FROM table_name
WHERE some_column=some_value

Ex.

DELETE FROM Persons
WHERE LastName='Tjessem' AND FirstName='Jakob'

Delete All Rows

DELETE FROM table_name

or

DELETE * FROM table_name

INSERT INTO

inserts new data into a database

Syntax

INSERT INTO table_name
VALUES (value1, value2, value3,...)

or

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

ex.

INSERT INTO Persons
VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')

or

INSERT INTO Persons (P_Id, LastName, FirstName)
VALUES (5, 'Tjessem', 'Jakob')

• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index