持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第9天，点击查看活动详情
本文是 狂神说 Java：MySQL 最新教程通俗易懂 的课程笔记
前言
学校突然要期中检测🤦♀️，摸鱼的summer表示很无奈，🙄，那就简单了解一下数据库，后续再慢慢补充，期末再补充吧🧑💻
操作数据库语句
操作数据库
MySQL的数据库语言分为四大类：
DDL:数据定义语言DML:数据操作语言DQL:数据查询语言DCL:数据控制语言
创建数据库
create DATABASE `mydata`
删除数据库
DROP DATABASE `mydata`
使用数据库
USE `mydata`
查看所有数据库
SHOW DATABASES;
数据库的常见列类型
| 数值类型 | 大小(字节) |
|---|---|
tinyint | 1 |
smallint | 2 |
mediumint | 3 |
int | 4 |
bigint | 8 |
float | 4 |
double | 8 |
decimal | 字符串形势 |
关于对int(M)这种的简单理解：
再写sql的时候，我们可能会见到int(1),int(4)这种，你可能只是知道，这里面的数字并不代表存储的宽度
我的理解：这个长度，其实就是为了告诉sql数据库，这个字段的存储数据的宽度为M位数，即使你不是M位数，只要不超过这个int类型，其实都是可以正常存储的
| 字符类型 | 说明 |
|---|---|
char | 定长字符串，范围0-255 |
varchar | 变长字符串,范围在0-65535 |
tinytext | 小文本，范围2^8-1 |
text | 文本串,范围5=2^16-1 |
| 日期 | 说明 |
|---|---|
data | 日期格式：YYY_MM_DD |
time | 时间格式：HH:mm:ss |
datetime | 时间格式：YY_MM_DD HH:mm:ss |
timestamp | 时间戳 |
year | 年 |
数据库的字段属性
| 字段属性 | 含义 |
|---|---|
UNSIGNED | 无符号的 |
ZEROFILL | 零填充位数 |
AUTO_INCREASE | 自增 |
NOT NULL | 是否可空 |
DAFAULT | 默认值 |
COMMENT | 注释，可选 |
一个业务型数据库必须有这些字段
| 字段 | 说明 |
|---|---|
id | 一般是主键 |
version | 乐观锁 |
is_delete | 伪删除 |
gmt_create | 创建时间 |
gmt_update | 修改时间 |
创建数据库表
CREATE TABLE `表名`(
`字段名` 列类型 [属性] [索引] [注释]，
....
)[引擎][字符集][注释]
下面举一个列子
CREATE TABLE `student`(
`id` INT(4) NOT NULL ANUT_INCREASEMENT COMMENT '学号'，
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名'，
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' comment '密码'，
`sex` varchar(2) NOT NULL DEFAULT '女' comment '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY key(`id`)
) ENGINE=INNODB default charset=utf8;
# ENGINE=INNODB default charset=utf8;意思是，使用INNODB引擎，使用默认的utf-8字符集
注意：
- 每一句，我们用逗号（
,）进行分隔，最后一行可以没有 - 数据库不使用驼峰法，所以一般是使用小写字母或者下划线分隔，首字母可以大写
- Table名和字段名用反引号括起来
- 字符串，尽量使用单引号
- 一个表只会有一个主键，他跟在语句的最后面
查看表的结构
-- 查看结构
DESC student;
DESCRIBE student;# 描述这个表，就是查看这个表的结构
-- 查看创建数据库的语句
SHOW Create database mydata;
-- 查看建表语句
SHOW create table student;
常见数据库引擎
InnoDBMyISAM
| 特点 | InnoDB | MyISAM |
|---|---|---|
| 事务支持 | 支持 | 不支持 |
| 数据行锁定 | 支持 | 不支持 |
| 外键约束 | 支持 | 不支持 |
| 全文索引 | 不支持 | 支持 |
| 表空间 | 大约是MyISAM的两倍 | 较小 |
总结：
InnoDB:他的安全性比较高，事件处理，可以进行多表多用户操作
MyISAM:节约空间，速度较快
修改删除数据表
修改表的名称rename
-- Alter table 数据表名 Rename 改为的数据表名
ALTER Table student Rename student1;
-- alter:本来就有更改的意思
增加表的字段add
-- Alter table 数据表名 Add 字段名 列类型
ALTER Table student Add age INT(11);
修改表的字段约束(列类型)modify
-- Alter table 数据表名 Modify 字段名 列类型
Alter table student Modify age Varchar(10);
修改表的字段名change
Alter table student Change age age1 Int(1)
modify和change的区别
modify：其实是对约束的修改，对列类型的修改change:类似于重命名，是对字段名进行改变
删除表的字段drop
Alter Table student drop age1;
删除表
Drop table if exists student
MySQL的数据管理
外键
删除有外键的表，必须先删除引用这个表的表
现在我们让student表也拥有grageid，年级字段，我们新建一个表，表示年级，年级字段引用年级表的gradeid字段
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '年级ID',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
还有一种为已经创建的表生成外键的方式
# constraint：约束
Alter Table `student`
ADD constraint `FK_gradeid` Foreign Key(`gradeid`) References `grade`(`gradeid`)
其实你大概理解一下：增加一个约束FK_gradeid,外键是gradeid,依赖是grade这个表的gradeid这个属性
DML语言
数据操作语言
- 插入
INSERT - 更新
UPDATE - 删除
DELETE
插入insert into
举例：
往grade表里面插入
-- INSERT INTO `Table名` (`字段名`) Values(值)
INSERT INTO `grade`(`gradename`)Values('大四')
其实他非常的语义化：Table里面插入values值，因为值可能不是一个
插入多个值
INSERT INTO `grade`(`gradeid`) Values('大一'),('大二')
但是如果你是插入一个完整的记录的话
INSERT INTO `grade` VALUES (3,'大四'),(4,'大一')
我们其实可以不用写字段名，这样他会自已一个一个的慢慢匹配
INSERT INTO `grade` VALUES(4,'大二')
更新update
更新表里面的值
-- 更新
-- UPDATE `数据表` SET `字段`=值 where `字段`=值
-- 更新student表里面的id=20的字段的name值
UPDATE `student` SET `name`='summer' where 'id'=20
当然了，我们也可以一次更新多个值
UPDATE `student` SET `sex`='男','email'='summer@qq.com' where 'id' = 20
这里面我们就可以一次设置多个值，每个值之间，用逗号(,)分开
| 操作符 | 含义 |
|---|---|
= | 等于 |
<>或者!= | 不等于 |
BETWEEN...AND... | 在范围内 |
>,<,>=,<= | 大于，小于，大于等于，小于等于 |
AND | 且 |
OR | 或 |
NOT | 否定 |
删除delete和truncate
删除数据
只删除一条
-- delete from `数据表` WHERE `字段`= 值
delete from `grade` where `gradeid` = 1
清空整个表，但是，它里面的字段啥的，都是保留着的
-- TRUNCATE Table `数据表名`
truncate Table `grade`
区别：
delete和truncate
dlelete:这个在我的理解，他就是将你那条记录删了truncate:就是将所有的记录删了，但是字段不会变
delete和drop
drop:他这个其实都是在删除字段，就是那个表里面的列delete:他删除的是记录，就是表里面的行
DQL查询数据
DQL:数据查询语言
- 数据库最核心的语言
- 所有的查询都是用
select查询 - 简单和复杂的查询都能写
- 使用频率最高的语句
首先先建立一些必须要的数据表，并插入一些字段
-- 创建学生表
drop table if exists `student`;
create table `student`(
`studentno` int(4) not null comment '学号',
`loginpwd` varchar(20) default null,
`studentname` varchar(20) default null comment '学生姓名',
`sex` tinyint(1) default null comment '性别，0或1',
`gradeid` int(11) default null comment '年级编号',
`phone` varchar(50) not null comment '联系电话，允许为空',
`address` varchar(255) not null comment '地址，允许为空',
`borndate` datetime default null comment '出生时间',
`email` varchar (50) not null comment '邮箱账号允许为空',
`identitycard` varchar(18) default null comment '身份证号',
primary key (`studentno`),
unique key `identitycard`(`identitycard`),
key `email` (`email`)
) engine=myisam default charset=utf8;
-- 创建年级表
drop table if exists `grade`;
create table `grade`(
`gradeid` int(11) not null auto_increment comment '年级编号',
`gradename` varchar(50) not null comment '年级名称',
primary key (`gradeid`)
) engine=innodb auto_increment = 6 default charset = utf8;
-- 创建科目表
drop table if exists `subject`;
create table `subject`(
`subjectno`int(11) not null auto_increment comment '课程编号',
`subjectname` varchar(50) default null comment '课程名称',
`classhour` int(4) default null comment '学时',
`gradeid` int(4) default null comment '年级编号',
primary key (`subjectno`)
) engine = innodb auto_increment = 19 default charset = utf8;
-- 创建成绩表
drop table if exists `result`;
create table `result`(
`studentno` int(4) not null comment '学号',
`subjectno` int(4) not null comment '课程编号',
`examdate` datetime not null comment '考试日期',
`studentresult` int (4) not null comment '考试成绩',
key `subjectno`(`subjectno`)
) engine = innodb default charset = utf8;
-- 插入学生数据
insert into `student` (`studentno`, `loginpwd`, `studentname`, `sex`, `gradeid`, `phone`, `address`, `borndate`, `email`, `identitycard`) values
(1000, '123456', '张伟', 0, 2, '13800001234', '北京朝阳', '1980-1-1', 'text123@qq.com', '123456198001011234'),
(1001, '123456', '赵强', 1, 3, '13800002222', '广东深圳', '1990-1-1', 'text111@qq.com', '123456199001011233');
-- 插入成绩数据
insert into `result`(`studentno`, `subjectno`, `examdate`, `studentresult`) values
(1000, 1, '2013-11-11 16:00:00', 85),
(1000, 2, '2013-11-12 16:00:00', 70),
(1000, 3, '2013-11-11 09:00:00', 68),
(1000, 4, '2013-11-13 16:00:00', 98),
(1000, 5, '2013-11-14 16:00:00', 58);
-- 插入年级数据
insert into `grade` (`gradeid`, `gradename`) values
(1, '大一'), (2, '大二'), (3, '大三'), (4, '大四'), (5, '预科班');
-- 插入科目数据
insert into `subject`(`subjectno`, `subjectname`, `classhour`, `gradeid`) values
(1, '高等数学-1', 110, 1),
(2, '高等数学-2', 110, 2),
(3, '高等数学-3', 100, 3),
(4, '高等数学-4', 130, 4),
(5, 'C语言-1', 110, 1),
(6, 'C语言-2', 110, 2),
(7, 'C语言-3', 100, 3),
(8, 'C语言-4', 130, 4),
(9, 'Java程序设计-1', 110, 1),
(10, 'Java程序设计-2', 110, 2),
(11, 'Java程序设计-3', 100, 3),
(12, 'Java程序设计-4', 130, 4),
(13, '数据库结构-1', 110, 1),
(14, '数据库结构-2', 110, 2),
(15, '数据库结构-3', 100, 3),
(16, '数据库结构-4', 130, 4),
(17, 'C#基础', 130, 1);
基本查询
查询所有字段
select * from student;
查询指定字段,并且使用别名
也就是，我们展现出来的，是我们使用的别名
select studentno as '学号',studentname as '学生姓名' from `student`
这时候其实我们展示出来的是
| 学号 | 学生姓名 |
|---|---|
| 1000 | 张伟 |
| 1001 | 赵强 |
查询被连接的字符串
select concat("姓名+学号:",studentname,studentno) AS '新名字' from `student`
其实他的意思，也就是，将本来是两(多)个字段可以合并(concat)为一个
| 新名字 |
|---|
| 姓名+学号：张伟1000 |
| 姓名+学号：赵强1001 |
去重
对结果去重
其实我觉得，就是对当前表的这一列进行去重，返回去重过后的字段
-- select distinct `列名称` from `表名称`
select distinct studentno from `student`
查询表达式
select 100*3 AS RES
| RES |
|---|
| 300 |
select后面的表达式可以包括：
- 文本
- 列(
字段) null- 函数
- 计算表达式
- 系统遍历
where条件语句
就像介绍的那样，其实我们的
where其实就是一个判断语句
查询成绩在95~100
select * from `result` where studentresult Between 95 and 100
他这个是会返回一整条的记录
查询不是1000号学生的成绩
select * from `result` where not studentno = 1000
模糊查询
| 运算符 | 描述 |
|---|---|
IS NULL | 为空 |
IS NOT NULL | 非空 |
LIKE | 匹配 |
IN | 在集合内 |
当我们使用LIKE这个去进行匹配的时候，其实我感觉，就是在使用正则类似
%:相当于*_:相当于?
查询学生表中所有姓张的学生
select `studentno`,`studentname` from `student` where `studentname` like '张%'
查询在指定集合里面的学生
select `studentno`,`studentname` from `student` where `studentname` in (1000,1003,1023)
联表查询
例子：查询所有参加了考试的同学(学号，姓名，科目，分数)
flowchart LR
A[连接查询] --> B[内连接]
B-->D[相当于查询A表和B表的交集部分]
A-->C[外连接]
C-->E[左外查询]
E-->E1[查询左表的所有数据以及两表交集的部分数据]
C-->F[右外查询]
F-->F1[查询右表的所有数据以及两表交集的部分数据]
C-->G[自连接]
G-->G1[当前表与自身的连接查询以及自连接必须使用表别名]
内连接
查询A和B的交集部分数据
-
隐式内连接
select 字段列表 from 表1,表2 where 筛选条件 -
显式内连接
select 字段列表 from 表1 inner join 表2 on 筛选条件
例题：
查询每个员工的姓名以及相关联的部门名称
表结构：emp,dept
筛选条件：emp.dep_id = dept.id
select emp.dep_id,dept.id from `emp`,`dept` where emp.dep_id = dept.id
显式：
select emp.dep_id,dept.id from `emp` inner join `dept` on emp.dep_id = dept.id
也可以使用表别名,as
select e.dep_id,d.id
from `emp` as e
inner join `dept` as d
on e.dep_id = d.id
外连接
查询emp表的所有数据，和对应的部门信息
注意：因为这里需要我们emp的所有数据，所以我们就不能简单地只是用内连接，因为，内连接包含的可能不是全部
左外连接
select e.*,d.id
from emp as e -- 左表
left outer join dept as d -- 右表
on e.dept_id = d.id
右外连接
select e.*,d.id
from dept as d -- 左
right outer join emp as e
on e.dept_id = d.id
自连接
自连接：就是自己连接自己，也就是将一张表连续查询多次
select 字段名
from 表1 as 别名
join 表1 as 别名2
on 条件
子查询一定需要起别名
查询员工及其所诉领导的名字
因为员工和领导都属于员工，都在员工表里面，每个员工有一项manager_id记录领导的id
select a.name,b.name
from emp as a
join emp as b
on a.manage_id = b.id
查询所有员工及其领导姓名
select a.name,b.name
from emp as a -- 左
left outer join emp as b -- 右
on a.manage_id = b.id
等值连接
where：等值连接
查询员工名，工种号，工种名
select e.last_name,e.job_id,j.job_title
from emp as e, job as j
where e.job_id = j.job_id
加筛选条件
查询有奖金的员工名和部门名
select last_name,department_name
from emp as e,department as d
where e.department_id = d.department_id
and e.commission_pct is not null -- 非空
-- 因为之前已经有了一个where,所以，不可以出现多个，下面那个就使用and
查询城市名中第二个字符为o的部门名和城市名
select department_name,city
from department as d,location as l
where d.location_id = l.location.id
and city like '_o%'
做题思路：
- 分析需求，确定我们需要查询的是那些字段
- 从那些表查询数据
- 入谷坡存在多张表的查询，先从两张表的查询开始
- 确定使用那种查询
- 确定判断条件
查询学生所属的年级
select studentno,studentname,gradename
From student -- 左表
Inner join grade -- 右表
on student.gradeid = grade.gradeid
查询参加"高等数学-4"考试的同学信息（学号，学生，姓名，科目名，分数）
select s.SELECT s.studentno, studentname, subjectname, studentresult
FROM student AS s
INNER JOIN `result` AS r
ON s.studentno = r.studentno
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-4';
分组和排序
SQL的select完整语法
select [all|distinct] {*|table.*|[table.]field[as alias],...}
from table [as table_alias],....
[left|right|inner join table2]
[where ...]
[group by ...]
[having ...]
[order by ...]
[limit]
分组：
我的理解就是：你按照什么进行分组，这样当我们出现分组相同的时候，就只会显示一个
SELECT `subject`.subjectname,SUM(`subject`.classhour) AS '总课时' FROM `subject`,grade
WHERE `subject`.gradeid = grade.gradeid
GROUP BY `subject`.subjectname
having subjectname LIKE '%高等数学%
其实听他就是按照subject.name来进行分组
having:其实就是对分组完成以后的，在进行一次筛选
排序：
SELECT studentno, studentresult
FROM result
ORDER BY studentresult DESC;
这边我们使用order by进行排序
DESC:降序
ASC:升序
SELECT s.studentno,s.studentname,sub.subjectname,r.studentresult
FROM student as s
INNER JOIN result as r
ON s.studentno = r.studentno
INNER JOIN `subject` as sub
ON sub.subjectno = r.subjectno
WHERE sub.subjectname LIKE '%高等数学%'
ORDER BY r.studentresult DESC
一定要严格注意，顺序，也就是limit是放在最后的
子查询
简单说：其实就是查询里面嵌套着查询
- 子查询必须是在圆括号里
- 子查询不可以使用
order by,但是可以使用group by,可以起到类似效果 - 子查询不可以使用
聚合函数
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
查询 “高等数学-4” 的所有考试结果（学科，科目编号，成绩），降序排列。
SELECT studentno, subjectno, studentresult
FROM result
WHERE subjectno = (
SELECT subjectno FROM `subject`
WHERE subjectname = '高等数学-4'
)
ORDER BY studentresult DESC;
其实我的理解就是
-
首先我们发现
result里面没有科目这一栏，所以需要和subject联合起来 -
也就是，我们需要在
subject里面找到高等数学-4这个select studentno from subject WHERE subjectname = '高等数学-4' -
然后将两个连起来
为什么这边使用的是subjectno呢？
因为这两个表的这列的字段是相同的
MySQL常见函数
聚合函数
| 函数名 | 描述 |
|---|---|
count | 计数 |
sum | 求和 |
avg | 平均值 |
min | 最小值 |
max | 最大值 |
参考链接：
