【大数据开发阶段二】MySQL数据库【持续更新】

本文链接：https://blog.youkuaiyun.com/skyman30/article/details/129223368

SQL 教程_w3cschoolSQL 是用于访问和处理数据库的标准的计算机语言。在本教程中，您将学到如何使用 SQL 访问和处理数据系统中的数据，这类数据库包括：Oracle, Sybase, SQL Server, DB2, Access 等等。_来自SQL 教程，w3cschool编程狮。https://www.w3cschool.cn/sql/MySQL 教程_w3cschool编程狮为您提供关系型数据库管理系统--MySQL教程，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。在本教程中，会让大家快速掌握 MySQL 的基本知识，并轻松使用 MySQL 数据库。什么是数据库？数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。每个数据库都有一个或多.._来自MySQL 教程，w3cschool编程狮。https://www.w3cschool.cn/mysql/

一、基本的SELECT语句

1.SQL 分类

DDL：数据定义语言（操作数据库和表）

DML：数据操作语言（增删改表中的数据）
DQL：数据查询语言（查询表中得数据）
DCL：数据库控制语言（授权）

2.SQL语言的基本规则与规范

2.1基本规则

SQL可以写在一行或多行 ,为提高可读性，各子句分行写，必要时缩进
每条命令以；或\g或\G结束
关键字不能被缩写，也不能分行
字符串、时间日期的数据用单引号‘ ’
列的别名用双引号“ ”

2.2 SQL大小写规范

MySQL 在windows下是大小写不敏感的
MySQL 在Linux下是大小写敏感的

2.3 注释

单行注释： #注释文字（MySQL特有的方式）
单行注释：-- 注释文字（--后面必须有一个空格）
多行注释：/* 注释文字 */

2.4命名规则

http://t.csdn.cn/6mlEMhttp://t.csdn.cn/6mlEM

2.5导入现有数据表、表的数据

http://t.csdn.cn/ORYbchttp://t.csdn.cn/ORYbc

3.基本的SELECT语句

SELECT 字段一、字段二，... FROM 表名; 

# *表明所有字段
SELECT * FROM 表名;

3.1列的别名

as：（全程）alias，可以省略
可以用双引号“ ”

-- n是name的别名
SELECT name AS n FROM data;
SELECT name "n" FROM data;

3.2去除重复行

在字段前面加入DISTINCT

SELECT DISTINCT id FROM data;

3.3空值参与运算

空值：null
null 不等同与 0，‘ ’ 和 ‘null’
空值参与运算结果都为null

3.4着重号

如果出现字段名或表名与关键字重复，需要用 ` `

SELECT * FROM `order`;

3.5查询常数

查询时为每行数据都匹配这个常数

# 每个id前都有jack和456
SELECT ‘jack’,456,id FROM data

4.显示表结构

DESCRIBE，描述字段相关信息

DESCRIBE data;

5过滤数据

使用WHERE关键字，声明在FROM后面，紧挨着。

#查询 id 为 1 的数据
SELECT * FROM data WHERE id = 1;
#查询 name 为 Tom 的数据
SELECT * FROM data WHERE name = 'Tom';

二、运算符

1.算数运算符

加：+ ，减：- ，乘： * ，除： / div 取模： % mod

#注意点：
SELECT 100 + ‘1’ FROM DUAL;    --结果是101
SELECT 100 + ‘a’ FROM DUAL;    --结果是100，不能转为数值的字符串看作是0
SELECT 100 + null FROM DUAL;   --结果是null ，null参与的运算结果都为null
SELECT 100 DIV 0 FROM DUAL;    --结果是null

2.比较运算符

2.1

比较结果为真返回1，为假返回0，其他返回null

等于：= 安全等于：<=> （可以判断null）不等于： <> 和 != 小于：< 小于等于： <= 大于： > 大于等于 >=

2.2

IS NULL ｜ IS NOT NULL ｜ ISNULL

#查询表中id为null的数据
SELECT * FROM data WHERE id IS NULL;
SELECT * FROM data WHERE ISNULL(id);
SELECT * FROM data WHERE id <=> null;

#查询表中id不为null的数据
SELECT * FROM data WHERE id IS NOT NULL;
SELECT * FROM data WHERE NOT id <=> null;

最小值：LEAST() ｜最大值：GREATEST()
SELECT LEAST('a','g',''m),GREATEST('m','y','x','z')
FROM DUAL;

--返回结果为 a , z

BETWEEN 条件1 AND 条件2 (包含边界)

条件1是下界，2是上届
# 查询 id 从1到10的数据
SELECT * FROM data WHERE id BETWEEN 1 AND 10

IN （set）｜ NOT IN （set）

#查询id为1，3，5的数据
SELECT * FROM data WHERE id IN (1,3,5);

#查询id不为1，3，5的数据
SELECT * FROM data WHERE id NOT IN (1,3,5);

LIKE ：模糊查询

#查询name中含有a的数据
SELECT * FROM data WHERE name LIKE ‘%a%’;
-- %代表不确定个数的字符（0个，1个，多个）

#查询name中含有a和b的数据
SELECT * FROM data WHERE name LIKE ‘%a%’ AND  name LIKE ‘%b%‘;

#查询name中第二个字符是a的数据
SELECT * FROM data WHERE name LIKE ‘%_a%’;

#查询name中第二个字符是_的数据
SELECT * FROM data WHERE name LIKE ‘%_\_%’;
-- \是转义字符

正则表达式 REGEXP

http://t.csdn.cn/IRF9Yhttp://t.csdn.cn/IRF9Y

3.逻辑运算符

与：AND 或者 &&

或：OR 或者 ||

非: NOT 或者 !

异或 : XOR

4.位运算符

与：&

或 :｜

异或：^

取反：～

左移：<<

右移：>>

三、排序与分页

1.排序

没有使用排序操作，默认的查询返回结果是按添加数据的先后排序的
使用ORDER_BY对数据进行排序
DESC 降序排序
ASC 升序排序
默认是按升序排序
可以使用列的别名进行排序
列的别名不能用于WHERE中
可以进行多级排序
#查询id和name，用id的升序进行排列
SELECT id,name FROM data ORDER_BY id;
SELECT id,name FROM data ORDER_BY id ASC;

#查询id和name，用id的降序进行排列
SELECT id,name FROM data ORDER_BY id ESC;

2.分页

使用LIMIT实现数据分页

LIMIT （显示页数 -1） * 每页的记录，每页的记录
#每页显示10条，只显示第1页
SELECT * FROM data 
LIMIT 0,10;

#每页显示10条，只显示第2页
SELECT * FROM data 
LIMIT 10,10;
声明顺序：WHERE ---ORDER BY ---- LIMIT(必须放在最后)

新特性：LIMIT 。。。OFFSET。。。。;

四、多表查询

若有n个表实现多表查询，则至少需要n-1个查询条件
如果给表取了别名，在SELECT或WHERE中使用表名的活，只能用别名

1.多表查询分类

1.1角度一

等值连接
非等值连接

1.2 角度二

自连接
非自连接

1.3角度三

内连接
外连接

左外连接

右外连接

满外连接（全连接）

1.4 SQL99语法新特性

自然连接（NATURAL JOIN）：自动查询两张表中的所有相同的字段进行等值连接
USING

五、单行函数

1.数值函数

1.1基本函数

ABS、SIGN、 PI、MOD、CELL、FLOOR、RAND、ROUND、TRUNCATE、SQRT。。。。

1.2角度和弧度互换

RADIANS、DEGREES

1.3 三角函数（参数为弧度值）

SIN、COS、ASIN、ACOS、TAN、ATAN、ATAN2COT

2.字符串函数

ASCII、CHAR_LENGTH、LENGTH、CONCAT、CONCAT_WS、INSERT、REOLACE、UPPER、LOWER、LEFT、RIGHT、LPAD、RPAD、。。。。。

3.日期和时间函数

3.1获取日期和时间

CURDATE、CURTIME、NOW、SYSDATE、UTC_DATE。。。。

3.2日期和时间戳转换

UNIX_TIMESTAMP、FROM_UNIXTIME

3.3获取月份星期等函数

YEAR、MOUTH、MONTHNAME、QUARTER、WEEK。。。。

3.4日期的操作函数

EXTRACT

3.5时间和秒钟的转换

TIME_TO_SEC、SEC_TO_TIME

3.6计算时间和日期的函数

DATA_ADD、DATA_SUB

3.7日期的格式化与解析

DATA_FORMAT、GET_FORMAT。。。

4.流程控制函数

IF、IFNULL 、CASE WHEN 。。。。。 THEN。。。。 ELSE。。。。 END（if else）、

5.加密和解密函数

PASSWORD MD5

六、多行函数（聚合函数）

1.常见的聚合函数

1.1 AVG / SUM

1.2 MAX / MIN

1.3 COUNT

count中不考虑null值

2. GROUP_BY的使用

select中出现的非组函数的字段必须声明在group_by中
group by 声明from后、where后、order by之前、limit之前

3.HAVING的使用

用来过滤数据
过滤条件中使用了聚合函数必须用having代替where
过滤条件中没有使用聚合则既可以用having也可以用where，推荐使用where
having声明在group by 后面
having的前提是使用了groupby

4.SQL底层执行原理

七、子查询

八、创建和管理表

1.创建数据库

方式1:创建数据库
CREATE DATABASE 数据库名;

方式2:创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;

方式3:判断数据库是否存在，不存在则创建数据库（推荐）
CREATE DATABASE IF NOT EXISTS 数据库名;

2.管理数据库

查看数据库

SHOW DATABASES；

切换数据库

USE 数据库名

查看数据库中存在的数据表

SHOW TABLES；

查看当前使用的数据库

SELECT DATABASE() FROM DUAL

3.修改数据库

先查看数据库结构

SHOW TABLES DATABASE 数据库名

更改数据库字符集

ALTER DATABASE 数据库名 CHATRACTER SET ‘utf-8’;

4.删除数据库

DROP DATABASE 数据库名

DROP DATABASE IF EXISTS 数据库名

5、创建数据表

CREATE TABLE IF NOT EXISTS 数据表名（

字段类型，

字段2 类型

）；

查看表结构：DESC mysql

基于现有的表创建

CREAT TABLE 表1名

AS

SELECT 字段名。。。

FROM 表2名

6.修改表

ALTER TABLE

添加字段

ALTER TABLE 数据表名

ADD 字段名类型 #默认加到最后一个字段

ALTER TABLE 数据表名

ADD 字段名类型 FIRST

ALTER TABLE 数据表名

ADD 字段名类型 AFTER 字段

修改字段

ALTER TABLE 表

MODIFY 字段名类型 DEFAULT 默认值

重命名字段

ALTER TABLE 表

CHANGE 原始字段更改字段类型

删除字段

ALTER TABLE 表

DROP COLUMN 字段

7.重命名表

方式一

RENAME TABLE 表

TO 表名

方式二

ALTER TABLE 表名

RENAME TO 表明

8.删除表

DROP TABLE IF EXISTS 表名

9.清空表

TRUNCATE TABLE 表明

10.DCL中的COMMIT和ROLLBACK

COMMIT：提交数据，永久保存，不能回滚

ROLLBACK：回滚数据，回滚到最近一次执行COMMIT之后

11.对比TRUNCATE TABLE 和DELECT FROM

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

不同点：TRUNCATE TABLE：数据全部清除，不可回滚

DELCTE FROM ：表数据可以全部清除，数据可以回滚

九、数据处理-增删改

1.插入数据

方式一：逐条添加

没有指明添加的字段，按照声明字段的顺序进行添加

INSERT INTO 表

VALUES（各个字段对应的值）

指明添加的字段，没有进行赋值的值为null（默认）

INSERT INTO 表（字段）

VALUES （字段值）

方式二：将查询结果插入

INSERT INTO 表（字段）

SELECT 查询语句

2.更新数据（修改数据）

UPDATE 表

SET

WHERE

3.删除数据

DELETE FROM。 WHERE

4.mysql8新特性：计算列

十、数据类型

1.指明字符集

CHATACTER SET ‘utf8’

应用范围：数据库、表、字段

2.整数类型

TINYINT、1（字节）
SMALLINT、2
MEDIUMINT、3
INT、INTGER、4
BIGINT 、8

INT（5） ZEROFILL #显示宽度为5，长度不足5时用0填充

#当使用ZEROFILL时自动添加unsigned（无符号）

3.浮点数类型

FLOAT、4

DOUBLE、8

REAL

精度：

FLOAT（M，D）：M（精度）=整数位+小数位，D（标度）=小数位

不够精准，避免使用=来判断相等，为解决这个问题使用定点数

4.定点数类型

DECIMAL（M，D），DEC，NUMERIC

5.位类型

BIT（M）：1<=M<=64

6.日期和时间

YEAR 1 1901-2155

DATE 3 yyyy-mm-dd. CURRENT_DATE()或NOW()插入当前系统日期

TIME 3 D HH:MM:SS

DATETIME 8. yyyy-mm-dd HH:MM:SS

TIMESTAMP 4 比DATETIME小，分时区

7.文本字符串类型

ENUM：忽略大小写，可以用索引添加，只能选一个值

SET：可以选0个或多个

8.二进制字符串类型

BINARY

VARBINARY

十一、约束

http://t.csdn.cn/EKu4Mhttp://t.csdn.cn/EKu4M

表级别的强制规定

为了保证数据的完整性

1.分类

约束的字段个数：单列约束vs多列约束
约束作用范围：列级约束vs表级约束
约束的作用（功能）：非空约束vs唯一性约束vs主建约束vs外键约束vs检查约束vs默认值约束

2.如何添加约束

CREATE TABLE时

ALTER TABLE 时

3.非空约束

NOT NULL

没有表级约束

4.唯一性约束

UNIQUE：允许出现多个NULL

表级约束

CONSTRAINT uk UNIQUE（字段

）

5. 主键约束

PRIMARY KEY

6.自增列

auto_increment(主键或唯一键列)

7.外键约束

FOREIGN KEY

8。检查约束

mysql5.7不支持

CHECK

9. 默认值约束

DEFAULT