MySQL复习笔记
MySQL复习笔记
1.MySQL
1.1什么是数据库
数据库(DB, DataBase)
概念：数据仓库，软件，安装在操作系统（window、linux、mac…）之上
作用：存储数据，管理数据
1.2 数据库分类
关系型数据库：（SQL）
- MySQL，Oracle，Sql Server，DB2…
- 通过表和表之间，行和列之间的关系进行数据的存储
非关系型数据库：（Not Only SQL）
- Redis，MongDB
- 非关系型数据库，对象存储，通过对象自身的属性来决定
DBMS(数据库关系系统)
- 数据库的管理软件，科学有效地管理数据
1.3 MySQL简介
MySQL是一个关系型数据库管理系统
前世：瑞典MySQL AB 公司
今生：属于Oracle旗下产品
MySQL是最好的RDBMS（关系型数据库）应用软件之一
开源的数据库软件
体积小、速度快、总体拥有成本低
中小型网站、或者大型网站，集群
version：5.7、8.0
1.4连接数据库
所有语句使用；结尾
show databases；-- 查看所有数据库mysql>use school -- 切换数据库 use 数据库名
Database changedshow tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中所有的表的信息create database westos; -- 创建一个数据库exit; --退出连接-- 单行注释
/*（多行注释）
*/
数据库语言 CRUD CV API
DDL - 定义
DML - 操作
DQL - 查询
DCL - 控制
2. 操作数据库
2.1 操作数据库
操作数据库 > 操作数据库中的表 > 操作数据库中表的数据
1、创建数据库
CREATE DATABASE IF NOT EXISTS musichouse
2、删除数据库
DROP DATABASE IF EXISTS musichouse
3、使用数据库
-- 如果表名或字段名是个特殊字符需要加上`name`
USE `musichouse`
USE musichouseSELECT `user` FROM student
关键字不区分大小写
2.2 数据库的列类型
数值
| tinyint | 十分小的数据 | 1个字节 |
| smallint | 较小的数据 | 2个字节 |
| mediumint | 中等的数据 | 3个字节 |
| int | 标准的整数 | 4个字节（常用） |
| bigint | 较大的数据 | 8个字节 |
| float | 浮点数 | 4个字节 |
| double | 浮点数 | 8个字节 |
| decimal(金融计算) | 字符串形式的浮点数 | 精度问题 |
字符串
| char | 字符串固定大小 | 0~255 |
| varchar | 可变字符串 | 0~65535（常用 - String） |
| tinytext | 微型文本 | 2^8 -1 |
| text | 文本串 | 2^16 - 1（保存大文本） |
时间日期
java.util.Data
| date | YYYY-MM-DD | 日期格式 |
| time | HH : mm : ss | 时间格式 |
| dataime | YYYY-MM-DD HH : mm : ss | 最常用的时间格式 |
| timestamp | 时间戳 | 1970.1.1到现在的毫秒数 |
| year | 年份表示 |
null
- 没有值，位置
- 注意，不要使用NULL进行运算，结果为NULL
2.3 数据库的字段属性（重点）
Unsigned:
- 无符号的整数
- 声明了该列 不能声明为负数
zerofill：
- 0填充
- 不足的位数，使用0填充，int(3) 5 - > 005
自增：
- 通常理解为自增，自动在上一条记录的基础上+1（默认）
- 通常用来设计唯一的主键 - index，必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空：NULL not null
- 假设设置为 not null，如果不赋值，就会报错！
- NULL，如果不填写值，默认就是null！
默认：
- 设置默认的值
- gender 默认为男，如果不指定该列的值，则会有默认的值
扩展：
/* 每一个表，都必须存在以下五个字段！未来做项目用的，表示一个记录存在意义
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
2.4 创建数据库表（重点）
CREATE TABLE IF NOT EXISTS `studnet`(`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',`name` VARCHAR(30) DEFAULT '匿名' COMMENT '姓名',`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',`gender` 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=utf8CREATE TABLE [IF NOT EXISTS] `表名`(`字段名` 列类型[属性] [索引] [注释],...`字段名` 列类型[属性] [索引] [注释],
)[表类型][字符集设置][注释]
常用命令
SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显是表的结构
2.5 数据表的类型
-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大，约为MYSIAM的 2 倍 |
常规使用操作：
- MYISAM：节约空间，速度较快
- INNODB：安全性高，支持事务的处理，多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在 data 目录下，一个文件夹就对应一个数据库
本质还是文件的存储
MySQL 引擎在物理文件上的区别
- InnoDB 在数据库表中只有一个 *.frm 文件，以及上级目录下的ibdata1文件
- MYISAM 对应文件
- *.frm 表结构的对应文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
设置数据库表的字符集编码
CHARSET = utf8
不设置的话，会是mysql默认的字符集编码（不支持中文）
MySQL的默认编码是Latin1，不支持中文
在my.ini 中配置默认的编码
character-set-server=utf8
2.6 修改数据表
修改
-- 修改表名alter table 旧表名 rename as 新表明
ALTER TABLE teacher RENAME AS teacher1-- 增加表的字段 alter table 表名 add 字段名 列属性
ALTER TABLE teacher ADD age INT(11)-- 修改表的字段（重命名，修改约束！）
-- alter table 表名 modify 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age 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创建和删除操作尽量加上判断，以免报错
注意点
- `` 字段名使用`字段名`包裹
- 注释 – /* */
- sql关键字大小写不敏感
3. MySQL 数据管理
3.1 外键
方式一、创建表的时候添加约束，比较复杂
CREATE TABLE IF NOT EXISTS `grade`(`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年纪id',`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8-- 学生表的 gradeid 字段，要去引用年纪表的gradeid
-- 1.定义外键key
-- 2. 给这个外键添加约束（执行引用） references 引用
CREATE TABLE IF NOT EXISTS `student`(`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',`name` VARCHAR(30) DEFAULT '匿名' COMMENT '姓名',`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',`gender` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',`gradeid` INT(10) NOT NULL COMMENT '学生年级id',`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',`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删除有外键关系的表时，必须要先删除引用别人的（(从表），才能删除被引用的表（主表）
方式二、创建表后增加约束
-- 创建表的时候没有外键关系
ALTER TABLE `studnet`
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
-- 插入语句（添加）
-- insert into 表名(字段名1,字段名2,字段3)values('值1','值2','值3')
-- 由于主键自增，我们可以省略（如果不写表的字段，它会一一匹配）
INSERT INTO `grade` (`gradename`)VALUES('大一');
INSERT INTO `grade` (`gradename`)VALUES('大二');
INSERT INTO `grade` (`gradename`)VALUES('大三');
INSERT INTO `grade` (`gradename`)VALUES('大四');-- 一般写插入语句，要保证数据和字段一一对应
INSERT INTO `studnet`(`name`,`gender`,`address`,`gradeid`)
VALUES('frank','男','LA','1'),('sean','男','NY','1'),('JOHN','男','CA','1');
insert into 表名(字段名1,字段名2,字段3)values(‘值1’,‘值2’,‘值3’)
注意事项：
- 字段和字段之间使用 , 隔开
- 字段可以省略，但是值要一一对应
- 可以同时插入多条数据，values后面的值要用,隔开
3.4 修改 update
update 修改谁 (条件) set 原来的值 = 新值
-- 修改名字
UPDATE `student` SET `name`='steve' WHERE id = 1;
-- 如果不指定条件，会改动所有表
UPDATE `student` SET `name`='steve';、-- 语法
-- update 表名 set colnum_name = value where 条件;-- 修改多个属性，用,隔开
UPDATE `student` SET `name` = 'john',`gradeid`=3 WHERE id = 3;
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5-6 | false |
| <> 或 != | 不等于 | 5<>6 | true |
| > | |||
| < | |||
| <= | |||
| >= | |||
| BETWEEN… AND … | 在某个范围内 | [2,5] WHERE id BETWEEN 2 AND 5 | |
| AND | 我和你&& | 5>1 AND 1>2 | flase |
| OR | 我或你 | 5>1 OR 1>2 | true |
语法：update 表名 set colnum_name = value.[colnum_name = value] where 条件；
注意：
- colnum_name 是数据库的列，尽量带``
- 条件，筛选的条件，如果没有指定，则会修改所有的列
- value，是一个具体的值，也可以是一个变量
3.5 删除 delete
delete 命令
语法：delete from 表明 where 条件;
-- 删除数据
DELETE FROM `student` WHERE id = 3;
TRUNCATE 命令
作用：完全清空一个数据库表，表的结构和索引约束不会变
-- 清空 student 表
TRUNCATE `student`
delete和TRUNCATE区别
- 相同点：都能删除数据，并且不会删除表结构
- 不同点：
- TRUNCATE 重新设置 自增列 计数器会归零
- TRUNCATE 不会影响事务
- TRUNCATE 自增会归零，DELETE不会影响自增
DELETE删除的问题，重启数据库
- InnDB 自增列会从1开始（存在内存中的，断电即失）
- MyISAM 继续从上一个自增量开始（存在文件中的，不会丢失）
4. DQL查询数据（重点）
4.1 DQL
(Data Query LANGUAGE：数据查询语言)
- 所有的查询操作都用它 Select
- 简单的查询，复杂的查询都能做
- 数据库中最核心的语言
- 使用频率最高的语句
4.2 指定查询某个字段
-- SELECT 字段 FROM 表SELECT * FROM student-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student-- 别名，给结果起一个名字 AS 也可以给字段起别名，也可以给表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS s-- 函数 拼接字符串Concat(a,b)
SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM student
有时，列名意思不清晰需要用别名，AS 别名
去重 distinct
作用：去除 select 查询结果中重复的数据，重复数据只显示一条
-- 查询哪些同学参加了考试，成绩SELECT * FROM result SELECT `studentNo` FROM result -- 重复数据
SELECT DISTINCT `studentNo` FROM result
数据库的列
SELECT VERSION() -- 查询系统版本(函数)
SELECT 100*3-1 AS 计算结果 -- 用来计算(表达式)
SELECT @@auto_increment_increment -- 查询自增的步长-- 学生考试成绩 + 1分查看
SELECT `studentNo`,`StudentResult` + 1 AS '提分后' FROM result
数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量…（select选则的就是表达式）
4.3 Where条件句
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b | 逻辑与 |
| or || a or b | 逻辑或 | |
| not ! | not a ! a | 逻辑非 |
模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | a is null | 如果操作符为null，结果为真 |
| IS NOT NULL | a is not null | 如果操作符不为 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…其中的某一个值中，结果为真 |
-- 模糊查询
-- 查询姓刘的同学
-- like 结合 %（代表0到任意个字符） _（一个字符）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);-- 查询在北京的学生
SELECT `studentNo`,`StudentName` FROM `student`
WHERE `address` IN ('北京','河南');-- null、not null
-- 查询地址为空的学生
SELECT `studentNo`,`StudentName` FROM `student`
WHERE `address` = '' OR address IS NULL-- 查询有出生日期的同学 -> 不为空
SELECT `studentNo`,`StudentName` FROM `student`
WHERE `bornDate` IS NOT NULL
4.4 联表查询
JOIN 对比
-- 联表查询 join-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student/*
思路
1.分析需求，分析用查询的字段来自哪些表（连接查询）
2.确定使用哪种连接查询？7种
确定交叉点（这两个表种哪个数据是相同的）
判断的条件：学生表的StudentNo = 成绩表的StudentNo
*/
SELECT s.studentNo,studentName,subjectNo,studentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo = r.studentNo-- RIGHT join
SELECT s.studentNo,studentName,subjectNo,studentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNo = r.studentNo-- LEFT join
SELECT s.studentNo,studentName,subjectNo,studentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo-- join(连接的表) on(判断的条件) 连接查询
-- where 等值查询-- 查询缺考的同学
SELECT s.studentNo,studentName,subjectNo,studentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo
WHERE studentResult IS NULL-- join on 连接查询
-- where 等值查询-- 查询参加考试的同学信息：学号、姓名、科目名、分数（三个表SELECT s.studentNo,`studentName`,`subjectName`,`studentResult`
FROM student AS s
RIGHT JOIN result AS r
ON r.studentNo = s.studentNo
INNER JOIN `subject` AS sub
ON r.subjectNo = sub.subjectNo/*核心
查哪些数据 select ...
从哪几个表中查 from 表 xxx join 连接的表 on 交叉条件
假设存在一种多张表查询，慢慢来，先查询两张表，然后再慢慢增加FROM a LEFT JOIN b
FROM a RIGHT JOIN b
*/| 操作 | 描述 |
|---|---|
| inner join | 如果表种至少有一个匹配，就返回行 |
| left join | 即使右表没有匹配，也会从左表中返回所有的值 |
| right join | 即使左表没有匹配，也会从右表中返回所有的值 |