CPSC225/Notes/MySQL (Morelli)

Database Basics
A relational database is made up of tables. Databases are managed by database management system (DBMS) or database server. MySQL is an open-source DBMS. SQL (pronounced S-Q-L) is a query language used by most DBMS.

A database table may have multiple attributes (columns). The data in a table are contained in rows. Tables may be linked together (related) through special attributes that are designated keys. A primary key is an attribute (one or more) that contains values that uniquely identify the rows of a table. For example, a part's unique part number.

Additional Terminology
A relational model is a formal model that uses tables, attributes, and rows to manage data. The entity-relationship (ER) model is a modeling technique used to describe data.

A constraint is some kind of limitation on tables or attributes.

An index is a data structure used to provide fast access to the data.

A normalized database is a database created from a correct ER model.

MySQL Commands
MySQL commands can be given from the command prompt. In this example we start the MySQL Command Interpreter on either a Linux or Windows system, with command line arguments, -u for username and -p for password: % /usr/local/bin/mysql -uram -pshhh "C:\Program Files\EasyPHP1-7\mysql\bin\mysql.exe" -uram -pshhh Here are some examples to try from the MySQL prompt (NOTE that the commands end with a semicolon and the rest of the line is a brief explanatory comment):

mysql>SELECT NOW;   // Display the current date and time mysql>quit            // Quits the interpreter mysql>source file.sql // Runs the commands stored in the named file Here's a simple example of creating and using a database. mysql>CREATE DATABASE winestore;  // Creates a database mysql>use winestore;              // Use this database in the remaining commands // Create a table and its attributes and a primary key mysql>CREATE TABLE customer (id int(5) NOT NULL, name varchar(50),PRIMARY KEY(id)); mysql>DROP TABLE customer;        // Removes a table from the database mysql>DROP DATABASE winestore;    // Removes the entire database

Data Types and Modifiers
The two most widely used attributes are NOT NULL, which specifies that every row in the table must have a value for a given variable, and DEFAULT, which allows a default value to be set for an attribute:

id int(5) NOT NULL, name varchar(50) DEFAULT "unknown",

Primary Keys
A primary key is one or more attributes that uniquely identify a row of a table. For example, for a customer table, the customer's id attribute might be the key or the customer's first_name and last_name attributes might be the primary key.

Inserting, Updating, and Deleting Data
mysql>INSERT INTO customer VALUES (1, 'Jen'); // Insert customer id and name mysql>INSERT INTO customer VALUES (2, NULL);         // Insert with NULL for name mysql>INSERT INTO customer VALUES (3, 'Joe'), (4, 'Jane'); // Insert two records at once mysql>INSERT INTO customer SET id=5,name='Jerry';

mysql>DELETE FROM customer WHERE id=1;                // Deletes a row from customer table mysql>UPDATE customer SET name='Joe Smith' WHERE id=3; // Updates a row of the table

Querying the Database
SELECT id,name FROM customer;          // Outputs a 2-column table with all rows SELECT * FROM customer;                // Outputs all the data from customer table SELECT * FROM customer LIMIT 5;        // Outputs the first 5 rows of the table. SELECT * FROM customer WHERE id>3;     // Outputs all customers with id numbers greater than 3. SELECT name FROM customer WHERE id > 3;  // Outputs just the names.

SELECT * FROM customer ORDER BY name;    // Sorts the data by the name attribute SELECT name, COUNT(*) FROM customer GROUP BY name; // Outputs number of customers with same names.