MySQL自学总结，帮助初学者快速入门

自觉SQL语句写的不畅，便拿一本《MySQL必知必会》用了两天看完，觉得学得快，忘得也快！因此写一篇博客记录这两天所学，加深印象！写的仓促，不甚全面！仅仅快速学习了MySQL之轮廓，精髓不及一二。本文可以帮助初学者快速入门！

 以MySQL自带的world数据库中city、country、countrylanguage为例：

一、基本增删改查

1.select:
select * from city;

汇总数据：sum（）、avg（）、count（）、max（）、min（）
分组数据：group by
过滤：where、having
where过滤‘’行‘’数据，having过滤‘’组‘’数据。

表联结：

内联结：

select a.name as a_name,b.name as b_name,c.language as c_name from city a,country b,countrylanguage c where a.countrycode = b.code and a.countrycode =c.countrycode ;

也可以：

select a.name as a_name,b.name as b_name,c.language as c_name from (city a inner join country b on a.countrycode=b.code) inner join countrylanguage c on b.code=c.countrycode;

外联结
左右外联：left outer join,right outer join与left join、right join等同
以left、right一侧的表为主，若另一侧为空值，会以left、right一侧的表为主：

select a.name as a_name,b.name as b_name from city a left join country b on a.countrycode = b.code;

自连接：
select a.name as a_name,b.name as b_name from city a inner join city b on a.countrycode = b.countrycode where a.Population=237500
自然联结：通过MySQL自己的判断完成连接过程，不需要指定连接条件。MySql会使用表内的，相同的字段，作为连接条件。

2.Insert：

insert into city（name）values（），（），（）;
insert into city select * from city_new;

3.update：

update city 
    set name=‘KFC’ ，
        language=‘ZH’
    where id=1

4.delete:

delete from city where id=1;

PS:更新删除操作后边where必须带上条件，否则就是更新删除全表；
二、创建表
1.普通表创建

create table if not exists user_test(
    id int(50) not null auto_increment,
    name char(50) not null,
    adreess char(50) null default 'KFC' ,
    primary key(id)
)engine=myisam;

2.引擎
InnoDB、MyISAM、Memory：
InnoDB：支持事务
MyISAM：不支持事务
Memory：直接存储于内存，查询速度极快
三、视图

create or replace view my_view as
    select a.name,b.language from city a left join countrylanguage b on a.countrycode=b.countrycode;

四、存储过程
1.简单创建、执行、删除

create procedure my_procedure（）
begin
    select * from city;
end;

call my_procedure();

drop procedure if exists my_procedure

2.带参数：out 出参，in 入参，outin 传入传出

create procedure getMaxPopulationCity(out maxPopulation decimal(18,0))
BEGIN
    select max(population) into maxPopulation from city;
END;
call getMaxPopulationCity(@mx);
select @mx;
drop procedure if exists getMaxPopulationCity;

3.入参加出参：

create procedure avg_population(
    in num int,
    out avg_num decimal(18,2)
)
begin
select avg(a.p) into avg_num from (select population as p from city order by population desc limit 0,num) a;
end;
call avg_population(2,@avg_num);
select @avg_num;
drop procedure if exists avg_population;

4.加强版：

create procedure avg_population(
    in num int,
    in isNeedAVG boolean,
    out avg_num decimal(18,2),
    out sum_num decimal(18,2)
)
begin
declare page int default 2;
        if isNeedAVG THEN
                select avg(a.p) from (select population as p from city order by population desc limit page,num) a into avg_num;
        ELSE
                select sum(a.p) from (select population as p from city order by population desc limit 0,num) a into sum_num;
        end if;
end;

call avg_population(2,0,@avg_num,@sum_num);
select @avg_num,@sum_num;
drop procedure if exists avg_population;

5.游标：

create procedure avg_area(
    in v_name char(52),
    out result decimal(18,2)
)
BEGIN
    select population/surfacearea into result from country where name=v_name;
END;

CREATE PROCEDURE list_info_country()
BEGIN
    declare done boolean default 0;
    declare v_name char(52);
    declare result decimal(18,2);
    declare count cursor for select name from country;

declare continue handler for sqlstate '02000' set done=1;

create table result_info(
    name char(52),
    avg_area decimal(18,2)
);

open count;
REPEAT
    fetch count into v_name;

    call avg_area(v_name,result);

    insert into result_info(name,avg_area) values(v_name,result);
UNTIL done END REPEAT;

close count;

end;

call list_info_country();
select * from result_info;

drop table result_info;
drop procedure if exists list_info_country;
drop procedure if exists avg_area;

四、触发器
1.只有Insert、Update、Delete支持触发器，mysql其他语句不支持。
2.创建于删除触发器：

create trigger added_info after insert on result_info for each row insert into info values('Insert success!');
drop trigger added_info;

3.触发器值支持表，不支持视图与临时表。