SQL: 01 - SQL Statements

SELECT

Syntax

Return specific columns

SELECT col_name,col_name

FROM table_name

 

Return ALL columns

SELECT *

FROM table_name

 

Example

SELECT TOP 50 PERCENT MediaColor, MediaType

FROM Db_ArtBox.medialist

WHERE MediaType=’crayon’

AND (MediaColor=”red’

OR MediaColor=’darkred’)

ORDER BY MediaColor, MediaType DESC

GROUP BY

LIMIT 1

 

ORDER BY

Syntax

SELECT col_name,col_name FROM table_name

ORDER BY col_name,col_name DESC

 

Keyword	Description
ASC	Ascending
DESC	Descending

Example

SELECT *

FROM medialist

ORDER BY MediaColor, MediaType ASC

 

LIMIT (MySQL)

Syntax

SELECT *

FROM table_name

LIMIT 2

 

Example

SELECT *

FROM medialist

ORDER BY MediaName DESC

LIMIT 20

 

SELECT TOP (SQL Server)

 

Syntax

SELECT TOP 2 *

FROM table_name

SELECT TOP 50 PERCENT * FROM table_name

 

Example

SELECT TOP 2 *

FROM medialist

SELECT TOP 50 PERCENT * FROM medialist

 

Keyword	Description
2	Quantity
2 PERCENT	Percentage

 

LIKE

Syntax

 

Example

SELECT *

FROM medialist

WHERE MediaColor LIKE ‘%red%’

 

SELECT *

FROM medialist

WHERE MediaColor NOT LIKE ‘%red%’

 

SELECT *

FROM medialist

WHERE MediaColor NOT LIKE ‘_ed’

 

SELECT *

FROM medialist

WHERE MediaColor LIKE ‘r__’

 

WildCard Characters

In SQL, wildcard characters are used with the SQL LIKE operator.

SQL wildcards are used to search for data within a table.

With SQL, the wildcards are:

 

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for a single character
[charlist]	Sets and ranges of characters to match
[^charlist] or [!charlist]	Matches only a character NOT specified within the brackets

 

ALIAS

Syntax

SELECT column_name AS alias_name

FROM table_name;

 

Example

SELECT MediaType AS BrushType

FROM medialist

 

MySQL Column Combine

Syntax

 

Example

SELECT CONCAT(MediaType,’, ‘,MediaColor) AS MediaQuickName

FROM medialist

 

WHERE

Syntax

 

Example

ROWNUM <= number;

IN

Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name IN (value1,value2,...);

 

Example

SELECT MediaType

FROM medialist

WHERE MediaType IN (‘red’,dark red’’)

 

BETWEEN

 

Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

 

Example

SELECT *

FROM medialist

WHERE MediaColor BETWEEN ‘M%’ AND ‘R%’

 

CREATE DB

Syntax

CREATE DATABASE db_name

 

Example

CREATE DATABASE arttoolbox

 

Collation?

 

CREATE TABLE

Syntax

 

Example

CREATE TABLE medialist

(

/*

ID int IDENTITY(1000,1) PRIMARY KEY,

*/

ID Integer PRIMARY KEY AUTOINCREMENT,

MediaColor varchar(255) NOT NULL,

MediaType varchar(255),

Quantity int

)

 

Note: AUTOINCREMENT not working with MySQL

 

Try:

ID int NOT NULL AUTO_INCREMENT

PRIMARY KEY ID