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 tbl_nm_cake (name,flavor) VALUES ('bundt','chocolate');
INSERT INTO tbl_nm_cake VALUES (2,'nirvana','vanilla',NULL,NOW(),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