Day 1: SQL（2020.03.2）

1. 數(shù)據(jù)分析流程

業(yè)務(wù)理解->數(shù)據(jù)收集->數(shù)據(jù)處理->分析預(yù)測->數(shù)據(jù)展現(xiàn)->成果報(bào)告

2.數(shù)據(jù)庫常用命令

2.1常規(guī)查看

-- 查看系統(tǒng)中有哪些數(shù)據(jù)庫
SHOW DATABASES;

-- 創(chuàng)建cda數(shù)據(jù)庫
CREATE DATABASE 庫名;

-- 選擇進(jìn)入數(shù)據(jù)庫
use 庫名;

-- 刪除數(shù)據(jù)庫(慎用)
-- drop database 庫名;

2.2 Table級別的增刪改

-- 創(chuàng)建數(shù)據(jù)表
CREATE TABLE 表名(
did int(10),
dname VARCHAR(11)  -- varchar必須限制長度
);

-- 查看當(dāng)前數(shù)據(jù)庫中有哪些表
SHOW TABLES;

-- 查看表結(jié)構(gòu)
desc 表名;


-- 刪除數(shù)據(jù)表(慎用)
DROP TABLE 表名;


-- 創(chuàng)建帶有約束條件的表
CREATE TABLE 表名(
deptno int PRIMARY KEY,  -- 主鍵
dname VARCHAR(10),
loc VARCHAR(10)
);
CREATE TABLE employee(
empid int PRIMARY KEY auto_increment,  -- 主鍵+自動(dòng)增長
ename VARCHAR(10) UNIQUE,  -- 聯(lián)合約束
job VARCHAR(10) not NULL,
mgr int,
hiredate date,
sal FLOAT DEFAULT(0),
comm FLOAT,
deptno int ,
FOREIGN KEY(deptno) REFERENCES dept(deptno)
-- 外部約束deptno 依賴于上一個(gè)表dept中的deptno
);

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

2.3 Table內(nèi)部字段刪改（字段名等價(jià)于列名）

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

-- 修改字段類型
ALTER TABLE 表名 MODIFY 字段名 元素類型 約束條件;
  -- 只能改成非空，默認(rèn)峭判，自增林螃。

-- 添加字段
ALTER TABLE 表名 ADD 字段名 元素類型 位置;
  -- 默認(rèn)加在最后一列治宣，first就放在首列

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


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

2.4 Table內(nèi)數(shù)據(jù)增刪改


-- 插入數(shù)據(jù)：字段名與字段值的數(shù)據(jù)類型侮邀、個(gè)數(shù)绊茧、順序必須一一對應(yīng)
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 下導(dǎo)入數(shù)據(jù)目錄與權(quán)限的更改

1. 首先查看權(quán)限目錄
   在MySQL下輸入: show global variables like '%secure_file_priv%';  -- 顯示有安全權(quán)限的目錄
2. 當(dāng)?shù)玫?secure_file_priv 值為NULL時(shí)，說明不允許輸入輸出
  終端輸入: sudo vim /etc/my.cnf  打開my.cnf文件配置
  輸入: [mysqld_safe]
        [mysqld]
        secure_file_priv=""
  然后ESC->:wq->回車 保存
3.重啟MySQL
4.若在導(dǎo)入時(shí)出現(xiàn)權(quán)限問題
 終端: sudo chown -R mysql 文件所在目錄地址
 來打開目錄權(quán)限

-- 批量導(dǎo)入數(shù)據(jù)
load data infile '/Users/pal/MysqlFile/employee.csv' 
into table emp 
fields terminated by ','  -- 可修改分隔符
ignore 1 lines;

show global variables like '%secure_file_priv%';  -- 顯示有安全權(quán)限的目錄

select * from 表名; -- 檢查導(dǎo)入數(shù)據(jù)內(nèi)容
select count(*) from 表名; -- 檢查導(dǎo)入數(shù)據(jù)總行數(shù)

-- 更新數(shù)據(jù)
-- set sql_safe_updates=0; -- 設(shè)置數(shù)據(jù)庫安全權(quán)限，在提示數(shù)據(jù)庫權(quán)限時(shí)用
UPDATE 表名 SET 字段名=2000 WHERE 字段名=7369;
UPDATE 表名 SET 表名=2000;

-- 刪除數(shù)據(jù)
DELETE FROM 表名 WHERE 字段名=20;

-- 清空數(shù)據(jù)
TRUNCATE 表名;

2.5 Table內(nèi)數(shù)據(jù)查找

-- 單表查詢(虛擬結(jié)果集)
SELECT * FROM 表名;

-- 查詢指定列：查詢表中對應(yīng)關(guān)鍵字的列
SELECT 關(guān)鍵字1,關(guān)鍵字2 FROM 表名;

-- 查詢不重復(fù)的數(shù)據(jù)：查詢指定表中對應(yīng)關(guān)鍵字
SELECT DISTINCT 關(guān)鍵字1,關(guān)鍵字2 FROM 表名;

-- 條件查詢
-- 查詢條件的所屬列
SELECT * FROM 表名;
SELECT * FROM 表名 WHERE 列名='條件';

-- 查詢10號「部門」和20號「部門」中「工資」低于2000的員工信息
SELECT * FROM 表名 WHERE ('部門 '=10 or '部門' =20) and '工資' <2000;  -- and的優(yōu)先級高于or
SELECT * FROM 表名 WHERE '部門 ' in(10,20) and '工資' <2000;

-- 練習(xí)：查詢基本工資大于等于2000小于等于3000的員工信息
SELECT * FROM 表名 WHERE '工資'>=2000 and '工資'<=3000;  -- and的優(yōu)先級高于or

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

-- 空值查詢，mac下所有null會(huì)被替換為0
-- 查詢關(guān)鍵字為空的記錄
SELECT * FROM 表名 WHERE 關(guān)鍵字 IS NULL;


-- 查詢關(guān)鍵字不為空的記錄
SELECT * FROM 表名 WHERE 關(guān)鍵字 IS NOT NULL;

-- 查詢每位員工的實(shí)發(fā)工資(基本工資+提成,將指定關(guān)鍵字空值填充為0)
SELECT *,工資+IFNULL(提成,0)FROM 表名;


-- 設(shè)置別名
SELECT *,關(guān)鍵字+IFNULL(關(guān)鍵字,0) as '別名' FROM 表名;

UPDATE 表名 set 關(guān)鍵字=null where 關(guān)鍵字=0;
-- 練習(xí)：查詢每位員工的年薪(基本工資*12):empno,ename,年薪
SELECT empno,ename,sal*12 as '年薪' FROM emp;

-- 模糊查詢where...like...
-- 查詢姓名以a開頭的員工信息,%匹配后于所有字符衙传，_匹配一個(gè)
SELECT * FROM 表名 WHERE 關(guān)鍵字 like 'a%';

-- 查詢姓名中包含a的員工信息
SELECT * FROM 表名 WHERE 關(guān)鍵字 like '%a%';

-- 查詢姓名中第二個(gè)字符為a的員工信息
SELECT * FROM 表名 WHERE 關(guān)鍵字 like '_a%';

-- 練習(xí)：查詢員工姓名中不包含s的員工信息
SELECT * FROM 表名 WHERE 關(guān)鍵字 NOT like '%s%';

-- 查詢結(jié)果排序
-- 單字段排序：查詢信息按關(guān)鍵字降序(descend)顯示,
SELECT * FROM 表名 ORDER BY 關(guān)鍵字 desc;

-- 多字段排序：查詢信息先按關(guān)鍵字1升序、再關(guān)鍵字2降序顯示
SELECT * FROM 表名 ORDER BY 關(guān)鍵字1,關(guān)鍵字2 desc;

-- 限制查詢結(jié)果數(shù)量
-- 查詢基本工資最高的前5位員工
SELECT * FROM 表名 ORDER BY 關(guān)鍵字 DESC LIMIT 數(shù)量;

-- 查詢基本工資第6到10名的員工,LIMIT(相對首行的偏移量（默認(rèn)為0）俊鱼，返回結(jié)果數(shù)量)
SELECT * FROM 表名 ORDER BY 關(guān)鍵字 DESC LIMIT 5,5;
SELECT * FROM 表名 ORDER BY 關(guān)鍵字DESC LIMIT 5 OFFSET 10; -- OFFSET后加需要的相對首行的偏移量

-- 練習(xí)：查詢最后入職的5位員工
SELECT * FROM emp ORDER BY empno DESC LIMIT 5;