22417835
Description
Mind Map by Nathaniel Burke, updated more than 1 year ago
More
|
Created by TEL Bath
about 7 years ago
|
|
|
Copied by Nathaniel Burke
almost 4 years ago
|
|
Structured Query
Language (SQL)
- "SELECT" and "WHERE" Clauses
- A SELECT clause is used to retrieve data from a
database. To create a SELECT clause, the syntax is:
SELECT [Column 1 Name, Column 2 Name etc...]
FROM [Table Name]. Instead of specifying each
column, "*" can also be used as a wildcard to
retrieve data from every column in that table.
- A WHERE clause is used to filter which pieces of data another
SQL clause affects. For example, it can be used in conjunction
with a WHERE clause to specify that you only want to retrieve
specific data. The syntax is [First Statement] WHERE [Predicate].
- A SELECT clause is used to retrieve data from a
database. To create a SELECT clause, the syntax is:
SELECT [Column 1 Name, Column 2 Name etc...]
FROM [Table Name]. Instead of specifying each
column, "*" can also be used as a wildcard to
retrieve data from every column in that table.
- Editing Databases
- Databases can
be edited with
INSERT INTO,
UPDATE and
DELETE clauses.
- An INSERT INTO clause is used to add new records to
the database. You can either specify the columns you
are adding data to in parenthesis if you only want to
add data to specific columns or you can make sure the
values are in the same order as the columns on the
table and not specify the columns in the statement.
- Any fields you do
not specify data for
will be given the
NULL data type.
- Any fields you do
not specify data for
will be given the
NULL data type.
- An UPDATE clause is used to edit the values
of fields in a database. It takes the form of
UPDATE [Table Name] SET [Column 1
Name] = [Value 1], [Column 2 Name] =
[Value 2] etc... Without a WHERE clause, it
will update all records in the table.
- A DELETE clause allows you to remove
records from a table. The syntax is DELETE
FROM [Table Name]. Without a WHERE
clause, it will delete all the records in the
specified table.
- An INSERT INTO clause is used to add new records to
the database. You can either specify the columns you
are adding data to in parenthesis if you only want to
add data to specific columns or you can make sure the
values are in the same order as the columns on the
table and not specify the columns in the statement.
- Databases can
be edited with
INSERT INTO,
UPDATE and
DELETE clauses.
- Syntax
- The syntax of SQL is is very
logical and close to English,
making it intuitive to
understand.
- SQL commands can be
broken up into
clauses/statements and
predicates and
expressions.
- For example, in the command "SELECT * FROM People
WHERE Name = ‘John’", both "SELECT * FROM People"
and "WHERE Name='John'" are clauses/statements,
"Name='John'" is a predicate and "'John'" is an
expression.
- For example, in the command "SELECT * FROM People
WHERE Name = ‘John’", both "SELECT * FROM People"
and "WHERE Name='John'" are clauses/statements,
"Name='John'" is a predicate and "'John'" is an
expression.
- SQL commands can be
broken up into
clauses/statements and
predicates and
expressions.
- The syntax of SQL is is very
logical and close to English,
making it intuitive to
understand.
- Data Types
- Data types specify the type of
data that is contained in the
fields in a given column.
- More basic SQL languages
such as SQLite have a relatively
limited amount of available data
types such as integer (whole number),
text/string, real (decimal number) and
NULL (a data-type representing the
absence of data.
- More complex SQL languages such as
MySQL or SQL Server might also include data types for
representing dates or images.
- More basic SQL languages
such as SQLite have a relatively
limited amount of available data
types such as integer (whole number),
text/string, real (decimal number) and
NULL (a data-type representing the
absence of data.
- Data types specify the type of
data that is contained in the
fields in a given column.
- Joining Data from Multiple Tables
- Related records from
multiple tables found with
foreign keys can be joined
so commands can be
carried out using data from
both of them with a JOIN
clause.
- An INNER JOIN clause retrieves all the
records with matching values in both tables.
- A LEFT JOIN clause retrieves all the
records from the first table
specified and any matching
records in the second.
- A RIGHT JOIN clause retrieves all
the records from the second table
specified and any matching
records in the first.
- An FULL/OUTER JOIN clause
retrieves all the records in both
tables regardless of if there is a
match.
- An INNER JOIN clause retrieves all the
records with matching values in both tables.
- Related records from
multiple tables found with
foreign keys can be joined
so commands can be
carried out using data from
both of them with a JOIN
clause.
- "ORDER BY" Clause and "BETWEEN"
- An ORDER BY clause can be used with a
SELECT statement to describe which columns
the resulting data should be ordered by. For
text/string data types this will be in
alphabetical order while for numerical data
types it will be in order of size.
- ASC and DESC can be added
after each column name in an
ORDER BY clause to specify if
the order is ascending or
descendinging.
- ASC and DESC can be added
after each column name in an
ORDER BY clause to specify if
the order is ascending or
descendinging.
- BETWEEN can be used as a PREDICATE with a
WHERE clause to specify only to get records in a
certain range. The syntax is WHERE [Column Name]
BETWEEN [Value 1] AND [VALUE 2]
- An ORDER BY clause can be used with a
SELECT statement to describe which columns
the resulting data should be ordered by. For
text/string data types this will be in
alphabetical order while for numerical data
types it will be in order of size.
- Predicates and Boolean Operators
- Predicates are conditions that you can
test for a piece of data. They are often
used in a WHERE clause to specify data
that obeys the predicate.
- The most simple type of
predicate is testing if a record's
field is equal to the value
specified. The syntax for this is
simply [Column Name] = [Value]
- Another type of predicate created by using "LIKE". This
allows you to only specify data that fits specific patterns.
"%" is used to represent zero, one or multiple characters
and "_" is used to represent a single character. The
syntax is [Column Name] LIKE [String goes here. For
example "A%" would make the predicate true for any
fields that begin with the letter "A"].
- For the NULL data type, you have
to use IS NULL instead of =
"NULL" as NULL data types have
no value associated with them.
- The most simple type of
predicate is testing if a record's
field is equal to the value
specified. The syntax for this is
simply [Column Name] = [Value]
- Boolean operators such as AND, OR
and NOT can be used to combine
predicates in a single WHERE clause.
They act like their associated logic
gates with a true predicate being
binary 1 and a false predicate being
binary 0.
- Brackets can be used to
specify which parts of a
large group of predicates
and boolean operators
are looked at first
- Brackets can be used to
specify which parts of a
large group of predicates
and boolean operators
are looked at first
- Predicates are conditions that you can
test for a piece of data. They are often
used in a WHERE clause to specify data
that obeys the predicate.
Want to create your own Mind Maps for free with GoConqr? Learn more.