Greg Altimas Open source technolgy. Linux Operating Systems.
Note: You can surround a string with either single or double quotes. However, ANSI SQL standard specifies single quotes, so use ' ' .
To add record
INSERT INTO univ VALUES ("Brandywine Realty Trust","BDN",”NYSE”,"finc",1.5,"yes","")
To change servers of existing database
mysqldump -u user -p db-name > db-name.out
scp db-name.out user@ip:/backup
mysql -u user -p db-name > db-name.out
To create database
CREATE DATABASE dict;
To change character set
*To view current default character set
SHOW CREATE DATABASE dict;
*to change default character set
ALTER DATABASE dict DEFAULT CHARACTER SET utf8;
To create Tables
The id columns is a PRIMARY KEY and the two name columns are indexed together:
CREATE TABLE customer
(id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
last_name CHAR(20) NOT NULL, first_name CHAR(20) NOT NULL,
PRIMARY KEY (id),
INDEX (last_name, first_name))
Create a temporary table and make it a HEAP (in-memory) table for greater speed:
CREATE TEMPORARY TABLE tmp_table
(id MEDIUMINT NOT NULL UNIQUE, name CHAR(40))
TYPE = HEAP
To delete a column
ALTER TABLE tbl_name Drop column;
ex: ALTER TABLE stkMonNew drop exp_ret;
ALTER TABLE Stk.univ ADD COLUMN note VARCHAR(60)
To add a column:
ALTER TABLE tbl_name ADD column INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
ex: ALTER TABLE ordhist ADD tax enum('yes,'no') DEFAULT 'no';
ex: ALTER TABLE ordhist ADD settle_date DATE AFTER 2;
ALTER TABLE Stk.univ ADD COLUMN note VARCHAR(60)
To change a column declaration:
ex. ALTER TABLE stkMonNew CHANGE volume bigint;
ex: ALTER TABLE ordhist ADD tax enum('yes,'no') DEFAULT 'no';
ex: ALTER TABLE ordhist ADD settle_date DATE AFTER 2;
To delete records:
DELETE FROM tbl_name WHERE which records to delete
ex. DELETE FROM StkMonNew WHERE date like '2005-05%';
To add records:
UPDATE tbl_name SET which columns to change WHERE which records to update
ex. UPDATE univ SET active=’yes’ WHERE tckr=’SHLM’;
To load Table
% mysqlimport --local samp_db member.txt (--fields-enclosed-by)
Note Load in Text File: member is the name of the text file containing sql table definition.
Load DATA LOCAL INFILE "member.txt" INTO TABLE member;
If does not work try invoking mysql with option –local-infile option (mysql –local-infile “database of choice” -p)
To create a table from a text file
CREATE TABLE IF NOT EXISTS
% mysql samp_db < create_blah.sql
Delete table or database:
DROP TABLE
DROP DATABASE
DROP INDEX index_name ON tbl_name
Table Properties
To list table attributes
DESCRIBE table_name;
Privileges
To add a new user:
GRANT ALL on * TO 'zsa'@localhost IDENTIFIED by 'XXXXX';
To grant a user to all databases and tables with all privileges
GRANT ALL ON *.* TO zsa@localhost IDENTIFIED BY "secret" WITH GRANT OPTION
Select Statements
SELECT what to select
FROM table or tables
WHERE conditions that data must satisfy
ex1. - search numerically
SELECT * FROM score WHERE score > 95;
ex2. - search for string value's , note string comparison's are normally not case sensitive
SELECT last_name, first_name FROM alergy WHERE last_name='pollen';
SELECT last_name, first_name FROM king WHERE death < '1492-1-1';
SELECT last_name, first_name FROM king WHERE death < '1492-1-1' and (state='CA' or state='MI');
select * from univ where sctr='tech' and active='yes';
select * from univ where sctr='tech' and mcap > 0 and mcap < 1000 and active='yes' order by sctr
Note: The NULL value means "no value", if you use NULL with the arithmetic comparison operators the result is undefined:
To perform searchs for NULL values, you must use a special syntax. Instead of using = or != to test for equality use IS NULL or IS NOT NULL
ex. pattern matching: to match last names that begin with a letter
SELECT last_name, first_name FROM president WHERE last_name LIKE 'W%';
ex. pattern matching: to match records containing exactly 4 characters
SELECT last_name, first_name FROM president WHERE last_name LIKE '____';
ex. SQL calculations
SELECT curdate,TsxYield/Can91Tb*CanPrime as dnrm FROM Market_Watch
How to run mysql from cmd line
mysql -u usrname -p -D dbname -e "select * from tablename"