MySQL必知必会
来源:互联网 发布:淘宝店多少单一颗心 编辑:程序博客网 时间:2024/05/19 17:27
本文链接
http://alex-my.xyz/books/database/MySQL必知必会
http://blog.csdn.net/alex_my/article/details/72357498
1 基础知识
1 主键
- 唯一标识表中每行的这个列(这组列)称为主键。
- 应该总是定义主键,虽然并不总是需要主键。
- 任意两行都不具有相同的主键值。
- 每一行都必须具有一个主键值,不可为NULL。
2 常用命令
- SHOW DATABASES;
- SHOW TABLES;
- SHOW COLUMNS FROM table;
- SHOW STATUS;
- SHOW CREATE DATABASE database;
- SHOW CREATE TABLE table;
2 检索数据
除非确实需要绝大部分列或全部列,否则最好不使用通配符* 来获取所有的列。检索不需要的列通常会降低搜索和应用程序的性能。
1 DISTINCT和GROUP BY
SELECT DISTINCT `role_id` FROM user;SELECT `role_id` FROM user GROUP BY `role_id`;
- 二者都可以达到去重的效果。
- DISTINCT把列中的全部内容存储到内存中,可以理解为一个hash,最后的到hash中的key就可以得到结果。比较耗内存。
- GROUP BY先将列排序,然后去重。排序比较耗时间。
2 LIMIT
从0开始算
SELECT `role_id` FROM user LIMIT 5 OFFSET 3;SELECT `role_id` FROM user LIMIT 3, 5;
以上两条命令都表示从3开始的5行。
3 排序数据
默认为升序 ASC
。
SELECT `role_id`, `account_id` FROM user_pay ORDER BY `account_id`, `role_id` DESC LIMIT 20;
以上语句中, account_id默认为升序排列,也可以写上ASC。
4 过滤数据
1 IN操作符
SELECT * FROM user WHERE role_id IN (1000001, 1000002);
2 NOT操作符
SELECT * FROM user WHERE role_id IN (1000001, 1000002) LIMIT 10;
3 LIKE操作符
SELECT * FROM user WHERE name LIKE 't%';SELECT * FROM user WHERE name LIKE 't_'; -- t1, t2SELECT * FROM user WHERE name LIKE 't__'; -- t123, t34
- 以上两句都是模糊匹配用户名以t开头。
- %: 匹配任意0个或者多个字符。
- _: 一个_匹配1个任意字符,且必须有一个。
5 正则表达式搜索
MYSQL仅支持多数正则表达式实现的一个很小的子集。
1 基本字符匹配
SELECT name FROM user WHERE name REGEXP 't'; -- t, t1, t2SELECT name FROM user WHERE name REGEXP 't.'; -- t1, t2
.
表示匹配任意一个字符。- LIKE和REGEXP区别:
- LIKE 要求整个列匹配(使用通配符除外), REGEXP只要列中某个片段匹配即可。
- 假设有用户名为s123。则以下例子中, LIKE没有得到结果。
SELECT name FROM user WHERE name LIKE 's1'; -- 没有结果SELECT name FROM user WHERE name REGEXP 's1'; -- s123
2 OR匹配
SELECT name FROM user WHERE name REGEXP 's1|s2' ORDER BY name; -- s123, s2, s234
使用|功能上类似于SELECT中的OR语句。多个OR语句可以使用正则表达式替代,更简洁。
3 匹配几个字符之一
SELECT name FROM user WHERE name REGEXP 's[1238]' ORDER BY name; -- s123, s2, s234, s89
相当于
SELECT name FROM user WHERE name REGEXP 's1|s2|s3|s8' ORDER BY name;
也可以添加^,来匹配除指定以外的内容
SELECT name FROM user WHERE name REGEXP 's[^1238]' ORDER BY name; -- s4, s5
4 匹配范围
SELECT name FROM user WHERE name REGEXP 's[1-8]' ORDER BY name; -- s123, s2, s89..SELECT name FROM user WHERE name REGEXP '[a-z][1-8]' ORDER BY name; -- a1, b2, c3
5 匹配特殊字符
为了匹配特殊字符,必须用\\
为前导。
SELECT name FROM user WHERE name REGEXP 's\\-' ORDER BY name; -- s-5
\\
也用来引用具有特殊含义的字符
多数正则表达式使用\
转义特殊字符,以便能使用这些字符本身。但MySQL要求用\\
。
MySQL解释一个,正则表达式解释另外一个。
6 匹配字符类
为了方便工作,可以使用预定义的字符集
示例:
SELECT name FROM user WHERE name REGEXP '[[:alpha:]]1' ORDER BY name; -- h1, m1, s123
7 匹配多个实例
SELECT name FROM user WHERE name REGEXP '[[:digit:]]{4}' ORDER BY name; -- s4444, 21111
8 定位符
* 示例1
假设要找到以字母开头的用户名
SELECT name FROM user WHERE name REGEXP '[a-zA-Z]';
以上语句将会在文本任意位置进行查找匹配,并不符合以字母开头这依规定. 这里可以使用^
SELECT name FROM user WHERE name REGEXP '^[a-zA-Z]';
6 创建计算字段
存储在表中的数据不一定是应用程序所需要的。我们可以直接从数据库中检索出转换,计算或格式化过的数据。而不是检索出原始数据然后在应用程序中重新格式化。
1 拼接 CONCAT
表中含有role_id, name字段,应用程序需要这样的格式 role_name(role_id)
SELECT CONCAT(name, '(', role_id, ')') FROM user LIMIT 1; -- s123 (1000001)SELECT CONCAT(RTRIM(name), '(', role_id, ')') FROM user LIMIT 1; -- s123(1000001)
- RTRIM()函数去掉了值右边的所有空格。其余有LTRIM(), TRIM()
2 别名 AS
拼接处的结果没有名字,应用程序没法引用。可以使用别名解决这个问题。
SELECT CONCAT(name, '(', role_id, ')') AS info FROM user LIMIT 1;
这样,应用程序就可以使用info这个列,就像它本来就存在于表中一样。
3 执行算术计算
假设用户充值了money(元),每元可以换成10个代币,这里通过计算直接得出获得的总代币。
SELECT role_id, money, money * 10 AS total_gold FROM user LIMIT 10;
7 使用数据处理函数
1 字符串函数
2 数学函数
3 日期时间函数
* type的值可以为:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
4 条件判断函数
IF (expr, v1, v2);
SELECT IF (1 > 0, 'Y', 'N'); -- Y
IFNULL(v1, v2);
如果v1不为NULL, 返回v1,否则返回v2SELECT IFNULL('a', 'b'); -- a
5 系统信息函数
8 分组数据
1 数据分组
假设要获取用户的充值次数,最低充值额度,最高充值额度,平均充值额度,可以用以下命令:
SELECT role_id, COUNT(*) AS num, MIN(money) as min_money, MAX(money) as max_money, AVG(money) AS avg_money FROM user_pay;
以上得出的是总的信息,如果要获取每个用户的这些信息,就可以使用分组了。
SELECT role_id, COUNT(*) AS num, MIN(money) as min_money, MAX(money) as max_money, AVG(money) AS avg_money FROM user_pay GROUP BY role_id ORDER BY num;
以上按照每个用户来计算结果。
- 需要注意的是,GROUP BY 必须出现在 WHERE 之后,ORDER BY 之前
可以使用WITH ROLLUP得到汇总的值
SELECT role_id, COUNT(*) AS num, MIN(money) as min_money, MAX(money) as max_money, AVG(money) AS avg_money FROM user_pay GROUP BY role_id WITH ROLLUP;
以上在在结果的最后,会附上总的结果。
2 分组过滤
假设只需要得到充值2次(包含)以上用户的数据,则需要使用HAVING来过滤。
SELECT role_id, COUNT(*) AS num, MIN(money) as min_money, MAX(money) as max_money, AVG(money) AS avg_money FROM user_pay GROUP BY role_id HAVING num >= 2 ORDER BY num;
- 注意HAVING跟GROUP BY后面。
也可以同时使用WHERE和HAVING。
SELECT role_id, COUNT(*) AS num, MIN(money) as min_money, MAX(money) as max_money, AVG(money) AS avg_money FROM user_pay WHERE time >= 1483200000 GROUP BY role_id HAVING num >= 2 ORDER BY num;
以上通过WHERE新增了条件,2017年以来充值的。
当sql_mode为ONLY_FULL_GROUP_BY需要注意
查看sql_mode值
SELECT @@sql_mode;
结果:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
在sql_mode=ONLY_FULL_GROUP_BY的模式下,以下句子报错
SELECT role_id, money FROM user_pay GROUP BY role_id;
错误:
SELECT list is not in GROUP BY clause and contains nonaggregated column ...
表中的列
,出现在SELECT中时,也得出现在GROUP BY中。SELECT role_id, money FROM user_pay GROUP BY role_id, money;
同样,ORDER BY也需要注意这个问题。
3 SELECT字句顺序
SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY > LIMIT
9 子查询
1 子查询过滤
假设要得出充值用户的用户信息
SELECT role_id, name FROM user WHERE role_id in (SELECT role_id FROM user_pay);
- 在SELECT语句中,子查询总是从内向外处理。
需要保证WHERE语句中需要和子SELECT语句中有相同数目的列。二者名称可以不相同。
... WHERE role_id in (SELECT role_id ...)... WHERE role_id in (SELECT r_id ...)
2 做为计算字段使用子查询
假设要得出用户的充值次数(user_pay)以及用户信息(user)
SELECT role_id, name, (SELECT COUNT(*) FROM user_pay WHERE user_pay.role_id = user.role_id) AS recharge_countFROM user LIMIT 10;
10 联结
1 内联结
同9.2 假设要得出用户的充值次数(user_pay)以及用户信息(user), 以下两种方法都可以获得结果。
使用WHERE子句
SELECT role_id, COUNT(money) FROM user, user_pay WHERE user.role_id = user_pay.rid GROUP BY role_id;
使用INNER JOIN
SELECT role_id, COUNT(money) FROM user INNER JOIN user_pay ON user_pay.role_id = user.role_id GROUP BY role_id;
- ANSI SQL规范首选INNER JOIN。
2 外联结
外联结使用OUTER JOIN来表示。
必须在OUTER 前加上LEFT或RIGHT关键字。OUTER可以省略不写。 LEFT: 表示选中OUTER左侧表的所有行。
RIGHT: 表示选中OUTER右侧表的所有行。
SELECT a.role_id, SUM(b.money) AS total_rechargeFROM user a LEFT JOIN user_pay b ON a.role_id = b.role_id GROUP BY a.role_id;
以上信息获取用户的充值信息,如果有用户没有充值,则total_recharge=NULL。
如果使用RIGHT JOIN,如果user_pay中有用户数据在user表中找不到,则role_id=NULL。
11 组合查询
1 UNION
假设需要获取充值额度为30的用户, 以及渠道为1001的用户,使用组合查询:
SELECT role_id, money FROM user_pay WHERE money = 30UNIONSELECT role_id, money FROM user_pay WHERE channel_id = 1001;
- 组合使用UNION将独立的SELECT相连。
- 每个SELECT查询都必须包含相同的列,表达式或函数。但次序不必相同。
2 UNION ALL
UNION从查询结果中自动去除了重复的行。比如渠道1001也有人充值30的。
如果不想被去除重复的行,可以使用UNION ALL。
3 组合查询结果排序
可以在最后一条的SELECT后添加ORDER BY语句对结果进行排序。
12 全文本搜索
1 引擎支持
- MyISAM和InnoDB(5.6)都支持全文本搜索。
TODO
13 视图
视图可以简化操作,保护数据。
1 创建视图
- 使用CREATE VIEW创建视图。
- 使用DROP VIEW删除视图。
- 这边使用 CREATE OR REPLACE VIEW
创建一个视图,该视图从用户表(user), 用户充值表(user_pay)获取用户基本信息,总充值额度。
CREATE OR REPLACE VIEW user_pay_info ASSELECT b.role_id, b.name, SUM(a.money) AS total_moneyFROM user_pay aRIGHT JOIN user bON a.role_id = b.role_idGROUP BY b.role_id;
使用SHOW TABLES可以发现多了一个表,user_pay_info。
2 使用视图 SELECT
创建好视图后,再想获得用户充值信息,可以通过以下语句:
SELECT * FROM user_pay_info;
十分便捷。
- 虽然表面看是从user_pay_info中获取数据,但实际上仍然是从user, user_pay中获取数据。
3 更新视图 UPDATE
视图中存在以下操作,则不可更新:
- 分组 (GROUP BY, HAVING)
- 联结
- 子查询
- 聚集函数 (MIN, COUNT, SUM)
- DISTINCT
但凡MySQL不能确定能够正确更新到实际表(user, user_pay),则不允许进行视图更新。
一般,应该将视图用于检索,而不用于更新。
14 存储过程
相当于调用预编译好的sql集合。
1 创建存储过程 CREATE PROCEDURE
假设要知道每个用户的充值总额
CREATE PROCEDURE user_pay_total()BEGIN SELECT role_id, SUM(money) AS total_recharge FROM user_pay GROUP BY role_id;END
以上就创建好了。
需要注意的是,如果在命令行工具中直接用以上语句创建,会报错。
因为命令行工具也用;
做为分隔符,sql语句中也是用;
做为分隔符,存在冲突。
是用 DELIMITER 可以自定义命令行工具的分隔符
DELIMITER // CREATE PROCEDURE user_pay_total()BEGIN SELECT role_id, SUM(money) AS total_recharge FROM user_pay GROUP BY role_id;END //DELIMITER ;
以上DELIMITER告诉命令行工具,使用//
做为分隔符。最后一句恢复回;
做为分隔符。
2 使用存储过程 CALL
CALL user_pay_total();
以上语句会执行刚才创建的存储过程。
3 删除存储过程 DROP
可以直接使用DROP删除
DROP PROCEDURE user_pay_total;
但是,如果不存在user_pay_total(),就会报错。
所以,建议用以下命令:
DROP PROCEDURE IF EXISTS user_pay_total;
4 使用参数
参数可以用IN, OUT, INOUT修饰。 TODO
5 检测存储过程
以下语句可以显示创建存储过程的鳄鱼局
SHOW CREATE PROCEDURE user_pay_info;
15 触发器
1 创建触发器
- MySQL触发器只响应以下语句: INSERT, UPDATE, DELETE
- 保持每个数据库触发器名称唯一。
- 只有表才支持触发器,视图,临时表不支持。
创建tb1, tb2同tb1
CREATE TABLE `tb1` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '索引值', `value` int(11) NOT NULL COMMENT '数据', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
创建触发器,命令行下别忘了 DELIMITER
DELIMITER //CREATE TRIGGER tb1_cp_tb2 AFTER INSERT ON tb1 FOR EACH ROWBEGIN INSERT INTO tb2(id, value) VALUES (NEW.id, NEW.value);END //DELIMITER ;
以上触发器在tb1执行INSERT操作时触发,会给tb2插入相同的数据。
2 删除触发器
DROP TRIGGER IF EXISTS tb1_cp_tb2;
3 触发说明
- INSERT
- INSERT触发器可在INSERT执行之前或之后触发。
- 在触发器代码内,可以使用一个名为NEW的虚拟表,访问被插入的行。
- 对于AUTO_INCREMENT列,NEW在INSERT之前为0,在INSERT执行之后为自动生成的值。
- UPDATE
同INSERT DELETE
- DELETE触发器可在DELETE执行之前或之后触发。
在触发器代码内,可以使用一个名为OLD的虚拟表,访问被插入的行。
DELIMITER //CREATE TRIGGER tb1_cp_tb2 AFTER DELETE ON tb1 FOR EACH ROWBEGIN INSERT INTO tb2(id, value) VALUES (OLD.id, OLD.value);END //DELIMITER ;
从tb1删除的数据会被复制到tb2中。
16 事务处理
事务处理可以用来维护数据库的完整性,保证多个SQL命令要么完全执行,要么完全不执行。
1 事务处理示例
SELECT * FROM tb1;START TRANSACTION;DELETE FROM tb1;SELECT * FROM tb1;ROLLBACK;SELECT * FROM tb1;
- 以上语句中,当删除tb1后,再次查询,没有内容。当回滚后,数据又出现了。
- 可以使用COMMIT将事务提交上去执行。
- 不能回退SELECT, CREATE, DROP操作。
- 当执行COMMIT或ROLLBACK后,事务会自动关闭。
2 保留点
复杂的事务处理中,可能存在需要部分回退或者部分提交的情况。
可以使用保留点来处理。
SAVEPOINT d1; -- 创建了保留点...ROLLBACK TO d1; -- 回滚到保留点
当事务关闭后,保留点会自动释放。
3 autocommit
InnoDB默认 autocommit=on,即每一条sql语句都是当成一个事务,执行后就提交。
当写下START TRANSACTION时,autocommit的设置就无效了。需要等待COMMIT或ROLLBACK来结束事务。
autocommit针对的是每个与MySQL的链接,改变其值不会影响其它链接。
- mysql学习--mysql必知必会
- mysql必知必会 - 连接mysql
- mysql 必知必会
- mysql 必知必会
- MySQL必知必会
- MySQL必知必会
- MySQL必知必会
- 《Mysql必知必会》
- MySQL必知必会
- mysql必知必会
- mysql必知必会
- Mysql必知必会
- 《mysql必知必会》
- MySQL 必知必会
- MySQL必知必会
- MySQL必知必会
- MySQL必知必会
- MySQL必知必会
- Gogland use goimports
- UNIX命令行快速入门4
- JAVA的23种设计模式---单例模式
- x&(x-1) 表达式
- 信息安全工程师 学习笔记(三十六)
- MySQL必知必会
- Apache eagle监控hive任务问题
- linux 下多线程错误 undefined reference to `sem_init'
- mybatis事务相关知识点
- 如何处理List条目中含有EditText,并且EditText中文字或者数字可以修改
- 虚拟化相关概念与技术整理(2)——硬件辅助虚拟化简介
- Android Studio查看函数(方法)的形参列表,返回值,以及提示信息
- 数据库 ——(1)
- PHP--内存泄漏