数据库基础一

一、数据库介绍

名词介绍

数据库：是数据的汇集，它以一定的组织形式存于存储介质上。是对企业核心数据高效安全存储和管理的软件
DBA(管理维护使用数据库的管理人员)：负责数据库的规划、设计、协调、维护和管理等工作
应用程序：指以数据库为基础的应用程序
数据库(DBMS)是管理数据库的系统软件，它实现数据库系统的各种功能，是数据库系统的核心。

数据库(DBMS)产品种类

 RDBMS  : 关系型数据库 管理系统      : Oracle MySQL PG MSSQL
 NoSQL  : 非关系型数据库            : Mongodb Redis Elasticsearch
 NewSQL : 新型的分布式数据库 解决方案 : PinCAP TiDB PolarDB TDSQL OB，Spanner ，AliSQL(RDS+DRDS)

 RDBMS ---> NOSQL+RDBMS ---> NoSQL(RDBMS),RDBMS(NoSQL)----> NewSQL (spanner TiDB  PDB)

数据库各类型产品热度统计

SQL

1、SQL: Structure Query Language

结构化查询语言
关系型数据库通用的命令
遵循SQL92的标准(SQL_MODE)
SQL解释器

以;分号作为一条语句的结尾

关键字不区分大小写

2、数据存储协议：应用层协议，C/S

S：server, 监听于套接字，接收并处理客户端的应用请求
C：Client

3、客户端程序接口

4、应用编程接口

ODBC：Open Database Connectivity
JDBC：Java Data Base Connectivity

5、约束

约束：constraint，表中的数据要遵守的限制
主键：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；必须提供数据，即NOT NULL，一个表只能有一个
唯一键：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；允许为NULL，一个表可以存在多个
外键：一个表中的某字段可填入的数据取决于另一个表的主键或唯一键已有的数据
检查：字段值在一定范围内

6、基本概念

索引：将表中的一个或多个字段中的数据复制一份另存，并且按特定次序排序存储
关系运算：

选择：挑选出符合条件的行投影：挑选出需要的字段连接：表间字段的关联

7、数据模型

数据抽象：

物理层：数据存储格式，即RDBMS在磁盘上如何组织文件逻辑层：DBA角度，描述存储什么数据，以及数据间存在什么样的关系视图层：用户角度，描述DB中的部分数据
关系模型的分类：
- 关系模型
- 基于对象的关系模型
- 半结构化的关系模型：XML数据

8、MySQL资料

官方网址：

https://www.mysql.com/ http://mariadb.org/ https://www.percona.com

官方文档：

https://dev.mysql.com/doc/ https://mariadb.com/kb/en/ https://www.percona.com/software/mysql-database/percona-server

二、MySQL简介

1、什么是数据库？

　　数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，它产生于距今六十多年前，随着信息技术和市场的发展，特别是二十世纪九十年代以后，数据管理不再仅仅是存储和管理数据，而转变成用户所需要的各种数据管理的方式。数据库有很多种类型，从最简单的存储有各种数据的表格到能够进行海量数据存储的大型数据库系统都在各个方面得到了广泛的应用。

　　主流的数据库有：sqlserver，mysql，Oracle、SQLite、Access、MS SQL Server等，本文主要讲述的是mysql

2、数据库管理作用

将数据保存到文件或内存
接收特定的命令，然后对文件进行相应的操作

PS：如果有了以上管理系统，无须自己再去创建文件和文件夹，而是直接传递命令给上述软件，让其来进行文件操作，他们统称为数据库管理系统（DBMS，Database Management System）

3、数据库管理系统的优点

相互关联的数据的集合
较少的数据冗余
程序与数据相互独立
保证数据的安全、可靠
最大限度地保证数据的正确性
数据可以并发使用并能同时保证一致性

4、数据库管理系统的基本功能

 数据定义
 数据处理
 数据安全
 数据备份

5、数据库管理系统

数据库是数据的汇集，它以一定的组织形式存于存储介质上
DBMS是管理数据库的系统软件，它实现数据库系统的各种功能。是数据库系统的核心
DBA：负责数据库的规划、设计、协调、维护和管理等工作
应用程序指以数据库为基础的应用程序

6、数据库系统的架构

单机架构大型主机/终端架构主从式架构（C/S）分布式架构

7、关系型数据库

关系：关系就是二维表，其中：表中的行、列次序并不重要
行(row)：表中的每一行，又称为一条记录
列(column)：表中的每一列，称为属性，字段
主键(Primary key)：用于唯一确定一个记录的字段
域(domain)：属性的取值范围，如，性别只能是‘男’和‘女’两个值

8、联系的类型

 一对一联系(1:1)
 一对多联系(1:n)
 多对多联系(m:n)

（1）数据的操作：

数据提取：在数据集合中提取感兴趣的内容。SELECT 数据更新：变更数据库中的数据。INSERT、DELETE、UPDATE

（2）数据的约束条件：是一组完整性规则的集合

 实体（行）完整性 Entity integrity
 域（列）完整性 Domain Integrity
 参考完整性 Referential Integrity

9、简易数据规划流程

第一阶段：收集数据，得到字段

收集必要且完整的数据项
转换成数据表的字段

第二阶段：把字段分类，归入表，建立表的关联

关联：表和表间的关系
分割数据表并建立关联的优点
节省空间
减少输入错误
方便数据修改

第三阶段：

规范化数据库

10、数据库的正规化分析

数据库规范化，又称数据库或资料库的正规化、标准化，是数据库设计中的一系列原理和技术，以减少数据库中数据冗余，增进数据的一致性。关系模型的发明者埃德加·科德最早提出这一概念，并于1970年代初定义了第一范式、第二范式和第三范式的概念
RDMBS设计范式基础概念
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，不同的规范要求被称为不同范式，各种范式呈递次规范，越高的范式数据库冗余越小/2目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般数据库只需满足第三范式(3NF）即可

11、范式

1NF：无重复的列，每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性，确保每一列的原子性。除去同类型的字段，就是无重复的列说明：第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库
2NF：属性完全依赖于主键，第二范式必须先满足第一范式，要求表中的每个行必须可以被唯一地区分。通常为表加上一个列，以存储各个实例的唯一标识PK，非PK的字段需要与整个PK有直接相关性
3NF：属性不依赖于其它非主属性，满足第三范式必须先满足第二范式。第三范式要求一个数据库表中不包含已在其它表中已包含的非主关键字信息，非PK的字段间不能有从属关系

三、MySQL的特性

插件式存储引擎：也称为“表类型”，存储管理器有多种实现版本，功能和特性可能均略有别；用户可根据需要灵活选择，从 Mysql 5.5.5 开始 innoDB 引擎是 MYSQL 默认引擎

MyISAM ==> Aria InnoDB ==> XtraDB

单进程，多线程
诸多扩展和新特性
提供了较多测试组件
开源

四、MySQL安装

MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），MySQL数据库系统使用最常用的数据库管理语言–结构化查询语言（SQL）进行数据库管理。在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

使用mysql必须具备以下条件：

a. 安装MySQL服务端
b. 安装MySQL客户端
c. 【客户端】连接【服务端】
d. 【客户端】发送命令给【服务端MySQL】服务的接受命令并执行相应操作(增删改查等)

MySQL 企业版本选择

 5.6 ： 5.6.40+（记忆） 双数版，GA(稳定版) 6-12月
 5.7 ： 5.7.20+（2017913） 双数版
 8.0 ： 8.0.20+ 双数版

MySQL下载

项目官方：https://downloads.mariadb.org/mariadb/repositories/ 国内镜像：https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-x.y.z/yum/centos7-amd64/

最新版本下载地址：https://dev.mysql.com/downloads/mysql/8.0.html

历史版本下载地址：https://downloads.mysql.com/archives/community/

避免使用测试样例多的功能

Windows安装参考：http://www.cnblogs.com/lonelywolfmoutain/p/4547115.html

Linux安装参考：https://www.cnblogs.com/along21/p/7668023.html

yum源安装MySQL

yum源网址：https://downloads.mariadb.org/mariadb/repositories/

 vim /etc/yum.repos.d/mariadb.repo
 [mariadb]
 name = MariaDB
 baseurl = http://yum.mariadb.org/10.2/centos7-amd64
 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
 gpgcheck=1
 yum源安装mariadb： yum install MariaDB-server

 
 若有其他yum源，加一个enabled=0 先临时关闭，yum clean all 清除缓存，注意：官方给的yum源安装的Maria没有安装相依赖的包，还需自己安装相依赖的包
 查询端口对应的进程信息 lsof -i :3306 或 netstat -tnulp | grep 3306

MySQL 二进制包安装

 /usr/libexec/mysqld   服务器执行主程序
 /usr/lib/systemd/system/mariadb.service  服务程序
 /etc/my.cnf.d/server.cnf 配置文件
 /var/lib/mysql 数据库数据存放的路径
 /var/log/mariadb/mariadb.log  存放日志路径
 centos6启动mysql服务
 service mysqld start

准备虚拟机

主机名	IP	配置
db01	10.0.0.51	1核2G

关闭seLinux和防火墙

上传二进制包到/opt ，解压并创建软链接

 [root@db01 opt]# rz mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
 [root@db01 opt]# tar xf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
 [root@db01 opt]# du -sh *
 385M    mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz
 630M    mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
 2.5G    mysql-8.0.20-linux-glibc2.12-x86_64
 469M    mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
 [root@db01 opt]# ln -s /opt/mysql-8.0.20-linux-glibc2.12-x86_64 /usr/local/mysql
 [root@db01 opt]# ll /usr/local/mysql
 lrwxrwxrwx 1 root root 40 11月  2 11:21 /usr/local/mysql -> /opt/mysql-8.0.20-linux-glibc2.12-x86_64

基础环境准备

 # 移除冲突软件

 [root@db01 opt]# yum remove mariadb-libs -y

 # 创建虚拟用户

 [root@db01 opt]# useradd -M -r mysql
 [root@db01 opt]# id mysql

 uid=998(mysql) gid=996(mysql) 组=996(mysql)

 # 创建目录并授权
 [root@db01 opt]# mkdir -p /data/3306/data
 [root@db01 opt]# chown -R mysql. /data/
 [root@db01 opt]# ls -ld /data/
 drwxr-xr-x 3 mysql mysql 18 11月  2 11:26 /data/
 # 配置环境变量
 [root@db01 opt]# echo export PATH=\$PATH:/usr/local/mysql/bin/ >> /etc/profile && . /etc/profile

初始化数据（创建系统数据）

 [root@db01 opt]#yum install -y libaio-devel
 [root@db01 opt]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
 2020-11-02T03:32:56.683285Z 0 [System] [MY-013169] [Server] 
/opt/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.20) initializing of server in progress as process 2049
 2020-11-02T03:32:56.709263Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
 2020-11-02T03:32:58.296518Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
 2020-11-02T03:32:59.507707Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! 
Please consider switching off the --initialize-insecure option.

参数说明:

 --initialize-insecure       # 初始化核心参数(不安全的)
 --user=mysql                # 指定初始化用户(默认用户root)
 --basedir=/usr/local/mysql  # 数据库软件位置
 --datadir=/data/3306/data   # 数据存储位置

有可能的报错:

[error] mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

解决: yum install -y libaio-dever
多版本之间的初始化不同:

5.7 和 8.0 一样

5.7 之前不一样
 /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
 --initialize
 会生成一个12位,4种密码复杂度的 root@localhost 用户临时密码,第一次登录需要修改, 密码过期时间：180
 
 --initialize-insecure
 root@localhost 用户密码为空

配置文件

 cat > /etc/my.cnf <<EOF
 [mysqld]
 user=mysql
 basedir=/usr/local/mysql
 datadir=/data/3306/data
 port=3306
 socket=/tmp/mysql.sock
 [client]
 socket=/tmp/mysql.sock
 EOF

MySQL数据库无法启动

首先查看日志：/data/mysql/data/主机名.err 的 [ERROR] 上下文

如果报告类似 without updating PID 错误，可能原因：

/etc/my.cnf 路径不对 /tmp/mysql.sock 文件修改过或删除过 /data/3306/data 目录权限不是 mysql 参数改错了

加入systemctl服务管理，启动并开机自启

 [root@db01 ~]# cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
 [root@db01 ~]# systemctl enable mysqld
 mysqld.service is not a native service, redirecting to /sbin/chkconfig.
 Executing /sbin/chkconfig mysqld on
 [root@db01 ~]# systemctl start mysqld
 [root@db01 ~]# systemctl status mysqld.service

直接启动

 /etc/init.d/mysqld start
 /etc/init.d/mysqld stop
 /etc/init.d/mysqld status

五、MySQL操作

1、连接数据库

（1）连接命令mysql

 -u 用户
 -h 主机地址/主机名
 -p 密码
 -P 端口（大写字母）
 -s 静默
 -S 套接字位置（指定连接socket文件路径）
 -e 免交互执行SQL命令
 <  恢复数据
 执行SQL命令
 例：mysql -u user -p passwd
 数据库：database
 表：table
 行：row
 列：column
 索引：index
 视图：view
 用户：user
 权限：privilege
 存储过程：procedure
 存储函数：function
 触发器：trigger
 事件调度器：event scheduler，任务计划

例：mysql -u user -p passwd

mysql -uroot -p1 < /root/world.sql

（2）常见错误如下：

 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'
 (2), it means that the MySQL server daemon (Unix) or service (Windows) is not running.

（3）退出连接：

QUIT 或者 Ctrl+D

（4）-e 选项用法:

 [root@ansiblescripts]# mysql -pcentos  -e "show databases" # 直接输入命令可以查询，不需要进入到mysql里面再输入命令
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 +--------------------+

（5）socket地址

 服务器监听的两种socket地址：

 （1）ip socket: 监听在tcp的3306端口，支持远程通信
 （2）unix sock: 监听在sock文件上，仅支持本机通信
 (如：/var/lib/mysql/mysql.sock)
  说明：host为localhost,127.0.0.1时自动使用unix sock

（6）内置命令

 help                    打印mysql帮助
 \c  ctrl+c              结束上个命令运行
 \q quit; exit; ctrl+d   退出mysql
 \G                      将数据竖起来显示
 source                  恢复备份文件

2、查看数据库，创建数据库，使用数据库查看数据库

（1）查看数据库

 show databases;

默认（centos6 自带）数据库：

 mysql - 用户权限相关数据
 test - 用于用户测试数据
 information_schema - MySQL本身架构相关数据

（2）创建数据库:

 create database db1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;   # utf8编码
 create database db1 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;   # gbk编码

（3）使用数据库:

 use db1;

（4）显示当前使用的数据库中所有表：

 SHOW TABLES;

（5）设置数据库密码：

mysql 数据库路径：ls /var/lib/mysql/scripts 目录下。

[root@ansible scripts]# mysql_secure_installation  设置数据库密码的脚本
  
 NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
       SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
  
 In order to log into MariaDB to secure it, we'll need the current
 password for the root user.  If you've just installed MariaDB, and
 you haven't set the root password yet, the password will be blank,
 so you should just press enter here.
  
 Enter current password for root (enter for none):
 OK, successfully used password, moving on...
  
 Setting the root password ensures that nobody can log into the MariaDB
 root user without the proper authorisation.
  
 Set root password? [Y/n] y  # 设置root口令
 New password:
 Re-enter new password:
 Password updated successfully!
 Reloading privilege tables..
  ... Success!
  
  
 By default, a MariaDB installation has an anonymous user, allowing anyone
 to log into MariaDB without having to have a user account created for
 them.  This is intended only for testing, and to make the installation
 go a bit smoother.  You should remove them before moving into a
 production environment.
  
 Remove anonymous users? [Y/n] y # 删除匿名用户账号
  ... Success!
  
 Normally, root should only be allowed to connect from 'localhost'.  This
 ensures that someone cannot guess at the root password from the network.
  
 Disallow root login remotely? [Y/n] y  # 禁止远程登录
  ... Success!
  
 By default, MariaDB comes with a database named 'test' that anyone can
 access.  This is also intended only for testing, and should be removed
 before moving into a production environment.
  
 Remove test database and access to it? [Y/n] y  # 删除test测试数据库
  - Dropping test database...
  ... Success!
  - Removing privileges on test database...
  ... Success!
  
 Reloading the privilege tables will ensure that all changes made so far
 will take effect immediately.
  
 Reload privilege tables now? [Y/n] y  # 立即生效当前设置
  ... Success!
  
 Cleaning up...
  
 All done!  If you've completed all of the above steps, your MariaDB
 installation should now be secure.
  
 Thanks for using MariaDB!

六、用户管理

6.1 作用

登录数据库

管理数据库对象

6.2 格式

mysql用户账号由两部分组成：

 'USERNAME'@'HOST‘

HOST说明：

限制此用户可通过哪些远程主机连接mysql服务器
支持使用通配符：

 % 匹配任意长度的任意字符
 172.16.0.0/255.255.0.0 或 172.16.%.%
 _ 匹配任意单个字符

 示例:
 'root'@'localhost‘               本地用户
 'root'@'10.0.0.2‘                单一IP用户
 'root'@'10.0.0.%‘                范围IP用户
 'root'@'10.0.0.5%‘               范围IP用户
 'root'@'10.0.0.0/255.255.254.0‘  范围IP用户
 'root'@'%‘                       范围IP用户

不指定@'HOST‘默认@'%‘

匹配优先级：

安全规范:

a. 尽量小, 最好细化到单一IP, 不能使用%

b. 用户名有特点

c. 无用的用户要删除(5.7以前)或者锁定

d. 密码超过三种复杂度, 12位以上

6.3 操作命令

创建用户和密码

 create user 'USERNAME'@'HOST' identified by 'PASSWORD';

8.0新特性:
8.0 之后语句变化, grant不再支持创建用户和修改密码的功能, 不能
 grant all on *.* to wordpress@'10.0.0.%' identified by '123';
权限和用户分开管理

必须先创建用户再授权
 create user 'wordpress'@'10.0.0.%' identified by '123';
 grant all on *.* to wordpress@'10.0.0.%';
8.0 之前使用的密码插件是mysql_native_password

8.0 之后使用的密码插件是caching_sha2_password

导致问题: 老的客户端程序, 连接不了8.0版本

解决方法:

创建用户时指定密码加密插件为mysql_native_password
 create user wordpress@'10.0.0.%' identified with mysql_native_password by '123';
修改用户时替换密码加密插件为mysql_native_password
 alter user wordpress@'10.0.0.%' identified with mysql_native_password by '123';
配置文件中指定默认加密插件为mysql_native_password

修改用户

 alter user wordpress@'10.0.0.%' identified by '123';

锁定用户

 alter user wordpress@'10.0.0.%' account lock;

解锁用户

 alter user wordpress@'10.0.0.%' account unlock;

删除用户

 drop user wordpress@'10.0.0.%';

获取数据表结构

 desc mysql.user;

查询所有mysql用户

 select user,host,authentication_string,plugin from mysql.user;

设置别名as ""

 select user as "用户",host as "白名单",authentication_string as "密码",plugin as "插件" from mysql.user;

查看帮助

 ? 或者 help

查看创建用户帮助

 help create user;

生产中开用户

如何沟通开用户 1.是否有邮件批复 2.对哪些库和表做操作 3.做什么操作(增删改查) 4.从什么地址来登录

开发人员找你要root用户密码？ 1.走流程拒绝他 2.如果是金融类的公司（1）原则上是不允许任何非DBA人员持有或申请root （2）如果有人私下索要root密码，即时举报。

七、权限管理

7.1 作用

约束用户能够对数据库对象(库, 表)使用的功能(SQL)

7.2 语法格式

 grant 权限 on 权限范围 to 用户;

权限范围

对于数据库及内部其他权限

 *.*         　　　　　　全局范围, 所有数据库
 数据库名.* 　　　　　　　单库范围, 指定数据库中的所有
 数据库名.表 　　　　　　　单表范围, 指定数据库中的某张表
 数据库名.存储过程 　　　　指定数据库中的存储过程

对于用户和IP的权限

 用户名@IP地址  　　　　用户只能在该IP下才能访问
 用户名@192.168.1.%   用户只能在该IP段下才能访问(通配符%表示任意)
 用户名@%             用户可以再任意IP下访问(默认IP地址为%)

权限列表

查看所有权限列表

 show privileges;

 all：除 Grant option 外的所有权限
 
 alter：使用alter table
 alter routine：使用alter procedure和drop procedure
 create：使用create table
 create routine：使用create procedure
 create temporary tables：使用create temporary tables
 create user：使用create user、drop user、rename user和revoke  all privileges
 create view：使用create view
 delete：使用delete
 drop：使用drop table
 execute：使用call和存储过程
 file：使用select into outfile 和 load data infile
 grant option：使用grant 和 revoke
 index：使用index
 insert：使用insert
 lock tables：使用lock table
 process：使用show full processlist
 select：使用select
 show databases：使用show databases
 show view：使用show view
 update：使用update
 usage：无权限-仅允许连接
 reload：使用flush
 shutdown：使用mysqladmin shutdown(关闭MySQL)
 super：使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆
 replication client：服务器位置的访问
 replication slave：由复制从属使用

 ... ...

权限类别

管理类

 CREATE TEMPORARY TABLES
 CREATE USER
 FILE
 SUPER
 SHOW DATABASES
 RELOAD
 SHUTDOWN
 REPLICATION SLAVE
 REPLICATION CLIENT
 LOCK TABLES
 PROCESS

2、程序类： FUNCTION、PROCEDURE、TRIGGER

 CREATE
 ALTER
 DROP
 EXCUTE

库和表级别：DATABASE、TABLE

 ALTER
 CREATE
 CREATE VIEW
 DROP
 INDEX
 SHOW VIEW
 GRANT OPTION：能将自己获得的权限转赠给其他用户

4、数据操作

 SELECT
 INSERT
 DELETE
 UPDATE

字段级别

 SELECT(col1,col2,...)
 UPDATE(col1,col2,...)
 INSERT(col1,col2,...)

所有权限

 ALL PRIVILEGES 或 ALL

注意：

① MariaDB 服务进程启动时会读取mysql 库中所有授权表至内存

② GRANT 或 REVOKE 等执行权限操作会保存于系统表中，MariaDB 的服务进程通常会自动重读授权表，使之生效

③ 对于不能够或不能及时重读授权表的命令，可手动让MariaDB 的服务进程重读授权表：FLUSH PRIVILEGES;

7.3 操作命令

① 授权

 grant 权限 on 数据库.表 to '用户'@'IP地址';

不能重复授权, 重复授权会追加而不是覆盖

② 取消授权(回收权限)

 revoke 权限 on 数据库.表 from '用户名'@'IP地址';

③ 查看用户权限

专用命令

 show grants for '用户'@'IP地址';

查看授权表

 select * from mysql.user \G;

注意：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）

 加载mysql库

 use mysql;


 查看表名

 show tables;
 user        : user host auth plugin 全局范围授权的用户权限  (*.*)
 db          : user host auth plugin 单库范围授权的用户权限  (wp_user.*)
 table_priv  : user host auth plugin 单表范围授权的用户权限  (wp_user.d1)

示例

 create user root@'10.0.0.%' identified with mysql_native_password by '123';
 grant all on *.* to root@'10.0.0.%';
 create user wp_user@'10.0.0.%' identified with mysql_native_password by '123';
 grant select,update,delete,insert on wordpress.* to  wp_user@'10.0.0.%';
 revoke delete on *.* to wp_user@'10.0.0.%';

8.0 新特性

角色 role

创建角色
 create role app_rw;
给角色授权
 grant select,update,delete,insert on wordpress.* to app_rw;
创建用户
 create user test@'10.0.0.%' identified by '123';
给用户授权
 grant app_rw to test@'10.0.0.%';
查看当前登录用户所属角色
 select * from information_schema.APPLICABLE_ROLES;
删除角色
 drop role app_rw;
删除用户
 drop user test@'10.0.0.%';

 角色 role   MySQL 8.0 中的role支持需求

 oldboy.*  rw(insert,update,delete,select)     --->oldboy_rw
 oldboy.*  r (select)                      --->oldboy_r
 mysql> create role oldboy_rw,oldboy_r;
 mysql> grant select on oldboy.* to oldboy_r;
 mysql> grant select,update,insert,delete  on oldboy.* to oldboy_rw;
 mysql> create user user1@'%' identified by '123';
 mysql> create user user2@'%' identified by '123';
 mysql> grant oldboy_r  to user1@'%';
 mysql> grant oldboy_rw  to user2@'%';

本地管理员( root@localhost 用户)

管理员密码设定（root@localhost）

 [root@db01 ~]# mysqladmin password 1 # 设置密码为: 1

 [root@db01 ~]# mysqladmin -uroot -p password 123 # 修改密码为: 123
 Enter password:  # 输入旧密码 1

本地管理员 root@localhost 用户忘记密码(误删除\误修改)

关闭数据库

 [root@db01 ~]# systemctl stop mysqld

维护模式启动数据库

 [root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &

 ``--skip-grant-tables  # 跳过授权表`
 ``--skip-networking    # 跳过远程登录`

登录并修改密码

 [root@db01 ~]# mysql
 mysql> alter user root@'localhost' identified by '123';
 ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
 mysql> flush privileges;
 mysql> alter user root@'localhost' identified by '123';
 Query OK, 0 rows affected (0.01 sec)
 mysql> exit

如果 root@localhost 用户 (误删除\误修改) 不存在

误修改改回来就行
 [root@db01 ~]# mysql
 mysql> select user,host from mysql.user;
 +------------------+-----------+
 | user             | host      |
 +------------------+-----------+
 | mysql.infoschema | localhost |
 | mysql.session    | localhost |
 | mysql.sys        | localhost |
 | root             | 10.0.0.%  |
 +------------------+-----------+
 4 rows in set (0.00 sec)
 mysql> update mysql.user set host='localhost' where user='root' and host='10.0.0.%';
 mysql> exit
误删除就只能重新建立, 不能使用 CREATE 创建语句, 只能使用 INSERT 插入语句直接编辑用户表

关闭数据库，正常启动验证

 [root@db01 ~]# mysql -uroot -p123

八、连接管理

8.1 自带客户端命令

mysql

mysqldump

① 本地(socket文件)连接

前提条件: 提前创建 localhost 用户和密码

 mysql -uroot -p123 -S /tmp/mysql.sock

② 远程网络连接串(TCP/IP)连接

前提条件: 提前创建远程连接(IP) 用户和密码

 mysql -uroot -p123 -h10.0.0.51 -P3306

开发工具

sqlyog

navicat

workbench(官方开源)

程序(驱动)连接

PHP

python

go

java

九.配置文件

方式

 a.源码包，编译时安装一些参数（CMAKE）
 b.配置文件
 c.命令行指定

如果冲突,命令行优先级最高

配置文件应用

配置文件的默认读取路径

 [root@db01 ~]# mysqld --help --verbose | grep my.cnf | head -1
 /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

注意:

多个文件从左到右依次读取, 如果有重复, 最后读取的生效.

启动服务时, 强制指定读取那个配置文件 --defautls-file
 [root@db01 ~]# mysqld --defaults-file=/tmp/aa.txt &
 [root@db01 ~]# mysqld_safe --defaults-file=/tmp/aa.txt &

`配置文件结构`

 [root@db01 ~]# cat >/etc/my.cnf << EOF
 [mysqld]
 user=mysql                  
 basedir=/usr/local/mysql  
 datadir=/data/3306/data 
 socket=/tmp/mysql.sock
 server_id=6
 port=3306
 [mysql]
 socket=/tmp/mysql.sock
 EOF

 标签:
      服务端: [mysqld] [mysqld_safe] [server]
             影响: 数据库启动,初始化
      客户端: [mysql] [mysqladmin] [mysqldump] [client]
             影响: 只影响到本机客户端程序运行

十、启动关闭

启动

①

 /usr/local/mysql/bin/mysqld &

二进制文件
只有启动没有关闭, 不记录日志

②

/usr/local/mysql/bin/mysqld_safe &

shell脚本
只有启动没有关闭, 记录日志, 监控状态, 异常重启, ... ...

③ /usr/local/mysql/support-files/mysql.server {start|stop|restart|reload|force-reload|status}
④ service mysqld (start, stop, restart, try-restart, reload, force-reload, status)
⑤ systemctl mysqld {start|stop|restart|reload|enable|disable|status}

关闭

① /usr/local/mysql/bin/mysqladmin -uroot -p123 shutdown
②
 [root@db01 ~]#  /usr/local/mysql/bin/mysql -uroot -p123
 mysql> shutdown;
 # 8.0新特性
 mysql> restart;

③ /usr/local/mysql/support-files/mysql.server stop
④ service mysqld stop
⑤ systemctl mysqld stop

--mysqlx=0 # 关闭监听

[root@db01 ~]# netstat -tupln | grep mysqld tcp6 0 0 :::3306 :::* LISTEN 2385/mysqld tcp6 0 0 :::33060 :::* LISTEN 2385/mysqld

单版本

a. 多套目录

 mkdir -p /data/330{7..9}/data 
 chown -R mysql. /data

b. 配置文件

 cat >/data/3307/my.cnf <<EOF
 [mysqld]
 user=mysql
 basedir=/usr/local/mysql 
 datadir=/data/3307/data
 port=3307 
 socket=/tmp/mysql3307.sock 
 EOF
 cat >/data/3308/my.cnf <<EOF
 [mysqld]
 user=mysql
 basedir=/usr/local/mysql 
 datadir=/data/3308/data
 port=3308 
 socket=/tmp/mysql3308.sock 
 EOF
 cat >/data/3309/my.cnf <<EOF
 [mysqld]
 user=mysql
 basedir=/usr/local/mysql 
 datadir=/data/3309/data
 port=3309 
 socket=/tmp/mysql3309.sock 
 EOF

c. 初始化数据

 mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data
 mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3308/data
 mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3309/data

d. 启动数据库

 mysqld_safe --defaults-file=/data/3307/my.cnf &
 mysqld_safe --defaults-file=/data/3308/my.cnf &
 mysqld_safe --defaults-file=/data/3309/my.cnf &

十一、多实例

11.1 单版本单实例

 cat >/etc/systemd/system/mysqld3307.service <<EOF
 [Unit]
 Description=MySQL Server
 Documentation=man:mysqld(8)
 Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
 After=network.target
 After=syslog.target
 [Install]
 WantedBy=multi-user.target
 [Service]
 User=mysql
 Group=mysql
 ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
 LimitNOFILE = 5000
 EOF
 cat >/etc/systemd/system/mysqld3308.service <<EOF
 [Unit]
 Description=MySQL Server
 Documentation=man:mysqld(8)
 Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
 After=network.target
 After=syslog.target
 [Install]
 WantedBy=multi-user.target
 [Service]
 User=mysql
 Group=mysql
 ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
 LimitNOFILE = 5000
 EOF
 cat >/etc/systemd/system/mysqld3309.service <<EOF
 [Unit]
 Description=MySQL Server
 Documentation=man:mysqld(8)
 Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
 After=network.target
 After=syslog.target
 [Install]
 WantedBy=multi-user.target
 [Service]
 User=mysql
 Group=mysql
 ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
 LimitNOFILE = 5000
 EOF
  systemctl start mysqld3307
  systemctl start mysqld3308
  systemctl start mysqld3309
  systemctl enable mysqld3309
   systemctl enable mysqld3308
  systemctl enable mysqld3307
  [root@db01 opt]# netstat -tnupl|grep 330
 tcp6       0      0 :::3306                 :::*                    LISTEN      7393/mysqld         
 tcp6       0      0 :::3307                 :::*                    LISTEN      8087/mysqld         
 tcp6       0      0 :::3308                 :::*                    LISTEN      8135/mysqld         
 tcp6       0      0 :::3309                 :::*                    LISTEN      8174/mysqld

11.2 多版本多实例

a.5.6 和5.7解压和进行软连接

 [root@db01 opt]# ls
 mysql-5.6.46-linux-glibc2.12-x86_64
 mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz
 mysql-5.7.30-linux-glibc2.12-x86_64
 mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
 mysql-8.0.20-linux-glibc2.12-x86_64
 mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
 #软连接
 ln -s  /opt/mysql-5.6.46-linux-glibc2.12-x86_64  /usr/local/mysql56
 ln -s  /opt/mysql-5.7.30-linux-glibc2.12-x86_64  /usr/local/mysql57

b.创建目录并授权

 mkdir -p /data/331{6..7}/data 
 chown -R mysql. /data

c.配置文件

 cat >/data/3316/my.cnf <<EOF
 [mysqld]
 user=mysql
 basedir=/usr/local/mysql56 
 datadir=/data/3316/data
 port=3316 
 socket=/tmp/mysql3316.sock 
 EOF
 
 cat >/data/3317/my.cnf <<EOF
 [mysqld]
 user=mysql
 basedir=/usr/local/mysql57 
 datadir=/data/3317/data
 port=3317 
 socket=/tmp/mysql3317.sock 
 EOF

d.初始化数据

 /usr/local/mysql57/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/3317/data
 /usr/local/mysql56/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql56  --datadir=/data/3316/data

e.启动

 [root@db01 opt]# /usr/local/mysql56/bin/mysqld_safe --defaults-file=/data/3316/my.cnf &
 [root@db01 opt]# /usr/local/mysql57/bin/mysqld_safe --defaults-file=/data/3317/my.cnf &

f.连接

 [root@db01 opt]# mysql -S /tmp/mysql3307.sock
 [root@db01 opt]# mysql -S /tmp/mysql3316.sock
 [root@db01 opt]# mysql -S /tmp/mysql3317.sock

十二、升级

12.1 升级方式

a.inplace(就地升级)

适合于有主从环境。

b.merging（逻辑备份迁移升级）

12.2升级注意事项（INPLACE)

网址：https://dev.mysql.com/doc/refman/8.0/en/upgrade-paths.html

 来自于MySQL官网
 Upgrade is only supported between General Availability (GA) releases.
 Upgrade from MySQL 5.6 to 5.7 is supported. Upgrading to the latest release is recommended before upgrading to the next version. For example, upgrade to the latest MySQL 5.6 release before upgrading to MySQL 5.7.
 Upgrade that skips versions is not supported. For example, upgrading directly from MySQL 5.5 to 5.7 is not supported.
 Upgrade within a release series is supported. For example, upgrading from MySQL 5.7.x to 5.7.y is supported. Skipping a release is also supported. For example, upgrading from MySQL 5.7.x to 5.7.z is supported.
 翻译:
 a. 支持GA版本之间升级
 b. 5.6--> 5.7  ,先将5.6升级至最新版，再升级到5.7
 c. 5.5 ---> 5.7 ,先将5.5 升级至最新，再5.5---> 5.6最新，再5.6--->5.7 最新
 d. 回退方案要提前考虑好，最好升级前要备份(特别是往8.0版本升级)。
 e. 降低停机时间（停业务的时间)

12.3`INPLACE升级过程原理`

 0. 备份原数据库数据
 a. 安装新版本软件
 b. 关闭原数据库（挂维护页）
 c. 使用新版本软件 “挂” 旧版本数据启动(--skip-grant-tables ,--skip-networking)
 d. 升级 ： 只是升级系统表。升级时间和数据量无关的。
 e. 正常重启数据库。
 f. 验证各项功能是否正常。
 g. 业务恢复。

`5.6.48 ----> 5.7.30 Inplace 升级演练`

a. 安装新版本软件 5.7.30 ok。

b.停源库,做冷备

# 1. 快速关库功能关闭(优雅关闭) 连接到数据库中（5.6.48），执行以下语句。

 mysql -S /tmp/mysql3316.sock -e "set global innodb_fast_shutdown=0 ;"

查询：

 [root@db01 ~]# mysql -S /tmp/mysql3316.sock -e "select @@innodb_fast_shutdown ;"
 +------------------------+
 | @@innodb_fast_shutdown |
 +------------------------+
 |                      0 |
 +------------------------+
 [root@db01 data]# /usr/local/mysql56/bin/mysqladmin -S /tmp/mysql3316.sock shutdown
 201103 15:11:10 mysqld_safe mysqld from pid file /data/3316/data/db01.pid ended
 [1]-  Done                    /usr/local/mysql56/bin/mysqld_safe --defaults-file=/data/3316/my.cnf  (wd: /opt)
 (wd now: /data/3316/data)

冷备:

 [root@db01 ~]# cp -r /data/3316/data/ /tmp/bak

c.使用高版本软件（5.7.30）挂低版本（5.6.48）数据启动

 [root@db01 data]# cat > /data/3316/my.cnf <<EOF 
 [mysqld]
 user=mysql
 basedir=/usr/local/mysql57
 datadir=/data/3316/data
 socket=/tmp/mysql3316.sock
 port=3316
 innodb_fast_shutdown=0
 EOF
 [root@db01 data]# /usr/local/mysql57/bin/mysqld_safe --defaults-file=/data/3316/my.cnf --skip-grant-tables --skip-networking &

d. 升级（升级到8.0可以省略）

 [root@db01 data]# /usr/local/mysql57/bin/mysql_upgrade -S /tmp/mysql3316.sock --force

e. 重启数据库到正常状态

[root@db01 data]# /usr/local/mysql57/bin/mysqladmin -S /tmp/mysql3316.sock shutdown
 [root@db01 data]# /usr/local/mysql57/bin/mysqld_safe --defaults-file=/data/3316/my.cnf &

f.连接查看

 [root@db01 data]# mysql -S /tmp/mysql3316.sock

`Mysql5.7.30Inplace升级到MySQL8.0.20`

图形网址:https://www.processon.com/view/link/5fa0c8ffe401fd4885495a93

a. 安装mysqlsh

 [root@db01 opt]# tar xf mysql-shell-8.0.20-linux-glibc2.12-x86-64bit.tar.gz 
 [root@db01 opt]# ln -s /opt/mysql-shell-8.0.20-linux-glibc2.12-x86-64bit /usr/local/mysqlsh
 [root@db01 opt]# cd /usr/local/mysqlsh/
 [root@db01 opt]#echo export PATH=\$PATH:/usr/local/mysqlsh/bin/ >> /etc/profile && . /etc/profile
 [root@db01 bin]# mysqlsh --version
 mysqlsh  Ver 8.0.20 for Linux on x86_64 - for MySQL 8.0.20 (MySQL Community Server (GPL))

b. 在5730数据库中创建链接用户

 [root@db01 bin]# mysql -S /tmp/mysql3317.sock
 mysql> create user root@'10.0.0.%' identified with mysql_native_password by '123';
 mysql> grant all on *.* to root@'10.0.0.%';

c. 升级前检测

 [root@db01 ~]# mysqlsh root:123@10.0.0.51:3317 -e "util.checkForServerUpgrade()" >>/tmp/up.log

7.5.2 正式升级 a. 安装新版本软件 8.0.20 ok

b. 停源库

# 1. 快速关库功能关闭(优雅关闭)

连接到数据库中（5.7.30），执行以下语句。

 [root@db01 bin]# mysql -S /tmp/mysql3317.sock
 mysql> set global innodb_fast_shutdown=0 ;
 mysql> select @@innodb_fast_shutdown;
 mysql> shutdown ;

c. 使用高版本软件（8.0.20）挂低版本（5.7.30）数据启动

 [root@db01 data]# cat > /data/3317/my.cnf <<EOF 
 [mysqld]
 user=mysql
 basedir=/usr/local/mysql
 datadir=/data/3317/data
 socket=/tmp/mysql3317.sock
 port=3317
 EOF
 [root@db01 data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3317/my.cnf --skip-grant-tables --skip-networking &

d. 重启数据库到正常状态

 [root@db01 data]# mysqladmin -S /tmp/mysql3317.sock shutdown
 [root@db01 data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3317/my.cnf &

f.连接查看

 [root@db01 data]# mysql -S /tmp/mysql3317.sock

第十三章体系结构

MySQL C/S结构 Clinet : mysql mysqldump ,sqlyog,API Server : mysqld守护进程

实例(instance) 实例: mysqld + 线程(Master thread , IO ,SQL ,purge...) + 预分配内存(数据\日志\线程) 公司: boss + 员工(总经理+普通员工) + 办公室
MySQL服务的构成

Server

a. 连接层

 (1)提供连接协议
         Socket  
         TCPIP
 (2)验证用户名（root@localhost）密码合法性，进行匹配专门的授权表。
 
 (3)派生一个专用连接线程（接收SQL，返回结果）
       mysql> show processlist;
 思考： 
 忘记密码的参数在哪做的手脚？
 --skip-grant-tables  
 --skip-networking

b. SQL 层

 （1）验证SQL语法和SQL_MODE
 （2）验证语义
 （3）验证权限
 （4）解析器进行语句解析，生成执行计划（解析树）
 （5）优化器（各种算法，基于执行代价），根据算法，找到代价最低的执行计划。
     代价：CPU  IO  MEM
 （6）执行器按照优化器选择执行计划，执行SQL语句，得出获取数据的方法。
 （7）提供query cache(默认不开)，一般不开，会用redis
 （8）记录操作日志（binlog），默认没开

3.2 engine 存储引擎层

 真正和磁盘打交道的一个层次
 根据SQL层提供的取数据的方法，拿到数据，返回给SQL，结构化成表，再又连接层线程返回给用户。

4.对象存储结构和逻辑结构的对比

逻辑结构：

库：库名,库属性（字符集，校对规则）

 库               =========Linux目录
 create database wordpress charset utf8mb4;     ======mkdir /wordpress
 show databases; =======ls /
 use wordpress;   =======cd /wordpress

存储结构：

 库： 使用FS上的目录来表示 
 表： 
 MyISAM(ext2)
  user.frm  ： 存储的表结构（列，列属性）
  user.MYD  :  存储的数据记录
  user.MYI  ： 存储索引
 
 InnoDB(XFS)
  time_zone.frm ： 存储的表结构（列，列属性）
  time_zone.ibd ： 存储的数据记录和索引
  ibdata1       :   数据字典信息

innodb 段区页

 一般情况下（非分区表）
 一个表就是一个段
 一个段由多个区构成
 `一个区在（16k），64个连续的页，1M大小`

MySQL内置命令

 help                    打印mysql帮助
 \c  ctrl+c              结束上个命令运行
 \q quit; exit; ctrl+d   退出mysql
 \G                      将数据竖起来显示
 source                  恢复备份文件

SQL基础

名词认识

sql_mode

https://mariadb.com/kb/en/library/sql-mode/

作用：使数据准确, 符合常识

5.7 版本之后默认启用SQL92严格模式，通过sql_mode参数来控制

查看

 mysql> select @@sql_mode;
 +-----------------------------------------------------------------------------------------------------------------------+
 | @@sql_mode                                                                                                            |
 +-----------------------------------------------------------------------------------------------------------------------+
 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
 +-----------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)
 SQL_MODE：对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置，参看：
 常见MODE:
 1、NO_AUTO_CREATE_USER
 禁止GRANT创建密码为空的用户
 2、 NO_ZERO_DATE
 在严格模式，不允许使用‘0000-00-00’的时间
 3、ONLY_FULL_GROUP_BY
 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的
 4、NO_BACKSLASH_ESCAPES
 反斜杠“\”作为普通字符而非转义字符
 5、PIPES_AS_CONCAT
 将"||"视为连接操作符而非“或运算符”

字符集

作用：字符转换

查看支持的所有字符集

 mysql> show charset;

8.0 默认：utf8mb4 8.0 之前默认：latin1

面试问题：

utf8：最大存储3字节字符, 一个中文占3字节, 一个数据/字母/特殊符号占1字节

utf8mb4：最大存储4字节字符, 一个中文占3字节, 一个数据/字母/特殊符号占1字节, 可以存储emoji

校对规则

校对规则 = 排序规则 = collation

作用：影响排序

1.3.2 查询

 mysql> show collation;

 +----------------------------+----------+-----+---------+----------+---------+---------------+
 | Collation                  | Charset  | Id  | Default | Compiled | Sortlen | Pad_attribute |
 +----------------------------+----------+-----+---------+----------+---------+---------------+
 ... ...
 | utf8mb4_0900_ai_ci         | utf8mb4  | 255 | Yes     | Yes      |       0 | NO PAD        |
 | utf8mb4_0900_as_cs         | utf8mb4  | 278 |         | Yes      |       0 | NO PAD        |
 ... ...

*_ci 大小写不敏感

*_cs 大小写敏感

列属性

约束(一般建表时添加)

PK：primary key ：主键约束列值非空且唯一，主键在一个表中有且只有一个，但是可以有多个列一起构成。

NN：not null ：非空约束列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0。

UK：unique key ：唯一键列值不能重复。

FK：

建议：

每张表设置主键，建议是数字自增列

尽量对每个列设置非空

其他属性

default：默认值列中没有录入值时，会自动使用default的值填充。

auto_increment: 自增长针对数字列(常用于主键)，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）。

unsigned ：无符号针对数字列，非负数。

comment：注释

key：索引可以在某列上建立索引，来优化查询，一般是根据需要后添加。

数据类型

作用：控制存储数据的"格式"和规范，保证数据的准确性和标准性。

种类：

数值类型
字符类型
时间类型
二进制类型
JSON

数值类型

类	类型	说明
整数	TINYINT	极小整数数据类型(0~255)
整数	SMALLINT	较小整数数据类型(-2^15~2^15-1)
整数	MEDIUMINT	中型整数数据类型
整数	INT	常规(平均)大小的整数数据类型(-2^31~2^31-1)
整数	BIGINT	较大整数数据类型(-2^63~2^63-1)
浮点数	FLOAT	小型单精度(4字节)浮点数
浮点数	DOUBLE	常规单精度(8字节)浮点数
定点数	DECIMAL	具有小数点而且数值确定的数值
BIT	BIT	位字段值

面试题 区别

 tinyint ：1bytes ：0~255    ：-128~127     ：3位
 int     ：4bytes ：0~2^32-1 ：-2^31~2^31-1 ：10位
 bigint  ：8bytes ：0~2^64-1 ：-2^63~2^63-1 ：20位
 说明：手机号是11的，再加区号更长，一般是使用char类型。

浮点数你们公司怎么存储的?

金钱(精度要求高)有关的decimal

精度要求不高的，放到N倍，用整数类型，查询性能好

字符类型

类	类型	说明
文本	CHAR	固定长度字符串，最多为255个字符
文本	VARCHAR	可变长度字符串，最多为65,535个字符
文本	TINYTEXT	可变长度字符串，最多为255个字符
文本	TEXT	可变长度字符串，最多为65,535个字符
文本	MEDIUMTEXT	可变长度字符串，最多为16,777,215个字符
文本	LONGTEXT	可变长度字符串，最多为4,294,967,295个字符
整数	ENUM	由一组固定的合法值组成的枚举
整数	SET	由一组固定的合法值组成的集

 面试题 : char(10) 和 varchar(10) 区别
   共同点:最大字符长度10个
   不同点:定长的字符串类型。在存储字符串时，立即分配10个字符长度的存储空间，如果存不满，空格填充。
 varchar(10):
   共同点:最大字符长度10个
   不同点:变长的字符串类型。在存储字符串时，自动判断字符长度，按需分配存储空间，额外占用1-2bytes存储字符长度。
 enum('bj','tj','sh')：
 枚举类型，适合于存储固定的值，可以很大程度的优化我们的索引结构。例如：城市, 性别。

如果存储变长字符串，一般建议使用varchar，例如：人名。

时间类型

类型	格式	示例
DATE	YYYY-MM-DD	2020
TIME	hh:mm:ss[.uuuuuu]	00:00:00.000000
DATETIME	YYYY-MM-DD hh:mm:ss[.uuuuuu]	1000-01-01 00:00:00.000000
TIMESTAMP	YYYY-MM-DD hh:mm:ss[.uuuuuu]	1970-01-01 00:00:00.000000
YEAR	YYYY	2020

 DATETIME    8字节
 范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
 TIMESTAMP   4字节   会受到时区的影响
 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。

二进制类型

类	类型	说明
二进制	BINARY	类似于CHAR(固定长度)类型，但存储的是二进制字节字符串，而不是非二进制字符串
二进制	VARBINARY	类似于VARCHAR(可变长度)类型，但存储的是二进制字节字符串，而不是非二进制字符串
BLOB	TINYBLOB	最大长度为255个字节的BLOB列
BLOB	BLOB	最大长度为65,535个字节的BLOB列
BLOB	MEDIUDMBLOB	最大长度为16,777,215个字节的BLOB列
BLOB	LONGBLOB	最大长度为4,294,967,295个字节的BLOB列

JSON

5.7.8 版本新增官方文档

类似 varchar，不能设置长度，可以是 NULL ，但不能有默认值。

 约束 
     PK   primary key   非空且唯一
     NN   not null      非空 
     UK   unique key    唯一 
     FK
 建议:  
         1. 每张表设置主键,建议是数字自增列 
         2. 尽量对每个列设置非空
      其他属性   
     default        默认值
     auto_increment 自增长(主键)
     unsigned       数字列,无符号(非负数)
     comment        注释

`SQL介绍（面试题）`

简介

结构化查询语言，在RMBMS中通用的一类语言，符号SQL89 SQL92 SQL99 等国际标准。

分类

 `DDL：数据定义语言`
 `DCL：数据控制语言`
 `DML：数据操作语言`
 `DQL：数据查询语言`

`DDL(数据定义语言)`

库定义

创建

 -- 创建一个数据库
 CREATE DATABASE wordpress;
 -- 创建一个数据库并指定字符集
 CREATE DATABASE wordpress CHARSET utf8;
 -- 创建一个数据库并指定字符集和排序规则
 CREATE DATABASE wordpress CHARSET utf8 COLLATE utf8_bin;
 -- 创建模式(数据库对象的集合)在MySQL中等同于创建数据库
 CREATE SCHEMA wordpress;

删除(生产中禁止使用)

 DROP DATABASE wordpress;

修改

 ALTER DATABASE wordpress CHARSET utf8mb4;

注意：修改字符集，修改后的字符集一定是原字符集的严格超集，只影响之后存入的数据，修改前存入的数据还是原字符集编码方式，可能乱码

查询库相关信息（DQL）

 -- 查看所有数据库名
 SHOW DATABASES;
 -- 查看建库标准语句
 SHOW CREATE DATABASE wordpress;
 -- 查看字符集
 SHOW CHARSET;
 -- 查看排序规则
 SHOW COLLATION;

5.规范:

a. 禁止线上业务系统出现DROP操作. b. 库名: 不能大写字母,不能是关键字,不能使数字开头. c. 显式的设置字符集.

表定义

DDL规范

库规范

禁止线上业务系统出现DROP操作
库名：不能有大写字母(Linux区分大小写)，不能是关键字，不能有数字开头，要和业务相关
建库建库是显示的设置字符集

表规范

表名小写，不能有数字开头和大写字母，要和业务相关，建议格式：wp_user
显式的设置存储引擎和字符集和表的注释
每个表必须要有主键，是数字的自增的无关的
列名要和业务相关
每个列要有注释，设置为非空，无法保证非空，设置默认值0来填充。
列的数据类型，讲究：完整简短合适
变长列一般选择varchar类型，定长列一般选择char类型
精度不高的浮点数，放大N倍到整数
大字段，可以选择附件形式或ES数据库
8.0版本后，Online-DDL除了追加列，添加删除索引外，其他操作，请在数据库低谷时间点去做，如果很紧急，请使用pt-osc或者gh-ost

面试题：

https://www.jianshu.com/p/eba3a5541c4c

DCL

数据控制语言，权限管理

 grant
 revoke

DML

作用：对表中的数据行进行增、删、改

INSERT

 -- INSERT INTO 表名(列名,列名...) VALUES (值,值...),(值,值...);
 -- 标准
 INSERT INTO stu(id,sn,age,gender,addr,intime) 
 VALUES (1,'zs',18,'M','bj',NOW());
 -- 简写
 INSERT INTO stu 
 VALUES (1,'zs',18,'M','bj',NOW());
 -- 针对性的录入数据
 INSERT INTO stu(sn,addr)
 VALUES ('w1','bj');
 -- 同时录入多行数据
 INSERT INTO stu(sn,addr)
 VALUES 
 ('w1','bj'),
 ('w2','bj'),
 ('w3','bj');
 -- 查看表所有内容
 SELECT * FROM stu;

UPDATE

 -- 更新指定行
 UPDATE stu SET sn='w4' WHERE id=2;
 -- 更新所有行
 UPDATE stu SET sn='w4';

注意：生成应用中，UPDATE必须加WHERE条件!

防止误更新，配置UPDATE不加WHERE条件报错

立即生效，重启失效

 mysql> show variables like '%safe%';
 +----------------------------------+-------+
 | Variable_name                    | Value |
 +----------------------------------+-------+
 | log_statements_unsafe_for_binlog | ON    |
 | sql_safe_updates                 | OFF   |
 +----------------------------------+-------+
 2 rows in set (0.00 sec)
 
 mysql> set global sql_safe_updates=1;
 Query OK, 0 rows affected (0.00 sec)
 mysql> exit
 [root@db01 mysqlsh]# mysql -uroot -p123
 mysql> show variables like '%safe%';
 +----------------------------------+-------+
 | Variable_name                    | Value |
 +----------------------------------+-------+
 | log_statements_unsafe_for_binlog | ON    |
 | sql_safe_updates                 | ON    |
 +----------------------------------+-------+
 2 rows in set (0.00 sec)
 
 mysql> use test;
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A
 
 Database changed
 
 mysql> UPDATE stu SET sname='w4';
 ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

    永久生效

 sed -i '/\[mysqld\]/a sql_safe_updates=1' /etc/my.cnf

DELETE(生产中禁止使用)

 -- 删除指定行
 DELETE FROM stu WHERE id=3;
 -- 删除所有行
 DELETE FROM stu;
 -- 删除表
 DELETE FROM stu;
 TRUNCATE table stu;
 Drop table stu;

删除表区别（面试题）

操作	范围	表现	介绍
DELETE FROM stu;	全表数据	新插入行自增id断节	DML操作，逻辑性质删除，逐行"删除"(只是打一个标记)表中每行数据，速度慢，不会立即释放磁盘，HWM(高水位线)没有降低
TRUNCATE table stu;	全表数据	新插入行自增id从1开始	DDL操作，整表所有数据全部删除，清空数据页，立即释放磁盘空间，速度快
Drop table stu;	全表数据 + 表定义	表没了	立即释放磁盘空间

伪删除：用update来替代delete(面试题)

最终保证业务中查（select）不到即可

 -- 1. 添加状态列
 ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1为存在;0为不存在';
 -- 2. 使用 UPDATE 替代 DELETE
 UPDATE stu SET state=0 WHERE id=1;
 -- 3. 业务查询语句
 SELECT * FROM stu WHERE state=1;

DQL应用(select )

select子句顺序

子句	说明	是否必须使用
select	要返回的列或表示式	是
from	从中检索数据的表	仅在从表选择数据时使用
where	行级过滤	否
group by	分组说明	仅在按组计算聚集时使用
having	组级过滤	否
order by	输出排序顺序	否
limit	要检索的行数	否

单独使用

 -- select @@xxx 查看系统参数
 SELECT @@port;
 SELECT @@basedir;
 SELECT @@datadir;
 SELECT @@socket;
 SELECT @@server_id;
 mysql> show variables like '%trx%';
 mysql> show variables ;

-- select 函数();

 SELECT NOW();
 SELECT DATABASE();
 SELECT USER();
 SELECT CONCAT("hello world");
 SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
 SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;
 https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg

FROM

 SELECT 列1,列2 FROM 表
 SELECT  *  FROM 表

例子: -- 查询stu中所有的数据(不要对大表进行操作)

 SELECT * FROM stu ;

-- 查询stu表中,学生姓名和入学时间

 SELECT sname , intime FROM stu;

 world            ===>世界
 city             ===>城市
 country          ===>国家
 countrylanguage  ===>国家语言
 
 city:城市表
 DESC city;
 ID :         城市ID
 NAME :       城市名
 CountryCode: 国家代码，比如中国CHN 美国USA
 District :   区域
 Population : 人口
 
 SHOW CREATE TABLE city;
 SELECT * FROM city WHERE id<10;

WHERE

 SELECT col1,col2 FROM TABLE WHERE colN 条件;

等值查询

例子: -- 查询中国(CHN)所有城市信息

 SELECT * FROM city WHERE countrycode='CHN';

-- 查询北京市的信息

 SELECT * FROM city WHERE NAME='peking';

-- 查询甘肃省所有城市信息

 SELECT * FROM city WHERE district='gansu';

比较操作符(> < >= <= <>)

例子: -- 查询世界上少于100人的城市

 SELECT * FROM city WHERE population<100;

逻辑运算符(and or )

例子: -- 中国人口数量大于500w

 SELECT * FROM city WHERE countrycode='CHN' AND population>5000000;

-- 中国或美国城市信息

 SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';

like字句模糊查询

例子: -- 查询省的名字前面带guang开头的

 SELECT * FROM city WHERE district LIKE 'guang%';    
 注意:%不能放在前面,因为不走索引.
 如果业务中有大量需求,我们用"ES"来替代
 注意：
 `like`相当于`=`，但支持模糊匹配
 `%`不能放在最前面，因为不走索引
  `%`任意个任意字符
  `_`单个任意字符

IN

-- 中国或美国城市信息

 SELECT * FROM city WHERE countrycode IN ('CHN' ,'USA');

BETWEEN AND

例子: -- 查询世界上人口数量大于100w小于200w的城市信息

 SELECT * FROM city  WHERE population >1000000 AND population <2000000;
 SELECT * FROM city  WHERE population BETWEEN 1000000 AND 2000000;

IN 和 NOT IN

 -- where in
 -- 查询中国或美国的城市信息

 SELECT * FROM city WHERE `CountryCode` IN ('CHN' ,'USA');
 SELECT * FROM city WHERE `CountryCode`='CHN' OR `CountryCode`='USA';

 -- where not in
 -- 查询除了中国或美国外的其他所有城市信息
 SELECT * FROM city WHERE `CountryCode` NOT IN ('CHN' ,'USA');

NOT IN 不走索引

select+from+where+group by+聚合函数

group by + 常用聚合函数

作用

 根据 固定条件进行分组

 先分组(GROUP BY)，后去重统计(count())，类似于shell的`cat FILENAME | sort | uniq -c`
  注意：
 1. GROUP BY 必须在 where 之后，ORDER BY 之前
 2. GROUP BY 后面条件尽量使用主键，防止列值重复
 3. GROUP BY 多表连接查询可同时使用多表主键，防止列值重复

常用聚合函数

 max()     ：最大值
 min()     ：最小值
 avg()      ：平均值
 sum()      ：总和
 count()    ：个数
 group_concat() : 列转行

sql_mode

ONLY_FULL_GROUP_BY

mysql> select countrycode ,sum(population) from world.city group by countrycode;

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Population' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

如果有group by, select 后的列,要么是group by条件,要么是在聚合函数里处理.

例子：

例1：统计世界上每个国家的总人口数.

 USE world(xshell虚拟机使用)
 SELECT countrycode ,SUM(population)    FROM  city  GROUP BY countrycode;

例2：统计中国各个省的总人口数量(练习)

 SELECT district,SUM(Population) FROM city  WHERE countrycode='chn' GROUP BY district;

例3：统计世界上每个国家的城市数量(练习)

 SELECT countrycode,COUNT(id)  FROM city GROUP BY countrycode;

例四:统计中国每个省城市的个数

 SELECT district, COUNT(NAME)     FROM  city 
 WHERE countrycode='CHN'
 GROUP BY district;

例五：统计中国每个省 :城市个数,城市名列表.

 SELECT district, COUNT(*),GROUP_CONCAT(NAME) FROM  city 
 WHERE countrycode='CHN'
 GROUP BY district;
 #统计大于10的城市数
 HAVING  COUNT(*) >10;

注:district相当于awk取值

HAVING

作用：在 GROUP BY 后条件判断

 尽量少用，不走索引，必须使用时，请在业务低谷期或从库或拿出来使用其他方式判断

 where|group|having

例子：

 -- 统计中国每个省的城市数，只打印城市数大于10的省

 SELECT district,COUNT(id) FROM city WHERE countrycode='CHN' 
 GROUP BY district HAVING COUNT(id) > 10;

ORDER BY

作用

 实现先排序，by后添加条件列

应用案例

查看中国所有的城市，并按人口数进行排序(从大到小)

 SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;

统计中国各个省的总人口数量，按照总人口从大到小排序

 SELECT district AS 省 ,SUM(Population) AS 总人口
 FROM city
 WHERE countrycode='chn'
 GROUP BY district
 ORDER BY 总人口 DESC ;

ASC 和 DESC

 -- 查看中国所有城市信息，并按人口数从大到小进行排序
 SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;
 -- 查看世界上所有城市的名称，代码，人口数，并按城市代码从小到大进行排序，如果城市代码相同则按人口数从大到小排序
 SELECT `Name`,`CountryCode`,population 
 FROM city
 ORDER BY `CountryCode` ASC, `Population` DESC;

LIMIT

作用：显示指定行

统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名

 SELECT  district, SUM(population)  FROM  city 
 WHERE countrycode='CHN'
 GROUP BY district 
 HAVING SUM(population)>5000000
 ORDER BY SUM(population) DESC
 LIMIT 3 ;

LIMIT N ,M --->跳过N,显示一共M行 LIMIT 5,5

 SELECT  district, SUM(population)  FROM  city 
 WHERE countrycode='CHN'
 GROUP BY district 
 HAVING SUM(population)>5000000
 ORDER BY SUM(population) DESC
 LIMIT 2(跳过两行),5;
 
 SELECT  district, SUM(population)  FROM  city 
 WHERE countrycode='CHN'
 GROUP BY district 
 HAVING SUM(population)>5000000
 ORDER BY SUM(population) DESC
 LIMIT 5,OFFSET 2(跳过两行);

OFFSET

作用：指定行数偏移

 -- LIMIT N,M 
 -- LIMIT N OFFSET M;
 -- 跳过 N ,显示一共 M 行
 -- 统计中国每个省的总人口数，找出总人口大于500w的，并按总人口从大到小排序，显示6-10名

 SELECT district, SUM(population)  FROM  city 
 WHERE countrycode='CHN'
 GROUP BY district 
 HAVING SUM(population)>5000000
 ORDER BY SUM(population) DESC
 LIMIT 5,5;

DISTINCT

作用：去重

 SELECT countrycode FROM city ;
 SELECT DISTINCT(countrycode) FROM city  ;

UNION 和 UNION ALL

作用：联合查询

UNION 去重
UNION ALL 不去重

 -- 中国或美国城市信息

 SELECT * FROM city 
 WHERE countrycode IN ('CHN' ,'USA');
 
 SELECT * FROM city WHERE countrycode='CHN'
 UNION ALL
 SELECT * FROM city WHERE countrycode='USA'


 说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能

面试题：UNION UNION ALL区别？

 UNION     去重复
 UNION ALL 不去重复

 (SELECT  district, SUM(population)  FROM  city 
 WHERE countrycode='CHN'
 GROUP BY district 
 HAVING SUM(population)>5000000
 ORDER BY SUM(population) DESC
 LIMIT 5)
 UNION 
 (SELECT  district, SUM(population)  FROM  city 
 WHERE countrycode='CHN'
 GROUP BY district 
 HAVING SUM(population)>5000000
 ORDER BY SUM(population) DESC
 LIMIT 5)

join 多表连接查询

a.笛卡尔乘积

 mysql> select * from a,b

b.内连接

mysql> select * from a,join b on a.id = b.aid;
c.外连接

左外连接
```
 左外连接：left outer join
 语句：select * from a_table a left join b_table bon a.a_id = b.b_id;
```
mysql> select * from a left join b on a.id=b.aid;
mysql> select * from a right join b on a.id=b.aid;
mysql> select * from a left join b on a.id=b.aid and b.aid is null;

右外连接

 右外连接：right outer join
 select * from A right outer join B on 条件;
 语句：select * from a_table a right outer join b_table b on a.a_id = b.b_id;

案例准备

按需求创建一下表结构:

use school
 student ：学生表  nn
 sno：    学号
 sname：学生姓名
 sage： 学生年龄
 ssex： 学生性别
 teacher ：教师表
 tno：     教师编号
 tname：   教师名字
 course ：课程表
 cno：  课程编号
 cname：课程名字
 tno：  教师编号
 score  ：成绩表
 sno：  学号
 cno：  课程编号
 score：成绩
 -- 项目构建
 学生表
 CREATE DATABASE school CHARSET utf8;
 CREATE TABLE student(
 sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
 sname VARCHAR(20) NOT NULL COMMENT '姓名',
 sage TINYINT UNSIGNED  NOT NULL COMMENT '年龄',
 ssex  ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
 )ENGINE=INNODB CHARSET=utf8;
 课程表
 CREATE TABLE course(
 cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
 cname VARCHAR(20) NOT NULL COMMENT '课程名字',
 tno INT NOT NULL  COMMENT '教师编号'
 )ENGINE=INNODB CHARSET utf8;
 成绩表
 CREATE TABLE sc (
 sno INT NOT NULL COMMENT '学号',
 cno INT NOT NULL COMMENT '课程编号',
 score INT  NOT NULL DEFAULT 0 COMMENT '成绩'
 )ENGINE=INNODB CHARSET=utf8;
 教师表
 CREATE TABLE teacher(
 tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
 tname VARCHAR(20) NOT NULL COMMENT '教师名字'
 )ENGINE=INNODB CHARSET utf8;
 INSERT INTO student(sno,sname,sage,ssex)
 VALUES (1,'zhang3',18,'m');
 INSERT INTO student(sno,sname,sage,ssex)
 VALUES
 (2,'zhang4',18,'m'),
 (3,'li4',18,'m'),
 (4,'wang5',19,'f');
 INSERT INTO student
 VALUES
 (5,'zh4',18,'m'),
 (6,'zhao4',18,'m'),
 (7,'ma6',19,'f');
 INSERT INTO student(sname,sage,ssex)
 VALUES
 ('oldboy',20,'m'),
 ('oldgirl',20,'f'),
 ('oldp',25,'m');
 INSERT INTO teacher(tno,tname) VALUES
 (101,'oldboy'),
 (102,'hesw'),
 (103,'oldguo');
 DESC course;
 INSERT INTO course(cno,cname,tno)
 VALUES
 (1001,'linux',101),
 (1002,'python',102),
 (1003,'mysql',103);
 DESC sc;
 INSERT INTO sc(sno,cno,score)
 VALUES
 (1,1001,80),
 (1,1002,59),
 (2,1002,90),
 (2,1003,100),
 (3,1001,99),
 (3,1003,40),
 (4,1001,79),
 (4,1002,61),
 (4,1003,99),
 (5,1003,40),
 (6,1001,89),
 (6,1003,77),
 (7,1001,67),
 (7,1003,82),
 (8,1001,70),
 (9,1003,80),
 (10,1003,96);
 
 SELECT * FROM student;
 SELECT * FROM teacher;
 SELECT * FROM course;
 SELECT * FROM sc;

语法

查询张三的家庭住址

 SELECT A.name,B.address FROM
 A JOIN  B
 ON A.id=B.id
 WHERE A.name='zhangsan'

例子：

查询一下世界上人口数量小于100人的城市名和国家名

 SELECT b.name ,a.name ,a.population
 FROM  city  AS a
 JOIN  country AS b
 ON    b.code=a.countrycode
 WHERE  a.Population<100

查询城市shenyang，城市人口，所在国家名（name）及国土面积（SurfaceArea）

 SELECT a.name,a.population,b.name ,b.SurfaceArea
 FROM city  AS a JOIN country AS b
 ON a.countrycode=b.code
 WHERE a.name='shenyang';

AS别名

a.列别名

 　　SELECT
 　　 [column_1 | expression] [AS] descriptive_name #要给列添加别名，可以使用AS关键词后跟别名。
 　　FROM table_name;
 　　如果别名包含空格，则必须引用：
 　　SELECT
 　　 [column_1 | expression] [AS] 'descriptive name' #因为AS关键字是可选的，可以在语句中省略它。
 　　FROM table_name;
 查询选择员工的名字和姓氏，并将其组合起来生成全名。 CONCAT_WS函数用于连接名字和姓氏。
 SELECT
 CONCAT_WS(', ', lastName, firstname) [AS] 'Full name'
 FROM
 employees;
 在MySQL中，可以使用ORDER BY，GROUP BY和HAVING子句中的列别名来引用该列。
 以下查询使用ORDER BY子句中的列别名按字母顺序排列员工的全名：

 SELECT
 CONCAT_WS(' ', lastName, firstname) [as] 'Full name'
 FROM
 employees
 ORDER BY   
 'Full name';

b.表别名

 可以使用别名为表添加不同的名称。使用AS关键字在表名称分配别名，如下查询语句语法：
 table_name [AS] table_alias
 两个表都具有相同的列名称：customerNumber。如果不使用表别名来指定是哪个表中的customerNumber列：

 SELECT
 customerName,
 COUNT(o.orderNumber) [as] total #列别名
 FROM
 customers [as] c INNER JOIN orders [as] o #表别名
 ON c.customerNumber = o.customerNumber
 GROUP BY
 customerName
 HAVING total >=5
 ORDER BY total DESC;

 -- 查询世界上人口数量小于100人的城市名和国家名

 SELECT b.name ,a.name ,a.population
 FROM city AS a
 JOIN country AS b
 ON b.code=a.countrycode
 WHERE a.Population<100;


 -- 查询城市shenyang，城市人口，所在国家名（name）及国土面积（SurfaceArea）

 SELECT a.name AS cityname,
 a.population AS pp,
 b.name AS country,
 b.SurfaceArea AS sa
 FROM city AS a 
 JOIN country AS b
 ON a.countrycode=b.code
 WHERE a.name='shenyang';

CASE

简单函数：

枚举case_expression字段所有可能的值。

 CASE [case_expression] WHEN [when_expression_1] THEN [commands]… ELSE [default] END

case_expression可以是任何有效的表达式。

将case_expression的值与每个WHEN子句中的when_expression进行比较，例如when_expression_1，when_expression_2等。如果case_expression和when_expression_n的值相等，则执行相应的WHEN分支中THEN的命令commands，否则ELSE子句中的命令将被执行。如果省略ELSE子句，并且找不到匹配项，MySQL将引发错误。

搜索函数

可以写判断，只会返回第一个符合条件的值，其他case被忽略。

MySQL评估求值WHEN子句中的每个条件，直到找到一个值为TRUE的条件，然后执行THEN子句中的相应命令(commands)。

如果没有一个条件为TRUE，则执行ELSE子句中的命令(commands)。如果省略ELSE子句，并且没有一个条件为TRUE，MySQL将引发错误。

不允许在THEN或ELSE子句中使用空命令。如果您不想处理ELSE子句中的逻辑，同时又要防止MySQL引发错误，则可以在ELSE子句中放置一个空的BEGIN END块。

 语法：
 CASE
     WHEN condition_1 THEN commands
     WHEN condition_2 THEN commands
     ...
  ELSE commands
 END CASE;
 
 实例:
 CASE  
  WHEN creditlim > 50000 THEN 
     SET p_customerLevel = 'PLATINUM';
  WHEN (creditlim <= 50000 AND creditlim >= 10000) THEN
     SET p_customerLevel = 'GOLD';
  WHEN creditlim < 10000 THEN
     SET p_customerLevel = 'SILVER';
  END CASE;
 大于50K，则客户是PLATINUM客户。
 小于50K，大于10K，则客户是GOLD客户。
 小于10K，那么客户就是SILVER客户。

多列表练习

 #统计下每个学生平均成绩
   a.分析题意，找出所有相关表
   student
   sc  cource
   b.找到以上表的直接或间接关联条件
   student.sno
   sc.sno
   c.列举你要查询的列条件
   student.sname
   sc.score
   d.组合
   SELECT * FROM student;
 SELECT sname,AVG(sc.score)
 FROM  student  JOIN sc ON student.sno=sc.sno
 GROUP BY student.sno;
   练习 : 
 1.每位学生学习的课程门数

 SELECT student.sname,COUNT(sc.score)
 FROM  student  JOIN sc ON student.sno=sc.sno
 GROUP BY student.sno;
   a.分析题意，找出所有相关表
   student.sno
   sc.sno
   sc.cno
   cource.cno


 2.每位老师所教的课程门数
      a.分析题意，找出所有相关表

 teacher.tno
   cource.tno
    SELECT teacher.sname,COUNT(course.cname)
 FROM  teacher JOIN sc ON teacher.sno=cource.tno
 GROUP BY teacher.tno;


 3.每位老师所教的课程门数和名称
   a.分析题意，找出所有相关表

teacher.tno
cource.tno
SELECT teacher.sname,COUNT(course.cname),GROUP_CONCAT(course.cname)
 FROM  teacher  JOIN course 
ON teacher.sno=cource.tno
GROUP BY teacher.tno;


 4.每位学生学习的课程门数和名称

   student.sno
   sc.sno
   sc.cno
   course
  SELECT st.sname,COUNT(sc.score),
  GROUP_CONCAT(co.cname)
 FROM  student as st
 JOIN sc ON 
 st.sno=sc.sno
 join course as co
 on sc.cno=co.cno
 GROUP BY st.sno;


 1.统计zhang3,学习了几门课
   a.分析题意，找出所有相关表

   student.sno
   sc.sno
   sc.cno
   course.cno
 SELECT st.sname , COUNT(sc.cno)
 FROM student AS st
 JOIN sc ON st.sno=sc.sno
 WHERE st.sname='zhang3'

   
 2.查询zhang3,学习的课程名称有哪些?

  student.sno
   sc.sno
   sc.cno
   course.cno
   course cname
 SELECT st.sname , GROUP_CONCAT(co.cname)
 FROM student AS st
 JOIN sc 
 ON st.sno=sc.sno
 JOIN course AS co
 ON sc.cno=co.cno
 WHERE st.sname='zhang3' 
 group by  st.sname


 3.查询oldguo老师教的学生名.

   student sno
    sc sno
   sc  cno
   cource  cno
   cource  tno
   teacher tno
 SELECT te.tname , GROUP_CONCAT(st.sname)
 FROM student AS st
 JOIN sc 
 ON st.sno=sc.sno
 JOIN course AS co
 ON sc.cno=co.cno
 JOIN teacher AS te
 ON  co.tno=te.tno
 WHERE te.tname='oldguo' 
 group by te.tno;


 4.查询oldguo所教课程的平均分数

 SELECT te.tname , AVG(sc.score)
 FROM teacher AS te
 JOIN course AS co
 ON te.tno=co.tno
 JOIN sc
 ON co.cno=sc.cno
 WHERE te.tname='oldguo'
 group by te.tno;


 5.每位老师所教课程的平均分,并按平均分排序

   SELECT te.tname , AVG(sc.score) as 'avg'
 FROM teacher AS te
 JOIN course AS co
 ON te.tno=co.tno
 JOIN sc
 ON co.cno=sc.cno
 GROUP BY te.tname,co.cno(一个老师教多门课程)                      
 ORDER BY avg DESC ;


 6.查询oldguo所教的不及格的学生姓名

 teacher
 course
 sc
 student
 SELECT te.tname,GROUP_CONCAT(st.sname)
 FROM teacher AS te
 JOIN course  AS co
 ON te.tno=co.tno
 JOIN sc
 ON co.cno=sc.cno
 JOIN student AS st
 ON sc.sno=st.sno
 WHERE te.tname='oldguo' AND sc.score<60
 group by  te.tno;

 7.查询所有老师所教学生不及格的信息

 SELECT te.tname,GROUP_CONCAT(st.sname)
 FROM teacher AS te
 JOIN course  AS co
 ON te.tno=co.tno
 JOIN sc
 ON co.cno=sc.cno
 JOIN student AS st
 ON sc.sno=st.sno
 WHERE sc.score<60;
 group by  te.tno;


 8.查询平均成绩大于60分的同学的学号和平均成绩；

 select st.name,st.sno,avg(sc.score) as 'avg'
 from student as st
 join sc 
 on st.sno=sc.sno
 GROUP BY st.sno
 HAVING avg>60;

 9.查询所有同学的学号、姓名、选课数、总成绩；

 select st.sno,st.sname,count(co.cname),sum(sc.score)
 from student as st
   join sc
 on st.sno=sc.sno
  join course as co
  on sc.cno=co.cno
  GROUP BY st.sno

 
 10.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

 course
 sc
  SELECT co.cno,MAX(sc.score),MIN(sc.score)
 FROM course AS co
 JOIN sc
 ON co.cno=sc.cno
 GROUP BY co.cno


 11.统计各位老师,所教课程的及格率

 SELECT te.tname,COUNT(CASE  WHEN sc.score>=60 THEN 1 END)/COUNT(*)
 FROM teacher AS te 
 JOIN course AS co
 ON te.tno=co.tno
 JOIN sc
 ON co.cno=sc.cno
 GROUP BY te.tno


 12.查询每门课程被选修的学生数

 course
 sc
 SELECT co.cname,COUNT(sc.sno)
 FROM course AS co
 JOIN sc
 ON co.cno=sc.cno
 GROUP BY co.cno
 
13.查询出只选修了一门课程的全部学生的学号和姓名

 select co.cname,GROUP_CONCAT(CONCAT(st.sno,":",st.sname))
 FROM course AS co
 JOIN sc
 ON co.cno=sc.cno
 JOIN student as st
 on sc.sno=st.sno
 GROUP BY co.cno

 14.查询选修课程门数超过1门的学生信息

 student
 sc
 SELECT st.sname,COUNT(*)
 FROM student AS st
 JOIN sc
 ON st.sno=sc.sno
 GROUP BY  st.sno
 HAVING COUNT(*)>1

 15.统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表
 SELECT

 co.cname,
 GROUP_CONCAT(CASE WHEN sc.score>=85  THEN st.sname END) AS 优秀,
 GROUP_CONCAT(CASE WHEN sc.score>=70 AND sc.score<85  THEN st.sname END) AS 良好,
 GROUP_CONCAT(CASE WHEN sc.score>=60 AND sc.score<70  THEN st.sname END) AS 一般,
 GROUP_CONCAT(CASE WHEN sc.score<60  THEN st.sname END) AS 差
 FROM course as co
 JOIN sc 
 ON co.cno=sc.cno
 JOIN student as st
 ON sc.sno=st.sno
 GROUP BY co.cno

 {方法一：使用CASE WHEN
 SELECT course.cname,GROUP_CONCAT(CASE WHEN sc.score>85 THEN student.sname END) AS '85分以上',
 GROUP_CONCAT(CASE WHEN 70<sc.score<=85 THEN student.sname END) AS '70-85分',
 GROUP_CONCAT(CASE WHEN 60<=sc.score<=70 THEN student.sname END) AS '60-70分',
 GROUP_CONCAT(CASE WHEN sc.score<60 THEN student.sname END) AS '60分以下'
 FROM course JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno
 GROUP BY course.cno
 方法二：使用IFNLL
 SELECT course.cname, GROUP_CONCAT(CASE WHEN IFNULL(sc.score,0)>=85 THEN student.sname END) AS '优秀', 
 GROUP_CONCAT(CASE WHEN IFNULL(sc.score,0) BETWEEN 70 AND 85 THEN student.sname END ) AS '良好', 
 GROUP_CONCAT(CASE WHEN IFNULL(sc.score,0) BETWEEN 60 AND 70 THEN student.sname END) AS '一般', 
 GROUP_CONCAT(CASE WHEN IFNULL(sc.score,0)< 60 THEN student.sname END) AS '不及格' 
 FROM student JOIN sc ON sc.sno=student.sno 
 JOIN course ON course.cno=sc.cno 
 GROUP BY course.cno;
 方法三：使用IF
 SELECT course.cname,GROUP_CONCAT(IF(sc.score>85,student.sname,FALSE)) AS '85分以上',
 GROUP_CONCAT(IF(70<sc.score<=85,student.sname,FALSE)) AS '70-85分',
 GROUP_CONCAT(IF(60<=sc.score<=70,student.sname,FALSE)) AS '60-70分',
 GROUP_CONCAT(IF(sc.score<60,student.sname,FALSE)) AS '60分以下'
 FROM course JOIN sc ON course.cno=sc.cno 
 JOIN student ON sc.sno=student.sno
 GROUP BY course.cno}


 16.查询平均成绩大于85的所有学生的学号、姓名和平均成绩

 SELECT st.sno,st.sname,AVG(sc.score) as 'avg'
 FROM student as st
 JOIN sc 
 ON st.sno=sc.sno
 GROUP BY st.sno
 HAVING AVG>85

服务器配置　

mysqld选项，服务器系统变量和服务器状态变量： 官方文档：

https://dev.mysql.com/doc/refman/5.7/en/mysqld-option-tables.html https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables/

注意：其中有些参数支持运行时修改，会立即生效；有些参数不支持，且只能通过修改配置文件，并重启服务器程序生效；有些参数作用域是全局的，且不可改变；有些可以为每个用户提供单独（会话）的设置。

查看官方文档帮助：

 commdline: 是服务器选项。
  
 scope:global 既是服务器选项又是服务器变量。

获取mysqld的可用选项列表：

 mysqld --help --verbose
 mysqld --print-defaults 获取默认设置

设置服务器选项方法：

 在命令行中设置

 shell> ./mysqld_safe --skip-name-resolve=1
 在配置文件my.cnf中设置
 skip_name_resolve=1

MySQL中的系统数据库

mysql数据库

 是mysql的核心数据库，类似于Sql Server中的master库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息

performance_schema数据库

 MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表

information_schema数据库

 MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在information_schema数据库类似与“数据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）

服务器变量SQL_MODE

SQL_MODE：对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置，参看：https://mariadb.com/kb/en/library/sql-mode/ 常见MODE: NO_AUTO_CREATE_USER

 禁止GRANT创建密码为空的用户
 NO_ZERO_DATE
 在严格模式，不允许使用‘0000-00-00’的时间
 ONLY_FULL_GROUP_BY
 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的
 NO_BACKSLASH_ESCAPES
 反斜杠“\”作为普通字符而非转义字符
 PIPES_AS_CONCAT
 将"||"视为连接操作符而非“或运算符”

posted @ 2021-03-01 22:48 上善若水~小辉阅读(519) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

www.cnblogs.com

www.cnblogs.com/strugger-0316/

数据库基础一

一、数据库介绍

名词介绍

数据库(DBMS)产品种类

SQL

二、MySQL简介

1、什么是数据库 ？

2、数据库管理作用

3、数据库管理系统的优点

4、数据库管理系统的基本功能

5、数据库管理系统

6、数据库系统的架构

7、关系型数据库

8、联系的类型

9、简易数据规划流程

10、数据库的正规化分析

11、范式

三、MySQL的特性

四、MySQL安装

MySQL 企业版本选择

MySQL下载

yum源安装MySQL

MySQL 二进制包安装

五、MySQL操作

1、连接数据库

2、查看数据库，创建数据库，使用数据库查看数据库

六、用户管理

6.1 作用

6.2 格式

6.3 操作命令

七、权限管理

7.1 作用

7.2 语法格式

7.3 操作命令

本地管理员( root@localhost 用户)

八、连接管理

8.1 自带客户端命令

开发工具

程序(驱动)连接

九.配置文件

方式

配置文件应用

配置文件的默认读取路径

配置文件结构

十、启动关闭

单版本

十一、多实例

11.1 单版本单实例

11.2 多版本多实例

十二、升级

12.1 升级方式

12.2升级注意事项（INPLACE)

12.3INPLACE升级过程原理

5.6.48 ----> 5.7.30 Inplace 升级演练

Mysql5.7.30Inplace升级到MySQL8.0.20

a. 安装mysqlsh

第十三章 体系结构

SQL基础

名词认识

sql_mode

字符集

校对规则

列属性

数据类型

SQL介绍（面试题）

DDL(数据定义语言)

库定义

表定义

DDL规范

DCL

DML

INSERT

UPDATE

DELETE(生产中禁止使用)

DQL应用(select )

select子句顺序

单独使用

FROM

1、什么是数据库？

`配置文件结构`

12.3`INPLACE升级过程原理`

`5.6.48 ----> 5.7.30 Inplace 升级演练`

`Mysql5.7.30Inplace升级到MySQL8.0.20`

第十三章体系结构

`SQL介绍（面试题）`

`DDL(数据定义语言)`

服务器配置　