MySQL

MySQL is used when developing a PHP application. MySQL is the most popular open source database system used with PHP. The mysqli extension is MySQL improved extension.

open a new connection to the MySQL server:
mysqli_connect();

mysql> CREATE TABLE cake (
  id int primary key not NULL auto_increment,
  name VARCHAR(100),
  flavor VARCHAR(100),
  layer INT(3) zerofill,
  sex ENUM('m', 'f') NOT NULL,
  bake datetime,
  serve date
);

INSERT INTO cake (name,flavor) VALUES ('bundt','chocolate');
INSERT INTO cake VALUES (2,'nirvana','vanilla',NULL,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>='2013-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 * FROM cake WHERE nut<>''; IS NOT NULL, nut=NULL

UPDATE cake SET flavor='vanilla' WHERE name='vanilla';
UPDATE cake SET layer='3' WHERE id=3;

ALTER table cake add flavor varchar(100) default NULL; add a column to your existing table
ALTER table cake change col1 col2 varchar(10) AFTER column_name; change a column name from col1 to col2
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

DELETE FROM cake WHERE id=3;
DROP TABLE if exists cake;

comparison operators: LIKE '%a'     % zero or more char, _ underscore is exactly one char and NOT LIKE

join

join is a SQL query performed by cross-referencing tables, through a shared column relationship.

SELECT * FROM url,urlb WHERE url.sid=urlb.sid;

left join

left join returns all rows from the left table with the matching rows in the right table.   table.colum

because the joined columns of both tables have the same name city:

SELECT rank,city,subcity FROM 
      region 
        LEFT JOIN 
      regionsub 
        USING (city)  
      ORDER BY    ;

Table 1 is customers, Table 2 is orders: get all customers, and any orders they might have.

SELECT customers.customerName, orders.orderId 
FROM customers 
LEFT JOIN orders 
ON customers.customerid=orders.customerid 
ORDER BY customers.customerName;

inner join

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

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';  
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 cake WHERE name REGEXP '^.{5}$'; 
begins with any char, quantifier notation 5 chars, so this matches any 5 chars

show create table state; shows the create table statement that creates the table.

state: CREATE TABLE state (
state_id int(2) not NULL auto_increment,
name varchar(16) default NULL,
PRIMARY KEY (id)
)

mysqldump

generate the backup using mysqldump utility, a command line utility that is used to generate the logical backup of the MySQL database.

mysqldump --help

mysqldump -u [user_name] -p [password] [database_name] [table_name] > [dumpfile_name.sql] 
the command prompts for the password. Provide the appropriate password. 

read the dump file back into the server: 
shell> mysql db1 < dump1.sql

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

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]

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

note:

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

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

AS age an alias is used

death IS NOT NULL for those with non-NULL values

mysql> use databaser;
mysql> show tables;
mysql> desc cake;
mysql> SELECT database();
mysql> SELECT version();

fix:  
\c   clear the current input, cancel a command

in the bash shell   ctrl+C to cancel out 

--help

YYYY-MM-DD is the expected date format

ASC   ascending 1,2,3   a,n,r default
DESC descending 3,2,1   r,n,a