William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

mysql: case, alter table

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,
  case relation
  when 'A' then 'Wife'
  when 'B' then 'Dependent'
  when 'C' then 'Disabled'
  when 'D' then 'Children'
  end 
  as relation
from test
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.

Advertisements

One response to “mysql: case, alter table

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: