JAVA培训day8

Day8
数据库（删插改选）
今天主要讲了数据库：
alter table useradd sex varchar(1)null;

select*from user;
– 同时修改两个值
update user set age=18,sex='男’where name=‘小明’;
update user set age=16,sex='女’where name=‘小红’;
insert into user(name,pwd)values(‘小李’,‘241’);
insert into user(name,pwd)values(‘小白’,‘991’);
insert into user(name,pwd)values(‘小黑’,‘992’);
insert into user(name,pwd)values(‘小张’,‘996’);
update user set age=12,sex='男’where name=‘小李’;
update user set age=21,sex='女’where name=‘小白’;
update user set age=20,sex='男’where name=‘小黑’;
update user set age=17,sex='男’where name=‘小张’;

– 查询

select*from user where sex='男’and age>17;

– 查询结果只需要名字
select name from user where sex=‘女’ and age>18;

– 统计男生多少人 count 是mysql自带的函数，表示统计记录条数

select count(*)from user where sex=‘男’;

select avg(age) from user where sex=‘男’;

– 找出用户表里的最大年龄

select max(age) from user;

– 找出用户表里的最小年龄
select min(age) from user;

– 分组查询

select count(),sex from user group by sex;
– 取别名 用as当然as也可以不写，用空格代替
select count() as’总人数’,sex ‘性别’ from user group by sex;

select * from user where name=‘小白’ and pwd=‘991’;

select count(*),classname from student group by classname;

select *from student where classname ='JAVA1’or classname=‘JAVA9’;

然后又做了一些比较复杂的数据库的过滤
alter table student add subject varchar(10)null;

select*from student;

update student set subject=‘语文’;

insert into student (name,id,score,classname,subject)
values (‘哈哈’,‘1’,‘44’,‘JAVA5’,‘英文’);

insert into student (name,id,score,classname,subject)
select name,id,score,classname,‘数学’ from student where subject=‘语文’;

select avg(score)from student where classname=‘JAVA1’;
select avg(score),subject from student where classname=‘JAVA1’ group by subject;

– 每个(意味着分组group by)班有多少人语文成绩大于80
select count(*),classname from student where subject=‘语文’ and score>80 group by classname;

– 查询，优秀人>1的班级
– where和having的区别？ 1.分组后在过滤只能用having 2.group by后面只能用having
– where用于表名的后面，用来过滤查询结果
select count(),classname from student where subject=‘语文’ and score>80 group by classname having count()>1;
– 排序 使用order by列名
– 默认是从小到大，升序
select *from student where classname ='JAVA1’and subject='语文’order by score;
– 加了desc以后，就变成了从大到小，降序
select *from student where classname ='JAVA1’and subject='语文’order by score desc;

HashMap
最后讲了HashMap，复习了几个集合的特性
import java.util.HashMap;

public class MapTest {

public static void main(String[] args) {
	// TODO Auto-generated method stub
	//HashMap 特点
	//1.存储数据格式是key value形式
	//2.hash存储是无序的，无下标概念
	//3.key是不可以重复，value可以重复
	
	//数组特点
	//1.创建的时候，必须规定里面value的数据类型
	//2.创建的时候，必须规定数组的长度
	//3.集合里面数据有序，可以通过下表访问
	
	//ArrayList的特点
	//1.value的类型可以不固定，放任何类型
	//2.不固定长度
	//3.集合里面数据有序，可以通过下表访问
	
	
	HashMap map=new HashMap();
	//Map存储数据的格式是：key -value
	map.put("aa", 11);
	map.put("bb", "你好");
	//通过key获取value
	System.out.println(map.get("aa"));
	
}

}