diff options
| -rw-r--r-- | sql.html.markdown | 49 |
1 files changed, 48 insertions, 1 deletions
diff --git a/sql.html.markdown b/sql.html.markdown index 5c933465..19eae60f 100644 --- a/sql.html.markdown +++ b/sql.html.markdown @@ -3,6 +3,7 @@ language: SQL filename: learnsql.sql contributors: - ["Bob DuCharme", "http://bobdc.com/"] + - ["Th3G33k", "https://github.com/Th3G33k"] --- Structured Query Language (SQL) is an [ISO/IEC 9075](https://www.iso.org/standard/63555.html) standard language for creating and working with databases stored in a set of tables. Implementations usually add their own extensions to the language; [Comparison of different SQL implementations](http://troels.arvin.dk/db/rdbms/) is a good reference on product differences. @@ -15,6 +16,10 @@ Several of these sample commands assume that the [MySQL employee sample database ```sql -- Comments start with two hyphens. End each command with a semicolon. +/* +Multi-line comments +*/ + -- SQL is not case-sensitive about keywords. The sample commands here -- follow the convention of spelling them in upper-case because it makes -- it easier to distinguish them from database, table, and column names. @@ -54,7 +59,22 @@ SELECT * FROM departments WHERE dept_name LIKE 'S____'; SELECT DISTINCT title FROM titles; -- Same as above, but sorted (case-sensitive) by the title values. -SELECT DISTINCT title FROM titles ORDER BY title; +-- The order can be specified by adding ASC (ascending) or DESC (descending). +-- If omitted, it will sort in ascending order by default. +SELECT DISTINCT title FROM titles ORDER BY title ASC; + +-- Use the comparison operators (=, >, <, >=, <=, <>) and +-- the conditional keywords (AND, OR) to refine your queries. +SELECT * FROM departments WHERE dept_no = 'd001' OR dept_no = 'd002' + +-- Same as above. +SELECT * FROM departments WHERE dept_no IN ('d001', 'd0002') + +-- Opposite of the above. +SELECT * FROM departments WHERE dept_no NOT IN ('d001', 'd0002') + +-- Select in a given range. +SELECT * from departments WHERE dept_no BETWEEN 'd001' AND 'd0002' -- Show the number of rows in the departments table. SELECT COUNT(*) FROM departments; @@ -63,6 +83,24 @@ SELECT COUNT(*) FROM departments; -- have 'en' as a substring of the dept_name value. SELECT COUNT(*) FROM departments WHERE dept_name LIKE '%en%'; +-- Aggregate functions can be used, with GROUP BY, to compute a value +-- from a set of values. Most commonly used functions are: +-- MIN(), MAX(), COUNT(), SUM(), AVG(). +-- Use HAVING to filter rows by aggregated values. + +-- Retrieve the total number of employees, by department number, +-- with the condition of having more than 100 employees. +SELECT dept_no, COUNT(dept_no) FROM dept_emp GROUP BY dept_no +HAVING COUNT(dept_no) > 100 + +-- Aliases, using the optional keyword AS, can be used for column/table names. +SELECT COUNT(A.*) AS total_employees, COUNT(B.*) total_departments +FROM employees AS A, departments B + +-- Common date format is "yyyy-mm-dd". +-- However, it can vary according to the implementation, the operating system, and the session's locale. +SELECT * FROM dept_manager WHERE from_date >= '1990-01-01' + -- A JOIN of information from multiple tables: the titles table shows -- who had what job titles, by their employee numbers, from what -- date to what date. Retrieve this information, but instead of the @@ -75,6 +113,15 @@ SELECT employees.first_name, employees.last_name, FROM titles INNER JOIN employees ON employees.emp_no = titles.emp_no LIMIT 10; +-- Combine the result of multiple SELECT. +-- UNION selects distinct rows, UNION ALL selects all rows. +SELECT * FROM departments WHERE dept_no = 'd001' +UNION +SELECT * FROM departments WHERE dept_no = 'd002' + +-- SQL syntax order is: +-- SELECT _ FROM _ JOIN _ ON _ WHERE _ GROUP BY _ HAVING _ ORDER BY _ UNION + -- List all the tables in all the databases. Implementations typically provide -- their own shortcut command to do this with the database currently in use. SELECT * FROM INFORMATION_SCHEMA.TABLES |