MySQL入门基础知识@ljcccc.com

• 入门知识

  • 优点

    • 性能卓越，服务稳定 开源无版权制约，自主性强，使用成本低 社区用户活跃 软件体积小，安装简单，易于维护 口碑效应好 支持多操作系统，提供多种API、对Java、Python、PHP有很好的支持

  • 发行版本

    • Alpha 内部发行版

    • Beta 测试版

    • RC 候选版

    • GA 稳定版

      • 5.6版本

        • 小版本是偶数版

      • 5.7版本

        • 小版本是偶数版，5.7.17以上（支持高可用MGR）

  • MySQL产品线

    • 产品线1

      • 3.26版本~5.2版本

        • 正宗后代

        • centos5/6中默认都是5.1版本

        • centos7中默认是mariadb

      • 5.5~5.7~8.0版本

        • 借鉴社区贡献，进一步开发的版本

        • 主流版本：5.5、5.6、5.7

      • MySQL cluster 6.0版本&更高

        • 类似Oracle RAC（双主），硬件要求高

        • 一般各大网站没人用

    • 产品线2

      • Mariadb

      • PerconaDB 第三方 Xtrabackup PerconaDB

  • 软件的命名

    • mysql-5.6.40.tar.gz 5---主版本号 6---发行级别 40---发行系列的版本号

• 安装方式

  • yum/rpm

    • rpm

      • rpm -ivh MySQL-client-5.6.40-1.el6.x86_64.rpm rpm -ivh MySQL-server-5.6.40-1.el6.x86_64.rpm rpm -ivh MySQL-devel-5.6.40-1.el6.x86_64.rpm rpm -ivh MySQL-shared-5.6.40-1.el6.x86_64.rpm

    • yum

      • yum install mysql-server -y

  • 二进制安装步骤

    • 包：mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz

    • 安装依赖：yum install -y ncurses-devel libaio-devel autoconf cmake gcc gcc-c++ glibc

    • 解压： tar xf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz

    • 移动到指定目录：mv mysql-5.6.40-linux-glibc2.12-x86_64 /usr/local/mysql-5.6.40

    • 创建软连接： ln -s /usr/local/mysql-5.6.40 /usr/local/mysql

    • 创建MySQL用户：useradd mysql -s /sbin/nologin -M

    • 添加环境变量：[root@mysql50 ~]# vim /etc/profile.d/mysql.sh export PATH="/usr/local/mysql/bin:$PATH" source /etc/profile

    • 进入初始化目录：cd /usr/local/mysql/scripts

    • 执行初始化： ./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

    • 修改默认配置文件

      • vim /etc/my.cnf [mysqld] user=mysql basedir=/usr/local/mysql datadir=/usr/local/mysql/data server_id=6 port=3306 socket=/tmp/mysql.sock [mysql] socket=/tmp/mysql.sock prompt=3306 [\\d]>

    • 拷贝启动脚本

      • （centos6版本）： cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

      • （centos7版本）:加入到system管理 [root@mysql50 ~]# vim /usr/lib/systemd/system/mysqld.service [root@mysql50 ~]# systemctl daemon-reload ###重载

        • [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=https://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=/etc/my.cnf LimitNOFILE = 5000

    • 创建初始密码：mysqladmin -uroot -p password 123

  • 源码编译安装

    • 包：mysql-5.6.40.tar.gz、boost_1_59_0.tar.bz2

    • 安装依赖：yum install -y ncurses-devel libaio-devel autoconf cmake gcc gcc-c++ glibc

    • 安装boost：[root@ljc-97 tools]tar xf boost_1_59_0.tar.bz2 [root@ljc-97 tools]mv boost_1_59_0 /usr/local/boost

    • 解压： tar

    • 生成： cmake或gmake ./configure --prefix=

      • cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DMYSQL_DATADIR=/usr/local/mysql/data/ \ -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_MYISAM_STORAGE_ENGINE=1 \ -DENABLED_LOCAL_INFILE=1 \ -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci \ -DMYSQL_USER=mysql \ -DWITH_DEBUG=0 \ -DWITH_EMBEDDED_SERVER=1 \ -DDOWNLOAD_BOOST=1 -DENABLE_DOWNLOADS=1 -DWITH_BOOST=/usr/local/boost

    • 编译并安装：make && make install

    • 创建用户：useradd mysql -s /sbin/nologin -M

    • 授权：chown mysql.mysql /usr/local/mysql/ -R

    • ####暂不用做软连接：ln -s /usr/local/mysql-5.6.40 /usr/local/mysql

    • 添加环境变量：[root@mysql50 ~]# vim /etc/profile.d/mysql.sh export PATH="/usr/local/mysql/bin:$PATH" source /etc/profile

    • 修改配置文件(etc/my.cnf)

      • [root@ljc-97 ~]# egrep -v "^#|^$" /etc/my.cnf [mysqld] basedir=/usr/local/mysql ----mysql安装位置 datadir=/usr/local/mysql/data ----mysql数据存放位置 socket=/usr/local/mysql/mysql.sock symbolic-links=0 [mysqld_safe] log-error=/var/log/mysql.log ----mysql错误日志 pid-file=/var/run/mysql.pid !includedir /etc/my.cnf.d

    • 拷贝启动脚本

      • （centos6版本）： cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

      • （centos7版本）:加入到system管理 [root@mysql50 ~]# vim /usr/lib/systemd/system/mysqld.service [root@mysql51 ~]# systemctl daemon-reload ###重载

        • [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=https://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=/etc/my.cnf LimitNOFILE = 5000

    • 初始化脚本：cd /usr/local/mysql/scripts/

    • 初始化：./mysql_install_db --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --user=mysql

    • 启动加入开机自启：systemctl start mysqld systemctl status mysqld

  • 安装后优化

    • 创建密码

      • mysqladmin -uroot password '123'

    • 删除无用用户

• 两种连接方式

  • TCP/IP连接

    • mysql -uroot -p123 -h 127.0.0.1

    • mysql -uroot -p123 -h127.0.0.1 -S /tmp/mysql.sock

  • socket连接

    • mysql -uroot -p -hlocalhost

    • mysql -uroot -p123

  • 注意

    • 进入数据库执行 status 验证

    • 默认使用socket连接，因为使用TCP/IP连接需要建立三次握手

    • 不一定-h都是tcp,-hlocalhost是socket连接

• 程序结构

  • 连接层

    • 1、提供连接协议：TCP/IP 、SOCKET

    • 2、提供验证：用户、密码，IP，SOCKET

    • 3、提供专用连接线程：接收用户SQL，返回结果

    • 通过以下语句可以查看到连接线程基本情况 mysql> show processlist;

  • SQL层

    • 1、接收连接层的SQL语句

    • 2、验证语法的正确性

    • 3、权限检查：用户对库表有没有权限

    • 4、验证语义

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

    • 5、解析器：解析SQL语句，对语句执行前,进行预处理，生成解析树(执行计划),说白了就是生成多种执行方案.

    • 6、优化器：根据解析器得出的多种执行计划，进行判断，选择最优的执行计划 代价模型：资源（CPU IO MEM）的耗损评估性能好坏

    • 7、执行器：根据最优执行计划，执行SQL语句，产生执行结果 执行结果：在磁盘的xxxx位置上

      • 建立一个与存储引擎层交互的线程

      • 将执行语句交给存储引擎层，取数据 接收存储引擎层，结构化成表的数据结果

    • 8、提供查询缓存（默认是没开启的），会使用redis tair替代查询缓存功能

    • 9、提供日志记录（日志管理章节）：binlog，默认是没开启的。

  • 存储引擎层

    • 负责根据SQL层执行的结果，从磁盘上拿数据。 将16进制的磁盘数据，交由SQL结构化化成表， 连接层的专用线程返回给用户。

• 物理结构

  • 最底层的数据文件

• 逻辑结构

  • 库

  • 表

    • 元数据

      • 其他属性（行数+占用空间大小+权限）

    • 真实数据行

• 常用数据类型

  • 数值类型

    • 整数

      • TINYINT：常用于年龄

      • INT：十位以下数字

    • 浮点数

      • FLOAT：小型单精度（四个字节）浮点数

      • DOUBLE：常规双精度（八个字节）浮点数

    • 定点数

      • DECIMAL：精确值数值

  • 字符串类型

    • 文本CHAR：固定长度字符串，最多255个字符

    • 文本VARCHAR：可变长度字符串，最多为65535个字符

    • 整数ENUM：由一组固定的合法值组成的枚举

  • 时间类型

    • DATE: YYYY-MM-DD

    • TIEM: hh:mm:ss

    • datetime：年-月-日 时-分-秒

  • 二进制类型

• 表属性

  • 列属性

    • primary key：主键约束 设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。

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

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

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

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

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

    • auto_increment：自增长 针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）

    • comment：注释

  • 表属性

    • 存储引擎: InnoDB（默认的）

    • 字符集和排序规则: utf8---中文占3个字符 utf8mb4---中文占4个字符

• 基础管理

  • 用户管理

    • 增

      • mysql> create user @'10.0.0.%' identified by '123';

    • 删

      • mysql> drop user ljc@'10.0.0.%';

    • 改

      • mysql> alter user ljc@'10.0.0.%' identified by '456';

      • 修改密码

        • mysqladmin -uroot -p123 password '12'

        • mysql> set password=password('456')

        • mysql> update user set password=PASSWORD('123') where user='root' and host='localhost';

        • mysql> grant all privileges on *.* to oldboy@’10.0.0.%’ identified by ‘123’;

      • 忘记密码

        • 停止MySQL

        • 跳过授权表启动

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

        • 连接数据库

        • update修改密码

          • mysql> update mysql.user set password=password('567') where user='root' and host='localhost';

        • 刷新授权表

          • mysql> flush privileges;

        • 重启数据库

        • 使用新密码登录

    • 查

      • mysql> desc mysql.user; ----> authentication_string mysql> select user ,host ,authentication_string from mysql.user

  • 权限管理

    • 常用权限： ALL: SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ALL : 以上所有权限，一般是普通管理员拥有的 with grant option：超级管理员才具备的，给别的用户授权的功能

    • 查看权限：mysql> show grants for app@'10.0.0.%';

    • 收回权限：mysql> revoke delete on app.* from app@'10.0.0.%'；

    • 授权原则：最小权限

      • 单库

        • grant all on mysql.* to ljc@'10.0.0.%' identified by '123';

      • 单表

        • grant all on mysql.user to ljc@'10.0.0.%' identified by '123';

      • 单列

        • grant select(user,host) on mysql.user to ljc@'%' identified by '123';

  • 连接管理

    • MySQL自带的连接工具：mysql

      • -u：指定用户 mysql -uroot -p：指定密码 mysql -uroot -p123 -h：指定主机 mysql -uroot -p123 -h127.0.0.1 -P：指定端口 mysql -uroot -p123 -h127.0.0.1 -p3307 -S：指定sock mysql -uroot -p123 -h127.0.0.1 -S /tmp/mysql.sock -e：指定SQL(库外执行SQL语句) mysql -uroot -p123 -e "show databases;" --protocol=name：指定连接方式

    • 第三方：Navicat SQLyog

• MySQL的启动优先级

  • 配置文件的读取生效顺序

    • 生效顺序：最后读取的配置生效 配置文件读取顺序： 1、/etc/my.cnf 2、/etc/mysql/my.cnf 3、$basedir/my.cnf 4、defaults-extra-file （类似include） 5、~/my.cnf ----------同时配置仅有它的配置生效

    • 若加入了system管理并指定了默认的配置文件： --defaults-file：默认配置文件 如果使用./bin/mysqld_safe 守护进程启动mysql数据库时，使用了 --defaults-file=<配置文件的绝对路径>参数，这时只会使用这个参数指定的配置文件。

  • 1、命令行 2、defaults-file 3、配置文件(~/my.cnf) 5、配置文件(defaults-extra-file （类似include）) 6、配置文件($basedir/my.cnf) 7、配置文件(/etc/mysql/my.cnf) 8、配置文件(/etc/my.cnf) 9、预编译

• MySQL客户端常用命令

  • 库内接口命令

    • \c： 中断不想执行的SQL语句

    • 退出：exit 或者 \c

    • \G： 格式化显示查询的结果（key:value)

    • \T：tee 记录日志

    • \u： 或 use 使用数据库

    • \q：或 exit 或 quit 退出

    • \. ：或 source 导入SQL数据

    • \s：或 status 查看状态信息

  • mysqldump命令

    • 修改密码/设置密码

      • [root@mysql50 ~]# mysqladmin -uroot -p password

    • 关闭MySQL服务

      • [root@mysql50 ~]# mysqladmin -uroot -p shutdown

    • 查看配置文件所有的默认参数

      • [root@mysql50 ~]# mysqladmin -uroot -p variables |grep database

    • 查看MySQL存活状态

      • [root@mysql50 ~]# mysqladmin -uroot -p ping

    • 查看MySQL状态信息

      • [root@mysql50 ~]# mysqladmin -uroot -p status

    • 删除数据库

      • [root@mysql50 ~]# mysqladmin -uroot -p drop DATABASE

    • 创建数据库

      • [root@mysql50 ~]# mysqladmin -uroot -p create DATABASE

    • 重载授权表/刷新缓存主机

      • [root@mysql50 ~]# mysqladmin -uroot -p reload

    • 刷新日志

      • [root@mysql50 ~]# mysqladmin -uroot -p flush-log

    • 查看当前所在位置

      • mysql> select database();

• ​常用SQL分类

  • DDL应用(数据定义语言)

    • 库

      • 创建库

        • 建库规范： 1.库名不能有大写字母 2.建库要加字符集 3.库名不能有数字开头 4.库名要和业务相关

        • 建库标准语句： mysql> create database db charset utf8mb4; mysql> show create database xuexiao;

      • 删除

        • mysql>drop database test;

      • 修改

        • mysql>ALTER DATABASE school CHARSET utf8;

      • 查询（DQL）

        • show databases； show create database test；

    • 表

      • 创建

        • 建表规范： 1. 表名小写 2. 不能是数字开头 3. 注意字符集和存储引擎 4. 表名和业务有关 5. 选择合适的数据类型 6. 每个列都要有注释 7. 每个列设置为非空，无法保证非空，用0来填充。

        • 语法格式： create table stu( 列1 属性（数据类型、约束、其他属性） ， 列2 属性， 列3 属性 )

        • 

      • 查询（DQL）

        • use school show tables； desc stu; show create table stu； CREATE TABLE ceshi LIKE stu;

      • 改

        • #1.在上图表中添加QQ列 ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL UNIQUE COMMENT 'qq号'; #2.在sname后加微信列 ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER sname ; #3.在id列前加一个新列num ALTER TABLE stu ADD num INT NOT NULL COMMENT '数字' FIRST; #4.修改sname数据类型的属性 ALTER TABLE stu MODIFY sname VARCHAR(128) NOT NULL ; #5.将sgender 改为 sg 数据类型改为 CHAR 类型 ALTER TABLE stu CHANGE sgender sg CHAR(1) NOT NULL DEFAULT 'n' ;

      • 删除

        • #4.把刚才添加的列都删掉 ALTER TABLE stu DROP num; ALTER TABLE stu DROP qq; ALTER TABLE stu DROP wechat;

  • DML应用(数据操作语言)

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

      • 增

        • insert 插入一行数据

          • --- 最标准的insert语句 INSERT INTO stu(id,sname,sage,sg,sfz,intime) VALUES (1,'zs',18,'m','123456',NOW()); SELECT * FROM stu; --- 省事的写法 INSERT INTO stu VALUES (2,'ls',18,'m','1234567',NOW()); --- 针对性的录入数据 INSERT INTO stu(sname,sfz) VALUES ('w5','34445788'); --- 同时录入多行数据 INSERT INTO stu(sname,sfz) VALUES ('w55','3444578d8'), ('m6','1212313'), ('aa','123213123123'); SELECT * FROM stu;

      • 删

        • delete 删除数据

          • 全表删除

            • DELETE FROM stu WHERE id=3;

            • DELETE FROM stu truncate table stu; 区别: delete: DML操作, 是逻辑性质删除,逐行进行删除,速度慢. truncate: DDL操作,对与表段中的数据页进行清空,速度快.

          • 伪删除

            • 用update来替代delete，最终保证业务中查不到（select）即可

            • 1.添加状态列 ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 ; SELECT * FROM stu; 2. UPDATE 替代 DELETE UPDATE stu SET state=0 WHERE id=6; 3. 业务语句查询 SELECT * FROM stu WHERE state=1;

      • 改

        • update 修改数据

          • DESC stu; SELECT * FROM stu; UPDATE stu SET sname='zhao4' WHERE id=2; 注意：update语句必须要加where。

  • DQL应用(数据查询语言)

    • select

      • 单独使用

        • -- select @@xxx 查看系统参数

          • SELECT @@port;

          • SELECT @@basedir;

          • SELECT @@datadir;

          • SELECT @@socket;

          • SELECT @@server_id;

          • SELECT @@log_error;

          • SELECT @@log_bin;

        • -- 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;

      • 单表子句-from

        • 语法： SELECT 列1,列2 FROM 表 SELECT * FROM 表

          • 例子： -- 查询stu表中,学生姓名和入学时间 SELECT sname , intime FROM stu; -- 查询stu中所有的数据(不要对大表进行操作) SELECT * FROM stu ;

      • 单表子句-where

        • where配合等值查询

          • SELECT * from city WHERE District='ZHEJIANG'

        • where配合比较操作符(<、> 、>=、 <= 、!=)

          • SELECT * from city WHERE Population < 100

        • where配合逻辑运算符（and or）

          • SELECT * from city WHERE CountryCode='chn' and Population >5000000

          • SELECT * from city WHERE CountryCode='chn' or Population >5000000

        • where配合模糊查询

          • SELECT * FROM city WHERE district LIKE 'guang%';

        • where配合in语句

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

        • where配合between and

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

      • 单表子句-group by

        • 作用：根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列

        • 常用聚合函数

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

        • 例子

          • 例子1：统计世界上每个国家的总人口数. SELECT countrycode ,SUM(population) FROM city GROUP BY countrycode; 例子2：统计世界上每个国家的城市数量 SELECT countrycode,COUNT(id) FROM city GROUP BY countrycode;

      • 单表子句-having

      • 单表子句-order by

      • 单表子句-limit

      • 单表子句-unique

      • 多表连接

  • DCL应用(数据控制语言)

    • grant: grant all on *.* to root@'%' identified by '123'; max_queries_per_hour：一个用户每小时可发出的查询数量 max_updates_per_hour：一个用户每小时可发出的更新数量 max_connections_per_hour：一个用户每小时可连接到服务器的次数 max_user_connections：允许同时连接数量 revoke: revoke select on *.* from root@'%';