Chapter 5

1. Chapter 5 Introduction to SQL

2. SQL • Structured Query Language • = the “programming language” for relational databases • SQL is a nonprocedural language = the user specifies what must be done (ex. create a table), but not how it is to be done. • Several SQL dialects exist, in different RDBMSs; minor differences among them.

3. SQL – Creating DB Tables • Use CREATE TABLE command for each db table, basic syntax: CREATE TABLE tablename ( column_1 data_type[constraints] , column_2 data_type[constraints], … column_kdata_type[constraints] , PRIMARY KEY (column_i[, column_j …]) ] ); • Notes: • End each SQL command with a semicolon to execute it; • A comma separates all table element (column, PK) definitions;

4. sql.sql CREATE DATABASE sitename; USE sitename; CREATE TABLE users ( … First_nameVARCHAR(20) NOT NULL, … };

5. VARCHAR • Variable length string • Size varies from record to record.

6. create_myusers.sql • CSC301\create_myusers.sql • How would you create the table in db_frank? J:\>mysql -h cscdb.nku.edu -u frank -D db_frank -p < CSC301\create_myusers.sql Enter password: ******

7. insert_myusers.sql • CSC301\insert_myusers.sql • How would you upload this to db_frank? J:\>mysql -h cscdb.nku.edu -u frank -D db_frank -p < CSC301\insert_myusers.sql Enter password: ******

8. MySQL - SHOW and DESCRIBE • After you create the tables in your database, you can view: • All tables in the currently used database with: show tables; • The structure of any of the tables in the current database with: describe table_name;

9. myusers J:\>mysql -h cscdb.nku.edu -u frank -p Enter password: ****** mysql> use db_frank; mysql> show tables; mysql> describe myusers;

10. SQL – Deleting DB Tables • Use DROP TABLE command for each db table you want to delete; basic syntax: DROP TABLE tablename; • Note: • This command deletes all the rows in the table tablename and the table tablename itself!

11. DML – Adding Table Rows • SQL requires the use of the INSERT command to enter data into a table. • INSERT command’s basic syntax: INSERT INTO tablename VALUES (value_1, value_2, … , value_n); • This version of INSERT: • adds one table row (tuple) at a time • requires a value to be specified for every column of the table; values are specified in the same order columns were defined in

12. sql.sql • INSERT INTO users … • SHA1('mypass') – 160 bit hash digest of password • NOW() – function return the current system date and time

13. INSERT • Example: insert into customers values (1, "Julie Smith", "25 Oak Street", "Airport West"); insert into orders values (NULL, 1, 49.99, "2007-04-15"); • Notes: • The row contents are delimited by parentheses • Attribute entries are separated by commas • String and date values must be quoted (‘ or “) • Numerical entries are not enclosed in any special characters

14. myusers INSERT INTO myusers VALUES (NULL, ‘Charles', ‘Frank', ‘frank@nku.edu', SHA1(‘secret'), NOW());

15. SELECT • SELECT command is used to list the contents of a table (or more tables). • The simplest form (syntax) of a SELECT query is: SELECT column_list FROM tablename; • Column_list represents one or more attributes from tablename, separated by commas

16. SELECT • Example: SELECT name, city FROM customers;  the result contains all the rows and only the two specified columns of table customers.

17. sql.sql SELECT * FROM forums;Asterisk (*) can be used as a wildcard character to list all attributes for the selected rows (when column_listis *) SELECT user_id, username FROM users;

18. Listing Table Rows • Can limit data selected by placing conditional restrictionson the rows to be included in the output → with the WHERE clause of the SELECT statement. • Syntax: SELECT column_listFROM tablename[ WHERE condition_list ] ; • Will retrieve all the rows that match the conditions specified in the optional WHERE clause • If no rows match the specified criteria  result = an empty set of tuples (not an error!) condition_list = one or more conditional expressions connected by logical operators

19. Listing Table Rows (cont) • WHERE clause example: SELECT title FROM books WHERE price > 40; • Conditional restrictions in the condition_list : column_name comparison_operator value expression comparison_operator expression expression – with columns and values (constants) as operands; • Comparisons include the usual “suspects” • =, !=, <, etc. • Also can use BETWEEN value1 AND value2 • If a data field is empty, can test with IS NULL operator • SELECT name FROM customers WHERE address IS NULL;  Finds records with no address specified

20. Listing Table Rows (cont) • Can match patterns with LIKE • Pattern can be simple characters up to RE • % matches any number of characters, _ matches 1 character • Can match multiple conditions using AND and OR • Can negate a condition using NOT • Example: SELECT title FROM books WHERE price > 40 AND author like “Thomas%”;

21. Listing Table Rows • Can sort results of query with ORDER BY: SELECT column_list FROM tablename [ORDER BY column1 [ASC | DESC], column2 [ASC | DESC] …]; • Notes: • Although ORDER BY produces a sorted output, the actual table contents are unaffected by the ORDER BY clause! • ORDER BY clause must be listed last in SELECT (except for LIMIT) • Example: • SELECT title, price, author FROM books ORDER BY price, author;  all books info sorted by price and, for same price, ordered by the author default

22. myusers mysql> select * from myusers; mysql> select email from myusers where last_name='Frank'; mysql> select * from myusers where email like '%edu';

23. Updating Table Rows • Use the UPDATE command to modify data in a table. • UPDATE command’s syntax: UPDATE tablenameSET column_1 = expression_1 [, column_2 = expression_2 …][WHERE condition_list]; • expression = a simple value (76 or ‘Florida’), or a formula (price – 10) • condition_list = one or more conditional expressions connected by logical operators (and, or, not) • If more than one attribute is to be updated per tuple, separate modifications with commas

24. Updating Table Rows (cont) • Examples: UPDATE books SET price = price * 1.1; • increase all the book prices by 10% UPDATE customers SET address = ‘250 Olsens Road’ WHERE customer_id = 4; • update a certain customer’s address – PK used to identify the customer • Notes: • The WHERE clause is optional • If a WHERE clause is not specified, all rows from the specified table will be modified.

25. myusers mysql> select * from myusers; mysql> UPDATE myusersSET email='mike@authors.com' WHERE user_id = 18; mysql> select * from myusers where user_id='18';

26. DML – Deleting Table Rows • Use the DELETE command to delete data from a table. • DELETE command’s syntax: DELETE FROM tablename [WHERE condition_list ]; • Notes: • WHERE clause is optional • If a WHERE clause is not specified, all rows from the specified table will be deleted

27. Deleting Table Rows (cont) • Examples: DELETE FROM customers WHERE customer_id = 4; • delete data about a customer who didn’t place orders for a long time DELETE FROM books WHERE price < 4; • delete all cheap books (none, one, or more tuples can satisfy the condition) DELETE FROM books; • delete all books; table is not deleted, but remains empty

28. myusers mysql> DELETE FROM myusersWHERE user_id = 8 LIMIT 1; mysql> select * from users limit 10;