一、初始化数据库
创建数据库example01,并且创建表student,并且往表中插入数据:
(1)创建数据库example01,并且使用该数据库:
create database example01;
use example01;
可以使用show databases查看本机中共有哪些数据库。
(2)创建表students:
create table students(
id int,
student_name varchar(20),
sex char(1),
age int,
achievement double(5,2),
team_id int,
graduation_date date
);
使用desc students查看表结构:
(3)往students表中插入10条数据:
insert into students values(1,'XiaoMing',0,7,100,1,'2020-7-1');
insert into students values(2,'XiaoHong',1,8,199,1,'2020-7-1');
insert into students values(3,'XiaoLiang',0,15,60,2,'2020-7-1');
insert into students values(4,'XiaoMing',0,18,88,2,'2020-7-1');
insert into students values(5,'XiaoZhang',1,2,110.5,3,'2020-7-1');
insert into students values(6,'XiaoLi',0,6,30,3,'2020-7-1');
insert into students values(7,'XiaoLiu',0,3,null,4,'2020-7-1');
insert into students values(8,'zhangsan',0,6,50,4,'2020-7-1');
insert into students values(9,'WangWu',0,6,40,4,'2020-7-1');
insert into students values(10,'XiaoEr',0,6,40,null,'2020-7-1');
使用select * from students,查询students表中所有数据:
二、查询数据(一)——全查询:
1、查询所有学生的信息
select * from students;
2、查询每位学生的成绩:(注:需要有学生名字)
select student_name,achievement from students;
3、查询学生成绩和18年后的年龄
select student_name,achievement,age+18 from students;
该查询不会影响数据库中数据。
4、为上题的字段【age+18】设置别名:
select student_name,achievement,age+18 as 真实年龄 from students;
三、查询数据(二)——按条件查询
1、查询id为1的学生信息:
select * from students where id=1;
2、查询年龄为18岁的小明的信息:
select * from students where student_name='xiaoming' and age=18;
3、查询年龄超过10岁的学生信息:
select * from students where age>10;
4、查询年龄在3~15岁之间的学生信息:
(1)使用【≥】和【≤】
select * from students where age>=3 and age <=15;
(2)使用【between】和【and】
select * from students where age between 3 and 15;
5、查询成绩为null的学生信息:
select * from students where achievement is null;
6、查询年龄为3岁或者年龄为15岁或者年龄为18岁的学生信息:
(1)使用【or】
select * from students where age=3 or age =15 or age=18;
(2)使用【in(x,x,x)】
7、查询名字带有【Xiao】的学生信息:
select * from students where student_name like '%Xiao%';
四、查询数据(三)——排序(order by)
1、升序(asc)
(1)将成绩超过60分的学生,按照分数进行升序排序:
select * from students where achievement>=60 order by achievement asc;
如果是升序的话,【asc】可以省略不写。
(2)将成绩超过60分的学生,按照年龄进行升序排序:
select * from students where achievement>=60 order by age asc;
2、降序(desc)
(1)将成绩超过60分的学生,按照分数进行降序排序:
select * from students where achievement>=60 order by achievement desc;
3、多条件排序:
(1)将学生先按照年龄进行升序排序,年龄相同的,按照分数进行升序排序:
select * from students order by age asc,achievement asc;