MySQL – Introduction to working with Queries

MySQL – Working with MySQL Statements – Notes for class Discussion

See http://dev.mysql.com/doc/refman/5.6/en/sql-syntax.html for further reference.

CREATE TABLE … creates a new table
DROP TABLE … deletes a table (regardless of whether it contains data)
INSERT INTO … inserts records into a table
LOAD DATA LOCAL INFILE … INTO TABLE … reads data from a textfile into a table
SHOW TABLES; see a list of tables in your current database
UPDATE … modifies one or more records in the table
DELETE … deletes one or more records in the table [Note: This cannot be "undone"!]
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

 

Class Notes: Some of the Keywords to use with our examples of SELECT queries:
FROM to select the table to use
WHERE to stipulate criteria for the selection
DISTINCT to select unique instances (eliminates duplicates in a list based on the selected column)
ORDER BY which column(s) to use to sort the results
GROUP BY which column(s) to use to group the results for summaries such as sub-totals and other aggregate calculations (similar to using the “database” functions in Excel or “summary” statistics in Access)For example … to find the average price in this book collection by publishing house:SELECT author,AVG(price)
FROM books
GROUP BY edition
ORDER BY edition;
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.

 

Class Notes: Further examples on editing data and tables:
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>
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 price DECIMAL(6,2);

Data Types (http://dev.mysql.com/doc/refman/5.6/en/data-types.html)

Class Notes: Additional information about setting up tables:
Data Types INT(m) – an integer of length
DECIMAL(m,n) – a fixed number of m length with n decimal places [Note - we will also discuss DOUBLE(m,n) and FLOAT(m,n)]
VARCHAR(m) – a variable-length character field of up to length
CHAR(m) – a fixed-length character field of 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
Options to specify for a given column when creating a table 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 (also to ensure unique values in this field)
FOREIGN KEY – to set up a foreign key (to ensure referential integrity; this is ont necessary as you can join on the foreign key field without this.)For example: to create a primary key using a surrogate key field:
CREATE TABLE sample (
field1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, ...
)
Options for sorts Note: Sorts default to ascending
ASC – to sort in ascending order
DESC – to sort in a descending order

 

Class Notes: Warnings
Warnings Warnings can be generated at times when you use the UPDATE, INSERT, DELETE commands or any commands that manipulate tables and data such as LOAD DATA.Use show warnings right after the warnings appear to see them:mysql> UPDATE books
SET price = price+(price*.25);

Query OK, 6 rows affected (0.01 sec)
Rows matched: 6 Changed: 6 Warnings: 3

mysql> show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------+
| Note | 1265 | Data truncated for column 'price' at row 2 |
| Note | 1265 | Data truncated for column 'price' at row 3 |
| Note | 1265 | Data truncated for column 'price' at row 5 |
+-------+------+--------------------------------------------+
3 rows in set (0.00 sec)