数据库练习4

-- 创建数据库（--表示注释）
CREATE DATABASE my1


-- 使用数据库
USE my1


DROP DATABASE my1


-- 删除数据库
DROP DATABASE my1


-- 创建表
CREATE TABLE t_student(
id  INT PRIMARY KEY AUTO_INCREMENT ,
studentName VARCHAR(20),
sex VARCHAR(10),
class VARCHAR(10),
birthday DATE,
chinese DECIMAL(10,2),
math DECIMAL(10,2),
english DECIMAL(10,2)


)CHARSET=utf8;


-- 删除表
DROP TABLE t_student
DROP TABLE t_student
-- 查询表
SELECT*FROM t_student


SELECT id ,FROM t_student


-- 添加
INSERT INTO t_student(studentName,sex,class,birthday,chinese,math,english) VALUES('张三','男','一班','1990-01-01',90,80,60);
INSERT INTO t_student(studentName,sex,class,birthday,chinese,math,english) VALUES('李四','女','二班','1990-02-03',80,50,100);
INSERT INTO t_student(studentName,sex,class,birthday,chinese,math,english) VALUES('王五','男','三班','2000-03-03',70,40,55);
INSERT INTO t_student(studentName,sex,class,birthday,chinese,math,english) VALUES('赵六','女','一班','2000-04-04',66,10,100);
INSERT INTO t_student(studentName,sex,class,birthday,chinese,math,english) VALUES('王二麻子','男','三班','2000-05-05',50,100,10);
INSERT INTO t_student(studentName,sex,class,birthday,chinese,math,english) VALUES('田明','女','一班','1990-06-06',40,60,100);
INSERT INTO t_student(studentName,sex,class,birthday,chinese,math,english) VALUES('李坐困','男','二班','1990-07-07',50,10,100);
INSERT INTO t_student(studentName,sex,class,birthday,chinese,math,english) VALUES('匡小龙','女','三班','2000-08-08',100,80,90);
INSERT INTO t_student(studentName,sex,class,birthday,chinese,math,english) VALUES('李二','女','一班','2000-09-09',50,90,80);
INSERT INTO t_student(studentName,sex,class,birthday,chinese,math,english) VALUES('小米','女','二班','1990-11-11',90,87,100);
-- 删除id=2的记录
DELETE FROM t_student WHERE id=2 ;


-- 所有的女生英语成绩都加5分
 UPDATE t_student SET english=english+5 WHERE sex='女';
-- 所有数学不及格的一班学生，数学成绩加2分
UPDATE t_student SET math=math+2 WHERE math<60 AND class='一班';




-- 查询二班所有的男生
SELECT*FROM t_student WHERE class='二班' AND sex='男';


-- 查询三班所有的00后女生
SELECT*FROM t_student WHERE class='三班' AND sex='女' AND birthday>'2000-01-01' AND birthday<'2010-01-01';
-- 查询一班所有语文不及格的姓李的学生
SELECT*FROM t_student WHERE class='一班' AND chinese<60 AND studentName LIKE '李%';


-- 查询一班数学成绩在90分以上的女生
SELECT*FROM t_student WHERE class='一班' AND math>90 AND sex='女';