mysql-1

第一天

mysql 5.7.28二进制

centos 7.6

hostname：db-01

rpm -qa |grep mariadb

yum remove mariadb-libs -y

useradd mysql -s /sbin/nologin

id mysql

mkdir -p /app/database

mkdir -p /data

mkdir -p /binlog/3306

chown -R mysql.mysql /app /data /binlog

cd /app/database

tar xf mysql-5.7.28.*.tar.gz

ln -s mysql-5.7.8.* mysql

vim /etc/profile

​ export PATH=/app/database/mysql/bin:$PATH

source /etc/profile

mysql -V

==mysqld --initialize-insecure --user=mysql --basedir=/app/database/mysql --datadir=/data/3306==

dba可以忽略warning

5.6不是这个命令是：==/app/database/mysql/scripts/mysql_install_db==

​ yum install -y libaio-devel

vim /etc/my.cnf

[mysqld]
user=mysql
basedir=/app/databae/mysql
datadir=/data/3306
server_id=6
port=3306
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock



cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/app/databae/mysql
datadir=/data/3306
server_id=6
port=3306
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
EOF

cd /app/database/mysql/support-files

cp mysql.server /etc/init.d/mysqld

chkconfig --add mysqld

systemctl start mysqld

==mysql -S /tmp/mysql.sock==

只能在本地使用，不依赖ip和port

mysql -uroot -pmima -h ip -P3306

==实例：mysqld+工作线程+预分配的内存结构==

​ 功能：管理数据

mysqld的程序结构（两层）

  • server层

  • 连接层

    • 提供连接协议（socket、tcp/ip）
    • 验证
    • 提供专用链接线程

  • SQL层

    • 语法检查

    • 语义（DDL,DCL,DML,DTL...）

    • 权限

    • 解析器：解析预处理，得到多种执行计划

    • 优化器：基于代价cost，帮我们选择最优的方案（执行计划）

    • 执行器：按照优化器的选择，执行sql语句，得出执行结果（你需要的数据在磁盘的什么位置）

      • 你需要的数据在xxx段，xxx区，xxx页

    • 查询缓存（query cache默认不开启，8.0版本取消了），可以redis（阿里的Tair）替代

    • 日志记录（binlog二进制日志，glog---通用日志，需要人为开启）

  • 存储引擎层：相当于linux文件系统，和磁盘交互的模块

磁盘：xxx.ibd

#连接层
show processlist;

select user,host from mysql.user;

mysql的逻辑结构（==对象==）

  • 库
    • 库名
    • 库属性
  • 表
    • 表名
    • 表属性
    • 表内容/数据行
    • 列
show databases;
use mysql;
show tables;
desc user;

mysql的物理存储结构（==存储引擎层==）

  • 段
    • 一个表就是一个段（分区表除外）
    • 段可以由1个或多个区构成，但是多个区不一定是连续的
  • 区/簇
    • 一个区，默认1M，连续的64个pages
    • extent：连续64pages=1MB
  • 页
    • 一个页，默认16KB，连续的4个os block，最小的IO单元
    • page：16KB=4个连续block

第二天

用户管理

  • mysql用户：用户名@'白名单'
    • ==select user,host,authentication_string from mysql.user;==
create user xinzhan@'localhost';
select user,host from mysql.user;

create user xinzhan@'192.168.1.%'  identified by '123456';
select user,host,authentication_string from mysql.user;

alter user xinzhan@'localhost' identified by '123456';

drop user xinzhan@'localhost';
drop user xinzhan@'192.168.1.%';

8.0版本之前，可以通过grant命令，建立用户+授权

权限管理

  • 权限的表现方式
    • ==show privileges;==
  • 授权、回收权限操作
    • 语法8.0以前：grant 权限 on 对象 to 用户 identified by '密码';
    • 语法8.0+：
      • create user 用户 identified by '密码';
      • grant 权限 on 对象 to 用户;
    • 权限
      • ALL：管理员
      • 权限1,权限2,权限3,......： 普通用户（业务用户，开发用户）
      • Grant option：给别的用户授权
        • grant 权限1,权限2,权限3,...... on 对象 to 用户 identified by '密码' ==with grant option==;
    • 对象：库，表
      • *.*
      • 库名.*
      • 库名.表名
    • mysql授权表（扩展）
      • user---*.*
      • db---库名.*
      • tables_priv
      • columns_priv
    • 回收权限
      • 不能通过重复授权，修改权限，只能通过回收权限方式进行修改，但可以叠加权限
      • revoke create on app. from app@'192.168.1.%';*
grant all on *.* to xinzhan@'192.168.1.%'  identified  by '123456' with grant option;

select user,host from mysql.user;

#查询用户权限
show grants for xinzhan@'192.168.1.%';
#也可以通过下面语句查看权限（这个不一定好用）
select * from mysql.user\G;

grant create,update,select,insert,delete on app.* to app@'192.168.1.%'  identified by '123' 

revoke create on app.*  from app@'192.168.1.%';

超级管理员忘记密码怎么办

/etc/init.d/mysqld stop

service mysqld start --skip-grant-tables （这个比较灵异）

以前版本的做法：

/etc/init.d/mysqld stop

mysqld_safe --skip-grant-tables &

  • 关闭数据库（下面两种都可以）
    • systemctl stop mysqld
    • service mysqld stop
  • 使用安全模式启动（下面两种都可以）
    • ==mysqld_safe --skip-grant-tables --skip-networking &==
    • service mysqld start --skip-grant-tables --skip-networking
  • 登录
    • mysql
    • ==flush privileges;==
      • 手工加载授权表
    • alter user root@'localhost' identified by '123456';
  • 重启数据库到正常模式
    • service mysqld restart

连接管理

  • windows

    • sqlyog
    • workbench
    • navicat

  • linux（mysql自带客户端）

    • mysql

      参数列表：

      -u 用户名

      -p 密码

      -S 本地socket文件位置

      -h 数据库ip地址

      -P 数据库端口号

      -e 免交互执行数据库命令

      < 导入sql脚本

    • mysqldump

    • mysqladmin

  • api driver

==show processlist==

初始化配置管理

  • 源码安装，编译过程中设置初始化参数
  • 配置文件：数据库启动之前，设定配置文件参数（/etc/my.cnf）
  • 启动脚本命令行

配置文件的应用

  • 配置文件读取顺序
    • mysqld --help --verbose |grep my.cnf
  • 手动定制配置文件位置
    • ==mysqld --defaults-file=/opt/my.cnf &==
      • mysqld_safe --defaults-file=/opt/my.cnf &

启动和关闭

调用关系（流程）

service mysqld start/stop/restart

systemctl start/stop/restart mysqld

​ support-file/mysql.server

​ mysqld_safe：启动时可以临时设定参数

​ mysqld：启动时可以临时设定参数

临时设定参数

  • --skip-grant-tables
  • --skip-networking
  • --defaults-file=/xxx/my.cnf
  • --port=xxx

启动

​ systemctl start mysqld---> mysql.server--->mysql_safe--->mysqld

关闭

  • systemctl stop mysqld
  • service mysqld stop
  • mysqladmin -uroot -pxxx shutdown
  • mysql -uroot -p xxx -e "shutdown"

mysql的多实例

同版本的多实例

  • 规划
    • 软件一份，
    • 配置文件3份，/data/330{7..9}/my.cnf
    • 数据目录3份，/data/330{7..9}
    • 初始化数据3次，
    • 日志目录3份，/binlog/330{7..9}
    • socket文件3份，/tmp/mysql330{7..9}.sock
    • 端口3个，port=3307,3308,3309
    • server_id3个,service_id=7,8,9
  • 配制过程
    • 创建需要的目录
    • 创建配置文件
    • 初始化数据
    • 准备启动脚本
    • 启动多实例
mkdir -p /data/330{7..9}/data
mkdir -p /binlog/330{7..9}

cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/app/database/mysql
datadir=/data/3307/data
socket=/tmp/mysql3307.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/binlog/3307/mysql-bin
EOF

cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/app/database/mysql
datadir=/data/3308/data
socket=/tmp/mysql3308.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/binlog/3308/mysql-bin
EOF

cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/app/database/mysql
datadir=/data/3309/data
socket=/tmp/mysql3309.sock
log_error=/data/3309/mysql.log
port=3307
server_id=7
log_bin=/binlog/3309/mysql-bin
EOF

chown -R mysql.mysql /data   /binlog
mv /etc/my.cnf /etc/my.cnf.bak
mysqld  --initialize-insecure --user=mysql  --basedir=/app/database/mysql --datadir=/data/3307/data
mysqld  --initialize-insecure --user=mysql  --basedir=/app/database/mysql --datadir=/data/3308/data
mysqld  --initialize-insecure --user=mysql  --basedir=/app/database/mysql --datadir=/data/3309/data

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=/app/database/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=/app/database/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=/app/database/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF

systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
netstat  -tulpn  |grep  330

不同版本的多实例

  • 软连接不同版本软件，修改环境变量
  • 准备不同目录
  • 准备配置文件
  • 初始化数据
  • 准备启动脚本
cd /app/database
ln -s mysql-5.6.46-.*  mysql56
ln -s mysql-8.0.18-.*  mysql80
mv /etc/my.cnf /etc/my.cnf.bak

vim /etc/profile
#注释以下信息
#export PATH=/app/database/mysql/bin:$PATH
    
#export  PATH=/app/database/mysql/bin:$PATH
#export  PATH=/app/database/mysql56/bin:$PATH
#export  PATH=/app/database/mysql80/bin:$PATH
###三选其一

mkdir -p /data/331{7..8}/data
mkdir -p /binlog/331{7..8}
chown -R mysql.mysql /data/* /binlog/*

cat > /data/3317/my.cnf <<EOF
[mysqld]
basedir=/app/database/mysql56
datadir=/data/3317/data
socket=/tmp/mysql3317.sock
log_error=/data/3317/mysql.log
port=3317
server_id=17
log_bin=/binlog/3317/mysql-bin
EOF

cat > /data/3318/my.cnf <<EOF
[mysqld]
basedir=/app/database/mysql80
datadir=/data/3318/data
socket=/tmp/mysql3318.sock
log_error=/data/3318/mysql.log
port=3318
server_id=18
log_bin=/binlog/3318/mysql-bin
EOF

#5.6
/app/database/mysql56/scripts/mysql_install_db --user=mysql --basedir=/app/database/mysql56 --datadir=/data/3317/data

#8.0
/app/database/mysql80/bin/mysqld --initialize-insecure --user=mysql --basedir=/app/database/mysql80 --datadir=/data/3318/data

cat > /etc/systemd/system/mysqld3317.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=/app/database/mysql56/bin/mysqld --defaults-file=/data/3317/my.cnf
LimitNOFILE = 5000
EOF

cat > /etc/systemd/system/mysqld3318.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=/app/database/mysql80/bin/mysqld --defaults-file=/data/3318/my.cnf
LimitNOFILE = 5000
EOF

systemctl start mysqld3317
systemctl start mysqld3318
netstat  -tulpn  |grep  331

vim /etc/profile
export PATH=/app/database/mysql/bin:$PATH

source /etc/profile
mysql -S /tmp/mysql3317.sock
#mysql5.7本地客户端可以兼容不同版本的mysqld（5.6，8.0）
mysql -S /tmp/mysql3318.sock

第三天

sql基础

  • sql常用类型
    • mysql客户端自带的
      • help---不属于sql语句
    • server端分类命令
      • ==help contents==
        • help Data Definition
          • help DROP DATABASE
      • 分类（记前三）
        • DDL：数据定义语言---Data Definition
        • DCL：数据控制语言
        • DML：数据操作语言
        • DQL：数据查询语言
  • sql的各种名词
    • sql_mode---sql模式
      • 规范sql语句书写方式
      • ==select @@sql_mode;==
      • 版本5.6和5.7差别很大，5.7以后差不过一样
    • 字符集（charset）及校对规则（collation）
      • 字符集：==show charset==
        • utf8：最大存储长度，单个字符最多3个字节
        • utf8mb4：5.6版本才出现，现在建议都要用这个编码；最大存储长度，单个字符最多4个字节
        • create database zabbix ==charset utf8mb4==;
        • ==show create database zabbix;==
      • 校对规则
        • 每种字符集，有多种校对规则（排序规则）
        • ==show collation;==
        • select ASCII('A');

数据类型

  • 数字

    • 整数
      • tinyint：存储长度=1B
      • int：4B
      • bigint：8B
    • 小数

  • 字符串

    • char(长度)：定长字符串类型，最多255个字节
    • varchar(长度)：变长字符串类型，最多65535个字节
      • 除了存储字符串之外，还会额外使用1-2字节存储字符长度
    • enum('bj',sh)：枚举
    • 字符串类型选择会影响索引应用
    • 对于英文和数字，每个字符占一个字节长度
    • 对于中文（utf8，uft8mb4），每个字符占三个字节
      • uft8mb4字符集中==emoji字符，占4个字节长度==
    • ==select length(column_name) from table_name;==

  • 时间

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

  • 二进制

  • json

    • 版本8.0才有的
create database xinzhan charset  utf8mb4;
use xinzhan;
create table t1(id int,name  varchar(64),age tinyint);
show tables;
desc  t1;

约束

  • Primary Key---PK
    • 主键约束。作用：唯一+非空
    • 每一张表只能有一个主键，最为聚簇索引
  • not null
    • 非空约束，必须非空
    • 建议每个列都设置非空
  • unique key
    • 唯一约束，必须不重复的值
  • unsigned
    • 针对数字列，非负数

其他属性

  • default
    • 默认值
  • comment
    • 注释
  • auto_increment

sql应用

client

  • \c：结束上条命令运行
  • ==\G==：格式化输出
  • help
  • \q：退出mysql会话---ctrl+d
  • source：导入sql脚本，类似于<
  • system：调用linux命令

==Server==

  • DDL：数据定义语言

    • 库定义：库名 库属性

      • 创建库：==create database xxx charset utf8mb4;==
      • 查库
        • ==show database;==
        • ==show create databae xxx;==
      • 修改库：==alter database xxx charset utf8mb4;==
        • 库名是不能修改的
      • 删除库：==drop database xxx;==

    • 表定义

      • 创建表

        • CREATE TABLE stu(
    id      INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
    sname   VARCHAR(255) NOT NULL COMMENT '姓名',
    sage    TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
    sgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别' ,
    sfz     CHAR(18) NOT NULL UNIQUE  COMMENT '身份证',
    intime  TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间'
) ENGINE=INNODB CHARSET=utf8 COMMENT '学生表';

###或者如下
CREATE TABLE stu(
    id      INT NOT NULL  AUTO_INCREMENT COMMENT '学号',
    sname   VARCHAR(255) NOT NULL COMMENT '姓名',
    sage    TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
    sgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别' ,
    sfz     CHAR(18) NOT NULL UNIQUE  COMMENT '身份证',
    intime  TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间',
   PRIMARY KEY(id)
) ENGINE=INNODB CHARSET=utf8 COMMENT '学生表';

      • 查询表

        • ==show tables;==
        • ==desc table_name;==
        • ==show create table table_name;==

      • 修改表

        • ==alter table table_name add [column] column_name bigint not null unique key comment '注释';==
        • ==alter table table_name modify column_name char(11) not null unique key comment '注释';==
        • ==alter table table_name drop column_name;==

      • 删除表：==drop table table_name;==

第四天

DDL

线上ddl（alter）操作对于生产的影响

sql审核平台：yearing inception

说明：在mysql中，ddl语句在对表进行操作时，是要==锁元数据表==的。此时，所有修改类的命令无法正常运行

==所以==：在对于大表，业务繁忙的表，进行线上ddl操作时，要谨慎。尽量避开业务员繁忙期间，进行ddl

==``建议使用`：pt-online-schema-change(pt-osc) gh-ost 工具进行ddl操作，减少锁表的影响==

  • pt-osc工具的使用==？？？==
    • 往表中加列
  • pt-osc的工作原理
  • pt-osc简书

PerconaToolKit

DCL（略）

  • grant
  • revoke

DML

表中数据行进行操作

  • insert
    • ==insert into table_name (column_name,xxx,......) values(xxx,xxx,......);==
    • 简约方法
    • 部分录入数据
    • 批量录入方式
      • ==insert into table_name (column_name,xxx,......) values(xxx,xxx,......),(xxx,xxx,......),(xxx,xxx,......);==
  • update
    • 修改指定数据行的值，必须要明确要改哪一行，一般update语句都要有where的条件
      • update table_name set column_name=xxx where column_name1=xxx;
    • 如果没有指定某行，那就是全表的数据行都修改
  • delete
    • 删除指定数据行的值，必须要明确要删哪些行，一般delete语句都要有where的条件
      • delete from table_name where column_name=xxx;

==伪删除==

修改表结构，添加state状态列

==alter table table_name add column state tinyint not null default 1;==

删除数据改为update

==update table_name set state=0 where column_name=xxx;==

查询语句改为：

==select * from table_name where state=1;==

delete from table_name,drop table table_name,truncate table table_name 区别？

  1. 以上三条命令都可以删除全表数据
    • delete逻辑上是逐行删除。数据行多，操作很慢。并没有真正从磁盘中删除，只是在存储层面打标记，磁盘空间不立即释放
      • HWM高水位线不会降低
      • 全表扫描和数据页大量碎片会影响
    • drop将表结构（元数据）和==数据行==物理层次删除
    • truncate清空表==段==中的所有==数据页==。物理层次删除==全表==数据，磁盘空间立即释放，HWM高水位线会降低

DQL

  • select

    • select配合内置函数使用
      • select now();
      • select database();
      • select concat('xxx');
        • select concat(user,'@',host) from mysql.user;
      • select version();
      • select user();
    • 计算
      • select 10*100;
    • 查询数据库的参数
      • select @@port;
      • select @@socket;
      • select @@datadir;
      • select @@innodb_flush_log_at_trx_commit;

  • ==select标准用法==（配合其他子句使用）

    • 单表

      • select
  from 表1,表2,......
  where 过滤条件1 过滤条件2 ......
  group by 条件列1 条件列2 ......
      select_list 列名列表
  having 过滤条件1 过滤条件2 ......
  order by 条件列1 条件列2 ......
  limit 限制
  
                                      
###where在group by之前；

      having在group by+聚合函数之后，再做判断过滤使用

      
- **select * from world.city where countrycode in ('CHN','USA') and population>5000000;**

- ==group by 配合聚合函数使用==

- ```sql
  select district,sum(population),count(id),group_concat(name)
  from world.city
  where countrycode='CHN'
  group by district;
  
  
  ```

- **having子句---==后过滤==**

- ```sql
  select district,sum(population)
  from world.city
  where countrycode='CHN'
  group by district
  having sum(population)>5000000;
  ```

- **order by子句---排序**

- ```sql
  select district,sum(population)
  from world.city
  where countrycode='CHN'
  group by district
  having sum(population)>5000000
  order by sum(population) desc;
  ```

- limit子句

- 分页显示结果集

- ```mysql
  select district,sum(population)
  from world.city
  where countrycode='CHN'
  group by district
  having sum(population)>5000000
  order by sum(population) desc
  limit 5;
  
  ###limit 2,3---前面是跳过前2行，后面是显示3行
  ###limit 3 offset 2
  
  ```

  • ==多表链接查询==

    • use school
student ：学生表
sno：    学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别

teacher ：教师表
tno：     教师编号
tname：教师名字

course ：课程表
cno：  课程编号
cname：课程名字
tno：  教师编号

score  ：成绩表
sno：  学号
cno：  课程编号
score：成绩

###项目构建
drop database school;
CREATE DATABASE school CHARSET utf8;
USE school

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;

### 统计zhang3,学习了几门课
SELECT st.sno,st.sname , COUNT(sc.cno)
FROM student AS st
JOIN sc
ON st.sno=sc.sno
WHERE st.sname='zhang3' 
group by st.sno,st.sname;

### 查询zhang3,学习的课程名称有哪些（这个是先join再过滤，但是内连接的驱动表由优化器决定）
SELECT st.sno,st.sname , group_concat(course.cname)
FROM student AS st
JOIN sc
ON st.sno=sc.sno
join course
on sc.cno=course.cno
WHERE st.sname='zhang3' 
group by st.sno,st.sname;


#### 优化上一条sql语句（驱动表student，先过滤，再left join）
SELECT st.sno,st.sname , group_concat(course.cname)
FROM student AS st
left JOIN sc
ON st.sno=sc.sno
join course
on sc.cno=course.cno
WHERE st.sname='zhang3' 
group by st.sno,st.sname;

### https://www.jianshu.com/p/08c4b78402ff

    • 笛卡尔乘积

      • select * from teacher ,course;
      • select * from teacher join course;

    • ==内连接==

      • ==select * from teacher join course on teacher.tno=course.tno;==

      • A join B

        ​ on A.xxx=B.yyy

    • 外连接

      • left join：左表所有数据，右表满足条件的数据
      • right join：右表所有数据，左表满足条件的数据

聚合函数

max() min() avg() count() sum() group_concat()---列转行

  • 将结果集小的表（是针对整个语句）设置为驱动表更加合适，可以降低next loop的次数

  • 对于内连接来讲，我们是没法控制驱动表是谁，完全由优化器决定

  • 如果，需要人为干预，需要将内连接改为外连接

  • select别名---as

    • 列别名
      • 可以定制显示的列名
      • 可以在having，order by子句中调用
    • 表别名
      • 全局调用定义的别名

  • select的去重---distinct

  • select的union 和union all

    • 聚合两个结果集

    • union去重聚合的两个结果集

    • select * from world.city where countrycode='CHN'
union all
select * from world.city where countrycode='USA';

###上面的sql语句比下面的语句效率好
select * from world.city where countrycode in ('CHN','USA');
select * from world.city where countrycode='CHN' or countrycode='USA';

  • show---mysql独有的

    • ==show variables;==

      • 显示mysql中所有的参数信息

    • ==show variables like '%trx%'==

    • help show;
show databases;
show tables;

###
show tables from database_name;

show processlist;
show full processlist;
show charset;
show collation;
show engines;

show privileges;
show grants for ......
show create database ......
show create table ......

show index from ......

show engine innodb status;
show status;
show status like '%xxx%';
show variables;
show variables '%xxx%';

show binary logs;
show binlog events in
show master status

show slave status;
show relaylog events in

第五天

==元数据==

表（逻辑表）

  • 数据字典：表中列的定义信息（元数据）
    • myisam：xxx.frm
    • innodb
      • 8.0之前：xxx.frm+ibdata1
      • 8.0之后：xxx.frm
  • 数据行记录（真实数据）
    • myisam：xxx.myd
    • innodb：xxx.ibd
  • 索引（真实数据）
    • myisam：xxx.myi
    • innodb：xxx.ibd
  • 数据库状态
    • mysql库
    • ==I_S==---information_schema：视图
      • 可以查询数据字典、数据库状态、权限
      • 放在内存中的（内存库）
    • P_S
    • SYS库
  • 权限（元数据）
    • mysql.user
    • mysql.db
    • mysql.table
    • mysql.column
    • ......
  • 日志：专门日志文件

I_S--内存库

每次数据库启动，会自动在内存中生成I_S，生成查询mysql部分元数据信息==视图==。

I_S中的视图，保存的是查询元数据的方法，不保存数据本身

create view v_select as 
select district,sum(population)
from world.city
where countrycode='CHN'
group by district
having sum(population)>5000000
order by sum(population) desc
limit 5;

select  * from v_select;

use information_schema;
show tables;

# tables是个表（I_S）
desc tables；

I_S.tables---内存表

==保存了所有表的数据字典信息==，是个视图（内存中的表），有如下字段（仅介绍部分---常用的）：

  • TABLE_SCHEMA：表所在的库
  • TABLE_NAME：表名
  • ENGINE：表的引擎
  • TABLE_ROWS：表的数据行（不是实时的值）
  • AVG_ROW_LENGTH：平均行长度
  • DATA_LENGTH：表使用的存储空间大小（不是实时的值）
  • INDEX_LENGTH：表中索引占用空间大小
  • DATA_FREE：表中是否有碎片
### 数据库资产统计
#### 一、统计每个库下的所有表的个数，表名列表
select table_schema,count(table_name),group_concat(table_name)
from information_schema.tables
group by table_schema;

#### 二、统计每个库的占用空间总大小
##### 1.一张表的大小=AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
##### 2.一张表的大小=DATA_LENGTH
##### 单位是B（字节）
select table_schema,sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)
from information_schema.tables
group by table_schema;

#### 三、查询业务数据库（系统库除外），所有非innodb表
##### 系统库：information_schema，mysql，performance_schema，sys
select table_schema,table_name
from information_schema.tables
where engine!='InnoDB'
and table_schema not in ('sys','performance_schema','information_schema','mysql');

#### 四、查询业务数据库（系统库除外），所有非innodb表。将非innodb表改为innodb表
select concat('alter table ',table_schema,'.',table_name,' engine=innodb;')
from information_schema.tables
where engine!='InnoDB'
and table_schema not in ('sys','performance_schema','information_schema','mysql')
into outfile '/tmp/alter.sql';
##### 需要在/etc/my.cnf的[mysqld]中添加secure-file-priv=/tmp，然后重启数据库


cat > /etc/my.cnf <<EOF
[mysqld]
secure-file-priv=/tmp
user=mysql
basedir=/app/databae/mysql
datadir=/data/3306
server_id=6
port=3306
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
EOF

一些练习

### 1.查询oldguo老师教的学生名.
SELECT concat(te.tname,'_',te.tno) ,GROUP_CONCAT(st.sname)
FROM teacher AS te
JOIN course
ON te.tno=course.tno
JOIN sc
ON course.cno=sc.cno
JOIN student AS st
ON sc.sno=st.sno
WHERE te.tname='oldguo'
group by te.tno,te.tname;

### 2.查询oldguo所教课程的平均分数
SELECT concat(te.tname,'_',te.tno,'_',course.cname,'_',course,cno) ,avg(sc.score)
FROM teacher AS te
JOIN course
ON te.tno=course.tno
JOIN sc
ON course.cno=sc.cno
WHERE te.tname='oldguo'
group by te.tno,te.tname,course.cno;

### 3.每位老师所教课程的平均分,并按平均分排序
SELECT concat(te.tname,'_',te.tno,'_',course.cname,'_',course,cno) ,avg(sc.score)
FROM teacher AS te
JOIN course
ON te.tno=course.tno
JOIN sc
ON course.cno=sc.cno
group by te.tno,te.tname,course.cno
order by avg(sc.score) desc;

### 4.查询oldguo所教的不及格的学生姓名
SELECT concat(te.tname,'_',te.tno),group_concat(concat(st.sname,':',st.score))
FROM teacher AS te
JOIN course
ON te.tno=course.tno
JOIN sc
ON course.cno=sc.cno
JOIN student AS st
ON sc.sno=st.sno
where te.tname='oldguo' and sc.score<60
group by te.tno,te.tname;

### 5.查询所有老师所教学生不及格的信息
SELECT concat(te.tname,'_',te.tno),group_concat(concat(st.sname,':',st.score))
FROM teacher AS te
JOIN course
ON te.tno=course.tno
JOIN sc
ON course.cno=sc.cno
JOIN student AS st
ON sc.sno=st.sno
where sc.score<60
group by te.tno,te.tname;

### 6.查询平均成绩大于60分的同学的学号和平均成绩
select sc.sno,avg(sc.score)
from sc
group by sc.sno
having avg(sc.score)>60;

### 7.查询所有同学的学号、姓名、选课数、总成绩
select st.sno,st.sname,count(sc.cno),sum(sc.score) 
from student as st
join sc
on st.sno=sc.sno
group by st.sno,st.sname;

### 8.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select sc.cno,max(sc.score),min(sc.score)
from sc
group by sc.cno;

### 9.统计各位老师,所教课程的及格率
SELECT concat(te.tname,'_',te.tno,'_',course.cname,'_',course,cno) ,concat(count(case when sc.score>60 then 1 end)/count(sc.score)*100,'%') as '及格率'
FROM teacher AS te
JOIN course
ON te.tno=course.tno
JOIN sc
ON course.cno=sc.cno
group by te.tno,te.tname,course.cno;

### 10.查询每门课程被选修的学生数
### 11.查询出只选修了一门课程的全部学生的学号和姓名
### 12.查询选修课程门数超过1门的学生信息
### 13.统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表
select course.cname,
group_concat(case sc.score>85 then st.sname end) as '优秀',
group_concat(case sc.score>70 and sc.score<=85 then st.sname end) as '良好',
group_concat(case sc.score>60 and sc.score<=70 then st.sname end) as '一般',
group_concat(case sc.score<60 then st.sname end) as '不及格'
from course
join sc
on course.cno=sc.cno
join student as st
on sc.sno=st.sno
group by course.cname;

### 14.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 194,911评论 5赞 460
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 82,014评论 2赞 371
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 142,129评论 0赞 320
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,283评论 1赞 264
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,159评论 4赞 357
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,161评论 1赞 272
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,565评论 3赞 382
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,251评论 0赞 253
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,531评论 1赞 292
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,619评论 2赞 310
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,383评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,255评论 3赞 313
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,624评论 3赞 299
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 28,916评论 0赞 17
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,199评论 1赞 250
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,553评论 2赞 342
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,756评论 2赞 335

推荐阅读更多精彩内容

  • MySQL
    修改用户 mysql> alter user root@'localhost' identified by '12...
    不知道就阅读 875评论 0赞 0
  • Mysql-的基础应用
    SQL介绍 结构化的查询语言，关系型数据库通用的命令，遵循SQL92的标准（SQL_MODE） SQL常用种类 D...
    大仙儿没溜儿阅读 426评论 0赞 0
  • MySQL的基础操作一，至存储引擎篇
    数据类型1.1 作用 控制数据的规范性，让数据有具体含义，在列上进行控制1.2 种类1) 字符串 char(32)...
    浪子_lucy阅读 152评论 0赞 0
  • DBA-70-day04
    接昨天----->>> 9. DCL grant revoke 10. DML语句 10.1 作用： 针对表的数据...
    StandingBy_abc阅读 166评论 0赞 0
  • 家长会谈合作
    久违的晴天，家长会。 家长大会开好到教室时，离放学已经没多少时间了。班主任说已经安排了三个家长分享经验。 放学铃声...
    飘雪儿5阅读 7,454评论 16赞 22