SLIDE 1
Database Basics
l Remember our Database goal:
l To organize some data in a manner that makes it
Databases and PHP Creating and Using Databases in mySQL Database - - PowerPoint PPT Presentation
Databases and PHP Creating and Using Databases in mySQL Database - - PowerPoint PPT Presentation
Databases and PHP Creating and Using Databases in mySQL Database Basics l Remember our Database goal: l To organize some data in a manner that makes it easy to relate, store, and retrieve the data Database Basics l What do we need to know about
SLIDE 2
SLIDE 3
Database Basics
l What do we need to know about databases?
l How to design a database -- last lecture l How to use and update a database -- this lecture
SLIDE 4
Database Basics
Creating a Database Using/Updating a Database
SLIDE 5
Basic SQL Commands
l Structured Query Language (SQL) is an
ANSI (American National Standards Institute) standard.
l SQL is a language for creating, modifying,
and deleting database tables, records, and fields.
l Many different databases use SQL l Most databases have proprietary extensions
- f SQL.
SLIDE 6
Basic SQL Commands
l SQL allows you to:
l access a database l execute queries against a database l retrieve data from a database l Insert, Update and Delete records from a
database.
SLIDE 7
SQL Tables
l
Everything in SQL is stored in tables: Each table is identified by a name (i.e. People"). Tables contain records (rows) with data.
l
Below is an example of a table called "People": The table contains three records (one for each person) and four columns (LName, FName, Phone, and ID).
Lname Fname Phone# ID Smith John 5309 Karlsson Karl 4200 1 Wallace William 8569 2
SLIDE 8
Basic SQL DML (data manipulation language)
Command Action
CREATE
Creates a new table.
USE
Use a database
DESCRIBE
Show the fields in a table.
ALTER
Modifies the definition (structure, data types, etc.) of an existing table.
DROP
Permanently removes elements such as tables and fields.
INSERT
Adds a record to a table.
LOAD DATA
Loads records from a text file.
UPDATE
Modifies data in an existing record.
SELECT
Performs a query on a table, including mathematical functions, field comparison, pattern matching, etc.
SHOW
Print the databases (or tables or grants) available.
DELETE
Permanently removes elements from a table.
SLIDE 9
Basic SQL Commands
l Syntax
l Commands are by convention in all capital letters.
Doesnt really matter.
l Every command ends with a semicolon (;) l Table and field capitalization does matter.
SLIDE 10
Using mySQL directly (no php)
l First must log into the Linux server using ssh
- r putty.
l Then must start mySQL:
mysql -u yourAccountName -p
l Where yourAccountName is, well, your account
name
l Note that everything is lower case l The mySQL admin has given you access to a
particular database.
l You will be prompted for a password.
SLIDE 11
Using mySQL directly (no php)
l SHOW
l To see what databases you have access to type:
mysql> SHOW DATABASES; +-----------+ | Database | +-----------+ | Ithaca | | test | +-----------+ 2 rows in set (0.00 sec)
l Here, accountName has access to the Ithaca
database and a test database
SLIDE 12
Using mySQL directly (no php)
l To see what tables are in your database, type:
mysql> SHOW TABLES FROM Ithaca; +--------------------+ | Tables_in_students | +--------------------+ | student | | courses | | instructors | | students_courses | +--------------------+ 1 row in set (0.02 sec) mysql>
l
Note that the database is specified: Ithaca
l
Here, the students database has four tables named student, courses, instructors, and students_courses.
l
Caution: capitalization matters in the name of the database!
SLIDE 13
Using mySQL directly (no php)
l To see what privileges you have, type:
mysql> SHOW GRANTS FOR cs205user@localhost; +--------------------------------------------------------------------------------------+ | GRANTS for cs205@localhost | +--------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'cs205'@'localhost' IDENTIFIEDY BY PASSWORD '032c41e84373a7' | | GRANT SELECT, INSERT, DELETE, CREATE ON 'Ithaca'.* TO 'cs205user'@'localhost' | +--------------------------------------------------------------------------------------+ 2 rows in set (0.02 sec) mysql>
l
The localhost means the computer you are logged into.
l
You can also have privileges when sending commands from a remote
- machine. In this case you may see '%' or an IP address instead of
localhost
l
USAGE means no permissions.
l
The 'Ithaca'.* is the database Ithaca
SLIDE 14
Add a user
l Log in as root using the root password l CREATE USER ‘barrg’@’localhost’
IDENTIFIED BY ‘ithaca’
l GRANT ALL PRIVILEGES ON *.* to
'barrg'@'localhost’;
SLIDE 15
Modify a user
l To change a user’s password:
ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here’;
l To add privileges (e.g., ALL PRIVILEGES):
GRANT type_of_permission ON ‘database_name.table_name’.* TO ‘username’@'localhost’;
l To revoke privileges:
REVOKE type_of_permission ON ‘database_name.table_name’.* FROM ‘username’@‘localhost’
SLIDE 16
CREATE a database
l CREATE
l Allows you to create new databases and tables,
depending upon the permissions you have.
l Syntax to create a database:
CREATE DATABASE Junk;
Note! The square brackets indicate optional items. Do NOT put the brackets in your command!
SLIDE 17
Using mySQL directly (no php)
l USE.
l To automatically use a particular database for all
your work:
mysql> USE Ithaca; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with
- A
Database changed mysql>
l Now you can enter commands without specifying
the database:
mysql> SHOW TABLES;
SLIDE 18
CREATE a table
l CREATE
l Allows you to create new databases and tables,
depending upon the permissions you have.
l You specify the fields of the table (can modify
later), but not the values of the fields.
l Syntax to create a table:
CREATE TABLE [table name][(name_of_field1 field1_datatype, name_of_field2 field2_datatype , …)] [options ];
Note! The square brackets indicate optional items. Do NOT put the brackets in your command!
SLIDE 19
Basic SQL commands
l CREATE
l Example: creating the instructors table
CREATE TABLE instructors( instructorID int not null primary key, instrName varchar (30) not null );
l Notice the type l Not null means that the field is not initialized to a default value
automatically.
§
If you leave this off, then a default value will be used when a new record is created (if a value is not provided).
§
The actual default value varies depending on the type
§
Eg, ints are by default 0
You can leave the not null and primary key off of every line! not null means that field MUST have a value primary key is a unique identifier for the row. Every value must be unique.
SLIDE 20
Basic SQL commands
l CREATE
l Example: creating the instructors table
CREATE TABLE instructors( instructorID int not null primary key, instrName varchar (30) not null );
l Varchar(20) means that the field will have a varying number of
characters up to 20 max
l More information about CHAR and VARCHAR is here:
§
http://dev.mysql.com/doc/refman/5.0/en/char.html
l Information about other types is at: §
http://dev.mysql.com/doc/refman/5.0/en/data-types.html
SLIDE 21
Basic SQL commands
mysql> SHOW TABLES;
+-------------------+ | Tables_in_Ithaca | +-------------------+ | student | | courses | | instructors | | students_courses | +-------------------+
2 rows in set (0.00 sec) mysql> CREATE TABLE clubs (
- > studentID int primary key,
- > clubName varchar(20) not null
- > );
Query OK, 0 rows affected (0.00 sec) mysql> SHOW TABLES;
+-------------------+ | Tables_in_Ithaca | +-------------------+ | student | | courses | | instructors | | students_courses | | clubs | +-------------------+
3 rows in set (0.00 sec)
Create a student table with fields first, last, ID, major ID is an int, a primary key and not null first, last are varchar(20) Create a student table with fields first, last, ID, depart ID is an int, a primary key and not null first, last are varchar(20)
SLIDE 22
Basic SQL commands
l DESCRIBE
l Allows you to see the format of the fields in a table. l Example: to see the fields in the Students table:
mysql> DESCRIBE student; +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | firstname | varchar(20) | YES | | | | | lastname | varchar(20) | YES | | | | | GPA | decimal(3,2) | YES | | 0.00 | | | phone | varchar(15) | YES | | | | | major | varchar(30) | YES | | | | | dorm | varchar(20) | YES | | | | | creditsTaken | decimal(3,0) | YES | | 0 | | | studentID | decimal(6,0) | NO | | | | +--------------+--------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql>
SLIDE 23
Basic SQL commands
l SHOW
l You can see all the databases that you have access to:
SHOW DATABASES;
l You can also see the tables that are in a database:
USE Ithaca; SHOW TABLES;
SLIDE 24
Basic SQL commands
l ALTER
l Allows you to modify elements of a particular
table.
l Can add fields, change field types, delete fields. l Example: to change the field size of name:
ALTER TABLE courses CHANGE InstructorID InstructorID VARCHAR(30);
l Example: to add a column to a table:
ALTER TABLE courses ADD days varchar(5);
SLIDE 25
ALTER
l Change the student table to include GPA, an
int.
SLIDE 26
Basic SQL commands
l DROP
l Allows you to delete an entire table
DROP TABLE [table name];
l Example:
DROP TABLE Students;
l Also can use to delete specific fields:
ALTER TABLE courses DROP descript; Where courses is a table and descript is a field
SLIDE 27
Basic SQL commands
l INSERT
l Allows you to add a record to a table
INSERT INTO [table name]([name of field1] , [name
- f field2] , …) VALUES ([value of field 1] ,
[value of field 2] , … );
l Syntax:
l Must use single or double quotes around your values. l Convention is to use single quotes, but it doesnt matter.
Note! The square brackets indicate optional items. Do NOT put the brackets in your command!
SLIDE 28
Basic SQL commands
l INSERT
l Example:
mysql> INSERT INTO student (firstname, lastname, GPA, phone, major, dorm, creditsTaken)
- > VALUES ('John', 'Smith', '3.6', '274-3948', 'CS', 'Stanton', '63');
Query OK, 1 row affected (0.05 sec) mysql> SELECT * FROM student; +-----------+----------+------+----------+-------+---------+--------------+ | firstname | lastname | GPA | phone | major | dorm | creditsTaken | +-----------+----------+------+----------+-------+---------+--------------+ | John | Smith | 3.60 | 274-3948 | CS | Stanton | 63 | +-----------+----------+------+----------+-------+---------+--------------+ 1 row in set (0.00 sec) mysql>
SLIDE 29
INSERT
l Insert 2 records into the students table l and 2 records into the instructors table
SLIDE 30
Basic SQL commands
l DELETE
l Allows the deleting of records from tables :
DELETE FROM [table name] WHERE [expression];
l Example: to delete all records with instructorID of
76765 from the courses table:
DELETE FROM courses WHERE instructorID = 76765;
l If you want to delete only the record where the
instructor ID is 76765 and the course is 304-212, then use:
DELETE FROM courses WHERE instructorID = 76765 AND courseID = 304-212;
Note! The square brackets indicate optional items. Do NOT put the brackets in your command!
SLIDE 31
Basic SQL commands
l DELETE
l If you leave out the WHERE expression, you delete all
records!
DELETE FROM courses;
Deletes all the records in the courses table.
l If you want to delete a specific field from all records, use
the ALTER command:
ALTER TABLE courses DROP instructorID;
SLIDE 32
Basic SQL commands
l UPDATE
l
Modifies part of a record without replacing the entire record:
UPDATE [table name]SET [field name] = [new value] WHERE [expression];
l
Example: change name of instructor for course 304-212:
UPDATE courses SET InstructorID = 49281 WHERE courseID = 304-212;
l
Syntax:
l
If you leave out the WHERE, then all records are updated!
l
You can perform string functions and math functions on existing records and use the UPDATE command to modify their values
Note! The square brackets indicate optional items. Do NOT put the brackets in your command!
SLIDE 33
UPDATE
l Change the GPA of a student
SLIDE 34
SQL Queries
l SELECT
l This is the command you use to see whats in your database. l When talking about databases, a SELECT is called a query l Causes certain records in your table to be chosen, based on
criteria that you define:
SELECT [field names] FROM [table name] WHERE [expression] ORDER BY [fields];
l
Example: to select all the records in a table:
SELECT * FROM courses;
l
Example: to select just the entries in the courseID field of the courses table:
SELECT courseID FROM courses;
SLIDE 35
SQL Queries: WHERE
l You can use the word WHERE to limit your result sets, using
the following operators:
= equal to <> Not equal to >, <, <= GT, LT, LTE BETWEEN in a specified range LIKE matches a pattern
SELECT description FROM courses WHERE courseID = COMP 207;
returns
+-------------+ | description | +-------------+ | Game Dev | +-------------+ 1 row in set (0.00 sec)
SLIDE 36
AND, OR
l
AND and OR can also be used to construct more complicated queries:
SELECT * FROM People WHERE id > 0 AND Lname LIKE Hoch
l
The * is used as a wildcard, and will return the data in all columns
l
LIKE means only Lnames that exactly match Hoch will be returned
l
If you want Lnames that begin with Hoch, must use a wildcard:
Lname LIKE Hoch%
l
The % will match 0 or more characters, the underscore _ will match exactly 1 character.
l
To match the % or _ character, backslash them: \% or \_
SLIDE 37
AND, OR
l
AND and OR can also be used to construct more complicated queries:
mysql> SELECT * FROM courses WHERE instructorID = 343434 AND room = 'Williams 309'; +----------+-------------+--------------+------+---------+--------------+ | courseID | description | instructorID | days | time | room | +----------+-------------+--------------+------+---------+--------------+ | COMP 122 | Legos | 343434 | TR | 11:00am | Williams 309 | | COMP 123 | Legos II | 343434 | R | 2:30pm | Williams 309 | +----------+-------------+--------------+------+---------+--------------+ 2 rows in set (0.00 sec) mysql>
SLIDE 38
SQL Queries
l SELECT
l To select all records and have them returned in a
particular order, use an expression for ORDER BY.
l Example: to view courseID and course Descript
- rdered by courseID from smallest to largest:
SELECT courseID, description FROM courses ORDER BY courseID DESC;
l DESC means descending. ASC means ascending
(ASC is the default)
SLIDE 39
SQL query, ordered
mysql> SELECT courseID, description FROM courses ORDER BY courseID; +----------+-------------+ | courseID | description | +----------+-------------+ | COMP 110 | Intro | | COMP 122 | Legos | | COMP 123 | Legos II | | COMP 171 | CS I | | COMP 190 | MatLAB | | COMP 205 | Adv Web | | COMP 207 | Game Dev | | COMP 210 | Org | | COMP 310 | Op Sys | | COMP 315 | Graphics | | COMP 375 | DB | | COMP 390 | AI | +----------+-------------+ 12 rows in set (0.00 sec) mysql>
SLIDE 40
SQL Queries
l SELECT
l Can also perform mathematical and string functions
within SQL statements. To count the number of courses:
SELECT COUNT(courseID) FROM courses;
l Use the WHERE option to specify certain field values. l Example: get course ID and description for all courses
taught by the instructor with ID 76765:
SELECT courseID, descript FROM courses WHERE InstructorID = 76765;
SLIDE 41
SQL Queries
l SELECT
l Can select fields from different tables :
SELECT courses.descript, instructors.instructorName FROM courses, instructors WHERE courses.InstructorID = instructors.InstructorID;
l This query searches the instructors table and the
courses table.
l When the instructor ID in the instructors table matches the
instructor ID in the courses table
l Then the corresponding course description from the courses
table and the corresponding instructor name from the instructors table are printed.
SLIDE 42
SQL query, multiple tables
mysql> SELECT courses.description, instructors.lastname FROM courses, instructors
- > WHERE courses.instructorID = instructors.instructorID;
+-------------+------------+ | description | lastname | +-------------+------------+ | CS I | Applin | | Adv Web | Barr | | MatLAB | Erkan | | DB | Woodworth | | Graphics | Stansfield | | AI | Zollo | | Org | Daehn | | Legos | Woodworth | | Intro | Woodworth | | Game Dev | Stansfield | | Op Sys | Barr | | Legos II | Woodworth | +-------------+------------+ 12 rows in set (0.03 sec) mysql>
SLIDE 43
Complex SQL query
mysql> SELECT instructors.lastname FROM instructors, student, courses, students_courses WHERE student.studentID = 222222 AND students_courses.studentID = student.studentID AND students_courses.courseID = courses.courseID AND courses.instructorID = instructors.instructorID;
+------------+ | lastname | +------------+ | Applin | | Stansfield | | Woodworth | +------------+ 27 rows in set (0.00 sec)
Must specify which lastname since several tables have this field Must specify all tables that will be used in the query Must specify every table Notes:
- 1. Every table that you use in the SELECT or WHERE
clauses must be named in the FROM clause.
- 2. In the WHERE clause, each AND clause should
further restrict your search.
SLIDE 44
Complex SQL query
mysql> SELECT instructors.lastname FROM instructors, student, courses, students_courses WHERE student.studentID = 222222 AND students_courses.studentID = student.studentID AND students_courses.courseID = courses.courseID AND courses.instructorID = instructors.instructorID;
+-----------+----------+------+--------------+-------+--------+--------------+-----------+ | firstname | lastname | GPA | phone | major | dorm | creditsTaken | studentID | +-----------+----------+------+--------------+-------+--------+--------------+-----------+ | George | Smith | 3.20 | 607-274-1234 | CS | Pepsi | 37 | 111111 | | Sally | Jones | 4.00 | 607-274-2345 | CS | Coke | 56 | 222222 | | Fred | Kelly | 2.30 | 607-274-3456 | CIS | Dew | 48 | 333333 | | Ashley | Nevins | 3.80 | 607-274-4567 | CIS | Pepper | 101 | 444444 | | Mike | Melville | 3.90 | 607-274-5678 | CIS | Orange | 59 | 555555 | | Coleen | Richford | 2.80 | 607-274-6789 | CS | Pepper | 121 | 666666 | | Samantha | Foley | 3.50 | 607-274-7891 | CS | Pepper | 133 | 777777 | | Cynthia | James | 2.70 | 607-274-8912 | CIS | Coke | 76 | 888888 | | Sam | Smalley | 2.10 | 607-274-9123 | CIS | Orange | 92 | 999999 | +-----------+----------+------+--------------+-------+--------+--------------+-----------+
- 1. Restrict to
studentID 222222
+-----------+----------+ | studentID | courseID | +-----------+----------+ | 111111 | COMP 310 | | 111111 | COMP 122 | | 111111 | COMP 375 | | 111111 | COMP 390 | | 222222 | COMP 171 | | 222222 | COMP 207 | | 222222 | COMP 122 | | 333333 | COMP 310 |
- 2. The clause
students_courses.studentID = student.studentID restricts us to
using only the rows in the students_courses table where studentID is 222222
SLIDE 45
Complex SQL query
mysql> SELECT instructors.lastname FROM instructors, student, courses, students_courses WHERE student.studentID = 222222 AND students_courses.studentID = student.studentID AND students_courses.courseID = courses.courseID AND courses.instructorID = instructors.instructorID;
+-----------+----------+ | studentID | courseID | +-----------+----------+ | 111111 | COMP 310 | | 111111 | COMP 122 | | 111111 | COMP 375 | | 111111 | COMP 390 | | 222222 | COMP 171 | | 222222 | COMP 207 | | 222222 | COMP 122 | | 333333 | COMP 310 |
- 3. Now the clause
students_courses.courseID = courses.courseID restrict the rows
in the courses table to the values COMP 171, COMP 207, and COMP 122
+----------+-------------+--------------+------+---------+--------------+ | courseID | description | instructorID | days | time | room | +----------+-------------+--------------+------+---------+--------------+ | COMP 171 | CS I | 676767 | MWF | 9:00am | Williams 303 | | COMP 205 | Adv Web | 121212 | MWF | 10:00am | Williams 309 | | COMP 190 | MatLAB | 232323 | M | 4:00pm | Williams 303 | | COMP 375 | DB | 343434 | MWF | 2:00pm | Williams 303 | | COMP 315 | Graphics | 454545 | MWF | 11:00am | Williams 309 | | COMP 390 | AI | 565656 | MWF | 10:00am | Williams 303 | | COMP 210 | Org | 787878 | TR | 10:00am | Williams 309 | | COMP 122 | Legos | 343434 | TR | 11:00am | Williams 309 | | COMP 110 | Intro | 343434 | T | 2:30pm | Williams 319 | | COMP 207 | Game Dev | 454545 | MWF | 4:00pm | Williams 303 | | COMP 310 | Op Sys | 121212 | MWF | 8:00am | Williams 309 | | COMP 123 | Legos II | 343434 | R | 2:30pm | Williams 309 | +----------+-------------+--------------+------+---------+--------------+
SLIDE 46
Complex SQL query
mysql> SELECT instructors.lastname FROM instructors, student, courses, students_courses WHERE student.studentID = 222222 AND students_courses.studentID = student.studentID AND students_courses.courseID = courses.courseID AND courses.instructorID = instructors.instructorID;
+-----------+------------+---------------+--------------+------+--------------+-----------------+ | firstname | lastname | office | phone | dept | instructorID | email | +-----------+------------+---------------+--------------+------+--------------+-----------------+ | John | Barr | Williams 401A | 607-274-9876 | CS | 121212 | barr@ithaca | | Ali | Erkan | Williams 401B | 607-274-8765 | CS | 232323 | erkan@ithaca | | Pat | Woodworth | Williams 401D | 607-274-7654 | CS | 343434 | woodwrth@ithaca | | Sharon | Stansfield | Williams 301D | 607-274-6543 | CS | 454545 | stansfield@itha | | Teresa | Zollo | Williams 301C | 607-274-5432 | CS | 565656 | zollo@ithaca | | Anne | Applin | Williams 301C | 607-274-4321 | CS | 676767 | applin@ithaca | | Jim | Daehn | Williams 309 | 607-274-3219 | CS | 787878 | daehn@ithaca | +-----------+------------+---------------+--------------+------+--------------+-----------------+
- 4. Since we can only use the
columns in the courses table that have been chosen (shown in red), the clause
courses.instructorID = instructors.instructorID will only
choose the lines 676767, 343434, and 454545 from the instructors table.
+----------+-------------+--------------+------+---------+--------------+ | courseID | description | instructorID | days | time | room | +----------+-------------+--------------+------+---------+--------------+ | COMP 171 | CS I | 676767 | MWF | 9:00am | Williams 303 | | COMP 205 | Adv Web | 121212 | MWF | 10:00am | Williams 309 | | COMP 190 | MatLAB | 232323 | M | 4:00pm | Williams 303 | | COMP 375 | DB | 343434 | MWF | 2:00pm | Williams 303 | | COMP 315 | Graphics | 454545 | MWF | 11:00am | Williams 309 | | COMP 390 | AI | 565656 | MWF | 10:00am | Williams 303 | | COMP 210 | Org | 787878 | TR | 10:00am | Williams 309 | | COMP 122 | Legos | 343434 | TR | 11:00am | Williams 309 | | COMP 110 | Intro | 343434 | T | 2:30pm | Williams 319 | | COMP 207 | Game Dev | 454545 | MWF | 4:00pm | Williams 303 | | COMP 310 | Op Sys | 121212 | MWF | 8:00am | Williams 309 | | COMP 123 | Legos II | 343434 | R | 2:30pm | Williams 309 | +----------+-------------+--------------+------+---------+--------------+
SLIDE 47
Complex SQL query
mysql> SELECT instructors.lastname FROM instructors, student, courses, students_courses WHERE student.studentID = 222222 AND students_courses.studentID = student.studentID AND students_courses.courseID = courses.courseID AND courses.instructorID = instructors.instructorID;
+-----------+------------+---------------+--------------+------+--------------+-----------------+ | firstname | lastname | office | phone | dept | instructorID | email | +-----------+------------+---------------+--------------+------+--------------+-----------------+ | John | Barr | Williams 401A | 607-274-9876 | CS | 121212 | barr@ithaca | | Ali | Erkan | Williams 401B | 607-274-8765 | CS | 232323 | erkan@ithaca | | Pat | Woodworth | Williams 401D | 607-274-7654 | CS | 343434 | woodwrth@ithaca | | Sharon | Stansfield | Williams 301D | 607-274-6543 | CS | 454545 | stansfield@itha | | Teresa | Zollo | Williams 301C | 607-274-5432 | CS | 565656 | zollo@ithaca | | Anne | Applin | Williams 301C | 607-274-4321 | CS | 676767 | applin@ithaca | | Jim | Daehn | Williams 309 | 607-274-3219 | CS | 787878 | daehn@ithaca | +-----------+------------+---------------+--------------+------+--------------+-----------------+
- 5. Now when we choose a lastname in
instructors.lastname we can only choose the names in
the table that have been selected (highlighed in red below) by the WHERE clause.
+------------+ | lastname | +------------+ | Applin | | Stansfield | | Woodworth | +------------+
SLIDE 48
Basic SQL commands
l LOAD DATA
l Allows you to loading record data from a file l Technique:
l Create a file using any text editor. l Each record, consisting of all the columns in the table,
must be on its own line.
l Separate each column by a Tab character. l You can leave a column blank for a particular record
by placing a \n in that column for the record.
l Any blank lines you leave in the file result in blank
lines in the database table.
SLIDE 49
Basic SQL commands
l LOAD DATA
l To insert data into a table from a file:
LOAD DATA LOCAL INFILE /myDir/myfile.txt INTO TABLE tableName;
Syntax:
l Must use single or double quotes around your file name. l Either enter the full path to the file or have it in the directory
where you were when you started the mysql command.
SLIDE 50
Basic SQL commands
l LOAD DATA
l Example: Assume that you started mysql from a
directory which contains the file students.txt
l You can load the information from this file into the
students table of the Ithaca DB with:
USE Ithaca; LOAD DATA LOCAL INFILE students.txt INTO TABLE tableName;
SLIDE 51
SQL Resources
l For more info on SQL Id recommend the
following sites:
l http://dev.mysql.com/doc/ l http://www.w3schools.com/sql/ l webmonkey.com l PHP.Net