必备MYSQL语法--映射

1获取指定列（基础）

select id ,name from info;      　　  #info :数据表，=》取出指定列的数据
select id ,name as NM from info;     #取出id，名字将列名命名为NM 
select id ,name as NM ,132 from info #取出id，名字将列命名为NM，增加123列并以123命名；

2.获取指定列（嵌套查询）

select 
    id,
    name,
    666 as num,
    (select max(id) from depart) as mid,#取出depart表中的id最大值，以mid为列名增加最大值列
    (select min(id) from depart) as nid,#这两个只能去除一个值作为定量去查询
    age
from info;

3.获取指定列（条件查询）：

select
    id,
    name,
    666 as num,
    (select  title from depart where depart.id = info.depar) as x1 
from info;   

注：需要将两张表中的每条数据进行对比取出符合条件的id，然后查询title列，将其放入x1中。
　　查询效率相对比较低，如果数据量过大尽量不要使用。

4.获取指定列（多条件查询）

select
    id,
    name,
    case depart_id when 1 then "第一部门" end v1，
    case depart_id when 1 then "第一部门" else "其他" end v2，
    case depart_id when 1 then "第一部门" when 2 then "第二部门" else "其他" end v2，
　  case when age<18 then "少年" end v4
　　 case when age<18 then "少年" else "油腻男" end v5,
　　 case when age<18 then "少年" when age<30 then "青年" else "油腻男" and v6
from info；

5.排序

selesct * from info order by age desc;#取出info中所有的数据通过age进行倒序排列
select * from info order by age asc;

注：order by:排序
　　desc：倒序排列=>从大到小
　　asc： 顺序排列=>从小到大

select * from info order by age asc , id desc;
select * from info where id >10 order by age asc ,id desc;#取出info中age大于10的数据然后先通过age进行顺序排列，相同的通过id进行倒序排列
select * from info where id >6 order or name like "%y" order by age asc ,id desc;
注：优先级：从左往右

6.取部分数据：

select * from info limit 5; #取出前五条数据
select * from info order by id desc limit 3; #先进行倒序排列然后取出前五条数据
select * from info where id > 10 order by id desc limit 3; 先取出id大于10 的数据然后进行倒序排列取出前三条

select * from info limit 3 offset 2;#从第二个位置取三条数据
注：limit：用于取出范围，offset：取值的起始位置

 7.分组：

select age ,name from info group by age;(合并后只留下一行)
select age,max(id),min(id),count(id) from info group by age;#合并相同age的行，取出最大id作为一列，取出最小id作为一列，取出id的和作为一列，取出avg（平均值）作为一列=>都是基于合并行的
select * from info where id in (select max(id) from info group by age);

select age,count(id) from info group by age having count(id)>2; #通过id分组，取出相同id数量和作为一列，然后取出和大于2的列。
select age,count(id) from info where id>4 group by age having count(id)>2;

avg：平均值
group by：分组
having：筛选（与group by 搭配使用）

SOL的执行顺序：
where > group by > habing >order by > limit

8.左右连表：

 

主表 left outer join 从表 on 主表.xxx = 从表.id

select * from info left outer join depart on info.depar_id = depart.id; #将info表的所有列通过depar_id与从表的id进行对应，将两张表数据对应。
select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id; 

左连表：主表 left outer join 从表 on 主表.XX = 从表.id
右连表：从表 ringht outer join 主表 on 主表。xx = 从表.id
内连接： 表 inner join 表 on 条件（没有主次之分，忽略掉没有关联部分）
案例：select * from info inner join depart on info.depart_id = depart.id;

简化写法：select * from depart left join info on ....

9.表关系：

　　（1）一对多约束

#创建两张表（一对多id）
create table depart（
    id int not null auto_increment primary key,
    title varchar(16) not null
)default charset =utf8;

#创建关联表
creat table info(
    id int not null auto_increment primary key,
    name varchar(16) not null,
    email varchar(32) not null,
    age int,
    depart_id int not null,
    #增加外键约束
    constraint fk_info_depart foreign key (depart_id) references depart(id)
)default charset=utf8;

#表结构已经创建好，增加外键
alter table info add constraint fk_info_depart foreign key info(depart_id) references depart(id);

#删除外键：
alter table info drop foreign key fk_info_depart;

 (2)多对多外键约束

create table boy（
    id int not null auto_increment primary key,
    name varchar(16) not null
）default charset=utf8;

create table girl(
    id int not null auto_increment primary key,
    name varchar(16) not null
)default charset=utf8;

#关联表
create table boy_girl(
    id int not null auto_increment primary key,
    boy_id int not null,
    gitl_id int not null,
    constraint fk_boy_girl_boy foreign key boy_girl(boy_id) references boy(id),
    constraint fk_boy_girl_girl foreign key boy_girl(girl_id) references girl(id)
)default charset=utf8;

 10.权限管理：

（1）创建用户：

create user '用户名@连接者的ip地址' identified by “密码”；

（2）修改用户：

rename user "用户名@Ip地址"  to “新用户名@ip地址”；

（3）修改密码

set password for “用户名@ip地址”  = password（新密码）；

11.授权管理：

#授权
grant 权限 on 数据库.表 to “用户‘@’ip地址”

grant all privileges on * to "lh@localhost";  --用户lh拥有所有数据库的所有权限
grant all privileges on day26.* to "lh@localhost";--用户lh拥有数据库day26的所有权限
grant all privileges on day26.info to "lh@localhost";--用户lh拥有数据库day26的info表的所有权限



grant select  on day26.info to "lh@localhost";--用户lh拥有day26的查询权限
grant select,insert  on day26.* to "lh@localhost";--用户lh拥有数据day26的查询和插入权限