SQL Jan 14 2019 SQL SQL is Structured Query Language SQL is a language for accessing and updating databases Some people pronounce SQL as sequel Other people insist that only ess cue ell is the only correct pronunciation SQL is an ANSI American National Standards Institute standard Just about every relational database supports SQL Most also extend it in various incompatible ways Java and SQL Although SQL is a language in its own right it can be used from within Java Here s the general outline Get and install a database program that supports SQL Install a driver that lets Java talk to your database For example MySQL Connector J import javax sql to make the JDBC API available MySQL is the most commonly used open source database Oracle is the most commonly used commercial database JDBC used to stand for Java DataBase Connectivity but no longer stands for anything Use the JDBC API to talk to your database 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 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 missing Two null values are not considered to be equal Example table People First Na me Last Nam Gender e Age Phone John Smith M 27 2 4315 Sally Jones F 27 3 1542 John White M 32 2 4315 Mary Smith F 42 5 4321 People is the name of the table Each row is a record Each cell in a column contains the same kind of information In this example no single column contains unique information there are two John s etc Primary Keys We will want to look things up in a table To do that we need a way of choosing a particular row A primary key is a column or group of columns whose values uniquely identify each row Example In the previous table no single column could be used as a primary key Multiple people had the same first name same last name same gender same age and same telephone number No two people had the same first name and last name First name and Last name could be used as a primary key It s a lot more convenient to have a single column as a primary key Integrity Tables must follow certain integrity rules No two rows may be completely identical Any column that is a primary key or part of a primary key cannot contain null values There are some other rules about arrays and repeating groups that need not concern us here DDL and DML SQL contains two kinds of languages statement types DDL is the Data Definition Language it defines the structure of tables CREATE TABLE creates a new database table ALTER TABLE alters changes a database table DROP TABLE deletes a database table DML is the Data Manipulation Language it defines and manipulates the content of tables INSERT puts new data into the database SELECT gets data from the database UPDATE updates changes data in the database DELETE removes data from the database CREATE TABLE Syntax 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 quoted The data types will be described shortly The constraints are optional Notice where there are commas and where there aren t Common data types char size varchar size Integer value max size digits number size d Variable length character string maximum of size characters number size Fixed length character string maximum of 255 characters Decimal number value maximum of size digits total with not more than d digits to the right of the decimal date A calendar date Example table creation People First Na me Last Nam Gende e r Age Phon e John Smith M 27 24315 Sally Jones F 27 31542 John White M 32 24315 CREATE TABLE People First Name VARCHAR 12 Mary Smith F 42 5Last Name VARCHAR 25 4321 Gender CHAR 1 Age NUMBER 3 Phone CHAR 6 Constraints When a table is created constraints can be put on the columns unique no repeated values in this column primary key unique and used to choose rows not null must have a value ALTER TABLE ALTER TABLE table name ADD column name datatype Adds a column to the table ALTER TABLE table name DROP COLUMN column name Removes a column and all its data from the table DROP COLUMN is not available on all SQL platforms DROP TABLE Syntax DROP TABLE table name Just deleting all the rows from a table leaves a blank table with column names and types The DROP TABLE command removes the table from the database completely 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 to be satisfied Examples SELECT First Name Last Name FROM People SELECT FROM People WHERE age 40 How SELECT works People First Na me Last Nam Gende e r Age Phon e John Smith M 27 24315 Sally Jones F 27 31542 John White M 32 2SELECT First Name Last Name FROM People 4315 WHERE Age 30 Mary Smith F 42 54321 Result John White Mary Smith Names and strings SQL keywords such as SELECT are case insensitive but are traditionally written in all uppercase letters Table names and column names may or may not be case sensitive Data values presumably are case sensitive String data must be enclosed in single quotes Conditions Less than Less than or equal Equal Not equal to works on some databases Greater than or equal Greater than LIKE 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 conditions Operators Basic arithmetic operators are defined in SQL add subtract multiply divide modulus remainder INSERT INTO Syntax INSERT INTO table name column column VALUES value value The columns are the names of columns you are putting data into and the values are that data String data must be enclosed in single quotes Numbers are not quoted You can omit the column names if you supply a value for every column UPDATE Syntax 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 DELETE DELETE FROM table name WHERE column name some value Examples DELETE FROM Person WHERE Last Name Smith DELETE FROM Person Deletes all records from the table Joins I INNER JOIN A join lets you collect information from two or more tables and present it as a
View Full Document
Unlocking...