目录
一、常用函数
-- ===============常用函数============================
-- 数学运算
SELECT ABS(-8); -- 绝对值
SELECT CEILING(7.9); -- 向上取整
SELECT FLOOR(7.9); -- 向下取整
SELECT RAND(); -- 返回一个0~1之间的随机数
SELECT SIGN(10); -- 判断一个数的符号 0返回0 负数返回-1 正数返回1
-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航'); -- 返回字符串的长度 #10
SELECT CONCAT('我','爱','你们'); -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱'); -- 查询,从某个位置开始替换某个长度 #超级热爱编程helloworld
SELECT REPLACE('狂神说坚持就能成功','坚持','努力'); -- 替换出现的指定字符串 #狂神说努力就能成功
SELECT LOWER('KuanghShen'); -- 小写字母
SELECT UPPER('KuangShen'); -- 大写字母
SELECT INSTR('kuangshen','h'); -- 返回第一次出现的子串的索引 #7
SELECT SUBSTR('狂神说坚持就能成功',4,6); -- 返回指定的字符串 (源字符串,截取的位置,截取的长度) #坚持就能成功
SELECT REVERSE('清晨我上马'); -- 反转字符串
-- 查询姓周的同学名字,周改为邹(school表中)
SELECT REPLACE(`studentname`,'周','邹') 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());
-- 系统
SELECT SYSTEM_USER(); -- 获取系统用户
SELECT USER(); -- 获取系统用户
SELECT VERSION(); -- 获取当前数据库的版本
二、聚合函数
函数名称 | 描述 |
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
... | ... |
-- ==============聚合函数===================
-- 都能够统计表中的数据(想查询一个表中有多少个数据,就使用这个count())
SELECT COUNT(`borndate`) FROM `student`; -- Count(字段),会忽略所有的null值
SELECT COUNT(*) FROM `student`; -- Count(*),不会忽略null值,本质就是计算行数
SELECT COUNT(1) FROM `student`; -- Count(1),不会忽略所有的null值,本质就是计算行数
SELECT SUM(`studentresult`) AS 总和 FROM `result`;
SELECT AVG(`studentresult`) AS 平均分 FROM `result`;
SELECT MAX(`studentresult`) AS 最高分 FROM `result`;
SELECT MIN(`studentresult`) AS 平均分 FROM `result`;
三、数据库级别的MD5加密(扩展)
什么是MD5?
主要增强算法复杂度和不可逆。
MD5不可逆,具体的值MD5是一样的
-- ===========================测试MD5 加密====================
CREATE TABLE `testmd5`(`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 明文密码
INSERT INTO `testmd5` VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')
-- 加密
UPDATE `testmd5` SET `pwd`=MD5(pwd) WHERE id=1;
UPDATE `testmd5` SET `pwd`=MD5(`pwd`) -- 加密全部密码
-- 插入的时候加密
INSERT INTO `testmd5` VALUES(4,'xiaoming',MD5('123456'))
-- 如何校验:将用户传递进来的密码,进行md5加密,然后对比加密后的值
SELECT* FROM `testmd5` WHERE `name`='xiaoming' AND `pwd`=MD5('123456');
四、事务
要么都成功,要么都失败
事务原则:ACID原则-->原则性、一致性、隔离性、持久性 (脏读、不可重复读、幻读......)
参考博客链接:直接搜ACID即可
执行事务:
-- =======================事务===============================
-- mysql是默认开启事务自动提交的
SET autocommit=0;/*关闭*/
SET autocommit=1;/*开启(默认的,mysql默认是开启的)*/
-- 手动处理事务
SET autocommit=0; -- 关闭自动提交
-- 事务开启
START TRANSACTION; -- 标记一个事务的开始,从这个之后的sql在同一个事务内
INSERT xx
INSERT xx
-- 提交:持久化(成功才提交!)
COMMIT;
-- 回滚:回到的原来的样子(失败!)
ROLLBACK;
-- 事务结束
SET autocommit=1; -- 开启自动提交
-- 了解
SAVEPOINT 保存点名; -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名; -- 回滚到保存点
RELEASE SAVEPOINT 保存点名; -- 撤销保存点
执行事务:
-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci; -- 创建数据库,检查字符集
USE shop;
CREATE TABLE account(id INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
money DECIMAL(9,2) NOT NULL, -- decimal表示整数部分7位长度,小数部分最大2位
PRIMARY KEY(id))ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO account(`name`,money) VALUES ('A',2000.00),('B',10000.00);
-- 模拟转账:事务
SET autocommit=0; -- 关闭自动提交
START TRANSACTION; -- 开启事务(一组事务)
UPDATE account SET money=money-500 WHERE `name`='A'; -- A减500
UPDATE account SET money=money+500 WHERE `name`='B'; -- B加500
COMMIT;-- 提交事务(一旦提交就被持久化了)
ROLLBACK;-- 回滚(没有提交,数据还是会回到原来一开始的地方)
SET autocommit=1; -- 恢复默认