Mysql-基础

目录

基础简述

数据库和SQL

---

基础简述

MySQL 教程 | 菜鸟教程，菜鸟教程是入门必备知识，下面是各种去除掉解释的语法简述。

登录：mysql -u root -p

创建数据库：CREATE DATEBASE 数据库名

删除数据库：DROP DATEBASE 数据库名

选择数据库：USE 数据库名

创建数据表：CREATE TABLE IF NOT EXISTS '表名' (

                        'id' INT UNSIGNED AUTO-INCREMENT,

                        'name' VARCHAR(100) NOT NULL,

                        'date' DATE,

                        PRIMARY KEY('id')

                        )ENGINE = InnoDB DEFAULT CHARSET=utf8

修改表名：RENAME TABLE 表名 TO 新表名

增加字段：ALTER TABLE 表名 ADD COLUME 列名 类型(长度)

修改字段：ALTER TABLE 表名 MODIFY 列名 类型(长度) 约束

修改字段名：ALTER TABLE 表名 CHANGE 旧列名 新列名 类型(长度) 约束

删除字段：ALTER TABLE 表名 DROP COLUME 列名

删除数据表：DROP TABLE 表名

插入数据：INSERT INTO 表名 (name,date) VALUES ("mysql",NOW())

删除数据：DELETE FROM 表名 WHERE 条件表达式

                  TRUNCATE TABLE 表名

更改数据：UPDATE 表名 SET 字段='更改值'

查询数据：SELECT * FROM 表名

过滤数据：SELECT * FROM 表名 WHERE 约束条件

UNION：减少匹配次数，将结果集进行合并，要求两个结果集的列数相同

算术运算符：+ - * /   （若计算时有NULL，则结果全为NULL）

比较运算符：= <> > < >= <=   （判断为NULL的字段时不用=，而是谓词，语法：字段 IS NULL）

逻辑运算符：NOT AND OR ()

去重：DISTINCT 字段

排序：SELECT * FROM 表名 ORDER BY 排序字段 DESC        (ASC是升序，默认)

分组：SELECT 字段 FROM 表名 GROUP BY 聚合键,聚合键 HAVING 分组条件

        聚合键包含NULL时作为特定的一组数据，结果会以“空行”显示

        常见错误：1、在SELECT子句中书写了多余的列（多余的列和聚合键会出现多对一的情况）

                        2、在GROUP BY子句中写了列的别名

                        3、在WHERE子句中使用聚合函数（只有SELECT和HAVING中可以有聚合函数）

视图：视图就是保存好的SELECT语句

        限制：1、定义视图时不能使用ORDER BY子句，因为和表一样数据行是没有顺序的

                2、可以对视图进行增删改，前提是视图满足以下条件：

                        SELECT未使用DISTINCT

                        FROM只有一张表

                        未使用GROUP BY、HAVING

创建视图：CREATE VIEW 视图名称 (视图列名1,视图列名2) AS SELECT语句

删除视图：DROP VIEW 视图名称 CASCADE        （若有关联视图时要加上CASCADE）

子查询：一张一次性视图；

                标量子查询就是只返回一行一列的结果；

                关联子查询就是子查询里使用了外部查询包含的列，子查询与外部查询同步所以关联子查询有时也会被称为同步查询，有结合条件，类似GROUP BY进行了分组；

函数有200多种，可参考MySQL 函数 | 菜鸟教程，可分为5类：算术函数、字符串函数、日期函数、转换函数、聚合函数

聚合函数：COUNT，不计算为NULL的数据，统计行数可以用COUNT(*)；

                  SUM，AVG，不计算为NULL的数据；MAX，MIN，可以计算日期

算术函数：绝对值ABS，求余MOD，四舍五入ROUND

字符串函数：拼接CONCAT(str1,str2,str3)，长度LENGTH，小写LOWER，大写UPPER，替换REPLACE(对象字符串,被替换的部分,替换后的部分)，截取SUBSTRING(对象字符串 FROM 截取起始位置 FOR 截取的字符数)

日期函数：当前日期CURRENT_DATE，当前时间CURRENT_TIME，当前日期和时间CURRENT_TIMESTAMP，截取日期元素EXTRACT(日期元素 FROM 日期)

转换函数：类型转换CAST(要转换的值 AS 想要转换的类型)，COALESCE(数据1，数据2，...)返回第一个不是NULL的值，可处理空值为其他值

谓词：返回值是真值的函数

• 模糊查询，中间有str：LIKE '%str%'，str开头接任意1个字符：LIKE 'str_'
• 范围查询 BETWEEN 100 AND 1000   (包含了100和1000的临界值)
• 判断是否空 IS NULL
• IN(值1,值2,值3)
• EXISTS判断是否满足某种条件的记录，后面跟的是一条数据

CASE：CASE WHEN 求值表达式 THEN 表达式

                                    求值表达式 THEN 表达式

                                    ......

                                    ELSE 表达式

              END

表的并集：UNION，用在两个SELECT语句之间，对结果集进行∪运算。注意事项：

• 作为运算对象的记录的列数必须相同
• 作为运算对象的记录中列的类型必须一致
• 可以使用任何SELECT、WHERE等语句，但ORDER BY子句只能在最后使用一次
• UNION会去除重复行，若想保留重复行可以使用UNION ALL

表的交集：INTERSECT，对结果集进行∩运算，语法和注意事项与UNION一样

表的差集：EXCEPT，对结果集进行差集运算，去除EXCEPT之后的结果集

窗口函数：<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>)

                *[]中的内容可以省略，PARTITION BY设定排序对象的范围

能够作为窗口函数的有聚合函数和专用窗口函数RANK、DENSE_RANK、ROW_NUMBER；

专用窗口函数：

1. RANK：3条记录排在第1位时，排序1、1、1、4
2. DENSE_RANK：3条记录排在第1位时，排序1、1、1、2
3. ROW_NUMBER：3条记录排在第1位时，排序1、2、3、4

聚合函数一般计算累计值

可以在ORDER BY 后面加语句移动框架窗口，

1. ROW 2 PRECEDING：自身加前2行
2. ROW 2 FOLLOWING：自身加后2行
3. BETWEEN 1 PRECEDING AND 1 FOLLOWING：自身加前后各1行

ROLLUP(字段) 运算符在 GROUP BY 后面增加（在MySQL中语法：GROUP BY 字段 WITH ROLLUP）

此时得到GROUP BY()和GROUP BY(字段)的汇总结果，若ROLLUP(字段1,字段2)，则得到GROUP BY()、GROUP BY(字段1)、GROUP BY(字段1,字段2)的汇总结果

GROUPING运算符可查询ROLLUP后的聚合字段

CUBE与ROLLUP语法相同，会得到GROUP BY()、GROUP BY(字段1)、GROUP BY(字段2)、GROUP BY(字段1,字段2)的汇总结果

声明外键：ALTER TABLE 从表 ADD [CONSTRAINT 外键名称] foreign key(从表外键字段名) REFERENCES 主表(主表的主键)

删除外键：ALTER TABLE 从表 DROP foreign key 外键名称

创建索引：CREATE INDEX 索引名 ON 表名(列名)

添加索引：ALTER TABLE 表名 ADD INDEX 索引名(列表)

删除索引：DROP INDEX 索引名 ON 表名

关系型数据库6种范式：1NF、2NF、3NF、巴斯-科德范式(BCNF)、4NF、5NF(完美范式)

1NF：第一范式每一列不可再拆分，称为原子性

2NF：第二范式是在第一范式的基础上，所有的列完全依赖与主键列

3NF：第三范式是在第二范式的基础上，所有的列都和主键列直接相关

事务特性：ACID

原子性Atomicity：事务是最小的执行单位，不允许分割

一致性Consistency：确保从一个正确的状态，转换到另一个正确的状态

隔离性Isolation：并发访问数据库时，一个用户的事务是独立的，不会被其它事务所干扰

持久性Durablilty：一个事务被提交后，对数据库中数据的改变是持久的

进阶

ALL运算符必须以比较运算符开头，例如：>，>=，<，<=，<>，=，后跟子查询

条件	描述
c > ALL(…)	c列中的值必须大于要评估为true的集合中的最大值。
c >= ALL(…)	c列中的值必须大于或等于要评估为true的集合中的最大值。
c < ALL(…)	c列中的值必须小于要评估为true的集合中的最小值。
c <= ALL(…)	c列中的值必须小于或等于要评估为true的集合中的最小值。
c <> ALL(…)	c列中的值不得等于要评估为true的集合中的任何值。
c = ALL(…)	c列中的值必须等于要评估为true的集合中的任何值。

数据库和SQL

数据库（Database，DB）数据库管理系统（Database Management System，DBMS）

DBMS分为5类：

• 层次数据库（Hierarchical Database，HDB）：最古老的数据库之一，树状结构，曾经主流
• 关系数据库（Relational Database，RDB）：现最广泛的数据库，1969年诞生，采用行和列组成的二维表管理数据，有专门的SQL（Structured Query Language，结构化查询语言）来操作。RDBMS有Oracle Database、SQL Server、DB2、PostgreSQL、MySQL。RDBMS最常见的系统结构是客户端/服务器类型（C/S类型）
• 面向对象数据库（Object Oriented Database，OODB）：以对象为单位进行管理
• XML数据库（XML Database，XMLDB）：以XML形式来处理数据
• 键值存储系统（Key-Value Store，KVS）：一种单纯用来保存查询所使用的的主键和值的组合的数据库

SQL分为4类：

• DDL: 数据定义语言，用来定义数据库对象（数据库、表、字段）
• DML: 数据操作语言，用来对数据库表中的数据进行增删改
• DQL: 数据查询语言，用来查询数据库中表的记录（DQL可归纳为DML）
• DCL: 数据控制语言，用来创建数据库用户、控制数据库的控制权限、

SQL书写规则：

• 以 ；结尾
• 关键字不区分大小写
• 单词需要用半角空格或换行来分隔
• 常数的书写方式是固定的