SQLSlide 2Java and SQLDatabasesExample tablePrimary KeysIntegrityDDL and DMLCREATE TABLECommon data typesExample table creationConstraintsALTER TABLEDROP TABLESELECTHow SELECT worksNames and stringsConditionsOperatorsINSERT INTOUPDATEDELETEJoins I: INNER JOINJoins II: LEFT JOINJoins III: RIGHT JOINMySQLJDBCJDBC example IJDBC example IIThe EndJan 14, 2019SQLSQLSQL is Structured Query LanguageSome people pronounce SQL as “sequel”Other people insist that only “ess-cue-ell” is the only correct pronunciationSQL is a language for accessing and updating databasesSQL is an ANSI (American National Standards Institute) standardJust about every relational database supports SQLMost also extend it in various incompatible waysJava and SQLAlthough SQL is a language in its own right, it can be used from within JavaHere’s the general outline:Get and install a database program that supports SQLI use the free open source program MySQL, but you can use some other compatible database if you wishInstall a driver that lets Java talk to your databaseFor example, MySQL Connector/Jimport javax.sql.*; to make the JDBC API availableJDBC used to stand for “Java DataBase Connectivity,” but no longer stands for anythingUse the JDBC API to talk to your databaseDatabasesA database contains one or more tablesEach table has a nameA table consists of rows and columnsA row is a record: it contains information about a single entity (such as a person)Columns have names that tell what kind of information is stored in that column (for example, “Address”)The information in a cell may be of various types: string, integer, floating point number, date, blank, etc.A value of null means the data for that cell is missingTwo null values are not considered to be equalPeopleFirst_NameLast_NameGender Age PhoneJohn Smith M 27 2-4315Sally Jones F 27 3-1542John White M 32 2-4315Mary Smith F 42 5-4321Example tablePeople is the name of the tableEach row is a recordEach cell in a column contains the same kind of informationIn this example, no single column contains unique information (there are two “John”s, etc.)Primary KeysWe will want to look things up in a tableTo do that, we need a way of choosing a particular rowA primary key is a column, or group of columns, whose values uniquely identify each rowExample: In the previous table, no single column could be used as a primary keyMultiple people had the same first name, same last name, same gender, same age, and same telephone numberNo two people had the same first name and last nameFirst_name and Last_name could be used as a primary keyIt’s a lot more convenient to have a single column as a primary keyIntegrityTables must follow certain integrity rules:No two rows may be completely identicalAny column that is a primary key, or part of a primary key, cannot contain null valuesThere are some other rules about arrays and repeating groups that need not concern us hereDDL and DMLSQL contains two kinds of “languages” (statement types)DDL is the Data Definition Language; it defines the structure of tablesCREATE TABLE -- creates a new database tableALTER TABLE -- alters (changes) a database tableDROP TABLE -- deletes a database tableDML is the Data Manipulation Language; it defines and manipulates the content of tablesINSERT -- puts new data into the databaseSELECT -- gets data from the databaseUPDATE -- updates (changes) data in the databaseDELETE -- removes data from the databaseCREATE TABLESyntax:CREATE TABLE table_name ( column_name data_type constraint, … , column_name data_type constraint );Names, such as the table_name and the column_names, are not quotedThe data_types will be described shortlyThe constraints are optionalNotice where there are commas (and where there aren’t)Common data typeschar(size)Fixed-length character string (maximum of 255 characters)varchar(size)Variable-length character string (maximum of size characters)number(size)Integer value (max size digits)number(size, d)Decimal number value; maximum of size digits total, with not more than d digits to the right of the decimaldateA calendar dateExample table creationCREATE TABLE People ( First_Name VARCHAR(12), Last_Name VARCHAR(25), Gender CHAR(1), Age NUMBER(3), Phone CHAR(6) );PeopleFirst_NameLast_NameGenderAge PhoneJohn Smith M 27 2-4315Sally Jones F 27 3-1542John White M 32 2-4315Mary Smith F 42 5-4321ConstraintsWhen a table is created, constraints can be put on the columnsunique -- no repeated values in this columnprimary key -- unique and used to choose rowsnot null -- must have a valueALTER TABLEALTER TABLE table_name ADD column_name datatypeAdds a column to the tableALTER TABLE table_name DROP COLUMN column_nameRemoves a column (and all its data) from the tableDROP COLUMN is not available on all SQL platformsDROP TABLESyntax:DROP TABLE table_name;Just deleting all the rows from a table leaves a “blank” table with column names and typesThe DROP TABLE command removes the table from the database completelySELECTSyntax:SELECT columns FROM table WHERE condition ;columns is: a comma-separated list of column names, or * to indicate “all columns” table is the name of the tablecondition is an optional condition to be satisfiedExamples:SELECT First_Name, Last_Name FROM People;SELECT * FROM People WHERE age < 40;How SELECT works SELECT First_Name, Last_Name FROM People WHERE Age > 30;PeopleFirst_NameLast_NameGenderAge PhoneJohn Smith M 27 2-4315Sally Jones F 27 3-1542John White M 32 2-4315Mary Smith F 42 5-4321John WhiteMary SmithResult:Names and stringsSQL keywords (such as SELECT) are case insensitive, but are traditionally written in all uppercase lettersTable names and column names may or may not be case sensitiveData values presumably are case sensitiveString data must be enclosed in single quotesConditions< Less than<= Less than or equal= Equal<> Not equal to ( != works on some databases)>= Greater than or equal> Greater thanLIKE String equality; % may be used as a wildcard… WHERE First_Name LIKE 'Jo%';matches Joe, John, Joanna, etc.AND, OR and NOT can be used with conditionsOperatorsBasic arithmetic operators are defined in SQL: + add - subtract *
View Full Document