Selecting a database:
mysql> USE database;
Listing databases:
mysql> SHOW DATABASES;
Listing tables in a db:
mysql> SHOW TABLES;
Describing the format of a table:
mysql> DESCRIBE table;
Creating a database:
mysql> CREATE DATABASE db_name;
Creating a table:
mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));
Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);
Load tab-delimited data into a table:
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;
(Use \n for NULL)
Inserting one row at a time:
mysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');
(Use NULL for NULL)
Retrieving information (general):
mysql> SELECT from_columns FROM table WHERE conditions;
All values: SELECT * FROM table;
Some values: SELECT * FROM table WHERE rec_name = "value";
Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";
Reloading a new data set into existing table:
mysql> SET AUTOCOMMIT=1; # used for quick recreation of table
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;
Fixing all records with a certain value:
mysql> UPDATE table SET column_name = "new_value" WHERE record_name = "value";
Selecting specific columns:
mysql> SELECT column_name FROM table;
Retrieving unique output records:
mysql> SELECT DISTINCT column_name FROM table;
Sorting:
mysql> SELECT col1, col2 FROM table ORDER BY col2;
Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;
Date calculations:
mysql> SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table];
MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.
Pattern Matching:
mysql> SELECT * FROM table WHERE rec LIKE "blah%";
(% is wildcard - arbitrary # of chars)
Find 5-char values: SELECT * FROM table WHERE rec like "_____";
(_ is any single character)
Extended Regular Expression Matching:
mysql> SELECT * FROM table WHERE rec RLIKE "^b$";
(. for char, [...] for char class, * for 0 or more instances
^ for beginning, {n} for repeat n times, and $ for end)
(RLIKE or REGEXP)
To force case-sensitivity, use "REGEXP BINARY"
Counting Rows:
mysql> SELECT COUNT(*) FROM table;
Grouping with Counting:
mysql> SELECT owner, COUNT(*) FROM table GROUP BY owner;
(GROUP BY groups together all records for each 'owner')
Selecting from multiple tables:
(Example)
mysql> SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;
(You can join a table to itself to compare by using 'AS')
Currently selected database:
mysql> SELECT DATABASE();
Maximum value:
mysql> SELECT MAX(col_name) AS label FROM table;
Auto-incrementing rows:
mysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);
mysql> INSERT INTO table (name) VALUES ("tom"),("dick"),("harry");
Adding a column to an already-created table:
mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;
Removing a column:
mysql> ALTER TABLE tbl DROP COLUMN col;
(Full ALTER TABLE syntax available at mysql.com.)
Batch mode (feeding in a script):
# mysql -u user -p <> source batch_file;
Backing up a database with mysqldump:
# mysqldump --opt -u username -p database > database_backup.sql
(Use 'mysqldump --opt --all-databases > all_backup.sql' to backup everything.)
(More info at MySQL's docs.)
MySQL Cheat Sheet
Create Flash charts with PHP/SWF Charts
PHP/SWF Charts brings interactive eye candy to your charts and graphs. This PHP tool generates 20 types of charts, including line, column, 3-D column, pie, 3-D pie, bar, scatter, and polar charts.
Rather than creating simple images, PHP/SWF Charts outputs Flash movies, which you can view in a browser that's running at least version 6 of Adobe Flash Player. You can click and drill down on the charts, which feature animated transitions and present values on mouse hover. You can update the charts with live data without reloading the page.
PHP/SWF Charts isn't an open source tool, but you can download it for free and use it with certain restrictions. Simply download one of the three archives (around 164KB each), unzip it, and place charts.swf, charts.php, and the charts_library/ directory on your Web server.
Now you're ready to start charting. Say you want to create a chart that plots users' display resolution according to W3Schools' browser statistics. Use this code to generate the chart:
//browser_chart.php -- Creates the chart
include "charts.php";
//setting the chart's attributes
$chart[ 'axis_value' ] = array ( 'max'=>60, 'font'=>"arial", 'bold'=>true, 'size'=>10 );
$chart[ 'draw' ] = array ( array ( 'type'=>"text", 'rotation'=>-90, 'bold'=>true, size=> '22', 'x'=>10, 'y'=>230, 'width'=>300, 'height'=>200, 'text'=>"Display Resolutions", 'h_align'=>"left", 'v_align'=>"top" ),
array ( 'type'=>"text", 'color'=>"000033", 'alpha'=>50, 'font'=>"arial", 'rotation'=>-90, 'bold'=>true, 'size'=>12, 'x'=>7, 'y'=>210, 'width'=>300, 'height'=>55, 'text'=>"(per 100 visits)", 'h_align'=>"center", 'v_align'=>"middle" ) ); //throwing in the data
$chart ['chart_data'] = array ( array ( "", "2003", "2004", "2005", "2006" ),
array ( "640x480", 2, 1, 0, 0 ),
array ( "800x600", 47, 37, 30, 20 ),
array ( "1024x768", 40, 47, 53, 57 ),
array ( "Higher", 6, 10, 12, 17 ),
array ("Unknown", 5, 5, 5, 6),
);
//send the data to charts.swf
SendChartData ( $chart );
?>
In this file, you specify the data you need the chart to plot. The chart can take data through PHP variables, as shown, or from a database. You also specify the various attributes of the chart, including its type, color, and axis information. The Tutorial and Reference sections on the project's Web site explain the attributes in detail.
Now create a file that displays the chart inside a Web page:
//display_chart.php -- Displays the chart
//include charts.php to access the InsertChart function
include "charts.php";
//include the flash file, the charts library and the php file that has the chart details
echo InsertChart ( "charts.swf", "charts_library", "browser_chart.php" );
//footer information
echo "source: W3C Browser Statistics
"; ?>
This file can also be an HTML document if you've configured your server to process PHP inside HTML pages.
That's all there is to it. If you still aren't impressed, take a look at the gallery of charts you can create using PHP/SWF Charts.
Subscribe to:
Posts (Atom)
