Mysql

本文为原创文章，转载请注明出处 内容来自《MySQL必知必会》的阅读笔记整理

文章目录

Mysql1.检索2.过滤数据正则表达式 3.计算字段4.数据处理函数聚集函数数据分组 查询联结表组合查询 全文本搜索5.视图为什么使用视图创建视图 6.存储过程存储游标事务管理全球化和本地化安全管理数据库维护

1.检索

使用全限定类名 SELECT products .prod_name FROM products LIMIT 5, 5;

DISTINCT

排序数据 order by DESC ASC

SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;

只直接应用到位于其前面的列名

检索最大值

2.过滤数据

order by需要放在where之后

BETWEEN

NULL

操作符

AND

OR

计算次序

AND操作符计算次序比OR高，用圆括号解决

IN操作符

WHERE id IN(1,2)

NOT操作符

WHERE id NOT IN(1,2)

LIKE操作符

%通配符：任何字符出现的任意次数

WHERE name LIKE jet% 以jet开头的词

WHERE name LIKE jet% k 以jet开头以k结尾的词

WHERE name LIKE %jet% 使用多个%通配符，匹配任何位置包含文本jet的值

空格会有影响，使用函数去掉首尾空格或者使用多个通配符

下划线**(_）**通配符

与%用途一样，但只匹配单个字符，而不是多个

WHERE name LIKE _ jet%

通配符搜索处理代价大，不要过度使用

正则表达式

基本字符匹配

REGEXP与LIKE区别:LIKE匹配整个串而ERGEXP只匹配子串

区分大小写BINARY

WHERE name REGEXP BINARY ‘JER .000’

OR匹配，使用**|**

WHERE name REGEXP ‘1000|2000|3000’

匹配几个字符之一**[]**

WHERE id REGEXP ‘[123] JACK’ []是另一种形式的OR语句，等价于[1|2|3] JACK，不加括号则是1|2|3 JACK（1或2或3 JACK）

[^123] 匹配除1 2 3字符外的任何东西

匹配范围 [123456]等价于[1-6] [abcde]等价于[a-e]

匹配特殊字符

. 匹配任意字符，要查找包含 . 的，则加两个\ ，转义字符，用两个表示（一个MYSQL解释，一个正则表达式库解释）匹配字符类

匹配多个实例

定位符

3.计算字段

拼接字段 concat别名 AS

4.数据处理

函数

文本处理

聚集函数

AVG() COUNT() MAX() MIN() SUM()COUNT(*) COUNT(column)区别在于是否有NULL值的行SELECT AVG(DISTINCT PRICE) AS avg_price from list where id=1000;

数据分组

group by

ROLLUP group by with rollup

WHERE过滤行，而HAVING过滤分组

WHERE是在数据分组前进行过滤，而HAVING是在数据分组后

SELECT id , COUNT（* ) AS num FROM list WHERE PRICE>=10 GROUP BY id HAVING COUNT(*)>=2

SELECT字句顺序 SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT

查询

利用子查询进行过滤

SELECT cust_id FROM list WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id =‘TNT2’);

联结表

关系表的设计原则

表别名和列别名，表别名只在查询中使用，不返回客户机

内联结（等值联结）

外联结 在相关表中没有关联行的行

自联结 使用别名，比子查询快

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’ ;

自然联结

带聚集函数的联结

组合查询

UNION ：连接两个SELECT语句 与OR语句相同 会自动去除重复的行，若不去除则用UNION ALL对组合查询结果进行排序，不允许使用多条ORDER BY

全文本搜索

Match（） Against（） 传递给Match的值必须与FULLTEXT相同，不区分大小写，除非采用BINARY方式

可对结果进行排序，可排除等级为0的行，LIKE没等级

查询扩展

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(‘anvils’ IN BOOLEAN MODE);

​ 包含 heavy但不包含rope： Against(**‘heavy -rope*’ ** IN BOOLEAN MODE) -:排除操作符 *****：阶段操作符，词尾通配符

插入数据（INSERT）

INSERT INTO list(多个列名) VALUES(多个对应的value)，明确指定可以省略列（该列允许NULL值或表定义有默认值）

使用LOW_PRIORITY降低插入优先级（如果SELECT数据检索更重要）

插入多行

INSERT INTO list() VALUES() ; INSERT INTO list() VALUES(); 或者列名相同时，INSERT INTO list() VALUES（），（） ;

插入检索出的数据

从list2检索出要插入的值，插入到list

INSERT INTO list( column1,column2...) SELECT column1,column2... FROM list2;

更新和删除数据

UPDATE 更新表中特定的行和特定的数据，更新多个列时，用一个SET，多个“列=值”之间用逗号分隔，最后一列之后不用逗号

可再WHERE中使用子查询

UPDATE list SET email = '123.com' WHERE id = 1000; #更新哪一行，UPDATE以WHERE子句结束

IGNORE关键字

如果更新多行时发生错误，整个UPDATE操作将被取消；即使发生错误也继续更新，使用IGNORE

UPDATE IGNORE list1...

删除数据

DELETE：删除表的内容而不是表

DELETE FROM list WHERE id = 1000; #删除整行

若要删除所有行，则使用TRUNCATE TABLE速度更快，实际上是删除原来的表并重新创建一个表

要注意数据的更新和删除安全，没有撤销按钮

创建和操纵表

CREATE TABLE list1 ( id int NOT NULL AUTO_INCREMENT, name char(50) NOT NULL , country char(50) NOT NULL DEFAULT "中国", age int NULL, ...... FULLTEXT(id) PRIMARY KEY(id) )ENGINE = InnoDB #若忽略，则默认使用MyISAM引擎

若想在一个表不存在时创建它，则应在表名后给出IF NOT EXSITS

主键

主键必须唯一，每个行必须具有唯一的主键值，若主键使用单个列，则它的值必须唯一，若使用多个列，则列的组合值必须唯一

可在创建表时定义，也可在创建表后定义

多个列的主键

PRIMARY KEY(id，name)

AUTO_INCREMENT

每个表只允许一个AUTO_INCREMENT列，而且该列必须被索引，通过使它成为主键

#返回最后一个AUTO_INCREMENT的值 SELECT last__insert_id()

引擎

InnoDB是一个可靠的事务处理引擎，不支持全文本搜索

MEMORY在功能等同于MyISAM，数据存储在内存中，速度快，适合于临时表

MyISAM是一个性能极高的引擎，支持全文本搜索，但不支持事务处理

外键不可跨引擎

更新表

ALTER TABLE

ALTER TABLE list1 #给表添加一个列并更新表 ADD sex CHAR(20); ALTER TABLE list1 #删除列并更新表，DELETE是删除行 DROP COLUMN sex;

常见用途用来定义外键

ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

删除表

DROP TABLE products;

重命名表

RENAME TABLE products TO productor;

5.视图

为什么使用视图

重用SQL语句。 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。 使用表的组成部分而不是整个表。 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

创建视图

CREATE VIEW //创建视图 SHOW CREATE VIEW viewname //查看创建视图的语句 DROP VIEW viewname //删除视图 DROP VIEW viewname CREATE VIEW //更新视图，先删除再创建 CREATE OR REPLACE VIEW //若视图不存在，则REPLACE VIEW会创建视图，否则会代替原有视图

用视图重新格式化检索出的数据

CREATE VIEW vendorlocations AS SELECT Concat (RTri m(vend_name) ,'(', RTri m(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name; SELECT* FROM vendorlocations;

用视图过滤不想要的数据

CREATE VIEW customeremaillist AS SELECT cust_id, cust_name, cust_email FROM customers WHERE cust_email IS NOT NULL; SELECT * FROM customeremaillist;

更新视图

以下操作不可更新视图：分组，联结，子查询，并，聚集函数，DISTINCT，导出列

视图主要用于数据检索

视图为虚拟的表，包含的不是数据而是根据需要检索数据的查询

6.存储过程

存储

为以后的使用而保存一条或多条MySQL语句的集合，可将其视为批文件

简单 安全 高性能

CALL 执行存储过程 接受存储过程的名字以及需要传递给它的任意参数

创建存储过程

DELIMITER // CREATE PROCEDURE productpricing() BEGIN SELECT Avg(prod_price) AS priceaverage FROM products END // DELIMITER;

删除存储过程

存储过程在创建后，被保存在服务器上以供使用，直至被删除

DROP PROCEDURE IF EXISTS productpricing;

使用参数

CREATE PROCEDURE productpricing( OUT pl DECIMAL(8,2), //十进制，OUT指出相应的参数用来从存储过程传出一个值，IN为传递给存储过程 OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2) ） BEGIN SELECT Min(prod_price) INTO pl //用INTO保存到相应的变量 FROM products; SELECT Max(prod_price) INTO ph FROM products; SELECT Avg(prod_price) INTO pa FROM products; END; CALL productpricing(@pricelow,@pricehigh,@priceaverage); //不显示任何数据，只返回以后可显示的变量 SELECT @pricelow; SHOW CREATE PROCEDURE productpricing; //用来显示创建一个存储过程的CREATE语句

游标

在检索出来的行中前进或后退一行或多行

CREATE PROCEDURE processorders() BEGIN DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; END; -- 打开和关闭游标 OPEN ordernumbers; CLOSE ordernumbers;

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行，指定检索所需的列

触发器

在某个表发生更改时自动处理，响应（DELETE INSERT UPDATE）而自动执行的一条MySQL语句

创建触发器

唯一的触发器名触发器关联的表触发器应该响应的活动（DELETE INSERT UPDATE）触发器何时执行（处理之前或之后） -- 在每次插入新的行之后显示一次 Product added CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added'; -- 删除触发器 DROP TRIGGER newproduct; 表才支持触发器，视图不支持，临时表也不支持每个表每个事件每次只允许一个触发器每个表最多支持六个触发器 （DELETE INSERT UPDATE）*（BEFORE AFTER）

INSERT触发器

在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。 CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;

通常BEFORE用于数据验证或净化，保证插入表中的数据确实是需要的数据

DELETE触发器

在DELETE触发器代码内，引用一个名为OLD的虚拟表，访问被删除的行OLD中的值全都是只读的，不能更新 -- 在任意订单被删除之前执行此触发器， 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，0LD.order_ date, 0LD.cust_ id); END;

UPDATE触发器

在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；OLD中的值全都是只读的，不能更新。 -- 在更新之前保证每行州的名字都是大写 CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

进一步介绍

与其他DBMS相比，MySQL 5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行，则相关 的触发器也能执行。应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透 明地进行，与客户机应用无关。触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个 表非常容易。遗憾的是，MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

事务管理

维护数据库的完整性，成批的MySQL要没完全执行，要么完全不执行

标识事务开始 START TRANSACTION，事务会自动关闭

使用ROLLBACK

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

哪些语句可以回退

INSERT DELETE UPDATE ，不可回退SELECT语句，不能回退CREATE DROP操作，事务处理块中可使用这两个操作但是不能回退

commit

START TRANSACTION; DELETE FROM orderitems WHERE order_num = 20010; DELETE FROM orders WHERE order_num = 20010; COMMIT;

部分提交或部分回退，使用保留点SAVEPOINT，回退到某个占位符

SAVEPOINT delete1; ROLLBACK TO delete1；

释放保留点，在事务处理完成后自动释放，也可用RELEASE SAVEPOINT明确释放

更改默认的提交行为

-- autocommit针对每个连接而不是服务器的 SET autocommit = 0;

全球化和本地化

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

编码为某个字符集成员的内部表示 CHARACTER SET

校对为规定字符如何比较的指令 COLLATE

-- 查看所支持的字符集的完整列表 SHOW CHARACTER SET -- 查看所支持校对的完整列表,显示可用的校对以及它们适用的字符集，有的字符集不止一种校对 SHOW COLLATION -- 查看所用的字符集和校对 SHOW VARIABLES LIKE 'collation%' ; -- 创建表指定字符集和校对 CREATE TABLE mytable ( columnn1 INT, DEFAULT CHARACTER SET hebrew COLLATE hebrew_ genera1_ ci ; //也可指定列设置 columnn2 VARCHAR(10), ) DEFAULT CHARACTER SET hebrew COLLATE hebrew_ genera1_ ci ; -- 在与创建表时不同的校对顺序排序特定的SELECT语句 SELECT * FROM customers ORDER BY lastname,firstname COLLATE latinl _general_cs; Cast()Convert()

安全管理

-- Mysql数据库有一个名为user的表，包含所有用户账号，user表有一个名为user的列，存储用户登录名 USE mysql; SELECT user FROM user; -- 创建用户账号 CREATE USER datalibrary IDENTIFIED BY 'password' -- 重命名一个账户 RENAME USER datalibrary TO library; -- 删除用户帐号 DROP USER library; -- 设置访问权限 SHOW GRANTS FOR library;

GRANT

要授予的权限被授予访问权限的数据库或表用户名 -- 用户me对数据库library只有只读权限 GRANT SELECT ON library.* TO me

每个GRANT添加用户的一个权限

-- 将多条GRANT语句串在一起 GRANT SELECT, INSERT ON library.* TO me

REVOKE

REVOKE SELECT ON library.* FROM me

GRANT和REVOKE可在以下层次上控制访问权限

整个服务器，GRANT ALL 和 REVOKE ALL；整个数据库，使用ON database.*；特定的表，使用ON database.table；特定的列特定的存储过程

更改口令

-- 设置新密码，将新密码传递到Password（）函数进行加密 SET PASSWORD FOR me = Password('123456'); -- 不指定用户名时，更新当前登录用户的口令 SET PASSWORD = Password('123456');

数据库维护

-- 用来检测表键是否正确 ANALYZE TABLE orders; -- CHECK发现和修复问题 CHECK TABLE orders; -- 删除大量的数据后回收空间 OPTIMIZE TABLE orders;