1、初始MySQL
JavaEE：企业级Java开发 Web
-
sql
-
前端——页面：展示，数据
-
后台——连接点：连接数据库（JDBC、MyBatis）
连接前端（Severlet、Spring）：控制视图跳转，给前端传递数据
-
数据库——存数据
1.1、什么是数据库
数据库：DB（DataBase）
概念：数据仓库，软件，安装在操作系统上
作用：存储数据、管理数据
1.2、数据库分类
关系型数据库（SQL）：
- DB2
- SQLite
通过表和表之间，行和列之间的关系进行数据的存储
非关系型数据库（NoSQL——Not Only）：
- Redis
- MongoDB
对象存储，通过对象的属性来决定（如存储一个人的位置）
1.3、DBMS（数据库管理系统）
- 数据库的管理软件，科学有效的管理我们的数据，维护和获取数据
- MySQL——数据库管理系统：操作管理数据
- SQLyog——可视化
如果创建浮点型数据，不能指定长度！！！
1.4、连接数据库
命令行连接
mysql -uroot -p123456 --连接数据库
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';--修改用户密码
flush privileges;刷新权限
-------------------------------------
-- 所有语句都使用;结尾
show databases; -- 查看所有的数据库
use school; -- 切换数据库：use 数据库名
show tables; -- 查看数据库中所有的表
describe student； -- 显示表的信息
create database dorms; -- 创建一个数据库dorms
exit；-- 推出连接
-- 单行注释
/*
多行注释
*/
数据库xxx语言(CRUD增删改查)
- DDL 定义
- DML 操作
- DQL 查询
- DCL 控制
2、操作数据库
操作数据库>>操作数据库中的表>>操作数据库中表的数据
mysql关键字不区分大小写
2.1、操作数据库
1、创建数据库
create database [if not exists] dorms;
2、删除数据库
drop database [if exists] dorms;
3、使用数据库
use dorms
如果数据库是一个关键字，则在名字左右加上``
4、查看数据库
show databases
2.2、数据库的列类型(字段)
1、数值
- tinyint 1个字节
- smallint 2个字节
- mediumint 3个字节
- int 4个字节 int
- bigint 8个字节 long
- float 4个字节
- double 8个字节（精度问题！）
- decimal 字符串型的浮点数，金融计算时使用，解决精度问题
2、字符串
- char 字符串固定大小 0-255
- varchar 可变字符串 0-65535
varchar和char 的区别——定长和变长
对于 char(4) 表示固定容纳4个字符，当少于4个字符时，会使用空格填充空缺的部分，使其达到4个字符。如果超过4个字符，会自动截断超出部分
而 varchar(4)，小于4个的话，则插入多少个字符就存多少个。且对于varchar字段来说，需要使用一个（如果字符串长度小于255）或两个字节（长度大于255）来存储字符串的长度（因为varchar是变长的，没有这个长度值他不知道如何读取数据）
()里的长度是当前字符集的字符长度，而不是字节长度
text和varchar的最大限制都是64k个字节,但text本质是溢出存储,innodb默认只会存放前768字节在数据页中,而剩余的数据则会存储在溢出段中。text类型的数据,将被存储在元数据表之外地方,但是varchar/char将和其他列一起存储在表数据文件中，值得注意的是，varchar列在溢出的时候会自动转换为text类型。text数据类型实际上将会大幅度增加数据库表文件尺寸。
text列不允许拥有默认值
int字段的长度与你存储的数据长度无关，与显示有关
char字段的长度与你存储数据长度有关
int类型的存储大小为4个字节，一个字节8位，也就是2^32 。 int的取值范围（-2147483648 ～ 2147483647），在数据库中的int不管后面填的长度为多少，只要在int的取值范围内，都能够将你的存储的数正常放入。int类型中长度的意义是位数不满足时，自动补充0（使用zerofill才能够显示），但不影响存储
3、时间日期
java.util.Date
- date YYYY-MM-DD——日期格式
- time HH:mm:ss——时间格式
- datetime YYYY-MM-DD HH:mm:ss——最常用的时间格式
- timestamp 时间戳，表示1970年到现在的毫秒数
- year 表示年份
4、null
- 没有值，位置
- 不要使用其进行运算，结果为NULL
2.3、数据库的字段属性
Unsigned：
- 无符号的整数
- 声明了该列不能声明为负数
zerofill：
- 0填充
- 不足的位数，使用0填充。int(3) 5–>005
自增：
- 自动在上一条记录的基础上+1（默认）
- 通常用来设计唯一的逐渐index，必须是整数类型
- 可以自定义设计主键自增的起始值和步长
- 一个表只有一列能设置自增，且该字段必须为key
非空：
-
NULL/NOT NULL
-
假设设置为not null，如果不给它赋值则会报错
-
加入没有设置，如果不填写值则默认为null
default：
-
设置默认的值
-
sex，默认值设为男，则如果不指定该列的值，就自动为男
每个表都必须存在以下五个字段（做项目用的，表示数据存在的意义）
/*
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
2.4、创建数据库表
格式
create table [if not exit] `表名`(
`字段名` 列类型[属性][索引][注释]，
`字段名` 列类型[属性][索引][注释]，
......
`字段名` 列类型[属性][索引][注释]
)[表类型][字符串设置][注释]
注意点
使用英文的符号，表的名字和是字段尽量使用``括起来
AUTO_INCREMENT表示自增
字符串使用单引号或双引号括起来
所有的语句后面加英文的逗号，最后一个不用加
PRIMARY KEY表示主键，一般一个表只有一个唯一的主键
示例
CREATE TABLE `people`(
`id` INT(4) ZEROFILL NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`gender` VARCHAR(2) NOT NULL COMMENT '性别',
`age` INT(3) NOT NULL COMMENT '年龄',
`birthday` DATETIME DEFAULT NULL COMMENT '生日',
`phone` INT(11) DEFAULT 123 COMMENT '电话号码',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
常用命令
show tables -- 查看当前数据库的所有表
show create database school -- 查看创建数据库的语句
show create table student -- 查案创建表的语句
desc student -- 显示表的结构
2.5、数据表的类型
-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用
*/
不同类型的区别
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持（表锁定） | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大，约为2倍 |
不同类型的优点
- MYISAM 节约空间，速度较快
- INNODB 安全性高，事务的处理，多表多用户操作
在物理空间存在的位置
所有数据文件都存在data目录下，一个文件夹就对应一个数据库
本质还是文件的存储
物理文件上的区别
使用InnoDB引擎的表结构对应的物理文件
- .frm文件：保存了每个表的元数据，包括表结构的定义等；
- .ibd文件：InnoDB引擎开启了独立表空间(my.ini中配置innodb_file_per_table = 1)产生的存放该表的数据和索引的文件。
- .ibdata文件：共享表空间存储方式使用.ibdata文件，所有表共同使用一个ibdata文件
使用了MyISAM引擎的表会有对应的三种物理文件：
- *.frm–表定义，是描述表结构的文件。
- .MYD–"D"数据信息文件，是表的数据文件。
- *.MYI–"I"索引信息文件，是表数据文件中任何索引的数据树。
设置数据表的字符集编码
charset=utf8
不设置的话为，会是mysql默认的字符集编码
可在my.ini配置文件中设置默认的编码
charset-set-server=utf8
2.6、修改删除表
修改
-- 修改表名：alter table 旧表名 raname 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段：alter table 表名 add 字段名 列属性
ALTER TABLE teacher1 ADD wage INT(10)
-- 修改表的字段：重命名、修改约束
-- 修改约束 alter table 表名 modify 字段名 列属性[]
ALTER TABLE teacher1 MODIFY wage VARCHAR(11)
-- 字段重命名 alter table 表名 change 旧名字 新名字 列属性[]
ALTER TABLE teacher1 change age age1 INT(1)
-- 删除表的字段 alter table 表名 drop 字段名
ALTER TABLE teacher1 DROP age1
删除
-- 删除表
drop table if exists teacher1
3、MySQL数据管理
3.1、外键（了解）
CREATE TABLE `grade` (
`gradeId` INT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT COMMENT '年级ID',
`gradeName` VARCHAR(10) NOT NULL COMMENT '年级名字',
PRIMARY KEY (`gradeId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生表的gradeId字段要去引用成绩表的gradeId
CREATE TABLE `student`(
`id` INT(8) ZEROFILL NOT NULL AUTO_INCREMENT COMMENT '学生id',
`name` VARCHAR(10) NOT NULL COMMENT '学生姓名',
`gradeId` INT(8) ZEROFILL NOT NULL COMMENT '学生年级',
`gender` VARCHAR(3) NOT NULL COMMENT '性别',
PRIMARY KEY(`id`),
-- 定义外键key
KEY `FK_gradeId`(`gradeId`),
-- 给这个外键添加约束（执行引用） references引用
CONSTRAINT `FK_gradeId` FOREIGN KEY(`gradeId`) REFERENCES `grade`(`gradeId`)
)ENGINE INNODB DEFAULT CHARSET=utf8
-- 创建表的时候没有外键关系
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeId` FOREIGN KEY(`gradeId`) REFERENCES `grade`(`gradeId`);
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 被引用表(被引用列)
删除有外键关系的表的时候，必须要先删除去引用的表（从表），在删除被引用的表（主表）
以上的操作都是物理外键，数据库级别的外键，不建议使用（避免数据库过多造成困扰）
最佳操作
- 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
- 我们想使用多张表的数据，想使用外键（用程序实现）
3.2、DML语言
数据库意义：数据存储，数据管理
DML语言：数据库操作语言
- insert
- update
- delete
3.3、添加
-- 添加语句（插入）
-- insert into 表名[(字段1,字段2,字段3)] values ('值A1','值A2','值A3'),('值B1','值B2','值B3')...
INSERT INTO grade(`gradename`) VALUES ('大四');
-- 没有设置NOT NULL的字段和设置了自增的键都可以不赋值，其他字段不赋值都会报错
-- 如果省略表的字段，则会把所有字段与后面的值一一对应（即需要把每个字段的值都写出来，没有设置NOT NULL的字段和设置了自增的键如果不赋值也需要写NULL占位）
INSERT INTO `student` VALUES (NULL, '陈彦亨', NULL, '男');
-- 插入多个数据时values一般换行书写
INSERT INTO `student`(`name`, `gender`)
VALUES ('李四', '男'), ('王五', '女');
语法：insert into 表名[(字段1,字段2,字段3)] values ('值A1','值A2','值A3'),('值B1','值B2','值B3')...
注意事项：
- 字段和字段之间使用英文逗号隔开
- 字段是可以省略的，但是后面的值必须要一一对应，不能少
- 可以同时插入多条数据，values后面的值需要使用逗号隔开
3.4、修改
-- 修改语句(update)
-- 修改学员名字，带了条件
UPDATE `student` SET `name` = '陈梓坤' WHERE id = 1;
-- 不指定条件（会导致所有数据被修改）
UPDATE `student` SET `gender` = '男';
-- 修改多个属性，逗号隔开
UPDATE `student` SET `name` = '臭弟弟', `gender` = '女' WHERE id = 2;
语法：update 表名 set column_name = value, [column_name = value, ...] where 条件
条件语句where：返回布尔值
操作符：
- = ： 等于
- <> 或 != ：不等于
- < 、>、<=、>= ：小于，大于，小于等于，大于等于
- between A and B ：A和B的闭区间
- A and B ：A && B
- A or B ：A || B
- is null ：没有值
注意事项：
- column_name 是数据库的列，尽量带上``
- 条件，筛选的条件，如果没有指定，则会修改所有的列
- value，是一个具体的值，也可以是一个变量/函数
如current_time(获的当前时间)
3.5、删除
delete命令
语法：delete from 表名 [where 条件]
-- 删除数据（避免这样写，会删除所有数据）
delete from `student`;
-- 删除指定数据
delete from `student` where id = 1;
truncate命令
作用：完全清空一个数据库表，表的结构和索引约束不会变
-- 清空student表
truncate `student`;
delete和truncate命令的区别
- 相同点：都能删除数据，都不会删除表结构
- 不同点：
- truncate会重新设置自增列，计数器会归零，而delete的计数器不会归零
- truncate不会影响事务
4、DQL查询数据
4.1、DQL
简单的查询
SELECT CONCAT(StudentNo, CONCAT(':', StudentName)) AS 结果 FROM student
-- 查询表的全部字段
SELECT * FROM student;
-- 查询表的指定字段
SELECT `StudentNo`, `StudentName` FROM student;
-- 函数 concat(a, b)
SELECT CONCAT(StudentNo, CONCAT(':', StudentName)) AS 结果 FROM student
语法：select 字段1,... from 表
输入字段名时不区分大小写，输出的结果列名大小写形式和输入时保持一致，和创建时不一定一致
别名与去重
as 起别名
-- 别名：给结果起一个名字
-- 给字段起别名
SELECT StudentNo AS 学生学号, studentname AS 学生姓名 FROM student
-- 给表起别名
SELECT StudentNo, studentname 姓名 FROM student AS s
其别名时as可以省略,as后的内容可以带引号也可以不带
distinct 去重
-- 查询有哪些学生参加了考试
-- 查询所有成绩，每个学生有多个科目的成绩
select * from result;
-- 查询所有学生，发现重复数据（此表中没有主键）
select `StudentNo` from result;
-- 去除重复数据
select distinct `StudentNo` from result;
数据库表达式
数据库的列（表达式）
-- 查询系统版本（函数）
SELECT VERSION();
-- 查询计算结果（表达式）
SELECT 100*3-1 AS 计算结果;
-- 查询自增的步长（变量）
SELECT @@auto_increment_increment;
-- 查看学员考试成绩加一分后的结果
SELECT `StudentNo`, `StudentResult`+1 AS 加分后 FROM result;
格式：select 表达式 from 表;
数据库中的表达式：
- 文本值
- 列
- null
- 函数
- 计算表达式
- 系统变量
4.2、where条件子句
作用：检索数据中符合条件的值
搜索的条件为一个或者多个表达式
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | A and B / A && B | 逻辑与 |
| or || | A or B / A || B | 逻辑或 |
| not ! | not A / !A | 逻辑非 |
尽量使用英文字母
-- 查询考试成绩再95-100分之间的数据
-- and
SELECT `StudentNo`, `StudentResult` FROM `result`
WHERE `StudentResult` >= 95 AND `StudentResult` <= 100;
-- &&
SELECT `StudentNo`, `StudentResult` FROM `result`
WHERE `StudentResult` >= 95 && `StudentResult` <= 100;
-- 模糊查询（区间）
SELECT `StudentNo`, `StudentResult` FROM `result`
WHERE `StudentResult` BETWEEN 95 AND 100;
-- 查询除了1000号学生意外的同学的成绩数据
-- !=
SELECT `StudentNo`, `StudentResult` FROM `result`
WHERE `StudentNo` != 1000;
SELECT `StudentNo`, `StudentResult` FROM `result`
WHERE !`StudentNo` = 1000;
-- not
SELECT `StudentNo`, `StudentResult` FROM `result`
WHERE NOT `StudentNo` = 1000;
模糊查询：比较字符串
| 运算符 | 语法 | 描述 |
|---|---|---|
| is null | A is null | A为null则为真 |
| is not null | A is not null | A不为null则为真 |
| between | A between B and C | A再B和C之间则为真 |
| like | A like B | SQL匹配，A匹配B则为真 |
| in | A in (A1, A2, A3…) | A是A1,A2,A3…其中的一个值则为真 |
-- 模糊查询
-- 查询姓张的同学
-- like 结合&（代表任意个数的字符），_（代表一个字符）
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '张%';
-- 查询姓张的同学，名字后面只有一个字的
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '张_';
-- 查询姓刘的同学，名字后面有两个字的
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '张__';
-- 查询名字中间有伟字的同学
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentName` LIKE '%伟%';
-- in（具体的一个或者多个值）
-- 查询1001，1002，1003号学员
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `StudentNo` IN (1001, 1002, 1003);
-- 查询在北京或广东的学生(精确相等才返回true)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN ('北京', '广东');
-- null
-- 查询地址为空的学生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IS NULL OR NOT '';
-- 查询有出生日期的学生 不为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IS NOT NULL OR '';
4.3、联表查询
示例
student表
| StudentNo | StudentName |
|---|---|
| 1001 | 小王 |
| 1002 | 小红 |
| 1003 | 小黑 |
result表
| StudentNo | SubjectNo | StudentResult |
|---|---|---|
| 1001 | 1 | 90 |
| 1001 | 2 | 89 |
| 1001 | 3 | 91 |
| 1002 | 1 | 90 |
| 1002 | 2 | 20 |
| 1002 | 3 | 61 |
| 1004 | 1 | 112 |
| 1004 | 2 | 20 |
| 1004 | 3 | 21 |
-- 笛卡尔积
-- 两表关联，把左表的列和右表的列通过笛卡尔积的形式表达出来。
-- 语法：select * from t1 join t2
SELECT s.StudentNo, StudentName, SubjectNo, StudentResult
FROM Student AS s
INNER JOIN result AS r
/* 思路
1.分析需求，分析查询的字段来自哪些表
2.确定使用哪种连接查询
确定交叉点（这两个表中哪个字段数据是相同的）
判断的条件：学生表中的StudentNo = 成绩表的StudentNo
*/
-- 内连接
SELECT s.StudentNo, StudentName, SubjectNo, StudentResult
FROM Student AS s
INNER JOIN result AS r
ON s.StudentNo = r.StudentNo;
-- 左外连接
SELECT s.StudentNo, StudentName, SubjectNo, StudentResult
FROM Student AS s
LEFT JOIN result AS r
ON s.StudentNo = r.StudentNo;
-- 右外连接
SELECT s.StudentNo, StudentName, SubjectNo, StudentResult
FROM Student AS s
RIGHT JOIN result AS r
ON s.StudentNo = r.StudentNo;
Joins总结
| 操作 | 作用 |
|---|---|
| inner join：内连接 | 两表关联，保留两表中交集的记录 |
| left join：左外连接 | 两表关联，左表全部保留，右表关联不上用null表示 |
| right join：右外连接 | 两表关联，右表全部保留，左表关联不上用null表示 |
| full join：全连接 | 两表关联，两表的内容均保留，没有关联的字段用null表示 |
| 左表独有 | 两表关联，查询左表独有的数据，类似于集合中的t1 - t2 |
| 右表独有 | 两表关联，查询右表独有的数据，类似于集合中的t2 - t1 |
| 并集去交集 | 两表关联，取并集然后去交集 |
oracle里面有full join,但是在mysql中没有full join。我们可以使用union来达到目的。
练习
-- 查询缺考的同学
SELECT s.StudentNo, StudentName, SubjectNo, StudentResult
FROM Student AS s
LEFT JOIN Result AS r
ON s.StudentNo = r.StudentNo
WHERE studentresult IS NULL;
思路
- 我要查询哪些数据：
select ... - 从哪几个表中查：
from 表 xxx join 连接的表 on 交叉条件（两表的共有列） - 假设存在多张表的查询，先两张查询再慢慢叠加
SELECT s.StudentNo, StudentName, SubjectName, StudentResult
FROM Student AS s
RIGHT JOIN Result AS r
ON s.StudentNo = r.StudentNo
LEFT JOIN `subject` AS sub
ON r.SubjectNo = sub.SubjectNo
4.4、自联接
自己的表和自己的表联接
核心：一张表拆成两张一模一样的表
第一张表
| categoryId | pId | categoryName |
|---|---|---|
| 2 | 1 | 信息技术 |
| 3 | 1 | 软件开发 |
| 5 | 1 | 美术设计 |
第二张表
| categoryId | pId | categoryName |
|---|---|---|
| 4 | 3 | 数据库 |
| 8 | 2 | 办公信息 |
| 6 | 3 | web开发 |
| 7 | 5 | ps技术 |
操作：查询父类对应的子类关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
-- 查询父子信息：把一张表看成两张一样的表
SELECT a.`categoryName` AS '父栏目',
b.`categoryName` AS '子栏目'
FROM category AS a, category AS b
WHERE a.categoryId = b.pId;
4.5、分页和排序
排序
-- 排序：升序ASC，降序DESC
-- ORDER BY 通过哪个字段排序，怎么排
-- 查询学生高等数学-3的成绩，结果按成绩降序排序
SELECT stu.studentNo, studentName, SubjectName, StudentResult
FROM student AS stu
INNER JOIN result AS re
ON stu.studentNo = re.studentNo
INNER JOIN `subject` AS sub
ON re.subjectNo = sub.subjectNo
WHERE SubjectName = '高等数学-3'
ORDER BY StudentResult DESC
分页
为什么要分页？
缓解数据库压力，给人的体验更好 / 瀑布流
-- 分页，每页只显示五条数据
-- 语法: limit 数据索引起始值，每页数据数
-- limit 0, 5 第1-5条数据
-- limit 5, 5 第6-10条数据
SELECT stu.studentNo, studentName, SubjectName, StudentResult
FROM student AS stu
INNER JOIN result AS re
ON stu.studentNo = re.studentNo
INNER JOIN `subject` AS sub
ON re.subjectNo = sub.subjectNo
ORDER BY StudentResult DESC
LIMIT 0, 3
-- pagesize：页面大小（每页数据数）
-- n：当前页
-- (n-1)*pagesize:起始值
-- (数据总数 + 每页数据数 - 1)/每页数据数 = 总页数
语法：limit(查询数据的起始下标, pagesize)；
4.6、子查询
where（这个值是计算出来的（原本where后是一个定值或表中已有数据））
本质：在where语句中嵌套一个子查询语句
where (select * from)
查询 “ 数据库结构-1 ” 的所有考试结果，科目编号，成绩，降序排序
-- 连表查询
SELECT studentNo, r.subjectNo, studentresult
FROM result AS r
INNER JOIN `subject` AS s
ON r.subjectNo = s.subjectNo
WHERE subjectName = '数据库结构-1'
ORDER BY studentresult DESC
-- 子查询
SELECT studentNo, subjectNo, studentresult FROM result
WHERE subjectNo = (
SELECT subjectNo FROM `subject`
WHERE subjectName = '数据库结构-1'
)
ORDER BY studentresult DESC
高等数学分数不小于80分的学生的学号和姓名
-- 连表查询+子查询
SELECT DISTINCT s.studentNo, studentName
FROM result AS r
INNER JOIN student AS s
ON r.studentNo = s.studentNo
WHERE studentresult >= 80 AND subjectNo = (
SELECT subjectNo FROM `subject`
WHERE subjectName = '高等数学-1'
)
-- 连表查询
SELECT DISTINCT s.studentNo, studentName
FROM result AS r
INNER JOIN student AS s
ON r.studentNo = s.studentNo
INNER JOIN `subject` AS sub
ON r.subjectNo = sub.subjectNo
WHERE subjectName = '高等数学-1'
-- 子查询
SELECT studentNo, studentName FROM student WHERE studentNo IN (
SELECT studentNo FROM result WHERE studentresult >= 80 AND subjectNo = (
SELECT subjectNo FROM `subject` WHERE subjectName = '高等数学-1'
)
)
练习：查询C语言-1前5名同学的成绩信息（学号，姓名，分数）
使用子查询
SELECT s.studentNo, studentName, studentResult
FROM student AS s
INNER JOIN result AS r
ON s.studentNo = r.studentNo
WHERE subjectNo = (
SELECT subjectNo FROM `subject`
WHERE subjectName = 'C语言-1'
)
ORDER BY studentResult DESC
LIMIT 0, 5
4.7、分组和过滤
-- 查询每个科目的平均分，最高分，最低分，平均分>80分-- 查询不同课程的
SELECT subjectName, AVG(studentResult) AS 平均分, MAX(studentResult), MIN(studentResult)
FROM result AS r
INNER JOIN `subject` AS sub
ON r.subjectNo = sub.subjectNo
GROUP BY sub.subjectName
HAVING 平均分 > 80
having和where的差别
where在数据分组之前进行过滤，having在数据分组之后进行过滤。where排除的行不包括在分组中，这可能会改变计算值，从而影响having子句基于这些值过滤掉的分组
4.8、总结
完整的select语法
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[AS alias1][,table.field2[AS alias2]][,...]}
FROM TABLE_NAME [AS table_alias]
[LEFT | RIGHT | INNER JOIN table_name2] -- 联表查询
[WHERE ...] -- 指定结果需要的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING ...] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[OFFSET,]ROW_COUNT | row_countOFFset OFFSET}]; -- 指定查询的数据从哪条到哪条
-- []代表可选项，{}代表必选项
select 去重 要查询的字段 from 表（表和字段可以取别名）
xxx join 要连接的表 on 等值判断
where（具体的值 / 子查询语句）
group by（通过哪个字段来分组）
having（过滤分组后的信息，条件和where是一样的，位置不同）
order by …（通过那个字段排序）[升序 / 降序]
limit startindex, pagesize
顺序很重要！
5、MySQL常用函数
5.1、常用函数（不常用）
数学运算
- abs(-8) – 绝对值
- ceiling(9.4) – 向上取整
- floor(9.4) – 向下取整
- rand() – 返回一个0-1之间的随机数
- sign() – 判断一个数的符号(0–>0,负数->-1,正数->1)
字符串函数
- char_length(‘xxxx’) – 字符串长度
- concat(‘x’,‘xx’,…) – 拼接字符串
- insert(‘xxx’,fromindex,len,‘xx’) – 从某个位置开始替换某个长度的字符串，若长度为0则为插入，下标从1开始
- lower(‘xxx’) – 转换为小写字母
- upper(‘xxx’) – 转换为大写字母
- instr(‘xxxxxx’, ‘xx’) – 返回第一次出现的字串的索引
- replace(‘xxxx’,‘from’,‘to’) – 替换出现的指定字符串
- substr(‘xxx’,fromindex,len) – 返回指定的子字符串，没有len则返回到字符串末尾
- reverse(‘xx’) – 反转
时间和日期函数
- current_date() – 获取当前时间（年月日）
- curdate() – 获取当前日期
- now() – 获取当前时间（年月日时分秒）
- localtime() – 本地时间
- sysdate() – 系统时间
- year/month/day/hour/minute/second(now())
系统
- system_user()
- user()
- version()
5.2、聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| count() | 计数 |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
| … | … |
统计表中数据
count(字段) -- 会忽略所有的null值
count(*) -- 不会忽略null值
cout(1) -- 不会忽略null值
-- 查询平均分，最高分，最低分
SELECT ANY_VALUE(subjectName), AVG(studentResult), MAX(studentResult), MIN(studentResult)
FROM result AS r
INNER JOIN `subject` AS sub
ON r.subjectNo = sub.subjectNo
-- 因为没有分组，所以结果为第一个科目的名字以及所有科目的所有分数的平均值和最大最小值
-- 查询每个科目的平均分，最高分，最低分，平均分>80分
SELECT subjectName, AVG(studentResult) AS 平均分, MAX(studentResult), MIN(studentResult)
FROM result AS r
INNER JOIN `subject` AS sub
ON r.subjectNo = sub.subjectNo
GROUP BY sub.subjectName
HAVING 平均分 > 80
5.3、数据库级别的MD5加密
MD5不可逆，相同的值的MD5是一样的
由上–>MD5破解网站原理：网站背后有一个字典存储各种密码MD5加密后的值，与需要破解的加密后密码进行对比，相同则可知其加密前密码
-- 测试MD5加密
CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 明文密码
INSERT INTO testmd5 VALUES(4,'张三','123456'), (5,'李四','1234567'), (6,'王五','12345678')
-- 加密
UPDATE testmd5 SET pwd = MD5(pwd) WHERE id = 1 -- 指定加密
UPDATE testmd5 SET pwd = MD5(pwd) -- 全部加密
INSERT INTO testmd5 VALUES (0, '小米', MD5(123)) -- 插入时加密
-- 如何校验：将用户传递进来的密码，进行md5加密，对比加密后的值
SELECT * FROM testmd5 WHERE `name` = '小米' AND pwd = MD5('123')
6、事务
6.1、什么是事务
要么都成功，要么都失败
1、SQL执行 A给B转账：A1000 —> 200 B200
2、SQL执行 B收到A钱：A800 —> B400
即将一组SQL放在一个批次中去执行！
事务原则（ACID原则）
- 原子性
原子性表示要么都成功，要么都失败，不能只发生其中一个动作
- 一致性
事务前后的数据完整性要保持一致，如转账前后两个用户账户金额总数保持不变
- 隔离性
多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事物的操作数据所干扰，事物之间要相互隔离
- 持久性
事务一旦提交则不可逆，被持久化到数据库中
如果在操作前（事务还没有提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该为
A：800，B：200
如果在操作后（事务已经提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该为
A：600，B：400
事务的隔离级别
- 脏读：指一个事务读取了另外一个事务未提交的数据。
- 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。
- 虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致（一般是行影响，如下图所示：多了一行）
6.2、测试事务实现转账
语法部分
-- mysql是自动开启事务自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启(默认设置)
-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务之内
INSERT xx
INSERT xx
-- 提交：持久化(成功)
COMMIT
-- 回滚：回到原来的样子(失败)
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
-- 了解部分
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点
实战模拟部分
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `account`(`name`,`money`)
VALUES ('A',2000.00),('B',1000.00)
SHOW VARIABLES LIKE 'autocommit'; -- 检查事务是否自动提交
-- 模拟转账事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开始一个事务
UPDATE `account` SET money=money-500 WHERE `name` = 'A'; -- A减500
UPDATE `account` SET money=money+500 WHERE `name` = 'B'; -- B加500
COMMIT; -- 提交事务
ROLLBACK; -- 回滚
SET autocommit = 1;
7、索引
7.1、索引的分类
在一个表中，主键索引只能有一个，唯一索引可以有多个
- 主键索引（PRIMARY KEY）
- 唯一的标识，主键不可重复，只能有一个字段作为主键
- 唯一索引（UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
- 常规索引（KEY/INDEX）
- 默认的，index/key关键字来设置
- 全文索引（FULLTEXT）
- 在特定的数据库引擎下才有
- 快速定位数据
基础语法
-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后增加索引
-- 显示所有索引的信息
SHOW INDEX FROM `student`;
-- 增加一个全文索引列名
ALTER TABLE school.`student` ADD FULLTEXT INDEX `studentname`(`studentname`);
-- explain分析sql的执行状况
EXPLAIN SELECT * FROM `student`; -- 非全文索引
EXPLAIN SELECT * FROM `student` WHERE MATCH(studentName) AGAINST('刘');
7.2、测试索引
-- 建表
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0' COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'
-- 定义能创建一百万条数据的函数
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'24736743@qq.com',CONCAT('18',FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i = i + 1;
END WHILE;
RETURN i;
END $$
-- 运行函数，创建一百万条数据
SELECT mock_data();
-- 执行查询语句
SELECT * FROM app_user WHERE `name` = '用户999999'; -- 耗时0.775秒
-- 分析该查询语句执行情况
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户999999'; -- 需要查询数：992349
-- 创建索引第三种方式
-- create idnex 索引名 on 表（字段）
-- 索引名命名形式：id_表名_字段名
CREATE INDEX id_app_user_name ON app_user(`name`);
-- 重新执行查询语句
SELECT * FROM app_user WHERE `name` = '用户99999'; -- 耗时0.001秒
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户99999'; -- 需要查询数：1
索引在小数据量的时候，作用不大，但是在大数据的时候，区别十分明显
7.3、索引原则
- 索引不是越多越好
- 不要给经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加载常用来查询的字段上
索引的数据结构
Hash类型的索引
Btree：InnoDB的默认数据结构
8、权限管理与备份
8.1、用户管理
SQLyog用户管理
SQL命令操作
用户表：mysql.user
本质：读这张表进行增删改查
-- 创建用户 create user 用户名 identified by '密码'
CREATE USER '陈彦亨' IDENTIFIED BY '129496';
CREATE USER 陈梓坤 IDENTIFIED BY '129496';
CREATE USER test@localhost IDENTIFIED BY '111111';
-- 修改密码（修改当前用户密码）
USER mysql;
ALTER USER '陈彦亨'@'%' IDENTIFIED BY '111111';
-- 重命名 rename user 原名 to 新名
RENAME USER 陈彦亨 TO cyh;
-- 用户授权 grant 哪些权限 on 数据库.表 to 用户（给用户授予某个库中某张表的某种权限）
-- all privileges全部的权限（除给他人授权的权限，此权限root拥有）
GRANT ALL PRIVILEGES ON *.* TO cyh;
-- root用户的权限为：grant all privileges on *.* to root with grant option
-- 查询权限
SHOW GRANTS FOR cyh; -- 创建时没有写主机名（默认%，同localhost，但是查权限时不需要写主机名）
SHOW GRANTS FOR root@localhost; -- 创建用户时带主机名查询权限若没有主机名会报错
-- 撤销权限 revoke 哪些权限 on 数据库.表 from 用户
REVOKE ALL PRIVILEGES ON *.* FROM cyh;
-- 删除用户
DROP USER 陈梓坤;
8.2、MySQL备份
为什么要备份：
- 保证重要的数据不丢失
- 数据转移
MySQL数据库备份的方式
- 直接拷贝物理文件
- 在SQLyog这种可视化工具中手动导出
- 在想要导出的表或数据库中，右键，选择备份/导出
- 使用命令行的方式备份或导入
#备份
# mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >d:/a.sql
# mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school stdent result >d:/b.sql
# 导入
# 登录的情况下，切换到指定的数据库，如果导入数据库则不需要
# source 备份文件
source c:/desktop/a,sql
# 未登录状态下
# mysql -u用户名 -p密码 库名 <物理磁盘位置/备份文件
9、规范数据库设计
当数据库比较复杂时则需要设计
糟糕的数据库设计：
- 数据冗余，浪费空间
- 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计：
- 节省内存空间
- 保存数据库的完整性
- 方便我们开发系统
软件开发中，关于数据库的设计：
- 分析需求：分析业务和需要处理的数据库的需求
- 概要设计：设计关系图E-R图
9.1、设计数据库的步骤
以个人博客为例
-
搜集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 评论表
- 友链表（友链信息）
- 自定义表（系统信息，某些关键的字，或者一些主字段）key：value
-
标识实体（把需求落地到每个字段）
-
标识实体之间的关系
- 写博客：user–>blog
- 创建分类：user->category
- 关注：user->user
- 友链：links
- 评论：user-user-blog
9.2、三大范式
为什么需要数据规范化
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式
第一范式
原子性：保证每一列不可再分
第二范式
前提：满足第一范式
每张表只描述一件事情
第三范式
前提：满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关
规范性和性能的问题（空间与时间的取舍）
关联查询的表不得超过三张表
- 考虑商业化的需求和目标（成本，用户体验），数据库的性能更加重要
- 在规范性能的问题的时候，需要适当考虑一下规范性
- 故意给某些表增加一些冗余的字段（从多表查询变为单标查询）
- 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）
QL数据库备份的方式
- 直接拷贝物理文件
- 在SQLyog这种可视化工具中手动导出
- 在想要导出的表或数据库中，右键，选择备份/导出
- 使用命令行的方式备份或导入
#备份
# mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >d:/a.sql
# mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school stdent result >d:/b.sql
# 导入
# 登录的情况下，切换到指定的数据库，如果导入数据库则不需要
# source 备份文件
source c:/desktop/a,sql
# 未登录状态下
# mysql -u用户名 -p密码 库名 <物理磁盘位置/备份文件
9、规范数据库设计
当数据库比较复杂时则需要设计
糟糕的数据库设计：
- 数据冗余，浪费空间
- 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计：
- 节省内存空间
- 保存数据库的完整性
- 方便我们开发系统
软件开发中，关于数据库的设计：
- 分析需求：分析业务和需要处理的数据库的需求
- 概要设计：设计关系图E-R图
9.1、设计数据库的步骤
以个人博客为例
-
搜集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 评论表
- 友链表（友链信息）
- 自定义表（系统信息，某些关键的字，或者一些主字段）key：value
-
标识实体（把需求落地到每个字段）
-
标识实体之间的关系
- 写博客：user–>blog
- 创建分类：user->category
- 关注：user->user
- 友链：links
- 评论：user-user-blog
9.2、三大范式
为什么需要数据规范化
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式
第一范式
原子性：保证每一列不可再分
第二范式
前提：满足第一范式
每张表只描述一件事情
第三范式
前提：满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关
规范性和性能的问题（空间与时间的取舍）
关联查询的表不得超过三张表
- 考虑商业化的需求和目标（成本，用户体验），数据库的性能更加重要
- 在规范性能的问题的时候，需要适当考虑一下规范性
- 故意给某些表增加一些冗余的字段（从多表查询变为单标查询）
- 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）