MySql command Collection

Install mysql from homebrew

$ brew install mysql
$ brew info mysql
$ brew list  //check the list

connection with mysql

$ mysql -u root -p

if you didn't set password, after just press enter key and insert

mysql>ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourPassword';

SHOW - What can see DB & TABLE
CREATE - What make DB, TABLE
SELECT - Select datas
INSERT - Insert record
UPDATE - Update data
DELETE - Delete record
DROP - Delete DB, TABLE
ALTER - Modify each information

Connection of Mysql in cmd

mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Homebrew

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'perthhouse';
Query OK, 0 rows affected (0.00 sec)

mysql>

Gnerating DB

CREATE DATABASE D_N;

Show all databases

SHOW DATABASES;

Use database

USE D-N;

Generating Table

CREATE TABLE user(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(255),
email varchar(255)
);

SHOW TABLES;

3 Ways

DESC T_N

DESCRIBE T_N;

EXPLAIN T_N;

Insert table data

INSERT INTO T_N(F_N1, F_N2, F_N3.. ESC..) VALUES('DATA1', 'DATA2','DATA3'....)

SELECT * FROM T-N;

DROP DATABASE D_N;

DROP TABLE T_N;

ALTER TABLE T_N ADD F_N F_TYPE;

ALTER TABLE T_N DROP F_N;

Feild modify

ALTER TABLE T_N CHANGE F_N NewF_N NewF_Type;

Feild type modify

ALTER TABLE T_N MODIFY F_N NewF_Type;

ALTER TABLE T_N RENAME NewF_N;

Modify data

UPDATE T_N SET newF_N = "value" WHERE conditionF_N="value";

Delete data

DELETE FROM T_N WHERE F_N='value';

Search data

SELECT F_N T_N WHERE F_N="value";