MySQL

目录

MySQL概念及特点

SQL简介

举例说明设计表的时候如何选择合适的数据类型？

如何选择MySQL常用的存储引擎？

MySQL8以上版本的四大排名函数

union和full join的区别

拓展知识

---

MySQL概念及特点

MySQL是一种常用的数据库管理系统。我们选择MySQL的原因是它是免费的、开放源码的数据库，具有跨平台性，功能强大，使用方便。

SQL简介

structured query language 结构化查询语言，数据库管理系统通过sql语言来管理数据库中的数据。

组成：

DDL(data defination language) 数据定义语言，包括对数据库、表结构的操作，有DROP,CREATE,ALTER等

DML(data manipulation language) 数据操作语言，包括对表数据的增删改，有INSERT,DELETE,UPDATE等

DQL(data query language) 数据检索语言，有SELECT

DCL(data control language) 数据控制语言，主要用于控制用户的访问权限，有GRANT,REVOKE,COMMIT,ROLLBACK等

举例说明设计表的时候如何选择合适的数据类型？

创建一个学员表

-- 创建学员表(user)
-- 编号 id
-- 用户名 username
-- 年龄 age
-- 性别 sex
-- 邮箱 email
-- 地址 addr
-- 生日 birth
-- 薪水 salary
-- 电话 tel
-- 是否结婚 married
-- 注意：当需要输入中文的时候，需要临时转换客户端的编码方式
-- SET NAMES GBK;
-- 字段注释 通过COMMENT 注释内容 给字段添加注释
CREATE TABLE IF NOT EXISTS `user`(
id SMALLINT,
username VARCHAR(20),
age TINYINT,
sex ENUM('男','女','保密'),
email VARCHAR(50),
addr VARCHAR(200),
birth YEAR,
salary FLOAT(8,2),
tel INT,
married TINYINT(1) COMMENT '0代表未结婚，非0代表已婚'
)ENGINE=INNODB CHARSET=UTF8;

学员表里的学员有几百个，id字段选整数类型中的SMALLINT。

人名是字符串类型，长度是不固定的，但是不会超过20个字符，username字段选变长字符串类型VARCHAR(20)，占用空间小

1. CHAR定长字符串，占用空间大，速度快。CHAR在保存的时候，后面会用空格填充到指定的长度，在检索的时候后面的空格会去掉。
2. VARCHAR变长字符串，占用空间小，速度慢。VARCHAR在保存的时候，不进行填充。当值保存和检索时尾部的空格仍保留
3. TEXT类型是一种特殊的字符串类型。只能保存字符数据，而且不能有默认值。
4. 它们三个存储和检索数据的方式都不一样，数据检索的效率CHAR>VARCHAR>TEXT

人的年龄是整数型，学员的年龄肯定不超过100岁，age字段选TINYINT

性别是字符串型，sex字段选枚举类ENUM并列出枚举值

邮箱和地址都是长度可变的字符串，字符长度分别选择50和200，即email字段选VARCHAR(50)，addr字段选VARCHAR(200)

生日是日期型，这次选择只保存近期年份（公元1901-2155年），占用空间小，即birth字段选YEAR。其他日期型的原理是通过整型保存时间戳，所以占用空间大。

月薪是浮点类型，学员刚工作，最多赚到六位数的月薪，保留小数点后两位，salary字段选FLOAT(8,2)

1. 如果要表示小数，只能用浮点类型，整数类型不能表示小数
2. 浮点类型DOUBLE精度比FLOAT类型高，如果需要精确到10位以上，就应该选择DOUBLE类型
3. 对于精度要求较高的数据，需要使用定点数（DECIMAL）存储，因为定点数内部是以字符串形式存储的

电话号码通常是11位数的整数，Tel字段选INT

是否已婚就是布尔型，married字段选TINYINT(1)，其值只有0和1

如何选择MySQL常用的存储引擎？

同一个数据库中可以使用多种存储引擎的表，创建数据表的时候可以指定引擎。MySQL常用的存储引擎有InnoDB、MyISAM、MEMORY，默认引擎是InnoDB.

设计表的时候

如果对事务处理要求较高，就选InnoDB；

如果要求查询速度和写入速度比较快，就选MyISAM；

如果只是个临时表，用完释放掉，就选MEMORY

MySQL8以上版本的四大排名函数

排名函数() over(partition by 字段名1 order by 字段名2) 字段名3

ROW_NUMBER：排序数字连续且唯一，比如12345

RANK：排序数字跳跃，并列值序号相等，比如12245

DENSE_RANK：排序数字连续，并列值序号相等，比如12234

NTILE：根据参数指定的值将数据平分排序，比如

select NTILE(2) OVER(order by e.salary desc) as row_num , e.salary from employee e；

类似需求：统计cookie前1/3天的pv数有多少？

思路：前1/3天，可以使用ntile(3)分成三片，取ntile的值为1的pv进行sum

SELECT
	t.id,
	sum( t.pv ) spv 
FROM
	( SELECT id, crtime, pv, ntile ( 3 ) over ( PARTITION BY id ORDER BY crtime ) nt3 FROM nt ) t 
WHERE
	t.nt3 = 1 
GROUP BY
	t.id;

id	spv
cookie1	13
cookie2	10

union和full join的区别

union与union all 都是行合并，前者去重，后者不去重，合并后列数不变，行数变多

full join 是列合并，合并后列数变多，无法匹配的显示为空

拓展知识

1.开启输出日志的命令：\T 日志路径

2.创建数据库 CREATE DATABASE `baishi` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */

CHARACTER SET是字符集，COLLATE是排序的规则

参考资料 MYSQL中的COLLATE是什么

               软件开发没看上去那么简单，从utf8mb4_0900_ai_ci说起