Databases with my SQL Database a structured set

Databases with my. SQL

Database a structured set of data held in a computer, especially one that is accessible in various ways. DBMS – Database Management System is a computer software application that interacts with the user, other applications, and the database itself to capture and analyze data.

Applications Examples Filtering of databases use might include: email Searching Using the internet an ATM card Shopping online

Database Structure The database is a container of data created and manipulated through a DBMS and is made up of one or more tables. Any database made up of more than one table is said to be “relational. ” This means that the tables have specific relationships to each other that allows a set of related data values to be stored and looked up efficiently.

Database Structure Table – A structured list of data of a specific type Columns – a single field in a table. Row – a specific record in a table. Schema of tables – Rules that determine the layout and properties Primary Key – Unique identifier for each record Foreign Key – Identifier that establishes relationships between tables

Tables Much effort goes into the design of tables when creating a database. There are specific rules of “normalization” (more later) that are used as guides. Uniqueness – The key to remember is that each table should contain only one type of data and that each record must be unique. For example, you would never include a list of customers and their orders in a single table as each customer may order more than one thing and each thing might be ordered by more than one customer

Primary Key Record Identifier Always Must be unique (no repeating values)for each record Usually Best placed in the first column auto-generated (ensures uniqueness) if not a ‘real’ value such as a SSN or phone number

Foreign Key Relates records in one table to records in another Simple foreign keys are just the primary keys of records in another table Compound keys are when multiple values are combined to reference another table’s primary key. We will not be covering compound keys.

Example

Rules for Foreign Keys Must be the primary key of another table When any record is deleted, all records in other tables that have this record’s primary key as a foreign key must also be deleted. Otherwise you end up with ‘dangling pointers’ which may cause the database to fail.

Relationship Schemas: 4 Types Tables can be organized by one-to-one, one-to-many, many-to-one, and many-to-many One-To-One: the relationship between two tables where both the tables should be associated with each other based on only one matching row.

One-to-One: Schema

One-to-Many Relationship One-To-Many: a relationship between two tables where a row from one table can have multiple matching rows in another table. Example – One teacher teaches may classes

One-to-Many: Schema

Many-to-One Relationship Many-To-One: the local table may have many rows that are associated with one row in another table. Example – Many people have ordered the same item.

Many-to-Many Many-to-Many: In some cases, you may need multiple instances on both sides of the relationship. For example, each order can contain multiple items. And each item can also be in multiple orders. For these relationships, we need to create an extra table

Many-to-Many: Schema

SQL Structured Query Language (“sequel”) – Designed specifically for communicating with databases. Chief advantage is that it is open and supported by virtually all DBMSs. Easy to learn Powerful

my. SQL There are many versions of SQL. While each functions largely the same, there are differences in syntax tailored to specific environments. Popular examples are PL/SQL (procedural language SQL), an extension used primarily by Oracle, j. Script used in javascript, and My. SQL LAMP – Open source application group that includes Linux, Apache, My. SQL, and Perl/PHP/Python

Queries A query is an inquiry into the database using the SELECT statement. A query is used to extract data from the database in a readable format according to the user's request. For instance, if you have an employee table, you might issue a SQL statement that returns the employee who is paid the most.

SELECT There are four keywords, or clauses, that are valuable parts of a SELECT statement. These keywords are as follows: SELECT, FROM, WHERE, ORDER BY Examples: SELECT * FROM PRODUCTS_TBL lists all of the data in all of the columns found in the table Products_Tbl SELECT PROD_DESC FROM CANDY_TBL lists all of the product descriptions (one field) in the candy table SELECT * FROM PRODUCTS_TBL WHERE COST < 5 retrieves all records with a cost less than 5 from the products table

Select Sorts Often order you will want your results in a particular SELECT PROD_DESC, PROD_ID, COST FROM PRODUCTS_TBL WHERE COST < 20 ORDER BY PROD_DESC ASC This query pulls three fields from products where cost < 20 and organizes them by description in ascending (ascii) order

Select Counts Many times you just want to find out how many records are in a table. This is accomplished using count(*): SELECT COUNT(*) FROM TABLE_NAME

Multiple Tables Often you are combining data from multiple tables. Records in the tables are connected through one or more foreign keys. Joins – method of combining tables.

umn in the "Orders" table refers to the "Customer. ID" in the "Customers" table. The relationship between the two tables above is the "Customer. ID" column. g SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables: Multiple Tables Customer. ID Customer. Name Contact. Name Country Order. ID Customer. ID Order. Date 1 Alfreds Futterkiste Maria Anders Germany 10308 2 1996 -09 -18 2 Ana Trujillo Mexico 10309 37 1996 -09 -19 3 Antonio Moreno Mexico 10310 77 1996 -09 -20 Notice that the "Customer. ID" column in the "Orders" table refers to the "Customer. ID" in the "Customers" table. The relationship between the two tables above is the "Customer. ID" column.

Joins (INNER) JOIN: Returns records that have matching values in both tables LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

Joins Example SELECT Orders. Order. ID, Customers. Customer. Name, Orders. Order. Date FROM Orders INNER JOIN Customers ON Orders. Customer. ID=Customers. Customer. ID; Order. ID Customer. Name Order. Date 10308 Ana Trujillo 9/18/1996 10365 Antonio Moreno 11/27/1996 10383 Around the Horn 12/16/1996 10355 Around the Horn 11/15/1996 10278 Berglunds snabbköp 8/12/1996

SQL INNER JOIN Keyword The INNER JOIN keyword selects records that have matching values in both tables. INNER JOIN Syntax SELECT column_name(s) FROM table 1 INNER JOIN table 2 ON table 1. column_name = table 2. column_name;

Example of 3 Table Inner Join The following SQL statement selects all orders with customer and shipper information: SELECT Orders. Order. ID, Customers. Customer. Name, Shippers. Shipper. Name FROM ((Orders INNER JOIN Customers ON Orders. Customer. ID = Customers. Customer. ID) INNER JOIN Shippers ON Orders. Shipper. ID = Shippers. Shipper. ID);

Create Database Steps While you will spend most of your time adding or looking up data in databases using queries, sometimes you will want to create databases and tables on the fly. Latter we will see how to do all of these operations from inside Java using JDBC.

Step 1: Create the Database From the My. SQL command line, enter the command CREATE DATABASE <DATABASENAME>; Replace <DATABASENAMEs> with the name of your database. It cannot include spaces. For example, to create a database of all the US states, you might enter CREATE DATABASE us_states; Note: Commands do not have to be entered in upper-case. Note: All My. SQL commands must end with "; ". If you forgot to include the semicolon, you can enter just "; " on the next line to process the previous command.

Step 2: Display All Availabel Databases Enter the command SHOW DATABASES; to list all of the databases you have stored. Besides the database you just created, you will also see other mysql databases. You can ignore these for now.

Step 3: Select A Database Once the database has been created, you will need to select it in order to begin editing it. Enter the command USE us_states; . You will see the message Database changed, letting you know that your active database is now us_states.

Step 4: Create Tables To create a table, you will need to enter all of your table formatting in the initial command. To create a table, enter the following command: CREATE TABLE states (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, state CHAR(25), population INT(9)); . This will create a table named "states" with three fields: id, state, and population. Check to see the tables is there and has the right schema: Show tables – displays a list of all tables defined in database columns from states – displays field names and datatypes of table “states”

What it Means The INT command will make the id field contain only numbers (integers). The NOT NULL command makes sure that the id field cannot be left blank. The PRIMARY KEY designates the id field as the key field in the table. The key field should be set to a field that cannot contain any duplicates. The AUTO_INCREMENT command will automatically assign increasing values into the id field, essentially automatically numbering each entry. The CHAR(characters) and INT(integers) commands designate the types of data allowed in those fields. The number next to the commands indicated how many characters or integers can fit in the field.

Step 5: Add Data to Tables Now that the table has been created, it’s time to start entering your information. Use the following command to input your first entry: INSERT INTO states (id, state, population) VALUES (NULL, 'Alabama', '4822023'); This is essentially telling the database to enter the information provided into the three corresponding fields in the table. Since the id field contains the command NOT NULL, entering NULL as the value will force it to automatically increment to 1, thanks to the AUTO_INCREMENT command.

Alter Table: You messed up. Now what? The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. It is also used to add and drop various constraints on an existing table. Add Column: ALTER TABLE table_name ADD column_name datatype; Drop Column: ALTER TABLE table_name DROP COLUMN column_name; Alter Column: ALTER TABLE table_name ALTER COLUMN column_name datatype;

Drop Database or Table Time to start over. Be careful, these can’t be undone. DROP DATABASE databasename; DROP TABLE table_name;

Data Types: String CHAR(size) - Maximum size of 255 characters. Where size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters. VARCHAR(size) - Maximum size of 255 characters. Where size is the number of characters to store. Variable-length string. TINYTEXT(size) - Maximum size of 255 characters. Where size is the number of characters to store. TEXT(size) - Maximum size of 65, 535 characters. Where size is the number of characters to store

Data Types: Numeric BIT - Signed values range from -128 to 127. Unsigned values range from 0 to 255. INT - Signed values range from -2147483648 to 2147483647. Unsigned values range from 0 to 4294967295 DECIMAL(m, d) - fixed point number where m is the total digits and d is the number of digits after the decimal (defaults to 10, 0) DOUBLE(m, d) Double precision floating point number where m is the total digits and d is the number of digits after the decimal. BOOL - Boolean data type where a value of 0 is considered to be FALSE and any other value is considered to be TRUE

Data Types: Date/Time DATE Values range from '1000 -01 -01' to '9999 -12 -31'. 'YYYY-MM-DD'. Displayed as DATETIME Values range from '1000 -01 -01 00: 00' to '9999 -12 -31 23: 59'. Displayed as 'YYYY-MM-DD HH: MM: SS'. TIMESTAMP(m) Values range from '1970 -01 -01 00: 01' UTC to '203801 -19 03: 14: 07' UTC. Displayed as 'YYYY-MM-DD HH: MM: SS'. TIME Values range from '-838: 59' to '838: 59'. Displayed as 'HH: MM: SS'. YEAR[(2|4)] Year value as 2 digits or 4 digits. Default is 4 digits.