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()
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
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
CHECK
CHECK (col_name
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
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
DELETE FROM table_name
DELETE * FROM table_name
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
- Log in to post comments