Presentation on theme: "06/19/15 SQL. SQL is a structured query language. Some people pronounce SQL as "sequel". Other people insist that just "ess-cue-ell" is the only right thing."—Transcription from the presentation:
1 June 19, 15 SQL
2 SQL is a structured query language Some people pronounce SQL as "sequel" Other people insist that "ess-cue-ell" is the only correct pronunciation SQL is a language for accessing and updating databases SQL is an ANSI (American National Standards Institute) Almost all relational databases support SQL. Most also extend it in various incompatible ways.
3 Java and SQL Although SQL is a language in its own right, it can be used within Java. Here's the overview: Obtain and install a database program that supports SQL. MySQL is the most widely used open source database. Oracle is the most widely used commercial database Database Install a driver that allows Java to communicate with your database. Example: MySQL Connector/J import javax.sql.*; to expose the JDBC API JDBC used to mean "Java DataBase Connectivity", but it doesn't mean anything anymore. Use the JDBC API to communicate with your database
4 Databases A database contains one or more tables. Each table has a name. A table consists of rows and columns. A row is a record: it contains information about a single entity (for example, a person). Columns have names that indicate what kind of information is stored in that column (eg "address") The information in a cell can be of different types: string, integer, floating point, date, space, etc. A zero value means that the data for that cell is missing. Two null values are not considered equal
5 People FirstNameLastGenderAgePhone JohnSmithM272-4315 SallyJonesF273-1542 JohnWhiteM322-4315 MarySmithF425-4321 Example table People is table name Each row is a record Each cell in a column contains the same type of information In this example, no single column contains unique information ( there is two "John", etc.)
6 Primary Key We want to look up things in a table. To do this, we need a way to select a specific row. A primary key is a column or group of columns whose values uniquely identify each row. Example: In the table above, no column can be used as a primary key. Several people had the same first name, last name, gender, age and phone number. No two people have the same first and last name use both first and last name as the primary key Much more convenient to have a single column as the primary key
7 Integrity tables must follow certain integrity rules: No two rows can be completely identical. A column that is a primary key or part of a primary key cannot contain null values. There are a few other rules about repeating matrices and groups that needn't concern us here.
8 DDL and DML SQL contains two types of "languages" (statement types) DDL is the data definition language; defines the structure of tables CREATE TABLE - creates a new database table ALTER TABLE - modifies (changes) a database table DROP TABLE - deletes a database table DML is the data manipulation language; define and manipulate the contents of tables INSERT - put new data into the database SELECT - fetch data from the database UPDATE - update (modify) data in the database DELETE - remove data from the database
9 CREATE TABLE Syntax: CREATE TABLE tablename (data type constraint column name, ..., data type constraint column name); Names like table name and column name are not quoted. Data types are briefly described. Constraints are optional. Notice where the commas go (and where they don't).
10 Common Data Types char( length ) fixed-length string (maximum 255 characters) varchar( length ) variable-length string (maximum length characters) number ( length ) integer value (maximum length digits) number ( size, d ) decimal value; Maximum total digit length, with no more than d digits to the right of the decimal date A calendar date
11 Example of creating a table CREATE TABLE Persons ( Name_VARCHAR(12), Last Name VARCHAR(25), Gender CHAR(1), Age NUMBER(3), Phone CHAR(6) ); People FirstNameLastNameGenderAgePhone JohnSmithM272-4315 SallyJonesF273-1542 JohnWhiteM322-4315 MarySmithF425-4321
12 Constraints When creating a table, you can define constraints on single columns - there are no repeated values in this column. The primary key - unique and used to select non-zero rows - must have a value
13 ALTER TABLE ALTER TABLE tablename ADD columnname datatype Adds a column to the table ALTER TABLE tablename DROP COLUMN columnname Removes a column (and all of its data) from the table DROP COLUMN is not available on all SQL platforms
14 DROP TABLE Syntax: DROP TABLE table name ; Simply removing all rows from a table leaves an "empty" table of column names and types. The DROP TABLE command completely removes the table from the database.
fifteen SELECT syntax: 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 table. condition is an optional condition that must be true. Examples: SELECT First Name, Last Name FROM People; SELECT * FROM Persons WHERE Age < 40;
sixteen How SELECT SELECT works First Name, Last Name OF people WHERE Age > 30; People FirstNameLastNameGenderAgePhone JohnSmithM272-4315 SallyJonesF273-1542 JohnWhiteM322-4315 MarySmithF425-4321 JohnWhite MarySmith Result:
17 Names and strings SQL keywords (such as SELECT ) are not case sensitive, but are traditionally written in uppercase. Table names and column names are case sensitive. Data values are likely to be case sensitive
18 Conditions < Less than <= Less than or equal to = Equal to <> Not equal ( != works in some databases) >= Greater than or equal to > Greater than LIKE String equality; % can be used as a wildcard... WHERE First_Name LIKE 'Jo%'; matches Joe, John, Joanna, etc. AND, OR and NOT can be used with conditions
19 Operators Basic arithmetic operators are defined in SQL: + sum - remainder * multiplication / division % modulus (remainder)
20 INSERT INTO Syntax: INSERT INTO tablename ( column, ..., column ) VALUES ( value, ..., value ); The columns are the names of the columns you are pasting the data into, and the values are that data. String data must be enclosed in single quotes. Numbers are not enclosed in quotes. You can omit column names by providing a value for each column.
21 Syntax UPDATE: UPDATE table name SET column name = new value WHERE column name = value ; Example: UPDATE Person SET Age = Age + 1 WHERE First_Name = 'John' AND Last_Name = 'Smith';
22 DELETE DELETE FROM table_name WHERE column_name = some_value ; Examples: DELETE FROM Person WHERE Last Name = 'Smith'; DELETE FROM person; Delete all table records!
23 Joins I: INNER JOIN A join allows you to collect information from two or more tables and present them as a single table. Unions require the use of primary keys. An INNER JOIN returns all rows from both tables where there is a match. Example: SELECT Employees. Name , Orders.Product FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID The result is a table containing the names of the employees and the products they ordered. Only employees who ordered products are listed
24 Joins II: LEFT JOIN A LEFT JOIN returns all matching rows from the first table, even if there are no matching rows from the second table. Example: SELECT Employees.Name, Orders.Product FROM Employees LEFT JOIN Orders ON Employees.Employee_ID=Orders. Employee_ID The result is again a table with the names of the employees and the products they ordered. All employees are listed. If an employee has not ordered a product, this cell is empty
25 Joins III: RIGHT JOIN A RIGHT JOIN returns all matching rows from the second table, even if there are no matching rows in the first table. Example: SELECT Employees.Name, Orders.Product FROM Employees RIGHT JOIN Orders ON Employees.Employee_ID=Orders. Employee_ID The result is again a table with employee names and ordered products. All employees who have ordered a product are listed. All products are listed. If a product was requested but not requested by an employee, the employee's cell remains empty.
26 MySQL MySQL is an open source database. Like many open source software, MySQL is a very robust and stable product. Like many open source software, MySQL is not well developed (easy for the end user to install and configure), and MySQL does not offer all the features of Oracle. For most jobs you don't need these features anyway, if you don't have any implementation specific features it's easy to switch from one SQL database to another http://dev.mysql.com/download/
27 JDBC JDBC allows you to communicate with databases from a Java program. To use JDBC: Install and configure a bridge that connects Java to the database. Write Java statements that connect across the bridge. Write Java statements that communicate with the database. Each SQL command is written as a string and passed as an argument to a Java method
28 JDBC example I'm importing java.io.*; import java.sql.*; import oracle.jdbc.driver.OracleDriver; public class Start { public static void main(String[] args) throws Exception { // Get the registered driver class Class.forName("oracle.jdbc.driver.OracleDriver"); // Specify database location String url="jdbc:oracle:thin:@ivy.shu.ac.uk:1521:SHU92"; connection conn = DriverManager.getConnection(url, "root", "password"); // Do the work... on the next slide } }
29 JDBC II Example // JDBC sends a statement object to the database Statement stmt = conn.createStatement(); // A ResultSet contains the results of the ResultSet query rs = stmt.executeQuery("SELECT enname, job FROM emp" ); System.out.println("The EMP table contains:"); // print results // "next()" is almost, but not quite, an iterator, while (rs.next()) { System.out.println(rs.getString("ename") + " is a " + rs.getString("Work")); } conn.close();
30 The end
Download ppt "19-Jun-15 SQL. SQL is a structured query language. Some people pronounce SQL as "sequel". Other people insist that just "ess-cue-ell" is the only right thing."