1.從excel導入數據

在excel導入數據時要先在數據庫中創建對應的數據庫表

CREATE TABLE your_table_name (crawl_datetime DATE,url CHAR(255),company_name CHAR(255),company_size CHAR(255),company_type CHAR(255),job_type CHAR(255),job_name CHAR(255),edu CHAR(255),empltype CHAR(255),tag VARCHAR(255), -- 刪除逗號salary CHAR(255),city CHAR(255),workingexp CHAR(255),resume_count SMALLINT,company_score DOUBLE, -- 刪除長度work_place VARCHAR(255),require_content TEXT
);

接下來是操作的步驟

先選擇導入向導，

?

然后選擇excel

選擇文件，選擇需要導入的表

之后點擊開始等待一會就結束了

2.嘗試簡單查詢/復雜查詢

#select COUNT(*) FROM 智聯招聘
#select COUNT(*) FROM 智聯招聘 WHERE crawl_datetime > '2018-12-28'; 
#select * FROM 智聯招聘 UNION select * FROM 智聯招聘 #14.07s
#select url as 地址 FROM 智聯招聘

select url as 地址,crawl_datetime AS 時間,resume_count AS 申請人數 
FROM 智聯招聘
ORDER BY resume_count DESC; #1.509S

3.在select中使用if/case語句（from、where中也可以）

select url as 地址,crawl_datetime AS 時間,resume_count AS 申請人數 
FROM 智聯招聘
WHERE resume_count >= ALL (select resume_count FROM 智聯招聘 WHERE crawl_datetime = '2018-12-29' 
)
ORDER BY resume_count DESC; #2.333S

4.創建視圖

/*
CREATE VIEW 時間分類 AS
select job_name,CASE WHEN DAY(crawl_datetime) = 28 THEN 'good'ELSE 'bad'END 
from 智聯招聘 #1.347s
*/#SELECT * from 時間分類 #1.348s

5.創建存儲過程

DELIMITER $$
CREATE PROCEDURE get_job_name()
BEGINSELECT job_name FROM `智聯招聘`;
END $$
DELIMITER ;CALL get_job_name();

6.存儲過程中的本地變量

DROP PROCEDURE get_locak;
DELIMITER $$
CREATE DEFINER='sa'@localhost PROCEDURE get_locak(jobname VARCHAR(50),OUT tag2 VARCHAR(50),OUT edu2 VARCHAR(50)
)
BEGINSELECT tag,eduINTO tag2,edu2FROM `智聯招聘` WHERE job_name = jobname;
END $$
DELIMITER ;
CALL get_locak('湖南業務代表', @tag, @edu);SELECT @tag AS tag, @edu AS edu;
#注意不要重名

7.創建函數

CREATE FUNCTION get_risk_factor2()
RETURNS INTEGER
READS SQL DATA
BEGINDECLARE risk_id DECIMAL(9,2) DEFAULT 0;-- 計算風險因素的邏輯-- 假設你有一個與該函數相關的數據表來獲取風險因素-- 可以是一些計算，或者從其他地方獲取的值-- 這里我只是簡單地給 risk_id 賦值為 1，你需要根據實際情況進行修改SET risk_id = 1;-- 將風險因素乘以 5SET risk_id = risk_id * 5;-- 返回風險因素的值RETURN risk_id;
END;

8.創建觸發器

CREATE TRIGGER 添加之前觸發BEFORE INSERT ON `智聯招聘`FOR EACH ROW
BEGININSERT INTO 實踐觸發器(content)VALUES (NOW());
END

9.創建事件

CREATE EVENT 每一分鐘都添加
ON SCHEDULEEVERY 1 MINUTE
DO BEGININSERT INTO 實踐事件(content)VALUES (NOW());
END;SHOW EVENTS LIKE '每一分鐘%';
ALTER EVENT 每一分鐘都添加 DISABLE;

10.創建事務

START TRANSACTION;SELECT * FROM `智聯招聘`;COMMIT ;SHOW VARIABLES;

11.創建索引

EXPLAIN SELECT tag FROM `智聯招聘` #查看
create INDEX 標簽 on `智聯招聘`(tag); #2.881
SELECT tag FROM `智聯招聘` #0.008s #沒有索引是1.303s
DROP INDEX 標簽 on `智聯招聘`;