数据库

1, 数据库的安装和配置

1.1 数据库的概念

数据库的定义: 了解

1, Database：A database is an organized collection of data,stored and accessed electronically.

2, 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。

3, 数据库(Database)是一种结构化信息或数据的有组织的集合。

4, 在现实中，数据以及[数据库管理系统 (DBMS)](Database Management System) 一起被称为数据库系统，通常简称为数据库。

数据库的历史: 了解

自 20 世纪 60 年代初诞生至今，数据库已经发生了翻天覆地的变化。最初，人们使用分层数据库（树形模型/一对多）和网络数据库（图模型/多对多）这样的导航数据库来存储和操作数据。这些早期系统是很简单的，同时也缺乏灵活性。

20 世纪 80 年代，关系数据库开始兴起.

20 世纪 90 年代，面向对象的数据库开始成为主流。

最近，随着互联网的快速发展，为了更快速地处理非结构化数据，NoSQL 数据库应运而生。

1.2 数据库的分类

数据库分类

注: 数据库热度 数据库市场份额

• 关系型数据库：关系数据库在 20 世纪 80 年代成为主流。关系数据库中的数据项被组织为一系列具有列和行的表。关系数据库为访问结构化信息提供了有效和灵活的方法.

• 非关系型数据库：又被称为NoSQL数据库，它支持存储和操作非结构化及半结构化数据。而且随着 Web 应用的日益普及和复杂化，NoSQL 数据库得到了越来越广泛的应用.

• 等...

注: 区别 注意

SQL标准/SQL语言: 1986年定义了一个SQL标准: SQL标准语言 (SQL语言操作数据, 是以数据和数据之前的关系去操作数据的)

SQL语句: select * from user; 从user表中查出所有信息

关系型数据库和非关系型数据库, 虽然区别很多, 但是最本质的区别是: 关系型数据库以数据和数据之间存在的关系维护数据, 而非关系型数据库是指存储数据的时候数据和数据之前没有什么特定关系.

大家要注意的是, 虽然数据库有种类的区别，但是这是一个无关孰优孰劣的问题(没有谁好谁差之分)，主要取决于企业希望如何使用数据.从本质上讲, 他们都是用来存储数据的. (而对于我们一个Java后端开发来讲, 我们在实际工作中基本上是以关系型数据库为主, 非关系型数据库为辅的用法)

注: 关于NoSQL的SQL解释

SQL：结构化查询语言（Structured Query Language）简称SQL，是一种专门用来和数据库通信的标准语言，用于向数据库存取数据以及查询、更新和管理关系数据库系统. 每个关系型数据库都支持SQL语言/SQL标准.

一些数据库: 了解

MySQL

是一个由瑞典MySQLAB公司开发，属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一

Oracle

又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小微机环境。它是一种高效率的、可靠性好的、适应高吞吐量的数据库方案.

SQLServer

SQLServer是由微软公司开发的一种关系型据库管理系统，它已广泛用于电子商务、银行、保险、电力等行业。SQLServer提供了对XML和Internet标准的支持，具有强大的、灵活的、基于Web的应用程序管理功能。而且界面友好、易于操作，深受广大用户的喜爱，但它只能在Windows平台上运行(2017年开始支持Linux系统)，并对操作系统的稳定性要求较高，因此很难处理日益增长的用户数量。

DB2数据库

DB2数据库是由IBM公司研制的一种关系型数据库管理系统，主要应用于OS/2、Windows等平台下，具有较好的可伸缩性，可支持从大型计算机到单用户环境。

DB2支持标准的SQL，并且提供了高层次的数据利用性、完整性、安全性和可恢复性，以及从小规模到大规模应用程序的执行能力，适合于海量数据的存储，但相对于其他数据库管理系统而言，DB2的操作比较复杂。

MariaDB

MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB来代替MySQL的InnoDB。 MariaDB由MySQL的创始人Michael Widenius主导开发，他早前曾以10亿美元的价格，将自己创建的公司MySQL AB卖给了SUN，此后，随着SUN被甲骨文收购，MySQL的所有权也落入Oracle的手中。MariaDB名称来自Michael Widenius的女儿Maria的名字。

MariaDB基于事务的Maria存储引擎，替换了MySQL的MyISAM存储引擎，它使用了Percona的 XtraDB，InnoDB的变体，分支的开发者希望提供访问即将到来的MySQL 5.4 InnoDB性能。这个版本还包括了 PrimeBase XT (PBXT) 和 FederatedX存储引擎。

PostgreSQL

是以加州大学伯克利分校计算机系开发的 Postgres 版本 4.2 为基础的对象关系型数据库管理系统（ORDBMS）。和MySQL一样是开源数据库。POSTGRES 领先的许多概念只是在非常迟的时候才出现在商业数据库中。

MongoDB数据库

MongoDB是由10gen公司开发的一个介于关系数据库和非关系数据库之间的产品，是非关系数据库当中功能最丰富，最像关系数据库的。它支持的数据结构非常松散，是类似JSON的bjson格式，因此可以存储比较复杂的数据类型。

Mongo数据库管理系统最大的特点是它支持的查询语言非常强大，其语法有点类似于面向对象的查询语言，可以实现类似关系数据库单表查询的绝大部分功能，而且还支持对数据建立索引。不仅如此，它还是一个开源数据库，并且具有高性能、易部署、易使用、存储数据非常方便等特点。对于大数据量、高并发、弱事务的互联网应用，MongoDB完全可以满足Web2.0和移动互联网的数据存储需求。

Redis

Remote Dictionary Server(Redis) 是一个由 Salvatore Sanfilippo 写的 key-value 存储系统，是跨平台的非关系型数据库。 Redis 是一个开源的使用 ANSI C 语言编写、遵守 BSD 协议、支持网络、可基于内存、分布式、可选持久性的键值对(Key-Value)存储数据库，并提供多种语言的 API。

1.3 数据库的安装

1.数据库安装

安装数据库: 安装的是一个具有监听端口/分析SQL语句/存储数据/组织数据/响应数据为一体的数据库服务器

xxxxxxxxxx
33
1
// 更新包列表
2
sudo apt update
3
// 安装mysql
4
sudo apt install mysql-server mysql-client
5
// 安装客户端依赖包(编程需要)
6
sudo apt install libmysqlclient-dev
7
// 查看mysql状态
8
netstat -tap | grep mysql
9

10
// 设置账号密码相关的信息 (选项选Y)(密码级别选0)
11
sudo mysql_secure_installation
12

13
// 查看mysql状态
14
systemctl status mysql.service
15
// 也可用于查看mysql状态
16
sudo service mysql status
17

18
// 访问mysql 
19
sudo mysql -u root -p
20

21
// 设置密码长度 (SHOW VARIABLES LIKE 'validate_password%';)
22
set global validate_password.length=6;
23
// 修改用户的认证方式和密码
24
alter user 'root'@'localhost' identified with mysql_native_password by '123456';
25
// 刷新
26
flush privileges;
27
// 退出mysql客户端
28
exit;
29

30
// 重启mysql
31
service mysql restart
32
//或者
33
systemctl restart mysql

2.客户端连接数据库

3.Navicat连接数据库

1.4 数据库构成

客户端和服务器

在互联网的网络交互和数据访问中,一般常见两种网络架构模式: B/S结构或者C/S结构.

B/S: Browser-Server即浏览器和服务器, 即通过浏览器和服务器发起网络交互的数据请求.

C/S: Client-Server即客户端和服务器, 即通过客户端和服务器发起网络交互的数据请求.

而我们上面安装的Navicat以及MySQL, 这两者的关系是一个标准的C/S结构.

MySQL的内部数据组织方式

在MySQL中, 我们对数据的组织逻辑上是按照库/表/数据 这种三级结构组织的.

数据库: 表示一份完整的数据仓库, 在这个数据仓库中分为多张不同的表.

表: 表示某种特定类型数据的的结构化清单, 里面包含多条数据.

数据: 表中数据的基本单元.

2, SQL语法

2.1 SQL语言

SQL语言

SQL：结构化查询语言（Structured Query Language）简称SQL，是一种专门用来和关系型数据库通信的标准语言，用于向数据库存取数据以及查询、更新和管理关系数据库系统。

与其他语言(Java, C++...)不同的是, SQL由很少的词构成, 这是希望从数据库读写数据时能以更简单有效的方法进行.

SQL有如下优点

SQL语言不是某个特定的数据库提供的语言, 它是一种数据库标准语言.(最初由美国国家标准局 ANSI于1986年完成第一版SQL标准的定义,即SQL-86).这也就意味着每个关系型数据库都支持SQL语言.

SQL简单易学, 是由多个描述性很强的单词构成, 并且这些单词数量不多.

SQL尽管看上去很简单, 但是非常强有力; 灵活的使用SQL, 可以进行比较复杂的和高级的数据库操作.

SQL不区分大小写(除非在某些数据库使用的时候做了特殊设置-不建议这种行为).

2.2 SQL的基本操作

2.2.1 登录数据库

mysql -u root -p [回车]

输入密码

注1 : 注释

xxxxxxxxxx
5
1
-- <注释>; # 注释语句
2
# <注释>;  # 注释语句
3
/*
4
    <注释>   # 注释语句
5
*/    

注意:

在数据库语句中如果我们需要注释某些内容, 一般有三种方式

-- 注释符(要注意的是--之后要有一个空格再接着书写注释内容)

# 注释符 (之后不需要空格)

/* */ 注释符 (一般用于多行注释)

注2: 分号

SQL语句应该要以分号作为结束

2.2.2 库操作

查看数据库

show databases; # 查看所有数据库

show databases like '%数据库名%'; # 查看和期望命名相匹配的数据库

show create database 数据库名; # 查看数据库创建信息

xxxxxxxxxx
2
1
// show databases like '%数据库名%'; 
2
// %代表是一个通配符: 通配0-n个字符

xxxxxxxxxx
5
1
 show databases like 'test'; -- 指明就找test
2
 show databases like '%n';  -- 一个以n字符结束的数据库
3
 show databases like '%n%'; --  数据库名字中, 有一个n字符 
4

5
show create database test; -- 查看之前怎么创建的test数据库(sql语句是什么)

注: 了解(不需要专门记忆)

• information_schema：主要存储了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息和分区信息等。

• mysql：MySQL 的核心数据库，主要负责存储数据库用户、用户访问权限等 MySQL 自己需要使用的控制和管理信息。常用的比如在 mysql 数据库的 user 表中修改 root 用户密码。

  update mysql.user set authentication_string=password('123456') where user='root';

  flush privileges;

• performance_schema：主要用于收集数据库服务器性能参数。

• sys：sys 数据库主要提供了一些视图，数据都来自于 performation_schema，主要是让开发者和使用者更方便地查看性能问题。

创建数据库

xxxxxxxxxx
14
1
CREATE DATABASE [IF NOT EXISTS] <数据库名>
2
[[DEFAULT] CHARACTER SET <字符集名>] 
3
[[DEFAULT] COLLATE <校对规则名>];
4

5
eg:  
6

7
--  创建一个db47的数据库, 有可能创建失败直接报错(假如数据库服务里面已经有一个db47的数据库了)
8
create database db47;
9
-- 创建一个叫test数据库, 并且要求编码格式是utf8,  还要求排序规则utf8_bin
10
create database test character set utf8 collate utf8_bin; 
11
-- 如果不存在名字为db47的数据库, 就创建db47, 如果已经存在了db47的数据库, 就不创建(也不报错)
12
create database if not exists db47; 
13
-- 创建一个指定字符编码格式的和指定排序规则的数据库
14
create database if not exists test character set utf8 collate utf8_bin; 

• [ ] 可选。

• <数据库名>：创建数据库的名称。

• IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作; 如果这个数据库存在, 就不创建/不执行这个sql。

• [DEFAULT] CHARACTER SET：指定数据库的字符集。

• [DEFAULT] COLLATE：指定字符集的默认校对规则。

注:一些注意事项

• 注意我们MySQL创建数据库的时候, 如果没有指明编码格式, 那么创建出了数据库在存储字符串的时候, 很多系统默认使用latin1的编码格式 (latin1存储一个字符的时候一个字符占用1个字节, 并且不能存储中文, 中文没有办法用一个字节表示: 所以在实际工作的时候, 我们更应该使用utf8 or utf8mb4)

• MySQL中字符集: UTF8 和UTF8MB4的区别

xxxxxxxxxx
4
1
// (1)  5.5.3 版本以后的才支持UTF8MB4
2
// (2)  UTF8MB4是 UTF8 的超集并完全兼容UTF8。
3
// (3)  UTF8(也称UTF8MB3)，1字符使用3字节存储。 
4
// (4)  UTF8MB4，1字符使用4字节存储。(专门用来兼容4字节的UNICODE编码-平面设计-Emoji问题)。

• MySQL中几个了解的校对规则: 了解(不用记具体的排序规则, 只需要知道排序规则是干什么,是对谁起效果的)

xxxxxxxxxx
8
1
校对规则/排序规则也可以称为排序规则，是指在字符与字符之间的比较规则。一个字符集有多种校对规则，每个字符集都有一个默认的校对规则。
2

3
eg: UTF8MB4: 下面只是用来举例说明, 不用记
4
// (1) UTF8MB4_GENERAL_CI:  (默认规则),字符之间逐个比较,不区分大小写，在排序和比较效率更高。
5
        //  一些特殊字符不能做到精准排序。
6
        //  其实就是没有实现Unicode排序规则, 影响上实际并无所谓。
7
// (2) UTF8MB4_UNICODE_CI:  基于标准的Unicode来排序和比较,精确排序, 不区分大小写。
8
// (3) UTF8MB4_BIN: 字符直接用二进制数据编译存储，区分大小写，而且可以存二进制的内容。

删除数据库

在工作中不要删除数据库, 哪怕这个数据库已经没有任何用处了 ( 任何数据都是有价值的, 哪怕是错的数据)

xxxxxxxxxx
3
1
DROP DATABASE [IF EXISTS]  <数据库名>;  # 删除数据库
2

3
eg:   drop database test; # 删除test数据库

修改数据库

在工作中不要修改数据库, 因为没有任何意义

xxxxxxxxxx
5
1
ALTER DATABASE [数据库名] 
2
{  [ DEFAULT ] CHARACTER SET <字符集名> | [ DEFAULT ] COLLATE <校对规则名>  }
3

4

5
eg:  alter database test character set utf8 collate utf8_bin;  # 把test数据库的编码改成utf8, 校对规则改为utf8_bin

数据库中只提供了对数据库使用的字符集和校对规则修改操作。

xxxxxxxxxx
8
1
假设一种场景: 举个例子
2
// 第一步: 先创建了一个数据库: create database th54 character set utf8;
3
//        编码格式是utf8
4
// 第二步: 在这个数据库th54中创建一个表: studnets->utf8. (如果在创建表的时候没有指明编码格式, 默认跟随数据库的编码格式)
5
// 第三步: 修改了th54的编码格式: alter database th54 character set utf8mb4;
6
//         th54的编码格式已经修改为utf8mb4
7
//         studnets编码格式会不会跟着变? 默认不会, 还是utf8
8
// 第四步: 创建一个全新的User表 : User -> utf8mb4

选择数据库

xxxxxxxxxx
4
1
USE <数据库名>;  # 选择数据库
2

3
eg: 
4
use test; # 选择test数据库

一个MySQL系统中, 管理多个数据库。 我们只有进入对应的数据库中, 才能进一步操作数据库中的表和数据。

2.2.3 表操作

常见的数据类型

数据类型（DATA_TYPE）是指数据库所允许的数据的类型。

MySQL是一个数据存储容器, 数据类型定义了可以存储什么类型数据的规则。

数据库是由多张表构成。其中每个表中对应的数据列, 都应该有适当的数据类型，用于限制或允许该列中存储的数据。

(1) 整数

注意1:

INT和INTEGER在MySQL中并无区别, 仅是缩略写法.link。

注意2: 关于整数设置'长度/宽度'问题。

第一原则:无论给整数设置什么长度都不违背上述'有/无符号'表示的存储范围(上述范围表示是一切基本标准)。

第二原则: 设置长度之后, 如果存储的整数长度小于指定长度, 会默认在数字位前自动补空格, 以满足指定长度(但是补的空格这是不可见的), 也可以选择经过设置使用0填充; 设置长度之后, 如果存储的整数长度大于指定长度, 如果在表示范围内, 不做切割/不做处理(不补0, 也不补空格), 直接存储。

xxxxxxxxxx
6
1
CREATE TABLE `tb_test`  (
2
`id` int NOT NULL,
3
`age` int ZEROFILL NULL, -- zerofill: 填充0
4
`height` int(3) unsigned NOT NULL,
5
PRIMARY KEY (`id`)
6
);

(2) 浮点数

• M: 精度, 表示总数据位数。 取值范围为(1～255)。

• D: 标度, 表示小数位的位数。 取值范围为(1～30，且不能大于 M-2)。

• 需要注意的是: int类型设置宽度只是要不要填充空格和0的问题, 在float double中这个设置参数超过总位数会报错, 超过小数位限制舍弃

• FLOAT 和 DOUBLE 在不指定精度时，默认会按照实际的精度(由计算机硬件和操作系统决定)。

xxxxxxxxxx
7
1
-- FLOAT 类型的取值范围如下：
2
有符号的取值范围：-3.402823466E+38～-1.175494351E-38。
3
无符号的取值范围：0 和 -1.175494351E-38～-3.402823466E+38。
4

5
-- DOUBLE 类型的取值范围如下：
6
有符号的取值范围：-1.7976931348623157E+308～-2.2250738585072014E-308。
7
无符号的取值范围：0 和 -2.2250738585072014E-308～-1.7976931348623157E+308。

(3) 日期

• year/YEAR范围: 1901~ 2155包含255个年份, 对应一个字节表示范围。

• time/TIME范围: 11111111111111111111111(23位剩余一位符号位)--> 8388607(十进制)

8388607: 838(小时位) xx(描述分钟) xx(描述秒)。 (计算方式不用记)

• timestamp/TIMESTAMP范围: (时间戳) (一般用于一些计时, 倒计时, 数据传输 ......) (秒数1)

1970年1月1日作为UNIX TIME的纪元时间(开始时间)。

xxxxxxxxxx
11
1
--
2
create table user(
3
    id int,
4
   name varchar(5),
5
   height float(5, 2),
6
   birthday datatime,
7
   tag_time timestamp
8
)
9
--
10
insert into user4 values(1, 'zs', 188.01, '2000-11-12 55:30:12', '2000-01-01 00:00:00');
11
insert into user4 values(1, 'zs', 188.01, '2000-11-12 10:30:12', now());

(4) 字符串

L: 存储字符的实际长度。

M: 列的指定长度。

• CHAR (M不设置默认为1) 范围可以设置最大255 。

• VARCHAR(M不设置会报错) 最大长度为 65,535字节

• TEXT 最大长度为 65535字符

• LONGTEXT 最大长度长度为 4294967295 字符。

1, 数据库中的char可以正常存储字符串: eg: "zs", "我爱学习" ....; (但是一般我们用到的char的时候, 更多是为了存储性别)

2, 创建表的时候, 如果使用char类型, 不加宽度/长度, 长度默认为1; 如果使用varchar不加长度/宽度, 创建的时候会报错, text/longtext同样建议指明长度(有些版本中不指明会报错)

3, 在数据库中, 只要是字符串, 都有长度限制, 在字符串列存储数据的时候, 不能超出既定的长度限制 (字符串的长度限制是一个严格限定).

4, 如果使用char类型(定长字符串), 它是一个固定长度字符串: 如果我们常见char的时候指明的宽度为m, 每一个这个char列的数据, 最终开辟的内存空间为: 单个字符占用空间 * M; 和实际存储了几个字符无关

5, 我们使用varchar这种变长字符串存储数据的的时候, 字符串实际占用的内存空间和我们给varchar设置的宽度/长度没有任何关系, 只和这个varchar列实际存储的字符个数有关.

查看表

xxxxxxxxxx
4
1
SHOW TABLES;             # 查看该数据库中所有表
2
SHOW CREATE TABLE <表名>; # 查看表的创建语句
3
DESCRIBE <表名>;          # 查看表结构
4
DESC <表名>;              # 查看表结构

创建表

xxxxxxxxxx
29
1
CREATE TABLE <表名>  (
2
<列名1> <类型1> ,
3
[…] ,
4
<列名n> <类型n>
5
)   [表选项] [分区选项];
6

7
eg1:
8
create table employee(
9
    id int ,
10
name varchar(20),
11
gender char,
12
birthday date,
13
job varchar(20),
14
salary double(10,2)
15
)character set utf8 collate utf8_bin;
16
-- 不推荐专门给一个表设置编码格式(没什么用, 只会让一个数据库编码格式不统一)(建议不设编码格式: 表的编码格式遵从数据库设置的编码格式)
17

18
eg2: 仅仅是个完善的写法示例, 一些写法完全没有必要(比如重复设置字符编码格式), 比如注释
19
CREATE TABLE  employee1  (
20
id int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
21
name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '姓名',
22
gender varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '男' COMMENT '性别',
23
graduate_year year NULL DEFAULT NULL COMMENT '毕业时间',
24
birthday date NOT NULL COMMENT '生日',
25
job varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '职位名称',
26
salary double(10, 2) NULL DEFAULT 0.00 COMMENT '薪资',
27
create_time datetime NULL DEFAULT NULL COMMENT '信息创建时间',
28
PRIMARY KEY (id) USING BTREE
29
) ;

主键和自增问题

主键（PRIMARY KEY）又被称为''主键约束''，是 MySQL中使用非常频繁的约束。它有诸多作用(唯一标识, 查找效率...)。

注意1:

xxxxxxxxxx
6
1
-- 主键: 如果一个表中给某一列设置了主键, 那么这一列强制不能重复, 如果存储重复的主键会自动报错
2
-- 主键: 不允许是null, 存储null主键, 报错
3
-- 每个表只能定义一个主键。
4
-- 主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主键值的两行数据。
5

6
ps: 如果用户没有定义主键，也没有定义索引，那么InnoDB引擎会在创建表的时候, 自动生成一个不可见的ROW_ID的列名的聚簇索引，该列是一个6字节的自增数值，随着插入而自增, 来起到主键的作用。

主键分类:

xxxxxxxxxx
28
1
主键分为单字段主键和多字段联合主键
2
-- 单字段主键: 既将表中的一个字段设置主键; 通过 PRIMARY KEY 关键字来指定主键.
3
CREATE TABLE table_primary1(
4
id INT(11) PRIMARY KEY , -- 主键
5
name VARCHAR(25),
6
job VARCHAR(25),,
7
salary FLOAT
8
);
9
CREATE TABLE table_primary2(
10
id INT(11),
11
name VARCHAR(25),
12
job VARCHAR(25),
13
salary FLOAT,
14
PRIMARY KEY(id)
15
);
16

17
-- 如果一个表表在创建的时候没有主键, 增加主键的sql
18
-- alter table 表名 add primary key (主键列);
19
alter table user add primary key (id);
20

21
-- 联合主键: 复合主键(不建议)。这意味着id和name的组合在table_primary3表中表示主键。
22
CREATE TABLE table_primary3(
23
id INT(11),
24
name VARCHAR(25),
25
job VARCHAR(25),
26
salary FLOAT,
27
PRIMARY KEY(id, name)
28
);

注意2: 唯一键: 仅了解

xxxxxxxxxx
13
1
// 唯一键(Unique Key)是数据库中的一个约束，用于确保表中的所有记录在指定的一列或列组合上是唯一的。即:组合唯一。
2
// 与主键不同的是，唯一键允许有空值(NULL)。
3
// 表中可以有多个唯一键约束。
4
// 在某些情况下，唯一键也可以作为主键的一部分。
5

6
CREATE TABLE table_primary3(
7
   id INT(11),
8
   name VARCHAR(25),
9
   job VARCHAR(25),
10
   salary FLOAT,
11
   primary KEY(id),
12
   unique key (job, salary)
13
);

注意3: 自增问题 AUTO_INCREMENT

xxxxxxxxxx
17
1
CREATE TABLE table_primary1(
2
id INT(11) PRIMARY KEY AUTO_INCREMENT , -- 主键
3
name VARCHAR(25),
4
job VARCHAR(25),,
5
salary FLOAT
6
);
7
-- AUTO_INCREMENT 的初始值是 1，数据增加一条，该字段值自动加 1。
8
-- 建议: 一个表中应该只有一个字段使用 AUTO_INCREMENT 约束，且该字段一般位为作为索引/主键的id字段。
9
-- AUTO_INCREMENT 字段应该要设置 NOT NULL 属性。
10
-- AUTO_INCREMENT 约束的字段只能是整数类型。
11
-- AUTO_INCREMENT 上限为所约束的类型的数值上限。
12

13
-- 如果一个表表在创建的时候某些列没有自增, 设置自增
14
-- alter table 表名 modify 列名 int auto_increment;
15
alter table user modify id int auto_increment;
16

17

修改表

表的修改操作：不允许在工作直接修改表

xxxxxxxxxx
32
1
ALTER TABLE <表名> ADD COLUMN <列名> <类型>; # 添加列
2
ALTER TABLE <表名> ADD <新字段名> <数据类型> FIRST; # 头位置添加列
3
ALTER TABLE <表名> ADD <新字段名> <数据类型> AFTER <已经存在的字段名>; # 指定位置添加列
4
ALTER TABLE <表名> MODIFY COLUMN <列名> <类型>;  # 修改某列类型
5
ALTER TABLE <表名> CHANGE COLUMN <旧列名> <新列名> <新列类型>; # 修改列及类型
6
ALTER TABLE <表名> ALTER COLUMN <列名> SET DEFAULT <默认值>; # 修改某列默认值
7
ALTER TABLE <表名> ALTER COLUMN <列名> DROP DEFAULT; # 删除某列默认值
8
ALTER TABLE <表名> DROP COLUMN <列名>; # 删除某列
9
ALTER TABLE <表名> RENAME TO <新表名>; # 修改表名
10
ALTER TABLE <表名> RENAME AS <新表名>; # 修改表名
11
ALTER TABLE <表名> RENAME <新表名>; # 修改表名
12
RENAME TABLE <表名> TO <新表名>; # 修改表名
13
ALTER TABLE <表名> CHARACTER SET <字符集名>; # 修改表字符集
14
ALTER TABLE <表名> COLLATE <校对规则名>; # 修改表排序规则
15
ALTER TABLE <表名> [DEFAULT] CHARACTER SET <字符集名> [DEFAULT] COLLATE <校对规则名>;
16

17
eg:
18
alter table employee  add column height  float(5,2);
19
alter table employee  add column height  float(5,2) first;
20
alter table employee  add column height  float(5,2) after name;
21
alter table employee modify  column age float(5, 0);
22
alter table employee  change column age age1 float(5, 0);
23
alter table employee alter column age set default 20;
24
alter table employee alter column age drop default;
25
alter table employee drop column height;
26
alter table employee rename to aaa;
27
alter table aaa rename as employee;
28
alter table employee rename  aaa;
29
rename table aaa to employee;
30
alter table employee character set utf8mb4;
31
alter table employee collate utf8mb4_unicode_ci;
32
alter table employee character set gbk collate gbk_bin;

删除表

不允许在工作直接删除表

更不建议: 任何数据都是有意义的

xxxxxxxxxx
4
1
DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]
2

3
eg:
4
drop table if exists table_a, table_b, table_c;

2.2.4 数据操作

添加数据

xxxxxxxxxx
9
1
INSERT INTO <表名> [ (<列名1>, … <列名n> )]  VALUES (值1, … 值n), … (值1, … 值n);
2
INSERT INTO <表名> SET <列名1>=<值1>, … <列名n>=<值n>;
3

4
eg:
5
insert into employee1 (id, name, gender, graduate_year, birthday, job, salary, create_time) values(1, 'zs', '男', 2022, '1999-01-01', '程序员',  100.2, '2022-09-09 16:51:49');
6

7
insert into employee1 (id, name, gender, graduate_year, birthday, job, salary, create_time) values(1, 'zs', '男', 2022, '1999-01-01', '程序员',  100.2, '2022-09-09 16:51:49'),(2, 'ls', '男', 2020, '1997-01-01', '程序员',  10000.2, '2022-09-09 16:51:50');
8

9
insert into employee1  set id=4, name='ls', gender='男', graduate_year=2022, birthday='1999-01-01', job='程序员', salary=220.05,  create_time='2022-09-09 16:55:49';

• 如果values中包含数据和表列数据一一对应(无省略), 那么在插入语句中可以省略表名之后表列的一一列举。eg: insert into 表名 values (值1, … 值n);

• values中的内容应该要与对应插入字段对应。

• 数据中字符串和日期应该包含在引号中。

查询数据

xxxxxxxxxx
8
1
SELECT  *  FROM <表名字>  [ WHERE  <条件> ];
2
SELECT  <列名1>, …<列名n>  FROM <表名字>  [ WHERE  <条件> ];
3

4
eg:
5
select * from employee1;
6
select * from employee1 where id<20;
7
select name from employee1 where id>1;
8
select name, job, salary from employee1 where salary> 200;

• 查询的结果是一个新的临时表。

• 在MySQL中 select * from 表名 where 1; 表示查询所有数据。

修改数据

xxxxxxxxxx
4
1
UPDATE <表名> SET 列1=值1 [, 列2=值2 … ] [WHERE <条件> ]
2

3
eg:
4
update employee1 set job='老程序员' where salary >10000;

• 注意如果没有where子句指明条件, 那么修改就是对所有行的修改。

• 修改一行数据的多个列值时，SET 子句的每个值用逗号分开即可。

删除数据

在工作中删除数据, 一般是逻辑删除

xxxxxxxxxx
5
1
DELETE FROM <表名> [WHERE <条件>]
2

3
ag:
4
delete from employee;
5
delete from employee where id=4;

• 如果没有where以及条件, 默认删除是表中所有数据。

• delete不能单独只删除某一列数据, delete删除数据的最小单元为行。

• delete语句仅删除数据记录, 删除的不是表, 如果要删除表需要使用drop table语句。

2.3 特殊关键字

数据基础

xxxxxxxxxx
8
1
CREATE TABLE `students`  (
2
`id` int(11)  PRIMARY KEY  AUTO_INCREMENT,
3
`name` varchar(255)   ,
4
`class` varchar(255)  ,
5
`chinese` float  ,
6
`english` float  ,
7
`math` float  
8
) ;

xxxxxxxxxx
13
1
INSERT INTO students (id, name, class, chinese, english, math) VALUES (1, '武松', '一班', 70, 90, 60);
2
INSERT INTO students VALUES (2, '林冲', '一班', 70, 90, 90);
3
INSERT INTO students VALUES (3, '松江', '一班', 90, 90, 20);
4
INSERT INTO students VALUES (4, '贾琏', '二班', 60, 60, 60);
5
INSERT INTO students VALUES (5, '贾宝玉', '二班', 95, 80, 5); 
6
INSERT INTO students VALUES (6, '贾环', '二班', 25, 25, 5); 
7
INSERT INTO students VALUES (7, '曹操', '三班', 90, 90, 90); 
8
INSERT INTO students VALUES (8, '曹丕', '三班', 90, 80, 80); 
9
INSERT INTO students VALUES (9, '曹植', '三班', 98, 90, 80); 
10
INSERT INTO students VALUES (10, '刘备', '三班', 95, 90, 80); 
11
INSERT INTO students VALUES (11, '诸葛亮', '三班', 98, 95, 95); 
12
INSERT INTO students VALUES (12, '孙权', '三班', 80, 90, 80); 
13
INSERT INTO students (id) VALUES (13);

2.3.1 Where

xxxxxxxxxx
4
1
SELECT <查询内容|列等> FROM  <表名字> WHERE <查询条件|表达式>
2

3
eg:
4
select id, name from students where id > 10;

使用 WHERE 关键字并指定查询/删除/修改的条件, 让操作在满足条件的情况下执行 数据操作.

在构建Where的条件的过程中, 我们可能需要了解到一些重要的SQL运算符

1, 算术运算符: 可以用在条件中, 也可以用在结果集中

运算符	作用
+	加
-	减
*	乘
/	除
%	取余

xxxxxxxxxx
7
1
eg:
2
select * from students where (chinese + english + math) < 180; 
3
select * from students where (chinese - math) > 30; 
4
select *, (chinese*0.5 + english*0.1 + math *0.4) from students; 
5
select *, (chinese*0.5 + english*0.1 + math *0.4)  from students where (chinese*0.5 + english*0.1 + math *0.4) <= 60 ;  
6
select *, (chinese + english + math) /180 from students ;  
7
select *, (chinese + english + math) /180 from students  where (chinese + english + math) /180  < 1.2; 

2, 比较和逻辑运算符

运算符	作用	运算符	作用
=	等于	<=>	等于(可比较null)
!=	不等于	<>	不等于
<	小于	>	大于
<=	小于等于	>=	大于等于
is null	是否为null	is not null	是否不为null
between and	在闭区间内	in	是否在列表内
not in	不在列表内	like	通配符匹配(%:通配, _占位)
and	与	&&	与
or	或	||	或

xxxxxxxxxx
20
1
select * from students where chinese = 60;
2
select * from students where chinese <=> 60;
3
select * from students where chinese != 60;
4
select * from students where chinese <=> 60;
5
select * from students where  chinese < 60;
6
select * from students where  chinese > 90;
7
select * from students where  chinese <= 60;
8
select * from students where  chinese >= 90;
9
select * from students where  chinese is null;
10
select * from students where  chinese is not null;
11
select * from students where  chinese between 60 and 90;
12
select * from students where  chinese in (60 , 90);
13
select * from students where  chinese not in (60 , 90);
14
select * from students where  name = '曹操';
15
select * from students where  name like '曹操';
16
select * from students where  name like '曹%';
17
select * from students where  name like '曹%'  and  chinese = 90;
18
select * from students where  name like '曹%'  &&  chinese = 90;
19
select * from students where  name like '曹%'  or  chinese = 90;
20
select * from students where  name like '曹%'  ||  chinese = 90;

2.3.2 Distinct

使用DISTINCT对数据表中一个或多个字段重复的数据进行过滤，重复的数据只返回其一条数据给用户.

xxxxxxxxxx
5
1
SELECT DISTINCT <字段名> FROM <表名>;
2

3
eg:
4
select distinct chinese from students;
5
select distinct chinese, english from students;

注意:

xxxxxxxxxx
3
1
-- DISTINCT 只能在SELECT语句中使用。 (对结果进行去重)
2
-- 当对一个或多个字段去重时，DISTINCT 要写在所有字段的最前面。
3
-- 如果 DISTINCT 对多个字段去重时，只有多个字段组合起来完全是一样的情况下才会被去重。

2.3.3 Limit

使用LIMIT对数据表查询结果集大小进行限定.

xxxxxxxxxx
8
1
SELECT <查询内容|列等> FROM  <表名字> LIMIT 记录数目
2
SELECT <查询内容|列等> FROM  <表名字> LIMIT 初始位置，记录数目;
3
SELECT <查询内容|列等> FROM  <表名字> LIMIT 记录数目 OFFSET 初始位置;
4

5
eg:
6
select * from students limit 3;
7
select * from students limit 4, 3;
8
select * from students limit 3 offset 4;

LIMIT 记录数目: 从第一条开始, 限定记录数目

LIMIT 初始位置，记录数目: 从起始位置开始, 限定记录数目

LIMIT 记录数目 OFFSET 初始位置: 从起始位置开始, 限定记录数目

注意: 数据(默认下标从0开始)

2.3.4 As

AS 关键字用来为表和字段指定别名.

xxxxxxxxxx
11
1
<内容> AS <别名>
2

3
eg:
4
select  name from students;
5
select  * from students as s where s.chinese < 60;
6
select  s.name from students as s ;
7
select  s.name from students as s where s.chinese < 60;
8
select  name as username from students;
9
select  * from students;
10
select  *, (chinese + english + math) from students;
11
select  *, (chinese + english + math) as sum from students;

2.3.5 Order By

ORDER BY对查询数据结果集进行排序.

xxxxxxxxxx
8
1
SELECT <查询内容|列等> FROM  <表名字> ORDER BY <字段名> [ASC|DESC];
2

3
eg: 
4
select * from students order by chinese;
5
select * from students order by chinese asc;
6
select * from students order by chinese desc;
7
select * from students order by chinese, english;
8
select * from students order by chinese desc, english desc, math desc;

不加排序模式: 升序排序.

ASC: 升序排序.

DESC: 降序排序.

注意: 如上查询, 当我们进行多字段排序的时候, 会先满足第一个列的排序要求, 如果第一列一致的话, 再按照第二列进行排序, 以此类推.

2.3.6 Group By

使用 GROUP BY 关键字,对数据进行分组

xxxxxxxxxx
9
1
SELECT <查询内容|列等> FROM  <表名字> GROUP BY  <字段名...>
2

3
eg: 
4
select class, group_concat(name), group_concat(chinese) from students group by class;
5
select class, group_concat(name) from students where chinese > 90 group by class;
6

7
select class, group_concat(name), avg(chinese) from students group by class;
8
select class, group_concat(name) from students group by class having count(*) > 3;
9
select class, group_concat(name), avg(chinese) from students group by class having avg(chinese) > 60;   

GROUP_CONCAT()函数会把每个分组的字段值都显示出来.

HAVING 可以让我们对分组后的各组数据过滤。(一般和分组+聚合函数配合使用)

2.3.7 聚合函数

聚合函数一般用来计算列相关的指定值. 通常和分组一起使用

函数	作用	函数	作用
COUNT	计数	SUM	和
AVG	平均值	MAX	最大值
MIN	最小值

• COUNT: 计数

  xxxxxxxxxx
  7
  1
  SELECT <查询内容|列等> , COUNT <列|*> FROM  <表名字> GROUP BY HAVING COUNT <表达式|条件>
  2
  
  3
  eg:
  4
  select count(*) from students 
  5
  select count(name) from students 
  6
  select class, group_concat(name), count(*) from students group by class;
  7
  select class, group_concat(name), count(*) from students group by class having count(*) > 2;
  

  COUNT(*): 表示表中总行数

  COUNT(列): 计算除了列值为NULL以外的总行数

• SUM: 求和

  xxxxxxxxxx
  10
  1
  SELECT <查询内容|列等> , SUM<列> FROM  <表名字> GROUP BY HAVING SUM<表达式|条件>
  2
  
  3
  eg:
  4
  select sum(chinese) from students;
  5
  select sum(chinese), sum(english), sum(math) from students;
  6
  select class, group_concat(name), sum(chinese) from students group by class;
  7
  select class, group_concat(name), sum(chinese) from students group by class having sum(chinese)>200;
  8
  select class, group_concat(name), sum(chinese), sum(math) from students group by class;
  9
  select class, group_concat(name), sum(chinese), sum(math) from students group by class having sum(chinese)>200 and sum(math) > 200;
  10
  
  

• AVG: 平均值

  xxxxxxxxxx
  9
  1
  SELECT <查询内容|列等> , AVG<列> FROM  <表名字> GROUP BY HAVING AVG<表达式|条件>
  2
  
  3
  eg:
  4
  select avg(chinese) from students;
  5
  select avg(chinese), avg(english), avg(math) from students;
  6
  select class, group_concat(name), avg(chinese) from students group by class;
  7
  select class, group_concat(name), avg(chinese) from students group by class having avg(chinese)>=60;
  8
  select class, group_concat(name), avg(chinese), avg(math) from students group by class;
  9
  select class, group_concat(name), avg(chinese), avg(math) from students group by class having avg(chinese)>=60 and avg(math) >=60;
  

• MAX: 最大值

  xxxxxxxxxx
  9
  1
  SELECT <查询内容|列等> , MAX<列> FROM  <表名字> GROUP BY HAVING MAX<表达式|条件>
  2
  
  3
  eg: 
  4
  select max(chinese) from students;
  5
  select max(chinese), max(english), max(math) from students;
  6
  select class, group_concat(name), max(chinese) from students group by class;
  7
  select class, group_concat(name), max(chinese) from students group by class having max(chinese)>90;
  8
  select class, group_concat(name), max(chinese), max(math) from students group by class;
  9
  select class, group_concat(name), max(chinese), max(math) from students group by class having max(chinese)>=90 and max(math) >=70;
  

• MIN: 最小值

  xxxxxxxxxx
  9
  1
  SELECT <查询内容|列等> , MIN<列> FROM  <表名字> GROUP BY HAVING MIN<表达式|条件>
  2
  
  3
  eg:
  4
  select min(chinese) from students;
  5
  select min(chinese), min(english), max(math) from students;
  6
  select class, group_concat(name), min(chinese) from students group by class;
  7
  select class, group_concat(name), min(chinese) from students group by class having min(chinese)>60;
  8
  select class, group_concat(name), min(chinese), min(math) from students group by class;
  9
  select class, group_concat(name), min(chinese), min(math) from students group by class having min(chinese)>=60 and min(math) >=60;
  

2.4 SQL执行顺序

xxxxxxxxxx
11
1
(5) SELECT column_name, ... 
2
(1) FROM table_name, ... 
3
(2) [WHERE ...] 
4
(3) [GROUP BY ...] 
5
(4) [HAVING ...] 
6
(6) [ORDER BY ...];
7
(7) [Limit ...]
8

9
-- 计算每个班级中语文成绩大于60的同学的平均数学成绩, 获得平均数学成绩大于等于40的班级中平均语文成绩最高的那个班级的平均语文和数学成绩
10
eg:
11
    select class, group_concat(name), avg(chinese) as chineseA, avg(math) from students where chinese >= 60 group by class having  avg(math)>=40 order by chineseA desc  limit 0, 1;

• 小括号中的数字代表执行顺序

• having和select的执行顺序收到优化器的影响，可能会改变执行顺序

2.5 数据完整性

为了在实际工程环境中更好的使用和维护数据库数据, 在我们设计和使用数据库的库/表/数据的时候, 一般要遵循数据完整性规则; 数据完整保证在数据库设计和数据存储过程中, 对数据的存储/处理是做到尽可能正确; 做到降低用户在实际使用的时候出错的可能性, 尽可能提高数据库的使用效率.

ps: 所谓数据完整性: 是在数据库中表和数据的设计的时候定制的一些约定俗成的参考规范. 是站在组织数据的角度, 目的是希望在数据库中数据存储更规整,以及希望操作数据的时候效率更高

而数据完整性又包含: 实体完整性; 域完整性; 参照完整性

2.5.1 实体完整性

保证表中的每一行数据都是表中唯一的实体.(即:一个表中每一条数据都应该是唯一的)

实体完整性是为了保证表中数据唯一, 实体完全可由主键实现(通过一个主键的设置, 保证一个表中每一条信息都是唯一的).

xxxxxxxxxx
8
1
CREATE TABLE `students`  (
2
`id` int(11)  PRIMARY KEY  AUTO_INCREMENT,
3
`name` varchar(255)   ,
4
`class` varchar(255)  ,
5
`chinese` float  ,
6
`english` float  ,
7
`math` float  
8
) ;

2.5.2 域完整性

域完整性表示保证表中数据的字段的取值在有效范围之内或者符合特定的数据类型约束

1, 含义: 某一列的数据存储的类型要设计合适 (float, varchar,NULL, NOT NULL...)

2, 在某一列存储数据的时候, 存储的数据的内容, 应该要符合对这个列的类型以及大小限定

xxxxxxxxxx
8
1
CREATE TABLE `students`  (
2
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
3
`name` varchar(10)  NOT NULL DEFAULT "张飞",
4
`class` varchar(5)  NULL DEFAULT NULL,
5
`chinese` float NOT NULL,
6
`english` float NOT NULL,
7
`math` float NOT NULL
8
);

2.5.3 参照完整性

参照完整性用于确保相关联的表间的数据应该要保持一致.

避免因为一个表的数据/记录修改, 造成另一个表的内容变为无效的值. 一般来讲, 参照完整性是通过外键和主键来维护的.

外键是参照完整性的一种强有力的维护手段.

参照完整性 != 外键

主键的设置和取消: (了解)

xxxxxxxxxx
38
1
--方式一: 在创建表的时候设置外键
2
CREATE TABLE `class`  (
3
`id` int NOT NULL PRIMARY KEY,
4
`name` varchar(255) NULL
5
);
6
CREATE TABLE `student`  (
7
`id` int NOT NULL PRIMARY KEY,
8
`name` varchar(255) NULL,
9
`class_id` int ,
10
CONSTRAINT `foreign_key_name` FOREIGN KEY (class_id) REFERENCES `class`(`id`)
11
);
12
--方式二: 表创建完毕, 对表设置外键
13
CREATE TABLE `class`  (
14
`id` int NOT NULL PRIMARY KEY,
15
`name` varchar(255) NULL
16
);
17
CREATE TABLE `student`  (
18
`id` int NOT NULL PRIMARY KEY,
19
`name` varchar(255) NULL,
20
`class_id` int 
21
);
22

23
ALTER TABLE  `student` ADD CONSTRAINT `foreign_key_name` FOREIGN KEY (`class_id`) REFERENCES  `class` (`id`);
24

25
--方式三: 表创建完毕, 添加新的字段列, 并且设置为外键
26
CREATE TABLE `class`  (
27
`id` int NOT NULL PRIMARY KEY,
28
`name` varchar(255) NULL
29
);
30
CREATE TABLE `student`  (
31
`id` int NOT NULL PRIMARY KEY,
32
`name` varchar(255) NULL
33
);
34
alter table `student` add column `class_id` int null after `name`,
35
add constraint `foreign_key_name` foreign key (`class_id`) references `class` (`id`);
36

37
-- 删除外键
38
ALTER TABLE `student` DROP FOREIGN KEY `foreign_key_name`;

外键优缺点: 重要

xxxxxxxxxx
7
1
优点：
2
// 能够限制数据的增加、删除或者是修改操作，来保证数据的一致性。
3
缺点：
4
// 在插入/修改子表（student）的数据的时候，需要去父表（class）中找对应的数据
5
// 在删除/修改父表（class）的数据的时候，需要去检查子表（student）中是否有对应的数据
6
// 有了外键之后，影响了增加、删除、修改的性能
7
// 使用起来也不方便

在公司中是否使用外键呢: 重要

xxxxxxxxxx
5
1
看具体公司内部的情况是否使用外键
2
// 假如公司表中的数据量不大（外键对效率的影响比较小，甚至可以忽略），可以考虑使用外键
3
// 假如公司数据库表中的数据很多，（外键对于效率的影响就会很大），不应该使用外键
4
// 人为使用习惯(内心OS)
5
// ...