Mysql----学习笔记

Mysql基础笔记
环境配置
基本概念
mysql命令行
进入
use
show
查询
select
order by
where
like
数据过滤regexp
concat
文本函数
日期函数
数值函数
聚集函数
group
子查询
联结表
高级联结表
组合查询
全文本搜索
插入数据
更新
删除
表操作
视图操作
存储过程
游标
触发器
事务
导入导出
性能研究
实时监控

环境配置: 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.作为字段

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