MySQL入门语法（视频学习笔记），mysql优化的几种方法面试

2401_84103844

于 2024-04-20 06:27:41 发布

阅读量881

点赞数 26

分类专栏： 2024年程序员学习文章标签： mysql 学习笔记

本文链接：https://blog.csdn.net/2401_84103844/article/details/137986387

版权

2024年程序员学习专栏收录该内容

191 篇文章 0 订阅

订阅专栏

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip1024b （备注Java）

正文

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

2、操作数据库

=====================================================================

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

mysql关键字不区分大小写

2.1、操作数据库

1、创建数据库

create database [if not exists] dorms;

2、删除数据库

drop database [if exists] dorms;

3、使用数据库

use dorms

如果数据库是一个关键字，则在名字左右加上``

4、查看数据库

show databases

2.2、数据库的列类型(字段)

1、数值

tinyint 1个字节
smallint 2个字节
mediumint 3个字节
int 4个字节 int
bigint 8个字节 long
float 4个字节
double 8个字节（精度问题！）
decimal 字符串型的浮点数，金融计算时使用，解决精度问题

2、字符串

char 字符串固定大小 0-255
varchar 可变字符串 0-65535

varchar和char 的区别——定长和变长

对于 char(4) 表示固定容纳4个字符，当少于4个字符时，会使用空格填充空缺的部分，使其达到4个字符。如果超过4个字符，会自动截断超出部分

而 varchar(4)，小于4个的话，则插入多少个字符就存多少个。且对于varchar字段来说，需要使用一个（如果字符串长度小于255）或两个字节（长度大于255）来存储字符串的长度（因为varchar是变长的，没有这个长度值他不知道如何读取数据）

()里的长度是当前字符集的字符长度，而不是字节长度

tinytext 微信文本 2^8-1
text 文本串 2^16-1

text和varchar的最大限制都是64k个字节,但text本质是溢出存储,innodb默认只会存放前768字节在数据页中,而剩余的数据则会存储在溢出段中。text类型的数据,将被存储在元数据表之外地方,但是varchar/char将和其他列一起存储在表数据文件中，值得注意的是，varchar列在溢出的时候会自动转换为text类型。text数据类型实际上将会大幅度增加数据库表文件尺寸。

text列不允许拥有默认值

int字段的长度与你存储的数据长度无关，与显示有关

char字段的长度与你存储数据长度有关

int类型的存储大小为4个字节，一个字节8位，也就是2^32 。 int的取值范围（-2147483648 ～ 2147483647），在数据库中的int不管后面填的长度为多少，只要在int的取值范围内，都能够将你的存储的数正常放入。int类型中长度的意义是位数不满足时，自动补充0（使用zerofill才能够显示），但不影响存储

3、时间日期

java.util.Date

date YYYY-MM-DD——日期格式
time HH:mm:ss——时间格式
datetime YYYY-MM-DD HH:mm:ss——最常用的时间格式
timestamp 时间戳，表示1970年到现在的毫秒数
year 表示年份

4、null

没有值，位置
不要使用其进行运算，结果为NULL

2.3、数据库的字段属性

Unsigned：

无符号的整数
声明了该列不能声明为负数

zerofill：

0填充
不足的位数，使用0填充。int(3) 5–>005

自增：

自动在上一条记录的基础上+1（默认）
通常用来设计唯一的逐渐index，必须是整数类型
可以自定义设计主键自增的起始值和步长
一个表只有一列能设置自增，且该字段必须为key

非空：

NULL/NOT NULL
假设设置为not null，如果不给它赋值则会报错
加入没有设置，如果不填写值则默认为null

default：

设置默认的值
sex，默认值设为男，则如果不指定该列的值，就自动为男

每个表都必须存在以下五个字段（做项目用的，表示数据存在的意义）

id 主键

version 乐观锁

is_delete 伪删除

gmt_create 创建时间

gmt_update 修改时间

2.4、创建数据库表

格式

create table [if not exit] 表名(

字段名 列类型[属性][索引][注释]，

…

字段名 列类型[属性][索引][注释]

)[表类型][字符串设置][注释]

注意点

使用英文的符号，表的名字和是字段尽量使用``括起来

AUTO_INCREMENT表示自增

字符串使用单引号或双引号括起来

所有的语句后面加英文的逗号，最后一个不用加

PRIMARY KEY表示主键，一般一个表只有一个唯一的主键

示例

CREATE TABLE people(

id INT(4) ZEROFILL NOT NULL AUTO_INCREMENT COMMENT ‘学号’,

name VARCHAR(20) NOT NULL DEFAULT ‘匿名’ COMMENT ‘姓名’,

gender VARCHAR(2) NOT NULL COMMENT ‘性别’,

age INT(3) NOT NULL COMMENT ‘年龄’,

birthday DATETIME DEFAULT NULL COMMENT ‘生日’,

phone INT(11) DEFAULT 123 COMMENT ‘电话号码’,

PRIMARY KEY(id)

)ENGINE=INNODB DEFAULT CHARSET=utf8

常用命令

show tables – 查看当前数据库的所有表

show create database school – 查看创建数据库的语句

show create table student – 查案创建表的语句

desc student – 显示表的结构

2.5、数据表的类型

– 关于数据库引擎

INNODB 默认使用

MYISAM 早些年使用

不同类型的区别

| | MYISAM | INNODB |

| — | — | — |

| 事务支持 | 不支持 | 支持 |

| 数据行锁定 | 不支持（表锁定） | 支持 |

| 外键约束 | 不支持 | 支持 |

| 全文索引 | 支持 | 不支持 |

| 表空间的大小 | 较小 | 较大，约为2倍 |

不同类型的优点

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

在物理空间存在的位置

所有数据文件都存在data目录下，一个文件夹就对应一个数据库

本质还是文件的存储

物理文件上的区别

使用InnoDB引擎的表结构对应的物理文件

.frm文件：保存了每个表的元数据，包括表结构的定义等；
.ibd文件：InnoDB引擎开启了独立表空间(my.ini中配置innodb_file_per_table = 1)产生的存放该表的数据和索引的文件。
.ibdata文件：共享表空间存储方式使用.ibdata文件，所有表共同使用一个ibdata文件

使用了MyISAM引擎的表会有对应的三种物理文件：

*.frm–表定义，是描述表结构的文件。
.MYD–"D"数据信息文件，是表的数据文件。
*.MYI–"I"索引信息文件，是表数据文件中任何索引的数据树。

设置数据表的字符集编码

charset=utf8

不设置的话为，会是mysql默认的字符集编码

可在my.ini配置文件中设置默认的编码

charset-set-server=utf8

2.6、修改删除表

修改

– 修改表名：alter table 旧表名 raname 新表名

ALTER TABLE teacher RENAME AS teacher1

– 增加表的字段：alter table 表名 add 字段名列属性

ALTER TABLE teacher1 ADD wage INT(10)

– 修改表的字段：重命名、修改约束

– 修改约束 alter table 表名 modify 字段名列属性[]

ALTER TABLE teacher1 MODIFY wage 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

3、MySQL数据管理

=========================================================================

3.1、外键（了解）

CREATE TABLE grade (

gradeId INT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT COMMENT ‘年级ID’,

gradeName VARCHAR(10) NOT NULL COMMENT ‘年级名字’,

PRIMARY KEY (gradeId)

) ENGINE=INNODB DEFAULT CHARSET=utf8

– 学生表的gradeId字段要去引用成绩表的gradeId

CREATE TABLE student(

id INT(8) ZEROFILL NOT NULL AUTO_INCREMENT COMMENT ‘学生id’,

name VARCHAR(10) NOT NULL COMMENT ‘学生姓名’,

gradeId INT(8) ZEROFILL NOT NULL COMMENT ‘学生年级’,

gender VARCHAR(3) NOT NULL COMMENT ‘性别’,

PRIMARY KEY(id),

– 定义外键key

KEY FK_gradeId(gradeId),

– 给这个外键添加约束（执行引用） references引用

CONSTRAINT FK_gradeId FOREIGN KEY(gradeId) REFERENCES grade(gradeId)

)ENGINE INNODB DEFAULT CHARSET=utf8

– 创建表的时候没有外键关系

ALTER TABLE student 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 into 表名[(字段1,字段2,字段3)] values (‘值A1’,‘值A2’,‘值A3’),(‘值B1’,‘值B2’,‘值B3’)…

INSERT INTO grade(gradename) VALUES (‘大四’);

– 没有设置NOT NULL的字段和设置了自增的键都可以不赋值，其他字段不赋值都会报错

– 如果省略表的字段，则会把所有字段与后面的值一一对应（即需要把每个字段的值都写出来，没有设置NOT NULL的字段和设置了自增的键如果不赋值也需要写NULL占位）

INSERT INTO student VALUES (NULL, ‘陈彦亨’, NULL, ‘男’);

– 插入多个数据时values一般换行书写

INSERT INTO student(name, gender)

VALUES (‘李四’, ‘男’), (‘王五’, ‘女’);

语法：insert into 表名[(字段1,字段2,字段3)] values ('值A1','值A2','值A3'),('值B1','值B2','值B3')...

注意事项：

字段和字段之间使用英文逗号隔开
字段是可以省略的，但是后面的值必须要一一对应，不能少
可以同时插入多条数据，values后面的值需要使用逗号隔开

3.4、修改

– 修改语句(update)

– 修改学员名字，带了条件

UPDATE student SET name = ‘陈梓坤’ WHERE id = 1;

– 不指定条件（会导致所有数据被修改）

UPDATE student SET gender = ‘男’;

– 修改多个属性，逗号隔开

UPDATE student SET name = ‘臭弟弟’, gender = ‘女’ WHERE id = 2;

语法：update 表名 set column_name = value, [column_name = value, ...] where 条件

条件语句where：返回布尔值

操作符：

= ：等于
<> 或 != ：不等于
< 、>、<=、>= ：小于，大于，小于等于，大于等于
between A and B ：A和B的闭区间
A and B ：A && B
A or B ：A || B
is null ：没有值

注意事项：

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

如current_time(获的当前时间)

3.5、删除

delete命令

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

– 删除数据（避免这样写，会删除所有数据）

delete from student;

– 删除指定数据

delete from student where id = 1;

truncate命令

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

– 清空student表

truncate student;

delete和truncate命令的区别

相同点：都能删除数据，都不会删除表结构
不同点：
truncate会重新设置自增列，计数器会归零，而delete的计数器不会归零
truncate不会影响事务

4、DQL查询数据

=======================================================================

4.1、DQL

简单的查询

SELECT CONCAT(StudentNo, CONCAT(‘:’, StudentName)) AS 结果 FROM student

– 查询表的全部字段

SELECT * FROM student;

– 查询表的指定字段

SELECT StudentNo, StudentName FROM student;

– 函数 concat(a, b)

SELECT CONCAT(StudentNo, CONCAT(‘:’, StudentName)) AS 结果 FROM student

语法：select 字段1,... from 表

输入字段名时不区分大小写，输出的结果列名大小写形式和输入时保持一致，和创建时不一定一致

别名与去重

as 起别名

– 别名：给结果起一个名字

– 给字段起别名

SELECT StudentNo AS 学生学号, studentname AS 学生姓名 FROM student

– 给表起别名

SELECT StudentNo, studentname 姓名 FROM student AS s

其别名时as可以省略,as后的内容可以带引号也可以不带

distinct 去重

– 查询有哪些学生参加了考试

– 查询所有成绩，每个学生有多个科目的成绩

select * from result;

– 查询所有学生，发现重复数据（此表中没有主键）

select StudentNo from result;

– 去除重复数据

select distinct StudentNo from result;

数据库表达式

数据库的列（表达式）

– 查询系统版本（函数）

SELECT VERSION();

– 查询计算结果（表达式）

SELECT 100*3-1 AS 计算结果;

– 查询自增的步长（变量）

SELECT @@auto_increment_increment;

– 查看学员考试成绩加一分后的结果

SELECT StudentNo, StudentResult+1 AS 加分后 FROM result;

格式：select 表达式 from 表;

数据库中的表达式：

文本值
列
null
函数
计算表达式
系统变量

4.2、where条件子句

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

搜索的条件为一个或者多个表达式

逻辑运算符

| 运算符 | 语法 | 描述 |

| :-: | :-: | :-: |

| and && | A and B / A && B | 逻辑与 |

| or || | A or B / A || B | 逻辑或 |

| not ! | not A / !A | 逻辑非 |

尽量使用英文字母

– 查询考试成绩再95-100分之间的数据

– and

SELECT StudentNo, StudentResult FROM result

WHERE StudentResult >= 95 AND StudentResult <= 100;

– &&

SELECT StudentNo, StudentResult FROM result

WHERE StudentResult >= 95 && StudentResult <= 100;

– 模糊查询（区间）

SELECT StudentNo, StudentResult FROM result

WHERE StudentResult BETWEEN 95 AND 100;

– 查询除了1000号学生意外的同学的成绩数据

– !=

SELECT StudentNo, StudentResult FROM result

WHERE StudentNo != 1000;

SELECT StudentNo, StudentResult FROM result

WHERE !StudentNo = 1000;

– not

SELECT StudentNo, StudentResult FROM result

WHERE NOT StudentNo = 1000;

模糊查询：比较字符串

| 运算符 | 语法 | 描述 |

| — | — | — |

| is null | A is null | A为null则为真 |

| is not null | A is not null | A不为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,A3…其中的一个值则为真 |

– 模糊查询

– 查询姓张的同学

– like 结合&（代表任意个数的字符），_（代表一个字符）