1.创建学生表
create table student(
id int primary key auto_increment,
name varchar(20) not null,
sex char(5),
age int,
score double
);
2.在学生表中插入记录
方式一:
insert into student(name,age,sex,score)values('张三丰',22,'男',85);
insert into student(name)values('杨过');
方式二:
insert into student values(2,'小龙女','女',25,88.5);
方式三:
insert into student(name,age,sex,score)values('孙悟空',26,'男',91.5),
('猪八戒',21,'男',60),('白骨精',20,'女',65);
3.修改学生记录
1. 将所有学生的成绩改为60
update student set score=60;
2. 将id为10的学生的成绩修改为100
update student set score=100 where id=10;
3. 将id大于10的学生,年龄增加2岁,成绩提高5分
update student set age=age+2,score=score+5 where id>10;
4. 删除记录
1. 删除所有成绩不及格的学生
delete from student where score<60;
5.查询记录
1. 查询所有学生的所有字段
select * from student;
2. 查询所有学生的姓名和成绩
select name,score from student;
3. 查询所有学生的姓名和成绩,并将查询字段的别名改为汉字
select name as 姓名,score as 成绩 from student;
4. 查询成绩大于80分的学生姓名与成绩
select name,score from student where score>80;
6.限制查询:
1.查询student表中的前3条记录
select * from student limit 3;
2.查询student表中的第3~第5条记录(从偏移量为2开始查,最多查3条)
select * from student limit 2,3;
7.排序查询:
-
按照成绩升序排序
select * from student order by score;
-
按照成绩降序排序
select * from student order by score desc;
-
先按照成绩降序排序,如果成绩相等,则再按照年龄升序排序
select * from student order by score desc,age;
-
查询成绩是前三名的学生记录
select * from student order by score desc limit 3;
8.聚合函数:
-
查询班级的最高分
select max(score) from student;
-
查询班级的平均分
select avg(score) from student;
-
查询班级的总分
select sum(score) from student;
-
查询班级中成绩不为null的记录数
select count(score) from student;
-
查询班级中的总记录数
select count(*) from student;
-
通过计算的方式查询班级的平均成绩
select sum(score)/count(score) from student;
9.分组查询:
-
创建产品表
create table product( id int primary key auto_increment, name varchar(20) not null, price double not null, address varchar(20), type varchar(20) );
-
插入记录
insert into product(name,price,address,type)values ('方便面',5,'北京','零食'),('牙膏',12,'西安','日用品'), ('麻辣条',2,'黑龙江','零食'),('Mac电脑',2000,'美国','电子'), ('洗面奶',35,'西安','日用品'),('锅巴',8,'河南','零食');
-
查询同一种类商品(type)的平均价格各为多少
select type,avg(price) from product group by type;
-
查询零食组商品的平均价格为多少?
select type,avg(price) from product group by type having type='零食';
-
按照type和address进行分组,每组的平均价格是多少?
select type,address,avg(price) from product group by type,address;
10.模糊查询:
-
查询名字中包含’张’的学生记录
select * from student where name like '%张%';
-
查询姓’张’的学生记录
select * from student where name like '张%';
-
查询名字包含三个字,并且第二个字为’张’的学生记录
select * from student where name like '_张_';