MySQL:关系型数据库管理系统

一、基础知识

 1.数据(data)

数据是指对客观事物描述并可以鉴别的符号，这些符号是可识别的，抽象的。它不仅仅指狭义上的文本(数字，字母，字符等)，数据还有多种表现形式：图形、音频、视频等。

2.为什么使用数据库系统

存储方式	优点	缺点
内存	速度快	不能够永久的保存，数据是临时状态的
文件	文件可以永久保存	使用I/O流操作文件，不方便查找与操作

刚开始我们使用变量存储数据，后面使用数组，再往后使用的是集合等去存储数据。这些方式存储数据都是将数据存储在了内存中，当程序运行完，或者断电后，数据就丢失了。

后来我们学习的xml文件，可以将数据存储在文件中，然后用的时候可以解析xml文件取数据。但是不方便，有点繁琐，而且不便于以后的查询。

所以，我们需要学习数据库，它就是专门用来存储数据的，而且不会有上面存在的各种问题！

3.数据库(database)

数据库（Database，简称DB），就是存储数据的仓库。

其本质就是一个文件管理，就是以文件的形式将数据保存在电脑的磁盘中。

4.数据库管理系统(DBMS)

数据库管理系统(Database Manage System,简称DBMS)：操作，管理及维护数据库的大型软件。MySQL就是数据库管理系统软件。安装了MySQL的电脑。也可以称为数据库服务器。

作用：用于建立，使用和维护数据库，对数据库进行统一的管理。

5.数据库应用程序

数据库应用程序(Database Application System,DBAS)是在数据库管理系统基础上。使用数据库管理系统的语法，开发的直接面对最终用户的应用程序。

6.数据库管理员

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

7.数据库的分类

 1.关系型数据库

关系型数据库是将复杂的数据结构用较为简单的二维表来表示，由二维表及其之间的联系所组成的一个数据组。

在该类型数据库中，对数据的基本操作基本上都建立在一张或多张表上，采用结构化查询语言(SQL)对数据库进行操作。关系型数据库是比较主流的数据库技术。

优点：

1.易于维护：使用二维表的表结构，格式一致；

2.使用方便：SQL语言通用，关系型数据库都可以使用SQl进行操作；

3.复杂操作：可用于一个表以及多个表之间非常复杂的查询。

缺点：

1.读写性能比较差，尤其是海量数据的高效率读写；

2.固定的表结构，灵活度稍欠；

3.高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。

2.非关系型数据库

非关系型数据库也称之为NoSQ(Not Only SQL)L数据库，是一种数据结构化存储方法的集合，可以是文档或者键值对等。

优点：

1.格式灵活：存储数据的格式可以是key，value形式，文档形式，图片形式等等。

2.使用灵活，应用场景广泛，而关系型数据库则只支持基础的类型。

3.速度快：使用内存存储数据，而关系型数据库只能使用磁盘

4.高扩展性

缺点：

1.不提供sql支持，学习和使用成本较高

2.数据结构相对复杂，复杂查询方面稍欠

3.只适合存储一些较为简单的数据，对于需要进行较复杂查询的数据，关系型数据库显得更为合适

4.不适合持久存储海量数据

3.MySQL小结

数据库管理系统、数据库和数据库表之间的关系？

MySQL中管理着很多的数据库，在实际开发中，一个数据库一般对应一个应用，每个数据库中又保存着很多表，每张表对应着不同的业务，每张表中保存着很多条数据。

2.SQL语言入门

 1.SQl基本概念

SQL（Structured Query Language）是结构化查询语言的简称，它是一种数据库查询和程序设计语言，同时也是目前使用最广泛的关系型数据库操作语言。在数据库管理系统中，使用SQL语言来实现数据的存取、更新等功能。

SQL是由IBM公司在1974~1979年之间根据E.J.Codd发表的关系数据库理论为基础开发的，其前身是“SEQUEL”，后更名为SQL。由于SQL语言具有数据查询、数据操纵、数据定义和数据控制功能、简单易用，得到了快速的发展，并于1986年10月，被美国国家标准协会（American National Standards Institute，ANSI）采用为关系数据库管理系统的标准语言，后为国际标准化组织（International Organization for Standardization，ISO）采纳为国际标准。

2.SQL作用

        是所有关系型数据库的统一操作规范，不同关系型数据库都支持SQL        

        所有的关系型数据库都可以使用SQL

        不同数据库之间的SQL有一些区别（方言）

3.SQL语法规范

        SQL语句可以是单行或多行书写，以;结尾（Navicat中可以不写；）

        可以使用空格或者缩进增加语句的可读性

        MySQL中使用SQL不区分大小写，一般数据库名、表名、列名小写

        注释方式

注释语句	说明
-- 空格	单行注释
/***/	多行注释
#	MySQL特有的单行注释

4.SQL分类

数据查询语句（Data Query Language，DQL）：

DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。

        select

        from

        where

        order by

        having

数据操作语言（Data Manipulation Language，DML）：

DML主要用于对数据 增加、修改和删除的操作。

主要包括：

        INSERT：增加数据

        UPDATE：修改数据

        DELETE：删除数据

数据定义语言（Data Definition Language，DDL）：

DDL主要用针对是数据库对象（数据库、表、索引、视图）进行创建，修改和删除操作。

主要包括：

        CREATE：创建

        ALTER：修改

        DROP：删除

数据控制语言（Data Control Language，DCL）：

DCL用来授予或回收访问数据库的权限。

主要包括：

        GRANT：授予用户权限

        REVOKE：回收授予的权限。

事务控制语言（Transaction Control Language，TCL）：

TCL用于数据库的事务管理。

主要包括：

        START TRANSACTION：开启事务

        COMMIT：提交事务

        ROLLBACK：回滚事务

注意：

数据操纵语言DML（insert、update、delete）针对表中的数据 ；

而数据定义语言DDL（create、alter、drop）针对数据库对象，比如数据库database、表table、索引index、视图view、存储过程procedure；

二、DDL操作

1.DDL操作数据库

-- 查看所有的数据库
show databases;

-- 创建数据库
create database bjsxt01;

-- 选择数据库
use bjsxt01;

-- 删除数据库
drop database bjsxt01;

2.DDL操作数据库表

 1.详细数据类型

1.整数类型

MySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如，INT(4))。显示宽度并不限制可以在列内保存的值的范围.

2.浮点数类型

注意事项： 浮点数类型的宽度不会自动扩充，使用时需要指定长度。 如：score double(4,1) 总体长度是4 精度为1位。

3.字符串类型

1. char长度固定， 即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定长。

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

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

4. text不设置长度， 当不知道属性的最大长度时，适合用text。

按照查询速度： char最快， varchar次之，text最慢。

4.日期和时间类型

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

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

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

2.DDL-创建数据库表

create table 表名(
	字段1	数据类型,
    字段2 数据类型(长度)
);
-- 注意：最后一个字段不要加逗号，可以给字段的数据类型指定长度

3.DDL-删除数据库表

-- 语法格式：
DROP TABLE 表名;

-- 删除当前数据库中的某个表  drop table 表名
drop table student;

4.DDL-修改数据库表

1.修改表名

-- 语法格式：
rename table 旧表名 to 新表名;

-- 修改student表名为stu
rename table student to stu;

-- 显示所有表
show tables;

2.向表中添加字段

-- 语法格式：
alter table 表名 add 字段名称 字段类型;

-- 案例：向学生表中添加字段address，长度为50
alter table stu add address varchar(50); -- 默认添加到最后
alter table stu add address2 varchar(50) first; -- 添加到第一个
alter table stu add address3 varchar(50) after age; -- 添加到age字段之后

-- 查看表结构
desc stu;

3.修改表中字段

-- 语法格式： 
alter table 表名 modify 字段名称 字段类型;(修改字段类型和长度)

-- 案例：修改学生表中的字段address，长度改为30
alter table stu modify address varchar(30);

-- 语法格式：
alter table 表名 change 旧字段名 新字段名 类型(长度); (修改字段名 )

-- 案例：修改学生表中的字段address，修改为city，长度为10
alter table stu change address city varchar(10);

4.删除表中字段

-- 语法格式：
alter table 表名 drop 字段名;

-- 案例：删除学生表中 address2字段
alter table stu drop address2;

三、DML操作

 1.插入数据

-- 语法格式：
-- 1. 插入部分或全部字段 
insert into 表名(字段名1,字段名2,...,字段名n) values(值1,值2,...,值n);
-- 2. 插入全部字段 
insert into 表名 values(值1,值2,...,值n);

-- 案例：向学生表中插入数据
-- 方式1：插入全部字段，将所有字段名都写出来
insert into stu(sid,sname,sex,age,createdate,clazz,email) values(1,'张飞','男',50,'1990-05-05','1班','zhangfei@qq.com');

-- 方式2：插入全部字段，可以不写字段名
insert into stu values(2,'刘备','男',30,'1995-06-06','2班','liubei@qq.com');

-- 方式3：插入指定字段的值
insert into stu(sid,sname,age,createdate) values(3,'赵云',19,now());
insert into stu(sid,sname,createdate) values(4,'曹操',sysdate());
insert into stu(sid,createdate) values(5,current_date());

-- 一次添加多条数据
insert into 表名 values(值1,值2,...,值n),(值1,值2,...,值n),(值1,值2,...,值n);

注意事项：

1. 值与字段必须要对应，个数相同、数据类型相同

2. 值的数据大小，必须在字段指定的长度范围内

3. varchar char date类型的值使用单引号或者双引号包裹

4. 如果要插入空值，可以忽略不写或者写null

5. 如果插入指定字段的值，必须要上写列名

6. 字符串日期类型支持 "2000-12-23" "2000/12/23" "2000.12.23"

2.修改数据

-- 语法格式
update 表名 set 字段1=值1,字段2=值2,...,字段n=值n where 条件;
update 表名 set 字段1=值1,字段2=值2,...,字段n=值n; -- 慎用：不加条件会修改所有的数据

-- 案例1：将sid=1的学生性别改为女
update stu set sex='女' where sid=1;

-- 案例2：将sid=2的学生性别改为女，年龄改为30，班级改为11班
update stu set sex='女',age=30,clazz='11班' where sid=2;

3.删除数据

-- 语法格式
delete from 表名 where 条件;
delete from 表名; -- 慎用：删除表中的所有数据
truncate table 表名; -- 慎用：删除表中的所有数据

-- 案例1：删除sid=5的学生信息
delete from stu where sid=5;

-- 案例2：删除stu表中所有的数据
-- 方式一：
delete from stu;
-- 方式二：
truncate table stu; -- 推荐使用

truncate 和 delete的对比：

1. 这两种操作都可以删除表中的数据

2. delete为数据操作语言DML；truncate 为数据定义语言DDL。

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

4. delete操作可以回滚；truncate 操作会导致隐式提交，因此不能回滚（后面会讲解事务的提交和回滚）。

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

 四、表的约束

 1.约束的作用

可以对表中的数据做一些限制, 从而保证数据的正确性, 有效性, 和完整性。

违反约束的不正确数据是无法插入到表中的。

2.约束的分类

约束名	关键字	作用
主键	primary key	不可重复，不能为空，即唯一+非空
唯一	unique	不可重复
非空	not null	不能为空
默认值	default	赋予默认值
检查(Mysql8提供)	check	取值范围
外键	foreign	表与表之间的关系

3.主键约束

1.添加主键约束

-- 语法格式：
字段名 字段类型 primary key

-- 案例1：创建一个带有主键的emp表 字段 eid int  ename varchar(10)  esex char(1)
-- 方式一
create table emp(
	eid int primary key,  -- 设置主键 非空唯一
	ename varchar(10),
	sex char(1)
);

-- 方式二
drop table emp; -- 删除emp表
create table emp(
	eid int,
	ename varchar(10),
	sex char(1),
	primary key(eid)  -- 指定主键为eid字段
);

-- 方式三
drop table emp; -- 删除emp表
create table emp(
	eid int primary key,
	ename varchar(10),
	sex char(1)
);
-- 创建表时不指定主键, 然后通过DDL语句进行设置
alter table emp add primary key(eid);

2.测试主键的唯一和非空

-- 插入正常数据
insert into emp values(1,"宋江","男");

-- 插入一条数据, 主键为空
insert into emp values(null,"李逵","男");
-- Column 'eid' cannot be NULL 主键不能为空 

-- 插入一条数据, 主键重复
insert into emp values(1,"孙二娘","女");
-- Duplicate entry '1' for key 'emp.PRIMARY' 主键冲突

3.哪些字段可以作为主键？

        

• 通常针对业务去设计主键, 每张表都设计一个主键id, 并且只能有一个主键.

• 主键是给数据库和程序使用, 所以主键没有意义没有关系, 只要能保证不重复就好, 比如身份证就可以作为主键.

4.删除主键约束

-- 使用DDL 删除表中的主键约束
alter table emp drop primary key;

-- 查看表结构
desc emp;

-- 删除唯一约束(了解)
-- 添加了唯一约束为 eid int not null, 通过设置字段属性删除唯一约束 
alter table emp modify eid int;

5.主键自增

自己添加数据可能出现重复, 我们通常希望在每次插入新记录时, 数据库自动生成主键字段的值。

-- 语法格式: 
关键字 auto_increment	 表示自增长(字段类型必须为整数类型)

-- 案例：为emp表eid字段添加主键约束, 并设置为自增
drop table emp;
create table emp(
	eid int primary key auto_increment,
	ename varchar(10),
	sex char(1)
);
-- 测试主键自增
insert into emp values(null,'张三','男');
insert into emp values(default,'李四','男');
insert into emp(ename, esex) values('王五', '男');

 4.非空约束

-- 语法格式: 字段名 字段类型 not null

-- 案例：创建emp表，eid为主键约束,自增，ename为非空约束
-- 删除存在的emp表 
drop table emp;
create table emp(
	eid int primary key auto_increment,
	ename varchar(10) not null,
	sex char(1)
);
-- 测试非空约束
insert into emp values(default,null,'n');
-- Column 'ename' cannot be null  ename不能为空

5.唯一约束

-- 语法格式： 
字段名 字段类型 unique

-- 案例：emp表 eid 主键约束,自增  ename 非空约束 esex唯一约束
drop table emp; -- 删除存在的emp表 
create table emp(
	eid int primary key auto_increment,
	ename varchar(10) not null,
	sex char(1) unique
);
-- 测试唯一约束 添加两个性别为男
insert into emp values(default,'zs','男');
insert into emp values(default,'lisi','男');
-- Duplicate entry 'n' for key 'emp.esex'  esex重复

6.默认值约束

-- 语法格式： 字段名 字段类型 default '值'

-- 案例：emp表 eid 主键约束,自增  ename 非空约束 esex默认值'男'
-- 删除存在的emp表 
drop table emp;
create table emp(
	eid int primary key auto_increment,
	ename varchar(10) not null,
	sex char(1) default '男'
);
-- 查看emp表结构 
desc emp;
-- 测试默认值约束
insert into emp values(default,'zs',default);

-- 错误用法
insert into emp values(default,'zs',null); 

7.检查约束

语法格式：

字段名 字段类型 check(字段='值' or 字段='值')
字段名 字段类型 check(字段>n or 字段<m)
字段名 字段类型 check(字段>n and 字段<m)

案例：emp表 eid 主键约束, 自增 ename 非空约束 esex默认值'男'并检查 男|女

-- 删除存在的emp表 
drop table emp;
create table emp(    
    eid int primary key auto_increment,    
    ename varchar(10) not null,    
    esex char(1) default '男' check(esex='男' or esex='女')
);
-- 查看emp表结构 
desc emp;
-- 测试检查约束
insert into emp values(default,'lisi','n');

-- Check constraint 'emp_chk_1' is violated 不符合字段要求