首页  思维导图  详情

MySQL

2024-09-19 17:54:14   1  举报





AI智能生成

为你推荐

查看更多



mysql

Mysql

MySQL是一种流行的开源关系型数据库管理系统，它使用SQL语言来管理数据。它广泛应用于各种规模的企业、网站和应用程序中，提供高性能、高可靠性和易于使用的数据存储和检索功能。MySQL支持多种操作系统，包括Linux、Windows和macOS，同时提供了丰富的工具和驱动程序，以便与其他应用程序集成。得益于其强大的功能和可扩展性，MySQL已成为全球最流行的开源数据库解决方案之一。

MySQL笔记

数据分析

MySQL

作者其他创作

大纲/内容

exam.sql

exam

girl.sql

girls

myemployees.sql

myemployees

练习用的数据库

DB: 数据库：存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来。用户可以对数据库中的数据进行增加，修改，删除及查询操作。

数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理DB

概念

关系型数据库也叫结构化数据库，以表格的方式存放数据

RDBMS: 关系型数据库

NOSQL：非关系型数据库

分类

DBMS：数据库管理系统

用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言

SQL：结构化查询语言

数据库的相关概念

将数据持久化到本地，以结构化方式存储

方便查询，以结构化方式查询

数据库存储的好处

数据存放到表中，然后表再放到库中，MySQL中可以创建多个数据库，用图书馆类比

一个库中可以有多张表，每张表具有唯一的表名用来标识自己

表中有一个或多个列，列又称为“字段”，相当于java中“属性

表中的每一行数据，相当于java中“对象”

数据库存储的特点

MySQL

Oracle

DB2

SQL Server

..........

常见的关系型数据库

数据库介绍

历史

优点

安装

命令行

图形化界面

MySQL启动和停止

MySQL客户端的介绍

MySQL数据库的连接

对数据库的操作，对表的操作

数据定义语言

DDL

向表中增加删除修改数据，对数据的增删改的操作

数据操作语言

DML

数据查询语言

DQL

对权限进行控制，对权限的操作

数据控制语言

DCL

SQL语法分类

MySQL介绍

简介

show databases :显示数据库

切换数据库： use 数据库名

创建数据库： create database 数据库名

删除数据库：drop database 数据库名

创建数据库

语法

案例

创建表

查看数据库中所有的表： show tables;

desc 表名：查看表结构

删除表： drop table 表名

alter table 表名 rename to 新的表名

修改表的名字

alter table 表名 add 字段名字字段的类型(size)

添加字段

alter table 表名 drop 字段的名字

删除字段

alter table 表名 change 原来字段的名字要修改成的名字数据类型(size)

修改字段名

alter table 表名 modify 列名新的类型

修改字段数据类型

修改字段

change和modify的区别

修改表：alter table 表名开头

约束就是限制，规定，就是允许你干什么，不允许你干什么

含义

表示这个字段不能为空

非空： not null

表示这个字段不能重复，但是可以为空

唯一：unique

主键是一条记录跟其他记录进行区分标志，主键不能为空，必须唯一，不能重复

主键： primary key

用来设置这列的默认值

默认值：default

用来表示这个表和其他表关联的字段

外键：foreign key

用来规定这个字段只能输入指定的值，但是mysql不支持check约束

检查约束： check

六种约束

直接将约束定义在每一列的后面，这种约束叫做列级约束

非空

唯一

默认

主键

检查

包含

列级约束

将约束直接定义到表中，跟字段平级，这种约束叫做表级约束

外键

表级约束

字段名数据类型(size) 约束名称

【constraint 约束名】约束的类型(字段名)

constraint 约束名约束类型(字段名) references 表名（主键）

创建表时添加

alter table 表名 modify 字段名数据类型(size) 约束类型

添加列级约束

alter table 表名 add constraint 约束名字约束的类型(字段) 【references 表名(主键)】，注意，【】中的只有添加外键时才会用到

添加表级约束

修改表时添加

添加约束

约束

创建表结构

DDL：数据定义语言

注意：值的数量和数据类型要和字段的数量和数据类型应该保持一致

insert into 表名（字段1，字段2，.....） values(值1，值2，.....);

注意：如果省略了字段，默认插入所有字段

insert into 表名 values(值1，值2，......)

插入数据

更新语句

delete from 表名;

删除所有记录

delete from 表名 where 条件

删除符合某些条件的记录

删除语句

DML：数据操作语言

select 查询列表from 表名;

1、查询列表可以是字段、常量、表达式、函数，也可以包含多个2、查询结果是一个虚拟表

查询语句不会对表中的数据进行任何的改变

特点

select 字段名 from 表名;

查询单个字段

查询多个字段

select * from 表名

查询所有字段

select 常量值;注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要

查询常量

select 函数名(实参列表);

查询函数

select 1000*5;

查询表达式

select 字段名 as 别名，字段名 as 别名 from 表名；as可以省略

as关键字

起别名

示例

基础查询

select 查询列表from 表名where 筛选条件

< = <> != >= <= <=>安全等于

<> 表示不等于

!= 也表示不等于

简单条件运算符

is not

null值的判断运算符

between and

判断普通值

判断 null

<=> 安全等于

筛选条件分类

like:一般搭配通配符使用，可以判断字符型或数值型通配符：%：任意多个字符，_：任意单个字符

模糊查询

distinct

去除重复行

条件查询

distinct

select 查询列表from 表where 筛选条件order by 排序列表【asc | desc】

asc : 升序，可以省略

desc : 降序

排序规则

排序列表支持单个字段、多个字段、函数、表达式、别名

order by的位置一般放在查询语句的最后（除limit语句之外）

排序查询

类似于Java中的方法，将一组语句封装到方法体内部，对外暴露方法名

什么是函数

select 函数名(实参列表) 【from 表】;

调用方式

获取参数值的字节个数

SELECT length('john'); 4SELECT LENGTH('张三丰hahaha'); 15

length

拼接字符串

concat

将字符转换为大写和小写

SELECT UPPER('john');SELECT LOWER('joHn');

将姓变大写，名变小写，然后拼接

upper、lower

截取从指定索引处后面所有字符，注意：索引是从1开始

截取从指定索引处指定字符长度的字符

姓名中首字符大写，其他字符小写然后用_拼接，显示出来

substr

instr(字符串，要查找的子串)

返回子串第一次出现的索引，如果找不到返回0

举例

instr

trim(字符串)

含义：去掉字符串两边的空格

案例： SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;

ltrim : 去掉左边的空格

rtrim : 去掉右边的空格

trim(要去除字符串 from 从这个字符串去除)

SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') AS out_put;

去除指定的字符串

简单用法

{}中的内容是选择一个

[] 表示可以省略

BOTH ：表示去除两边的指定字符，默认值

LEADING ：去除左边的指定字符

TRAILING：去除右边的指定字符

SELECT TRIM(LEADING FROM ' ，，barxxx ');

去除左边的空格

remstr：可以省略，默认值是空格

语法：RIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

完整格式

TRIM([remstr FROM] str)

简化格式

复杂用法

trim

用指定的字符实现左右填充指定长度

lpad、rpad

替换（全部替换）

replace

函数返回subStr在string中出现的位置

说明

locate

字符串函数

四舍五入

SELECT ROUND(1.55); 2

小数位数默认为 0

SELECT CEIL(1.02); 2

ceil（浮点数）

向下取整，返回<=该参数的最大整数

SELECT FLOOR(-9.99); -10

floor（浮点数）

截断

取余

数学函数

当前时间：日期 + 时间

now()

当前系统日期，不包含时间

curdate()

返回当前时间，不包含日期

curtime()

SELECT YEAR(NOW()) 年;SELECT YEAR('1998-1-1') 年;

year(日期或者日期格式字符串)

SELECT month(NOW()) 年;SELECT month('1998-1-1') 年;

month(日期或者日期格式字符串)

获取时间的指定部分

将字符串通过指定的格式转换成日期

模式

将日期转换成字符

查询有奖金的员工名和入职日期(xx月/xx日 xx年)

日期函数

SELECT VERSION();SELECT DATABASE();SELECT USER();

其他函数

单行函数

count : 数量

max ：最大值

min ：最小值

sum ：求和

avg ：平均数

聚合函数(分组函数)

常见函数

以某个或多个字段作为条件进行分组

group by

以分组结果作为查询的条件

having

注意：分组之前的条件写在 where中，分组之后的条件加在 having后

案例：

分组查询

当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接

如何解决：添加有效的连接条件

笛卡尔乘积

等值连接

查询员工的工资和工资级别：

非等值连接

SQL92

select 查询列表from 表1 别名【inner】 join 表2 别名 on 连接条件where 筛选条件

①表的顺序可以调换②内连接的结果=多表的交集③n表连接至少需要n-1个连接条件

需求：查询员工编号，姓名，部门名称，工资等级

SQL99

内连接

select 查询列表from 表1 别名left|right|full【outer】 join 表2 别名 on 连接条件where 筛选条件group by 分组列表having 分组后的筛选order by 排序列表

①查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null②left join 左边的就是主表，right join 右边的就是主表 full join 两边都是主表③一般用于查询除了交集部分的剩余的不匹配的行

left join

左边表是主表

查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null

左外连接

right join

右边的就是主表

右外连接

full join

两边都是主表

MySQL 不支持

全连接

外连接

连接查询

外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多

嵌套在其他语句内部的select语句称为子查询或内查询，外面的select语句，则此语句称为外查询或主查询

查询结果只能是一行一列

select后面

查询结果可以是多行多列

from后面

查询结果可以是一行一列也可以是多行一列

where，having后面

出现位置分类

子查询

当要查询的条目数太多，一页显示不全

应用场景

select 查询列表from 表limit 【offset，】size;注意：offset代表的是起始的条目索引，默认从0开始size代表的是显示的条目数

分页计算公式

分页查询

将两个查询结果直接进行相加

union all

将两个查询结果相加，去掉重复的记录，只留下一个

union

查询语句1union 【all】查询语句2union 【all】...

联合查询

if函数： if else 的效果

ifnull

case 要判断的字段或表达式when 常量1 then 要显示的值1或语句1;when 常量2 then 要显示的值2或语句2;...else 要显示的值n或语句n;end

案例：查询员工的工资，要求部门号=10，显示的工资为1.1倍部门号=20，显示的工资为1.2倍部门号=30，显示的工资为1.3倍其他部门，显示的工资为原工资

类似 switch case 的效果

case when 条件1 then 要显示的值1或语句1when 条件2 then 要显示的值2或语句2。。。else 要显示的值n或语句nend

类似于多重if

case

流程控制语句

select 查询列表 ⑦from 表1 别名 ①连接类型 join 表2 ②on 连接条件 ③where 筛选 ④group by 分组列表 ⑤having 筛选 ⑥order by排序列表 ⑧limit 起始条目索引，条目数; ⑨

查询语句的执行顺序

查询总结

DQL：数据查询语言

固定ip地址：表示这个用户只能在固定的ip地址访问我这个数据库

% ：表示允许这个用户在任何ip地址上访问这个数据库

语法：CREATE USER 用户名@地址 IDENTIFIED BY '密码';

create user 'weiwei'@'localhost' identified by 'weiwei';

create user 'weiwei'@'%' identified by 'weiwei';

案例：

创建用户

注意： @后的ip地址应该跟创建用户时，设置的ip地址一样

刷新权限

FLUSH PRIVILEGES;

给用户授权

SHOW GRANTS FOR用户名@IP

SHOW GRANTS FOR 'weiwei'@'localhost';

查看用户权限

撤销授权

当给用户修改密码或者设置权限后需要刷新权限。

DROP USER 用户名@IP

drop user 'kaven'@'%';

删除用户

Use mysql;UPDATE USER SET authentication_string=PASSWORD(‘密码’) WHERE User=’用户名’;flush privileges;

set password for 'weiwei'@'localhost' = password('123456')

Use mysql;UPDATE USER SET authentication_string=PASSWORD('123456') WHERE User='weiwei';flush privileges;

修改用户密码

DCL：数据控制语言

注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别

变量由系统定义，不是用户定义，属于服务器层

定义：

作用域：针对于所有会话（连接）有效，但不能跨重启

show global variables;

查看所有系统变量

show global variables like '%char%';

查看满足条件的部分系统变量

select @@global.autocommit;

select @@global .系统变量名;

查看指定的系统变量的值

方法1：set global 系统变量名=值;

方法2：set @@global .系统变量名=值;

为某个系统变量赋值

使用

全局变量

作用域：针对于当前会话（连接）有效

show 【session】variables;

show 【session】 variables like '%char%';

select @@session.autocommit;

select @@【session.】系统变量名;

方法1：set 【session】系统变量名=值;

方法2：set @@【session.】系统变量名=值;

会话变量

系统变量

变量由用户自定义，而不是系统提供的

1、声明2、赋值3、使用（查看、比较、运算等）

使用步骤

针对于当前会话（连接）有效，作用域同于会话变量

作用域

赋值操作符

SET @变量名=值;SET @变量名:=值;SELECT @变量名:=值;注意：如果是通过select进行赋值一定要使用 :=

SELECT 字段 INTO @变量名FROM 表;

声明并初始化

SET @变量名=值;SET @变量名:=值;SELECT @变量名:=值;

赋值（修改）

SELECT @变量名;

使用（查看变量的值）

用户变量

仅仅在定义它的begin end块中有效只能应用在 begin end中的第一句话

DECLARE 变量名类型;DECLARE 变量名类型【DEFAULT 值】;

声明

SET 局部变量名=值;SET 局部变量名:=值;SELECT 局部变量名:=值;

SELECT 字段 INTO 局部变量名FROM 表;

赋值（更新变量的值）

SELECT 局部变量名;

局部变量

SET @m=1;SET @n=1;SET @sum=@m+@n;SELECT @sum;

DECLARE m INT DEFAULT 1;DECLARE n INT DEFAULT 1;DECLARE SUM INT;SET SUM=m+n;SELECT SUM;

注意：通常begin-end用于定义一组语句块，在各大数据库中的客户端工具中可直接调用，但在mysql中不可用。begin-end、流程控制语句、局部变量只能用于函数、存储过程内部、游标、触发器的定义内部。

案例：声明两个变量，求和并打印

用户变量：当前会话

局部变量:定义它的BEGIN END中

用户变量：会话的任何地方

局部变量：BEGIN END的第一句话

定义位置

用户变量：不用指定类型

用户变量和局部变量的对比

自定义变量

变量

名称

原因

在mysql中创建函数时出现这种错误的解决方案：set global log_bin_trust_function_creators=1;

缺点：数据库重启后失效

第一种

windows操作系统下：1. 由于我们使用MySql 时，需要修改mysql 的 my.ini 的配置文件。2. 但是 mysql 5.7 的 my.ini 位置并不在 C:\\Program Files\\MySQL\\MySQL Server 5.7 此目录的my-default.ini 并不是我们要的：3. 如果安装在C盘的：我们要进入 C:\\ProgramData\\MySQL\\MySQL Server 5.7 此目录即可看到my.ini 此时你可以修改次配置啦！

在my.ini里面设置log-bin-trust-function-creators=1不过这个需要重启服务

第二种

解决办法

报错

一组预先编译好的SQL语句的集合，理解成批处理语句1、提高代码的重用性2、简化操作3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新函数：有且仅有1 个返回，适合做处理数据后返回一个结果

跟存储过程的区别

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型BEGIN\t函数体END;

1.参数列表包含两部分：参数名参数类型2.函数体：肯定会有return语句，如果没有会报错如果return语句没有放在函数体的最后也不报错，但不建议3.函数体中仅有一句话，则可以省略begin end4.使用 delimiter语句设置结束标记

注意

创建语法

SELECT 函数名(参数列表)

调用语法

返回公司的员工个数

CREATE FUNCTION myf1() RETURNS INTBEGIN DECLARE c INT DEFAULT 0;#定义局部变量 SELECT COUNT(*) INTO c#赋值 FROM employees; RETURN c;END $

实现

SELECT myf1()$

调用

无参函数案例

根据员工名，返回它的工资

CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLEBEGIN\tSET @sal=0;#定义用户变量 \tSELECT salary INTO @sal #赋值\tFROM employees\tWHERE last_name = empName; RETURN @sal;END $

SELECT myf2('k_ing') $

有参函数案例1

根据部门名，返回该部门的平均工资

CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLEBEGIN\tDECLARE sal DOUBLE ;\tSELECT AVG(salary) INTO sal\tFROM employees e\tJOIN departments d ON e.department_id = d.department_id\tWHERE d.department_name=deptName;\tRETURN sal;END $

SELECT myf3('IT')$

有参函数案例2

创建函数，实现传入两个float，返回二者之和

不查表案例

SHOW CREATE FUNCTION myf3;

查看函数信息

DROP FUNCTION myf3;

删除函数

函数

虚拟表，和普通表一样使用mysql5.1版本出现的新特性，是通过表动态生成的数据只是保存了sql逻辑

语法：create view 视图名as查询语句;

查询刘备的员工编号，姓名，薪水，部门名称，工资等级

案例1

视图

CREATE PROCEDURE 存储过程名(参数列表)BEGIN 存储过程体（一组合法的SQL语句）END

该参数可以作为输入，也就是该参数需要调用方传入值

该参数可以作为输出，也就是该参数可以作为返回值

out

该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

inout

参数模式

参数名

参数类型

in stuname varchar(20)

参数列表中每个参数包括三部分

有些客户端看到【;】直接会提交，但是写在存储过程中的SQL语句，在我们创建存储过程的过程中是不能执行的，就像是我们在声明方法时，方法里面的代码不能执行一样，只有调用的时候才能执行呢。

如果存储过程体仅仅只有一句话，begin end可以省略存储过程体中的每条sql语句的结尾要求必须加分号。存储过程的结尾可以使用 delimiter 重新设置。

语法：delimiter 结束标记案例：delimiter $

call 存储过程名(实参列表)

调用方法

插入到admin表中五条记录

CALL myp1()$

空参列表

创建存储过程实现根据女神名，查询对应的男神信息

CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))BEGIN\tSELECT bo.*\tFROM boys bo\tRIGHT JOIN beauty b ON bo.id = b.boyfriend_id\tWHERE b.name=beautyName;END $

call myp2('柳岩')$

创建存储过程判断用户是否登录成功

案例2

带in模式参数

根据输入的女神名，返回对应的男神名

根据输入的女神名，返回对应的男神名和魅力值

带out 模式参数

传入a和b两个值，最终a和b都翻倍并返回

带inout模式参数

drop procedure 存储过程名；

删除存储过程

SHOW CREATE PROCEDURE myp2;

查看存储过程信息

子主题

声明异常处理的语法

while 条件 do 循环语句end while;

DECLARE COUNT INT DEFAULT 0; DECLARE SUM INT DEFAULT 0; WHILE COUNT <= 100 DO SET SUM = SUM + COUNT; SET COUNT = COUNT + 1; END WHILE;

while

repeat 循环语句until 条件 end repeat;

declare i int;set i = 1;repeat insert into user_profile_company (uid) values (i+1); set i = i + 1;until i >= 20end repeat;

repeat

loop

循环语句

DECLARE 光标名称 CURSOR FOR 查询语法declare cursor_name cursor for select_statement

定义游标

OPEN 光标名称open cursor_name

打开游标

FETCH 光标名称 INtO var_name [，var_name ].....fetch cursor_name into var_name

取出游标中的数据

CLOSE curso_name;close 光标名称

关闭游标

游标案例

游标