mysql 基础2

 

回顾
  1 关系型数据
    Mysql Oracle DB2 (IBM)
    SqlServer(MS Sever) 
 
  2  Mysql -u -p
     exit
  show databases
  use ___
  show tables
  desc table_name
  source .sql ---> 导入数据库中
 
     SQL语句  作用查询数据库 
 
  语法关键字  select from where order by
 
      select column_name,column_name *
   from table_name
   where
   order by   asc  desc
    
  查询关键字
    1 = >  < <> >= <=
    2 and or not
    3 [not] between xx and xxx
    4 [not] in
    5 like   %  _ 
    6 is null  is not null
           
    Mysql 函数
    1 字符串相关函数
          length('')
    char_length();
    upper();
    lower();
    left();
    rigth();
    lpad();
    rpad();
    instr();
    replace();
    repeat();
    trim();
    substring();
    concat();
    reverse();

        2 数字函数
      floor(123.456) ---> 截取小数点
   123
   
   select floor(123.456);
   
      round(123.4) ----> 四舍五入
     
   select round(123.6);
   
   select round(123.456,2);
   特点： 保留小数点后2位，并进行四舍五入
   
   select round(153.456,-2);
   特点： 剔除小数点前的2位，并进行四舍五入
   
      truncate() ---- 截取小数
   
   select truncate(12.234,1);
      参数1 任意小数
            参数2 小数点后保留位数  

            特例 select truncate(12,2); 
                 12.00   
   
  
        3日期函数
                2011-10-19 10:05
  NOW()  返回当前的日期和时间
  select now();
  
  Current(当前) 缩写 CUR
  
  CURDATE()  返回当前的日期
  select curdate();
  
  CURTIME()  返回当前的时间
  select curtime();
  
  DATE(时间)  提取是 时间 的日期
  TIME(时间) 提取是 时间 的时分秒
  
     select date(now());
  select time(now());
  select date('2001-10-11 10:30');
  select time('2009-10-11 10:31');
  
  Year()  提取对应时间 的年份
  Month() 提取对应时间 的月份
  
  select year(now());DA
  select month(now());
  
  select month('2011-10-11 13:45');
  
  
  select extract(HOUR_MINUTE from now());
  
 
  EXTRACT(字段 From 日期)  返回日期/时间按的单独部分
    字段的合法值：
       MICROSECOND 
       SECOND
       MINUTE
       HOUR
       DAY
       WEEK
       MONTH
       QUARTER
       YEAR
       SECOND_MICROSECOND
       MINUTE_MICROSECOND
       MINUTE_SECOND
       HOUR_MICROSECOND
       HOUR_SECOND
       HOUR_MINUTE
       DAY_MICROSECOND
       DAY_SECOND
       DAY_MINUTE
       DAY_HOUR
       YEAR_MONTH
  DATE_ADD(日期, INTERVAL 数量 字段)  
    select date_add(now(), interval 10 day);
  
  
  DATE_SUB(日期, INTERVAL 数量 字段)
    select date_sub(now(), interval 8 day);
  
  select datediff(str_to_date("2010-1-1",'%Y-%m-%d'),str_to_date("2013-8-8",'%Y-%m-%d'));   2010-1-1 减去 2013-8-8的天数
  
  
  
  DATEDIFF(日期1, 日期2)  日期的间隔差距  日期1-日期2=差距

  select datediff(now(),now());
  
  DATE_FORMAT(日期, 格式)
    格式字符：
        %a 缩写星期名
        %b 缩写月名
        %c 月，数值
        %D 带有英文前缀的月中的天
        %d 月的天，数值(00-31)
        %e 月的天，数值(0-31)
        %f 微秒
        %H 小时 (00-23)
        %h 小时 (01-12)
        %I 小时 (01-12)
        %i 分钟，数值(00-59)
        %j 年的天 (001-366)
        %k 小时 (0-23)
        %l 小时 (1-12)
        %M 月名
        %m 月，数值(01-12)
        %p AM 或 PM
        %r 时间，12-小时（hh:mm:ss AM 或 PM）
        %S 秒(00-59)
        %s 秒(00-59)
        %T 时间, 24-小时 (hh:mm:ss)
        %U 周 (00-53) 星期日是一周的第一天
        %u 周 (00-53) 星期一是一周的第一天
        %V 周 (01-53) 星期日是一周的第一天，与 %X 使用
        %v 周 (01-53) 星期一是一周的第一天，与 %x 使用
        %W 星期名
        %w 周的天 （0=星期日, 6=星期六）
        %X 年，其中的星期日是周的第一天，4 位，与 %V 使用
        %x 年，其中的星期一是周的第一天，4 位，与 %v 使用
        %Y 年，4 位
        %y 年，2 位
        
  SimpleDateFormat
        String --- Date
        Date ---- String
  new SimpleDateFormate('yyyy-MM-dd');
        sdf.format(date) --- String
  sdf.parse(string) --- date
  
  
  select now();
  
  select date_format(now(),'%y-%m-%d %h %p');
  
  STR_TO_DATE(字符串, 格式)
  
  数字  10
  字符串 ''
  字符串 ---- 日期类型的数据
  
  str_to_date('2010-10-11','%Y-%m-%d');
  
  select date_add(str_to_date('2010-10-11','%Y-%m-%d'),interval 10 day);
  
  
  select datediff(now(),str_to_date('2011-10-01','%Y-%m-%d'));
  
  
  LAST_DAY(日期) 获得月份所在月的最后一天

        select last_day(now());
  
  select last_day(str_to_date('2011-02-10','%Y-%m-%d'));
  
-------------------------------------------------------------  
        查询 employees 每个月倒数第三天入职的员工信息
        31 -- 29   2
  30 -- 28   2
  28 -- 26   2
  29 -- 27   2

  last_day -- hire_date = 2
  datediff(last_day-hire_date) = 2
  
  select *
  from employees
  where datediff(hire_date,last_day(hire_date)) =16;

  select datediff(str_to_date("2010-1-1",'%Y-%m-%d'),
                str_to_date("2013-8-8",'%Y-%m-%d'));  
                2010-1-1 减去 2013-8-8的天数
  
---------------------------------------------------------
别名  alias
   列别名
   select first_name as name ,salary*12 as anuSalary
   from employees ;
  
   表别名
    select *
 from employees as emp;
 
 select emp.*
 from employees as emp;
  
--------------------------------------------
Mysql ifnull()
Oracle nvl()

   思考
      10000  0.4
   10000 + 10000*0.4 == 14000

     算出所有雇员的总月薪
 
  null值 在sql 语句中是不能够进行算术运算
 
  select salary+(salary)*ifnull(commission_pct,0)
  from employees ;
 
     ifnull(参数1,参数2)
  如果参数1 不是null 返回参数1的数据
  如果参数1 是null   返回参数2的数据
 
     select ifnull('suns','sunshuai');
     select ifnull(null,'sunshuai');

-------------------------------------------
  聚合函数
     avg() ---> 计算某一列的平局值
  sum() ---> 计算某一列的数值和
  max() ---> 计算某一列的最大值
  min() ---> 计算某一列的最小值
  count() ----> 计算数量(数据条数)

     查询所有employees 表中 salary 的平局值
 
  select min(salary) as Sal
  from employees;
 
 
  select count(*)
  from employees;
 
  a)*  计算整个表中的行数
    
     select count(commission_pct)
     from employees ; 
 
  b)列名 计算当前列中非空数据的条数
  c)count(distinct 列名) 去除当前列重复数据
    并计算数据条数
   
     select count(distinct first_name)
  from employees ;
  
  
  distinct 作用 去出查询的重复数据
  
  select distinct first_name
  from employees
  where first_name = 'Steven';
 
----------------------------------------- 
聚合函数
   avg() sum() max() min() count() 

   select avg(salary)
   from employees ;
  
   查询 每个部门中的 员工平局工资

   select count(distinct department_id)
   from employees;
  
   分组 group by  分组条件
  
   select department_id,avg(salary)
   from employees
   group by department_id;
  
  
   查询每个部门中的最大工资
  
   select department_id,max(salary)
   from employees
   group by department_id;
  
   group by 注意：1 只有出现在group by 语句后的列名字才可
                    以出现在 select语句块中 
         2 如果有一列没有出现在group by
                    只有书写聚合函数中才可书写这样的列      
  
   ------------------------------------------
   having 条件查询
   
   查询 20 ，30 平局工资
   select department_id ,avg(salary)
   from employees
   where department_id in (20,30)
   group by department_id; 20 30
  
   select department_id,avg(salary)
   from employees
   group by department_id (11)
   having department_id in (20,30);
  
   having where 区别
   1 having where 执行相同sql时 执行效率不同
     where执行效率高

   原因：  sql语句执行顺序不同
        1 sql语句 书写顺序
          select from where group by having order by    
     2 sql语句执行顺序
     from --> where--->group by---> having---> order by
  
  
    having 真正的意图
 
 查询 平局工资 大于 3000
 这些部门的平局工资 和 部门编号
 
 select department_id,avg(salary)
 from employees
 group by department_id
 having avg(salary) > 6000;
 
 
 having 查询中应用了聚合函数的条件判断
        那么使用having
 
 sql 执行顺序
 from ---> where ---> group by ---having---order by
 
  结论：
     where 应用的条件判断 是没有聚合函数使用的
  having 应用的条件判断 是有聚合函数使用的
 
--------------------------------------
分页查询   limit 限制
    select *          起始条数 共显示几条
 from employees limit 0,     5  第一页
                         5,     5  第二页
       10,    5
  
-------------------------------------
case when 语句
  case
      when xxx then xxx
      when xxx then xxx
注意 中间没有逗号，  
  查询员工的信息,当员工的工资 大于 10000 时 显示 high
                              小于 10000         low 
  select first_name,salary,case
                             when salary >= 10000  then 'high'
                      when salary <  10000  then 'low'
                           end
  from employees;
 
  case
    when salary >1000  then 'high'
 when salary <1000  then 'low'
  end; 
 
  select name,case
  when sal>2000 then 'high'
  when sal<=2000 then 'low'
 end
from account;
------------------------------------
回顾
  1
   sql select
       from
    where
    group by
    having
    order by
 1 语法关键字 书写顺序 含义
    2             执行顺序
   
  2 查询关键字
  3 函数
      数字
   字符
   日期
  
   ifnull
  
   聚合函数
  
  4 case when    
 
---------------------------------------
子查询
   概念 ：  在一条sql语句中 嵌套另外的sql 查询语句

   where 子查询
  
   a)单值where子查询
  
   查询 工资 大于 平局工资的 这些员工的信息
  
   select avg(salary)
   from employees;
  
   select *
   from employees
   where salary > (select avg(salary)
                   from employees
       );
  
  
   查询 工资 大于 最大工资
   这些员工的信息
  
   select max(salary)
   from employees
  
   select *
   from employees
   where salay > (select max(salary)
                  from employees);
  
  
  
  
   查询 工资 大于 first_name Lex
   这些员工的信息
  
   select salary
   from employees
   where first_name = 'Lex';
  
   select *
   from employees
   where salary > (select salary
                   from employees
                   where first_name = 'Lex');
  
   b) 多值where子查询
  
     查询 工资 为 最高工资 ，
  工资为最低工资
  这些员工的信息
 
  select *
  from employees
  where salary in ((select max(salary)
                   from employees),
       (select min(salary)
       from employees ));
  
       
  

   from  子查询  
    
    select *
    from employees;
 
 
 select last_name,first_name,salary
 from employees ;

 select temp.last_name
    from  (select last_name,first_name,salary
           from employees) as temp 
    where temp.first_name = 'Steven';    
  
   
 from 子查询  进行预处理
 
 
----------------------------------------
多表联合查询
  创建数据库 hr_m
 
 
  分门别类的存储在多张表中
 
  问题
   1 查询 雇员的信息 同时 显示
     雇员所在部门的名字 和 位置 
 
 
  4种多表联合操作

  1 内连接
      select e.ename,d.dname,d.loc
   from emp as e,dept as d
   where e.deptno = d.deptno ;
 
    SQL 99  
   inner join 
  
   select e.ename,d.dname,d.loc
   from emp as e
      inner join dept as d   
      on e.deptno = d.deptno ;
  
  2 左外连接
      左外连接关键字
   左边这张表的数据全部查询
  
      查询所有雇员信息，部门名，位置
  
   select e.ename,d.dname,d.loc
   from emp as e
   left outer join dept as d
   on e.deptno = d.deptno ;
  
  
  3 右外连接
    查询雇员信息 ，以及所有的部门信息
 
    select e.ename,d.dname,d.loc
 from emp as e
 right outer join dept as d
 on e.deptno = d.deptno ;
 
 
    select e.ename,d.dname,d.loc
 from dept as d
 left outer join emp as e
 on d.deptno = e.deptno ;
 
 
  4 全连接 Mysql 不支持
     概念 1 连接所有能够连接的数据
       2 并且把2张表中不能连接的数据查询出来
   
       select e.ename,d.dname,d.loc
    from emp as e
    full join dept as d
    on e.deptno = d.deptno;
 
   --------------------------------------
   查询  每个部门中 工资最高的员工信息
  
   select deptno, max(sal)
   from emp
   group by deptno ;
  
  
   select *
   from emp    
   where sal in (select max(sal)
                 from emp
     group by deptno);

 

    deptno  max
  10     5000
  20     3000
  30     2850
 
  ename    salary deptno
   SMITH    300    20     20  3000
   SUNS     3000   20     20  3000
   SUNS1    100    10      10  5000
   SUNS2    5000   10      10  5000
   SUNS3    500    30      30   2850
   SUNS4    2850   30      30   2850
 

      select e.*
      from emp as e
      inner join (select deptno, max(sal) as maxSal
                  from emp
                  group by deptno) as temp  
   on e.deptno = temp.deptno    
      where e.sal = temp.maxSal;