SQL (Structured Query Language)DefinitionHistoryLanguage StructureThere are 3 basic categories of SQL Statements:SQL-Data StatementsSyntaxExampleSlide 9Slide 10SyntaxSlide 12Slide 13Slide 14Slide 15Slide 16Slide 17Slide 18SQL-Schema StatementsSlide 20Slide 21Slide 22Slide 23Slide 24ReferencesSQL (Structured Query Language)SQL (Structured Query Language) CS 157ACS 157A By By JIA HUANGJIA HUANGDefinition Definition SQL- SQL- is a database sublanguage for is a database sublanguage for querying and modifying querying and modifying relational databasesrelational databasesHistoryHistorySQL was developed by IBM research SQL was developed by IBM research in the mid 70’s and standardized by in the mid 70’s and standardized by ANSI in 1986.ANSI in 1986.Language StructureLanguage StructureSQL is a keyword based language.SQL is a keyword based language.Each statement begins with a unique Each statement begins with a unique keyword.keyword.SQL statements consist of clauses SQL statements consist of clauses which begin with a keyword.which begin with a keyword.SQL syntax is not case sensitive.SQL syntax is not case sensitive.There are 3 basic categories of There are 3 basic categories of SQL Statements: SQL Statements: SQL-Data StatementsSQL-Data Statements SQL-Transaction StatementsSQL-Transaction Statements SQL-Schema StatementsSQL-Schema StatementsSQL-Data StatementsSQL-Data StatementsSELECT SELECT -- query tables and views in the -- query tables and views in the database database INSERT INSERT -- add rows to tables -- add rows to tables UPDATEUPDATE-- modify columns in table rows -- modify columns in table rows DELETE DELETE -- remove rows -- remove rows from tablesfrom tablesSyntax Syntax SELECTSELECT column_name column_name (using * for all (using * for all column)column) FROMFROM table_name table_nameSELECTSELECT column_name column_nameFROMFROM table_name table_nameWHEREWHERE condition conditionExampleExample“Customer" tableLastName FirstName Address CityHanson Olla 5354 9th ST San JoseStevenson Trove 57 Broadway Las Vegas Peterson Kari 43 Storage RenoSELECT LastName,FirstName FROM Customer LastName FirstNameHanson OllaStevenson TrovePeterson KariExampleExample“Customer" tableLastName FirstName Address CityHanson Olla 5354 9th ST San JoseStevenson Trove 57 Broadway Las Vegas Peterson Kari 43 Storage RenoSELECT Address FROM Customer WHERE LASTNAME=“Hanson”Address5354 9th STWith the WHERE clause, the following operators can be used:Operator Description= Equal<> Not equal> Greater than< Less than>= Greater than or equal<= Less than or equalBETWEENBetween an inclusive rangeLIKE Search for a patternSyntaxSyntaxINSERT INTO INSERT INTO table_nametable_name VALUESVALUES (value1, value2,....) (value1, value2,....)You can also specify the columns for which you want You can also specify the columns for which you want to insert data:to insert data:INSERT INTOINSERT INTO table_name (column1, table_name (column1, column2,...) column2,...) VALUESVALUES (value1, value2,....) (value1, value2,....)ExampleExample“Customer" tableINSERT INTO CustomerVALUES (“Peterson”, ”Kari”, “43 Story”, ”Reno”)LastName FirstName Address CityHanson Olla 5354 9th ST San JoseStevenson Trove 57 Broadway Las Vegas Peterson Kari 43 Storage RenoLastName FirstName Address CityHanson Olla 5354 9th ST San JoseStevenson Trove 57 Broadway Las VegasExampleExample“Customer" tableINSERT INTO Customer (LastName, City)VALUES (“Peterson”, ”Reno”)LastName FirstName Address CityHanson Olla 5354 9th ST San JoseStevenson Trove 57 Broadway Las Vegas Peterson RenoLastName FirstName Address CityHanson Olla 5354 9th ST San JoseStevenson Trove 57 Broadway Las VegasSyntaxSyntaxUPDATEUPDATE table_name table_name SETSET column_name = new_value column_name = new_value WHEREWHERE column_name = some_value column_name = some_valueExampleExample“Customer" tableUPDATE Customer SET Address = “21 Century”WHERE LastName =“Stevenson”LastName FirstName Address CityHanson Olla 5354 9th ST San JoseStevenson Trove 21 Century Las Vegas LastName FirstName Address CityHanson Olla 5354 9th ST San JoseStevenson Trove 57 Broadway Las VegasSyntaxSyntaxDELETE FROMDELETE FROM table_name table_name WHEREWHERE column_name = some_value column_name = some_valueYou can delete all rows by:You can delete all rows by: DELETE FROMDELETE FROM table_name table_name DELETEDELETE * * FROMFROM table_name table_nameExampleExample“Customer" tableDELETE FROM Customer WHERE LastName =“Stevenson”LastName FirstName Address CityHanson Olla 5354 9th ST San JoseLastName FirstName Address CityHanson Olla 5354 9th ST San JoseStevenson Trove 57 Broadway Las VegasExampleExample“Customer" tableDELETE FROM Customer ORDELETE * FROM CustomerLastName FirstName Address CityLastName FirstName Address CityHanson Olla 5354 9th ST San JoseStevenson Trove 57 Broadway Las VegasSQL-Schema StatementsSQL-Schema StatementsCREATE TABLECREATE TABLE -- create tables -- create tables DROP TABLEDROP TABLE -- drop tables -- drop tables GRANTGRANT -- grant privileges on tables -- grant privileges on tables and views to other usersand views to other usersREVOKEREVOKE-- revoke privileges on tables -- revoke privileges on tables and views from other users and views from other usersSyntaxSyntaxTo create a database:To create a database: CREATE DATABASECREATE DATABASE database_name database_name To create a table in a database: To create a table in a database: CREATE TABLECREATE TABLE table_name table_name ( column_name1 data_type,( column_name1 data_type, column_name2 data_type,column_name2 data_type, ....... ....... ) )ExampleExampleCREATE TABLECREATE TABLE CustomerK ( LastName CustomerK ( LastName varchar, FirstName varchar, Address varchar, varchar, FirstName varchar, Address varchar, Age int ) Age int ) Result:Result:“Customer" tableLastName FirstName Address AgeThe table below contains the most common data types in SQL:Data Type DescriptionInteger (size)int (size)smallint (size)tinyint (size)Hold integers only. The maximum number of digits are specified in parenthesis.Decimal (size,d)numeric (size,d)Hold numbers with fractions. The maximum number of digits are specified in "size". The maximum number of digits to the right of the decimal is specified in "d".Char (size) Holds a fixed length string (can contain letters, numbers, and special
View Full Document
Unlocking...