<tbody> <tr> <td id="artContent" style="max-width: 656px;"> <div style="width: 656px; margin: 0; padding: 0; height: 0;"></div> <ul><li><p>DQL：</p></li></ul><p>数据查询语言（凡是带有 select 关键字的都是查询语句）</p><p>select...</p><ul><li><p>DML：</p></li></ul><p>数据操作语言（凡是对表中的<strong>数据</strong>进行增删改的都是 DML）</p><p>insert 增 delete 删 update 改</p><ul><li><p>DDL：</p></li></ul><p>数据定义语言（凡是带有 create、drop、alter 的都是 DDL）</p><p>主要操作的是<strong>表的结构</strong>，不是表的数据</p><ul><li><p>TCL：</p></li></ul><p>事务控制语言（包括：事务提交 commit、事务回滚 rollback）</p><ul><li><p>DCL：</p></li></ul><p>数据控制语言（授权 grant、撤销权限 revoke）</p><ul><li><p><strong>desc 表名</strong> 查看表的结构</p></li><li><p><strong>select 命令</strong></p></li><li><p>select * from 表名; 查看表的信息</p></li><li><p>select version(); 查看当前使用的版本号</p></li><li><p>select database(); 查看当前使用的数据库</p></li><li><p>c 用来终止一条命令的输入</p></li><li><p><br></p></li><li><p><strong>source 地址xxx.sql</strong></p></li><li><p>xxx.sql 这种文件被称为 sql 脚本文件</p></li><li><p>批量的执行 sql 语句，可以使用 sql 脚本文件</p></li><li><p>PRI UNI MUL<br># 如果键是PRI，则列是主键或多列主键中的列之一。<br># 如果键是UNI，则该列是唯一索引的第一列。(唯一索引允许多个空值，但可以通过检查Null字段来判断该列是否允许空。)<br># 如果键为MUL，则该列是非唯一索引的第一列，其中允许在列中多次出现给定值。</p></li></ul><ul><li><p><strong>select 字段名 from 表名</strong></p></li><li><p>若查询多个字段，用逗号隔开即可</p></li><li><p><strong>查询所有字段</strong></p></li><li><p>不建议使用 *，因为它会先将 * 转为所有字段，效率低，可读性差，建议写上所有字段。</p></li><li><p><strong>select 字段名 as 别名 from 表名</strong></p></li><li><p>只是将 指定字段 显示的查询结果字段名显式为别名（select 语句永远不会进行修改操作）</p></li><li><p>as 可以省略，用空格代替</p></li><li><p>若别名含有空格，可以用单引号（或双引号，但在oracle数据库中使用不了）引起来，字符串标准的使用单引号括起来</p></li><li><p><br></p></li><li><p><strong>字段可以使用数字表达式</strong></p></li></ul><p><strong>select 字段 from 表名 where 条件;</strong></p><ul><li><p>= 等于</p></li><li><p><> 或 != 不等于</p></li><li><p>< 小于 <= 小于等于</p></li><li><p>'>' 大于 >= 大于等于</p></li><li><p><strong>between ... and ...</strong> 表示两个值之间（闭区间） 等同于 >= and <=</p></li><li><p><strong>is null</strong> 为 null （<strong>is not null</strong> 不为空，数据库中 null 代表什么都没有）</p></li><li><p>and 并且 or 或者 （若两者同时出现，<strong>and 的优先级 比 or的优先级高</strong>，可加括号避免）</p></li><li><p><strong>in</strong> 包含，相当于多个 or（not in 不在这几个值当中的数据）</p></li></ul><p><strong>in(具体值，具体值，......)</strong> 不是区间</p><ul><li><p>not 取非，主要用在 is 或 in 中</p></li><li><p><strong>like</strong> 模糊查询，支持 % 或 下划线匹配</p></li><li><p><strong>%</strong> 匹配任意个字符</p></li><li><p><strong>下划线</strong>，一个下划线只能匹配任意一个字符（<strong>若想匹配下划线，可以加上转义字符</strong>）</p></li><li><p>select ename from emp where ename like '%K' 找出以 K 结尾的名字</p></li></ul><ul><li><p><strong>select 字段名 from 表名 order by 字段名</strong> 默认是升序</p></li><li><p>select 字段名 from 表名 order by 字段名 desc 指定降序（Descending order）</p></li><li><p>select 字段名 from 表名 order by 字段名 <strong>asc</strong> 指定升序（Ascending order）</p></li><li><p><br></p></li><li><p><strong>多个字段排序</strong></p></li><li><p>select 字段名 from 表名 order by 字段名 控制字符，字段名 控制字符，...</p></li><li><p><strong>只有前者相同的情况下，才会轮到后者排序</strong></p></li><li><p><br></p></li><li><p>根据字段的位置进行排序</p></li><li><p>select 字段名 from 表名 order by 字段名的列位置（不建议在开发中写，不健壮，列的顺序容易修改）</p></li><li><p><strong>select ... from ... where ... order by ...</strong></p></li><li><p>执行顺序 from -> where -> select -> order by（排序总是在最后）</p></li><li><p><br></p></li><li><p><strong>在处理使用Mysql时，数据表采用utf8字符集，使用中发现中文不能直接按照拼音排序</strong></p></li><li><p>select * from musician_ordered order by convert(name using gbk) collate gbk_chinese_ci;</p></li><li><p>对name字段进行gbk编码，然后，对编码后的内容根据gbk_chinese_ci进行整理排序</p></li><li><p>这样得到的结果，英文是排在中文前面的，而且是根据拼音排序的</p></li><li><p>gbk_chinese_ci：按照普通的字母顺序排，不区分大小写</p></li><li><p>gbk_bin：按照二进制顺序排，区分大小写</p></li></ul><p>一个输入对应一个输出，和其对应的是多行处理函数（多个输入，对应一个输出）</p><ul><li><p><strong>lower</strong> 转换小写</p></li><li><p>select lower(字段名) from 表名</p></li><li><p><strong>upper</strong> 转换大写</p></li><li><p>select upper(字段名) from 表名</p></li><li><p><strong>substr</strong> 取子串</p></li><li><p>select substr(字段名，起始下标（从1开始），截取的长度)</p></li><li><p><strong>length</strong> 长度</p></li><li><p>select length(字段名) from 表名</p></li><li><p><strong>concat</strong> 进行拼接</p></li><li><p>select concat(字段名，字段名) from 表名</p></li><li><p><strong>trim</strong> 去空格</p></li><li><p>select 字段名 from 表名 where 字段名 = trim(' % ')</p></li><li><p><strong>str_to_date</strong> 将字符串转换成日期</p></li><li><p><strong>date_format</strong> 格式化日期</p></li><li><p><strong>format</strong> 设置千分位</p></li><li><p><strong>round</strong> 四舍五入</p></li><li><p>若 select 后面接字面量/字面值，则生成一个表，其中的数据全部变为这个字面量，字段名为 这个字面量。</p></li><li><p>select round(数值，保留几个小数) from 表名</p></li><li><p>若为 -1 例如：1236.567 -> 1240 保留到十位</p></li><li><p>若为 -2 例如：1236.567 -> 1200 保留到百位</p></li><li><p><strong>rand</strong> 生成随机数</p></li><li><p>select rand() from 表名 生成 [0-1）的随机数</p></li><li><p>例如：select round(rand()<em>100</em>, 0) from 表名 生成100以内的随机数</p></li><li><p><strong>ifnull</strong> 可以将 null 转换成一个具体值</p></li><li><p>数据库中只要 NULL 参与运算，结果就为 NULL</p></li><li><p><strong>ifnull（数据，被当作哪个值）</strong> 如果数据为 NULL，则将其当作后面指定的值</p></li><li><p><strong>case ... when ... then ... when ... then ... else ... end</strong> 类似于 if-else 语句</p></li></ul><p>输入多行，最终输出一行</p><ul><li><p>count 计数</p></li><li><p>sum 求和</p></li><li><p>avg 平均值</p></li><li><p>max 最大值</p></li><li><p>min 最小值</p></li></ul><ul><li><p>分组函数在<strong>使用的时候必须先进行分组</strong>，然后才能用；</p></li></ul><p>如果你<strong>没有对数据进行分组，整张表默认为一组</strong>。</p><ul><li><p>分组函数自动忽略 NULL， 不需要提前对 NULL 进行处理</p></li><li><p>count（*） 统计表当中的总行数。（不可能有一行记录所有的列都为 NULL）</p></li><li><p>分组函数不能直接使用在 where 子句中因为 where 子句执行的时候，还没有进行分组</p></li><li><p>所有的分组函数可以组合起来一起用 （select min(),max(),... from 表名）</p></li></ul><p>在实际的应用中，可能需要先进行分组，然后对每一组的数据进行操作</p><ul><li><p>select ... from ... group by ...</p></li><li><p>select ... from ... where ... group by ... order by ...</p></li><li><p>执行顺序：<strong>from -> where -> group by -> select -> order by</strong></p></li><li><p>在一条 select 语句当中，如果有 group by 语句的话，select 后面只能跟：<strong>参加分组的字段以及分组函数</strong>，其他的一律不能跟</p></li></ul><ul><li><p><strong>多个字段联合分组</strong></p></li><li><p>select xxx from xxx group by 字段1，字段2，...</p></li><li><p>使用 <strong>having</strong> 可以对分完组之后的数据进一步过滤，having 不能单独使用（单独使用没有意义），必须和 group by 联合使用(<strong>很耗资源，尽量少用</strong>)</p></li><li><p>优化策略：where 和 having，优先选择 where，where 实在完成不了了，再选择 having</p></li><li><p>select Colle, max(Credit) MC from course group by Colle having MC >= 4 order by MC desc;</p></li><li><p>执行顺序 <strong>from -> group by -> having -> select -> order by</strong> (为啥 having 后面可以使用别名？（<strong>MYSQl可以这么做是因为MYSQL用的是临时表，在having前已经产生了数据，所以你可以用别名，但SQL Sever不可以，SQL是在having后才Select</strong>）因为mysql对此作了扩展。在mysql 5.7.5之前的版本，ONLY_FULL_GROUP_BY sql mode默认不开启。在5.7.5或之后的版本默认开启。)</p></li></ul><div></div><ul><li><p>把查询结果去除重复记录</p></li><li><p>需要放在所有字段的最前面</p></li><li><p>若 distinct 后接多个字段，表示多个字段联合起来去重</p></li></ul><ul><li><p><strong>内连接</strong></p></li><li><p>等值连接</p></li><li><p>非等值连接</p></li><li><p>自连接</p></li><li><p><strong>外连接</strong></p></li><li><p>左外连接（左连接）</p></li><li><p>右外连接（右连接）</p></li><li><p><strong>全连接</strong></p></li></ul><ul><li><p>若将两张表进行连接查询，且没有任何条件的限制，会发生笛卡尔现象</p></li><li><p><strong>最终查询结果的条数，是两张表条数的乘积</strong>（匹配次数也是）</p></li><li><p>如何避免？连接时加上<strong>条件</strong>，满足这个条件的记录被筛选出来</p></li><li><p><strong>但匹配次数不会减少</strong>，加上条件只是为了避免笛卡尔积现象，并为了筛选出有效的记录例：select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno（<strong>SQL92</strong>)这种写法可以提高效率，不会导致字段名在两个表中都找</p></li><li><p>通过笛卡尔积现象可以得出，表的连接次数越多效率越低，需要尽量减少表的连接次数</p></li></ul><p><strong>案例：</strong>查询每个员工所在部门的名称，显示员工名和部门名？</p><p>emp e 和 dept d 表进行连接。条件是：e.deptno = d.deptno</p><p>SQL92语法：（结构不够清晰，表的连接条件和后期进一步筛选的条件，都放到了 where 子句中）</p><pre>select e.ename, d.dnamefrom emp e, dept dwhere e.deptno = d.deptno;</pre><p>SQL99语法：（表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加 where 子句）</p><div></div><pre>select ...from ...inner join ...on ... #（非等值条件）</pre><p><strong>技巧：</strong>把一张表看成两张表</p><div></div><pre>select ...from ...left (outer) join ...on ... # 筛选条件</pre><p><strong>思考：</strong>外连接的查询结果条数 >= 内连接的查询结果条数</p><div></div><pre>SELECT stu.*,sc.c_id,sc.score,co.c_name,te.t_nameFROM stu JOIN sc on stu.s_id=sc.s_idJOIN coon sc.c_id=co.c_idJOIN teon co.t_id=te.t_id;</pre><p>select 语句中 嵌套 select 语句，被嵌套的 select 语句称为 子查询。</p><div></div><ul><li><p>union 的效率要高一些（相对多表连接，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻...，但是 union 可以减少匹配的次数，还可以完成两个结果集的拼接（乘法变成了加法））</p></li><li><p>注意：</p></li><li><p>union 进行结果集合并的时候，要求两个结果集的列数相同</p></li><li><p>两个结果集的数据类型也需要相同（oracle），但 MySQL 中可以不相同</p></li></ul><pre>select ... from ... where ...unionselect ... from ... where ...</pre><p>将查询结果集的一部分取出来。（通常使用在分页查询当中）</p><ul><li><p>limit startIndex, length （起始下标，长度） 起始下标从 0 开始</p></li><li><p>缺省用法：limit 5 这是取前 5</p></li></ul><ul><li><p>limit 在 order by 之后执行</p></li></ul><div></div><pre>select (5) ...from (1) ...where (2) ...group by (3) ...having (4) ...order by (6) ...limit (7) ...</pre><blockquote><p>建表属于 DDL 语句，DDL 包括：create drop alter</p></blockquote><div></div><blockquote><p>表名建议以 t_ 或者 tbl_ 开始，可读性强，见名知意。</p><p>字段名：见名知意</p><p>表名和字段名都属于标识符</p></blockquote><ul><li><p><strong>varchar</strong>（最长 255）</p></li><li><p>可变长度的字符串，比较智能，节省空间，会根据实际的数据长度动态分配空间</p></li><li><p>优缺点</p></li><li><p>优点：节省空间</p></li><li><p>缺点：需要动态分配空间，速度慢</p></li><li><p><strong>char</strong>（最长 255）</p></li><li><p>定长字符串，不管实际的数据长度是多少，分配固定长度的空间取存储数据，使用不恰当的时候，可能会导致空间的浪费</p></li><li><p>优缺点</p></li><li><p>优点：不需要动态分配空间，速度块</p></li><li><p>缺点：使用不当可能会导致空间的浪费</p></li></ul><blockquote><p>varchar 和 char 一般根据实际情况进行选择，若字段固定长度一般选择 char，长度不一则选择 varchar</p></blockquote><ul><li><p><strong>int</strong>（最长 11）</p></li><li><p>数字中的整数型（ java中的 int）</p></li><li><p><strong>bigint</strong></p></li><li><p>数字中的长整型（java 中的 long）</p></li><li><p><strong>float</strong></p></li><li><p>单精度浮点型数据</p></li><li><p><strong>double</strong></p></li><li><p>双精度浮点型数据double (M,D) M：表示有效数字的最大位数 D：表示小数点后的位数</p></li><li><p><strong>date</strong>（不包括具体时间）</p></li><li><p>短日期类型</p></li><li><p><strong>datetime</strong>（包括具体时间）</p></li><li><p>长日期类型</p></li><li><p><strong>clob（Character Large Object）</strong></p></li><li><p>字符大对象，最多可以存储 4G 的字符串</p></li><li><p>例如：存储一篇文章，一个说明</p></li><li><p>超过 255 个字符的都要采用 CLOB 字符大对象来存储</p></li><li><p><strong>blob（Binary Large Object）</strong></p></li><li><p>二进制大对象</p></li><li><p>专门用来存储图片、声音、视频等流媒体数据</p></li><li><p>需要用 IO 流，来往 BLOB 类型的字段上插入数据</p></li></ul><pre>drop table 表名,...; # 当这张表不存在的时候会报错drop table if exists 表名,...; # 如果这张表存在的话，删除(若后面存在已经被删除的表，会有警告，但其他表也会被删除)</pre><div></div><blockquote><p>将一个查询结果当作一张表新建，实现表的快速复制（数据也同样拷贝了）</p></blockquote><pre>insert into 表名(字段名 1，字段名 2，字段名 3，...) value(值1，值2，值3，...)# 字段名和值 要一一对应（数量要对应，数据类型要对应）# 若字段名都省略的话，相当于都写上了# 没有给其他字段指定值的话，默认值为 NULL （除非 创建表的时候指定了默认值）</pre><div></div><pre># 很少用 要求查询结果的结构和表的结构相同insert into 表名1 (select * from 表名0);</pre><div></div><p><strong>将字符串 varchar 类型转换成 date 类型</strong></p><pre>%Y 年(4位)%y 年(2位)%m 月%d 日%H 时(00-23)%h 时(00-12)%i 分%s 秒# insert into t_user(id,name,birth) values(2,'jack',str_to_date('01-12-1990','%d-%m-%Y');str_to_date('字符串日期'，'日期格式')注意：若提供的字符串日期格式为 '%Y-%m-%d' 则不需要 str_to_date 转换 # insert into t_user(id,name,birth) values(2,'jack','1990-10-05');常用于 insert 语句，因为插入的时候需要一个日期类型的数据</pre><p><strong>将日期转换成字符串</strong></p><div></div><ul><li><p>date 是短日期，仅含年月日信息 （默认格式：%Y-%m-%d）</p></li><li><p>datetime 是长日期，含有年月日 时分秒信息（默认格式：%Y-%m-%d %H:%i:%s）</p></li></ul><p>可以获取当前系统的时间，并且获取的时间是 datetime 类型的</p><pre>update 表名 set 字段名1 = 值1，字段名2 = 值2，字段名3 = 值3 ... where 条件;</pre><p>注意：若没有条件限制将会导致所有数据全部更新。</p><div></div><p>注意：若没有条件，会删除整张表的数据。</p><pre>truncate table 表名; # (DDL)# 删除效率比较高，表被一次截断，物理删除# 优点：不支持回滚# 缺点：快速</pre><ul><li><p><strong>create drop alter</strong></p></li><li><p>什么是对表结构的修改？添加一个字段删除一个字段修改一个字段</p></li></ul><p><strong>constraint</strong></p><ul><li><p>在创建表的时候，可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性！</p></li><li><p>作用：保证表中的数据有效！</p></li></ul><ul><li><p>非空约束：not null</p></li><li><p>唯一性约束：unique</p></li><li><p>主键约束：primary key（简称 PK）</p></li><li><p>外键约束：foreign key（简称 FK）</p></li><li><p>检查约束：check （mysql 不支持，oracle 支持）</p></li></ul><p>not null 约束的字段<strong>不能为 NULL</strong> （只有列级约束）</p><div></div><p>unique 约束的字段<strong>不能重复</strong>，但是可以为 NULL</p><pre>ERROR 1062 (23000): Duplicate entry 'lisa' for key 't_user2.name'# 若让 unique 约束的字段重复，则会报该错误# 但若有多个 NULL，不是表示重复，因为 NULL 表示什么都没有</pre><div></div><pre>create table 表名( 字段名1 类型1, 字段名2 类型2, unique(字段名1,字段名2) # 表级约束 需要给多个字段联合起来添加某一个约束的时候使用);</pre><div></div><p><strong>primary key</strong></p><ul><li><p>主键约束：一种约束</p></li><li><p>主键字段：该字段上添加了主键约束，该字段就叫做主键字段</p></li><li><p>主键值：主键字段中的每一个值都叫做主键值</p></li></ul><ul><li><p>主键值是每一行记录的唯一标识（身份证号）</p></li><li><p>任何一张表都应该有主键（否则无效）</p></li></ul><ul><li><p>not null + unique （主键值不能为 NULL，同时也不能重复）</p></li><li><p>可以使用表级约束</p></li></ul><pre>create table 表名( id int, name varchar(255), # primary key(id) 单一主键 primary key(id,name) # 复合主键！！！（开发中不建议使用） # 表级约束 若包含两个字段，则表示两者联合判断主键（这样算一个主键）);</pre><ul><li><p>一张表，<strong>主键约束只能添加 1 个</strong></p></li><li><p>主键值（一般都是数字，或者定长的）</p></li><li><p>建议使用：int bigint char</p></li><li><p>不建议使用：varchar</p></li></ul><ul><li><p><strong>自然主键</strong></p></li><li><p>主键值是一个自然数，和业务没关系</p></li><li><p><strong>业务主键</strong></p></li><li><p>主键值和业务紧密关联</p></li><li><p>实际开发中使用自然主键多，还是业务主键多？</p></li><li><p>自然主键使用比较多，因为主键只要做到不重复就行，不需要有意义，业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候，可能会影响到主键值，所以尽量使用自然主键</p></li></ul><div></div><p><strong>foreign key</strong></p><ul><li><p>外键约束：一种约束（foreign key）</p></li><li><p>外键字段：该字段上添加了外键约束</p></li><li><p>外键值：外键字段当中的每一个值</p></li></ul><ol><li><p>业务背景 设计数据库表，来描述“班级和学生”的信息：</p></li></ol><pre># 方案一：t_studentno(pk) name classno classname-----------------------------------------------------1 lisi 101 上海理工大学计科一班2 zhangsan 101 上海理工大学计科一班3 wangwu 102 上海理工大学计科二班4 zhaoliu 102 上海理工大学计科二班5 qing 103 上海理工大学计科三班缺点：数据冗余，空间浪费# 方案二：t_class（父表）classno(pk) classname-------------------------------------101 上海理工大学计科一班102 上海理工大学计科二班103 上海理工大学计科三班t_student(子表)no(pk) name cno(fk)----------------------------------1 lisi 1012 zhangsan 1013 wangwu 1024 zhaoliu 1025 qing 103</pre><div></div><ul><li><p>解决中文乱码</p></li><li><p>set names gbk; 相当于是告诉 MySQL 服务器软件，我们在当前命令行下输入的内容是GBK编码。当命令窗口关闭或退出 MySQL 服务后，它再输入中文就会出现问题</p></li></ul><ol><li><p>注意</p></li></ol><ul><li><p>外键引用的父表中字段不一定是主键，但要求是 unique 的</p></li><li><p>外键可以为 NULL</p></li></ul><ul><li><p>存储引擎是 MySQL 中特有的一个术语，其他数据库中没有（Oracle中有，但不叫这个名字）</p></li><li><p>实际上存储引擎是一个<strong>表存储/组织数据的方式</strong></p></li><li><p>不同的存储引擎，表存储数据的方式不同</p></li></ul><pre>create table t_product( id int(11) primary key, name varchar(255))engine = InnoDB default charset = utf8; # 指定存储引擎 和 默认字符编码方式（MySQL默认字符集为 utf8）</pre><div></div><ul><li><p>使用三个文件表示每个表</p></li><li><p>格式文件 - 存储表结构的定义（mytable.frm)</p></li><li><p>数据文件 - 存储表行的内容（mytable.MYD)</p></li><li><p>索引文件 - 存储表上的索引（mytable.MYI）</p></li><li><p>索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制</p></li><li><p>对于一张表来说，只要是<strong>主键</strong>，或者<strong>加有 unique 约束</strong>的字段上会自动创建索引</p></li><li><p>MyISAM 存储引擎特点</p></li><li><p><strong>可被转换为压缩、只读表来节省空间</strong></p></li><li><p>但不支持事务机制，<strong>安全性低</strong></p></li></ul><ul><li><p>MySQL <strong>默认</strong>的存储引擎，同时也是一个重量级的存储引擎</p></li><li><p>InnoDB 管理的表具有下列主要特征：</p></li><li><p>每个 InnoDB 表在数据库目录中以 .frm 格式文件表示</p></li><li><p>InnoDB 表空间 <strong>tablespace</strong> 被用于存储表的内容（表空间是一个逻辑名称，表空间用于存储<strong>数据+索引</strong>）</p></li><li><p>提供一组用来记录事务性活动的日志文件</p></li><li><p>用 COMMIT（提交）、SAVAPOINT 及 ROLLBACK（回滚）支持事务处理</p></li><li><p>提供全 ACID 兼容</p></li><li><p>在 MySQL 服务器崩溃后提供自动恢复</p></li><li><p>多版本（MVCC）和行级锁定</p></li><li><p>支持外键及引用的完整性，包括级联删除和更新</p></li><li><p>InnoDB 存储引擎特点</p></li><li><p><strong>支持事务</strong>，以保证数据的<strong>安全</strong></p></li><li><p><strong>效率不是很高</strong>，并且不能压缩，不能转换为只读表，<strong>不能很好的节省空间</strong></p></li></ul><ul><li><p>使用 MEMORY 存储引擎的表，其<strong>数据存储在内存</strong>中，且行的长度固定</p></li></ul><ul><li><p>MEMORY 存储引擎管理的表具有下列特征：</p></li><li><p>在数据库目录内，每个表均以 .frm 格式的文件表示</p></li><li><p>表数据及索引被存储在<strong>内存</strong>中（目的就是查询快！）</p></li><li><p>表级锁机制</p></li><li><p>不能包含 TEXT 或 BLOB 字段</p></li></ul><ul><li><p>MEMORY 存储引擎以前被称为 HEAP 引擎</p></li></ul><ul><li><p>优缺点：</p></li><li><p>优点：<strong>查询效率是最高的</strong>，不需要和硬盘交互</p></li><li><p>缺点：<strong>不安全</strong>，关机之后数据消失。因为数据和索引都是在内存当中</p></li><li><p><strong>内存</strong>中的数据是通过<strong>电容这种电子元件在承载</strong>，电容充放电都是需要时间的，所以可以大胆猜测，即使断电了，内存中的数据全部消失也是需要时间的</p></li></ul><p><strong>transaction</strong></p><ul><li><p><strong>一个事务就是一个完整的业务逻辑。</strong></p></li><li><p>是一个最小的工作单元，不可再分。</p></li></ul><ul><li><p>只有 DML 语句才有事务这一说insertdeleteupdate</p></li><li><p>只要你的操作一旦涉及到数据的增、删、改，那么就一定要考虑安全问题</p></li></ul><ul><li><p>假设所有的业务，只要一条 DML 语句就能完成，还有必要存在事务机制吗？正是因为做某件事的时候，需要多条 DML 语句共同联合起来才能完成，所以需要事务的存在。如果任何一件复杂的事情都能用一条 DML 语句搞定，那么事务则没有存在的价值了</p></li><li><p><strong>事务本质上就是批量的 DML 语句同时成功，或者同时失败！</strong></p></li></ul><ul><li><p>InnoDB 存储引擎：提供一组用来记录事务性活动的日志文件</p></li></ul><pre>事务开启了：insertinsertdeleteinsertupdateupdate事务结束了！</pre><ul><li><p>在事务的执行过程中，每一条 DML 的操作都会记录到 “<strong>事务性活动的日志文件</strong>” 中。</p></li><li><p>在事务的执行过程中，我们可以提交事务，也可以回滚事务</p></li><li><p><strong>提交事务</strong></p></li><li><p><strong>清空</strong>事务性活动的日志文件，<strong>将数据全部彻底持久化到数据表中</strong></p></li><li><p>提交事务标志着事务的结束，并且是一种<strong>全部成功</strong>的结束</p></li><li><p><strong>回滚事务</strong></p></li><li><p>将之前所有的 DDL 操作全部<strong>撤销</strong>，并且<strong>清空</strong>事务性活动的日志文件</p></li><li><p>回滚事务标志着事务的结束，并且是一种<strong>全部失败</strong>的结束</p></li></ul><ul><li><p>提交事务：<strong>commit;</strong> 语句</p></li><li><p>回滚事务：<strong>rollback;</strong> 语句（回滚永远都是只能回滚到上一次的提交点！）</p></li><li><p>注意：在 MySQL 当中默认的事务行为是怎样的？</p></li><li><p>MySQL <strong>默认情况</strong>下是支持自动提交事务的</p></li><li><p>即<strong>每执行一条 DML 语句，则提交一次</strong>（这种自动提交的方式是不符合开发习惯的，因为一个业务通常是需要多条 DML 语句共同执行才能完成的，为了保证数据的安全，必须要求同时成功后再提交，而不能执行一条就提交一条）</p></li><li><p>如何关闭？ <strong>start transaction;</strong></p></li></ul><div></div><ol><li><p><strong>A ：原子性</strong></p></li><li><p>事务是最小的工作单元，不可再分</p></li><li><p><strong>C：一致性</strong></p></li><li><p>在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性</p></li><li><p><strong>I：隔离性</strong></p></li><li><p>A 事务 和 B 事务之间具有一定的隔离</p></li><li><p><strong>D：持久性</strong></p></li><li><p>事务最终结束的一个保障：事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上</p></li></ol><ol><li><p><strong>读未提交</strong> <strong>read uncommitted</strong>（最低的隔离级别）</p></li><li><p>事务 A 可以读取到事务 B未提交的数据</p></li><li><p>存在的问题：<strong>脏读现象</strong>（Dirty Read）—— 读到了脏数据</p></li><li><p>这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步</p></li><li><p><strong>读已提交</strong> <strong>read committed</strong></p></li><li><p>事务 A只能读取到事务 B提交之后的数据</p></li><li><p>解决了什么问题：<strong>解决了脏读的现象</strong></p></li><li><p>存在的问题：<strong>不可重复读取数据</strong></p></li><li><p>在事务开启之后，第一次读到的数据是 3 条，当前事务还没有结束，第二次再去读取，可能读到的数据变成 4 条，3 不等于 4 称为不可重复读取数据</p></li><li><p>这种隔离级别是比较真是的数据，每一次读到的数据是绝对的真实</p></li><li><p>Oracle 数据库默认的隔离级别是：read committed</p></li></ol><ol><li><p><strong>可重复读repeatable read</strong></p></li></ol><ul><li><p>提交之后也读不到，永远读取的都是刚开启事务时的数据</p></li><li><p><strong>可重复读取</strong></p></li><li><p>不管是多久，每一次在事务 A 中读取到的数据都是已知的，即使事务 B 将数据已经修改，并且提交了，事务 A 读取到的数据还是没有发生改变。</p></li><li><p>解决了什么问题：解决了不可重复读取数据</p></li><li><p>存在的问题：可能会出现<strong>幻影读</strong></p></li><li><p>每一次读取到的数据都是幻象，不真实</p></li><li><p>MySQL 中默认的事务隔离级别就是这个！</p></li></ul><ol><li><p><strong>序列化/串行化</strong> <strong>serializable</strong>（最高的隔离级别）</p></li><li><p>效率最低，解决了所有的问题</p></li><li><p>表示事务排队，不能并发！</p></li><li><p>类似于 synchronized 线程同步（事务同步）</p></li><li><p>每一次读取到的数据都是最真是的，并且效率是最低的</p></li></ol><ul><li><p>查看事务隔离级别</p></li></ul><pre>MySQL 8.0+select @@transaction_isolation; # 查看当前会话隔离级别select @@global.transaction_isolation; # 查看系统当前隔离级别MySQL 5.0+select @@tx_isolation; # 查看当前会话隔离级别select @@global.tx_isolation; # 查看系统当前隔离级别</pre><ul><li><p>设置事务隔离级别</p></li></ul><div></div><pre>MySQL 在单个事务中 select 的查询结果可以看到临时的变化例如：start transaction 会取消自动提交例如：start transactioninsert into t_user2 values(1,2,'a');select * from t_user2; # 此时 可以看到查询结果中有了数据（但这个数据只是临时的）， # 但实际上没有在没有 commit 或 rollback 的情况下话，实际表中数据未变化commit; # 此时 实际表才有数据 事务结束（DML 语句全部成功），清空事务性活动的日志文件，并将数据彻底持久化在表中若存在其他事务，使用 select 还需要根据 事务隔离级别 做具体变换</pre><div></div><ul><li><p>索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制</p></li><li><p>一张表的一个字段可以添加一个索引，也可以多个字段联合起来添加索引</p></li><li><p>索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制</p></li><li><p>MySQL 在查询方面主要是两种方式：</p></li><li><p>全表扫描</p></li><li><p>根据索引检索</p></li></ul><ul><li><p>注意：</p></li><li><p>在 MySQL 当中索引是需要排序的，底层是一个 <strong>B-Tree</strong> 数据结构</p></li><li><p>遵循<strong>左小右大</strong>原则存放，采用中序遍历方式遍历取数据</p></li></ul><ul><li><p>在任何数据库当中<strong>主键</strong>上都会自动添加索引对象，另外在 MySQL 当中，一个字段上如果有 <strong>unique</strong> 约束的话，也会自动创建索引对象</p></li><li><p>在任何数据库当中，任何一张表的<strong>任何一条记录</strong>在硬盘存储上都有一个硬盘的<strong>物理存储编号</strong></p></li><li><p>在 MySQL 当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在</p></li><li><p>在 MyISAM 存储引擎中，索引存储在一个 <strong>.MYI 文件</strong>中</p></li><li><p>在 InnoDB 存储引擎中，索引存储在一个逻辑名称叫做 <strong>tablespace</strong> 的表空间当中</p></li><li><p>在 MEMORY 存储引擎中，索引存储在<strong>内存</strong>当中</p></li><li><p>不管索引存储在哪里，索引在 MySQL 当中都是以一个树的形式存在（<strong>自平衡二叉树：B-Tree</strong>）</p></li></ul><pre>select * from t_user where id = 101;# MySQL 发现 id 字段上有索引对象，所以会通过索引对象 idIndex 进行查找# 通过 101 得到物理编号：0x6666，此时马上 SQL 语句转换select * from t_user where 物理编号 = 0x6666;</pre><p><strong>实现原理</strong>：缩小扫描的范围（形成树），避免全表扫描</p><ul><li><p>数据量庞大</p></li><li><p>该字段经常出现在 where 后面，以条件的形式存在，即该字段总是被扫描</p></li><li><p>该字段有很少的 DML 操作（因为 DML 之后，索引需要重新排序）</p></li></ul><ul><li><p>建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。</p></li><li><p>建议通过主键查询 或者 通过 unique 约束的字段进行查询，效率比较高</p></li></ul><div></div><ul><li><p>select * from emp where ename like '%T';<br><br># ename 上即使添加了索引，也不会走索引？<br># 因为模糊匹配当中以 '%' 开头了，就会走模糊匹配的路线去查询（全表查询，需要知道第一个字母）<br># 尽量避免模糊查询的时候以 '%' 开头（优化策略）</p></li><li><p>select * from emp where ename = 'KING' or job = 'MANAGER';<br><br># 如果 or 两边有一个字段没有索引，那么另一个字段上的索引就会失效</p></li><li><p>create index emp_job_sal_index on emp(job,sal);<br><br># 复合索引：两个字段或者更多的字段联合起来添加一个索引<br># 若使用复合索引的时候，没有使用左侧的列查找，索引就会失效</p></li><li><p>create index emp_sal_index on emp(sal);<br><br>select * from emp where sal+1 = 800;<br># 在 where 中，索引列若参加了运算，索引失效</p></li><li><p>select * from emp where lower(ename) = 'smith';<br><br># 若在 where 当中索引列使用了函数，索引会失效</p></li><li><p># 若使用 类型转换 也会失效</p></li></ul><ul><li><p><strong>单一索引</strong></p></li><li><p>一个字段上添加索引</p></li><li><p><strong>复合索引</strong></p></li><li><p>两个字段或者更多的字段上添加索引</p></li><li><p><strong>主键索引</strong></p></li><li><p>主键上添加索引</p></li><li><p><strong>唯一性索引</strong></p></li><li><p>具有 unique 约束的字段上添加索引</p></li><li><p>注意：唯一性比较弱的字段上添加索引用处不大</p></li></ul><ul><li><p>view：站在不同的角度去看待同一份数据</p></li></ul><pre># 创建视图对象 create view 视图名(自己起) as select ...;# 删除视图对象 drop view 视图名(已存在) # 注意： 只有 DQL 语句才能以 view 的形式创建 create view 视图名 as DQL语句;</pre><ul><li><p><strong>方便，简化开发，利于维护</strong></p></li><li><p>可以将一条复杂的 DQL 语句以视图对象的形式新建，可以大大地简化开发，并且利于后期的维护，因为修改的时候也只需要修改一个位置，只需要修改视图对象所映射的 SQL 语句</p></li><li><p>使用视图可以像使用 table 一样，<strong>对视图的 CRUD 也会更新到原表当中</strong></p></li><li><p>视图不是在内存当中，视图对象也是以文件形式存储在硬盘上的，不会消失</p></li></ul><div></div><p>Database Administrator 数据库管理员</p><ul><li><p><strong>导出</strong></p></li><li><p>在 windows 的 dos 命令窗口中：</p></li><li><p><br></p></li><li><p># 导出数据库<br>mysqldump 数据库名>文件路径名 -uroot -p<br># 导出数据库当中的指定表<br>mysqldump 表名>文件的路径名 -uroot -p<br>Enter password:xxxxxxx</p></li><li><p><br></p></li><li><p><strong>导入</strong></p></li><li><p>需要先登录到 MySQL 数据库服务器上</p></li><li><p>然后创建数据库</p></li><li><p>create database 数据库名;<br>使用数据库</p></li><li><p>use 数据库名;<br>初始化数据</p></li><li><p>source 文件路径名;</p></li></ul><p>数据库表的设计依据。教你怎么进行数据库表的设计。</p><blockquote><p>设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费</p></blockquote><ul><li><p>要求任何一张表<strong>必须有主键</strong>，每一个字段<strong>原子性不可再分</strong></p></li><li><p>最核心，最重要的范式，所有表的设计都需要满足。</p></li><li><p>学生编号 学生姓名 联系方式<br>--------------------------------------------<br>1001 张三 ,<br>1002 李四 ,<br>1003 王五 ,<br><br>以上是学生表，不满足第一范式<br># 第一：没有主键<br># 第二：联系方式可以分为邮箱地址和电话号码<br>故更改为：<br>学生编号 学生姓名 邮箱地址 电话号码<br>--------------------------------------------<br>1001 张三 <br>1002 李四 <br>1003 王五 </p></li></ul><ul><li><p>建立在第一范式的基础之上，要求所有非主键字段<strong>完全依赖主键，不要产生部分依赖</strong></p></li><li><p>学生编号 学生姓名 教师编号 教师姓名<br>----------------------------------------<br>1001 张三 001 王老师<br>1002 李四 002 赵老师<br>1003 王五 001 王老师<br>1001 张三 002 赵老师<br><br># 多对多关系<br>这张表描述了学生和老师的关系（1个学生可能有多个老师，1个老师也可能有多个学生）<br><br>不满足第一范式？修改：<br>学生编号+教师编号（PK） 学生姓名 教师姓名<br>----------------------------------------<br>1001 001 张三 王老师<br>1002 002 李四 赵老师<br>1003 001 王五 王老师<br>1001 002 张三 赵老师<br><br>学生编号和教师编号，两个字段联合做主键，满足了第一范式，但是不满足第二范式！<br># '张三’ 依赖 1001，'王老师’ 依赖 001，产生了部分依赖：<br># 缺点：数据冗余，空间浪费。'张三’重复，'王老师’重复，'赵老师’重复<br><br>修改：使用三张表来表示多对多的关系！！！<br>学生表：<br>学生编号（PK） 学生姓名<br>-------------------------<br>1001 张三<br>1002 李四<br>1003 王五<br><br>教师表：<br>教师编号（PK） 教师姓名<br>--------------------------<br>001 王老师<br>002 赵老师<br><br>学生教师关系表<br>id（PK） 学生编号（PK） 教师编号（PK）<br>--------------------------------------<br>1 1001 001<br>2 1002 002<br>3 1003 001<br>4 1001 002<br><br></p></li><li><p>口诀：多对多，三张表，关系表两个外键！！！</p></li></ul><ul><li><p>建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖</p></li><li><p><br></p></li><li><p>学生编号（PK） 学生姓名 班级编号 班级名称<br>---------------------------------------------<br>1001 张三 01 一年一班<br>1002 李四 02 一年二班<br>1003 王五 03 一年三班<br>1004 赵六 03 一年三班<br><br># 一对多关系<br>以上表的设计是描述：班级和学生的关系（一个班级可以有多个学生）<br>1. 满足第一范式：有主键<br>2. 满足第二范式：主键不是复合主键，没有产生部分依赖。主键是单一主键<br>3. 是否满足第三范式？<br>不满足！ '一年一班’ 依赖 '01’，'01’ 依赖 '1001’，产生了传递依赖<br><br>修改：<br>班级表：<br>班级编号（PK） 班级名称<br>-------------------------<br>01 一年一班<br>02 一年二班<br>03 一年三班<br><br>学生表：<br>学生编号（PK） 学生姓名 班级编号（FK）<br>---------------------------------------<br>1001 张三 01<br>1002 李四 02<br>1003 王五 03<br>1004 赵六 03</p></li><li><p><br></p></li><li><p>口诀：一对多，两张表，多的表加外键！</p></li></ul><ul><li><p>一对多</p></li><li><p><br></p></li><li><p>一对多，两张表，多的表加外键！</p></li><li><p><br></p></li><li><p>多对多</p></li><li><p><br></p></li><li><p>多对多，三张表，关系表两个外键！</p></li><li><p><br></p></li><li><p>一对一</p></li><li><p><br></p></li><li><p>在实际的开发中，可能存在一张表字段太多，过于庞大，这时候就需要拆分表</p></li><li><p><br></p></li><li><p>t_user<br>id login_name login_pwd real_name email address ...<br>---------------------------------------------------------------------<br>1 zhangsan 123 张三 <br>2 lisi 567 李四 <br>...<br><br>这样庞大的表建议拆分为两张：<br>t_login 登录信息表<br>id(PK) login_name login_pwd<br>-------------------------------<br>1 zhangsan 123<br>2 lisi 567<br><br>t_user 用户详细信息表<br>id(PK) real_name email address... login_id(FK+unique)<br>-----------------------------------------------------------------------<br>100 张三 1<br>200 李四 2</p></li><li><p><br>口诀：一对一，外键唯一！</p></li></ul><ul><li><p>数据库设计三范式是理论上的</p></li><li><p>实践和理论有的时候是有偏差的</p></li><li><p>最终的目的都是为了<strong>满足客户的需求</strong>，<strong>有的时候会拿冗余换执行速度</strong></p></li><li><p>因为在 sql 中，<strong>表和表之间连接次数越多，效率越低（笛卡尔积）</strong></p></li><li><p>有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql 语句的编写难度也会降低</p></li></ul> </td> </tr> </tbody> 讯享网
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请联系我们，一经查实，本站将立刻删除。
如需转载请保留出处：https://51itzy.com/kjqy/183662.html