单表基础

创建两个表

CREATE TABLE student (
id int(11) DEFAULT NULL COMMENT ‘学号’,
name varchar(20) DEFAULT NULL COMMENT ‘学生名字’,
score double DEFAULT NULL COMMENT ‘学生成绩’,
groupname varchar(20) DEFAULT NULL COMMENT ‘班级’

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE user (
name varchar(100) DEFAULT NULL,
pwd varchar(100) DEFAULT NULL,
id int(11) NOT NULL AUTO_INCREMENT,
age int(11) DEFAULT NULL,
sex varchar(1) DEFAULT NULL,
email varchar(255) DEFAULT NULL,
mobile varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

sql语句练习
use java9;
– *表示所有的列
select * from user;
– 如果查询结果不需要全部的列，可以把需要的列名放在select后面
select name,pwd from user;

– where是一个关键字，表示后面接查询的过滤条件
select * from user where age>10;
– 修改的格式： update 表名 set 列的名字=值
update user set sex=‘男’;

update user set sex=‘女’ where name=‘yyy’;
– 如果需要修改多列的数据，可以set后面用,隔开
update user set sex=‘女’,age=23 where name=‘天天’;

– 如果mobile是null值就不算了，只统计了mobile非空的多少条
select count(mobile) from user;

– count()的话，所有列只要有一列有值，就算一条
select count() from user where sex=‘男’;
– avg算列的平均值
select avg(age) from user;
– as 表示取别名
select count() as ‘男生总数’ from user where sex=‘男’;
– group by 分组
– 先分组， 再计数
select sex,count() from user group by sex;

select * from student;
– 统计每个班的人数
select groupname, count() from student group by groupname;
– 统计每个班的及格人数
select groupname, count() from student where score>=60 group by groupname;

– 统计每个班，大于80分的人数，并且降序排列
select groupname, count() from student where score>=80 group by groupname order by count() desc;

– 统计每个班的及格人数,并且只显示及格人数大于6的记录
select groupname, count() from student where score>=60 group by groupname having count()>6;