Day 1: SQL（2020.03.2）

1. 数据分析流程

业务理解->数据收集->数据处理->分析预测->数据展现->成果报告

2.数据库常用命令

2.1常规查看

-- 查看系统中有哪些数据库
SHOW DATABASES;

-- 创建cda数据库
CREATE DATABASE 库名;

-- 选择进入数据库
use 库名;

-- 删除数据库(慎用)
-- drop database 库名;

2.2 Table级别的增删改

-- 创建数据表
CREATE TABLE 表名(
did int(10),
dname VARCHAR(11)  -- varchar必须限制长度
);

-- 查看当前数据库中有哪些表
SHOW TABLES;

-- 查看表结构
desc 表名;


-- 删除数据表(慎用)
DROP TABLE 表名;


-- 创建带有约束条件的表
CREATE TABLE 表名(
deptno int PRIMARY KEY,  -- 主键
dname VARCHAR(10),
loc VARCHAR(10)
);
CREATE TABLE employee(
empid int PRIMARY KEY auto_increment,  -- 主键+自动增长
ename VARCHAR(10) UNIQUE,  -- 联合约束
job VARCHAR(10) not NULL,
mgr int,
hiredate date,
sal FLOAT DEFAULT(0),
comm FLOAT,
deptno int ,
FOREIGN KEY(deptno) REFERENCES dept(deptno)
-- 外部约束deptno 依赖于上一个表dept中的deptno
);

-- 修改表名
ALTER TABLE 已存在表名 RENAME 新表名;

2.3 Table内部字段删改（字段名等价于列名）

-- 修改字段名
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 元素类型;
DESC 表名;

-- 修改字段类型
ALTER TABLE 表名 MODIFY 字段名 元素类型 约束条件;
  -- 只能改成非空，默认，自增。

-- 添加字段
ALTER TABLE 表名 ADD 字段名 元素类型 位置;
  -- 默认加在最后一列，first就放在首列

-- 修改字段的排列位置：
ALTER TABLE 表名 MODIFY 字段名 元素类型 AFTER 字段名;


-- 删除字段
ALTER TABLE 表名  DROP 字段名;

2.4 Table内数据增删改

-- 插入数据：字段名与字段值的数据类型、个数、顺序必须一一对应
insert into 表名(deptno,dname,loc) values (10,'accounting','new york'),(20,'research','dallas');
insert into 表名 values (30,'sales','chicago'),(40,'operations','boston');
SELECT * FROM 表名;

2.4.1 Mac 下导入数据目录与权限的更改

1. 首先查看权限目录
   在MySQL下输入: show global variables like '%secure_file_priv%';  -- 显示有安全权限的目录
2. 当得到 secure_file_priv 值为NULL时，说明不允许输入输出
  终端输入: sudo vim /etc/my.cnf  打开my.cnf文件配置
  输入: [mysqld_safe]
        [mysqld]
        secure_file_priv=""
  然后ESC->:wq->回车 保存
3.重启MySQL
4.若在导入时出现权限问题
 终端: sudo chown -R mysql 文件所在目录地址
 来打开目录权限

-- 批量导入数据
load data infile '/Users/pal/MysqlFile/employee.csv' 
into table emp 
fields terminated by ','  -- 可修改分隔符
ignore 1 lines;

show global variables like '%secure_file_priv%';  -- 显示有安全权限的目录

select * from 表名; -- 检查导入数据内容
select count(*) from 表名; -- 检查导入数据总行数

-- 更新数据
-- set sql_safe_updates=0; -- 设置数据库安全权限，在提示数据库权限时用
UPDATE 表名 SET 字段名=2000 WHERE 字段名=7369;
UPDATE 表名 SET 表名=2000;

-- 删除数据
DELETE FROM 表名 WHERE 字段名=20;

-- 清空数据
TRUNCATE 表名;

2.5 Table内数据查找

-- 单表查询(虚拟结果集)
SELECT * FROM 表名;

-- 查询指定列：查询表中对应关键字的列
SELECT 关键字1,关键字2 FROM 表名;

-- 查询不重复的数据：查询指定表中对应关键字
SELECT DISTINCT 关键字1,关键字2 FROM 表名;

-- 条件查询
-- 查询条件的所属列
SELECT * FROM 表名;
SELECT * FROM 表名 WHERE 列名='条件';

-- 查询10号「部门」和20号「部门」中「工资」低于2000的员工信息
SELECT * FROM 表名 WHERE ('部门 '=10 or '部门' =20) and '工资' <2000;  -- and的优先级高于or
SELECT * FROM 表名 WHERE '部门 ' in(10,20) and '工资' <2000;

-- 练习：查询基本工资大于等于2000小于等于3000的员工信息
SELECT * FROM 表名 WHERE '工资'>=2000 and '工资'<=3000;  -- and的优先级高于or

SELECT * FROM 表名 WHERE 工资 BETWEEN 2000 AND 3000;

-- 空值查询，mac下所有null会被替换为0
-- 查询关键字为空的记录
SELECT * FROM 表名 WHERE 关键字 IS NULL;


-- 查询关键字不为空的记录
SELECT * FROM 表名 WHERE 关键字 IS NOT NULL;

-- 查询每位员工的实发工资(基本工资+提成,将指定关键字空值填充为0)
SELECT *,工资+IFNULL(提成,0)FROM 表名;


-- 设置别名
SELECT *,关键字+IFNULL(关键字,0) as '别名' FROM 表名;

UPDATE 表名 set 关键字=null where 关键字=0;
-- 练习：查询每位员工的年薪(基本工资*12):empno,ename,年薪
SELECT empno,ename,sal*12 as '年薪' FROM emp;

-- 模糊查询where...like...
-- 查询姓名以a开头的员工信息,%匹配后于所有字符，_匹配一个
SELECT * FROM 表名 WHERE 关键字 like 'a%';

-- 查询姓名中包含a的员工信息
SELECT * FROM 表名 WHERE 关键字 like '%a%';

-- 查询姓名中第二个字符为a的员工信息
SELECT * FROM 表名 WHERE 关键字 like '_a%';

-- 练习：查询员工姓名中不包含s的员工信息
SELECT * FROM 表名 WHERE 关键字 NOT like '%s%';

-- 查询结果排序
-- 单字段排序：查询信息按关键字降序(descend)显示,
SELECT * FROM 表名 ORDER BY 关键字 desc;

-- 多字段排序：查询信息先按关键字1升序、再关键字2降序显示
SELECT * FROM 表名 ORDER BY 关键字1,关键字2 desc;

-- 限制查询结果数量
-- 查询基本工资最高的前5位员工
SELECT * FROM 表名 ORDER BY 关键字 DESC LIMIT 数量;

-- 查询基本工资第6到10名的员工,LIMIT(相对首行的偏移量（默认为0），返回结果数量)
SELECT * FROM 表名 ORDER BY 关键字 DESC LIMIT 5,5;
SELECT * FROM 表名 ORDER BY 关键字DESC LIMIT 5 OFFSET 10; -- OFFSET后加需要的相对首行的偏移量

-- 练习：查询最后入职的5位员工
SELECT * FROM emp ORDER BY empno DESC LIMIT 5;