Java第二阶段_day02_MySQL进阶

🏠个人主页：不会写代码的满满
🧑个人简介：大家好，我是满满，一个想要与大家共同进步的男人😉😉
目前状况🎉：开学即将大三，目标就是半年内找到一份实习工作👏👏
💕欢迎大家：这里是CSDN，我总结知识的地方，欢迎来到我的博客，我亲爱的大佬😘

正文开始 ----------

学习目标

• 能够使用SQL语句操作数据库
• 掌握MYSQL支持的数据类型
• 能够使用SQL语句操作表结构
• 能够使用SQL语句进行数据的添加修改和删除的操作
• 能够使用SQL语句简单查询数据
• 能够使用SQL关联查询

1. 数据定义语言（DDL）🧬

操作数据库

创建数据库

1. 直接创建数据库
   CREATE DATABASE 数据库名;

2. 判断是否存在并创建数据库(了解)
   CREATE DATABASE IF NOT EXISTS 数据库名;

3. 创建数据库并指定字符集(了解)
   CREATE DATABASE 数据库名 CHARACTER SET 字符集;

4. 具体操作：

• 直接创建数据库db1

  CREATE DATABASE db1;
  1

  

• 判断是否存在并创建数据库db2

  CREATE DATABASE IF NOT EXISTS db2;
  1

  

• 创建数据库并指定字符集为gbk

  CREATE DATABASE db2 CHARACTER SET gbk;
  1

  

查看数据库

1. 查看所有的数据库

   SHOW databases;
   1

   

2. 查看某个数据库的定义信息

   SHOW CREATE DATABASE 数据库名;
   1

   

修改数据库(了解)

修改数据库字符集格式

ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;

具体操作：

• 将db3数据库的字符集改成utf8

  ALTER DATABASE db3 DEFAULT CHARACTER SET utf8;
  1

  

删除数据库

DROP DATABASE 数据库名;

具体操作：

• 删除db2数据库

  DROP DATABASE db2;
  1

  

使用数据库

1. 查看正在使用的数据库
   SELECT DATABASE();
2. 使用/切换数据库
   USE 数据库名;

具体操作：

• 查看正在使用的数据库

  SELECT DATABASE();
  1

  

• 使用db1数据库

  USE db1;
  1

  

操作表

如果已执行 use 数据库名; 可以省略数据库名。

创建表

语法：

CREATE TABLE [IF NOT EXISTS] 表名 (
   字段名 字段类型 [ primary key | unique | not null | auto_increment |
      | binary | default 缺省值 | comment 注释语句],
   ......
) [ ENGINE=InnoDB | DEFAULT CHARSET=utf8 ];
1
2
3
4
5

表可选项：

if not exists：如果不存在就执行建表

engine：设置数据引擎，默认为innodb

default charset：设置默认字符编码

字段可选项：

primary key：主键，值唯一，不可为null

unique：唯一键，值唯一，可以为null

not null：不可为null，不写这一项，则默认为可以接受null值

auto_increment：自动增长，只能与整数类型搭配，默认每次增长1

binary：与字符类型搭配，在字段比较时大小写敏感

default：设置缺省值，若插入值为null时，则使用默认值填充

comment：设置字段注释

示例：

MySQL的数值数据类型

​ MySQL支持所有标准SQL数值数据类型。

​ 这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

整数类型

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

​ TINYINT，1字节，8位，16进制最大表示为FF，2进制表示最大为1111 1111，有符号，第一位为符号为（整数为0，负数为1），所以整数最大值为0111 1111（127），负数最大值为1111 1111（-128），无符号的最大值是255。

• 可以用unsigned控制是否有符号位

• 可以使用zerofill控制是否有前导零

• 也存在布尔类型。首先mysql是不支持布尔类型的,当把一个数据设置成布尔类型的时候,数据库会自动转换成tinyint(1)的数据类型,其实这个就是变相的布尔。 默认值也就是1,0两种,分别对应了布尔类型的true和false。

• 类型后面(1)，代表的显示长度，只有跟zerofill配合起来才能用。简单地说，没有(1)，会显示成00x数字，具体连数字带前导零总共几位，有(n)来限制。

  我们在这里测试一下。

  create table test1 (
  c1 TINYINT,
  c2 tinyint UNSIGNED,
  c3 tinyint ZEROFILL );
  1
  2
  3
  4

  然后执行sql，分别看看执行结果，分析报错信息。

  insert into test1 values(1, 2, 3);
  insert into test1 values(-1, 2, 3);
  insert into test1 values(1, -2, 3);
  insert into test1 values(1, 2, -3);
  insert into test1 values(127, 2, -3);
  insert into test1 values(-128, 128, 3);
  insert into test1 values(-128, 256, 255);
  1
  2
  3
  4
  5
  6
  7

  总结：zerofill具备unsigned的隐藏属性，不能存负值。

SMALLINT，2字节，16位，16进制最大表示为FFFF。

MEDIUMINT，3字节，24位，16进制最大表示为FF FFFF。

INT或INTEGER ，4字节，32位 ，16进制最大表示为FFFF FFFF。

BIGINT，8字节，64位 ，16进制最大表示为FFFF FFFF FFFF FFFF。

​ 这些类型都可以用unsigned和zerofill修饰。

浮点数类型

科学计数法，0.123 * 10^1。

创建该类型字段时，可以设置（M，D）模式控制数值范围

create table test4 (
  f1 float,
  f2 float(5, 2),
  f3 float(16，4)
);

insert into test4 values (123.456789, 123.456789, 123.456789);
insert into test4 values (1234.56789, 1234.56789, 1234.56789);
insert into test4 values (12345.6789, 12345.6789, 12345.6789);
insert into test4 values (123456.789, 123456.789, 123456.789);
insert into test4 values (1234567.89, 1234567.89, 1234567.89);
insert into test4 values (12345678.9, 12345678.9, 12345678.9);
insert into test4 values (123456789, 123456789, 123456789);
select * from test4;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

**从上面看出：**默认的float类型都只能存6个数字（包括小数点前后的位数），整数超过6位就被科学计数表示（id=4），小数位超出则需要四舍五入。

float(m,d)：小数点后位数为d，即整数位数为（m-d），整数位超出则整数为(m-d)个，小数点后位数为(d)个9999，不用科学计算了；若小数位超出，则需要四舍五入。

单精度浮点数的精度是不高的，我们可以试试double。

drop table test5;
create table test5 (
  d1 double,
  d2 double(5, 2),
  d3 double(16, 4)
);

insert into test5 values (123.456789, 123.456789, 123.456789);
insert into test5 values (1234.56789, 1234.56789, 1234.56789);
insert into test5 values (12345.6789, 12345.6789, 12345.6789);
insert into test5 values (123456.789, 123456.789, 123456.789);
insert into test5 values (1234567.89, 1234567.89, 1234567.89);
insert into test5 values (12345678.9, 12345678.9, 12345678.9);
insert into test5 values (123456789, 123456789, 123456789);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

双精度浮点型的有效精度更高（16位左右），可以容纳更多的值。

定点小数类型

我们创建一个包含decimal类型字段的表

drop table test6;
create table test6 (
  d1 decimal,
  d2 decimal(6, 2),
  d3 decimal(32, 4)
);
insert into test6 values (123.456789, 123.456789, 123.456789);
insert into test6 values (1234.56789, 1234.56789, 1234.56789);
insert into test6 values (12345.6789, 12345.6789, 12345.6789);
insert into test6 values (123456.789, 123456.789, 123456.789);
insert into test6 values (1234567.89, 1234567.89, 1234567.89);
insert into test6 values (12345678.9, 12345678.9, 12345678.9);
1
2
3
4
5
6
7
8
9
10
11
12

decimal型的默认整数位为10，小数位为0，即默认为整数。

在MySQL中，定点数以字符串形式存储，因此，其精度比浮点数要高，而且浮点数会出现误差，这是浮点数一直存在的缺陷。如果要对数据的精度要求比较高，还是选择定点数decimal比较安全。

MySQL 数值类型溢出处理(了解)

当 MySQL 在某个数值列上存储超出列数据类型允许范围的值时，结果取决于当时生效的 SQL 模式

• 如果启用了严格的 SQL 模式，则 MySQL 会根据 SQL 标准拒绝带有错误的超出范围的值，并且插入失败。

  SET sql_mode = 'TRADITIONAL'; -- 将当前会话的模式设置严格模式
  1

• 如果没有启用任何限制模式，那么 MySQL 会将值裁剪到列数据类型范围的上下限值并存储

  • 当超出范围的值分配给整数列时，MySQL 会存储表示列数据类型范围的相应端点的值
  • 当为浮点或定点列分配的值超出指定（或默认）精度和比例所隐含的范围时，MySQL 会存储表示该范围的相应端点的值

  SET sql_mode = ''; -- 禁用所有模式
  1

不光是insert 的时候，其他时候也可能会触发数值溢出。

数值表达式求值过程中的溢出会导致错误，例如，因为最大的有符号 BIGINT 值是 9223372036854775807，因此以下表达式会产生错误。

mysql> SELECT 9223372036854775807 + 1;
ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'
1
2

为了在这种情况下使操作成功，需要将值转换为 unsigned

mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;
+-------------------------------------------+
| CAST(9223372036854775807 AS UNSIGNED) + 1 |
+-------------------------------------------+
|                       9223372036854775808 |
+-------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

整数数值之间的减去，如果其中一个类型为 UNSIGNED ，默认情况下会生成无符号结果。如果为负，则会引发错误。

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
1
2
3
4
5

这种情况下，如果启用了 NO_UNSIGNED_SUBTRACTION SQL 模式，则结果为负。

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|                      -1 |
+-------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

如果此类操作的结果用于更新 UNSIGNED 整数列，则结果将裁剪为列类型的最大值，如果启用了 NO_UNSIGNED_SUBTRACTION 则裁剪为 0。但如果启用了严格的 SQL 模式，则会发生错误并且列保持不变。

MySQL的日期时间数据类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

我们来创建 一个表试试。

drop table dt01;
create table dt01(
  d1 year,
  d2 date,
  d3 time,
  d4 datetime,
  d5 timestamp
);

insert into dt01 values ('2020', '2020-3-4', '3:4:5','2020-3-4 3:4:5', null);
insert into dt01 values ('2020', '20200304', '131415','20200304131415', CURRENT_TIMESTAMP);
insert into dt01 values ('2020', '2020-3-4', '3:4:5','2020-3-4 3:4:5', CURRENT_TIMESTAMP);
insert into dt01 values ('2020', '2020:3:4', '03:04:05','2020:3:4 3:4:5', CURRENT_TIMESTAMP);
insert into dt01 values ('2020', '2020-3-4', '3:4:5',
STR_TO_DATE('2019-12-5 7:8:9', '%Y-%m-%d %H:%i:%s'), CURRENT_TIMESTAMP);
insert into dt01 values ('2020', '2020-3-4', '3:4:5',
STR_TO_DATE('2019*12*5 7&8&9', '%Y*%m*%d %H&%i&%s'), CURRENT_TIMESTAMP);
insert into dt01 values ('2020', '2020-3-4', '3:4:5',
STR_TO_DATE('2019年12月12日 7时8分9秒', '%Y年%m月%d日 %H时%i分%s秒'), CURRENT_TIMESTAMP);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

日期时间类型转换成格式字符串，可以使用这种方法。

select date_format(now(), ‘%Y-%m-%d %H:%i:%s’); 
1

格式字符串转换成日期时间类型，可以使用这个函数

STR_TO_DATE('2019-12-5 7:8:9', '%Y-%m-%d %H:%i:%s')
1

format可以使用的值为：

%M 月名字(January……December)

%W 星期名字(Sunday……Saturday)

%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）

%Y 年, 数字, 4 位

%y 年, 数字, 2 位

%a 缩写的星期名字(Sun……Sat)

%d 月份中的天数, 数字(00……31)

%e 月份中的天数, 数字(0……31)

%m 月, 数字(01……12)

%c 月, 数字(1……12)

%b 缩写的月份名字(Jan……Dec)

%j 一年中的天数(001……366)

%H 小时(00……23)

%k 小时(0……23)

%h 小时(01……12)

%I 小时(01……12)

%l 小时(1……12)

%i 分钟, 数字(00……59)

%r 时间,12 小时(hh:mm:ss [AP]M)

%T 时间,24 小时(hh:mm:ss)

%S 秒(00……59) %s 秒(00……59)

%p AM或PM

%w 一个星期中的天数(0=Sunday ……6=Saturday ）

%U 星期(0……52), 这里星期天是星期的第一天

%u 星期(0……52), 这里星期一是星期的第一天

%% 一个文字“%”。

MySQL的字符串类型

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换，CHAR删除尾部的空格，VARCHAR则保留尾部的空格。

因为varchar要记录数据长度（系统根据数据长度自动分配空间），所以每个varchar数据产生后，系统都会在数据后面增加1-2个字节的额外开销：是用来保存数据所占用的空间长度

如果数据本身小于127个字符：额外开销一个字节；如果大于127个，就开销两个字节。

最大长度根据字符集换算，GBK每个字符2个字节，UTF8每个字符3个字节，utf8mb4每个字符4字节，那么

在GBK字符集下，varchar最大长度32766，UTF8最大长度21844，UTF8MB4最大长度16384。

我们来验证一下

create table test7 (
  test_char varchar(21845)
) character set utf8;
create table test7 (
  test_char varchar(32766)
) character set gbk;
create table test7 (
  test_char varchar(16383)
) character set utf8mb4;
1
2
3
4
5
6
7
8
9

还有一个有趣的现象，mysql表的每行记录字节总和不能超过65535。

create table test7 (
  c1 char(100),
  c2 varchar(21845)
) character set utf8;

create table test7 (
  c1 char(100),
  c2 varchar(21744)
) character set utf8;
1
2
3
4
5
6
7
8
9

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

1.BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob是以二进制方式存储，不分大小写。
2.BLOB存储的数据只能整体读出。
3.TEXT可以指定字符集，BLOB不用指定字符集。

MySQL的枚举类型（了解）

枚举类型：在数据插入之前，先设定几个项，这几个项就是可能最终出现的数据结果。

如果确定某个字段的数据只有那么几个值：如性别，男、女、保密，系统就可以在设定字段的时候规定当前字段只能存放固定的几个值：使用枚举

基本语法：enum(数据值1,数据值2…)

create table student (
  sex enum('男', '女')
	);
desc student;
1
2
3
4

插入数据：合法数据，字段对应的值必须是设定表的时候所确定的值

insert into student values('男');
insert into student values('xx');
1
2

我们可以把这个student表扩展一下，来看看enum与字符字段的区别。

create table student (
  s char(1),
  sex enum('男', '女')
	) character set utf8mb4;
insert into student values('男','男');
insert into student values('女','女');
select * from student;
select s+0,  sex+0 from student;
1
2
3
4
5
6
7
8

验证结果说明，enum实际在数据库中以整型存放，获取值时，会进行对应值的转换。

在MySQL数据库中,连接字符串,如果我们使用+号的话,就会出现结果"0",所以MySQL数据库,我们必须使用concat连接字符串。

MySQL的SET类型（了解）

在ENUM中我们只能从允许值列表中给字段插入一个值，而在SET类型中可以给字段插入多个值

CREATE TABLE set_t(
a SET('1','2','3','4','5')
);
desc set_t;
1
2
3
4

再来插些值看看

INSERT set_t VALUES('1');
INSERT set_t VALUES('1,2,3');
1
2

插入值时值之间不能有空格，插入会报错，必须把空格去掉

MySQL数据类型
MySQL中的我们常使用的数据类型如下：

详细的数据类型如下(不建议详细阅读！)

**注意：**char与VARCHAR的区别：

1．CHAR的长度是固定的，而VARCHAR的长度是可以变化的， 比如，存储字符串“abc"，对于CHAR (20)，表示你存储的字符将占20个字节(包括17个空字符)，而同样的VARCHAR2 (20)则只占用3个字节的长度，20只是最大值，当你存储的字符小于20时，按实际长度存储。

2．CHAR的效率比VARCHAR2的效率稍高。

VARCHAR比CHAR节省空间，在效率上比CHAR会稍微差一些，即要想获得效率，就必须牺牲一定的空间，这也就是我们在数据库设计上常说的‘以空间换效率’。

具体操作:

创建student表包含id,name,birthday字段

CREATE TABLE student (
      id INT,
      name VARCHAR(20),
      birthday DATE
);
1
2
3
4
5

查看表

1. 查看某个数据库中的所有表
   SHOW TABLES;

2. 查看表结构
   DESC 表名;

3. 查看创建表的SQL语句
   SHOW CREATE TABLE 表名;

具体操作：

• 查看mysql数据库中的所有表

  SHOW TABLES;
  1

  

• 查看student表的结构

  DESC student;
  1

  

• 查看student的创建表SQL语句

  SHOW CREATE TABLE student;
  1

  

快速创建一个表结构相同的表

CREATE TABLE 新表名 LIKE 旧表名;

具体操作：

• 创建s1表，s1表结构和student表结构相同

  CREATE TABLE s1 LIKE student;
  1

  

删除表

1. 直接删除表
   DROP TABLE 表名;
2. 判断表是否存在并删除表(了解)
   DROP TABLE IF EXISTS 表名;

具体操作：

• 直接删除表s1表

  DROP TABLE s1;
  1

  

• 判断表是否存在并删除s1表

  DROP TABLE IF EXISTS s1;
  1

  

修改表结构

修改表结构使用不是很频繁，只需要知道下，等需要使用的时候再回来查即可

1. 添加表列
   ALTER TABLE 表名 ADD 列名 类型;

   具体操作：

   • 为学生表添加一个新的字段remark,类型为varchar(20)

     ALTER TABLE student ADD remark VARCHAR(20);
     1

     

2. 修改列类型
   ALTER TABLE 表名 MODIFY 列名 新的类型;
   具体操作：

   • 将student表中的remark字段的改成varchar(100)

     ALTER TABLE student MODIFY remark VARCHAR(100);
     1

     

3. 修改列名
   ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
   具体操作：

   • 将student表中的remark字段名改成intro，类型varchar(30)

     ALTER TABLE student CHANGE remark intro varchar(30);
     1

     

4. 删除列
   ALTER TABLE 表名 DROP 列名;
   具体操作：

   • 删除student表中的字段intro

     ALTER TABLE student DROP intro;
     1

     

5. 修改表名
   RENAME TABLE 表名 TO 新表名;
   具体操作：

   • 将学生表student改名成student2

      RENAME TABLE student TO student2;
     1

     

6. 修改字符集
   ALTER TABLE 表名 character set 字符集;
   具体操作：

   • 将sutden2表的编码修改成gbk

     ALTER TABLE student2 character set gbk;
     1

     

2. 单表查询语句（Select）🙈

查询不会对数据库中的数据进行修改.只是一种显示数据的方式
准备数据

CREATE TABLE student3 (
  id int,
  name varchar(20),
  age int,
  sex varchar(5),
  address varchar(100),
  math int,
  english int
);

INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);
1
2
3
4
5
6
7
8
9
10
11

简单查询

查询表所有数据

1. 使用*表示所有列
   SELECT * FROM 表名;
   具体操作：

   SELECT * FROM student3;
   1

   

2. 写出查询每列的名称
   SELECT 字段名1, 字段名2, 字段名3, ... FROM 表名;
   具体操作：

   SELECT id, NAME ,age, sex, address, math, english FROM student3;
   1

   

查询指定列

查询指定列的数据,多个列之间以逗号分隔
SELECT 字段名1, 字段名2... FROM 表名;

具体操作：
查询student3表中的id , name , age , sex , address 列

SELECT id, NAME ,age, sex, address FROM student3;
1

别名查询

​ 1、“*”，表示按照create table的顺序排列的所有列。

​ 2、表名.*，表示取回一个指定表中的所有列，适用于多表关联时，存在同名字段。列表中使用非限定列名，可能会产生解析错误。

​ 2、按照用户所需顺序排列的列名的清单。

​ 3、可以使用别名取代列名，形式如下：

column name as column_heading 
1

​ mysql还支持不带as，直接空格跟别名的方式来指定别名。

​ 4、表达式（列名、常量、函数，或以算术或逐位运算符连接的列名、常量和函数的任何组合）。

​ 5、内部函数或集合函数。

​ 6、上述各项的任何一种组合。

SQL的运算符

​ MySql中，数据库中的表结构确立后，表中的数据代表的意义就已经确定。而通过 MySQL 运算符进行运算，就可以获取到表结构以外的另一种数据。

​ 例如，学生表中存在一个 birth 字段，这个字段表示学生的出生年份。而运用 MySQL 的算术运算符用当前的年份减学生出生的年份，那么得到的就是这个学生的实际年龄数据。

算术运算符

​ 算术运算符是 SQL 中最基本的运算符，MySQL 中的算术运算符如下表所示。

​ 注意：在除法运算和模运算中，如果除数为0，将是非法除法，返回结果为NULL。

​ 加法减法没什么可以说的，我们先来说说乘法。

​ 整数的乘法结果是整数。小数的乘法呢？

mysql> select 2*3 as t;
+---+
| t |
+---+
| 6 |
+---+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

mysql> select 2.0*3.0 as t;
+------+
| t    |
+------+
| 6.00 |
+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

可以看到小数乘以小数，结果仍然是小数，我们需要关注精度，保留了乘数和被乘数的小数之和，系统并不会将小数点后的零自动去除。如果需要去除，则采用强制转换函数。

mysql> select 1.234 * 5.678 as t;
+----------+
| t        |
+----------+
| 7.006652 |
+----------+
1 row in set (0.00 sec)
mysql> select convert(1.234 * 5.678, decimal(10,2)) as t;
+------+
| t    |
+------+
| 7.01 |
+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

MySQL 的CONVERT()函数可用来获取一个类型的值，并产生另一个类型的值。具体的语法如下：

CONVERT(value, type);
1

直接在sql中操作乘法的精度没问题，我们来看看字段操作怎么样。

create table ta (
aaa double,
bbb double,
ccc float,
ddd float,
eee decimal(10,2),
fff decimal(10,2)
);
insert into ta values (1.23, 2.34, 1.23, 2.34, 1.23, 2.34);
mysql> select aaa, bbb, aaa*bbb, ccc, ddd, ccc*ddd, eee, fff ,eee*fff from ta;
+------+------+--------------------+------+------+--------------------+------+------+---------+
| aaa  | bbb  | aaa*bbb            | ccc  | ddd  | ccc*ddd            | eee  | fff  | eee*fff |
+------+------+--------------------+------+------+--------------------+------+------+---------+
| 1.23 | 2.34 | 2.8781999999999996 | 1.23 | 2.34 | 2.8781999390602095 | 1.23 | 2.34 |  2.8782 |
+------+------+--------------------+------+------+--------------------+------+------+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

可以发现除了decimal，float和double的乘法操作都存在精度问题，需要强转。

可以发现：

sql里面直接数相乘，与字段中decimal相乘一致，结果小数位数保留被乘数乘数之和。

double类型数相乘，结果的精度高于float数相乘。

提示：能用decimal，就不要用float和double。

除法操作，我们也来看看精度问题。跟乘法类似，

进一步尝试

select 1.22345 / 2.3456;
insert into ta values (1.2345, 2.3456, 1.2345, 2.3456, 1.2345, 2.3456);
select aaa, bbb, aaa/bbb, ccc, ddd, ccc/ddd, eee, fff ,eee/fff from ta where aaa=1.2345;
1
2
3

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H6C4sGsS-1660318360407)(https://gaoziman.oss-cn-hangzhou.aliyuncs.com/img/FUFI4950CV]H}X%J_}LS9.png)]

可以发现：

sql里面直接数相除，与字段中decimal相除一致，结果小数位数保留被除数除数之和。

double类型数相除，结果的精度高于float数相除。

比较运算符

select语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录时符合条件的，比较结果为真，则返回1，为假则返回0，比较结果不确定则返回NULL。

select 1<2;
1

等于（==）与严格等于运算符（<=>）

严格等于和等于运算符（=）的作用一致，只不过多了一个功能，就是可以判断NULL值，如下：

select 1=0,'2'=2,(1+3)=(2+2),NULL=NULL;
select 1<=>0,'2'<=>2,(1+3)<=>(2+2),NULL<=>NULL;
1
2

不等于运算符（<>或!=）

不等于运算符用于判断数字、字符串、表达式是否不相等，如果不相等则返回 1，否则返回 0 ，但是不能判断 NULL 值。

IS NULL 、IS NOT NULL

• IS NULL是 检验一个值是否为 NULL ，如果为 NULL ，返回值为 1，否则返回值为 0；
• IS NOT NULL 检验一个值是否不为 NULL ，如果不为 NULL ，返回值为 1，否则返回值为 0。

BETWEEN AND

用于判断一个值是否落在两个值之间。

select 4 between 1 and 5,4 between 4 and 6,12 between 9 and 10;
1

between … and 操作符是包含两边端点的。

IN、NOT IN

• IN ：判断一个值是否是 IN 列表中的任意一个值；
• NOT IN ：判断一个值是否不是 IN 列表中的任意一个值。

select 2 in(3,5,8,2), 2 not in(1,3,5);
1

LIKE

• LIKE 运算符用来匹配字符串（正则表达式），如果匹配则返回 1，如果不匹配则返回 0；
• LIKE 使用两种通配符：‘%’ 用于匹配任何数目的字符，包括零字符 ； ‘_’ 只能匹配一个字符。

mysql> select 'stud' like 'stu_','stud' like 's___','stud' like '%d';
1

REGEXP

1）REGEXP运算符用来匹配字符串，如果匹配则返回1，如果不匹配则返回0；
2）REGEXP 使用以下几种通配符：

• ‘^’：用于匹配以什么开头的字符串；
• ‘$’：用以匹配以什么结尾的字符串；
• ‘.’：用于匹配任何一个单字符串；
• ‘[…]’：用于匹配在方括号内的任何字符；
• '*'用于匹配零个或多个在它前面的字符；

select 'ssky' regexp '^s', 'ssky' regexp 'y$', 'ssky' regexp '.sky', 'ssky' regexp '[ab]' ;
1

逻辑运算符

逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。

逻辑非（NOT 或 ！）

• 当操作数为 0 时，所得值为 1；
• 当操作数为非 0 时，所得值为 0；
• 当操作数为 NULL 时，所得值为 NULL。

mysql> select not  10,!10,not(1-1),!(1-1),not 1+1,not null;
1

逻辑与（AND或&&）

• 当所有操作数均为非零值、并且不为 NULL 时，所得值为 1；
• 当一个或多个操作数为 0 时，所得值为 0 ；
• 其余情况所得值为 NULL。

mysql> select 1 and -1,1 && 0,0 and null,1 && null;
1

逻辑或（OR 或 || ）

• 当两个操作数均为非 NULL 值，且任意一个操作数为非零值时，结果为 1 ，否则为 0；
• 当有一个操作数为 NULL ，且另一个操作数为非零值时，则结果为 1 ，否则结果为 NULL；
• 当两个操作数均为 NULL 时，则所得结果为 NULL。

mysql> select 1 or -1 or 0,1 || 2,0 or null,null|| null;
1

逻辑异或（XOR）

• a XOR b 的计算等同于 ( a AND (NOT b) ) 或 ( (NOT a) AND b )；
• 当任意一个操作数为 NULL 时，返回值为 NULL；
• 对于非 NULL 的操作数，如果两个操作数都是非 0 值或者都是 0 值，则返回结果为 0；
• 如果一个为 0 值，另一个为非 0 值，返回结果为 1。

mysql> select 1 xor 1,0 xor 0,1 xor 0,1 xor null,1 xor 1 xor 1;
1

运算符的优先级

最低优先级为： :=。

最高优先级为： !、BINARY、 COLLATE。

​ 用（），千万记得用括号。

where子句

​ where子句设置了搜索条件。

​ 它在insert，update，delete语句中的应用方法也与在select语句中的应用方法完全相同。搜索条件紧跟在关键词where的后面。如果用户要在语句中使用多个搜索条件，则可用and或or连接。

​ 搜索条件的基本语法是

select * from test1 where aaa = '呵呵';
select* from test1 where not aaa = '呵呵';
select* from test1 where aaa != '呵呵';
1
2
3

​

distinct关键字的用法

​ 在mysql中，distinct关键字的主要作用就是对数据库表中一个或者多个字段重复的数据进行过滤，只返回其中的一条数据给用户，distinct只可以在select中使用。

​ distinct的原理：
​ distinct进行去重的主要原理是通过先对要进行去重的数据进行分组操作，然后从分组后的每组数据中去一条返回给客户端，在这个分组的过程可能会出现两种不同的情况：

​ distinct 依赖的字段全部包含索引：
​ 该情况mysql直接通过操作索引对满足条件的数据进行分组，然后从分组后的每组数据中去一条数据。

​ distinct 依赖的字段未全部包含索引：
​ 该情况由于索引不能满足整个去重分组的过程，所以需要用到临时表，mysql首先需要将满足条件的数据放到临时表中，然后在临时表中对该部分数据进行分组，然后从临时表中每个分组的数据中去一条数据，在临时表中进行分组的过程中不会对数据进行排序。

GROUP BY 语句

​ GROUP BY 语句根据一个或多个列对结果集进行分组。

select bbb from test1 group by bbb;
--这其实跟distinct返回的结果一致
select distinct bbb from test1;
1
2
3

聚合函数

​ 聚合函数aggregation function又称为组函数。 默认情况下 聚合函数会对当前所在表当做一个组进行统计，MySQL提供了许多聚合函数，包括AVG，COUNT，SUM，MIN，MAX等。除COUNT函数外，其它聚合函数在执行计算时会忽略NULL值。

​ 聚合函数的特点

　　1.每个组函数接收一个参数（字段名或者表达式），统计结果中默认忽略字段为NULL的记录
　　2.要想列值为NULL的行也参与组函数的计算，必须使用IFNULL函数对NULL值做转换。
　　3.不允许出现嵌套 比如sum(max(xx))
1
2
3

AVG函数

​ AVG()函数计算一组值的平均值。 它计算过程中是忽略NULL值的。

select avg(bbb) from test1;
select aaa, avg(bbb) from test1 group by aaa;
1
2

​ 如果select的字段列表除了聚合函数以外，没有其他字段，可以不用group by分组子句。否则必须搭配group by使用。

MAX()函数

​ MAX()函数返回一组值中的最大值，其语法如下所示 -

select aaa, max(bbb) from test1 group by aaa;
1

MIN()函数

​ MIN()函数返回一组值中的最小值，其语法如下所示 -

select aaa, min(bbb) from test1 group by aaa;
1

​

​ 现在我们设想一个应用场景，计算公司部门的员工最高工资和最低工资，先创建表。

drop table emp;
create table emp(
	emp_id int primary key auto_increment,
	emp_name varchar(20) comment '员工姓名',
	emp_dept varchar(20) comment '部门名称',
	salary decimal(10, 2) comment '工资',
	hiredate datetime comment '入职时间'
);

insert into emp(emp_name, emp_dept, salary, hiredate) values('赵大', '开发部', 4500, '2016-3-1');
insert into emp(emp_name, emp_dept, salary, hiredate) values('陈二', '开发部', 5000, '2015-5-6');
insert into emp(emp_name, emp_dept, salary, hiredate) values('张三', '开发部', 7000, '2012-7-4');
insert into emp(emp_name, emp_dept, salary, hiredate) values('李四', '测试部', 5500, '2015-3-5');
insert into emp(emp_name, emp_dept, salary, hiredate) values('王五', '测试部', 3500, '20180407');
insert into emp(emp_name, emp_dept, salary, hiredate) values('钱六', '销售部', 6000, '20170909');
insert into emp(emp_name, emp_dept, salary, hiredate) values('周七', '财务部', 5200, '20170709');
select * from emp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

​ 我们要如何统计各个部门的最高工资和最低工资呢？

select emp_dept, max(salary) from emp group by emp_dept;
select emp_dept, min(salary) from emp group by emp_dept;
1
2

​ 解释一下这个结果：
1、满足“SELECT子句中的列名必须为分组列或列函数”，因为SELECT有GROUP BY DEPT中包含的列DEPT。
2、“列函数对于GROUP BY子句定义的每个组各返回一个结果”，根据部门分组，对每个部门返回一个结果，就是每个部门的最高薪水。
3、分组查询可以在形成组和计算列函数之前具有消除非限定行的标准 WHERE 子句。必须在GROUP BY 子句之前指定 WHERE 子句。

select emp_dept, max(salary) from emp where hiredate between '20150101' and '2016-12-31' group by emp_dept;
1

COUNT()函数

​ COUNT()函数返回结果集中的行数。

select count(*) from emp;
select count(1) from emp;
select count(emp_id) from emp;
select emp_dept, count(emp_id) from emp group by emp_dept;
1
2
3
4

注意：count() 在统计时，会计入null值。

SUM()函数

​ SUM()函数返回一组值的总和，SUM()函数忽略NULL值。如果找不到匹配行，则SUM()函数返回NULL值。

select emp_dept, sum(salary) from emp group by emp_dept;
select emp_dept, sum(salary) from emp  where emp_dept='aaa' group by emp_dept;
1
2

Order by 子句

​ 如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
1
2

• 你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
• 你可以设定多个字段来排序。
• 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。

​ 我们来看几个例子

select * from emp order by salary;
--默认情况按升序排列
select * from emp order by salary desc;
--指定desc后按降序排列
select emp_dept, sum(salary) from emp group by emp_dept order by emp_dept;
--字符串也可以排序，排序依据为字符编码的二进制值
select emp_dept, sum(salary) from emp group by emp_dept order by emp_dept desc;
1
2
3
4
5
6
7

​ SELECT后被选择的列，可以在ORDER by和GROUP BY中，通过列名、列别名或者代表列位置的整数（从1开始）来引用。

select emp_dept, sum(salary) from emp group by emp_dept order by 1 desc;
1

用 union/union all来连接结果集

​ 如果想选择其他几个表中的行或从一个单一的表作为一个单独的结果集行的几个集会，那么可以使用的UNION。

​ UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。如果允许重复的值，请使用 UNION ALL。我们来创建一个场景，学生表和教师表

create table teacher(
	id int primary key auto_increment,
	teacher_name varchar(20),
	teacher_city varchar(20)
);
create table student(
	id int primary key auto_increment,
	student_name varchar(20),
	student_city varchar(20)
);
insert into teacher(teacher_name, teacher_city) values('赵大', '武汉');
insert into teacher(teacher_name, teacher_city) values('陈二', '鄂州');
insert into teacher(teacher_name, teacher_city) values('张三', '襄阳');

insert into student(student_name, student_city) values('李四', '宜昌');
insert into student(student_name, student_city) values('王五', '恩施');
insert into student(student_name, student_city) values('钱六', '黄石');
insert into student(student_name, student_city) values('周七', '孝感');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

​ 我们用两个查询分别取得两个表的结果集，然后连接。

select * from teacher
union
select * from student;
1
2
3

​ 如果要获取老师和学生来自哪些城市，则

select teacher_city from teacher
union
select student_city from student;
1
2
3

注意：

​ 1、UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名
​ 2、UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。union只关注数据类型，数据业务含义是否相同不管。

​ 我们再插入3条记录

insert into student(student_name, student_city) values('周七1', '武汉');
insert into student(student_name, student_city) values('周七2', '武汉');
insert into student(student_name, student_city) values('周七3', '武汉');
1
2
3

​ 比较一下3句sql

select * from teacher
union
select * from student;
select teacher_city from teacher
union
select student_city from student;
select teacher_city from teacher
union all
select student_city from student;
1
2
3
4
5
6
7
8
9

​ union会将结果集去重，它比较结果集中的全部字段，所有字段都相同的将被去除。union all 不去重。

union的用法及注意事项

​ union:联合的意思，即把两次或多次查询结果合并起来。
​ 要求：两次查询的列数必须一致
​ 推荐：列的类型可以不一样，但推荐查询的每一列，想对应的类型以一样
​ 可以来自多张表的数据：多次sql语句取出的列名可以不一致，此时以第一个sql语句的列名为准。
​ 如果不同的语句中取出的行，有完全相同(这里表示的是每个列的值都相同)，那么union会将相同的行合并， 最终只保留一行。也可以这样理解，union会去掉重复的行。
​ 如果不想去掉重复的行，可以使用union all。
​ 如果子句中有order by,limit，需用括号()包起来。推荐放到所有子句之后，即对最终合并的结果来排序或筛选。

select emp_dept, sum(salary) from emp group by emp_dept
union
select emp_dept, sum(salary) from emp group by emp_dept
order by emp_dept desc;
--或者这样
(select emp_dept, sum(salary) from emp group by emp_dept)
union all
(select emp_dept, sum(salary) from emp group by emp_dept)
order by emp_dept desc;
1
2
3
4
5
6
7
8
9

3.模糊查询🧁

糊查询 需要使用 like 占位符，几个案例学会模糊查询，下面是stus表的数据

脑图

mysql> select * from stus;
1

% 多个任意字符

1.查询 stus 表中 name 以猪开头的学生信息
mysql> select * from t_stu where name like "吴%";
1
2

2.查询 stus 表中 name 以三结尾的学生信息
 SELECT * FROM t_stu WHERE username LIKE '%三';
1
2

3.查询 stus 表中 name 中包含八的学生信息
select * from t_stu where username like '%八%';
1
2

_ 单个任意字符

4.查询 stus 表中 name 以张开头的且名字长度为2的学生信息
select * from t_stu where username like '张_';
1
2

5.查询 t_stu 表中 username 以刘开头的且名字长度为3的学生信息
select * from t_stu where username like '刘__';
1
2

6.查询 t_stu 表中 username 以建军结尾的且名字长度为3的学生信息
select * from t_stu where username like '_建军';
1
2

7.查询 t_stu 表中 username 以张开头以苗结尾的且名字长度为3的学生信息
select * from t_stu where username like '飞_飞';
1
2

4. 排序查询🍒

键字**：order by
关系型数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出来的数据的顺序有意义，可以使用 order by 子句对查询结果进行排序，默认是升序，即从小到大 ASC，如果想要降序排序，则需要指定 DESC

脑图

ASC 升序

1. 查询stus表中名字字段，要求按照年龄的升序进行排序
SELECT * FROM t_stu ORDER BY age ASC;
1
2

2. 查询stus表中名字,年龄字段，要求按照年龄大于7进行筛选之后按照年龄的升序进行排序
select name,age from stus where age > 7 order by age;
1
2

DESC 降序

3. 查询t_stu表中名字字段，要求按照年龄的升序进行排序
SELECT * FROM t_stu ORDER BY age DESC;

1
2
3

limit 选学

这个还没讲到，如果你不知道，可以跳过，后面会将到哦

4. 查询t_stu表中名字字段，要求按照年龄的升序进行排序，并通过limit返回一条数据，即年龄最小的
SELECT username FROM t_stu ORDER BY age ASC LIMIT 1;
1
2

位置问题

在使用 order by 子句对检索出的数据进行排序时，应该保证它是位于 from，如果有条件，应位于where之后，如果使用 limit ，它必须位于 order by 之后，使用子句的次序不对将产生错误消息

5. 聚合函数🥇

MySQL 提供了5个聚合函数，聚合函数能够汇总数据，这些函数是高效设计的，它们返回结果一般比你自己在客户机应用程序中计算要快的多

脑图

AVG()

返回某列的平均值
查看t_stu表求的年龄平均值

SELECT AVG(age) '年龄平均值' FROM t_stu;
1

COUNT()

返回某列的行数
查看t_stu表男性有多少人

SELECT COUNT(id)  '男性人数' FROM t_stu WHERE sex = '男';
1

MAX()

返回某列的最大值
找出表中最大年龄是多少

SELECT MAX(age) ''最大年龄 FROM t_stu;
1

MIN()

返回某列的最小值
找出表中最小年龄是多少

SELECT MIN(age) '最小年龄' FROM t_stu;

1
2

SUM()

返回某列之和
查询所有人的年龄之和

SELECT SUM(age) '年龄之和' FROM t_stu;
1

6. 分组查询🔎

脑图

分组

对t_stu表的数据按照性别进行分组，并查看男女都有多少人
SELECT  sex '性别',COUNT(*) '人数'  FROM t_stu GROUP BY sex ;
1
2

过滤

mysql> select gradeId,count(*) as num from stus group by gradeId having count(*) > 1;
+---------+-----+
| gradeId | num |
+---------+-----+
|       1 |   2 |
|       3 |   2 |
+---------+-----+
1
2
3
4
5
6
7

having和where的区别

• 执行时间不一样：where是分组之前进行限定，不满足where条件，就不参与分组，而having是分组之后对结果进行过滤
• 可判断的条件不一样：where 不能对聚合函数进行判断，having可以
• where > 聚合函数 > having

7. select子句查询顺序🖥️

select

from

where 行级过滤

group by 分组

having 组级过滤

order by 输出排序顺序

limit 要检索的条目数

8.数据操作语言-DML🥤

插入记录

插入全部字段

• 所有的字段名都写出来
  INSERT INTO 表名 (字段名1, 字段名2, 字段名3…) VALUES (值1, 值2, 值3);
• 不写字段名
  INSERT INTO 表名 VALUES (值1, 值2, 值3…);

插入部分数据

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
没有添加数据的字段会使用NULL

1. 关键字说明

   INSERT INTO 表名 – 表示往哪张表中添加数据
   (字段名1, 字段名2, …)  --  要给哪些字段设置值
   VALUES (值1, 值2, …); -- 设置具体的值
   1
   2
   3

2. 注意

   • 值与字段必须对应，个数相同，类型相同
   • 值的数据大小必须在字段的长度范围内
   • 除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）
   • 如果要插入空值，可以不写字段，或者插入null

3. 具体操作:

   • 插入部分数据，往学生表中添加 id, name, age, sex数据

   INSERT INTO student (id, NAME, age, sex) VALUES (1, '张三', 20, '男');
   1

   

   • 向表中插入所有字段

     • 所有的字段名都写出来

      INSERT INTO student (NAME, id, age, sex, address) VALUES ('李四', 2, 23, '女', '广州');
     1

     

     • 不写字段名

     INSERT INTO student VALUES (3, '王五', 18, '男', '北京');
     1

     

操作日期时间类型字段

在MySQL中，操作日期时间类型，需要注意以下几个方面

第一种：YEAR（了解）

1、数字或字符形式的值insert到表中YEAR字段后都是变成数字形式显示；

2、字符的’0’与’00’会转化为2000，字符‘0000’和数字的0与00则直接是0000；

3、不论字符还是数字形式的1~99都可以转化为对应年份，<70转成20XX，>=70转成19XX。

4、大于0，小于1901或超过2155是无法写入YEAR类型字段的。

第二种：TIME

1、尽量还是用字符形式做参数；

2、只有一个数字则表示秒，如果有两个数字和一个“：”则自动补充“00”作为秒；

3、前面一个数字加空格后跟着带“：”串，则前面数字要乘以24加上第一个“：”前的数字作为第一个“：”前的结果，其他不变。

第三种：DATE

1、YYYYMMDD与YYMMDD格式不论数字还是字符，都可以写入到DATE类型字段中；

2、YYMMDD格式中表示YY数字00_{69转化为2000}2069，而70_{99则转化为1970}1999。

第四与第五种：DATETIME与TIMESTAMP

1、TIMESTAMP未指定值的情况下会自动填充系统时间；

2、TIMESTAMP超出范围的值不能写入；

3、YYYY-MM-DD HH:MM:SS与YYYYMMDDHHMMSS格式都可以表示DATETIME与TIMESTAMP。

虽然MySQL支持直接在sql里写字符串形式的日期时间，但是这依赖于系统自带的日期数据格式，如果跟提供的日期时间串不匹配则不能正确解析，建议使用日期时间转换函数

select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30
select maketime(12,15,30); -- '12:15:30'
select date_format('2008-08-08 22:23:01', '%Y-%m-%d %H:%i:%s');  -- 20080808222301
1
2
3

DOS命令窗口操作数据乱码问题的解决

当我们使用DOS命令行进行SQL语句操作如有有中文会出现乱码，导致SQL执行失败

错误原因:因为MySQL的客户端设置编码是utf8,而系统的DOS命令行编码是gbk，编码不一致导致的乱码
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vBAMS6O1-1660318360433)(D:\Program Files (x86)]\gakataka\Student\recv\第2阶段___JavaWeb\Java第二阶段_day02_MySQL进阶\Java第二阶段_day02_MySQL进阶知识点\Java第二阶段_day02_MySQL进阶.assets\DOS中文乱码02.png)

查看 MySQL 内部设置的编码
show variables like 'character%';

解决方案：修改client、connection、results的编码为GBK，保证和DOS命令行编码保持一致

1. 单独设置

   set character_set_client=gbk; 
   set character_set_connection=gbk;
   set character_set_results=gbk;
   1
   2
   3

2. 快捷设置

   set names gbk;
   1

   注意：以上2种方式为临时方案，退出DOS命令行就失效了，需要每次都配置

3. 修改MySQL安装目录下的my.ini文件，重启服务所有地方生效。此方案将所有编码都修改了
   

蠕虫复制

什么是蠕虫复制：在已有的数据基础之上，将原来的数据进行复制，插入到对应的表中
语法格式：INSERT INTO 表名1 SELECT * FROM 表名2;
作用:将表名2中的数据复制到表名1中

具体操作:

• 创建student2表，student2结构和student表结构一样

CREATE TABLE student2 LIKE student;
1

• 将student表中的数据添加到student2表中

INSERT INTO student2 SELECT * FROM student;
1

注意：如果只想复制student表中name,age字段数据到student2表中使用如下格式
INSERT INTO student2(NAME, age) SELECT NAME, age FROM student;

更新表记录

1. 不带条件修改数据
   UPDATE 表名 SET 字段名=值;

2. 带条件修改数据
   UPDATE 表名 SET 字段名=值 WHERE 字段名=值;

3. 关键字说明

   UPDATE: 修改数据
   SET: 修改哪些字段
   WHERE: 指定条件
   1
   2
   3

4. 具体操作：

   • 不带条件修改数据，将所有的性别改成女

     UPDATE student SET sex='女';
     1

     

   • 带条件修改数据，将id号为2的学生性别改成男

     UPDATE student SET sex='男' WHERE id=2;
     1

     

   • 一次修改多个列，把id为3的学生，年龄改成26岁，address改成北京

     UPDATE student SET age=26, address='北京' WHERE id=3;
     1

     

删除表记录

1. 不带条件删除数据
   DELETE FROM 表名;

2. 带条件删除数据
   DELETE FROM 表名 WHERE 字段名=值;

3. truncate删除表记录
   TRUNCATE TABLE 表名;

   truncate和delete的区别：

   • delete是将表中的数据一条一条删除
   • truncate是将整个表摧毁，重新创建一个新的表,新的表结构和原来表结构一模一样

4. 具体操作：

   • 带条件删除数据，删除id为3的记录

     DELETE FROM student WHERE id=3;
     1

     

   • 不带条件删除数据,删除表中的所有数据

     DELETE FROM student;
     1

     

DROP TABLE IF EXISTS s1;
1

9. SQL约束❤️

数据完整性
数据完整性是为了保证插入到数据库中的数据是正确的,防止用户可能的错误输入。
数据完整性分为实体完整性、域完整性、参照完整性。

实体(行)完整性

(实体完整性中的实体指的是表中的行,因为一行记录对应一个实体)
实体完整性规定表的一行在表中是唯一的实体,不能出现重复。
实体完整性通过表的主键来实现。

域(列)完整性

指数据库表的列（即字段）必须符合某种特定的数据类型或约束
非空约束：NOT NULL
唯一约束：UNIQUE

参照完整性

参照完整性指的就是多表之间的关系设计，主要使用外键约束。

约束英文：constraint

​ 约束实际上就是表中数据的限制条件

​ 表在设计的时候加入约束的目的就是为了保证表中的记录完整和有效。

​ 约束分类：
1）not null ：非空约束，保证字段的值不能为空

2）default：默认约束，保证字段总会有值，即使没有插入值，都会有默认值！

3）unique：唯一，保证唯一性但是可以为空，比如座位号

4）primary key ：主建约束，同时保证唯一性和非空

5）foreign key：外键约束，用于限制两个表的关系，保证从表该字段的值来自于主表相关联的字段的值(此节与表之间的关系一起讲)。

6）check：检查性约束，限制字段输入值的范围

非空约束

​ 用not null约束的字段不能为null值，必须给定具体的数据

​ 创建非空约束的方法：

1、建表时创建

create table tc_1(
  aaa int not null,
  bbb varchar(20) not null,
  ccc datetime not null
);
1
2
3
4
5

2、在已创建好的表的字段上去除、添加非空约束

alter table tc_1 modify aaa int;
alter table tc_1 modify bbb varchar(20) not null;
1
2

​ 非空约束不可以指定约束名称，在系统约束列表中也找不到对应的记录。

SELECT * FROM information_schema.TABLE_CONSTRAINTS where table_schema='test';
1

3、删除表时会删除表上全部的非空约束。

唯一性约束

​ unique约束的字段，具有唯一性，不可重复，但可以为null。

​ 创建唯一性约束的方法：

1、建表时创建

--列约束定义模式
create table tc_2(
  aaa int unique,
  bbb varchar(20) unique,
  ccc datetime unique
);
--表约束定义模式
--表级约束可以给约束起名字(方便以后通过这个名字来删除这个约束)
create table tc_3(
  aaa int,
  bbb varchar(20),
  ccc datetime,
  unique(aaa),
  unique(bbb),
  constraint u_ccc unique(ccc)
);
--组合约束模式
create table tc_4(
  aaa int,
  bbb varchar(20),
  ccc datetime,
  unique(aaa, bbb),
  unique(ccc)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

2、在已创建好的表的字段上去除唯一约束

-- 找到对应的唯一约束名称
SELECT * FROM information_schema.TABLE_CONSTRAINTS where table_schema='test'
  and table_name='tc_3';
-- 唯一约束同时也是索引，还可以用索引方式找到
show index from tc_3;
-- 用删除索引的方法来删除唯一约束
drop index u_ccc on tc_3;
-- 在删除表时会自动删除表上全部的唯一约束
1
2
3
4
5
6
7
8

3、在已创建好的表的字段上追加唯一约束

--以约束模式添加
ALTER TABLE tc_3 ADD unique(ccc);
--以索引模式添加
create unique index u_ccc on tc_3 (ccc);
1
2
3
4

默认值约束

​ “默认值（Default）”的完整称呼是“默认值约束（Default Constraint）”。MySQL默认值约束用来指定某列的默认值。

​ 创建默认值约束

create table tc_5(
  aaa int default 0,
  bbb varchar(20) default 'aaa',
  ccc datetime default '2020-1-1'
);
insert into tc_5 values();
1
2
3
4
5
6

​ 在已存在的表字段上添加、修改默认值约束

alter table tc_5 modify aaa int default 5;
alter table tc_5 modify bbb varchar(20) default 'bbb';
1
2

​ 删除已存在的默认值约束

alter table tc_5 modify aaa int default null;
alter table tc_5 modify bbb varchar(20) default null;
-- 在删除表时会自动删除表上全部的默认约束
1
2
3

​ 与非空约束类似，默认值约束也无法指定名称，也无法在 information_schema.TABLE_CONSTRAINTS 表中查到。

主键约束

​ 表中的某个字段添加**主键约束(primary key)**后，该字段为主键字段，主键字段中出现的每一个数据都称为主键值

​ 1、主键约束（PRIMARY KEY）

​ 1) 主键用于唯一地标识表中的每一条记录，可以定义一列或多列为主键。
​ 2) 是不可能（或很难）更新．
​ 3) 主键列上没有任何两行具有相同值（即重复值），不允许空（NULL）．
​ 4) 主健可作外健，唯一索引不可；

​ 2、唯一性约束（UNIQUE）
​ 1) 唯一性约束用来限制不受主键约束的列上的数据的唯一性，用于作为访问某行的可选手段，一个表上可以放置多个唯一性约束．
​ 2) 只要唯一就可以更新．
​ 3) 即表中任意两行在 指定列上都不允许有相同的值，允许空（NULL）．
​ 4) 一个表上可以放置多个唯一性约束

	3、唯一性约束和主键约束的区别：
（1）.唯一性约束允许在该列上存在NULL值，而主键约束的限制更为严格，不但不允许有重复，而且也不允许有空值。
（2）.在创建唯一性约束和主键约束时可以创建聚集索引和非聚集索引，但在 默认情况下主键约束产生**聚集索引**，而唯一性约束产生**非聚集索引**。
1
2
3

​ 在建表时创建主键约束

-- 在列数据库类型后加入主键关键字
create table tc_6(
  aaa int primary key,
  bbb varchar(20),
  ccc datetime
);
-- 在所有字段声明完毕后，加入主键声明
create table tc_6(
  aaa int,
  bbb varchar(20),
  ccc datetime,
  primary key(aaa)
);
-- 第2种模式下，可以创建成联合主键（复合主键）
create table tc_6(
  aaa int,
  bbb varchar(20),
  ccc datetime,
  primary key(aaa， bbb)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

​ 在已创建的表上建立主键约束

create table tc_7(
  aaa int,
  bbb varchar(20),
  ccc datetime
);
alter table tc_7 add primary key(aaa);
alter table tc_7 add primary key(aaa, bbb);
1
2
3
4
5
6
7

​ 主键约束可以在 information_schema.TABLE_CONSTRAINTS 表中查找到，其约束名称固定为 PRIMARY。

​ 删除已存在主键约束

alter table tc_7 drop primary key;
-- 在删除表时会自动删除表上的主键约束
1
2

​ MySQL不支持直接修改主键索引，想要重定义一个表的主键索引，只能先删除原来的索引，再加入新的索引。

检查约束

​ 检查约束可以限制新增 、修改字段值，使得其符合约束指定的规则

​ 在创建表时建立检查约束

create table tc_9(
  aaa int primary key,
  bbb varchar(20),
  ccc datetime,
  check(aaa > 100 and aaa<1000)
);
create table tc_9(
  aaa int primary key,
  bbb varchar(20),
  ccc datetime,
  constraint c_tc_9 check(aaa > 100 and aaa<1000)
);
1
2
3
4
5
6
7
8
9
10
11
12

​ 可以指定约束的名称，否则数据库管理系统将自行定义约束名称。

insert into tc_9 values(123, 'aaa', '1-2-3');
-- 不满足约束的数据，无法插入记录表
insert into tc_9 values(23, 'aaa', '1-2-3');
insert into tc_9 values(1234, 'aaa', '1-2-3');
1
2
3
4

​ 检查约束可以作用于数值类型、日期类型、字符串类型

create table tc_9(
  aaa int primary key,
  bbb varchar(20),
  ccc datetime,
  constraint c_tc_9a check(aaa > 100 and aaa<1000),
  constraint c_tc_9b check(bbb in ('男', '女')),
  constraint c_tc_9c check(ccc > '2000-1-1')
);

insert into tc_9 values(123, '男', '2001-2-3');
-- 不满足约束的数据，无法插入记录表
insert into tc_9 values(223, 'aaa', '2001-2-3');
insert into tc_9 values(223, '女', '1995-2-3');
1
2
3
4
5
6
7
8
9
10
11
12
13

​ 在已创建的表上追加检查约束

alter table tc_9 add constraint c_tc_9d check(ccc < '2020-1-1');
insert into tc_9 values(223, '女', '2025-2-3');
1
2

​ 注意：追加检查约束时，会检查相应字段的值，如果不满足检查约束的限制，检查约束无法创建。

​ MySQL不支持修改已存在的检查约束，但是可以删除这个约束，再重新添加。

alter table tc_9 drop check c_tc_9d;
alter table tc_9 add constraint c_tc_9d check(ccc < '2020-1-1');
1
2

自动增长

​ 当对应的字段不给值，或者说默认值，或者给null的时候会自动地被系统触发，系统会从当前字段中已有的最大值再进行+1，得到一个新的不同的值，通常和主键搭配。

​ 自增长的特点 ：

a)、任何一个字段做自增长前提 自身是一个索引(key一栏有值)。

b)、自增长字段通常是整形。

c)、一个表中只能有1个自增长。

create table if not exists my_auto_increment(
  id int primary key auto_increment,
  name varchar(10) not null
) charset utf8;
1
2
3
4

自增长如果对应的字段输入了值，那么自增长失效，但是下一次还是能够正确的自增长(从最大值+1)。

​ 修改自增长。

自增长如果是涉及到字段改变，必须先删除自增长，后增加，一张表中只能有1个自增长。

修改当前已存在自增长的值，修改只能比当前已有的自增长的最大值大，不能小(小则不生效)。

​ 删除自增增长。

​ 自增长是字段的一个属性，可以通过modify属性来修改(字段没有自增长)。

​ 语法：alter table 表名字 modify 字段名 数据类型

alter table my_auto_increment modify id int;
1

​ 自增长为什么从1开始？又为什么每次都是自增1呢？

​ 所有系统的表现(如字符集、校对集)都是由系统的内部变量进行控制的。

查看自增长对应的变量的语法：show variables like ‘auto_increment%’;

​ 可以修改变量实现不同的效果。如果对整个数据修改(而不是但张表)，这种方式是修改时会话级(当前客户端，当此连接有效，关闭失效)。

--修改成一次自增为5
set auto_increment_increment = 5;
1
2

10. 多表查询😋

脑图

试着查询两张表

​ 在实际运用数据的场景中，往往我们使用的数据并不是来自一个表，而需要从多个表中抽取数据来形成我们需要的结果集。

​ SQL 连接子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。连接的结果可以在逻辑上看作是由SELECT语句指定的列组成的新表。

等值连接与非等值连接

​ 连接条件是使用等式控制限定，就是等值连接。使用 !=、>、<等非=号来控制里连接条件的，就是非等值连接。

交叉连接 – 笛卡尔乘积 cross join

​ 当两个表或多个表，没有通过连接字段进行关联，而直接进行连接，这时会形成笛卡尔积，结果集会是所有参与连接的表的条数的乘积。

select a.teacher_name, b.student_name
from teacher a, student b;
select a.teacher_name, b.student_name, c.bbb
from teacher a, student b, test01 c order by a.teacher_name;
select a.teacher_name, b.student_name from teacher a
	cross join student b;
1
2
3
4
5
6

​ 笛卡尔积通常是没有意义的，不满足业务需求，实际运用中应避免笛卡尔积的形成。

​ 不要漏写关联字段！！！ 不要漏写关联字段！！！ 不要漏写关联字段！！！

内连接 inner join

关键字：INNER JOIN

连接结果仅包含符合连接条件的行组合起来作为结果集，参与连接的两个表都应该符合连接条件使用关键词：INNER JOIN 连接多张表

​ 内连接使用比较运算符对两个表中的数据进行比较，并列出与连接条件匹配的数据行，组合成新的纪录。结果只保留满足条件的记录。

​ 我们来重新创建教师表和学生表，建立一对多的关系。

drop table teacher;
create table teacher(
	id int primary key auto_increment,
	teacher_name varchar(20),
	teacher_city varchar(20)
);
drop table student;
create table student(
	id int primary key auto_increment,
	student_name varchar(20),
	student_city varchar(20),
	teacher_id int
	-- CONSTRAINT fk_t_s foreign key (teacher_id) references teacher(id) on update cascade on delete cascade
    -- 单行注释用 "-- " 注意这个风格下"--【空格】" 也就是说“--" 与注释之间是有空格的
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

insert into teacher(teacher_name, teacher_city) values
	('赵大', '武汉'), ('陈二', '鄂州'), ('张三', '襄阳');
insert into student(student_name, student_city, teacher_id ) values
	('李四', '宜昌', 1), ('王五', '恩施', 1), ('钱六', '黄石', 1),
	('周七', '孝感', 2), ('胡八', '武汉', 2);
1
2
3
4
5

数据准备完成后，我们来看看如何内连接两个表。

select a.teacher_name, b.student_name
	from teacher a, student b where a.id = b.teacher_id;
-- 或者是
select a.teacher_name, b.student_name
from teacher a
	INNER JOIN student b on a.id = b.teacher_id;
1
2
3
4
5
6

​ 得到的结果是左边的表，去匹配右边的表，只取得左右两边都能匹配上的数据，匹配不上的数据抛弃。

​ 左表中一条记录，对应右表中多条记录的，左表记录重复多次，每次匹配右表中的一条独立的记录。

​ 我们再插入一些不构成关联的教师和学生的数据，来看看内关联如何处理。

insert into teacher(teacher_name, teacher_city) values
	('王胖', '武汉');
insert into student(student_name, student_city, teacher_id ) values
	('大金牙', '武汉', 20);
1
2
3
4

隐式内连接

内连接还有一种隐式的写法，即不需要显示的指定 INNER JOIN 关键字，需要注意，使用隐式内连接条件的关键字要使用 where 而不再是 on

select a.teacher_name, b.student_name
	from teacher a, student b where a.id = b.teacher_id;
1
2

左外连接 left out join

​ 是指以左边的表的数据为基准，去匹配右边的表的数据，如果匹配到就显示，匹配不到就显示为null。关键字是left outer join，等效于left join。

select a.teacher_name, b.student_name
from teacher a
	left outer join student b on a.id = b.teacher_id;
select a.teacher_name, b.student_name
from teacher a
	left join student b on a.id = b.teacher_id;
	
select a.teacher_name, b.student_name
from teacher a
	left join student b on a.id = b.teacher_id
where b.teacher_id is not null;
-- 这句sql等效于内连接
select a.teacher_name, b.student_name
from teacher a
	left join student b on a.id = b.teacher_id
where b.teacher_id is null;
-- 这句sql取出没有学生对应的老师
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

​ 得到的结果是左边的表，完整的全表数据，去匹配右边的表，取得右边能匹配上的数据，匹配不上的数据填null。

​ 左表中一条记录，对应右表中多条记录的，左表记录重复多次，每次匹配右表中的一条独立的记录。

右外连接 right outer join

​ 是指以右边的表的数据为基准，去匹配左边的表的数据，如果匹配到就显示，匹配不到就显示为null。关键字是right outer join，等效于right join。

select a.teacher_name, b.student_name
from teacher a
	right outer join student b on a.id = b.teacher_id;
select a.teacher_name, b.student_name
from teacher a
	right join student b on a.id = b.teacher_id;
	
	
select a.teacher_name, b.student_name
from teacher a
	right join student b on a.id = b.teacher_id
where a.id is not null;
-- 这句sql等效于内连接
select a.teacher_name, b.student_name
from teacher a
	right outer join student b on a.id = b.teacher_id
where a.id is null;
-- 这句sql取出没有老师对应的学生
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

​ 得到的结果是右边的表，取完整的全表数据，去匹配左边的表，取得左边能匹配上的数据，匹配不上的数据填null。

​ 右表中一条记录，对应左表中多条记录的，右表记录重复多次，每次匹配左表中的一条独立的记录。

全外连接 full outer join

​ 全外连接是在内连接的基础上将左表和右表的未匹配数据都加上。

SELECT * 
FROM TESTA 
FULL OUTER JOIN TESTB
ON TESTA.A=TESTB.A
1
2
3
4

​ 注意：MySQL不支持全外连接，但是Oracle支持全外连接。

​ 其等效于

select a.teacher_name, b.student_name
from teacher a
	left outer join student b on a.id = b.teacher_id
union  -- 注意这里需要使用去重，而不是union all
select a.teacher_name, b.student_name
from teacher a
	right outer join student b on a.id = b.teacher_id;
1
2
3
4
5
6
7

自然连接 natural join

​ 通过mysql自己判断完成连接过程,不需要指定连接条件，mysql会根据多个表内的相同字段作为连接条件。

​ 自然连接分为两种：内自然连接(natural join)和外自然连接，其中外自然连接又分为左外自然连接(natural left join)和右外自然连接(rnatural right join)。注意：自然连接没有判断语句。

​ 内自然连接如下：

select a.teacher_name, b.student_name
from teacher a
	natural join student b;
-- 这实际等效于以下sql
select a.teacher_name, b.student_name
from teacher a
	inner join student b on a.id = b.id;
1
2
3
4
5
6
7

​ 所以使用自然连接的前提条件是连接字段必须同名。

​ 我们来重新创建教师表和学生表

drop table teacher;
create table teacher(
	teacher_id int primary key auto_increment,
	teacher_name varchar(20),
	teacher_city varchar(20)
);
drop table student;
create table student(
	student_id int primary key auto_increment,
	student_name varchar(20),
	student_city varchar(20),
	teacher_id int
);
insert into teacher(teacher_name, teacher_city) values
	('赵大', '武汉'), ('陈二', '鄂州'), ('张三', '襄阳');
insert into student(student_name, student_city, teacher_id ) values
	('李四', '宜昌', 1), ('王五', '恩施', 1), ('钱六', '黄石', 1),
	('周七', '孝感', 2), ('胡八一', '武汉', 2);
insert into teacher(teacher_name, teacher_city) values
	('王胖子', '武汉');
insert into student(student_name, student_city, teacher_id ) values
	('大金牙', '武汉', 20);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

左外自然连接：

select a.teacher_name, b.student_name
from teacher a
	natural left outer join student b;
-- 其等效于以下sql
select a.teacher_name, b.student_name
from teacher a
	left join student b on a.teacher_id = b.teacher_id;
1
2
3
4
5
6
7

右外自然连接：

select a.teacher_name, b.student_name
from teacher a
	natural right outer join student b;
-- 其等效于以下sql
select a.teacher_name, b.student_name
from teacher a
	right join student b on a.teacher_id = b.teacher_id;
1
2
3
4
5
6
7

自连接

​ 自连接是连接的一种用法，但并不是连接的一种类型，因为他的本质是把一张表当成两张表来使用。 处理业务是有时在信息查询时需要进行对自身连接（自连接），所以我们需要为表定义别名。

select ta.student_name, tb.student_city
from studnet ta， student tb
where ta.student_id = tb.student_id;
1
2
3