MySQL 人工智能 数据分析

MySQL完整梳理汇总

Posted by Joey on February 11, 2022

mysql教程简介

  • 开放源代码的关系型数据库管理系统。由瑞典MySql AB公司1995开发。

数据库的概述

  • 持久化persistence

数据库的概念

  • DB：数据库（database）
  • DBMS：数据库管理系统（database management system）通过DBMS操作数据库
  • SQL：结构化查询语言（structured query language）专门用来数据库通信的语言。

数据库和数据库系统的关系

  • 通过TCP/IP协议，服务器端口号 访问对应的Mysql服务器

常见的数据库管理系统排名

  • 三四百家
    1. Oracle、 2. MySql 主流的两家。
  • MySql被Oracle收购

为什么选择MySql

  1. 开源代码，成本低
  2. 性能卓越，服务稳定
  3. 软件体积小，使用简单，易于维护
  4. 历史悠久，社区用户活跃
  5. 许多互联网公司使用

Oracle vs MySql

  • Orcale适合大型跨国企业，对收费不敏感，对性能有高要求
  • MySql体积小、速度快、成本低

RDBMS（关系型） 和 非RDBMS（非关系型）

  • 非RDBMS： 文档型数据库 搜索引擎数据库 Solr、Elasticsearch、Splunk 列式数据库 关系型数据库属于行存储，列式数据库属于列存储。 列式数据库的好处：降低IO，适合分布式文件系统 图形数据库

ORM思想（Object Relational Mapping）对象关系映射

数据库中的一个表 == Java/Python的一个类 表中的一条数据 == 类中的一个对象/实体 表中的一个列 == 类中的一个字段（field）、属性（attribute）

表、记录、字段

E-R（entity-relationship，实体-联系）模型中有三个主要概念是：实体集、属性、联系集 一个实体集（class）对应于数据库中的一个表（table），一个实体（instance）则对应于数据库表中的一行（row），也称为一条记录（record），一个属性（attribute）对应于数据库表中的一列（column），也称为一个字段（field）

SQL分类

  • DDL（data definition languages、 数据定义语言）：create、drop、alter、rename、truncate
  • DML（data manipulation languages、 数据操作语言）：insert、delete、update、select（重中之重）
  • DCL（data control language、 数据控制语言）：grant、revoke、commit、rollback、savepoint

学习技巧：大处着眼、小处着手。

SQL语言规则和规范

基本规则

  • 各子句分行写，要注意锁进
  • 每行命令要用/g 或者 /G 用在终端操作
  • 关键字不能被缩写也不能被分开
  • 关于标点符号

如何导入数据

  • 方式一： source 文件的全路径名
  • 方式二：基于具体的图形化界面工具导入数据。

select * from xxx

  • select 列名1，列名2，列名3… from xxx

列的别名 （as: 全称： alias（别名））/ 可以使用双引号定义别名

  1. select 列名1 别名，列名2，列名3… from xxx
  2. select 列名1 as 别名，列名2，列名3… from xxx
  3. select 列名1 “别名“，列名2，列名3… from xxx

去重复行

  • 查询有有哪几类id
  • select distinct 列名 from xxx;

空值参与运算

  • 空值： null
  • null不等同于0，’’, ‘null’
  • 空值参与运算: IFNULL(num, 0): select employee_id, salary “月工资”, salary * (1 + IFNULL(commission_pct, 0)) * 12 “年工资” From employees;

着重号``

  • 一些单词和数据库名称重复，需要用着重号
  • 例如： SELECT * FROM ORDER;

查询常数

  • SELECT 常数名称 employee_id, last_name FROM XXX;
  • 出现在表头位置，用常数表示表的代表意义

显示表结构

  • DESCRIBE employees; #显示了表中字段的详细信息
  • DESC employees; 缩写写法

过滤数据

  • 查询90号员工的信息 
    SELECT * FROM XXX
过滤调节 WHERE last_name = 'joey';

运算符

  • 算数运算符： +、 -、 *、/或div、%或MOD
  • 取模运算：%或MOD
  • 比较运算符：=、 <=>、>、 <、！=、<=、>=、
  • 只要有NULL参与判断，结果就为NULL
  • 安全等于 <=> 用于判断NULL
  • IS NULL、IS NOT NULL、ISNULL()
  • LEAST()最小的、GREATEST()最大的
  • BETWEEN…AND… 区间
  • OR 并且两种条件
  • IN (set)和 NOT IN (set) 查找离散值：WHERE XXX = 10 OR XXX = 20 OR XXX = 30 相当于 WHERE XXX IN (10, 20 ,30)
  • LIKE 模糊查询；% 代表不确定个数的字符：WHERE XXX LIKE “%a%”;
  • LIKE 模糊查询；% 代表不确定个数的字符：WHERE XXX LIKE “a%”; // 查询以字符a开头的信息
  • LIKE 模糊查询；% 代表不确定个数的字符：WHERE XXX LIKE “%a%” AND XXX LIKE “%e%”; // 查询又包含a又包含e
  • LIKE 模糊查询；% 代表不确定个数的字符：WHERE XXX LIKE “%a%e%”; // 查询又包含a在前e在后的信息
  • _ ： 代表一个不确定的字符
  • 查询第二个字符是a的：WHERE XXX LIKE “_a%”;
  • 如果出现搜索字符对应位置是_，干扰查询，需要用转义字符\：WHERE XXX LIKE “__a%”;
  • 这则表达式REGEXP / RLIKE
  • XOR： “异”, 两个判断一个真一个假的情况下输出信息;
  • ()优先级最高

位运算符

  • & 、 ｜ 、^ 、 ～ 、 》 、 《

排序与分页

ORDER BY实现排序操作

  • 升序：ASC
  • 降序：DESC
  • SELECT ID FROM XXX ORDER BY salary ASC;
  • 声明：WHERE 需要在ORDER BY前面

二级排序

  • SELECT empolyee_id,salary,department_id FROM empolyees ORDER BY empolyee_id DESC, salary ASC;

LIMIT 实现分页

SELECT empolyee_id,salary,department_id 
FROM empolyees
LIMIT (pageNo - 1) * pageSize, pageSize
  • 只想展示32，33条数据 
    SELECT empolyee_id,salary,department_id 
FROM empolyees
LIMIT 31, 2
  • LIMIT…OFFSET… 只想展示32，33条数据 
    SELECT empolyee_id,salary,department_id 
FROM empolyees
LIMIT 2 OFFSET 31

多表查询

  • 笛卡尔积
  • 正确的多表查询 
    SELECT employee_id, department_name
FROM employees, departments
## 两个表的连接条件
WHERE employees.`employee_id` = departments.`department_id`;
  • 如果查询语句中出现了多个表都存在的字段，则必须指明此字段所在的表 
    SELECT employee_id, department_name, employees.department_id
FROM employees, departments
WHERE employees.`employee_id` = departments.`department_id`;

  • 建议：从sql优化的角度来看，建议多表查询时，每个字段名称前都表明所在对应的表。

  • 当查询条件过多，sql的可读性变差，需要在SELECT 和 WHERE 中使用表的别名, 一旦使用的表别名，一定要使用表的别名，不能使用原名 
    SELECT emp.employee_id, dep.department_name, emp.department_id
FROM employees emp, departments dep
WHERE emp.`employee_id` = dep.`department_id`;

演绎式 VS 归纳式

  • 演绎式：提出问题1 —-> 解决问题1; 提出问题2 —-> 解决问题2
  • 归纳式：总 — 分

多表查询的分类

  • 角度一：等值连接 VS 非等值连接
  • 角度二：自连接 VS 非自连接
  • 角度三：内连接 VS 外连接

等值连接 VS 非等值连接

  • 等值 =
  • 非等值 !=、>、 < …

自连接 VS 非自连接

  • 自连接：将一张表声明成两张表进行自查询
  • 非自连接：两张表进行自查询

内连接 VS 外连接

  • 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
  • 外连接：合并具有同一列的两个以上的表的行，结果集中包含一个表与另一个表匹配的行以外，还查询到了左表 或 右表 不匹配的行
  • 外连接分类：左外连接、右外连接、满外连接 
    ## 内连接练习：
## 查询`所有信息`的last_name、department_name信息
SQL99语法实现内连接：
SELECT last_name、department_name, city
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN localtion l
ON d.location_id = l.location_id 

## SQL99语法实现左外连接练习：
SELECT last_name、department_name, city
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN localtion l
ON d.location_id = l.location_id 

## SQL99语法实现右外连接练习：
SELECT last_name、department_name, city
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN localtion l
ON d.location_id = l.location_id 

## 满外连接不支持 FULL OUTER JOIN
## SQL99语法实现满外连接练习：
SELECT last_name、department_name, city
FROM employees e FULL JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN localtion l
ON d.location_id = l.location_id

UNION 和 UNION ALL的使用

  • UNION：会执行去重的操作
  • UNION ALL：不会去重操作
  • 结论： 如果明确知道合并数据后不存在重复数据，或者不需要去重复数据，则尽量使用UNION ALL语句，以提高效率

7种JOIN

  • 内连接：JOIN （中间重复2次)
  • 左外连接：LEFT JOIN
  • 右外连接：RIGHT JOIN
  • 左外减内连：先JOIN，再WHERE d.department_id IS NULL;
  • 右外减内连：先JOIN，再WHERE e.department_id IS NULL;
  • 满连接：左外减内连 UNION ALL 右外连接 / 右外减内连 UNION ALL 左外连接
  • 满外连接：左外减内连 UNION ALL 右外减内连

自然连接

  • 自然连接： 它会帮你自动的连接2张表中所有相同的字段; NATURAL JOIN

USING 连接

  • USING：连接合并两个表中相同的字段 例如：JOIN…ON e.department_id = d.department_id; 可以用USING：JOIN…ON USING(department_id)

函数

  • 函数的作用，把我们经常使用的代码封装起来，需要的时候直接调用。Function(),数学中的y=f(x)
  • 内置函数 / 自定义函数
  • 单行函数（输入1个输出1个） / 多行函数（输入多个输出1个）

数值函数

基本函数

  • ABS（x）返回x的绝对值
  • SIGN（x）正数返回1，负数返回-1，0返回0
  • PI（）圆周率的值
  • CEIL（x），CEILING（x）返回大于或等于某个值的最小整数
  • FLOOR（x）返回小于或等于某个值的最大整数
  • LEAST（e1， e2，e3…）返回列表中的最小值
  • GREATEST（e1， e2，e3…）返回列表中的最大值
  • MOD（x，y）返回x除以y以后的余数
  • RAND（）返回0-1的随机值
  • RAND（x）返回0-1的随机值，其中x的值用作种子值，相同的x值会产生相同的随机数
  • ROUND（x）返回一个对x的值进行四舍五入后，最接近x的整数
  • ROUND（x，y）返回数字x截断为y位小数的结果。比如：ROUND（1.34，1）保留一位小数=1.3
  • TRUNCATE（x，y）截断，比如：ROUND（1.54，0）保留小数前=1
  • SQRT（x）返回x的平方根。当x的值为负数时，返回NULL。

三角函数

  • SIN（x）返回x的正弦值，其中，参数x为弧度值。
  • ASIN（x）返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL。
  • COS（x）返回x的余弦值，其中，参数x为弧度值。
  • ACOS（x）返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL。
  • TAN（x）返回x的正切值，其中，参数x为弧度值。
  • ATAN（x）返回x的正切值，即返回正切值为x的值。
  • ATAN2（m，n）返回两个参数的反正切值。
  • COT（x）返回x的余切值，其中，x为弧度值。

角度与弧度互换函数

  • RADIANS（x）将角度转化为弧度，其中，参数x为角度值。
  • DEGREES（x）将弧度转化为角度，其中，参数x为弧度值。

指数与对数

  • POW（x，y）表示x的y次方
  • POWER（x， y）表示x的y次方
  • EXP（x）返回e的x次方，其中e是一个常数，2.718281828459045
  • LN（x），LOG（x）返回以e为底的x的对数，当x <= 0时，返回的结果为NULL
  • LOG10（x）返回以10为底的x的对数，当x <= 0时，返回的结果为NULL
  • LOG2（x）返回以2为底的x的对数，当x <= 0时，返回的结果为NULL

进制间的转换

  • BIN（x）返回x的二进制编码
  • HEX（x）返回x的十六进制编码
  • OCT（x）返回x的八进制编码
  • CONV（x，f1，f2）返回f1进制数变成f2进制数

字符串函数

  • ASCII（s）返回字符串s中的第一个字符的ASCII码值
  • CHAR_LENGTH(s) 返回字符串s的字符数，作用与CHARACTER_LENGTH(s)相同
  • LENGTH（s）返回字符串s的字节数，和字符集有关
  • CONCAT（s1，s2，s3…,sn）连接s1，s2，…sn为一个字符串
  • CONCAT_WS(x, s1, s2, … sn) 通CONCAT，但是每个字符之间要加上x
  • INSERT（str，idx，len，replacestr）将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
  • REPLACE（str，a，b）用字符串b替换字符串str中所有出现的字符串a
  • UPPER（s）或 UCASE（s）将字符串的s的所有字母转换成大写字母
  • LOWER（s）或 LCASE（s）将字符串的s的所有字母转换成小写字母
  • LEFT（str，n）返回字符串str最左边的n个字符
  • RIGHT（str，n）返回字符串str最右边的n个字符
  • LPAD（str，len，pad）用字符串pad对str最左边进行填充，知道str的长度为len个字符（实现右对齐效果）
  • RPAD（str，len，pad）用字符串pad对str最右边进行填充，知道str的长度为len个字符（实现左对齐效果）
  • LTRIM（s）去掉字符串s左侧空格
  • RTRIM（s）去掉字符串s右侧空格
  • TRIM（s）去掉字符串s开始与结尾的空格
  • TRIM（s1 FROM s）去掉字符串s开始与结尾的s1
  • TRIM（LEADING s1 FROM s）去掉字符串s开始处的s1
  • REPEAT（str，n）返回str重复n次的结果
  • SPACE（n）返回n个空格
  • STRCMP（s1， s2）比较字符串s1，s2的ASCII码值的大小
  • SUBSTR（s，index，len）返回字符串s的index位置其len个字符，作用与SUBSTRING（s，n，len）、MID（s，n，len）相同
  • LOCATE（substr，str）返回字符串substr在字符串str中首次出现的位置，作用于POSITION（substr IN str）、INSTR（str，substr）相同。未找到，返回0；
  • ELT（m, s1, s2, …, sn）返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
  • FIELD（s，s1，s2，…, sn）返回字符串s在字符串列表中第一次出现的位置
  • FIND_IN_SET（s1，s2）返回字符串s1在字符串s2中出现的位置。其中，字符串s2时一个以逗号分隔符的字符串
  • REVERSE（s）返回s反转后的字符串
  • NULLIF（value1， value2）比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

日期和时间函数

  • CURDATE(), CURRENT_DATE() 返回当前日期，只包含年月日
  • CURTIME(), CURRENT_TIME() 返回当前时间，只包含时分秒
  • NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() 返回当前系统日期和时间
  • UTC_DATE() 返回UTC日期
  • UTC_TIME() 返回UTC时间

日期与时间戳的转换

  • UNIX_TIMESTAMP() 以UNIX时间戳的形式返回当前时间。
  • UNIX_TIMESTAMP(date) 将时间date以UNIX时间戳的形式返回
  • FROM_UNIXTIME(timestamp) 将UNIX时间戳的时间转换为普通格式的时间

获取月份、星期、星期数、天数等函数

  • YEAR(date) / MONTH(date) / DAY(date) 返回具体的日期值
  • HOUR(time) / MINUTE(time) / SECOND(time) 返回具体的时间值
  • MONTHNAME(date) 返回月份：January…
  • DAYNAME(date) 返回星期几: MONDAY, TUESDAY…
  • WEEKDAY(date) 返回周几; 注意：周一是0，周二是1…
  • QUARTER(date) 返回日期对应的季度，范围为1～4
  • WEEK(date), WEEKOFYEAR(date) 返回一年中的第几周
  • DAYOFYEAR(date) 返回一年中的第几天
  • DAYOFMONTH(date) 返回所在月份的第几天
  • DAYOFWEEK(date) 返回周几，注意：周日是1，周一是2

时间和秒钟转换的函数

  • TIME_TO_SEC(time) 将time转化秒并返回结果值
  • SEC_TO_TIME(seconds) 将seconds描述转化为包含小时、分钟和秒的时间

计算日期和时间的函数

  • DATE_ADD(datetime, INTERVAL expr type), ADDDATE(date, INTERVAL expr type) 返回与给定日期时间相差INTERVAL时间段的日期时间。
  • DATE_SUB(date, INTERVAL expr type), SUBDATE(date, INTERVAL expr type) 返回与date相差INTERVAL时间间隔的日期。
  • ADDTIME(time1, time2)
  • SUBTIME(time1, time2)
  • DATEDIFF(date1, date2)
  • TIMEDIFF(time1, time2)
  • FROM_DAYS(N) 从0000年1月1日起，N天以后的日期
  • TO_DAYS(date) 返回日期date距离从0000年1月1日的天数
  • LAST_DAY(date) 返回date所在月份的最后一天的日期
  • MAKEDATE(year, n) 针对指定年份中的天数返回一个日期
  • PERIOD_ADD(time, n) 返回time加上n后的时间

日期的格式化与解析：日期转化为字符串 / 字符串转化为日期

  • DATE_FORMATE(date, fmt)
  • TIME_FIRMATE(time, fmt)
  • GET_FORMATE(date_type, format_type)
  • STR_TO_DATE(str, fmt)

流程控制函数

  • IF(value, value1, value2) 如果value值为true，返回value1， 否则返回value2
  • IFNULL(value1, value2) 如果value1不为NULL，返回value1，否则返回value2
  • CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … （相等于java的if.. else.. ）
  • CASE expr WHEN 常量1 THEN 结果1 WHEN 常量2 THAN 结果2. … END

加密与解密函数

  • PASSWORD(str) 返回字符串str的加密版本，41位长的字符串，加密不可逆
  • MD5(str) 返回字符串str的md5加密后，也是一种加密方式。若参数为NULL，则会返回NULL
  • SHA(str)

MYSQL信息函数

  • SELECT VERSION(), CONNECTION_ID(), DATABASE(), SCHEMA(), USER(), CURRENT_USER(), CHARSET(‘qiaoyu’), COLLATION(‘qiaoyu’) FROM DUAL;

其他函数

  • FORMAT(value, n) 四舍五入小数保留n位
  • CONV(value, from, to) 将value的值进行不同进制之间的转换
  • INET_ATON(ipvalue) 将以点分割的IP地址转化为一个数字
  • INET_NTOA(value) 将数字形式的IP地址转化为以点分割的IP地址
  • BENCHMARKE(n, expr)
  • CONVERT(value USING char_code) 将value所使用的字符编码修改为char_code

聚合函数（多行函数）

  • 自变量可以是多个，但是结果只能是一个
  • 聚合函数作用于一组数据，并对一组数据返回一个值
  • 比如一个表中最大的值是多少

聚合函数

  • 常见的几个聚合函数
    1. AVG / SUM
    2. MAX / MIN
    3. COUNT
    4. 方差
    5. 标准差
    6. 中位数
  • 如果需要统计表中的记录书，使用COUNT()、COUNT(1)、COUNT(具体字段)哪个效率更高？答：COUNT()、COUNT(1)

GROUP BY的使用（分组）

  • 比如查询各个部门的平均工资 
    SELECT department_id, AVG(salary), SUM(salary)
FROM employees
GROUP BY department_id
  • 结论一：SELECT中出现的非组函数的字段必须声明再GROUP BY中。反之，GROUP BY中声明的字段可以不出现在SELECT中。
  • 结论二：GROUP BY 声明在FROM后面、WHERE后面，ORDER BY前面、LIMIT前面
  • 结论三：MySQL中GROUP BY中使用WITH ROLLUP（WITH ROULLUP 把整体数据看成一组）

HAVING 用来过滤数据的

  • 比如查询各个部门中最高工资比10000高的部门信息 
    SELECT department_id, MAX(money) moneys
From employees
GROUP BY department_id
HAVING MAX(moneys) > 10000;
  • 要求1: 如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。否则报错。
  • 要求2: HAVING必须声明在GROUP BY后面
  • 开发中，我们使用HAVING的前提是SQL中使用GROUP BY

HAVING VS WHERE

  • 当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中

  • 当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE或HAVING中都可以。但是大家都声明在WHERE中

  • HAVING使用范围更广
  • WHERE效率更高，WHERE先筛选后关联；HAVING先关联后筛选；

SQL底层执行的原理

  1. SELECT语句的完整结构
    • SELECT （存在聚合函数）
    • FROM…, (LEFT/RIGHT)JOIN …ON …
    • WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
    • GROUP BY
    • HAVING
    • DISTINCT 去重
    • ORDER BY 排序（ASC/DESC）
    • LIMIT
  2. SQL语句的执行过程
  3. SELECT （是否存在聚合函数）
  4. FROM…, (LEFT/RIGHT)JOIN …ON …
  5. WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
  6. GROUP BY
  7. HAVING
  8. DISTINCT
  9. ORDER BY 排序（ASC/DESC）
  10. LIMIT

自查询举例与自查询的分类

子查询

  • 指一个查询语句潜逃在另一个查询语句内部的查询，这个特性从MySQL4.1开始引入 问题：谁的工资比Abel的高？ 答：自连接 
    SELECT e2.last_name, e2.salary
FROM employees e1, employees e2
WHERE e2.`salary` > e1.`salary` # 多表的连接条件
AND e1.last_name = 'Abel'

    优化自查询

    SELECT salary
FROM employees
WHERE salary > ANY(
              SELECT salary
              FROM employees
              WHERE last_name = 'Abel'
              );
  • 自查询的分类
    1. 角度一：从内查询返回结果的条目数 单行子查询 VS 多行子查询
    2. 角度二：内查询时否被执行多次 相关子查询 VS 不相关子查询
      • 比如：相关子查询的需求：查询工资大于本部门平均工资的员工信息 不相关子查询的需求：查询工资大于本公司平均工资员工的信息

单行子查询

  • 单行操作符： = 、 != 、 > 、 < 、 >= 、 <=

多行子查询

  • 多行操作符： IN 、 ANY 、 ALL 、 SOME(同ANY)

关联子查询

  • 结论：除了GROUP BY和LIMIT，其他地方都可以声明子查询；
  • 数据库名，表名不能超过30个字符，限制为29.
  • 必须只能包含A-Z，a-z, 0-9, 共63个字符
  • 数据库名，表名，字段名等对象中间不要包含空格
  • 同一个mysql软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
  • 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用
  • 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就变成字符型了。

创建数据库

  • 方式1: CREATE DATABASE 数据库名;
  • 方式2: CREATE DATABASE 数据库名 CHARACTER SET 字符集;
  • 方式3: CREATE DATABASE IF NOT EXISTS 数据库名;

管理数据库

  1. 查看数据库列表
    • SHOW DATABASES;
  2. 切换数据库
    • USE 数据库名;
  3. 查看当前数据库中保存的数据表
    • SHOW TABLES;
  4. 查看当前使用的数据库
    • SELECT DATABASE() FROM DUAL;
  5. 查看指定数据库下保存的数据表
    • SHOW TABLES FROM 数据表名;

修改数据库（一般不能改）

  1. 更改数据库的字符集
    • ALTER DATABASE 数据库名 CHARACTER SET ‘utf8’;
    • ALTER DATABASE 数据库名 CHARACTER SET ‘gbk’;

数据库改名

  • DATABASE不能改名，一些可视化工具提供的改名是新建数据库，把所有表复制到新库，再删除旧库完成。

删除数据库

  1. DROP DATABASE 数据库名;
  2. DROP DATABASE IF EXISTS 数据库名;

创建表

  1. 方式1: CREATE TABLE [IF NOT EXISTS] 表名( id INT, emp_name VARCHAR(15), ## 使用VARCHAR来定义字符串，必须在使用VARCHAR时指明其长度。 hire_date DATE );

查看表结构

  • DESC 表名;
  • SHOW CREATE TABLE 表名;

查看表数据

  • SELECT * FROM 表名;

基于现有的表创建新表

CREATE TABLE 新表名 AS SELECT 字段名, 字段名, 字段名 FROM 旧表名

修改表

  1. 添加一个字段
    • ALTER TABLE 表名 ADD 字段名 DOUBLE(10,2)
    • 添加到第一个：ALTER TABLE 表名 ADD 字段名 FIRST
    • 放置到某一个后面：ALTER TABLE 表名 ADD 字段名 AFTER 某一个字段
  2. 修改一个字段
    • ALTER TABLE 表名 MODIFY <字段名> <字段新名称> <字段的类型>
  3. 重命名一个字段
    • ALTER TABLE 表名 CHANGE <字段名> <字段新名称> <字段的类型>
  4. 删除一个字段
    • ALTER TABLE <表名> DROP <字段名>

重命名表

  • 方式一：RENAME TBALE 表名 TO 新表名
  • 方式二：ALTER TABLE 表名 RENAME TO 新表名

删除表：不光将表结构删除掉，同时表中的数据也要删除掉，释放空间

  • DROP TABLE IF EXISTS 表名;

清空表：表中数据删除掉，表结构保留

  • TRUNCATE TABLE 表名;

DCL 中的COMMIT 和 ROLLBACK

  • COMMIT：提交数据，一旦执行，数据永久的保存在数据库中，不能回滚。
  • ROLLBACK：一旦执行，可以回滚，回滚到最近的一次COMMIT之后。

对比 TRUNCATE 和 DELETE FROM

  • 相同点：都可以实现对表中数据的删除，同时保留表结构。

  • 不同点：TRUNCATE：一旦执行此操作，表数据全部清除。同时，数据是不可以回滚的。 DELETE FROM：一旦执行操作，表数据可以全部清除。同时，数据是可以实现回滚的。

  • DDL 和 DML的说明 DDL代表数据定义语言。DML代表数据操作语言。DDL语句用于创建数据库、模式、约束、用户、表等。DML语句用于插入、更新或删除记录。
    1. DDL的操作一旦执行，就不可回滚
    2. DML默认情况下，一旦执行，也不可回滚。但是，如果在执行DML之前，执行了 SET autocommit = false; 则执行的DML操作就可以实现回滚; SET autocommit = false 对DDL失效;
  • 因为DDL在执行指令之后会自动执行一次commit，所以不能回滚；

添加数据

  • 一条一条插入
    1. INSERT INTO 表名 VALUE（1，’Tom’, ‘2023-01-01’）;（不推荐）
    2. INSERT INTO 表名(id, name, data) VALUE (1，’Tom’, ‘2023-01-01’); (推荐)
    3. INSERT INTO 表名(id, name, data) VALUES (1，’Tom’, ‘2023-01-01’)(2, ‘xxx’, 2023-01-02)…;
  • 将查询结果插入到表 
    INSERT INTO 表名(id, name, salary)
SELECT id, name, salary 
FROM 查询表名;
  • 添加表中要添加数据的字段长度不能低于查询表中的长度

更新数据

UPDATE... SET ... WHERE ...

UPDATE 更新表名
SET 字段
WHERE id = 5;
  • 同时修改多个字段 
    UPDATE 更新表名
SET 字段1 = '', 字段2 = ''
WHERE id = 5;

删除数据

  • DELETE FROM … WHERE … 
    DELETE FROM 表名
WHERE id = 1;

MYSQL8的新特性：计算列

CREATE TABLE 表名(
    a INT,
    b INT,
    c INT GENERATED ALWAYS AS (a + b) VIRTUAL #c即为计算列
);

INSERT INTO 表名（a, b） VALUE(10, 20)

MYSQL中支持的数据类型

  • 整数类型：TINYINT（-128～127）、SMALLINT（-32768～32767）、MEDIUMINT、INT（或INTEGER）、BIGINT
  • 浮点类型：FLOAT、DOUBLE
  • 定点书类型：DECIMAL
  • 位类型： BIT
  • 日期时间类型： YEAR、TIME、DATE、DATETIME、TIMESTAMP
  • 文本字符串类型: VARCHAR、CHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
  • 枚举类型: ENUM..
  • 集合类型: SET
  • 二进制字符串类型：BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
  • JSON类型：JSON对象、JSON数组
  • 空间数据类型：单值类型 / 集合类型

属性

  • NULL
  • NOT NULL
  • DEFAULT
  • PRIMARY KEY
  • AUTO_INCREMENT 自动递增，适用于整数类型
  • UNSIGNED 无符号，不能为负数
  • CHARACTER SET name 指定一个字符集

本文首次发布于 Joey Blog, 作者 @乔宇(Joey) ,转载请保留原文链接..

CATALOG
    FEATURED TAGS
    iOS Web Vue JavaScript 前端 开发技巧 Debug Obj-C Xcode ReactiveCocoa 函数式编程 开源框架 Runtime 生活 Mac 效率 终端 Git Swift zsh 开源库 ruby shell 健身 React 人工智能 数据分析 python