数据库名称可以为【schoolDB】、字符集【uft8】、排序规则【uft8_general_ci】
建表语句
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`createDate` datetime DEFAULT NULL,
`userName` varchar(20) DEFAULT NULL,
`pwd` varchar(36) DEFAULT NULL,
`phone` varchar(11) DEFAULT NULL,
`age` tinyint(3) DEFAULT NULL,
`sex` char(2) DEFAULT NULL,
`introduce` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
插入语句
insert into student values(0,'2024-2-25 10:00:00','王源','123',
'13453453453',24,'男','客厅狂欢。');
insert into student values(0,'2024-2-25 10:00:00','王俊凯','123',
'13453453453',25,'男','重生之门。');
insert into student values(0,'2024-2-25 10:00:00','易烊千玺','123',
'13453453453',24,'男','送你一朵小红花。');
insert into student values(0,'2024-2-25 10:00:00','丁禹兮','123',
'13453453453',29,'男','大理寺少卿游。');
insert into student values(0,'2024-2-25 10:00:00','成毅','123',
'13453453453',34,'男','莲花楼。');
insert into student values(0,'2024-2-25 10:00:00','吴磊','123',
'13453453453',25,'男','星汉灿烂。');
insert into student values(0,'2024-2-25 10:00:00','白敬亭','123',
'13453453453',31,'男','开端。');
聚合函数的语句使用
#聚合函数5个,数量count(x),最大值max(x),最小值min(x),平均数avg(x),求和sum(x)
select count(*) '所有成年人数量' from student where age>=18;
select max(age) '最大年龄',min(age) '最小年龄' from student;
select avg(age) '平均年龄' from student;
select sum(age) '总年龄' from student;
select avg(age) 'avg出的总年龄',sum(age)/(select count(*) from student) '计算机出的' from student;