My SQL A Quick Introduction Databases Database Collection

(My) SQL A Quick Introduction

Databases Database Collection of data l Organise data into tables of records of data l Relational Database Related collection of data l Tables with links between them l

Relational Database Management System RDBMS l SQL Server, Access, Foxpro, d. Base, Paradox, DB 2, Oracle, Postgres, Sybase, My. SQL l Usually a database server with clients l All access to data through RDBMS server l Security l Reliability l Ease of programming l

SQL Structured Query Language l l l A way of asking questions of databases Also giving instructions A full programming language l Will only give a very brief introduction My. SQL l l l One of many relational database management systems using SQL “The world’s most popular open source database” An example of a very successful open source product and business

My. SQL One server can host several databases Strict access control to different users, computers, databases and tables Can communicate with the My. SQL server using almost any programming language l l l Connect to server Select database Issue SQL queries Retrieve results Disconnect from server PHP works well with My. SQL

SQL: SELECT, WHERE A means of selecting and returning data from a database SELECT fields FROM table WHERE expression l SELECT name, dept FROM employees WHERE salary > 3000 l SELECT * FROM customers l

CREATE, DROP A means of creating new tables, l l CREATE TABLE customers (number INT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(128) NOT NULL, identification CHAR(64), PRIMARY KEY(number)) Specify the data type for every field in the table Can specify properties about each field (Can’t be empty, automatically incrementing, …) Can specify properties for the table (primary key, indexes, …) Deleting tables, l DROP TABLE customers

INSERT, UPDATE, DELETE Putting data into a table l INSERT INTO customers (name, identification) VALUES ("Matt Ryan", "014678692")‘ Modifying data in a table l UPDATE customers SET name=“Matthew” WHERE identification = “ 014678692” Deleting items from a table l DELETE FROM customers WHERE name=“Matthew”

PHP and My. SQL Several useful functions, mysql_connect(server, user, password); l mysql_select_db(database); l result = mysql_query(sql); l mysql_num_rows(result); l mysql_fetch_assoc(result); l Many, many more