mysql随笔

Mysql学习笔记 1、操作数据库 use dataBaseName  //使用数据库 show databases   //显示所有数据库 show tables  //显示该数据库中的所有数据表 show columns from tableName == describe tableName  //显示表tableName下所有字段信息 show status  //显示广泛的服务器状态信息 show create database or show create table  //显示创建特定数据库或表的Mysql语句 show grants //显示所有用户或特定用户的安全权限 show errors or show warnings  //显示服务器的错误或者警告信息 insert into table(column_a,column_b...) values(...) update table set column_a = xx,column_b = xx ... where xx = xx delete from table where xx = xx(省略where时会将该表下所搜数据删除) drop table tableName  //删除表 rename table tableNameA to tableNameB  //重命名表 mysql中的引擎类型： InnoDB 一个可靠的事务处理引擎，不支持全文本搜索 MyISAM 一个性能极高的引擎，支持全文本搜索 MEMORY 在功能上等同于MyISAM，但由于数据存储在内存而不是硬盘中，速度很快 ps：外键不能跨引擎 2、检索数据： 从products表中检索三列数据 select prod_id,prod_name,prod_price from products 检索所有列，*为通配符 select * from products 从products表中检索不同的vend_id和vend_name数据(检索出的数据不仅仅vend_id不同，vend_name也不同) select distinct vend_id,vend_name from products 使用limit限制结果 select prod_name from products limit 5   //limit 5 指示mysql返回5行数据 select prod_name from products limit 5,5  //返回从行5开始的5行 注意检索出来的第一行为行0而不是行1，因此limit 5,5 将检索第6行开始的5行数据，第1个数为开始位置，第2个数为检索的行数 limit 4 offset 3 //表示从行3开始的4行数据 3、排序检索数据 按照prod_name排序 select prod_name from products order by prod_name 按照prod_id,prod_name排序 select prod_id,prod_price,prod_name from products order by prod_id,prod_name 指定反向排序prod_id，正向排序prod_name select prod_id,prod_price,prod_name from products order by prod_id desc,prod_name desc只应用于其前面的列 4、过滤数据 简单where子句 select prod_id,prod_name from products where prod_id = 1000 where子句的操作符 =   等于 <> 不等于 != 不等于 < 小于 > 大于 <= 小于等于 >= 大于等于 between A and B 在指定A和B之间 空值检查 select prod_name from products where prod_name is (not) null ps：在匹配过滤或者不匹配过滤中，null值均不被返回 5、数据过滤 and操作符(同时匹配) select prod_id,prod_name,prod_price from products where prod_id = 1000 and prod_price < 2000 or操作符(匹配任一) select prod_id,prod_name,prod_price from products where prod_id = 1000 or prod_id = 2000 计算次序 select prod_id,prod_name,prod_price from products where prod_id = 1000 and prod_price < 2000 or prod_name= 'Jack' in操作符，返回vend_id为1000,1001,1003的所有products select prod_id,prod_name,prod_price from products where vend_id in (1000,1001,1002) not操作符 select prod_id,prod_name,prod_price from products where vend_id not in (1000,1001,1002) 6、过滤数据——通配符(通配符是区分大小写的) %通配符(匹配任意字符) 以下语句不匹配'java'，如果要匹配则需使用'%ava'或者'%ava%' select prod_id,prod_name from products where prod_name like 'ava%' _通配符(匹配任意单个字符) 以下语句不匹配'.5 ton anvil'，要匹配的话需使用'% ton anvil' select prod_id,prod_name from products where prod_name like '_ ton anvil' 通配符使用注意： 1、不用过度使用通配符 2、不要将通配符用在搜索模式的最开始，这是最慢的 3、注意通配符的位置 7、mysql正则表达式 基本字符匹配 检索prod_name包含文本1000的所有行 select prod_id,prod_name from products where prod_name regexp '1000' order by prod_name like与regexp区别： 对于如下两条语句，第一条返回null，第二条返回一条数据，这是因为like匹配整个列，如果被匹配的文本在列值中出现则like返回空，相应的行也不会返回。regexp在列值内进行匹配，被匹配的文本在列值中出现则相应行被直接返回。 select prod_id,prod_name from products where prod_name like '1000' order by prod_name select prod_id,prod_name from products where prod_name regexp '1000' order by prod_name regexp中的其它匹配符(. - | []等) .表示匹配任意一个字符，如下匹配1000、2000等 select prod_name from products where prod_name regexp '.000'  |表示匹配其中之一 select prod_name from products where prod_name regexp 'jack|lucy' []表示匹配几个字符之一 select prod_name from products where prod_name regexp '[123] ton' 注意以下语句匹配'1'或'2'或'3 ton' select prod_name from products where prod_name regexp '1|2|3 ton' [0-9]、[a-z]表示匹配范围内的任一字符 select prod_name from products where prod_name regexp '[1-5] ton' \\转义来匹配特殊字符 \\.表示查找. \\f换页 \\n换行 \\r回车 \\t制表 \\v纵向制表 \\\表示查找\ 匹配字符类(表示一个集合，如果需要取得其中一个还要在外层加[]，即[[:digit:]]表示匹配任意一个数字) [:alnum:] 任意字母或数字 [:alpha:] 任意字符 [:blank:] 空格和制表 [:cntrl:] ASCII控制字符 [:digit:] 任意数字 [:graph:] 与[:print:]相同，但不包含空格 [:lower:] 任意小写字母 [:print:] 任意可打印字符 [:punct:] 既不在[:alnum:]又不在[:cntrl:]中的字符 [:space:] 包括空格在内的任意空白字符 [:upper:] 任意大写字母 [:xdigit:] 任意十六进制数字 重复元字符 * 表示匹配任意多个 + 表示匹配至少一个 ? 表示匹配0个或1个 {n} 表示匹配n个 {n,} 表示匹配大于等于n个 {n,m} 表示匹配n到m个 例子 '\\([0-9] sticks?\\)'\\(匹配(，[0-9]匹配任意数字，?表示可有可无，\\)匹配) 定位符 ^ 表示文本开始 $ 表示文本结束 [[:<:]] 词的开始 [[:>:]] 词的结尾 例子 '^[0-9\\.]'   [0-9\\.]表示以0-9任意数字或者.字符，合在一起表示以.或者数字开头的匹配 8、创建计算字段 拼接字段 Concat(column_a,'(',column_b,')') 使用as创建别名 select Concat(prod_name,'(',prod_country,')') as prod_title from products order by prod_name 9、数据处理函数 文本处理函数 Left() 返回串左边的字符 Length() 返回串的长度 Locate() 返回串的一个子串 Lower() 将串转换为小写 LTrim() 去掉串左边的空格 Right() 返回串右边的字符 RTrim() 去掉串右边的空格 Soundex() 返回串的Soundex值，即发音 SubString() 返回子串的字符 Upper() 将串转换为大写 日期和时间处理函数 AddDate() AddTime() CurDate() CurTime() Date() DateDiff() 计算两个日期之差 Date_Add() Date_Format() Day() DayOfWeek() Hour() 返回一个时间的小时部分 Minute() Month() 返回一个日期的月份部分 Now() 返回当前日期和时间 Second() Time() Year() 例子 选择订单日期为2005-09-01的订单 select cust_id,order_num from orders where Date(order_date) = '2005-09-01' 选择2005年9月下的订单 select cust_id,order_num from orders where Year(order_date) = 2005 and Month(order_date) = 9 数值处理函数 Abs() Cos() Exp() Mod() 取余 Pi() Rand() 返回一个随机数 Sin() Sqrt() Tan() 10、汇总数据(对于null值的列，直接忽略) AVG() 返回某列的平均值 COUNT() 返回某列行数 MAX() MIN() SUM() 聚集不同的行使用distinct select AVG(distinct prod_price) as avg_price from products where vend_id = 1003 11、分组数据 数据分组：group by select vend_id,count(*) as num_prods from products group by vend_id 这里使用count(*)函数，对于每个vend_id而不是整个表计算num_prods一次，从而可以统计相同vend_id的product数 过滤分组 select cust_id,count(*) as orders from orders group by cust_id having count(*) >= 2 列出具有2个以上、价格为10以上的产品的供应商： select cust_id,count(*) as num_prods from products where prod_price >= 10 group by vend_id having  count(*) >= 2 order by vend_id desc ps：一般在使用group by子句时，应该也给出order by子句，不要依赖group by排序数据，group by仅用于分组。 select子句的顺序 select from where group by having order by limit 12、使用子查询 1、查出prod_id为'TNT2'的订单号 select order_num from orderitems where prod_id = 'TNT2' 2、查询具有订单2005和2007的客户ID select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2') 3、查询订购物品为'TNT2'的所有客户ID select cust_id,cust_name,cust_contact from customer where cust_id in (select cust_id from orders whereorder_num in (select order_num from orderitems where prod_id = 'TNT2')) 以上实现列出订购物品为'TNT2'的所有客户 ps：子查询一般结合in操作符一起使用 13、联结表 创建联结(交叉联结) select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id = products.vend_idorder by vend_name,prod_name ps：如果这里没有where联结条件的话，返回的行数为2张表的笛卡尔积，即检索出来的行数为表1行数*表2行数 select vend_name,prod_name,prod_price from vendors,products order by vend_name,prod_name 内部联结(同以上的等值联结) select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id 联结多张表 select vend_name,prod_name,prod_price,quantity from orderitems,products,vendors where products.vend_id =vendors.vend_id and orderitems.prod_id = vendors.vend_id and order_num = 20005 14、创建高级联结 左外联结 select customer.cust_id,orders.order_num from customers left outer join orders on customers.cust_id =orders. cust_id  右外联结 select customer.cust_id,orders.order_num from customers right outer join orders on customers.cust_id =orders. cust_id  15、使用union进行组合查询 union直接连接多个select语句，union与where的区别：union总是完成与多个where条件相同的工作，相比几个单独where语句查询出来的总和，union自动去除了结果相同的行，所以union查询结果数永远小于等于where，如果想返回所有匹配行可以使用union all 16、全文本搜索(fulltext，一般不区分大小写，除非使用binary) 为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的不断变化重新索引。 启用全文本搜索支持 一般在创建表时启用全文本搜索，如下： create table productnotes{ note_id int not null auto_increment, prod_id char(50) not null, note_date datetime not null, note_text text null, primary key(note_id), fulltext(note_text) }ENGINNE=MyISAM; 进行全文本搜索 select note_text from productnotes where match(note_text) against('rabbit'); 例子: select note_text,match(note_text) against('rabbit') as rank from productnotes; ps：此处计算出来的rank为等级值，大于等于0始终，不存在则为0，越靠前等级值越大 启用查询拓展(可以直接查询出来相关联的所有行数据，不一定要包含'rabbit') select note_text from productnotes where match(note_text) against('rabbit' with query expansion)  布尔文本搜索 全文本布尔操作符 + 包含，词必须存在 - 排除，词必须不存在 > 包含，且增加等级值 < 包含，且减少等级值 () 把词组成子表达式 ~ 取消一个词的排序值 * 词尾通配符 "" 定义并匹配一个短语 例子: 搜索匹配包含词rabbit和bait的行 select note_text from productnotes where match(note_text) against('+rabbit +bait' in boolean mode) 搜索匹配包含词rabbit或bait的行 select note_text from productnotes where match(note_text) against('rabbit bait' in boolean mode) 匹配rabbit和bait，增加前者等级，降低后者等级 select note_text from productnotes where match(note_text) against('>rabbit <bait' in boolean mode) 匹配safe和combination，降低后者等级 select note_text from productnotes where match(note_text) against('+safe +(<combination)' inboolean mode) 全文本搜索注意事项： 1、短词始终被忽略(短词是指长度为3或者3以下的词) 2、如果表中的行数少于3行，则全文本搜索不返回任何结果 3、忽略词中的单引号，例如：don't 为dont 17、插入检索出的数据 insert select语句 insert into customers(cust_id,cust_contact,cust_email,cust_mail,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) select cust_id,cust_contact,cust_email,cust_mail,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country from custnew; 为了简单起见，该语句在insert和select语句中使用了相同的列明。事实上，不用指定列名匹配，因为select出来的数据是通过位置匹配再insert的。 18、视图 视图是虚拟的表。 select cust_name,cust_contact from customers,orders,orderitems where customers.cust_id = orders.cust_id andorderitems.order_num = orders.order_num and prod_id = 'TNT2'    将以上整个语句封装成为一个虚拟表productcustomers，以后查询检索数据直接从这里可以轻松检索。 select cust_name,cust_contact from productcustomers where prod_id = 'TNT2' 视图作用： 1、重用sql语句 2、简化复杂sql操作 3、使用表的组成部分而不是整张表 4、保护数据 5、更改数据格式和表示 视图规则和限制： 1、命名唯一 2、对于可以创建的视图数目没有限制 3、创建视图必须要有足够的访问权限 4、视图可以嵌套 5、order by可以用在视图中，但是如果从视图检索的sql语句中也包含order by语句，则视图中的order by将被覆盖 6、视图不能索引 7、视图可以和表一起使用 使用视图： 1、使用create view viewName创建视图 2、使用drop view viewName删除视图 3、更新视图时可以先drop再create，也可以直接create or replace view 检查视图： show create view viewName; 视图事例： 1、利用视图简化复杂联结，如： create view productcustomers as select cust_name,cust_contact from customers,orders,orderitems where customers.cust_id = orders.cust_id and orderitems.order_num = orders.order_num and prod_id = 'TNT2' select cust_name,cust_contact from productcustomers where prod_id = 'TNT2'  2、利用视图重新格式化检索出来的数据，如： create view vendorlocations as select Concat(RTrim(vend_name),'(',RTrim(vend_country),')') asvend_title from vendors order by vend_name select * from vendorlocations 3、用视图过滤不想要的数据 create view customermaillist as select cust_id,cust_name,cust_email from customers where cust_email is not null 4、使用视图与计算字段 create view orderitemsexpanded as select order_num,prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems 19、存储过程 存储过程简单地讲就是为以后的使用而保存的一条或多条sql语句的集合，可以理解为批处理语句，但其作用不仅仅与批处理 使用存储过程要比单独的sql语句要快。 执行存储过程 call productpricing(@pricelow,@pricehigh,@priceaverage); 创建存储过程 create procedure productpricing() begin select Avg(prod_price) as priceaverage from products; end; 删除存储过程 drop procedure productpricing; 存储过程参数类型 IN OUT INOUT总计3种参数类型，相应参数的数据类型可以是mysql支持的任意类型。 例子： create procedure ordertotal( in onumber int, in taxable boolean, out ototal decimal(8,2) )comment 'Obtain order total,optionally adding tax' begin declare total decimal(8,2); declare taxrate int default 6; select Sum(item_price*quantity) from orderitems where order_num = onumber into total; if taxable then select total+(total/100*taxrate) into total; end if; select total into ototal; end; 检查存储过程 show create procedure ordertotal; 20、使用游标 有时，在检索出来的数据中前进或后退一行或多行，这就是使用游标的原因。 创建游标 create procedure processorders() begin declare ordernumbers cursor for select order_num from orders; end; 打开或关闭游标 如果不手动关闭，则mysql会在存储过程结束时自动关闭。 open ordernumbers; close ordernumbers; 使用游标数据 create procedure processorders() begin declare ordernumbers cursor for select order_num from orders; open ordernumbers; fetch ordernumbers into o; close ordernumbers; end; 例子： create procedure processorders() begin declare done boolean default 0; declare o int; declare ordernumbers cursor for select order_num from orders; declare continue handler for sqlstate '02000' set done = 1; open ordernumbers; repeat -- Get order number fetch ordernumbers into o; until done end repeat; close ordernumbers; end; 完整事例： create procedure processorders() begin declare done boolean default 0; declare o int; declare t decimal(8,2); declare ordernumbers cursor for select order_num from orders; declare continue handler for sqlstate '02000' set done = 1; create table if not exists ordertotals(order_num int,total decimal(8,2)); open ordernumbers; repeat fetch ordernumbers into o; call ordertotal(o,1,t); insert into ordertotals(order_num,total) values(o,t); until done end repeat; close ordernumbers; end; 21、使用触发器 在某个表发生更改时自动处理即是触发器。 创建触发器时需要给出的4条信息，分别是： 1、触发器名 2、触发器关联的表 3、触发器应该响应的活动(delete、insert、update) 4、触发器何时执行 创建触发器 create trigger newproduct after insert on products for each row select 'Product added'; ps：触发器仅仅表支持，视图不支持 删除触发器 drop trigger newproduct; insert触发器 1、在insert触发器内部可以引用一个名为NEW的虚拟表，访问被插入的行 2、对于auto_increment列，NEW在insert执行之前包含0，在insert执行之后包含新的自动生成的值 create trigger newproduct after insert on orders for each row select NEW.order_num; delete触发器 1、在delete触发器内部，可以引用一个名为OLD的虚拟表，访问被删除的行 2、OLD中的值全部是只读的，不能被更新 create trigger deleteorder before delete on orders for each row begin insert into archive_orders(order_num,order_date,cust_id) values(OLD.order_num,OLD.order_name); end; update触发器 1、在update触发器代码内，可以引用一个名为OLD的虚拟表访问update执行前的值，引用一个名为NEW的表访问update后的值 2、在before update触发器中，NEW的值可能也被更新 3、OLD中的值全部是只读的，不能被更新 create trigger updatevendor before update on vendors for each row set NEW.vend_state = Upper(NEW.vend_state); ps：mysql不支持从触发器中调用存储过程 22、mysql事务管理 事务管理可以用来维护数据库的完整性，它可以保证mysql操作要么全部执行成功，要么全部不执行。 事务(transaction)：指一组sql语句 回退(rollback)：指撤销指定sql语句的过程 提交(commit):只将未存储的sql语句结果写入数据库表 保留点(savepoint):指事务中设置的临时占位符，可以对它发布回退 select * from ordertotals; start transaction; delete from ordertotals; select * from ordertotals; rollback; select * from ordertotals; 使用commit select * from ordertotals; start transaction; delete from ordertotals; commit; 使用savepoint savepoint delete1; rollback to delete1;