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 changed

show 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 musichouse

SELECT `user` FROM student

关键字不区分大小写

2.2 数据库的列类型

数值

字符串

时间日期

java.util.Data

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=utf8

CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名` 列类型[属性] [索引] [注释],
	...
	`字段名` 列类型[属性] [索引] [注释],
)[表类型][字符集设置][注释]

常用命令

SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显是表的结构

2.5 数据表的类型

-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/

常规使用操作：

• 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’)
注意事项：

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;

语法：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条件句

模糊查询：比较运算符