Section 1
Preview this deck
self vs. inner vs. outer vs. cross
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
6 years ago
Date created
Mar 1, 2020
Cards (65)
Section 1
(50 cards)
self vs. inner vs. outer vs. cross
"outer" will return nulls and "inner" won't "self" is useful when everyone has an employee id and supervisor id in the same table--> can join so we can identify the supervisor's name in each case for CROSS see http://www.mysqltutorial.org/mysql-cross-join/
SELECT DISTINCT
used to return unique values in the result set. It filters out all duplicate values. Here, the result set lists each genre in the movies table exactly once. 1. SELECT DISTINCT specifies that the statement is going to be a query that returns unique values in the specified column(s)
intersect
limit
replace into table(column) values()
insert a new row or delete old row first then insert new row
alter table/view
adding/removing columns changing column attributes add primary key change column datatype rename table/view
minus
can only use with select and froms
like
create similar tables, DBs, wildcards (% and __)
delete join
more than one delete at the same time
MERGE
does not have its own indexes; it uses indexes of the component tables instead speed up performance when joining multiple tables MySQL only allows you to perform SELECT, DELETE, UPDATE and INSERT operations on the MERGE tables.
set
change values in table, often followed by WHERE for conditions
recursive stored procedure
stored procedure that calls itself
update
cursor
iterate through a result set returned by a SELECT statement and process each row accordingly -> like a for loop with stuff inside read-only, non-scrollable and asensitive 1. declare cursor 2.declare NOT FOUND handler 3. build whatever 4. test outside of script
insert
putting VALUES into table
update join
MyISAM
optimized for compression and speed. MyISAM tables are also portable between platforms and operating systems. default storage engine
Group By operator
operator that divides the selected records into groups based on the values in the specified field.
Memory
stored in memory and use hash indexes so that they are faster than MyISAM tables. lifetime of the data of the memory tables depends on the uptime of the database server i.e. HEAP
delete
delete from ... where ...
in
insert ignore
LOOP statements
while; repeat; (loop, leave, iterate)
CASE
sub for IF CASE - WHEN - THEN - ELSE*
insert on duplicate key update
order by
comes at end of statement, can ASC or DESC
CSV
storage engine
SIGNAL statement
return an error or warning condition to the caller from a stored program provides you with control over which information for returning such as value and messageSQLSTATE. SIGNAL SQLSTATE | condition_name; SET condition_information_item_name_1 = value_1, condition_information_item_name_1 = value_2, etc;
exists
rollup
add-on to GroupBy, will add up and total
error handling
action either EXIT or CONTINUE DECLARE action HANDLER FOR condition_value statement; SELECT 'Error, duplicate key occurred'; -> what user sees
where
filtering, good place to nest statements with "in" or "="
ON DELETE CASCADE
To ensure all child tables are deleted inf the parent is deleted. This can be your FOREIGN KEY constraint.
Views
can only subquery from WHERE, not FROM can create views from three types of algorithms (merged, temptable, undefined) helps with security and complexity of queries updateable
stored functions
...
having
similar to WHERE, same placement
Archive
compresses a record when it is inserted and decompress it using the zlib library as it is read. only allow INSERT and SELECT statements does not support indexes
select
choosing columns to hold in table
IF procedure
IS NULL
a comparison operator used to check whether an attribute has a value.
and
InnoDB
fully support ACID-compliant and transactions. supports foreign keys, commit, rollback, roll-forward operations. The size of an InnoDB table can be up to 64TB -> 1/4th size of MyISAM
between
in same place we would find "and"/"or"
stored procedure
program stored in the database that is used to enforce business rules can be invoked by triggers, other stored procedures, and applications such as Java, Python, PHP. MySQL puts it into a cache and maintains its own stored procedure cache for every single connection. reduce the traffic between application and database server only run in command line with delimiters?
union
view resolution
combination of input query and the query in the view definition into one query
or
joins
connect two DBs at the hip, the hip being wherever you define the two to have overlap already (foreign keys)
RESIGNAL
must use the RESIGNAL statement within an error or warning handler, otherwise, you will get an error message saying that "RESIGNAL when handler is not active". Notice that you can use SIGNAL statement anywhere inside a stored procedure. You can omit all attributes of the RESIGNAL statement, even the SQLSTATE value
insert into... select
insert data into a table from the result of a SELECT statement.
Section 2
(15 cards)