SQL Database Management Guide for Beginners

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_namecolumn_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 • ResultSetrs = null • Execute your select • rs = st.execute("select * from mytable"); • Loop through results: while (rs.next()){intnum = 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

SQL Database Management Guide for Beginners