1、相关概念 2、MariaDB通用二进制包安装 3、MySQL通用二进制包安装 4、编译安装MySQL 5、MySQL/MariaDB客户端 6、使用MySQL数据库 ================================================================================================================================================ 相关概念 1、关系型数据库 Relational database，关系型数据库是创建在关系模型基础上的数据库(Relational Model)，关系模型是由埃德加·科德于1970年首先提出的， 数据库管理规则要遵循科德十二定律。 2、数据库管理系统 database management system，DBMS，是为了管理关系型数据库而设计的一种电脑管理软件。例如：关系型数据管理系统(Relational database management system,RDBMS)。常见的 RDBMS 有：Oracle、MySQL、MariaDB、PostgreSQL(pgsql)、EnterproseDB、Microsoft SQL Server。 数据库管理系统的基本功能： 数据定义 数据处理 数据安全 数据备份 3、关系模型 所有数据都表示为数学上的关系。 4、数据库设计范式 数据范式的目的是为更好的减少数据冗余，更好的利用的磁盘空间，数据库范式是数据库内部数据各属性之间的合理化程序。 第一范式(1NF) 关系中的属性都是不可再分最的最基本的数据项。例如二维表中的属性 "地址" ，就不具备属性原子性的性质，地址还可以划分为国家、城市、街道。 所谓第一范式(1NF)是指在关系模型中，对[域]添加的一个规范要求，所有的域都应该是原子性的，[即数据库表的每一列都是不可分割的原子数据项]， 而不能是集合，数组，记录等非原子数据项。[即实体中的某个属性有多个值时，必须拆分为不同的属性]。在符合第一范式（1NF）表中的每个域值只 能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。 说明：在任何一个关系数据库中，第一范式(1NF)是对关系模式的设计基本要求，一般设计中都必须满足第一范式(1NF)。不过有些关系模型中突破了1NF 的限制，这种称为非1NF的关系模型。换句话说，是否必须满足1NF的最低要求，主要依赖于所使用的关系模型。 第二范式(2NF) 第二范式(2NF)是在第一范式(1NF)的基础上建立起来的，即满足第二范式(2NF)必须先满足第一范式(1NF)。[第二范式(2NF)要求数据库表中的每个实例或 记录必须可以被唯一地区分]。[选取一个能区分每个实体的属性或属性组，作为实体的唯一标识]。 第二范式(2NF)要求实体的属性完全依赖于主关键字。[所谓完全依赖是指不能存在仅依赖主关键字一部分的属性]，如果存在，那么这个属性和主关键字的 这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简 而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。 第三范式(3NF) 第三范式(3NF)是第二范式(2NF)的一个子集，即满足第三范式(3NF)必须满足第二范式(2NF)。简而言之，[第三范式(3NF)要求一个关系中不能包含已在其 它关系中已包含的非主关键字属性]。简而言之，第三范式就是属性不依赖于其它非主属性，也就是在满足2NF的基础上，任何非主属性不得传递依赖于主 属性。 为了满足关系型数据库的设计范式，一张表可能被拆分成多张属性是具有原子性性质的表。当需要查找数据时，又需要将多张表 join 起来，而这个 join(表 连接) 的操作，又会消耗大量的系统资源，造成数据查找性能的下降。 5、二维表结构及相关术语 1、关系(Relation)：一个关系对应一个二维表 2、元组(Tuple)：二维表中的[一行]表示一个元组，也称为记录。 3、属性(Attribute)：字段。二维表中的[列]对应一个属性。列的值称为[属性值] 4、域(Dimain)：属性值的[取值范围]称为域 5、关系模式(Relation Model)：对行的定义即是关系模型。例如：学生(姓名,学号,班级,专业) 6、主键(Primary key)：在一个关系中存在一个属性或多个属性的集合能够唯一标识一个实体，这个属性或属性的集合称为键,主键不能为空。 7、唯一键：在一个关系中存在一个属性或多个属性的集合能够唯一标识一个实体，允许为空(NULL)，唯一键可以有多个。 8、外键：一个表中的字段可填入的数据取决于另外一个主键已有的数据 9、约束(Constraint)：属性值的数据要遵守的限制 10、索引：将表中的一个或者多个字段数据复制另存，并按特定次序排序存储。为了加速存储 11、关系运算： 选择：挑选出符合条件的行 投影：挑选出需要的字段 连接：连接关系表，更好的完成数据查询 12、约束：constraint，表中的数据要遵守的限制 13、视图 view 6、数据库管理系统的组成 在文件的更高层级之上，提供一个逻辑结构，可以使得装载数据时有更快的效率。这种逻辑结构就是数据库管理工具。数据库管理系统在文件系统 之上附加的一层软件程序，能够存取它能够理解的特有的原数据。 数据库管理系统核心组件： 磁盘空间管理器：真正与文件系统关联 缓存管理器：负责将磁盘空间的数据加载到内存 文件和存取接口：与文件系统关联的接口 锁管理器 事务管理器 事务：(transaction)多个操作被当作一个整体。一个整体需要多个操作完成此事件，事务管理器能够保证这个事件能够被完整的执行，而 不是操作还没有完成，即系统认为这个事件已经完成，事务管理器能够识别多个操作是一个事件的组成部分。 [一个事务是指一系列数据库操作组成一个完整逻辑的过程] 例如：银行转账，从原账户扣除金额，在目标账户增加金额，这两个操作构成一个完整的事务。 为保证事务的正确统一性，应具备 ACID 特性：原子性(atomicity，或称不可分割性{一个事务的步骤不能被分割执行})、一致性(consistency {例如：主从复制架构中的主节点和从节点的数据不一致性})、隔离性(isolation，又称独立性{锁机制，有可能出现死锁情况，当出现死锁时， 其中一个事务会被释放不予执行})、持久性(durability) 恢复管理器 SQL引擎(解释器)： 计划执行器 分析器 操作求解器 优化器 7、与数据库服务器的通信 以MySQL为例，数据库启动后，会监听在TCP的某个端口上，等待客户端的请求。客户端与数据库服务器端通信之前先建立TCP三次握手，然后 使用MySQL协议进行验证并通信。 实现数据库客户端的程序有命令行接口(CLI)以及图形界面的程序(GUI)，以及其他库级别的数据库客户端(编程接口)，例如php-mysql等。 SQL:Structure Query Language,结构化查询语言。 8、数据库的抽象 数据库是数据的结构化集合。 物理层：决定数据的存储格式，即数据库管理系统在磁盘上是如何组织文件的 逻辑层：描述数据库中存储什么数据，以及数据间存在的关系 视图层：描述部分数据 9、数据库管理系统优点 相互关联的数据的集合 较少的数据冗余 程序与数据独立 保证数据的安全、可靠 最大限度保证数据段正确性 数据可以并发使用并能同时保证一致性 10、数据库系统架构 单机架构 大型主机/终端架构 分布式架构 主从式C/S 11、实体-关系模型 E-R 实体：客观存在并可以相互区分的客观事物或抽象事件称为实体。在E-R图中用矩形框表示实体，把实体名写在框内 属性：实体具有的特征和性质 联系：数据之间的关系的集合 ================================================================================================================================================ MariaDB通用二进制包安装 1、安装方式 1、源代码编译安装 2、rpm包安装 1.操作系统安装源提供的rpm包 2.官方提供的下载rpm包安装源 3、通用二进制格式安装 2、MariaDB介绍 MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可。开发这个分支的原因之一是：甲骨文公司收购了MySQL后，有将 MySQL闭源的潜在风险，因此社区采用分支的方式来避开这个风险。MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。 MariaDB新特性： 1、插件式存储引擎 MyISAM 不支持事务 在 MariaDB 上实现是 Aria InnoDB 支持事务 在 MariaDB 上的实现是 XtraDB 查看MySQL支持的存储引擎：SHOW ENGINES; 2、诸多扩展和新特性 3、提供测试组件 4、单进程，多线程 连接线程 守护线程 MariaDB文档：https://mariadb.com/kb/en/library/ 3、MariaDB安装 -----RPM包安装 1、rpm包安装 官方yum源链接 http://yum.mariadb.org/ 1、添加MariaDB官方yum源 vim /etc/MariaDB.repo [MariaDB] name=MariaDB-10.1 Install baseurl=http://yum.mariadb.org/10.1/centos73-amd64/ gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 2、安装 yum install mariadb mariadb-server Note： 1、对于 CentOS6 CentOS7 安装 MySQL /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h node1 password 'new-password' 或者 mysql_secure_installation 2、MariaDB安装后 root 用户没有密码，且存在匿名用户，所以需要执行安全初始化脚本或者手动修改密码 3、安装包组 yum groups info mariadb mariadb-client 4、MariaDB通用二进制包安装 MariaDB二进制分发包的包名命名格式为：mariadb-VERSION-OS.tar.gz。MariaDB 10.2 是目前 MariaDB 的稳定版本。 它是建立在具有 MySQL 5.6 和 5.7 功能的 MariaDB 10.1之上。 Note： 1、初始化脚本 For MariaDB 5.1.32 only the line "./scripts/mysql_install_db --user=mysql" should be changed to "./bin/mysql_install_db --user=mysql" 2、MariaDB搜索配置文件 "/etc/my.cnf"(在某些系统上 "/etc/mysql/my.cnf" "~/.my.cnf")。如果您有一个旧 my.cnf文件(可能来自MariaDB或MySQL的系统安装)，则需要注意不要在新的二进制.tar安装中使用旧文件。 3、使用软链接将二进制文件安装到原始名称。这样做就可以通过将软链接移动到另一个目录来轻松地更改MariaDB版本 4、mysql_secure_installation 工具 {安全初始化程序} 设置数据库管理员root口令 禁止root远程登录 删除anonymous用户帐号 删除test数据库 5、解压后的目录 /usr/local/mysql bin # 二进制程序 data # 默认数据库目录 docs include # 头文件 lib # 库文件 man mysql-test scripts share sql-bench support-files # 示例配置文件及服务脚本 6、/mydata/data/ ibdata1 # 存储引擎的数据文件 ib_logfile0 # 事务日志 ib_logfile1 # 事务日志 mysql # 数据库 mysql-bin.000001 # 二进制日志文件 mysql-bin.000002 mysql-bin.index # 二进制日志文件的索引文件 performance_schema # 数据库 test # 数据库 xuekaixin.err # 错误日志，未必都是错误信息，启动、关闭的信息也记录于此。 xuekaixin.pid # pid文件 7、MariaDB配置文件 ini格式，为各程序均提供配置信息，由 [prog_name] 的配置段组成。按照配置文件查找优先顺序，最后查找到的配置文件 最终生效。 配置文件查找次序： /etc/my.cnf /etc/mysql/my.cnf mysqld --defaults-extra-file = /path/to/some_my.cnf # 运行mysqld时指定的配置文件 $MYSQL_BASE/my.cnf # 运行当前进程的用户的家目录下的隐藏文件 .my.cnf Note： .my.cnf # 家目录下的隐藏文件 在家目录创建 .my.cnf 文件,可以避免每次登陆都输入密码 # [mysql] user = root host = localhost password = xjw,./qwe 8、CentOS7 安装mariadb时有可能会遇到日志文件目录和文件不存在，需要自己创建并设置mysql有写权限。 1、创建用户和用户组展开二进制压缩包及初始化操作 { groupadd mysql useradd -g mysql mysql 或者 useradd -r -m -d /mydata -s /sbin/nologin mysql } cd /usr/local tar -zxvpf /path-to/mariadb-VERSION-OS.tar.gz -C /usr/local ln -s mariadb-VERSION-OS mysql # 建议使用软链接，易于测试，升级，降级等，同一时间安装多个MariaDB版本 cd mysql ./scripts/mysql_install_db --user=mysql chown -R root:mysql . # MySQL工作目录属主改为root，属组改为mysql chown -R mysql data # 数据库文件存放目录data 2、创建数据库逻辑卷 Note：分区要修改为 8e 格式 # 创建物理卷 pvcreate /dev/sdb1 # 创建卷组 vgcreate MariaDB-data /dev/sdb1 # 创建逻辑卷 lvcreate -L 10G -n LV-MariaDB-data MariaDB-data # 格式化逻辑卷 mkfs.xfs /dev/MariaDB-data/LV-MariaDB-data # 创建目录 mkdir /mydata # 挂载逻辑卷 mount /dev/MariaDB-data/LV-MariaDB-data /mydata/data/ # 修改目录属主属组(mysql运行mysql进程的用户的mysql，mysql进程在此目录写数据需要有权限) chown mysql:mysql data 3、生成mysql原数据 ./mysql_install_db --user=mysql --datadir=/mydata/data --basedir=/usr/local/mysql [--user=mysql] 4、提供配置文件 mkdir /etc/mysql cp /usr/local/mysql/support-files/my-innodb-heavy-4G.cnf|my-xxx.xnf /etc/mysql/my.cnf 注：此处提供的配置文件要进行修改 5、后续配置 # 导出头文件 ln -sv /usr/local/mysql/include/ /usr/include/mysql # 导出库文件 vim /etc/ld.so.conf.d/mysql.conf /usr/local/mysql/lib ldconfig -l ldconfig -p # 二进制文件 vim /etc/profile.d/mysql.sh export PATH=/usr/local/mysql/bin:$PATH # 导出帮助文档 vim /etc/man_db.conf MANDATORY_MANPATH /usr/local/mysql/man 6、提供服务脚本 复制LSB风格的服务脚本 Note：日志文件的权限可能会报错，需要手动创建日志目录及文件，并给予 setfacl 特殊权限 7、执行安全初始化脚本 5、配置MariaDB my.cnf 配置文件 [mysqld] skip-networking = 1 # 禁用TCP/IP连接，ariaDB默认情况下，为了安全起见，默认是禁用TCP/IP进行连接数据库的。禁用的配置指令 bind-address = # 绑定的IP地址 datadir = /path/to/data_dir # 指定数据库路径 innodb_file_per_table = on # 每张表都在数据库目录下创建一个文件 skip_name_resolve = on # 禁止主机名解析 # 远程连接用户授权 GRANT ALL PRIVILEGES ON db.name.table_name TO 'user_name'@'hostname|IP' IDENTIFIED BY 'user-passwd'; # 通知mysql服务器重读授权表 FLUSH PRIVILEGES; 6、服务器监听的两种socket ip socket # 监听在tcp的3306端口，支持远程通信 unix scoket # 监听在sock文件上(/tmp/lib/mysql/mysql.sock|/var/lib/mysql/mysql.sock),仅支持本地通信 ================================================================================================================================================ MySQL通用二进制包安装 1、下载通用二进制安装包 mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz 2、安装MySQL依赖库libaio yum install libaio Note： 对于MySQL 5.7.19及更高版本：支持非均匀内存访问(NUMA)已​​被添加到通用Linux构建中，该构建现在依赖于 libnuma库; 如果您的系统上没有安装库， 请使用系统的软件包管理器来搜索并安装它(有关示例命令，请参阅最后一个项目符号)。 3、解压到指定目录下并创建mysql的软链接 tar -xf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz -C /usr/local/ ln -sv /usr/local/mysql-5.7.17-linux-glibc2.5-x86_64/ mysql Note： 安装之前要确定/etc/目录下没有之前安装mysql残留的配置文件 ll -d /etc/my* shell> groupadd mysql shell> useradd -r -g mysql -s /bin/false mysql shell> cd /usr/local shell> cd mysql shell> mkdir mysql-files shell> chmod 750 mysql-files shell> chown -R mysql . shell> chgrp -R mysql . shell> bin/mysql_install_db --user=mysql # MySQL 5.7.5 shell> bin/mysqld --initialize --user=mysql # MySQL 5.7.6 and up Note： 执行之后会生成一个临时root密码 2017-10-25T09:58:28.138768Z 1 [Note] A temporary password is generated for root@localhost: us9N>h>5)HDE shell> chown -R root . shell> chown -R mysql data mysql-files shell> cp support-files/mysql.server /etc/init.d/mysql.server ***后续配置参考博客*** ================================================================================================================================================ 编译安装MySQL 1、安装编译工具 yum -y groups install "Development tools" 2、安装依赖库文件 yum -y install ncurses-devel openssl-devel libevent-devel jemalloc-devel cmake 3、解压源码包 tar -xf mariadb-10.1.28.tar.gz 4、进入解压后的目录内 cd mariadb-10.1.28 5、编译参数配置 cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mariadb-10.1.28 -DMYSQL_DATADIR=/mydata/data -DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci 6、修改数据库目录权限 chown -R mysql data # 数据库文件存放目录data 7、生成mysql原数据 ./mysql_install_db --user=mysql --datadir=/mydata/data --basedir=/usr/local/mysql [--user=mysql] 8、提供配置文件 mkdir /etc/mysql cp /usr/local/mysql/support-files/my-innodb-heavy-4G.cnf|my-xxx.xnf /etc/mysql/my.cnf 注：此处提供的配置文件要进行修改 9、后续配置 # 导出头文件 ln -sv /usr/local/mysql/include/ /usr/include/mysql # 导出库文件 vim /etc/ld.so.conf.d/mysql.conf /usr/local/mysql/lib ldconfig -l ldconfig -p # 二进制文件 vim /etc/profile.d/mysql.sh export PATH=/usr/local/mysql/bin:$PATH # 导出帮助文档 vim /etc/man_db.conf MANDATORY_MANPATH /usr/local/mysql/man 10、提供服务脚本 复制LSB风格的服务脚本 Note：日志文件的权限可能会报错，需要手动创建日志目录及文件，并给予 setfacl 特殊权限 或者 [Unit] Description=MariaDB Database Server After=syslog.target After=network.target [Service] Type=simple User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld_safe --basedir=/usr/local/mysql --datadir=/mydata/data TimeoutSec=300 PrivateTmp=false [Install] WantedBy=multi-user.target 11、执行安全初始化脚本 mysql_secure_installation 编译安装MySQL-5.5(马哥文档) cmake的重要特性之一是其独立于源码(out-of-source)的编译功能，即编译工作可以在另一个指定的目录中而非源码目录中进行，这可以保证源码目录不受任何一次编译的影响，因此在同一个源码树上可以进行多次不同的编译，如针对于不同平台编译。 编译安装MySQL-5.5 一、安装cmake 跨平台编译器 # tar xf cmake-2.8.8.tar.gz # cd cmake-2.8.8 # ./bootstrap # make # make install 二、编译安装mysql-5.5.33 1、使用cmake编译mysql-5.5 cmake指定编译选项的方式不同于make，其实现方式对比如下： ./configure cmake . ./configure --help cmake . -LH or ccmake . 指定安装文件的安装路径时常用的选项： -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DSYSCONFDIR=/etc 默认编译的存储引擎包括：csv、myisam、myisammrg和heap。若要安装其它存储引擎，可以使用类似如下编译选项： -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 若要明确指定不编译某存储引擎，可以使用类似如下的选项： -DWITHOUT__STORAGE_ENGINE=1 比如： -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITHOUT_FEDERATED_STORAGE_ENGINE=1 -DWITHOUT_PARTITION_STORAGE_ENGINE=1 如若要编译进其它功能，如SSL等，则可使用类似如下选项来实现编译时使用某库或不使用某库： -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 其它常用的选项： -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DENABLED_LOCAL_INFILE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_DEBUG=0 -DENABLE_PROFILING=1 如果想清理此前的编译所生成的文件，则需要使用如下命令： make clean rm CMakeCache.txt 2、编译安装 # groupadd -r mysql # useradd -g mysql -r -d /mydata/data mysql # tar xf mysql-5.5.33.tar.gz # cd mysql-5.5.33 # cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DMYSQL_DATADIR=/mydata/data \ -DSYSCONFDIR=/etc \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_ARCHIVE_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITH_READLINE=1 \ -DWITH_SSL=system \ -DWITH_ZLIB=system \ -DWITH_LIBWRAP=0 \ -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci # make # make install # cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/mydata/data -DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci # cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/mydata/data -DSYSCONFDIR=/etc ================================================================================================================================================ MySQL/MariaDB客户端使用 1、mysql登录 选项： --usr=USER_NAME | -u USER_NAME # 以哪一个用户的身份连接MySQL，省略表示管理员连接root用户 --passwd=PASSWD | -p PASSWORD # 密码，省略表示空 --host=HOSTNAME | -h HOSTNAME # 连接的主机。省略表示连接本地 --port=# | -P # 指定连接MySQL服务器的端口，my.cnf 文件中 $MYSQL_TCP_PORT 定义 --protocol # 指明使用协议，通常都是TCP协议 -S # 基于本地socket通信时指明socket的路径 -D | --database= # 指明连人数据库后使用的数据库名 -C # 连接服务的通信的过程中是否压缩数据 -e " " # 执行语句，而不连接数据库 示例： # 直接登录 mysql -u root -h localhsost -p ***** mysql -uroot -hlocalhsost -p ***** # 把命令发往mysql服务器然后返回执行结果 mysql -u root -h localhsost -p***** -e 'COMD;' mysql -u root -h localhost -pxjw,./qwe -e 'SHOW DATABASES;' 注：mysql的用户账号由两部分组成，'user_name'@'hostname|IP' ，hostname用于限制此用户可用过哪些主机远程连接mysql服务。 支持使用通配符号。"%" ---> 匹配任意长度的字符 "_" ---> 匹配任意单个字符 2、和MySQL交互的方式 1、客户端命令(命令行接口执行SQL语句)，本地执行 mysql> help # 获取所有客户端命令 +-------------+-------------------+------------------------------------------------------------+ | Command | Shortcut | Description | +-------------+-------------------+------------------------------------------------------------+ | \G | \G | Display current query results vertically. | | \dt | \dt[+] [table] | List or describe tables. | | \e | \e | Edit command with editor (uses $EDITOR). | | \f | \f [name] | List or execute favorite queries. | | \fd | \fd [name] | Delete a favorite query. | | \fs | \fs name query | Save a favorite query. | | \l | \l | List databases. | | \once | \o [-o] filename | Append next result to an output file (overwrite using -o). | | \timing | \t | Toggle timing of commands. | | connect | \r | Reconnect to the database. Optional database argument. | | exit | \q | Exit. | | help | \? | Show this help. | | nopager | \n | Disable pager, print to stdout. | | notee | notee | Stop writing results to an output file. | | pager | \P [command] | Set PAGER. Print the query results via PAGER. | | prompt | \R | Change prompt format. | | quit | \q | Quit. | | rehash | \# | Refresh auto-completions. | | source | \. filename | Execute commands from file. | | status | \s | Get status information from the server. | | system | system [command] | Execute a system shell commmand. | | tableformat | \T | Change the table format used to output results. | | tee | tee [-o] filename | Append all results to an output file (overwrite using -o). | | use | \u | Change to a new database. | +-------------+-------------------+------------------------------------------------------------+ 2、服务器命令(通过mysql协议发往服务器执行并取出结果) 3、MySQL有关程序 Client： mysql mysqldump mysqladmin Server： mysqld_safe mysqld mysqld_multi # 多实例 4、mysql客户端使用 交互模式 客户端命令 服务器端命令 脚本模式 shell> mysql -uUSERNAME -hHOST -pPASSWORD < /path/to/file.sql mysql> source /path/to/file.sql ================================================================================================================================================ 使用MySQL数据库 1、关系型数据库常见组件 database 数据库 table 表 index 索引 view 视图 呈现出来的虚拟表 user 用户 privilege 权限 procedure 存储过程，无返回值 function 存储函数，有返回值 trigger 触发器 event scheduler 事件调度器(任务计划) 2、SQL语句使用 1、SQL语句类型 DDL:Data Defination Language(数据定义语言) CGRATE DROP ALTER DML:Data Manipulation Language(数据操作语言) INSERT DELETE UPDATE DCL:Data Control Language(数据控制语言) GRANT # 用户授权 REVOKE # 权限回收 DQL：Data Query Language(数据查询语言) SELECT SQL语句规范： 字段、数据库名称区分大小写 多行用 ";" 结尾区分 注释行用 /*xxxxxxxx*/ 或者 -- xxxxxxxx 或者 # (MySQL注释) 2、数据库操作 use DATA_NAME; # 设定默认使用的数据库{数据库名称必须字母开头，可以有 #_$ 三个特殊字符} SHOW DATABASES； # 显示数据库 SHOW TABLES [FEOM db_name]; # 显示数据库中的表 DESC TABLES_NAME; # 显示指定表结构 CREATE DATABASE [IF NOT EXISTS] 'databases_name'; # 创建一个数据库 DROP DATABASE [IF EXISTS] 'databases_name'; # 删除有一个数据库 SHOW CHARACTER SET; # 查看支持的所有字符集 SHOW COLLATIONS; # 查看支持的排序规则 SHOW ENGINES; # 查看所有引擎 3、表 创建表 CREATE TABLE [IF NOT EXISTS] table_name(col1_name col1_type [修饰符],col2_name col2_type [修饰符],......,PRIMARY KEY(col1_name,col2_name)) ENGINE='engine_name'; 修饰符： NULL NOT NULL # 不能为空 UNSIGNED # 无符号数 DEFAULT VALUE # 给定默认值 AUTO_INCREMENT # 自动增长类型的字段、必须为主键或唯一键 PRIMARY KEY # 定义主键 (能够唯一标识表中每一个记录的字段或字段的组合) # 从另一张表中选择数据并新建新表，新表会被之间插入查询来的数据 CREATE TABLE table_name SELECT * FROM table_name; # 通过复制现存的表的表结构创建，不复制数据 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) } 删除表 DROP TABLE [IF EXISTS] table_name; 查看表状态 SHOW TABLE STATUS LIKE 'table_name'\G 修改表 ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification] 字段： 添加字段：add ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ] FIRST表示将字段添加到最前面，AFTER col_name 表示添加到指定字段后面 删除字段：drop DROP [COLUMN] col_name 修改字段：alter、change、modify ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] 索引： 添加索引：add 删除索引：drop 插入数据 INSERT INTO table_name (col1,col2,) VALUE (val1,val2....),(val.......); # col1，col2表示字段名称，只给col1和col2字段插入数据 INSERT INTO table_name VALUE (val1,val2,.....),(val1,val2,.....); # 表示插入所有字段的值，可插入多行 INSERT INTO table_name SELECT * FROM table_name; # 批量(从表中选择记录输入到新表)添加记录到表中 删除数据 DELETE FROM table_name WHERE where_condition [ORDER BY 'col_name'] [LIMIT [m],n]; truncate table table_name; # 不记录日志直接删除表中的所有数据 查询 SELECT col1,col2 FROM table_name [WHERE where_condition] [ORDER BY 'col_name' [DESC]] [LIMIT [m],n]; # DESC 表示倒序排序 [LIMIT [m],n] # 表示跳过m个，选择n个 WHERE where_condition: 1.操作符：> < >= <= == != 2.条件组合条件，逻辑操作：and or not 3.LIKE 模糊匹配 %：任意长度任意字符 _：任意单个字符 示例：SELECT * FROM table_name WHERE name LIKE "abc%"; 4.RLIKE 基于正则表达式进行模式匹配 示例：SELECT * FROM table_name WHERE name RLIKE "^abc.$"; 5.IS NULL 6.IS NOT NULL 7.BETWEEN N AND M # 范围 8.IN 示例：SELECT * FROM table_name WHERE name IN ("abc","def"); 多表查询 select table_name1.id,table_name2.name from table_name1 as A,table_name2 as B WHERE A.id=B.id 字段别名 mariadb root@localhost:xjx_data> SELECT id as ID,name as NAME FROM students; +----+--------------+ | ID | NAME | +----+--------------+ | 1 | xuejingfang | | 2 | xuejinwei | | 3 | xuejingna | | 4 | xueyanping | | 5 | zhangfengmei | +----+--------------+ 修改数据 UPDATE table_name SET col1 = value1,col2 = value2,.... WHERE where_condition; 4、索引 索引是特殊的数据结构，能够加速数据的查询。索引通常定义在查找条件的字段上。索引也可以索引名称。索引 只能删除和创建。提高查询速度，但占用额外的空间，影响插入速度。 创建索引： CREATE INDEX index_name [index_type] ON tbl_name (index_col_name,...); 查看索引： SHOW INDEXES FROM [db_name.]table_name; 删除索引： DROP INDEX index_name ON tbl_name 5、用户账号 CREATE USER 'username'@'hostname|IP' [IDENTIFIED BY 'passwd']; 删除用户 DROP USER 'username'@'hostname|IP'; 修改密码 1、SET PASSWORD FOR 'user'@'host' = PASSWORD('password'); 2、UPDATE mysql.user SET password=PASSWORD('password') WHERE User=' '; 3、mysqlamin 6、授权管理 MySQL服务器属性变量查看 SHOW {GLOBAL|SESSION} VARIABLES [LIKE where_condition]; 状态变量查看 SHOW {GLOBAL|SESSION} STATUS [LIKE where_condition]; 用户授权 GRANT ALL PRIVILEGES ON db_name.table_name TO 'user_name'@'hostname' [IDENTIFIED BY 'user-passwd']; # 创建用户并授权访问数据库 ALL # 所有权限 SELECT|ALTER # 查询，修改权限 *.* # 所有库的所有表 db_name.* # 指定库的指定表 db_name.tbl_name # 指定库的指定表 db_name.routine_name # 指定库的存储例程 'user_name'@'172.18.%.%' # 表示172.18网段所有主机都可以登录 'user_'@'172.18.%.%' # 表示172.18网段所有主机且用户名是user开头后面跟一个字符的账号都可以登录 FLUSH PRIVILEGES; # 通知mysql服务器重读授权表 查看某个用户的授权信息 SHOW GRANTS FOR 'username'@'hostname'; SHOW GRANTS FOR CURRENT_USER; # 查看当前用户的授权 7、回收授权 REVOKE priv_type,.... ON db_name,tb_name FROM 'user_name'@'hostname'; 示例： REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost'; REVOKE ALL PRIVILEGES ON xjx_data.* FROM 'rem'@'172.18.26.1'; Note：MariaDB 服务进程启动时都会读取 mysql 库中的所有授权表至内存中。GRANT 或者 REVOKE 等执行权限操作 会保存于表中，MariaDBd 的服务进程会自动重读授权表。 对于不能够或者不能及时重读授权表的命令，可手动让 MariaDB 的服务进程重读授权表：FLUSH PRIVILEGES 3、数据类型 字段数据类型：决定了比较方式、存储空间、能够参与的运算。 1.字符型 char(#) # 固定长度 255个字符 varchar(#) # 自动长度 65536个字符 tinytext # 可变长度 255个字符 binary(#) # 区分大小写，固定大小 varbinary() # 区分大小写，自动长度。可直接创建索引 对象存储： text # 大段字符。可变长度，65536个字符，不能有默认值 tinytext mediumtext longtext # 可变长度 最多2的32次方-1个字符 BINARY(M) # 固定长度，可存二进制或字符，允许长度为0M字节 VARBINARY(M)# 可变长度，可存二进制或字符，允许长度 为0-M字节 BLOB # 以二进制方式存储，不分区大小写，只能将数据整体读出 tinyblob mediumblob longblob 内置类型： ENUM # 枚举，只能从中挑一个 ENUM(, , ,) SET # 集合，任意组合 字符类型修饰符： 定义在定义字段之后 NOT NULL # 非空字符约束 NULL # 可以为空 DEFAULT 'string' # 默认字符 CHARACTER SET ' ' # 字符集 COLLATION # 使用的排序规则 2.数值型 精确数值型 整数 init tinyint # 1个字节 smallint # 2个字节 mediumint # 3个字节 bigint # 8个字节 Note： 取值范围加上 unsigned 关键字，则无负数取值范围，例如 tinyint unsigned tinyint(m) m指定显示宽度， 规定了MySQL 的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说， Int(1)和Int(20)是相同的 decimal # 十进制 修饰符： AUTO_INCREMENT {满足条件：UNSIGNED、PRIMARY、NOT NULL} 近似数值型 float # 单精度浮点型 单精度浮点型 8位精度(4字节) m总个数，d小数位 double # 双精度浮点型 双精度浮点型 16位精度(8字节) m总个数，d小 数位 Note: 设一个字段定义为float(6,3)，如果插入一个数123.45678,实 际数据库里存的是123.457，但总个数还以实际为准，即6位 3.日期时间型 date # 日期 time # 时间 datetime # 日期时间，占8个字节 timestamp # 时间戳，自动存储记录修改时间。这个字段里的时间数据会随其他字段修 改的时候自动刷新，这个数据类型的字段可以 存放这条记录最后被修改 的时间。 year(2) # 时间 year(4) 4.布尔型 5.NULL 5.定点数 4、练习 # 创建数据库 CREATE DATABASE xjx_data; # 创建表 CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED); # 查看表 SHOW TABLES; # 查看表结构 DESC students; # 查看创建数据库或者表的SQL语句 SHOW CREATE TABLE students; # 添加一个字段 ALTER TABLE students ADD gender ENUM('m','f'); # 给表中插入数据 INSERT INTO students VALUES (2,'xuejinwei',7,'m'),(1,'xuejingfang',9,'f'),(3,'xuejingna',5,'f'); # 查看所有字段的信息 SELECT * FROM students; # 按id排序，且限制只显示前两行数据 SELECT * FROM students ORDER BY 'id' LIMIT 2; # 查找ID大于3小于5的数据 SELECT * FROM students WHERE 3