MySQL笔记

Part1 基础介绍

1）数据库是一个以某种方式存储的数据集合
2）DBMS是数据库软件，MySQL是其中一种 客户机-服务器软件。
3）数据库由多张表组成，表由列（字段）和行（记录）组成
4）连结数据库操作
5）基础操作规范：

  • 每一句SQL语句用；结束
  • SQL语句不区分大小写，但执行关键词最好使用大写
  • 对比不同写法的处理速度
SET PROFILING = 1;
SQL语句；
SHOW PROFILES；

Part2 数据库操作

A.库操作

1）查询所有数据库
SHOW DATABASES;

2）使用数据库
USE 数据库名；

3）创建数据库
CREATE DATABASE 数据库名 CHARSET=UTF8；

4）查看当前使用数据库
SELECT DATABASE();

5）删除数据库
DROP DATABASE 数据库名；

B.表操作

1）查看当前DATABASE的所有表
SHOW TABLES;

2）查看表结构

  • DESC 表名；
  • SHOW COLUMNS FROM 表名；

3）查看表的创建语句
SHOW CREATE TABLE 表名；

4）创建表
CREATE TABLE 表名（列1 类型1 约束1，列2 类型2 约束2）ENGINE=INNODB；

  • 创建列时需指明类型和约束

常见类型：

  • 整数：int 、bit（*只有1和0两个值）
  • 非负数：unsigned
  • 小数：decimal(X,Y) → X位数，小数占Y位
  • 字符串：varchar(X) → X个字符，输入字符数量可不等于X，可变
    char(X) → X个字符，输入字符数量等于X，不可变
  • 枚举：enum(值1，值2)

常见约束

  • 主键：primary key → 唯一识别数据
    // 指定方式：
    a. CREATE TABLE 表名（id INT PRIMARY KEY,.........);
    b. CREATE TABLE 表名（id INT,.........,PRIMARY(id));
    // 主键通常和AUTO_INCREMENT连用，一个表里只能由一个AUTO_INCREMENT
    // 获取最后一个AUTO_INCREMENT值 → SELECT LAST_INSERT_列名();
    // 主键非空 NOT NULL
  • 是否为空值 NULL / NOT NULL
  • 惟一值 UNIQUE：此字段的值不允许重复
    默认 DEFAULT 数字/字符串：当不填写此值时会使用默认值，如果填写时以填写为准
CREATE TABLE test（
                  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                  name VARCHAR(20) NULL DEFAULT ' ',
                  age INT UNSIGNED DEFAULT 0,
                  gender ENUM('男'，'女’),
                  height DECIMAL(5,2));

5）修改表 → ALTER

  • 增加字段
    ALTER TABLE 表名 ADD 列名 类型 约束；

  • 修改字段（重命名）
    ALTER TABLE 表名 CHANGE 原列名 新列名 类型 约束；

  • 修改字段（不重命名）
    ALTER TABLE 表名 MODIFY 列名 类型 约束；

  • 删字段
    ALTER TABLE 表名 DROP 列名；

6）删除表
DROP TABLE 表名；

7）重命名表
RENAME TABLE 原表名 TO 旧新表名

C. 数据操作-增删改（查）

1）增

  • 全列插入
    INSERT INTO 表名 VALUES(值1，值2，........);
    // 值的顺序与表中字段顺序对应，如果无值，用NULL占位
    // 主键列是自动增长，但在全列插入时需要用 0 或 NULL 或 DEFAULT占位，插入成功后以实际数据为准

  • 部分列插入
    INSERT INTO 表名(列1,列3,....) VALUES(值1,值3,.....);

  • 多行插入
    INSERT INTO 表名 VALUES(值1，值2，........),(值1-1，值2-1，.........);

2）删
DELETE FROM 表名 WHERE 条件；
// DELETE 不需要接通配符* 即可把该行所有列信息删除
// 永久性删除
// 增加一个 is_delete 列 来逻辑删除，保留原删除数据，通过is_delete数值来判断是否删除

ALTER TABLE 表名 ADD is_delete bit DEFAULT 0;
UPDATE 表名 SET is_delete=1 WHERE 条件；

3）改
UPDATE 表名 SET 列1 = 值1，列2 = 值2，....... WHERE 条件；

Part3 查询

A.基本语句

- SELECT 语句
1）查询所有列，使用通配符*
SELECT * FROM 表名;

2）查询指定列，如果是多个，使用逗号隔开
SELECT 列1，列2，... FROM 表名;

3）检索不同行，使用DISTINCT，至于指定列名前，但DISTINCT 作用于其后的所有列，而不只是最靠近的那个
SELECT DISTINCT 列1 FROM 表名;

4）限制结果，使用LIMIT X,Y，从X行开始的取Y行（X、Y表数字）
SELECT * FROM 表名 LIMIT 1,2;
-- LIMIT和X中间要有空格
-- Y不写，默认为选取一行
-- X是从行0开始的，即1表示第2行

// LIMIT另一种写法：LIMIT Y OFFSET X（从X行开始的取Y行）

5）使用 AS 给表、列起别名，但限定引用时也需要用别名
SELECT biao.列1 AS a FROM 表名 AS biao;
列别名可返回到客户机，但表别名只能在查询中使用以缩短SQL语句

- ODER BY 排序语句
1）跟在WHERE子句之后，可接多个列名，中间用逗号隔开，默认升序，若降序在尾部加 DESC
SELECT * FROM 表名 ORDER BY 列1,列2 DESC;

  1. ORDER BY + LIMIT组合可筛选出按序排列后的指定名次
    SELECT * FROM 表名 ORDER BY 列1 LIMIT 3;

- WHERE 条件筛选语句
1）根据某个条件过滤数据 WHERE 条件，处理行数据
2）WHERE 子句类型

  • 比较运算

    = 等于
    <> 或 != 不等于
    < 小于
    <= 小于等于
    大于
    = 大于等于
    BETWEEN 在指定的两个值之间（连续），包括开始值和结尾值

  • 逻辑运算

    IN 在指定范围内（非连续），所有值用括号括起来，且用逗号分隔；经常和子查询配合使用
    AND 满足所有给定条件
    OR 满足任一给定条件
    NOT 否定之后跟的任何条件，但跟NULL组合时，为 IS NOT NULL

    *AND 优先于OR，多个逻辑运算操作符组合时，用括号来分隔

  • LIKE +通配符 过滤，字符串模糊查询
    1）通配符：用来匹配值的一部分的特殊字符。

    % 表示任意数量的字符，但不能匹配NULL
    _ 表示一个字符

    2）LIKE 搜索模式：由字面值、通配符或者两者结合的搜索条件，此时搜索区分大小写

    3）通配符匹配空格，所以尾空格可能会干扰通配符匹配，建议使用RTRIM()函数来消除空格从而进行准确的字符串匹配

    4）通配符并不是高效的搜索条件，尤其至于开始处的搜索模式，不要过度使用

  • NULL 判断是否为空
    WHERE 列 IS NULL
    1）NULL 为空值，不等于0，空格' '，空字符串''
    2）NULL与NOT组合为 IS NOT NULL
    3）在通过过滤选择出不具有特定值的行时，无法返回具有NULL值的行

- GROUP BY 分组语句
1）GROUP BY子句处理分组数据，然后对每个组而不是整个结果进行聚集
SELECT 列1 FROM 表名 GROUP BY 列1;

  • GROUP BY后可以跟多个列
  • GROUP BY子句中列出的每个列都必须是检索列或有效的表达式 （但不能是聚集函数）
  • 除聚集计算语句之外，SELECT 语句中的每个列都必须在GROUP BY子句中给出
  • 如果SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名
  • GROUP BY子句必须在WHERE子句之后，ORDER BY子句之前。

2）GROUP BY + ROLLUP
可以得到每个分组以及每个分组汇总的值

3）GROUP BY + HAVING
针对列的条件筛选，跟在GROUP BY 后，支持所有WHERE操作符

4）GROUP BY + GROUP_CONCAT()
根据分组结果，使用GROUP_CONCAT()来放置每一组的某字段的值的集合
SELECT 列1,GROUP_CONCAT(列2) FROM 表名 GROUP BY 列1;

B.子查询

嵌套在查询语句里的查询。子查询是辅助主查询的,要么充当条件,要么充当数据源
1）替换WHERE子句里的子查询
需保证子查询里的SELECT语句具有元查询WHERE子句中相同的列

SELECT 列1
FROM 表1
WHERE 列x 操作符 （SELECT 列x
                   FROM 表2
                   WHERE 某条件）；

2）替换计算字段的子查询
- 需保证子查询里的SELECT语句具有元查询WHERE子句中相同的列
- 需先确保子查询里的列的值与元查询里的列的值都匹配，且使用限定引用

SELECT 列1,
       （SELECT 函数
        FROM 表2
        WHERE 表1.列x = 表2.列x）AS 别名
FROM 表1；

C.联结表

将关系表联结在一次处理数据，通过外键定义多表之间的关系。外键即某个表中的一列是另一个表的主键
1）内联结/等值联结（交集）

  • WHERE联结语句
SELECT 列*
FROM 表1，表2
WHERE 表1.主键 = 表2.外键；

列*：在不是列1主键的情况下，且不是自联结，如果是两个表中重名的列，则使用限定引用。
// 笛卡尔积：由没有联结条件的表达关系返回的结果
//联结的表越多，性能下降的越快

  • INNER JOIN ON语句
SELECT 列*
FROM 表1 INNER JOIN 表2
ON 表1.主键 = 表2.外键；

2）自联结
同一个表互相联结，需使用别名来区分

SELECT 列1
FROM 表1 AS b1 INNER JOIN 表2 AS b2
ON b1.主键 = b2.主键
WHERE b2满足某条件；

3）自然联结
排除多次出现，使每个列只返回一次。一般是主表使用通配符（主表.*），对其它表的列使用明确子集来完成（其它表.列1，其它表.列2）

4）外部联结（半并集）
结果除了包含关联交集的行，也包含没有关联行的那些行。通常使用 LEFT OUTER JOIN ON（以左为主表） 或者 RIGHT OUTER JOIN ON。

D.组合查询

多个SELECT语句一起执行，每个SELECT语句之间用UNION联结

1）适用情况

  • 同一个表中不同筛选条件，但返回一样的列、表达式、聚集函数（顺序可不一致）
SELECT 列1,列2,列3，
FROM 表名
WHERE 列2某条件
UNION
SELECT 列1,列2,列3，
FROM 表名
WHERE 列3某条件；

//不过这种情况下采用多条WHERE子句性能会更好

SELECT 列1,列2,列3，
FROM 表名
WHERE 列2某条件 
  OR 列3某条件；
  • 在单个查询中从不同表返回类似结构的数据⭐

2）UNION从查询结果里自动去除重复行，如果要保留，则使用UNION ALL

3）在使用UNION组合查询排序时，只能使用一条ORDER BY子句，且必须出现在最后一条SELECT语句之后

Part4 函数

- CONCAT() 拼接函数
将不同列的值拼接输出，组成元素用逗号分隔，一般包括列名和字符串，字符串用引号括住

SELECT CONCAT(列1,'字符串’,列2,'字符串')
FROM 表名

- 文本处理函数
字符串位置从1开始，行从0开始
1）返回/截取字符

  • LEFT(字符串/列，Y) ：从字符串/列中的左边开始，返回Y个字符
  • RIGHT(字符串/列，Y) ：从字符串/列中的右边开始，返回Y个字符
  • SUBSTRING(字符串/列，X，Y)：从字符串/列中第X个字符开始，返回Y个字符，Y不写，默认到尾
    负数意为倒数数的位置
  • SUBSTRING_INDEX(字符串/列，关键词，Y)：
    从字符串/列中，从头开始到以第Y个关键词之间的字符（不包括第Y个关键词）；
SELECT SUBSTRING_INDEX('qin1qin2qin3qin4','qin',2);

输出结果为：qin1

如果Y为负数，则反过来，从末尾开始:

SELECT SUBSTRING_INDEX('qin1qin2qin3qin4','qin',-2);

输出结果为：3qin4

如果关键词不在搜索字符串/列里，则返回整个字符串:

SELECT SUBSTRING_INDEX('qin1qin2qin3qin4','haha',2);

输出结果为：qin1qin2qin3qin4

2）去空格

LTRIM() : 消除左边空格
RTRIM(): 消除右边空格
TRIM(): 消除左右空格

3）大小写

LOWER(): 转小写
UPPER(): 转大写

4）返回串长度
LENGTH()

5）返回串的相似发音值
SOUNDEX()

SELECT SOUNDEX(列1) = SOUNDEX('字符串'）
FROM 表名；

- 日期时间处理函数
时间模式默认yyyy-mm-dd hh:mm:ss

1）获取当前时间

  • NOW()：获取当前时间（执行开始的时间）
    // SYSDATE(): 获取执行时的动态值

  • CURDATE()：获取当前的年月日

  • CURTIME()：获取当前的时分秒

2）时间格式转换

  • 字符串转换为标准格式，用占位符来表示时间元素实际位置，从而识别再转换成标准格式
    STR_TO_DATE('时间字符串/列'，'位置对应的时间占位符‘)

日期格式识别
%Y: 年，4位； %y： 年，2位
%M: 英文月份； %m：时间月份
%D：带英文后缀的日期；%d：天，数值
%H：24小时制； %h 或 %l：12小时制
%i：分
%s：秒
%T：显示时分秒，24小时制
%r：显示时分秒，12小时制
%j：显示当前日期是今年的第几天

SELECT STR_TO_DATE('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); --2008-08-09 08:09:30

  • 日期转换为特殊字符串形式
    DATE_FORMAT(日期，指定字符串格式)

SELECT DATE_FORMAT('2020-02-03 13:45:06.676','%Y-%M-%D %I:%i:%S-%T') ; --2020-February-3rd 01:45:06-13:45:06

  • 提取时间信息
    DATE()：获取日期年月日
    TIME()：获取时间时分秒
    YEAR(); MONTH(); DAY(); HOUR(); MINUTE(); SECOND();
    QUARTER()：获取季度
    WEEK()：获取周
    DAYOFYEAR()：日期在年度中第几天
    DAYOFMONTH()：日期在月度中第几天
    DAYOFWEEK()：日期在周中第几天，周日为第一天
    WEEKDAY()：日期在周中第几天，周日为第一天
    YEARWEEK()：年和周
    DAYNAME()：返回英文星期
    MONTHNAME()：返回英文月份
    LAST_DAY()：返回月份中最后一天

3）日期运算

  • 现有日期运算
    DATE_SUB(日期，INTERVAL 偏移量 时间类型)
    DATE_ADD(日期，INTERVAL 偏移量 时间类型)

日期里应该要包含时间类型，时间类型和偏移量格式要一致

【时间类型包括】：
A.单独元素： YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、WEEK、QUARTER
B.组合类型：单独元素_单独元素，如YEAR_MONTH（年到月）DAY_SECOND（天到秒）

SELECT DATE_SUB('1998-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);
--偏移天到秒，1天1小时1分1秒：1997-12-30 22:58:59

  • 两个日期之间的运算
    A.DIFF
    DATE_DIFF(date1,date2)：两个日期之间的天数差，date1-date2
    TIME_DIFF(time1,time2)：两个日期之间的时间差，返回time格式
    比较日期的格式需相同，date1是结束，date2是开始

B.TIMESTAMPDIFF()函数
TIMESTAMPDIFF(时间类型，开始，结束)
//开始、结束必须是date/datetime格式
//时间类型包括：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、WEEK、QUARTER

SELECT TIMESTAMPDIFF(year,'2002-05-01','2001-01-01');-- -1

- 数据处理函数
ABS()：返回绝对值
COS()：返回一个角度的余弦
EXP()：返回指数值
MOD(X,Y)：返回X除以Y的余数
PI()：返回圆周率
RAND()：返回一个随机数
SIN()：返回一个角度的正弦
SQRT()：返回一个数的平方根
TAN()：返回一个角度的正切

- 聚集函数

  • AVG()：平均值，忽略NULL
  • COUNT()：计数
    //COUNT(*)：记数所有行，不管表列中包含的是NULL还是非空值
    //COUNT(列)：对特定列中具有值的进行记数，忽略NULL
  • MAX()：最大值，忽略NULL
    在用于文本数据时，如果数据按相应的列排序，则返回最后一行
  • MIN()：最小值，忽略NULL
    在用于文本数据时，如果数据按相应的列排序，则返回最前一行
  • SUM()：求和

Part5 其它

A.视图

视图：虚拟的表，简化SQL语句，便于检索

CREATE VIEW 视图名 AS
SELECT 语句；

SELECT 语句
FROM 视图名；

// SHOW CREATE VIEW 视图名；→ 可用来查看创建视图的语句
// DROP VIEW 视图名；→ 删除视图

B.事务处理

1）事务：必须成批执行的MySQL操作
2）标准特性：
a.原子性（atomicity）
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

b.一致性（consistency）
数据库总是从一个一致性的状态转换到另一个一致性的状态。

c.隔离性（isolation）
通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。

d.持久性（durability）
一旦事务提交，则其所做的修改会永久保存到数据库。

3）表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎

4）命令语句

  • 开启事务
    START TRANSACTION;

  • 提交事务
    COMMIT;
    事务不能隐形提交，需明确命令

  • 回滚事务
    ROLLBACK；
    撤销SQL语句，能处理INSERT、UPDATE、DELETE语句，不能回退SELECT、DROP、CREATE语句

  • 保留点
    SAVEPOINT 占位符名1；
    SQL语句；
    ROLLBACK TO 占位符1；
    创建占位符来支持回退部分事务处理

5）更改SQL语句的默认提交
SET autocommit=0;
直至为1（真）才提交更改

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,332评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,508评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,812评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,607评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,728评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,919评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,071评论 3赞 410
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,802评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,256评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,576评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,712评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,389评论 4赞 332
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,032评论 3赞 316
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,798评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,026评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,473评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,606评论 2赞 350