mysql汇总

JokerDJ

发布于 2023-11-27 13:24:46

6440

文章被收录于专栏：JokerDJJokerDJ

数据库mysql

数据库介绍

什么是数据库

数据库是是按照数据结构来组织、存储和管理数据的仓库

数据库的发展史

最早的数据库:

通过大量的分类、比较和表格绘制的机器运行数百万穿孔卡片来进行数据的处理，其运行结果在纸上打印出来或者制成新的穿孔卡片。而数据管理就是对所有这些穿孔卡片进行物理的储存和处理。

现在的数据库

当时计算机开始广泛地应用于数据管理，对数据的共享提出了越来越高的要求。传统的文件系统已经不能满足人们的需要。能够统一管理和共享数据的数据库管理系统（DBMS）应运而生。

数据库管理系统DBMS

数据库是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。
它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。用户通过DBMS访问数据库中的数据，数据库管理员也通过dbms进行数据库的维护工作。
数据库管理系统是数据库系统的核心，是管理数据库的软件。
我们一般说的数据库,就是指的DBMS: 数据库服务器

常见的数据库

Oracle：运行稳定，可移植性高，功能齐全，性能超群！适用于大型企业领域。
DB2：速度快、可靠性好，适于海量数据，恢复性极强。适用于大中型企业领域。
MySQL：开源，体积小，速度快。适用于于中小型企业领域。
SQL Server：全面，效率高，界面友好，操作容易，但是不跨平台。适用于于中小型企业领域。

结构化查询语言SQL

SQL语言的功能包括查询、操纵、定义和控制，是一个综合的、通用的关系数据库语言，同时又是一种高度非过程化的语言，只要求用户指出做什么而不需要指出怎么做。

非关系型数据库NoSql

主要包含：MongoDB,Redis,HBase…

RDBMS专业术语

表：具有固定的列数,和任意的行数

数据库：数据库是一些关联表的集合
列：一个数据项 Field 字段

行：一条记录 row

主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

MySql介绍

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
Mysql是开源的，所以你不需要支付额外的费用。
Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL使用标准的SQL数据语言形式。
Mysql可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。

MySql8安装 (windows安装)

下载地址：https://dev.mysql.com/downloads/mysql/

下载完成后打开

步骤：

打开安装包，第一步选择自定义

只需要安装mysql8的服务器

点击Execute

点击下一步

mysql的端口默认是3306 根据自己需求可以进行更改

继续下一步

输入数据库的密码（自定义），默认最高用户是root,可以自己添加用户

下一步

点击安装

安装成功后看C盘是否有MYSQL的文件夹，有的话就安装完成了

配置mysql的环境变量右键此电脑>点击属性>高级系统设置

系统环境变量 path

点击确定，保存打开cmd 输入 mysql -uroot -p密码

MySql的存储引擎

什么是存储引擎：

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。不同的存储引擎性能是不一样的

什么是事务

是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

存储引擎分类

MYISAM：

它不支持事务，也不支持外键，尤其是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。
每个MyISAM在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分别为： .frm(存储表定义) MYD(MYData，存储数据) MYI(MYIndex，存储索引)

INNODB：

InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

MEMORY：

memory使用存在内存中的内容来创建表。每个MEMORY表实际对应一个磁盘文件，格式是.frm
MEMORY类型的表访问非常快，因为它到数据是放在内存中的，并且默认使用HASH索引，但是一旦服务器关闭，表中的数据就会丢失，但表还会继续存在。

mysql的结构

MySql数据库分为两种：

系统数据库(数据库服务器自带的,自带的4个)

information_schema：存储数据库对象信息，如用户表信息,列信息,根除,字符,分区，里面的内容我们不能动.
performance_schema：存储数据库服务器性能参数信息
mysql：存储数据库用户权限信息
sys：通过这个库可以快速的了解系统的元数据信息这个库是通过视图的形式把information_schema 和performance_schema结合起来，查询出更加令人容易理解的数据
用户数据库：用户自己创建的数据库,一个项目用一个数据库

常用数据库命令

创建数据库：create database 数据库名称;
删除数据库：drop database 数据库名称;
查看数据库：show databases;
使用数据库：use 数据库名;
查看数据库当中有多少张表：show tables;

注意写的顺序,先使用数据库,再去查有多少张表

举例：查看数据库

使用数据库：

使用Navicate图形化界面工具

官网地址：http://www.navicat.com.cn/download/direct-download?product=navicat_mysql_cs_x64.exe&location=1&support=Y 网盘地址： https://pan.baidu.com/s/1_z9TbieMX9Kemnyj5WWrBQ 提取码：4dgw

字符集

1. 字符集的由来：

计算机只能识别二进制代码无论是计算机程序还是数据，最终都会转换成二进制，计算机才能认识。
为了计算机不只能做科学计算，也能处理文字信息。人们想出了给每一个文字符号编码以便于计算识别处理的办法，这就是计算机字符集的由来。

2. ASSCII

一套文字符号及其编码,比较规则的集合。 20世纪60年代初。美国标准化组织ANSI发布了第一个字符集。ASCII 后来又进一步变成了国际标准ISO-646

各大字符集: 自ASCII后。为了处理不同的文字。各大计算机公司，各国，标准化政府，组织先后发明了几百种字符集。
ISO-8859
GB2312-80
GBK
BIG5

2. unicode

为了统一字符编码。国际标准化组织ISO的一些成员国于1984年发起制定了新的国际字符集标准。容纳全世界各种语言，文字，和符号。最后这个标准ISO-10646
ISO-10646发布后，遭到了美国计算机公司的反对。
1988年，Xerox公司提议制定了新的以16位编码人统一字符集。并联合不Apple,IBM,SUN，Microsoft等公司成立了Unicode技术委员会。专门负责收集，整理，和编码。于1991年推出了Unicode1.0
都是为了字符编码统一问题，ISO和Unicode协会推出了连个不同人标准。这显然是不利的。后来双方开始谈判。1991年10月达成协议。ISO将Unicode收编。起了个名BMP

3. UTF-16

ISO-10646编码空间足以容纳从古自今使用过的文字和字符。但很多文字字符已经很少用了。超过99%的在用文字字符都编入了BMP.因此，绝大部分情况下。 Unicode双字节方式都能满足需求。而且比双字节编码方式4字节原始编码来说，更节省内存和处理时间。这也是Unicode流行的原因。
万一使用了BMP以这后文字怎么办？ Unicode提出了UTF-16的解决办法。

4. UTF-8

虽然UTF-16解决了上面问题。但当时的计算机和网络世界还是ASCII的天下。只能处理单字节数据流。UTF-16离开了Unicode环境后。在传输和处理中，都存在问题。
于是又提出了UTF-8的解决文案，
UTF-8按一定的规则，将一个ISO10646或Unicode转换成1至4个字节的编码
其中ASCII转成单字节编码。也就严格兼容了ASCII字符集。
UTF-8的2，3，4字节用以转换ISO-10646标准的UCS-4原始码。

5. 汉字的一些常见字符集

GB2312
GB13000
GBK
GB18030

什么是SQL

SQL是Structured Quevy Language(结构化查询语言)的缩写。
SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。
在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的。

sql功能分类

1. DDL:数据定义语言

用来定义数据库对象：创建库，表，列等。

2. DML：数据操作语言

用来操作数据库表中的记录

3. DQL：数据查询语言

用来查询数据

4. DCL：数据控制语言

用来定义访问权限和安全级别

SQL数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。 MySQL支持所有标准SQL数值数据类型。

MySQL支持多种类型，大致可以分为三类:

1. 数值类型

2. 字符串类型

3. 日期和时间类型

常用数据类型

double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
char：固定长度字符串类型； char(10) 'abc ’
varchar：可变长度字符串类型；varchar(10) ‘abc’
text：字符串类型;
blob：二进制类型；
date：日期类型，格式为：yyyy-MM-dd；
time：时间类型，格式为：hh:mm:ss
datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

在mysql中，字符串类型和日期类型都要用单引号括起来。‘Joker’ ‘2020-01-01’

SQL语句

使用Navicate图形化界面工具连接数据库：

DDL:数据定义语言

1. 创建数据库

create database 数据库名  character set utf8;

2. 创建学生表

use joker;
create table student( -- 创建数据表
Id bigint,
name varchar(20),
age int
);

3. 添加一列

ALTER TABLE 表名 ADD 列名 数据类型;

4. 查看表的字段信息

DESC 表名;

5. 修改一个表的字段类型

ALTER TABLE 表名 MODIFY 字段名  数据类型;

6. 删除一列

ALTER TABLE 表名 DROP 字段名;

7. 修改表名

RENAME TABLE 原始表名 TO  要修改的表名;

8. 查看表的创建细节

SHOW CREATE TABLE 表名;

9. 修改表的列名

ALTER TABLE 表名 CHANGE 原始列名 新列名 数据类型;

10. 删除表

DROP TABLE 表名;

1. 修改表的字符集为gbk

ALTER TABLE 表名 CHARACTER SET 字符集名称;

DML：数据操作语言

1. 查询表中的所有数据

SELECT * FROM 表名;

2. 插入操作

INSERT INTO 表名（列名1，列名2 ...）VALUE (列值1，列值2...);

注意事项:

列名与列值的类型、个数、顺序要一一对应。
值不要超出列定义的长度。
插入的日期和字符一样，都使用引号括起来。

批量插入

INSERT INTO 表名（列名1，列名2 ...）VALUES (列值1，列值2...)，(列值1，列值2...);

3. 更新操作

UPDATE 表名 SET 列名1=列值1，列名2=列值2 ... WHERE 列名=值

把所有学生的年龄改为20

update students set age=20

把姓名为小键的年龄改为18

update students set age=18 where name='小键';

把小吴的年龄在原来基础上加1岁

update students set age=age+1 where name='小吴';

修改数据库密码 mysql8之前:

use mysql;
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
flush privileges;刷新MySQL的系统权限相关表

mysql8:

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

使用mysqladmin修改密码:

mysqladmin -u root -p password 123456

4. 删除操作

删除表

DELETE FROM 表名 【WHERE 列名=值】

TRUNCATE TABLE 表名;

DELETED 与TRUNCATE的区别

DELETE 删除表中的数据，表结构还在;删除后的数据可以找回
TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快。

DQL：数据查询语言

1. 查询所有列

SELECT * FROM 表名;

2. 结果集

数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。结果集：

通过查询语句查询出来的数据以表的形式展示我们称这个表为虚拟结果集。存放在内存中。
查询返回的结果集是一张虚拟表。

3. 查询指定列的数据

SELECT 列名1，列表2... FROM 表名;

4. 条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用一些运算符及关键字：

=（等于）、!=（不等于）、<>（不等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）；
BETWEEN…AND；值在什么范围
IN(set)；固定的范围值
IS NULL；（为空） IS NOT NULL（不为空）
AND；与
OR；或
NOT；非
查询性别为男，并且年龄为20的学生记录

SELECT * FROM students WHERE gender='男' AND age=20;

查询学号为1001 或者名为zs的记录

SELECT * FROM students WHERE id ='1001' OR name='zs';

查询学号为1001，1002，1003的记录

SELECT * FROM students WHERE id='1001' OR id='1002' OR 1001='1003';

SELECT * FROM students WHERE id  IN('1001','1002','1003');

查询年龄为null的记录

SELECT * FROM students WHERE age IS NULL;

查询年龄在18到20之间的学生记录

SELECT * FROM students WHERE age>=18 AND age<=20;

SELECT * FROM students WHERE age BETWEEN 18 AND 20;

查询性别非男的学生记录

SELECT * FROM students WHERE gender !='男';

查询姓名不为null的学生记录

SELECT * FROM students WHERE  name IS NOT  NULL;

5. 模糊查询

根据指定的关键进行查询, 使用LIKE关键字后跟通配符通配符: _ ：任意一个字符 %：任意0~n个字符

查询姓名由5个字母构成的学生记录

SELECT * FROM students WHERE name LIKE '_____';
-- 模糊查询必须使用LIKE关键字。其中 “_”匹配任意一个字母，5个“_”表示5个任意字母。

查询姓名由5个字母构成，并且第5个字母为“s”的学生记录

SELECT * FROM students WHERE name LIKE '____s';

查询姓名以“m”开头的学生记录

SELECT * FROM students WHERE name LIKE 'm%';
-- 其中“%”匹配0~n个任何字母。

查询姓名中第2个字母为“u”的学生记录

SELECT * FROM students WHERE name LIKE '_u%';

查询姓名中包含“s”字母的学生记录

SELECT * FROM stu WHERE name LIKE '%s%';

6. 字段控制查询

去除重复记录

SELECT DISTINCT name FROM students;

把查询字段的结果进行运算，必须都要是数据型

SELECT *,字段1+字段2 FROM 表名;

列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

SELECT *,age+IFNULL(score,0) FROM students;

对查询结果起别名

在上面查询中出现列名为sx+IFNULL(yw,0)，这很不美观，现在我们给这一列给出一个别名，为total：

SELECT *, yw+IFNULL(sx,0) AS total FROM score;

SELECT *, yw+IFNULL(sx,0)  total FROM score; -- 省略 AS

7. 排序

创建表:

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `gender` varchar(1) DEFAULT NULL,
  `hire_date` date DEFAULT NULL,
  `salary` decimal(10,0) DEFAULT NULL,
  `performance` double(255,0) DEFAULT NULL,
  `manage` double(255,0) DEFAULT NULL,
  `department` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `employee` VALUES (1001, '张三', '男', '1991-7-25', 2000, 200, 500, '营销部');
INSERT INTO `employee` VALUES (1002, '李四', '男', '2017-7-5', 4000, 500, NULL, '营销部');
INSERT INTO `employee` VALUES (1003, '王五', '女', '2018-5-1', 6000, 100, 5000, '研发部');
INSERT INTO `employee` VALUES (1004, '赵六', '男', '1991-6-1', 1000, 3000, 4000, '财务部');
INSERT INTO `employee` VALUES (1005, '孙七', '女', '2018-3-23', 8000, 1000, NULL, '研发部');
INSERT INTO `employee` VALUES (1006, '周八', '男', '2010-9-8', 5000, 500, 1000, '人事部');
INSERT INTO `employee` VALUES (1007, '吴九', '女', '2017-7-5', 8000, 601, NULL, '研发部');
INSERT INTO `employee` VALUES (1008, '郑十', '女', '2014-4-6', 4000, 1801, NULL, '人事部');

对查询的结果进行排序使用关键字ORDER BY 排序类型

升序ASC 从小到大默认
降序DESC 从大到小
对所有员工的薪水进行排序

SELECT *FROM employee ORDER BY salary ASC;

查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序

SELECT * FROM employee ORDER BY salary DESC, id ASC;

8.聚合函数

对查询的结果进行统计计算常用聚合函数:

COUNT()：统计指定列不为NULL的记录行数；
MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

1. COUNT

查询employee表中记录数：

SELECT COUNT(*) AS total_record FROM employee;

查询员工表中有绩效的人数

SELECT COUNT(performance)  FROM employee;

查询员工表中月薪大于2500的人数：

SELECT COUNT(*) FROM employee WHERE salary > 2500;

统计月薪与绩效之和大于5000元的人数：

SELECT COUNT(*)  FROM employee WHERE salary+IFNULL(performance,0) > 5000;

查询有绩效的人数，和有管理费的人数：

SELECT COUNT(performance), COUNT(manage) FROM employee;

2. SUM和AVG

查询所有雇员月薪和：

SELECT SUM(salary) FROM employee;

查询所有雇员月薪和，以及所有雇员绩效和

SELECT SUM(salary), SUM(performance) FROM employee;

查询所有雇员月薪+绩效和：

SELECT SUM(salary+IFNULL(performance,0)) FROM employee;

统计所有员工平均工资：

SELECT AVG(salary) FROM employee;

3. MAX和MIN

查询最高工资和最低工资：

SELECT MAX(salary), MIN(salary) FROM employee;

9. 分组查询

什么是分组查询

将查询结果按照1个或多个字段进行分组，字段值相同的为一组

分组使用

SELECT gender from employee GROUP BY gender;

根据gender字段来分组，gender字段的全部值只有两个(‘男’和’女’)，所以分为了两组当group by单独使用时，只显示出每组的第一条记录所以group by单独使用时的实际意义不大

group by + group_concat()

group_concat(字段名)可以作为一个输出字段来使用表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合

SELECT gender,GROUP_CONCAT(name) from employee GROUP BY gender;

group by + 聚合函数

通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个"值的集合"做一些操作

查询每个部门的部门名称和每个部门的工资和

SELECT department,SUM(salary) FROM employee GROUP BY department;

查询每个部门的部门名称以及每个部门的人数

SELECT department,COUNT(*) FROM employee GROUP BY department;

查询每个部门的部门名称以及每个部门工资大于1500的人数

SELECT department,COUNT(salary) FROM employee WHERE salary > 1500 GROUP BY department;

group by + having

用来分组查询后指定一些条件来输出查询结果 having作用和where一样，但having只能用于group by

查询工资总和大于9000的部门名称以及工资和

SELECT department,GROUP_CONCAT(salary) FROM employee GROUP BY department;
	
SELECT department,SUM(salary) FROM employee GROUP BY department;

总和大于9000

SELECT department,SUM(salary) FROM employee GROUP BY department HAVING SUM(salary)>9000;

having与where的区别

having是在分组后对数据进行过滤. where是在分组前对数据进行过滤 having后面可以使用分组函数(统计函数) where后面不可以使用分组函数 WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

查询工资大于2000的，工资总和大于6000的部门名称以及工资和

查询工资大于2000的

SELECT * FROM employee WHERE salary >2000;

各部门工资

SELECT department, GROUP_CONCAT(salary) FROM employee WHERE salary >2000 GROUP BY department;

各部门工资总和

SELECT department, SUM(salary) FROM employee WHERE salary >2000 GROUP BY department;

各部门工资总和大于6000

SELECT department, SUM(salary) FROM employee WHERE salary >2000 GROUP BY department HAVING SUM(salary)>6000;

各部门工资总和大于6000降序排列

SELECT department, SUM(salary) FROM employee 
WHERE salary >2000 
GROUP BY department 
HAVING SUM(salary)>6000 
ORDER BY SUM(salary) DESC;

10. LIMIT

从哪一行开始查，总共要查几行 Limit 参数1，参数2

参数1：从哪一行开始查
参数2：一共要查几行

角标是从0开始

格式：

select * from 表名 limit 0,3;

11. 书写顺序

书写顺序

执行顺序

数据库的完整性

什么是数据的完整性

保证用户输入的数据保存到数据库中是正确的。

如何添加数据完整性

在创建表时给表中添加约束

完整性分类

实体完整性
域完整性
参照完整性

实体完整性

什么是实体完整性

表中的一行(一条记录)代表一个实体（entity）

实体完整性的作用

标识每一行数据不重复。行级约束

约束类型

主键约束（primary key）
唯一约束(unique)
自动增长列(auto_increment)
主键约束

特点：

每个表中要有一个主键
数据唯一，且不能为null

添加主键约束的方式

CREATE TABLE 表名(字段名1 数据类型 primary key,字段2 数据类型);
CREATE TABLE 表名(字段1 数据类型, 字段2 数据类型,primary key(要设置主键的字段));
CREATE TABLE 表名(字段1 数据类型, 字段2 数据类型,primary key(主键1，主键2));

联合主键：两个字段数据同时相同时，才违反联合主键约束。

1.先创建表 2.再去修改表，添加主键

ALTER TABLE student  ADD CONSTRAINT  PRIMARY  KEY (id);

唯一约束：

特点：

指定列的数据不能重复
可以为空值

CREATE TABLE 表名(字段名1 数据类型 字段2 数据类型 UNIQUE);

自动增长列

特点：

指定列的数据自动增长
即使数据删除，还是从删除的序号继续往下

CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT ，字段2 数据类型 UNIQUE);

域完整性

限制此单元格的数据正确，不对照此列的其它单元格比较域代表当前单元格

域完整性约束：

数据类型：数值类型、日期类型、字符串类型

非空约束（not null）

CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT ，字段2 数据类型 UNIQUE not null);

默认值约束(default)

CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT ，字段2 数据类型 UNIQUE not null default '男');

插入的时候，values当中的值直接给default

参照完整性

什么是参照完整性

是指表与表之间的一种对应关系通常情况下可以通过设置两表之间的主键、外键关系，或者编写两表的触发器来实现。有对应参照完整性的两张表格，在对他们进行数据插入、更新、删除的过程中，系统都会将被修改表格与另一张对应表格进行对照，从而阻止一些不正确的数据的操作。数据库的主键和外键类型一定要一致；两个表必须得要是InnoDB类型设置参照完整性后，外键当中的内值，必须得是主键当中的内容

一个表设置当中的字段设置为主键，设置主键的为主表

CREATE TABLE student(sid int PRIMARY key,name varchar(50) not null,sex varchar(10) default '男');

创建表时，设置外键，设置外键的为子表

CREATE TABLE score(
sid INT,
score DOUBLE,
CONSTRAINT fk_stu_score_sid FOREIGN KEY(sid) REFERENCES student(id));

表之间关系

一对一一夫一妻
一对多关系一个人可以拥有多辆汽车，要求查询某个人拥有的所有车辆。创建Person表

创建Car表

多对多关系

学生选课，一个学生可以选修多门课程，每门课程可供多个学生选择。
一个学生可以有多个老师，而一个老师也可以有多个学生

创建老师表

创建学生表

创建学生与老师关系表

关系图

添加外键

多表操作

合并结果集

什么是合并结果集

合并结果集就是把两个select语句的查询结果合并到一起

合并结果集的两种方式

UNION 合并时去除重复记录
UNION ALL 合并时不去除重复记录

格式： UNION:

SELECT * FROM 表1 UNION SELECT * FROM 表2；
SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;

创建表：

UNION：

UNION ALL：

注意事项：被合并的两个结果：列数、列类型必须相同。

多表联查：

什么是连接查询

也可以叫跨表查询，需要关联多个表进行查询

什么是笛卡尔集

假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况同时查询两个表，出现的就是笛卡尔集结果

查询时给表起别名

多表联查，如何保证数据正确逐行判断，相等的留下，不相等的全不要

连接查询

1. 内连接

内连接图示：

作用：查询两张表的共有部分语句：

Select <select_list> from tableA A  Inner join  tableB B  on A.Key = B.Key

示例：

SELECT * from employee e  INNER JOIN department d on e.depart_id = d.id;

多表连接：建表：

使用99连接法：

使用内联查询

2. 左连接

图示

作用：把左边表的内容全部查出,右边表只查出满足条件的记录语句：

Select <select_list> from tableA A Left Join  tableB B  on A.Key = B.Key

示例

SELECT * from employee e  LEFT JOIN department d on e.depart_id = d.id;

3. 右连接

图示：

作用

把右边表的内容全部查出,左边表只查出满足条件的记录

语句

Select <select_list> from tableA A Right Join  tableB B  on A.Key = B.Key

示例

SELECT * from employee e  RIGHT JOIN department d on e.depart_id = d.id;

子查询

什么是子查询

一个select语句中包含另一个完整的select语句。或两个以上SELECT，那么就是子查询语句了。

子查询出现的位置

where后，把select查询出的结果当作另一个select的条件值
from后，把查询出的结果当作一个新表；

示例表

使用

查询与项羽同一个部门人员工

先查出项羽所在的部门编号

再根据编号查同一部门的员工

把第1条查出来的结果当第2天语句的条件

查询工资高于程咬金的员工

查出程咬金的工资

再去根据查出的结果查询出大于该值的记录员工名称

工资高于30号部门所有人的员工信息

先查出30号部门工资最高的那个人

再到整个表中查询大于30号部门工资最高的那个人

查询工作和工资与妲己完全相同的员工信息

先查出妲已的工作和工资

根据查询结果当作条件再去查询工作和工资相同的员工由于是两个条件，使用 IN进行判断

有2个以上直接下属的员工信息

对所有的上级编号进行分组

找出大于2个的，大于2个说明有两个下属

把上条的结果当作员工编号时行查询

查询员工编号为7788的员工名称、员工工资、部门名称、部门地址

函数介绍

事先提供好的一些功能可以直接使用
函数可以用在SELECT语句及其子句
也可以用在UPDATE,DELETE语句当中。

函数分类

字符串函数
数值函数
日期和时间函数
流程函数
其它函数

字符串函数

concat(s1,s2…sn)

将传入的字符连接成一个字符串任何字符串与null进行连接结果都是null

insert(str,x,y,instr)

将字符串str从x位置开始，y个字符长的子串替换为指定的字符

LOWER(Str)和UPPER(str)

将字符串转成小写或大写

LEFT(str,x)和RIGHT(str，x)

分别返回字符串最左边的x个字符和最右边的x个字符

如果第二个参数为null，那么不返回任何字符

LPAD（str,n,pad）和RPAD(str,n,pad)

用字符串pad对str最左边或最右边进行填充，直接到长度为n个字符长度

LTRIM(str)和RTRIM(str)

去掉字符串当中最左侧和最右侧的空格

TRIM(str)

去掉字符串左右的空格

REPEAT(str,x)

返回str重复x次的结果

REPLACE(str,a,b)

用字符串b替换字符串str中所有出现的字符串a.

SUBSTRING(str,x,y)

返回字符串str中第x位置起y个字符长度的字符

数值函数

ABS(x)

返回X的绝对值

CEIL(x)

小数不为零部分上取整，即向上取最近的整数

FLOOR(x)

小数部分下取整，即向下取最近的整数

MOD(X,Y)

返回X/Y的模

RAND()

返回0-1内容的随机值

日期和时间函数

CURDATE()

返回当前日期，只包含年月日

CURTIME()

返回当前时间，只包含时分秒

NOW()

返回当前日期和时间，年月日时分秒都包含

UNIX_TIMESTAMP

返回当前日期的时间戳

FROM_UNIXTIME(unixtime)

将一个时间戳转换成日期

WEEK(DATE)

返回当前是一年中的第几周

YEAR(DATE)

返回所给日期是那一年

HOUR(TIME)

返回当前时间的小时

MINUTE(TIME)

返回当前时间的分钟

DATE_FORMAT(date,fmt)

按字符串格式化日期date值

DATE_ADD(date,interval expr type)

计算日期间隔

DATEDIFF(date1,date2)

计算两个日期相差的天数

流程函数

IF(value,t,f)

如果value是真，返回t,否则返回f

IFNULL(value1,value2)

如果value1不为空，返回value1否者返回value2

CASE WHEN THEN END

其他常用函数

DATABASE()

返回当前数据库名

VERSION()

返回当前数据库版本

USER()

返回当前登陆用户名

PASSWORD(STR)

对str进行加密

MD5()

返回str的MD5值

什么是事务

不可分割的操作,假设该操作有ABCD四个步骤组成.
若ABCD四个步骤都成功完成,则认为事务成功.
若ABCD中任意一个步骤操作失败,则认为事务失败
每条sql语句都是一个事务
事务只对DML语句有效,对于DQL无效

事务的ACID

原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚

一致性（Consistency）

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
让数据保持一定上的合理
一个商品出库时，仓库商品数量减1，对应用户的购物车中商品加1

隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

持久性（Durability）

持久性是指一个事务一旦被提交了，就不能再回滚了，已经把数据保存到数据库当中了。

事务的使用

开启事务：start transaction
提交事务：commit 所有语句全部执行完毕，没有发生异常,提交事务，更新到数据库当中。
回滚事务：rollback 当遇到一突发情况，撤销执行的sql语句

执行流程

事务的并发问题

更新丢失

两个或多个事务选择同一行,然后基于最初选定的值更新该行时,
由于每个事务都不知道其它事务的存在 , 就会发生丢失更新问题,
最后的更新覆盖了其它事务所做的更新

脏读

举例：

老板要给程序员发工资，程序员的工资是3.6万/月。
但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，
就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。
但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交
实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。
事务A读到了事务B已修改,但尚未提交的数据
解决办法 Read committed！读提交，能解决脏读问题

不可重复读

举例：

程序员拿着工资卡（卡里当然是只有3.6万），当他买单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，
就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。
当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了。程序员就会很郁闷，明明卡里是有钱的…
一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读
事务A读取到了事务B已经提交的修改数据
解决办法 Repeatable read

重复读

举例：

程序员拿着工资卡（卡里还是有3.6万），当他买时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。
这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

幻读

程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（妻子事务开启）看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。
当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。
解决办法：Serializable 但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

对应关系

事务隔离级别脏读不可重复读幻读
读未提交（read-uncommitted）是是是
不可重复读（read-committed）否是是
可重复读（repeatable-read）否否是
串行化（serializable）否否否

事务隔离级别

Read uncommitted：就是一个事务可以读取另一个未提交事务的数据
Read committed：一个事务要等另一个事务提交后才能读取数据
Repeatable read：就是在开始读取数据（事务开启）时，不再允许修改操作
Serializable ：在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读

查看隔离级别

select @@global.tx_isolation,@@tx_isolation;

设置隔离级别

全局的

set global transaction isolation level read committed;

当前会话

 set session transaction isolation level read committed;

什么是权限

限制一个用户能够做什么事情，在MySQL中，可以设置全局权限，指定数据库权限，指定表权限，指定字段权限

有哪些权限

create：创建数据库、表或索引权限
DROP：除数据库或表权限
ALTER：ALTER更改表，比如添加字段、索引等
DELETE：删除数据权限
INDEX：索引权限
INSERT：插入权限
SELECT：查询权限
UPDATE：更新权限
CREATE VIEW：创建视图权限
EXECUTE：执行存储过程权限

Mysql8之前

创建用户：

create user '用户名'@'localhost' identified by '密码';

删除用户：

DROP USER 用户名称

分配权限：

GRANT 权限 (columns) ON 数据库对象 TO 用户 IDENTIFIED BY "密码" WITH GRANT OPTION

Mysql8

MySQL8新增了角色(role)的概念，使账号权限的管理，更加灵活方便。角色，就是一些权限的集合。然后再把角色授权给某个账户

创建角色

create role 'app_dev','app_read','app_write';

查询授权情况

show grants for 'app_dev';

USAGE “无权限” 就是让你这个用户可以像个用户似的登录，但是除了能看到有哪些数据库外，什么权限也没有

创建的角色也和账号一样保存在mysql.user表中

select * from mysql.user;

给角色授权

给指定角色权限

grant select,insert,update,delete on *.* to app_dev;
grant select on mydb.* to app_read;
grant insert,update,delete on mydb.* to app_write;

创建用户, 将权限授予用户

创建用户

create user myuser2 identified with mysql_native_password by 'myuser2';

分配角色

grant app_write to myuser2;

查询用户对应的角色

show grants for myuser1;

看到的是角色，并不是具体的权限如果要查看具体的权限则需要这样执行show grants

show grants for myuser2 using app_dev

通过使用using app_dev，会将账号和角色的权限一并显示 4. 可以继续给角色添加权限

grant create on mydb.* to app_dev;

激活角色

查看当前角色

select current_role()

激活指定用户授权的所有角色

set default role all to myuser1

删除指定权限

revoke  create on sys.* from '角色名称'@'127.0.0.1'; 
revoke all  on sys.* from 'app_dev'@'127.0.0.1';

删除用户

drop user '用户名'@'127.0.0.1';

什么是索引

帮助Mysql高效获取数据的数据结构
索引就是数据结构
类似新华字典的索引目录,可以通过索引目录快速查到你想要的字
排好序的快速查找数据

为什么要建立索引

提高查询效率没有排序之前一个一个往后找通过索引进行排序之后,可以直接定义到想要的位置
排好序的快速查找数据结构–>就是索引

优势

索引类似大学图书馆建立的书目索引,提高数据检索的效率,降低数据库的IO成本
通过索引对数据项进行排序,降低数据排序成本,降低了CPU的消耗

劣势

一般来说, 索引本身也很大, 索引往往以文件的形式存储到磁盘上
索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录.所以索3引也是要占磁盘空间的
虽然索引提高了查询速度,但是会降低更新表的速度.
因为更新表时, MYSQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,
会调整因为更新所带来的键值变化后索引的信息

索引分类

单值索引一个索引只包含间个列,一个表可以有多个单值索引一般来说, 一个表建立索引不要超过5个
唯一索引索引列的值必须唯一,但允许有空值
复合索引一个索引包含多个列
全文索引 MySQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的速度。

索引为什么能快速查找数据

二叉查找树
B-Tree
B+Tree

基本语法

创建索引

create [UNIQUE] index 索引名称 ON 表名(字段(长度))
alter 表名 add [unque] index[索引名称] on(字段(长度))

查看索引

show index from 表名

删除索引

drop index[索引名称] on 表名

更改索引

添加一个主键,索引必须是唯一索引,不能为NULL

alter table tab_name add primary key(column_list)

创建的索引是唯一索引,可以为NULL

alter table tab_name add unque index_name(column_list)

普通索引,索引值可出现多次

alter table tab_name add index index_name(column_list)

全文索引

alter table tab_name add fulltext index_name(column_list)

索引建立选择

适合建立索引

主键自动建立唯一索引 primary
频繁作为查询条件的字段应该创建索引比如银行系统银行帐号,电信系统的手机号
查询中与其它表关联的字段,外键关系建立索引比如员工,部门外键
频繁更新的字段不适合建立索引每次更新不单单更新数据,还要更新索引
where条件里用不到的字段不建立索引查询中排序的字段,排序的字段若通过索引去访问将大提升排序速度索引能够提高检索的速度和排序的速度查询中统计或分组的字段分组的前提是必排序

什么是视图

视图是一个虚拟表，其内容由查询定义。
同真实的表一样，视图包含一系列带有名称的列和行数据。
行和列数据来自定义视图的查询所引用的表，并且在引用视图时动态生成。
简单的来说视图是由select结果组成的表；

视图的特性

视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，
不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；
可以跟基本表一样，进行增删改查操作(增删改操作有条件限制)；

视图的作用

安全性创建一个视图，定义好该视图所操作的数据。之后将用户权限与视图绑定这样的方式是使用到了一个特性：grant语句可以针对视图进行授予权限。
查询性能提高。
提高了数据的独立性

创建视图

CREATE [ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}]
               VIEW 视图名 [(属性清单)]
               AS SELECT 语句
               [WITH [CASCADED|LOCAL] CHECK OPTION];

ALGORITHM参数:

merge：处理方式替换式，可以进行更新真实表中的数据；
TEMPTABLE：具化式，由于数据存储在临时表中，所以不可以进行更新操作！
UNDEFINED：没有定义ALGORITHM参数 mysql更倾向于选择替换方式。是因为它更加有效。

WITH CHECK OPTION：更新数据时不能插入或更新不符合视图限制条件的记录。 LOCAL和CASCADED：为可选参数，决定了检查测试的范围，默认值为CASCADED。

修改视图

CREATE OR REPLACE VIEW 视图名 AS SELECT [...] FROM [...];

删除视图

drop view 视图名称;

视图机制

替换式操作视图时，视图名直接被视图定义给替换掉
具化式 mysql先得到了视图执行的结果，该结果形成一个中间结果暂时存在内存中。外面的select语句就调用了这些中间结果(临时表)
替换式与具化式区别替换方式，将视图公式替换后，当成一个整体sql进行处理了。具体化方式，先处理视图结果，后处理外面的查询需求。

视图不可更新部分

聚合函数;
DISTINCT 关键字;
GROUP BY子句;
HAVING 子句;
UNION 运算符;
FROM 子句中包含多个表;
SELECT 语句中引用了不可更新视图;
只要视图当中的数据不是来自于基表，就不能够直接修改

什么是存储过程

一组可编程的函数，是为了完成特定功能的SQL语句集经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程就是具有名字的一段代码，用来完成一个特定的功能。
创建的存储过程保存在数据库的数据字典中

为什么要用存储过程

将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL的调用
批量处理
统一接口，确保数据的安全
相对于oracle数据库来说，MySQL的存储过程相对功能较弱，使用较少。

存储过程的创建和调用

创建存储过程

CREATE PROCEDURE 名称()
   BEGIN
   语句
   END $$

调用存储过程

call  名称（）;

删除存储过程

drop procedure  名称

存储过程变量

在存储过程中声明一个变量
使用DECLARE语句 DECLARE 变量名数据类型(大小) DEFAULT 默认值; 可以声明一个名为total_sale的变量，数据类型为INT，默认值为0

DECLARE total_sale INT DEFAULT 0;

分配变量值要为变量分配一个值，可以使用SET语句

SET total_count = 10;

使用SELECT INTO语句将查询的结果分配给一个变量

SELECT COUNT(*) INTO total_products FROM products

变量的范围如果在存储过程中声明一个变量，那么当达到存储过程的END语句时，它将超出范围，因此在其它代码块中无法访问

存储过程参数

三种类型

IN：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT：表示过程向调用者传出值
INOUT：INOUT参数是IN和OUT参数的组合。

定义参数：create produce name(模式,参数名称数据类型（大小）)

存储过程语句

IF语句

IF expression THEN 
   statements;
END IF;
##################
IF expression THEN
   statements;
ELSE
   else-statements;
END IF;

CASE语句

CASE  case_expression
   WHEN when_expression_1 THEN commands
   WHEN when_expression_2 THEN commands
   ...
   ELSE commands
END CASE;

循环

WHILE expression DO
   statements
END WHILE
#############
REPEAT
 statements;
UNTIL expression
END REPEAT

查看存储过程

查看所有存储过程：

SHOW PROCEDURE STATUS;

查看指定数据库中的存储过程

SHOW PROCEDURE STATUS WHERE db = 'My_test4';

查看指定存储过程源代码

SHOW CREATE PROCEDURE 存储过程名

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2023-11-27，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！