添加、修改、读或者查询、删除

CURD(添加、修改、读或者查询、删除)

向表中添加数据insert into:
语法一：insert into 表名 values(值1,值2,值n)----值列表的个数与字段列表个数相匹配，且顺序相对应
       insert into 表名 values(值1,值2,值n),(值1,值2,值n)---一次性添加多条记录
eg:
insert into cms_function values(0,'empty','判断变量的值是否为空','empty($var)','当值为"0",0,0.0,"",array(),null,false结果为true');  //自动递增的列值可以写0或者null
insert into cms_function values(0,'unset','销毁变量','unset($var)',''),(0,'pathinfo','获取路径中的各个部分信息','pathinfo($path,PATHINFO_EXTENTION|PATHINFO_DIRNAME)','此函数返回的是关联数组或者字符串');

语法二：insert into 表名(字段列表) values(值列表)
       insert into 表名(字段列表) values(值列表1),(值列表n)---添加多条记录
eg:
insert into cms_function(name,function,htuse) values('is_array','判断变量是否为数组','is_array($var)');

语法三:insert into 表名 set 字段名1=值1,字段名n=值n ---一次只能添加一条记录
    insert into cms_function set name='count',function='获取数组长度',htuse='count($array)',remark='count($array,1)递归统计元素个数,判断是否为多维数组count($array)==count($array,1)'

查询数据select：
select 字段列表 from 表名 [where 条件]  查询满足条件的相关数据---当没有写where条件时查询所有
select name from cms_function-----  查询所有函数名
select name as func_name from cms_function----查询所有函数名，并且为name取别名 :字段名 [as] 别名
select name  func_name from cms_function----查询所有函数名，并且为name取别名
select name,htuse from cms_function
select *  from cms_function---查看所有函数所有信息

where条件:
条件运算符：>,>=,<=,=(相等，注意不是两个等号),!=   字段名 条件运算符 值
select * from cms_function where name = 'isset';
select id,name,function from cms_function where id=6;
select id,name,function from cms_function where id<3;
select id,name,function from cms_function where id<=3;
select id,name,function from cms_function where id>3;
select id,name,function from cms_function where id>=3;
select id,name,function from cms_function where id!=3;

逻辑运算符:and(&&),or(||),not(!)
select *  from cms_function where id>=3 and id<=5;
select *  from cms_function where id between 3 and 5;  //字段名 between minNum and maxNum  包含了边界
select *  from cms_function where id<3 or id>5;
select *  from cms_function where id not between 3 and 5; //字段名 not between minNum and maxNum  不包含边界
select name,htuse from cms_function where name='empty' or name='unset' or name='is_array';
select name,htuse from cms_function where name in('empty','unset','is_array');  //字段名 in(值列表) 字段值在值列表之中的
select * from cms_function where name!='empty' and name!='unset' and name!='is_array';
select * from cms_function where name not in('empty','unset','is_array'); //字段名 not in(值列表) 字段值在值列表之中的
select *  from cms_function where 0; //查询到0条记录 在mysql中数字0视为false,非0视为true
select *  from cms_function where 1; //查询到所有记录

模糊匹配:字段名 like 表达式   (%代表的是0个或者n个字符,_代表的是任意一个字符)
select * from cms_function where name like '%\_%';  查看函数名包含有下划线的函数所有信息,匹配_本身用\_
select * from cms_function where name like 'is%';
select * from cms_function where name like '%t';
模糊匹配:字段名 not like 表达式   (%代表的是0个或者n个字符,_代表的是任意一个字符)
select * from cms_function where name not like '%\_%';


将筛选好的行再进行限定行数 limit start,length  比如数据分页用此关键词 start=(page-1)*length
select * from cms_function limit 3;  //从第一行显示，显示3行
select * from cms_function limit 0,3;  //0是从第一行开始

将筛选好的行再进行排序:order by 字段名 [asc|desc][,字段名2 asc|desc]  asc是升序，默认的，desc降序
select * from cms_function order by name;
select * from cms_function order by name desc;
select * from cms_function order by status desc,id asc;  //多字段排序

查询所有显示【status=1】的函数按函数名排序只显示前3条记录
select * from cms_function where status=1 order by name limit 3;  --->where .... order by ... limit...

查询显示以及不显示的函数个数

count(字段名|1|*)---统计行数

select  count(1) as total from cms_function;  //统计函数个数
select  count(1) as total from cms_function where status=1;
select  count(1) as total from cms_function where status=-1;

group by 字段名---将记录按指定的字段分类

select status from  cms_function group by status;
select status, count(id) total from cms_function group by status;

分组基础上筛选用having 表达式---写在group by之后
select status, count(id) total from cms_function group by status having total>4;

查询含有20张以上的相片的相册名---photo相片表(id,xc_id相册id,url,title,created,summary) xc相册表(id,name,created)
select name from xc where id in((select xc_id total from photo group by xc_id having total>20)) ----子查询

删除数据:delete from 表名 [where 条件]  删除满足条件的记录，注意当没有写where条件时删除所有行
delete from cms_function where name like '%\_%';
delete from cms_function limit 2;
delete from cms_function where id between 3 and 5;
delete from cms_function;
truncate table cms_function;
truncate table 表名; //清除所有记录，id值从1开始录入,用于删除测试数据

更新记录:update 表名 set 字段名1=值1,字段名2=值2,字段名n=值n [where 条件] 更新满足条件的记录,当不写条件时，更新所有行

update cms_function set name = 'isFile' where name='is_file';
update cms_function set name = upper(name);