1 / 28

Chapter 5

Chapter 5. Introduction to SQL. 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.

ossie
Download Presentation

Chapter 5

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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;

More Related