Ben Learning MySQL ================== Infrastructure -------------- To start the server process, if not already started at boot-time: $ safe_mysqld --user=mysql # indicate the user to run as Typical installations store the data in /var/db/mysql/. Run the interactive client like so: $ mysql -u username [-h hostname] -p Enter password: mysql> SQL commands always end with semicolon. Administration -------------- Set the root password like so: $ mysqladmin -u root password blahblahblah Log in as root, create a new database: > SHOW DATABASES; [...] > CREATE DATABASE tempdb; # create 'tempdb' database > USE tempdb; # make it the active db > DROP DATABASE tempdb; # delete it The 'mysql' database contains six system tables: > USE mysql; > SHOW TABLES; # shows all tables in the db [...] > DESCRIBE user; # describe each field in the 'user' table [...] SQL -- (at least, MySQL's version of it!) --- * Common Data Types INT REAL DATE TIME CHAR(length) # fixed length; extra space is padded out. VARCHAR(length) # variable length, up to 255 chars. TEXT(length) # variable length, up to 64k chars. CHAR BINARY # a byte of binary data VARCHAR BINARY # variable length binary data BLOB # even longer binary data ENUM(list) # enumerated type, e.g. ENUM('apple', 'orange', 'banana') * Delete macro-level stuff > DROP TABLE footable; > DROP TABLE footable, bartable, baztable; > DROP TABLE IF EXISTS footable; # avoids script errors > DROP DATABASE foodb ; # harsh * Create a new table Just list each column name, data type, and optional modifiers: > CREATE TABLE footable ( columnname1 type [modifiers], columnname2 type [modifiers], ... ); > CREATE TABLE footable ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, age INT DEFAULT 5, name varchar(200)); * Common column modifiers PRIMARY KEY # field is main index for relational table 'joins' NOT NULL # don't allow the field to be uninitialized. DEFAULT 'foo' # if uninitialized, use this default value. UNSIGNED # for numeric values only AUTO_INCREMENT # only one field can have this. NULL value activates. * Creating indices Independently: > CREATE INDEX ageindex ON footable (age, id); or at table-creation time: > CREATE TABLE footable ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, age INT DEFAULT 5, name varchar(200), INDEX ageindex (age, id), UNIQUE INDEX nameindex (name)); # name field must be unique * Inserting a new row > INSERT INTO tablename (field1, field2, ...) VALUES (val1, val2, ...); > INSERT INTO footable (id, age, name) VALUES (3, 29, 'Ben'); If you ignore fields, then they'll either be initialized as NULL or take on a default value: > INSERT INTO footable (age, name) VALUES (29, 'Ben'); # NULL causes AUTO_INCREMENT on id Strings with single quotes may need to be escaped with \ char. ('Ben\'s homedir', or 'C:\\foo\\bar') * Changing an existing row > UPDATE tablename SET field1=val1, field2=val2, ... [WHERE condition]; > UPDATE footable SET age=30 WHERE name='Ben'; * Delete rows > DELETE FROM tablename [WHERE condition] > DELETE FROM footable WHERE name='Joe'; * Reading (Querying) A query always returns the answer as a virtual table. You simply ask for the columns you want in the answer, and supply the necessary restrictions. > SELECT field1, field2, ... FROM table1, table2, ... [WHERE condition]; > SELECT name, age FROM footable; * Restricting the Query The WHERE clause specifies a condition to select the rows. The HAVING clause, however, filters rows in the *results* of the query: > SELECT name FROM footable WHERE age > 31; HAVING filters on the *result* set, rather than the whole table: > SELECT name FROM footable WHERE age > 31 HAVING id > 5 So after first pulling out all rows with age > 31, it runs an id > 5 restriction on that result set. HAVING is most often used with GROUP BY, mentioned later on. You can also simply limit the number of returned rows with LIMIT: > SELECT name FROM footable WHERE age > 31 LIMIT 5 # only return the first 5 rows that match > SELECT name FROM footable WHERE age > 31 LIMIT 4, 10 # return the 4th match, and 10 after that The IN and BETWEEN operators are nice for selecting a range: > SELECT name FROM footable WHERE age BETWEEN 10 and 20; > SELECT name FROM footable WHERE name IN ('Larry', 'Moe', 'Curley'); * Sorting results Use the ORDER BY keyword: > SELECT name, age FROM footable WHERE age > 31 ORDER_BY age; Use DESC (descending) to reverse sort: > SELECT name, age FROM footable WHERE age > 31 ORDER_BY age DESC; You can also ORDER BY multiple columns: > SELECT name, age FROM footable WHERE age > 31 ORDER_BY id, age; * Grouping results Combine similar rows together in your results with GROUP BY: > SELECT age FROM footable GROUP BY age; This returns a list of all the unique ages in the table. If we had used a WHERE clause, it would be a list of the unique ages in only those rows which matched the condition. * Joins (relational queries) Great example from O'Reilly Book: > SELECT book.title, author.name # want two columns from different tables FROM author, book WHERE author.id = book.author; * Aliases Use the AS keyword to create short aliases for field names or table names: > SELECT really_long_field_name AS foofield, age FROM footable WHERE foofield > 31; > SELECT name, age FROM really_long_table_table_name AS mytable; * Beware NULL comparisons Never ever say 'WHERE foo = NULL' -- it will always succeed. Instead, say 'WHERE foo IS NULL' or 'WHERE foo IS NOT NULL'. * Pattern matches Really simple matching can be done with the LIKE/NOT LIKE keywords: > SELECT name FROM footable WHERE name LIKE 'Be%'; The % operator is like .*, and the _ operator is like . But MySQL allows true regexps, so why bother with that crap. > SELECT name FROM footable WHERE name REGEXP '^Be.+'; Woo! * Full-text searching This is a fluffy MySQL feature, that gives a list of 'hits' when pattern matching, and even allows you to view the score of each hit. 1. Create an index called FULLTEXT: > CREATE INDEX searchindex ON footable (name); 2. Use the MATCH/AGAINST keywords to get hits: > SELECT id FROM footable WHERE MATCH (name) AGAINST ('some string'); * Table locking Three lock types: read # table becomes read-only read local # table is read-only, but INSERT is allowed, # provided actual db files don't change. write # no other client can read or write > LOCK TABLES footable WRITE; > [... make changes ...] > UNLOCK TABLES; * Transactions If you have MySQL 4, you can use txns: 1. Create a berkeley-db based table: > CREATE TABLE footable (...) TYPE = BDB; 2. Group commands into a single txn, then commit them: > BEGIN; > UPDATE footable SET age=30 WHERE name='Ben'; > UPDATE footable SET age=34 WHERE name='Joe'; > COMMIT; You can abort the txn by running ROLLBACK instead of COMMIT. * Functions The good stuff! You can put function calls anywhere: as a virtual 'field' to return in a query, within a WHERE condition, and so on. There are a zillion built-in functions, and you can define your own too. Too complex to put here, check MySQL reference docs. * Running a bunch of SQL commands Put your SQL commands in a text file. Execute them like this: $ mysql -h host -u username -p < command_file.txt * Import/Export You can import/export data from/to a delimited text file. MySQL assumes tab-delimited text files by default, unless you say so: > LOAD DATA LOCAL INFILE 'mydata.txt' INTO TABLE footable; > LOAD DATA LOCAL INFILE 'mydata.txt' INTO TABLE footable FIELDS TERMINATED BY ','; > SELECT * INTO OUTFILE 'mydata.txt' FROM footable; > SELECT * INTO OUTFILE 'mydata.txt' FIELDS TERMINATED BY ',' FROM footable; THE SUPER-SHORT COMMAND SUMMARY ------------------------------- Create and destroy stuff: CREATE, DROP stuff to create/destroy: DATABASE, TABLE, INDEX Write ops on rows: INSERT, UPDATE, DELETE Read op returns table: SELECT Sort or Compress table: ORDER BY, GROUP BY Restricting searches: WHERE (pre-scan), HAVING (post-scan)