Mysql学习记录

mysql (内连接，外连接，左连接，右连接，自连接）学习记录

• 内连接只显示连接过程中交叉的部分

select * from A a inner join B b on a.id = b.id

• 左连接右连接同属于外连接

select * from A a left join B b on a.id = b.id -- 显示左表全部记录以及左右表同时存在的记录
select * from A a right join B b on a.id = b.id -- 显示右表全部记录以及左右表同时存在的记录

• 为现有表添加主键

  alter table my_contacts
add column contact_id int not null auto_increment first,
add primary key (contact_id);

• 使用CASE表达式来Update
  update movie_table
set category =
case
when drama = 'T' then 'drama'
when comedy = 'T' then 'comedy'
else 'misc
end;

分组排序

MySQL 5.6 Schema Setup:

CREATE TABLE DistrictProducts
(district  VARCHAR(16) NOT NULL,
 name      VARCHAR(16) NOT NULL,
 price     INTEGER NOT NULL,
 PRIMARY KEY(district, name, price));

INSERT INTO DistrictProducts VALUES('东北', '橘子',	100);
INSERT INTO DistrictProducts VALUES('东北', '苹果',	50);
INSERT INTO DistrictProducts VALUES('东北', '葡萄',	50);
INSERT INTO DistrictProducts VALUES('东北', '柠檬',	30);
INSERT INTO DistrictProducts VALUES('关东', '柠檬',	100);
INSERT INTO DistrictProducts VALUES('关东', '菠萝',	100);
INSERT INTO DistrictProducts VALUES('关东', '苹果',	100);
INSERT INTO DistrictProducts VALUES('关东', '葡萄',	70);
INSERT INTO DistrictProducts VALUES('关西', '柠檬',	70);
INSERT INTO DistrictProducts VALUES('关西', '西瓜',	30);
INSERT INTO DistrictProducts VALUES('关西', '苹果',	20);

Query 1:

 select p1.district, p1.name, p1.price,
 (select count(distinct p2.price) + 1 from DistrictProducts p2 
						 ---  (select count(p2.price) + 1 from DistrictProducts p2
  where p1.district = p2.district 
  and p1.price > p2.price ) as rank 
   from DistrictProducts p1

Results:

| district | name | price | rank |
|----------|------|-------|------|
|       东北 |   柠檬 |    30 |    1 |
|       东北 |   橘子 |   100 |    3 |
|       东北 |   苹果 |    50 |    2 |
|       东北 |   葡萄 |    50 |    2 |
|       关东 |   柠檬 |   100 |    2 |
|       关东 |   苹果 |   100 |    2 |
|       关东 |   菠萝 |   100 |    2 |
|       关东 |   葡萄 |    70 |    1 |
|       关西 |   柠檬 |    70 |    3 |
|       关西 |   苹果 |    20 |    1 |
|       关西 |   西瓜 |    30 |    2 |

CREATE TABLE DistrictProducts2
(district  VARCHAR(16) NOT NULL,
 name      VARCHAR(16) NOT NULL,
 price     INTEGER NOT NULL,
 ranking   INTEGER,
 PRIMARY KEY(district, name));

    INSERT INTO DistrictProducts2 VALUES('东北', '橘子',	100, null);
    INSERT INTO DistrictProducts2 VALUES('东北', '苹果',	50, null);
    INSERT INTO DistrictProducts2 VALUES('东北', '葡萄',	50, null);
    INSERT INTO DistrictProducts2 VALUES('东北', '柠檬',	30, null);
    INSERT INTO DistrictProducts2 VALUES('关东', '柠檬',	100, null);
    INSERT INTO DistrictProducts2 VALUES('关东', '菠萝',	100, null);
    INSERT INTO DistrictProducts2 VALUES('关东', '苹果',	100, null);
    INSERT INTO DistrictProducts2 VALUES('关东', '葡萄',	70, null);
    INSERT INTO DistrictProducts2 VALUES('关西', '柠檬',	70, null);
    INSERT INTO DistrictProducts2 VALUES('关西', '西瓜',	30, null);
    INSERT INTO DistrictProducts2 VALUES('关西', '苹果',	20, null);
    
    
     
UPDATE DistrictProducts2 P1
  SET ranking = (SELECT COUNT(P2.price) + 1
                     FROM DistrictProducts2 P2
                     WHERE P1.district = P2.district
                     AND P2.price > P1.price);

win10卸载mysql完成后，删除服务solutions

最后sc delete mysql 需要管理员运行