sql表操作的基础语法

总结:1.插入数据:insert into b(a,l) values(1,"2");insert into b values();
2、修改数据:update b set name='',ir= where id= ;
3、删除数据:delete from b where id= ;truncate table b; delete from b;
4、查询数据:select * from b;
5.查询指定字段，多项目查询:select pdate,pname from b where id=1;
6.查询时增加常量:select id as '',pname as '' where id=1;
7.查询去重:select distinct sex from b ;
8.查询时计算:select pname,(a+s+f) from b where id= ;
9.条件查询:selelct name from b where product='新的';
10.< > <> <= >=的使用:select js from socre where grade>50 or grade<80 or grade>=65
or grade<=75 or grade <> 66;
11.between and 的用法:select * from f where grade= between 60 and 70;
12.null和空字符串的用法:null是没有值具体用法:is null或者is not null;''空字符串是有
值但是值是空的，没有被实参赋值是空字符串。
12.%和_的用法:%表示一个或多个任意字符，_表示一个任意字符;一般和like模糊查询一起使用
查询姓张的人:select * from teacher where name like '张%';
查询姓张名字是三个字的人:select * from teacher where name like '张__';
13.SUM/AVG/MAX/MIN/COUNT()的用法:
求和：select sum(js) from scroe;求js成绩的总和
求js成绩的平均数；select avg(js) from scroe;
求css的最高成绩:select max(css) from scroe;
求css的最低成绩:select min(css) from scroe;
求学js的人数:select js,count(*) from scroe group by js;
14.ASC DESC的用法；ASC是正序从小到大；DESC是倒叙从大到小；
css成绩从小到大进行排序；selelct * from scroe group by css asc;
js成绩从大到小进行排序:select * from scroe group by js desc;
group by 是分组计数;order by 是进行排序用的
SELECT * FROM score ORDER BY js ASC,jquery DESC;-- 是以js为主排序，当js的值相同的是
时候再按照jquery的成绩排；
15.有关count()方法的使用
查询每个班的人数:select clas,count(*)from student group by clas;
16.查询每个班js的总分:select clas,count(js) from student group by clas;

 

转载于:https://www.cnblogs.com/hankai2735/p/11189713.html