Mysql笔记
数据库SQL分类
-
DQL 数据库查询
- Select相关语言
-
DML 数据库操作语言
- CRUD
-
DDL 数据库定义语言
- create drop alter truncate
-
TCL 事物控制语言
- 事务提交 commit
- 实物回滚 rollback
-
DCL 数据控制语言
- 授权grant
- 权限撤销revoke
-
数据导入
- use databasexx
- source 路径.sql
-
常用命令
- select version(); 查看版本号
- select database(); 查看数据库
- \c 终止
- 别名 select name as 'xx xx' 有空格使用单引号或双引号，最好用单引号，也可以不用as
- select sal * 12 from table 列乘12
-
优先级
- and优先级高于or
-
in 不是区间，是具体值的列表
- select* from table where job in('1','2')
-
排序
- select * from table order by sal asc, ename desc
- 按照sal一致进行排序，当一致时，使用desc进行降序
- select sal, ename order by 2
- 按照第二列排序
- select * from emp where sal between 1250 to 3000 order by sal desc
- select * from table order by sal asc, ename desc
-
聚合函数
- count
- sum
- avg
- max
- min
- 要先进行分组，再使用。未分组默认整个表是一组
- group by xx
- 注意点
-
分组函数 默认忽略null值，不需要先对null进行处理
-
count(* ) 统计所有行数 和 count每个字段，*是组合不为null，count每个字段是统计字段下不为null的总数
-
select ename from emp where sal > min(sal) 错误
-
执行顺序
- from
- where
- group by
- select
- order by
-
select 在分组之后执行
-
- group by 字段1，字段2 会根据第一个字段先聚合，后一个字段在之前字段内聚合
- having
- 在分组后筛选，用于开始无法使用where的情况
-
distinct关键字
- 要用在最前方
- 如果有多个字段，代表联合distinct
-
连接方式
- 内连接 inner join
- 等值连接
- on xx = yy
- 非等值链接
- on xx > yy and yy > zz
- 自连接
- 查询自己的领导 内连接
- 等值连接
- 外连接 outer join 可读性强
- 内连接是双方共有的就会显示，外连接是一方存在，一方不存在，内连接无法使用。
- 外连接以一个表为主表。一个表为附表
- 连接产生了主次关系。
- 左外连接 左连接
- 左边的是主表
- 右外连接 右连接
- 右边的是主表
- 全连接
- 内连接 inner join
-
笛卡尔积现象
- 两张表联查，不使用连接
-
避免笛卡尔积
- 连接时，加条件
- 匹配次数没有减少
-
sql92 和 sql99条件
- select xx
- from
- （inner）join // 连接 inner可以省略
- on // 连接条件
- where // 筛选条件
-
子查询
- where子查询
- select enamel,sal from emp where sal > (select min(sal) from emp)
- from后面的子查询可以当成一张临时表
- select 子查询
- 要保证后面子查询只有一条
- where子查询
-
union字段
- 合并结果集，在多表连接的情况下，可以优化代码。
-
limit
- 在order by后执行
- limit 开始索引 0开始 length长度
- limit length 长度
- limit pageSize * (pageNo - 1) pageSize
-
执行顺序
- from
- where（join on where）
- group by
- having
- select
- order by
- limit
-
建表
- DBL语句
- table 建议 t_ tb_
- 见名知意
- create table(字段名 1 类型,
字段名 2 类型,
);
- varchar（0-255） 自动变长，会根据实际长度，动态更新空间，速度慢。
- char（0-255） 定长字符串，速度快，容易浪费空间
- clob 可存4G以上字符串
- int（0-11） java int
- bigint java long
- float 单精度浮点
- double 双精度浮点型
- blob
- date 短日期类型
- datetime 长日期类型
-
插入日期类型
- str_to_date(日期，'%Y-%m-%d')
- %Y 年 %y 使用两位
- %m 月
- %d 日
- %h 小时
- %i 分
- %s 秒
-
查询日期
- date_format(字段名birth,'%y-%m-%d')
-
mysql 默认格式
- %Y-%m-%d 不需要使用str_to_date()，或者date_format()
- %Y-%m-%d %h:%i:%s
-
获取现在时间
- now()
-
批量插入
- insert into t_user(id,name,birth) values('','',''),(),(),(),()
- creat t_users select * from t_user 将查询结果当成一张表
- insert into t_user2 select * from t_user 插入一个表 嵌套select表
-
delete 删除 DML语句
- 比较慢，删除效率低
- 空间不会释放
- 优点：支持回滚，后悔可以回复数据
-
truncate 物理删除 DDL语句
- 删除效率高，表被一次截断
- 缺点：不支持回滚
- 优点：快速
- truncate table dept;
-
约束
- 非空约束 not null
- 只有列级约束
- 唯一约束 unique
- 联合唯一约束 unique(x1,x2)
- 主键
- mysql中，当not null 和unique联合使用的时候，会默认成为主键
- 开发建议单一主键，不使用复合主键
- 列级约束只能添加一个，但是可以在表级约束上添加第二个
- 建议int ，bigint，不建议varchar
- 自然主键：id 1 2 3 auto_increment
- 业务主键：身份证号，银行卡号等
- 外键
- 外键约束，外键字段，外键值
- 先删子表，再删父表
- 先创建父表再删子表
- 先插入父，再插入子
- foreign key(cno) references t_class(classno)
- 外键可以为null
- 作为外键的元素至少具有unique约束
- check
- 列级约束
- 加在字段后的约束
- x1 type not null,
- 表级约束
- 在建表的最后
- unique(x1,x2)
- 非空约束 not null
-
存储引擎
-
默认存储引擎 engine = innodb
-
默认存储方式 default charset = utf8
-
支持的引擎 9大存储引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ -
myisam用三个文件表示每一个表
- 格式文件.frm文件 表结构
- 数据文件.myd文件 数据
- 索引文件。myi文件 索引
- 主键或者有unique约束的字段会自动创建索引
- 可以缩小扫描范围
- 优势
- 可以被压缩、只读表来节省空间
-
重量级存储引擎
- 每个innodb表，以.frm格式文件保存
- innodb表空间tablespace被用于储存表内容，以及索引
- Innodb 默认支持事务
- commit提交 savepoint检查点 rollback回滚，支持事务
- Innodb 支持崩溃后自动恢复
- 提供全acid兼容
- 多版本mvvc和行级锁定
- 支持外键及引用的完整性
- 效率不高 不能压缩
-
memory存储引擎（heap引擎）
- 数据和索引都存入内存中
- 不能包含blob和text
- 查询效率最高
- 关机后数据消失
- 索引存在内存
-
-
索引
- 全表扫描
- 根据索引扫描
- 机制，扫描某个区间，利用排序好的区间
- 需要排序，索引的实现是使用 treeMap
- B-tree
- 采用中序遍历方式取数据
- 结构
- 自平衡二叉树，btree
- mysql使用b+树，比一般的b-tree要短
- 过程
- 每一行数据都对应一个硬盘位置编号
- 先去二叉树里找id对应的硬盘位置地址
- 改写sql，直接去硬盘查
- 找到对应的数据
- 通过物理编号，查到结果
- 什么适合索引
- 条件一：数据量庞大
- 条件二：某个字段经常出现在where后面，经常被扫描
- 条件三：该字段的增删改少
-
索引创建语句
- create index [ name ] on [ table ] (字段);
- 解释语句
- explain sql语句
-
索引失效
- 使用%T，以%开头无法通过索引查询
- or语句，一边有索引，一边没有索引，索引失效。
- 使用复合索引，没有使用左侧的列，导致索引失效。 最左原则，和B+树有关
- where中使用索引的列进行数学运算
- where中的索引列使用了函数
-
索引是优化的手段
- 单一索引 ： 一个字段添加索引
- 复合索引 ： 两个字段或者更多字段添加索引
- 主键索引 ： 主键添加索引 默认
- 唯一索引 ： unique 约束自动添加索引
-
视图
- dql语句，才能以view的形式创建
- create view index_view as select * from xx;
- drop view index_view;
- 可以做什么
- 对视图view的操作可以影响原表
- 相当于创建了一个引用，指向了一个sql语句
- update可以同时更新多个表的数据
- view 也是文件，但是是引用
-
DBA语句
-
Create USER 'username' IDENTIFIED by 'password' 创建用户
-
GRANT all privileges on . to 'p361'@'%'identified by '123';
-
Revoke
-
数据导出(命令行)
- mysqldump databaseName > D:\database.sql -uroot -p123456 // 导入一张表
- mysqldump databaseName emp > D:\database.sql -uroot -p123456 // 导出一张表
-
数据导入(进入mysql)
- create database databaseName;
- use databaseName;
- source D:\database.sql;
-
