代码(续)
MySQL常用函数
- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个 0~1 之间的随机数
SELECT SIGN(10) -- 判断一个数的符号 0-0 负数返回-1,正数返回 1
- 字符串函数
SELECT CHAR_LENGTH('我只想做你的太阳') -- 字符串长度
SELECT CONCAT('哈','撒','剋') -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') -- 查询,从某个位置开始替换某个长度
SELECT LOWER('Stephen Curry') -- 小写字母
SELECT UPPER('Stephen Curry') -- 大写字母
SELECT INSTR('Stephen Curry','e') -- 返回第一次出现的子串的索引
SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换出现的指定字符串
SELECT SUBSTR('我不相信坚持就能成功',4,6) -- 返回指定的子字符串 (源字符串,截取的位置,截取的长度)
SELECT REVERSE('清晨我上马') -- 反转
- 查询姓 周的同学,名字 邹
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()
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
事物
要么都成功,要么都失败
一一一一一一一一一一一
SQL 执行:
A 给 B 转账
A 1000 —> 200
B 200
SQL 执行
B 收到 A 的钱
A 800 —> B 400
一一一一一一一一一一一
将一组SQL 放在一个批次中去执行
执行事物代码流程
-- mysql 是默认开启事务自动提交的
SET autocommit = 0 /* 关闭 */
SET autocommit = 1 /* 开启(默认的) */
-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始,从这个之后的 sql 都在同一个事务内
INSERT xx
INSERT xx
-- 提交: 持久化 (成功!)
COMMIT
-- 回滚: 回到的原来的样子 (失败!)
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
- 新建一个shop数据库
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
- 建表
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(9) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
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'
UPDATE `account` SET money=money+500 WHERE `name`='b'
-- 提交事物,事物就被持久化了
COMMIT;
-- 如果没提交成功,数值回滚
ROLLBACK;
-- 恢复默认事物自动提交
SET autocommit=1;
索引
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
索引的分类:
主键索引 (PRIMARY KEY )
唯一的标识,主键不可重复,只能有一个列作为主键
唯一索引 (UNIQUE KEY)
避免重复的列出现,唯一索引可以重复,多个列都可以标识位 唯一索引
常规索引 (KEY/INDEX)
默认的, index。key 关键字来设置
全文索引 (FullText)
在特定的数据库引擎下才有,MyISAM 快速定位数据
测试:新建app_user表,在其中插入1000000条数据,然后比较有索引和没索引的查询时间
- 建表
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别(0:男;1:女)',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) 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用户表'
- 插入1000000条数据的代码
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()*((999999999100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i = i+1;
END WHILE;
RETURN i;
END;
- 执行插入数据代码
SELECT mock_data();
在没有索引的情况下查询“用户1”
SELECT * FROM app_user WHERE `name` = '用 户1'
创建索引
CREATE INDEX id_app_user_name ON app_user(`name`);
在有索引的情况下查询“用户1”
SELECT * FROM app_user WHERE `name` = '用 户1'
通过在数据量很大的库中测试,我们发现搜索时间在创建了索引后变得很小
结论:索引在小数据量的时候,用处不大,但是在大数据的时候,区别十分明显
权限管理
我们可以通过 SQLyog中的可视化管理
也可以通过代码来管理
- 创建用户
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER czx IDENTIFIED BY '123456'
- 修改用户密码
-- 修改密码 (修改当前用户密码)
SET PASSWORD = PASSWORD('123456')
-- 修改密码 (修改指定用户密码)
SET PASSWORD FOR czx = PASSWORD('123456')
- 修改用户名
重命名 RENAME USER 原来名字 TO 新的名字
RENAME USER czx TO czx2
- 用户授权
-- 用户授权 ALL PRIVILEGES 全部的权限 , 库表
-- ALL PRIVILEGES 除了给别人授权,其他都能够干
GRANT ALL PRIVILEGES ON *.* TO czx2
- 查询权限
-- 查询权限
SHOW GRANTS FOR czx2
-- 查看指定用户的权限
SHOW GRANTS FOR root@localhost
- 撤销权限
-- 撤销权限 REVOKE 哪些权限, 在哪个库撤销,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM czx2
- 删除用户
-- 删除用户
DROP USER czx2
MySQL备份
备份的好处:
1.保证重要的数据不丢失
2.数据转移
- 备份操作
在想要备份的数据库或者表上右键,备份/导出(这里选择导出)
规范数据库设计
当数据库比较复杂的时候,我们就需要对数据库进行设计了
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据库插入和删除都会麻烦、异常 【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计:
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
数据库设计三大范式:
第一范式(1NF)
原子性 : 保证每一列不可再分
第二范式(2NF)
前提:满足第一范式
每张表只描述一件事情
第三范式(3NF)
前提:满足第一范式 和 第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关
数据库部分完…