《MySQL必知必会》书摘

前面的章节介绍的SQL,MySQL等内容,就不记笔记了.

查询数据库信息:status
查询数据库变量:show status 查找使用like

第六章:过滤数据

WHERE 子句操作符

范围值检查

SELECT uid,username FROM t_user WHERE uid BETWEEN 3 AND 6;

范围值检查

SELECT uid,username FROM t_user WHERE username IS NULL;

• 返回的是字段为NULL的列(不是等于0),使用IS NOT NULL可以查询非NULL结果

第七章:数据过滤

组合WHERE 子句

AND OR

AND与OR关键字,功能不言而喻,但是在组合使用时AND的优先度高于OR,正确的使用需要使用圆括号明确的分组相应的操作.

IN

SELECT uid,username FROM t_user WHERE uid IN(4,5);

• 为什么要使用IN操作符?
  • IN操作符的语法更清楚且直观.
  • 在使用IN时,计算的次序更容易管理(因为使用的操作符更少)
  • IN操作符一般比OR操作符执行更快.
  • IN的最大优点是可以包含其他SELECT语句,使得能够更动态的建立WHERE子句.

NOT

NOT操作符有且只有一个功能,就是否定它之后的任何条件.
SELECT uid,username FROM t_user WHERE uid NOT IN(2,5);

第八章:用通配符进行过滤

LIKE操作符

• 使用%可以进行通配搜索,例如: %l, a%b,b%.
• 注意:%不仅匹配一个或多个,还可以匹配0个字符.另外%不可以匹配NULL,是一个例外.
• 使用_可以匹配一个字符,但是不能多也不能少.

第九章:用正则表达式进行搜索

select uid,username from t_user where username REGEXP'ab';

• 注意:MYSQL仅支持正则表达式的一个很小的子集.

LIKE 和 REGEXP的区别

• LIKE匹配整个行,如果被匹配的文本在列值中出现,LIKE将不会找到它,相应的行也不会返回(除非使用了通配符).而REGEXP在列值内进行匹配,如果被匹配的文本在列值中出现,REGEXP将会找到它,对应的列将会被返回.这是一个非常重要的区别.
  SELECT uid,username FROM t_user WHERE username REGEXP`1000|2000`;
• 匹配两个字符串其中的一个.

匹配几个字符之一

SELECT uid,username FROM t_user WHERE username REGEXP`[123]Tom`;

匹配范围,例如1到5

REGEXP`[1-5]Tom`

• 剩下的内容基本参考正则的语法,就不记笔记了,但是注意SQL中的预定义字符集不同与java中,请自行查找.
• 匹配字符的出现次数同正则语法中.

第十章:创建计算字段

拼接字段

• 在MySQL中使用Concat()函数拼接两个列
• SELECT Concat(username,uid) FROM t_user;

第十一章:使用数据处理函数

SELECT uid, UPPER(username) AS username_upcase FROM t_user;

• 常用的文本处理函数
  • Left() 返回左侧的字符
  • Length() 返回字符串的长度
  • Locate() 找出字符串的一个子串
  • Lower() 将串转小写
  • LTrim() 去掉串左边的空格
  • RTrim() 去掉右边的字符
  • Soundex() 去掉串的SOUNDEX值
    • SOUNDEX是一个将任何字符串转换为描述其语音表示的字母数字模式的算法.考虑了类似的发音字符和音节,能够对字符串进行发音对比而不是字符的对比.该算法不是SQL的概念,但是大多数DBMS都支持.
    • 应用场景:查寻发音类似的字符
  • SubString() 返回子串的字符
  • Upper() 将串转换为大写

第十二章:汇总数据

聚合函数(aggregate function)运行在行组上,计算和返回单个值的函数.

• AVG() 平均值
• COUNT() 行数
  • Tips:COUNT(*)会包含值,但是指定列名的会忽略.
• MAX() 最大值
  • 会自动忽略NULl值的行
• MIN() 最小值
  • 会自动忽略NULl值的行
• SUM() 列值之和
  • 会自动忽略NULl值的行

第十五章:联结表

SELECT vend_name,prod_name,prod_price 
FROM vendors,products 
WHERE vendors.vend_id = products.vend_id 
ORDER BY vend_name,prod_name;

• 上式使用的是等值联结,这种联结也被称为内部联结.
• 在使用联结时一定不要忘记WHERE子句,否则返回的笛卡尔积比你想象的数据多的多.同时请注意,不正确的过滤条件导致MySQL返回不正确的值.

SELECT vend_name,prod_name,prod_price 
FROM vendors INNER JOIN products 
ON vendors.vend_id= products.vend_id

• 语句中的SELECT与前面相同,但是两个表之间的关系是FROM子句的组成部分,以INNER JOIN指定.在使用这种语法时,联结条件用特定的ON子句而不是WHERE子句给出.传递给ON的实际条件与传递给WHERE的相同.ANSI SQL标准推荐INNER JOIN语法.

第十七章:组合查询

使用UNION

• 使用时只需要将在给出的每条的语句中加上UNION
• 例:查找价格小于5的所有商品,还包括供应商为1001和1002的所有商品(不考虑价格)

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必须由两条或两条以上的SELECT语句组成使用,语句中间使用UNION分割
  • UNION中的每个查询必须包含相同的列,表达式或聚合函数
  • 列数据类型必须兼容,不必完全相同,但是必须是DBMS可以隐式转换的类型
  • 如果从多表中查询到了相同的数据,默认是去除重复的,如果需要所有项目使用UNION ALL
  • 使用UNION时ORDER BY子句只能有一次出现,并且作用整个结果

第十八章:全文本搜索 PASS

第十九章:插入数据

• 尽量避免全字段插入,因为这样的SQL语句高度依赖表中列的定义次序,是很不安全的.
• 插入多行每组值用一对圆括号括起来,用逗号分割.

插入检索出的数据 INSERT SELECT

INSERT INTO customers(
  cus_id,
  cus_email,
  cus_name,
  cus_city
)
SELECT 
cus_id,
cus_email,
cus_name,
cus_city
FROM custnew;

• 注意:虽然上例使用了相同的列名,但其实不要求匹配,甚至MySQL是不关心SELECT返回的列名,它使用的是列的位置.FROM中可以使用WHERE.

第二十章:更新和删除数据

更新数据 UPDATE

UPDATE customers SET cust_email="email@gmail.com" WHERE cust_id=1999;

• 注意:如果不是想要更新全表,一定不要忘记WHERE
• IGNORE关键字:如果UPDATE语句更新多行,并且在更新这些行的其中一部分发生错误,则整个UPDATE操作都会取消,回滚值.为了即使发生错误也进行更新可以使用IGNORE关键字

删除数据 DELETE

• DELETE不需要列明或者通配符,DELETE删除整行而不是列,删除指定列使用UPDATE
• DELETE删除行,但是不删除表本身
• 如果需要更快的删除,使用TRUNCATE TABLE,它完成相同的工作,但是速度更快(因为TRUNCATE是删除原有的表,并且重新创建一个表,而DELETE是逐行删除)

第二十一章:创建和操作表

• 创建表时,表名必须不存在,否则报错.如有需要先删除后创建.如果需要在一个表不存在的时候创建使用关键字IF NOT EXISTS,这样只有在表名不存在的时候才会创建.

使用主键

PRIMARY KEY(vend_id),多主键:PRIMARY KEY(order_num,order_item)

AUTO_INCREMENT

• 使用自增的缺点是插入时都不知道生产的值是谁,可以使用 SELECT last_insert_id();,该语句将返回最后一个AUTO_INCREMENT值

设置表字段的默认值:quantity int NOT NULL DEFAULT 1,

MySQL数据库引擎类型

• 在创建表是可以在最后使用ENGINE=InnoDB指定引擎,MySQL中具有多个引擎,具有不同的特点和特性
• 常见引擎
  • InooDB:一个可靠的事物处理引擎,不支持全文搜索
  • MEMORY:功能同MYyISAM,但是数据存储在内存中,速度很快(适合添加临时表)
  • MyISAM:是一个性能极高的引擎,它支持全文本的搜索,但是不支持事物处理.
  • 注意:外键不能跨引擎使用,即使用一个引擎的表不能引用具有不同引擎表的外键

更新表 ALTER TABLE

• 增加列:ALTER TABLE vendors ADD vend_phone CHAR(20);
• 删除列:ALTER TABLE vendors DROP COLUMN vend_phone;
• 增加外键:ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors(vend_id)

重命名表:RENAME TABLE customers2 TO customers;

第二十二章:视图

• 我的了解:一个暂时存储数据的虚拟表,时我们可以重启SQL语句,简化操作,保护表中的隐私数据,更改时间格式和表示,视图可以返回与底层表不同格式的数据.

视图的规则和限制

• 视图必须唯一命名,且不能和其他表或视图重名,数量不限
• 视图的创建需要足够的访问权限
• 视图可以嵌套,即可以利用其他视图中检索出的数据构造一个视图
• 检索视图时可以使用ORDER BY,这样会覆盖视图中的ORDER BY
• 视图不能索引,也不能有关联的触发器或默认值
• 视图可以和表一起使用,例如联结视图和表进行SELECT

创建视图

• 使用CREATE VIEW语句创建
• 使用SHOW CREATE VIEW viewname;来查看创建视图的语句
• 使用DROP VIEW viewname删除视图

CREATE VIEW productocustomers AS
SELECT cust_name,cust_contact,prod_id 
FROM customers,orders,orderitems 
WHERE customers.cust_id = orders.cust_id 
AND orderitems.order_num = orders.order_num;

• 视图的其他用途,自行了解
  • 利用视图简化复杂的联结
  • 使用视图重新格式化检索出的数据,加文本处理函数等
  • 用视图过滤不需要的数据
  • 使用视图与计算字段

第二十三章:存储过程

• 很多时候需要使用复杂的多条语句才能完成一次操作,这时可以创建存储过程,为之后的使用而保存的一条或多条的MySQL语句.可以看做批文件,但是作用不局限与批处理.

为什么要使用存储过程

• 优点:
  • 简化对变动的管理,如果表名或列名有变化,只需要更改存储过程的代码,使用的人甚至不需要这些变化.
  • 提高性能,因为使用存储过程比单独使用SQL语句快
  • 存在一些只能用在单个请求的MySQL元素和特性,存储过程可以使用它们编写功能更强更灵活的代码(下一章)
  • 简而言之:简单,安全,高性能
• 缺点:
  • 编写存储过程更复杂,需要更高的技能
  • 没有较高的权限不可以创建存储过程

创建存储过程

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

• 存储过程定义时可以在其中接受参数,在()中定义.
• 注意:如果在命令行中编写存储过程是结束符会使存储过程被错误解析,发生报错.解决办法:修改命令行中的语法分隔符.(自行百度)

使用存储过程

CALL productpricing()

编写带变量复杂的存储过程:PASS

第二十四章:游标

• 好像是在存储过程中创建

创建游标

CREATE PROCEDURE processorders()
BEGIN
  DECLARE ordernumbers CURSOR
  FOR
  SELECT ordernum FROM orders;
END;

• 使用游标: 打开游标:OPEN CURSOR,关闭游标:CLOSE ordernumbers;
• 使用FETCH语句访问游标的每一行,FETCH指定检索什么数据,检索出的数据存储在什么地方.它还向前移动游标中的内部行指针,使下一条FETCH语句检索下一行(不能重复读取同一行),编写在存储过程中
• 编写过程:pass

第二十五章:触发器

• 如果需要在某些事件发生时自动执行怎么办,例如:售出商品自动减库存,删除行时保留副本
• 触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句:DELETE INSERT UPDATE,其他语句不支持触发器

创建触发器

• 创建触发器需要4个条件
  • 唯一的触发器名
  • 触发器关联的表
  • 触发器响应的操作(DELETE,INSERT,UPDATE)
  • 触发器何时执行(处理前或处理后)

CREATE TRIGGER newproduct AFTER INSERT ON products FROM EACH ROW SELECT 'Product added`;

• ps:我自己编写的一直报错,不知道是什么问题,有可能是版本的问题吗.

删除触发器: DROP TRIGGER newproduct;

后面还有视图组合触发器等待:pass

第二十六章:事务处理

注意使用支持事物管理的引擎

• 概念同java中,保证一组操作不会中途停止,它们或整体执行,或完全不执行,如果发生错误则回退到某个已知的安全的状态

• 术语

  • 事物(transaction) 指一组SQL语句
  • 回退(rollback) 指撤销指定SQL语句的过程
  • 提交(commit) 指将未存储的SQL语句结果写入数据库表
  • 保留点(savepoint) 指事物处理中设置的临时占位符(place-holder),你可以对它发布回退(与回退整个事物处理不同)

  控制事务处理

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

  • 开始事务START TRANSACTION
  • 那些语句可以rollback:INSERT,UPDATE,DELETE,不能回退CREATE或DROP
  • 创建保留点:SAVEPOINT delete1
    • 每个保留点具有唯一的名字
    • 使用ROLLBACK TO delete1;
    • 保留点越多越好,可以灵活的进行回退

可以修改默认的提交行为: SET autocommit=0

第二十七章:全球化和本地化:设置编码校对等 PASS

第二十八章:访问控制

• 控制用户的权限,保证安全.一般不使用root账户,同Linux一样
• 管理用户,用户存储在mysql表中:USER mysql; SELECT user FROM user;

创建用户,修改权限,删除用户,具体权限:pass

第二十九章:数据库维护

• ANALYZE TABLE 表名来检查表键是否正常
• CHECK TABLE orders.orderitems;
  • 我不知道这两个语句的不同,检查单表的结果是相同的
• 如果从一个表中删除了大量的数据,使用OPTIMIZE来回收使用的空间,优化表的性能.

启动mysql命令行的选项

• --help 表示帮助
• --safe-mode 装载减去某些最佳配置的服务器
• --verbose 显示全文本消息(为获取更详细的帮助消息与–help联合使用)
• --version 显示版本信息然后退出

第三十章:改善性能

• mysql是一个多线程多用户的DBMS,可以执行多个任务,如果每一个执行慢,所有的请求都会慢,可以使用SHOW PROCESSLIST显示所有的活动进程,还可以使用KILL结束某个线程(需要管理员登录)
• 总是有多种方法编写SELECT语句,尝试联结,并,子查询等,找出最佳的方法
• 使用EXPLAIN语句让MySQL解释它将如何执行一个SELECT语句
• 一般来说存储过程比一条条的SQL快
• 使用正确的数据类型
• 尽量不使用通配符匹配行
• 导入数据是关闭自动提交,因为可能还需要删除索引
• SELECT中有一系列复杂的OR条件,可以使用多条SELECT和UNION语句联结,会极大的改善性能
• 索引改善数据检索的性能,但损害数据插入,删除和更新的性能.
• LIKE很慢,使用FULLTEXT代替

结束 2020/1/12 20:57