SQL

1
SQL

• pepper

2
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

3
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

4
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

5
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

6
Create a table

• CREATE TABLE table_name (column_name column_type
  )
• Plus insert PRIMARY KEY ( tutorial_id ) at end
• Ex
• create table tutorials_tbl(
• tutorial_id INT NOT NULL AUTO_INCREMENT,
• tutorial_title VARCHAR(100) NOT NULL,
• tutorial_type VARCHAR(40) NOT NULL,
• submission_date DATE,
• PRIMARY KEY ( tutorial_id ) )
• Credit to http//www.tutorialspoint.com/mysql/my
  sql-create-tables.htm

7
Create a table simpler

• CREATE TABLE table_name (column_name column_type
  )
• Plus insert PRIMARY KEY ( tutorial_id ) at end
• Ex
• create table tutorials_tbl(
• tutorial_id INT ,
• tutorial_title VARCHAR(100) ,
• tutorial_type VARCHAR(40) )
• Credit to http//www.tutorialspoint.com/mysql/my
  sql-create-tables.htm

8
Create a related table

• Create table tutorials_types (
• type VARCHAR(40) NOT NULL,
• type_name VARCHAR(100) NOT NULL,
• type_manager int not null,
• PRIMARY KEY ( type) )

Table picture from wikipedia
9
Insert a row

• INSERT INTO table_name ( field1, field2,...fieldN
  ) VALUES ( value1, value2,...valueN )
• Examples
• INSERT INTO tutorials_tbl (tutorial_title,
  tutorial_type, submission_date) VALUES ("Learn
  PHP", "php",NOW())
• INSERT INTO tutorials_tbl (tutorial_title,
  tutorial_type, submission_date) VALUES ("Learn
  MySQL", "db",NOW())
• INSERT INTO tutorials_tbl (tutorial_title,
  tutorial_type, submission_date) VALUES ("Learn
  Oracle", "db",NOW())

Credit http//www.tutorialspoint.com/mysql/mysql-
insert-query.htm
10
Insert rows into another table

• INSERT INTO tutorials_types values ("php", "php
  tutorials",10)
• INSERT INTO tutorials_types values ("db",
  "database tutorials",20)

11
Query your tables

• select from tutorials_tbl
• select from tutorials_types
• select from tutorials_types
• inner join tutorials_tbl on
• tutorials_tbl.tutorial_type
• tutorials_types.type

12
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

13
Switch to another database

• Show databases
• Use ltdatabase namegt

14
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