Windows install directory: C:\Program Files\MySQL\MySQL Server 5.5\ Windows data directory: C:\ProgramData\MySQL\MySQL Server 5.5\data Linux data directory: /var/lib/mysql/ for RPM or /usr/local/mysql for tarball hostname.err #log file libmysqlclient C API $mysql_config --libs #-L -l options for compiling /usr/share/doc/packages/MySQL-server/manual_toc.html manual.html /usr/share/doc/mysql-* /usr/share/mysql Server config file(s): default good for most servers Linux: global: /etc/my.cnf OR /etc/mysql/my.cnf per-server: datadir/my.cnf per-user: ~/.my.cnf /usr/share/doc/packages/MySQL-server/my-medium.cnf Windows: installDir\my*.ini OLD: %SystemRoot%/my.ini C:\my.cnf Defaults for storage engine, char set, TCP port, SQL mode. [mysql] #no-auto-rehash to allow table and column completion Linux only :( Start server: Linux: /etc/init.d/mysql start OR [sudo] start mysql stop mysql Windows: net start|stop mysql #start|stop service or run services.msc for Automatic/Manual/Disable Start client in CMD: (Use CMD to run MySQL client rather than the "MySQL Command Line Client" link because you won't know what folder it starts in and so won't be able to access your files.) $mysql use passwordless anonymous user on localhost $mysql -u username -p -h hostname.or.IP default host is 'localhost' 'localhost' on Unix means connect via Unix domain socket (default: /tmp/mysql.sock but is /var/lib/mysql/mysql.sock ) '.' on Windows means connect via named pipe (if server started with --enable-named-pipe option) (default pipe name: MySQL) else connect via TCP/IP, default port is 3306 default username is Unix login, on Windows is 'ODBC' $mysql --pager=more --prompt="(\u@\h) [\d]> " --no-beep or -b $mysql --i-am-a-dummy (--safe-updates) no delete/update w/out Where with key or Limit, select of < 1000 rows unless Limit, joins of < 1 million rows. mysql client commands: (not SQL!) Up and Down arrow keys have command history. HOME and END go to beginning and end of line. You do not have to be at end of line to hit Enter. Tab-completion of tables and fields in Unix. ; tells mysql the query is complete: multiple lines per query and multiple queries per line. pager more Not in Windoze version :( nopager tee tooutfile Log interactive to file (like typescript in shell) notee Stop the logging help list of client commands OR ? system shellcmd prompt (\u@\h) [\d]>\_ rehash Rebuild completion hash. Tab-completion not available in Windoze. :( source sql_batchfilename # \. quits at first error status #of mysql client program \G instead of ; displays results vertically \c clear command ^C (ctrl c) terminates mysql. How to stop long output? \q quit mysql OR quit OR exit ? ? SQLstatement/function brief syntax help. HELP contents server-side help. syntax & examples of everything. help tables in mysql database. from Manual. server information: SHOW STATUS; #310 counters FLUSH STATUS; #reset counters SHOW PROCESSLIST; #connected clients/threads SHOW VARIABLES; #120/329? of them Show variables Like '%buffer'; SHOW INNODB STATUS; # select host,user,password from mysql.user; #users ********************** SHOW DATABASES; Show Databases Like '...'; SCHEMA synonym for Database USE db1 SHOW TABLES; Show Tables Like '...'; Show Tables From db1; DESCRIBE t1; Desc t1; Desc t1 f2; Show Columns|Fields From t1 [From db1] [Like '...']; SHOW CREATE TABLE t1; SHOW INDEX FROM t [FROM db]; SHOW TABLE STATUS; Name,Engine,Row_format,Rows,Avg_row_length,Data_length,Index_length, Create_time,Update_time SHOW WARNINGS; if any, of previous statement CREATE DATABASE [If Not Exists] db2; DROP DATABASE [If Exists] db2; can't rename a database :( Must dump and reload into new named db. CREATE TABLE t2 (f1 datatypeAndMods, f2 datatypeAndMods,...); CREATE TABLE t2 (f1 datatypeAndMods, f2 datatypeAndMods,.., PRIMARY KEY(f1), INDEX(f5), FOREIGN KEY(f3) REFERENCES t1(f1)) ENGINE=InnoDB; CREATE TABLE t2 LIKE t1; #copy of existing table definition. but notFKs CREATE TABLE t2 SELECT * FROM t1 WHERE 0; #but not t1's indexes, auto_increment, non-default engine CREATE TABLE t2 SELECT * FROM t1; #populate it too. CREATE TEMPORARY TABLE t3 ... #exists for connection only, per-user DROP TABLE t1; create database|table IF NOT EXISTS..; #to avoid error if already exists drop database|table IF EXISTS..; #to avoid error if doesn't exist ALTER TABLE t ADD fj itsTypeAndMods; #add a column. modifiers too ALTER TABLE t ADD fj itsTypeAndMods [FIRST|AFTER fi]; #in certain column position ALTER TABLE t ADD PRIMARY KEY(fi); ALTER TABLE t ADD FOREIGN KEY(fk) REFERENCES t2(t2PK); ALTER TABLE t CHANGE currentFname newFname itsTypeAndMods; #change name and/or type of column ALTER TABLE t MODIFY fi itsNewTypeAndMods; #change type of column ALTER TABLE t DROP fi; ALTER TABLE t DROP FOREIGN KEY constraint_name; ALTER TABLE t ENGINE=InnoDB; #change engine type. @@storage_engine is default. Show Engines; ALTER TABLE t1 RENAME TO t2; RENAME TABLE t1 TO t2; RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2; #swap table names ALTER TABLE t1 action1, action2, action3... LOAD DATA [LOCAL] INFILE 'filename' INTO TABLE t1 IGNORE|REPLACE #Ignore dups (default if Local), Replace existing dup #default if on server is error abort. FIELDS TERMINATED BY ' ' #default is tab \t LINES TERMINATED BY '\r\n' #default is newline \n IGNORE n LINES #skip first n lines of file (f2,f6,f3) #list of columns corresponding to fields of datafile if LOCAL file on client, duplicate errors are ignored/skipped. if file on server, duplicate error causes abort unless Ignore or Replace used. file on server could have been created by Select .. Into Outfile 'fname'... command line interface to Load Data Infile: $ mysqlimport --local --fields-terminated-by=" " --fields-enclosed-by='"' --lines-terminated-by="\r\n" --ignore|--replace db table.txt filename must be table name. #Linux: convert mm/dd/yy to 20yy-mm-dd $sed 's/\(..\)\/\(..\)\/\(..\)/20\3-\1-\2/' INSERT INTO t1 VALUES(f1val, f2val, null, .., fnval); #all columns in created order. #specified columns only, others default: Not dependent on column (re)order: INSERT INTO t1 (fi, fj, fk) VALUES(val, val, val); INSERT INTO t1 () VALUES(); #all columns default value INSERT INTO t1 SET fi=val, fj=val, fk=val; #specified columns only #multiple rows inserted: more efficient than multiple Inserts INSERT INTO t1 VALUES (values for all columns),(values for all columns),... INSERT INTO t1 (fi,fj,fk) VALUES(3 values),(3 values),... #control behavior when attempt to insert duplicate unique key: INSERT IGNORE.... skip the dup attempt, else error abort (on multiple-row insert) REPLACE INTO .... replace existing with dup, or insert #do an update of row with duplicate unique key: else insert new row INSERT Into t1 (fi,fj,fk) Values(v1,v2,v3) ON DUPLICATE KEY UPDATE fi=expr; #single-row inserting null into non-null column is error (except Timestamp and Auto_increment columns) #multi-row inserting null into non-null column is warning, zero inserted. INSERT INTO t1 SELECT ... FROM ...; #subquery DELETE FROM t1; #all rows deleted. slow. auto_increment column reset TRUNCATE [TABLE] t1; #all rows deleted. fast. auto_increment column reset DELETE FROM t1 WHERE expr #delete rows matching the where expression DELETE FROM t1 WHERE 1; #delete all rows. auto_increment column Not reset DELETE FROM t1 WHERE expr LIMIT n; #delete some n of them DELETE FROM t1 WHERE expr ORDER BY fi LIMIT n; #delete the first n of them #delete rows that match field in another table. 2 syntaxes: DELETE FROM t1 USING t1,t2 WHERE t1.fi=t2.fj; DELETE t1 FROM t1,t2 WHERE t1.fi=t2.fj; UPDATE t1 SET fi=val; #all rows' fi changed UPDATE t1 SET fi=val WHERE expr #change fi to val of rows matched by where expression. rows-matched vs rows-affected. Timestamp column updated only if other column changes. Updating Not Null column to Null sets it to default. #useful if id=id-1 Order By id: UPDATE t1 SET fi=val [Where...] ORDER BY fi; UPDATE t1 SET fi=val WHERE expr LIMIT n; UPDATE t1 SET fi=val WHERE expr ORDER BY fi LIMIT n; #update table based on another table: UPDATE t1,t2 SET t1.fi=t2.fi WHERE t1.id=t2.id; ********************** Functions: string math date-time encryption Chapter 13 version() curdate() curtime() now() user() database() current DB uuid() number globally unique in space and time (timestamp+NIC) round(num[,decimals]) round to nearest integer [or to #decimals] truncate(num,decimals) ,0 chop to int rand() 0<=rand()<1 pow(2,4) sqrt(2) mod(7,2) % and MOD too DIV for integer division format(num,decimals) display as #,###,###.ddd But changes to string! length(string) length(string)-length(replace(string,' ',''))+1 count of # words left(string,prefixLength) right(string,suffixlength) concat(string1,string2,...) concat('$ ',monetaryField) prepend $ concat('$ ',format(monetaryField,2)) replace(string,substringMatch,replacementString) locate substr compress(string) monthname(date) month(date) 1-12 year(date) date_add(datetime, INTERVAL 40 DAY) date_format(datetime, '%W %M %D %Y %r') datediff(d1,d2) d1-d2 in days /365 for years if(condition,true_branch,false_brand) C ?: conditional operator Aggregate/column functions: count sum min max avg variance stddev group_concat various bitwise ********************** # export resultset to shell: $mysql db1 -e "select * from t1" Output format: -B (no borders) (CMD doesn't allow ' ?) -H (html table) -X (xml) $mysql -e "use menagerie;select * from pet;" #redirect output to file: $mysql -e "select ....." >outfilename tee tooutfile Log interactive to file (like typescript in shell) ... all this being copied to the totoutfile notee Stop the logging Output to text file on server: SELECT selectList INTO OUTFILE 'outfilename' FROM ... must have FILE privilege. file must not already exist. same default format and options as Load Data Local Infile. file is world-writable, owned by mysql. Useful for reloading with LOAD DATA INFILE... (i.e. not LOCAL) use: mysql -e "select ....." >outfilename to output to file on client ********************** backup a db to a text file (creates a SQL script file of Create Table and Inserts) #one or more tables from one database: $mysqldump db [tablenames] #structure and data #all tables from many databases: $mysqldump --databases db1 db2 #all tables from all databases: $mysqldump --all-databases --opt turns on lots of useful options lots of other configuration options... Can restore by: $mysql dbname outputfile or: | more -- or from within mysql as: source thisbatchfile -- or as: \. thisbatchfile use menagerie; select * from pet; select '----------------------------------------------------------'; select owner as 'Cat owners:' from pet where species='cat'; ********************** Indexes 1.speedup search on indexed columns. (primary optimization technique) 2.some enforce uniqueness. Index and Unique can be named. Primary Key cannot be named (its name is Primary). Can be multiple Indexes and Uniques but only one Primary Key. All can be composite. Index index is non-unique; for retrieval speedup purposes only. create table t1 (..., fi datatype,..., INDEX [indexname] (fi)) #can name it CREATE INDEX indexname ON t1 (fi) #must name it alter table t1 Add INDEX [indexname] (fi) alter table t1 Drop INDEX indexname|fi DROP INDEX indexname ON t1 #name must be used Foreign Key is an Index. Unique index uniqueness enforced but if column can have Null, multiple Nulls are allowed. create table t1 (..., fi datatype UNIQUE, ...) #if one column only create table t1 (..., fi datatype,..., UNIQUE [indexname] (fi)) #can name it alter table t1 Add UNIQUE [indexname] (fi) CREATE UNIQUE INDEX indexname ON t1 (fi) #must name it alter table t1 Drop INDEX indexname|fi #must use indexname if has one DROP INDEX indexname ON t1 #name must be used Primary Key index is unique, non-null. Equivalent to Unique and Not Null. create table t1 (..., fi datatype PRIMARY KEY, ...) #if one column only create table t1 (..., fi datatype,..., PRIMARY KEY(fi)) alter table t1 Add PRIMARY KEY(fi) alter table t1 Drop PRIMARY KEY DROP INDEX `PRIMARY` On t1 must be backquoted Create Index can not do Primary Key Fulltext index for text searching on MyISAM table only. Information about table's indexes: Desc t; Show Create Table t; SHOW INDEX FROM t [FROM db]; ********************** Identifiers (names of databases, tables, columns, indexes, aliases, variables, triggers, procedures) letters digits _ $ can start with any, but best to avoid starting with digit. cannot consist of digits cannot be a reserved word (SQL keyword or function name). Quoted identifiers: within backquotes ` (and " if --ansi or --sql-mode=ANSI_QUOTES) Any character except Ascii 0 and 255. Can be reserved word. Can be all digits. Database and table names also not . / \ (or others not allowed by OS) " and ' usually delimit a string. ` can quote an identifier. Alias can be any characters but should be quoted (in ' " `) if is reserved word, has special chars, or is all digits. ********************** Case sensitivity: Database and table names, because they correspond to directories and files, respectively, case-sensitive on Unix, insensitive on Windows (but case must be consistent thruout a query). Column, index, trigger, routine are case insensitive (but Tab completion is case sensitive). fi=FI Reserved words and functions are case insensitive. select=SELECT user()=UsEr() User variables are case sensitive. @myvar!=@myVar Aliases are case sensitive on table names only (XP too?), insensitive otherwise. Strings are case insensitive unless BINARY ********************** User Variables: per-user per-connection SET @varname=expr_value; # := can be used instead of = select @varname; #are case sensitive. integer, real, string, Null values only. scalar only. SET @sumpop=(Select sum(population) from country); Select name,population,population/@sumpop From country Where population>.1*@sumpop; SET @tenthLargest = (Select surfacearea From country Order By surfacearea Desc Limit 10,1); select @tenthlargest; #Sudan's pop. select sum(surfacearea) from country where surfacearea>=@tenthlargest and name!='antarctica'; set @avgpop=(select avg(population) from country); # value closest to average value: select name,population,abs(population-@avgpop) as deviationAvg from country order by deviationAvg limit 1; Subqueries can accomplish all of the above. cannot be used in Limit :( SHOW VARIABLES; #system variables. global and per session; some are constants SET GLOBAL key_buffer=50M; #applies to all new threads SET SESSION sort_buffer_size=50M; #applies to this thread only ********************** Prepared statements. sort of like a macro or function. per connection only. PREPARE pStmtName FROM 'sql statement ?as formal argument'; EXECUTE pStmtName USING @varname; DEALLOCATE PREPARE pStmtName; #dropped at connection end anyway Prepare namePop From 'Select Name, Population From Country Where code=?'; Set @v1='usa'; Execute namePop Using @v1; Set @v1='jpn'; Execute namePop Using @v1; Efficiency of sql statement only parsed once by server, upon Prepare. ********************** Comments # to end of line -- to end of line (space after --) /* embedded, multiline C-style comment */ /*! portability comment. not a comment for MySQL but is for other SQLs. allows MySQL-specific stuff ignored by others */ /*!32300 same but ignored by older than 3.23.00 versions of MySQL */ ********************** MyISAM table t1 has 4 binary files in its db's directory: t1.frm table definition t1.MYD data t1.MYI indexes db.opt properties these 3 files are the complete info of the tables. copying/moving/deleting them copies/moves/deletes the table. can be symlinked to other disk/partition for performance or safety. 3 formats: fixed-length rows if no variable-length columns (varchar,text,blob). dynamic rows. compressed to read-only by myisampack utility. Utility to check and repair MyISAM tables by operating directly on the files. Run on server computer, usually when mysqld down, or lock the tables. Need read (check) and write (repair) permissions on files. $ myisamcheck db [tables] --databases db1 db2... --all-databases default is --check|-c --recover|-r --analyze|-a (optimize indexes?) Cmd line interface to server's CHECK|REPAIR TABLE $ mysqlcheck db [tables] --databases db1 db2 --all-databases same options are myisamcheck. can check InnoDB tables too. InnoDB table t1 (default on Windows if Configuration Wizard used) only has: t1.frm table definition db.opt Data is in tablespace shared by all InnoDB tables in data directory: ibdata1 etc. can be multiple files and raw partitions. SHOW INNODB STATUS; status of InnoDB storage engine ********************** $mysql -u root #logon as mysql's admin (not system root) #first time use to tighten security: use mysql select user,host,password from user; #2 roots, 2 anonymous OR select user,host,password from mysql.user; #2 roots, 2 anonymous select * from user where user=''; #2 anonymous's privileges select * from user where user='' and host='%'; #network anonymous's privileges grant select on *.* to ''@'%'; #network anonymous can do selects on all DBs #delete the anonymous accounts: delete from mysql.user where user=''; flush privileges; #change roots' password: set password for 'root'@'localhost = password('newpasswd'); set password for 'root'@'fostix' = password('newpasswd'); OR $mysqladmin -u root -h fostix password='newpasswd' $mysqladmin -u root password='newpasswd' #localhost root OR (both roots at once): update mysql.user set password = password('newpasswd') where user ='root'; flush privileges; $mysql -e "select host,db,user from db" mysql $mysql -e "select user,host,password from user" mysql -u root -p ********************** #create users: #connection privilege: GRANT usage on *.* to joedoe@localhost identified by 'newpasswd'; #more privileges on widgets DB: grant select,insert,update,delete on widgets.* to joedoe@localhost; grant all privileges on widgets.* to joedoe@localhost; SHOW GRANTS [FOR user]; #suitable for duplication SHOW PRIVILEGES; #privileges that are supported select * from mysql.db; REVOKE select on widgets.* from joedoe@localhost; revoke all privileges on widgets.* from joedoe@localhost; revoke all privileges on *.* from joedoe@localhost; delete from user where user='joedoe'; #delete user flush privileges; ;create a user and assign privileges: grant select on widgets.* to joedoe@localhost identified by 'newpasswd'; ********************** $mysqladmin ping|status|version|processlist|variables|shutdown|create db|drop db ... $mysqlshow [db [table [column]]] -i other info ********************** Things you might think you can do but can't: aggFunc in Where alias in Where select fi,aggFunc without a Group By. The one fi value of resultset will be meaningless. select fi,fj,aggFunc both fi and fj should be in Group By Order By and With Rollup Order By in a subquery OK now! 5 Order By Count(*) (but its alias can be)