常用sql

多表联查

select g.number,p.name,p.nowCost,u.realname,u.phone from wb_barter_goods as g 
left join wb_barter_users as u on g.usersId = u.id 
left join wb_barter_product as p on p.id = g.productId   
where u.id = 1

 ------------------

| 字段1 | 字段2 |
------------------
| 值1 | 值2 |
------------------

在结果集中增加一个字段,这个字段是表中没有的,值是固定的,例如:(我就知道是有这个语句的嘛)
---------------------------
| 字段1 | 字段2 | 新增字段 |
----------------------------
| 值1 | 值2 | 值固定 |
-------------------------------

$sql = "select id,nickname as nick_name,headimgurl as avatar,1 as sex,$scene_id as scene,logintime as dt_join from wp_microsite_member limit 0,1";

##2015-07-25 找出最后一次插入记录的id
  

   insert into …… //
    //紧跟insert 语句之后
    select last_insert_id();

##2015-07-15  

  //转换为日期
    FROM_UNIXTIME( addtime, '%Y-%m-%d %h:%m:%s' )

    group_concat
    //去重复字段
    SELECT id,uid,content,GROUP_CONCAT(DISTINCT uid) FROM `wp_magicwall` GROUP BY uid ORDER BY addtime  

##2015-07-14

    find_in_set()
    //假如字符串str在由N子链组成的字符串strlist,则返回值范围在1-N之间,str不在strlist,则返回值为0
    fing_in_set(str,strlist)
    //可返回多条记录

##函数

##coalesce()
    colesce() // 返回传入的参数中的一个非null的值
    
##case when
    //case when 用于计算条件列表并返回多个可能结果表达式之一
    case 
    when boolean_express then result_express
    ……
    else else_result_express
    end

    //
    case input_express
     when when_express then result_express
     ……
     else else_result_express
    end
###例子
    select 'price' = 
        case
            when price is null then 'have no price'
            when price < 10 then 'cheap'
            when price >= 10 then 'middle'
            else 'expensive'
        end,
        case (title as varchar(20)) as 'short title'
    from titles
    order by price

###例子2
    select 
    case
            when price is null then 'have no price'
            when price < 10 then 'cheap'
            when price >= 10 then 'middle'
            else 'expensive'
        end,
        case (title as varchar(20)) as 'short title'
    from titles
    group by
        case
            when price is null then 'unpriced'
            when price < 10 then 'bargain'
            when price between 10 and 20 then 'average'
            else 'gift to impress relatives'
        end,
        title
    order by 
        case
            when price is null then 'unpriced'
            when price < 10 then 'bargain'
            when price between 10 and 20 then 'average'
            else 'gift to impress relatives'
        end,
        title

  


#2015-06-29
    

 //更改数据库编码
    alter database `newbwifi` default character set utf8 collate utf8_bin;
    //查看数据库编码
    show variables like 'character%';

    //
    SET character_set_database = utf8

  


#2015-05-18

     

NOW() 2015-05-18 15:41:12
    CURDATE() 2015-05-18
    CURTIME() 15:41:12

    create table shop (
    id int not null,
    name varchar(50) not null,
    addDate datetime not null default CURDATE(),
    primary key (id)
    )

    //DATE_ADD()函数向日期添加指定的时间间隔
    DATE_ADD(date,INTERVAL     expr type)
    //date 是合法日期表达式,expr 是添加的时间间隔
    //type MICROSECOND,SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,YEAR
    
    select id, DATE_ADD(addDate,INTERVAL 2 DAY) as showDate from shop

    CONCATE(str1,str2,……)
    //返回结果为连接参数产生的字符串,如果有任何一个参数为null,则返回值为null

  


#2015-05-14

##导出sql  

     mysqldump -u root -p > route_online.sql; 

DATE_ADD()函数向日期添加指定的时间间隔    

    DATE_ADD();
    DARE_ADD(date,INTERVAL expr type);
#2015-05-13
清空表 
    
    truncate table tablename;
    source dir/weixin.sql;

# 2015-04-27
去重  

    select distinct pid from wifi_shop ORDER BY pid

  

# 2015-04-03

##mysql-导出文件
    select * from routemap where (last_heartbeat_time is NULL) into OUTFILE 'c:/test.xls' CHARACTER SET gbk 
    
    insert into a(pic) select ad_thumb from wifi_ad

     ---

  


# 2015-03-08

## 触发器
mysql触发器(trigger),监视某种情况,并触发某种操作  
1. 监视地点(table)  
2. 监视事件(insert/update/delete)  
3. 触发时间(after/before)
4. 触发事件(insert/update/delete)

语法
    
    #商品表
    create table g
    ( id int primary key auto_increment,
       name varchar(20),
      num int)
    #订单表
    create table o
    ( oid int primary key auto_increment,
       gid int,
       much int);
    insert into g(name,num) values('商品1',10),('商品2',10),('商品3',10);

    delimiter $ #先执行此句,告诉mysql语句的结尾换成$
    create trigger triggerName
    after/before insert/update/delete on 表名
    for each row # 这句话是mysql中固定的
    begin
    update g set num=num-3 where id=1; # sql语句;
    end $
    insert into o(gid,much) values(1,3)$

---

  

## 表存储引擎

InnoDB与MyIsam的区别  
- InnoDB存储为一个文件  
- MyISAM在磁盘上存储成三个文件,.frm文件存储表定义,数据:.MYD,索引文件:.MYI  
- MyISAM类型的表强调的是性能,执行速度比InnoDB快,但是不提供外键和事物  
- 执行select,MyISAM更快  
- MyISAM是表锁,InnoDB是行锁

  


## 事务处理

1. 关闭自动提交模式,自动提交模式被关闭的同时,通过PDO对象实例对数据库做出的更改直到调用PDO::commit()结束事物才被提交。若失败了,则调用PDO::rollBack()将回滚对数据库做出的更改并将数据库连接返回到自动提交模式。
2. 返回值 Boolean

        <?php
        $dbh -> beginTransaction ();/*开始一个事务,关闭自动提交*/
        /*更改数据库架构及数据*/
        $sth = $dbh -> exec ("DROP TABLE fruit");
        $sth = $dbh -> exec ("UPDATE admin set name = 'yang'");
        /*识别出错误并回滚更改*/
        $dbh -> rollBack();
        /*数据库连接现在返回到自动提交模式*/
        ?>

---

## 备份恢复
- mysql/bin/mysqldump.exe  
- 执行脚本,备份数据库  

        mysqldump.exe -uroot -proot test >a.sql


---

## 缓存
    redis  memcache apc 
---

## 静态化
当Browser请求Server时,进行数据交互,S解析到缓冲池上,将数据静态化存入文件,将文件返回给B

    <?php
    ob_start();//打开缓冲区,当缓冲区激活时,所有来自PHP程序的非文件头信息均不会发送,而
    是保存在内部缓冲区,为了输出缓冲区的内容,可以使用flush()或flush()输出缓冲区内容。

    echo "this is yangling";
    //将str存入缓冲区,不会直接输出到浏览器,直到使用flush或者ob_end_flush()才会输出到浏览器

    header("location:index.php");//输出不放入缓冲去

    ob_end_flush();

    flush();//刷新缓冲区的内容,使用频率高

    ob_get_contents();//返回内部缓冲区的内容,如果输出缓冲区没有激活,则返回false

    ob_get_length();//返回内部缓冲区的长度

    ob_end_flush();//发送内部缓冲区的内容到浏览器,并且关闭输出缓冲区

    ob_end_clean();//删除内部缓冲区的内容,并且关闭内部缓冲区

    ob_implicit_flush();//打开或关闭绝对刷新

    ob_get_clean(); //得到当前缓冲区的内容并删除当前输出缓冲区

    

---

## mysql

- mysql服务开启了查询缓存,可以提高性能

        [mysqld]
        port=3306
        basedir="D:/phpStudy/MySQL/"
        datadir="D:/phpStudy/MySQL/data/"
        character-set-server=utf8
        default-storage-engine=MyISAM 
        #innodb
        sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
        max_connections=512
        
        #SQL慢查询
        log-slow-queries = D:\logs\mysqlslowquery.log
        long_query_time = 2
        #(其他参数如上)
        
        #查询缓存大小
        query_cache_size=0
        table_cache=256
        tmp_table_size=18M

- **慢查询**,配置已开启

        #navicat->工具->命令界面
        show variables like 'slow%' #环境变量
        show variables like 'char%'
        select sleep(3);

- 索引的分类 主键索引(不为空),普通索引,唯一索引(可以为空),(全文索引,)
- 索引不一定是主键或者唯一字段,但是在常用的字段上要加索引;
- 索引的两种算法(betree & hash)
- 检测有没有索引语句

        show create table test \G;
        show create database test \G;
- 索引的增删改查

        alter table test add index index_name (column_list);
        #table_name = test
        alter table test add unique (colunm_list);
        alter table test add primary key(column_list);
        
        create index index_name on test (column_list);
        create unique index index_name on test(column_list);
        
        drop index index_name on test;
        alter table test drop index index_name;
        alter table test drop primary key;
        
        show index from test;
        show key from test;

- 查看表结构

        explain select * from test \G;
- 不使用 `order by rand()`;
- 字段使用 `not null`;
- 使用`char` 不使用`varchar`;
- 垂直分割(将数据建立外键,分出表),水平分割(如qq表);
- 不要用不是索引的字段来排序;
- `like`不要使用`'%中%'`,使用`'中%'`;
- 不要使用`not in` 或者`<>`
- `order by `

        #如只需分组,不需排序时,需之名order by null        
        explain select title from test group by id \G;
        #extra : using filesort;
        explain select title from test group by id order by null \G;
        #extra : ;

  

 

posted on 2015-08-06 20:24  y_php  阅读(338)  评论(0编辑  收藏  举报