MySQL Flashcards

1
Q

My SQL Comments

A

–this is a comment #this is a non-standard SQL comment /* multi line comment */

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

How can you show all columns of a table named MyTable?

A

SELECT * FROM MyTable

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

SELECT

A

Select the following columns for display in the query

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

AS

A

Specify aliases/names for the columns SELECT Name AS Country

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

ORDER BY

A

Sort based on a column. ORDER BY Continent To sort in ascending/descending order ORDER BY Continent ASC ORDER BY Continent DESC To sort on one criteria then another (two levels): ORDER BY Continent, Name To sort in different sort orders ORDER BY Continent DESC, Region, Name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

How can you do multi-level sorting? (First by one column, then another?)

A

To sort on one criteria then another (two levels): ORDER BY Continent, Name To sort in different sort orders ORDER BY Continent DESC, Region, Name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

WHERE

A

Filters results. Takes a Boolean expression and returns the row only if the expression is true. WHERE Population)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

How can you return rows in a query that matches a condition?

A

WHERE Population<1000

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How can you return rows in a column that have a value that matches a pattern?

A

WHERE Name LIKE ‘%island%’ WHERE Name REGEXP ‘^.[a-e].*$’ %=wildcard - = any character

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

How can you return rows in a column that match values in a list only?

A

WHERE CONTINENT IN (‘Europe’, ‘Asia’)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

INSERT

A

Insert new rows into table INSERT INTO test VALUES (1,2,3) To only insert into specified columns INSERT INTO test(b,c) VALUES (2,3) To insert values from a query INSERT INTO test(a,b,c) SELECT id, name, description FROM item

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

How can you insert a new row into a table?

A

INSERT INTO test VALUES (1,2,3)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How can you insert values from a query into a tabe?

A

To insert values from a query INSERT INTO test(a,b,c) SELECT id, name, description FROM item

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

How can you only insert into specified columns of a table?

A

INSERT INTO test(b,c) VALUES (2,3)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

UPDATE

A

Change data in particular columns/rows as specified by WHERE UPDATE test SET c=NULL WHERE a=2

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

How can you change data in particular columns/rows of a table?

A

UPDATE test SET c=NULL WHERE a=2

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

DELETE

A

Deletes/removes rows from a table DELETE FROM test WHERE a=2;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

How can you delete all rows in a table?

A

DELETE FROM test

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

DROP TABLE

A

Deletes table from database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

How can you delete a table from a databse?

A

DROP TABLE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

How can you concatenate strings in MYSQL?

A

Strings (in single quotes/double quotes) are separated by spaces for concatenation. ‘hello’ ‘world’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

NULL

A

Represents the lack of a value. Cannot be tested for by using the equal sign since it has no value. a=NULL –invalid To test for equality: a IS NULL -or- a IS NOT NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

CREATE DATABASE

A

Creates a new database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

How can you create a new database?

A

CREATE DATABASE newdatabase

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
How can you select/activate a database for further operations?
USE newdatabase
26
USE
Selects/activates a database
27
CREATE TABLE
Creates a new table CREATE TABLE test( id INTEGER, name VARCHAR(255), state CHAR(2) );
28
DESCRIBE
Shows the structure of a table DESCRIBE test
29
SHOW TABLE STATUS
Shows the tables inside the database and their status SHOW TABLE STATUS To see just the details/status of one table: SHOW TABLE STATUS LIKE 'test'
30
DROP TABLE IF EXISTS
Drops the table if it already exists DROP TABLE IF EXISTS test
31
How can you show the schema/structure of a table?
DESCRIBE test
32
How can you show the tables inside a database and their status?
SHOW TABLE STATUS
33
How can you delete a table if it exists?
DROP TABLE IF EXISTS test
34
SHOW CREATE TABLE
Shows the create table statement for creating an identical table SHOW CREATE TABLE
35
What is an index?
A data structure that optimizes searches, sequential access, insertions, and deletions. - Keeps a logical order for a set of data. - organizes indexes via B-trees.
36
How can you add an index?
CREATE TABLE( id INTEGER, a VARCHAR(255), INDEX(a) );
37
Multicolumn indexes
INDEX (a, b) A multicolumn index is an index that based on more than one column (e.g. a and b). They can be used for lookups in queries that specify values in a known range for combinations of A and B values, or for queries that specify just an A value/column (leftmost in the index). SELECT \* FROM table WHERE a=1 AND b=2 SELECT \* FROM table WHERE a=4 The index would NOT work for: SELECT \* FROM table WHERE b=2
38
DROP INDEX
Used to delete an index DROP INDEX ab ON testtable
39
How can you delete an index?
DROP INDEX ab ON testtable
40
How can you name an index?
INDEX ab (a,b)
41
What are constraints?
Constraints are used to define specific rules and behaviors for some of your columns. 1. NOT NULL 2. UNIQUE 3. PRIMARY KEY: NOT NULL + UNIQUE 4. AUTO\_INCREMENT 5. SERIAL: PRIMARY KEY + AUTO\_INCREMENT
42
NOT NULL
Constraint. Disallows NULL values in the column
43
UNIQUE
Constraint. Only unique values are allowed. An index is created (Key = UNI) to enforce the uniqueness. \*\*BUT, since NULL is not a value, adding multiple NULL values will NOT give an integrity constraint error. To prevent this, you should use UNIQUE NOT NULL
44
DEFAULT
Sets the default value of a field.
45
PRIMARY KEY
Constraint that includes NOT NULL and UNIQUE, and provides an index named "primary". - only one primary key allowed per table - does not require that the ID column uses a primary key constraint
46
AUTO\_INCREMENT
automatically adds entries sequentially
47
SERIAL
Column with both AUTO\_INCREMENT and PRIMARY\_KEY Type of column is bigint(20) unsigned
48
LAST\_INSERT\_ID()
Function that returns the last global insert's ID SELECT LAST\_INSERT\_ID();
49
How can you make values in a column rely on the available keys in another table?
FOREIGN KEY (a\_id) REFERENCES tablea(id) - adding values that do not exist in the referenced table/column will cause an error - deleting a row/key in the parent/referenced table that is depended on/used is also forbidden
50
FOREIGN KEY
Declares a column that relies on a foreign key in another table. FOREIGN KEY (c\_id) REFERENCES client(id) - adding values that do not exist in the referenced table/column will cause an error - deleting a row/key in the parent/referenced table that is depended on/used is also forbidden
51
ALTER TABLE
Alter a table (e.g. add/delete columns) after it has been defined. ALTER TABLE test ADD col VARCHAR(10) ALTER TABLE test DROP col ALTER TABLE test ADD col VARCHAR(10) AFTER a
52
How can you add columns to a table?
ALTER TABLE test ADD col VARCHAR(10)
53
How can you delete columns in a table?
ALTERTABLE test DROP col
54
How can you add a column in a particular position of the table?
ALTER TABLE test ADD col VARCHAR(10) AFTER a
55
How can you add a column to the beginning of a table?
ALTER TABLE test ADD col VARCHAR(10) FIRST
56
What are the types of MYSQL data types?
1. Numeric 1. Integer | TINY INT | SMALLINT | MEDIUMINT | INTEGER | BIGINT 2. Fixed Point | DECIMAL(#significant digits, position of decimal point from right) 3. Floating Point | FLOAT/REAL | DOUBLE 2. Strings 1. Character Strings | CHAR(fixedlength) | VARCHAR(maxlength) 2. Binary Strings | BINARY(fixedlength) | VARBINARY(maxlength) 3. Large Object Storage | TINYBLOB/TEXT | BLOB/TEXT | MEDIUMBLOB/TEXT | LONGBLOB/TEXT 3. Dates and Times 1. DATE | YYYY-MM-DD 2. DATETIME | YYYY-MM-DD HH:MM:SS 3. TIMESTAMP | YYYY-MM-DD HH:MM:SS 4. TIME | HH:MM:SS or HHH:MM:SS 5. YEAR | YYYY 4. Lists/ Enumeration 1. ENUM(list) 2. SET(list) 5. Boolean
57
Aggregate function that concatenates all values in a column.
GROUP\_CONCAT(Column) GROUP\_CONCAT(Column SEPARATOR '/') GROUP\_CONCAT(DISTINCT Column SEPARATOR '/')
58
CASE
CASE WHEN a THEN 'true' ELSE 'false' END AS boolA or CASE a WHEN 0 THEN 'false' WHEN 1 THEN 'true' END AS boolA
59
What is a trigger?
An operation that's automatically performed when a specific database event occurs. Commonly used to update a table whenever a row is inserted/updated in another table.
60
What is a stored routine?
A stored routine is a set of SQL statements that are stored on the database server and can be used by any client with permission to use them. There are two kinds of stored routines. (1) Stored functions and (2) stored procedures.
61
A stored function returns a value and is used int the context of an expression.
62
What is a stored procedure?
A stored procedure is essentially a stored series of MySQL commands/a query. It is used like a complete query and is called separetely using the call statement. ## Footnote ``` DELIMITER // CREATE PROCEDURE total\_duration(IN a VARCHAR(255), OUT d VARCHAR(255)) BEGIN SELECT tracklen(SUM(duration)) INTO d FROM track WHERE album\_id IN(SELECT id FROM album WHERE artist LIKE a) ; END // CALL total\_duration('%hendrix', @dur); ```