MySQL学习笔记-恶补基础篇

概述

MySQL学习笔记，跟随b站up主狂神，恶补理论，弥补曾经犯下的错误 ，程序员落泪ing…

狂神：只要学不死，就往死里学

视频链接：https://www.bilibili.com/video/BV1NJ411J79W

1、初识数据库

1.1、DataBase

存储数据，管理数据

1.2、数据库分类

关系型数据库

• MySQL ，SQL Server ，DB2 ， SQLite…

非关系型数据库：

• Redis
• MongoDB

1.3、DBMS（数据库管理系统）

• 数据库的维护管理软件

1.4、命令行操作数据库

mysql -uroot -p123456 --链接数据库
create database student --创建数据库
show databases; --查看所有数据库
use 数据库名 --切换数据库
show tables -- 查看所有表
describe student --查看student表结构
exit --退出链接

数据库xxx语言：

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

2、操作数据库

关键字不区分大小写

2.1、操作数据库

• 创建数据库

create database [if not exists] student

• 使用数据库

--表明或者字段名如果是一个特殊字符，就需要带`
use `student`

• 删除数据库

drop database [if exists] student

2.2、数据库列类型

数值

• tinyint 十分小的数据，1个字节
• smallint 较小的数据 ， 2个字节
• mediumint ， 3 个字节
• int 标准的整数 ， 4个字节
• big 较大的数字，8个字节
• float 单精度浮点数 4 个字节
• double 浮点数 8个字节
• decimal 字符串形式的浮点数 金融计算一般使用decimal

字符串

• char 字符串固定大小 0 -255

• varchar 可变字符串 0 - 65536

• 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进行运算

2.3、数据库字段类型

Unsigned

• 无符号整数
• 生命了该列不能声明为负数

zerofill

• 0填充
• 不足的位数使用零填充 int（5） 3 — 003

自增

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

非空

• 假设设置为not null，如果不赋值会报错

默认

• 设置默认值

拓展

• 每一个表都要有以下五个字段 ，阿里巴巴开发手册
  • id 主键
  • version 乐观锁
  • is_delete 伪删除
  • gmt_create 创建时间
  • gmt_update 更新时间

2.4、创建数据库表

create TABLE IF NOT EXISTS `student`(
	`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

2.5、数据表的类型

MyISAM和InnoDB的区别

InnoDB 默认使用

MyISAM 早些年使用

	MyISAM	InnoDB
事务	不支持	支持
数据行锁定	不支持	支持
外键	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为2倍

常规使用操作：

• MyISAM 节约空间 速度较快
• InnoDB 安全性高，事务处理，多表多用户操作

在物理空间的位置：

所有数据库文件都存在data文件夹下，本质还是文件存储

• InnoDB ： 在数据库表中只有.frm文件，以及上级目录的abdata1文件
• MyISAM对应文件
  • frm文件 表结构定义文件
  • MYD 数据文件（data）
  • MYI 索引文件

2.6、修改删除表

修改

Alter

• 修改表名

Alter Table 旧表名 RENAME as 新表名

• 增加字段

Alter Table 表名 add age Int(11)

• 修改字段(重命名，修改约束)

Alter table 表名 modify age varchar(11) --约束
Alter table 表名 change age age1 varchar(11) --重命名

• 删除表字段

Alter table 表名 drop 字段名

删除

• 删除表

Drop table if exists 表名

所有的创建和删除操作尽量加上判断，以免报错

注意点：

• ``字段名包裹
• 注释 – /**/
• sql大小写不敏感，建议小写

3、MySQL数据管理

3.1、外键

外键居然是了解即可。。。。。

方式一 创建表是增加约束 麻烦

create table `grade`(
  `id` int(10) not null auto_increment comment 'id',
  `name` varchar(50) not null comment 'name',
  primary key (`id`)
)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 '姓名',
  `gradeid` int(10) not null comment '年级',
	PRIMARY KEY (`id`),
  key `FK_gradeid`(`gradeid`), --定义外键key
  constraint `FK_gradeid` foreign key(`gradeid`) reference `grade`(`id`)   --给这个外键添加约束（执行引用）
)ENGINE=INNODB DEFAULT CHARSET=utf8

删除有外键关系的表，必须先删除引用别人的表（从表），在删除被引用的表（主表）

方法二 修改表添加

alter table `stuent`
add constraint `FK_gradeid` foreign key(`gradeid`) reference `grade`(`id`)

以上都是数据库级别的物理外键，不建议使用（避免数据库过多造成困扰）

最佳实践：

• 数据库就是单纯的表，只用来存数据，只有行和列
• 想用多张表的数据，用程序实现

3.2、DML语言

DML：数据操作语言

3.3、添加

insert

--插入语句
insert into 表名([字段1，字段2，字段3...]) values('值1','值2','值3'...)
--由于主键自增，我们可以省略（如果不写字段，他就会一一匹配）
--我们写插入语句，一定要数据与字段一一对应
--插入多个字段
insert into 表名 ([字段]) 
values('值1'),('值2'),('值3')...)

注意：

• 字段可以省略，但是后面的值必须一一对应，不能少

3.4、修改

update

update student set name = 'xwy' where id = '1' --没有选择条件默认改变所有数据

--修改多个属性
update student set name = 'xwy',email = '11.com'  where id = '1'

条件：where子句。运算符 id=某个值，>某个值，在某个区间内修改…

操作符返回布尔值

操作符	含义	范围	结果
=	等于	5=6	false
<>或者!=	不等于	5<>6	True
> , >=
< ,<=
between…and…	闭合区间	[2,5]
And
or

注意：

• Column_name 是数据库列，尽量``
• 条件，筛选的条件如果没有指定就会修改所有的列
• value是一个具体的值，也可以是一个变量（时间一般会用）

3.5、删除

delete

delete from 表名 [where 条件]

truncate

完全清空数据库，表的结构和索引约束不会变

truncate table 表名

区别：

• truncate 重新设置自增列，自增会归零！
• truncate不会影响事务

delete删除的问题，重启数据库，现象

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

4、DQL查询数据

4.1、DQL

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

4.2、查询

select * from student --查询student的全部字段
select stuname from student --查询某个字段
select stuname as 姓名 from student --别名
select concat('姓名：'，stuname) as 新名字 from student --函数 拼接字符串

列的名字不是那么见名知意，就用别名

去重查询 distinct

select distinct stuNum from result --	去除查询结果中重复的数据只显示一条 

数据库的列（表达式）

select version() --查询系统版本（函数）
select 100*3 as jieguo --用来计算（计算表达式）
select @@auto_increment_increment --查询自增的步长（变量）
select `studentResult`+1 as '提分后' form result

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

4.3、where

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

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	两个都为真就为真
or ||	a or b a ||b	一个为真就为真
Not !	not a !a	真为假，假为真

尽量使用英文表达式

select stuNum,sturesult from result
     where sturesult>=95 and sturesult<