数据库基础一
一、数据库介绍
名词介绍
-
数据库：是数据的汇集，它以一定的组织形式存于存储介质上。是对企业核心数据 高效 安全 存储和管理的软件
-
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、客户端程序接口
-
CLI
-
GUI
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_password8.0 之后使用的密码插件是
caching_sha2_password导致问题: 老的客户端程序, 连接不了8.0版本
解决方法:
创建用户时指定密码加密插件为
mysql_native_passwordcreate user wordpress@'10.0.0.%' identified with mysql_native_password by '123';
修改用户时替换密码加密插件为
mysql_native_passwordalter 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.3INPLACE升级过程原理
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-networkingb. 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
revokeDML
作用：对表中的数据行进行增、删、改
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.cnfDELETE(生产中禁止使用)
-- 删除指定行 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-
-
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] ENDcase_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)的学生列表
SELECTco.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
将"||"视为连接操作符而非“或运算符” -
很高兴各位朋友能欣赏作品，本文版权归作者和博客园共有，欢迎转载，请在文章页面明显位置给出原文出处，否则将追究法律责任。 原文链接： https://www.cnblogs.com/strugger-0316
