mysql数据管理系统（3）

##字段查询
select * from students;
select  s_name as sn(别名) from students;
select s.s_name as sn from students as s(别名);

#模糊查询  like
select * from students   where  s_name like '%o%';
#指定条件查询
select * from mins where age>18 and sex = '男’;

#内连接  inner join
select * from department inner join students
select d_id,d_name from department , students
#内连接条件查询  
select * from department as d inner join students as s
  on d.d_id = s.dept_id

#左连接 小表放左边作主表
select d.d_id,d.d_name,s.s_name from department as d left join 
students as s on d.d_id =s.dept_id
#右连接                                                                                    right
 #全连接  去重
select * from department as d left join students as s on d.d_id=
s.dept_id
union
select * from department as d right join students as s in d.d_id=
s.dept_id
  
#子表查询
select * from department left join(
select  dept_id,s_name,s_age,s_sex  from students left join
stu_detail on students.s_id =stu_detail.stu_id) as e on e.dept
_id =department.d_id
#子表条件查询
select * from students where dept_id =(
   select d_id from department where s_name='java');
 
#排序
select * from mins order by age desc;
                                                          asc
#select * from mins order by age dsec limit 4 
                                                                            1,4
#分组
select d_id,d_name,count(*) from students
      inner join department on 
      students.dept_id = department.d_id
      group by d_id,d_name ;
                                                having count(*)=2

#处理空值
select s_name.ifnull(dept_id,1) from students
#左截取
select s_name,left(s_name,2) from students;
                          right
                         substring
#添加索引
alter table mins add index(age)

##mysql 函数
#最大值
select max(age) from mins;
           min 
           avg
       
#视图
create viem stu_det as select s.s_name, d.d_id from department
as d left join students as s on s.dept_id = d.d_id left  join stu_de
tail as stu on s.s_id = stu.stu.id;