数据蛙数据分析每周作业

MySQL笔记

2020-09-13  本文已影响0人  QinChu

Part1 基础介绍

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

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）查看表结构

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

6）删除表
DROP TABLE 表名；

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

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

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 子句类型

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

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）内联结/等值联结（交集）

SELECT 列*
FROM 表1，表2
WHERE 表1.主键 = 表2.外键；

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

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）获取当前时间

2）时间格式转换

日期格式识别
%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

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

3）日期运算

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

【时间类型包括】：
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

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()：返回一个角度的正切

- 聚集函数

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）命令语句

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

上一篇 下一篇

猜你喜欢

热点阅读