jiegojiegojiego

ChatGPT

https://chatgptmirror.com/chat

重载所有修改过的配置文件：systemctl daemon-reload

开启服务：systemctl start mysqld

开机自启：systemctl enable mysqld

载入数据

LOAD DATA LOCAL INFILE '/root/mysql/employee/employee.csv' INTO TABLE employeloyee FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

创建视图：

create view table1 as select calendar.id,check_date,is_work_day from calendar join attendance on calendar.calendar_date=attendance.check_date group by calendar.calendar_date;

创建视图：

 create view table2 as select emp_name,(select count(*) from calendar where is_work_day = 'Y')as att_days from employee,calendar group by emp_name;

创建视图：

create view table3 as select check_date as calendar_date,emp_name,clock_in from employee join attendance on employee.emp_id=attendance.emp_id  where time(clock_in) > '09:00:00' group

up by emp_name;

创建视图：

 create view table4 as select check_date as calendar_date,emp_name,clock_out from employee join attendance on employee.emp_id=attendance.emp_id  where time(clock_out) < '18:00:00' grouproup by emp_name limit 1,1;

limit 1，1从第二条记录开始，查询一行结果

笛卡尔积

SELECT c.calendar_date, e.emp_name, a.clock_in, a.clock_out FROM calendar c CROSS JOIN employee e LEFT JOIN attendance a ON a.check_date = c.calendar_date AND a.emp_id = e.emp_id WHERE (c.is_work_day = "Y" AND a.emp_id IS NULL) OR (a.check_date IS NOT NULL);

CREATE TABLE employee(

emp_id INT PRIMARY KEY,

emp_sex CHAR(40),

emp_email VARCHAR(50),

emp_salary VARCHAR(10),

emp_bonus VARCHAR(10),

emp_job_id INT,

emp_dept_id INT,

emp_manager VARCHAR(50),

emp_name CHAR(10),

emp_date DATE

);