一、安装
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)
