DBMS 数据库管理系统
5.7用的多
安装尽可能用压缩包安装
- 下载并解压 https://dev.mysql.com/downloads/mysql/
- 配置环境变量（bin目录）
- 解压\mysql路径下新建配置文件my.ini
[mysqld]
basedir=D:\Program Files\mysql-5.7.19\ #基础目录
datadir=D:\Program Files\mysql-5.7.19\data\ #自动生成不要手动新建
port=3306
skip-grant-tables #跳过密码验证 - 管理员模式CMD进入bin运行
- 安装服务
mysqld -install - 初始化数据库
mysqld --initialize-insecure --console - 启动mysql, 启动后, 任务管理器-服务 可查看mysql服务
net start mysql - 进入管理界面，-p后面不能有空格，密码可为空
mysql -u root -p - 改密码
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; - 刷新权限
flush privileges; - 退出
exit 或 Ctrl + c
- 删除配置文件中 skip-grant-tables
- 重新启动mysql
net stop mysql
net start mysql
win环境安装SQLyog
新建数据库 字符集utf8 校对utf8_general_id
新建表 引擎InnoDB
SQL常用
-- /**/ 注释
show databases; -- 查看所有数据库
use XXX; -- 切换数据库
show tables; --查看数据库中所有的表
describe XXX; --显示表中所有信息
create database XXX; -- 创建数据库
数据库 DDL定义 DML 操作 DQL 查询 DCL 控制 语言
操作数据库
- 创建数据库
create database [if not exists] XXX - 删除数据库
drop database [if exists] XXX - 切换数据库
use XXX
use `` -- 当XXX为关键字特殊字符，`反引号，Tab键上面 - 查看数据库
show databases -- 查看所有数据库
数据库的列类型
- 数值
tinyint 1字节
smallint 2字节
mediumint 3字节
int 4字节 INT(M) M为显示宽度
bigint 8字节
float 4字节
double 8字节
decimal 字符串形式浮点数（金融计算） - 字符串
char 0~255
varchar 0~65535
tinytext 2^8-1
text 2^16-1 大文本 - 时间日期
date YYYY-MM-DD
time HH:mm:ss
datetime YYYY-MM-DD HH:mm:ss
timestamp
year - null
无值 未知
数据库的字段属性
- unsigned
整数类型为非负 - zerofill
用零填充 - 自增
通常为整型唯一的主键 - 非空
not null，不赋值会报错
null，不赋值为null - 默认
设置默认值
拓展：
- id
主键 - version
乐观锁 - is_delete
伪删除 - gmt_create
创建时间 - gmt_update
修改时间
创建表
create table [if not exists] XXX (
id INT(4) not null auto_increment comment 'XXX',
name varchar(30) not null default 'XXX' comment 'XXX',
pwd varchar(20) not null default 'XXX' comment 'XXX',
primary key(id)
)engine=innodb default charset=utf8
字段名 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]
字符串用’或“，字段之间用,逗号隔开
show create database [table] XXX -- 显示创建数据库(表)的语句
desc XXX -- 显示表的结构
表引擎
- innodb 默认
安全性高 - myisam 早期使用 无事务支持 无行锁 无外键 支持全文索引 表空间为innodb一半
节约空间 速度快
物理空间位置
/data 一个文件夹对应一个数据库
- innodb 表有.frm文件，以及上级目录ibdata1文件
- myisam 表结构文件.frm，数据文件.MYD，索引文件.MYI
字符集编码
默认编码（Latin1）不支持中文
- 创建表时改为 charset=utf8
- 配置文件my.ini中 character-set-server=utf8
修改删除
- 修改表
alter table XXX rename as YYY - 增加字段
alter table XXX add AAA int(10 - 修改字段（重命名、约束）
alter table XXX change AAA BBB int(10)
alter table XXX modify AAA varchar(10) - 删除表字段
alter table XXX drop AAA - 删除表
drop table [if exists] XXX
数据库级别的外键（物理外键，不建议使用）
主表，从表（被引用表）
被引用表不可删除
- 新建表时添加约束
create table if not exists XXX(
-- 定义key
keyFK_id(id)
-- 给外键添加约束
constraintFK_idforeign_key (id) referencesthattablename(id)
) - 新建表后添加约束
alter tablethistablenameadd constraintthattablename(id)
数据操作
select、from、join、where、group by、having、order by、limit
- 插入 一一对应
insert into 表名 (字段1，字段2...) values ('值1'),('值2')... - 修改
update 表名 set列1= 'AAA',列2='BBB' where id = 1;
没有where会全部行都修改
where子句 (=等于、不等于<> !=、闭合区间between and、与and &&、或or ||、非not !) - 删除
delete from 表名 where 条件
truncate 清空表，表结构、索引、约束不变（重新设置自增列，不影响事务） - 查询字段
select 字段1,字段2 from 表名
别名：字段、表 as 别名
拼接：concat('姓名：', studentname) as 新名字 from student
去重：select distinct
版本：select version()
计算：select 100*6 as 结果、selectstudentresult+ 1 from 成绩表
自增步长：select @@auto_increment_increment - 模糊查询
is [not] null --空字符串与null不同
like 模糊匹配 where studentname like '刘%' 可以用%代表一或多值，_代表一个值
in 在...中 where studentNo in (1001,1002,1003) 具体的值，不能用% _ -
联表查询 （自连接）
join 联接表
on 联接查询
where 等值查询
- inner join 表里至少有一个匹配
- left join 从左表返回所有值，即使右表没有匹配
- right join 从右表返回所有值，即使左表没有匹配
- 排序 order by
升asc 降desc - 分页 limit （瀑布流）
limit 起始值，页面大小
起始值 =（当前页-1）* 页面大小 - 子查询 效率高
where XXX = (嵌套查询) =具体值 in范围
mysql函数
abs() ceiling() floor() rand() sign() char_length concat() insert() lower() upper() instr() replace() substr() reverse() current_date() now() localtime() sysdate() year(now()) system_user() version()
聚合函数：where不能使用聚合函数
count(表名) 忽略null count(*) count(1) 不忽略null
sum() avg() max() min()
group by 通过什么字段来分组
having 过滤分组后
update XXXX set pwd=md5(pwd) --加密全部密码
事务（转账）
ACID原则：原子性 一致性 隔离性 持久性
脏读：一个事务读到另一个事务未提交数据
幻读：一个事务读取到另一个事务插入数据，导致前后不一致
宕机：事务没有提交，恢复原状；事务已经提交，持久化到库
set autocommit = 0 关闭自动提交（默认开启）
手动事务处理：
start transaction 事务开启
insert XXX
insert YYY
commit 提交，持久化
rollback 回滚（失败）
set autocommit = 1 开启自动提交（恢复）
保存点：（没用）
savepoint
rollback to savepoint 回滚到保存点
release savepoint 撤销保存点
索引
高效获取数据的数据结构，创建索引的行查询更快
索引原则：
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引加载常用的数据上
索引类型：
- 主键 primary key
表内唯一 - 唯一索引 unique key
避免出现重复列 - 常规索引 key/index
- 全文索引 full text
在特定的数据库引擎，快速定位数据
show index from XXX
explain --分析sql执行状况
(了解)
delimiter $$ 函数标志
create function XXX()
returns int
begin
函数体
return i;
end;
索引命名：id_表名_字段名
create index id_user_name on user(name)
alter table table_name add index index_name (column_list)
索引数据结构
- hash
- betree (innodb默认)
权限管理
用户表：mysql.user
create user XXX identified by '123456' --创建用户
set password [for XXX] = password('111111') --修改用户密码
rename user XXX to YYY
grant all privileges on 库.表 --用户授权，全部数据*.*，（不能给其他用户授权）
show grant --查看权限
revoke all privileges on 库.表 from XXX --撤销权限
drop user XXX --删除用户
备份
- 拷贝物理文件 data
- 命令行 mysqldump -h主机 -u用户 -p密码 库 表 >路径/文件名.sql
导入：登录情况下 source 路径/文件.sql
数据库设计
分析需求 概要设计（E-R图）
三大范式
1NF 每一列不可分
2NF 每张表只与一件事有关
3NF 每列与主键直接相关
规范与性能平衡 (阿里 查询不超过关联三个表)
数据库链接池
池化技术 （数据库链接 释放 消耗资源）
最小连接数 最大连接数 等待超时
数据库连接池（Datasource）DBCP C3P0 Druid（阿里）
