windows
- 尽量使用压缩包安装(exe 卸载不干净)
- 下载地址
- 解压复制到安装目录, 本人解压到的是D:\env\mysql-5.7.19
- 配置环境变量
- 我的电脑->属性->高级->环境变量->选择PATH
- 在其后面添加
mysql安装文件里bin目录路径: 例 D:\env\mysql-5.7.19\bin
- 在安装包下新建
my.ini文件: D:\env\mysql-5.7.19\my.ini[mysqld] basedir=D:\env\mysql-5.7.19\ # 替换成自己的安装目录, 要加斜杠 datadir=D:\env\mysql-5.7.19\data\ port=3306 skip-grant-tables # 跳过密码验证 - 以
管理员模式运行 CMD, 将路径切换至mysql下的bin目录, 输入mysqld –install(安装mysql) - 输入
mysqld --initialize-insecure --user=mysql初始化数据文件(生成 data 文件夹) - 启动mysql:
net start mysql - 进入管理界面:
mysql –u root –p - 修改 root 密码:
update mysql.user set authentication_string=password('root用户的密码') where user='root' and Host = 'localhost'; - 刷新权限:
flush privileges; - 修改
my.ini文件, 注释最后一句 skip-grant-tables - 重启mysql即可正常使用
net stop mysql net start mysql
SQLyog(企业版本v12.08):
- 注册名: kuangshen
- 注册码: 8d8120df-a5c3-4989-8f47-5afc79c56e7c
SQL 语句
- 清除已有语句：
\c
数据库操作
create database if not exists 数据库名 CHARACTER SET utf8 COLLATE utf8_general_ci; -- 创建: 基字符集 `utf8`; 数据库排序规则 `utf8_general-ci`
drop database if exists 数据库名; -- 删除
show databases; -- 显示 所有数据库
use 数据库名; -- 使用 数据库
show create database 数据库名; -- 显示创建数据库的语句
表定义(DDL: data definition language)
CREATE TABLE IF NOT EXISTS 表名(
-- '字段名1' 列类型 [属性][索引][COMMENT 注释],
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`address` varchar(100) DEFAULT NULL COMMENT '地址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
`version` COMMENT '乐观锁',
`is_delete` COMMENT '伪删除',
`gmt_create` COMMENT '创建时间',
`gmt_update` COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 注释内容
-- MyISAM : 节约空间及相应速度
-- InnoDB : 安全性, 事务处理及多用户操作数据表
DESC 表名; -- 表描述
use 表名; -- 使用 表
ALTER TABLE 表名 RENAME AS 新表名 -- 改表名
DROP TABLE IF EXISTS 表名; -- 删除表
show create table 表名; -- 显示创建数据表的语句
ALTER TABLE 表名 ADD 字段名 列约束 -- 添加字段
ALTER TABLE 表名 MODIFY 字段名 列约束 -- 修改列约束
ALTER TABLE 表名 CHANGE 字段名 新字段名 列约束 -- 修改列约束 + 字段名
ALTER TABLE 表名 DROP 字段名 -- 删除字段
数据值和列类型
- int: 标准的整数
- decimal: 字符串形式的浮点数, 金额计算的时候, 一般用 decimal
- varchar: 可变字符串 0~65535
- text: 文本串(保存大文本) 2^16-1
- datetime: 时间格式 YYYY-MM-DD HH:mm:ss
- timestamp: 时间戳
数值类型
字符串类型
日期和时间型数值类型
NULL值
- 理解为 "没有值" 或 "未知值"
- 不要用NULL进行算术运算 , 结果仍为NULL
数据字段属性
- UnSigned
- 无符号的
- 声明该数据列不允许负数 .
- ZEROFILL
- 0填充的
- 不足位数的用0来填充, 如int(3),5则为005
- Auto_InCrement
- 自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)
- 通常用于设置主键 , 且为整数类型
- 可定义起始值和步长
- 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
- SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)
- NULL 和 NOT NULL
- 默认为NULL , 即没有插入该列的数值
- 如果设置为NOT NULL , 则该列必须有值
- DEFAULT: 默认值
- 用于设置默认值
- 例如,性别字段,默认为"男" , 否则为 "女" ; 若无指定该列的值 , 则默认值为"男"的值
mysql –u root –p -- 连接数据库
exit; -- 退出 连接
update mysql.user set authentication_string=password('root用户的密码') where user='root' and Host = 'localhost'; -- 修改用户名
flush privileges; -- 刷新权限
表操作(DML: data manipulation language)
- DELETE 与 TRUNCATE 区别
- DELETE 会记录日志, TRUNCATE 不会
- 未重启数据库服务时: TRUNCATE 重置自增id, DELETE 不会
- 重启数据库服务后:
- DELETE 在 InnoDB 引擎下会重置自增id(是存储在内存中,断电即失)
- DELETE 在 MyISAM 引擎下不会重置自增id(存在文件中,不会丢失)
INSERT INTO 表名 (字段1,字段2,pwd,...) VALUES('值01','值02',MD5('密码')...),('值11','值12',MD5('密码')...);
UPDATE 表名 SET 字段1=值1,字段2=子查询结果,... WHERE 筛选条件; -- 更新
UPDATE 表名 SET pwd=MD5(pwd); -- 加密全部的密码
DELETE FROM 表名 WHERE 筛选条件; -- 删除数据
TRUNCATE [TABLE] 表名; -- 清空表数据: 重置自增id, 不影响事务
外键
- 操作的是物理外键, 数据库级别的外键(添加了额外的表), 不建议使用
- 最佳实践: 数据库就是单纯的表, 用程序查询来实现 外键
-- 方式1: 建表时
CREATE TABLE `student` (
`gradeid` INT(10) DEFAULT NULL COMMENT '外键字段,年级ID', -- 1. 定义对应的外键字段
KEY `FK_gradeid` (`gradeid`), -- 2. 定义`约束名` 与引用字段的对应(key名格式为`FK_字段名`)
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `引用表` (`引用列名`) -- 3. 约束 key, 是哪个外键字段, 引用`哪个表`的`哪个字段`
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 方式2(更方便): 建表后修改
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`); -- 约束 key, 是`当前表`哪个`引用字段`, 引用`哪个表`的`哪个字段`
-- 删除外键: 先删`引用子表`, 后删`主表`
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的,索引还在,所以还要删除索引
-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student DROP INDEX FK_gradeid;
表查询(DQL: Data Query Language)
SELECT * -- 选择哪些字段(AS 字段别名)
FROM 表名 as 表别名 -- 从哪张表里选择
LEFT|RIGHT|INNER JOIN 表2 as 表2别名 -- 连表查询: 左连接 右连接 内连接
WHERE 筛选条件 -- 筛选条件
GROUP BY 字段 -- 分组: 例 计算`男女生各自平均身高`, 则需将学生根据`男女`进行分组
HAVING 筛选条件 -- 分组后的筛选条件: 用了`GROUP BY`则不能用 WHERE, 只能用 HAVING
ORDER BY 排序字段 排序方式 -- 排序: 排序方式(DESC 倒序, ASC 升序)
LIMIT (page-1)*size, size; -- 分页
SELECT DISTINCT 字段 FROM 表名; -- 去除重复数据
SELECT 10*3-1 AS 计算结果 -- 用来计算(表达式)
SELECT @@auto_increment_increment; -- 查询自增的步长
-- 聚合函数
SELECT COUNT(列名 / * /1) as 总和 FROM 表名; -- 计数: 列名, 会忽略所有的 null 值; * 1 本质计算行数, 1 性能更好
SUM() as 总和 -- 求和
AVG() as 平均分 -- 平均数
MAX() as 最高分 -- 最大值
MIN() as 最低分
-- 普通函数
SELECT CONCAT('姓名: ', name) as 新名字 FROM 表名; -- 合并字符串
ABS(查询结果) -- 绝对值
CEILING(查询结果) -- 向上取整
FLOOR(查询结果) -- 向上取整
RAND() -- 随机数0~1
SIGN(查询结果) -- 判断一个数的符号: 0 0; 负数 -1; 正数 1
CHAR_LENGTH() -- 字符串长度
CONCAT('姓名: ', name) -- 合并字符串
INSERT(查询结果, pos, 删除个数, 插入的字符串) -- 插入, 替换
LOWER(查询结果) -- 小写字母
UPPER(查询结果) -- 大写字母
CURDATE() -- 当前日期
NOW() -- 当前时间
LOCALTIME() -- 本地时间 YYYY-MM-DD HH:mm:ss
SYSDATE() -- 系统时间
YEAR(NOW()) -- 年: month() day() hour() minute() second()
VERSION() -- 系统版本
USER() -- 当前用户
表连接
- 左连接 右连接 内连接
SELECT 字段1,表1别名.字段2,... -- 两张表都有的字段, 需指明是哪张表的
FROM 表1 表1别名
INNER JOIN 表2 表2别名
ON 表1别名.字段 = 表2别名.字段
INNER JOIN 表3 表3别名
ON 表1别名.字段 = 表3别名.字段
SELECT 省表.字段1 AS
SELECT 字段1 FROM 表1 LEFT JOIN 表2 ON 表1.字段n = 表2.字段n; -- 1
SELECT 字段1 FROM 表1 LEFT JOIN 表2 ON 表1.字段n = 表2.字段n WHRER 表2.字段n IS NULL; -- 2
SELECT 字段1 FROM 表1 INNER JOIN 表2 ON 表1.字段n = 表2.字段n; -- 3
SELECT 字段1 FROM 表1 RIGHT JOIN 表2 ON 表1.字段n = 表2.字段n; -- 4
SELECT 字段1 FROM 表1 RIGHT JOIN 表2 ON 表1.字段n = 表2.字段n WHRER 表1.字段n IS NULL; -- 5
筛选条件
-- 等于`=` 小于`<` 大于`>`
-- 不等于`!=` 小于等于`<=` 大于等于`>=`
-- IS NULL IS NOT NULL
WHERE id = 6;
WHERE id IS NULL;
-- 且: AND &&
-- 或: OR ||
-- 非: NOT !
WHERE id > 4 AND id < 8;
WHERE id > 8 OR id < 4;
WHERE NOT id = 6; -- WHERE id != 6;
-- 在 [m,n] 区间内(左闭右闭): BETWEEN m AND n
WHERE id BETWEEN 2 AND 6;
WHERE name LIKE '陈%'; -- %: 0~任意个字符; _: 1个字符
WHERE id IN (1,4,7); -- 在指定的范围内
事务(转账)
- 将
一组SQL放在一个批次中去执行 - ACID:
- 原子性: 要么都成功, 要么都失败
- 一致性: 事务前后的数据完整性要保证一致
- 隔离性: 多个用户并发访问数据库时, 数据库为每个用户用户的事务,不能被其他事务的操作数据所干扰
- 隔离所导致的问题: 脏读, 不可重复读, 虚读(幻读)
- 持久性: 事务一旦提交不可逆, 被持久化到数据库中
- Mysql 默认开启事务
-- Mysql 默认开启事务
SET autocommit = 0; -- 关闭事务
SET autocommit = 1; -- 开启
START TRANSACTION -- 1. 开始一个事务(标记事务的起始点)
COMMIT -- 提交
ROLLBACK -- 回滚
SET autocommit = 1; -- 还原`事务开启`
-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点
索引
- 索引准则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量(500万)的表建议不要加索引
- 索引一般应加在查找条件的字段
- 索引的作用
- 提高查询速度
- 确保数据的唯一性
- 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
- 全文检索字段进行搜索优化.
- 索引分类
- 主键索引 (Primary Key)
- 唯一索引 (Unique)
- 常规索引 (Index)
- 全文索引 (FullText)
- 主键索引
- 主键: 某一个属性组能唯一标识一条记录
- 特点
- 最常见的索引类型
- 确保数据记录的唯一性
- 确定特定数据记录在数据库中的位置
- 唯一索引
- 作用: 避免同一个表中某数据列中的值重复
- 与主键索引的区别:
- 主键索引只能有一个
- 唯一索引可能有多个
- 常规索引
- 作用: 快速定位特定数据
- 注意:
- index 和 key 关键字都可以设置常规索引
- 应加在查询找条件的字段
- 不宜添加太多常规索引,影响数据的插入,删除和修改操作
- 全文索引(百度搜索, 博客)
- 作用: 快速定位特定数据
- 注意:
- 只能用于MyISAM类型的数据表
- 只能用于CHAR , VARCHAR , TEXT数据列类型
- 适合大型数据集
- v5.6-: 只有 MyISAM 存储引擎支持全文索引
- v5.6+: MyISAM 和 InnoDB 存储引擎均支持全文索引;
- 数据类型为 char、varchar、text 及其系列才可以建全文索引
- 索引的数据结构
- hash类型的索引：查询单条快，范围查询慢
- btree类型的索引：b+树，层数越多，数据量指数级增长(innodb默认支持它)
- 不同的存储引擎支持的索引类型也不一样
- InnoDB 支持事务，支持行级别锁定，只支持 B-tree、Full-text 等索引
- MyISAM 不支持事务，支持表级别锁定，只支持 B-tree、Full-text 等索引
- Memory 不支持事务，支持表级别锁定，只支持 B-tree、Hash 等索引
- NDB 支持事务，支持行级别锁定，只支持 Hash 索引
- Archive 不支持事务，支持表级别锁定，不支持索引
- 不同索引的原理
-- 方法1: 创建表时
CREATE TABLE 表名(
-- 字段定义...
UNIQUE INDEX | KEY 索引名(字段1(长度) ASC|DESC, 字段2,...) -- 唯一索引
FULLTEXT INDEX | KEY 索引名(字段1(长度) ASC|DESC) -- 全文索引
SPATIAL INDEX | KEY 索引名(字段1(长度) ASC|DESC) -- 空间索引
);
-- 方法2: ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名(字段1(长度) ASC|DESC, 字段2,...);
ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(字段1(长度) ASC|DESC, 字段2);
ALTER TABLE 表名 ADD SPATIAL INDEX 索引名(字段1(长度) ASC|DESC, 字段2);
-- 方法3: CREATE在已存在的表上创建索引
CREATE UNIQUE INDEX 索引名 ON 表名 (字段1(长度) ASC|DESC, 字段2,...);
CREATE UNIQUE FULLTEXT 索引名 ON 表名 (字段1(长度) ASC|DESC, 字段2,...);
CREATE UNIQUE SPATIAL 索引名 ON 表名 (字段1(长度) ASC|DESC, 字段2,...);
-- 删除索引
DROP INDEX 索引名 ON 表名;
ALTER TABLE 表名 DROP PRIMARY KEY; -- 删除主键索引
-- 显示所有的索引信息
SHOW INDEX FROM 表名;
-- 分析查询过程
EXPLAIN SELECT * FROM 表名 WHERE 过滤条件;
sql 编程
DELIMITER $$ -- 写函数前必须写, 标志
CREATE FUNCTION mock_data() -- 创建一个函数
RETURNS INT -- 返回值类型
BEGIN -- 函数开始
DECLARE num INT DEFAULT 1000000; -- 定义一个变量, 默认值为 1000000
DECLARE i INT DEFAULT 0; -- 定义一个变量 i, 默认值为 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; -- 函数结束
SELECT mock_data(); -- 执行函数
权限
-- 用户信息表: mysql.user
CREATE USER 用户名 IDENTIFIED BY '密码'; -- 创建用户
SET PASSWORD = PASSWORD('密码'); -- 修改密码(当前用户)
SET PASSWORD FOR 用户名 = PASSWORD('密码'); -- 修改密码(指定用户)
RENAME USER 原用户名 TO 新用户名; -- 修改用户名
-- 给用户分配权限: GRANT 权限1,权限2 ON 库名.表名 TO 用户名;
GRANT all privileges ON *.* TO 用户名; -- 授予 `哪些权限` 在 `哪个库的哪个表` 上, 给 `哪个用户`
-- all privileges: 表示所有权限(但不能给别人授权)
-- grant option: 给别人授权的 权限
-- *.*: 表示所有库的所有表
-- 撤消权限: REVOKE 权限列表 ON 库名.表名 FROM 用户名;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名; -- 撤销所有权限
-- 查看权限
SHOW GRANTS FOR 用户名; -- 指定用户: root用户须加主机名(root@localhost)
SHOW GRANTS; -- 当前用户
SHOW GRANTS FOR CURRENT_USER; -- 当前用户
SHOW GRANTS FOR CURRENT_USER(); -- 当前用户
DROP USER 用户名; -- 删除用户
FLUSH PRIVILEGES; -- 刷新权限
权限列表
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 ALL PRIVILEGES。
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] ...
备份 & 导入
- 数据库备份必要性
- 保证重要数据不丢失
- 数据转移
- MySQL数据库备份方法
- mysqldump备份工具
- 数据库管理工具,如SQLyog
- 直接拷贝数据库文件
data文件夹和相关配置文件
-- ########## 导出 ##########
-- 1. 导出一张表: mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
mysqldump -uroot -p123456 school student >D:/a.sql
-- 2. 导出多张表: mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
mysqldump -uroot -p123456 school student result >D:/a.sql
-- 3. 导出所有表: mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
mysqldump -uroot -p123456 school >D:/a.sql
-- 4. 导出一个库: mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
mysqldump -uroot -p123456 -B school >D:/a.sql
-- ########## 导入 ##########
-- 1. 登录后: source 备份文件
source D:/a.sql
-- 2. 未登录: mysql -u用户名 -p密码 库名 < 备份文件
规范数据库设计
三大范式
- 第一范式: 原子性 保证每一列不可再分
- 第二范式: 每张表只描述一件事情
- 第三范式: 确保数据表中的每一列数据都和主键直接相关, 不能间接相关
规范性 和 性能的问题
关联查询的表不得超过 3 张表
- 考虑商业化的需求和目标, 数据库性能更重要
- 在规范性能的时候, 适当考虑下规范性
- 故意给某些表增加一些冗余的字段(从多表查询变成单表查询)
- 故意增加一些计算列(从大数据量降低为小数据量的查询)
数据库驱动
- 百度搜索
mysql-connector node
SQL 注入
字段值 = ' OR 1 = 1 -- ' -- 用 `OR 一个成立的表达式` 能查询所有的数据, 用`--`注释后面的语句
字段值 = ' ;DROP DATABASE (数据库名) --' -- 用`;`结束查询语句, 再执行删库操作, 用`--`注释后面的语句
SELECT * FROM 表名 WHERE `字段名`=字段值;
