一、安装

brew install mysql

可能会报错

Error: Cannot install mysql because conflicting formulae are installed.

mysql-connector-c: because both install MySQL client libraries

解决办法

brew unlink mysql-connector-c

重新安装

二、启动并设置密码

启动服务

brew services start mysql # 后台启动，方便下次直接使用

mysql.server start # 手动启动，电脑重启后mysql服务不会自动开启

登陆

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

修改密码

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

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';

退出mysql命令行：

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

三、数据库操作

创建数据库：create database database_name;

mysql> create database test;

Query OK, 1 row affected (0.00 sec)

查看数据库：show databases;

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

+--------------------+

5 rows in set (0.00 sec)

打开数据库：use database_name

mysql> use test;

Database changed

删除数据库

drop database database_name

四、数据表操作

显示数据库中的所有表

show tables;

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

创建数据表：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)

添加新列：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)

删除数据表

drop table table_name;

复制表：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)

五、数据操作

查询数据

select * from 表名 where 字段 = 值;

插入数据

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)

更新数据

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)

删除数据

delete from 表名 where 字段=值;

六、批量导入csv数据

修改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]下添加即可

重启mysql服务

brew services restart mysql

登陆mysql

mysql -u root -p

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

导入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

查看导入的数据

mysql> select count(*) from customer;

+----------+

| count(*) |

+----------+

| 97823 |

+----------+

1 row in set (0.02 sec)