mysql -u user -p database_name -e "SELECT * FROM users"
Use the .my.cnf. If you don't have create it vi ~/.my.cnf
[client]
user=your_user
password=your_password
Now we can execute our sql with shorter code.
mysql database_name -e "SELECT * FROM users"
Hooray !!!
I think it's important to design your database at your early stage of development because you cannot alter everything of database.
As a MySQL beginner, I never pay attention to the ON UPDATE or ON DELETE clause when creating table.
Let's say we have users and items table where its relationship is one user has many items. So we will create a foreign key owner_id in items table which refer to the users table like this.
CREATE TABLE IF NOT EXISTS `items` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`owner_id` INT UNSIGNED,
`title` VARCHAR(255) CHARACTER SET NOT NULL,
FOREIGN KEY(owner_id) REFERENCES users(id),
PRIMARY KEY (`id`) );Everything is ok. Well well well you code your app, user can add item, edit it, remove it and see it. But what happen when a user created a lot of items, then when she decide to delete her account? Should we remove the items she created? Or we want to remove it together? It really depends on how your application works but the code above won't work.
So why? Because when we set the build the relationship between users and items using foreign key we must also think about what if the user id is being updated or the user is removed from database? MySQL let us set what to do via the value of ON UPDATE and ON DELETE. By default, ON UPDATE and ON DELETE is set to NO ACTION.
| TYPE | DESCRIPTION |
|---|---|
| NO ACTION / RESTRICT | This is the default value. In our example user delete won't work |
| SET NULL | When user is removed(or update), the column of owner_id is set to null |
| CASCADE | When user is removed(or update), items belonged to her will be remove(or update) |
Also make sure foreign key has the same column characteristic(type, size, nullable) with it's references.
If you write your mysql in file instead of type it in the database CLI, writing comment will be helpful. In real life, apps are large and just like code, we don't always remember or know what the sql is doing. Comment can help. We can write comment in sql by various types.
-- comment
# comment
/* comment */In some time we need to backup our database and mysqldump is really handy for this.
mysqldump -u your_user -p your_database > file_name.sql
Yup ! We can now save this to some local file. This is very cool. What if we just want to save data instead of table strcuture.
mysqldump -u your_user -p your_database --no-create-info > file_name.sql
Now we have only data =). We can also choose to ignore some tables data.
mysqldump -u your_user -p your_db --no-create-info --ignore-table=your_db.users --ignore-table=your_db.users > file_name.sql
If you want to include date in your file name:
// assumed that we set our user and password in .my.cnf
mysqldump db_name > file_name_`date "+%Y%m%d_%H%M%S"`.sql
Here we have a file looks something like file_name_20151111_082826.sql
- https://rtcamp.com/tutorials/mysql/mycnf-preference/
- http://stackoverflow.com/questions/5109993/mysqldump-data-only
- http://stackoverflow.com/questions/1571581/how-to-add-on-delete-cascade-in-alter-table-statement
- http://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint
- http://stackoverflow.com/questions/1027656/what-is-mysqls-default-on-delete-behavior
- http://stackoverflow.com/questions/9098655/how-can-i-add-comments-in-mysql