Sign In
|
Register
> Browse
all docs
DocStore
Legal
Business
Personal Finance
Technology
Education
Jobs & Careers
Tax
Real Estate
Current Events
Politics & History
Guides
Science
Entertainment
Health & Fitness
Medicine
Conferences
Art & Literature
Lifestyle
Travel
Templates
> Featured
> Browse
MySQL Command
Reviews
Shared by:
Ganesh Nalawade
Categories
Technology
>
Computers & Internet
Tags
mysql
,
IT
,
database
,
opensource
Stats
views:
1075
rating:
not rated
reviews:
0
posted:
11/6/2007
language:
pages:
0
Public Domain
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 < 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.)
Shared by:
Ganesh Nalawade
Ganesh Nalawade
IT
System Administrator
WalMart.com
walmart.com
contact me
In order to send a message you need to be logged in.
About
http://ganesh.nalawade.googlepages.com/aboutme
Other docs by
Ganesh Nalawad...
Budget Planner
Views: 728 | Downloads: 176
All Car Details
Views: 794 | Downloads: 121
Indian Languages cheat sheet
Views: 1343 | Downloads: 196
Indian Tourist Place- Western Maharashtra
Views: 541 | Downloads: 63
MOSS 2007 Installation
Views: 1112 | Downloads: 134
Indian Premier League details
Views: 518 | Downloads: 44
Vmware VM, ESX, GSX Cheat Sheet
Views: 1452 | Downloads: 243
Automotive Terminology
Views: 1021 | Downloads: 86
Personal Finance Management Sheet
Views: 1656 | Downloads: 343
City Bus routes in Mumbai Metro City
Views: 1241 | Downloads: 46
Indian Union Budget Highlights
Views: 722 | Downloads: 17
Creative Objects
Views: 760 | Downloads: 35
Beauty of colors
Views: 647 | Downloads: 60
Configure DNS in Linux
Views: 1552 | Downloads: 202
Windows Keyboard Shortcuts
Views: 2124 | Downloads: 419
Related docs
Mysql
Views: 935 | Downloads: 111
MySQL Command
Views: 24 | Downloads: 3
MySQL Tutorial
Views: 125 | Downloads: 38
mysql examples
Views: 307 | Downloads: 62
PHP and MySQL
Views: 499 | Downloads: 98
mysql to xml
Views: 119 | Downloads: 42
MySQL
Views: 3052 | Downloads: 59
MySQL Tutorial
Views: 225 | Downloads: 83
MYSQL
Views: 11 | Downloads: 0
Mysql tutorial
Views: 10 | Downloads: 1
mysql-tutorial
Views: 397 | Downloads: 24
How to Create MySQL Database using command line
Views: 4 | Downloads: 0
Best Practices for Deploying MySQL on the Solaris Platform
Views: 224 | Downloads: 19