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;