MySQL基础知识

常用命令
-------------------------------------------------------------------------------
   简单查询    
     select 字段名  from 表名
     其中要注意：
               select和from都是关键字。
               字段名和表名都是标识符。
          强调：
               对于SQL语句来说，都是通用的，
               所有的SQL语句以“;”结尾，
               另外SQL语句不区分大小写,都行。
例如： select   name    from   stu；
---------------------------------------------------------------------- 
  查询多个字段使用”,“号隔开
       select  name , age  from  stu;
  查询所有字段使用“*”号隔开,或者把所有的字段名全部写上
例如：    select * from stu
     注意：
          1. 这种方式效率低，可读性差
          2. 在DOS命令窗口想快速查看一下可以使用
-----------------------------------------------------------------------
 给查询的列起别名
   select name as n from stu;       在stu表中查询name属性并起别名为n
         使用as关键字起别名；
         注意：这里只是将查询的结果列名显示为n，并不会修改原列名
         记住：select 语句是永远不会进行修改操作的，只负责查询
         as关键字是可以省略的
         例如   select name n from stu;
   注意:  如果在起别名的时候，别名有空格出现这是不被允许的，会报错，
            除非将别名使用单引号括起来，如：'name  a' 或者使用双引号，           
    强调：
         在所有的数据库当中,所有的字符串使用单引号""括起来，单引号是标准，
         双引号不是标准双引号在mysql数据库中可以使用，但是在其他地方可能会报错。
 ------------------------------------------------------------------------------
在查询语句当中字段可以使用数学表达式
  如： select  much*12 from stu;   将much乘以12的结果在stu表中查询出来
         select sal*12 as '年薪' from stu； 年薪乘以12的结果查询出来
----------------------------------------------------------------------------------
======================================================
条件查询
      注意： 查询符合条件的结果查询出来不是所有！
      语法格式：
           select 
                 字段1，字段2...........
                from
                     表名
                      wher
                           条件;
=  等于
       如：  select sal  from  stu  where  sal=500;
<> 或 !=  不等于
   如：  select sal  from  stu  where  sal!=500;
           select sal,name  from  stu  where  sal<>500;
<
<=
>
>= 
       以上用法和上述一样！！！！！！！！！！！！！！
----------------------------------------------------------------------------------------------
between....and.....   两个值之间等同于 >= and <=
       如: 查询在300到500之间的sal , 并起别名为s，把符合条件的sal，以及对应的sb打印出来
            select  sb, sal  as  s  from  emp  where  between  300  and  500;
         注意:   使用between....and....时，必须遵循左小右大的原则，是闭区间
--------------------------------------------------------------------------------------------------
is null  为null   is not null   不为空
--------------------------------------------------------------------------------------------
and  并且
or 或者
      当and和or 同时出现时，and的优先级更高，想让or执行需添加小括号
 如： where sal>2500 and (id=3 or id=2);
in 包括， not in 不包括  (相当于多个or)
      如 ：作用相同
       select name,id from stu where id=1 or id=2;
       select name,id from stu where in(1,2); 

not   可以取非 ,主要用在 is 或 in 当中
            is null
            is not null
            in
            not in
 like   称模糊查询，支持%或下划线_匹配
            %匹配任意个字符
            下划线 _ ，一个下划线只匹配一个字符 
          (%是一个特殊的符号，_ 也是一个特殊的符号)
    如：     包含有o的  : select name from stu where name like '%o%';
               包含下划线的的：select name from stu where name like '%\_%';（转义字符\_）
               以a结尾的  : select name from stu where name like '%a';
               以s开头的  :   select name from stu where name like 'a%';
               第二个字母是f的：select name from stu where name like '_f%';
               第三个字母是f的：select name from stu where name like '__f%';
---------------------------------------------------------------------------------------------------
      排序： 降序 升序       
            select name from stu order by  desc;                             
                -- asc 升序      asc 默认升序 
         指定升序，降序
              select name,id from stu order by id desc   #指定降序
      多个字段排序
              select name,id from emp order by sal asc,name asc;
      // 根据工资sal进行升序输出，如果sal相等则按照name升序输出
     根据字段的位置进行排序输出
            select * from emp order by 2;   
      // 如果表中第二列式是id,则按照id降序输出，不建议这么写，因为随着数据的删除和修改，位置会变

-------------------------------------------------------------------------------------------------------------
单行函数(数据处理函数)
       单行处理函数： 一个输入对应一个输出
     lower 转换小写 
          select lower(name) from emp;   //将name以小写的形式输出
     upper 转换大写
          select upper(name) from emp
     sunstr 取子串  （substr(被截取的字符串，起始下标，被截取的长度) ）
         select lower(name,1,1) as n from emp
                 注意从1下标开始，没有0
     concat  进行字符串的拼接
          select concat( name,age) from emp;
     length 取长度
         select length(name) let from emp;  获取emp表中每个name的长度
     trim  去空格
            select  * name from emp where name = ('      Tom');  去除字段里面的空格
     str_to_date 将字符串转换为日期
                    
     date_format  格式化日期
     format  设置千分位
     round 四舍五入
              select 字段/字面值 from emp；
              select name from emp;  注意如果name不是数值型会报错！！！！！1054
              select 1000 a from emp;  --  1000
              select round(1236.567 , 0) a from emp;  --  1237
              select round(1236.567 , 1) a from emp;  --  1236.6  保留一个小数
              select round(1236.567 , -1) a from emp;  --  1240    保留到十位
              select round(1236.567 , -2) a from emp;  --  1200    保留到百位
     rand()  随机数
           select round(rand()*100,0) from emp;  随机得到100以内的数，使用round可以使得到数值是一个整数，否则将会是位数特别多的小数
     ifnull  可以将null转换成一个具体的值
        注意： 在数据库当中，只要有null参与的数学运算，最终结果就是null;   例如 sal + null  结果就是 null，要进行相应处理
           select sal+buzhu from emp;  结果是null 因为buzhu是null  所以结果是null    
           select sal+innull(buzhu , 0) from emp;   如果补助为null，则补助值为0，得出结果 sal+0
    case......when...then.....when....then.....else....end....
             当员工的工作岗位job是部门组长时，工资上调10%，当工作岗位是经理时，工资上调50%，其他正常
              select (case job when '部门组长' then sal*1.1 when '经理' then sal*1.5 else sal end)  as newsal from emp;
--------------------------------------------------------------------------------------------------
分组函数( 多行处理函数： 输入多行最终输出一行) ：
       分组函数必须在使用的时候必须先进行分组，才能用，如果没有对数据进行分组，默认为一组！！！！！！！！！！！！！！
             count   计数
             sum     求和
             avg      平均值
             max     最大值
             min     最小值
      select avg(sal)  from emp;    --  全部工资相加后的平均工资  
      select max(sal)  from emp;   -- 最高工资
      select min(sal)  from emp;   -- 最低工资
      select max(sal)  from emp;   -- 最高工资
      select count(name)  from emp;   -- 求员工数量，有多少个名字就有多少个人
分组函数使用的时候注意一下几点：
     1.分组函数自动忽略null, 不再需要对null进行处理
     2.分组函数中 count(*) 和count(字段) 的区别
       select count(*)  from emp;     统计该字段下所有不为null的元素的总数
       select count(sal)  from emp;  统计表中的总行数，只要有一行数据count就++，因为每一行记录都不可能为null,只要有一列不为null那就是有效的数据
     3.分组函数不能出现在where字句当中
                会报错 #1111 
     4.所有的分组函数都可以组合一起使用
        select sum(sal),min(sal),max(sal) from emp;
分组查询
        select
                ......
        from
                ........
        where
                ...........
        group by
                .......... 
        order by
                .........    
       以上的关键字顺序不能乱，需要记忆
             执行顺序是     1.from
                                  2.where
                                  3.group by
                                  4.select
                                  5.order by
         为什么分组函数不能再where使用？
          select * from emp where sal > min(sal); 会报错！！
              因为分组函数必须先分组才能使用，
              where执行的时候，还没有分组，因此在where后面使用会报错；

   例子1： 找出每个工作岗位的工资和
         思路： 对工作岗位进行分组分组，对工资求和
               select job,sum(sal) from emp group by job;
            注意： 一下语句在mysql可以使用，oracle中报错；语句毫无意义
                  select name,job,sum(sal) from emp group by job;
              结论！！！！！！！！：
               一条select 语句当中，如果有group by语句的话，
               select 后面只能跟：参加的分组的字段，以及分组函数，
               其他的一律不跟
   例子2： 找出每个部门，不同岗位的最高工资
           技巧： 可以把两个字段当成一个字段看( 两个字段联合分组 )
               select '部门', '岗位'，max(sal) from emp group by '部门', '岗位';
   例子3 ：找出每个部门最高薪资，显示薪资大于3000的
            select '部门'，max( sal ) from emp where sal >3000 group by '部门';  # 思路优先排除薪资低于3000，先试用where过滤
            select '部门'，max( sal ) from emp group by '部门' having sal>3000;   -- 使用having子句
              注意：优先选择where，实在没办法，再使用having,因为效率较低！！！
  例子4： where没有办法的？
             找出每个部门的平均薪资，显示薪资高于2500的！
                 select '部门'，max( sal ) from emp where avg(sal) >2500 group by '部门';
                             -- 语法错误，无法在where后使用分组函数
                 select '部门'，max( sal ) from emp group by '部门' having avg(sal)>2500; 
-----------------------------------------------------------------------------------
  大总结：单表的查询
        select
                ......
        from
                ........
        where
                ...........
        group by
                .......... 
        having
                ...........
        order by
                .........  
以上执行顺序不能颠倒  1.from                    从张表中查询数据，
                                  2.where                  先经过where进行过滤
                                  3.group by              对这些有价值的数据进行分组
                                  4.having                 分组之后可以使用having继续过滤
                                  5.select                   select 查询出来 
                                  6.order by               最后排序输出  ！
             
---------------------------------------------------------------------------------------------------               

查询结果去除重复记录 distinct
   注意：  原表数据不会修改，只是查询结果去重
           select distinct job  from emp;  -- 查询的结果,job不会有重复，
           select distinct job,name from emp;  # 表示两个字段联合起来去重
           select job,distinct name from emp;  -- 语法错误，distinct只能出现在所有字段最前面
       例如：  统计所有岗位的数量
                  select count( distinct job ) from emp; 
连接查询
       从一张表单独查询叫单标查询；
       emp表和dept表联合起来查询数据，emp表取员工名字，dept表取部门名字。叫连接查询
  连接查询的分类：
          根据语法分类
              SQL92： 1992年出现的语法
              SQL99： 1999年出现的语法
           根据表连接的方式 ：
                  内连接:  等值连接、 非等值连接、 自连接
                  外连接：左外连接(左连接)、右外连接(右连接)
                  全连接。 
      当两张表联合起来查询，没有设置任何条件设 置时会发生笛卡尔积的现象，即两张表行数的乘积 如 10*10
      为避免笛卡尔积现象，连接时应对其设置条件
            select ename,dname from emp,dept where emp.deptno = dept.deptno;
            select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;  -- 起表名很重要，效率问题，SQL92语法
          思考：虽然两条语句都避免了类笛卡尔积的现象，但是匹配的次数还是两张表行数的乘积
          注意：因此得出，表连接的次数越多，效率越低，因此应当尽量避免表的连接次数
  内连接----》等值连接：
           例子1：  查询每个员工所在部门名称，显示员工名和部门名
              emp e 和 dept d表进行连接，条件是e.deptno = d.deptno
    SQL92的语法：
              select e.enmae,d.name from emp e, dept d where e.deptno = d.deptno;
           缺点，结构不清晰，连接条件和后期的筛选条件放在where 后面
    SQL99的语法
              select
                  e.name,d,name 
              from
                  emp e
              join 
                  dept d
              on 
                  e.deptno = d.deptno;
      优点，结构清晰，表的连接时独立的，连接之后，如果需要进一步筛选，在后面继续添加          
              

----------------------------------------------------------------------------------------------------------------
执行SOL语句脚本的命令：
                   mysql> couroe D:\Desk \App \ vip.sql;
            
约束（constraint）
      在创建表的时候，给表中的字段加上一些约束，来保证这个表中的数据的完整性，有效性 ！！！
     约束的作用：就是保证表中数据有效 !
           约束包括：
                    非空约束        not null
                    唯一性约束     unique
                    主键约束        primary key  (简称PK)
                    外检约束        foreign key   (简称FK)
                    检查约束        (oracle支持)

主键 primary  key
     主键约束： 一种约束
     主键字段： 该字段上添加了主键约束
     主键值：外键字段当中的每一个值
 主键是每一行记录的身份证号
  主键是每一行记录的身份证号
记住 ：任何一张表都应该有主键，没有主键，表无效！
主键的特征 ： not null + unique,(主键值不能为null且不能重复)，主键约束一张表只能有一个。
不建议使用：varchar当主键，一般使用数字，一般都是定长的。
主键除了单一，复合主键还有自然和业务这种分类 ：
       自然主键： 主键值是一个自然数，和业务没有关系。
       业务主键： 主键和业务紧密关联，例如拿银行卡号做主键值。
    实际当中使用自然主键多，因为使用业务主键时当业务发生变动时，可能会影响到主键值，因此尽量使用自然主键。
       create table stu(
                id int primary key,  
                --列级约束，一个字段做主键，叫做： 单一主键
                name varchar(20)
               );
       create table stu(
                id int ,
                class varchar(20),         
                name varchar(20)，
                primary key(id) 
              #表级约束,多出现在给多个字段联合起来添加约束时，多个字段联合做主键称之为复合主键！
               );
外键 foreign key
     外键约束： 一种约束
     外键字段： 该字段上添加了外键约束
     外键值：外键字段当中的每一个值
注意： 外键引用的不一定是主键，但是只是要具有唯一性unique约束，且外键值可以为null.
  例如：
 t_class
classno   addr
......................................
100       北京
101       上海

 t_student
 no      name      con(FK引用t_class这张表的classno)
....................................................
1        jack          100
2        anli          100
3        meimei        101
1        shuiguo       101
con字段没有任何约束的时候，可能会导致数据无效，例如出现"103",这个不在的值，因此需要添加外键约束foreign key
drop table if exists t_class;
drop table if exists t_student;
  create table t_class(
        classno int primary key,
        addr varchar(255)
  )
  create table t_student(
        no int primary key,
        con int, // 注意此类型要和t_class的classno值类型要一样
        foreign key(con) references t_class(classno)
   )
     注意：当添加了外键以后
            t_class是父表     t_student是子表，父子关系
  1.  删除表的顺序
         先删除子，再删除父
  2. 创建数据的顺序
        先创建父，再创建子
  3.  删除数据的顺序        
        先删除子，在删除父
   4. 插入数据的顺序  
         先插入父，再插入子