# Create a database called'db_foo' CREATE DATABASE db_foo; # Create a user'foo' which only allowed access via 'localhost' # i.e. mysql -u foo -p CREATEUSER'foo'@'localhost' IDENTIFIED BY'password'; # Grantall privileges touser'foo'onlyfor database 'db_foo' # if want to allowed forALL database, just change 'db_foo.*'to'*.*' GRANTALL PRIVILEGES ON db_foo.*TO'foo'@'localhost'WITHGRANT OPTION; # Create a user'foo' which allowed access remotely # i.e. mysql -u foo -h 192.168.1.xxx -p CREATEUSER'foo'@'%' IDENTIFIED BY'another_or_same_password'; # Grantall privileges touser'foo'onlyfor database 'db_foo' GRANTALL PRIVILEGES ON db_foo.*TO'foo'@'%'WITHGRANT OPTION;
Look at the inner SUBSTRING_INDEX, we explode by -(dash), so the 5 is to take from the 1st to 5th opponent. So we get 123-456-789-abc-this_is_what_we_want.
Now the outer SUBSTRING_INDEX is to get 1 opponent start from the left, which is the last opponent, is what we want this_is_what_we_want.
Migrate data form table A to table B
1 2 3 4
INSERTINTO table_b (col_1, col_2, col_3) (SELECT col_1, col_2, col_3 FROM table_a WHERE col_1 ='foo');
ALTERTABLE tablename ADD column_new_name VARCHAR(100) AFTER column_old_name; ALTERTABLE tablename MODIFY column_new_name VARCHAR(100) CHARACTERSET utf8 COLLATE utf8_general_ci; /* make sure the collation is same as column_old_name */ /* copy the data from column_old_name to column_new_name */ UPDATE tablename SET column_new_name = column_old_name; ALTERTABLE tablename ADD FULLTEXT(column_new_name); /* Delete old column */ alterTABLE tablename DROPCOLUMN column_old_name;
Update all dates by addeing 30 minutes
1
UPDATE `tablename` SET `created_date` = DATE_ADD(`created_date` , INTERVAL30MINUTE ) WHERE `column` ='value'