DML

DML: data manpulation language

insert 、delete、update、select、truncate

#查询
#查询全部
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!=1 and gender='女';

select * from stu where gender='女'  or class_id!=1;

select gender from stu;

#去重复distinct
select distinct gender,class_id from stu ;

#asc 升序 desc降序
select * from stu order by class_id desc,id desc;

#模糊匹配 like
select * 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=1 and 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.66
where height>999 and class_id=1;

update stu 
set height=height+1;

select * from stu;# is null /is not null
select * from stu where gender is not null;
select * from stu where hometomw is null;

复杂查询

1. limit 起始位置,长度 limit 3,7
2. group by 和聚合函数（sum/avg/max/min/count）
3. having 用在group by之后的选择
4. union & union all 有all不去重，没有all去重
5. 四种连接
   
   • 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;
#求和
select sum(roc_price) from records_of_consumption;

#求平均值
select avg(roc_another_price) from records_of_consumption;

#求最大值
select max(roc_price) from records_of_consumption;

#求最小值
select min(roc_another_price) from records_of_consumption;

#求统计值
select count(*) from records_of_consumption where roc_price>100.00;

#limit 限制返回的指定的行数
select *from records_of_consumption order by roc_number desc limit 0,3;
select *from records_of_consumption order by roc_number desc limit 3,3;
select *from records_of_consumption order by roc_number desc limit 6,3;
use testdb;
# max sum min avg count
select cno,max(degree) from score group by cno;
#男 女各有多少人
select ssex,count(*) from student group by ssex;
#求每个人班有多少人
select class,count(*) from student group by class;
#求每科的平均成绩
select cno,avg(degree)  from score group by cno;
#求不止一个男生的班级
select class from student where ssex='男' group by class having count(*)>1 ;
#查询最低分大于70，最高分小于90的Sno列
select sno from score group by  sno having max(degree)<90 and min(degree)>70; 
#查询每门课都大于80的Sno列
select sno ,min(degree),max(degree) from score group by sno having min(degree)>80;

#union去重、 union all全部显示
select name ,country from websites
union all
select app_name,country from apps;

#join 连表查询
use websites;
select w.name,w.url,a.date,a.count from 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 left join access_log a on w.id=a.site_id;

#right join
select*from access_log a right join 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>80 and c.DEGREE<90);