mac下安装和使用mysql

参考链接：

https://www.jianshu.com/p/fd3aae701db9
https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

用的是第二种方法，homebrew安装

1. brew install mysql

2. unset TMPDIR

3. bash mysql_install_db --verbose --user=root --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp
4. 启动、停止服务：mysql.server start/stop
5. 登陆mysql：mysql -uroot -p
6. 修改root密码：ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
7. 忘记root密码时:mysql.server start --skip-grant-tables，然后输入mysql，再输入FLUSH PRIVILEGES;最后修改密码ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
8. 创建数据库：create database databasename；
9. 创建表：

   create table dept(
       DEPTNO int(2) not null comment '部门编号',
       DNAME varchar(14) comment '部门名称',
       LOC varchar(13) comment '部门所在城市',
       primary key (DEPTNO)
   )；
   create table emp(
       EMPNO int(4) not null comment '雇员编号',
       ENAME VARCHAR(10) comment '雇员姓名',
       JOB VARCHAR(9) comment '职位',
       MGR int(4) comment '上司',
       HIREDATE date comment '入职日期',
       SAL numeric(7,2) comment '工资',
       COMM numeric(7,2) comment '佣金',
       DEPTNO int(2) comment '部门编号',
       primary key (EMPNO),
       foreign key(DEPTNO) references dept (DEPTNO)
   )

   1. create table dept(
   2. DEPTNO int(2) not null comment '部门编号',
   3. DNAME varchar(14) comment '部门名称',
   4. LOC varchar(13) comment '部门所在城市',
   5. primary key (DEPTNO)
   6. )；
   7. create table emp(
   8. EMPNO int(4) not null comment '雇员编号',
   9. ENAME VARCHAR(10) comment '雇员姓名',
   10. JOB VARCHAR(9) comment '职位',
   11. MGR int(4) comment '上司',
   12. HIREDATE date comment '入职日期',
   13. SAL numeric(7,2) comment '工资',
   14. COMM numeric(7,2) comment '佣金',
   15. DEPTNO int(2) comment '部门编号',
   16. primary key (EMPNO),
   17. foreign key(DEPTNO) references dept (DEPTNO)
   18. )

   create table dept(
       DEPTNO int(2) not null comment '部门编号',
       DNAME varchar(14) comment '部门名称',
       LOC varchar(13) comment '部门所在城市',
       primary key (DEPTNO)
   )；
   create table emp(
       EMPNO int(4) not null comment '雇员编号',
       ENAME VARCHAR(10) comment '雇员姓名',
       JOB VARCHAR(9) comment '职位',
       MGR int(4) comment '上司',
       HIREDATE date comment '入职日期',
       SAL numeric(7,2) comment '工资',
       COMM numeric(7,2) comment '佣金',
       DEPTNO int(2) comment '部门编号',
       primary key (EMPNO),
       foreign key(DEPTNO) references dept (DEPTNO)
   )
10. 导入数据：

    insert into dept VALUES
    (10,'ACCOUNTING','NEW YORK'),
    (20,'RESEARCH','DALLAS'),
    (30,'SALES','CHICAGO'),
    (40,null,'BOSTON')

    1. insert into dept VALUES
    2. (10,'ACCOUNTING','NEW YORK'),
    3. (20,'RESEARCH','DALLAS'),
    4. (30,'SALES','CHICAGO'),
    5. (40,null,'BOSTON')

    insert into dept VALUES
    (10,'ACCOUNTING','NEW YORK'),
    (20,'RESEARCH','DALLAS'),
    (30,'SALES','CHICAGO'),
    (40,null,'BOSTON')
11. 连接操作：
    • 左/右/内连接：select a.id,b.id from a left/right/inner join b on a.id=b.id
    • 全连接：select a.id,b.id from a full join b on a.id=b.id，在mysql中应该用如下语句：select a.id,b.id from a left join b on a.id=b.id union all select a.id,b.id from a right join b on a.id=b.id where a.id is null
    • 笛卡尔积:select a.id,b.id from a cross join b
12. 添加主键：alter table tablename add primary key(ID)