MySQL 入门（三）

2022-02-18  本文已影响0人  yjtuuige

五、MySQL 函数

1. 常用函数（了解）
-- 绝对值
SELECT ABS(-8);
-- 向上取整
SELECT CEILING(9.4);
-- 向下取整
SELECT FLOOR(9.4);
-- 随机数：返回一个0-1之间的随机数
SELECT RAND();
-- 符号函数: 负数返回-1,正数返回1,0返回0
SELECT SIGN(0);

-- 返回字符串包含的字符数
SELECT CHAR_LENGTH('字符串');
-- 合并字符串,参数可以有多个
SELECT CONCAT('Hello','W','orld');
-- 替换字符串,从某个位置开始替换某个长度
SELECT INSERT('helloworld',1,5,'Hi,');
-- 转小写
SELECT LOWER('HelloWorld');
-- 转大写
SELECT UPPER('helloworld');
-- 截取字符串：从左边截取
SELECT LEFT('hello,world',5);
-- 截取字符串：从右边截取
SELECT RIGHT('hello,world',5);
-- 替换字符串
SELECT REPLACE('helloworld','hello','Hi!');
-- 截取字符串,开始和长度
SELECT SUBSTR('helloworld',4,6);
-- 反转字符串
SELECT REVERSE('helloworld');

-- 查询姓张的同学,改成姓王
SELECT REPLACE(`StudentName`,'张','王') AS 新名字
FROM `student` WHERE `StudentName` LIKE '张%';

-- 获取当前日期
SELECT CURRENT_DATE();
-- 获取当前日期
SELECT CURDATE();
-- 获取当前日期和时间
SELECT NOW();
-- 获取当前日期和时间
SELECT LOCALTIME();
-- 获取当前日期和时间
SELECT SYSDATE();

-- 获取年月日,时分秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

-- MySQL 版本
SELECT VERSION();
-- 用户
SELECT USER();
2. 聚合函数（重点）
函数名称 描述
COUNT() 计数，如：select count(*) *不建议使用 ，效率低
SUM() 列的总和：数值字段或表达式
AVG() 列的平均值：数值字段或表达式
MAX() 最大值：数值字段、字符字段或表达式
MIN() 最小值：数值字段、字符字段或表达式 
-- COUNT() 计数：对全部数据行的查询；
-- 方式1：指定列
SELECT COUNT(`StudentName`) FROM `student`;
-- 方式2：COUNT(*)
SELECT COUNT(*) FROM `student`;
-- 方式3 推荐：COUNT(1)
SELECT COUNT(1) FROM `student`;

SELECT SUM(`StudentResult`) AS 总和 FROM `result`;
SELECT AVG(`StudentResult`) AS 平均分 FROM `result`;
SELECT MAX(`StudentResult`) AS 最高分 FROM `result`;
SELECT MIN(`StudentResult`) AS 最低分 FROM `result`;

count() 三种方式区别

数据库级别的 MD5 加密（扩展）

-- 创建表
CREATE TABLE `testmd5` (
    `id` INT NOT NULL,
    `name` VARCHAR(20) NOT NULL,
    `pwd` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4

-- 插入数据
INSERT INTO testmd5 VALUES(1,'aaa','123456'),(2,'bbb','456789');

-- 单独对某个用户(如：aaa)的密码加密
UPDATE testmd5 SET pwd = MD5(pwd) WHERE NAME = 'aaa';

-- 插入新数据时自动加密
INSERT INTO testmd5 VALUES(3,'ccc',MD5('123456'));

-- 查询登录用户信息（将用户输入密码，加密后进行比对）
SELECT * FROM testmd5 WHERE `name`='aaa' AND pwd=MD5('123456');
3. 内置函数（小结）
-- 数值函数
-- 绝对值 abs(-10.9) = 10
abs(x);
-- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
format(x, d);
ceil(x); -- 向上取整 ceil(10.1) = 11
floor(x); -- 向下取整 floor (10.1) = 10
round(x); -- 四舍五入去整
mod(m, n); -- m%n m mod n 求余 10%3=1
pi(); -- 获得圆周率
pow(m, n); -- m^n
sqrt(x); -- 算术平方根
rand(); -- 随机数
truncate(x, d); -- 截取d位小数

-- 时间日期函数
now(), current_timestamp(); -- 当前日期时间
current_date(); -- 当前日期
current_time(); -- 当前时间
date('yyyy-mm-dd hh:ii:ss'); -- 获取日期部分
time('yyyy-mm-dd hh:ii:ss'); -- 获取时间部分
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化时间
unix_timestamp(); -- 获得unix时间戳
from_unixtime(); -- 从时间戳获得时间

-- 字符串函数
length(string); -- string长度，字节
char_length(string); -- string的字符个数
-- 从str的position开始,取length个字符
substring(str, position [,length]);
-- 在str中用replace_str替换search_str
replace(str ,search_str ,replace_str);
-- 返回substring首次在string中出现的位置
instr(string ,substring);
concat(string [,...]); -- 连接字串
charset(str); -- 返回字串字符集
lcase(string); -- 转换成小写
left(string, length); -- 从string2中的左边起取length个字符
load_file(file_name); -- 从文件读取内容
-- 同instr,但可指定开始位置
locate(substring, string [,start_position]);
-- 重复用pad加在string开头,直到字串长度为length
lpad(string, length, pad);
ltrim(string); -- 去除前端空格
repeat(string, count); -- 重复count次
rpad(string, length, pad); --在str后用pad补充,直到长度为length
rtrim(string); -- 去除后端空格
strcmp(string1 ,string2); -- 逐字符比较两字串大小

-- 聚合函数
count();
sum();
max();
min();
avg();
group_concat();

-- 其他常用函数
md5();
default();

六、事务

1. 概述

什么是事务

事务的 ACID 原则

隔离级别

隔离级别 脏读可能性 不可重复读可能性 幻读可能性 加锁度
READ UNCOMMITTED Y Y Y N
READ COMMITTED N Y Y N
REPEATABLE READ N N Y N
SERIALIZABLE N N N Y
2. 事务实现
-- 使用set语句来改变自动提交模式
SET autocommit = 0; -- 关闭
SET autocommit = 1; -- 开启(默认)

-- 1.关闭自动提交
SET autocommit = 0;
-- 2.开始一个事务,标记事务的起始点
START TRANSACTION

INSERT ...

-- 3.提交一个事务给数据库
COMMIT

-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK

-- 4.还原MySQL数据库的自动提交
SET autocommit =1;

-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点
image 
/*
A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000
创建数据库shop和创建表account并插入2条数据
*/
-- 创建数据库
CREATE DATABASE `shop` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE `shop`;
-- 创建数据表
CREATE TABLE `account` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  `cash` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

-- 插入记录
INSERT INTO `account` (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00);

-- 转账实现(事务)
-- 1.关闭自动提交
SET autocommit = 0;
-- 2.开始一个事务,标记事务的起始点
START TRANSACTION;
UPDATE `account` SET `cash`=`cash`-500 WHERE `name`='A';
UPDATE `account` SET `cash`=`cash`+500 WHERE `name`='B';
-- 3.提交事务
COMMIT;
-- 回滚
# rollback;
-- 4.恢复自动提交
SET autocommit = 1;

七、索引

1. 索引分类

索引的作用

分类

2. 主键索引（Primary Key）
3. 唯一索引（Unique）
CREATE TABLE `grade`(
    `grade_id` INT AUTO_INCREMENT PRIMARYKEY,
    `grade_name` VARCHAR(32) NOT NULL UNIQUE
    -- 或 UNIQUE KEY `grade_id` (`grade_id`)
)
4. 常规索引（Key/Index）
-- 方式1：创建表时添加
CREATE TABLE `result`(
    -- 省略一些代码
    -- 常规索引（INDEX或KEY）
    KEY `studentNo` (`studentNo`),
    KEY `subjectNo` (`subjectNo`)
)

-- 方式2：创建后添加（INDEX或KEY）
ALTER TABLE `result` ADD INDEX `student_no`(`student_no`);
5. 全文索引（FullText）

创建索引方式：

CREATE TABLE 表名 (
    字段名1 数据类型 [完整性约束条件…],
    字段名2 数据类型 [完整性约束条件…],
    [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
    [索引名] (字段名[(长度)] [ASC |DESC])
);

CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]);

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]);

-- 删除索引：
DROP INDEX 索引名 ON 表名字;

-- 删除主键索引： 
ALTER TABLE 表名 DROP PRIMARY KEY;

-- 显示索引信息：
SHOW INDEX FROM 表名;

-- 增加全文索引
ALTER TABLE `student` ADD FULLTEXT
INDEX `studentname`(`StudentName`);

-- EXPLAIN : 分析SQL语句执行性能
EXPLAIN SELECT * FROM student WHERE studentno='1000';

EXPLAIN SELECT *FROM `student` WHERE MATCH(`StudentName`) AGAINST('李');

拓展：测试索引

CREATE TABLE `app_user` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
  `email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
  `phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
  `gender` TINYINT UNSIGNED DEFAULT '0' COMMENT '性别（0:男；1：女）',
  `password` VARCHAR(100) NOT NULL COMMENT '密码',
  `age` TINYINT DEFAULT '0' COMMENT '年龄',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

-- 函数代码：生成 100 万条数据
DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
  DECLARE num INT DEFAULT 1000000;
  DECLARE i INT DEFAULT 0;
  WHILE i<num DO
    INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`) VALUES(CONCAT('用户',i),'24736743@qq.com',CONCAT('18',FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
    SET i=i+1;
  END WHILE;
  RETURN i;
END;

-- 如果报错（错误代码：1418），先执行这条代码，然后重新执行函数代码
SET GLOBAL log_bin_trust_function_creators=TRUE;

-- 函数代码执行成功后，再执行此处代码
SELECT mock_data();

-- 数据生成后，可恢复设置
SET GLOBAL log_bin_trust_function_creators=FALSE;

-- 查看耗时
SELECT * FROM `app_user` WHERE `name` = '用户9999';
-- 效率测试
EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户9999';
image image 
CREATE INDEX `id_app_user_name` ON `app_user`(`name`);
image 
-- 查看耗时
SELECT * FROM `app_user` WHERE `name` = '用户9999';
-- 效率测试
EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户9999';
image image
6. 索引准则
7. 索引的数据结构

不同存储引擎，支持的索引类型

八、权限管理

1. 用户管理
  1. 使用 SQLyog 创建用户，并授予权限演示
image image
  1. 基本命令
/* 用户和权限管理 */ 
-- 用户信息表：mysql.user

-- 刷新权限
FLUSH PRIVILEGES;

-- 增加用户
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
    - 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
    - 只能创建用户，不能赋予权限。
    - 用户名，注意引号：如 'user_name'@'192.168.1.1'
    - 密码也需引号，纯数字密码也要加引号
    - 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD
    
-- 重命名用户
RENAME USER old_user TO new_user

-- 为当前用户设置密码
SET PASSWORD = PASSWORD('密码');
-- 为指定用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码'); 

-- 删除用户
DROP USER 用户名;

-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password'];
    - all privileges 表示所有权限
    - *.* 表示所有库的所有表
    - 库名.表名 表示某库下面的某表
    
-- 查看权限 SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名;

-- 查看当前用户权限
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名;
-- 撤销所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名;

-- 权限列表
ALL [PRIVILEGES] -- 设置除GRANT OPTION之外的所有简单权限
ALTER -- 允许使用ALTER TABLE
ALTER ROUTINE -- 更改或取消已存储的子程序
CREATE -- 允许使用CREATE TABLE
CREATE ROUTINE -- 创建已存储的子程序
CREATE TEMPORARY TABLES -- 允许使用CREATE TEMPORARY TABLE
CREATE USER -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALLPRIVILEGES。
CREATE VIEW -- 允许使用CREATE VIEW
DELETE -- 允许使用DELETE
DROP -- 允许使用DROP TABLE
EXECUTE -- 允许用户运行已存储的子程序
FILE -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX -- 允许使用CREATE INDEX和DROP INDEX
INSERT -- 允许使用INSERT
LOCK TABLES -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS -- 允许使用SHOW FULL PROCESSLIST
REFERENCES -- 未被实施
RELOAD -- 允许使用FLUSH
REPLICATION CLIENT -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT -- 允许使用SELECT
SHOW DATABASES -- 显示所有数据库
SHOW VIEW -- 允许使用SHOW CREATE VIEW
SHUTDOWN -- 允许使用mysqladmin shutdown
SUPER -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。 
UPDATE -- 允许使用UPDATE
USAGE -- “无权限”的同义词
GRANT OPTION -- 允许授予权限

/* 表维护 */
-- 分析和存储表的关键字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 检查一个或多个表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理数据文件的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
2. MySQL 备份

mysqldump 客户端：

image 
# 导出 可以-w携带备份条件
# 1.导出一张表
mysqldump -u用户名 -p密码 库名 表名 >文件名(D:/a.sql)

# 2.导出多张表
mysqldump -u用户名 -p密码 库名 表1 表2 表3 >文件名(D:/a.sql)

# 3.导出所有表
mysqldump -u用户名 -p密码 库名 >文件名(D:/a.sql)

# 4.导出一个库
mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)

# 1.登录mysql的情况下：
source 备份文件(D:/a.sql)
# 2.不登录的情况下
mysql -u用户名 -p密码 库名 <备份文件(D:/a.sql)

SQLyog 备份

image
上一篇 下一篇

猜你喜欢

热点阅读