DML语句

• DML语句
• //插入
• insert into articles (`userid`,`title`,`category_id`)values('12','测试','22');
• //更新
• update articles set `title`='测试两下' where `category_id`='22';
• update articles set `title`='测试两下';//将影响所有行
• update articles set `title`='实验三下' order by id desc limit 5;
• //删除
• delete from articles where `id`='1039';
• //id倒序删除5行
• delete from articles order by id desc limit 5;
• DQL语句
• //查询所有
• select * from articles;
• //查询指定列
• select `id`,`cid`,`title` from articles;
• //使用AS别名
• select `title` as '标题',`id`,`views` as '浏览量' from articles;
• //distinct 关键字去除重复
• select distinct title as "标题" from articles;
• //查询非空
• select * from articles where remark is not null;
• //范围查询
• select * from articles where views between 30 and 40;
• select * from articles where views not between 30 and 40;
• //范围比较查询
• select * from articles where views = '61' or views='89' or views='15';
• //使用IN进行范围比较查询
• select * from articles where views in('61','89','15');
• select * from articles where views not in('61','89','15');
• 使用LIKE进行模糊查询
• % 表示0个或多个
• _ 表示任意单个
• select * from articles where title like '%php%';
• select * from articles where title not like '%php%';
• select * from articles where title like '_ss自动序列';
• 多表查询
• select * from article,categorie ;
• select * from article,categorie where article.category_id = categorie.id;
• 自身连接查询
  连接查询不仅可以用于多张表之间的连接也可以用于一张表与其自身的连接,方法,为它起两个别名
• select cs1.name '父类别名称' , cs2.name '子类别名称' from categorie cs1,categorie cs2 where cs1.id=cs2.pid;
• 

---

• 嵌套查询(子查询)
• in()里面的返回必须是返回单行,不能是集合,可以用”=”,”>”,”<”,”!=”,或用LIMIT来限制返回单行
• select * from article where category_id in(select id from categorie where id=1);
• 
• LIMIT限定结果行数()
• select * from article where category_id in(select id from categorie) LIMIT 4,8;//返回4到8条
• 统计函数
  count();
  avg();
  sum();
  max();
  min();
• select max(views) as "浏览最多" from article;
• EXPLAIN语句检测索引和查询能否良好匹配的简便方法’
• explain select * from article;
• +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
• | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
• +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
• | 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 38 | 100.00 | NULL |
• +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
• 1 row in set, 1 warning (0.00 sec)