Mac-brew安裝Mysql_8.0

一、安裝

brew install mysql
  1. 可能會報(bào)錯(cuò)
Error: Cannot install mysql because conflicting formulae are installed.
  mysql-connector-c: because both install MySQL client libraries
  1. 解決辦法
brew unlink mysql-connector-c
  1. 重新安裝

二、啟動(dòng)并設(shè)置密碼

  1. 啟動(dòng)服務(wù)
brew services start mysql  # 后臺啟動(dòng)，方便下次直接使用
mysql.server start # 手動(dòng)啟動(dòng)，電腦重啟后mysql服務(wù)不會自動(dòng)開啟
  1. 登陸
mysql -u root -p  # 提示輸入密碼：直接enter 默認(rèn)沒有密碼
  1. 修改密碼
  • 網(wǎng)上鋪天蓋地的重置密碼的方式，各種報(bào)錯(cuò)
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword')  # 適用于老版本
  • 新版本8.0了 ，老版本的修改密碼的語法已不再適用，mysql 5.7.9以后廢棄了password字段和password()函數(shù)；
  • 正確的修改方式
# root角色進(jìn)入mysql后，執(zhí)行以下3行代碼
use mysql;  
FLUSH PRIVILEGES;
# 注意，密碼由8位以上大寫+小寫+特殊字符+數(shù)字組成的密碼
ALTER user 'root'@'localhost' IDENTIFIED BY 'newpassward';
  1. 退出mysql命令行：
exit、quit、ctrl + D  # 3個(gè)方法都行

三、數(shù)據(jù)庫操作

  1. 創(chuàng)建數(shù)據(jù)庫：create database database_name;
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
  1. 查看數(shù)據(jù)庫：show databases;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)
  1. 打開數(shù)據(jù)庫：use database_name
mysql> use test;
Database changed
  1. 刪除數(shù)據(jù)庫
drop database database_name

四、數(shù)據(jù)表操作

  1. 顯示數(shù)據(jù)庫中的所有表
show tables;
  1. 顯示數(shù)據(jù)表的結(jié)構(gòu)：desc 表名;
mysql> desc latest_file_io;

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| thread    | varchar(149) | YES  |     | NULL    |       |
| file      | varchar(512) | YES  |     | NULL    |       |
| latency   | text         | YES  |     | NULL    |       |
| operation | varchar(32)  | NO   |     | NULL    |       |
| requested | text         | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
  1. 創(chuàng)建數(shù)據(jù)表：create tabel 表名(字段名稱 字段類型, ……);
create table learn_class(id int, name varchar(20), primary key(id));  # id為主鍵

mysql> desc learn_class;  # 查看創(chuàng)建的表

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
  1. 添加新列：alter table 列名 add 字段名稱 字段類型;
alter table learn_class add price int;

mysql> desc learn_class;  # 查看添加列后的數(shù)據(jù)結(jié)構(gòu)

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| price | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  1. 刪除數(shù)據(jù)表
drop table table_name;
  1. 復(fù)制表：create table 新表名 like 被復(fù)制表名;
mysql> create table class_info like learn_class;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| class_info     |
| learn_class    |
+----------------+
2 rows in set (0.00 sec)

五、數(shù)據(jù)操作

  1. 查詢數(shù)據(jù)
select * from 表名 where 字段 = 值;
  1. 插入數(shù)據(jù)
insert into 表名 (字段1, ……) values (值1, ……);

# 插入數(shù)據(jù)
mysql> insert into class_info (id, name, price) values (01, 'python', 300);
Query OK, 1 row affected (0.00 sec)

# 查看數(shù)據(jù)
mysql> select * from class_info;  
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | python |   300 |
+----+--------+-------+
1 row in set (0.00 sec)
  1. 更新數(shù)據(jù)
update 表名 set 字段=值,...,字段n=值n where 字段=值;

# 更新數(shù)據(jù)
mysql> update class_info set price=250 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 查看更新結(jié)果
mysql> select * from class_info;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | python |   250 |
+----+--------+-------+
1 row in set (0.00 sec)
  1. 刪除數(shù)據(jù)
delete from 表名 where 字段=值;

六、批量導(dǎo)入csv數(shù)據(jù)

  1. 修改Mysql導(dǎo)入導(dǎo)出配置
  • MAC上使用brew安裝的MySQL默認(rèn)沒有導(dǎo)出權(quán)限，需要手動(dòng)配置；
  • 查看是否有導(dǎo)入導(dǎo)出權(quán)限，secure_file_priv為Null表示不允許導(dǎo)入導(dǎo)出；
mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.02 sec)
  • 設(shè)置secure_file_priv值為導(dǎo)入導(dǎo)出的文件路徑；
cd /usr/local/etc/  # 找到my.cnf文件
vim my.cnf  # 編輯
secure_file_priv= /user/data/  # 在[mysqld]下添加即可
  1. 重啟mysql服務(wù)
brew services restart mysql
  1. 登陸mysql
mysql -u root -p
  1. 創(chuàng)建數(shù)據(jù)庫表，其列數(shù)及數(shù)據(jù)類型與csv文件一致
mysql> create table customer(id bigint,opt_lock integer,address varchar(100),company_name varchar(40),email varchar(50),idcard varchar(18),month_income decimal(8,1),name varchar(15),new_date timestamp,password varchar(50),phone varchar(11),prev_date timestamp,qq varchar(30),register_date timestamp,username varchar(20),vocation varchar(50),wx_id varchar(255),rr_url varchar(100),xl_url varchar(100),open_id varchar(255),is_openid_enable integer,city varchar(255),customer_type integer,enterorise_name varchar(255),main_business varchar(255),major_name varchar(255),school_name varchar(255),code varchar(255),coupon_code_id bigint,admission_time varchar(50),business_time varchar(50),employed_date varchar(50),mobile_income double,regedit_num varchar(100),iostoken varchar(255),school_able integer,bank_verify integer,verify_amount decimal(10,2),channel_cust_id varchar(255),channel_id bigint,independent_password varchar(255),source_from integer,idfa_ios varchar(255),mac_ios varchar(255),business_circle varchar(255),electric_platform varchar(255),online_store_name varchar(255),source_mark varchar(255),version_ios varchar(255),adpromote_from varchar(10),adr_token varchar(255),business_lic_num varchar(255),latitude varchar(255),longitude varchar(255),sex varchar(255),baitiao integer,logout integer,is_tk_on integer,is_apply_finish integer,share_code varchar(30),created_at timestamp,updated_at timestamp,is_phone_valid integer,PRIMARY KEY (id));
Query OK, 0 rows affected (0.04 sec)
  1. 導(dǎo)入csv數(shù)據(jù)
mysql> LOAD DATA [LOCAL] INFILE '/Users/Desktop/customer.csv'  # 讀取csv文件。備注：若數(shù)據(jù)文件在客戶端時(shí)要寫LOCAL，在服務(wù)器端時(shí)不用寫
    -> INTO TABLE test.customer  # 寫入指定的數(shù)據(jù)庫表
    -> FIELDS TERMINATED BY ','   # 文件的字段由逗號隔開
    -> ENCLOSED BY '"'   # 字段指定由雙引號括起來
    -> LINES TERMINATED BY '\n'  # CSV文件的每一行都由指示的換行符終止
    -> IGNORE 1 ROWS;  # 文件具有包含列標(biāo)題的第一行，無需導(dǎo)入
Query OK, 97823 rows affected, 65535 warnings (5.08 sec)
Records: 97823  Deleted: 0  Skipped: 0  Warnings: 1552333
  1. 查看導(dǎo)入的數(shù)據(jù)
mysql> select count(*) from customer;
+----------+
| count(*) |
+----------+
|    97823 |
+----------+
1 row in set (0.02 sec)
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容