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
聚合函数
- 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 可读性强
  - 内连接是双方共有的就会显示，外连接是一方存在，一方不存在，内连接无法使用。
  - 外连接以一个表为主表。一个表为附表
  - 连接产生了主次关系。
  - 左外连接左连接
    - 左边的是主表
  - 右外连接右连接
    - 右边的是主表
- 全连接
笛卡尔积现象
- 两张表联查，不使用连接
避免笛卡尔积
- 连接时，加条件
- 匹配次数没有减少
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 子查询
  - 要保证后面子查询只有一条
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)
存储引擎
- 默认存储引擎 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;

posted @ 2021-11-16 14:53 kobe96 阅读(28) 评论(0) 编辑收藏举报

刷新页面返回顶部

kobe96

Mysql笔记

数据库SQL分类

公告