| 自学MySQL笔记 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
mysqldump 备份 备份单独库下面的所有表 mysqldump -uuname -ppassword db > 地址+备份文件名称 备份某库下的几个表 mysqldump -uuname -ppassword db table1 table2 table3 等> 地址+备份文件名称 备份多个数据库 mysqldump -uuname -ppassword -B db1 db2 >地址+备份文件名称 备份所有数据库 mysqldump -uuname -ppassword -A 导出的是 建表语句和 insert语句 注意：恢复文件的 地址形式 如 e:library.sql 文件名称 后没有分号！！！ 恢复 1 登陆到 mysql命令行的状态下 库级文件的恢复 mysql> source 备份文件地址 表级文件的恢复 mysql> use 库名 mysql> source 备份文件地址 2 不登陆的情况下 库级的 mysql -u用户名 -p密码 <备份文件地址 表级 mysql -u用户名 -p密码 库名<备份文件地址 mysql 模糊查询 like % -->通配任意字符 _ -->通配一个字符 练习： 1 % -->通配任意字符 2 _ -->通配一个字符 笛卡尔积与连接查询 连接查询 （左连接 右连接 内连接） 笛卡尔乘积 集合特性 ： 确定性 无序性 唯一性 一张表可以看做是一个集合，每行数据相当于集合的一个元素 Union时 去掉重复 原理 就是集合元素的唯一性 表中存在完全相同的两行 是因为 表内部 存在 rowid 进行区分
笛卡尔积 如果 a∈A, b∈B A*B = ( a, b);
例如 A=(1,2,3,4,5);B=(11,12); 那么 A*B (1,11), (2,11), (3,11), (4,11), (5,11), (1,12), (2,12), (3,12), (4,12), (5,12);
A有 M 个元素 B 有N 个元素 那么 A*B 有 M*N个元素 同理 表A有 M 行 表B 有N 行 那么 A*B 有 M*N行 例如： ta tb 两表 笛卡尔积 通过分析可以看出 tb 表的 a b c d 每个分别和 ta 的a b c d 组合一遍
左连接 1 连上表 2 连接条件 例如： select good_id,goods.cat_id,goods_name,shop_price from goods left join category on good.cat_id = category.cat_id; 字段名重复那么需要加表前缀，否则会报错； error 1052(23000) column * in field list is ambiguous 最后两行可以看 作是一张表。
左连接语法： select 列1，列2，列N from table 1 left join table 2 on table 1 列 = table 2 列； on 后面的条件只要是条件就可以 可以不是等于 但是常用是等于。将from 后面的当做一个普通表看待。 右连接语法： select 列1，列2，列N from table 1 right join table 2 on table 1 列 = table 2 列；
内连接 inner select 列1，列2，列N from table 1 inner join table 2 on table 1 列 = table 2 列；
左连接 右连接 内连接的区别和联系：
左连接和右连接
是以左边的表为基准。通俗的讲，先将左边的表全部显示出来，然后右边的表id与左边表id相同的记录就“拼接”上去，比如说id为1的记录。如果没有匹配的id，比如说t1中id为2的t2中就没有。那边就以null显示。 右外连接过程正好相反。 内连接
只有一条记录。内连接就是只取出符合过滤条件的记录 也就是t1.id=t2.id 那么符合t1.id=t2.id的记录只有id=1这一条，所以只显示一条。 不像外连接，是将你作为基准的表（左外连接就是左边表为基准，右外连接就是右边表为基准）的所有行都显示出来。 －－－－－－－－－－－－－－－－－［以下为网上的一点资料（粘贴的）］－－－－－－－－－－－－－－－－－－ LEFT JOIN操作用于在任何的 FROM 子句中，组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接。左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录，即使在第二个（右边）表中并没有相符值的记录。 语法： FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2 说明： ① table1, table2参数用于指定要将记录组合的表的名称。 ② field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的名称。 ③ compopr参数指定关系比较运算符："="， "<"， ">"， "<="， ">=" 或 "<>"。 ④ 如果在INNER JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段，将会发生错误 mysql 新的学习模式 1 通过备份 数据库文件 查看备份代码 学习sql 语句！！ 你会发现 备份的语句和你 原本 建表语句是 略有不同的 更加标准 和 详细 2 备份的语句更加规范 并且通过其文本明白备 份原理； 即 通过将建表 建库 inser 语句等 进行备份 并且 可以表示文档注释 等等。 子查询（完整） from 把内层的查询结果作为外层查询比较查询 例：查最新商品（以 goods_id最大为最新） 1 select * from goods where goods_id = 最大的gooods_id; 2 select * from goods where goods_id = (select max(goods_id) from goods); where 表达式则取出哪一行
表达式在哪一行成立 =,!= ,<,><=,>= in ,between and or,and,not
having 表达式 数据在表中，表在硬盘或者内存中以文件的形式存在 where就是针对文件发生作用 查询结果，也可以看做是一张表，去文件一般临时存在于缓冲区 having 就是针对查询结果发挥着作用 group by 分组 一般和统计函数（sum ,avg,min,max）结合使用
order by
默认是升序的 如果想显示声明升序排列 可用asc来声明
可以"字段名" desc 来声明降序排 可以用来查询商品等价格 以及新旧
注：可以按照多条件排序,前面的条件优先！！
order by 列1 [asc/desc], 列2 [asc/desc]; exists 把外层的查询结果代入内层，看内层是否成立
查询商品栏目 select * from category where exists(select * from goods where goods.cat_id = categor.cat_id); cat_id字段相同 需要加上表前缀
limit 在语句的最后 起到限制显示结果条目个数
limit [offset,][N]; offset表示偏移量（跳过几个） N 表示取出的条目个数 如果offset不写 相当于0 #在 mysql 里 表示 注释 列操作（增加 修改 删除列） 列操作（增加 修改 删除列） 创建表单 表单描述 增加列 1 alter table 列名 add 列声明； 列声明 列名称 列类型 【列属性】[列默认值] 增加的列默认在表的的最后一列
2 alter table 列名 add 列声明 after 列； 声明 新增的列在原来哪个列后面 3 alter table 列名 add 列声明 first； 如果放在第一列 那么 在列声明后加first即可
发现问题 auto_increment的问题 插入 id 使用auto_increment时 要求列声明里列必须被定义为 key
修改列 alter table 表名change 被改变的列 列声明 原来： 修改列： 删除列 alter table 表名 drop 列名 ； mysql union 的用法 union 的用法 （一般用于多张表的union）
合并查询结果
将两次或者两次以上的结果合并在一起
要求： 两次查询的列数一致 推荐，查询每一列，相对应的列类型是一样的 多次sql语句 取出 的列名可以不一致，此时以取第一个sql 的列名为准。
如果不同的语句中取出的行 有每个列的值都相同，那么相同的行将被合并成一行（去重复） 如果不想去重复union加上 all
创建表格 create table ta (id char(1) default null, num int not null default 0 ) engine=InnoDB charset=utf8;
insert into ta (id,num) values ('a',10), ('b',20), ('c',30), ('d',40);
create table tb (id char(1) default null, num int not null default 0 ) engine=InnoDB charset=utf8;
insert into tb (id,num) values ('a',50), ('b',60), ('c',70), ('d',80);
1 合并 两张表格 2 合并两张表单 把相同id下的数值sum求和
发现问题 使用聚合 函数时 不许有空格 Sum(num) Sum (num)
将会把 sum 识别为 表 报该数据库下不存在该表单错误 Error 1630(42000)
注意：如果子句里 有 order by | limit 需要 union 两边加小括号 例如 （select good_id,cat_id,goods_name，shop_price from goods _id =where cat_id = 4 order by
shop_price desc ）union (select good_id,cat_id,goods_name，shop_price from goods _id =where
cat_id = 5 order by shop_price desc)；
但是 容易有误 解决方案 1 order by 尽量放在最后使用 即岁最终合并后的结果 进行排序 （select good_id,cat_id,goods_name，shop_price from goods _id =where cat_id = 4 ）union
(select good_id,cat_id,goods_name，shop_price from goods _id =where cat_id = 5 order by
shop_price desc； 2 每个子语句 加上limit mysql 视图视图的定义： 视图 就是由查询结果组成的一张虚拟表 视图的创建语法： create view视图名 as select 语句; 显示表 将会被显示出来 可以看到“表结构” 和视图内容 删除视图语法： drop view 视图名; 视图修改 alter view 视图名 as select 语句；
创建视图的原因： 1、 简化查询 2、 可以进行权限控制 比如：可以把 商品名称 等创建视图，把商品价格等“隐藏”。把表的权限保留，只把视图提供给别人。 3、大的数据分表时，可以用到数据。 比如:表的行数大于200 万行时，开始变慢，可以把一张表的数据拆成4张表（视图）来存放。 4、可以将多张表合并成一张表（视图）。结合 union .
注意： 1、可以完全把视图当作表看待。 2、视图源于表，如果表的内容改变了，那么视图的内容也会跟着发生改变。 3、视图的增删改也将会影响表，但是视图并不是总是可以增删改的。视图和表数据是一一对应时，可以修改。对于视图insert视图必须包含所有表中没有默认值的列。
视图的 algorithm(算法) Algorithm = merge/temptable/undefined merge 当引用视图时，引用视图的语句和定义视图的语句合并。 意味着，视图只是一个规则，当查询视图时， 把查询视图的语句（比如where 等）与创建时的语句where子句合并分析，形成一条select语句。相当于把语句效果叠加。
temptable当引用视图时，根据视图的创建语句，建立一个临时表。 是把 根据创建时候的语句瞬间创建临时表，查询视图的语句，从该临时表查询。
undefined 未定义自动，让系统帮你选择。 mysql 字符集（CHARACTER SET）和校对集（COLLATE） 字符集（CHARACTER SET）和校对集（COLLATE） 字符集： 可以设置 服务器默认字符集 数据库默认字符集 表默认字符集 甚至到列默认字符集 某个级别没有指定，那么继承上级。 mysql4.1及其之后的版本，对字符集的支持分为四个层次: 服务器(server)，数据库(database)，数据表(table)和连接(connection)： character_set_server：这是设置服务器使用的字符集 character_set_client ：这是设置客户端发送查询使用的字符集 character_set_connection ：这是设置服务器需要将收到的查询串转换成的字符集 character_set_results ：这是设置服务器要将结果数据转换到的字符集，转换后才发送给客户端 整个过程： - client(如php程序)发送一个查询； - 服务器收到查询，将查询串从character_set_client 转换到character_set_connection，然后执行转换后的查询； - 服务器将结果数据转换到character_set_results字符集后发送回客户端。 1 、告诉服务器发送的数据是什么类型的。 character_set_client 2、告诉转换器，转换什么编码 character_set_connection 3、查询结果用什么编码 character_set_results 如果三者都是字符集N 那么 可以简写为 set names N;
例如： 客户端字符集和连接字符集
如果connection和 服务器的字符集，比client小时，容易丢失数据。
create table 时的 charset 是 服务器的字符编码
校对集： 可以理解为，排序规则等。一个字符集可能有多种校对集合； ------ 官方文档--------- 1、数据库字符集和校对 CREATE DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] [[DEFAULT] COLLATE collation_name] 例如：
MySQL这样选择数据库字符集和数据库校对规则： · 如果指定了CHARACTER SET X和COLLATE Y，那么采用字符集X和校对规则Y。 · 如果指定了CHARACTER SET X而没有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。 · 否则，采用服务器字符集和服务器校对规则。 2、表字符集和校对每一个表有一个表字符集和一个校对规则，它不能为空。为指定表字符集和校对规则，CREATE TABLE 和ALTER TABLE语句有一个可选的子句： CREATE TABLE tbl_name (column_list) [DEFAULT CHARACTER SET charset_name [COLLATE collation_name]]
ALTER TABLE tbl_name [DEFAULT CHARACTER SET charset_name] [COLLATE collation_name] MySQL按照下面的方式选择表字符集和 校对规则： · 如果指定了CHARACTER SET X和COLLATE Y，那么采用CHARACTER SET X和COLLATE Y。 · 如果指定了CHARACTER SET X而没有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。 · 否则，采用服务器字符集和服务器校对规则。 如果在列定义中没有指定列字符集和校对规则，则默认使用表字符集和校对规则。表字符集和校对规则是MySQL的扩展;在标准SQL中没有。 3、列字符集和校对每一个“字符”列（即，CHAR、VARCHAR或TEXT类型的列）有一个列字符集和一个列 校对规则，它不能为空。列定义语法有一个可选子句来指定列字符集和校对规则： col_name {CHAR | VARCHAR | TEXT} (col_length) [CHARACTER SET charset_name [COLLATE collation_name]] 例如： CREATE TABLE Table1 ( column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci ); MySQL按照下面的方式选择列字符集和校对规则： · 如果指定了CHARACTER SET X和COLLATE Y，那么采用CHARACTER SET X和COLLATE Y。 · 如果指定了CHARACTER SET X而没有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。 · 否则，采用表字符集和服务器校对规则。 CHARACTER SET和COLLATE子句是标准的SQL。 示例1：表和列定义
在这里我们有一个列使用latin1字符集和latin1_german1_ci校对规则。是显式的定义，因此简单明了。需要注意的是，在一个latin2表中存储一个latin1列不会存在问题。 示例2：表和列定义
这次我们有一个列使用latin1字符集和一个默认校对规则。尽管它显得自然，默认校对规则却不是表级。相反，因为latin1的默认校对规则总是latin1_swedish_ci，列c1有一个校对规则latin1_swedish_ci（而不是latin1_danish_ci）。 示例3：表和列定义
我们有一个列使用一个默认字符集和一个默认校对规则。在这种情况下，MySQL查找表级别来确定列字符集和 校对规则。因此，列c1的字符集是latin1，它的 校对规则是latin1_danish_ci。 示例4：数据库、表和列定义
我们创建了一个没有指定字符集和校对规则的列。我们也没有指定表级字符集和校对规则。在这种情况下，MySQL查找数据库级的相关设置。（数据库的设置变为表的设置，其后变为列的设置。）因此，列c1的字符集为是latin2，它的 校对规则是latin2_czech_ci。 在SQL语句中使用COLLATE· 使用COLLATE子句，能够为一个比较覆盖任何默认校对规则。COLLATE可以用于多种SQL语句中。下面是一些例子： · 使用ORDER BY： · SELECT k · FROM t1 · ORDER BY k COLLATE latin1_german2_ci; · 使用AS： · SELECT k COLLATE latin1_german2_ci AS k1 · FROM t1 · ORDER BY k1; · 使用GROUP BY： · SELECT k · FROM t1 · GROUP BY k COLLATE latin1_german2_ci; · 使用聚合函数： · SELECT MAX(k COLLATE latin1_german2_ci) · FROM t1; · 使用DISTINCT： · SELECT DISTINCT k COLLATE latin1_german2_ci · FROM t1; · 使用WHERE： · SELECT * · FROM t1 · WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k; · SELECT * · FROM t1 · WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci; · 使用HAVING： · SELECT k · FROM t1 · GROUP BY k · HAVING k = _latin1 'Müller' COLLATE latin1_german2_ci; 子句优先 COLLATE子句有较高的优先级（高于||），因此下面两个表达式是等价的： x || y COLLATE z x || (y COLLATE z) 触发器（trigger） 作用：
可以监视 增删改操作 并触发 增删改操作 创建触发器 (delimiter $) create trigger 触发器名称 after/before insert/update/delete on 表名 for each row begin sql 语句—一个或者多个语句，范围在 insert/update/delete内; end ($)
在此需要重新设置结束符号 delimiter $ 创建第一个简单触发器： 效果 g 表 羊自动减少 3只
发现问题：
设置结尾符号语句 不可带 分号 否则设置不成功 解决方案
如何在触发器中引用行的值 对于insert 而言 ，新增的行用new来表示。 行中的每一列的值，用 new.列名来表示。 对于delete而言，删去的行用 old来表示。 行中的每一列的值，用old.列名来表示。 对于update而言，更新前的行用 old来表示。old.列名表示更新前的引用。 更新后的行，用new.列名来表示更新后的引用。 触发器的删除 drop trigger 触发器名称 触发器的显示 清空表单 truncate 表名
购买下单实例 1、创建满足条件的新的触发器 2、执行 insert（下第一笔订单 购买 4 号商品，数量为2） 3、查看o订单 4、g商品表中的 对应id号得商品减少 完整购买案例 1、增加一个订单，库存相应减少 2、删除一个订单，库存相应增加 3、修改下单时，商品表数量相应发生变化 触发器里 after 和 before的区别 after 是先完成数据的操作，再出发时间，不能对前面的增删改作出影响。 如果用的是 before,可以对数据进行审查。是先于触发再完成增删改，可以审查、判断、即将发生的增删改操作。 before应用 创建触发器时即可对插入数据进行影响 create trigger buy before insert on o for each row begin if new.much >5 then set new.much=5; end if; update g set num = num -new.much where id = new.gid; end$ 索引 注意: 1、 不要过度索引 2、 索引条件列（where后面最频繁的条件列） 3、 尽量索引散列值，过于集中的值不要索引。 索引类型 1、普通索引（index）：仅仅是加快查询速度 2、唯一索引(unique index)：行上的值不能重复 3、主键索引(primary key)：主键不能索引 主键索引必定是唯一的，唯一索引不一定是主键， 一张表上只能一个主键，可以有一个或者多个唯一索引。 4、 全文索引(fulltext index)：在mysql 默认情况下对于中文作用不大。 查看 一张表上的索引 show index from 表名 (/G可以横着显示) 索引不知道名称默认以索引的列名作索引 建立索引 alter table 表名 add index/unique/fulltext [索引名](列名) [索引名]可以不写 ，不写默认与列名相同。 alter table 表名 add primary key （列名） 创建索引示例： 为 tel列 创建普通索引 为email列 添加唯一索引 为intro列添加全文索引 为id列添加主键 删除索引 alter table drop index 索引名 示例： 删除email（唯一）索引 删除主键索引 全文索引使用 查看匹配度 select 列名, match (索引) against (‘ 索引词’) from表名; 新发现 as 支持 汉字 全文停止字：全文索引，不针对非常频繁的词，做索引。针对汉语意义不大，因为因为英文单词可以依赖空格等标点来判断单词界限，进而对单词进行索引，而中文mysql无法识别单词界限。以下表列出了默认的全文停止字 ：
注意！！换服务器导入数据： 大数据量时，先把索引去掉，导入后，统一加上索引。 索引加快查询速度，降低增删改的速度。 额外： show create table 表名 可以查看 创建表的语句 发现问题 使用全文索引时加上引擎 存储引擎 最常见的是:Mysam和 innoDB 数据库的engine显示 MyISAM ，批量插入速度快，不支持事务，锁表 InnoDB 批量插入相对较慢，支持事务，锁行 -----------官方文档---------begin MySQL插件式存储引擎的体系结构 下述存储引擎是最常用的： · MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。 · InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。 · BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。 · Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。 · Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。 · Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。 · Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。 · Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。 · Other：其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件），Blackhole（用于临时禁止对数据库的应用程序输入），以及Example引擎（可为快速创建定制的插件式存储引擎提供帮助）。 ----------------------------------end 事务 含义： 通俗来将就是，一组操作，要么同时执行，要么同时不执行。 选择存储引擎 InnoDB 事务的使用： 开启事务：start transaction; 执行sql操作 (结束事务)commit(提交)/rollback(回滚/ 撤销) 模拟银行转账示例： commit示例： 说明： (结束事务)commit(提交)/rollback(回滚/ 撤销) 之后，事务结束，如果需要再使用，那么需要重新开始事务； rollback示例： 事务原理： 建库建表的时候 要选用I nnoDB 说明: -----------------摘自官方文档--------begin 不能回滚的语句 有些语句不能被回滚。通常，这些语句包括数据定义语言（DDL）语句，比如创建或取消数据库的语句，和创建、取消或更改表或存储的子程序的语句。 您在设计事务时，不应包含这类语句。如果您在事务的前部中发布了一个不能被回滚的语句，则后部的其它语句会发生错误，在这些情况下，通过发布ROLLBACK语句不能 回滚事务的全部效果。 会造成隐式提交的语句 以下语句（以及同义词）均隐含地结束一个事务，似乎是在执行本语句前，您已经进行了一个COMMIT。 · ALTER FUNCTION, ALTER PROCEDURE, ALTER TABLE, BEGIN, CREATE DATABASE, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, DROP DATABASE, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP TABLE, LOAD MASTER DATA, LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START TRANSACTION, TRUNCATE TABLE, UNLOCK TABLES. · 当当前所有的表均被锁定时，UNLOCK TABLES可以提交事务。 · CREATE TABLE, CREATE DATABASE DROP DATABASE, TRUNCATE TABLE, ALTER FUNCTION, ALTER PROCEDURE, CREATE FUNCTION, CREATE PROCEDURE, DROP FUNCTION和DROP PROCEDURE等语句会导致一个隐含提交。 · InnoDB中的CREATE TABLE语句被作为一个单一事务进行处理。这意味着，来自用户的ROLLBACK不会撤销用户在事务处理过程中创建的CREATE TABLE语句。 事务不能被嵌套。这是隐含COMMIT的结果。当您发布一个START TRANSACTION语句或其同义词时，该COMMIT被执行，用于任何当前事务。
mysqldump 备份 备份单独库下面的所有表 mysqldump -uuname -ppassword db > 地址+备份文件名称 备份某库下的几个表 mysqldump -uuname -ppassword db table1 table2 table3 等> 地址+备份文件名称 备份多个数据库 mysqldump -uuname -ppassword -B db1 db2 >地址+备份文件名称 备份所有数据库 mysqldump -uuname -ppassword -A 导出的是 建表语句和 insert语句 注意：恢复文件的 地址形式 如 e:library.sql 文件名称 后没有分号！！！ 恢复 1 登陆到 mysql命令行的状态下 库级文件的恢复 mysql> source 备份文件地址 表级文件的恢复 mysql> use 库名 mysql> source 备份文件地址 2 不登陆的情况下 库级的 mysql -u用户名 -p密码 <备份文件地址 表级 mysql -u用户名 -p密码 库名<备份文件地址 mysql 模糊查询 like % -->通配任意字符 _ -->通配一个字符 练习： 1 % -->通配任意字符 2 _ -->通配一个字符 笛卡尔积与连接查询 连接查询 （左连接 右连接 内连接） 笛卡尔乘积 集合特性 ： 确定性 无序性 唯一性 一张表可以看做是一个集合，每行数据相当于集合的一个元素 Union时 去掉重复 原理 就是集合元素的唯一性 表中存在完全相同的两行 是因为 表内部 存在 rowid 进行区分
笛卡尔积 如果 a∈A, b∈B A*B = ( a, b);
例如 A=(1,2,3,4,5);B=(11,12); 那么 A*B (1,11), (2,11), (3,11), (4,11), (5,11), (1,12), (2,12), (3,12), (4,12), (5,12);
A有 M 个元素 B 有N 个元素 那么 A*B 有 M*N个元素 同理 表A有 M 行 表B 有N 行 那么 A*B 有 M*N行 例如： ta tb 两表 笛卡尔积 通过分析可以看出 tb 表的 a b c d 每个分别和 ta 的a b c d 组合一遍
左连接 1 连上表 2 连接条件 例如： select good_id,goods.cat_id,goods_name,shop_price from goods left join category on good.cat_id = category.cat_id; 字段名重复那么需要加表前缀，否则会报错； error 1052(23000) column * in field list is ambiguous 最后两行可以看 作是一张表。
左连接语法： select 列1，列2，列N from table 1 left join table 2 on table 1 列 = table 2 列； on 后面的条件只要是条件就可以 可以不是等于 但是常用是等于。将from 后面的当做一个普通表看待。 右连接语法： select 列1，列2，列N from table 1 right join table 2 on table 1 列 = table 2 列；
内连接 inner select 列1，列2，列N from table 1 inner join table 2 on table 1 列 = table 2 列；
左连接 右连接 内连接的区别和联系：
左连接和右连接
是以左边的表为基准。通俗的讲，先将左边的表全部显示出来，然后右边的表id与左边表id相同的记录就“拼接”上去，比如说id为1的记录。如果没有匹配的id，比如说t1中id为2的t2中就没有。那边就以null显示。 右外连接过程正好相反。 内连接
只有一条记录。内连接就是只取出符合过滤条件的记录 也就是t1.id=t2.id 那么符合t1.id=t2.id的记录只有id=1这一条，所以只显示一条。 不像外连接，是将你作为基准的表（左外连接就是左边表为基准，右外连接就是右边表为基准）的所有行都显示出来。 －－－－－－－－－－－－－－－－－［以下为网上的一点资料（粘贴的）］－－－－－－－－－－－－－－－－－－ LEFT JOIN操作用于在任何的 FROM 子句中，组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接。左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录，即使在第二个（右边）表中并没有相符值的记录。 语法： FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2 说明： ① table1, table2参数用于指定要将记录组合的表的名称。 ② field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的名称。 ③ compopr参数指定关系比较运算符："="， "<"， ">"， "<="， ">=" 或 "<>"。 ④ 如果在INNER JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段，将会发生错误 mysql 新的学习模式 1 通过备份 数据库文件 查看备份代码 学习sql 语句！！ 你会发现 备份的语句和你 原本 建表语句是 略有不同的 更加标准 和 详细 2 备份的语句更加规范 并且通过其文本明白备 份原理； 即 通过将建表 建库 inser 语句等 进行备份 并且 可以表示文档注释 等等。 子查询（完整） from 把内层的查询结果作为外层查询比较查询 例：查最新商品（以 goods_id最大为最新） 1 select * from goods where goods_id = 最大的gooods_id; 2 select * from goods where goods_id = (select max(goods_id) from goods); where 表达式则取出哪一行
表达式在哪一行成立 =,!= ,<,><=,>= in ,between and or,and,not
having 表达式 数据在表中，表在硬盘或者内存中以文件的形式存在 where就是针对文件发生作用 查询结果，也可以看做是一张表，去文件一般临时存在于缓冲区 having 就是针对查询结果发挥着作用 group by 分组 一般和统计函数（sum ,avg,min,max）结合使用
order by
默认是升序的 如果想显示声明升序排列 可用asc来声明
可以"字段名" desc 来声明降序排 可以用来查询商品等价格 以及新旧
注：可以按照多条件排序,前面的条件优先！！
order by 列1 [asc/desc], 列2 [asc/desc]; exists 把外层的查询结果代入内层，看内层是否成立
查询商品栏目 select * from category where exists(select * from goods where goods.cat_id = categor.cat_id); cat_id字段相同 需要加上表前缀
limit 在语句的最后 起到限制显示结果条目个数
limit [offset,][N]; offset表示偏移量（跳过几个） N 表示取出的条目个数 如果offset不写 相当于0 #在 mysql 里 表示 注释 列操作（增加 修改 删除列） 列操作（增加 修改 删除列） 创建表单 表单描述 增加列 1 alter table 列名 add 列声明； 列声明 列名称 列类型 【列属性】[列默认值] 增加的列默认在表的的最后一列
2 alter table 列名 add 列声明 after 列； 声明 新增的列在原来哪个列后面 3 alter table 列名 add 列声明 first； 如果放在第一列 那么 在列声明后加first即可
发现问题 auto_increment的问题 插入 id 使用auto_increment时 要求列声明里列必须被定义为 key
修改列 alter table 表名change 被改变的列 列声明 原来： 修改列： 删除列 alter table 表名 drop 列名 ； mysql union 的用法 union 的用法 （一般用于多张表的union）
合并查询结果
将两次或者两次以上的结果合并在一起
要求： 两次查询的列数一致 推荐，查询每一列，相对应的列类型是一样的 多次sql语句 取出 的列名可以不一致，此时以取第一个sql 的列名为准。
如果不同的语句中取出的行 有每个列的值都相同，那么相同的行将被合并成一行（去重复） 如果不想去重复union加上 all
创建表格 create table ta (id char(1) default null, num int not null default 0 ) engine=InnoDB charset=utf8;
insert into ta (id,num) values ('a',10), ('b',20), ('c',30), ('d',40);
create table tb (id char(1) default null, num int not null default 0 ) engine=InnoDB charset=utf8;
insert into tb (id,num) values ('a',50), ('b',60), ('c',70), ('d',80);
1 合并 两张表格 2 合并两张表单 把相同id下的数值sum求和
发现问题 使用聚合 函数时 不许有空格 Sum(num) Sum (num)
将会把 sum 识别为 表 报该数据库下不存在该表单错误 Error 1630(42000)
注意：如果子句里 有 order by | limit 需要 union 两边加小括号 例如 （select good_id,cat_id,goods_name，shop_price from goods _id =where cat_id = 4 order by
shop_price desc ）union (select good_id,cat_id,goods_name，shop_price from goods _id =where
cat_id = 5 order by shop_price desc)；
但是 容易有误 解决方案 1 order by 尽量放在最后使用 即岁最终合并后的结果 进行排序 （select good_id,cat_id,goods_name，shop_price from goods _id =where cat_id = 4 ）union
(select good_id,cat_id,goods_name，shop_price from goods _id =where cat_id = 5 order by
shop_price desc； 2 每个子语句 加上limit mysql 视图视图的定义： 视图 就是由查询结果组成的一张虚拟表 视图的创建语法： create view视图名 as select 语句; 显示表 将会被显示出来 可以看到“表结构” 和视图内容 删除视图语法： drop view 视图名; 视图修改 alter view 视图名 as select 语句；
创建视图的原因： 1、 简化查询 2、 可以进行权限控制 比如：可以把 商品名称 等创建视图，把商品价格等“隐藏”。把表的权限保留，只把视图提供给别人。 3、大的数据分表时，可以用到数据。 比如:表的行数大于200 万行时，开始变慢，可以把一张表的数据拆成4张表（视图）来存放。 4、可以将多张表合并成一张表（视图）。结合 union .
注意： 1、可以完全把视图当作表看待。 2、视图源于表，如果表的内容改变了，那么视图的内容也会跟着发生改变。 3、视图的增删改也将会影响表，但是视图并不是总是可以增删改的。视图和表数据是一一对应时，可以修改。对于视图insert视图必须包含所有表中没有默认值的列。
视图的 algorithm(算法) Algorithm = merge/temptable/undefined merge 当引用视图时，引用视图的语句和定义视图的语句合并。 意味着，视图只是一个规则，当查询视图时， 把查询视图的语句（比如where 等）与创建时的语句where子句合并分析，形成一条select语句。相当于把语句效果叠加。
temptable当引用视图时，根据视图的创建语句，建立一个临时表。 是把 根据创建时候的语句瞬间创建临时表，查询视图的语句，从该临时表查询。
undefined 未定义自动，让系统帮你选择。 mysql 字符集（CHARACTER SET）和校对集（COLLATE） 字符集（CHARACTER SET）和校对集（COLLATE） 字符集： 可以设置 服务器默认字符集 数据库默认字符集 表默认字符集 甚至到列默认字符集 某个级别没有指定，那么继承上级。 mysql4.1及其之后的版本，对字符集的支持分为四个层次: 服务器(server)，数据库(database)，数据表(table)和连接(connection)： character_set_server：这是设置服务器使用的字符集 character_set_client ：这是设置客户端发送查询使用的字符集 character_set_connection ：这是设置服务器需要将收到的查询串转换成的字符集 character_set_results ：这是设置服务器要将结果数据转换到的字符集，转换后才发送给客户端 整个过程： - client(如php程序)发送一个查询； - 服务器收到查询，将查询串从character_set_client 转换到character_set_connection，然后执行转换后的查询； - 服务器将结果数据转换到character_set_results字符集后发送回客户端。 1 、告诉服务器发送的数据是什么类型的。 character_set_client 2、告诉转换器，转换什么编码 character_set_connection 3、查询结果用什么编码 character_set_results 如果三者都是字符集N 那么 可以简写为 set names N;
例如： 客户端字符集和连接字符集
如果connection和 服务器的字符集，比client小时，容易丢失数据。
create table 时的 charset 是 服务器的字符编码
校对集： 可以理解为，排序规则等。一个字符集可能有多种校对集合； ------ 官方文档--------- 1、数据库字符集和校对 CREATE DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] [[DEFAULT] COLLATE collation_name] 例如：
MySQL这样选择数据库字符集和数据库校对规则： · 如果指定了CHARACTER SET X和COLLATE Y，那么采用字符集X和校对规则Y。 · 如果指定了CHARACTER SET X而没有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。 · 否则，采用服务器字符集和服务器校对规则。 2、表字符集和校对每一个表有一个表字符集和一个校对规则，它不能为空。为指定表字符集和校对规则，CREATE TABLE 和ALTER TABLE语句有一个可选的子句： CREATE TABLE tbl_name (column_list) [DEFAULT CHARACTER SET charset_name [COLLATE collation_name]]
ALTER TABLE tbl_name [DEFAULT CHARACTER SET charset_name] [COLLATE collation_name] MySQL按照下面的方式选择表字符集和 校对规则： · 如果指定了CHARACTER SET X和COLLATE Y，那么采用CHARACTER SET X和COLLATE Y。 · 如果指定了CHARACTER SET X而没有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。 · 否则，采用服务器字符集和服务器校对规则。 如果在列定义中没有指定列字符集和校对规则，则默认使用表字符集和校对规则。表字符集和校对规则是MySQL的扩展;在标准SQL中没有。 3、列字符集和校对每一个“字符”列（即，CHAR、VARCHAR或TEXT类型的列）有一个列字符集和一个列 校对规则，它不能为空。列定义语法有一个可选子句来指定列字符集和校对规则： col_name {CHAR | VARCHAR | TEXT} (col_length) [CHARACTER SET charset_name [COLLATE collation_name]] 例如： CREATE TABLE Table1 ( column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci ); MySQL按照下面的方式选择列字符集和校对规则： · 如果指定了CHARACTER SET X和COLLATE Y，那么采用CHARACTER SET X和COLLATE Y。 · 如果指定了CHARACTER SET X而没有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。 · 否则，采用表字符集和服务器校对规则。 CHARACTER SET和COLLATE子句是标准的SQL。 示例1：表和列定义
在这里我们有一个列使用latin1字符集和latin1_german1_ci校对规则。是显式的定义，因此简单明了。需要注意的是，在一个latin2表中存储一个latin1列不会存在问题。 示例2：表和列定义
这次我们有一个列使用latin1字符集和一个默认校对规则。尽管它显得自然，默认校对规则却不是表级。相反，因为latin1的默认校对规则总是latin1_swedish_ci，列c1有一个校对规则latin1_swedish_ci（而不是latin1_danish_ci）。 示例3：表和列定义
我们有一个列使用一个默认字符集和一个默认校对规则。在这种情况下，MySQL查找表级别来确定列字符集和 校对规则。因此，列c1的字符集是latin1，它的 校对规则是latin1_danish_ci。 示例4：数据库、表和列定义
我们创建了一个没有指定字符集和校对规则的列。我们也没有指定表级字符集和校对规则。在这种情况下，MySQL查找数据库级的相关设置。（数据库的设置变为表的设置，其后变为列的设置。）因此，列c1的字符集为是latin2，它的 校对规则是latin2_czech_ci。 在SQL语句中使用COLLATE· 使用COLLATE子句，能够为一个比较覆盖任何默认校对规则。COLLATE可以用于多种SQL语句中。下面是一些例子： · 使用ORDER BY： · SELECT k · FROM t1 · ORDER BY k COLLATE latin1_german2_ci; · 使用AS： · SELECT k COLLATE latin1_german2_ci AS k1 · FROM t1 · ORDER BY k1; · 使用GROUP BY： · SELECT k · FROM t1 · GROUP BY k COLLATE latin1_german2_ci; · 使用聚合函数： · SELECT MAX(k COLLATE latin1_german2_ci) · FROM t1; · 使用DISTINCT： · SELECT DISTINCT k COLLATE latin1_german2_ci · FROM t1; · 使用WHERE： · SELECT * · FROM t1 · WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k; · SELECT * · FROM t1 · WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci; · 使用HAVING： · SELECT k · FROM t1 · GROUP BY k · HAVING k = _latin1 'Müller' COLLATE latin1_german2_ci; 子句优先 COLLATE子句有较高的优先级（高于||），因此下面两个表达式是等价的： x || y COLLATE z x || (y COLLATE z) 触发器（trigger） 作用：
可以监视 增删改操作 并触发 增删改操作 创建触发器 (delimiter $) create trigger 触发器名称 after/before insert/update/delete on 表名 for each row begin sql 语句—一个或者多个语句，范围在 insert/update/delete内; end ($)
在此需要重新设置结束符号 delimiter $ 创建第一个简单触发器： 效果 g 表 羊自动减少 3只
发现问题：
设置结尾符号语句 不可带 分号 否则设置不成功 解决方案
如何在触发器中引用行的值 对于insert 而言 ，新增的行用new来表示。 行中的每一列的值，用 new.列名来表示。 对于delete而言，删去的行用 old来表示。 行中的每一列的值，用old.列名来表示。 对于update而言，更新前的行用 old来表示。old.列名表示更新前的引用。 更新后的行，用new.列名来表示更新后的引用。 触发器的删除 drop trigger 触发器名称 触发器的显示 清空表单 truncate 表名
购买下单实例 1、创建满足条件的新的触发器 2、执行 insert（下第一笔订单 购买 4 号商品，数量为2） 3、查看o订单 4、g商品表中的 对应id号得商品减少 完整购买案例 1、增加一个订单，库存相应减少 2、删除一个订单，库存相应增加 3、修改下单时，商品表数量相应发生变化 触发器里 after 和 before的区别 after 是先完成数据的操作，再出发时间，不能对前面的增删改作出影响。 如果用的是 before,可以对数据进行审查。是先于触发再完成增删改，可以审查、判断、即将发生的增删改操作。 before应用 创建触发器时即可对插入数据进行影响 create trigger buy before insert on o for each row begin if new.much >5 then set new.much=5; end if; update g set num = num -new.much where id = new.gid; end$ 索引 注意: 1、 不要过度索引 2、 索引条件列（where后面最频繁的条件列） 3、 尽量索引散列值，过于集中的值不要索引。 索引类型 1、普通索引（index）：仅仅是加快查询速度 2、唯一索引(unique index)：行上的值不能重复 3、主键索引(primary key)：主键不能索引 主键索引必定是唯一的，唯一索引不一定是主键， 一张表上只能一个主键，可以有一个或者多个唯一索引。 4、 全文索引(fulltext index)：在mysql 默认情况下对于中文作用不大。 查看 一张表上的索引 show index from 表名 (/G可以横着显示) 索引不知道名称默认以索引的列名作索引 建立索引 alter table 表名 add index/unique/fulltext [索引名](列名) [索引名]可以不写 ，不写默认与列名相同。 alter table 表名 add primary key （列名） 创建索引示例： 为 tel列 创建普通索引 为email列 添加唯一索引 为intro列添加全文索引 为id列添加主键 删除索引 alter table drop index 索引名 示例： 删除email（唯一）索引 删除主键索引 全文索引使用 查看匹配度 select 列名, match (索引) against (‘ 索引词’) from表名; 新发现 as 支持 汉字 全文停止字：全文索引，不针对非常频繁的词，做索引。针对汉语意义不大，因为因为英文单词可以依赖空格等标点来判断单词界限，进而对单词进行索引，而中文mysql无法识别单词界限。以下表列出了默认的全文停止字 ：
注意！！换服务器导入数据： 大数据量时，先把索引去掉，导入后，统一加上索引。 索引加快查询速度，降低增删改的速度。 额外： show create table 表名 可以查看 创建表的语句 发现问题 使用全文索引时加上引擎 存储引擎 最常见的是:Mysam和 innoDB 数据库的engine显示 MyISAM ，批量插入速度快，不支持事务，锁表 InnoDB 批量插入相对较慢，支持事务，锁行 -----------官方文档---------begin MySQL插件式存储引擎的体系结构 下述存储引擎是最常用的： · MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。 · InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。 · BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。 · Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。 · Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。 · Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。 · Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。 · Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。 · Other：其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件），Blackhole（用于临时禁止对数据库的应用程序输入），以及Example引擎（可为快速创建定制的插件式存储引擎提供帮助）。 ----------------------------------end 事务 含义： 通俗来将就是，一组操作，要么同时执行，要么同时不执行。 选择存储引擎 InnoDB 事务的使用： 开启事务：start transaction; 执行sql操作 (结束事务)commit(提交)/rollback(回滚/ 撤销) 模拟银行转账示例： commit示例： 说明： (结束事务)commit(提交)/rollback(回滚/ 撤销) 之后，事务结束，如果需要再使用，那么需要重新开始事务； rollback示例： 事务原理： 建库建表的时候 要选用I nnoDB 说明: -----------------摘自官方文档--------begin 不能回滚的语句 有些语句不能被回滚。通常，这些语句包括数据定义语言（DDL）语句，比如创建或取消数据库的语句，和创建、取消或更改表或存储的子程序的语句。 您在设计事务时，不应包含这类语句。如果您在事务的前部中发布了一个不能被回滚的语句，则后部的其它语句会发生错误，在这些情况下，通过发布ROLLBACK语句不能 回滚事务的全部效果。 会造成隐式提交的语句 以下语句（以及同义词）均隐含地结束一个事务，似乎是在执行本语句前，您已经进行了一个COMMIT。 · ALTER FUNCTION, ALTER PROCEDURE, ALTER TABLE, BEGIN, CREATE DATABASE, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, DROP DATABASE, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP TABLE, LOAD MASTER DATA, LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START TRANSACTION, TRUNCATE TABLE, UNLOCK TABLES. · 当当前所有的表均被锁定时，UNLOCK TABLES可以提交事务。 · CREATE TABLE, CREATE DATABASE DROP DATABASE, TRUNCATE TABLE, ALTER FUNCTION, ALTER PROCEDURE, CREATE FUNCTION, CREATE PROCEDURE, DROP FUNCTION和DROP PROCEDURE等语句会导致一个隐含提交。 · InnoDB中的CREATE TABLE语句被作为一个单一事务进行处理。这意味着，来自用户的ROLLBACK不会撤销用户在事务处理过程中创建的CREATE TABLE语句。 事务不能被嵌套。这是隐含COMMIT的结果。当您发布一个START TRANSACTION语句或其同义词时，该COMMIT被执行，用于任何当前事务。
|
