java项目实战实训第四天

今天主要内容为数据库的增删改查练习：

首先创建表以及添加数据：

CREATE TABLE student (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  gender VARCHAR(10) NOT NULL,
  age INT,
  class_name VARCHAR(50) NOT NULL,
  phone VARCHAR(20) NOT NULL
);

INSERT INTO student (name, gender, age, class_name, phone) VALUES
('张三', '男', 18, '1班', '13812345678'),
('李四', '女', 19, '2班', '13412345678'),
('王五', '男', 20, '1班', '18812345678'),
('赵六', '女', 18, '3班', '15012345678'),
('刘七', '男', 19, '2班', '13512345678'),
('陈八', '女', 22, '1班', '13712345678'),
('杨九', '男', 21, '4班', '13912345678'),
('钱十', '女', 18, '3班', '15112345678');

之后通过该表进行以下代码练习：

1、查询学生表中所有学生的信息
SELECT * FROM student;
2、查询名字为"李四"的学生信息
SELECT * FROM student WHERE name = '李四';
3、查询班级为"1班"的学生信息
SELECT * FROM student WHERE class_name = '1班';
4、查询性别为女生且年龄不大于20岁的学生信息
SELECT * FROM student WHERE gender = '女' AND age <= 20;
5、查询学生表中共有多少个不同班级
SELECT COUNT(DISTINCT class_name) FROM student;
6、将所有姓名为"张三"的学生的性别修改为"女"
UPDATE student SET gender = '女' WHERE name = '张三';
7、查询每个班级的学生人数
SELECT class_name, COUNT(*) AS count FROM student GROUP BY class_name;
8、将年龄为空的学生的年龄修改为18
UPDATE student SET age = 18 WHERE age IS NULL;
9、查询没有电话号码的学生
SELECT * FROM student WHERE phone IS NULL OR phone = '';
10、删除所有班级为"3班"的学生记录
DELETE FROM student WHERE class_name = '3班';
11、查询年龄最小的学生信息
SELECT * FROM student ORDER BY age ASC LIMIT 1;
12、查询除了班级为"2班"之外的所有学生信息
SELECT * FROM student WHERE class_name != '2班';
13、将所有年龄不足18岁的学生的班级修改为"未成年班"
UPDATE student SET class_name = '未成年班' WHERE age < 18;
14、查询名字中含有"李"字的学生信息
SELECT * FROM student WHERE name LIKE '%李%';
15、查询每个班级的平均年龄
SELECT class_name, AVG(age) FROM student GROUP BY class_name;
16、查询同时存在男生和女生的班级名
SELECT class_name FROM student GROUP BY class_name HAVING COUNT(DISTINCT gender) = 2;
17、将所有电话号码中的区号改为"010"<