MySQL

Exploring MySQL, the open source, multithreaded, multi-user SQL DBMS used by great sites such as Wikipedia.org and Slashdot.com.

• Miscellany
• Links

Miscellany

• shell> mysql -h hostname -u usernamme -p myDB
  • mysql> \c -- Cancels command.
  • mysql> quit
• mysql> show databases;
  • mysql> create database myDB;
  • mysql> use myDB;
• mysql> show tables;
  • mysql> create table t2 (col1 int, col2 char);
  • mysql> create table typical_table( t_ID int not null auto_increment primary key, t_str varchar(64), t_strb text, t_dtm datetime, t_int int, t_flt float )
  • mysql> describe t2;
  • mysql> show create table t2;
  • mysql> drop table t2;
• mysql> alter table t1 rename t2;
  • mysql> alter table t2 modify a tinyint not null; -- Changes definition of column a.
  • mysql> alter table t2 change b c char(20); -- Changes definition and name of column b.
  • mysql> alter table t2 drop column c;
  • mysql> alter table t2 add col1 int first, add col3 text after col2; -- The default location of added columns is last.
• mysql> load data local infile 'pathAndFile' into table table lines terminated by '\r\n'; Where each line is a record with fields separated by tabs, and NULLs are represented as literal '\N'. The lines terminated portion is just for Windows.
  • mysql> INSERT INTO t2 (ID, name) VALUES (1,'dog') , (2,'cat'); -- You can insert multiple rows with a single statement.
• mysql> insert into t2 values(1,'hi');
  • mysql> insert into t2 values(null,79); -- insert null for auto_increment columns
  • mysql> select last_insert_id(); -- to get latest ID
    mysql> select @@identity; -- to get latest ID as of MySQL 3.23.25
  • mysql> ALTER TABLE t2 AUTO_INCREMENT=5000; -- resets the initial auto_increment value --unless that column has already has a value greater than what you reset to.
• mysql> select now(), curdate(), curtime(), utc_timestamp();
  • mysql> SELECT CURDATE(), (YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5)<RIGHT(birth,5)); -- Show curdate and age if birth is a date field.
  • mysql> select year(curdate()), month(curdate());
  • mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
    mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
• mysql> select @@version
• mysql> load data local infile '/pat/source.txt' into table t1 lines terminated by '\r\n'; -- source files have 1 record per line, tabs between columns, null indicated literally by "\N".

Links

Links that lead to off-site pages about MySQL.

• Official
  • MySQL.com
  • MySQL Reference Manual
    • Column types
    • Date and time functions
    • SQL statement syntax
• Miscellany
  • #mysql [HashMySQL.org]. Wiki on MySQL.
  • forge.mysql.com.
  • Introduction to MySQL [databases.about.com/od/shareware/a/mysql.htm]
  • MySQL [W]
  • "MySQL and UTF-8 — no more question marks!" [bluetwanger.de/blog/2006/11/20/mysql-and-utf-8-no-more-question-marks/]
  • MySQL General Purpose Stored Routines Library [nongnu.org/mysql-sr-lib/]. 'This routine library will collect general purpose MySQL 5 stored procedures and functions. What is to be considered "general purpose"? Any routine that enhances the language itself or that can be useful in an abstract database fits the bill.'
  • Planet MySQL [planetmysql.org]. 'an aggregation of blogs and news from MySQL developers and users'

Page Modified: (Hand noted: 2007-10-13 19:58:34Z) (Auto noted: 2007-11-04 23:37:28Z)