PHP, SQL & MySQL howto

Databaser / PHP, SQL & MySQL howto
How to change collation of database, table, column?
What is the best collation to use for MySQL (with PHP)
How to change Collation to utf8_bin in one go
mysql check collation of a table

This command shows information about a table, including the collation:

SHOW TABLE STATUS

You'll simply need to run an ALTER on each of the tables as follows:

ALTER TABLE table_name COLLATE utf8_bin;

other example:

ALTER TABLE table_name COLLATE utf8_general_ci;

If you also need to update the existing character encoding (unlikely by the sounds of things), you can use:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

other example:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Update the existing character encoding for a individual table:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;

To set default collation for the whole database:

ALTER DATABASE database_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Create user without setting a password:

CREATE USER 'jeffrey'@'localhost';

Create user and set user's password:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

Delete user:

DROP USER 'jeffrey'@'localhost';

or

delete from mysql.user WHERE User='name';

On MySQL 5.7.6 and later:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

On MySQL 5.7.5 and earlier:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password');

SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('cleartext password');

That is equivalent to the following statements:
UPDATE mysql.user SET Password=PASSWORD('cleartext password') WHERE User='bob' AND Host='%.example.org';

FLUSH PRIVILEGES;

Another way to set the password is to use GRANT:
GRANT USAGE ON *.* TO 'bob'@'%.example.org' IDENTIFIED BY 'cleartext password';

select * from mysql.user;

select User from mysql.user;

SHOW GRANTS FOR 'root'@'localhost';

GRANT ALL PRIVILEGES ON *.* TO 'jeffrey'@'localhost' WITH GRANT OPTION

select user,host from mysql.user;

To show privileges:
show grants for 'user'@'host';

To show privileges for all users:
select * from information_schema.user_privileges;

or

SELECT sql_grants FROM common_schema.sql_show_grants;

To change privileges, first revoke. Such as:
revoke all privileges on *.* from 'user'@'host';

Then grant the appropriate privileges as desired:
grant SELECT,INSERT,UPDATE,DELETE ON `db`.* TO 'user'@'host';

or

GRANT USAGE ON *.* TO 'jeffrey'@'localhost'; (This is the default setting for non-admin users)

Finally, flush:
flush privileges;

Info on flush privileges:
Reloads the privileges from the grant tables in the mysql database.

The server caches information in memory as a result of GRANT and CREATE USER statements. This memory is not released by the corresponding REVOKE and DROP USER statements, so for a server that executes many instances of the statements that cause caching, there will be an increase in memory use. This cached memory can be freed with FLUSH PRIVILEGES.

UPDATE table_name SET column_name = CONCAT(column_name, ' This will be added.');

UPDATE table_name SET column_name = CONCAT(column_name, ' This will be added.') WHERE value ='something';

Delete data in column:

UPDATE table_name SET column_name = NULL WHERE column_name is not null;

Delete a row:

DELETE FROM table_name WHERE column_name = some_value;