MYSQL数据库基础

MYSQL数据库基础

一.数据库原理

1.数据的时代

涉及的数据量大

数据不随程序的结束而消失

数据被多个应用程序分享

大数据

数据分类：

1.结构化数据：即有固定格式和有限长度的数据。例如填的表格就是结构化的数据，

例如填的表格就是结构化的数据

2.非结构化的数据：非结构化的数据越来越多，就是不定长、无固定格式的数据。

有时候非常长，有时候几句话就没了；例如语音，视频都是非结构化的数据

3.半结构化数据：比如：XML或者HTML的格式的数据

2.数据库的发展史

1）文件管理系统的缺点

编写应用程序不方便

数据冗余不可避免

应用程序依赖性

不支持对文件的并发访问

数据间联系弱

难以按用户视图表示数据

无安全控制功能

2）数据库系统发展阶段

萌芽阶段：文件系统

使用磁盘文件来存储数据

初级阶段：第一代数据库

出现了网状模型、层次模型的数据库

中级阶段：第二代数据库

关系型数据库和结构化查询语言

高级阶段：新一代数据库

“关系-对象”型数据库

3）DBMS数据库管理系统

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

4） 数据库管理系统的优点

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

5）数据库管理系统的基本功能

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

6）数据库系统的架构

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

7）各种数据库管理系统

1.层次数据库

                             软件203
                               ↓
                              课程
                             ↙↓↘
                          ↙   ↓   ↘
                      mysql  java   html 

2.网状数据库

相较于层次数据库就更为复杂，

3.RDBMS关系型数据库

Relational Database Management System,关系模型最初由IBM公司的英国计算机科学家埃德加·科德

(Edgar F. Codd)于1969年描述,1974年，IBM开始开发系统R，这是一个开发RDBMS原型的研究项目。

然而，第一个商业上可用的RDBMS是甲骨文，于1979年由关系软件（现为甲骨文公司）发布

关系统型数据库相关概念

• 关系Relational ：关系就是二维表，其中：表中的行、列次序并不重要
• 行row：表中的每一行，又称为一条记录record
• 列column：表中的每一列，称为属性，字段，域field
• 主键Primary key：PK , 用于惟一确定一个记录的字段，一张表只有一个主键
• 域domain：属性的取值范围，如，性别只能是’男’和’女’两个值，人类的年龄只能0-150

常用关系数据库

MySQL: MySQL, MariaDB, Percona Server

PostgreSQL: 简称为pgsql，EnterpriseDB

Oracle

MSSQL

DB2

8）关系型数据库理论

实体Entity：客观存在并可以相互区分的客观事物或抽象事件称为实体,在E-R图中用矩形框表示实体，把实体名写在框内

属性：实体所具有的特征或性质

联系：联系是数据之间的关联集合，是客观存在的应用语义链

• 实体内部的联系：指组成实体的各属性之间的联系。如职工实体中，职工号和部门经理号之间

有一种关联关系

• 实体之间的联系：指不同实体之间联系。例：学生选课实体和学生基本信息实体之间

• 实体之间的联系用菱形框表示

联系类型

• 一对一联系(1:1)
• 一对多联系(1:n)：外键
• 多对多联系(m:n)：增加第三张表

数据的操作

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

数据库规划流程

收集数据，得到字段

收集必要且完整的数据项

转换成数据表的字段

二.MYSQL历史

1979年：TcX公司 Monty Widenius，Unireg
1996年：发布MySQL1.0，Solaris版本，Linux版本
1999年：MySQL AB公司，瑞典
2003年：MySQL 5.0版本，提供视图、存储过程等功能
2008年：Sun公司 以10亿美元收购MySQL
2009年：Oracle公司以 74 亿美元收购 sun 公司
2009年：Monty成立MariaDB

MySQL 的三大主要分支

• mysql
• mariadb
• percona Server

https://www.mysql.com/

http://mariadb.org/

https://www.percona.com

版本演变

MySQL：5.1 --> 5.5 --> 5.6 --> 5.7 ----->6.0 - —>7.0 --> 8.0

MariaDB：5.1 -->5.5 -->10.0–> 10.1 --> 10.2 --> 10.3 --> 10.4 --> 10.5

MySQL被Sun收购后，搞了个过渡的6.0版本，没多久就下线了,后来被Oracle收购后，终于迎来了像样的5.6版本，之后就是5.7、8.0版本。由于6.0版本号已被用过，7.x系列版本专用于NDB Cluster，因而新版本号从8.0开始

关系型数据库和非关系型数据库

1）描述主流的数据库系统
关系型数据库：
Mysql（Oracle公司） 、 SQL server（微软) 、access(微软公司office产品）
Oracle 、DB2（IBM公司），sybase（sybase） 等等



2）关系型数据库和非关系型数据库的区别
关系数据库：
关系数据库结构是二维数据库表，二维表当中每个字段（列）用来描述对象的一个属性，
每个记录（行）用来描述一个对象的信息（完整信息），关系数据库写到哪里也就是存储在硬盘当中 
读写系统就会受到的IO限制或者瓶颈 

其他
关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织
优点：
1、易于维护：都是使用表结构，格式一致；
2、使用方便：SQL语言通用，可用于复杂查询；
3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。
缺点：
1、读写性能比较差，尤其是海量数据的高效率读写；
2、固定的表结构，灵活度稍欠；
3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。



缓存加速软件
非关系型数据库（NoSQL）：
MongoDB 、 Redis（内存数据库/缓存数据库）K-V键值对、与之类似的Memcache,K-V键值对
redis-memecache对比：
相同点：存储高热数据(在内存中高速运行）
不同点：redis可以做持久化保存，可以存储对象


非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。

优点：

1、格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、
图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
2、速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；
3、高扩展性；
4、成本低：nosql数据库部署简单，基本都是开源软件。

缺点：

1、不提供sql支持，学习和使用成本较高；
2、无事务处理；
3、数据结构相对复杂，复杂查询方面稍欠。

三.mysql安装及优化

1.yum安装

[root@localhost ~]#yum install mariadb-server -y
[root@localhost ~]#systemctl start mariadb.service
[root@localhost ~]#mysql
[root@localhost ~]#mysql_secure_installation 
#初始化设置  先输入密码 一路回车     




[root@centos7 ~]#tee /etc/yum.repos.d/mysql.repo <<EOF
[mysql]
name=mysql5.7
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
gpgcheck=0
EOF

清华大学  没有初始密码   5.7.41


[root@centos7 ~]#tee /etc/yum.repos.d/mysql.repo <<EOF
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/
enabled=1
gpgcheck=0
EOF

官方源   5.7.3.

[root@centos7 ~]#cat >/etc/yum.repos.d/mysql.repo <<EOF
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/
enabled=1
gpgcheck=0
EOF


http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/

[root@centos7 ~]#yum -y install mysql-community-server
[root@localhost yum.repos.d]# systemctl start mysqld
[root@localhost yum.repos.d]# ss -ntap |grep 3306
[root@localhost yum.repos.d]# mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@centos7 ~]#grep password /var/log/mysqld.log
[root@localhost yum.repos.d]# mysql -u root -p'+8saq%5+ljzG'
#特殊符号用单引号引起来



myslq    
客户端软件
mysaladmin 




mysql> status
#必须改密码
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

set global validate_password_policy=0;
set global validate_password_length=1;
#修改密码策略

mysql> alter user root@'localhost' identified by 'Admin@123';


mysql> \h
#查看帮助



应用单多线程：
pstree -p
cat /proc/线程id/status
proc 存在内存中

mysql> system hostname
localhost.localdomain
#可以直接使用系统命令
#source 可以调用脚本


mysql> \h, help
mysql> \u，use
mysql> \s，status
mysql> \!，system

mysql [OPTIONS] [database]
命令格式

-A, --no-auto-rehash 禁止补全
-u, --user= 用户名,默认为root
-h, --host= 服务器主机,默认为localhost
-p, --passowrd= 用户密码,建议使用-p,默认为空密码
-P, --port= 服务器端口
-S, --socket= 指定连接socket文件路径
-D, --database= 指定默认数据库
-C, --compress 启用压缩
-e   "SQL" 执行SQL命令
-V, --version 显示版本
-v  --verbose 显示详细信息
--print-defaults 获取程序默认使用的配置

#默认空密码登录
mysql  -uroot  -p

mysql>use mysql #切换数据库
mysql> select database();                   #查看当前数据库
mysql>select user(); #查看当前用户
mysql>SELECT User,Host,Password FROM user;
mysql>system clear #清屏
mysql> ^DBye       #ctrl+d 退出

mysqladmin命令

mysqladmin [OPTIONS] command command...

#查看mysql服务是否正常，如果正常提示mysqld is alive
mysqladmin -u用户 -p密码   ping
mysqladmin -uroot -p123123  ping
#关闭mysql服务，但mysqladmin命令无法开启
mysqladmin -uroot -pcentos shutdown
#创建数据库testdb
mysqladmin -uroot -pcentos   create testdb 
#删除数据库testdb
mysqladmin -uroot -pcentos   drop testdb
#修改root密码
mysqladmin -uroot -pAdmin@123 password ‘zhj'
#日志滚动,生成新文件/var/lib/mysql/mariadb-bin.00000N
mysqladmin -uroot -pcentos flush-logs

2.编译安装mysql

1）安装依赖包

yum -y install gcc gcc-c++ cmake bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel   ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel

2）源码编译安装

利用cmake编译,而利用传统方法，cmake的重要特性之一是其独立于源码(out-of-source)的编译功能，即编译工作可以在另一个指定的目录中而非源码目录中进行，这可以保证源码目录不受任何一次编译的影响，因此在同一个源码树上可以进行多次不同的编译，如针对于不同平台编译编译选项: https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html

3）下载并解压缩源码包

tar xf  mysql-boost-5.7.20.tar.gz

[root@localhost opt]#cd mysql-5.7.20/
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8  \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1

#############模块解释#####################
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
#指定mysql的安装路径
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
#指定mysql进程监听套接字文件（数据库连接文件）的存储路径
-DSYSCONFDIR=/etc \
#指定配置文件的存储路径
-DSYSTEMD_PID_DIR=/usr/local/mysql \
#指定进程文件的存储路径
-DDEFAULT_CHARSET=utf8  \
#指定默认使用的字符集编码，如utf8
-DDEFAULT_COLLATION=utf8_general_ci \
#指定默认使用的字符集校对规则
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
#安装INNOBASE存储引擎
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
#安装ARCHIVE存储引擎
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
#安装BLACKHOLE存储引擎
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
#安装FEDERATED存储引擎
-DMYSQL_DATADIR=/usr/local/mysql/data \
#指定数据库文件的存储路径
-DWITH_BOOST=boost \
#指定boost的路径，
-DWITH_SYSTEMD=1
#生成便于systemctl管理的文件
#############模块解释#####################


make 
make install

4、数据库目录进行权限调整
[root@localhost mysql-5.7.20]#chown -R mysql:mysql /usr/local/mysql/

5、调整配置文件
[root@localhost mysql-5.7.20]#vi /etc/my.cnf
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES


NO_ENGINE_SUBSTITUTION
如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常
STRICT_TRANS_TABLES
在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制
NO_AUTO_CREATE_USER
禁止:GRANT创建密码为空的用户
NO_AUTO_VALUE_ON_ZERO
mysql中的自增长列可以从o开始。默认情况下自增长列是从1开始的，如果你插入值为0的数据会报错
NO_ZERO_IN_DATE
mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告
ERROR_FOR_DIVISION_BY_ZERO
在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。默认情况下数据被零除时MysQL返回NULL
IPES_AS_CONCAT
将"||"视为字符串的连接操作符而非或运算符，这和oracle数据库是一样的，
ANSI QUOTES
启用ANSI QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

[root@localhost mysql-5.7.20]#chown mysql:mysql /etc/my.cnf
#改变属主 属组


6、设置环境变量
echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
echo 'export PATH' >> /etc/profile
source /etc/profile


7、初始化数据库
cd /usr/local/mysql/

bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/

8、数据库开启自启、 关闭、状态 
systemctl enable mysqld
systemctl start mysqld
systemctl stop mysqld
systemctl status mysqld
netstat -anpt | grep 3306


9、设置Mysql密码

mysqladmin -u root -p password
#开始初始密码为空  回车即可   然后输入新密码


update mysql.user set authentication_string=password('123123') where user='root' and host='127.0.0.1' or host='localhost';
update mysql.user set authentication_string=password('newpassword') where user='root' and host='127.0.0.1' or host='localhost';



10、登录数据库
mysql -u root -p 



#################提示：#############
如果出错，执行rm -f CMakeCache.txt
###################################

cmake . \
-DCMAKE_INSTALL_PREFIX=/apps/mysql \
-DMYSQL_DATADIR=/data/mysql/ \
-DSYSCONFDIR=/etc/ \
-DMYSQL_USER=mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_BOOST=/usr/local/src/mysql/boost

3.二进制安装

准备用户
groupadd -r -g 306 mysql
useradd -r -g 306 -u 306 -d /data/mysql mysql


准备数据目录，建议使用逻辑卷
#可选做，后面的脚本mysql_install_db可自动生成此目录
mkdir /data/mysql
chown mysql:mysql /data/mysq


准备二进制文件
tar xf mysql-VERSION-linux-x86_64.tar.gz -C /usr/local
cd /usr/local
ln -sv mysql-VERSION mysql
chown -R root:root /usr/local/mysql/


准备配置文件
cd /usr/local/mysql
cp -b support-files/my-default.cnf   /etc/my.cnf
vim /etc/my.cnf
#mysql语句块中添加以下三个选项
[mysqld]
datadir = /data/mysql
innodb_file_per_table = on  #在mariadb5.5以上版的是默认值，可不加
skip_name_resolve = on      #禁止主机名解析，建议使用 可选项


创建数据库文件
cd /usr/local/mysql/
./scripts/mysql_install_db --datadir=/data/mysql --user=mysql

ll /data/mysql/


准备服务脚本，并启动服务
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start

#如果有对应的service 文件可以执行下面
cp /usr/local/mysql/support-files/systemd/mariadb.service 
/usr/lib/systemd/system/
systemctl daemon-reload
systemctl enable --now mariadb


PATH路径
echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh


安全初始化
/usr/local/mysql/bin/mysql_secure_installation

4.优化操作

1）生成提示符

#查看mysql版本
[root@centos7 ~]#mysql -V

#修改提示符

[root@localhost ~]#vim /etc/my.cnf                       
[mysql]
prompt=(\\u@\\h) [\\d]>\\_


mycli

2）自动补全

yum安装可以 编译安装有问题

[mysql]
prompt=(mysql) [\\d]>\\_
auto-rehash
#自动补全 只能补全敲过的命令

5.客户端程序

• mysql: 交互式或非交互式的CLI工具
• mysqldump：备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成
• insert等写操作语句保存文本文件中
• mysqladmin：基于mysql协议管理mysqld
• mysqlimport：数据导入工具
• MyISAM存储引擎的管理工具：
• myisamchk：检查MyISAM库
• myisampack：打包MyISAM表，只读

用户账号

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

'USERNAME'@'HOST'
root@'10.0.0.100'     只有100能访问后面单引号必加
root@'10.0.0.%'

说明：

HOST限制此用户可通过哪些远程主机连接mysql服务器

支持使用通配符：

% 匹配任意长度的任意字符,相当于shell中*, 示例: 172.16.0.0/255.255.0.0 或 172.16.%.%
_ 匹配任意单个字符,相当于shell中?

四.基础操作

1.SQL分类

• 数据库：database
• 表：table，行：row 列：column
• 索引：index
• 视图：view
• 存储过程：procedure
• 存储函数：function
• 触发器：trigger
• 事件调度器：event scheduler，任务计划
• 用户：user
• 权限：privilege

SQL 语言规范

• 在数据库系统中，SQL 语句不区分大小写，建议用大写

• SQL语句可单行或多行书写，默认以 " ; " 结尾

• 关键词不能跨多行或简写

• 用空格和TAB 缩进来提高语句的可读性

• 子句通常位于独立行，便于编辑，提高可读性

数据库对象和命名

数据库的组件(对象)：

数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

命名规则：

必须以字母开头，后续可以包括字母,数字和三个特殊字符（# _ $）

不要使用MySQL的保留字,tabble select show databases

SQL语句分类

• DDL: Data Defination Language 数据定义语言

CREATE，DROP，ALTER

• DML: Data Manipulation Language 数据操纵语言

INSERT，DELETE，UPDATE

软件开发：CRUD

• DQL：Data Query Language 数据查询语言

SELECT

• DCL：Data Control Language 数据控制语言

GRANT，REVOKE

• TCL：Transaction Control Language 事务控制语言

COMMIT，ROLLBACK，SAVEPOINT

#DDL: Data Defination Language 数据定义语言
CREATE，DROP，ALTER

#DML: Data Manipulation Language 数据操纵语言
INSERT，DELETE，UPDATE

#DQL：Data Query Language 数据查询语言
SELECT 


#DCL：Data Control Language 数据控制语言
GRANT，REVOKE，COMMIT，ROLLBACK


SELECT *                 #SELECT子句
FROM products             #FROM子句
WHERE price>666         #WHERE子句

2.查看帮助信息

mysql> help create
#help 后面跟上具体命令可以查看帮助

3.查看支持的字符集

show charset;
#查看支持字符集
默认拉丁文字

utf8     | UTF-8 Unicode   
#阉割版的

utf8mb4  | UTF-8 Unicode 
#真实的版本


修改字符集
vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4

show create database test;

4.管理数据库

1）创建数据库

mysql> show variables like 'char%';
#查看当前默认字符
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)


mysql> create database db1;         #建立数据库db1
Query OK, 1 row affected (0.00 sec)

mysql> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE "db1" /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> create database db2 charset=utf8;   #指定字符集为utf-8
Query OK, 1 row affected (0.00 sec)

mysql> create database zabbix character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.00 sec)
#字符集 utf8  不区分大小写    collate utf8_bin这个排序规则  可以区分 
#字符集 utf8  不区分大小写    collate utf8_bin这个排序规则  可以区分 

2）修改数据库

当建立好数据库后可以使用 ALTER 命令修改

mysql> ALTER DATABASE db1 character set utf8;
Query OK, 1 row affected (0.00 sec)
#修改字符集

[root@localhost ~]#cat /usr/local/mysql/data/db2/db.opt 
default-character-set=utf8
default-collation=utf8_general_ci
#查看db2地字符集

3）删除数据库

DROP DATABASE|SCHEMA [IF EXISTS] ‘DB_NAME’;

mysql> drop database db1;
Query OK, 0 rows affected (0.02 sec)

#数据库其实 就是一个文件夹，删除数据库等于删除文件夹

4）查看数据库列表

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db2                |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
6 rows in set (0.00 sec)

5.数据类型

mysql支持多种内置数据类型

数值类型

日期/时间类型

字符串(字符)类型

选择正确的数据类型对于获得高性能至关重要，三大原则：

1. 更小的通常更好，尽量使用可正确存储数据的最小数据类型

2. 简单就好，简单数据类型的操作通常需要更少的CPU周期

3. 尽量避免NULL，包含为NULL的列，对MySQL更难优化

1） 整数型

• tinyint(m) 1个字节 范围(-128~127) 有一个正或负的表示符

• smallint(m) 2个字节 范围(-32768~32767)

• mediumint(m) 3个字节 范围(-8388608~8388607)

• int(m) 4个字节 范围(-2147483648~2147483647)

• bigint(m) 8个字节 范围(±9.22*10的18次方)

上述数据类型，如果加修饰符unsigned后，则最大值翻倍

如：tinyint unsigned的取值范围为(0~255)

2） 浮点型(float和double)，近似值

float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位, 注意: 小数点不占用总个数

double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位, 注意: 小数点不占用总个数

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位

3） 定点数

在数据库中存放的是精确值,存为十进制 decimal(5.2) 100.01 - 999.99

格式 decimal(m,d) 表示 最多 m 位数字,其中 d 个小数,小数点不算在长度内

比如: DECIMAL(6,2) 总共能存6位数字，末尾2位是小数，字段最大值 9999.99 （小数点不算在长度内）

参数m<65 是总个数，d<30且 d<m 是小数位

MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。

例如: decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：其中，小数点前的9个数字用4个

字节，小数点后的9个数字用4个字节，小数点本身占1个字节

浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用

8个字节

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时

4） 字符串

char(n) 固定长度，最多255个字符,注意不是字节

varchar(n) 可变长度，最多65535个字符

tinytext 可变长度，最多255个字符

text 可变长度，最多65535个字符

mediumtext 可变长度，最多2的24次方-1个字符

longtext 可变长度，最多2的32次方-1个字符

BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节

VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节

内建类型：ENUM枚举, SET集合

char和varchar的比较:

参考：https://dev.mysql.com/doc/refman/8.0/en/char.html

name vachar（4） char 固定长度字符

1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符串末尾不能有空格，varchar不限于此

2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节

3.char类型的字符串检索速度要比varchar类型的快

5）修饰符

适用所有类型的修饰符：

名称	含义
NULL	数据列可包含NULL值，默认值
NOT NULL	数据列不允许包含NULL值，*为必填选项
DEFAULT	默认值
PRIMARY KEY	主键，所有记录中此字段的值不能重复，且不能为NULL
UNIQUE KEY	唯一键，所有记录中此字段的值不能重复，但可以为NULL
CHARACTER SET	name 指定一个字符集

适用数值型的修饰符：

名称	作用
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号

6.表操作

1）查看数据库结构

#查看数据库
show create database db1;



#选择数据库，切换库类似于cd
show tables;
show tables in mysql;


####查看表结构(字段)####
格式：describe [数据库名.]表名；
desc user;
desc servers;
#查看表结构

desc mysql.servers;
#不想先使用数据库可以使用上面的

2）新建表

###2.创建新的表####
create table 表名 （字段1 数据类型，字段2 数据类型[,...] [,PRIMARY KEY (主键名)]）；
#主键一般选择代表唯一性的字段不允许为空值（null）,且一个表只能有一个主键###

create table info (id int NOT NULL,name char(10) NOT NULL, age int, phone int
PRIMARY KEY (id) );
主键

create database db1;
use db1;
 create table student (id smallint unsigned primary key auto_increment, name varchar(10), age tinyint unsigned,gender enum('M','F') default 'M' );
 
 unsigned：取消负数
primary  key：主键
auto_increment： 自增长
enum('M','F')：多选 
default 'M'：默认值为 M


insert student (name,age) values('ben',19);
#加入数据


insert student values();
#加入空字段
select * from student;
#查找 会发现多一条空记录

desc student;




show create table student;

show tables；

show table status like 'students';
show table status like 'student'\G;
#分行

3）修改表结构

alter 添加字段

 mysql > alter table student add   phone char(11) not null ;
        命令字  命令字  表名   关键字  字段名称字段属性 

change 修改字段 名称

mysql [db1]> alter table student change phone mobile char(11);
mysql [db1]> desc student;

drop 删除字段

mysql [db1]> alter table student drop mobile;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

其他

ALTER TABLE students RENAME s1;
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
ALTER TABLE s1 MODIFY phone int;
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
ALTER TABLE s1 DROP COLUMN mobile;
ALTER TABLE s1 character set utf8;
ALTER TABLE s1 change name name varchar(20) character set utf8;
ALTER TABLE students ADD gender ENUM('m','f');
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY; 
ALTER TABLE students DROP age;
DESC students;
#新建表无主键，添加和删除主键
CREATE TABLE t1 SELECT * FROM students;
ALTER TABLE t1 add primary key (stuid);
ALTER TABLE t1 drop primary key

7.DML语言

DML: INSERT, DELETE, UPDATE

1）INSERT语句

功能：一次插入一行或多行数据

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
   [INTO] tbl_name [(col_name,...)]
   {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
   [ ON DUPLICATE KEY UPDATE #如果重复更新之
     col_name=expr
       [, col_name=expr] ... ]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
   [INTO] tbl_name
   SET col_name={expr | DEFAULT}, ...
   [ ON DUPLICATE KEY UPDATE
     col_name=expr
       [, col_name=expr] ... ]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
   [INTO] tbl_name [(col_name,...)]
    SELECT ...
   [ ON DUPLICATE KEY UPDATE
     col_name=expr
       [, col_name=expr] ... ]

INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

#解释
insert  表名[（字段）]  值（值1，值2....）,(值1，值2)......；
使用 insert 语句时 如果不在表后加上字段就要一一对应填写上信息（注意 字符串用引号引起来）

也可以指定添加的字段

mysql [db1]> insert into student(name,age,phone) values('mike',20,'10086')；
#添加一条记录

mysql [db1]> insert student(name,age,phone) values('mike',20,'10086'),('peter',18,'10000');
#用逗号隔添加多条记录


mysql [db1]> insert student values(null,'小强',20,'M','10010');
#不指明添加字段，  学生编号有 自增长可以使用空

2）UPDATE语句

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
   [WHERE where_condition]
   [ORDER BY ...]
   [LIMIT row_count]
   
   
   
#解释
update  表名   set  字段=修改  指定哪条记录;

注意：一定要有限制条件，否则将修改所有行的指定字段

mysql [db1]> update student set gender='F' where id=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

3） DELETE语句

删除表中数据，但不会自动缩减数据文件的大小。

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
   [WHERE where_condition]
   [ORDER BY ...]
   [LIMIT row_count]
 可先排序再指定删除的行数
 
 #解释：
 delete  from  表名   指定条件

注意：一定要有限制条件，否则将清空表中的所有数据

如果想清空表，保留表结构，也可以使用下面语句，此语句会自动缩减数据文件的大小。

mysql [db1]> delete from student where id=5;
Query OK, 1 row affected (0.01 sec)

8.DQL语句

添加脚本

source /root/hellodb_innodb.sql 

1） 单表操作

语法：

SELECT
 [ALL | DISTINCT | DISTINCTROW ]
 [SQL_CACHE | SQL_NO_CACHE]
 select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [FOR UPDATE | LOCK IN SHARE MODE]

说明：

• 字段显示可以使用别名：

col1 AS alias1, col2 AS alias2, …

select name,age from students where stuid=6；    #只挑出感兴趣的列

• WHERE子句：指明过滤条件以实现"选择"的功能：

• 过滤条件：布尔型表达式

算术操作符：+, -, *, /, %

比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=

select * from students where age=20;    #年龄等于20岁
select * from students where age >20;   #大于20岁
select * from students where age >=20 and age<=30; #20岁到30岁

范例查询: BETWEEN min_num AND max_num

select * from students where age between 20 and 30;   #20岁到30岁

• 不连续的查询: IN (element1, element2, …)

select * from students where age  in (20,22,30);

• 空查询: IS NULL, IS NOT NULL

select * from students where classid is null;

• DISTINCT 去除重复行，范例：SELECT DISTINCT gender FROM students;

select age from students;
select distinct age from students;     #去重

• 模糊查询: LIKE 使用 % 表示任意长度的任意字符 _ 表示任意单个字符

select * from students where name='Xu Zhu';    #精确查找
select * from students where name like 's%';   #找到s 开头的
select * from students where name like '%s%';  #包含


############sql注入攻击#################
create table user (id int primary key auto_increment,name varchar(20) not null,password varchar(30) not null);

insert user values(null,'admin','123456');
insert user values(null,'test','123456');

select * from user where name='admin' and password='123456';   #找到数据就是 用户名密码正确。找不到就是错误

select * from user where name='admin' and password='' or '1'='1';

网页上 输入用户名核密码 
lisi    123123
select * from user where name='lisi' and passwd='123123' or  1=1;





select * from user where name='admin' and password='' or '1=1';
select * from user where name='admin'; -- ' and password='123';      #--代表注释的意思
select * from user where name='admin'; # ' and password='123';

• RLIKE：正则表达式，索引失效，不建议使用

• REGEXP：匹配字符串可用正则表达式书写模式，同上

• 逻辑操作符：NOT，AND，OR，XOR

• GROUP BY：根据指定的条件把查询结果进行"分组"以用于做"聚合"运算

常见聚合函数： count(), sum(), max(), min(), avg(),注意:聚合函数不对null统计

count（）数量 sum（）总和 max（）最大值 avg（）平均值

分类：按照班级来分类
select classid 班级,count(*) 学员数量 from students group by classid;    #班级和学员数量为别名

select age,count(*) from students group by age;     #统计每个年龄的有多少人

select classid,max(age) from students group by classid;  #每个班里最大年龄

select gender,avg(age) from students group by gender;    #按性别分组  求平均值



select classid,name from students group by classid;
#一旦分组后   select 后面一般只能跟  分组的字段核聚合函数

HAVING: 对分组聚合运算后的结果指定过滤条件

一旦分组 group by ，select语句后只跟分组的字段，聚合函数

• ORDER BY: 根据指定的字段对查询结果进行排序

升序：ASC

降序：DESC

select * from students order by age ; 
#按年龄大小  正序排序

select * from students order by age desc;
#按年龄大小 倒序排序

• LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制,跳过offset,显示row_count行,offset默为值为0

  select * from students order by age limit 5;   #排序前5
  
  select * from students order by age limit 5,3;   #跳过前5，  显示前5的后三个   分页
  

• 对查询结果中的数据请求施加"锁"

FOR UPDATE: 写锁，独占或排它锁，只有一个读和写操作

LOCK IN SHARE MODE: 读锁，共享锁，同时多个读操作

2）多表查询

子查询

子查询 subquery 即SQL语句调用另一个SELECT子句,可以是对同一张表,也可以是对不同表,主要有以下

四种常见的用法

1.嵌套

select avg(age) from students;   #平均年龄
select * from students where age > (select avg(age) from students);    #  类似嵌套


update students set age= (select avg(age) from students) where stuid=25;
ERROR 1093 (HY000): You can't specify target table 'students' for update in FROM clause

update teachers set age= (select avg(age) from students) where tid=4;
#将students  表里的 平均年龄，作为  值  赋给teacher表

2.联合

竖向合并，把第二张表追加到后面

select * from teachers
union    #  纵向合并   字段数一样  子段类型要要一致    字段顺序最好要一样
select stuid,name,age,gender from students;

横向合并（交叉连接）

第一张表的 第一个记录 分别和第二张表里所有的记录做合并

第一张表的 第二个记录 分别和第二张表里所有的记录做合并

以此类推…

一般不用，产生的数据数量 10万 交叉 20 万 mysql 千万级瓶颈（需要分库 分表） 200亿

select * from students cross join teachers;
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name          | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   1 | Song Jiang    |  45 | M      |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   2 | Zhang Sanfeng |  94 | M      |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   4 | Lin Chaoying  |  93 | F      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |   1 | Song Jiang    |  45 | M      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |   2 | Zhang Sanfeng |  94 | M      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |   3 | Miejue Shitai |  77 | F      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |   4 | Lin Chaoying  |  93 | F      |

内连接

A 表 B表 取交集

语法：

A表内容    inner join   第二张表  on  条件  stuid=tid

第一张表的内容 inner join 第二张表 on 条件

select * from students inner join teachers on students.teacherid=teachers.tid;   #哪个学生  对应哪个老师
update students  set teacherid=1 where stuid=6;    #更新数据

select students.name,students.gender,teachers.name,teachers.gender from students inner join teachers on teachers.tid=students.stuid;

select s.name,s.gender,s.teacherid,t.name,t.gender from students s inner join teacherschers t on s.teacherid=t.tid;

外连接

左外连接 A 表全要 ， B表只要相同的部分

A表内容 left join 第二张表 on 条件 stuid=tid

select * from students s left join teachers t on s.teacherid=t.tid;


select * from students left join teachers on students.teacherid=teachers.tid where teachers.tid is null;

右外连接

select * from students s right join teachers t on s.teacherid=t.tid;

自连接

create table emp(id int,name varchar(11),leaderid int);
insert emp values('1','zhengshao',null),('2','baishao',1),('3','weishao',2),(4,'he',3);

查询每个上级领导姓名
 id   | name      | leaderid |
+------+-----------+----------+
|    1 | zhengshao |     NULL |
|    2 | baishao   |        1 |
|    3 | weishao   |        2 |
|    4 | he        |        3 |
+------+-----------+----------+
 id   | name      | leaderid |
+------+-----------+----------+
|    1 | zhengshao |     NULL |
|    2 | baishao   |     zhnegshao |
|    3 | weishao   |     baishao |
|    4 | he        |     weishao |
+------+-----------+----------+

 id   | name      | leaderid |
+------+-----------+----------+
|    1 | zhengshao |     NULL |
|    2 | baishao   |        1 |
|    3 | weishao   |        2 |
|    4 | he        |        3 |
+------+-----------+----------+




select e.name 员工,l.name 领导 from emp e inner join emp l on e.leaderid=l.id;
#内连接  没领导会丢失  建议使用左连接
select e.name 员工,l.name 领导 from emp e left join emp l on e.leaderid=l.id;

三表查询

show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| emp               |
| scores            |
| students          |
| teachers          |
| toc               |
| user              |
+-------------------+


学生表   students   1个学生可以报名多门课程 


课程表   courses   1门多个学生



分数表    scores

select * from students inner  join scores on students.stuid=scores.stuid;
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+
| StuID | Name        | Age | Gender | ClassID | TeacherID | ID | StuID | CourseID | Score |
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |  1 |     1 |        2 |    77 |
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |  2 |     1 |        6 |    93 |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |  3 |     2 |        2 |    47 |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |  4 |     2 |        5 |    97 |
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |  5 |     3 |        2 |    88 |
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |  6 |     3 |        6 |    75 |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |  7 |     4 |        5 |    71 |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |  8 |     4 |        2 |    89 |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |  9 |     5 |        1 |    39 |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 | 10 |     5 |        7 |    63 |
|     6 | Shi Qing    |  46 | M      |       5 |         1 | 11 |     6 |        1 |    96 |
|     7 | Xi Ren      |  19 | F      |       3 |      NULL | 12 |     7 |        1 |    86 |
|     7 | Xi Ren      |  19 | F      |       3 |      NULL | 13 |     7 |        7 |    83 |
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL | 14 |     8 |        4 |    57 |
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL | 15 |     8 |        3 |    93 |
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+
15 rows in set (0.00 sec)

select st.name,sc.courseid,sc.score from students st inner  join scores sc on st.stuid=sc.stuid;stuid=sc.stuid;


select st.name,sc.courseid,sc.score from students st inner  join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid;


select st.name,co.course,sc.courseid,sc.score from students st inner  join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid;

五.高级查询语言

1.SELECT ----显示表格中一个或数个字段的所有数据记录

select  字段 from 表名;  

select  age from students; 

2.DISTINCT ----不显示重复的数据记录

SELECT DISTINCT "字段" FROM "表名";

select distinct age from students

3.WHERE ----有条件查询

SELECT "字段" FROM "表名" WHERE "条件";

select * from students where age >20;     #大于20岁

4.AND OR 且 或

SELECT "字段" FROM "表名" WHERE "条件1" {[AND|OR] "条件2"}+ ;

select * from students where (age >20 and age <30) or age >50; # 20到30   或大于50

5. IN ----显示已知的值的数据记录

SELECT "字段" FROM "表名" WHERE "字段" IN ('值1', '值2', ...);

 select * from students where age in (22);     #只找22岁
 select * from students where age in (22，100); #找22岁和100

6.BETWEEN ----显示两个值范围内的数据记录

SELECT "字段" FROM "表名" WHERE "字段" BETWEEN '值1' AND '值2';

select * from students where age between 20 and 50;     #找到 20岁到50岁之间
select * from students where classid between 2 and 4;   #找到  2班4班的人

7.通配符

通常通配符都是跟 LIKE 一起使用的  模糊查询
% ：百分号表示零个、一个或多个字符
_ ：下划线表示单个字符

'A_Z'：所有以 'A' 起头，另一个任何值的字符，且以 'Z' 为结尾的字符串。例如，'ABZ' 和 'A2Z' 都符合这一个模式，而 'AKKZ' 并不符合 (因为在 A 和 Z 之间有两个字符，而不是一个字符)。
'ABC%': 所有以 'ABC' 起头的字符串。例如，'ABCD' 和 'ABCABC' 都符合这个模式。
'%XYZ': 所有以 'XYZ' 结尾的字符串。例如，'WXYZ' 和 'ZZXYZ' 都符合这个模式。
'%AN%': 所有含有 'AN'这个模式的字符串。例如，'LOS ANGELES' 和 'SAN FRANCISCO' 都符合这个模式。
'_AN%'：所有第二个字母为 'A' 和第三个字母为 'N' 的字符串。例如，'SAN FRANCISCO' 符合这个模式，而 'LOS ANGELES' 则不符合这个模式。

8.like----匹配一个模式来找出我们要的数据记录

SELECT "字段" FROM "表名" WHERE "字段" LIKE {模式};


select * from students where name like 's%';     # s开头
select * from students where name like '% ch__';

9. ORDER BY ----按关键字排序

SELECT "字段" FROM "表名" [WHERE "条件"] ORDER BY "字段" [ASC, DESC];
#ASC 是按照升序进行排序的，是默认的排序方式。
#DESC 是按降序方式进行排序。


select * from students order by age;    #年龄升序排列
select * from students order by age desc;  #年龄降序排
select * from students order by age desc limit 3;


select * from students where classid=1 order by age;  #找出1班的人按升序排序

10.函数

数学函数：

函数名	函数值
abs(x)	返回 x 的绝对值
rand()	返回 0 到 1 的随机数
mod(x,y)	返回 x 除以 y 以后的余数
power(x,y)	返回 x 的 y 次方
round(x )	返回离 x 最近的整数
round(x,y)	保留 x 的 y 位小数四舍五入后的值
sqrt(x)	返回 x 的平方根
truncate(x,y)	返回数字 x 截断为 y 位小数的值
ceil(x)	返回大于或等于 x 的最小整数
floor(x)	返回小于或等于 x 的最大整数
greatest(x1,x2…)	返回集合中最大的值，也可以返回多个字段的最大的值
least(x1,x2…)	返回集合中最小的值，也可以返回多个字段的最小的值

例子：

select abs(-100);   #取绝对值
select rand();      #随机数 0到1 间
select mod(10,3);   #10 除3 取余数
select power(2,3)   #求2的3次方
select round(2.6);   #返回离2.6最近的整数3
select sqrt(9);     #返回9 的平方根
select truncate (1.235,2);   #返回前两位值
select ceil (1.5);  #返回大于等于1.5 的值
select floor (1.5);  #返回小于等于1.5 的值
select greatest(1,2,3); #返回集合中的 最大值
select least(1,2,3);    #返回集合中的最小值 

聚合函数：

函数名	函数意
avg()	返回指定列的平均值
count()	返回指定列中非 NULL 值的个数
min()	返回指定列的最小值
max()	返回指定列的最大值
sum(x)	返回指定列的所有值之和

select  函数(*|单个字段)

##############    avg    ######################
select avg(age) from students;   #avg  平均值
select avg(age) from students where classid=1;  #求1班年龄平均值


##############  count  ######################
select count(classid) from students；       #统计非空classid 字段 一共有多少行记录

select count(distinct classid) from students  #一共有几个班级 去重

select count(classid) from students;
select count(*) from students;

聚合函数 count()  括号中是具体的字段  如果有null 值不统计 
count()  括号中是  *   会统计 null
#count(*) 包括了所有的列的行数，在统计结果的时候，不会忽略列值为 NULL
#count(列名) 只包括列名那一列的行数，在统计结果的时候，会忽略列值为 NULL 的行


##############   min  ###################
select min(age) from students;

###############  max  ######################
select max(classid) 班级最大序号 from students;


###############  sum  ###################
select sum(age) from students;  #求年龄总和
select sum(age) from students where classid=1;   #求1班的年龄总和

字符串字符

函数名	函数意义
concat(x,y)	将提供的参数 x 和 y 拼接成一个字符串
substr(x,y)	获取从字符串 x 中的第 y 个位置开始的字符串，
substr(x,y,z)	获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
trim(LEADING ‘’ ，FROM ‘’)	返回去除指定格式的值
length(x)	返回字符串 x 的长度
replace(x,y,z)	将字符串 z 替代字符串 x 中的字符串 y
upper(x)	将字符串 x 的所有字母变成大写字母
lower(x)	将字符串 x 的所有字母变成小写字母
left(x,y)	返回字符串 x 的前 y 个字符
right(x,y)	返回字符串 x 的后 y 个字符
repeat(x,y)	将字符串 x 重复 y 次
space(x)	返回 x 个空格
strcmp(x,y)	比较 x 和 y，返回的值可以为-1,0,1
reverse(x)	将字符串 x 反转

#concat
select concat(name,classid) from students where stuid=1;   #将姓名字段核班级字段合在一起
select concat(name,'  ',classid) from students whereere classid=1;
#加空格

#substr
select name from students where stuid=25;
select substr(name,3) from students where stuid=25;

11.GROUP BY

对GROUP BY后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的

GROUP BY 有一个原则，凡是在 GROUP BY 后面出现的字段，必须在 SELECT 后面出现；
凡是在 SELECT 后面出现的、且未在聚合函数中出现的字段，必须出现在 GROUP BY 后面

SELECT "字段1", 聚合函数("字段2") FROM "表名" GROUP BY "字段1";

select  classid from students group by classid;   #对 classid字段进行分组  一共有8组
select  age from students group by age;           # 对 age 字段  分组
select  gender from students group by gender;     # 对性别进行分组

select classid,count(classid) from students group by classid;   
凡是在 SELECT 后面出现的、且未在聚合函数中出现的字段，必须出现在 GROUP BY 后面

select gender,avg(age) from students group by gender;    #按性别分组  求平均值


5.5可以执行
5.7直接报错
select * from students group by classid;

having

HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足

select classid,count(classid) from students group by classid having classid > 3;

select classid,count(classid) from students group by classid where classid > 3;

12.视图view 临时表

视图：数据库中的虚拟表，这张虚拟表中不包含真实数据，只是做了映射

create  view   视图名  as   查询结果


create view v1 as select * from students where age > 50;
show tables;
select * from v_test;

update students set age=90 where stuid=25;
select * from v_test;

13.正则表达式

 匹配	      描述
1、^	    匹配文本的开始字符
2、$	    匹配文本的结束字符
3、.	    匹配任何单个字符
4、*		匹配零个或多个在它前面的字符
5、+		匹配前面的字符 1 次或多次
6、字符串	匹配包含指定的字符串
7、p1|p2	匹配 p1 或 p2
8、[…]		匹配字符集合中的任意一个字符
9、[^…]	匹配不在括号中的任何字符
10、{n}		匹配前面的字符串 n 次
11、{n,m}	匹配前面的字符串至少 n 次，至多 m 次
    {,m}   最多m次
    {n,}    最少n次
12、？     匹配一个字符 

select name from students where name regexp '^s'
select name from students where name regexp 's';
select name from students where name regexp 's.i';
select name from students where name regexp '^s|l';

14.子查询

子查询 ----连接表格，在WHERE 子句或 HAVING 子句中插入另一个 SQL 语句

SELECT "字段1" FROM "表格1" WHERE "字段2" [比较运算符] 				#外查询
(SELECT "字段1" FROM "表格2" WHERE "条件");						   #内查询

select  tid  from  teachers;
select name,stuid from students where stuid in(select tid from teachers);


select avg(age) from students;   #平均年龄
select * from students where age > (select avg(age) from students);    #


update students set age= (select avg(age) from students) where stuid=25;
ERROR 1093 (HY000): You can't specify target table 'students' for update in FROM clause

update teachers set age= (select avg(age) from students) where tid=4;
#将students  表里的 平均年龄，作为  值  赋给teacher表

六.mysql 的账户管理

1.存放用户信息的表

mysql 的用户 放在mysql数据库中的user表中

select user,host,authentication_string from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

2.查看当前使用用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)

3.新建用户

CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];
----------------------------------------------------------------------------------------------------------
'用户名'：指定将创建的用户名
'来源地址'：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%
'密码'：若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；
        若使用加密密码，需要先使用SELECT PASSWORD('密码'); 获取密文，再在语句中添加 PASSWORD '密文';
        若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用）
----------------------------------------------------------------------------------------------------------

'USERNAME'@'HOST'
'用户名'@'来源地址'

@'HOST': 主机名： user1@'web1.kgc.org'
IP地址或Network 
 通配符： %   _
 示例：ywx@172.16.%.%  
      user2@'192.168.1.%'

mysql  -u用户名  -p密码   -h远程主机  -P端口号



create user test@'192.168.%.%';   #建立远程登录用户
create user ywx@'192.168.91.%' identified by '123123';   #可以后面加密码
select user,host,password from mysql.user;   #查看字段  密码为空

mysql -utest -h192.168.91.100    #使用其他用户登录

alter user test@'192.168.%.%' identified by  '123123';    #新版可以  旧版的mariadb  不可以
ALTER  USER test@'192.168.%.%'IDENTIFIED BY 'centos';

4.修改用户名称

rename user '旧名字' to '新名字;

rename user 'zhangsan'@'192.168.91.%' to 'lisi'@'192.168.91.%';

5.删除用户

drop  user '用户名';

drop user liwu@'%';

6.修改用户密码

密码有安全性策略可以修改取消

set global validate_password_policy=0;
set global validate_password_length=1;
#修改密码策略

SET PASSWORD = PASSWORD('abc123');  #只能改自己当前

set password for '用户' = password('密码');    #


set password = 'abc123';    #给当前用户修改密码

set password for 'lisi'@'192.168.91.%' = 'abc123';   #给其他用户修改密码

7.破解密码

修改配置文件

vim /etc/my.cnf
[mysqld]
skip-grant-tables 
#数据库的单用户模式
skip-networking  #MySQL8.0不需要

#然后清空密码
update mysql.user set authentication_string='' where user='root' and host='localhost';
#注意刷新后生效
flush privileges;

8.远程登录

mysql -utest -h192.168.91.100   -p'密码'  -P端口号

9.用户权限管理

权限类别：

• 管理类
• 程序类
• 数据库级别
• 表级别
• 字段级别

管理类：

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

库和表级别：针对 DATABASE**、**TABLE

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

数据操作

• SELECT
• INSERT
• DELETE
• UPDATE

字段级别

• SELECT(col1,col2,…)
• UPDATE(col1,col2,…)
• INSERT(col1,col2,…)

所有权限

• ALL PRIVILEGES 或 ALL

1）查看权限

SHOW GRANTS FOR 'lisi'@'%';
#USAGE权限只能用于数据库登陆，不能执行任何操作；USAGE权限不能被回收，即 REVOKE 不能删除用户。

2）授予权限

GRANT语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户；当指定的用户名存在时， GRANT 语句用于修改用户信息。

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];

----------------------------------------------------------------------------------------------------------
#权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select,insert,update”。使用“all”表示所有权限，可授权执行任何操作。
#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符“*”。例如，使用“xxx.*”表示授权操作的对象为 xxx数据库中的所有表。
#'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“192.168.80.%”等。
#IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分，则用户的密码将为空。
----------------------------------------------------------------------------------------------------------

GRANT select ON xxx.* TO 'zhangsan'@'localhost' IDENTIFIED BY '123456';
#允许用户 zhangsan 在本地查询 kgc 数据库中 所有表的数据记录，但禁止查询其他数据库中的表的记录。


GRANT ALL [PRIVILEGES] ON *.* TO 'lisi'@'%' IDENTIFIED BY '123456';
#允许用户 lisi 在所有终端远程连接 mysql ，并拥有所有权限。

flush privileges;
#不要忘记刷新
quit

3）撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址;

REVOKE ALL ON *.* FROM 'cxk'@'%';

4）远程连接图形界面

grant all privileges on *.* to zhj@'%' identified by '123456';
flush privileges;

七.索引

索引介绍

索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现

索引的概念

• 索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于C语言的链表通过指针指向数据记录的内存地址）。

• 使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。

• 索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。

• 索引是表中一列或者若干列值排序的方法。

• 建立索引的目的是加快对表中记录的查找或排序。

索引的作用优点 1.加快查询速度，提高数据库性能
●设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因。
●当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。避免排序和使用临时表
●可以降低数据库的IO成本（减少io次数），并且索引还可以降低数据库的排序成本。将随机I/O转为顺序I/O
●通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。
●可以加快表与表之间的连接。
●在使用分组和排序时，可大大减少分组和排序的时间。
●建立索引在搜索和恢复数据库中的数据时能显著提高性能

缺点：

• 占用额外的磁盘空间，影响插入速度 占用磁盘空间

创建索引的原则依据
索引虽可以提升数据库查询的速度，但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源，在有索引的情况下，数据库会先进行索引查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担。
●表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是主表的主键，查询时可以快速定位。
●记录数超过300行的表应该有索引。如果没有索引，每次查询都需要把表遍历一遍，会严重影响数据库的性能。
●经常与其他表进行连接的表，在连接字段上应该建立索引。
●唯一性太差的字段不适合建立索引。
●更新太频繁地字段不适合创建索引。
●经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引。
●在经常进行 GROUP BY、ORDER BY 的字段上建立索引；
●索引应该建在选择性高的字段上。
●索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。

索引优化

• 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧，尽量不要在列上进行运算（函数操作和表达式操作）

• 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性（不重复的索引值和数据表的记录总数的比值）来评估，尽量使用短索引，如果可以，应该制定一个前缀长度

• 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引

• 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧

• 只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引

• 对于经常在where子句使用的列，最好设置索引

• 对于有多个列where或者order by子句，应该建立复合索引

• 对于like语句，以 % 或者 _ 开头的不会使用索引，以 % 结尾会使用索引

• 尽量不要使用not in和<>操作,虽然可能使用索引,但性能不高

• 不要使用RLIKE正则表达式会导致索引失效

• 查询时，能不要就不用，尽量写全字段名，比如:select id,name,age from students;

• 大部分情况连接效率远大于子查询

• 在有大量记录的表分页时使用limit

• 对于经常使用的查询，可以开启查询缓存

• 多使用explain和profile分析查询语句

• 查看慢查询日志，找出执行时间长的sql语句优化

索引类型：

• B+ TREE、HASH、R TREE、FULL TEXT
• 聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
• 主键索引、二级（辅助）索引
• 稠密索引、稀疏索引：是否索引了每一个数据项
• 简单索引、组合索引: 是否是多个字段的索引
• 左前缀索引：取前面的字符做索引
• 覆盖索引：从索引中即可取出要查询的数据，性能高

1.索引管理

1）查看索引

show index from 表名;
show keys from 表名;

show index from hellodb.students;

2）建立索引

CREATE INDEX 索引名 ON 表名 (列名[(length)]);

select * from students where name='xu xian';  #找许仙

create index idx_name on students(name(5));    #已name  为字段建立索引


show index from students;                     #查看 索引 会多一条
explain select * from students where name='xu xian';   #可以看到使用了索引


[root@localhost ~]# mysql  -uroot -p'自己的密码' hellodb < testlog.sql
#生成测试环境
(root@localhost) [hellodb]> call sp_testlog;
#生成  一百万条数据

create index index_name on testlog(name);  #建立索引
drop index inx_name on testlog;            #删除索引

create index index_name on students(name);  #给students 表加 name表
explain select * from students where name like 's%';  #找s开头的人看是否会用索引
explain select * from students where name like '%s';  #找s结尾的人是否会用
explain select * from students where name like '%s%'; #包含s的是否会调用索引
explain select * from students where name like 'x%';  #是否使用索引

3）删除索引

直接删除索引

DROP INDEX 索引名 ON 表名;

2.EXPLAIN 工具

以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息，用来查看查询优化器如何执行查询

EXPLAIN SELECT clause

 explain select * from stundets where stuid=20;

说明：

列名	说明
id	执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置
select_type	简单查询：SIMPLE|复杂查询：PRIMARY（最外面的SELECT）、DERIVED（用于FROM中的子查询）、UNION（UNION语句的第一个之后的SELECT语句）、UNIONRESUlT（匿名临时表）、SUBQUERY（简单子查询）
table	访问引用哪个表（引用某个查询，如“derived3”）
type	关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式
possible_keys	查询可能会用到的索引
key	显示mysql决定采用哪个索引来优化查询
key_len	显示mysql在索引里使用的字节数
ref	当使用索引列等值查询时,与索引列进行等值匹配的对象信息
rows	为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值
Extra	额外信息 Using index：MySQL将会使用覆盖索引，以避免访问表 Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤 Using temporary：MySQL对结果排序时会使用临时表 Using filesort：对结果使用一个外部索引排序

说明： type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：NULL> system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref

类型	说明
All	最坏的情况,全表扫描
index	和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，扫描索引的数据，它比按索引次序全表扫描的开销要小很多
range	范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range
ref	一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
eq_ref	最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 （高效）
const	当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效）
system	这是const连接类型的一种特例，表仅有一行满足条件。
Null	意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效）

3.使用 profile 工具 监控

#打开后，会显示语句执行详细的过程
set profiling = ON;
#查看语句,注意结果中的query_id值
show profiles ;
#显示语句的详细执行步骤和时长
Show profile for query #  
show profile for query 1;

八.并发控制

1.锁机制

锁类型：

• 读锁：共享锁，也称为 S 锁,只读不可写（包括当前事务） ，多个读互不阻塞 只能读 不能写 别人也能看
• 写锁：独占锁，排它锁，也称为 X 锁,写锁会阻塞其它事务（不包括当前事务）的读和写 写锁 别人看都看不了
• S 锁和 S 锁是兼容的，X 锁和其它锁都不兼容，举个例子，事务 T1 获取了一个行 r1 的 S 锁，另外事务 T2 可以立即获得行 r1 的 S 锁，此时 T1 和 T2 共同获得行 r1 的 S 锁，此种情况称为锁兼容，但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁，则必须等待 T1 对行 r1 锁的释放，此种情况也称为锁冲突

锁粒度：

• 表级锁：MyISAM 存储引擎 mysql
• 行级锁：InnoDB

实现

• 存储引擎：自行实现其锁策略和锁粒度
• 服务器级：实现了锁，表级锁，用户可显式请求

分类：

• 隐式锁：由存储引擎自动施加锁

• 显式锁：用户手动请求

  锁策略：在锁粒度及数据安全性寻求的平衡机制

update students set teacherid=1 where stuid=6；   #同时在两个窗口执行只有一个能成功

加锁：
LOCK TABLES 表名 [[AS] alias] 锁的类别  [, tbl_name [[AS] alias] lock_type] ...

锁的类别：
读锁
写锁


lock tables students  read;   #加读锁
update students set classid=2 where stuid=24;
unlock  tables;     #释放锁

flush tables  with  read  lock;   #整个数据库加锁
unlock  tables;

2.事务

事务的概念
●事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。
●事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。
●事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。
●事务通过事务的整体性以保证数据的一致性。
●事务能够提高在向表中更新和插入信息期间的可靠性。

进到 数据库 当中

每一条命令默认开启了一次事务

ACID特性：

A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚

C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态,类似于质量守恒定律(A1wB 0 A1w 给 B转1w 始终保持A+B=1w)

I： Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发.(不最后提交看不到，脏数

D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

begin;                #开启一个事务
....   create database/table   insert into   update XX set   delete from     #事务中写操作
savepoint XX;         #在事务中创建回滚点
rollback to XX;       #在事务中回滚到指定的回滚点
commit;    rollback;  #提交或回滚结束事务  # create  drop alter  这些撤回不了

set autocommit=0  #是否自动提交事务  退出后要再加


死锁：
两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态
范例：找到未完成的导致阻塞的事务
BEGIN;
#开启事务
update students set classid=10;
#终端1

update students set classid=20;
#终端2

show engine innodb status;
#在第三个会话中执行

1）事务隔离级别

MySQL 支持四种隔离级别，事务隔离级别从上至下更加严格

select @@tx_isolation;    #系统隔离级别，是系统自带变量
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

vim  /etc/my.cnf
transaction-isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLEREAD|SERIALIZABLE'

SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLEREAD|SERIALIZABLE'

隔离级别	脏读	可重复读	幻读	加读锁
读未提交	可以出现	可以出现	可以出现	否
读提交	不允许出现	可以出现	可以出现	否
可重复读	不允许出现	不允许出现	可以出现	否
序列化	不允许出现	不允许出现	不允许出现	是

• READ UNCOMMITTED 未提交可读 可读取到未提交数据，产生脏读
• READ COMMITTED 提交可读 可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致可读
• REPEATABLE READ 幻读
  可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
• SERIALIZABLE 串读
  可串行化，未提交的读事务阻塞修改事务（加读锁，但不阻塞读事务），或者未提交的修改事务阻塞其它事务的读写（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差

MVCC和事务的隔离级别：

MVCC（多版本并发控制机制）只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁

可重复读 默认是此隔离级别

#1.打开两个终端的 数据库，都开启事务
use hellodb;
begin;


#在其中一个终端上插入数据在未提交前，另一个终端的事务无法看到修改的结果，但是自己可以看见
insert teachers values(5,'a',79,'M');
select * from teachers;

#终端1 提交后  终端2只要不结束事务还是看不到，需要提交事务后才可以看到。

演示第一隔离级别 可看见脏读

vim /etc/my.cnf
[mysqld]
transaction-isolation=READ-UNCOMMITTED

systemctl restart mysqld

select @@tx_isolation;   #查看隔离级别 

begin;
#在其中一个终端上插入数据在未提交前,另一终端也可以看见
insert teachers values(6,'c',80,'M');
select * from teachers;

演示串行化 最严格的隔离级别

vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE
systemctl restart mysqld

begin;
#开启事务两边可以同时读表，会互相锁
select * from teachers;
delete from teachers where tid=5;
#无法删除加锁  并发性较差

九.存储引擎

• storage limits（存储上限）： myisam 256t innodb64 但是mysql 根本达不到这么大的数据量
• transactions（事务）： myisam 不支持 innodb64 支持
• locking granularity（锁级别）： myiasam 表级 innodb64 行级
• 提高mvcc（多版本的并发控制） ： 提高数据库的并发性
• data caches （数据缓存）：

1.MyISAM 引擎特点

• 不支持事务
• 表级锁定
• 读写相互阻塞，写入不能读，读时不能写
• 只缓存索引
• 不支持外键约束
• 不支持聚簇索引
• 读取数据较快，占用资源较少
• 不支持MVCC（多版本并发控制机制）高并发
• 崩溃恢复性较差
• MySQL5.5.5 前默认的数据库引擎

MyISAM 存储引擎适用场景

• 只读（或者写较少）
• 表较小（可以接受长时间进行修复操作）

MyISAM 引擎文件

• tbl_name.frm 表格式定义
• tbl_name.MYD 数据文件
• tbl_name.MYI 索引文件

2.InnoDB引擎特点

• 行级锁
• 支持事务，适合处理大量短期事务
• 读写阻塞与事务隔离级别相关
• 可缓存数据和索引
• 支持聚簇索引
• 崩溃恢复性更好
• 支持MVCC高并发
• 从MySQL5.5后支持全文索引
• 从MySQL5.5.5开始为默认的数据库引擎

其它存储引擎

• Performance_Schema：Performance_Schema数据库使用

• Memory ：将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎

• MRG_MyISAM：使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large Data Base)环境，如数据仓库

• Archive ：为存储和检索大量很少参考的存档或安全审核信息，只支持SELECT和INSERT操作；支持行级锁和专用缓存区

• Federated联合：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独MySQL服务器的能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境

• BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性

• Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性

• CSV：CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换

• BLACKHOLE ：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储

• example："stub"引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎

3.管理存储引擎

查看mysql支持的存储引擎

show engines;

show table status like 'user'\G   #这张表用的是 myisam
show table status from mysql\G ;

查看当前默认的存储引擎

show variables like '%storage_engine%';

设置默认的存储引擎

vim /etc/my.cnf
[mysqld]
default_storage_engine = InnoDB

查看库中所有表使用的存储引擎

show table status from db_name;

查看库中指定表的存储引擎

show table status like  'tb_name';
show create table tb_name;

设置表的存储引擎：

CREATE TABLE tb_name(... ) ENGINE=InnoDB;
ALTER TABLE tb_name ENGINE=InnoDB;

4.MySQL中的系统数据库

• mysql 数据库

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

• information_schema 数据库

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

• performance_schema 数据库

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

• sys 数据库

MySQL5.7之后新增加的数据库，库中所有数据源来自performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DataBase的运行情况

5.存储过程

存储过程：多表SQL的语句的集合，可以独立执行，存储过程保存在mysql.proc表中

存储过程优势
1、执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
2、SQL语句加上控制语句的集合，灵活性高
3、在服务器端存储，客户端调用时，降低网络负载
4、可多次重复被调用，可随时修改，不影响客户端调用
5、可完成所有的数据库操作，也可控制数据库的信息访问权限

##创建存储过程##
DELIMITER $$							#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
CREATE PROCEDURE Proc()					#创建存储过程，过程名为Proc，不带参数
-> BEGIN								#过程体以关键字 BEGIN 开始
-> select * from Store_Info;			#过程体语句
-> END $$								#过程体以关键字 END 结束
DELIMITER ;								#将语句的结束符号恢复为分号

##调用存储过程##
CALL Proc;

##查看存储过程##
SHOW CREATE PROCEDURE [数据库.]存储过程名;		#查看某个存储过程的具体信息

SHOW CREATE PROCEDURE Proc;

SHOW PROCEDURE STATUS [LIKE '%Proc%'] \G


##存储过程的参数##
IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE 数据库名.存储过程名([in变量名 类型,out 参数 2，...])
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
	BEGIN
		[DECLARE 变量名 类型 [DEFAULT 值];]
		存储过程的语句块;
	END$$

DELIMITER ;

DELIMITER $$

CREATE
    PROCEDURE `demo`.`demo2`(IN s_sex CHAR(1),OUT s_count INT)
	-- 存储过程体
	BEGIN
		-- 把SQL中查询的结果通过INTO赋给变量
		SELECT COUNT(*) INTO s_count FROM student WHERE sex= s_sex;
		SELECT s_count;
		
	END$$
DELIMITER ;



-- @s_count表示测试出输出的参数
CALL demo2 ('男',@s_count);

是本地存储

• example："stub"引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎

3.管理存储引擎

查看mysql支持的存储引擎

show engines;

show table status like 'user'\G   #这张表用的是 myisam
show table status from mysql\G ;

查看当前默认的存储引擎

show variables like '%storage_engine%';

设置默认的存储引擎

vim /etc/my.cnf
[mysqld]
default_storage_engine = InnoDB

查看库中所有表使用的存储引擎

show table status from db_name;

查看库中指定表的存储引擎

show table status like  'tb_name';
show create table tb_name;

设置表的存储引擎：

CREATE TABLE tb_name(... ) ENGINE=InnoDB;
ALTER TABLE tb_name ENGINE=InnoDB;

4.MySQL中的系统数据库

• mysql 数据库

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

• information_schema 数据库

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

• performance_schema 数据库

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

• sys 数据库

MySQL5.7之后新增加的数据库，库中所有数据源来自performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DataBase的运行情况

5.存储过程

存储过程：多表SQL的语句的集合，可以独立执行，存储过程保存在mysql.proc表中

存储过程优势
1、执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
2、SQL语句加上控制语句的集合，灵活性高
3、在服务器端存储，客户端调用时，降低网络负载
4、可多次重复被调用，可随时修改，不影响客户端调用
5、可完成所有的数据库操作，也可控制数据库的信息访问权限

##创建存储过程##
DELIMITER $$							#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
CREATE PROCEDURE Proc()					#创建存储过程，过程名为Proc，不带参数
-> BEGIN								#过程体以关键字 BEGIN 开始
-> select * from Store_Info;			#过程体语句
-> END $$								#过程体以关键字 END 结束
DELIMITER ;								#将语句的结束符号恢复为分号

##调用存储过程##
CALL Proc;

##查看存储过程##
SHOW CREATE PROCEDURE [数据库.]存储过程名;		#查看某个存储过程的具体信息

SHOW CREATE PROCEDURE Proc;

SHOW PROCEDURE STATUS [LIKE '%Proc%'] \G


##存储过程的参数##
IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE 数据库名.存储过程名([in变量名 类型,out 参数 2，...])
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
	BEGIN
		[DECLARE 变量名 类型 [DEFAULT 值];]
		存储过程的语句块;
	END$$

DELIMITER ;

DELIMITER $$

CREATE
    PROCEDURE `demo`.`demo2`(IN s_sex CHAR(1),OUT s_count INT)
	-- 存储过程体
	BEGIN
		-- 把SQL中查询的结果通过INTO赋给变量
		SELECT COUNT(*) INTO s_count FROM student WHERE sex= s_sex;
		SELECT s_count;
		
	END$$
DELIMITER ;



-- @s_count表示测试出输出的参数
CALL demo2 ('男',@s_count);