连表查询
-- ======== 根据学生id `id` 查询所有学生成绩 内连表查询 ==========
SELECT
g.`Student_name` AS '学生姓名',
g.`grade` AS '年级',
`Chinese` AS '语文',
`Mathematics` AS '数学',
`English` AS '英语',
`Synthesis` AS '文综'
FROM
student_grades AS g
INNER JOIN student_information AS i ON g.id = i.id -- ======== 根据学生信息表 `student_information` 查询所有学生成绩 左连表查询 ==========
SELECT
i.`Student_name` AS '学生姓名',
i.`grade` AS '年级',
`Chinese` AS '语文',
`Mathematics` AS '数学',
`English` AS '英语',
`Synthesis` AS '文综'
FROM
student_information AS i
LEFT JOIN student_grades AS g ON i.Student_name = g.Student_name -- ======== 根据成绩表 `student_grades` 查询所有学生成绩 右连表查询 ==========
SELECT
g.`Student_name` AS '学生姓名',
g.`grade` AS '年级',
`Chinese` AS '语文',
`Mathematics` AS '数学',
`English` AS '英语',
`Synthesis` AS '文综'
FROM
student_information AS i
RIGHT JOIN student_grades AS g ON i.Student_name = g.Student_name
MD5加密
-- ======== MD5 加密 ==========
INSERT INTO `mysqltext`.`users` ( `user`, `pwd` )
VALUES
('user1',MD5( '123456' ))
SELECT* FROM`mysqltext`.`users`
事务(ACID)
ACID 原理 脏读 幻读 ,ACID解释为:原子性,一致性,持久性,隔离性
-- ======== 事务 ACID 原理 脏读 幻读 ==========
-- ========ACDI 原子性 一致性 持久性 隔离性 ========
SET autocommit = 0
-- ========SET autocommit = 1 -- 默认为1(1 开启) ========
-- ======== A 给 B 账户转账 200 ========
UPDATE `mysqltext`.`bank` SET `balance` = `balance` - 200 WHERE `UserName` = 'A'
-- ======== B 收到 A 账户转账的 200 ========
UPDATE `mysqltext`.`bank` SET `balance` = `balance` + 200 WHERE `UserName` = 'B'
-- ======== 查看事务是否提交成功 ========
SELECT * FROM mysqltext.bank
-- ======== 事务回滚 ========
ROLLBACK
-- ======== 手动提交 ========
COMMIT
-- ======== 执行完成回复自动提交 ========
SET autocommit = 1
执行结果
SQL中循环插入数据
-- 插入100万数据.
DELIMITER $$
-- 写函数之前必须要写,标志
CREATE FUNCTION mock_datc()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
INSERT INTO `mysqltext`.`users`(`User_name`, `User_pwd`, `User_age`, `User_sex`, `User_phone`)
VALUES (CONCAT('User',15+i), UUID(), FLOOR(RAND() * 100), FLOOR(RAND() * 2), CONCAT('18',FLOOR(RAND() * (999999999 - 100000000)+100000000)));
SET i=i+1;
END WHILE;
RETURN i;
END;
SELECT mock_datc() -- 执行此函数 生成一百万条数据
delimiter $$ # 定义结束符
drop procedure if exists addTestData; # 存储过程名叫:addTestData
create procedure addTestData()
begin
declare number int;
set number = 1;
while number <= 1000000 #插入N条数据
do
INSERT INTO `my_we`.`user`(`name`, `username`, `password`, `age`, `mailbox`)
VALUES (CONCAT('Geore',number), CONCAT('User',number), UUID(), FLOOR(RAND() * 100)+1, CONCAT('18',FLOOR(RAND() * (999999999 - 100000000)+100000000),"@qq.com"));
set number = number + 1;
end
while;
end $$;
call addTestData()
第一次执行可能会报这个错误
大概意思为:
该函数声明中没有确定性、没有SQL或读取SQL数据,并且启用了二进制日志记录(您可能希望使用不太安全的日志变量
问题分析:
出现这种情况的原因一般是开启了主从复制。
一个执行更新的不确定子程序(存储过程、函数、触发器)是不可重复的,在从服务器上执行(相对与主服务器是重复执行)可能会造成恢复的数据与原始数据不同,从服务器不同于主服务器的情况。
在主服务器上,除非子程序被声明为确定性的或者不更改数据,否则创建或者替换子程序将被拒绝。这意味着当创建一个子程序的时候,必须要么声明它是确定性的数据。
解放方法:
设置信任:
set global log_bin_trust_function_creators=TRUE;
添加索引
什么是索引
索引(index)是帮助MySQL高效获取数据的数据结构(有效),在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。简而言之:帮助MySQL高效的查询出数据的数据结构叫做索引。
-- 索引的优势
-- 索引类似于书籍的目录,提高数据检索的效率,减少数据库IO的成本。
-- 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗。
-- 索引的劣势
-- 实际上索引也是一张表,存储在磁盘上,该表保存了主键与索引字段,并指向实体类的记录。
-- 虽然索引大大提高了查询的速度,但是降低了增删改的速度,对表进行update、insert、delete时,需要对索引文件进行更新。
所以一般使用在查询中可以大大增加查询的速度
-- 命名规范
-- id_表名_字段名
-- CREATE INDEX 索引名称 ON 表名(字段名)
CREATE INDEX id_users_User_name ON users('User_name');
SELECT * FROM users WHERE User_name = 'User9999';
使用到的表
/*
Navicat Premium Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 80013
Source Host : localhost:3306
Source Schema : mysqltext
Target Server Type : MySQL
Target Server Version : 80013
File Encoding : 65001
Date: 13/04/2023 09:21:28
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for bank
-- ----------------------------
DROP TABLE IF EXISTS `bank`;
CREATE TABLE `bank` (
`UserName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`balance` double(9, 2) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of bank
-- ----------------------------
INSERT INTO `bank` VALUES ('A', 400.00);
INSERT INTO `bank` VALUES ('B', 1900.00);
-- ----------------------------
-- Table structure for student_grades
-- ----------------------------
DROP TABLE IF EXISTS `student_grades`;
CREATE TABLE `student_grades` (
`id` int(11) NULL DEFAULT NULL COMMENT '学生唯一id( 与学生信息表id 一致 )',
`Student_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学生姓名',
`grade` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '年级',
`Chinese` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '语文成绩',
`Mathematics` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '数学成绩',
`English` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '英语成绩',
`Synthesis` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '文综'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student_grades
-- ----------------------------
INSERT INTO `student_grades` VALUES (1002, '曾华', '大一', '96', '90', '67', 'null');
INSERT INTO `student_grades` VALUES (1003, '匡明', '大一', '60', '100', '87', 'null');
INSERT INTO `student_grades` VALUES (1004, '王丽', '大二', '77', '99', '56', 'null');
INSERT INTO `student_grades` VALUES (1005, '李军', '大三', '87', '78', '78', 'null');
INSERT INTO `student_grades` VALUES (1006, '王芳', '大四', '67', '67', '76', 'null');
INSERT INTO `student_grades` VALUES (1007, '陆均', '大三', '89', '85', '87', 'null');
INSERT INTO `student_grades` VALUES (1008, '秦明', '大三', '89', '85', '76', 'null');
INSERT INTO `student_grades` VALUES (1009, '陆晓', '大二', '100', '99', '79', 'null');
INSERT INTO `student_grades` VALUES (10001, '王鹤', '大一', '89', '70', '79', 'null');
INSERT INTO `student_grades` VALUES (10010, '黎鸣', '大二', '100', '100', '100', 'null');
-- ----------------------------
-- Table structure for student_information
-- ----------------------------
DROP TABLE IF EXISTS `student_information`;
CREATE TABLE `student_information` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '唯一id',
`Student_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
`age` int(11) NULL DEFAULT NULL COMMENT '年龄',
`sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
`grade` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '年级',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1011 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student_information
-- ----------------------------
INSERT INTO `student_information` VALUES (1001, '曾华', 20, '男', '大一');
INSERT INTO `student_information` VALUES (1002, '匡明', 19, '男', '大一');
INSERT INTO `student_information` VALUES (1003, '王丽', 19, '女', '大一');
INSERT INTO `student_information` VALUES (1004, '李军', 21, '男', '大二');
INSERT INTO `student_information` VALUES (1005, '王芳', 22, '女', '大三');
INSERT INTO `student_information` VALUES (1006, '陆均', 23, '男', '大四');
INSERT INTO `student_information` VALUES (1007, '秦明', 22, '男', '大三');
INSERT INTO `student_information` VALUES (1008, '陆晓', 23, '女', '大三');
INSERT INTO `student_information` VALUES (1009, '王鹤', 20, '女', '大二');
INSERT INTO `student_information` VALUES (1010, NULL, NULL, NULL, NULL);
-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`User_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户 id',
`User_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名称',
`User_pwd` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户密码',
`User_age` int(4) NULL DEFAULT NULL COMMENT '用户年龄',
`User_sex` int(1) NULL DEFAULT NULL COMMENT '用户性别',
`User_phone` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户手机号',
PRIMARY KEY (`User_ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;