sql实践

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()
BEGIN
	SELECT 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)
)
BEGIN
	SELECT tag,edu
	INTO tag2,edu2
	FROM `智联招聘` 
	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
BEGIN
    DECLARE risk_id DECIMAL(9,2) DEFAULT 0;
    -- 计算风险因素的逻辑
    -- 假设你有一个与该函数相关的数据表来获取风险因素
    -- 可以是一些计算，或者从其他地方获取的值
    -- 这里我只是简单地给 risk_id 赋值为 1，你需要根据实际情况进行修改
    SET risk_id = 1;
    -- 将风险因素乘以 5
    SET risk_id = risk_id * 5;
    -- 返回风险因素的值
    RETURN risk_id;
END;

8.创建触发器

CREATE TRIGGER 添加之前触发
	BEFORE INSERT ON `智联招聘`
	FOR EACH ROW
BEGIN
	INSERT INTO 实践触发器(content)
	VALUES (NOW());
END

9.创建事件

CREATE EVENT 每一分钟都添加
ON SCHEDULE
	EVERY 1 MINUTE
DO BEGIN
	INSERT 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 `智联招聘`;