MySQL（一）

文章目录

• ​​1、初始MySQL​​

• ​​1.1、概述​​
• ​​1.2、数据库分类​​
• ​​1.3、MySQL安装​​
• ​​1.4、安装可视化工具​​
• ​​1.5、相关的SQL语句​​

• ​​2、操作数据库​​

• ​​2.1、操作数据库​​
• ​​2.2、数据库与的列类型​​
• ​​2.3、数据库的字段属性​​
• ​​2.4、创建数据库表​​
• ​​2.5、数据表的类型​​
• ​​2.6、修改删除表​​

• ​​3、MySQL数据管理​​

• ​​3.1、外键（了解）​​
• ​​3.2、DML语言（重点）​​
• ​​3.3、添加​​
• ​​3.4、修改​​
• ​​3.5、删除​​

• ​​4、DQL（重点）​​

• ​​4.1、DQL​​
• ​​4.2、指定查询字段​​
• ​​4.3、where条件子句​​
• ​​4.4、联表查询​​
• ​​4.5、分页和排序​​
• ​​4.6、子查询​​
• ​​4.7、过滤和分组​​

1、初始MySQL

1.1、概述

数据库：DataBase

作用：存储数据，管理数据

最开始是瑞典的MySQL公司的产品，如今是Oracle旗下产品，。MySQL是最好的RDBMS（Relational Database Management System，关系数据库管理系统）应用软件之一

体积小、速度快、总体拥有成本低，招人成本低

适用于中小型网站，甚至是部分大型网站（利用集群）。

官网：​​https://www.mysql.com​​

1.2、数据库分类

关系型数据库：（SQL）

• MySQL、Oracle、SqlServer、DB2、SQLlite

非关系型数据库：（NoSQL）

• Redis、MongDB

1.3、MySQL安装

尽量使用压缩包安装mysql（.exe方式安装后卸载比较麻烦）、

安装步骤：

1. 下载相应版本的MySQL压缩包到本地
2. 将压缩包解压到我们未来希望的它在的位置
3. 配置环境变量（类似于java，我们希望能全局启动）
4. 在MySQL的目录下新建MySQL的配置文件my.ini（可以理解为使用.exe方式安装时的那些步骤，我们直接用配置文件替代）

[mysqld]

#基础目录
basedir=E:\mysql-5.5.25-winx64\

#data目录会在后期自动生成
datadir=E:\mysql-5.5.25-winx64\data\

#mysql对应的端口号
port=3306

#设置跳过密码，我们第一次进入mysql，可以修改密码，然后再注释掉这个语句
skip-grant-tables

1. 以管理员身份运行CMD，并切换到MySQL文件的bin目录下
2. 输入mysqld -install命令，安装mysqld，安装成功会出现Service successfully installed提示
3. 再输入命令mysqld --initialize-insecure --user=mysql，用于初始化我们的数据库
4. 输入命令net start mysql，用于启动我们的MySQL服务
5. 登录我们的MySQL服务，使用mysql -u root -p命令（切记，p后面不要添加空格）
6. 在输入密码时直接按回车
7. 输入相应的sql语句修改我们的密码（sql语句需要带上对应的 ；）

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';

1. 修改密码以后，使用sql语句==flush privileges;==刷新权限
2. 注释掉my.ini配置文件中的跳过密码的语句skip-grant-tables
3. 重启MySQL，即可使用

补充：

• 开启MySQL服务：net start mysql
• 关闭MySQL服务：net stop mysql
• 清空MySQL服务（如果安装出错，可以重头再来）：sc delete mysql

1.4、安装可视化工具

navicat

当然这种可视化工具比较多，差不多就行了，安装起来也比较简单

使用可视化工具新建数据库和表步骤：

1. 连接我们的MySQL
2. 输入相关的信息
3. 右键点击我们已经连接上的数据库，选择新建数据库
4. 设置我们新建的数据信息
5. 添加表的字段及表名
6. 我们可以查看到对应的SQL片段

CREATE TABLE `school`.`Untitled`  (
  `id` int(10) NOT NULL COMMENT '学生id',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生姓名',
  `age` int(3) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

1. 当然你也可以通过查看navicat的日志来查看我们的SQL片段
2. 查看我们创建的表

1.5、相关的SQL语句

mysql -uroot -p123456  --登录对应的数据库

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';  --修改密码

flush privileges; --刷新权限

------------

show databases;  --查看所有的数据库

use school;  --使用school数据库

show tables;  --查看所有的数据库中所有的表

describe student;  --获取数据库中student表的所有信息

create database westos;  --创建一个名为westors的数据库

exit;  --退出连接

--单行注释
/*多行注释*/

数据库xxx语言：

• DDL 定义
• DML 操作
• DQL 查询
• DCL 控制

2、操作数据库

操作数据库 --> 操作数据库中的表 --> 操作数据库中表的数据

2.1、操作数据库

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] shool

2、删除数据库

DROP DATABASE [IF NOT EXISTS] school

3、使用数据库

USE school

4、查看数据库

SHOW DATABASES

补充：

如果我们的表名或者字段名，是一个特殊字符，我们需要带上 ` 。举个例子，我们需要创建一个名为user的表，但是我们的MySQL系统里面就有一个user表，那么我们在使用的时候，就可以使用 ` 符号，用来区分我们输入的是关键字的user还是我们自己的user

我们在使用的sql语句的时候，可以对照我们的历史日志来学习记忆

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 保存大文本

时间日期

• date 日期格式 YYYY-MM-DD
• time 时间格式 HH:mm:ss
• datetime 日期+时间 YYYY-MM-DD HH:mm:ss 常用的时间格式
• timestamp 时间戳 1970.1.1到现在的毫秒数 较常用的时间格式
• year 年份

null

• null，没有值 不要使用null进行运算

2.3、数据库的字段属性

unsigned

• 无符号的整数，表明该列的数字不会是负数

zerofill

• 0填充，不足的位数使用0来填充，如0001

自增autoincrement

• 自动在上有一条记录的基础上+1
• 通常用来设计表唯一的主键，必须是整数类型
• 可以自定义主键自增的起始位置和自增的步长

非空 not null

• 假如我们定义了非空，不赋值就会报错

补充

未来我们的数据库的字段会按照阿里巴巴开发规范进行，那么就需要添加几个其他的字段：

• id：主键
• version：用于乐观锁
• is_delete：用于伪删除（有这个字段当作一个标志位，数据被删除的时候不是真的删除，而是改变标志位）
• gmt_create：创建时间
• gmt_update：更新时间

2.4、创建数据库表

--创建一个指定数据库下面的表
CREATE TABLE [IF NOT EXISTS] `school`.`student1`  (
-- 字段 数据类型 不能为null 备注
  `id` int(10) NOT NULL COMMENT '学生id',
  `name` varchar(255) NOT NULL COMMENT '学生姓名',
  `age` int(3) NOT NULL,
-- 将id属性设置为主键
  PRIMARY KEY (`id`)
-- 设置数据库的引擎和字符编码
)ENGINE=INNODB DEFAULT CHARSET=utf8

注意：这里一定要明确 ` 和 ’ ，注它们的状态以及使用位置。字段使用 ` ，配置使用 ’ 。

格式：

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

其他命令

-- 查看创建数据库的语句
SHOW CREATE DATABASE school
-- 查看创建对应表的语句
SHOW CREATE TABLE student
-- 显示表的结构
DESC student

2.5、数据表的类型

数据表类型的比较：

常规使用操作：

• MYISAM：节约成本，速度较快
• INNODB：安全性高，事务的处理，多表多用户操作

所有的数据库文件都存在我们的data目录下，一个文件对应一个数据库，所以说，数据库存储的本质还是文件存储。

MySQL的两个不同的引擎在物理文件上的区别

• InnoDB：在数据库表中只有一个*.frm文件，以及一个上级目录下的ibadata1文件（较大）
• MYISAM：对应的文件

• *.frm：表结构的定义文件
• *.MYD：数据文件（data）
• *.MYI：索引文件（index）

设置数据库表的字符集编码：CHARSET=utf8

• 如果不手动设置编码的话，会使用mysql默认的字符集编码Latinl（不支持中文）
• 当然我们也可以在配置文件my.ini中直接设置我们的默认字符集编码为utf8，但是不建议在物理层面使用。发生表迁移时容易出问题

2.6、修改删除表

1、修改表名

--ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student2 RENAME AS student3

2、增加表字段

--ALTER TABLE 表名 ADD 添加字段 字段类型
ALTER TABLE student3 ADD age INT(11)

3、修改表的字段（字段，字段的属性）

--ALTER TABLE 表名 MODIFY 字段名 字段属性
ALTER TABLE student3 MODIFY age VARCHAR(22)

--ALTER TABLE 表名 CHANGE 旧字段 新字段 新字段属性
ALTER TABLE student3 CHANGE age age1 INT(33)

补充：

modify：用于修改字段的属性

change：可以用于修改字段的属性，也可以用于直接修改字段，一般使用于后者

4、删除表的字段

--ALTER TABLE 表名 DROP 表中的字段
ALTER TABLE student3 DROP age1

5、删除表

--DROP TABLE IF EXISTS 表名
DROP TABLE IF EXISTS student3

所有的创建和删除操作，尽量加上IF EXISTS

3、MySQL数据管理

3.1、外键（了解）

创建物理外键（数据库级别）

方式一：创建表的时候进行

--创建一个学生表，并且将gradeid设置为外键
CREATE TABLE IF NOT EXISTS `school`.`student2`  (
   `id` int(10) NOT NULL COMMENT '学生id',
   `name` varchar(255) NOT NULL COMMENT '学生姓名',
   `age` int(3) NOT NULL,
    `gradeid` INT(10) NOT NULL COMMENT '学生年级',
    PRIMARY KEY (`id`),
    KEY `FK_gradeid` (`gradeid`),
   CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

--创建一个年级表
CREATE TABLE IF NOT EXISTS `school`.`grade`  (
   `gradeid` int(10) NOT NULL COMMENT '年级id',
   `gradename` varchar(255) NOT NULL COMMENT '年级名称',
   PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

此时，如果我们先去删除表grade，就会提示存在外键引用无法删除。我们应先删除student2表（从表），再删除表grade表（主表）

方式二：表创建完以后再添加

--创建两张没有外键约束的表格
CREATE...
CREATE...

--最后再来添加外键依赖
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)

以上操作增强了表与表之间的耦合，不建议使用。

在我们实际的使用时，可以使用程序去实现外键

3.2、DML语言（重点）

数据库意义：数据存储，数据管理

DML语言：数据库操作语言

• insert
• update
• delete

3.3、添加

insert

语法格式：

insert into 表名([字段名1],[字段名2],[字段名3]) value (‘值11’,‘值12’,‘值13’)

--插入语句（添加）
--格式：insert into 表名([字段名1],[字段名2],[字段名3]) value ('值11','值12','值13').('值21','值22','值23')
--每一个插入值得括号可以类比为一个java对象
INSERT INTO `grade`(`gradeid`,`gradename`) VALUES ('4','大四'),
                          ('3','大三');
                          
INSERT INTO `grade`VALUES ('4','大四'),
              ('3','大三');

总结

• 字段和字段之间使用英文得逗号分隔开
• 字段是可以省略的，但是后面的值必须要一一对应，否则就会出错。
• 可以同时插入多条数据，同一组数据，需要放在同一个括号里面。不同组的数据，需要使用，分隔开

3.4、修改

update

语法格式：

UPDATE 表名 SET column_name = value,[column_age = value …] WHERE [条件]

--修改学生的名字，带条件。只会修改指定的那一句
UPDATE `student2` SET `name` = 'mobian' WHERE id = 1;

--修改学生的名字，不带条件。会修改所有表的name字段的value
UPDATE `student2` SET `name` = '默辨'

--修改多个属性，使用逗号分隔
UPDATE `student2` SET `name` = 'mobian',`age`='22' WHERE id = 1;

条件：where子句，运算符 id 等于某个值，大于某个值，在某个区间内修改

使用详情：

修改的value可以是一个具体的值，也可以是一个变量

UPDATE `student2` SET `birthday` = CURRENT_TIME WHERE `id` = 1;

3.5、删除

delete

语法格式：

DELETE FROM 表名 [WHERE 条件]

--直接删除一张表
DELETE FROM `student2`

--删除一张表的指定数据
DELETE FROM `student2` WHERE id = 1;

TRUNCATE

--清空student2表
TRUNCATE `student2`

DELETE 和 TRUNCATE 异同点

相同点：

• 都能删除数据，都不会删除表的结构

不同点：

• TRUNCATE 删除表以后会重新设置自增列的计数器，不影响事务
• DELETE仅仅是删除了数据内容，计数器没有变化

补充：

DELDETE的删除问题，重启数据库以后的现象

• InnoDB：自增列会重1开始（存在内存中的，断电即失）
• MyISAM：继续从上一个自增量开始（存在文件中的，不会丢失）

4、DQL（重点）

数据库查询语言（Data Query Language）

4.1、DQL

• 所有的查询操作都需要是使用它
• 查询又分为简单的查询和复杂的查询
• 数据库中最核心的语言
• 使用频率最高

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}] --分页查询，指定查询的记录从哪条到哪条

[]：代表可选

{}：代表必选

4.2、指定查询字段

SELECT 表达式 FROM 表

简单的用法

--查询指定表中的所有信息
SELECT * FROM student

--查询指定的表中的指定字段
SELECT `name` FROM student

--给指定表以及查询出来的字段起别名
SELECT `name` AS 学生姓名 FROM student AS s

--使用函数CONCAT(a, b)，会在查询出来的字段内容前添加指定内容
SELECT CONCAT('姓名：',`name`) AS 学生姓名 FROM student

去重 DISTINCT

作用：去除SELECT查询出来的结果中的数据，重复的数据只显示一条

--将查询出来的数据进行去重
SELECT DISTINCT `name` FROM `student`

数据库相关的表达式

SELECT VERSION() --查询我们系统的版本信息
SELECT 100*2+2 --利用我们的select进行数学计算
SELECT @@AUTO_INCREMENT_INCREMENT --查询自增的步长

SELECT `age`+1 AS `新年龄` FROM `student` --将学生表中年龄字段+1

数据库中的表达式：文本值、列、NULL、函数、计算表达式、系统变量…

4.3、where条件子句

作用：检索数据中符合条件的值

搜索的条件由一个或多个表达式组成，返回布尔值

逻辑运算符

使用方式与前面的update相同

补充not使用

SELECT* FROM `student2` WHERE not id = 1

比较运算符：模糊查询

使用方式：

--查询学生学生表中id不为null的学生信息
SELECT * FROM `student2`
WHERE id is not NULL

--查询学生表中id在1和2之间的学生信息
SELECT * FROM `student2`
WHERE `id` BETWEEN 1 and 2

--查询名字中，共三个字符且中间为6的名字的学生信息
SELECT * FROM `student2`
WHERE name LIKE '_6_'

--查询名字中，包含6的名字的学生信息
SELECT * FROM `student2`
WHERE name LIKE '%6%'

--查询学生表中id为1，2的学生信息
SELECT * FROM `student2`
WHERE id in(1,2)

4.4、联表查询

JOIN

如何理解这个左右值得查询？

当我们在查询得时候，希望以哪一个表为底表。右连接的时候，以右边的表为底表，当另一个表中的条件匹配的时候，就返回对应的数据，但是数据的信息是在底表的基础上进行修改的。反之则为左连接。

当我们需进行某些联表查询的时候：

/*
1.分析我们希望查询的结果包含哪些字段
2.我们的字段分别来自哪些表
3.确定我们使用哪一种查询（7种）
*/


-- join(连接的表) on (判断的条件) 连接查询
-- from a left join b on a.id = b.id
-- where 等值查询

SELECT s.studentNum,studentName
FROM student s
RIGHT JOIN course c
ON s.studentNum = c.studentNum

自查询

将一张表，假想成两张表，我们查询的时候，使用的条件都是来自一张表。类似于在自己类部进行细分的查找。

4.5、分页和排序

排序

SELECT * FROM `student2`
ORDER BY age ASC --升序排列

SELECT * FROM `student2`
ORDER BY age DESC --降序排列

分页

SELECT * FROM `student2`
ORDER BY age DESC
-- LIMIT 其实位置， 页面大小
LIMIT 1,2

4.6、子查询

where后面的条件为一个select语句

本质：在where语句中嵌套一个子查询语句（由里到外以此执行）

SELECT * FROM `student2`
WHERE `id` in (
  SELECT id FROM `student1`
)

改写成联表查询：

SELECT id 
FROM `student2` AS s1
INNER JOIN `student1` AS s2
ON s1.id = s2.id

两者根据自己的喜好使用。

联表查询：联合多个表一起查询

子查询：在自己本身的查询语句里面，再书写一个查询语句

4.7、过滤和分组

SELECT * FROM `student2`
GROUP BY `name` --分组（根据指定的字段，将表的内容进行分组，可用配合平均数、最大值...使用）
--where不能与GROUP BY 联和使用
--WHERE age >12

--我们这里应该使用HAVING关键字
HAVING age >12