#查询#查询全部select * from stu;
#查询某些字段select name,gender,height from stu;
#查询带多个条件select * from stu where gender='女' and class_id=1;#查询>select * from stu where id>4;
select * from stu where class_id!=1and gender='女';select * from stu where gender='女' or class_id!=1;select gender from stu;
#去重复distinctselectdistinct gender,class_id from stu ;
#asc 升序 desc降序select * from stu orderby class_id desc,id desc;
#模糊匹配 likeselect * from stu where name like'张%龙';select * from stu where name like'张%';select * from stu where name like'%虎';select * from stu where name like'%一%';#between...and... 区间选择select * from stu where birthday between '1930-01-01' and '2000-01-01';# in 指定几个选项select * from stu where id in(1,2,5,6);
ount统计条数
select count(*) from stu where class_id=1and gender='男' and birthday > '2000-01-01';##############插入
insert into stu values(
4,"张三龙",1,"女","2010-06-06 12:00:00",120.00
);
select * from stu;
# 非必填、自增、含有默认值
insert into stu(name,class_id,gender,birthday,height)
values("张四龙",1,"男","1990-07-08 12:00:00",189.03);
#删除
delete from stu where name="张二龙" ;
select * from stu;
#更新操作
update stu
set birthday="1990-01-01 12:12:12",height=150.66where height>999and class_id=1;
update stu
set height=height+1;
select * from stu;# is null /is not nullselect * from stu where gender isnot null;
select * from stu where hometomw is null;
复杂查询
limit 起始位置,长度 limit 3,7
group by 和聚合函数(sum/avg/max/min/count)
having 用在group by之后的选择
union & union all 有all不去重,没有all去重
四种连接
inner join : select * from a [inner] join b on a.id = b.aid (select * from a ,b where a.id=b.aid)
left join : select * from a left join b on a.id = b.aid 保留左表中有但是右表没有的记录
right join : select * from a right join b on a.id = b.aid 保留右表中有但是左表没有的记录
full join : select * from a full join b on a.id=b.aid 保留左表和右表的所有记录
use internet_bar_management_system;
#求和
selectsum(roc_price) from records_of_consumption;
#求平均值
selectavg(roc_another_price) from records_of_consumption;
#求最大值
selectmax(roc_price) from records_of_consumption;
#求最小值
selectmin(roc_another_price) from records_of_consumption;
#求统计值
selectcount(*) from records_of_consumption where roc_price>100.00;
#limit 限制返回的指定的行数
select *from records_of_consumption orderby roc_number desc limit 0,3;select *from records_of_consumption orderby roc_number desc limit 3,3;select *from records_of_consumption orderby roc_number desc limit 6,3;
use testdb;
# max sum min avg count
select cno,max(degree) from score groupby cno;
#男 女各有多少人
select ssex,count(*) from student groupby ssex;
#求每个人班有多少人
select class,count(*) from student groupby class;
#求每科的平均成绩
select cno,avg(degree) from score groupby cno;
#求不止一个男生的班级
select class from student where ssex='男'groupby class havingcount(*)>1 ;
#查询最低分大于70,最高分小于90的Sno列
select sno from score groupby sno havingmax(degree)<90andmin(degree)>70;
#查询每门课都大于80的Sno列
select sno ,min(degree),max(degree) from score groupby sno havingmin(degree)>80;
#union去重、 union all全部显示
select name ,country from websites
unionallselect app_name,country from apps;
#join 连表查询
use websites;
select w.name,w.url,a.date,a.countfrom access_log a ,websites w where a.site_id=w.id;
use testdb;
select t.SNAME,c.CNAME,s.DEGREE from course c,score s,student t where c.CNO=s.CNO and s.sno=t.sno;
#1.李诚 所教授的课程名称、编号(第一种)
select t.tname ,c.cname,c.cno from teacher t,course c where t.TNO=c.TNO and t.tname='李诚';
#2.名字为淘宝的网站的总访问量
select w.name ,sum(a.`count`) from websites w,access_log a where w.id=a.site_id and w.name='淘宝';
#1.李诚 所教授的课程名称、编号(第二种)
#inner join与join 是一样的
select t.tname,c.cname,c.cno from teacher t join course c on t.tno=c.tno where t.tname='李诚';
#left join
select *from websites w leftjoin access_log a on w.id=a.site_id;
#right join
select*from access_log a rightjoin websites w on w.id=a.site_id;
#子查询
#成绩大于80小于90的学生名字
select s.SNAME from student s where s.sno in(select sno from score c where c.DEGREE>80and c.DEGREE<90);
DML: data manpulation languageinsert 、delete、update、select、truncate#查询#查询全部select * from stu;#查询某些字段select name,gender,height from stu;#查询带多个条件select * from stu where gender='女' and class_id=1;#