# MySQL / MariaDB & MongoDB
Everything about MySQL / MariaDB and MongoDB
# How to do Everything in MySQL/MariaDB
## Useful Tricks
### Convert a Column to Uppercase
UPDATE table\_name SET `column_name` = UPPER( `column_name` )
---
### Show and Change View Definer
```
SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';
SHOW CREATE VIEW [view_name];
ALTER DEFINER = '[username]'@'[host]' VIEW [view_name] AS [select statement];
```
---
### Alter Views
```
ALTER VIEW AS [view statements]
```
---
### MySQL Datatype for Different Password Hashes
It depends on the hashing algorithm you use. Hashing always produces a result of the same length, regardless of the input. It is typical to represent the binary hash result in text, as a series of hexadecimal digits. Or you can use the \[UNHEX()\]([http://dev.mysql.com/doc/refman/5.5/en/string-](http://dev.mysql.com/doc/refman/5.5/en/string-) functions.html#function\_unhex) function to reduce a string of hex digits by half.
- MD5 generates a 128-bit hash value. You can use CHAR(32) or BINARY(16)
- SHA-1 generates a 160-bit hash value. You can use CHAR(40) or BINARY(20)
- SHA-224 generates a 224-bit hash value. You can use CHAR(56) or BINARY(28)
- SHA-256 generates a 256-bit hash value. You can use CHAR(64) or BINARY(32)
- SHA-384 generates a 384-bit hash value. You can use CHAR(96) or BINARY(48)
- SHA-512 generates a 512-bit hash value. You can use CHAR(128) or BINARY(64)
- BCrypt generates an implementation-dependent 448-bit hash value. [You might need CHAR(56), CHAR(60), CHAR(76), BINARY(56) or BINARY(60)](http://stackoverflow.com/questions/5881169/storing-a-hashed-password-bcrypt-in-a-database-type-length-of-column)
NIST recommends using SHA-256 or higher for passwords. Lesser hashing algorithms have their uses, but they are [known to be crackable](http://www.larc.usp.br/%7Epbarreto/hflounge.html).
You should [salt](http://en.wikipedia.org/wiki/Password_salt) your passwords before applying the hashing function. Salting a password does not affect the length of the hash result.
---
### Chinese Support in MySQL
Convert entire database to UTF-8: `ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;`
Convert entire table to UTF-8: `ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;`
Convert field to UTF-8: `ALTER TABLE tablename MODIFY columnname columndef CHARACTER SET utf8 COLLATE utf8_unicode_ci;`
---
### MySQL Grant Permission
```
Grant all on {dbname}.* to 'Id'@'localhost' identified by 'password'
```
---
### Convert All Table Columns' Charset and Collation
```
ALTER TABLE CONVERT TO CHARACTER SET COLLATE ;
```
Convert Database to MyISAM
```
#!/bin/sh
DB=''
TABLES=$(/opt/lampp/bin/mysql -uroot --skip-column-names -B -D $DB -e 'show tables')
for T in $TABLES
do
/opt/lampp/bin/mysql -uroot -D $DB -e "ALTER TABLE $T ENGINE=MYISAM"
done
```
## Solution to Common Problems
### MariaDB Function Error From mysqldump
Use DELIMITER keyword to change end of function delimiter, e.g.:
```
DELIMITER //
CREATE FUNCTION counter () RETURNS INT
BEGIN
UPDATE counter SET c = c + 1;
RETURN (SELECT c FROM counter LIMIT 1);
END;
//
CREATE FUNCTION counter2 () RETURNS INT
BEGIN
UPDATE counter SET c = c + 2;
RETURN (SELECT c FROM counter LIMIT 1);
END;
//
DELIMITER ;
```
---
### Error while sending QUERY packet
Change your maxallowedpacket by using one of the following methods:
- In mysql prompt, enter `SET GLOBAL max_allowed_packet=524288000;`
- Set `max_allowed_packet` in `my.ini`
# Setup for Remote Access
1. Grant Privileges
1. ```
GRANT ALL ON .* TO @'%' IDENTIFIED BY '';
```
2. Testing Remote Access
1. ```
mysql -uroot -p -h <host/ip>
```
# Basic MongoDB Operations
### Queries and Indexes
#### Display query stats
```
db..find({}).explain('executionStats')
```
### Basic Document Operations
#### Find all documents in a collection
```
db..find({})
```
#### Sorting documents
```
db..find({}).sort()
```
#### Find one document in a collection
```
db..findOne({})
```
#### Count documents in a collection
```
db..count()
```
#### Insert a document
```
db..insert({fieldA: 'a', fieldB: 'b'})
```
#### Updating a document
```
db..update({_id: ''}, {'$set': {'fieldA': 'value'}})
```
#### Updating multiple documents
```
db..update({: ''}, {'$set': {'fieldA': 'value'}}, {multi: true})
```
#### Delete a document
```
db..remove({_id: ''})
```
### Collection Operations
#### Remove a collection
```
db..drop()
```
#### List all collections
```
show collections
```
### Database Operations
#### List all databases
```
show databases
```
#### Switch to a database
```
use
```