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 databases? l How to design a database -- last lecture l How to use and update a database -- this lecture

Database Basics Creating a Database Using/Updating a Database

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 of SQL.

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.

SQL Tables Everything in SQL is stored in tables: Each table is identified by a l name (i.e. � People"). Tables contain records (rows) with data. Below is an example of a table called "People": l Lname Fname Phone# ID Smith John 5309 0 Karlsson Karl 4200 1 Wallace William 8569 2 The table contains three records (one for each person) and four columns (LName, FName, Phone, and ID).

Basic SQL DML (data manipulation language) Command Action Creates a new table. CREATE USE Use a database Show the fields in a table. DESCRIBE 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. Loads records from a text file. LOAD DATA UPDATE Modifies data in an existing record. Performs a query on a table, including mathematical functions, field comparison, pattern SELECT matching, etc. Print the databases (or tables or grants) available. SHOW DELETE Permanently removes elements from a table.

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

Using mySQL directly (no php) l First must log into the Linux server using ssh or 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.

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

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> Note that the database is specified: Ithaca l Here, the students database has four tables named student, l courses, instructors, and students_courses . Caution: capitalization matters in the name of the database! l

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> The localhost means the computer you are logged into. l You can also have privileges when sending commands from a remote l machine. In this case you may see '%' or an IP address instead of localhost USAGE means no permissions. l The 'Ithaca'.* is the database Ithaca l

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

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’

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!

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;

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!

Basic SQL commands not null means that l CREATE field MUST have a value l Example: creating the instructors table primary key is a unique identifier for CREATE TABLE instructors( the row. Every value instructorID int not null primary key, must be unique. instrName varchar (30) not null You can leave the ); not null and primary l Notice the type key off of every line! 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 §

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 §

Basic SQL commands mysql> SHOW TABLES; +-------------------+ | Tables_in_Ithaca | +-------------------+ | student | Create a student table with fields | courses | | instructors | first, last, ID, major | students_courses | +-------------------+ 2 rows in set (0.00 sec) ID is an int, a primary key and not null first, last are varchar(20) mysql> CREATE TABLE clubs ( -> studentID int primary key, -> clubName varchar(20) not null Create a student table with fields -> ); first, last, ID, depart Query OK, 0 rows affected (0.00 sec) mysql> SHOW TABLES; ID is an int, a primary key and not null +-------------------+ first, last are varchar(20) | Tables_in_Ithaca | +-------------------+ | student | | courses | | instructors | | students_courses | | clubs | +-------------------+ 3 rows in set (0.00 sec)

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>

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;

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

ALTER l Change the student table to include GPA, an int.