SQL

 update

update patients set Status='active' where id=7;

 create table

create table inactive_patients as  select * from patients where status='inactive';

delete from

delete from patients where status='inactive';

 alter table

alter table patients rename to active_patients;

with...select

with total_active as (select count(*) as active from active_patients),
total_inactive as (select count(*) as inactive from inactive_patients),
total_patients as (select total_active.active+total_inactive.inactive as total 
from total_active,total_inactive) select total_active.active*100/total_patients.total 
as percent_active, 
total_inactive.inactive*100/total_patients.total as percent_inactive from total_active, 
total_inactive,total_patients;

↓这两者都可以

select * ,max(id) from active_patients;
select * from active_patients where id=(select max(id) from active_patients);

 union , random()

select * from (select name,address,phone from active_patients 
union 
select name,address,phone from inactive_patients) order by random() limit 10;

先写 group by , 然后是having ,然后是order by

alter table add column; update...set

alter table panels add column LDL decimal(5,2);
update panels set LDL=panels.Cholesterol-(panels.HDL+panels.Triglycerides/5);

 最下面这个折腾了我很久。其一是rv，它只是(SELECT Borderline_High, High FROM recommended_values WHERE Lipid = "LDL")的临时名称。其二是group by有两个参数，LDL 和Name。问题是明明没有重复的Name,然而去掉LDL之后，还会少很多数据。原来是因为LDL是被join的主表Panels中的列，而Name是active_patients 中的列，它join到Panels之后，就出现了重复值，一个病人可能检查了两次，一次有LDL超标，一次没有超标，如果只用Name来分，就会漏掉（是随机选择超标还是没超标的数么？还是只要有没超标的记录就不符合要求？），所以还要LDL一起来分。

SELECT 
     active_patients.Name AS Name, 
    round(LDL) AS LDL, 
    active_patients.Address AS Address, 
    active_patients.Phone AS Phone
FROM 
    panels, 
    (SELECT Borderline_High, High FROM recommended_values WHERE Lipid = "LDL") rv
JOIN active_patients ON panels.patient_ID = active_patients.ID
GROUP BY 
    LDL, 
    active_patients.Name 
HAVING LDL >= rv.Borderline_High
ORDER BY LDL DESC;

 这里和上面的with 很相似，但是处理方式不一样

SELECT 
    COUNT(CASE WHEN (LDL >= rv.Borderline_High AND LDL < rv.High) THEN LDL END) AS Borderline_High,
    COUNT(CASE WHEN LDL >= rv.High THEN LDL END) AS High
FROM 
    panels, 
    (SELECT Borderline_High, High FROM recommended_values WHERE Lipid = "LDL") rv;