MySQL基础知识和常用命令总结

检索数据
排序检索数据
过滤数据
使用通配符过滤
使用正则表达式进行搜索
创建计算字段
使用数据处理函数
汇总数据
分组数据
使用子查询
联结表
创建高级联结
组合查询
全文本搜索
插入数据
更新和删除数据
创建和操纵表
使用视图
使用存储过程
使用游标
使用触发器
管理事务处理
全球化和本地化
安全管理
数据库维护
改善性能.

模式可以用来描述数据库中特定的表以及整个数据库,和其中表的关系
行（row）表中的一个记录

必须先使用use命令打开数据库，才能读取其中的数据。

mysql> show columns from user;//DESCRIBE
help show;
> select  distinct prod_id prod_name,prod_price from products;//返回不同的行！DISTINCT，限制返回结果。LIMIT
SELECT prod_name,FROM products LIMIT 3,3(start,rows)

help 命令

如果使用DISTINCT关键字，它必须直接放在所有列的前面,完全限定符：表名.列名
子句（clause） SQL语句由子句构成，有些子句是必需的，而有的是可选的。

不同行distinct，排序order by XXX DESC

SELECT DISTINCT FOROM prod_name FROM products ORDER BY prod_name LIMIT 2,4;

结构体排序。先，，然后。。指定多列，用逗号隔开,DESC关键字只应用到直接位于其前面的列名,多列降序排列，则每个列都需要指定DESC关键字。

子句次序 SELECT xx FROM XXX WHERE xxxx ORDER BY xx DESC LIMIT 1

单引号用来限定字符串。如果将值与进行比较，则。用来与数值列进行比较的值不用引号。范围运算符between

SELECT prod_name,prod_price FROM prodects WHERE prod_price BETWEEN 4 AND 5;
SELECT prod_id ,prod_prices FROM produts WHERE  prod_id=1002 AND prod_price<=10;
SELECT prod_id ,prod_prices FROM produts WHERE  prod_id NOT IN(1002,1004)AND prod_price<=10;
SELECT prod_name FROM prodects WHERE prod_name LIKE jar%;
SELECT prod_name FROM prodects WHERE prod_name LIKE j_r%;
//WHERE prod_price is null

MySQL允许给出多个WHERE子句。这些子句可以两种方式使用：以AND子句的方式或OR子句的方式使用,AND在计算次序中优先级更高，使用括号改变优先级，OR的泛化-- -- IN
IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句
WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件。

为在搜索子句中使用通配符，必须使用，%表示任何字符出现任意次数,下划线只匹配单个字符,通配符不要放在开始。正则表达式

SELECT prod_name FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name ;//替代了LIKE

LIKE匹配整个列，而REGEXP在列值内进行匹配。使用^(开始）和$(结束）定位符（anchor）即可，为了匹配特殊字符，必须用\ \为前导。

区分通配符和正则表达式(匹配范围、匹配几个字符之一，或者)

可以使用预定义的字符集，称为字符类（character class）
存储在表中的数据都不是应用程序所需要的。需要转换-- -计算字段，例如，拼接。没有列名，需要用AS来指定。计算字段的另一常见用途是对检索出的数据进行算术计算.

SELECT Concat(RTrim(vend_name),' (',vend_country,')') AS vend_title
FROM vendors ORDER BY vend_name;
SELECT 2*4 XXX;//算术运算符

使用处理函数，字符串，数字，日期，DBMS信息

日期和时间函数在MySQL语言中具有重要的作用。

，日期必须为
格式yyyy-mm-dd。

datatime与date类型比较，截取相同部分比较Date(xxx)

汇总数据aggregate function

针对对象，列：avg,max(单列、忽略null),min ,sum,count()返回某列的行数

AVG()只用于单个列

COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目

COUNT(*):对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
COUNT(column):对特定列中具有值的行进行计数，忽略NULL值。

SELECT SUM(item*quantity) AS total_price
FROM orderitems
WHERE order_num=23000;
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id=2003;

SELECT COUNT(*) AS num_items,
		MIN(prod_price) AS price_min,
		MAX(prod_price) AS price_max,
		AVG(prod_price) AS price_avg,
FROM products
这些函数是高效设计的

数据分组与过滤,而

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id.//按照商家分组

这导致对每个vend_id，而不是整个表计算num_prods一次

。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给.

where-- >groub by -- >order by desc limit

WHERE过滤指定的是行而不是分组，分组过滤用having

。唯一的差别是WHERE过滤行，而HAVING过滤分组。HAVING支持所有WHERE操作符。语义相同，但针对对象不同。这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

SELECT cust_id,COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*)>2;
//它保留COUNT(*) >=2（两个以上的订单）的那些分组

同时使用where和group by

SELECT vend_id,COUNT(*) AS num_prods
FROM products
WHERE prod_price >=10;//对所的行过滤，不管你是哪个组！体会，where是分组前的操作
GROUP BY vend_id
HAVING COUNT(*) >=3;

GROUP BY 和ORDER by的区别

千万不要仅依赖GROUP BY排序数据。SELE

SELECT order_num ,SUM(quantity*item_prices) AS order_total
FROM orderitems
GROUP BY order_num//需要和select中的列相同，除了聚集函数
HAVING SUM(quantity*item_price)>=50
ORDER BY order_total;

select语句只有select子句是必须的，且select后面跟的是表达式

利用子查询进行过滤，select的结果可用于另一条SELECT语句的WHERE子句。内层的SELECT语句的列和外层的WHERE列相同

SELECT order_num FROM orderitems WHERE prod_id='INIT2';//20002,20004
SELECT cust_id FROM orders WHERE order_num IN(20002,20004)
 合并
SELECT cust_id
FROM orders
WHERE order_num IN(SELECT order_num
                   FROM orderitems
                   WHERE prod_id='INT2');//执行流程：从内向外。
//查询ID的信息。
SELECT cust_name,cust_contact
FROM customers
WHERE cust_id IN(SELECT cust_id//返回客户ID
                FROM orders
                WHERE order_num IN(SELECT order_num//返回订单号列表
                                   FROM orderitems
                                   WHERE prod_id='INT2'));

作为计算字段使用子查询，子查询：过滤或者使用计算字段

--每个客户的总订单数
思路：先求单个客户的，然后使用子查询
SELECT COUNT(*) AS orders
FROM orders
WHERE cust_id=10001;
//为了对每个可以执行COUNT(*) ,子查询

SELECT cust_name,
		cust_state,
		(SELECT COUNT(*)
        FROM orders
        WHERE orders.cust_id=customers.cust_id) AS orders
 FROM customers
 ORDER BY cust_name;

子查询构造过程：从内到外，逐渐进行。

测试时外层循环用硬编码，eg:where cust_id=23223;

子查询最常见的使用是，以及
一类数据一个表。各表通过某些常用的值（即关系设计中的关系（relational））互相关联。
外键（foreign key）外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

连接：要连接的和

SELECT vend_name,prod_name,prod_price
FROM vendors AS v,products AS p--所有表
WHERE v.vend_id=p.vend_id-- 连接方式：equijoin，等值连接，又称内部联结
ORDER BY vend_name,prod_name;
完全限定列名消除二义性！！

SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id=products.vend_id;
//多个表。列出所有，然后关联！！

其实，对于这种联结(等值连接、内部连接)可以使用稍微不同的语法来明确指定联结的类型。FROM A,B WHERE A.col1=B.col2可以简写为：inner join。联结是SQL中最重要最强大的特性

使用哪种语法? ANSI SQL规范首选INNER JOIN语法，连接条件。

别名除了用于列名和计算字段外，SQL还允许给表名起别名。

缩短SQL语句；
允许在单条SELECT语句中。自连接

--物品有问题，该供应商的其他商品是否也有问题
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' --级联操作和过滤数据。

联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结,需要与left或者right来指明

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）

SELECT customers.cust_id,COUNT(orders.order_num) AS num_ord
FROM customers RIGHT OUTER JION orders
ON orders.cust_id=customers.cust_id

应该，否则会得出笛卡儿积。

多条SQL 的组合查询 UNION

MySQL也允许执行多个查询（多条SELECT语句）,并将结果作为单个查询结果集返回,union或者复合查询(compound query),组合查询和多个WHERE条件

所需做的只是给出每条SELECT语句，在各条语句之间放上关键字UNION。，因为返回的结果合为一个表

--UNION从查询结果集中自动去除了重复的行
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price<=5
UNION ALL--保留重复
SELECT vend_id,prod_id,prod_price
FROM proucts
WHERE vend_id IN(1001,1002)
ORDER BY vend_id,prod_price;--能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后

----等价于多个where条件
SELECT vend_id ,prod_id,prod_price
FROM products
WHERE prod_price<=5
	OR vend_id IN (1001,1002)

UNION几乎总是完成与多个WHERE条件相同的工作。UNION ALL为UNION的一种形式，它完成WHERE子句完成不了的工作。如果确实需要每个条件的匹配行全部出现（包括重复行），则必须使用UNION ALL而不是WHERE。

在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后

全文索引

CREATE TABLE product_notes(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datetime NOT NULL,
note_text text NULL,

PRIMARY KEY(note_id),
FULLTEXT(note_text)
)ENGINE=MyISAM;

MyISAM支持全文本搜索（对结果排序，较高优先级的行先返回，自带停用词列表(stopword)），而InnoDB不支持
子句FULLTEXT(列名称)的指示对它进行索引，先导入，后定义索引，在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。

SELECT note_text
FROM product_notes
WHERE Math(note_text) Against('rabbit') AS rank;
-- 效果上等价于LIKE ‘%rabbit%’,但效率不同。
SELECT note_text
FROM product_notes
WHERE note_text LIKE '%rabbit%';

插入完整的行 insert into tables values()

INSERT INT Customers(可以指定列名顺序)
VALUES(a),VALUES(b);-- 插入多个行

。插入时提高整体性能，可以使用INSERT LOW_PRIORITY INTO

插入检索出的数据 insert select，列的位置。

INSERT INTO customers(cust_id,cust_contact,cust_email,cust_name) SELECT
cust_id,cust_contact,cust_email,cust_name
FROM custnew;

更新数据(update,以where结束)和删除数据(delete)

UPDATE (IGNORE) customers--  表名
SET cust_email='[email protected]'，cust_name='ddd'
WHERE cust_id=10004;

不要省略WHERE子句(对update和delete都有效),UPDATE语句总是以要更新的表的名字开始.

DELETE FROM要求指定从中删除数据的表名。WHERE子句过滤要删除的行。

DELETE FROM customers
WHERE cust_id=10004;

DELETE不需要列名或通配符。。
更快的删除：truncate：TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。应该使用引用完整性。使用默认值而不是NULL值

创建表：表名+列名

CREATE TABLE orders(
order_num int NOT NULL AUTO_INCREMENT,
cust_id int NOT NULL DEFAULT 1,-- 默认值
PRIMARY KEY(order_num)
)ENGINE=InnoDB;

NULL为默认设置.使用的最简单的编号是下一个编号.AUTO_INCREMENT;

每个表只允许一个AUTO_INCREMENT列,而且它必须被索引（如，通过使它成为主键）。通过last_insert_id()函数获得最后一个AUTO_INCREMENT.存储引擎负责创建表等操作。

在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。

更新表 alter table

ALTER TABLE vendors
ADD vend_phone CHAR(20);-- 增加列。
ALTER TABLE vendors
DROP COLUMN vend_phone;

ALTER TABLE的一种常见用途是

ALTER TABLE order_items
ADD CONSTRAINT fk_item_orders
FOREIGN KEY(order_num) REFERECES orders(order_num);

使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份,没有撤回操作。
：DROP TABLE 表名

：RENAME TABLE 原来的表名 TO 新的表名

RENAME TABLE backup_a TO a,
			backup_b TO b;

视图

。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

视图作用：

重用SQL。
简化复杂的SQL操作。

视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。
视图用CREATE VIEW语句来创建。

SHOW CREATE VIEW viewname

其语法为DROP VIEW viewname;

视图更新：先drop后create或者：CREATE OR REPLACE VIEW
；视图的另一常见用途是。视图对于简化计算字段的使用特别有用。

CREATE VIEW product_customers AS
SELECT cust_name,cust_contact,prod_id
FROM customers,orders,order_items
WHERE customers.cust_id=orders.cust_id
	AND order_items.order_num=orders.order_num;-- 连接条件
--  体会，这儿就是将连接之后的逻辑的表作为视图
--  应用
SELECT * FROM product_customers；
SELECT cust_name,cust_contact
FROM product_customers
WHERE prod_id='TNT2';
--  利用视图，可一次性编写基础的SQL，然后根据需要多次使用。作用类似于函数

CREATE VIEW location AS
SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country),' )') AS title
FROM vendors
ORDER BY vend_name;

SELECT * FROM location;-- 应用视图
--  用视图过滤不想要的数据
CREATE VIEW list AS
SELECT cust_id,cust_name,cust_email
FROM customers
WHERE cust_eamil IS NOT NULL;

--  SELECT * FROM list;-- WHERE子句与WHERE子句将自动合并。
CREATE VIEW infomation AS
SELECT order_num,prod_id,quantity,item_price,quentity*item_prices AS total_price
FROM order_items;
--  应用
SLECT * FROM infomation
WHERE order_num=20005;

基本上可以说，如果MySQL不能正确地确定被更新的，则不允许更新图（包括插入和删除视）。这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：GROUP BY 、JOIN、UNION、聚集函数(MIN(),COUNT(),SUM(),DISTINCT)

使用存储过程

存储过程简单来说，就是为以后的使用而。可将其视为批文件，虽然它们的作用不仅限于批处理。
MySQL称存储过程的执行为调用,CALL

CALL product_pricing(
				@pricelow,
				@pricehigh,
				@price_average);-- 调用。
-- -- --  MYSQL 调用时需要修改结束分割符号。

DELEMITER //
CREATE PROCEDURE prices()
BEGIN
SELECT Avg(prod_price) AS price_avg
FROM products;
END//
DELEMITER ;
-- 应用
CALL prices（）；
-- 删除
DROP PROCEDURE IF EXISTS prices;

带参数的存过程

-- 声明变量
;DECLARE price_low DECIMAL(8,2)；
DELEMITER //
CREATE PROCEDURE prices(
OUT p1 DECIMAL(8,2),
OUT ph DECIMAL(8,2))
BEGIN
SELECT MIN(prod_price) INTO p1 FROM products;
SELECT MAX(prod_price) INTO ph FROM products;
END //
DELEMITER ;
CALL prices(@price_low,@proce_high);所有MySQL变量都必须以@开始。
--  以上调用不显示任何的数据，它返回变量；
应用
SELECT @price_low;
--  COMMENT关键字
SHOW CREATE PROCEDURE

游标cursor：逐行处理结果集

需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。，它不是一条SELECT语句，而是被该语句检索出来的结果集。MySQL游标只能用于存储过程（和函数）。使用FETCH来读取所需数据

BEGIN

	DECLARE o INT;
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE order_nums CURSOR FOR -- 声明游标 ，是一种类型。
		SELECT order_num FROM orders;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

	--使用
    OPEN order_nums;
    --循环
    REPEAT
    	FETCH order_nums INTO o;
        UNTIL done
    END REPEAT;
    --关闭
    CLOSE order_nums;
END;
-- SQLSTATE'02000'是一个未找到条件

触发器：SQL语句与事件关联

应用场景：

insert时检查电话号格式。
delete时保存副本等。

触发器是MySQL响应以下语句(DELETE、INSERT、UPDATE)而（或位于BEGIN和END语句之间的一组语句）.

CREATE TRIGGER new_product AFTER INSERT ON products
FOR EACH ROW SELECT 'product added';
-- 删除，触发器不支持更新
DROP TRIGGER new_product;

只有表才支持触发器，视图不支持（临时表也不支持）。每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后)

CREATE TRIGGER new_order AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;-- FOR EACH ROW 跟具体的SQL语句。

-- 应用
INSERT INTO orders(order_date,cust_id)
VALUES(Now(),10001); -- 返回值就是触发器定义的内容。
-- DELETE 触发器
CREATE TRIGGER delete_order BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
	INSERT INTO archive_orders
	VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END
-- 引用一个名为OLD的虚拟表

-- UPDATE 触发器的应用，数据验证
CREATE TRIGGER update_vendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state=Upper(NEW.vend_state)

触发器的一种非常有意义的使用是创建审计跟踪。

事务处理

事务（transaction）指一组SQL语句；

回退（rollback）指指定SQL语句的过程；
提交（commit）指将未存储的SQL语句结果；
保留点（savepoint）指事务处理中设置的，你可以对它发布回退（与回退整个事务处理不同）。

事务处理用来管理INSERT、UPDATE和DELETE语句,不能回退SELECT、CREATE、DROP。

SELECT * FROM order_totals;
START TRANSACTION;
DELETE FROM  order_totals;
SELECT * FROM order_totals;
ROLLBACK;-- 回退到START TRANSACTION
DELETE FROM order_items WHERE order_num=20001;
DELETE FROM orders WHERE order_num=23334;
SELECT * FROM order_totals;
-- 提交。
COMMIT;

隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。在事务处理时，提交不会隐含地进行.

为了支持，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。这些占位符称为保留点SAVEPOINT

SAVEPOINT delete1;
ROLLBACK TO delete1;-- 保留点越多越好。RELEASE SAVEPOINT

更改默认的提交行为(连接专用)：SET autocommit=0;

全球化和本地化:字符集

[ ] 字符集为字母和符号的集合；
[ ] 编码为某个字符集成员的内部表示；
[ ] 。

SHOW CHARACTER SET;
SHOW COLLATION;
SHOW VARIABLES LIKE 'character%'

-- 创建表时指定字符集和校对。
-- 当不指定COLLATE,则使用数据库默认。
CREATE TABLE mytable(
    column1 INT,
    columns2 VARCHAR(10)

)DEFAULT CHARACTER SET  hebrew
COLLATE hebrew_general_ci;

校对在对用检索出来的数据排序时起重要的作用。
COLLATE可用于ORDER BY、GROUP BY、HAVING、聚集函数、别名等。

SELECT * FROM customers
ORDER BY lastname,firstname COLLATE latin1_general_cs;

安全管理

访问控制:设置权限，使用GRANT语句

MySQL用户账号和信息存储在名为mysql的库中.

USE mysql;
SELECT user FROM user;

MySQL的权限用用户名和主机名结合定义,不指定采用默认值

CREATE USER 'yyq' identified by '密码' ;-- 可以不指定密码，指定时需要用identified by
RENAME USER yyq TO 'new_name';

GRANT:权限名称-数据库或者表-用户名。回收权限：revoke

GRANT SELECT on test.* TO yyq;
REVOKE SELECT ON test.* TO yyq;

GRANT和REVOKE可在几个层次上控制访问权限：

 ，使用GRANT ALL和REVOKE ALL；
 ，使用ON database.*；
 特定的表，使用ON database.table；
 特定的列；
 特定的存储过程。

简化多次授权:GRANT SELECT,INSERT ON test.* TO yyq

更改口令

SET PASSWORD FOR yyq=Password('ddd');

数据库维护：备份mysqldump

mysqlhotcopy,BACKUP TABLE

analyze table user;
check table user;

查看日志：1.错误日志·hostname.err;2.查询日志hostname.log;3.二进制日志(更新)：hostname-bin；4.慢查询日志hostname-slow.log

改善性能

SHOW VARIABLES;SHOW STATUS;SHOW PROCESSLIST。

总是有不止一种方法编写同一条SELECT语句。
使用EXPLAIN命令检查SQL执行过程。

[x] 应该总是使用正确的数据类型。串类型、数值类型、日期和时间、二进制类型Blob(Binary long Object)
[ ] 。
[ ] LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。

如果数值是计算（求和、平均等）中使用的数值，则应该存储在数值数据类型列中。如果作为字符串（可能只包含数字）使用，则应该保存在串数据类型列中。
不使用引号与串不一样，数值不应该括在引号内。

justisme