MySQL

Following is a brief summary of some of the concepts on MySQL that we have covered in class:

Here are some notes about writing and running MySQL scripts and using MySQL on i5.nyu.edu:

To use MySQL on i5.nyu.edu:

To run a MySQL script on i5.nyu.edu:

Here is an exercise to try on i5.nyu.edu:

$ mysql -u <netid> <netid>
mysql> use <netid>
mysql> create table book (
  -> title varchar(32),
  -> author varchar(64)
  -> );
[Result should be: Query OK. 0 rows affected.]
mysql> insert into book values ('Pride and Prejudice', 'Jane Austen');
[Result should be: Query OK. 1 row affected]
mysql> select * from book;
[Result should show the name and author and 1 row in set]
mysql> exit
[Result should show Bye -> and return you to the Unix command line prompt.]

 

Some of the Commands we will use in MySql:

CREATE TABLE ...

creates a new table
DROP TABLE ... deletes a table (regardless of whether it contains data)
INSERT INTO ... inserts records into a table
SHOW TABLES; see a list of tables in your current database
UPDATE ... modifies a record in the table
DELETE ... deletes a record in the table
DISPLAY ... to see the columns (fields) in a table
DESCRIBE ... to see information about the table columns and field properties
SHOW COLUMNS from ... to see information about the table columns and field properties. [Note: This is the same as "describe".]
ALTER TABLE ... ADD COLUMN to add a column to a table
SELECT ... selects records from a table: remember to use the concepts of join, selection criteria, and which columns to project

 

Keywords to use with the above:

from to select the table to use
where to stipulate criteria
order by how to sort the results
group by how to group the results for summaries
limit limit how many records to return and which ones; e.g. limit 10 returns the first 10 records; limit 10,20 returns the records from 10 to 20.

 

Some examples:

UPDATE UPDATE table1
SET field1 = <value>
WHERE field2 = <value>
SELECT SELECT <field1, field2, etc>
FROM <table1, table2, etc>
WHERE <criteria>
ORDER BY <sort field1, sort field 2, etc>

SELECT a.title, a.composer_name, b.origin
FROM recordings a, composers b
WHERE a.composer_name = b.composer_name
AND a.music_type = "Ballet";
DELETE DELETE FROM table1 WHERE <condition>
ALTER TABLE

ALTER TABLE table1
ADD COLUMN <new field> <field-type>(field length); e.g.
ALTER TABLE books
ADD COLUMN year_written INT(4);

(Note: Additional alter table parameters are listed on page 87.)

 

Additional information about setting up tables:
Column Types

INT(m) - an integer of m length
DECIMAL(m,n) - a number of m length with n decimal places
VARCHAR(m) - a variable-length character field of up to m length
CHAR(m) - a fixed-length character field of m length
DATE - a date in the format 'YYYY-MM-DD'
DATETIME - a date and time in the format 'YYYY-MM-DD-HH:MM:SS'
TEXT - a text block of up to 65,535 characters

Note: A full list of column types is listed in the book on page 49.

Options to specify for a given column NOT NULL - for required fields
DEFAULT <default value> - to set a default value
AUTO-INCREMENT - for numeric columns, this sets the value of the field 1 greater than the previous record
PRIMARY KEY - to set a primary key
Options for sorts Note: Sorts default to ascending
DESC - to sort in a descending order

Additional material (not required for the exam): Using Perl DBI and MySQL: composers & recordings -- and the source code in Perl.

Return