click below
click below
Normal Size Small Size show me how
SQL
Term | Definition |
---|---|
SELECT | extracts data from a database |
UPDATE | updates data in a database |
DELETE | deletes data from a database |
INSERT INTO | inserts new data into a database |
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 |
Data returned from a SELECT statement is stored in a __________ . | Result table |
The data stored in a result table is called the _________ . | Result-set |
SELECT * | select all |
SELECT DISTINCT | select all the different values |
WHERE | extracts records that fulfill a specified condition |
> | greater than |
< | less than |
>= | greater than or equal to |
<= | less than or equal to |
<> OR != | not equal to |
BETWEEN | between a certain range |
LIKE | search for a pattern |
IN | to specify multiple possible values for a column |
AND | displays records if all the conditions separated by AND are true |
OR | displays records if any of the conditions separated by OR are true |
NOT | displays records if the conditions are not true |
ORDER BY | Sorts the result-set according to specified criteria |
ORDER BY _____ DESC | orders by descending order |
IS NULL | A criteria that searches for fields that are empty |
IS NOT NULL | A criteria that searches for fields that are not empty |
SELECT TOP / LIMIT / FETCH | Specifies the number of records to return in the result set |
SELECT TOP [#] PERCENT | selects first % |
MIN () | returns the smallest value of selected column |
MAX() | returns the largest value of selected column |
COUNT() | returns the number of rows that match a specified criterion |
AVG() | returns the average value of a numeric column |
SUM() | returns the total sum of a numeric column |
LIKE 'a%' | Finds any values that start with "a" |
LIKE '%a' | Finds any values that end with "a" |
LIKE '_r%' | Finds any values that have "r" in the second position |
LIKE '%or%' | Finds any values that have "or" in any position |
LIKE 'a_%' | finds any values that start with "a" and are at least 2 characters in length |
LIKE 'a__%' | Finds any values that start with "a" and are at least 3 characters in length |
LIKE 'a%o' | Finds any values that start with "a" and ends with "o" |
NOT LIKE 'a%' | Finds any values that do not start with "a" |
[ ] | finds any values with any single character within the brackets |
[^] or [!] | finds any character not in the brackets |
[-] | finds any single character within the specified range in the brackets |
BETWEEN # AND # | Between dates |
AS | creates an alias |
JOIN | combines rows from two or more tables based on a related column between them |
INNER JOIN | returns records that have matching values in both tables |
LEFT OUTER JOIN | Returns all records from the left table and matched records from the right |
RIGHT OUTER JOIN | Returns all records from the right table and matched records from the left |
FULL JOIN | Returns all records when there is a match in either left or right table |
UNION | combines the result-set of two or more select statements, selecting distinct values by default |
UNION ALL | combines the result-set of two or more select statements, selecting all values regardless of duplicates |
BACKUP DATABASE | Creates a back up of an existing database |
BACKUP DATABASE name WITH DIFFERENTIAL | Backs up changes only in order to reduce back up time |
NOT NULL | A constraint that ensures that a column will not have null values |
UNIQUE | A constraint that ensures that all values in a column are different |
PRIMARY KEY | A constraint that uniquely identifies each record in a table. Primary keys must contain UNIQUE values |
FOREIGN KEY | A primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables. |
The table with the foreign key is called the ________ table. | Child |
The table with the primary key is called the ___________ table. | Parent |
CHECK | A constraint used to limit the value range that can be placed in a column. |
DEFAULT | Constraint used to set a default value for a column if no other value is specified. |
AUTO_INCREMENT | Automatically generates a unique number when a new record is inserted into a table. |
DATE | YYYY-MM-DD |
DATETIME | YYYY-MM-DD HH:MM:SS, doesn't include the time zone. |
TIMESTAMP | YYYY-MM-DD HH:MM:SS; includes the time zone. |
YEAR | YYYY or YY |
CREATE VIEW | A virtual table based on the result-set of an SQL statement. The fields in a view are fields from one or more real tables in the database. |
CHAR(size) | Fixed-length character string. Size is specified in parenthesis. Max 255 bytes. |
VARCHAR(size) | Variable-length character string. Max size is specified in parenthesis. |
BINARY(Size) | Equal to CHAR() but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1. |
VARBINARY(size) | Equal to VARCHAR() but stores binary byte strings. The size parameter specifies the maximum column length in bytes. |
TINYBLOB | Binary Large Objects (BLOBs). Max length 255 bytes. |
TINYTEXT | Holds a string with a maximum length of 255 characters |
TEXT(size) | Holds a string with a maximum length of 65,535 bytes |
BLOB(Size) | Binary Large Objects (BLOBs). Holds up to 65,535 bytes/ |
MEDIUMTEXT | Holds a string with a maximum length of 16,777,215 characters |
MEDIUMBLOB | For BLOBs (Binary Large Objects). Holds up to 16,777,215 bytes |
LONGTEXT | Holds a string with a maximum length of 4,294,967,295 characters |
LONGBLOB | For BLOBs (Binary Large Objects). Holds up to 4,294,967,295 bytes |
ENUM(val1, val2, val3, ...), | A string object that can only have one value, chosen from a list of possible values. You can list up to 65,535 values. If a value is inserted that is not in the list, a blank value will be inserted. The values are sorted in the order you enter them. |
SET(val1, val2, val3, ...) | A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list |
INT(size) | -2,147,483,648 to 2,147,483,647 normal. 0 to 4,294,967,295 UNSIGNED*. The maximum number of digits may be specified in parenthesis |
FLOAT(size, d) | A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter. SCIENCE. |
DECIMAL(size, d) | An exact fixed-point number. BUSINESS. |