Transcript SQL

SQL
pepper
Why SQL
• File I/O is a great deal of code
• Optimal file organization and indexing is
critical and a great deal of code and theory
implementation
• File locking
• Security concerns
• Much more
Different SQL Servers
• Mysql (open source – no cost to use though
may cost to embed and resell outside gpl)
• Ms sql (microsoft )
• Oracle
• Sybase
• Access
• Older AS/400 – SQL machine
Structure
• SQL Server runs a service
• accepts sql commands using their version of
the standard query language
• Allows access to the data inside the SQL server
• Organized into databases
• Tables (like spreadsheets) inside databases
• Gui management interface
– Access / mysql workbench / mssql studio mgr
Your databases
• Connect with :
mysql -u yourdbuser -p yourdatabase
– The –p means the password will be entered later. You can
also put the password right after the p as in pmypassword.
No spaces and no quotes
– Your database name is your db user and is usually your
ecampus logon name
– You should also be able to connect to pepperdb
• See your tables
show tables
• See information inside your table
select * from tablename
Create a table
•
•
•
•
CREATE TABLE table_name (column_name column_type );
Plus insert PRIMARY KEY ( id ) at end
Ex:
create table tut(
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(100) NOT NULL,
type VARCHAR(40) NOT NULL,
date DATE,
PRIMARY KEY ( id ) );
Credit to : http://www.tutorialspoint.com/mysql/mysql-create-tables.htm
Create a related table
• Create table types (
type VARCHAR(40) NOT NULL,
name VARCHAR(100) NOT NULL,
manager int not null,
PRIMARY KEY ( type) );
Table picture from wikipedia
Insert a row
• INSERT INTO table_name ( field1, field2,...fieldN )
VALUES ( value1, value2,...valueN );
• Examples:
• INSERT INTO tut ( title, type, submission_date)
VALUES ("Learn PHP", "php",NOW());
• INSERT INTO tut (title, type, submission_date)
VALUES ("Learn MySQL", "db",NOW());
• INSERT INTO tut (title, type, submission_date)
VALUES ("Learn Oracle", "db",NOW());
Credit: http://www.tutorialspoint.com/mysql/mysql-insert-query.htm
Insert rows into another table
• INSERT INTO types values ("php", "php
tutorials",10);
• INSERT INTO types values ("db", "database
tutorials",20);
Query your tables
• select * from tut ;
• select * from types;
• select * from types
inner join tut on
tut.type =
types.type;
Select statement
•
•
•
•
•
•
•
Select – keyword indicating just looking
List all columns (* for all; table.column)
From – keyword indicating table names follow
Table name
Join type + next table + on + matching fields
Where – keyword indicating row selection
Column = something
Switch to another database
• Show databases
• Use <database name>
MySQL Workbench
• Mysql IDE
• Free community version
– Install with connector/J so you can connect to java
– Your server password is critical
• Models vs Database vs Server Instance
– Model forward engineer to database
– Forward engineer script can be run on panther
Java and Sql
• Need a connection driver
– Mysql : connecter /j -> jdbc
– Java:
– Add mysql connector/j jar file to your IDE
– Your code:
• Import java.sql.*
• Load the jdbc driver inside your code
– Class.forName("com.mysql.jdbc.Driver").newInstance();
• Create a connection object
– Connection conn =
DriverManager.getConnection("jdbc:mysql://localhost:3306","
root","root");
Java and SQL cont
• Make an sql statement object
– Statement st = conn.createStatement();
• Build a statement
– String aSQLst = "create database warehouse"
• Execute the statement
– st.execute(aSQLst);
Java and SQL get results
• Select statement will return a result as a result
set object
• Create the result set object
– ResultSet rs = null
• Execute your select
– rs = st.execute("select * from mytable");
• Loop through results:
while (rs.next()){
int num = rs.getInt("numColumn");
String word = rs.getString("name");
}
Summary
•
•
•
•
•
•
What is SQL
How to open a database
How to create tables
How to query tables – very, very basic
How to switch database context
Using SQL with Java