1 / 25

SQL (Structured Query Language)

SQL (Structured Query Language). CS 157A By JIA HUANG. Definition . SQL- is a database sublanguage for querying and modifying relational databases. History.

shina
Télécharger la présentation

SQL (Structured Query Language)

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. SQL (Structured Query Language) • CS 157A • By • JIA HUANG

  2. Definition • SQL- is a database sublanguage for querying and modifying relational databases

  3. History • SQL was developed by IBM research in the mid 70’s and standardized by ANSI in 1986.

  4. Language Structure • SQL is a keyword based language. • Each statement begins with a unique keyword. • SQL statements consist of clauses which begin with a keyword. • SQL syntax is not case sensitive.

  5. There are 3 basic categories of SQL Statements: • SQL-Data Statements • SQL-Transaction Statements • SQL-Schema Statements

  6. SQL-Data Statements • SELECT -- query tables and views in the database • INSERT -- add rows to tables • UPDATE-- modify columns in table rows • DELETE -- remove rows from tables

  7. SELECT column_name (using * for all column) FROM table_name SELECT column_name FROM table_name WHERE condition Syntax

  8. Example “Customer" table SELECT LastName,FirstName FROM Customer

  9. Example “Customer" table SELECT Address FROM Customer WHERE LASTNAME=“Hanson”

  10. With the WHERE clause, the following operators can be used:

  11. Syntax INSERT INTO table_name VALUES (value1, value2,....) You can also specify the columns for which you want to insert data: INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)

  12. Example “Customer" table INSERT INTO Customer VALUES (“Peterson”, ”Kari”, “43 Story”, ”Reno”)

  13. Example “Customer" table INSERT INTO Customer (LastName, City) VALUES (“Peterson”, ”Reno”)

  14. Syntax • UPDATE table_name SET column_name = new_valueWHERE column_name = some_value

  15. Example “Customer" table UPDATE Customer SET Address = “21 Century” WHERE LastName =“Stevenson”

  16. Syntax • DELETE FROM table_name WHERE column_name = some_value You can delete all rows by: DELETE FROM table_name DELETE * FROM table_name

  17. Example “Customer" table DELETE FROM Customer WHERE LastName =“Stevenson”

  18. Example “Customer" table DELETE FROM Customer OR DELETE * FROM Customer

  19. SQL-Schema Statements • CREATE TABLE -- create tables • DROP TABLE -- drop tables • GRANT -- grant privileges on tables and views to other users • REVOKE-- revoke privileges on tables and views from other users

  20. Syntax • To create a database: CREATE DATABASE database_name To create a table in a database: CREATE TABLE table_name( column_name1 data_type,column_name2 data_type, ....... )

  21. Example • CREATE TABLE Customer  ( LastName varchar, FirstName varchar, Address varchar, Age int ) Result: “Customer" table

  22. The table below contains the most common data types in SQL:

  23. Syntax • To delete a table (the table structure, attributes, and indexes will also be deleted): • To delete a database: DROP DATABASE database_name DROP TABLE table_name

  24. Some other commands: ORDER BY AND & OR JOIN UNION ALTER GROUP BY SELECT INTO …

  25. References • http://www.w3schools.com/sql/default.asp • http://www.firstsql.com/tutor.htm • http://www.tc.umn.edu/~hause011/

More Related