MySQL
数据库和表
SHOW DATABASES; 显示所有数据库:
USE database; 选择数据库:
SHOW TABLES; 显示数据库内所有表:
SHOW COLUMNS FROM customers; 显示表列:
SHOW STATUS; 显示广泛的服务器状态信息
检索(SELECT)
SELECT (DISTINCT) prod_name, prod_id, prod_price
FROM prodcuts;
限制结果 LIMIT
LIMIT 5; 显示0-5行
LIMIT 5,5; 显示5开始的5行
排序(ORDER BY)
SELECT prod_name
FROM products
ORDER BY prod_price(DESC),prod_name;
ORDER BY 所依据的列可以不在SELECT中。默认是升序,若需要降序在对应列名后+DESC。
过滤(WHERE)
SELECT prod_name,prod_price
FROM products
WHERE prod_price=2.50;
(ORDER BY )
ORDER BY在WHERE后面
WHERE子句操作符有: =, <>, !=, <, <=, >, >=, BETWEEN, IS NULL。
操作符:AND, OR, IN, NOT (AND优先次序比OR高)
通配符过滤(LIKE)
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '%anvil_'
%:表示任何字符出现任意次数。
_:表示任何字符出现一次
使用技巧
1、不要过度使用通配符。如果其他操作符能达到相同的目的,应该使用其他操作符。
2、除非绝对有必要,否则不要放在开始处。
正则表达式搜索(REGEXP)
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000' ([1-2]000 )
LIKE和REGEXP的区别
LIKE匹配整个列,在列值中出现也不会返回(除非使用通配符,这时候其实也是整个列相同),只有整个列相同才会返回。( LIKE ‘1000’ 不能检索出’JetPack 1000’)
REGEXP在列值中匹配,只要在列值出现了就可以。( REGEXP ‘1000’ 能检索出’JetPack 1000’)
字符类
类 | 说明 |
---|---|
[:alnum:] | 任意字母和数字(同[a-zA-Z0-9]) |
[:alpha:] | 任意字符(同[a-zA-Z]) |
[:blank:] | 空格和制表(同[\\t]) |
[:cntrl:] | ASCII控制字符(ASCII 0到31和127) |
[:digit:] | 任意数字(同[0-9]) |
[:lower:] | 任意小写字母(同[a-z]) |
重复元字符
元字符 | 说明 |
---|---|
* | 0个或多个匹配 |
+ | 1个或多个匹配,等于{1,} |
? | 0个或1个匹配,等于{0,1} |
{n} | 制定数目匹配 |
{n,} | 不少于制定数目的匹配 |
{n,m} | 匹配数目的范围(m不超过255) |
^ | 文本的开始 |
$ | 文本的结尾 |
计算字段
拼接
SELECT Concat(vend_name,'(',vend_country,')')
FROM vendors
ORDER BY vend_name;
数据处理函数
函数 | 说明 |
---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值之和 |
分组(GROUP BY)
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id (WITH ROLLUP)
HAVING COUNT(*)>=2;
HAVING用于过滤分组,分组的结果不是有序的。
GROUP BY必须使用选择列,且是所有
重要规定
1、GROUP BY可以包含任意数目的列。
2、如果在GROUP BY子句中嵌套了分组,数据将在最后规定的分组上进行汇总。
3、GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚合函数)。
4、除聚集计算语句外,SELECT语句中的每个列都 必须在GROUP BY子句中给出。
HAVING和WHERE的区别
HAVING用于过滤分组。在分组后过滤
WHERE用于过滤列。在分组前过滤,排除的行不在分组中。
SELECT子句顺序
SELECT
FROM
WHERE
GROUP BY
HAING
ORDER BY
LIMIT
联结
使用WHERE联结
SELECT vend_name, prod_name, prod_price
FROM vendors,products
WHERE vendors.vend_id=products.vend_id
ORDER BY vend_name, prod_name;
如果没有WHERE输出为笛卡尔积
内部联结、自然联结(INNER JOIN)
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id;
子联结通过使用表别名来获得。
外部联结(RIGHT OUTER JOIN\LEFT OUTER JOIN)
对比内部联结包含了没有共同列的那些行
SELECT vend_name, prod_name, prod_price
FROM vendors LEFT OUTER JOIN products ON vendors.vend_id=products.vend_id;
组合查询(UNION)
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price<=5
UNION (ALL)
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002)
ORDER BY vend_id,prod_price;
UNION返回的不饱和重复行,如果需要返回重复行,则使用UNION ALL。
即使ORDER BY在最后一个SELECT中,但他是对整个UNION结果进行排序。
一般使用组合查询的两种情况
1、在单个查询中从不同的表返回类似结果的数据。
2、对单个表执行多个查询,按单个查询返回数据。
UNION规则
1、必须由两条或两条以上的SELECT语句组成。
2、UNION中的每个查询必须包含相同的列,表达式或聚合函数。
全文本搜
虽然有LIKE和REGEXP还需要全文本搜索的理由
1、性能:通配符和正则表达式匹配通常要求MySQL尝试匹配表中的所有行。行数增加搜索会更耗时。
2、明确控制:使用通配符和正则表达式匹配,很难受明确控制匹配什么不匹配什么。
3、智能化:通配符和正则表达式不能提供智能化的选择结果。
启用全文本搜索(FULLTEXT)
在CREATE TABLE时使用FULLTEXT。定义后在增加、更新或删除行时,索引会自动更新。
CREATE TABLE productnotes
(
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;
不用在导入数据时使用FULLTEXT 应该每次插入一行都会更新索引,应该导入所有数据再定义FULLTEXT。
进行全文本搜索(Match()和Against())
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');
返回包含rabbit的行。
**Match()说明:**传递给Match()的值必须与FULLTEXT()定义中的相同。如果制定多个列,则必须按照次序正确列出。且搜索不区分大小写,除非使用BINARY。
LIKE不会以有效的顺序返回数据。但全文本搜索返回以文本匹配的良好程度排序(比如所在的位置)
SELECT note_text, Match(note_text) Against('rabbit')
FROM productnotes;
查询扩展
除了搜索的词,还有展示和搜索的词有关的所有行。所以是两次搜索,1:找到匹配的所有行,2:匹配的行有什么其他“有用”的词,3:再进行全文本搜索,不仅包含搜索的词还包含了“有用”的词。
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
布尔文本搜索
**即使没有FULLTEXT索引也可以使用。**选择要匹配的词,排斥的词,优先等级。
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);
这时候有没有IN BOOLEAN MODE没区别
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
仍然匹配heavy,但-rope* 明确地指示排除包含rope*
布尔操作符 | 说明 |
---|---|
+ | 包含,词必须出现 |
- | 排除,词必须不出现 |
> | 包含,而且增加等级 |
< | 包含,而且减少等级 |
() | 词组合在一起组成子表达式 |
~ | 取消一个词的排序值 |
* | 词尾的通配符 |
“” | 定义一个短语 |
全文本搜索的使用说明
1、索引全文本数据时,短语被忽略且从索引中排除。短词定义为那些具有3个或3个一下字符的词(如果需要可以更改);
2、MySQL带有一个内建的非用词列表,这些词在索引时被忽略;
3、出现频率很高的词,搜索他们没什么用处。因此在MySQL中一个词出现50%以上的行中,则将它作为一个非用词忽略;
4、如果表中的行数小于3行,则全文本搜索不返回结果;
5、忽略词中的单引号。例如,don’t索引为dont;
6、不具有词分隔符(包含日语和汉语)的语言不能恰当的返回全文本搜索结果;
7、仅在MyISAM数据库引擎中支持全文本搜索。
插入数据(INSERT)
可以插入
1、插入完整的行;
2、插入行的一部分;
3、插入多行;
4、插入某些查询的结果。
插入完整的行
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
VALUES(
'Pep E. LaPew',
'100 Main Street',
'Los Angles',
'CA',
'90046',
'USA',
NULL,
NULL);
使用这种语法可以省略列。如果没有指定列则需要按顺序写全部值(建议还是这样写)
INSERT操作可能很耗时,可以使用LOW_PRIORITY来降低优先级(INSERT LOW_PRIORITY INTO),先执行SELECT
插入检索出的数据
INSERT INTO customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM custnew;
其实MySQL不关心SELECT返回的列名。它使用的是列的位置,SELECT的第一列用来填充INSERT指定的第一列。
更新和删除数据
更新数据(UPDATE)
UPDATE (IGNORE) customers
SET cust_email='elmer@fudd.com'
WHERE cust_id=10005;
一定要记得使用WHERE,不使用WHERE的话会更新全部行
使用IGNORE的话,当更新多行即使发生错误也继续执行。不使用IGNORE的话会停止且前面的修改被取消。
删除数据(DELETE)
DELETE FROM customers
WHERE cust_id=10006;
一定要记得使用WHERE,不使用WHERE的话会删除全部行
删除列使用UPDATE结合NULL,DELETE不会删除表
如果想删除全部行可以使用TRUNCATE TABLE(删除表再创建新表),更快。
更新和删除的指导原则
1、除非更新或删除每一行,否则绝对不要使用不带WHERE子句的IUPDATE或DELETE语句;
2、保证每个表都有主键;
3、在对IUPDATE或DELETE语句使用WHERE子句前,应该用SELET进行测试保证过滤的是正确数据;
4、使用强制实施引用完整性的数据库,这样MySQL将不允许删除具有与其他表相关联的数据的行。
创建和操作表
创建表(CREATE)
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL ,
cust_address char(50) NULL ,
cust_city char(50) NULL ,
cust_state char(5) NULL ,
cust_zip char(10) NULL ,
cust_country char(50) NULL ,
cust_contact char(50) NULL ,
cust_email char(255) NULL (DEFAULE 123@qq.com),
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
主键可以是多个列,如PRIMARY KEY (cust_id, cust_name)。但不允许NULL。
可以使用DEFAULE来制定默认值
关于AUTO_INCREMENT
每个表只运行一个AUTO_INCREMENT列且必须被索引。AUTO_INCREMENT会告诉MySQL,本列每增加一行时自动增量。每次执行一个INSERT操作时,MySQL自动对该列增量。
覆盖AUTO_INCREMENT 可以使用INSERT指定插入行的AUTO_INCREMENT列(但它必须是唯一的),该值将被用来替代自动生成的值,且后序的增量将开始使用该手工插入的值。
确定AUTO_INCREMENT的值 使用last_insert_id()来获得生成的值,ast_insert_id()返回最后一个AUTO_INCREMENT值。
引擎类型
InnoDB:可靠的食物处理引擎,不支持全文本搜索;
MEMORY:功能等同于MyISAM,但数据存储在内存中,速度很快(适合临时表);
MyISAM:性能极高的引擎,它支持全文本搜索但不支持事物处理。
引擎类型可以混用,但外键不能跨引擎。
更新表(ALTER)
ALTER TABLE vendors
ADD vend_phone CHAR(20);
ALTER TABLE vendors
DROP COLUMN vend_phone;
常用来定义外键
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) REFERENCES orders (order_num);
删除表
DROP TABLE customers2;
重命名表
RENAME TABLE customers2 TO customers;
视图
视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据,因此它们返回的数据是从其他表中检索出来的。
**性能:**每次使用视图都必须处理查询执行时所需要的检索
**规则:**1、视图必须唯一命名(也不能和表相同)2、有足够的访问权限 3、ORDER BY可以用在视图中,但如果使用视图的SELECT也包含了ORDER BY,那么该视图中的ORDER BY将被覆盖。 4、视图不能索引,也不能有关联的触发器或默认值。
视图的使用
创建
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contavt, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id=orders.cust_id AND orderitems.order_num=orders.order_num;
更新
更新一个视图将更新基表。但并非所有视图都可以更新,如果不能准确的定位则不能更新(包括插入和删除)。
如果有一下操作不允许更新:分组、联结、子查询、并,聚集函数,DISTINCT,导出(计算)列。
存储过程
存储过程简单来说就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件,虽然作用不仅限于批处理。
存储过程的优缺点
理由
1、把处理封装在容易处理的单元中,简化复杂的操作;
2、防止多人反复执行相同的代码(步骤太多)而出错;
3、简化对变动的处理,只需要修改存储过程的代码就可以了;
4、提供性能,使用存储过程比使用单独的SQL语句要快。
总的来说就是**简单、安全、性能高**。
缺陷
1、编写复杂,需要丰富的经验;
2、没有创建存储过程的安全访问权限,只允许使用。
使用存储过程
执行
CALL productpricing(@pricelow,
@pricehigh,
@priceaverage);
其中productpricing是存储过程。
创建存储过程
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END;
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END//
这里注意有两个;。mysql可能会出现语法错误。可以临时更改命令实用程序的语句分隔符。
删除存储过程
DROP PROCEDURE (IF EXISTS) productpricing;
如果不存在会报错,可以使用IF EXISTS来仅当存在时删除。
使用参数
CREATE PROCEDURE productpricing(
OUT pl DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN
SELECT Min(prod_price)
INTO pl
FROM products; SELECT Max(prod_price)
INTO ph
FROM products;
SELECT Avg(prod_price)
INTO pa
FROM products;
END;
接收3个参数,每个参数必须具有指定的类型,这里使用十进制值。IN(传递给存储过程)、OUT(从存储过程传出)和INPUT(对存储过程传入和传出)。存储过程的代码在BEGIN和END语句内。
这里的使用为
CALL productpricing(@pricelow,
@pricehigh,
@priceaverage);
SELECT @priceaverage;
检查存储过程
创建一个存储过程的CREATE语句
SHOW CREATE PROCEDURE ordertotal;
获得何时、由谁创建等详细信息的存储过程列表
SHOW PROCEDURE STATUS;
返回COMMENT的信息。但这时候返回全部存储过程,可以使用LIKE进行一个过滤。
游标(CURSOR)
游标是一个存储在MySQL服务器上的数据库查询,不是一条SELECT语句,而是被该语句检索出来的结果集。
游标主要用于交互应用,其中用户需要滚动屏幕的数据,并对数据进行浏览或更改。
使用游标
1、声明(定义)游标,这个时候没有检索数据,只是定义要使用SELECT语句;
2、打开游标;
3、对填有数据的游标,根据需要取出(检索)各行;
4、在结束游标使用时,必须关闭游标。
CREATE PROCEDURE processorders()
BEGIN
--声明变量
DECLARE o INT;
DECLARE done BOOLEAN DEFAULT 0;
--声明游标
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
--声明句柄 continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE SET done=1;
--打开游标
OPEN ordernumbers;
--循环开始
REPEAT
--使用游标数据
FETCH ordernumbers INTO o;
--循环结束
UNTIL done END REPEAT;
--关闭游标,如果没有声明关闭则END会自动关闭
CLOSE ordernumbers;
END;
FETCH用来检索当前行的order_num列到一个名为o的局部声明的变量中。
声明的顺序一定是变量、游标、句柄。
02000 主要代表的意思可以理解为:
发生下述异常之一:
1、SELECT INTO 语句或 INSERT 语句的子查询的结果为空表;
2、在搜索的 UPDATE 或 DELETE 语句内标识的行数为零;
3、在 FETCH 语句中引用的游标位置处于结果表最后一行之后。
触发器
触发器是MySQL响应一下任意语句而自动执行的一条MySQL语句(或BEGIN和EDN语句之间的一组语句):DELETE、INSERT、UPDATE。
在MySQL中触发器名必须在每个表中唯一,但不是在每个数据库中唯一。但别的DBMS不一定,最好还是数据库内唯一。
只有表才支持触发器,视图不支持,MySQL触发器中不支持CALL语句。
CREATE TRIGGER newproduct AFTER INSERT ON producrs
FOR EACH ROW SELECT 'Product added';
如果BEFOR触发器是不,则MySQL不执行请求的操作,且不执行AFTER触发器。
INSERT触发器
1、在INSERT 触发器代码内,可引用一个名为NEW的虚拟表,访问被插入的行;
2、在BEFORE INSERT 触发器中,NEW中的值也可以被更新;
3、对于AUTO_INCREMENT 列,NEW在INSERT执行之前包含0,在INSERT执行之后包含新的自动生成值。
DELETE触发器
1、在DELETE 触发器代码内,可以引用一个名为OLD的虚拟表,访问被删除的行;
2、OLD中的值全部都是只读的,不能更新。
UPDATE触发器
1、可以引用一个OLD的虚拟表访问以前的值,引用一个NEW的虚拟表访问新更新的值;
2、在BEFORE UPDATE 触发器中,NEW中的值可能也被更新;
3、OLD中的值全都是只读的,不能更新。
管理事务处理
事务处理(transaction processing)可以用来维护数据库的完整性,它保证成批的MySQL操作要么完全执行,要么完全不执行。
回退ROLLBACK
用于回退(撤销)MySQL语句
SELECT * FROM ordertotals;
--事务
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;
事务可以用来管理INSERT、UPDATE和DELETE语句。不能回退SELECT语句(虽然这样做也没意义)。不能回退CREATE或DROP操作。事务处理块可以使用这两句,但不会被撤回。
提交(COMMIT)
一般MySQL语句都是直接针对数据库执行和编写的。即每个语句结束后都有一个隐含提交。
如果想修改MySQL的默认自动提交行为,可以使用:SET autocommit=0;
但在事务块中,提交不会隐含地进行。
START TRANSACTION;
DELETE FROM ordertotals WHERE order_num=20010;
DELETE FROM orders WHERE order_num=20010;
COMMIT;
当COMMIT或ROLLBACK语句执行后,事务会自动关闭。
使用保留点(SAVEPOINT)
SAVEPOINT delete1;
ROLLBACK TO delete1;
保留点在事务处理完完成(执行一条ROLLBACK或COMMIT)后会自动释放。以可以用RELEASE SAVEPOINT释放保留点。
字符集和校对顺序
字符集为字母和符号的合集;
编码为某个字符集成员的内部表示;
校对为规定字符如何比较的指令;
查看字符集完整列表,显示所有可用的字符集以及每个字符集的描述和默认校对。
SHOW CHARACTER SET;
查看所支持校对的完整列表,以及它们使用的字符集。
SHOW COLLATION;
查看数据库所使用的字符集和校对
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';
不同的表或者不同的列可能都需要不同的字符集,可以在创建表时指定。
CREATE TABLE mytable
(
columnn1 INT,
columnn2 VARCHAR(10);
) DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
1、如果制定CHARACTER SET和COLLATE则使用;
2、如果只指定CHARACTER SET,则使用此字符集及其默认的校对;
3、如果即不指定CHARACTER SET也不指定COLLATE,则使用数据库默认。
安全管理
获得所有用户账号列表
USE mysql;
SELECT user FROM user;
创建用户账号,CREATE USER创建一个新用户账号,创建时不一定需要口令,但也可以使用IDENTIFIED BY来指定。
CREATE USER ben IDENTIFIED BY 'password';
GRANT SELECT ON crashcourse.* TO bforta IDENTIFIED BY 'password';
也可以使用GRANT语句或INSERT语句来创建用户。安全起见不建议用INSERT,不安全。
重命名一个用户
RENAME USER ben TO bforta;
删除用户
DROP USER bforta;
查看用户访问权限
SHOW GRANTS FOR bforta;
赋予访问权限
GRANT SELECT ON crashcourse.* TO bforta;
撤销访问权限
REVOKE SELECT ON crashcourse.* TO bforta;
更改口令(密码)
SET PASSWORD FOR bforta=Password('new password');
SET PASSWORD = Password('new password');