mysql examples


//===
show index from table1;

ALTER DATABASE db1
DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_unicode_ci' ;

alter table phrase add column freq int unsigned;

show create table t1;


http://www.w3schools.com/sql/sql_alter.asp
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
alter table phrase modify column val char(8);

//=== mysql data type char(8)
varchar(500)
char(254)


https://dev.mysql.com/doc/refman/5.0/en/string-type-overview.html

CREATE TABLE t
(
c1 VARCHAR(20) CHARACTER SET utf8,
c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);



//=== http://www.w3schools.com/sql/sql_unique.asp
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

ALTER TABLE Persons
ADD UNIQUE (P_Id)


ALTER TABLE Persons
DROP INDEX uc_PersonID



//=== http://dev.mysql.com/doc/refman/5.1/en/create-table.html
CREATE TABLE new_tbl LIKE orig_tbl;
CREATE TABLE new_tbl SELECT * FROM orig_tbl;

CREATE TABLE IF NOT EXISTS t1 (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`val` varchar(200) NOT NULL,
PRIMARY KEY (`id`),
index `val` (`val`)
) DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci'


//=== mysql datatype int
https://dev.mysql.com/doc/refman/5.0/en/integer-types.html

INTEGER (or INT) , SMALLINT, TINYINT, MEDIUMINT, BIGINT.

http://stackoverflow.com/questions/5562322/difference-between-int-and-int3-data-types-in-my-sql
Difference between “int” and “int(3)” data types in my sql

INT(10) specifies an INT with a display width of 10 digits
and doesn't affect the size of the integer.






//====
http://dba.stackexchange.com/questions/6570/setting-default-charset-collation-for-mysql-database

"""...
CREATE DATABASE IF NOT EXISTS foo
DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci'


ALTER DATABASE foo
DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci'
..."""


http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci

utf8_general_ci , utf8_unicode_ci ,


"""...
You should never, ever use utf8_general_ci: it simply doesn’t work. It’s a throwback to the bad old days of ASCII stooopeeedity from fifty years ago

...
In short: utf8_unicode_ci uses the Unicode Collation Algorithm as defined in the Unicode standards,
whereas utf8_general_ci is a more simple sort order which results in "less accurate" sorting results.


..."""