mysql基本原语法操作

在mysql这种关系型数据库中，文中所列的基本用法需要重点掌握，小白的福音啊~

该文章仅作为学习mysql数据库操作的学习笔记

-- 注释

-- 创建表格
-- not null  非空约束. 该列数据不可以为空
-- primary key  这一列是主键. 全表唯一. 不能重复
-- auto_increment  自动增长. 不用我们维护
create table stu(
	sno int(5) not null  primary key auto_increment,
	sname varchar(50) not null, 
	sage int(3)
)

-- 删除表格
drop table stu;


-- 添加数据  insert into xxx(字段, 字段,) values (值1, 值2,)
insert into stu(sname, sage, school) values ("李嘉诚", 18, "沙河小学");
insert into stu(sname, sage, school) values ("渣渣辉", 28, "巩华家园小学");
insert into stu(sname, sage, school) values ("张翠山", 38, "师大附小");

-- 删除数据  delete from xxxx where 条件
delete from stu where sno = 4 ;
delete from stu;

-- 修改数据 update xxx set 字段 = 值, 字段2 = 值2.... where 条件
update stu set sage = 79 where sno = 5;
update stu set sage = 79, school = '鹤北镇小学' where sno = 5;

-- 查询
-- 简单查询 select *|字段1, 字段2, 字段3... from xxx
select * from stu;
select sname as `姓名`, school as `学校` from stu;

-- 带条件的查询语句  where
select * from stu where sage = 28;
select * from stu where sage > 28;

-- 不等于: <>  !=
select * from stu where sage <> 28;
select * from stu where sage != 28;

-- 年龄在28-56之间的人的信息
select * from stu where sage between 28 and 56;

-- 查询姓李的人的信息
select * from stu where sname like '李%';

-- 查询年龄是28, 38的人
select * from stu where sage in (28, 38);

-- 通过and和or进行连接
select * from stu where sage = 28 or sage = 38;
-- select * from stu where username  = ? and password = ?



-- 创建一个学生表
create table stu_new(
	sno int(5) primary key auto_increment,
	sname varchar(30),
	sgender int(2),
	sage int(5),
	score int(5),
	class varchar(50)
)

insert into stu_new(sname, sgender, sage, score, class) values 
("赵本山", 1, 65, 90, "一年二班"),
("范伟", 1, 60, 23, "一年二班"),
("小沈阳", 1, 31, 9, "一年一班"),
("沈春阳", 2, 28, 60, "一年一班"),
("严启航", 1, 18, 99, "一年一班"),
("赵四", 1, 38, -9, "一年三班"),
("武则天", 2, 1300, 67, "一年三班");


-- 统计每个班级的平均成绩是多少
-- 聚合函数, sum(), count(), avg(), max(), min()
-- 聚合函数一般和group by子句搭配使用
-- group by 分组
-- 规定. 凡事没有放在聚合函数内的东西. 都要堆在group by 后面
select class, avg(score) from stu_new group by class;

-- 统计男生女生有多少人
select sgender, count(*), avg(score),max(score), min(score) from stu_new group by sgender;

-- 统计每个班级的男生女生数量
select class, sgender, count(*) from stu_new group by class, sgender;

-- 对统计结果进行筛选, having 条件
-- 统计出平均成绩合格的班级
select class, avg(score) from stu_new group by class having avg(score) >= 60;


-- 排序
select * from stu_new order by score asc;
select * from stu_new order by score desc;

-- limit start, length
select * from stu_new limit 0, 4