website for self-learning programming

Here are some websites you can learn how to code by yourself online.

1. Codecademy

2. Treehouse

3. One Month Rails

4. Hackerrank

reclaim unused space for mysql

The problem is that mysql will not release the disk space after delete operations to an InnoDB table. That means the disk usage of mysql will not change even you delete half of the data in the database, which will be a large waste for disk usage.

There are two ways to reorganize the physical storage of table data and associated index data to reduce storage space.

1. Use OPTIMIZE TABLE or MYSQLCHECK.

 OPTIMIZE TABLE tb1_name

but optimize table can only be performed for one table, if you want to optimize all the databases, you could either write a script to do it or use mysqlcheck command instead.

MYSQLCHECK -o --all-databases

2.  Resize the InnoDB Tablespace (if not use innodb_file_per_table?)

1. use mysqldump to dump all the InnoDB tables, including InnoDB tables located in MySQL database.
2. stop the server.
3. remove all the existing tablespace files (*.ibd), including the ibdata and ib_log files. Do not forget to remove *.ibd files for tables located in the MySQL database. 
4. remove any .frm files for InnoDB tables.
5. configure a new tablespace.
6. restart the server
7. import the dump files. 

If your databases only use the InnoDB engine, it may be simpler to dump all databases, stop the server, remove all databases and InnoDB log files, restart the server, and import the dump files.

References

1. http://stackoverflow.com/questions/1270944/mysql-innodb-not-releasing-disk-space-after-deleting-data-rows-from-table 
2. http://dev.mysql.com/doc/refman/5.6/en/innodb-data-log-reconfiguration.html 

sqlite command quick search

1. create database

sqlite3 db1

2. create table

create table tb1(id varchar(10), name varchar(10));

3. insert data into table

insert into tb1 values('1','dude1');

4. select data

select * from tb1;

5. view structure

select * from sqlite_master;

6. show help

.help

7. list databases

.databases

8. list tables

.tables

9. change mode

.mode list/column/csv/html/tcl..

10. change seperator

.separator ","

11. change width

.width

12. show headers

.header on/off

13. backup database

echo '.dump' | sqlite3 db1 | gzip -c > db1.dump.gz

14. rebuild database from backup

zcat db1.dump.gz | sqlite3 db2

15. delete table

delete table tb1;

16. exit sqlite

.exit

17. describe table structure

.schema

      or

PRAGMA table_info(posts);