MYSQL总结

系统 2098 0
      
          1
      
      
        #登录数据库


      
      
          2
      
       mysql 
      
        -
      
      hlocalhost 
      
        -
      
      uroot 
      
        -
      
      
        p;


      
      
          3
      
      
        #修改密码


      
      
          4
      
       mysqladmin 
      
        -
      
      uroot 
      
        -
      
      
        pold password new;


      
      
          5
      
      
          6
      
      
          7
      
      
        #显示数据库


      
      
          8
      
      
        show databases;


      
      
          9
      
      
        #显示数据表


      
      
         10
      
      
        show tables;


      
      
         11
      
      
        #选择数据库


      
      
         12
      
      
        use
      
      
         examples;


      
      
         13
      
       #创建数据库并设置编码utf
      
        -
      
      
        8
      
      
         多语言


      
      
         14
      
      
        create
      
      
        database
      
       `examples` 
      
        default
      
      
        character
      
      
        set
      
      
         utf8 collate utf8_general_ci;


      
      
         15
      
      
        #删除数据库


      
      
         16
      
      
        drop
      
      
        database
      
      
         examples;


      
      
         17
      
      
        #创建表


      
      
         18
      
      
        create
      
      
        table
      
      
         test(


      
      
         19
      
           id 
      
        int
      
      (
      
        10
      
      ) unsigned zerofill 
      
        not
      
      
        null
      
      
         auto_increment,


      
      
         20
      
           email 
      
        varchar
      
      (
      
        40
      
      ) 
      
        not
      
      
        null
      
      
        ,


      
      
         21
      
           ip 
      
        varchar
      
      (
      
        15
      
      ) 
      
        not
      
      
        null
      
      
        ,


      
      
         22
      
           state 
      
        int
      
      (
      
        10
      
      ) 
      
        not
      
      
        null
      
      
        default
      
      
        '
      
      
        -1
      
      
        '
      
      
        ,


      
      
         23
      
      
        primary
      
      
        key
      
      
         (id)


      
      
         24
      
       )engine
      
        =
      
      
        InnoDB;


      
      
         25
      
      
        #显示表结构


      
      
         26
      
      
        describe 


      
      
         27
      
      
        #删除表


      
      
         28
      
      
        drop
      
      
        table
      
      
         test;


      
      
         29
      
      
        #重命名表


      
      
         30
      
      
        alter
      
      
        table
      
      
         test_old rename test_new;


      
      
         31
      
      
        #添加列


      
      
         32
      
      
        alter
      
      
        table
      
       test 
      
        add
      
       cn 
      
        int
      
      (
      
        4
      
      ) 
      
        not
      
      
        null
      
      
        ;


      
      
         33
      
      
        #修改列


      
      
         34
      
      
        alter
      
      
        table
      
       test change id id1 
      
        varchar
      
      (
      
        10
      
      ) 
      
        not
      
      
        null
      
      
        ;


      
      
         35
      
      
        #删除列 


      
      
         36
      
      
        alter
      
      
        table
      
       test 
      
        drop
      
      
         cn;


      
      
         37
      
      
        #创建索引


      
      
         38
      
      
        alter
      
      
        table
      
       test 
      
        add
      
      
        index
      
      
         (cn,id);


      
      
         39
      
      
        #删除索引


      
      
         40
      
      
        alter
      
      
        table
      
       test 
      
        drop
      
      
        index
      
      
         cn


      
      
         41
      
      
        #插入数据


      
      
         42
      
      
        insert
      
      
        into
      
       test (id,email,ip,state) 
      
        values
      
      (
      
        2
      
      ,
      
        '
      
      
        qq@qq.com
      
      
        '
      
      ,
      
        '
      
      
        127.0.0.1
      
      
        '
      
      ,
      
        '
      
      
        0
      
      
        '
      
      
        );


      
      
         43
      
      
        #删除数据 


      
      
         44
      
      
        delete
      
      
        from
      
       test 
      
        where
      
       id 
      
        =
      
      
        1
      
      
        ;


      
      
         45
      
      
        #修改数据


      
      
         46
      
      
        update
      
       test 
      
        set
      
       id
      
        =
      
      
        '
      
      
        1
      
      
        '
      
      ,email
      
        =
      
      
        '
      
      
        q@qq.com
      
      
        '
      
      
        where
      
       id
      
        =
      
      
        1
      
      
        ;


      
      
         47
      
      
        #查数据


      
      
         48
      
      
        select
      
      
        *
      
      
        from
      
      
         test;  #取所有数据


      
      
         49
      
      
        select
      
      
        *
      
      
        from
      
       test limit 
      
        0
      
      ,
      
        2
      
      
        ;  #取前两条数据 


      
      
         50
      
      
        select
      
      
        *
      
      
        from
      
       test email 
      
        like
      
      
        '
      
      
        %qq%
      
      
        '
      
       #查含有qq字符 _表示一个 
      
        %
      
      
        表示多个


      
      
         51
      
      
        select
      
      
        *
      
      
        from
      
       test 
      
        order
      
      
        by
      
       id 
      
        asc
      
      
        ;#降序desc


      
      
         52
      
      
        select
      
      
        *
      
      
        from
      
       test id 
      
        not
      
      
        in
      
      (
      
        '
      
      
        2
      
      
        '
      
      ,
      
        '
      
      
        3
      
      
        '
      
      
        );#id不含2,3或者去掉not表示含有


      
      
         53
      
      
        select
      
      
        *
      
      
        from
      
       test timer 
      
        between
      
      
        1
      
      
        and
      
      
        10
      
      
        ;#数据在1,10之间


      
      
         54
      
      
         55
      
       #
      
        --
      
      
        -------------------------表连接知识------------------------------
      
      
         56
      
       #等值连接又叫内链接 
      
        inner
      
      
        join
      
      
         只返回两个表中连接字段相等的行


      
      
         57
      
      
        select
      
      
        *
      
      
        from
      
       A 
      
        inner
      
      
        join
      
       B 
      
        on
      
       A.id 
      
        =
      
      
         B.id; #写法1


      
      
         58
      
      
        select
      
      
        *
      
      
        from
      
       A,B 
      
        where
      
       A.id 
      
        =
      
      
         B.id; #写法2


      
      
         59
      
      
        select
      
       a.id,a.title 
      
        from
      
       A a 
      
        inner
      
      
        join
      
       B b 
      
        on
      
       a.id
      
        =
      
      b.id 
      
        and
      
       a.id
      
        =
      
      
        1
      
      
        ;#写法3 表的临时名称


      
      
         60
      
      
        select
      
       a.id 
      
        as
      
       ID,a.title 
      
        as
      
       标题 
      
        from
      
       A 
      
        inner
      
      
        join
      
       B 
      
        on
      
       A.id
      
        =
      
      
        B.id;#添加as字句


      
      
         61
      
      
         62
      
       #左连接又叫外连接 
      
        left
      
      
        join
      
      
         返回左表中所有记录和右表中连接字段相等的记录


      
      
         63
      
      
        select
      
      
        *
      
      
        from
      
       A 
      
        left
      
      
        join
      
       B 
      
        on
      
       A.id 
      
        =
      
      
         B.id;


      
      
         64
      
      
         65
      
      
        select
      
      
        *
      
      
        from
      
       A 
      
        left
      
      
        join
      
       (B,C,D) 
      
        on
      
       (B.i1
      
        =
      
      A.i1 
      
        and
      
       C.i2
      
        =
      
      A.i2 
      
        and
      
       D.i3 
      
        =
      
      
         A.i3);#复杂连接


      
      
         66
      
      
         67
      
       #右连接又叫外连接 
      
        right
      
      
        join
      
      
         返回右表中所有记录和左表中连接字段相等的记录


      
      
         68
      
      
        select
      
      
        *
      
      
        from
      
       A 
      
        right
      
      
        join
      
       B 
      
        on
      
       A.id 
      
        =
      
      
         B.id;


      
      
         69
      
      
         70
      
       #完整外部链接 
      
        full
      
      
        join
      
      
         返回左右表中所有数据


      
      
         71
      
      
        select
      
      
        *
      
      
        from
      
       A 
      
        full
      
      
        join
      
       B 
      
        on
      
       A.id 
      
        =
      
      
         B.id;


      
      
         72
      
      
         73
      
      
        #交叉连接 没有where字句 返回卡迪尔积


      
      
         74
      
      
        select
      
      
        *
      
      
        from
      
       A 
      
        cross
      
      
        join
      
      
         B;


      
      
         75
      
      
        --
      
      
        -----------------------表连接结束------------------------------------------------------------
      
      
         76
      
      
        --
      
      
        ---------------索引创建------------------------------------------------
      
      
         77
      
       show 
      
        index
      
      
        from
      
      
         A #查看索引


      
      
         78
      
      
        alter
      
      
        table
      
       A 
      
        add
      
      
        primary
      
      
        key
      
      
        (id) #主键索引


      
      
         79
      
      
        alter
      
      
        table
      
       A 
      
        add
      
      
        unique
      
      
        (name) #唯一索引


      
      
         80
      
      
        alter
      
      
        table
      
       A 
      
        add
      
      
        index
      
      
         name(name) #普通索引


      
      
         81
      
      
        alter
      
      
        table
      
       A 
      
        add
      
      
         fulltext(name) #全文索引


      
      
         82
      
      
        alter
      
      
        table
      
       A 
      
        add
      
      
        index
      
      
         name(id,name) #多列索引


      
      
         83
      
      
         84
      
      
        #常用函数


      
      
         85
      
      
        abs
      
      (
      
        -
      
      
        1
      
      
        )#绝对值


      
      
         86
      
      
        pi
      
      
        ()#pi值


      
      
         87
      
      
        sqrt
      
      (
      
        2
      
      
        )#平方根


      
      
         88
      
       mod(
      
        -
      
      
        5
      
      ,
      
        3
      
      )#取余
      
        -
      
      
        2
      
      
         89
      
       ceil(
      
        10.6
      
      )#进位
      
        +
      
      
        1
      
       结果11 ceil(
      
        10.0
      
      
        )结果10


      
      
         90
      
      
        floor
      
      (
      
        10.6
      
      )#取整 
      
        10
      
      
         91
      
      
        round
      
      (
      
        2.5
      
      
        )#四舍五入到整数 结果3


      
      
         92
      
      
        round
      
      (
      
        2.5
      
      ,
      
        2
      
      )#保留两位小数 结果2.
      
        50
      
      
         93
      
      
        truncate
      
      (
      
        2.5234
      
      ,
      
        3
      
      )#取小数后3位不四舍五入 
      
        2.523
      
      
         94
      
      
        sign
      
      (
      
        -
      
      
        2
      
      );#符号函数 返回
      
        -
      
      
        1
      
      
         0还是0 正数返回1


      
      
         95
      
       pow(
      
        2
      
      ,
      
        3
      
      ),
      
        exp
      
      (
      
        2
      
      
        );#2的3次幂 或e的2次幂


      
      
         96
      
      
        log
      
      (
      
        2
      
      ),
      
        log10
      
      (
      
        2
      
      
        );#求对数


      
      
         97
      
      
        radians
      
      (
      
        180
      
      ),
      
        degrees
      
      (
      
        0.618
      
      
        );#角度弧度转换


      
      
         98
      
      
        sin
      
      (
      
        0.5
      
      ),
      
        asin
      
      (
      
        0.5
      
      )#正弦和反正弦 类似cos 
      
        acos
      
      
        tan
      
      
        atan
      
      
         99
      
       length(
      
        '
      
      
        hi
      
      
        '
      
      
        )#计算字符长度


      
      
        100
      
       concat(
      
        '
      
      
        1
      
      
        '
      
      ,
      
        1
      
      ,
      
        '
      
      
        hi
      
      
        '
      
      
        )#合并字符串


      
      
        101
      
      
        insert
      
      (
      
        '
      
      
        12345
      
      
        '
      
      ,
      
        1
      
      ,
      
        0
      
      ,
      
        '
      
      
        7890
      
      
        '
      
      
        );#从开头第1个字符开始到0个结束,替换成后边字符串,0表示在最前边插入


      
      
        102
      
       ucase(
      
        '
      
      
        a
      
      
        '
      
      ),lcase(
      
        '
      
      
        A
      
      
        '
      
      
        )#转成大写和小写


      
      
        103
      
      
        left
      
      (
      
        '
      
      
        abcd
      
      
        '
      
      ,
      
        2
      
      ),
      
        right
      
      (
      
        '
      
      
        abcd
      
      
        '
      
      ,
      
        2
      
      
        );#返回前两个字符和后两个字符


      
      
        104
      
      
        ltrim
      
      (
      
        '
      
      
          0  
      
      
        '
      
      ),
      
        rtrim
      
      (
      
        '
      
      
         0 
      
      
        '
      
      ),trim(
      
        '
      
      
          0  
      
      
        '
      
      
        )#删除空格


      
      
        105
      
      
        replace
      
      (
      
        '
      
      
        1234567890
      
      
        '
      
      ,
      
        '
      
      
        345678
      
      
        '
      
      ,
      
        '
      
      
        0
      
      
        '
      
      
        );#替换输出12090


      
      
        106
      
      
        substring
      
      (
      
        '
      
      
        12345
      
      
        '
      
      ,
      
        1
      
      ,
      
        2
      
      
        )#取字符 输出12 1是位置 2是长度


      
      
        107
      
       instr(
      
        '
      
      
        1234
      
      
        '
      
      ,
      
        '
      
      
        234
      
      
        '
      
      
        );#取得234位置是2


      
      
        108
      
      
        reverse
      
      (
      
        '
      
      
        1234
      
      
        '
      
      
        );#反序输出4321


      
      
        109
      
      
        current
      
      
        ()#返回日期


      
      
        110
      
      
        curtime()#返回时间


      
      
        111
      
      
        now()#返回日期时间


      
      
        112
      
      
        month
      
      
        (now())#当前月份 monthname 英文月份


      
      
        113
      
      
        dayname(now())#星期英文 dayofweek()1是星期天 weekday()1是星期二


      
      
        114
      
      
        week(now())#本年第多少周


      
      
        115
      
      
        dayofyear(now()),dayofmonth(now())#今天是本年第多少天 今天是本月第多少天


      
      
        116
      
      
        year
      
      (now()),
      
        month
      
      (now()),
      
        day
      
      
        (now()),hour(now()),minute(now()),second(now())#返回年月日 时分秒


      
      
        117
      
       time_to_sec(now()),sec_to_time(
      
        3600
      
      
        *
      
      
        8
      
      
        );#转换时间为秒和还原


      
      
        118
      
      
        version()#mysql版本


      
      
        119
      
      
        database
      
      
        ()#当前连接的数据库 没有为null


      
      
        120
      
      
        user
      
      
        ()#获取用户名


      
      
        121
      
       md5(
      
        '
      
      
        a
      
      
        '
      
      
        )#加密字符串


      
      
        122
      
      
        ascii
      
      (
      
        '
      
      
        a
      
      
        '
      
      
        )#ascii值97


      
      
        123
      
       bin(
      
        100
      
      ),hex(
      
        100
      
      ),oct(
      
        100
      
      
        )#返回二进制 十六进制 八进制


      
      
        124
      
       conv(
      
        10001
      
      ,
      
        2
      
      ,
      
        8
      
      
        );#各种进制相互转换


      
      
        125
      
      
        rand
      
      
        ()#生成0到1之间随机数


      
      
        126
      
       sleep(
      
        0.02
      
      
        )#暂停秒数


      
      
        127
      
      
        128
      
      
        数据库优化


      
      
        129
      
      
        .开启缓存,尽量使用php函数而不是mysql


      
      
        130
      
       . explain 
      
        select
      
      
         语句可以知道性能


      
      
        131
      
       .一行数据使用 limit 
      
        1
      
      
      
        132
      
      
        .为搜索字段重建索引 比如关键字 标签


      
      
        133
      
      
        .表连接join保证字段类型相同并且有其索引


      
      
        134
      
       .随机查询使用php $r 
      
        =
      
       mysql_query("
      
        SELECT
      
      
        count
      
      (
      
        *
      
      ) 
      
        FROM
      
      
        user
      
      
        ");


      
      
        135
      
                           $d 
      
        =
      
      
         mysql_fetch_row($r);


      
      
        136
      
                           $
      
        rand
      
      
        =
      
       mt_rand(
      
        0
      
      ,$d
      
        [
      
      
        0
      
      
        ]
      
      
        -
      
      
        1
      
      
        );


      
      
        137
      
                           $r 
      
        =
      
       mysql_query("
      
        SELECT
      
       username 
      
        FROM
      
      
        user
      
       LIMIT $
      
        rand
      
      , 
      
        1
      
      
        ");


      
      
        138
      
       .避免使用select 
      
        *
      
      
         应该使用具体字段


      
      
        139
      
       .每张表都是用id主键,并且是unsigned 
      
        int
      
      
        140
      
      
        .对于取值有限而固定使用enum类型,如性别 国家 名族 部门 状态


      
      
        141
      
       .尽可能使用not 
      
        null
      
       ip存储使用int(
      
        4
      
      
        ),使用ip 转化函数ip2long()相互long2ip()


      
      
        142
      
      
        .delete和insert语句会锁表,所以可以采用分拆语句操作


      
      
        143
      
      
        while
      
      (
      
        1
      
      ){操作语句;usleep(
      
        2000
      
      
        );}


      
      
        144
      
      
        .选择正确的存储引擎;MyISAM适合大量查询 写操作多用InnoDB支持事务


      
      
        145
      
      
        146
      
      
        #存储过程


      
      
        147
      
      
        #存储程序


      
      
        148
      
      
        delimiter #定义存储程序


      
      
        149
      
      
        create
      
      
        procedure
      
       getversion(out params 
      
        varchar
      
      (
      
        20
      
      
        )) #params是传出参数 in传进 out传出 inout传回


      
      
        150
      
      
        begin
      
      
        151
      
      
        select
      
       version() 
      
        into
      
      
         params; #版本信息赋值params


      
      
        152
      
      
        end
      
      
        153
      
       call getversion(
      
        @a
      
      
        ); #调用存储过程


      
      
        154
      
      
        select
      
      
        @a
      
      
        ;


      
      
        155
      
      
        delimiter #定义存储函数


      
      
        156
      
      
        create
      
      
        function
      
       display(w 
      
        varchar
      
      (
      
        20
      
      )) 
      
        returns
      
      
        varchar
      
      (
      
        20
      
      
        )


      
      
        157
      
      
        begin
      
      
        158
      
      
        return
      
       concat(
      
        '
      
      
        hello
      
      
        '
      
      
        ,w);


      
      
        159
      
      
        end
      
      
        160
      
      
        select
      
       display(
      
        '
      
      
        world
      
      
        '
      
      
        );


      
      
        161
      
      
        162
      
      
        drop
      
      
        procedure
      
      
        if
      
      
        exists
      
      
         spName; #删除一个存储过程


      
      
        163
      
      
        alter
      
      
        function
      
       spName 
      
        []
      
      
        ;#修改一个存储过程


      
      
        164
      
       show 
      
        create
      
      
        procedure
      
      
         spName;#显示存储过程信息


      
      
        165
      
      
        declare
      
       varName type 
      
        default
      
      
         value;#声明局部变量


      
      
        166
      
      
        #if语句


      
      
        167
      
      
        if
      
       条件 
      
        then
      
      
         语句


      
      
        168
      
       elseif 条件 
      
        then
      
      
         语句


      
      
        169
      
      
        else
      
      
         语句


      
      
        170
      
      
        end
      
      
        if
      
      
        171
      
      
        #case语句


      
      
        172
      
      
        case
      
      
         条件


      
      
        173
      
      
        when
      
       条件 
      
        then
      
      
         语句


      
      
        174
      
      
        when
      
       条件 
      
        then
      
      
         语句


      
      
        175
      
      
        else
      
      
         语句


      
      
        176
      
      
        end
      
      
        case
      
      
        177
      
      
        #loop语句


      
      
        178
      
      
        fn:loop


      
      
        179
      
      
        语句


      
      
        180
      
      
        end
      
      
         loop fn;


      
      
        181
      
      
        leave fn #退出循环


      
      
        182
      
      
        #while语句


      
      
        183
      
       fn:
      
        while
      
      
         条件 do


      
      
        184
      
      
        语句


      
      
        185
      
      
        end
      
      
        while
      
      
         fn


      
      
        186
      
      
        187
      
      
        188
      
      
        #mysql使用帮助资料


      
      
        189
      
      
        ? contents; #列出帮助类型


      
      
        190
      
      
        ? data types;#列出数据类型


      
      
        191
      
        int
      
      
        ;#列出具体类型


      
      
        192
      
      
        ? show;#show语句


      
      
        193
      
       ? 
      
        create
      
      
        table
      
      
        ;#


      
      
        194
      
      
        #常见表的比较


      
      
        195
      
      
                            Myisam   BDB    Memory    InnoDB    Archive


      
      
        196
      
      
        存储限制        no           no      yes                64T        no


      
      
        197
      
      
        事物安全                      支持                         支持                         


      
      
        198
      
      
        锁机制         表锁           页锁    表锁             行锁          行锁


      
      
        199
      
      
        全文索引       支持


      
      
        200
      
      
        外键支持                                                        支持


      
      
        201
      
      
        myisam  frm存储表定义 MYD存储数据 MYI存储索引


      
      
        202
      
      
        InnoDB 用于事务处理


      
      
        203
      
      
        char
      
      
         和 varchar保存和索引都不相同


      
      
        204
      
       浮点数float(
      
        10
      
      ,
      
        2
      
      ) 定点数decimal(
      
        10
      
      ,
      
        2
      
      
        )


      
      
        205
      
      
        长度一定下,浮点数表示更大数据范围,缺点是引起精度丢失,货币等使用定点数存储


      
      
        206
      
      
                索引适合于where字句或者连接字句列


      
      
        207
      
      
                对于唯一值使用唯一索引


      
      
        208
      
      
        209
      
       添加新用户 
      
        grant
      
      
        select
      
      ,
      
        insert
      
      ,
      
        update
      
      ,
      
        delete
      
      
        on
      
      
        *
      
      .
      
        *
      
      
        to
      
       Yoby
      
        @localhost
      
       identified 
      
        by
      
      
        '
      
      
        mysql
      
      
        '
      
      
        ; 


      
      
        210
      
       #           
      
        *
      
      .
      
        *
      
       数据库名.表名,限制登录某一个数据库 test.
      
        *
      
                                 localhost是本地主机 网络可以使用 
      
        '
      
      
        %
      
      
        '
      
      代替所有主机        
      
        '
      
      
        mysql
      
      
        '
      
      
        是密码 Yoby是用户名  所有权限可以用 all代替


      
      
        211
      
       查看用户权限 show grants 
      
        for
      
      
        '
      
      
        root
      
      
        '
      
      @
      
        '
      
      
        localhost
      
      
        '
      
      
        ;


      
      
        212
      
       移除权限  
      
        revoke
      
      
        all
      
      
        on
      
      
        *
      
      .
      
        *
      
      
        from
      
       root
      
        @localhost
      
      
        ;


      
      
        213
      
      
        group
      
      
        by
      
      
         id 分组


      
      
        214
      
      
        having
      
      
         限制字句


      
      
        215
      
       select1 
      
        union
      
      
         select2 联合查询有重复去掉保留一行


      
      
        216
      
       select2 
      
        union
      
      
        all
      
       select2 所有行合并到结果集中去
    

 

#登录数据库
mysql -hlocalhost -uroot -p;
#修改密码
mysqladmin -uroot -pold password new;


#显示数据库
show databases;
#显示数据表
show tables;
#选择数据库
use examples;
#创建数据库并设置编码utf-8 多语言
create database `examples` default character set utf8 collate utf8_general_ci;
#删除数据库
drop database examples;
#创建表
create table test(
    id int(10) unsigned zerofill not null auto_increment,
    email varchar(40) not null,
    ip varchar(15) not null,
    state int(10) not null default '-1',
    primary key (id)
)engine=InnoDB;
#显示表结构
describe
#删除表
drop table test;
#重命名表
alter table test_old rename test_new;
#添加列
alter table test add cn int(4) not null;
#修改列
alter table test change id id1 varchar(10) not null;
#删除列
alter table test drop cn;
#创建索引
alter table test add index (cn,id);
#删除索引
alter table test drop index cn
#插入数据
insert into test (id,email,ip,state) values(2,'qq@qq.com','127.0.0.1','0');
#删除数据
delete from test where id = 1;
#修改数据
update test set id='1',email='q@qq.com' where id=1;
#查数据
select * from test;  #取所有数据
select * from test limit 0,2;  #取前两条数据
select * from test email like '%qq%' #查含有qq字符 _表示一个 %表示多个
select * from test order by id asc;#降序desc
select * from test id not in('2','3');#id不含2,3或者去掉not表示含有
select * from test timer between 1 and 10;#数据在1,10之间

#---------------------------表连接知识------------------------------
#等值连接又叫内链接 inner join 只返回两个表中连接字段相等的行
select * from A inner join B on A.id = B.id; #写法1
select * from A,B where A.id = B.id; #写法2
select a.id,a.title from A a inner join B b on a.id=b.id and a.id=1;#写法3 表的临时名称
select a.id as ID,a.title as 标题 from A inner join B on A.id=B.id;#添加as字句

#左连接又叫外连接 left join 返回左表中所有记录和右表中连接字段相等的记录
select * from A left join B on A.id = B.id;

select * from A left join (B,C,D) on (B.i1=A.i1 and C.i2=A.i2 and D.i3 = A.i3);#复杂连接

#右连接又叫外连接 right join 返回右表中所有记录和左表中连接字段相等的记录
select * from A right join B on A.id = B.id;

#完整外部链接 full join 返回左右表中所有数据
select * from A full join B on A.id = B.id;

#交叉连接 没有where字句 返回卡迪尔积
select * from A cross join B;
-------------------------表连接结束------------------------------------------------------------
-----------------索引创建------------------------------------------------
show index from A #查看索引
alter table A add primary key(id) #主键索引
alter table A add unique(name) #唯一索引
alter table A add index name(name) #普通索引
alter table A add fulltext(name) #全文索引
alter table A add index name(id,name) #多列索引

#常用函数
abs(-1)#绝对值
pi()#pi值
sqrt(2)#平方根
mod(-5,3)#取余-2
ceil(10.6)#进位+1 结果11 ceil(10.0)结果10
floor(10.6)#取整 10
round(2.5)#四舍五入到整数 结果3
round(2.5,2)#保留两位小数 结果2.50
truncate(2.5234,3)#取小数后3位不四舍五入 2.523
sign(-2);#符号函数 返回-1 0还是0 正数返回1
pow(2,3),exp(2);#2的3次幂 或e的2次幂
log(2),log10(2);#求对数
radians(180),degrees(0.618);#角度弧度转换
sin(0.5),asin(0.5)#正弦和反正弦 类似cos acos tan atan
length('hi')#计算字符长度
concat('1',1,'hi')#合并字符串
insert('12345',1,0,'7890');#从开头第1个字符开始到0个结束,替换成后边字符串,0表示在最前边插入
ucase('a'),lcase('A')#转成大写和小写
left('abcd',2),right('abcd',2);#返回前两个字符和后两个字符
ltrim('  0  '),rtrim(' 0 '),trim('  0  ')#删除空格
replace('1234567890','345678','0');#替换输出12090
substring('12345',1,2)#取字符 输出12 1是位置 2是长度
instr('1234','234');#取得234位置是2
reverse('1234');#反序输出4321
current()#返回日期
curtime()#返回时间
now()#返回日期时间
month(now())#当前月份 monthname 英文月份
dayname(now())#星期英文 dayofweek()1是星期天 weekday()1是星期二
week(now())#本年第多少周
dayofyear(now()),dayofmonth(now())#今天是本年第多少天 今天是本月第多少天
year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now())#返回年月日 时分秒
time_to_sec(now()),sec_to_time(3600*8);#转换时间为秒和还原
version()#mysql版本
database()#当前连接的数据库 没有为null
user()#获取用户名
md5('a')#加密字符串
ascii('a')#ascii值97
bin(100),hex(100),oct(100)#返回二进制 十六进制 八进制
conv(10001,2,8);#各种进制相互转换
rand()#生成0到1之间随机数
sleep(0.02)#暂停秒数

数据库优化
.开启缓存,尽量使用php函数而不是mysql
. explain select 语句可以知道性能
.一行数据使用 limit 1;
.为搜索字段重建索引 比如关键字 标签
.表连接join保证字段类型相同并且有其索引
.随机查询使用php $r = mysql_query("SELECT count(*) FROM user");
                    $d = mysql_fetch_row($r);
                    $rand = mt_rand(0,$d[0] - 1);
                    $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");
.避免使用select * 应该使用具体字段
.每张表都是用id主键,并且是unsigned int
.对于取值有限而固定使用enum类型,如性别 国家 名族 部门 状态
.尽可能使用not null ip存储使用int(4),使用ip 转化函数ip2long()相互long2ip()
.delete和insert语句会锁表,所以可以采用分拆语句操作
    while(1){操作语句;usleep(2000);}
.选择正确的存储引擎;MyISAM适合大量查询 写操作多用InnoDB支持事务

#存储过程
#存储程序
delimiter #定义存储程序
create procedure getversion(out params varchar(20)) #params是传出参数 in传进 out传出 inout传回
begin
select version() into params; #版本信息赋值params
end
call getversion(@a); #调用存储过程
select @a;
delimiter #定义存储函数
create function display(w varchar(20)) returns varchar(20)
begin
return concat('hello',w);
end
select display('world');

drop procedure if exists spName; #删除一个存储过程
alter function spName [];#修改一个存储过程
show create procedure spName;#显示存储过程信息
declare varName type default value;#声明局部变量
#if语句
if 条件 then 语句
elseif 条件 then 语句
else 语句
end if
#case语句
case 条件
when 条件 then 语句
when 条件 then 语句
else 语句
end case
#loop语句
fn:loop
语句
end loop fn;
leave fn #退出循环
#while语句
fn:while 条件 do
语句
end while fn


#mysql使用帮助资料
? contents; #列出帮助类型
? data types;#列出数据类型
? int;#列出具体类型
? show;#show语句
? create table;#
#常见表的比较
                    Myisam   BDB    Memory    InnoDB    Archive
存储限制        no           no      yes                64T        no
事物安全                      支持                         支持                         
锁机制         表锁           页锁    表锁             行锁          行锁
全文索引       支持
外键支持                                                        支持
myisam  frm存储表定义 MYD存储数据 MYI存储索引
InnoDB 用于事务处理
char 和 varchar保存和索引都不相同
浮点数float(10,2) 定点数decimal(10,2)
长度一定下,浮点数表示更大数据范围,缺点是引起精度丢失,货币等使用定点数存储
        索引适合于where字句或者连接字句列
        对于唯一值使用唯一索引

添加新用户 grant select,insert,update,delete on *.* to Yoby@localhost identified by 'mysql';
#           *.* 数据库名.表名,限制登录某一个数据库 test.*                           localhost是本地主机 网络可以使用 '%'代替所有主机        'mysql'是密码 Yoby是用户名  所有权限可以用 all代替
查看用户权限 show grants for 'root'@'localhost';
移除权限  revoke all on *.* from root@localhost;
group by id 分组
having 限制字句
select1 union select2 联合查询有重复去掉保留一行
select2 union all select2 所有行合并到结果集中去

MYSQL总结


更多文章、技术交流、商务合作、联系博主

微信扫码或搜索:z360901061

微信扫一扫加我为好友

QQ号联系: 360901061

您的支持是博主写作最大的动力,如果您喜欢我的文章,感觉我的文章对您有帮助,请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧,狠狠点击下面给点支持吧,站长非常感激您!手机微信长按不能支付解决办法:请将微信支付二维码保存到相册,切换到微信,然后点击微信右上角扫一扫功能,选择支付二维码完成支付。

【本文对您有帮助就好】

您的支持是博主写作最大的动力,如果您喜欢我的文章,感觉我的文章对您有帮助,请用微信扫描上面二维码支持博主2元、5元、10元、自定义金额等您想捐的金额吧,站长会非常 感谢您的哦!!!

发表我的评论
最新评论 总共0条评论