cocomas.com

MySQL

MySQL is the open source database.

INSERT INTO cake (name,flavor) VALUES ('bundt','chocolate');

INSERT INTO cake VALUES ('caro','vanilla',NULL,2,NOW(),NOW()); password('susie'),NULL,NOW()

SELECT name,layer FROM cake WHERE flavor='vanilla' ORDER BY layer DESC; descending 3,2,1 :ascending 1,2,3 ASC is default

SELECT * FROM cake WHERE bake>='2012-1-1';

SELECT * FROM cake WHERE flavor='c%' AND nut='pecan' LIMIT 2; OR, % to search for partial matches, select the top 2

SELECT * FROM cake WHERE (flavor='chocolate' AND nut='pecan') OR (flavor='chocolate' AND nut='walnut');

SELECT name,flavor FROM cake;

SELECT DISTINCT flavor FROM cake;

UPDATE cake SET flavor='plum' WHERE name='plum crumb';

UPDATE cake SET layer='3' WHERE id=3;

UPDATE cake SET nut=NULL WHERE id=3;

ALTER table cake add flavor varchar(100) default NULL; add a column to your existing table.

ALTER table cake drop flavor; drop to remove a column and all its data.

ALTER table cake modify flavor VARCHAR(12); modify a column_definition, e.g. from 10 to 12.

ALTER table cake change col1 col2 varchar(10) AFTER column_name; change a column name from col1 to col2.

DELETE FROM cake WHERE id=3;

---

comparison operators

LIKE '%a' % zero or more char, _ underscore is exactly one char
NOT LIKE

inner join

SELECT fn,ln,ci.st,zi INTO personsbak FROM persons WHERE ci='san diego': create a 'personsbak' table

selecting data from more than 1 table
create a 'personsorderbak' table that contains data from the 2 tables 'persons' and 'orders'
SELECT persons.ln,orders.num INTO personsorderbak FROM persons INNER JOIN orders ON persons.pid=orders.pid

pattern matching using regular expressions

SELECT * FROM cake WHERE name REGEXP '^b';   regexlib cheatsheet

SELECT * FROM cake WHERE name REGEXP '^b'; ^ match the start of a string

SELECT * FROM cake WHERE name REGEXP 'b$';$ match the end of a string

SELECT * FROM cake WHERE name REGEXP '^[br]'; begins with an explicit set of characters to match, [br] matches brian or brenda

SELECT * FROM pet WHERE name REGEXP '^.{5}$'; begins with any char, quantifier notation 5 chars, so this matches any 5 chars

---

SHOW CREATE TABLE example;

Table Create Table

example Create Table 'example' (
'id' int(11) default NULL,
'data' varchar(100) default NULL
)

---

mysqldump

mysql > mysqldump db1 tbl2 > dump.sql

mysql > mysqldump db1 tbl1 tbl2 tbl3 > dump.sql

note: without the --all-databases or --databases option, it does not contain CREAT DATABASE and USE statements and it is not necessary to specify a default into which to load the data

reload

mysql > mysql < dump.sql

reload

mysql > use db1;

mysql > LOAD DATA INFILE 't1.txt' INTO TABLE t1;

mysqldump --help mysqldump1
mysqldump databasename > dump.sql
mysqldump -u [username] -p [password] databasename > dump.sql

create a backup file which can rewrite an existing database without having to delete the older database manually first.
mysqldump --add-drop-table -u sadmin -p pass21 Customers > custback.sql
mysqldump -u [username] -p [password] [databasename] [table1 table2 table3]

mysqldump restore

to restore mysql database from a dump file
mysqldump -u [username] -p [password] databasename < dump.sql
and key in your database user password

---

SELECT * INTO OUTFILE '/bak/reference.txt' FROM cake; writes the selected rows to a file

LOAD DATA LOCAL INFILE 'filename' INTO TABLE cake; reads rows from a text file into a table

AS age an alias is used

death IS NOT NULL for those with non-NULL values