sql

建表

create table example (id int not null primary key,           
                        name varchar(20) not null,                                      
                        stu_id int,                                      
                        constraint d_fk foreign key(stu_id)           
                            references example1(stu_id)                                         )

修改字段属性和位置

alter table example modify name varchar(30) <first/after> <column> 

修改字段名称

alter table example change name1 name2 varchar(20)

增加字段

alter table example add address varchar(40)  not null  after phone

更改表的引擎

alter table user engine=<myisam>

删外键

alter table [table_name] drop foreign key [alias]

创建索引

create table <table_name> (filed_name type [constrains]，
                            filed_name type [constrains],                           filed_name type [constrains],
                            [unique | fulltext | spatial] index | key
                                [alias] (name [length] [asc | desc]
                        );

create table index (id int unique, name varchar(20), unique index index_id (id asc));       

create table index (id int, subject varchar(30),  index index_st(subject10));

create table index5 (id int, name varchar(20), sex char(4), index index_ns(name, sex));

原图创建索引

create [unique | fulltext | spatial] index <index_name>
on <table_name> (field_name [(length)] [ asc | desc] );

create unique index index_id on table_name(field_name , [field_name2]);

alter table table_name add  [unique | fulltext | spatial] index <index_name>  (field_name [(length)] [ asc | desc] )

 alter table example add unique index index_name (name(20));

删除索引

drop index index_name on table_name;

创建视图

create [algorithm = {undefined | merge | temptable} ]
view view_name [(field_name_list)]
as select... [with [cascaded | local]  check option];

create algorithm=merge
    view1(name,sex,age,address)
    as select name, age, sex, address
        from people,work where people.id =work.peo_id
        with local check option;

create or replace [algorithm = {undefined | merge | temptable} ]
        view view_name [(field_name_list)]
        as select... 
        [with [cascaded | local]  check option];

alter [algorithm = {undefined | merge | temptable} ]
        view view_name [(field_name_list)]
        as select... 
        [with [cascaded | local]  check option];    

删除视图

drop view [if exists] view_name_list [restrict | cascade]

创建触发器

create trigger tri_name before | after event_name 
    on table_name for each row query...

create trigger tri_1 before insert
    on table_name for each row insert into now_time values (now());

create trigger tri_name berfore|after event_name 
    on table_name for each row 
    begin
        query
    end 

delimiter &&
create trigger tri_2 after delete 
    on table_name for each row
    begin
        insert into people value('xiaoming', 'male');
        insert into people value('xiaohong', 'female')
    end 
    &&
delimiter ; 

查询语句

select field_name_list 
        from table_name,view_name
        [where condition]
        [group by field_name [having condition][with rollup]]
        [group by field_name asc|desc]

condition 
[not] in ()
[not] between 10 and 20
[not] like ""  %任意长，_单个字符
is [not] null

in,not in, any, all, exists, not exists

插入

insert into table_name [(field_name_list)] values (values) [,(),()...]

更新

update table_name
        set fidle_name1=value1 [....,]

条件函数

if(expr,value_one, value_two)
    select id,name if(age > 10 'young', 'old') from people

ifnull(value_one, value_two)    

case when expr1 then value_one[when expr2 then value_two...][else value_n]end

加密函数

password('str')
md5('str')
encode('str', ps_str)
decode('crypt_str', ps_str)

format(int, digit）

ascii('str')
bin(int)
hex(int)
oct(int)
conv(int, radix1, radix2)

inet_aton(ip) => int
inet_ntoa(int) => ip

get_lock(lock_name,time)
release_lock(name)
is_free_lock(name)

benchmark(count, expr)expr执行count次

convert('str' usinggbk)

cast(field as type)
convert(x, type)
改变输出指的类型

创建存储过程

create procedure sp_name([proc_parameter[,...]])[characteristic ...] query_body

proc_parameter:[in|out|inout] parameter_name type

characteristic:language sql(query_body 是sql语句), [not] deterministic(默认非确定，相同输入可能不同输出), {contains sql|no sql| reads sql data|modifies sql data}(sql语句有无读写操作),sql security{definer|invoker}(定义者才能执行，调用者可以执行),comment'string'


create procedure num_from_pop (in emp_id int, out count_num int)
    reads sql data
    begin 
        select count(*) into count_num
        from employee
        where d_id=emp_id;
    end

创建存储函数

create function sp_name([func_parameter_list[,...]])
    returns type
    [characteristic ...]routine_body

变量

declare mysql int default 10;
set mysql=30;
select id into mysql from pop where id = 2;

条件

declare condition_name condition for condition_value
condition_value:
    sqlstate[value] sqlstate_value | mysql_error_code

declare can_not_find condition for sqlstate '42s02';
declare can_not_find condition for 1146;    

程序

declare handler_type handler for condition_value[,...] sp_statement
handler_type;
    continue | exit |undo
condition_value;
    sqlstate[value]sqlstate_value | condition_name | sqlwarning |not found |sqlexception |mysql_error_code

1.declare continue handler for sqlstate '42s02' set @info='can not find';
2.declare continue handler for 1146 set @info='can not find';
3.can_not_find condition for 1146;
declare continue handler for can_not_find set @info='error';

光标

declare cursor_name cursor for select_statement;
open cursor_name;
fetch cur_result into var_name[var_name,...];
close cursor_name;

流程

if search_condition then statement_list
    [elseif search_condition then statement_list]...
    [else statement_list]
end if 

case case_value 
    when when_value then statement_list
    [when when_value then statement_list]...
    [else statement_list]   
end case

if age>20 then set @grade=@grade+2;
    elseif age=then @grade=@grade+1;
    else @grade=@grade-1;
end if; 

[begin_label:]loop
    statement_list
end loop [end_label]    

leave label

iterate label

add_num:loop
    set @num=@num+1;
    if @num=100 then
        leave add_num
    elseif mod(@num ,3)=0 then
        iterate add_num;
    select * from pop
end loop add_num;   

[begin_label:]repeat
    statement_list
    until search_condition
end repeat[end_label]   

[begin_label:] while search_condition do
    statement_list
end while[end_label]    

显示过程和函数的状态与定义

show {procedure|function} state [like 'pattern'];
show create {procedure | function } sp_name;

修改过程和函数

alter {procedure | function} sp_name [characteristic ...]
characteristic:
    {contains sql|no sql |reads sql data|modifies sql data | sql security{definer | invoker}}
    |comment 'string'

alter procedure num_from_peo
    modifies sql data
    sql security invoker;

删除过程和函数

dorp {procedure | function} sp_name;

备份

mysqldump -u username -p dbname [dbname] [table_name ...] > xxx.mysql

explain| desc

type: system一条记录、const多条中查询一条，all全表扫描，eq_ref多表链接后表用unique或pk，ref多表查询后表用普通索引，unique_subquery子查询中使用unique或pk，index_subquery子查询中普通索引，range给出查询范围，index对索引进行完整扫描