Mac-brew安装Mysql_8.0

一、安装

brew install mysql

1. 可能会报错

Error: Cannot install mysql because conflicting formulae are installed.
  mysql-connector-c: because both install MySQL client libraries

2. 解决办法

brew unlink mysql-connector-c

3. 重新安装

二、启动并设置密码

1. 启动服务

brew services start mysql  # 后台启动，方便下次直接使用
mysql.server start # 手动启动，电脑重启后mysql服务不会自动开启

2. 登陆

mysql -u root -p  # 提示输入密码：直接enter 默认没有密码

3. 修改密码

• 网上铺天盖地的重置密码的方式，各种报错

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword')  # 适用于老版本

• 新版本8.0了 ，老版本的修改密码的语法已不再适用，mysql 5.7.9以后废弃了password字段和password()函数；
• 正确的修改方式

# root角色进入mysql后，执行以下3行代码
use mysql;  
FLUSH PRIVILEGES;
# 注意，密码由8位以上大写+小写+特殊字符+数字组成的密码
ALTER user 'root'@'localhost' IDENTIFIED BY 'newpassward';

4. 退出mysql命令行：

exit、quit、ctrl + D  # 3个方法都行

三、数据库操作

1. 创建数据库：create database database_name;

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

2. 查看数据库：show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

3. 打开数据库：use database_name

mysql> use test;
Database changed

4. 删除数据库

drop database database_name

四、数据表操作

1. 显示数据库中的所有表

show tables;

2. 显示数据表的结构：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)

3. 创建数据表：create tabel 表名(字段名称 字段类型, ……);

create table learn_class(id int, name varchar(20), primary key(id));  # id为主键

mysql> desc learn_class;  # 查看创建的表

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

4. 添加新列：alter table 列名 add 字段名称 字段类型;

alter table learn_class add price int;

mysql> desc learn_class;  # 查看添加列后的数据结构

+-------+-------------+------+-----+---------+-------+
| 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)

5. 删除数据表

drop table table_name;

6. 复制表：create table 新表名 like 被复制表名;

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)

五、数据操作

1. 查询数据

select * from 表名 where 字段 = 值;

2. 插入数据

insert into 表名 (字段1, ……) values (值1, ……);

# 插入数据
mysql> insert into class_info (id, name, price) values (01, 'python', 300);
Query OK, 1 row affected (0.00 sec)

# 查看数据
mysql> select * from class_info;  
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | python |   300 |
+----+--------+-------+
1 row in set (0.00 sec)

3. 更新数据

update 表名 set 字段=值,...,字段n=值n where 字段=值;

# 更新数据
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

# 查看更新结果
mysql> select * from class_info;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | python |   250 |
+----+--------+-------+
1 row in set (0.00 sec)

4. 删除数据

delete from 表名 where 字段=值;

六、批量导入csv数据

1. 修改Mysql导入导出配置

• MAC上使用brew安装的MySQL默认没有导出权限，需要手动配置；
• 查看是否有导入导出权限，secure_file_priv为Null表示不允许导入导出；

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.02 sec)

• 设置secure_file_priv值为导入导出的文件路径；

cd /usr/local/etc/  # 找到my.cnf文件
vim my.cnf  # 编辑
secure_file_priv= /user/data/  # 在[mysqld]下添加即可

2. 重启mysql服务

brew services restart mysql

3. 登陆mysql

mysql -u root -p

4. 创建数据库表，其列数及数据类型与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)

5. 导入csv数据

mysql> LOAD DATA [LOCAL] INFILE '/Users/Desktop/customer.csv'  # 读取csv文件。备注：若数据文件在客户端时要写LOCAL，在服务器端时不用写
    -> INTO TABLE test.customer  # 写入指定的数据库表
    -> FIELDS TERMINATED BY ','   # 文件的字段由逗号隔开
    -> ENCLOSED BY '"'   # 字段指定由双引号括起来
    -> LINES TERMINATED BY '\n'  # CSV文件的每一行都由指示的换行符终止
    -> IGNORE 1 ROWS;  # 文件具有包含列标题的第一行，无需导入
Query OK, 97823 rows affected, 65535 warnings (5.08 sec)
Records: 97823  Deleted: 0  Skipped: 0  Warnings: 1552333

6. 查看导入的数据

mysql> select count(*) from customer;
+----------+
| count(*) |
+----------+
|    97823 |
+----------+
1 row in set (0.02 sec)