MySQL

下载MySQL和PopSQL, 安装好后在Mac的“系统偏好设置”中可以查看MySQL的当前状态，默认为running状态。
配置环境变量，在这里以bash终端为例。

# 将环境变量写入终端配置
echo 'export PATH=/usr/local/mysql/bin:$PATH>> ~/.bash_profile'
# 重启配置
source  ~/.bash_profile
# 配置root初始密码
mysql -u root -p

>mysql 命令行

# 进入可修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'csad23sa12312';
# 创建数据库,注意分号不能少
create database warrior;

PopSQL

PopSQL 是一款非常好用的数据管理工具，它不仅仅是MySQL，几乎支持市面上大多数数据库。打开PopSQL点击左上角进入Peferences/Connections，点击右上角的Add new connection, 选择MySQL。填写好相关配置信息：

在这里我们需要注意的是Connection Type这个选项，PopSQL默认是云连接，本地环境需要点击开启。

在操作界面的数据库选择MySQL，创建第一个query。

CREATE TABLE student(
    student_id INT,
    name VARCHAR(20),
    major VARCHAR(20),
    PRIMARY KEY(student_id)
);

查看表格

DESCRIBE student

Table中的CRUD

# 插入数据
INSERT INTO student VALUES(1,"Zhang Wei","English")
# 获取Table全部数据,包含所有字段
SELECT * FROM student
# 插入指定字段的数据，缺损字段默认为NULL
INSERT INTO student(student_id,name) VALUES(3,"边晓红")
# 删除TABLE
DROP student
# 获取数据长度
SELECT COUNT(student_id) FROM student;
# 分组获取数据长度
SELECT COUNT(sex) FROM student
GROUP BY sex;
# 规则匹配
SELECT * FROM student
WHERE name LIKE "%com";
WHERE name LIKE "%school%";
WHERE birth LIKE "__-02%";

单一更新

# 指定更新的Table
UPDATE student
SET major = "英语" 
WHERE student_id = 4

批量更新

# 指定更新的Table
UPDATE student
SET major = "英语" 
WHERE major = "English"
# 双匹配
WHERE major = "English" OR "英专";

删除

DELETE FROM student
WHERE student_id  = 1;

Queries

SELECT student.name,student.major
FROM student
# 降序    
ORDER BY student_id DESC;
# 升序
ORDER BY student_id ASC;
# 限制数量
LIMIT 10;
# 指定条件
WHERE major = "English"
WHERE major = "English" OR "英语"
WHERE major IN ("English","Japanses","Chinese") AND age>18;

UNION & JOIN

# UNION将两个SELECT语句组合在一起

SELECT Name 
FROM Boys 
WHERE Rollno < 16 

UNION

SELECT Name 
FROM Girls 
WHERE Rollno > 9 

# JOIN
SELECT Boys.Name, Boys.Age, Girls.Address,
FROM Boys 
INNER JOIN Girls 
ON Boys.Rollno = Girls.Rollno;

嵌套请求

在工资表中找到工资大于3万的员工ID, 然后基于这些emp_id，找出他们的名字。

SELECT employee.first_name
FROM employee
WHERE employee.emp_id IN(
  SELECT work_with.emp_id
  FROM works_with
  WHERE works_with.total_sales>30000;
)