update: 2013-08-11, 笔记录了一半不到，还没码完....update: 2013-11-23, DONE mysql速查 参考： Mysql必知必会,网络blog, stackoverflow 注； 基础部分sql参考 《mysql必知必会》, 还不错的一本书，菜鸟入门级，需要的话可以入手 在浏览器中使用查找 寻找
update: 2013-08-11, 笔记录了一半不到，还没码完....>
mysql速查
参考： Mysql必知必会,网络blog, stackoverflow
注； 基础部分sql参考 《mysql必知必会》, 还不错的一本书，菜鸟入门级，需要的话可以入手
在浏览器中使用查找
寻找一个好的mysql开源gui工具
环境配置
ubuntu安装mysql
sudo apt-get install mysql-server mysql-client netstat -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 Administrator MySQL Query Browser
use
创建库:
>CREATE DATABASE MYSQLDATA
使用某个库
use db_name
show
查看所有数据库
show databases;
列出库中所有表
use db_name; show tables;
列出表的所有列信息
show columns from table_name; or desc table_name;
显示创建的sql语句
show create database db_name; show create table table_name;
其他
show status 服务器状态信息 show grants 显示授权用户 show errors/show warnings 显示服务器错误或警告信息
查询
SELECT子句顺序
SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT
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 NULL NULL, 无值，它与字段包含0，空字符串或仅仅包含空格不同
多条件，组合and
>SELECT col1 FROM tb_name WHERE col1=100 AND col2 <= 10
多条件, 组合or
>SELECT col1 FROM tb_name WHERE col1=100 OR col2 <= 10
优先级 and 大于 or, 先处理的and,所以应该适当使用括号
select prod_id from products where (prod_price < 2.5 or vend_id = 1000) and 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 col1 FROM tb_name WHERE 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_name Ltrim() 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的行
group
group
>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.作为字段
战刀API淘客自动采集系统
下载
战刀API淘客自动采集系统是一个以php+MySQL进行开放的淘宝客网站程序。程序说明：1.老Y文章系统更改为php战刀API淘宝客系统。2.本程序就是基于"淘宝客基础API"开发出来的产品 。3.现在淘宝客API 重新开放申请 商品全自动更新。网站功能：1.现在淘宝客API 重新开放申请，本程序就是基于"淘宝客基础API"开发出来的产品，海量商品全自动更新
>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 <=5 UNION SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001,1002) UNION自动去除重复行 UNION ALL 保留
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() BEGIN SELECT Avg(price) AS priceavg FROM products; END;
调用:CALL productpricing()
2.删除存储过程
>DROP PROCEDURE productpricing
3.使用参数
>CREATE PROCEDURE ordertotal( IN onumber INT, OUT ototal DECIMAL(8,2) ) BEGIN SELECT Sum(item_price*quality) FROM orderitems WHERE order_num = onumber INTO ototal; END;
调用:
>CALL ordertotal(200005, @total; >SELECT @total;
3.检查存储过程
>SHOW CREATE PROCEDURE ordertotal;
游标
1.创建游标
>CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT 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 BEGIN INSERT 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);
事务
基本概念
ACID A,原子性，食物是一个原子操作单元，其对数据的修改，要么全执行，要么全不执行 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.f2 FROM 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 < backupfile.sql mysql -hhostname -ppassword databasename tablename < backuptablefile.sql
还原压缩的MySQL数据库
gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename
将数据库转移到新服务器
mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename
将查询结果导入外部文件
SELECT a,b,a+b FROM test_table INTO 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基础笔记, 感谢原作者分享。
