mysql
一、存取数据的演变史
1、文本文件
使用文本文件存储数据的时候路径不固定数据格式不统一，一旦更换设备就容易出现无法使用的情况。
文件路径不固定:C:\aaa.txt D:\bbb.txt E:\ccc.txt
数据格式不统一:jason|123 jason$123 jason 123
2、软件开发目录规范
规定了数据应该保存在db目录下>>>:路径偏向统一。
db/user.txt
db/userinfo.txt
db/jason.json
db/jason（比如使用pocket模块存储对象）
但是数据格式还是没有得到统一，有文本、json格式、对象等数据存储格式。
3、数据库服务(重点)
特点
统一路径，统一操作方式
降低学习成本，提高开发效率
1.数据库管理软件的由来
基于我们之前所学，数据要想永久保存，都是保存于文件中，毫无疑问，一个文件仅仅只能存在于某一台机器上。
如果我们暂且忽略直接基于文件来存取数据的效率问题，并且假设程序所有的组件都运行在一台机器上，那么用文件存取数据，并没有问题。
很不幸，这些假设都是你自己意淫出来的，上述假设存在以下几个问题。。。。。。
①程序所有的组件就不可能运行在一台机器上
- 因为这台机器一旦挂掉则意味着整个软件的崩溃，并且程序的执行效率依赖于承载它的硬件，而一台机器机器的性能总归是有限的，受限于目前的硬件水平，就一台机器的性能垂直进行扩展是有极限的。
- 于是我们只能通过水平扩展来增强我们系统的整体性能，这就需要我们将程序的各个组件分布于多台机器去执行。
②数据安全问题
- 根据1的描述，我们将程序的各个组件分布到各台机器，但需知各组件仍然是一个整体，言外之意，所有组件的数据还是要共享的。但每台机器上的组件都只能操作本机的文件，这就导致了数据必然不一致。
- 于是我们想到了将数据与应用程序分离：把文件存放于一台机器，然后将多台机器通过网络去访问这台机器上的文件（用socket实现），即共享这台机器上的文件,共享则意味着竞争，会发生数据不安全，需要加锁处理。。。。
③并发
根据2的描述，我们必须写一个socket服务端来管理这台机器（数据库服务器）上的文件，然后写一个socket客户端，完成如下功能：
- 1.远程连接（支持并发）
- 2.打开文件
- 3.读写（加锁）
- 4.关闭文件
总结
我们在编写任何程序之前，都需要事先写好基于网络操作一台主机上文件的程序（socket服务端与客户端程序），于是有人将此类程序写成一个专门的处理软件，这就是mysql等数据库管理软件的由来，但mysql解决的不仅仅是数据共享的问题，还有查询效率，安全性等一系列问题，总之，把程序员从数据管理中解脱出来，专注于自己的程序逻辑的编写。
二、数据库软件应用史
1.单机游戏
数据存储于各个计算机的本地 无法共享
2.网络游戏
数据存储于网络中 可以共享(数据库服务)
数据库服务集群:提升数据的安全性
三、数据库的本质
1.站在底层原理的角度
数据库指的是操作数据的进程(一堆代码)
2.站在实际应用的角度
数据库指的是可视化操作界面(一些软件)
ps:以后不做特殊说明的情况下讲数据库其实指的是数据库软件
数据库软件本质也是CS架构的程序。这意味着所有的程序员其实都有资格编写一款数据库软件，只不过我们短时间内没这个水平。
四、数据库的分类
1.关系型数据库
特征1:拥有固定的表结构(字段名 字段类型)
id name pwd
特征2:数据之间可以建立数据库层面关系
用户表数据
豪车表数据
豪宅表数据
常见的关系型数据库：
MySQL、Oracle（中文叫甲骨文）、MariaDB、PostgreSQL、sql server、sqlite、db2、access
1.MySQL:开源免费，使用最广，性价比贼高。
主要用于大型门户，例如搜狗、新浪等，它主要的优势就是开放源代码。
因为开放源代码，这个数据库是免费的，他现在是甲骨文公司的产品。
2.Oracle:收费，使用成本较高。但是安全性也是最高的。
主要用于银行、铁路、飞机场等。该数据库功能强大，软件费用高。也是甲骨文公司的产品。
3.PostgreSQL:开源免费并且支持二次开发，兼容性极高。
4.MariaDB:跟MySQL是一个作者，开源免费。
MySQL被甲骨文收购后，MySQL 的原创人员有拉出另外一个分支，命名MariaDB 。该数据库被维基百科，Facebook 甚至 Google等技术巨头使用。
MariaDB 是一种可为 MySQL 提供插件替换功能的数据库服务器。开发人员的首要关注点是安全性，在每个版本发布时，开发人员还会合并所有 MySQL 的安全修补程序，并在需要时对其进行增强。
5.sqlite:小型数据库，主要用于本地测试
2.非关系型数据库
特征1:没有固定的表结构 数据存储采用K:V键值对的形式
{'name':'jason'}
特征2:数据之间无法建立数据库层面的关系
- 虽然不能建立数据库层面的关系，但是我们可以自己编写代码建立逻辑层面的关系。
redis、mongoDB、memcache
1.redis:目前最火、使用频率最高的非关系型数据库(缓存数据库)
虽然缓存数据库是基于内存做数据存取但是拥有持久化的功能，也就是有自动保存和写日志的功能。
2.mongoDB:文档型数据库 最像关系型数据库的非关系型数据库
主要用在爬虫以及大数据领域
3.memcache:已经被redis淘汰
虽然数据库软件有很多 但是操作方式大差不差 学会了一个几乎就可以学会所有
其中以MySQL最为典型
五、MySQL简介
1.版本问题
8.0:最新版
5.7:使用频率较高
5.6:学习推荐使用
ps:站在开发的角度使用哪个版本学习都没有关系，但是新版的软件封装了一些功能，不需要我们自己操作了，对于学习者来说应该亲自实践一下更好。
2、MySQL软件下载及安装见博客
MySQL软件安装教程（windows系统）：
https://www.cnblogs.com/zhihuanzzh/p/16915579.html
3.主要目录介绍
bin目录
存放启动文件
mysqld.exe(服务端) mysql.exe(客户端)
data目录
存放核心数据
my-default.ini
默认的配置文件
readme
软件说明
六、MySQL基本使用
当我们刚下载完MySQL的安装包解压到指定目录后其实已经可以使用MySQL了。只是需要开两个cmd窗口才能使用MySQL服务
打开步骤讲解：
1、先打开cmd窗口（cmd建议使用管理员身份打开）
2、切换到mysql的bin目录下先启动服务端
mysqld
3、保持窗口不关闭 重新打开一个新的cmd窗口
4、切换到mysql的bin目录下启动客户端
mysql
ps：直接使用mysql命令默认是游客模式 权限和功能都很少
使用用户名登陆的指令
mysql -u用户名 -p密码
管理员默认没有密码，连续回车即可（有密码就输入）
mysql -uroot -p密码
有些同学的电脑在启动服务端的时候就会报错，不要慌。拷贝报错信息，然后百度搜索：
mysql启动报错（后面粘贴错误信息）
七、将MySQL服务制作成系统服务
1、把bin目录添加到环境变量
先清空之前打开的cmd窗口,一定要把之前用cmd启动的服务端关闭(ctrl+c)
1.右键计算机，点击属性
2.点击高级系统设置
3.在弹出的窗口中点击环境变量
4.双击第二个内容框（系统变量）中的path
5.在弹出的新窗口中点击新建，然后输入bin文件夹的路径
ps：将MySQL的bin目录路径追加到环境变量中时，win7需要用；分割。
2、将MySQL服务制作成windows系统服务（相当于把MySQL服务制作成系统的守护进程）
将MySQL服务制作成windows系统服务后就不需要使用cmd开启MySQL服务端了。
1.如何查看系统服务
方式一：
同时按住ESC键+SHIFT键+CTRL键打开任务管理器，然后点击侧边栏中最后一个选项就可以查看系统服务
方式二：
同时按住win键和R键，输入services.msc，然后按回车，就能打开服务界面
2.制作MySQL的Windows服务
步骤一：以管理员身份打开cmd窗口
步骤二：输入指令
mysqld --install
3.首次添加MySQL服务后并不会自动启动，需要认为操作一下
打开方式一：选中MySQL服务右键，然后点击启动
打开方式二：使用cmd输入代码启动
开启后我们可以去服务窗口查看状态
3.如何卸载
步骤一：先关闭服务端
打开cmd，输入指令：net stop mysql
步骤二：移除系统服务
接着输入指令：mysqld --remove
八、密码相关操作
1.修改密码
方式1:mysqladmin
输入下列指令：
mysqladmin -u用户名 -p原密码 password 新密码
方式2:直接修改存储用户数据的表
如何查看存储用户数据的表
select * from mysql.user\G
这里如果不在末尾跟上\G读取的内容会出现格式错乱，跟上\G后可以一行行展示文件内容。
方式3:冷门操作 有些版本可能还不支持
set password=password('新密码') # 修改当前登录用户的密码
2.忘记密码的解决方法
方式1:卸载重新装
方式2:把data目录删除 拷贝同桌的目录
这里就相当于用你同桌的信息登陆
方式3:小把戏操作
1.关闭正常的服务端
net stop mysql
2.以跳过授权表的方式重启服务端(不校验密码)
mysqld --skip-grant-table
3.以管理员身份进入然后修改mysql.user表数据即可
mysql -uroot -p
update mysql.user set password=password('123') where Host='localhost' and User='root';
最后一行的代码的作用是设置root账号的密码为123
4.关闭服务端 然后以正常方式启动即可
九、SQL与NoSQL
数据库服务端是可以服务多种类型的客户端，客户端可以是自己开发的，也可以是python代码编写，也可以是java代码编写，但是这样就导致了数据库服务端操作会变得复杂。
SQL和NoSQL就相当于是数据库服务端规定了客户端操作服务端的数据时需要使用的语言。
SQL（操作关系型数据库的语言）
SQL结构化查询语言是一种数据库操作的非过程式编程语言，用于存取数据以及查询、更新和管理关系型数据库系统，一般脚本文件后缀为.sql。
NoSQL（操作非关系型数据库的语言）
NoSQL泛指非关系型数据库，主要用于针对超大规模和高并发的社交SNS类型网站的解决方案
ps:要想跟数据库交互就必须使用数据库指定的语言。
SQL有时候也指代关系型数据库。
NoSQL有时候也指代非关系型数据库。
SQL 和 NoSQL对比
复杂查询SQL数据库比较擅长；
SQL数据库不适合分层次的数据存储，NoSQL可以很好的实现数据的分层次存储，更适合大数据；
对于要求数据严格一致性的应用中，通常使用SQL，因为他稳定，能够保证数据操作的原子性和一致性；
而NoSQL对事务的处理能力有限，一般保证最终一致性；
SQL厂商支持，而NoSQL是社区支持；
性能对比中，NoSQL明显优于SQL数据库，一般NoSQL都充分的利用系统的内存资源；
NoSQL数据库开发方便，不用考虑数据关系和格式。
十、数据库重要概念
什么是数据库服务器
运行有DBMS服务端的计算机，该计算机对内存和硬盘要求都相对较高
什么是数据库管理系统（DataBase Management System 简称DBMS）
管理数据的套接字软件，CS架构
什么是库（DataBase，简称DB）
文件夹
什么是表
文件
什么是记录
一组数据构成一条记录，相当于文件中的一行内容，如1,jason,male,18
什么是数据（Data）
事物的状态
举例
强调:小白阶段为了更加方便的理解，做了以下比喻，本质其实有一点点的区别。
库 就相当于是 文件夹
表 就相当于是 文件夹里面的文件
记录 就相当于是 文件夹里面的文件中的一行行数据
验证指令
1.查看所有的库名称
show databases;
2.查看所有的表名称
show tables;
3.查看所有的记录
select * from mysql.user;
十一、基本SQL语句
注意事项
1.sql语句必须以分号结尾
2.sql语句编写错误之后不用担心 可以直接执行报错即可
基于库的增删改查指令
1.创建库
create database 库名;
库文件会创建在MySQL文件夹内的data文件夹内。
2.查看库
show databases; 查看所有的库名称
其中上图中放的information_schema存在于内存中
show create database 库名; 查看指定库信息
3.编辑库
alter database 库名 charset='utf8';
修改字符编码类型（默认情况是latinl——拉丁文）
4.删除库
drop database 库名;
基于表的增删改查
操作表之前需要先创建库库
create database db1;
然后切换操作库
use db1;
1.创建表
create table 表名(字段名 字段类型,字段名 字段类型);
2.查看表
show tables; 查看库下所有的表名称
show create table 表名; 查看指定表信息
describe 表名; 查看表结构
desc 表名;
ps:如果想跨库操作其他表 只需要在表名前加库名即可
desc mysql.user;
3.编辑表
alter table 表名 rename 新表名;
4.删除表
drop table 表名;
基于记录的增删改查
1.插入数据
insert into 表名 values(数据值1,数据值2); 方式一
insert into t1 values(2, 'kevin1', 20),(3, 'kevin2', 20),(4, 'kevin3', 20),(5, 'kevin4', 20),(6, 'kevin5', 20); # 第二种方式，全字段增加, 批量增加
insert into t1(id, name) values(7, 'kevin7'); # 空的使用NULL填充
有几个字段名插入几个值
2.查询数据
select * from 表名;
查询表中所有的数据
3.编辑数据
update 表名 set 字段名=新数据 where 筛选条件;
update t1 set name='tank' where id=1;
update t1 set age=30 where name='tank';
update t1 set name='jerry',age=30 where id=3;
update t1 set name='oscar' where name='tank' and age=30;
update t1 set name='oscar' where name='kevin3' or name='kevin4';
update t1 set name='tony';
"""以后再自行更新和删除的sql语句的时候，一定要细心、好好看看你的条件是否正确"""
案例：
update t1 set age=age+40 where id=1;
update t1 set price=price+10;
"""一定要谨慎!!!!"""
4.删除数据
delete from 表名;
后面不写where筛选的话就会删除表中的所有数据。
delete from 表名 where id=2;
delete from t1 where id=1;
delete from t1 where id=2 or id=7;
delete from t1; # 这是清空表
十二、字符编码与配置文件
昨天我们讲解到MySQL文件夹内的my-default.ini文件是配置文件，这里我们讲一些简单的配置设置。
ps：修改配置文件后需要重新启动MySQL服务才能生效。
mysql配置文件的作用
1.影响服务端的启动（mysqld）
2.影响客户端的连接
1.\s——查看MySQL相关信息
登陆MySQL后使用\s命令后就可以查看当前用户、版本、编码、端口号。
MySQL5.6（5.6版本字符编码类型是拉丁文和utf8）及之前的版本编码需要人为统一 之后的版本已经全部默认统一。如果想要永久修改编码配置，需要操作配置文件。
2.配置文件修改默认字符编码类型
默认的配置文件是my-default.ini
但是我们在使用的时候不建议直接修改默认的配置文件，理想的操作应该是拷贝上述文件并重命名为my.ini，然后再my.ini中修改配置。
配置文件打开后分成两部分内容，一部分是注释，一部分是配置代码。注释部分在每一行的开头有井号（#）。
ps：配置文件中的注释可以有中文，但是配置代码中不能出现中文。
配置代码
直接拷贝字符编码相关配置即可无需记忆
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
ps:
1.utf8mb4能够存储表情 功能更强大
2.utf8与utf-8是有区别的 MySQL中只有utf8
修改了配置文件中关于[mysqld]的配置 需要重启服务端
3.利用配置文件免输入账号信息登陆
将管理员登录的账号密码直接写在配置文件中，之后使用mysql登录即可。
代码如下：
[mysql]
user='root'
password=123
十三、数据库存储引擎
1、存储引擎
数据库针对数据采取的多种存取方式。存储引擎是数据库底层软件组件，数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据库操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
2、查看存储引擎的语句
show engines;
3、需要了解的四个存储引擎
1.MyISAM
MySQL5.5（包括5.5版本）之前默认使用的的存储引擎。
存取数据的速度快，但是功能较少，安全性较低。
2.InnoDB
MySQL5.5之后默认使用的的存储引擎。
支持事务、行锁、外键等操作，虽然存取速度没有MyISAM快，但是安全性更高。
3.Memory
基于内存存取数据，仅用于临时表数据存取。
4.BlackHole
任何写入进去的数据都会立刻丢失。
4、了解不同存储引擎底层文件个数
用各个存储引擎创建不同的表，然后去查看文件个数（如果不是5.6版本可能文件个数会不一样）。
create database db2;
use db2;
create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=memory;
create table t4(id int) engine=blackhole;
1.innodb有两个文件
.frm 表结构
.ibd 表数据(表索引也放一起了)
2.myisam有三个文件
.frm 表结构
.MYD 表数据
.MYI 表索引
3.memory只有一个文件
.frm 表结构
4.blackhole只有一个文件
.frm 表结构
创建了表之后我们加入数据
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
ps:MySQL默认忽略大小写
十四、数据类型(重要)
1、整型: 存储整数的
tinyint smallint int bigint
# 不同的数据类型区别就是所存储的范围不一样
tinyint: 它是使用一个字节来保存数据，一个字节代表8位 11111111--->256种情况(0-255) (-128-127)
smallint:2个字节, 代表16位, 65536(0-65535) (-32768-32767)
mediumint: 3个字节
int: 4个字节，2**32=42....(-21...- 21...)
bigint:8个字节(最大的) 可以存手机号(11)
"""怎么选数据类型：看你这一列存什么数据"""
比如：age int
## 整型默认情况下带不带符号?
create table t5 (id tinyint);
insert into t5 values(256);
结论是：带符号的，所有的整型默认都是带符号的 减半
# 怎么样去掉符号
create table t6 (id tinyint unsigned);
2、浮点型
float double decimal
float(255, 30) # 总位数是255位、小数点后60位
double(255, 30) # 总位数是255位、小数点后60位
decimal(65, 30) # 总位数是255位、小数点后60位
"""他们三个区别是什么呢?"""
create table t7 (id float(255, 30));
create table t8 (id double(255, 30));
create table t9 (id decimal(65, 30));
insert into t7 values (1.11111111111111111111111111);
insert into t8 values (1.11111111111111111111111111);
insert into t9 values (1.11111111111111111111111111);
"""结论：三者的精确度不一样：decimal >>> double >>> float(精确到7位了)"""
# 以后到底是选哪个更好呢
"""就我多年的经验来看，大家都选decimal"""
price decimal(8,2) # 999.99 # price：1000
3、字符串
char(4): 定长类型，超出4位，就报错，不够4位，使用空格填充 abc helloworld
varchar(4): 可变长类型，超出4位，报错，不够4位的，有几位存几位 abc a
create table t10 (id int, name char(4));
create table t11 (id int, name varchar(4));
insert into t10 values(1, 'jerry');
insert into t11 values(1, 'jerry');
"""如果你想超出范围之后，直接报错，需要设置严格模式!!!"""
sql_mode
show variables like "%mode%";
# 设置严格模式
1. 命令行模式：临时修改
set global sql_mode='STRICT_TRANS_TABLES'; # 不区分大小写
2. 配置文件修改：永久修改
## 研究定长和不定长
create table t12 (id int, name char(4));
create table t13 (id int, name varchar(4));
insert into t12 values(1, 'ke');
insert into t13 values(1, 'ke');
## 验证是否补充了空格
select char_length(name) from t12;
select char_length(name) from t13;
"""默认情况下，没有对char类型填充空格，如果想看填充了空格，需要设置严格模式"""
# 设置严格模式
1. 命令行模式：临时修改
set global sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH'; # 不区分大小写
2. 配置文件修改：永久修改
4、日期
date datetime time year
年月日 年月日 十分秒 十分秒 年
create table t14 (
id int,
reg_time date,
reg1_time datetime,
reg2_time time,
reg3_time year
);
insert into t14 values(1, '2023-10-1', '2023-11-11 11:11:11', '11:11:11', 2023);
5、枚举
# 多选一
enum
create table t15 (id int, hobby enum('read', 'music', 'tangtou', 'xijio'));
insert into t15 values(1, 'read');
# 多选多:包含多选一
set
create table t16 (id int, hobby set('read', 'music', 'tangtou', 'xijio'));
insert into t16 values(2, 'read,music1');
创建表的完整语法
create table t1(
id int,
name varchar(43),
age int
);
create table 库名.表名(
字段名1 数据类型 约束条件 约束条件 约束条件 约束条件,
字段名2 数据类型 约束条件 约束条件 约束条件 约束条件,
字段名3 数据类型 约束条件 约束条件 约束条件 约束条件,
字段名4 数据类型 约束条件 约束条件 约束条件 约束条件,
字段名5 数据类型 约束条件 约束条件 约束条件 约束条件,
字段名6 数据类型 约束条件 约束条件 约束条件 约束条件
);
insert into 库名.t1 values('1', 2, 3, 4, 5, 6);
1. 字段名和数据类型必须写的，不能省略
2. 约束条件是可选的，可有可无，而且可有有多个
3. 表结构中最后一个字段不能有逗号
约束条件
约束条件其实就是在数据类型的基础之上在做约束
1. unsigned # 无符号
id int unsigned
2. zerofill # 0填充
3. default # 默认值
create table t4 (id int, name varchar(32) default 'kevin');
insert into t4 values(1, 'jerry');
insert into t4(id) values(1);
4. not null # 非空
create table t5 (id int, name varchar(32) not null);
insert into t5(id) values(1);
5. unique # 唯一
单列唯一
create table t6 (id int, name varchar(32) unique);
多列唯一
create table t7 (
id int,
ip varchar(32),
port varchar(32),
unique(ip, port)
);
6. 主键(primary key)
"""主键单纯从约束上来看，它相当于是非空且唯一 unique not null"""
id unique not null ---------> id primary key
create table t8 (id int primary key);
create table t8 (id int unique not null);
# 主键本身是一种索引，索引能够加快查询速度
InnoDB存储引擎规定每一张表都要有一个主键，但是，我之前创建的表都没有指定主键, 表是怎么创建成功的?
"""
是因为InnoDB存储引擎内部有一个隐藏的主键，这个主键我们看不到，它也不能够加快查询速度，仅仅是为了帮助我们把表创建成功. 所以，以后我们创建表的时候都主动的创建一个主键，我们自己创建的主键能够加快查询速度，因为是一个索引.
"""
一般情况下，主键应该创建哪个字段? 大多都给id字段加了，所以，每一张表都要有一个id字段，并且一张表中不只是有一个主键，可以有多个主键，但是，大多数情况下，都只有一个
主键一般都给id aid sid uid pid ...
create table t(
id int primary key,
name varchar(32)
)
# 我们可以通过主键确定一张表中得唯一一条记录!!!
7. auto_incrment
# 自增：每一次主动比上一次加1
"""一般情况下，它配合主键使用"""
create table t9 (
id int primary key auto_increment,
name varchar(32)
);
整型中括号中得数字的作用
id int(10) # 数字不代表的是范围
name varchar(32) # 数字代表的就是存储的范围
create table t1(id int(3));
create table t2(id int(9));
insert into t2 values(9);
create table t3(id int(9) zerofill);
insert into t3 values(9);
清空表的两种方式
1. delete from t; # 不会重置id值
2. truncate t9; # 清空表、重置id值
"""truncate：建议使用truncate，使用这个，万一你清空错了，还有机会恢复"""
mysql它有很多个日志文件，binlog日志-----》可以恢复数据，记录了你所有的SQL语句
补充一些其他的SQL语句
语法：
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段 # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
"""除了以上的SQL语句，还有很多其他的，如果遇到了不会写，就直接搜索引擎"""
数据准备
# 数据准备
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age smallint(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('tom','male',78,'20150302','teacher',1000000.31,401,1),#以下是教学部
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
查询关键字之where
# where------>筛选条件的
"""
模糊查询:没有明确的筛选条件
关键字:like
关键符号:
%:匹配任意个数任意字符
_:匹配单个个数任意字符
show variables like '%mode%';
"""
where筛选功能
"""
模糊查询:没有明确的筛选条件
关键字:like
关键符号:
%:匹配任意个数任意字符
_:匹配单个个数任意字符
show variables like '%mode%se';
"""
# 1.查询id大于等于3小于等于6的数据
select id,name from emp where id >= 3 and id <= 6;
select * from emp where id between 3 and 6;
# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000); # 简写
# 3.查询员工姓名中包含o字母的员工姓名和薪资
# 在你刚开始接触mysql查询的时候，建议你按照查询的优先级顺序拼写出你的sql语句
"""
先是查哪张表 from emp
再是根据什么条件去查 where name like ‘%o%’
再是对查询出来的数据筛选展示部分 select name,salary
"""
select name,salary from emp where name like '%o%';
# 4.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
# 5.查询id小于3或者大于6的数据
select * from emp where id not between 3 and 6;
# 6.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (20000,18000,17000);
# 7.查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is
select name,post from emp where post_comment = NULL; # 查询为空！
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;
'''在sql中，NULL和''不一样''
查询关键字之group by分组
分组: 按照某个指定的条件将单个单个的个体分成一个个整体
按照男女分组：男 女
按照年龄分组：20岁以下 20-30 30-40
# 单纯的分组是没有意义的
在MySQL中分组之后，只能够获得分组的依据! 按照哪个字段分组就只能获取这个字段的值，别的字段不能拿到
分组一般配合聚合函数使用：
sum max min avg count
分组的关键字：group by
# 数据分组应用场景：每个部门的平均薪资，男女比例等
# 1.按部门分组
1. 分组之后默认可以获取所有的字段信息
2. 分组之后，展示的数据都是每个组的第一条数据
分组
"""
按照某个指定的条件将单个单个的个体分成一个个整体
eg: 按照男女将人分组
按照肤色分组
按照年龄分组
"""
# 分组之后默认只能够直接过去到分组的依据 其他数据都不能直接获取
针对5.6需要自己设置sql_mode
set global sql_mode = 'only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
# 聚合函数
聚合函数主要就是配合分组一起使用
max min sum count avg
# 数据分组应用场景：每个部门的平均薪资，男女比例等
# 1.按部门分组
select * from emp group by post; # 分组后取出的是每个组的第一条数据
select id,name,sex from emp group by post; # 验证
"""
设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，
不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取
"""
set global sql_mode="strict_trans_tables,only_full_group_by";
# 重新链接客户端
select * from emp group by post; # 报错
select id,name,sex from emp group by post; # 报错
select post from emp group by post; # 获取部门信息
# 强调:只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名
# 2.获取每个部门的最高工资
# 以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
# 每个部门的最高工资
select post,max(salary) from emp group by post;
补充:在显示的时候还可以给字段取别名
select post as '部门',max(salary) as '最高工资' from emp group by post;
as也可以省略 但是不推荐省 因为寓意不明确
# 每个部门的最低工资
select post,min(salary) from emp group by post;
# 每个部门的平均工资
select post,avg(salary) from emp group by post;
# 每个部门的工资总和
select post,sum(salary) from emp group by post;
# 每个部门的人数
select post,count(id) from emp group by post;
统计的时候只要是非空字段 效果都是一致的
这里显示age，salary，id最后演示特殊情况post_comment
分组补充函数
# group_concat 分组之后使用
如果真的需要获取分组以外的数据字段 可以使用group_concat()
# 每个部门的员工姓名
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'|',sex) from emp group by post;
select post,group_concat(name,'|',sex, '|', gender) from emp group by post;
select post,group_concat(distinct name) from emp group by post;
select post,group_concat(distinct name separator '%') from emp group by post;
# concat 不分组使用
select concat(name,sex) from emp;
select concat(name,'|',sex) from emp;
# concat_ws()
select post,concat_ws('|', name, age, gender) from emp group by post;
关键字之having过滤
where与having都是筛选功能 但是有区别
where在分组之前对数据进行筛选
having在分组之后对数据进行筛选
1.统计各部门年龄在30岁以上的员工平均薪资,并且保留平均薪资大于10000的部门.
# 先筛选出年龄在30岁以上的
select * from emp where age > 30;
# 在进行分组，按照部门分组
select avg(salary) as avg_salary from emp where age > 30 group by post;
# 保留平均薪资大于10000的部门
select avg(salary) as avg_salary from emp where age > 30 group by post having avg(salary) > 10000;
关键字之distinct去重
distinct:去重
"""带主键的数据去重有没有意义? 没有，主键本身就是唯一的"""
select distinct id,age from emp;
关键字之order by排序
select * from emp order by salary; #默认升序排
select * from emp order by salary desc; #降序排
#先按照age降序排，在年轻相同的情况下再按照薪资升序排
select * from emp order by age desc,salary;
'''多字段排序，如果想让后面的字段排序生效，前提：前面的排序字段必须一样'''
# 统计各部门年龄在20岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
# 20岁以上的员工
select * from emp where age > 20;
# 各部门的平均薪资
select avg(salary) from emp where age > 20 group by post having avg(salary) > 1000;
#
select avg(salary) from emp where age > 20 group by post having avg(salary) > 1000 order by avg(salary) desc;
关键字之limit分页
# 限制展示条数
# 限制展示条数
select * from emp limit 3;
# 查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
# 分页显示
select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
select * from emp limit 5,5;
关键字之regexp正则
select * from emp where name regexp '^j.*(n|y)$';
浙公网安备 33010602011771号