MySQL必知必会

最新推荐文章于 2022-07-06 11:04:18 发布
最新推荐文章于 2022-07-06 11:04:18 发布
阅读量770 收藏
点赞数 1
文章标签： 数据库
原文链接：https://yq.aliyun.com/articles/206272
版权

本文链接
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, t2
SELECT * FROM user WHERE name LIKE 't__';   -- t123, t34
  • 以上两句都是模糊匹配用户名以t开头。
  • %: 匹配任意0个或者多个字符。
  • _: 一个_匹配1个任意字符，且必须有一个。

5 正则表达式搜索

MYSQL仅支持多数正则表达式实现的一个很小的子集。

1 基本字符匹配

SELECT name FROM user WHERE name REGEXP 't';    -- t, t1, t2
SELECT 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

\\也用来引用具有特殊含义的字符

特殊字符含义
\\f换页
\\n换行
\\r回车
\\t制表
\\v纵向制表

多数正则表达式使用\转义特殊字符，以便能使用这些字符本身。但MySQL要求用\\。
MySQL解释一个，正则表达式解释另外一个。

6 匹配字符类

为了方便工作，可以使用预定义的字符集

类说明
[:alnum:]任意字母和数字 ([a-zA-Z0-9])
[:alpha:]任意字符 ([a-zA-Z])
[:blank:]空格和指标 (\\t)
[:cntrl:]ASCII控制字符 (ASCII 0~31, 127)
[:digit:]任意数字 ([0-9])
[:graph:]与[:print:]相同，但不包括空格
[:lower:]任意小写字母 ([a-z])
[:print:]任意可打印的数字
[:punct:]同时不在[:alnum:][:cntrl:]中的任意字符
[:space:]包括空格在内的任意空白字符 ([\\t\\n\\r\\t\\v])
[:upper:]任意大写字母 ([A-Z])
[:xdigit:]任意十六进制数字 ([a-fA-F0-9])

示例:

SELECT name FROM user WHERE name REGEXP '[[:alpha:]]1' ORDER BY name; -- h1, m1, s123

7 匹配多个实例

字符说明
*0个或多个匹配
+1个或多个匹配 ({1, })
?0个或1个匹配 ({0, 1})
{n}指定数目的匹配
{n, }不少于指定数目的匹配
{n, m}匹配数目范围, m 不超过255
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 字符串函数

函数说明示例结果
CHAR_LENGTH(S)返回字符串s字符数SELECT CHAR_LENGTH(‘abc 你好’);6
LENGTH(S)返回字符串s的长度SELECT LENGTH(‘abc 你好’);10
CONCAT(S1,S2,…)合并为一个字符串SELECT CONCAT(‘hello’, ’ abc’);hello abc
CONCAT_WS(x, s1, s2,…)同CONCAT,但会加上xSELECT CONCAT_WS(‘+’, ‘1’, ‘2’, ‘3’);1+2+3
INSERT(s1, x, length, s2)将字符串s2替换s1的x位置开始长度为length的字符串SELECT INSERT(‘abcdefg’, 2, 3, ‘123’);a123efg
UPPER(s)将字符串s的所有字母变成大写字母SELECT UPPER(‘abcd’);ABCD
LOWER(s)将字符串s的所有字母变成小写字母SELECT LOWER(‘ABCD’);abcd
LEFT(s, n)返回字符串s的前n个字符SELECT LEFT(‘abcdef’, 3);abc
RIGHT(s, n)返回字符串s的后n个字符SELECT RIGHT(‘abcdef’, 3);def
LPAD(s1, length, s2)字符串s2来填充s1的开始处，使字符串长度达到lengthSELECT LPAD(‘abc’, 8, ‘123’);12312abc
RPAD(s1, length, s2)字符串s2来填充s1的结尾处，使字符串的长度达到lengthSELECT RPAD(‘abc’, 8, ‘123’);abc12312
LTRIM(s)去掉字符串s开始处的空格SELECT LTRIM(’ abc ‘);‘abc ‘
RTRIM(s)去掉字符串s结尾处的空格SELECT RTRIM(’ abc ‘);’ abc’
TRIM(s)去掉字符串s开始和结尾处的空格SELECT TRIM(’ abc ‘);‘abc’
TRIM(s1 FROM s)去掉字符串s中开始处和结尾处的字符串s1SELECT TRIM(‘-’ FROM ‘—hello–’);hello
REPEAT(s, n)将字符串s重复n次SELECT REPEAT(‘abc’, 3);abcabcabc
SPACE(n)返回n个空格SELECT SPACE(3);‘   ’
REPLACE(s, s1, s2)将字符串s2替代字符串s中的字符串s1SELECT REPLACE(‘abcdef’, ‘abc’, ‘12’);12def
STRCMP(s1, s2)比较字符串s1和s2SELECT STRCMP(‘abc’, ‘abc’);0
STRCMP(s1, s2)比较字符串s1和s2SELECT STRCMP(‘abc’, ‘abcd’);-1
STRCMP(s1, s2)比较字符串s1和s2SELECT STRCMP(‘abc’, ‘ab’);1
SUBSTRING(s, n, length)获取从字符串s中的第n个位置开始长度为length的字符串SELECT SUBSTRING(‘abcdefg’, 2, 3);bcd
MID(s, n, length)同SUBSTRINGSELECT MID(‘abcdefg’, 3, 2);cd
LOCATE(s1, s)从字符串s中获取s1的开始位置SELECT LOCATE(‘de’, ‘abcdefg’);4
POSITION(s1, s)从字符串s中获取s1的开始位置SELECT POSITION(‘de’ IN ‘abcdefg’);4
INSTR(s, s1)从字符串s中获取s1的开始位置SELECT INSTR(‘abcdefg’, ‘de’);4
REVERSE(s)将字符串s的顺序反过来SELECT REVERSE(‘a,b,c,d,e,f’);f,e,d,c,b,a
ELT(n, s1, s2, …)返回第n个字符串SELECT ELT(3, ‘abc’, ‘def’, ‘ghi’, ‘jkl’);ghi
EXPORT_SET(…)见示例SELECT EXPORT_SET(6, ‘y’, ‘n’, ‘_’, 3);n_y_y
FIELD(s, s1, s2, …)返回第一个与字符串s匹配的字符串位置SELECT FIELD(‘b’, ‘a’, ‘b’, ‘c’);2
FIND_IN_SET(str, str_list)见示例SELECT FIND_IN_SET(‘4’, ‘6,5,4,3,2,1’);3

2 数学函数

函数说明示例结果
ABS(x)返回x的绝对值
CEIL(x)返回大于或等于x的最小整数
CEILING(x)返回大于或等于x的最小整数
FLOOR(x)返回小于或等于x的最大整数
RAND()返回0->1的随机数
RAND(x)返回0->1的随机数，x值相同时返回的随机数相同
SIGN(x)返回x的符号，x是负数、0、正数分别返回-1、0和1
PI()返回圆周率(3.141593）
TRUNCATE(x, y)返回数值x保留到小数点后y位的值(不会四舍五入)
ROUND(x)返回离x最近的整数
ROUND(x, y)保留x小数点后y位的值(四舍五入)
POW(x, y)返回x的y次方
POWER(x, y)返回x的y次方
SQRT(x)返回x的平方根
EXP(x)返回e的x次方
MOD(x, y)返回x除以y以后的余数
LOG(x)返回自然对数(以e为底的对数)
LOG10(x)返回以10为底的对数
RADIANS(x)将角度转换为弧度
DEGREES(x)将弧度转换为角度
SIN(x)求正弦值(参数是弧度)
ASIN(x)求反正弦值(参数是弧度)
COS(x)求余弦值(参数是弧度)
ACOS(x)求反余弦值(参数是弧度)
TAN(x)求正切值(参数是弧度)
ATAN(), ATAN2()求反正切值(参数是弧度)
COT()求余切值(参数是弧度)

3 日期时间函数

函数说明示例结果
CURDATE(), CURRENT_DATE()返回当前日期SELECT CURRENT_DATE();2017-05-11
CURTIME(), CURRENT_TIME返回当前时间SELECT CURRENT_TIME();19:01:11
NOW()返回当前日期和时间SELECT NOW();2017-05-11 19:01:30
CURRENT_TIMESTAMP()返回当前日期和时间同上
LOCALTIME()返回当前日期和时间同上
SYSDATE()返回当前日期和时间同上
LOCALTIMESTAMP()返回当前日期和时间同上
UNIX_TIMESTAMP()以UNIX时间戳的形式返回当前时间SELECT UNIX_TIMESTAMP();1494500521
UNIX_TIMESTAMP(d)将时间d以UNIX时间戳的形式返回SELECT UNIX_TIMESTAMP(‘2017-05-11 19:02:01’);1494500521
FROM_UNIXTIME(d)将UNIX时间戳的时间转换为普通格式的时间SELECT FROM_UNIXTIME(1494500521);2017-05-11 19:02:01
UTC_DATE()返回UTC日期SELECT UTC_DATE();2017-05-11
UTC_TIME()返回UTC时间SELECT UTC_TIME();11:06:13
MONTH(d)返回日期d中的月份值，1->12SELECT MONTH(‘2017-05-11’);5
MONTHNAME(d)返回日期当中的月份名称SELECT MONTHNAME(‘2017-05-11’);May
DAYNAME(d)返回日期d是星期几SELECT DAYNAME(‘2017-05-11 19:07:12’);Thursday
DAYOFWEEK(d)日期d今天是星期几，1星期日，2星期一SELECT DAYOFWEEK(‘2017-05-11’);5
WEEKDAY(d)日期d今天是星期几，0表示星期一，1表示星期二SELECT WEEKDAY(‘2017-05-11’);3
WEEK(d)，WEEKOFYEAR(d)计算日期d是本年的第几个星期，范围是0->53SELECT WEEK(‘2017-05-11’);19
DAYOFYEAR(d)计算日期d是本年的第几天SELECT DAYOFYEAR(‘2017-05-11’);131
DAYOFMONTH(d)计算日期d是本月的第几天SELECT DAYOFMONTH(‘2017-05-11’);11
QUARTER(d)返回日期d是第几季节，返回1->4SELECT QUARTER(‘2017-05-11’);2
HOUR(t)返回t中的小时值SELECT HOUR(‘2017-05-11 19:11:23’);19
MINUTE(t)返回t中的分钟值SELECT MINUTE(‘2017-05-11 19:11:23’);11
SECOND(t)返回t中的秒钟值SELECT SECOND(‘2017-05-11 19:11:23’);23
EXTRACT(type FROM d)从日期d中获取指定的值，type指定返回的值(见下文)SELECT EXTRACT(WEEK FROM ‘2017-05-11 19:11:23’);19
TIME_TO_SEC(t)将时间t转换为秒SELECT TIME_TO_SEC(‘19:11:23’);69083
SEC_TO_TIME(s)将以秒为单位的时间s转换为时分秒的格式SELECT SEC_TO_TIME(69083);19:11:23
TO_DAYS(d)计算日期d距离0000年1月1日的天数SELECT TO_DAYS(‘2017-05-11 19:11:23’);736825
FROM_DAYS(n)计算从0000年1月1日开始n天后的日期SELECT FROM_DAYS(736825);2017-05-11
DATEDIFF(d1,d2)计算日期d1->d2之间相隔的天数SELECT DATEDIFF(‘2017-05-11’, ‘2017-05-12’);-1
ADDDATE(d,n)计算其实日期d加上n天的日期SELECT ADDDATE(‘2017-05-11 19:11:23’, 3);2017-05-14 19:11:23
ADDDATE(d，INTERVAL expr type)计算起始日期d加上一个时间段后的日期SELECT ADDDATE(‘2017-05-11 19:11:23’, INTERVAL 3 HOUR);2017-05-11 22:11:23
DATE_ADD(d,INTERVAL expr type)同上SELECT DATE_ADD(‘2017-05-11 19:11:23’, INTERVAL 10 HOUR);2017-05-12 05:11:23
SUBDATE(d,n)日期d减去n天后的日期SELECT SUBDATE(‘2017-05-12 05:11:23’, 13);2017-04-29 05:11:23
SUBDATE(d,INTERVAL expr type)日期d减去一个时间段后的日期SELECT SUBDATE(‘2017-04-29 05:11:23’, INTERVAL 10 MINUTE);2017-04-29 05:01:23
ADDTIME(t,n)时间t加上n秒的时间SELECT ADDTIME(‘2017-04-29 05:01:23’, 30);2017-04-29 05:01:53
SUBTIME(t,n)时间t减去n秒的时间SELECT SUBTIME(‘2017-04-29 05:01:53’, 30);2017-04-29 05:01:23
DATE_FORMAT(d,f)按表达式f的要求显示日期dSELECT DATE_FORMAT(‘2017-04-29 05:01:23’, ‘%Y-%m-%d’);2017-04-29
TIME_FORMAT(t,f)按表达式f的要求显示时间tSELECT TIME_FORMAT(‘2017-04-29 05:01:23’, ‘%r’);05:01:23 AM

* 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，否则返回v2

    SELECT IFNULL('a', 'b');    -- a

5 系统信息函数

函数说明示例结果
VERSION()返回数据库的版本号SELECT VERSION();5.7.11
CONNECTION_ID()返回服务器的连接数SELECT CONNECTION_ID();13
DATABASE()返回当前数据库名SELECT DATABASE();database-learn
USER()返回当前用户SELECT USER();root@localhost
CHARSET(s)返回字符串s的字符集SELECT CHARSET(“123”);utf8
COLLATION(s)返回字符串s的字符排列方式SELECT COLLATION(“a123”);utf8_general_ci
LAST_INSERT_ID()返回最近生成的AUTO_INCREMENT值SELECT LAST_INSERT_ID();0

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_count
FROM 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_recharge
FROM 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 = 30
UNION
SELECT 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 AS
SELECT b.role_id, b.name, SUM(a.money) AS total_money
FROM user_pay a
RIGHT JOIN user b
ON a.role_id = b.role_id
GROUP 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 ROW
BEGIN
    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
    1. INSERT触发器可在INSERT执行之前或之后触发。
    2. 在触发器代码内，可以使用一个名为NEW的虚拟表，访问被插入的行。
    3. 对于AUTO_INCREMENT列，NEW在INSERT之前为0，在INSERT执行之后为自动生成的值。
  • UPDATE
    同INSERT

  • DELETE

    1. DELETE触发器可在DELETE执行之前或之后触发。

    2. 在触发器代码内，可以使用一个名为OLD的虚拟表，访问被插入的行。

      DELIMITER //

CREATE TRIGGER tb1_cp_tb2 
AFTER DELETE ON tb1 
FOR EACH ROW
BEGIN
    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的链接，改变其值不会影响其它链接。

weixin_33826609
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql必知必会
u014606454的博客
07-18 489
第一章 数据库（database） 保存有组织的数据的容器（通常是一个文件或一组文件）。 数据库软件称为DBMS（数据库管理系统）。数据库是通过DBMS创建和操纵的容器。 表（table） 某种特定类型数据的结构化清单，用来存储某种特定类型的数据。 模式（schema） 关于数据库和表的布局及特性的信息。 列（column） 表中的一个字段。所有表都是由一个或多个列组成的，列中存储着表中某部分的信息。 行（row） 表中的一个记录。 主键（primary key）一列（或一组列），其值能够唯一区分表中每个行
《MySQL必知必会》知识点总结
weixin_43896452的博客
10-20 483
数据库是一个以某种有组织的方式存储的数据集合。简单理解就是将其想象为一个文件柜，此文件柜是一个存放数据的物理位置，不管数据是什么以及如何组织的。数据库：保存有组织的数据的容器。数据库软件DBMS：创建操纵数据库这个容器SQL是结构化查询语言(Structured Query Language)的缩写，SQL是一种专门用来与数据库通信的语言。SQL的优点不是某个特定数据库供应商转悠的语言，几乎所有的数据库都支持SQLSQL简单易学，所有语句都是由描述性很强的英语单词组成。
MySQL必知必会_学习笔记
Conor Wang
11-19 184
SQL 结构化查询语言,用来鱼数据库通信的语言 DBMS 数据库管理系统,对数据库进行检索,管理,处理 包括Oracle,MySQL,Microsoft SQL Server, Access ,PostgreSQL MySQL 开源,可二次开发,执行速度快,可信赖 启动MySQL sudo ps -ef|grep mysqld 查看MySQL是否安装 mysql -V 查看M...
30 分钟读完《MySQL 必知必会》
12-14
第一章 了解 SQL 介绍几个数据库的概念 数据库（database） 通过DBMS创建和操纵的保存有组织的数据的容器（通常是一个文件或一组文件）。 数据库软件/数据库管理系统（DBMS）访问数据库，创建和操纵数据库，比如 MySQL、Oracle 表（table） 某种特定类型数据的结构化清单。 列（column） 表中的一个字段。所有表都是由一个或多个列组成的。 数据类型（datatype） 所容许的数据的类型。每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据。 行（row） 表中的一个记录。 主键（primary key）一列（或一组列），其值能够唯一区分表中每个
mysql必知必会重点总结
LYX_WIN
07-07 800
数据库表的创建说明：https://blog.csdn.net/duhena0384/article/details/80396542数据库创建表详细说明：https://www.jianshu.com/p/cf2765fad21f一、数据库1.选择数据库 crashcourse为数据库名USE crashcourse;2.显示数据库SHOW DATABASES;3.显示表SHOW TABLES...
Mysql必知必会
12-14
查看有哪些库 show databases; 查看当前库中有哪些表 show tables 查看表中有哪些列 show columns from table_name; describe table_name; 查看服务器状态 show status; 查看建表语句或者创建...# 表示从第三行
MySQL必知必会系列.pdf
03-11
MySQL必知必会系列 MySQL 是世界上最受欢迎的数据库管理系统之一。本书从介绍简单的数据检索开始，逐步深入一些复杂的内容，包括联结的使用、子查询、正则表达式和基于全文本的搜索、存储过程、游标、触发器、表...
mysql必知必会读书笔记
小杰312的博客
04-25 3499
本文纯粹就是小杰为了更加全面的了解学习mysql的读书笔记, 小杰觉得这本书写的相当不错, 感兴趣的朋友可以去读阅。 小杰在文末也会附上它的pdf的云盘地址, 大家一起学习，共同进步, 知识不分高低, 计算机的学习小杰认为也是一个 量变 ---> 质变 的过程 天道酬勤, 水滴石穿, 在不同的阶段就干好自己当前阶段力所能及之事, 至少是没有在寝室的床上瘫着消磨时光 ---...
《MySQL必知必会》总结
qq_18724841的博客
09-10 231
MySQL必知必会 1.登陆和使用 1.1 登陆 mysql -uroot -proot 1.2 使用 use crashcourse; 1.3 查看数据库/表 常用 SHOW DATABASES; --查看数据库列表 SHOW TABLES; --查看表的列表 SHOW COLUMNS FROM customers; --查看表中的列信息 DESCRIBE customers; --查看表中的列信息(快捷方式) 非常用 SHOW STATUS; --显示服务器状态信息 SHOW CREATE
MySql必知必会学习
sxycylq的博客
07-06 414
MySQL必知必会学习，基本MySQL语法
存储过程 循环创建多张表 和 多个数据库
hudeyong926的专栏
05-25 710
创建所有数据库 调用：call create_databases('znke_z_log_', 0, 128); 参数说明： 1、要创建的数据库前缀 2、目标数据库后缀最小数 3、目标数据库后缀最大数（数据库总数量） CREATE PROCEDURE `create_databases`(IN db_pre VARCHAR(20),IN min_num INT,IN max_num INT) BEGIN DECLARE i INT; DECLARE db_name VARCHAR..
MySQL必知必会的36个知识点！
Java后端技术
11-22 2784
毫无疑问，MySQL 是当下最流行的开源数据库。凭借强大的性能和易于使用性，它已被Google、Facebook、YouTube、百度、网易和新浪等大型互联网公司所应用。...
书评：MySQL必知必会（MysQL crashcourse）
坊间码农一枚
03-08 1895
书评：MySQL 必知必会（MysQL crash course） 一直以来都没有机会真正学习数据库方面的知识，至少是没有直接的驱动力。年前由于工作需要，同事给我介绍了这本书，英文版的，它的名字吸引了我。可是直到前两周我才真正打开它，学习它的内容，现在不仅仅是它的名字吸引我了。 用了一周的时间，我看了多半的内容，其实是没有一周，开始两天一直在鼓捣MySQL安装，启动，管理的一些
mysql必学十大必会_《MySQL必知必会》学习笔记
weixin_30205357的博客
01-19 790
数据库：数据库是一种以某种有组织的方式存储的数据集合。其本质就是一个容器，通常是一个或者一组文件。表：表示一种结构化的文件，可用来存储某种特定类型的数据。模式：描述数据库中特定的表以及整个数据库和其中表的关系。表具有一些特性，这些特性定义了数据在表中如何存储，可以存储什么样的数据，数据如何分解，各个部分信息如何命名等信息。列：表中的一个字段，所有表都是有一个或多个列组成的。行：行是表中的一个独立的...
《Mysql必知必会》
Gyoung杨广
06-09 1222
《Mysql必知必会》
MySQL必知必会汇总
在长期主义之路上，与伟大格局观者同行，做时间的朋友。
10-17 1612
第一章：了解SQL 数据库基础：（概念） 数据库软件： DBMS（数据库管理系统） 数据库： 通过DBMS创建和操纵的容器； 保存有组织的数据的容器–>通常是一个文件或者一组文件； 表： 某种特定类型的结构化清单； 相同的数据库中不能两次使用想用的表名； 不同的数据库中可以使用相同的表名； 列和数据类型： 表有列组成，列中存储着表中某部分的信息； 表中的一个字段，所有的表都是由一个或多个列组成的； 每个列都有对应的数据类型； 数据类型： 所容许的数据的类型，每个表列都有对应
csdn mysql必知必会
最新发布
01-26
CSND MySQL必知必会是一本针对MySQL数据库的入门教程，旨在帮助读者快速上手掌握MySQL数据库的基本知识和使用技巧。全书分为三个部分，分别是MySQL数据库基础知识、MySQL数据库操作和MySQL数据库管理与优化，内容全面系统。 在MySQL数据库基础知识部分，书中介绍了数据库的概念、数据库的设计原则、SQL语言基础以及简单的数据库设计与实现。这些知识点的介绍为读者打下了坚实的理论基础。 在MySQL数据库操作部分，书中详细介绍了MySQL数据库的增删改查操作，包括数据表的创建、数据的插入与更新、数据的删除以及查询操作等。通过这部分的学习，读者可以快速掌握MySQL数据库的基本操作技巧。 在MySQL数据库管理与优化部分，书中主要介绍了数据库备份与恢复、数据库性能优化、数据库安全性管理等内容，帮助读者更好地管理和优化MySQL数据库，保障数据库的安全和稳定运行。 总的来说，CSND MySQL必知必会是一本适合初学者的MySQL数据库教程，内容简洁清晰，通俗易懂。通过学习本书，读者可以快速入门MySQL数据库，并且掌握一定的数据库管理与优化技巧，对于从事软件开发和数据库管理工作的读者来说是一本实用的参考书籍。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值