sql语句练习

-- 创建一个带有枚举类型的数据表
create table testEnum(
testID int(4) not null,
enumValue enum('郑州', '安阳', '许昌')
);
-- 插入数据，采用的方式是使用枚举里面的具体数据
insert into testEnum(testID, enumValue) values(1, '郑州');
insert into testEnum(testID, enumValue) values(2, '安阳');
insert into testEnum(testID, enumValue) values(3, '许昌');


select * from testEnum;


-- 插入数据，但是这里用的是枚举数据的特征，每一个枚举类型都是独立特有，值
-- 是不相同的，采用是枚举的数值
insert into testEnum(testID, enumValue) values(4, 1);
insert into testEnum(testID, enumValue) values(5, 2);
insert into testEnum(testID, enumValue) values(6, 3);


-- 这条语句是错误的，因为插入的枚举类型数值，超出了枚举的范围
-- 枚举的范围从1开始
insert into testEnum(testID, enumValue) values(7, 4);


-- 创建带有集合SET类型的数据表
create table testSet(
-- not null 表示该数据不能为空
-- primary key 表示主键
-- auto_increment 表示自动增长
testID int(4) not null primary key auto_increment,
likes set('AUGA3', 'AKM', 'M4A1', 'Kar98k')
);


-- 0000 1010 
-- 下面两条语句不对，虽然testID采用了自动增长，但是在通过SQL
-- 语句添加数据时，仍然要考虑数据的一致性
insert into testSet values('AUGA3');
insert into testSet values(1);


-- 正确！！！
insert into testSet(likes) values('AUGA3');


-- 不正确！！！因为这给一个字段两个数值
insert into testSet(likes) values('AUGA3', 'AKM');


-- 正确！！！
insert into testSet(likes) values('AUGA3,AKM');


-- 推荐方式
-- 因为SET数据类型 每一个数据的对应的数值是1,2,4,8~~~
-- 我们可以采用组合求和的方式来对应不同的数据
insert into testSet(likes) values(15);
insert into testSet(likes) values(7);




-- 查询数据表stuinfo里面所有信息
select * from stuInfo;


-- 获取stuinfo里面的姓名
select stuName from stuInfo;
-- 获取stuInfo里面姓名和年龄的信息，这里不同的字段用逗号隔开
select stuName, stuAge from stuInfo;


-- 获取stuInfo里面姓名和年龄，但是要求年龄是大约等于45
select stuName, stuAge from stuInfo where stuAge >= 45;


-- 获取stuInfo里面的姓名和年龄数据，但是要求年龄大于45而且成绩大于90
select stuName as "姓名", stuAge as "年龄" from stuInfo where stuAge > 45
and stuScore > 90;


-- 获取stuInfo里面的姓名和性别数据，但是要求年龄大于等于45或者成绩大于等于85
select stuName as "姓名", stuSex as "性别" from stuInfo where stuAge >= 45 
or stuScore >= 85;
-- 插入一个姓名重复的数据
insert into stuInfo values(7, '刘晓磊', '男', 18, 60);


-- 获取stuInfo里面姓名，但是要过滤掉重复的姓名
select distinct stuName from stuInfo;


-- 获取stuInfo里面的姓名数据，并且改变表字段展示的名字
-- 利用as 给字段起一个别名
select stuName as "姓名" from stuInfo;


-- 排序查询(默认升序)
select * from stuInfo order by stuScore asc; #ascending


-- 排序查询 降序
select stuName, stuAge from stuInfo order by stuAge desc; #descending


-- 多重条件排序，按照成绩升序排序，如果成绩相同，按照年龄降序排序
-- 首先会按照第一个条件排序，但是如果发现出现相同的数据时，会按照第二个
-- 条件继续排序，第一个条件是主条件，第二个条件是辅助条件
select * from stuInfo order by stuScore asc, stuAge desc;


-- 数据准备
insert into stuInfo values(8, '吴京', '男' , 42, 99);
insert into stuInfo values(9, '谢楠', '女' , 36, 99);
insert into stuInfo values(10, '王宝强', '男' , 40, 90);
insert into stuInfo values(11, '黄磊', '男' , 50, 95);
insert into stuInfo values(12, '何炅', '男' , 50, 100);
insert into stuInfo values(13, '薛之谦', '男' , 39, 96);
insert into stuInfo values(14, '刘亦菲', '女' , 33, 85);
insert into stuInfo values(15, '黄轩', '男' , 45, 95);




-- 模糊查询
-- 要求查询的数据是 stuName以爸爸结尾的数据
select * from stuInfo where stuName like "%爸爸";


-- 查询姓名以刘开始的信息
-- % 通配的是从0 到 n 个字符
select * from stuInfo where stuName like "刘%";


-- 查询姓名是以磊结尾，并且磊字之前有且只能有一个字符
-- _表示匹配一个字符
select * from stuInfo where stuName like "_磊";


-- 查询姓名中带有磊字数据
select * from stuInfo where stuName like "%磊%";




-- 模拟分页 limit
-- 表示只获取前三条数据
select * from stuInfo limit 3;


-- 以五个数据为一页，做分页处理，
-- limit 0, 5 表示从第0个数据开始，向后获取5个数据
-- limit 5, 10  表示从第5个数据开始，向后获取10个数据
select * from stuInfo limit 0, 5;
select * from stuInfo limit 5, 5;
select * from stuInfo limit 10, 5;


-- 内置函数
-- 【注意】不推荐使用！！！这样会影响数据库的效率，增长数据库的负担
-- 通常都会从数据库中获取到数据之后，然后通过Java代码来完成剩余操作


-- 获取最大值
select max(stuAge) as "最大年龄" from stuInfo;


-- 获取最小值
select min(stuScore) as "最低分" from stuInfo;


-- 平均分
select avg(stuAge) as "平均年龄" from stuInfo;


-- 获取总数
select count(*) as "总数" from stuInfo;
select count(*) as "总数" from stuInfo where stuAge > 45;


-- 数据备份
INSERT INTO `stuinfo` VALUES (1,'刘晓磊','男',16,99),(2,'晓晨',NULL,NULL,NULL),(3,'马云爸爸','男',50,99),(4,'强东老爷','男',45,90),(5,'花藤爸爸','男',60,80),(6,'丁磊大叔','男',52,95),(7,'刘晓磊','男',18,60),(8,'吴京','男',42,99),(9,'谢楠','女',36,99),(10,'王宝强','男',40,90),(11,'黄磊','男',50,95),(12,'何炅','男',50,100),(13,'薛之谦','男',39,96),(14,'刘亦菲','女',33,85),(15,'黄轩','男',45,95);


-- 删除数据表里面的所有数据行
delete from stuInfo;


-- 删除指定ID的数据行
delete from stuInfo where stuId = 5;


-- 删除成绩小于80的数据行
-- where 之后可以跟条件 ，> < >= <= = !=          不等于 <> 
delete from stuInfo where stuScore < 80;


-- 使用truncate清空整个数据表，不会影响数据表结构，但是会清空数据行
-- 并且会影响原本的自增条件，从1开始
truncate table testset;


insert into testset(likes) values(6);




-- 修改数据 stuId为2的数据
update stuInfo set stuSex = '男', stuAge = 18, stuScore = 99 
where stuId = 2;


-- 修改stuName = '花藤爸爸' 的性别
update stuInfo set stuSex = '女' where stuName = "花藤爸爸";




-- 连表查询 一对一
create table men(
menID int(4) not null primary key auto_increment,
menName char(30),
menAge tinyint, 
grilID int # 男生的老婆ID
);


create table gril(
grilID int(4) not null primary key auto_increment,
grilName char(30),
grilAge tinyint
);


-- 插入数据
insert into men(menName, menAge, grilID) values("李雷雷", 30, 1);
insert into men(menName, menAge, grilID) values("苏孝乾", 22, 2);
insert into men(menName, menAge, grilID) values("余海洋", 24, 3);
insert into men(menName, menAge, grilID) values("小胖", 30, 4);


insert into gril(grilName, grilAge) values('韩梅梅', 30);
insert into gril(grilName, grilAge) values('春哥', 35);
insert into gril(grilName, grilAge) values('如花', 55);
insert into gril(grilName, grilAge) values('晓晨', 24);


-- 连表查询 用在男生表中的女生ID和女生表中的女生ID进行匹配，展示对应的数据
select * from men, gril where men.grilID = gril.grilID;


-- 内联查询 推荐使用这个方式
-- 可以实现多个表联合查询
-- on 和 where类型
select * from men inner join gril on men.grilID = gril.grilID;




-- 一对多
create table father(
fID int, 
fName char(30)
);


create table son(
sID int,
sName char(30),
fID int
);


insert into father values(1, "何鸿燊");
insert into father values(2, "李嘉诚");


insert into son values(1, "何酋君", 1);
insert into son values(2, "何酋亨", 1);
insert into son values(3, "李泽楷", 2);
insert into son values(4, "李泽钜", 2);


-- 查询一下李嘉诚的儿子都是谁
select * from father inner join son on son.fID = father.fID 
where fName = "李嘉诚";


-- 查询一下何鸿燊的儿子都是谁，这里使用了别名来简化书写
select * from father f inner join son s on s.fID = f.fID 
where fName = "何鸿燊";


-- 一对多：父子关系，用户和订单关系，商铺和商品




-- 多对多
create table student (
stuID int,
stuName char(30)
);


create table course(
cID int,
cName char(30)
);


create table stuToCourse(
scID int, # 中间表的ID号
stuID int, # 学生ID
cID int  # 课程ID
);


insert into student values(1, "刘晓磊");
insert into student values(2, "李良良");
insert into student values(3, "苏阳阳");
insert into student values(4, "余海洋");


insert into course values(1, "Java从入门到放弃");
insert into course values(2, "C语言从入门到出家");
insert into course values(3, "MySQL从删库到跑路");
insert into course values(4, "C++从入门到跳楼");
insert into course values(5, "PHP是世界上最好的语言");


insert into stuToCourse values(1, 1, 1);
insert into stuToCourse values(2, 1, 3);
insert into stuToCourse values(3, 1, 2);


insert into stuToCourse values(4, 2, 3);
insert into stuToCourse values(5, 2, 4);
insert into stuToCourse values(6, 2, 5);


insert into stuToCourse values(7, 3, 1);
insert into stuToCourse values(8, 3, 3);
insert into stuToCourse values(9, 3, 5);


insert into stuToCourse values(10, 4, 2);
insert into stuToCourse values(11, 4, 1);
insert into stuToCourse values(12, 4, 3);


-- 看一下海洋学了什么
select * from student s 
inner join stuToCourse sc on s.stuID = sc.stuID
inner join course c on sc.cID = c.cID 
where s.stuName = "余海洋";


select * from student s
inner join stuToCourse sc on s.stuID = sc.stuID
inner join course c on sc.cID = c.cID
where s.stuName = "李良良";


-- 能否看一下C语言从入门到出家
select * from course c 
inner join stuToCourse sc on c.cId = sc.cID
inner join student s on sc.stuId = s.stuID
where c.cName = "C语言从入门到出家";


select * from course c 
inner join stuToCourse sc on c.cId = sc.cID
inner join student s on sc.stuId = s.stuID
where c.cName = "MySQL从删库到跑路";


update stuToCourse set cID = 4 where scID = 3;