Mysql学习笔记

注意：sql语句顺序

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

一、基本命令

连接mysql ：mysql -u name -p password -h host -P port;

显示数据库：show databases;

显示表：show tables;

显示表结构：describe 表名; show columns from 表名;

显示服务器信息：show status;

显示创建数据库的语句：show create database 数据库名;

显示创建表的语句：show create table 表名;

显示授予的权限：show grants;

显示错误和警告：show errors; show warnings;

二、基本查询

去重DISTINCT 
SELECT DISTINCT vend_id
FROM products;

限制条数
从第一条起，取二条，第一条是从零开始
SELECT *
FROM products LIMIT 1,2;

从第一条起，取两条条，
SELECT *
FROM products LIMIT 2 OFFSET 1;

排序
倒序
SELECT vend_id
FROM products ORDER BY vend_id DESC;

多列排序
SELECT vend_id,prod_price
FROM products 
ORDER BY vend_id DESC,prod_price DESC;

价格中最高：
SELECT vend_id,prod_price
FROM products 
ORDER BY prod_price DESC
LIMIT 1;

条件查询
价格在10到40之间
SELECT vend_id,prod_price
FROM products 
WHERE prod_price BETWEEN 10 AND 40;

邮件地址为空
SELECT *
FROM customers 
where cust_email is NOT NULL;

显示价格大于10元且由1002或1003制造，and 高于 or
SELECT *
FROM products 
where prod_price >= 10 
and (vend_id = 1002 or vend_id = 1003);

in操作符和or相同
SELECT *
FROM products 
where vend_id IN (1002,1003);

Like关键字
%表示多个字符
SELECT *
FROM products 
where prod_name LIKE 'j%';

*一个字符
SELECT *
FROM products 
where prod_name LIKE 'j_';

正则表达式（mysql仅支持正则表达式一小部分）

*匹配任意字符
SELECT *
FROM products 
where prod_name REGEXP '.000';	

BINARY 区分大小写
SELECT *
FROM products 
where prod_name REGEXP BINARY 'j.';

匹配其中一个（相当于or）
SELECT *
FROM products 
where prod_name REGEXP '1000|2000';

匹配组合字符（如：1 ton , 2 ton）
SELECT *
FROM products 
where prod_name REGEXP '[123] ton';
也可以这样写：
SELECT *
FROM products 
where prod_name REGEXP '[1|2|3] ton';

-匹配范围
SELECT *
FROM products 
where prod_name REGEXP '[1-5] ton';

\\特殊字符
SELECT *
FROM products 
where prod_name REGEXP '\\.';

匹配数字，至少出现了4次
SELECT *
FROM products 
where prod_name REGEXP '[0-9]{4,}';

测试正则表达式（匹配成功返回1，否则返回0）
SELECT '12a' REGEXP '[0-9]{3,}';

字段拼接（多数系统使用+,||）但mysql使用concat()函数
SELECT CONCAT('产品名称：',prod_name,' 价格：',prod_price)
FROM products;

别名（可以不加单引号）
SELECT CONCAT('产品名称：',prod_name,' 价格：',prod_price)
as '详情'
FROM products;

按照日期查询
SELECT *
FROM orders
WHERE DATE(order_date) = '2005-09-1';

查询2005年9月份的订单
SELECT *
FROM orders
WHERE Year(order_date) = 2005 
and Month(order_date) = 9;

组合聚集函数
SELECT COUNT(*) as num_items,
	MAX(prod_price) as max_price,
	MIN(prod_price) as min_price,
	AVG(prod_price) as age_price
FROM products;

group by 分组
每个厂的产品个数
SELECT vend_id,count(*) as items_num
FROM products GROUP BY vend_id;

having 对分组后的数组过滤
SELECT vend_id,count(*) as items_num
FROM products GROUP BY vend_id
HAVING items_num >= 3;

inner join内连接
SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
on vendors.vend_id = products.vend_id;

自连接
SELECT p1.prod_id,p2.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'fb';


外连接：
对每个客户所下订单统计（没有包括未下订单的）
SELECT customers.cust_id,COUNT(*)
FROM customers,orders
WHERE customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
也可以这样写：
SELECT customers.cust_id,COUNT(*)
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

包括未下订单的用户：
SELECT customers.cust_id,COUNT(orders.cust_id)
FROM customers LEFT JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
UNION去除多条select语句重复行
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(1002,1003);

三、全文索引

myISAM和InnoDB 前者支持

在创建表时，用FULLTEXT(被所引的字段)，并制定表的存储引擎为MyISAM

CREATE TABLE fulltest(
	uid int NOT NULL,
	uname VARCHAR(20) NOT NULL,
	info text NULL,
	PRIMARY KEY(uid),
	FULLTEXT(info)
)ENGINE=MyISAM;

全文索引（在where语句中）

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('Customer');

全文所引（在select语句中）

SELECT note_text,
	MATCH(note_text) AGAINST('rabbit') AS rank;
FROM productnotes;

四、插入数据

注意：insert语句有可能很耗时（特别是更新很多索引时）用该语句INSERT LOW_PRIORITY  INTO降低对数据库的影响

INSERT INTO student(uid,uname) value(12,'lisi');
INSERT INTO student value(12,'lisi');
INSERT INTO student1 SELECT * from student;

创建表时导入数据
CREATE table tt2(
	uid INT NOT NULL,
	uname VARCHAR(20) NOT NULL
) SELECT * FROM tt

五、更新数据

注意：更新数据时，一定不要忘了where条件

UPDATE tt
SET uid = 123,uname = 'lisi'
WHERE uid = 1;

六、删除数据

DELETE FROM tt
WHERE uid = 123;

七、表操作

1、创建表

CREATE TABLE IF NOT EXISTS test(
	uid INT NOT NULL AUTO_INCREMENT,
	uname VARCHAR(20) NOT NULL,
	sex VARCHAR(2) NOT NULL DEFAULT '男',
	PRIMARY KEY(uid)
) ENGINE=INNODB;

主键（primary key）：非空且唯一

当使用了自动增量，可以使用SELECT LAST_INSERT_ID();查看下一条数据的自动增量值

alter修改表结构
（1）添加主键
alter table 表名 add constraint 主键 （形如：PK_表名） primary key 表名(主键字段); 
（2）添加外键约束：
 
alter table 从表 add constraint 外键（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段); 
 
（3）删除主键约束：（mysql）
 alter table 表名 drop primary key; 
 
（4）删除外键约束：
 alter table 表名 drop foreign key 外键（区分大小写）; 
 
（5）添加列：
 alter table 表名 add column 列名 varchar(30); 
 
（6）删除列：
 alter table 表名 drop column 列名; 
 
（7）修改列名MySQL： 
alter table bbb change nnnnn hh int; 
 
(3)修改列属性：
alter table t_book modify name varchar(22);

删除表
DROP TABLE 表名;

重命名表
RENAME TABLE test TO test1
　　tt TO t1;

八、视图

使用总结：

使用CREATE VIEW 创建视图，

SHOW CREATE VIEW viewname;查看视图

DROP VIEW viewname; 删除视图

更新视图，可以先删除，也可以CREATE OR REPLACE VIEW

创建

CREATE VIEW testview AS
SELECT uid,uname
FROM test1;

使用视图
SELECT *
FROM testview;

九、存储过程

显示存储过程：SHOW CREATE PROCEDURE searchName;

模糊列出：SHOW PROCEDURE STATUS LIKE '%max%';

删除：CREATE PROCEDURE IF EXISTS 名称;

注意：使用mysql命令行，需要更改分隔符，

DELIMITER /.

1、无参数存储过程

CREATE PROCEDURE insetpro()
BEGIN
	INSERT INTO protest value(1);
END

2、求最大，最小，平均值，存储过程

CREATE PROCEDURE pro_max_min_avg(
	OUT max DECIMAL(8,2),
	OUT min DECIMAL(8,2),
	OUT avg DECIMAL(8,2))
BEGIN
	SELECT MAX(prod_price)
	INTO max
	FROM products;
	SELECT MIN(prod_price)
	INTO min
	FROM products;
	SELECT AVG(prod_price)
	INTO avg
	FROM products;
END
调用：
CALL pro_max_min_avg(@max,@min,@avg);
SELECT @max,@min,@avg;

3、输入用户id返回用户姓名

CREATE PROCEDURE searchName(
	IN uid INT,
	OUT uname VARCHAR(20))
BEGIN
	SELECT cust_name
	FROM customers
	WHERE cust_id = uid
	INTO uname;
END

调用：
CALL searchName(10001,@uname);
SELECT @uname;

十、游标

使用流程：

定义游标-》打开游标-》操作游标-》关闭游标

（1）定义游标：

DECLARE cursor_name CURSOR FOR SELECT_statement;

（2）打开游标：

OPEN 打开游标

OPEN cursor_name;

（3）操作游标：

FETCH 获取游标当前指针的记录，并传给指定变量列表,注意变量数必须与MySQL游标返回的字段数一致,要获得多行数据，使用循环语句去执行FETCH

FETCH cursor_name INTO variable list;

（4）关闭游标：　　

CLOSE关闭游标

CLOSE cursor_name ;

注意:MySQL的游标是向前只读的,也就是说，你只能顺序地从开始往后读取结果集，不能从后往前，也不能直接跳到中间的记录

实例1：取一行插入到制定表中

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE a INT;
	DECLARE b VARCHAR(20);
	DECLARE cur1 CURSOR FOR SELECT uid,uname FROM tt2;
 
  OPEN cur1;
 
  FETCH cur1 INTO a, b;
  INSERT INTO tt(uid,uname) value(a,b);

  CLOSE cur1;
END
调用：
CALL curdemo();

实例2：将商品价格低于20的商品存储到ttt表中

CREATE PROCEDURE curtest()
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE pid CHAR(10);
	DECLARE pprice DECIMAL(8,2);
	DECLARE curs CURSOR FOR SELECT prod_id,prod_price from products WHERE prod_price < 20;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

	OPEN curs;
	REPEAT
		FETCH curs INTO pid,pprice;
		INSERT INTO ttt value(pid,pprice);
	UNTIL done END REPEAT;

	CLOSE curs;
	
END
调用：
CALL curtest();

十一、触发器

创建触发器，需要这4条信息：

唯一的触发器名

触发器关联的表

触发器相应的活动（DELETE,INSERT,UPDATE）

触发器何时执行（之前，之后）

注意：mysql中，两个表可以有同名的触发器，只有表支持，临时表也不支持，每个表最多支持6个触发器，不能更新和覆盖，只能删除

1、创建触发器

向产品表插入一条数据，就将事件记录到log表中

CREATE TRIGGER tri1 
AFTER INSERT ON products
FOR EACH ROW
BEGIN
	INSERT INTO log VALUE('INSERT',NOW());
END

实例2：创建两个表，对t1插入的数据，自动插入到t2表

CREATE TABLE t1(
	uid INT
);

CREATE TABLE t2(
	uid INT
);

CREATE TRIGGER t12t2 
AFTER INSERT ON t1
FOR EACH ROW
BEGIN
	INSERT INTO t2 value(NEW.uid);
END
测试：
INSERT INTO t1 VALUE(1);
INSERT INTO t1 VALUE(2);
INSERT INTO t1 VALUE(3);
INSERT INTO t1 VALUE(4);

SELECT * FROM t2;
输出：
1
2
3
4

实例3：t1表删除一条数据，将该条数据保存到t3表

CREATE TABLE t3(
	uid INT
);

CREATE TRIGGER t1delt3
BEFORE DELETE ON t1
FOR EACH ROW
BEGIN
	INSERT INTO t3 VALUE(OLD.uid);
END
测试：
DELETE FROM t1 WHERE uid=3;
SELECT * FROM t3;
输出:
3

实例4：更新t1表内容时，将更改前后数据写到t5表中

CREATE TABLE t5(
	oid INT,
	nid INT
);
CREATE TRIGGER t1updatet5
BEFORE UPDATE ON t1
FOR EACH ROW
BEGIN
	INSERT INTO t5 VALUE(OLD.uid,NEW.uid);
END

t1表测试：
UPDATE t1 SET uid = 3 WHERE uid = 4;
SELECT * FROM t1;
t1表输出：
1
3
10

t5表测试：
SELECT * FROM t5;
t5表输出：
4	3

可以看出，这个表记录上上次修改的值

总结：

OLD.col_name在更新或删除它之前，引用已有行中的1列。

NEW.col_name在更新它之后引用将要插入的新行的1列或已有行的1列。

十二、事务

MyISAM和InnoDB，前者不支持事务

术语：

事务（transaction）：一组sql语句

回退（rollback）：撤销制定sql语句的过程

提交（commit）：将未存储的sql语句写入数据库

保留点（savepoint）相当于一个标记，你可以回退到某个点

1、使用事务

SELECT * FROM orderitems;
START TRANSACTION;
DELETE FROM orderitems;
SELECT * FROM orderitems;
ROLLBACK;
SELECT * FROM orderitems;

可回退的语句：

Insert,delete,update,在事务中可以包含，create ,drop ,但就算事务回退，他们也不会回退

2、保存点

SAVEPOINT P1;

ROLLBACK TO p1;

保存点在事务完成时（rollback,commit）后自动释放，mysql 5以来，可以显示释放RELEASE SAVEPOINT;

3、设置自动提交，针对某个连接

SET autocommit=0;

十二、字符集和编码

字符集：字母和符号的集合

编码：某个字符集陈成员的内部表示

校对：字符如何比较

查看mysql 支持的字符集和校对

SHOW CHARACTER SET;
SHOW COLLATION;
_cs 区分大小写
_ci 不区分大小写

2、查看该系统所用字符集和校对

SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

3、创建表指定

CREATE TABLE mytable(
	col1 INT,
	col2 INT
)DEFAULT CHARACTER SET hebrew
	COLLATE hebrew_general_ci;

4、创建表时，指定某列字符和校对

CREATE TABLE mytable1(
	col1 INT,
	col2 CHAR(20) CHARACTER SET latin1 COLLATE latin1_general_ci
)DEFAULT CHARACTER SET hebrew
	COLLATE hebrew_general_ci;

5、在查询时指定校对，还应用于GROUP BY,HAVING,聚集函数，别名

创建表：

CREATE TABLE t7(
	uname VARCHAR(20)
)DEFAULT CHARACTER SET latin1 
	COLLATE latin1_general_ci;
查询：
SELECT * FROM t7 ORDER BY uname COLLATE latin1_general_cs;

十三、安全

1、用户管理

查看所有用户：SELECT user FROM user;
创建用户：CREATE USER u1 IDENTIFIED BY 'u1';
CREATE USER 'u6'@'localhost' IDENTIFIED by 'u6';
grant all privileges on test.* to 'u3'@'localhost';
FLUSH PRIVILEGES;
不指定主机名的用户只能远程访问
重命名：RENAME USER u1 TO u2;
删除：DROP USER u2;

2、权限

查看：SHOW GRANTS FOR u1;
授权：GRANT SELECT,UPDATE ON exam.* to u1;
撤销：REVOKE SELECT,UPDATE ON exam.* FROM u5;

3、控制权限层次

整个服务器：GRANT ALL,REVOKE ALL;

整个数据库：ON database.*;

特定表：ON database.tablename;

特定列：

特定存储过程

4、更改密码

SET PASSWORD FOR u3@localhost = PASSWORD('u3');
更改当前登录用户：SET PASSWORD = PASSWORD('root');

十四、数据库管理

1、备份

myaqldump

mysqlhotcopy 有些引擎可能不支持

BACKUP TABLE 或 SELECT INTO OUTFILE 用RESTORE TAB;E还原

在备份之前，应刷新数据，FLUSH TABBLES;

整理于2013-8-19 by renpingqing