MySQL - 数据库基础

1.  数据库基础知识

数据库基本概念

•  数据

数据（Data）是指对客观事物进行描述并可以鉴别的符号，这些符号是可识别的、抽象的。它不仅指狭义上的数字，而是有多种表现形式：字母、文字、文本、图形、音频、视频等。现在计算机存储处理的数据范围十分广泛，而描述这些数据的符号也变得越来越复杂。

•  数据库

数据库（Database，DB）指的是以一定格式存放、能够实现多个用户共享、与应用程序彼此独立的数据集合。

• 数据库管理系统

数据库管理系统（Database Management System，DBMS）是用来定义和管理数据的软件。如何科学的组织和存储数据，如何高效的获取和维护数据，如何保证数据的安全性和完整性，这些都需要靠数据库管理系统完成。目前，比较流行的数据库管理系统有：Oracle、MySQL、SQL Server、DB2等。

• 数据库应用程序

数据库应用程序（Database Application System，DBAS）是在数据库管理系统基础上，使用数据库管理系统的语法，开发的直接面对最终用户的应用程序，如学生管理系统、人事管理系统、图书管理系统等。

•  数据库管理员

数据库管理员（Database Administrator，DBA）是指对数据库管理系统进行操作的人员，其主要负责数据库的运营和维护。

• 最终用户

最终用户（User）指的是数据库应用程序的使用者。用户面向的是数据库应用程序（通过应用程序操作数据），并不会直接与数据库打交道。

• 数据库系统

数据库系统（Database System，DBS）一般是由数据库、数据库管理系统、数据库应用程序、数据库管理员和最终用户构成。其中DBMS是数据库系统的基础和核心。

 

• 数据库类型

数据库经过几十年的发展，出现了多种类型。根据数据的组织结构不同，主要分为网状数据库、层次数据库、关系型数据库和非关系型数据库四种。目前最常见的数据库模型主要是：关系型数据库和非关系型数据库。

1. 关系型数据库

关系型数据库模型是将复杂的数据结构用较为简单的二元关系（二维表）来表示，如图1-4所示。在该类型数据库中，对数据的操作基本上都建立在一个或多个表格上，我们可以采用结构化查询语言（SQL）对数据库进行操作。关系型数据库是目前主流的数据库技术，其中具有代表性的数据库管理系统有：Oracle、DB2、SQL Server、MySQL等。

2. 非关系型数据库NOSQL

NOSQL（Not Only SQL）泛指非关系型数据库。关系型数据库在超大规模和高并发的web2.0纯动态网站已经显得力不从心，暴露了很多难以克服的问题。NOSQL数据库的产生就是为了解决大规模数据集合多重数据种类带来的挑战，尤其是大数据应用难题。常见的非关系型数据库管理系统有Memcached、MongoDB等。

 

常见的关系型数据库：

虽然非关系型数据库的优点很多，但是由于其并不提供SQL支持、学习和使用成本较高并且无事务处理，所以本书的重点是关系型数据库。下面我们将介绍一下常用的关系型数据库管理系统。

1. Oracle

Oracle数据库是由美国的甲骨文（Oracle）公司开发的世界上第一款支持SQL语言的关系型数据库。经过多年的完善与发展，Oracle数据库已经成为世界上最流行的数据库，也是甲骨文公司的核心产品。

Oracle数据库具有很好的开放性，能在所有的主流平台上运行，并且性能高、安全性高、风险低；但是其对硬件的要求很高、管理维护和操作比较复杂而且价格昂贵，所以一般用在满足对银行、金融、保险等行业大型数据库的需求上。

2. DB2

DB2是IBM公司著名的关系型数据库产品。DB2无论稳定性，安全性，恢复性等等都无可挑剔，而且从小规模到大规模的应用都可以使用，但是用起来非常繁琐，比较适合大型的分布式应用系统。

3. SQL Server

SQL Server是由Microsoft开发和推广的关系型数据库，SQL Server的功能比较全面、效率高，可以作为中型企业或单位的数据库平台。SQL Server可以与Windows操作系统紧密继承，无论是应用程序开发速度还是系统事务处理运行速度，都能得到大幅度提升。但是，SQL Server只能在Windows系统下运行，毫无开放性可言。

4. MySQL

MySQL是一种开放源代码的轻量级关系型数据库，MySQL数据库使用最常用的结构化查询语言（SQL）对数据库进行管理。由于MySQL是开放源代码的，因此任何人都可以在General Public License的许可下下载并根据个人需要对其缺陷进行修改。

由于MySQL数据库体积小、速度快、成本低、开放源码等优点，现已被广泛应用于互联网上的中小型网站中，并且大型网站也开始使用MySQL数据库，如网易、新浪等。

1.2 MySQL基础知识

MySQL入门

MySQL数据库最初是由瑞典MySQL AB公司开发，2008年1月16号被Sun公司收购。2009年，SUN又被Oracle收购。MySQL是目前IT行业最流行的开放源代码的数据库管理系统，同时它也是一个支持多线程高并发多用户的关系型数据库管理系统。MySQL之所以受到业界人士的青睐，主要是因为其具有以下几方面优点：

1. 开放源代码

MySQL最强大的优势之一在于它是一个开放源代码的数据库管理系统。开源的特点是给予了用户根据自己需要修改DBMS的自由。MySQL采用了General Public License，这意味着授予用户阅读、修改和优化源代码的权利，这样即使是免费版的MySQL的功能也足够强大，这也是为什么MySQL越来越受欢迎的主要原因。

2. 跨平台

MySQL可在不同的操作系统下运行，简单地说，MySQL可以支持Windows系统、UNIX系统、Linux系统等多种操作系统平台。这意味着在一个操作系统中实现的应用程序可以很方便地移植到其他的操作系统下。

3. 轻量级

MySQL的核心程序完全采用多线程编程，这些线程都是轻量级进程，它在灵活地为用户提供服务的同时，又不会占用过多系统资源。因此MySQL能够更快速、高效的处理数据。

4. 成本低

MySQL分为社区版和企业版，社区版是完全免费的，而企业版是收费的。即使在开发中需要用到一些付费的附加功能，价格相对于昂贵的Oracle、DB2等也是有很大优势的。其实免费的社区版也支持多种数据类型和正规的SQL查询语言，能够对数据进行各种查询、增加、删除、修改等操作，所以一般情况下社区版就可以满足开发需求了，而对数据库可靠性要求比较高的企业可以选择企业版。

2. DDL和DML

 

2.1创建数据库表

1. 认识数据库表

表（Table）是数据库中数据存储最常见和最简单的一种形式，数据库可以将复杂的数据结构用较为简单的二维表来表示。二维表是由行和列组成的，分别都包含着数据，如表所示。                                     

学号	姓名	性别	年龄
17071401	张三	男	20
17071402	李四	女	18
17071403	王五	男	21
17071404	赵六	女	19

每个表都是由若干行和列组成的，在数据库中表中的行被称为记录，表中的列被称为是这些记录的字段。

记录也被称为一行数据，是表里的一行。在关系型数据库的表里，一行数据是指一条完整的记录。

字段是表里的一列，用于保存每条记录的特定信息。如表5-1所示的学生信息表中的字段包括“学号”、“姓名”、“性别”和“年龄”。数据表的一列包含了某个特定字段的全部信息。

【示例1】创建数据库表student

-- 创建一个数据库表  
create table student(  
    sno int(6),  
    name varchar(12),  
    sex char(2),  
    age int(2),  
    enterdate date,  
    clazzname varchar(12),  
    email varchar(20) -- 最后一个字段没有，  
);  
-- 查看表的结构  
desc student;  
-- 查看完整的建表语句  
show  create table student  
-- CREATE TABLE `student` (  
--   `sno` int(6) DEFAULT NULL,  
--   `name` varchar(12) DEFAULT NULL,  
--   `sex` char(2) DEFAULT NULL,  
--   `age` int(2) DEFAULT NULL,  
--   `enterdate` date DEFAULT NULL,  
--   `clazzname` varchar(12) DEFAULT NULL,  
--   `email` varchar(20) DEFAULT NULL  
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
-- 查看表的数据  
select * from  student

数据库表列类型：

1.整数类型

 

整数类型	大小	表数范围（有符号）	表数范围（无符号）	作用
TINYINT	1字节	(-128，127)	(0，255)	小整数值
SMALLINT	2字节	(-32768，32767)	(0，65535)	大整数值
MEDIUMINT	3字节	(-8388608，8388607)	(0，16777215)	大整数值
INT/INTEGER	4字节	(-2147483648，2147483647)	(0，4294967295)	大整数值
BIGINT	8字节	(-9233372036854775808，9223372036854775807)	(0，18446744073709551615)	极大整数值

2.浮点数类型

浮点数类型	大小	作用
FLOAT	4字节	单精度浮点数值
DOUBLE	8字节	双精度浮点数值

3.字符串类型

字符串类型	大小	描述
CHAR(M)	0~255字符	允许长度0~M个字符的定长字符串
VARCHAR(M)	0~65535字符	允许长度0~M个字符的变长字符串
BINARY(M)	0~255字节	允许长度0~M个字节的定长二进制字符串
VARBINARY(M)	0~65535字节	允许长度0~M个字节的变长二进制字符串
TINYBLOB	0~255字节	二进制形式的短文本数据（长度为不超过255个字符）
TINYTEXT	0~255字节	短文本数据
BLOB	binary large object	二进制形式的长文本数据
TEXT		长文本数据
MEDIUMBLOB	0~16777215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0~16777215字节	中等长度文本数据
LOGNGBLOB	0~4294967295字节	二进制形式的极大文本数据
LONGTEXT	0~4294967295字节	极大文本数据

CHAR和VARCHAR类型相似，均用于存于较短的字符串，主要的不同之处在于存储方式。CHAR类型长度固定，VARCHAR类型的长度可变。

因为VARCHAR类型能够根据字符串的实际长度来动态改变所占字节的大小，所以在不能明确该字段具体需要多少字符时推荐使用VARCHAR类型，这样可以大大地节约磁盘空间、提高存储效率。

CHAR和VARCHAR表示的是字符的个数，而不是字节的个数

4.日期和时间类型                      

类型	格式	取值范围	0值
TIME	‘HH:MM:SS’	(‘-838:59:59’, ‘838:59:59’)	‘00:00:00’
DATE	‘YYYY-MM-DD’	(‘1000-01-01’, ‘9999-12-31’)	‘0000-00-00’
YEAR	YYYY	(1901, 2155), 0000	0000
DATETIME	‘YYYY-MM-DD HH:MM:SS’	(‘1000-01-01 00:00:00’, ‘9999-12-31  23:59:59’)	‘0000-00-00 00:00:00’
TIMESTAMP  时间戳	‘YYYY-MM-DD HH:MM:SS’	(‘1970-01-01 00:00:01’ UTC,   ‘2038-01-19 03:14:07’ UTC)	‘0000-00-00 00:00:00’

 

TIMESTEMP类型的数据指定方式与DATETIME基本相同，两者的不同之处在于以下几点：

(1) 数据的取值范围不同，TIMESTEMP类型的取值范围更小。

(2) 如果我们对TIMESTAMP类型的字段没有明确赋值，或是被赋与了NULL值，MySQL会自动将该字段赋值为系统当前的日期与时间。

(3) TIMESTEMP类型还可以使用CURRENT_TIMESTAMP来获取系统当前时间。

(4) TIMESTEMP类型有一个很大的特点，那就是时间是根据时区来显示的。例如，在东八区插入的TIMESTEMP数据为2017-07-11 16:43:25，在东七区显示时，时间部分就变成了15:43:25，在东九区显示时，时间部分就变成了17:43:25。

注意：查看MySQL支持的列类型 mysql> help data types

 

2.2 添加、更新、删除数据

 【示例2】添加、更新、删除数据

-- 添加数据  
insert into student values(1,'张三丰','男',34,'1256-12-23','武当一班','zhsf@wd.cn');  
insert into student values(1,'张三丰','男',34,'1256-12-23','武当一班','zhsf@wd.cn');  
insert into student values(1,'张三丰','男男',80,'1256-12-23','武当一班','zhsf@wd.cn');  
insert into student (sno,name) values(12345678,'张三丰');  
insert into student (sno,name,enterdate) values(1,'张三丰',now());  
insert into student (sno,name,enterdate) values(1,'张三丰',sysdate());  
insert into student (sno,name,enterdate) values(1,'张三丰',CURRENT_DATE());  
insert into student (sno,name,enterdate) values(1,null,"1256/12/23");  
insert into student set sno = 3, name='张无忌',sex='男',email='zhangwj@wd.cn'  
-- 查询数据  
select * from student  
-- 修改数据  
update student set sex ='男',age = 64 where sno = 12345678  
update student set sno = 2 where sno = 12345678  
-- delete必须后跟from  is null  不是 = null  
delete from student where email is null    
delete from student;  

注意事项

1. char varchar 是字符的个数，不是字节的个数，可以使用binary，varbinary表示定长和不定长的字节个数。
2. int  宽度是显示宽度，如果超过，可以自动增大宽度 int底层都是4个字节
3. 如何写入当前的时间   now()  sysdate()  CURRENT_DATE()
4. 时间的方式多样   '1256-12-23'   "1256/12/23"  "1256.12.23"
5. 字符串不区分单引号和双引号
6. delete语句中from不可少

2.3修改删除数据库表

【示例3】修改、删除表结构（不是数据）

-- 修改表的结构  
-- 1.增加一列  
alter table student add score double(4,1) ; -- 默认最后一列  
insert into student (score ) value (123.4 )  
-- double float 不可以超越长度限制  
insert into student (score ) value (123.4567 )   
alter table student add score double(4,1) first;  
alter table student add score double(4,1) after age;  
-- 2.删除一列  
alter table student drop score  
-- 3.修改一列  列名不变  
alter table student modify score double(5,2); -- 3+2  
-- 4.修改一列  列名改变  
alter table student change score score2 double(5,2)  
-- 5.修改表名   
alter table student rename to stu  
alter table stu rename student  
-- 查看表的结构  
desc student  
-- 查看数据  
select * from student;  
select * from stu;  
-- 删除数据库表  
drop table student; 

2.4 表的完整性约束

为防止不符合规范的数据存入数据库，在用户对数据进行插入、修改、删除等操作时，MySQL提供了一种机制来检查数据库中的数据是否满足规定的条件，以保证数据库中数据的准确性和一致性，这种机制就是完整性约束。

 

MySQL中主要支持六种完整性约束，如表所示。 其中Check约束是MySQL8中提供的支持。  

约束条件	约束描述
PRIMARY KEY	主键约束，约束字段的值可唯一地标识对应的记录
NOT NULL	非空约束，约束字段的值不能为空
UNIQUE	唯一约束，约束字段的值是唯一的
CHECK	检查约束，限制某个字段的取值范围
DEFAULT	默认值约束，约束字段的默认值
AUTO_INCREMENT	自动增加约束，约束字段的值自动递增
FOREIGN KEY	外键约束，约束表与表之间的关系

约束从作用上可以分为两类：

(1)   表级约束：可以约束表中任意一个或多个字段。与列定义相互独立，不包含在列定义中；与定义用‘，’分隔；必须指出要约束的列的名称；

(2)   列级约束：包含在列定义中，直接跟在该列的其它定义之后 ，用空格分隔；不必指定列名；

1.主键约束

主键约束（PRIMARY KEY，缩写PK），是数据库中最重要的一种约束，其作用是约束表中的某个字段可以唯一标识一条记录。因此，使用主键约束可以快速查找表中的记录。就像人的身份证、学生的学号等等，设置为主键的字段取值不能重复（唯一），也不能为空（非空），否则无法唯一标识一条记录。

主键可以是单个字段，也可以是多个字段组合。对于单字段主键的添加可使用表级约束，也可以使用列级约束；而对于多字段主键的添加只能使用表级约束。

2.非空约束

非空约束（NOT NULL，缩写NK）规定了一张表中指定的某个字段的值不能为空（NULL）。设置了非空约束的字段，在插入的数据为NULL时，数据库会提示错误，导致数据无法插入。

无论是单个字段还是多个字段非空约束的添加只能使用列级约束（非空约束无表级约束）

为已存在表中的字段添加非空约束

alter   table student8 modify stu_sex varchar(1) not null;

 

使用ALTER TABLE语句删除非空约束 

alter  table student8 modify stu_sex varchar(1) null;

 

3. 唯一约束

唯一约束（UNIQUE，缩写UK）比较简单，它规定了一张表中指定的某个字段的值不能重复，即这一字段的每个值都是唯一的。如果想要某个字段的值不重复，那么就可以为该字段添加为唯一约束。

无论单个字段还是多个字段唯一约束的添加均可使用列级约束和表级约束

4. 检查约束

       检查约束（CHECK）用来限制某个字段的取值范围，可以定义为列级约束，也可以定义为表级约束。MySQL8开始支持检查约束。

5. 默认值约束

默认值约束（DEFAULT）用来规定字段的默认值。如果某个被设置为DEFAULT约束的字段没插入具体值，那么该字段的值将会被默认值填充。

     默认值约束的设置与非空约束一样，也只能使用列级约束。

6. 字段值自动增加约束

自增约束（AUTO_INCREMENT）可以使表中某个字段的值自动增加。一张表中只能有一个自增长字段，并且该字段必须定义了约束（该约束可以是主键约束、唯一约束以及外键约束），如果自增字段没有定义约束，数据库则会提示“Incorrect table definition; there can be only one auto column and it must be defined as a key”错误。

由于自增约束会自动生成唯一的ID，所以自增约束通常会配合主键使用，并且只适用于整数类型。一般情况下，设置为自增约束字段的值会从1开始，每增加一条记录，该字段的值加1。

为已存在表中的字段添加自增约束

/*创建表student11*/
 create   table student11 (
       stu_id int(10) primary key,
       stu_name varchar(3),
       stu_sex varchar (1)
);
/*为student11表中的主键字段添加自增约束*/
alter   table student11 modify stu_id int(10) auto_increment;

使用ALTER TABLE语句删除自增约束 

alter   table studen11 modify stu_id int(10); 

【示例4】表的非外键约束

-- 列级约束  
create  table student(  
    sno int(4) primary key auto_increment,  
    name varchar(12) not null,  
    sex char(1)  default '男' check (sex ='男' or sex ='女'),  
    age int(3) check (age>18 and age<30),  
    enterdate date,  classname varchar(10),  
    email varchar(20) unique  
);  
drop  table student;  
-- 表级约束  
create  table student(  
    sno int(10),  
    name varchar(12) not null,  
    sex char(2)  default '男',  
    age int(2),  
    enterdate date,  
    classname varchar(20),  
    email varchar(20),  
    constraint pk_stu primary key(sno)   ,  
    constraint uk_stu_email unique(email),  
    constraint ck_stu_sex check (sex ='男'or sex='女'),  
    constraint ck_stu_age check(age>18 and age< 30)  
);  
insert into student (sno,name,sex,age)values(1,'zhangsan','aa',80)  
desc student  
alter table student drop  PRIMARY KEY  
-- 删除唯一约束()  
alter table student drop index uk_stu_email  

第三节DDL和DML

3.1 外键约束

外键约束（FOREIGN KEY，缩写FK）是用来实现数据库表的参照完整性的。外键约束可以使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。

外键是指表中某个字段的值依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束。被依赖的表我们通常称之为父表或者主表，设置外键约束的表称为子表或者从表。举个例子：如果想要表示学生和班级的关系，首先要有学生表和班级表两张表，然后学生表中有个字段为stu_clazz（该字段表示学生所在的班级），而该字段的取值范围由班级表中的主键cla_no字段（该字段表示班级编号）的取值决定。那么班级表为主表，学生表为从表，且stu_clazz字段是学生表的外键。通过stu_clazz字段就建立了学生表和班级表的关系。

【示例5】表的外键约束

-- 创建一个班级表  
create table class (  
    cno int(4) auto_increment,  -- 只有主键才可以自增  
    cname varchar(12) not null,  
    room  varchar(4),   
    primary key(cno)  
);  
-- insert into class values (null,'Java001',507);  
-- insert into class values (null,'Java002',502);  
-- insert into class values (null,'大数据001',401);  
insert into class values (null,'Java001',507),(null,'Java002',502),(null,'大数据001',401);  
-- 查询数据  
select * from class  
-- 创建一个学生表  
create table student2(  
    sno int(6) primary key auto_increment,  
    name varchar(12),  
    sex char(1),  
    age int(2),  
    classno int(4),  
    constraint fk_stu2_classno foreign key (classno) references class(cno)  
);  
desc student2;  
insert into student2 values(null,'zhangsan','男',23,1);  
insert into student2 values(null,'lisi','男',24,1);  
insert into student2 values(null,'wangwu','男',16,2);  
insert into student2 values(null,'zhaoliu','男',24,4);  --   
delete from class where cno = 1  
update class set cno = 5 where cno = 2  
select * from class  
select * from student2  
-- 如果想删除1班，请手动的先对1班的学生进行处理（删除或者清空外键）  
update student2 set classno = null where classno = 1  
delete from class where cno = 1  
-- 希望在更新班级编号的时候，可以直接更新学生的班级编号；希望在删除某个班级的时候，清空学生的班级编号  
-- 修改外键设置：外键要修改只能先删除再添加  
alter table student2 drop foreign key fk_stu2_classno; -- 注意提示的问题，其实已经删除了外键  
alter table student2 add constraint fk_stu2_classno foreign key (classno) references class(cno) on delete set null on update cascade  

【示例6】DML更多操作

select * from class    
select * from student2    
-- 1.快速创建数据库表  结构和数据完全相同    
create table class2      
AS    
select * from class    
select * from class2    
-- 2.快速创建数据库表  只要结构相同，不要数据    
create table class3    
AS    
select * from class where 1=2    
select * from class3    
-- 3.快速创建数据库表,只要部分结构    
create table student3    
as     
select sno,name,sex from student2  -- where 1=2    
select * from student3    
-- 添加数据    
insert into student3 values (5,'tianqi','男'),(6,'zhaoliu','女'), (7,'lisi','男')    
insert into student3 set sno = 8,name = 'lisi2' ;    
-- 删除所有数据    
delete from student3     
-- 删除所有数据时，更推荐使用该方式，效率高    
truncate table student3

从最终的结果来看，虽然使用TRUNCATE操作和使用DELETE操作都可以删除表中的全部记录，但是两者还是有很多区别的，其区别主要体现在以下几个方面：

(1)DELETE为数据操作语言DML；TRUNCATE为数据定义语言DDL。

(2) DELETE操作是将表中所有记录一条一条删除直到删除完；TRUNCATE操作则是保留了表的结构，重新创建了这个表，所有的状态都相当于新表。因此，TRUNCATE操作的效率更高。

(3)DELETE操作可以回滚；TRUNCATE操作会导致隐式提交，因此不能回滚（在第十章中会讲解事务的提交和回滚）。

(4)DELETE操作执行成功后会返回已删除的行数（如删除4行记录，则会显示“Affected rows：4”）；截断操作不会返回已删除的行量，结果通常是“Affected rows：0”。DELETE操作删除表中记录后，再次向表中添加新记录时，对于设置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增；TRUNCATE操作则会重新从1开始自增。