1. 建表SQL ``sql CREATE TABLE IF NOT EXISTSarticle(idINT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,author_idINT(10)UNSIGNED NOT NULL,category_idINT(10) UNSIGNED NOT NULL,viewsINT(10) UNSIGNED NOT NULL,commentsINT(10) UNSIGNED NOT NULL,titleVARBINARY(255) NOT NULL,content` TEXT NOT NULL );

    INSERT studey_mysql.article (author_id, category_id, views, comments , title , content ) VALUES (1,1,1,1,’1’,’1’), (2,2,2,2,’2’,’2’), (1,1,3,3,’3’,’3’);

    2. 2. 检索 , 创建索引
    3. ```sql
    4. # 查询 category_id 为1 且 comments 大于1 的情况下 , views最多的 article_id
    5. explain
    6. select * from article
    7. where category_id=1 and comments >1
    8. order by views desc
    9. limit 0,1
    11. #创建索引( 测试之后 不合适 )
    12. create index idx_article_ccv on article(category_id,comments,views);
    14. #查看索引
    15. show index from article
    17. #删除不合适的索引
    18. drop index idx_article_ccv on article;
    20. #重新创建
    21. create index idx_article_cv on article(category_id,views);
    1. 知识点 ```

    2. where后面的就需要添加索引

    3. 如果字段是范围查询就不用加索引 ; 例如 < , > ```