MYSQL基础笔记

update: 2013-08-11, 笔记录了一半不到，还没码完….>< update: 2013-11-23, DONEmysql速查

参考： 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.作为字段

>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