SQL Essentials v2

SQL Language

SQL HOME

SQL Syntax

 

SQL Functions

SQL Select

SQL Distinct

SQL Where

SQL And & Or

SQL Order By

SQL Insert Into

SQL Update

SQL Delete

SQL Injection

SQL Select Top

SQL Like

SQL Wildcards

SQL In

SQL Between

SQL Aliases

SQL Joins

SQL Inner Join

SQL Left Join

SQL Right Join

SQL Full Join

SQL Union

SQL Select Into

SQL Into Select

SQL Create DB

SQL Create Table

SQL Constraints

SQL Not Null

SQL Unique

SQL Primary Key

SQL Foreign Key

SQL Check

SQL Default

SQL Create Index

SQL Drop

SQL Alter

SQL Auto Increment

SQL Views

SQL Dates

SQL Null Values

SQL Null Functions

SQL Data Types

SQL DB Data Types

SQL Avg()

SQL Count()

SQL First()

SQL Last()

SQL Max()

SQL Min()

SQL Sum()

SQL Group By

SQL Having

SQL Ucase()

SQL Lcase()

SQL Mid()

SQL Len()

SQL Round()

SQL Now()

SQL Format()

References:

SQL Tutorial

SQL HOMESQL IntroSQL SyntaxSQL SelectSQL DistinctSQL WhereSQL And & OrSQL Order BySQL Insert IntoSQL UpdateSQL DeleteSQL InjectionSQL Select TopSQL LikeSQL WildcardsSQL InSQL BetweenSQL AliasesSQL JoinsSQL Inner JoinSQL Left JoinSQL Right JoinSQL Full JoinSQL UnionSQL Select IntoSQL Into SelectSQL Create DBSQL Create TableSQL ConstraintsSQL Not NullSQL UniqueSQL Primary KeySQL Foreign KeySQL CheckSQL DefaultSQL Create IndexSQL DropSQL AlterSQL Auto IncrementSQL ViewsSQL DatesSQL Null ValuesSQL Null FunctionsSQL Data TypesSQL DB Data Types

SQL Functions

SQL FunctionsSQL Avg()SQL Count()SQL First()SQL Last()SQL Max()SQL Min()SQL Sum()SQL Group BySQL HavingSQL Ucase()SQL Lcase()SQL Mid()SQL Len()SQL Round()SQL Now()SQL Format()

SQL Quick RefSQL Hosting

Statements

SELECT

  • queries a table for one or more rows based on specified columns
  • only columns specified will be returned
  • SELECT * returns ALL COLUMNS

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

  • column name alias to be used in the returned query dataset
  • helpful for:
    • shortening column names
    • creating unique column names for joins/unions

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/NOT IN

  • allows multiple values to be used with WHERE clause
  • specifying values
    • ('value1', 'value2')
  • specifying a column for values
    • (SELECT COL FROM TABLE)

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

Example

SELECT MediaType
FROM medialist
WHERE ColorType IN (‘red’,'dark red’)


SELECT MediaType
FROM medialist
WHERE ColorType IN (SELECT ColorType FROM Suppliers)

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

CONSTRAINTS

In SQL, we have the following constraints:

  • NOT NULL - Indicates that a column cannot store NULL value
  • UNIQUE - Ensures that each row for a column must have a unique value
  • PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
  • FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
  • CHECK - Ensures that the value in a column meets a specific condition
  • DEFAULT - Specifies a default value when specified none for this column

Syntax(MySQL)

CREATE TABLE table_name
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

ADD CONSTRAINT

Syntax

ALTER TABLE table_name
ADD UNIQUE (column_name)

DROP CONSTRAINT

Syntax

DROP UNIQUE
DROP PRIMARY KEY

NOTNULL

During Table Creation

CREATE TABLE table_name
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

PRIMARY KEY

Syntax

PRIMARY KEY (ID

Example

CONSTRAINT newIDCol PRIMARY KEY (ID,MediaColor)

FOREIGN KEY

Points to a PRIMARY KEY in another table

Syntax

Example

FOREIGN KEY (ID) REFERENCES projectlist(ID)

DROP

Syntax

Example

ALTER TABLE medialist
DROP FOREIGN KEY ID

MySQL

CREATE TABLE medialistarchive LIKE medialist;
INSERT INTO medialistarchive LIKE medialist;

CREATE INDEX

Syntax

CREATE INDEX index_name
ON table_name (column_name )

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

Example
CREATE UNIQUE INDEX medialist_Index
ON MediaIndex (MediaColor)

ALTER TABLE

  • update table schema

Syntax

Example

ALTER TABLE table_name AUTO_INCREMENT=1000

ADD

Syntax

ALTER TABLE table_name
ADD col_name varchar(255)

Example

MODIFY

Syntax

ALTER TABLE table_name
MODIFY col_name varchar(30)

Example

ALTER TABLE medialist
MODIFY COLUMN MediaBrand varchar(35)

DROP

Syntax

ALTER TABLE table_name
DROP COLUMN col_name

Example

INSERT

  • Inserts a row into a table
  • Provides values for one or more columns

Syntax

INSERT INTO table_name (col_name1,col_name2,col_name3)
VALUES(value1,value2,value3)

Example

INSERT INTO medialist (MediaColor, MediaType, MediaQuantity )
VALUES (‘red’, ‘crayon’, 1)
INSERT INTO medialist (MediaColor, MediaType, MediaQuantity )
VALUES (‘blue’, ‘crayon’, 2)
INSERT INTO medialist (MediaColor, MediaType, MediaQuantity )
VALUES (‘green’, ‘marker’, 3)

INSERT INTO

  • Copies data from one table into another EXISTING table.

Syntax

INSERT INTO dest_table_name(col_name1,col_name2,col_name3)
SELECT col_name1,col_name2,col_name3
FROM source_table_name

Example

INSERT INTO medialist (MediaColor, MediaType)
SELECT MediaColor, MediaType FROM
medialist_ArchiveTable

SELECT INTO

  • Copies data from one table into a NEW table
  • NOTE: This wasn’t working in MySQL5.6. Had to create the new table, then to INSERT INTO SELECT

Example

SELECT *
INTO medialist_ArchiveTable_2014 [IN external_db]
From medialist

UPDATE/SET

Syntax

Example

UPDATE medialist
SET MediaType=’marker’
WHERE ID=’1001’

DELETE FROM

Syntax

Example

DELETE FROM medialist
WHERE ID=’1002’

DELETE TABLE CONTENTS

Syntax

Example

DELETE FROM medialist
DELETE * FROM medialist

JOIN

JOIN tabulates columns from two or more tables WITH SAME NAMES

  • INNER JOIN: Returns all rows when there is at least one match in BOTH tables(default)
  • LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
  • RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
  • FULL JOIN: Return all rows when there is a match in ONE of the tables

 

 

 

 

 

 

Syntax

Example

SELECT medialist.MediaType, medialist.MediaColor, medialist.MediaBrand
FROM medialist
INNER JOIN MediaPriceListTable
ON medialist.MediaType=’brush’

UNION

  • UNION tabulates columns from two or more tables WITH DIFFERENT NAMES

Syntax

Example

Data Types

Data type

Description

CHARACTER(n)

Character string. Fixed-length n

VARCHAR(n) or

CHARACTER VARYING(n)

Character string. Variable length. Maximum length n

BINARY(n)

Binary string. Fixed-length n

BOOLEAN

Stores TRUE or FALSE values

VARBINARY(n) or

BINARY VARYING(n)

Binary string. Variable length. Maximum length n

INTEGER(p)

Integer numerical (no decimal). Precision p

SMALLINT

Integer numerical (no decimal). Precision 5

INTEGER

Integer numerical (no decimal). Precision 10

BIGINT

Integer numerical (no decimal). Precision 19

DECIMAL(p,s)

Exact numerical, precision p, scale s. Example: decimal(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal

NUMERIC(p,s)

Exact numerical, precision p, scale s. (Same as DECIMAL)

FLOAT(p)

Approximate numerical, mantissa precision p. A floating number in base 10 exponential notation. The size argument for this type consists of a single number specifying the minimum precision

REAL

Approximate numerical, mantissa precision 7

FLOAT

Approximate numerical, mantissa precision 16

DOUBLE PRECISION

Approximate numerical, mantissa precision 16

DATE

Stores year, month, and day values

TIME

Stores hour, minute, and second values

TIMESTAMP

Stores year, month, day, hour, minute, and second values

INTERVAL

Composed of a number of integer fields, representing a period of time, depending on the type of interval

ARRAY

A set-length and ordered collection of elements

MULTISET

A variable-length and unordered collection of elements

XML

Stores XML data

SQL Data Type Quick Reference

  • However, different databases offer different choices for the data type definition.
  • The following table shows some of the common names of data types between the various database platforms:

Data type

Access

SQLServer

Oracle

MySQL

PostgreSQL

boolean

Yes/No

Bit

Byte

N/A

Boolean

integer

Number (integer)

Int

Number

Int

Integer

Int

Integer

float

Number (single)

Float

Real

Number

Float

Numeric

currency

Currency

Money

N/A

N/A

Money

string (fixed)

N/A

Char

Char

Char

Char

string (variable)

Text (<256)

Memo (65k+)

Varchar

Varchar

Varchar2

Varchar

Varchar

binary object

OLE Object Memo

Binary (fixed up to 8K)

Varbinary (<8K)

Image (<2GB)

Long

Raw

Blob

Text

Binary

Varbinary

Operators

Operator

Description

=

Equal

<>

Not equal. Note: In some versions of SQL this operator may be written as !=

>

Greater than

<

Less than

>=

Greater than or equal

<=

Less than or equal

BETWEEN

Between an inclusive range

LIKE

Search for a pattern

IN

To specify multiple possible values for a column

SQL Injection

http://www.w3schools.com/sql/sql_injection.asp

Copyright 2018· All rights reserved