### MySQL
#### MySQL
1. ##### mysql-8.0.19-winx64.zip ,
2. ##### MySQLbinPATH C:\mysql-8.0.19-winx64\bin
3. ##### MySQLmy.ini()
```
[mysqld]
# 3306
port=3306
# mysql
basedir=c:\mysql-8.0.19-winx64
# mysql
datadir=c:\mysql-8.0.19-winx64\data
#
max_connections=200
#
max_connect_errors=10
# UTF8
character-set-server=UTF8MB4
#
default-storage-engine=INNODB
# mysql_native_password
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
# mysql
default-character-set=UTF8MB4
[client]
# mysql
port=3306
default-character-set=UTF8MB4
```
4. ##### MySQL : cmd,binmysqld --install MySQL
5. ##### MySQLbin `mysqld --initialize-insecure`
6. ##### MySQL : `net start mysql`
7. ##### MySQL `mysql -u root -p`
8. #####
```
ALTER user 'root'@'localhost'
identified with mysql_native_password
by 'root'
```
####
1. ##### mysqlmysql,cmd
```
net stop mysql
```
2. ##### windows+Rregedit
3. #####
+ HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL
+ HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL
4. ##### cmd,sc delete ,MySQL
```
sc delete mysql
```
####
1. #####
+
```mysql
CREATE DATABASE db_name DEFAULT CHARSET=UTF8MD4;
```
+ CREATE :
+ DATABASE :
+ db_name :
2. #####
+
```mysql
USE db_name;
```
3. #####
+
```mysql
SHOW databses;
```
4. #####
+
```mysql
DROP DATABASE db_name;
```
####
1. #####
1.
| | | |
| :-------: | :---: | :-----------------------------------------: |
| TINYINT | 1 | (-128, 127) |
| SMALLINT | 2 | (-32768, 32767) |
| MEDIUMINT | 3 | (-83388608, 83388607) |
| **INT** | 4 | |
| BIGINT | 8 | |
| **FLOAT** | 4 | |
| DOUBLE | 8 | |
| DECIMAL | | DECIMAL(M,D),M>DM+2D+2 [] |
2.
| | | |
| :----------: | ------ | --------------------------------- |
| **DATE** | 3 | -- |
| **TIME** | 3 | -- |
| **DATETIME** | 8 | ----- |
| YEAR | 1 | |
| TIMESTAMP | 4 | 1970-01-01 00:00:00 |
3.
| | | |
| ----------- | ----------------- | :---------------------------: |
| **CHAR** | **0-255 ** | **** |
| **VARCHAR** | **0-65535 ** | **** |
| TINYBLOB | 0-255 | 255 |
| BLOB | 0-65535 | |
| MEDIUMBLOB | 0-16777215 | |
| LONGBLOB | 0-4294967295 | |
| TINYTEXT | 0-255 | |
| TEXT | 0-65535 | |
| MEDIUMTEXT | 0-16777215 | |
| LONGTEXT | 0-4294967295 | |
3. **VARCHAR CHAR TEXT**
+ **CHAR** : , 0-255,255,MySQL,,char ,
+ **VARCHAR** : V5.0.3 255;V5.0.3 65532 (3)
+ **TEXT** : , 2^16-1
+ VARCHAR, VARCHAR , VARCHAR TEXT 255 VARCHAR TEXT,CHAR
2. #####
+
```mysql
SHOW TABLES;
```
+
```mysql
desc tab_name;
```
3. #####
+
```mysql
CREATE TABLE tab_name(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT "",
name VARCHAR(20) NOT NULL COMMENT "",
date DATETIME COMMENT ""
)DEFAULT CHARACTER SET 'UTF8MB4';
```
+ AUTO_INCREMENT :
+ COMMENT :
+ DEFAULT CHARACTER SET 'UTF8MB4' :
4. #####
1.
+
```mysql
ALTER TABLE tab_name
ADD col_name [FIRST | AFTER old_col_name]
```
+
1.
```mysql
ALTER TABLE tab_name
ADD col_name VARCHAR(200);
```
2.
```mysql
ALTER TABLE tab_name
ADD col_name VARCHAR(200) FIRST;
```
3. (emp_name )
```mysql
ALTER TABLE tab_name
ADD col_name VARCHAR(200)
AFTER emp_name;
```
2.
+
```mysql
ALTER TABLE tab_name DROP col_name;
```
3.
+
```mysql
ALTER TABLE tab_name
CHANGE old_col_name new_col_name
ALTER TABLE tab_name
MODIFY col_name
```
+
1.
```mysql
ALTER TABLE tab_name
ALTER col_name SET DEFAULT "";
```
2.
```mysql
ALTER TABLE tab_name
MODIFY col_name VARCHAR(200);
```
3.
```mysql
ALTER TABLE tab_name
CHANGE old_col_name new_col_name VARCHAR(100);
```
5. #####
+
```mysql
ALTER TABLE old_tab_name RENAME AS/TO new_tab_name;
```
6. #####
+
```mysql
DROP TABLE tab_name;
```
6. #####
1. **** :
2. ** (PRIMARY KEY)** :
3. ** (FOREIGN KEY)** :
+ : ()
+ : ,
4. ** (UNIQUE)** :
5. ** (DEFAULT)** :
6. ** (NOT NULL)** :
7. ** (AUTO_INCREMENT)** : +1
8. ** (UNSIGNED)** :
#### CSV
1. #####
+ **git bash** win **sed**
+
```
mysql -u root -p -e "select * from db_name.table_name" | sed -e "s/\t/,/g" -e "s/NULL/ /g" -e "s/\n/\r\n/g" > d:/test.csv
```
+ `sed -e "s/\t/,/g" -e "s/NULL/ /g" -e "s/\n/\r\n/g"`
+ `-e "s/\t/,/g"` : **** **,**
+ `-e "s/NULL/ /g"` : **NULL** csv ****
+ `-e "s/\n/\r\n/g"` : **** csv **\n**
2. ##### mysql
+ **select .... into outfile** : mysqlselect
+ -- > csv
```
select * from table_name
into outfile "/db/test1.csv"
fields terminated by "," # tab\t
optionally enclosed by '"' #
escaped by "\" # backslash\ )
lines terminated by "\n"; #
```
3. ##### Navicat
#### CRUD
1. ##### INSERT
+
```mysql
INSERT INTO tab_name [(col_name, ...)]
VALUES
(...),
(...);
```
1. (,;,)
1.
```mysql
INSERT INTO tab_name VALUES(..., ...);
```
2.
```mysql
INSERT INTO tab_name(col_name1, col_name2, ...)
VALUES(val1, val2n ...);
```
2.
```mysql
INSERT INTO tab_name
VALUES
(val1, val2, ...),
(val1, val2, ...);
```
3.
1. ( WHERE false ,)
```mysql
CREATE TABLE new_tab
SELECT * FROM old_tab WHERE 1=2;
```
2.
```mysql
INSERT INTO new_tab
SELECT * FROM old_tab;
```
2. ##### DELETE
+
```mysql
DELETE FROM tab_name [WHERE ];
```
1. ()
1. **`TRUNCATE TABLE [tab_name]`** : ,
```mysql
TRUNCATE TABLE tab_name;
```
2. **`DELETE FROM [tab_name]`** : ,
```mysql
DELECT FROM tab_name;
```
2. **`DELECT`** : ,
```mysql
DELECT FROM tab_name WHERE ;
```
3. **`LIMIT`** : , CPU
```mysql
DELECT FROM tab_name LIMIT n;
```
3. ##### UPDATE ()
+
```mysql
UPDATE tab_name
SET col_name1=1,
col_name2=2...
[WHERE ];
```
4. ##### SELECT
1. ()
1.
+
```mysql
SELECT * FROM tab_name;
```
2.
+
```mysql
SELECT col_name1, col_name2 FROM tab_name;
```
3.
+
```mysql
SELECT DISTINCT col_name FROM tab_name;
```
4.
+
```mysql
SELECT * FROM tab_name WHERE ;
```
5.
+
```mysql
SELECT * FROM tab_name
WHERE 1 and 2
```
6.
+
```mysql
SELECT * FROM tab_name
WHERE 1 or 2;
```
7. BETWEEN...AND
+
```mysql
SELECT * FROM tab_name
WHERE col_name BETWEEN AND ;
```
8. IN
+
```mysql
SELECT * FROM tab_name
WHERE col_name IN ();
SELECT * FROM tab_name
WHERE col_name NOT IN ();
```
9. IS NULL
+
```mysql
SELECT * FROM tab_name
WHERE col_name IS NULL;
SELECT * FROM tab_name
WHERE col_name IS NOT NULL;
```
10.
+ (% ,_)
```mysql
SELECT * FROM tab_name
WHERE col_name LIKE '(eg: %)'
```
11.
+ (ESC, DESC)
```mysql
SELECT * FROM tab_name
ORDER BY col_name DESC;
```
12.
+
```mysql
SELECT * FROM tab_name
ORDER BY tab_name DESC
LIMIT (start), (size);
```
13.
+
```mysql
SELECT GROUP_CONCAT(col_name1), col_name2
FROM tab_name
GROUP BY col_name
HAVING ;
```
2. ( MySQL ,)
1. count() : ,
+
```mysql
SELECT count(col_name\*) FROM tab_name;
```
2. sum() : ,
+
```mysql
SELECT sum(col_name) FROM tab_name;
```
3. avg() :
+
```mysql
SELECT avg(col_name) FROM tab_name;
```
4. max() :
+
```mysql
SELECT max(col_name) FROM tab_name;
```
5. min() :
+
```mysql
SELECT min(col_name) FROM tab_name;
```