MySQL/MariaDB基础及简单SQL语句

快乐的老鼠

　　MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言--结构化查询语言（SQL）进行数据库管理。
　　在Linux操作系统内核中提供了MySQL或MariaDB的rpm包，CentOS6以前为MySQL，CentOS7开始更换为MariaDB；可直接使用rpm包进行安装，或到官方站点 www.mysql.com  下载安装；
　　关系型数据库管理系统（DBMS）：
　　范式：第一、第二、第三范式。
　　表：行、列。（一张表可以没有任何一行但至少有一列）
　　数据库：表，索引，视图(虚表)，SQL_interface，存储过程，存储函数，触发器，事件调度器;
　　约束：
　　主键约束：数据唯一且不能为空，每张表只能有一个主键；
　　唯一键约束：数据唯一，可以为空，每张表中不限制唯一键的数量；
　　外键约束：引用性约束或参考性约束；即：如果某表中频繁出现冗余信息，应该将此类信息存储于其他的表中，而此表中该字段的数据为另一张表的主键中所包含的值；
　　检查性约束：表达式约束；
　　关系型数据库管理系统（DBMS）的三层模型：物理层（面向系统管理员）、逻辑层（面向程序员或DBA）、视图层（面向最终用户）。
　　配置文件位置：
　　/etc/mysql/my.cnf    ---    /etc/my.cnf    ---    /etc/my.cnf.d    ---    ~/my.cnf
　　以上四个配置文件可能不存在，跟据执行顺序家目录下的my.cnf最后被执行，也就是说如果后面的文件存在的话执行后的效果会覆盖前面的配置文件；一般只有一个配置文件存在，如果同时存在则后面执行的最后生效。
　　各配置文件中的格式都为键值对儿存储；（eg：innodb_file_per_table = ON等）
　　可以使用mysql_safe命令开启mysql/mariadb服务；
　　mysql_safe  -c, --defaults-file=name
　　根据指定的文件开启mysql服务。不在读取原本的四个配置文件。
　　mysql_safe  -e, --defaults-extra-file=name
　　在读取原本的配置文件过后读取指定的文件，如果有参数冲突会以后面的文件中的参数为准。
　　安装好mysql或mariadb后直接使用mysql命令进入SQL命令行界面：（我设置了登陆密码所以要使用-p选项进入）

　　刚安装的mysql或mariadb可以使用mysql_secure_installation 命令对mysql进行初始化安全设置；
　　简单的命令有：\q退出    \h帮助    \c是前面写入的语句失效    等；

　　MySQL的数据类型：
　　字符型：
　　CHAR(#),BINARY(#)：定长字符类型；CHAR类型不区分字符大小写，BINARY类型区分；
　　VARCHAR(#),VARBINARY(#)：变长字符类型；
　　TEXT：TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT；
　　BLOB：TINYBLOB，BLOB，MEDIUMBLOB，LONGBLOB；
　　数值型：
　　浮点型：近似值；
　　单精度
　　双精度
　　REAL
　　BIT
　　整型：精确值；
　　INTEGER：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT；
　　BOOLEAN
　　DICIMAL
　　OCTAL
　　HEXIMAL
　　日期时间型：
　　日期型：DATE 2018/07/19
　　时间型：TIME 9:38:42
　　日期时间型：DATETIME 2018/07/19 9:38:42
　　时间戳：TIMESTAMP，数值型的整型；
　　内建类型：
　　ENUM：枚举；
　　SET：集合；
　　数据类型的修饰符：
　　字符型：NULL, NOT NULL, DEFAULT 'STRING',CHARACTER SET 'CHARSET', COLLATION "COLLATION"；
　　整型：NULL, NOT NULL, DEFAULT 'VALUE', AUTO_INCREMENT, UNSIGNED；
　　日期时间型：NULL, NOT NULL, DEFAULT 'DATE/TIME/DATETIME'
　　SQL语句：
　　DDL：数据库操作；
　　DML：数据操作；
　　DCL：授权等控制操作；
　　DDL：CREATE、ALTER、DROP、DESC、HELP、SHOW（创建、修改、删除、查看属性、帮助、查看）
　　创建表：
　　CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
　　(create_definition,...)
　　[table_options]
　　[partition_options]
　　//使用SQL语句全新的定义出一张新表，包括表的名称、字段数量、数据类型、存储引擎的选择等各种属性；
　　CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
　　[(create_definition,...)]
　　[table_options]
　　[partition_options]
　　select_statement
　　//利用SELECT语句的查询结果来填充新表的内容，但是新表的表格式可能与基表不一致，很多的数据类型的修饰符可能会丢失；
　　CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
　　{ LIKE old_tbl_name | (LIKE old_tbl_name) }
　　//直接复制基本的表格式到新表上，但新表中没有任何数据，即为空表；
　　注意：
　　1.对于MySQL或MariaDB的表来说，存储引擎是非常重要的概念，通常需要在创建表的时候来指定；如果没有明确指定，则使用默认的存储引擎；
　　2.对于已经创建完成的空表，可以任意调整其存储引擎；
　　3.对于非空表，不建议直接修改表的存储引擎；
　　建议：在创建表之初或者存储数据之前，确定表的存储引擎；
　　删除表：
　　DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]
　　建议：修改表名称使指定表不再被继续使用并非删除；
　　修改表格式：

　　ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [,>　　可以修改的内容：
　　ADD：字段，索引，约束，键(主键，唯一键，外键)
　　CHANGE：字段名称，字段定义格式和字段的位置；
　　MODIFY：字段定义格式和字段的位置；
　　DROP：字段，索引，约束，键；
　　RENAME：修改表名称；
　　查看表结构：
　　DESC [db_name.]tbl_name;
　　查看表的定义方式：
　　SHOW CREATE TABLE tbl_name;
　　查看表的状态和属性信息：
　　SHOW TABLE STATUS [from | in db_name] like 'PATTERN' | where expr;
　　示例：
　　MariaDB [hellodb]> show table status where name='students'\G
　　DML：INSERT/REPLACE、DELETE、UPDATE、SELECE
　　INSERT：向表中插入新的数据记录；每次可以向表中插入一行或多行数据；
　　INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
　　示例：
　　MariaDB [hellodb]> insert into students (Name,Age,Gender) values ('Rio Messi',31,'M');
　　MariaDB [hellodb]> insert into students (Name,Age,Gender) values ('Guo Jing',40,'M'),('Huang Rong',27,'F');
　　没有明确的规定字段名称，则意味着为一行中的各个字段添加数据内容：
　　MariaDB [hellodb]> insert into students values (30,'Liu Bei',57,'M',1,2);
　　注意：添加的数据内容，必须要严格的对应每个数据字段，需要保证数据类型的匹配；
　　INSERT [INTO] tbl_name SET col_name={expr | DEFAULT}, ...
　　示例：
　　MariaDB [hellodb]> insert into students set Name='Tang Xuanzang',Age=35,Gender='M';
　　INSERT [INTO] tbl_name [(col_name,...)] SELECT ...
　　将后面SELECT语句的查询结果插入到选中的目标表中；注意下列问题：
　　1.SELECT语句的查询结果中包含的字段数量，应该和目标表中的指定字段数量相同；
　　2.SELECT语句的查询结果中包含的各字段的数据类型，必须要与目标表中各字段的数据类型保持一致；
　　此种插入数据的方法，更多的用于表复制操作；
　　此前曾经使用CREATE TABLE命令通过复制表格式的方式创建过一个空的新表，然后再将原表中的数据以方法复制到新表中；
　　REPLACE命令与INSERT命令的功能几乎完全相同，除了一种特殊情况之外：
　　当向表中插入数据时，如果主键位置或唯一键位置出现重复数据时，不会继续插入而是选择替换对应行中各字段的数据；
　　DELETE：
　　Single-table syntax:
　　DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
　　Multiple-table syntax:
　　DELETE tbl_name[.*] [, tbl_name[.*]] ... FROM table_references [WHERE where_condition]
　　默认情况下，MySQL或MariaDB都不会阻止不带有WHERE条件子句的删除操作，这将意味着，有可能会因为此操作导致清空整张表中的数据；
　　限制条件：
　　WHERE where_condition
　　LIMIT row_count
　　ORDER BY ... LIMIT row_count
　　WHERE where_condition LIMIT row_count
　　WHERE where_condition ORDER BY ... LIMIT row_count
　　示例：
　　MariaDB [hellodb]> delete from students limit 3;
　　删除正常的查询结果中的前三行数据记录；
　　MariaDB [hellodb]> delete from students where Age delete from students where Name like 'h%' limit 2;
　　删除Name字段以"H|h"开头的所有数据记录中的前两条记录；
　　MariaDB [hellodb]> delete from students order by age desc limit 3;
　　删除根据Age字段进行降序排序的查询结果中的前三条数据记录；
　　UPDATE：
　　Single-table syntax:
　　UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
　　Multiple-table syntax:
　　UPDATE table_references SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition]
　　默认情况下，MySQL或MariaDB都不会阻止不带有WHERE条件子句的修改操作，这将意味着，有可能会因为此操作导致整张表中的所有数据记录被同时修改；所以需要使用限制条件，限制条件同上；
　　注：在MySQL或MariaDB中，如果服务器变量sql_safe_updates=ON，则可以阻止不带有限制条件的UPDATE更新操作或DELETE删除操作；
　　使用set修改服务器变量参数（当次有效），修改配置文件（永久生效）
　　1.运行时修改：
　　MariaDB [(none)]> SET [GLOBAL|SESSION] system_var_name = expr;
　　MariaDB [(none)]> SET @@[GLOBAL.|SESSION.]system_var_name = expr;
　　示例：
　　set global innodb_file_per_table=1;
　　set @@global.innodb_file_per_table=0;
　　2.永久修改：
　　通过在配置文件中直接书写服务器参数或变量的赋值语句；重启服务即可生效；
　　innodb_file_per_table = ON
　　SELECT
　　Query Cache：MySQL/MariaDB的查询结果缓存；
　　K/V对存储；
　　Key：查询语句经过hash之后的hash值；
　　Value：查询语句的执行结果；
　　MySQL/MariaDB的查询执行路径：
　　1.用户发送请求 --> 查询缓存(命中) --> 响应用户；
　　2.用户发送请求 --> 查询缓存(未命中) --> 解析器 --> 预处理器 --> [查询优化器 -->] 查询执行引擎 --> 存储引擎 --> 查询执行引擎 --> [缓存查询结果 -->] 响应用户；

　　DISTINCT：数据去重；即：重复出现的数据仅显示一次；
　　SQL_CACHE：
　　显式的指出必须将此次的查询语句的执行结果存放至查询缓存；
　　SQL_NO_CACHE：
　　显式的指出绝对不能将此次的查询语句的执行结果存放至查询缓存；
　　query_cache_type服务器变量是MySQL的缓存开关，通常有三个取值：
　　1.ON：启用缓存功能；
　　默认缓存所有符合缓存条件的查询结果；除非使用SQL_NO_CACHE参数明确指出不缓存查询结果；
　　2.OFF：关闭缓存功能；
　　默认不缓存任何查询结果；仅能缓存使用SQL_CACHE参数明确的指出的查询结果；
　　3.DEMAND：按需缓存；
　　如果明确指出SQL_CACHE，即缓存查询结果，否则，默认隐式关闭查询缓存；
　　SELECT查询语句比较复杂分为单表查询和多表查询：此处查询需要限制要求，根据限制查询出用户需要的数据；
　　单表查询：
　　WHERE条件子句：
　　通过指明特定的过滤条件或表达式来实现"选择"运算；过滤条件有下列几种：
　　1.算术表达式：Age+10，
　　算术操作符：+, -, *, /, %；
　　2.比较表达式：Age+10, >=, 　　显示查询结果中的第二行和第三行；

　　MariaDB [hellodb]> select>　　统计每个班级里面的人数：

　　MariaDB [hellodb]> select>　　统计每个班级里面所有人的平均年龄：

　　MariaDB [hellodb]> select>　　统计所有学生中男生和女生的平均年龄：
　　MariaDB [hellodb]> select Gender,avg(Age) as nos from students group by Gender;
　　统计人数超过3人的班级及其人数数据：

　　MariaDB [hellodb]> select>　　统计人数超过3人的班级及其人数数据并根据额班级人数降序排序；

　　MariaDB [hellodb]> select>　　多表查询：
　　多表查询：
　　建议：在生成环境中，能使用单表查询即可得到结果的操作，尽可能使用单表查询；因为多表查询会给服务器造成过大的负载压力；
　　所谓多表查询，即指通过对多个表内容的查询，以获得具有一定关联关系的查询结果的查询方式；也称为连接操作，连接操作也就是将多张表关联在一起的方法；
　　连接操作：
　　交叉连接：也称为笛卡尔积连接；
　　内连接：
　　等值连接：让表和表之间通过某特定字段的等值判断的方式建立的内连接；
　　非等值连接：让表和表之间通过某特定字段的不等值判断的方式建立的内连接；在极少的场合中才有应用；
　　外连接：以某张为基准表，判断参考表与基准表之间的连接关系；
　　左外连接：
　　以左表为基准表，右表为参考表，显示出基准表中所有的行，并将参考表中与基准表中有关联关系的行合并输出，如果基准表中的行与参考表中无关，则输出NULL；
　　连接操作符：LEFT JOIN
　　右外连接：
　　以右表为基准表，左表为参考表，显示出基准表中所有的行，并将参考表中与基准表中有关联关系的行合并输出，如果基准表中的行与参考表中无关，则输出NULL；
　　连接操作符：RIGHT JOIN
　　自然连接：
　　通过MySQL的进程自行判断并完成的连接过程。通常MySQL会使用表中的名称相同的字段作为基本的连接条件；
　　连接操作符：NATRUAL INNER
　　自然外连接：
　　自然左外连接：
　　连接操作符：NATURAL LEFT JOIN
　　自然右外连接：
　　连接操作符：NATURAL RIGHT JOIN
　　自连接：
　　人为的将一张表中的两个字段之间建立的连接关系；
　　示例：
　　交叉内连接：
　　每个学生所在的班级名称：
　　MariaDB [hellodb]> select Name,Class from students as s,classes as c where s.CLassID=c.ClassID;
　　MariaDB [hellodb]> select Name,Class from students,classes where students.CLassID=classes.ClassID;
　　交叉左外连接：
　　每个学生所在班级的名称，即使该学生不属于任何班级：

　　MariaDB [hellodb]> select Name,Class from students left join>　　交叉右外连接：
　　每个班级的学生姓名，即使该班级中没有任何学生；

　　MariaDB [hellodb]> select>　　||

　　MariaDB [hellodb]> select>　　子查询：嵌套查询；
　　在SELECT查询语句中嵌套另一个SELECT查询语句；等同于从某个视图中获取查询结果；
　　SELECT col1,col2,* FROM tbl_name WHERE col OPTS VALUE;
　　示例：
　　用于WHERE子句中的子查询：
　　查询学生中年龄大于全班平均年龄的学生的姓名和年龄；
　　MariaDB [hellodb]> select Name,Age from students where Age>(select avg(Age) from students);
　　用于IN子句中的子查询：
　　查询学生的年龄和老师的年龄相同的学生的名字：
　　MariaDB [hellodb]> select Name from students where Age in (select Age from teachers);
　　查询学生的年龄和老师的年龄相同的学生和老师的名字：
　　MariaDB [hellodb]> select t.Name as Teacher,s.Name as Student from students as s,teachers as t where s.Age=t.Age;
　　用于FROM子句的子查询：
　　查询有班级的学生对应的班级名称：
　　MariaDB [hellodb]> select s.Name,s.Class from (select StuID,students.Name,students.Age,Gender,Class from students,classes where students.ClassID=classes.ClassID) as s;
　　联合查询：
　　将多张表的内容通过多个SELECT语句查询得到的结果组合输出；
　　注意：使用联合查询的前提条件：
　　多张表需要有相同数据类型的字段；
　　操作符：UNION
　　示例：

　　MariaDB [hellodb]> select StuID as>　　select查询占用资源程度：
　　单表查询 --> 多表查询(交叉内连接) --> 多表查询(外连接) --> 子查询 --> 联合查询；