MySQL3 课堂案例

==@ 作者：秦 @ 时间：2025.9.19==

--创建数据库

create database school;

--设置数据库编码格式

-- 设置数据库编码格式为utf8
alter database school charset utf8;

--使用数据库

use school;

--创建 student 表

create table student
(
	id int primary key, -- primary key 主键自动增长且不允许为空
	name varchar(100),
	age int,
	gender char(4),
	tel int,
	address varchar(100)
);

--添加数据

insert into student(id,name,age,gender,tel,address)
values(1,'张三',12,'男',123,'重庆万州');
insert into student(id,name,age,gender,tel,address)
values(2,'李四',12,'女',123,'重庆永川');
insert into student(id,name,age,gender,tel,address)
values(3,'王五',12,'女',123,'重庆铜仁');
insert into student(id,name,age,gender,tel,address)
values(4,'赵六',13,'女',123,'湖北武汉');
insert into student(id,name,age,gender,tel,address)
values(5,'田七',13,'男',123,'湖南长沙');
insert into student(id,name,age,gender,tel,address)
values(6,'肖战',13,'女',123,'北京');
insert into student(id,name,age,gender,tel,address)
values(7,'王一博',14,'男',123,'上海');
insert into student(id,name,age,gender,tel,address)
values(8,'沈腾',14,'男',123,'深圳');
insert into student(id,name,age,gender,tel,address)
values(9,'玛丽',14,'男',123,'广州');
insert into student(id,name,age,gender,tel,address)
values(10,'周杰伦',15,'男',123,'杭州');

---- 查询语法

-- 查询所有数据
select * from student;
-- 只查询男学生的信息
select * from student where gender ='男';
select * from student where gender ='女';
-- 查询 姓名是 周杰伦 性别是 男
select * from student where name ='周杰伦' and gender='男';
-- 查询 姓名是 周杰伦 性别是 男,只显示 电话号码
select tel,address,age from student where name ='周杰伦' and gender='男';
-- 年龄降序排序
-- order by 字段 desc 降序
-- order by 字段 desc 降序
select * from student order by age desc;
select * from student order by age asc;
-- 查询 年龄在12-14之间的学生信息
select * from student where age >=12 and age<=15;
-- 查询地址是 重庆区域的学生信息
-- like '% 文字 %'-模糊查询
select name,age from student where address like '%重庆%';

MySQL1 课堂案例

MySQL2 课堂案例