MySQL入门

MySQL 基础

SQLyog的安装与使用

  • SQLyog 64位破解版 v12.09
  • 选择完成后弹出注册窗口,我们填入软件的注册码:
    • 名称:ddooo
    • 证书秘钥:8d8120df-a5c3-4989-8f47-5afc79c56e7c
  • MySQL服务的启动和停止:
    • net start 服务名(启动服务)
    • net stop 服务名(停止服务)
  • MySQL服务的登录和退出:
    • 方式一:mysql自带的客户端(只限于root用户)
    • 方式二:命令提示符
      • 登录:mysql 【-h主机名 -P端口号 】-u用户名 -p密码
      • 退出:exit或ctrl+C
  • MySQL常用命令
    | 名称 | 命令 | | —- | —- | | 查看当前所有的数据库 | show databases | | 查看当前库的所有表 | show tables | | 查看其他库的所有表 | show tables from 库名 | | 查看表结构 | desc 表名 | | | | | | | | 创建表 | creat table 表名(列名 列类型,列名 列类型) |

  • MySQL的语法规范

    • 不区分大小写,但建议关键字大写,表名、列名小写
    • 每条命令最好用分号结尾
    • 每条命令根据需要,可以进行缩进或换行
    • 注释
      • 单行注释:#注释文字
      • 单行注释: — 注释文字(中间必须有空格)
      • 多行注释:/ 注释文字 /

        DQL语言

  • (Database Query Language)数据库表查询操作语言

  • 执行顺序:

    • select 查询的字段 ⑦
    • from 表1 别名 ①
    • 连接类型 join 表2 别名 ③
    • on 连接条件 ②
    • where 筛选条件 ④
    • group by 分组的字段 ⑤
    • having 分组后的筛选条件 ⑥
    • distinct 列名 ⑧
    • order by 排序的字段或表达式 ⑨
    • limit 偏移 ,条目数 ⑩

      基础查询

  • 语法:

    1. select
    2. 查询列表1,
    3. 查询列表2
    4. ···
    5. from
    6. 表名 ;
  • 特点:

    • 查询列表可以是:表中的字段、常量值、表达式、函数、 *(代表表中的所有字段)
    • 字符型和日期型的常量值必须用单引号引起来,数值型不需要
    • 查询的结果是一个虚拟的表格

      条件查询

  • 语法:

    1. select
    2. 查询的字段
    3. from
    4. 表名
    5. where 筛选条件 ;
  • 分类:

    • 简单条件运算符: > 、< 、= 、>=、<=、<> (不等于) 、<=> (安全等于)
    • 逻辑运算符:and、or、not
    • 模糊查询:like、between and(闭区间)、in、is null 、is not null (由于=、<>不能判断null值)
      • like一般搭配通配符使用,可以判断字符型或数值型
        1. //like
        2. like '_$_%' escape '$';
        3. //between and
        4. between 100 and 200;
        5. //in
        6. in(`A`,`B`);
  • 特点:where一定放在from的后面(挨着)

  • where不支持别名

    排序查询

  • 语法:

    1. select
    2. 查询的字段
    3. from
    4. where 筛选条件
    5. order by 排序列表 asc | desc,
    6. 排序列表 asc | desc ······;
  • 理解:

    • ASC:升序 (如果不写,默认是升序)
    • DESC:降序
  • 特点:order by支持别名

    分组查询

  • 语法:

    1. select
    2. 查询的字段,分组函数
    3. from
    4. 表名
    5. [where 筛选条件]
    6. group by 分组的字段(表达式或函数)
    7. [order by 排序的字段或表达式]
  • 分组后筛选语法:

    1. select
    2. 查询的字段,分组函数
    3. from
    4. 表名
    5. group by 分组的字段(表达式或函数)
    6. having 分组后的筛选条件;
  • 特点:

    • 可以按单个字段分组。也可以按多个字段分组,字段之间用逗号隔开
    • 和分组函数一同查询的字段最好是分组后的字段
    • group by、having支持别名

      多表连接查询

  • 查询的字段来自于多个表时用到

  • 分类:
    • sql92标准:在MySQL中仅支持内连接
    • sql99标准:在MySQL中支持内连接 + 外连接(左外连接、右外连接)+ 交叉连接
  • 特点:
    • 表支持别名(一般为表起别名,提高阅读性和性能)
    • 如果为表起了别名,就不能使用原来的表名去限定
    • n个表连接,至少需要n-1个连接条件
    • 多个表不分主次,没有顺序要求
  • 语法( sql99 ):

    1. select 查询的字段
    2. from 1 别名
    3. 连接类型 join 2 别名
    4. on 连接条件
    5. [where 筛选条件]
    6. [group by 分组的字段]
    7. [having 分组后的筛选条件]
    8. [order by 排序的字段或表达式]
  • 分类:

    • 内连接☆:inner(默认值,可省略)
      • 等值连接
      • 非等值连接
      • 自连接
    • 外连接:
      • 左外☆:left outer (outer可省略)
      • 右外☆:right outer
      • 全外:full outer
    • 交叉连接:cross
  • 理解:

    • 等值连接:等值连接的结果 = 多个表的交集
    • 左、右外连接:查询结果为主表中的所有记录。如果从表中有和它匹配的,则显示匹配的值,否则显示null
    • 全外连接 = 内连接的结果+表1中有但表2没有的+表2中有但表1没有的
    • 交叉连接 = 笛卡尔乘积的结果

      子查询

  • 含义:

    • 一条查询语句中又嵌套了另一条完整的select语句,其中被嵌套的select语句,称为子查询或内查询
    • 在外面的查询语句,称为主查询或外查询
  • 分类:
    • 标量子查询(结果集只有一行一列)[或称单行子查询]
    • 列子查询(结果集只有一列多行)[或称多行子查询]
    • 行子查询(结果集有一行多列)
    • 表子查询(结果集一般为多行多列)
  • 特点:

    • 子查询都放在小括号内
    • 子查询可以放在from后面、select后面、where后面、having后面,exists后面····· 但一般放在条件的右侧
    • 子查询优先于主查询执行,主查询使用了子查询的执行结果
    • 单行子查询,一般搭配单行操作符使用:> < = <> >= <=
    • 多行子查询,一般搭配多行操作符使用:any、all、some、in、not in | 操作符 | 含义 | | —- | —- | | in | not in | 等于列表中的任意一个 | | any | some | 和子查询返回的某一个值比较 | | all | 和子查询返回的所有值比较 |
  • 非法使用子查询的情况:

    • 子查询的结果类型于所需不匹配
    • 子查询的结果为空

      分页查询

  • 语法:

    1. select 字段|表达式
    2. from 表名
    3. [where 条件]
    4. [group by 分组字段]
    5. [having 条件]
    6. [order by 排序的字段]
    7. limit 起始的条目索引 , 条目数;
  • 特点:

    • 起始条目索引从0开始(可省略,默认为0)
    • limit子句放在查询语句的最后
    • 公式(page = 要显示的页数,sizePerPage = 每页显示条目数):
      1. limit (page - 1) * sizePerPage,sizePerPage

      联合查询

  • 将多条查询语句的结果合并成一个结果

  • 语法:

    1. select 查询列表 from 表名 where 条件
    2. union [all]
    3. select 查询列表 from 表名 where 条件
    4. union [all]
    5. ···
  • 特点:

    • union去重,union all不去重
    • 多条查询语句的查询的列的类型几乎相同
    • 多条查询语句的查询的列数必须是一致的

      杂知识点及常见函数

      单行函数

      | | 功能 | 说明 | | —- | —- | —- | | 字符函数 | | | | length | 获取字节个数 | utf8中一个汉字是3个字节 | | concat | 拼接字符 | 若用引号,建议用单引号 | | upper | lower | 大小写转换 | | | substr | 截取子串 | SQL中索引从1开始 | | instr | 返回子串第一次出现的索引 | 若找不到,则返回0 | | trim | 去前后指定的空格和字符 | trim(字符 from 字符) | | lpad | rpad | 左 | 右填充 | | | replace | 替换 | | | 数学函数 | | | | round | 四舍五入 | | | ceil | floor | 向上 | 下取整 | | | mod | 取模 | 结果符号和被除数正负同 | | truncate | 截断 | | | 日期函数 | | | | now | 当前系统日期+时间 | | | curdate | 当前系统日期 | | | curtime | 当前系统时间 | | | year|month ·· | 获取日期指定部分 | | | str_to_date | 将字符转换成日期 | 如(‘1998-3-2’,’%Y-%c-%d’) | | date_format | 将日期转换成字符 | | | 其他函数 | | | | version | 版本号 | | | database | 当前数据库 | | | user | 当前连接用户 | | | 流程控制函数 | | | | if | 处理双分支 | 类似于Java中三元运算符 | | case | 处理多分支 | |
  • 日期格式符: | 格式符 | 功能 | | —- | —- | | %Y | 四位的年份 | | %y | 两位的年份 | | %m | 月份(01,02) | | %c | 月份(1,2) | | %d | 日(01,02) | | %H | 小时(24小时制) | | %h | 小时(12小时制) | | %i | 分钟(00,01) | | %s | 秒(00,01) |

分组函数

  • 功能:做统计使用,又称统计函数、聚合函数、组函数 | | 功能 | 说明 | | —- | —- | —- | | sum | 求和 | | | max | 最大值 | | | min | 最小值 | | | avg | 平均值 | | | count | 计数 | |

  • 特点:

    • 以上五个分组函数都忽略null值,除了count(*)
    • sum和avg一般用于处理数值型,max、min、count可以处理任何数据类型
    • 都可以搭配distinct使用,用于统计去重后的结果
    • count的参数可以支持:字段、*、常量值(一般放1)

      起别名(as)

  • 格式:

    1. select 查询列表 as 别名;
  • 特定:

    • as 可省略
    • 别名若需要引号,建议使用双引号 “ “

      去重(distinct)

  • 格式:

    1. select distinct 查询列表;

    +号

  • 使用情况:

    • 两个操作数都为数值型,做加法运算
    • 若存在字符型,试图将字符型数值转换成数值型 。若转换失败,则将字符型数值转换成0
    • 只要有以个操作数为null,则结果肯定为null

      =号

  • 不能判断null值

  • <=> 安全等于号 既可以判断普通的数组,也可以判断null值

    通配符

  • 分类:

    • % (任意多个字符,包含0个字符)
    • _ (任意单个字符)
  • 想让通配符作为字符出现:

    • 方式一(推荐使用):

      1. like '_$_%' escape '$'; // $ 可替换为任意字符
    • 方式二:

      1. like '_\_%';

      DML语言

  • (Database Manipulation Language)数据库表数据操作语言

    插入

  • 语法:

    1. #方式1
    2. insert into 表名(列名 ,···)
    3. values(值1 ,,...),(值2 ,,...);
    4. #方式2
    5. insert into 表名
    6. set 列名1=值,列名2=值···
  • 特点

    • 可以为空的字段,可以不用插入值,或用null填充
    • 字段可以省略,但默认所有字段,并且顺序和表中的存储顺序一致
    • 方式1可以插入多行,支持子查询

      修改

  • 语法:

    1. #单表
    2. update 表名
    3. set 字段=新值,字段=新值
    4. [where 条件]
    5. #多表
    6. update 1 别名
    7. 连接类型 join 2 别名
    8. on 连接条件
    9. set 字段=新值,字段=新值
    10. [where 条件]

    删除

  • 语法:

    • 方式一(delete语句):

      1. #单表
      2. delete
      3. from 表名
      4. [where 筛选条件]
      5. #多表
      6. delete 别名1,别名2
      7. from 1 别名1
      8. 连接类型 join 2 别名2
      9. on 连接条件
      10. where 筛选条件;
    • 方式二(truncate语句):

      1. truncate table 表名
    • 两种方式对比:

      • truncate不能加where条件,而delete可以加where条件
      • truncate的效率高一点
      • truncate 删除带自增长的列的表后,如果再插入数据,数据从1开始
      • delete 删除带自增长列的表后,如果再插入数据,数据从上一次的断点处开始
      • truncate删除不能回滚,delete删除可以回滚

        DDL语言

  • (Database Definition Language)数据库表结构操作语言

    库和表的管理

  • 库的管理:

    1. #创建库
    2. create database (if not exists) 库名
    3. #删除库
    4. drop database (if exists) 库名
  • 表的管理:

    • 创建表:

      1. create table (if not exists) 表名(
      2. 列名1 列的类型1 [(长度) 约束],
      3. 列名2 列的类型2 [(长度) 约束],
      4. ·······
      5. );
    • 修改表:

      1. alter table 表名 操作名 column 列名 [列类型 约束]
      • 操作名:add | modify | drop | change | rename [to]
        • add (添加字段)
        • modify (修改字段类型和列级约束)
        • drop (删除字段)
        • change (修改字段名) alter table 表名 change column 旧列名 新列名 新类型 (类型不能省)
        • rename [to] (修改表名)
  • 删除表:

    1. drop table (if exists) 表名;
  • 复制表:

    1. #仅复制结构
    2. create table 表名1 like 表名2;
    3. #复制表的结构+数据
    4. create table 表名1
    5. select * from 表名2
    6. [where 条件]
  • 补充操作:

    • 创建mysql数据库用户

      1. create user tom identified by '新密码';
    • 授予权限
      ```

      授予通过网络方式登录的tom用户,对所有库所有表的全部权限,密码设为abc123.

      grant all privileges on . to tom@’%’ identified by ‘abc123’;

    给tom用户使用本地命令行方式,授予atguigudb这个库下的所有表的插删改查的权限。

    grant select,insert,delete,update on atguigudb.* to tom@localhost identified by ‘abc123’; ```

    常见类型

    数值型

    整形
  • 分类:tinyint(1)、smallint(2)、mediumint(3)、int / integer(4)、bigint(8) (括号内为所占字节数)

  • 特点:

    • 如果不特殊设置,则默认是有符号(+ -)
    • 在关键字后添加unsigned设置为无符号
    • 如果插入的数值超出了整形的范围,则会报out of range异常,并且插入临界值
    • 如果不设置长度,则会有默认值。
    • 长度代表了显示的最大宽度,如果不够,搭配zerofill使用会用0在左边填充(使用了zerofill,则默认为无符号)
      小数
  • 分类:

    • 浮点型:
      • float(M , D)
      • double(M , D)
    • 定点型:
      • dec(M , D)
      • decimal(M , D)
  • 特点:

    • M:整数位数+小数位数
    • D:小数位数
    • 如果超过范围,则同样插入临界值
    • M和D都可以省略。如果是decimal,则M默认为10,D默认为0。如果是float和double,则会根据插入的数值的精度来决定精度
    • 定点型的精确度较高,若对精确度有高要求 可选用

      字符型

  • 分类:

    • 较短的文本(M代表最大的字符数):
      • char(M):[M可以省略,默认为1。固定长度的字符。比较耗费空间。效率高]
      • varchar(M):[M不可以省略。可变长度的字符。比较节省空间。效率低]
      • binary和varbinary(用于保存二进制)
      • enum (用于保存枚举) 、set(用于保存集合)
  • 较长的文本:text、blob(较大的二进制)

    日期型

  • 分类:

    • data(日期)、time(时间)、year(年)
    • datetime(日期+时间) [8个字节。范围1000-9999。不受时区等影响]
    • timestamp(日期+时间) [4个字节。范围1970-2038。受时区等影响]

      常见约束

  • 一种限制,用于限制表中的数据。为了保证表中数据的的准确和可靠

  • 分类(六大约束):
    • not null :非空,用于保证该字段的值不能为空
    • default:默认,用于保证该字段有默认值
    • primary key:主键,用于保证该字段的值具有唯一性,且非空
    • unique:唯一,用于保证该字段的值具有唯一性,但可为空
    • foreign key:外键,用于保证该字段的值必须来自主表关联列的值(在从表添加外键约束,用于引用主表中某列的值,且主表的关联列必须是一个key[一般是主键 或 唯一])
    • check(MySQL不支持):检查
  • 添加分类:
    • 列级约束:六大约束语法上都支持(可加多个,用空格隔开),但外键约束没有效果
    • 表级约束:除了非空、默认,其他的都支持
  • 标识列:
    • 可以不用手动的插入值,系统提供默认的序列值,又称为自增长列 ( auto_increment )
    • 特点:
      • 一个表中至多有一个标识列
      • 标识符要求是一个key(主键、唯一键)
      • 标识符的类型只能是数值型
      • 标识符可以通过set auto_increment_increment = number 设置步长。可以通过手动插入值,设置起始值
  • 主键和唯一对比:
    | | 保证唯一性 | 允许为空 | 允许存在的个数 | 允许组合 | | —- | —- | —- | —- | —- | | 主键 | √ | × | 至多有一个 | √ (不推荐使用) | | 唯一 | √ | √ | 可以存在多个 | √ (不推荐使用) |

  • 创建表时添加约束,通用写法:

    1. create table if not exists stuinfo(
    2. id int primary key,
    3. stuname varchar(20) not null,
    4. age int default 18,
    5. seat int unique,
    6. majorid int,
    7. #表级约束
    8. [constraing fk_stuinfo_major] foreign key(majorid) references major(id)
    9. );
  • 修改表时添加约束,语法:

    1. #列级约束
    2. alter table 表名 modify column 字段名 字段类型 新约束;
    3. #表级约束
    4. alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用];
  • 修改表时删除约束,语法:

    1. #列级约束
    2. alter table 表名 modify column 字段名 字段类型 ;
    3. #表级约束
    4. alter table 表名 drop 约束类型(唯一键用的是index) [列名];

TCL语言

  • (Transaction Control language)事务控制语言

    数据库事务

  • 含义:通过一组逻辑操作单元 ( 一组DML——SQL语句 ),将数据从一种状态切换到另外一种状态

  • 事务的特性 (ACID):
    • 原子性(Atomicity):一个事务不可再分割,要么都执行要么都回滚
    • 一致性(Consistency):一个事务执行会使数据从一个一致状态 切换到 另外一个一致状态
    • 隔离性(Isolation):一个事务的执行不受其他事务的干扰
    • 持久性(Durability):一个事务一旦提交,则会永久的改变数据库中的数据
  • 事务的分类:
    • 隐式事务:事务没有明显的开启和结束的标记
    • 显式事务:事务具有明显的开启和结束的标记(前提:必须先设置自动提交功能为 禁用)
  • 相关步骤:
    1. 开启事务
    2. 编写事务的一组逻辑操作单元(insert、delete、update、select)
    3. 提交事务或回滚事务 ```

      开启事务

      set autocommit=0; start transaction;

      编写事务的一组逻辑操作单元

·······

结束事务(二选一)

commit;(提交事务) rollback;(回滚事务)

  1. - savepoint 节点名(设置保存点,只能搭配rollabck to 使用)
  2. - commit to 节点
  3. - rollback to 节点
  4. - 事务的并发问题:
  5. - 脏读:一个事务读取到了另外一个事务未提交的数据
  6. - 不可重复读:同一个事务中,多次读取到的数据不一致
  7. - 幻读:一个事务读取数据时,另外一个事务进行更新,导致第一个事务读取到了没有更新的数据
  8. - 事务的隔离级别:
  9. | | 脏读 | 不可重复读 | 幻读 |
  10. | --- | --- | --- | --- |
  11. | read uncommitted ( 读未提交 ) | | | |
  12. | read committed ( 读已提交 ) | × | | |
  13. | repeatable read( 可重复读 ) | × | × | |
  14. | serializable( 串行化 ) | × | × | × |
  15. - MySQL中默认第三个隔离级别
  16. - Oracle中默认第二个隔离级别(只支持read committed serializable)
  17. - 串行化可以避免任何并发问题,但性能十分低下,实际开发中不可能使用
  18. <a name="0d98c747"></a>
  19. ## 其他
  20. <a name="096449bd"></a>
  21. ### 视图
  22. - 虚拟表,和普通表一样使用
  23. - 视图的创建语法:

create view 视图名 as 查询语句;

  1. - 视图的查看

方式一:

desc 视图名;

方式二:

show create view 视图名;

  1. - 视图的修改:
  2. - 方式一:

若存在修改,否则创建

create or replace view 视图名 as 查询语句;

  1. - 方式二:

alter view 视图名 as 查询语句;

  1. - 视图数据的删除:

delete from 视图名;

  1. - 视图的删除:

drop view 视图名1,视图名2···;

  1. - 以下视图不能更新:
  2. - 包含以下关键字的sql语句:分组函数、distinctgroup byjoinhavingunion或者union all
  3. - 常量视图
  4. - select中包含子查询
  5. - from一个不能更新的视图
  6. - where子句的子查询引用了from子句中的表
  7. <a name="ddc7d28b"></a>
  8. ### 变量
  9. - 分类:
  10. - 系统变量
  11. - 全局变量 (作用域:将为所有的全局变量赋初始值,针对所有的会话有效,但不能跨重启服务器)
  12. - 会话变量 (作用域:仅针对当前会话(连接) 有效)
  13. - 自定义变量
  14. - 用户变量 (作用域:仅针对当前会话(连接) 有效)
  15. - 局部变量 (作用域:仅在定义它的beginend中有效)
  16. - 命令:
  17. - 查看系统变量:

show global | [session] variables [ like ‘XXX’ ];

  1. - 查看指定的某个系统变量的值

select @@global | [session].系统变量名

  1. - 为某个系统变量赋值

方式一

set global | [session] 系统变量名=值;

方式二

set @@global | [session].系统变量名=值;

  1. - 注意:
  2. - 全局级别用global 会话级别用session。如果不写,则默认session
  3. - 自定义变量:
  4. - 使用步骤:
  5. 1. 声明并初始化(局部变量可以不初始化)
  6. 2. 赋值
  7. - 用户变量(放在会话中的任何地方)

声明并初始化

set @用户变量名=值 set @用户变量名:=值 select @用户变量名:=值

赋值

select 字段 into @用户变量名 from 表名;

查看用户变量的值

select @用户变量名;

  1. - 局部变量(只能在begin end中,且为第一句话)

声明

declare 变量名 类型 [default 值];

赋值

set 局部变量名=值 set 局部变量名:=值 select @局部变量名:=值 select 字段 into 局部变量名 from 表名;

查看用户变量的值

select 局部变量名;

  1. <a name="a74b3354"></a>
  2. ### 存储过程
  3. - 一组预先编译的SQL语句的集合
  4. - 分类(in、out、inout都可以在一个存储过程中带多个):
  5. - 无返回无参
  6. - 仅仅带in类型,无返回有参
  7. - 仅仅带out类型,有返回无参
  8. - 既带in又带out,有返回有参
  9. - 带inout,有返回有参
  10. - 创建存储过程,语法:

create procedure 存储过程名(参数列表) begin 存储过程体(如果仅有一条SQL语句,可以省略 begin end ) end 结束标记

  1. - 参数列表包含:参数模式(in|out|inout)、参数名、参数类型
  2. - in:该参数只能作为输入,需要调用方传入值
  3. - out:该参数只能作为输出,返回值
  4. - inout:该参数既可以作为输入也可以作为输入,需要传递值,又可以返回值
  5. - 需要用 `delimiter 新的结束标记` 设置新的结束标记
  6. - 调用存储过程,语法:

call 存储过程名(实参列表) 结束标记

  1. - 删除存储过程,语法:

drop procedure 存储过程名;

  1. - 查看存储过程,语法:

show create procedure 存储过程名;

  1. <a name="870a51ba"></a>
  2. ### 函数
  3. - 特点:
  4. - 有且仅有一个返回
  5. - 创建函数,语法:

create function 函数名(参数列表) returns 返回类型 begin 函数体(如果仅有一条SQL语句,可以省略 begin end ) return 值; end 结束标记

  1. - 参数列表:参数名、参数类型
  2. - 调用函数,语法:

select 函数名(参数列表) 结束标记

  1. - 查看函数,语法:

show create function 函数名;

  1. - 删除函数,语法:

drop function 函数名;

  1. <a name="67138944"></a>
  2. ### 流程控制结构
  3. <a name="d3e31548"></a>
  4. #### 分支结构
  5. <a name="1795dd27"></a>
  6. ##### if 函数
  7. - 语法:

if(条件,值1,值2);

  1. <a name="70a06a04"></a>
  2. ##### case结构
  3. - 情况1 (类似于switch语句,一般用于实现等值判断)

case 要判断的字段或表达式 when 常量1 then 返回的值1或语句1;(若是值不用有分号,语句必须有分号) when 常量2 then 返回的值2或语句2; ··· else 返回的值或语句; (可以无else) end [case] (如果是放在 begin end中需要加上case,如果放在select后面不需要)

  1. - 情况2 (类似于多重IF语句,一般用于实现区间判断)

case when 条件1 then 返回的值1或语句1; when 条件2 then 返回的值2或语句2; ··· else 返回的值或语句; end [case]

  1. - 特点:
  2. - 可以作为表达式,嵌套在其他语句中使用,可以放在任何地方
  3. - 可以作为独立的语句使用,只能放在begin end
  4. <a name="240c3f39"></a>
  5. ##### if elseif 结构
  6. - 语法:

if 条件1 then 语句1; elseif 条件2 then 语句2; … else 语句n; end if;

  1. - 特点:
  2. - 只能用在begin end
  3. <a name="037bdbce"></a>
  4. #### 循环结构
  5. - 分类:whilelooprepeat
  6. - 循环控制:
  7. - iterate:类似于continue 结束本次循环继续下一次
  8. - leave:类似于break 结束当前所在的循环
  9. - 语法:

先判断后执行

[标签:] while 循环条件 do 循环体 end while [标签];

先执行后判断

[标签:]repeat 循环体 until 结束循环的条件 end repeat [标签];

没有条件的死循环

[标签:] loop 循环体 end loop [标签];

  1. - 特点:
  2. - 只能用在begin end
  3. <a name="61fcb360"></a>
  4. # MySQL 高级
  5. <a name="96758988"></a>
  6. ## Linux 下 MySQL 环境搭建
  7. - rpm -ivh xxx 命令 安装MySQL服务端和客户端(在c盘内找)
  8. > rpm -ivh MySQL-server-5.5.48-1.linux2.6.x86_64.rpm
  9. > rpm -ivh MySQL-client-5.5.48-1.linux2.6.x86_64.rpm
  10. - 查看是否安装成功
  11. > mysqladmin --version
  12. - 启动Mysql服务
  13. > service mysql start
  14. - 关闭Mysq服务
  15. > service mysql stop
  16. - 设置初始密码
  17. > /usr/bin/mysqladmin -u root password 123456
  18. - 设置mysql开机自启
  19. > chkconfig mysql on
  20. - 连接Mysql
  21. > mysql -u root -p
  22. - 修改配置文件位置
  23. > cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
  24. - 修改配置文件中设置的字符集(增加 加黑字体 内容)
  25. > (前几处在20行左右)
  26. > [client]<br />#password = your_password<br />port = 3306<br />socket = /var/lib/mysql/mysql.sock<br />**default-character-set=utf8**
  27. > [mysqld]<br />port = 3306
  28. > **character_set_server=utf8**
  29. > **character_set_client=utf8**<br />**collation-server=utf8_general_ci**<br />socket = /var/lib/mysql/mysql.sock<br />skip-external-locking<br />key_buffer_size = 384M<br />max_allowed_packet = 1M<br />table_open_cache = 512<br />sort_buffer_size = 2M<br />read_buffer_size = 2M<br />read_rnd_buffer_size = 8M
  30. > [mysql]<br />no-auto-rehash<br />**default-character-set=utf8**
  31. > (这个在100行左右)
  32. <a name="6e19b8f2"></a>
  33. ## 架构介绍
  34. <a name="d98d2cd9"></a>
  35. ### 逻辑架构
  36. - 连接层
  37. - 最上层是一些客户端 连接服务,包含本地 sock 通信 大多数基于客户端 / 服务端工具实现的类似于tcp / ip 的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案
  38. - 在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于 SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限
  39. - 服务层
  40. - 第二层架构主要完成核心服务功能,如 SQL 接口,并完成缓存的查询,SQL 的分析 优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如过程、函数等
  41. - 在该层,服务器会解析查询并创建相应的内部解析树,并对其完成相应的优化如确定查询表的顺序,是否利用索引等,最后生成相应的执行操作
  42. - 如果是 select 语句,服务器还会查询内部的缓存。如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统的性能
  43. - 引擎层
  44. - 存储引擎层,存储引擎真正负责了 MySQL 中数据的存储和提取,服务器通过 API 与存储引擎进行通信
  45. - 不同的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取
  46. - 存储层
  47. - 数据存储层,主要是将数据存储在运行于裸设备的文件系统之上,并完成与存储引擎的交互
  48. <a name="3f113298"></a>
  49. ### 存储引擎
  50. - 插件式的存储引擎架构将查询处理 其它的系统任务以及数据的存储提取相分离
  51. - 这种架构可以根据业务的需求 实际需要选择合适的存储引擎
  52. - MyISAM & InnoDB
  53. | 对比项 | MyISAM | InnoDB |
  54. | --- | --- | --- |
  55. | 主外键 | 不支持 | 支持 |
  56. | 事务 | 不支持 | 支持 |
  57. | / 表锁 | 表锁 | 行锁 |
  58. | <br /><br />缓存 | <br /><br />只缓存索引,不缓存真实数据 | 既缓存索引,还缓存真实数据<br />对内存要求较高,内存大小对性能有决定性影响 |
  59. | 表空间 | | |
  60. | 关注点 | 性能 | 事务 |
  61. | 默认安装 | Y | Y |
  62. - 表锁:操作一条记录也会锁住整个表,不适合高并发的操作
  63. - 行锁:操作时只锁某一行,不对其它行有影响,适合高并发的操作
  64. <a name="15c64aa0"></a>
  65. ### 配置文件
  66. - 二进制日志log - bin
  67. - 主从复制
  68. - 错误日志log - error
  69. - 默认是关闭的。用于记录严重的警告和错误信息,每次启动和关闭的详细信息等
  70. - 查询日志 log
  71. - 默认是关闭的,记录查询的 sql 语句,如果开启会降低 mysql 的整体性能,因为记录日志需要消耗系统资源
  72. - 数据文件
  73. - frm 文件:存放表结构
  74. - myd 文件:存放表数据
  75. - myi 文件:存放表索引
  76. <a name="202ffe9f"></a>
  77. ## 查询优化 & 索引优化
  78. <a name="bSl5b"></a>
  79. ### 索引
  80. - 索引是:帮助 MySQL 高效获取数据的数据结构
  81. - 我们平常所说的索引,如果没有特别指明,都是指B+树结构组织的索引
  82. - 结构:BTree索引、Hash索引、full-text 全文索引、R-Tree索引
  83. - 分类
  84. - **单值索引**:一个索引只包含单个列,一个表可以有多个单列索引
  85. - **唯一索引**:索引列的值必须唯一,但允许有空值
  86. - **复合索引**:一个索包含多个列
  87. - 基本语法
  88. - 创建

create [unique] index indexName on mytable(columnname(length))

如果是CHAR,VARCHAR类型,length可以小于字段实际长度; 如果是BLOB和TEXT类型,必须指定length

alter mytable add [unique] index [indexName] on (columnname(length)) 有四种方式来添加数据表的索引: ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。

ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。

  1. - 删除

drop index [indexName] on mytable

  1. - 查看

show index from table_name ```

  • BTree索引的检索原理
    • 一颗b+树,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示)
    • 非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项。真实的数据只存在于叶子节点

image.png

  • 需要创建索引的情况
    • 主键自动建立唯一索引
    • 频繁作为查询条件的字段应该创建索引
    • 查询与其它表关联的字段,外键关系建立索引
    • 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
    • 查询中统计或者分组字段
  • 不要创建索引的情况
    • 频繁更新的字段不适合创建索引
    • where 条件里用不到的字段不创建索引
    • 表记录太少
    • 经常增删改的表
    • 数据重复且分布平均的表字段

  • 性能下降 SQL 慢的几点原因
    • 查询语句写的烂
    • 索引失效
    • 关联查询太多 join
    • 服务器调优及 参数设置不合理(缓冲、线程数)