0%

MySQL-安裝及基本指令

安裝MySQL

因為我是Mac,所以選擇MySQL Community Server下載。

https://dev.mysql.com/downloads/

安裝完成之後會出現一個彈跳視窗,給你一個暫時的密碼,後面可以再修改。

1
A temporary password is generated for root@localhost: wj4U#ds.fQvM

啟動mysql

到我們mysql的安裝位置啟動。

1
2
$ cd usr/local/mysql/bin
$ ./mysql -u root -p

修改密碼

1
2
$ ALTER USER "root"@"localhost" IDENTIFIED by "new_password";
$ exit;

安裝Docker

可參考此影片:https://www.youtube.com/watch?v=f9jBaXbv4nA&list=PLfQqWeOCIH4BfWvgffq2-pIm9Mc8pf_OI&index=2

在docker上執行mysql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
https://hub.docker.com/_/mysql
$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:(mysql_version)
$ docker ps (see all running containers )
$ docker exec -it (container_name or its ID) sh(enter container shell)
$ mysql -u root -p (use this caontainer's password)

$ docker stop (container_name or its ID)
$ docker ps -a (can see all containers been closed)
$ docker rm (container_name or its ID)

掛載資料檔
1.將欲使用的資料檔路徑掛載進container(如果已經掛載過就可以省略此步驟)
$ docker run --name some-mysql -v /Users/phil/Downloads:/root -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7

2.用source指令導入檔案
$ source /root/file_name

Database command

1
2
3
4
5
$ create database (database_name);
$ show databases; (see all databases we have now)
$ use (database_name); (switch to another database)
$ select (database_name); (see which database we are using)
$ drop (database_name); (remove database)

Table command

1
2
3
4
5
6
7
8
$ create table table_name( column type, column type....);
$ create table if not exists table_name( column type, column type....);

$ show tables; (see all tables we have)
$ describe table_name; (see all contents of the table)
$ show columns from table_name; (same result as $ describe table_name)
$ drop table table_name; (remove the table)

Practice (create an employee table)

1
2
3
4
5
6
7
8
9
$ create table employees(
id INT AUTO_INCREMENT,
birth_date DATE,
gender ENUM("F","M"),
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
hire_date DATE),
PRIMARY_KEY(id);

Data Insert command

1
insert into table_name(column1_name, column2_name,...) values (column1_value, cloumn2_value,...)