一、在pycharm中运行mysql编写登录注册功能
1.注册
先用navicat建立一张表，比如就ID主键，姓名，密码这三个字段建立表
# pycharm代码实操
# 1.还是先导入模块，创建pymysql的链接对象
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
passwd='15679', # 这是你的Mysql管理员的密码，记得带括号！
db='db5', # 刚才创建的表所在的库
charset='utf8mb4',
autocommit=True
)
# 2.建立一下游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.获取用户名和密码
while True:
username = input('请输入用户名>>>:').strip()
password = input('请输入密码>>>:').strip()
# 4.编写sql语句,但是先查询一次数据库是不是已经存在这个用户名了
sql1 = "select * from user_info where name = %s and pwd = %s"
# 5.通过游标发送数据到数据库
cursor.execute(sql1, (username, password))
res1 = cursor.fetchall()
if res1:
print('用户已存在')
continue
# 6.确定没有注册过的用户再发一次数据给数据库插入
sql = 'insert into user_info(name,pwd) values (%s,%s)' # 百分号这里不用管，后面有人帮你处理填入的东西
# 7.通过游标发送数据到数据库
cursor.execute(sql, (username, password))
res = cursor.fetchall()
if not res:
print('注册成功') # 正常插入数据是没有返回提醒啥的
break
2.登录
这个和注册差不多，甚至简单些
# 1.还行先导一下模块，创建pymysql的链接对象
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
passwd='15679', # 这是你的Mysql管理员的密码，记得带括号！
db='db5', # 刚才创建的表所在的库
charset='utf8mb4',
autocommit=True
)
# 2.建立一下游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.获取用户名和密码
while True:
username = input('请输入用户名>>>:').strip()
password = input('请输入密码>>>:').strip()
# 4.编写sql语句,但是先查询一次数据库是不是已经存在这个用户名了
sql1 = "select * from user_info where name = %s and pwd = %s"
# 5.通过游标发送数据到数据库
cursor.execute(sql1, (username, password))
res1 = cursor.fetchall()
if res1:
print('登录成功')
break
print('用户名或者密码错误')
二、视图
视图，相当于把查询后的表虚拟保存下来，供后续使用
语法：
create view xxx as 已经筛选出的结果产生的表
结论：
能少用就少用，只能查询，不能修改
# 可以查询，可以连表
SELECT `学生姓张`.`姓名`,`学生姓张`.`性别`,T.a as '平均分' FROM `学生姓张` INNER JOIN (SELECT student_id,AVG(num) as a FROM score WHERE student_id in (SELECT 学号 FROM `学生姓张`) GROUP BY student_id HAVING AVG(NUM) > 80) AS T ON T.student_id = `学生姓张`.`学号`;
三、触发器
1.delimiter 关键字
语法>>>：delimiter xx 即可
该关键字可以改当前运行的mysql环境的执行符号；变为其他执行的符号
一般和触发器一起使用，要做的事情做完后记得该回去，不然就麻烦大了
2.触发器
针对表数据的（增、删、改）——前、后共六种场景下，会自动触发的功能
语法结构>>>：
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row begin
sql 语句 #执行 取决于上述 表 是否做了 上述操作
end
3.案例
create table cmd (
id int primary key auto_increment,
USER CHAR (32),
priv CHAR (10),
cmd CHAR (64),
sub_time datetime, #提交时间
success enum('yes','no') #0代表执行失败
);
create table errlog(
id INT primary key auto_increment,
err_cmd CHAR(64),
err_time datetime
);
#触发器
delimiter $$ # 将mysql 默认的结束符由；换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row begin
if NEW.success = 'no' then # 新记录都会呗MySQL封装成NEW对象
insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
end if;
end $$
delimiter ; # 结束之后记得再改回来，不然后面结束符 就是$$了
#往表cmd 中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('kevin','0755','ls -l /etc',NOW(),'yes'),
('kevin','0755','cat /etc/passwd',NOW(),'no'),
('kevin','0755','useradd xxx',NOW(),'no'),
('kevin','0755','ps aux',NOW(),'yes');
# 查询errlog表记录
select * from errlog;
#删除 触发器
drop trigger tri_after_insert_cmd;
四、事务（重要）
1.什么是事务
事务，就是要做的或所做的事，数据库的事务指作为单个逻辑工作单元执行的一系列操作（SQL语句），这些操作要么全部不执行，要么全部执行。可以想象成一个容器，能放一堆SQL语句，因为他能保证数据的安全性、一致性所以多用于交易之间的数据库双边校验。
2.四大特性
ACID
A：原子性
事务中的各项操作是不可分割的整体，要么同时成功，要么同时失败
C：一致性
和原子性相辅相成，要么同时成功，要么同时失败
I：隔离性
多个事务之间，彼此不干扰，相当于每个人都在一个小房子里处理数据，不过最后提交的一刹那还是得遵守一致性。
D：持久性——永久性
操作完成后也通过了二次验证，无法回退，永远的完成了
# 代码验证
# 先创立一个员工薪资表
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`salary` decimal(10, 2) NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;
# 插入数据
INSERT INTO `employee` VALUES (1, '张三', 5000.00);
INSERT INTO `employee` VALUES (2, '李四', 8000.00);
INSERT INTO `employee` VALUES (3, '王五', 10000.00);
INSERT INTO `employee` VALUES (4, '小红', 9000.00);
INSERT INTO `employee` VALUES (5, '小晓', 3000.00);
INSERT INTO `employee` VALUES (6, '韩立', 12000.00);
INSERT INTO `employee` VALUES (7, '历飞羽', 8500.00);
INSERT INTO `employee` VALUES (8, '风希', 30000.00);
INSERT INTO `employee` VALUES (9, '蛮胡子', 5500.00);
SET FOREIGN_KEY_CHECKS = 1;
# 开启事务
start transaction；
# 开两个命令界面，都查询名为韩立的员工的薪资
select * from employee where name = '韩立';
# 在第二个命令界面改一下薪资+1000
update employee set salary=salary+1000 where name = '韩立';
# 在第二个命令界面查看现在韩立的薪资
select * from employee where name ='韩立' # >>> 13000
# 但是在第一个命令查看现在韩立的薪资，发现还是12000
select * from employee where name ='韩立'
# 这就是提现了事务的隔离性
# 这时候给第二个命令界面 执行commit操作，把数据刷入硬盘了。
commit
# 但是又去第一个命令查看现在韩立的薪资，发现还是12000
select * from employee where name ='韩立'
# 这时候在第一个命令界面改一下薪资+1000
update employee set salary=salary+1000 where name = '韩立';
# 在第一个命令界面查看现在韩立的薪资
select * from employee where name = '韩立'; # >>> 14000!!!
# 所以查的时候数据是当时时间节点的，但是改的时候mysql会再次去查一次，这是为了保证数据的安全性，每次改都会临时再去查，确实会降低些效率
关键字
start transaction: 申明开始事务
rollback 回滚
commit 提交，验证 同时结束事务
savepoint 节点，相当于存档点
相当于占位符（保留点）
创建占位符可以使用savepoint
创建 savepoint sp01;
回退到占位符地址 rollback to sp01;
保留点在执行rollback 或者 commit之后自动释放
- Mysql是完整的，默认每条sql开启事务，并且结束后自动执行commit，所以没法rollback，改完了就真的改完了
3.隔离级别——很重要，要背
在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB 支持所有隔离界别
语法>>>：set transaction isolation leval 级别
read uncomitted（未提交读） 【脏读】
事物中的修改即使没有提交，对其他事物也都是可见的，事物可以读取未提交的数据，这一现象也称之位脏读
read committed（提交读） 【不可重复读】
大多数 数据库系统默认的隔离级别
一个事物从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做不可重复读
repeatable read（可重复读） MySQL默认隔离界别
它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
能够解决“脏读”问题，但是无法解读“幻读”
phantom Read幻读 现象
所谓幻读指的是当某个事物在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生换行，InnoDB和XtraDB通过多版本并发控制（MVCC）及间隙繁琐策略解决该问题
serializable（可串行读）
相当于让事务排队，强制事务串行执行，很少使用该级别
4.一句话说MVCC
首先，只能在提交读和可重复读两种隔离级别下工作
工作原理，一句话，就是给数据加上两个标识号，用来标识有没有被修改
这样后续在执行改的最后的完成操作时，会再次查询标识号是否和前面拿的时候的一致，不一致则证明期间数据被动过，则重新再拿一次
五、存储过程
类似于python中的自定义函数
语法：
delmiter 临时结束符
create procedure 名字(参数，参数) # 相当于创建了一个函数
# 英文意思步骤
begin
sql语句;
end 临时结束符
delimiter ；
——————————————————————————————————————
# 如何调用？
call 名字（）即可
# 创建无参函数
delimiter $$
CREATE PROCEDURE p1()
BEGIN
SELECT * FROM employee;
END $$
delimiter ;
_______________________
# 注意创建了函数后不要重复创建了，去调用即可
CALL p1()
>>>
+----+------+----------+
| id | name | salary |
+----+------+----------+
| 1 | 张三 | 5000.00 |
| 2 | 李四 | 8000.00 |
| 3 | 王五 | 10000.00 |
| 4 | 小红 | 9000.00 |
| 5 | 小晓 | 3000.00 |
| 6 | 韩立 | 13000.00 |
| 7 | 历飞羽 | 8500.00 |
| 8 | 风希 | 30000.00 |
| 9 | 蛮胡子 | 5500.00 |
+----+------+----------+
# 创建有参函数 比如说封一个，查询某员工表id大于x的员工中薪资大于y的
# 员工的姓名
delimiter $$
CREATE PROCEDURE is_poor(
in m int,
in n int,
out res int
)
begin
select name from employee where id > 3 and salary > 5000;
set res =0; # 用来标志存储过程是否执行
end$$
delimiter ;
————————————————————————————
# 注意，因为要传出res，所以得传进去的时候传一个绑定值的变量名进去
# 我们可以在mysql命令下创建变量
set @res=1
# 可以查看
select @res;
_____________________________
# 调用函数
call is_poor(3,5000,@res)
# 得到结果
ps：日后工作，一般不是自己封，一般还是自己写sql查询，除非DB封了很好的方法
六、函数
mysql已经定义好了的，内置的
可以通过 help函数名 查看帮助信息
移除指定字符
select Trim() # 默认移除两边空额
select LTrim # 默认移除右边空格
select RTrim # 默认移除左边空格
大小写转换
select Lower # 全部变小写
select Upper # 全部变大写
获取左右起始 指定个数字符
select Left # 从左边开始获取指定个数字符
select Right # 从右边开始 获取指定个数字符
返回读音相似值（对英文效果）
# 挺有意思
where Soundex(name) = Soundex('') # 获取发音相似的数据
日期——date_format
在MySQl中 表示时间格式尽量采用 年-月-日 形式
create table blog(
id int primary key auto_increment,
name char (32),
sub_time datetime
);
insert into blog (name,sub_time)
values
('第一篇','2022-04-01 12:12:12'),
('第二篇','2022-05-01 12:12:12'),
('第三篇','2022-06-01 12:12:12'),
('第四篇','2022-06-10 12:12:12'),
('第五篇','2022-08-01 12:12:12'),
('第六篇','2022-08-15 12:12:12'),
('第八篇','2022-10-01 12:12:12'),
('第九篇','2022-11-01 12:12:12');
select date_format(sub_time,'%Y-%m') as '年-月',count(id) as '篇数'from blog group by date_format(sub_time,'%Y-%m'); #按年月分组 获取 年月 的文章id计数
# 按年月日 筛选 获取
select * from blog where Date(sub_time) = '22-04-01';
#按年月筛选
select * from blog where Year(sub_time)=2022 AND Month(sub_time)=08;
# 跟多 日期 处理拓展
adddate #增加一个日志
addtime #增加一个时间
datediff #计算两个日期差值
流程控制
if条件
delimiter // #临时修改结束符
create procedure proce_if()
begin
declare i int default 0;
if i = 1 THEN
select 1;
elseif i = 2 then
select 2;
else
select 7;
end if;
end //
delimiter ; #改回结束符
while循环
delimiter //
create procedure proc_while()
begin
declare num int;
set num = 0;
whele num < 10 do
select
num ;
set num = num + 1;
end whtle ;
end //
delimiter ;