对MySQL/MariaDB的认知
MySQL/MariaDB是一种关系型数据库管理系统(RDBMS)，在这种RDBMS中，含有二维表结构；表分为：
Row: 记录（行）；
Column: 字段（列）；
注意：任何一张RDBMS表中，可以没有记录，但必须要有字段，也就是列；
在Linux中，MySQL的主配置文件在/etc/my.cnf中；
安装好之后，我们最好加入两行语句在进行启动；
innodb_file_per_table = ON
skip_name_resolve=ON
mysql中分为交互式命令和非交互式命令工具：
非交互式命令：
--database=db_name, -D db_name
用于指定使用mysql客户端程序连接到mysql服务器之后，默认使用哪个数据库；如果不给定该选项，默认为NULL；
--execute=statement, -e statement
连接至mysql服务器并将后面的SQL语句直接交给服务器运行，并直接返回执行结果，并直接退出交互式登录模式；
--host=host_name, -h host_name
指定此次mysql程序所连接的mysql服务器的主机名称或主机IP地址；默认时localhost(127.0.0.1)
--user=user_name, -u user_name
用于指定连接mysql服务器时使用的用户名；默认是'root'@'localhost'
mysql的用户名：USERNAME@HOSTNAME
USERNAME表示登录的用户名称；
HOSTNAME表示用户通过那个特定的客户端主机连接到mysql的服务器；
如：'root'@'172.16.0.1'
表示只有172.16.0.1主机可以通过root用户连接至mysql服务器
HOSTNAME中可以使用通配符：
_：任意单个字符；
%：任意长度的任意字符；
如：'root'@'172.16.%.%'
表示172.16.0.0/16网段中的所有主机都可以使用root用户连接至mysql服务器；
如：'root'@'172._.%'
表示172.16.0.0-172.16.9.255地址的所有主机都可以使用root用户连接至mysql服务器；
注意：默认情况下，所有用户均为空密码，并且还有匿名用户存在，因此，为了增强安全性，可以使用mysql_secure_installation命令完成账户安全初始化：mysql服务器仅能让root用户在本地登录，并且移除匿名用户，并且移除匿名用户和test数据库；
--passwod[=password], -p
用于指定连接mysql服务器时，为对应用户指定身份验证所需密码；如果密码部分省略不写，执行命令后需要在新行中给出密码，这种方式相对更安全；
交互式命令：
客户端命令：mysql程序自身完成或执行的命令，在客户端所在地运行；
clear (\c)
停止发送当前正在键入的命令或语句并直接返回提示符
delimiter (\d)
设置语句结束标记；默认语句结束标记为";"
ego (\G)
将其前面的语句送往mysql服务器运行，并将返回的结果纵向显示
go (\g)
无需输入语句结束标记，字节将其前面的语句送往服务器执行；
exit,quit (\q)
退出mysql的机交互式模式
status (\s)
获取mysql服务器的状态信息
use (\u)
将主子定的数据库作为当前正在使用的数据库；
source (\.)
可以在mysql交互式模式中执行SQL脚本文件；
?,help (\?)
获取客户端命令的帮助信息；
服务器端命令：mysql客户端程序通过mysql协议向mysql服务器端发送的SQL语句；
注意：再书写服务器端命令时，每个SQL语句都必须以指定的结束标记结尾，默认是";"
MySQL的数据类型：
字符型：
数值型：
浮点型；
整型
日期时间型：
内建类型：
枚举：
集合：
对MySQL的操作，分为DDL和DML
DDL：数据定义语言
以数据库为对象的操作：
DATABASE, TABLE, VIEW, FUNCTION, PRECEDURE, INDEX, ..
DML：数据操纵语言
以数据为对象的操作：
INSERT, DELETE, UPDATE/REPLACE, SELECT, ..
DDL:
数据库
创建数据库：
CREATE {DATABASE | SCHEMA} db_name CHARACTER SET [=] charset_name | COLLATE [=] COLLATElation_name
修改数据库：
> 删除数据库：
DROP {DATABASE | SCHEMA} db_name
表：
创建表：
CREATE TABLE tbl_name
(create_definition,...)
//使用SQL语句全新的定义出一张新表，包括表的名称，字段数量，数据类型、存储引擎的选择等各种属性；
Or:
CREATE TABLE tbl_name
[(create_definition,...)]
select_statement
//利用SELECT语句的查询结果来填充新表的内容，但是新表的表格式可能与基表不一致，很多的数据类型的修饰符可能会丢失；
Or:
CREATE TABLE tbl_name
{ LIKE old_tbl_name
| (LIKE old_tbl_name) }
//直接复制基表的表格式复制到新表上，但新表中没有任何数据，即为空表；
注意：
1.对于MySQL/MariaDB的表来说，存储引擎是非常重要的概念，通常需要在创建表的时候来指定；如果没有明确指定，则使用默认的存储引擎；
2.对于已经创建完成的空表，可以任意调整其存储引擎；
3.对于非空表，不建议直接修改表的存储引擎；
删除表：
DROP TABLE tbl_name [, tbl_name] ...
建议：使用修改表名称的方式使指定表不再被继续使用；
修改表的结构定义：
> 可以修改的内容：
ADD：字段，索引，约束，键(主键，唯一键，外键)
CHANGER：字段名称，字段定义格式和字段的位置；
MODIFY：字段定义格式和字段的位置；
DROP：字段，索引，约束，键；
RENAME：修改表名称；
查看表结构：
DESC tab1_name；
查看表的定义方式：
SHOW CREATE TABLE tb1_name;
查看表的状态和属性信息：
SHOW TABLE STATUS like 'PATTERN' | where expr;
视图：VIEW，虚表；
就是一个SELECT语句的执行结果；
创建视图：
CREATE VIEW view_name [(column_list)] AS SELECT clause;
删除视图：
DROP VIEW view_name [, view_name] ...
注意：
能否在视图中实现插入新的数据记录；取决于基表中没有被视图所包含选择的字段是否要求不能为空，如果有此类约束，则结果是无法插入新数据；否则可以插入新数据，没有被视图选择的字段内容，默认都为"NULL"；
DML: 操纵数据；
INSERT/REPLACE、DELECT、UPDATE、SELECT
INSERT：向表中插入新的数据记；每次可以向表中插入一行或多行数据；
INSERT tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),..
示例：
insert into students (Name,Age,Gender) values ('Rio Messi',31,"M");
INSERT tbl_name SET col_name={expr | DEFAULT},..
示例：
insert into students set Name='Tang Xuanzang',Age='35',Gender='M';
INSERT tbl_name [(col_name,...)] SELECT ...
将后面SELECT语句的查询结构插入到选中的目标表中；注意下列问题：
1.SELECT语句的查询结果中包含的字段数量，应该和目标表中的指定字段数量相同；
2.SELECT语句的查询结果中包含的各字段的数据类型，必须要与目标表中各字段的数据类型保持一致；
此种插入数据的方法，更多的用于表复制操作：
此前曾经使用CREATE TABLE命令通过复制表格式的方式创建过一个空的新表，然后在将原表中的数据复制到新表中；
没有明确的规定字段安名称，则意味着为一行张总的各个字段添加数据内容；
注意：添加的数据内容，必须要严格的对应每个数据字段，需要保证数据类型的匹配；
REPALCE命令与INSERT命令几乎完全相同，除了一种特殊情况之外；
当向表中存放数据时，如果主键位置或唯一键位置出现重复数据时，不会继续插入，而是选择替换对应行中各字段的数据；
DELETE：
Single-table syntax:
DELETE FROM tbl_name WHERE where_condition]
Multiple-table syntax:
DELETE tbl_name[.*] [, tbl_name[.*]] ... FROM table_references
or
DELETE FROM tbl_name[.*] [, tbl_name[.*]] ... USING table_references
注意：默认情况下，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
示例：
delete from students limit 3;
删除在正常的查询结果中的前三行数据记录；
delete from students where Age select ClassID,avg(Age) as nos from students where ClassID is not null group by ClassID; 统计所有学生中男生和女生的平均年龄：
MariaDB > select Gender,avg(Age) as nos from students group by Gender; Having子句：对于经过分组归类并进行了聚合运算之后的结果进行条件过滤；
其条件表达式的书写格式与WHERE子句相同；
示例：
统计人数超过3人的班级及其人数数据：
MariaDB > select ClassID,count(ClassID) as nos from students where ClassID is not null group by ClassID having nos>=3; ORDER BY子句：更具指定的字段将查询结果进行排序，可以使用过升序或降序进行排序，默认升序；
升序：ASC；
降序：DESC；
示例：
MariaDB > select ClassID,count(ClassID) as nos from students where ClassID is not null group by ClassID having nos>=3 order by nos desc; LIMIT子句：
对于查询的结果进行限定行数的输出；
1.LIMIT row_count
offset：偏移量，在输出结果中，从第一行开始(含)跳过的不显示的行数；
row_count：要显示的行数；
示例：显示查询结果中的第二行和第三行；
MariaDB > select ClassID,count(ClassID) as nos from students where ClassID is not null group by ClassID having nos>=3 order by nos desc limit 1,2; 2.LIMIT row_count OFFSET offset
示例：显示查询结果中的第二行和第三行；
MariaDB > select ClassID,count(ClassID) as nos from students where ClassID is not null group by ClassID having nos>=3 order by nos desc limit 2 offset 1; 多表查询：
建议：能使用单表查询即可得到结果的操作，尽可能使用单表查询；因为多表查询会给服务器造成过大的负载压力；
所谓多表查询，即指通过对多个表内容的查询，以获得具有一定关联关系的查询结果的查询方式；也称为连接操作，连接操作也就是将多张表关联在一起的方法；
连接操作：
交叉连接：
也称为笛卡尔积连接；
内连接：
等值连接：让表和表之间通过某特定字段的等值判断的方式建立的内连接；
非等值连接：让表和表之间通过某特定字段的不等值判断的方式建立的内连接；在既减少的场合中才有应用；
外连接：以某张表为基准表，判断参考表与基准表之间的连接关系；
左外连接：
以左表为基准表，显示出基准表中所有的行，并将参考表中与基准表中有关联关系的行合并输出，如果基准表中的行与参考表中无关，则输出NULL；
连接操作符：LEFT JOIN
右外连接：
以右表为基准表，显示出基准表中所有的行，并将参考表中与基准表中有关联关系的行合并输出，如果基准表中的行与参考表中无关，则输出NULL；
连接操作符：RIGHT JOIN
自然连接：
通过MySQL的进程自行判断并完成的连接过程；通常MySQL会使用表中的名称相同额字段作为基本的连接条件；
连接操作符：NATRUAL INNER
自然外连接：
自然左外连接：
连接操作符：NATURAL LEFT JOIN
自然右外连接：
连接操作符：NATURAL RIGHT JOIN
自连接：
人为的将一张中的两个字段之间建立的连接关系；
示例：
交叉内连接：
每个学生所在的班级名称：
MariaDB > select Name,Class from students as s,classes as c where s.CLassID=c.ClassID;MariaDB > select Name,Class from students,classes where students.CLassID=classes.ClassID; 交叉左外连接：
每个学生所在班级的名称，即使该学生不属于任何班级：
MariaDB > select Name,Class from students left join classes on students.CLassID=classes.ClassID; 交叉右外连接：
每个班级的学生姓名，即使该班级中没有任何学生；
MariaDB > select Class,Name from students right join classes on students.ClassID=classes.ClassID; ||
MariaDB > select Class,Name from classes left join students on students.ClassID=classes.ClassID; 子查询：嵌套查询；
在SELECT查询语句中嵌套另一个SELECT查询语句；等同于从某个视图中获取查询结果；
SELECT col1,col2,* FROM tbl_name WHERE col OPTS VALUE;
示例：
用于WHERE子句中的子查询：
查询学生中年龄大于全班平均年龄的学生的姓名和年龄；
MariaDB > select Name,Age from students where Age>(select avg(Age) from students); 用于IN子句中的子查询：
查询学生的年龄和老师的年龄相同的学生的名字：
MariaDB > select Name from students where Age in (select Age from teachers); 查询学生的年龄和老师的年龄相同的学生和老师的名字：
MariaDB > select t.Name as Teacher,s.Name as Student from students as s,teachers as t where s.Age=t.Age; 用于FROM子句的子查询：
查询有班级的学生对应的班级名称：
MariaDB > 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 > select StuID as ID,Name,Age,Gender from students union select TID as ID,Name,Age,Gender from teachers; MySQL用户管理：
'Username'@'Hostname'
Username：任意的字符串组合，只能包含基本意义的字符；可以包含"_" "." "-";
Hostname:
FQDN
Domain_name
IP_ADDRESS
可以使用MySQL的专用通配符：%，_
skip_name_resolve={ON|OFF}
创建用户账户：
CREATE USER语句：
CREATE USERuser [> 重命名用户账户：
RENAME USER old_user TO new_user [, old_user TO new_user] ...
删除用户账户：
DROP USER语句：
DROP USER user [, user] ...
用户账户的密码管理：
1.SET PASSWORD语句：
SET PASSWORD = { PASSWORD('cleartext password') | OLD_PASSWORD('cleartext password') | 'encrypted password' }
示例：
MariaDB > set password for 'test'@'%' = PASSWORD('qhdlink'); 2.也可以使用DML语句修改用户账户密码；
示例：
MariaDB > update user set Password=PASSWORD('qhdlink.com') where User='test'; 对于已经建立的用户或未建立的用户进行授权；
GRANT语句：
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON priv_level TO user_specification [, user_specification] ... ssl_option] ...}]
priv_type
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX,> object_type:
TABLE | FUNCTION | PROCEDURE
priv_level:
* | *.* | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_name
*：表示所有的数据库；
*.*：表示所有的数据库中的所有表对象；
db_name.*：表示指定数据库中的所有表对象；
db_name.tbl_name：表示指定数据库中的指定的表对象；
tbl_name：表示当前正在使用的数据库中的指定的表对象；
db_name.routine_name：表示指定数据库中的指定存储函数后存储过程对象；
user_specification:
user [> ssl_option:
SSL | X509 | CIPHER 'cipher' | ISSUER 'issuer' | SUBJECT 'subject'
with_option:
GRANT OPTION | MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count
取消授权/收回授权：
REVOKE语句：
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON priv_level FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
示例：
MariaDB > revoke delete on hellodb.* from 'test'@'%';
MariaDB > revoke all on hellodb.students from 'test'@'%';
MariaDB > revoke select(Age,ClassID) on hellodb.students from 'test'@'%';
注意：在取消已经做出的授权时，REVOKE语句所指定的priv_level部分应该和授权时GRANT语句所指定的priv_level保持绝对一致；否则判定此次取消授权的操作失败；
示例：前提是testdb数据库中包含有tb1和tb2两张表；
MariaDB > grant all on testdb.* to 'test'@'%';
MariaDB > revoke all on testdb.tb2 from 'test'@'%';
ERROR 1147 (42000): There is no such grant defined for user 'test' on host '%' on table 'tb2'
正确的取回授权的方式：
MariaDB > revoke all on testdb.* from 'test'@'%';
MariaDB > grant all on testdb.tb1 to 'test'@'%';
此时，'test'@'%'用户就只有对testdb数据库中tb2表有所有操作权限；
查看用户的授权：
show grants语句；
SHOW GRANTS
练习：
1、在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄；
MariaDB > select Name,Age from students where Age>25 and Gender='M'; 2、以ClassID为标准进行分组，显示每个班级中学生的平均年龄；
MariaDB > select ClassID,avg(Age) from students group by ClassID; 3、显示第2题中平均年龄大于30的分组及平均年龄；
MariaDB > select ClassID,avg(Age) as nosfrom students group by classid having nos>30; 4、显示以L开头的名字的同学的信息；
MariaDB > select * from students where Name like 'L%'; 5、显示TeacherID非空的同学的相关信息；
MariaDB > select * from students where TeacherID is not null; 6、以年龄排序后，显示年龄最大的前10位同学的信息；
MariaDB > select * from students order by Age Desc limit 10; 7、查询年龄大于等于20岁，小于等于25岁的同学的信息；使用你能想到的所有方法实现，×××；
MariaDB > select * from students whereage between 20 and 25;
MariaDB > select * fromstudents where Age>=20 and Age select * from students having Age>=20 and Ageselect ClassId,count(ClassId) from students group by ClassID; 2、以Gender分组，显示男女学员各自的年龄之和；
MariaDB > select Gender,sum(Gender) from students group by Gender; 3、以ClassID分组，显示其平均年龄大于25的班级；
MariaDB > Select ClassId,avg(Age) from students group by ClassID having avg(Age)>25; 4、以Gender分组，显示各组中年龄大于25的学员的年龄之和；
MariaDB > select Gender,Age,sum(Age)from studentswhere Age>25 group by Gender; 多表练习：
1、显示前5位同学的姓名、课程及成绩；
MariaDB > select Name,Course,Score from students,courses,scores where students.StuID=scores.StuID and courses.CourseID=scores.CourseID and students.StuID select Name,Course,Score from students,courses,scores where students.StuID=scores.StuID and courses.CourseID=scores.CourseID and scores.Score>=80; 3、求前8位同学每位同学自己两门课的平均成绩，并按降序排列；
MariaDB > select Name,avg(Score) from students,scores where students.StuID=scores.StuID group by Name order by avg(Score) desc; 4、显示每门课程课程名称及学习了这门课的同学的个数；
MariaDB > select Course,count(Course) from students,scores,courses where students.StuID=scores.StuID and courses.CourseID=scores.CourseID group by Course; 思考：
1、如何显示其年龄大于平均年龄的女同学的名字？
MariaDB > select Name,Age from students where Gender='F' and Age>(select avg(Age) from students where Gender='F'); 2、如何显示其学习的课程为第1、2，4或第7门课的同学的名字？
MariaDB > select Name,s.ClassID,c.CourseID from students as s,coc as c where s.ClassID=c.ClassID and c.CourseID in (1,2,4,7) order by ClassID; 3、如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学？
MariaDB > select s.* from (select s.name,s.age,s.classid,c.avge from students as s,(select count(name) as num,avg(Age) as avge,classid as classid from students group by classid having num >= 3) as c where s.classid=c.classid) as s where s.age>s.avge;
+---------------+-----+---------+---------+
| name | age | classid | avge |
+---------------+-----+---------+---------+
| Shi Potian |22 | 1 | 20.5000 |
| Xie Yanke |53 | 2 | 36.0000 |
| Ding Dian |32 | 4 | 24.7500 |
| Yu Yutong |26 | 3 | 20.2500 |
| Yuan Chengzhi |23 | 6 | 20.7500 |
| Xu Zhu |21 | 1 | 20.5000 |
| Lin Chong |25 | 4 | 24.7500 |
| Hua Rong |23 | 7 | 19.6667 |
| Huang Yueying |22 | 6 | 20.7500 |
+---------------+-----+---------+---------+
9 rows in set (0.00 sec)
4、统计各班级中年龄大于全校同学平均年龄的同学。
MariaDB > select * from students group by ClassID having Age>(select avg(Age) from students);
页:
[1]