[大数据]MySQL知识总结

MySQL总结

数据类型

1.整数类型

整数类型分5种：TINYINT、SMALUNT、MEDIUMINT、INT和 BIGINT

2.浮点数类型和定点数类型

浮点数和定点数来存储小数。浮点数的类型有两种：单精度浮点数类型（FLOAT)和双精度浮点数类型（DOUBLE)。而定点数类型只有一种即DECIMAL类型。

注：DECIMAL类型的有效取值范围是由M和D决定的。其中，M表示的是数据的长 度，D表示的是小数点后的长度。比如，将数据类型为DECIMAL(6,2)的数据6.5243 插人数据库后显示的结果为6.52

3.字符串类型

常用CHAR 和 VARCHAR 表示字符串。两者不同的是：VARCHAR存储可变长度的字符串。

当数据为CHAR(M)类型时，不管插入值的长度是实际是多少它所占用的存储空间都是M个字节；而VARCHAR(M)所对应的数据所占用的字节数为实际长度加1

4.文本类型

文本类型用于表示大文本数据，例如，文章内容、评论、详情等

5.日期与时间类型

类型分为：YEAR、DATE、TIME、DATETIME 和 TIMESTAMP。下图列举了日期和时间数据类型所对应的字节数、取值范围、日期格式以及零值：

5.1 YEAR类型

YEAR类型用于表示年份，在MySQL中，可以使用以下三种格式指定YEAR类型 的值。
1、使用4位字符串或数字表示，范围为’1901’—'2155’或1901—2155。例如，输人 ‘2019’或2019插人到数据库中的值均为2019。
2、使用两位字符串表示，范围为’00’—‘99’。其中，‘00’—'69’范围的值会被转换为 2000—2069范围的YEAR值，‘70’—'99’范围的值会被转换为1970—1999范围的YEAR 值。例如，输人’19’插人到数据库中的值为2019。
3、使用两位数字表示，范围为1—99。其中，1—69范围的值会被转换为2001— 2069范围的YEAR值，70—99范围的值会被转换为1970—1999范围的YEAR值。例 如，输人19插入到数据库中的值为2019。
请注意：当使用YEAR类型时，一定要区分’0’和0。因为字符串格式的’0’表示的YEAR值是2000而数字格式的0表示的YEAR值是0000。

5.2 TIME类型

TIME类型用于表示时间值，它的显示形式一般为HH:MM:SS，其中，HH表示小时， MM表示分,SS表示秒。在MySQL中，可以使用以下3种格式指定TIME类型的值。
1、以’D HH:MM:SS’字符串格式表示。其中，D表示日可取0—34之间的值, 插人数据时，小时的值等于(DX24+HH)。例如，输入’2 11:30:50’插人数据库中的日期为59:30:50。
2、以’HHMMSS’字符串格式或者HHMMSS数字格式表示。 例如，输人’115454’或115454,插入数据库中的日期为11:54:54
3、使用CURRENT_TIME或NOW()输人当前系统时间。

5.3 DATETIME类型

DATETIME类型用于表示日期和时间，它的显示形式为’YYYY-MM-DD HH: MM:SS’，其中，YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分，SS 表示秒。在MySQL中，可以使用以下4种格式指定DATETIME类型的值。
以’YYYY-MM-DD HH:MM:SS’或者’YYYYMMDDHHMMSS’字符串格式表示的日期和时间，取值范围为’1000-01-01 00:00:00’—‘9999-12-3 23:59:59’。例如，输人’2019-01-22 09:01:23’或 ‘20140122_0_90123’插人数据库中的 DATETIME 值都为 2019-01-22 09:01:23。
1、以’YY-MM-DD HH:MM:SS’或者’YYMMDDHHMMSS’字符串格式表示的日期和时间，其中YY表示年，取值范围为’00’—‘99’。与DATE类型中的YY相同，‘00’— '69’范围的值会被转换为2000—2069范围的值，‘70’—'99’范围的值会被转换为1970—1999范围的值。
2、以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期 和时间。例如，插入20190122090123或者190122090123,插人数据库中的DATETIME值都 为 2019-01-22 09:01:23。
3、使用NOW来输人当前系统的日期和时间。

5.4 TIMESTAMP类型

TIMESTAMP类型用于表示日期和时间，它的显示形式与DATETIME相同但取值范围比DATETIME小。在此，介绍几种TIMESTAMP类型与DATATIME类型不同的形式：
1、使用CURRENT_TIMESTAMP输人系统当前日期和时间。
2、输人NULL时系统会输人系统当前日期和时间。
3、无任何输人时系统会输入系统当前日期和时间。

6.二进制类型

在MySQL中常用BLOB存储二进制类型的数据，例如：图片、PDF文档等。BLOB类型分为如下四种：

数据库表基本操作

数据库基本操作

创建数据库并查看数据库基本信息

create database 数据库名称;
show database 数据库名称;

删除数据库命令

drop database 数据库名称

修改数据库字符集（编码方式）

alter database 数据库名称 character set gbk

切换数据库

use 数据库名称

查看当前使用的数据库

select database()

数据表基本操作（以创建学生表为例）

创建数据表

create table student(
	id int,
 	name varchar(20),
 	gender varchar(10),
	birthday date	
);

查看数据表

查看当前数据表

show tables;

查看数据表基本信息

show table student;

查看数据表字段信息

desc student

修改数据表

修改数据表表名

alter table student rename to stu;

修改字段名

alter table stu change name sname varchar(10);

修改字段类型

alter table stu modify sname int;

增加字段

alter table stu add address varchar(50);

删除字段

alter table stu drop address;

删除数据表

drop table stu;

数据表的约束

为防止错误的数据被插入到数据表，MySQL中定义了一些维护数据库完整性的规则；这些规则常称为表的约束。常见约束如下：

以上五种约束条件针对表中字段进行限制从而保证数据表中数据的正确性和唯一性。换句话说，表的约束实际上就是表中数据的限制条件。

主键约束

主键约束即primary key用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。这点类似于我们每个人都有一个身份证号，并且这个身份证号是唯一的。

字段名 数据类型 primary key;

方式一

create table student(
id int primary key,
name varchar(20)
);

方式二

create table student(
id int,
name varchar(20),
primary key(id)
);

非空约束

非空约束即 NOT NULL指的是字段的值不能为空

字段名 数据类型 NOT NULL；

默认值约束

默认值约束即DEFAULT用于给数据表中的字段指定默认值，即当在表中插入一条新记录时若未给该字段赋值，那么，数据库系统会自动为这个字段插人默认值

字段名 数据类型 DEFAULT 默认值；

唯一性约束

唯一性约束即UNIQUE用于保证数据表中字段的唯一性，即表中字段的值不能重复出现

字段名 数据类型 UNIQUE;

外键约束

外键约束即FOREIGN KEY常用于多张表之间的约束

-- 在创建数据表时语法如下：
CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)
-- 将创建数据表创号后语法如下：
ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);

示例：

create table student(
id int primary key,
name varchar(20)
);
create table class(
classid int primary key,
studentid int
);
alter table class add constraint fk_class_studentid foreign key(studentid) references student(id);

数据一致性

建立外键是为了保证数据的完整和统一性。但是，如果主表中的数据被删除或修改从表中对应的数据该怎么办呢？很明显，从表中对应的数据也应该被删除，否则数据库中会存在很多无意义的垃圾数据。

删除外键

alter table 从表名 drop foreign key 外键名；
-- 示例
alter table class drop foreign key fk_class_studentid;

细节注意

1、从表里的外键通常为主表的主键
2、从表里外键的数据类型必须与主表中主键的数据类型一致
3、主表发生变化时应注意主表与从表的数据一致性问题

数据表插入数据

为表中所有字段插入数据

每个字段与其值是严格一一对应的。也就是说：每个值、值的顺序、值的类型必须与对应的字段相匹配。但是，各字段也无须与其在表中定义的顺序一致，它们只要与 VALUES中值的顺序一致即可。

INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);
-- 示例
insert into student (id,name,age,gender) values (1,'bob',16,'male');

为表中指定字段插入数据

INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);

同时插入多条记录

INSERT INTO 表名 [(字段名1,字段名2,...)]VALUES (值 1,值 2,…),(值 1,值 2,…),...;
-- 示例
insert into student (id,name,age,gender) values (2,'lucy',17,'female'),(3,'jack',19,'male'),(4,'tom',18,'male');

数据表更新数据

更新字段数据

UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…] [WHERE 条件表达式];
-- 示例（更新部分数据）
update student set age=20,gender='female' where name='tom';
-- 示例（更新全部数据）
update student set age=18;

数据表删除数据

删除字段数据

DELETE FROM 表名 [WHERE 条件表达式];
-- 示例（删除指定数据）
delete from student where age=14;
-- 示例（删除全部数据）
delete from student;

TRUNCATE和DETELE都能实现删除表中的所有数据的功能，但两者也是有区别的：
1、DELETE语句后可跟WHERE子句，可通过指定WHERE子句中的条件表达式只删除满足条件的部分记录；但是，TRUNCATE语句只能用于删除表中的所有记录。
2、使用TRUNCATE语句删除表中的数据后，再次向表中添加记录时自动增加字段的默认初始值重新由1开始；使用DELETE语句删除表中所有记录后，再次向表中添加记录时自动增加字段的值为删除时该字段的最大值加1
3、DELETE语句是DML语句，TRUNCATE语句通常被认为是DDL语句

数据表简单查询

简单查询中最常用的两种查询：查询所有字段和查询指定字段

查询所有字段

select * from student;

查询指定字段

selest sid,sname from student;

常数的查询

select sid,sname,'2021-03-02' from student;

从查询中过滤重复数据

在使用DISTINCT 时需要注意：
在SELECT查询语句中DISTINCT关键字只能用在第一个所查列名之前。

select distinct gender from student;

算术运算符

在SELECT查询语句中还可以使用加减乘除运算符。

 select sname,age+10 from student;

函数

聚合函数

常常有类似的需求：统计某个字段的最大值、最小值、 平均值等等。提供了聚合函数来实现这些功能。所谓聚合，就是将多行汇总成一行；其实，所有的聚合函数均如此——输入多行，输出一行。聚合函数具有自动滤空的功能，若某一个值为NULL，那么会自动将其过滤使其不参与运算。

聚合函数使用规则：
只有SELECT子句和HAVING子句、ORDER BY子句中能够使用聚合函数。例如，在WHERE子句中使用聚合函数是错误的。

count()

统计表中数据的行数或者统计指定列其值不为NULL的数据个数

-- 查询有多少该表中有多少人
select count(*) from student;

max()

计算指定列的最大值，如果指定列是字符串类型则使用字符串排序运算

select max(age) from student;

min()

计算指定列的最小值，如果指定列是字符串类型则使用字符串排序运算

select sname,min(age) from student;

sum()

计算指定列的数值和，如果指定列类型不是数值类型则计算结果为0

select sum(age) from student;

avg()

计算指定列的平均值，如果指定列类型不是数值类型则计算结果为0

select avg(age) from student;

其他常用函数

时间函数

SELECT NOW();
SELECT DAY (NOW());
SELECT DATE (NOW());
SELECT TIME (NOW());
SELECT YEAR (NOW());
SELECT MONTH (NOW());
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT CURRENT_TIMESTAMP();
SELECT ADDTIME('14:23:12','01:02:01');
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);
SELECT DATEDIFF('2019-07-22','2019-05-05');

字符串函数

-- 连接函数
SELECT CONCAT ()
-- 索引查找
SELECT INSTR ();
-- 统计长度
SELECT LENGTH();

数学函数

-- 绝对值
SELECT ABS(-136);
-- 向下取整
SELECT FLOOR(3.14);
-- 向上取整
SELECT CEILING(3.14);

条件查询

数据库中存有大量数据，我们可根据需求获取指定的数据。此时，我们可在查询语句中通过WHERE子句指定查询条件对查询结果进行过滤。

使用关系运算符查询

在WHERE中可使用关系运算符进行条件查询，常用的关系运算符如下所示：

-- 示例
select * from student where age>=17;

使用IN关键字查询

IN关键字用于判断某个字段的值是否在指定集合中。如果字段的值恰好在指定的集合中，则将字段所在的记录将査询出来。

-- 示例
select * from student where sid in ('S_1002','S_1003');
select * from student where sid not in ('S_1001');

使用BETWEEN AND关键字查询

BETWEEN AND用于判断某个字段的值是否在指定的范围之内。如果字段的值在指定范围内，则将所在的记录将查询出来

-- 示例 
select * from student where age between 15 and 18;
select * from student where age not between 15 and 18;

使用空值查询

在MySQL中，使用 IS NULL关键字判断字段的值是否为空值。注意：空值NULL不同于0，也不同于空字符串

select * from student where sname is not null;

使用AND关键字查询

在MySQL中可使用AND关键字可以连接两个或者多个查询条件。

select * from student where age>15 and gender='male';

使用OR关键字查询

在使用SELECT语句查询数据时可使用OR关键字连接多个査询条件。在使用OR关键字时，只要记录满足其中任意一个条件就会被查询出来

select * from student where age>15 or gender='male';

使用LIKE关键字查询

MySQL中可使用LIKE关键字可以判断两个字符串是否相匹配

普通字符串

select * from student where sname like 'wang';

含有%通配的字符串

%用于匹配任意长度的字符串。例如，字符串“a%”匹配以字符a开始任意长度的字符串

select * from student where sname like "li%";
select * from student where sname like '%g';
select * from student where sname like '%s%';

含有_通配的字符串

下划线通配符只匹配单个字符，如果要匹配多个字符，需要连续使用多个下划线通配符。例如，字符串“ab_”匹配以字符串“ab”开始长度为3的字符串，如abc、abp等等；字符串“a__d”匹配在字符“a”和“d”之间包含两个字符的字符串，如"abcd"、"atud"等等。

select * from student where sname like "zx__";

使用LIMIT限制查询结果的数量

当执行查询数据时可能会返回很多条记录，而用户需要的数据可能只是其中的一条或者几条

-- 查询学生表中年纪最小的3位同学 MySQL命令：
select * from student order by age asc limit 3;

使用GROUP BY进行分组查询

与聚合函数一起使用

-- 统计各部门员工个数 MySQL命令：
select count(*), departmentnumber from employee group by departmentnumber;

和聚合函数以及HAVING一起使用

-- 统计工资总和大于8000的部门 MySQL命令：
select sum(salary),departmentnumber from employee group by departmentnumber having sum(salary)>8000;

使用ORDER BY对查询结果排序

SELECT 字段名1,字段名2,…
FROM 表名
ORDER BY 字段名1 [ASC 丨 DESC],字段名2 [ASC | DESC];

字段名1、字段名2是查询结果排序的依据；参数 ASC表示按照升序排序，DESC表示按照降序排序；默认情况下，按照ASC方式升序排序。通常情况下，ORDER BY子句位于整个SELECT语句的末尾。

-- 示例
select * from student order by age asc;

别名设置

在査询数据时可为表和字段取別名，该别名代替表和字段的原名参与查询操作。

为表取别名

在查询操作时，假若表名很长使用起来就不太方便，此时可为表取一个別名，用该别名来代替表的名称。

SELECT * FROM 表名 [AS] 表的别名 WHERE .... ;
-- 示例
select * from student as stu;

为表指定别名，AS关键字可以省略不写

为字段取别名

在查询操作时，假若字段名很长使用起来就不太方便，此时可该字段取一个別名，用该别名来代替字段的名称。

SELECT 字段名1 [AS] 别名1 , 字段名2 [AS] 别名2 , ... FROM 表名 WHERE ... ;
-- 示例
select name as '姓名',id from student;

为字段指定别名，AS关键字可以省略不写

表的关联关系

多对一
多对一(亦称为一对多)是数据表中最常见的一种关系。例如：员工与部门之间的关系，一个部门可以有多个员工；而一个员工不能属于多个部门只属于某个部门。在多对一的表关系 中，应将外键建在多的一方否则会造成数据的冗余。
多对多
多对多是数据表中常见的一种关系。例如：学生与老师之间的关系，一个学生可以有多个老师而且一个老师有多个学生。通常情况下，为了实现这种关系需要定义一张中间表(亦称为连接表)该表会存在两个外键分别参照老师表和学生表。
一对一
在开发过程中，一对一的关联关系在数据库中并不常见；因为以这种方式存储的信息通常会放在同一张表中。

关联查询

select * from student where classid=(select cid from class where cname='Java');

关于关联关系的删除数据

请从班级表中删除Java班级。在此，请注意：班级表和学生表之间存在关联关系；要删除Java班级，应该先删除学生表中与该班相关联的学生。否则，假若先删除Java班那么学生表中的cid就失去了关联

delete from student where classid=(select cid from class where cname='Java');
delete from class where cname='Java';

多表连接查询

交叉连接查询

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡儿积；比如：集合A={a,b}，集合B={0,1,2}，则集合A和B的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。所以，交叉连接也被称为笛卡尔连接。

SELECT * FROM 表1 CROSS JOIN 表2;

CROSS JOIN用于连接两个要查询的表，通过该语句可以查询两个表中所有的数据组合。

内连接查询

内连接(Inner Join)又称简单连接或自然连接，是一种非常常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据行，组合成新的记录。也就是说在内连接查询中只有满足条件的记录才能出现在查询结果中。

SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段
-- 示例
select employee.ename,department.dname from department inner join employee on department.did=employee.departmentid;

INNER JOIN用于连接两个表，ON来指定连接条件；其中INNER可以省略。

外连接查询

在使用内连接查询时我们发现：返回的结果只包含符合查询条件和连接条件的数据。但是，有时还需要在返回查询结果中不仅包含符合条件的数据，而且还包括左表、右表或两个表中的所有数据，此时我们就需要使用外连接查询。外连接又分为左(外)连接和右(外)连接。

SELECT 查询字段1,查询字段2, ... FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件

关键字左边的表被称为左表，关键字右边的表被称为右表；OUTER可以省略。

在使用左(外)连接和右(外)连接查询时，查询结果是不一致的，具体如下：
1、LEFT [OUTER] JOIN 左(外)连接：返回包括左表中的所有记录和右表中符合连接条件的记录。
2、RIGHT [OUTER] JOIN 右(外)连接：返回包括右表中的所有记录和左表中符合连接条件的记录。

子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询；该查询语句可以嵌套在一个 SELECT、SELECT…INTO、INSERT…INTO等语句中。在执行查询时，首先会执行子查询中的语句，再将返回的结果作为外层查询的过滤条件。在子査询中通常可以使用比较运算符和IN、EXISTS、ANY、ALL等关键字。

带比较运算符的子查询

select * from class where cid=(select classid from student where sname='张三');

带EXISTS关键字的子查询

EXISTS关键字后面的参数可以是任意一个子查询，它不产生任何数据只返回TRUE或FALSE。当返回值为TRUE时外层查询才会执行

select * from class where exists (select * from student where sname='王五');

带ANY关键字的子查询

ANY关键字表示满足其中任意一个条件就返回一个结果作为外层查询条件。

select * from class where cid > any (select classid from student);

带ALL关键字的子查询

ALL关键字与ANY有点类似，只不过带ALL关键字的子査询返回的结果需同时满足所有内层査询条件。

select * from class where cid > all (select classid from student);

最后

查询语句的书写顺序和执行顺序
select ===> from ===> where ===> group by ===> having ===> order by ===> limit
查询语句的执行顺序
from ===> where ===> group by ===> having ===> select ===> order by ===> limit