Basics of SQL
SELECT Data
SELECT id, firstName, lastName FROM someTable WHERE email='someemail'
SELECT * FROM someTable
SELECT * FROM someTable ORDER BY id
SELECT * FROM someTable ORDER BY id DESC
SELECT tab1_attr, tab2_attr FROM table1, table2 WHERE some_tab1_attr=some_tab2_attr;
INSERT Data
INSERT INTO someTable (id, firstName, lastName) VALUES ('some value', 900, 'Jan-10-1999'); // here we can specify attributes in any order
INSERT INTO someTable VALUES ('some value', 900, 'Jan-10-1999'); // here the attributes are used in the order they are in the table.
UPDATE Data
UPDATE someTable SET age=22,name="gandhi" WHERE id=1 AND name="mahatma"
DELETE Data
DELETE FROM someTable WHERE id = 1
DELETE FROM someTable WHERE name = "someName"
CREATE Table
CREATE TABLE someTable (id integer PRIMARY KEY, Last_Name varchar(30), Essay text);
Specify Which Database To Use
use some_database_name_here;
Numeric Data Types In SQL
TINYINT
Signed Range: -128 to 127.
Unsigned Range: 0 to 255.
BOOL, BOOLEAN
A synonym for TINYINT.
Zero = false.
Non-zero = true.
SMALLINT
Signed Range: -32768 to 32767.
Unsigned Range: 0 to 65535.
(2^16 = 65536)
MEDIUMINT
Signed Range: -8388608 to 8388607.
Unsigned Range: 0 to 16777215.
(2^24 = 16777216)
INT, INTEGER
Signed Range: -2147483648 to 2147483647.
Unsigned Range: 0 to 4294967295.
(2^32 = 4294967296).
BIGINT
Signed Range: -9223372036854775808 to 9223372036854775807.
Unsigned Range: 0 to 18446744073709551615.
(2^64 = 18446744073709551616).
FLOAT
A small (single-precision) floating-point number.
Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38.
Using FLOAT might give you some unexpected problems because all calculations in MySQL are done with double precision.
DOUBLE
A normal-size (double-precision) floating-point number.
Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308.
String Data Types In SQL
CHAR
This is exactly like a VARCHAR with one important difference. With a VARCHAR, you specify the maximum # of characters. With a CHAR, you specify the exact number of characters. So if you have a char declared as CHAR(10), then whatever you put in that CHAR has to be 10 characters long.
VARCHAR
A variable-length string where you have to specify the maximum number of characters. The max length you can specify is theoretically 65,535, but it might be lower on some systems.
BLOB vs TEXT Types
BLOB values are treated as binary strings (byte strings). They have no character set, and sorting and comparison are based on the numeric values of the bytes in column values. TEXT values are treated as nonbinary strings (character strings). They have a character set, and values are sorted and compared based on the collation of the character set.
The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive fashion for BLOB values and case-insensitive fashion for TEXT values. In other words, a TEXT is a case-insensitive BLOB. No case conversion takes place during storage or retrieval.
BLOB and TEXT columns cannot have DEFAULT values.
TINYBLOB
Maximum Length: 255 bytes.
TINYTEXT
Maximum Length: 255 bytes.
BLOB
Maximum Length: 65,535 bytes.
TEXT
Maximum Length: 65,535 bytes.
MEDIUMBLOB
Maximum Length: 16,777,215 bytes.
MEDIUMTEXT
Maximum Length: 16,777,215 bytes.
LONGBLOB
Maximum Length: 4 GB.
LONGTEXT
Maximum Length: 4 GB.
Delete a Table
DROP TABLE some_tablename;
See All Tables In Your Database
SHOW TABLES;
See The Attributes Of a Particular Table
DESCRIBE some_tableName;
See All Data Currently In a Table
SELECT * FROM some_tablename;
Pattern Matching
SQL has a very simple form of pattern matching. It's format is:
s LIKE p
Where s is a string and p is a pattern. The two special characters that SQL uses are:
% sign: Matches any sequence of 0 or more characters.
_ : Matches any one character. Here's an example:
movie LIKE "Star %";
So if movie was "Star Trek", it would match this pattern.
Sorting the Output
You can sort the output in ascending or descending order on a single attribute.
To sort by ascending, use:
ORDER BY attribute_name
To sort by descending, use:
ORDER BY attribute_name DESC
Subqueries
In SQL, one query can be used in various ways to help in the evaluation of another. A query that is part of another is called a subquery. There are a number of ways subqueries can be used:
- Subqueries can return a single constant, and this constant can be compared with another value in a WHERE clause.
- Subqueries can return relations that can be used in various ways in WHERE clauses.
- Subqueries can appear in FROM clauses, followed by a tuple variable that represents the tuples in the result of the subquery.
Here's an example of a query that uses a subquery:
SELECT name
FROM MovieExec
WHERE cert_id =
(SELECT producer_id
FROM Movies
WHERE title = 'The Matrix'
};
Here's an example of a subquery in a FROM clause:
SELECT name
FROM MovieExec, (SELECT producer_id, producer_name
FROM Movies, Producers
WHERE title = someTitle AND
year = someYear
) Prod
WHERE cert_id = Prod.producer_id;
Notice how we're using a subquery and it returns an on-the-fly relation consisting of tuples with the two attributes producer_id and producer_name. On the second-to-last line we give this on-the-fly relation the alias 'Prod', and then we use that new alias on the last line.
The EXISTS Operator
EXISTS R is a condition that is true if and only if R is not empty.
The IN Operator
s IN R is true if and only if s is equal to one of the values in R. Likewise, s NOT IN R is true if and only if s is equal to no value in R.
The ALL And ANY Operators
s > ALL R is true if and only if s is greater than every value in R.
s > ANY R is true if and only if s is greater than at least one value in R.
Theta-Joins
To do a theta-join, we put JOIN between relation names R and S and follow them by ON and a condition.
Example:
Movies JOIN StarsIn ON movie.title = StarsIn.movietitle AND year = movieYear;
// note how we're doing dot-notation in one and not in the other. Both are legal ways to do it since the two attribute names are different...so the dot notation is not really necessary here.
Natural Joins
A natural join differs from a theta-join in that:
- The join condition is that all pairs of attributes from the two relations having a common name are equared, and there are no other conditions.
- One of each pair of equated attributes is projected out.
Example:
SELECT * FROM Movies NATURAL JOIN MovieExec;
Notice we don't have an 'ON' here, because as we said a natural join automatically joins on the attributes having the same name, as long as those attributes have the same value.
So if we're joining on an attribute called 'exec_name', that code is the same as:
SELECT *
FROM Movies, MovieExec
WHERE Movies.exec_name = MovieExec.exec_name
You can also have multiple natural joins:
SELECT *
FROM
table1 NATURAL JOIN table2
NATURAL JOIN table3
It doesn't matter what order you put the tables in. The way this works is, table1 is natural joined with table2, and the resulting table is natural joined with table3.
Eliminating Duplicates
Use the DISTINCT keyword:
SELECT DISTINCT name, age FROM People;
Note that distinct applies to the whole tuple. You can't use DISTINCT to tell SQL to eliminate duplicates of one attribute only — you need a more complex SQL query for that.
Grouping
You can group your tuples on a particular attribute. To do this, use GROUP BY:
SELECT studioName, SUM(length) FROM Movies GROUP BY studioName;
Note that once you group your tuples you can use aggregation operators. Here we're using the SUM operator on length.
While queries involving GROUP BY generally have both grouping attributes and aggregations in the SELECT clause, it is technically not necessary to have both. For example, we could write:
SELECT studioName
FROM Movies
GROUP BY studioName;
This query would group the tuples of Movies according to their studio name and then print the studio name for each group, no matter how many tuples there are with a given studio name. Thus, the above query has the same effect as
SELECT DISTINCT studioName
FROM Movies;
The Aggregation Operators - SUM, AVG, MIN, MAX, COUNT
SUM
Gives you the total sum of all the values of an attribute:
SELECT SUM(age) FROM People;
AVG
Gives you the average of all the values of an attribute:
SELECT AVG(age) FROM People;
MIN/MAX
Give you the minimum and maximum values of an attribute, respectively:
SELECT MIN(age) FROM People;
SELECT MAX(age) FROM People;
COUNT
SELECT COUNT(*) FROM Movies; // This counts the number of tuples in Movies.
SELECT COUNT(movieTitle) FROM Movies; // This counts the number of values in the movieTitle column in the Movies relation. This value should be the same as the value for COUNT(*).
SELECT COUNT (DISTINCT movieTitle) FROM Movies; // This counts the number of unique movie titles in the Movies relation. This value might well be different from the values of the previous two queries.
The HAVING Clause
The HAVING Clause is like a WHERE clause for GROUP BY. You write it after the GROUP BY clause.
Example:
SELECT name
FROM Movies
WHERE producer_id > 10
GROUP BY name
HAVING MIN(year) < 1930;
You can use HAVING with any of the aggregation operators:
SUM, AVG, MIN, MAX or COUNT.
Note that, while any attribute of relations in the FROM clause may be aggregated n the HAVING clause, only those attributes that are in the GROUP BY list may appear unaggregated in the HAVING clause. This same rule also applies to the select clause. For example, if you have a query like this:
SELECT title
FROM Movies
GROUP BY title
HAVING dir = "coen"
This won't work! And it's because we're using the 'dir' attribute unaggregated in HAVING, but it doesn't appear in the GROUP BY clause.
Foreign Keys
What Foreign Keys Are
When you declare an attribute a foreign key, you connect it to the primary key of another table. If an attribute is a foreign key, it means that whatever value is put in it should match one of the values of the primary key it's connected to.
For example, if you have a table Movies with an attribute director. You have another table Directors with a primary key director_name. Directors has three tuples and the three values of director_name are:
{"Coen Brothers","Andrei Tarkovsky","Quentin Tarantino"}.
Now you declare the Movies.director attribute a foreign key and tie it to Directors.director_name. That means Movies.director can only have three values; "Coen Brothers","Andrei Tarkovsky", and "Quentin Tarantino".
How To Declare An Attribute As a Foreign Key
There are two ways to declare a foreign key:
1. If the foreign key is a single attribute we may follow its name and type by a declaration that it "references" some attribute (which must be a key – primary or unique) of some table. The form of the declaration is:
REFERENCES <table> (<attribute>)
For example:
CREATE TABLE Movies(
director VARCHAR(20) REFERENCES Directors(director_name)
);
MySQL does not recognize this format. Here's a format MySQL does recognize.
2. Alternatively, we may put the declaration at the end of the CREATE TABLE statement:
FOREIGN KEY (<attributes>) REFERENCES <table>(<attributes>)
CREATE TABLE Movies (
# other fields created here
FOREIGN KEY (director) REFERENCES Directors(director_name)
);
This is the only format MySQL recognizes for foreign key constraints.
Both the referenced and the referencing tables must be using storage engine InnoDB.
Here's how to specify which engine to use in your CREATE TABLE statement:
CREATE TABLE SomeTable(
id INT PRIMARY KEY,
name VARCHAR(20)
)ENGINE = INNODB;
Make sure you also read the section on maintaining referential integrity with foreign-key constraints.
Maintaining Referential Integrity With Foreign Keys
The Problem
There are two problems we could have when we have a foreign key. Let's take our example above, where we have a table Movies with a foreign key director that's tied to Directors.director_name.
What if:
1) (DELETE) We delete a director where the director_name appears in Movies.director?
2) (UPDATE) We modify a director_name that appears in Movies.director?
Presumably, now we're screwed because the foreign key Movies.director no longer matches any values of Movies.director_name. What does SQL do in this case? It depends what you tell it.
The Three Solutions
1. The Default Policy (Reject Violating Modifications):
When you try to modify Directors in either of the above ways, SQL doesn't let you.
2. The Cascade Policy:
When you make a change to Directors, the same change is echoed in Movies.
So if a director_name value was originally "Coen Brothers" and you change it to "Tarantino", all Movies.director entries with a value of "Coen Brothers" are changed to "Tarantino" automatically.
If you delete the Directors tuple with director_name = "Coen Brothers", then all Movies tuples where director = "Coen Brothers" are deleted too.
3. The Set-Null Policy:
When you make a change to a director_name, the corresponding entries in Movies.director are all changed to NULL.
Now I know what you're thinking — you want to do set-null when we do a DELETE, and a cascade when we do an UPDATE. Turns out, this is pretty common, and you can mix and match the keywords
'ON DELETE', 'ON UPDATE',
'SET NULL' and 'CASCADE'
Any way you want. So to execute what we just talked about 3 lines ago, we would say:
CREATE TABLE Movies(
director VARCHAR(20) REFERENCES Directors(director_name)
ON DELETE SET NULL
ON UPDATE CASCADE
);
The NOT NULL Constraint
If you use the NOT NULL keywords with an attribute, then that attribute can not have a NULL value.
director VARCHAR(20) NOT NULL
The CHECK Constraint
Complex constraints can be done using the CHECK constraint.
You can use CHECK on a single attribute like so:
gender CHAR(1) CHECK (gender IN ('F','M'))
social_sec INT CHECK (social_sec < 1000000000)
You can use CHECK on multiple attributes like so:
CREATE TABLE Movies (
name VARCHAR(30),
director VARCHAR(30),
gender CHAR(1),
CHECK (gender = 'F' OR director NOT LIKE 'Mr')
);
You can put anything in the CHECK clause that you can put in a WHERE clause. The constraint is checked each time something is INSERTed or UPDATEd.
Views
What Are Views
Relations that are defined with a CREATE TABLE statement actually exist in the database. That is, a SQL system stores tables in some physical organization.
There is another class of SQL relations, called (virtual) views, that do not exist physically. Rather, they are defined by an expression much like a query. Views, in turn, can be queried as if they existed physically.
Declaring Views
The format for the declaration is:
CREATE VIEW view_name AS view_definition.
Example:
CREATE VIEW ParamountMovies AS
SELECT title, year
FROM Movies
WHERE studioName = "Paramount";
Querying Views
A view can be queried exactly as if it were a stored table — same syntax and everything.
Example:
SELECT title
FROM ParamountMovies
WHERE year = 1979;
Renaming Attributes In Views
Here's an example where we rename "title" as "movieTitle" and "year" as "movieYear" for the ParamountMovies view:
CREATE VIEW ParamountMovies(movieTitle, movieYear) AS
SELECT title, year
FROM Movies
WHERE studioName = "Paramount";
Change Password For Currently Logged In User
set PASSWORD = PASSWORD("[newPassword]");
Using mysqldump To Export Data
#export a whole database
mysqldump -u[your username] -p [some database] > full_db.sql
#export one table in the database
mysqldump -u[your username] -p [some database] [some table] > some_table.sql
#export a couple of tables in the database
mysqldump -u[your username] -p [some database] [table1] [table2] > multiple_tables.sql
Outer Join
Suppose you have two tables, R and S.
There will be tuples in R that just don't join with any tuples in S...they just don't have a match with any of the tuples in S according to out condition in WHERE or ON. These tuples are called dangling tuples. Inner Joins will throw these tuples away, but Outer Joins will keep them.
Specify an OUTER JOIN like this:
SELECT drinker, favbeer
FROM Likes NATURAL LEFT OUTER JOIN Adores
This means that every tuple in Likes is going to appear at least once in the results. Even if it doesn't match any tuple in Adores, it will still show up once.
So for example, let's say you have these two tables, Likes and Adores:
And here's the query you run on it:
SELECT drinker, favBeer
FROM Likes NATURAL LEFT OUTER JOIN Adores
Here is the table you will get:
Note that we get the drinker and favBeer we asked for. Also note that there are no entries for 'Zarko' in the adores table, but since we did a LEFT OUTER JOIN he showed up in the resulting table anyway with NULLs for his favorite beers.
Indexes
Indexes are used to speed up SELECTs.
PROS
They are useful when you have big relations (lots of data) but onle a small fraction of the tuples are going to satisfy your WHERE clauses. In the absence of indexing, every tuple must be examined to produce the result. So indexing speeds things up while searching.
CONS
If you have an index, then your modifications are slowed down.Indexes also take up additional storage space on the disk and in memory.
So you should choose your indexes carefully. The best index choices are any primary keys and any unique attributes in a relation. MySQL automatically creates indexes on these.
Creating an Index
Here's how to create an index:
CREATE INDEX index_name ON SomeTable(some_attribute)
Create a New Database
CREATE DATABASE db_name;
Auto Increment
By default MySQL won't add an "AUTO_INCREMENT" to any primary keys you create. If your primary key is just something generic like "id", then you probably want auto increment. To add it, just specify "AUTO_INCREMENT" in your create query:
CREATE TABLE Images(
id MEDIUMINT PRIMARY KEY AUTO_INCREMENT
);
Transactions
Transactions in mysql work with InnoDB tables, not MyISAM (default) tables.
Transactions allow you to have queries executed automatically. Either all of the queries will be executed, or none of them will be.
The snytax is:
START TRANSACTION;
/* all queries go here */
COMMIT/ROLLBACK; /* if you choose 'commit', all the queries will actually happen. 'Rollback' cancels them.
Example:
START TRANSACTION;
INSERT INTO Movies(name, dir) VALUES ("The Matrix","Wachovskis");
INSERT INTO Movies(name, dir) VALUES ("The Shining","Kubrick");
COMMIT;
Isolation Levels in a Transaction
There are 4 isolation levels possible, arranged here in order of safest and slowest to most dangerous and fastest.
SERIALIZABLE
Suppose you have Transaction #1 with 5 queries, and Transaction #2 with 3 queries. You can't say which transaction will go first, but whichever one does will execute ALL of its queries before the other one starts. There will be no weird interweaving, where transaction #1 executes a couple queries, then transaction #2 executes a couple queries and so on.
READ COMMITTED
Now we CAN (and probably will) have weird interweaving going on. But any data that any of the queries read will be committed (i.e. the way the data was before either of the transactions started happening) data.
REPEATABLE READ
If a transaction reads data twice, then what it read the first time, will always be read again.
READ UNCOMMITTED
No constraint, even on reading data written and then removed by a rollback.
GRANTing Privileges
You can grant privileges to other users. See this page for all privileges possible.
The general snytax is:
GRANT [list of privileges] TO [list of people] [WITH GRANT OPTION (optional)];
Examples:
GRANT SELECT, INSERT TO dennis;
GRANT ALL ON mydb.mytbl TO nick;
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO someone WITH GRANT OPTION;
The WITH GRANT OPTION clause gives the user the ability to give to other users any privileges the user has at the specified privilege level. You should be careful to whom you give the GRANT OPTION privilege, because two users with different privileges may be able to combine privileges!
REVOKing Privileges
Same as GRANT, but instead of GRANT...TO, it's REVOKE...FROM.
Triggers
Triggers are only activated when certain events, specified by the programmer, occur.
Things you can specify in a trigger:
1. The action that causes the trigger to activate
1. Does the trigger activate BEFORE the action occurs, or AFTER (most of the time, you'll want BEFORE)?
3. If it's an update, the old value of the updated attribute is available as OLD.attribute_name and the new value of the updated attribute is available as NEW.attribute_name.
delimiter |
/* this trigger runs before something is inserted into table_name */
CREATE TRIGGER mytrigger BEFORE INSERT ON table_name
FOR EACH ROW BEGIN /* this 'for each' and 'end' bit is just required by mysql */
/* so here all we're saying is "hey, the value inserted for the attribute attr1
has to be at least 10, otherwise we'll make it 10.
*/
IF NEW.attr1 < 10 THEN
SET NEW.attr1 = 10;
END IF;
END; /* required */
|
delimiter ;
Notice how we had to temporarily change the delimiter. This is because we also have semi-colons inside the trigger, and if we didn't change the delimiter to a |, MySQL thinks those semi-colons are for the trigger, and then it gets freaked out.