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>
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