MySQL快速入门
SQL语句
SQL语句概述
1.SQL 是用于访问和处理数据库的标准的计算机语言。
2.SQL指结构化查询语言，全称是 Structured Query Language。
3.SQL 可以访问和处理数据库。
4.SQL 是一种 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言。
SQL语句特点
\1. 具有综合统一性，不同数据库的支持的sql稍有不同
为许多任务提供了统一的命令，这样方便用户学习和使用，基本的 SQL 命令只需很少时间就能学会，甚至最高级的命令也可以在几天内掌握。数据库的操作任务通常包括以下几方面：增、删、改、查
常见数据库（mysql、sqlserver、oracle、db2等）
\2. 非过程化语言
3.语言简捷，用户容易接受 select , drop, alter, create, insert, update ,delete
select * from mayikt_users ----查询 mayikt_users 的数据
4.集合性
SQL 可以在高层的数据结构上进行工作，工作时不是单条地处理记录，而对数据进行成组的处理语句都接受集合作为输入，并且返回集合作为输出
SQL语法特点
1.SQL 对关键字大小不铭感;
2.SQL语句可以单行或者多行数据，以分行结束;
3.SQL语句注释：
-- 单行注释 （后面一定要加一个空格）
#单行注释 #后面可以不用加空格
/*
多行注释
多行注释
*
/
数据库系统简介
数据库是一个以某种有组织的方式存储在硬盘上数据集合；
| id | name（名称） | age(年龄) |
|---|---|---|
| 1 | mayikt | 23 |
| 2 | meite | 28 |
| 3 | zhangsan | 18 |
| 4 | lisi | 19 |
数据库应用场景 以后开发JavaWeb项目 背后的数据都是存放在数据库（互联网）中。
数据库系统的分类
A.关系型数据库(RDBMS)
1.Oracle数据库 （甲骨文公司） 收费的
2.MySQL数据库（最流行的数据库） 免费版本 源代码开源
3.SQLServer 数据库 （微软开发的数据库）c#（微软公司） windows
4.Sqlite（嵌入式关系数据库） 学习 安卓手机端程序开发
5.db2
B.非关系型数据库(NoSQL)
1.Redis（缓存数据库）
2.Mongodb（文档数据库）
3.Elasticsearch(搜索服务)
4.Hbase（分布式、列示数据库）
SQL与数据库的关系
1.SQL是一种用于操作数据库的语言，SQL适用于所有关系型数据库
2.MySQL、Oracle、SQLServer、DB2 是一个数据库软件，这些数据库软件支持标准SQL，也就是通过SQL可以使用这些软件，不过每一个数据库系统会在标准SQL的基础上扩展自己的SQL语法，大部分的3.NoSQL数据库有自己的操作语言，对SQL支持的并不好。
SQL 属于 数据库编程语言 数据库 MySQL、Oracle、SQLServer、DB2 数据库软件
MySQL简介
简介：
1.MySQL数据库管理系统由瑞典的DataKonsultAB公司研发，该公司被Sun公司收购，现在Sun公司又被Oracle公司收购，因此MySQL目前属于 Oracle 旗下产品。
2.MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版（免费版本）和商业版（收费），由于其体积小、速度快、总体拥有成本低，一般中小型网站的开发都选择 MySQL 作为网站数据库。
特点：
1.MySQL数据库是用C和C++语言编写的，以保证源码的可移植性
2.支持多个操作系统例如：Windows、Linux、Mac OS等等
3.支持多线程，可以充分的利用CPU资源
4.为多种编程语言提供API，包括C语言，Java，PHP、Python、go语言等
5.MySQL优化了SQL算法，有效的提高了查询速度
6.MySQL开放源代码且无版权制约，自主性强、使用成本低。
7.MySQL历史悠久、社区及用户非常活跃，遇到问题，可以很快获取到帮助。
MySQL的安装与卸载
安装
解压安装
MySQL的安装两种方式（MySQL8.0）
1.解压版本 或者直接安装方式
https://dev.mysql.com/downloads/mysql/
1.解压mysql-8.0.27-winx64
注意：不要放在有中文名字和空格的的目录下
2.在mysql-8.0.27-winx64文件夹下面新建一个my.ini文件和一个data文件夹
使用mysql 数据 ----data文件夹中
3.my.ini 添加以下内容：
[mysqld] # 设置3306端口 port=3306 # 设置mysql的安装目录 basedir=D:\\mysql-8.0.27-winx64 # 设置mysql数据库的数据的存放目录 datadir=D:\\mysql-8.0.27-winx64\\data # 允许最大连接数 max_connections=200 # 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统 max_connect_errors=10 # 服务端使用的字符集默认为UTF8 character-set-server=utf8 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB [mysql] # 设置mysql客户端默认字符集 default-character-set=utf8 [client] # 设置mysql客户端连接服务端时默认使用的端口 port=3306 default-character-set=utf8
# 设置mysql的安装目录
basedir=D:\mysql-8.0.27-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\mysql-8.0.27-winx64\data
改成自己 路径
4.配置系统环境
我的电脑→属性→高级→环境变量→系统变量→新建
变量名：MYSQL_HOME
变量值：D:\path\mysql\mysql-8.0.27-winx64 （也就是刚解压的地方）
Path 中新建一段：%MYSQL_HOME%\bin
5.以管理员的身份打开cmd窗口跳转路径到D:\path\mysql\mysql-8.0.27-winx64，依次输入以下命令： 注意：是以管理员的身份打开cmd
1.初始化：mysqld --initialize --user=mysql --console
初始化 mysql 成功之后 账户和密码
账户：root
密码：随机密码 ,8s5%IoDpgu,
2.添加服务 mysqld -install
3.启动服务 net start mysql
4.登进数据库 （密码就输入刚才的随机密码） mysql -u root -p
就是为临时密码 ;tTqBrpRs3#d
测试
root 账户的密码 错误
5.ALTER USER root@localhost IDENTIFIED BY 'root'; # 修改密码为：root
退出：exit
直接安装
2.直接安装方式
https://downloads.mysql.com/archives/installer/
一、双击运行安装包执行安装
1、选择Custom，该种方式可以设置安装位置，仅安装所需的组件，点击Next
2、选择需要的组件，点击Advanced Options
3、设置安装位置，点击OK
4、点击Next
5、点击Execute
6、点击Next
7、点击Next
8、选择配置类型，端口号等，直接默认即可，勾选Advanced Configuration下面的框，可以设置日志、服务器id等参数，点击Next
9、设置认证方式，选择第二项，可以兼容以前版本，点击Next
10、设置root账户，也可添加其他账户，并设置它们的角色，点击Next
11、配置mysql服务名，直接默认就好，点击Next
12、配置日志选项等，直接默认，点击Next
13、配置服务器id，该项一般用在主从服务器设置，本次直接默认，点击Next
14、应用配置，点击Execute
15、点击Finish
二、双击运行安装包执行卸载
1、点击Remove
2、勾选要卸载的产品，点击Next
3、勾选是否删除数据，本次勾选，点击Next
4、执行卸载，点击Execute
5、勾选卸载安装包，点击Finish，电脑进行重启，卸载完成！
MySQL客户端工具
Navicat
Navicat for MySQL 是管理和开发 MySQL 或 MariaDB 的理想解决方案。它是一套单一的应用程序，能同时连接 MySQL 和 MariaDB 数据库，并与 Amazon RDS、Amazon Aurora、Oracle Cloud、Microsoft Azure、阿里云、腾讯云和华为云等云数据库兼容。这套全面的前端工具为数据库管理、开发和维护提供了一款直观而强大的图形界面。
Navicat | 下载 Navicat Premium 14 天免费 Windows、macOS 和 Linux 的试用版
在使用 Navicat for Mysql连接mysql 8.0时会报如下错误：
mysql8.0 引入了新特性 caching_sha2_password；这种密码加密方式客户端不支持；客户端支持的是mysql_native_password 这种加密方式；
1.mysql -u root -p
2.USE mysql;
3.ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
4.FLUSH PRIVILEGES;
SQLyog
SQLyog 是一个快速而简洁的图形化管理MYSQL数据库的工具，它能够在任何地点有效地管理你的数据库，由业界著名的Webyog公司出品。
使用SQLyog可以快速直观地让您从世界的任何角落通过网络来维护远端的MySQL数据库。
Dbeaver
dbeaver是免费和开源（GPL）为开发人员和数据库管理员通用数据库工具。
易用性是该项目的主要目标，是经过精心设计和开发的数据库管理工具。免费、跨平台、基于开源框架和允许各种扩展写作（插件）。
它支持任何具有一个JDBC驱动程序数据库。
它可以处理任何的外部数据源。
MySQL连接不上怎么办？
我们如果在使用Navicat 连接 mysql服务器端 报如下错误：
解决办法：
1.检查mysql ip和端口号码是否正确
2.检查mysql服务是否启动（例如杀毒软件优化电脑过程中 会停止mysql服务）
数据库基本操作
DDL
数据库模式定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。
1.对数据库创建、删除、修改操作
2.对表结构创建、删除、修改操作
创建数据库（database）
n多张不同的表结构
mayiktuser1
mayiktuser2
.......
数据库操作
查询
查询所有数据库的名称: show databases; 查询某个数据库的字符集:查询某个数据库的创建语句 show create database 数据库名称; 查询当前正在使用的数据库名称 select database(); 使用数据库 use 数据库名称;
创建
创建数据库： create database 数据库名称; 创建数据库，判断不存在，再创建： create database if not exists 数据库名称; 创建数据库，并指定字符集 create database 数据库名称 character set 字符集名;
删除
语法： 删除数据库 drop database 数据库名称; 判断数据库存在，存在再删除 drop database if exists 数据库名称;
修改
注意：一般不对数据库进行修改操作，因为修改的安全性低，容易导致数据的缺失。 基本操作：修改数据库的字符集 alter database 数据库名称 character set 字符集名称;
表操作
创建表
create table if not exists 表名( 列名1 数据类型1[长度] 【字段约束】, 列名2 数据类型2[长度] 【字段约束】, .... 列名n 数据类型n[长度] 【字段约束】 ); * 注意：最后一列，不需要加逗号（,）
use mayiktmeite;
CREATE TABLE if not exists mayikt_user(
id INT,
name VARCHAR(20),
age int,
create_time date,
address VARCHAR(100)
);
修改
1.修改表名称：
语法：alter table 表名 rename to 新的表名;
ALTER TABLE mayikt_user RENAME TO meite_user ;
删除表
drop table 表名;
drop table if exists 表名;
数据类型
MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
数值类型
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 Bytes | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 Bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 Bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 Bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 Bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 Bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度浮点数值 |
| DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
| 类型 | 大小(bytes) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
字符串类型
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符
DML
数据操作语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和select 等。(增添改查）
DML与DDL区别？
DDL 对数据库 表结构 增加、修改 删除操作
DML 表结构中的数据 增加（insert）、修改（update） 删除（delete） 查询（select ）
insert
INSERT INTO 语句用于向表格中插入新的行。
语法格式：
INSERT INTO 表名称 (列名1，列名2 ...) VALUES (值1, 值2,....) // 插入数据列与值的顺序需要一一对应。
INSERT INTO 表名称 VALUES (值1, 值2,....) // 向表中插入所有列
例子：
INSERT INTO mayikt_user (id, name, age, addres, create_time) VALUES (1, 'mm', 1, '1', '2022-01-20 03:51:54');
INSERT INTO mayikt_user VALUES (1, 'mm', 1, '1', '2022-01-20 03:51:54');
update
语法格式：
update 表名称 set 字段=值，字段=值，...； ---直接修改整张表的所有行数据
update 表名称 set 字段=值，字段=值，... where 条件 ---- 根据条件查找到对应行数据 修改
例子:
UPDATE mayikt_user SETname= 'mm',age= 25 WHEREid` = 1;
UPDATE mayikt_user SETname= 'mm',age` = 25 ---修改所有
delete
语法格式：
delete from 表名称 where 条件 根据条件删除表中的数据
TRUNCATE mayikt_user --清空表中所有的数据
例子：
delete from mayikt_user where where id=1; 根据条件id=1 删除id=1 行数据
delete from mayikt_user ---清空表中所有的数据
TRUNCATE mayikt_user --清空表中所有的数据
TRUNCATE 与delete 不同？
truncate:会清空表中所有的数据，速度快，不可回滚；实质是删除整张表包括数据再重新创建表；
delete:逐行删除数据，每步删除都是有日志记录的，可以回滚数据；实质是逐行删除表中的数据；
作业题
dml 操作练习题
1.创建一个员工表employees 字段如下：
| id（员工编号） | name（员工名称） | sex(员工性别) 1男 0 女 | (salary)员工薪资 |
|---|---|---|---|
| 1 | mayikt | 1 | 3600 |
CREATE TABLE IF NOT EXISTS mayikt_employees (
id int ,
name varchar(20) ,
sex tinyint ,
salary double
);
2.插入一条数据
INSERT INTO mayikt_employees (id, name, sex, salary) VALUES (1, 'mayikt', 1, 3600);
3.修改 id=1 薪水 为8000
update mayikt_employees set salary='8000' where id=1
4.将表中的所有数据 名称为mayikt 改为meite
update mayikt_employees set name='meite'
5.修改 id=1 当前薪水+ 3000
update mayikt_employees set salary=salary+3000 where id=1
6.删除id=1 行数据
delete from mayikt_employees where id=1;
约束
什么是约束
用于限制表中的数据，为了保证表中数据的准确性和可靠性,不符合约束的数据，插入时就会失败。
约束条件在创建表时可以使用， 也可以修改表的时候添加约束条件
例如 用户信息表中 手机号码不允许为空，身份证号码不允许重复。
| id | 姓名(name) | 手机号码(phone) | 身份证号码(idCard) | 性别 | 地址 |
|---|---|---|---|---|---|
| 1 | mayikt | 18140663385 | 420923111111111111111 | 男 | |
| 2 | mayikt | 18140663385 | 420923111111111111112 |
约束作用
用于限制表中的数据，为了保证表中数据的准确性和可靠性,不符合约束的数据，插入时就会失败。
约束分类
1.NOT NULL ：非空，用于保证该字段的值不能为空。例如学生表的学生姓名及学号等等。
2.DEFAULT：默认值，用于保证该字段有默认值。例如学生表的学生性别
3.PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。例如学生表的学生学号等。
4.UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空。例如注册用户的手机号，身份证号等。
5.CHECK：检查约束（MySql不支持），检查字段的值是否为指定的值。
6.FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。例如学生表的专业编号
主键约束
1.MySQL 主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行，这样的一列或多列称为表的主键，通过它可以强制表的实体完整性，同时可以方便根据主键查询该行数据。
2.选取设置主键约束的字段 主键约束即在表中定义一个主键来唯一确定表中每一行数据的标识符,主键可以是表中的某一列或者多列的组合，其中由多列组合的主键称为复合主键,主键应该遵守下面的规则
3.每个表只能定义一个主键，主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在两行数据有相同的主键值，这是唯一性原则
4.在创建表时设置主键约束 在 CREATE TABLE 语句中，主键是通过 PRIMARY KEY 关键字来指定的
5.当创建主键的约束时，系统会默认所在的列和列组合 建立对应的索引，方便提高查询效率。
| id | 姓名 | 年龄 | 身份证号码(idCard) |
|---|---|---|---|
| 1 | mayikt | 22 | 420923111111111111111 |
| 2 |
| id | 姓名(name) | 手机号码(phone) | 身份证号码(idCard) | 性别 | 地址 |
|---|---|---|---|---|---|
| 1 | mayikt | 18140663385 | 420923111111111111111 | 男 | |
| 2 | mayikt | 18140663385 | 420923111111111111112 | 女 |
如果使用表中的 一列 主键-------单列主键
使用表中的 多个列（id，手机号码、身份证号码）多列-多列主键（复合主键）
条件：一张表中只能够允许有一个主键、主键值 不允许是为空 主键保证 每行数据完整唯一性
不允许重复的。
mysql 查询高级知识 索引--- 索引 方便提高查询效率
1.添加单列主键
2.添加多列联合主键
使用主键约束 PRIMARY KEY。
单列主键
1.定义字段时，指定单列主键
语法格式：
CREATE TABLE 表的名称 (
<字段名> <数据类型> PRIMARY KEY
}
CREATE TABLE `mayikt_users` (
`id` int PRIMARY KEY ,
`name` varchar(255) ,
`age` int
) ;
-
定义完字段之后，指定主键列（复合主键）
CREATE TABLE 表的名称 (
....
CONSTRAINT 主键名称 PRIMARY key (主键列);
}
CREATE TABLE `mayikt_users` (
`id` int ,
`name` varchar(255) ,
`age` int,
CONSTRAINT pk1 PRIMARY key (id,phone)
) ;
联合主键
联合主键（复合主键），由多个列（字段组成）。
注意事项：
1.当主键是有一个列组成时，不能够直接在字段名称后面声明主键约束；
2.一张表只能够允许一个主键
CREATE TABLE `mayikt_users` (
`id` int,
`name` varchar(255),
`age` int ,
`phone` varchar(11) ,
CONSTRAINT pk1 PRIMARY KEY (id,phone)
);
自定增长约束
设置自动增长
1.在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值，每增加一条记录，主键自动增加。
2.通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长
3.语法格式如下：
字段名 数据类型 AUTO_INCREMENT
4.默认情况下，AUTO_INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。
一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复(即为主键或主键的一部分)。
AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性。
AUTO_INCREMENT 约束的字段只能是整数类型(TINYINT、SMALLINT、INT、BIGINT 等)。
AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束，如果达到上限，AUTO_INCREMENT 就会失效。
注意：
1.一张表中只能有一个自动增长的字段2.配合主键一起使用 并且只适用于整数类型3.自动增长默认的初始值1，每增加一条记录，该字段的值会增加1
drop table mayikt_users;
CREATE TABLE `mayikt_users` (
`id` int PRIMARY KEY AUTO_INCREMENT ,
`name` varchar(255),
`age` int ,
`phone` varchar(11)
);
自动增长设置初始值
默认自动增长初始值是从1开始
mysql 指定自动增长字段初始值 1000 2000
1.创建表的时候指定
CREATE TABLE `mayikt_users` (
`id` int PRIMARY KEY AUTO_INCREMENT ,
`name` varchar(255),
`age` int ,
`phone` varchar(11)
)AUTO_INCREMENT=1000;
2.创建表之后修改自定增长开始值
alter table mayikt_users AUTO_INCREMENT 2000;
delete与truncate 删除数据区别
delete 删除数据之后，自动增长还是从最后一个删除数据的id基础上做自增;
truncate 清空数据之后 自动增长是从初始值1开始
delete from mayikt_users ---清空表数据
truncate mayikt_users ---清空表数据
TRUNCATE 与delete 不同？
truncate:会清空表中所有的数据，速度快，不可回滚；实质是删除整张表包括数据再重新创建表；
自动设定初始值 失效
delete:逐行删除数据，每步删除都是有日志记录的，可以回滚数据；实质是逐行删除表中的数据；
非空约束
1.MySql -- not null 非空约束用于确保当前列的值不为空；在创建表时，如果指定 not null 该字段在插入数据时
不允许为空;
2.语法格式；
2.1 创建表的时候 <字段名><数据类型>NOT NULL;
CREATE TABLE `mayikt_users` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`age` int NOT NULL,
`phone` varchar(11) NOT NULL,
PRIMARY KEY (`id`)
);
2.2 alter table 【数据库名.】表名称 modify 字段名 数据类型 not null;
唯一约束
MySQL 唯一约束（Unique Key）是指所有记录中该字段的值不能重复出现。例如为 phone 字段加上唯一性约束后，每条记录的 phone 值都是唯一的，不能出现重复的情况。如果其中一条记录的 phone 值为‘1880663389’，那么该表中就不能出现另一条记录的 phone 值也为‘1880663389’，唯一约束值可以允许null。、
主键约束 满足唯一性且值不允许为null 而我们的唯一约束 值允许为null
一张表结构中只允许有一个主键约束但是可以有多个唯一约束。
例如手机号码是唯一约束，每条记录的手机号码是不允许重复且保证唯一。
1.在创建表时设置唯一约束
语法规则:
UNIQUE KEY 约束名称 (约束字段)
CREATE TABLE `mayikt_users` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255),
`age` int DEFAULT NULL,
`phone` varchar(11) ,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_phone` (`phone`) -- 指定约束名称和约束字段
);
2.在修改表时添加唯一约束
语法规则如下：
ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE (<列名>)；
ALTER TABLE mayikt_users ADD CONSTRAINT unique_phone UNIQUE(phone);
[SQL]ALTER TABLE mayikt_users ADD CONSTRAINT unique_phone UNIQUE(phone);
[Err] 1062 - Duplicate entry '1880663389' for key 'mayikt_users.unique_phone'
该表中手机号码数据已经重复呢，
3.删除约束
ALTER TABLE mayikt_users DROP INDEX unique_phone;
默认约束
默认值（Default）的完整称呼是“默认值约束（Default Constraint）”，用来指定某列的默认值。在表中插入一条新记录时，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。其中，“默认值”为该字段设置的默认值，如果是字符类型的，要用单引号括起来。
1.在创建表时设置默认值约束
<字段名> <数据类型> DEFAULT <默认值>;
CREATE TABLE `mayikt_users` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT 'mayikt',
`age` int DEFAULT 1,
`phone` varchar(11) ,
PRIMARY KEY (`id`)
);
2.在修改表时添加默认值约束
ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;
ALTER TABLE mayikt_users
CHANGE COLUMN phone phone varchar(11) DEFAULT '1111';
零填充约束
定义了数据类型的长度，如果实际位数小于定义的长度，显示时会在左边用0填充
语法:
create table 表名称 ( 字段名称 int zerofill, ... );
id int(10)
id=1
0000000001
id=1000
0000001000
DQL
什么是DQL
1.DQL（Data Query Language）即数据库查询语言，用来查询所需要的信息，在查询的过程中，需要判断所查询的数据与表之间的关，我们可以使用select语句来查询数据。
select * from 表的名称 where 查询的条件
DML与DDL区别？
DDL 对数据库 表结构 增加、修改 删除操作
DML 表结构中的数据 增加（insert）、修改（update） 删除（delete）
2.查询语句语法格式
SELECT [ALL|DISTINCT] <目标列表达式> [别名] [ ,<目标列表达式> [别名]] … FROM <表名或视图名> [别名] [ ,<表名或视图名> [别名]] … [WHERE <条件表达式>] [GROUP BY <列名1> [HAVING <条件表达式>]] [ORDER BY <列名2> [ASC|DESC] [limit <数字或者列表>]
分页、分组、排序
select *(列名称) from 表名称 where 条件
1.查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件;
2.SELECT 命令可以读取一条或者多条记录;
3.使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
4.使用 WHERE 语句来包含任何条件。
5.使用 LIMIT 属性来设定返回的记录数。
6.OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
3.数据的初始化
-- 创建数据库 ddl
create DATABASE if not EXISTS mayikt;
-- 使用mayikt数据库
use mayikt;
drop table mayikt_student;
-- 创建mayikt_student学生表
CREATE TABLE `mayikt_student` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) not null COMMENT '姓名',
`age` tinyint COMMENT '年龄',
`address` varchar(255) COMMENT '地址',
`class_id` int COMMENT '班级id',
PRIMARY KEY (`id`)
);
-- 新增测试数据 dml
INSERT INTO mayikt_student VALUES(NULL,'余胜军',28,'湖北武汉','01');
INSERT INTO mayikt_student VALUES(NULL,'小哈',21,'上海','01');
INSERT INTO mayikt_student VALUES(NULL,'张三',17,'北京','02');
INSERT INTO mayikt_student VALUES(NULL,'李四',22,'山东','02');
INSERT INTO mayikt_student VALUES(NULL,'王麻子',11,'四川','02');
基本查询
-- 1.查询所有的学生 select * from mayikt_student; -- 2.查询学生的姓名和年龄 select name,age from mayikt_student; -- 3.别名称查询 使用关键字as select * from mayikt_student as student; -- 4.列别名称 select name as 姓名,age 年龄 from mayikt_student as student; -- 5.去重复值 select DISTINCT class_id from mayikt_student; -- 6.查询结果是表达式(运算值)；将所有的学生年龄+5岁 select name,age+5 as age from mayikt_student;
-- 1.查询所有的学生 -- 2.查询学生的姓名和年龄 -- 3.别名称查询 使用关键字as -- 4.列别名称 -- 5.去重复值 -- 6.查询结果是表达式(运算值)；将所有的学生年龄+5岁
运算符
数据库中的表结构确立后，表中的数据代表的意义就已经确定。而通过MySQL运算符进行运算，就可以获取到表结构以外的另一种数据。例如，学生表中存在一个birth（出生日期）字段，这个字段表示学生的出生年份。而运用MySQL的算术运算符用当前的年份减学生出生的年份，那么得到的就是这个学生的实际年龄数据。这就是MySQL的运算符，所以熟悉并掌握运算符的应用，我们需要熟悉一下MySQL支持的4种运算符都具备哪些功能。
| name | birth | |
|---|---|---|
| 余胜军 | 1997.10.7 | |
算术运算符
比较运算符
逻辑运算符
位运算符
算术运算符
算术运算符是MySQL中最常用的一类运算符。MySQL支持的算术运算符包括：加、减、乘、除、求余。
| 运算符 | 作用 |
|---|---|
| + | 加法 |
| - | 减法 |
| * | 乘法 |
| / 或 DIV | 除法 |
| % 或 MOD | 取余 |
select 6+2; select 6-2; select 6*2; select 6/2; select 6%2; -- 将每位学生的年龄+10 SELECT `name`,age +10 as age from mayikt_student; -- 将每位学生的年龄乘以5 SELECT `name`,age *5 as age from mayikt_student;
比较运算符
比较运算符是查询数据时最常用的一类运算符。SELECT语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。
| 符号 | 描述 | 备注 |
|---|---|---|
| = | 等于 | |
| <>, != | 不等于 | |
| > | 大于 | |
| < | 小于 | |
| <= | 小于等于 | |
| >= | 大于等于 | |
| BETWEEN | 在两值之间 | >=min&&<=max |
| NOT BETWEEN | 不在两值之间 | |
| IN | 在集合中 | |
| NOT IN | 不在集合中 | |
| <=> | 严格比较两个NULL值是否相等 | 两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0 |
| LIKE | 模糊匹配 | |
| REGEXP 或 RLIKE | 正则式匹配 | |
| IS NULL | 为空 | |
| IS NOT NULL | 不为空 |
逻辑运算符
逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回1。如果表达式是假，结果返回0。逻辑运算符又称为布尔运算符。MySQL中支持4种逻辑运算符，分别是与、或、非和异或。
| 运算符号 | 作用 |
|---|---|
| NOT 或 ! | 逻辑非 |
| AND | 逻辑与 |
| OR | 逻辑或 |
| XOR | 逻辑异或 |
位运算符
参与运算符的操作数，按二进制位进行运算。包括位与（&）、位或（|）、位非（~）、位异或（^）、左移（<<）、右移(>>)6种。
| 运算符号 | 作用 |
|---|---|
| & | 按位与 |
| | | 按位或 |
| ^ | 按位异或 |
| ! | 取反 |
| << | 左移 |
| >> | 右移 |
比较和逻辑运算符使用
--查询学生的名称是为余胜军 SELECT * from mayikt_student where name ='余胜军' --查询学生的名称不是为余胜军 SELECT * from mayikt_student where name !='余胜军' SELECT * from mayikt_student where name <>'余胜军' SELECT * from mayikt_student where not (name ='余胜军') --查询学生年龄是为17岁 SELECT * from mayikt_student where age=17 --查询学生年龄是大于17岁 SELECT * from mayikt_student where age>17 --查询学生年龄是小于17岁 SELECT * from mayikt_student where age<17 --查询学生年龄是18岁-40岁之间 SELECT * from mayikt_student where age>17 and age<41 SELECT * from mayikt_student where age>17 && age<41 SELECT * from mayikt_student where age BETWEEN 18 and 40 -- 查询年龄是在17或者 28岁的学生 SELECT * from mayikt_student where age=17 or age=28; SELECT * from mayikt_student where age=17 || age=28; SELECT * from mayikt_student where age in (17,28); --查询名称含有“军” SELECT * from mayikt_student where name like '%军%' --查询名称开头“小” SELECT * from mayikt_student where name like '小%' --查询名称第二字“汉” SELECT * from mayikt_student where name like '_汉%' --查询地址是为null学生 SELECT * from mayikt_student where address is null; --查询地址不是为null的学生 SELECT * from mayikt_student where address is not null;
--查询学生的名称是为余胜军 --查询学生的名称不是为余胜军 --查询学生年龄是为17岁 --查询学生年龄是大于17岁 --查询学生年龄是小于17岁 --查询学生年龄是18岁-40岁之间 -- 查询年龄是在17或者 28岁的学生 --查询名称含有“军” --查询名称开头“小” --查询名称第二字“汉” --查询地址是为null学生 --查询地址不是为null的学生
排序
如果我们需要对读取的数据进行排序，可以利用 order by 根据字段来进行升序或者降序排列 再返回结果。
升序： 从小到大
降序：从大到小
order by 根据字段 数字、字母、汉字
语法格式：
以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据： SELECT field1, field2,…fieldN table_name1, table_name2… ORDER BY field1, [field2…] [ASC [DESC]]
1.asc代表 升序排列 desc代表降序排列 默认是为升序;
升序就是从小到大、降序就是从大到小
2.order by 可以支持单个字段，多个字段 ；
3.order by 放在查询语句最后面，limit 分页除外；
1.根据学生年龄从小到大; SELECT * from mayikt_student order by age; 2.根据学生年龄从大到小; SELECT * from mayikt_student order by age desc; 3.判断学生的年龄大于18岁，在从小到大排序 SELECT * from mayikt_student where age>18 order by age ; 4.根据学生的年龄从大到小排序，以班级id 从小到大排序 当年龄相同 则根据 班级id从小到大排序 SELECT * from mayikt_student order by age desc ,class_id desc ; 5.根据班级id去重，根据班级id从大到小排序 SELECT DISTINCT class_id from mayikt_student ORDER BY class_id desc;
1.根据学生年龄从小到大; 2.根据学生年龄从大到小; 3.判断学生的年龄大于18岁，在从小到大排序 4.根据学生的年龄从大到小排序，以班级id 从小到大排序 当年龄相同 则根据 班级id从小到大排序 5.根据班级id去重，根据班级id从大到小排序
一般的情况下我们使用数字排序，从小到大 或者从大到小；
如果是字母排序 则 根据字母的顺序 从A到Z排序 或者Z到A顺序
如果是汉字的拼音排序，用的比较多是在人名的排序中，按照姓氏的拼音字母，从A到Z排序
分页
概述：limit
在mysql中当数据量很大时，显示屏长度有限，我们可以对数据进行分页显示，例如数据总共
100条，每页10条数据，可以分成10页。
格式：
方式1：显示前n条 select 字段1，字段2 ... from mayikt_student limit n 方式2：分页显示 select 字段1，字段2 ... from mayikt_student limit m,n
m: 整数，表示从第几条索引开始 计算方式(当前页-1)*每页显示条数
n：整数，表示查询多少条数据
案例：
SELECT * from mayikt_student limit 5; SELECT * from mayikt_student limit 5,5;
需求
1.查询用户表中前5条数据 SELECT * from mayikt_student limit 5; 2.从第6条开始显示 显示5条 SELECT * from mayikt_student limit 5,5;
聚合查询
我们在之前的查询是根据条件一行一行的判断，而使用聚合查询，它是对列的值进行计算，让后返回
一个单一的值。
| 聚合函数 | 作用 |
|---|---|
| count(age) | 统计指定列不为null的记录行数 |
| sum() | 计算指定列的数据和 |
| max() | 计算指定列的数据最大值 |
| min() | 计算指定列的数据最小值 |
| avg() | 计算指定列的数据平均值 |
1.查询学生表的总人数 SELECT count(*) from mayikt_student 2.查询学生年龄大于18的 总人数 SELECT count(*) from mayikt_student where age>18 3.查询classid=1 所有学生年龄总和 SELECT SUM(age) from mayikt_student where class_id='1' 4.查询学生最大年龄 SELECT max(age) from mayikt_student 5.查询学生最小年龄 SELECT max(age),min(age) from mayikt_student 6.求学生年龄的平均值 SELECT avg(age) from mayikt_student
1.查询学生表的总人数 2.查询学生年龄大于18的 总人数 3.查询classid=1 所有学生年龄总和 4.查询学生最大年龄 5.查询学生最小年龄 6.求学生年龄的平均值
聚合查询 null的处理
1.count函数对null值的处理
如果count函数的参数(*),则统计所有记录的行数，如果参数是为某字段，不统计含null值记录行数
2.sum、avg、max、min 函数对null 也是做忽略
1.查询学生年龄为null总人数 SELECT count(*) from mayikt_student where age is null;
分组查询
分组查询是指使用 group by 字句对查询信息进行分组。
湖北省
山东省
广东省
格式：
select 字段1，字段2 ... from 表名称 group by 分组字段 having 分组条件
分组的条件使用 having 不是where
案例:
SELECT class_id ,count(*) from mayikt_student group by class_id;
1.统计class_id 分类对应学生的个数 SELECT class_id ,count(*) from mayikt_student group by class_id;
注意分组 返回列 只能为 分组的字段或者聚合函数；
分组之后的条件筛选
1.分组之后对统计结果进行分组条件筛选 必须使用having，不能够使用where
2.HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集
语法格式：
SELECT 字段1，字段2 ... from mayikt_student GROUP BY 分组字段 having 分组条件;
案例需求：
统计每个班级学生人数大于1人以上的班级 SELECT class_id as 班级, count(*) as 人数 from mayikt_student GROUP BY class_id HAVING count(*) >1
先from 、 GROUP BY 、count 、HAVING
注意事项 ：
1、where 后不能跟聚合函数，因为where执行顺序大于聚合函数。
2、where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
3、having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。
作业题
练习题1
CREATE TABLE `meite_student` (
`id` int NOT NULL,
`name` varchar(255) DEFAULT NULL,
`sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`chinese` int DEFAULT NULL,
`english` int DEFAULT NULL,
`maths` int DEFAULT NULL
) ;
测试数据
INSERT INTO `mayikt`.`meite_student`
(`id`, `name`, `sex`, `chinese`, `english`, `maths`)
VALUES ('1', '余胜军', '男', '98', '99', '100');
INSERT INTO `mayikt`.`meite_student`
(`id`, `name`, `sex`, `chinese`, `english`, `maths`)
VALUES ('2', '刘文', '女', '99', '87', '88');
INSERT INTO `mayikt`.`meite_student`
(`id`, `name`, `sex`, `chinese`, `english`, `maths`)
VALUES ('3', '王麻子', '女', '22', '55', '33');
INSERT INTO `mayikt`.`meite_student`
(`id`, `name`, `sex`, `chinese`, `english`, `maths`)
VALUES ('4', '黄红军', '男', '55', '33', '22');
INSERT INTO `mayikt`.`meite_student`
(`id`, `name`, `sex`, `chinese`, `english`, `maths`)
VALUES ('5', '张玲', '女', '95', '95', '88');
1.查询表中所有学生信息 2.查询表中所有学生信息 只返回学生名称和数学成绩 字段 3.过滤表中重复数据 4.统计每个学生的总分 5.所有学生总分 加5分 6.查询语文成绩大于80分以上同学 7.查询总分大于180分以上同学 8.查询英语成绩80-90 之间的同学 9.查询英语成绩不在80-90 之间的同学 10.查询英语成绩22,99,33学生 信息 11.查询所有姓余的学生信息 like 12.查询语文、数学、外语 大于等于72分的学生信息 13.查询英语大于等于72分或者总分大于等于180分以上学生信息 or 14.根据英语成绩字段排序 升序和降序 15.根据总分成绩 从高到低排列 16.统计学生表中 性别男和女 分别有多少学生 17.根据英语成绩字段排序 (从高到低排序 取前3名) SELECT name, chinese+english+maths as 总分 FROM meite_student ORDER BY chinese+english+maths desc limit 3;
1.查询表中所有学生信息 select * from meite_student; 2.查询表中学生名称和数学成绩 select `name`,maths from meite_student; 3.过滤表中重复数据 select DISTINCT * from meite_student; 4.统计每个学生的总分 select name 姓名, chinese+english+maths 总分 from meite_student 5.所有学生总分 加5分 select name 姓名, (chinese+english+maths)+5 总分 from meite_student 6.查询语文成绩大于80分以上同学 select * from meite_student where chinese>80 7.查询总分大于180分以上同学 select name 姓名, (chinese+english+maths) as 总分 from meite_student where chinese+english+maths>180 8.查询英语成绩80-90 之间的同学 select * from meite_student where english >80 and english<90 select * from meite_student where english BETWEEN 80 and 90 9.查询英语成绩不在80-90 之间的同学 select * from meite_student where english not BETWEEN 80 and 90 10.查询英语成绩22,99,33学生 信息 select * from meite_student where english in(22,99,33) 11.查询所有姓余的学生信息 SELECT * from meite_student where name like '%余%' 12.查询语文、数学、外语 大于等于72分的学生信息 SELECT * from meite_student where chinese>=72 and english>=72 and maths>=72 13.查询语文和数学大于等于72分或者总分大于180分以上学生信息 SELECT * from meite_student where (english>=72 and maths>=72) or (chinese+english+maths)>180 14.根据英语成绩字段排序 升序和降序 SELECT * from meite_student ORDER BY english desc 15.根据总分成绩 从高到低排列 SELECT name as 姓名,(chinese+english+maths) as 总分 from meite_student order by (chinese+english+maths) asc 16.统计学生表中 姓名男和女 分表有多少学生 SELECT sex ,count(*) from meite_student GROUP BY sex
练习题2
需求：
-- 1. 根据员工的薪资升序或者降序排列 -- 2.根据员工的薪资升序排列，且不查询到财务部门的员工信息 -- 3.查询姓名第二字非“麻”的且薪资>=15000的员工信息，根据薪资升序排列 -- 4.查询每位员工综合年薪 根据年薪降序排列 -- 5.求每个不同部门的平均薪水 -- 6.求每个不同部门的平均薪水且平均薪资大于13000的部门 -- 7.求每个部门中最高薪水 -- 8.求每个部门有多少员工 -- 9.查询员工最高薪资和最低薪资员工信息 -- 10.查询员工最高薪资和最低薪资的差距
初始化sql：
CREATE TABLE `employee` (
`number` int DEFAULT NULL COMMENT '员工编号',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '姓名',
`hiredate` date DEFAULT NULL COMMENT '入职时间',
`salary` double DEFAULT NULL COMMENT '薪资',
`bonus` double DEFAULT NULL COMMENT '奖金',
`department` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门编号'
)
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1001', '余胜军', '2020-06-01', '18000', '3000', '研发部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1002', '刘军', '2019-06-01', '16000', '1000', '研发部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1003', '张三', '2018-02-10', '6000', '500', '财务部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1004', '王麻子', '2022-02-10', '27000', '500', '财务部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1005', '刘软', '2013-02-10', '6000', '500', 'UI部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1006', '王麻子', '2022-02-10', '6000', '500', 'UI部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1007', '李四', '2022-02-25', '5000', '100', '财务部门');
INSERT INTO `mayikt`.`employee` (`number`, `name`, `hiredate`, `salary`, `bonus`, `department`) VALUES ('1008', '余国军', '2022-02-25', '10000', '50', '研发部门');
-- 1. 根据员工的薪资升序或者降序排列 select * from employee order by salary desc ; select * from employee order by salary asc ; -- 2.根据员工的薪资升序排列，且不查询到财务部门的员工信息 select * from employee where department!='财务部门' order by salary asc -- 3.查询姓名第二字非“麻”的且薪资>=15000的员工信息，根据薪资升序排列 select * from employee where name not like '_麻%' and salary>=15000 order by salary asc ; -- 4.查询每位员工综合年薪 根据年薪降序排列 select name as 姓名 ,(salary*12)+bonus as 年薪 from employee order by (salary*12)+bonus desc -- 5.求每个不同部门的平均薪水 select department,avg(salary) from employee GROUP BY department -- 6.求每个不同部门的平均薪水且平均薪资大于13000的部门 select department,avg(salary) from employee GROUP BY department HAVING avg(salary)>13000 -- 7.求每个部门中最高薪水 select max(salary) from employee GROUP BY department -- 8.求每个部门有多少员工 select department, count(*) from employee GROUP BY department -- 9.查询员工最高薪资和最低薪资员工信息 select * from employee order by salary desc limit 1 select * from employee order by salary asc limit 1 -- 10.查询员工最高薪资和最低薪资的差距 select max(salary)-min(salary) from employee
多表关系
实际的项目开发中，一个项目通常需要很多张表才能够完成，例如在学生系统中 有学生班级表(stu_class)、学生信息表(stu_info)等多张表。这些表存在一定的关系。
会员信息--会员信息表
课程信息---课程详细内容
例如
1.一对一 使用的比较少
2.一对多
3.多对多
一对一
1.一个学生只有一张身份证，一个身份证只能够对应一个学生
2.一般一对一关系使用的比较少
一对多
例如 学生班级表(stu_class)、学生信息表(stu_info)等多张表
1.一个学生只要一个班级，学生信息表与班级表一一对应；
2.在表中添加一个外键，指向另一方主键，确保一对一关系;
部门与员工
例如：一个部门有多个员工，一个员工只能够对应一个部门
多对多
学生和课程
1.一个学生可以选择多门课程学习 ，一个课程也可以被很多学生选择
原则：多对多关系实现需要借助第三章中间表实现，中间表至少需要包含两个字段，
将多对多的关系拆分一对多的关系。
外键约束
MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。
外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性
注意事项：主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。
定义外键时，需要遵守下列规则：
1.主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
2.必须在主表定义主键。
3.主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中；
4.外键中列的数据类型必须和主表主键中对应列的数据类型相同。
创建主外键
在 CREATE TABLE 语句中，通过 FOREIGN KEY 关键字来指定外键，具体的语法格式如下：
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]
例如：
CREATE TABLE `mayikt_class` ( `id` int NOT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `mayikt_student` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `age` tinyint DEFAULT '0', `address` varchar(255) DEFAULT NULL, `class_id` int DEFAULT NULL, PRIMARY KEY (`id`), CONSTRAINT mayikt_class_id FOREIGN KEY (class_id) REFERENCES mayikt_class(id) -- 外键约束 ); [CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…] [CONSTRAINT <mayikt_class_id>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…] CONSTRAINT mayikt_class_id FOREIGN KEY (class_id) REFERENCES mayikt_class(id)
删除外键约束
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>; ALTER TABLE mayikt_student DROP FOREIGN KEY mayikt_class_id;
验证：外键约束
1.先向主表新增数据，在向从表新增数据
2.外键列的值必须要在主表存在 或者是为空
3.主表的数据不能够随便删除，从表数据可以随便删除 或者先
删除从表所有数据没有任何关联主表的字段 在可以删除主表的对应的数据
联表查询
多表查询是指多张表联合一起查询，例如学生信息与学生班级表、部门与员工表。
1.交叉连接查询 (笛卡尔积)
2.内连接查询
3.外连接查询
4.子查询
5.表自关联
数据的准备：
CREATE TABLE `mayikt_class` (
`id` int NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `mayikt_student` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`age` tinyint DEFAULT '0',
`address` varchar(255) DEFAULT NULL,
`class_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
CONSTRAINT mayikt_class_id FOREIGN KEY (class_id) REFERENCES mayikt_class(id) -- 外键约束
);
INSERT INTO `mayikt`.`mayikt_class` (`id`, `name`) VALUES ('1', '第一期');
INSERT INTO `mayikt`.`mayikt_class` (`id`, `name`) VALUES ('2', '第二期');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`, `class_id`) VALUES ('1', 'mayikt', '18', '武汉市', '1');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`, `class_id`) VALUES ('2', 'meite', '23', '上海市', '2');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`, `class_id`) VALUES ('3', '李思', '12', '孝感市', '1');
INSERT INTO `mayikt`.`mayikt_student` (`id`, `name`, `age`, `address`, `class_id`) VALUES ('4', '刘流', '27', '武汉市', '1');
交叉连接查询 (笛卡尔积)
语法：select * from mayikt_class,mayikt_student
得到的查询结果是两张表的笛卡尔积，也就是用A表中的每条数据都去匹配B表中的所有数据，获得的结果往往不是我们需要的，一般很少使用交叉连接,缺点数据比较冗余。
内连接
显示内连接:
SELECT * FROM A INNER JOIN B ON 条件;
隐示内连接:
SELECT * FROM A,B WHERE 条件;
案例
-- 1.查询每个班级下所有学生信息 -- 显示内连接 select * from mayikt_class INNER JOIN mayikt_student on mayikt_class.id =mayikt_student.class_id; -- 隐士内连接 SELECT * from mayikt_class ,mayikt_student where mayikt_student.class_id= mayikt_class.id -- 2.需求查询第一期所有学生 SELECT * from mayikt_class ,mayikt_student where mayikt_student.class_id= mayikt_class.id and mayikt_class.id='1' select * from mayikt_class INNER JOIN mayikt_student on mayikt_class.id =mayikt_student.class_id and mayikt_class.id='1' -- 3.查询第一期和第二期所有的学生 select * from mayikt_class INNER JOIN mayikt_student on mayikt_class.id =mayikt_student.class_id and (mayikt_class.id='1' or mayikt_class.id='2') select * from mayikt_class INNER JOIN mayikt_student on mayikt_class.id =mayikt_student.class_id and mayikt_student.class_id in (1,2) -- 4.查询每个班级下的学生总数 并且学生总数升序排列 select mayikt_class.`name` , count(*) from mayikt_class INNER JOIN mayikt_student on mayikt_class.id =mayikt_student.class_id GROUP BY mayikt_class.id order by count(*) asc -- 5.查询班级总人数>2的班级，并且人数降序排列 select mayikt_class.`name` , count(*) from mayikt_class INNER JOIN mayikt_student on mayikt_class.id =mayikt_student.class_id GROUP BY mayikt_class.id HAVING count(*)>2 order by count(*) asc
2.需求查询第一期所有学生
3.查询第一期和第二期所有的学生
4.查询每个班级下的学生总数 并且学生总数升序排列
5.查询班级总人数>2的班级，并且人数降序排列
外连接
外连接:左外连接、右外连接、全外连接（union）。
1.左外链接（left outer join，outer可以省略）
语法：SELECT * FROM A LEFT OUTER JOIN B ON 条件;
左外连接获得的查询结果是左边的表A的全部信息和A，B两张表的交集，左边A表的全部包含A表中在B表中没有对应关系的信息
2.右外连接（right outer join，outer可以省略）
语法：SELECT * FROM A RIGHT OUTER JOIN B ON 条件;
总结：右外连接获得的查询结果是右边的表B的全部信息和A，B两张表的交集，右边B表的全部包含B表中在A表中没有对应关系的信息
3.全外连接
select * from mayikt_class left join mayikt_student on mayikt_class.id=mayikt_student.class_id union select * from mayikt_class right join mayikt_student on mayikt_class.id=mayikt_student.class_id;
左连接 以左边为准 左变有该数据 就会返回 右变没有匹配上则直接返回为null
右连接 以右边为准 右变有该数据 就会返回 左变没有匹配上则直接返回为null
内连接左边与右边都是必须匹配才会返回
需求:
1.查询哪些班级是有学生 哪些班级是没有学生 select * from mayikt_class left join mayikt_student on mayikt_class.id=mayikt_student.class_id; 2.查询哪些学生是有班级，哪些学生是没有班级 select * from mayikt_class right join mayikt_student on mayikt_class.id=mayikt_student.class_id; 3.使用union关键字实现左连接和右连接的并集 让去重复数据
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
子查询
基本子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。
通俗易懂也就是SELECT 嵌套的查询
子查询中常用的操作符有 ANY（SOME）、ALL、IN 和 EXISTS。
需求：
1.查询年龄最大的学生信息，显示信息包含 学生的id、学生的名称、学生的年龄
select * from mayikt_student order by age desc limit 1
select * from mayikt_student where age in (select max(age) from mayikt_student)
2.查询第一期和第三期学生信息
select * from mayikt_student join mayikt_class on mayikt_student.class_id=mayikt_class.id where class_id='1' or class_id='3'
select * from mayikt_student where class_id in (select id from mayikt_class where id='1' or id='3' )
3.查询第一期学生年龄大于18岁 学生信息
select * from mayikt_student join mayikt_class on mayikt_student.class_id=mayikt_class.id and mayikt_student.class_id='1' and mayikt_student.age>18;
select * from mayikt_student where age >18 and class_id in ( select id from mayikt_class where id='1')
select * from (select * from mayikt_class where id='1') a1 join (select * from mayikt_student where age >18) a2 on a1.id=a2.class_id;
子查询关键字之all
all关键字用在比较操作操符的后面，表示查询结果的多个数据中的所有都满足该比较操作符才算满足
比较操作符：= 、>、!=、>=、<=等
select ... from mayikt_user（表的名称） where age（字段） > all(查询语句) 相当于： select ... from mayikt_user（表的名称） where age> result1 and age >result2
需求1：查询年龄大于第一期所有年龄的学生信息
select * from mayikt_student where age> all( select age from mayikt_student where class_id='1' )
需求2：查询没有班级的学生信息
select * from mayikt_student where class_id!= all( select id from mayikt_class )
子查询关键字之any
select ... from mayikt_user（表的名称） where age（字段） > any(查询语句) 相当于： select ... from mayikt_user（表的名称） where age> result1 or age >result2
需求：查询学生年龄大于第一期任意一个学生年龄的信息
select * from mayikt_student where age> any( select age from mayikt_student where class_id='1' ) and class_id!=1;
all 底层 多个 and 比较
any 底层 多个 or 或者比较
子查询关键字之not in和in
语法格式：
select * from mayikt_student where 字段 in（查询语句） select * from mayikt_student where 字段 =result1 or 字段 =result2
特点：
in关键字 用于判断某个记录的值 是否在指定的集合中
使用 not in 可以实现取反
需求：
查询第一期和第三期所有学生信息
select * from mayikt_student where class_id in (select id from mayikt_class where name='第一期' or name='第二期' )
in 子查询语句中 等于 or 连接比较
SELECT * from mayikt_student
where class_id=1 or class_id =3
all 子查询语句中 and 比较符
any 子查询语句中 or 比较符
子查询关键字之exists
EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
EXISTS比in关键字运算效率高，实际开发中 如果是数据量大的情况下 推荐使用EXISTS关键字。
语法：
select * from mayikt_student where EXISTS (查询语句---查询到结果 就返回true 没有查询到结果 就返回 false)
需求1:
1.查询学生年龄大于18岁 学生信息
select * from mayikt_student s1
where EXISTS(select * from mayikt_student s2
where s1.age >18
)
2.查询班级下 有学生的班级
select * from mayikt_class a where EXISTS( select * from mayikt_student b where b.class_id=a.id )
3.查询有班级的学生信息
select * from mayikt_student s1 where EXISTS( select * from mayikt_class b where s1.class_id = b.id )
子查询之自关联查询
对mysql数据自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。
注意 关联查询时必须给表取别名
select 字段列表 from 表名称 a ,表 名称b where 条件; 或者 select 字段列表 from 表名称 a left join 表名称 b on 条件
以京东电商为例子：
京东(JD.COM)-正品低价、品质保障、配送及时、轻松购物！
表结构
CREATE TABLE `commodity_type` ( `id` int NOT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '商品类型名称', `parent_id` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
select * from commodity_type as a ,commodity_type as b where a.id=b.parent_id;
多表综合练习题1
1.创建表结构
CREATE TABLE `mayikt_dept` (
`dept_no` int NOT NULL COMMENT '部门id',
`dept_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门名称',
`dept_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门地址',
PRIMARY KEY (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
CREATE TABLE `mayikt_emp` (
`emp_number` int DEFAULT NULL COMMENT '员工编号',
`emp_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工编号',
`emp_post` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工职务',
`emp_leader_number` int DEFAULT NULL COMMENT '员工领导编号',
`emp_hiredate` datetime DEFAULT NULL COMMENT '员工入职时间',
`emp_salary` double(10,0) DEFAULT NULL COMMENT '员工薪水',
`emp_bonus` int DEFAULT NULL COMMENT '员工奖金',
`emp_deptno` int DEFAULT NULL COMMENT '员工对外部门表外键'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
INSERT INTO `mayikt`.`mayikt_dept` (`dept_no`, `dept_name`, `dept_address`) VALUES ('1', '技术部门', '湖北武汉市');
INSERT INTO `mayikt`.`mayikt_dept` (`dept_no`, `dept_name`, `dept_address`) VALUES ('2', '财务部门', '中国上海市');
INSERT INTO `mayikt`.`mayikt_dept` (`dept_no`, `dept_name`, `dept_address`) VALUES ('3', '设计部门', '湖北孝感市');
INSERT INTO `mayikt`.`mayikt_emp` (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1001', '余胜军', 'CEO', NULL, '2021-11-01 11:32:46', '20000', '10000', '1');
INSERT INTO `mayikt`.`mayikt_emp` (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1002', '小薇', 'CFO', '1001', '2021-10-01 11:32:46', '5000', '10000', '2');
INSERT INTO `mayikt`.`mayikt_emp` (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1004', '张三', 'CTO', NULL, '2021-11-01 11:32:46', '80000', '10000', '1');
INSERT INTO `mayikt`.`mayikt_emp` (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1005', '李四', '技术总监', '1004', '2021-11-01 11:32:46', '20000', '10000', '1');
INSERT INTO `mayikt`.`mayikt_emp` (`emp_number`, `emp_name`, `emp_post`, `emp_leader_number`, `emp_hiredate`, `emp_salary`, `emp_bonus`, `emp_deptno`) VALUES ('1006', '王麻子', '客服', NULL, '2022-03-02 11:49:45', '3500', NULL, NULL);
1.返回员工拥有部门的 员工信息含员工部门 select * from mayikt_emp as a join mayikt_dept as b on a.emp_deptno=b.dept_no; 2.查询员工薪资大于小薇的 员工信息 select * from mayikt_emp where emp_salary>( select emp_salary from mayikt_emp where emp_number='1002' ) 3.返回员工所属领导信息 select * from mayikt_emp as a, mayikt_emp as b where a.emp_number=b.emp_leader_number 4.返回入职时间早于领导 入职时间 select * from mayikt_emp as a, mayikt_emp as b where a.emp_number=b.emp_leader_number and a.emp_hiredate>b.emp_hiredate 5.返回从事财务工作的员工信息 select * from mayikt_emp as a join mayikt_dept as b on a.emp_deptno=b.dept_no and b.dept_name='财务部门' 6.求每个部门 最低员工薪资 select emp_deptno,min(emp_salary) from mayikt_emp GROUP BY emp_deptno 7.返回员工薪资大于 平均薪资员工 select * from mayikt_emp where emp_salary >( select avg(emp_salary) from mayikt_emp )
多表综合练习题2
单独创建员工等级表
CREATE TABLE `mayikt_salgrade` ( `grade` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工等级', `losal` double(255,0) DEFAULT NULL COMMENT '最低工资', `hisal` double DEFAULT NULL COMMENT '最高工资' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
1.查询工资大于财务部所有员工的 信息 select * from mayikt_emp where emp_salary > all( select emp_salary from mayikt_emp where emp_salary and emp_deptno='2' ) 2.求从事相同职务工作最低工资和最高工资 select emp_post,min(emp_salary) as 最低工资,max(emp_salary) as 最高工资 from mayikt_emp GROUP BY emp_post 3.计算每位员工的年薪，并且以年薪降序排列 select ((emp_salary *12)+emp_bonus) as 年薪 from mayikt_emp ORDER BY 年薪 desc 4.返回工资处于P4级别员工的信息 select * from mayikt_emp where emp_salary BETWEEN ( select losal from mayikt_salgrade where grade='P4' ) and ( select hisal from mayikt_salgrade where grade='P4' ) 5.返回工资处于P1员工信息含部门名称 select * from mayikt_emp as e join mayikt_dept as d on e.emp_deptno=d.dept_no join mayikt_salgrade s on s.grade='p1' and e.emp_salary>=s.losal and e.emp_salary <=s.hisal
1.查询工资大于财务部所有员工的 信息
2.求从事相同职务工作最低工资和最高工资
3.计算每位员工的年薪，并且以年薪降序排列
4.返回工资处于P4级别员工的信息
5.返回工资处于P1员工信息含部门名称
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
