mysql基础（安装-配置-使用）

mysql-DBA笔记

mysql-DBA笔记

前言数据库产品介绍

什么是数据

人类认为的数据：抽象出来的，图片，视频，文字，表格，数字。

计算机：2进制，16进制。

DBMSS数据库管理系统

RDBMS：关系型数据库管理系统

特点：二维表结构，提供很丰富的查询功能，高一致性，性能相对较差。

NoSQL：

特点：无结构话存储数据，性能较高

NewSQL：

PinCAP TiDB

Aliyun PolarDB + OB

Tencent TDSQL

面试题

1）笔试：介绍你了解过的数据库产品？

RDBMS：MySQL，Oracle，MSSQL，PG.

NoSQL：Redis，Memcached，MongoDB，ES.

NewSQL：NewSQL，PolarDB，TDSQL.

2）面试：你们公司都用了哪些数据库产品或者你用过哪些数据库产品？

运维：MySQL 或者 RDS.

DBA：MySQL，Redis，MongoDB，ES，Oracle.

MySQL 企业版本选择

Oracle MySQL产品线

E版：企业版，收费版。

C版：社区版，开源的。

社区版

DMR，RC：测试开发板。

GA：稳定发布版。

企业版本选择

5.5：7-8%

5.6：50%，占比最大（5.6.3x：5.6.34 - 5.6.42）

5.7：40%，（5.7.2x：5.7.20 - 5.7.26）

8.0：2-3%，（8.0.11+，8.0.17以上更好）

MySQL的获取

5.6.38 5.7.20是在2017.9.13GA的

GA版本一般是在6-12月的

例如下载mysql的5726版本（https://mysql.com）

预言：8.0，至少未来10年，都是主流版本.（---oldguo）

同源产品

Oracle MySQL：5.0 5.1 5.5 5.6 5.7 8.0 9.0

Mariadb：5.0 5.1 5.5 10.x ...

percona：5.0 5.1 5.5 10.x ...

第一章 MySQL-8.0版本二进制版本部署

1.1 系统的准备

网络配置：ip，hostname，iptables，selinux

规划目录：binlog

上传软件至/usr/local

[root@db01 local]# mkdir -p /date/mysql/data_3306     #数据路径
[root@db01 local]# mkdir -p /date/mysql/binlog_3306   #binlog日志路径
[root@db01 local]# tar xf mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz

用户创建

[root@db01 local]# useradd mysql

授权

[root@db01 local]# ln -s mysql-8.0.16-linux-glibc2.12-x86_64 mysql8
[root@db01 local]# chown -R mysql.mysql /usr/local/mysql8 /data

环境变量

vim /etc/profile
添加:
export PATH=/usr/local/mysql8/bin:$PATH

[root@db01 bin]# source /etc/profile

[root@db01 bin]# mysql -V
mysql  Ver 8.0.16 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)

残留处理

[root@db01 bin]# rpm -qa |grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@db01 bin]# yum remove mariadb-libs -y

依赖包安装

[root@db01 local]# yum install libaio-devel -y

数据初始化（建系统库）

[root@db01 data_3306]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql8 --datadir=/data/mysql/data_3306

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

二进制部署mysql-5.7.26

#清空8.0环境,部署MySQL5.7
pkill mysqld 
rm -rf /data/mysql/data_3306/* 
chkconfig --del  mysqld
[root@db01 local]# tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz 
[root@db01 local]# ln -s mysql-5.7.26-linux-glibc2.12-x86_64 mysql57
vim /etc/profile 
export PATH=/usr/local/mysql57/bin:$PATH
[root@db01 local]# source /etc/profile
[root@db01 local]# cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
port=3306
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3306
server_id=6
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

vim /etc/profile 
export PATH=/usr/local/mysql57/bin:$PATH
[root@db01 local]# source /etc/profile
[root@db01 local]# chown -R mysql.mysql /usr/local/mysql57
[root@db01 local]# chown -R mysql.mysql /data

[root@db01 data_3306]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3306

第二章体系结构与基础管理

2.1 MySQL工作模型

C/S：客户端/服务端模型

服务端程序 mysqld
客户端程序 mysql mysqldump mysqladmin ......
连接协议

网络套接字：TCP/IP网络连接串 ---->mysql -uroot -p123 -h10.0.0.51 -P3306

本地套接字：socket本地连接串 ---->mysql -uroot -p123 -S /tmp/mysql.sock

2.2 MySQL的实例

实例 = mysqld + Master Thread + worker Threads + 专用内存

2.3 mysqld程序结构

1）SQL是什么：结构化查询语言，关系型数据库中的专用命令。

2）SQL种类：

DDL：数据定义

DCL：数据控制

DML：数据操作

DQL：数据查询

3）一条SQL语句的执行逻辑

2.4 MySQL逻辑结构

库、表......

库：database / schema

表：table（二维表结构）

表名、表属性、字段(列/表结构)、数据行(记录)

2.5 MySQL对象物理结构

1）宏观：一个库对应一个目录

user.frm：表结构

user.MYD：数据行

user.MYI：索引

time_zone.frm：表结构

time_zone.ibd：数据行 + 索引

2）微观：

每一个数据表（segment段）包含有extent区（默认1M）包含一个page页（默认16KB，一个extent区包含64个page），一个page包含4个连续的os block（默认4K），一个os block包含连续的8个扇区（默认512B）

2019.12.16回顾
(1) 数据库种类
RDBMS: MySQL
NoSQL: Redis,MongoDB,ES
NewSQL

(2) MySQL版本选择
小版本: 5.7版本建议安装5.7.23以上. 8.0版本建议安装8.0.11版本以上

(3) 安装
5.7版本以上的初始化数据：
mysql --initialize --user=mysql --basedir=/usr/local/mysql8 --datadir=/data/mysql/data_3306  #安全的、初始化成功后，提示修改密码

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql8 --datadir=/data/mysql/data_3306  #不安全的初始化，初始化后可直接登录

(4) 体系结构
一条SQL语句的执行
1.连接层：
	连接协议：TCP, Socket
	验证：
		native: 8.0以前验证方式
		sha2：8.0以后验证方式
	连接线程：接收SQL，返回结果
	show processlist;  #监控连接的数量, 统计并发情况
2.SQL：
	语法
	语义
	权限
	解析   ---->执行计划树
	预处理 ---->代价计算(cost)
	优化器 ---->选择代价第低的
	执行器 ---->结果-->段-->区-->页
	查询缓存QC(query_cache)
		案例：5.7.23版本, 按月我 分区表, 做压力测试
		开启QC之前：
			select * from a where aa=xxx
			QPS 3000+
		开启之后：
			select * from a where aa=xxx;
			QPS 600+
		热点数据, 可以用Redis缓存数据库承当. 
	日志记录(按需开启)
		binlog
		general_log
		
3.存储引擎层
	去系统找到需要的数据页, 返回给SQL, 结构化数据成表.

MySQL存储物理结构
	段：一个表就是一个段, 由1个或多个区构成
	区：连续的64个page, 默认是1M, 最小的存储分配单元
	页：连续的4个OS block, 默认是16KB, 最小的IO单元

2.6 MySQL基础管理

1.用户管理

作用：
- 登录
- 管理对象

定义：用户名@'白名单'. 白名单：可以连接黑名单：不允许连接

用户名：不要太长，和业务有关

emp_user01

grant all on *.* to wordpress '%' identified by '123'
白名单

user@'10.0.0.56'
user@'%'
user@'10.0.0.%'   255.255.255.0   24位/24掩码
user@'10.0.0.0/255.255.254.0'     23位掩码
user@'10.0.0.5%'    50-59      
user@'localhost'  本地

#常用
user@'10.0.0.%'
user@'10.0.0.0/255.255.254.0'  23位
user@'10.0.0.5%' 50-59
user@'localhost' 本地

用户管理：mysql> create user oldguo@'10.0.0.%' identified by '123';
查询用户：mysql> select user,host from mysql.user;
查询用户密码：mysql> select user,host,authentication_string from mysql.user;
修改用户密码：mysql> alter user oldguo@'10.0.0.%' identified by '123456';
删除用户：mysql> drop user oldguo@'10.0.0.%';

说明：8.0+ 版本：必须先创建用户在授权； 8.0以前：可以grant授权时自动创建用户。

#演示
[root@db01 ~]# mysql -uroot -p123456
mysql> create user oldqiang@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| oldguo        | 10.0.0.%  |
| oldqiang      | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys     | localhost |
| oldguo        | localhost |
| root          | localhost |
+---------------+-----------+
6 rows in set (0.00 sec)

mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| oldguo        | 10.0.0.%  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| oldguo        | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| oldqiang      | 10.0.0.%  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+---------------+-----------+-------------------------------------------+
6 rows in set (0.00 sec)
mysql> alter user oldqiang@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql> drop user oldqiang@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

2.权限管理

1）MySQL权限列表：mysql> show privileges;

2）授权和回收权限：

GRANT (all权限) ON *.*(权限作用范围) to(用户) wordpress '%' IDENTIFIED BY '123' with grant option;

权限：ALL, Slect, Reload...

权限作用范围：

*.* ---> 一般是管理员会设置的方法；

oldguo.* ---> 一般是业务用户会设置的方法；

oldguo.t1 ---> 一般是业务用户设置的方法

3）企业授权案例：

① 授权一个管理员用户wyq，可以从10网段任意地址登录管理数据库

mysql> grant all on *.* to wyq@'10.0.0.%' identified by '123' with grant option; ####   给wyq这个用户一个10.0.0.%网段的管理员权限。

② 授权一个业务用户app，可以从10网段地址访问app库的所有表

mysql> grant Usage, Update on app.* to app@'10.0.0.%' identified by '123' with grant option;

③ 授权一个开发用户dev，可以对dev库进行业务开发。

###具体视公司业务设置权限

4）root管理员密码忘记或被篡改如何处理?

--skip-grant-tables ##关闭验证模块

--skip-networking ##关闭端口（跳过tcp的连接协议）

#关闭数据库，并启动到单用户模式
[root@db01 data_3306]# systemctl stop mysqld
[root@db01 data_3306]# mysqld_safe  --skip-grant-tables  --skip-networking  &

#无密码登录MySQL并修改密码
mysq
mysql> flush privileges;    ###授权语句加载到内存。
mysql> alter user root@'localhost' identified by '123456';

#重启数据库到正常模式
[root@db01 data_3306]# systemctl restart mysqld
[root@db01 data_3306]# mysql -uroot -p123456

5）查询用户权限

mysql> show grants for app@'10.0.0.%';
+---------------------------------------------------------------------+
| Grants for app@10.0.0.%                                             |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'app'@'10.0.0.%'                              |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `app`.* TO 'app'@'10.0.0.%' |
+---------------------------------------------------------------------+

6）回收权限

mysql> revoke delete,drop on app.* from 'app'@'10.0.0.%';

3.MySQL的连接管理

3.1 自带客户端工具

mysql

-u  用户名
-p  密码
-h  IP
-P  端口
-S  socket位置
-e  免交互
<   导入SQL脚本

#例子
1.TCP连接串远程登录
注：需提前创建用户
例子: 
(1) TCP连接串远程登录
注:需要提前创建好用户
mysql> grant all on *.* to oldguo@'10.0.0.%' identified by '123';
[root@db01 data_3306]# mysql -uoldguo -p -h 10.0.0.51 -P 3306
Enter password:
(2) socket连接方式
注:需要提前创建好localhost用户
mysql> grant all on *.* to oldguo@'localhost' identified by '123';
[root@db01 data_3306]# mysql -uoldguo -p -S /tmp/mysql.sock 
Enter password:

###思考？如何查看是通过端口号还是通过远程连接的
mysql> show processlist;
+----+--------+------------+------+---------+------+----------+------------------+
| Id | User   | Host       | db   | Command | Time | State    | Info             
+----+--------+------------+------+---------+------+----------+------------------+
|  9 | root   | localhost  | NULL | Sleep   |  128 |          | NULL             
| 12 | oldguo | db01:60754 | NULL | Query   |    0 | starting | show processlist 
+----+--------+------------+------+---------+------+----------+------------------+
3 rows in set (0.00 sec)

db01:60754   ###表示远程连接
 localhost   ###表示本地连接
(3)免交互式的登录
[root@db01 ~]# mysql -uroot -p -e "show processlist"
(4)导入sql脚本--->两种方法
① mysql> source /root/world.sql
② [root@db01 ~]# mysql -uroot -p </root/world.sql

mysqladmin

①修改密码-->必须知道原密码

 [root@db01 ~]# mysqladmin -uroot -p123456 password 123
 [root@db01 ~]# mysql -uroot -p123

②关闭数据库

[root@db01 ~]# mysqladmin -uroot -p123 shutdown

mysqldump(备份恢复的时候讲---此处略过)

2.第三方开发工具

sqlyog ----- >在软件包打开连接就行----->新建---->10.0.0.51 oldguo 123

navicat ------>在软件包安装打开连接用就行

workbench ------>网上下载自行学习。

3.应用程序连接

php-mysql ###驱动程序一般取官方网站的

pip3 install mysql

jar

4.MySQL的启动关闭--->启动的是实例

###mysql 的启动流程
systemctl start mysqld--->support-files/mysql.server--->mysqld_safe--->mysqld

启动方式：

systemctl ---->mysql.server start ----->mysqld_safe ---->mysqld（service启动方式）

mysql_safe/mysqld

5.MySQL的初始化配置

①配置方法（优先级）

源码安装定制 < -----初始化配置文件 <-----命令行启动时定制 (三种启动方式优先级)

②5.2 初始化配置文件

[root@db01 ~]# mysqld --help --verbose|grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 
                      my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default   ##依次从左到右读取，后面的会覆盖前面的
                      
建议一个mysql实例一个配置文件即-->my.cnf

③5.3 配置文件书写格式

[root@db01 data_3306]# cat /etc/my.cnf 
[mysqld]                              ### 标签             
user=mysql
port=3306
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3306
server_id=6
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

标签项  --->[mysqld]
服务器端：[mysqld],[mysqld_safe]  ---> 影响到MySQL启动
客户端：  [clinet],[mysql],[mysqldump]  ---> 影响本地客户端程序

配置项 ----->key=value

④命令行（mysqld，mysqld_safe）

--default
--skip-grant-tables
--skip-networking

⑤化配置文件

mysqld --defaults-file=/opt/a.cnf & (需提前准备配置文件)

6.多实例的规划和配置

分布式架构中应用非常广泛

[root@db01 data_3306]# mkdir -p /data/mysql/data_{3307,3308,3309}

2.配置文件准备

cat > /data/mysql/my3307.cnf <<EOF
[mysqld]
user=mysql
port=3307
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3307
server_id=7
socket=/tmp/mysql3307.sock
EOF
cat > /data/mysql/my3308.cnf <<EOF
[mysqld]
user=mysql
port=3308
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3308
server_id=8
socket=/tmp/mysql3308.sock
EOF
cat > /data/mysql/my3309.cnf <<EOF
[mysqld]
user=mysql
port=3309
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3309
server_id=9
socket=/tmp/mysql3309.sock
EOF

3.授权

[root@db01 data_3306]# chown -R mysql.mysql /data/

4.初始化数据

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3307
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3308
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3309

5.启动多实例

[root@db01 mysql]# mysqld --defaults-file=/data/mysql/my3307.cnf &
[root@db01 mysql]# mysqld --defaults-file=/data/mysql/my3308.cnf &
[root@db01 mysql]# mysqld --defaults-file=/data/mysql/my3309.cnf &
[root@db01 mysql]# netstat -tulnp

6.使用systemd管理多实例

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/mysql57/bin/mysqld --defaults-file=/data/mysql/my3307.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/mysql57/bin/mysqld --defaults-file=/data/mysql/my3308.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/mysql57/bin/mysqld --defaults-file=/data/mysql/my3309.cnf
LimitNOFILE = 5000
EOF

#启动
pkill mysqld
systemctl restart mysqld
systemctl start mysqld3306
systemctl start mysqld3307
systemctl start mysqld3308

第三章 SQL基础

1.SQL介绍

1.1 简介

结构化查询语言

1.2 SQL标准

SQL89 SQL92 SQL99 SQL03 SQL05

1.3 SQL_MODE

限制除数为零5.5 5.6版本

约束日期规范，防止出现零年零月零分

mysql> select @@sql_mode;

1.4 SQL类型

DDL：数据定义语言：库名，库属性，表名，表属性，列(列名，列属性)，数据行

DCL：数据控制语言：权限

DML：数据操作语言：数据行

DQL：数据查询语言：数据行

1.5 SQL功能

管理，操作数据库对象：逻辑对象：库，表

库：库名，库属性

表：表名，表属性，列(列名，列属性)，数据行都可以被定义（创建、删除、修改）

MySQL规范性存储限制

2.1 字符集Charset

面试：请你说说utf8和utf8mb4的区别？

utf8：最大字节长度3个：3个字节

utf8mb4：最大字节长度4个，可以存储emoji表情字符.：4个字符；

mysql> show charset;

2.2 排序规则校对规则

show collation；

默认是大小写不敏感

utf8mb4 general_ci

utf8mb4_bin

2.3 数据类型

①数字类型

	tinyint：1字节可以存储255个数字 ===>11111111===>0-2^8-1===>-2^7-2^7-1(3位)  
	int：4字节长度  ===>0-2^32=>-2^32-2^32-1（10位数）
	bigint：8字节长度  ===>0-2^64-1 ===>-2^63-2^63-1（20位数）

②字符串类型

char(10): 定长，最多存储10个字符，占用存储空间一定，最多存储255个字符
char(64): 定长，最多存储64个字符，占用存储空间一定，最多存储255个字符
varchar(10): 最多存储65535个字符
	变长类型，最多10个，按需分配存储空间
	需要额外1个字符或2个字符存储字符长度
	因素：变长的字符串列，90%几率都是varchar
	具体的原因：	节省空间
enum('m','f'):

③时间类型

datetime：范围为从1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999
TIMESTAMP:1970-01-01 00:00:00.000000 至 2038-01-19 .3:14:07.999999
timestamp会受到时区的影响.

④二进制类型

⑤JSON(8.0)

day03

1、约束---（身份证号？一样？不可以重复？有些数据是必须填的？怎么控制）

1.1 PrimaryKey(PK):主键

特点：唯一且非空，一张表中只能有一个主键约束.一般是一一个数字列，最好的是无意义的。

主要特点：唯一且非空

1.2 NOT NULL：非空约束

特点：不能为空，必须录入值。我们建议，业务关键列，特别是索引，尽量设置非空。

1.3 UNIQUE 唯一约束

特点：不能有重复值，一般像手机号，身份证号，邮箱，联系方式，id都是可以设定为UNIQUE的

1.4 unsigned 数字列。（年龄是负的？）

特点：必须要加载数字列后，表示数字无负数。

2、其他属性

2.1 AUTO_INCREMENT 自增长特点：适用于ID主键列（序号自己增长）

2.2 DEFAULT 默认值（年龄不想让别人知道）

特点：使用在NOT NULL列中，不填值得时候设定为默认值。

2.3 COMMENT 注释（更容易理解业务属性，是干什么的，让表的数据更加具体化）

特点：建议每个列都有一个注释。

10张表： ----每个列加一个注释。------让运维快速熟悉公司业务。

3、DCL：数据控制语言：权限

grant

revoke

4、DDL应用(数据定义语言：库名，库属性，表名，表属性，列(列名，列属性)，数据行)

4.1 库的定义

4.1. 库

### 增加库

CREATE DATABASE oldguo CHARSET utf8mb4 COLLATE utf8mb4_bin;

### 删除库  (危险，不代表生产操作)

DROP DATABASE oldguo;

### 改库

ALTER DATABASE oldguo CHARSET utf8mb4 COLLATE utf8mb4_bin;

### 查库（DQL）

mysql> show databases;
mysql> show create database oldboy;

4.1.5 规范

①库名要和业务有关

②库名不能有大写字母，可以有小写字符，数字，特殊符号。

为什么?---->不规范
CREATE DATABASE oldguo CHARSET utf8mb4 COLLATE utf8mb4_bin;

③库名不能以数字开头

④库名不能是预留字符（不能创建个database）

create database table; ---->不能创建

（5）不要超过18个字符。

（6）必须设置字符集，尽量是utf8mb4。

⑦收回所有用户的DROP权限。

4.2表定义

4.2.1增
create table
4.2.2
drop table
4.2.3
alter table
4.2.4
show tables
show create table xx
mysql> desc teacher
    -> ;

开发角度和dba角度规划

# 学生表
CREATE TABLE `xuesheng` (
  `xid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '学号',
  `xname` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '姓名',
  `xage` tinyint(3) unsigned NOT NULL DEFAULT '99' COMMENT '年龄',
  `xsex` char(1) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '性别',
  `xtel` char(14) COLLATE utf8mb4_bin NOT NULL COMMENT '手机号',
  `xcard` char(18) COLLATE utf8mb4_bin NOT NULL COMMENT '身份证号',
  `xaddr` enum('北京市','上海市','深圳市','山东省','甘肃省','河北省','山西省','河南省','辽宁省','吉林省','黑龙江省','内蒙古自治区','新疆维吾尔自治区','四川省','陕西省','江苏省','福建省','湖北省','广东省','广西省') COLLATE utf8mb4_bin NOT NULL DEFAULT '北京市' COMMENT '地区',
  `xdate` datetime DEFAULT NULL COMMENT '入学时间',
  PRIMARY KEY (`xid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;  

xid   xname   xsex  xtel  xcard  xaddr   xdate
1     张三    m     110    123   北京市  2019-01-01
2     马六    m     110    123   北京市  2019-01-01
3     李四    m     110    123   北京市  2019-01-01
4     王五    m     110    123   北京市  2019-01-01
5     铁锤    m     110    123   北京市  2019-01-01
6     钢蛋    m     110    123   北京市  2019-01-01
7     孙悟空  m     110    123   北京市  2019-01-01
8     猪八戒  m     110    123   北京市  2019-01-01
9     唐僧    m     110    123   北京市  2019-01-01
10    沙僧    m     110    123   北京市  2019-01-01
11    白龙马  m     110    123   北京市  2019-01-01
12    牛魔王  m     110    123   北京市  2019-01-01
13    张无忌  m     110    123   北京市  2019-01-01
14    赵敏    m     110    123   北京市  2019-01-01
15    郭靖    m     110    123   北京市  2019-01-01
16    黄蓉    m     110    123   北京市  2019-01-01
17    小龙女  m     110    123   北京市  2019-01-01
18    杨过    m     110    123   北京市  2019-01-01
19    欧阳峰  m     110    123   北京市  2019-01-01
20    小沈阳  m     110    123   北京市  2019-01-01


cid  cname  tid    cprice 
1001 linux  t0001    19800  
1002 python t0002    21800  
1003 golang t0003    16000  
1004 DBA    t0004    15000  
1005 safe   t0005    17800  

CREATE TABLE course (
cid  INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(64) NOT NULL COMMENT '课程名称',
tid   CHAR(5) NOT NULL COMMENT '讲师名',
cprice INT NOT NULL COMMENT '课程价格'
)ENGINE=INNODB CHARSET=utf8mb4;

# 练习 
# 教师表
teacher
tid    tname  tage tsex tyear txl   tstar 
t0001  oldboy 48    m    20   本科  10
t0002  oldxu  28    m    6    本科  6
t0003  oldq   30    m    6    本科  6
t0004  oldguo 32    m    10   本科  6
t0005  oldzh  32    m    10   本科  6

CREATE TABLE teacher (
tid CHAR(5) NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(64) NOT NULL  COMMENT '教师姓名',
tage TINYINT NOT NULL  DEFAULT 99 COMMENT '教师年龄',
tsex CHAR(1) NOT NULL DEFAULT 'm' COMMENT '教师性别',
tyear TINYINT NOT NULL  DEFAULT 3 COMMENT '工作年限',
txl  VARCHAR(64) NOT NULL DEFAULT '本科' COMMENT '学历',
tstar TINYINT NOT NULL DEFAULT 5 COMMENT '级别:1-10'
)ENGINE=INNODB CHARSET=utf8mb4;


# 成绩表 
CREATE TABLE score (
xid INT NOT NULL COMMENT '学生编号',
cid INT  NOT NULL COMMENT '课程编号',
socre INT NOT NULL DEFAULT 0 COMMENT '课程分数',
quekao TINYINT NOT NULL DEFAULT 0 COMMENT '是否缺考:1缺考,0未缺考'
)ENGINE=INNODB CHARSET=utf8mb4;



score 
xid     cid    score quekao
1        1001   80     0
1        1002   70     0
2        1001   0      1
2        1003   90     0
3        1004   80     0
4        1004   100    0 
5        1005   60     0 
4        1005   30     0 
5        1002   60     0 
6        1002   45     0 
7        1003   67     0 
7        1004   98     0 
8        1004   76     0 
9        1001   80     0  
10       1002   99     0 
11       1003   40     0
11       1004   50     0
12       1005   0      1
12       1003   90     0
13       1001   30     0
14       1002   100    0 
15       1003   60     0 
14       1004   30     0 
15       1005   60     0 
16       1002   45     0 
17       1003   67     0 
17       1004   98     0 
18       1004   76     0 
19       1005   75     0  
20       1002   68     0

建表需要注意的事项--规范化>

①表名: 不能大写字母, 和业务有关,不能数字开头,长度控制在18字符以内,不能和关键字同名.

2、要设置存储引擎类型: INNODB,要设置字符集

③列名要有意义

④合适的，完整的，简短的数据类型. (会影响到索引的性能)

⑤每个表要有主键,实在不知道怎么设置,也要找一个无关的自增长列设置为主键.
⑥尽量每个列都有not null (特别是将来要做为索引的列)
⑦每个列要有注释信息

mysql> show tables;
mysql> desc teacher
    -> ;
mysql> show tables;

mysql> create table teacher_bak like teacher;    ###模糊创建一个teacher表
Query OK, 0 rows affected (0.02 sec)

mysql> desc teacher;
mysql> desc teacher_bak;
mysql> drop table teacher_bak;
Query OK, 0 rows affected (0.14 sec)

4.2.2 删(危险! 谨慎操作!)
mysql> drop table teacher_bak;
mysql> truncate table teacher;
面试: 请你说明 drop table truncate delete table 区别 ?
drop table : 表结构+数据(物理性删除)
truncate table : 数据(清空数据页)
delete from table: 清空数据行(逐行删除)

(1) 添加列 
DESC xuesheng;
ALTER TABLE xuesheng ADD xqq  BIGINT  NOT NULL UNIQUE COMMENT 'qq号';
ALTER TABLE xuesheng ADD wechat  BIGINT  NOT NULL UNIQUE COMMENT '微信号' AFTER xtel;
ALTER TABLE xuesheng ADD mail  BIGINT  NOT NULL UNIQUE COMMENT '邮箱' FIRST'

(2) 删除列
ALTER TABLE xuesheng DROP mail;
ALTER TABLE xuesheng DROP xqq;
ALTER TABLE xuesheng DROP wechat;
ALTER TABLE xuesheng DROP mail;

(3) 修改
1. 修改表名:
ALTER TABLE xuesheng RENAME TO student;
面试题: 
上亿行的数据规划:
		1. 按月归档表
		2. 没用的历史表进行挪走或删除
pt-archiver  自己扩展 *****

2. 修改某一列的属性信息
DESC student;
ALTER TABLE student MODIFY xname VARCHAR(128) NOT NULL COMMENT '姓名';

3. 修改列名和属性
ALTER TABLE student CHANGE xsex xgender CHAR(2) NOT NULL DEFAULT 'm' COMMENT '性别';

注意事项：

执行alter语句，都是需要进行锁表操作的，此时只能发生查询操作，不能做修改操作。

我们建议，alter语句，尽量在业务不繁忙期间发生。如果非得线上操作,建议使用pt-osc工具进行，

4.2.4 查
show tables ;
desc teacher;
show create table xx;

作业：

自己了解 pt-osc pt-archiver

percona-toolkit --- - >www . percona. com

5、DML 数据操作语言

5.1 insert 表中插入数据

# 学生表:student
drop table student;
CREATE TABLE `student` (
  `xid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '学号',
  `xname` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '姓名',
  `xage` tinyint(3) unsigned NOT NULL DEFAULT '99' COMMENT '年龄',
  `xsex` char(1) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '性别',
  `xtel` char(14) COLLATE utf8mb4_bin NOT NULL COMMENT '手机号',
  `xcard` char(18) COLLATE utf8mb4_bin NOT NULL COMMENT '身份证号',
  `xaddr` enum('北京市','上海市','深圳市','山东省','甘肃省','河北省','山西省','河南省','辽宁省','吉林省','黑龙江省','内蒙古自治区','新疆维吾尔自治区','四川省','陕西省','江苏省','福建省','湖北省','广东省','广西省') COLLATE utf8mb4_bin NOT NULL DEFAULT '北京市' COMMENT '地区',
  `xdate` datetime DEFAULT NULL COMMENT '入学时间',
  PRIMARY KEY (`xid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;  

INSERT INTO student(xid,   xname , xage    ,xsex , xtel , xcard  ,xaddr ,xdate)
VALUES
(1 ,    '张三'   , 11  ,   'm'  ,   '110'  ,  '660',   '北京市',            '2019-01-01'),
(2 ,    '马六'   , 13  ,   'm'  ,   '111'  ,  '661',   '上海市',            '2019-01-01'),
(3 ,    '李四'   , 14  ,   'm'  ,   '112'  ,  '662',   '北京市',            '2019-01-01'),
(4 ,    '王五'   , 17  ,   'm'  ,   '113'  ,  '663',   '山东省',            '2019-01-01'),
(5 ,    '铁锤'   , 18  ,   'f'  ,   '114'  ,  '664',   '河南省',            '2019-01-01'),
(6 ,    '钢蛋'   , 13  ,   'f'  ,   '115'  ,  '665',   '河北省',            '2019-01-01'),
(7 ,    '孙悟空' , 19  ,   'm'  ,   '116'  ,  '666',   '山西省',            '2019-01-01'),
(8 ,    '猪八戒' , 21  ,   'm'  ,   '117'  ,  '667',   '河北省',            '2019-01-01'),
(9 ,    '唐僧'    ,23   ,  'm'   ,  '118'   , '668' ,  '吉林省',            '2019-01-01'),
(10,    '沙僧'    ,31   ,  'm'   ,  '120'   , '669' ,  '辽宁省',            '2019-01-01'),
(11,    '白龙马'  ,26   ,  'm'   ,  '119'   , '670' ,  '广西省',           '2019-01-01') ,
(12,    '牛魔王'  ,19   ,  'm'   ,  '121'   , '671' ,  '四川省',            '2019-01-01'),
(13,    '张无忌'  ,20   ,  'm'   ,  '122'   , '672' ,  '福建省',            '2019-01-01'),
(14,    '赵敏'    ,28   ,  'f'   ,  '123'   , '673' ,  '广东省',            '2019-01-01'),
(15,    '郭靖'    ,29   ,  'm'   ,  '124'   , '674' ,  '甘肃省',            '2019-01-01'),
(16,    '黄蓉'    ,17   ,  'f'   ,  '125'   , '675' ,  '深圳市',            '2019-01-01'),
(17,    '小龙女'  ,22   ,  'f'   ,  '126'   , '766' ,  '黑龙江省',     	   '2019-01-01'),
(18,    '杨过'    ,33   ,  'm'   ,  '127'   , '777' ,  '新疆维吾尔自治区',  '2019-01-01'),
(19,    '欧阳峰'  ,25   ,  'm'   ,  '128'   , '888' ,  '内蒙古自治区',      '2019-01-01'),
(20,    '小沈阳'  ,23   ,  'm'   ,  '129'   , '999' ,  '陕西省',            '2019-01-01');

SELECT * FROM student;

# 课程表:course
drop table course;
CREATE TABLE course (
cid  INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(64) NOT NULL COMMENT '课程名称',
tid   CHAR(5) NOT NULL COMMENT '讲师名',
cprice INT NOT NULL COMMENT '课程价格'
)ENGINE=INNODB CHARSET=utf8mb4;

insert into course(cid,  cname , tid    ,cprice )
values
(1001, 'linux'  ,'t0001' ,19800),  
(1002, 'python' ,'t0002' ,21800),  
(1003, 'golang' ,'t0003' ,16000),  
(1004, 'DBA'    ,'t0004' ,15000),  
(1005, 'safe'   ,'t0005' ,17800);  


# 教师表 : teacher
CREATE TABLE teacher (
tid CHAR(5) NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(64) NOT NULL  COMMENT '教师姓名',
tage TINYINT NOT NULL  DEFAULT 99 COMMENT '教师年龄',
tsex CHAR(1) NOT NULL DEFAULT 'm' COMMENT '教师性别',
tyear TINYINT NOT NULL  DEFAULT 3 COMMENT '工作年限',
txl  VARCHAR(64) NOT NULL DEFAULT '本科' COMMENT '学历',
tstar TINYINT NOT NULL DEFAULT 5 COMMENT '级别:1-10'
)ENGINE=INNODB CHARSET=utf8mb4;


# 成绩表 : score
DROP TABLE score;
CREATE TABLE score (
xid INT NOT NULL COMMENT '学生编号',
cid INT  NOT NULL COMMENT '课程编号',
score INT NOT NULL DEFAULT 0 COMMENT '课程分数',
quekao TINYINT NOT NULL DEFAULT 0 COMMENT '是否缺考:1缺考,0未缺考'
)ENGINE=INNODB CHARSET=utf8mb4;
INSERT INTO score(xid  ,   cid  ,  score ,quekao) 
VALUES
(1        ,1001   ,80     ,0),
(1        ,1002   ,70     ,0),
(2        ,1001   ,0      ,1),
(2        ,1003   ,90     ,0),
(3        ,1004   ,80     ,0),
(4        ,1004   ,100    ,0), 
(5        ,1005   ,60     ,0), 
(4        ,1005   ,30     ,0), 
(5        ,1002   ,60     ,0), 
(6        ,1002   ,45     ,0), 
(7        ,1003   ,67     ,0), 
(7        ,1004   ,98     ,0), 
(8        ,1004   ,76     ,0), 
(9        ,1001   ,80     ,0),  
(10       ,1002   ,99     ,0), 
(11       ,1003   ,40     ,0),
(11       ,1004   ,50     ,0),
(12       ,1005   ,0      ,1),
(12       ,1003   ,90     ,0),
(13       ,1001   ,30     ,0),
(14       ,1002   ,100    ,0), 
(15       ,1003   ,60     ,0), 
(14       ,1004   ,30     ,0), 
(15       ,1005   ,60     ,0), 
(16       ,1002   ,45     ,0), 
(17       ,1003   ,67     ,0), 
(17       ,1004   ,98     ,0), 
(18       ,1004   ,76     ,0), 
(19       ,1005   ,75     ,0),  
(20       ,1002   ,68     ,0);

5.2 update
SELECT * FROM student;
UPDATE student SET xname='王钢蛋' WHERE xid=6;
UPDATE student SET xname='李铁锤' WHERE xid=5;

5.3 delete
INSERT INTO student VALUES(21,'王二麻子',22,'f','921','345','上海市','2020-01-01');
DELETE FROM student WHERE xid=21;

扩展:

伪删除
(1) 添加状态列 is_del (1代表删除,0代表有效)
ALTER TABLE student ADD is_del TINYINT NOT NULL DEFAULT 0 COMMENT '1代表删除,0代表有效';
SELECT * FROM student;

(2) delete ---> update
原语句:
delete from student where xid=20;

改为 :
update student set is_del=1 where xid=20;

(3) 更改业务查询方法
原语句:
SELECT * FROM student;
改为:
SELECT * FROM student where is_del=0;

作业:
percona-toolkit ---->www.percona.com
pt-archiver 自己扩展
pt-osc 自己扩展

day04

###day03回顾
1. create table  like 只能复制表结构,那有什么命令可以连数据一起复制?

(1) 方法一:
create table stu select * from  student;
主键等特性没有被复制.

(2) 方法二:
create table st  like student;
insert into st select * from student  

2. pt 索引章节之后
pt-archivher    
pt-osc


3. 主键是干什么用的
(1) 约束 : unique not null
(2) 聚簇索引索引: 组织和存储数据.
(3) 加速查询


4. update 和 delete 范围操作

update t1 set name='王二麻子' where name like '王二%'
delete from t1  where name like '王二%';
delete from t1  where id >1  and id <5 ;
delete from t1  where id between 1 and 5 ;  >=1  <=5

DQL 数据库查询语言
select
SELECT

1.1 SELECT 单独使用

1. (1) 查询数据库的参数..
SELECT @@port;       #查询数据库的端口
SELECT @@datadir;    #查询数据库的路径
SELECT @@basedir;    #@@basedir   /usr/local/mysql-5.7.26-linux-glibc2.12-x86_64/
SELECT @@innodb_flush_log_at_trx_commit;
SHOW VARIABLES LIKE '%TRX%';
(2) 调用内置函数
USE oldguo
SELECT DATABASE() ##查看当前所在的库
SELECT NOW();  ##查看当前时间
SELECT USER,HOST FROM mysql.user;
SELECT CONCAT (USER,"@",HOST) FROM mysql.user;   ##拼接打印出root@loscalhost
SELECT GROUP_CONCAT(xid) FROM student;
SELECT SUM(xid) FROM student;
   
(3) 简易计算器，可以做计算。
SELECT 4*5;

1.2 SELECT 配合其他句子使用 ***

1.2.1 子句列表介绍

FROM ------ 查询对象(表,视图)
WHERE ----- 过滤子句(grep)
GROUP BY -- ---分组子句(统计分析类) 面试： GROUP BY 执行的过程
HAVING ------后过滤子句
ORDER BY -------- 排序子句
LIMIT -- 限制子句(分页子句)

(顺序--->从上到下依次执行（记住顺序））

### 面试题百分之九十面试经典案例！！！
### 面试1：GROUP BY 执行的过程：

 排序---->去重---->函数聚合
 SELECT a,count(b) from t group by a;
 首先对a排序，去重，然后执行b，函数聚合。
 ### 面试2：SQL_MODE(5.7+)=ONLY_FULL GROUPBY  ONLY_FULL GROUPBY这个是干嘛的？
mysql> SELECT name,District,SUM(population)  FROM  city WHERE countrycode='CHN'  GROUP BY Districtt;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
没有在GROUP BY后面，也没有在聚合函数里面。

1、select 后的列，不在group by 句子后
2、也不在聚合函数中出现的。

1.2.2 配合FROM应用

-- word模板库介绍
---  英文单词介绍
---  city   --城市
---  id     --序号id主键
---  NAME   --城市名
---  Countrycode    --国家代码 （CHN，USA，JPN）
---  District       --城市人口数

例子:
查询表中所有数据（cat)

### 将world.sql上传到/root目录下
mysql> source /root/world.sql
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM city;   ###查询表中所有数据

2.查询name和population信息(awk取列)
SELECT NAME,population FROM city;
mysql> SELECT NAME,population FROM city;
+-------NAME-------------------------+------population---+
| Rafah                              |      92020        |
+------------------------------------+-----------       -+

1.2.3 select+ from + where (grep)使用 ---->等值查询

----select配合where等值查询
例子:
----       
1.查询中国所有的城市信息
SELECT * FROM city
WHERE count rycode= ' CHN' ;      #列。。。怎么样。。。

2.查询ID为100的城市信息
SELECT * FROM city
WHERE id=100;

3.查询中国河北省的城市信息
SELECT * FROM city
WHERE countrycode='CHN' AND district='hebei';   ###两个必须同时满足

4、查询中国或者美国的城市
SELECT * FROM city
WHERE countrycode='CHN' OR countrycode='USA';

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

或者；
SELECT * FROM city
WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city
WHERE countrycode='USA' ;

where配合范围查询

-- 例子 : 
-- 1. 查询人口数量小于100人的城市
SELECT * FROM city
WHERE population<100;
-- 2. 查询人口数量100w-200w之间的
SELECT * FROM city 
WHERE population>=1000000 AND population<=2000000 ;

或者:
SELECT * FROM city 
WHERE population BETWEEN 1000000 AND 2000000 ;

-- 3. 查询国家代号是CH开头的城市信息
SELECT * FROM city 
WHERE countrycode LIKE 'CH%';   ###注意百分号在后边；

mysql> SELECT * FROM city WHERE population<100;
+------+-----------+-------------+----------+------------+
| ID   | Name      | CountryCode | District | Population |
+------+-----------+-------------+----------+------------+
| 2912 | Adamstown | PCN         | –        |         42 |
+------+-----------+-------------+----------+------------+

-- 结果集显示特点: 必须是1v1,不能是1vN
-- 例子 :
-- 1. 统计一下每个国家的人口总数

SELECT countrycode,SUM(population) 
FROM  city
GROUP BY countrycode;

-- 2. 统计中国每个省的人口总数
SELECT District,SUM(population) 
FROM  city
WHERE countrycode='CHN' 
GROUP BY District;

###先走FROM语句，然后筛选出国家CHN，接着筛选出District，然后进行一对一求和执行SELECT语句。

-- 3.统计一下中国每个省的城市个数以及城市名称
SELECT  district,COUNT(NAME),GROUP_CONCAT(NAME)  FROM  city
WHERE countrycode='CHN'
GROUP BY district;

-- 4、统计每个国家城市个数
SELECT countrycode,COUNT(NAME) 
FROM  city 
GROUP BY countrycode;

###经典报错
mysql> SELECT name,District,SUM(population)  FROM  city WHERE countrycode='CHN'  GROUP BY Districtt;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql>

1.2.6 having 后判断

-- 1. 统计中国每个省的人口总数,只显示总人口数大于500w的省信息.
SELECT  district,SUM(population)  FROM  city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)  >=5000000;

1.2.7 order by 排序子句

例子：
-- 1、查询中国所有城市信息，并按照人口数从大到小排序输出。
SELECT * FROM city
WHERE countrycode='CHN'
ORDER BY population DESC;   ###DESC从大到小
-- 2. 查询中国所有城市信息,按城市名排序.
SELECT * FROM city
WHERE countrycode='CHN'
ORDER BY NAME;

-- 3. 查询中国所有省的总人口,并按总人口数从大到小排序输出.

SELECT  district,SUM(population)  FROM  city
WHERE countrycode='CHN'
GROUP BY district
ORDER BY SUM(population) DESC;

1.2.8 limit 分页限制子句

-- 1、查询中国所有省的总人口,并按总人口数从大到小排序输出.

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

-- 2、跳过第一行，查询中国所有省的总人口,并按总人口数从大到小排序输出.
SELECT  district,SUM(population)  FROM  city
WHERE countrycode='CHN'
GROUP BY district
ORDER BY SUM(population) DESC
LIMIT 1,5;

## 注意：LIMIT 谨慎使用，500w以上的表性能比较差。   ---会遍历500w 的数据。
一般会改为明确查找。

day-05

1、多表连接查询

1.1介绍
内连接 inner join
外连接 left join right join
笛卡尔 --->笛卡尔乘积一对多 a4*b4=16ab 没有on条件，自由搭配乘积。

1.2作用
聚合多张表数据,实现查询需求
查询人口数小于100人城市名,国家名,国土面积?

1.3 多表连接的语法

1.3.1 内连接

FROM A

join B

-- 1. 查询人口数小于100人城市名,国家名,国土面积?
-- 套路 :  1. 找关联表 2. 找关系列

USE world
SHOW TABLES;
DESC city;
DESC country;

SELECT 
city.name,
country.name, 
country.SurfaceArea,
city.`Population`
FROM city
JOIN country
ON city.`CountryCode`=country.`Code`
WHERE city.`Population`<100


-- 2. 统计查询每位学员的平均分

SELECT student.xid ,
student.xname,
AVG(score.score)   
FROM  student
JOIN score 
ON student.xid=score.xid
GROUP BY student.xid,student.xname 


-- 3. 统计每位学员学习了几门课
SELECT student.xid ,
student.xname,
COUNT(score.score)   
FROM  student
JOIN score 
ON student.xid=score.xid
GROUP BY student.xid,student.xname

-- 4. 查询每位老师教的课程名和价格
SELECT teacher.tname,course.`cname`,course.`cprice`
FROM teacher 
JOIN course
ON  teacher.`tid`=course.`tid`;

-- 5. 每位老师教的学生的个数和姓名列表
SELECT 
teacher.tname,
COUNT(student.xid),
GROUP_CONCAT(student.xname)
FROM student 
JOIN score 
ON student.xid=score.xid 
JOIN course
ON score.cid=course.cid
JOIN teacher 
ON course.tid=teacher.tid
GROUP BY teacher.tid,teacher.tname;

### 经典练习题
综合练习题:
-- 1、查询oldguo老师教的学生名
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
GROUP_CONCAT(student.`xname`) AS "学生列表"
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
JOIN student 
ON score.`xid`=student.`xid`
WHERE teacher.tname='oldguo'
GROUP BY teacher.tid,teacher.tname;

-- 2、查询oldguo所教课程的平均分数
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
course.cname AS "课程名",
AVG(score.score) AS "平均分"
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
WHERE teacher.tname='oldguo'
GROUP BY teacher.tid,teacher.tname,course.cname;

-- 3、每位老师所教课程的平均分,并按平均分排序
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
course.cname AS "课程名",
AVG(score.score) AS "平均分"
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
GROUP BY teacher.tid,teacher.tname,course.cname;


-- 4、查询白龙马,学习的课程名称有哪些?
SELECT 
CONCAT(student.xname,"_",student.`xid`) AS "学生姓名",
GROUP_CONCAT(course.`cname`) AS "课程列表"
FROM student 
JOIN score
ON student.xid = score.xid 
JOIN course 
ON score.`cid`=course.`cid`
WHERE student.xname='白龙马'
GROUP BY student.xid,student.xname;

-- 5、统计每位老师赚了多少钱。
SELECT teacher.`tname`,COUNT(student.`xid`),SUM(course.`cprice`)
FROM teacher
JOIN course
ON teacher.`tid` = course.`tid`
JOIN score
ON course.`cid` = score.`cid`
JOIN student
ON score.`xid` = student.`xid`
GROUP BY teacher.`tid`,teacher.`tname`
-- 5.1 统计每位老师,每门课程,分别的收入情况
SELECT 
CONCAT(teacher.`tname`,"_",teacher.`tid`) AS "讲师名",
course.`cprice`*COUNT(score.xid) AS "课程收入"
FROM teacher
JOIN course
ON teacher.`tid` = course.`tid`
JOIN score
ON course.`cid` = score.`cid`
GROUP BY teacher.`tid`,teacher.`tname` , course.`cid`;
-- 6、统计每门课程学习的人数
SELECT course.`cname`,COUNT(score.`xid`)
FROM course 
JOIN score
ON course.`cid`=score.`cid`
GROUP BY course.`cname`;
-- 7、查询oldboy老师教的学生不及格的学生名单 
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名"
,GROUP_CONCAT(CONCAT(student.xname,":",score.score))
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
JOIN student 
ON score.`xid`=student.`xid`
WHERE teacher.`tname`='oldboy' AND score.`score`<60
GROUP BY teacher.tid,teacher.tname;
-- 8、统计每位老师不及格学生名单
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名"
,GROUP_CONCAT(CONCAT(student.xname,":",score.score))
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
JOIN student 
ON score.`xid`=student.`xid`
WHERE score.`score`<60
GROUP BY teacher.tid,teacher.tname;

SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
course.`cname`,
AVG(score.`score`)
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
GROUP BY teacher.tid,teacher.tname,course.`cid`;


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

SELECT course.`cid`,MAX(score.`score`),MIN(score.`score`)
FROM course
JOIN score
ON course.`cid`=score.`cid` 
GROUP BY course.`cid`;

-- 11. 查询平均成绩大于60分的同学的学号和平均成绩.
SELECT 
CONCAT(student.`xname`,"_",student.`xid`),
AVG(score.`score`)
FROM student
JOIN score
ON student.`xid`=score.`xid`
GROUP BY student.`xname`,student.`xid`
HAVING AVG(score.`score`)>60
ORDER BY AVG(score.`score`) DESC ;

扩展:
-- 12. 统计各位老师,所教课程的及格率

SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
course.`cname`,
CONCAT(COUNT(CASE WHEN score.score>60 THEN 1  END)/COUNT(score.xid)*100,"%")
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
GROUP BY teacher.tid,teacher.tname,course.`cid`;

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

SELECT 
course.`cname` AS 课程名称,
GROUP_CONCAT(CASE WHEN score.`score` >= 85 THEN student.xname END )  AS "优秀",
GROUP_CONCAT(CASE WHEN score.`score` >=70 AND score.`score` < 85  THEN student.xname  END) AS "良好",
GROUP_CONCAT(CASE WHEN score.`score` >=60  AND score.`score` <70 THEN  student.xname  END  )AS "一般",
GROUP_CONCAT(CASE WHEN score.`score` <60  THEN  student.xname END  ) AS "不及格"
FROM student 
JOIN score
ON student.xid = score.xid 
JOIN course 
ON score.`cid`=course.`cid`
GROUP BY course.`cid`;

1.5 left/right join 外连接应用

一般应用在强制驱动表时,强制小结果集驱动大表
-- 生产中可以使用left join 强制 驱动表.尽量减少next loop的出现.
-- 为什么要强制? inner join 优化器 自动选择, 按照索引选择的几率较大
select a.name,b.telnum from a left join b on a.id=b.id where a.age>=18

1.6 补充别名的应用 .

1.6.1 列别名

SELECT 
course.`cname` AS 课程名称,
GROUP_CONCAT(CASE WHEN score.`score` >= 85 THEN student.xname END )  AS "优秀",
GROUP_CONCAT(CASE WHEN score.`score` >=70 AND score.`score` < 85  THEN student.xname  END) AS "良好",
GROUP_CONCAT(CASE WHEN score.`score` >=60  AND score.`score` <70 THEN  student.xname  END  )AS "一般",
GROUP_CONCAT(CASE WHEN score.`score` <60  THEN  student.xname END  ) AS "不及格"
FROM student 
JOIN score
ON student.xid = score.xid 
JOIN course 
ON score.`cid`=course.`cid`
GROUP BY course.`cid`;
说明:  1. 为了显示的好看.  2. 可以在 having 或 order by 子句中调用

1.6.2 表别名

SELECT 
CONCAT(te.tname,"_",te.tid) AS "教师名"
,GROUP_CONCAT(CONCAT(st.xname,":",sc.score))
FROM teacher as te 
JOIN course as  co 
ON te.`tid`=co.`tid`
JOIN score as sc 
ON co.`cid`=sc.`cid`  
JOIN student  as st
ON sc.`xid`=st.`xid`
WHERE sc.`score`<60
GROUP BY te.tid,te.tname;

SELECT 
CONCAT(te.tname,"_",te.tid) AS "教师名"
,GROUP_CONCAT(CONCAT(st.xname,":",sc.score))
FROM teacher AS te 
JOIN course AS  co 
ON te.`tid`=co.`tid`
JOIN score AS sc 
ON co.`cid`=sc.`cid`  
JOIN student  AS st
ON sc.`xid`=st.`xid`
WHERE sc.`score`<60
GROUP BY te.tid,te.tname;

show 语句的列表介绍 ***

-- 查看所有的库
show databases; 
-- 查看当前库下的所有表
show tables;
show tables from world;

-- 查看当前并发会话信息
show processlist;
show  full processlist;

-- 查看数据库支持的权限
show privileges;

-- 查看数据库参数信息
show variables 
show variables like '%trx%';

-- 查看字符集&校对规则 
show  charset;
show  collation;

-- 查看建库&建表语句
show create database world;
show create table world.city;

-- 查看用户权限 
show grants for root@'localhost';

-- 查看支持的存储引擎
show engines;

-- 查询表中索引信息
show index from world.city;

-- 查看数据库当前状态信息
show status;
show status like '%lock%';

-- 查看InnoDB引擎相关的状态信息(内存,事务,锁,线程...)
show engine innodb status\G

-- 查看二进制日志相关信息
show binary logs ;
show master status; 
show binlog events in 'xxxx';

-- 查看主从复制相关信息
show relaylog events in 'xxxx';
show slave status \G

mysql> help show ;

3. Information_schema 统计信息库

3.1 介绍: 
视图 ? 
1. 安全 : 只允许查询,不知道操作的是什么对象.
2. 方便 : 只需要简单的select语句即可使用.

3.2 作用: 
1. 方便做数据库资产统计
库\表 : 
	个数
	数据量(大小,行数)
	每张表的数据字典信息
2. 获取到Server层状态信息
3. 获取到InnoDB引擎层的状态信息 	
mysql> use information_schema
3.3 应用举例:
mysql> desc tables;

TABLES : 
TABLE_SCHEMA  : 表所在的库 
TABLE_NAME    : 表名
ENGINE        : 表的引擎      
TABLE_ROWS    : 表的行数
AVG_ROW_LENGTH: 平均行长度(字节) 
INDEX_LENGTH  : 索引占用长度(字节) 
TABLE_COMMENT : 表注释 


-- 例子: 
-- 1. 简单查询体验TABLES信息
SELECT * FROM TABLES;
-- 2. 所有业务库和表的名字.
SELECT table_schema , table_name 
FROM information_schema.tables
WHERE table_schema NOT IN  ('sys','information_schema','performance_schema','mysql');   ###排除系统库
-- 3. 统计每个业务库,表的个数和列表
SELECT table_schema , COUNT(table_name),GROUP_CONCAT(table_name) 
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
GROUP BY table_schema;
-- 4. 统计业务数据库的总数据量
SELECT SUM(table_rows * AVG_ROW_LENGTH+index_length)/1024 AS total_KB
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql');
-- 5. 每个业务库分别统计数据量
SELECT table_schema,SUM(table_rows * AVG_ROW_LENGTH+index_length)/1024 AS total_KB
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
GROUP BY table_schema
ORDER  BY total_KB DESC ;
-- 6. top 3 数据量大的表
SELECT table_schema,table_name,(table_rows * AVG_ROW_LENGTH+index_length)/1024 AS table_kb
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
ORDER BY  table_kb DESC 
LIMIT 3;
-- 7. 查询所有非INNODB的表
SELECT table_schema,table_name ,ENGINE FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
AND ENGINE <> 'innodb';

-- 8. 查询所有非INNODB的表 , 并且提出修改建议
SELECT 
table_schema,
table_name ,
ENGINE ,
CONCAT("alter table ",table_schema,".",table_name," engine=innodb;") AS "修改建议"
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
AND ENGINE <> 'innodb';

-- 9. 所有业务库和表的名字,并且生成备份语句
SELECT 
table_schema , 
table_name ,
CONCAT("mysqldump ",table_schema," ",table_name," > /bak/",table_schema,"_",table_name,".sql") AS "备份"
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql');


===========
MySQL 基础入门

MySQL 核心技术 
索引  
存储引擎 
日志
备份
主从

MySQL 架构和优化 
高可用 
分布式 
优化
NoSQL 
mongodb 
redis

posted @ 2019-12-22 17:16 老王教你学Linux 阅读(174) 评论(0) 编辑收藏举报

刷新页面返回顶部

老王教你学Linux

吃水不忘挖井人，走路不忘领路者！！！ 文档配视频效果更佳哦：https://edu.51cto.com/lecturer/14390454.html