2023面试高手

这个屌丝很懒，什么也没留下！

热门标签

MySQL8.0零基础入门_mysql8.0怎么使用

作者：2023面试高手 | 2024-05-08 09:51:20

踩

mysql8.0怎么使用

一、安装MySQL

1.进入 MySQL 官网下载 MySQL 安装包(mysql-installer-community-8.0.24.0.msi)

https://downloads.mysql.com/archives/installer/

2.双击.exe 安装文件，等待一会

3.选择 Custom

4.选择 MySQL Server 8.0.24 -X64

5.点击 Execute 执行, 如果弹出窗口则打钩并安装 VC++

6.安装后点击 Next

7.点击 Execute 执行

8.继续点击 Next, Next，到配置页面

9.保持默认选择，点击 Next

10.输入 MySQL 的 root 用户密码，并牢记！

11.点击 Next

12.点击 Execute

13.点击 Finish 完成安装

二、配置 MySQL 环境变量

1.找到 MySQL 安装路径的 bin 目录：

C:\Program Files\MySQL\MySQL Server 8.0\bin

2.右键“我的电脑”，点击“属性”，在弹出的窗口中点击“高级系统设置”

3.点击“环境变量”

4.点击系统变量中的“Path”

5.点击“新建”，将 MySQL 安装目录的 bin 路径粘贴进去，然后点击“上移”

置顶，最后连续点击“确定”

三、启动 MySQL 和登录 MySQL

1.启动 MySQL (安装后默认已启动)

2.登录 MySQL (① mysql -u root -p ②输入 MySQL 密码)

四、MySQL基础

1、数据库:是一种逻辑概念，用来存放数据的仓库，通过数据库软件来实现。数据库由很多表组成，表是二维的，一张表里面有很多字段。字段一字排开，对数据就一行一行的写入表中。数据库的表，在于能够用二维表现多维的关系。如：oracle、DB2、MySQL、Sybase、MSSQL Server等。

2、数据仓库：是数据库概念的升级。从逻辑上理解，数据库和数据仓库没有区别，都是通过数据库软件实现存放数据的地方，只不过从数据量来说，数据仓库要比数据库更庞大德多。数据仓库主要用于数据挖掘和数据分析，辅助领导做决策；如：AWS Redshift, Greenplum, Hive等. 简称是:ETL，是英文 Extract-Transform-Load

区别主要总结为以下几点：

1.数据库只存放在当前值，数据仓库存放历史值；

2.数据库内数据是动态变化的，只要有业务发生，数据就会被更新，而数据仓库则是静态的历史数据，只能定期添加、刷新；

3.数据库中的数据结构比较复杂，有各种结构以适合业务处理系统的需要，而数据仓库中的数据结构则相对简单；

4.数据库中数据访问频率较高，但访问量较少，而数据仓库的访问频率低但访问量却很高；

5.数据库中数据的目标是面向业务处理人员的，为业务处理人员提供信息处理的支持，而数据仓库则是面向高层管理人员的，为其提供决策支持；

6.数据库在访问数据时要求响应速度快，其响应时间一般在几秒内，而数据仓库的响应时间则可长达数几小时

关于数据库和数据仓库的本质区别到底是什么？我们先来看一个例子。

拿电商行业来说好了。基本每家电商公司都会经历，从只需要业务数据库到要数据仓库的阶段。

第一阶段, 电商早期启动非常容易，入行门槛低。找个外包团队，做了一个可以下单的网页前端 + 几台服务器 + 一个MySQL，就能开门迎客了。这好比手工作坊时期。

第二阶段，流量来了，客户和订单都多起来了，普通查询已经有压力了，这个时候就需要升级架构变成多台服务器和多个业务数据库（量大+分库分表），这个阶段的业务数字和指标还可以勉强从业务数据库里查询。初步进入工业化。

第三个阶段，一般需要 3-5 年左右的时间，随着业务指数级的增长，数据量的会陡增，公司角色也开始多了起来，开始有了 CEO、CMO、CIO，大家需要面临的问题越来越复杂，越来越深入。高管们关心的问题，从最初非常粗放的：“昨天的收入是多少”、“上个月的 PV、UV 是多少”，逐渐演化到非常精细化和具体的用户的集群分析，特定用户在某种使用场景中，例如“20~30岁女性用户在过去五年的第一季度化妆品类商品的购买行为与公司进行的促销活动方案之间的关系”。

这类非常具体，且能够对公司决策起到关键性作用的问题，基本很难从业务数据库从调取出来。原因在于： 1、业务数据库中的数据结构是为了完成交易而设计的，不是为了查询和分析的便利设计的。 2、业务数据库大多是读写优化的，即又要读（查看商品信息），也要写（产生订单，完成支付）。因此对于大量数据的读（查询指标，一般是复杂的只读类型查询）是支持不足的。

1.数据库简介

1.1数据库系统

1.1.1数据库

DataBase【DB】，指的是长期保存到计算机上的数据，按照一定顺序组织，可以被各种用户或者应用共享的数据集合

持久化存储 / 临时存储(缓存)

【用于存储数据的地方，可以视为存储数据的容器】

1.1.2数据库管理系统

DataBase Management System【DBMS】, 能够管理和操作数据库的大型的软件

数据库是CS模式: Client/Server

用于建立、使用和维护数据库，对数据库进行统一的管理和控制，为了保证数据库的安全性和完整性，用户可以通过数据库管理系统访问数据库中的数据

1.1.3数据库的应用

涉及到大量的数据需要长期存储，就可以使用数据库

使用：增删改查的操作 (curd) create update read delete

持久化：数据持久化, 一般存在硬盘，MySQL

缓存：临时存储，一般可以存在内存, Redis

1.2常见数据库管理系统

1）Oracle（甲骨文）: 目前比较成功的关系型数据库管理系统，运行稳定，功能齐全，性能超群，技术领先，主要应用在大型的企业数据库领域, 收费.

2）DB2: IBM（国际商业机器公司）的产品，伸缩性比较强

3）SQL Server: Microsoft的产品，软件界面友好，易学易用，在操作性和交互性方面独树一帜

4）MySQL: 免费的数据库系统，被广泛引用于中型应用系统，体积小，速度快，总体拥有成本低，开放源代码，2008年被SUN收购，2009年SUN被Oracle收购

2.SQL概述

2.1SQL简介

Structure Query Language，结构化查询语言

2.2数据库服务器、数据库和表之间的关系

表：为了保存应用实体中的数据，一般会给数据库中创建表，一个数据库可以同时管理多个表

2.3数据在SQL中的存储形式

数据库 => 表

表: 表格table

列：表示字段, id， name, age

行：代表一条数据，一条记录或一个实体

id name age
1 马云 50
2 马化腾 40
3 马明哲 60

id	name	age
1	马云	50
2	马化腾	40
3	马明哲	60

2.4SQL的分类

DDL【Data Definition Language】,数据定义语言，用户创建、修改、删除表结构

DML【Data Manipulation Language】,数据操作语言，用于对表数据进行增删改的操作

DQL【Data Query Language】,数据查询语言，用于负责表数据的查询工作

DCL【Data Control Language】：数据控制语言，用来定义访问权限和安全级别

3.数据库操作

3.1DDL

使用关键字：CREATE ALTER DROP

注意：一般情况下，mysql关键字是大写的，但是为了方便，一般小写

3.1.1 create创建

语法：


登录
mysql -uroot -p密码
 
查看所有的数据库
show databases; 
 
#1.创建数据库
CREATE DATABASE database_name charset=utf8;
 
例:创建数据库test.
create database test charset=utf8;  
 
2.使用数据库
use 数据库名
use test;
 
#3.创建表
CREATE TABLE 表名 (
		字段1 字段类型[列级别约束条件][默认值]，
		字段2 字段类型[列级别约束条件][默认值]，
		….
		字段n 字段类型[列级别约束条件][默认值]
		[表级别约束条件]
)
 
# 创建user表
create table user(
	id int primary key auto_increment, 
	name varchar(20), 
	age int
);
 
# 查看所有的表:
show tables;
 
# 查看创建数据表的结构.
show create table 表名
desc 表名;

演示：


#查询当前数据库服务器中的所有数据库
mysql> show databases;			    
+--------------------+	
| Database           |
+--------------------+
| information_schema |
| mydb1              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)
 
#查看当前正在使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| mydb1      |
+------------+
1 row in set (0.00 sec)
 
#切换数据库
mysql> use mydb1;
Database changed
 
#退出数据库
演示命令：
#方式一
mysql> exit;
Bye
 
#方式二
mysql> quit;
Bye
 
#注意：如要再次使用数据库，则需要重新登录

3.1.2alter操作

a.常用数据类型

1.数字数据类型
1）int:
   正常大小的整数，可以带符号。如果是有符号的，它允许的范围是从-2147483648到2147483647。如果是无符号，允许的范围是从0到4294967295。可以指定多达11位的宽度。 int => int(11)

2）tinyint:
   一个非常小的整数，可以带符号。如果是有符号，它允许的范围是从-128到127。如果是无符号，允许的范围是从0到255，可以指定多达4位数的宽度。 int(4)

3）smallint:
   一个小的整数，可以带符号。如果有符号，允许范围为-32768至32767。如果无符号，允许的范围是从0到65535，可以指定最多5位的宽度。 int(5)

4）mediumint:
   一个中等大小的整数，可以带符号。如果有符号，允许范围为-8388608至8388607。如果无符号，允许的范围是从0到16777215，可以指定最多9位的宽度。int(9)

5）bigint:
   一个大的整数，可以带符号。如果有符号，允许范围为-9223372036854775808到9223372036854775807。如果无符号，允许的范围是从0到18446744073709551615. 可以指定最多20位的宽度。 int(20)

6）float(M,D):
   不能使用无符号的浮点数字。可以定义显示长度(M)和小数位数(D)。这不是必需的，并且默认为10,2。其中2是小数的位数，10是数字(包括小数)的总数。小数精度可以到24个浮点。 float(10,2)

7）double(M,D):
       不能使用无符号的双精度浮点数。可以定义显示长度(M)和小数位数(D)。这不是必需的，默认为16,4，其中4是小数的位数。小数精度可以达到53位的DOUBLE。 REAL是DOUBLE同义词。 double(16,4)

8）decimal(M,D):
   非压缩浮点数不能是无符号的。在解包小数，每个小数对应于一个字节。定义显示长度(M)和小数(D)的数量是必需的。 NUMERIC是DECIMAL的同义词。[decimal]

2.日期和时间类型
1）date:
   以YYYY-MM-DD格式的日期，在1000-01-01和9999-12-31之间。例如，1973年12月30日将被存储为1973-12-30。
2）datetime:
   日期和时间组合以YYYY-MM-DD HH:MM:SS格式，在1000-01-01 00:00:00 到9999-12-31 23:59:59之间。例如，1973年12月30日下午3:30，会被存储为1973-12-30 15:30:00。
3）timestamp:
   1970年1月1日午夜之间的时间戳，到1973的某个时候。这看起来像前面的DATETIME格式，无需只是数字之间的连字符; 1973年12月30日下午3点30分将被存储为19731230153000(YYYYMMDDHHMMSS)。
4）time:
   存储时间在HH:MM:SS格式。
5）year(M):
   以2位或4位数字格式来存储年份。如果长度指定为2(例如YEAR(2))，年份就可以为1970至2069(70〜69)。如果长度指定为4，年份范围是1901-2155，默认长度为4。

3.字符串类型
虽然数字和日期类型比较有意思，但存储大多数数据都可能是字符串格式。下面列出了在MySQL中常见的字符串数据类型。
1）char(20):
       固定长度的字符串是以长度为1到255之间个字符长度(例如：CHAR(5))，存储右空格填充到指定的长度。限定长度不是必需的，它会默认为1。 name char(50)
2）varchar(M):
   可变长度的字符串是以长度为1到255之间字符数(高版本的MySQL超过255); 例如： VARCHAR(25). 创建VARCHAR类型字段时，必须定义长度。 [varchar] name varchar(255)

3）blob or text:
   字段的最大长度是65535个字符。 BLOB是“二进制大对象”，并用来存储大的二进制数据，如图像或其他类型的文件。定义为TEXT文本字段还持有大量的数据; 两者之间的区别是，排序和比较上存储的数据，BLOB大小写敏感，而TEXT字段不区分大小写。不用指定BLOB或TEXT的长度。
4）tinyblob 或 tinytext:
   BLOB或TEXT列用255个字符的最大长度。不指定TINYBLOB或TINYTEXT的长度。
5）mediumblob or mediumtext:
       BLOB或TEXT列具有16777215字符的最大长度。不指定MEDIUMBLOB或MEDIUMTEXT的长度。
6）longblob 或 longtext:
   BLOB或TEXT列具有4294967295字符的最大长度。不指定LONGBLOB或LONGTEXT的长度。

enum:
   枚举，这是一个奇特的术语列表。当定义一个ENUM，要创建它的值的列表，这些是必须用于选择的项(也可以是NULL)。例如，如果想要字段包含“A”或“B”或“C”，那么可以定义为ENUM为 ENUM(“A”，“B”，“C”)也只有这些值(或NULL)才能用来填充这个字段。在处理互斥数据时使用的居多.比如人类的性别.enum("男","女"), enum(1,0)
set:集合类型：

set("m1","m2","m3"....)用于保存多个同类型的数据,比如爱好.

注意：主要了解 char 和 varchar 的区别

char(M)是固定长度的字符串，在定义时指定字符串列长。当保存数据时如果长度不够在右侧填充空格以达到指定的长度。M 表示列的长度，M 的取值范围是0-255个字符 name char(20)

varchar(M)是长度可变的字符串，M 表示最大的列长度。M 的取值范围是0-65535。varchar的最大实际长度是由最长的行的大小和使用的字符集确定的，而实际占用的空间为字符串的实际长度+1

name varchar(30)

主要使用的数据类型：

数字型数据类型：int float

日期类：datetime timestamp

字符串：varchar(num) text【长字符串】

b.alert语法

#1.修改表名
语法规则：ALTER TABLE old_table_name RENAME [TO] new_table_name

alter table user rename py2105;
alter table py2105 rename to user;

#2.修改字段的数据类型
语法规则：ALTER TABLE table_name MODIFY 字段名数据类型
修改完成之后可以查看DESC table_name检验结果

alter table user modify age varchar(200);

#3.修改字段名
语法规则：ALTER TABLE table_name CHANGE 旧字段名新字段名数据类型

alter table user change name xingming char(20);

#4.添加字段
语法规则：ALTER TABLE table_name ADD 新字段名数据类型 [约束条件] [FIRST|AFTER 已经存在的字段名]

alter table user add score int(10);

#5.删除字段
语法规则：ALTER TABLE table_name DROP 字段名

#6.修改字段的排列位置
语法规则：ALTER TABLE table_name MODIFY 字段1 数据类型 FIRST|AFTER 字段2
first: 设置成第一个
after 字段2：在指定字段2的后面

#7.删除表的外键约束
语法规则：ALTER TABLE table_name DROP FOREIGN KEY 外键约束名（不是字段名）

#8.删除数据表
#删除没有被关联的表
语法规则：DROP TABLE [IF EXISTS] 表1，表2...
#删除被其他表关联的的表
直接删除会出现错误的，操作：先解除关联再进行删除

c.需求：创建一个员工表如下图

演示：


#切换数据库
mysql> use mydb1			
Database changed
#查看当前正在使用的数据库
mysql> select database();	
+------------+
| database() |
+------------+
| mydb1      |
+------------+
1 row in set (0.00 sec)
 
#在当前数据库下创建新的表
mysql> create table worker(			
-> id int(11) primary key auto_increment,
-> name varchar(20),
-> gender varchar(10),
-> brithday date,
-> entry_date date,
-> job varchar(20),
-> salary double,
-> resume text
-> );
# primary key表示主键    auto_increment 表示自动增长.   一般用于id字段  一个数据表一般情况下有一个主键字段,是id. 这个字段是自动增长的,不用维护.是唯一的.
Query OK, 0 rows affected (0.02 sec)
 
 
#显示当前数据库中的所有表
mysql> show tables;			
+-----------------+
| Tables_in_mydb1 |
+-----------------+
| worker          |
+-----------------+
1 row in set (0.00 sec)
 
#显示指定表中的所有字段
mysql> desc worker;			
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | NO   | PRI | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| gender     | varchar(10) | YES  |     | NULL    |       |
| brithday   | date        | YES  |     | NULL    |       |
| entry_date | date        | YES  |     | NULL    |       |
| job        | varchar(20) | YES  |     | NULL    |       |
| salary     | double      | YES  |     | NULL    |       |
| resume     | blob        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
 
#增加字段image
mysql> alter table worker add image blob;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc worker;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| gender     | varchar(10) | YES  |     | NULL    |       |
| brithday   | date        | YES  |     | NULL    |       |
| entry_date | date        | YES  |     | NULL    |       |
| job        | varchar(20) | YES  |     | NULL    |       |
| salary     | double      | YES  |     | NULL    |       |
| resume     | blob        | YES  |     | NULL    |       |
| image      | blob        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
 
#修改job的长度为60
mysql> alter table worker modify job varchar(60);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc worker;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| gender     | varchar(10) | YES  |     | NULL    |       |
| brithday   | date        | YES  |     | NULL    |       |
| entry_date | date        | YES  |     | NULL    |       |
| job        | varchar(60) | YES  |     | NULL    |       |
| salary     | double      | YES  |     | NULL    |       |
| resume     | blob        | YES  |     | NULL    |       |
| image      | blob        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
 
#删除image字段
mysql> alter table worker drop image;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc worker;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| gender     | varchar(10) | YES  |     | NULL    |       |
| brithday   | date        | YES  |     | NULL    |       |
| entry_date | date        | YES  |     | NULL    |       |
| job        | varchar(60) | YES  |     | NULL    |       |
| salary     | double      | YES  |     | NULL    |       |
| resume     | blob        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
 
#对表名重新命名
#方式一
mysql> rename table worker to user;
Query OK, 0 rows affected (0.00 sec)
#方式二
mysql> alter table  worker rename to user;
Query OK, 0 rows affected (0.00 sec)
 
#查看表的信息
mysql> desc user;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| gender     | varchar(10) | YES  |     | NULL    |       |
| brithday   | date        | YES  |     | NULL    |       |
| entry_date | date        | YES  |     | NULL    |       |
| job        | varchar(60) | YES  |     | NULL    |       |
| salary     | double      | YES  |     | NULL    |       |
| resume     | blob        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
 
 
#查看创建表的详细信息
mysql> show create table user;
| Table | Create Table                                                                                                   
| user  | CREATE TABLE `user` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`gender` varchar(10) DEFAULT NULL,
`brithday` date DEFAULT NULL,
`entry_date` date DEFAULT NULL,
`job` varchar(60) DEFAULT NULL,
`salary` double DEFAULT NULL,
`resume` blob
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
1 row in set (0.00 sec)
 
 
#修改表的字符集为gbk
mysql> alter table user character set gbk;	
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show create table user;			#查看信息，已经改为gbk
 
| Table | Create Table                                       
| user  | CREATE TABLE `user` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
`gender` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
`brithday` date DEFAULT NULL,
`entry_date` date DEFAULT NULL,
`job` varchar(60) CHARACTER SET latin1 DEFAULT NULL,
`salary` double DEFAULT NULL,
`resume` blob
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
 
1 row in set (0.00 sec)
 
#将列名name修改为username
mysql> alter table user change name username varchar(100);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> desc user;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | YES  |     | NULL    |       |
| username   | varchar(100) | YES  |     | NULL    |       |
| gender     | varchar(10)  | YES  |     | NULL    |       |
| brithday   | date         | YES  |     | NULL    |       |
| entry_date | date         | YES  |     | NULL    |       |
| job        | varchar(60)  | YES  |     | NULL    |       |
| salary     | double       | YES  |     | NULL    |       |
| resume     | blob         | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

3.1.3drop删除

语法：


# 删除数据库
DROP DATABASE database_name

演示：


# 删除数据库
drop database mysql1;

3.2DML: 增删改

3.2.1 insert 插入

语法：


#单行插入
第一种:INSERT INTO table_name (field1, field2,...fieldN) VALUES(value1, value2,...valueN);
或者
第二种:INSERT INTO table_name VALUES(value1, value2,...valueN);  这种写法,每个字段都要去写.
 
#多行插入[批量插入]
第三种:INSERT INTO table_name (field1, field2,...fieldN)
              VALUES
              (value1, value2,...valueN),
              (value12, value22,...valueNN)...;
 
第一种和第三种方式使用的比较多.
 
注意：
	a.列名和列值的类型、个数以及顺序一一对应
	b.可以把列名当做Python中的形参，把列值当做实参
	c.值不能超出列定义的长度
	d.如果插入的是空值，写Null/null
	e.插入的是日期，和字符串一样，使用引号括起来  "1988-11-22 10:11:12"
 
select * from 表名;  查询数据表中所有的字段信息.

演示：


mysql> use mydb1;
Database changed
mysql> show tables;
Empty set (0.00 sec)
 
mysql> create table worker(
-> id int(11) primary key auto_increment,
-> name varchar(20),
-> gender varchar(10),
-> salary double
-> );
Query OK, 0 rows affected (0.01 sec)
 
mysql> show tables;                                                             +-----------------+
| Tables_in_mydb1 |
+-----------------+
| worker          |
+-----------------+
1 row in set (0.00 sec)
 
mysql> desc worker;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
| salary | double      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
#插入单条数据
mysql> insert into worker(iname,gender,salary) values('tom','b',4000);
Query OK, 1 row affected (0.02 sec)
 
mysql> insert into worker(name,gender,salary) values('jack','b',6000);
Query OK, 1 row affected (0.01 sec)
 
#如果给每个字段都赋值，就可以省略掉字段的书写
mysql> insert into worker values(3,'rose','g',6000);
Query OK, 1 row affected (0.01 sec)
 
mysql> desc worker;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
| salary | double      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
# 查看当前表内所有的数据
mysql> select * from worker;
+------+------+--------+--------+
| id   | name | gender | salary |
+------+------+--------+--------+
|    1 | tom  | b      |   4000 |
|    2 | jack | b      |   6000 |
|    3 | rose | g      |   6000 |
+------+------+--------+--------+
3 rows in set (0.00 sec)
 
#一次性插入多条数据【批量插入】
mysql> insert into worker(name,gender,salary) values('bob','b',1500),('hello','g',5500),('abc','b',6600);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select * from worker;                                                   
+------+-------+--------+--------+
| id   | name  | gender | salary |
+------+-------+--------+--------+
|    1 | tom   | b      |   4000 |
|    2 | jack  | b      |   6000 |
|    3 | rose  | g      |   6000 |
|    4 | bob   | b      |   1500 |
|    5 | hello | g      |   5500 |
|    6 | abc   | b      |   6600 |
+------+-------+--------+--------+
6 rows in set (0.00 sec)

3.2.2 update更新

语法：


UPDATE table_name SET field1=new-value1, field2=new-value2  [WHERE Clause]
 
update user set name = "大狗",age = 29 where id = 2; 
注意：
	a.完全可以更新一个字段或者多个字段
	b.where相当于Python中的if语句
	c.可以指定任何条件到where子句中
	d.如果没有where子句，则默认所有的行都被同时更新为指定的操作[慎用！一般要结合where使用]
	e.where后面的条件一般使用id字段.

演示：


#1.将所有员工的薪水修改为5000
mysql> update worker set salary=5000;
Query OK, 6 rows affected (0.01 sec)
Rows matched: 6  Changed: 6  Warnings: 0
 
mysql> select * from worker;
+------+-------+--------+--------+
| id   | name  | gender | salary |
+------+-------+--------+--------+
|    1 | tom   | b      |   5000 |
|    2 | jack  | b      |   5000 |
|    3 | rose  | g      |   5000 |
|    4 | bob   | b      |   5000 |
|    5 | hello | g      |   5000 |
|    6 | abc   | b      |   5000 |
+------+-------+--------+--------+
6 rows in set (0.00 sec)
 
#2.将tom的薪水改为3000元
mysql> update worker set salary=3000 where name='tom';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from worker;
+------+-------+--------+--------+
| id   | name  | gender | salary |
+------+-------+--------+--------+
|    1 | tom   | b      |   3000 |
|    2 | jack  | b      |   5000 |
|    3 | rose  | g      |   5000 |
|    4 | bob   | b      |   5000 |
|    5 | hello | g      |   5000 |
|    6 | abc   | b      |   5000 |
+------+-------+--------+--------+
6 rows in set (0.00 sec)
 
#3.将jack的薪水改为10000，并将性别改为g
mysql> update worker set salary=10000,gender='g' where name='jack';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from worker;
+------+-------+--------+--------+
| id   | name  | gender | salary |
+------+-------+--------+--------+
|    1 | tom   | b      |   3000 |
|    2 | jack  | g      |  10000 |
|    3 | rose  | g      |   5000 |
|    4 | bob   | b      |   5000 |
|    5 | hello | g      |   5000 |
|    6 | abc   | b      |   5000 |
+------+-------+--------+--------+
6 rows in set (0.00 sec)
 
#4.将rose的薪水在原来的基础上增加1000
mysql> update worker set salary=salary+1000 where name='rose';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from worker;
+------+-------+--------+--------+
| id   | name  | gender | salary |
+------+-------+--------+--------+
|    1 | tom   | b      |   3000 |
|    2 | jack  | g      |  10000 |
|    3 | rose  | g      |   6000 |
|    4 | bob   | b      |   5000 |
|    5 | hello | g      |   5000 |
|    6 | abc   | b      |   5000 |
+------+-------+--------+--------+
6 rows in set (0.01 sec)
 
#5.将abc的薪水改为6000
update worker set salary=6000 where name='abc';
#6.将bob的性别改为b
update worker set gender='b' where name='bob';

where子句

语法：

注意：where子句其实就是一个操作符，类似于Python中的if语句，可以做数据的筛选

操作符说明
= 相等
<> / != 不相等
< 小于
<= 小于等于
> 大于
>= 大于等于
in (A，B) A 和 B 中间的一个
between A and B 位于两值之间
AND 连接多个表达式并且的关系

操作符	说明
=	相等
<> / !=	不相等
<	小于
<=	小于等于
>	大于
>=	大于等于
in (A，B)	A 和 B 中间的一个
between A and B	位于两值之间
AND	连接多个表达式并且的关系

3.2.3delete删除

语法：


DELETE FROM table_name [WHERE Clause]       物理删除
is_del  (1 or 0)   1表示删除   0表示未删除      逻辑删除   update table 表名 set is_del = 1
 
注意：
	a.如果where子句没有指定，则默认将表中的数据全部删除【慎用！】
	b.可以指定任何条件在where子句中

演示：


#1.删除表中tom的全部数据
mysql> delete from worker where name='tom';
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from worker;
+------+-------+--------+--------+
| id   | name  | gender | salary |
+------+-------+--------+--------+
|    2 | jack  | g      |  10000 |
|    3 | rose  | g      |   6000 |
|    4 | bob   | b      |   5000 |
|    5 | hello | g      |   5000 |
|    6 | abc   | b      |   5000 |
+------+-------+--------+--------+
5 rows in set (0.00 sec)
 
#2.删除表中的所有数据
mysql> delete from worker;
Query OK, 5 rows affected (0.01 sec)
 
mysql> select * from worker;
Empty set (0.00 sec)
 
# 删除表中的数据的方法有delete,truncate, 其中TRUNCATE TABLE用于删除表中的所有行，而不记录单个行删除操作。TRUNCATE TABLE 与没有 WHERE 子句的 DELETE 语句类似；但是，TRUNCATE TABLE 速度更快，使用的系统资源和事务日志资源更少
# Truncate是一个能够快速清空资料表内所有资料的SQL语法。并且能针对具有自动递增值的字段，做计数重置归零重新计算的作用。
mysql> truncate table worker;
Query OK, 0 rows affected (0.02 sec)
 
#3.删除表
mysql> drop table worker;
Query OK, 0 rows affected (0.01 sec)
 
mysql> show tables;
Empty set (0.00 sec)
 
注意：
delete:删除表中的指定数据，表结构还在，删除之后的数据可以找回，对自动增加的字段无影响
truncate：清空表中的数据，删除的数据是不能找回的，执行速度比delete快，自动增加的字段会重新计数
drop: 删除表，数据和表结构都删除

4.总结:

刚开始接触数据库时的操作流程:

1.启动mysql数据库服务 net start mysql80(默认已经设置了开机启动,可以忽略不计)
2.连接数据库 mysql -uroot -p密码
3.创建数据库 create database 数据库名字;
4.选择要操作的数据库 use 数据库名字;
5.创建数据表示例: create table 数据表名字(id int(10),name char(20),age int(10));

五、DQL和数据完整性

1.DQL

数据库执行DQL语言不会对数据库中的数据发生任何改变，而是让数据库发送查询结果到客户端

查询返回的结果其实是一张虚拟表

语法：

SELECT 列名 FROM 表名 [WHERE --> GROUP BY -->HAVING--> ORDER BY --->LIMIT]
create table student(id int(10) primary key auto_increment,
    name char(10),age int(10),
    gender enum("female","male"));
 
insert into student(name,age,gender) values("aaaa",19,"female"),("bbbbbb",20,"male"),("cc",20,"male"),("ddd",16,"female"),("eee",20,"female");

1.1基础查询

演示：


#1.查询所有列  * 表示所有的列
mysql> select * from student;
+------+----------+------+--------+
| id   | name     | age  | gender |
+------+----------+------+--------+
| 1    | aaaa     |   19 | female |
| 2    | bbbbbb   |   20 | male   |
| 3    | cc       |   15 | male   |
| 4    | ddd      |   16 | female |
| 5    | eee      |   20 | female |
+------+----------+------+--------+
5 rows in set (0.00 sec)
 
 
#2.查询指定列
mysql> select id,name,gender from student;
+------+----------+--------+
| id   | name     | gender |
+------+----------+--------+
| 1    | aaaa     | female |
| 2    | bbbbbbbb | male   |
| 3    | cc       | male   |
| 4    | ddd      | female |
| 5    | eee      | female |
+------+----------+--------+
5 rows in set (0.00 sec)

1.2条件查询

主要结合where的使用

between...and: 介于..和..之间

and：逻辑与

or：逻辑或

in / not in：类似于Python中的成员运算符

is / is not: 类似于Python中的身份运算符 , 常用语判断null值，如：name is null

演示：


#1.查询性别为女，并且年龄为20的记录
mysql> select * from student where gender='female' and age=20;
+------+------+------+--------+
| id   | name | age  | gender |
+------+------+------+--------+
| 5    | eee  |   20 | female |
+------+------+------+--------+
1 row in set (0.00 sec)
 
#2.查询编号为1或者姓名为ddd的记录
mysql> select * from student where id='1' or name='ddd';
+------+------+------+--------+
| id   | name | age  | gender |
+------+------+------+--------+
| 1    | aaaa |   19 | female |
| 4    | ddd  |   16 | female |
+------+------+------+--------+
2 rows in set (0.00 sec)
 
#3.查询编号分别为1,2,3的记录
mysql> select * from student where id='1' or id='2' or id='3';
+------+----------+------+--------+
| id   | name     | age  | gender |
+------+----------+------+--------+
| 1    | aaaa     |   19 | female |
| 2    | bbbbbbbb |   20 | male   |
| 3    | cc       |   15 | male   |
+------+----------+------+--------+
3 rows in set (0.00 sec)
#简写形式
mysql> select * from student where id in('1','2','3');
+------+----------+------+--------+
| id   | name     | age  | gender |
+------+----------+------+--------+
| 1    | aaaa     |   19 | female |
| 2    | bbbbbbbb |   20 | male   |
| 3    | cc       |   15 | male   |
+------+----------+------+--------+
3 rows in set (0.00 sec)
 
#4.查询编号不为1,2,3的记录
mysql> select * from student where id not in('1','2','3');
+------+------+------+--------+
| id   | name | age  | gender |
+------+------+------+--------+
| 4    | ddd  |   16 | female |
| 5    | eee  |   20 | female |
+------+------+------+--------+
2 rows in set (0.00 sec)
 
 
#5.查询年龄为null的记录
mysql> select * from student where age is null;
Empty set (0.00 sec)
 
#6.查询年龄在15~20之间的记录     
mysql> select * from student where age>=15 and age<=20;
+------+----------+------+--------+
| id   | name     | age  | gender |
+------+----------+------+--------+
| 1    | aaaa     |   19 | female |
| 2    | bbbbbbbb |   20 | male   |
| 3    | cc       |   15 | male   |
| 4    | ddd      |   16 | female |
| 5    | eee      |   20 | female |
+------+----------+------+--------+
5 rows in set (0.00 sec)
#简写形式
mysql> select * from student where age between 15 and 20;
+------+----------+------+--------+
| id   | name     | age  | gender |
+------+----------+------+--------+
| 1    | aaaa     |   19 | female |
| 2    | bbbbbbbb |   20 | male   |
| 3    | cc       |   15 | male   |
| 4    | ddd      |   16 | female |
| 5    | eee      |   20 | female |
+------+----------+------+--------+
5 rows in set (0.00 sec)
 
#7.查询性别非男的记录
#方式一
mysql> select * from student where gender='female';
+------+------+------+--------+
| id   | name | age  | gender |
+------+------+------+--------+
| 1    | aaaa |   19 | female |
| 4    | ddd  |   16 | female |
| 5    | eee  |   20 | female |
+------+------+------+--------+
3 rows in set (0.00 sec)
#方式二
mysql> select * from student where gender!='male';
+------+------+------+--------+
| id   | name | age  | gender |
+------+------+------+--------+
| 1    | aaaa |   19 | female |
| 4    | ddd  |   16 | female |
| 5    | eee  |   20 | female |
+------+------+------+--------+
3 rows in set (0.00 sec)
#方式三
mysql> select * from student where gender<>'male';
+------+------+------+--------+
| id   | name | age  | gender |
+------+------+------+--------+
| 1    | aaaa |   19 | female |
| 4    | ddd  |   16 | female |
| 5    | eee  |   20 | female |
+------+------+------+--------+
3 rows in set (0.00 sec)

1.3模糊查询

where 子句中 = 表示精准查询

like：一般情况下结合where子句使用

通配符：

_ : 匹配任意一个字符

%：匹配0~n个字符【n大于等于1】

演示：


#1.查询姓名由2个字符组成的记录
mysql> select * from student where name like '__';
 
 
#2.查询姓名由3个字符组成的记录，并且最后一个字母为c的记录
mysql> select * from student where name like '__c';
Empty set (0.00 sec)
 
#3.查询以a开头的记录
mysql> select * from student where name like 'a%';
+------+------+------+--------+
| id   | name | age  | gender |
+------+------+------+--------+
| 1    | aaaa |   19 | female |
+------+------+------+--------+
1 row in set (0.01 sec)
 
#4.查询姓名中包含b的记录
mysql> select * from student where name like '%b%';
+------+----------+------+--------+
| id   | name     | age  | gender |
+------+----------+------+--------+
| 2    | bbbbbbbb |   20 | male   |
+------+----------+------+--------+
1 row in set (0.00 sec)
 
#5.查询姓名中第2个字母为c的记录
mysql> select * from student where name like '_c%';
+------+------+------+--------+
| id   | name | age  | gender |
+------+------+------+--------+
| 3    | cc   |   15 | male   |
+------+------+------+--------+
1 row in set (0.00 sec)

1.4字段控制查询

as: 起别名，用法：select 字段 as 别名

distinct: 去除重复记录

演示：


#1.去除重复记录   使用 distinct
mysql> select name from student;
+------+
| id   |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| 1    |
+------+
6 rows in set (0.00 sec)
 
mysql> select distinct id from student;
+------+
| id   |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
+------+
5 rows in set (0.01 sec)
 
#2.给列名起别名
mysql> select name,gender  from student;
+----------+--------+
| name     | gender |
+----------+--------+
| aaaa     | female |
| bbbbbbbb | male   |
| cc       | male   |
| ddd      | female |
| eee      | female |
| ffff     | male   |
+----------+--------+
6 rows in set (0.00 sec)
 
 
mysql> select name as 姓名,gender as 性别  from student;
+----------+--------+
| 姓名     | 性别   |
+----------+--------+
| aaaa     | female |
| bbbbbbbb | male   |
| cc       | male   |
| ddd      | female |
| eee      | female |
| ffff     | male   |
+----------+--------+
6 rows in set (0.00 sec)
 
mysql> select name  姓名1,gender  性别1  from student;
+----------+---------+
| 姓名1    | 性别1   |
+----------+---------+
| aaaa     | female  |
| bbbbbbbb | male    |
| cc       | male    |
| ddd      | female  |
| eee      | female  |
| ffff     | male    |
+----------+---------+
6 rows in set (0.00 sec)

1.5排序

order by: 指定数据返回的顺序

asc：ascending,升序(默认)

desc: descending，降序

用法：select from 表 order by xxx

演示：


#1.查询所有的记录，按照年龄升序排序
mysql> select * from student order by age asc;
+------+----------+------+--------+
| id   | name     | age  | gender |
+------+----------+------+--------+
| 3    | cc       |   15 | male   |
| 4    | ddd      |   16 | female |
| 1    | aaaa     |   19 | female |
| 2    | bbbbbbbb |   20 | male   |
| 5    | eee      |   20 | female |
| 1    | ffff     |   30 | male   |
+------+----------+------+--------+
6 rows in set (0.00 sec)
 
#2.查询所有学生记录，按照年龄降序排序，如果年龄相等，则按照编号进行升序排序 
mysql> select * from student order by age desc,id asc; 
+------+----------+------+--------+ 
| id   | name     | age  | gender | 
+------+----------+------+--------+ 
| 1    | ffff     |   30 | male   | 
| 2    | bbbbbbbb |   20 | male   | 
| 5    | eee      |   20 | female | 
| 1    | aaaa     |   19 | female | 
| 4    | ddd      |   16 | female | 
| 3    | cc       |   15 | male   | 
+------+----------+------+--------+ 
6 rows in set (0.00 sec)

1.6聚合函数

聚合函数主要用来做纵向运算

count(): 统计指定列不为null的记录行数


#1.查询年龄大于20的人数
mysql> select count(*)  from student where age>20;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

sum():计算指定列的数值和


#1.查询所有学生的年龄和
mysql> select sum(age) from student;
+----------+
| sum(age) |
+----------+
|      120 |
+----------+
1 row in set (0.01 sec)
 
#2.查询所有学生的年龄和，以及所有学生的编号和
mysql> select sum(age),sum(id) from student;
+----------+---------+
| sum(age) | sum(id) |
+----------+---------+
|      120 |      16 |
+----------+---------+
1 row in set (0.00 sec)

求指定列中的最大值和最小值

max():

min():


#求最大年龄和最小年龄
mysql> select max(age),min(age) from student;
+----------+----------+
| max(age) | min(age) |
+----------+----------+
|       30 |       15 |
+----------+----------+
1 row in set (0.00 sec)

avg()

average:平均数


#查询所有学生的平均年龄
mysql> select avg(age) from student;
+----------+
| avg(age) |
+----------+
|  20.0000 |
+----------+
1 row in set (0.00 sec)

总结：

查询关键字的书写顺序：select 聚合函数 from where order by

1.7分组查询

group by：分组查询

having：有...,表示条件，类似于where的用法

注意: 如果要对分组后的数据进行筛选, 这时候使用having,不使用where.

演示：


#在当前数据库下创建新的表
 
# 员工表
create table emp(empno int primary key, enname varchar(20), job varchar(20), mgr int, hiredate date, sal double, comm double, deptno int);
 
# deptno表示部门编号
# sal  表示员工工资
 
# 添加数据
insert into emp values(7369,'smith','clark',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'allen','salesman',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'jones','managen',7839,'1981-04-02',2975,null,30);
insert into emp values(7654,'martin','salesman',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'blake','manager',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'clark','manageer',7839,'1980-06-17',2450,null,10);
insert into emp values(7788,'scott','analyst',7566,'1987-02-20',3000,null,20);
insert into emp values(7839,'king','president',null,'1987-02-20',5000,null,10);
 
#1.查询各个部门的人数
mysql> select count(*) from emp group by deptno;
+----------+
| count(*) |
+----------+
|        2 |
|        2 |
|        4 |
+----------+
3 rows in set (0.00 sec)
 
#2.查询每个部门的部门编号和每个部门的工资和
mysql> select deptno,sum(sal) from emp group by deptno;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
|     10 |  7450.00 |
|     20 |  3800.00 |
|     30 |  8675.00 |
+--------+----------+
3 rows in set (0.00 sec)
 
 
#3.查询每个部门的部门编号和每个部门的人数
mysql> select deptno,count(*) from emp group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        2 |
|     20 |        2 |
|     30 |        4 |
+--------+----------+
3 rows in set (0.00 sec)
 
 
#4.查询每个部门的部门编号和每个部门工资大于1500的人数
mysql> select deptno,count(*) from emp where sal>1500 group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        2 |
|     20 |        1 |
|     30 |        3 |
+--------+----------+
3 rows in set (0.01 sec)
 
 
#5.查询工资总和大于7000的部门编号以及工资和
mysql> select deptno,sum(sal) from emp group by deptno having sum(sal)>7000;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
|     10 |  7450.00 |
|     30 |  8675.00 |
+--------+----------+
2 rows in set (0.00 sec)

总结：

having和where的区别

a.二者都表示对数据执行条件

b. having是在分组之后对分组进行过滤

where是在分组之前对数据进行过滤

c. having后面可以使用聚合函数

where后面不可以使用聚合函数

演示：


#查询工资大于1500，工资总和大于6000的部门编号和工资和
mysql> select deptno,sum(sal) from emp where sal>1500 group by deptno having sum(sal)>6000;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
|     10 |  7450.00 |
|     30 |  7425.00 |
+--------+----------+
2 rows in set (0.00 sec)

1.8分页查询

limit：用来限定查询的起始行，以及总行数

演示：


#1.查询4行记录，起始行从0开始
mysql> select * from emp limit 0,4;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | enname | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7369 | smith  | clark    | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | allen  | salesman | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7566 | jones  | managen  | 7839 | 1981-04-02 | 2975.00 |    NULL |     30 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
4 rows in set (0.00 sec)
 
#2.查询3行记录，起始行从第2行开始，不包含第2行
mysql> select * from emp limit 2,3;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | enname | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7566 | jones  | managen  | 7839 | 1981-04-02 | 2975.00 |    NULL |     30 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | blake  | manager  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
3 rows in set (0.01 sec)

总结：

mysql> select deptno,sum(sal) from emp where sal>1500 group by deptno having sum(sal)>5000 order by sum(sal) asc limit 4;

查询语句书写顺序：select----》from---》where---》group by-----》having-----》order by----->limit

查询语句执行顺序：from----》where-----》group by----》having----》order by ----》select-----》limit

扩展:

case表达式: 类似于python中的 if elif ... else语句


CASE  
	WHEN  condition1(条件) THEN result1
	WHEN  condition2(条件) THEN result2
	WHEN  condition3(条件) THEN result3
	ELSE  result
END
 
 condition 是一个返回boolean 的表达式。 如果结果为真，那么 CASE 表达式的结果就是符合条件的 result。 如果结果为假，那么以相同方式搜寻任何随后的 WHEN 子句。 如果没有 WHEN condition 为真，那么 case 表达式的结果就是在 ELSE 子句里的值。 如果省略了 ELSE 子句而且没有匹配的条件， 结果为 NULL。
 
简单的CASE函数
CASE sex 
    WHEN 'male' THEN '男' 
    WHEN 'female' THEN '女' 
ELSE '其他' END 
-- 查询学员表的性别,当性别为male时,显示男, 当性别为female时,显示女
select name, case 
		when gender = "male" then '男'
		when gender = "female" then '女'
		else '其他' 
		end gender from student;

2.数据的完整性

作用：保证用户输入的数据保存到数据库中是正确的

实质：创建表的时候给表中的字段添加约束

2.1实体完整性

实体：表中的一行或者一条记录代表一个实体

实体完整性的作用：标识每一行数据不重复

约束类型：

主键约束【primary key】

唯一约束【unique】

自动增长列【auto_increment】

2.1.1主键约束(primary key)

特点：数据唯一，且不能为null

主关键字可以是表中的一个字段或者多个字段，它的值用来唯一标识表中的某一条记录

场景：在多个表的关联关系中

演示：
mysql> create table stu1(
 -> 		id int primary key,
 -> 		name varchar(50) 
 -> );
 
mysql> create table stu2(
	-> 		id int ,
 -> 		name varchar(50),
 -> 		primary key(id，name)
 -> );
 
mysql> create table stu3(
	-> 		id int,
	-> 		name varchar(50)
 -> );
mysql> alter table stu3 add constraint stu3_id primary key(id);
 

2.1.2唯一约束(unique)

作用：在非主键列中不能输入重复的值

演示：


mysql> create table stu4(
 -> 		id int primary key,
 -> 		name varchar(50) unique
 -> );
 
 
# primary key和unique之间的区别
a.二者都强调的是唯一性
b.在同一个表中，一般只出现一个primary key，可以出现多个unique
c.primary key不允许为null，但是unique是允许的

2.1.3自动增长列

给主键添加添加自动增长性，列只能是整数类型

场景：一般添加给主键

演示：
mysql> create table stu5(
 -> id int primary key auto_increment,
 -> name varchar(50) unique
 -> );
 

2.2域完整性

作用：限制单元格数据的正确性，

域代表当前单元格

约束类型：

数据类型

非空约束【not null】

默认值约束【default】

2.2.1数据类型

数字类型：int float double

日期类型：date datetime

字符串类型：varchar(20)

2.2.2非空约束


mysql> create table stu6( 
 	id int primary key auto_increment, 
 	name varchar(50) unique not null,
 );
 
#注意：name被约束为not null，插入数据的时候，name坚决不能为null，如果为null，数据库立马报错

2.2.3默认值约束


mysql> create table stu7(
 -> id int primary key auto_increment,
 -> name varchar(50) unique not null,
 -> address varchar(50) default "beijing"
 -> );
 
mysql> insert into stu7 (id,name,address) values(1,'aaa','fff');
mysql> insert into stu7 (id,name,address) values(2,'bbb',default);
 
mysql> select * from stu7;
+----+------+---------+
| id | name | address |
+----+------+---------+
|  1 | aaa  | fff     |
|  2 | bbb  | beijing |
+----+------+---------+

2.3外键约束

添加外键约束：foreign key

注意：添加外键必须先有主键，主键和外键的类型必须保持一致

举例：用户表，商品表

作用：将两个甚至多个表产生联系,为了维护数据的参考完整性, 可以更方便的查看E-R图

演示：


#创建表
#用户表
create table user(
	uid int(10) primary key,
	uname varchar(50)
);
 
#商品表
create table goods(
 uid int(10),
 gname char(10),
 price int(10)
);
 
#插入数据
insert into user values(1001,'zhangsan');
insert into user values(1002,'xiaoming');
insert into user values(1003,'jack');
insert into user values(1004,'tom');
 
insert into goods values(1001,'奔驰G63',1999999);
insert into goods values(1002,'苹果13',8787);
insert into goods values(1003,'宝马z4',977898);
insert into goods values(1004,'香奈儿',20323);
insert into goods values(1004,'爱马仕',32323);
insert into goods values(1002,'卡地亚手镯',322323);
 
#查询
mysql> select * from user;
 
mysql> select * from goods;
 
 
添加外键约束
#方式一
mysql> create table goods1( score int, courseid int,stuid int(10), constraint user_id foreign key(uid) references user(uid) );
#注意：user_id是给约束起的名字，可以自定义
 
CREATE TABLE `goods1` (
`uid` int DEFAULT NULL,
`ganme` varchar(20) DEFAULT NULL,
`price` int DEFAULT NULL,
KEY `user_id` (`uid`),
CONSTRAINT `user_id` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
 
#方式二
mysql> alter table goods add constraint user_id2 foreign key(uid) references user(uid);
 
#注意：主键和外键的类型必须保持一致
 
user就是主表    goods就是从表  
如果要删除数据,需要先删除从表的数据,然后才能删除主表对应的数据.

六、MySQL多表联查

1.多表查询

1.1表与表之间的关系

一对一

通过嵌套的方式

一对多【多对一】

添加外键

多对多

单独创建一张新的表

1.2合并结果集

作用：将两个select语句的查询结果合并到一起

两种方式：

union：去除重复记录【并集】记录的所有字段信息全部一致才叫重复记录

union all：获取所有的结果

演示：


#创建表
mysql> create table A(
 	 name varchar(10),
	 	 score int
   );
 
mysql> create table B( 
		name varchar(10), 
		score int 
	  );
 
#批量插入数据
mysql> insert into A values('a',10),('b',20),('c',30);
mysql> insert into B values('a',10),('d',40),('c',30);
 
#查询结果
mysql> select * from A;
+------+-------+
| name | score |
+------+-------+
| a    |    10 |
| b    |    20 |
| c    |    30 |
+------+-------+
 
mysql> select * from B;
+------+-------+
| name | score |
+------+-------+
| a    |    10 |
| d    |    40 |
| c    |    30 |
+------+-------+
 
#合并结果集
mysql> select * from A
-> union
-> select * from B;
+------+-------+
| name | score |
+------+-------+
| a    |    10 |
| b    |    20 |
| c    |    30 |
| d    |    40 |
+------+-------+
 
mysql> select * from A
-> union all
-> select * from B;
+------+-------+
| name | score |
+------+-------+
| a    |    10 |
| b    |    20 |
| c    |    30 |
| a    |    10 |
| d    |    40 |
| c    |    30 |
+------+-------+

注意：被合并的两个结果，列数、列类型必须相同

如果遇到列数不相同的情况，如下的解决办法：


mysql> insert into C values('a',10,29),('e',20,45),('c',30,10);
 
mysql> select * from A
-> union 
-> select name,score from C;
+------+-------+
| name | score |
+------+-------+
| a    |    10 |
| b    |    20 |
| c    |    30 |
| e    |    20 |
+------+-------+

1.3链接查询

作用：求出多个表的乘积，例如t1和t2，如果采用了连接查询，得到的结果是t1*t2

演示：

mysql> select * from student,score;
+-------+----------+-------+-------+----------+
| stuid | stuname | stuid | score | courseid |
+-------+----------+-------+-------+----------+
| 1001 | zhangsan | 1001 | 98 | 1 |
| 1002 | lisi | 1001 | 98 | 1 |
| 1003 | jack | 1001 | 98 | 1 |
| 1004 | tom | 1001 | 98 | 1 |
| 1001 | zhangsan | 1002 | 80 | 2 |
| 1002 | lisi | 1002 | 80 | 2 |
| 1003 | jack | 1002 | 80 | 2 |
| 1004 | tom | 1002 | 80 | 2 |
| 1001 | zhangsan | 1003 | 70 | 1 |
| 1002 | lisi | 1003 | 70 | 1 |
| 1003 | jack | 1003 | 70 | 1 |
| 1004 | tom | 1003 | 70 | 1 |
| 1001 | zhangsan | 1004 | 60 | 2 |
| 1002 | lisi | 1004 | 60 | 2 |
| 1003 | jack | 1004 | 60 | 2 |
| 1004 | tom | 1004 | 60 | 2 |
| 1001 | zhangsan | 1002 | 75 | 3 |
| 1002 | lisi | 1002 | 75 | 3 |
| 1003 | jack | 1002 | 75 | 3 |
| 1004 | tom | 1002 | 75 | 3 |
+-------+----------+-------+-------+----------+
20 rows in set (0.01 sec)

#问题：进行连接查询，会产生笛卡尔积
#笛卡尔积：两个集合相乘的结果
#解释：假设集合A={a,b},集合B={0,1,2},则笛卡尔积的结果{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}

#解决办法：在实际应用中，需要去除重复记录，则需要通过条件进行过滤
mysql> select stu.sname,stu.class,stu.sno,score.degree from student as stu,score
where stu.sno = score.sno;
sname class sno degree
陆君       95031       103       92
陆君      95031       103       86
陆君       95031       103       85
匡明       95031       105       88
匡明       95031       105       75
匡明       95031       105       79
王芳       95031       109       78
王芳       95031       109       68
王芳       95031       109       81

1.3.1内链接inner join ... on

inner join 表示内连接. inner可以省略不写.

on后面跟的是连接条件

内连接的特点：查询结果必须满足条件

演示：


#内连接
mysql> select stu.sname,stu.class,stu.sno,score.degree from student as stu inner join score on stu.sno = score.sno;
+-------+-------+-----+--------+
| sname | class | sno | degree |
+-------+-------+-----+--------+
| 陆君  | 95031 | 103 |     92 |
| 陆君  | 95031 | 103 |     86 |
| 陆君  | 95031 | 103 |     85 |
| 匡明  | 95031 | 105 |     88 |
| 匡明  | 95031 | 105 |     75 |
| 匡明  | 95031 | 105 |     79 |
| 王芳  | 95031 | 109 |     78 |
| 王芳  | 95031 | 109 |     68 |
| 王芳  | 95031 | 109 |     81 |
+-------+-------+-----+--------+
 
#等价写法
mysql> select stu.sname,stu.class,stu.sno,score.degree from student as stu,score where stu.sno = score.sno;
+-------+-------+-----+--------+
| sname | class | sno | degree |
+-------+-------+-----+--------+
| 陆君  | 95031 | 103 |     92 |
| 陆君  | 95031 | 103 |     86 |
| 陆君  | 95031 | 103 |     85 |
| 匡明  | 95031 | 105 |     88 |
| 匡明  | 95031 | 105 |     75 |
| 匡明  | 95031 | 105 |     79 |
| 王芳  | 95031 | 109 |     78 |
| 王芳  | 95031 | 109 |     68 |
| 王芳  | 95031 | 109 |     81 |
+-------+-------+-----+--------+
 
 
#练习：-- 查询成绩大于80分的学生的记录(学生的姓名\班级号\学号等)
-- 方式1:
-- select stu.sname,stu.sno,stu.class,score.degree from student as stu,score where stu.sno = score.sno and score.degree > 80;
+-------+-----+-------+--------+
| sname | sno | class | degree |
+-------+-----+-------+--------+
| 陆君  | 103 | 95031 |     92 |
| 陆君  | 103 | 95031 |     86 |
| 陆君  | 103 | 95031 |     85 |
| 匡明  | 105 | 95031 |     88 |
| 王芳  | 109 | 95031 |     81 |
+-------+-----+-------+--------+
 
-- 方式2:
select stu.sname,stu.sno,stu.class,score.degree from student as stu inner join score on stu.sno = score.sno and score.degree > 80;
+-------+-----+-------+--------+
| sname | sno | class | degree |
+-------+-----+-------+--------+
| 陆君  | 103 | 95031 |     92 |
| 陆君  | 103 | 95031 |     86 |
| 陆君  | 103 | 95031 |     85 |
| 匡明  | 105 | 95031 |     88 |
| 王芳  | 109 | 95031 |     81 |
+-------+-----+-------+--------+

1.3.2外链接 outer join ... on

outer join 表示外连接

on 后面跟的是连接的条件

特点：以其中一个表作为参照连接另外一个表

分类：

左外连接：left join on 以左表为主,会把左表的数据全部查询出来, 会把符合条件的右表数据查询出来

右外连接：right join on 以右表为主,会把右表的数据全部查询出来, 会把符合条件的左表数据查询出来

演示：


-- -- 部门表
create table dept(
	deptno int primary key auto_increment, -- 部门编号
	dname varchar(14) ,	  -- 部门名字
	loc varchar(13)   -- 地址
) ;
-- -- 员工表
create table emp(
  empno int primary key auto_increment,-- 员工编号
  ename varchar(10), -- 员工姓名										-
  job varchar(9),	-- 岗位
  mgr int,	 -- 直接领导编号
  hiredate date, -- 雇佣日期，入职日期
  sal int, -- 薪水/工资
  comm int,  -- 提成
  deptno int, -- 部门编号
  foreign key (deptno) references dept(deptno));
 
insert into dept values(10,'财务部','北京');
insert into dept values(20,'研发部','上海');
insert into dept values(30,'销售部','广州');
insert into dept values(40,'行政部','深圳');
insert into dept values(50,'法务部','北京');
insert into dept values(60,'部','上海');
insert into dept values(70,'销售部','广州');
insert into dept values(80,'行政部','深圳');
insert into emp values(7369,'刘一','职员',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'陈二','推销员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'李四','经理',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'周八','分析师',7566,'1987-06-13',3000,null,20);
insert into emp values(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'郑十','推销员',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'郭十一','职员',7788,'1987-06-13',1100,null,20);
insert into emp values(7900,'钱多多','职员',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'大锦鲤','分析师',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'木有钱','职员',7782,'1983-01-23',1300,null,10);
 
 
#左外连接
mysql> select emp.empno,emp.ename,emp.job,emp.deptno,dept.dname from emp left join dept on emp.deptno = dept.deptno;
+-------+-----------+-----------+--------+-----------+
| empno | ename     | job       | deptno | dname     |
+-------+-----------+-----------+--------+-----------+
|  7369 | 刘一      | 职员      |     20 | 研发部    |
|  7499 | 陈二      | 推销员    |     30 | 销售部    |
|  7521 | 张三      | 推销员    |     30 | 销售部    |
|  7566 | 李四      | 经理      |     20 | 研发部    |
|  7654 | 王五      | 推销员    |     30 | 销售部    |
|  7698 | 赵六      | 经理      |     30 | 销售部    |
|  7782 | 孙七      | 经理      |     10 | 财务部    |
|  7788 | 周八      | 分析师    |     20 | 研发部    |
|  7839 | 吴九      | 总裁      |     10 | 财务部    |
|  7844 | 郑十      | 推销员    |     30 | 销售部    |
|  7876 | 郭十一    | 职员      |     20 | 研发部    |
|  7900 | 钱多多    | 职员      |     30 | 销售部    |
|  7902 | 大锦鲤    | 分析师    |     20 | 研发部    |
|  7934 | 木有钱    | 职员      |     10 | 财务部    |
+-------+-----------+-----------+--------+-----------+
 
 
#右外连接
mysql> select emp.empno,emp.ename,emp.job,emp.deptno,dept.dname from emp right join dept on emp.deptno = dept.deptno;
+-------+-----------+-----------+--------+-----------+
| empno | ename     | job       | deptno | dname     |
+-------+-----------+-----------+--------+-----------+
|  7782 | 孙七      | 经理      |     10 | 财务部    |
|  7839 | 吴九      | 总裁      |     10 | 财务部    |
|  7934 | 木有钱    | 职员      |     10 | 财务部    |
|  7369 | 刘一      | 职员      |     20 | 研发部    |
|  7566 | 李四      | 经理      |     20 | 研发部    |
|  7788 | 周八      | 分析师    |     20 | 研发部    |
|  7876 | 郭十一    | 职员      |     20 | 研发部    |
|  7902 | 大锦鲤    | 分析师    |     20 | 研发部    |
|  7499 | 陈二      | 推销员    |     30 | 销售部    |
|  7521 | 张三      | 推销员    |     30 | 销售部    |
|  7654 | 王五      | 推销员    |     30 | 销售部    |
|  7698 | 赵六      | 经理      |     30 | 销售部    |
|  7844 | 郑十      | 推销员    |     30 | 销售部    |
|  7900 | 钱多多    | 职员      |     30 | 销售部    |
|  NULL | NULL      | NULL      |   NULL | 行政部    |
|  NULL | NULL      | NULL      |   NULL | 法务部    |
|  NULL | NULL      | NULL      |   NULL | 部        |
|  NULL | NULL      | NULL      |   NULL | 销售部    |
|  NULL | NULL      | NULL      |   NULL | 行政部    |
+-------+-----------+-----------+--------+-----------+

1.4子查询

在一个select语句中包含另外一个完整的select语句【select语句的嵌套】

注意：

a.子查询出现的位置：

from后

where子句的后面，作为条件的一部分被查询

b。当子查询出现在where后面作为条件时，可以使用关键字：any、all

c.子查询结果集的形式

单行单列

单行多列

多行多列

多行单列

演示：


#1.查询和刘一在同一个部门的员工
#思路：先查询刘一所在的部门，然后根据部门查找所有的信息
mysql> select deptno from emp where enname='刘一';
+--------+
| deptno |
+--------+
|     20 |
+--------+
 
mysql> select * from emp where deptno=(select deptno from emp where ename='刘一') and ename != '刘一';
+-------+--------+--------+------+------------+------+------+--------+
| empno | ename  | job    | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+--------+------+------------+------+------+--------+
|  7566 | 李四   | 经理   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7788 | 周八   | 分析师 | 7566 | 1987-06-13 | 3000 | NULL |     20 |
|  7876 | 郭十一 | 职员   | 7788 | 1987-06-13 | 1100 | NULL |     20 |
|  7902 | 大锦鲤 | 分析师 | 7566 | 1981-12-03 | 3000 | NULL |     20 |
+-------+--------+--------+------+------------+------+------+--------+
 
#2.查询工资高于王五的员工信息
#思路：先查询王五的工资，然后根据结果查询其他的员工信息
mysql> select ename,sal from emp where sal > (select sal from emp where ename = "王五");
 
+--------+------+
| ename  | sal  |
+--------+------+
| 陈二   | 1600 |
| 李四   | 2975 |
| 赵六   | 2850 |
| 孙七   | 2450 |
| 周八   | 3000 |
| 吴九   | 5000 |
| 郑十   | 1500 |
| 大锦鲤 | 3000 |
| 木有钱 | 1300 |
+--------+------+
 
#3.查询工资高于30号部门所有人的员工信息
#思路：先查询30号部门中的最高工资，根据最高工资查询其他的员工信息
mysql> select * from emp where deptno=30;
+-------+--------+--------+------+------------+------+------+--------+
| empno | ename  | job    | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+--------+------+------------+------+------+--------+
|  7499 | 陈二   | 推销员 | 7698 | 1981-02-20 | 1600 |  300 |     30 |
|  7521 | 张三   | 推销员 | 7698 | 1981-02-22 | 1250 |  500 |     30 |
|  7654 | 王五   | 推销员 | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
|  7698 | 赵六   | 经理   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7844 | 郑十   | 推销员 | 7698 | 1981-09-08 | 1500 |    0 |     30 |
|  7900 | 钱多多 | 职员   | 7698 | 1981-12-03 |  950 | NULL |     30 |
+-------+--------+--------+------+------------+------+------+--------+
 
mysql> select max(sal) from emp where deptno=30;
+----------+
| max(sal) |
+----------+
|     2850 |
+----------+
 
mysql> select * from emp where sal>(select max(sal) from emp where deptno=30);
+-------+--------+--------+------+------------+------+------+--------+
| empno | ename  | job    | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+--------+------+------------+------+------+--------+
|  7566 | 李四   | 经理   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7788 | 周八   | 分析师 | 7566 | 1987-06-13 | 3000 | NULL |     20 |
|  7839 | 吴九   | 总裁   | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7902 | 大锦鲤 | 分析师 | 7566 | 1981-12-03 | 3000 | NULL |     20 |
+-------+--------+--------+------+------------+------+------+--------+
 
#4.查询工作类型和工资与张三完全相同的员工信息
#思路：先查询张三的工作类型和工资，然后再查询其他的员工信息
mysql> select * from emp where (job,sal) in (select job,sal from emp where ename='张三');
 
+-------+-------+--------+------+------------+------+------+--------+
| empno | ename | job    | mgr  | hiredate   | sal  | comm | deptno |
+-------+-------+--------+------+------------+------+------+--------+
|  7521 | 张三  | 推销员 | 7698 | 1981-02-22 | 1250 |  500 |     30 |
|  7654 | 王五  | 推销员 | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
+-------+-------+--------+------+------------+------+------+--------+

在一个user表中，有id，name，age字段，一个day表，有id，time，date字段，要把两个表连接起来，并且查询其中name和time都不相同的数据

select * from( (select * from tableA as A group by 'name') join (select * from tableB as B group by 'time') on A.id = B.id)


select title,  maxt.max_s-(Domestic_sales+International_sales)
from(SELECT *
FROM  Movies m
left join Boxoffice b on m.Id = b.Movie_id)as mb
cross join
(SELECT Domestic_sales+International_sales as max_s
FROM  Movies m
left join Boxoffice b on m.Id = b.Movie_id
order by Domestic_sales+International_sales DESC
limit 1)as maxt

2.数据库的备份和恢复

2.1备份

生成SQL脚本，导出数据

命令：mysqldump -uroot -p 数据库名>生成sql脚本的路径

注意：

可以不需要登录数据库

windows系统: 使用管理员权限打开CMD

演示：

C:\Users\> mysqldump -u root -p demo2105 > C:/Users/Desktop/school.sql
Enter password:

2.2恢复

执行sql脚本，恢复数据

前提：必须先创建数据库【空的】

注意：需要先登录数据库，然后进入指定的数据库，执行sql脚本

演示：

C:\Users> mysql -u root -p
Enter password:

mysql> create database test;

mysql> use test;
mysql> show tables;
mysql> source C:/Users/Desktop/school.sql;

3.索引

3.1索引介绍

索引看着挺高大上的一个名字，说白了就是我们一本书最前面的目录。

假如你用新华字典来查找“张”这个汉字，不使用目录的话，你可能要从新华字典的第一页找到最后一页，可能要花二个小时。字典越厚呢，你花的时间就越多。现在你使用目录来查找“张”这个汉字，张的首字母是z，z开头的汉字从900多页开始，有了这条线索，你查找一个汉字可能只要一分钟，由此可见索引的重要性。
select * from student where name="张";

索引用于快速找出在某个列中有一特定值的行。

不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。

当然索引也不易过多，索引越多写入，修改的速度越慢。因为，写入修改数据时，也要修改索引。

MySQL中，所有数据类型的列都可以被索引，常用的存储引擎InnoDB和MyISAM能支持每个表创建16个索引。InnoDB和MyISAM使用的索引其底层算法是B-tree（B树），B-tree是一种自平衡的树，类似于平衡二叉排序树，能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的操作都在对数时间内完成。

3.2索引分类

索引类型	功能说明
普通索引	最基本的索引，它没有任何限制
唯一索引	某一列启用了唯一索引则不准许这一列的行数据中有重复的值。针对这一列的每一行数据都要求是唯一的 unique
主键索引	它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引，常用于用户ID。类似于书中的页码 primary key
全文索引	对于需要全局搜索的数据，进行全文索

3.3查看索引

基本语法：
   show index from tablename;
示例：
   show index from user\G;
示例说明：
   查看user表的索引结构

3.4普通索引

基本语法：
   alter table 表 add index(字段)
示例：
   alter table money add index(username);
示例说明：
   为money表的username字段增加索引

3.5唯一索引

基本语法：
   alter table 表 add unique(字段)
示例：
   alter table money add unique(email);
示例说明：
   为money表的email字段增加唯一索引

3.6主键索引

基本语法：
   alter table 表 add primary key(字段)
示例：
   alter table money add primary key(id);
示例说明：
   为money表的id字段增加主键索引

3.7创建表时声明索引

创建表时可在创建表语句后加上对应的类型即可声明索引：

primary key(字段)
index (字段)
unique (字段)

create table user (
   id int auto_increment,
   name varchar(20),
   primary key(id),
   unique (name)
);

3.8删除索引

基本语法：
   ALTER TABLE table_name DROP INDEX index_name
示例：
   alter table money drop index age;
示例说明：
   为money表删除age索引

4.事物

4.1事务控制语言(DTL)

我们每执行一条SQL语句,每执行一组SQL语句,我们都可以称为事务
如果一组SQL语句里,某一个SQL语句失败了,称为整个事务的失败,因此出现这种情况必须要恢复到正常的情况上才能没有问题.
在逛淘宝的时候，购买了某一个货品钱付了,但淘宝服务器刚好断电了,商家没办法收到你的钱,就不发货。担保交易银行中转帐的时候, 款已经扣除了,但突然断电,导致对方收不到钱.

如果有了事务，就可以避免该事情。
事务可以看作是一个“容器”，将多条语句，放入该容器，最后，只要一个命令行，来决定其中的所有语句是否“执行”

4.2事务的四大特征

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
   事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
   事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：
       原子性（Atomicity）事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行
       一致性（Consistency）事务应确保数据库的状态从一个一致状态转变为另一个一致状态
       隔离性（Isolation）多个事务并发执行时，一个事务的执行不应影响其他事务的执行
       持久性（Durability）已被提交的事务对数据库的修改应该永久保存在数据库中

4.3事务的原理

传统情况：

事物的机制：

4.4事物模式

在mysql中默认一条sql语句一个事务
因此,如果需要开启事务模式的话
使用 start transaction开头,开启事务模式
使用 commit 语句执行后,才能真正生效
使用 rollback 语句进行回滚

查看数据表的存储引擎。
show table status like "数据表名";
sa
查看mysql 的自动提交模式：
select @@autocommit; 值为1表示开启自动提交。值为0表示禁止自动提交

设置mysql 数据库的提交模式
set @@autocommit = 0; 禁止自动提交
set @@autocommit = 0; s开启自动提交

4.5事物的流程

执行语句	说明
start transaction	执行开始事务命令后,下面将进入事务模式.
update、delete、insert 操作	在事务执行后,这些操作只在内存状态下进行,而不是在物理状态下
commit	在事务执行完成后,确认执行无误且成功,就可以使用commit把内存中执行的结果,提交到物理内存中
rollback	如果在事务执行过程中,发生了错误,则可以使用rollback命令回滚到上一个事务操作

4.6事物的使用

七、mysql窗口函数

sql语句：


CREATE TABLE `student`  (
  `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(0) NULL DEFAULT NULL,
  `sex` enum('男','女') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `score` int(0) NULL DEFAULT NULL,
  `class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `sex`(`sex`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 
INSERT INTO `student` VALUES (1, '天偿好', 28, '男', 45, '1班');
INSERT INTO `student` VALUES (2, '奇奇坏', 20, '女', 85, '2班');
INSERT INTO `student` VALUES (3, '小菲美', 18, '女', 70, '1班');
INSERT INTO `student` VALUES (4, '渣渣辉', NULL, '男', 82, '3班');
INSERT INTO `student` VALUES (5, '涛涛壮', 21, '男', 70, '1班');
INSERT INTO `student` VALUES (6, 'aaaaa', 23, '男', 85, '2班');
INSERT INTO `student` VALUES (7, 'bbb', 34, '女', 74, '3班');
INSERT INTO `student` VALUES (8, 'aacc', 32, '男', 79, '1班');
INSERT INTO `student` VALUES (9, 'cc', 31, '男', 70, '2班');
 
 
CREATE TABLE `order1`  (
  `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_num` int(0) UNSIGNED NOT NULL,
  `amount` float UNSIGNED NULL DEFAULT NULL,
  `dt` date NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 
 
INSERT INTO `order1` VALUES (1, 1, 100, '2021-05-31');
INSERT INTO `order1` VALUES (2, 1, 210, '2021-05-13');
INSERT INTO `order1` VALUES (3, 2, 231, '2021-06-11');
INSERT INTO `order1` VALUES (4, 2, 543, '2021-07-11');
INSERT INTO `order1` VALUES (5, 2, 325, '2021-07-30');
INSERT INTO `order1` VALUES (6, 3, 212, '2021-07-21');
INSERT INTO `order1` VALUES (7, 3, 323, '2021-06-11');
INSERT INTO `order1` VALUES (8, 3, 333, '2021-08-02');
INSERT INTO `order1` VALUES (9, 3, 110, '2021-07-28');
INSERT INTO `order1` VALUES (10, 3, 90, '2021-08-03');

1.窗口函数有什么用？

在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

        排名问题：每个部门按业绩来排名
        topN问题：找出每个部门排名前N的员工进行奖励

        面对这类需求，就需要使用sql的高级功能窗口函数了。

2.什么是窗口函数？

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

窗口函数的基本语法如下：
<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)
<窗口函数>的位置，可以放以下两种函数：

1）专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2）聚合函数，如sum,avg, count, max, min等

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

3.主要分5类

用法：窗口函数 over(partition by 分组字段 order by 排序字段)

3.1序号函数

rank(): 返回数据集中每个值的排名。排名值是根据当前行之前的行数加1，不包含当前行。因此，排序的关联值可能产生顺序上的空隙。这个排名会对每个窗口分区进行计算；

dense_rank() : 返回一组数值中每个数值的排名。这个函数与 rank() ，相似，除了关联值不会产生顺序上的空隙；

row_number() : 为每行数据返回一个唯一的顺序的行号，从1开始，根据行在窗口分区内的顺序；

需求:按照班级将学生分组,然后将每个班级的学生按照成绩排名.

例如下图，是班级表中的内容s

如果我们想在每个班级内按成绩排名，得到下面的结果。

实现:
SELECT *,RANK() over ( PARTITION BY class ORDER BY score DESC ) AS ranking FROM student;
我们来解释下这个sql语句里的select子句。rank是排序的函数。要求是“每个班级内按成绩排名”，这句话可以分为两部分：

(1）每个班级内：按班级分组 partition by用来对表分组。在这个例子中，所以我们指定了按“班级”分组（partition by 班级） (2）按成绩排名 order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。

窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？

这是因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。

现在我们说回来，为什么叫“窗口”函数呢？这是因为partition by分组后的结果称为“窗口”，这里的窗口不是我们家里的门窗，而是表示“范围”的意思。

简单来说，窗口函数有以下功能：

同时具有分组和排序的功能.

不减少原表的行数.

专用窗口函数rank, dense_rank, row_number有什么区别呢？

它们的区别我举个例子，你们一下就能看懂：
SELECT *,RANK() over ( PARTITION BY class ORDER BY score DESC ) AS ranking, DENSE_RANK() over ( PARTITION BY class ORDER BY score DESC ) AS dense,ROW_NUMBER() over ( PARTITION BY class ORDER BY score DESC ) AS rownumber FROM student;
从上面的结果可以看出：

rank函数：这个例子中如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。

dense_rank函数：这个例子中如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。

row_number函数：这个例子中不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

最后，需要强调的一点是：在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以。

3.2分布函数

percent_rank() : 返回数据集中每个数据的排名百分比。结果是根据 (r - 1) / (n - 1) 其中 r 是由 rank() 计算的当前行排

名， n 是当前窗口分区内总的行数；(不常用)

cume_dist()：表示当前行及小于当前行在窗口分区总行数中的占比。

需求:按照订单数统计在总订单数中的占比.

SELECT *,percent_rank() over ( PARTITION BY user_num ORDER BY amount DESC ) AS cume, cume_dist() over ( PARTITION BY user_num ORDER BY amount DESC ) AS cume FROM order1;

这个数值是怎么计算到的呢？答案是:(rank-1) / (rows-1)

rank就是我们前面使用rank()函数计算出来的排名，rows就是行数

示例:

user 表和 transaction表


CREATE TABLE `transaction`  (
  `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userid` int(0) NULL DEFAULT NULL,
  `amount` int(0) NULL DEFAULT NULL,
  `paydate` date NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 
INSERT INTO `transaction` VALUES (1, 1, 212, '2021-11-02');
INSERT INTO `transaction` VALUES (2, 2, 324, '2021-11-04');
INSERT INTO `transaction` VALUES (3, 1, 456, '2021-11-05');
INSERT INTO `transaction` VALUES (4, 3, 3476, '2021-11-06');
INSERT INTO `transaction` VALUES (5, 4, 675, '2021-11-06');
INSERT INTO `transaction` VALUES (6, 5, 12, '2021-11-07');
INSERT INTO `transaction` VALUES (7, 2, 543, '2021-11-08');
INSERT INTO `transaction` VALUES (8, 1, 34, '2021-11-08');
INSERT INTO `transaction` VALUES (9, 2, 4562, '2021-11-02');
INSERT INTO `transaction` VALUES (10, 2, 31, '2021-11-02');
INSERT INTO `transaction` VALUES (11, 3, 23, '2021-11-02');
INSERT INTO `transaction` VALUES (12, 3, 321, '2021-11-04');
INSERT INTO `transaction` VALUES (13, 4, 12, '2021-11-04');
INSERT INTO `transaction` VALUES (14, 4, 2345, '2021-11-05');
INSERT INTO `transaction` VALUES (15, 2, 32, '2021-11-05');
INSERT INTO `transaction` VALUES (16, 2, 3334, '2021-11-06');
INSERT INTO `transaction` VALUES (17, 1, 76, '2021-11-06');
INSERT INTO `transaction` VALUES (18, 3, 435, '2021-11-07');
INSERT INTO `transaction` VALUES (19, 4, 7765, '2021-11-07');
INSERT INTO `transaction` VALUES (20, 3, 433, '2021-11-08');
INSERT INTO `transaction` VALUES (21, 4, 325, '2021-11-08');
INSERT INTO `transaction` VALUES (22, 2, 433, '2021-11-08');
 
CREATE TABLE `user`  (
  `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `createtime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 
INSERT INTO `user` VALUES (1, '张三', '深圳', '2021-08-01 12:41:12');
INSERT INTO `user` VALUES (2, '李四', '广州', '2021-06-01 12:41:26');
INSERT INTO `user` VALUES (3, '王文英', '北京', '2021-10-12 12:41:52');
INSERT INTO `user` VALUES (4, '王五', '纽约', '2021-06-17 12:42:04');
INSERT INTO `user` VALUES (5, '赵敏', '台湾', '2021-11-16 12:42:27');

计算每个用户的交易金额占总交易金额的百分比.
# 第一步: 对用户分组计算出每个用户的交易总额 everymoney表示每个用户的交易总额
# 第二步:计算所有的用户的交易总额 sum(a.everymoney)
# 第三步:每个用户总交易金额比上所有用户总交易金额 a.everymoney/(sum(a.everymoney)

SELECT a.name,a.everymoney,sum(a.everymoney) over() AS totalmoney,a.everymoney/(sum(a.everymoney) over()) AS percent FROM(SELECT u.name,sum(t.amount) everymoney FROM user u JOIN transaction t ON u.id=t.userid GROUP BY u.name) a;

统计在同一天内各个用户的消费排名:


SELECT a.NAME,a.paydate,row_number() OVER w AS num FROM 
(SELECT u.NAME,paydate,sum( amount ) AS total FROM USER u JOIN TRANSACTION t ON u.id = t.userid GROUP BY u.NAME,paydate ) a # a表示对前面的小括号内的sql结果起别名
window w AS ( PARTITION BY paydate ORDER BY a.total DESC );  # w表示对窗口函数起别名

3.3前后函数

lag() 向上偏移

lead() 向下偏移

应用场景:

1.取时间间隔为N天的记录
2.计算本次记录与上次记录的差值
3.取某一字段的前N行数据或后N行数据

三个参数: 第一个参数是表达式或者字段, 第二个参数是偏移量第三个参数就是控制赋值.

需求:查询上一个订单距离当前订单的时间间隔。
select *,datediff(dt,last_date) as diff from(
	select user_num,dt,lag(dt,1,dt) over w as last_date
	from order1
	WINDOW w as (order by dt)   给窗口函数起别名
)t;  t起别名
 

3.4头尾函数：first_val()/ last_val()

按时间排序记录每个用户的最早订单和最晚订单，并不是最小金额和最大金额订单。

需求:查询截止到当前订单，按照日期排序第一个订单和最后一个订单的订单金额。
select * from (select *,
	FIRST_VALUE(amount) over w as first_amount,
	LAST_VALUE(amount) over w as last_amount
	from order1
	WINDOW w as (PARTITION BY user_num order by dt)
)t;

3.5其他函数：nth_value() / ntile()

nth_value(expr,n)
用途：返回窗口中第N个expr的值，expr可以是表达式，也可以是列名。

需求:每个用户订单中显示本用户金额排名第二和第三的订单金额。
select * from (select *,
nth_value(amount,2)  over w as second_amount,
nth_value(amount,3)  over w as third_amount
from order1
WINDOW w as (partition by user_num order by amount desc)
) t;
ntile
1.将数据按照某一字段进行分组
2.分组排名,取前N%,注意区分和Top N不同

需求:将每个用户的订单按照订单金额分成3组。
select * from (select 
ntile(3) over w as nf,
id,amount,dt,user_num
from order1
WINDOW w as (partition by user_num order by amount desc)
) t;

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/2023面试高手/article/detail/554008