简单的SQL语句(复习)

-- 增加
insert into admin values(null,'gxsunshine','123456',1);

-- 删除
delete from admin where loginId = '小明';

-- 改
update admin set loginId = '摆渡人gx' where id = 4; 

-- 查
select * from admin;
select loginId, loginPwd from admin;
select count(*) from admin where loginId='摆渡人gx' and loginPwd='1234';
select count(*) from admin where loginId='摆渡人gx' and loginPwd='123456';
select * from admin where loginId='摆渡人gx' and loginPwd='123456';

-- 简单的SQL注入
select * from admin where loginId='' and 1=1 -- ' and loginPwd='123456';

-- 模糊查询，主要要like,between, in, is null
-- % 可以匹配N个字符
select * from student where name like '张%' 
-- _ 只可以匹配一个字符
select * from student where name like '张_'
-- between 表示在某个区间,配合and使用
select * from score where result between 60 and 80
-- in用于完全配备，可以有多个条件，只要完全匹配其中一个就可以,()不要忘记
select * from student where address in ('广州','深圳');
-- 用 in 同时删除多条数据。更改多条也行。
delete from test where id in(3,4,6)
-- is null 用于查询某个属性null的情况
select * from student where email is null;

-- 分页 limit ,
-- 第一个参数，表示忽略多少条，第二个参数表示取多少条
select * from student limit 5,5;

-- 排序 order by
-- 默认asc 升序排序
select * from score order by result;
降序排序
select * from score order by result desc;

-- 分组查询，必然会用到聚合函数
-- 基本上是 sum(),avg(),min(),max(),count()
-- 前面四个只能用于数值
-- 最小学生的年龄
select min(age) as 年龄 from student;
-- count(*)统计条数，count(属性) 会忽略null值
select count(*) from student
select count(email) from student

-- 分组 group by
-- 关键点：按什么分组？用什么聚合函数？
-- 技巧：某列如果出现在from之前，但是又没有再聚合函数中，
-- 那么一定出现在group by 中.
-- 查询班级中男女同学的个数
select sex,count(*) from student group by sex;
-- 统计每个年级有多少个学生
select gradeId,count(*) as 个数 from student group by gradeId;
-- 统计每个学生的平均分
select studentno,avg(result) as 平均分 from score group by studentno;


-- 连接查询 
-- 查询学号，姓名，年龄，班级名
select 
     studentNo,name,age,grade.gradeName
  from  student,grade
     where student.gradeId = grade.gradeId;

-- 用内连接性能更好一点
select 
    studentNo,name,age,g.gradeName
   from student s 
         inner join grade g 
               on s.gradeId = g.gradeId;