【MySQL】 基础知识 详细版（上）

两天学完MySQL

今天肝了一天学了MySQL，本来打算一天学完的，但是放假了还是会有各种乱七八糟的事情。只能分成两天来学习。关于MySQL 的安装还是比较简单的，主要是我安装了就懒得再截图记录了，大家不要用msi文件安装，直接cmd安装比较方便，初学者还能熟悉一下cmd操作。博客里面大部分都是使用了SQLyog，这可以对数据库进行可视化操作。

1 连接数据库

打开cmd 不熟悉的小伙伴 快捷键 ：win+r 输入cmd shift +ctrl+enter是以管理员的身份启动

mysql -uroot -p --按回车可以不显示密码

mysql -uroot -p密码  --直接输入密码

flush privileges;--刷新权限
--------------------------------------------
--所有语句都是;结尾
show databases; --展示全部数据库

use 数据库名 --使用数据库

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

describe 表名; --显示数据库中表的信息

create database 数据库名; --创建一个数据库

exit; --退出链接

--单行注释
/*
*/

2 操作数据库

2.1 操作数据库（了解）

创建数据库

create database if not exists 数据库名

删除数据库

drop database if exists 数据库名

如果你的表名或者字段名是个特殊字符，就需要带`` 之后的字段和表都会带上以免不必要的麻烦

使用数据库

use 数据库名 --如果你的表名或者字段名是个特殊字符，就需要带``

查看数据库

 show databases --查看所有数据库

学习思路：

1. 对照sqlyog可视化历史记录，我们再sqlyog上进行操作的时候，历史记录上都会有对应的代码。基此我们可以对照学习

2. 固定的语法或关键词必须要记住

2.2 数据库的列类型

---

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小的数据 3个字节
• int 标准的整数 4个字节 常用的
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节
• decimal 字符串的类型浮点数 大数类型 金融计算的时候，一般使用 decimal

---

字符串

• char 字符串固定大小 0-255
• varchar 可变字符串 0~65535 常用的
• 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：

• 无符号的整数
• 表示该列不能为负数

zeorfill：

• 0填充的
• 不足的位数，使用0来填充 int（3）， 5—005；

自增：

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

非空(NULL或 NOT NULL)：

• 如果不填写值就会报错
• NULL，如果不填写，默认就是null;

默认：

• 设置默认的值
• sex 默认值为男，如果不指定该列的值，那么指定为男

拓展

每个表都必须存在一下五个字段！未来做项目用的，表示一个记录存在意义。这是阿里巴巴规范手册上的，现阶段了解即可

id 主键

`version`  乐观锁

is_delete   伪删除

gmt_create 创建时间

gmt_update 修改时间

2.4 使用sql创建一张表格

-- 目标创建school数据库里面的student表格
-- 注意点，使用英文(),表的名称和字段 尽量使用`` 括起来
-- AUTO_INCREMENT 自增
-- 字符串的使用 单括号加起来
-- PRIMARY KEY 主键，一般一个表只有唯一的主键

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 '123435' 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

SHOW CREATE DATABASE school --查看创建数据库的语句
SHOW CREATE TABLE student --查看student数据表的定义语句
DESC student --显示表的结构
-- 这就是逆向操作

2.5 数据表的类型

关于数据库引擎

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大

常规使用操作

• MYISAM 节约空间 ，速度较快
• INNODB 安全性高，支持事务的处理

在物理空间存在的位置

所有数据库文件都存在data目录下

本质还是文件的存储

MYSQL 引擎在物理文件上的区别

• INNODB 在数据库表中只有一个 *frm文件，以及上级文件下innodb文件
• MYISAM 对应的文件
  • *.frm 表结构的定义文件
  • *.MYD 数据文件
  • .MYI 索引文件

设置数据表的字符集编码

CHARSET=utf8

不设置的话，会是mysql默认的字符集编码（不支持中文）！

MYSQL的默认编码时Latin1，不支持中文

可以在my.ini中配置默认的编码

character-set-server=utf8

但是还是在建表的时候加上CHARSET=utf8，否则这个表在别人电脑上可能就是乱码。

2.6 修改删除 表

修改表

--修改表名    旧表名             新表名
ALTER TABLE teacher RENAME AS teacher1

-- 增加表的字段
ALTER TABLE teacher1 ADD age INT(11)

-- 修改表的字段名称（重命名，修改约束）
ALTER TABLE teacher1 MODIFY age VARCHAR (11)--修改约束
ALTER TABLE teacher1 CHANGE age age1 INT(1) --字段重命名

-- 删除表的字段
ALTER TABLE  teacher1 DROP age1

删除表

--删除表
DROP TABLE IF EXISTS teacher1

注意点：

• `` 所有的字段名使用这个包裹，为了安全
• 注释 –
• sql 关键字大小写不敏感，建议大家写小写
• 所有的符号全部用英文

3.MySQL数据管理

3.1 外键（了解即可）

外键也称之为外键约束: foreign key

外键: 外面的键, 一张表的一个字段(非主键)指向另外一个表的主键, 那么该字段就称之为外键.

外键所在的表称之为子表(附表); 外键所指向的主键所在的表称之为父表(主表)

添加外键

方式一 在创建表的时候，添加约束（麻烦好复杂）

1. student表的gradeid字段要去引用年级表的gradeid
2. 定义外键key
3. 给这个外键添加约束 references引用

CREATE TABLE `grade`(
   `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年纪id',
   `gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
   PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
  


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 '123435' 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'邮箱',
  `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

删除有外键关系的表的时候，先要删除使用外键的（从表student），再去删除被引用的表（主表）

方式二：创建表成功后，添加外键约束

--创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`)；
--ALTER TABLE `表`ADD CONSTRAINT `约束名` FOREIGN KEY(`作为外键的列`) REFERENCES `哪个表` (`哪个字段`)；

以上操作都是物理外键，数据库级别的外键，不建议使用。

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行和列
• 我们想使用多张表的数据，想使用外键,用程序来实现。

3.2 DML语言（全部背下来）

我们之前学了如何修改删除 表，这是对表或者字段操作的，现在学习一下DML语言。忘了修改删除表操作的回头去看一下。

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

DML语言：

insert

update

delete

3.3添加

--插入语句（添加）
--insert into 表名 (字段名1) values('值1'),('值2'),('值3'),('值4')
--由于主键自增我们可以省略，如果不写表的字段，他就会一一匹配
-- 一般写插入语句，字段名可以省略，我们一定要数据和字段一一对应
-- 可以一次插入多个值，就意味着对应的几列有好几行的数据都添加了

INSERT INTO `grade` (`gradename`) VALUES('大一'),('大二'),('大三'),('大四')

注意事项：

1. 字段值可以省略，但是我们必须一一对应不能少。
2. values后面的值需要使用**,**隔开即可 values(‘值1’),(‘值2’),(‘值3’),(‘值4’)

3.4 修改

Update 修改谁（条件） set 原来的值=新值

-- 修改学员的名字
UPDATE `student` SET `name`='学员1' where id=1;

--不指定条件的情况下，会改动所有表格
update `student` set `name`='糟糕';

--修改多个属性，逗号隔开
update `student` set `name`='詹姆斯' ,`email`='13453141@outlook.com' where id=1;

--语法
--update 表名 set colnum_name =value, [colnum_name =value] where [条件]

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

操作符会返回布尔值

操作符：

• =， <, >, >=, <= 这些操作符与我们平时使用时的作用相同
• <> 和!= 都是不等于的意思
• AND相当于&&
• OR相当于||/
• between … and… 在某一个范围之内，闭区间。

--通过多个条件定位数据
update `student` set `name`='杜兰特' where `name`='詹姆斯' and sex='男'

注意：

• colnum_name 是数据库的列，尽量带上``
• 筛选的条件，如果没有指定，则会修改所有的列
• value，是一个具体的值，也可以是一个变量

update `student` set `birthday`= CURRENT_TIME where`name`='杜兰特' AND sex='男'

3.5删除

delete 命令

语法：delete from 表名 [where 条件]

-- 删除数据（避免这样写）,会将表中的所有数据删除
delete from `student`

-- 删除指定数据
delete from `student` where id=1;

TRUNCATE 命令

作用：完全清空一个数据库表，表的结构和索引约束不会变！

-- 清空 student 表
TRUNCATE `student`

delete 与 TRUNCATE 的区别

相同点：

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

不同点：

• TRUNCATE 重新设置 自增列，计数器会归零
• TRUNCATE 不会影响事务

--测试 delete与 TRUNCATE的区别
CREATE TABLE `test` (
    `id` INT (4) NOT NULL AUTO_INCREMENT,
    `coll` VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


INSERT INTO `test` (`coll`) VALUE('1'),('2'),('3')
DELETE FROM `test` -- 不会影响自增

TRUNCATE TABLE `test` -- 会影响到自增，自增归零

1. 我们先创建了一个test表格

2. 创建好是这样的，我们在添加三条记录

3. 我们使用delete进行全部删除后，再添加三条记录

可以看到自增的id没有归零，而TRUNCATE会将id的自增归零。

了解即可：delete删除问题，断电重启数据库

• innodb 自增会从1开始（存在内存当中的，断电即失）
• MYISAM 继续从上一个自增量开始 （存放在文件当中的，不会丢失）

4.DQL查询数据

(Data Query) LANGUAGE ：数据查询语言

• 所有的查询操作都用它 select
• 简单的查询，复杂的查询它都能做
• 数据库中最核心的语言，最重要的语言，使用频率最高的

4.1简单查询

我们有两张表 student表中含有 学号 姓名 性别 年级 住址 手机号 身份证号 和出生日期

result 中含有 学号 考试科目 考试时间 和成绩

-- 语法 select 字段 from 表
--查询全部的学生
select * from student

--查询指定字段,中间用逗号隔开
select `StudentNo`,`StudentName` from student

-- 别名，有时候列的名字不是那么清楚，我们可以起别名 字段和表都可以起别名
select `StudentNo` as 学号 ,`StudentName` 姓名 from student as 学生

-- 函数 Concat(a,b) 将a和b连起来
select concat('姓名',StudentName) as 新名字 from student

去重 distinct

作用：我们想查询哪些学生参加了考试，但是一个学生会参加多门考试，这时候我们就需要去重，使重复的数据只显示一条

--查询一下有哪些同学参加了考试
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

select 表达式 from 表

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

4.2where条件子句

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

搜索的条件就是

逻辑运算符

运算符	语法	描述
and &&	a and b a&& b	逻辑与
or ||	a or b a|| b	逻辑或
not !	not a !a	逻辑非

select `studentNo`,`StudentResult` from result

-- 查询考试成绩再95-100之间的同学
select `StundentNo`,`StudentResult` from result
where StudentResult >=95 and StudentResult <=100

--模糊查询
select `StudentNo`,`StudentResult` from result
where StudentResult between 95 and 100

-- 除了学号为100的学生之外的同学成绩
select `StudentNo`,`StudentResult` from result
where studentNo!=100

模糊查询

运算符
IS NULL	a is null	如果操作符为NULL，结果为真
IS NOT NULL	a is not null	如果操作符不为NULL,结果为真
BETWEEN	a between b nad c	若a在b和 c之间，结果为真
Like	a like b	SQL匹配，若a匹配b结果为真
In	a in (a1,a2,a3,a4…)	假设a在a1，或者a2…其中的某一个值中，结果为真

-- ======================like============================

-- 查询姓刘的同学
--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` is NULL or `Address` is '';

4.3联表查询

之前的查询都是查一张表里面的，现实中的查询肯定比这复杂多，所以我们学习一下多表查询。举个例子：我们有两张表，一张是关于学生成绩的表格，里面包含学生的成绩，学号，姓名。一张是学生填的申请交换生的表格，里面有 学号 姓名 想要去的学校。 那么我们怎么查询表一中报名了的同学呢？

首先我们了解一下SQL 连接（join）子句

JOIN 类型：

1. INNER JOIN :如果两个表中都满足条件，则返回对应的字段相当于取交集。
2. LEFT JOIN ：假设有一张空表，left join就是先将左表全部放在这个空表上，再去跟右表匹配，符合条件的则会添加左表对应的字段。如果匹配不成功，左表 也会全部保留，对应右表的字段则为NULL。
3. RIGHT JOIN：保留全部右表，即使匹配不成功。

--语法
 from 左表a left join 右表b  --这时候就是以左表a为基准
 from 左表a right join 右表b --这时候就是以右表b为基准

思路：

1. 分析需求看，分析查询的字段来自哪些表
2. 确定使用哪种连接查询
3. 确定交叉点，这个两个表中哪个数据是相同的
   • 判断的条件：学生表中id=报名表中id

4.3.1INNER JOIN :

这个是表一：

这个是表二

SELECT j.id,sex,`name`,GPA,university 
FROM jiaohuan j
INNER JOIN student s
ON s.id = j.id

4.3.2 LEFT JOIN

4.3.3 RIGHT JOIN

4.3.4 自连接（了解）

自己的表和自己的表连接，核心：一张表分成两张一样的表即可

现在我们有一张叫category的表格 其中 pid代表这个category的上级种类，为1就代表这是最高级别的。categoryid代表的它的子种类的pid。

为了看的清楚我将这一张表拆成两张表

父类

category	categoryname
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryname
3	4	数据库
3	6	web开发
5	7	ps技术
2	8	办公信息

操作：查询父类对应的子类关系，查询出来的结果应该长这样：

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

那么我们怎么使用sql查询呢？

-- 查询父子信息

SELECT a.categoryname AS '父栏目' ,b.categoryname AS '子栏目' 
FROM category AS a ,category AS b
WHERE a.categoryid = b.pid

我们只有一张表，但是我们给这一张表起两个名字，相当于有了两张一模一样的表。

4.4分页和排序

4.4.1 排序

-- 分页和排序  
-- order by 通过哪个字段排序
-- 排序：升序 asc
-- 降序 desc
-- 语法 order by `字段` asc/desc
 

4.4.2 分页

为什么要分页？

缓解数据库压力，给人的体验更好

语法：limit 查询起始下标 , pagesize

--假设一页有五条数据
-- 第一页 limit 0,5  (1-1)*5
-- 第二页 limit 5,5  (2-1)*5
-- 第二页 limit 10,5 (3-1)*5
-- 第N页 的查询起始下标  (n-1)*pagesize

--一般我们在分页的时候 要显示这么几个值
-- 【pagesize ：页面大小】
-- 【（n-1）*pagesize：起始值】
-- 【n:当前页】
-- 【数据总数/页面大小=总页面】

现在查询语句慢慢复杂起来了，我们看一下select的标准语法

4.5 子查询

我们之前的where里的查询都是一个确定的值，那么怎么让where里面判断一个不确定的值（计算出来的值）。这就要用到子查询

本质： 在where语句中嵌套一个查询

我们有三张表 】

1. student 里面有 StudentNo StudentName
2. result 里面有 StudentNo SubjectNo 和 studentReuslt
3. subject 里面有 SubjectNo 和SubjectName

-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNO`,`StudentName` 
FROM student s
INNER JOIN result r
ON r.StudentNo =s.StudentNo
WHERE `StudentResult` >=80

-- 在这基础上增加一个学科  高等数学
-- 查询 高等数学 的编号
SELECT DISTINCT s.`StudentNO`,`StudentName` 
FROM student s
INNER JOIN result r
ON r.StudentNo =s.StudentNo
WHERE `StudentResult` >=80 
AND `SubjectNo` =(
  SELECT `SubjectNo` FROM `subject`
  WHERE `SubjectName`='高等数学'
)

-- 全部用子查询
SELECT StudentNo,StudentName FROM student WHERE StudentNo IN(
 SELECT StudentNo FROM result WHERE StudentResult>=80 AND `SubjectNO`=(
     SELECT SubjectNo FROM `subject` WHERE `SubjectName` ='高等数学' 
 )
)

/* 
  我们现在学生表里面查询学生的学号与名字，由于student表里面每个学生的学号只有一次所有不用distinct。
  那么只用依靠StudentNo筛选，先从result中筛选分数大于80分的学号，再从subject里面筛选SubjectName='高等数学'的学科编号
*/