1 / 45

Databases and PHP

Databases and PHP. Creating and Using Databases in mySQL. Database Basics. Remember our Database goal: To organize some data in a manner that makes it easy to relate, store, and retrieve the data. Database Basics. What do we need to know about databases?

dyllis
Download Presentation

Databases and PHP

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Databases and PHP Creating and Using Databases in mySQL

  2. Database Basics • Remember our Database goal: • To organize some data in a manner that makes it easy to relate, store, and retrieve the data

  3. Database Basics • What do we need to know about databases? • How to design a database -- last lecture • How to use and update a database -- this lecture

  4. Database Basics Creating a Database Using/Updating a Database

  5. Basic SQL Commands • Structured Query Language (SQL) is an ANSI (American National Standards Institute) standard. • SQL is a “language” for creating, modifying, and deleting database tables, records, and fields. • Many different databases use SQL • Most databases have proprietary extensions of SQL.

  6. Basic SQL Commands • SQL allows you to: • access a database • execute queries against a database • retrieve data from a database • Insert, Update and Delete records from a database.

  7. SQL Tables • Everything in SQL is stored in tables: Each table is identified by a name (i.e. “People"). Tables contain records (rows) with data. • 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).

  8. Basic SQL DML (data manipulation language)

  9. Basic SQL Commands • Syntax • Commands are by convention in all capital letters. Doesn’t really matter. • Every command ends with a semicolon (‘;’) • Table and field capitalization does matter.

  10. Using mySQL directly (no php) • First must log into the Linux server using ssh or putty. • Then must start mySQL: mysql -u cs205user -p • Where cs205user is the account name • Note that everything is lower case • The mySQL admin has given you access to a particular database. • You will be prompted for a password.

  11. Using mySQL directly (no php) • SHOW • To see what databases you have access to type: mysql> SHOW DATABASES; +-----------+ | Database | +-----------+ | Ithaca | | test | +-----------+ 2 rows in set (0.00 sec) • Here, cs205user has access to the Ithaca database and a test database

  12. Using mySQL directly (no php) • 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> • Note that the database is specified: Ithaca • Here, the students database has four tables named student,courses, instructors, and students_courses. • Caution: capitalization matters in the name of the database!

  13. Using mySQL directly (no php) • 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> • The localhost means the computer you are logged into. • 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 • USAGE means no permissions. • The 'Ithaca'.* is the database Ithaca

  14. Using mySQL directly (no php) • USE. • 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> • Now you can enter commands without specifying the database: mysql> SHOW TABLES;

  15. Basic SQL commands • DESCRIBE • Allows you to see the format of the fields in a table. • 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>

  16. Basic SQL commands • SHOW • You can see all the databases that you have access to: SHOW DATABASES; • You can also see the tables that are in a database: USE Ithaca; SHOW TABLES;

  17. SQL Queries • SELECT • This is the command you use to see what’s in your database. • When talking about databases, a SELECT is called a “query” • 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]; • Example: to select all the records in a table: SELECT * FROM courses; • Example: to select just the entries in the courseID field of the courses table: SELECT courseID FROM courses;

  18. SQL Queries: WHERE • 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)

  19. AND, OR • AND and OR can also be used to construct more complicated queries: SELECT * FROM People WHERE id > 0 AND Lname LIKE ‘Hoch’ • The * is used as a wildcard, and will return the data in all columns • LIKE means only Lnames that exactly match ‘Hoch’ will be returned • If you want Lnames that begin with ‘Hoch’, must use a wildcard: Lname LIKE ‘Hoch%’ • The % will match 0 or more characters, the underscore ‘_’ will match exactly 1 character. • To match the ‘%’ or ‘_’ character, backslash them: ‘\%’ or ‘\_’

  20. AND, OR • 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>

  21. SQL Queries • SELECT • To select all records and have them returned in a particular order, use an expression for ORDER BY. • Example: to view courseID and course Descript ordered by courseID from smallest to largest: SELECT courseID, description FROM courses ORDER BY courseID DESC; • DESC means descending. ASC means ascending (ASC is the default)

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

  23. SQL Queries • SELECT • Can also perform mathematical and string functions within SQL statements. To count the number of courses: SELECT COUNT(courseID) FROM courses; • Use the WHERE option to specify certain field values. • Example: get course ID and description for all courses taught by the instructor with ID 76765: SELECT courseID, descript FROM courses WHERE InstructorID = ‘76765’;

  24. SQL Queries • SELECT • Can select fields from different tables : SELECT courses.descript, instructors.instructorName FROM courses, instructors WHERE courses.InstructorID = instructors.InstructorID; • This query searches the instructors table and the courses table. • When the instructor ID in the instructors table matches the instructor ID in the courses table • Then the corresponding course description from the courses table and the corresponding instructor name from the instructors table are printed.

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

  26. 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: Every table that you use in the SELECT or WHERE clauses must be named in the FROM clause. In the WHERE clause, each AND clause should further restrict your search.

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

  28. 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 | +----------+-------------+--------------+------+---------+--------------+

  29. 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; +----------+-------------+--------------+------+---------+--------------+ | 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 | +----------+-------------+--------------+------+---------+--------------+ 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. +-----------+------------+---------------+--------------+------+--------------+-----------------+ | 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 | +-----------+------------+---------------+--------------+------+--------------+-----------------+

  30. 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; 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. +-----------+------------+---------------+--------------+------+--------------+-----------------+ | 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 | +-----------+------------+---------------+--------------+------+--------------+-----------------+ +------------+ | lastname | +------------+ | Applin | | Stansfield | | Woodworth | +------------+

  31. Basic SQL commands • CREATE • Allows you to create new databases and tables, depending upon the permissions you have. • You specify the fields of the table (can modify later), but not the values of the fields. • Syntax to create a table: CREATE TABLE [table name][(name_of_field1 field1_datatype, name_of_field2 field2_datatype , …)] [options ];

  32. Basic SQL commands • CREATE • Example: creating the instructors table CREATE TABLE instructors( instructorID int not null primary key, instrName varchar (30) not null ); • Notice the type • 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

  33. Basic SQL commands • CREATE • Example: creating the instructors table CREATE TABLE instructors( instructorID int not null primary key, instrName varchar (30) not null ); • Varchar(20) means that the field will have a varying number of characters up to 20 max • More information about CHAR and VARCHAR is here: • http://dev.mysql.com/doc/refman/5.0/en/char.html • Information about other types is at: • http://dev.mysql.com/doc/refman/5.0/en/data-types.html

  34. 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)

  35. Basic SQL commands • INSERT • Allows you to add a record to a table INSERT INTO [table name]([name of field1] , [name of field2] , …) VALUES (‘[value of field 1]’ , ‘[value of field 2]’ , … ); • Syntax: • Must use single or double quotes around your values. • Convention is to use single quotes, but it doesn’t matter.

  36. Basic SQL commands • INSERT • 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>

  37. Basic SQL commands • ALTER • Allows you to modify elements of a particular table. • Can add fields, change field types, delete fields. • Example: to change the field size of name: ALTER TABLE courses CHANGE InstructorID InstructorID VARCHAR(30); • Example: to add a column to a table: ALTER TABLE courses ADD days varchar(5);

  38. Basic SQL commands • DROP • Allows you to delete an entire table DROP TABLE [table name]; • Example: DROP TABLE Students; • Also can use to delete specific fields: ALTER TABLE courses DROP descript; Where courses is a table and descript is a field

  39. Basic SQL commands • LOAD DATA • Allows you to loading record data from a file • Technique: • Create a file using any text editor. • Each record, consisting of all the columns in the table, must be on its own line. • Separate each column by a Tab character. • You can leave a column blank for a particular recordby placing a ‘\n’ in that column for the record. • Any blank lines you leave in the file result in blank lines in the database table.

  40. Basic SQL commands • LOAD DATA • To insert data into a table from a file: LOAD DATA LOCAL INFILE “/myDir/myfile.txt” INTO TABLE tableName; Syntax: • Must use single or double quotes around your file name. • Either enter the full path to the file or have it in the directory where you were when you started the mysql command.

  41. Basic SQL commands • LOAD DATA • Example: Assume that you started mysql from a directory which contains the file students.txt • 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;

  42. Basic SQL commands • UPDATE • Modifies part of a record without replacing the entire record: UPDATE [table name]SET [field name] = ‘[new value]’ WHERE [expression]; • Example: change name of instructor for course 304-212: UPDATE courses SET InstructorID = ‘49281’ WHERE courseID = ‘304-212’; • Syntax: • If you leave out the WHERE, then all records are updated! • You can perform string functions and math functions on existing records and use the UPDATE command to modify their values

  43. Basic SQL commands • DELETE • Allows the deleting of records from tables : DELETE FROM [table name] WHERE [expression]; • Example: to delete all records with instructorID of 76765 from the courses table: DELETE FROM courses WHERE instructorID = ‘76765’; • 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’;

  44. Basic SQL commands • DELETE • If you leave out the WHERE expression, you delete all records! DELETE FROM courses; Deletes all the records in the courses table. • If you want to delete a specific field from all records, use the ALTER command: ALTER TABLE courses DROP instructorID;

  45. SQL Resources • For more info on SQL I’d recommend the following sites: • http://dev.mysql.com/doc/ • http://www.w3schools.com/sql/ • webmonkey.com • PHP.Net

More Related