Transcript SQL - Adelphi University

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 ( 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/mysql-create-tables.htm
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
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
Insert rows into another table
• INSERT INTO tutorials_types values ("php",
"php tutorials",10);
• INSERT INTO tutorials_types values ("db",
"database tutorials",20);
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;
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