my_mysql
###一键偷懒YUM安装MySQbL###
1.安装mysql数据库
#yum install -y mariadb-server mariadb
2.登录mysql数据库常用选项
-h：指定服务端主机地址
-u: 指定登入的用户名
-p：指明密码
-D：指明登入的数据库
-e：连接之后直接运行SQL语句，不进入交互式模式（可以在脚本中使用）
3.mysql语言分为3大类
DDL：数据库定义语言 create , alter , drop
DML：数据库操作语言 insert , delete , update , select
DCL：数据库控制语言 grant , revoke
4.数据库DDL语言
创建库 create database zxxsql;
删除库 drop database zxxsql;
修改库 alter database zxxsql character set = utf8
创建表 create table home (id int not null primary key auto_increment, name varchar(250) not null, class varchar(250) not null);
查看表结构 desc home;
修改表
添加字段：alter table home add gender enum('f','m');
删除字段：alter table home drop gender;
修改字段：
alter table home change name username varchar(100) after id;
alter table home modify username varchar(100) first;
删除表：drop table home;
5.数据库DML语言
<1>insert 在home表中插入3组class和username数据。
insert into home (class,username) values ('ops', '运维开发'), ('opsdev', '运维开发'), ('开发', 'java开发');
<2>update 修改home表数据
update home set class = '开发部门' where id = 1;
<3>delete 删除表数据
delete from home where class = '开发';
<4>select
查询表上的所有的数据 select * from home;
查询部分数据 select id,class from home;
# 还可以取个别名 select id as num,class from home;
使用where子句过滤
# 可以使用的算数运算符：>, < , >=, <=, ==, !=
# 可以使用连接词：and , or
select * from home where id >= 2;
select * from home where id <= 2 and id >1;
select * from home where id between 1 and 2;
# 可以使用like做模糊匹配（%：表示任意长度的字符，_：表示任意单个字符）
select * from home where class like 'ops%';
# 可以使用null对值进行判断
select * from home where id is not null;
select * from home where id is null;
使用order指定排序（默认是asc,升序排列）
select * from home order by id desc;
6.DCL数据库语言
<1>grant
#先创建用户，再授权
create user zxx@'172.16.19.%' identified by '123456';
grant all on *.* to zxx@'172.16.19.%';
flush privileges;
#创建用户的同时给用户授权
grant all on *.* to zxx@'172.16.19.%' identified by '123456';
flush privileges;
#给用户授予某些权限
show grants for zxx@'172.16.19.%';
<2>查看用户权限
show grants for zxx@'172.16.19.%';
<3>删除用户
delete from mysql.user where user = "zxx";
flush privileges;
7.select 查询语句详解
-1：where子句：指明过滤条件
可以使用的算数运算符：+， -， * ，/ ，= ，!= ，<=， >=
between 较小的数 and 较大的数
in (较小的数，较大的数)
is null 或 is not null
like模糊匹配
例如：
select * from home where id >= 2;
select * from home where id <= 2 and id >1;
select * from home where id between 1 and 2;
select * from home where id in (1,2); # 从1和2中取值
select * from home where class like 'ops%';
select * from home where id is not null;
select * from home where id is null;
-2：group by 子句：根据指定的查询条件将查询结构进行分组，用于做聚合运算
使用聚合函数：avg( ) , max( ) , min( ) , count( ), sum( )
select age,gender from students group by gender;
select avg(age),gender from students group by gender;
select min(age), gender from students group by gender;
select max(age), gender from students group by gender;
select count(id), gender from students group by gender;
-3：having子句：将分组之后的结果再次过滤
select avg(age) as 'average_age', gender from students group by gender having average_age > 50;
-4：order by子句 ：根据指定的字段对查询结果进行排序，默认为升序，降序使用关键字desc
select name,age from students order by age desc;
-5：limit 子句：对查询的结果进行输出行数的限制
select name,age from students order by age desc limit 8; # 选前8行
select name,age from students order by age limit 4, 2; # 前4个不选，从第五行开始选2行
###总结单表查询次序##########################################################################################################################
子句的书写顺序：
where -> group by -> having -> order by -> limit
例如：
select *,avg(score) as '各班平均成绩' from students where id > 1 group by class having avg(score) > 55 order by score desc limit 3 ; #
#############################################################################################################
