MySQL Flashcards
My SQL Comments
–this is a comment #this is a non-standard SQL comment /* multi line comment */
How can you show all columns of a table named MyTable?
SELECT * FROM MyTable
SELECT
Select the following columns for display in the query
AS
Specify aliases/names for the columns SELECT Name AS Country
ORDER BY
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 can you do multi-level sorting? (First by one column, then another?)
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
WHERE
Filters results. Takes a Boolean expression and returns the row only if the expression is true. WHERE Population)
How can you return rows in a query that matches a condition?
WHERE Population<1000
How can you return rows in a column that have a value that matches a pattern?
WHERE Name LIKE ‘%island%’ WHERE Name REGEXP ‘^.[a-e].*$’ %=wildcard - = any character
How can you return rows in a column that match values in a list only?
WHERE CONTINENT IN (‘Europe’, ‘Asia’)
INSERT
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 can you insert a new row into a table?
INSERT INTO test VALUES (1,2,3)
How can you insert values from a query into a tabe?
To insert values from a query INSERT INTO test(a,b,c) SELECT id, name, description FROM item
How can you only insert into specified columns of a table?
INSERT INTO test(b,c) VALUES (2,3)
UPDATE
Change data in particular columns/rows as specified by WHERE UPDATE test SET c=NULL WHERE a=2
How can you change data in particular columns/rows of a table?
UPDATE test SET c=NULL WHERE a=2
DELETE
Deletes/removes rows from a table DELETE FROM test WHERE a=2;
How can you delete all rows in a table?
DELETE FROM test
DROP TABLE
Deletes table from database
How can you delete a table from a databse?
DROP TABLE
How can you concatenate strings in MYSQL?
Strings (in single quotes/double quotes) are separated by spaces for concatenation. ‘hello’ ‘world’
NULL
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
CREATE DATABASE
Creates a new database
How can you create a new database?
CREATE DATABASE newdatabase
How can you select/activate a database for further operations?
USE newdatabase
USE
Selects/activates a database
CREATE TABLE
Creates a new table CREATE TABLE test( id INTEGER, name VARCHAR(255), state CHAR(2) );
DESCRIBE
Shows the structure of a table DESCRIBE test
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’
DROP TABLE IF EXISTS
Drops the table if it already exists DROP TABLE IF EXISTS test
How can you show the schema/structure of a table?
DESCRIBE test
How can you show the tables inside a database and their status?
SHOW TABLE STATUS
How can you delete a table if it exists?
DROP TABLE IF EXISTS test
SHOW CREATE TABLE
Shows the create table statement for creating an identical table SHOW CREATE TABLE
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.
How can you add an index?
CREATE TABLE( id INTEGER, a VARCHAR(255), INDEX(a) );
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
DROP INDEX
Used to delete an index DROP INDEX ab ON testtable
How can you delete an index?
DROP INDEX ab ON testtable
How can you name an index?
INDEX ab (a,b)
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
NOT NULL
Constraint. Disallows NULL values in the column
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
DEFAULT
Sets the default value of a field.
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
AUTO_INCREMENT
automatically adds entries sequentially
SERIAL
Column with both AUTO_INCREMENT and PRIMARY_KEY Type of column is bigint(20) unsigned
LAST_INSERT_ID()
Function that returns the last global insert’s ID SELECT LAST_INSERT_ID();
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
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
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
How can you add columns to a table?
ALTER TABLE test ADD col VARCHAR(10)
How can you delete columns in a table?
ALTERTABLE test DROP col
How can you add a column in a particular position of the table?
ALTER TABLE test ADD col VARCHAR(10) AFTER a
How can you add a column to the beginning of a table?
ALTER TABLE test ADD col VARCHAR(10) FIRST
What are the types of MYSQL data types?
- Numeric
- Integer | TINY INT | SMALLINT | MEDIUMINT | INTEGER | BIGINT
- Fixed Point | DECIMAL(#significant digits, position of decimal point from right)
- Floating Point | FLOAT/REAL | DOUBLE
- Strings
- Character Strings | CHAR(fixedlength) | VARCHAR(maxlength)
- Binary Strings | BINARY(fixedlength) | VARBINARY(maxlength)
- Large Object Storage | TINYBLOB/TEXT | BLOB/TEXT | MEDIUMBLOB/TEXT | LONGBLOB/TEXT
- Dates and Times
- DATE | YYYY-MM-DD
- DATETIME | YYYY-MM-DD HH:MM:SS
- TIMESTAMP | YYYY-MM-DD HH:MM:SS
- TIME | HH:MM:SS or HHH:MM:SS
- YEAR | YYYY
- Lists/ Enumeration
- ENUM(list)
- SET(list)
- Boolean
Aggregate function that concatenates all values in a column.
GROUP_CONCAT(Column)
GROUP_CONCAT(Column SEPARATOR ‘/’)
GROUP_CONCAT(DISTINCT Column SEPARATOR ‘/’)
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
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.
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.
A stored function returns a value and is used int the context of an expression.
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.
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);
