MySQL一课一得

在社会信息化程度日益提高的今天,数据库已经无处不在了。一个网站需要有数据库来存储数据。一个学校需要用数据库来存储学生和教师的信息。一个公司需要用数据库来存储员工的信息和公司的资料。
作为一名学大数据技术专业的大学生，有着这样学习MySQL数据库的机会非常难得，在课堂学习了理论知识，在课后中应用理论，理解理论。通过这次学习MySQL数据库，我收获了很多，一方面理解懂到了许多以前没懂过的专业知识与理论知识的应用，另一方面还提高了自我动手的能力。本次MySQL数据库课程学习，是对我潜力的进一步锻炼，也是一种考验。从中获得的诸多收获，也是很可贵的，是十分有好处的。

MySQL基础知识

概念性陈述

1 、 MySQL 是小型关系数据库管理系统，开源、规模小、成本低。

2 、数据库是由数据构成的有序的集合。

3 、数据存储在结构化的数据表中。

4 、数据库， DB ，长期存储、有组织、可共享、统一管理的数据集合。

5 、 DB 通过 DBMS 进行统一管理。

6 、

7 、数据库系统（ DS ） = 数据库（ DB ） + 数据库管理系统（ DBMS ） + 数据库应用程序 (DA)

8 、数据库系统， database system ，提供存储空间，用来存储各种数据，是一个存储数据的容器。一个数 据库可以包含N 个文件，一个数据库系统包含 N 个数据库。

9 、数据库应用程序， database application ，负责与 DBMS 通信，访问和管理 DBMS 中存储的数据，允许 对数据进行插入、修改、删除。

10 、 SQL 语言，是结构化语言。包含：

数据定义语言（ DDL ）【 drop 、 create 、 alter 】

数据操作语言（ DML ）【 insert 、 update 、 delete 】

数据查询语言（ DQL ）【 select 】

数据控制语言（ DCL ）【 grant 、 revoke 、 commit 、 rollback 】。

11 、主要数据库方位接口： ODBC 、 JDBC 、 ADO .NET 、 PDO

ODBC， Open Database Connectivity ，开放数据库连接，可以通过统一的方式处理所有数据库。 JDBC， Java DataBase Connectivity ， Java 数据库连接， Java API ，一组用 Java 编写的类和接 口组成。 ADO .NET ， Microsoft 在 .NET 框架开发中的一组和数据源进行交互的面向对象类库。 PDO， PHP Data Object ， PHP 访问数据库的轻量级的、一致的接口，提供了一个数据访问的抽象层。

12 、 InnoDB

13 、 MySQL 默认字符集： utf8mb4

14 、启动 / 停止 MySQL 服务命令（ DOS ）： net start mysql / net stop mysql

15 、连接 MySQL 数据库命令（ DOS ）： mysql -h hostname -u username -p hostname—— 主

机地址， ip

16 、登录数据库命令（ DOS ）： mysql -h localhost -u root -p

17 、 MySQL 8 开始，系统表全部换成事务型的 InnoDB 表。

18 、 MySQL 主要存储引擎： MyISAM 、 InnoDB 、 MEMORY(HEAP) 、 BDB 、 FEDERATED 等。可以使用 show engines;查看系统支持的存储引擎

21 、 select version(); 可以获取 MySQL 版本号

数据类型

类型名称	说明	存储需求	类型名称	说明	存储需求
tinyint	很小的整数	1 字节	float	单精度浮点数	4 字节
smallint	小的整数	2字节	double	双精度浮点数	4 字节
mediumint	中等整数	3字节	decimal(M,D),DEC	压缩的 “ 严格 ” 定点 数	M+2 字节
int	整数	4字节	year	YYYY	1 字节
bigint	大整数	8字节	TIME	HH:MM:SS	3 字节
char(M)	固定长度字 符串	M字节	DATE	YYYY-MM-DD	3 字节
varchar(M)	可变长度字 符串	Len+1	DATETIME	YYYY-MM-DD HH:MM:SS	8 字节
text	非二进制字 符串	Len+2	TIMESTAMP	YYYY-MM-DD HH:MM:SS	4 字节

-- 数据类型

决定数据的存储格式，包含整数、浮点数、精确小数、二进制、日期 / 时间、字符串数据类型。

-- 如何选择数据类型

如果不需要小数，使用整形保存数据；如果需要小数，使用浮点数类型。

定义货币等对精度有高要求的，使用 decimal 数据类型。

对存储不大但要求速度的使用 char 类型，反之使用 varchar 类型。

如果需要存储范围较大的日期，最好使用 datetime 。

in 运算符，判断操作数是否为 in 列表中的其中一个值，如果是，返回 1 ，否则返回 0 。

not in 运算符，判断操作数是否为 in 列表中的其中一个值，如果不是，返回 1 ，否则返回 0 。

like 运算符，匹配字符串，如果匹配返回 1 ，否则返回 0 。 '%' ，匹配任意数目的字符， '_' ，匹配单个字符。

and 或者 && ，逻辑运算符， …… 和 …… 均满足返回 1 ，否则返回 0 。

or 或者 || ，逻辑运算符， …… 和 …… 只要满足一个返回 1 ，否则返回 0 。

NULL ，空值，表示数据未知、不适用或将在以后添加数据。和 0 不同，和空字符串不同。

库表操作、完整性约束

概念性知识

1 、数据表是数据库中最重要、最基本的操作对象。

2 、数据表是数据存储的基本单位。

3 、数据表数据存储是按照行和列的格式进行存储的。每一行代表一条唯一的记录，每一列代表记录中的一 个域。

4 、表，是数据库对象。包含行、列，行又称为记录，是数据单位；列，又称为字段，记录了数据类型、数 据长度等信息，是属性，用于描述信息。

5 、列级约束条件：

主键、外键、唯一性、检查、默认、非空

直接跟在列定义后，不必指定列名

6 、表级约束条件：

主键、外键、唯一性、检查 不包含在列定义中，通常多列同时进行约束，必须之处要约束的列名

7 、创建的表名，不区分大小写，不能使用 SQL 关键字。

8 、主键， primary key constraint ，是表中一列或多列的组合。非空、唯一。能够唯一标识表中的一条 记录，可以结合外键定义不同数据表之间的关系，可以加快数据库查询速度。

9 、外键，属于参照完整性。用于在两表之间建立数据的连接，可以一列或多列。可以为空值。如果非空， 则该外键值必须等于其所在主键表的值。子表的外键必须关联父表的主键，且关联字段的数据类型必须匹 配。

10 、非空约束， not null constraint ，字段值不能为空

11 、默认约束， default constraint ，指定某列默认值

12 、自增长约束，通过为主键添加 auto_increment 实现，初始值为 1 ，一个表只能由一个字段使用

auto_increment 约束，且该字段必须是主键的一部分，该字段数据类型可以是任何整数类型【 tinyint 、 smallint、 int 、 bigint 】。

语句实现

-- 查看数据库

show databases; -- 查看当前存在的数据库

-- 创建数据库

create database 数据库名称;

show create database 数据库名称; -- 查看数据库定义语句

-- 删除数据库

drop database 数据库名称;

经典练习：

1、查看当前系统中的数据库。
2、创建数据库book，使用show语句查看数据库定义信息。
3、删除数据库。
典型错误：No database selected。解决方案：use 数据库名; -- 指定在哪个数据库进行操作

-- 新建表
create table 表名
(
字段名1 数据类型 [列级约束条件] [默认值],
……
字段名N 数据类型 [列级约束条件] [默认值]
[表级约束条件]
);
show tables; //查看数据表
-- 单字段定义主键
字段名 数据类型 primary key [默认值]
create table tb_employee
(
id int(11) primary key,
name varchar(50),
deptid int(11),
idcard char(18)
);
-- 多字段联合主键
primary key [字段1,字段2,……,字段n]
create table tb_employee
(
id int(11),
name varchar(50),
idcard char(18),
gender tinyint(2),
primary key(id,idcard)
);
-- 创建外键
[constraint <外键名称>] foreign key 字段名1 [,字段名2,……] references <主表名称> 主键列
1 [,主键列2,……]
create table tb_employee
(
id int(11),
name varchar(50) not null,
idcard char(18) unique,
gender tinyint(2) default 1,
deptid int(11),
primary key(id,idcard),
constraint fk_emp_dept foreign key(deptid) references tb_dept(id)
);

视图

概念性知识及语句实现

数据库中的视图是虚拟表

视图从一个或多个表导出，可以使用 select 语句进行数据查询

如果基本表数据发生变化，该变化自动反映到视图中

视图可以屏蔽基本表结构，通过分权限制，提升安全性

视图和表的区别

视图是已经编译好的 SQL 语句，是基于基本表结果的可视化表，表不是

视图没有实际物理记录，表有

表是视图的内容，视图是表查询结果的可视化结果

表占物理存储空间，视图不占物理存储空间

视图是概念性存在，只有定义没有实体。表真实存在可以及时修改，视图修改只能通过创建语句进

行修改。

视图的建立、删除不影响其关联的基本表

视图依赖基本表

-- 创建视图
create view 视图名称
as
查询语句
[with [cascaded | local] check option]; -- 视图在更新时保证在视图的权限范围之内
-- 替换已创建的视图
create or replace view 视图名称
as
查询语句
[with [cascaded | local] check option];
-- 视图调用
select *|<字段列表> from 视图名;
和select语句使用相同
-- 修改视图，可使用CREATE OR REPLACE VIEW语句，语法：
create or replace view 视图名称
as
查询语句
[with [cascaded | local] check option];
alter view 视图名称
as
查询语句
[with [cascaded | local] check option];

更新视图是指通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟表，其中没有数据。
通过视图更新的时候都是转到基本表上进行更新的，如果对视图增加或者删除记录，实际上是对其基本表增加或者删除记录。

-- 删除视图
drop view [if exists] 视图名称 [,视图名称] ……
[restrict | cascade]
-- 查看视图
看视图的信息可以通过SHOW TABLE STATUS的方法完成，语法：show table status like '视图名';
查看视图的信息可以通过SHOW TABLE STATUS的方法完成，语法：show create view 视图名;
describe 视图名;-- 查看视图基本信息
desc 视图名;-- 查看视图基本信息简写形式

数据表之间存在外键关联情况，如果直接删除主表(外键列作为主键的表)，会显示失败，因为其操作破坏了表的参照完整性。
如果一定要删除，需先删与其关联的子表数据，再删主表；也可以先删外键约束。
 

-- 解除外键约束
alter table <表名> drop foreign key <约束名称>;
alter table tb_employee drop foreign key fk_emp_dept;
-- 非空约束
字段名 数据类型 not null --tb_employee表中的name列
-- 唯一性约束
字段名 数据类型 unique -- 在列后直接定义
[constraint <约束名>] unique(<字段名>)
唯一性约束和主键约束区别：
一个表可以有多个字段是唯一性约束，但只能有一个主键；
主键不允许空值，唯一性约束允许有空值，只是这个空值只有一个。
-- 默认约束
字段名 数据类型 default 默认值
-- 自增长约束
字段名 数据类型 auto_increment
create table tb_employee
(
id int(11) primary key auto_increment,
name varchar(50) not null,
idcard char(18) unique,
gender tinyint(2) default 1,
deptid int(11)
constraint fk_emp_dept foreign key(deptid) references tb_dept(id)
);
-- 查看数据表结构
describe 表名;
show create table <表名>;
-- 更新表
alter table <旧表名> rename [TO] <新表名>;
-- 修改字段数据类型
alter table <表名> modify <字段名> <数据类型>
-- 修改字段名
alter table <表名> change <旧字段名> <新字段名> <新数据类型>;
-- 添加字段
alter table <表名> add <字段名> <数据类型> [约束条件] [first | after 字段名];
-- 删除字段
alter table <表名> drop <字段名>;
-- 删除表的外键约束
alter table <表名> drop foreign key <外键约束名>;
-- 删除表
drop talbe [if exists] 表1,表2,……;
-- 插入语句
insert into 表名(列名列表) values(值列表);
向表中插入数据有两种方法：
1、全表插入，必须和表中的列数目相等，且顺序必须相同，数据类型必须匹配。如果表结构发生变化，语
句也需要修改。
2、指定列插入，列名列表和值列表是一一匹配关系，且数据类型必须匹配
同时插入多条数据时，值列表之间使用逗号分隔
-- 更新语句
update 表名 set 列名=值[,列名=值……];
where 条件限制
-- 删除语句
delete from 表名 [where 条件限制];
-- 如果希望删除表中的所有记录，还可以使用truncate table语句(仅作了解)
视图
概念性知识及语句实现
truncate table 表名; -- 将直接删除原来的表，并重新创建一个表
truncate是直接删除表，而非删除记录，所以执行速度比delete快
-- 根据查询结果新增表
create into 表名
select 查询;
-- 根据查询结果插入数据
insert into 表名

函数

概念性知识及语句实现

-- 变量定义
变量可以在子程序中定义使用，作用范围在begin……end程序中
declare 变量名[,变量名]…… 数据类型[defaule 值];
declare para int default 666;-- 定义para变量，并赋初值666
declare val varchar;

-- 变量赋值
set 变量名=值|表达式[,值|表达式]……;
declare val1,val2,val3 int;
set val1=20,val2=60;
set val3=val1+val2;
select 列名[,……] into 变量名[,……] 查询条件表达式;
declare bname varchar(20);
select bookname into bname from bookinfo where bookid='TP391.7/2';
-- 流程控制语句
-- IF
if 判断条件 then 执行语句集
[elseif 判断条件 then 执行语句集]……
[else 执行语句集]
end if
-- 判断val是否为空，如果空返回‘val为空值’，否则返回‘val非空’
declare returnStr varchar(40);
if val is NULL
then select 'val为空值' into returnStr;
else select 'val非空' into returnStr;
end if;
-- CASE
case 条件判断表达式
when 值 then 语句集
[when 值 then 语句集]……
[else 语句集]
end case
-- 判断val值
declare returnStr varchar(40);
case val
when 1 then select 'val=1' into returnStr;
when 2 then select 'val=2' into returnStr;
else select 'val!=1 varl<>2' into returnStr;
end case;
-- WHILE
[while标注名称:] while 判断表达式 do
语句体
end while [while标注名称]
declare i int default 0;
while i<100 do
set i=i+1;
end while;
-- delimiter //语句，将MySQL结束符设置为//，避免与存储过程或函数中SQL语句的结束符冲突，以
end//结束
-- delimiter ; 恢复分号为默认结束符
-- 创建函数
create function 函数名称([形参])
returns 返回数据类型
函数体实现语句
-- 判断val是否为空，如果空返回‘val为空值’，否则返回‘val非空’
delimiter #
create function func_demo1(val varchar(40))
returns varchar(40)
no SQL
BEGIN
declare returnStr varchar(40);
if val is NULL
then select 'val为空值' into returnStr;
else select 'val非空' into returnStr;
end if;
return returnStr;
end#
delimiter ;
-- 判断val值
delimiter #
create function func_demo2(val int)
returns varchar(40)
no SQL
BEGIN
declare returnStr varchar(40);
case val
when 1 then select 'val=1' into returnStr;
when 2 then select 'val=2' into returnStr;
else select 'val!=1 varl<>2' into returnStr;
end case;
return returnStr;
end#
delimiter ;
select func_demo2(5);
-- 函数调用，使用select语句实现
select func_demo1('白马非马'); -- 返回：val非空
select func_demo1(NULL); -- 返回：val为空值
select func_demo1(''); -- 返回：val非空
-- 实现1+2+3+……+N；
delimiter //
create function func_sumToN(n int)
returns int
no sql
BEGIN
declare returnNum int default 0;
while n>0 do
set returnNum=returnNum+n;
set n=n-1;
end while;
return returnNum;
end//
delimiter ;
select func_sumToN(100);
-- 查看函数
show function status [like '匹配条件'] -- 查看函数状态
show function status like 'f%'; -- 查看所有f开头的函数的状态，显示数据库、名字、类型、创
建者、修改日期等
show function 函数名称 -- 查看函数定义
-- 修改函数
alter function 函数名称 [函数特征]
-- 删除函数
drop function [if exists] 函数名称
drop function fn_demo; -- 删除fn_demo，不存在会报错误
drop function if exists fn_demo; -- 如果fn_demo存在进行删除，否则不做

在MySQL数据库课程中我明白了许多：提高合作讨论能力，遇到问题及时与同学、老师沟通反馈，最终解决问题。让我意识到集体讨论问题的重要性，只有互相交流，才能更快的进步。MySQL数据库课程让我学到了很多东西，不仅从理论上了解了MySQL数据库的知识，而且实践能力也有很大的提高。在MySQL数据库课程期间，我学会了如何更好地利用理论知识解决实际问题，理论知识与实践相结合，真正地利用所学知识。在短短的时间里，我在沟通、自学取得了显著的进步。让我对MySQL数据库在方面感性有一个新的认识。我相信这些宝贵的经验将成为我未来成功的重要基石。