Search This Blog

2011-10-01

mysql cheat sheet

-- File:MySqlCheatSheet.sql v.1.1.0 docs at the end 
 
/* [mysql dir] */
/bin/mysql -h hostname -u root -p 
 
mysql -u userName -p  -D DbName -B -e "select * from users;"   > /home/yogeorgi/tmp/list.csv ;
mysql -u userName -p  -D information_schema -B -e "select columns from columns;"   > /home/yogeorgi/tmp/list.csv ;
 
-- Batch mode (feeding in a script): 
mysql -u user -p < batch_file
/* (Use -t for nice table layout and -vvv for command echoing.) */
-- Alternatively: 
source batch_file;
 
 
-- List all databases on the mysql server. 
show databases;
 
/* Switch to a database. */
use [db name];
 
-- get all the tables in the currently selected database 
show tables;
 
-- get the table's fields format 
describe [TableName];
 
-- to remove a db
drop database [database name];
 
-- to drop a table 
drop table [TableName];
 
/* Show all data in a table. */
SELECT * FROM [TableName];
 
/* Returns the columns and column information pertaining to the designated table.  */
show columns from [TableName];
 
/* Show certain selected rows with the value "whatever". */
SELECT * FROM [TableName] WHERE [field name] = "whatever";
 
/* Show all records containing the name "Bob" AND the phone number '3444444'.  */
SELECT * FROM [TableName] WHERE name = "Bob" AND phone_number = '3444444';
 
/* Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.  */
SELECT * FROM [TableName] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;
 
/* Show all records starting with the letters 'bob' AND the phone number '3444444'.  */
SELECT * FROM [TableName] WHERE name like "Bob%" AND phone_number = '3444444';
 
-- Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. 
-- This finds any record beginning with a. 
SELECT * FROM [TableName] WHERE rec RLIKE "^a$";
 
-- how-to show only unique records. 
SELECT DISTINCT [column name] FROM [TableName];
 
/* Show selected records sorted in an ascending (asc) or descending (desc).  */
SELECT [col1],[col2] FROM [TableName] ORDER BY [col2] DESC;
 
/* Return number of rows. */
SELECT COUNT(*) FROM [TableName];
 
/* Sum column. */
SELECT SUM(*) FROM [TableName];
 
/* Join tables on common columns.  */
 
select lookup.illustrationid, lookup.personid,person.birthday from lookup
left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;
 
/* Switch to the mysql db. Create a new user.  */
 
INSERT INTO [TableName] (Host,User,Password) VALUES('%','user',PASSWORD('password'));
 
-- Change a users password.(from unix shell). 
[mysql dir]/bin/mysqladmin -u root -h  hostname.blah.org -p password 'new-password'
 
-- Change a users password.(from MySQL prompt).
SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
 
/* Allow the user "bob" to connect to the server from localhost using the password "passwd"  */
grant usage on *.* to bob@localhost identified by 'passwd';
 
/* Switch to mysql db.Give user privilages for a db. */
INSERT INTO [TableName] (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N'); 
 
/* OR  */
grant all privileges on databasename.* to username@localhost;
 
/* To update info already in a table. */
UPDATE [TableName] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user'; 
 
/* ADelete a row(s) from a table. */
DELETE from [TableName] where [field name] = 'whatever';
 
-- how-to Update database permissions/privilages. 
FLUSH PRIVILEGES;
 
-- how-to delete a column
alter table [TableName] drop column [column name]; 
 
-- how-to add a new column to table 
alter table [TableName] add column [new column name] varchar (20);
 
-- how-to change a column name in a table 
alter table [TableName] change [old column name] [new column name] varchar (50);
 
-- Make a unique column so you get no dupes.  
alter table [TableName] add unique ([column name]); 
 
-- Make a column bigger
alter table [TableName] modify [column name] VARCHAR(3);
 
-- Delete unique from table. 
alter table [TableName] drop index [colmn name]; 
 
--how-to load a CSV file into a table.
LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [TableName] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
 
/* Dump all databases for backup. Backup file is sql commands to recreate all db's.  */
[mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
 
-- Dump one database for backup. 
[mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql 
 
-- Dump a table from a database
[mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
 
-- Restore database (or database table) from backup. 
[mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql 
 
/* Create Table Example 1. */
CREATE TABLE [TableName] (
  FirstName                 VARCHAR(20)
, MiddleName             VARCHAR(3)
, LastName                 VARCHAR(35)
, Suffix                     VARCHAR(3)
, OffficeId                     VARCHAR(10)
, UserId                     VARCHAR(15)
, UserName             VARCHAR(8)
, Email                     VARCHAR(35)
, Phone                     VARCHAR(25)
, Groups                     VARCHAR(15)
, DateStamp             DATE
, TimeStamp             time
, PgpEmail                 VARCHAR(255)
); 
 
-- Create Table Example 2. 
create table [TableName] (
   personid int(50) not null auto_increment primary key
 , firstname varchar(35)
 , middlename varchar(50)
 , lastnamevarchar(50) default 'bato'
);
 
 
-- Inserting one row at a time: 
INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');
(Use NULL for NULL)
 
-- Retrieving information (general):
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:
SET AUTOCOMMIT=1; # used for quick recreation of table
 
DELETE FROM pet;
 
LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;
 
-- Fixing all records with a certain value:
UPDATE table SET column_name = "new_value" WHERE record_name = "value";
 
-- Selecting specific columns:
SELECT column_name FROM table;
 
-- Retrieving unique output records:
SELECT DISTINCT column_name FROM table;
 
-- Sorting:
SELECT col1, col2 FROM table ORDER BY col2;
Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;
 
--Date calculations:
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:
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:
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"
 
 
-- Grouping with Counting:
SELECT owner, COUNT(*) FROM table GROUP BY owner;
(GROUP BY groups together all records for each 'owner')
 
-- Selecting from multiple tables:
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:
SELECT DATABASE();
 
-- Maximum value:
SELECT MAX(col_name) AS label FROM table;
 
-- Auto-incrementing rows:
CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);
INSERT INTO tabe (name) VALUES ("tom"),("dick"),("harry");
 
-- Adding a column to an already-created table:
ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;
 
-- Removing a column:
ALTER TABLE tbl DROP COLUMN col;
(Full ALTER TABLE syntax available at mysql.com.)
 
-- Create a database on the sql server. */
create database [databasename];
 
-- 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.)
 
-- how-to export to csv the db's metadata 
use information_schema ; 
 
SELECT     TABLE_CATALOG  , TABLE_SCHEMA  , TABLE_NAME  , COLUMN_NAME  , ORDINAL_POSITION  , COLUMN_DEFAULT  , IS_NULLABLE  , DATA_TYPE  , CHARACTER_MAXIMUM_LENGTH  , CHARACTER_OCTET_LENGTH  , NUMERIC_PRECISION  , NUMERIC_SCALE  , CHARACTER_SET_NAME  , COLLATION_NAME  , COLUMN_TYPE  , COLUMN_KEY  , EXTRA  , PRIVILEGES  , COLUMN_COMMENT
INTO OUTFILE '/var/lib/mysql/information_schema.csv'
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY ' ' 
LINES TERMINATED BY '\n' 
from COLUMNS; 
 
 
-- Purpose: 
-- This is a list of handy MySQL commands that I use time and time again. 
-- At the bottom are statements, clauses, and functions you can use in 
-- To login (from unix shell) use -h only if needed. 
-- Shamelessly stolen from Internet and forgot to mention the sources ... 
 
-- VersionHistory
-- 1.1.0 --- ysg --- docs, formatting 
-- 1.0.0 --- ysg --- Hard copy paste from Internet ... 

Labels

perl (41) Cheat Sheet (25) how-to (24) sql server 2008 (13) windows (13) linux (12) oracle (12) sql (12) Unix (11) cmd windows batch (10) mssql (10) cmd (9) script (9) textpad (9) netezza (8) sql server 2005 (8) cygwin (7) meta data mssql (7) metadata (7) code generation (6) Informatica (5) bash (5) cheatsheet (5) energy (5) tsql (5) utilities (5) excel (4) future (4) generic (4) html (4) perl modules (4) programs (4) settings (4) sh (4) shortcuts (4) поуки (4) принципи (4) Focus Fusion (3) Solaris (3) cool programs (3) development (3) economy (3) example (3) freeware (3) fusion (3) git cheat sheet (3) morphus (3) mssql 2005 (3) nuclear (3) nz (3) parse (3) python (3) sftp (3) sofware development (3) source (3) sqlplus (3) table (3) vim (3) .Net (2) C# (2) China (2) GUI (2) Google (2) GoogleCL (2) Solaris Unix (2) ascii (2) awk (2) batch (2) cas (2) chrome extensions (2) code2html (2) columns (2) configuration (2) conversion (2) duplicates (2) excel shortcuts (2) export (2) file (2) free programs (2) informatica sql repository (2) logging (2) mssql 2008 (2) mysql (2) next big future (2) nsis (2) nz netezza cheat sheet (2) nzsql (2) ora (2) prediction (2) publish (2) release management (2) report (2) security (2) single-click (2) sqlserver 2005 (2) sqlserver 2008 (2) src (2) ssh (2) template (2) tools (2) vba (2) video (2) xlt (2) xml (2) youtube videos (2) *nix (1) .vimrc (1) .virmrc vim settings configs (1) BSD license (1) Bulgaria (1) Dallas (1) Database role (1) Dense plasma focus (1) Deployment (1) ERP (1) ExcelToHtml (1) GD (1) GDP (1) HP-UX (1) Hosting (1) INC (1) IT general (1) ITIL management bullshit-management (1) IZarc (1) Java Web Start (1) JavaScript anchor html jquery (1) Khan Academy (1) LINUX UNIX BASH AND CYGWIN TIPS AND TRICKS (1) Linux Unix rpm cpio build install configure (1) Linux git source build .configure make (1) ListBox (1) MIT HYDROGEN VIRUS (1) OO (1) Obama (1) PowerShell (1) Run-time (1) SDL (1) SIWA (1) SOX (1) Scala (1) Services (1) Stacks (1) SubSonic (1) TED (1) abstractions (1) ansible hosts linux bash (1) ansible linux deployment how-to (1) ansible yum pip python (1) apache (1) apache 2.2 (1) application life cycle (1) architecture (1) archive (1) arguments (1) avatar (1) backup (1) bash Linux open-ssh ssh ssh_server ssh_client public-private key authentication (1) bash perl search and replace (1) bash stub (1) bin (1) biofuels (1) biology (1) books (1) browser (1) bubblesort (1) bugs (1) build (1) byte (1) cas_sql_dev (1) chennai (1) chrome (1) class (1) claut (1) cmdow (1) code generation sqlserver (1) command (1) command line (1) conf (1) confluence (1) console (1) convert (1) cool programs windows free freeware (1) copy-paste (1) csv (1) ctags (1) current local time (1) cygwin X11 port-forwarding mintty xclock Linux Unix X (1) cygwin bash how-to tips_n_tricks (1) cygwin conf how-to (1) data (1) data types (1) db2 cheat sheet (1) db2 starter ibm bash Linux (1) debt (1) diagram (1) dictionaries (1) digital (1) disk (1) disk space (1) documentation (1) dos (1) dubai (1) e-cars (1) electric cars (1) electricity (1) emulate (1) errors (1) exponents (1) export workflow (1) extract (1) fast export (1) fexp (1) file extension (1) file permissions (1) findtag (1) firewall (1) for loop (1) freaky (1) functions (1) fusion research (1) german (1) git gitlab issues handling system (1) google cli (1) google code (1) google command line interface (1) gpg (1) ha (1) head (1) helsinki (1) history (1) hop or flop (1) host-independant (1) how-to Windows cmd time date datetime (1) ibm db2 cognos installation example db deployment provisioning (1) ideas (1) image (1) informatica oracle sql (1) informatica repo sql workflows sessions file source dir (1) informatica source files etl (1) install (1) it management best practices (1) java (1) jump to (1) keyboard shortcuts (1) ksh (1) level (1) linkedin (1) linux bash ansible hosts (1) linux bash commands (1) linux bash how-to shell expansion (1) linux bash shell grep xargs (1) linux bash tips and t ricks (1) linux bash unix cygwin cheatsheet (1) linux bash user accounts password (1) linux bash xargs space (1) linux cheat sheet (1) linux cheat-sheet (1) linux cheatsheet cheat-sheet revised how-to (1) linux how-to non-root vim (1) linux ssh hosts parallel subshell bash oneliner (1) london (1) make (1) me (1) metacolumn (1) metadata functions (1) metaphonre (1) method (1) model (1) movie (1) multithreaded (1) mysql cheat sheet (1) mysql how-to table datatypes (1) n900 (1) nano (1) neteza (1) netezza bash linux nps (1) netezza nps (1) netezza nps nzsql (1) netezza nz Linux bash (1) netezza nz bash linux (1) netezza nz nzsql sql (1) netezza nzsql database db sizes (1) non-password (1) nord pol (1) nps backup nzsql schema (1) number formatting (1) nz db size (1) nz table count rows (1) nzsql date timestamp compare bigint to_date to_char now (1) on-lier (1) one-liners (1) one-to-many (1) oneliners (1) open (1) open source (1) openrowset (1) oracle PL/SQL (1) oracle Perl perl (1) oracle installation usability (1) oracle number formatting format-model ora-sql oracle (1) oracle templates create table (1) oracle trigger generic autoincrement (1) oracle vbox virtual box cheat sheet (1) oracle virtual box cheat sheet (1) outlook (1) parser (1) password (1) paths (1) perl @INC compile-time run-time (1) perl disk usage administration Linux Unix (1) perl modules configuration management (1) permissions (1) php (1) picasa (1) platform (1) postgreSQL how-to (1) powerShell cmd cygwin mintty.exe terminal (1) ppm (1) predictions (1) prices (1) principles (1) productivity (1) project (1) prompt (1) proxy account (1) public private key (1) publishing (1) putty (1) qt (1) read file (1) registry (1) relationship (1) repository (1) rm (1) scp (1) scripts (1) scsi (1) search and replace (1) sed (1) sendEmail (1) sh stub (1) shortcuts Windows sql developer Oracle (1) sidebar (1) silicon (1) smtp (1) software procurement (1) sofware (1) sort (1) sql script (1) sql_dev (1) sqlcmd (1) sqlite (1) sqlite3 (1) sshd (1) sshd cygwin (1) stackoverflow (1) stored procedure (1) stub (1) stupidity (1) subroutines (1) svn (1) sysinternals (1) tail (1) tar (1) temp table (1) templates (1) teradata (1) terminal (1) test (1) testing (1) theory (1) thorium (1) time (1) tip (1) title (1) tmux .tmux.conf configuration (1) tmux efficiency bash (1) tool (1) ui code prototyping tips and tricks (1) umask Linux Unix bash file permissions chmod (1) url (1) urls (1) user (1) utility (1) utils (1) vb (1) vim perl regex bash search for string (1) vim recursively hacks (1) vim starter (1) vim-cheat-sheet vim cheat-sheet (1) vimeo (1) visual stuio (1) warsaw (1) wiki (1) wikipedia (1) window (1) windows 7 (1) windows 8 (1) windows programs (1) windows reinstall (1) windows utility batch perl space Windows::Clipboard (1) wisdoms (1) workflow (1) worth-reading (1) wrapper (1) xp_cmdshell (1) xslt (1) youtube (1)

Blog Archive

Translate with Google Translate

My Blog List

VideoBar

This content is not yet available over encrypted connections.