首页  思维导图  详情

MySQL数据库项目式教程

2022-05-23 14:36:07   0  举报





AI智能生成

MySQL数据库项目式教程

mysql底层原理

程序开发

项目计划

作者其他创作

大纲/内容

MySQL印象

什么是数据库(DB)？

数据库(Data Base)存放数据的仓库，用于有组织的采存储数据，相较于EXCEL而言它能够存储的数据量级要更高。

什么是结构化查询语言(SQL)?

SQL (Structured Query Language) 是一种具有数据操纵和数据定义等多种功能的数据库结构化查询语言。

什么是数据库管理系统(DBMS)

数据库管理系统（Database Manage System）数据库是通过DBMS来创建和操作，种类很多，当下较为常用的关系型数据库管理系统有Oracle、MySql、SQL sever。(PS: Oracle、MySql和SQL sever之间的sql语言虽不完全一致，但也有许多相似之处)。

三者之间的关系可以概括为：数据库管理系统（DBMS）使用SQL语句管理数据库（DB）

如何安装Mysql

MySQL的安装和配置(超详细图文教程)_清水-CSDN博客_mysql安装配置教程

SQL基础语句

DDL（数据库定义语言）

Creat：创建数据库和表

Drop: 删除数据库和表

Alter：修改数据库和表

DML（数据库操纵语言）

Insert：向表中插入数据

Delete：删除表中数据

Select:：查询表中数据

DCL（数据库控制语言）

Commit：确认对数据库中数据进行变更

Rollback：取消对数据库中数据进行变更

Crant：赋予用户权限

SQL书写规则

SQL语句以英文分号（;）结尾；

SQL语句不区分关键字大小写；

输入符号的时候只能使用英文的。

创建与维护MySQL数据表

数据库的结构及数据完整性设计

数据完整性简介：

数据冗余是指数据库中存在一些重复的数据，数据完整性是指数据库中的数据能够正确反应实际情况。

实体完整性：

实体的完整性强制表的标识符列或主键的完整性(通过唯一约束，主键约束或标识列属性)。

实体完整性的实现有两种方式：

主键约束：

一张表只能有一列设置主键，值必须唯一，不允许为空，innoDB存储引擎，主键就是索引。

主键 MySQL的主键名总是PRIMARY，当创建主键约束时，如果表的存储引擎是innoDB，系统默认会在所在的列和列组合上建立对应的唯一索引，

主键约束相当于唯一约束与非空约束的组合，主键约束列不允许重复，也不允许出现空值；多列组合的主键约束，列都不允许为空值，并且组合的值不允许重复。每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。

增加主键

删除主键

自增主键

唯一值约束：

一张表可以有多个列添加唯一值约束，一直允许一条记录为空值。

域完整性：

限制类型(数据类型)，格式(通过检查约束和规则)，可能值范围(通过外键约束，检查约束，默认值定义，非空约束和规则)。

默认值在表中插入一条新1的记录时，如果没有为该字段赋值，那么数据库系统会自动为该字段赋一条默认值。

给表中一列添加默认值约束

删除表中一列的默认值约束

创建非空约束

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。

Null类型特征：所有的类型的值都可以是null，包括int、float等数据类型空字符串是不等于NULL，0也不等于NULL。

check关键字，在插入新行或者更改已有行时才起作用，作用是阻止不满足条件的值进入该列，对null值无效，因为插入null就相当于没有插入。一个列可有多个check。

目前MySQL不支持check约束，微软MSSQL支持Check约束，但创建表时可以指定Check约束，但不起作用。

引用完整性：

在删除和输入记录时，引用完整性保持表之间已定义的关系。引用完整性确保键值在所有表中一致，不能引用不存在的值。如果一个键。

添加外键约束：foreign key
引用完整性会降低sql的执行效率，有时候能不用就不用。

使用语句创建数据表及约束

在mysql中，可以使用“CREATE TABLE”语句来创建数据表，语法格式“CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];”，其中“表定义选项”由列名、列的定义以及可能的空值说明、完整性约束或表索引组成。

创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性）约束的过程。接下来我们介绍一下创建数据表的语法形式。

在 MySQL 中，可以使用 CREATE TABLE 语句创建表。其语法格式为：
CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];

其中，[表定义选项]的格式为：
<列名1> <类型1> [,…] <列名n> <类型n>

修改删除数据表结构

MySQL数据库修改表结构的方法：1、使用add添加字段，使用drop删除字段；2、使用alter修改字段名；3、修改列类型；4、修改表名；5、修改表选项；6、修改列属性。

添加
Alter table 表名 add【column】字段名列类型列属性【first|after 字段名】

删除
Alter table 表名 drop【column】字段名;

修改字段名
语句：alter table 表名 change 原字段名新字段名列类型列属性;
说明：就算是仅修改字段名，那么字段的原始类型，原属性也要重新书写，不然就变为删除。

修改列类型
语句：alter table 表名 modify 字段名列类型列属性;

修改表选项
语句：alter table 表名表选项；
说明：虽然MYSQL提供了修改表选项的命令，但是如果一个表中已经有数据，那么就不要执行修改字符集的命令。

修改列属性
列属性包含not null、default、unique、primary key、auto_increment
当一个表创建以后，对于列属性的操作，我们可以使用alter table 表名 modify 来操作。在操作的时候如果书写了列属性就
是添加列属性，如果没有书写就是删除列属性。

数据插入、更新及删除

添加数据

SQL语句：
INSERT INTO 语句
1、为表中所有的字段添加数据
向表中添加的数据称之为记录。

insert语句中指定所有字段名

在insert 语句中列出表中的所有字段名，其值与其字段名、类型要一一对应！
语法格式：
INSERT INTO 表名（字段名1，字段名2，...）
VALUES(值1，值2，...);

insert语句中不指定字段名。

其实，他就是在不指定字段名的情况下，直接用values为其默认赋值。
注意：
正因为没有字段名，则values中值得顺序必须与字段在表中顺序一致。
基本语法：
INSERT INTO 表名 VALUES(值1，值2，....);

更新数据

更新数据，即对表中存在的数据进行修改。
SQL语句：
UPDATE 语句
基本语法：
UPDATE 表名
SET 字段名1=值1[,字段名2=值2，...]
[WHERE 条件表达式]

update更新部分数据

使用where子句指定更新条件，来更新表中的某一条或几条记录。

update 更新全部数据

很简单，即没有where 语句，就会将表中的所有记录的指定字段1都进行更新！
基本语法：
UPDATE 表名
SET 字段名1=值1[字段名2=值2，...];

删除数据

即，对表中存在的记录进行删除。
基本语法：
DELETE FROM 表名 [ WHERE 条件表达式 ] ;

表名指的是要执行删除操作的表。
where 条件表达式，可选参数，只要满足条件的记录会被删除！

DELETE 删除部分数据

根据指定条件删除表中的某一条或者某几条记录，需 WHERE 子句指定要删除的条件。

DELETE 删除全部数据

很简单，即没有 `WHERE 子句 ;
DELETE FROM 表名 ;

创建与维护MySQL数据库

使用命令语句创建修改和删除数据库式选择与查看数据库

创建数据库

在创建一个新的数据库之前，要先连接到 MySQL 服务器，然后执行 CREATE DATABASE 语句

如果数据库创建成功，将会出现 “Query OK, 1 row affected” 的提示信息

数据库创建成功后，MySQL 服务器会在其数据目录下创建一个目录，其名与数据库名相同，这个新目录被称为数据库目录。

查看数据库

要查看当前 MySQL 服务器上的数据库列表，使用以下命令

show databases

在所示的数据库列表中，information_schema 和 mysql 为系统数据库，test 为测试数据库，
information_schema 是信息数据库，其中保存着 MySQL 服务器所维护的所有其他数据库的信息。
mysql 数据库存储了 MySQL 的账户信息以及 MySQL 账户的访问权限，进而实现 MySQL 的账户的身份认证和权限验证，确保数据库安全。test 数据库则是安装时创建的一个测试数据库，是一个空数据库，其中没有任何表，可以删除

显示数据库

数据库创建好之后，可以使用以下 MySQL 命令来查看数据库的相关信息，如默认字符集等：

选择当前数据库

在进行数据库操作前，必须指定操作的是哪个数据库，即需要指定哪一个数据库为当前数据库。在使用 CREATE DATABASE 命令创建新的数据库后，新数据库并不会自动的成为当前数据库。使用以下命令进行指定：

use 数据库名;

删除数据库

如果要删除某个指定的数据库，如 bookstore 数据库，则使用如下命令：

drop database bookstore;

MySQL数据表的检索

查询时选择列

如果要查询某个表中的指定列的所有数据，则查询语句可以写作：
select 列名1,列名2,列名3... from
要说明一个，这个语句后面仍然可以使用where子句，用来选择指定行的指定列。这样可以更精准的查询出需要的结果来。

查询时选择行

查询第一行记录：

select * from table limit 1;

查询第n行到第m行记录，或者第n行

-- 2.1 查询连续的多行记录(第n~m行)
   select * from table1  limit n-1,m-n+1;
   -- 查询第6行到第15行的记录
   SELECT * FROM table LIMIT 5,10;
   -----------------------------------------
   -- 2.2 查询第n行
   select * from table1 limit n-1,1;
   -- 查询第5行
   select * from employee limit 4,1;
   -- 查询第10行
   select * from employee limit 9,1;

查询前n行记录

-- 方法一
select * from table1 limit 0,n;
-- 方法二
select * from table1 limit n;

查询后n行记录

-- 倒序排序，取前n行 id为自增形式
select * from table1 order by id desc dlimit n；

查询一条记录($id)的下一条记录

select * from table1 where id>$id order by id asc dlimit 1

查询一条记录($id)的上一条记录

select * from table1 where id<$id order by id desc dlimit 1

查询结果排序

ORDER BY 子句主要用来将结果集中的数据按照一定的顺序进行排序。

1.列名
指定用于排序的列。可以指定多个列，列名之间用逗号分隔。
2.表达式
指定用于排序的表达式。
3.位置
指定用于排序的列在 SELECT 语句结果集中的位置，通常是一个正整数。
4.ASC|DESC
关键字 ASC 表示按升序分组，关键字 DESC 表示按降序分组，其中 ASC 为默认值。这两个关键字必须位于对应的列名、表达式、列的位置之后。

使用 ORDER BY 子句应该注意以下几个方面

ORDER BY 子句中可以包含子查询。

当排序的值中存在空值时，ORDER BY 子句会将该空值作为最小值来对待。

当在 ORDER BY 子句中指定多个列进行排序时，MySQL 会按照列的顺序从左到右依次进行排序。

查询的数据并没有以一种特定的顺序显示，如果没有对它们进行排序，则将根据插入到数据表中的顺序显示。使用 ORDER BY 子句对指定的列数据进行排序。

查询的分总与汇总

聚集函数

AVG（）函数

AVG()通过对表中行数计数并计算特定列值之和，求得该列的平均值。

COUNT()函数

COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。 COUNT()函数有两种使用方式。

MAX()函数

MAX()返回指定列中的最大值。
MAX()要求指定列名

MIN()函数

MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。
MIN()要求指定列名

SUM()函数

SUM()用来返回指定列值的和（总计）。

创建多表连接查询

连接查询：同时涉及多个表的查询

连接条件：用来连接两个表的条件表1.字段=表2.字段

内连接 inner join

特点：两个表交叉的部分被查询出来

语法：select * from 表1 inner join 表2 on 表1.字段=表2.字段;
select 字段... from 表1 别名1 inner join 表2 别名2 on 连接条件;

左外连接 left[outer] join [outer]可以被省略

特点：左表中的记录全部出现在结果中，如果对应的某行记录在右表中没有对应关系，则右表会自动补空（值）

语法：select * from 表1 left join 表2 on 表1.字段=表2.字段;
select 字段... from 表1 别名1 left join 表2 别名2 on 连接条件;

右外连接 right[outer] join

特点：右表中的记录全部出现在结果中，如果对应的某行记录在左表中没有对应关系，则左表会自动补空

语法：select * from 表1 inner join 表2 on 表1.字段=表2.字段;
select 字段... from 表1 别名1 inner join 表2 别名2 on 连接条件;

交叉连接 cross join

特点：A表中的记录一一匹配B表中的所有记录

语法：select 字段... from 表1 cross join 表2 ;

where外键条件多表查询多个条件之间用and连接

特点：相当于内连接，效果一致

语法：select * from 表1 别名1,表2 别名2,表3 别名3... where 外键条件(关系) and 条件1 and 条件2...;

创建子查询

作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。假如需要显示 customers表中每个客户的订单总数。订单与相应的客户ID存储在 orders 表中。
为了执行这个操作，遵循下面的步骤。

从 customers 表中检索客户列表。

对于检索出的每个客户，统计其在 orders 表中的订单数目。

可使用 SELECT COUNT ( *) 对表中的行进行计数，并且通过提供一条 WHERE 子句来过滤某个特定的客户ID，可仅对该客户的订单进行计数。例如，下面的代码对客户 10001 的订单进行计数：

创建与使用视图

使用语句创建视图

基本语法

可以使用 CREATE VIEW 语句来创建视图。
语法格式如下：
CREATE VIEW <视图名> AS <SELECT语句>

语法说明如下。
<视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
<SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

对于创建视图中的 SELECT 语句的指定存在以下限制：
用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。
SELECT 语句不能引用系统或用户变量。
SELECT 语句不能包含 FROM 子句中的子查询。
SELECT 语句不能引用预处理语句参数。

视图定义中引用的表或视图必须存在。但是，创建完视图后，可以删除定义引用的表或视图。可使用 CHECK TABLE 语句检查视图定义是否存在这类问题。
视图定义中允许使用 ORDER BY 语句，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。
视图定义中不能引用 TEMPORARY 表（临时表），不能创建 TEMPORARY 视图。
WITH CHECK OPTION 的意思是，修改视图时，检查插入的数据是否符合 WHERE 设置的条件。

修改视图

可以使用 ALTER VIEW 语句来对已有的视图进行修改。
语法格式如下：
ALTER VIEW <视图名> AS <SELECT语句>
语法说明如下：
<视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
<SELECT 语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
需要注意的是，对于 ALTER VIEW 语句的使用，需要用户具有针对视图的 CREATE VIEW 和 DROP 权限，以及由 SELECT 语句选择的每一列上的某些权限。
修改视图的定义，除了可以通过 ALTER VIEW 外，也可以使用 DROP VIEW 语句先删除视图，再使用 CREATE VIEW 语句来实现。

视图是一个虚拟表，实际的数据来自于基本表，所以通过插入、修改和删除操作更新视图中的数据，实质上是在更新视图所引用的基本表的数据。
注意：对视图的修改就是对基本表的修改，因此在修改时，要满足基本表的数据定义。

某些视图是可更新的。也就是说，可以使用 UPDATE、DELETE 或 INSERT 等语句更新基本表的内容。对于可更新的视图，视图中的行和基本表的行之间必须具有一对一的关系。

利用视图更新数据表

视图是一个虚拟表，实际的数据来自于基本表，所以通过插入、修改和删除操作更新视图中的数据，实质上是在更新视图所引用的基本表的数据。

可以使用 ALTER VIEW 语句来对已有的视图进行修改。

语法格式如下：

ALTER VIEW <视图名> AS <SELECT语句>

视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。

<SELECT 语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

需要注意的是，对于 ALTER VIEW 语句的使用，需要用户具有针对视图的 CREATE VIEW 和 DROP 权限，以及由 SELECT 语句选择的每一列上的某些权限。

修改视图的定义，除了可以通过 ALTER VIEW 外，也可以使用 DROP VIEW 语句先删除视图，再使用 CREATE VIEW 语句来实现。

删除视图

可以使用 DROP VIEW 语句来删除视图。

语法格式如下：
DROP VIEW <视图名1> [ , <视图名2> …]

其中：<视图名>指定要删除的视图名。DROP VIEW 语句可以一次删除多个视图，但是必须在每个视图上拥有 DROP 权限。

创建与使用存储过程

创建简单存储过程

可以使用 CREATE PROCEDURE 语句创建存储过程，语法格式如下：
CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>

创建带输入参数的存储过程

call in_out_procedure(1,count_number); MySQL如何调用带参数的存储过程？ call
in_out_procedure(1,@param1); select @param1;

注意，带有输出out的存储过程，我们在使用时，需要先获得输出结果，这里我们使用参数param1，相当于是接收返回结果，然后就可以使用这个返回结果了。

创建带输入和输出参数的存储过程

mysql存储过程中，IN用来输入参数，OUT用来输出参数，INOUT输入和输出。
IN参数
创建存储过程

delimiter //
create procedure proce1(IN count int)
begin
select count;
set count = 2;
select count;
end
//

在上面存储过程中，我们定义了一个参数@count，赋值为1，传入到存储过程中，在存储过程中赋值为2，当存储过程结束后，打印@count的值依旧是1.

创建应用游标的存储过程

游标就类似于迭代器或者指针之类的东西，它指向第一条数据库中的记录，每取一次记录则游标向后移动一位

1、declare 申明 – declare 游标名 cursor for select statement

2、open 打开 --open 游标名

3、fetch 取值 --fetch 游标名 into var1,var2,[…]

4、close 关闭–close 游标名

3、游标要和select语句配合使用

创建与使用事务

A:atomicity 原子性
C:Consistency 一致性
I: Isolation 隔离性
D:durabiliity 可靠性

事务的基本写法
查看表引擎命令
show engines;

innodb支持事务,myisam不支持事务

事务的开启:
begin或者start transaction

创建与使用触发器

创建及使用触发器

触发器（TRIGGER）是MySQL的数据库对象之一，从5.0.2版本开始支持。该对象与编程语言中的函数非常类似，都需要声明、执行等。但是触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行。有点类似DOM中的事件。

那么为什么要使用数据库对象触发器呢？在具体开发项目时，经常会遇到如下实例：

<1> 在学生表中拥有字段学生姓名，字段学生总数，每当添加一条学生信息时，学生的总数就必须同时更改。

<2> 在学生表中还会有学生姓名的缩写，学生住址等字段，添加学生信息时，往往需要检查电话、邮箱等格式是否正确。

上面的例子使用触发器完成时具有这样的特点，需要在表发生改变时，自动进行一些处理。MySQL在触发DELETE/UPDATE/INSERT语句时就会自动执行所设置的操作，其他SQL语句则不会激活触发器。

创建触发器
使用帮助命令查看具体的语法：

CREATE

    [DEFINER = { user | CURRENT_USER }]

    TRIGGER trigger_name

    trigger_time trigger_event

    ON tbl_name FOR EACH ROW

    trigger_body

语法中

trigger_name：触发器的名称，不能与已经存在的触发器重复；

trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发；

trigger_event:：{ INSERT |UPDATE | DELETE }，触发该触发器的具体事件；

tbl_name：该触发器作用在tbl_name上；

查看及删除触发器

查看触发器

SHOW TRIGGERS 语句查看触发器信息

SHOW TRIGGERS;

在 triggers 表中查看触发器信息

删除触发器

DROP TRIGGER 触发器名；

DROP TRIGGER trig_book ;

DROP TRIGGER trig_book2 ;

数据库的安全性维护

添加数据库用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

username – 你将创建的用户名说明:

host – 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%

password – 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器

给予、回收数据库用户权限

授权

GRANT privileges ON databasename.tablename TO 'username'@'host';

privileges – 用户的操作权限,如SELECT , INSERT , UPDATE 等(详细列表见该文最后面).如果要授予所的权限则使用ALL说明:

databasename – 数据库名

tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用* 表示, 如*.*

用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

设置与更改用户密码

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

如果是当前登陆用户用

SET PASSWORD = PASSWORD("newpassword");