【数据库学习笔记】03 MySQL数据库CURD整理大全

2018.5.10 修

String 日期存入数据库的Date日期

1.MySQL语句方式

to_date(#{birthday},'YYYY/MM/DD')

2018.4.12

一：数据表操作

创建一个数据表

create table stuInfo {
		#字段名 字段数据类型
		#学生ID
		stuId int,
		
		#姓名 varchar(30) char (30)
		#varchar是一个可变长数据
		stuName varchar(30),
		
		#性别 tinyint 1个自己
		stuGender tinyint,
		
		#年龄 tinyint 1个字节，保存年龄足够 255
		stuAge tinyint,
};

添加新的字段

alter table stuInfo add stuDesc text;
	
	alter table stuInfo add stuScort int after stuAge;

修改老字段的数据类型

alter table stuInfo modify stuName char(30);

删除已有字段

alter table stuInfo drop stuDesc;

修改已有字段你的字段名和数据类型

alter table StuInfo change stuGender stuSex char(1);

按照数据库字段顺序插入一条完整的数据

insert into StuInfo(stuId,stuName,stuSex,stuAge,stuScore);
values(1,"卢子逊",'男',16,99)；

下面的语句是不可以的，数据类型和操作的字段不同

insert into StuInfo(stuId,stuName,stuSex,stuAge,stuScore);
values(1,"卢子逊",16,'男',99)；

选中一些字段添加数据，剩余数据会按照默认值处理。

insert into stuInfo(stuId,stuName) values(2,"小陈");

不需要指定字段名 ，但是要求插入的数据是和字段顺序是一致的。

insert into stuInfo values(1,"啊啊啊 ",'啊'，50,99);

创建一个带有枚举类型的数据表

create table testEnum(
    	testID int(4) not null,
    	enumValue enum('青岛','潍坊','烟台')
    );

插入数据，采用的方式是使用枚举里面的具体数据

insert into testEnum(testID,enumValue) values(1,'青岛');
insert into testEnum(testID,enumValue) values(1,'青岛');
insert into testEnum(testID,enumValue) values(1,'青岛');

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);

创建带有集合类型的数据表

create table testSet(

--not null 表示改数据不能为空
--primary key 表示主键
--auto increment 表示自动增长
	testID int (4) not null primary key auto_increment,)-- primary key----整条数据的主键。
	likes set('AUG','AKM','M4A1','Kar98k')
	
	
);

---- 0000 1111
--	下面两条语句不对，虽然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);

二：查询 select（DQL语句）

查询数据表stuinfo里面所有信息

select * from stuInfo;

只获取stuinfo里面的姓名

select stuName from stuInfo;

“，” 获取stuInfo姓名和年龄的信息，这里不同的字段用逗号隔开。

select stuName,stuAge from stuInfo;

“where” 获取stuinfo里面的姓名和年龄，但是要求年龄大于等于45

select stuName,stuAge from stuInfo where stuAge >= 45;

“and” 获取stuinfo里面的姓名和年龄数据，但是要求年龄大于45而且成绩大于90

select stuName as "姓名",stuAge as "年龄"from stuInfo where stuAge >=45 and stuScore > 90;

"or" 获取stuInfo里面的姓名和性别数据，但是要求年龄大于等于45或者成绩大于等于85

select stuName as "姓名"，stuSex as "性别" from stuInfo where stuAge >= 45 or stuScore >=85;

插入一个姓名重复的数据

insert into stuInfo values(7,'卢子逊','18','男',18,60);

“distinct” 获取stuInfo姓名，但是要过滤掉重复的姓名

select distinct stuName from stuInfo;

“as” 获取stuInfo里面的姓名数据，并且改变字段展示的别名字---name->as 姓名

--利用as 给字段起一个别名
select stuName as "姓名" from stuInfo;

"by ascending" 排序查询(默认升序)

select * from stuInfo order by stuScore asc; --#ascending

“by descending” 排序查询 降序

select*from stuName,stuAge from stuInfo order by stuAge desc; --#descending

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

数据准备

insert into stuInfo values(10,'卢子逊爸爸','男'，45,6);
insert into stuInfo values(10,'李伟爸爸','男'，45,6);
insert into stuInfo values(10,'李钱','男'，45,6);
insert into stuInfo values(10,'卢磊','男'，45,6);
insert into stuInfo values(10,'卢子逊','男'，45,6);
insert into stuInfo values(10,'卢子逊','男'，45,6);
insert into stuInfo values(10,'卢子逊','男'，45,6);

"like" 模糊查询

--要求查询的数据是 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,10;

内置函数

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

max(字段名) 获取最大值

select max(stuAge) as "最大年龄" from stuInfo;

min(字段名) 获取最小值

select min (stuScore) as "最低分" from stuInfo;

avg(stuAge) 获取平均分

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

count(*) 获取总数

select count(*) as "总个数" from stuInfo where stuAge >= 45;

三：删除 delete

删除指定ID的数据行

delete from stuInfo where stuId = 5;

删除成绩小于80的数据行

--where 之后可以跟条件 ， > < >=  <=   =  !=   不等于 <>
delete from stuInfo where stuScore < 80;

使用truncate清空整个数据表，不会影响数据表结构，但是会清空数据行和自增条件并且会影响原本的自增条件，例如ID重新从0开始。

truncate table xxx;

truncate stuInfo testset where testID = 8;

insert into testset(likes) values(6);

四：更新 update

修改数据 stuId为2的数据 set "字段名" = 新值

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,
	girlID int #男生老婆的ID
	
) ;

create table girl(
	girlID  int(4) not null primary key auto_increment,
	girlName char(30),
	girlAge tinyint,
);

插入数据

insert into men (menName,menAge,girlID) values("卢子逊",30,1);
insert into men (menName,menAge,girlID) values("李乾",23,2);
insert into men (menName,menAge,girlID) values("李伟爸爸",31,3);
insert into men (menName,menAge,girlID) values("卢逊",32,4);
insert into men (menName,menAge,girlID) values("卢子",30,5);

insert into girl(girlName,girlAge,girlID) values("杨静"，23);
insert into girl(girlName,girlAge,girlID) values("杨"，13);
insert into girl(girlName,girlAge,girlID) values("静"，24);
insert into girl(girlName,girlAge,girlID) values("杨位静"，25);

连表查询 用在男生表中的女生ID和女生表中女生ID进行匹配，展示对应的数据。

select * from men ,girl where men.girlID = girl.girlID;

内联查询 推荐使用这个方式可以实现多个表联合查询

-- 关键字 on 和 where 类似

select * from men inner join girl on men.girlID= girl.girlID;

一对多查询

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 = "何鸿燊";

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

INNer join数据准备

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(5,"C++");
insert into course values(4,"MySQL");
insert into course values(3,"C语言");
insert into course values(2,"PHP");

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

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

insert into stuToCourse values(7,3,4);
insert into stuToCourse values(8,3,2);
insert into stuToCourse values(9,3,3);

insert into stuToCourse values(10,4,4);
insert into stuToCourse values(11,4,2);
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语言";

修改 改的是中间表。

update stuToCourse set cID = 4 where scID = 3;

联表 查询

一：内连接

上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！

SQL标准的内连接为：

    SELECT *FROM emp e
    INNER JOIN dept d
    ON e.deptno=d.deptno;

内连接的特点：查询结果必须满足条件。

二：外连接

包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。

a.左外连接

SELECT * FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

b.右外连接

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL

交叉查询，这个结果是有问题的 笛卡尔乘积 存在重复数据 不推荐使用

select empName,deptName from employee,dept;

-- 需求: 查询员工及其所在目标，显示员工姓名和部门名称

多表查询

-- 1.确定查询哪些表格 2.确定要查询的字段 3.表和表之间的关系

内连接查询，只有满足条件的结果才会展示（使用最多的多表查询）

select empName,deptName 	-- 要查询的字段
from employee,dept 	-- 要查询的表格
where empName.deptID =dept.id; --表和表之间的关系

-- inner join 内连接的另一种语法，where查询的结果是一样
select empName,deptName
from employee	--主表
inner join dept --连接的哪一种表
on employee.deptID = dept.id;

-- 使用别名
select e.empName,d.deptName
from employee e
inner join dept d 
on e.deptID = dept.id;

-- 需求，查看每一个部门的员工
-- 预期结果
--	Javaee 张三
--	javaee 赵六
--	ios 王五
--	php 李四

-- A left/right outer join B

左【外】连接查询： 使用左边表中的数据来匹配右边表的数据，如果有符合条件，展示数据格式：A表字段在左 B表字段在右，如果没有符合条件的连接数据，显示null

select d.deptName,e.empName
from dept d
left outer join employee e
on d.id = e.deptID;

右【外】连接查询： 使用左边表中的数据来匹配左边表的数据，如果有符合条件，展示数据格式：A表字段在右，B表字段在左，如果没有符合条件的连接数据，显示null

select d.deptName,e.empName
from employee e
right outer join dept d
on d.id = e.deptID;

自连接查询

-- 修改员工表结构，添加上司
alter table employee add bossId int;

update employee set bossId = 1 where id = 2;
update employee set bossId = 2 where id = 3;
update employee set bossId = 3 where id = 4;

-- 预期结果
	--张三 null
	--李四 张三
	--王五 李四
	--赵六 王五
	
select e.employee,b.employee
from employee e
left outer join employee b
on e.bossId = b.id;