MySQL Common Part II

jbrown 10 March, 2008 21:31 Databases Permalink Trackbacks (0)

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 or n)

Example:

mysql> LOAD DATA LOCAL INFILE 'C:/menagerie-db/pet.txt' INTO TABLE pet;

| Slim     | Benny  | snake    | m    | 1996-04-29 | NULL       |
|          | NULL   | NULL     | NULL | NULL       | NULL       |
| Puffball | Daine  | hampster | f    | 1999-03-30 | 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 < batch_file
(Use -t for nice table layout and -vvv for command echoing.)
Alternatively: mysql> 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.)

Download MySQL HERE - http://dev.mysql.com/downloads/mysql/5.0.html


comments

  1. 24 hour payday loan cheap loan long payday term check cashing usa payday loans no checking account needed ameriloan phone number instant online payday advances hershey candy bar emergency cash advance loans payday loan no fax bad credit payday loans hershey chocolate nutritional information payday yes debt consolidation loans bad credit cashland ten dollar payday loan check cashing locations los angeles ca Please, send your abuse here!!! send.your.abuse.here@gmail.com

    Posted by pmasnmu — 27 Oct 2009, 01:54

  2. faxless payday advances paydaymax california check cashing locations sacramento debt consolidation 100 online payday loans payday lenders ace check cashing centers in houston fast payday loan application payday advance loans locations check into cash unsecured bad credit personal loans no teletrack no credit check no fax payday loans payday lenders in virginia payday loans online bad credit money tree faxless fast payday loans speedy cash online payday loan service faxless payday advance payday loan uk 24 hour check cashing stores in atlanta cash advance credit card rates sonic payday ltd magnum payday personal loans online phone numbers

    Posted by oihunqs — 29 Oct 2009, 17:40

  3. cash advance loans no faxing the check cashing store locations payday loans Nashville Davidson cash lenders credit counseling payday loan store of indiana bad credit cash advance cash advance fee capital one online cash advance no fax payday loans quick faxless payday loans personal debt consolidation loan fast loan personal small powerball winning numbers my payday loan yes feesinstant payday loan check cashing services portland oregon payday loan Tucson Please, send your abuse here!!! send.your.abuse.here@gmail.com

    Posted by hhcxbwz — 30 Oct 2009, 04:05

  4. direct lender payday loans personal cash loans no credit check no fax payday loans with fees payday loans uk no faxing pachira teletrack payday loans payday loans bad credit no faxing gary coleman loan cash america pawn shop guaranteed cash loans ameriloan payday loan fees pay day one cash advance instant online payday advance loans payday ok payday lending 1 hour online cash advance lenders capital one personal loans overnight student loans urgent cash advance instant approval cash advances payday loan uk capital one pls payday loan store chicago south dakota wikipedia guaranteed bad credit personal loan

    Posted by brqdkoz — 31 Oct 2009, 17:58

  5. no faxing online payday loans cash advance 100 online loans instant approval payday loans faxless payday cash advance payday loans no faxing required cash loan lenders foreclosures predatory lending 24 hour check cashing services ace cash advance locations in houston texas payday cash advances credit counseling payday loan complaints check cashing store fees payday loan cash advance locations payday loans in 1 hour payday loan store indianapolis overnight payday loans check cashing services new york instant payday advance cash advance loans in georgia payday express reviews payday select loans borrowers ace cash advance san antonio texas united cash loans address faxless online payday loan lenders

    Posted by lrkwoxz — 03 Nov 2009, 20:22

  6. payday advance loans locations payday loans for bad credit no faxing advance auto parts ace check cash express locations check cashing instant payday advance loans stores payday loan Colorado Springs approval payday cash advance loans paydaymax review no fax payday advance loan instant no fax payday loans online no fax payday loan lenders guaranteed no fax payday advances payday loans online approval instant online payday loan no fax payday loans no faxing no employment verification long term payday loans pay day now 1000 no fax payday loan list of online payday loan companies payday loans las vegas nv payday loans online no employment verification ameriloan payday loan contact number ameriloan payday loan contact number phone number payday cash advance loan payday loans Atlanta

    Posted by sicutfq — 04 Nov 2009, 21:12

  7. instant faxless payday loan 30 day payday loan cash advance locations in maryland 100 online cash advance united check cashing locations in pennsylvania california check cashing stores llc check cashing services at walmart paycheck advance loans virginia advance america payday loan home loan bad credit instant faxless payday loan instant faxless cash advance payday select cash advance payday loans Colorado Springs cash lenders payday loan yes complaints payday loans no checking account payday lenders online payday select cash loan signature loans hershey medical center sonic payday 24 7 instant no fax payday loans savings account payday loans payday loans Colorado Springs

    Posted by kjeoqrb — 06 Nov 2009, 20:48

  8. bad credit mortgage payday loan debt relief payday loan debt relief payday loan debt relief payday loan debt relief ace check cash express houston texas nix check cashing long beach ca nix check cashing long beach ca payday loans no faxing fast no fax cash advance check cashing locations san diego nix check cashing long beach ca payday loans for unemployed cheap payday loans nix check cashing long beach ca nix check cashing long beach ca absolutely no fax payday loan pls payday loan store illinois payday loan debt relief emergency cash advance loans by phone quick faxless payday advances sonic payday advance loan direct deposit checks cash advance loans in ohio payday loans virginia beach

    Posted by fazbcuz — 07 Nov 2009, 20:50

  9. ameriloan payday loan phone number

    Posted by gadkwwx — 10 Nov 2009, 01:51

  10. ameriloan payday loan phone number

    Posted by bbfsuin — 10 Nov 2009, 02:20

  11. ace check cashing place quick online payday loans uk cash advance payday loan sonic payday land login payday cash advance loans today cash advance fee on credit card check cashing store florida locations payday loan online cash advance canada payday loans las vegas no faxing savings payday loans mortgage calculator moneygram south dakota powerball numbers payday loan Boston payday loan consolidation help sonic payday land login instant no fax cash advance loans cash advance credit card interest personal loan rates check cashing fees california check cashing store florida locations cashnetusa payday advance quick online payday loans uk payday loan services bad credit installment loans

    Posted by ujdjsre — 11 Nov 2009, 01:39

  12. interesting opinion, thanks, ambien generics no prescription, 8OOO,

    Posted by mnifojaf@elobvvum.com — 11 Nov 2009, 21:42

  13. paycheck advancements no fax bad credit payday loans quick loans payday loan no faxing required faxless cash advances

    Posted by wfwaxtr — 12 Nov 2009, 02:44

  14. paycheck advancements no fax bad credit payday loans quick loans payday loan no faxing required faxless cash advances

    Posted by nrfnftv — 13 Nov 2009, 03:04

  15. the check cashing store florida

    Posted by dtzeipc — 14 Nov 2009, 11:09

  16. the check cashing store florida

    Posted by hypzfxo — 14 Nov 2009, 11:25

  17. ace check cash express locations houston no credit check no fax payday loans loans calculator quick payday loans online cash advance online lenders south dakota powerball numbers 24 hour check cashing stores sonic payday land cash loan nix check cashing inglewood ca payday uk loans mortgage calculator western union pay day america payday loans online reviews 24 hour check cashing stores in new york Please, send your abuse here!!! send.your.abuse.here@gmail.com

    Posted by qqhupek — 16 Nov 2009, 10:38

  18. the check cashing store florida

    Posted by bzphjdk — 16 Nov 2009, 14:08

  19. no teletrak payday loans

    Posted by qyhrqna — 17 Nov 2009, 06:02

  20. sonic payday finance companies payday one loans alternative payday loans Indianapolis payday loans calgary alberta paydayone reviews the check cashing store florida magnum funding ontario encore winning numbers payday loans online bad credit

    Posted by msiihvt — 18 Nov 2009, 12:36


Add comment

Add comment

Powered by LifeType
© 2006 - Design by Omar Romero (all rights reserved)