第二版MYSQL视频教程-韩忠康

SQL 之操作之操作 创建表 2013 年 9 月 10 日 22 43 create table db name tbl name 列名 字符型定义 列名 字符型 表属性 设置默认数据库 2013 年 9 月 10 日 22 46 Use db name 查看表 2013 年 9 月 10 日 22 48 有哪些表有哪些表 Show tables like pattern 例如 show tables like php 是 mysql 的通配符 某个表的创建信息某个表的创建信息 show create table tbl name 查看表结构 描述表结构 2013 年 9 月 10 日 22 50 Describe tbl name 简写是 desc tbl name 删除表 2013 年 9 月 10 日 22 51 drop table tbl name 如果表不存在 报错 完整版完整版 drop table if exists tbl name 如果表不存在 不报错 适用于 database 修改表名 2013 年 9 月 10 日 22 51 Rename table old tbl name to new tbl name 支持同时修改多个表名 中间用 间隔 支持夸数据库重命名 Rename table exam user to 1234 user 可以利用跨数据库重命名 为数据库重命名 创建一个新的数据库 旧的数据库内的表 都 rename 到新的数据库内 再删除旧的数据库 修改列定义 2013 年 9 月 10 日 22 54 修改表结构修改表结构 alter table tbl name add drop change modify 修改表选项修改表选项 Alter table tbl name 新的表选项 例如 alter table student character set utf8 1 增加一个新的列 增加一个新的列 add 新列定义 2 修改一个列的定义 修改一个列的定义 Modify 列定义 3 删除一个列 删除一个列 drop 列定义 4 重命名一个列 重命名一个列 Change Crud 2013 年 9 月 10 日 22 43 Create read update delete 增删改查 1 创建数据 插入数据 2 获得数据 查询数据 3 删除数据 4 修改数据 创建数据 2013 年 9 月 10 日 23 06 InsertInsert intointo 表名 字段列表 表名 字段列表 valuesvalues 值列表 值列表 如果需要在插入时 为所有的字段设置值 那么可以省略字段列表 要求是值得顺序 应该与表中的字段的顺序一致 获得数据 查询数据 2013 年 9 月 10 日 23 09 SelectSelect 字段列表字段列表 fromfrom 表名表名 查询条件查询条件 字段列表 可以使用 代替 表示所以字段 查询条件可以省略 表示所以的记录都获得 相当于 where 1 Where 是条件的意思 1 表示永远为真 有条件的 删除数据 2013 年 9 月 10 日 23 12 DeleteDelete fromfrom 表名表名 条件 条件 删除需要在逻辑上严格给条件 否则容易造成数据误操作 导致损失 语法上可以没有 where 如果需要删除所有数据 可以使用 where 1 修改数据 2013 年 9 月 10 日 23 13 UpdateUpdate 表名表名 setset 字段字段 新值新值 条件条件 校对规则 2013 年 9 月 10 日 23 06 每一套 编码字符集都有与之相关的校对规则 什么事校对规则 在当前编码下 字符之间的比较顺序是什么 查看变量设置查看变量设置 Show variables 常见校对规则 常见校对规则 ci 不区分大小写 cs 区分大小写 bin 编码比较 每个字符集都支持不定数量的校对规则 可以通过如下指令查看 Show collation 可以用 like 可以看到 字符集 地区名 比较规则 ci cs bin 不区分 区分 字节比较 在设置字符集的时 可以设置当前字符集所采用的校对规则 如果不设置校对规则 那么每一个字符集 都有一个默认的校对规则 以 GBK 为例 比较 chinese ci 和 bin 之间的区别 Order by 关键字 可以在获得数据时 将数据按照某个字段的排序 create table tbl3 name varchar 10 character set gbk collage gbk chinese ci create table tbl4 name varchar 10 character set gbk collage gbk bin insert into tbl3 values a insert into tbl3 values B insert into tbl3 values c insert into tbl4 values a insert into tbl4 values B insert into tbl4 values c 整形 2013 年 9 月 10 日 23 22 可以用 unsignedunsigned 控制是否有符号 不写就认为有符号 定义显示宽度 通过规定数据的先是宽度 达到统一显示的目的 类型 M M 表示显示的最小宽度是多少 需要使用前导零填充达到目的 称之为 zerofill 也存在布尔 bool 类型 但是就是 tinyint 1 的别名 小数 2013 年 9 月 10 日 23 26 单精度 float 有效数字 6 位左右 双精度 double 有效数字 16 位左右 支持 控制数值的范围 Type M D M 表示所有的数值位数 不包括小数点 和符号 D 表示允许的小数位数 定点数定点数 Decimal M D M 总位数 D 小数位数 范围存在 M 默认为 10 D 默认为 0 小数也支持 zerofill 和 unsigned 日期时间 2013 年 9 月 10 日 23 27 1 年月日时分秒 Datetime 范围 1000 01 01 00 00 00 到 9999 12 31 23 59 59 2 时间戳 Timestamp 存储时是整形 但是表示时是日期时间 检索时 0 可以检索时间戳 支持任意分隔符的日期 支持 0 值 表示当前是没有规定 2013 04 0 表示 4 月整个月 逻辑想法 3 time 类型 一天中的时间 表示时间间隔 在表示时间间隔时 可以使用天表示 格式 D HH MN SS D 天 4 year 类型 此时日期的范围 就不是 1000 9999 了 而是 1901 2155 一个字节表示 字符串类型 2013 年 9 月 10 日 23 29 1 Char M Char M M 表示允许的字符串长度 在 char 中表示严格限定的长度 2 Varchar M Varchar M M 表示允许的字符串长度 在 varchar 中表示允许的最大长度 Char 5 Varchar 5 5 个字符1 个字符Varchar 需要一个字节保存 字符串总长度 abc 5 个字符4 个字符 abcdf 5 个字符6 个字符 abcdefg M 表示的是字符数 而不是字节数 但是 总的长度的使用是按照字节计算的 最大长度是保存的最大字节数 M 是保存的最大字符数 注意 字段的最大长度 除了类型本身的限制 记录总长度也有限制 真实的 varchar 的长度 总长度 65535 Varchar 特点 当类型数据超过 255 个字符的时候 采用 2 个字节表示长度 65535 2 65533 整条记录 需要一个额外的字节 用于保存当前字段的 null 值 除非所有的字段都不是 null 这个字节才可以省略 一个记录 无论有多少个字段存在 null 都是使用统一的一个字节来表示 而不是每个字段一个字节 3 texttext 文本 有很多兄弟类型 Tinytext longtext 表示的字符串长度不一样 4 4 enumenum 枚举类型枚举类型 枚举类型用整形方式保存 节约空间 5 setset 集合类型集合类型 最多占用八个字节 最多可以表示出 64 个状态 定义列属性 2013 年 9 月 10 日 23 38 是否为空 是否为空 规定一个字段的值 是否可以为 null Null not null 默认 可以为 null 默认值属性 默认值属性 Default value 来声明 默认值 在没有为该字段设置值时是启动的 而默认值的设置需要使用固定值 常见的是 一个字段不能为空 而且存在默认值 主键丨唯一索引主键丨唯一索引 主键 PK primary key 可以惟一标识 某条记录的字段或者字段的集合 就是集合 组合主键 主键可以是 真是实体的属性 但是常用的好的解决方案 是利用一个与实体信息不相关的属性 作为唯一标识 主键与业务逻辑关系不发生关系 只用来表示记录 设置主键的语法 设置主键的语法 使用 PrimaryPrimary keykey 完成 两种方案 1 字段上设置 主键字符在插入时不能为空 或者冲突 2 在定义完字符后 可以定义 优势 可以定义多列主键 组合主键 用来标识记录 一个主键内包含两个字段 一个主键内包含多个字段 而不是多个字段都是主键 只需要一个唯一标识即可 mysql 规定只能存在一个主键 要求 常见的设计 每个表都应该存在一个可以唯一标识的主键字段 最好与实体没有联系 不是实体属性字段 自动增长 为每条数据提供给一个唯一的标识 每次插入记录时 将某个字段的值自动加 1 使用 auto increment 标识 需要整形 还需要有索引 插入数据时 可以选择插入 NULL 或者不插入 自动增长的初始值 是可以设置的 默认是 1 通过表的选项 auto increment num 如果 num 的值小于已经存在主键的值 则按现在最大主键继续增长 可以手动插入该列的值 并会自动排序 但是如果是主键的话 不能重复 该列可以更新 如果删除已存在主键的记录 删除后添加的新记录 外键 foreign key 2013 年 9 月 10 日 23 57 概念概念 如果一个实体 student 的某个字段 student class id 指向 引用 另一个实体 class 的主键 class class id 就称 student 实体的 class id 是外键 被指向的实体 称之为主实体 主表 也叫父实体 父表 负责指向的实体 称之为从实体 从表 也叫子实体 子表 作用作用 用于约束处于关系内的实体 增加子表记录时 是否有与之对应的父表记录 在删除或者更新主表记录时 子表应该如何处理相关的记录 定义一个外键定义一个外键 在从表上增加一个外键字段 指向主表的主键 使用关键字 foreign key Foreign key 外键字段 references 主表名 关联字段 主表记录删除时的动作 主表记录更新时的动作 设置级联操作 在主表数据发生改变是 与之关联的从表数据应该如何处理 主表更新 主表更新 On update 主表删除 主表删除 On delete 使用关键字 允许的级联动作 Cascade 关联操作 如果主表被更新或删除 那么从表也会执行相应的操作 Set null 设置为 null 主表数据被更新 主键值更新 从表的外键被设置为 null 主表记录被删除 从表相关记录外键被设置成 null 但注意 要求该外键列 没有 not null 属性约束 Restrict 拒绝父表删除和更新 先删除 再新建 通过修改表完成 alter table tbl name drop foreign key class id 删除外键需要通过指定外键名称达到目的 可以通过在创建外键时 定制名称 或者使用 mysql 默认生成的名称 Set null 删除测试 alter table tbl name add foreign key 外键定义 Cascade 删除测试 On update 指的是只有主表的主键发生变化 才会对从表发生影响 实体之间的关系 2013 年 9 月 11 日 0 03 1 1 设计 两个实体表内 存在相同的主键字段 如果记录的主键值等于另一个关系表内记录的主键值 则两条记录 1 1 对应 1 n 一对多 一个实体 对应多个其他实体 例如 一个班级对应多个学生 设计 再多的那端增加一个字段 用于指向该实体所属的另外的实体的表示 M N 多对多 设计 典型的 利用一个中间表表示尸体之间的对应关系 储存引擎 2013 年 9 月 11 日 19 06 表类型 默认的服务器表类型 用过 my ini 查看 可以配置 Default storage engine INNODB 在创建表或者编辑表时 可以指定表的存储引擎 利用表属性 engine 引擎类型 创建时指定表存储引擎 选择存储引擎的依据 1 性能 2 功能 MyisamMyisam 擅长 插入操作 检索操作擅长 插入操作 检索操作 InnodbInnodb 擅长 更新操作 删除操作擅长 更新操作 删除操作 选择表的存储引擎的标注 是否需要事物支持 是否支持外键 并发量的要求 错误恢复的处理 是否支持某些具体功能 最常用的存储引擎 Myisam indexed sequential access method 有索引的顺序访问方法 区别 区别 1 数据和索引的保存的文件不同 myisam 是分开保存 而 innodb 是保存到表空间 Myisam 一个表 三个文件 tbl name frm 结构 tbl name myd 数据 tbl name myi 索引 innodb 一个表一个文件 Tbl name frm 结构 所有的 innodb 表 都使用相同的 innodb 存储表空间在保存数据和索引 2 myisam 支持索引压缩 而 innodb 索引和数据是绑定保存不压缩的 体积大 3 innodb 很多时候是行级锁 而 myisam 是表级锁 innodb 的并发高 4 inndb 不支持 fulltext 类型的索引 新版本的 innodb 也支持 5 innodb 支持事务 外键 数据完整性约束要强 而 myisam 不支持 6 innodb 中不保存表的具体行数 也就是说 执行 select count from table 时 7 innodb 要扫描一遍整个表来计算有多少行 但是 myisam 只要简单的 读出保存好的行数即可 注意的是 当 count 语句包含 where 条件时 两种表 的操作是一趟的 8 对于 auto increment 类型的字段 innodb 中必须包含只有该字段的索引 但是 在 myisam 表中 可以和其他字段一起建立联合索引 Order by 2013 年 9 月 11 日 19 14 按照字段值进行排序 Order by 字段 升序丨降序 asc 丨 desc 校对规则决定排序关系 允许多字段排序 指的是 先按照第一个字段排序 如果说不能区分 才使用第二个字段 以此类推 注意 如果是分组 则应该使用对分组字段进行排序的 group by 语法 Distinct 2013 年 9 月 11 日 19 17 去除重复记录 重复的记录 指的是 字段值 都相同的记录 而不是部分字段相同的记录 相对的是 all 表示所有 默认就是 all 行为 Union 2013 年 9 月 11 日 19 17 联合查询 将多条 select 语句的记录 合并到一起 称之为联合操作 使用 union 关键字联合两个 select 语句即可 场景 获得数据的条件 出现逻辑冲突 或者很难在一个逻辑内表示 就可以拆分成 多个逻辑 分别实现 最后将结果合并到一起 环境 环境 获得 0115 班所有代课老师 结果按照代课天数升序排列 同时需要获得 0228 班 结果按照降序排序 注意 注意 如果 union 的结果存在重复的记录 那么会消除重复 可以通过 union 选项 all 达到目的 排序注意事项 排序注意事项 子语句结果的排序 1 将子语句包裹于括号内 2 子语句的 order by 只有在配合 limit 时 才生效 原因是 union 在做子语句时 会 对没有 limit 的子语句的 order by 优化 忽略 对所有结果进行统一排序 只需要在最后一个 select 语句后 增加相应的排序规则即可 提示 提示 子语句的括号不是必须的 但推荐使用括号包裹 注意 注意 规定 多个 select 语句的检索到的字段数 必须一致 更加严格的是 数据类型上 应该也有要求 一致 但是 mysql 内部会做类型转换 处理 要求是能够转换成功 检索结果中的列名称的问题 第一条 select 语句的列名而定 Limit 2013 年 9 月 11 日 19 16 限制获得的记录数量 Limit 语法 Limit offset row count Offset 偏移量 从 0 开始 可以省略 默认为 0 Row count 总记录数 如果数量大于余下的记录数 则获取余下的所有记录数 子查询 2013 年 9 月 11 日 19 21 场景 查询代课老师天数最多的信息 逻辑没问题 但是出现了代课天数一致的最多的如何处理 不能确定是否有重复 或者有几个重复的 换个思路 现货的最多的代课天数是多少天 再判断那个老师的代课天数和最大值是一样的 Mysql 允许将上面的查询结果 作为一个值来使用 Mysql 允许语句里面套语句 定义 语句内部的查询语句 就是子查询语句 注意 子查询必须用括号包裹起来 子查询分类 不同的分类 会有不同的使用方式 分类标准 分类标准 1 子查询出现的位置 2 子查询的返回值形式 返回值分类 单一值 标量子查询 一列 列子查询 表 多行多列 表子查询 出现为止 Where 型 where 后 From 型 from 后 Exists 型 exists 后 如何使用 标量子查询标量子查询 获得一个值之后 使用关系运算符 where 进行判断 列子查询列子查询 获得一列 通常是多个行的一列值 案例 获得所有带过 0228 班 讲师的信息 所有 0228 班讲师的姓名 再 利用这行姓名 查询其带过的所有班的记录 一定是使用集合类的操作符来完成 in 丨 not in 还有其他的集合操作符 Any 集合 集合中的任意一个 any 集合 等于集合中的任意一个即可 等同于 in All 集合 集合中的所有元素 all 集合 不等于集合中的所有元素 等同于 not in Some 集合 集合中的一些 集合中的一部分 等同于 any Not any 英文解释 表示不是任何一个的意思 在 mysql 中的含义 表示 不是其中任意一个即可 如果是 any 集合 不等于集合中的任意一个 只要与集合中的任意一 个元素不相等即可 总结 any 自然外连接 左外 natrual left join left join tbl name using 字符段 右外 natrual right join right join tbl name using 字符段 连接时 支持多表连接 Outfile 2013 年 9 月 11 日 19 37 将检索到的数据 保存到服务器的文件内 通过 select into outfile 文件地址 from select from teacher class select into outfile c myenvir one from teacher class 去相应的文件内查询 注意 可以自动创建文件 但是不能重写已有文件 也不能建立文件夹 生成的文件格式 默认的 采用行来区分记录 而采用制表符来区分字段 为了满足某种特殊的需求 会采用不同的分割方式 Mysql 支持 在导出数据时 设 置记录与字段的分割符 通过如下选项 Fields 设置