1. Frequently I use order by ‘column1’ instead of primary key to select, for better performance, I create a index on ‘column1’:
CREATE INDEX column1_users USING BTREE ON users (column1);
2. I want to diplay user’s fullname, date of birth, and relationship. The sql looks like this and works fine:
select concat(firstname,' ',surname) fullname,
date_format(dob, '%M %d, %Y') dob,
when 'A' then 'Wife'
when 'B' then 'Dependent'
when 'C' then 'Disabled'
when 'D' then 'Children'
where gwl = '1234567890'
order by relation
3. I found MySQL is really powerful. for example, I was taught that for Oracle tables, you could add columns, but couldn’t drop column, couldn’t change column type, or decrease column size.
But in MySQL, it works for almost all these cases we can imagine.
Here I list some examples regarding on ALTER TABLE:
First we create a table: CREATE TABLE t1 (a INTEGER,b CHAR(10));
-- To rename the table from t1 to t2:
ALTER TABLE t1 RENAME t2;
-- To change column a from INTEGER to TINYINT NOT NULL (leaving the name the same), and to change column b from CHAR(10) to CHAR(20) as well as renaming it from b to c:
ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
-- To add a new TIMESTAMP column named d:
ALTER TABLE t2 ADD d TIMESTAMP;
-- To add an index on column d and a UNIQUE index on column a:
ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a);
-- To remove column c:
ALTER TABLE t2 DROP COLUMN c;
-- To add a new AUTO_INCREMENT integer column named c:
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (c);
We indexed c (as a PRIMARY KEY) because AUTO_INCREMENT columns must be indexed, and we declare c as NOT NULL because primary key columns cannot be NULL.
-- drop column from table:
ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
-- move columns inside table, and rename:
ALTER TABLE test change column test1 test2 varchar(20) first;
Pretty cool. MySQL seems have a lot of treasure for us to dig into.