SHARE
TWEET

Untitled

a guest Jun 17th, 2019 129 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. # COIS-3400H: Database Management
  2. ## Lecture 2
  3.  
  4. ### Recap of Last Lecture
  5. - Table names are unique within a DB
  6. - Column names are unique within a table
  7. - Columns are typed
  8. - The result from a query will **always** be a table (even if it is only 1 row
  9.   long)
  10. - Your code does not interact with a database:
  11.   - It will either interact over a server (TCP/IP);
  12.   - Or it will run locally, through a DB Library
  13.   - These "adapters" are what interact with the database itself
  14.   - *Be efficient!* You don't want to use tons of memory on a local system, or
  15.     use tons of data over a network to get a result back.
  16.  
  17. ### XAMPP
  18. - Open source x-platform web server solution
  19. - Framework from *Apache Friends* that helps developers test their databases,
  20.   etc
  21. - XAMPP allows us to avoid using Loki for development purposes
  22. - XAMPP lets us use
  23.   - Apache
  24.   - MySQL
  25.   - FileZilla
  26.   - Mercury
  27.   - TomCat
  28. - We only care about the *Apache* and *MySQL* services
  29. - There may be an error with Apache and Skype both listening on port 80. Kill
  30.   Skype.
  31.  
  32. ### PHPMyAdmin
  33. - Developed in 1998
  34. - MySQL administration tool, one of the most popular ones.
  35. - Allows you to use SQL Queries, mess with settings, and gives us a GUI to
  36.   interact with
  37. - Once XAMPP is running Apache and MySQL, the service runs locally at
  38.   [localhost/phpmyadmin](localhost/phpmyadmin).
  39. - Different databases can be encoded with different standards
  40.   - Default is *latin1_swedish_ci*
  41.   - Another good pick is *utf8_general_ci* (Python likes this encoding)
  42. - We won't be using the GUI to create tables in our databases because:
  43.   - We like to have more control over our columns
  44.   - Using an SQL command offers more advanced error reporting
  45.   - It's no fun
  46.  
  47. To interact with a DB, go to the SQL tab. Make sure you have the right DB
  48. selected. If you tick the "Retain query box": this will stop PHPMyAdmin from
  49. emptying the text-area after each query.
  50.  
  51. #### Naming Conventions
  52. - Columns have Simple, short names. Try to stick to one word
  53. - Columns have `lowerCamelCase`
  54.   - Acronyms can be capitalized
  55.   - ex. name, date, carVIN, empID
  56. - Tables have `UpperCamelCase`
  57.  
  58. ### RDBMS - Relational Database Management System
  59. - A database which is stored with tables, which have columns and rows
  60. - Tables are related to one another as well
  61. - There are different types of RDBMS
  62. - Key value databases
  63. - Navigational databases
  64.  
  65. ### SQL - Structured Query Language
  66. - Lets you access and manipulate databases
  67. - ANSI (1986) and ISO (1987) standard
  68.   - It is regulated and standardized
  69. - However, there are still different "flavours" of SQL with slight differences
  70.   in notation and syntax
  71. - All flavours support things like SELECT, UPDATE, DELETE, INSERT, WHERE, etc.
  72.   in order to comply with ANSI/ISO.
  73.  
  74. ### SQL Statements
  75. - What perform the actual operations
  76. - SQL keywords are **not** case sensitive (`selet` = `SELECT`), although it is
  77.   convention to put keywords in all uppercase
  78. - Some flavours need semi-colons, some do not (PHPMyAdmin does)
  79. - Important commands
  80.   - `SELECT` - Extracts information from a DB (perhaps the most important)
  81.   - `UPDATE`
  82.   - `DELETE`
  83.   - `INSERT INTO` - Insert into the database
  84.   - `CREATE DATABASE`
  85.   - `ALTER DATABASE`
  86.   - `CREATE TABLE`
  87.   - `ALTER TABLE`
  88.   - `DROP TABLE` - Deletes a table
  89.   - `CREATE INDEX` - Creates an index (search_key)
  90.   - `DROP INDEX` - Deletes an index
  91. - Every SQL query returns as a Result-Set, which is a table in and of itself
  92. - In SQL, `*` is called a "Kleene Star"
  93.  
  94. #### `SELECT` syntax:
  95. - `SELECT DISTINCT` can be used to select only unique values
  96. ```sql
  97. SELECT column1, column2, ... FROM table_name;
  98. SELECT DISTINCT column1, column2, ... FROM table_name;
  99. ```
  100. - We use the Primary-Key (column) to ensure uniqueness.
  101.   - i.e., for people, we could use their SIN, since we know it will always be
  102.     unique
  103.  
  104. #### `WHERE` clause
  105. - Basically an `if` statement
  106. - Filters on a specified condition, rather than uniqueness
  107. - ex.
  108. ```sql
  109. SELECT column1, column2 FROM table_name WHERE condition;
  110. ```
  111. - Operators:
  112.   - `=` - Equal
  113.   - `<>` - Not equal (could be `!=` in other flavours)
  114.   - `<`
  115.   - `>`
  116.   - `<=`
  117.   - `>=`
  118. - Example:
  119.   ```sql
  120.   SELECT * FROM employees where name = Alaadin;
  121.   -- Corrected, this is:
  122.   SELECT * FROM employees WHERE name = 'Alaadin';
  123.   ```
  124.   - `where` -> `WHERE`, follow conventions
  125.   - Strings should be in quotes
  126. - `WHERE` can use `AND` and `OR` and `NOT`
  127. - You can use brackets when compounding statements, but we try not to use
  128.   complicated statements
  129.  
  130. #### `ORDER BY` Keyword
  131. - Sorts the result-set in ascending or descending order
  132. - Use `ASC` or `DESC`
  133. ```sql
  134. SELECT c1, c2 FROM table1 ORDER BY c1, c2, ASC;
  135. ```
  136.  
  137. #### SQL `NULL` Values
  138. - A field with a `NULL` value is a field with no value
  139. - Does **not** mean "no value" -- it could mean something in some cases
  140.   - This is important when we get to the `JOIN` operator
  141. - White-space is actually data in an SQL table
  142. - We can test for `NULL` values
  143. - `IS NULL` and `IS NOT NULL`
  144. ```sql
  145. SELECT c1 FROM table1 WHERE c1 IS NULL;
  146. SELECT c1, c2 FROM table1 WHERE c2 IS NOT NULL;
  147. ```
  148. - This is useful to check integrity of data
  149.  
  150. #### `UPDATE` Statement
  151. - Used to modify existing data records in a table
  152. ```sql
  153. UPDATE table1 SET c1 = data WHERE condition;
  154. ```
  155. - Not using a `WHERE` condition will be catastrophic!
  156.  
  157. #### SQL `TOP`, `LIMIT`, or `ROWNUM` Clause
  158. - `SELECT TOP` is used to specify the number of records to return
  159.   - Useful with large tables of thousands of records
  160.   - Lets you only see the top ten
  161. - `LIMIT` and `ROWNUM` work similarly
  162. ```sql
  163. SELECT col1 FROM table WHERE condition LIMIT 12;
  164. ```
  165.  
  166. #### `MIN()` and `MAX()` Functions
  167. - Returns the smallest and largest value of the specific column
  168. ```sql
  169. SELECT MIN(column_name) FROM table WHERE condition;
  170. SELECT MAX(column_name) FROM table WHERE condition;
  171. ```
  172.  
  173. #### `COUNT()`, `AVG()` and `SUM()`
  174. - `COUNT()` returns the number of rows that match the condition
  175. - `AVG()` gives the average value of a numeric column
  176. - `SUM()` sums numeric columns
  177.  
  178. #### `LIKE` Operator
  179. - Used in a `WHERE` clause to search for a specified pattern
  180. - Like a RegEx
  181. - `%` - Zero, one, or more character wildcard
  182. - `_` - Placeholder for a single character
  183. - Examples
  184.   ```sql
  185.   WHERE custName LIKE 'a%'; --Start with 'a'
  186.   WHERE custName LIKE '%a'; --End with 'a'
  187.   WHERE custName LIKE '%or%'; --Has 'or' anywhere in it
  188.   WHERE custName LIKE '_r%'; --Has 'r' in the second position
  189.   WHERE custName LIKE 'a_%_%'; --Starts with 'a' and is at least 3 characters
  190.   WHERE custName LIKE 'a%o'; --Starts with 'a' and ends with 'o'
  191.   ```
  192.  
  193. #### `IN` Operator
  194. - Allows you to specify multiple values in a `WHERE` clause
  195. - `IN` is shorthand for multiple `OR`s.
  196. - Example:
  197. ```sql
  198. SELECT * FROM countries WHERE name = 'Germany' OR name = 'Algeria';
  199. -- Becomes:
  200. SELECT * FROM countries WHERE name IN ('Germany', 'Algeria');
  201. ```
  202.  
  203. #### `BETWEEN` Operator
  204. - `BETWEEN` is inclusive
  205. - "45 to 100"
  206.   - English: 46, 47, ..., 99
  207.   - SQL: 45, 46, ..., 100
  208.  
  209. #### SQL Aliases
  210. - Used to give a table or column a temporary name for the duration of the query
  211. - Useful for long queries
  212. - Example:
  213. ```sql
  214. SELECT col1 AS alias FROM table;
  215. --could be handy for
  216. SELECT name AS n FROM table WHERE n in ('Germany', 'Algeria', 'Sweden');
  217. ```
  218.  
  219. #### `HAVING` Operator
  220. - Useful for replacing `AND` and `OR` in some cases
  221. ```sql
  222. SELECT c1 FROM table1 WHERE cond1 GROUP BY c1 HAVING cond2 ORDER BY c1
  223. ```
  224.  
  225. #### `EXISTS` Operators
  226. - Tests the existence of any record in a subquery
  227. - Returns true if the subquery returns one or more records
  228. - Syntax:
  229. ```sql
  230. SELECT colName
  231. FROM TableName
  232. WHERE EXISTS
  233. (SELECT colName FROM TableName WHERE condition);
  234. ```
  235. ```sql
  236. SELECT supplierName
  237. FROM Suppliers
  238. WHERE EXISTS
  239. (SELECT productName
  240.   FROM Products
  241.   WHERE supplierID = Suppliers.supplierID AND price > 20);
  242. ```
  243.  
  244. #### `INSERT INTO` Statement
  245. - Copies data from one table into another
  246. - Cannot copy in somewhere if the records already exists
  247.   - Use `UPDATE` if the record already exists
  248.  
  249. #### SQL Comments
  250. - `-- words` for single-line commends
  251. - `/* words */` for multi-line comments
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top