Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

mysql

memorize.ai (lvl 286)

(0)

100 cards

()

Section 1

Preview this deck

create database <name>

Front

1 / 100

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 14, 2020

Cards (100)

Section 1

(50 cards)

create database <name>

Front

create database <name>;

Back

insert into <tablename> (col list); values (list of values);

Front

impt to specify the column list. they will match up, not dependent on order in the table which may change.

Back

fully qualified column name. table.colname

Front

use if working with more than one table in select statement

Back

SUBSTRING(str,pos), SUBSTR(str FROM pos), SUBSTRING(str,pos,len), SUBSTR(str FROM pos FOR len)

Front

starts at the position, can use substr(), goes for length a negative number means start from last char and move left

Back

create view <viewname> (col_list)

Front

to explicitly name the cols, they must be the same as what are in the select statement

Back

select is used with functions and calculations

Front

without the where statement

Back

use <database name>;

Front

Back

select distinct refers to the whole line, not just the adjacent word

Front

Back

where

Front

row level filtering .. followed by operation

Back

foreign key: create table ( foreign key yade references othertable.yadeya);

Front

Back

select trim( concat()) rtrim() ltrim()

Front

gets rid of white space

Back

select 'X' from table

Front

Back

to update multiple cols update <table> set <col=value>, <col=value>, ... where <condition>

Front

can use subqueries

Back

delete .. where ...

Front

delete only takes rows, does not delete the table itself

Back

null and empty

Front

'' is empty, it is not NULL. so if the column is NOT NULL, '' will work

Back

user defined variable

Front

set @i = 2; select @i = @i += 1 ...

Back

inner join / equi join

Front

these are equivalent. where tab1.var = tab2.var / from tab1 INNER JOIN tab2 ON tab1.var = tab2.var

Back

calculated field

Front

Back

order by

Front

output sort order. Optional but should always include it when using a group by statement. Last statement

Back

count()

Front

the number of rows in a column Column rows with NULL values in them are ignored by the COUNT(<col_name>) function if a column name is specified, but not if the asterisk (*) is used.

Back

subqueries

Front

can return a value, a row, a col or a table, must be in parentheses, can nest arbitrarily deep, but performance worsens. will use joins

Back

update <table> set <col> where <condition>;

Front

update can be one record, or the whole data set. If there is no where statement, the whole data set is changed

Back

joins

Front

select cols for output from tables where <condition>

Back

subquery can be used as alternative to GROUP BY uses correlated subqueries when there is an aggregate

Front

Back

to delete cols use UPDATE SET=NULL

Front

Back

insert a partial column

Front

if null is allowed, or default value is set

Back

order by

Front

a col, should after the where clause

Back

drop database <name>;

Front

Back

rename table <current_name> TO <new_name>

Front

Back

copy info from one table(s) into another SELECT * INTO (col list) SELECT (col list) from <table>

Front

import from other tables insert (list of cols) select from

Back

copy a table create table <name> select * from <existingTable>;

Front

export to a new table or can select specific cols rather than *

Back

select concat( fields and strings)

Front

to create a calculated field

Back

group by: Every selected column expression must be in the group by

Front

but you can have a calculated field, except some versions of mysql allow related fields...

Back

DEFAULT <value>

Front

used in table creation, often with time or date eg, current_date(). Often used instead of NULL

Back

cross join, cartesian product

Front

each row of table 1 with every row of table 2, the cross product

Back

limit

Front

number of rows include

Back

having

Front

group level filtering. Like where statement but for a group rather than individual. same commands as where. Where filters before grouping, Having filters after grouping. Having replaces "where",. only use it with group by. The group by statement comes before the having statement.

Back

group by

Front

Use only when calculating aggregates by group. Placement after where and before order by. EVERY SELECTED COL NAME MUST BE IN THE GROUP BY STATEMENT

Back

natural join

Front

don't use it, can have problems. it doesn't return duplicate columns from difft tables. The syntax has no qualifier, no 'on' or 'user'

Back

create view <view name> as select from <name> where <>

Front

creates a view

Back

select;

Front

cols or expressions to be returned

Back

must use WHERE with delete and update or will do it to the whole table. use select first to try it

Front

Back

alter table <table>

Front

shouldn't need to be used. eg alter table mytab add NewCol INTEGER; also possible change or drop col, add constraints or keys

Back

as <col name> follows calculated field

Front

alias, used to give name to calculated fields, and tables to rename a col with a problem or make it clearer or shorter, keep to single word sometimes called derived column

Back

truncate table to delete all rows

Front

Back

use primary key and foreign keys

Front

referential integrity

Back

create view (col name list)

Front

the col name list is for the new output

Back

drop table <name>

Front

deletes table

Back

table alias

Front

once you use it, you must use it throughout, you can't have some with complete names and some with the alias, will get an error

Back

aggregate functions- sum, max, min, avg, count

Front

return a single value, operate on set of rows sum, max, min, avg of a col

Back

Section 2

(50 cards)

multiiple joins:There are several ways to write a query with several joins. There is the chained approach in which each additional table is just added at the end. This is the syntax used most often. vs. embedded joins - what's the difference other than the syntax?

Front

Back

if you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

Front

Back

aggregate: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Front

select * from staff where salary > (select avg(salary) from staff); select deptid,COUNT() as TotalCount from staff group by deptid having count() > 2

Back

multiple joins with group by group by needs to be at the end of all or have an error

Front

Back

Alias Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.

Front

Back

aggregate function returns one row

Front

Back

Alias You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses.

Front

Back

The problem is that you cannot use the b inside the select of the where clause.

Front

select itemID from (select itemID from Items where numBids > 0) as b where currently = (select max(currently) from b);

Back

can use several aggregates in a select (or where)

Front

select max(), min() from table

Back

The ON clause defines the relationship between the tables. The WHERE clause describes which rows you are interested in. Many times you can swap them and still get the same result, however this is not always the case with a left outer join. If the ON clause fails you still get a row with columns from the left table but with nulls in the columns from the right table. If the WHERE clause fails you won't get that row at all.

Front

Back

select name, continent, population from world where continent not in ( select continent from (select continent, population from world group by continent, population having population > 25000000) as t

Front

Back

GROUP BY

Front

MySQL extends the use of GROUP BY so that you can use nonaggregated columns or calculations in the SELECT list that do not appear in the GROUP BY clause. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. For example, you do not need to group on customer.name in the following query In standard SQL, you would have to add customer.name to the GROUP BY clause. In MySQL, the name is redundant

Back

subquery predicates - Both set membership (IN) and quantified (SOME, ANY, ALL) predicates per- form a comparison with a value expression—usually a column from the source you specify in the FROM clause of your outer query.

Front

predicates where value expression (<>, <,>,=) some,any, all(select...)

Back

embedding join within join

Front

select from ((table join table on search statement) join table on search) join table on search

Back

use subquery for filtering

Front

select a, b from xyz where (select c, d from qtz where..)

Back

wildcards

Front

'%' any number of any chars '_' one char '[]' a set, eg '[JM]' J or M

Back

CREATE TABLE as <table name> select * from <table2 name>;

Front

Back

the 4 data manipulation operations

Front

select, , update, insert, delete

Back

union 2 conditions on the 2 results- same number of columns and similar type- means you could have them in a where statement

Front

Back

how to choose the nth row by col- omg- like soln for max

Front

select (*) from table t1 where n-1 = (select (id) from table t2 where t2.col > t1.col); n-1=0 is the max

Back

substring(str, number) -number starts from the end

Front

Back

INSERT SELECT

Front

appends to an existing table. imports data

Back

group by. Have to add all the vars on the select line in the group by. full group by

Front

Back

FROM categories LEFT JOIN user_category_subscriptions ON user_category_subscriptions.category_id = categories.category_id and user_category_subscriptions.user_id =1 See, with an inner join, putting a clause in the join or the where is equivalent. However, with an outer join, they are vastly different. As a join condition, you specify the rowset that you will be joining to the table. This means that it evaluates user_id = 1 first, and takes the subset of user_category_subscriptions with a user_id of 1 to join to all of the rows in categories. This will give you all of the rows in categories, while only the categories that this particular user has subscribed to will have any information in the user_category_subscriptions columns. Of course, all other categories will be populated with null in the user_category_subscriptions columns. Conversely, a where clause does the join, and then reduces the rowset. So, this does all of the joins and then eliminates all rows where user_id doesn't equal 1. You're left with an inefficient way to get an inner join.

Front

Back

use subquery for generating a column

Front

select a, b, (select c from xyz where..) from qtz where

Back

multiple joins, put condition in the "on" statement rather than a "where" if you want it to not be the last statement

Front

Back

join USING instead of ON

Front

If the matching columns in the two tables have the same name and all you want to do is join on equal values, use the USING clause and list the column names.

Back

subquery - where Exists Sometimes it's useful to know simply that a related row EXISTS in the result set returned by a sub- query.

Front

select a from B where exists in (select..)

Back

you cannot use DISTINCT with COUNT() because COUNT() counts all rows in a table, regard- less of whether any are redundant or contain Null values. count(variable) does not include NULL value

Front

Back

union order by must be at end and applies to the select statement, this clause must appear at the very end after the last SELECT statement. The ORDER BY applies to the result of the UNION, not the last SELECT statement

Front

Back

if you want to include a column in the output that is not the result of an aggregate calculation, you must also include it in the GROUP BY clause.

Front

Back

ALL if there is a null value, will return null in some cases. Unless the statement is false, then returns false. So condition on value > 0

Front

Back

NOT IN is not an alias for <> ANY, but for <> ALL

Front

Back

select on select

Front

if multiple, use with WHERE .. IN if single result, then can use with WHERE or on same line as SELECT

Back

select on select, if multiple, can use ALL or ANY

Front

SELECT name FROM world WHERE population > ALL (SELECT population FROM world WHERE continent='Europe')

Back

REGEXP begin of phrase '^' end '$'

Front

'phrase' REGEXP 'what to match on'

Back

conditions for aliasing a col

Front

Back

Because an aggregate function returns a single value, you can use it as part of a comparison predicate in a search condition.You have to place the aggregate function within a subquery,however,and then use the subquery as part of the comparison predicate.

Front

...WHERE ContractPrice >= (SELECT AVG(ContractPrice) FROM Engagements)

Back

examples of creating expressions that do not include aggregate functions. One of the most common mistakes is to attempt to group on the expression you create in the SELECT clause rather than on the individual columns. Remember that the GROUP BY clause must refer to columns created by the FROM and WHERE clauses. It cannot use an expression you create in your SELECT clause

Front

using aliases in the group by is wrong

Back

self join: To join a table itself means that each row of the table is combined with itself and with every other row of the table.

Front

Back

UNION UNION ALL

Front

to select rows from different tables, union does not have duplicates but union all does

Back

aggregate - it doesn't make sense to select a col value and an aggregate, because the aggregate returns a single value. But if you have group by, it does make sense

Front

select name, count(dogs_owned) from dogowners select name, count(dogs_owned) from dogowners group by name

Back

round(value, number)

Front

number is number of decimals or if negative the number of places, e.g., -3 is rounding to nearest thousand

Back

You can't reference the result of an aggregate function (e.g. MAX() ) in a WHERE clause of the same query.

Front

several ways to handle it WHERE t.id = (SELECT MAX(id) FROM ... ) SELECT t.firstName , t.Lastname, t.id FROM mytable t ORDER BY t.id DESC LIMIT 1 SELECT t.firstName , t.Lastname , t.id FROM mytable t JOIN ( SELECT MAX(mx.id) AS max_id FROM mytable mx) m ON m.max_id = t.id

Back

you can use a GROUP BY clause and not include any aggregate functions in your SELECT clause? Sure you can! When you do this, you get the same effect as using the DISTINCT keyword

Front

SELECT Customers.CustCityName FROM Customers GROUP BY Customers.CustCityName

Back

You can only use aggregates for comparison in the HAVING clause: GROUP BY ... HAVING SUM(cash) > 500 can use in a select clause

Front

Back

use concat() function to concatenate

Front

Back

another way to do max (or min) order by col desc limit 1

Front

Back

SELECT * INTO <table> from <table> SELECT (col list) INTO

Front

creates a new table from existing tables, exports data

Back

embedding select

Front

select from (select from where)

Back