04_mysql练习

1 单表练习

-- 作业1：
DROP DATABASE db_1;
-- 1 创建数据库db_1 编码集为gbk
CREATE DATABASE db_1 CHARSET='gbk';

-- 4 使用数据库db_1
USE db_1;

-- 2 查看与db_1相关的编码集
SHOW VARIABLES LIKE '%char%';

-- 3 修改db_1编码集为utf8 
ALTER DATABASE db_1 CHARSET='utf8';  

-- 5 创建表student: 名字 性别  年龄 是否党员  照片 分数  饭卡钱数  生日
CREATE TABLE student(
   sname VARCHAR(4),
   sex CHAR(1),
   sage INT,
   sdy BOOL,
   sphoto BLOB,
   score FLOAT(4,1),
   smoney DECIMAL(9,2),
   sbirth DATE
);

DESC student;
-- 6 修改列名和列类型
ALTER TABLE student CHANGE sbirth sbirthday DATETIME;

-- 7 删除饭卡钱数
ALTER TABLE student DROP smoney;
ALTER TABLE student MODIFY sphoto LONGBLOB(9999);

/*一般情况下 不会吧图片/视频/音乐存储在数据库中 ：： 只存储文件的url*/
INSERT INTO student(sname,sphoto) VALUES("韩梅梅",LOAD_FILE("C:/Users/Administrator/Desktop/img/1.jpg"));


-- 8 添加10条记录
SELECT * FROM student;
INSERT INTO student VALUES(
   SUBSTRING(REPLACE(UUID(),"-",""),6,4),
   IF(RAND()>0.5,'男','女'),
   TRUNCATE(RAND()*8+18,0),
   RAND()>0.5,
   NULL,
   TRUNCATE(RAND()*100,1),
   CONCAT(TRUNCATE(RAND()*10+1900,0),'/',TRUNCATE(RAND()*12+1,0),'/',TRUNCATE(RAND()*30+1,0),' 10:00:00')
);
SELECT * FROM student;



-- 9  修改女生的分数+1
UPDATE student SET score=score+1 WHERE sex='女';

-- 10 修改所有学生名字后面加"同学"两个字
ALTER TABLE student MODIFY sname VARCHAR(10);
UPDATE student SET sname=CONCAT(sname,'同学');

-- 11 删除分数少于30的学生记录
DELETE FROM student WHERE score < 30;

-- 12 查询所有记录
SELECT * FROM student;

-- 作业2：
-- 1 创建一个学生表：学号 名字 年龄 性别 分数 生日
DROP TABLE student;
CREATE TABLE student(
   sid INT,
   sname VARCHAR(4),
   sage INT,
   sex CHAR(1),
   score FLOAT(4,1),
   sbirth DATE
);
INSERT INTO student VALUES(
   TRUNCATE(RAND()*10000,0),
   SUBSTRING(REPLACE(UUID(),"-",""),6,4),
   TRUNCATE(RAND()*8+18,0),
   IF(RAND()>0.5,'男','女'),
   TRUNCATE(RAND()*100,1),
   CONCAT(TRUNCATE(RAND()*10+1900,0),'/',TRUNCATE(RAND()*12+1,0),'/',TRUNCATE(RAND()*30+1,0))
);

SELECT * FROM student;

-- 2 获取在1995年之前出生的所有学生的信息
/*时间也可以比较*/
SELECT NOW() > STR_TO_DATE('2000年01月01日','%Y年%m月%d日');
SELECT * FROM student WHERE sbirth < STR_TO_DATE('1905年01月01日 00:00:00','%Y年%m月%d日 %H:%i:%s');

-- 3 获取班级中所有的学生的姓氏：：字符串截取使用函数substring(sname,index,length)
SELECT sname 名字,SUBSTRING(sname,1,1) 姓 FROM student;
SELECT DISTINCT SUBSTRING(sname,1,1) 姓 FROM student;

-- 4 获取女生和男生的最高分，最低分，总人数
SELECT sex 性别, COUNT(*) 人数,MAX(score) 最高分,MIN(score) 最低分
FROM student
GROUP BY sex

-- //5 获取每个月过生日的总人数 按月份倒序
SELECT sbirth,MONTH(sbirth),EXTRACT(MONTH FROM sbirth)
FROM student;


SELECT MONTH(sbirth),COUNT(*)
FROM student
GROUP BY MONTH(sbirth)
ORDER BY MONTH(sbirth) DESC;


-- 5 获取年龄为20，22，26的所有男生的信息
SELECT * FROM student WHERE sex='男' AND sage IN(20,22,26);

-- 6 获取所有及格的学生中不同年龄的情况：年龄 人数 平均分 
SELECT sage 年龄,COUNT(*) 人数 ,AVG(IFNULL(score,0)) 平均分
FROM student
WHERE score >=60
GROUP BY sage;

-- 7 获取所有分数及格的20岁以上的男生并且名字中含有"a"的学生的信息
SELECT *
FROM student
WHERE score >=60 AND sage >20 AND sex='男' AND sname LIKE '%a%';

-- //8 获取下个月过生日的所有学生的信息
SELECT * FROM student WHERE MOD(MONTH(sbirth),12)=MOD(MONTH(NOW())+1,12);
SELECT * FROM student WHERE MONTH(sbirth)=IF(MONTH(NOW())+1=13,1,MONTH(NOW())+1);

-- 8 获取分数最高的三位女学生的信息
SELECT *
FROM student
WHERE sex='女'
ORDER BY score DESC
LIMIT 0,3;

-- 9 让所有的同学进行自我介绍：我叫xxx,今年xx岁，考了xx分，请叫我美女/帅哥
SELECT  CONCAT('我叫',IFNULL(sname,'无名'),',今年',IFNULL(sage,0),'岁，考了',IFNULL(score,0),
        '分，请叫我',IF(sex='男','帅哥',IF(sex='女','美女','妖精'))) 自我介绍
FROM student;
INSERT INTO student(sid) VALUES(1001);