can’t login as mysql user root from normal user account in ubuntu 16.04

1 – First, connect in sudo mysql

sudo mysql -u root

2 – Check your accounts present in your db

SELECT User,Host FROM mysql.user;
| User             | Host      |
| admin            | localhost |
| debian-sys-maint | localhost |
| magento_user     | localhost |
| mysql.sys        | localhost |
| root             | localhost |

3 – Delete current root@localhost account

mysql> DROP USER 'root'@'localhost';
Query OK, 0 rows affected (0,00 sec)

4 – Recreate your user

mysql> CREATE USER 'root'@'%' IDENTIFIED BY '';
Query OK, 0 rows affected (0,00 sec)

5 – Give permissions to your user (don’t forget to flush privileges)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,01 sec)

6 – Exit mysql and try to reconnect without sudo


Mysql change collation or character set

Database level

CREATE DATABASE czech_slovak_names CHARACTER SET = 'keybcs2' COLLATE = 'keybcs2_bin';
ALTER DATABASE czech_slovak_names COLLATE = 'keybcs2_general_ci';

Table level

CREATE TABLE english_names (id INT, name VARCHAR(40))
COLLATE 'utf8_icelandic_ci';

ALTER TABLE table_name
SET charset_name [COLLATE collation_name];

ALTER TABLE table_name MODIFY ascii_text_column TEXT CHARACTER SET utf8;
ALTER TABLE table_name MODIFY ascii_varchar_column VARCHAR(M) CHARACTER SET utf8;

Column level

CREATE TABLE european_names (
 croatian_names VARCHAR(40) COLLATE 'cp1250_croatian_ci',
 greek_names VARCHAR(40) CHARACTER SET 'greek');

To store emoji, or non utf8 character

 ALTER TABLE personals
 MODIFY self_introduction
 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

SQL in 10 minutes notes

Lesson 6 – Wildcard

SELECT prod_name 
FROM Products 
WHERE prod_name 
LIKE 'F%y';
% represents zero, one, or more characters at the specified location 
in the search pattern

SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name 
LIKE '__ inch teddy bear';
The underscore is used just like %, but instead of matching multiple 
characters the underscore matches just a single character

SELECT cust_contact 
FROM Customers 
WHERE cust_contact 
LIKE '[JM]%' 
ORDER BY cust_contact;
The brackets ([]) wildcard is used to specify a set of characters, 
any one of which must match a character in the specified position

Lesson 7 – Creating Calculated Fields

SELECT CONCAT(vend_name, ' (', vend_country, ')') AS vender_infor 
FROM Vendors 
ORDER BY vend_name;

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) +')' AS vender_infor 
FROM Vendors
ORDER BY vend_name;

The RTRIM() function trims all space from the right of a value. By using RTRIM(), the individual columns are all trimmed properly.

An alias is just that, an alternatename for a field or value. Aliases are assigned with the AS keyword.

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;