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 ...