Java mysql 2

#1.SQL结构化查询语言：
必须是通过服务器，连接我们的数据库，才能进行操作
使用各种SQL语句、增、删、改、查
1> 添加数据
 insert into 表名 (字段1,字段2……) values (值1,值2……);
 insert into 表名 values (值1,值2……),(值1,值2……);
 insert into 表名 (字段1,字段2……) values (值1,值2……),(值1,值2……); 
 insert into 表名 values (值1,值2...);
 insert into 表名 set 字段1=值1,字段2=值2...;

注意：
值和字段名要一一对应，否则会报错
你写入的值一定要和数据类型相匹配

2> 删除数据
delete from 表名 where 条件;
注意：删除数据的时候，一定要加上where条件，否则会删除所有的数据

3> 修改数据
update 表名 set 要修改的字段=修改后的值 where 条件
注意：修改数据的时候，一定要加上where条件，否则会修改所有的数据

4> 查找数据
select *(所有字段) from 表;
select 字段1,字段2.. from 表;

1) DDL 数据定义语言
用于定义和管理数据对象，包括数据库，数据表等。
数据库 -> 数据表：对数据库或数据表的创建、删除、修改等操作
    create 创建表、视图、索引...
    drop   删除表、视图、索引...
    alter  修改表
    truncate 截断
    
2) DML 数据操控语言 (**)
用于操作数据库对象中所包含的数据。
对数据的 增、删、改 这些操作，就是数据操作语言
    insert 插入数据
    update 修改数据
    delete 删除数据
    select 查询数据
    
3) DCL 数据控制语言
是用来管理数据库的语言，包括管理权限及数据更改。
    grant     授权
    revoke  回收授权

4) TCL 事务控制语言 transaction control 
    start transaction 开始事务（begin）
    commit
    rollback
    
#2. DDL

##2.1 组合主键
    unique 唯一
    not null 非空
    primary key 主键 (只要有一个)
    foreign key 外键 (需要引用另一个表的主键或唯一列)
    
    |     1001 | 张三     |        1 | 企业管理 |   50 |
    |     1001 | 张三     |        1 | 企业管理 |   90 |  非法记录
    |     1001 | 张三     |        2 | 马克思   |   70 |
    |     1003 | 王五     |        2 | 马克思   |   70 |
    
    1001 + 1
    1001 + 1 非法
    1001 + 2 
    1003 + 2
create table sc(
    ...
    primary key (sid, cid) /*组（复）合主键*/
);
    
## 2.1 alter 修改
给老师表添加一列年龄 alter table teacher add age tinyint;
给老师表的姓名列扩展长度  alter table teacher modify tname varchar(30);

## 2.2 truncate 截断
delete from 表;     -- 如果和事务sql结合使用，是有机会恢复
truncate table 表;  -- 没法和事务结合，不能恢复，效率更高

#3. DML 
## 3.1 insert 插入数据
标准语法： insert into 表(多个列...) values (多个值...);
mysql独有的： 一次插入多行数据
            insert into 表(多个列...) values 
                (多个值), (多个值), (多个值) ...;
标准SQL写法：
insert into student values(null,'张三','1990-10-10','男');
insert into student values(null,'李四','1981-10-10','男');
mysql写法：
insert into student values(null,'张三','1990-10-10','男'),
    (null,'李四','1981-10-10','男');
    
    
标准语法：用insert插入另一张表的数据（列的个数和类型要一致）
insert into 表1（列1,列2...） select 列1，列2... from 表2；
insert into s2(sid,sname) select sid,sname from student;

## 3.2 update 对已有的数据进行更新
-- 更新所有的员工工资涨100
update emp set sal=sal+100;

-- 修改7369的员工部门为10号部门
update emp set deptno=10 where empno=7369;

-- 修改7000的员工部门为10号部门
update emp set deptno=10 where empno=7000;
如果条件不满足去更新不会报错，但影响行数是0

-- 修改7369的员工工资为9000，职务经理
update emp set sal=9000,job='manager'
    where empno=7369;
    
注意：主键列主要用于查询条件，一般不会更新

## 3.3 删除
delete from 表; 全部删除
delete from 表 where 条件; 删除符合条件的记录

-- 删除不能成功，因为员工表中有记录引用了10号部门
delete from dept where deptno=10;

-- 删除成功，因为40号部门没有员工引用
delete from dept where deptno=40;

主键所在表可以称为`主表`，而外界所在表称为`从表`
主表中删除的记录，如果被从表引用，那么会删除失败，违反外键约束（外键约束默认行为）。

外键约束还有一种行为，主表记录删除，把从表中与之关联的记录一块删除（级联删除）
以员工和部门为例：foreign key(deptno) references dept(deptno) on delete cascade
 例如： 删除10号部门，不仅会删除10号部门，而且会被从表（emp）中的10部门下所有员工也一块删除

## 3.4 select 查询

select 列名... from 表名 where 条件;

### 条件
1.比较运算
  表示相等的 =
  大于 >
  大于等于 >=
  小于  <
  小于等于 <=
  不等于 !=
  
2.逻辑运算 and（并且） or（或者） not(取反)
  
  -- 查询工资大于800 并且 部门等于20
  where sal>800 and deptno=20
  -- 部门=20 或者 等于30的
  where deptno=20 or deptno=30;
  
3. null值
数据库中的null ，比较特殊，不能使用=来比较, 必须用 is null
  例如：查询佣金为null的员工
  select * from emp where comm is null;
  例如：查询佣金不为null的员工
  select * from emp where comm is not null;

4. like (模糊查询)
   例如查询以 ‘S’字母打头的员工
   
select * from emp where ename like 'S%';

通配符 % 表示匹配0~多个任意的字符
通配符 _ 表示匹配一个任意字符

5. between ... and ... 范围匹配
求工资大于等于1000， 小于等于2000的员工
select * from emp where sal>=1000 and sal<=2000;

select * from emp where sal between 1000 and 2000;
1) 必须按有小到大顺序
2) between 包含等于条件

6. in 求这一列是否在一个值列表内
求所有销售人员和经理
select * from emp where job='SALESMAN' or job='MANAGER';

select * from emp where job in ('SALESMAN', 'MANAGER');

7. limit (mysql特有的)
用来限制返回的结果数
1) limit n;       n表示最多返回几条记录
2) limit m, n;    m代表偏移量（下标） 注意偏移量从0开始

8. order by 用来给结果排序
语法： order by 列名 asc|desc, 列名 asc|desc, ...
例如：按工资升序：
select * from emp order by sal;
select * from emp order by sal asc;
按工资降序：
select * from emp order by sal desc;
其中如果不写asc或desc，默认升序

如果多列排序，如果第一列取值相同，再按照第二列进行排序，例如：
先按工资排序，工资相同的，再按姓名排序：
select * from emp order by sal asc, ename asc; /*如果升序，两个asc都可以省略*/

9. select 语句的顺序：
select ... from 表 where 条件 order by 排序 limit 限制

## 3.5 函数
例如 lower 函数把字符变成小写
     upper 函数把字符变成大写
     
常用的函数 :
1) 时间加减
date_add(原始时间, 时间间隔)
    时间间隔的语法：  interval 数字 时间单位
    数字可以是正数或负数
    
    select date_add(now(), interval 1 day );
2) 提取时间的某个部分
extract(时间部分 from 原始时间)
select extract(year from now());
select extract(year_month from now());
返回的结果是一个数字类型

例：要获取1981年入职的员工
select * from emp where extract(year from hiredate)=1981;

3)类型转换
select cast('11' as signed)+1;
select cast('12.55555' as decimal(5,2));

4)拼接字符串
concat(值1, 值2, ... 值n)
select concat('a', 'b', 'c');
select concat('a', 18, 'c'); /*可以把其它类型当做字符串拼接*/

5) 求长度的函数
char_length 按字符为单位统计长度
length      按字节为单位统计长度
utf8mb4编码下，汉字一个字符占三个字节，英文一个字符占一个字节

6) 标准sql：
case
    when 条件1 then 值1
    when 条件2 then 值2
    ...
    else 值n
end
整个case...end 最后返回一个值

select sal, 
case 
    when sal>2000 then '高工资'
    when sal between 1000 and 2000 then '中等'
    else '低工资'
end from emp;

7) 组函数
最大值 max(列)
最小值 min(列)
和     sum(列)
个数   count(列)  会排除null值
       count(*)   针对这次查询，看看一共有多少行
平均值 avg(列)

8) 去除重复
distinct
select distinct job from emp; /*去除重复后的职位有哪些*/
select count(distinct job) from emp; /*有几种职位*/

9) 查询帮助
? contents   总目录

## 3.6 分组语法 (group by 后的列取值相同的会被分为一组)
select ... from 表 group by 列1,列2... having 分组条件

select deptno,count(*),max(sal) from emp group by deptno;
把部门编号取值相同的分为一组, 配合组函数一起使用

|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |

|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000.00 |    NULL |     20 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100.00 |    NULL |     20 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |

|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |

查询分组后个数大于3的部门编号和个数
select deptno,count(*),max(sal) from emp 
   group by deptno having count(*)>3 order by max(sal);
   
分组后 select，order by 的限制
  1) select,order by 中的列必须和group by中的列一样
  2) 其它没有包含在 group by语句中的列，必须和组函数一起用
   
执行从先到后
where 进行一遍条件过滤 , 再执行 group by 分组,  再执行 having中的条件, 再执行select, 再执行order by, 执行limit

语法顺序
select ... from ... where ... group by ... having ... order by ... limit;

列别名问题
语法：  select 列名 别名 ... 
语法2： select 列名 as 别名 ...

select deptno 部门编号,count(*) 人数,max(sal) 最高工资 from emp 
   group by deptno having count(*)>3 order by max(sal);
   
select deptno 部门编号,count(*) 人数,max(sal) 最高工资 from emp 
   group by deptno having count(*)>3 order by 人数;
   
select empno "员工 编号" from emp; /*列别名中如果存在空格等特殊符号，需要用双引号包围列别名*/
   
表别名问题
语法 ： select ... from 表 别名
select e.* from emp e;

多列分组
select deptno, job, count(*) from emp group by deptno, job; 
/*按deptno和job这两列取值都相同的分为一组*/

|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |

|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |

|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |

|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000.00 |    NULL |     20 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |

|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100.00 |    NULL |     20 |

|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |

|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |

|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |

|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |