# MySQL Note
###### tags: `work` `Self_Learning` `database`
## Basic
### Case-insensitive
SQL query is case-insensitive, which means that `select *` is same as `SELECT *`, even if `SeLecT *`.
### Comma
Use comma to `SELECT` multiple objects in one query. e.g.,
```sql
SELECT VERSION(), CURDATE();
```
The output is:
|version()|current_date|
|-|-|
|5.7.32|2020-11-26|
### newline, tab, whitespace insensitive
```sql
SELECT * FROM my_table;
```
is equivalent to
```sql=
SELECT
*
FROM
my_table;
```
### Math supported
SQL can use as a calculator. Also support `+`, `-`, `*`, `/`, `MOD(, )`.
```sql
SELECT SIN(PI()/2);
```
The output is `1`.
### Comment
SQL query uses `#` to add a comment.
## Basic Command
### SELECT
[doc](https://dev.mysql.com/doc/refman/5.7/en/select.html)
Basically it's like a "print" function, which means it can be used to print a string. e.g.,
```sql
SELECT "this is a string."
```
> Double and single quotation mark are equivalent to SQL.
Because it's like a "print" function, it can be used to print the particular columns:
```sql
SELECT name, birth FROM people_table;
```
### UPDATE
Update value of data.
```sql
UPDATE <table_name> SET <data_name>=<new_value> WHERE <condition>;
```
### SHOW
[doc](https://dev.mysql.com/doc/refman/5.7/en/show.html)
* `SHOW databases`: show all databases exist on the server.
* `SELECT database`: currently selected database.
* `SHOW tables`: show all tables exist.
### USE
[doc](https://dev.mysql.com/doc/refman/5.7/en/use.html)
Use `USE <database>` to change to annother database.
### CREATE
* create database
```sql
CREATE DATABASE <database_name>;
```
* create table
```sql
CREATE TABLE <table_name> (<data_name> <data_type>, ...);
```
Data types ([offical doc](https://dev.mysql.com/doc/refman/5.7/en/data-types.html)) include a lot of options such like:
* `VARCHAR(<int>)`
* `INTERGER`, `INT`
* `TEXT`
* `CHAR(1)`
* `DATE` (YYYY-MM-DD)
* `BLOB`
* `NULL`
### DESCRIBE
Show the structure of a table. e.g.,
```sql
DESCRIBE test_table;
```
---
## Advanced create
### AUTO_INCREMENT
Data type set as `auto_increment` will increase automatically. e.g.,
```sql
CREATE TABLE test_table (id INTEGER AUTO_INCREMENT, name TEXT);
```
Use `NULL` as value.
### PRIMARY KEY
:::danger
Only one primary key avaliable.
:::
Data type set as `PRIMARY KEY` can make sure that each value is unique, which usually used in serial number (S/N) or id. e.g.,
```sql
CREATE TABLE test_table (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name TEXT);
```
### NOT NULL
To force it can't be `NULL`.
```sql
CREATE TABLE test_table (
id INTERGER NOT NULL,
name TEXT);
```
* acceptable data: (3, null), (5, "Ted")
* unacceptable data: (null, null), (null, "Tom")
---
## Insert data
In this section, we use the table `tools`
|data name|data type|
|-|-|
|SN|`INTERGER NOT NULL`|
|name|`TEXT`|
|buy_date|`DATE`|
### INSERT
This example insert this data:
|SN|name|buy_date|
|-|-|-|
|'oven'|1103517346|'2018-08-16'|
into `tools`. We use insert:
```sql
INSERT INTO tools VALUES ('oven', 1103517346, '2018-08-16');
```
Can insert multiple data:
```sql
INSERT INTO tools VALUES
('oven', 1103517346, '2018-08-16'),
('firge', 5620561572, '1999-12-10'),
('phone', 6592618265, '2011-03-23');
```
### Load local data
Assume that there's a text file named as data.txt:
```
oven 1103517346 2018-08-16
```
If we want to load data.txt in `tool`, use query:
```sql
LOAD DATA LOCAL INFILE '/data.txt' INTO TABLE tools;
```
If we have multiple data in data.txt seperated by newline (``"\r\n"``), use this query:
```sql
LOAD DATA LOCAL INFILE '/data.txt'
INTO TABLE tools LINES TERMINATED BY '\r\n';
```
---
## FROM & WHERE (& IN & BETWEEN)
```sql
SELECT what_to_select
FROM which_table
WHERE condition;
```
### operator used in setting condition
* operator
* equality: `=` or `IS`
* inequality: `>`, `<`, `<=`, `>=`
> Note that the data type `date` support inequality operator.
* Logical opeator
`NOT`, `AND`, `OR`, `XOR`
* `IS NOT NULL`
check if the value is empty.
### IN
```sql
SELECT * FROM people_table WHERE name IN ('Jack', 'Tom');
```
### BETWEEN ... AND ...
```sql
SELECT * FROM people_table WHERE birth BETWEEN '1990-01-01' AND '2000-12-31';
```
---
## Deal with existing data (edit, remove)
### DELETE
[doc](https://dev.mysql.com/doc/refman/5.7/en/delete.html)
```sql
DELETE FROM fruits_table WHERE name = "apple";
```
### UPDATE ... SET ...
[doc](https://dev.mysql.com/doc/refman/5.7/en/update.html)
```sql
UPDATE fruits_table SET color = "red" WHERE name = "apple";
```
---
## Advanced select
### ORDER BY
```sql
SELECT name, birth FROM people_table ORDER BY birth;
```
### ORDER ... DESC
Sort in reverse.
```sql
SELECT name, birth FROM people_table ORDER BY birth DESC;
```
### AS
Used to set column title.
```sql
SELECT CURDATE() AS curr;
```
output is:
|curr|
|-|
|2020-11-20|
It can be a string.
```sql
SELECT CURDATE() AS 'current date';
```
output is:
|current date|
|-|
|2020-11-20|
After using `AS`, it can be used in anywhere in the same query.
### LIKE
SQL pattern matching enables you to use `_` to match any single character and `%` to match any number of characters (including zero characters).
```sql
SELECT * FROM strings WHERE title LIKE '_b%';
```
* acceptable: Abby, cba, 1b
* unacceptable: bcc, aqbxx
We can use several `_` to select particular number of characters.
`_____` $\Leftrightarrow$ `12345`
### REGEXP
Regular expression.
### COUNT
[doc](https://dev.mysql.com/doc/refman/5.7/en/aggregate-functions.html#function_count)
```sql
SELECT COUNT(*) FROM my_table;
```
### GROUP BY
Consider 4 data in `tools` like:
|id|name|buydate|
|-|-|-|
|1|oven|2020-04-03|
|2|frige|2018-09-29|
|3|oven|2019-11-30|
|4|hammer|2020-05-30|
* If we want to list each quantity, we can use:
```sql
SELECT name, COUNT(*) AS count FROM tools GROUP BY name;
```
The output is:
|name|count|
|-|-|
|frige|1|
|hammer|1|
|oven|2|
* We can also get the latest year of `buydate`:
```sql
SELECT name, YEAR(MAX(buydate)) AS 'buy year'
FROM tools GROUP BY name;
```
The output is:
|name|buy year|
|-|-|
|frige|2018|
|hammer|2020|
|oven|2020|
---
## built-in functions
### CURDATE()
Current date.
### TIMESTAMPDIFF()
e.g. ages:
```sql
SELECT name, birth, CURDATE(),
TIMESTAMPDIFF(year, birth, CURDATE()) AS age
FROM people_table ORDER BY birth;
```
### YEAR(), MONTH(), DAYOFMONTH()
Select particular part from a `DATE` type. e.g.
```sql
YEAR(CURDATE());
MONTH(CURDATE());
DAYOFMONTH(CURDATE());
```
### DATE_ADD & INTERVAL
`DATE_ADD` will automatically deal with the date format.
```sql
DATE_ADD(CURDATE(), INTERVAL 1 MONTH)
DATE_ADD(CURDATE(), INTERVAL 1 DAY)
```
### MAX() & MIN()
* Example 1:
```sql
SELECT MAX(price) AS "max price" FROM shop;
```
* Example 2: Find `id`, `name` and `price` of the most expensice `merchandise`.
```sql
SELECT id, name, price FROM merchandise
WHERE price=(SELECT MAX(price) FROM merchandise);
```
---
## JOIN
[doc](https://dev.mysql.com/doc/refman/5.6/en/outer-join-simplification.html)
```sql
select table1 [INNER/LEFT/RIGHT] JOIN table2 ON [condition]
```
Example used below are
* `table1`:
|id|
|-|
|1|
|2|
|3|
|4|
* `table2`:
|id|
|-|
|1|
|5|
|3|
|3|
### INNER JOIN
Inner join will retain the data that meets the conditions.
```sql
SELECT a.id, b.id FROM table1 AS a
INNER JOIN table2 AS b
ON a.id = b.id;
```
output:
|id|id|
|-|-|
|1|1|
|3|3|
|3|3|
#### Use Case:
Use this when both sides must have corresponding values.
For example, consider a list of orders and the customers who placed them. An order without a customer represents erroneous data, and a customer without an order is not of interest to us.
### LEFT JOIN
Left join will base on left one.
```sql
SELECT a.id, b.id FROM table1 AS a
LEFT JOIN table2 AS b
ON a.id = b.id;
```
output:
|id|id|
|-|-|
|1|1|
|2|null|
|3|3|
|3|3|
|4|null|
#### Use Case:
Use this when one side may not have related data on the other side.
For example, if we want a list of members and their orders, a LEFT JOIN is appropriate. This ensures that even members who have never placed an order will still appear in the result list.
### RIGHT JOIN
Right join will base on right one.
```sql
SELECT a.id, b.id FROM table1 AS a
RIGHT JOIN table2 AS b
ON a.id = b.id;
```
output:
|id|id|
|-|-|
|1|1|
|null|5|
|null|6|
|3|3|
|3|3|
> `a LEFT JOIN b` is equivalent to `b LEFT JOIN a`.
#### Use Case:
This is similar to LEFT JOIN but applies in the opposite direction. It is used when we are more interested in retaining all entries from the right table, even if there's no matching entry in the left table.
### FULL OUTER JOIN
No matter it do or do not have value on left side or right side, all data will be included.
#### Use Case:
Use this for cases where you want to list all entries from both sides, regardless of whether there are matching entries on the other side.
For instance, in a situation where we aim to list all students and the classes they take, a FULL OUTER JOIN is suitable. This approach accounts for students enrolled in multiple classes, as well as those enrolled in only one class, ensuring no student or class information is omitted even if some fields end up being NULL due to the absence of corresponding data.
### USING
```sql
ON a.id=b.id
```
is equivalent to
```sql
USING(id)
```
---
## Create a new account
[doc](https://dev.mysql.com/doc/refman/5.7/en/creating-accounts.html)
Requires root:
> in shell `mysql -u root -p`
### create user
```sql
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'newpassword';
```
### grant privileges
[doc](https://dev.mysql.com/doc/refman/5.7/en/grant.html)
* demo
```sql
GRANT all
ON <priv_level>
TO <username>
WITH GRANT OPTION;
```
* <priv_level> can be
* ```*```
* ```*.*```
* ```db_name.*```
* ```db_name.tbl_name```
* ```tbl_name```
* ```db_name.routine_name```
This query could be more complex. generally it looks like:
```sql
GRANT ALL ON *.* TO 'newuser'@'localhost' WITH GRANT OPTION;
```
## User-Defined Variables
Use `@` and `:=`. e.g.,
```sql
SELECT @min_price:=min(price), @max_price:=MAX(price) FROM shop;
```
After defining variables, we can use it as a variable:
```sql
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
```
Sign in with Wallet
Connect another wallet