java零基础 字节跳动 Filecoin C语言 springcloud cmake arm vue使用教程 vue网站 vue响应式布局 php零基础入门视频 android项目实战 db2从入门到精通 kafka消费不到数据 matlab区分大小写吗 python可视化编程 java手册 java安装步骤 java时间转时间戳 h5模板 网站后台模板 popen 销售清单打印软件 微信python退出程序 h370主板 思源黑体cn js延迟加载 高通cpu排行 华为下拉开关设置 电脑听歌识曲 机械键盘个别键位失灵 坐标标注插件 0x000007a ajaxpro 战法装备 掌门一对一官网下载 ps画笔工具变成十字 强制换行快捷键 this关键字 java数组转字符串
当前位置: 首页 > 学习教程  > 编程语言

02 MySQL 入门

2020/9/19 14:48:33 文章标签:

7. 数据库的备份和还原

  1. 命令行:

    备份
    	mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
    	mysqldump -uroot -proot db1>d://a.sql
    还原:
    	1. 登录数据库  
    	2. 创建数据库	creat database db1
    	3. 使用数据库	use database db1
    	4. 执行文件。    source d://a.sql
    
  2. 图形化工具

8. DCL

DCL:管理用户,授权

  • DBA:数据库管理员

  • DCL:管理用户,授权

    1. 管理用户

      1. 添加用户:
      语法:CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
      
      1. 删除用户:
      语法:DROP USER '用户名'@'主机名';
      
      1. 修改用户密码:
      USE mysql;
      UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
      UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
      SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
      SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
      
      1. 查询用户:
      -- 1. 切换到mysql数据库
      USE myql;
      -- 2. 查询user表
      SELECT * FROM USER;
      
    2. 权限管理

      1. 查询权限:

        -- 查询权限
        SHOW GRANTS FOR '用户名'@'主机名';
        SHOW GRANTS FOR 'lisi'@'%';
        
      2. ​ 授予权限:

        -- 授予权限
        grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
        -- 给张三用户授予所有权限,在任意数据库任意表上
        
        GRANT ALL ON *.* TO 'zhangsan'@'localhost';
        
      3. 撤销权限:

        -- 撤销权限:
        revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
        REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';
        

9. 事务

9.1 事务的基本介绍

  1. 概念: 如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。

  2. 操作:

    1. 开启事务:start transaction;

    2. 回滚:rollback;

    3. 提交:commit;

  3. 例子:

    CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10),
    balance DOUBLE
    );
    -- 添加数据
    INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
    SELECT * FROM account;
    UPDATE account SET balance = 1000;
    -- 张三给李四转账 500 元
    
    -- 0. 开启事务
    START TRANSACTION;
    -- 1. 张三账户 -500
    
    UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
    -- 2. 李四账户 +500
    -- 出错了...
    UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
    
    -- 发现执行没有问题,提交事务
    COMMIT;
    
    -- 发现出问题了,回滚事务
    ROLLBACK;
    
  4. MySQL数据库中事务默认自动提交
    事务提交的两种方式:

    • 自动提交:
      • mysql就是自动提交的
        * 一条DML(增删改)语句会自动提交一次事务。
        • 手动提交:
          • Oracle 数据库默认是手动提交事务
            • 需要先开启事务,再提交
    • 修改事务的默认提交方式:
      • 查看事务的默认提交方式:SELECT @@autocommit; – 1 代表自动提交 0 代表手动提交
        • 修改默认提交方式: set @@autocommit = 0;

9.2 事务的四大特征(ACID)

  1. 原子性(atomicity):是不可分割的最小操作单位,要么同时成功,要么同时失败。
  2. 持久性(durability):当事务提交或回滚后,数据库会持久化的保存数据。
  3. 隔离性(isolation):多个事务之间。相互独立。
  4. 一致性(consistency):事务操作前后,数据特征不变总量不变。

9.3 事务的隔离级别(了解)

  • 概念:多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。

  • 存在问题:

    1. 脏读:一个事务,读取到另一个事务中没有提交的数据

    2. 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样

    3. 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。

      事务在插入已经检查过不存在的记录时,惊奇的发现这些数据已经存在了,之前的检测获取到的数据如同鬼影一般。

      例子:

      在事务1中,查询User表id为1的是用户否存在,如果不存在则插入一条id为1的数据。

      在事务1查询结束后,事务2往User表中插入了一条id为1的数据。

      此时,由于事务1查询到id为1的用户不存在,因此插入1条id为1的数据。

      但是由于事务2已经插入了1条id为1的数据,因此此时会报主键冲突,对于事务1 的业务来说是执行失败的,这里事务1 就是发生了幻读,因为事务1读取的数据状态并不能支持他的下一步的业务,见鬼了一样。这里要灵活的理解读取的意思,第一次select是读取,第二次的insert其实也属于隐式的读取,只不过是在mysql的机制中读取的,插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。

  • 隔离级别:

    1. read uncommitted:读未提交

      产生的问题:脏读、不可重复读、幻读

    2. read committed:读已提交 (Oracle)

      产生的问题:不可重复读、幻读

    3. repeatable read:可重复读 (MySQL默认)

      产生的问题:幻读

    4. serializable:串行化

      可以解决所有的问题

    • **注意:**隔离级别从小到大安全性越来越高,但是效率越来越低
    • MySQL数据库查询隔离级别:
      • select @@tx_isolation;
    • 数据库设置隔离级别:
      • set global transaction isolation level 级别字符串;

10. MySQl数据类型

10.1 整型

类型大小范围(有符号)范围(无符号)用途
TINYINT1 byte(-128,127)(0,255)小整数值
SMALLINT2 bytes(-32 768,32 767)(0,65 535)大整数值
MEDIUMINT3 bytes(-8 388 608,8 388 607)(0,16 777 215)大整数值
INT或INTEGER4 bytes(-2 147 483 648,2 147 483 647)(0,4 294 967 295)大整数值
BIGINT8 bytes(-9,223,372,036,854,775,808,9 223 372 036 854 775 807)(0,18 446 744 073 709 551 615)极大

10.2 浮点型

类型大小范围(有符号)范围(无符号)用途
FLOAT4 bytes(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)0,(1.175 494 351 E-38,3.402 823 466 E+38)单精度 浮点数值
DOUBLE8 bytes(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)双精度 浮点数值
DECIMAL对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值

10.3 日期和时间类型

类型大小 ( bytes)范围格式用途
DATE31000-01-01/ 9999-12-31YYYY-MM-DD日期值
TIME3‘-838:59:59’/ ‘838:59:59’HH:MM:SS时间值或持续时间
YEAR11901/ 2155YYYY年份值
DATETIME81000-01-01 00:00:00/ 9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP4开始时间1970-01-01 00:00:00
结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07
YYYYMMDD HHMMSS混合日期和时间值,时间戳

10.4 字符串类型

类型大小用途
CHAR(N)N个字节, 0<=N<=255定长字符串 (不足N时补充空格)
VARCHAR(N)L+1个字节,其中L<=N且0<=M<=65535(2^16 -1)变长字符串
TINYBLOB0-255 bytes不超过 255 个字符的二进制字符串
TINYTEXT0-255 bytes 2短文本字符串
BLOB0-65 535 bytes二进制形式的长文本数据
TEXT0-65 535 bytes长文本数据
MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215 bytes中等长度文本数据
LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据
LONGTEXT0-4 294 967 295 bytes极大文本数据

注意:char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。

11. 函数

11.1 MySQL常用内置函数

  1. 字符串函数
  2. 数字函数
  3. 日期函数
  4. 高级函数

详情查看 菜鸟教程 https://www.runoob.com/mysql/mysql-functions.html

  1. 加密函数
    • MD5() 信息摘要算法
    • PASSWORD() 密码算法

CASE函数

SELECT NAME,
CASE ID
WHEN '1' THEN 'yes'
ELSE 'no'
END 'id'  -- 别名 id
FROM account;


SELECT NAME,
CASE 
WHEN ID = '1' THEN 'yes'
ELSE 'no'
END 'id'  -- 别名 id
FROM account;

11.2 自定义函数

11.2.1 概念

自定义函数 (user-defined function, UDF) 是一种对MySQL扩展的途径, 其用法与内置函数相同.

两个必要条件:

  1. 参数: 可以有零个或者多个

  2. 返回值: 只能有一个返回值

函数可以返回任意类型的值, 同样可以接受这些类型的参数, 函数的参数与返回值没有必然联系

11.2.2 创建自定义函数

CREATE FUNCTION function_name
RETURNS
{STRING|INTEGER|REAL|DECIMAL}
routine_body
  1. 函数体由合法的SQL语句构成;

  2. 函数体可以是简单的SELECT或INSERT语句;

  3. 函数体如果为复合结构则使用BEGIN…END语句;

  4. 复合结构可以包含声明,循环,控制结构;

SET NAMES UTF8;改变客户端编码格式(不影响服务器端存储)

11.2.3 不带参数的自定义函数

日期格式化输出(原始语句)

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');

函数式语句

CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒')

使用

SELECT f1()

11.2.4 带参数的自定义函数

CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1+num2)/2;

使用

SELECT f2(10,15);

创建好的函数可以在navicat左侧视图查看

更改MySQL语句结束分割符

DELIMITER //
DELIMITER ;

11.2.5 具有复合结构函数体的自定义函数

此时函数体需要使用BEGIN…END包裹语句;

CREATE FUNCTION addaccount(NAME VARCHAR(10))
RETURNS INT UNSIGNED
BEGIN
INSERT account(name,balance) values(NAME,50);
RETURN LAST_INSERT_ID();
END

12. 存储过程

12.1 概念介绍

MySQL SQL语句命令执行流程 SQL命令->MySQL引擎(分析)->语法正确->可识别命令(编译)->执行->执行结果->返回结果至客户端

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uviBiec8-1600497838872)(Pictures/SQL语句命令执行流程.png)]

存储过程是SQL语句和控制语句的预编译集合, 以一个名称存储并作为一个单元来处理, 存储过程存储在数据库内,可以由应用程序调用执行,而且允许用户声明变量 以及进行流程控制,存储过程可以接收参数,可以接受输入类型的参数,也可以接收输出类型的参数,并且可以返回多个返回值,其执行效率比单一SQL语句的执行效率高.

存储过程只在第一次进行语法分析与编译

优点

  1. 增强SQL语句的功能和灵活性(控制语句,流程控制)
  2. 实现较快的执行速度
  3. 减少了网络流量

12.2 创建存储过程

基础语法

CREATE
[DEFINER={USER|CURREN_USER}]  -- 一般可省略
PROCEDURE sp_name([proc_parameter[,...]])  -- 零到多个参数
[characteristic ...] routine_body

proc_parameter:
[IN|OUT|INOUT]param_name type
  • IN, 表示该参数的值必须在调用存储过程时指定
  • OUT, 表示该参数的值可以被存储过程改变, 并且可以返回
  • INOUT, 表示该参数在调用存储过程时指定,并且可以被改变和返回

过程体

  1. 过程体由合法的SQL语句构成;

  2. 过程体可以是’任意’SQL语句;

  3. 过程体如果为复合结构则使用BEGIN…END语句;

  4. 复合结构可以包含声明,循环,控制结构;

12.2.1 创建不带参数的存储过程

CREATE PROCEDURE sp1() SELECT VERSION();

调用存储过程

CALL sp_name([parameter[,...]]) -- or
CALL sp_name[()]
CALL sp1;

12.2.2 创建带IN参数的存储过程

CREATE PROCEDURE removeAccountById(IN ac_id INT UNSIGNED)
BEGIN
DELETE FROM account WHERE id = ac_id;
END

-- 调用
CALL removeAccountById(5);

删除

DROP PROCEDURE IF EXISTS sp_name;

12.2.3 创建带IN OUT参数的存储过程

CREATE PROCEDURE removeAccountAndReturnUserNums(IN ac_id INT UNSIGNED,OUT accountNums INT UNSIGNED)
BEGIN
DELETE FROM account WHERE id = ac_id;
SELECT count(id) FROM account INTO accountNums;
END

-- 调用 
CALL removeAccountAndReturnUserNums(3,@nums)
SELECT @nums;

例子

-- 定义结束符为“$$”,mysql默认结束符为“;”
-- 意思是告诉mysql解释器,该段命令是否已经结束了,即标识一段命令起始和结束
delimiter $$

-- 创建存储过程
-- sp_char_split_inser:存储过程名称
-- strs:存储过程参数名称
-- in:表示该参数为输入参数;out:表示该参数为输出参数;inout:表示该参数为输入输出参数。不写时默认为in,即输入参数。
create procedure sp_char_split_inser(in strs text)
begin 
    declare i int default 0;
    declare leng int default 0;
    declare word char(1);
    -- 判断字符串是否为空或空字符串
    if(strs is not null && strs <> '') then 
        -- 获取字符串长度
        set leng = char_length(strs);
        -- 循环
        while i < leng do 
            -- 获取第一个字符
            set word=left(strs,1);
            if(word is not null && word <> '') then 
                -- 判断该条数据是否存在
                if not exists(select 1 from demo.charinfo where Hanzi=word limit 1) then 
                    -- 插入数据
                    insert into demo.charinfo(Hanzi) values(word);
                end if;
            end if;
            -- 截取除第一个字符之外的所有字符
            set strs=substring(strs,2);
            set i=i+1;
        end while;
    end if;
end;
-- 命令结束
$$
delimiter ;

12.2.4 创建带有多个OUT类型参数的存储过程

row_count()得到最近一次操作被影响到的记录总数.

CREATE PROCEDURE removeAccountByIdAndReturnInfos(IN ac_id INT UNSIGNED,OUT deleteAccounts INT UNSIGNED,OUT accountCounts INT UNSIGNED)
BEGIN
DELETE FROM account WHERE id = ac_id;
SELECT ROW_COUNT() INTO deleteAccounts;
SELECT COUNT(id) FROM account INTO accountCounts; 
END

调用

CALL removeAccountByIdAndReturnInfos(6,@uuu,@uuu2)
-- 调用时 @uuu,@uuu2 可不用提前定义
-- 定义和查询成员变量方法
SET @uuu=7;   SELECT @uuu

12.2.5 返回表的存储过程写法

CREATE PROCEDURE selectIds()
BEGIN
SELECT * FROM account; 
END

12.2.6 存储过程与自定义函数的区别

  1. 存储过程实现的功能要复杂一些, 而函数的针对性更强;
  2. 存储过程可以返回多个值, 函数只能有一个返回值;
  3. 存储过程一般独立的来执行, 而函数可以作为其他SQL语句的组成部分来出现

利用存储过程能提高程序运行效率

13. MySQL变量的使用

在mysql文档中,mysql变量可分为两大类,即系统变量和用户变量。

13.1 MySQL变量概念

但根据实际应用又被细化为四种类型,即局部变量、用户变量、会话变量和全局变量。

一、局部变量

mysql局部变量,只能用在begin/end语句块中,比如存储过程中的begin/end语句块。

其作用域仅限于该语句块。

-- declare语句专门用于定义局部变量,可以使用default来说明默认值
declare age int default 0;

-- 局部变量的赋值方式一
set age=18;

-- 局部变量的赋值方式二
select StuAge 
into age
from demo.student 
where StuNo='A001';

二、用户变量

mysql用户变量,mysql中用户变量不用提前申明,在用的时候直接用“@变量名”使用就可以了。其作用域为当前连接。

-- 第一种用法,使用set时可以用“=”或“:=”两种赋值符号赋值
set @age=19;

set @age:=20;

-- 第二种用法,使用select时必须用“:=”赋值符号赋值
select @age:=22;

select @age:=StuAge 
from demo.student 
where StuNo='A001';

三、会话变量

mysql会话变量,服务器为每个连接的客户端维护一系列会话变量。

其作用域仅限于当前连接,即每个连接中的会话变量是独立的。

-- 显示所有的会话变量
show session variables;

-- 设置会话变量的值的三种方式
set session auto_increment_increment=1;
set @@session.auto_increment_increment=2;
set auto_increment_increment=3;        -- 当省略session关键字时,默认缺省为session,即设置会话变量的值

-- 查询会话变量的值的三种方式
select @@auto_increment_increment;
select @@session.auto_increment_increment;
show session variables like '%auto_increment_increment%';        -- session关键字可省略

-- 关键字session也可用关键字local替代
set @@local.auto_increment_increment=1;
select @@local.auto_increment_increment;

四、全局变量

mysql全局变量,全局变量影响服务器整体操作,当服务启动时,它将所有全局变量初始化为默认值。要想更改全局变量,必须具有super权限。

其作用域为server的整个生命周期。

-- 显示所有的全局变量
show global variables;

-- 设置全局变量的值的两种方式
set global sql_warnings=ON;        -- global不能省略
set @@global.sql_warnings=OFF;

-- 查询全局变量的值的两种方式
select @@global.sql_warnings;
show global variables like '%sql_warnings%';

13.2 几种变量的对比使用

操作类型全局变量会话变量用户变量局部变量(参数)
文档常用名global variablessession variablesuser-defined variableslocal variables
出现的位置命令行、函数、存储过程命令行、函数、存储过程命令行、函数、存储过程函数、存储过程
定义的方式只能查看修改,不能定义只能查看修改,不能定义直接使用,@var形式declare count int(4);
有效生命周期服务器重启时恢复默认值断开连接时,变量消失断开连接时,变量消失出了函数或存储过程的作用域,变量无效
查看所有变量show global variables;show session variables;--
查看部分变量show global variables like 'sql%';show session variables like 'sql%';--
查看指定变量select @@global.sql_modeselect @@max_connections;select @@session.sql_mode;select @@local.sql_mode;select @@sql_mode;select @var;select count;
设置指定变量set global sql_mode='';set @@global.sql_mode='';set session sql_mode = '';set local sql_mode = '';set @@session.sql_mode = '';set @@local.sql_mode = '';set @@sql_mode = '';set sql_mode = '';set @var=1;set @var:=101;select 100 into @var;set count=1;set count:=101;select 100 into count;

本文链接: http://www.dtmao.cc/news_show_200188.shtml

附件下载

相关教程

    暂无相关的数据...

共有条评论 网友评论

验证码: 看不清楚?