update: 2013-08-11, 笔记录了一半不到，还没码完....>
mysql速查
参考： mysql必知必会,网络blog, stackoverflow
注； 基础部分sql参考 《mysql必知必会》, 还不错的一本书，菜鸟入门级，需要的话可以入手
在浏览器中使用查找
寻找一个好的mysql开源gui工具
环境配置ubuntu安装mysql
sudo apt-get install mysql-server mysql-clientnetstat -nltp | grep mysql配置文件 /etc/mysql/my.conf
基本概念数据库基础：
innodb是一个可靠地事务处理引擎，不支持全文本搜索myisam是一个性能极高的引擎，支持全文本搜索，不支持事务处理
数据库-database
保存有组织的数据的容器（通常是一个文件或一组文件）
表-table
某种特定类型数据的结构化清单
模式-schema
关于数据库和表的布局及特性的信息
列-column
表中的一个字段，所有表都是由一个或多个列组成的
数据类型-datatype
所容许的数据的类型。每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据
行-row
表中的一个记录
主键-primary key
一列或一组列，其值能够唯一区分表中的每个行
mysql命令行进入
输入： mysql或者 mysql -u ken mysql -u ken -p -h myserver -p 9999 【给出用户名，主机名，端口】获取帮助: mysql --help
命令格式和说明：
1.命令必须；或\g结束，仅enter不执行明林2.help 或\h获得帮助3.quit或exit退出
可以用gui工具
mysql administratormysql query browser
use创建库:
>create database mysqldata
使用某个库
use db_name
show查看所有数据库
show databases;
列出库中所有表
use db_name;show tables;
列出表的所有列信息
show columns from table_name;ordesc table_name;
显示创建的sql语句
show create database db_name;show create table table_name;
其他
show status 服务器状态信息show grants 显示授权用户show errors/show warnings 显示服务器错误或警告信息
查询select子句顺序
selectfromwheregroup byhavingorder bylimit
select检索单个列
>select col from tb_name;
多个列
>select col1, col2 from tb_name
检索所有列
>select * from tb_name;#除非确认要用到所有列
检索去重
>select distinct col from tb_name
限制结果数
>select col1 from tb_name limit 5;返回不多于五行>select col1 from tb_name limit 5, 5 第一个为开始位置，初始为0.第二个为显示个数等价于limit 5 offset 5
order by按某个字段排序
>select col1 from tb_name order by col1
按多列排序
>select col1, col2, col3 from tb_name order by col1, col2
指定排序方向（升序降序）
>select col1, col2 from tb_name order by col1 desc;【默认asc】注意：如果想在多个列上排序，必须对每个列使用desc注意：order by必须放在limit之前
where过滤
>select col1, col2 from tb_name where col1 = 2.5;
过滤不匹配
>select col1, col2 from tb_name where col1 1000
范围检查
>select col1, col2 from tb_name where col1 between 5 and 10
空值检查
>select col1 from tb_name where col2 is nullnull, 无值，它与字段包含0，空字符串或仅仅包含空格不同
多条件，组合and
>select col1 from tb_name where col1=100 and col2 多条件, 组合or
>select col1 from tb_name where col1=100 or col2 优先级 and 大于 or, 先处理的and,所以应该适当使用括号
select prod_id from products where (prod_price 1;
指定查询范围, in操作符
>select col1 from tb_name where col1 in (1001,1002)
取反，not操作符
>select col1 from tb_name where col1 not in (1001,1002)
操作符
=!=>=between a and b
like通配
>select col1 from tb_name where col1 like ‘jet%’%匹配0个或多个字符
单个字符
>select col1 from tb_name where col1 like ‘_ ton anvil’
数据过滤regexp正则搜索
>select col1from tb_namewhere col1 regexp ‘1000’regexp ‘.000’regexp对列值匹配
进行or匹配
>select col1 from tb_name where col1 regexp ‘1000|2000’
几个之一
select prod_id from products where prod_name regexp '[1|2]000';
匹配范围
select prod_id from products where prod_name regexp '[1-5]000';
匹配特殊字符，\ 进行转义
必须使用\\为前导。 \\->select col1 from tb_name where col1 regexp ‘\\.’
like和 regexp
like整列匹配regexp 列值内匹配
concat拼接字符
>select concat(name, ‘ ----‘, age) from tb_name
去除空白
>select rtrim(name) from tb_nameltrim() trim()
使用列名
>select concat(name, ‘---‘, age) as info from tb_name
算术计算
>select quantity * item_price as total_price from tb_name支持+ - * /
文本函数文本处理函数
left() 串左边字符length() 串长度locate() 找出串的一个子串lower() 转为小写ltrim() 去掉左边空格right() 返回串右边字符rtrim() 去掉串右边空格soundex() 返回字符串soundex值upper() 大写
eg
>select upper(name)from tb_name
日期函数日期和时间处理函数
adddate() 增加一个日期-天或周addtime() 增加一个时间curdate() 返回当前日期curtime() 返回当前时间date() 返回日期时间的日期部分datediff() 计算两个日期差date_add() 高度灵活的日期运算函数date_format() 返回一个格式化的日期或时间串day() 返回一个日期的天数部分dayofweek() 对于一个日期，返回对应的星期几hour()minute()month()now() 当前日期和时间second()time() 当前日期时间的时间部分year()
eg
>select col1 from tb_name where date(order_date) = ‘2005-09-01’
常用日期和时间函数
date()返回日期时间的日期部分day()返回日期的天数部分
数值函数数值处理函数
abs()cos()exp() 指数mod()pi() 返回圆周率rand() 随机数sin()sqrt()tan()
聚集函数avg 平均
>select avg(price) as avg_price from tb_name
count 计数
select count(*) from products; #无论null还是非空，均纳入计数select count(prod_id) from products; #计数有值记录，忽略null值
max 最大
>select max(price) as max_price from tb_name
min 最小
>select min(price) as min_price from tb_name
sum 求和
>select sum(quantity) as total from tb_name#sum函数忽略值为null的行
groupgroup
>select id, count(*) as num_prods from tb_name group by id
注意：
1.group by 可以包含任意数目的列2.group by 中每个列都必须是检索列或有效的表达式（但不能使聚集函数）3.除聚集函数外，select语句中的每个列都必须在group by子句中出现4.如果分组列有null值，null将作为一个分组返回5.group by 子句必须出现在where子句之后, order by 之前
过滤分组
>select cust_id, count(*) as orders from orders group by cust_id having count(*) > 2
where和having区别
where在分组前过滤，having在分组后过滤
子查询1.用于过滤
>select cust_id from orders where order_num in (select order_num from orderitems)
2.作为字段
>select cust_name, cust_state, (select count(*) from orders where orders.cust_id = customers.cust_id) as orders from customers order by cust_name
联结表1.创建联结
>select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id order by vend_name, prod_name;
可进行联结多个表
2.内部联结
>select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id
高级联结表1.自联结
>select prod_id, prod_name from products where vend_id = (select vend_id from products where prod_id = ‘dtntr’)
等价于
>select p1.prod_id, p1.prod_name from products as p1, products as p2 where p1.vend_id = p2.vend_id and p2.prod_id = ‘dtntr’
2.外部联结
>select customers.cust_id, orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id
组合查询1.union
>select vend_id, prod_id, prod_price from products where prod_price 2.放在union后的排序语句
对所有select生效
全文本搜索myisam 支持全文本搜索
innodb不支持全文本搜索
1.启用
>create table productnotes( note_id int not null aut_increment, note_text text null, fulltext(note_text)
2.进行全文本搜索
>select note_text from tb_name where match(note_text) against(‘rabbit’)
3.布尔文本搜索
>select note_text from productontes where match(note_text) against(‘heavy’ in boolean mode)
插入数据1.基本插入
>insert into customers(cust_name, cust_address) values(‘pep’, ‘100 main street’)
2.插入多行
>insert into customers(cust_name, cust_address) values(‘pep’, ‘100 main street’), (‘tim’, ‘200 main street’);
3.插入检索出来的数据
>insert into customers(cust_name, cust_address) select cust_name, custaddress from custnew;
更新1.更新行
>update customers set cust_email = ‘a@fudd.com’ where cust_id = 10005
2.即使发生错误也继续进行而不是退出
>update ignore customers
删除1.删除数据
>delete from customers where cust_id = 10006
表操作1.创建表
>create table customers( cust_id int not null auto_increment, cust_name char(50) not null, vend_city char(50) null, quantity int not null default 1, primary key(cust_id))engine=innodb
2.更新表
加字段
>alter table vendors add vend_phone char(20)
删除某个字段
>alter table tb1 drop column names;
改变列类型
>alter table infos change list list tinyint not null default '0'
加主键
>alter table tb1 add primary key(id)
删除一个字段
>alter table tb1 drop field_name
增加自增长主键
alter table customers change id id not null auto_increment primary key;
增加新字段并设置为主键
alter table tablename add new_field_id int(5) default 0 not null auto_increment add primary key(new_field_id)alter table example add id int not null;alter table example add unique(url)>alter table vendors drop column vend_phone
alter table syntax:http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
3.删除表
>drop table customers2;
4.清空表数据
>delete from mytable;
5.重命名表
>rename table customers2 to customers; alter table 'oldname' rename to 'newname'
视图操作1.创建视图
>create view productcustomers as select cust_name, cust_contact from customers, orders, orderitems where customers.cust_id = orders.cust_id
2.使用视图
>select cust_name, cust_contact from productcustomers where prod_id = ‘tnt2’
存储过程1.创建简单存储过程
>create procedure productpricing() beginselect avg(price) as priceavgfrom products; end;
调用:call productpricing()
2.删除存储过程
>drop procedure productpricing
3.使用参数
>create procedure ordertotal(in onumber int,out ototal decimal(8,2))beginselect sum(item_price*quality)from orderitemswhere order_num = onumberinto ototal;end;
调用:
>call ordertotal(200005, @total;>select @total;
3.检查存储过程
>show create procedure ordertotal;
游标1.创建游标
>create procedure processorders() begindeclare ordernumbers cursorforselect order_number from orders;begin ordernumbers;fetch ordernumbers into o;close ordernumbers; end;declare continue handler for sqlstate ‘02000’ set done=1;repeat fetch ordernumbers into o;end;
触发器1.创建触发器
>create trigger newproduct after insert on products for each row select ‘product added’
2.删除触发器
>drop trigger newproduct;
3.insert触发器
>create trigger neworder after insert on orders for each row select new.order_num
4.delete触发器
>create trigger deleteorder before delete on orders for each row begininsert into archive_orders(order_num, order_date, cust_id)values(old.order_num, old.order_date, old.cust_id); end;
5.update触发器
>create trigger updatevendor before update on vendors for each row set new.vend_state = upper(new.vend_state);
事务基本概念
acida,原子性，食物是一个原子操作单元，其对数据的修改，要么全执行，要么全不执行c.一致性，事务开始和完成的时候，数据必须都保持一致状态（所有相关数据规则和内部数据结构）i.隔离性，保证事务不受外部并发操作影响，即事务处理中间过程状态对外不可见d.持久性，事务完成后，对数据修改时永久性的，及时出现系统故障也能够保持
1.事务
>start transaction delete from ordertotals; select * from ordertotals;>rollback回退>commit提交
2.设立保留点
>savepoint delete1;>rollback to delete1;
导入导出1.导入
用文本形式插入数据
>load data local infile 'd:/mysql.txt' into table mytable;
导入.sql
>use database;>source d:/mysql.sql
从另外一张表往这张表插入
insert into tab1(f1,f2)select a.f1, a.f2from a where a.f1='a'
2.备份
导出要用到mysql的mysqldump工具，基本用法是：
mysqldump [options] database [tables]
备份mysql数据库的命令
mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql
备份mysql数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。
mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql
直接将mysql数据库压缩备份
mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz
备份mysql数据库某个(些)表
mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql
同时备份多个mysql数据库
mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql
仅仅备份数据库结构
mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql
备份服务器上所有数据库
mysqldump –all-databases > allbackupfile.sql
还原
还原mysql数据库的命令
mysql -hhostname -uusername -ppassword databasename 还原压缩的mysql数据库
gunzip 将数据库转移到新服务器
mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -c databasename
将查询结果导入外部文件
select a,b,a+b from test_tableinto outfile '/tmp/result.txt'fields terminated by ',' optionally enclosed by ''lines terminated by '\n'或者mysql -u you -p -e select ... > file_name
性能研究
1.什么情况下无法使用索引？
实时监控查看mysql数据库的当前连接数
命令： show processlist;或者 # mysqladmin -uroot -p密码 processlist
当前状态
命令： show status;或者 # mysqladmin -uroot -p密码 status
原文地址：mysql基础笔记, 感谢原作者分享。