1 / 52

Structured Query Language(SQL)

Structured Query Language(SQL). XU Yinqing SEEM PHD YEAR 3. Outline. SQL Statements Basic statements SQL functions SQLite: a SQL engine SQLite browser SQLite Manager: add-on for Firefox. What is SQL?. SQL stands for Structured Query Language

aspen-palmer
Download Presentation

Structured Query Language(SQL)

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. Structured Query Language(SQL) XU Yinqing SEEM PHD YEAR 3

  2. Outline • SQL Statements • Basic statements • SQL functions • SQLite: a SQL engine • SQLite browser • SQLite Manager: add-on for Firefox

  3. What is SQL? • SQL stands for Structured Query Language • SQL lets you access and manipulate databases • SQL is an ANSI (American National Standards Institute) standard

  4. What Can SQL do? • SQL can execute queries against a database • SQL can insert records in a database • SQL can update records in a database • SQL can delete records from a database • SQL can create new databases • SQL can create new tables in a database • …

  5. SQL is a Standard - BUT.... • Although SQL is an ANSI (American National Standards Institute) standard, there are different versions of the SQL language. • However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.

  6. Using SQL in Your Web Site • To build a web site that shows data from a database, you will need: • An RDBMS database program (i.e. MS Access, SQL Server, MySQL) • To use a server-side scripting language, like PHP or ASP • To use SQL to get the data you want • To use HTML / CSS

  7. RDBMS • RDBMS stands for Relational Database Management System. • RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. • The data in RDBMS is stored in database objects called tables. • A table is a collection of related data entries and it consists of columns and rows.

  8. Database Tables

  9. Note • SQL is NOT case sensitive: SELECT is the same as select • Semicolon after SQL Statements? Depend on the database system • Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

  10. Some of The Most Important SQL Commands • SELECT - extracts data from a database • UPDATE - updates data in a database • DELETE - deletes data from a database • INSERT INTO - inserts new data into a database • CREATE DATABASE - creates a new database • ALTER DATABASE - modifies a database • CREATE TABLE - creates a new table • ALTER TABLE - modifies a table • DROP TABLE - deletes a table • CREATE INDEX - creates an index (search key) • DROP INDEX - deletes an index

  11. SELECT • SQL SELECT Syntax • SELECT column_name,column_nameFROM table_name; • and • SELECT * FROM table_name;

  12. Database Tables

  13. SELECT • The following SQL statement selects the "CustomerName" and "City" columns from the "Customers" table: • SELECT CustomerName,City FROM Customers; • The following SQL statement selects all the columns from the "Customers" table: • SELECT * FROM Customers;

  14. SELECT DISTINCT • The DISTINCT keyword can be used to return only distinct (different) values. • SQL SELECT DISTINCT Syntax • SELECT DISTINCT column_name,column_nameFROM table_name; • EXAMPLE: • SELECT DISTINCT City FROM Customers;

  15. Database Tables

  16. WHERE • The WHERE clause is used to filter records. • The WHERE clause is used to extract only those records that fulfill a specified criterion. • SQL WHERE Syntax • SELECT column_name,column_nameFROM table_nameWHERE column_name operator value;

  17. Database Tables

  18. WHERE • The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table: • SELECT * FROM CustomersWHERE Country='Mexico';

  19. Operators in The WHERE Clause

  20. AND & OR • The AND operator displays a record if both the first condition AND the second condition are true. • The OR operator displays a record if either the first condition OR the second condition is true.

  21. AND & OR • AND Operator Example • SELECT * FROM CustomersWHERE Country='Germany'AND City='Berlin'; • OR Operator Example • SELECT * FROM CustomersWHERE City='Berlin'OR City='München'; • Combining AND & OR • SELECT * FROM CustomersWHERE Country='Germany'AND (City='Berlin' OR City='München');

  22. SELECT * FROM CustomersWHERE Country='Germany'AND City='Berlin'; Database Tables

  23. ORDER BY • The ORDER BY keyword is used to sort the result-set by one or more columns. • The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword. • SQL ORDER BY Syntax • SELECT column_name,column_nameFROM table_nameORDER BY column_name,column_nameASC|DESC;

  24. ORDER BY • ORDER BY Example • SELECT * FROM CustomersORDER BY Country; • ORDER BY DESC Example • SELECT * FROM CustomersORDER BY Country DESC;

  25. Database Tables

  26. INSERT INTO • The INSERT INTO statement is used to insert new records in a table. • SQL INSERT INTO Syntax • It is possible to write the INSERT INTO statement in two forms. • The first form does not specify the column names where the data will be inserted, only their values: • INSERT INTO table_nameVALUES (value1,value2,value3,...); • The second form specifies both the column names and the values to be inserted: • INSERT INTO table_name (column1,column2,column3,...)VALUES (value1,value2,value3,...);

  27. INSERT INTO • Assume we wish to insert a new row in the "Customers" table. • We can use the following SQL statement: • INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

  28. INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway'); Database Tables

  29. UPDATE • The UPDATE statement is used to update existing records in a table. • SQL UPDATE Syntax • UPDATE table_nameSET column1=value1,column2=value2,...WHERE some_column=some_value; • Assume we wish to update the customer "AlfredsFutterkiste" with a new contact person and city. • We use the following SQL statement: • UPDATE CustomersSET ContactName='Alfred Schmidt', City='Hamburg'WHERE CustomerName='AlfredsFutterkiste';

  30. UPDATE CustomersSET ContactName='Alfred Schmidt', City='Hamburg'WHERE CustomerName='AlfredsFutterkiste'; Database Tables

  31. DELETE • The DELETE statement is used to delete rows in a table. • SQL DELETE Syntax • DELETE FROM table_nameWHERE some_column=some_value; • Assume we wish to delete the customer "AlfredsFutterkiste" from the "Customers" table. • We use the following SQL statement: • DELETE FROM CustomersWHERE CustomerName='AlfredsFutterkiste' AND ContactName='Maria Anders';

  32. DELETE • It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact: DELETE FROM table_name;orDELETE * FROM table_name; • Be very careful when deleting records. You cannot undo this statement!

  33. LEFT JOIN • The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. • SQL LEFT JOIN Syntax • SELECT column_name(s)FROM table1LEFT JOIN table2ON table1.column_name=table2.column_name; • or: • SELECT column_name(s)FROM table1LEFT OUTER JOIN table2ON table1.column_name=table2.column_name;

  34. SELECT Customers.CustomerName, Orders.OrderIDFROM CustomersLEFT JOIN OrdersON Customers.CustomerID=Orders.CustomerIDORDER BY Customers.CustomerName; Demo Database Customers Orders

  35. SQL FUNCTION • MAX • MIN • GROUP BY

  36. MAX • The MAX() function returns the largest value of the selected column. • SQL MAX() Syntax • SELECT MAX(column_name) FROM table_name;

  37. Demo database SELECT MAX(Price) AS HighestPrice FROM Products;

  38. MIN • The MIN() function returns the smallest value of the selected column. • SQL MIN() Syntax • SELECT MIN(column_name) FROM table_name;

  39. Demo database SELECT MIN(Price) AS SmallestOrderPrice FROM Products;

  40. GROUP BY • The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. • SQL GROUP BY Syntax • SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name;

  41. SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM OrdersLEFT JOIN ShippersON Orders.ShipperID=Shippers.ShipperIDGROUP BY ShipperName; Demo Databases Orders Shippers

  42. SQLite: a SQL engine • SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is the one database, which is zero-configured, that means like other database you do not need to configure it in your system. • SQLite engine is not a standalone process like other databases, you can link it statically or dynamically as per your requirement with your application. The SQLite accesses its storage files directly.

  43. Why SQLite? • SQLite does not require a separate server process or system to operate.(serverless). • SQLite comes with zero-configuration, which means no setup or administration needed. • A complete SQLite database is stored in a single cross-platform disk file. • SQLite is very small and light weight, less than 400KiB fully configured or less than 250KiB with optional features omitted. • …

  44. Two interactive tools • Tools interact with SQLite • SQLite Browser • SQLite Manager • How to create tables using these two tools?

  45. SQLite browser • http://sourceforge.net/projects/sqlitebrowser/ • How to create table?

  46. File-> New Database

  47. SQLite Manager • https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/ • How to create table?

  48. Database-> New Database

More Related